public virtual void GetProductIDFromBarCode(CDBUtil dbUtil, MySqlConnection conn, string productCode)
        {
            string sqlBarCode = " SELECT pb.ProductID, pb.ProductBarCode FROM productbarcode pb "
                                + " INNER JOIN products p "
                                + " ON(p.ProductID = pb.ProductID) "
                                + " WHERE pb.ProductBarCode = '" + productCode.Trim() + "'";

            string sqlProductCode = " SELECT ProductID, ProductCode FROM products WHERE ProductCode = '" + productCode.Trim() + "'";

            MySqlDataReader reader = dbUtil.sqlRetrive(sqlBarCode, conn);

            if (reader.Read())
            {
                if (reader["ProductID"] != DBNull.Value)
                {
                    ProductId = Convert.ToInt32(reader["ProductID"]);
                }
            }
            else
            {
                MySqlDataReader reader2 = dbUtil.sqlRetrive(sqlProductCode, conn);
                if (reader2.Read())
                {
                    if (reader2["ProductID"] != DBNull.Value)
                    {
                        ProductId = Convert.ToInt32(reader2["ProductID"]);
                    }
                }
                else
                {
                    ProductId = 0;
                }
            }
            reader.Close();
        }
예제 #2
0
        public virtual bool CheckDailyDocIsApprove(CDBUtil dbUtil, MySqlConnection conn)
        {
            string sql = " SELECT DocumentDate, DocumentID FROM document WHERE documenttypeid=30 AND shopid=" + ShopId +
                         " AND DocumentStatus=1 AND DocumentDate='" + this.DocumentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'";
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                DocumentDate = reader.GetDateTime("DocumentDate");
                DocumentId   = reader.GetInt32("DocumentID");
                reader.Close();
                return(false); // Approved
            }
            else
            {
                sql = " SELECT DocumentDate, DocumentID FROM document WHERE documenttypeid=30 AND shopid=" + ShopId +
                      " AND DocumentStatus=2 AND DocumentDate='" + this.DocumentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'";
                reader.Close();
                reader = dbUtil.sqlRetrive(sql, conn);
                if (reader.Read())
                {
                    DocumentDate = reader.GetDateTime("DocumentDate");
                    DocumentId   = reader.GetInt32("DocumentID");
                    reader.Close();
                    return(true); // Not Approve
                }
                else
                {
                    reader.Close();
                }
            }
            return(false);
        }
        public virtual bool CheckDailyDocIsApprove(CDBUtil dbUtil, MySqlConnection conn, int documentId)
        {
            string sql = " SELECT * FROM document WHERE documentId=" + documentId +
                         " AND documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
                         " AND DocumentStatus=1 ";

            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                if (reader["DocumentDate"] != DBNull.Value)
                {
                    this.DocumentDate = reader.GetDateTime("DocumentDate");
                }
                if (reader["DocumentID"] != DBNull.Value)
                {
                    this.DocumentId = reader.GetInt32("DocumentID");
                }
                if (reader["Remark"] != DBNull.Value)
                {
                    this.Remark = reader.GetString("Remark");
                }
                reader.Close();
                return(false); // Approved
            }
            else
            {
                sql = " SELECT * FROM document WHERE documentId=" + documentId +
                      " AND documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
                      " AND DocumentStatus=2 ";
                reader.Close();
                reader = dbUtil.sqlRetrive(sql, conn);
                if (reader.Read())
                {
                    if (reader["DocumentDate"] != DBNull.Value)
                    {
                        this.DocumentDate = reader.GetDateTime("DocumentDate");
                    }
                    if (reader["DocumentID"] != DBNull.Value)
                    {
                        this.DocumentId = reader.GetInt32("DocumentID");
                    }
                    if (reader["Remark"] != DBNull.Value)
                    {
                        this.Remark = reader.GetString("Remark");
                    }
                    reader.Close();
                    return(true); // Not Approve
                }
                else
                {
                    reader.Close();
                }
            }
            return(false);
        }
        public int GetMaxVendorId(CDBUtil dbUtil, MySqlConnection conn)
        {
            // get max vendorId from IdRange tabel
            int    maxVendorId = 0;
            string sql         = " SELECT MAX(VendorID) AS MaxVendorID FROM vendors WHERE ShopID=" + ShopId;

            //ShopId = ShopId == 1 ? 0 : ShopId;
            IdRange idRange = new IdRange(ShopId, 15);

            idRange.getIdRange(conn);

            using (MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn))
            {
                if (reader.Read())
                {
                    if (reader["MaxVendorID"] != DBNull.Value)
                    {
                        maxVendorId = reader.GetInt32("MaxVendorID") + 1;
                        if (!(maxVendorId >= idRange.MinId && maxVendorId <= idRange.MaxId))
                        {
                            maxVendorId = idRange.MinId;
                        }
                    }
                    else
                    {
                        maxVendorId = idRange.MinId;
                    }
                }
                else
                {
                    maxVendorId = idRange.MinId;
                }
            }
            return(maxVendorId);
        }
