///<summary>Validates and throws exceptions. Deletes automatic adjustments that fall within the pay period.</summary> public static List <TimeAdjust> GetListForTimeCardManage(long empNum, DateTime fromDate, DateTime toDate) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <TimeAdjust> >(MethodBase.GetCurrentMethod(), empNum, fromDate, toDate)); } List <TimeAdjust> retVal = new List <TimeAdjust>(); //List<TimeAdjust> listTimeAdjusts=new List<TimeAdjust>(); string command = "SELECT * FROM timeadjust WHERE " + "EmployeeNum = " + POut.Long(empNum) + " " + "AND " + DbHelper.DateColumn("TimeEntry") + " >= " + POut.Date(fromDate) + " " + "AND " + DbHelper.DateColumn("TimeEntry") + " <= " + POut.Date(toDate) + " " + "ORDER BY TimeEntry"; //listTimeAdjusts=Crud.TimeAdjustCrud.SelectMany(command); return(Crud.TimeAdjustCrud.SelectMany(command)); //Delete automatic adjustments.------------------------------------------------------------------------------------------ //for(int i=0;i<listTimeAdjusts.Count;i++) { // if(!listTimeAdjusts[i].IsAuto) {//skip and never delete manual adjustments // retVal.Add(listTimeAdjusts[i]); // continue; // } // TimeAdjusts.Delete(listTimeAdjusts[i]);//delete auto adjustments for current pay period //} //Validate--------------------------------------------------------------------------------------------------------------- //none necessary at this time. //return retVal; }
public static int GetRecallUndoCount(DateTime date) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetInt(MethodBase.GetCurrentMethod(), date)); } string command = "SELECT COUNT(*) FROM commlog " + "WHERE " + DbHelper.DateColumn("CommDateTime") + " = " + POut.Date(date) + " " + "AND (SELECT ItemValue FROM definition WHERE definition.DefNum=commlog.CommType) ='" + CommItemTypeAuto.RECALL.ToString() + "'"; return(PIn.Int(Db.GetScalar(command))); }
public static void RecallUndo(DateTime date) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { Meth.GetVoid(MethodBase.GetCurrentMethod(), date); return; } string command = "DELETE FROM commlog " + "WHERE " + DbHelper.DateColumn("CommDateTime") + " = " + POut.Date(date) + " " + "AND (SELECT ItemValue FROM definition WHERE definition.DefNum=commlog.CommType) ='" + CommItemTypeAuto.RECALL.ToString() + "'"; Db.NonQ(command); }
///<summary>For a given date, gets a list of dateTimes of missed calls. Gets directly from the Asterisk database, hard-coded.</summary> public static List <DateTime> GetMissedCalls(DateTime date) { DataConnection dcon = new DataConnection("192.168.0.197", "asteriskcdrdb", "opendental", "secret", DatabaseType.MySql); string command = "SELECT calldate FROM cdr WHERE " + DbHelper.DateColumn("calldate") + " = " + POut.Date(date) + " " + "AND (dcontext='ext-group' OR dcontext='ext-local') AND dst='vmu998'"; List <DateTime> retVal = new List <DateTime>(); DataTable table = dcon.GetTable(command); for (int i = 0; i < table.Rows.Count; i++) { retVal.Add(PIn.DateT(table.Rows[i][0].ToString())); } return(retVal); }
///<summary></summary> public static List <TimeAdjust> Refresh(long empNum, DateTime fromDate, DateTime toDate) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <TimeAdjust> >(MethodBase.GetCurrentMethod(), empNum, fromDate, toDate)); } string command = "SELECT * FROM timeadjust WHERE " + "EmployeeNum = " + POut.Long(empNum) + " " + "AND " + DbHelper.DateColumn("TimeEntry") + " >= " + POut.Date(fromDate) + " " + "AND " + DbHelper.DateColumn("TimeEntry") + " <= " + POut.Date(toDate) + " " + "ORDER BY TimeEntry"; return(Crud.TimeAdjustCrud.SelectMany(command)); }
///<summary>Get all sheets for a patient for today.</summary> public static List <Sheet> GetForPatientForToday(long patNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <Sheet> >(MethodBase.GetCurrentMethod(), patNum)); } string datesql = "CURDATE()"; if (DataConnection.DBtype == DatabaseType.Oracle) { datesql = "(SELECT CURRENT_DATE FROM dual)"; } string command = "SELECT * FROM sheet WHERE PatNum=" + POut.Long(patNum) + " AND " + DbHelper.DateColumn("DateTimeSheet") + " = " + datesql; return(Crud.SheetCrud.SelectMany(command)); }
///<summary>Returns all sheets for the given patient in the given date range which have a description matching the examDescript in a case insensitive manner. If examDescript is blank, then sheets with any description are returned.</summary> public static List <Sheet> GetExamSheetsTable(long patNum, DateTime startDate, DateTime endDate, string examDescript) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <Sheet> >(MethodBase.GetCurrentMethod(), patNum, startDate, endDate, examDescript)); } string command = "SELECT * " + "FROM sheet WHERE PatNum=" + POut.Long(patNum) + " " + "AND SheetType=" + POut.Int((int)SheetTypeEnum.ExamSheet) + " "; if (examDescript != "") { command += "AND Description LIKE '%" + POut.String(examDescript) + "%' "; //case insensitive text matches } command += "AND " + DbHelper.DateColumn("DateTimeSheet") + ">=" + POut.Date(startDate) + " AND " + DbHelper.DateColumn("DateTimeSheet") + "<=" + POut.Date(endDate) + " " + "ORDER BY DateTimeSheet"; return(Crud.SheetCrud.SelectMany(command)); }
///<summary>Returns all automatically generated timeAdjusts for a given employee between the date range (inclusive).</summary> internal static List <TimeAdjust> GetSimpleListAuto(long employeeNum, DateTime startDate, DateTime stopDate) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <TimeAdjust> >(MethodBase.GetCurrentMethod(), employeeNum, startDate, stopDate)); } List <TimeAdjust> retVal = new List <TimeAdjust>(); //List<TimeAdjust> listTimeAdjusts=new List<TimeAdjust>(); string command = "SELECT * FROM timeadjust WHERE " + "EmployeeNum = " + POut.Long(employeeNum) + " " + "AND " + DbHelper.DateColumn("TimeEntry") + " >= " + POut.Date(startDate) + " " + "AND " + DbHelper.DateColumn("TimeEntry") + " < " + POut.Date(stopDate.AddDays(1)) + " " //add one day to go the end of the specified date. + "AND IsAuto=1"; //listTimeAdjusts=Crud.TimeAdjustCrud.SelectMany(command); return(Crud.TimeAdjustCrud.SelectMany(command)); }
///<summary>Validates and throws exceptions. Gets all time adjusts between date range and time adjusts made during the current work week. </summary> public static List <TimeAdjust> GetValidList(long empNum, DateTime fromDate, DateTime toDate) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetObject <List <TimeAdjust> >(MethodBase.GetCurrentMethod(), empNum, fromDate, toDate)); } List <TimeAdjust> retVal = new List <TimeAdjust>(); string command = "SELECT * FROM timeadjust WHERE " + "EmployeeNum = " + POut.Long(empNum) + " " + "AND " + DbHelper.DateColumn("TimeEntry") + " >= " + POut.Date(fromDate) + " " + "AND " + DbHelper.DateColumn("TimeEntry") + " <= " + POut.Date(toDate) + " " + "ORDER BY TimeEntry"; retVal = Crud.TimeAdjustCrud.SelectMany(command); //Validate--------------------------------------------------------------------------------------------------------------- //none necessary at this time. return(retVal); }
public static DataTable GetLetterMergeInfo(Patient PatCur, LetterMerge letter) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), PatCur, letter)); } //jsparks- This is messy and prone to bugs. It needs to be reworked to work just like //in SheetFiller.FillFieldsInStaticText. Just grab a bunch of separate objects //instead of one result row. string command; //We need a very small table that tells us which tp is the most recent. //command="DROP TABLE IF EXISTS temptp;"; //Db.NonQ(command); //command=@"CREATE TABLE temptp( // DateTP date NOT NULL default '0001-01-01')"; //Db.NonQ(command); //command+=@"CREATE TABLE temptp // SELECT MAX(treatplan.DateTP) DateTP // FROM treatplan // WHERE PatNum="+POut.PInt(PatCur.PatNum)+";"; //Db.NonQ(command); command = "SET @maxTpDate=(SELECT MAX(treatplan.DateTP) FROM treatplan WHERE PatNum=" + POut.Long(PatCur.PatNum) + ");"; command += "SELECT "; for (int i = 0; i < letter.Fields.Count; i++) { if (i > 0) { command += ","; } if (letter.Fields[i] == "NextAptNum") { command += "MAX(plannedappt.AptNum) NextAptNum"; } //other: else if (letter.Fields[i] == "TPResponsPartyNameFL") { command += DbHelper.Concat("MAX(patResp.FName)", "' '", "MAX(patResp.LName)") + " TPResponsPartyNameFL"; } else if (letter.Fields[i] == "TPResponsPartyAddress") { command += "MAX(patResp.Address) TPResponsPartyAddress"; } else if (letter.Fields[i] == "TPResponsPartyCityStZip") { command += DbHelper.Concat("MAX(patResp.City)", "', '", "MAX(patResp.State)", "' '", "MAX(patResp.Zip)") + " TPResponsPartyCityStZip"; } else if (letter.Fields[i] == "SiteDescription") { command += "MAX(site.Description) SiteDescription"; } else if (letter.Fields[i] == "DateOfLastSavedTP") { command += DbHelper.DateColumn("MAX(treatplan.DateTP)") + " DateOfLastSavedTP"; } else if (letter.Fields[i] == "DateRecallDue") { command += "MAX(recall.DateDue) DateRecallDue"; } else if (letter.Fields[i] == "CarrierName") { command += "MAX(CarrierName) CarrierName"; } else if (letter.Fields[i] == "CarrierAddress") { command += "MAX(carrier.Address) CarrierAddress"; } else if (letter.Fields[i] == "CarrierCityStZip") { command += DbHelper.Concat("MAX(carrier.City)", "', '", "MAX(carrier.State)", "' '", "MAX(carrier.Zip)") + " CarrierCityStZip"; } else if (letter.Fields[i] == "SubscriberNameFL") { command += DbHelper.Concat("MAX(patSubsc.FName)", "' '", "MAX(patSubsc.LName)") + " SubscriberNameFL"; } else if (letter.Fields[i] == "SubscriberID") { command += "MAX(inssub.SubscriberID) SubscriberID"; } else if (letter.Fields[i] == "NextSchedAppt") { command += "MIN(appointment.AptDateTime) NextSchedAppt"; } else if (letter.Fields[i] == "Age") { command += "MAX(patient.Birthdate) BirthdateForAge"; } else if (letter.Fields[i] == "Guarantor") { command += DbHelper.Concat("MAX(patGuar.FName)", "' '", "MAX(patGuar.LName)") + " Guarantor"; } else if (letter.Fields[i] == "GradeSchool") { command += "MAX(site.Description) GradeSchool"; } else if (letter.Fields[i].StartsWith("referral.")) { command += "MAX(referral." + letter.Fields[i].Substring(9) + ") " + letter.Fields[i].Substring(9); } else { command += "MAX(patient." + letter.Fields[i] + ") " + letter.Fields[i]; } } command += " FROM patient " + "LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.IsFrom=1 " + "LEFT JOIN referral ON refattach.ReferralNum=referral.ReferralNum " + "LEFT JOIN plannedappt ON plannedappt.PatNum=patient.PatNum AND plannedappt.ItemOrder=1 " + "LEFT JOIN site ON patient.SiteNum=site.SiteNum " + "LEFT JOIN treatplan ON patient.PatNum=treatplan.PatNum AND DateTP=@maxTpDate " + "LEFT JOIN patient patResp ON treatplan.ResponsParty=patResp.PatNum " + "LEFT JOIN recall ON recall.PatNum=patient.PatNum " + "AND (recall.RecallTypeNum=" + POut.Long(PrefC.GetLong(PrefName.RecallTypeSpecialProphy)) + " OR recall.RecallTypeNum=" + POut.Long(PrefC.GetLong(PrefName.RecallTypeSpecialPerio)) + ") " + "LEFT JOIN patplan ON patplan.PatNum=patient.PatNum AND Ordinal=1 " + "LEFT JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum " + "LEFT JOIN insplan ON inssub.PlanNum=insplan.PlanNum " + "LEFT JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum " + "LEFT JOIN patient patSubsc ON patSubsc.PatNum=inssub.Subscriber " + "LEFT JOIN appointment ON appointment.PatNum=patient.PatNum " + "AND AptStatus=" + POut.Long((int)ApptStatus.Scheduled) + " " + "AND AptDateTime > " + DbHelper.Now() + " " + "LEFT JOIN patient patGuar ON patGuar.PatNum=patient.Guarantor " + "WHERE patient.PatNum=" + POut.Long(PatCur.PatNum) + " GROUP BY patient.PatNum " + "ORDER BY refattach.ItemOrder"; return(Db.GetTable(command)); }
///<summary>Gets data for the history grid in the SendClaims window.</summary> public static DataTable RefreshHistory(DateTime dateFrom, DateTime dateTo) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo)); } string command = "Select CONCAT(CONCAT(patient.LName,', '),patient.FName) AS PatName,carrier.CarrierName," + "clearinghouse.Description AS Clearinghouse,DateTimeTrans,etrans.OfficeSequenceNumber," + "etrans.CarrierTransCounter,Etype,etrans.ClaimNum,etrans.EtransNum,etrans.AckCode,etrans.Note " + "FROM etrans " + "LEFT JOIN carrier ON etrans.CarrierNum=carrier.CarrierNum " + "LEFT JOIN patient ON patient.PatNum=etrans.PatNum " + "LEFT JOIN clearinghouse ON clearinghouse.ClearinghouseNum=etrans.ClearinghouseNum WHERE " //if(DataConnection.DBtype==DatabaseType.Oracle){ // command+="TO_"; //} + DbHelper.DateColumn("DateTimeTrans") + " >= " + POut.Date(dateFrom) + " AND " //if(DataConnection.DBtype==DatabaseType.Oracle){ // command+="TO_"; //} + DbHelper.DateColumn("DateTimeTrans") + " <= " + POut.Date(dateTo) + " " + "AND Etype!=" + POut.Long((int)EtransType.Acknowledge_997) + " " + "AND Etype!=" + POut.Long((int)EtransType.Acknowledge_999) + " " + "AND Etype!=" + POut.Long((int)EtransType.BenefitInquiry270) + " " + "AND Etype!=" + POut.Long((int)EtransType.BenefitResponse271) + " " + "AND Etype!=" + POut.Long((int)EtransType.AckError) + " " //We exclude canadian transaction response types, since the responses can be accessed from the request transaction inside of the claim history portion of FormSendClaims. + "AND Etype!=" + POut.Long((int)EtransType.ClaimAck_CA) + " " //Could be attached to a claim, cob claim or ROT. + "AND Etype!=" + POut.Long((int)EtransType.ClaimEOB_CA) + " " //Could be attached to a claim, cob claim or ROT. + "AND Etype!=" + POut.Long((int)EtransType.EligResponse_CA) + " " //Will always be attached to an Eligibility request. + "AND Etype!=" + POut.Long((int)EtransType.EmailResponse_CA) + " " //Will always be attached to a Request for Outstanding Transactions (ROT). + "AND Etype!=" + POut.Long((int)EtransType.OutstandingAck_CA) + " " //Will always be attached to an ROT. + "AND Etype!=" + POut.Long((int)EtransType.PaymentResponse_CA) + " " //Will always be attached to a Request for Payment Reconciliation (RPR). + "AND Etype!=" + POut.Long((int)EtransType.PredetermAck_CA) + " " //Could be attached to a Predetermination request or an ROT. + "AND Etype!=" + POut.Long((int)EtransType.PredetermEOB_CA) + " " //Could be attached to a Predetermination request or an ROT. + "AND Etype!=" + POut.Long((int)EtransType.ReverseResponse_CA) + " " //Will always be attached to a Reversal request. + "AND Etype!=" + POut.Long((int)EtransType.SummaryResponse_CA) + " " //Will always be attached to a Request for Summary Reconciliation (RSR). //For Canada, when the undo button is used from Manage | Send Claims, the ClaimNum is set to 0 instead of deleting the etrans entry. //For transaction types related to claims where the claimnum=0, we do not want them to show in the history section of Manage | Send Claims because they have been undone. + "AND (ClaimNum<>0 OR Etype NOT IN (" + POut.Long((int)EtransType.Claim_CA) + "," + POut.Long((int)EtransType.ClaimCOB_CA) + "," + POut.Long((int)EtransType.Predeterm_CA) + "," + POut.Long((int)EtransType.ClaimReversal_CA) + ")) " + "ORDER BY DateTimeTrans"; DataTable table = Db.GetTable(command); DataTable tHist = new DataTable("Table"); tHist.Columns.Add("patName"); tHist.Columns.Add("CarrierName"); tHist.Columns.Add("Clearinghouse"); tHist.Columns.Add("dateTimeTrans"); tHist.Columns.Add("OfficeSequenceNumber"); tHist.Columns.Add("CarrierTransCounter"); tHist.Columns.Add("etype"); tHist.Columns.Add("Etype"); tHist.Columns.Add("ClaimNum"); tHist.Columns.Add("EtransNum"); tHist.Columns.Add("ack"); tHist.Columns.Add("Note"); DataRow row; string etype; for (int i = 0; i < table.Rows.Count; i++) { row = tHist.NewRow(); row["patName"] = table.Rows[i]["PatName"].ToString(); row["CarrierName"] = table.Rows[i]["CarrierName"].ToString(); row["Clearinghouse"] = table.Rows[i]["Clearinghouse"].ToString(); row["dateTimeTrans"] = PIn.DateT(table.Rows[i]["DateTimeTrans"].ToString()).ToShortDateString(); row["OfficeSequenceNumber"] = table.Rows[i]["OfficeSequenceNumber"].ToString(); row["CarrierTransCounter"] = table.Rows[i]["CarrierTransCounter"].ToString(); row["Etype"] = table.Rows[i]["Etype"].ToString(); etype = Lans.g("enumEtransType", ((EtransType)PIn.Long(table.Rows[i]["Etype"].ToString())).ToString()); if (etype.EndsWith("_CA")) { etype = etype.Substring(0, etype.Length - 3); } row["etype"] = etype; row["ClaimNum"] = table.Rows[i]["ClaimNum"].ToString(); row["EtransNum"] = table.Rows[i]["EtransNum"].ToString(); if (table.Rows[i]["AckCode"].ToString() == "A") { row["ack"] = Lans.g("Etrans", "Accepted"); } else if (table.Rows[i]["AckCode"].ToString() == "R") { row["ack"] = Lans.g("Etrans", "Rejected"); } row["Note"] = table.Rows[i]["Note"].ToString(); tHist.Rows.Add(row); } return(tHist); }
///<summary>Used when printing or emailing recall to make a commlog entry without any display.</summary> public static void InsertForRecall(long patNum, CommItemMode _mode, int numberOfReminders, long defNumNewStatus) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { Meth.GetVoid(MethodBase.GetCurrentMethod(), patNum, _mode, numberOfReminders, defNumNewStatus); return; } long recallType = Commlogs.GetTypeAuto(CommItemTypeAuto.RECALL); string command; string datesql = "CURDATE()"; if (DataConnection.DBtype == DatabaseType.Oracle) { datesql = "(SELECT CURRENT_DATE FROM dual)"; } if (recallType != 0) { command = "SELECT COUNT(*) FROM commlog WHERE "; command += DbHelper.DateColumn("CommDateTime") + " = " + datesql; command += " AND PatNum=" + POut.Long(patNum) + " AND CommType=" + POut.Long(recallType) + " AND Mode_=" + POut.Long((int)_mode) + " AND SentOrReceived=1"; if (Db.GetCount(command) != "0") { return; } } Commlog com = new Commlog(); com.PatNum = patNum; com.CommDateTime = DateTime.Now; com.CommType = recallType; com.Mode_ = _mode; com.SentOrReceived = CommSentOrReceived.Sent; com.Note = ""; if (numberOfReminders == 0) { com.Note = Lans.g("FormRecallList", "Recall reminder."); } else if (numberOfReminders == 1) { com.Note = Lans.g("FormRecallList", "Second recall reminder."); } else if (numberOfReminders == 2) { com.Note = Lans.g("FormRecallList", "Third recall reminder."); } else { com.Note = Lans.g("FormRecallList", "Recall reminder:") + " " + (numberOfReminders + 1).ToString(); } if (defNumNewStatus == 0) { com.Note += " " + Lans.g("Commlogs", "Status None"); } else { com.Note += " " + DefC.GetName(DefCat.RecallUnschedStatus, defNumNewStatus); } com.UserNum = Security.CurUser.UserNum; Insert(com); }
///<summary>Computes aging for the family specified. Specify guarantor=0 in order to calculate aging for all families. ///Gets all info from database. ///The aging calculation will use the following rules within each family: ///1) The aging "buckets" (0 to 30, 31 to 60, 61 to 90 and Over 90) ONLY include account activity on or ///before AsOfDate. ///2) BalTotal will always include all account activity, even future entries, except when in historical ///mode, where BalTotal will exclude account activity after AsOfDate. ///3) InsEst will always include all insurance estimates, even future estimates, except when in ///historical mode where InsEst excludes insurance estimates after AsOfDate. ///4) PayPlanDue will always include all payment plan charges minus credits, except when in ///historical mode where PayPlanDue excludes payment plan charges and payments after AsOfDate.</summary> public static void ComputeAging(long guarantor, DateTime AsOfDate, bool historic) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { Meth.GetVoid(MethodBase.GetCurrentMethod(), guarantor, AsOfDate, historic); return; } //Zero out either entire database or entire family. //Need to zero everything out first to catch former guarantors. string command = "UPDATE patient SET " + "Bal_0_30 = 0" + ",Bal_31_60 = 0" + ",Bal_61_90 = 0" + ",BalOver90 = 0" + ",InsEst = 0" + ",BalTotal = 0" + ",PayPlanDue= 0"; if (guarantor != 0) { command += " WHERE Guarantor=" + POut.Long(guarantor); } Db.NonQ(command); if (AsOfDate.Year < 1880) { AsOfDate = DateTime.Today; } string asOfDate = POut.Date(AsOfDate); string billInAdvanceDate = POut.Date(AsOfDate.AddDays(PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays))); if (historic) { billInAdvanceDate = POut.Date(DateTime.Today.AddDays(PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays))); } string thirtyDaysAgo = POut.Date(AsOfDate.AddDays(-30)); string sixtyDaysAgo = POut.Date(AsOfDate.AddDays(-60)); string ninetyDaysAgo = POut.Date(AsOfDate.AddDays(-90)); string familyPatNums = ""; Collection <string> familyPatNumList = new Collection <string> (); if (guarantor != 0) { familyPatNums = "("; command = "SELECT p.PatNum FROM patient p WHERE p.Guarantor=" + guarantor; DataTable tFamilyPatNums = Db.GetTable(command); for (int i = 0; i < tFamilyPatNums.Rows.Count; i++) { if (i > 0) { familyPatNums += ","; } string patNum = tFamilyPatNums.Rows[i][0].ToString(); familyPatNums += patNum; familyPatNumList.Add(patNum); } familyPatNums += ")"; } //We use temporary tables using the "CREATE TEMPORARY TABLE" syntax here so that any temporary //tables created are specific to the current database connection and no actual files are created //in the database. This will prevent rogue files from collecting in the live database, and will //prevent aging calculations on one computer from affecting the aging calculations on another computer. //Unfortunately, this has one side effect, which is that our connector reopens the //connection every time a command is run, so the temporary tables only last for a single //command. To get around this issue, we run the aging script as a single command/script. //Unfortunately, the "CREATE TEMPORARY TABLE" syntax gets replicated if MySQL replication is enabled, //which becomes a problem becauase the command is then no longer connection specific. Therefore, //to accomodate to the few offices using database replication with MySQL, when creating the temporary aging tables, //we append a random string to the temporary table names so the possibility to temporary table //name collision is practically zero. //Create a temporary table to calculate aging into temporarily, so that the patient table is //not being changed by multiple threads if more than one user is calculating aging. //Since a temporary table is dropped automatically only when the connection is closed, //and since we use connection pooling, drop them before using. string tempTableSuffix = CodeBase.MiscUtils.CreateRandomAlphaNumericString(14);//max size for a table name in oracle is 30 chars. string tempAgingTableName = "tempaging" + tempTableSuffix; string tempOdAgingTransTableName = "tempodagingtrans" + tempTableSuffix; if (DataConnection.DBtype == DatabaseType.Oracle) { try { //We would use DROP TEMPORARY TABLE IF EXISTS syntax here but no such syntax exists in Oracle. command = "DROP TEMPORARY TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } catch { //The tables do not exist. Nothing to do. } try { //We would use DROP TABLE IF EXISTS syntax here but no such syntax exists in Oracle. command = "DROP TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } catch { //The tables do not exist. Nothing to do. } } else { command = "DROP TEMPORARY TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); command = "DROP TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } if (DataConnection.DBtype == DatabaseType.Oracle) { command = "CREATE GLOBAL TEMPORARY TABLE " + tempAgingTableName + " (" + "PatNum NUMBER," + "Guarantor NUMBER," + "Charges_0_30 NUMBER(38,8) DEFAULT 0," + "Charges_31_60 NUMBER(38,8) DEFAULT 0," + "Charges_61_90 NUMBER(38,8) DEFAULT 0," + "ChargesOver90 NUMBER(38,8) DEFAULT 0," + "TotalCredits NUMBER(38,8) DEFAULT 0," + "InsEst NUMBER(38,8) DEFAULT 0," + "PayPlanDue NUMBER(38,8) DEFAULT 0," + "BalTotal NUMBER(38,8) DEFAULT 0" + ");"; } else { command = "CREATE TEMPORARY TABLE " + tempAgingTableName + " (" + "PatNum bigint," + "Guarantor bigint," + "Charges_0_30 DOUBLE DEFAULT 0," + "Charges_31_60 DOUBLE DEFAULT 0," + "Charges_61_90 DOUBLE DEFAULT 0," + "ChargesOver90 DOUBLE DEFAULT 0," + "TotalCredits DOUBLE DEFAULT 0," + "InsEst DOUBLE DEFAULT 0," + "PayPlanDue DOUBLE DEFAULT 0," + "BalTotal DOUBLE DEFAULT 0" + ");"; } if (guarantor == 0) { //We insert all of the patient numbers and guarantor numbers only when we are running aging for everyone, //since we do not want to examine every patient record when running aging for a single family. command += "INSERT INTO " + tempAgingTableName + " (PatNum,Guarantor) " + "SELECT p.PatNum,p.Guarantor " + "FROM patient p;"; //When there is only one patient that aging is being calculated for, then the indexes actually //slow the calculation down slightly, but they significantly improve the speed when aging is being //calculated for all familes. if (DataConnection.DBtype == DatabaseType.Oracle) { command += "CREATE INDEX " + tempAgingTableName.ToUpper() + "_PATNUM ON " + tempAgingTableName + " (PatNum);"; command += "CREATE INDEX " + tempAgingTableName.ToUpper() + "_GUAR ON " + tempAgingTableName + " (Guarantor);"; } else { command += "ALTER TABLE " + tempAgingTableName + " ADD INDEX IDX_" + tempAgingTableName.ToUpper() + "_PATNUM (PatNum);"; command += "ALTER TABLE " + tempAgingTableName + " ADD INDEX IDX_" + tempAgingTableName.ToUpper() + "_GUARANTOR (Guarantor);"; } } else { //Manually create insert statements to avoid having the database system visit every patient record again. //In my testing, this saves about 0.25 seconds on an individual family aging calculation on my machine in MySQL. command += "INSERT INTO " + tempAgingTableName + " (PatNum,Guarantor) VALUES "; for (int i = 0; i < familyPatNumList.Count; i++) { if (i > 0) { command += ","; } command += "(" + familyPatNumList[i] + "," + guarantor + ")"; } command += ";"; } //Create another temporary table which holds a very concise summary of the entire office transaction history, //so that all transactions can be treated as either a general credit or a general charge in the aging calculation. //Since we are recreating a temporary table with the same name as last time aging was run, //the old temporary table gets wiped out. if (DataConnection.DBtype == DatabaseType.Oracle) { command += "CREATE GLOBAL TEMPORARY TABLE " + tempOdAgingTransTableName + " (" + "PatNum NUMBER," + "TranDate DATE DEFAULT TO_DATE('0001-01-01', 'yyyy-mm-dd')," + "TranAmount NUMBER(38,8) DEFAULT 0" + ");"; } else { command += "CREATE TEMPORARY TABLE " + tempOdAgingTransTableName + " (" + "PatNum bigint," + "TranDate DATE DEFAULT '0001-01-01'," + "TranAmount DOUBLE DEFAULT 0" + ");"; } //Get the completed procedure dates and charges for the entire office history. command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " + "SELECT pl.PatNum PatNum," + "pl.ProcDate TranDate," + "pl.ProcFee*(pl.UnitQty+pl.BaseUnits) TranAmount " + "FROM procedurelog pl " + "WHERE pl.ProcStatus=2 " + (guarantor == 0?"":(" AND pl.PatNum IN " + familyPatNums)) + ";"; //Paysplits for the entire office history. command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " + "SELECT ps.PatNum PatNum," + "ps.ProcDate TranDate," + "-ps.SplitAmt TranAmount " + "FROM paysplit ps " + "WHERE ps.PayPlanNum=0 " + //Only splits not attached to payment plans. (guarantor == 0?"":(" AND ps.PatNum IN " + familyPatNums)) + ";"; //Get the adjustment dates and amounts for the entire office history. command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " + "SELECT a.PatNum PatNum," + "a.AdjDate TranDate," + "a.AdjAmt TranAmount " + "FROM adjustment a " + "WHERE a.AdjAmt<>0 " + (guarantor == 0?"":(" AND a.PatNum IN " + familyPatNums)) + ";"; //Claim payments and capitation writeoffs for the entire office history. command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " + "SELECT cp.PatNum PatNum," + "cp.DateCp TranDate," + //Always use DateCP rather than ProcDate to calculate the date of a claim payment. "-cp.InsPayAmt-cp.Writeoff TranAmount " + "FROM claimproc cp " + "WHERE cp.status IN (1,4,5,7) " + //received, supplemental, CapClaim or CapComplete. (guarantor == 0?"":(" AND cp.PatNum IN " + familyPatNums)) + ";"; //Payment plan principal for the entire office history. command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " + "SELECT pp.PatNum PatNum," + "pp.PayPlanDate TranDate," + "-pp.CompletedAmt TranAmount " + "FROM payplan pp " + "WHERE pp.CompletedAmt<>0 " + (guarantor == 0?"":(" AND pp.PatNum IN " + familyPatNums)) + ";"; if (DataConnection.DBtype == DatabaseType.Oracle) { //The aging calculation buckets, insurance estimates, and payment plan due amounts are //not yet calculated for Oracle as they have not been needed yet. Just calculates //account balance totals. string tempTotalsTableName = "temptotals" + tempTableSuffix; command += "CREATE GLOBAL TEMPORARY TABLE " + tempTotalsTableName + " (" + "PatNum NUMBER DEFAULT 0," + "BalTotal NUMBER(38,8) DEFAULT 0" + ");"; command += "CREATE INDEX " + tempTotalsTableName.ToUpper() + "_PATNU ON " + tempTotalsTableName + " (PatNum);"; command += "INSERT INTO " + tempTotalsTableName + " " + "SELECT PatNum,ROUND(SUM(TranAmount),2) FROM " + tempOdAgingTransTableName + "GROUP BY PatNum;"; command += "UPDATE patient p " + "SET p.BalTotal=(SELECT t.BalTotal FROM " + tempTotalsTableName + " t WHERE t.PatNum=p.PatNum " + DbHelper.LimitAnd(1) + ");"; Db.NonQ(command); } else { //Now that we have all of the pertinent transaction history, we will calculate all of the charges for //the associated patients. //Calculate over 90 day charges for all specified families. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total charges for each patient during this time period and //place the results into memory table 'chargesOver90'. "(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(ninetyDaysAgo) + " GROUP BY t.PatNum) chargesOver90 " + //Update the tempaging table with the caculated charges for the time period. "SET a.ChargesOver90=chargesOver90.TotalCharges " + "WHERE a.PatNum=chargesOver90.PatNum;"; //Calculate 61 to 90 day charges for all specified families. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total charges for each patient during this time period and //place the results into memory table 'charges_61_90'. "(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(sixtyDaysAgo) + " AND " + "t.TranDate>=" + DbHelper.DateColumn(ninetyDaysAgo) + " GROUP BY t.PatNum) charges_61_90 " + //Update the tempaging table with the caculated charges for the time period. "SET a.Charges_61_90=charges_61_90.TotalCharges " + "WHERE a.PatNum=charges_61_90.PatNum;"; //Calculate 31 to 60 day charges for all specified families. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total charges for each patient during this time period and //place the results into memory table 'charges_31_60'. "(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(thirtyDaysAgo) + " AND " + "t.TranDate>=" + DbHelper.DateColumn(sixtyDaysAgo) + " GROUP BY t.PatNum) charges_31_60 " + //Update the tempaging table with the caculated charges for the time period. "SET a.Charges_31_60=charges_31_60.TotalCharges " + "WHERE a.PatNum=charges_31_60.PatNum;"; //Calculate 0 to 30 day charges for all specified families. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total charges for each patient during this time period and //place the results into memory table 'charges_0_30'. "(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount>0 AND t.TranDate<=" + DbHelper.DateColumn(asOfDate) + " AND " + "t.TranDate>=" + DbHelper.DateColumn(thirtyDaysAgo) + " GROUP BY t.PatNum) charges_0_30 " + //Update the tempaging table with the caculated charges for the time period. "SET a.Charges_0_30=charges_0_30.TotalCharges " + "WHERE a.PatNum=charges_0_30.PatNum;"; //Calculate the total credits each patient has ever received so we can apply the credits to the aged charges below. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total credits for each patient and store the results in memory table 'credits'. "(SELECT t.PatNum,-SUM(t.TranAmount) TotalCredits FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount<0 AND t.TranDate<=" + DbHelper.DateColumn(asOfDate) + " GROUP BY t.PatNum) credits " + //Update the total credit for each patient into the tempaging table. "SET a.TotalCredits=credits.TotalCredits " + "WHERE a.PatNum=credits.PatNum;"; //Calculate claim estimates for each patient individually on or before the specified date. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the insurance estimates for each patient and store the results into //memory table 't'. "(SELECT cp.PatNum,SUM(cp.InsPayEst+cp.Writeoff) InsEst " + "FROM claimproc cp " + "WHERE cp.PatNum<>0 " + (historic?(" AND ((cp.Status=0 AND cp.ProcDate<=" + DbHelper.DateColumn(asOfDate) + ") OR " + "(cp.Status=1 AND cp.DateCP>" + DbHelper.DateColumn(asOfDate) + ")) AND cp.ProcDate<=" + DbHelper.DateColumn(asOfDate) + " "):" AND cp.Status=0 ") + (guarantor == 0?"":(" AND cp.PatNum IN " + familyPatNums + " ")) + "GROUP BY cp.PatNum) t " + //not received claims. //Update the tempaging table with the insurance estimates for each patient. "SET a.InsEst=t.InsEst " + "WHERE a.PatNum=t.PatNum;"; //Calculate the payment plan charges for each payment plan guarantor //on or before the specified date (also considering the PayPlansBillInAdvanceDays setting). //We cannot exclude payments made outside the specified family, since payment plan //guarantors can be in another family. command += "UPDATE " + tempAgingTableName + " a," + "(SELECT ppc.Guarantor,IFNULL(SUM(ppc.Principal+ppc.Interest),0) PayPlanCharges " + "FROM payplancharge ppc " + "WHERE ppc.ChargeDate<=" + DbHelper.DateColumn(billInAdvanceDate) + " " + //bill in adv. date accounts for historic vs current because of how it is set above. "GROUP BY ppc.Guarantor) c " + "SET a.PayPlanDue=c.PayPlanCharges " + "WHERE c.Guarantor=a.PatNum;"; //Calculate the total payments made to each payment plan //on or before the specified date and store the results in memory table 'p'. //We cannot exclude payments made outside the specified family, since payment plan //guarantors can be in another family. command += "UPDATE " + tempAgingTableName + " a," + "(SELECT ps.PatNum,SUM(ps.SplitAmt) PayPlanPayments " + "FROM paysplit ps " + "WHERE ps.PayPlanNum<>0 " + //only payments attached to payment plans. (historic?(" AND ps.ProcDate<=" + DbHelper.DateColumn(asOfDate) + " "):"") + "GROUP BY ps.PatNum) p " + "SET a.PayPlanDue=a.PayPlanDue-p.PayPlanPayments " + "WHERE p.PatNum=a.PatNum;"; //Calculate the total balance for each patient. //In historical mode, only transactions on or before AsOfDate will be included. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total balance for each patient and //place the results into memory table 'totals'. "(SELECT t.PatNum,SUM(t.TranAmount) BalTotal FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount<>0 " + (historic?(" AND t.TranDate<=" + DbHelper.DateColumn(asOfDate)):"") + " GROUP BY t.PatNum) totals " + //Update the tempaging table with the caculated charges for the time period. "SET a.BalTotal=totals.BalTotal " + "WHERE a.PatNum=totals.PatNum;"; //Update the family aged balances onto the guarantor rows of the patient table //by placing credits on oldest charges first, then on younger charges. command += "UPDATE patient p," + //Sum each colum within each family group inside of the tempaging table so that we are now //using family amounts instead of individual patient amounts, and store the result into //memory table 'f'. "(SELECT a.Guarantor,SUM(a.Charges_0_30) Charges_0_30,SUM(a.Charges_31_60) Charges_31_60," + "SUM(a.Charges_61_90) Charges_61_90,SUM(a.ChargesOver90) ChargesOver90," + "SUM(TotalCredits) TotalCredits,SUM(InsEst) InsEst,SUM(PayPlanDue) PayPlanDue," + "SUM(BalTotal) BalTotal " + "FROM " + tempAgingTableName + " a " + "GROUP BY a.Guarantor) f " + //Perform the update of the patient table based on the family amounts summed into table 'f', and //distribute the payments into the oldest balances first. "SET " + "p.BalOver90=ROUND((CASE " + //over 90 balance paid in full. "WHEN f.TotalCredits>=f.ChargesOver90 THEN 0 " + //over 90 balance partially paid or unpaid. "ELSE f.ChargesOver90-f.TotalCredits END),2)," + "p.Bal_61_90=ROUND((CASE " + //61 to 90 day balance unpaid. "WHEN f.TotalCredits<=f.ChargesOver90 THEN f.Charges_61_90 " + //61 to 90 day balance paid in full. "WHEN f.ChargesOver90+f.Charges_61_90<=f.TotalCredits THEN 0 " + //61 to 90 day balance partially paid. "ELSE f.ChargesOver90+f.Charges_61_90-f.TotalCredits END),2)," + "p.Bal_31_60=ROUND((CASE " + //31 to 60 day balance unpaid. "WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90 THEN f.Charges_31_60 " + //31 to 60 day balance paid in full. "WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60<=f.TotalCredits THEN 0 " + //31 to 60 day balance partially paid. "ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60-f.TotalCredits END),2)," + "p.Bal_0_30=ROUND((CASE " + //0 to 30 day balance unpaid. "WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90+f.Charges_31_60 THEN f.Charges_0_30 " + //0 to 30 day balance paid in full. "WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60+f.Charges_0_30<=f.TotalCredits THEN 0 " + //0 to 30 day balance partially paid. "ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60+f.Charges_0_30-f.TotalCredits END),2)," + "p.BalTotal=ROUND(f.BalTotal,2)," + "p.InsEst=ROUND(f.InsEst,2)," + "p.PayPlanDue=ROUND(f.PayPlanDue,2) " + "WHERE p.PatNum=f.Guarantor;"; //Aging calculations only apply to guarantors. Db.NonQ(command); } if (DataConnection.DBtype == DatabaseType.Oracle) { try { //We would use DROP TEMPORARY TABLE IF EXISTS syntax here but no such syntax exists in Oracle. command = "DROP TEMPORARY TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } catch { //The tables do not exist. Nothing to do. } try { //We would use DROP TABLE IF EXISTS syntax here but no such syntax exists in Oracle. command = "DROP TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } catch { //The tables do not exist. Nothing to do. } } else { command = "DROP TEMPORARY TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); command = "DROP TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } }
/// <param name="isPrintReport">Only applicable to ODHQ. If true, will add ADP pay numer and note. The query takes about 9 seconds if this is set top true vs. about 2 seconds if set to false.</param> public static string GetTimeCardManageCommand(DateTime startDate, DateTime stopDate, bool isPrintReport) { string command = @"SELECT clockevent.EmployeeNum,"; if (PrefC.GetBool(PrefName.DistributorKey) && isPrintReport) //OD HQ { command += "COALESCE(wikilist_employees.ADPNum,'NotInList') AS ADPNum,"; } command += @"employee.FName,employee.LName, SEC_TO_TIME((((TIME_TO_SEC(tempclockevent.TotalTime)-TIME_TO_SEC(tempclockevent.OverTime)) +TIME_TO_SEC(tempclockevent.AdjEvent))+TIME_TO_SEC(IFNULL(temptimeadjust.AdjReg,0))) +(TIME_TO_SEC(tempclockevent.OverTime)+TIME_TO_SEC(IFNULL(temptimeadjust.AdjOTime,0)))) AS tempTotalTime, SEC_TO_TIME((TIME_TO_SEC(tempclockevent.TotalTime)-TIME_TO_SEC(tempclockevent.OverTime)) +TIME_TO_SEC(tempclockevent.AdjEvent)+TIME_TO_SEC(IFNULL(temptimeadjust.AdjReg,0))) AS tempRegHrs, SEC_TO_TIME(TIME_TO_SEC(tempclockevent.OverTime)+TIME_TO_SEC(IFNULL(temptimeadjust.AdjOTime,0))) AS tempOverTime, tempclockevent.AdjEvent, temptimeadjust.AdjReg, temptimeadjust.AdjOTime, IFNULL(tempclockevent.Rate2Hours,'00:00:00') AS differential, SEC_TO_TIME(TIME_TO_SEC(tempbreak.BreakTime)+TIME_TO_SEC(AdjEvent)) AS BreakTime " ; if (isPrintReport) { command += ",tempclockevent.Note "; } command += @"FROM clockevent LEFT JOIN (SELECT ce.EmployeeNum,SEC_TO_TIME(IFNULL(SUM(UNIX_TIMESTAMP(ce.TimeDisplayed2)),0)-IFNULL(SUM(UNIX_TIMESTAMP(ce.TimeDisplayed1)),0)) AS TotalTime, SEC_TO_TIME(IFNULL(SUM(TIME_TO_SEC(CASE WHEN ce.OTimeHours='-01:00:00' THEN ce.OTimeAuto ELSE ce.OTimeHours END)),0)) AS OverTime, SEC_TO_TIME(IFNULL(SUM(TIME_TO_SEC(CASE WHEN ce.AdjustIsOverridden='1' THEN ce.Adjust ELSE ce.AdjustAuto END)),0)) AS AdjEvent, SEC_TO_TIME(SUM(UNIX_TIMESTAMP(CASE WHEN ce.Rate2Hours='-01:00:00' THEN ce.Rate2Auto ELSE ce.Rate2Hours END))) AS Rate2Hours" ; if (isPrintReport) { command += @", (SELECT CASE WHEN cev.Note !="""" THEN cev.Note ELSE """" END FROM clockevent cev WHERE cev.TimeDisplayed1 >= " + POut.Date(startDate) + @" AND cev.TimeDisplayed1 <= " + POut.Date(stopDate.AddDays(1)) + @" AND cev.TimeDisplayed2 > " + POut.Date(new DateTime(0001, 1, 1)) + @" AND (cev.ClockStatus = '0' OR cev.ClockStatus = '1') AND cev.EmployeeNum=ce.EmployeeNum ORDER BY cev.TimeDisplayed2 LIMIT 1) AS Note" ; } command += @" FROM clockevent ce WHERE ce.TimeDisplayed1 >= " + POut.Date(startDate) + @" AND ce.TimeDisplayed1 <= " + POut.Date(stopDate.AddDays(1)) + @" AND ce.TimeDisplayed2 > " + POut.Date(new DateTime(0001, 1, 1)) + @" AND (ce.ClockStatus = '0' OR ce.ClockStatus = '1') GROUP BY ce.EmployeeNum) tempclockevent ON clockevent.EmployeeNum=tempclockevent.EmployeeNum LEFT JOIN (SELECT timeadjust.EmployeeNum,SEC_TO_TIME(SUM(TIME_TO_SEC(timeadjust.RegHours))) AS AdjReg, SEC_TO_TIME(SUM(TIME_TO_SEC(timeadjust.OTimeHours))) AdjOTime FROM timeadjust WHERE " + DbHelper.DateColumn("TimeEntry") + " >= " + POut.Date(startDate) + @" AND " + DbHelper.DateColumn("TimeEntry") + " <= " + POut.Date(stopDate) + @" GROUP BY timeadjust.EmployeeNum) temptimeadjust ON clockevent.EmployeeNum=temptimeadjust.EmployeeNum LEFT JOIN (SELECT ceb.EmployeeNum,SEC_TO_TIME(IFNULL(SUM(UNIX_TIMESTAMP(ceb.TimeDisplayed2)),0)-IFNULL(SUM(UNIX_TIMESTAMP(ceb.TimeDisplayed1)),0)) AS BreakTime FROM clockevent ceb WHERE ceb.TimeDisplayed1 >= " + POut.Date(startDate) + @" AND ceb.TimeDisplayed1 <= " + POut.Date(stopDate.AddDays(1)) + @" AND ceb.TimeDisplayed2 > " + POut.Date(new DateTime(0001, 1, 1)) + @" AND ceb.ClockStatus = '2' GROUP BY ceb.EmployeeNum) tempbreak ON clockevent.EmployeeNum=tempbreak.EmployeeNum INNER JOIN employee ON clockevent.EmployeeNum=employee.EmployeeNum AND IsHidden=0 " ; if (PrefC.GetBool(PrefName.DistributorKey) && isPrintReport) //OD HQ { command += "LEFT JOIN wikilist_employees ON wikilist_employees.EmployeeNum=employee.EmployeeNum "; } //TODO:add Rate2Hours and Rate2Auto Columns to report. command += @"GROUP BY EmployeeNum ORDER BY employee.LName" ; return(command); }
public static DataTable GetListOrderBy2014(List <EhrPatListElement2014> elementList) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), elementList)); } DataTable table = new DataTable(); string select = "SELECT patient.PatNum,patient.LName,patient.FName"; string from = "FROM patient"; string where = "WHERE TRUE "; //Makes formatting easier when adding additional clauses because they will all be AND clauses. for (int i = 0; i < elementList.Count; i++) { switch (elementList[i].Restriction) { case EhrRestrictionType.Birthdate: //--------------------------------------------------------------------------------------------------------------------------- select += ",patient.BirthDate, ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5))) AS Age"; from += ""; //only selecting from patient table where += "AND ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5)))" + GetOperandText(elementList[i].Operand) + "" + PIn.String(elementList[i].CompareString) + " "; break; case EhrRestrictionType.Gender: //------------------------------------------------------------------------------------------------------------------------------ select += ",patient.Gender"; //will look odd if user adds multiple gender columns, enum needs to be "decoded" when filling grid. break; case EhrRestrictionType.LabResult: //--------------------------------------------------------------------------------------------------------------------------- //TODO Units from += ",ehrlab AS ehrlab" + i + ",ehrlabresult AS ehrlabresult" + i + " "; where += "AND ehrlab" + i + ".PatNum=patient.PatNum AND ehrlab" + i + ".EhrLabNum=ehrlabresult" + i + ".EhrLabNum "; //join where += "AND ('" + elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierID OR '" + elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierIDAlt) "; //filter, LOINC of lab observation if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880) { where += "AND ehrlabresult" + i + ".ObservationDateTime >=" + POut.Date(elementList[i].StartDate) + " "; //on or after this date } if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880) { where += "AND ehrlabresult" + i + ".ObservationDateTime <=" + POut.Date(elementList[i].EndDate) + " "; //on or before this date } switch (elementList[i].LabValueType) { //CE and CWE should be SNOMEDCT codes, string compare elementList[i].LabValue to ehrlabresult.ObservationValueCodedElementID or ObservationValueCodedElementIDAlt case HL70125.CE: case HL70125.CWE: select += ",(CASE WHEN ehrlabresult" + i + ".ObservationValueCodedElementID='' THEN ehrlabresult" + i + ".ObservationValueCodedElementIDAlt ELSE ehrlabresult" + i + ".ObservationValueCodedElementID END) AS LabValue"; where += "AND (ehrlabresult" + i + ".ObservationValueCodedElementID='" + elementList[i].LabValue + "' OR " + "ehrlabresult" + i + ".ObservationValueCodedElementIDAlt='" + elementList[i].LabValue + "') " + "AND (ehrlabresult" + i + ".ValueType='CWE' OR ehrlabresult" + i + ".ValueType='CE') "; break; //DT is stored as a string in ehrlabresult.ObservationValueDateTime as YYYY[MM[DD]] case HL70125.DT: select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateFormatColumn("RPAD(ehrlabresult"+i+".ObservationValueDateTime,8,'01')","%m/%d/%Y"); where += "AND " + DbHelper.DateColumn("RPAD(ehrlabresult" + i + ".ObservationValueDateTime,8,'01')") + GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' " + "AND ehrlabresult" + i + ".ValueType='DT' "; break; //TS is YYYYMMDDHHMMSS, string compare case HL70125.TS: select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s"); where += "AND ehrlabresult" + i + ".ObservationValueDateTime " //+POut.DateT(PIn.DateT(DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s"))) + GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' " + "AND ehrlabresult" + i + ".ValueType='TS' "; break; //00:00:00 case HL70125.TM: select += ",ehrlabresult" + i + ".ObservationValueTime"; where += "AND ehrlabresult" + i + ".ObservationValueTime" + GetOperandText(elementList[i].Operand) + "'" + POut.TSpan(PIn.TSpan(elementList[i].LabValue)) + "' " + "AND ehrlabresult" + i + ".ValueType='TM' "; break; case HL70125.SN: select += ",CONCAT(CONCAT(CONCAT(ehrlabresult" + i + ".ObservationValueComparator,ehrlabresult" + i + ".ObservationValueNumber1),ehrlabresult" + i + ".ObservationValueSeparatorOrSuffix),ehrlabresult" + i + ".ObservationValueNumber2)"; where += "AND ehrlabresult" + i + ".ValueType='SN' "; break; case HL70125.NM: select += ",ehrlabresult" + i + ".ObservationValueNumeric"; where += "AND ehrlabresult" + i + ".ObservationValueNumeric" + GetOperandText(elementList[i].Operand) + POut.Double(PIn.Double(elementList[i].LabValue)) + " " + "AND ehrlabresult" + i + ".ValueType='NM' "; break; case HL70125.FT: case HL70125.ST: case HL70125.TX: select += ",ehrlabresult" + i + ".ObservationValueText"; //where+="AND ehrlabresult"+i+".ObservationValueText"+GetOperandText(elementList[i].Operand)+POut.String(elementList[i].LabValue)+" " where += "AND (ehrlabresult" + i + ".ValueType='FT' OR ehrlabresult" + i + ".ValueType='ST' OR ehrlabresult" + i + ".ValueType='TX') "; break; } select += ",ehrlabresult" + i + ".ObservationDateTime "; //select+=",labresult"+i+".ObsValue,labresult"+i+".DateTimeTest";//format column name when filling grid. //from+=",labresult AS labresult"+i+", labpanel AS labpanel"+i; //where+="AND labpanel"+i+".LabpanelNum=labresult"+i+".LabpanelNum AND patient.PatNum=labpanel"+i+".PatNum ";//join //where+="AND labresult"+i+".TestId='"+elementList[i].CompareString+"' " // +"AND labresult"+i+".ObsValue"+GetOperandText(elementList[i].Operand)+"'"+PIn.String(elementList[i].LabValue)+"' ";//filter //if(elementList[i].StartDate!=null && elementList[i].StartDate.Year>1880) { // where+="AND labresult"+i+".DateTimeTest>"+POut.Date(elementList[i].StartDate)+" ";//after this date //} //if(elementList[i].EndDate!=null && elementList[i].EndDate.Year>1880) { // where+="AND labresult"+i+".DateTimeTest<"+POut.Date(elementList[i].EndDate)+" ";//before this date //} break; case EhrRestrictionType.Medication: //-------------------------------------------------------------------------------------------------------------------------- select += ",medicationpat" + i + ".DateStart"; //Name of medication will be in column title. from += ",medication AS medication" + i + ", medicationpat AS medicationpat" + i; where += "AND medicationpat" + i + ".PatNum=patient.PatNum "; //join //This is unusual. Part of the join logic is in the code below because medicationPat.MedicationNum might be 0 if it came from newcrop. where += "AND ((medication" + i + ".MedicationNum=MedicationPat" + i + ".MedicationNum AND medication" + i + ".MedName LIKE '%" + PIn.String(elementList[i].CompareString) + "%') " + " OR (medication" + i + ".MedicationNum=0 AND medicationpat" + i + ".MedDescript LIKE '%" + PIn.String(elementList[i].CompareString) + "%')) "; if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880) { where += "AND medicationpat" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date } if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880) { where += "AND medicationpat" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date } break; case EhrRestrictionType.Problem: //----------------------------------------------------------------------------------------------------------------------------- select += ",disease" + i + ".DateStart"; //Name of problem will be in column title. from += ",disease AS disease" + i + ", diseasedef AS diseasedef" + i; where += "AND diseasedef" + i + ".DiseaseDefNum=disease" + i + ".DiseaseDefNum AND disease" + i + ".PatNum=patient.PatNum "; //join where += "AND (diseasedef" + i + ".ICD9Code='" + PIn.String(elementList[i].CompareString) + "' OR diseasedef" + i + ".SnomedCode='" + PIn.String(elementList[i].CompareString) + "') "; //filter if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880) { where += "AND disease" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date } if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880) { where += "AND disease" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date } break; case EhrRestrictionType.Allergy: //----------------------------------------------------------------------------------------------------------------------------- select += ",allergy" + i + ".DateAdverseReaction"; //Name of allergy will be in column title. from += ",allergy AS allergy" + i + ", allergydef AS allergydef" + i; where += "AND allergydef" + i + ".AllergyDefNum=allergy" + i + ".AllergyDefNum AND allergy" + i + ".PatNum=patient.PatNum "; //join where += "AND allergydef" + i + ".Description='" + PIn.String(elementList[i].CompareString) + "' "; //filter if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880) { where += "AND allergy" + i + ".DateAdverseReaction>" + POut.Date(elementList[i].StartDate) + " "; //after this date } if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880) { where += "AND allergy" + i + ".DateAdverseReaction<" + POut.Date(elementList[i].EndDate) + " "; //before this date } break; case EhrRestrictionType.CommPref: //---------------------------------------------------------------------------------------------------------------------------- select += ",patient.PreferContactConfidential"; from += ""; //only selecting from patient table where += "AND patient.PreferContactConfidential=" + PIn.Int(contactMethodHelper(elementList[i].CompareString)) + " "; break; default: //should never happen. continue; } } string command = select + " " + from + " " + where; return(Db.GetTable(command)); }