using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using System;
using System.Data;

namespace BatchService.Framework.Utility
{
    public class LISHelper
    {
        public enum SampleQueryActionType
        {
            SampleInfo,
            ResultInfo,
            ValidatedReulstInfo_TB_LABRESULT,
            ValidatedReulstInfo_TB_LABRESULTXJ,
            ValidatedResultInfo_TB_CURRENT_ITEM_RESULT,
            ValidatedResultInfo_BACT,
            SampleInfoAndResultInfo,
            SampleFee,
            /// <summary>
            /// 未发送给HIS的标本
            /// </summary>
            UnSendSampleInfo,
            /// <summary>
            /// 取消审核的标本
            /// </summary>
            CanceledSampleInfo,
            TB_INSTRUMENT_LIST,
        }
        public enum ItemQueryActionType
        {
            TB_ITEM_PROPERTY,
            GetCalcuatedItemProperty,
        }
        public enum DicType
        {
            TB_INSTRUMENT_LIST,
            TB_OPERATOR,
            TB_CODE_INFO,
        }

        public enum UpdateActionType
        {
            Sending,
            Sended,
            SendError,
        }

        public static string EncryptString(string sourceText)
        {
            string is_out = "";
            long il_x, il_y, il_len, il_bit;

            il_len = sourceText.Length;
            il_x = 0;
            il_y = 0;
            is_out = "";
            for (var i = 0; i < il_len; i++)
            {
                il_bit = (int)((char)sourceText.Substring(i, 1)[0]);
                il_y = ((il_bit * 13) % 256) + il_x;
                is_out = is_out + (char)(il_y);
                il_x = (il_bit * 13) / 256;
            }
            is_out = is_out + (char)(il_x);
            sourceText = is_out;
            il_len = sourceText.Length;
            il_x = 0;
            il_y = 0;
            is_out = "";
            for (var i = 0; i < il_len; i++)
            {
                il_bit = (int)((char)sourceText.Substring(i, 1)[0]);
                il_y = (il_bit / 16) + 64;
                is_out = is_out + (char)(il_y);
                il_y = (il_bit % 16) + 64;
                is_out = is_out + (char)(il_y);
            }
            if (is_out == "@@")
            {
                is_out = "";
            }
            return is_out;
        }

        public string GetAlarmTestItem(string lisconnString, string sampleDate, string instrument, string sampleno, string itemCode)
        {
            string sql = string.Format("SELECT a.* FROM TB_REPORTLIMIT a WHERE a.sample_date='{0}' and a.instrument='{1}' and a.sample_no='{2}' and a.item_code='{3}'",
                sampleDate, instrument, sampleno, itemCode);
            var ds = SqlHelper.ExecuteDataset(lisconnString, CommandType.Text, sql);
            return JsonConvert.SerializeObject(ds.Tables[0], new DataTableConverter());
        }
        public string GetAlarmSamplesBySampleDate(string lisConnString, string sampleDate, string sendflag)
        {
            string sql = string.Format("SELECT a.*,b.FIRST_VALIDATE_DATE,b.PATIENT_TYPE,b.VALIDATE_DOCTOR,rtrim(c.code_info) as 'dept' FROM TB_REPORTLIMIT a " +
                " left join tb_current_sample_info b on a.sample_date=b.sample_date and a.sample_no=b.sample_no and a.instrument=b.instrument " +
                " left join tb_code_info c on b.department=c.code_name and code_kind='DP'" +
                " WHERE a.sample_date>='{0}'", sampleDate);
            if (sendflag == null)
            {
                sql += " and (a.sendflag is null or a.sendflag='9')";
            }
            else
            {
                sql += string.Format(" and a.sendflag = '{0}'", sendflag);
            }
            sql += " ORDER BY a.sample_date,a.instrument, a.sample_no,a.item_code";
            var ds = SqlHelper.ExecuteDataset(lisConnString, CommandType.Text, sql);
            return JsonConvert.SerializeObject(ds.Tables[0], new DataTableConverter());
        }