예제 #5
0
        public void StaffsInfo(CDBUtil dbUtil, MySqlConnection conn, int staffId)
        {
            string          sql    = "SELECT * FROM staffs WHERE StaffID=" + staffId;
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            while (reader.Read())
            {
                if (reader["StaffID"] != DBNull.Value)
                {
                    StaffId = Convert.ToInt32(reader["StaffID"]);
                }
                if (reader["StaffCode"] != DBNull.Value)
                {
                    StaffCode = reader["StaffCode"].ToString();
                }
                if (reader["StaffFirstName"] != DBNull.Value)
                {
                    StaffFirstName = reader["StaffFirstName"].ToString();
                }
                if (reader["StaffLastName"] != DBNull.Value)
                {
                    StaffLastName = reader["StaffLastName"].ToString();
                }
            }
            reader.Close();
        }
예제 #6
0
        public List <Document> ListDocType10(CDBUtil dbUtil, MySqlConnection conn)
        {
            string sql = " SELECT * FROM document " +
                         " WHERE ShopID= " + ProductLevelId + " AND DocumentTypeID = 10 " +
                         " AND documentstatus=2";
            MySqlDataReader reader  = dbUtil.sqlRetrive(sql, conn);
            List <Document> docList = new List <Document>();

            while (reader.Read())
            {
                Document document = new Document();
                if (reader["DocumentID"] != DBNull.Value)
                {
                    document.DocumentId = Convert.ToInt32(reader["DocumentID"]);
                }
                if (reader["DocumentYear"] != DBNull.Value)
                {
                    document.DocumentYear     = Convert.ToInt32(reader["DocumentYear"]);
                    document.DocumentMonth    = Convert.ToInt32(reader["DocumentMonth"]);
                    document.DocumentMonthStr = Convert.ToDateTime(new DateTime(Convert.ToInt32(reader["DocumentYear"]),
                                                                                Convert.ToInt32(reader["DocumentMonth"]), 1)).ToString("MMMM");
                }
                docList.Add(document);
            }
            reader.Close();
            return(docList);
        }
        // check working document from operationdate
        public virtual bool GetWorkingStockDocument(CDBUtil dbUtil, MySqlConnection conn, DateTime operationDate)
        {
            Boolean isApprove = true;
            string  sql       = " SELECT * FROM document " +
                                " WHERE documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
                                " AND DocumentStatus=1 " +
                                " AND DocumentDate='" + operationDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'";
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                if (reader["DocumentDate"] != DBNull.Value)
                {
                    this.DocumentDate = reader.GetDateTime("DocumentDate");
                }
                if (reader["DocumentID"] != DBNull.Value)
                {
                    this.DocumentId = reader.GetInt32("DocumentID");
                }
                if (reader["Remark"] != DBNull.Value)
                {
                    this.Remark = reader.GetString("Remark");
                }
                if (reader["DocumentStatus"] != DBNull.Value)
                {
                    this.DocumentStatus = reader.GetInt32("DocumentStatus");
                }
                isApprove = false;
            }
            reader.Close();
            return(isApprove);
        }
예제 #8
0
        public List <ProductLevel> ListInv(CDBUtil dbUtil, MySqlConnection conn, bool isStockCountConfig)
        {
            string sql = "";

            sql = " SELECT * FROM ProductLevel WHERE Deleted = 0 ";
            MySqlDataReader reader;

            reader = dbUtil.sqlRetrive(sql, conn);
            List <ProductLevel> invList = new List <ProductLevel>();

            while (reader.Read())
            {
                ProductLevel invInfo = new ProductLevel();
                if (reader["ProductLevelID"] != DBNull.Value)
                {
                    invInfo.ProductLevelId = reader.GetInt32("ProductLevelID");
                }
                if (reader["ProductLevelCode"] != DBNull.Value)
                {
                    invInfo.ProductLevelCode = reader.GetString("ProductLevelCode");
                }
                if (reader["ProductLevelName"] != DBNull.Value)
                {
                    invInfo.ProductLevelName = reader.GetString("ProductLevelName");
                }
                invList.Add(invInfo);
            }
            reader.Close();
            return(invList);
        }
        public virtual bool CheckTransferDocument(CDBUtil dbUtil, MySqlConnection conn, int inv, ref DateTime lastTransferDate)
        {
            // หาเอกสารยกยอดของเดือนปัจจุบัน
            bool   isTransfer = false;
            string sql        = "SELECT * FROM document WHERE ShopID=" + inv + " AND DocumentTypeID=10 " +
                                " AND DocumentStatus=" + DocumentStatus + " AND DocumentMonth=" + DateTime.Now.Month + " AND DocumentYear=" + DateTime.Now.ToString("yyyy", dateProvider);

            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                if (reader["DocumentDate"] != DBNull.Value)
                {
                    lastTransferDate = reader.GetDateTime("DocumentDate");
                }
                if (reader["Remark"] != DBNull.Value)
                {
                    this.Remark = reader.GetString("Remark");
                }
                isTransfer = true;
            }
            reader.Close();

            //ถ้าไม่เจอ หาล่าสุด
            if (isTransfer == false)
            {
                sql = "SELECT * FROM document WHERE ShopID=" + inv + " AND DocumentTypeID=" +
                      " 10 AND DocumentStatus=" + DocumentStatus + " ORDER BY DocumentDate DESC LIMIT 1";

                reader = dbUtil.sqlRetrive(sql, conn);
                if (reader.Read())
                {
                    if (reader["DocumentDate"] != DBNull.Value)
                    {
                        lastTransferDate = reader.GetDateTime("DocumentDate");
                    }

                    isTransfer = true;
                }
            }
            reader.Close();
            return(isTransfer);
        }
        public override bool CheckCountStockWeeklyDay(CDBUtil dbUtil, MySqlConnection conn)
        {
            // programpropertyvalue programtype 3 = invent, PropertyID 7 = weekly stock count
            DayOfWeek dayOfWeek = (DayOfWeek)DateTime.Now.DayOfWeek;
            int       day       = 0;

            switch (dayOfWeek.ToString())
            {
            case "Sunday":
                day = 0;
                break;

            case "Monday":
                day = 1;
                break;

            case "Tuesday":
                day = 2;
                break;

            case "Wednesday":
                day = 3;
                break;

            case "Thursday":
                day = 4;
                break;

            case "Friday":
                day = 5;
                break;

            case "Saturday":
                day = 6;
                break;
            }

            string sql = @" SELECT * FROM programpropertyvalue " +
                         @" WHERE ProgramTypeID=3 AND PropertyID=7 AND PropertyValue=" + day;

            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                reader.Close();
                return(false);
            }
            else
            {
            }
            return(true);
        }
