// fetches document information given document id
public static string FetchDocumentInfo(int doc_id)
{
string qryString = "SELECT DOC_DESC FROM DOCLIST d WHERE d.DOC_ID = @doc_id ORDER BY d.DOC_ID, d.DOC_DESC";
string doc_desc = "";
// create database connection
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
dbmgr.CreateParameters(1);
dbmgr.AddParameters(0, "@doc_id", doc_id);
dbmgr.ExecuteReader(CommandType.Text, qryString);
if (dbmgr.DataReader.Read())
doc_desc = dbmgr.DataReader["DOC_DESC"].ToString();
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbmgr.Dispose();
}
return doc_desc;
}
// fetches list of reports given a report type
// if rpt_type = "ALL" then fetches all reports
public static object FetchReportList(string rpt_type)
{
ArrayList list = new ArrayList();
DBReport item = new DBReport();
string qryString;
PropertyInfo[] p = item.GetType().GetProperties();
if (rpt_type == "ALL")
qryString = "SELECT * FROM REPORTLIST WHERE RPT IS NOT NULL ORDER BY RPT_ID";
else
qryString = "SELECT * FROM REPORTLIST WHERE RPT_TYPE LIKE @rpt_type ORDER BY RPT_ID";
// create database connection
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
dbmgr.CreateParameters(1);
dbmgr.AddParameters(0, "@rpt_type", rpt_type);
dbmgr.ExecuteReader(System.Data.CommandType.Text, qryString);
while (dbmgr.DataReader.Read())
{
item = new DBReport();
item = (DBReport)TotalManager.FetchObject(item, p, dbmgr);
list.Add(item);
}
}
catch (Exception ex)
{
throw (ex);
}
finally
{
dbmgr.Dispose();
}
return list;
}
// fetches report infomration for a given report id
public static DBReport FetchReport(int id)
{
DBReport report = new DBReport();
string qryString;
qryString = "SELECT * FROM REPORTLIST WHERE RPT_ID = @rpt_id";
// create database connection
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
dbmgr.CreateParameters(1);
dbmgr.AddParameters(0, "@rpt_id", id);
dbmgr.ExecuteReader(System.Data.CommandType.Text, qryString);
if (dbmgr.DataReader.Read())
{
// get properties of object and fetch object
PropertyInfo[] p = report.GetType().GetProperties();
report = (DBReport)TotalManager.FetchObject(report, p, dbmgr);
}
}
catch (Exception ex)
{
throw (ex);
}
finally
{
dbmgr.Dispose();
}
return report;
}
// update lists (1) insert, (2) update, (3) delete
public static void SaveComboBoxList(ComboBoxItem item, IList list, ref bool in_use)
{
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
dbmgr.BeginTransaction();
switch (item.update_type)
{
// insert item to list
case 1:
dbmgr.CreateParameters(2);
dbmgr.AddParameters(0, "@name", item.name);
dbmgr.AddParameters(1, "@desc", item.desc);
dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, item.query + "_u");
break;
// update item in list
case 2:
dbmgr.CreateParameters(3);
dbmgr.AddParameters(0, "@value", item.value);
dbmgr.AddParameters(1, "@name", item.name);
dbmgr.AddParameters(2, "@desc", item.desc);
dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, item.query + "_u");
break;
// delete item from list
case 3:
dbmgr.CreateParameters(2);
dbmgr.AddParameters(0, "@value", item.value);
dbmgr.AddParameters(1, "@in_use", in_use, true);
dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, item.query + "_d");
in_use = Convert.ToBoolean(((System.Data.Common.DbParameter)dbmgr.Parameters.GetValue(1)).Value);
break;
}
dbmgr.CommitTransaction();
}
catch (Exception ex)
{
dbmgr.RollbackTransaction();
throw (ex);
}
finally
{
dbmgr.Dispose();
}
}
// creates arraylist for ComboBox, given initial message & type
public static ArrayList FetchComboBoxList(string initMsg, ComboBoxItem.Type type, int id)
{
// initialize
ArrayList list = new ArrayList();
string query;
// determine table based on type passed
switch (type)
{
case ComboBoxItem.Type.FAFZ:
default:
query = "SELECT FZ_ID, FZ FROM FZLIST WHERE FA_ID = @fa_id ORDER BY FZ";
break;
}
// connect to database
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
dbmgr.CreateParameters(1);
dbmgr.AddParameters(0, "@fa_id", id);
dbmgr.ExecuteReader(CommandType.Text, query);
// add the default item to list
if (initMsg.Length > 0)
list.Add(new ComboBoxItem(initMsg, "0"));
// add subsequent items to list
while (dbmgr.DataReader.Read())
list.Add(new ComboBoxItem(dbmgr.DataReader.GetString(1), dbmgr.DataReader.GetInt32(0).ToString()));
}
catch (Exception ex)
{
throw ex;
}
return list;
}
// update reportlist
public static void Update(object obj)
{
// create database connection
IDBManager dbmgr;
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
DBReport item = (DBReport)obj;
try
{
dbmgr.Open();
dbmgr.CreateParameters(5);
dbmgr.AddParameters(0, "@rpt_id", item.rpt_id);
dbmgr.AddParameters(1, "@title_hdr", item.title_hdr);
dbmgr.AddParameters(2, "@project_hdr", item.project_hdr);
dbmgr.AddParameters(3, "@doc_hdr", item.doc_hdr);
dbmgr.AddParameters(4, "@plant_hdr", item.plant_hdr);
dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.REPORTLIST_u");
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbmgr.Dispose();
}
}
// fetches all parameters given a report id
public static ArrayList FetchReportParams(int id)
{
ArrayList list = new ArrayList();
DBReportParam param = new DBReportParam();
PropertyInfo[] p = param.GetType().GetProperties();
string qryString = "SELECT * FROM REPORT_PARAMS WHERE RPT_ID = @id";
// create database connection
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
dbmgr.CreateParameters(1);
dbmgr.AddParameters(0, "@id", id);
dbmgr.ExecuteReader(System.Data.CommandType.Text, qryString);
while (dbmgr.DataReader.Read())
{
param = new DBReportParam();
param = (DBReportParam)TotalManager.FetchObject(param, p, dbmgr);
list.Add(param);
}
}
catch (Exception ex)
{
throw (ex);
}
finally
{
dbmgr.Dispose();
}
return list;
}
// fetches drawing revision from drawing list for a given drawing id
public static string FetchDrawingRev(int dwg_id)
{
// NOTE: if multiple dwg_rev exists then the latest rev is selected (b/c sorting dwg_rev in descending order)
string qryString = "SELECT DWG_REV FROM DWGLIST d WHERE d.DWG_ID = @dwg_id ORDER BY d.DWG_ID, d.DWG_REV DESC";
string rev = "";
// create database connection
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
dbmgr.CreateParameters(1);
dbmgr.AddParameters(0, "@dwg_id", dwg_id);
dbmgr.ExecuteReader(CommandType.Text, qryString);
if (dbmgr.DataReader.Read())
rev = dbmgr.DataReader["DWG_REV"].ToString();
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbmgr.Dispose();
}
return rev;
}