        public void UpdateAlarmSampleState(string lisConnString, string sampledate, string instrument, string sampleno, string sendflag)
        {
            string sql = string.Format("update TB_REPORTLIMIT set sendflag='{0}' where sample_date='{1}' and instrument='{2}' and sample_no='{3}'",
                sendflag, sampledate, instrument, sampleno);
            SqlHelper.ExecuteNonQuery(lisConnString, CommandType.Text, sql);
        }

        public string GetDicInfo(string lisConnString,
            DicType dicType, JObject paraJson)
        {
            string sql = "";
            switch (dicType)
            {
                case DicType.TB_OPERATOR:
                    sql = string.Format("SELECT OPERATOR_ID, OPERATE_NAME, VALIDATE_FLAG, PASSWORD, SHORTNAME, ROLE, " +
                "DEPARTMENT, REFRESH_FLAG, LAST_MODIFIED, updatetime FROM dbo.TB_OPERATOR");
                    break;
                case DicType.TB_INSTRUMENT_LIST:
                    if (paraJson.Count == 0)
                    {
                        sql = string.Format("SELECT * FROM dbo.TB_INSTRUMENT_LIST");
                    }
                    else
                    {
                        sql = string.Format("SELECT * FROM dbo.TB_INSTRUMENT_LIST where INSTRUMENT_CODE='{0}'",
                            paraJson["Instrument"].ToString());
                    }
                    break;
                case DicType.TB_CODE_INFO:
                    sql = string.Format("SELECT * FROM TB_CODE_INFO where CODE_KIND='{0}' order by CODE_NAME", StringHelper.GetString(paraJson["CODE_KIND"]));
                    break;
            }
            var dt = SqlHelper.ExecuteDataset(lisConnString, CommandType.Text, sql).Tables[0];
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            return JsonConvert.SerializeObject(dt, new DataTableConverter());
        }