예제 #11
0
        public bool CheckTransferStockOfThisShop(CDBUtil dbUtil, MySqlConnection conn)
        {
            string sql = " SELECT * FROM document WHERE " +
                         " ShopID= " + ProductLevelId + " AND DocumentTypeID = 10 " +
                         " AND DocumentMonth = " + DocumentMonth +
                         " AND DocumentYear = " + DocumentYear + " AND DocumentStatus = 2";
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                reader.Close();
                return(true);
            }
            return(false);
        }
예제 #12
0
        public DateTime GetLastTransferDocument(CDBUtil dbUtil, MySqlConnection conn, int invId)
        {
            string          sql    = "SELECT * FROM document WHERE ShopID=" + invId + " AND DocumentTypeID=10 ORDER BY DocumentDate DESC LIMIT 1";
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                if (reader["DocumentID"] != DBNull.Value)
                {
                    this.DocumentDate = reader.GetDateTime("DocumentDate");
                }
            }
            reader.Close();
            return(this.DocumentDate);
        }
예제 #13
0
        public int GetMaxVendorGroupId(CDBUtil dbUtil, MySqlConnection conn)
        {
            int             maxVendorGroupId = 0;
            string          sql    = " SELECT MAX(VendorGroupID) AS MaxVendorGroupID FROM vendorgroup WHERE ShopID=" + ShopId;
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                if (reader["MaxVendorGroupID"] != DBNull.Value)
                {
                    maxVendorGroupId = reader.GetInt32("MaxVendorGroupID") + 1;
                }
            }
            reader.Close();
            return(maxVendorGroupId);
        }
예제 #14
0
        public int GetLastEndMonth(CDBUtil dbUtil, MySqlConnection conn)
        {
            int    month = 0;
            string sql   = " SELECT MAX(DocumentMonth) AS DocumentMonth FROM document " +
                           " WHERE ShopID= " + ProductLevelId + " AND DocumentTypeID = 10 " +
                           " AND DocumentYear = " + DocumentYear + " AND documentstatus=2";
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            while (reader.Read())
            {
                if (reader["DocumentMonth"] != DBNull.Value)
                {
                    month = Convert.ToInt32(reader["DocumentMonth"]);
                }
            }
            reader.Close();
            return(month);
        }
