///<summary>Use before Insert to determine if this County name already exists. Also used when closing patient edit window to validate that the Countyname exists.</summary>
public static bool DoesExist(string countyName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), countyName));
}
string command =
"SELECT * FROM county "
+ "WHERE CountyName = '" + POut.String(countyName) + "' ";
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return(false);
}
else
{
return(true);
}
}
///<summary>Returns a list of insplans that are dependent on the Cur employer. The list includes carriage returns for easy display. Used before deleting an employer to make sure employer is not in use.</summary>
public static string DependentInsPlans(Employer Cur){
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetString(MethodBase.GetCurrentMethod(),Cur);
}
string command="SELECT carrier.CarrierName,CONCAT(CONCAT(patient.LName,', '),patient.FName) "
+"FROM insplan "
+"LEFT JOIN inssub ON insplan.PlanNum=inssub.PlanNum "
+"LEFT JOIN patient ON inssub.Subscriber=patient.PatNum "
+"LEFT JOIN carrier ON insplan.CarrierNum=carrier.CarrierNum "
+"WHERE insplan.EmployerNum = "+POut.Long(Cur.EmployerNum);
DataTable table=Db.GetTable(command);
string retStr="";
for(int i=0;i<table.Rows.Count;i++){
if(i>0){
retStr+="\r\n";//return, newline for multiple names.
}
retStr+=PIn.String(table.Rows[i][1].ToString())+": "+PIn.String(table.Rows[i][0].ToString());
}
return retStr;
}
///<summary></summary>
public static bool IsReferralAttached(long referralNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), referralNum));
}
string command =
"SELECT * FROM refattach"
+ " WHERE ReferralNum = '" + referralNum + "'";
DataTable table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public static DataTable GetTableDataTypes()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod()));
}
Random rnd = new Random();
string rndStr = rnd.Next(1000000).ToString();
string command = "DROP TABLE IF EXISTS tempdt" + rndStr + @";"
+ "CREATE TABLE tempdt" + rndStr + @" (TString VARCHAR(50),TDecimal DECIMAL(10,2),TDateTime DATETIME);"
+ "INSERT INTO tempdt" + rndStr + @" (TString,TDecimal,TDateTime) VALUES ('string',123.45,DATE('2013-04-11'));";
Db.NonQ(command);
command = "SELECT * FROM tempdt" + rndStr + @";";
DataTable table = Db.GetTable(command);
command = "DROP TABLE IF EXISTS tempdt" + rndStr + @";";
Db.NonQ(command);
return(table);
}
private static DataTable GetSmsOutbound(DateTime dateTimeStart, DateTime dateTimeEnd)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateTimeStart, dateTimeEnd));
}
//-- Returns Count of outbound messages and total customer charges accrued.
string command = @"
SELECT
COUNT(*) NumMessages,
SUM(t.MsgChargeUSD) MsgChargeUSDTotal
FROM
smsmtterminated t
WHERE
t.MsgStatusCust IN(1,2,3,4)
AND t.DateTimeTerminated>=" + POut.DateT(dateTimeStart, true) + @"
AND t.DateTimeTerminated <" + POut.DateT(dateTimeEnd, true) + ";";
return(Db.GetTable(command));
}
///<summary>Provider(student) is required.</summary>
public static DataTable GetForCourseClass(long schoolCourse, long schoolClass)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), schoolCourse, schoolClass));
}
string command = "SELECT Descript,ReqNeededNum "
+ "FROM reqneeded ";
//if(schoolCourse==0){
// command+="WHERE ProvNum="+POut.PInt(provNum);
//}
//else{
command += "WHERE SchoolCourseNum=" + POut.Long(schoolCourse)
//+" AND ProvNum="+POut.PInt(provNum);
//}
+ " AND SchoolClassNum=" + POut.Long(schoolClass);
command += " ORDER BY Descript";
return(Db.GetTable(command));
}
///<summary>Returns a list of insplans that are dependent on the Cur carrier. Used to display in carrier edit.</summary>
public static List <string> DependentPlans(Carrier Cur)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), Cur));
}
string command = "SELECT CONCAT(CONCAT(LName,', '),FName) FROM patient,insplan,inssub"
+ " WHERE patient.PatNum=inssub.Subscriber"
+ " AND insplan.PlanNum=inssub.PlanNum"
+ " AND insplan.CarrierNum = '" + POut.Long(Cur.CarrierNum) + "'"
+ " ORDER BY LName,FName";
DataTable table = Db.GetTable(command);
List <string> retStr = new List <string>();
for (int i = 0; i < table.Rows.Count; i++)
{
retStr.Add(PIn.String(table.Rows[i][0].ToString()));
}
return(retStr);
}
///<summary>Returns only three columns from all ProcTPs -- TreatPlanNum, PatNum, and ProcNumOrig.</summary>
public static List <ProcTP> GetAllLim()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <ProcTP> >(MethodBase.GetCurrentMethod()));
}
string command = "SELECT TreatPlanNum,PatNum,ProcNumOrig FROM proctp";
DataTable table = Db.GetTable(command);
List <ProcTP> listProcTpsLim = new List <ProcTP>();
foreach (DataRow row in table.Rows)
{
ProcTP procTp = new ProcTP();
procTp.TreatPlanNum = PIn.Long(row["TreatPlanNum"].ToString());
procTp.PatNum = PIn.Long(row["PatNum"].ToString());
procTp.ProcNumOrig = PIn.Long(row["ProcNumOrig"].ToString());
listProcTpsLim.Add(procTp);
}
return(listProcTpsLim);
}
public static List <System.Windows.Media.Color> GetProdProvColors()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <System.Windows.Media.Color> >(MethodBase.GetCurrentMethod()));
}
string command = @"SELECT ProvColor
FROM provider WHERE IsHidden=0
ORDER BY ItemOrder" ;
DataTable table = Db.GetTable(command);
List <System.Windows.Media.Color> retVal = new List <System.Windows.Media.Color>();
for (int i = 0; i < table.Rows.Count; i++)
{
System.Drawing.Color dColor = System.Drawing.Color.FromArgb(PIn.Int(table.Rows[i]["ProvColor"].ToString()));
System.Windows.Media.Color mColor = System.Windows.Media.Color.FromArgb(dColor.A, dColor.R, dColor.G, dColor.B);
retVal.Add(mColor);
}
return(retVal);
}
///<summary>Only used once. Gets a list of subscriber names from the database that have the specified plan. Used to display in the insplan window. The returned list never includes the inssub that we're viewing.</summary>
public static List <string> GetSubscribersForPlan(long planNum, long excludeSub)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), planNum, excludeSub));
}
string command = "SELECT CONCAT(CONCAT(LName,', '),FName) "
+ "FROM inssub LEFT JOIN patient ON patient.PatNum=inssub.Subscriber "
+ "WHERE inssub.PlanNum=" + POut.Long(planNum) + " "
+ "AND inssub.InsSubNum !=" + POut.Long(excludeSub) + " "
+ " ORDER BY LName,FName";
DataTable table = Db.GetTable(command);
List <string> retStr = new List <string>(table.Rows.Count);
for (int i = 0; i < table.Rows.Count; i++)
{
retStr.Add(PIn.String(table.Rows[i][0].ToString()));
}
return(retStr);
}
///<summary>Gets all task lists for the repeating trunk.</summary>
public static List <TaskList> RefreshRepeatingTrunk()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TaskList> >(MethodBase.GetCurrentMethod()));
}
string command = "SELECT tasklist.*,"
+ "(SELECT COUNT(*) FROM taskancestor,task WHERE taskancestor.TaskListNum=tasklist.TaskListNum "
+ "AND task.TaskNum=taskancestor.TaskNum AND task.TaskStatus=" + POut.Int((int)TaskStatusEnum.New) + " "
+ "AND COALESCE(task.ReminderGroupId,'')='') NewTaskCount " //No reminder tasks
//I don't think the repeating trunk would ever track by user, so no special treatment here.
//Acutual behavior in both cases needs to be tested.
+ "FROM tasklist "
+ "WHERE Parent=0 "
+ "AND DateTL < " + POut.Date(new DateTime(1880, 01, 01)) + " "
+ "AND IsRepeating=1 "
+ "ORDER BY tasklist.Descript,tasklist.DateTimeEntry";
return(TableToList(Db.GetTable(command)));
}
///<summary>This is the query that was used prior to the job manager to lookup customer votes, pledges, and contact information for feature requests.</summary>
private static DataTable GetFeatureRequestContact(long featureRequestNum)
{
//No remoting role check needed; private method.
string command = "SELECT A.RequestID, A.LName, A.FName, A.ODPatNum, A.BillingType, A.Email, A.HmPhone, "
+ "A.WkPhone, A.WirelessPhone, A.Votes, A.AmountPledged AS AmountPledged_, A.DateVote "
+ "FROM "
+ "(SELECT 1 AS ItemOrder, request.RequestId, p.LName, p.FName, p.PatNum AS 'ODPatNum', '' AS BillingType, "
+ " p.Email, p.HmPhone, p.WkPhone, p.WirelessPhone, '' AS Votes, '' AS AmountPledged, request.DateTimeEntry AS 'DateVote' "
+ "FROM bugs.request INNER JOIN customers.Patient p ON p.PatNum = request.PatNum "
+ "WHERE bugs.request.RequestId =" + POut.Long(featureRequestNum) + " "
+ " UNION ALL "
+ "SELECT 2 AS ItemOrder, vote.RequestID AS RequestID, p.LName, p.FName, p.PatNum AS 'ODPatNum', def.ItemName AS BillingType, "
+ " p.Email, p.HmPhone, p.WkPhone, p.WirelessPhone, vote.Points AS Votes, vote.AmountPledged, vote.DateTStamp AS 'DateVote' "
+ "FROM bugs.vote INNER JOIN customers.Patient p ON p.PatNum = vote.PatNum INNER JOIN customers.definition def ON def.DefNum = p.BillingType "
+ " WHERE vote.RequestId =" + POut.Long(featureRequestNum) + " "
+ ") A "
+ "ORDER BY CAST(A.RequestID AS UNSIGNED INTEGER), A.ItemOrder";
return(Db.GetTable(command));
}
///<summary>Surround with try/catch.
///No need to pass in usernum, it is set before the remoting role and passed in for logging.</summary>
public static void Update(Carrier carrier, Carrier oldCarrier, long userNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), carrier, oldCarrier, userNum);
return;
}
string command;
DataTable table;
if (CultureInfo.CurrentCulture.Name.EndsWith("CA")) //Canadian. en-CA or fr-CA
{
if (carrier.IsCDA)
{
if (carrier.ElectID == "")
{
throw new ApplicationException(Lans.g("Carriers", "Carrier Identification Number required."));
}
if (!Regex.IsMatch(carrier.ElectID, "^[0-9]{6}$"))
{
throw new ApplicationException(Lans.g("Carriers", "Carrier Identification Number must be exactly 6 numbers."));
}
}
//so the edited carrier looks good, but now we need to make sure that the original was allowed to be changed.
command = "SELECT ElectID,IsCDA FROM carrier WHERE CarrierNum = '" + POut.Long(carrier.CarrierNum) + "'";
table = Db.GetTable(command);
if (PIn.Bool(table.Rows[0]["IsCDA"].ToString()) && //if original carrier IsCDA
PIn.String(table.Rows[0]["ElectID"].ToString()).Trim() != "" && //and the ElectID was already set
PIn.String(table.Rows[0]["ElectID"].ToString()) != carrier.ElectID) //and the ElectID was changed
{
command = "SELECT COUNT(*) FROM etrans WHERE CarrierNum= " + POut.Long(carrier.CarrierNum)
+ " OR CarrierNum2=" + POut.Long(carrier.CarrierNum);
if (Db.GetCount(command) != "0")
{
throw new ApplicationException(Lans.g("Carriers", "Not allowed to change Carrier Identification Number because it's in use in the claim history."));
}
}
}
Crud.CarrierCrud.Update(carrier, oldCarrier);
InsEditLogs.MakeLogEntry(carrier, oldCarrier, InsEditLogType.Carrier, userNum);
}
///<summary>Gets the last batch number from db for the HQ version of this clearinghouseClin and increments it by one.
///Then saves the new value to db and returns it. So even if the new value is not used for some reason, it will have already been incremented.
///Remember that LastBatchNumber is never accurate with local data in memory.</summary>
public static int GetNextBatchNumber(Clearinghouse clearinghouseClin)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetInt(MethodBase.GetCurrentMethod(), clearinghouseClin));
}
//get last batch number
string command = "SELECT LastBatchNumber FROM clearinghouse "
+ "WHERE ClearinghouseNum = " + POut.Long(clearinghouseClin.HqClearinghouseNum);
DataTable table = Db.GetTable(command);
int batchNum = PIn.Int(table.Rows[0][0].ToString());
//and increment it by one
if (clearinghouseClin.Eformat == ElectronicClaimFormat.Canadian)
{
if (batchNum == 999999)
{
batchNum = 1;
}
else
{
batchNum++;
}
}
else
{
if (batchNum == 999)
{
batchNum = 1;
}
else
{
batchNum++;
}
}
//save the new batch number. Even if user cancels, it will have incremented.
command = "UPDATE clearinghouse SET LastBatchNumber=" + batchNum.ToString()
+ " WHERE ClearinghouseNum = " + POut.Long(clearinghouseClin.HqClearinghouseNum);
Db.NonQ(command);
return(batchNum);
}
///<summary>Should only be called from the PhoneTrackingServer which will be invoking this every ~1.6 seconds.
///Inserts a new entry into the triage metric table that all workstations will start to select from in order to fill local metrics.</summary>
public static void InsertTriageMetric()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod());
return;
}
//The following query was being run by every workstation in the office every 1.6 seconds which was causing slowness issues.
//The query might need to be improved but for now we are just removing it from the workstations and having the PTS be the only entity running it.
string command = @"SELECT
COALESCE(SUM(CASE WHEN PriorityDefNum=502 THEN 1 END),0) AS CountBlueTasks,-- triage blue
COALESCE(SUM(CASE WHEN PriorityDefNum=503 THEN 1 END),0) AS CountWhiteTasks,-- triage white
COALESCE(SUM(CASE WHEN PriorityDefNum=501 THEN 1 END),0) AS CountRedTasks,-- triage red
-- time of oldest triage task or the oldest tasknote if one exists
COALESCE(MIN(CASE WHEN PriorityDefNum=502 THEN (SELECT GREATEST(IFNULL(task.DateTimeEntry,'0001-01-01'),
IFNULL((SELECT MAX(DateTimeNote)
FROM tasknote WHERE tasknote.tasknum=task.tasknum),'0001-01-01'))) END),'0001-01-01') AS TimeOfOldestBlueTaskNote,
-- time of oldest urgent task or the oldest tasknote if one exists
COALESCE(MIN(CASE WHEN PriorityDefNum=501 THEN (SELECT GREATEST(IFNULL(task.DateTimeEntry,'0001-01-01'),
IFNULL((SELECT MAX(DateTimeNote)
FROM tasknote WHERE tasknote.tasknum=task.tasknum),'0001-01-01'))) END),'0001-01-01') AS TimeOfOldestRedTaskNote
FROM task
WHERE TaskListNum=1697 -- Triage task list
AND TaskStatus!=2 -- Not done (new or viewed)" ;
DataTable table = Db.GetTable(command);
if (table == null || table.Rows == null || table.Rows.Count < 1)
{
return;
}
TriageMetric triageMetric = new TriageMetric()
{
CountBlueTasks = PIn.Int(table.Rows[0]["CountBlueTasks"].ToString()),
CountWhiteTasks = PIn.Int(table.Rows[0]["CountWhiteTasks"].ToString()),
CountRedTasks = PIn.Int(table.Rows[0]["CountRedTasks"].ToString()),
DateTimeOldestTriageTaskOrTaskNote = PIn.DateT(table.Rows[0]["TimeOfOldestBlueTaskNote"].ToString()),
DateTimeOldestUrgentTaskOrTaskNote = PIn.DateT(table.Rows[0]["TimeOfOldestRedTaskNote"].ToString()),
};
Crud.TriageMetricCrud.Insert(triageMetric);
}
public static DataTable GetHouseCalls(DateTime FromDate, DateTime ToDate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), FromDate, ToDate));
}
//now, the query--------------------------------------------------------------------------
//Appointment Reminder Fields- numbers are as they come back from db-----------------------
//0-LastName
//1-FirstName (or we substitute 2-Preferred Name if exists)
// PatientNumber (Can be 3-PatNum or 4-ChartNumber, depending on what user selected)
//5-HomePhone
//6-WorkNumber
//7-EmailAddress
// SendEmail (this will be true if email address exists. Might change later)
//8-Address
//9-Address2 (although they did not offer this as an option)
//10-City
//11-State
//12-Zip
//13-ApptDate
//13-ApptTime
//14-ApptReason (procedures descriptions-user can't edit)
//15-DoctorNumber (for the Doctor, we currently use the patient primary provider. Otherwise, we would run into trouble with appointments assigned to a specific hygienist.)
//15-DoctorName
//16-IsNewPatient
string command = @"SELECT patient.LName,patient.FName,patient.Preferred
,patient.PatNum,patient.ChartNumber,patient.HmPhone,patient.WkPhone
,patient.Email,patient.Address,patient.Address2,patient.City,patient.State
,patient.Zip
,appointment.AptDateTime,appointment.ProcDescript
,patient.PriProv
,appointment.IsNewPatient
FROM patient,appointment
WHERE patient.PatNum=appointment.PatNum "
+ "AND (appointment.AptStatus=1 OR appointment.AptStatus=4) " //sched or ASAP
+ "AND appointment.AptDateTime > " + POut.Date(FromDate) //> midnight
+ " AND appointment.AptDateTime < " + POut.Date(ToDate.AddDays(1)); //< midnight
return(Db.GetTable(command));
}
///<summary>If trying to change the amount and attached to a deposit, it will throw an error, so surround with try catch.</summary>
public static void Update(ClaimPayment cp, bool isDepNew = false)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), cp, isDepNew);
return;
}
if (!isDepNew && cp.DepositNum != 0 && PrefC.GetBool(PrefName.ShowAutoDeposit))
{
string cmd = "SELECT deposit.Amount,SUM(COALESCE(claimpayment.CheckAmt,0))+SUM(COALESCE(payment.PayAmt,0)) depAmtOthers "
+ "FROM deposit "
+ "LEFT JOIN payment ON payment.DepositNum=deposit.DepositNum "
+ "LEFT JOIN claimpayment ON claimpayment.DepositNum=deposit.DepositNum AND claimpayment.ClaimPaymentNum!=" + POut.Long(cp.ClaimPaymentNum) + " "
+ "WHERE deposit.DepositNum=" + POut.Long(cp.DepositNum);
DataTable tble = Db.GetTable(cmd);
if (tble.Rows.Count == 0)
{
cp.DepositNum = 0;
}
else if (PIn.Double(tble.Rows[0]["depAmtOthers"].ToString()) + cp.CheckAmt != PIn.Double(tble.Rows[0]["Amount"].ToString()))
{
throw new ApplicationException(Lans.g("ClaimPayments", "Not allowed to change the amount on checks attached to deposits."));
}
}
else
{
string command = "SELECT DepositNum,CheckAmt 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" && //if claimpayment is already attached to a deposit
PIn.Double(table.Rows[0][1].ToString()) != cp.CheckAmt) //and checkAmt changes
{
throw new ApplicationException(Lans.g("ClaimPayments", "Not allowed to change the amount on checks attached to deposits."));
}
}
Crud.ClaimPaymentCrud.Update(cp);
}
///<summary>Get the list of records for the pending plan deletion report for plans that need to be brought to the patient's attention.</summary>
public static DataTable GetPendingDeletionTable(Collection <string[]> deletePatientRecords)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), deletePatientRecords));
}
string whereTrojanID = "";
for (int i = 0; i < deletePatientRecords.Count; i++)
{
if (i > 0)
{
whereTrojanID += "OR ";
}
whereTrojanID += "i.TrojanID='" + deletePatientRecords[i][0] + "' ";
}
string command = "SELECT DISTINCT " +
"p.FName," +
"p.LName," +
"p.FName," +
"p.LName," +
"p.SSN," +
"p.Birthdate," +
"i.GroupNum," +
"s.SubscriberID," +
"i.TrojanID," +
"CASE i.EmployerNum WHEN 0 THEN '' ELSE e.EmpName END," +
"CASE i.EmployerNum WHEN 0 THEN '' ELSE e.Phone END," +
"c.CarrierName," +
"c.Phone " +
"FROM patient p,insplan i,employer e,carrier c,inssub s " +
"WHERE p.PatNum=s.Subscriber AND " +
"(" + whereTrojanID + ") AND " +
"i.CarrierNum=c.CarrierNum AND " +
"s.PlanNum=i.PlanNum AND " +
"(i.EmployerNum=e.EmployerNum OR i.EmployerNum=0) AND " +
"(SELECT COUNT(*) FROM patplan a WHERE a.PatNum=p.PatNum AND a.InsSubNum=s.InsSubNum) > 0 " +
"ORDER BY i.TrojanID,p.LName,p.FName";
return(Db.GetTable(command));
}
public static List <Etrans835Attach> GetForEtransNumOrClaimNums(bool isSimple, long etransNum = 0, params long[] listClaimNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Etrans835Attach> >(MethodBase.GetCurrentMethod(), isSimple, etransNum, listClaimNums));
}
List <string> listWhereClauses = new List <string>();
if (listClaimNums.Length != 0)
{
listWhereClauses.Add("etrans835attach.ClaimNum IN (" + String.Join(",", listClaimNums.Select(x => POut.Long(x))) + ")");
}
if (etransNum != 0) //Manually detached rows will have claimNum 0.
{
listWhereClauses.Add("(etrans.EtransNum=" + POut.Long(etransNum) + " AND etrans835attach.ClaimNum=0)");
}
if (listWhereClauses.Count == 0)
{
return(new List <Etrans835Attach>());
}
string command = "SELECT etrans835attach.* "
+ (isSimple?"":",etrans.DateTimeTrans ")
+ "FROM etrans835attach "
+ (isSimple?"":"INNER JOIN etrans ON etrans.EtransNum=etrans835attach.EtransNum ")
+ "WHERE " + string.Join(" OR ", listWhereClauses);
DataTable table = Db.GetTable(command);
if (isSimple)
{
return(Crud.Etrans835AttachCrud.TableToList(table));
}
List <Etrans835Attach> listAttaches = Crud.Etrans835AttachCrud.TableToList(table);
for (int i = 0; i < listAttaches.Count; i++)
{
Etrans835Attach attach = listAttaches[i];
DataRow row = table.Rows[i];
attach.DateTimeTrans = PIn.DateT(row["DateTimeTrans"].ToString());
}
return(listAttaches);
}
///<summary>Returns a dicitonary such that the key is a clinicNum and the value is a count of patients whith a matching patient.ClinicNum</summary>
public static SerializableDictionary <long, int> GetClinicalPatientCount()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetSerializableDictionary <long, int>(MethodBase.GetCurrentMethod()));
}
SerializableDictionary <long, int> retVal = new SerializableDictionary <long, int>();
string command =
@"SELECT ClinicNum, COUNT(*) AS Count
FROM patient
GROUP BY ClinicNum" ;
DataTable table = Db.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
long clinicNum = PIn.Long(table.Rows[i]["ClinicNum"].ToString());
int clinicCount = PIn.Int(table.Rows[i]["Count"].ToString());
retVal.Add(clinicNum, clinicCount);
}
return(retVal);
}
///<summary>All repeating items for one date type with no heirarchy.</summary>
public static List <Task> RefreshRepeating(TaskDateType dateType, long currentUserNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Task> >(MethodBase.GetCurrentMethod(), dateType, currentUserNum));
}
string command =
"SELECT task.*, "
+ "(SELECT COUNT(*) FROM taskunread WHERE task.TaskNum=taskunread.TaskNum "
+ "AND taskunread.UserNum=" + POut.Long(currentUserNum) + ") IsUnread, " //Not sure if this makes sense here
+ "(SELECT LName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") LName, "
+ "(SELECT FName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") FName, "
+ "(SELECT Preferred FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") Preferred "
+ "FROM task "
+ "WHERE IsRepeating=1 "
+ "AND DateType=" + POut.Long((int)dateType) + " "
+ "ORDER BY DateTimeEntry";
DataTable table = Db.GetTable(command);
return(TableToList(table));
}
///<summary>Gets all 'new' tasks for a user.</summary>
public static List <Task> RefreshUserNew(long userNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Task> >(MethodBase.GetCurrentMethod(), userNum));
}
string command = "SELECT task.*,1 AS IsUnread, "
//we fill the IsUnread column with 1's because we already know that they are all unread
+ "(SELECT tasklist.Descript FROM tasklist WHERE task.TaskListNum=tasklist.TaskListNum) ParentDesc, "
+ "(SELECT LName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") LName, "
+ "(SELECT FName FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") FName, "
+ "(SELECT Preferred FROM patient WHERE task.KeyNum=patient.PatNum AND task.ObjectType=" + POut.Int((int)TaskObjectType.Patient) + ") Preferred "
+ "FROM task,taskunread "
+ "WHERE task.TaskNum=taskunread.TaskNum "
+ "AND taskunread.UserNum = " + POut.Long(userNum) + " "
+ "GROUP BY task.TaskNum " //in case there are duplicate unreads
+ "ORDER BY task.DateTimeEntry";
DataTable table = Db.GetTable(command);
return(TableToList(table));
}
///<summary>Returns a list of patient names that are attached to this referral. Used to display in the referral edit window.</summary>
public static string[] GetPats(long refNum, bool IsFrom)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <string[]>(MethodBase.GetCurrentMethod(), refNum, IsFrom));
}
string command = "SELECT CONCAT(CONCAT(patient.LName,', '),patient.FName) "
+ "FROM patient,refattach,referral "
+ "WHERE patient.PatNum=refattach.PatNum "
+ "AND refattach.ReferralNum=referral.ReferralNum "
+ "AND refattach.IsFrom=" + POut.Bool(IsFrom)
+ " AND referral.ReferralNum=" + refNum.ToString();
DataTable table = Db.GetTable(command);
string[] retStr = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
retStr[i] = PIn.String(table.Rows[i][0].ToString());
}
return(retStr);
}
/*///<summary>Returns header widths for list sorted in the same order as the columns appear in the DB. Can be more efficient than using cache.</summary>
* public static List<WikiListHeaderWidth> GetForListNoCache(string listName) {
* if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
* return Meth.GetObject<List<WikiListHeaderWidth>>(MethodBase.GetCurrentMethod(),listName);
* }
* List<WikiListHeaderWidth> retVal = new List<WikiListHeaderWidth>();
* List<WikiListHeaderWidth> tempList = new List<WikiListHeaderWidth>();
* string command="DESCRIBE wikilist_"+POut.String(listName);
* DataTable listDescription=Db.GetTable(command);
* command="SELECT * FROM wikilistheaderwidth WHERE ListName='"+POut.String(listName)+"'";
* tempList=Crud.WikiListHeaderWidthCrud.SelectMany(command);
* for(int i=0;i<listDescription.Rows.Count;i++) {
* for(int j=0;j<tempList.Count;j++) {
* //Add WikiListHeaderWidth from tempList to retVal if it is the next row in listDescription.
* if(listDescription.Rows[i][0].ToString()==tempList[j].ColName) {
* retVal.Add(tempList[j]);
* break;
* }
* }
* //next description row.
* }
* return retVal;
* }*/
///<summary>Returns header widths for list sorted in the same order as the columns appear in the DB. Uses cache.</summary>
public static List <WikiListHeaderWidth> GetForList(string listName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <WikiListHeaderWidth> >(MethodBase.GetCurrentMethod(), listName));
}
List <WikiListHeaderWidth> retVal = new List <WikiListHeaderWidth>();
string command = "DESCRIBE wikilist_" + POut.String(listName);//TODO: Oracle compatible?
DataTable tableDescripts = Db.GetTable(command);
List <WikiListHeaderWidth> listHeaderWidths = GetWhere(x => x.ListName == listName);
for (int i = 0; i < tableDescripts.Rows.Count; i++)
{
WikiListHeaderWidth addWidth = listHeaderWidths.Where(x => x.ColName == tableDescripts.Rows[i][0].ToString()).FirstOrDefault();
if (addWidth != null)
{
retVal.Add(addWidth);
}
}
return(retVal);
}
private static List <VersionRelease> RefreshAndFill(string command)
{
DataTable table = Db.GetTable(command);
List <VersionRelease> retVal = new List <VersionRelease>();
VersionRelease vers;
for (int i = 0; i < table.Rows.Count; i++)
{
vers = new VersionRelease();
vers.VersionReleaseId = PIn.Int(table.Rows[i]["VersionReleaseId"].ToString());
vers.MajorNum = PIn.Int(table.Rows[i]["MajorNum"].ToString());
vers.MinorNum = PIn.Int(table.Rows[i]["MinorNum"].ToString());
vers.BuildNum = PIn.Int(table.Rows[i]["BuildNum"].ToString());
vers.IsForeign = PIn.Bool(table.Rows[i]["IsForeign"].ToString());
vers.DateRelease = PIn.Date(table.Rows[i]["DateRelease"].ToString());
vers.IsBeta = PIn.Bool(table.Rows[i]["IsBeta"].ToString());
vers.HasConvertScript = PIn.Bool(table.Rows[i]["HasConvertScript"].ToString());
retVal.Add(vers);
}
return(retVal);
}
///<summary>It is known that patNumStr is not empty</summary>
public static DataTable GetTableRaw(bool noIns, int monthStart, string patNumStr)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), noIns, monthStart, patNumStr));
}
string command = $@"SELECT patient.PatNum,patient.LName,patient.FName,patient.Email,patient.HmPhone,patient.WirelessPhone,patient.WkPhone,
patient.PreferRecallMethod,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip,patient.PriProv,patient.BillingType,
patplan.PatPlanNum,inssub.InsSubNum,inssub.PlanNum,COALESCE(carrier.CarrierName,'') carrierName,COALESCE(clinic.Abbr,'Unassigned') clinicAbbr
FROM patient
LEFT JOIN patplan ON patient.PatNum=patplan.PatNum
LEFT JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum
LEFT JOIN insplan ON insplan.PlanNum=inssub.PlanNum
LEFT JOIN carrier ON insplan.CarrierNum=carrier.CarrierNum
LEFT JOIN clinic ON clinic.ClinicNum=patient.ClinicNum
WHERE patient.PatStatus={POut.Int((int)PatientStatus.Patient)}
AND patient.PatNum IN ({patNumStr}){(noIns?"":$@"
AND patplan.Ordinal=1 AND insplan.MonthRenew={POut.Int(monthStart)}")}" ;
return(Db.GetTable(command));
}
///<summary>Returns a list of patients that are dependent on the Cur employer. The list includes carriage returns for easy display. Used before deleting an employer to make sure employer is not in use.</summary>
public static string DependentPatients(Employer Cur)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod(), Cur));
}
string command = "SELECT CONCAT(CONCAT(LName,', '),FName) FROM patient"
+ " WHERE EmployerNum = '" + POut.Long(Cur.EmployerNum) + "'";
DataTable table = Db.GetTable(command);
string retStr = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (i > 0)
{
retStr += "\r\n"; //return, newline for multiple names.
}
retStr += PIn.String(table.Rows[i][0].ToString());
}
return(retStr);
}
public static int GetDuplicateBlockoutCount()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetInt(MethodBase.GetCurrentMethod()));
}
string command = @"SELECT COUNT(*) countDups,SchedDate,MAX(schedule.ScheduleNum) ScheduleNum," //MAX on id as to not change query behavior from prior to MySQL/Oracle independence.
+ @"(SELECT " + DbHelper.GroupConcat("so1.OperatoryNum", false, true) + @" FROM scheduleop so1 WHERE so1.ScheduleNum=schedule.ScheduleNum) AS ops
FROM schedule
WHERE SchedType=2
GROUP BY SchedDate,ops,StartTime,StopTime
HAVING countDups > 1" ;
DataTable table = Db.GetTable(command);
int retVal = 0;
for (int i = 0; i < table.Rows.Count; i++)
{
retVal += PIn.Int(table.Rows[i][0].ToString()) - 1;
}
return(retVal);
}
//also table with special chars: |, <, >, &, ', ", and \
public static DataTable GetTableSpecialChars()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod()));
}
//Special characters in the columns as well as in the column names
string command = "SELECT '" + POut.String("cell00|") + "' AS '|<>','" + POut.String("cell01<") + "' AS '&\\'\"\\\\' "
+ "UNION ALL "
+ "SELECT '" + POut.String("cell10>") + "','" + POut.String("cell11&") + "' "
+ "UNION ALL "
+ "SELECT '" + POut.String("cell20\'") + "','" + POut.String("cell21\"") + "' "
+ "UNION ALL "
+ "SELECT '" + POut.String("cell30\\") + "','" + POut.String("cell31/") + "'";
DataTable table = Db.GetTable(command);
table.TableName = "Table|<>&'\"\\";
table.Columns.Add("DirtyString");
table.Rows[0]["DirtyString"] = DirtyString;
return(table);
}
public static RegistrationKey GetByKey(string regKey)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <RegistrationKey>(MethodBase.GetCurrentMethod(), regKey));
}
if (!Regex.IsMatch(regKey, @"^[A-Z0-9]{16}$"))
{
throw new ApplicationException("Invalid registration key format.");
}
string command = "SELECT * FROM registrationkey WHERE RegKey='" + POut.String(regKey) + "'";
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
throw new ApplicationException("Invalid registration key.");
}
RegistrationKey key = null;
for (int i = 0; i < table.Rows.Count; i++)
{
key = new RegistrationKey();
key.RegistrationKeyNum = PIn.Int(table.Rows[i][0].ToString());
key.PatNum = PIn.Int(table.Rows[i][1].ToString());
key.RegKey = PIn.String(table.Rows[i][2].ToString());
key.Note = PIn.String(table.Rows[i][3].ToString());
key.DateStarted = PIn.Date(table.Rows[i][4].ToString());
key.DateDisabled = PIn.Date(table.Rows[i][5].ToString());
key.DateEnded = PIn.Date(table.Rows[i][6].ToString());
key.IsForeign = PIn.Bool(table.Rows[i][7].ToString());
//key.UsesServerVersion =PIn.PBool(table.Rows[i][8].ToString());
key.IsFreeVersion = PIn.Bool(table.Rows[i][9].ToString());
key.IsOnlyForTesting = PIn.Bool(table.Rows[i][10].ToString());
//key.VotesAllotted =PIn.PInt(table.Rows[i][11].ToString());
}
//if(key.DateDisabled.Year>1880){
// throw new ApplicationException("This key has been disabled. Please call for assistance.");
//}
return(key);
}
