public static bool GetAllEmailAddresses(List <EmailData> list, string search)
{
bool Retval = true;
list.Clear();
#if !NO_ASA
EmailData data;
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement;
if (string.IsNullOrEmpty(search))
{
SqlStatement = "SELECT EMAIL_ONLY_USERS.emailId, EMAIL_ONLY_USERS.description, EMAIL.emailAddress from EMAIL_ONLY_USERS, EMAIL where EMAIL.emailid = EMAIL_ONLY_USERS.emailId ORDER BY EMAIL_ONLY_USERS.description";
}
else
{
SqlStatement = string.Format("SELECT EMAIL_ONLY_USERS.emailId, EMAIL_ONLY_USERS.description, EMAIL.emailAddress from EMAIL_ONLY_USERS, EMAIL where EMAIL.emailid = EMAIL_ONLY_USERS.emailId and EMAIL_ONLY_USERS.Description like '{0}' ORDER BY EMAIL_ONLY_USERS.description", search);
}
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, ModuleName, "GetAllEmailAddresses", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
MakeEmailRec(myDataReader, out data);
list.Add(data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, ModuleName, "GetAllEmailAddresses", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// Return all email list records based on search string
public static bool GetAllLists(List <TableData> list, string search)
{
bool Retval = true;
list.Clear();
#if !NO_ASA
TableData data;
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement;
if (string.IsNullOrEmpty(search))
{
SqlStatement = "SELECT * FROM EMAIL_LIST WHERE LISTTYPE = 0 ORDER BY description";
}
else
{
SqlStatement = string.Format("SELECT * FROM EMAIL_LIST WHERE LISTTYPE = 0 AND DESCRIPTION LIKE '{0}' ORDER BY description", search);
}
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, ModuleName, "GetAllLists", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
list.Add(data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, ModuleName, "GetAllLists", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
public static SADataReader GetReportData(string sqlStmt, out SAConnection _conn)
{
SADataReader myDataReader;
if (MainClass.ExecuteSelect(sqlStmt, true, TableName, "ReportTemp", "GetReportData", out _conn, out myDataReader))
{
return(myDataReader);
}
return(null);
}
public static bool DeletePrintAndViewReports()
{
bool Retval = true;
TableData data;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
// Get all reports of type PrintAndView only
string SqlStatement = string.Format("SELECT * FROM REPORT WHERE reportType={0}", (int)ReportTypeEnum.BuildToPrintOrView);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Report", "DeletePrintAndViewReports", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
// If Copy failed, delete the record - it will not have a CompletedReportFile
if (data.CopyStatus == CopyStatusEnum.Failed || data.RunStatus == RunStatusEnum.Failed || data.RunStatus == RunStatusEnum.Aborted)
{
DeleteRecord(MainClass.ToInt("Report", data.ReportIid));
}
else if (IsToBeDeleted(data.CompletedReportFile))
{
DeleteRecord(MainClass.ToInt("Report", data.ReportIid));
}
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "Report", "DeletePrintAndViewReports", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
public static bool GetAllDomains(List <TableData> list, string search = "")
{
bool Retval = true;
list.Clear();
#if !NO_ASA
TableData data;
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement;
if (string.IsNullOrEmpty(search))
{
SqlStatement = "SELECT DomainName, DomainIid, SystemAccountName, DataBlock, CreatorID, IsDefault FROM USER_DOMAINS ORDER BY DomainName";
}
else
{
SqlStatement = string.Format("SELECT DomainName, DomainIid, SystemAccountName, DataBlock, CreatorID, IsDefault FROM USER_DOMAINS WHERE DomainName = '{0}'", search);
}
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ModuleName", "GetAllDomains", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
list.Add(data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "ModuleName", "GetAllDomains", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
public static bool GetSortedAllDiscardReasons(List <TableData> list)
{
bool Retval = true;
list.Clear();
#if !NO_ASA
TableData data;
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT ReasonIid, ReasonId, ReasonText FROM DISCARD_REASONS ORDER BY ReasonText";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "DiscardReason", "GetAllDiscardReasons", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
if (data.ReasonIid == 1)
{
list.Insert(0, data);
}
else
{
list.Add(data);
}
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "DiscardReason", "GetSortedAllDiscardReasons", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
public static bool IsDuplicatePath(string dirPath, bool isEdit, int pathId, out bool pathExists)
{
bool Retval = true;
pathExists = false;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement;
if (!isEdit)
{
SqlStatement = "SELECT * from DIR_PATH_TREE WHERE dirpath=" + "'" + dirPath + "'" + "AND isBatch = 0";
}
else
{
SqlStatement = "SELECT * from DIR_PATH_TREE WHERE dirpath=" + "'" + dirPath + "'" + "AND isBatch = 0 AND pathId != " + pathId;
}
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "DirPathTree", "IsDuplicatePath", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
pathExists = true;
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "IsDuplicatePath", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// get a record
// integer vals are returned as -1 if they're null
public static bool GetRecord(int ReportIid, string FilterField, out TableData data)
{
bool Retval = true;
data = new TableData(-1, "", FilterMethodEnum.Unused,
RelativeDatesEnum.Unused, DateTime.MinValue, DateTime.MinValue, "");
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
StringBuilder tempStr = new StringBuilder();
string SqlStatement = "SELECT * from REPORT_FILTER WHERE reportIid=" + ReportIid + " AND FilterField='" + FilterField + "'";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ReportFilter", "GetRecord", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
if (tempStr.Length > 0)
{
tempStr.Append(",");
}
tempStr.Append(data.FilterItems);
}
data.FilterItems = tempStr.ToString();
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// get all records from HH_PAR_LOCATION table
// integer vals are returned as -1 if they're null
public static bool GetRecs(List <TableData> list)
{
bool Retval = true;
list.Clear();
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
TableData rec;
string SqlStatement = "SELECT * from HH_PAR_LOCATION JOIN AREAS ON HH_PAR_LOCATION.areaIid=AREAS.areaIid";
SqlStatement += " ORDER BY areaName, parLocationName";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "HHParLocation", "GetRecs", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
rec = new TableData(
MainClass.ToInt(TableName, myDataReader["parLocationIid"]),
myDataReader["parLocationName"].ToString(),
MainClass.ToInt(TableName, myDataReader["areaIid"]),
myDataReader["areaName"].ToString(),
myDataReader["costCenter"].ToString(),
myDataReader["stnCareArea"].ToString(),
myDataReader["zone"].ToString());
list.Add(rec);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "HHParLocation", "GetRecs", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// get a record
// integer vals are returned as -1 if they're null
public static bool GetRecord(int PathId, out TableData data)
{
bool Retval = true;
data = new TableData();
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from DIR_PATH_TREE WHERE pathId=" + PathId;
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "DirPathTree", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
data = new TableData(
PathId,
myDataReader["dirDescription"].ToString(),
myDataReader["dirPath"].ToString(),
MainClass.ToBool(TableName, myDataReader["isBatch"]),
MainClass.ToInt(TableName, myDataReader["parentId"]),
MainClass.ToBool(TableName, myDataReader["purge"]),
MainClass.ToBool(TableName, myDataReader["isNetworkDrive"]));
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// check if a batch report or custom list is using the group list or email
// return true if record found
public static bool ReportLinkExists(int nIid, EmailType type)
{
bool Retval = false;
string SqlStatement;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
if (type == EmailType.User)
{
SqlStatement = "SELECT * from EMAIL_LIST_TO_EMAIL where emailtype=0 and emailId=" + nIid.ToString();
}
else
{
SqlStatement = "SELECT * from EMAIL_LIST_TO_EMAIL where emailtype=1 and emailId=" + nIid.ToString();
}
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "EmailListToEmail", "ReportLinkExists", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
Retval = true;
}
}
catch (Exception ex)
{
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
"Report", ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "EmailListToEmail", "ReportLinkExists", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// Get the list of email IDs associated with a given list id
public static bool GetEmailIdsForList(int listIid, List <int> emailList, List <int> listList)
{
bool Retval = true;
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("Select EmailId, emailType from EMAIL_LIST_TO_EMAIL WHERE ListIid = '{0}'", listIid);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "EmailListToEmail", "GetEmailIdsForList", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
if (MainClass.ToInt(TableName, myDataReader["emailType"]) == (int)EmailType.User)
{
emailList.Add(MainClass.ToInt(TableName, myDataReader["emailId"]));
}
else
{
listList.Add(MainClass.ToInt(TableName, myDataReader["emailId"]));
}
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "EmailListToEmail", "GetEmailIdsForList", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
return(Retval);
}
public static bool GetDomainUsers(List <TableData> users, int domainIid)
{
bool Retval = true;
users.Clear();
#if !NO_ASA
TableData data;
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement;
SqlStatement = string.Format("SELECT * from USERS WHERE DomainIid='{0}'", domainIid);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Users", "GetAllDomains", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
users.Add(data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "Users", "GetDomainUsers", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
public static bool GetAllEmailUsers(List <emailUserData> list)
{
bool Retval = true;
list.Clear();
#if !NO_ASA
emailUserData data;
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT u.UserName name, e.emailaddress address, e.emailID emailID FROM Users u, email e where e.emailID = u.emailID union " +
"select o.description name, e.emailaddress, e.emailID FROM email_only_users o, email e where e.emailID = o.emailID order by name";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Email", "GetAllEmailUsers", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
MakeEmailDataRec(myDataReader, out data);
list.Add(data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "Email", "GetAllEmailUsers", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// get a record
// integer vals are returned as -1 if they're null
// return false if no record found
public static bool GetRecord(int autoRefillIid, out TableData ard)
{
bool Retval = false;
ard = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from HH_AUTO_REFILL WHERE autoRefillIid=" + autoRefillIid;
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "HHAutoRefill", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
ard = new TableData(
autoRefillIid,
(WhenRefillEnum)(Int16)myDataReader["whenRefill"],
(ItemsToRefillEnum)(Int16)myDataReader["itemsToRefill"],
myDataReader["userName"].ToString(),
myDataReader["userId"].ToString());
Retval = true;
}
}
catch (Exception ex)
{
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "HHAutoRefill", "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// get all message queues from EXECUTABLE_TASK table
public static bool GetQueues(List <string> list)
{
bool Retval = true;
#if !NO_ASA
string msgQueue;
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT msgQueue from EXECUTABLE_TASK";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ExecutableTask", "GetQueues", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
msgQueue = myDataReader["msgQueue"].ToString();
if (msgQueue != "")
{
list.Add(msgQueue);
}
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "ExecutableTask", "GetQueues", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
public static bool IsClient()
{
string serverName = "";
bool isClient = false;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from TBLPYXOPTIONS";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "TBLPYXOPTIONS", "IsClient", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
serverName = myDataReader["DbServerName"].ToString();
if (string.Compare(serverName, Environment.MachineName, true) != 0)
{
isClient = true;
}
}
}
catch (Exception ex)
{
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "IsClient", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(isClient);
}
// get records that are not batch
public static bool GetRecordsToPurge(out List <TableData> list)
{
bool Retval = true;
list = new List <TableData>();
TableData data;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from DIR_PATH_TREE WHERE isBatch=0 AND purge=1";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "DirPathTree", "GetRecord", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
list.Add(data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecordsToPurge", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// return record given its primary key
public static bool GetRecord(string Destination, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from XQMESSAGE_COUNT WHERE Destination='{0}'",
MainClass.FixStringForSingleQuote(Destination));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "XQMESSAGE_COUNT", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
data = new TableData(
myDataReader["Destination"].ToString()
, MainClass.ToInt(TableName, myDataReader["Message_count"]));
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
/// <summary>
/// Get all records with given case iid
/// </summary>
/// <param name="CaseIid"></param>
/// <param name="data"></param>
/// <returns></returns>
public static bool GetRecords(int CaseIid, out List <TableData> data)
{
bool Retval = true;
data = new List <TableData>();
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from PROC_MGMT_TX WHERE caseIid={0}", CaseIid);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ProcMgmtTx", "GetFirstRecord", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
TableData td;
MakeDataRec(myDataReader, out td);
data.Add(td);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetFirstRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// return record given its primary keys
public static bool GetRecord(int UserIid, int AccessTypeIid, int DeviceIid, int BtnBoardNbr, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from DOOR_ACCESS_EXCLUSION WHERE UserIid='{0}' AND AccessTypeIid='{1}' AND DeviceIid='{2}' AND BtnBoardNbr='{3}'",
(int)UserIid, (int)AccessTypeIid, (int)DeviceIid, (int)BtnBoardNbr);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "DoorAccessExclusion", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// return record given its primary keys
public static bool GetRecord(DateTime MonthOfYear, string DeviceName, int BtnBoardNbr, int BtnPocketNbr, string ItemId, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from MONTHLY_SUMMARY WHERE MonthOfYear='{0}' AND DeviceName='{1}' AND BtnBoardNbr='{2}' AND BtnPocketNbr='{3}' AND ItemId='{4}'",
MainClass.DateTimeToTimestamp(MonthOfYear), MainClass.FixStringForSingleQuote(DeviceName), (int)BtnBoardNbr, (int)BtnPocketNbr, MainClass.FixStringForSingleQuote(ItemId));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "MonthlySummary", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// return record given its primary keys
public static bool GetRecord(int MsgIid, string DeviceName, DateTime MessageTime, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from HH_MESSAGE_LOG WHERE MsgIid='{0}' AND DeviceName='{1}' AND MessageTime='{2}'",
(int)MsgIid, MainClass.FixStringForSingleQuote(DeviceName), MainClass.DateTimeToTimestamp(MessageTime));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "HHMessageLog", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// return record given its primary keys
public static bool GetRecord(string DeviceName, int BtnBoardNbr, int SubDrawer, string PktDescriptor, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from LAST_POCKET_ACCESS WHERE DeviceName='{0}' AND BtnBoardNbr='{1}' AND SubDrawer='{2}' AND PktDescriptor='{3}'",
MainClass.FixStringForSingleQuote(DeviceName), (int)BtnBoardNbr, (int)SubDrawer, MainClass.FixStringForSingleQuote(PktDescriptor));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "LastPocketAccess", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// get all tasks from EXECUTABLE_TASK table for specified taskID
// integer vals are returned as -1 if they're null
public static bool GetRecs(int TaskId, List <TableData> list)
{
bool Retval = true;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
TableData esd;
string SqlStatement = "SELECT * from EXECUTABLE_TASK WHERE taskId=" + TaskId;
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ExecutableTask", "GetRecs", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
MakeDataRec(myDataReader, out esd);
list.Add(esd);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "ExecutableTask", "GetRecs", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// check if a batch report of the given batch name exists
// return true if record found
public static bool BatchReportExists(string BatchName)
{
bool Retval = false;
// data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from Report where BatchName='" + MainClass.FixStringForSingleQuote(BatchName) + "'" + " AND ReportType= " + (int)ReportTypeEnum.Batch;
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Report", "BatchReportExists", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
// MakeDataRec(myDataReader, out data);
Retval = true;
}
}
catch (Exception ex)
{
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
"Report", ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "Report", "BatchReportExists", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// return record given its primary keys
public static bool GetRecord(int PtIid, DateTime EffectTime, int PtHistIid, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from PATIENT_HIST WHERE PtIid='{0}' AND EffectTime='{1}' AND PtHistIid='{2}'",
(int)PtIid, MainClass.DateTimeToTimestamp(EffectTime), (int)PtHistIid);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "PatientHist", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// return record given its primary key
public static bool GetRecord(int SystemType, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from SYSTEM_TYPE WHERE SystemType='{0}'",
(int)SystemType);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "SystemType", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// return record given its primary key
public static bool GetRecord(string DeviceName, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from HH_SHADOW_DEVICE_STATUS WHERE DeviceName='{0}'",
MainClass.FixStringForSingleQuote(DeviceName));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "HHShadowDeviceStatus", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// return record given its primary keys
public static bool GetRecord(string RefillId, int SystemType, string StnName, string ItemId, int PktIid, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from REFILL WHERE RefillId='{0}' AND SystemType='{1}' AND StnName='{2}' AND ItemId='{3}' AND PktIid='{4}'",
MainClass.FixStringForSingleQuote(RefillId), (int)SystemType, MainClass.FixStringForSingleQuote(StnName), MainClass.FixStringForSingleQuote(ItemId), (int)PktIid);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Refill", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
MakeDataRec(myDataReader, out data);
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}