public ActionResult InitTableData()
{
string unitID = Helper.ToString(Request["unitID"]);
if (string.IsNullOrEmpty(unitID))
{
return(null);
}
string WGJG0103 = RequestHelper.GetDeStrByName("WGJG0103"),
CodeItemValue = RequestHelper.GetStrByName("CodeItemValue"),
startDate = RequestHelper.GetStrByName("startDate"),
endDate = RequestHelper.GetStrByName("endDate");
int page = Helper.ToInt(Request["page"]);
int rows = Helper.ToInt(Request["rows"]);
HCQ2_Model.SelectModel.WGJG01ChartModel model = new HCQ2_Model.SelectModel.WGJG01ChartModel()
{
unitID = unitID, keyword = WGJG0103, stauts = CodeItemValue, dateStart = startDate, dateEnd = endDate, page = page, rows = rows
};
List <WGJG01Model> list = operateContext.bllSession.WGJG01.GetWageListDataByUnit(model);
TableModel tModel = new TableModel()
{
total = operateContext.bllSession.WGJG01.SelectCount(s => s.UnitID == unitID),
rows = list
};
return(Json(tModel, JsonRequestBehavior.AllowGet));
}
public ActionResult InitWageTableData()
{
string rowid = Helper.ToString(Request["rowid"]);
string unitID = Helper.ToString(Request["unitID"]);
string dateStart = Helper.ToString(Request["dateStart"]);
string dateEnd = Helper.ToString(Request["dateEnd"]);
string sendType = Helper.ToString(Request["sendType"]);
string userName = RequestHelper.GetDeStrByName("userName");//关键字
int page = Helper.ToInt(Request["page"]);
int rows = Helper.ToInt(Request["rows"]);
HCQ2_Model.SelectModel.WGJG01ChartModel model = new HCQ2_Model.SelectModel.WGJG01ChartModel()
{
rowID = rowid, keyword = userName, unitID = unitID, isGive = sendType, dateStart = dateStart, dateEnd = dateEnd, page = page, rows = rows
};
List <WGJG02Model> list = operateContext.bllSession.WGJG02.GetWageDetailByRowId(model);
TableModel tModel = new TableModel()
{
total = operateContext.bllSession.WGJG02.CountGrantPersons(model),
rows = list
};
return(Json(tModel, JsonRequestBehavior.AllowGet));
}
public List <WGJG01Model> GetWageListDataByUnit(HCQ2_Model.SelectModel.WGJG01ChartModel model)
{
if (string.IsNullOrEmpty(model.rowID) && string.IsNullOrEmpty(model.unitID))
{
return(null);
}
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
if (!model.isAll)
{
sb.Append(" TOP " + model.rows);
}
sb.Append(@" g1.RowID,b1.modelName,b2.UnitName,g1.UnitID,code.CodeItemName,code.CodeItemValue,g1.WGJG0102,g1.WGJG0103,g1.WGJG0104
, g1.WGJG0105, g1.WGJG0106, g1.WGJG0107,allPerson=(SELECT COUNT(*) FROM dbo.WGJG02 w2 WHERE w2.WGJG01RowID=g1.RowID),
surePerson=(SELECT COUNT(*) FROM dbo.WGJG02 w2 WHERE w2.WGJG01RowID=g1.RowID AND w2.WGJG0211='1'),payPerson=(SELECT COUNT(*) FROM dbo.WGJG02 w2 WHERE w2.WGJG01RowID=g1.RowID AND w2.WGJG0211<>'1'),allMoney=(SELECT SUM(WGJG0207) FROM dbo.WGJG02 w2 WHERE w2.WGJG01RowID=g1.RowID),sureMoney=(SELECT SUM(WGJG0208) FROM dbo.WGJG02 w2 WHERE w2.WGJG01RowID=g1.RowID AND ISNULL(WGJG0211,'')='1'),payMoney=(SELECT SUM(WGJG0207) FROM dbo.WGJG02 w2 WHERE w2.WGJG01RowID=g1.RowID AND ISNULL(WGJG0211,'')<>'1') FROM ");
if (!string.IsNullOrEmpty(model.rowID))
{
sb.Append(
string.Format("(SELECT * FROM dbo.WGJG01 WHERE RowID='{0}' ", model.rowID));
}
else
{
sb.Append(
string.Format("(SELECT * FROM dbo.WGJG01 WHERE UnitID='" + model.unitID + "'"));
}
//1.关键字
if (!string.IsNullOrEmpty(model.keyword))
{
sb.Append(string.Format(" AND WGJG0103 LIKE '%{0}%' ", model.keyword));
}
//2.状态
if (!string.IsNullOrEmpty(model.stauts))
{
sb.Append(string.Format(" AND WGJG0101='{0}' ", model.stauts));
}
//3.日期
if (!string.IsNullOrEmpty(model.dateStart) && !string.IsNullOrEmpty(model.dateEnd))
{
sb.Append(string.Format(" AND WGJG0102 BETWEEN '{0}' AND '{1}' ", model.dateStart, model.dateEnd));
}
else if (!string.IsNullOrEmpty(model.dateStart))
{
sb.Append(string.Format(" AND WGJG0102>='{0}' ", model.dateStart));
}
else if (!string.IsNullOrEmpty(model.dateEnd))
{
sb.Append(string.Format(" AND WGJG0102<='{0}' ", model.dateEnd));
}
sb.Append(" ) g1 LEFT JOIN ");
sb.Append(@"(SELECT UnitID,UnitName AS modelName FROM dbo.B01) b1 ON g1.UnitID=b1.UnitID LEFT JOIN
(SELECT UnitID, UnitName FROM dbo.B01) b2 ON g1.UnitID = b2.UnitID LEFT JOIN
(SELECT item1.*,item2.* FROM
(SELECT item_id,item_code FROM dbo.T_ItemCode WHERE item_code='GDBS') item1 INNER JOIN
(SELECT code_name AS CodeItemName,code_value AS CodeItemValue,item_id AS id FROM dbo.T_ItemCodeMenum) item2 ON item1.item_id = item2.id) code ON g1.WGJG0101=code.CodeItemValue ");
sb.Append(" WHERE b1.modelName IS NOT NULL ");
if (model.page > 1 && !model.isAll)
{
sb.Append(string.Format(@" and g1.DispOrder>
(SELECT MAX(CASE WHEN LEN(DispOrder)=0 THEN 0 ELSE DispOrder END) FROM(SELECT TOP {0} DispOrder FROM dbo.WGJG01 ORDER BY DispOrder ASC) g) ", model.rows * (model.page - 1)));
}
sb.Append(" ORDER BY g1.WGJG0102 DESC");
DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text);
return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <WGJG01Model>(dt));
}