예제 #15
0
        public void GetLastDocdetailID(CDBUtil dbUtil, MySqlConnection conn)
        {
            string strSql = "SELECT MAX(DocDetailID) AS LastDocDetailID FROM docdetailtemp " +
                            " WHERE DocumentID=" + DocumentId + " AND ShopID=" + ShopId;

            MySqlDataReader reader          = dbUtil.sqlRetrive(strSql, conn);
            int             lastDocDetailID = 0;

            while (reader.Read())
            {
                if (reader["LastDocDetailID"] != DBNull.Value)
                {
                    lastDocDetailID = reader.GetInt32(0);
                }
            }
            reader.Close();
            this.DocDetailId = lastDocDetailID + 1;
        }
        public decimal GetMaterialSumAll(CDBUtil dbUtil, MySqlConnection conn,
                                         DateTime dateFrom, DateTime dateTo, int materialId, int invId)
        {
            string strDocumentTypeId = "";

            if (this.DocumentTypeId == 24)
            {
                strDocumentTypeId = "(18,19)";
            }
            else if (DocumentTypeId == 30)
            {
                strDocumentTypeId = "(31,32)";
            }
            else if (DocumentTypeId == 57)
            {
                strDocumentTypeId = "(58,59)";
            }

            string sql = "";

            sql = " SELECT SUM(b.ProductAmount) * c.MovementInStock AS DiffAmount " +
                  " FROM document a " +
                  " INNER JOIN docdetail b " +
                  " ON a.DocumentID=b.DocumentID AND a.ShopID=b.ShopID " +
                  " INNER JOIN documenttype c " +
                  " ON a.DocumentTypeID=c.DocumentTypeID AND a.ShopID=c.ShopID " +
                  " WHERE a.DocumentTypeID IN " + strDocumentTypeId + " AND b.ProductID=" + materialId + " AND " +
                  " a.DocumentDate BETWEEN {d '" + dateFrom.ToString("yyyy'-'MM'-'dd", dateProvider) + "'} " +
                  " AND {d '" + dateTo.ToString("yyyy'-'MM'-'dd", dateProvider) + "'} " +
                  " AND a.ShopID=" + invId + " AND c.LangID=2 GROUP BY b.ProductID";
            decimal         diffAmount = 0;
            MySqlDataReader reader     = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                diffAmount = reader.GetDecimal("DiffAmount");
            }
            reader.Close();
            return(diffAmount);
        }
        public decimal GetMaterialSumByCountDate(CDBUtil dbUtil, MySqlConnection conn,
                                                 DateTime documentDate, int materialId, int invId)
        {
            string sql = "";

            sql = " SELECT SUM(b.ProductAmount) AS DiffAmount " +
                  " FROM document a " +
                  " INNER JOIN docdetail b " +
                  " ON a.DocumentID=b.DocumentID AND a.ShopID=b.ShopID " +
                  " WHERE a.DocumentTypeID = " + this.DocumentTypeId + " AND b.ProductID=" + materialId + " AND " +
                  " a.DocumentDate={d '" + documentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'} " +
                  " AND a.ShopID=" + invId + " GROUP BY b.ProductID, b.ProductAmount, a.DocumentDate";
            decimal         diffAmount = 0;
            MySqlDataReader reader     = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                diffAmount = reader.GetDecimal("DiffAmount");
            }
            reader.Close();
            return(diffAmount);
        }
        public virtual bool GetLastCountDate(CDBUtil dbUtil, MySqlConnection conn, int invId, ref DateTime lastCountDate)
        {
            bool   isCount = false;
            string sql     = " SELECT * FROM document WHERE ShopID=" + invId + " AND  DocumentTypeID=" + this.DocumentTypeId +
                             " AND DocumentStatus IN (" + DocumentStatusStr + ") ORDER BY DocumentDate DESC LIMIT 1";
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                if (reader["DocumentDate"] != DBNull.Value)
                {
                    lastCountDate = reader.GetDateTime("DocumentDate");
                }
                if (reader["Remark"] != DBNull.Value)
                {
                    this.Remark = reader.GetString("Remark");
                }
                isCount = true;
            }
            reader.Close();
            return(isCount);
        }
예제 #19
0
        public void GetLastDocumentNumber(CDBUtil dbUtil, MySqlConnection conn)
        {
            //MySqlDataReader reader = dbUtil.sqlRetrive(" SELECT MaxDocumentNumber FROM maxdocumentnumber WHERE " +
            //    " ShopID=" + this.ShopId + " AND DocType=" + this.DocumentTypeId + " AND DocumentYear = " + this.DocumentYear +
            //    " AND DocumentMonth = " + this.DocumentMonth, conn);
            //if (reader.Read())
            //{
            //    if (reader["MaxDocumentNumber"] != DBNull.Value)
            //        this.DocumentNumber = reader.GetInt32(0) + 1;
            //}
            //else
            //{
            //reader.Close();

            MySqlDataReader reader = dbUtil.sqlRetrive("SELECT MAX(DocumentNumber) AS DocumentNumber FROM document " +
                                                       " WHERE ShopID=" + this.ShopId + " AND DocumentTypeID=" + this.DocumentTypeId +
                                                       " AND DocumentYear=" + this.DocumentYear + " AND DocumentMonth=" + this.DocumentMonth, conn);

            if (reader.Read())
            {
                if (reader["DocumentNumber"] != DBNull.Value)
                {
                    this.DocumentNumber = reader.GetInt32(0) + 1;
                }
                else
                {
                    this.DocumentNumber = 1;
                }
            }
            else
            {
                this.DocumentNumber = 1;
            }
            reader.Close();
            //}

            //if (!reader.IsClosed)
            //    reader.Close();
        }
