Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
            }
        }