/// <summary> /// 查询用户租赁信息 /// </summary> /// <param name="Row"></param> /// <returns></returns> public string RentalHistoryList(DataRow Row) { string backstr = ""; DataTable dt = null; try { string ConnStr = ConnectionDb.GetUnifiedConnectionString(); string UserId = Row["UserId"].ToString(); IDbConnection com = new SqlConnection(ConnStr); //Tb_User User = com.Query<Tb_User>("select * from Tb_User where Id=@Id", new { Id = UserId }).ToList<Tb_User>().SingleOrDefault(); //if (User==null) //{ // return JSONHelper.FromString(false, "该用户不存在"); //} int page = AppGlobal.StrToInt(Row["Page"].ToString()); int row = AppGlobal.StrToInt(Row["PageSize"].ToString()); dt = BaseGetData.GetList(ConnStr, " and UserId='" + UserId + "'", page, row, "RegisterDate", 0, "View_Tb_Rental", "Id").Tables[0]; } catch (Exception ex) { backstr = ex.Message; } if (backstr == "") { return(JSONHelper.FromString(dt)); } else { return(JSONHelper.FromString(false, backstr)); } }
/// <summary> /// 配置支付请求参数,根据当前商家获取 /// </summary> /// <param name="BussId"></param> public bool GenerateConfig(string BussId, out SDKConfig con) { IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection()); string query = "SELECT * FROM Tb_UnionPayCertificate WHERE BussId=@BussId"; Model.Model.Buss.Tb_UnionPayCertificate T = conn.Query <Model.Model.Buss.Tb_UnionPayCertificate>(query, new { BussId = BussId }).SingleOrDefault(); SDKConfig c = new SDKConfig(); con = c; if (T == null) { return(false); } else { //获取配置参数 con.signCertPath = T.signCertPath.ToString(); con.signCertPwd = T.signCertPwd.ToString(); con.validateCertDir = T.validateCertDir.ToString(); con.encryptCert = T.encryptCert.ToString(); con.merId = T.merId.ToString(); con.AccNo = T.accNo.ToString(); return(true); } }
/// <summary> /// 获取当前可用的优惠券余额 /// </summary> public string GetCanUseCouponBalanceInBuss(DataRow row) { if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString())) { return(JSONHelper.FromString(false, "用户id不能为空")); } if (!row.Table.Columns.Contains("CorpId") || string.IsNullOrEmpty(row["CorpId"].ToString())) { return(JSONHelper.FromString(false, "物业公司id不能为空")); } if (!row.Table.Columns.Contains("BussId") || string.IsNullOrEmpty(row["BussId"].ToString())) { return(JSONHelper.FromString(false, "商家id不能为空")); } using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { DataTable dt = conn.ExecuteReader(@"SELECT CorpId,BussId,isnull(Balance,0) as Balance FROM Tb_Resources_CouponBalance WHERE UserId=@UserId AND CorpId=@CorpId AND (BussId=@BussId OR BussId IS NULL)", new { UserId = row["UserId"], CorpId = row["CorpId"], BussId = row["BussId"] }).ToDataSet().Tables[0]; return(JSONHelper.FromString(dt)); } }
/// <summary> /// 优惠券使用历史 /// </summary> public string GetCouponUsedHistoryList(DataRow row) { if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString())) { return(JSONHelper.FromString(false, "用户id不能为空")); } int pageCount = 0; int Counts = 0; int PageIndex = 1; int PageSize = 10; if (row.Table.Columns.Contains("PageIndex") && AppGlobal.StrToInt(row["PageIndex"].ToString()) > 0) { PageIndex = AppGlobal.StrToInt(row["PageIndex"].ToString()); } if (row.Table.Columns.Contains("PageSize") && AppGlobal.StrToInt(row["PageSize"].ToString()) > 0) { PageSize = AppGlobal.StrToInt(row["PageSize"].ToString()); } using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { string sql = @"SELECT * FROM ( select R.Id, R.Amount, B.BussName,R.ReceiptDate,R.PayDate, OffsetMoney=(SELECT sum(isnull(x.OffsetMoney,0)) FROM Tb_Charge_ReceiptDetail x WHERE x.ReceiptCode=R.Id) FROM Tb_Charge_Receipt as R left join Tb_System_BusinessCorp as B on b.BussId = R.BussId WHERE R.UserId='" + row["UserId"].ToString() + @"' ) t WHERE t.OffsetMoney>0"; DataTable dt = BussinessCommon.GetList(out pageCount, out Counts, sql, PageIndex, PageSize, "PayDate", 1, "Id", ConnectionDb.GetBusinessConnection(), "*").Tables[0]; return(JSONHelper.FromString(dt)); } }
/// <summary> /// 订单确认收款 /// </summary> /// <param name="OrderId"></param> /// <returns></returns> public static string ReceBusinessOrder(string OrderId) { try { string ConnStr = ConnectionDb.GetBusinessConnection(); IDbConnection Conn = new SqlConnection(ConnectionDb.GetBusinessConnection()); string query = "SELECT * FROM Tb_Charge_Receipt WHERE OrderId=@OrderId"; Tb_Charge_Receipt T_Order = Conn.Query <Tb_Charge_Receipt>(query, new { OrderId = OrderId }).SingleOrDefault(); if (T_Order.IsPay.ToString() == "已付款") { return("订单已付款"); } //订单确认收款,收款动作写在下面 T_Order.IsPay = "已付款"; T_Order.PayDate = DateTime.Now; T_Order.Method = "银联"; Conn.Execute("UPDATE Tb_Charge_Receipt SET IsPay=@IsPay,PayDate=@PayDate,Method='银联' WHERE Id=@Id", new { IsPay = T_Order.IsPay.ToString(), PayDate = T_Order.PayDate.ToString(), Id = T_Order.Id.ToString() }); return("success"); } catch (Exception E) { return(E.Message.ToString()); } }
//新增房屋出租或房屋转让 public string RentalNew(DataRow Row) { string backstr = ""; try { decimal Amount = 0.0m, Rent = 0.0m; if (Row.Table.Columns.Contains("Price") && !string.IsNullOrEmpty(Row["Price"].ToString())) { Amount = AppGlobal.StrToDec(Row["Price"].ToString()); } if (Row["BussType"].ToString() == "租售") { Rent = Amount; Amount = 0.0m; } string ConnStr = ConnectionDb.GetUnifiedConnectionString(); IDbConnection Conn = new SqlConnection(ConnStr); string Query = @"INSERT INTO Tb_Rental (Id,CommunityId,BussType,LinkMan,Province,City,Area,Address,Mobile,Img,UserId,ProcessState,Amount,Rent,RegisterDate,RegisterMan) VALUES (@Id,@CommunityId,@BussType,@LinkMan,@Province,@City,@Area,@Address,@Mobile,@Img,@UserId,'受理',@Amount,@Rent,getdate(),@LinkMan)"; Conn.Execute(Query, new { Id = Guid.NewGuid().ToString(), CommunityId = Row["CommunityId"].ToString(), BussType = Row["BussType"].ToString(), LinkMan = Row["LinkMan"].ToString(), Province = Row["Province"].ToString(), City = Row["City"].ToString(), Area = Row["Area"].ToString(), Address = Row["Address"].ToString(), Mobile = Row["Mobile"].ToString(), Img = Row["Img"].ToString(), UserId = Row["UserId"].ToString(), Amount = Amount, Rent = Rent }); } catch (Exception ex) { backstr = ex.Message; } if (backstr == "") { return(JSONHelper.FromString(true, "success")); } else { return(JSONHelper.FromString(false, backstr)); } }
private string GetRentalTop(DataRow Row) { string backstr = ""; DataTable dt = null; try { string ConnStr = ConnectionDb.GetUnifiedConnectionString(); StringBuilder sb = new StringBuilder(); if (Row.Table.Columns.Contains("AppleBundleID") && Row["AppleBundleID"].ToString() != "")//苹果包名 { sb.AppendFormat(" AND CommunityId in(select Id from Tb_Community where AppleBundleID = '{0}') ", Row["AppleBundleID"]); } if (Row.Table.Columns.Contains("AndroidPackageName") && Row["AndroidPackageName"].ToString() != "")//安桌包名 { sb.AppendFormat(" AND CommunityId in(select Id from Tb_Community where AndroidPackageName = '{0}') ", Row["AndroidPackageName"]); } sb.AppendFormat(" AND ProcessState='发布' AND BussType='{0}'", Row["BussType"].ToString()); if (Row["BussType"].ToString() == "租售") { sb.AppendFormat(@" AND (convert(NVARCHAR(20),Rent) <> '面议' OR (convert(DECIMAL(18,2), replace(rent, '面议', '0'))>=0 AND convert(DECIMAL(18,2), replace(rent, '面议', '0'))<=99999999) OR Rent IS NULL)"); } if (Row["BussType"].ToString() == "转让") { sb.AppendFormat(" AND ((Amount>=0 AND Amount<=99999999) OR Amount IS NULL)"); } int page = 1; int row = 5; dt = BaseGetData.GetList(ConnStr, sb.ToString(), page, row, "RegisterDate", 1, "View_Tb_Rental", "Id").Tables[0]; } catch (Exception ex) { backstr = ex.Message; } if (backstr == "") { return(JSONHelper.FromString(dt)); } else { return(JSONHelper.FromString(false, backstr)); } }
/// <summary> /// /// </summary> /// <param name="Ds"></param> /// <returns></returns> public string GenerateOrder(DataSet Ds) { DataRow Row = Ds.Tables[0].Rows[0]; bool IsBankOk = false; bool IsBusinessOk = false; string OrderId = ""; string prepay_str = ""; string txnTime = DateTime.Now.ToString("yyyyMMddHHmmss"); string UserId = Row["UserId"].ToString(); string BussId = Row["BussId"].ToString(); bool IsConfig = GenerateConfig(BussId); string Amount = "0"; if (IsConfig == false) { return(JSONHelper.FromString(false, "未配置证书文件")); } //生成商家账单 string BussinessResult = GenerateBusinessOrder(Ds, BussId, UserId, txnTime, ref IsBusinessOk, ref Amount, ref OrderId); if (IsBusinessOk == true) { //生成预支付单,并返回订单ID string BankResult = GenerateBankOrder(BussId, OrderId, txnTime, Amount, ref IsBankOk, ref prepay_str); if (IsBankOk == false) { return(JSONHelper.FromString(false, BankResult)); } else { //更新订单银行流水号 IDbConnection Conn = new SqlConnection(ConnectionDb.GetBusinessConnection()); string Query = "UPDATE Tb_Charge_Receipt SET PrepayStr=@prepay_str WHERE OrderId = @OrderId "; Conn.Execute(Query, new { prepay_str = prepay_str.ToString(), OrderId = OrderId }); //返回请求字符串 return(JSONHelper.FromJsonString(true, prepay_str)); } } else { return(JSONHelper.FromString(false, BussinessResult)); } }
private void ChangeUnitPrice(Dictionary <String, decimal> unitPrice) { try { using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { foreach (var model in unitPrice) { String sql = @"UPDATE Tb_Charge_ReceiptDetail SET UnitPrice=@UnitPrice WHERE RpdCode=@RpdCode;"; conn.Execute(sql, new { UnitPrice = model.Value, RpdCode = model.Key }); } } } catch (Exception ex) { //ignore } }
/// <summary> /// 配置支付请求参数,根据当前商家获取 /// </summary> /// <param name="BussId"></param> public bool GenerateConfig(string BussId) { IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection()); string query = "SELECT * FROM Tb_AlipayCertifiate WHERE BussId=@BussId"; var T = conn.Query <Model.Model.Buss.Tb_AlipayCertifiate>(query, new { BussId = BussId }).FirstOrDefault(); if (T == null) { return(false); } else { c.partner = T.partner.ToString(); c.seller_id = T.seller_id.ToString(); c.private_key = T.private_key.ToString(); c.alipay_public_key = T.alipay_public_key.ToString(); return(true); } }
/// <summary> /// 配置支付请求参数,根据当前商家获取 /// </summary> /// <param name="BussId"></param> public Config GetGenerateConfig(string BussId) { Config config = null; using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { string query = "SELECT * FROM Tb_AlipayCertifiate WHERE BussId=@BussId"; var T = conn.Query <Model.Model.Buss.Tb_AlipayCertifiate>(query, new { BussId = BussId }).FirstOrDefault(); if (T != null) { config = new Config(); config.partner = T.partner.ToString(); config.seller_id = T.seller_id.ToString(); config.private_key = T.private_key.ToString(); config.alipay_public_key = T.alipay_public_key.ToString(); } return(config); } }
/// <summary> /// 配置支付请求参数,根据当前商家获取 /// </summary> /// <param name="BussId"></param> public WxPayConfig GenerateConfig(string BussId) { WxPayConfig wxPayConfig = null; IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection()); string query = "SELECT * FROM Tb_WeiXinPayCertificate WHERE BussId=@BussId"; Model.Model.Buss.Tb_WeiXinPayCertificate T = conn.Query <Model.Model.Buss.Tb_WeiXinPayCertificate>(query, new { BussId = BussId }).SingleOrDefault(); if (T != null) { wxPayConfig = new WxPayConfig(); wxPayConfig.APPID = T.appid.ToString(); wxPayConfig.MCHID = T.mch_id.ToString(); wxPayConfig.KEY = T.appkey.ToString(); wxPayConfig.APPSECRET = T.appsecret.ToString(); wxPayConfig.SSLCERT_PATH = T.SSLCERT_PATH; wxPayConfig.SSLCERT_PASSWORD = T.SSLCERT_PASSWORD; wxPayConfig.NOTIFY_URL = Global_Fun.AppWebSettings("WechatPayBuss_Notify_Url").ToString(); } return(wxPayConfig); }
private string GenerateOfflineOrder(DataSet ds) { DataRow Row = ds.Tables[0].Rows[0]; string prepay_str = ""; string txnTime = DateTime.Now.ToString("yyyyMMddHHmmss"); string orderId = ""; decimal Amount = 0.0m; decimal CouponAmount = 0.0m; string BussId = Row["BussId"].ToString(); string UserId = Row["UserId"].ToString(); bool IsBusinessOk = false; string Name = Row["Name"].ToString(); string Mobile = Row["Mobile"].ToString(); string DeliverAddress = Row["DeliverAddress"].ToString(); // 俊发需求5896 string communityId = null; if (Row.Table.Columns.Contains("CommunityId") && !string.IsNullOrEmpty(Row["CommunityId"].ToString())) { communityId = Row["CommunityId"].ToString(); } //生成商家账单 string BussinessResult = GenerateBusinessOrder(ds, communityId, BussId, UserId, txnTime, ref IsBusinessOk, ref Amount, ref CouponAmount, ref orderId, Name, Mobile, DeliverAddress); if (!IsBusinessOk) { return(new ApiResult(false, BussinessResult).toJson()); } using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { string sql = string.Format(@"UPDATE Tb_Charge_Receipt SET PrepayStr=@prepay_str,Method='线下支付',IsPay='已付款',ReturnCode='TRADE_FINISHED',ReturnMsg='TRADE_FINISHED',PayDate=GetDate(),Amount={0},CouponAmount={1},RealAmount=0 WHERE OrderId = @OrderId", Amount, CouponAmount); conn.Execute(sql, new { prepay_str = prepay_str.ToString(), OrderId = orderId }); } return(new ApiResult(true, "下单成功").toJson()); }
/// <summary> /// /// </summary> /// <param name="OrderId"></param> /// <param name="ReturnCode"></param> /// <param name="ReturnMsg"></param> /// <returns></returns> public static string UpdateBusinessOrder(string OrderId, string ReturnCode, string ReturnMsg) { try { string ConnStr = ConnectionDb.GetBusinessConnection(); IDbConnection Conn = new SqlConnection(ConnectionDb.GetBusinessConnection()); string query = "SELECT * FROM Tb_Charge_Receipt WHERE OrderId=@OrderId OR Id=@OrderId"; Tb_Charge_Receipt T_Order = Conn.Query <Tb_Charge_Receipt>(query, new { OrderId = OrderId }).SingleOrDefault(); T_Order.ReturnCode = ReturnCode; T_Order.ReturnMsg = ReturnMsg; Conn.Update(T_Order); return("success"); } catch (Exception E) { return(E.Message.ToString()); } }
/// <summary> /// 获取业主优惠券余额 /// </summary> public string GetCouponBalanceList(DataRow row) { if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString())) { return(JSONHelper.FromString(false, "用户id不能为空")); } if (!row.Table.Columns.Contains("CorpId") || string.IsNullOrEmpty(row["CorpId"].ToString())) { return(JSONHelper.FromString(false, "物业公司id不能为空")); } using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { DataTable dt = conn.ExecuteReader(@"SELECT b.CorpId,b.BussId,b.Balance, BussName = (SELECT top 1 BussName FROM Tb_System_BusinessCorp a WHERE a.BussId = b.BussId) FROM Tb_Resources_CouponBalance b WHERE UserId=@UserId AND CorpId=@CorpId", new { UserId = row["UserId"], CorpId = row["CorpId"] }).ToDataSet().Tables[0]; return(JSONHelper.FromString(dt)); } }
/// <summary> /// 订单继续支付 /// </summary> public string GoOnGenerateOrder(DataSet Ds) { DataRow Row = Ds.Tables[0].Rows[0]; bool IsBankOk = false; bool IsBusinessOk = true;//商家订单是否生成成功 string OrderId = Row["OrderId"].ToString(); string prepay_str = ""; //取得上一次商家订单信息 IDbConnection ConnOrder = new SqlConnection(ConnectionDb.GetBusinessConnection()); string OrderQuery = "SELECT * FROM Tb_Charge_Receipt WHERE OrderId=@OrderId"; Model.Model.Buss.Tb_Charge_Receipt TOrder = ConnOrder.Query <Model.Model.Buss.Tb_Charge_Receipt>(OrderQuery, new { OrderId = OrderId }).SingleOrDefault(); if (TOrder != null) { IsBusinessOk = true; } string txnTime = TOrder.txnTime.ToString(); string BussId = TOrder.BussId.ToString(); bool IsConfig = GenerateConfig(BussId); if (IsConfig == false) { return(JSONHelper.FromString(false, "未配置证书文件")); } decimal realAmount = 0.0m; string sql = @"SELECT * FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode"; IEnumerable <Tb_Charge_ReceiptDetail> ReceiptDetails = ConnOrder.Query <Tb_Charge_ReceiptDetail>(sql, new { ReceiptCode = TOrder.Id }); foreach (Tb_Charge_ReceiptDetail item in ReceiptDetails) { // 计算此时应该多少钱 sql = @"SELECT * FROM Tb_Resources_Details WHERE ResourcesID=@ResourcesID"; Tb_Resources_Details resources = ConnOrder.Query <Tb_Resources_Details>(sql, new { ResourcesID = item.ResourcesID }).FirstOrDefault(); if (resources != null) { if (resources.IsGroupBuy == "是" && resources.GroupBuyEndDate.HasValue && resources.GroupBuyEndDate.Value > DateTime.Now) { realAmount += (resources.GroupBuyPrice.Value + item.Quantity); } else { realAmount += (resources.ResourcesSalePrice - resources.ResourcesDisCountPrice) * item.Quantity; } if (item.OffsetMoney.HasValue) { realAmount -= item.OffsetMoney.Value; } if (item.OffsetMoney2.HasValue) { realAmount -= item.OffsetMoney2.Value; } } } if (IsBusinessOk == true) { sql = "SELECT * FROM Tb_System_BusinessCorp WHERE BussId=@BussId"; Tb_System_BusinessCorp bussInfo = ConnOrder.Query <Tb_System_BusinessCorp>(sql, new { BussId = TOrder.BussId }).FirstOrDefault(); //生成预支付单,并返回订单ID string BankResult = GenerateBankOrder(BussId, OrderId, txnTime, realAmount, ref IsBankOk, ref prepay_str, bussInfo.BussName + "订单,共" + ReceiptDetails.Count() + "种商品"); if (IsBankOk == false) { return(JSONHelper.FromString(false, BankResult)); } else { //更新订单银行流水号 IDbConnection Conn = new SqlConnection(ConnectionDb.GetBusinessConnection()); string Query = "UPDATE Tb_Charge_Receipt SET PrepayStr=@prepay_str WHERE OrderId = @OrderId "; Conn.Execute(Query, new { prepay_str = prepay_str.ToString(), OrderId = OrderId }); //返回请求字符串 return(JSONHelper.FromJsonString(true, prepay_str)); } } return(JSONHelper.FromString(false, "生成订单失败")); }
/// <summary> /// 订单退款 /// </summary> /// <param name="out_trade_no"></param> /// <returns></returns> public static string CancelOrder(string out_trade_no) { string ConnStr = ConnectionDb.GetBusinessConnection(); return(""); }
/// <summary> /// /// </summary> /// <param name="Ds"></param> /// <returns></returns> public string GenerateOrder(DataSet Ds) { DataRow Row = Ds.Tables[0].Rows[0]; bool IsBankOk = false; bool IsBusinessOk = false; string OrderId = ""; string prepay_str = ""; string txnTime = DateTime.Now.ToString("yyyyMMddHHmmss"); string BussId = Row["BussId"].ToString(); string UserId = Row["UserId"].ToString(); bool IsConfig = GenerateConfig(BussId); if (IsConfig == false) { return(JSONHelper.FromString(false, "未配置证书文件")); } string Name = Row["Name"].ToString(); string Mobile = Row["Mobile"].ToString(); string DeliverAddress = Row["DeliverAddress"].ToString(); string subject = ""; decimal Amount = 0.0m; decimal CouponAmount = 0.0m; // 俊发需求5896 string communityId = null; if (Row.Table.Columns.Contains("CommunityId") && !string.IsNullOrEmpty(Row["CommunityId"].ToString())) { communityId = Row["CommunityId"].ToString(); } //生成商家账单 string BussinessResult = GenerateBusinessOrder(Ds, communityId, BussId, UserId, txnTime, ref IsBusinessOk, ref Amount, ref CouponAmount, ref OrderId, Name, Mobile, DeliverAddress); if (IsBusinessOk == true) { using (IDbConnection Conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { Tb_System_BusinessCorp bussInfo = Conn.Query <Tb_System_BusinessCorp>(@"SELECT * FROM Tb_System_BusinessCorp WHERE BussId=@BussId", new { BussId = BussId }).FirstOrDefault(); if (bussInfo != null) { subject = bussInfo.BussName + "订单,共" + Ds.Tables["Product"].Rows.Count + "种商品"; } } //生成预支付单,并返回订单ID string BankResult = GenerateBankOrder(BussId, OrderId, txnTime, (Amount - CouponAmount), ref IsBankOk, ref prepay_str, subject); if (IsBankOk == false) { return(JSONHelper.FromString(false, BankResult)); } else { using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { string sql; if ((Amount - CouponAmount) == 0) { sql = string.Format(@"UPDATE Tb_Charge_Receipt SET PrepayStr=@prepay_str,Method='支付宝',IsPay='已付款',ReturnCode='TRADE_FINISHED',ReturnMsg='TRADE_FINISHED',PayDate=GetDate(),Amount={0},CouponAmount={1},RealAmount=0 WHERE OrderId = @OrderId", Amount, CouponAmount); } else { sql = @"UPDATE Tb_Charge_Receipt SET PrepayStr=@prepay_str WHERE OrderId = @OrderId"; } try { conn.Execute(sql, new { prepay_str = prepay_str.ToString(), OrderId = OrderId }); IDbConnection tw2bsConn = new SqlConnection(PubConstant.UnifiedContionString.ToString()); var commInfo = tw2bsConn.QueryFirstOrDefault($"SELECT CorpID,CommName FROM Tb_Community WHERE Id ='{communityId}'"); if (commInfo.CorpID == 1985)//俊发需求6662 { var info = conn.QueryFirstOrDefault($@"SELECT HeadquartersPhone,CommPhone,BusinessPhone FROM Tb_NotificationManager WHERE CommunityID = '{communityId}'"); if (info != null) { string msg = $@"【俊发物业】{commInfo.CommName}项目,有新的已支付订单,请尽快去后台跟进处理。"; Common.Sms.Send_v2(1410, "jfwy", "jfwy0424", info.HeadquartersPhone, msg, out string strErrMsg); Common.Sms.Send_v2(1410, "jfwy", "jfwy0424", info.CommPhone, msg, out string strErrMsg1); Common.Sms.Send_v2(1410, "jfwy", "jfwy0424", info.BusinessPhone, msg, out string strErrMsg2); } } } catch (Exception) { } } //返回请求字符串 return(JSONHelper.FromJsonString(true, prepay_str, false)); } } else { return(JSONHelper.FromString(false, BussinessResult)); } }
/// <summary> /// 订单继续支付 /// </summary> public string GoOnGenerateOrder(DataSet Ds) { DataRow Row = Ds.Tables[0].Rows[0]; bool IsBankOk = false; bool IsBusinessOk = true;//商家订单是否生成成功 string OrderId = Row["OrderId"].ToString(); string prepay_str = ""; //取得上一次商家订单信息 IDbConnection ConnOrder = new SqlConnection(ConnectionDb.GetBusinessConnection()); string OrderQuery = "SELECT * FROM Tb_Charge_Receipt WHERE OrderId=@OrderId OR Id=@OrderId"; Model.Model.Buss.Tb_Charge_Receipt TOrder = ConnOrder.Query <Model.Model.Buss.Tb_Charge_Receipt>(OrderQuery, new { OrderId = OrderId }).SingleOrDefault(); if (TOrder != null) { IsBusinessOk = true; } string txnTime = TOrder.txnTime.ToString(); string BussId = TOrder.BussId.ToString(); bool IsConfig = GenerateConfig(BussId); if (IsConfig == false) { return(JSONHelper.FromString(false, "未配置证书文件")); } decimal realAmount = 0.0m; string sql = @"SELECT * FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode"; IEnumerable <Tb_Charge_ReceiptDetail> ReceiptDetails = ConnOrder.Query <Tb_Charge_ReceiptDetail>(sql, new { ReceiptCode = TOrder.Id }); Dictionary <String, decimal> keyValuePairs = new Dictionary <string, decimal>(); foreach (Tb_Charge_ReceiptDetail item in ReceiptDetails) { decimal unitPrice = 0.0m; // 计算此时应该多少钱 sql = @"SELECT * FROM Tb_Resources_Details WHERE ResourcesID=@ResourcesID"; Tb_Resources_Details resources = ConnOrder.Query <Tb_Resources_Details>(sql, new { ResourcesID = item.ResourcesID }).FirstOrDefault(); if (resources != null) { if (resources.IsGroupBuy == "是" && resources.GroupBuyEndDate.HasValue && resources.GroupBuyEndDate.Value > DateTime.Now && resources.GroupBuyStartDate.HasValue && resources.GroupBuyStartDate.Value < DateTime.Now ) { realAmount += (item.GroupPrice.HasValue ? item.GroupPrice.Value : 0) * item.Quantity; unitPrice = item.GroupPrice.HasValue ? item.GroupPrice.Value : 0; } else { unitPrice = (item.SalesPrice.Value - item.DiscountPrice.Value) >= 0 ? item.SalesPrice.Value - item.DiscountPrice.Value : 0; //如果销售价格 小于 优惠价 则算成0 realAmount += ((item.SalesPrice.Value - item.DiscountPrice.Value) >= 0 ? item.SalesPrice.Value - item.DiscountPrice.Value : 0) * item.Quantity; } keyValuePairs.Add(item.RpdCode, unitPrice); if (item.OffsetMoney.HasValue) { realAmount -= item.OffsetMoney.Value; } if (item.OffsetMoney2.HasValue) { realAmount -= item.OffsetMoney2.Value; } //修改当前商品的团购价 } } //加上运费 var money = realAmount + TOrder.Freight; //如果不需要给钱 直接完成该订单 if (money <= 0) { String result = ReceBusinessOrder(OrderId, 0); return(JsonConvert.SerializeObject(new { Result = result.ToLower() == "success" ? "true" : "false", Msg = result, })); } ChangeUnitPrice(keyValuePairs); if (IsBusinessOk == true) { sql = "SELECT * FROM Tb_System_BusinessCorp WHERE BussId=@BussId"; Tb_System_BusinessCorp bussInfo = ConnOrder.Query <Tb_System_BusinessCorp>(sql, new { BussId = TOrder.BussId }).FirstOrDefault(); //生成预支付单,并返回订单ID string BankResult = GenerateBankOrder(BussId, OrderId, txnTime, money, ref IsBankOk, ref prepay_str, bussInfo.BussName + "订单,共" + ReceiptDetails.Count() + "种商品"); if (IsBankOk == false) { return(JSONHelper.FromString(false, BankResult)); } else { //更新订单银行流水号 IDbConnection Conn = new SqlConnection(ConnectionDb.GetBusinessConnection()); string Query = @"UPDATE Tb_Charge_Receipt SET PrepayStr=@prepay_str WHERE OrderId = @OrderId OR Id=@OrderId "; Conn.Execute(Query, new { prepay_str = prepay_str.ToString(), OrderId = OrderId }); //返回请求字符串 return(JSONHelper.FromJsonString(true, prepay_str)); } } return(JSONHelper.FromString(false, "生成订单失败")); }
/// <summary> /// 生成商家订单 /// </summary> public string GenerateBusinessOrder(DataSet Ds, string communityId, string BussId, string UserId, string txnTime, ref bool IsOk, ref decimal Amount, ref decimal CouponAmount, ref string OrderId, string Name, string Mobile, string DeliverAddress) { DataRow Row = Ds.Tables[0].Rows[0]; int?corpId = null; if (Row.Table.Columns.Contains("CorpId") && !string.IsNullOrEmpty(Row["CorpId"].ToString())) { corpId = AppGlobal.StrToInt(Row["CorpId"].ToString()); } var iid = Guid.NewGuid().ToString(); // 生成商家收款订单 Tb_Charge_Receipt Receipt = new Tb_Charge_Receipt { Id = iid, CorpId = corpId, BussId = BussId.ToString(), UserId = UserId, OrderId = Guid.NewGuid().ToString().Replace("-", ""), ReceiptSign = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999).ToString(), Name = Name, IsPay = "未付款", IsReceive = "未收货", IsDeliver = "未发货", ReceiptMemo = Row["ReceiptMemo"].ToString(), ReceiptType = "通用票据", ReceiptDate = DateTime.Now, PayDate = null, MchId = c.partner, Partner = c.partner, PrepayStr = "", txnTime = txnTime.ToString(), ReturnCode = "", ReturnMsg = "", Express = "", ExpressNum = "", Mobile = Mobile, //联系电话 DeliverAddress = DeliverAddress, //收货地址 IsDelete = 0 }; // 是否使用优惠券抵扣 if (Row.Table.Columns.Contains("UseCoupon")) { Receipt.IsUseCoupon = AppGlobal.StrToInt(Row["UseCoupon"].ToString()); } using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { // 保存订单信息 conn.Insert(Receipt); if (!string.IsNullOrEmpty(communityId)) { conn.Execute("UPDATE Tb_Charge_Receipt SET CommunityId=@CommunityId WHERE ID=@IID", new { CommunityId = communityId, IID = iid }); } /* * 计算此时的订单金额,此时不应该保存商品的价格信息; * 如果用户不付款,且订单未取消期间,商家更改了商品单价或可优惠价或规格追加价,付款时则需要重新计算 * * 1、获取用户在该商家的可用优惠券余额 * 2、获取商品此时的单价和可优惠价,对应的规格型号的追加单价 * 3、计算当前订单内的商品是否支持优惠券抵扣,如果支持,计算最多能抵扣的价格并更新优惠券余额 */ // 1、获取用户优惠券余额 dynamic balanceInfo = conn.Query <dynamic>(@"SELECT isnull(Balance1,0) AS Balance1,isnull(Balance2,0) AS Balance2 FROM (SELECT Balance1=(SELECT Balance FROM Tb_Resources_CouponBalance WHERE UserId=@UserId AND CorpId=@CorpId AND BussId=@BussId), Balance2=(SELECT Balance FROM Tb_Resources_CouponBalance WHERE UserId=@UserId AND CorpId=@CorpId AND BussId IS NULL)) as x;", new { UserId = Receipt.UserId, CorpId = corpId, BussId = Receipt.BussId }).FirstOrDefault(); decimal totalAmount = 0.0m; // 商品总价 decimal userCouponBalance = 0.0m; // 用户可用优惠券余额 decimal totalUseCouponMoney = 0.0m; // 当前能使用优惠券抵扣的钱 decimal bussBalance = 0.0m; // 商家专用券余额 decimal corpBalance = 0.0m; // 物管通用券余额 if (balanceInfo != null) { bussBalance = balanceInfo.Balance1; corpBalance = balanceInfo.Balance2; userCouponBalance = bussBalance + corpBalance; } // 2、获取商品价格信息 foreach (DataRow DetailRow in Ds.Tables["Product"].Rows) { // 订单商品详情 Tb_Charge_ReceiptDetail ReceiptDetail = new Tb_Charge_ReceiptDetail(); ReceiptDetail.RpdCode = Guid.NewGuid().ToString(); ReceiptDetail.ReceiptCode = Receipt.Id; ReceiptDetail.ShoppingId = DetailRow["ShoppingId"].ToString(); ReceiptDetail.ResourcesID = DetailRow["Id"].ToString(); ReceiptDetail.RpdMemo = DetailRow["RpdMemo"].ToString(); ReceiptDetail.Quantity = DataSecurity.StrToInt(DetailRow["Quantity"].ToString()); ReceiptDetail.OffsetMoney = 0.0m; ReceiptDetail.OffsetMoney2 = 0.0m; // 商品的单价和可优惠单价 string sql = @"SELECT * FROM Tb_Resources_Details WHERE isnull(IsDelete,0)=0 AND IsRelease='是' AND IsStopRelease='否' AND ResourcesID=@ResourcesID"; Tb_Resources_Details ResourcesDetail = conn.Query <Tb_Resources_Details>(sql, new { ResourcesID = ReceiptDetail.ResourcesID }).FirstOrDefault(); // 商品存在,期间未失效 if (ResourcesDetail != null) { // 库存不足,跳过 if (ReceiptDetail.Quantity > ResourcesDetail.ResourcesCount) { continue; } ReceiptDetail.SalesPrice = ResourcesDetail.ResourcesSalePrice; ReceiptDetail.DiscountPrice = ResourcesDetail.ResourcesDisCountPrice; bool IsInGroupBuyTime = false; if (ResourcesDetail.IsGroupBuy == "是") { if (ResourcesDetail.GroupBuyStartDate.HasValue && ResourcesDetail.GroupBuyStartDate.Value <= DateTime.Now && ResourcesDetail.GroupBuyEndDate.HasValue && ResourcesDetail.GroupBuyEndDate.Value >= DateTime.Now) { IsInGroupBuyTime = true; ReceiptDetail.GroupPrice = ResourcesDetail.GroupBuyPrice; } } // 1、商品单价 decimal price = 0.0m; if (IsInGroupBuyTime) { price = (ResourcesDetail.GroupBuyPrice.Value - ResourcesDetail.ResourcesDisCountPrice) * ReceiptDetail.Quantity; } else { price = (ResourcesDetail.ResourcesSalePrice - ResourcesDetail.ResourcesDisCountPrice) * ReceiptDetail.Quantity; } // 2、商品规格追加价格 sql = "SELECT * FROM View_Tb_ResourcesSpecificationsPrice WHERE ShoppingId=@ShoppingId"; MobileSoft.Model.Resources.Tb_ResourcesSpecificationsPrice propertyInfo = conn.Query <MobileSoft.Model.Resources.Tb_ResourcesSpecificationsPrice>(sql, new { ShoppingId = DetailRow["ShoppingId"].ToString() }).FirstOrDefault(); if (propertyInfo != null && propertyInfo.Price.HasValue) { price = price + propertyInfo.Price.Value * ReceiptDetail.Quantity; } // 当前商品需要抵扣的金额 decimal currCouponMoney = 0.0m; string isSupportCoupon = ""; // 3、商品支持抵扣券 并且 还有用户还有足够的优惠券余额 if (ResourcesDetail.IsSupportCoupon == "1") { isSupportCoupon = "是"; } else { isSupportCoupon = "否"; } if (isSupportCoupon == "是" && Receipt.IsUseCoupon.HasValue && Receipt.IsUseCoupon.Value > 0 && userCouponBalance > 0) { // 计算当前商品需要抵扣的金额 currCouponMoney = (ResourcesDetail.MaximumCouponMoney.HasValue ? ResourcesDetail.MaximumCouponMoney.Value : 0) * ReceiptDetail.Quantity; if (bussBalance > 0) { if (bussBalance >= currCouponMoney) { // 优惠券使用明细 ReceiptDetail.OffsetMoney = currCouponMoney; ReceiptDetail.OffsetMoney2 = 0.0m; totalUseCouponMoney += currCouponMoney; bussBalance -= currCouponMoney; userCouponBalance -= currCouponMoney; } else { // 先用专用券抵扣 ReceiptDetail.OffsetMoney = bussBalance; totalUseCouponMoney += bussBalance; userCouponBalance -= bussBalance; // 专用券不够差额 decimal differ = currCouponMoney - bussBalance; // 专用券不够,通用券余额大于0 if (corpBalance > 0) { // 通用券足够抵扣 if (corpBalance >= differ) { ReceiptDetail.OffsetMoney2 = differ; totalUseCouponMoney += differ; corpBalance -= differ; userCouponBalance -= differ; } // 通用券不足以抵扣 else { ReceiptDetail.OffsetMoney2 = corpBalance; totalUseCouponMoney += corpBalance; userCouponBalance -= corpBalance; corpBalance = 0; } } } } else { // 没有专用券,但是有通用券 if (corpBalance > 0) { // 通用券足够抵扣 if (corpBalance > currCouponMoney) { ReceiptDetail.OffsetMoney2 = currCouponMoney; totalUseCouponMoney += currCouponMoney; corpBalance -= currCouponMoney; userCouponBalance -= currCouponMoney; } // 通用券不足以抵扣 else { ReceiptDetail.OffsetMoney2 = corpBalance; totalUseCouponMoney += corpBalance; userCouponBalance -= corpBalance; corpBalance = 0; } } } } // 订单商品总价追加 totalAmount += price; // 插入订单内商品数据 conn.Insert(ReceiptDetail); } // 删除购物车 conn.Execute("UPDATE Tb_ShoppingCar SET IsDelete=1 WHERE Id=@ShoppingId", new { ShoppingId = ReceiptDetail.ShoppingId }); } // 3、更新用户优惠券信息 if (totalUseCouponMoney > 0) { conn.Execute("proc_Resources_CouponBalance_Use", new { UserId = Receipt.UserId, CorpId = corpId, BussId = Receipt.BussId, UseMoney = totalUseCouponMoney }, null, null, CommandType.StoredProcedure); } // ref参数处理 IsOk = true; Amount = totalAmount; CouponAmount = totalUseCouponMoney; OrderId = Receipt.OrderId; } return("生成订单成功"); }
/// <summary> /// 获取所有订单 /// </summary> private string GetORdersAll_New(DataRow row) { if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString())) { return(JSONHelper.FromString(false, "用户编号不能为空")); } int pageCount; int Counts; int PageIndex = 1; int PageSize = 2; //string QueryType = "all"; string IsDeliver = ""; //是否发货 string IsPay = ""; //付款状态 string HandleState = ""; //处理状态 string IsReceive = ""; //是否收货 if (row.Table.Columns.Contains("PageIndex") && AppGlobal.StrToInt(row["PageIndex"].ToString()) > 0) { PageIndex = AppGlobal.StrToInt(row["PageIndex"].ToString()); } if (row.Table.Columns.Contains("PageSize") && AppGlobal.StrToInt(row["PageSize"].ToString()) > 0) { PageSize = AppGlobal.StrToInt(row["PageSize"].ToString()); } string whereStr = ""; string FldName = @"CorpId,BussId,BussNature,BussName,Id,ReceiptSign,HandleState,IsReceive,IsPay,IsDeliver, OrderDetailNumber,Tb_Charge_Receipt.Id AS OrderId,ReceiptDate,Express,ExpressNum,CouponBalance"; StringBuilder sb = new StringBuilder(); try { whereStr += " and r.UserId='" + row["UserId"] + "'"; if (row.Table.Columns.Contains("IsPay") && row["IsPay"].ToString() != "") { IsPay = row["IsPay"].ToString(); whereStr += " and R.IsPay='" + IsPay + "'"; } if (row.Table.Columns.Contains("IsDeliver") && row["IsDeliver"].ToString() != "") { IsDeliver = row["IsDeliver"].ToString(); whereStr += " and isnull(R.IsDeliver,'未发货')='" + IsDeliver + "'"; } if (row.Table.Columns.Contains("IsReceive") && row["IsReceive"].ToString() != "") { IsReceive = row["IsReceive"].ToString(); whereStr += " and isnull(R.IsReceive,'未收货')='" + IsReceive + "'"; // 乐生活 if (IsReceive == "未收货" && string.IsNullOrEmpty(IsDeliver) && HttpContext.Current.Request.Url.Host.Contains("lsc.easylife365.com")) { whereStr += " and isnull(R.IsDeliver,'未收货')='已发货'"; } else if (IsPay == "已付款" && IsReceive == "已收货") { whereStr += " and isnull(R.IsDeliver,'未收货')='已发货' AND isnull(R.IsEvaluated,0)=0"; } } //查询订单 string OrderSql = @"select ReturnSatus=0 ,ISNULL(B.BussWorkedTel,B.BussMobileTel) as BussMobile, B.BussName,B.BussNature,R.*,isnull(x.Balance,0) as CouponBalance, OrderDetailNumber=(select COUNT(1) from Tb_Charge_ReceiptDetail where ReceiptCode=R.Id and RpdIsDelete=0) from Tb_Charge_Receipt as R inner join Tb_System_BusinessCorp as B on b.BussId = R.BussId LEFT JOIN Tb_Resources_CouponBalance x ON x.BussId=R.BussId AND x.UserId=R.UserId where ISNULL( R.IsDelete,0)=0 and isnull(B.IsClose,'未关闭')='未关闭' and ISNULL( B.IsDelete,0)=0 " + whereStr; DataSet Ds_Order = BussinessCommon.GetList(out pageCount, out Counts, OrderSql, PageIndex, PageSize, "ReceiptDate", 1, "Id", PubConstant.BusinessContionString, FldName); sb.Append("{\"Result\":\"true\","); if (Ds_Order != null && Ds_Order.Tables.Count > 0 && Ds_Order.Tables[0].Rows.Count > 0) { sb.Append("\"data\":["); for (int i = 0; i < Ds_Order.Tables[0].Rows.Count; i++) { DataRow dr = Ds_Order.Tables[0].Rows[i]; if (dr["IsDeliver"] == null || dr["IsDeliver"].ToString() == "" || dr["IsDeliver"].ToString() == "未发货") { dr["IsReceive"] = null; } if (!String.IsNullOrEmpty(dr["IsReceive"].ToString()) && !String.IsNullOrEmpty(dr["ConfirmReceivedTime"].ToString()) && DateTime.TryParse(dr["ConfirmReceivedTime"].ToString(), out DateTime confirmReceivedTime)) { dr["ReturnSatus"] = ReturnInfoStatus(dr["IsRetreat"], confirmReceivedTime, (String)dr["IsReceive"]); } DataSet Ds_OrderDetails = null; if (dr["IsPay"] != null && dr["IsPay"].ToString() == "未付款") { using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { Ds_OrderDetails = BussinessCommon.GetShoppingDetailed_New(dr["Id"].ToString()); } } else { Ds_OrderDetails = BussinessCommon.GetShoppingDetailed(dr["Id"].ToString()); } if (i > 0) { sb.Append(","); } //sb.Append(JSONHelper.FromDataRow(dr)); string strr = JSONHelper.FromDataRow(dr); strr = strr.Substring(0, strr.Length - 1); sb.Append(strr); if (Ds_OrderDetails != null && Ds_OrderDetails.Tables.Count > 0 && Ds_OrderDetails.Tables[0].Rows.Count > 0) { sb.Append(",\"Details\":["); for (int j = 0; j < Ds_OrderDetails.Tables[0].Rows.Count; j++) { DataRow dr_Details = Ds_OrderDetails.Tables[0].Rows[j]; if (j > 0) { sb.Append(","); } sb.Append(JSONHelper.FromDataRow(dr_Details)); // 查询商品属性 using (var bzconn = new SqlConnection(PubConstant.BusinessContionString)) { var specArr = bzconn.Query(@"select a.PropertyId,a.SpecId,b.SpecName,a.Price,A.DiscountAmount,A.GroupBuyingPrice,C.ResourcesName from Tb_ResourcesSpecificationsPrice a left join Tb_Resources_Specifications b ON a.SpecId=b.Id LEFT JOIN Tb_Resources_Details AS C ON A.ResourcesID=C.ResourcesID where a.ResourcesID=@ResourcesID AND a.SpecId IS NOT NULL AND a.SpecId IN (SELECT SpecId FROM Tb_ShoppingDetailed WHERE ShoppingId=@ShoppingId);", new { ResourcesID = Ds_OrderDetails.Tables[0].Rows[j]["ResourcesID"], ShoppingId = Ds_OrderDetails.Tables[0].Rows[j]["ShoppingId"] }); List <string> list = new List <string>(); foreach (var spec in specArr) { var param = new { PropertyId = spec.PropertyId, SpecId = spec.SpecId, SpecName = spec.SpecName, DiscountAmount = spec.DiscountAmount, GroupBuyingPrice = spec.GroupBuyingPrice, Price = spec.Price, ResourcesName = spec.ResourcesName, }; list.Add(JsonConvert.SerializeObject(param)); } sb.Insert(sb.Length - 1, ",\"Property\":[" + string.Join(",", list) + "]"); } } sb.Append("]"); } else { sb.Append(",\"Details\":[]"); } sb.Append("}"); } sb.Append("]"); } else { sb.Append("\"data\":[]"); } sb.Append("}"); } catch (Exception ex) { sb = new StringBuilder(); sb.Append(JSONHelper.FromString(false, ex.Message)); } return(sb.ToString()); }
//private string GetGoodsCollectionInfo(DataRow row) //{ // if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString())) // { // return JSONHelper.FromString(false, "用户编号不能为空"); // } // if (!row.Table.Columns.Contains("GoodsId") || string.IsNullOrEmpty(row["GoodsId"].ToString())) // { // return JSONHelper.FromString(false, "商品ID不能为空"); // } // string userId = row["UserId"].ToString(); // string goodsId = row["GoodsId"].ToString(); // int counts=0; // using (SqlConnection conn = new SqlConnection(PubConstant.BusinessContionString)) // { // string sql = @"SELECT COUNNT(*) FROM Tb_Goods_Collection WHERE UserId = @UserId AND GoodsId = @GoodsId"; // counts = conn.QueryFirstOrDefault<int>(sql, new { UserId = userId, GoodsId = goodsId}); // if (counts > 0) // { // return JSONHelper.FromString(true, "1"); // } // else // { // return JSONHelper.FromString(true, "0"); // } // } //} //private string GoodsCollect(DataRow row) //{ // if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString())) // { // return JSONHelper.FromString(false, "用户编号不能为空"); // } // if (!row.Table.Columns.Contains("GoodsId") || string.IsNullOrEmpty(row["GoodsId"].ToString())) // { // return JSONHelper.FromString(false, "商品ID不能为空"); // } // string userId = row["UserId"].ToString(); // string goodsId = row["GoodsId"].ToString(); // int counts = 0; // using (SqlConnection conn = new SqlConnection(PubConstant.BusinessContionString)) // { // string sql = @"INSERT INTO Tb_Goods_Collection(UserId,GoodsId) Values(@UserId,@GoodsId)"; // counts = conn.Execute(sql, new { UserId = userId, GoodsId = goodsId }); // if (counts > 0) // { // return JSONHelper.FromString(true, "收藏成功"); // } // else // { // return JSONHelper.FromString(false, "收藏失败"); // } // } //} //private string CancelGoodsCollect(DataRow row) //{ // if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString())) // { // return JSONHelper.FromString(false, "用户编号不能为空"); // } // if (!row.Table.Columns.Contains("GoodsId") || string.IsNullOrEmpty(row["GoodsId"].ToString())) // { // return JSONHelper.FromString(false, "商品ID不能为空"); // } // string userId = row["UserId"].ToString(); // string goodsId = row["GoodsId"].ToString(); // int counts = 0; // using (SqlConnection conn = new SqlConnection(PubConstant.BusinessContionString)) // { // string sql = @"DELETE FROM Tb_Goods_Collection WHERE UserId = @UserId AND GoodsId = @GoodsId"; // counts = conn.Execute(sql, new { UserId = userId, GoodsId = goodsId }); // if (counts > 0) // { // return JSONHelper.FromString(true, "取消收藏成功"); // } // else // { // return JSONHelper.FromString(false, "取消收藏失败"); // } // } //} /// <summary> /// 订单详情 /// </summary> /// <param name="row"></param> /// <returns></returns> private String QueryOrderDetail(DataRow row) { if (!row.Table.Columns.Contains("Id") || string.IsNullOrEmpty(row["Id"].AsString())) { return(JSONHelper.FromString(false, "订单详情失败")); } String orderId = row["Id"].ToString(); int pageCount; int Counts; int PageIndex = 1; int PageSize = 2; string whereStr = ""; string FldName = @"CorpId,BussId,BussNature,BussName,Id,ReceiptSign,HandleState,IsReceive,IsPay,IsDeliver, OrderDetailNumber,Tb_Charge_Receipt.Id AS OrderId,ReceiptDate,Express,ExpressNum,CouponBalance"; StringBuilder sb = new StringBuilder(); try { whereStr += " and r.Id='" + orderId + "'"; //查询订单 string OrderSql = @"select ReturnSatus=0,ISNULL(B.BussWorkedTel,B.BussMobileTel) as BussMobile, B.BussName,B.BussNature,R.*,isnull(x.Balance,0) as CouponBalance, OrderDetailNumber=(select COUNT(1) from Tb_Charge_ReceiptDetail where ReceiptCode=R.Id and RpdIsDelete=0) from Tb_Charge_Receipt as R inner join Tb_System_BusinessCorp as B on b.BussId = R.BussId LEFT JOIN Tb_Resources_CouponBalance x ON x.BussId=R.BussId AND x.UserId=R.UserId where ISNULL( R.IsDelete,0)=0 and isnull(B.IsClose,'未关闭')='未关闭' and ISNULL( B.IsDelete,0)=0 " + whereStr; DataSet Ds_Order = null; using (IDbConnection con = new SqlConnection(PubConstant.BusinessContionString)) { Ds_Order = con.ExecuteReader(OrderSql).ToDataSet(); } sb.Append("{\"Result\":\"true\","); if (Ds_Order != null && Ds_Order.Tables.Count > 0 && Ds_Order.Tables[0].Rows.Count > 0) { sb.Append("\"data\":["); for (int i = 0; i < Ds_Order.Tables[0].Rows.Count; i++) { DataRow dr = Ds_Order.Tables[0].Rows[i]; if (dr["IsDeliver"] == null || dr["IsDeliver"].ToString() == "" || dr["IsDeliver"].ToString() == "未发货") { dr["IsReceive"] = null; } if (!String.IsNullOrEmpty(dr["IsReceive"].ToString()) && !String.IsNullOrEmpty(dr["ConfirmReceivedTime"].ToString()) && DateTime.TryParse(dr["ConfirmReceivedTime"].ToString(), out DateTime confirmReceivedTime)) { dr["ReturnSatus"] = ReturnInfoStatus(dr["IsRetreat"], confirmReceivedTime, (String)dr["IsReceive"]); } DataSet Ds_OrderDetails = null; if (dr["IsPay"] != null && dr["IsPay"].ToString() == "未付款") { using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { Ds_OrderDetails = BussinessCommon.GetShoppingDetailed_New(dr["Id"].ToString()); } } else { Ds_OrderDetails = BussinessCommon.GetShoppingDetailed(dr["Id"].ToString()); } if (i > 0) { sb.Append(","); } string strr = JSONHelper.FromDataRow(dr); strr = strr.Substring(0, strr.Length - 1); sb.Append(strr); if (Ds_OrderDetails != null && Ds_OrderDetails.Tables.Count > 0 && Ds_OrderDetails.Tables[0].Rows.Count > 0) { sb.Append(",\"Details\":["); for (int j = 0; j < Ds_OrderDetails.Tables[0].Rows.Count; j++) { DataRow dr_Details = Ds_OrderDetails.Tables[0].Rows[j]; if (j > 0) { sb.Append(","); } sb.Append(JSONHelper.FromDataRow(dr_Details)); // 查询商品属性 using (var bzconn = new SqlConnection(PubConstant.BusinessContionString)) { var specArr = bzconn.Query(@"select a.PropertyId,a.SpecId,b.SpecName,a.Price,A.DiscountAmount,A.GroupBuyingPrice,C.ResourcesName from Tb_ResourcesSpecificationsPrice a left join Tb_Resources_Specifications b ON a.SpecId=b.Id LEFT JOIN Tb_Resources_Details AS C ON A.ResourcesID=C.ResourcesID where a.ResourcesID=@ResourcesID AND a.SpecId IS NOT NULL AND a.SpecId IN (SELECT SpecId FROM Tb_ShoppingDetailed WHERE ShoppingId=@ShoppingId);", new { ResourcesID = Ds_OrderDetails.Tables[0].Rows[j]["ResourcesID"], ShoppingId = Ds_OrderDetails.Tables[0].Rows[j]["ShoppingId"] }); List <string> list = new List <string>(); foreach (var spec in specArr) { list.Add("{\"PropertyId\":\"" + spec.PropertyId + "\",\"SpecId\":\"" + spec.SpecId + "\",\"SpecName\":\"" + spec.SpecName + "\",\"DiscountAmount\":" + spec.DiscountAmount + ",\"GroupBuyingPrice\":" + spec.GroupBuyingPrice + ",\"Price\":" + spec.Price + ",\"ResourcesName\":\"" + spec.ResourcesName + "\" }"); } sb.Insert(sb.Length - 1, ",\"Property\":[" + string.Join(",", list) + "]"); } } sb.Append("]"); } else { sb.Append(",\"Details\":[]"); } sb.Append("}"); } sb.Append("]"); } else { sb.Append("\"data\":[]"); } sb.Append("}"); } catch (Exception ex) { sb = new StringBuilder(); sb.Append(JSONHelper.FromString(false, ex.Message)); } return(sb.ToString()); }
private string OrderDetail(DataRow row) { if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString())) { return(JSONHelper.FromString(false, "用户id不能为空")); } if (!row.Table.Columns.Contains("ReceiptCode") || string.IsNullOrEmpty(row["ReceiptCode"].ToString())) { return(JSONHelper.FromString(false, "订单id不能为空")); } var userId = row["UserId"].ToString(); var receiptCode = row["ReceiptCode"].ToString(); string whereStr = $" and r.UserId='{userId}' AND R.Id='{receiptCode}'"; string FldName = @"CorpId,BussId,BussNature,BussName,Id,ReceiptSign,HandleState,IsReceive,IsPay,IsDeliver, OrderDetailNumber,Tb_Charge_Receipt.Id AS OrderId,ReceiptDate,Express,ExpressNum,CouponBalance"; StringBuilder sb = new StringBuilder(); try { //查询订单 string OrderSql = @"select B.BussName,B.BussNature,R.*,isnull(x.Balance,0) as CouponBalance, OrderDetailNumber=(select COUNT(1) from Tb_Charge_ReceiptDetail where ReceiptCode=R.Id and RpdIsDelete=0) from Tb_Charge_Receipt as R inner join Tb_System_BusinessCorp as B on b.BussId = R.BussId LEFT JOIN Tb_Resources_CouponBalance x ON x.BussId=R.BussId AND x.UserId=R.UserId where ISNULL( R.IsDelete,0)=0 and ISNULL( B.IsDelete,0)=0 " + whereStr; DataSet Ds_Order = BussinessCommon.GetList(out int pageCount, out int Counts, OrderSql, 1, 1, "ReceiptDate", 1, "Id", PubConstant.BusinessContionString, FldName); sb.Append("{\"Result\":\"true\","); if (Ds_Order != null && Ds_Order.Tables.Count > 0 && Ds_Order.Tables[0].Rows.Count > 0) { sb.Append("\"data\":"); for (int i = 0; i < Ds_Order.Tables[0].Rows.Count; i++) { DataRow dr = Ds_Order.Tables[0].Rows[i]; if (dr["IsDeliver"] == null || dr["IsDeliver"].ToString() == "" || dr["IsDeliver"].ToString() == "未发货") { dr["IsReceive"] = null; } DataSet Ds_OrderDetails = null; if (dr["IsPay"] != null && dr["IsPay"].ToString() == "未付款") { using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { Ds_OrderDetails = BussinessCommon.GetShoppingDetailed_New(dr["Id"].ToString()); } } else { Ds_OrderDetails = BussinessCommon.GetShoppingDetailed(dr["Id"].ToString()); } if (i > 0) { sb.Append(","); } //sb.Append(JSONHelper.FromDataRow(dr)); string strr = JSONHelper.FromDataRow(dr); strr = strr.Substring(0, strr.Length - 1); sb.Append(strr); if (Ds_OrderDetails != null && Ds_OrderDetails.Tables.Count > 0 && Ds_OrderDetails.Tables[0].Rows.Count > 0) { sb.Append(",\"Details\":["); for (int j = 0; j < Ds_OrderDetails.Tables[0].Rows.Count; j++) { DataRow dr_Details = Ds_OrderDetails.Tables[0].Rows[j]; if (j > 0) { sb.Append(","); } sb.Append(JSONHelper.FromDataRow(dr_Details)); // 查询商品属性 using (var bzconn = new SqlConnection(PubConstant.BusinessContionString)) { var specArr = bzconn.Query(@"select a.PropertyId,a.SpecId,b.SpecName,a.Price,A.DiscountAmount,A.GroupBuyingPrice from Tb_ResourcesSpecificationsPrice a left join Tb_Resources_Specifications b ON a.SpecId=b.Id where a.ResourcesID=@ResourcesID AND a.SpecId IS NOT NULL AND a.SpecId IN (SELECT SpecId FROM Tb_ShoppingDetailed WHERE ShoppingId=@ShoppingId);", new { ResourcesID = Ds_OrderDetails.Tables[0].Rows[j]["ResourcesID"], ShoppingId = Ds_OrderDetails.Tables[0].Rows[j]["ShoppingId"] }); List <string> list = new List <string>(); foreach (var spec in specArr) { //list.Add("{\"PropertyId\":\"" + spec.PropertyId + "\",\"SpecId\":\"" + spec.SpecId + "\",\"SpecName\":\"" + spec.SpecName + "\",\"Price\":" + spec.Price + " }"); list.Add(JsonConvert.SerializeObject(new { PropertyId = spec.PropertyId, SpecId = spec.SpecId, SpecName = spec.SpecName, Price = spec.Price, DiscountAmount = spec.DiscountAmount, GroupBuyingPrice = spec.GroupBuyingPrice, })); } sb.Insert(sb.Length - 1, ",\"Property\":[" + string.Join(",", list) + "]"); } } sb.Append("]"); } else { sb.Append(",\"Details\":[]"); } sb.Append("}"); } } else { sb.Append("\"data\":[]"); } sb.Append("}"); } catch (Exception ex) { sb = new StringBuilder(); sb.Append(JSONHelper.FromString(false, ex.Message)); } return(sb.ToString()); }
/// <summary> /// 订单确认收款 /// </summary> public static string ReceBusinessOrder(string OrderId, decimal realAmount) { try { String moblie = String.Empty; using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { var code = RandomCodeHelper.CreateRandomCode(5); string sql = "SELECT * FROM Tb_Charge_Receipt WHERE OrderId=@OrderId OR Id=@OrderId"; Tb_Charge_Receipt Receipt = conn.Query <Tb_Charge_Receipt>(sql, new { OrderId = OrderId }).SingleOrDefault(); if (Receipt.IsPay.ToString() == "已付款") { return("订单已付款"); } //订单确认收款,收款动作写在下面 Receipt.IsPay = "已付款"; Receipt.PayDate = DateTime.Now; Receipt.Method = "支付宝"; Receipt.RealAmount = realAmount; Receipt.ExtractionCode = code; //总价上面不包括运费 //Receipt.RealAmount = realAmount - Receipt.Freight; //获取商家的手机号 String businessMoblieSql = " SELECT BussMobileTel FROM Tb_System_BusinessCorp WHERE bussId=@bussId "; moblie = conn.QueryFirstOrDefault <String>(businessMoblieSql, new { bussId = Receipt.BussId }); // 对于没有使用优惠券的,直接设置Amount if (Receipt.IsUseCoupon <= 0) { //GetLog().Info(new { realAmount = realAmount, RealAmount = Receipt.RealAmount }); conn.Execute("UPDATE Tb_Charge_Receipt SET IsPay=@IsPay,PayDate=@PayDate,Method='支付宝',Amount=@RealAmount,RealAmount=@RealAmount WHERE Id=@Id", new { IsPay = Receipt.IsPay.ToString(), PayDate = Receipt.PayDate.ToString(), RealAmount = Receipt.RealAmount, Id = Receipt.Id.ToString() }); IEnumerable <Tb_Charge_ReceiptDetail> ReceiptDetails = conn.Query <Tb_Charge_ReceiptDetail>(@"SELECT * FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode", new { ReceiptCode = Receipt.Id }); foreach (Tb_Charge_ReceiptDetail item in ReceiptDetails) { Tb_Resources_Details resources = conn.Query <Tb_Resources_Details>("SELECT * FROM Tb_Resources_Details WHERE ResourcesID=@ResourcesID", new { ResourcesID = item.ResourcesID }).FirstOrDefault(); if (resources != null) { #region 根据购物车获取具体的某一个规格属性 ,并使用该规格属性上得价格 String sqlStr = @"SELECT A.Price,A.DiscountAmount,A.GroupBuyingPrice FROM Tb_ResourcesSpecificationsPrice AS A INNER JOIN (SELECT B.ResourcesID,C.PropertysId,C.SpecId FROM Tb_ShoppingCar AS B LEFT JOIN Tb_ShoppingDetailed AS C ON B.Id=C.ShoppingId WHERE B.Id IS NOT NULL AND B.Id=@Id) AS D ON D.ResourcesID=A.ResourcesID AND D.PropertysId =A.PropertyId AND D.SpecId=A.SpecId"; var praceInfo = conn.QueryFirstOrDefault(sqlStr, new { Id = item.ShoppingId }); if (null == praceInfo) { return(JSONHelper.FromString(false, "商品没有规格属性")); } resources.ResourcesSalePrice = (decimal)praceInfo.Price; resources.GroupBuyPrice = (decimal)praceInfo.GroupBuyingPrice; resources.ResourcesDisCountPrice = (decimal)praceInfo.DiscountAmount; #endregion decimal detailAmount = 0.0m; if (resources.IsGroupBuy == "是" && resources.GroupBuyEndDate.HasValue && resources.GroupBuyEndDate > DateTime.Now) { detailAmount = resources.GroupBuyPrice.Value * item.Quantity; } else { detailAmount = (resources.ResourcesSalePrice - resources.ResourcesDisCountPrice) * item.Quantity; } conn.Execute(@"UPDATE Tb_Charge_ReceiptDetail SET SalesPrice=@SalesPrice, DiscountPrice=@DiscountPrice, GroupPrice=@GroupPrice, DetailAmount=@DetailAmount, OffsetMoney=0 WHERE ResourcesID=@ResourcesID AND RpdCode=@RpdCode", new { SalesPrice = resources.ResourcesSalePrice, DiscountPrice = resources.ResourcesDisCountPrice, GroupPrice = (( resources.IsGroupBuy == "是" && resources.GroupBuyEndDate.HasValue && resources.GroupBuyEndDate > DateTime.Now && resources.GroupBuyStartDate.HasValue && resources.GroupBuyStartDate <= DateTime.Now ) ? resources.GroupBuyPrice : null), DetailAmount = detailAmount, ResourcesID = resources.ResourcesID, RpdCode = item.RpdCode }); // 减库存 //conn.Execute("UPDATE Tb_Resources_Details SET ResourcesCount=(ResourcesCount-@Count) WHERE ResourcesID=@ResourcesID", // new { Count = AppGlobal.StrToLong(item.Quantity.ToString()), ResourcesID = resources.ResourcesID }); #region 减库存,减少规格参数得库存 //获取当前订单得所有商品 sql = $@"SELECT B.ResourcesID,C.PropertysId,C.SpecId,A.ReceiptCode,Quantity FROM Tb_Charge_ReceiptDetail AS A INNER JOIN Tb_ShoppingCar AS B ON A.ShoppingId=B.Id INNER JOIN Tb_ShoppingDetailed AS C ON A.ShoppingId=C.ShoppingId WHERE A.RpdCode=@Id"; var detail = conn.Query(sql, new { Id = item.RpdCode }); //逐个减少各个规格得参数,增加销售数量 StringBuilder updateStr = new StringBuilder(); foreach (var model in detail) { updateStr.Append($"UPDATE Tb_ResourcesSpecificationsPrice SET Inventory= Inventory - {model.Quantity} ,SalesVolumes= SalesVolumes + {item.Quantity } WHERE BussId={Receipt.BussId} AND ResourcesID='{model.ResourcesID}' AND PropertyId= '{model.PropertysId}' AND SpecId='{model.SpecId}' ;"); } var count = conn.Execute(updateStr.ToString()); //然后重新核对商品得数量,增加销售数量 if (count == detail.Count()) { conn.Execute($"UPDATE Tb_Resources_Details SET ResourcesCount=(SELECT SUM(Inventory) AS Inventory FROM Tb_ResourcesSpecificationsPrice WHERE ResourcesID=@ResourcesID),ActualSalesVolumes = ActualSalesVolumes + {item.Quantity} WHERE ResourcesID =@ResourcesID", new { ResourcesID = resources.ResourcesID }); } #endregion } } } else { IEnumerable <Tb_Charge_ReceiptDetail> ReceiptDetails = conn.Query <Tb_Charge_ReceiptDetail>(@"SELECT * FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode", new { ReceiptCode = Receipt.Id }); IEnumerable <Tb_Resources_Details> resources = conn.Query <Tb_Resources_Details>(@"SELECT * FROM Tb_Resources_Details WHERE ResourcesID in(SELECT ResourcesID FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode)", new { ReceiptCode = Receipt.Id }); decimal totalAmount = 0.0m; // 商品总价,此处计算的是不算优惠券时的总价 // 此时Tb_Charge_ReceiptDetail.DetailAmount保存的是未计算优惠券时的价格,最终价格需要另行计算 foreach (Tb_Resources_Details item in resources) { foreach (Tb_Charge_ReceiptDetail receiptDetail in ReceiptDetails) { if (receiptDetail.ResourcesID == item.ResourcesID) { // 团购 if (item.IsGroupBuy == "是" && item.GroupBuyEndDate.HasValue && item.GroupBuyEndDate > DateTime.Now) { receiptDetail.DetailAmount = (item.GroupBuyPrice.Value * receiptDetail.Quantity); } else { receiptDetail.DetailAmount = (item.ResourcesSalePrice - item.ResourcesDisCountPrice) * receiptDetail.Quantity; } totalAmount += receiptDetail.DetailAmount.Value; break; } } } conn.Execute("UPDATE Tb_Charge_Receipt SET IsPay=@IsPay,PayDate=@PayDate,Method='支付宝',Amount=@Amount,RealAmount=@RealAmount WHERE Id=@Id", new { IsPay = Receipt.IsPay.ToString(), PayDate = Receipt.PayDate.ToString(), Amount = totalAmount, RealAmount = Receipt.RealAmount, Id = Receipt.Id.ToString() }); // 本次使用的抵扣券金额 //decimal couponMoney = totalAmount - realAmount; //decimal tempUsedCouponMoney = 0.0m;// 暂时没有用处,判断本次使用的抵扣券是否和订单详细记录里面使用的抵扣券价格一样 foreach (Tb_Resources_Details item in resources) { foreach (Tb_Charge_ReceiptDetail receiptDetail in ReceiptDetails) { if (receiptDetail.ResourcesID == item.ResourcesID) { // 更新订单详情表商品付费金额信息 conn.Execute(@"UPDATE Tb_Charge_ReceiptDetail SET SalesPrice=@SalesPrice, DiscountPrice=@DiscountPrice, GroupPrice=@GroupPrice, DetailAmount=@DetailAmount WHERE ResourcesID=@ResourcesID AND ReceiptCode=@ReceiptCode", new { SalesPrice = item.ResourcesSalePrice, DiscountPrice = item.ResourcesDisCountPrice, GroupPrice = ((item.IsGroupBuy == "是" && item.GroupBuyEndDate.HasValue && item.GroupBuyEndDate > DateTime.Now) ? item.GroupBuyPrice : null), DetailAmount = receiptDetail.DetailAmount.Value - receiptDetail.OffsetMoney.Value, ResourcesID = receiptDetail.ResourcesID, ReceiptCode = receiptDetail.ReceiptCode }); break; } } } } //支付成功生成核销码 conn.Execute("UPDATE Tb_Charge_Receipt SET ExtractionCode=@ExtractionCode WHERE Id=@Id", new { ExtractionCode = code, Id = Receipt.Id.ToString() }); //订单推送 //Orders.OrderInfoPush(Receipt.CorpId, Receipt.OrderId, moblie); return("success"); } } catch (Exception ex) { return(ex.Message); } }
/// <summary> /// 订单继续支付 /// </summary> public string GoOnGenerateOrder(DataSet Ds) { DataRow Row = Ds.Tables[0].Rows[0]; bool IsBankOk = false; bool IsBusinessOk = true;//商家订单是否生成成功 string OrderId = Row["OrderId"].ToString(); string prepay_str = ""; //取得上一次商家订单信息 IDbConnection ConnOrder = new SqlConnection(ConnectionDb.GetBusinessConnection()); string OrderQuery = "SELECT * FROM Tb_Charge_Receipt WHERE OrderId=@OrderId"; Model.Model.Buss.Tb_Charge_Receipt TOrder = ConnOrder.Query <Model.Model.Buss.Tb_Charge_Receipt>(OrderQuery, new { OrderId = OrderId }).SingleOrDefault(); if (TOrder != null) { IsBusinessOk = true; } string txnTime = TOrder.txnTime.ToString(); string BussId = TOrder.BussId.ToString(); WxPayConfig wxPayConfig = GenerateConfig(BussId); if (null == wxPayConfig) { return(JSONHelper.FromString(false, "未配置证书文件")); } decimal realAmount = 0.0m; string sql = @"SELECT * FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode"; IEnumerable <Tb_Charge_ReceiptDetail> ReceiptDetails = ConnOrder.Query <Tb_Charge_ReceiptDetail>(sql, new { ReceiptCode = TOrder.Id }); foreach (Tb_Charge_ReceiptDetail item in ReceiptDetails) { // 计算此时应该多少钱 sql = @"SELECT * FROM Tb_Resources_Details WHERE ResourcesID=@ResourcesID"; Tb_Resources_Details resources = ConnOrder.Query <Tb_Resources_Details>(sql, new { ResourcesID = item.ResourcesID }).FirstOrDefault(); if (resources != null) { if (resources.IsGroupBuy == "是" && resources.GroupBuyEndDate.HasValue && resources.GroupBuyEndDate.Value > DateTime.Now) { realAmount += (resources.GroupBuyPrice.Value + item.Quantity); } else { realAmount += (resources.ResourcesSalePrice - resources.ResourcesDisCountPrice) * item.Quantity; } if (item.OffsetMoney.HasValue) { realAmount -= item.OffsetMoney.Value; } if (item.OffsetMoney2.HasValue) { realAmount -= item.OffsetMoney2.Value; } } } if (IsBusinessOk == true) { sql = "SELECT * FROM Tb_System_BusinessCorp WHERE BussId=@BussId"; Tb_System_BusinessCorp bussInfo = ConnOrder.Query <Tb_System_BusinessCorp>(sql, new { BussId = TOrder.BussId }).FirstOrDefault(); //生成银行订单,返回银行流水号 WxPayData Data = new WxPayData(); string BankResult = GenerateBankOrder(BussId, OrderId, txnTime, (int)(realAmount * 100), ref IsBankOk, ref Data, wxPayConfig); if (IsBankOk == false) { return(JSONHelper.FromString(false, BankResult)); } else { //更新订单银行流水号 IDbConnection Conn = new SqlConnection(ConnectionDb.GetBusinessConnection()); string Query = "UPDATE Tb_Charge_Receipt SET PrepayStr=@prepay_str WHERE OrderId = @OrderId "; Conn.Execute(Query, new { prepay_str = prepay_str.ToString(), OrderId = OrderId }); //返回请求字符串 //向手机端返回银行记录 WxPayData result = new WxPayData(); result.SetValue("appid", Data.GetValue("appid")); result.SetValue("partnerid", Data.GetValue("mch_id")); result.SetValue("prepayid", Data.GetValue("prepay_id")); result.SetValue("noncestr", Data.GetValue("nonce_str")); result.SetValue("package", "Sign=WXPay"); result.SetValue("timestamp", (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000); result.SetValue("sign", result.MakeSign()); return(JSONHelper.FromJsonString(true, result.ToJson())); } } return(JSONHelper.FromString(false, "生成订单失败")); }
//房屋租赁列表 public string RentalList(DataRow Row) { string backstr = ""; DataTable dt = null; try { string ConnStr = ConnectionDb.GetUnifiedConnectionString(); StringBuilder sb = new StringBuilder(); if (Row.Table.Columns.Contains("CommunityId") && Row["CommunityId"].ToString() != "") { sb.AppendFormat(" AND CommunityId='{0}'", Row["CommunityId"].ToString()); } else { if (string.IsNullOrEmpty(Row["City"].ToString()) == false) { sb.AppendFormat(" AND (City LIKE '%{0}%' OR Province LIKE '%{0}%')", Row["City"].ToString().Replace("市", "")); } if (Row.Table.Columns.Contains("AppleBundleID") && Row["AppleBundleID"].ToString() != "")//苹果包名 { sb.AppendFormat(" AND CommunityId in(select Id from Tb_Community where AppleBundleID = '{0}') ", Row["AppleBundleID"]); } if (Row.Table.Columns.Contains("AndroidPackageName") && Row["AndroidPackageName"].ToString() != "")//安桌包名 { sb.AppendFormat(" AND CommunityId in(select Id from Tb_Community where AndroidPackageName = '{0}') ", Row["AndroidPackageName"]); } } sb.AppendFormat(" AND ProcessState='发布' AND BussType='{0}'", Row["BussType"].ToString()); if (string.IsNullOrEmpty(Row["HouseType"].ToString()) == false) { sb.AppendFormat(" AND HouseType LIKE '%{0}%'", Row["HouseType"].ToString()); } if (Row["BussType"].ToString() == "租售") { sb.AppendFormat(@" AND (convert(NVARCHAR(20),Rent) <> '面议' AND (convert(DECIMAL(18,2), replace(rent, '面议', '0'))>={0} AND convert(DECIMAL(18,2), replace(rent, '面议', '0'))<={1}) OR Rent IS NULL)", Row["StartAmount"].ToString(), Row["EndAmount"].ToString()); } if (Row["BussType"].ToString() == "转让") { sb.AppendFormat(" AND ((Amount>={0} AND Amount<={1}) OR Amount IS NULL)", Row["StartAmount"].ToString(), Row["EndAmount"].ToString()); } int page = AppGlobal.StrToInt(Row["Page"].ToString()); int row = AppGlobal.StrToInt(Row["PageSize"].ToString()); dt = BaseGetData.GetList(ConnStr, sb.ToString(), page, row, "RegisterDate", 1, "View_Tb_Rental", "Id").Tables[0]; } catch (Exception ex) { backstr = ex.Message; } if (backstr == "") { return(JSONHelper.FromString(dt)); } else { return(JSONHelper.FromString(false, backstr)); } }
/// <summary> /// 生成商家订单 /// </summary> /// <param name="Ds"></param> /// <param name="BussId"></param> /// <param name="UserId"></param> /// <param name="txnTime"></param> /// <param name="IsOk"></param> /// <param name="Amount"></param> /// <param name="OrderId"></param> /// <returns></returns> public string GenerateBusinessOrder(DataSet Ds, string communityId, string BussId, string UserId, string txnTime, ref bool IsOk, ref string Amount, ref string OrderId, WxPayConfig wxPayConfig) { DataRow Row = Ds.Tables[0].Rows[0]; IDbConnection Conn = new SqlConnection(ConnectionDb.GetBusinessConnection()); string BusinessOrderId = Guid.NewGuid().ToString(); //生成商家收款订单 string Id = Guid.NewGuid().ToString(); Tb_Charge_Receipt EntityReceipt = new Tb_Charge_Receipt(); EntityReceipt.Id = Id; EntityReceipt.BussId = BussId.ToString(); EntityReceipt.OrderId = BusinessOrderId; // 是否使用抵扣券 if (Row.Table.Columns.Contains("UseCoupon")) { EntityReceipt.IsUseCoupon = AppGlobal.StrToInt(Row["UseCoupon"].ToString()); } OrderId = BusinessOrderId; string ReceiptSign = "";//获得订单号 //获取票据号 DynamicParameters dp = new DynamicParameters(); dp.Add("@Id", BussId); dp.Add("@OrderLength", 32); dp.Add("@Num", "", DbType.String, ParameterDirection.Output); using (IDbConnection IDbConn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { IDbConn.Execute("Proc_GetOrderNum", dp, null, null, CommandType.StoredProcedure); ReceiptSign = dp.Get <string>("@Num"); } EntityReceipt.ReceiptSign = ReceiptSign.ToString(); EntityReceipt.UserId = UserId.ToString(); IDbConnection ConnUnified = new SqlConnection(ConnectionDb.GetUnifiedConnectionString()); string QueryUser = "******"; Tb_User EntityUser = ConnUnified.Query <Tb_User>(QueryUser, new { UserId = UserId }).SingleOrDefault(); //查找用户名称 EntityReceipt.Name = EntityUser.Name.ToString(); EntityReceipt.Mobile = EntityUser.Mobile.ToString(); EntityReceipt.IsPay = "未付款"; EntityReceipt.IsReceive = "未收货"; EntityReceipt.Amount = 0; EntityReceipt.ReceiptMemo = Row["ReceiptMemo"].ToString(); EntityReceipt.ReceiptType = "通用票据"; EntityReceipt.ReceiptDate = DateTime.Now; EntityReceipt.MchId = wxPayConfig.MCHID; EntityReceipt.Partner = wxPayConfig.MCHID; EntityReceipt.PrepayStr = ""; EntityReceipt.txnTime = txnTime.ToString(); EntityReceipt.ReturnCode = ""; EntityReceipt.ReturnMsg = ""; EntityReceipt.IsDeliver = "未发货"; EntityReceipt.Express = ""; EntityReceipt.ExpressNum = ""; EntityReceipt.DeliverAddress = ""; EntityReceipt.PayDate = DateTime.Now; EntityReceipt.IsDelete = 0; Conn.Insert(EntityReceipt); if (!string.IsNullOrEmpty(communityId)) { Conn.Execute("UPDATE Tb_Charge_Receipt SET CommunityId=@CommunityId WHERE ID=@IID", new { CommunityId = communityId, IID = EntityReceipt.Id }); } decimal TotalAmount = 0.00M; //收成商家收款明细 foreach (DataRow DetailRow in Ds.Tables[1].Rows) { Tb_Charge_ReceiptDetail EntityReceiptDetail = new Tb_Charge_ReceiptDetail(); EntityReceiptDetail.RpdCode = Guid.NewGuid().ToString(); EntityReceiptDetail.ReceiptCode = Id; EntityReceiptDetail.ResourcesID = DetailRow["Id"].ToString(); EntityReceiptDetail.Quantity = DataSecurity.StrToInt(DetailRow["Quantity"].ToString()); string QueryResourcesSql = "SELECT * FROM Tb_Resources_Details WHERE ResourcesID=@ResourcesID"; Tb_Resources_Details T = Conn.Query <Tb_Resources_Details>(QueryResourcesSql, new { ResourcesID = DetailRow["Id"].ToString() }).SingleOrDefault(); EntityReceiptDetail.SalesPrice = T.ResourcesSalePrice; EntityReceiptDetail.DiscountPrice = T.ResourcesDisCountPrice; EntityReceiptDetail.GroupPrice = T.GroupBuyPrice; EntityReceiptDetail.DetailAmount = T.ResourcesSalePrice - T.ResourcesDisCountPrice; EntityReceiptDetail.RpdMemo = DetailRow["RpdMemo"].ToString(); Conn.Insert(EntityReceiptDetail); //计算订单的总金额 TotalAmount = TotalAmount + DataSecurity.StrToDecimal(EntityReceiptDetail.DetailAmount.ToString()); } //更新订单总金额 EntityReceipt.Amount = TotalAmount; Amount = Convert.ToString(TotalAmount * 100); IsOk = true; Conn.Update(EntityReceipt); return("生成订单成功"); }
/// <summary> /// 订单确认收款 /// </summary> /// <param name="OrderId"></param> /// <returns></returns> public static string ReceBusinessOrder(string OrderId, int realAmount) { try { using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { string sql = "SELECT * FROM Tb_Charge_Receipt WHERE OrderId=@OrderId"; Tb_Charge_Receipt Receipt = conn.Query <Tb_Charge_Receipt>(sql, new { OrderId = OrderId }).SingleOrDefault(); if (Receipt.IsPay.ToString() == "已付款") { return("订单已付款"); } //订单确认收款,收款动作写在下面 Receipt.IsPay = "已付款"; Receipt.PayDate = DateTime.Now; Receipt.Method = "微信"; Receipt.RealAmount = realAmount / 100.0m; // 对于没有使用优惠券的,直接设置Amount if (Receipt.IsUseCoupon <= 0) { conn.Execute("UPDATE Tb_Charge_Receipt SET IsPay=@IsPay,PayDate=@PayDate,Method='支付宝',Amount=@RealAmount,RealAmount=@RealAmount WHERE Id=@Id", new { IsPay = Receipt.IsPay.ToString(), PayDate = Receipt.PayDate.ToString(), RealAmount = Receipt.RealAmount, Id = Receipt.Id.ToString() }); IEnumerable <Tb_Charge_ReceiptDetail> ReceiptDetails = conn.Query <Tb_Charge_ReceiptDetail>(@"SELECT * FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode", new { ReceiptCode = Receipt.Id }); foreach (Tb_Charge_ReceiptDetail item in ReceiptDetails) { Tb_Resources_Details resources = conn.Query <Tb_Resources_Details>("SELECT * FROM Tb_Resources_Details WHERE ResourcesID=@ResourcesID", new { ResourcesID = item.ResourcesID }).FirstOrDefault(); if (resources != null) { decimal detailAmount = 0.0m; if (resources.IsGroupBuy == "是" && resources.GroupBuyEndDate.HasValue && resources.GroupBuyEndDate > DateTime.Now) { detailAmount = resources.GroupBuyPrice.Value * item.Quantity; } else { detailAmount = (resources.ResourcesSalePrice - resources.ResourcesDisCountPrice) * item.Quantity; } conn.Execute(@"UPDATE Tb_Charge_ReceiptDetail SET SalesPrice=@SalesPrice, DiscountPrice=@DiscountPrice, GroupPrice=@GroupPrice, DetailAmount=@DetailAmount, OffsetMoney=0 WHERE ResourcesID=@ResourcesID AND ReceiptCode=@ReceiptCode", new { SalesPrice = resources.ResourcesSalePrice, DiscountPrice = resources.ResourcesDisCountPrice, GroupPrice = ((resources.IsGroupBuy == "是" && resources.GroupBuyEndDate.HasValue && resources.GroupBuyEndDate > DateTime.Now) ? resources.GroupBuyPrice : null), DetailAmount = detailAmount, ResourcesID = resources.ResourcesID, ReceiptCode = item.ReceiptCode }); // 减库存 conn.Execute("UPDATE Tb_Resources_Details SET ResourcesCount=(ResourcesCount-@Count) WHERE ResourcesID=@ResourcesID", new { Count = AppGlobal.StrToLong(item.Quantity.ToString()), ResourcesID = resources.ResourcesID }); } } } else { IEnumerable <Tb_Charge_ReceiptDetail> ReceiptDetails = conn.Query <Tb_Charge_ReceiptDetail>(@"SELECT * FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode", new { ReceiptCode = Receipt.Id }); IEnumerable <Tb_Resources_Details> resources = conn.Query <Tb_Resources_Details>(@"SELECT * FROM Tb_Resources_Details WHERE ResourcesID in(SELECT ResourcesID FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode)", new { ReceiptCode = Receipt.Id }); decimal totalAmount = 0.0m; // 商品总价,此处计算的是不算优惠券时的总价 // 此时Tb_Charge_ReceiptDetail.DetailAmount保存的是未计算优惠券时的价格,最终价格需要另行计算 foreach (Tb_Resources_Details item in resources) { foreach (Tb_Charge_ReceiptDetail receiptDetail in ReceiptDetails) { if (receiptDetail.ResourcesID == item.ResourcesID) { // 团购 if (item.IsGroupBuy == "是" && item.GroupBuyEndDate.HasValue && item.GroupBuyEndDate > DateTime.Now) { receiptDetail.DetailAmount = (item.GroupBuyPrice.Value * receiptDetail.Quantity); } else { receiptDetail.DetailAmount = (item.ResourcesSalePrice - item.ResourcesDisCountPrice) * receiptDetail.Quantity; } totalAmount += receiptDetail.DetailAmount.Value; break; } } } conn.Execute("UPDATE Tb_Charge_Receipt SET IsPay=@IsPay,PayDate=@PayDate,Method='微信',Amount=@Amount,RealAmount=@RealAmount WHERE Id=@Id", new { IsPay = Receipt.IsPay.ToString(), PayDate = Receipt.PayDate.ToString(), Amount = totalAmount, RealAmount = Receipt.RealAmount, Id = Receipt.Id.ToString() }); // 本次使用的抵扣券金额 //decimal couponMoney = totalAmount - realAmount; //decimal tempUsedCouponMoney = 0.0m;// 暂时没有用处,判断本次使用的抵扣券是否和订单详细记录里面使用的抵扣券价格一样 foreach (Tb_Resources_Details item in resources) { foreach (Tb_Charge_ReceiptDetail receiptDetail in ReceiptDetails) { if (receiptDetail.ResourcesID == item.ResourcesID) { //if (receiptDetail.OffsetMoney.HasValue && receiptDetail.OffsetMoney.Value > 0) //{ // tempUsedCouponMoney += receiptDetail.OffsetMoney.Value; //} //if (receiptDetail.OffsetMoney2.HasValue && receiptDetail.OffsetMoney2.Value > 0) //{ // tempUsedCouponMoney += receiptDetail.OffsetMoney2.Value; //} //else //{ // receiptDetail.OffsetMoney = 0.0m; //} // 更新订单详情表商品付费金额信息 conn.Execute(@"UPDATE Tb_Charge_ReceiptDetail SET SalesPrice=@SalesPrice, DiscountPrice=@DiscountPrice, GroupPrice=@GroupPrice, DetailAmount=@DetailAmount WHERE ResourcesID=@ResourcesID AND ReceiptCode=@ReceiptCode", new { SalesPrice = item.ResourcesSalePrice, DiscountPrice = item.ResourcesDisCountPrice, GroupPrice = ((item.IsGroupBuy == "是" && item.GroupBuyEndDate.HasValue && item.GroupBuyEndDate > DateTime.Now) ? item.GroupBuyPrice : null), DetailAmount = receiptDetail.DetailAmount.Value - receiptDetail.OffsetMoney.Value, ResourcesID = receiptDetail.ResourcesID, ReceiptCode = receiptDetail.ReceiptCode }); // 减库存 conn.Execute("UPDATE Tb_Resources_Details SET ResourcesCount=(ResourcesCount-@Count) WHERE ResourcesID=@ResourcesID", new { Count = AppGlobal.StrToLong(receiptDetail.Quantity.ToString()), ResourcesID = item.ResourcesID }); break; } } } } return("success"); } } catch (Exception ex) { return(ex.Message); } }
/// <summary> /// 社区活动 /// </summary> /// <param name="row"></param> /// <returns></returns> public string CommunityActivitiesList(DataRow Row) { string result = ""; #region 接受参数 string strCommunityId = ""; //项目ID string strCurrPage = "1"; //第几页 string strPageSize = "10"; //分页的大小 if (Row.Table.Columns.Contains("CommunityId")) { strCommunityId = AppGlobal.ChkStr(Row["CommunityId"].ToString()); } else { return(JSONHelper.FromString(false, "缺少参数CommID")); } if (Row.Table.Columns.Contains("CurrPage")) { strCurrPage = AppGlobal.ChkNum(Row["CurrPage"].ToString()); } if (Row.Table.Columns.Contains("PageSize")) { strPageSize = AppGlobal.ChkNum(Row["PageSize"].ToString()); } #endregion #region 变量定义 string strErrMsg = ""; string strCommID = ""; string SQLContionString = ""; int PageCount = 0; int Counts = 0; StringBuilder sListContent = new StringBuilder(""); int iCurrPage = AppGlobal.StrToInt(strCurrPage); int iPageSize = AppGlobal.StrToInt(strPageSize); #endregion SQLContionString = ConnectionDb.GetConnection(Row["CommunityId"].ToString()); MobileSoft.Model.Unified.Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(Row["CommunityId"].ToString()); if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } #region 查询社区活动 string strSQLCommAct = "and ActivitiesType<>'0001' and isnull(IsDelete, 0)=0 And isnull(IsRun, 0)=1 AND CommID = " + Community.CommID; DataTable dTableCommAct = null; dTableCommAct = (new Business.TWBusinRule(SQLContionString)).HSPR_CommActivities_CutPage(out PageCount, out Counts, strSQLCommAct, iCurrPage, iPageSize); if (dTableCommAct.Rows.Count > 0) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("InfoID", typeof(string))); dt.Columns.Add(new DataColumn("Heading", typeof(string))); dt.Columns.Add(new DataColumn("IssueDate", typeof(string))); dt.Columns.Add(new DataColumn("ImageUrl", typeof(string))); foreach (DataRow DRow in dTableCommAct.Rows) { DataRow dr = dt.NewRow(); dr["InfoID"] = DRow["ActivitiesID"].ToString(); dr["Heading"] = DRow["ActivitiesTheme"].ToString(); dr["IssueDate"] = DRow["IssueDate"].ToString(); dr["ImageUrl"] = string.IsNullOrEmpty(DRow["ActivitiesImages"].ToString()) ? "" : DRow["ActivitiesImages"].ToString().IndexOf("http") >= 0 ? DRow["ActivitiesImages"].ToString() : DRow["ActivitiesImages"].ToString(); dt.Rows.Add(dr); } result = JSONHelper.FromString(dt); } else { result = JSONHelper.FromString(dTableCommAct); } #endregion dTableCommAct.Dispose(); return(result); }
/// <summary> /// /// </summary> /// <param name="Ds"></param> /// <returns></returns> public string GenerateOrder(DataSet Ds) { DataRow Row = Ds.Tables[0].Rows[0]; bool IsBankOk = false; bool IsBusinessOk = false; string OrderId = ""; string txnTime = DateTime.Now.ToString("yyyyMMddHHmmss"); string UserId = Row["UserId"].ToString(); string BussId = Row["BussId"].ToString(); WxPayConfig wxPayConfig = GenerateConfig(BussId); if (null == wxPayConfig) { return(JSONHelper.FromString(false, "未配置证书文件")); } string Name = Row["Name"].ToString(); string Mobile = Row["Mobile"].ToString(); string DeliverAddress = Row["DeliverAddress"].ToString(); string subject = ""; int Amount = 0; int CouponAmount = 0; // 俊发需求5896 string communityId = null; if (Row.Table.Columns.Contains("CommunityId") && !string.IsNullOrEmpty(Row["CommunityId"].ToString())) { communityId = Row["CommunityId"].ToString(); } //生成商家账单 string BussinessResult = GenerateBusinessOrder(Ds, communityId, BussId, UserId, txnTime, ref IsBusinessOk, ref Amount, ref CouponAmount, ref OrderId, Name, Mobile, DeliverAddress, wxPayConfig); if (IsBusinessOk == true) { using (IDbConnection Conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { Tb_System_BusinessCorp bussInfo = Conn.Query <Tb_System_BusinessCorp>(@"SELECT * FROM Tb_System_BusinessCorp WHERE BussId=@BussId", new { BussId = BussId }).FirstOrDefault(); if (bussInfo != null) { subject = bussInfo.BussName + "订单,共" + Ds.Tables["Product"].Rows.Count + "种商品"; } } //生成银行订单,返回银行流水号 WxPayData Data = new WxPayData(); string BankResult = GenerateBankOrder(BussId, OrderId, txnTime, (Amount - CouponAmount), ref IsBankOk, ref Data, wxPayConfig); if (IsBankOk == false) { return(JSONHelper.FromString(false, BankResult)); } else { using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection())) { string sql; if ((Amount - CouponAmount) == 0) { sql = string.Format(@"UPDATE Tb_Charge_Receipt SET PrepayStr=@prepay_str,Method='微信',IsPay='已付款',ReturnCode='TRADE_FINISHED',ReturnMsg='TRADE_FINISHED',PayDate=GetDate(),Amount={0:###.##},CouponAmount={1:###.##},RealAmount=0 WHERE OrderId = @OrderId", Amount / 100, CouponAmount / 100); } else { sql = @"UPDATE Tb_Charge_Receipt SET PrepayStr=@prepay_str WHERE OrderId = @OrderId"; } conn.Execute(sql, new { prepay_str = Data.GetValue("prepay_id").ToString(), OrderId = OrderId }); } //向手机端返回银行记录 WxPayData result = new WxPayData(); result.SetValue("appid", Data.GetValue("appid")); result.SetValue("partnerid", Data.GetValue("mch_id")); result.SetValue("prepayid", Data.GetValue("prepay_id")); result.SetValue("noncestr", Data.GetValue("nonce_str")); result.SetValue("package", "Sign=WXPay"); result.SetValue("timestamp", (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000); result.SetValue("sign", result.MakeSign()); // 上面的顺序不允许变动 result.SetValue("total_fee", Amount); return(JSONHelper.FromJsonString(true, result.ToJson())); } } else { return(JSONHelper.FromString(false, BussinessResult)); } }