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