/// <summary>
/// 创建报表操作
/// </summary>
/// <param name="model">工资报表信息</param>
/// <param name="lstSummary">工资合计信息</param>
/// <param name="dtFixedSalary">固定工资信息</param>
public static bool CreateSalaryReport(SalaryReportModel model, ArrayList lstSummary, DataTable dtFixedSalary)
{
//变量定义
ArrayList lstCommand = new ArrayList();
//工资报表信息
lstCommand.Add(InsertSalaryReport(model));
//工资合计信息
InsertSalarySummary(lstSummary, lstCommand);
//工资明细
InsertSalaryDetail(dtFixedSalary, model.CompanyCD, model.ReprotNo, model.ModifiedUserID, lstCommand);
//执行保存操作
bool isSucc = SqlHelper.ExecuteTransWithArrayList(lstCommand);
if (isSucc) model.ID = ((SqlCommand)lstCommand[0]).Parameters["@ReportID"].Value.ToString();
return isSucc;
}
protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
SalaryReportModel searchModel = new SalaryReportModel();
//设置查询条件
//报表编号
searchModel.ReprotNo = txtReportNo.Text.Trim();
//报表主题
searchModel.ReportName = txtReportName.Text.Trim();
//所属年月
searchModel.ReportMonth = Request.Form["ddlYear"].ToString() + Request.Form["ddlMonth"].ToString();
//编制状态
searchModel.Status = Request.Form["ddlStatus"].ToString();
//审批状态
searchModel.FlowStatus = Request.Form["ddlFlowStatus"].ToString();
//查询数据
DataTable dtReport = SalaryReportBus.SearchReportInfo(searchModel);
//导出标题
string headerTitle = "工资报表编号|工资报表主题|所属月份|开始日期|结束日期|编制人|编制日期|报表状态|审批状态";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "ReprotNo|ReportName|ReportMonth|StartDate|EndDate|Creator|CreateDate|StatusName|FlowStatus";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dtReport, header, field, "工资报表列表");
}
/// <summary>
/// 生成报表处理
/// </summary>
/// <param name="belongMonth">所属月份</param>
/// <returns></returns>
public static string CreateSalaryReport(SalaryReportModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//获取公司代码
string companyCD = userInfo.CompanyCD;
string userID = userInfo.UserID;
model.CompanyCD = companyCD;
model.ModifiedUserID = userID;
//获取人员信息
EmployeeSearchModel searchEmplModel = new EmployeeSearchModel();
//设置公司代码
searchEmplModel.CompanyCD = companyCD;
searchEmplModel.StartDate = model.StartDate;
searchEmplModel.EndDate = model.EndDate;
//查询人员信息
DataTable dtEmplInfo = EmployeeInfoDBHelper.GetWorkEmplInfo(searchEmplModel);
DataTable dtNew = SalaryItemBus.GetOutEmployeeInfo(searchEmplModel);
dtEmplInfo.Merge(dtNew);
//获取员工固定工资
DataTable dtEmplSalary = SalaryEmployeeDBHelper.GetSalaryEmployeeInfo(companyCD);
DataTable dtFixedSalary = new DataTable();
dtFixedSalary = dtEmplSalary.Clone();
//dtFixedSalary.Columns["SalaryMoney"].DataType.GetType() = "System.Decimal";
//获取员工计件工资
DataTable dtPeiceSalary = PieceworkSalaryDBHelper.GetMonthPieceworkSalary(companyCD, model.ReportMonth,model .StartDate ,model.EndDate );
string spMonth = model.ReportMonth.Substring(0, 4) + "-" + model.ReportMonth.Substring(4);
//获取员工出勤率
DataTable dtAttendance = PieceworkSalaryDBHelper.GetMonthAttendance(companyCD, spMonth );
//获取员工计时工资
DataTable dtTimeSalary = TimeSalaryDBHelper.GetMonthTimeSalary(companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取员工产品单品提成工资
DataTable dtCommSalary = CommissionSalaryDBHelper.GetMonthCommSalary(companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取公司业务提成
DataTable dtComanySalary = InputCompanyRoyaltyDBHelper.GetMonthCompanySalary(companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取公司业务提成设置
DataTable dtCompanySet = InputCompanyRoyaltyDBHelper.GetCompanySetInfo(companyCD);
//获取个人业务信息
DataTable dtPersonCommSalary = InputPersonalRoyaltyDBHelper.GetMonthPersonSalary(companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取部门业务提成
DataTable dtDeptSalary = InputDepatmentRoyaltyDBHelper.GetMonthDeptSalary (companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取部门业务提成设置
DataTable dtDeptSet = InputDepatmentRoyaltyDBHelper.GetDeptSetInfo(companyCD);
//获取员工整体薪资结构
DataTable dtStructure = SalaryEmployeeStructureSetDBHelper.GetSalaryStructure(companyCD);
//获取绩效工资
DataTable dtPerformanceSalary = InputPerformanceRoyaltyDBHelper.GetMonthPerformanceSalary (companyCD, model.ReportMonth, model.StartDate, model.EndDate);
//获取社会保险信息
DataTable dtInsuSalary = InsuEmployeeDBHelper.GetInsuEmployeeInf(companyCD,model.ReportMonth );
//获取个人所得税信息
DataTable dtPersonSalary = InputPersonTrueIncomeTaxDBHelper.SearchPersonTax (companyCD, model.ReportMonth);
//变量定义
ArrayList lstSummary = new ArrayList();
//遍历所有的员工信息，获取工资总额
for (int i = 0; i < dtEmplInfo.Rows.Count; i++)
{
//变量定义
SalaryReportSummaryModel summaryModel = new SalaryReportSummaryModel();
//变量定义
decimal totalSalary = 0;
decimal salaryMoney = 0;
//公司代码
summaryModel.CompanyCD = companyCD;
//报表编号
summaryModel.ReprotNo = model.ReprotNo;
//获取员工ID
string employeeID = GetSafeData.GetStringFromInt(dtEmplInfo.Rows[i], "ID");
summaryModel.EmployeeID = employeeID;
summaryModel.AdminLevelName = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "AdminLevelName");
summaryModel.DeptName = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "DeptName");
summaryModel.EmployeeName = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "EmployeeName");
summaryModel.EmployeeNo = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "EmployeeNo");
summaryModel.QuarterName = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "QuarterName");
string deptID = GetSafeData.ValidateDataRow_String(dtEmplInfo.Rows[i], "DeptID");
//获取员工薪资结构
DataRow[] drStructure = dtStructure.Select("EmployeeID=" + employeeID);
if (drStructure.Length > 0)
{
}
else
{
lstSummary.Add(summaryModel);
continue;
}
#region 判断是否设置固定工资
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsFixSalarySet") == "1")//判断是否设置固定工资
{
//固定工资
DataRow[] drFixedSalary = dtEmplSalary.Select("EmployeeID=" + employeeID);
//遍历所有固定工资项，计算工资额
for (int j = 0; j < drFixedSalary.Length; j++)
{
//导入工资记录
dtFixedSalary.ImportRow(drFixedSalary[j]);
//该项工资额为DBNull时，增加该工资项
if (drFixedSalary[j]["SalaryMoney"] != DBNull.Value)
{
//获取是否扣款
string flag = GetSafeData.ValidateDataRow_String(drFixedSalary[j], "PayFlag");
string cal = GetSafeData.ValidateDataRow_String(drFixedSalary[j], "Calculate");
decimal fina=0;
if (string.IsNullOrEmpty(cal))
{
fina =Convert.ToDecimal(drFixedSalary[j]["SalaryMoney"]);
}
else
{
while (cal.IndexOf('A') != -1)
{
cal = cal.Replace('A', '+');
}
string numlist = GetSafeData.ValidateDataRow_String(drFixedSalary[j], "ParamsList");
string[] numberlist = numlist.Split(',');
string temp = cal;
for (int inde = 0; inde < numberlist.Length; inde++)
{
if (numberlist[inde] == "@")
{
DataRow[] drtem = dtAttendance.Select("EmployeeID=" + employeeID);
decimal atten=0;
if (drtem .Length >0)
{
atten = Math.Round(Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drtem[0], "AttendanceRate")) / 100, 2);
}
if (atten < 1)
{
cal = cal.Replace("{" + numberlist[inde] + "}", Convert.ToString(atten));
}
else
{
cal = cal.Replace("{" + numberlist[inde] + "}", "0");
}
}
else
{
if (!string.IsNullOrEmpty(numberlist[inde]))
{
DataRow[] drtem = dtEmplSalary.Select("EmployeeID=" + employeeID + " and ItemNo=" + numberlist[inde]);
if (drtem.Length > 0)
{
cal = cal.Replace("{" + numberlist[inde] + "}", GetSafeData.ValidateDataRow_String(drtem[0], "SalaryMoney"));
}
else
{
cal = cal.Replace("{" + numberlist[inde] + "}", "0");
}
}
}
}
try
{
// string result = CustomEval.eval(cal).ToString();
// fina = Convert.ToDecimal(result);
}
catch
{
fina = 0;
}
}
if ("0".Equals(flag))
{
//增加工资额
totalSalary += fina;
}
else
{
//减少工资额
// totalSalary -= Convert.ToDecimal(drFixedSalary[j]["SalaryMoney"]);
salaryMoney += fina ;
}
for (int a = 0; a < dtFixedSalary.Rows.Count; a++)
{
string empl=dtFixedSalary .Rows [a]["EmployeeID"]==null ?"":dtFixedSalary .Rows [a]["EmployeeID"].ToString ();
string iten=dtFixedSalary .Rows [a]["ItemNo"]==null ?"":dtFixedSalary .Rows [a]["ItemNo"].ToString ();
string organItem=drFixedSalary[j]["ItemNo"] ==null ?"":drFixedSalary[j]["ItemNo"] .ToString ();
if ((empl == employeeID) && ( iten == organItem))
{
dtFixedSalary.Rows[a]["SalaryMoney"] = fina.ToString();
}
}
}
}
//固定工资
summaryModel.FixedMoney = totalSalary.ToString();
}
else
{
//固定工资
summaryModel.FixedMoney = "0";
}
#endregion
#region 判断是否设置计件
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsPieceWorkSet") == "1")//判断是否设置计件
{
//计件工资
DataRow[] drPeiceSalary = dtPeiceSalary.Select("EmployeeID=" + employeeID);
if (drPeiceSalary.Length > 0)
{
//设置计件工资
summaryModel.WorkMoney = GetSafeData.GetStringFromDecimal(drPeiceSalary[0], "TotalSalary");
//加到总工资
totalSalary += Convert.ToDecimal(drPeiceSalary[0]["TotalSalary"]);
}
}
else
{
//设置计件工资
summaryModel.WorkMoney = "0";
//加到总工资
totalSalary += 0;
}
#endregion
#region 判断是否设置计时
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsTimeWorkSet") == "1")//判断是否设置计时
{
//计时工资
DataRow[] drTimeSalary = dtTimeSalary.Select("EmployeeID=" + employeeID);
if (drTimeSalary.Length > 0)
{
//设置计时工资
summaryModel.TimeMoney = GetSafeData.GetStringFromDecimal(drTimeSalary[0], "TotalSalary");
totalSalary += Convert.ToDecimal(drTimeSalary[0]["TotalSalary"]);
}
}
else
{
//设置计时工资
summaryModel.TimeMoney = "0";
totalSalary += 0;
}
#endregion
#region 判断是否设置产品单品提成
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsProductRoyaltySet") == "1")//判断是否设置产品单品提成
{
//提成工资 dtPersonSalary
DataRow[] drCommSalary = dtCommSalary.Select("EmployeeID=" + employeeID);
if (drCommSalary.Length > 0)
{
//设置产品单品提成工资
//summaryModel.CommissionMoney = GetSafeData.GetStringFromDecimal(drCommSalary[0], "TotalSalary");Convert.ToDecimal(drCommSalary[0]["TotalSalary"]);
summaryModel.CommissionMoney = GetSafeData.GetStringFromDecimal(drCommSalary[0], "TotalSalary");
totalSalary += Convert.ToDecimal(drCommSalary[0]["TotalSalary"]);
}
}
else
{
//设置产品单品提成工资
summaryModel.CommissionMoney = "0";
//commisionMoney = 0;
totalSalary += 0;
}
#endregion
#region 判断是否设置个人业务提成
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsPersonalRoyaltySet") == "1")//判断是否设置个人业务提成
{
//
DataRow[] drPersonCommSalary = dtPersonCommSalary.Select("EmployeeID=" + employeeID);
if (drPersonCommSalary.Length > 0)
{
//设置个人业务提成
summaryModel.PersonComMoney = GetSafeData.GetStringFromDecimal(drPersonCommSalary[0], "TotalSalary");
totalSalary += Convert.ToDecimal(drPersonCommSalary[0]["TotalSalary"]);
}
}
else
{
//设置个人业务提成
//summaryModel.CommissionMoney = "0";
summaryModel.PersonComMoney ="0";
totalSalary += 0;
}
#endregion
#region 判断是否设置公司业务提成
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsCompanyRoyaltySet") == "1")//判断是否设置公司业务提成
{
decimal rate = 0;
//提成工资 dtPersonSalary dtComanySalary
DataRow[] drCompanyalary = dtComanySalary.Select("DeptID=" + deptID);
DataRow[] drCompanyalarySet = dtCompanySet.Select("DeptID=" + deptID, "MiniMoney asc");
DataRow[] drCompanyalarySetCommon = dtCompanySet.Select("DeptID=0", "MiniMoney asc");//获取通用规则
if (drCompanyalary.Length > 0)
{
string tem= GetSafeData.ValidateDataRow_String(drCompanyalary[0], "TotalSalary");
decimal temp=Convert .ToDecimal (tem );
bool isCheck = false;
if (drCompanyalarySet.Length > 0)
{
for (int a = 0; a < drCompanyalarySet.Length; a++)
{
decimal minMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySet[a], "MiniMoney"));
decimal maxMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySet[a], "MaxMoney"));
if (temp >= minMoney && temp <maxMoney)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySet[a], "TaxPercent"));
isCheck = true;
break;
}
}
if (!isCheck)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySet[(drCompanyalarySet.Length - 1)], "TaxPercent"));
}
}
else if (drCompanyalarySetCommon.Length > 0)
{
bool isHave = false;
int len=drCompanyalarySetCommon.Length;
for (int a = 0; a <len ; a++)
{
decimal minMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySetCommon[a], "MiniMoney"));
decimal maxMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySetCommon[a], "MaxMoney"));
if (temp >= minMoney && temp < maxMoney)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySetCommon[a], "TaxPercent"));
isHave = true;
break;
}
}
if (!isHave)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drCompanyalarySetCommon[(len -1)], "TaxPercent"));
}
}
summaryModel .CompanyComMoney = Convert .ToString ( decimal.Round(temp * rate * (Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drStructure[0], "CompanyRatePercent"))) / 10000, 2));
totalSalary += decimal.Round(temp * rate * (Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drStructure[0], "CompanyRatePercent"))) / 10000, 2);
}
}
else
{
summaryModel.CompanyComMoney = "0";
totalSalary += 0;
}
#endregion
#region 判断是否设置部门业务提成
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsDeptRoyaltySet") == "1")//判断是否设置部门业务提成
{
decimal rate = 0;
//提成工资 dtPersonSalary dtComanySalary
DataRow[] drDeptalary = dtDeptSalary.Select("DeptID=" + deptID);
DataRow[] drDeptalarySet = dtDeptSet.Select("DeptID=" + deptID, "MiniMoney asc");
DataRow[] drDeptalarySetCommon = dtDeptSet.Select("DeptID=0", "MiniMoney asc");//获取通用规则
if (drDeptalary.Length > 0)
{
string tem = GetSafeData.ValidateDataRow_String(drDeptalary[0], "TotalSalary");
decimal temp = Convert.ToDecimal(tem);
bool isCheck = false;
if (drDeptalarySet.Length > 0)
{
for (int a = 0; a < drDeptalarySet.Length; a++)
{
decimal minMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySet[a], "MiniMoney"));
decimal maxMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySet[a], "MaxMoney"));
if (temp >= minMoney && temp < maxMoney)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySet[a], "TaxPercent"));
break;
}
}
if (!isCheck)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySet[(drDeptalarySet.Length - 1)], "TaxPercent"));
}
}
else if (drDeptalarySetCommon.Length > 0)
{
bool isHave = false;
int len = drDeptalarySetCommon.Length;
for (int a = 0; a < len; a++)
{
decimal minMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySetCommon[a], "MiniMoney"));
decimal maxMoney = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySetCommon[a], "MaxMoney"));
if (temp >= minMoney && temp < maxMoney)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySetCommon[a], "TaxPercent"));
isHave = true;
break;
}
}
if (!isHave)
{
rate = Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drDeptalarySetCommon[(len- 1)], "TaxPercent"));
}
}
summaryModel .DeptComMoney = Convert .ToString ( decimal.Round(temp * rate * (Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drStructure[0], "DeptRatePercent"))) / 10000, 2));
totalSalary += decimal.Round(temp * rate * (Convert.ToDecimal(GetSafeData.ValidateDataRow_String(drStructure[0], "DeptRatePercent"))) / 10000, 2);
}
}
else
{
summaryModel.DeptComMoney = "0";
totalSalary += 0;
}
#endregion
#region 判断是否设置绩效
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsPerformanceSet") == "1")//判断是否设置绩效
{
//计时工资
DataRow[] drPerformanceSalary = dtPerformanceSalary.Select("EmployeeID=" + employeeID);
if (drPerformanceSalary.Length > 0)
{
//设置绩效工资
//summaryModel.TimeMoney = GetSafeData.GetStringFromDecimal(drPerformanceSalary[0], "TotalPerformanceMoney");
summaryModel.PerformanceMoney = GetSafeData.GetStringFromDecimal(drPerformanceSalary[0], "TotalPerformanceMoney");//待新建一项绩效工资栏目
totalSalary += Convert.ToDecimal(drPerformanceSalary[0]["TotalPerformanceMoney"]);
}
}
else
{
//设置绩效工资
summaryModel.PerformanceMoney = "0";
totalSalary += 0;
}
#endregion
//工资总额
summaryModel.AllGetMoney = totalSalary.ToString();
decimal insuMoney = 0;
#region 判断是否设置社会保险
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsInsurenceSet") == "1")//判断是否设置社会保险
{
//社会保险
DataRow[] drInsuSalary = dtInsuSalary.Select("EmployeeID=" + employeeID);
for (int x = 0; x < drInsuSalary.Length; x++)
{
//数据不为空时，计算社会保险总额
if (drInsuSalary[x]["InsuranceBase"] != DBNull.Value
&& drInsuSalary[x]["PersonPayRate"] != DBNull.Value)
{
//保险基数
decimal insuBase = Convert.ToDecimal(drInsuSalary[x]["InsuranceBase"]);
//个人缴纳百分比
decimal insuPercent = Convert.ToDecimal(drInsuSalary[x]["PersonPayRate"]);
//增加社会保险额
insuMoney += insuBase * insuPercent / 100;
}
}
//社会保险
summaryModel.Insurance = StringUtil.TrimZero(insuMoney.ToString());
}
else
{
summaryModel.Insurance = "0";
}
#endregion
decimal minusMoney = 0;
decimal PersonMoney = 0;
#region 判断是否设置个人所得税
if (GetSafeData.ValidateDataRow_String(drStructure[0], "IsPerIncomeTaxSet") == "1")//判断是否设置个人所得税
{
//个人所得税 dtPersonSalary
DataRow[] drPersonSalary = dtPersonSalary.Select("EmployeeID=" + employeeID);
if (drPersonSalary.Length > 0)
{
decimal salryCount = drPersonSalary[0]["SalaryCount"] == null ? 0 : Convert.ToDecimal(drPersonSalary[0]["SalaryCount"]);
decimal TaxPercent = drPersonSalary[0]["TaxPercent"] == null ? 0 : Convert.ToDecimal(drPersonSalary[0]["TaxPercent"]);
decimal TaxCount = drPersonSalary[0]["TaxCount"] == null ? 0 : Convert.ToDecimal(drPersonSalary[0]["TaxCount"]);
decimal te=0;
if ((totalSalary - salryCount - insuMoney) > 0)
{
te = (totalSalary - salryCount - insuMoney) * TaxPercent / 100 - TaxCount;
}
//个人所得税
summaryModel.IncomeTax = te.ToString ();
// totalSalary -= Convert.ToDecimal(drPersonSalary[0]["TaxCount"]);
PersonMoney = te;
}
}
else
{
//个人所得税
summaryModel.IncomeTax = "0" ;
// totalSalary -= Convert.ToDecimal(drPersonSalary[0]["TaxCount"]);
PersonMoney = 0;
}
#endregion
// decimal[] taxInfo = TaxCalculateBus.CalculateTax(totalSalary);
//税额
// decimal minusMoney = taxInfo[1];
// summaryModel.IncomeTax = StringUtil.TrimZero(minusMoney.ToString());
minusMoney = insuMoney + PersonMoney + salaryMoney;
//应扣款合计
//minusMoney =minusMoney +salaryMoney;
summaryModel.AllKillMoney = minusMoney.ToString() ;
//实发工资
summaryModel.SalaryMoney = (totalSalary - minusMoney).ToString();
//
lstSummary.Add(summaryModel);
}
//登陆报表信息
bool isSucc = SalaryReportDBHelper.CreateSalaryReport(model, lstSummary, dtFixedSalary);
if (!isSucc) return null;
else return PageDisplayInfo(dtEmplInfo, dtFixedSalary, lstSummary, companyCD);
}
/// <summary>
/// 查询工资报表信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable SearchReportInfo(SalaryReportModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//公司代码
model.CompanyCD = userInfo.CompanyCD;
//执行查询
return SalaryReportDBHelper.SearchReportInfo(model);
}
/// <summary>
/// 保存工资报表信息
/// </summary>
/// <param name="lstDetail">明细信息</param>
/// <param name="lstSummary">合计信息</param>
/// <param name="model">基本信息</param>
/// <returns></returns>
public static bool SaveSalaryInfo(ArrayList lstDetail, ArrayList lstSummary, SalaryReportModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//获取公司代码
model.CompanyCD = userInfo.CompanyCD;
//用户ID
model.ModifiedUserID = userInfo.UserID;
//执行保存操作
bool isSucc = SalaryReportDBHelper.SaveSalaryInfo(lstDetail, lstSummary, model);
//返回
return isSucc;
}
/// <summary>
/// 查询工资报表信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable SearchReportInfo(SalaryReportModel model)
{
#region 查询语句
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" A.ID ");
searchSql.AppendLine(" ,A.ReprotNo ");
searchSql.AppendLine(" ,A.ReportName ");
searchSql.AppendLine(" ,Substring(A.ReportMonth, 1, 4) + '年' ");
searchSql.AppendLine(" + Substring(A.ReportMonth, 5, 2) + '月' ");
searchSql.AppendLine(" AS ReportMonth ");
searchSql.AppendLine(" ,CONVERT(VARCHAR(10),A.StartDate,21) AS StartDate ");
searchSql.AppendLine(" ,CONVERT(VARCHAR(10),A.EndDate,21) AS EndDate ");
searchSql.AppendLine(" ,ISNULL(B.EmployeeName, '') AS Creator ");
searchSql.AppendLine(" ,CONVERT(VARCHAR(10),A.CreateDate,21) AS CreateDate ");
searchSql.AppendLine(" ,A.Status ");
searchSql.AppendLine(" ,CASE A.Status ");
searchSql.AppendLine(" WHEN '0' THEN '待提交' ");
searchSql.AppendLine(" WHEN '1' THEN '已生成' ");
searchSql.AppendLine(" WHEN '2' THEN '已提交' ");
searchSql.AppendLine(" WHEN '3' THEN '已确认' ");
searchSql.AppendLine(" ELSE '' ");
searchSql.AppendLine(" END AS StatusName ");
searchSql.AppendLine(" ,CASE h.FlowStatus ");
searchSql.AppendLine(" WHEN '1' THEN '待审批' ");
searchSql.AppendLine(" WHEN '2' THEN '审批中' ");
searchSql.AppendLine(" WHEN '3' THEN '审批通过' ");
searchSql.AppendLine(" WHEN '4' THEN '审批不通过' ");
searchSql.AppendLine(" WHEN '5' THEN '撤销审批' ");
searchSql.AppendLine(" ELSE ' ' ");
searchSql.AppendLine(" END AS FlowStatus ,isnull( Convert(varchar(100),A.ModifiedDate,23),'') AS ModifiedDate ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.SalaryReport A ");
searchSql.AppendLine(" LEFT JOIN officedba.EmployeeInfo B ");
searchSql.AppendLine(" ON B.companyCD=A.companyCD AND B.ID = A.Creator ");
//searchSql.AppendLine(" LEFT JOIN officedba.FlowInstance C ");
//searchSql.AppendLine(" ON C.CompanyCD = A.CompanyCD ");
//searchSql.AppendLine(" AND C.BillID = A.ID ");
//searchSql.AppendLine(" AND C.BillTypeFlag = @BillTypeFlag ");
//searchSql.AppendLine(" AND C.BillTypeCode = @BillTypeCode ");
//searchSql.AppendLine(" AND C.ModifiedDate =( ");
//searchSql.AppendLine(" SELECT ");
//searchSql.AppendLine(" MAX(D.ModifiedDate) ");
//searchSql.AppendLine(" FROM ");
//searchSql.AppendLine(" officedba.FlowInstance D ");
searchSql.AppendLine(" LEFT JOIN ( ");
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" MAX(E.id) ID,E.BillID,E.BillNo ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.FlowInstance E,officedba.SalaryReport n ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" E.CompanyCD = n.CompanyCD ");
searchSql.AppendLine(" AND E.BillID = n.ID ");
searchSql.AppendLine(" AND E.BillTypeFlag = @BillTypeFlag ");
searchSql.AppendLine(" AND E.BillTypeCode = @BillTypeCode group by E.BillID,E.BillNo ) g ");
searchSql.AppendLine(" on A.ID=g.BillID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.FlowInstance h ON g.ID=h.ID ");
//searchSql.AppendLine(" WHERE ");
//searchSql.AppendLine(" D.CompanyCD = A.CompanyCD ");
//searchSql.AppendLine(" AND D.BillID = A.ID ");
//searchSql.AppendLine(" AND D.BillTypeFlag = @BillTypeFlag ");
//searchSql.AppendLine(" AND D.BillTypeCode = @BillTypeCode) ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//单据类别标识
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeFlag", ConstUtil.BILL_TYPEFLAG_HUMAN));
//单据类别编码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeCode", ConstUtil.BILL_TYPECODE_HUMAN_SALARY_REPORT));
#region 页面输入条件
//报表编号
if (!string.IsNullOrEmpty(model.ReprotNo))
{
searchSql.AppendLine(" AND A.ReprotNo LIKE '%' + @ReprotNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReprotNo", model.ReprotNo));
}
//报表主题
if (!string.IsNullOrEmpty(model.ReportName))
{
searchSql.AppendLine(" AND A.ReportName LIKE '%' + @ReportName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReportName", model.ReportName));
}
//所属月份
if (!string.IsNullOrEmpty(model.ReportMonth))
{
if (model.ReportMonth.Length == 4)
{
searchSql.AppendLine(" AND A.ReportMonth LIKE @ReportMonth + '%' ");
}
else
{
searchSql.AppendLine(" AND A.ReportMonth LIKE + '%' + @ReportMonth ");
}
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReportMonth", model.ReportMonth));
}
//编制状态
if (!string.IsNullOrEmpty(model.Status))
{
searchSql.AppendLine(" AND A.Status = @Status ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Status", model.Status));
}
//审批状态
if (!string.IsNullOrEmpty(model.FlowStatus))
{
//待提交时
if ("0".Equals(model.FlowStatus))
{
searchSql.AppendLine(" AND H.FlowStatus IS NULL ");
}
else
{
searchSql.AppendLine(" AND H.FlowStatus = @FlowStatus ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FlowStatus", model.FlowStatus));
}
}
#endregion
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 更新工资合计信息
/// </summary>
/// <param name="model">基本信息</param>
/// <param name="lstDetail">工资详细信息</param>
/// <param name="lstCommand">命令</param>
/// <returns></returns>
private static void UpdateSalaryReportDetail(SalaryReportModel model, ArrayList lstDetail, ArrayList lstCommand)
{
//数据不存在时，返回不做处理
if (lstDetail == null || lstDetail.Count < 1) return;
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine(" INSERT INTO ");
insertSql.AppendLine(" officedba.SalaryReportDetail ");
insertSql.AppendLine(" (CompanyCD ");
insertSql.AppendLine(" ,ReprotNo ");
insertSql.AppendLine(" ,EmployeeID ");
insertSql.AppendLine(" ,ItemNo ");
insertSql.AppendLine(" ,ItemName ");
insertSql.AppendLine(" ,ItemOrder ");
insertSql.AppendLine(" ,PayFlag ");
insertSql.AppendLine(" ,SalaryMoney ");
insertSql.AppendLine(" ,ChangeFlag ");
insertSql.AppendLine(" ,ModifiedDate ");
insertSql.AppendLine(" ,ModifiedUserID) ");
insertSql.AppendLine(" VALUES ");
insertSql.AppendLine(" (@CompanyCD ");
insertSql.AppendLine(" ,@ReprotNo ");
insertSql.AppendLine(" ,@EmployeeID ");
insertSql.AppendLine(" ,@ItemNo ");
insertSql.AppendLine(" ,@ItemName ");
insertSql.AppendLine(" ,@ItemOrder ");
insertSql.AppendLine(" ,@PayFlag ");
insertSql.AppendLine(" ,@SalaryMoney ");
insertSql.AppendLine(" ,@ChangeFlag ");
insertSql.AppendLine(" ,getdate() ");
insertSql.AppendLine(" ,@ModifiedUserID) ");
#region SQL语句
StringBuilder updateSql = new StringBuilder();
updateSql.AppendLine(" UPDATE officedba.SalaryReportDetail ");
updateSql.AppendLine(" SET SalaryMoney = @SalaryMoney ");
updateSql.AppendLine(" ,ModifiedDate = getdate() ");
updateSql.AppendLine(" ,ModifiedUserID = @ModifiedUserID ");
updateSql.AppendLine(" WHERE ");
updateSql.AppendLine(" CompanyCD = @CompanyCD ");
updateSql.AppendLine(" AND ReprotNo = @ReprotNo ");
updateSql.AppendLine(" AND ItemNo = @ItemNo ");
updateSql.AppendLine(" AND EmployeeID = @EmployeeID ");
#endregion
//
string companyCD = model.CompanyCD;
string modifyUser = model.ModifiedUserID;
//遍历所有工资合计信息
for (int i = 0; i < lstDetail.Count; i++)
{
//定义变量
SalaryReportDetailModel detailModel = (SalaryReportDetailModel)lstDetail[i];
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
if (IsExsistSummaryDetails(companyCD, detailModel.ReprotNo, detailModel.EmployeeID, detailModel.ItemNo))
{
//设置SQL语句
comm.CommandText = updateSql.ToString();
//设置保存的参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", companyCD));//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReprotNo", detailModel.ReprotNo));//工资报表编号
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", detailModel.EmployeeID));//员工ID
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ItemNo", detailModel.ItemNo));//工资项编号
comm.Parameters.Add(SqlHelper.GetParameterFromString("@SalaryMoney", detailModel.SalaryMoney));//工资额
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", modifyUser));//更新用户ID
}
else
{
comm.CommandText = insertSql.ToString();
//设置保存的参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", companyCD));//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReprotNo", detailModel.ReprotNo));//工资报表编号
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", detailModel.EmployeeID));//员工ID
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ItemNo", detailModel.ItemNo));//工资项编号
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ItemName", detailModel.ItemName ));//工资项名称
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ItemOrder", detailModel.ItemOrder ));//排列先后顺序
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PayFlag", detailModel.PayFlag ));//是否扣款
comm.Parameters.Add(SqlHelper.GetParameterFromString("@SalaryMoney", detailModel.SalaryMoney));//工资额
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ChangeFlag", detailModel.ChangeFlag ));//工资额
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", modifyUser));//更新用户ID
}
lstCommand.Add(comm);
}
}
/// <summary>
/// 更新报表基本信息
/// </summary>
/// <param name="model">报表基本信息</param>
/// <returns></returns>
private static SqlCommand UpdateSalaryReport(SalaryReportModel model)
{
#region SQL文拼写
StringBuilder updateSql = new StringBuilder();
updateSql.AppendLine(" UPDATE ");
updateSql.AppendLine(" officedba.SalaryReport ");
updateSql.AppendLine(" SET ReportName = @ReportName ");
updateSql.AppendLine(" ,ReportMonth = @ReportMonth ");
updateSql.AppendLine(" ,StartDate = @StartDate ");
updateSql.AppendLine(" ,EndDate = @EndDate ");
updateSql.AppendLine(" ,Status = @Status ");
updateSql.AppendLine(" ,ModifiedDate = getdate() ");
updateSql.AppendLine(" ,ModifiedUserID = @ModifiedUserID ");
updateSql.AppendLine(" WHERE ");
updateSql.AppendLine(" CompanyCD = @CompanyCD ");
updateSql.AppendLine(" AND ReprotNo = @ReprotNo ");
#endregion
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = updateSql.ToString();
//其他参数
model.Status = "1";
SetReportParameter(comm, model);
//执行更新
return comm;
}
/// <summary>
/// 保存工资报表信息
/// </summary>
/// <param name="lstDetail">明细信息</param>
/// <param name="lstSummary">合计信息</param>
/// <param name="model">基本信息</param>
/// <returns></returns>
public static bool SaveSalaryInfo(ArrayList lstDetail, ArrayList lstSummary, SalaryReportModel model)
{
//变量定义
ArrayList lstCommand = new ArrayList();
//工资报表信息
lstCommand.Add(UpdateSalaryReport(model));
//工资合计信息
UpdateSalaryReportSummary(model.CompanyCD, lstSummary, lstCommand);
//工资明细
UpdateSalaryReportDetail(model, lstDetail, lstCommand);
//执行保存操作
bool isSucc = SqlHelper.ExecuteTransWithArrayList(lstCommand);
return isSucc;
}
/// <summary>
/// 报表参数设置
/// </summary>
/// <param name="comm">命令</param>
/// <param name="model">报表</param>
private static void SetReportParameter(SqlCommand comm, SalaryReportModel model)
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));//企业代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReprotNo", model.ReprotNo));//工资报表编号
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReportName", model.ReportName));//工资报表主题
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReportMonth", model.ReportMonth));//工资月份
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", model.StartDate));//开始时间
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", model.EndDate));//结束时间
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", model.Creator));//创建人ID(对应员工表ID)
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", model.CreateDate));//编制日期
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Status", model.Status));//状态
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));//最后更新用户ID
}
/// <summary>
/// 登陆工资报表信息
/// </summary>
/// <param name="model">工资报表信息</param>
/// <returns></returns>
private static SqlCommand InsertSalaryReport(SalaryReportModel model)
{
#region SQL语句
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine(" INSERT INTO ");
insertSql.AppendLine(" officedba.SalaryReport ");
insertSql.AppendLine(" (CompanyCD ");
insertSql.AppendLine(" ,ReprotNo ");
insertSql.AppendLine(" ,ReportName ");
insertSql.AppendLine(" ,ReportMonth ");
insertSql.AppendLine(" ,StartDate ");
insertSql.AppendLine(" ,EndDate ");
insertSql.AppendLine(" ,Creator ");
insertSql.AppendLine(" ,CreateDate ");
insertSql.AppendLine(" ,Status ");
insertSql.AppendLine(" ,ModifiedDate ");
insertSql.AppendLine(" ,ModifiedUserID) ");
insertSql.AppendLine(" VALUES ");
insertSql.AppendLine(" (@CompanyCD ");
insertSql.AppendLine(" ,@ReprotNo ");
insertSql.AppendLine(" ,@ReportName ");
insertSql.AppendLine(" ,@ReportMonth ");
insertSql.AppendLine(" ,@StartDate ");
insertSql.AppendLine(" ,@EndDate ");
insertSql.AppendLine(" ,@Creator ");
insertSql.AppendLine(" ,@CreateDate ");
insertSql.AppendLine(" ,@Status ");
insertSql.AppendLine(" ,getdate() ");
insertSql.AppendLine(" ,@ModifiedUserID) ");
insertSql.AppendLine(" SET @ReportID= @@IDENTITY ");
#endregion
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
//设置存储过程名
comm.CommandText = insertSql.ToString();
//状态
model.Status = "0";
//设置保存的参数
SetReportParameter(comm, model);
//添加返回参数
comm.Parameters.Add(SqlHelper.GetOutputParameter("@ReportID", SqlDbType.Int));
//执行插入并返回插入结果
return comm;
}
