public PageModel <StoreBalanceOrderInfo> GetBalanceOrders(StoreBalanceOrderQuery query)
{
if (query.IsBalanceOver)
{
return(new StoreBalanceDao().GetOverBalanceOrders(query));
}
StringBuilder stringBuilder = new StringBuilder($"StoreId = {query.StoreId} and IsBalanceOver = {0} ");
stringBuilder.AppendFormat(" AND (OrderStatus={0} OR (OrderStatus={1} AND ShippingDate IS NOT NULL))", 5, 4);
if (query.StartDate.HasValue && query.EndDate.HasValue)
{
stringBuilder.AppendFormat(" AND OrderDate BETWEEN '{0}' AND '{1}'", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value), DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
else if (query.StartDate.HasValue)
{
stringBuilder.AppendFormat(" AND DATEDIFF(dd,'{0}',OrderDate) >= 0", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
else if (query.EndDate.HasValue)
{
stringBuilder.AppendFormat(" AND DATEDIFF(dd,'{0}',OrderDate) <= 0", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
if (!string.IsNullOrEmpty(query.OrderId))
{
stringBuilder.AppendFormat(" AND OrderId = '{0}'", DataHelper.CleanSearchString(query.OrderId));
}
return(DataHelper.PagingByRownumber <StoreBalanceOrderInfo>(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "Hishop_Orders", "OrderId", stringBuilder.ToString(), "OrderId,OrderDate,OrderTotal,AdjustedFreight AS Freight,DeductionMoney,CouponValue,RefundAmount,IsStoreCollect,Tax,OrderType"));
}
public decimal GetOverBalanceOrdersTotal(StoreBalanceOrderQuery query)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT ISNULL(SUM(ISNULL(InCome,0)),0) - ISNULL(SUM(ISNULL(Expenses,0)),0) AS BalanceTotal FROM Hishop_StoreBalanceDetails WHERE TradeType = " + 2 + " AND StoreId = " + query.StoreId);
if (query.StartDate.HasValue)
{
stringBuilder.AppendFormat(" AND TradeNo IN(SELECT OrderId FROM Hishop_Orders WHERE DATEDIFF(dd,'{0}',OrderDate) >= 0)", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
if (query.EndDate.HasValue)
{
stringBuilder.AppendFormat(" AND TradeNo IN(SELECT OrderId FROM Hishop_Orders WHERE DATEDIFF(dd,'{0}',OrderDate) <= 0)", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
if (query.OverStartDate.HasValue && query.OverEndDate.HasValue)
{
stringBuilder.AppendFormat(" AND (CreateTime BETWEEN '{0}' AND '{1}')", DataHelper.GetSafeDateTimeFormat(query.OverStartDate.Value), DataHelper.GetSafeDateTimeFormat(query.OverEndDate.Value));
}
else if (query.OverStartDate.HasValue)
{
stringBuilder.AppendFormat(" AND DATEDIFF(dd,'{0}',CreateTime) >= 0", DataHelper.GetSafeDateTimeFormat(query.OverStartDate.Value));
}
else if (query.OverEndDate.HasValue)
{
stringBuilder.AppendFormat(" AND DATEDIFF(dd,'{0}',CreateTime) <= 0", DataHelper.GetSafeDateTimeFormat(query.OverEndDate.Value));
}
if (!string.IsNullOrEmpty(query.OrderId))
{
stringBuilder.AppendFormat(" AND TradeNo = '{0}'", DataHelper.CleanSearchString(query.OrderId));
}
return(base.database.ExecuteScalar(CommandType.Text, stringBuilder.ToString()).ToDecimal(0));
}
public PageModel <StoreBalanceOrderInfo> GetOverBalanceOrders(StoreBalanceOrderQuery query)
{
StringBuilder stringBuilder = new StringBuilder($"StoreId = {query.StoreId} AND TradeType = {2} ");
if (query.StartDate.HasValue && query.EndDate.HasValue)
{
stringBuilder.AppendFormat(" AND TradeNo IN(SELECT OrderId FROM Hishop_Orders WHERE OrderDate BETWEEN '{0}' AND '{1}')", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value), DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
else if (query.StartDate.HasValue)
{
stringBuilder.AppendFormat(" AND TradeNo IN(SELECT OrderId FROM Hishop_Orders WHERE DATEDIFF(dd,'{0}',OrderDate) >= 0)", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
else if (query.EndDate.HasValue)
{
stringBuilder.AppendFormat(" AND TradeNo IN(SELECT OrderId FROM Hishop_Orders WHERE DATEDIFF(dd,'{0}',OrderDate) <= 0)", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
if (query.EndDate.HasValue)
{
stringBuilder.AppendFormat(" AND TradeNo IN(SELECT OrderId FROM Hishop_Orders WHERE DATEDIFF(dd,'{0}',OrderDate) <= 0)", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
if (query.OverStartDate.HasValue && query.OverEndDate.HasValue)
{
stringBuilder.AppendFormat(" AND CreateTime BETWEEN '{0}' AND '{1}'", DataHelper.GetSafeDateTimeFormat(query.OverStartDate.Value), DataHelper.GetSafeDateTimeFormat(query.OverEndDate.Value));
}
else if (query.OverStartDate.HasValue)
{
stringBuilder.AppendFormat(" AND DATEDIFF(dd,'{0}',CreateTime) >= 0)", DataHelper.GetSafeDateTimeFormat(query.OverStartDate.Value));
}
else if (query.OverEndDate.HasValue)
{
stringBuilder.AppendFormat(" AND DATEDIFF(dd,'{0}',CreateTime) <= 0)", DataHelper.GetSafeDateTimeFormat(query.OverEndDate.Value));
}
if (!string.IsNullOrEmpty(query.OrderId))
{
stringBuilder.AppendFormat(" AND TradeNo = '{0}'", DataHelper.CleanSearchString(query.OrderId));
}
query.SortBy = "TradeDate";
return(DataHelper.PagingByRownumber <StoreBalanceOrderInfo>(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "Hishop_StoreBalanceDetails", "JournalNumber", stringBuilder.ToString(), "TradeNo AS OrderId,TradeDate AS OrderDate,PlatCommission,Income AS OverBalance"));
}
public decimal GetNotOverBalanceOrdersTotal(StoreBalanceOrderQuery query, decimal commsionRate)
{
decimal num = default(decimal);
string text = $"SELECT SUM(OrderTotal + ISNULL(DeductionMoney,0) + ISNULL(CouponValue,0) - ROUND(Tax,2) - ISNULL(RefundAmount,0) - ROUND((OrderTotal + ISNULL(DeductionMoney,0) + ISNULL(CouponValue,0) - Tax - ISNULL(RefundAmount,0) - AdjustedFreight) * {commsionRate / 100m},2)) AS PlatTotal FROM Hishop_Orders WHERE IsStoreCollect = 0 ";
string text2 = $"SELECT SUM(ISNULL(DeductionMoney,0) + ISNULL(CouponValue,0) - ROUND(Tax,2) - ROUND((OrderTotal + ISNULL(DeductionMoney,0) + ISNULL(CouponValue,0) - Tax - ISNULL(RefundAmount,0) - AdjustedFreight) * {commsionRate / 100m},2)) AS StoreTotal FROM Hishop_Orders WHERE IsStoreCollect = 1 ";
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("AND StoreId = {0} and IsBalanceOver = 0 ", query.StoreId);
stringBuilder.AppendFormat(" AND (OrderStatus = {0} OR (OrderStatus={1} AND ShippingDate IS NOT NULL))", 5, 4);
if (query.StartDate.HasValue)
{
stringBuilder.AppendFormat(" AND DATEDIFF(dd,'{0}',OrderDate) >= 0", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
if (query.EndDate.HasValue)
{
stringBuilder.AppendFormat(" AND DATEDIFF(dd,'{0}',OrderDate) <= 0", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
if (!string.IsNullOrEmpty(query.OrderId))
{
stringBuilder.AppendFormat(" AND OrderId = '{0}'", DataHelper.CleanSearchString(query.OrderId));
}
string commandText = text + stringBuilder.ToString() + ";" + text2 + stringBuilder.ToString() + ";";
using (IDataReader dataReader = base.database.ExecuteReader(CommandType.Text, commandText))
{
if (dataReader.Read())
{
num = ((IDataRecord)dataReader)["PlatTotal"].ToDecimal(0);
}
if (dataReader.NextResult() && dataReader.Read())
{
num += ((IDataRecord)dataReader)["StoreTotal"].ToDecimal(0);
}
}
return(num);
}
public static decimal GetOverBalanceOrdersTotal(StoreBalanceOrderQuery query)
{
return(new StoreBalanceDao().GetOverBalanceOrdersTotal(query));
}
public static decimal GetNotOverBalanceOrdersTotal(StoreBalanceOrderQuery query, decimal commsionRate)
{
return(new StoreBalanceDao().GetNotOverBalanceOrdersTotal(query, commsionRate));
}
public static PageModel <StoreBalanceOrderInfo> GetBalanceOrders(StoreBalanceOrderQuery query)
{
return(new StoreBalanceDao().GetBalanceOrders(query));
}
