using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SOH.Kernel;
using EAS.Services;
using SOH.BLL;
using SOH.Entities;
using SOH.Data;
using SOH.Common;
using System.Text.RegularExpressions;
using SOH;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace SOH.JianYan
{
    [ModuleAttribute(ModuleID = "35054A26-DAD4-4F35-B650-981D555F31E8", ModuleName = "北京华大外检数据传输")]
    public partial class frmBeiJingHuaDa : SOH.Window.baseChildForm
    {
        int fddm;
        int zdsh;
        DataTable dgvDt = new DataTable();

        private static SqlConnection connection;
        private static SqlConnection connection1;

        private static SqlConnection zbconnection;
        private static SqlConnection zbconnection1;

        public frmBeiJingHuaDa()
        {
            InitializeComponent();
        }

        private void frmBeiJingHuaDa_Load(object sender, EventArgs e)
        {
            fddm = short.Parse(LoginUser.yydm);
            //初始化
            dgvDt.Columns.Add("jyxmbm", typeof(string));
            dgvDt.Columns.Add("tm", typeof(string)); //Barcode
            dgvDt.Columns.Add("datetime", typeof(string));//CollectdDate
            dgvDt.Columns.Add("jcxmmc", typeof(string));
            dgvDt.Columns.Add("name", typeof(string)); //PatientName
            dgvDt.Columns.Add("sex", typeof(string));
            dgvDt.Columns.Add("age", typeof(string));   //Age
            dgvDt.Columns.Add("result", typeof(string)); //Final
            dgvDt.Columns.Add("ckfw", typeof(string)); //DisploWhigh
            dgvDt.Columns.Add("jcxmbm", typeof(int));
        }

        private void button1_Click(object sender, EventArgs e)
        {
            shuaxin();
        }
        public void shuaxin()
        {
            string mapSql = "select * from DuiZhao";
            DataTable mapDt = new DataTable();



            DataTable dt = new DataTable();
            string KM_Sql = @"select Sinonym,Barcode,CollectdDate,PatientName,case Sex when 2 then '女' when 1 then '男' else '不详' end as xb,
                              Age,DisploWhigh,ShortName,Final,isReaded from report 
                               where isReaded=0 ";
//and CollectdDate between '" + dtStart.Value + "' and '" + dtEnd.Value + "'";

            if (fddm == 1)
            {
                dt = DzGetKingmedDataSet(KM_Sql);
                mapDt = DzGetKingmedDataSet(mapSql);
            }
            else
            {
                dt = ZbGetKingmedDataSet(KM_Sql);
                mapDt = ZbGetKingmedDataSet(mapSql);
            }

            if (dt.Rows.Count != 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    
                    DataRow[] mapDrArr = mapDt.Select("xmmc='" + dr["ShortName"].ToString() + "'");//查询
                    if (mapDrArr.Length > 0)
                    {
                        string newTm = "";
                        DataTable jcxmdzDt = new DataTable();
                        foreach (DataRow drfro in mapDrArr)
                        {
                            newTm = dr["Barcode"].ToString().Substring(0, dr["Barcode"].ToString().Length - 2);
                            string jcxmdzSql = "select * from t_jcxm j where jcxmbm in (select xmbm from t_zhxmmx where zhbm in (select zhbm from t_tempdjxm where tm='" + newTm + "'))";
                            if (fddm == 1)
                            {
                                jcxmdzDt = GetDataSet1(jcxmdzSql);
                            }
                            else
                            {
                                jcxmdzDt = GetZbDataSet1(jcxmdzSql);
                            }
                            if (jcxmdzDt.Rows.Count != 0)
                            {
                                DataRow[] mapdrs = jcxmdzDt.Select("jcxmbm=" + drfro["xmbm"]);
                                if (mapdrs.Length > 0)
                                {
                                    DataRow drs = dgvDt.NewRow();
                                    drs["jyxmbm"] = dr["Sinonym"].ToString();
                                    drs["tm"] = newTm;
                                    drs["datetime"] = dr["CollectdDate"].ToString();
                                    drs["jcxmmc"] = mapdrs[0]["jcxmmc"].ToString();
                                    drs["name"] = dr["PatientName"].ToString();
                                    drs["sex"] = dr["xb"].ToString();
                                    drs["age"] = dr["Age"].ToString();
                                    drs["result"] = dr["Final"].ToString();
                                    drs["ckfw"] = dr["DisploWhigh"].ToString();
                                    drs["jcxmbm"] = Convert.ToInt32(mapdrs[0]["jcxmbm"]);
                                    dgvDt.Rows.Add(drs);
                                }
                            }
                        }

                    }
                    else
                    {

                    }
                }
                dataGridView1.DataSource = dgvDt;
            }
            else
            {
                dataGridView1.DataSource = null;
            }
        }


        #region 数据库查询方法

        public static SqlConnection Connection
        {
            get
            {
                string con = "";
                System.Configuration.Configuration cm = System.Configuration.ConfigurationManager.OpenExeConfiguration(AppDomain.CurrentDomain.BaseDirectory + "\\local.ext");
                con = cm.AppSettings.Settings["Advice_DZ"].Value;
                if (connection == null)
                {
                    connection = new SqlConnection(con);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;

            }
        }

        public static SqlConnection zbConnection
        {
            get
            {
                string con = "";
                System.Configuration.Configuration cm = System.Configuration.ConfigurationManager.OpenExeConfiguration(AppDomain.CurrentDomain.BaseDirectory + "\\local.ext");
                con = cm.AppSettings.Settings["Advice_ZB"].Value;
                if (zbconnection == null)
                {
                    zbconnection = new SqlConnection(con);
                    zbconnection.Open();
                }
                else if (zbconnection.State == System.Data.ConnectionState.Closed)
                {
                    zbconnection.Open();
                }
                else if (zbconnection.State == System.Data.ConnectionState.Broken)
                {
                    zbconnection.Close();
                    zbconnection.Open();
                }
                return zbconnection;

            }
        }

        public static SqlConnection Connection1
        {
            get
            {
                string con = "";
                System.Configuration.Configuration cm = System.Configuration.ConfigurationManager.OpenExeConfiguration(AppDomain.CurrentDomain.BaseDirectory + "\\local.ext");
                con = cm.AppSettings.Settings["bodycheck"].Value;
                if (connection1 == null)
                {
                    connection1 = new SqlConnection(con);
                    connection1.Open();
                }
                else if (connection1.State == System.Data.ConnectionState.Closed)
                {
                    connection1.Open();
                }
                else if (connection1.State == System.Data.ConnectionState.Broken)
                {
                    connection1.Close();
                    connection1.Open();
                }
                return connection1;

            }
        }

        public static SqlConnection zbConnection1
        {
            get
            {
                string con = "";
                System.Configuration.Configuration cm = System.Configuration.ConfigurationManager.OpenExeConfiguration(AppDomain.CurrentDomain.BaseDirectory + "\\local.ext");
                con = cm.AppSettings.Settings["zbbodycheck"].Value;
                if (zbconnection1 == null)
                {
                    zbconnection1 = new SqlConnection(con);
                    zbconnection1.Open();
                }
                else if (zbconnection1.State == System.Data.ConnectionState.Closed)
                {
                    zbconnection1.Open();
                }
                else if (zbconnection1.State == System.Data.ConnectionState.Broken)
                {
                    zbconnection1.Close();
                    zbconnection1.Open();
                }
                return zbconnection1;

            }
        }

        public static DataTable DzGetKingmedDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable ZbGetKingmedDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, zbConnection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable GetDataSet1(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection1);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable GetZbDataSet1(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, zbConnection1);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable ZBGetDataSet1(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, zbConnection1);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable ExecuteCommand(string procName, string value)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection;
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@khbm", value);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }

        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }

        public static int ZbExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, zbConnection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }

        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }

        public static int GetScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public static int GetScalar(params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection;
            cmd.CommandText = "Pro_InsertOrder";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
        public static void ExecuteSqlTran(List<string> SQLStringList)
        {

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection;
            SqlTransaction tx = Connection.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();

            }
            catch (System.Data.SqlClient.SqlException E)
            {
                tx.Rollback();
                //throw new Exception(E.Message);
                MessageBox.Show("登记失败！");
            }


        }

        #endregion

        public bool isnumeric(string str)
        {
            char[] ch = new char[str.Length];
            ch = str.ToCharArray();
            for (int i = 0; i < str.Length; i++)
            {
                if (ch[i] < 48 || ch[i] > 57)
                    return false;
            }
            return true;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (checkBox1.Checked == true)
            {
                zdsh = 4;
            }
            else
            {
                zdsh = 1;
            }

            if (dgvDt.Rows.Count != 0)
            {
                foreach (DataRow dr in dgvDt.Rows)
                {
                    string tmh = "";
                    if(dr["tm"].ToString().Length>10)
                    {
                        tmh = dr["tm"].ToString().Substring(0, dr["tm"].ToString().Length - 2);
                    }else
                    {
                        tmh = dr["tm"].ToString();
                    }
                    


                    string xmbm = dr["jyxmbm"].ToString();
                    string jcxmSql = "select * from t_jcxm where zhuxiao=0 and jcxmbm='" + dr["jcxmbm"].ToString() + "'";
                    DataTable resultDt = new DataTable();

                    if (fddm == 1)
                    {
                        resultDt = GetDataSet1(jcxmSql);
                    }
                    else
                    {
                        resultDt = GetZbDataSet1(jcxmSql);
                    }


                    if (resultDt.Rows.Count > 0)
                    {
                        //录入体检结果
                        t_tjjg tjjg = new t_tjjg();
                        tjjg.tm = Convert.ToInt32(tmh);
                        tjjg.xmbm = Convert.ToInt32(resultDt.Rows[0]["jcxmbm"].ToString());
                        tjjg.jg = dr["result"].ToString();
                        tjjg.dw = resultDt.Rows[0]["dw"].ToString();
                        tjjg.lx = 0;
                        tjjg.cksx = resultDt.Rows[0]["cksx"].ToString();
                        tjjg.ckxx = resultDt.Rows[0]["ckxx"].ToString();
                        tjjg.cksx = tjjg.cksx.Equals("") ? "0" : tjjg.cksx;
                        tjjg.ckxx = tjjg.ckxx.Equals("") ? "0" : tjjg.ckxx;
                        tjjg.czy = LoginUser.username;
                        tjjg.dyckfw = dr["ckfw"].ToString();
                        tjjg.upflag = 5;
                        var vservice = ServiceContainer.GetService<It_tjjg>();
                        vservice.Update(tjjg);

                        //修改金域数据状态值为2
                        string upStataSql = "update report set isReaded=1 where Barcode like '%" + dr["tm"].ToString() + "%' and Sinonym='" + xmbm + "'";
                        if (fddm == 1)
                        {
                            int km = ExecuteCommand(upStataSql);
                        }
                        else
                        {
                            int kmZb = ZbExecuteCommand(upStataSql);
                        }


                        //计算体检诊断
                        string zdhsZd = "";
                        if (isnumeric(tjjg.jg))
                        {
                            decimal dbjg = Convert.ToDecimal(tjjg.jg);

                            try
                            {
                                if (dbjg > Convert.ToDecimal(tjjg.cksx))
                                {
                                    zdhsZd = resultDt.Rows[0]["jcxmmc"].ToString() + "偏高\r\n";

                                }
                                else if (dbjg < Convert.ToDecimal(tjjg.cksx))
                                {
                                    zdhsZd = resultDt.Rows[0]["jcxmmc"].ToString() + "偏低\r\n";
                                }
                                else if (tjjg.jg.Trim().Equals("阳性"))
                                {
                                    zdhsZd = resultDt.Rows[0]["jcxmmc"].ToString() + " 阳性\r\n";
                                }
                                else if (tjjg.jg.Trim().Equals("+"))
                                {
                                    zdhsZd = resultDt.Rows[0]["jcxmmc"].ToString() + " 阳性\r\n";
                                }
                                else if (tjjg.jg.Trim().Equals("+-"))
                                {
                                    zdhsZd = resultDt.Rows[0]["jcxmmc"].ToString() + " 弱阳性\r\n";
                                }
                                else if (tjjg.jg.Trim().Equals("弱阳"))
                                {
                                    zdhsZd = resultDt.Rows[0]["jcxmmc"].ToString() + " 弱阳性\r\n";
                                }
                                else if (tjjg.jg.Trim().Equals("弱阳性"))
                                {
                                    zdhsZd = resultDt.Rows[0]["jcxmmc"].ToString() + " 弱阳性\r\n";
                                }
                                else
                                {
                                    zdhsZd = resultDt.Rows[0]["jcxmmc"].ToString() + "：" + tjjg.jg + "\r\n";
                                }
                            }
                            catch
                            {
                            }
                        }


                        if (tjjg.jg.IndexOf(">") != 0)
                        {
                            zdhsZd = resultDt.Rows[0]["jcxmmc"].ToString() + "偏高\r\n";
                        }

                        if (tjjg.jg.IndexOf("<") != 0)
                        {
                            zdhsZd = resultDt.Rows[0]["jcxmmc"].ToString() + "偏低\r\n";
                        }

                        //录入体检诊断
                        string tjjdSql = "select * from t_tjjd where tm=" + tmh + " and ksbm=" + resultDt.Rows[0]["ksbm"].ToString() + "";
                        DataTable tjjdDt = new DataTable();
                        string ksSql = "select * from t_ks where ksbm=" + resultDt.Rows[0]["ksbm"].ToString() + "";
                        DataTable ksDt = new DataTable();

                        if (fddm == 1)
                        {
                            tjjdDt = GetDataSet1(tjjdSql);
                            ksDt = GetDataSet1(ksSql);
                        }
                        else
                        {
                            tjjdDt = GetZbDataSet1(tjjdSql);
                            ksDt = GetZbDataSet1(ksSql);
                        }

                        t_tjjd tjjd = new t_tjjd();
                        tjjd.tm = Convert.ToInt32(tmh);
                        short ksbm = Convert.ToInt16(resultDt.Rows[0]["ksbm"].ToString());
                        tjjd.ksbm = ksbm;

                        if (ksbm == 80)
                        {
                            tjjd.zdjg = "详情请见检验结果报告单";
                        }
                        else
                        {
                            if (tjjdDt.Rows.Count == 0)
                            {
                                if (zdhsZd == "")
                                {
                                    tjjd.zdjg = ksDt.Rows[0]["kszdjg"].ToString().Trim();
                                }
                                else
                                {
                                    tjjd.zdjg = zdhsZd;
                                }
                                
                            }
                            else
                            {
                                if (tjjdDt.Rows[0]["zdjg"].ToString().Trim() == ksDt.Rows[0]["kszdjg"].ToString().Trim())
                                {
                                    tjjd.zdjg = zdhsZd;
                                }
                                else
                                {
                                    tjjd.zdjg = tjjdDt.Rows[0]["zdjg"].ToString() + zdhsZd;
                                }
                            }
                        }

                        if (fddm==2)
                        {
                            tjjd.zdys = "杜文经";
                        }
                        else
                        {
                            tjjd.zdys = LoginUser.xm;
                        }
                        tjjd.czy = LoginUser.username;
                        tjjd.djsj = DateTime.Now.ToString("yyyy-MM-dd");
                        tjjd.upflag = 5;
                        var vservice1 = ServiceContainer.GetService<It_tjjd>();
                        vservice1.Update(tjjd, false, short.Parse(LoginUser.yydm));

                        //修改检查项目状态
                        string tempdjxmSql = "select * from t_tempdjxm where tm='" + tmh + "' and ksbm=" + ksbm + " and ztz !=4";
                        DataTable tempdjxmDt = new DataTable();

                        if (fddm == 1)
                        {
                            tempdjxmDt = GetDataSet1(tempdjxmSql);
                        }
                        else
                        {
                            tempdjxmDt = GetZbDataSet1(tempdjxmSql);
                        }
                        string message = "";
                        if (tempdjxmDt.Rows.Count != 0)
                        {

                            foreach (DataRow dr1 in tempdjxmDt.Rows)
                            {
                                string tjjg2Sql = "select * from t_tjjg where tm='" + tmh + "' and xmbm in (select xmbm from t_zhxmmx where zhbm='" + dr1["zhbm"].ToString() + "')";
                                DataTable tjjg2Dt = new DataTable();
                                string zhxmmxSql = "select * from t_zhxmmx where zhbm='" + dr1["zhbm"].ToString() + "'";
                                DataTable zhxmmxDt = new DataTable();

                                if (fddm == 1)
                                {
                                    tjjg2Dt = GetDataSet1(tjjg2Sql);
                                    zhxmmxDt = GetDataSet1(zhxmmxSql);
                                }
                                else
                                {
                                    tjjg2Dt = GetZbDataSet1(tjjg2Sql);
                                    zhxmmxDt = GetZbDataSet1(zhxmmxSql);
                                }

                                if (tjjg2Dt.Rows.Count == zhxmmxDt.Rows.Count)
                                {

                                    t_tempdjxm tempdjxm = new t_tempdjxm();
                                    tempdjxm.tm = Convert.ToInt32(tmh);
                                    tempdjxm.zhbm = Convert.ToInt32(dr1["zhbm"].ToString());
                                    tempdjxm.ztz = Convert.ToInt16(zdsh);
                                    tempdjxm.czy = LoginUser.username;
                                    tempdjxm.upflag = 1;
                                    //int ksbm = Convert.ToInt32(cbxhyxm.SelectedValue.ToString());
                                    tempdjxm.ksbm = ksbm;
                                    tempdjxm.operid = "";
                                    var tempvs = ServiceContainer.GetService<It_tempdjxm>();
                                    tempvs.updateZtz(tempdjxm);
                                }
                                else
                                {
                                    message += "条码号：" + tmh + "组合项目：" + dr1["zhbm"].ToString() + "中有项目未录入结果，无法自动审核。\r\n ";
                                }
                            }
                        }
                        if (message != "")
                        {
                            //MessageBox.Show(message);
                        }
                        else
                        {
                            //MessageBox.Show("审核成功！");
                        }
                    }
                    else
                    {
                        continue;
                    }
                }
                shuaxin();
            }
        }
    }
}