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