// 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);
}
// insert record given all TableData fields
public static bool InsertRecord(TableData data)
{
bool Retval = false;
string SqlStatement = "INSERT INTO ITEM_COST (ItemId, EffectTime, CostPerIssue, CostPerUnitOrder) VALUES ("
+ "'" + MainClass.FixStringForSingleQuote(data.ItemId) + "'" + ", " + MainClass.DateTimeToTimestamp(data.EffectTime) + ", " + data.CostPerIssue + ", " + data.CostPerUnitOrder + ")";
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "ItemCost", "InsertRecord");
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);
}
// return record given its primary keys
public static bool GetRecord(string ItemId, DateTime EffectTime, out TableData data)
{
bool Retval = true;
data = null;
#if !NO_ASA
SAConnection _conn;
SADataReader myDataReader;
string SqlStatement = string.Format("SELECT * from ITEM_COST WHERE ItemId='{0}' AND EffectTime='{1}'",
MainClass.FixStringForSingleQuote(ItemId), MainClass.DateTimeToTimestamp(EffectTime));
if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ItemCost", "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);
}
// delete record given its primary keys
public static bool DeleteRecord(string ItemId, DateTime EffectTime)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE ITEM_COST WHERE ItemId='{0}' AND EffectTime='{1}'",
MainClass.FixStringForSingleQuote(ItemId), MainClass.DateTimeToTimestamp(EffectTime));
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "ItemCost", "DeleteRecord");
return(Retval);
}
// insert record and return its primary key
public static bool InsertRecord(TableData data, out int NewIid)
{
bool Retval = false;
NewIid = -1;
string SqlStatement = "INSERT INTO STATION_EVENT (DeviceName, ShutdownType, StationStopTime, StationStartTime, EventMsg) VALUES ("
+ "'" + MainClass.FixStringForSingleQuote(data.DeviceName) + "'" + ", " + (int)data.ShutdownType + ", " + MainClass.DateTimeToTimestamp(data.StationStopTime) + ", " + MainClass.DateTimeToTimestamp(data.StationStartTime) + ", " + "'" + MainClass.FixStringForSingleQuote(data.EventMsg) + "'" + ")";
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "StationEvent", "InsertRecord", out NewIid);
return(Retval);
}
// insert record given all TableData fields
public static bool InsertRecord(TableData data)
{
bool Retval = false;
string SqlStatement = "INSERT INTO POCKET_ACCESS_DEVICENAME_RPTINDX (DeviceName, PurgeDate, SystemType) VALUES ("
+ "'" + MainClass.FixStringForSingleQuote(data.DeviceName) + "'" + ", " + MainClass.DateTimeToTimestamp(data.PurgeDate) + ", " + (int)data.SystemType + ")";
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "reportindexes", "InsertRecord");
return(Retval);
}
// insert record and return its primary key
public static bool InsertRecord(TableData data, out int NewIid)
{
bool Retval = false;
NewIid = -1;
string SqlStatement = "INSERT INTO POCKET_ACCESS (DeviceName, TxTime, BtnDoorNbr, BtnBoardNbr, BtnBoardSeq, SubDrawer, PktDescriptor, ChassisSeqNbr, BtnColumnNbr, MsgTypeId, ItemName, ItemShortDesc, ItemAlias, ItemId, ItemAltId, ItemAltId2, ItemClass, ItemClassName, ItemType, ExpBeginCount, ActualBeginCount, TrxQty, PktPhysMaxQty, PktParQty, PktRefillPoint, PktCritLow, StnTotal, PostDateTime, LotNumber, SerialNumber, VendorName, UserName, UserId, TempUser, WitnName, WitnId, PtLastName, PtFirstName, PtMiddleName, PtId, PtAltId1, PtAltId2, PtComment1, PtComment2, NUnitName, Room, Bed, PtType, EADT, TempPatient, Waste, PtMergeId, HasBeenArchived, NextExpireTime, CostPerIssue, CostPerUnitRefill, CostPerUnitOrder, AreaName, ItemUnitOfIssue, ItemUnitOfRefill, ItemUnitOfOrder, TrxUOM, AdmitDrName, AttendDrName, KitId, FacilityCode, OrigVendSeq, OrigVendDate, IsBillable, CostCenter, VendorPartNumber, TimeBias, ItemIsChargeable, ItemStock, RefillId, RefillOrderedQty, RefillShippedQty, BtnPocketNbr, XfrToStation, ProcedureName, ProcedureCode, ServiceName, PhysicianName, PocketUnitOfIssue, PocketUOIRatio, TxCost, ItemBillOnly, SystemType, CabinetId, Drawer, PktPosLo, PktPosHi, StorageUnitName, SubUnitName, BinNumber, CaseId, CaseName, CaseCart, NewLotSerial, OverRideText, OverRideReason, SellPrice, CDM, SessionID, OrderNum, FloorStock, CaseProcId, isGLCode,OriginalTakeStation) VALUES ("
+ "'" + MainClass.FixStringForSingleQuote(data.DeviceName) + "'" + ", " + MainClass.DateTimeToTimestamp(data.TxTime) + ", " + (int)data.BtnDoorNbr + ", " + (int)data.BtnBoardNbr + ", " + (int)data.BtnBoardSeq + ", " + (int)data.SubDrawer + ", " + "'" + MainClass.FixStringForSingleQuote(data.PktDescriptor) + "'" + ", " + (int)data.ChassisSeqNbr + ", " + (int)data.BtnColumnNbr + ", " + (int)data.MsgTypeId + ", " + "'" + MainClass.FixStringForSingleQuote(data.ItemName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ItemShortDesc) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ItemAlias) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ItemId) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ItemAltId) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ItemAltId2) + "'" + ", " + (int)data.ItemClass + ", " + "'" + MainClass.FixStringForSingleQuote(data.ItemClassName) + "'" + ", " + (int)data.ItemType + ", " + (int)data.ExpBeginCount + ", " + (int)data.ActualBeginCount + ", " + (int)data.TrxQty + ", " + (int)data.PktPhysMaxQty + ", " + (int)data.PktParQty + ", " + (int)data.PktRefillPoint + ", " + (int)data.PktCritLow + ", " + (int)data.StnTotal + ", " + MainClass.DateTimeToTimestamp(data.PostDateTime) + ", " + "'" + MainClass.FixStringForSingleQuote(data.LotNumber) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.SerialNumber) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.VendorName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.UserName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.UserId) + "'" + ", " + (int)data.TempUser + ", " + "'" + MainClass.FixStringForSingleQuote(data.WitnName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.WitnId) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PtLastName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PtFirstName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PtMiddleName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PtId) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PtAltId1) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PtAltId2) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PtComment1) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PtComment2) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.NUnitName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.Room) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.Bed) + "'" + ", " + (int)data.PtType + ", " + (int)data.EADT + ", " + (int)data.TempPatient + ", " + "'" + MainClass.FixStringForSingleQuote(data.Waste) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PtMergeId) + "'" + ", " + (int)data.HasBeenArchived + ", " + MainClass.DateTimeToTimestamp(data.NextExpireTime) + ", " + data.CostPerIssue + ", " + data.CostPerUnitRefill + ", " + data.CostPerUnitOrder + ", " + "'" + MainClass.FixStringForSingleQuote(data.AreaName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ItemUnitOfIssue) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ItemUnitOfRefill) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ItemUnitOfOrder) + "'" + ", " + (int)data.TrxUOM + ", " + "'" + MainClass.FixStringForSingleQuote(data.AdmitDrName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.AttendDrName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.KitId) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.FacilityCode) + "'" + ", " + (int)data.OrigVendSeq + ", " + MainClass.DateTimeToTimestamp(data.OrigVendDate) + ", " + (int)data.IsBillable + ", " + "'" + MainClass.FixStringForSingleQuote(data.CostCenter) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.VendorPartNumber) + "'" + ", " + (int)data.TimeBias + ", " + (int)data.ItemIsChargeable + ", " + (int)data.ItemStock + ", " + "'" + MainClass.FixStringForSingleQuote(data.RefillId) + "'" + ", " + (int)data.RefillOrderedQty + ", " + (int)data.RefillShippedQty + ", " + (int)data.BtnPocketNbr + ", " + "'" + MainClass.FixStringForSingleQuote(data.XfrToStation) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ProcedureName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ProcedureCode) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ServiceName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PhysicianName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PocketUnitOfIssue) + "'" + ", " + (int)data.PocketUOIRatio + ", " + data.TxCost + ", " + (int)data.ItemBillOnly + ", " + (int)data.SystemType + ", " + (int)data.CabinetId + ", " + (int)data.Drawer + ", " + (int)data.PktPosLo + ", " + (int)data.PktPosHi + ", " + "'" + MainClass.FixStringForSingleQuote(data.StorageUnitName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.SubUnitName) + "'" + ", " + (int)data.BinNumber + ", " + "'" + MainClass.FixStringForSingleQuote(data.CaseId) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.CaseName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.CaseCart) + "'" + ", " + (int)data.NewLotSerial + ", " + "'" + MainClass.FixStringForSingleQuote(data.OverRideText) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.OverRideReason) + "'" + ", " + data.SellPrice + ", " + (int)data.CDM + ", " + "'" + MainClass.FixStringForSingleQuote(data.SessionID) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.OrderNum) + "'" + ", " + (int)data.FloorStock + ", " + "'" + MainClass.FixStringForSingleQuote(data.CaseProcId) + "'" + ", " + (int)data.isGLCode + ", " + "'" + MainClass.FixStringForSingleQuote(data.OriginalTakeStation) + "'" + ")";
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "PocketAccess", "InsertRecord", out NewIid);
return(Retval);
}