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);
}
///<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));
}
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>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 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));
}
//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);
}
///<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));
}
public static DataTable GetGroupedTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, string procCode, bool hasAllProvs)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, procCode, hasAllProvs));
}
string query = "SELECT procs.ItemName,procs.ProcCode,procs.Descript,";
if (DataConnection.DBtype == DatabaseType.MySql)
{
query += "Count(*),FORMAT(ROUND(AVG(procs.fee),2),2) $AvgFee,SUM(procs.fee) AS $TotFee ";
}
else //Oracle needs quotes.
{
query += "Count(*),AVG(procs.fee) \"$AvgFee\",SUM(procs.fee) AS \"$TotFee\" ";
}
query += "FROM ( "
+ "SELECT procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits) -COALESCE(SUM(claimproc.WriteOff),0) fee, "
+ "procedurecode.ProcCode, procedurecode.Descript, definition.ItemName, definition.ItemOrder "
+ "FROM procedurelog "
+ "INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum "
+ "INNER JOIN definition ON definition.DefNum=procedurecode.ProcCat "
+ "LEFT JOIN claimproc ON claimproc.ProcNum=procedurelog.ProcNum AND claimproc.Status=" + POut.Int((int)ClaimProcStatus.CapComplete) + " "
+ "WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " ";
if (!hasAllProvs)
{
query += "AND procedurelog.ProvNum IN (" + String.Join(",", listProvNums) + ") ";
}
if (ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics))))
{
query += "AND procedurelog.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
query += "AND procedurecode.ProcCode LIKE '%" + POut.String(procCode) + "%' "
+ "AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " "
+ "GROUP BY procedurelog.ProcNum ) procs "
+ "GROUP BY procs.ProcCode "
+ "ORDER BY procs.ItemOrder,procs.ProcCode";
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(query)));
}
///<summary></summary>
public static DataTable GetInsCoTable(string carrier)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), carrier));
}
string query = "SELECT carrier.CarrierName"
+ ",CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) AS SubscriberName,carrier.Phone,"
+ "insplan.Groupname "
+ "FROM insplan,inssub,patient,carrier " //,patplan "//we only include patplan to make sure insurance is active for a patient. We don't want any info from patplan.
+ "WHERE inssub.Subscriber=patient.PatNum "
+ "AND inssub.PlanNum=insplan.PlanNum "
+ "AND EXISTS (SELECT * FROM patplan WHERE patplan.InsSubNum=inssub.InsSubNum) "
//+"AND insplan.PlanNum=patplan.PlanNum "
//+"AND patplan.PatNum=patient.PatNum "
//+"AND patplan.Ordinal=1 "
+ "AND carrier.CarrierNum=insplan.CarrierNum "
+ "AND carrier.CarrierName LIKE '" + POut.String(carrier) + "%' "
+ "ORDER BY carrier.CarrierName,patient.LName";
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query)));
}
///<summary>Gets all Evaluations from the DB. Multiple filters are available. Dates must be valid before calling this.</summary>
public static DataTable GetFilteredList(DateTime dateStart, DateTime dateEnd, string lastName, string firstName, long uniqueID, long courseNum, long instructorNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, lastName, firstName, uniqueID, courseNum, instructorNum));
}
string command = "SELECT evaluation.EvaluationNum,evaluation.EvalTitle,evaluation.DateEval,evaluation.StudentNum,evaluation.InstructNum,"
+ "stu.LName,stu.FName,schoolcourse.CourseID,gradingscale.Description,evaluation.OverallGradeShowing FROM evaluation "
+ "INNER JOIN provider ins ON ins.ProvNum=evaluation.InstructNum "
+ "INNER JOIN provider stu ON stu.ProvNum=evaluation.StudentNum "
+ "INNER JOIN schoolcourse ON schoolcourse.SchoolCourseNum=evaluation.SchoolCourseNum "
+ "INNER JOIN gradingscale ON gradingscale.GradingScaleNum=evaluation.GradingScaleNum "
+ "WHERE TRUE";
if (!String.IsNullOrWhiteSpace(lastName))
{
command += " AND stu.LName LIKE '%" + POut.String(lastName) + "%'";
}
if (!String.IsNullOrWhiteSpace(firstName))
{
command += " AND stu.FName LIKE '%" + POut.String(firstName) + "%'";
}
if (uniqueID != 0)
{
command += " AND evaluation.StudentNum = '" + POut.Long(uniqueID) + "'";
}
if (courseNum != 0)
{
command += " AND schoolcourse.SchoolCourseNum = '" + POut.Long(courseNum) + "'";
}
if (instructorNum != 0)
{
command += " AND evaluation.InstructNum = '" + POut.Long(instructorNum) + "'";
}
command += " AND evaluation.DateEval BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd);
command += " ORDER BY DateEval,LName";
return(Db.GetTable(command));
}
public static DataTable RefreshManyStudents(long classNum, long courseNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), classNum, courseNum));
}
DataTable table = new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("donereq");
table.Columns.Add("FName");
table.Columns.Add("LName");
table.Columns.Add("studentNum"); //ProvNum
table.Columns.Add("totalreq"); //not used yet. It will be changed to be based upon reqneeded. Or not used at all.
string command = "SELECT COUNT(DISTINCT req2.ReqStudentNum) donereq,FName,LName,provider.ProvNum,"
+ "COUNT(DISTINCT req1.ReqStudentNum) totalreq "
+ "FROM provider "
+ "LEFT JOIN reqstudent req1 ON req1.ProvNum=provider.ProvNum AND req1.SchoolCourseNum=" + POut.Long(courseNum) + " "
+ "LEFT JOIN reqstudent req2 ON req2.ProvNum=provider.ProvNum AND " + DbHelper.Year("req2.DateCompleted") + " > 1880 "
+ "AND req2.SchoolCourseNum=" + POut.Long(courseNum) + " "
+ "WHERE provider.SchoolClassNum=" + POut.Long(classNum)
+ " GROUP BY FName,LName,provider.ProvNum "
+ "ORDER BY LName,FName";
DataTable raw = Db.GetTable(command);
for (int i = 0; i < raw.Rows.Count; i++)
{
row = table.NewRow();
row["donereq"] = raw.Rows[i]["donereq"].ToString();
row["FName"] = raw.Rows[i]["FName"].ToString();
row["LName"] = raw.Rows[i]["LName"].ToString();
row["studentNum"] = raw.Rows[i]["ProvNum"].ToString();
row["totalreq"] = raw.Rows[i]["totalreq"].ToString();
table.Rows.Add(row);
}
return(table);
}
///<summary>It is known that patNumStr is not empty</summary>
public static DataTable GetAnnualMaxInfo(string patNumStr)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNumStr));
}
string commandAnnualMax = $@"SELECT benefit.PlanNum,
MAX(CASE WHEN CoverageLevel!={POut.Int((int)BenefitCoverageLevel.Family)} THEN MonetaryAmt ELSE -1 END) AnnualMaxInd,
MAX(CASE WHEN CoverageLevel={POut.Int((int)BenefitCoverageLevel.Family)} THEN MonetaryAmt ELSE -1 END) AnnualMaxFam
FROM benefit
INNER JOIN inssub ON inssub.PlanNum=benefit.PlanNum
INNER JOIN patplan ON patplan.InsSubNum=inssub.InsSubNum
LEFT JOIN covcat ON benefit.CovCatNum=covcat.CovCatNum
WHERE COALESCE(covcat.EbenefitCat,{POut.Int((int)EbenefitCategory.General)})={POut.Int((int)EbenefitCategory.General)}
AND benefit.BenefitType={POut.Int((int)InsBenefitType.Limitations)}
AND benefit.MonetaryAmt>0
AND benefit.QuantityQualifier={POut.Int((int)BenefitQuantity.None)}
AND patplan.PatNum IN ({patNumStr})
GROUP BY benefit.PlanNum
ORDER BY NULL" ;//Removes filesort reference from query explain
return(Db.GetTable(commandAnnualMax));
}
///<summary></summary>
public static DataTable GetPrescriptionTable(bool isRadioPatient, string inputText)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), isRadioPatient, inputText));
}
string query = "SELECT CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName)," +
"' '),patient.MiddleI),rxpat.rxdate,"
+ "rxpat.drug,rxpat.sig,rxpat.disp,provider.abbr FROM patient,rxpat,provider "
+ "WHERE patient.patnum=rxpat.patnum AND provider.provnum=rxpat.provnum ";
if (isRadioPatient)
{
query += "AND patient.lname like '" + POut.String(inputText) + "%'"
+ " ORDER BY patient.lname,patient.fname,rxpat.rxdate";
}
else
{
query += "AND rxpat.drug like '" + POut.String(inputText) + "%'"
+ " ORDER BY patient.lname,rxpat.drug,rxpat.rxdate";
}
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query)));
}
///<summary>Gets all claimpayments of the specified claimpayment type, within the specified date range and from the specified clinic.
///0 for clinics means all clinics, 0 for claimpaytype means all types.</summary>
public static DataTable GetForDateRange(DateTime dateFrom, DateTime dateTo, long clinicNum, long claimpayGroup)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, clinicNum, claimpayGroup));
}
string command = "SELECT claimpayment.*,"
+ "(CASE WHEN (SELECT COUNT(*) FROM eobattach WHERE eobattach.ClaimPaymentNum=claimpayment.ClaimPaymentNum)>0 THEN 1 ELSE 0 END) hasEobAttach "
+ "FROM claimpayment "
+ "WHERE CheckDate >= " + POut.Date(dateFrom) + " "
+ "AND CheckDate <= " + POut.Date(dateTo) + " ";
if (clinicNum != 0)
{
command += "AND ClinicNum=" + POut.Long(clinicNum) + " ";
}
if (claimpayGroup != 0)
{
command += "AND PayGroup=" + POut.Long(claimpayGroup) + " ";
}
command += "ORDER BY CheckDate";
return(Db.GetTable(command));
}
public static DataTable GetAccountTotalByType(DateTime asOfDate, AccountType acctType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), asOfDate, acctType));
}
string sumTotalStr = "";
if (acctType == AccountType.Asset)
{
sumTotalStr = "SUM(ROUND(DebitAmt,3)-ROUND(CreditAmt,3))";
}
else //Liability or equity
{
sumTotalStr = "SUM(ROUND(CreditAmt,3)-ROUND(DebitAmt,3))";
}
string command = "SELECT Description, " + sumTotalStr + " SumTotal, AcctType "
+ "FROM account, journalentry "
+ "WHERE account.AccountNum=journalentry.AccountNum AND DateDisplayed <= " + POut.Date(asOfDate) + " AND AcctType=" + POut.Int((int)acctType) + " "
+ "GROUP BY account.AccountNum "
+ "ORDER BY Description, DateDisplayed ";
return(Db.GetTable(command));
}
///<summary>Currently only for user queries. The connection must already be opened before calling this method.
///Fills and returns a DataTable from the database.
///Throws an exception if a connection could not be found via the passed in server thread.</summary>
public static DataTable GetTableConAlreadyOpen(int serverThread, string command, bool isSqlValidated)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), serverThread, command, isSqlValidated));
}
//If the dictionary does not contain the ServerThread key, then something went wrong. Just stop and throw.
MySqlConnection con;
if (!_dictCons.TryGetValue(serverThread, out con))
{
throw new ApplicationException("Critical error in GetTableConAlreadyOpen: A connection could not be found via the given server thread ID.");
}
if (!isSqlValidated && !Db.IsSqlAllowed(command)) //Throws Exception if Sql is not allowed, which is handled by the ExceptionThreadHandler and output in a MsgBox
{
throw new ApplicationException("Error: Command is either not safe or user does not have permission.");
}
//At this point, we know that _dictCons contains the current connection's ServerThread ID.
DataTable table = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(new MySqlCommand(command, con));
try {
Db.LastCommand = command;
QueryMonitor.RunMonitoredQuery(() =>
DataCore.ExecuteQueryFunc(() => da.Fill(table)),
command
);
}
finally {
con.Close(); //if the query was stopped or has finished executing, this will close the connection that it was executing on.
lock (_lockObj) {
_dictCons.Remove(serverThread);
}
}
return(table);
}
public static DataTable GetBirthdayTable(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
string dateWhereClause;
string orderByClause;
if (dateFrom.Year == dateTo.Year)
{
dateWhereClause = "SUBSTRING(Birthdate,6,5) >= '" + dateFrom.ToString("MM-dd") + "' "
+ "AND SUBSTRING(Birthdate,6,5) <= '" + dateTo.ToString("MM-dd") + "' ";
orderByClause = "MONTH(Birthdate),DAY(Birthdate)";
}
else //The date range spans more than 1 calendar year
{
dateWhereClause = "(SUBSTRING(Birthdate,6,5) >= '" + dateFrom.ToString("MM-dd") + "' "
+ "OR SUBSTRING(Birthdate,6,5) <= '" + dateTo.ToString("MM-dd") + "') ";
orderByClause = "SUBSTRING(Birthdate,6,5) < '" + dateFrom.ToString("MM-dd") + "',MONTH(Birthdate),DAY(Birthdate)";
}
string command = "SELECT LName,FName,Preferred,Address,Address2,City,State,Zip,Birthdate "
+ "FROM patient "
+ "WHERE " + dateWhereClause + " "
+ "AND Birthdate > '1880-01-01' "
+ "AND PatStatus=0 "
+ "ORDER BY " + orderByClause;
DataTable table = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
table.Columns.Add("Age");
for (int i = 0; i < table.Rows.Count; i++)
{
table.Rows[i]["Age"] = Patients.DateToAge(PIn.Date(table.Rows[i]["Birthdate"].ToString()), dateTo).ToString();
}
return(table);
}
public static DataTable GetMissingXTransTable(DateTime dateStart, DateTime dateEnd)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
}
string command = "SELECT payment.PatNum,LName,FName,payment.DateEntry,payment.PayDate,payment.PayNote,payment.PayAmt "
+ "FROM patient "
+ "INNER JOIN payment ON payment.PatNum=patient.PatNum "
//only payments with the same PaymentType as the X-Charge PaymentType for the clinic
+ "INNER JOIN ("
+ "SELECT ClinicNum,PropertyValue AS PaymentType FROM programproperty "
+ "WHERE ProgramNum=" + POut.Long(Programs.GetProgramNum(ProgramName.Xcharge)) + " AND PropertyDesc='PaymentType'"
+ ") paytypes ON paytypes.ClinicNum=payment.ClinicNum AND paytypes.PaymentType=payment.PayType "
+ "LEFT JOIN xchargetransaction ON xchargetransaction.PatNum=payment.PatNum "
+ "AND " + DbHelper.DtimeToDate("TransactionDateTime") + "=payment.DateEntry "
+ "AND (CASE WHEN xchargetransaction.ResultCode=5 THEN 0 ELSE xchargetransaction.Amount END)=payment.PayAmt "
+ "AND xchargetransaction.ResultCode IN(0,5,10) "
+ "WHERE payment.DateEntry BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " "
+ "AND TransactionDateTime IS NULL "
+ "ORDER BY payment.PayDate ASC,LName,FName";
return(Db.GetTable(command));
}
public static DataTable GetData(long feeSchedNum, long clinicNum, long provNum, bool isCategories, bool includeBlanks)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), feeSchedNum, clinicNum, provNum, isCategories, includeBlanks));
}
DataTable data = GetDataSet(feeSchedNum, clinicNum, provNum);
DataTable retVal = new DataTable("ProcCodes");
if (isCategories)
{
retVal.Columns.Add(new DataColumn("Category"));
}
retVal.Columns.Add(new DataColumn("Code"));
retVal.Columns.Add(new DataColumn("Desc"));
retVal.Columns.Add(new DataColumn("Abbr"));
retVal.Columns.Add(new DataColumn("Fee"));
List <ProcedureCode> listProcCodes = new List <ProcedureCode>();
if (isCategories)
{
Def[][] arrayDefs = ReportsComplex.RunFuncOnReportServer(() => Defs.GetArrayShortNoCache());
listProcCodes = ReportsComplex.RunFuncOnReportServer(() => ProcedureCodes.GetProcList(arrayDefs))
.OrderBy(x => x.ProcCat).ThenBy(x => x.ProcCode).ToList(); //Ordered by category
}
else
{
listProcCodes = ReportsComplex.RunFuncOnReportServer(() => ProcedureCodes.GetAllCodes()); //Ordered by ProcCode, used for the non-category version of the report if they want blanks.
}
bool isFound;
List <Def> listDefs = Defs.GetDefsNoCache(DefCat.ProcCodeCats);
for (int i = 0; i < listProcCodes.Count; i++)
{
isFound = false;
DataRow row = retVal.NewRow();
if (isCategories)
{
//reports should no longer use the cache.
Def def = listDefs.FirstOrDefault(x => x.DefNum == listProcCodes[i].ProcCat);
row[0] = def == null ? "" : def.ItemName;
row[1] = listProcCodes[i].ProcCode;
row[2] = listProcCodes[i].Descript;
row[3] = listProcCodes[i].AbbrDesc;
}
else
{
row[0] = listProcCodes[i].ProcCode;
row[1] = listProcCodes[i].Descript;
row[2] = listProcCodes[i].AbbrDesc;
}
for (int j = 0; j < data.Rows.Count; j++)
{
if (data.Rows[j]["ProcCode"].ToString() == listProcCodes[i].ProcCode)
{
isFound = true;
double amt = PIn.Double(data.Rows[j]["Amount"].ToString());
if (isCategories)
{
if (amt == -1)
{
row[4] = "";
isFound = false;
}
else
{
row[4] = amt.ToString("n");
}
}
else
{
if (amt == -1)
{
row[3] = "";
isFound = false;
}
else
{
row[3] = amt.ToString("n");
}
}
break;
}
}
if (includeBlanks && !isFound)
{
retVal.Rows.Add(row); //Including a row that has a blank fee.
}
else if (isFound)
{
retVal.Rows.Add(row);
}
//All other rows (empty rows where we don't want blanks) are not added to the dataset.
}
return(retVal);
}
///<summary>If not using clinics then supply an empty list of clinicNums. listClinicNums must have at least one item if using clinics.</summary>
public static DataTable GetInsTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums,
List <long> listInsuranceTypes, List <long> listClaimPayGroups, bool hasAllProvs, bool hasAllClinics, bool hasInsuranceTypes, bool isGroupedByPatient,
bool hasAllClaimPayGroups, bool doShowProvSeparate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listInsuranceTypes, listClaimPayGroups,
hasAllProvs, hasAllClinics, hasInsuranceTypes, isGroupedByPatient, hasAllClaimPayGroups, doShowProvSeparate));
}
string whereProv = "";
if (!hasAllProvs)
{
whereProv += " AND claimproc.ProvNum IN(";
for (int i = 0; i < listProvNums.Count; i++)
{
if (i > 0)
{
whereProv += ",";
}
whereProv += POut.Long(listProvNums[i]);
}
whereProv += ") ";
}
string whereClin = "";
//reports should no longer use the cache
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
if (hasClinicsEnabled)
{
whereClin += " AND claimproc.ClinicNum IN(";
for (int i = 0; i < listClinicNums.Count; i++)
{
if (i > 0)
{
whereClin += ",";
}
whereClin += POut.Long(listClinicNums[i]);
}
whereClin += ") ";
}
string whereClaimPayGroup = "";
if (!hasAllClaimPayGroups)
{
whereClaimPayGroup = " AND PayGroup IN (" + String.Join(",", listClaimPayGroups) + ") ";
}
string queryIns =
@"SELECT claimproc.DateCP,carrier.CarrierName,MAX("
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + @") lfname,GROUP_CONCAT(DISTINCT provider.Abbr) Provider, ";
if (hasClinicsEnabled)
{
queryIns += "clinic.Abbr Clinic, ";
}
queryIns += @"claimpayment.CheckNum,SUM(claimproc.InsPayAmt) amt,claimproc.ClaimNum,claimpayment.PayType,COUNT(DISTINCT claimproc.PatNum) countPats
FROM claimproc
LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum
LEFT JOIN patient ON claimproc.PatNum = patient.PatNum
LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
LEFT JOIN provider ON provider.ProvNum=claimproc.ProvNum
LEFT JOIN claimpayment ON claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum " ;
if (hasClinicsEnabled)
{
queryIns += "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum ";
}
queryIns += "WHERE (claimproc.Status=1 OR claimproc.Status=4) " //received or supplemental
+ whereProv
+ whereClin
+ whereClaimPayGroup
+ "AND claimpayment.CheckDate >= " + POut.Date(dateFrom) + " "
+ "AND claimpayment.CheckDate <= " + POut.Date(dateTo) + " ";
if (!hasInsuranceTypes && listInsuranceTypes.Count > 0)
{
queryIns += "AND claimpayment.PayType IN (";
for (int i = 0; i < listInsuranceTypes.Count; i++)
{
if (i > 0)
{
queryIns += ",";
}
queryIns += POut.Long(listInsuranceTypes[i]);
}
queryIns += ") ";
}
queryIns += @"GROUP BY claimproc.DateCP,claimproc.ClaimPaymentNum,";
if (doShowProvSeparate)
{
queryIns += @"provider.ProvNum,";
}
if (hasClinicsEnabled)
{
queryIns += "claimproc.ClinicNum,clinic.Abbr,";
}
queryIns += "carrier.CarrierName,claimpayment.CheckNum";
if (isGroupedByPatient)
{
queryIns += ",patient.PatNum";
}
queryIns += " ORDER BY claimpayment.PayType,claimproc.DateCP,lfname";
if (!hasInsuranceTypes && listInsuranceTypes.Count == 0)
{
queryIns = DbHelper.LimitOrderBy(queryIns, 0);
}
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryIns));
foreach (DataRow row in table.Rows)
{
//If there is more than one patient attached to a check, we will append an asterisk to the end.
int countPats = PIn.Int(row["countPats"].ToString());
if (countPats > 1)
{
row["lfname"] = row["lfname"].ToString().TrimEnd() + "*";
}
}
table.Columns.Remove("countPats"); //Remove this column because we don't want it to show on the report
return(table);
}
///<summary>If not using clinics, or for all clinics with clinics enabled, supply an empty list of clinicNums. If the user is restricted, for all
///clinics supply only those clinics for which the user has permission to access, otherwise it will be run for all clinics.</summary>
public static DataTable GetPatTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, List <long> listPatientTypes,
bool hasAllProvs, bool hasAllClinics, bool hasPatientTypes, bool isGroupedByPatient, bool isUnearnedIncluded, bool doShowProvSeparate,
bool doShowHiddenTPUnearned)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listPatientTypes, hasAllProvs, hasAllClinics,
hasPatientTypes, isGroupedByPatient, isUnearnedIncluded, doShowProvSeparate, doShowHiddenTPUnearned));
}
//reports should no longer use the cache
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
List <long> listHiddenUnearnedDefNums = new List <long>();
if (!doShowHiddenTPUnearned)
{
listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
}
//patient payments-----------------------------------------------------------------------------------------
//the selected columns have to remain in this order due to the way the report complex populates the returned sheet
string queryPat = "SELECT payment.PayDate DatePay,"
+ "MAX(" + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ") lfname,GROUP_CONCAT(DISTINCT provider.Abbr),";
if (hasClinicsEnabled)
{
queryPat += "clinic.Abbr clinicAbbr,";
}
queryPat += "payment.CheckNum,SUM(COALESCE(paysplit.SplitAmt,0)) amt,payment.PayNum,ItemName,payment.PayType "
+ "FROM payment "
+ "LEFT JOIN paysplit ON payment.PayNum=paysplit.PayNum "
+ "LEFT JOIN patient ON payment.PatNum=patient.PatNum "
+ "LEFT JOIN provider ON paysplit.ProvNum=provider.ProvNum "
+ "LEFT JOIN definition ON payment.PayType=definition.DefNum ";
if (hasClinicsEnabled)
{
queryPat += "LEFT JOIN clinic ON clinic.ClinicNum=paysplit.ClinicNum ";
}
queryPat += "WHERE payment.PayDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " ";
if (hasClinicsEnabled && listClinicNums.Count > 0)
{
queryPat += "AND paysplit.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
if (!hasAllProvs && listProvNums.Count > 0)
{
queryPat += "AND paysplit.ProvNum IN(" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
if (!hasPatientTypes && listPatientTypes.Count > 0)
{
queryPat += "AND payment.PayType IN (" + string.Join(",", listPatientTypes.Select(x => POut.Long(x))) + ") ";
}
if (!isUnearnedIncluded) //UnearnedType of 0 means the paysplit is NOT unearned
{
queryPat += "AND paysplit.UnearnedType=0 ";
}
else if (listHiddenUnearnedDefNums.Count > 0 && !doShowHiddenTPUnearned) //Include unearned but not of the TP type.
{
queryPat += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
queryPat += "GROUP BY payment.PayNum,payment.PayDate,payment.CheckNum,definition.ItemName,payment.PayType ";
if (doShowProvSeparate)
{
queryPat += ",provider.ProvNum ";
}
if (hasClinicsEnabled)
{
queryPat += ",clinic.Abbr ";
}
if (isGroupedByPatient)
{
queryPat += ",patient.PatNum ";
}
queryPat += "ORDER BY payment.PayType,payment.PayDate,lfname";
if (!hasPatientTypes && listPatientTypes.Count == 0)
{
queryPat = DbHelper.LimitOrderBy(queryPat, 0);
}
return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryPat)));
}
///<summary></summary>
public static DataTable GetWriteoffTable(DateTime dateStart, DateTime dateEnd, bool isIndividual, string carrierText, bool isWriteoffPay)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, isIndividual, carrierText, isWriteoffPay));
}
string queryText = "";
//individual
if (isIndividual)
{
queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd)
+ ", @CarrierName='%" + POut.String(carrierText) + "%';";
if (isWriteoffPay)
{
queryText += @"SELECT claimproc.DateCP,
CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),
carrier.CarrierName,
provider.Abbr,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,patient,carrier,provider
WHERE provider.ProvNum = claimproc.ProvNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.PatNum = patient.PatNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4) /*received or supplemental*/
AND claimproc.DateCP >= @DateFrom
AND claimproc.DateCP <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY claimproc.ClaimNum
ORDER BY claimproc.DateCP" ;
}
else //use procedure date
{
queryText += @"SELECT claimproc.ProcDate,
CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),
carrier.CarrierName,
provider.Abbr,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,patient,carrier,provider
WHERE provider.ProvNum = claimproc.ProvNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.PatNum = patient.PatNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) /*received or supplemental or notreceived*/
AND claimproc.ProcDate >= @DateFrom
AND claimproc.ProcDate <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY claimproc.ClaimNum
ORDER BY claimproc.ProcDate" ;
}
}
else
{
//group
if (isWriteoffPay)
{
queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd)
+ ", @CarrierName='%" + POut.String(carrierText) + "%';"
+ @"SELECT carrier.CarrierName,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,carrier
WHERE claimproc.PlanNum = insplan.PlanNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4) /*received or supplemental*/
AND claimproc.DateCP >= @DateFrom
AND claimproc.DateCP <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY carrier.CarrierNum
ORDER BY carrier.CarrierName" ;
}
else
{
queryText = "SET @DateFrom=" + POut.Date(dateStart) + ", @DateTo=" + POut.Date(dateEnd)
+ ", @CarrierName='%" + POut.String(carrierText) + "%';"
+ @"SELECT carrier.CarrierName,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,carrier
WHERE claimproc.PlanNum = insplan.PlanNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) /*received or supplemental or notreceived*/
AND claimproc.ProcDate >= @DateFrom
AND claimproc.ProcDate <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY carrier.CarrierNum
ORDER BY carrier.CarrierName" ;
}
}
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(queryText)));
}
///<Summary></Summary>
public static DataTable GetAllWithoutCharges()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod()));
}
DataTable table = new DataTable();
table.Columns.Add("dateStop");
table.Columns.Add("family");
table.Columns.Add("PatNum");
table.Columns.Add("RegKey");
string command = @"
DROP TABLE IF EXISTS tempRegKeys;
CREATE TABLE tempRegKeys(
tempRegKeyId int auto_increment NOT NULL,
PatNum bigint NOT NULL,
RegKey VARCHAR(255) NOT NULL,
IsMissing tinyint NOT NULL,
Date_ DATE NOT NULL DEFAULT '0001-01-01',
PRIMARY KEY(tempRegKeyId),
KEY(PatNum));
/*Fill table with patnums for all guarantors of regkeys that are still active.*/
INSERT INTO tempRegKeys (PatNum,RegKey,Date_)
SELECT patient.Guarantor,RegKey,'0001-01-01'
FROM registrationkey
LEFT JOIN patient ON registrationkey.PatNum=patient.PatNum
WHERE DateDisabled='0001-01-01'
AND DateEnded='0001-01-01'
AND IsFreeVersion=0
AND IsOnlyForTesting=0;
/*Set indicators on keys with missing repeatcharges*/
UPDATE tempRegKeys
SET IsMissing=1
WHERE NOT EXISTS(SELECT * FROM repeatcharge WHERE repeatcharge.PatNum=tempRegKeys.PatNum);
/*Now, look for expired repeating charges. This is done in two steps.*/
/*Step 1: Mark all keys that have expired repeating charges.*/
/*Step 2: Then, remove those markings for all keys that also have unexpired repeating charges.*/
UPDATE tempRegKeys
SET Date_=(
SELECT IFNULL(MAX(DateStop),'0001-01-01')
FROM repeatcharge
WHERE repeatcharge.PatNum=tempRegKeys.PatNum
AND DateStop < " + DbHelper.Now() + @" AND DateStop > '0001-01-01');
/*Step 2:*/
UPDATE tempRegKeys
SET Date_='0001-01-01'
WHERE EXISTS(
SELECT * FROM repeatcharge
WHERE repeatcharge.PatNum=tempRegKeys.PatNum
AND DateStop = '0001-01-01');
SELECT LName,FName,tempRegKeys.PatNum,tempRegKeys.RegKey,IsMissing,Date_
FROM tempRegKeys
LEFT JOIN patient ON patient.PatNum=tempRegKeys.PatNum
WHERE IsMissing=1
OR Date_ > '0001-01-01'
ORDER BY tempRegKeys.PatNum;
DROP TABLE IF EXISTS tempRegKeys;" ;
DataTable raw = Db.GetTable(command);
DataRow row;
DateTime dateRepeatStop;
for (int i = 0; i < raw.Rows.Count; i++)
{
row = table.NewRow();
if (raw.Rows[i]["IsMissing"].ToString() == "1")
{
row["dateStop"] = "Missing Repeat Charge";
}
else
{
row["dateStop"] = "";
}
dateRepeatStop = PIn.Date(raw.Rows[i]["Date_"].ToString());
if (dateRepeatStop.Year > 1880)
{
if (row["dateStop"].ToString() != "")
{
row["dateStop"] += "\r\n";
}
row["dateStop"] += "Expired Repeat Charge:" + dateRepeatStop.ToShortDateString();
}
row["family"] = raw.Rows[i]["LName"].ToString() + ", " + raw.Rows[i]["FName"].ToString();
row["PatNum"] = raw.Rows[i]["PatNum"].ToString();
row["RegKey"] = raw.Rows[i]["RegKey"].ToString();
table.Rows.Add(row);
}
return(table);
}
public static DataTable GetPatientFormsTable(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum));
}
//DataConnection dcon=new DataConnection();
DataTable table = new DataTable("");
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("date");
table.Columns.Add("dateOnly", typeof(DateTime)); //to help with sorting
table.Columns.Add("dateTime", typeof(DateTime));
table.Columns.Add("description");
table.Columns.Add("DocNum");
table.Columns.Add("imageCat");
table.Columns.Add("SheetNum");
table.Columns.Add("showInTerminal");
table.Columns.Add("time");
table.Columns.Add("timeOnly", typeof(TimeSpan)); //to help with sorting
//but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now.
List <DataRow> rows = new List <DataRow>();
//sheet---------------------------------------------------------------------------------------
string command = "SELECT DateTimeSheet,SheetNum,Description,ShowInTerminal "
+ "FROM sheet WHERE IsDeleted=0 "
+ "AND PatNum =" + POut.Long(patNum) + " "
+ "AND (SheetType=" + POut.Long((int)SheetTypeEnum.PatientForm) + " OR SheetType=" + POut.Long((int)SheetTypeEnum.MedicalHistory);
if (PrefC.GetBool(PrefName.PatientFormsShowConsent))
{
command += " OR SheetType=" + POut.Long((int)SheetTypeEnum.Consent); //Show consent forms if pref is true.
}
command += ")";
//+"ORDER BY ShowInTerminal";//DATE(DateTimeSheet),ShowInTerminal,TIME(DateTimeSheet)";
DataTable rawSheet = Db.GetTable(command);
DateTime dateT;
for (int i = 0; i < rawSheet.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.DateT(rawSheet.Rows[i]["DateTimeSheet"].ToString());
row["date"] = dateT.ToShortDateString();
row["dateOnly"] = dateT.Date;
row["dateTime"] = dateT;
row["description"] = rawSheet.Rows[i]["Description"].ToString();
row["DocNum"] = "0";
row["imageCat"] = "";
row["SheetNum"] = rawSheet.Rows[i]["SheetNum"].ToString();
if (rawSheet.Rows[i]["ShowInTerminal"].ToString() == "0")
{
row["showInTerminal"] = "";
}
else
{
row["showInTerminal"] = rawSheet.Rows[i]["ShowInTerminal"].ToString();
}
if (dateT.TimeOfDay != TimeSpan.Zero)
{
row["time"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower();
}
row["timeOnly"] = dateT.TimeOfDay;
rows.Add(row);
}
//document---------------------------------------------------------------------------------------
command = "SELECT DateCreated,DocCategory,DocNum,Description "
+ "FROM document,definition "
+ "WHERE document.DocCategory=definition.DefNum"
+ " AND PatNum =" + POut.Long(patNum)
+ " AND definition.ItemValue LIKE '%F%'";
//+" ORDER BY DateCreated";
DataTable rawDoc = Db.GetTable(command);
long docCat;
for (int i = 0; i < rawDoc.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.DateT(rawDoc.Rows[i]["DateCreated"].ToString());
row["date"] = dateT.ToShortDateString();
row["dateOnly"] = dateT.Date;
row["dateTime"] = dateT;
row["description"] = rawDoc.Rows[i]["Description"].ToString();
row["DocNum"] = rawDoc.Rows[i]["DocNum"].ToString();
docCat = PIn.Long(rawDoc.Rows[i]["DocCategory"].ToString());
row["imageCat"] = Defs.GetName(DefCat.ImageCats, docCat);
row["SheetNum"] = "0";
row["showInTerminal"] = "";
if (dateT.TimeOfDay != TimeSpan.Zero)
{
row["time"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower();
}
row["timeOnly"] = dateT.TimeOfDay;
rows.Add(row);
}
//Sorting
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
DataView view = table.DefaultView;
view.Sort = "dateOnly,showInTerminal,timeOnly";
table = view.ToTable();
return(table);
}
///<summary>For orderBy, use 0 for BillingType and 1 for PatientName.</summary>
public static DataTable GetBilling(bool isSent, int orderBy, DateTime dateFrom, DateTime dateTo, List <long> clinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), isSent, orderBy, dateFrom, dateTo, clinicNums));
}
DataTable table = new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("amountDue");
table.Columns.Add("balTotal");
table.Columns.Add("billingType");
table.Columns.Add("insEst");
table.Columns.Add("IsSent");
table.Columns.Add("lastStatement");
table.Columns.Add("mode");
table.Columns.Add("name");
table.Columns.Add("PatNum");
table.Columns.Add("payPlanDue");
table.Columns.Add("StatementNum");
table.Columns.Add("SuperFamily");
table.Columns.Add("ClinicNum");
string command = "SELECT guar.BalTotal,patient.BillingType,patient.FName,guar.InsEst,statement.IsSent,"
+ "IFNULL(MAX(s2.DateSent)," + POut.Date(DateTime.MinValue) + ") LastStatement,"
+ "patient.LName,patient.MiddleI,statement.Mode_,guar.PayPlanDue,patient.Preferred,"
+ "statement.PatNum,statement.StatementNum,statement.SuperFamily,patient.ClinicNum "
+ "FROM statement "
+ "LEFT JOIN patient ON statement.PatNum=patient.PatNum "
+ "LEFT JOIN patient guar ON guar.PatNum=patient.Guarantor "
+ "LEFT JOIN statement s2 ON s2.PatNum=patient.PatNum "
+ "AND s2.IsSent=1 ";
if (PrefC.GetBool(PrefName.BillingIgnoreInPerson))
{
command += "AND s2.Mode_ !=1 ";
}
if (orderBy == 0) //BillingType
{
command += "LEFT JOIN definition ON patient.BillingType=definition.DefNum ";
}
command += "WHERE statement.IsSent=" + POut.Bool(isSent) + " ";
//if(dateFrom.Year>1800){
command += "AND statement.DateSent>=" + POut.Date(dateFrom) + " "; //greater than midnight this morning
//}
//if(dateFrom.Year>1800){
command += "AND statement.DateSent<" + POut.Date(dateTo.AddDays(1)) + " "; //less than midnight tonight
//}
if (clinicNums.Count > 0)
{
command += "AND patient.ClinicNum IN (" + string.Join(",", clinicNums) + ") ";
}
command += "GROUP BY guar.BalTotal,patient.BillingType,patient.FName,guar.InsEst,statement.IsSent,"
+ "patient.LName,patient.MiddleI,statement.Mode_,guar.PayPlanDue,patient.Preferred,"
+ "statement.PatNum,statement.StatementNum,statement.SuperFamily ";
if (orderBy == 0) //BillingType
{
command += "ORDER BY definition.ItemOrder,patient.LName,patient.FName,patient.MiddleI,guar.PayPlanDue";
}
else
{
command += "ORDER BY patient.LName,patient.FName";
}
DataTable rawTable = Db.GetTable(command);
double balTotal;
double insEst;
double payPlanDue;
DateTime lastStatement;
List <Patient> listFamilyGuarantors;
foreach (DataRow rawRow in rawTable.Rows)
{
row = table.NewRow();
if (rawRow["SuperFamily"].ToString() == "0") //not a super statement, just get bal info from guarantor
{
balTotal = PIn.Double(rawRow["BalTotal"].ToString());
insEst = PIn.Double(rawRow["InsEst"].ToString());
payPlanDue = PIn.Double(rawRow["PayPlanDue"].ToString());
}
else //super statement, add all guar positive balances to get bal total for super family
{
listFamilyGuarantors = Patients.GetSuperFamilyGuarantors(PIn.Long(rawRow["SuperFamily"].ToString())).FindAll(x => x.HasSuperBilling);
//exclude fams with neg balances in the total for super family stmts (per Nathan 5/25/2016)
if (PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
listFamilyGuarantors = listFamilyGuarantors.FindAll(x => x.BalTotal > 0);
insEst = 0;
}
else
{
listFamilyGuarantors = listFamilyGuarantors.FindAll(x => (x.BalTotal - x.InsEst) > 0);
insEst = listFamilyGuarantors.Sum(x => x.InsEst);
}
balTotal = listFamilyGuarantors.Sum(x => x.BalTotal);
payPlanDue = listFamilyGuarantors.Sum(x => x.PayPlanDue);
}
row["amountDue"] = (balTotal - insEst).ToString("F");
row["balTotal"] = balTotal.ToString("F");;
row["billingType"] = Defs.GetName(DefCat.BillingTypes, PIn.Long(rawRow["BillingType"].ToString()));
if (insEst == 0)
{
row["insEst"] = "";
}
else
{
row["insEst"] = insEst.ToString("F");
}
row["IsSent"] = rawRow["IsSent"].ToString();
lastStatement = PIn.Date(rawRow["LastStatement"].ToString());
if (lastStatement.Year < 1880)
{
row["lastStatement"] = "";
}
else
{
row["lastStatement"] = lastStatement.ToShortDateString();
}
row["mode"] = Lans.g("enumStatementMode", ((StatementMode)PIn.Int(rawRow["Mode_"].ToString())).ToString());
row["name"] = Patients.GetNameLF(rawRow["LName"].ToString(), rawRow["FName"].ToString(), rawRow["Preferred"].ToString(), rawRow["MiddleI"].ToString());
row["PatNum"] = rawRow["PatNum"].ToString();
if (payPlanDue == 0)
{
row["payPlanDue"] = "";
}
else
{
row["payPlanDue"] = payPlanDue.ToString("F");
}
row["StatementNum"] = rawRow["StatementNum"].ToString();
row["SuperFamily"] = rawRow["SuperFamily"].ToString();
row["ClinicNum"] = rawRow["ClinicNum"].ToString();
table.Rows.Add(row);
}
return(table);
}
///<summary>Used only from FormReferenceSelect to get the list of references.</summary>
public static DataTable GetReferenceTable(bool limit, long[] billingTypes, bool showBadRefs, bool showUsed, bool showGuarOnly, string city, string state, string zip,
string areaCode, string specialty, int superFam, string lname, string fname, string patnum, int age)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), limit, billingTypes, showBadRefs, showUsed, showGuarOnly, city, state, zip, areaCode, specialty, superFam, lname, fname, patnum, age));
}
string billingSnippet = "";
if (billingTypes.Length != 0)
{
for (int i = 0; i < billingTypes.Length; i++)
{
if (i == 0)
{
billingSnippet += "AND (";
}
else
{
billingSnippet += "OR ";
}
billingSnippet += "BillingType=" + POut.Long(billingTypes[i]) + " ";
if (i == billingTypes.Length - 1)
{
billingSnippet += ") ";
}
}
}
string phonedigits = "";
for (int i = 0; i < areaCode.Length; i++)
{
if (Regex.IsMatch(areaCode[i].ToString(), "[0-9]"))
{
phonedigits = phonedigits + areaCode[i];
}
}
string regexp = "";
for (int i = 0; i < phonedigits.Length; i++)
{
if (i < 1)
{
regexp = "^[^0-9]?"; //Allows phone to start with "("
}
regexp += phonedigits[i] + "[^0-9]*";
}
DataTable table = new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("CustReferenceNum");
table.Columns.Add("PatNum");
table.Columns.Add("FName");
table.Columns.Add("LName");
table.Columns.Add("HmPhone");
table.Columns.Add("State");
table.Columns.Add("City");
table.Columns.Add("Zip");
table.Columns.Add("Specialty");
table.Columns.Add("age");
table.Columns.Add("SuperFamily");
table.Columns.Add("DateMostRecent");
table.Columns.Add("TimesUsed");
table.Columns.Add("IsBadRef");
List <DataRow> rows = new List <DataRow>();
string command = @"SELECT * FROM (SELECT cr.*,p.LName,p.FName,p.HmPhone,p.State,p.City,p.Zip,p.Birthdate,pf.FieldValue,
(SELECT COUNT(*) FROM patient tempp WHERE tempp.SuperFamily=p.SuperFamily AND tempp.SuperFamily<>0) AS SuperFamily,
(SELECT COUNT(*) FROM custrefentry tempcre WHERE tempcre.PatNumRef=cr.PatNum) AS TimesUsed
FROM custreference cr
INNER JOIN patient p ON cr.PatNum=p.PatNum
LEFT JOIN patfield pf ON cr.PatNum=pf.PatNum AND pf.FieldName='Specialty'
WHERE cr.CustReferenceNum<>0 " ; //This just makes the following AND statements brainless.
command += "AND (p.PatStatus=" + POut.Int((int)PatientStatus.Patient) + " OR p.PatStatus=" + POut.Int((int)PatientStatus.NonPatient) + ") " //excludes deleted, etc.
+ billingSnippet;
if (age > 0)
{
command += "AND p.Birthdate <" + POut.Date(DateTime.Now.AddYears(-age)) + " ";
}
if (regexp != "")
{
command += "AND (p.HmPhone REGEXP '" + POut.String(regexp) + "' )";
}
command += (lname.Length > 0?"AND (p.LName LIKE '" + POut.String(lname) + "%' OR p.Preferred LIKE '" + POut.String(lname) + "%') ":"")
+ (fname.Length > 0?"AND (p.FName LIKE '" + POut.String(fname) + "%' OR p.Preferred LIKE '" + POut.String(fname) + "%') ":"")
+ (city.Length > 0?"AND p.City LIKE '" + POut.String(city) + "%' ":"")
+ (state.Length > 0?"AND p.State LIKE '" + POut.String(state) + "%' ":"")
+ (zip.Length > 0?"AND p.Zip LIKE '" + POut.String(zip) + "%' ":"")
+ (patnum.Length > 0?"AND p.PatNum LIKE '" + POut.String(patnum) + "%' ":"")
+ (specialty.Length > 0?"AND pf.FieldValue LIKE '" + POut.String(specialty) + "%' ":"")
+ (showBadRefs?"":"AND cr.IsBadRef=0 ")
+ (showGuarOnly?"AND p.Guarantor=p.PatNum":"");
if (limit)
{
command = DbHelper.LimitOrderBy(command, 40);
}
command += @") AS tempcustref WHERE PatNum<>0 "; //Once again just making AND statements brainless.
if (superFam > 0)
{
command += "AND SuperFamily>" + POut.Int(superFam) + " ";
}
if (showUsed)
{
command += "AND TimesUsed>0 ";
}
DataTable rawtable = Db.GetTable(command);
for (int i = 0; i < rawtable.Rows.Count; i++)
{
row = table.NewRow();
row["CustReferenceNum"] = rawtable.Rows[i]["CustReferenceNum"].ToString();
row["PatNum"] = rawtable.Rows[i]["PatNum"].ToString();
row["FName"] = rawtable.Rows[i]["FName"].ToString();
row["LName"] = rawtable.Rows[i]["LName"].ToString();
row["HmPhone"] = rawtable.Rows[i]["HmPhone"].ToString();
row["State"] = rawtable.Rows[i]["State"].ToString();
row["City"] = rawtable.Rows[i]["City"].ToString();
row["Zip"] = rawtable.Rows[i]["Zip"].ToString();
row["Specialty"] = rawtable.Rows[i]["FieldValue"].ToString();
row["age"] = Patients.DateToAge(PIn.Date(rawtable.Rows[i]["Birthdate"].ToString())).ToString();
row["SuperFamily"] = rawtable.Rows[i]["SuperFamily"].ToString();
DateTime recentDate = PIn.DateT(rawtable.Rows[i]["DateMostRecent"].ToString());
row["DateMostRecent"] = "";
if (recentDate.Year > 1880)
{
row["DateMostRecent"] = recentDate.ToShortDateString();
}
row["TimesUsed"] = rawtable.Rows[i]["TimesUsed"].ToString();
row["IsBadRef"] = rawtable.Rows[i]["IsBadRef"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
///<summary>Used to get a list of carriers to display in the FormCarriers window.</summary>
public static DataTable GetBigList(bool isCanadian, bool showHidden, string carrierName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), isCanadian, showHidden, carrierName));
}
DataTable tableRaw;
DataTable table;
string command;
//if(isCanadian){
command = "SELECT Address,Address2,canadiannetwork.Abbrev,carrier.CarrierNum,"
+ "CarrierName,CDAnetVersion,City,ElectID,"
+ "COUNT(insplan.PlanNum) insPlanCount,IsCDA,"
+ "carrier.IsHidden,Phone,State,Zip "
+ "FROM carrier "
+ "LEFT JOIN canadiannetwork ON canadiannetwork.CanadianNetworkNum=carrier.CanadianNetworkNum "
+ "LEFT JOIN insplan ON insplan.CarrierNum=carrier.CarrierNum "
+ "WHERE "
+ "CarrierName LIKE '%" + POut.String(carrierName) + "%' ";
if (isCanadian)
{
command += "AND IsCDA=1 ";
}
if (!showHidden)
{
command += "AND carrier.IsHidden=0 ";
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "GROUP BY carrier.CarrierNum ";
}
else //Oracle
{
command += "GROUP BY Address,Address2,canadiannetwork.Abbrev,carrier.CarrierNum,"
+ "CarrierName,CDAnetVersion,City,ElectID,IsCDA,"
+ "carrier.IsHidden,Phone,State,Zip ";
}
command += "ORDER BY CarrierName";
tableRaw = Db.GetTable(command);
table = new DataTable();
table.Columns.Add("Address");
table.Columns.Add("Address2");
table.Columns.Add("CarrierNum");
table.Columns.Add("CarrierName");
table.Columns.Add("City");
table.Columns.Add("ElectID");
table.Columns.Add("insPlanCount");
table.Columns.Add("isCDA");
table.Columns.Add("isHidden");
table.Columns.Add("Phone");
//table.Columns.Add("pMP");
//table.Columns.Add("network");
table.Columns.Add("State");
//table.Columns.Add("version");
table.Columns.Add("Zip");
DataRow row;
for (int i = 0; i < tableRaw.Rows.Count; i++)
{
row = table.NewRow();
row["Address"] = tableRaw.Rows[i]["Address"].ToString();
row["Address2"] = tableRaw.Rows[i]["Address2"].ToString();
row["CarrierNum"] = tableRaw.Rows[i]["CarrierNum"].ToString();
row["CarrierName"] = tableRaw.Rows[i]["CarrierName"].ToString();
row["City"] = tableRaw.Rows[i]["City"].ToString();
row["ElectID"] = tableRaw.Rows[i]["ElectID"].ToString();
if (PIn.Bool(tableRaw.Rows[i]["IsCDA"].ToString()))
{
row["isCDA"] = "X";
}
else
{
row["isCDA"] = "";
}
if (PIn.Bool(tableRaw.Rows[i]["IsHidden"].ToString()))
{
row["isHidden"] = "X";
}
else
{
row["isHidden"] = "";
}
row["insPlanCount"] = tableRaw.Rows[i]["insPlanCount"].ToString();
row["Phone"] = tableRaw.Rows[i]["Phone"].ToString();
//if(PIn.Bool(tableRaw.Rows[i]["IsPMP"].ToString())){
// row["pMP"]="X";
//}
//else{
// row["pMP"]="";
//}
//row["network"]=tableRaw.Rows[i]["Abbrev"].ToString();
row["State"] = tableRaw.Rows[i]["State"].ToString();
//row["version"]=tableRaw.Rows[i]["CDAnetVersion"].ToString();
row["Zip"] = tableRaw.Rows[i]["Zip"].ToString();
table.Rows.Add(row);
}
return(table);
}
public static DataTable GetData(List <long> listProvNums, List <long> listClinicNums, DateTime dateStart, DateTime dateEnd, bool includeNoNotes,
bool includeUnsignedNotes, ToothNumberingNomenclature toothNumberFormat, ProcNoteGroupBy groupBy)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listProvNums, listClinicNums, dateStart, dateEnd, includeNoNotes, includeUnsignedNotes,
toothNumberFormat, groupBy));
}
string whereNoNote = "";
string whereUnsignedNote = "";
string whereNotesClause = "";
if (includeNoNotes)
{
whereNoNote = @"
LEFT JOIN (
SELECT procedurelog.PatNum,procedurelog.ProcDate
FROM procedurelog
INNER JOIN procnote ON procnote.ProcNum=procedurelog.ProcNum
INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum
AND procedurecode.ProcCode NOT IN ('D9986','D9987')
WHERE procedurelog.ProcDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + @"
AND (procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " OR (procedurelog.ProcStatus=" + POut.Int((int)ProcStat.EC)
+ @" AND procedurecode.ProcCode='~GRP~'))"
+ @" GROUP BY procedurelog.PatNum,procedurelog.ProcDate
) hasNotes ON hasNotes.PatNum=procedurelog.PatNum AND hasNotes.ProcDate=procedurelog.ProcDate " ;
whereNotesClause = "AND (n1.ProcNum IS NOT NULL OR hasNotes.PatNum IS NULL) ";
}
if (includeUnsignedNotes)
{
if (includeNoNotes)
{
whereNotesClause = "AND (n1.ProcNum IS NOT NULL OR hasNotes.PatNum IS NULL OR unsignedNotes.ProcNum IS NOT NULL)";
}
else
{
whereNotesClause = "AND (n1.ProcNum IS NOT NULL OR unsignedNotes.ProcNum IS NOT NULL)";
}
whereUnsignedNote = @"
LEFT JOIN procnote unsignedNotes ON unsignedNotes.ProcNum=procedurelog.ProcNum
AND unsignedNotes.Signature=''
AND unsignedNotes.EntryDateTime= (SELECT MAX(n2.EntryDateTime)
FROM procnote n2
WHERE unsignedNotes.ProcNum = n2.ProcNum) " ;
}
string command = @"SELECT MAX(procedurelog.ProcDate) ProcDate,MAX(CONCAT(CONCAT(patient.LName, ', '),patient.FName)) PatName,procedurelog.PatNum,
(CASE WHEN COUNT(procedurelog.ProcNum)=1 THEN MAX(procedurecode.ProcCode) ELSE '' END) ProcCode,
(CASE WHEN COUNT(procedurelog.ProcNum)=1 THEN MAX(procedurecode.Descript) ELSE '" + Lans.g("FormRpProcNote", "Multiple procedures") + @"' END) Descript,
(CASE WHEN COUNT(procedurelog.ProcNum)=1 THEN MAX(procedurelog.ToothNum) ELSE '' END) ToothNum,
(CASE WHEN COUNT(procedurelog.ProcNum)=1 THEN MAX(procedurelog.Surf) ELSE '' END) Surf "
+ (includeNoNotes || includeUnsignedNotes?",(CASE WHEN MAX(n1.ProcNum) IS NOT NULL THEN 'X' ELSE '' END) AS Incomplete ":"")
+ (includeNoNotes?",(CASE WHEN MAX(hasNotes.PatNum) IS NULL THEN 'X' ELSE '' END) AS HasNoNote ":"")
+ (includeUnsignedNotes?",(CASE WHEN MAX(unsignedNotes.ProcNum) IS NOT NULL THEN 'X' ELSE '' END) AS HasUnsignedNote ":"") + @"
FROM procedurelog
INNER JOIN patient ON procedurelog.PatNum = patient.PatNum
INNER JOIN procedurecode ON procedurelog.CodeNum = procedurecode.CodeNum
AND procedurecode.ProcCode NOT IN ('D9986','D9987')
" + (includeNoNotes || includeUnsignedNotes?"LEFT":"INNER") + @" JOIN procnote n1 ON procedurelog.ProcNum = n1.ProcNum
AND (n1.Note LIKE '%""""%' OR n1.Note REGEXP '" + @"\[Prompt:""[a-zA-Z_0-9 ]+""\]') "//looks for either "" (pre 17.3) or [Prompt:"{word}"] (post 17.3)
+ @" AND n1.EntryDateTime= (SELECT MAX(n2.EntryDateTime)
FROM procnote n2
WHERE n1.ProcNum = n2.ProcNum) "
+ whereNoNote + " "
+ whereUnsignedNote + @"
WHERE procedurelog.ProcDate BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + @"
AND (procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C)
+ " OR (procedurelog.ProcStatus=" + POut.Int((int)ProcStat.EC) + " "
+ @" AND procedurecode.ProcCode='~GRP~')) "
+ whereNotesClause;
if (listProvNums.Count > 0)
{
command += @"AND procedurelog.ProvNum IN (" + String.Join(",", listProvNums) + ") ";
}
if (listClinicNums.Count > 0)
{
command += @"AND procedurelog.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
if (groupBy == ProcNoteGroupBy.Patient)
{
command += @"GROUP BY procedurelog.PatNum ";
}
else if (groupBy == ProcNoteGroupBy.DateAndPatient)
{
command += @"GROUP BY procedurelog.ProcDate,procedurelog.PatNum ";
}
else
{
command += "GROUP BY procedurelog.ProcNum ";
}
command += @"ORDER BY ProcDate, LName";
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
foreach (DataRow row in table.Rows)
{
row["ToothNum"] = Tooth.ToInternat(row["ToothNum"].ToString(), toothNumberFormat);
}
return(table);
}
public static DataTable GetProvList(DateTime dt)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dt));
}
#if DEBUG
_elapsedTimeProvList = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeProvList = "Elapsed time for GetProvList:\r\n";
stopWatch.Restart();
stopWatchTotal.Restart();
#endif
Random rnd = new Random();
string rndStr = rnd.Next(1000000).ToString();
string command;
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = @"CREATE TABLE tempdash" + rndStr + @" (
ProvNum bigint NOT NULL PRIMARY KEY,
production decimal NOT NULL,
income decimal NOT NULL
) DEFAULT CHARSET=utf8" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "CREATE TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//providers
command = @"INSERT INTO tempdash" + rndStr + @" (ProvNum)
SELECT ProvNum
FROM provider WHERE IsHidden=0
ORDER BY ItemOrder" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "providers: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//production--------------------------------------------------------------------
//procs
command = @"UPDATE tempdash" + rndStr + @"
SET production=(SELECT SUM(ProcFee*(UnitQty+BaseUnits)) FROM procedurelog
WHERE procedurelog.ProvNum=tempdash" + rndStr + @".ProvNum
AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + @"
AND ProcDate=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "production - procs: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//capcomplete writeoffs were skipped
//adjustments
command = @"UPDATE tempdash" + rndStr + @"
SET production=production+(SELECT IFNULL(SUM(AdjAmt),0) FROM adjustment
WHERE adjustment.ProvNum=tempdash" + rndStr + @".ProvNum
AND AdjDate=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "production - adjustments: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//insurance writeoffs
if (PrefC.GetBool(PrefName.ReportsPPOwriteoffDefaultToProcDate)) //use procdate
{
command = @"UPDATE tempdash" + rndStr + @"
SET production=production-(SELECT IFNULL(SUM(WriteOff),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash" + rndStr + @".ProvNum
AND ProcDate=" + POut.Date(dt) + @"
AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) )" ; //received or supplemental or notreceived
}
else
{
command = @"UPDATE tempdash" + rndStr + @"
SET production=production-(SELECT IFNULL(SUM(WriteOff),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash" + rndStr + @".ProvNum
AND DateCP=" + POut.Date(dt) + @"
AND (claimproc.Status=1 OR claimproc.Status=4) )" ;//received or supplemental
}
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "production - writeoffs: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//income------------------------------------------------------------------------
//patient income
command = @"UPDATE tempdash" + rndStr + @"
SET income=(SELECT SUM(SplitAmt) FROM paysplit
WHERE paysplit.ProvNum=tempdash" + rndStr + @".ProvNum
AND DatePay=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "income - patient: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//ins income
command = @"UPDATE tempdash" + rndStr + @"
SET income=income+(SELECT IFNULL(SUM(InsPayAmt),0) FROM claimproc
WHERE claimproc.ProvNum=tempdash" + rndStr + @".ProvNum
AND DateCP=" + POut.Date(dt) + ")";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "income - insurance: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//final queries
command = "SELECT * FROM tempdash" + rndStr + @"";
DataTable table = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProvList += "SELECT * : " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
stopWatchTotal.Stop();
_elapsedTimeProvList += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
_elapsedTimeProvList += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeProvList);
}
#endif
return(table);
}
///<summary>Used to get sheets filled via the web. Passing in a null or empty list of clinic nums will only return sheets that are not assigned to a clinic.</summary>
public static DataTable GetWebFormSheetsTable(DateTime dateFrom, DateTime dateTo, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listClinicNums));
}
if (listClinicNums == null || listClinicNums.Count == 0)
{
listClinicNums = new List <long>()
{
0
}; //To ensure we filter on at least one clinic (HQ).
}
DataTable table = new DataTable("");
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("date");
table.Columns.Add("dateOnly", typeof(DateTime)); //to help with sorting
table.Columns.Add("dateTime", typeof(DateTime));
table.Columns.Add("description");
table.Columns.Add("time");
table.Columns.Add("timeOnly", typeof(TimeSpan)); //to help with sorting
table.Columns.Add("PatNum");
table.Columns.Add("SheetNum");
table.Columns.Add("IsDeleted");
table.Columns.Add("ClinicNum");
List <DataRow> rows = new List <DataRow>();
string command = "SELECT DateTimeSheet,Description,PatNum,SheetNum,IsDeleted,ClinicNum "
+ "FROM sheet WHERE "
+ "DateTimeSheet >= " + POut.Date(dateFrom) + " AND DateTimeSheet <= " + POut.Date(dateTo.AddDays(1)) + " "
+ "AND IsWebForm = " + POut.Bool(true) + " "
+ "AND (SheetType=" + POut.Long((int)SheetTypeEnum.PatientForm) + " OR SheetType=" + POut.Long((int)SheetTypeEnum.MedicalHistory) + ") "
+ (PrefC.HasClinicsEnabled ? "AND ClinicNum IN (" + string.Join(",", listClinicNums) + ") " : "");
DataTable rawSheet = Db.GetTable(command);
DateTime dateT;
for (int i = 0; i < rawSheet.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.DateT(rawSheet.Rows[i]["DateTimeSheet"].ToString());
row["date"] = dateT.ToShortDateString();
row["dateOnly"] = dateT.Date;
row["dateTime"] = dateT;
row["description"] = rawSheet.Rows[i]["Description"].ToString();
row["PatNum"] = rawSheet.Rows[i]["PatNum"].ToString();
row["SheetNum"] = rawSheet.Rows[i]["SheetNum"].ToString();
if (dateT.TimeOfDay != TimeSpan.Zero)
{
row["time"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower();
}
row["timeOnly"] = dateT.TimeOfDay;
row["IsDeleted"] = rawSheet.Rows[i]["IsDeleted"].ToString();
row["ClinicNum"] = PIn.Long(rawSheet.Rows[i]["ClinicNum"].ToString());
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
DataView view = table.DefaultView;
view.Sort = "dateOnly,timeOnly";
table = view.ToTable();
return(table);
}
