// return record given its primary keys
public static bool GetRecord(int ParLocationIid, string StorageUnitName, int SubUnitIid, int BinNumber, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from HH_SHADOW_POCKET WHERE ParLocationIid='{0}' AND StorageUnitName='{1}' AND SubUnitIid='{2}' AND BinNumber='{3}'",
(int)ParLocationIid, MainClass.FixStringForSingleQuote(StorageUnitName), (int)SubUnitIid, (int)BinNumber);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "HHShadowPocket", "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 ItemID, string CostCenter, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from ITEM_PRICE WHERE ItemID='{0}' AND CostCenter='{1}'",
MainClass.FixStringForSingleQuote(ItemID), MainClass.FixStringForSingleQuote(CostCenter));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ItemPrice", "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 TxTime, int NSequenceNum, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from PEND_POCKET_ACCESS WHERE TxTime='{0}' AND NSequenceNum='{1}'",
MainClass.DateTimeToTimestamp(TxTime), (int)NSequenceNum);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "PendPocketAccess", "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 PktIid, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from POCKET WHERE PktIid='{0}'",
(int)PktIid);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Pocket", "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 ImportFileName, DateTime ImportTime, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from D_S_IMPORT_REGISTRY WHERE ImportFileName='{0}' AND ImportTime='{1}'",
MainClass.FixStringForSingleQuote(ImportFileName), MainClass.DateTimeToTimestamp(ImportTime));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "DSImportRegistry", "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 par locations associated with this auto refill
// return false if error or no data found
public static bool GetParLocs(int autoRefillIid, List <int> ParLocList)
{
bool Retval = false;
ParLocList.Clear();
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from HH_AUTOREFILL_TO_PARLOC WHERE autoRefillIid=" + autoRefillIid;
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "HHAutoRefillToParloc", "GetParLocs", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
ParLocList.Add(MainClass.ToInt(TableName, myDataReader["parLocationIid"]));
Retval = true;
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetParLocs", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// convert binary DB field value to byte[]
public static byte[] ToByteArray(string table, object inval)
{
byte[] Retval = { };
try
{
if (Type.GetTypeCode(inval.GetType()) != TypeCode.DBNull)
{
Retval = (byte[])inval;
}
}
catch (Exception ex)
{
string err = String.Format(StringTable.GetString("DatabaseCastError"),
table, ex.Message.ToString());
ServiceMessages.InsertRec(MainClass.AppName, "MainClass", "ToByteArray", err);
}
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)
{
bool Retval = false;
// data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from REPORT where emailtype=1 and emailId=" + nIid.ToString();
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Report", "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, "Report", "ReportLinkExists", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
// check for duplicate GL code, ignoring the one we are working on
public static bool IsDuplicatedGLCode(string glCode, int excludedIid)
{
bool glCodeExists = false;
DeleteOldGLCode(glCode);
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from PATIENTS WHERE ptId='" + glCode + "' AND PtIid <> " + excludedIid.ToString();
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "PATIENTS", "IsDuplicatedGLCode", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
glCodeExists = true;
}
}
catch (Exception ex)
{
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "IsDuplicatedGLCode", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(glCodeExists);
}
// convert Decimal DB field value to Decimal
public static Decimal ToDecimal(string table, object inval)
{
Decimal Retval = (decimal) - 1.0;
try
{
if (Type.GetTypeCode(inval.GetType()) != TypeCode.DBNull)
{
Retval = (Decimal)inval;
}
}
catch (Exception ex)
{
string err = String.Format(StringTable.GetString("DatabaseCastError"),
table, ex.Message.ToString());
ServiceMessages.InsertRec(MainClass.AppName, "MainClass", "ToDecimal", err);
}
return(Retval);
}
// find Iid for printer name
public static int GetIidFromPrinterName(string printerName)
{
int printerIid = 0;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from PRINTER_DATA WHERE printerName=" + "'" + printerName + "'";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "PRINTER_DATA", "GetIidFromPrinterName", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
printerIid = (int)myDataReader["printerIid"];
}
}
catch (Exception ex)
{
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetIidFromPrinterName", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(printerIid);
}
// get task from TASK_SCHEDULE table given a select statement
// integer vals are returned as -1 if they're null
// DateTime vals are returned as MinValue if they're null
// return false if error or no record found
public static bool GetData(string SqlStatement, out TableData tsd)
{
bool Retval = false;
tsd = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "TaskSchedule", "GetRecord", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
MakeDataRec(myDataReader, out tsd);
Retval = true;
}
}
catch (Exception ex)
{
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "TaskSchedule", "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 for duplicate printer descriptions, ignoring the one we are working on
public static bool IsDuplicatedPrinterDescription(string printerDescription, int excludedPrinterIid)
{
bool descExists = false;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from PRINTER_DATA WHERE description='" + printerDescription + "' AND printerIid <> " + excludedPrinterIid.ToString();
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "PRINTER_DATA", "IsDuplicatedPrinterDescription", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
descExists = true;
}
}
catch (Exception ex)
{
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "IsDuplicatedPrinterDescription", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(descExists);
}
// Get Server name
public static string GetServerName()
{
string consoleName = "";
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from TBLPYXOPTIONS";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "TBLPYXOPTIONS", "GetServerName", out _conn, out myDataReader))
{
try
{
if (myDataReader.Read())
{
consoleName = myDataReader["DbServerName"].ToString();
}
}
catch (Exception ex)
{
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetServerName", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(consoleName);
}
public static bool GetEmailAddressesInList(int listIid, List <string> emailAddresses)
{
bool Retval = true;
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT E.EMAILADDRESS FROM EMAIL E, EMAIL_LIST_TO_EMAIL L WHERE E.EMAILID = L.EMAILID AND L.LISTIID = '{0}' AND L.EMAILTYPE = 0 ORDER BY EMAILADDRESS", listIid);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "EmailListToEmail", "GetSortedEmailAddressesForList", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
emailAddresses.Add(myDataReader["EMAILADDRESS"].ToString());
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"),
TableName, ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "EmailListToEmail", "GetSortedEmailAddressList", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
return(Retval);
}
// Execute a SQL statement & return the connection
// Return value: true if successful
static bool ExecuteSql(bool ReturnConn, string SqlStatement, bool LogErrMsg,
string table, string CallingClass, string CallingFunction,
out SAConnection _conn)
{
bool RetVal = true;
_conn = null;
try
{
if (OpenDB(CallingClass, CallingFunction, out _conn))
{
SACommand cmd = new SACommand(SqlStatement, _conn);
cmd.ExecuteNonQuery();
}
else
{
RetVal = false;
}
}
catch (Exception ex)
{
RetVal = false;
if (LogErrMsg)
{
string err = String.Format(StringTable.GetString("DatabaseError"),
table,
ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, CallingClass, CallingFunction, err);
}
}
finally
{
if (!ReturnConn && _conn != null)
{
_conn.Close();
}
}
return(RetVal);
}
// return record given its primary keys
public static bool GetRecord(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 BOARD_COUPLING WHERE DeviceIid='{0}' AND BtnBoardNbr='{1}'",
(int)DeviceIid, (int)BtnBoardNbr);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "BoardCoupling", "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();
}
if (_conn != null)
{
_conn.Close();
}
}
}
#endif
return(Retval);
}
// return record given its primary keys
public static bool GetRecord(string Destination, int Message_number, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from COMM_RQ WHERE Destination='{0}' AND Message_number='{1}'",
MainClass.FixStringForSingleQuote(Destination), (int)Message_number);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "CommRQ", "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();
}
if (_conn != null)
{
_conn.Close();
}
}
}
#endif
return(Retval);
}
// return record given its emailId
public static bool GetRecord(int listIid, int emailId, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from EMAIL_LIST_TO_EMAIL WHERE ListIid ='{0}' AND EmailId = '(1)'",
(int)listIid, (int)emailId);
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "EmailListToEmail", "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();
}
if (_conn != null)
{
_conn.Close();
}
}
}
#endif
return(Retval);
}
// return record given its primary keys
public static bool GetRecord(int AttnIid, int AttnCategory, DateTime Message_time, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from ATTENTION WHERE AttnIid='{0}' AND AttnCategory='{1}' AND Message_time='{2}'",
(int)AttnIid, (int)AttnCategory, MainClass.DateTimeToTimestamp(Message_time));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Attention", "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();
}
if (_conn != null)
{
_conn.Close();
}
}
}
#endif
return(Retval);
}
// return record given its primary key
public static bool GetRecord(DateTime DateOfArchRecs, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from ARCHIVE_INDEX WHERE DateOfArchRecs='{0}'",
MainClass.DateTimeToTimestamp(DateOfArchRecs));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ArchiveIndex", "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();
}
if (_conn != null)
{
_conn.Close();
}
}
}
#endif
return(Retval);
}
// return record given its primary keys
public static bool GetRecord(int DeviceIid, string MsgType, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from DEVICE_BLOCKED_MSGS WHERE DeviceIid='{0}' AND MsgType='{1}'",
(int)DeviceIid, MainClass.FixStringForSingleQuote(MsgType));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "DeviceBlockedMsgs", "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();
}
if (_conn != null)
{
_conn.Close();
}
}
}
#endif
return(Retval);
}
// return record given its primary keys
public static bool GetRecord(DateTime DayOfMonth, 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 DAILY_SUMMARY WHERE DayOfMonth='{0}' AND DeviceName='{1}' AND BtnBoardNbr='{2}' AND BtnPocketNbr='{3}' AND ItemId='{4}'",
MainClass.DateTimeToTimestamp(DayOfMonth), MainClass.FixStringForSingleQuote(DeviceName), (int)BtnBoardNbr, (int)BtnPocketNbr, MainClass.FixStringForSingleQuote(ItemId));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "DailySummary", "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();
}
if (_conn != null)
{
_conn.Close();
}
}
}
#endif
return(Retval);
}
// Get PrinterIid for a given Console name
public static bool GetRecordFromConsoleName(string ConsoleName, out TableData data)
{
bool Retval = false;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from CONSOLE_DATA where consoleName='" + MainClass.FixStringForSingleQuote(ConsoleName) + "' ";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ConsoleData", "GetRecordFromConsoleName", 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"),
"ConsoleData", ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, "ConsoleData", "GetRecordFromConsoleName", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
}
if (_conn != null)
{
_conn.Close();
}
}
}
#endif
return(Retval);
}
public static int FindNotices(string message, DateTime timeframe)
{
int count = 0;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from ATTENTION WHERE attnMsg = '{0}' AND Message_time > {1}",
MainClass.FixStringForSingleQuote(message), MainClass.DateTimeToTimestamp(timeframe));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Attention", "FindNotices", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
++count;
}
}
catch (Exception ex)
{
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();
}
if (_conn != null)
{
_conn.Close();
}
}
}
#endif
return(count);
}
// convert timestamp DB field value to DateTime
public static DateTime ToDate(string table, object inval)
{
DateTime Retval = DateTime.MinValue;
try
{
if (Type.GetTypeCode(inval.GetType()) != TypeCode.DBNull)
{
Retval = (DateTime)inval;
}
if (Retval.Year == 1 && Retval.Month == 1 && Retval.Day == 1)
{
Retval = DateTime.MinValue; // sybase translates null as time 0, but sets it to 12:00PM instead of 12:00AM
}
}
catch (Exception ex)
{
string err = String.Format(StringTable.GetString("DatabaseCastError"),
table, ex.Message.ToString());
ServiceMessages.InsertRec(MainClass.AppName, "MainClass", "ToDate", err);
}
return(Retval);
}
public static bool GetRecord(string tagId, out TableData data)
{
bool Retval = false;
data = null;
#if !NO_ASA
string SqlStatement = "SELECT * FROM LOT_SERIAL where tagIDDisplay = :tagId";
try
{
using (SAConnection con = MainClass.GetConnection())
{
con.Open();
using (SACommand cmd = new SACommand(SqlStatement, con))
{
cmd.Parameters.Add(new SAParameter("tagId", SADbType.VarChar, 64)).Value = tagId;
using (SADataReader sa = cmd.ExecuteReader())
{
if (sa.Read())
{
MakeDataRec(sa, out data);
Retval = true;
}
}
}
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + "=" + tagId + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
#endif
return(Retval);
}
// convert integer DB field value to int
public static int ToInt(string table, object inval)
{
int Retval = -1;
Type t = inval.GetType();
try
{
if (Type.GetTypeCode(t) == TypeCode.Int16)
{
Retval = (Int16)inval;
}
else if (Type.GetTypeCode(t) != TypeCode.DBNull)
{
Retval = (int)inval;
}
}
catch (Exception ex)
{
string err = String.Format(StringTable.GetString("DatabaseCastError"),
table, ex.Message.ToString());
ServiceMessages.InsertRec(MainClass.AppName, "MainClass", "ToInt", err);
}
return(Retval);
}
// return all records
// -1 for an int or enum arg means that DB field was NULL
// DateTime.MinTime for a DateTime arg means that DB field was NULL
public static bool GetRecs(List <TableData> list)
{
bool Retval = true;
list.Clear();
#if !NO_ASA
TableData data;
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from Reports";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Reports", "GetData", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
data = new TableData(
MainClass.ToInt(TableName, myDataReader["ReportIid"]),
myDataReader["reportFile"].ToString(),
myDataReader["recordFilter"].ToString(),
MainClass.ToInt(TableName, myDataReader["numCopy"]),
MainClass.ToBool(TableName, myDataReader["sendMessage"]),
myDataReader["dateFrom"].ToString(),
myDataReader["dateTo"].ToString(),
myDataReader["dateRange"].ToString(),
(SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["groupDirection1"]),
(SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["groupDirection2"]),
(SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["groupDirection3"]),
myDataReader["groupHeader1"].ToString(),
myDataReader["groupHeader2"].ToString(),
myDataReader["groupHeader3"].ToString(),
myDataReader["groupTable1"].ToString(),
myDataReader["groupTable2"].ToString(),
myDataReader["groupTable3"].ToString(),
(SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["sortDirection1"]),
(SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["sortDirection2"]),
(SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["sortDirection3"]),
myDataReader["sortHeader1"].ToString(),
myDataReader["sortHeader2"].ToString(),
myDataReader["sortHeader3"].ToString(),
myDataReader["sortTable1"].ToString(),
myDataReader["sortTable2"].ToString(),
myDataReader["sortTable3"].ToString(),
myDataReader["filterText1"].ToString(),
myDataReader["filterText2"].ToString(),
myDataReader["filterText3"].ToString(),
myDataReader["filterText4"].ToString(),
myDataReader["filterText5"].ToString(),
myDataReader["filterText6"].ToString(),
myDataReader["filterText7"].ToString(),
myDataReader["filterText8"].ToString(),
myDataReader["filterText9"].ToString(),
myDataReader["filterText10"].ToString(),
myDataReader["filterText11"].ToString(),
myDataReader["filterText12"].ToString(),
myDataReader["filterText13"].ToString(),
myDataReader["filterText14"].ToString(),
myDataReader["filterText15"].ToString(),
myDataReader["batchName"].ToString(),
MainClass.ToBool(TableName, myDataReader["reportDelete"])
);
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, "Reports", "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 all records with specified ReportIid
// integer vals are returned as -1 if they're null
// to use:
// List<TableData> data = new List<TableData>(); GetRecords(iid, data);
public static bool GetRecords(int ReportIid, List <TableData> list)
{
bool Retval = true;
StringBuilder filter = new StringBuilder();
StringBuilder items = new StringBuilder();
list.Clear();
TableData data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = "SELECT * from REPORT_FILTER WHERE reportIid=" + ReportIid + " ORDER BY filterField, filterItems";
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ReportFilter", "GetRecord", out _conn, out myDataReader))
{
try
{
while (myDataReader.Read())
{
if (myDataReader["FilterField"].ToString() == filter.ToString())
{ // multiple records, append items
items.Append("," + myDataReader["filteritems"]);
continue;
}
else
{ // new filter field
if (data != null)
{ // save previous record
data.FilterItems = items.ToString();
list.Add(data);
}
// create new record
MakeDataRec(myDataReader, out data);
filter.Clear();
items.Clear();
filter.Append(myDataReader["FilterField"]);
items.Append(myDataReader["FilterItems"]);
}
}
// add last record to list
if (data != null)
{
data.FilterItems = items.ToString();
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, "GetRecords", err);
}
finally
{
if (myDataReader != null)
{
myDataReader.Close();
myDataReader.Dispose();
myDataReader = null;
}
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
}
#endif
return(Retval);
}
