/// <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 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>
/// 查询购物车 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());
}
/// <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());
}
