public static string getDataList(string conditions, int rows)
{
if (rows == 0)
{
return("[]");
}
StatisticsBLL bll = new StatisticsBLL();
QueryConditionModel conditionModel = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
DataTable dt = bll.StatisticsByModelBLL(conditionModel);
List <KeyValueModel> list = new List <KeyValueModel>();
if (dt.Rows.Count > 0)
{
list.Add(new KeyValueModel("全血-CBC", string.IsNullOrEmpty(dt.Rows[0]["count_times_wb_cbc"].ToString()) ? "0" : dt.Rows[0]["count_times_wb_cbc"].ToString()));
list.Add(new KeyValueModel("全血-CBC+CRP", string.IsNullOrEmpty(dt.Rows[0]["count_times_wb_cbc_crp"].ToString()) ? "0" : dt.Rows[0]["count_times_wb_cbc_crp"].ToString()));
list.Add(new KeyValueModel("全血-CRP", string.IsNullOrEmpty(dt.Rows[0]["count_times_wb_crp"].ToString()) ? "0" : dt.Rows[0]["count_times_wb_crp"].ToString()));
list.Add(new KeyValueModel("预稀释-CBC", string.IsNullOrEmpty(dt.Rows[0]["count_times_pd_cbc"].ToString()) ? "0" : dt.Rows[0]["count_times_pd_cbc"].ToString()));
list.Add(new KeyValueModel("预稀释-CBC+CRP", string.IsNullOrEmpty(dt.Rows[0]["count_times_pd_cbc_crp"].ToString()) ? "0" : dt.Rows[0]["count_times_pd_cbc_crp"].ToString()));
list.Add(new KeyValueModel("预稀释-CRP", string.IsNullOrEmpty(dt.Rows[0]["count_times_pd_crp"].ToString()) ? "0" : dt.Rows[0]["count_times_pd_crp"].ToString()));
}
else
{
list.Add(new KeyValueModel("全血-CBC", "0"));
list.Add(new KeyValueModel("全血-CBC+CRP", "0"));
list.Add(new KeyValueModel("全血-CRP", "0"));
list.Add(new KeyValueModel("预稀释-CBC", "0"));
list.Add(new KeyValueModel("预稀释-CBC+CRP", "0"));
list.Add(new KeyValueModel("预稀释-CRP", "0"));
}
//构造成Json的格式传递
return(JsonConvert.SerializeObject(list).Replace("null", "0"));
}
public int getDeviceCount(QueryConditionModel conditValue)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT count(1) FROM device_info d ";
if (!string.IsNullOrEmpty(conditValue.DeviceType))
{
whereSql.Append(@" AND d.DeviceType='" + conditValue.DeviceType + "'");
}
else
{
whereSql.Append(@" AND d.DeviceType ='血液细胞分析仪'");
}
if (!string.IsNullOrEmpty(conditValue.ProductSeries))
{
whereSql.Append(@" AND d.ProductSeries='" + conditValue.ProductSeries + "'");
}
if (!string.IsNullOrEmpty(conditValue.ModelType))
{
whereSql.Append(@" AND d.ProductModel='" + conditValue.ModelType + "'");
}
if (!string.IsNullOrEmpty(whereSql.ToString()))
{
sql += " WHERE " + whereSql.ToString().Substring(4);
}
using (var conn = new MySqlConnection(Global.strConn))
{
return(conn.QuerySingle <int>(sql));
}
}
public int getPoctAllCount(QueryConditionModel conditValue)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT count(cn) FROM(SELECT count(1)cn FROM (SELECT DISTINCT d.SN,d.Model,p.card_name FROM device_info d,poct_item p WHERE d.DeviceType='POCT'
AND d.SN = p.device_sn {0} )t LEFT OUTER JOIN poct_statistics_item c ON t.SN = c.device_sn GROUP BY t.Model,t.card_name)m ";
if (!string.IsNullOrEmpty(conditValue.Model))
{
whereSql.Append(@" AND d.Model='" + conditValue.Model + "'");
}
if (!string.IsNullOrEmpty(conditValue.Card))
{
whereSql.Append(@" AND p.card_name = '" + conditValue.Card + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtStart))
{
whereSql.Append(@" AND p.dtinsert>='" + conditValue.dtStart + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtEnd))
{
whereSql.Append(@" AND p.dtinsert<='" + conditValue.dtEnd + "'");
}
string SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
return(conn.QuerySingle <int>(SqlCondit));
}
}
public static string getDeviceList(string conditions, int rows, int page, string sort, string sortOrder)
{
if (rows == 0)
{
return("{\"total\":0,\"rows\":[]}");
}
PoctStatisticsBLL bll = new PoctStatisticsBLL();
PagerInfo pagerInfo = new PagerInfo();
pagerInfo.CurrenetPageIndex = page;
pagerInfo.PageSize = rows;
SortInfo sortInfo = new SortInfo(sort, sortOrder);
QueryConditionModel conditionModel = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
//List<QueryList> list = new List<QueryList>();
DataTable dt = bll.PoctStatisticsByAreaBLL(conditionModel, pagerInfo, sortInfo);
pagerInfo.RecordCount = bll.getAreaPoctDeviceCount(conditionModel);
//Json格式的要求{total:22,rows:{}}
//构造成Json的格式传递
var result = new { total = pagerInfo.RecordCount, rows = dt };
return(JsonConvert.SerializeObject(result).Replace("null", "\"\""));
}
public DataTable StatisticsAllPoctDAL(QueryConditionModel conditValue, PagerInfo pagerInfo, SortInfo sortInfo)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT t.Model,t.card_name,SUM(c.smpl) AS smpl, SUM(c.card_consume) AS card_consume FROM (SELECT DISTINCT d.SN,d.Model,p.card_name FROM device_info d,poct_item p WHERE d.DeviceType='POCT'
AND d.SN = p.device_sn {0} )t LEFT OUTER JOIN poct_statistics_item c ON t.SN = c.device_sn group by t.Model,t.card_name ORDER BY t.Model
LIMIT " + (pagerInfo.PageSize * (pagerInfo.CurrenetPageIndex - 1)) + "," + pagerInfo.PageSize;;
if (!string.IsNullOrEmpty(conditValue.Model))
{
whereSql.Append(@" AND d.Model='" + conditValue.Model + "'");
}
if (!string.IsNullOrEmpty(conditValue.Card))
{
whereSql.Append(@" AND p.card_name = '" + conditValue.Card + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtStart))
{
whereSql.Append(@" AND p.dtinsert>='" + conditValue.dtStart + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtEnd))
{
whereSql.Append(@" AND p.dtinsert<='" + conditValue.dtEnd + "'");
}
string SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(SqlCondit, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds.Tables[0]);
}
}
public int getAreaBioDeviceCount(QueryConditionModel conditValue)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT COUNT(1) AS count FROM(SELECT d.Region,bc.num FROM device_info d
LEFT JOIN bio_statistics_item bc ON d.SN = bc.device_sn WHERE d.DeviceType ='生化仪' {0} GROUP BY Region)t; ";
if (!string.IsNullOrEmpty(conditValue.Model))
{
whereSql.Append(@" AND d.Model='" + conditValue.Model + "'");
}
if (!string.IsNullOrEmpty(conditValue.Region))
{
whereSql.Append(@" AND d.Region='" + conditValue.Region + "'");
}
if (!string.IsNullOrEmpty(conditValue.Num))
{
whereSql.Append(@" AND bc.num='" + conditValue.Num + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtStart))
{
whereSql.Append(@" AND bc.dtinsert>='" + conditValue.dtStart + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtEnd))
{
whereSql.Append(@" AND bc.dtinsert<='" + conditValue.dtEnd + "'");
}
var SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
return(conn.QuerySingle <int>(SqlCondit));
}
}
public DataTable PoctStatisticsByTypeForExportBLL(QueryConditionModel conditValue)
{
PagerInfo pagerInfo = new PagerInfo();
pagerInfo.CurrenetPageIndex = 1;
pagerInfo.PageSize = 65530;
return(dal.PoctStatisticsByTypeDAL(conditValue, pagerInfo, null));
}
public List <QueryList> GetDeviceInfoDAL(QueryConditionModel conditValue)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT d.DeviceName, d.SIM, d.SN,d.SESSIONID,d.ProductSeries, d.ProductModel
FROM device_info d
where 1=1 ";
if (!string.IsNullOrEmpty(conditValue.DeviceType))
{
whereSql.Append(@" AND d.DeviceType='" + conditValue.DeviceType + "'");
}
if (conditValue.DeviceState == MachineState.已连接仪器)
{
whereSql.Append(@" AND d.sessionid is not null");
}
else if (conditValue.DeviceState == MachineState.未连接仪器)
{
whereSql.Append(@" AND d.sessionid is null");
}
if (!string.IsNullOrEmpty(conditValue.ProductSeries))
{
whereSql.Append(@" AND d.ProductSeries='" + conditValue.ProductSeries + "'");
}
if (!string.IsNullOrEmpty(conditValue.ModelType))
{
whereSql.Append(@" AND d.ProductModel='" + conditValue.ModelType + "'");
}
if (!string.IsNullOrEmpty(conditValue.OEM))
{
whereSql.Append(@" AND d.OEM='" + conditValue.OEM + "'");
}
if (!string.IsNullOrEmpty(conditValue.Agent))
{
whereSql.Append(@" AND d.Agent='" + conditValue.Agent + "'");
}
if (!string.IsNullOrEmpty(conditValue.ReagentType))
{
whereSql.Append(@" AND d.ReagentType='" + conditValue.ReagentType + "'");
}
if (!string.IsNullOrEmpty(conditValue.Region))
{
whereSql.Append(@" AND d.Region='" + conditValue.Region + "'");
}
sql = sql + whereSql.ToString();
using (var conn = new MySqlConnection(Global.strConn))
{
return(conn.Query <QueryList>(sql).ToList());
}
}
public DataTable StatisticsAllBioDevicesForExportBLL(QueryConditionModel conditValue)
{
PagerInfo pagerInfo = new PagerInfo();
pagerInfo.CurrenetPageIndex = 1;
pagerInfo.PageSize = 65530;
return(dal.StatisticsAllBioDevicesDAL(conditValue, pagerInfo, null));
}
public DataTable PoctStatisticsByTypeDAL(QueryConditionModel conditValue, PagerInfo pagerInfo, SortInfo sortInfo)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT case d.MachineType when 0 then '标准机' when 1 then '招标机' else '其他' end AS MachineType,
COUNT(d.SN) device_count, SUM(bc.smpl) AS smpl, SUM(bc.card_consume) AS card_consume
FROM device_info d LEFT JOIN poct_statistics_item bc ON d.SN = bc.device_sn WHERE DeviceType ='POCT' {0}
GROUP BY MachineType ORDER BY SN LIMIT " + (pagerInfo.PageSize * (pagerInfo.CurrenetPageIndex - 1)) + "," + pagerInfo.PageSize + ";";
if (!string.IsNullOrEmpty(conditValue.Model))
{
whereSql.Append(@" AND d.Model='" + conditValue.Model + "'");
}
if (!string.IsNullOrEmpty(conditValue.ProductSeries) && conditValue.ProductSeries == "1")//标准机
{
whereSql.Append(@" AND d.MachineType=0");
}
else if (!string.IsNullOrEmpty(conditValue.ProductSeries) && conditValue.ProductSeries == "2")//招标机
{
whereSql.Append(@" AND d.MachineType=1");
}
else if (!string.IsNullOrEmpty(conditValue.ProductSeries) && conditValue.ProductSeries == "3")//其他
{
whereSql.Append(@" AND d.MachineType<>0 AND d.MachineType<>1");
}
if (!string.IsNullOrEmpty(conditValue.Card))
{
whereSql.Append(@" AND d.SN in(SELECT device_sn FROM poct_item WHERE card_name = '" + conditValue.Card + "')");
}
if (!string.IsNullOrEmpty(conditValue.dtStart))
{
whereSql.Append(@" AND bc.dtinsert>='" + conditValue.dtStart + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtEnd))
{
whereSql.Append(@" AND bc.dtinsert<='" + conditValue.dtEnd + "'");
}
var SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(SqlCondit, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds.Tables[0]);
}
}
/***********bio***********/
public DataTable StatisticsAllBioDevicesDAL(QueryConditionModel conditValue, PagerInfo pagerInfo, SortInfo sortInfo)
{
StringBuilder whereSql = new StringBuilder();
// string sql = @"SELECT d.DeviceName,d.dtupdate,d.SIM,d.SN,SUM(c.smpl) AS smpl, SUM(c.R1) AS R1, SUM(c.R2) AS R2
// FROM device_info d LEFT OUTER JOIN bio_statistics_item c
// ON d.SN = c.device_sn WHERE d.DeviceType='生化仪' {0} GROUP BY SN
// LIMIT " + (pagerInfo.PageSize * (pagerInfo.CurrenetPageIndex - 1)) + "," + pagerInfo.PageSize; ;
// if (!string.IsNullOrEmpty(conditValue.SN))
// {
string sql = @"SELECT d.DeviceName,c.dtinsert as dtupdate,d.SIM,d.SN,c.smpl, c.R1, c.R2
FROM device_info d LEFT OUTER JOIN bio_statistics_item c
ON d.SN = c.device_sn WHERE d.DeviceType='生化仪' {0} ORDER BY c.dtinsert DESC
LIMIT " + (pagerInfo.PageSize * (pagerInfo.CurrenetPageIndex - 1)) + "," + pagerInfo.PageSize;
//}
if (!string.IsNullOrEmpty(conditValue.Model))
{
whereSql.Append(@" AND d.Model='" + conditValue.Model + "'");
}
if (!string.IsNullOrEmpty(conditValue.SN))
{
whereSql.Append(@" AND d.SN='" + conditValue.SN + "'");
}
if (!string.IsNullOrEmpty(conditValue.Num))
{
whereSql.Append(@" AND c.num='" + conditValue.Num + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtStart))
{
whereSql.Append(@" AND c.dtinsert>='" + conditValue.dtStart + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtEnd))
{
whereSql.Append(@" AND c.dtinsert<='" + conditValue.dtEnd + "'");
}
string SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(SqlCondit, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds.Tables[0]);
}
}
public static string getDataList(string conditions, int rows)
{
if (rows == 0)
{
return("{\"total\":0,\"rows\":[]}");
}
StatisticsBLL bll = new StatisticsBLL();
QueryConditionModel conditionModel = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
DataTable dt = bll.StatisticsByReagentTypeBLL(conditionModel);
if (dt.Rows.Count < 2)
{
DataRow dr;
if (dt.Rows.Count == 0)
{
dr = dt.NewRow();
dr[0] = "open";
dr[1] = dr[2] = dr[3] = dr[4] = dr[5] = 0;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = "close";
dr[1] = dr[2] = dr[3] = dr[4] = dr[5] = 0;
dt.Rows.Add(dr);
}
else
{
if (dt.Rows[0][0].ToString().ToLower() == "close")
{
dr = dt.NewRow();
dr[0] = "open";
dr[1] = dr[2] = dr[3] = dr[4] = dr[5] = 0;
dt.Rows.InsertAt(dr, 0);
}
else if (dt.Rows[0][0].ToString().ToLower() == "open")
{
dr = dt.NewRow();
dr[0] = "close";
dr[1] = dr[2] = dr[3] = dr[4] = dr[5] = 0;
dt.Rows.Add(dr);
}
}
}
//构造成Json的格式传递
return(JsonConvert.SerializeObject(dt).Replace("null", "0"));
}
/// <summary>
/// 统计-按机型
/// </summary>
/// <param name="conditValue"></param>
/// <returns></returns>
public DataTable StatisticsByOEMDAL(QueryConditionModel conditValue, PagerInfo pagerInfo, SortInfo sortInfo)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT d.OEM, COUNT(d.SN) device_count, SUM(bc.count_times_total) AS count_times_total, SUM(br.reagent_dil) AS reagent_dil, SUM(br.reagent_lh) AS reagent_lh, SUM(br.reagent_r2) AS reagent_r2
FROM device_info d LEFT JOIN blood_count bc ON d.SN = bc.device_sn LEFT JOIN blood_reagent br ON d.SN = br.device_sn {0}
GROUP BY OEM ORDER BY SN LIMIT " + (pagerInfo.PageSize * (pagerInfo.CurrenetPageIndex - 1)) + "," + pagerInfo.PageSize + ";";
if (!string.IsNullOrEmpty(conditValue.DeviceType))
{
whereSql.Append(@" AND DeviceType='" + conditValue.DeviceType + "'");
}
else
{
whereSql.Append(@" AND DeviceType ='血液细胞分析仪'");
}
if (!string.IsNullOrEmpty(conditValue.ProductSeries))
{
whereSql.Append(@" AND ProductSeries='" + conditValue.ProductSeries + "'");
}
if (!string.IsNullOrEmpty(conditValue.ModelType))
{
whereSql.Append(@" AND ProductModel='" + conditValue.ModelType + "'");
}
if (!string.IsNullOrEmpty(whereSql.ToString()))
{
whereSql = new StringBuilder(" WHERE " + whereSql.ToString().Substring(4));
}
var SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(SqlCondit, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds.Tables[0]);
//return conn.Query<dynamic>(SqlCondit).ToList();
}
}
public int getTypePoctDeviceCount(QueryConditionModel conditValue)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT COUNT(1) AS count FROM(SELECT d.MachineType FROM device_info d
LEFT JOIN poct_statistics_item bc ON d.SN = bc.device_sn WHERE DeviceType ='POCT' {0} GROUP BY MachineType)t; ";
if (!string.IsNullOrEmpty(conditValue.Model))
{
whereSql.Append(@" AND d.Model='" + conditValue.Model + "'");
}
if (!string.IsNullOrEmpty(conditValue.ProductSeries) && conditValue.ProductSeries == "1")//标准机
{
whereSql.Append(@" AND d.MachineType=0");
}
else if (!string.IsNullOrEmpty(conditValue.ProductSeries) && conditValue.ProductSeries == "2")//招标机
{
whereSql.Append(@" AND d.MachineType=1");
}
else if (!string.IsNullOrEmpty(conditValue.ProductSeries) && conditValue.ProductSeries == "3")//其他
{
whereSql.Append(@" AND d.MachineType<>0 AND d.MachineType<>1");
}
if (!string.IsNullOrEmpty(conditValue.Card))
{
whereSql.Append(@" AND d.SN in(SELECT device_sn FROM poct_item WHERE card_name = '" + conditValue.Card + "')");
}
if (!string.IsNullOrEmpty(conditValue.dtStart))
{
whereSql.Append(@" AND bc.dtinsert>='" + conditValue.dtStart + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtEnd))
{
whereSql.Append(@" AND bc.dtinsert<='" + conditValue.dtEnd + "'");
}
var SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
return(conn.QuerySingle <int>(SqlCondit));
}
}
/// <summary>
/// 统计所有仪器
/// </summary>
/// <param name="conditValue"></param>
/// <param name="pagerInfo"></param>
/// <param name="sortInfo"></param>
/// <returns></returns>
public DataTable StatisticsAllDevicesDAL(QueryConditionModel conditValue, PagerInfo pagerInfo, SortInfo sortInfo)
{
StringBuilder whereSql = new StringBuilder();
var sql = @"SELECT IFNULL(d.DeviceName,'') AS DeviceName, d.SIM, d.SN, c.count_times_total, r.reagent_dil, r.reagent_lh, r.reagent_r2 FROM device_info d INNER JOIN ( SELECT SN FROM device_info {0} ";
sql += " LIMIT " + (pagerInfo.PageSize * (pagerInfo.CurrenetPageIndex - 1)) + "," + pagerInfo.PageSize;
sql += ") AS t USING (SN) LEFT JOIN blood_count c ON d.SN = c.device_sn LEFT JOIN blood_reagent r ON d.SN = r.device_sn;";
if (!string.IsNullOrEmpty(conditValue.DeviceType))
{
whereSql.Append(@" AND DeviceType='" + conditValue.DeviceType + "'");
}
else
{
whereSql.Append(@" AND DeviceType ='血液细胞分析仪'");
}
if (!string.IsNullOrEmpty(conditValue.ProductSeries))
{
whereSql.Append(@" AND ProductSeries='" + conditValue.ProductSeries + "'");
}
if (!string.IsNullOrEmpty(conditValue.ModelType))
{
whereSql.Append(@" AND ProductModel='" + conditValue.ModelType + "'");
}
if (!string.IsNullOrEmpty(whereSql.ToString()))
{
whereSql = new StringBuilder(" WHERE " + whereSql.ToString().Substring(4));
}
var SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(SqlCondit, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds.Tables[0]);
//return conn.Query<dynamic>(SqlCondit).ToList();
}
}
public int getPoctDeviceCount(QueryConditionModel conditValue)
{
StringBuilder whereSql = new StringBuilder();
// string sql = @"SELECT count(1) FROM (SELECT d.DeviceName,d.SIM,d.SN,SUM(c.smpl) AS smpl, SUM(c.card_consume) AS card_consume
// FROM device_info d LEFT OUTER JOIN poct_statistics_item c
// ON d.SN = c.device_sn WHERE d.DeviceType='POCT' {0} GROUP BY SN)t ";
// if (!string.IsNullOrEmpty(conditValue.SN))
// {
string sql = @"SELECT count(1) FROM (SELECT c.dtinsert,d.SN FROM device_info d,poct_item p,poct_statistics_item c WHERE d.DeviceType='POCT' {0}
AND d.SN=p.device_sn AND d.SN=c.device_sn)t ";
//}
if (!string.IsNullOrEmpty(conditValue.Model))
{
whereSql.Append(@" AND d.Model='" + conditValue.Model + "'");
}
if (!string.IsNullOrEmpty(conditValue.SN))
{
whereSql.Append(@" AND d.SN = '" + conditValue.SN + "'");
}
if (!string.IsNullOrEmpty(conditValue.Card))
{
whereSql.Append(@" AND p.card_name = '" + conditValue.Card + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtStart))
{
whereSql.Append(@" AND c.dtinsert>='" + conditValue.dtStart + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtEnd))
{
whereSql.Append(@" AND c.dtinsert<='" + conditValue.dtEnd + "'");
}
string SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
return(conn.QuerySingle <int>(SqlCondit));
}
}
public int getBioDeviceCount(QueryConditionModel conditValue)
{
StringBuilder whereSql = new StringBuilder();
// string sql = @"SELECT count(1) FROM (SELECT d.DeviceName,d.SIM,d.SN,SUM(c.smpl) AS smpl, SUM(c.R1) AS R1, SUM(c.R2) AS R2
// FROM device_info d LEFT OUTER JOIN bio_statistics_item c
// ON d.SN = c.device_sn WHERE d.DeviceType='生化仪' {0} GROUP BY SN)t ";
// if (!string.IsNullOrEmpty(conditValue.SN))
// {
string sql = @"SELECT count(1) FROM (SELECT c.dtinsert,d.SN FROM device_info d LEFT OUTER JOIN bio_statistics_item c
ON d.SN = c.device_sn WHERE d.DeviceType='生化仪' {0} )t " ;
//}
if (!string.IsNullOrEmpty(conditValue.Model))
{
whereSql.Append(@" AND d.Model='" + conditValue.Model + "'");
}
if (!string.IsNullOrEmpty(conditValue.SN))
{
whereSql.Append(@" AND d.SN='" + conditValue.SN + "'");
}
if (!string.IsNullOrEmpty(conditValue.Num))
{
whereSql.Append(@" AND c.num='" + conditValue.Num + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtStart))
{
whereSql.Append(@" AND c.dtinsert>='" + conditValue.dtStart + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtEnd))
{
whereSql.Append(@" AND c.dtinsert<='" + conditValue.dtEnd + "'");
}
string SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
return(conn.QuerySingle <int>(SqlCondit));
}
}
/// <summary>
/// 按封闭试剂类型统计
/// </summary>
/// <param name="conditValue"></param>
/// <returns></returns>
public DataTable StatisticsByReagentTypeDAL(QueryConditionModel conditValue)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT d.ReagentType, COUNT(d.SN) device_count, SUM(bc.count_times_total) AS count_times_total, SUM(br.reagent_dil) AS reagent_dil, SUM(br.reagent_lh) AS reagent_lh, SUM(br.reagent_r2) AS reagent_r2
FROM device_info d LEFT JOIN blood_count bc ON d.SN = bc.device_sn LEFT JOIN blood_reagent br ON d.SN = br.device_sn
WHERE reagenttype != '' AND reagenttype IS NOT NULL {0}
GROUP BY ReagentType ORDER BY ReagentType DESC;";
if (!string.IsNullOrEmpty(conditValue.DeviceType))
{
whereSql.Append(@" AND DeviceType='" + conditValue.DeviceType + "'");
}
else
{
whereSql.Append(@" AND DeviceType ='血液细胞分析仪'");
}
if (!string.IsNullOrEmpty(conditValue.ProductSeries))
{
whereSql.Append(@" AND ProductSeries='" + conditValue.ProductSeries + "'");
}
if (!string.IsNullOrEmpty(conditValue.ModelType))
{
whereSql.Append(@" AND ProductModel='" + conditValue.ModelType + "'");
}
var SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(SqlCondit, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds.Tables[0]);
//return conn.Query<dynamic>(SqlCondit).ToList();
}
}
/// <summary>
/// 根据条件绘图
/// </summary>
/// <param name="allArv"></param>
/// <param name="condition"></param>
private void DrawChart(string condition, string ChartType)
{
List <DataMap <string> > bindingList = new List <DataMap <string> >();
if (condition == "GroupNo")
{
// List<ArvLocationDto> loc = CallerFactory.Instance.GetService<IArvOpService>().FindArvLocations(null);
// bindingList = loc.GroupBy(q => q.GetType().GetProperty(condition).GetValue(q, null)).Select(q => new DataMap<string> { Key = q.Key.ToString(), Value = q.Count() }).ToList();
}
else
{
QueryConditionModel model = new QueryConditionModel(condition);
bindingList = CallerFactory.Instance.GetService <IArvOpService>().GetGroupCount <string>(null, model);
}
chart.Series.Clear();
if (bindingList != null)
{
if (ChartType == "柱形图")
{
DrawBar(bindingList);
}
else if (ChartType == "折线图")
{
DrawLine(bindingList);
}
else
{
DrawPie(bindingList);
}
}
gcCount.DataSource = bindingList;
colValue.SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
colValue.SummaryItem.DisplayFormat = "档案总数为：{0}";
}
public DataTable BioStatisticsByAreaDAL(QueryConditionModel conditValue, PagerInfo pagerInfo, SortInfo sortInfo)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT d.Region, COUNT(d.SN) device_count, SUM(bc.smpl) AS smpl, SUM(bc.R1) AS R1, SUM(bc.R2) AS R2
FROM device_info d LEFT JOIN bio_statistics_item bc ON d.SN = bc.device_sn WHERE d.DeviceType ='生化仪' {0}
GROUP BY Region ORDER BY SN LIMIT " + (pagerInfo.PageSize * (pagerInfo.CurrenetPageIndex - 1)) + "," + pagerInfo.PageSize + ";";
if (!string.IsNullOrEmpty(conditValue.Model))
{
whereSql.Append(@" AND d.Model='" + conditValue.Model + "'");
}
if (!string.IsNullOrEmpty(conditValue.Region))
{
whereSql.Append(@" AND d.Region='" + conditValue.Region + "'");
}
if (!string.IsNullOrEmpty(conditValue.Num))
{
whereSql.Append(@" AND bc.num='" + conditValue.Num + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtStart))
{
whereSql.Append(@" AND bc.dtinsert>='" + conditValue.dtStart + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtEnd))
{
whereSql.Append(@" AND bc.dtinsert<='" + conditValue.dtEnd + "'");
}
var SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(SqlCondit, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds.Tables[0]);
}
}
//public async Task SearchImageOfSeries(string studyUID, string seriesUID)
//{
// var client = new DicomClient();
// client.NegotiateAsyncOps();
// var cGetRequest = CreateCGetBySeriesUID(studyUID, seriesUID);
// cGetRequest.OnResponseReceived += (req, response) =>
// {
// Console.WriteLine(response.Status);
// };
// client.OnCStoreRequest += req =>
// {
// Console.WriteLine(DateTime.Now.ToString() + " recived");
// SaveImage(req.Dataset);
// return new DicomCStoreResponse(req, DicomStatus.Success);
// };
// var pcs = DicomPresentationContext.GetScpRolePresentationContextsFromStorageUids(
// DicomStorageCategory.Image,
// DicomTransferSyntax.ExplicitVRLittleEndian,
// DicomTransferSyntax.ImplicitVRLittleEndian,
// DicomTransferSyntax.ImplicitVRBigEndian);
// client.AdditionalPresentationContexts.AddRange(pcs);
// client.AddRequest(cGetRequest);
// await client.SendAsync(_pacsNode.Host, _pacsNode.Port, false, AET, _pacsNode.AET);
//}
private DicomCFindRequest CreateStudyRequest(QueryConditionModel condition)
{
// there is a built in function to create a Study-level CFind request very easily:
// return DicomCFindRequest.CreateStudyQuery(patientName: patientName);
// but consider to create your own request that contains exactly those DicomTags that
// you realy need pro process your data and not to cause unneccessary traffic and IO load:
var request = new DicomCFindRequest(DicomQueryRetrieveLevel.Study);
// Always add the encoding
request.Dataset.Add(new DicomTag(0x8, 0x5), "ISO_IR 100");
// Add the dicom tags with empty values that should be included in the result of QR Server.
request.Dataset.Add(DicomTag.PatientName, condition.PatientName)
.Add(DicomTag.PatientID, condition.PatientID)
.Add(DicomTag.ModalitiesInStudy, condition.Modality)
.Add(DicomTag.StudyDate, "")
.Add(DicomTag.StudyInstanceUID, "")
.Add(DicomTag.StudyDescription, condition.StudyDescription);
return(request);
}
public static string getDeviceList(string conditions)
{
StatisticsBLL bll = new StatisticsBLL();
QueryConditionModel conditionModel = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
var dt = bll.StatisticsByReagentTypeBLL(conditionModel);
var deivceTotal = Convert.ToInt32(dt.Compute("sum(机器数)", "true"));
var countTimesTotal = Convert.ToInt32(dt.Compute("sum(样本数)", "true"));
for (int i = 0; i < dt.Rows.Count; i++)
{
if (deivceTotal > 0)
{
dt.Rows[i]["机器数比例"] = Math.Round((double)(Convert.ToInt32(dt.Rows[i]["机器数"]) / deivceTotal * 100), 2) + "%";
}
if (countTimesTotal > 0)
{
dt.Rows[i]["样本数比例"] = Math.Round((double)(Convert.ToInt32(dt.Rows[i]["样本数"]) / deivceTotal * 100), 2) + "%";
}
}
return(JsonConvert.SerializeObject(dt).Replace("null", "\"\""));
}
public DataTable StatisticsByModelBLL(QueryConditionModel conditValue)
{
StringBuilder whereSql = new StringBuilder();
var sql = @"SELECT SUM(count_times_wb_cbc) AS count_times_wb_cbc,SUM(count_times_wb_cbc_crp) AS count_times_wb_cbc_crp,SUM(count_times_wb_crp) AS count_times_wb_crp,
SUM(count_times_pd_cbc) AS count_times_pd_cbc,SUM(count_times_pd_cbc_crp) AS count_times_pd_cbc_crp,SUM(count_times_pd_crp) AS count_times_pd_crp
FROM device_info,blood_count WHERE SN = device_sn {0}";
if (!string.IsNullOrEmpty(conditValue.DeviceType))
{
whereSql.Append(@" AND DeviceType='" + conditValue.DeviceType + "'");
}
else
{
whereSql.Append(@" AND DeviceType ='血液细胞分析仪'");
}
if (!string.IsNullOrEmpty(conditValue.ProductSeries))
{
whereSql.Append(@" AND ProductSeries='" + conditValue.ProductSeries + "'");
}
if (!string.IsNullOrEmpty(conditValue.ModelType))
{
whereSql.Append(@" AND ProductModel='" + conditValue.ModelType + "'");
}
var SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(SqlCondit, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds.Tables[0]);
//return conn.Query<dynamic>(SqlCondit).ToList();
}
}
public static string getDeviceList(string conditions, int rows, int page, string sort, string sortOrder)
{
if (rows == 0)
{
return("{\"total\":0,\"rows\":[]}");
}
DeviceQueryBLL bll = new DeviceQueryBLL();
PagerInfo pagerInfo = new PagerInfo();
pagerInfo.CurrenetPageIndex = page;
pagerInfo.PageSize = rows;
SortInfo sortInfo = new SortInfo(sort, sortOrder);
QueryConditionModel conditionModel = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
if (conditionModel.QueryRange == "1")
{
conditionModel.DeviceState = MachineState.已连接仪器;
}
else if (conditionModel.QueryRange == "2")
{
conditionModel.DeviceState = MachineState.未连接仪器;
}
List <QueryList> list = new List <QueryList>();
list = bll.GetDeviceInfoBLL(conditionModel, pagerInfo, sortInfo);
pagerInfo.RecordCount = bll.getDeviceCount(conditionModel);
//Json格式的要求{total:22,rows:{}}
//构造成Json的格式传递
var result = new { total = pagerInfo.RecordCount, rows = list };
return(JsonConvert.SerializeObject(result).Replace("null", "\"\""));
}
public int getOEMDeviceCount(QueryConditionModel conditValue)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT COUNT(1) AS count FROM(SELECT OEM FROM device_info d {0} GROUP BY OEM)t; ";
if (!string.IsNullOrEmpty(conditValue.DeviceType))
{
whereSql.Append(@" AND d.DeviceType='" + conditValue.DeviceType + "'");
}
else
{
whereSql.Append(@" AND d.DeviceType ='血液细胞分析仪'");
}
if (!string.IsNullOrEmpty(conditValue.ProductSeries))
{
whereSql.Append(@" AND d.ProductSeries='" + conditValue.ProductSeries + "'");
}
if (!string.IsNullOrEmpty(conditValue.ModelType))
{
whereSql.Append(@" AND d.ProductModel='" + conditValue.ModelType + "'");
}
if (!string.IsNullOrEmpty(whereSql.ToString()))
{
whereSql = new StringBuilder(" WHERE " + whereSql.ToString().Substring(4));
}
var SqlCondit = string.Format(sql, whereSql.ToString());
using (var conn = new MySqlConnection(Global.strConn))
{
return(conn.QuerySingle <int>(SqlCondit));
}
}
public int getPoctAllCount(QueryConditionModel conditValue)
{
return(dal.getPoctAllCount(conditValue));
}
public DataTable StatisticsAllPoctBLL(QueryConditionModel conditValue, PagerInfo pagerInfo, SortInfo sortInfo)
{
return(dal.StatisticsAllPoctDAL(conditValue, pagerInfo, sortInfo));
}
public int getTypePoctDeviceCount(QueryConditionModel conditValue)
{
return(dal.getTypePoctDeviceCount(conditValue));
}
public DataTable PoctStatisticsByTypeBLL(QueryConditionModel conditValue, PagerInfo pagerInfo, SortInfo sortInfo)
{
return(dal.PoctStatisticsByTypeDAL(conditValue, pagerInfo, sortInfo));
}
/// <summary>
/// 统计-按OEM
/// </summary>
/// <param name="conditValue"></param>
/// <param name="pagerInfo"></param>
/// <param name="sortInfo"></param>
/// <returns></returns>
public DataTable StatisticsByOEMBLL(QueryConditionModel conditValue, PagerInfo pagerInfo, SortInfo sortInfo)
{
//conditValue.ProductSeries = Common.UIdataToDB(conditValue.ProductSeries);
return(dal.StatisticsByOEMDAL(conditValue, pagerInfo, sortInfo));
}
