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