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