/// <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>
private string DelChargeReceipt_New(DataRow row)
{
if (!row.Table.Columns.Contains("ReceiptCode") || string.IsNullOrEmpty(row["ReceiptCode"].ToString()))
{
return(JSONHelper.FromString(false, "订单id不能为空"));
}
if (!row.Table.Columns.Contains("CorpId") || string.IsNullOrEmpty(row["CorpId"].ToString()))
{
return(JSONHelper.FromString(false, "物管公司id不能为空"));
}
string str = "";
try
{
IDbConnection con = new SqlConnection(PubConstant.GetConnectionString("BusinessContionString"));
Tb_Charge_Receipt charge = BussinessCommon.GetChargeReceiptModel(row["ReceiptCode"].ToString());
if (charge != null)
{
charge.IsDelete = 1;
charge.CancellationType = 1;//业主主动取消
charge.CancellationTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
charge.CancellationReason = "";
con.Update <Tb_Charge_Receipt>(charge);
con.Execute("update Tb_Charge_ReceiptDetail set RpdIsDelete=1 where ReceiptCode=@ReceiptCode", new { ReceiptCode = charge.Id });
if (charge.IsUseCoupon.HasValue && charge.IsUseCoupon.Value == 1)
{
con.Execute("proc_Resources_CouponBalance_Return",
new { CorpId = AppGlobal.StrToInt(row["CorpId"].ToString()), ReceiptCode = charge.Id }, null, null, CommandType.StoredProcedure);
}
}
else
{
str = "删除失败";
}
}
catch (Exception ex)
{
str = ex.Message;
}
if (str != "")
{
return(JSONHelper.FromString(false, str));
}
else
{
return(JSONHelper.FromString(true, ""));
}
}
/// <summary>
/// 修改订单处理状态 UpdateChargeHandleState
/// </summary>
/// <param name="row"></param>
/// ReceiptCode 商品订单票据编码
/// 返回：
/// true:""
/// false:删除失败
/// <returns></returns>
private string UpdateChargeHandleState(DataRow row)
{
if (!row.Table.Columns.Contains("ReceiptCode") || string.IsNullOrEmpty(row["ReceiptCode"].ToString()))
{
return(JSONHelper.FromString(false, "订单不能为空"));
}
string str = "";
try
{
IDbConnection con = new SqlConnection(PubConstant.GetConnectionString("BusinessContionString"));
Tb_Charge_Receipt charge = BussinessCommon.GetChargeReceiptModel(row["ReceiptCode"].ToString());
if (charge != null)
{
charge.IsDeliver = "已发货";
charge.IsReceive = "已收货";
charge.ConfirmReceivedTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
con.Update <Tb_Charge_Receipt>(charge);
}
else
{
str = "修改失败";
}
}
catch (Exception ex)
{
str = ex.Message;
}
if (str != "")
{
return(JSONHelper.FromString(false, str));
}
else
{
return(JSONHelper.FromString(true, ""));
}
}
/// <summary>
/// 删除订单 DelChargeReceipt
/// </summary>
/// <param name="row"></param>
/// ReceiptCode 商品订单票据编码
/// 返回：
/// true:""
/// false:删除失败
/// <returns></returns>
private string DelChargeReceipt(DataRow row)
{
if (!row.Table.Columns.Contains("ReceiptCode") || string.IsNullOrEmpty(row["ReceiptCode"].ToString()))
{
return(JSONHelper.FromString(false, "订单不能为空"));
}
string str = "";
try
{
IDbConnection con = new SqlConnection(PubConstant.GetConnectionString("BusinessContionString"));
Tb_Charge_Receipt charge = BussinessCommon.GetChargeReceiptModel(row["ReceiptCode"].ToString());
if (charge != null)
{
charge.IsDelete = 1;
con.Update <Tb_Charge_Receipt>(charge);
con.ExecuteScalar("update Tb_Charge_ReceiptDetail set RpdIsDelete=1 where ReceiptCode=@ReceiptCode", new { ReceiptCode = charge.Id }, null, null, CommandType.Text);
}
else
{
str = "删除失败";
}
}
catch (Exception ex)
{
str = ex.Message;
}
if (str != "")
{
return(JSONHelper.FromString(false, str));
}
else
{
return(JSONHelper.FromString(true, ""));
}
}
/// <summary>
/// 加入购物车 SetShoppingCar
/// </summary>
/// <param name="row"></param>
/// UserId 用户编码【必填】
/// ResourcesID 商品编码【必填】
/// BussId 商家编码【必填】
/// Number 数量 【可不填，默认为1】
/// PropertysId 属性规格【格式：属性ID：规格ID,属性ID：规格ID,属性ID：规格ID】
/// 返回：
/// false：错误信息
/// true：操作成功
/// <returns></returns>
private string SetShoppingCar(DataRow row)
{
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString()))
{
return(JSONHelper.FromString(false, "用户编码不能为空"));
}
if (!row.Table.Columns.Contains("ResourcesID") || string.IsNullOrEmpty(row["ResourcesID"].ToString()))
{
return(JSONHelper.FromString(false, "商品编码不能为空"));
}
if (!row.Table.Columns.Contains("BussId") || string.IsNullOrEmpty(row["BussId"].ToString()))
{
return(JSONHelper.FromString(false, "商家编码不能为空"));
}
//if (!row.Table.Columns.Contains("PropertysId") || string.IsNullOrEmpty(row["PropertysId"].ToString()))
//{
// return JSONHelper.FromString(false, "规格编码不能为空");
//}
// 2017年9月23日16:47:32，谭洋
int?CorpId = null;
if (row.Table.Columns.Contains("CorpId") && !string.IsNullOrEmpty(row["CorpId"].ToString()))
{
CorpId = AppGlobal.StrToInt(row["CorpId"].ToString());
}
string UserId = row["UserId"].ToString();
string BussId = row["BussId"].ToString();
string ResourcesID = row["ResourcesID"].ToString();
int Number = 1;//加入购物车数量，默认1
//该参数值格式为： 属性ID：规格ID,属性ID：规格ID,属性ID：规格ID
string PropertysId = row["PropertysId"].ToString();
//获取属性查询条件
string PropertysIdWhere = BussinessCommon.GetShoppingPropertys(PropertysId);
if (row.Table.Columns.Contains("Number") && AppGlobal.StrToInt(row["Number"].ToString()) > 0)
{
Number = AppGlobal.StrToInt(row["Number"].ToString());
}
IDbConnection con = new SqlConnection(PubConstant.GetConnectionString("BusinessContionString"));
string str = "";
try
{
List <Tb_ShoppingCar> Car = new List <Tb_ShoppingCar>();
//查询购物车中是否有该商品
Car = BussinessCommon.GetShoppingCheck(UserId, ResourcesID);
Tb_Resources_Details Resources = BussinessCommon.GetResourcesModel(ResourcesID);
if (Resources == null || string.IsNullOrEmpty(Resources.ResourcesID))
{
return(JSONHelper.FromString(false, "该商品不存在"));
}
if (Car != null && Car.Count > 0)//如果已存在此商品，
{
int j = 0;
int z = 0;
foreach (Tb_ShoppingCar item in Car)//循环此人该购物车中的这种商品
{
z++;
DataSet ds = null;
if (PropertysIdWhere != "")
{
//查询属性明细中已有的属性
ds = BussinessCommon.GetShoppingDetailedCheck(item.Id, PropertysIdWhere);
}
//如果属性存在，新增
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
if (item.Number + Number > Resources.ResourcesCount)//库存检测
{
return(JSONHelper.FromString(false, "该商品库存不足"));
}
else//新增
{
SetShopping(CorpId, UserId, ResourcesID, BussId, Number, PropertysId, con);
}
j++;
break;
}
//属性不存在，则添加属性
if (z == Car.Count)
{
if (item.Number + Number > Resources.ResourcesCount)//库存检测
{
return(JSONHelper.FromString(false, "该商品库存不足"));
}
item.Number = item.Number + Number;
con.Update <Tb_ShoppingCar>(item);
//添加属性
SetShoppingCarDetailed(BussId, PropertysId, con, item);
}
}
}
else
{
if (Number > Resources.ResourcesCount)//库存检测
{
return(JSONHelper.FromString(false, "该商品库存不足"));
}
else//新增
{
SetShopping(CorpId, UserId, ResourcesID, BussId, Number, PropertysId, con);
}
}
}
catch (Exception ex)
{
str = ex.Message;
}
if (str != "")
{
return(JSONHelper.FromString(false, str));
}
else
{
return(JSONHelper.FromString(true, "操作成功"));
}
}
/// <summary>
/// 查询购物车 GetShoppingCar
/// </summary>
/// <param name="row"></param>
/// UserId 用户编码【必填】
/// PageIndex 默认1
/// PageSize 默认5
/// 返回：
/// Result：true
/// data:Id[购物车明细ID],Number[数量],ResourcesName[商品名称],ResourcesSalePrice[销售价格],Img[图片],ResourcesCount[库存],BussId[商家编号],ResourcesID[商品ID],BussName[商家名称]
/// AccountMoney：总金额
/// <returns></returns>
private string GetShoppingCar(DataRow row)
{
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString()))
{
return(JSONHelper.FromString(false, "用户编码不能为空"));
}
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());
}
int pageCount;
int Counts;
string sql = @"SELECT a.Id,a.Number,a.ResourcesID,a.CorpId,a.BussId,b.BussNature,b.BussName,c.ResourcesName,C.IsServiceType,c.PushContent,a.AddTime,
(SELECT TOP 1 value FROM SplitString(c.Img,',',1)) AS Img,
c.ResourcesSalePrice,c.ResourcesDisCountPrice,c.IsGroupBuy,c.GroupBuyPrice,C.GroupBuyStartDate,C.GroupBuyEndDate,c.IsSupportCoupon,c.MaximumCouponMoney
FROM Tb_ShoppingCar a LEFT JOIN Tb_System_BusinessCorp b on a.BussId=b.BussId
LEFT JOIN Tb_Resources_Details c on c.ResourcesID=a.ResourcesID
WHERE c.IsRelease = '是' AND c.IsStopRelease = '否' AND isnull(a.IsDelete,0)=0 AND isnull(c.IsDelete,0)=0 AND isnull(b.IsDelete,0)=0
AND isnull(b.IsClose,'未关闭')='未关闭'
AND a.UserId='" + row["UserId"].ToString() + "'";
//查询购物车明细
DataSet ds = BussinessCommon.GetList(out pageCount, out Counts, sql, PageIndex, PageSize, "AddTime", 0, "Id", PubConstant.GetConnectionString("BusinessContionString"), "*");
StringBuilder sb = new StringBuilder();
sb.Append("{\"Result\":\"true\",");
sb.Append("\"data\":");
if (ds == null || ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0)
{
sb.Append("[]");
}
else
{
//sb.Append(JSONHelper.FromDataTable(ds.Tables[0]));
int i = 0;
sb.Append("[");
foreach (DataRow item in ds.Tables[0].Rows)
{
if (i > 0)
{
sb.Append(",");
}
string sstr = JSONHelper.FromDataRow(item);
string ss = sstr.ToString().Substring(0, sstr.ToString().Length - 1);
sb.Append(ss);
sb.Append(",");
sb.Append("\"Property\":");
sb.Append("[");
int j = 0;
IDbConnection con = new SqlConnection(PubConstant.GetConnectionString("BusinessContionString"));
DataSet ds_Property = con.ExecuteReader(@" select p.PropertyName,s.SpecName,E.Price,E.DiscountAmount,E.GroupBuyingPrice
from Tb_ShoppingDetailed as d
left join Tb_Resources_Property as p on d.PropertysId = p.Id
left join Tb_Resources_Specifications as s on d.SpecId = s.Id
left join Tb_ShoppingCar as c on c.Id = d.ShoppingId
LEFT JOIN Tb_ResourcesSpecificationsPrice AS E ON E.ResourcesID=C.ResourcesID AND E.BussId=c.BussId and E.PropertyId=d.PropertysId and E.SpecId=d.SpecId
where d.ShoppingId = '" + item["Id"] + "'", null, null, null, CommandType.Text).ToDataSet();
foreach (DataRow dr in ds_Property.Tables[0].Rows)
{
if (j == 0)
{
sb.Append(JSONHelper.FromDataRow(dr));
}
else
{
sb.Append(",");
sb.Append(JSONHelper.FromDataRow(dr));
}
j++;
}
sb.Append("]");
sb.Append("}");
i++;
}
sb.Append("]");
}
sb.Append("}");
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>
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());
}
/// <summary>
/// 生成订单 SetOrder
/// </summary>
/// <param name="row"></param>
/// ShoppingDetailed 购物车明细编号【必填】
/// UserAddressId 收货地址编号【必填】
/// UserId 用户编号【必填】
/// 返回：
/// true:""
/// false:errmessage
/// <returns></returns>
private string SetOrder(DataRow row)
{
if (!row.Table.Columns.Contains("ShoppingDetailed") || string.IsNullOrEmpty(row["ShoppingDetailed"].ToString()))
{
return(JSONHelper.FromString(false, "购物车明细编号不能为空"));
}
if (!row.Table.Columns.Contains("UserAddressId") || string.IsNullOrEmpty(row["UserAddressId"].ToString()))
{
return(JSONHelper.FromString(false, "收货地址编号不能为空"));
}
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString()))
{
return(JSONHelper.FromString(false, "用户编号不能为空"));
}
IDbConnection con = new SqlConnection(PubConstant.GetConnectionString("BusinessContionString"));
string str = "";
try
{
//获取收货地址
Tb_User_Address UserAddress = BussinessCommon.GetAddressModel(row["UserAddressId"].ToString());
//获取购物明细
DataTable Dt_ShoppingDetailed = BussinessCommon.GetShoppingDetailedView(" Id in (" + BussinessCommon.GetShoppingDetailedIdS(row["ShoppingDetailed"].ToString()) + ")");
if (Dt_ShoppingDetailed == null || Dt_ShoppingDetailed.Rows.Count <= 0)
{
return(JSONHelper.FromString(false, "生成订单失败：未找到购物明细"));
}
Tb_Charge_Receipt Order = new Tb_Charge_Receipt();
//根据商家ID分组
var query = from t in Dt_ShoppingDetailed.AsEnumerable()
group t by new { t1 = t.Field <string>("BussId") } into m
select new
{
BussId = m.Key.t1
};
if (query.ToList().Count > 0)
{
query.ToList().ForEach(q =>
{
//生成订单
Order = new Tb_Charge_Receipt();
Order.Id = Guid.NewGuid().ToString();
Order.BussId = q.BussId;
Order.OrderId = Guid.NewGuid().ToString();
Order.ReceiptSign = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999).ToString();
Order.UserId = row["UserId"].ToString();
if (UserAddress != null)
{
if (UserAddress.Address != "" && UserAddress.Address.Split(',').Length > 0)
{
Order.Name = UserAddress.Address.Split(',')[0];
Order.DeliverAddress = UserAddress.Address.Split(',')[1];
}
Order.Mobile = UserAddress.Mobile;
}
else
{
throw new Exception("该收货地址不存在");
}
Order.ReceiptDate = DateTime.Now;
Order.IsReceive = "未收货";
Order.IsDeliver = "未发货";
Order.IsPay = "未付款";
Order.IsDelete = 0;
//查询此商家的所有购物车明细
DataRow[] dr = Dt_ShoppingDetailed.Select("BussId=" + q.BussId);
//生成订单明细
foreach (DataRow item in dr)
{
Tb_Charge_ReceiptDetail OrderDetail = new Tb_Charge_ReceiptDetail();
OrderDetail.RpdCode = Guid.NewGuid().ToString();
OrderDetail.ReceiptCode = Order.Id;
OrderDetail.ResourcesID = item["ResourcesID"].ToString();
OrderDetail.Quantity = AppGlobal.StrToInt(item["Number"].ToString());
OrderDetail.SalesPrice = AppGlobal.StrToDec(item["ResourcesSalePrice"].ToString());
OrderDetail.DiscountPrice = AppGlobal.StrToDec(item["ResourcesDisCountPrice"].ToString());
OrderDetail.GroupPrice = AppGlobal.StrToDec(item["GroupBuyPrice"].ToString());
OrderDetail.DetailAmount = AppGlobal.StrToDec(item["SubtotalMoney"].ToString());;
OrderDetail.RpdMemo = "";
OrderDetail.RpdIsDelete = 0;
//累计订单金额【取销售价格】
Order.Amount += AppGlobal.StrToDec(OrderDetail.SalesPrice.ToString()) * AppGlobal.StrToDec(OrderDetail.Quantity.ToString());
//生成订单明细
con.Insert <Tb_Charge_ReceiptDetail>(OrderDetail);
}
//生成订单
con.Insert <Tb_Charge_Receipt>(Order);
//判断此商家中是否存在该客户
string sqlStr = "select BussId from Tb_Customer_List where BussId='" + q.BussId + "' and UserId='" + row["UserId"] + "' and Mobile='" + UserAddress.Mobile + "'";
List <string> list = con.Query <string>(sqlStr, null, null, true, null, CommandType.Text).ToList <string>();
//如果不存在，将此客户添加至此商家的客户资料中
if (list.Count <= 0)
{
Tb_Customer_List cust = new Tb_Customer_List();
cust.Id = Guid.NewGuid().ToString();
cust.BussId = q.BussId;
cust.UserId = row["UserId"].ToString();
cust.Mobile = UserAddress.Mobile;
cust.CustName = "";
con.Insert <Tb_Customer_List>(cust);
}
});
}
}
catch (Exception ex)
{
str = ex.Message;
}
if (str != "")
{
return(JSONHelper.FromString(false, str));
}
else
{
return(JSONHelper.FromString(true, ""));
}
}
/// <summary>
/// 查询订单 GetORdersAll
/// </summary>
/// <param name="row"></param>
/// UserId 用户编号【必填】
/// PageIndex
/// PageSize
/// IsDeliver 是否发货
/// IsPay 付款状态
/// HandleState 处理状态
/// IsReceive 是否收货
/// 返回：
/// {Result:true,data:[BussName 商家名称,Id 订单ID,ReceiptSign 订单编号,Amount 总金额,HandleState 订单状态,IsReceive 是否收货,IsPay 付款状态,IsDeliver 是否发货,OrderDetailNumber 明细数量,Details:[RpdCode 明细id,Quantity 数量,SalesPrice 销售价格,DetailAmount 小计,ResourcesName 商品名称,Img 商品图片],[]],[],[]}
/// 备注：当IsReceive 值为'已发货'时，订单字段中多二个物流信息：Express 物流名称，ExpressNum 物流编号
/// <returns></returns>
private string GetORdersAll(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 = 5;
#pragma warning disable CS0219 // 变量“IsDeliver”已被赋值，但从未使用过它的值
string IsDeliver = ""; //是否发货
#pragma warning restore CS0219 // 变量“IsDeliver”已被赋值，但从未使用过它的值
#pragma warning disable CS0219 // 变量“IsPay”已被赋值，但从未使用过它的值
string IsPay = ""; //付款状态
#pragma warning restore CS0219 // 变量“IsPay”已被赋值，但从未使用过它的值
#pragma warning disable CS0219 // 变量“HandleState”已被赋值，但从未使用过它的值
string HandleState = ""; //处理状态
#pragma warning restore CS0219 // 变量“HandleState”已被赋值，但从未使用过它的值
#pragma warning disable CS0219 // 变量“IsReceive”已被赋值，但从未使用过它的值
string IsReceive = ""; //是否收货
#pragma warning restore CS0219 // 变量“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());
}
var FldName = @"BussId,BussName,Id,ReceiptSign,Amount,RealAmount,IsUseCoupon,HandleState,
IsReceive,IsPay,IsDeliver,OrderDetailNumber,OrderId,
IsEevaluate,ReceiptDate,Express,ExpressNum";
StringBuilder sb = new StringBuilder();
try
{
var condition = $" AND r.UserId='{ row["UserId"].ToString() }'";
// 支付状态
if (row.Table.Columns.Contains("IsPay") && row["IsPay"].ToString() != "")
{
condition += $" AND r.IsPay='{ row["IsPay"].ToString() }'";
}
else
{
condition += $" AND isnull(r.IsPay,'未付款')='未付款'";
}
// 发货状态
if (row.Table.Columns.Contains("IsDeliver") && row["IsDeliver"].ToString() != "")
{
condition += $" AND r.IsDeliver='{ row["IsDeliver"].ToString() }'";
}
else
{
condition += $" AND isnull(r.IsDeliver,'未发货')='未发货'";
}
// 收货状态
if (row.Table.Columns.Contains("IsReceive") && row["IsReceive"].ToString() != "")
{
condition += $" AND r.IsReceive='{ row["IsReceive"].ToString() }'";
}
else
{
condition += $" AND isnull(r.IsReceive,'未收货')='未收货'";
}
// 操作确认
if (row.Table.Columns.Contains("HandleState") && row["HandleState"].ToString() != "")
{
condition += $" AND r.HandleState='{ row["HandleState"].ToString() }'";
}
//查询订单
var sql = @"SELECT b.BussName,a.*,
OrderDetailNumber=
(
SELECT COUNT(1) FROM Tb_Charge_ReceiptDetail x
LEFT JOIN Tb_Resources_Details y on x.ResourcesID=y.ResourcesID
WHERE x.ReceiptCode=a.Id and isnull(x.RpdIsDelete,0)=0
AND y.IsRelease='是' AND y.IsStopRelease='否' AND isnull(y.IsDelete,0)=0
),
IsEevaluate=
(
SELECT count(1) FROM Tb_Resources_CommodityEvaluation e
LEFT JOIN Tb_Charge_ReceiptDetail t ON t.RpdCode=e.RpdCode
WHERE t.ReceiptCode=a.Id and isnull(t.RpdIsDelete,0)=0 and isnull(e.IsDelete,0)=0
)
FROM Tb_Charge_Receipt a
LEFT JOIN Tb_System_BusinessCorp b on a.BussId=b.BussId
WHERE isnull(a.IsDelete,0)=0 AND isnull(b.IsDelete,0)=0 " + condition;
DataSet Ds_Order = BussinessCommon.GetList(out pageCount, out Counts, sql, 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];
DataSet 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));
}
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());
}