        /// <summary>
        /// 执行LIS标本相关查询
        /// </summary>
        /// <param name="lisConnString">LIS连接字符串</param>
        /// <param name="para">JSON参数对象,Instrument,SampleDate,SampleNo</param>
        /// <returns>返回JSON</returns>
        public string ExecuteSampleQueryAction(string lisConnString,
            SampleQueryActionType queryActionType,
            string instrument = "",
            string sampleDate = "",
            string sampleNo = "",
            int topNumber = 0)
        {
            string sql = "";
            string temp = "";
            switch (queryActionType)
            {
                case SampleQueryActionType.SampleInfo:
                    sql = string.Format("SELECT * FROM TB_CURRENT_SAMPLE_INFO a " +
                        " INNER JOIN TB_INSTRUMENT_LIST b ON a.INSTRUMENT=b.INSTRUMENT_CODE" +
                        " WHERE a.SAMPLE_DATE='{0}' AND a.INSTRUMENT='{1}' AND a.SAMPLE_NO='{2}'",
                        sampleDate, instrument, sampleNo);
                    break;
                case SampleQueryActionType.SampleInfoAndResultInfo:
                    sql = string.Format("SELECT * FROM TB_CURRENT_SAMPLE_INFO a INNER JOIN TB_CURRENT_ITEM_RESULT b " +
                        " ON a.SAMPLE_DATE=b.SAMPLE_DATE AND a.INSTRUMENT=b.INSTRUMENT AND a.SAMPLE_NO=b.SAMPLE_NO" +
                        " WHERE a.SAMPLE_DATE='{0}' AND a.INSTRUMENT='{1}' AND a.SAMPLE_NO='{2}'",
                        sampleDate, instrument, sampleNo);
                    break;
                case SampleQueryActionType.ResultInfo:
                    sql = string.Format("SELECT * FROM TB_CURRENT_ITEM_RESULT a " +
                        "WHERE a.SAMPLE_DATE='{0}' AND a.INSTRUMENT='{1}' AND a.SAMPLE_NO='{2}'",
                        sampleDate, instrument, sampleNo);
                    break;
                case SampleQueryActionType.ValidatedResultInfo_TB_CURRENT_ITEM_RESULT:
                    sql = string.Format("SELECT * FROM TB_CURRENT_SAMPLE_INFO a " +
                        " INNER JOIN TB_CURRENT_ITEM_RESULT b ON a.SAMPLE_DATE=b.SAMPLE_DATE " +
                        " INNER JOIN TB_ITEM_PROPERTY c ON b.INSTRUMENT=c.INSTRUMENT and b.ITEM_CODE=c.ITEM_CODE " +
                        " AND a.INSTRUMENT=b.INSTRUMENT AND a.SAMPLE_NO=b.SAMPLE_NO " +
                        " WHERE a.VALIDATE_FLAG='2' and a.SAMPLE_DATE='{0}' AND a.INSTRUMENT='{1}' " +
                        " AND a.SAMPLE_NO='{2}' and c.print_type='1' order by c.REPORT_SEQ",
                        sampleDate, instrument, sampleNo); 
                    break;
                case SampleQueryActionType.ValidatedReulstInfo_TB_LABRESULT:
                    sql = string.Format("SELECT b.*,c.IS_COUNT,c.FORMULA FROM TB_CURRENT_SAMPLE_INFO a " +
                        " INNER JOIN TB_LABRESULT b ON a.SAMPLE_DATE=b.SAMPLE_DATE " +
                        " AND a.INSTRUMENT=b.INSTRUMENT AND a.SAMPLE_NO=b.SAMPLE_NO " +
                        " INNER JOIN TB_ITEM_PROPERTY c on b.instrument=c.INSTRUMENT and b.item_no=c.ITEM_CODE " +
                        " WHERE left(a.instrument,3)<>'@#@' and a.VALIDATE_FLAG='2' and a.SAMPLE_DATE='{0}' AND a.INSTRUMENT='{1}' " +
                        " AND a.SAMPLE_NO='{2}'",
                        sampleDate, instrument, sampleNo);
                    break;
                case SampleQueryActionType.ValidatedReulstInfo_TB_LABRESULTXJ:
                    sql = string.Format("SELECT b.* FROM TB_CURRENT_SAMPLE_INFO a " +
                        " INNER JOIN TB_LABRESULTXJ b ON a.SAMPLE_DATE=b.SAMPLE_DATE " +
                        " AND a.INSTRUMENT=b.INSTRUMENT AND a.SAMPLE_NO=b.SAMPLE_NO " +
                        " WHERE a.VALIDATE_FLAG='2' and a.SAMPLE_DATE='{0}' AND a.INSTRUMENT='{1}' " +
                        " AND a.SAMPLE_NO='{2}'",
                        sampleDate, instrument, sampleNo);
                    break;
                case SampleQueryActionType.SampleFee:
                    sql = string.Format("SELECT a.* FROM TB_CURRENT_SAMPLE_FEE a " +
                        " WHERE a.SAMPLE_DATE='{0}' AND a.INSTRUMENT='{1}' AND a.SAMPLE_NO='{2}'",
                        sampleDate, instrument, sampleNo);
                    break;
                case SampleQueryActionType.UnSendSampleInfo:

                    if (topNumber > 0)
                    {
                        temp = " top " + topNumber;
                    }
                    sql = string.Format("select * from (SELECT {0} a.*,b.INSTRUMENT_KIND,b.INSTRUMENT_NAME FROM TB_CURRENT_SAMPLE_INFO a " +
                        " INNER JOIN TB_INSTRUMENT_LIST b ON a.INSTRUMENT=b.INSTRUMENT_CODE" +
                        " WHERE 1=1 " +
                        " AND a.VALIDATE_FLAG='2' AND (a.SendFlag IS NULL OR a.SendFlag=0) and " +
                        " (a.BARCODE is not null and a.BARCODE<>'') and a.SAMPLE_DATE>=getdate()-30 order by a.sample_date desc,a.first_validate_date asc) aa " +
                        "union all " +
                        "select * from (SELECT {0} a.*,b.INSTRUMENT_KIND,b.INSTRUMENT_NAME FROM TB_CURRENT_SAMPLE_INFO a " +
                        " INNER JOIN TB_INSTRUMENT_LIST b ON a.INSTRUMENT=b.INSTRUMENT_CODE" +
                        " WHERE 1=1 " +
                        " AND a.VALIDATE_FLAG='2' AND a.SendFlag =99 and " +
                        " (a.BARCODE is not null and a.BARCODE<>'') and a.SAMPLE_DATE>=getdate()-30 order by a.sample_date desc,a.first_validate_date asc) bb " +
                        "union all " +  
                        "select * from (SELECT top 1 a.*,b.INSTRUMENT_KIND,b.INSTRUMENT_NAME FROM TB_CURRENT_SAMPLE_INFO a " +
                        " INNER JOIN TB_INSTRUMENT_LIST b ON a.INSTRUMENT=b.INSTRUMENT_CODE" +
                        " WHERE 1=1 " + 
                        " AND a.VALIDATE_FLAG='2' AND a.SendFlag<0 and " +  
                        " (a.BARCODE is not null and a.BARCODE<>'') and a.SAMPLE_DATE>=getdate()-30 order by a.sample_date desc,a.sendtime asc) cc ",
                        temp,
                        sampleDate, 
                        DateTime.Today.AddDays(-3));
                    break;
                case SampleQueryActionType.CanceledSampleInfo:

                    if (topNumber > 0)
                    {
                        temp = " top " + topNumber;
                    }
                    sql = string.Format("SELECT {0} a.*,b.INSTRUMENT_KIND,b.INSTRUMENT_NAME FROM TB_CURRENT_SAMPLE_INFO a " +
                        " INNER JOIN TB_INSTRUMENT_LIST b ON a.INSTRUMENT=b.INSTRUMENT_CODE" +
                        " WHERE a.SAMPLE_DATE='{1}' " +
                        " AND a.VALIDATE_FLAG='1' AND (a.SendFlag =-2) and " +
                        " (a.ORDER_NO is not null and a.ORDER_NO<>'')",
                        temp,
                        sampleDate);
                    break;
                case SampleQueryActionType.TB_INSTRUMENT_LIST:
                    sql = string.Format("SELECT * from TB_INSTRUMENT_LIST a" +
                        " WHERE a.INSTRUMENT_CODE='{0}'",
                        instrument);
                    break;
                case SampleQueryActionType.ValidatedResultInfo_BACT:
                    sql = string.Format("SELECT c.TEST_METHOD as 'anti_testmethod', c.current_result as 'anti_result',c.addt_result as 'anti_addtresult', * FROM TB_CURRENT_SAMPLE_INFO a " +
                        " INNER JOIN TB_BIO_CURRENT_BACT_RESULT b ON a.SAMPLE_DATE=b.SAMPLE_DATE AND a.INSTRUMENT=b.INSTRUMENT AND a.SAMPLE_NO=b.SAMPLE_NO " +
                        " LEFT JOIN TB_BIO_CURRENT_ANTI_RESULT c ON b.SAMPLE_DATE=c.SAMPLE_DATE AND b.INSTRUMENT=c.INSTRUMENT AND b.SAMPLE_NO=c.SAMPLE_NO AND b.BACT_CODE=c.BACT_CODE" +
                        " LEFT JOIN TB_BIO_ANTIBIOTICS_ITEM d ON c.INSTRUMENT=d.INSTRUMENT AND c.ANTI_CODE=d.ANTI_CODE" +
                        " WHERE a.VALIDATE_FLAG='2' and a.SAMPLE_DATE='{0}' AND a.INSTRUMENT='{1}' " +
                        " AND a.SAMPLE_NO='{2}' order by a.sample_date, a.instrument,a.sample_no,d.report_seq",
                        sampleDate, instrument, sampleNo);
                    break;
            }
            var dt = SqlHelper.ExecuteDataset(lisConnString, CommandType.Text, sql).Tables[0];
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            return JsonConvert.SerializeObject(dt, new DataTableConverter());
        }

