コード例 #1
0
        //导出Excel
        public string PostExportToExcel(int supplierCostID, int serverCostID, string supplierName)
        {
            IBLL.ICOST_CostTableBLL      c_BLL        = new BLL.COST_CostTableBLL();
            List <Cost_CostTableDetails> supplierData = c_BLL.GetCostFeeDetailList(supplierCostID); //供应商
            List <Cost_CostTableDetails> serverData   = c_BLL.GetCostFeeDetailList(serverCostID);   //客服
            List <Cost_CostTableDetails> allData      = supplierData.Union(serverData).ToList();    //全部数据

            List <ErrorColumn> errorList = new List <ErrorColumn>();

            foreach (var item in allData.Where(e => e.Flag != "1"))                                                                                                                               //循环供应商上传的费用
            {
                var otherModel = allData.Find(e => (e.Employee_ID == item.Employee_ID && e.PaymentStyle == item.PaymentStyle && e.CityName == item.CityName && e.CreateFrom != item.CreateFrom)); //对应的数据

                if (otherModel != null)
                {
                    string empID = otherModel.Employee_ID.ToString();
                    otherModel.Flag = "1";//标识为已循环
                    Type           t1         = typeof(Cost_CostTableDetails);
                    PropertyInfo[] propertys1 = t1.GetProperties();
                    //比较费用
                    foreach (PropertyInfo pi in propertys1)
                    {
                        string name = pi.Name;
                        if (name != "CreateFrom" && name != "COST_CostTable_ID" && name != "Flag")
                        {
                            object serverValue   = t1.GetProperty(name).GetValue(otherModel, null) == null ? "" : t1.GetProperty(name).GetValue(otherModel, null);
                            object supplierValue = t1.GetProperty(name).GetValue(item, null) == null ? "" : t1.GetProperty(name).GetValue(item, null);
                            if (!serverValue.Equals(supplierValue))
                            {
                                ErrorColumn errorModel = new ErrorColumn();
                                errorModel.ID     = empID;
                                errorModel.Column = name;
                                errorList.Add(errorModel);
                            }
                        }
                    }
                }
                else//只存在一方的数据来源
                {
                    Type           t1         = typeof(Cost_CostTableDetails);
                    PropertyInfo[] propertys1 = t1.GetProperties();
                    foreach (PropertyInfo pi in propertys1)
                    {
                        string      name       = pi.Name;
                        ErrorColumn errorModel = new ErrorColumn();
                        errorModel.ID     = item.Employee_ID.ToString();
                        errorModel.Column = name;
                        errorList.Add(errorModel);
                    }
                }
            }
            string urlPath = string.Empty;

            if (errorList.Count != 0) //费用对比有差异
            {
                urlPath = ExportToExcel(allData, supplierName, errorList);
            }

            return(urlPath);
        }