예제 #20
0
        public void ProductData(CDBUtil dbUtil, MySqlConnection conn, int productId)
        {
            string sql = " SELECT a.*, b.* FROM products a " +
                         " JOIN  productprice b " +
                         " ON a.ProductID=b.ProductID " +
                         " WHERE b.MainPrice=1 AND a.ProductID=" + productId +
                         " AND a.Deleted=0";
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            while (reader.Read())
            {
                if (reader["ProductCode"] != DBNull.Value)
                {
                    ProductCode = reader["ProductCode"].ToString();
                }
                if (reader["ProductName"] != DBNull.Value)
                {
                    ProductName = reader["ProductName"].ToString();
                }
                if (reader["ProductVATRate"] != DBNull.Value)
                {
                    VatRate = Convert.ToInt32(reader["ProductVATRate"]);
                }
                if (reader["VATType"] != DBNull.Value)
                {
                    VatType = Convert.ToInt32(reader["VATType"]);
                }
                if (reader["ProductPrice"] != DBNull.Value)
                {
                    ProductPrice = Convert.ToDecimal(reader["ProductPrice"]);
                }
                if (reader["ProductUnitName"] != DBNull.Value)
                {
                    ProductUnitName = reader["ProductUnitName"].ToString();
                }
            }
            reader.Close();
        }
        public virtual bool GetWorkingStockDocument(CDBUtil dbUtil, MySqlConnection conn)
        {
            /* ตรวจสอบว่า มีเอกสารการนับที่ยังไม่อนุมัติหรือป่าว โดยเอาตัวล่าสุดเสมอ
             *
             */
            Boolean isApprove = true;
            string  sql       = " SELECT * FROM document " +
                                " WHERE documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
                                " AND DocumentStatus=1 AND DocumentMonth=" + this.DocumentDate.Month +
                                " AND DocumentYear=" + this.DocumentDate.Year + //" ORDER BY documentdate DESC LIMIT 1";
                                " AND DocumentDate='" + this.DocumentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'";
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                if (reader["DocumentDate"] != DBNull.Value)
                {
                    this.DocumentDate = reader.GetDateTime("DocumentDate");
                }
                if (reader["DocumentID"] != DBNull.Value)
                {
                    this.DocumentId = reader.GetInt32("DocumentID");
                }
                if (reader["Remark"] != DBNull.Value)
                {
                    this.Remark = reader.GetString("Remark");
                }
                if (reader["DocumentStatus"] != DBNull.Value)
                {
                    this.DocumentStatus = reader.GetInt32("DocumentStatus");
                }
                isApprove = false;
            }
            reader.Close();
            return(isApprove);
        }
예제 #22
0
        public List <Vendors> GetVendorGroup(CDBUtil dbUtil, MySqlConnection conn)
        {
            string strSql = "SELECT * FROM vendorgroup WHERE Deleted=0";

            if (ShopId != 1)
            {
                strSql += " AND ShopID=" + ShopId;
            }

            List <Vendors>  listVendorGroup = new List <Vendors>();
            MySqlDataReader reader          = dbUtil.sqlRetrive(strSql, conn);

            while (reader.Read())
            {
                if (reader["VendorGroupID"] != DBNull.Value)
                {
                    listVendorGroup.Add(new Vendors(Convert.ToInt32(reader["VendorGroupID"]),
                                                    reader["VendorGroupCode"].ToString(),
                                                    reader["VendorGroupName"].ToString()));
                }
            }
            reader.Close();
            return(listVendorGroup);
        }
예제 #23
0
        public List <Vendors> ListVendorGroup(CDBUtil dbUtil, MySqlConnection conn)
        {
            string sql = " SELECT a.*, b.ProductLevelName FROM vendorgroup a, productlevel b WHERE a.ShopID=b.ProductLevelID AND a.Deleted=0";
            // if (ShopId != 0)
            //    sql += " AND ShopID=" + ShopId;
            MySqlDataReader reader     = dbUtil.sqlRetrive(sql, conn);
            List <Vendors>  vendorList = new List <Vendors>();

            while (reader.Read())
            {
                Vendors vendor = new Vendors();
                if (reader["VendorGroupID"] != DBNull.Value)
                {
                    vendor.VendorGroupId = reader.GetInt32("VendorGroupID");
                }
                if (reader["ShopID"] != DBNull.Value)
                {
                    vendor.ShopId = reader.GetInt32("ShopID");
                }
                if (reader["VendorGroupCode"] != DBNull.Value)
                {
                    vendor.VendorGroupCode = reader.GetString("VendorGroupCode");
                }
                if (reader["VendorGroupName"] != DBNull.Value)
                {
                    vendor.VendorGroupName = reader.GetString("VendorGroupName");
                }
                if (reader["ProductLevelName"] != DBNull.Value)
                {
                    vendor.VendorInvName = reader.GetString("ProductLevelName");
                }
                vendorList.Add(vendor);
            }
            reader.Close();
            return(vendorList);
        }
