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