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));
|
}
|
|
}
|
}
|
