public DataTable GetExportData(WGJG02ByUnitID model)
{
if (string.IsNullOrEmpty(model.UnitID))
{
return(null);
}
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT w1.WGJG0201,w1.WGJG0202,w1.A0101,w1.A0177,b1.B0002,b1.B0002 AS UnitID,code1.E0386,code2.WGJG0203,w1.WGJG0204,w1.WGJG0205, w1.WGJG0206, w1.WGJG0207, w1.WGJG0208, w1.WGJG0209, w1.WGJG0211, w1.WGJG0212, w1.PClassID FROM ");
sb.Append(string.Format("(SELECT * FROM dbo.WGJG02 WHERE PersonID IN (SELECT PersonID FROM dbo.A01) AND UnitID LIKE '{0}%' ", model.UnitID));
//时间发放区间
if (!string.IsNullOrEmpty(model.DateStart) && !string.IsNullOrEmpty(model.DateEnd))
{
sb.Append(string.Format(" AND WGJG0201 BETWEEN '{0}' AND '{1}' ", model.DateStart, model.DateEnd));
}
else if (!string.IsNullOrEmpty(model.DateStart) && string.IsNullOrEmpty(model.DateEnd))
{
sb.Append(string.Format(" AND WGJG0201>='{0}' ", model.DateStart));
}
else if (string.IsNullOrEmpty(model.DateStart) && !string.IsNullOrEmpty(model.DateEnd))
{
sb.Append(string.Format(" AND WGJG0201<='{0}' ", model.DateEnd));
}
sb.Append(") w1 LEFT JOIN ");
sb.Append(@" (SELECT UnitID,UnitName AS B0002 FROM dbo.B01) b1 ON w1.UnitID=b1.UnitID LEFT JOIN
(SELECT CodeItemID, CodeItemName AS E0386 FROM dbo.SM_CodeItems WHERE CodeID = 'JA') code1 ON w1.E0386 = code1.CodeItemID LEFT JOIN
(SELECT CodeItemID, CodeItemName AS WGJG0203 FROM dbo.SM_CodeItems WHERE CodeID = 'GZFFFS') code2 ON w1.WGJG0203 = code2.CodeItemID ");
sb.Append(string.Format(" ORDER BY w1.A0177,w1.WGJG0201;"));
return(SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text));
}
public int CountPersonsByModel(WGJG02ByUnitID model)
{
if (model == null)
{
return(0);
}
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("select count(*) from WGJG02 where PersonID IN (SELECT PersonID FROM dbo.A01) AND UnitID LIKE '{0}%' ", model.UnitID));
//关键字
if (!string.IsNullOrEmpty(model.A0101))
{
sb.Append(string.Format(" and A0101 like '%{0}%' ", model.A0101));
}
//时间发放区间
if (!string.IsNullOrEmpty(model.DateStart) && !string.IsNullOrEmpty(model.DateEnd))
{
sb.Append(string.Format(" and WGJG0201 BETWEEN '{0}' AND '{1}' ", model.DateStart, model.DateEnd));
}
else if (!string.IsNullOrEmpty(model.DateStart) && string.IsNullOrEmpty(model.DateEnd))
{
sb.Append(string.Format(" and WGJG0201>='{0}' ", model.DateStart));
}
else if (string.IsNullOrEmpty(model.DateStart) && !string.IsNullOrEmpty(model.DateEnd))
{
sb.Append(string.Format(" and WGJG0201<='{0}' ", model.DateEnd));
}
return(Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text)));
}
public void ExportToExcel(WGJG02ByUnitID model)
{
//1.0获取待导出数据
System.Data.DataTable dt = DBSession.IWGJG02DAL.GetExportData(model);
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("A0101", "姓名");
dict.Add("B0002", "用工单位");
dict.Add("UnitID", "用工单位");
dict.Add("E0386", "工种");
dict.Add("WGJG0203", "发放方式");
dict.Add("WGJG0202", "约定发放时间");
dict.Add("WGJG0201", "确定时间");
dict.Add("WGJG0207", "应发金额");
dict.Add("WGJG0208", "实发金额");
if (dt != null)
{
DataRow row = dt.NewRow();
row["A0101"] = "合计";
row["WGJG0207"] =
dt.Select(" WGJG0207 IS NOT NULL").AsEnumerable().Select(d => d.Field <decimal>("WGJG0207")).Sum();
row["WGJG0208"] = dt.Select(" WGJG0208 IS NOT NULL").AsEnumerable().Select(d => d.Field <decimal>("WGJG0208")).Sum();
dt.Rows.Add(row);
}
HCQ2_Common.NpoiHelper.DataTableToExeclForNpoi(dt, dict, "发放汇总", "发放汇总", "A0177", true, ",WGJG0207,WGJG0208,");
}
public int CountPersonsByModel(WGJG02ByUnitID model)
{
if (model == null)
{
return(0);
}
return(DBSession.IWGJG02DAL.CountPersonsByModel(model));
}
public List <WGJG02Model> GetWageDetailByUnitID(WGJG02ByUnitID model)
{
if (model == null)
{
return(null);
}
return(DBSession.IWGJG02DAL.GetWageDetailByUnitID(model));
}
