/// <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_history GetItem(pts_stock_history inItem, bool checkDate) { //SQL library PSQL SQL = new PSQL(); string query = string.Empty; //Open SQL connection SQL.Open(); //SQL query string query = "SELECT stock_history_id, stock_id, packing_cd, item_cd, supplier_cd, order_no, invoice,po_no, stockin_date, "; query += "stockin_user_cd, stockin_qty, packing_qty, registration_user_cd, registration_date_time FROM pts_stock_history "; query += "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 = '" + 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_history outItem = new pts_stock_history { stock_history_id = (int)reader["stock_history_id"], 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(), po_no = reader["po_no"].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); }
/// <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_history inItem, DateTime fromDate, DateTime toDate, bool checkDate) { try { //SQL library PSQL SQL = new PSQL(); string query = string.Empty; listStockItems = new BindingList <pts_stock_history>(); //Open SQL connection SQL.Open(); //SQL query string query = "SELECT stock_history_id, stock_id, packing_cd, item_cd, supplier_cd, order_no, invoice,po_no, stockin_date, "; query += "stockin_user_cd, stockin_qty, packing_qty, registration_user_cd, registration_date_time FROM pts_stock_history "; query += "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 + "' "; } query += "ORDER BY stock_id"; //Execute reader for read database IDataReader reader = SQL.Command(query).ExecuteReader(); while (reader.Read()) { pts_stock_history outItem = new pts_stock_history { stock_history_id = (int)reader["stock_history_id"], 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(), po_no = reader["po_no"].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); } }