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