Exemplo n.º 1
0
        /// <summary>
        /// Sum stock qty
        /// </summary>
        /// <param name="inItem">input info stock for search</param>
        /// <param name="fromDate"></param>
        /// <param name="toDate"></param>
        /// <param name="checkDate">check date stockin for search</param>
        /// <param name="isStockIn">true: stock in qty, false: packing qty</param>
        /// <returns></returns>
        public double SumStockQty(pts_stock inItem, DateTime fromDate, DateTime toDate, bool checkDate, bool isStockIn)
        {
            //SQL library
            PSQL   SQL   = new PSQL();
            string query = string.Empty;

            //Open SQL connection
            SQL.Open();
            //SQL query string
            if (isStockIn)
            {
                query = "SELECT SUM(stockin_qty) FROM pts_stock WHERE 1=1 ";
            }
            else
            {
                query = "SELECT SUM(packing_qty) FROM pts_stock WHERE 1=1 ";
            }
            if (!string.IsNullOrEmpty(inItem.packing_cd))
            {
                query += "AND packing_cd ='" + inItem.packing_cd + "' ";
            }
            if (!string.IsNullOrEmpty(inItem.item_cd))
            {
                query += "AND item_cd ='" + inItem.item_cd + "' ";
            }
            if (!string.IsNullOrEmpty(inItem.supplier_cd))
            {
                query += "AND supplier_cd ='" + inItem.supplier_cd + "' ";
            }
            if (!string.IsNullOrEmpty(inItem.order_no))
            {
                query += "AND order_no ='" + inItem.order_no + "' ";
            }
            if (!string.IsNullOrEmpty(inItem.invoice))
            {
                query += "AND invoice ='" + inItem.invoice + "' ";
            }
            if (!string.IsNullOrEmpty(inItem.po_no))
            {
                query += "AND po_no ='" + inItem.po_no + "' ";
            }
            if (checkDate)
            {
                query += "AND stockin_date >= '" + fromDate.ToString("yyyy-MM-dd") + "' ";
                query += "AND stockin_date <= '" + toDate.ToString("yyyy-MM-dd") + "' ";
            }
            if (!string.IsNullOrEmpty(inItem.stockin_user_cd))
            {
                query += "AND stockin_user_cd ='" + inItem.stockin_user_cd + "' ";
            }
            //Execute non query for read database
            double result = (double)SQL.Command(query).ExecuteScalar();

            query = string.Empty;
            SQL.Close();
            return(result);
        }
Exemplo n.º 2
0
        /// <summary>
        /// Update stock item
        /// </summary>
        /// <param name="inItem">stock item after change</param>
        /// <returns></returns>
        public int UpdateItem(pts_stock inItem)
        {
            //SQL library
            PSQL   SQL   = new PSQL();
            string query = string.Empty;

            //Open SQL connection
            SQL.Open();
            //SQL query string
            query  = "UPDATE pts_stock SET packing_cd ='" + inItem.packing_cd + "', item_cd ='" + inItem.item_cd + "', supplier_cd ='";
            query += inItem.supplier_cd + "', order_no ='" + inItem.order_no + "', invoice ='" + inItem.invoice;
            query += "', stockin_date ='" + inItem.stockin_date + "', stockin_user_cd ='" + inItem.stockin_user_cd;
            query += "', stockin_qty ='" + inItem.stockin_qty + "', packing_qty ='" + inItem.packing_qty + "', registration_user_cd ='";
            query += inItem.registration_user_cd + "' WHERE stock_id ='" + inItem.stock_id + "'";
            //Execute non query for read database
            int result = SQL.Command(query).ExecuteNonQuery();

            query = string.Empty;
            SQL.Close();
            return(result);
        }
Exemplo n.º 3
0
        /// <summary>
        /// Add new stock item
        /// </summary>
        /// <param name="inItem">input stock item</param>
        /// <returns></returns>
        public int AddItem(pts_stock inItem)
        {
            //SQL library
            PSQL   SQL   = new PSQL();
            string query = string.Empty;

            //Open SQL connection
            SQL.Open();
            //SQL query string
            query  = "INSERT INTO pts_stock(packing_cd, item_cd, supplier_cd, order_no, invoice, stockin_date,";
            query += "stockin_user_cd, stockin_qty, packing_qty, registration_user_cd) VALUES ";
            query += "('" + inItem.packing_cd + "','" + inItem.item_cd + "','" + inItem.supplier_cd + "','" + inItem.order_no;
            query += "','" + inItem.invoice + "','" + inItem.stockin_date + "','" + inItem.stockin_user_cd + "','";
            query += inItem.stockin_qty + "','" + inItem.packing_qty + "','" + inItem.registration_user_cd + "')";
            //Execute non query for read database
            int result = SQL.Command(query).ExecuteNonQuery();

            query = string.Empty;
            SQL.Close();
            return(result);
        }
