/// <summary> /// 根据城市编码和保险公司编号 获取各返点参数列表 /// </summary> /// <param name="insurecode"></param> /// <param name="citycode"></param> /// <returns></returns> public Model.FX_PolicyInsurePara GetPolicyInsurePara(string insurecode, string citycode) { Model.FX_PolicyInsurePara mop = new Model.FX_PolicyInsurePara(); DataTable dt = new BLL.FX_InsureParaDetail().GetList(insurecode, citycode).Tables[0]; if (dt.Rows.Count <= 0) { return(null); } mop.BusinessTotal = Convert.ToDecimal(dt.Rows[0]["ParaValue"]); //商业总反点 mop.BusinessCommission = Convert.ToDecimal(dt.Rows[1]["ParaValue"]); //商业直接销售反点 2-6 mop.BusinessCommissionLevel2 = Convert.ToDecimal(dt.Rows[2]["ParaValue"]); //商业二级销售反点3-7 mop.BusinessCommissionLevel1 = Convert.ToDecimal(dt.Rows[3]["ParaValue"]); //商业顶级反点4-8 mop.CompulsoryTotal = Convert.ToDecimal(dt.Rows[4]["ParaValue"]); //交强险总返点 mop.CompulsoryCommission = Convert.ToDecimal(dt.Rows[5]["ParaValue"]); //交强险直接返点2-6 mop.CompulsoryCommissionLelve2 = Convert.ToDecimal(dt.Rows[6]["ParaValue"]); //交强险二级返点3-7 mop.CompulsoryCommissionLelve1 = Convert.ToDecimal(dt.Rows[7]["ParaValue"]); //交强险顶级返点-8 mop.BusinessTax = Convert.ToDecimal(dt.Rows[8]["ParaValue"]); //商业险税点 mop.CompulsoryTax = Convert.ToDecimal(dt.Rows[9]["ParaValue"]); //交强险税点 mop.CreateTime = DateTime.Now; mop.InsureCode = insurecode; return(mop); }
/// <summary> /// 添加保单 /// </summary> /// <param name="model"></param> /// <param name="policyDetailList"></param> /// <returns></returns> public bool AddPolicyList(Model.FX_Policy model, List <Model.FX_PolicyDetail> policyDetailList) { using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { #region 新增保单===主表 StringBuilder strSql = new StringBuilder(); strSql.Append("insert into FX_Policy("); strSql.Append("AgentId,CustomerId,VIN,DrivingLicense,DrivingLicensePic,CarType,OrganizationCode,InsureCode,FX_PolicyNo,PolicyNoCompulsory,PolicyNoBusiness,PolicyType,CreateTime,PayTime,OfferTime,OfferNum,PolicyState,PayState,PayUrl,PayValidateCode,PaydealCode,PolicyAmount,CompulsoryAmount,BusinessAmount,VehicleAndVesselTax,StartTime,EndTime,CityCode,Discount,UserId,Remark,CancelRemark,RejectNum,RejectTime,CustomerName,CustomerMoblie,CustomerIdNo,CustomerIdPic,CarModel,CarNo,CarEngineNo,CarRegisterDate,IsRead,SafetyFactor,DangerousCount,InquiryDate,ComeFrom,CZBTbCustomerID)"); strSql.Append(" values ("); strSql.Append("@AgentId,@CustomerId,@VIN,@DrivingLicense,@DrivingLicensePic,@CarType,@OrganizationCode,@InsureCode,@FX_PolicyNo,@PolicyNoCompulsory,@PolicyNoBusiness,@PolicyType,@CreateTime,@PayTime,@OfferTime,@OfferNum,@PolicyState,@PayState,@PayUrl,@PayValidateCode,@PaydealCode,@PolicyAmount,@CompulsoryAmount,@BusinessAmount,@VehicleAndVesselTax,@StartTime,@EndTime,@CityCode,@Discount,@UserId,@Remark,@CancelRemark,@RejectNum,@RejectTime,@CustomerName,@CustomerMoblie,@CustomerIdNo,@CustomerIdPic,@CarModel,@CarNo,@CarEngineNo,@CarRegisterDate,@IsRead,@SafetyFactor,@DangerousCount,@InquiryDate,@ComeFrom,@CZBTbCustomerID)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@AgentId", SqlDbType.Int, 4), new SqlParameter("@CustomerId", SqlDbType.Int, 4), new SqlParameter("@VIN", SqlDbType.NVarChar, 50), new SqlParameter("@DrivingLicense", SqlDbType.NVarChar, 50), new SqlParameter("@DrivingLicensePic", SqlDbType.NVarChar, -1), new SqlParameter("@CarType", SqlDbType.Int, 4), new SqlParameter("@OrganizationCode", SqlDbType.NVarChar, 50), new SqlParameter("@InsureCode", SqlDbType.NVarChar, 50), new SqlParameter("@FX_PolicyNo", SqlDbType.NVarChar, 50), new SqlParameter("@PolicyNoCompulsory", SqlDbType.NVarChar, 50), new SqlParameter("@PolicyNoBusiness", SqlDbType.NVarChar, 50), new SqlParameter("@PolicyType", SqlDbType.Int, 4), new SqlParameter("@CreateTime", SqlDbType.DateTime), new SqlParameter("@PayTime", SqlDbType.DateTime), new SqlParameter("@OfferTime", SqlDbType.DateTime), new SqlParameter("@OfferNum", SqlDbType.Int, 4), new SqlParameter("@PolicyState", SqlDbType.Int, 4), new SqlParameter("@PayState", SqlDbType.Int, 4), new SqlParameter("@PayUrl", SqlDbType.NVarChar, -1), new SqlParameter("@PayValidateCode", SqlDbType.NVarChar, 50), new SqlParameter("@PaydealCode", SqlDbType.NVarChar, 50), new SqlParameter("@PolicyAmount", SqlDbType.Decimal, 9), new SqlParameter("@CompulsoryAmount", SqlDbType.Decimal, 9), new SqlParameter("@BusinessAmount", SqlDbType.Decimal, 9), new SqlParameter("@VehicleAndVesselTax", SqlDbType.Decimal, 9), new SqlParameter("@StartTime", SqlDbType.DateTime), new SqlParameter("@EndTime", SqlDbType.DateTime), new SqlParameter("@CityCode", SqlDbType.NVarChar, 50), new SqlParameter("@Discount", SqlDbType.Decimal, 9), new SqlParameter("@UserId", SqlDbType.Int, 4), new SqlParameter("@Remark", SqlDbType.NVarChar, 100), new SqlParameter("@CancelRemark", SqlDbType.NVarChar, 100), new SqlParameter("@RejectNum", SqlDbType.Int, 4), new SqlParameter("@RejectTime", SqlDbType.DateTime), new SqlParameter("@CustomerName", SqlDbType.NVarChar, 50), new SqlParameter("@CustomerMoblie", SqlDbType.NVarChar, 50), new SqlParameter("@CustomerIdNo", SqlDbType.NVarChar, 50), new SqlParameter("@CustomerIdPic", SqlDbType.NVarChar, -1), new SqlParameter("@CarModel", SqlDbType.NVarChar, 50), new SqlParameter("@CarNo", SqlDbType.NVarChar, 50), new SqlParameter("@CarEngineNo", SqlDbType.NVarChar, 100), new SqlParameter("@CarRegisterDate", SqlDbType.DateTime), new SqlParameter("@IsRead", SqlDbType.Bit, 1), new SqlParameter("@SafetyFactor", SqlDbType.Decimal, 9), new SqlParameter("@DangerousCount", SqlDbType.Int, 4), new SqlParameter("@InquiryDate", SqlDbType.DateTime), new SqlParameter("@ComeFrom", SqlDbType.Int, 4), new SqlParameter("@CZBTbCustomerID", SqlDbType.NVarChar, 50) }; parameters[0].Value = model.AgentId; parameters[1].Value = model.CustomerId; parameters[2].Value = model.VIN; parameters[3].Value = model.DrivingLicense; parameters[4].Value = model.DrivingLicensePic; parameters[5].Value = model.CarType; parameters[6].Value = model.OrganizationCode; parameters[7].Value = model.InsureCode; parameters[8].Value = model.FX_PolicyNo; parameters[9].Value = model.PolicyNoCompulsory; parameters[10].Value = model.PolicyNoBusiness; parameters[11].Value = model.PolicyType; parameters[12].Value = model.CreateTime; parameters[13].Value = model.PayTime; parameters[14].Value = model.OfferTime; parameters[15].Value = model.OfferNum; parameters[16].Value = model.PolicyState; parameters[17].Value = model.PayState; parameters[18].Value = model.PayUrl; parameters[19].Value = model.PayValidateCode; parameters[20].Value = model.PaydealCode; parameters[21].Value = model.PolicyAmount; parameters[22].Value = model.CompulsoryAmount; parameters[23].Value = model.BusinessAmount; parameters[24].Value = model.VehicleAndVesselTax; parameters[25].Value = model.StartTime; parameters[26].Value = model.EndTime; parameters[27].Value = model.CityCode; parameters[28].Value = model.Discount; parameters[29].Value = model.UserId; parameters[30].Value = model.Remark; parameters[31].Value = model.CancelRemark; parameters[32].Value = model.RejectNum; parameters[33].Value = model.RejectTime; parameters[34].Value = model.CustomerName; parameters[35].Value = model.CustomerMoblie; parameters[36].Value = model.CustomerIdNo; parameters[37].Value = model.CustomerIdPic; parameters[38].Value = model.CarModel; parameters[39].Value = model.CarNo; parameters[40].Value = model.CarEngineNo; parameters[41].Value = model.CarRegisterDate; parameters[42].Value = model.IsRead; parameters[43].Value = model.SafetyFactor; parameters[44].Value = model.DangerousCount; parameters[45].Value = model.InquiryDate; parameters[46].Value = model.ComeFrom; parameters[47].Value = model.CZBTbCustomerID; object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), parameters); if (obj == null) { trans.Rollback(); return(false); } else { model.PolicyId = Convert.ToInt32(obj); } #endregion #region 具体险种列表 foreach (Model.FX_PolicyDetail p in policyDetailList) { StringBuilder _strSql = new StringBuilder(); _strSql.Append("insert into FX_PolicyDetail("); _strSql.Append("PolicyId,InsuranceTypeId,InsuranceTypeDetail,TotalAmount,CreateTime,UpdateTime,IsUse)"); _strSql.Append(" values ("); _strSql.Append("@PolicyId,@InsuranceTypeId,@InsuranceTypeDetail,@TotalAmount,@CreateTime,@UpdateTime,@IsUse)"); _strSql.Append(";select @@IDENTITY"); SqlParameter[] _parameters = { new SqlParameter("@PolicyId", SqlDbType.Int, 4), new SqlParameter("@InsuranceTypeId", SqlDbType.Int, 4), new SqlParameter("@InsuranceTypeDetail", SqlDbType.NVarChar, 100), new SqlParameter("@TotalAmount", SqlDbType.Decimal, 9), new SqlParameter("@CreateTime", SqlDbType.DateTime), new SqlParameter("@UpdateTime", SqlDbType.DateTime), new SqlParameter("@IsUse", SqlDbType.Int, 4) }; _parameters[0].Value = model.PolicyId; _parameters[1].Value = p.InsuranceTypeId; _parameters[2].Value = p.InsuranceTypeDetail; _parameters[3].Value = p.TotalAmount; _parameters[4].Value = p.CreateTime; _parameters[5].Value = p.UpdateTime; _parameters[6].Value = p.IsUse; DbHelperSQL.GetSingle(conn, trans, _strSql.ToString(), _parameters); } #endregion #region ===分佣参数表 if (model.PolicyInsurePara != null) { Model.FX_PolicyInsurePara modelinsuerpara = model.PolicyInsurePara; StringBuilder strSqlInsurePara = new StringBuilder(); strSqlInsurePara.Append("insert into FX_PolicyInsurePara("); strSqlInsurePara.Append("PolicyID,InsureCode,BusinessTotal,BusinessCommission,BusinessCommissionLevel1,BusinessCommissionLevel2,BusinessTax,CompulsoryTotal,CompulsoryCommission,CompulsoryCommissionLelve1,CompulsoryCommissionLelve2,CompulsoryTax,CreateTime)"); strSqlInsurePara.Append(" values ("); strSqlInsurePara.Append("@PolicyID,@InsureCode,@BusinessTotal,@BusinessCommission,@BusinessCommissionLevel1,@BusinessCommissionLevel2,@BusinessTax,@CompulsoryTotal,@CompulsoryCommission,@CompulsoryCommissionLelve1,@CompulsoryCommissionLelve2,@CompulsoryTax,@CreateTime)"); strSqlInsurePara.Append(";select @@IDENTITY"); SqlParameter[] parametersInsurePara = { new SqlParameter("@PolicyID", SqlDbType.Int, 4), new SqlParameter("@InsureCode", SqlDbType.NVarChar, 50), new SqlParameter("@BusinessTotal", SqlDbType.Decimal, 9), new SqlParameter("@BusinessCommission", SqlDbType.Decimal, 9), new SqlParameter("@BusinessCommissionLevel1", SqlDbType.Decimal, 9), new SqlParameter("@BusinessCommissionLevel2", SqlDbType.Decimal, 9), new SqlParameter("@BusinessTax", SqlDbType.Decimal, 9), new SqlParameter("@CompulsoryTotal", SqlDbType.Decimal, 9), new SqlParameter("@CompulsoryCommission", SqlDbType.Decimal, 9), new SqlParameter("@CompulsoryCommissionLelve1", SqlDbType.Decimal, 9), new SqlParameter("@CompulsoryCommissionLelve2", SqlDbType.Decimal, 9), new SqlParameter("@CompulsoryTax", SqlDbType.Decimal, 9), new SqlParameter("@CreateTime", SqlDbType.DateTime) }; parametersInsurePara[0].Value = model.PolicyId; parametersInsurePara[1].Value = model.InsureCode; parametersInsurePara[2].Value = modelinsuerpara.BusinessTotal; parametersInsurePara[3].Value = modelinsuerpara.BusinessCommission; parametersInsurePara[4].Value = modelinsuerpara.BusinessCommissionLevel1; parametersInsurePara[5].Value = modelinsuerpara.BusinessCommissionLevel2; parametersInsurePara[6].Value = modelinsuerpara.BusinessTax; parametersInsurePara[7].Value = modelinsuerpara.CompulsoryTotal; parametersInsurePara[8].Value = modelinsuerpara.CompulsoryCommission; parametersInsurePara[9].Value = modelinsuerpara.CompulsoryCommissionLelve1; parametersInsurePara[10].Value = modelinsuerpara.CompulsoryCommissionLelve2; parametersInsurePara[11].Value = modelinsuerpara.CompulsoryTax; parametersInsurePara[12].Value = modelinsuerpara.CreateTime; DbHelperSQL.GetSingle(conn, trans, strSqlInsurePara.ToString(), parametersInsurePara); } #endregion trans.Commit(); } catch (Exception err) { trans.Rollback(); Common.LogHelper.WriteLog(Common.Enums.LogEnum.Error, "err:" + err.Message); return(false); } } } return(true); }