public ItemSelectWndColumnSearchType get_ITEMSELECTWND_COLUMN_SEARCH_TYPE()
{
ItemSelectWndColumnSearchType clsItemSelectWndColumnType = ItemSelectWndColumnSearchType.BcDesc;
try
{
clsItemSelectWndColumnType = (ItemSelectWndColumnSearchType)Enum.Parse(typeof(ItemSelectWndColumnSearchType), get_Sysconfig(Constants.SYS_CONFIG_ITEMSELECTWND_COLUMNTYPE));
}
catch { }
return(clsItemSelectWndColumnType);
}
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);
}
}