private void SetDataSource(ReportDocument Report)
{
ReportDataset rptds = new ReportDataset();
//long lngProductGroupName = long.Parse(cboProductGroup.SelectedItem.Value);
//long lngSubGroupName = long.Parse(cboSubGroup.SelectedItem.Value);
string ProductGroupName = string.Empty;
if (cboProductGroup.SelectedItem.Value != Constants.ZERO_STRING) ProductGroupName = cboProductGroup.SelectedItem.Value;
string SubGroupName = string.Empty;
if (cboSubGroup.SelectedItem.Value != Constants.ZERO_STRING) SubGroupName = cboSubGroup.SelectedItem.Value;
string ReportType = cboReportType.SelectedItem.Text;
ProductColumns clsProductColumns = new ProductColumns();
#region clsProductColumns
clsProductColumns.ProductCode = true;
clsProductColumns.BarCode = true;
clsProductColumns.BarCode2 = true;
clsProductColumns.BarCode3 = true;
clsProductColumns.ProductDesc = true;
clsProductColumns.ProductSubGroupName = true;
clsProductColumns.BaseUnitName = true;
clsProductColumns.UnitName = true;
clsProductColumns.ProductGroupName = true;
clsProductColumns.DateCreated = true;
clsProductColumns.Price = true;
clsProductColumns.Quantity = true;
clsProductColumns.MinThreshold = true;
clsProductColumns.MaxThreshold = true;
clsProductColumns.PurchasePrice = true;
clsProductColumns.SupplierName = true;
clsProductColumns.QuantityIN = true;
clsProductColumns.QuantityOUT = true;
clsProductColumns.RIDMinThreshold = true;
clsProductColumns.RIDMaxThreshold = true;
clsProductColumns.RID = true;
//clsProductColumns.BranchActualQuantity = true;
//clsProductColumns.BranchQuantity = true;
//clsProductColumns.BranchQuantityIN = true;
//clsProductColumns.BranchQuantityOUT = true;
#endregion
ProductDetails clsSearchKey = new ProductDetails();
#region Search Key
clsSearchKey.BranchID = Convert.ToInt32(cboBranch.SelectedItem.Value);
clsSearchKey.SupplierID = Convert.ToInt32(cboContact.SelectedItem.Value);
clsSearchKey.ProductGroupID = Convert.ToInt64(cboProductGroup.SelectedItem.Value);
clsSearchKey.ProductSubGroupID = Convert.ToInt64(cboSubGroup.SelectedItem.Value);
clsSearchKey.ProductCode = txtProductCode.Text;
#endregion
Products clsProduct = new Products();
clsProduct.GetConnection();
ProductInventories clsProductInventories = new ProductInventories(clsProduct.Connection, clsProduct.Transaction);
DataTable dt;
string ProductIDs = null;
switch (cboReportType.SelectedValue)
{
case ReportTypes.ProductList:
#region Products List
dt = clsProductInventories.ListAsDataTable(Int32.Parse(cboBranch.SelectedItem.Value), SupplierID: long.Parse(cboContact.SelectedItem.Value), ProductGroupID: long.Parse(cboProductGroup.SelectedItem.Value), ProductSubGroupID: long.Parse(cboSubGroup.SelectedItem.Value), ProductCode: txtProductCode.Text);
clsProduct.CommitAndDispose();
foreach (System.Data.DataRow dr in dt.Rows)
{
DataRow drNew = rptds.Products.NewRow();
foreach (DataColumn dc in rptds.Products.Columns)
drNew[dc] = dr[dc.ColumnName];
rptds.Products.Rows.Add(drNew);
}
break;
#endregion
case ReportTypes.ProductPriceList:
#region Products Price List
dt = clsProductInventories.ListAsDataTable(int.Parse(cboBranch.SelectedItem.Value), SupplierID: long.Parse(cboContact.SelectedItem.Value), ProductGroupID: long.Parse(cboProductGroup.SelectedItem.Value), ProductSubGroupID: long.Parse(cboSubGroup.SelectedItem.Value), ProductCode: txtProductCode.Text);
clsProduct.CommitAndDispose();
foreach (DataRow dr in dt.Rows)
{
DataRow drNew = rptds.Products.NewRow();
foreach (DataColumn dc in rptds.Products.Columns)
drNew[dc] = dr[dc.ColumnName];
rptds.Products.Rows.Add(drNew);
ProductIDs += dr["ProductID"].ToString() + ",";
}
break;
#endregion
case ReportTypes.ProductListWithInvalidMatrix:
#region Products List With Invalid Unit Matrix
dt = clsProductInventories.ListAsDataTable(int.Parse(cboBranch.SelectedItem.Value), SupplierID: long.Parse(cboContact.SelectedItem.Value), ProductGroupID: long.Parse(cboProductGroup.SelectedItem.Value), ProductSubGroupID: long.Parse(cboSubGroup.SelectedItem.Value), ProductCode: txtProductCode.Text, ShowOnlyWithInvalidUnitMatrix: true);
clsProduct.CommitAndDispose();
foreach (System.Data.DataRow dr in dt.Rows)
{
DataRow drNew = rptds.Products.NewRow();
foreach (DataColumn dc in rptds.Products.Columns)
drNew[dc] = dr[dc.ColumnName];
rptds.Products.Rows.Add(drNew);
}
break;
#endregion
case ReportTypes.WeightedProductsForWeighingScale:
case ReportTypes.CountedProductsForWeighingScale:
#region Weighted and Counted Products For Weighing Scale
dt = clsProductInventories.ListAsDataTable(int.Parse(cboBranch.SelectedItem.Value), SupplierID: long.Parse(cboContact.SelectedItem.Value), ProductGroupID: long.Parse(cboProductGroup.SelectedItem.Value), ProductSubGroupID: long.Parse(cboSubGroup.SelectedItem.Value), ProductCode: txtProductCode.Text);
clsProduct.CommitAndDispose();
foreach (DataRow dr in dt.Rows)
{
if (dr[ProductColumnNames.BarCode].ToString() != null && dr[ProductColumnNames.BarCode].ToString() != string.Empty)
{
DataRow drNew = rptds.Products.NewRow();
foreach (DataColumn dc in rptds.Products.Columns)
drNew[dc] = dr[dc.ColumnName];
rptds.Products.Rows.Add(drNew);
ProductIDs += dr["ProductID"].ToString() + ",";
}
}
break;
#endregion
case ReportTypes.ProductsInDemoReport:
#region Products In Demo
DateTime StartTransactionDate = DateTime.TryParse(txtStartDate.Text + " " + txtStartTime.Text, out StartTransactionDate) ? StartTransactionDate : DateTime.MinValue;
DateTime EndTransactionDate = DateTime.TryParse(txtEndDate.Text + " " + txtEndTime.Text, out EndTransactionDate) ? EndTransactionDate : DateTime.MinValue;
SalesTransactionItems clsSalesTransactionItemsDemo = new SalesTransactionItems();
System.Data.DataTable dtDemo = clsSalesTransactionItemsDemo.ProductsInDemoReport(Int32.Parse(cboBranch.SelectedItem.Value), Int64.Parse(cboContact.SelectedItem.Value), cboProductGroup.SelectedItem.Text, cboSubGroup.SelectedItem.Text, txtProductCode.Text, "", StartTransactionDate, EndTransactionDate);
clsSalesTransactionItemsDemo.CommitAndDispose();
foreach (DataRow dr in dtDemo.Rows)
{
DataRow drNew = rptds.ProductsInDemo.NewRow();
foreach (DataColumn dc in rptds.ProductsInDemo.Columns)
drNew[dc] = dr[dc.ColumnName];
rptds.ProductsInDemo.Rows.Add(drNew);
}
break;
#endregion
default:
return;
}
Report.SetDataSource(rptds);
SetParameters(Report);
}
private void LoadProducts(System.Data.SqlClient.SortOrder SequenceSortOrder)
{
try
{
tblLayoutProducts.Controls.Clear();
if (mboLocked) return;
Int64 intSequenceNoStart = 0;
if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
try { intSequenceNoStart = long.Parse(cmdProductLeft.Tag.ToString()); }
catch { }
else
try { intSequenceNoStart = long.Parse(cmdProductRight.Tag.ToString()); }
catch { }
// Sep 24, 2014 put an override if cmdSubGroupLeft.Tag = 0
// always do an asceding coz its already the end.
if (intSequenceNoStart < Constants.C_RESTOPLUS_MAX_PRODUCTS) intSequenceNoStart = 0; //reset to 0 if it's 1
if (intSequenceNoStart == 0) SequenceSortOrder = System.Data.SqlClient.SortOrder.Ascending;
//if (tblLayoutProducts.Controls.Count <= Constants.C_RESTOPLUS_MAX_PRODUCTS) { intSequenceNoStart -= 1; SequenceSortOrder = System.Data.SqlClient.SortOrder.Ascending; }
ProductColumns clsProductColumns = new ProductColumns();
clsProductColumns.BarCode = true;
clsProductColumns.ProductCode = true;
clsProductColumns.SequenceNo = true;
ProductColumns clsSearchColumns = new ProductColumns();
long lngProductSubGroupID = 0;
try { lngProductSubGroupID = long.Parse(lblItems.Tag.ToString()); }
catch { }
Products clsProduct = new Products(mConnection, mTransaction);
// always put the branchid as zero so that it will show all products
System.Data.DataTable dtProduct = clsProduct.ListAsDataTable(clsProductColumns, 0, ProductListFilterType.ShowActiveOnly, intSequenceNoStart, SequenceSortOrder,
clsSearchColumns, string.Empty, Constants.ZERO, Constants.ZERO, string.Empty, lngProductSubGroupID, string.Empty, SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending ? Constants.C_RESTOPLUS_MAX_PRODUCTS : Constants.C_RESTOPLUS_MAX_PRODUCTS + 1, mclsTerminalDetails.ShowItemMoreThanZeroQty, true, "SequenceNo", SequenceSortOrder == System.Data.SqlClient.SortOrder.Ascending ? SortOption.Ascending : SortOption.Desscending);
//System.Data.DataTable dtProduct = clsProduct.ListAsDataTableFE(Constants.BRANCH_ID_MAIN, string.Empty, ProductListFilterType.ShowActiveOnly, Constants.C_RESTOPLUS_MAX_PRODUCTS + 1, false, "SequenceNo", System.Data.SqlClient.SortOrder.Ascending);
clsProduct.CommitAndDispose();
// re-order the products by sequcen no
if (dtProduct.Rows.Count > 0)
{
System.Data.DataView dv = dtProduct.DefaultView;
dv.Sort = "SequenceNo";
dtProduct = dv.ToTable();
}
int iRow = 0;
int iCol = 0;
int iCtr = 1;
if (dtProduct.Rows.Count == 0)
{
cmdProductLeft.Tag = "0".ToString(); // reset the sequenceno to 0 if no record
cmdProductRight.Tag = "0".ToString(); // reset the sequenceno to 0 if no record
}
foreach (System.Data.DataRow dr in dtProduct.Rows)
{
if (iCtr > Constants.C_RESTOPLUS_MAX_PRODUCTS) break;
if (iCtr == 1) cmdProductLeft.Tag = dr[Data.ProductColumnNames.SequenceNo].ToString();
if (iCtr >= 1 && dtProduct.Rows.Count > Constants.C_RESTOPLUS_MAX_PRODUCTS) cmdProductRight.Tag = dr[Data.ProductColumnNames.SequenceNo].ToString();
//if (iCtr == Constants.C_RESTOPLUS_MAX_PRODUCTS && dtProduct.Rows.Count > Constants.C_RESTOPLUS_MAX_PRODUCTS)
//{
// if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
// try { if (iCtr == 1) cmdProductLeft.Tag = dr[Data.ProductColumnNames.SequenceNo].ToString(); }
// catch { }
// else
// try { cmdProductRight.Tag = dr[Data.ProductColumnNames.SequenceNo].ToString(); }
// catch { }
//}
//else if (dtProduct.Rows.Count > 0 && dtProduct.Rows.Count <= Constants.C_RESTOPLUS_MAX_PRODUCTS)
//{
// if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
// {
// try {
// if (Int32.Parse(cmdProductRight.Tag.ToString()) < Int32.Parse(dr[Data.ProductColumnNames.SequenceNo].ToString()))
// cmdProductRight.Tag = dr[Data.ProductColumnNames.SequenceNo].ToString();
// }
// catch { }
// cmdProductLeft.Tag = "0".ToString();
// }
// else
// {
// try { if (iCtr == 1) cmdProductLeft.Tag = dr[Data.ProductColumnNames.SequenceNo].ToString(); }
// catch { }
// // cmdProductRight.Tag = cmdProductRight.Tag; // do not reset
// }
//}
ProductButton cmdProduct = new ProductButton();
cmdProduct.AutoSizeMode = System.Windows.Forms.AutoSizeMode.GrowAndShrink;
cmdProduct.BackColor = System.Drawing.Color.WhiteSmoke;
cmdProduct.Dock = System.Windows.Forms.DockStyle.Fill;
cmdProduct.FlatAppearance.BorderColor = System.Drawing.Color.Gold;
cmdProduct.Font = new System.Drawing.Font("Tahoma", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
cmdProduct.ForeColor = System.Drawing.SystemColors.ControlText;
cmdProduct.GradientBottom = System.Drawing.Color.DarkGray;
cmdProduct.GradientTop = System.Drawing.Color.WhiteSmoke;
cmdProduct.ImageAlign = System.Drawing.ContentAlignment.TopCenter;
cmdProduct.Location = new System.Drawing.Point(3, 3);
cmdProduct.Size = new System.Drawing.Size(115, 89);
cmdProduct.TabIndex = iCtr - 1;
cmdProduct.TextAlign = System.Drawing.ContentAlignment.BottomCenter;
cmdProduct.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText;
cmdProduct.UseVisualStyleBackColor = false;
cmdProduct.Name = "cmdProduct" + iCtr.ToString();
string strProductCode = dr[Data.ProductColumnNames.ProductCode].ToString();
if (strProductCode.Length > 12)
{
//string strTempProductCode = "";
string[] strCodes = strProductCode.Split(' ');
int ixCtr = 0;
foreach (string strCode in strCodes)
{
if (ixCtr == 0)
cmdProduct.Text = strCode;
else
cmdProduct.Text += "\r\n" + strCode;
ixCtr++;
//if ((strTempProductCode.Length + strCode.Length) < 12)
//{
// strTempProductCode += (strTempProductCode.Length == 0) ? strCode : " " + strCode;
//}
//else
//{
// cmdProduct.Text += (cmdProduct.Text.Length == 0) ? strTempProductCode : "\r\n" + strTempProductCode;
// strTempProductCode = strCode;
//}
//if ((strProductCode.LastIndexOf(strCode) + strCode.Length) == strProductCode.Length) //mean this is the last
// cmdProduct.Text += "\r\n" + strTempProductCode;
}
}
else {
cmdProduct.Text = strProductCode;
}
cmdProduct.Tag = dr[Data.ProductColumnNames.BarCode].ToString();
cmdProduct.Click += new System.EventHandler(cmdProduct_Click);
tblLayoutProducts.Controls.Add(cmdProduct, iCol, iRow);
iCol++; iCtr++;
}
}
catch { }
}
public System.Data.DataTable ListAsDataTable(ProductColumns clsProductColumns, int BranchID, ProductListFilterType clsProductListFilterType,
Int64 SequenceNoStart, System.Data.SqlClient.SortOrder SequenceSortOrder,
ProductColumns SearchColumns, string SearchKey, Int64 SupplierID, Int64 ProductGroupID, string ProductGroupName, Int64 ProductSubGroupID, string ProductSubGroupName, Int32 limit, bool isQuantityGreaterThanZERO, bool CheckIItemisSold, string SortField, SortOption SortOrder)
{
try
{
clsProductColumns.IncludeAllPackages = true;
// include branchid in the selection if branchid is not zero
if (BranchID != 0) clsProductColumns.BranchID = true;
string SQL = SQLSelect(clsProductColumns) + "WHERE prd.deleted=0 ";
if (SequenceNoStart != Constants.ZERO)
{
if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
SQL += "AND prd.SequenceNo < " + SequenceNoStart.ToString() + " ";
else
SQL += "AND prd.SequenceNo > " + SequenceNoStart.ToString() + " ";
}
if (BranchID != Constants.ZERO) SQL += "AND inv.BranchID = " + BranchID.ToString() + " ";
if (CheckIItemisSold) SQL += "AND prd.IsItemSold = 1 ";
if (clsProductListFilterType == ProductListFilterType.ShowActiveOnly) SQL += "AND prd.Active = 1 ";
if (clsProductListFilterType == ProductListFilterType.ShowInactiveOnly) SQL += "AND prd.Active = 0 ";
if (SearchKey != string.Empty)
{
string SQLSearch = string.Empty;
if (SearchColumns.BarCode)
{ if (SQLSearch == string.Empty) SQLSearch += "pkg.Barcode1 LIKE @SearchKey "; else SQLSearch += "OR pkg.Barcode1 LIKE @SearchKey "; }
if (SearchColumns.BarCode2)
{ if (SQLSearch == string.Empty) SQLSearch += "pkg.Barcode2 LIKE @SearchKey "; else SQLSearch += "OR pkg.Barcode2 LIKE @SearchKey "; }
if (SearchColumns.BarCode3)
{ if (SQLSearch == string.Empty) SQLSearch += "pkg.Barcode3 LIKE @SearchKey "; else SQLSearch += "OR pkg.Barcode3 LIKE @SearchKey "; }
if (SearchColumns.ProductCode)
{ if (SQLSearch == string.Empty) SQLSearch += "prd.ProductCode LIKE @SearchKey "; else SQLSearch += "OR prd.ProductCode LIKE @SearchKey "; }
if (SearchColumns.ProductDesc)
{ if (SQLSearch == string.Empty) SQLSearch += "prd.ProductDesc LIKE @SearchKey "; else SQLSearch += "OR prd.ProductDesc LIKE @SearchKey "; }
if (SQLSearch != string.Empty) SQL += "AND (" + SQLSearch + ") ";
}
if (SupplierID != Constants.ZERO)
SQL += "AND (prd.SupplierID = " + SupplierID + " OR prd.ProductID IN (SELECT DISTINCT(ProductID) FROM tblProductBaseVariationsMatrix WHERE SupplierID = " + SupplierID + ")) ";
if (ProductSubGroupID != Constants.ZERO)
{ SQL += "AND prd.ProductSubGroupID = " + ProductSubGroupID + " "; }
if (!string.IsNullOrEmpty(ProductSubGroupName))
{ SQL += "AND sgrp.ProductSubGroupName = '" + ProductSubGroupName + "' "; }
if (ProductGroupID != Constants.ZERO)
{ SQL += "AND sgrp.ProductGroupID = " + ProductGroupID + " "; }
if (!string.IsNullOrEmpty(ProductGroupName))
{ SQL += "AND grp.ProductGroupName = '" + ProductGroupName + "' "; }
if (isQuantityGreaterThanZERO)
{ SQL += "AND inv.Quantity > 0 "; }
if (!string.IsNullOrEmpty(SortField))
{
SQL += "ORDER BY " + SortField + " ";
if (SortOrder == SortOption.Ascending)
SQL += "ASC ";
else
SQL += "DESC ";
}
if (limit != 0)
SQL += "LIMIT " + limit + " ";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmSearchKey = new MySqlParameter("@SearchKey", MySqlDbType.String);
prmSearchKey.Value = SearchKey + "%";
cmd.Parameters.Add(prmSearchKey);
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
//public System.Data.DataTable ListAsDataTable(ProductColumns clsProductColumns, int BranchID, ProductListFilterType clsProductListFilterType,
// long SequenceNoStart, System.Data.SqlClient.SortOrder SequenceSortOrder,
// ProductColumns SearchColumns, string SearchKey, long SupplierID, long ProductGroupID, string ProductGroupName, long ProductSubGroupID, string ProductSubGroupName, Int32 limit, bool isQuantityGreaterThanZERO, bool CheckIItemisSold, string SortField, SortOption SortOrder, string GroupBy)
//{
// try
// {
// clsProductColumns.IncludeAllPackages = true;
// // include branchid in the selection if branchid is not zero
// if (BranchID != 0) clsProductColumns.BranchID = true;
// string SQL = SQLSelect(clsProductColumns) + "WHERE prd.deleted=0 ";
// if (SequenceNoStart != Constants.ZERO)
// {
// if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
// SQL += "AND prd.SequenceNo < " + SequenceNoStart.ToString() + " ";
// else
// SQL += "AND prd.SequenceNo > " + SequenceNoStart.ToString() + " ";
// }
// if (BranchID != Constants.ZERO) SQL += "AND inv.BranchID = " + BranchID.ToString() + " ";
// if (CheckIItemisSold) SQL += "AND prd.IsItemSold = 1 ";
// if (clsProductListFilterType == ProductListFilterType.ShowActiveOnly) SQL += "AND prd.Active = 1 ";
// if (clsProductListFilterType == ProductListFilterType.ShowInactiveOnly) SQL += "AND prd.Active = 0 ";
// if (SearchKey != string.Empty)
// {
// string SQLSearch = string.Empty;
// if (SearchColumns.BarCode)
// { if (SQLSearch == string.Empty) SQLSearch += "prd.Barcode LIKE @SearchKey "; else SQLSearch += "OR prd.Barcode LIKE @SearchKey "; }
// if (SearchColumns.BarCode2)
// { if (SQLSearch == string.Empty) SQLSearch += "prd.Barcode2 LIKE @SearchKey "; else SQLSearch += "OR prd.Barcode2 LIKE @SearchKey "; }
// if (SearchColumns.BarCode3)
// { if (SQLSearch == string.Empty) SQLSearch += "prd.Barcode3 LIKE @SearchKey "; else SQLSearch += "OR prd.Barcode3 LIKE @SearchKey "; }
// if (SearchColumns.ProductCode)
// { if (SQLSearch == string.Empty) SQLSearch += "prd.ProductCode LIKE @SearchKey "; else SQLSearch += "OR prd.ProductCode LIKE @SearchKey "; }
// if (SearchColumns.ProductDesc)
// { if (SQLSearch == string.Empty) SQLSearch += "prd.ProductDesc LIKE @SearchKey "; else SQLSearch += "OR prd.ProductDesc LIKE @SearchKey "; }
// if (SQLSearch != string.Empty) SQL += "AND (" + SQLSearch + ") ";
// }
// if (SupplierID != Constants.ZERO)
// SQL += "AND (prd.SupplierID = " + SupplierID + " OR ProductID IN (SELECT DISTINCT(ProductID) FROM tblProductBaseVariationsMatrix WHERE SupplierID = " + SupplierID + ")) ";
// if (ProductSubGroupID != Constants.ZERO)
// { SQL += "AND prd.ProductSubGroupID = " + ProductSubGroupID + " "; }
// if (ProductSubGroupName != string.Empty && ProductSubGroupName != null)
// { SQL += "AND tblProductSubGroup.ProductSubGroupName = '" + ProductSubGroupName + "' "; }
// if (ProductGroupID != Constants.ZERO)
// { SQL += "AND tblProductSubGroup.ProductGroupID = " + ProductGroupID + " "; }
// if (ProductGroupName != string.Empty && ProductGroupName != null)
// { SQL += "AND tblProductGroup.ProductGroupName = '" + ProductGroupName + "' "; }
// if (isQuantityGreaterThanZERO)
// { SQL += "AND inv.Quantity > 0 "; }
// if (GroupBy != string.Empty || GroupBy != null)
// SQL += "GROUP BY " + GroupBy + " ";
// if (SortField == string.Empty) SortField = "ProductCode";
// SQL += "ORDER BY " + SortField + " ";
// if (SortOrder == SortOption.Ascending)
// SQL += "ASC ";
// else
// SQL += "DESC ";
// if (limit != 0)
// SQL += "LIMIT " + limit + " ";
// MySqlCommand cmd = new MySqlCommand();
// cmd.CommandType = System.Data.CommandType.Text;
// cmd.CommandText = SQL;
// MySqlParameter prmSearchKey = new MySqlParameter("@SearchKey",MySqlDbType.String);
// prmSearchKey.Value = SearchKey + "%";
// cmd.Parameters.Add(prmSearchKey);
// string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
// base.MySqlDataAdapterFill(cmd, dt);
// return dt;
// }
// catch (Exception ex)
// {
// {
// }
// throw base.ThrowException(ex);
// }
//}
public System.Data.DataTable ListAsDataTable(ProductColumns clsProductColumns, ProductDetails clsSearchKey,
int BranchID = 0, ProductListFilterType clsProductListFilterType = ProductListFilterType.ShowActiveOnly,
Int64 SequenceNoStart = 0, System.Data.SqlClient.SortOrder SequenceSortOrder = System.Data.SqlClient.SortOrder.Ascending,
Int64 SupplierID = 0, Int64 ProductGroupID = 0, string ProductGroupName = "",
Int64 ProductSubGroupID = 0, string ProductSubGroupName = "",
bool CheckisQuantityGreaterThanZERO = false, bool isQuantityGreaterThanZERO = false,
bool CheckItemisSold = false, bool ItemisSold = true,
string SortField = "", SortOption SortOrder = SortOption.Ascending, Int32 limit = 0)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect(clsProductColumns) + "WHERE prd.deleted=0 ";
if (SequenceNoStart != Constants.ZERO)
{
if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
SQL += "AND prd.SequenceNo < " + SequenceNoStart.ToString() + " ";
else
SQL += "AND prd.SequenceNo > " + SequenceNoStart.ToString() + " ";
}
if (BranchID != Constants.ZERO) SQL += "AND tblProductInventory.BranchID = " + BranchID.ToString() + " ";
if (CheckItemisSold) SQL += "AND prd.IsItemSold = 1 ";
if (clsProductListFilterType == ProductListFilterType.ShowActiveOnly) SQL += "AND prd.Active = 1 ";
if (clsProductListFilterType == ProductListFilterType.ShowInactiveOnly) SQL += "AND prd.Active = 0 ";
if (SupplierID != Constants.ZERO)
SQL += "AND (prd.SupplierID = " + SupplierID + " OR ProductID IN (SELECT DISTINCT(ProductID) FROM tblProductBaseVariationsMatrix WHERE SupplierID = " + SupplierID + ")) ";
if (ProductSubGroupID != Constants.ZERO)
{ SQL += "AND prd.ProductSubGroupID = " + ProductSubGroupID + " "; }
if (ProductSubGroupName != string.Empty && ProductSubGroupName != null)
{ SQL += "AND sgrp.ProductSubGroupName = '" + ProductSubGroupName + "' "; }
if (ProductGroupID != Constants.ZERO)
{ SQL += "AND sgrp.ProductGroupID = " + ProductGroupID + " "; }
if (ProductGroupName != string.Empty && ProductGroupName != null)
{ SQL += "AND grp.ProductGroupName = '" + ProductGroupName + "' "; }
if (isQuantityGreaterThanZERO)
{ SQL += "AND tblProductInventory.Quantity > 0 "; }
//if (GroupBy != string.Empty || GroupBy != null)
// SQL += "GROUP BY " + GroupBy + " ";
if (SortField == string.Empty) SortField = "ProductCode";
SQL += "ORDER BY " + SortField + " ";
if (SortOrder == SortOption.Ascending)
SQL += "ASC ";
else
SQL += "DESC ";
if (limit != 0)
SQL += "LIMIT " + limit + " ";
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
private string SQLSelect(ProductColumns clsColumns)
{
clsColumns.ProductID = true; // this must always be selected
#region Default Columns to Select
if (clsColumns.ColumnsAll)
{
object boxed = clsColumns; // needs to unboxed so that the value will be assigned, otherwise it won't work
System.Reflection.FieldInfo[] fi = clsColumns.GetType().GetFields();
foreach (System.Reflection.FieldInfo info1 in fi)
{
info1.SetValue(boxed, true);
}
clsColumns = (ProductColumns)boxed; // boxed so that the value will be assigned, otherwise it won't work
}
else if (clsColumns.ColumnsNameID)
{
clsColumns.ProductDesc = true;
}
else if (clsColumns.ColumnsCodeNameID)
{
clsColumns.ProductCode = true;
clsColumns.ProductDesc = true;
}
#endregion
string stSQL = "SELECT ";
if (clsColumns.BarCode) stSQL += "pkg." + ProductColumnNames.PackageID + ", ";
if (clsColumns.BarCode) stSQL += "IFNULL(pkg.BarCode1,pkg.BarCode4) BarCode, ";
if (clsColumns.BarCode) stSQL += "pkg." + ProductColumnNames.BarCode1 + ", ";
if (clsColumns.BarCode) stSQL += "pkg." + ProductColumnNames.BarCode2 + ", ";
if (clsColumns.BarCode) stSQL += "pkg." + ProductColumnNames.BarCode3 + ", ";
if (clsColumns.ProductCode) stSQL += "prd." + ProductColumnNames.ProductCode + ", ";
if (clsColumns.ProductDesc) stSQL += "prd." + ProductColumnNames.ProductDesc + ", ";
if (clsColumns.OrderSlipPrinter) stSQL += "prd.OrderSlipPrinter1 ,prd.OrderSlipPrinter2 ,prd.OrderSlipPrinter3 ,prd.OrderSlipPrinter4 ,prd.OrderSlipPrinter5, ";
if (clsColumns.ProductSubGroupID) stSQL += "prd." + ProductColumnNames.ProductSubGroupID + ", ";
if (clsColumns.ProductSubGroupCode) stSQL += "sgrp." + ProductColumnNames.ProductSubGroupCode + ", ";
if (clsColumns.ProductSubGroupName) stSQL += "sgrp." + ProductColumnNames.ProductSubGroupName + ", ";
if (clsColumns.ProductGroupID) stSQL += "sgrp." + ProductColumnNames.ProductGroupID + ", ";
if (clsColumns.ProductGroupCode) stSQL += "grp." + ProductColumnNames.ProductGroupCode + ", ";
if (clsColumns.ProductGroupName) stSQL += "grp." + ProductColumnNames.ProductGroupName + ", ";
if (clsColumns.UnitID) stSQL += "prd." + ProductColumnNames.BaseUnitID + " 'BaseUnitID', ";
if (clsColumns.UnitCode) stSQL += "unt." + ProductColumnNames.UnitCode + " 'BaseUnitCode', ";
if (clsColumns.UnitName) stSQL += "unt." + ProductColumnNames.UnitName + " 'BaseUnitName', ";
if (clsColumns.UnitID) stSQL += "prd." + ProductColumnNames.UnitID + ", ";
if (clsColumns.UnitCode) stSQL += "unt." + ProductColumnNames.UnitCode + ", ";
if (clsColumns.UnitName) stSQL += "unt." + ProductColumnNames.UnitName + ", ";
if (clsColumns.DateCreated) stSQL += "prd." + ProductColumnNames.DateCreated + ", ";
if (clsColumns.Deleted) stSQL += "prd." + ProductColumnNames.Deleted + ", ";
if (clsColumns.Active) stSQL += "prd." + ProductColumnNames.Active + ", ";
if (clsColumns.SupplierID) stSQL += "prd." + ProductColumnNames.SupplierID + ", ";
if (clsColumns.SupplierCode) stSQL += "cntct.ContactCode AS SupplierCode, ";
if (clsColumns.SupplierName) stSQL += "cntct.ContactName AS SupplierName, ";
if (clsColumns.Price) stSQL += "pkg." + ProductColumnNames.Price + ", ";
if (clsColumns.Price) stSQL += "pkg." + ProductColumnNames.Price1 + ", ";
if (clsColumns.Price) stSQL += "pkg." + ProductColumnNames.Price2 + ", ";
if (clsColumns.Price) stSQL += "pkg." + ProductColumnNames.Price3 + ", ";
if (clsColumns.Price) stSQL += "pkg." + ProductColumnNames.Price4 + ", ";
if (clsColumns.Price) stSQL += "pkg." + ProductColumnNames.Price5 + ", ";
if (clsColumns.WSPrice) stSQL += "pkg." + ProductColumnNames.WSPrice + ", ";
if (clsColumns.PurchasePrice) stSQL += "pkg." + ProductColumnNames.PurchasePrice + ", ";
if (clsColumns.PercentageCommision) stSQL += "prd." + ProductColumnNames.PercentageCommision + ", ";
if (clsColumns.IncludeInSubtotalDiscount) stSQL += "prd." + ProductColumnNames.IncludeInSubtotalDiscount + ", ";
if (clsColumns.IsCreditChargeExcluded) stSQL += "prd." + ProductColumnNames.IsCreditChargeExcluded + ", ";
if (clsColumns.VAT) stSQL += "pkg." + ProductColumnNames.VAT + ", ";
if (clsColumns.EVAT) stSQL += "pkg." + ProductColumnNames.EVAT + ", ";
if (clsColumns.LocalTax) stSQL += "pkg." + ProductColumnNames.LocalTax + ", ";
if (clsColumns.Quantity) stSQL += "inv." + ProductColumnNames.Quantity + " AS Quantity, ";
if (clsColumns.Quantity) stSQL += "fnProductQuantityConvert(prd.ProductID, inv." + ProductColumnNames.Quantity + ", prd.BaseUnitID) AS ConvertedQuantity, ";
if (clsColumns.MinThreshold) stSQL += "prd." + ProductColumnNames.MinThreshold + ", ";
if (clsColumns.MaxThreshold) stSQL += "prd." + ProductColumnNames.MaxThreshold + ", ";
if (clsColumns.RID) stSQL += "prd." + ProductColumnNames.RID + ", ";
if (clsColumns.ChartOfAccountIDPurchase) stSQL += "prd." + ProductColumnNames.ChartOfAccountIDPurchase + ", ";
if (clsColumns.ChartOfAccountIDSold) stSQL += "prd." + ProductColumnNames.ChartOfAccountIDSold + ", ";
if (clsColumns.ChartOfAccountIDInventory) stSQL += "prd." + ProductColumnNames.ChartOfAccountIDInventory + ", ";
if (clsColumns.ChartOfAccountIDTaxPurchase) stSQL += "prd." + ProductColumnNames.ChartOfAccountIDTaxPurchase + ", ";
if (clsColumns.ChartOfAccountIDTaxSold) stSQL += "prd." + ProductColumnNames.ChartOfAccountIDTaxSold + ", ";
if (clsColumns.IsItemSold) stSQL += "prd." + ProductColumnNames.IsItemSold + ", ";
if (clsColumns.WillPrintProductComposition) stSQL += "prd." + ProductColumnNames.WillPrintProductComposition + ", ";
if (clsColumns.VariationCount) stSQL += "prd." + ProductColumnNames.VariationCount + ", ";
if (clsColumns.QuantityIN) stSQL += "inv." + ProductColumnNames.QuantityIN + " AS QuantityIN, ";
if (clsColumns.QuantityOUT) stSQL += "inv." + ProductColumnNames.QuantityOUT + " AS QuantityOUT, ";
if (clsColumns.ActualQuantity) stSQL += "inv." + ProductColumnNames.ActualQuantity + " AS ActualQuantity, ";
if (clsColumns.ActualQuantity) stSQL += "fnProductQuantityConvert(prd.ProductID, inv." + ProductColumnNames.ActualQuantity + ", prd.BaseUnitID) AS ConvertedActualQuantity, ";
if (clsColumns.ReorderQty) stSQL += "prd." + ProductColumnNames.MaxThreshold + " - inv." + ProductColumnNames.Quantity + " AS ReorderQty, ";
if (clsColumns.RIDMinThreshold) stSQL += "prd." + ProductColumnNames.RIDMinThreshold + ", ";
if (clsColumns.RIDMaxThreshold) stSQL += "prd." + ProductColumnNames.RIDMaxThreshold + ", ";
if (clsColumns.RIDReorderQty) stSQL += "prd.RIDMaxThreshold - inv.Quantity AS RIDReorderQty, ";
if (clsColumns.BranchID) stSQL += "inv." + ProductColumnNames.BranchID + ", ";
if (clsColumns.RewardPoints) stSQL += "prd." + ProductColumnNames.RewardPoints + ", ";
if (clsColumns.SequenceNo) stSQL += "prd." + ProductColumnNames.SequenceNo + ", ";
stSQL += "prd.ProductID ";
stSQL += "FROM tblProducts prd ";
stSQL += "INNER JOIN tblProductSubGroup sgrp ON prd.ProductSubGroupID = sgrp.ProductSubGroupID ";
stSQL += "INNER JOIN tblProductGroup grp ON sgrp.ProductGroupID = grp.ProductGroupID ";
stSQL += "INNER JOIN tblUnit unt ON prd.BaseUnitID = unt.UnitID ";
stSQL += "INNER JOIN tblContacts cntct ON prd.SupplierID = cntct.ContactID ";
stSQL += "INNER JOIN tblProductPackage pkg ON prd.ProductID = pkg.ProductID ";
stSQL += "INNER JOIN tblUnit untpkg ON pkg.UnitID = untpkg.UnitID ";
stSQL += "LEFT OUTER JOIN tblProductInventory inv ON prd.ProductID = inv.ProductID ";
return stSQL;
}