public static string getLogList(string conditions, int rows, int page, string sort, string sortOrder)
{
if (rows == 0)
{
return("{\"total\":0,\"rows\":[]}");
}
StatisticsBLL bll = new StatisticsBLL();
PagerInfo pagerInfo = new PagerInfo();
pagerInfo.CurrenetPageIndex = page;
pagerInfo.PageSize = rows;
SortInfo sortInfo = new SortInfo(sort, sortOrder);
LogConditionModel conditionModel = JsonConvert.DeserializeObject <LogConditionModel>(conditions.Replace("\"0\"", "\"\""));
DataTable dt = bll.StatisticsLogsBLL(conditionModel, pagerInfo, sortInfo);
pagerInfo.RecordCount = bll.getLogCount(conditionModel);
//Json格式的要求{total:22,rows:{}}
//构造成Json的格式传递
var result = new { total = pagerInfo.RecordCount, rows = dt };
return(JsonConvert.SerializeObject(result).Replace("null", "\"\""));
}
public DataTable StatisticsLogsForExportBLL(LogConditionModel conditValue)
{
PagerInfo pagerInfo = new PagerInfo();
pagerInfo.CurrenetPageIndex = 1;
pagerInfo.PageSize = 65530;
return(dal.StatisticsLogsDAL(conditValue, pagerInfo, null));
}
public int getLogCount(LogConditionModel conditValue)
{
return(dal.getLogCount(conditValue));
}
public DataTable StatisticsLogsBLL(LogConditionModel conditValue, PagerInfo pagerInfo, SortInfo sortInfo)
{
return(dal.StatisticsLogsDAL(conditValue, pagerInfo, sortInfo));
}
public int getLogCount(LogConditionModel conditValue)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT COUNT(1) AS count FROM(SELECT dl.device_sn FROM device_log dl LEFT JOIN device_info d ON dl.device_sn = d.SN {0})t; ";
if (!string.IsNullOrEmpty(conditValue.DeviceType))
{
whereSql.Append(@" AND DeviceType='" + conditValue.DeviceType + "'");
}
else
{
whereSql.Append(@" AND DeviceType ='生化仪'");
}
if (!string.IsNullOrEmpty(conditValue.Model))
{
whereSql.Append(@" AND Model='" + conditValue.Model + "'");
}
if (!string.IsNullOrEmpty(conditValue.Region))
{
whereSql.Append(@" AND Region='" + conditValue.Region + "'");
}
if (!string.IsNullOrEmpty(conditValue.DeviceName))
{
whereSql.Append(@" AND DeviceName='" + conditValue.DeviceName + "'");
}
if (!string.IsNullOrEmpty(conditValue.SIM))
{
whereSql.Append(@" AND SIM='" + conditValue.SIM + "'");
}
if (!string.IsNullOrEmpty(conditValue.SN))
{
whereSql.Append(@" AND SN='" + conditValue.SN + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtStart))
{
whereSql.Append(@" AND dtInsert >='" + conditValue.dtStart + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtEnd))
{
whereSql.Append(@" AND dtInsert <='" + conditValue.dtEnd + "'");
}
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 DataTable StatisticsLogsDAL(LogConditionModel conditValue, PagerInfo pagerInfo, SortInfo sortInfo)
{
StringBuilder whereSql = new StringBuilder();
string sql = @"SELECT d.DeviceName,d.SIM,d.SN,dl.dtinsert,dl.content
FROM device_log dl LEFT JOIN device_info d ON dl.device_sn = d.SN {0}
ORDER BY dl.dtinsert DESC, dl.id 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.Model))
{
whereSql.Append(@" AND Model='" + conditValue.Model + "'");
}
if (!string.IsNullOrEmpty(conditValue.Region))
{
whereSql.Append(@" AND Region='" + conditValue.Region + "'");
}
if (!string.IsNullOrEmpty(conditValue.DeviceName))
{
whereSql.Append(@" AND DeviceName='" + conditValue.DeviceName + "'");
}
if (!string.IsNullOrEmpty(conditValue.SIM))
{
whereSql.Append(@" AND SIM='" + conditValue.SIM + "'");
}
if (!string.IsNullOrEmpty(conditValue.SN))
{
whereSql.Append(@" AND SN='" + conditValue.SN + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtStart))
{
whereSql.Append(@" AND dtInsert >='" + conditValue.dtStart + "'");
}
if (!string.IsNullOrEmpty(conditValue.dtEnd))
{
whereSql.Append(@" AND dtInsert <='" + conditValue.dtEnd + "'");
}
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]);
}
}
public void ProcessRequest(HttpContext context)
{
//context.Response.ContentType = "text/plain";
//context.Response.Write("Hello World");
string message = "";
string Action = context.Request["Action"];
//params
string sn = context.Request["sn"] ?? "";
string model = context.Request["model"] ?? "";
if (model.Trim() == "0")
{
model = "";
}
string conditions = context.Request["conditions"] ?? "";
DeviceQueryBLL bll = new DeviceQueryBLL();
StatisticsBLL staticBll = new StatisticsBLL();
PoctStatisticsBLL poctStaticBll = new PoctStatisticsBLL();
QueryConditionModel condition;
DataTable dt = new DataTable();
string fileName = "";
string[] headers = new string[] { };
switch (Action)
{
case "fault":
dt = bll.GetDeviceFaultForExportBLL(sn);//获取导出数据源
fileName = "错误信息";
headers = new string[] { "错误码", "时间" };
break;
case "yangben":
dt = bll.GetDeviceSampleForExportBLL();//获取导出数据源
fileName = "样本信息";
headers = new string[] { "system_seq", "仪器序列号", "计数值", "计数类型", "时间" };
break;
case "cuowu":
dt = bll.GetDeviceFaultForExportBLL();//获取导出数据源
fileName = "错误信息";
headers = new string[] { "id", "仪器序列号", "错误码", "时间" };
break;
case "bio_fault":
condition = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
dt = bll.GetBioDeviceFaultBLL(condition.SN, condition.dtStart, condition.dtEnd);
fileName = "生化仪统计_故障信息";
headers = new string[] { "错误码", "时间" };
break;
case "bio_all":
condition = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
dt = staticBll.StatisticsAllBioDevicesForExportBLL(condition);
fileName = "生化仪统计_所有机器";
headers = new string[] { "仪器名称", "上报时间", "SIM卡号", "仪器序列号", "样本数", "R1试剂使用量(mL)", "R2试剂使用量(mL)" };
break;
case "bio_area":
condition = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
dt = staticBll.BioStatisticsByAreaForExportBLL(condition);
fileName = "生化仪统计_按区域";
headers = new string[] { "装机区域", "仪器总数", "样本数", "R1试剂使用量(mL)", "R2试剂使用量(mL)" };
break;
case "bio_type":
condition = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
dt = staticBll.BioStatisticsByTypeForExportBLL(condition);
fileName = "生化仪统计_按机型";
headers = new string[] { "机型", "仪器总数", "样本数", "R1试剂使用量(mL)", "R2试剂使用量(mL)" };
break;
case "poct_fault":
condition = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
dt = bll.GetPoctDeviceFaultBLL(condition.SN, condition.dtStart, condition.dtEnd);
fileName = "POCT统计_故障信息";
headers = new string[] { "错误码", "时间" };
break;
case "poct_all":
condition = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
dt = poctStaticBll.StatisticsAllPoctDevicesForExportBLL(condition);
fileName = "POCT统计_所有机器";
headers = new string[] { "仪器名称", "上报时间", "SIM卡号", "仪器序列号", "样本数", "测试卡消耗数" };
break;
case "poct_area":
condition = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
dt = poctStaticBll.PoctStatisticsByAreaForExportBLL(condition);
fileName = "POCT统计_按区域";
headers = new string[] { "装机区域", "仪器总数", "样本数", "测试卡消耗数" };
break;
case "poct_type":
condition = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
dt = poctStaticBll.PoctStatisticsByTypeForExportBLL(condition);
fileName = "POCT统计_按机型";
headers = new string[] { "机型", "仪器总数", "样本数", "测试卡消耗数" };
break;
case "poct_all_num":
condition = JsonConvert.DeserializeObject <QueryConditionModel>(conditions.Replace("\"0\"", "\"\""));
dt = poctStaticBll.StatisticsAllPoctNumForExportBLL(condition);
fileName = "POCT统计_总量统计";
headers = new string[] { "仪器型号", "测试项目", "样本数", "测试卡消耗数" };
break;
case "bio_log":
LogConditionModel conditionModel = JsonConvert.DeserializeObject <LogConditionModel>(conditions.Replace("\"0\"", "\"\""));
dt = staticBll.StatisticsLogsForExportBLL(conditionModel);
fileName = "日志查询";
headers = new string[] { "仪器名称", "SIM卡号", "仪器序列号", "发生时间", "日志内容" };
break;
}
ExportExcel(context, dt, headers, fileName + sn + "_" + DateTime.Now.ToString("yyyyMMddHHmmss"));
context.Response.Write(message);
}
