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); }