public StoreStatisticsInfo GetStoreCashierStatistics(int storeId, DateTime?startTime, DateTime?endTime) { if (!startTime.HasValue) { startTime = DateTime.Now.Date; } string text = " AND {0} >= '" + DataHelper.GetSafeDateTimeFormat(startTime.Value) + "'"; if (endTime.HasValue) { text = text + " AND {0} <= '" + DataHelper.GetSafeDateTimeFormat(endTime.Value) + "'"; } string text2 = "hishop.plugins.payment.cashreceipts"; StringBuilder stringBuilder = new StringBuilder("SELECT "); stringBuilder.Append("(SELECT SUM(OrderTotal) FROM Hishop_Orders WHERE StoreId = {0} " + string.Format(text, "PayDate") + " AND ParentOrderId<>'-1' ) AS TodayCashierTotal,"); stringBuilder.Append("(SELECT SUM(OrderTotal) FROM Hishop_Orders WHERE StoreId = {0} AND LOWER(GateWay) = '" + text2 + "'" + string.Format(text, "PayDate") + " AND ParentOrderId<>'-1' ) AS CashCashierTotal,"); stringBuilder.Append("(SELECT SUM(OrderTotal) FROM Hishop_Orders WHERE StoreId = {0} AND LOWER(GateWay) <> '" + text2 + "'" + string.Format(text, "PayDate") + " AND ParentOrderId<>'-1' ) AS OnlinePayCashierTotal,"); stringBuilder.Append("(SELECT SUM(OrderTotal) FROM Hishop_Orders WHERE StoreId = {0} AND ShippingModeId = -2" + string.Format(text, "PayDate") + " AND ParentOrderId<>'-1' ) AS OnDoorCashierTotal,"); stringBuilder.Append("(SELECT SUM(OrderTotal) FROM Hishop_Orders WHERE StoreId = {0} AND IsStoreCollect = 1" + string.Format(text, "PayDate") + " AND ParentOrderId<>'-1' ) AS OfflineCashierTotal,"); stringBuilder.Append("(SELECT SUM(OrderTotal) FROM Hishop_Orders WHERE StoreId = {0} AND IsStoreCollect = 0" + string.Format(text, "PayDate") + " AND ParentOrderId<>'-1' ) AS PlatCashierTotal"); DbCommand sqlStringCommand = base.database.GetSqlStringCommand(stringBuilder.ToString()); StoreStatisticsInfo storeStatisticsInfo = new StoreStatisticsInfo(); using (IDataReader dataReader = base.database.ExecuteReader(sqlStringCommand)) { if (dataReader.Read()) { storeStatisticsInfo.TodayCashierTotal = ((IDataRecord)dataReader)["TodayCashierTotal"].ToDecimal(0); storeStatisticsInfo.CashCashierTotal = ((IDataRecord)dataReader)["CashCashierTotal"].ToDecimal(0); storeStatisticsInfo.OnlinePayCashierTotal = ((IDataRecord)dataReader)["OnlinePayCashierTotal"].ToDecimal(0); storeStatisticsInfo.OnDoorCashierTotal = ((IDataRecord)dataReader)["OnDoorCashierTotal"].ToDecimal(0); storeStatisticsInfo.OfflineCashierTotal = ((IDataRecord)dataReader)["OfflineCashierTotal"].ToDecimal(0); storeStatisticsInfo.PlatCashierTotal = ((IDataRecord)dataReader)["PlatCashierTotal"].ToDecimal(0); } } return(storeStatisticsInfo); }
public StoreStatisticsInfo GetStoreOrderStatistics(int storeId) { StringBuilder stringBuilder = new StringBuilder("SELECT "); stringBuilder.Append("(SELECT COUNT(OrderId) FROM Hishop_Orders WHERE IsConfirm = 0 AND ItemStatus = 0 AND StoreId = {0} AND ShippingModeId = -2 AND ParentOrderId<>'-1' AND (OrderStatus = " + 2 + " OR (OrderStatus = " + 1 + " AND PaymentTypeId = -3))) AS WaitConfirmTotal,"); stringBuilder.Append("(SELECT COUNT(OrderId) FROM Hishop_Orders WHERE (OrderStatus = " + 2 + " OR (OrderStatus = " + 1 + " AND Gateway = 'hishop.plugins.payment.podrequest')) AND ItemStatus = 0 AND ShippingModeId <> -2 AND ParentOrderId<>'-1' AND OrderType <>" + 6 + " AND StoreId = {0}) AS WaitSendGoodsTotal,"); stringBuilder.Append("(SELECT COUNT(OrderId) FROM Hishop_Orders WHERE IsConfirm = 1 AND (ShippingModeId = -2) AND StoreId = {0} AND ItemStatus = 0 AND ParentOrderId<>'-1' AND (OrderStatus = " + 2 + " OR OrderStatus = " + 1 + ")) AS WaitPickGoodsTotal,"); stringBuilder.Append("(SELECT COUNT(OrderId) FROM Hishop_Orders WHERE OrderStatus = " + 3 + " AND StoreId = {0} AND ParentOrderId<>'-1' AND ShippingModeId <> -2) AS WaitGetGoodsTotal,"); string text = " StoreId = {0} "; text = text + " AND (HandleStatus = " + 0 + " AND AfterSaleType = " + 0; text = text + " OR (HandleStatus NOT IN(" + 2 + "," + 1 + ") AND AfterSaleType = " + 1 + ")"; text = text + " OR (HandleStatus = " + 0 + " AND AfterSaleType = " + 3 + ")"; text = text + " OR (HandleStatus NOT IN(" + 2 + "," + 1 + ") AND AfterSaleType = " + 2 + ")"; text += ")"; stringBuilder.Append("(SELECT COUNT(*) FROM vw_Hishop_AfterSaleRecords WHERE " + text + ") AS WaitDealAfterSaleTotal,"); stringBuilder.Append("(SELECT COUNT(*) FROM vw_Hishop_AfterSaleRecords WHERE StoreId = {0} and HandleStatus in (0,4) and AfterSaleType in(1,2)) AS WaitReceiptAfterSaleTotal,"); stringBuilder.Append("(SELECT COUNT(OrderId) FROM Hishop_Orders WHERE OrderDate >= '" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date) + "' AND ParentOrderId<>'-1' ) AS TodayOrderTotal,"); stringBuilder.Append("(SELECT COUNT(DISTINCT(ProductId)) FROM Hishop_StoreSKUs WHERE (Stock <= 0 OR Stock <= WarningStock) AND StoreId = {0}) AS StockWarningTotal"); DbCommand sqlStringCommand = base.database.GetSqlStringCommand(string.Format(stringBuilder.ToString(), storeId)); StoreStatisticsInfo storeStatisticsInfo = new StoreStatisticsInfo(); using (IDataReader dataReader = base.database.ExecuteReader(sqlStringCommand)) { if (dataReader.Read()) { storeStatisticsInfo.WaitConfirmTotal = ((IDataRecord)dataReader)["WaitConfirmTotal"].ToInt(0); storeStatisticsInfo.WaitSendGoodsTotal = ((IDataRecord)dataReader)["WaitSendGoodsTotal"].ToInt(0); storeStatisticsInfo.WaitPickGoodsTotal = ((IDataRecord)dataReader)["WaitPickGoodsTotal"].ToInt(0); storeStatisticsInfo.WaitGetGoodsTotal = ((IDataRecord)dataReader)["WaitGetGoodsTotal"].ToInt(0); storeStatisticsInfo.WaitDealAfterSaleTotal = ((IDataRecord)dataReader)["WaitDealAfterSaleTotal"].ToInt(0); storeStatisticsInfo.TodayOrderTotal = ((IDataRecord)dataReader)["TodayOrderTotal"].ToInt(0); storeStatisticsInfo.StockWarningTotal = ((IDataRecord)dataReader)["StockWarningTotal"].ToInt(0); storeStatisticsInfo.WaitReceiptAfterSaleTotal = ((IDataRecord)dataReader)["WaitReceiptAfterSaleTotal"].ToInt(0); } } return(storeStatisticsInfo); }