/// <summary>
/// Log In and Log Out
/// </summary>
/// <param name="usercd">User code</param>
/// <param name="isLogin">true: log in, false: log out</param>
public bool LogIO(string usercd, bool isLogin)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = @"UPDATE m_login_password SET ";
if (isLogin)
{
query += "last_login_time='" + DateTime.Now + "', is_online = '1' ";
}
else
{
query += "is_online ='0' ";
}
query += "WHERE user_cd ='" + usercd + "'";
//Execute query
int result = SQL.Command(query).ExecuteNonQuery();
//Close SQL connection
SQL.Close();
if (result > 0)
{
return(isLogin);
}
else
{
return(!isLogin);
}
}
/// <summary>
/// Get user infomation with department and position
/// </summary>
/// <param name="dept_code"></param>
/// <param name="postion_code"></param>
/// <returns></returns>
public m_mes_user GetPositionUser(string dept_code, string postion_code)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = @"SELECT user_cd, user_name, locale_id, multi_login_flag, registration_user_cd, registration_date_time,
registrated_factory_cd, dept_cd, user_position_cd FROM m_mes_user ";
query += "WHERE dept_cd ='" + dept_code + "' and user_position_cd ='" + postion_code + "'";
//Execute reader for read database
IDataReader reader = SQL.Command(query).ExecuteReader();
query = string.Empty;
reader.Read();
//Get an item
m_mes_user outItem = new m_mes_user
{
user_cd = reader["user_cd"].ToString(),
user_name = reader["user_name"].ToString(),
locale_id = (int)reader["locale_id"],
multi_login_flag = reader["multi_login_flag"].ToString(),
registration_date_time = (DateTime)reader["registration_date_time"],
registration_user_cd = reader["registration_user_cd"].ToString(),
dept_cd = reader["dept_cd"].ToString(),
user_position_cd = reader["user_position_cd"].ToString(),
};
reader.Close();
//Close SQL connection
SQL.Close();
return(outItem);
}
/// <summary>
/// Change password of user
/// </summary>
/// <param name="inItem">input new info of user</param>
/// <returns></returns>
public bool ChangePassword(m_login_password inItem)
{
EncryptDecrypt endecrypt = new EncryptDecrypt();
string pass = endecrypt.Encrypt(inItem.password);
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "UPDATE m_login_password SET password ='******' WHERE user_cd ='" + inItem.user_cd + "' ";
//Execute non query
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
SQL.Close();
if (result > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Get list all issue code
/// </summary>
public void GetListIssueCode()
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
listIssueCode = new List <pts_issue_code>();
//Open SQL connection
SQL.Open();
//SQL query string
query = "select * from pts_issue_code where 1=1 ";
query += "order by issue_cd";
//Execute reader for read database
IDataReader reader = SQL.Command(query).ExecuteReader();
query = string.Empty;
while (reader.Read())
{
//Get an item
pts_issue_code outItem = new pts_issue_code
{
issue_cd = (int)reader["issue_cd"],
issue_name = reader["issue_name"].ToString(),
registration_date_time = (DateTime)reader["registration_date_time"],
registration_user_cd = reader["registration_user_cd"].ToString()
};
//Add item into list
listIssueCode.Add(outItem);
}
reader.Close();
//Close SQL connection
SQL.Close();
}
/// <summary>
/// Get infomation of an issue code
/// </summary>
/// <param name="issue_code">issue code need get infomation</param>
public pts_issue_code GetIssueCode(int issue_code)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "select * from pts_issue_code where 1=1 ";
query += "and issue_cd = '" + issue_code + "' ";
query += "order by issue_cd";
//Execute reader for read database
IDataReader reader = SQL.Command(query).ExecuteReader();
query = string.Empty;
//Get an item
reader.Read();
pts_issue_code outItem = new pts_issue_code
{
issue_cd = (int)reader["issue_cd"],
issue_name = reader["issue_name"].ToString(),
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);
}
/// <summary>
/// Get all department
/// </summary>
public void GetListDepartment()
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
listDept = new BindingList <m_department>();
//Open SQL connection
SQL.Open();
//SQL query string
query = "select dept_id, dept_cd, dept_name, registration_user_cd, registration_date_time from m_department ";
query += "order by dept_cd";
//Execute reader for read database
IDataReader reader = SQL.Command(query).ExecuteReader();
query = string.Empty;
while (reader.Read())
{
//Get an item
m_department outItem = new m_department
{
dept_id = (int)reader["dept_id"],
dept_cd = reader["dept_cd"].ToString(),
dept_name = reader["dept_name"].ToString(),
registration_date_time = (DateTime)reader["registration_date_time"],
registration_user_cd = reader["registration_user_cd"].ToString()
};
//Add item into list
listDept.Add(outItem);
}
reader.Close();
//Close SQL connection
SQL.Close();
}
public int UpdateMultiItem(List <pts_stock> inList)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
int result = 0;
//SQL query string
foreach (pts_stock inItem in inList)
{
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
result += SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
}
SQL.Close();
return(result);
}
/// <summary>
/// Update W/H qty of list item
/// </summary>
/// <param name="inList"></param>
/// <returns></returns>
public int ListStockOutUpdateValue(List <OutputItem> inList)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
string listItemCD = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "update pts_item set wh_qty = case item_cd ";
for (int i = 0; i < inList.Count; i++)
{
query += "when '" + inList[i].item_number + "' then wh_qty - " + inList[i].delivery_qty + " ";
listItemCD += inList[i].item_number + "','";
}
listItemCD = listItemCD.Remove(listItemCD.Length - 2);
query += "end where item_cd in ('" + listItemCD + ")";
//Execute non query for read database
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
SQL.Close();
return(result);
}
public int AddMultiItem(List <pts_stockout_log> inList)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "INSERT INTO pts_stockout_log(packing_cd, process_cd, issue_cd, stockout_date, stockout_user_cd, ";
query += "stockout_qty, real_qty, received_user_cd, comment, remark) VALUES ";
foreach (pts_stockout_log inItem in inList)
{
query += "('" + inItem.packing_cd + "','" + inItem.process_cd + "','" + inItem.issue_cd + "','" + inItem.stockout_date;
query += "','" + inItem.stockout_user_cd + "','" + inItem.stockout_qty + "','" + inItem.real_qty + "','";
query += inItem.received_user_cd + "','" + inItem.comment + "','" + inItem.remark + "'),";
}
query = query.Remove(query.Length - 1);
//Execute non query for read database
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
SQL.Close();
return(result);
}
/// <summary>
/// Delete an item
/// </summary>
/// <param name="id">item id</param>
/// <returns></returns>
public int Delete(string itemcd)
{
try
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "DELETE FROM pts_item WHERE item_cd ='" + itemcd + "'";
//Execute non query for read database
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
SQL.Close();
return(result);
}
catch (Exception ex)
{
if (ex.Message.Split(':')[0] == "23503")
{
throw new Exception("This item is used on another table. Please check and try again!");
}
else
{
throw new Exception(ex.Message);
}
}
}
/// <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>
/// Get destination code from supplier
/// </summary>
/// <returns></returns>
public List <pts_destination> GetListDestination()
{
List <pts_destination> outList = new List <pts_destination>();
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "SELECT supplier_cd, supplier_name FROM pts_supplier WHERE supplier_cd like '999%' ORDER BY supplier_cd";
//Execute non query for read database
IDataReader reader = SQL.Command(query).ExecuteReader();
query = string.Empty;
while (reader.Read())
{
pts_destination outItem = new pts_destination()
{
dept_cd = "Other",
destination_cd = reader["supplier_cd"].ToString(),
destination_name = reader["supplier_name"].ToString(),
registration_user_cd = UserData.usercode,
registration_date_time = DateTime.Now
};
outList.Add(outItem);
}
reader.Close();
SQL.Close();
return(outList);
}
public double GetStockOutQty(string processCD, string itemCD)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "SELECT sum(stockout_qty) FROM pts_stockout_log WHERE ";
query += "process_cd like '" + processCD + "%' ";
query += "and packing_cd in(select packing_cd from pts_stock where item_cd ='" + itemCD + "') ";
//Execute reader for read database
object result = SQL.Command(query).ExecuteScalar();
SQL.Close();
if (result is System.DBNull)
{
return(0);
}
else
{
return((double)result);
}
}
/// <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>
/// 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);
}
public void Search(pts_noplan inItem)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "SELECT noplan_id, noplan_cd, destination_cd, item_cd, noplan_qty, noplan_usercd, noplan_date ";
query += "FROM pts_noplan WHERE 1=1 ";
if (!string.IsNullOrEmpty(inItem.noplan_cd))
{
query += "AND noplan_cd ='" + inItem.noplan_cd + "' ";
}
if (!string.IsNullOrEmpty(inItem.destination_cd))
{
query += "AND destination_cd ='" + inItem.destination_cd + "' ";
}
if (!string.IsNullOrEmpty(inItem.item_cd))
{
query += "AND item_cd ='" + inItem.item_cd + "' ";
}
if (!string.IsNullOrEmpty(inItem.noplan_usercd))
{
query += "AND noplan_usercd ='" + inItem.noplan_usercd + "' ";
}
query += "ORDER BY noplan_id";
//Execute reader for read database
IDataReader reader = SQL.Command(query).ExecuteReader();
//Clear query
query = string.Empty;
//Read data into list
while (reader.Read())
{
pts_noplan outItem = new pts_noplan
{
noplan_id = (int)reader["noplan_id"],
noplan_cd = reader["noplan_cd"].ToString(),
destination_cd = reader["destination_cd"].ToString(),
item_cd = reader["item_cd"].ToString(),
noplan_qty = (double)reader["noplan_qty"],
noplan_usercd = reader["noplan_usercd"].ToString(),
noplan_date = (DateTime)reader["noplan_date"],
};
listNoPlan.Add(outItem);
}
//Close reader and connection
reader.Close();
SQL.Close();
}
/// <summary>
/// Search list log
/// </summary>
/// <param name="inItem">input info</param>
/// <param name="fromDate"></param>
/// <param name="toDate"></param>
/// <param name="checkDate">check date for search or no</param>
public void Search(pts_stock_log inItem, DateTime fromDate, DateTime toDate, bool checkDate)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "SELECT log_id, log_date, log_action, log_user_cd, stock_id, stock_field, before_value, after_value ";
query += "FROM pts_stock_log WHERE 1=1 ";
if (checkDate)
{
query += "AND log_date >= '" + fromDate.ToString("yyyy-MM--dd HH:mm:ss") + "' ";
query += "AND log_date <= '" + toDate.ToString("yyyy-MM--dd HH:mm:ss") + "' ";
}
if (!string.IsNullOrEmpty(inItem.log_action))
{
query += "AND log_action ='" + inItem.log_action + "' ";
}
if (!string.IsNullOrEmpty(inItem.log_user_cd))
{
query += "AND log_user_cd ='" + inItem.log_user_cd + "' ";
}
if (!string.IsNullOrEmpty(inItem.stock_field))
{
query += "AND stock_field ='" + inItem.stock_field + "' ";
}
query += "ORDER BY log_id";
//Execute reader for read database
IDataReader reader = SQL.Command(query).ExecuteReader();
while (reader.Read())
{
pts_stock_log outItem = new pts_stock_log
{
log_id = (int)reader["log_id"],
log_date = (DateTime)reader["log_date"],
log_action = reader["log_action"].ToString(),
log_user_cd = reader["log_user_cd"].ToString(),
stock_id = reader["stock_id"].ToString(),
stock_field = reader["stock_field"].ToString(),
before_value = reader["before_value"].ToString(),
after_value = reader["after_value"].ToString(),
};
listStockLog.Add(outItem);
}
query = string.Empty;
SQL.Close();
}
/// <summary>
/// Delete an item type
/// </summary>
/// <param name="id">item type id</param>
/// <returns></returns>
public int Delete(int id)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "DELETE FROM pts_item_type WHERE type_id ='" + id + "'";
//Execute non query for read database
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
return(result);
}
public bool CheckOnline(string usercd)
{
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "SELECT is_online FROM m_login_password WHERE user_cd ='" + usercd + "'";
//Exectute scalar
bool result = (bool)SQL.Command(query).ExecuteScalar();
query = string.Empty;
SQL.Close();
return(result);
}
/// <summary>
/// Search list premac item from DB
/// </summary>
/// <param name="inItem">search info</param>
public void Search(pre_649_order inItem, DateTime fromdate, DateTime todate, bool checkdate)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
listOrderItem = new List <pre_649_order>();
//Open SQL connection
SQL.Open();
//SQL query string
query = "SELECT item_number, order_number, order_qty, supplier_cd, order_date ";
query += "FROM pre_649_order WHERE 1=1 ";
if (!string.IsNullOrEmpty(inItem.item_number))
{
query += "AND item_number ='" + inItem.item_number + "' ";
}
if (!string.IsNullOrEmpty(inItem.order_number))
{
query += "AND order_number ='" + inItem.order_number + "' ";
}
if (checkdate)
{
query += "AND order_date >='" + fromdate + "' AND order_date <='" + todate + "' ";
}
query += "ORDER BY item_number, order_date";
//Execute reader for read database
IDataReader reader = SQL.Command(query).ExecuteReader();
query = string.Empty;
while (reader.Read())
{
//Get an item
pre_649_order outItem = new pre_649_order
{
item_number = reader["item_number"].ToString(),
order_number = reader["order_number"].ToString(),
order_qty = (double)reader["order_qty"],
supplier_cd = reader["supplier_cd"].ToString(),
order_date = (DateTime)reader["order_date"],
};
//Add item into list
listOrderItem.Add(outItem);
}
reader.Close();
//Close SQL connection
SQL.Close();
}
/// <summary>
/// Delete an item
/// </summary>
/// <param name="inItem">Input item</param>
/// <returns>Number item is deleted</returns>
public int DeleteItem(pts_stockout_log inItem)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "DELETE FROM pts_stockout_log WHERE stockout_id ='" + inItem.stockout_id + "'";
//Execute non query for read database
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
SQL.Close();
return(result);
}
public int DeleteDept(int id)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "DELETE FROM m_department WHERE dept_id ='" + id + "'";
//Execute non query for read database
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
SQL.Close();
return(result);
}
public int UpdateIssueCode(pts_issue_code UpdateIssueCd)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "UPDATE pts_issue_code SET issue_cd='" + UpdateIssueCd.issue_cd + "', issue_name='" + UpdateIssueCd.issue_name + "',registration_user_cd ='" + UpdateIssueCd.registration_user_cd;
query += "', registration_date_time = now() where issue_cd ='" + UpdateIssueCd.issue_cd + "'";
//Execute non query for read database
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
return(result);
}
public int AddDestination(pts_destination adddest)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "INSERT INTO pts_destination(destination_cd, destination_name, dept_cd, registration_user_cd)";
query += "VALUES ('" + adddest.destination_cd + "','" + adddest.destination_name + "','" + adddest.dept_cd + "','" + adddest.registration_user_cd + "')";
//Execute non query for read database
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
return(result);
}
/// <summary>
/// Delete issue code
/// </summary>
/// <param name="issue"></param>
/// <returns></returns>
public int Delete(int issue)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "DELETE FROM pts_issue_code WHERE issue_cd ='" + issue + "'";
//Execute non query for read database
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
SQL.Close();
return(result);
}
/// <summary>
/// Delete this plan
/// </summary>
/// <returns></returns>
public int Delete()
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "DELETE FROM pts_plan WHERE plan_id ='" + plan_id + "'";
//Execute non query SQL
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
SQL.Close();
return(result);
}
/// <summary>
/// Add an item type
/// </summary>
/// <param name="inItem">new item type</param>
/// <returns></returns>
public int AddItemType(pts_item_type 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(type_id, type_name, registration_user_cd)";
query += "VALUES ('" + inItem.type_id + "','" + inItem.type_name + "','" + inItem.registration_user_cd + "')";
//Execute non query for read database
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
return(result);
}
/// <summary>
/// Get a supplier
/// </summary>
/// <param name="inItem">input info supplier to search</param>
/// <returns></returns>
public pts_supplier GetSupplier(pts_supplier inItem)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "select supplier_id, supplier_cd, supplier_name, supplier_tel, supplier_fax, supplier_address, registration_date_time,registration_user_cd ";
query += "from pts_supplier where 1=1 ";
if (inItem.supplier_id > 0)
{
query += "and supplier_id ='" + inItem.supplier_id + "' ";
}
if (!string.IsNullOrEmpty(inItem.supplier_cd))
{
query += "and supplier_cd ='" + inItem.supplier_cd + "' ";
}
if (!string.IsNullOrEmpty(inItem.supplier_name))
{
query += "and supplier_name ='" + inItem.supplier_name + "' ";
}
//Execute reader for read database
IDataReader reader = SQL.Command(query).ExecuteReader();
reader.Read();
pts_supplier outItem = new pts_supplier
{
supplier_id = (int)reader["supplier_id"],
supplier_cd = reader["supplier_cd"].ToString(),
supplier_name = reader["supplier_name"].ToString(),
supplier_tel = reader["supplier_tel"].ToString(),
supplier_fax = reader["supplier_fax"].ToString(),
supplier_address = reader["supplier_address"].ToString(),
registration_date_time = (DateTime)reader["registration_date_time"],
registration_user_cd = reader["registration_user_cd"].ToString()
};
reader.Close();
query = string.Empty;
//Close SQL connection
SQL.Close();
return(outItem);
}
/// <summary>
/// Delete an user postition
/// </summary>
/// <param name="inItem">input user position</param>
/// <returns></returns>
public int Delete(m_user_position inItem)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "DELETE FROM m_user_position WHERE user_position_id ='" + inItem.user_position_id + "'";
//Execute non query for add item
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
//Close SQL connection
SQL.Close();
return(result);
}
/// <summary>
/// Update an item type
/// </summary>
/// <param name="inItem">input item type</param>
/// <returns></returns>
public int Update(pts_item_type inItem)
{
//SQL library
PSQL SQL = new PSQL();
string query = string.Empty;
//Open SQL connection
SQL.Open();
//SQL query string
query = "UPDATE pts_item_type SET type_id='" + inItem.type_id + "', type_name='" + inItem.type_name;
query += "', registration_user_cd ='" + inItem.registration_user_cd;
query += "', registration_date_time = now() where type_id ='" + inItem.type_id + "'";
//Execute non query for read database
int result = SQL.Command(query).ExecuteNonQuery();
query = string.Empty;
return(result);
}