コード例 #2
0
        public IHttpActionResult GetCompany(int id = 0, int billid = 0) //id为公司ID billid 为费用单ID
        {
            if (!IsValidation())
            {
                return(Json(new { code = -1, message = "验证未通过" }));
            }

            try
            {
                using (SysEntities db = new SysEntities())
                {
                    if (billid == 0)
                    {
                        string res = "{\"Id\":null,\"Remark\":null,\"BillDate\":null,\"Employees\":[{\"IDNumber\":\"123456789012345678\",\"Name\":\"刘腾飞\",\"Style\":\"正常\",\"Remark\":[{\"Name\":\"有问题说明的原因\"}],\"PayType\":\"补缴\",\"PensionCity\":\"杭州\",\"PensionSection\":\"2015-7~2015-8\",\"PensionMonth\":2,\"PensionCompanyWage\":\"5565\",\"PensionPersonWage\":\"3565\",\"PensionCompanyPercentage\":25,\"PensionPersonPercentage\":3,\"MedicalCity\":\"杭州\",\"MedicalSection\":\"2015-7~2015-8\",\"MedicalMonth\":2,\"MedicalCompanyWage\":\"5565\",\"MedicalPersonWage\":\"3565\",\"MedicalCompanyPercentage\":25,\"MedicalPersonPercentage\":3,\"WorkInjuryCity\":\"杭州\",\"WorkInjurySection\":\"2015-7~2015-8\",\"WorkInjuryMonth\":2,\"WorkInjuryCompanyWage\":\"5565\",\"WorkInjuryPersonWage\":\"3565\",\"WorkInjuryCompanyPercentage\":25,\"WorkInjuryPersonPercentage\":3,\"UnemploymentCity\":\"杭州\",\"UnemploymentSection\":\"2015-7~2015-8\",\"UnemploymentMonth\":2,\"UnemploymentCompanyWage\":\"5565\",\"UnemploymentPersonWage\":\"3565\",\"UnemploymentCompanyPercentage\":25,\"UnemploymentPersonPercentage\":3,\"MaternityCity\":\"杭州\",\"MaternitySection\":\"2015-7~2015-8\",\"MaternityMonth\":2,\"MaternityCompanyWage\":\"5565\",\"MaternityPersonWage\":\"3565\",\"MaternityCompanyPercentage\":25,\"MaternityPersonPercentage\":3,\"HousingFundCity\":\"杭州\",\"HousingFundSection\":\"2015-7~2015-8\",\"HousingFundMonth\":2,\"HousingFundCompanyWage\":\"5565\",\"HousingFundPersonWage\":\"3565\",\"HousingFundCompanyPercentage\":25,\"HousingFundPersonPercentage\":3,\"LateFees\":\"3565\",\"DisabledInsuranceFee\":\"3565\",\"HeatingFees\":\"3565\",\"MaterialFees\":\"3565\",\"ServiceFees\":\"3565\"}]}";
                        return(Json(new { code = 0, message = res }));
                    }
                    else
                    {
                        IBLL.ICOST_CostTableBLL m_BLL = new BLL.COST_CostTableBLL();
                        int total = 0;
                        List <CostFeeModel> queryData2 = m_BLL.GetCostFeeList(null, 1, int.MaxValue, null, null, 0, 0, ref total);


                        var res = (from a in queryData2
                                   where a.ID == billid
                                   select new
                        {
                            Id = a.ID,
                            Remark = a.Status,
                            BillDate = a.YearMonth,
                            Employees = (from s in m_BLL.GetCostFeeDetailList(a.ID)
                                         select new
                            {
                                IDNumber = s.CertificateNumber,
                                Name = s.EmployName,
                                Style = "",
                                Remark2 = "",
                                PayType = ((Common.EmployeeMiddle_PaymentStyle)s.PaymentStyle).ToString(),
                                PensionCity = s.CityName,
                                PensionSection = s.YanglaoPaymentInterval,
                                PensionMonth = s.YanglaoPaymentMonth,
                                PensionCompanyWage = s.YanglaoCompanyCost,
                                PensionPersonWage = s.YanglaoPersonCost,
                                PensionCompanyPercentage = s.YanglaoCompanyRadix,
                                PensionPersonPercentage = s.YanglaoPersonRatio,


                                MedicalCity = s.CityName,
                                MedicalSection = s.YiliaoPaymentInterval,
                                MedicalMonth = s.YiliaoPaymentMonth,
                                MedicalCompanyWage = s.YiliaoCompanyCost,
                                MedicalPersonWage = s.YiliaoPersonCost,
                                MedicalCompanyPercentage = s.YiliaoCompanyRadix,
                                MedicalPersonPercentage = s.YiliaoPersonRatio,



                                WorkInjuryCity = s.CityName,
                                WorkInjurySection = s.GongshangPaymentInterval,
                                WorkInjuryMonth = s.GongshangPaymentMonth,
                                WorkInjuryCompanyWage = s.GongshangCompanyCost,
                                WorkInjuryPersonWage = s.GongshangPersonCost,
                                WorkInjuryCompanyPercentage = s.GongshangCompanyRadix,
                                WorkInjuryPersonPercentage = s.GongshangPersonRatio,



                                UnemploymentCity = s.CityName,
                                UnemploymentSection = s.ShiyePaymentInterval,
                                UnemploymentMonth = s.ShiyePaymentMonth,
                                UnemploymentCompanyWage = s.ShiyeCompanyCost,
                                UnemploymentPersonWage = s.ShiyePersonCost,
                                UnemploymentCompanyPercentage = s.ShiyeCompanyRadix,
                                UnemploymentPersonPercentage = s.ShiyePersonRatio,



                                MaternityCity = s.CityName,
                                MaternitySection = s.ShengyuPaymentInterval,
                                MaternityMonth = s.ShengyuPaymentMonth,
                                MaternityCompanyWage = s.ShengyuCompanyCost,
                                MaternityPersonWage = s.ShengyuPersonCost,
                                MaternityCompanyPercentage = s.ShengyuCompanyRadix,
                                MaternityPersonPercentage = s.ShengyuPersonRatio,


                                HousingFundCity = s.CityName,
                                HousingFundSection = s.GongjijinPaymentInterval,
                                HousingFundMonth = s.GongjijinPaymentMonth,
                                HousingFundCompanyWage = s.GongjijinCompanyCost,
                                HousingFundPersonWage = s.GongjijinPersonCost,
                                HousingFundCompanyPercentage = s.GongjijinCompanyRadix,
                                HousingFundPersonPercentage = s.GongjijinPersonRatio,


                                OtherCost = s.OtherCost,
                                OtherInsuranceCost = s.OtherInsuranceCost,
                                MaterialFees = s.ProductionCost,
                                ServiceFees = s.ServiceCost,
                            }).ToList()
                        }).ToList();

                        return(Json(new { code = 0, message = res }));
                    }
                };
            }
            catch (Exception ee)
            {
                return(Json(new { code = -1, message = ee.ToString() }));
            }
        }
