/// <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);
}