/// <summary> /// Get all item location /// </summary> public void GetListLocation() { try { //SQL library PSQL SQL = new PSQL(); string query = string.Empty; listItems = new BindingList <pts_item>(); //Open SQL connection SQL.Open(); //SQL query string query = "select distinct item_location from pts_item order by item_location"; //Execute reader for read database IDataReader reader = SQL.Command(query).ExecuteReader(); query = string.Empty; while (reader.Read()) { //Get an item pts_item outItem = new pts_item { item_location = reader["item_location"].ToString(), }; listItems.Add(outItem); } reader.Close(); //Close SQL connection SQL.Close(); } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// Search list item /// </summary> /// <param name="inItem">input item info search</param> /// <param name="checkType">check item type</param> public void SearchItem(pts_item inItem, bool checkType) { //SQL library PSQL SQL = new PSQL(); string query = string.Empty; listItems = new BindingList <pts_item>(); //Open SQL connection SQL.Open(); //SQL query string query = "SELECT item_cd, item_name, type_id, item_lv, item_location, item_unit, lot_size, wh_qty, wip_qty, "; query += "repair_qty, registration_user_cd, registration_date_time FROM pts_item WHERE 1=1 "; if (!string.IsNullOrEmpty(inItem.item_cd)) { query += "and item_cd = '" + inItem.item_cd + "' "; } if (!string.IsNullOrEmpty(inItem.item_unit)) { query += "and item_unit = '" + inItem.item_unit + "' "; } if (!string.IsNullOrEmpty(inItem.item_location)) { query += "and item_location ='" + inItem.item_location + "' "; } if (checkType) { query += "and type_id = '" + inItem.type_id + "' "; } query += "order by item_cd"; //Execute reader for read database IDataReader reader = SQL.Command(query).ExecuteReader(); query = string.Empty; while (reader.Read()) { //Get an item pts_item outItem = new pts_item { item_cd = reader["item_cd"].ToString(), item_name = reader["item_name"].ToString(), type_id = (int)reader["type_id"], item_lv = (int)reader["item_lv"], item_location = reader["item_location"].ToString(), item_unit = reader["item_unit"].ToString(), lot_size = (double)reader["lot_size"], wh_qty = (double)reader["wh_qty"], wip_qty = (double)reader["wip_qty"], repair_qty = (double)reader["repair_qty"], registration_date_time = (DateTime)reader["registration_date_time"], registration_user_cd = reader["registration_user_cd"].ToString() }; //Add item into list listItems.Add(outItem); } reader.Close(); //Close SQL connection SQL.Close(); }
/// <summary> /// Get infomation of an item with item code /// </summary> /// <param name="itemCD">item code</param> /// <returns></returns> public pts_item GetItem(string itemCD) { try { //SQL library PSQL SQL = new PSQL(); string query = string.Empty; //Open SQL connection SQL.Open(); //SQL query string query = "SELECT item_cd, item_name, type_id, item_lv, item_location, item_unit, lot_size, wh_qty, wip_qty, "; query += "repair_qty, registration_user_cd, registration_date_time FROM pts_item WHERE 1=1 "; query += "and item_cd = '" + itemCD + "' "; query += "order by item_cd"; //Execute reader for read database IDataReader reader = SQL.Command(query).ExecuteReader(); query = string.Empty; reader.Read(); //Get an item pts_item outItem = new pts_item { item_cd = reader["item_cd"].ToString(), item_name = reader["item_name"].ToString(), type_id = (int)reader["type_id"], item_lv = (int)reader["item_lv"], item_location = reader["item_location"].ToString(), item_unit = reader["item_unit"].ToString(), lot_size = (double)reader["lot_size"], wh_qty = (double)reader["wh_qty"], wip_qty = (double)reader["wip_qty"], repair_qty = (double)reader["repair_qty"], registration_date_time = (DateTime)reader["registration_date_time"], registration_user_cd = reader["registration_user_cd"].ToString() }; reader.Close(); //Close SQL connection SQL.Close(); return(outItem); } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// Add an item into database /// </summary> /// <param name="inItem"></param> /// <returns></returns> public int AddItem(pts_item inItem) { //SQL library PSQL SQL = new PSQL(); string query = string.Empty; //Open SQL connection SQL.Open(); //SQL query string query = "INSERT INTO pts_item(type_id, item_cd, item_name, item_location, item_unit, lot_size, registration_user_cd) "; query += "VALUES ('" + inItem.type_id + "','" + inItem.item_cd + "','" + inItem.item_name + "','"; query += inItem.item_location + "','" + inItem.item_unit + "','" + inItem.lot_size + "','"; query += inItem.registration_user_cd + "')"; //Execute non query for read database int result = SQL.Command(query).ExecuteNonQuery(); query = string.Empty; return(result); }
/// <summary> /// Update an item /// </summary> /// <param name="inItem"></param> /// <returns></returns> public int Update(pts_item inItem) { //SQL library PSQL SQL = new PSQL(); string query = string.Empty; //Open SQL connection SQL.Open(); //SQL query string query = "UPDATE pts_item SET type_id='" + inItem.type_id + "', item_cd='" + inItem.item_cd + "', item_name='"; query += inItem.item_name + "', item_location='" + inItem.item_location + "', item_unit='" + inItem.item_unit; query += "', lot_size ='" + inItem.lot_size + "', wh_qty ='" + inItem.wh_qty + "', wip_qty ='" + inItem.wip_qty; query += "', repair_qty ='" + inItem.repair_qty + "', registration_user_cd ='" + inItem.registration_user_cd; query += "', registration_date_time = now() where item_id ='" + inItem.item_id + "'"; //Execute non query for read database int result = SQL.Command(query).ExecuteNonQuery(); query = string.Empty; return(result); }