예제 #24
0
        public List <ProductLevel> ListInv(CDBUtil dbUtil, MySqlConnection conn, int staffRoleId)
        {
            bool   isHQ = false;
            string sql  = "";

            // Check shop is hq
            sql = " SELECT * FROM property WHERE HeadOrBranch=1";
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                isHQ = true;
            }
            else
            {
                isHQ = false;
            }
            reader.Close();

            sql = "";
            if (isHQ)
            {
                sql = " SELECT * FROM ProductLevel WHERE Deleted = 0 AND IsInv=1 ";
            }
            else
            {
                sql = " SELECT * FROM ProductLevel WHERE Deleted = 0 AND IsInv=1 AND ProductLevelID <> 1  ";
            }

            System.Data.DataTable dtResult = dbUtil.List("SELECT * FROM staffroleviewinventory WHERE StaffRoleID=" + staffRoleId, conn);
            if (dtResult.Rows.Count > 0)
            {
                sql += " AND ProductLevelID IN ( ";
                for (int i = 0; i <= dtResult.Rows.Count - 1; i++)
                {
                    sql += dtResult.Rows[i]["ProductLevelID"].ToString();
                    if (i < dtResult.Rows.Count - 1)
                    {
                        sql += " , ";
                    }
                }
                sql += " ) ";
            }
            reader = dbUtil.sqlRetrive(sql, conn);
            List <ProductLevel> invList = new List <ProductLevel>();

            while (reader.Read())
            {
                ProductLevel invInfo = new ProductLevel();
                if (reader["ProductLevelID"] != DBNull.Value)
                {
                    invInfo.ProductLevelId = reader.GetInt32("ProductLevelID");
                }
                if (reader["ProductLevelCode"] != DBNull.Value)
                {
                    invInfo.ProductLevelCode = reader.GetString("ProductLevelCode");
                }
                if (reader["ProductLevelName"] != DBNull.Value)
                {
                    invInfo.ProductLevelName = reader.GetString("ProductLevelName");
                }
                invList.Add(invInfo);
            }
            reader.Close();
            return(invList);
        }
        public virtual bool CheckCountStockWeeklyDay(CDBUtil dbUtil, MySqlConnection conn)
        {
            // programpropertyvalue programtype 3 = invent, PropertyID 7 = weekly stock count
            DayOfWeek dayOfWeek = (DayOfWeek)DateTime.Now.DayOfWeek;
            int       day       = 0;

            switch (dayOfWeek.ToString())
            {
            case "Sunday":
                day = 0;
                break;

            case "Monday":
                day = 1;
                break;

            case "Tuesday":
                day = 2;
                break;

            case "Wednesday":
                day = 3;
                break;

            case "Thursday":
                day = 4;
                break;

            case "Friday":
                day = 5;
                break;

            case "Saturday":
                day = 6;
                break;
            }

            string sql = " SELECT b.PropertyValue FROM programproperty a, programpropertyvalue b " +
                         " WHERE a.ProgramTypeID=b.ProgramTypeID AND a.PropertyID=b.PropertyID AND " +
                         " a.ProgramTypeID=3 AND a.PropertyID=7 AND b.PropertyValue=" + day;
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                reader.Close();
                return(true);
            }
            else
            {
                reader.Close();
                sql = " SELECT b.PropertyValue FROM programproperty a, programpropertyvalue b " +
                      " WHERE a.ProgramTypeID=b.ProgramTypeID AND a.PropertyID=b.PropertyID AND " +
                      " a.ProgramTypeID=3 AND a.PropertyID=7 ";
                reader = dbUtil.sqlRetrive(sql, conn);
                if (reader.Read())
                {
                    if (reader["PropertyValue"] != DBNull.Value)
                    {
                        if (reader["PropertyValue"].ToString() == "-1")
                        {
                            reader.Close();
                            return(false);
                        }
                        else
                        {
                            reader.Close();
                        }
                    }
                    else
                    {
                        reader.Close();
                    }
                }
                else
                {
                    reader.Close();
                }
            }
            return(false);
        }
예제 #26
0
        public int GetVendor(CDBUtil dbUtil, MySqlConnection conn, int vendorId)
        {
            string          sql    = "SELECT * FROM vendors WHERE VendorID=" + vendorId + " AND Deleted=0";
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
            int             i      = 0;

            while (reader.Read())
            {
                if (reader["VendorID"] != DBNull.Value)
                {
                    VendorId = reader.GetInt32("VendorID");
                }
                if (reader["VendorGroupID"] != DBNull.Value)
                {
                    VendorGroupId = reader.GetInt32("VendorGroupID");
                }
                if (reader["ShopID"] != DBNull.Value)
                {
                    ShopId = reader.GetInt32("ShopID");
                }
                if (reader["VendorName"] != DBNull.Value)
                {
                    VendorName = reader.GetString("VendorName");
                }
                if (reader["VendorCode"] != DBNull.Value)
                {
                    VendorCode = reader.GetString("VendorCode");
                }
                if (reader["VendorFirstName"] != DBNull.Value)
                {
                    VendorFirstName = reader.GetString("VendorFirstName");
                }
                if (reader["VendorLastName"] != DBNull.Value)
                {
                    VendorLastName = reader.GetString("VendorLastName");
                }
                if (reader["VendorAddress1"] != DBNull.Value)
                {
                    VendorAddress1 = reader.GetString("VendorAddress1");
                }
                if (reader["VendorCity"] != DBNull.Value)
                {
                    VendorCity = reader.GetString("VendorCity");
                }
                if (reader["VendorProvince"] != DBNull.Value)
                {
                    VendorProvice = reader.GetInt32("VendorProvince");
                }
                if (reader["VendorZipCode"] != DBNull.Value)
                {
                    VendorZipCode = reader.GetString("VendorZipCode");
                }
                if (reader["VendorTelephone"] != DBNull.Value)
                {
                    VendorTel = reader.GetString("VendorTelephone");
                }
                if (reader["VendorMobile"] != DBNull.Value)
                {
                    VendorMobile = reader.GetString("VendorMobile");
                }
                if (reader["VendorFax"] != DBNull.Value)
                {
                    VendorFax = reader.GetString("VendorFax");
                }
                if (reader["VendorEmail"] != DBNull.Value)
                {
                    VendorEmail = reader.GetString("VendorEmail");
                }
                if (reader["VendorAdditional"] != DBNull.Value)
                {
                    VendorAdditional = reader.GetString("VendorAdditional");
                }
                if (reader["InsertDate"] != DBNull.Value)
                {
                    InsertDate = reader.GetDateTime("InsertDate");
                }
                if (reader["InputBy"] != DBNull.Value)
                {
                    InputBy = reader.GetInt32("InputBy");
                }
                if (reader["UpdateDate"] != DBNull.Value)
                {
                    UpdateDate = reader.GetDateTime("UpdateDate");
                }
                if (reader["VendorTermOfPayment"] != DBNull.Value)
                {
                    VendorTermOfPayMent = reader.GetInt32("VendorTermOfPayment");
                }
                if (reader["VendorCreditDay"] != DBNull.Value)
                {
                    VendorCreditDay = reader.GetInt32("VendorCreditDay");
                }
                if (reader["DefaultTaxType"] != DBNull.Value)
                {
                    DefaultTaxType = reader.GetInt32("DefaultTaxType");
                }
                i++;
            }
            reader.Close();
            return(i);
        }
