private void GetLRAccountData(ContractInfoBusiness contractInfoBusiness, string dateyyyymm) { var listLRInsureContModel = contractInfoBusiness.lstLRInsureContModel; var listStatement = contractInfoBusiness.lstInsuranceReinsuranceStatementModel; for (int i = 0; i < listStatement.Count; i++) { var model = listStatement[i]; try { LRInsureContModel lRInsureContModel = GetLRInsureContModel(listLRInsureContModel, model); if (lRInsureContModel == null) { throw new Exception("Get " + model.ToCompanyName + " information error"); } LRAccountModel lrAccountModel = new LRAccountModel(); var reinsurer = new Reinsurer().GetReinsurerInforByName(model.ToCompanyName); var reinsurerCode = reinsurer == null ? string.Empty : reinsurer.ReinsurerCode; lrAccountModel.TransactionNo = CommFuns.GetTransactionNo2(i + 1, dateyyyymm); lrAccountModel.CompanyCode = origanizationCode; lrAccountModel.AccountID = lRInsureContModel.MainReInsuranceContNo + dateyyyymm.Substring(0, 6);//账单编号 lrAccountModel.AccountingPeriodfrom = Convert.ToDateTime(dateyyyymm.Substring(0, 4) + "-" + dateyyyymm.Substring(4, 2) + "-01").ToString("yyyy/MM/dd"); lrAccountModel.AccountingPeriodto = Convert.ToDateTime(dateyyyymm.Substring(0, 4) + "-" + dateyyyymm.Substring(4, 2) + "-01").AddMonths(1).AddDays(-1).ToString("yyyy/MM/dd"); lrAccountModel.ReinsurerCode = reinsurerCode; lrAccountModel.ReinsurerName = reinsurer == null ? string.Empty : reinsurer.ReinsurerChineseName; lrAccountModel.ReInsuranceContNo = lRInsureContModel.MainReInsuranceContNo; //合同号码 lrAccountModel.ReInsuranceContName = lRInsureContModel.ReInsuranceContName; //合同名称 lrAccountModel.Currency = "156"; lrAccountModel.ReinsurancePremium = decimal.Round(decimal.Parse(model.Debit.ReinsurancePremiums), 2).ToString(); // lrAccountModel.ReinsuranceCommssionRate = "0.5"; //分保佣金、分保费50% lrAccountModel.ReinsuranceCommssion = decimal.Round(decimal.Parse(model.Credit.ReinsuranceCommissions), 2).ToString(); // lrAccountModel.ReturnReinsurancePremium = "0"; lrAccountModel.ReturnReinsuranceCommssion = "0"; lrAccountModel.ReturnSurrenderPay = "0"; lrAccountModel.ReturnClaimPay = decimal.Round(decimal.Parse(model.Credit.ReinsuranceClaimAmounts), 2).ToString(); lrAccountModel.ReturnMaturity = "0"; lrAccountModel.ReturnAnnuity = "0"; lrAccountModel.ReturnLivBene = "0"; lrAccountModel.AccountStatus = "1"; lrAccountModel.PairingStatus = "2"; lrAccountModel.PairingDate = lrAccountModel.AccountingPeriodto == null ? "" : Convert.ToDateTime(lrAccountModel.AccountingPeriodto).ToString("yyyy/MM/dd"); lrAccountModel.CurrentRate = "1"; listLRAccount.Add(lrAccountModel); } catch (Exception ex) { throw; } } }
public void WriteLRInsureContSheet(ContractInfoBusiness contractInfoBusiness, string OutPutFolderPath, string LastDateOfMonthyyyyMMdd) { IExcel excelApp = new ExcelCore.ExcelCore(); try { ProcessLogProxy.Normal("Start building LRInsureCont excel"); LastDateOfMonth = LastDateOfMonthyyyyMMdd; var excelPath = OutPutFolderPath + @"\TEMP_" + ExcelTemplateName.LRInsureCont + ".xlsx"; ExcelTemplate excelTemplate = new ExcelTemplate(); excelTemplate.CreateTemplate(excelApp, excelPath, ExcelTemplateName.LRInsureCont);//创建模板 //GetLRInsureContData(contractInfoBusiness);//得到需写入excel的数据 excelApp.OpenExcel(excelPath, false); for (int i = 0; i < contractInfoBusiness.lstLRInsureContModel.Count; i++) { var model = listLRInsureContModel[i]; excelApp.SetCellValue("Sheet1", i + 2, "A", CommFuns.GetTransactionNo2(i + 1, LastDateOfMonth)); excelApp.SetCellValue("Sheet1", i + 2, "B", model.CompanyCode); excelApp.SetCellValue("Sheet1", i + 2, "C", model.ReInsuranceContNo); excelApp.SetCellValue("Sheet1", i + 2, "D", model.ReInsuranceContName); excelApp.SetCellValue("Sheet1", i + 2, "E", model.ReInsuranceContTitle); excelApp.SetCellValue("Sheet1", i + 2, "F", model.MainReInsuranceContNo); excelApp.SetCellValue("Sheet1", i + 2, "G", model.ContOrAmendmentType); excelApp.SetCellValue("Sheet1", i + 2, "H", model.ContAttribute); excelApp.SetCellValue("Sheet1", i + 2, "I", model.ContStatus); excelApp.SetCellValue("Sheet1", i + 2, "J", model.TreatyOrFacultativeFlag); excelApp.SetCellValue("Sheet1", i + 2, "K", model.ContSigndate); excelApp.SetCellValue("Sheet1", i + 2, "L", model.PeriodFrom); excelApp.SetCellValue("Sheet1", i + 2, "M", model.PeriodTo); excelApp.SetCellValue("Sheet1", i + 2, "N", model.ContType); excelApp.SetCellValue("Sheet1", i + 2, "O", model.ReinsurerCode); excelApp.SetCellValue("Sheet1", i + 2, "P", model.ReinsurerName); excelApp.SetCellValue("Sheet1", i + 2, "Q", model.ChargeType); } excelApp.SetSheetAutoFit("Sheet1"); excelApp.Save(); excelApp.Close(); ProcessLogProxy.SuccessMessage("Build Success"); } catch (Exception ex) { ProcessLogProxy.Error(ex.Message); ProcessLogProxy.Error("Build fail"); } }
private void Test3(string yearMonthDay, int serialNumber, int rowIndex, HugeDisasterModel temp, int contractOrder, string productCode, string productName, string productType, string termType, ContractInfoBusiness business) { ZaiBaoProductInfo tempModel = new ZaiBaoProductInfo(); string currentTransactionNo = CommFuns.GetTransactionNo2(serialNumber, yearMonthDay); excelApp.SetCellValue(rowIndex, "A", currentTransactionNo); excelApp.SetCellValue(rowIndex, "B", origanizationCode); var tempEntity = reinsurer.GetReinsurerInforByName(temp.Reinsurer); var tempCompanyCode = tempEntity == null ? string.Empty : tempEntity.ReinsurerCode; //string currentReInsuranceContNo = "RICN" + tempCompanyCode + "M" // + contractOrder.ToString().PadLeft(2, '0') + "000"; var referenceEntity = business.lstLRInsureContModel.Where(e => e.ReInsuranceContName.Equals(temp.TreatyName) && e.ReinsurerCode.Equals(tempCompanyCode)).FirstOrDefault(); string currentReInsuranceContNo = referenceEntity == null ? string.Empty : referenceEntity.ReInsuranceContNo; //mainContractCode = referenceEntity == null ? string.Empty : referenceEntity.MainReInsuranceContNo; excelApp.SetCellValue(rowIndex, "C", currentReInsuranceContNo); excelApp.SetCellValue(rowIndex, "D", temp.TreatyName); excelApp.SetCellValue(rowIndex, "E", string.Empty); excelApp.SetCellValue(rowIndex, "F", currentReInsuranceContNo); excelApp.SetCellValue(rowIndex, "G", "1"); excelApp.SetCellValue(rowIndex, "H", productCode); excelApp.SetCellValue(rowIndex, "I", productName); //string tempGpfFlag = string.Empty; string productCodeFirstChar = string.Empty; if (!string.IsNullOrEmpty(productCode)) { productCodeFirstChar = productCode.Trim().Substring(0, 1); productCodeFirstChar = productCodeFirstChar.Equals("G") ? "02" : "01"; excelApp.SetCellValue(rowIndex, "J", productCodeFirstChar); } excelApp.SetCellValue(rowIndex, "K", productType); var tempCateGory = PersonalLiabilityCategory.LstCategory.Where(e => e.CategoryName.Equals(temp.BenefitReinsured)).FirstOrDefault(); //excelApp.SetCellValue(rowIndex, "L", liabilityCode); excelApp.SetCellValue(rowIndex, "L", tempCateGory == null ? string.Empty : tempCateGory.CategoryCode); excelApp.SetCellValue(rowIndex, "M", temp.BenefitReinsured); excelApp.SetCellValue(rowIndex, "N", tempCompanyCode); excelApp.SetCellValue(rowIndex, "O", tempEntity == null ? string.Empty : tempEntity.ReinsurerChineseName); //excelApp.SetCellValue(rowIndex, "O", temp.Reinsurer); excelApp.SetCellValue(rowIndex, "P", temp.RIratio); string tempMethodCode = this.GetRiMethodICodeByName(temp.RImethodI); excelApp.SetCellValue(rowIndex, "Q", tempMethodCode); excelApp.SetCellValue(rowIndex, "R", "01"); excelApp.SetCellValue(rowIndex, "S", termType); excelApp.SetCellValue(rowIndex, "T", "0"); excelApp.SetCellValue(rowIndex, "U", "0"); excelApp.SetCellValue(rowIndex, "V", "0"); tempModel.TransactionNo = currentTransactionNo; tempModel.CompanyCode = origanizationCode; tempModel.ReInsuranceContNo = currentReInsuranceContNo; tempModel.ReInsuranceContName = temp.TreatyName; tempModel.ReInsuranceContTitle = ConfigInformation.TextValue; tempModel.MainReInsuranceContNo = currentReInsuranceContNo; tempModel.ContOrAmendmentType = "1"; tempModel.ProductCode = productCode; tempModel.ProductName = productName; tempModel.GPFlag = productCodeFirstChar; tempModel.ProductType = productType; tempModel.LiabilityCode = tempCateGory == null ? string.Empty : tempCateGory.CategoryCode; tempModel.LiabilityName = temp.BenefitReinsured; tempModel.ReinsurerCode = tempCompanyCode; tempModel.ReinsurerName = temp.Reinsurer; tempModel.ReinsuranceShare = temp.RIratio; tempModel.ReinsurMode = tempMethodCode; tempModel.ReInsuranceType = "01"; tempModel.TermType = termType; tempModel.RetentionAmount = "0"; tempModel.RetentionPercentage = "0"; tempModel.QuotaSharePercentage = "0"; business.lstZaiBaoProductInfo.Add(tempModel); }
private void Test(string yearMonthDay, int serialNumber, int rowIndex, RIContractInfo temp, int contractOrder, int currentIndex, string productType, string liabilityCode, string termType, ContractInfoBusiness contractInfoBusiness, bool isMainContract = true) { ZaiBaoProductInfo tempModel = new ZaiBaoProductInfo(); string currentTransactionNo = CommFuns.GetTransactionNo2(serialNumber, yearMonthDay); excelApp.SetCellValue(rowIndex, "A", currentTransactionNo); excelApp.SetCellValue(rowIndex, "B", origanizationCode); var tempEntity = reinsurer.GetReinsurerInforByName(temp.Reinsurer); var tempCompanyCode = tempEntity == null ? string.Empty : tempEntity.ReinsurerCode; string currentReInsuranceContNo = string.Empty; string mainContractCode = string.Empty; #region 废代码 //string mainContractCode = "RICN" + tempCompanyCode + "M" // + contractOrder.ToString().PadLeft(2, '0') + "000"; //if (isMainContract) //{ // currentReInsuranceContNo = "RICN" + tempCompanyCode + temp.ContractTypeSign // + contractOrder.ToString().PadLeft(2, '0') + "000"; //} //else //{ // currentReInsuranceContNo = "RICN" + tempCompanyCode + temp.ContractTypeSign // + contractOrder.ToString().PadLeft(2, '0') + currentIndex.ToString().PadLeft(3, '0'); //} #endregion var referenceEntity = contractInfoBusiness.lstLRInsureContModel.Where(e => e.ReInsuranceContName.Equals(temp.TreatyName) && e.ReinsurerCode.Equals(tempCompanyCode)).FirstOrDefault(); currentReInsuranceContNo = referenceEntity == null ? string.Empty : referenceEntity.ReInsuranceContNo; mainContractCode = referenceEntity == null ? string.Empty : referenceEntity.MainReInsuranceContNo; excelApp.SetCellValue(rowIndex, "C", currentReInsuranceContNo); excelApp.SetCellValue(rowIndex, "D", temp.TreatyName); excelApp.SetCellValue(rowIndex, "E", string.Empty); excelApp.SetCellValue(rowIndex, "F", mainContractCode); string tempContractType = temp.ContractTypeSign.Equals("M") ? "1" : "2"; excelApp.SetCellValue(rowIndex, "G", tempContractType); excelApp.SetCellValue(rowIndex, "H", temp.ProductCode); excelApp.SetCellValue(rowIndex, "I", temp.ReinsurerName); //string tempGpfFlag = string.Empty; string productCodeFirstChar = string.Empty; if (!string.IsNullOrEmpty(temp.ProductCode)) { productCodeFirstChar = temp.ProductCode.Trim().Substring(0, 1); productCodeFirstChar = productCodeFirstChar.Equals("G") ? "02" : "01"; excelApp.SetCellValue(rowIndex, "J", productCodeFirstChar); } excelApp.SetCellValue(rowIndex, "K", productType); excelApp.SetCellValue(rowIndex, "L", liabilityCode); excelApp.SetCellValue(rowIndex, "M", temp.BenefitReinsured); excelApp.SetCellValue(rowIndex, "N", tempCompanyCode); //excelApp.SetCellValue(rowIndex, "O", temp.Reinsurer); excelApp.SetCellValue(rowIndex, "O", tempEntity == null ? string.Empty : tempEntity.ReinsurerChineseName); excelApp.SetCellValue(rowIndex, "P", temp.RIratio); string tempMethodCode = this.GetRiMethodICodeByName(temp.RImethodI); excelApp.SetCellValue(rowIndex, "Q", tempMethodCode); excelApp.SetCellValue(rowIndex, "R", "04"); excelApp.SetCellValue(rowIndex, "S", termType); string currentRetentionAmount = this.GetRetentionAmount(tempMethodCode, temp.Retention); excelApp.SetCellValue(rowIndex, "T", currentRetentionAmount); string currentRetentionPercentage = this.GetRetentionPercentage(tempMethodCode, temp.Retention); excelApp.SetCellValue(rowIndex, "U", currentRetentionPercentage); string QuotaSharePercentage = "0"; if (tempMethodCode.Equals("2") || tempMethodCode.Equals("3")) { QuotaSharePercentage = (1 - decimal.Parse(currentRetentionPercentage)).ToString("0.00"); } excelApp.SetCellValue(rowIndex, "V", QuotaSharePercentage); tempModel.TransactionNo = currentTransactionNo; tempModel.CompanyCode = origanizationCode; tempModel.ReInsuranceContNo = currentReInsuranceContNo; tempModel.ReInsuranceContName = temp.TreatyName; tempModel.ReInsuranceContTitle = ConfigInformation.TextValue; tempModel.MainReInsuranceContNo = mainContractCode; tempModel.ContOrAmendmentType = tempContractType; tempModel.ProductCode = temp.ProductCode; tempModel.ProductName = temp.ReinsurerName; tempModel.GPFlag = productCodeFirstChar; tempModel.ProductType = productType; tempModel.LiabilityCode = liabilityCode; tempModel.LiabilityName = temp.BenefitReinsured; tempModel.ReinsurerCode = tempCompanyCode; tempModel.ReinsurerName = temp.Reinsurer; tempModel.ReinsuranceShare = temp.RIratio; tempModel.ReinsurMode = tempMethodCode; tempModel.ReInsuranceType = "04"; tempModel.TermType = termType; tempModel.RetentionAmount = currentRetentionAmount; tempModel.RetentionPercentage = currentRetentionPercentage; tempModel.QuotaSharePercentage = QuotaSharePercentage; contractInfoBusiness.lstZaiBaoProductInfo.Add(tempModel); }