/// <summary> /// 根据条件 获取项目台账数据集合 /// </summary> /// <param name="param"></param> /// <returns></returns> public List <ItemPreserveModel> GetInitData(ItemPreserveParam param) { sb?.Clear(); sb.Append(string.Format(@"SELECT TOP {0} tt.*,code.ip_status_text,record.tail_after FROM (SELECT ROW_NUMBER() OVER(ORDER BY ip_id) AS RowNumber, ip_id, unit_name, unit_code, ip_status, area_code, pact_money, pay_money, CONVERT(varchar(100), pay_date, 23) AS pay_date, pay_cash_money, pra_cash_money, duty_person, touch_phone, CONVERT(varchar(100), pact_start, 23) AS pact_start, record_name, CONVERT(varchar(100), update_date, 23) AS update_date, user_note FROM BB_ItemPreserve WHERE 1=1 ", param.rows)); _param?.Clear(); if (!string.IsNullOrEmpty(param.unit_code)) { sb.Append(" AND unit_code = @unit_code "); _param.Add("@unit_code", param.unit_code); } else if (!string.IsNullOrEmpty(param.area_code)) { sb.Append(" AND area_code = @area_code "); _param.Add("@area_code", param.area_code); } else { sb.Append(" AND 1=0 "); } if (!string.IsNullOrEmpty(param.status)) { sb.Append(" AND ip_status = @status "); _param.Add("@status", param.status); } sb.Append(string.Format(@" ) tt LEFT JOIN (SELECT DATEDIFF(dd,tt.track_date,GETDATE()) AS tail_after,tt.unit_code FROM (SELECT ROW_NUMBER() OVER(PARTITION BY unit_code ORDER BY track_date ASC) AS RowNumber,unit_code,track_date FROM BB_TrackRecord WHERE tr_status='02') AS tt WHERE tt.RowNumber=1) record ON tt.unit_code=record.unit_code LEFT JOIN (SELECT item1.*,item2.* FROM (SELECT item_id, item_code FROM dbo.T_ItemCode WHERE item_code = 'ItemStatus') item1 INNER JOIN (SELECT code_name AS ip_status_text, code_value AS ip_status, item_id AS id FROM dbo.T_ItemCodeMenum) item2 ON item1.item_id = item2.id) code ON tt.ip_status = code.ip_status WHERE tt.RowNumber > {0} ORDER BY tt.RowNumber;", (param.page - 1) * param.rows)); DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, SqlHelper.GetParameters(_param)); return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <ItemPreserveModel>(dt)); }
/// <summary> /// 根据条件 获取项目台账记录总数 /// </summary> /// <param name="param"></param> /// <returns></returns> public int GetCountData(ItemPreserveParam param) { sb?.Clear(); sb.Append(string.Format("SELECT COUNT(*) FROM BB_ItemPreserve WHERE 1=1 ")); _param?.Clear(); if (!string.IsNullOrEmpty(param.unit_code)) { sb.Append(" AND unit_code = @unit_code "); _param.Add("@unit_code", param.unit_code); } else if (!string.IsNullOrEmpty(param.area_code)) { sb.Append(" AND area_code = @area_code "); _param.Add("@area_code", param.area_code); } else { sb.Append(" AND 1=0 "); } if (!string.IsNullOrEmpty(param.status)) { sb.Append(" AND ip_status = @status "); _param.Add("@status", param.status); } return(Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text, SqlHelper.GetParameters(_param)))); }
/// <summary> /// 根据条件 获取项目台账记录总数 /// </summary> /// <param name="param"></param> /// <returns></returns> public int GetCountData(ItemPreserveParam param) { if (null != param) { return(DBSession.IBB_FacilityPreserveDAL.GetCountData(param)); } return(0); }
/// <summary> /// 根据条件 获取设备数据集合 /// </summary> /// <param name="param"></param> /// <returns></returns> public List <FacilityPreserveModel> GetInitData(ItemPreserveParam param) { if (null != param) { return(DBSession.IBB_FacilityPreserveDAL.GetInitData(param)); } return(null); }
/// <summary> /// 根据条件 获取项目台账记录总数 /// </summary> /// <param name="param"></param> /// <returns></returns> public int GetCountData(ItemPreserveParam param) { sb?.Clear(); sb.Append(string.Format("SELECT COUNT(*) FROM (SELECT fa_number FROM BB_FacilityPreserve WHERE 1=1 ")); _param?.Clear(); if (!string.IsNullOrEmpty(param.unit_code)) { sb.Append(" AND unit_code = @unit_code "); _param.Add("@unit_code", param.unit_code); } else if (!string.IsNullOrEmpty(param.area_code)) { sb.Append(" AND area_code = @area_code "); _param.Add("@area_code", param.area_code); } else { sb.Append(" AND 1=0 "); } //安装人 if (!string.IsNullOrEmpty(param.install_name)) { sb.Append(string.Format(" AND install_name LIKE '%{0}%' ", param.install_name)); } //技术支持 if (!string.IsNullOrEmpty(param.skiller)) { sb.Append(string.Format(" AND skiller LIKE '%{0}%' ", param.skiller)); } sb.Append(string.Format(@") tt LEFT JOIN (SELECT t.deviceid,t.sy_date FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY deviceid ORDER BY sy_date DESC) AS number FROM T_Synchronous WHERE 1=1 ")); if (!string.IsNullOrEmpty(param.unit_code)) { sb.Append(" AND sy_unit_id = @sy_unit_id "); _param.Add("@sy_unit_id", param.unit_code); } sb.Append(string.Format(@" ) t WHERE t.number=1) ylong ON tt.fa_number=ylong.deviceid WHERE 1=1 ")); if (!string.IsNullOrEmpty(param.status)) { //正常 if (param.status == "1") { sb.Append(" AND DATEDIFF(mi,ylong.sy_date,GETDATE())<=30 "); } else { sb.Append(" AND (DATEDIFF(mi,ylong.sy_date,GETDATE())>30 OR ISNULL(ylong.sy_date,'')='')"); } } return(Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text, SqlHelper.GetParameters(_param)))); }
public ActionResult InitTable() { string area_code = RequestHelper.GetStrByName("area_code"), //区域代码 unit_code = RequestHelper.GetStrByName("unit_code"), //项目代码 status = RequestHelper.GetDeStrByName("status"); //状态 int page = RequestHelper.GetIntByName("page"), rows = RequestHelper.GetIntByName("rows"); ItemPreserveParam param = new ItemPreserveParam { rows = rows, page = page, unit_code = unit_code, area_code = area_code, status = status }; List <ItemPreserveModel> list = operateContext.bllSession.BB_ItemPreserve.GetInitData(param); TableModel tModel = new TableModel() { total = operateContext.bllSession.BB_ItemPreserve.GetCountData(param), rows = list }; return(Json(tModel, JsonRequestBehavior.AllowGet)); }
public ActionResult InitTable() { string area_code = RequestHelper.GetStrByName("area_code"), //区域代码 unit_code = RequestHelper.GetStrByName("unit_code"), //项目代码 facityStatus = RequestHelper.GetDeStrByName("facityStatus"), //设备状态 installName = RequestHelper.GetDeStrByName("installName"), //安装人 skillName = RequestHelper.GetDeStrByName("skillName"); //技术支持 int page = RequestHelper.GetIntByName("page"), rows = RequestHelper.GetIntByName("rows"); ItemPreserveParam param = new ItemPreserveParam { rows = rows, page = page, unit_code = unit_code, area_code = area_code, status = facityStatus, install_name = installName, skiller = skillName }; List <FacilityPreserveModel> list = operateContext.bllSession.BB_FacilityPreserve.GetInitData(param); TableModel tModel = new TableModel() { total = operateContext.bllSession.BB_FacilityPreserve.GetCountData(param), rows = list }; return(Json(tModel, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 根据条件 获取设备数据集合 /// </summary> /// <param name="param"></param> /// <returns></returns> public List <FacilityPreserveModel> GetInitData(ItemPreserveParam param) { sb?.Clear(); sb.Append(string.Format(@"SELECT TOP {0} * FROM (SELECT tt.*,ROW_NUMBER() OVER(ORDER BY fp_id) AS RowNumber,CAST(DATEDIFF(day,track.track_date,GETDATE()) AS NVARCHAR(10)) AS tail_after,ylong.deviceid,(CASE WHEN (ylong.preDate>30 or ISNULL(ylong.deviceid,'')='') THEN '异常' ELSE '正常' END) as fa_status_text FROM (SELECT fp_id,area_code,unit_name,unit_code,fa_number,touch_name, touch_phone,install_name,install_id,CONVERT(varchar(100),install_date,23) AS install_date,skiller,skiller_id,user_note FROM BB_FacilityPreserve WHERE 1=1 ", param.rows)); _param?.Clear(); //项目/区域 if (!string.IsNullOrEmpty(param.unit_code)) { sb.Append(" AND unit_code = @unit_code "); _param.Add("@unit_code", param.unit_code); } else if (!string.IsNullOrEmpty(param.area_code)) { //区域代码 sb.Append(" AND area_code = @area_code "); _param.Add("@area_code", param.area_code); } else { sb.Append(" AND 1=0 "); } //安装人 if (!string.IsNullOrEmpty(param.install_name)) { sb.Append(string.Format(" AND install_name LIKE '%{0}%' ", param.install_name)); } //技术支持 if (!string.IsNullOrEmpty(param.skiller)) { sb.Append(string.Format(" AND skiller LIKE '%{0}%' ", param.skiller)); } sb.Append(") tt LEFT JOIN (SELECT t.deviceid,t.sy_date,DATEDIFF(mi,sy_date,GETDATE()) AS preDate FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY deviceid ORDER BY sy_date DESC) AS number FROM T_Synchronous WHERE 1=1 "); if (!string.IsNullOrEmpty(param.unit_code)) { sb.Append(" AND sy_unit_id = @sy_unit_id "); _param.Add("@sy_unit_id", param.unit_code); } sb.Append(string.Format(@") t WHERE t.number=1) ylong ON tt.fa_number=ylong.deviceid LEFT JOIN (SELECT * FROM (SELECT fa_number,track_date,ROW_NUMBER() OVER(PARTITION BY fa_number ORDER BY track_date DESC) AS RowCordNumber FROM dbo.BB_TrackRecord WHERE tr_status='01' ")); if (!string.IsNullOrEmpty(param.unit_code)) { sb.Append(" AND unit_code = @track_code "); _param.Add("@track_code", param.unit_code); } else if (!string.IsNullOrEmpty(param.area_code)) { //区域代码 sb.Append(" AND area_code = @track_code "); _param.Add("@track_code", param.area_code); } else { sb.Append(" AND 1=0 "); } sb.Append(string.Format(" ) Record WHERE Record.RowCordNumber=1) track ON track.fa_number=tt.fa_number WHERE 1=1 ")); if (!string.IsNullOrEmpty(param.status)) { //正常 if (param.status == "1") { sb.Append(" AND DATEDIFF(mi,ylong.sy_date,GETDATE())<=30 "); } else { sb.Append(" AND (DATEDIFF(mi,ylong.sy_date,GETDATE())>30 OR ISNULL(ylong.sy_date,'')='')"); } } sb.Append(string.Format(") item WHERE item.RowNumber > {0};", (param.page - 1) * param.rows)); DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, SqlHelper.GetParameters(_param)); return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <FacilityPreserveModel>(dt)); }