예제 #27
0
        public virtual bool AddDocument(CDBUtil dbUtil, MySqlConnection conn)
        {
            string sql = "";

            if (this.DocumentTypeId == 7 || this.DocumentTypeId == 10)
            {
                sql = " SELECT * FROM document WHERE ShopID=" + ShopId +
                      " AND DocumentTypeID = " + DocumentTypeId + " AND DocumentYear=" +
                      this.DocumentYear + " AND DocumentMonth = " + this.DocumentMonth +
                      " AND DocumentStatus=1";
            }
            else if (this.DocumentTypeId == 57)
            {
                StockCountWeeklyDoro scd = new StockCountWeeklyDoro(conn, ShopId, 57);

                string dateFrom = scd.SundayDate();

                sql = " SELECT * FROM document \n" +
                      " WHERE ShopID=" + ShopId + " AND DocumentTypeID=" + DocumentTypeId + "\n" +
                      " AND DocumentDate BETWEEN '" + dateFrom + "' AND \n" +
                      " '" + DateTime.Now.ToString("yyyy-MM-dd", dateProvider) + "' \n" +
                      " AND DocumentStatus=1";
            }
            else
            {
                sql = " SELECT * FROM document WHERE ShopID=" + ShopId +
                      " AND DocumentTypeID = " + DocumentTypeId +
                      " AND DocumentStatus=1  AND DocumentDate=" +
                      " {d '" + this.DocumentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "'} " +
                      " AND DocumentStatus=1";
            }
            MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);

            if (reader.Read())
            {
                // Add Docdetail
                if (reader["DocumentID"] != DBNull.Value)
                {
                    DocumentId = Convert.ToInt32(reader["DocumentID"]);
                }
                if (reader["Remark"] != DBNull.Value)
                {
                    Remark = reader.GetString("Remark");
                }

                reader.Close();

                sql = "UPDATE document SET UpdateDate='" + DateTime.Now.ToString("yyyy'-'MM'-'dd HH':'mm':'ss", dateProvider) + "', " +
                      " Remark = '" + Remark + "' WHERE DocumentID=" + DocumentId + " AND ShopID=" + ShopId;
                dbUtil.sqlExecute(sql, conn);

                sql = "DELETE FROM docdetail WHERE DocumentID=" + DocumentId + " AND ShopID=" + ShopId;
                dbUtil.sqlExecute(sql, conn);

                sql = "DELETE FROM docdetailtemp WHERE DocumentID=" + DocumentId + " AND ShopID=" + ShopId;
                dbUtil.sqlExecute(sql, conn);

                return(true);
            }
            else
            {
                reader.Close();
                sql = " INSERT INTO document (DocumentID, ShopID, " +
                      " DocumentTypeID, DocumentYear, DocumentMonth, DocumentNumber, DocumentDate, InputBy, " +
                      " UpdateBy, DocumentStatus, Remark, DocumentIDRef, DocIDRefShopID, ProductLevelID, InsertDate, UpdateDate) " +
                      " VALUES(" + DocumentId + ", " + ShopId + ", " + DocumentTypeId + ", " +
                      DocumentYear + ", " + DocumentMonth + ", " + DocumentNumber + ", '" +
                      DocumentDate.ToString("yyyy'-'MM'-'dd", dateProvider) + "', " +
                      InputBy + ", " + UpdateBy + ", 1, '" + Remark + "', " + DocumentIdRef + ", " + DocIdRefShopId + ", " + ProductLevelId + ", '" +
                      DateTime.Now.ToString("yyyy'-'MM'-'dd HH':'mm':'ss", dateProvider) + "', '" +
                      DateTime.Now.ToString("yyyy'-'MM'-'dd HH':'mm':'ss", dateProvider) + "')";

                try
                {
                    int exec = dbUtil.sqlExecute(sql, conn);
                    if (exec > 0)
                    {
                        // add maxdocumentnumber
                        AddMaxDocumentNumber(dbUtil, conn);
                        return(true);
                    }
                }
                catch
                {
                    return(false);
                }
            }
            return(false);
        }