        public int UpdateSentFlag(string lisConnString,
            UpdateActionType updateActionType,
            string instrument = "",
            string sampleDate = "",
            string sampleNo = "",
            string message = "")
        {
            string sql = "";
            string temp = "";
            message = message.Replace("'", "");
            switch (updateActionType)
            {
                case UpdateActionType.Sending:
                    sql = string.Format("update TB_CURRENT_SAMPLE_INFO set sendflag=1,sendtime='{3}',sendMessage='{4}'" +
                        " WHERE SAMPLE_DATE='{0}' AND INSTRUMENT='{1}' AND SAMPLE_NO='{2}'",
                        sampleDate, instrument, sampleNo, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), message);
                    break;
                case UpdateActionType.Sended:
                    sql = string.Format("update TB_CURRENT_SAMPLE_INFO set sendflag=2,sendtime='{3}',sendMessage='{4}'" +
                        " WHERE SAMPLE_DATE='{0}' AND INSTRUMENT='{1}' AND SAMPLE_NO='{2}'",
                        sampleDate, instrument, sampleNo, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), message);
                    break;
                case UpdateActionType.SendError:
                    sql = string.Format("update TB_CURRENT_SAMPLE_INFO set sendflag=-1,sendtime='{3}',sendMessage='{4}'" +
                        " WHERE SAMPLE_DATE='{0}' AND INSTRUMENT='{1}' AND SAMPLE_NO='{2}'",
                        sampleDate, instrument, sampleNo, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), message);
                    break;
            }
            return SqlHelper.ExecuteNonQuery(lisConnString, CommandType.Text, sql);
        }

        public string ExecuteItemQueryAction(string lisConnString,
            ItemQueryActionType itemActionType,
            string instrument = "",
            string itemCode = "",
            string result = "",
            string gender = "",
            string age = "",
            string sampletype = "",
            string diagnose = "")
        {
            string json = "";
            string sql = "";
            switch (itemActionType)
            {
                case ItemQueryActionType.GetCalcuatedItemProperty:
                    JObject itemProperty = new JObject();
                    itemProperty.Add("ItemCode", "");
                    itemProperty.Add("ItemName", "");
                    itemProperty.Add("Ref", "");
                    itemProperty.Add("Unit", "");
                    itemProperty.Add("RefLowest", "");
                    itemProperty.Add("RefHighest", "");
                    itemProperty.Add("ResultFlag", "M");

                    sql = string.Format("SELECT * FROM TB_ITEM_PROPERTY a " +
                        " WHERE a.INSTRUMENT='{0}' AND a.ITEM_CODE='{1}'",
                        instrument, itemCode);
                    var dt = SqlHelper.ExecuteDataset(lisConnString, CommandType.Text, sql).Tables[0];
                    DataRow row = dt.Rows[0];

                    itemProperty["ItemCode"] = StringHelper.GetString(row["ITEM_CODE"]);
                    itemProperty["ItemName"] = StringHelper.GetString(row["ITEM_NAME"]);
                    itemProperty["Ref"] = StringHelper.GetString(row["REPORT_LIMIT"]);
                    itemProperty["Unit"] = StringHelper.GetString(row["UNIT"]);
                    itemProperty["RefLowest"] = StringHelper.GetString(row["NORMAL_LOW"], "0");
                    itemProperty["RefHighest"] = StringHelper.GetString(row["NORMAL_HIGH"], "0");

                    if (StringHelper.IsNumeric(result) && !string.IsNullOrEmpty(StringHelper.GetString(row["REPORT_LIMIT"])))
                    {
                        double db_result = 0;
                        double db_high = 0;
                        double db_low = 0;

                        double.TryParse(result, out db_result);
                        double.TryParse(StringHelper.GetString(itemProperty["RefLowest"]), out db_low);
                        double.TryParse(StringHelper.GetString(itemProperty["RefHighest"]), out db_high);

                        LogHelper.Debug(string.Format("ItemCode:{0},ItemName:{1},Result:{2},Ref:{3},Low:{4},High:{5}",
                            StringHelper.GetString(row["ITEM_CODE"]),
                            StringHelper.GetString(row["ITEM_NAME"]),
                            db_result,
                            StringHelper.GetString(row["REPORT_LIMIT"]),
                            db_low,
                            db_high));
                        if (db_high >= db_low)
                        {
                            if (db_result > db_high)
                            {
                                itemProperty["ResultFlag"] = "H";
                            }
                            else if (db_result < db_low)
                            {
                                itemProperty["ResultFlag"] = "L";
                            }
                        }
                    }
                    sql = "";
                    string sql_temp = string.Format("SELECT * FROM TB_ITEM_LIMITS where 1=1 " +
                        " and INSTRUMENT='{0}' and ITEM_CODE='{1}' ",
                        instrument, itemCode);
                    if (StringHelper.GetString(row["IS_GENDER"]) == "Y" && !string.IsNullOrEmpty(gender))
                    {
                        sql += string.Format(" and GENDER='{0}'", gender);
                    }
                    if (StringHelper.GetString(row["IS_AGE"]) == "Y" && !string.IsNullOrEmpty(age))
                    {
                        sql += string.Format(" and (AGE_LOW<={0} and AGE_HIGH>={0}) ", age);
                    }
                    if (StringHelper.GetString(row["IS_SAMPLETYPE"]) == "Y" && !string.IsNullOrEmpty(sampletype))
                    {
                        sql += string.Format(" and SAMPLE_TYPE='{0}'", sampletype);
                    }
                    if (StringHelper.GetString(row["IS_DISGNOSIS"]) == "Y" && !string.IsNullOrEmpty(diagnose))
                    {
                        sql += string.Format(" and DISGNOSIS='{0}'", diagnose);
                    }
                    if (!string.IsNullOrEmpty(sql))
                    {
                        sql = sql_temp + sql;
                        LogHelper.Debug("取参考值SQL:" + sql);
                        var dt_limit = SqlHelper.ExecuteDataset(lisConnString, CommandType.Text, sql).Tables[0];
                        if (dt_limit.Rows.Count > 0)
                        {
                            var row_limit = dt_limit.Rows[0];
                            itemProperty["Ref"] = StringHelper.GetString(row_limit["LIMIT_VALUE"]);
                            itemProperty["RefLowest"] = StringHelper.GetString(row_limit["LIMIT_LOW"], "0");
                            itemProperty["RefHighest"] = StringHelper.GetString(row_limit["LIMIT_HIGH"], "0");

                            double db_result = 0;
                            double db_limit_high = 0;
                            double db_limit_low = 0;

                            double.TryParse(result, out db_result);
                            double.TryParse(itemProperty["RefLowest"].ToString(), out db_limit_low);
                            double.TryParse(itemProperty["RefHighest"].ToString(), out db_limit_high);

                            itemProperty["ResultFlag"] = "M";
                            if (db_limit_high >= db_limit_low)
                            {
                                if (db_result > db_limit_high)
                                {
                                    itemProperty["ResultFlag"] = "H";
                                }
                                else if (db_result < db_limit_low)
                                {
                                    itemProperty["ResultFlag"] = "L";
                                }
                            }
                            LogHelper.Debug(string.Format("ItemCode:{0},ItemName:{1},Result:{2},Ref:{3},Low:{4},High:{5},flag:{6}",
                                row["ITEM_CODE"].ToString(),
                                row["ITEM_NAME"].ToString(),
                                db_result,
                                row["REPORT_LIMIT"].ToString(),
                                db_limit_low,
                                db_limit_high,
                                itemProperty["ResultFlag"].ToString()));
                        }
                    }
                    json = JsonConvert.SerializeObject(itemProperty);
                    break;
                case ItemQueryActionType.TB_ITEM_PROPERTY:
                    sql = string.Format("SELECT * FROM TB_ITEM_PROPERTY a " +
                        " WHERE a.INSTRUMENT='{0}' AND a.ITEM_CODE='{1}'",
                        instrument, itemCode);
                    dt = SqlHelper.ExecuteDataset(lisConnString, CommandType.Text, sql).Tables[0];
                    json = JsonConvert.SerializeObject(dt, new DataTableConverter());
                    break;
            }

            return json;
        }

        public string GetTestItemesByFeeItemCode(string lisConnString,
            string instrument = "",
            string FeeItemCode = "")
        {
            var sql = string.Format("SELECT * FROM TB_FEEITEM_VS_REPORTITEM a " +
                " WHERE a.INSTRUMENT='{0}' AND a.FEEITEM_NO='{1}'",
                        instrument, FeeItemCode);
            var dt = SqlHelper.ExecuteDataset(lisConnString, CommandType.Text, sql).Tables[0];
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            return JsonConvert.SerializeObject(dt, new DataTableConverter());
        }

        public void TransferResultToLABRESULT(string lisConnString,
            string instrument = "",
            string sampleDate = "",
            string sampleNo = "")
        {
            var jsonInstrumentInfo = ExecuteSampleQueryAction(lisConnString,
                 SampleQueryActionType.TB_INSTRUMENT_LIST, instrument);
            var jsonInstrumentInfoObj = JsonConvert.DeserializeObject(jsonInstrumentInfo) as JArray;
            if (StringHelper.GetString(jsonInstrumentInfoObj[0]["INSTRUMENT_KIND"]).ToUpper() == "XJ")
            {
                #region 细菌结果

                string deleteSql = string.Format("delete FROM TB_LABRESULTXJ " +
                        " WHERE instrument='{0}' AND sample_no='{1}' AND sample_date='{2}'",
                        instrument,
                        sampleNo,
                        sampleDate);
                SqlHelper.ExecuteNonQuery(lisConnString, CommandType.Text, deleteSql);

                var jsonBactResults = ExecuteSampleQueryAction(lisConnString,
                     SampleQueryActionType.ValidatedResultInfo_BACT,
                     instrument, sampleDate, sampleNo);

                var jsonBactResultsObj = JsonConvert.DeserializeObject(jsonBactResults) as JArray;

                var index = 1;
                foreach (var jsonBactResult in jsonBactResultsObj)
                {
                    string sql = string.Format("SELECT COUNT(*) FROM TB_LABRESULTXJ a " +
                        " WHERE a.instrument='{0}' AND a.sample_no='{1}' AND a.sample_date='{2}' " +
                        " AND a.print_order='{3}'",
                        instrument,
                        sampleNo,
                        sampleDate,
                        index);

                    int rowCount = (int)SqlHelper.ExecuteScalar(lisConnString, CommandType.Text, sql);
                    if (rowCount > 0)
                    {
                        continue;
                    }

                    sql = string.Format("INSERT INTO TB_LABRESULTXJ " +
                        " (instrument, sample_no, sample_date, print_order, bact_code, bact_name, " +
                        " anti_code, anti_name, bact_result, resultkind, anti_Result, addt_result, " +
                        " test_method, result_date_time, feeitem_no, feeitem_name, order_no, reqno, " +
                        " bact_addtresult) " +
                        " VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', " +
                        " '{6}', '{7}', '{8}', '{9}', '{10}', '{11}'," +
                        " '{12}', '{13}', '{14}', '{15}', '{16}', '{17}'" +
                        " , '{18}')",
                        jsonBactResult["INSTRUMENT"].ToString(),
                        jsonBactResult["SAMPLE_NO"].ToString(),
                        jsonBactResult["SAMPLE_DATE"].ToString(),
                        index,
                        StringHelper.GetString(jsonBactResult["BACT_CODE"]),
                        StringHelper.GetString(jsonBactResult["BACT_NAME"]),

                        StringHelper.GetString(jsonBactResult["ANTI_CODE"]),
                        StringHelper.GetString(jsonBactResult["ANTI_NAME"]),
                        StringHelper.GetString(jsonBactResult["CURRENT_RESULT"]),
                        StringHelper.GetString(jsonBactResult["RESULT_KIND"]),
                        StringHelper.GetString(jsonBactResult["anti_result"]),
                        StringHelper.GetString(jsonBactResult["anti_addtresult"]),

                        StringHelper.GetString(jsonBactResult["anti_testmethod"]),
                        StringHelper.GetString(jsonBactResult["FIRST_VALIDATE_DATE"]),
                        "", "", "", "",
                        StringHelper.GetString(jsonBactResult["ADDT_RESULT"]));
                    SqlHelper.ExecuteNonQuery(lisConnString, CommandType.Text, sql);
                    index++;
                }
                #endregion
            }
            else
            {
                #region 普通标本结果
                string deleteSql = string.Format("delete FROM TB_LABRESULT " +
                        " WHERE instrument='{0}' AND sample_no='{1}' AND sample_date='{2}'",
                        instrument,
                        sampleNo,
                        sampleDate);
                SqlHelper.ExecuteNonQuery(lisConnString, CommandType.Text, deleteSql);

                var json = ExecuteSampleQueryAction(lisConnString,
                 SampleQueryActionType.ValidatedResultInfo_TB_CURRENT_ITEM_RESULT,
                 instrument,
                 sampleDate,
                 sampleNo);
                if (json.Length == 0) return;
                var results = JsonConvert.DeserializeObject(json) as JArray;

                foreach (JObject result in results)
                {
                    string sql = string.Format("SELECT COUNT(*) FROM TB_LABRESULT a " +
                        " WHERE a.instrument='{0}' AND a.sample_no='{1}' AND a.sample_date='{2}' " +
                        " AND a.item_no='{3}'",
                        instrument,
                        sampleNo,
                        sampleDate,
                        result["ITEM_CODE"].ToString());

                    int rowCount = (int)SqlHelper.ExecuteScalar(lisConnString, CommandType.Text, sql);
                    if (rowCount > 0)
                    {
                        continue;
                    }

                    string jsonItemProperty = ExecuteItemQueryAction(lisConnString,
                         ItemQueryActionType.GetCalcuatedItemProperty,
                         instrument, 
                         result["ITEM_CODE"].ToString(),
                         result["CURRENT_RESULT"].ToString(),
                         result["GENDER"].ToString(),
                         result["AGE"].ToString(),
                         result["SAMPLE_TYPE"].ToString(),
                         result["DIAGNOSE"].ToString());
                    var jsonObjItemProperty = JsonConvert.DeserializeObject(jsonItemProperty) as JObject;
                    sql = string.Format("INSERT INTO TB_LABRESULT " +
                        " (instrument, sample_no, sample_date, item_no, " +
                        " item_name, print_order, result_date_time, result, unit, resultflag, ref, " +
                        " feeitem_no, feeitem_name, order_no, reqno) " +
                        " VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', {5}, '{6}', '{7}', '{8}', '{9}'" +
                        " , '{10}', '{11}', '{12}', '{13}', '{14}')",
                        result["INSTRUMENT"].ToString(),
                        result["SAMPLE_NO"].ToString(),
                        result["SAMPLE_DATE"].ToString(),
                        StringHelper.GetString(result["ITEM_CODE"]),
                        StringHelper.GetString(result["ITEM_NAME"]),
                        StringHelper.GetString(result["REPORT_SEQ"]),
                        StringHelper.GetString(result["FIRST_VALIDATE_DATE"]),
                        StringHelper.GetString(result["CURRENT_RESULT"]),
                        StringHelper.GetString(jsonObjItemProperty["Unit"]),
                        StringHelper.GetString(jsonObjItemProperty["ResultFlag"]),
                        StringHelper.GetString(jsonObjItemProperty["Ref"]),
                        "", "", "", "");
                    SqlHelper.ExecuteNonQuery(lisConnString, CommandType.Text, sql);
                }

                #endregion
            }
        }

        public string ExecuteQueySQL(string lisConnString, string sql)
        {
            var dt = SqlHelper.ExecuteDataset(lisConnString, CommandType.Text, sql).Tables[0];
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            return JsonConvert.SerializeObject(dt, new DataTableConverter());
        }

        public string getOperatorCodeByName(string lisConnString, string name)
        {
            var sql = string.Format("select a.operator_id from tb_operator a where a.OPERATE_NAME='{0}'", name);
            return StringHelper.GetString(SqlHelper.ExecuteScalar(lisConnString, CommandType.Text, sql));
        }

    }
}