/// <summary> /// 将数据转为指定html格式,并以流的形式返回,以便导出成指定格式的文件 /// </summary> /// <param name="title"></param> /// <param name="dt"></param> public static byte[] OutPensionFileStream(int cols, V_EmployeePensionReports dt) { StringBuilder sb = new StringBuilder(); sb.Append(Utility.GetHeader().ToString()); sb.Append(GetBody(dt, cols).ToString()); byte[] by = Encoding.UTF8.GetBytes(sb.ToString()); return(by); }
public byte[] ExportEmployeePensionReports(string sort, string filterString, IList <object> paras, string userID, string CompanyID, DateTime Dt) { byte[] result = null; try { V_EmployeePensionReports Reports = new V_EmployeePensionReports(); Reports = GetEmployeePensionReports(sort, filterString, paras, userID, CompanyID, Dt); int cols = 7; result = OutPensionFileStream(cols, Reports); } catch (Exception ex) { SMT.Foundation.Log.Tracer.Debug("导出员工薪酬社保报表出错ExportEmployeePensionReports:" + ex.Message); } return(result); }
/// <summary> /// 获取社保、薪酬的EXCEL /// </summary> /// <param name="dt"></param> /// <returns></returns> public static StringBuilder GetBody(V_EmployeePensionReports Reports, int cols) { StringBuilder s = new StringBuilder(); int SecCols = cols - 1; s.Append("<body>\n\r"); s.Append("<table ID=\"Table0\" border=1 CELLSPACING=1 CELLPADDING=3 width=100% align=center>\n\r"); s.Append("<tr>"); s.Append("<td colspan=\"" + cols + "\" align=center class=\"title\">" + Reports.COMPANYNAME + "产业单位" + Reports.ENDDATE + "薪酬、保险缴交明细表</td>"); s.Append("</tr>\n\r"); s.Append("<tr>"); s.Append("<td>截止时间:</td>"); s.Append("<td colspan=\"" + SecCols + "\" align=center class=\"title\">" + Reports.ENDDATE + "</td>"); s.Append("</tr>\n\r"); s.Append("<tr>"); s.Append("<td>产业单位:</td>"); s.Append("<td colspan=\"" + SecCols + "\" align=center class=\"title\">" + Reports.COMPANYNAME + "</td>"); s.Append("</tr>\n\r"); s.Append("</table>\n\r"); s.Append("<table border=1 cellspacing=0 CELLPADDING=3 width=100% align=center>"); s.Append("<tr>"); s.Append("<td align=center class=\"title\" rowspan=\"2\" colspan=\"2\">保险类别</td>"); s.Append("<td align=center class=\"title\" rowspan=\"2\" >是否参保</td>"); s.Append("<td align=center class=\"title\" rowspan=\"2\">参保人数(人)</td>"); s.Append("<td align=center class=\"title\" >单位缴纳</td>"); s.Append("<td align=center class=\"title\" >个人缴纳</td>"); s.Append("<td colspan=\"2\" align=center class=\"title\" >薪酬总额</td>"); s.Append("</tr>"); s.Append("<tr>"); //s.Append("<td ></td>"); //s.Append("<td ></td>"); //s.Append("<td ></td>"); //s.Append("<td></td>"); s.Append("<td align=center class=\"title\" >缴费金额合计(元)</td>"); s.Append("<td align=center class=\"title\" >缴费金额合计(元)</td>"); s.Append("<td align=center class=\"title\" >固定收入合计</td>"); s.Append("<td align=center class=\"title\" >应发绩效工资合计</td>"); s.Append("</tr>"); #region 合同制员工 s.Append("<tr>"); s.Append("<td rowspan=\"7\" class=\"x1282\">合同制员工</td>"); s.Append("<td class=\"x1282\">养老</td>"); s.Append("<td class=\"x1282\">是</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLPENSIONNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLCOMPANYPENSIONNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLOWNERPENSIONNUMERS + "</td>"); s.Append("<td class=\"x1282\" rowspan=\"6\">" + Reports.TOTOALFIXINCOMEMONEY + "</td>"); s.Append("<td rowspan=\"6\">" + Reports.TOTALPERFORMANCEMONEY + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">工伤</td>"); s.Append("<td class=\"x1282\">是</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLINJURYNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLCOMPANYINJURYNUMERS + "</td>"); s.Append("<td>" + Reports.FULLOWNERINJURYNUMERS + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">生育</td>"); s.Append("<td class=\"x1282\">是</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLBEARNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLCOMPANYBEARNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLOWNERBEARNUMERS + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">医疗</td>"); s.Append("<td class=\"x1282\">是</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLMEDICALNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLCOMPANYMEDICALNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLOWNERMEDICALNUMERS + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">失业</td>"); s.Append("<td class=\"x1282\">是</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLLOSTNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLCOMPANYLOSTNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLOWNERLOSTNUMERS + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">住房公积金</td>"); s.Append("<td class=\"x1282\">是</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLCPFNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLCOMPANYCPFNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLOWNERCPFNUMERS + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">小计</td>"); s.Append("<td class=\"x1282\">是</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLNUMERSTOTAL + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLCOMPANYTOTAL + "</td>"); s.Append("<td class=\"x1282\">" + Reports.FULLOWNERTOTAL + "</td>"); s.Append("<td colspan=\"2\"></td>"); s.Append("</tr>"); #endregion #region 非合同制 s.Append("<tr>"); s.Append("<td rowspan=\"7\" class=\"x1282\">非合同制员工(指临时工/挂靠员工)</td>"); s.Append("<td class=\"x1282\">养老</td>"); s.Append("<td class=\"x1282\">/</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPPENSIONNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPCOMPANYPENSIONNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPOWNERPENSIONNUMERS + "</td>"); s.Append("<td class=\"x1282\" rowspan=\"6\">" + Reports.TEMPTOTOALFIXINCOMEMONEY + "</td>"); s.Append("<td class=\"x1282\" rowspan=\"6\">" + Reports.TEMPTOTALPERFORMANCEMONEY + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">工伤</td>"); s.Append("<td class=\"x1282\">/</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPINJURYNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPCOMPANYINJURYNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPOWNERINJURYNUMERS + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">生育</td>"); s.Append("<td class=\"x1282\">/</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPBEARNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPCOMPANYBEARNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPOWNERBEARNUMERS + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">医疗</td>"); s.Append("<td class=\"x1282\">/</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPMEDICALNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPCOMPANYMEDICALNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPOWNERMEDICALNUMERS + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">失业</td>"); s.Append("<td class=\"x1282\">/</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPLOSTNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPCOMPANYLOSTNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPOWNERLOSTNUMERS + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">住房公积金</td>"); s.Append("<td class=\"x1282\">/</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPCPFNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPCOMPANYCPFNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPOWNERCPFNUMERS + "</td>"); s.Append("</tr>"); s.Append("<tr>"); s.Append("<td class=\"x1282\">总计</td>"); s.Append("<td class=\"x1282\">/</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPNUMERS + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPCOMPANYTOTAL + "</td>"); s.Append("<td class=\"x1282\">" + Reports.TEMPOWNERTOTAL + "</td>"); //s.Append("<td class=\"x1282\" colspan=\"2\"></td>"); s.Append("</tr>"); #endregion //for (int i = 0; i < dt.Rows.Count; i++) //{ // s.Append("<tr>"); // for (int j = 0; j < dt.Columns.Count; j++) // { // s.Append("<td class='" + GetCss(dt.Rows[i][j].ToString(), dt.Columns[j].DataType.Name) + "'>" + dt.Rows[i][j].ToString() + "</td>"); // } // s.Append("</tr>"); //} s.Append("</table>"); s.Append("</body></html>"); return(s); }
public V_EmployeePensionReports GetEmployeePensionReports(string sort, string filterString, IList <object> paras, string userID, string CompanyID, DateTime Dt) { V_EmployeePensionReports Reports = new V_EmployeePensionReports(); try { List <object> queryParas = new List <object>(); queryParas.AddRange(paras); SetOrganizationFilter(ref filterString, ref queryParas, userID, "T_HR_PENSIONDETAIL"); decimal IntYear = Dt.Year; decimal IntMonth = Dt.Month; IQueryable <T_HR_PENSIONDETAIL> ents = from ent in dal.GetObjects().Include("T_HR_PENSIONMASTER") where ent.PENSIONMOTH == IntMonth && ent.PENSIONYEAR == IntYear && ent.OWNERCOMPANYID == CompanyID select ent; if (!string.IsNullOrEmpty(filterString)) { ents = ents.Where(filterString, queryParas.ToArray()); } var entCompany = from ent in dal.GetObjects <T_HR_COMPANY>() where ent.COMPANYID == CompanyID select ent; if (entCompany.Count() > 0) { Reports.COMPANYNAME = entCompany.FirstOrDefault().BRIEFNAME; } string StrYear = Dt.Year.ToString(); string StrMonth = Dt.Month.ToString(); Reports.ENDDATE = StrYear + "年" + StrMonth + "月"; //养老总数 //var CompanyPensions = ents.Sum(p => p.PENSIONCOMPANYCOST ); //个人养老保险总数 //var PersonPensions = ents.Sum(p=>p.PENSIONPERSONCOST); //养老保险人数 //var PensionsNums = ents.Where(p=> p.PENSIONCOMPANYCOST >0 ).Count(); Reports.FULLMEDICALNUMERS = 0; Reports.FULLLOSTNUMERS = 0; Reports.FULLCPFNUMERS = 0; Reports.FULLPENSIONNUMERS = 0; Reports.FULLINJURYNUMERS = 0; Reports.FULLBEARNUMERS = 0; Reports.FULLCOMPANYPENSIONNUMERS = 0; Reports.FULLOWNERPENSIONNUMERS = 0;//养老 Reports.FULLCOMPANYCPFNUMERS = 0; Reports.FULLOWNERCPFNUMERS = 0;//公积金 Reports.FULLCOMPANYMEDICALNUMERS = 0; Reports.FULLOWNERMEDICALNUMERS = 0;//医疗 Reports.FULLCOMPANYINJURYNUMERS = 0; Reports.FULLOWNERINJURYNUMERS = 0;//工伤 Reports.FULLCOMPANYLOSTNUMERS = 0; Reports.FULLOWNERLOSTNUMERS = 0;//失业 Reports.FULLCOMPANYBEARNUMERS = 0; Reports.FULLOWNERBEARNUMERS = 0;//生育 Reports.TOTOALFIXINCOMEMONEY = 0; #region 非合同工 Reports.TEMPMEDICALNUMERS = 0; Reports.TEMPLOSTNUMERS = 0; Reports.TEMPCPFNUMERS = 0; Reports.TEMPPENSIONNUMERS = 0; Reports.TEMPINJURYNUMERS = 0; Reports.TEMPBEARNUMERS = 0; Reports.TEMPCOMPANYPENSIONNUMERS = 0; //养老保险 Reports.TEMPOWNERPENSIONNUMERS = 0; //个人缴交养老保险 Reports.TEMPCOMPANYCPFNUMERS = 0; //公司公积金 Reports.TEMPOWNERCPFNUMERS = 0; //个人缴公积金 Reports.TEMPCOMPANYMEDICALNUMERS = 0; //单位缴交医疗金 Reports.TEMPOWNERMEDICALNUMERS = 0; //个人缴医疗金 Reports.TEMPCOMPANYINJURYNUMERS = 0; //公司缴交工伤险 Reports.TEMPOWNERINJURYNUMERS = 0; //公司缴交工伤险 Reports.TEMPCOMPANYLOSTNUMERS = 0; //公司缴交失业险 Reports.TEMPOWNERLOSTNUMERS = 0; //个人缴交失业险 Reports.TEMPCOMPANYBEARNUMERS = 0; //公司缴交生育险 Reports.TEMPOWNERBEARNUMERS = 0; //个人缴交生育险 #endregion Reports.FULLNUMERSTOTAL = 0; Reports.TEMPNUMERS = 0; Reports.FULLCOMPANYTOTAL = 0; Reports.TEMPCOMPANYTOTAL = 0; Reports.FULLOWNERTOTAL = 0; Reports.TEMPOWNERTOTAL = 0; Reports.TOTOALFIXINCOMEMONEY = 0; if (ents.Count() > 0) { //ents = from contract in dal.GetObjects<T_HR_EMPLOYEECONTRACT> ents.ToList().ForEach(item => { var employees = from ent in dal.GetObjects <T_HR_EMPLOYEECONTRACT>().Include("T_HR_EMPLOYEE") where ent.T_HR_EMPLOYEE.EMPLOYEEID == item.EMPLOYEEID select ent; //获取该员工当月的工资中固定收入 var Salary = from ent in dal.GetObjects <T_HR_EMPLOYEESALARYRECORD>() where ent.EMPLOYEEID == item.EMPLOYEEID && ent.SALARYYEAR == StrYear && ent.SALARYMONTH == StrMonth && ent.CHECKSTATE == "2" select ent; if (employees.Count() > 0) { Reports.FULLMEDICALNUMERS += 1; Reports.FULLLOSTNUMERS += 1; Reports.FULLCPFNUMERS += 1; Reports.FULLPENSIONNUMERS += 1; Reports.FULLINJURYNUMERS += 1; Reports.FULLBEARNUMERS += 1; if (item.PENSIONCOMPANYCOST != null) { Reports.FULLCOMPANYPENSIONNUMERS += item.PENSIONCOMPANYCOST;//养老保险 } if (item.PENSIONPERSONCOST != null) { Reports.FULLOWNERPENSIONNUMERS += item.PENSIONPERSONCOST;//个人缴交养老保险 } if (item.HOUSINGFUNDCOMPANYCOST != null) { Reports.FULLCOMPANYCPFNUMERS += item.HOUSINGFUNDCOMPANYCOST;//公司公积金 } if (item.HOUSINGFUNDPERSONCOST != null) { Reports.FULLOWNERCPFNUMERS += item.HOUSINGFUNDPERSONCOST;//个人缴公积金 } if (item.MEDICARECOMPANYCOST != null) { Reports.FULLCOMPANYMEDICALNUMERS += item.MEDICARECOMPANYCOST;//单位缴交医疗金 } if (item.MEDICAREPERSONCOST != null) { Reports.FULLOWNERMEDICALNUMERS += item.MEDICAREPERSONCOST;//个人缴医疗金 } if (item.INJURYINSURANCECOMPANYCOST != null) { Reports.FULLCOMPANYINJURYNUMERS += item.INJURYINSURANCECOMPANYCOST;//公司缴交工伤险 } if (item.INJURYINSURANCEPERSONCOST != null) { Reports.FULLOWNERINJURYNUMERS += item.INJURYINSURANCEPERSONCOST;//公司缴交工伤险 } if (item.UNEMPLOYEDINSURANCECOMPANYCOST != null) { Reports.FULLCOMPANYLOSTNUMERS += item.UNEMPLOYEDINSURANCECOMPANYCOST;//公司缴交失业险 } if (item.UNEMPLOYEDPERSON != null) { Reports.FULLOWNERLOSTNUMERS += item.UNEMPLOYEDPERSON;//个人缴交失业险 } if (item.BIRTHINSURANCECOMPANYCOST != null) { Reports.FULLCOMPANYBEARNUMERS += item.BIRTHINSURANCECOMPANYCOST;//公司缴交生育险 } if (item.BIRTHPERSONCOST != null) { Reports.FULLOWNERBEARNUMERS += item.BIRTHPERSONCOST;//个人缴交生育险 } if (Salary.Count() > 0) { Reports.TOTOALFIXINCOMEMONEY += Salary.FirstOrDefault().FIXEDINCOMESUM; } } else { Reports.TEMPMEDICALNUMERS += 1; Reports.TEMPLOSTNUMERS += 1; Reports.TEMPCPFNUMERS += 1; Reports.TEMPPENSIONNUMERS += 1; Reports.TEMPINJURYNUMERS += 1; Reports.TEMPBEARNUMERS += 1; Reports.TEMPCOMPANYPENSIONNUMERS += item.PENSIONCOMPANYCOST; //养老保险 Reports.TEMPOWNERPENSIONNUMERS += item.PENSIONPERSONCOST; //个人缴交养老保险 Reports.TEMPCOMPANYCPFNUMERS += item.HOUSINGFUNDCOMPANYCOST; //公司公积金 Reports.TEMPOWNERCPFNUMERS += item.HOUSINGFUNDPERSONCOST; //个人缴公积金 Reports.TEMPCOMPANYMEDICALNUMERS += item.MEDICARECOMPANYCOST; //单位缴交医疗金 Reports.TEMPOWNERMEDICALNUMERS += item.MEDICAREPERSONCOST; //个人缴医疗金 Reports.TEMPCOMPANYINJURYNUMERS += item.INJURYINSURANCECOMPANYCOST; //公司缴交工伤险 Reports.TEMPOWNERINJURYNUMERS += item.INJURYINSURANCEPERSONCOST; //公司缴交工伤险 Reports.TEMPCOMPANYLOSTNUMERS += item.UNEMPLOYEDINSURANCECOMPANYCOST; //公司缴交失业险 Reports.TEMPOWNERLOSTNUMERS += item.UNEMPLOYEDPERSON; //个人缴交失业险 Reports.TEMPCOMPANYBEARNUMERS += item.BIRTHINSURANCECOMPANYCOST; //公司缴交生育险 Reports.TEMPOWNERBEARNUMERS += item.BIRTHPERSONCOST; //个人缴交生育险 if (Salary.Count() > 0) { Reports.TEMPTOTOALFIXINCOMEMONEY += Salary.FirstOrDefault().FIXEDINCOMESUM; } } }); Reports.FULLNUMERSTOTAL = Reports.FULLMEDICALNUMERS + Reports.FULLINJURYNUMERS + Reports.FULLPENSIONNUMERS + Reports.FULLBEARNUMERS + Reports.FULLLOSTNUMERS + Reports.FULLCPFNUMERS; Reports.TEMPNUMERS = Reports.TEMPMEDICALNUMERS + Reports.TEMPINJURYNUMERS + Reports.TEMPPENSIONNUMERS + Reports.TEMPBEARNUMERS + Reports.TEMPLOSTNUMERS + Reports.TEMPCPFNUMERS; Reports.FULLCOMPANYTOTAL = Reports.FULLCOMPANYMEDICALNUMERS + Reports.FULLCOMPANYLOSTNUMERS + Reports.FULLCOMPANYINJURYNUMERS + Reports.FULLCOMPANYPENSIONNUMERS + Reports.FULLCOMPANYCPFNUMERS + Reports.FULLCOMPANYBEARNUMERS; Reports.TEMPCOMPANYTOTAL = Reports.TEMPCOMPANYMEDICALNUMERS + Reports.TEMPCOMPANYLOSTNUMERS + Reports.TEMPCOMPANYINJURYNUMERS + Reports.TEMPCOMPANYPENSIONNUMERS + Reports.TEMPCOMPANYCPFNUMERS + Reports.TEMPCOMPANYBEARNUMERS; Reports.FULLOWNERTOTAL = Reports.FULLOWNERMEDICALNUMERS + Reports.FULLOWNERPENSIONNUMERS + Reports.FULLOWNERLOSTNUMERS + Reports.FULLOWNERINJURYNUMERS + Reports.FULLOWNERCPFNUMERS + Reports.FULLOWNERBEARNUMERS; Reports.TEMPOWNERTOTAL = Reports.TEMPOWNERMEDICALNUMERS + Reports.TEMPOWNERPENSIONNUMERS + Reports.TEMPOWNERLOSTNUMERS + Reports.TEMPOWNERINJURYNUMERS + Reports.TEMPOWNERCPFNUMERS + Reports.TEMPOWNERBEARNUMERS; Reports.TOTOALFIXINCOMEMONEY = Reports.FULLCOMPANYTOTAL + Reports.FULLOWNERTOTAL; //Reports.TOTALPERFORMANCEMONEY = } } catch (Exception ex) { SMT.Foundation.Log.Tracer.Debug("PensionDetailBLL-GetEmployeePensionReports导出员工薪酬社保报表出错" + ex.ToString()); } return(Reports); }