/// <summary> /// 是否可以自动付款 /// </summary> /// <param name="parameter"></param> /// <returns></returns> public JsonResult <dynamic> IsCanAutoPayment(GenerateOrderParameter parameter) { //判断待办与正常的参保人所有所要缴纳金额+本次金额之和与账户金额作比较 //待办与正常的参保人所有所要缴纳金额 decimal totalAmount = _socialSecurityService.GetMonthTotalAmountByMemberID(parameter.MemberID); //本次订单金额 string SocialSecurityPeopleIDsStr = string.Join(",", parameter.SocialSecurityPeopleIDS); string sqlstr = $@"select SUM(ISNULL(dbo.SocialSecurity.SocialSecurityBase*SocialSecurity.PayProportion/100,0)*ISNULL(dbo.SocialSecurity.PayMonthCount,0)+ ISNULL(dbo.AccumulationFund.AccumulationFundBase*AccumulationFund.PayProportion/100,0)*ISNULL(dbo.AccumulationFund.PayMonthCount,0)) from dbo.SocialSecurityPeople left join dbo.SocialSecurity on SocialSecurityPeople.SocialSecurityPeopleID = SocialSecurity.SocialSecurityPeopleID left join dbo.AccumulationFund on SocialSecurityPeople.SocialSecurityPeopleID=AccumulationFund.SocialSecurityPeopleID where SocialSecurityPeople.SocialSecurityPeopleID in({SocialSecurityPeopleIDsStr})"; decimal SSAF_Amount = DbHelper.QuerySingle <decimal>(sqlstr); int SSNum = DbHelper.QuerySingle <int>($@"select count(1) from SocialSecurityPeople left join SocialSecurity on SocialSecurityPeople.SocialSecurityPeopleID = SocialSecurity.SocialSecurityPeopleID where SocialSecurityPeople.SocialSecurityPeopleID in({SocialSecurityPeopleIDsStr})"); decimal SSBacklogCost = DbHelper.QuerySingle <decimal>("select BacklogCost from CostParameterSetting where Status =0 ") * SSNum; int AFNum = DbHelper.QuerySingle <int>($@"select count(1) from SocialSecurityPeople left join AccumulationFund on SocialSecurityPeople.SocialSecurityPeopleID = AccumulationFund.SocialSecurityPeopleID where SocialSecurityPeople.SocialSecurityPeopleID in({SocialSecurityPeopleIDsStr})"); decimal AFBacklogCost = DbHelper.QuerySingle <decimal>("select BacklogCost from CostParameterSetting where Status =1 ") * SSNum; decimal ThisAmount = SSAF_Amount + SSBacklogCost + AFBacklogCost; decimal AccountAmount = DbHelper.QuerySingle <decimal>($"select ISNULL(Account,0) from Members where MemberID = {parameter.MemberID}"); if (totalAmount + ThisAmount < AccountAmount) { return new JsonResult <dynamic> { status = true, Message = "可以自动扣款" } } ; else { return new JsonResult <dynamic> { status = false, Message = "不可以自动扣款" } }; }
public JsonResult <dynamic> GenerateOrder(GenerateOrderParameter parameter) { //首先判断是否有未支付订单,若有,则不能生成订单 if (_orderService.IsExistsWaitingPayOrderByMemberID(parameter.MemberID)) { return(new JsonResult <dynamic> { status = false, Message = "有未支付的订单,请先进行支付" }); } string SocialSecurityPeopleIDStr = string.Join(",", parameter.SocialSecurityPeopleIDS); //判断所选参保人中有没有超过14号的 string sqlstr = $"select * from SocialSecurity where SocialSecurityPeopleID in({SocialSecurityPeopleIDStr})"; List <SocialSecurity> socialSecurityList = DbHelper.Query <SocialSecurity>(sqlstr); foreach (var socialSecurity in socialSecurityList) { if (socialSecurity.PayTime.Month < DateTime.Now.Month || (socialSecurity.PayTime.Month == DateTime.Now.Month && socialSecurity.PayTime.Day > 13)) { return(new JsonResult <dynamic> { status = false, Message = "参保人日期已失效,请修改" }); } } string sqlstr1 = $"select * from AccumulationFund where SocialSecurityPeopleID in({SocialSecurityPeopleIDStr})"; List <AccumulationFund> accumulationFundList = DbHelper.Query <AccumulationFund>(sqlstr1); foreach (var accumulationFund in accumulationFundList) { if (accumulationFund.PayTime.Month < DateTime.Now.Month || (accumulationFund.PayTime.Month == DateTime.Now.Month && accumulationFund.PayTime.Day > 13)) { return(new JsonResult <dynamic> { status = false, Message = "参保人日期已失效,请修改" }); } } ////参保人ID数组 //string[] SocialSecurityPeopleIDS = HttpContext.Current.Request.Form.GetValues("SocialSecurityPeopleIDS"); ////用户ID //int MemberID = Convert.ToInt32(HttpContext.Current.Request.Form["MemberID"]); string orderCode = DateTime.Now.ToString("yyyyMMddHHmmssfff") + new Random().Next(1000).ToString().PadLeft(3, '0'); Dictionary <bool, string> dic = _orderService.GenerateOrder(SocialSecurityPeopleIDStr, parameter.MemberID, orderCode); return(new JsonResult <dynamic> { status = dic.First().Key, Message = dic.First().Key ? "订单生成成功!" : "订单生成失败!", Data = dic.First().Value }); }
public JsonResult <dynamic> AutoPayment(GenerateOrderParameter parameter) { Dictionary <bool, string> dic = null; using (TransactionScope transaction = new TransactionScope()) { try { #region 生成订单 string SocialSecurityPeopleIDStr = string.Join(",", parameter.SocialSecurityPeopleIDS); string orderCode = DateTime.Now.ToString("yyyyMMddHHmmssfff") + new Random().Next(1000).ToString().PadLeft(3, '0'); dic = _orderService.GenerateOrder(SocialSecurityPeopleIDStr, parameter.MemberID, orderCode); #endregion if (!dic.First().Key) { throw new Exception("自动扣款失败"); } string sqlOrderDetail = $"select * from OrderDetails where OrderCode ={dic.First().Value}"; List <OrderDetails> orderDetailList = DbHelper.Query <OrderDetails>(sqlOrderDetail); string sqlAccountRecord = ""; string sqlSocialSecurityPeople = ""; //支出记录 foreach (var orderDetail in orderDetailList) { sqlSocialSecurityPeople += $"update SocialSecurityPeople set IsPay=1 where SocialSecurityPeopleID ={orderDetail.SocialSecurityPeopleID};"; sqlAccountRecord += orderDetail.SocialSecurityFirstBacklogCost != 0 ? $"insert into AccountRecord(MemberID,SocialSecurityPeopleID,SocialSecurityPeopleName,ShouZhiType,LaiYuan,OperationType,Cost,CreateTime) values({parameter.MemberID},{orderDetail.SocialSecurityPeopleID},'{orderDetail.SocialSecurityPeopleName}','支出','余额','社保代办',{orderDetail.SocialSecurityFirstBacklogCost},getdate());" : string.Empty; sqlAccountRecord += orderDetail.AccumulationFundFirstBacklogCost != 0 ? $"insert into AccountRecord(MemberID,SocialSecurityPeopleID,SocialSecurityPeopleName,ShouZhiType,LaiYuan,OperationType,Cost,CreateTime) values({parameter.MemberID},{orderDetail.SocialSecurityPeopleID},'{orderDetail.SocialSecurityPeopleName}','支出','余额','公积金代办',{orderDetail.AccumulationFundFirstBacklogCost},getdate());" : string.Empty; } //更新未参保人的支付状态 DbHelper.ExecuteSqlCommand(sqlSocialSecurityPeople, null); //更新记录 DbHelper.ExecuteSqlCommand(sqlAccountRecord, null); //更新订单 string sqlUpdateOrder = $"update [Order] set Status = {(int)OrderEnum.completed},PaymentMethod='余额扣款',PayTime=getdate() where OrderCode={dic.First().Value}"; DbHelper.ExecuteSqlCommand(sqlUpdateOrder, null); transaction.Complete(); } catch (Exception ex) { return(new JsonResult <dynamic> { status = false, Message = "自动扣款失败" }); } finally { transaction.Dispose(); } } return(new JsonResult <dynamic> { status = true, Message = "自动扣款成功", Data = dic.First <KeyValuePair <bool, string> >().Value }); }