예제 #28
0
        public List <Vendors> GetVendors(CDBUtil dbUtil, MySqlConnection conn, int vendorGroupId)
        {
            string strSql = "SELECT a.*, b.*, c.ProductLevelName FROM vendors a JOIN vendorgroup b " +
                            " ON (a.VendorGroupID=b.VendorGroupID AND a.ShopID=b.ShopID) " +
                            " JOIN productlevel c ON (a.ShopID=c.ProductLevelID) WHERE a.Deleted=0 and b.Deleted=0";

            if (vendorGroupId > 0)
            {
                strSql += " AND b.VendorGroupID=" + vendorGroupId + " AND b.ShopID=" + ShopId;
            }
            //if(ShopId != 1) strSql += " AND a.ShopID = " + ShopId; // hq เห็นหมด

            MySqlDataReader reader     = dbUtil.sqlRetrive(strSql, conn);
            List <Vendors>  listVendor = new List <Vendors>();

            while (reader.Read())
            {
                Vendors vendor = new Vendors();
                if (reader["VendorID"] != DBNull.Value)
                {
                    vendor.VendorId = reader.GetInt32("VendorID");
                }
                if (reader["VendorGroupID"] != DBNull.Value)
                {
                    vendor.VendorGroupId = reader.GetInt32("VendorGroupID");
                }
                if (reader["ShopID"] != DBNull.Value)
                {
                    vendor.ShopId = reader.GetInt32("ShopID");
                }
                if (reader["VendorCode"] != DBNull.Value)
                {
                    vendor.VendorCode = reader.GetString("VendorCode");
                }
                if (reader["VendorName"] != DBNull.Value)
                {
                    vendor.VendorName = reader.GetString("VendorName");
                }
                if (reader["VendorFirstName"] != DBNull.Value)
                {
                    vendor.VendorFirstName = reader.GetString("VendorFirstName");
                }
                if (reader["VendorLastName"] != DBNull.Value)
                {
                    vendor.VendorLastName = reader.GetString("VendorLastName");
                }
                if (reader["VendorAddress1"] != DBNull.Value)
                {
                    vendor.VendorAddress1 = reader.GetString("VendorAddress1");
                }
                if (reader["VendorAddress2"] != DBNull.Value)
                {
                    vendor.VendorAddress2 = reader.GetString("VendorAddress2");
                }
                if (reader["VendorCity"] != DBNull.Value)
                {
                    vendor.VendorCity = reader.GetString("VendorCity");
                }
                if (reader["VendorProvince"] != DBNull.Value)
                {
                    vendor.VendorProvice = reader.GetInt32("VendorProvince");
                }
                if (reader["VendorZipCode"] != DBNull.Value)
                {
                    vendor.VendorZipCode = reader.GetString("VendorZipCode");
                }
                if (reader["VendorTelephone"] != DBNull.Value)
                {
                    vendor.VendorTel = reader.GetString("VendorTelephone");
                }
                if (reader["VendorMobile"] != DBNull.Value)
                {
                    vendor.VendorMobile = reader.GetString("VendorMobile");
                }
                if (reader["VendorFax"] != DBNull.Value)
                {
                    vendor.VendorFax = reader.GetString("VendorFax");
                }
                if (reader["VendorEmail"] != DBNull.Value)
                {
                    vendor.VendorEmail = reader.GetString("VendorEmail");
                }
                if (reader["VendorAdditional"] != DBNull.Value)
                {
                    vendor.VendorAdditional = reader.GetString("VendorAdditional");
                }
                if (reader["InsertDate"] != DBNull.Value)
                {
                    vendor.InsertDate = reader.GetDateTime("InsertDate");
                }
                if (reader["VendorFirstName"] != DBNull.Value)
                {
                    vendor.VendorFirstName = reader.GetString("VendorFirstName");
                }
                if (reader["InputBy"] != DBNull.Value)
                {
                    vendor.InputBy = reader.GetInt32("InputBy");
                }
                if (reader["UpdateDate"] != DBNull.Value)
                {
                    vendor.UpdateDate = reader.GetDateTime("UpdateDate");
                }
                if (reader["VendorTermOfPayment"] != DBNull.Value)
                {
                    vendor.VendorTermOfPayMent = reader.GetInt32("VendorTermOfPayment");
                }
                if (reader["VendorCreditDay"] != DBNull.Value)
                {
                    vendor.VendorCreditDay = reader.GetInt32("VendorCreditDay");
                }
                if (reader["VendorGroupCode"] != DBNull.Value)
                {
                    vendor.VendorGroupCode = reader.GetString("VendorGroupCode");
                }
                if (reader["VendorGroupName"] != DBNull.Value)
                {
                    vendor.VendorGroupName = reader.GetString("VendorGroupName");
                }
                if (reader["ProductLevelName"] != DBNull.Value)
                {
                    vendor.VendorInvName = reader.GetString("ProductLevelName");
                }

                listVendor.Add(vendor);
            }
            reader.Close();
            return(listVendor);
        }