///<summary>Used for ortho chart audit trail. Attempts to parse the DateOfService from the security log text. If it is unable to parse the date, it will return MinDate.
///<para>Returning MinDate from this function results in the audit trail entries for multiple dates of service displaying intermingled on the date "0001-01-01", harmless.</para></summary>
public static DateTime GetOrthoDateFromLog(SecurityLog securityLog)
{
//There are 3 cases to try, in order of ascending complexity. If a simple case succeeds at parsing a date, that date is returned.
//1) Using the new log text, there should be an 8 digit number at the end of each log entry. This is in the format "YYYYMMDD" and should be culture invariant.
//2) Using the old log text, the Date of service appeared as a string in the middle of the text block.
//3) Using the old log text, the Date of service appeared as a string in the middle of the text block in a culture dependant format.
DateTime retVal = DateTime.MinValue;
#region Ideal Case, Culture invariant
try {
string dateString = securityLog.LogText.Substring(securityLog.LogText.Length - 8, 8);
retVal = new DateTime(int.Parse(dateString.Substring(0, 4)), int.Parse(dateString.Substring(4, 2)), int.Parse(dateString.Substring(6, 2)));
if (retVal != DateTime.MinValue)
{
return(retVal);
}
}
catch (Exception ex) {
ex.DoNothing();
}
#endregion
#region Depricated, log written in english
try {
if (securityLog.LogText.StartsWith("Ortho chart field edited. Field date: "))
{
retVal = DateTime.Parse(securityLog.LogText.Substring("Ortho chart field edited. Field date: ".Length, 10)); //Date usually in the format MM/DD/YYYY, unless using en-UK for example
if (retVal != DateTime.MinValue)
{
return(retVal);
}
}
}
catch (Exception ex) {
ex.DoNothing();
}
#endregion
#region Depricated, log written in current culture
try {
if (securityLog.LogText.StartsWith(Lans.g("FormOrthoChart", "Ortho chart field edited. Field date")))
{
string[] tokens = securityLog.LogText.Split(new string[] { ": " }, StringSplitOptions.None);
retVal = DateTime.Parse(tokens[1].Replace(Lans.g("FormOrthoChart", "Field name"), ""));
if (retVal != DateTime.MinValue)
{
return(retVal);
}
}
}
catch (Exception ex) {
ex.DoNothing();
}
#endregion
#region Depricated, log written in non-english non-current culture
//not particularly common or useful.
#endregion
return(retVal); //Should be DateTime.MinVal if we are returning here.
}
///<summary>Throws exceptions. Will purposefully throw ODExceptions that are already translated and and formatted.</summary>
public static ApiResponse VoidPayment(string paySimplePaymentId, long clinicNum = -1)
{
ValidateProgram(clinicNum);
if (string.IsNullOrWhiteSpace(paySimplePaymentId))
{
throw new Exception(Lans.g("PaySimple", "Invalid PaySimple Payment ID to void."));
}
return(PaySimpleApi.PutPaymentVoided(GetAuthHeader(clinicNum), paySimplePaymentId));
}
///<summary>Throws exceptions. Will purposefully throw ODExceptions that are already translated and and formatted.</summary>
public static ApiResponse AddCreditCard(long customerId, string ccNum, DateTime ccExpDate, string billingZipCode = "", long clinicNum = -1)
{
ValidateProgram(clinicNum);
if (customerId == 0)
{
throw new ODException(Lans.g("PaySimple", "Invalid PaySimple Customer ID provided: ") + customerId.ToString());
}
return(PaySimpleApi.PostAccountCreditCard(GetAuthHeader(clinicNum), PaySimpleApi.MakeNewAccountCreditCardData(customerId, ccNum, ccExpDate, PaySimpleApi.GetCardType(ccNum), billingZipCode)));
}
///<summary>Throws exceptions. Will purposefully throw ODExceptions that are already translated and and formatted.
///Returns the PaymentId given by PaySimple.</summary>
public static ApiResponse MakePaymentByToken(CreditCard cc, decimal payAmt, long clinicNum = -1)
{
ValidateProgram(clinicNum);
if (cc == null || string.IsNullOrWhiteSpace(cc.PaySimpleToken))
{
throw new ODException(Lans.g("PaySimple", "Error making payment by token"));
}
return(MakePayment(cc.PatNum, cc, payAmt, "", DateTime.MinValue, false, "", "", clinicNum));
}
///<summary>Throws exception if account is in use.</summary>
public static void Delete(Account acct)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), acct);
return;
}
//check to see if account has any journal entries
string command = "SELECT COUNT(*) FROM journalentry WHERE AccountNum=" + POut.Long(acct.AccountNum);
if (Db.GetCount(command) != "0")
{
throw new ApplicationException(Lans.g("FormAccountEdit",
"Not allowed to delete an account with existing journal entries."));
}
//Check various preference entries
command = "SELECT ValueString FROM preference WHERE PrefName='AccountingDepositAccounts'";
string result = Db.GetCount(command);
string[] strArray = result.Split(new char[] { ',' });
for (int i = 0; i < strArray.Length; i++)
{
if (strArray[i] == acct.AccountNum.ToString())
{
throw new ApplicationException(Lans.g("FormAccountEdit", "Account is in use in the setup section."));
}
}
command = "SELECT ValueString FROM preference WHERE PrefName='AccountingIncomeAccount'";
result = Db.GetCount(command);
if (result == acct.AccountNum.ToString())
{
throw new ApplicationException(Lans.g("FormAccountEdit", "Account is in use in the setup section."));
}
command = "SELECT ValueString FROM preference WHERE PrefName='AccountingCashIncomeAccount'";
result = Db.GetCount(command);
if (result == acct.AccountNum.ToString())
{
throw new ApplicationException(Lans.g("FormAccountEdit", "Account is in use in the setup section."));
}
//check AccountingAutoPay entries
List <AccountingAutoPay> listAutoPays = AccountingAutoPays.GetDeepCopy();
for (int i = 0; i < listAutoPays.Count; i++)
{
strArray = listAutoPays[i].PickList.Split(new char[] { ',' });
for (int s = 0; s < strArray.Length; s++)
{
if (strArray[s] == acct.AccountNum.ToString())
{
throw new ApplicationException(Lans.g("FormAccountEdit", "Account is in use in the setup section."));
}
}
}
command = "DELETE FROM account WHERE AccountNum = " + POut.Long(acct.AccountNum);
Db.NonQ(command);
}
///<Summary>For use in areas of the program where we have only have room for the simple abbr. Such as pick boxes in the claim edit window. This will give Abbr (hidden).</Summary>
public string GetAbbr()
{
string retval = Abbr;
if (IsHidden)
{
retval += " " + Lans.g("Providers", "(hidden)");
}
return(retval);
}
///<Summary>For use in areas of the program where we have more room than just simple abbr. Such as pick boxes in reports. This will give Abbr - LName, FName (hidden).</Summary>
public string GetLongDesc()
{
string retval = Abbr + " - " + LName + ", " + FName;
if (IsHidden)
{
retval += " " + Lans.g("Providers", "(hidden)");
}
return(retval);
}
public static void SetParameter(Sheet sheet, string paramName, object paramValue)
{
SheetParameter param = GetParamByName(sheet.Parameters, paramName);
if (param == null)
{
throw new ApplicationException(Lans.g("Sheet", "Parameter not found: ") + paramName);
}
param.ParamValue = paramValue;
}
///<summary>Surround with try/catch because it can throw exceptions. We don't really need to make this public, but it's required in order to follow the RemotingRole pattern.</summary>
public static void Validate(bool isNew, Userod user)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), isNew, user);
return;
}
//should add a check that employeenum and provnum are not both set.
//make sure username is not already taken
string command;
long excludeUserNum;
if (isNew)
{
excludeUserNum = 0;
}
else
{
excludeUserNum = user.UserNum; //it's ok if the name matches the current username
}
if (!IsUserNameUnique(user.UserName, excludeUserNum))
{
throw new Exception(Lans.g("Userods", "UserName already in use."));
}
//make sure that there would still be at least one user with security admin permissions
if (!isNew)
{
command = "SELECT COUNT(*) FROM grouppermission "
+ "WHERE PermType='" + POut.Long((int)Permissions.SecurityAdmin) + "' "
+ "AND UserGroupNum=" + POut.Long(user.UserGroupNum);
if (Db.GetCount(command) == "0") //if this user would not have admin
//make sure someone else has admin
{
command = "SELECT COUNT(*) FROM userod,grouppermission "
+ "WHERE grouppermission.PermType='" + POut.Long((int)Permissions.SecurityAdmin) + "'"
+ " AND userod.UserGroupNum=grouppermission.UserGroupNum"
+ " AND userod.IsHidden =0"
+ " AND userod.UserNum != " + POut.Long(user.UserNum);
if (Db.GetCount(command) == "0") //there are no other users with this permission
{
throw new Exception(Lans.g("Users", "At least one user must have Security Admin permission."));
}
}
}
//an admin user can never be hidden
command = "SELECT COUNT(*) FROM grouppermission "
+ "WHERE PermType='" + POut.Long((int)Permissions.SecurityAdmin) + "' "
+ "AND UserGroupNum=" + POut.Long(user.UserGroupNum);
if (Db.GetCount(command) != "0" && //if this user is admin
user.IsHidden) //and hidden
{
throw new Exception(Lans.g("Userods", "Admins cannot be hidden."));
}
}
///<summary></summary>
private static void Validate(Schedule sched)
{
if (sched.StartTime > sched.StopTime)
{
throw new Exception(Lans.g("Schedule", "Stop time must be later than start time."));
}
if (sched.StartTime + TimeSpan.FromMinutes(5) > sched.StopTime && sched.Status == SchedStatus.Open)
{
throw new Exception(Lans.g("Schedule", "Stop time cannot be the same as the start time."));
}
}
///<summary>Surround with try/catch. If there are any dependencies, then this will throw an exception.
///This is currently only called from FormCarrierEdit.
///No need to pass in usernum, it is set before the remoting role and passed in for logging.</summary>
public static void Delete(Carrier Cur, long userNum = 0)
{
if (RemotingClient.RemotingRole != RemotingRole.ServerWeb)
{
userNum = Security.CurUser.UserNum; //must be before normal remoting role check to get user at workstation
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), Cur, userNum);
return;
}
//look for dependencies in insplan table.
string command = "SELECT insplan.PlanNum,CONCAT(CONCAT(LName,', '),FName) FROM insplan "
+ "LEFT JOIN inssub ON insplan.PlanNum=inssub.PlanNum "
+ "LEFT JOIN patient ON inssub.Subscriber=patient.PatNum "
+ "WHERE insplan.CarrierNum = " + POut.Long(Cur.CarrierNum) + " "
+ "ORDER BY LName,FName";
DataTable table = Db.GetTable(command);
string strInUse;
if (table.Rows.Count > 0)
{
strInUse = ""; //new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
if (i > 0)
{
strInUse += "; ";
}
strInUse += PIn.String(table.Rows[i][1].ToString());
}
throw new ApplicationException(Lans.g("Carriers", "Not allowed to delete carrier because it is in use. Subscribers using this carrier include ") + strInUse);
}
//look for dependencies in etrans table.
command = "SELECT DateTimeTrans FROM etrans WHERE CarrierNum=" + POut.Long(Cur.CarrierNum)
+ " OR CarrierNum2=" + POut.Long(Cur.CarrierNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
strInUse = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (i > 0)
{
strInUse += ", ";
}
strInUse += PIn.DateT(table.Rows[i][0].ToString()).ToShortDateString();
}
throw new ApplicationException(Lans.g("Carriers", "Not allowed to delete carrier because it is in use in the etrans table. Dates of claim sent history include ") + strInUse);
}
command = "DELETE from carrier WHERE CarrierNum = " + POut.Long(Cur.CarrierNum);
Db.NonQ(command);
InsEditLogs.MakeLogEntry(null, Cur, InsEditLogType.Carrier, userNum);
}
///<summary>NO LONGER USED.
///Leaving function here in case we want to reuse the code in future. We only support connecting to archive DB directly.</summary>
public static T RunFuncOnArchiveDatabase <T>(Func <T> f)
{
//Makes a connection to the archive database, validates that the version of the database is the same as the current database version,
//executes the func passed in, and then sets the connection back to the original database before returning the results of the func passed in.
//Optionally pass in connection settings to override the archive preferences. Throws exceptions.
if (RemotingClient.RemotingRole.In(RemotingRole.ClientWeb, RemotingRole.ServerWeb)) //May already be behind a remoting role check.
//This method will eventually invoke SetDB() which is unacceptable for the Middle Tier.
{
throw new ApplicationException(Lans.g(nameof(MiscData), "Archive databases are not available when using a Middle Tier connection.") + "\r\n" +
Lans.g(nameof(MiscData), "Archive databases may only be created or accessed on a direct database connection."));
}
string connectionStrOrig = DataConnection.GetCurrentConnectionString();
DatabaseType dbTypeOrig = DataConnection.DBtype;
DataConnection dcon = new DataConnection();
try {
//Keep track of the original connection settings so that we can revert back to them once finished archiving.
Version versionDbOrig = new Version(PrefC.GetString(PrefName.DataBaseVersion));
string archiveServerName = PrefC.GetString(PrefName.ArchiveServerName);
string archiveUserName = PrefC.GetString(PrefName.ArchiveUserName);
string decryptedPass;
CDT.Class1.Decrypt(PrefC.GetString(PrefName.ArchivePassHash), out decryptedPass);
//Connect to the archive database. This can throw many exceptions.
dcon.SetDb(archiveServerName, MiscData.GetArchiveDatabaseName(), archiveUserName, decryptedPass, "", "", dbTypeOrig);
#region Validate archive database version
//At this point there is an active connection to the archive database, validate the DataBaseVersion.
string version = PrefC.GetStringNoCache(PrefName.DataBaseVersion);
if (string.IsNullOrEmpty(version))
{
//Preference table does not have version information. Somehow they have a database with proper structure but no data.
//This archive database can't be trusted and we have no idea what version the schema is at.
//They need to call support so that we can take a look or they need to delete the invalid archive (or remove it from the data dir)
//so that a new archive database can be made from scratch.
throw new ApplicationException("Invalid archive database detected.");
}
Version versionDbArchive = new Version(version);
if (versionDbOrig > versionDbArchive)
{
//The archive database needs to be updated before funcs can be invoked against it.
throw new ApplicationException("Archive database is at a lower version than the current database."
+ " Run the Archive tool in order to update the database.");
}
else if (versionDbArchive > versionDbOrig)
{
throw new ApplicationException("Archive database version is higher than the current database. Process cannot continue.");
}
#endregion
//Invoke the func passed in.
return(f());
}
finally { //Always put the connection back to the original no matter what happened above when trying to make an archive.
dcon.SetDb(connectionStrOrig, "", dbTypeOrig); //It is acceptable to crash the program if this fails.
}
}
///<summary>Surround with try-catch</summary>
public static void Delete(long employeeNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), employeeNum);
return;
}
//appointment.Assistant will not block deletion
//schedule.EmployeeNum will not block deletion
string command = "SELECT COUNT(*) FROM clockevent WHERE EmployeeNum=" + POut.Long(employeeNum);
if (Db.GetCount(command) != "0")
{
throw new ApplicationException(Lans.g("FormEmployeeSelect",
"Not allowed to delete employee because of attached clock events."));
}
command = "SELECT COUNT(*) FROM timeadjust WHERE EmployeeNum=" + POut.Long(employeeNum);
if (Db.GetCount(command) != "0")
{
throw new ApplicationException(Lans.g("FormEmployeeSelect",
"Not allowed to delete employee because of attached time adjustments."));
}
command = "SELECT COUNT(*) FROM userod WHERE EmployeeNum=" + POut.Long(employeeNum);
if (Db.GetCount(command) != "0")
{
throw new ApplicationException(Lans.g("FormEmployeeSelect",
"Not allowed to delete employee because of attached user."));
}
command = "UPDATE appointment SET Assistant=0 WHERE Assistant=" + POut.Long(employeeNum);
Db.NonQ(command);
command = "SELECT ScheduleNum FROM schedule WHERE EmployeeNum=" + POut.Long(employeeNum);
DataTable table = Db.GetTable(command);
List <string> listScheduleNums = new List <string>(); //Used for deleting scheduleops below
for (int i = 0; i < table.Rows.Count; i++)
{
//Add entry to deletedobjects table if it is a provider schedule type
DeletedObjects.SetDeleted(DeletedObjectType.ScheduleProv, PIn.Long(table.Rows[i]["ScheduleNum"].ToString()));
listScheduleNums.Add(table.Rows[i]["ScheduleNum"].ToString());
}
if (listScheduleNums.Count > 0)
{
command = "DELETE FROM scheduleop WHERE ScheduleNum IN(" + POut.String(String.Join(",", listScheduleNums)) + ")";
Db.NonQ(command);
}
//command="DELETE FROM scheduleop WHERE ScheduleNum IN(SELECT ScheduleNum FROM schedule WHERE EmployeeNum="+POut.Long(employeeNum)+")";
//Db.NonQ(command);
command = "DELETE FROM schedule WHERE EmployeeNum=" + POut.Long(employeeNum);
Db.NonQ(command);
command = "DELETE FROM employee WHERE EmployeeNum =" + POut.Long(employeeNum);
Db.NonQ(command);
command = "DELETE FROM timecardrule WHERE EmployeeNum=" + POut.Long(employeeNum);
Db.NonQ(command);
}
///<summary>Returns and error message to display to the user if default clearinghouses are not set up; Otherwise, empty string.</summary>
public static string CheckClearinghouseDefaults()
{
if (PrefC.GetLong(PrefName.ClearinghouseDefaultDent) == 0)
{
return(Lans.g("ContrAccount", "No default dental clearinghouse defined."));
}
if (PrefC.GetBool(PrefName.ShowFeatureMedicalInsurance) && PrefC.GetLong(PrefName.ClearinghouseDefaultMed) == 0)
{
return(Lans.g("ContrAccount", "No default medical clearinghouse defined."));
}
return("");
}
///<summary>Primarily used when user clicks OK from the InsPlan window. Gets a carrierNum from the database based on the other supplied carrier data. Sets the CarrierNum accordingly. If there is no matching carrier, then a new carrier is created. The end result is a valid carrierNum to use.</summary>
public static Carrier GetIndentical(Carrier carrier)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <Carrier>(MethodBase.GetCurrentMethod(), carrier));
}
if (carrier.CarrierName == "")
{
return(new Carrier()); //should probably be null instead
}
Carrier retVal = carrier.Copy();
string command = "SELECT CarrierNum FROM carrier WHERE "
+ "CarrierName = '" + POut.String(carrier.CarrierName) + "' "
+ "AND Address = '" + POut.String(carrier.Address) + "' "
+ "AND Address2 = '" + POut.String(carrier.Address2) + "' "
+ "AND City = '" + POut.String(carrier.City) + "' "
+ "AND State = '" + POut.String(carrier.State) + "' "
+ "AND Zip = '" + POut.String(carrier.Zip) + "' "
+ "AND Phone = '" + POut.String(carrier.Phone) + "' "
+ "AND ElectID = '" + POut.String(carrier.ElectID) + "' "
+ "AND NoSendElect = '" + POut.Bool(carrier.NoSendElect) + "'";
DataTable table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
//A matching carrier was found in the database, so we will use it.
retVal.CarrierNum = PIn.Long(table.Rows[0][0].ToString());
return(retVal);
}
//No match found. Decide what to do. Usually add carrier.--------------------------------------------------------------
//Canada:
if (CultureInfo.CurrentCulture.Name.EndsWith("CA")) //Canadian. en-CA or fr-CA
{
throw new ApplicationException(Lans.g("Carriers", "Carrier not found.")); //gives user a chance to add manually.
/*if(carrier.ElectID!=""){
* command="SELECT CarrierNum FROM carrier WHERE "
+"ElectID = '"+POut.String(carrier.ElectID)+"' "
+"AND IsCDA=1";
* table=Db.GetTable(command);
* if(table.Rows.Count>0){//if there already exists a Canadian carrier with that ElectID
* retVal.CarrierNum=PIn.Long(table.Rows[0][0].ToString());
* //set carrier.CarrierNum to the carrier found (all other carrier fields will still be wrong)
* //throw new ApplicationException(Lans.g("Carriers","The carrier information was changed based on the EDI Code provided."));
* return retVal;
* }
* }*/
//Notice that if inserting a carrier, it's never possible to create a canadian carrier.
}
Insert(carrier);
retVal.CarrierNum = carrier.CarrierNum;
return(retVal);
}
///<summary>Returns the description of the fee schedule. Appends (hidden) if the fee schedule has been hidden.</summary>
public static string GetDescription(long feeSchedNum)
{
//No need to check RemotingRole; no call to db.
string feeSchedDesc = "";
FeeSched feeSched = GetFirstOrDefault(x => x.FeeSchedNum == feeSchedNum);
if (feeSched != null)
{
feeSchedDesc = feeSched.Description + (feeSched.IsHidden ? " (" + Lans.g("FeeScheds", "hidden") + ")" : "");
}
return(feeSchedDesc);
}
///<summary></summary>
private static void Validate(CovSpan span)
{
//No need to check RemotingRole; no call to db.
if (span.FromCode == "" || span.ToCode == "")
{
throw new ApplicationException(Lans.g("FormInsSpanEdit", "Codes not allowed to be blank."));
}
if (String.Compare(span.ToCode, span.FromCode) < 0)
{
throw new ApplicationException(Lans.g("FormInsSpanEdit", "From Code must be less than To Code. Remember that the comparison is alphabetical, not numeric. For instance, 100 would come before 2, but after 02."));
}
}
///<summary>Returns a list of all appointments and whether that appointment has a conflict for the given listChildOpNums.
///Used to determine if there are any overlapping appointments for ALL time between a 'master' op appointments and the 'child' ops appointments.
///If an appointment from one of the give child ops has a confilict with the master op, then the appointment.Tag will be true.
///Throws exceptions.</summary>
public static List <ODTuple <Appointment, bool> > MergeApptCheck(long masterOpNum, List <long> listChildOpNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <ODTuple <Appointment, bool> > >(MethodBase.GetCurrentMethod(), masterOpNum, listChildOpNums));
}
if (listChildOpNums == null || listChildOpNums.Count == 0)
{
return(new List <ODTuple <Appointment, bool> >());
}
if (listChildOpNums.Contains(masterOpNum))
{
throw new ApplicationException(Lans.g("Operatories", "The operatory to keep cannot be within the selected list of operatories to combine."));
}
List <int> listApptStatus = new List <int>();
listApptStatus.Add((int)ApptStatus.Scheduled);
listApptStatus.Add((int)ApptStatus.Complete);
listApptStatus.Add((int)ApptStatus.Broken);
listApptStatus.Add((int)ApptStatus.PtNote);
//12/09/2016 - Query below originally created by Allen and moddified for job by Joe.
string command =
"SELECT childAppointments.*, " +
"MAX(CASE WHEN " +
"(childAppointments.AptDateTime <= MasterAppointments.AptDateTime AND childAppointments.AptDateTime + INTERVAL (LENGTH(childAppointments.Pattern) * 5) MINUTE > MasterAppointments.AptDateTime) " +
"OR " +
"(MasterAppointments.AptDateTime <= childAppointments.AptDateTime AND MasterAppointments.AptDateTime + INTERVAL (LENGTH(MasterAppointments.Pattern) * 5) MINUTE > childAppointments.AptDateTime) " +
"THEN 1 ELSE 0 END) AS HasConflict " +
"FROM ( " +
"SELECT * " +
"FROM appointment " +
"WHERE Op =" + POut.Long(masterOpNum) + " " +
"AND aptstatus IN (" + String.Join(",", listApptStatus) + ") " +
") MasterAppointments " +
"CROSS JOIN ( " +
"SELECT * " +
"FROM appointment " +
"WHERE Op IN (" + String.Join(",", listChildOpNums) + ") " +
"AND aptstatus IN (" + String.Join(",", listApptStatus) + ") " +
") childAppointments " +
"GROUP BY childAppointments.AptNum";
DataTable table = Db.GetTable(command);
List <Appointment> list = AppointmentCrud.TableToList(table);
List <ODTuple <Appointment, bool> > listTuplesAptConflicts = new List <ODTuple <Appointment, bool> >();
for (int i = 0; i < table.Rows.Count; i++)
{
Appointment appt = list.First(x => x.AptNum == PIn.Long(table.Rows[i]["AptNum"].ToString())); //Safe
bool hasConflict = PIn.Bool(table.Rows[i]["HasConflict"].ToString());
listTuplesAptConflicts.Add(new ODTuple <Appointment, bool>(appt, hasConflict));
}
return(listTuplesAptConflicts);
}
///<summary>Gets the human readable description of the patrestriction, passed through Lans.g.
///Returns empty string if the enum was not found in the switch statement.</summary>
public static string GetPatRestrictDesc(PatRestrict patRestrictType)
{
switch (patRestrictType)
{
case PatRestrict.ApptSchedule:
return(Lans.g("patRestrictEnum", "Appointment Scheduling"));
case PatRestrict.None:
default:
return("");
}
}
///<summary>Throws exception if anything about the practice information is not valid.
///All intended exceptions are Exceptions and are already translated.</summary>
public static void ValidateClinic(Clinic clinic)
{
if (clinic == null)
{
throw new Exception("Clinic not found");
}
if (!Regex.IsMatch(clinic.Phone, "^[0-9]{10}?$"))
{
throw new ODException(Lans.g("Erx", "Clinic phone must be exactly 10 digits") + ": " + clinic.Description);
}
if (clinic.Phone.StartsWith("555"))
{
throw new ODException(Lans.g("Erx", "Clinic phone cannot start with 555") + ": " + clinic.Description);
}
if (Regex.IsMatch(clinic.Phone, "^[0-9]{3}555[0-9]{4}$"))
{
throw new ODException(Lans.g("Erx", "Clinic phone cannot contain 555 in the middle 3 digits") + ": " + clinic.Description);
}
if (!Regex.IsMatch(clinic.Fax, "^[0-9]{10}?$"))
{
throw new ODException(Lans.g("Erx", "Clinic fax must be exactly 10 digits") + ": " + clinic.Description);
}
if (clinic.Fax.StartsWith("555"))
{
throw new ODException(Lans.g("Erx", "Clinic fax cannot start with 555") + ": " + clinic.Description);
}
if (Regex.IsMatch(clinic.Fax, "^[0-9]{3}555[0-9]{4}$"))
{
throw new ODException(Lans.g("Erx", "Clinic fax cannot contain 555 in the middle 3 digits") + ": " + clinic.Description);
}
if (clinic.Address == "")
{
throw new ODException(Lans.g("Erx", "Clinic address blank") + ": " + clinic.Description);
}
if (Regex.IsMatch(clinic.Address, ".*P\\.?O\\.? .*", RegexOptions.IgnoreCase))
{
throw new ODException(Lans.g("Erx", "Clinic address cannot be a PO BOX") + ": " + clinic.Description);
}
if (clinic.City == "")
{
throw new ODException(Lans.g("Erx", "Clinic city blank") + ": " + clinic.Description);
}
if (StateCodes.IndexOf(clinic.State.ToUpper()) < 0)
{
throw new ODException(Lans.g("Erx", "Clinic state abbreviation invalid") + ": " + clinic.Description);
}
string clinicZip = Regex.Replace(clinic.Zip, "[^0-9]*", ""); //Zip with all non-numeric characters removed.
if (clinicZip.Length != 9)
{
throw new ODException(Lans.g("Erx", "Clinic zip must be 9 digits") + ": " + clinic.Description);
}
}
///<summary>Throws exception if anything about the practice information is not valid.
///All intended exceptions are ODExceptions and should be translated by the caller.</summary>
public static void ValidatePracticeInfo()
{
string practicePhone = PrefC.GetString(PrefName.PracticePhone);
if (!Regex.IsMatch(practicePhone, "^[0-9]{10}$")) //"^[0-9]{10}(x[0-9]+)?$")) {
{
throw new ODException(Lans.g("Erx", "Practice phone must be exactly 10 digits."));
}
if (practicePhone.StartsWith("555"))
{
throw new ODException(Lans.g("Erx", "Practice phone cannot start with 555."));
}
if (Regex.IsMatch(practicePhone, "^[0-9]{3}555[0-9]{4}$"))
{
throw new ODException(Lans.g("Erx", "Practice phone cannot contain 555 in the middle 3 digits."));
}
string practiceFax = PrefC.GetString(PrefName.PracticeFax);
if (!Regex.IsMatch(practiceFax, "^[0-9]{10}(x[0-9]+)?$"))
{
throw new ODException(Lans.g("Erx", "Practice fax must be exactly 10 digits."));
}
if (practiceFax.StartsWith("555"))
{
throw new ODException(Lans.g("Erx", "Practice fax cannot start with 555."));
}
if (Regex.IsMatch(practiceFax, "^[0-9]{3}555[0-9]{4}$"))
{
throw new ODException(Lans.g("Erx", "Practice fax cannot contain 555 in the middle 3 digits."));
}
if (PrefC.GetString(PrefName.PracticeAddress) == "")
{
throw new ODException(Lans.g("Erx", "Practice address blank."));
}
if (Regex.IsMatch(PrefC.GetString(PrefName.PracticeAddress), ".*P\\.?O\\.? .*", RegexOptions.IgnoreCase))
{
throw new ODException(Lans.g("Erx", "Practice address cannot be a PO BOX."));
}
if (PrefC.GetString(PrefName.PracticeCity) == "")
{
throw new ODException(Lans.g("Erx", "Practice city blank."));
}
if (StateCodes.IndexOf(PrefC.GetString(PrefName.PracticeST).ToUpper()) < 0)
{
throw new ODException(Lans.g("Erx", "Practice state abbreviation invalid."));
}
string practiceZip = Regex.Replace(PrefC.GetString(PrefName.PracticeZip), "[^0-9]*", ""); //Zip with all non-numeric characters removed.
if (practiceZip.Length != 9)
{
throw new ODException(Lans.g("Erx", "Practice zip must be 9 digits."));
}
}
///<summary></summary>
public static void DeleteObject(long sheetDefNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), sheetDefNum);
return;
}
//validate that not already in use by a refferral.
string command = "SELECT LName,FName FROM referral WHERE Slip=" + POut.Long(sheetDefNum);
DataTable table = Db.GetTable(command);
//int count=PIn.PInt(Db.GetCount(command));
string referralNames = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (i > 0)
{
referralNames += ", ";
}
referralNames += table.Rows[i]["FName"].ToString() + " " + table.Rows[i]["LName"].ToString();
}
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is already in use by referrals. Not allowed to delete.") + " " + referralNames);
}
//validate that not already in use by automation.
command = "SELECT AutomationNum FROM automation WHERE SheetDefNum=" + POut.Long(sheetDefNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by automation. Not allowed to delete."));
}
//validate that not already in use by a laboratory
command = "SELECT Description FROM laboratory WHERE Slip=" + POut.Long(sheetDefNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by laboratories. Not allowed to delete.")
+ "\r\n" + string.Join(", ", table.Select().Select(x => x["Description"].ToString())));
}
//validate that not already in use by clinicPref.
command = "SELECT ClinicNum FROM clinicpref WHERE ValueString='" + POut.Long(sheetDefNum) + "' AND PrefName='" + POut.String(PrefName.SheetsDefaultRx.ToString()) + "'";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by clinics. Not allowed to delete.")
+ "\r\n" + string.Join(", ", table.Select().Select(x => Clinics.GetDesc(PIn.Long(x["ClinicNum"].ToString())))));
}
command = "DELETE FROM sheetfielddef WHERE SheetDefNum=" + POut.Long(sheetDefNum);
Db.NonQ(command);
Crud.SheetDefCrud.Delete(sheetDefNum);
}
///<summary></summary>
public static long Insert(Employee Cur)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Cur.EmployeeNum = Meth.GetLong(MethodBase.GetCurrentMethod(), Cur);
return(Cur.EmployeeNum);
}
if (Cur.LName == "" && Cur.FName == "")
{
throw new ApplicationException(Lans.g("FormEmployeeEdit", "Must include either first name or last name"));
}
return(Crud.EmployeeCrud.Insert(Cur));
}
///<summary>Throws exceptions. Will purposefully throw ODExceptions that are already translated and and formatted.
///Returns the PaymentId given by PaySimple.</summary>
private static ApiResponse MakePaymentNoPat(decimal payAmt, string ccNum, DateTime ccExpDate, string billingZipCode = "", string cvv = "", long clinicNum = -1)
{
ValidateProgram(clinicNum);
if (string.IsNullOrWhiteSpace(ccNum) || ccExpDate.Year < DateTime.Today.Year)
{
throw new ODException(Lans.g("PaySimple", "Error making payment"));
}
long psCustomerId = AddCustomer("UNKNOWN", "UNKNOWN", "", clinicNum);
ApiResponse apiResponse = AddCreditCard(psCustomerId, ccNum, ccExpDate, billingZipCode);
string accountId = apiResponse.PaySimpleToken;
return(PaySimpleApi.PostPayment(GetAuthHeader(clinicNum), PaySimpleApi.MakeNewPaymentData(PIn.Long(accountId), payAmt, cvv)));
}
///<summary></summary>
public static void Update(JournalEntry je)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), je);
return;
}
if (je.DebitAmt < 0 || je.CreditAmt < 0)
{
throw new ApplicationException(Lans.g("JournalEntries", "Error. Credit and debit must both be positive."));
}
Crud.JournalEntryCrud.Update(je);
}
///<summary></summary>
public static long Insert(JournalEntry je)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
je.JournalEntryNum = Meth.GetLong(MethodBase.GetCurrentMethod(), je);
return(je.JournalEntryNum);
}
if (je.DebitAmt < 0 || je.CreditAmt < 0)
{
throw new ApplicationException(Lans.g("JournalEntries", "Error. Credit and debit must both be positive."));
}
return(Crud.JournalEntryCrud.Insert(je));
}
/*public static Employee[] GetListByExtension(){
* if(ListShort==null){
* return new Employee[0];
* }
* Employee[] arrayCopy=new Employee[ListShort.Length];
* ListShort.CopyTo(arrayCopy,0);
* int[] arrayKeys=new int[ListShort.Length];
* for(int i=0;i<ListShort.Length;i++){
* arrayKeys[i]=ListShort[i].PhoneExt;
* }
* Array.Sort(arrayKeys,arrayCopy);
* //List<Employee> retVal=new List<Employee>(ListShort);
* //retVal.Sort(
* return arrayCopy;
* }*/
///<summary></summary>
public static void Update(Employee Cur)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), Cur);
return;
}
if (Cur.LName == "" && Cur.FName == "")
{
throw new ApplicationException(Lans.g("FormEmployeeEdit", "Must include either first name or last name"));
}
Crud.EmployeeCrud.Update(Cur);
}
public static TreatPlan GetUnassigned(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <TreatPlan>(MethodBase.GetCurrentMethod(), patNum));
}
string command = "SELECT * FROM treatplan "
+ "WHERE PatNum=" + POut.Long(patNum) + " "
+ "AND TPStatus=" + POut.Int((int)TreatPlanStatus.Inactive) + " "
+ "AND Heading='" + POut.String(Lans.g("TreatPlans", "Unassigned")) + "'";
return(Crud.TreatPlanCrud.SelectOne(command) ?? new TreatPlan());
}
///<summary>Surround by try catch, because it will throw an exception if trying to delete a claimpayment attached to a deposit or if there are eobs attached.</summary>
public static void Delete(ClaimPayment cp)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), cp);
return;
}
//validate deposits
string command = "SELECT DepositNum FROM claimpayment "
+ "WHERE ClaimPaymentNum=" + POut.Long(cp.ClaimPaymentNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return;
}
if (table.Rows[0][0].ToString() != "0" && !HasAutoDeposit(cp)) //if claimpayment is already attached to a deposit and was not created automatically
{
#if !DEBUG
throw new ApplicationException(Lans.g("ClaimPayments", "Not allowed to delete a payment attached to a deposit."));
#endif
}
//validate eobs
command = "SELECT COUNT(*) FROM eobattach WHERE ClaimPaymentNum=" + POut.Long(cp.ClaimPaymentNum);
if (Db.GetScalar(command) != "0")
{
throw new ApplicationException(Lans.g("ClaimPayments", "Not allowed to delete this payment because EOBs are attached."));
}
if (table.Rows[0][0].ToString() != "0") //deposit was created automatically. Delete deposit.
{
Deposit deposit = Deposits.GetOne(cp.DepositNum);
if (deposit != null)
{
Deposits.Delete(deposit);
}
}
command = "UPDATE claimproc SET "
+ "DateInsFinalized='0001-01-01' "
+ "WHERE ClaimPaymentNum=" + POut.Long(cp.ClaimPaymentNum) + " "
+ "AND (SELECT SecDateEntry FROM claimpayment WHERE ClaimPaymentNum=" + POut.Long(cp.ClaimPaymentNum) + ")=CURDATE()";
Db.NonQ(command);
command = "UPDATE claimproc SET "
+ "ClaimPaymentNum=0 "
+ "WHERE claimpaymentNum=" + POut.Long(cp.ClaimPaymentNum);
//MessageBox.Show(string command);
Db.NonQ(command);
command = "DELETE FROM claimpayment "
+ "WHERE ClaimPaymentnum =" + POut.Long(cp.ClaimPaymentNum);
//MessageBox.Show(string command);
Db.NonQ(command);
}
///<summary></summary>
public static string RetrieveAndImport(Clearinghouse clearinghouseClin, bool isAutomaticMode, IODProgressExtended progress = null
, bool isTimeToRetrieve = false)
{
progress = progress ?? new ODProgressExtendedNull();
string errorMessage = "";
bool doRetrieveReports = isTimeToRetrieve || (!isAutomaticMode && IsTimeToRetrieveReports(isAutomaticMode, out errorMessage, progress));
if (doRetrieveReports) //Timer interval OK. Now we can retrieve the reports from web services.
{
if (!isAutomaticMode)
{
Prefs.UpdateDateT(PrefName.ClaimReportReceiveLastDateTime, DateTime.Now);
}
errorMessage = RetrieveReports(clearinghouseClin, isAutomaticMode, progress);
if (errorMessage != "")
{
progress.UpdateProgress(Lans.g(progress.LanThis, "Error getting reports, attempting to import manually downloaded reports."));
}
progress.UpdateProgress(Lans.g(progress.LanThis, "Report retrieval successful. Attempting to import."));
//Don't return yet even if there was an error. This is so that Open Dental will automatically import reports that have been manually
//downloaded to the Reports folder.
}
if (isAutomaticMode && clearinghouseClin.ResponsePath.Trim() == "")
{
return(""); //The user opened FormClaimsSend, or FormOpenDental called this function automatically.
}
if (progress.IsPauseOrCancel())
{
progress.UpdateProgress(Lans.g(progress.LanThis, "Canceled by user."));
return(errorMessage);
}
string importErrors = ImportReportFiles(clearinghouseClin, progress);
if (!string.IsNullOrWhiteSpace(importErrors))
{
if (string.IsNullOrWhiteSpace(errorMessage))
{
errorMessage = importErrors;
progress.UpdateProgress(Lans.g(progress.LanThis, "Error importing."));
}
else
{
errorMessage += "\r\n" + importErrors;
}
}
if (string.IsNullOrWhiteSpace(errorMessage) && string.IsNullOrWhiteSpace(importErrors))
{
progress.UpdateProgress(Lans.g(progress.LanThis, "Import successful."));
}
return(errorMessage);
}