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);
}