private string SQLSelect(ProductPackageColumns clsProductPackageColumns)
{
string stSQL = "SELECT ";
if (clsProductPackageColumns.ProductID) stSQL += "tblProductPackage.ProductID, ";
if (clsProductPackageColumns.MatrixID) stSQL += "tblProductPackage.MatrixID, ";
if (clsProductPackageColumns.UnitID) stSQL += "tblProductPackage.UnitID, ";
if (clsProductPackageColumns.UnitCode) stSQL += "tblUnit.UnitCode, ";
if (clsProductPackageColumns.UnitName) stSQL += "tblUnit.UnitName, ";
if (clsProductPackageColumns.Price) stSQL += "tblProductPackage.Price, ";
if (clsProductPackageColumns.Price) stSQL += "tblProductPackage.Price1, ";
if (clsProductPackageColumns.Price) stSQL += "tblProductPackage.Price2, ";
if (clsProductPackageColumns.Price) stSQL += "tblProductPackage.Price3, ";
if (clsProductPackageColumns.Price) stSQL += "tblProductPackage.Price4, ";
if (clsProductPackageColumns.Price) stSQL += "tblProductPackage.Price5, ";
if (clsProductPackageColumns.WSPrice) stSQL += "tblProductPackage.WSPrice, ";
if (clsProductPackageColumns.PurchasePrice) stSQL += "tblProductPackage.PurchasePrice, ";
if (clsProductPackageColumns.Quantity) stSQL += "tblProductPackage.Quantity, ";
if (clsProductPackageColumns.VAT) stSQL += "tblProductPackage.VAT, ";
if (clsProductPackageColumns.EVAT) stSQL += "tblProductPackage.EVAT, ";
if (clsProductPackageColumns.LocalTax) stSQL += "tblProductPackage.LocalTax, ";
if (clsProductPackageColumns.BarCode1) stSQL += "tblProductPackage.BarCode1, ";
if (clsProductPackageColumns.BarCode2) stSQL += "tblProductPackage.BarCode2, ";
if (clsProductPackageColumns.BarCode3) stSQL += "tblProductPackage.BarCode3, ";
if (clsProductPackageColumns.ProductDesc) stSQL += "tblProducts.ProductDesc, ";
stSQL += "tblProductPackage.PackageID ";
stSQL += "FROM tblProductPackage ";
if (clsProductPackageColumns.ProductDesc)
stSQL += "INNER JOIN tblProducts ON tblProductPackage.ProductID = tblProducts.ProductID ";
if (clsProductPackageColumns.UnitCode || clsProductPackageColumns.UnitName)
stSQL += "INNER JOIN tblUnit ON tblProductPackage.UnitID = tblUnit.UnitID ";
return stSQL;
}
public ProductPackageColumns getAllPackageColumns()
{
ProductPackageColumns clsProductPackageColumns = new ProductPackageColumns();
clsProductPackageColumns.PackageID = true;
clsProductPackageColumns.ProductID = true;
clsProductPackageColumns.MatrixID = true;
clsProductPackageColumns.UnitID = true;
clsProductPackageColumns.UnitCode = true;
clsProductPackageColumns.UnitName = true;
clsProductPackageColumns.PurchasePrice = true;
clsProductPackageColumns.Price = true;
clsProductPackageColumns.Price1 = true;
clsProductPackageColumns.Price2 = true;
clsProductPackageColumns.Price3 = true;
clsProductPackageColumns.Price4 = true;
clsProductPackageColumns.Price5 = true;
clsProductPackageColumns.WSPrice = true;
clsProductPackageColumns.Quantity = true;
clsProductPackageColumns.VAT = true;
clsProductPackageColumns.EVAT = true;
clsProductPackageColumns.LocalTax = true;
clsProductPackageColumns.BarCode1 = true;
clsProductPackageColumns.BarCode2 = true;
clsProductPackageColumns.BarCode3 = true;
clsProductPackageColumns.ProductDesc = true;
return clsProductPackageColumns;
}
public System.Data.DataTable ListAsDataTable(ProductPackageColumns clsProductPackageColumns, ProductPackageColumns SearchColumns, string SearchKey, long SequenceNoStart, System.Data.SqlClient.SortOrder SequenceSortOrder, int Limit, string SortField, System.Data.SqlClient.SortOrder SortOrder)
{
try
{
string SQL = SQLSelect(clsProductPackageColumns) + "WHERE 1=1 ";
if (SequenceNoStart != 0)
{
if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
SQL += "AND PackageID < " + SequenceNoStart.ToString() + " ";
else
SQL += "AND PackageID > " + SequenceNoStart.ToString() + " ";
}
if (SearchKey != string.Empty)
{
string SQLSearch = string.Empty;
if (SearchColumns.BarCode1)
{ if (SQLSearch == string.Empty) SQLSearch += "Barcode1 LIKE @SearchKey "; else SQLSearch += "OR Barcode1 LIKE @SearchKey "; }
if (SearchColumns.BarCode2)
{ if (SQLSearch == string.Empty) SQLSearch += "Barcode2 LIKE @SearchKey "; else SQLSearch += "OR Barcode2 LIKE @SearchKey "; }
if (SearchColumns.BarCode3)
{ if (SQLSearch == string.Empty) SQLSearch += "Barcode3 LIKE @SearchKey "; else SQLSearch += "OR Barcode3 LIKE @SearchKey "; }
if (SQLSearch != string.Empty) SQL += "AND (" + SQLSearch + ") ";
if (SearchColumns.ProductID)
{ SQL += "AND tblProductPackage.ProductID = " + SearchKey + " "; }
}
if (SortField != string.Empty && SortField != null)
{
SQL += "ORDER BY " + SortField + " ";
if (SortOrder != System.Data.SqlClient.SortOrder.Descending) 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);
if (SearchKey.StartsWith("%") == true)
prmSearchKey.Value = SearchKey + "%";
else
prmSearchKey.Value = "%" + SearchKey + "%";
cmd.Parameters.Add(prmSearchKey);
System.Data.DataTable dt = new System.Data.DataTable("tblProductPackage");
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
{
}
throw base.ThrowException(ex);
}
}