Exemplo n.º 4
0
        /// <summary>
        /// Get an stock item
        /// </summary>
        /// <param name="inItem">input info stock item search</param>
        /// <param name="checkDate">check stock in date</param>
        /// <returns></returns>
        public pts_stock GetItem(pts_stock inItem, bool checkDate)
        {
            //SQL library
            PSQL   SQL   = new PSQL();
            string query = string.Empty;

            //Open SQL connection
            SQL.Open();
            //SQL query string
            query  = "SELECT stock_id, packing_cd, item_cd, supplier_cd, order_no, invoice, stockin_date, stockin_user_cd, ";
            query += "stockin_qty, packing_qty, registration_user_cd, registration_date_time FROM pts_stock WHERE 1=1 ";
            if (!string.IsNullOrEmpty(inItem.packing_cd))
            {
                query += "AND packing_cd ='" + inItem.packing_cd + "' ";
            }
            if (!string.IsNullOrEmpty(inItem.item_cd))
            {
                query += "AND item_cd ='" + inItem.item_cd + "' ";
            }
            if (!string.IsNullOrEmpty(inItem.supplier_cd))
            {
                query += "AND supplier_cd ='" + inItem.supplier_cd + "' ";
            }
            if (!string.IsNullOrEmpty(inItem.order_no))
            {
                query += "AND order_no ='" + inItem.order_no + "' ";
            }
            if (!string.IsNullOrEmpty(inItem.invoice))
            {
                query += "AND invoice ='" + inItem.invoice + "' ";
            }
            if (checkDate)
            {
                query += "AND stockin_date = '" + inItem.stockin_date.ToString("yyyy-MM-dd") + "' ";
            }
            if (!string.IsNullOrEmpty(inItem.stockin_user_cd))
            {
                query += "AND stockin_user_cd ='" + inItem.stockin_user_cd + "' ";
            }
            query += "ORDER BY stock_id";
            //Execute reader for read database
            IDataReader reader = SQL.Command(query).ExecuteReader();

            reader.Read();
            pts_stock outItem = new pts_stock
            {
                stock_id               = (int)reader["stock_id"],
                packing_cd             = reader["packing_cd"].ToString(),
                item_cd                = reader["item_cd"].ToString(),
                supplier_cd            = reader["supplier_cd"].ToString(),
                order_no               = reader["order_no"].ToString(),
                invoice                = reader["invoice"].ToString(),
                stockin_date           = (DateTime)reader["stockin_date"],
                stockin_user_cd        = reader["stockin_user_cd"].ToString(),
                stockin_qty            = (double)reader["stockin_qty"],
                packing_qty            = (double)reader["packing_qty"],
                registration_user_cd   = reader["registration_user_cd"].ToString(),
                registration_date_time = (DateTime)reader["registration_date_time"],
            };

            reader.Close();
            query = string.Empty;
            //Close connection
            SQL.Close();
            return(outItem);
        }
Exemplo n.º 5
0
 /// <summary>
 /// Search list stock
 /// </summary>
 /// <param name="inItem">input info stock search</param>
 /// <param name="fromDate"></param>
 /// <param name="toDate"></param>
 /// <param name="checkDate">check stock in date</param>
 public bool SearchItem(pts_stock inItem, DateTime fromDate, DateTime toDate, bool checkDate)
 {
     try
     {
         //SQL library
         PSQL   SQL   = new PSQL();
         string query = string.Empty;
         listStockItems = new BindingList <pts_stock>();
         //Open SQL connection
         SQL.Open();
         //SQL query string
         query  = "SELECT stock_id, packing_cd, item_cd, supplier_cd, order_no, invoice, stockin_date, stockin_user_cd, ";
         query += "stockin_qty, packing_qty, registration_user_cd, registration_date_time FROM pts_stock WHERE 1=1 ";
         if (!string.IsNullOrEmpty(inItem.packing_cd))
         {
             query += "AND packing_cd ='" + inItem.packing_cd + "' ";
         }
         if (!string.IsNullOrEmpty(inItem.item_cd))
         {
             query += "AND item_cd ='" + inItem.item_cd + "' ";
         }
         if (!string.IsNullOrEmpty(inItem.supplier_cd))
         {
             query += "AND supplier_cd ='" + inItem.supplier_cd + "' ";
         }
         if (!string.IsNullOrEmpty(inItem.order_no))
         {
             query += "AND order_no ='" + inItem.order_no + "' ";
         }
         if (!string.IsNullOrEmpty(inItem.invoice))
         {
             query += "AND invoice ='" + inItem.invoice + "' ";
         }
         if (checkDate)
         {
             query += "AND stockin_date >= '" + fromDate.ToString("yyyy-MM-dd") + "' ";
             query += "AND stockin_date <= '" + toDate.ToString("yyyy-MM-dd") + "' ";
         }
         if (!string.IsNullOrEmpty(inItem.stockin_user_cd))
         {
             query += "AND stockin_user_cd ='" + inItem.stockin_user_cd + "' ";
         }
         query += "ORDER BY stock_id";
         //Execute reader for read database
         IDataReader reader = SQL.Command(query).ExecuteReader();
         while (reader.Read())
         {
             pts_stock outItem = new pts_stock
             {
                 stock_id               = (int)reader["stock_id"],
                 packing_cd             = reader["packing_cd"].ToString(),
                 item_cd                = reader["item_cd"].ToString(),
                 supplier_cd            = reader["supplier_cd"].ToString(),
                 order_no               = reader["order_no"].ToString(),
                 invoice                = reader["invoice"].ToString(),
                 stockin_date           = (DateTime)reader["stockin_date"],
                 stockin_user_cd        = reader["stockin_user_cd"].ToString(),
                 stockin_qty            = (double)reader["stockin_qty"],
                 packing_qty            = (double)reader["packing_qty"],
                 registration_user_cd   = reader["registration_user_cd"].ToString(),
                 registration_date_time = (DateTime)reader["registration_date_time"],
             };
             listStockItems.Add(outItem);
         }
         reader.Close();
         query = string.Empty;
         //Close connection
         SQL.Close();
         if (listStockItems.Count > 0)
         {
             return(true);
         }
         else
         {
             return(false);
         }
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
 }