public static UserReportModel getUserReport(string CreatedBy, DateTime fromDate, DateTime toDate) { var list = new List <RefundReport>(); var sql = new StringBuilder(); sql.AppendLine(String.Format("SELECT Ord.ID, SUM(ISNULL(OrdDetail.Quantity, 0)) AS Quantity, SUM(OrdDetail.Quantity * ISNULL(Product.CostOfGood, Variable.CostOfGood)) AS TotalCost, SUM(OrdDetail.Quantity * OrdDetail.SoldPricePerProduct) AS TotalRevenue, SUM(OrdDetail.Quantity * OrdDetail.RefundFeePerProduct) AS TotalRefundFee")); sql.AppendLine(String.Format("FROM tbl_RefundGoods AS Ord")); sql.AppendLine(String.Format("INNER JOIN tbl_RefundGoodsDetails AS OrdDetail")); sql.AppendLine(String.Format("ON Ord.ID = OrdDetail.RefundGoodsID")); sql.AppendLine(String.Format("LEFT JOIN tbl_ProductVariable AS Variable")); sql.AppendLine(String.Format("ON OrdDetail.SKU = Variable.SKU")); sql.AppendLine(String.Format("LEFT JOIN tbl_Product AS Product")); sql.AppendLine(String.Format("ON OrdDetail.SKU = Product.ProductSKU")); sql.AppendLine(String.Format("WHERE 1 = 1")); if (!String.IsNullOrEmpty(CreatedBy)) { sql.AppendLine(String.Format(" AND Ord.CreatedBy = '{0}'", CreatedBy)); } sql.AppendLine(String.Format(" AND (CONVERT(NVARCHAR(10), Ord.CreatedDate, 121) BETWEEN CONVERT(NVARCHAR(10), '{0:yyyy-MM-dd}', 121) AND CONVERT(NVARCHAR(10), '{1:yyyy-MM-dd}', 121))", fromDate, toDate)); sql.AppendLine(String.Format("GROUP BY Ord.ID, OrdDetail.RefundFeePerProduct")); var reader = (IDataReader)SqlHelper.ExecuteDataReader(sql.ToString()); while (reader.Read()) { var entity = new RefundReport(); entity.ID = Convert.ToInt32(reader["ID"]); entity.Quantity = Convert.ToInt32(reader["Quantity"]); entity.TotalCost = Convert.ToDouble(reader["TotalCost"]); entity.TotalRevenue = Convert.ToDouble(reader["TotalRevenue"]); entity.TotalRefundFee = Convert.ToInt32(reader["TotalRefundFee"]); list.Add(entity); } reader.Close(); return(new UserReportModel() { totalRefundQuantity = list.Sum(x => x.Quantity), totalRevenue = list.Sum(x => x.TotalRevenue), totalCost = list.Sum(x => x.TotalCost), totalRefundFee = list.Sum(x => x.TotalRefundFee), }); }
public static List <RefundProductReportModel> getRefundProductReport(string SKU, int CategoryID, string CreatedBy, DateTime fromDate, DateTime toDate) { var list = new List <RefundReport>(); var sql = new StringBuilder(); sql.AppendLine("BEGIN"); if (CategoryID > 0) { sql.AppendLine(String.Empty); sql.AppendLine("WITH category AS("); sql.AppendLine(" SELECT"); sql.AppendLine(" ID"); sql.AppendLine(" , CategoryName"); sql.AppendLine(" , ParentID"); sql.AppendLine(" FROM"); sql.AppendLine(" tbl_Category"); sql.AppendLine(" WHERE"); sql.AppendLine(" 1 = 1"); sql.AppendLine(" AND ID = " + CategoryID); sql.AppendLine(""); sql.AppendLine(" UNION ALL"); sql.AppendLine(""); sql.AppendLine(" SELECT"); sql.AppendLine(" CHI.ID"); sql.AppendLine(" , CHI.CategoryName"); sql.AppendLine(" , CHI.ParentID"); sql.AppendLine(" FROM"); sql.AppendLine(" category AS PAR"); sql.AppendLine(" INNER JOIN tbl_Category AS CHI"); sql.AppendLine(" ON PAR.ID = CHI.ParentID"); sql.AppendLine(")"); sql.AppendLine("SELECT"); sql.AppendLine(" ID"); sql.AppendLine(", CategoryName"); sql.AppendLine(", ParentID"); sql.AppendLine("INTO #category"); sql.AppendLine("FROM category;"); } sql.AppendLine("SELECT"); sql.AppendLine(" CONVERT(VARCHAR(10), Ord.CreatedDate, 121) AS CreatedDate,"); sql.AppendLine(" Ord.ID,"); sql.AppendLine(" OrdDetail.SKU,"); sql.AppendLine(" OrdDetail.Quantity,"); sql.AppendLine(" OrdDetail.SoldPricePerProduct,"); sql.AppendLine(" OrdDetail.RefundFeePerProduct"); sql.AppendLine("INTO #data"); sql.AppendLine("FROM tbl_RefundGoods AS Ord"); sql.AppendLine("INNER JOIN tbl_RefundGoodsDetails AS OrdDetail"); sql.AppendLine("ON Ord.ID = OrdDetail.RefundGoodsID"); sql.AppendLine("WHERE 1 = 1"); if (!String.IsNullOrEmpty(CreatedBy)) { sql.AppendLine(String.Format(" AND Ord.CreatedBy = '{0}'", CreatedBy)); } if (!String.IsNullOrEmpty(SKU)) { sql.AppendLine(String.Format(" AND OrdDetail.SKU LIKE '{0}%'", SKU)); } sql.AppendLine(String.Format(" AND CONVERT(NVARCHAR(10), Ord.CreatedDate, 121) BETWEEN CONVERT(NVARCHAR(10), '{0:yyyy-MM-dd}', 121) AND CONVERT(NVARCHAR(10), '{1:yyyy-MM-dd}', 121)", fromDate, toDate)); sql.AppendLine("SELECT"); sql.AppendLine(" DAT.CreatedDate,"); sql.AppendLine(" DAT.ID,"); sql.AppendLine(" SUM(ISNULL(DAT.Quantity, 0)) AS Quantity,"); sql.AppendLine(" SUM(DAT.Quantity * ISNULL(PRO.CostOfGood, 0)) AS TotalCost,"); sql.AppendLine(" SUM(DAT.Quantity * DAT.SoldPricePerProduct) AS TotalRevenue,"); sql.AppendLine(" SUM(DAT.Quantity * DAT.RefundFeePerProduct) AS TotalRefundFee"); sql.AppendLine("FROM #data AS DAT"); sql.AppendLine("INNER JOIN ("); sql.AppendLine(" SELECT"); sql.AppendLine(" Product.CategoryID,"); sql.AppendLine(" ("); sql.AppendLine(" CASE Product.ProductStyle"); sql.AppendLine(" WHEN 1 THEN Product.ProductSKU"); sql.AppendLine(" ELSE Variable.SKU"); sql.AppendLine(" END"); sql.AppendLine(" ) AS SKU,"); sql.AppendLine(" ("); sql.AppendLine(" CASE Product.ProductStyle"); sql.AppendLine(" WHEN 1 THEN Product.CostOfGood"); sql.AppendLine(" ELSE Variable.CostOfGood"); sql.AppendLine(" END"); sql.AppendLine(" ) AS CostOfGood"); sql.AppendLine(" FROM tbl_Product AS Product"); sql.AppendLine(" LEFT JOIN tbl_ProductVariable AS Variable"); sql.AppendLine(" ON Product.ID = Variable.ProductID"); sql.AppendLine(" WHERE 1 = 1"); if (CategoryID > 0) { sql.AppendLine(" AND EXISTS("); sql.AppendLine(" SELECT"); sql.AppendLine(" NULL AS DUMMY"); sql.AppendLine(" FROM"); sql.AppendLine(" #category"); sql.AppendLine(" WHERE"); sql.AppendLine(" ID = Product.CategoryID"); sql.AppendLine(" )"); } sql.AppendLine(") AS PRO"); sql.AppendLine("ON DAT.SKU = PRO.SKU"); sql.AppendLine("GROUP BY"); sql.AppendLine(" DAT.CreatedDate"); sql.AppendLine(", DAT.ID"); sql.AppendLine(" END"); var reader = (IDataReader)SqlHelper.ExecuteDataReader(sql.ToString()); while (reader.Read()) { var entity = new RefundReport(); entity.CreatedDate = Convert.ToDateTime(reader["CreatedDate"]); entity.ID = Convert.ToInt32(reader["ID"]); entity.Quantity = Convert.ToInt32(reader["Quantity"]); entity.TotalCost = Convert.ToDouble(reader["TotalCost"]); entity.TotalRevenue = Convert.ToDouble(reader["TotalRevenue"]); entity.TotalRefundFee = Convert.ToInt32(reader["TotalRefundFee"]); list.Add(entity); } reader.Close(); var result = list.GroupBy(g => g.CreatedDate) .Select(x => new RefundProductReportModel() { reportDate = x.Key, totalRefund = x.Sum(s => s.Quantity), totalRevenue = x.Sum(s => s.TotalRevenue), totalCost = x.Sum(s => s.TotalCost), totalRefundFee = x.Sum(s => s.TotalRefundFee), }) .OrderBy(o => o.reportDate) .ToList(); return(result); }