コード例 #3
0
        //导出费用对比Excel表
        private string ExportToExcel(List <Cost_CostTableDetails> queryData, string supplierName, List <ErrorColumn> errorList)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                queryData = queryData.OrderBy(o => o.EmployName).ThenBy(o => o.CreateFrom).ToList();
                IBLL.ICOST_CostTableBLL c_BLL = new BLL.COST_CostTableBLL();
                FileStream   file             = new FileStream(System.Web.HttpContext.Current.Server.MapPath("../../Template/Excel/费用对比导出模板.xls"), FileMode.Open, FileAccess.Read);
                HSSFWorkbook hssfworkbook     = new HSSFWorkbook(file);
                hssfworkbook.SetSheetName(0, "费用明细");

                ISheet sheet1 = hssfworkbook.GetSheetAt(0);
                InsertRowsdaili2(sheet1, 7, queryData.Count() - 1);

                //上色
                ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
                cellStyle.BorderBottom = BorderStyle.Thin;           //边框
                cellStyle.BorderLeft   = BorderStyle.Thin;
                cellStyle.Alignment    = HorizontalAlignment.Center; //水平居中
                IFont cellFont = hssfworkbook.CreateFont();
                cellFont.Color = (short)FontColor.Red;
                cellStyle.SetFont(cellFont);


                for (int i = 0; i < queryData.Count(); i++)
                {
                    List <ErrorColumn> singleEmployee = new List <ErrorColumn>();
                    string             employeeID     = queryData[i].Employee_ID.ToString();
                    singleEmployee = errorList.Where(o => o.ID == employeeID).ToList();
                    IRow row = sheet1.GetRow(7 + i);
                    row.GetCell(0).SetCellValue(((Common.CostTable_CreateFrom)queryData[i].CreateFrom).ToString());
                    row.GetCell(1).SetCellValue(queryData[i].EmployName);
                    row.GetCell(2).SetCellValue(queryData[i].CertificateNumber); // 身份证号
                    row.GetCell(3).SetCellValue(queryData[i].SupplierName);      // 供应商
                    string style = ((Common.EmployeeMiddle_PaymentStyle)queryData[i].PaymentStyle).ToString();
                    row.GetCell(4).SetCellValue(style);                          // 缴费类型
                    row.GetCell(5).SetCellValue(queryData[i].CityName);          // 缴纳地

                    #region 社保信息
                    // 养老
                    row.GetCell(6).SetCellValue(queryData[i].YanglaoPaymentInterval);
                    row.GetCell(7).SetCellValue(queryData[i].YanglaoPaymentMonth == null ? 0 : (int)queryData[i].YanglaoPaymentMonth);
                    row.GetCell(8).SetCellValue(queryData[i].YanglaoCompanyRadix == null ? 0 : (double)queryData[i].YanglaoCompanyRadix);
                    row.GetCell(9).SetCellValue((double)queryData[i].YanglaoCompanyCost);
                    row.GetCell(10).SetCellValue((double)queryData[i].YanglaoPersonCost);

                    // 失业
                    row.GetCell(12).SetCellValue(queryData[i].ShiyePaymentInterval);
                    row.GetCell(13).SetCellValue(queryData[i].ShiyePaymentMonth == null ? 0 : (int)queryData[i].ShiyePaymentMonth);
                    row.GetCell(14).SetCellValue(queryData[i].ShiyeCompanyRadix == null ? 0 : (double)queryData[i].ShiyeCompanyRadix);
                    row.GetCell(15).SetCellValue((double)queryData[i].ShiyeCompanyCost);
                    row.GetCell(16).SetCellValue((double)queryData[i].ShiyePersonCost);

                    // 工伤
                    row.GetCell(18).SetCellValue(queryData[i].GongshangPaymentInterval);
                    row.GetCell(19).SetCellValue(queryData[i].GongshangCompanyRadix == null ? 0 : (double)queryData[i].GongshangCompanyRadix);
                    row.GetCell(20).SetCellValue((double)queryData[i].GongshangCompanyCost);

                    // 医疗
                    row.GetCell(21).SetCellValue(queryData[i].YiliaoPaymentInterval);
                    row.GetCell(22).SetCellValue(queryData[i].YiliaoPaymentMonth == null ? 0 : (int)queryData[i].YiliaoPaymentMonth);
                    row.GetCell(23).SetCellValue(queryData[i].YiliaoCompanyRadix == null ? 0 : (double)queryData[i].YiliaoCompanyRadix);
                    row.GetCell(24).SetCellValue((double)queryData[i].YiliaoCompanyCost);
                    row.GetCell(25).SetCellValue((double)queryData[i].YiliaoPersonCost);

                    // 大病
                    row.GetCell(27).SetCellValue((double)queryData[i].DaeCompanyCost);
                    row.GetCell(28).SetCellValue((double)queryData[i].DaePersonCost);

                    // 生育
                    row.GetCell(29).SetCellValue((double)queryData[i].ShengyuCompanyCost);

                    // 公积金
                    row.GetCell(30).SetCellValue(queryData[i].GongjijinPaymentInterval);
                    row.GetCell(31).SetCellValue(queryData[i].GongjijinPaymentMonth == null ? 0 : (int)queryData[i].GongjijinPaymentMonth);
                    row.GetCell(32).SetCellValue(queryData[i].GongjijinCompanyRadix == null ? 0 : (double)queryData[i].GongjijinCompanyRadix);
                    row.GetCell(33).SetCellValue((double)queryData[i].GongjijinCompanyCost);
                    row.GetCell(34).SetCellValue((double)queryData[i].GongjijinPersonCost);

                    // 补充公积金
                    row.GetCell(36).SetCellValue((double)queryData[i].GongjijinBCCompanyCost);
                    row.GetCell(37).SetCellValue((double)queryData[i].GongjijinBCPersonCost);

                    row.GetCell(38).SetCellValue((double)queryData[i].OtherInsuranceCost); // 其他社保费用
                    row.GetCell(39).SetCellValue((double)queryData[i].OtherCost);          // 其他费用

                    row.GetCell(43).SetCellValue((double)queryData[i].ProductionCost);     // 工本费
                    row.GetCell(44).SetCellValue((double)queryData[i].ServiceCost);        // 服务费
                    #endregion
                    // 循环给不一致数据上色标红
                    List <ExcelVSList> excelList = GetExcelVSList();
                    for (int k = 0; k < excelList.Count; k++)
                    {
                        if (singleEmployee.Select(o => o.Column).ToArray().Contains(excelList[k].Column))
                        {
                            row.GetCell(excelList[k].Cell).CellStyle = cellStyle;
                        }
                    }
                }
                IRow LastRow = sheet1.CreateRow(9 + queryData.Count());
                LastRow.CreateCell(0);
                LastRow.GetCell(0).SetCellValue("");
                sheet1.ForceFormulaRecalculation = true;
                string fileName = supplierName + "_" + "费用对比差异详情" + ".xls";
                string urlPath  = "/DataExport/" + fileName;                                     // 文件下载的URL地址,供给前台下载
                string filePath = System.Web.HttpContext.Current.Server.MapPath("\\" + urlPath); // 文件路径

                file = new FileStream(filePath, FileMode.Create);
                hssfworkbook.Write(file);
                file.Close();

                return(urlPath);  // 导出成功
            }
        }