// insert a record
public static bool InsertRecord(TableData data)
{
bool RetVal = false;
string strSqlStatement = string.Format("INSERT INTO hh_pocket (parLocationIid, storageUnitName, subUnitName, subUnitIid, binNumber, itemIid, pktPhysMaxQty, pktParQty, pktRefillPoint, pktCurQty, pktCritLow, pocketUnitOfIssue, pocketUOIRatio, includeInDOP, lastRefill, lastStockOut, lastInventory) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}'), '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}'), '{14}', '{15}', '{16}')",
data.ParLocationIid,
MainClass.FixStringForSingleQuote(data.StorageUnitName),
MainClass.FixStringForSingleQuote(data.SubUnitName),
data.SubUnitIid,
data.BinNumber,
data.ItemIid,
data.PktPhysMaxQty,
data.PktParQty,
data.PktRefillPoint,
data.PktCurQty,
data.PktCritLow,
MainClass.FixStringForSingleQuote(data.PocketUnitOfIssue),
data.PocketUOIRatio,
data.IncludeInDOP,
data.LastRefill,
data.LastStockOut,
data.LastInventory);
RetVal = MainClass.ExecuteSql(strSqlStatement, true, TableName, "HHPocket", "InsertRec");
return(RetVal);
}
// update fields of specified record - all fields except lastSuccess (left alone) and nextExecTime (made null) which are updated later.
// -1 for an int or DateTime field means make it null
public static bool UpdateRecord(int TaskScheduleIid, TableData data)
{
bool Retval = true;
string ScheduleString = MakeSchedString(data.TimeUnit, data.IntervalNum, data.StartTime,
data.DayOfWeekMask, data.MonthOfYearMask, data.DayOfMonth, data.FirstLast);
string sql = "UPDATE TASK_SCHEDULE SET taskId=" + data.TaskId
+ ", taskName=" + "'" + MainClass.FixStringForSingleQuote(data.TaskName) + "'"
+ ", argId=" + MainClass.IntToDBString(data.ArgId)
+ ", userName="******"'" + MainClass.FixStringForSingleQuote(data.UserName) + "'"
+ ", enabled=" + MainClass.BoolToInt(data.Enabled)
+ ", timeUnit=" + (int)data.TimeUnit
+ ", intervalNum=" + MainClass.IntToDBString(data.IntervalNum)
+ ", startTime=" + MainClass.DateTimeToTimestamp(data.StartTime)
+ ", endTime=" + MainClass.DateTimeToTimestamp(data.EndTime)
+ ", dayOfWeekMask=" + MainClass.IntToDBString(data.DayOfWeekMask)
+ ", monthOfYearMask=" + MainClass.IntToDBString(data.MonthOfYearMask)
+ ", dayOfMonth=" + MainClass.IntToDBString(data.DayOfMonth)
+ ", firstLast=" + MainClass.IntToDBString((int)data.FirstLast)
+ ", deleteWhenDone=" + MainClass.BoolToInt(data.DeleteWhenDone)
+ ", taskComment=" + "'" + MainClass.FixStringForSingleQuote(data.TaskComment) + "'"
+ ", execMissedTasks=" + MainClass.BoolToInt(data.ExecMissedTasks)
+ ", schedString=" + "'" + ScheduleString + "'"
+ ", nextExecTime=NULL"
+ " WHERE taskScheduleIid=" + TaskScheduleIid;
Retval = MainClass.ExecuteSql(sql, true, TableName, "TaskSchedule", "UpdateRecord");
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 CASES_TRANSFERRED (PatientIid, TempPatient, CaseId, CaseName, CaseCart, CaseCreatorId, CaseCreatorName, CaseCreationTime, LastCaseActTime, ProcedureTime, CaseSourceDevName, CaseTransDevName, CaseTransDateTime, newCaseID, oldProcedureCode, newProcedureCode, physicianName, serviceName, oldCaseType, newCaseType, newProcedureName, OldProcedureName, PatientLastName, PatientAltID1, PatientAltID2, PatientType, PatientNurseUnitName, PatientRoom, PatientBed, PatientComment1, PatientComment2, PatientAdmitDrName, PatientAttendDrName, PatientEADTFlag, PatientEquivalentPatID) VALUES ("
+ (int)data.PatientIid + ", " + (int)data.TempPatient + ", " + "'" + MainClass.FixStringForSingleQuote(data.CaseId) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.CaseName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.CaseCart) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.CaseCreatorId) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.CaseCreatorName) + "'" + ", " + MainClass.DateTimeToTimestamp(data.CaseCreationTime) + ", " + MainClass.DateTimeToTimestamp(data.LastCaseActTime) + ", " + MainClass.DateTimeToTimestamp(data.ProcedureTime) + ", " + "'" + MainClass.FixStringForSingleQuote(data.CaseSourceDevName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.CaseTransDevName) + "'" + ", " + MainClass.DateTimeToTimestamp(data.CaseTransDateTime)
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.NewCaseID) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.OldProcedureCode) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.NewProcedureCode) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PhysicianName) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.ServiceName) + "'"
+ ", " + (int)data.OldCaseType
+ ", " + (int)data.NewCaseType
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.NewProcedureName) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.OldProcedureName) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PatientLastName) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PatientAltID1) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PatientAltID2) + "'"
+ ", " + (int)data.PatientType
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PatientNurseUnitName) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PatientRoom) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PatientBed) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PatientComment1) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PatientComment2) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PatientAdmitDrName) + "'"
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PatientAttendDrName) + "'"
+ ", " + (int)data.PatientEADTFlag
+ ", " + "'" + MainClass.FixStringForSingleQuote(data.PatientEquivalentPatID) + "'"
+ ")";
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "CasesTransferred", "InsertRecord", out NewIid);
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 SITE (SiteId, SiteName, Address1, Address2, City, State, ZipCode, Country, Contact, Phone, DiagLine, Fax, Description, Notes, FacilityCode, PreExpPw, ArchiveId, PwExpDelta, UserIdExpireDelta, TempUserExpDays, PwLen, PtIdFormat, ConLogOffTimeOut, RptSortByItemId, Voice, Version, ArchiveYearFormat, DbMachineName, PrintStockOut, PrintCriticalLow, PrintADTconflict, PrintIncomingDiscrep, PrintCommDown, PermitGlobalPatientList, HomeFax, HomeEmail, ClientServerSystem, ORFeatures, DodWarning, CopyReports, EmailReports, UserDoorAccessPerItem, TechDoorAccessPerItem, Ticci, PwEnableComplex, PwComplexRegex, PwComplexDesc, PwMaxLen, PwHistory, PwHistoryClamp, ORISInterface, SmtpHost, SmptPort, SmtpUserSSL, SmtpUserName, SmtpPassword, pwEnableExtended, upgrade, siteGUID, allowGLCodeTransactions, allowORISTempToTempCaseTransfer,allowAssociator, allowAutoCaseToPatientXfer, holdRFIDTxForCase, activeDirectoryOnly, logVerbosity, logMsgFormat, logServerIP, logServerPort, logEnableRemote, pwMinAge, pwMinChg) VALUES ("
+ "'" + MainClass.FixStringForSingleQuote(data.SiteId) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.SiteName) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.Address1)
+ "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.Address2) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.City) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.State)
+ "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.ZipCode) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.Country) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.Contact)
+ "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.Phone) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.DiagLine) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.Fax)
+ "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.Description) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.Notes) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.FacilityCode)
+ "'" + ", " + (int)data.PreExpPw + ", " + "'" + MainClass.FixStringForSingleQuote(data.ArchiveId) + "'" + ", " + (int)data.PwExpDelta + ", " + (int)data.UserIdExpireDelta + ", " + (int)data.TempUserExpDays + ", "
+ (int)data.PwLen + ", " + "'" + MainClass.FixStringForSingleQuote(data.PtIdFormat) + "'" + ", " + (int)data.ConLogOffTimeOut + ", " + (int)data.RptSortByItemId + ", " + (int)data.Voice + ", " + "'" + MainClass.FixStringForSingleQuote(data.Version)
+ "'" + ", " + (int)data.ArchiveYearFormat + ", " + "'" + MainClass.FixStringForSingleQuote(data.DbMachineName) + "'" + ", " + (int)data.PrintStockOut + ", " + (int)data.PrintCriticalLow + ", " + (int)data.PrintADTconflict + ", "
+ (int)data.PrintIncomingDiscrep + ", " + (int)data.PrintCommDown + ", " + (int)data.PermitGlobalPatientList + ", " + "'" + MainClass.FixStringForSingleQuote(data.HomeFax) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.HomeEmail)
+ "'" + ", " + (int)data.ClientServerSystem + ", " + (int)data.ORFeatures + ", " + (int)data.DodWarning + ", " + (bool)data.CopyReports + ", " + (bool)data.EmailReports + ", " + (int)data.UserDoorAccessPerItem + ", "
+ (int)data.TechDoorAccessPerItem + ", " + (int)data.Ticci + ", " + (int)data.PwEnableComplex + ", " + "'" + MainClass.FixStringForSingleQuote(data.PwComplexRegex) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(data.PwComplexDesc)
+ "'" + ", " + (int)data.PwMaxLen + ", " + (int)data.PwHistory + ", " + (int)data.PwHistoryClamp + ", " + (int)data.ORISInterface + ", " + "'" + MainClass.FixStringForSingleQuote(data.SmtpHost) + "'" + ", " + (int)data.SmtpPort
+ ", " + (bool)data.SmtpUseSSL + ", " + "'" + MainClass.FixStringForSingleQuote(data.SmtpUsername) + "'" + ", " + "'" + MainClass.FixStringForSingleQuote(ConvertToUnsecureString(data.SmtpPassword)) + "'"
+ ", " + (bool)data.pwEnableExtended + ", " + (bool)data.upgrade + ", " + "'" + MainClass.FixStringForSingleQuote(data.siteGUID) + "'" + ", " + (int)data.allowGLCodeTransactions
+ ", " + (int)data.AllowORISTempToTempCaseTransfer
+ ", " + (int)data.allowAssociator
+ ", " + (int)data.allowAutoCaseToPatientXfer
+ ", " + (int)data.HoldRFIDTxForCase
+ ", " + (int)data.ActiveDirectoryOnly
+ ", " + (int)data.logSeverity + ", " + MainClass.FixStringForSingleQuote(data.logMsgFormat) + ", " + MainClass.FixStringForSingleQuote(data.logServerIP) + ", " + MainClass.FixStringForSingleQuote(data.logServerPort)
+ ", " + (int)data.logEnableRemote
+ ", " + (int)data.PwMinAge + ", " + (int)data.PwMinChg
+ ")";
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "Site", "InsertRecord", out NewIid);
return(Retval);
}
// Update Email Status
public static bool UpdateEmailStatus(int reportIid, Report.EmailStatusEnum emailStatus)
{
bool retVal = true;
string strSqlStatement = "UPDATE REPORT SET emailStatus = " + (int)emailStatus + " WHERE reportIid = " + reportIid;
retVal = MainClass.ExecuteSql(strSqlStatement, true, TableName, "Report", "UpdateEmailStatus");
return(retVal);
}
// Update Completed Report File
public static bool UpdateCompletedReportFile(int reportIid, string completedReportFile)
{
bool retVal = true;
string strSqlStatement = "UPDATE REPORT SET completedReportFile = " + "'" + completedReportFile + "'" + " WHERE reportIid = " + reportIid;
retVal = MainClass.ExecuteSql(strSqlStatement, true, TableName, "Report", "UpdateCompletedReportFile");
return(retVal);
}
public static bool RemoveActiveDirectory()
{
bool Retval = true;
string SqlStatement = string.Format("UPDATE USERS SET ActiveDirectory = 0, DomainIid = -1, DomainUserName = ''");
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "Users", "RemoveActiveDirectory");
return(Retval);
}
// Remove the domain from all users that have indicated dommainIid
public static bool RemoveDomain(int domainIid)
{
bool Retval = true;
string SqlStatement = string.Format("UPDATE USERS SET DomainIid = -1, DomainUserName = '' WHERE DomainIid='{0}'", (int)domainIid);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "Users", "RemoveDomain");
return(Retval);
}
// Update parent
public static bool UpdateParentId(int oldParentId, int newParentId)
{
bool retVal = true;
string sql = "UPDATE DIR_PATH_TREE SET parentId = " + newParentId + " WHERE parentId = " + oldParentId;
retVal = MainClass.ExecuteSql(sql, true, TableName, "DirPathTree", "UpdateParentId");
return(retVal);
}
// Update Report Type
public static bool UpdateReportType(int reportIid, Report.ReportTypeEnum reportType)
{
bool retVal = true;
string strSqlStatement = "UPDATE REPORT SET reportType = " + (int)reportType + " WHERE reportIid = " + reportIid;
retVal = MainClass.ExecuteSql(strSqlStatement, true, TableName, "Report", "UpdateRunStatus");
return(retVal);
}
public static bool DeleteRecord(string ClientName)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE CONNECTED_PVSCLIENTS WHERE ClientName='{0}'", ClientName);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "ConnectedPvsClients", "DeleteRecord");
return(Retval);
}
public static bool DeleteReport(int reportIid)
{
bool result;
string sqlStmt = string.Format("Delete from REPORT_TEMP WHERE reportIid = {0}", reportIid);
result = MainClass.ExecuteSql(sqlStmt, true, TableName, "ReportTemp", "DeleteReport");
return(result);
}
public static bool UpdateBatchPathId(int reportIid, int newBatchPathId)
{
bool retVal = true;
string strSqlStatement = "UPDATE REPORTS SET batchPathId = " + "'" + newBatchPathId + "'" + " WHERE reportIid = " + reportIid;
retVal = MainClass.ExecuteSql(strSqlStatement, true, TableName, "Reports", "UpdateBatchPathId");
return(retVal);
}
// delete record given its primary key
public static bool DeleteRecord(int _ReasonIid)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE DISCARD_REASONS WHERE ReasonIid={0}", _ReasonIid);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "DiscardReason", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(int reportIid)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE REPORTS WHERE reportIid='{0}'", (int)reportIid);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "Reports", "DeleteRecord");
return(Retval);
}
// delete record given its primary keys
public static bool DeleteRecord(int UserIid, int AccessTypeIid, int DeviceIid, int BtnBoardNbr)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE DOOR_ACCESS_EXCLUSION WHERE UserIid='{0}' AND AccessTypeIid='{1}' AND DeviceIid='{2}' AND BtnBoardNbr='{3}'",
(int)UserIid, (int)AccessTypeIid, (int)DeviceIid, (int)BtnBoardNbr);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "DoorAccessExclusion", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(int PktAccIid)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE BILLING_TBL WHERE PktAccIid='{0}'",
(int)PktAccIid);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "BillingTbl", "DeleteRecord");
return(Retval);
}
// delete record given its primary keys
public static bool DeleteRecord(DateTime MonthOfYear, string DeviceName, int BtnBoardNbr, int BtnPocketNbr, string ItemId)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE 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));
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "MonthlySummary", "DeleteRecord");
return(Retval);
}
// delete specified record
public static bool DeleteRecord(int Iid)
{
bool Retval = true;
string sql = "DELETE HH_AUTO_REFILL WHERE autoRefillIid = " + Iid;
Retval = MainClass.ExecuteSql(sql, true, TableName, "HHAutoRefill", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(int UserIid)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE HH_SHADOW_USERS WHERE UserIid='{0}'",
(int)UserIid);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "HHShadowUsers", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(int ReqIid)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE REQUEST_ITEM WHERE ReqIid='{0}'",
(int)ReqIid);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "RequestItem", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(int DrawerType)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE DWR_TYPE WHERE DrawerType='{0}'",
(int)DrawerType);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "DwrType", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(int patientOrGLCode)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE Patient_Or_GL_Code WHERE patientOrGLCode='{0}'",
(int)patientOrGLCode);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "Patient_Or_GL_Code", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(int SystemType)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE SYSTEM_TYPE WHERE SystemType='{0}'",
(int)SystemType);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "SystemType", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(int SubUnitIid)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE HH_SHADOW_SUBUNIT WHERE SubUnitIid='{0}'",
(int)SubUnitIid);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "HHShadowSubUnit", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(int DeviceIid)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE DEVICES WHERE DeviceIid='{0}'",
(int)DeviceIid);
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "Devices", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(DateTime DateOfArchRecs)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE ARCHIVE_INDEX WHERE DateOfArchRecs='{0}'",
MainClass.DateTimeToTimestamp(DateOfArchRecs));
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "ArchiveIndex", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(string Source)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE RQMESSAGE_COUNT WHERE Source='{0}'",
MainClass.FixStringForSingleQuote(Source));
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "RQMessageCount", "DeleteRecord");
return(Retval);
}
// delete record given its primary key
public static bool DeleteRecord(string DeviceName)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE HH_SHADOW_DEVICE_STATUS WHERE DeviceName='{0}'",
MainClass.FixStringForSingleQuote(DeviceName));
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "HHShadowDeviceStatus", "DeleteRecord");
return(Retval);
}
// delete record given its primary keys
public static bool DeleteRecord(int MsgIid, string DeviceName, DateTime MessageTime)
{
bool Retval = true;
string SqlStatement = string.Format("DELETE HH_MESSAGE_LOG WHERE MsgIid='{0}' AND DeviceName='{1}' AND MessageTime='{2}'",
(int)MsgIid, MainClass.FixStringForSingleQuote(DeviceName), MainClass.DateTimeToTimestamp(MessageTime));
Retval = MainClass.ExecuteSql(SqlStatement, true, TableName, "HHMessageLog", "DeleteRecord");
return(Retval);
}