public System.Data.DataTable ListAsDataTable(int BranchID = 0, long ProductID = 0, long MatrixID =0, string BarCode = "", string ProductCode = "", Int64 ProductGroupID = 0, Int64 ProductSubGroupID = 0, Int64 SupplierID = 0, ProductListFilterType clsProductListFilterType = ProductListFilterType.ShowActiveAndInactive,
bool isQuantityGreaterThanZERO = false, Int32 Limit = 0, Int32 isSummary = 1, string ExpirationDate = Constants.C_DATE_MIN_VALUE_STRING, Int32 ForReorder = 0, Int32 OverStock = 0, string SortField = "", SortOption SortOrder = SortOption.Ascending, bool ShowOnlyWithInvalidUnitMatrix = false)
{
try
{
DateTime dteExpiration = Constants.C_DATE_MIN_VALUE;
DateTime.TryParse(ExpirationDate, out dteExpiration);
string SQL = "CALL procProductInventorySelect(@BranchID, @ProductID, @MatrixID, @BarCode, @ProductCode, @ProductGroupID, @ProductSubGroupID, @SupplierID, @ShowActiveAndInactive, @isQuantityGreaterThanZERO, @lngLimit, @isSummary, @dteExpiration, @ForReorder, @OverStock, @SortField, @SortOrder)";
// do an override if Only those with invalid unit matrix
if (ShowOnlyWithInvalidUnitMatrix)
SQL = "CALL procProductInventorySelectWithInvalidUnitMatrix(@BranchID, @ProductID, @MatrixID, @BarCode, @ProductCode, @ProductGroupID, @ProductSubGroupID, @SupplierID, @ShowActiveAndInactive, @isQuantityGreaterThanZERO, @lngLimit, @isSummary, @dteExpiration, @ForReorder, @OverStock, @SortField, @SortOrder)";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@BranchID", BranchID);
cmd.Parameters.AddWithValue("@ProductID", ProductID);
cmd.Parameters.AddWithValue("@MatrixID", MatrixID);
cmd.Parameters.AddWithValue("@BarCode", BarCode);
cmd.Parameters.AddWithValue("@ProductCode", ProductCode);
cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID);
cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID);
cmd.Parameters.AddWithValue("@SupplierID", SupplierID);
cmd.Parameters.AddWithValue("@ShowActiveAndInactive", clsProductListFilterType.ToString("d"));
cmd.Parameters.AddWithValue("@isQuantityGreaterThanZERO", isQuantityGreaterThanZERO);
cmd.Parameters.AddWithValue("@lngLimit", Limit);
cmd.Parameters.AddWithValue("@isSummary", isSummary);
cmd.Parameters.AddWithValue("@dteExpiration", dteExpiration.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@ForReorder", ForReorder);
cmd.Parameters.AddWithValue("@OverStock", OverStock);
cmd.Parameters.AddWithValue("@SortField", SortField);
switch (SortOrder)
{
case SortOption.Ascending:
cmd.Parameters.AddWithValue("@SortOrder", "ASC");
break;
case SortOption.Desscending:
cmd.Parameters.AddWithValue("@SortOrder", "DESC");
break;
}
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)
{
throw;
}
}
public System.Data.DataTable ListAsDataTable(int Month, int Year, int BranchID = 0, long ProductID = 0, long MatrixID = 0, string BarCode = "", string ProductCode = "", Int64 ProductGroupID = 0, Int64 ProductSubGroupID = 0, Int64 SupplierID = 0, ProductListFilterType clsProductListFilterType = ProductListFilterType.ShowActiveAndInactive,
bool isQuantityGreaterThanZERO = false, Int32 Limit = 0, Int32 isSummary = 1, string ExpirationDate = Constants.C_DATE_MIN_VALUE_STRING, Int32 ForReorder = 0, Int32 OverStock = 0, string SortField = "", SortOption SortOrder = SortOption.Ascending)
{
DateTime dteExpiration = Constants.C_DATE_MIN_VALUE;
DateTime.TryParse(ExpirationDate, out dteExpiration);
string SQL = "CALL procProductInventoryMonthlySelect(@DateMonth, @BranchID, @ProductID, @MatrixID, @BarCode, @ProductCode, @ProductGroupID, @ProductSubGroupID, @SupplierID, @ShowActiveAndInactive, @isQuantityGreaterThanZERO, @lngLimit, @isSummary, @dteExpiration, @ForReorder, @OverStock, @SortField, @SortOrder)";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@DateMonth", Year.ToString() + "-" + Month.ToString("0#"));
cmd.Parameters.AddWithValue("@BranchID", BranchID);
cmd.Parameters.AddWithValue("@ProductID", ProductID);
cmd.Parameters.AddWithValue("@MatrixID", MatrixID);
cmd.Parameters.AddWithValue("@BarCode", BarCode);
cmd.Parameters.AddWithValue("@ProductCode", ProductCode);
cmd.Parameters.AddWithValue("@ProductGroupID", ProductGroupID);
cmd.Parameters.AddWithValue("@ProductSubGroupID", ProductSubGroupID);
cmd.Parameters.AddWithValue("@SupplierID", SupplierID);
cmd.Parameters.AddWithValue("@ShowActiveAndInactive", clsProductListFilterType.ToString("d"));
cmd.Parameters.AddWithValue("@isQuantityGreaterThanZERO", isQuantityGreaterThanZERO);
cmd.Parameters.AddWithValue("@lngLimit", Limit);
cmd.Parameters.AddWithValue("@isSummary", isSummary);
cmd.Parameters.AddWithValue("@dteExpiration", dteExpiration.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@ForReorder", ForReorder);
cmd.Parameters.AddWithValue("@OverStock", OverStock);
cmd.Parameters.AddWithValue("@SortField", SortField);
switch (SortOrder)
{
case SortOption.Ascending:
cmd.Parameters.AddWithValue("@SortOrder", "ASC");
break;
case SortOption.Desscending:
cmd.Parameters.AddWithValue("@SortOrder", "DESC");
break;
}
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);
}
private void LoadList()
{
string SortField = "ProductDesc";
if (Request.QueryString["sortfield"] != null)
{
SortField = Common.Decrypt(Request.QueryString["sortfield"].ToString(), Session.SessionID);
}
SortOption sortoption = SortOption.Ascending;
if (Request.QueryString["sortoption"] != null)
{
sortoption = (SortOption)Enum.Parse(typeof(SortOption), Common.Decrypt(Request.QueryString["sortoption"], Session.SessionID), true);
}
ProductListFilterType clsProductListFilterType = ProductListFilterType.ShowActiveOnly;
if (rdoShowInactiveOnly.Checked == true)
{
clsProductListFilterType = ProductListFilterType.ShowInactiveOnly;
}
if (rdoShowActiveOnly.Checked == true)
{
clsProductListFilterType = ProductListFilterType.ShowActiveOnly;
}
if (rdoShowAll.Checked == true)
{
clsProductListFilterType = ProductListFilterType.ShowActiveAndInactive;
}
int intLimit = 0;
try { intLimit = int.Parse(txtLimit.Text); }
catch { }
string SearchKey = string.Empty;
if (Request.QueryString["Search"] != null)
{
SearchKey = Server.UrlDecode(Common.Decrypt((string)Request.QueryString["search"], Session.SessionID));
}
else if (Session["Search"] != null)
{
SearchKey = Server.UrlDecode(Common.Decrypt(Session["Search"].ToString(), Session.SessionID));
}
try { Session.Remove("Search"); } catch { }
if (SearchKey == null)
{
SearchKey = string.Empty;
}
else if (SearchKey != string.Empty)
{
Session.Add("Search", Common.Encrypt(SearchKey, Session.SessionID));
}
Data.ProductDetails clsSearchKeys = new Data.ProductDetails();
clsSearchKeys.BarCode = SearchKey;
clsSearchKeys.BarCode2 = SearchKey;
clsSearchKeys.BarCode3 = SearchKey;
clsSearchKeys.ProductCode = SearchKey;
ProductAddOnFilters clsProductAddOnFilters = new ProductAddOnFilters();
clsProductAddOnFilters.BarcodeFrom = txtBarcodeFrom.Text;
clsProductAddOnFilters.BarcodeTo = txtBarcodeTo.Text;
clsProductAddOnFilters.ProductCodeFrom = txtProductCodeFrom.Text;
clsProductAddOnFilters.ProductCodeTo = txtProductCodeTo.Text;
clsProductAddOnFilters.ProductSubGroupNameFrom = txtProductSubGroupNameFrom.Text;
clsProductAddOnFilters.ProductSubGroupNameTo = txtProductSubGroupNameTo.Text;
clsProductAddOnFilters.ProductGroupNameFrom = txtProductGroupnameFrom.Text;
clsProductAddOnFilters.ProductGroupNameTo = txtProductGroupNameTo.Text;
clsProductAddOnFilters.SupplierNameFrom = txtSupplierNameFrom.Text;
clsProductAddOnFilters.SupplierNameTo = txtSupplierNameTo.Text;
Products clsProduct = new Products();
System.Data.DataTable dt = clsProduct.ListAsDataTable(clsSearchKeys: clsSearchKeys, ProductAddOnFilters: clsProductAddOnFilters, clsProductListFilterType: clsProductListFilterType, limit: 100, SortField: SortField, SortOrder: SortOption.Ascending);
clsProduct.CommitAndDispose();
PageData.DataSource = dt.DefaultView;
int iPageSize = Convert.ToInt16(Session["PageSize"]);
PageData.AllowPaging = true;
PageData.PageSize = iPageSize;
try
{
PageData.CurrentPageIndex = Convert.ToInt16(cboCurrentPage.SelectedItem.Value) - 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
catch
{
PageData.CurrentPageIndex = 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
cboCurrentPage.Items.Clear();
for (int i = 0; i < PageData.PageCount; i++)
{
int iValue = i + 1;
cboCurrentPage.Items.Add(new ListItem(iValue.ToString(), iValue.ToString()));
if (PageData.CurrentPageIndex == i)
{
cboCurrentPage.Items[i].Selected = true;
}
else
{
cboCurrentPage.Items[i].Selected = false;
}
}
lblDataCount.Text = " of " + " " + PageData.PageCount;
}
private System.Data.DataTable ListAsDataTable(Int64 ProductID=0, int BranchID =0, string BarCode = "", string ProductCode = "", string MatrixDescription="", Int64 SupplierID = 0, ProductListFilterType clsProductListFilterType = ProductListFilterType.ShowActiveAndInactive,
bool isQuantityGreaterThanZERO = false, Int32 Limit = 0, string SortField = null, SortOption SortOrder = SortOption.Ascending)
{
try
{
string SQL = "CALL procProductVaritionMatrixSelect(@BranchID, @ProductID, @BarCode, @ProductCode, @MatrixDescription, @SupplierID, @ShowActiveAndInactive, @isQuantityGreaterThanZERO, @lngLimit, @SortField, @SortOrder)";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@BranchID", BranchID);
cmd.Parameters.AddWithValue("@ProductID", ProductID);
cmd.Parameters.AddWithValue("@BarCode", BarCode);
cmd.Parameters.AddWithValue("@ProductCode", ProductCode);
cmd.Parameters.AddWithValue("@MatrixDescription", MatrixDescription);
cmd.Parameters.AddWithValue("@SupplierID", SupplierID);
cmd.Parameters.AddWithValue("@ShowActiveAndInactive", clsProductListFilterType.ToString("d"));
cmd.Parameters.AddWithValue("@isQuantityGreaterThanZERO", isQuantityGreaterThanZERO);
cmd.Parameters.AddWithValue("@lngLimit", Limit);
cmd.Parameters.AddWithValue("@SortField", SortField);
switch (SortOrder)
{
case SortOption.Ascending:
cmd.Parameters.AddWithValue("@SortOrder", "ASC");
break;
case SortOption.Desscending:
cmd.Parameters.AddWithValue("@SortOrder", "DESC");
break;
}
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);
}
}
public System.Data.DataTable ListAsDataTableFE(int BranchID, string BarCode, ItemSelectWndColumnSearchType clsItemSelectWndColumnSearchType, ProductListFilterType clsProductListFilterType, Int32 limit, bool isQuantityGreaterThanZERO, string SortField = "ProductCode", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelectSimple(BranchID);
SQL = "SELECT " +
"prd.PackageID, " +
"prd.ProductID, " +
"prd.MatrixID, " +
"prd.ProductCode, " +
"prd.ProductDesc, " +
"prd.ProductSubGroupID, " +
"prd.ProductSubGroupCode, " +
"prd.BaseUnitID, " +
"prd.DateCreated, " +
"prd.PercentageCommision, " +
"prd.IncludeInSubtotalDiscount, " +
"prd.IsCreditChargeExcluded, " +
"prd.SupplierID, " +
"prd.RewardPoints, " +
"prd.MinThreshold, " +
"prd.MaxThreshold, " +
"prd.BarCode1, " +
"prd.BarCode2, " +
"prd.BarCode3, " +
"IFNULL(prd.BarCode1,prd.BarCode4) BarCode, " +
"prd.Price, " +
"prd.Price1, prd.Price2, prd.Price3, prd.Price4, prd.Price5, " +
"prd.WSPrice, " +
"prd.PurchasePrice, " +
"prd.VAT, " +
"prd.EVAT, " +
"prd.LocalTax, " +
"SUM(IFNULL(inv.Quantity,0)) - SUM(IFNULL(inv.ReservedQuantity,0)) Quantity, " +
"MAX(IFNULL(inv.IsLock,0)) IsLock, " +
//"fnProductQuantityConvert(inv.ProductID, inv.Quantity, prd.BaseUnitID) AS ConvertedQuantity, " +
"SUM(IFNULL(inv.ActualQuantity,0)) ActualQuantity, " +
"IFNULL(mtrx.Description,'') MatrixDescription ";
SQL += " FROM (SELECT prd.ProductID, prd.ProductCode, prd.ProductDesc, prd.ProductSubGroupID, sgrp.ProductSubGroupCode, prd.BaseUnitID, prd.DateCreated, prd.PercentageCommision, prd.IncludeInSubtotalDiscount, prd.IsCreditChargeExcluded " +
" ,prd.SupplierID, prd.RewardPoints, prd.MinThreshold, prd.MaxThreshold " +
" ,pkg.PackageID, pkg.MatrixID ,pkg.BarCode1, pkg.BarCode2, pkg.BarCode3, pkg.BarCode4, pkg.Price, pkg.Price1, pkg.Price2, pkg.Price3, pkg.Price4, pkg.Price5, pkg.WSPrice, pkg.PurchasePrice, pkg.VAT, pkg.EVAT, pkg.LocalTax " +
" FROM tblProducts prd INNER JOIN tblProductPackage pkg ON prd.productID = pkg.ProductID AND prd.BaseUnitID = pkg.UnitID AND pkg.Quantity = 1 " +
" INNER JOIN tblProductSubGroup sgrp ON prd.ProductSubGroupID = sgrp.ProductSubGroupID " +
" LEFT OUTER JOIN tblProductBaseVariationsMatrix mtrx ON mtrx.ProductID = prd.ProductID AND pkg.MatrixID = mtrx.MatrixID " +
" LEFT OUTER JOIN tblProductInventory inv ON inv.ProductID = prd.ProductID AND inv.MatrixID = IFNULL(mtrx.MatrixID,0) AND inv.BranchID=" + BranchID.ToString() + " " +
" WHERE prd.Active = 1 AND prd.deleted = 0 AND IFNULL(mtrx.deleted, prd.deleted) = 0 ";
if (!string.IsNullOrEmpty(BarCode))
{
switch (clsItemSelectWndColumnSearchType)
{
case ItemSelectWndColumnSearchType.BcPc:
SQL += " AND (Barcode1 LIKE @BarCode OR Barcode2 LIKE @BarCode OR BarCode3 LIKE @BarCode OR BarCode4 LIKE @BarCode OR prd.ProductCode LIKE @BarCode) ";
break;
case ItemSelectWndColumnSearchType.BcDesc:
SQL += " AND (Barcode1 LIKE @BarCode OR Barcode2 LIKE @BarCode OR BarCode3 LIKE @BarCode OR BarCode4 LIKE @BarCode OR prd.ProductDesc LIKE @BarCode) ";
break;
case ItemSelectWndColumnSearchType.PcDesc:
SQL += " AND (prd.ProductCode LIKE @BarCode OR prd.ProductDesc LIKE @BarCode) ";
break;
case ItemSelectWndColumnSearchType.SgPcDesc:
SQL += " AND (prd.ProductCode LIKE @BarCode OR prd.ProductDesc LIKE @BarCode OR sgrp.ProductSubGroupCode LIKE @BarCode) ";
break;
case ItemSelectWndColumnSearchType.SgDesc:
SQL += " AND (prd.ProductDesc LIKE @BarCode OR sgrp.ProductSubGroupCode LIKE @BarCode) ";
break;
default:
SQL += " AND (Barcode1 LIKE @BarCode OR Barcode2 LIKE @BarCode OR BarCode3 LIKE @BarCode OR BarCode4 LIKE @BarCode OR prd.ProductCode LIKE @BarCode) ";
break;
}
cmd.Parameters.AddWithValue("@BarCode", BarCode == "%" ? BarCode : BarCode + "%");
}
if (isQuantityGreaterThanZERO)
{
SQL += "AND inv.Quantity > 0 ";
}
SQL += " LIMIT " + limit + ") prd ";
SQL += " LEFT OUTER JOIN tblProductBaseVariationsMatrix mtrx ON mtrx.ProductID = prd.ProductID AND prd.MatrixID = mtrx.MatrixID ";
if (BranchID == 0)
{
SQL += " LEFT OUTER JOIN tblBranch brnch ON 1=1 " +
" LEFT OUTER JOIN tblProductInventory inv ON inv.ProductID = prd.ProductID AND inv.MatrixID = IFNULL(mtrx.MatrixID,0) AND brnch.BranchID = INV.BranchID";
}
else
{
SQL += " LEFT OUTER JOIN tblBranch brnch ON brnch.BranchID=" + BranchID.ToString() + " " +
" LEFT OUTER JOIN tblProductInventory inv ON inv.ProductID = prd.ProductID AND inv.MatrixID = IFNULL(mtrx.MatrixID,0) AND INV.BranchID=" + BranchID.ToString() + " ";
}
SQL += "WHERE IFNULL(mtrx.deleted, 0) = 0 ";
SQL += "GROUP BY prd.PackageID, " +
"prd.ProductID, " +
"prd.MatrixID, " +
"prd.ProductCode, " +
"prd.ProductDesc, " +
"prd.ProductSubGroupID, " +
"prd.ProductSubGroupCode, " +
"prd.BaseUnitID, " +
"prd.DateCreated, " +
"prd.PercentageCommision, " +
"prd.IncludeInSubtotalDiscount, " +
"prd.IsCreditChargeExcluded, " +
"prd.SupplierID, " +
"prd.RewardPoints, " +
"prd.MinThreshold, " +
"prd.MaxThreshold, " +
"prd.BarCode1, " +
"prd.BarCode2, " +
"prd.BarCode3, " +
"IFNULL(prd.BarCode1,prd.BarCode4), " +
"prd.Price, " +
"prd.Price1, prd.Price2, prd.Price3, prd.Price4, prd.Price5, " +
"prd.WSPrice, " +
"prd.PurchasePrice, " +
"prd.VAT, " +
"prd.EVAT, " +
"prd.LocalTax," +
"IFNULL(mtrx.Description,'') ";
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "ProductCode") + " ";
SQL += SortOrder == System.Data.SqlClient.SortOrder.Ascending ? "ASC " : "DESC ";
SQL += limit == 0 ? "" : "LIMIT " + limit.ToString() + " ";
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);
}
}
public System.Data.DataTable ListAsDataTable(Int32 BranchID = 0, ProductDetails clsSearchKeys = new ProductDetails(), ProductAddOnFilters ProductAddOnFilters = new ProductAddOnFilters(), ProductListFilterType clsProductListFilterType = ProductListFilterType.ShowActiveAndInactive,
long SequenceNoStart = 0, System.Data.SqlClient.SortOrder SequenceSortOrder = System.Data.SqlClient.SortOrder.Ascending, Int32 limit = 0, bool isQuantityGreaterThanZERO = false, string SortField = "ProductCode", SortOption SortOrder = SortOption.Ascending)
{
try
{
System.Data.DataTable dt = ListAsDataTable(BranchID, clsSearchKeys.BarCode, clsSearchKeys.ProductCode, clsSearchKeys.SupplierID, clsProductListFilterType,
isQuantityGreaterThanZERO, ProductAddOnFilters, limit, SortField, SortOrder);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
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);
}
}
private System.Data.DataTable ListAsDataTable(Int32 BranchID = 0, string BarCode = "", string ProductCode = "", Int64 SupplierID = 0, ProductListFilterType clsProductListFilterType = ProductListFilterType.ShowActiveAndInactive,
bool isQuantityGreaterThanZERO = false, ProductAddOnFilters ProductAddOnFilters = new ProductAddOnFilters(), Int32 limit = 0, string SortField = "", SortOption SortOrder = SortOption.Ascending)
{
// 26Oct2014 - additional filters ProductCodeFrom, ProductSubGroupNameFrom, ProductGroupNameFrom, SupplierNameFrom
string SQL = "CALL procProductSelect(@BranchID, @BarCode, @ProductCode, @SupplierID, @ShowActiveAndInactive, @isQuantityGreaterThanZERO, " +
"@BarcodeFrom, @BarcodeTo, " +
"@ProductCodeFrom, @ProductCodeTo, @ProductSubGroupNameFrom, @ProductSubGroupNameTo, " +
"@ProductGroupNameFrom, @ProductGroupNameTo, @SupplierNameFrom, @SupplierNameTo, " +
"@limit, @SortField, @SortOrder)";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("BranchID", BranchID);
cmd.Parameters.AddWithValue("BarCode", BarCode);
cmd.Parameters.AddWithValue("ProductCode", ProductCode);
cmd.Parameters.AddWithValue("SupplierID", SupplierID);
cmd.Parameters.AddWithValue("ShowActiveAndInactive", clsProductListFilterType.ToString("d"));
cmd.Parameters.AddWithValue("isQuantityGreaterThanZERO", isQuantityGreaterThanZERO);
cmd.Parameters.AddWithValue("BarcodeFrom", ProductAddOnFilters.BarcodeFrom);
cmd.Parameters.AddWithValue("BarcodeTo", ProductAddOnFilters.BarcodeTo);
cmd.Parameters.AddWithValue("ProductCodeFrom", ProductAddOnFilters.ProductCodeFrom);
cmd.Parameters.AddWithValue("ProductCodeTo", ProductAddOnFilters.ProductCodeTo);
cmd.Parameters.AddWithValue("ProductSubGroupNameFrom", ProductAddOnFilters.ProductSubGroupNameFrom);
cmd.Parameters.AddWithValue("ProductSubGroupNameTo", ProductAddOnFilters.ProductSubGroupNameTo);
cmd.Parameters.AddWithValue("ProductGroupNameFrom", ProductAddOnFilters.ProductGroupNameFrom);
cmd.Parameters.AddWithValue("ProductGroupNameTo", ProductAddOnFilters.ProductGroupNameTo);
cmd.Parameters.AddWithValue("SupplierNameFrom", ProductAddOnFilters.SupplierNameFrom);
cmd.Parameters.AddWithValue("SupplierNameTo", ProductAddOnFilters.SupplierNameTo);
cmd.Parameters.AddWithValue("limit", limit);
cmd.Parameters.AddWithValue("SortField", SortField);
cmd.Parameters.AddWithValue("SortOrder", SortOrder == SortOption.Ascending ? "ASC" : "DESC");
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;
}
public System.Data.DataTable ProductIDandCodeDataTable(ProductListFilterType clsProductListFilterType = ProductListFilterType.ShowActiveAndInactive, string SearchKey="", long SupplierID = 0, long ProductGroupID = 0, string ProductGroupName = "", long ProductSubGroupID=0, string ProductSubGroupName="", Int32 limit=100, bool isQuantityGreaterThanZERO=false, bool CheckIItemisSold=true, string SortField="ProductCode", SortOption SortOrder=SortOption.Ascending)
{
try
{
string SQL = "CALL procProductCodeSelect(@BranchID, @BarCode, @ProductCode, @SupplierID, @ShowActiveAndInactive, @isQuantityGreaterThanZERO, @lngLimit, @SortField, @SortOrder)";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@BranchID", 0);
cmd.Parameters.AddWithValue("@BarCode", SearchKey);
cmd.Parameters.AddWithValue("@ProductCode", SearchKey);
cmd.Parameters.AddWithValue("@SupplierID", SupplierID);
cmd.Parameters.AddWithValue("@ShowActiveAndInactive", clsProductListFilterType.ToString("d"));
cmd.Parameters.AddWithValue("@isQuantityGreaterThanZERO", isQuantityGreaterThanZERO);
cmd.Parameters.AddWithValue("@lngLimit", limit);
cmd.Parameters.AddWithValue("@SortField", SortField);
switch (SortOrder)
{
case SortOption.Ascending:
cmd.Parameters.AddWithValue("@SortOrder", "ASC");
break;
case SortOption.Desscending:
cmd.Parameters.AddWithValue("@SortOrder", "DESC");
break;
}
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 SearchDataTableSimple(ProductListFilterType clsProductListFilterType, string SearchKey, long SupplierID, long ProductGroupID, string ProductGroupName, long ProductSubGroupID, string ProductSubGroupName, Int32 limit, bool isQuantityGreaterThanZERO, bool CheckIItemisSold, string SortField, SortOption SortOrder)
//{
// try
// {
// string SQL = SQLSelectSimple() + "WHERE a.Deleted = 0 ";
// if (CheckIItemisSold) SQL += "AND a.IsItemSold = 1 ";
// if (clsProductListFilterType == ProductListFilterType.ShowActiveOnly) SQL += "AND a.Active = 1 ";
// if (clsProductListFilterType == ProductListFilterType.ShowInactiveOnly) SQL += "AND a.Active = 0 ";
// if (SearchKey != string.Empty)
// {
// SQL += "AND (Barcode LIKE @SearchKey " +
// "OR ProductCode LIKE @SearchKey " +
// "OR ProductDesc LIKE @SearchKey) ";
// }
// if (SupplierID != 0)
// SQL += "AND (SupplierID = " + SupplierID + " OR ProductID IN (SELECT DISTINCT(ProductID) FROM tblProductBaseVariationsMatrix WHERE SupplierID = " + SupplierID + ")) ";
// if (ProductGroupID != Constants.ZERO)
// { SQL += "AND a.ProductGroupID = " + ProductGroupID + " "; }
// //if (ProductGroupName != "" && ProductGroupName != null)
// //{ SQL += "AND a.ProductGroupID = (SELECT ProductGroupName = '" + ProductGroupName + "') "; }
// //else { SQL += "AND ProductGroupName <> '' "; }
// //if (ProductSubGroupID != Constants.ZERO)
// //{ SQL += "AND a.ProductSubGroupID = " + ProductSubGroupID + " "; }
// //if (ProductSubGroupName != "" && ProductSubGroupName != null)
// //{ SQL += "AND ProductSubGroupName = '" + ProductSubGroupName + "' "; }
// //else { SQL += "AND ProductSubGroupName <> '' "; }
// if (isQuantityGreaterThanZERO == true)
// SQL += "AND a.Quantity > 0 ";
// 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 SearchDataTableSimple(int BranchID, ProductListFilterType clsProductListFilterType, string SearchKey, long SupplierID, long ProductGroupID, string ProductGroupName, long ProductSubGroupID, string ProductSubGroupName, Int32 limit, bool isQuantityGreaterThanZERO, bool CheckIItemisSold, string SortField, SortOption SortOrder)
{
try
{
string SQL = SQLSelectSimple(BranchID);
if (CheckIItemisSold) SQL += "AND a.IsItemSold = 1 ";
if (clsProductListFilterType == ProductListFilterType.ShowActiveOnly) SQL += "AND a.Active = 1 ";
if (clsProductListFilterType == ProductListFilterType.ShowInactiveOnly) SQL += "AND a.Active = 0 ";
if (!string.IsNullOrEmpty(SearchKey))
{ SQL += "AND (Barcode LIKE @SearchKey OR Barcode2 LIKE @SearchKey OR ProductCode LIKE @SearchKey) "; }
if (SupplierID != 0)
SQL += "AND (SupplierID = " + SupplierID + " OR a.ProductID IN (SELECT DISTINCT(ProductID) FROM tblProductBaseVariationsMatrix WHERE SupplierID = " + SupplierID + ")) ";
if (ProductGroupID != Constants.ZERO)
{ SQL += "AND a.ProductGroupID = " + ProductGroupID + " "; }
if (isQuantityGreaterThanZERO == true)
SQL += "AND a.Quantity > 0 ";
if (!string.IsNullOrEmpty(SortField)) 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 SearchDataTableActiveInactive(ProductListFilterType clsProductListFilterType, string SearchKey, string SortField, SortOption SortOrder, Int32 limit, bool isQuantityGreaterThanZERO)
{
try
{
System.Data.DataTable dt = SearchDataTable(clsProductListFilterType, SearchKey, Constants.ZERO, Constants.ZERO, string.Empty, Constants.ZERO, string.Empty, limit, isQuantityGreaterThanZERO, false, SortField, SortOrder);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public System.Data.DataTable ListAsDataTableActiveInactive(ProductListFilterType clsProductListFilterType, string SortField, SortOption SortOrder, int limit, bool isQuantityGreaterThanZERO)
{
try
{
string SQL = SQLSelect() + "WHERE a.Deleted = 0 ";
if (clsProductListFilterType == ProductListFilterType.ShowActiveOnly) SQL += "AND a.Active = 1 ";
else if (clsProductListFilterType == ProductListFilterType.ShowInactiveOnly) SQL += "AND a.Active = 0 ";
if (isQuantityGreaterThanZERO == true)
SQL += "AND a.Quantity > 0 ";
if (SortField != string.Empty && SortField != null)
{
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;
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);
}
}
