public System.Data.DataTable SalesPerItemByGroup(string ProductGroupName, string TransactionNo, string CustomerName, string CashierName, string TerminalNo,
DateTime StartTransactionDate, DateTime EndTransactionDate, TransactionStatus Status, PaymentTypes PaymentType, SaleperItemFilterType pvtSaleperItemFilterType)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = "CALL procGenerateSalesPerItemByGroup(@SessionID, @ProductGroupName, @TransactionNo, @CustomerName, @CashierName, @TerminalNo, @StartTransactionDate, @EndTransactionDate);";
Random clsRandom = new Random();
MySqlParameter prmSessionID = new MySqlParameter("@SessionID", clsRandom.Next(1234567, 99999999));
cmd.Parameters.Add(prmSessionID);
cmd.Parameters.AddWithValue("@ProductGroupName", ProductGroupName);
cmd.Parameters.AddWithValue("@TransactionNo", TransactionNo);
cmd.Parameters.AddWithValue("@CustomerName", CustomerName);
cmd.Parameters.AddWithValue("@CashierName", CashierName);
cmd.Parameters.AddWithValue("@TerminalNo", TerminalNo);
cmd.Parameters.AddWithValue("@StartTransactionDate", StartTransactionDate.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@EndTransactionDate", EndTransactionDate.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.CommandText = SQL;
base.ExecuteNonQuery(cmd);
SQL = "SELECT " +
"spi.ProductGroup," +
"spi.ProductID," +
"spi.ProductCode," +
"MAX(cntct.ContactCode) SupplierCode," +
"SUM(spi.Quantity) 'Quantity'," +
"SUM(spi.Amount) 'Amount'," +
"SUM(spi.PurchaseAmount) 'PurchaseAmount', " +
"SUM(spi.Discount) 'Discount', " +
"MIN(spi.PurchasePrice) 'PurchasePrice', " +
"MAX(spi.InvQuantity) 'InvQuantity', " +
"IFNULL(MIN(ppph.PurchasePrice),0) 'PurchasePrice2', " +
"IFNULL(MAX(cntct2.ContactCode),'') SupplierCode2 " +
"FROM tblSalesPerItem spi " +
"INNER JOIN tblProducts prd ON spi.ProductID = prd.ProductID " +
"INNER JOIN tblContacts cntct ON prd.SupplierID = cntct.ContactID " +
"LEFT OUTER JOIN ( " +
" SELECT ProductID, SupplierID, MIN(PurchasePrice) PurchasePrice " +
" FROM tblProductPurchasePriceHistory ppph WHERE ppph.PurchasePrice <> 0 " +
" AND PurchaseDate >= DATE_ADD(NOW(), INTERVAL -6 MONTH) " +
" GROUP BY ProductID " +
") ppph ON prd.ProductID = ppph.ProductID " +
" AND ppph.SupplierID <> prd.SupplierID " +
" AND ppph.PurchasePrice <> spi.PurchasePrice " +
//"LEFT OUTER JOIN tblProductPurchasePriceHistory ppph ON prd.ProductID = ppph.ProductID " +
// "AND ppph.SupplierID <> prd.SupplierID " +
// "AND ppph.PurchasePrice <> 0 " +
// "AND ppph.PurchasePrice <> spi.PurchasePrice " +
// "AND PurchaseDate >= DATE_ADD(NOW(), INTERVAL -6 MONTH) " +
"LEFT OUTER JOIN tblContacts cntct2 ON ppph.SupplierID = cntct2.ContactID " +
"WHERE SessionID = @SessionID ";
switch (pvtSaleperItemFilterType)
{
case SaleperItemFilterType.ShowPositiveOnly:
SQL += "AND Amount > PurchaseAmount ";
break;
case SaleperItemFilterType.ShowNegativeOnly:
SQL += "AND Amount < PurchaseAmount ";
break;
}
SQL += "GROUP BY spi.ProductGroup, spi.ProductID, spi.ProductCode ORDER BY ProductCode, MIN(ppph.PurchasePrice);";
cmd.CommandText = SQL;
cmd.Parameters.Clear();
cmd.Parameters.Add(prmSessionID);
System.Data.DataTable dt = new System.Data.DataTable("SalesTransactionPerItem");
base.MySqlDataAdapterFill(cmd, dt);
SQL = "DELETE FROM tblSalesPerItem WHERE SessionID = @SessionID;";
cmd.CommandText = SQL;
cmd.Parameters.Clear();
cmd.Parameters.Add(prmSessionID);
base.ExecuteNonQuery(cmd);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
