// 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;
}
// 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;
}
public static int SelectedItemId(ComboBoxItem.Type type, string selected_text)
{
// initialize
int value = 0;
string query;
switch (type)
{
case ComboBoxItem.Type.Unit:
query = "SELECT UNIT_ID AS ID FROM UNITLIST WHERE UNIT ='" + selected_text + "'";
break;
case ComboBoxItem.Type.Sys:
query = "SELECT SYS_ID AS ID FROM SYSLIST WHERE SYS ='" + selected_text + "'";
break;
case ComboBoxItem.Type.Comp_Type:
default:
query = "SELECT COMP_TYPE_ID AS ID FROM COMPTYPES WHERE COMP_TYPE ='" + selected_text + "'";
break;
}
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.ExecuteReader(CommandType.Text, query);
while (dbmgr.DataReader.Read())
{
value = Convert.ToInt32(dbmgr.DataReader["id"]);
return value;
}
}
catch
{
return value;
}
finally
{
dbmgr.Dispose();
}
return value;
}
// determine id/index of selected value from autocomplete extender (returns 0 if not found)
public static int SelectedItemId(AutoCompleteType item_type, string selected_value)
{
// initialize
int index = 0;
string qryString="";
// determine type of item
switch (item_type)
{
case AutoCompleteType.Drawing:
qryString = "SELECT DWG_ID AS id FROM DWGLIST WHERE DWG_REF='" + selected_value + "'";
break;
case AutoCompleteType.Cable:
qryString = "SELECT CABLE_ID AS id FROM CABLIST WHERE CABLE='" + selected_value + "'";
break;
case AutoCompleteType.Component:
case AutoCompleteType.Interlock:
case AutoCompleteType.Power:
qryString = "SELECT COMP_ID AS id FROM COMPLIST WHERE COMP='" + selected_value + "'";
break;
case AutoCompleteType.Route:
qryString = "SELECT NODE_ID AS id FROM ROUTELIST WHERE NODE='" + selected_value + "'";
break;
case AutoCompleteType.FArea:
qryString = "SELECT FA_ID AS id FROM FALIST WHERE FA='" + selected_value + "'";
break;
case AutoCompleteType.FZone:
qryString = "SELECT FZ_ID AS id FROM FZLIST WHERE FZ='" + selected_value + "'";
break;
case AutoCompleteType.FRoom:
qryString = "SELECT RM_ID AS id FROM FRLIST WHERE RM='" + selected_value + "'";
break;
case AutoCompleteType.Disposition:
qryString = "SELECT DISP_ID AS id FROM DISPLIST WHERE DISP='" + selected_value + "'";
break;
case AutoCompleteType.BE:
qryString = "SELECT BE_ID AS id FROM BELIST WHERE BE='" + selected_value + "'";
break;
case AutoCompleteType.KSF:
qryString = "SELECT KSF_ID AS id FROM KSFLIST WHERE KSF='" + selected_value + "'";
break;
case AutoCompleteType.Mode:
qryString = "SELECT MODE_ID AS id FROM MODELIST WHERE MODE='" + selected_value + "'";
break;
case AutoCompleteType.NSCA:
qryString = "SELECT COMP_ID AS id FROM COMPLIST WHERE COMP='" + selected_value + "' AND ((SSD_REQ=1 AND PRI_SUB='P') OR (NSCA_REQ=1))";
break;
case AutoCompleteType.NPO:
qryString = "SELECT COMP_ID AS id FROM COMPLIST WHERE COMP='" + selected_value + "' AND NPO_REQ=1";
break;
case AutoCompleteType.PRA:
qryString = "SELECT COMP_ID AS id FROM COMPLIST WHERE COMP='" + selected_value + "' AND PRA_REQ=1";
break;
case AutoCompleteType.ALLDocument:
qryString = "SELECT DOC_ID AS id FROM DOCLIST WHERE DOC='" + selected_value + "'";
break;
case AutoCompleteType.EEDocument:
qryString = "SELECT DOC_ID AS id FROM DOCLIST WHERE DOC='" + selected_value + "' AND DOC_TYPE='EE'";
break;
case AutoCompleteType.LICDocument:
qryString = "SELECT DOC_ID AS id FROM DOCLIST WHERE DOC='" + selected_value + "' AND DOC_TYPE='LIC'";
break;
case AutoCompleteType.GENDocument:
qryString = "SELECT DOC_ID AS id FROM DOCLIST WHERE DOC='" + selected_value + "' AND DOC_TYPE='GEN'";
break;
}
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr); // get provider
dbmgr.ConnectionString = user.plantDBStr; // set connection string
try
{
dbmgr.Open(); // open database
dbmgr.ExecuteReader(CommandType.Text, qryString); // execute query
while (dbmgr.DataReader.Read()) // get text and id for each item
{
index = Convert.ToInt32(dbmgr.DataReader["id"]);
return index;
}
}
catch
{
return index;
}
finally
{
dbmgr.Dispose();
}
return index;
}
// 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();
}
}
public static DataSet GenerateExport(int id, string query, string name)
{
DataSet ds = new DataSet(name);
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
ds = dbmgr.ExecuteDataSet(CommandType.Text, query);
ds.DataSetName = name;
}
catch (Exception ex)
{
throw (ex);
}
finally
{
dbmgr.Dispose();
}
return ds;
}
// fetches an IList of the selected lookup table. This method supports the lookupTable.aspx page.
public static IList FetchLookupTable(string type, ref string tableName)
{
ArrayList list = new ArrayList();
string query = "SELECT * FROM %TABLE% ORDER BY %ITEM%";
string table = "";
string item = "";
//ComboBoxItem item;
// get table name from type
// NOTE: type must match TABLECOLUMNTYPES.COLTYPE_NAME
switch (type)
{
case "Units":
table = "UNITLIST";
item = "UNIT";
break;
case "Systems":
table = "SYSLIST";
item = "SYS";
break;
case "Positions":
table = "POSLIST";
item = "POS";
break;
case "Trains":
table = "TRAINLIST";
item = "TRAIN";
break;
case "Method":
table = "SSD_METHODLIST";
item = "METHOD";
break;
case "Component Types":
table = "COMPTYPES";
item = "COMP_TYPE";
break;
case "Snapshots":
table = "SNAPSHOTTYPES";
item = "SNAPSHOT_NAME";
break;
case "Paths":
table = "PATHLIST";
item = "SNAPSHOT_NAME";
break;
case "Modes":
table = "MODELIST";
item = "MODE";
break;
case "Box":
table = "BOXLIST";
item = "BOX";
break;
case "KSF":
table = "KSFLIST";
item = "KSF";
break;
}
tableName = table;
query = query.Replace("%TABLE%", table);
query = query.Replace("%ITEM%", item);
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.ExecuteReader(CommandType.Text, query);
while (dbmgr.DataReader.Read())
// set item's name(NAME), desc(DESC), value(ID), query(TABLE)
list.Add(new ComboBoxItem(dbmgr.DataReader.GetString(1), dbmgr.DataReader.GetString(2), dbmgr.DataReader.GetInt32(0).ToString(), table));
}
catch (Exception ex)
{
throw (ex);
}
finally
{
dbmgr.Dispose();
}
return list;
}
public static IList FetchHistory()
{
// initialize
History item = new History();
PropertyInfo[] p = item.GetType().GetProperties();
List<History> list = new List<History>();
string qryString = "SELECT * FROM HISTORY_LOG";
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.ExecuteReader(CommandType.Text, qryString);
while (dbmgr.DataReader.Read())
{
item = new History();
item = (History)FetchObject(item, p, dbmgr);
list.Add(item);
}
}
catch (Exception ex)
{
throw (ex);
}
finally
{
dbmgr.Dispose();
}
return list;
}
// 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;
}
// creates arraylist for ComboBox, given initial message & type
public static ArrayList FetchComboBoxList(string initMsg, ComboBoxItem.Type type)
{
// initialize
ArrayList list = new ArrayList();
string query;
// determine table based on type passed
switch (type)
{
case ComboBoxItem.Type.Box:
query = "SELECT * FROM BOXLIST ORDER BY BOX";
break;
case ComboBoxItem.Type.Unit:
query = "SELECT * FROM UNITLIST ORDER BY UNIT";
break;
case ComboBoxItem.Type.Sys:
query = "SELECT * FROM SYSLIST ORDER BY SYS";
break;
case ComboBoxItem.Type.Position:
query = "SELECT * FROM POSLIST ORDER BY POS";
break;
case ComboBoxItem.Type.Train:
query = "SELECT * FROM TRAINLIST ORDER BY TRAIN";
break;
case ComboBoxItem.Type.Method:
query = "SELECT * FROM SSD_METHODLIST ORDER BY METHOD";
break;
case ComboBoxItem.Type.Comp_Type:
query = "SELECT * FROM COMPTYPES ORDER BY COMP_TYPE";
break;
case ComboBoxItem.Type.DrawingType:
query = "SELECT * FROM DWGTYPES WHERE DWGTYPE_ID <= 4";
break;
case ComboBoxItem.Type.FZone:
query = "SELECT * FROM FZLIST ORDER BY FZ";
break;
case ComboBoxItem.Type.FArea:
query = "SELECT * FROM FALIST ORDER BY FA";
break;
case ComboBoxItem.Type.KSF:
query = "SELECT * FROM KSFLIST ORDER BY KSF";
break;
case ComboBoxItem.Type.Bin:
query = "SELECT BIN_ID, BIN, BIN_DESC FROM BINLIST ORDER BY BIN_ID";
break;
case ComboBoxItem.Type.ExportTables:
query = "SELECT COLTYPE_ID, COLTYPE_NAME, QUERY, EDIT FROM TABLECOLUMNTYPES WHERE EXPORT = 1";
break;
case ComboBoxItem.Type.LookupTables:
default:
query = "SELECT COLTYPE_ID, COLTYPE_NAME, QUERY, EDIT FROM TABLECOLUMNTYPES WHERE LEVEL = 1 ORDER BY COLTYPE_NAME";
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.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())
{
switch (type)
{
case ComboBoxItem.Type.ExportTables:
// set item's name(COLTYPE_NAME), desc(""), value(ID), query(QUERY)
list.Add(new ComboBoxItem(dbmgr.DataReader.GetString(1), "", dbmgr.DataReader.GetInt32(0).ToString(), dbmgr.DataReader.GetString(2)));
break;
case ComboBoxItem.Type.LookupTables:
// set item's name(COLTYPE_NAME), desc(""), value(ID|EDIT), query(QUERY)
list.Add(new ComboBoxItem(dbmgr.DataReader.GetString(1), "", dbmgr.DataReader.GetInt32(0).ToString() + "|" + dbmgr.DataReader.GetBoolean(3).ToString(), dbmgr.DataReader.GetString(2)));
break;
case ComboBoxItem.Type.Bin:
list.Add(new ComboBoxItem(dbmgr.DataReader.GetString(1) + ": " + dbmgr.DataReader.GetString(2), dbmgr.DataReader.GetInt32(0).ToString()));
break;
default:
list.Add(new ComboBoxItem(dbmgr.DataReader.GetString(1), dbmgr.DataReader.GetInt32(0).ToString()));
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
return list;
}
// fetches autocomplete list
public static string[] FetchAutoCompleteList(string prefix, string ContextKey, AutoCompleteType type)
{
// NOTE: autocomplete lists are utilized as web service instead of dropdownlist to minimize view state
string qryString = "";
List<string> items = new List<string>();
switch (type)
{
case AutoCompleteType.Component:
qryString = "SELECT COMP_ID, COMP FROM COMPLIST WHERE COMP LIKE '" + prefix + "%' AND PRI_SUB <> 'D' ORDER BY COMP";
break;
case AutoCompleteType.Drawing:
qryString = "SELECT DWG_ID, DWG_REF FROM DWGLIST WHERE DWG_REF LIKE '" + prefix + "%' ORDER BY DWG_REF, DWG_REV DESC";
break;
case AutoCompleteType.Power:
qryString = "SELECT POWER_ID, POWER FROM viewPOWERLIST WHERE POWER LIKE '" + prefix + "%' ORDER BY POWER";
break;
case AutoCompleteType.Subcomp:
// qryString = "SELECT COMP_ID, COMP FROM COMPLIST WHERE COMP LIKE '" + prefix + "%' AND PRI_SUB='S' ORDER BY COMP";
qryString = "SELECT COMP_ID, COMP FROM COMPLIST WHERE COMP LIKE '" + prefix + "%' AND PRI_SUB <> 'D' ORDER BY COMP";
break;
case AutoCompleteType.Interlock:
qryString = "SELECT COMP_ID AS INTLK_ID, COMP AS INTLK FROM COMPLIST WHERE COMP LIKE '" + prefix + "%' ORDER BY COMP";
break;
case AutoCompleteType.Cable:
qryString = "SELECT CABLE_ID, CABLE FROM CABLIST WHERE CABLE LIKE '" + prefix + "%' ORDER BY CABLE";
break;
case AutoCompleteType.Route:
qryString = "SELECT NODE_ID, NODE FROM ROUTELIST WHERE NODE LIKE '" + prefix + "%' ORDER BY NODE";
break;
case AutoCompleteType.FArea:
qryString = "SELECT FA_ID, FA FROM FALIST WHERE FA LIKE '" + prefix + "%' ORDER BY FA";
break;
case AutoCompleteType.FZone:
qryString = "SELECT FZ_ID, FZ FROM FZLIST WHERE FZ LIKE '" + prefix + "%' ORDER BY FZ";
break;
case AutoCompleteType.FRoom:
qryString = "SELECT RM_ID, RM FROM FRLIST WHERE RM LIKE '" + prefix + "%' ORDER BY RM";
break;
case AutoCompleteType.Disposition:
qryString = "SELECT DISP_ID, DISP FROM DISPLIST WHERE DISP LIKE '" + prefix + "%' ORDER BY DISP";
break;
case AutoCompleteType.NSCA:
qryString = "SELECT COMP_ID, COMP FROM COMPLIST WHERE COMP LIKE '" + prefix + "%' AND PRI_SUB <> 'D' AND ((SSD_REQ=1 AND PRI_SUB='P') OR (NSCA_REQ=1)) ORDER BY COMP";
break;
case AutoCompleteType.PRA:
qryString = "SELECT COMP_ID, COMP FROM COMPLIST WHERE COMP LIKE '" + prefix + "%' AND PRI_SUB <> 'D' AND PRA_REQ=1 ORDER BY COMP";
break;
case AutoCompleteType.NPO:
qryString = "SELECT COMP_ID, COMP FROM COMPLIST WHERE COMP LIKE '" + prefix + "%' AND PRI_SUB <> 'D' AND NPO_REQ=1 ORDER BY COMP";
break;
case AutoCompleteType.BDEndpt:
qryString = "SELECT DISTINCT 0 AS BD_ENDPT_ID, BD_ENDPT FROM viewBDENDPOINTS WHERE BD_ENDPT LIKE '" + prefix + "%' ORDER BY BD_ENDPT";
break;
case AutoCompleteType.BE:
qryString = "SELECT DISTINCT BE_ID, BE FROM BELIST WHERE BE LIKE '" + prefix + "%' ORDER BY BE";
break;
case AutoCompleteType.KSF:
qryString = "SELECT DISTINCT KSF_ID, KSF FROM KSFLIST WHERE KSF LIKE '" + prefix + "%' ORDER BY KSF";
break;
case AutoCompleteType.Mode:
qryString = "SELECT DISTINCT MODE_ID, MODE FROM MODELIST WHERE MODE LIKE '" + prefix + "%' ORDER BY MODE";
break;
case AutoCompleteType.ALLDocument:
qryString = "SELECT DISTINCT DOC_ID, DOC FROM DOCLIST WHERE DOC LIKE '" + prefix + "%' ORDER BY DOC";
break;
case AutoCompleteType.EEDocument:
qryString = "SELECT DISTINCT DOC_ID, DOC FROM DOCLIST WHERE DOC LIKE '" + prefix + "%' AND DOC_TYPE='EE' ORDER BY DOC";
break;
case AutoCompleteType.LICDocument:
qryString = "SELECT DISTINCT DOC_ID, DOC FROM DOCLIST WHERE DOC LIKE '" + prefix + "%' AND DOC_TYPE='LIC' ORDER BY DOC";
break;
case AutoCompleteType.GENDocument:
qryString = "SELECT DISTINCT DOC_ID, DOC FROM DOCLIST WHERE DOC LIKE '" + prefix + "%' AND DOC_TYPE='GEN' ORDER BY DOC";
break;
}
String dbString = Util.Crypto.Decrypt(Util.Crypto.CryptoType.TripleDES, Util.Config.getValue("dbKey", "FRIAS"), ContextKey);
IDBManager dbmgr = new DBManager(dbString);
dbmgr.ConnectionString = dbString;
try
{
dbmgr.Open(); // open database
dbmgr.ExecuteReader(CommandType.Text, qryString); // execute query
while (dbmgr.DataReader.Read()) // get text and id for each item
{
items.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(dbmgr.DataReader.GetString(1), dbmgr.DataReader.GetInt32(0).ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbmgr.Dispose();
}
return items.ToArray(); //return the string array
}
// 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;
}
