/// <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
});
}