/// <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);
}
///<summary>Returns the HQ-level default clearinghouse. You must manually override using OverrideFields if needed. If no default present, returns null.</summary>
public static Clearinghouse GetDefaultDental()
{
//No need to check RemotingRole; no call to db.
return(GetClearinghouse(PrefC.GetLong(PrefName.ClearinghouseDefaultDent)));
}
///<summary>this code is similar to code in the phone tracking server. But here, we frequently only change clockStatus and ColorBar by setting employeeNum=-1. If employeeNum is not -1, then EmployeeName also gets set. If employeeNum==0, then clears employee from that row.</summary>
public static void SetPhoneStatus(ClockStatusEnum clockStatus, int extens, long employeeNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), clockStatus, extens, employeeNum);
return;
}
string command = @"SELECT phoneempdefault.EmployeeNum,phoneempdefault.IsTriageOperator,Description,phoneempdefault.EmpName,HasColor,phone.ClockStatus "
+ "FROM phone "
+ "LEFT JOIN phoneempdefault ON phone.Extension=phoneempdefault.PhoneExt "
+ "WHERE phone.Extension=" + POut.Long(extens);
DataTable tablePhone = Db.GetTable(command);
if (tablePhone.Rows.Count == 0)
{
//It would be nice if we could create a phone row for this extension.
return;
}
long empNum = PIn.Long(tablePhone.Rows[0]["EmployeeNum"].ToString());
bool isTriageOperator = PIn.Bool(tablePhone.Rows[0]["IsTriageOperator"].ToString());
string empName = PIn.String(tablePhone.Rows[0]["EmpName"].ToString());
string clockStatusDb = PIn.String(tablePhone.Rows[0]["ClockStatus"].ToString());
Employee emp = Employees.GetEmp(employeeNum);
if (emp != null) //A new employee is going to take over this extension.
{
empName = emp.FName;
empNum = emp.EmployeeNum;
}
else if (employeeNum == 0) //Clear the employee from that row.
{
empName = "";
empNum = 0;
}
//if these values are null because of missing phoneempdefault row, they will default to false
//PhoneEmpStatusOverride statusOverride=(PhoneEmpStatusOverride)PIn.Int(tablePhone.Rows[0]["StatusOverride"].ToString());
bool hasColor = PIn.Bool(tablePhone.Rows[0]["HasColor"].ToString());
#region DateTimeStart
//When a user shows up as a color on the phone panel, we want a timer to be constantly going to show how long they've been off the phone.
string dateTimeStart = "";
//It's possible that a new user has never clocked in before, therefore their clockStatus will be empty. Simply set it to the status that they are trying to go to.
if (clockStatusDb == "")
{
clockStatusDb = clockStatus.ToString();
}
if (clockStatus == ClockStatusEnum.Break ||
clockStatus == ClockStatusEnum.Lunch)
{
//The user is going on Lunch or Break. Start the DateTimeStart counter so we know how long they have been gone.
dateTimeStart = "DateTimeStart=NOW(), ";
}
else if (clockStatus == ClockStatusEnum.Home)
{
//User is going Home. Always clear the DateTimeStart column no matter what.
dateTimeStart = "DateTimeStart='0001-01-01', ";
}
else //User shows as a color on big phones and is not going to a status of Home, Lunch, or Break. Example: Available, Training etc.
//Get the current clock status from the database.
{
ClockStatusEnum clockStatusCur = (ClockStatusEnum)Enum.Parse(typeof(ClockStatusEnum), clockStatusDb);
//Start the clock if the user is going from a break status to any other non-break status.
if (clockStatusCur == ClockStatusEnum.Home ||
clockStatusCur == ClockStatusEnum.Lunch ||
clockStatusCur == ClockStatusEnum.Break)
{
//The user is clocking in from home, lunch, or break. Start the timer up.
if (hasColor) //Only start up the timer when someone with color clocks in.
{
dateTimeStart = "DateTimeStart=NOW(), ";
}
else //Someone with no color then reset the timer. They are back from break, that's all we need to know.
{
dateTimeStart = "DateTimeStart='0001-01-01', ";
}
}
}
#endregion
//Update the phone row to reflect the new clock status of the user.
string clockStatusNew = clockStatus.ToString();
if (clockStatus == ClockStatusEnum.None)
{
clockStatusNew = "";
}
command = "UPDATE phone SET ClockStatus='" + POut.String(clockStatusNew) + "', "
+ dateTimeStart
//+"ColorBar=-1, " //ColorBar is now determined at runtime by OD using Phones.GetPhoneColor.
+ "EmployeeNum=" + POut.Long(empNum) + ", "
+ "EmployeeName='" + POut.String(empName) + "' "
+ "WHERE Extension=" + extens;
Db.NonQ(command);
if (PrefC.GetBool(PrefName.DockPhonePanelShow)) //hq only
//Zero out any duplicate phone table rows for this employee.
//This is possible if a user logged off and another employee logs into their computer. This would cause duplicate entries in the big phones window.
{
UpdatePhoneToEmpty(employeeNum, extens);
}
}
public static ApptReminderRule CreateDefaultReminderRule(ApptReminderType ruleType, long clinicNum = 0, bool isBeforeAppointment = true)
{
ApptReminderRule rule = null;
switch (ruleType)
{
case ApptReminderType.Reminder:
rule = new ApptReminderRule()
{
ClinicNum = clinicNum, //works with practice too because _listClinics[0] is a spoofed "Practice/Defaults" clinic with ClinicNum=0
TypeCur = ApptReminderType.Reminder,
TSPrior = TimeSpan.FromHours(3),
TemplateSMS = "Appointment Reminder: [NameF] is scheduled for [ApptTime] on [ApptDate] at [ClinicName]. If you have questions call [ClinicPhone].", //default message
TemplateEmail = @"[NameF],
Your appointment is scheduled for [ApptTime] on [ApptDate] at [OfficeName]. If you have questions, call <a href=""tel:[OfficePhone]"">[OfficePhone]</a>.",
TemplateEmailSubject = "Appointment Reminder", //default subject
TemplateSMSAggShared = "Appointment Reminder:\n[Appts]\nIf you have questions call [ClinicPhone].",
TemplateSMSAggPerAppt = "[NameF] is scheduled for [ApptTime] on [ApptDate] at [ClinicName].",
TemplateEmailSubjAggShared = "Appointment Reminder",
TemplateEmailAggShared = @"[Appts]
If you have questions, call <a href=""tel:[OfficePhone]"">[OfficePhone]</a>.",
TemplateEmailAggPerAppt = "[NameF] is scheduled for [ApptTime] on [ApptDate] at [ClinicName].",
//SendOrder="0,1,2" //part of ctor
};
break;
case ApptReminderType.ConfirmationFutureDay:
rule = new ApptReminderRule()
{
ClinicNum = clinicNum, //works with practice too because _listClinics[0] is a spoofed "Practice/Defaults" clinic with ClinicNum=0
TypeCur = ApptReminderType.ConfirmationFutureDay,
TSPrior = TimeSpan.FromDays(7),
TemplateSMS = "[NameF] is scheduled for [ApptTime] on [ApptDate] at [OfficeName]. Reply [ConfirmCode] to confirm or call [OfficePhone].", //default message
TemplateEmail = @"[NameF],
Your appointment is scheduled for [ApptTime] on [ApptDate] at [OfficeName]. Click <a href=""[ConfirmURL]"">[ConfirmURL]</a> to confirm " +
@"or call <a href=""tel:[OfficePhone]"">[OfficePhone]</a>.",
TemplateEmailSubject = "Appointment Confirmation", //default subject
TemplateSMSAggShared = "[Appts]\nReply [ConfirmCode] to confirm or call [OfficePhone].",
TemplateSMSAggPerAppt = "[NameF] is scheduled for [ApptTime] on [ApptDate] at [ClinicName].",
TemplateEmailSubjAggShared = "Appointment Confirmation",
TemplateEmailAggShared = @"[Appts]
Click <a href=""[ConfirmURL]"">[ConfirmURL]</a> to confirm or call <a href=""tel:[OfficePhone]"">[OfficePhone]</a>.",
TemplateEmailAggPerAppt = "[NameF] is scheduled for [ApptTime] on [ApptDate] at [ClinicName].",
//SendOrder="0,1,2" //part of ctor
DoNotSendWithin = TimeSpan.FromDays(1).Add(TimeSpan.FromHours(10)),
TemplateAutoReply = "Thank you for confirming your appointment with [OfficeName]. We look forward to seeing you.",
TemplateAutoReplyAgg = "Thank you for confirming your appointments with [OfficeName]. We look forward to seeing you",
IsAutoReplyEnabled = true,
};
break;
case ApptReminderType.PatientPortalInvite:
if (isBeforeAppointment)
{
rule = new ApptReminderRule()
{
ClinicNum = clinicNum,
TypeCur = ApptReminderType.PatientPortalInvite,
TSPrior = TimeSpan.FromDays(7),
TemplateEmail = @"[NameF],
In preparation for your upcoming dental appointment at [OfficeName], we invite you to log in to our Patient Portal. " + @"
There you can view your scheduled appointments, view your treatment plan, send a message to your provider, and view your account balance. " + @"
Visit our <a href=""[PatientPortalURL]"">Patient Portal</a> and use this temporary user name and password to log in:
User name: [UserName]
Password: [Password]
If you have any questions, please give us a call at <a href=""tel:[OfficePhone]"">[OfficePhone]</a>, and we would be happy to answer any of your questions.",
TemplateEmailSubject = "Patient Portal Invitation",
TemplateEmailSubjAggShared = "Patient Portal Invitation",
TemplateEmailAggShared = @"[NameF],
In preparation for your upcoming dental appointments at [OfficeName], we invite you to log in to our Patient Portal. " + @"
There you can view your scheduled appointments, view your treatment plan, send a message to your provider, and view your account balance. " + @"
Visit our <a href=""[PatientPortalURL]"">Patient Portal</a> and use these temporary user names and passwords to log in:
[Credentials]
If you have any questions, please give us a call at <a href=""tel:[OfficePhone]"">[OfficePhone]</a>, and we would be happy to answer any of your questions.",
TemplateEmailAggPerAppt = @"[NameF]
User name: [UserName]
Password: [Password]
",
SendOrder = "2" //Email only
};
break;
}
else //Same day
{
rule = new ApptReminderRule()
{
ClinicNum = clinicNum,
TypeCur = ApptReminderType.PatientPortalInvite,
TSPrior = new TimeSpan(-1, 0, 0), //Send 1 hour after the appointment
TemplateEmail = @"[NameF],
Thank you for coming in to visit [OfficeName] today. As a follow up to your appointment, we invite you to log in to our Patient Portal. " + @"
There you can view your scheduled appointments, view your treatment plan, send a message to your provider, and view your account balance. " + @"
Visit <a href=""[PatientPortalURL]"">Patient Portal</a> and use this temporary user name and password to log in:
User name: [UserName]
Password: [Password]
If you have any questions, please give us a call at <a href=""tel:[OfficePhone]"">[OfficePhone]</a>, and we would be happy to answer any of your questions.",
TemplateEmailSubject = "Patient Portal Invitation",
TemplateEmailSubjAggShared = "Patient Portal Invitation",
TemplateEmailAggShared = @"[NameF],
Thank you for coming in to visit [OfficeName] today. As a follow up to your appointment, we invite you to log in to our Patient Portal. " + @"
There you can view your scheduled appointments, view your treatment plan, send a message to your provider, and view your account balance. " + @"
Visit <a href=""[PatientPortalURL]"">Patient Portal</a> and use these temporary user names and passwords to log in:
[Credentials]
If you have any questions, please give us a call at <a href=""tel:[OfficePhone]"">[OfficePhone]</a>, and we would be happy to answer any of your questions.",
TemplateEmailAggPerAppt = @"[NameF]
User name: [UserName]
Password: [Password]
",
SendOrder = "2" //Email only
};
break;
}
case ApptReminderType.ScheduleThankYou:
rule = new ApptReminderRule()
{
ClinicNum = clinicNum, //works with practice too because _listClinics[0] is a spoofed "Practice/Defaults" clinic with ClinicNum=0
TypeCur = ApptReminderType.ScheduleThankYou,
TSPrior = new TimeSpan(-1, 0, 0), //default to send thank you 1 hour after creating appointment.
TemplateSMS = "[NameF], thank you for scheduling with [OfficeName] on [ApptDate] at [ApptTime].", //default message
TemplateEmail = @"[NameF],
Thank you for scheduling your appointment with [OfficeName] on [ApptDate] at [ApptTime]. If you have questions, call <a href=""tel:[OfficePhone]"">[OfficePhone]</a>.",
TemplateEmailSubject = "Appointment Thank You", //default subject
TemplateSMSAggShared = "Thank you for scheduling these appointments: [Appts]",
TemplateSMSAggPerAppt = "[NameF] for [ApptTime] on [ApptDate] at [ClinicName]",
TemplateEmailSubjAggShared = "Appointment Thank You",
TemplateEmailAggShared = @"Thank you for scheduling these appointments: [Appts]
If you have questions, call <a href=""tel:[OfficePhone]"">[OfficePhone]</a>.",
TemplateEmailAggPerAppt = "[NameF] is scheduled for [ApptTime] on [ApptDate] at [ClinicName].",
//SendOrder="0,1,2" //part of ctor
DoNotSendWithin = new TimeSpan(2, 0, 0), //Do not send within 2 hours of appointment.AptDateTime.
};
break;
}
if (PrefC.GetBool(PrefName.EmailDisclaimerIsOn))
{
rule.TemplateEmail += "\r\n\r\n\r\n[EmailDisclaimer]";
rule.TemplateEmailAggShared += "\r\n\r\n\r\n[EmailDisclaimer]";
}
return(rule);
}
///<summary>In progress. Probably needs a different name. Info must be validated first.</summary>
public static string GenerateMessageText(Clearinghouse clearhouse, Carrier carrier, Provider billProv, Clinic clinic, InsPlan insPlan, Patient subscriber, InsSub insSub)
{
int batchNum = Clearinghouses.GetNextBatchNumber(clearhouse);
string groupControlNumber = batchNum.ToString(); //Must be unique within file. We will use batchNum
int transactionNum = 1;
StringBuilder strb = new StringBuilder();
//Interchange Control Header
strb.AppendLine("ISA*00* *" //ISA01,ISA02: 00 + 10 spaces
+ "00* *" //ISA03,ISA04: 00 + 10 spaces
+ clearhouse.ISA05 + "*" //ISA05: Sender ID type: ZZ=mutually defined. 30=TIN. Validated
+ X12Generator.GetISA06(clearhouse) + "*" //ISA06: Sender ID(TIN). Or might be TIN of Open Dental
+ clearhouse.ISA07 + "*" //ISA07: Receiver ID type: ZZ=mutually defined. 30=TIN. Validated
+ Sout(clearhouse.ISA08, 15, 15) + "*" //ISA08: Receiver ID. Validated to make sure length is at least 2.
+ DateTime.Today.ToString("yyMMdd") + "*" //ISA09: today's date
+ DateTime.Now.ToString("HHmm") + "*" //ISA10: current time
+ "U*00401*" //ISA11 and ISA12.
//ISA13: interchange control number, right aligned:
+ batchNum.ToString().PadLeft(9, '0') + "*"
+ "0*" //ISA14: no acknowledgment requested
+ clearhouse.ISA15 + "*" //ISA15: T=Test P=Production. Validated.
+ ":~"); //ISA16: use ':'
//Functional Group Header
strb.AppendLine("GS*HS*" //GS01: HS for 270 benefit inquiry
+ X12Generator.GetGS02(clearhouse) + "*" //GS02: Senders Code. Sometimes Jordan Sparks. Sometimes the sending clinic.
+ Sout(clearhouse.GS03, 15, 2) + "*" //GS03: Application Receiver's Code
+ DateTime.Today.ToString("yyyyMMdd") + "*" //GS04: today's date
+ DateTime.Now.ToString("HHmm") + "*" //GS05: current time
+ groupControlNumber + "*" //GS06: Group control number. Max length 9. No padding necessary.
+ "X*" //GS07: X
+ "004010X092~"); //GS08: Version
//Beginning of transaction--------------------------------------------------------------------------------
int seg = 0; //count segments for the ST-SE transaction
//Transaction Set Header
//ST02 Transact. control #. Must be unique within ISA
seg++;
strb.AppendLine("ST*270*" //ST01
+ transactionNum.ToString().PadLeft(4, '0') + "~"); //ST02
seg++;
strb.AppendLine("BHT*0022*13*" //BHT02: 13=request
+ transactionNum.ToString().PadLeft(4, '0') + "*" //BHT03. Can be same as ST02
+ DateTime.Now.ToString("yyyyMMdd") + "*" //BHT04: Date
+ DateTime.Now.ToString("HHmmss") + "~"); //BHT05: Time, BHT06: not used
//HL Loops-----------------------------------------------------------------------------------------------
int HLcount = 1;
//2000A HL: Information Source--------------------------------------------------------------------------
seg++;
strb.AppendLine("HL*" + HLcount.ToString() + "*" //HL01: Heirarchical ID. Here, it's always 1.
+ "*" //HL02: No parent. Not used
+ "20*" //HL03: Heirarchical level code. 20=Information source
+ "1~"); //HL04: Heirarchical child code. 1=child HL present
//2100A NM1
seg++;
strb.AppendLine("NM1*PR*" //NM101: PR=Payer
+ "2*" //NM102: 2=Non person
+ Sout(carrier.CarrierName, 35) + "*" //NM103: Name Last.
+ "****" //NM104-07 not used
+ "PI*" //NM108: PI=PayorID
+ Sout(carrier.ElectID, 80, 2) + "~"); //NM109: PayorID. Validated to be at least length of 2.
HLcount++;
//2000B HL: Information Receiver------------------------------------------------------------------------
seg++;
strb.AppendLine("HL*" + HLcount.ToString() + "*" //HL01: Heirarchical ID. Here, it's always 2.
+ "1*" //HL02: Heirarchical parent id number. 1 in this simple message.
+ "21*" //HL03: Heirarchical level code. 21=Information receiver
+ "1~"); //HL04: Heirarchical child code. 1=child HL present
seg++;
//2100B NM1: Information Receiver Name
strb.AppendLine("NM1*1P*" //NM101: 1P=Provider
+ (billProv.IsNotPerson?"2":"1") + "*" //NM102: 1=person,2=non-person
+ Sout(billProv.LName, 35) + "*" //NM103: Last name
+ Sout(billProv.FName, 25) + "*" //NM104: First name
+ Sout(billProv.MI, 25, 1) + "*" //NM105: Middle name
+ "*" //NM106: not used
+ "*" //NM107: Name suffix. not used
+ "XX*" //NM108: ID code qualifier. 24=EIN. 34=SSN, XX=NPI
+ Sout(billProv.NationalProvID, 80) + "~"); //NM109: ID code. NPI validated
//2100B REF: Information Receiver ID
seg++;
strb.Append("REF*");
if (billProv.UsingTIN)
{
strb.Append("TJ*"); //REF01: qualifier. TJ=Federal TIN
}
else //SSN
{
strb.Append("SY*"); //REF01: qualifier. SY=SSN
}
strb.AppendLine(Sout(billProv.SSN, 30) + "~"); //REF02: ID
//2100B N3: Information Receiver Address
seg++;
if (PrefC.GetBool(PrefName.UseBillingAddressOnClaims))
{
strb.Append("N3*" + Sout(PrefC.GetString(PrefName.PracticeBillingAddress), 55)); //N301: Address
}
else if (clinic == null)
{
strb.Append("N3*" + Sout(PrefC.GetString(PrefName.PracticeAddress), 55)); //N301: Address
}
else
{
strb.Append("N3*" + Sout(clinic.Address, 55)); //N301: Address
}
if (PrefC.GetBool(PrefName.UseBillingAddressOnClaims))
{
if (PrefC.GetString(PrefName.PracticeBillingAddress2) == "")
{
strb.AppendLine("~");
}
else
{
//N302: Address2. Optional.
strb.AppendLine("*" + Sout(PrefC.GetString(PrefName.PracticeBillingAddress2), 55) + "~");
}
}
else if (clinic == null)
{
if (PrefC.GetString(PrefName.PracticeAddress2) == "")
{
strb.AppendLine("~");
}
else
{
//N302: Address2. Optional.
strb.AppendLine("*" + Sout(PrefC.GetString(PrefName.PracticeAddress2), 55) + "~");
}
}
else
{
if (clinic.Address2 == "")
{
strb.AppendLine("~");
}
else
{
//N302: Address2. Optional.
strb.AppendLine("*" + Sout(clinic.Address2, 55) + "~");
}
}
//2100B N4: Information Receiver City/State/Zip
seg++;
if (PrefC.GetBool(PrefName.UseBillingAddressOnClaims))
{
strb.AppendLine("N4*" + Sout(PrefC.GetString(PrefName.PracticeBillingCity), 30) + "*" //N401: City
+ Sout(PrefC.GetString(PrefName.PracticeBillingST), 2) + "*" //N402: State
+ Sout(PrefC.GetString(PrefName.PracticeBillingZip).Replace("-", ""), 15) + "~"); //N403: Zip
}
else if (clinic == null)
{
strb.AppendLine("N4*" + Sout(PrefC.GetString(PrefName.PracticeCity), 30) + "*" //N401: City
+ Sout(PrefC.GetString(PrefName.PracticeST), 2) + "*" //N402: State
+ Sout(PrefC.GetString(PrefName.PracticeZip).Replace("-", ""), 15) + "~"); //N403: Zip
}
else
{
strb.AppendLine("N4*" + Sout(clinic.City, 30) + "*" //N401: City
+ Sout(clinic.State, 2) + "*" //N402: State
+ Sout(clinic.Zip.Replace("-", ""), 15) + "~"); //N403: Zip
}
//2100B PRV: Information Receiver Provider Info
seg++;
//PRV*PE*ZZ*1223G0001X~
strb.AppendLine("PRV*PE*" //PRV01: Provider Code. PE=Performing. There are many other choices.
+ "ZZ*" //PRV02: ZZ=Mutually defined = health care provider taxonomy code
+ X12Generator.GetTaxonomy(billProv) + "~"); //PRV03: Specialty code
HLcount++;
//2000C HL: Subscriber-----------------------------------------------------------------------------------
seg++;
strb.AppendLine("HL*" + HLcount.ToString() + "*" //HL01: Heirarchical ID. Here, it's always 3.
+ "2*" //HL02: Heirarchical parent id number. 2 in this simple message.
+ "22*" //HL03: Heirarchical level code. 22=Subscriber
+ "0~"); //HL04: Heirarchical child code. 0=no child HL present (no dependent)
//2000C TRN: Subscriber Trace Number
seg++;
strb.AppendLine("TRN*1*" //TRN01: Trace Type Code. 1=Current Transaction Trace Numbers
+ "1*" //TRN02: Trace Number. We don't really have a good primary key yet. Keep it simple. Use 1.
+ "1" + billProv.SSN + "~"); //TRN03: Entity Identifier. First digit is 1=EIN. Next 9 digits are EIN. Length validated.
//2100C NM1: Subscriber Name
seg++;
strb.AppendLine("NM1*IL*" //NM101: IL=Insured or Subscriber
+ "1*" //NM102: 1=Person
+ Sout(subscriber.LName, 35) + "*" //NM103: LName
+ Sout(subscriber.FName, 25) + "*" //NM104: FName
+ Sout(subscriber.MiddleI, 25) + "*" //NM105: MiddleName
+ "*" //NM106: not used
+ "*" //NM107: suffix. Not present in Open Dental yet.
+ "MI*" //NM108: MI=MemberID
+ Sout(insSub.SubscriberID.Replace("-", ""), 80) + "~"); //NM109: Subscriber ID. Validated to be L>2.
//2100C REF: Subscriber Additional Information. Without this, old plans seem to be frequently returned.
seg++;
strb.AppendLine("REF*6P*" //REF01: 6P=GroupNumber
+ Sout(insPlan.GroupNum, 30) + "~"); //REF02: Supplemental ID. Validated.
//2100C DMG: Subscriber Demographic Information
seg++;
strb.AppendLine("DMG*D8*" //DMG01: Date Time Period Qualifier. D8=CCYYMMDD
+ subscriber.Birthdate.ToString("yyyyMMdd") + "~"); //DMG02: Subscriber birthdate. Validated
//DMG03: Gender code. Situational. F or M. Since this was left out in the example,
//and since we don't want to send the wrong gender, we will not send this element.
//2100C DTP: Subscriber Date. Deduced through trial and error that this is required by EHG even though not by X12 specs.
seg++;
strb.AppendLine("DTP*307*" //DTP01: Qualifier. 307=Eligibility
+ "D8*" //DTP02: Format Qualifier.
+ DateTime.Today.ToString("yyyyMMdd") + "~"); //DTP03: Date
//2110C EQ: Subscriber Eligibility or Benefit Enquiry Information
//X12 documentation seems to say that we can loop this 99 times to request very specific benefits.
//ClaimConnect wants to see either an EQ*30 for "an eligibility request", or an EQ*35 for "a general benefits request".
//The director of vendor implementation at ClaimConnect has informed us that we should send an EQ*35 to get the full set of benefits.
seg++;
strb.AppendLine("EQ*35~"); //Dental Care
//seg++;
//strb.AppendLine("EQ*30~");//EQ01: 30=General Coverage
//seg++;
//strb.AppendLine("EQ*23~");//Diagnostic
//seg++;
//strb.AppendLine("EQ*4~");//Diagnostic Xray
//seg++;
//strb.AppendLine("EQ*41~");//Routine Preventive
//seg++;
//strb.AppendLine("EQ*25~");//Restorative
//seg++;
//strb.AppendLine("EQ*26~");//Endo
//seg++;
//strb.AppendLine("EQ*24~");//Perio
//seg++;
//strb.AppendLine("EQ*40~");//Oral Surgery
//seg++;
//strb.AppendLine("EQ*36~");//Crowns
//seg++;
//strb.AppendLine("EQ*39~");//Prosth
//seg++;
//strb.AppendLine("EQ*27~");//Maxillofacial Prosth
//seg++;
//strb.AppendLine("EQ*37~");//Accident
//seg++;
//strb.AppendLine("EQ*38~");//Ortho
//seg++;
//strb.AppendLine("EQ*28~");//Adjunctive
//
//2000D If we add a dependent loop it would go here. It would be about 20 lines.
//2100D, etc
//EQ series, etc.
//Not allowed to send this unless subscriber and dependent are different
//We would also have to add code to process the EBs which distinguishes between subscribers and dependents.
//
//Transaction Trailer
seg++;
strb.AppendLine("SE*"
+ seg.ToString() + "*" //SE01: Total segments, including ST & SE
+ transactionNum.ToString().PadLeft(4, '0') + "~");
//End of transaction--------------------------------------------------------------------------------------
//Functional Group Trailer
strb.AppendLine("GE*" + transactionNum.ToString() + "*" //GE01: Number of transaction sets included
+ groupControlNumber + "~"); //GE02: Group Control number. Must be identical to GS06
//Interchange Control Trailer
strb.AppendLine("IEA*1*" //IEA01: number of functional groups
+ batchNum.ToString().PadLeft(9, '0') + "~"); //IEA02: Interchange control number
return(strb.ToString());
/*
* return @"
* ISA*00* *00* *30*AA0989922 *30*330989922 *030519*1608*U*00401*000012145*1*T*:~
* GS*HS*AA0989922*330989922*20030519*1608*12145*X*004010X092~
* ST*270*0001~
* BHT*0022*13*ASX012145WEB*20030519*1608~
* HL*1**20*1~
* NM1*PR*2*Metlife*****PI*65978~
* HL*2*1*21*1~
* NM1*1P*1*PROVLAST*PROVFIRST****XX*1234567893~
* REF*TJ*200384584~
* N3*JUNIT ROAD~
* N4*CHICAGO*IL*60602~
* PRV*PE*ZZ*1223G0001X~
* HL*3*2*22*0~
* TRN*1*12145*1AA0989922~
* NM1*IL*1*SUBLASTNAME*SUBFIRSTNAME****MI*123456789~
* DMG*D8*19750323~
* DTP*307*D8*20030519~
* EQ*30~
* SE*17*0001~
* GE*1*12145~
* IEA*1*000012145~";
*/
//return "ISA*00* *00* *30*AA0989922 *30*330989922 *030519*1608*U*00401*000012145*1*T*:~GS*HS*AA0989922*330989922*20030519*1608*12145*X*004010X092~ST*270*0001~BHT*0022*13*ASX012145WEB*20030519*1608~HL*1**20*1~NM1*PR*2*Metlife*****PI*65978~HL*2*1*21*1~NM1*1P*1*PROVLAST*PROVFIRST****XX*1234567893~REF*TJ*200384584~N3*JUNIT ROAD~N4*CHICAGO*IL*60602~PRV*PE*ZZ*1223G0001X~HL*3*2*22*0~TRN*1*12145*1AA0989922~NM1*IL*1*SUBLASTNAME*SUBFIRSTNAME****MI*123456789~DMG*D8*19750323~DTP*307*D8*20030519~EQ*30~SE*17*0001~GE*1*12145~IEA*1*000012145~";
}
/// <summary>Only Called only from FormPayment.butOK click. Only called if the user did not enter any splits. Usually just adds one split for the current patient. But if that would take the balance negative, then it loops through all other family members and creates splits for them. It might still take the current patient negative once all other family members are zeroed out.</summary>
public static List <PaySplit> Allocate(Payment pay) //double amtTot,int patNum,Payment payNum){
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PaySplit> >(MethodBase.GetCurrentMethod(), pay));
}
string command =
"SELECT Guarantor FROM patient "
+ "WHERE PatNum = " + POut.Long(pay.PatNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return(new List <PaySplit>());
}
command =
"SELECT patient.PatNum,EstBalance,PriProv,SUM(InsPayEst)+SUM(Writeoff) insEst_ "
+ "FROM patient "
+ "LEFT JOIN claimproc ON patient.PatNum=claimproc.PatNum "
+ "AND Status=0 " //NotReceived
+ "WHERE Guarantor = " + table.Rows[0][0].ToString() + " "
+ "GROUP BY patient.PatNum,EstBalance,PriProv";
//+" ORDER BY PatNum!="+POut.PInt(pay.PatNum);//puts current patient in position 0 //Oracle does not allow
table = Db.GetTable(command);
List <Patient> pats = new List <Patient>();
Patient pat;
//first, put the current patient at position 0.
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows[i]["PatNum"].ToString() == pay.PatNum.ToString())
{
pat = new Patient();
pat.PatNum = PIn.Long(table.Rows[i][0].ToString());
pat.EstBalance = PIn.Double(table.Rows[i][1].ToString());
if (!PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
pat.EstBalance -= PIn.Double(table.Rows[i]["insEst_"].ToString());
}
pat.PriProv = PIn.Long(table.Rows[i][2].ToString());
pats.Add(pat.Copy());
}
}
//then, do all the rest of the patients.
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows[i]["PatNum"].ToString() == pay.PatNum.ToString())
{
continue;
}
pat = new Patient();
pat.PatNum = PIn.Long(table.Rows[i][0].ToString());
pat.EstBalance = PIn.Double(table.Rows[i][1].ToString());
if (!PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
pat.EstBalance -= PIn.Double(table.Rows[i]["insEst_"].ToString());
}
pat.PriProv = PIn.Long(table.Rows[i][2].ToString());
pats.Add(pat.Copy());
}
//first calculate all the amounts
double amtRemain = pay.PayAmt; //start off with the full amount
double[] amtSplits = new double[pats.Count];
//loop through each family member, starting with current
for (int i = 0; i < pats.Count; i++)
{
if (pats[i].EstBalance == 0 || pats[i].EstBalance < 0)
{
continue; //don't apply paysplits to anyone with a negative balance
}
if (amtRemain < pats[i].EstBalance) //entire remainder can be allocated to this patient
{
amtSplits[i] = amtRemain;
amtRemain = 0;
break;
}
else //amount remaining is more than or equal to the estBal for this family member
{
amtSplits[i] = pats[i].EstBalance;
amtRemain -= pats[i].EstBalance;
}
}
//add any remainder to the split for this patient
amtSplits[0] += amtRemain;
//now create a split for each non-zero amount
PaySplit PaySplitCur;
List <PaySplit> retVal = new List <PaySplit>();
for (int i = 0; i < pats.Count; i++)
{
if (amtSplits[i] == 0)
{
continue;
}
PaySplitCur = new PaySplit();
PaySplitCur.PatNum = pats[i].PatNum;
PaySplitCur.PayNum = pay.PayNum;
PaySplitCur.ProcDate = pay.PayDate;
PaySplitCur.DatePay = pay.PayDate;
PaySplitCur.ClinicNum = pay.ClinicNum;
PaySplitCur.ProvNum = Patients.GetProvNum(pats[i]);
PaySplitCur.SplitAmt = Math.Round(amtSplits[i], CultureInfo.CurrentCulture.NumberFormat.CurrencyDecimalDigits);
//PaySplitCur.InsertOrUpdate(true);
retVal.Add(PaySplitCur);
}
//finally, adjust each EstBalance, but no need to do current patient
//This no longer works here. Must do it when closing payment window somehow
/*for(int i=1;i<pats.Length;i++){
* if(amtSplits[i]==0){
* continue;
* }
* command="UPDATE patient SET EstBalance=EstBalance-"+POut.PDouble(amtSplits[i])
+" WHERE PatNum="+POut.PInt(pats[i].PatNum);
* Db.NonQ(command);
* }*/
return(retVal);
}
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
switch ((PPOWriteoffDateCalc)PrefC.GetInt(PrefName.ReportsPPOwriteoffDefaultToProcDate)) //use procdate
{
case PPOWriteoffDateCalc.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
break;
case PPOWriteoffDateCalc.InsPayDate:
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
break;
case PPOWriteoffDateCalc.ClaimPayDate:
command = @"UPDATE tempdash" + rndStr + @"
SET production=production-(SELECT IFNULL(SUM(claimsnapshot.WriteOff),0) FROM claimproc cp
INNER JOIN claimsnapshot ON cp.ClaimProcNum=claimsnapshot.ClaimProcNum
WHERE claimproc.ProvNum=tempdash" + rndStr + @".ProvNum
AND claimsnapshot.DateTEntry=" + POut.Date(dt) + @"
AND (claimproc.Status=1 OR claimproc.Status=4) )" ;//received or supplemental
break;
}
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>Returns the clearinghouseNum for claims for the supplied payorID. If the payorID was not entered or if no default was set, then 0 is returned.</summary>
public static long AutomateClearinghouseHqSelection(string payorID, EnumClaimMedType medType)
{
//No need to check RemotingRole; no call to db.
//payorID can be blank. For example, Renaissance does not require payorID.
Clearinghouse clearinghouseHq = null;
if (medType == EnumClaimMedType.Dental)
{
if (PrefC.GetLong(PrefName.ClearinghouseDefaultDent) == 0)
{
return(0);
}
clearinghouseHq = GetClearinghouse(PrefC.GetLong(PrefName.ClearinghouseDefaultDent));
}
if (medType == EnumClaimMedType.Medical || medType == EnumClaimMedType.Institutional)
{
if (PrefC.GetLong(PrefName.ClearinghouseDefaultMed) == 0)
{
//No default set, substituting emdeon medical otherwise first medical clearinghouse.
List <Clearinghouse> listClearingHouses = GetDeepCopy(false);
clearinghouseHq = listClearingHouses.FirstOrDefault(x => x.CommBridge == EclaimsCommBridge.EmdeonMedical && x.HqClearinghouseNum == x.ClearinghouseNum);
if (clearinghouseHq == null)
{
clearinghouseHq = listClearingHouses.FirstOrDefault(x => x.Eformat == ElectronicClaimFormat.x837_5010_med_inst && x.HqClearinghouseNum == x.ClearinghouseNum);
}
//If we can't find a clearinghouse at all, just return 0.
if (clearinghouseHq == null)
{
return(0);
}
return(clearinghouseHq.ClearinghouseNum);
}
clearinghouseHq = GetClearinghouse(PrefC.GetLong(PrefName.ClearinghouseDefaultMed));
}
if (clearinghouseHq == null) //we couldn't find a default clearinghouse for that medType. Needs to always be a default.
{
return(0);
}
Clearinghouse clearingHouseOverride = GetClearinghouseByPayorID(payorID);
if (clearingHouseOverride != null) //an override exists for this payorID
{
if (clearingHouseOverride.Eformat == ElectronicClaimFormat.x837D_4010 || clearingHouseOverride.Eformat == ElectronicClaimFormat.x837D_5010_dental ||
clearingHouseOverride.Eformat == ElectronicClaimFormat.Canadian || clearingHouseOverride.Eformat == ElectronicClaimFormat.Ramq)
{ //all dental formats
if (medType == EnumClaimMedType.Dental) //med type matches
{
return(clearingHouseOverride.ClearinghouseNum);
}
}
if (clearingHouseOverride.Eformat == ElectronicClaimFormat.x837_5010_med_inst)
{
if (medType == EnumClaimMedType.Medical || medType == EnumClaimMedType.Institutional) //med type matches
{
return(clearingHouseOverride.ClearinghouseNum);
}
}
}
//no override, so just return the default.
return(clearinghouseHq.ClearinghouseNum);
}
///<summary>Gets the default clinic for texting. Returns null if no clinic is set as default.</summary>
public static Clinic GetDefaultForTexting()
{
return(GetFirstOrDefault(x => x.ClinicNum == PrefC.GetLong(PrefName.TextingDefaultClinicNum)));
}
///<summary>Returns true if any of the preferences: OrthoBandingCodes, OrthoDebondCodes, OrthoVisitCodes aren't blank.</summary>
public static bool HasOrthoCasesEnabled()
{
return(PrefC.GetString(PrefName.OrthoBandingCodes) != "" ||
PrefC.GetString(PrefName.OrthoVisitCodes) != "" ||
PrefC.GetString(PrefName.OrthoDebondCodes) != "");
}
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>Parses comma delimited list of procCodes from the specified OrthoCase proc type preference
///(OrthoBandingCodes, OrthoDebondCodes, OrthoVisitCodes). Returns as list of proc codes.</summary>
public static List <string> GetListProcTypeProcCodes(PrefName procType)
{
//No remoting role check; no call to db
return(PrefC.GetString(procType).Split(',').Select(x => x.Trim()).ToList());
}
public static EmailMessage GetEmailMessageForPortalStatement(Statement stmt, Patient pat)
{
//No need to check RemotingRole; no call to db.
if (stmt.PatNum != pat.PatNum)
{
string logMsg = Lans.g("Statements", "Mismatched PatNums detected between current patient and current statement:") + "\r\n"
+ Lans.g("Statements", "Statement PatNum:") + " " + stmt.PatNum + " " + Lans.g("Statements", "(assumed correct)") + "\r\n"
+ Lans.g("Statements", "Patient PatNum:") + " " + pat.PatNum + " " + Lans.g("Statements", "(possibly incorrect)");
SecurityLogs.MakeLogEntry(Permissions.StatementPatNumMismatch, stmt.PatNum, logMsg, LogSources.Diagnostic);
}
EmailMessage message = new EmailMessage();
message.PatNum = pat.PatNum;
message.ToAddress = pat.Email;
message.FromAddress = EmailAddresses.GetByClinic(pat.ClinicNum).GetFrom();
string emailBody;
if (stmt.EmailSubject != null && stmt.EmailSubject != "")
{
message.Subject = stmt.EmailSubject;
}
else //Subject was not preset, set a default subject.
{
message.Subject = Lans.g("Statements", "New Statement Available");
}
if (stmt.EmailBody != null && stmt.EmailBody != "")
{
emailBody = stmt.EmailBody;
}
else //Body was not preset, set a body text.
{
emailBody = Lans.g("Statements", "Dear") + " [nameFLnoPref],\r\n\r\n"
+ Lans.g("Statements", "A new account statement is available.") + "\r\n\r\n"
+ Lans.g("Statements", "To view your account statement, log on to our portal by following these steps:") + "\r\n\r\n"
+ Lans.g("Statements", "1. Visit the following URL in a web browser:") + " " + PrefC.GetString(PrefName.PatientPortalURL) + ".\r\n"
+ Lans.g("Statements", "2. Enter your credentials to gain access to your account.") + "\r\n"
+ Lans.g("Statements", "3. Click the Account icon on the left and select the Statements tab.");
}
message.BodyText = Statements.ReplaceVarsForEmail(emailBody, pat, stmt);
return(message);
}
///<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");
string command = "SELECT patient.BalTotal,BillingType,FName,patient.InsEst,statement.IsSent,"
+ "IFNULL(MAX(s2.DateSent)," + POut.Date(DateTime.MinValue) + ") LastStatement,"
+ "LName,MiddleI,statement.Mode_,PayPlanDue,Preferred,"
+ "statement.PatNum,statement.StatementNum,statement.SuperFamily "
+ "FROM statement "
+ "LEFT JOIN patient ON statement.PatNum=patient.PatNum "
+ "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 patient.BalTotal,BillingType,FName,patient.InsEst,statement.IsSent,"
+ "LName,MiddleI,statement.Mode_,PayPlanDue,Preferred,"
+ "statement.PatNum,statement.StatementNum,statement.SuperFamily ";
if (orderBy == 0) //BillingType
{
command += "ORDER BY definition.ItemOrder,LName,FName,MiddleI,PayPlanDue";
}
else
{
command += "ORDER BY LName,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();
table.Rows.Add(row);
}
return(table);
}
///<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 GetProcsNotBilled(List <long> listClinicNums, bool includeMedProcs, DateTime dateStart, DateTime dateEnd, bool showProcsBeforeIns)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, includeMedProcs, dateStart, dateEnd, showProcsBeforeIns));
}
string query = "SELECT ";
if (PrefC.GetBool(PrefName.ReportsShowPatNum))
{
query += DbHelper.Concat("CAST(patient.PatNum AS CHAR)", "'-'", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
query += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
query += " AS 'PatientName',procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits),"
+ "procedurelog.ProcNum,procedurelog.ClinicNum "
+ "FROM patient "
+ "INNER JOIN procedurelog ON procedurelog.PatNum = patient.PatNum "
+ "AND procedurelog.ProcFee>0 "
+ "AND procedurelog.procstatus=" + (int)ProcStat.C + " "
+ "AND procedurelog.ProcDate BETWEEN "+ POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " "
+ "INNER JOIN ( "
+ "SELECT PatNum FROM patplan GROUP BY PatNum "
+ " )HasIns ON HasIns.PatNum = patient.PatNum ";
if (listClinicNums.Count > 0)
{
query += "AND procedurelog.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
query += "INNER JOIN procedurecode ON procedurecode.CodeNum = procedurelog.CodeNum ";
if (CultureInfo.CurrentCulture.Name.EndsWith("CA"))
{
query += "AND procedurecode.IsCanadianLab=0 "; //ignore Canadian labs
}
query += "LEFT JOIN claimproc ON claimproc.ProcNum = procedurelog.ProcNum "
+ "LEFT JOIN insplan ON insplan.PlanNum = claimproc.PlanNum ";
if (showProcsBeforeIns)
{
query += "WHERE ((claimproc.NoBillIns=0 "
+ "AND claimproc.Status=" + (int)ClaimProcStatus.Estimate + ") "
+ "OR claimproc.ClaimProcNum IS NULL) ";
}
else
{
query += "WHERE claimproc.NoBillIns=0 "
+ "AND claimproc.Status=" + (int)ClaimProcStatus.Estimate + " ";
}
if (!includeMedProcs)
{
query += "AND (insplan.IsMedical=0 ";
if (showProcsBeforeIns)
{
query += "OR insplan.PlanNum IS NULL ";
}
query += ") ";
}
query += "GROUP BY procedurelog.ProcNum "
+ "ORDER BY patient.LName,patient.FName,patient.PatNum,procedurelog.ProcDate";
return(Db.GetTable(query));
}
///<summary>Returns true if this definition is in use within the program. Consider enhancing this method if you add a definition category.
///Does not check patient billing type or provider specialty since those are handled in their S-class.</summary>
public static bool IsDefinitionInUse(Def def)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), def));
}
List <string> listStrCommands = new List <string>();
switch (def.Category)
{
case DefCat.AdjTypes:
if (new[] {
PrefName.BrokenAppointmentAdjustmentType,
PrefName.TreatPlanDiscountAdjustmentType,
PrefName.BillingChargeAdjustmentType,
PrefName.FinanceChargeAdjustmentType,
PrefName.SalesTaxAdjustmentType
}.Any(x => PrefC.GetLong(x) == def.DefNum))
{
return(true);
}
listStrCommands.Add("SELECT COUNT(*) FROM adjustment WHERE AdjType=" + POut.Long(def.DefNum));
break;
case DefCat.ApptConfirmed:
if (new[] {
PrefName.AppointmentTimeArrivedTrigger,
PrefName.AppointmentTimeSeatedTrigger,
PrefName.AppointmentTimeDismissedTrigger,
PrefName.WebSchedNewPatConfirmStatus,
PrefName.WebSchedRecallConfirmStatus,
}.Any(x => PrefC.GetLong(x) == def.DefNum))
{
return(true);
}
if (new[] { PrefName.ApptEConfirmStatusSent, PrefName.ApptEConfirmStatusAccepted, PrefName.ApptEConfirmStatusDeclined, PrefName.ApptEConfirmStatusSendFailed }
.Any(x => PrefC.GetLong(x) == def.DefNum))
{
return(true);
}
listStrCommands.Add("SELECT COUNT(*) FROM appointment WHERE Confirmed=" + POut.Long(def.DefNum));
break;
case DefCat.AutoNoteCats:
listStrCommands.Add("SELECT COUNT(*) FROM autonote WHERE Category=" + POut.Long(def.DefNum));
break;
case DefCat.BillingTypes:
if (new[] {
PrefName.PracticeDefaultBillType
}.Any(x => PrefC.GetLong(x) == def.DefNum))
{
return(true);
}
break;
case DefCat.ContactCategories:
listStrCommands.Add("SELECT COUNT(*) FROM contact WHERE Category=" + POut.Long(def.DefNum));
break;
case DefCat.Diagnosis:
listStrCommands.Add("SELECT COUNT(*) FROM procedurelog WHERE Dx=" + POut.Long(def.DefNum));
break;
case DefCat.ImageCats:
listStrCommands.Add("SELECT COUNT(*) FROM document WHERE DocCategory=" + POut.Long(def.DefNum));
listStrCommands.Add("SELECT COUNT(*) FROM sheetfielddef WHERE FieldType=" + POut.Int((int)SheetFieldType.PatImage) + " AND FieldName=" + POut.Long(def.DefNum));
break;
case DefCat.PaymentTypes:
if (def.DefNum.In(PrefC.GetLong(PrefName.RecurringChargesPayTypeCC), PrefC.GetLong(PrefName.AccountingCashPaymentType)))
{
return(true);
}
listStrCommands.Add("SELECT COUNT(*) FROM payment WHERE PayType=" + POut.Long(def.DefNum));
break;
case DefCat.PaySplitUnearnedType:
if (def.DefNum.In(PrefC.GetLong(PrefName.TpUnearnedType)))
{
return(true);
}
listStrCommands.Add("SELECT COUNT(*) FROM paysplit WHERE UnearnedType=" + POut.Long(def.DefNum));
break;
case DefCat.Prognosis:
listStrCommands.Add("SELECT COUNT(*) FROM procedurelog WHERE Prognosis=" + POut.Long(def.DefNum));
break;
case DefCat.RecallUnschedStatus:
if (def.DefNum.In(
PrefC.GetLong(PrefName.RecallStatusMailed),
PrefC.GetLong(PrefName.RecallStatusTexted),
PrefC.GetLong(PrefName.RecallStatusEmailed),
PrefC.GetLong(PrefName.RecallStatusEmailedTexted)))
{
return(true);
}
listStrCommands.Add("SELECT COUNT(*) FROM appointment WHERE UnschedStatus=" + POut.Long(def.DefNum));
listStrCommands.Add("SELECT COUNT(*) FROM recall WHERE RecallStatus=" + POut.Long(def.DefNum));
break;
case DefCat.TaskPriorities:
listStrCommands.Add("SELECT COUNT(*) FROM task WHERE PriorityDefNum=" + POut.Long(def.DefNum));
break;
case DefCat.TxPriorities:
listStrCommands.Add("SELECT COUNT(*) FROM procedurelog WHERE Priority=" + POut.Long(def.DefNum));
break;
case DefCat.CommLogTypes:
listStrCommands.Add("SELECT COUNT(*) FROM commlog WHERE CommType=" + POut.Long(def.DefNum));
break;
default:
break;
}
return(listStrCommands.Any(x => Db.GetCount(x) != "0"));
}
///<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.DatePay 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.DatePay<=" + 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);
}
}
///<summary>Will throw an error if not authorized and message not suppressed.</summary>
public static bool IsAuthorized(Permissions perm, DateTime date, bool suppressMessage, long userGroupNum)
{
//No need to check RemotingRole; no call to db.
if (!GroupPermissions.HasPermission(userGroupNum, perm))
{
if (!suppressMessage)
{
throw new Exception(Lans.g("Security", "Not authorized for") + "\r\n" + GroupPermissions.GetDesc(perm));
}
return(false);
}
if (perm == Permissions.AccountingCreate || perm == Permissions.AccountingEdit)
{
if (date <= PrefC.GetDate(PrefName.AccountingLockDate))
{
if (!suppressMessage)
{
throw new Exception(Lans.g("Security", "Locked by Administrator."));
}
return(false);
}
}
//Check the global security lock------------------------------------------------------------------------------------
//the list below is NOT the list of permissions that take dates. See GroupPermissions.PermTakesDates().
if (perm == Permissions.AdjustmentCreate ||
perm == Permissions.AdjustmentEdit ||
perm == Permissions.PaymentCreate ||
perm == Permissions.PaymentEdit ||
perm == Permissions.ProcComplCreate ||
perm == Permissions.ProcComplEdit
//|| perm==Permissions.ImageDelete
|| perm == Permissions.InsPayCreate ||
perm == Permissions.InsPayEdit ||
perm == Permissions.SheetEdit ||
perm == Permissions.CommlogEdit
)
{
//If the global lock is date-based:
if (date.Year > 1 && //if a valid date was passed in
date <= PrefC.GetDate(PrefName.SecurityLockDate)) //and that date is earlier than the lock
{
if (PrefC.GetBool(PrefName.SecurityLockIncludesAdmin) || //if admins are locked out too
!GroupPermissions.HasPermission(userGroupNum, Permissions.SecurityAdmin)) //or is not an admin
{
if (!suppressMessage)
{
throw new Exception(Lans.g("Security", "Locked by Administrator before ") + PrefC.GetDate(PrefName.SecurityLockDate).ToShortDateString());
}
return(false);
}
}
//If the global lock is days-based:
if (date.Year > 1 && //if a valid date was passed in
PrefC.GetInt(PrefName.SecurityLockDays) > 0 &&
date <= DateTime.Today.AddDays(-PrefC.GetInt(PrefName.SecurityLockDays))) //and that date is earlier than the lock
{
if (PrefC.GetBool(PrefName.SecurityLockIncludesAdmin) || //if admins are locked out too
!GroupPermissions.HasPermission(userGroupNum, Permissions.SecurityAdmin)) //or is not an admin
{
if (!suppressMessage)
{
throw new Exception(Lans.g("Security", "Locked by Administrator before ") + PrefC.GetInt(PrefName.SecurityLockDays).ToString() + " days.");
}
return(false);
}
}
}
//Check date/days limits on individual permission----------------------------------------------------------------
if (!GroupPermissions.PermTakesDates(perm))
{
return(true);
}
DateTime dateLimit = GetDateLimit(perm, userGroupNum);
if (date > dateLimit) //authorized
{
return(true);
}
//Prevents certain bugs when 1/1/1 dates are passed in and compared----------------------------------------------
//Handling of min dates. There might be others, but we have to handle them individually to avoid introduction of bugs.
if (perm == Permissions.ClaimSentEdit || //no date sent was entered before setting claim received
perm == Permissions.ProcComplEdit || //a completed procedure with a min date.
perm == Permissions.InsPayEdit || //a claim payment with no date.
perm == Permissions.TreatPlanEdit ||
perm == Permissions.AdjustmentEdit ||
perm == Permissions.CommlogEdit || //usually from a conversion
perm == Permissions.ProcDelete) //because older versions did not set the DateEntryC.
{
if (date.Year < 1880 && dateLimit.Year < 1880)
{
return(true);
}
}
if (!suppressMessage)
{
throw new Exception(Lans.g("Security", "Not authorized for") + "\r\n"
+ GroupPermissions.GetDesc(perm) + "\r\n" + Lans.g("Security", "Date limitation"));
}
return(false);
}
///<summary>Only called once from FormPayment when trying to change an amount or an account on a payment that's already linked to the Accounting section or when trying to create a new link. This automates updating the Accounting section. Do not surround with try-catch, because it was already validated in ValidateLinkedEntries above. Use -1 for newAcct to indicate no changed. The name is required to give descriptions to new entries.</summary>
public static void AlterLinkedEntries(double oldAmt, double newAmt, bool isNew, long payNum, long newAcct, DateTime payDate,
string patName)
{
//No need to check RemotingRole; no call to db.
if (!Accounts.PaymentsLinked())
{
return; //user has not even set up accounting links.
}
bool amtChanged = false;
if (oldAmt != newAmt)
{
amtChanged = true;
}
Transaction trans = Transactions.GetAttachedToPayment(payNum); //this gives us the oldAcctNum
double absNew = newAmt; //absolute value of the new amount
if (newAmt < 0)
{
absNew = -newAmt;
}
//if(trans==null && (newAcct==0 || newAcct==-1)) {//then this method will not even be called
if (trans == null) //no previous link, but user is trying to create one.
//this is the only case where a new trans is required.
{
trans = new Transaction();
trans.PayNum = payNum;
trans.UserNum = Security.CurUser.UserNum;
Transactions.Insert(trans); //sets entry date
//first the deposit entry
JournalEntry je = new JournalEntry();
je.AccountNum = newAcct; //DepositAccounts[comboDepositAccount.SelectedIndex];
je.CheckNumber = Lans.g("Payments", "DEP");
je.DateDisplayed = payDate; //it would be nice to add security here.
if (absNew == newAmt) //amount is positive
{
je.DebitAmt = newAmt;
}
else
{
je.CreditAmt = absNew;
}
je.Memo = Lans.g("Payments", "Payment -") + " " + patName;
je.Splits = Accounts.GetDescript(PrefC.GetLong(PrefName.AccountingCashIncomeAccount));
je.TransactionNum = trans.TransactionNum;
JournalEntries.Insert(je);
//then, the income entry
je = new JournalEntry();
je.AccountNum = PrefC.GetLong(PrefName.AccountingCashIncomeAccount);
//je.CheckNumber=;
je.DateDisplayed = payDate; //it would be nice to add security here.
if (absNew == newAmt) //amount is positive
{
je.CreditAmt = newAmt;
}
else
{
je.DebitAmt = absNew;
}
je.Memo = Lans.g("Payments", "Payment -") + " " + patName;
je.Splits = Accounts.GetDescript(newAcct);
je.TransactionNum = trans.TransactionNum;
JournalEntries.Insert(je);
return;
}
//at this point, we have established that there is a previous transaction.
List <JournalEntry> jeL = JournalEntries.GetForTrans(trans.TransactionNum);
long oldAcct = 0;
JournalEntry jeDebit = null;
JournalEntry jeCredit = null;
bool signChanged = false;
double absOld = oldAmt;//the absolute value of the old amount
if (oldAmt < 0)
{
absOld = -oldAmt;
}
if (oldAmt < 0 && newAmt > 0)
{
signChanged = true;
}
if (oldAmt > 0 && newAmt < 0)
{
signChanged = true;
}
for (int i = 0; i < 2; i++)
{
if (Accounts.GetAccount(jeL[i].AccountNum).AcctType == AccountType.Asset)
{
oldAcct = jeL[i].AccountNum;
}
if (jeL[i].DebitAmt == absOld)
{
jeDebit = jeL[i];
}
//old credit entry
if (jeL[i].CreditAmt == absOld)
{
jeCredit = jeL[i];
}
}
//Already validated that both je's are not null, and that oldAcct is not 0.
if (newAcct == 0) //detaching it from a linked transaction. We will delete the transaction
//we don't care about the amount
{
Transactions.Delete(trans); //we need to make sure this doesn't throw any exceptions by carefully checking all
//possibilities in the validation routine above.
return;
}
//Either the amount or the account changed on an existing linked transaction.
bool acctChanged = false;
if (newAcct != -1 && oldAcct != newAcct)
{
acctChanged = true; //changing linked acctNum
}
if (amtChanged)
{
if (signChanged)
{
jeDebit.DebitAmt = 0;
jeDebit.CreditAmt = absNew;
jeCredit.DebitAmt = absNew;
jeCredit.CreditAmt = 0;
}
else
{
jeDebit.DebitAmt = absNew;
jeCredit.CreditAmt = absNew;
}
}
if (acctChanged)
{
if (jeDebit.AccountNum == oldAcct)
{
jeDebit.AccountNum = newAcct;
}
if (jeCredit.AccountNum == oldAcct)
{
jeCredit.AccountNum = newAcct;
}
}
JournalEntries.Update(jeDebit);
JournalEntries.Update(jeCredit);
}
public static ListenerServiceType SetEConnectorOnOff(bool isListening)
{
return(WebSerializer.DeserializePrimitiveOrThrow <ListenerServiceType>(
GetWebServiceMainHQInstance().SetEConnectorType(WebSerializer.SerializePrimitive <string>(PrefC.GetString(PrefName.RegistrationKey)), isListening)));
}
public static List <List <int> > GetProdInc(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <List <int> > >(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
#if DEBUG
_elapsedTimeProdInc = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeProdInc = "Elapsed time for GetProdInc:\r\n";
stopWatch.Restart();
stopWatchTotal.Restart();
#endif
string command;
command = @"SELECT procedurelog.ProcDate,
SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(claimproc.WriteOff),0)
FROM procedurelog
LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum
AND claimproc.Status='7' /*only CapComplete writeoffs are subtracted here*/
WHERE procedurelog.ProcStatus = '2'
AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + @"
AND procedurelog.ProcDate <= " + POut.Date(dateTo) + @"
GROUP BY MONTH(procedurelog.ProcDate)" ;
DataTable tableProduction = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tableProduction: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = @"SELECT AdjDate,
SUM(AdjAmt)
FROM adjustment
WHERE AdjDate >= " + POut.Date(dateFrom) + @"
AND AdjDate <= " + POut.Date(dateTo) + @"
GROUP BY MONTH(AdjDate)" ;
DataTable tableAdj = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tableAdj: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
switch ((PPOWriteoffDateCalc)PrefC.GetInt(PrefName.ReportsPPOwriteoffDefaultToProcDate))
{
case PPOWriteoffDateCalc.InsPayDate:
command = "SELECT "
+ "claimproc.DateCP,"
+ "SUM(claimproc.WriteOff) "
+ "FROM claimproc "
+ "WHERE claimproc.DateCP >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.DateCP <= " + POut.Date(dateTo) + " "
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") " //Received or supplemental
+ "GROUP BY MONTH(claimproc.DateCP)";
break;
case PPOWriteoffDateCalc.ProcDate:
command = "SELECT "
+ "claimproc.ProcDate,"
+ "SUM(claimproc.WriteOff) "
+ "FROM claimproc "
+ "WHERE claimproc.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.ProcDate <= " + POut.Date(dateTo) + " "
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "
+ "GROUP BY MONTH(claimproc.ProcDate)";
break;
case PPOWriteoffDateCalc.ClaimPayDate: //Means preference is PPOWriteoffDateCalc.InsDate, or PPOWriteoffDateCalc.ClaimPayDate.
command = "SELECT "
+ "claimsnaptshot.DateTEntry,"
+ "SUM(claimsnapshot.WriteOff) "
+ "FROM claimproc "
+ "INNER JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum "
+ "AND claimsnapshot.DateTEntry >= " + POut.Date(dateFrom) + " "
+ "AND claimsnapshot.DateTEntry <= " + POut.Date(dateTo) + " "
+ "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") "
+ "GROUP BY MONTH(claimsnaptshot.DateTEntry)";
break;
}
DataTable tableWriteoff = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tableWriteoff: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "SELECT "
+ "paysplit.DatePay,"
+ "SUM(paysplit.SplitAmt) "
+ "FROM paysplit "
+ "WHERE paysplit.IsDiscount=0 "
+ "AND paysplit.DatePay >= " + POut.Date(dateFrom) + " "
+ "AND paysplit.DatePay <= " + POut.Date(dateTo) + " "
+ "GROUP BY MONTH(paysplit.DatePay)";
DataTable tablePay = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tablePay: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "SELECT claimpayment.CheckDate,SUM(claimproc.InsPayamt) "
+ "FROM claimpayment,claimproc WHERE "
+ "claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum "
+ "AND claimpayment.CheckDate >= " + POut.Date(dateFrom) + " "
+ "AND claimpayment.CheckDate <= " + POut.Date(dateTo) + " "
+ " GROUP BY claimpayment.CheckDate ORDER BY checkdate";
DataTable tableIns = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
stopWatchTotal.Stop();
_elapsedTimeProdInc += "tableIns: " + stopWatch.Elapsed.ToString() + "\r\n";
_elapsedTimeProdInc += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeProdInc);
}
#endif
//production--------------------------------------------------------------------
List <int> listInt;
listInt = new List <int>();
for (int i = 0; i < 12; i++)
{
decimal prod = 0;
decimal adjust = 0;
decimal inswriteoff = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tableProduction.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableProduction.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableProduction.Rows[j][0].ToString()).Month)
{
prod += PIn.Decimal(tableProduction.Rows[j][1].ToString());
}
}
for (int j = 0; j < tableAdj.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableAdj.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableAdj.Rows[j][0].ToString()).Month)
{
adjust += PIn.Decimal(tableAdj.Rows[j][1].ToString());
}
}
for (int j = 0; j < tableWriteoff.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableWriteoff.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableWriteoff.Rows[j][0].ToString()).Month)
{
inswriteoff += PIn.Decimal(tableWriteoff.Rows[j][1].ToString());
}
}
listInt.Add((int)(prod + adjust - inswriteoff));
}
List <List <int> > retVal = new List <List <int> >();
retVal.Add(listInt);
//income----------------------------------------------------------------------
listInt = new List <int>();
for (int i = 0; i < 12; i++)
{
decimal ptincome = 0;
decimal insincome = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tablePay.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tablePay.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tablePay.Rows[j][0].ToString()).Month)
{
ptincome += PIn.Decimal(tablePay.Rows[j][1].ToString());
}
}
for (int j = 0; j < tableIns.Rows.Count; j++) //
{
if (datePeriod.Year == PIn.Date(tableIns.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableIns.Rows[j][0].ToString()).Month)
{
insincome += PIn.Decimal(tableIns.Rows[j][1].ToString());
}
}
listInt.Add((int)(ptincome + insincome));
}
retVal.Add(listInt);
return(retVal);
}
///<summary>Returns a blank string if there were no errors while attempting to update internal carriers using iTrans n-cpl.json file..</summary>
public static string TryCarrierUpdate(bool isAutomatic = true, ItransImportFields fieldsToImport = ItransImportFields.None)
{
Clearinghouse clearinghouse = Clearinghouses.GetDefaultDental();
if (clearinghouse.CommBridge != EclaimsCommBridge.ITRANS ||
string.IsNullOrEmpty(clearinghouse.ResponsePath) ||
!File.Exists(ODFileUtils.CombinePaths(clearinghouse.ResponsePath, "ITRANS Claims Director.exe")) ||
(isAutomatic && PrefC.GetString(PrefName.WebServiceServerName).ToLower() != Dns.GetHostName().ToLower())) //Only server can run when isOnlyServer is true.
{
return(Lans.g("Clearinghouse", "ITRANS must be the default dental clearinghouse and your Report Path must be set first."));
}
Process process = new Process {
StartInfo = new ProcessStartInfo {
FileName = ODFileUtils.CombinePaths(clearinghouse.ResponsePath, "ITRANS Claims Director.exe"),
Arguments = " --getncpl"
}
};
process.Start();
process.WaitForExit();
string ncplFilePath = ODFileUtils.CombinePaths(clearinghouse.ResponsePath, "n-cpl.json");
string json = File.ReadAllText(ncplFilePath);//Read n-cpl.json
EtransMessageText msgTextPrev = EtransMessageTexts.GetMostRecentForType(EtransType.ItransNcpl);
if (msgTextPrev != null && msgTextPrev.MessageText == json)
{
return(Lans.g("Clearinghouse", "Carrier list has not changed since last checked.")); //json has not changed since we last checked, no need to update.
}
//Save json as new etrans entry.
Etrans etrans = Etranss.CreateEtrans(File.GetCreationTime(ncplFilePath), clearinghouse.HqClearinghouseNum, json, 0);
etrans.Etype = EtransType.ItransNcpl;
Etranss.Insert(etrans);
ItransNCpl iTransNCpl = null;
try {
iTransNCpl = JsonConvert.DeserializeObject <ItransNCpl>(json); //Deserialize n-cpl.json
}
catch (Exception ex) {
ex.DoNothing();
return(Lans.g("Clearinghouse", "Failed to import json."));
}
foreach (ItransNCpl.Carrier jsonCarrier in iTransNCpl.ListCarriers) //Update providers.
{
OpenDentBusiness.Carrier odCarrier = Carriers.GetByElectId(jsonCarrier.Bin); //Cached
if (odCarrier == null) //Carrier can not be matched to internal Carrier based on ElectID.
{
if (!fieldsToImport.HasFlag(ItransImportFields.AddMissing))
{
continue;
}
OpenDentBusiness.Carrier carrierNew = new OpenDentBusiness.Carrier();
carrierNew.ElectID = jsonCarrier.Bin;
carrierNew.IsCDA = true;
carrierNew.CarrierName = jsonCarrier.Name.En;
carrierNew.Phone = TelephoneNumbers.ReFormat(jsonCarrier.Telephone?.First().Value);
if (jsonCarrier.Address.Count() > 0)
{
Address add = jsonCarrier.Address.First();
carrierNew.Address = add.Street1;
carrierNew.Address2 = add.Street2;
carrierNew.City = add.City;
carrierNew.State = add.Province;
carrierNew.Zip = add.PostalCode;
}
carrierNew.CanadianSupportedTypes = GetSupportedTypes(jsonCarrier);
carrierNew.CarrierName = jsonCarrier.Name.En;
try {
Carriers.Insert(carrierNew);
}
catch (Exception ex) {
ex.DoNothing();
}
continue;
}
else if (!odCarrier.IsCDA)
{
continue;
}
OpenDentBusiness.Carrier odCarrierOld = odCarrier.Copy();
odCarrier.CanadianSupportedTypes = GetSupportedTypes(jsonCarrier);
odCarrier.CDAnetVersion = POut.Int(jsonCarrier.Versions.Max(x => PIn.Int(x)));
List <ItransImportFields> listFields = Enum.GetValues(typeof(ItransImportFields)).Cast <ItransImportFields>().ToList();
foreach (ItransImportFields field in listFields)
{
if (fieldsToImport == ItransImportFields.None)
{
break; //No point in looping.
}
if (field == ItransImportFields.None || !fieldsToImport.HasFlag(field))
{
continue;
}
switch (field)
{
case ItransImportFields.Phone:
if (jsonCarrier.Telephone.Count > 0)
{
odCarrier.Phone = TelephoneNumbers.ReFormat(jsonCarrier.Telephone.First().Value);
}
break;
case ItransImportFields.Address:
if (jsonCarrier.Address.Count() > 0)
{
Address add = jsonCarrier.Address.First();
odCarrier.Address = add.Street1;
odCarrier.Address2 = add.Street2;
odCarrier.City = add.City;
odCarrier.State = add.Province;
odCarrier.Zip = add.PostalCode;
}
break;
case ItransImportFields.Name:
odCarrier.CarrierName = jsonCarrier.Name.En;
break;
}
}
try {
long userNum = 0;
if (!isAutomatic)
{
userNum = Security.CurUser.UserNum;
}
Carriers.Update(odCarrier, odCarrierOld, userNum);
}
catch (Exception ex) {
ex.DoNothing();
}
}
return(""); //Blank string represents a completed update.
}
///<summary>If not using clinics then supply an empty list of clinicNums. listClinicNums must have at least one item if using clinics.
///The table returned has the following columns in this order:
///PatientName, ProcDate, Descript, ProcFee, ProcNum, ClinicNum, PatNum, IsInProcess</summary>
public static DataTable GetProcsNotBilled(List <long> listClinicNums, bool includeMedProcs, DateTime dateStart, DateTime dateEnd,
bool showProcsBeforeIns, bool hasMultiVisitProcs)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, includeMedProcs, dateStart, dateEnd, showProcsBeforeIns, hasMultiVisitProcs));
}
string query = "SELECT ";
if (PrefC.GetBool(PrefName.ReportsShowPatNum))
{
query += DbHelper.Concat("CAST(PatNum AS CHAR)", "'-'", "LName", "', '", "FName", "' '", "MiddleI");
}
else
{
query += DbHelper.Concat("LName", "', '", "FName", "' '", "MiddleI");
}
query += " AS 'PatientName',Stat,ProcDate,Descript,procFee,ProcNum,ClinicNum,PatNum "
+ "FROM (SELECT patient.LName,patient.FName,patient.MiddleI,"
+ "CASE WHEN procmultivisit.ProcMultiVisitNum IS NULL "
+ "THEN '" + Lans.g("enumProcStat", ProcStat.C.ToString()) + "' ELSE '" + Lans.g("enumProcStat", ProcStatExt.InProcess) + "' END Stat,"
+ "procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits) procFee,"
+ "procedurelog.ProcNum,procedurelog.ClinicNum,patient.PatNum,insplan.PlanNum,MAX(insplan.IsMedical) isMedical,"
+ "CASE WHEN MIN(insplan.IsMedical)=0 THEN 1 ELSE 0 END hasDental "
+ "FROM patient "
+ "INNER JOIN procedurelog ON procedurelog.PatNum = patient.PatNum "
+ "AND procedurelog.ProcFee>0 "
+ "AND procedurelog.procstatus=" + (int)ProcStat.C + " "
+ "AND procedurelog.ProcDate BETWEEN "+ POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " "
+ "INNER JOIN procedurecode ON procedurecode.CodeNum = procedurelog.CodeNum ";
if (CultureInfo.CurrentCulture.Name.EndsWith("CA"))
{
query += "AND procedurecode.IsCanadianLab=0 "; //ignore Canadian labs
}
query += "LEFT JOIN claimproc ON claimproc.ProcNum = procedurelog.ProcNum "
+ "LEFT JOIN insplan ON insplan.PlanNum = claimproc.PlanNum "
+ "LEFT JOIN procmultivisit ON procmultivisit.ProcNum=procedurelog.ProcNum AND procmultivisit.IsInProcess=1 "
+ "WHERE EXISTS(SELECT 1 FROM patplan WHERE patplan.PatNum=patient.PatNum) "
+ "AND ((claimproc.NoBillIns=0 AND claimproc.Status=" + (int)ClaimProcStatus.Estimate + ") ";
if (showProcsBeforeIns)
{
query += "OR claimproc.ClaimProcNum IS NULL ";
}
query += ") ";
if (!hasMultiVisitProcs)
{
query += "AND (procmultivisit.ProcMultiVisitNum IS NULL) ";
}
if (listClinicNums.Count > 0)
{
query += "AND procedurelog.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
query += "GROUP BY procedurelog.ProcNum "
+ ") procnotbilled "; //End of the main query which is treated like a sub query in order to process includeMedProcs and showProcsBeforeIns.
//Having the "AND insplan.IsMedical=0" check within the WHERE clause of the main query causes slowness for large databases.
//MySQL will freak out when looking for what index to use which causes full row scans to take place instead of simply filtering the results.
//This problem can be resolved by putting the insplan.IsMedical=0 check into the LEFT JOIN clause and performing a corresponding NULL check.
//However, the "OR insplan.PlanNum IS NULL" complicates the query enough to where it is easier to just put the old WHERE clause outside.
//This sub query trick improved the following report for a large office from ~55 seconds to ~5 seconds.
query += "WHERE (procnotbilled.hasDental=1 "; //Always include procedures when the patient has dental insurance.
if (includeMedProcs)
{
query += "OR procnotbilled.isMedical=1 ";
}
if (showProcsBeforeIns)
{
query += "OR procnotbilled.PlanNum IS NULL ";
}
query += ") ORDER BY LName,FName,PatNum,ProcDate";
return(Db.GetTable(query));
}
///<summary>Surround with try/catch. Also aggregates the content into the master page (unless specified to not).
///If isPreviewOnly, then the internal links will not be checked to see if the page exists, as it would make the refresh sluggish.
///And isPreviewOnly also changes the pointer so that the page looks non-clickable.
///For emails, this only gets called while in the email edit window. The returned string will be used to switch between plain and html text.
///</summary>
public static string TranslateToXhtml(string markupText, bool isPreviewOnly, bool hasWikiPageTitles = false, bool isEmail = false, bool canAggregate = true)
{
//No need to check RemotingRole; no call to db.
#region Basic Xml Validation
string s = markupText;
MatchCollection matches;
//"<",">", and "&"-----------------------------------------------------------------------------------------------------------
s = s.Replace("&", "&");
s = s.Replace("&<", "<"); //because "&" was changed to "&" in the line above.
s = s.Replace("&>", ">"); //because "&" was changed to "&" in the line above.
s = "<body>" + s + "</body>";
XmlDocument doc = new XmlDocument();
using (StringReader reader = new StringReader(s)) {
doc.Load(reader);
}
#endregion
#region regex replacements
if (isEmail)
{
s = TranslateEmailImages(s); //handle email images and wiki images separately.
}
else
{
//[[img:myimage.gif]]------------------------------------------------------------------------------------------------------------
matches = Regex.Matches(s, _odWikiImage);
foreach (Match match in matches)
{
string imgName = match.Value.Substring(match.Value.IndexOf(":") + 1).TrimEnd("]".ToCharArray());
string wikiPath = "";
try {
wikiPath = WikiPages.GetWikiPath();
}
catch (Exception ex) {
ex.DoNothing();
throw;
}
string fullPath = FileAtoZ.CombinePaths(wikiPath, POut.String(imgName));
if (CloudStorage.IsCloudStorage)
{
//WebBrowser needs to have a local file to open, so we download the images to temp files.
OpenDentalCloud.Core.TaskStateDownload state = CloudStorage.Download(Path.GetDirectoryName(fullPath), Path.GetFileName(fullPath));
string tempFile = PrefC.GetRandomTempFile(Path.GetExtension(fullPath));
File.WriteAllBytes(tempFile, state.FileContent);
fullPath = tempFile;
}
s = s.Replace(match.Value, "<img src=\"file:///" + fullPath.Replace("\\", "/") + "\"></img>");
}
//[[keywords: key1, key2, etc.]]------------------------------------------------------------------------------------------------
matches = Regex.Matches(s, _odWikiKeyword);
foreach (Match match in matches) //should be only one
{
s = s.Replace(match.Value, "<span class=\"keywords\">keywords:" + match.Value.Substring(11).TrimEnd("]".ToCharArray()) + "</span>");
}
//[[file:C:\eaula.txt]]------------------------------------------------------------------------------------------------
matches = Regex.Matches(s, _odWikiFile);
foreach (Match match in matches)
{
string fileName = match.Value.Replace("[[file:", "").TrimEnd(']');
s = s.Replace(match.Value, "<a href=\"wikifile:" + fileName + "\">file:" + fileName + "</a>");
}
//[[folder:\\serverfiles\storage\]]------------------------------------------------------------------------------------------------
matches = Regex.Matches(s, _odWikiFolder);
foreach (Match match in matches)
{
string folderName = match.Value.Replace("[[folder:", "").TrimEnd(']');
s = s.Replace(match.Value, "<a href=\"folder:" + folderName + "\">folder:" + folderName + "</a>");
}
//[[filecloud:AtoZ/SheetImages/happyclown.jpg]]------------------------------------------------------------------------------------------------
matches = Regex.Matches(s, _odWikiFilecloud);
foreach (Match match in matches)
{
string fileName = CloudStorage.PathTidy(match.Value.Replace("[[filecloud:", "").TrimEnd(']'));
s = s.Replace(match.Value, "<a href=\"wikifilecloud:" + fileName + "\">filecloud:" + fileName + "</a>");
}
//[[foldercloud:AtoZ/PenguinPictures/]]------------------------------------------------------------------------------------------------
matches = Regex.Matches(s, _odWikiFoldercloud);
foreach (Match match in matches)
{
string folderName = CloudStorage.PathTidy(match.Value.Replace("[[foldercloud:", "").TrimEnd(']'));
s = s.Replace(match.Value, "<a href=\"foldercloud:" + folderName + "\">foldercloud:" + folderName + "</a>");
}
}
//Color and text are for both wiki and email. It's important we do this before Internal Link or else the translation may not work.
//[[color:red|text]]----------------------------------------------------------------------------------------------------------------
matches = Regex.Matches(s, _odWikiColor); //.*? matches as few as possible.
foreach (Match match in matches)
{
//string[] paragraphs = match.Value.Split(new string[] { "\n" },StringSplitOptions.None);
string tempText = "<span style=\"color:";
string[] tokens = match.Value.Split('|');
if (tokens.Length < 2) //not enough tokens
{
continue;
}
if (tokens[0].Split(':').Length != 2) //Must have a color token and a color value seperated by a colon, no more no less.
{
continue;
}
for (int i = 0; i < tokens.Length; i++)
{
if (i == 0)
{
tempText += tokens[0].Split(':')[1] + ";\">"; //close <span> tag
continue;
}
tempText += (i > 1?"|":"") + tokens[i];
}
tempText = tempText.TrimEnd(']');
tempText += "</span>";
s = s.Replace(match.Value, tempText);
}
//[[font-family:courier|text]]----------------------------------------------------------------------------------------------------------------
matches = Regex.Matches(s, _odWikiFont); //.*? matches as few as possible.
foreach (Match match in matches)
{
//string[] paragraphs = match.Value.Split(new string[] { "\n" },StringSplitOptions.None);
string tempText = "<span style=\"font-family:";
string[] tokens = match.Value.Split('|');
if (tokens.Length < 2) //not enough tokens
{
continue;
}
if (tokens[0].Split(':').Length != 2) //Must have a color token and a color value seperated by a colon, no more no less.
{
continue;
}
for (int i = 0; i < tokens.Length; i++)
{
if (i == 0)
{
tempText += tokens[0].Split(':')[1] + ";\">"; //close <span> tag
continue;
}
tempText += (i > 1?"|":"") + tokens[i];
}
tempText = tempText.TrimEnd(']');
tempText += "</span>";
s = s.Replace(match.Value, tempText);
}
if (!isEmail)
{
//[[InternalLink]]--------------------------------------------------------------------------------------------------------------
matches = Regex.Matches(s, @"\[\[.+?\]\]");
List <string> pageNamesToCheck = new List <string>();
List <bool> pageNamesExist = new List <bool>();
string styleNotExists = "";
if (hasWikiPageTitles)
{
if (!isPreviewOnly)
{
foreach (Match match in matches)
{
//The '&' was replaced with '&' above, so we change it back before looking for a wiki page with that name.
pageNamesToCheck.Add(match.Value.Trim('[', ']').Replace("&", "&"));
}
if (pageNamesToCheck.Count > 0)
{
pageNamesExist = WikiPages.CheckPageNamesExist(pageNamesToCheck); //this gets a list of bools for all pagenames in one shot. One query.
}
}
foreach (Match match in matches)
{
styleNotExists = "";
if (!isPreviewOnly)
{
//The '&' was replaced with '&' above, so we change it back before looking for a wiki page with that name.
string pageName = match.Value.Trim('[', ']').Replace("&", "&");
int idx = pageNamesToCheck.IndexOf(pageName);
if (!pageNamesExist[idx])
{
styleNotExists = "class='PageNotExists' ";
}
}
s = s.Replace(match.Value, "<a " + styleNotExists + "href=\"" + "wiki:" + match.Value.Trim('[', ']') /*.Replace(" ","_")*/
+ "\">" + match.Value.Trim('[', ']') + "</a>");
}
}
else
{
List <long> listWikiPageNums = WikiPages.GetWikiPageNumsFromPageContent(s);
List <WikiPage> listWikiPages = WikiPages.GetWikiPages(listWikiPageNums);
int numInvalid = 1;
foreach (Match match in matches)
{
WikiPage wp = listWikiPages.FirstOrDefault(x => x.WikiPageNum == PIn.Long(match.Value.TrimStart('[').TrimEnd(']')));
string pageName;
if (wp != null)
{
pageName = wp.PageTitle;
}
else
{
pageName = "INVALID WIKIPAGE LINK " + numInvalid++;
}
if (!isPreviewOnly)
{
styleNotExists = "";
if (wp == null)
{
styleNotExists = "class='PageNotExists' ";
}
}
pageName = pageName.Replace("&", "&").Replace("&<", "<").Replace("&>", ">");
string replace = "<a " + styleNotExists + "href=\"" + "wiki:" + pageName /*.Replace(" ","_")*/ + "\">" + pageName + "</a>";
Regex regex = new Regex(Regex.Escape(match.Value));
//Replace the first instance of the match with the wiki page name (or unknown if not found).
s = regex.Replace(s, replace, 1);
}
}
}
//Unordered List----------------------------------------------------------------------------------------------------------------
//Instead of using a regex, this will hunt through the rows in sequence.
//later nesting by running ***, then **, then *
s = ProcessList(s, "*");
//numbered list---------------------------------------------------------------------------------------------------------------------
s = ProcessList(s, "#");
//table-------------------------------------------------------------------------------------------------------------------------
//{|
//!Width="100"|Column Heading 1!!Width="150"|Column Heading 2!!Width=""|Column Heading 3
//|-
//|Cell 1||Cell 2||Cell 3
//|-
//|Cell A||Cell B||Cell C
//|}
//There are many ways to parse this. Our strategy is to do it in a way that the generated xml is never invalid.
//As the user types, the above example will frequently be in a state of partial completeness, and the parsing should gracefully continue anyway.
//rigorous enforcement only happens when validating during a save, not here.
matches = Regex.Matches(s, _odWikiTable, RegexOptions.Singleline);
foreach (Match match in matches)
{
//If there isn't a new line before the start of the table markup or after the end, the match group value will be an empty string
//Tables must start with "'newline'{|" and end with "|}'newline'"
string tableStrOrig = match.Value;
StringBuilder strbTable = new StringBuilder();
string[] lines = tableStrOrig.Split(new string[] { "{|\n", "\n|-\n", "\n|}" }, StringSplitOptions.RemoveEmptyEntries);
strbTable.AppendLine("<table>");
List <string> colWidths = new List <string>();
for (int i = 0; i < lines.Length; i++)
{
if (lines[i].StartsWith("!")) //header
{
strbTable.AppendLine("<tr>");
lines[i] = lines[i].Substring(1); //strips off the leading !
string[] cells = lines[i].Split(new string[] { "!!" }, StringSplitOptions.None);
colWidths.Clear();
for (int c = 0; c < cells.Length; c++)
{
if (Regex.IsMatch(cells[c], @"(Width="")\d+""\|")) //e.g. Width="90"|
{
strbTable.Append("<th ");
string width = cells[c].Substring(7); //90"|Column Heading 1
width = width.Substring(0, width.IndexOf("\"")); //90
colWidths.Add(width);
strbTable.Append("Width=\"" + width + "\">");
strbTable.Append(ProcessParagraph(cells[c].Substring(cells[c].IndexOf("|") + 1), false)); //surround with p tags. Allow CR in header.
strbTable.AppendLine("</th>");
}
else
{
strbTable.Append("<th>");
strbTable.Append(ProcessParagraph(cells[c], false)); //surround with p tags. Allow CR in header.
strbTable.AppendLine("</th>");
}
}
strbTable.AppendLine("</tr>");
}
else if (lines[i].Trim() == "|-")
{
//totally ignore these rows
}
else //normal row
{
strbTable.AppendLine("<tr>");
lines[i] = lines[i].Substring(1); //strips off the leading |
string[] cells = lines[i].Split(new string[] { "||" }, StringSplitOptions.None);
for (int c = 0; c < cells.Length; c++)
{
strbTable.Append("<td Width=\"" + colWidths[c] + "\">");
strbTable.Append(ProcessParagraph(cells[c], false));
strbTable.AppendLine("</td>");
}
strbTable.AppendLine("</tr>");
}
}
strbTable.Append("</table>");
s = s.Replace(tableStrOrig, strbTable.ToString());
}
#endregion regex replacements
#region paragraph grouping
StringBuilder strbSnew = new StringBuilder();
//a paragraph is defined as all text between sibling tags, even if just a \n.
int iScanInParagraph = 0; //scan starting at the beginning of s. S gets chopped from the start each time we grab a paragraph or a sibiling element.
//The scanning position represents the verified paragraph content, and does not advance beyond that.
//move <body> tag over.
strbSnew.Append("<body>");
s = s.Substring(6);
bool startsWithCR = false; //todo: handle one leading CR if there is no text preceding it.
if (s.StartsWith("\n"))
{
startsWithCR = true;
}
string tagName;
Match tagCurMatch;
while (true) //loop to either construct a paragraph, or to immediately add the next tag to strbSnew.
{
iScanInParagraph = s.IndexOf("<", iScanInParagraph); //Advance the scanner to the start of the next tag
if (iScanInParagraph == -1) //there aren't any more tags, so current paragraph goes to end of string. This won't happen
{
throw new ApplicationException(Lans.g("WikiPages", "No tags found."));
//strbSnew.Append(ProcessParagraph(s));
}
if (s.Substring(iScanInParagraph).StartsWith("</body>"))
{
strbSnew.Append(ProcessParagraph(s.Substring(0, iScanInParagraph), startsWithCR));
//startsWithCR=false;
//strbSnew.Append("</body>");
s = "";
iScanInParagraph = 0;
break;
}
tagName = "";
tagCurMatch = Regex.Match(s.Substring(iScanInParagraph), "^<.*?>"); //regMatch);//.*? means any char, zero or more, as few as possible
if (tagCurMatch == null)
{
//shouldn't happen unless closing bracket is missing
throw new ApplicationException(Lans.g("WikiPages", "Unexpected tag:") + " " + s.Substring(iScanInParagraph));
}
if (tagCurMatch.Value.Trim('<', '>').EndsWith("/"))
{
//self terminating tags NOT are allowed
//this should catch all non-allowed self-terminating tags i.e. <br />, <inherits />, etc...
throw new ApplicationException(Lans.g("WikiPages", "All elements must have a beginning and ending tag. Unexpected tag:") + " " + s.Substring(iScanInParagraph));
}
//Nesting of identical tags causes problems:
//<h1><h1>some text</h1></h1>
//The first <h1> will match with the first </h1>.
//We don't have time to support this outlier, so we will catch it in the validator when they save.
//One possible strategy here might be:
//idxNestedDuplicate=s.IndexOf("<"+tagName+">");
//if(idxNestedDuplicate<s.IndexOf("</"+tagName+">"){
//
//}
//Another possible strategy might be to use regular expressions.
tagName = tagCurMatch.Value.Split(new string[] { "<", " ", ">" }, StringSplitOptions.RemoveEmptyEntries)[0]; //works with tags like <i>, <span ...>, and <img .../>
if (s.IndexOf("</" + tagName + ">") == -1) //this will happen if no ending tag.
{
throw new ApplicationException(Lans.g("WikiPages", "No ending tag:") + " " + s.Substring(iScanInParagraph));
}
switch (tagName)
{
case "a":
case "b":
case "div":
case "i":
case "span":
iScanInParagraph = s.IndexOf("</" + tagName + ">", iScanInParagraph) + 3 + tagName.Length;
continue; //continues scanning this paragraph.
case "h1":
case "h2":
case "h3":
case "ol":
case "ul":
case "table":
case "img": //can NOT be self-terminating
if (iScanInParagraph == 0) //s starts with a non-paragraph tag, so there is no partially assembled paragraph to process.
//do nothing
{
}
else //we are already part way into assembling a paragraph.
{
strbSnew.Append(ProcessParagraph(s.Substring(0, iScanInParagraph), startsWithCR));
startsWithCR = false; //subsequent paragraphs will not need this
s = s.Substring(iScanInParagraph); //chop off start of s
iScanInParagraph = 0;
}
//scan to the end of this element
int iScanSibling = s.IndexOf("</" + tagName + ">") + 3 + tagName.Length;
//tags without a closing tag were caught above.
//move the non-paragraph content over to s new.
strbSnew.Append(s.Substring(0, iScanSibling));
s = s.Substring(iScanSibling);
//scanning will start a totally new paragraph
break;
default:
if (isEmail)
{
iScanInParagraph = s.IndexOf("</" + tagName + ">", iScanInParagraph) + 3 + tagName.Length;
continue; //continues scanning this paragraph
}
throw new ApplicationException(Lans.g("WikiPages", "Unexpected tag:") + " " + s.Substring(iScanInParagraph));
}
}
strbSnew.Append("</body>");
#endregion
#region aggregation
doc = new XmlDocument();
using (StringReader reader = new StringReader(strbSnew.ToString())) {
doc.Load(reader);
}
StringBuilder strbOut = new StringBuilder();
XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
settings.IndentChars = "\t";
settings.OmitXmlDeclaration = true;
settings.NewLineChars = "\n";
using (XmlWriter writer = XmlWriter.Create(strbOut, settings)) {
doc.WriteTo(writer);
}
//spaces can't be handled prior to this point because crashes the xml parser.
strbOut.Replace(" ", " "); //handle extra spaces.
strbOut.Replace("<td></td>", "<td> </td>"); //force blank table cells to show not collapsed
strbOut.Replace("<th></th>", "<th> </th>"); //and blank table headers
strbOut.Replace("{{nbsp}}", " "); //couldn't add the earlier because
strbOut.Replace("<p></p>", "<p> </p>"); //probably redundant but harmless
//aggregate with master
if (isEmail)
{
if (canAggregate)
{
s = PrefC.GetString(PrefName.EmailMasterTemplate).Replace("@@@body@@@", strbOut.ToString());
return(s);
}
return(strbOut.ToString());
}
else
{
s = WikiPages.MasterPage.PageContent.Replace("@@@body@@@", strbOut.ToString());
}
#endregion aggregation
/*
* //js This code is buggy. It will need very detailed comments and careful review before/if we ever turn it back on.
* if(isPreviewOnly) {
* //do not change cursor from pointer to IBeam to Hand as you move the cursor around the preview page
* s=s.Replace("*{\n\t","*{\n\tcursor:default;\n\t");
* //do not underline links if you hover over them in the preview window
* s=s.Replace("a:hover{\n\ttext-decoration:underline;","a:hover{\n\t");
* }*/
return(s);
}
public static string Validate(Clearinghouse clearhouse, Carrier carrier, Provider billProv, Clinic clinic, InsPlan insPlan, Patient subscriber, InsSub insSub)
{
StringBuilder strb = new StringBuilder();
X12Validate.ISA(clearhouse, strb);
X12Validate.Carrier(carrier, strb);
if (carrier.ElectID.Length < 2)
{
if (strb.Length != 0)
{
strb.Append(",");
}
strb.Append("Electronic ID");
}
if (billProv.SSN.Length != 9)
{
if (strb.Length != 0)
{
strb.Append(",");
}
strb.Append("Prov TIN 9 digits");
}
X12Validate.BillProv(billProv, strb);
if (PrefC.GetBool(PrefName.UseBillingAddressOnClaims))
{
X12Validate.BillingAddress(strb);
}
else if (clinic == null)
{
X12Validate.PracticeAddress(strb);
}
else
{
X12Validate.Clinic(clinic, strb);
}
if (insSub.SubscriberID.Length < 2)
{
if (strb.Length != 0)
{
strb.Append(",");
}
strb.Append("SubscriberID");
}
if (subscriber.Birthdate.Year < 1880)
{
if (strb.Length != 0)
{
strb.Append(",");
}
strb.Append("Subscriber Birthdate");
}
if (insPlan.GroupNum == "")
{
if (strb.Length != 0)
{
strb.Append(",");
}
strb.Append("Group Number");
}
return(strb.ToString());
}
/*
* public static Document Import(Bitmap image,long docCategory,Patient pat) {
* string patFolder="";
* if(PrefC.UsingAtoZfolder) {
* patFolder=GetPatientFolder(pat,GetPreferredAtoZpath());
* }
* Document doc=new Document();
* doc.FileName=".jpg";
* doc.DateCreated=DateTime.Today;
* doc.DocCategory=docCategory;
* doc.PatNum=pat.PatNum;
* doc.ImgType=ImageType.Photo;
* //doc.RawBase64 handled further down.
* //doc.Thumbnail="";//no thumbnail yet
* Documents.Insert(doc,pat);//this assigns a filename and saves to db
* doc=Documents.GetByNum(doc.DocNum);
* try {
* SaveDocument(doc,image,ImageFormat.Jpeg,patFolder);
* if(PrefC.UsingAtoZfolder) {
* Documents.Update(doc);
* }
* }
* catch {
* Documents.Delete(doc);
* throw;
* }
* return doc;
* }*/
/// <summary>Saves to either AtoZ folder or to db. Saves image as a jpg. Compression will differ depending on imageType.</summary>
public static Document Import(Bitmap image, long docCategory, ImageType imageType, Patient pat)
{
string patFolder = "";
if (PrefC.UsingAtoZfolder)
{
patFolder = GetPatientFolder(pat, GetPreferredAtoZpath());
}
Document doc = new Document();
doc.ImgType = imageType;
doc.FileName = ".jpg";
doc.DateCreated = DateTime.Today;
doc.PatNum = pat.PatNum;
doc.DocCategory = docCategory;
Documents.Insert(doc, pat); //creates filename and saves to db
doc = Documents.GetByNum(doc.DocNum);
long qualityL = 0;
if (imageType == ImageType.Radiograph)
{
qualityL = 100;
}
else if (imageType == ImageType.Photo)
{
qualityL = 100;
}
else //Assume document
//Possible values 0-100?
{
qualityL = PrefC.GetLong(PrefName.ScannerCompression);
}
ImageCodecInfo myImageCodecInfo;
ImageCodecInfo[] encoders;
encoders = ImageCodecInfo.GetImageEncoders();
myImageCodecInfo = null;
for (int j = 0; j < encoders.Length; j++)
{
if (encoders[j].MimeType == "image/jpeg")
{
myImageCodecInfo = encoders[j];
}
}
EncoderParameters myEncoderParameters = new EncoderParameters(1);
EncoderParameter myEncoderParameter = new EncoderParameter(System.Drawing.Imaging.Encoder.Quality, qualityL);
myEncoderParameters.Param[0] = myEncoderParameter;
//AutoCrop()?
try {
SaveDocument(doc, image, myImageCodecInfo, myEncoderParameters, patFolder);
if (!PrefC.UsingAtoZfolder)
{
Documents.Update(doc); //because SaveDocument stuck the image in doc.RawBase64.
//no thumbnail yet
}
}
catch {
Documents.Delete(doc);
throw;
}
return(doc);
}
///<summary>Gets the DataTable to display for treatment finder report</summary>
///<param name="listProviders">Include '0' in the list to get for all providers.</param>
///<param name="listBilling">Include '0' in the list to get for all billing types.</param>
///<param name="listClinicNums">Pass in an empty list to get for all clinics.</param>
public static DataTable GetTreatmentFinderList(bool noIns, bool patsWithAppts, int monthStart, DateTime dateSince, double aboveAmount,
List <long> listProviders, List <long> listBilling, string code1, string code2, List <long> listClinicNums, bool isProcsGeneral)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), noIns, patsWithAppts, monthStart, dateSince, aboveAmount, listProviders, listBilling, code1, code2,
listClinicNums, isProcsGeneral));
}
#if DEBUG
Stopwatch sw = Stopwatch.StartNew();
#endif
DataTable table = new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("PatNum");
table.Columns.Add("LName");
table.Columns.Add("FName");
table.Columns.Add("contactMethod");
table.Columns.Add("address");
table.Columns.Add("City");
table.Columns.Add("State");
table.Columns.Add("Zip");
table.Columns.Add("annualMaxInd");
table.Columns.Add("annualMaxFam");
table.Columns.Add("amountUsedInd");
table.Columns.Add("amountUsedFam");
table.Columns.Add("amountPendingInd");
table.Columns.Add("amountPendingFam");
table.Columns.Add("amountRemainingInd");
table.Columns.Add("amountRemainingFam");
table.Columns.Add("treatmentPlan");
table.Columns.Add("carrierName");
table.Columns.Add("clinicAbbr");
List <DataRow> rows = new List <DataRow>();
string command = "";
string joinAnnualMax = "";
string joinCoverageInfo = "";
string joinIndInfo = "";
string joinFamInfo = "";
string subSelectPlanned = "";
string cmdFutureApt = @" AND patient.PatNum NOT IN (
SELECT PatNum FROM appointment WHERE AptStatus=" + POut.Int((int)ApptStatus.Scheduled) + @"
AND AptDateTime>=" + DbHelper.Curdate() + ")";
DateTime renewDate = BenefitLogic.ComputeRenewDate(DateTime.Now, monthStart);
List <long> listPatNums = new List <long>();
if ((!listProviders.Contains(0) || !listBilling.Contains(0) || listClinicNums.Count > 0))
{
string cmdPatients = "SELECT PatNum from patient ";
string patWhere = "";
if (!listProviders.Contains(0))
{
patWhere += " AND patient.PriProv IN (" + string.Join(",", listProviders) + ") ";
}
if (!listBilling.Contains(0))
{
patWhere += " AND patient.BillingType IN (" + string.Join(",", listBilling) + ") ";
}
if (listClinicNums.Count > 0)
{
patWhere += " AND patient.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (!patsWithAppts)
{
patWhere += cmdFutureApt;
}
cmdPatients += "WHERE TRUE " + patWhere;
listPatNums = Db.GetListLong(cmdPatients);
if (listPatNums.Count == 0)
{
return(table);
}
}
joinCoverageInfo = @"
SELECT patplan.PatPlanNum,claimproc.InsSubNum,
SUM(CASE WHEN claimproc.Status=" + POut.Int((int)ClaimProcStatus.NotReceived) + @" AND claimproc.InsPayAmt=0
THEN claimproc.InsPayEst ELSE 0 END) AmtPending,
SUM(CASE WHEN claimproc.Status IN (" + POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Adjustment) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + @"
) THEN claimproc.InsPayAmt ELSE 0 END) AmtUsed
FROM claimproc
INNER JOIN patient ON patient.PatNum=claimproc.PatNum
LEFT JOIN patplan ON patplan.PatNum=claimproc.PatNum
AND patplan.InsSubNum=claimproc.InsSubNum
LEFT JOIN procedurelog pl ON pl.ProcNum=claimproc.ProcNum
LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum " ;
if (!isProcsGeneral)
{
joinCoverageInfo += @"
LEFT JOIN (
SELECT isub.InsSubNum,
COALESCE(cp.FromCode,pc.ProcCode) AS FromCode,
COALESCE(cp.ToCode,pc.ProcCode) AS ToCode
FROM inssub isub
INNER JOIN benefit b ON b.PlanNum=isub.PlanNum
AND b.BenefitType=" + (int)InsBenefitType.Limitations + @"
AND b.QuantityQualifier=" + (int)BenefitQuantity.None + @"
AND b.TimePeriod IN (" + (int)BenefitTimePeriod.ServiceYear + "," + (int)BenefitTimePeriod.CalendarYear + @")
LEFT JOIN covcat cc ON cc.CovCatNum=b.CovCatNum
LEFT JOIN covspan cp ON cp.CovCatNum=cc.CovCatNum
LEFT JOIN procedurecode pc ON pc.CodeNum=b.CodeNum
WHERE (cc.CovCatNum IS NOT NULL OR b.CodeNum!=0)
)ProcCheck ON ProcCheck.InsSubNum=claimproc.InsSubNum
AND pc.ProcCode BETWEEN ProcCheck.FromCode AND ProcCheck.ToCode " ;
}
joinCoverageInfo += "WHERE claimproc.Status IN (" + (int)ClaimProcStatus.NotReceived + ", " + (int)ClaimProcStatus.Received
+ ", " + (int)ClaimProcStatus.Adjustment + ", " + (int)ClaimProcStatus.Supplemental + ") ";
if (!isProcsGeneral)
{
joinCoverageInfo += "AND ProcCheck.InsSubNum IS NULL ";
}
joinCoverageInfo += "AND claimproc.ProcDate BETWEEN " + POut.Date(renewDate) + @" AND " + POut.Date(renewDate.AddYears(1)) + @" ";
if (listPatNums.Count > 0)
{
joinCoverageInfo += @"AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
joinCoverageInfo += cmdFutureApt;
}
joinIndInfo = joinCoverageInfo + " GROUP BY patplan.PatPlanNum ";
joinFamInfo = joinCoverageInfo + " GROUP BY claimproc.InsSubNum ";
subSelectPlanned = @"
(SELECT COALESCE(SUM(ProcFee),0) AmtPlanned
FROM procedurelog " ;
if (code1 != "")
{
subSelectPlanned += "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum ";
}
subSelectPlanned += "WHERE ProcStatus=" + (int)ProcStat.TP + " ";
if (code1 != "")
{
subSelectPlanned += "AND procedurecode.ProcCode>='" + POut.String(code1) + "' "
+ " AND procedurecode.ProcCode<='" + POut.String(code2) + "' ";
}
if (dateSince.Year > 1880)
{
subSelectPlanned += "AND procedurelog.DateTP>=" + POut.DateT(dateSince) + " ";
}
subSelectPlanned += "AND PatNum=patient.PatNum ";
subSelectPlanned += "GROUP BY PatNum) ";
joinAnnualMax = @"
SELECT insplan.PlanNum, MAX(CASE WHEN CoverageLevel!=" + POut.Int((int)BenefitCoverageLevel.Family) + @"
THEN MonetaryAmt ELSE -1 END) AnnualMaxInd/*for oracle in case there's more than one*/,
MAX(CASE WHEN CoverageLevel=" + POut.Int((int)BenefitCoverageLevel.Family) + @"
THEN MonetaryAmt ELSE -1 END) AnnualMaxFam/*for oracle in case there's more than one*/
FROM benefit
INNER JOIN insplan ON insplan.PlanNum=benefit.PlanNum
INNER JOIN inssub ON inssub.PlanNum=benefit.PlanNum
INNER JOIN patplan ON patplan.InsSubNum=inssub.InsSubNum
INNER JOIN patient ON patient.PatNum=patplan.PatNum
LEFT JOIN covcat ON benefit.CovCatNum=covcat.CovCatNum
WHERE (covcat.EbenefitCat=" + (int)EbenefitCategory.General + @" OR ISNULL(covcat.EbenefitCat))
AND benefit.BenefitType=" + (int)InsBenefitType.Limitations + @"
AND benefit.MonetaryAmt > 0
AND benefit.QuantityQualifier=" + (int)BenefitQuantity.None + " ";
if (listPatNums.Count > 0)
{
joinAnnualMax += @"AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
joinAnnualMax += cmdFutureApt;
}
joinAnnualMax += @"GROUP BY insplan.PlanNum";
command = @"SELECT patient.PatNum, patient.LName, patient.FName,
patient.Email, patient.HmPhone, patient.PreferRecallMethod,
patient.WirelessPhone, patient.WkPhone, patient.Address,
patient.Address2, patient.City, patient.State, patient.Zip,
patient.PriProv, patient.BillingType,
COALESCE(annualMax.AnnualMaxInd,0) ""AnnualMaxInd"",
COALESCE(annualMax.AnnualMaxFam,0) ""AnnualMaxFam"",
IndividualInfo.AmtUsed ""AmountUsedInd"",
FamilyInfo.AmtUsed ""AmountUsedFam"",
IndividualInfo.AmtPending ""AmountPendingInd"",
FamilyInfo.AmtPending ""AmountPendingFam"",
COALESCE(annualMax.AnnualMaxInd,0)-COALESCE(IndividualInfo.AmtUsed,0)-COALESCE(IndividualInfo.AmtPending,0) AS ""$AmtRemainingInd"",
COALESCE(annualMax.AnnualMaxFam,0)-COALESCE(FamilyInfo.AmtUsed,0)-COALESCE(FamilyInfo.AmtPending,0) AS ""$AmtRemainingFam""," +
subSelectPlanned + @"""$TreatmentPlan"", carrier.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 ("
+ joinIndInfo
+ @")IndividualInfo ON IndividualInfo.PatPlanNum=patplan.PatPlanNum
LEFT JOIN ("
+ joinFamInfo
+ @")FamilyInfo ON FamilyInfo.InsSubNum=inssub.InsSubNum
LEFT JOIN ("
+ joinAnnualMax
+ @") annualMax ON annualMax.PlanNum=inssub.PlanNum
AND (annualMax.AnnualMaxInd>0 OR annualMax.AnnualMaxFam>0)/*may not be necessary*/
LEFT JOIN clinic ON clinic.ClinicNum=patient.ClinicNum
WHERE TRUE
AND patient.PatStatus=" + POut.Int((int)PatientStatus.Patient) + " ";
if (!noIns) //if we don't want patients without insurance
{
command += " AND patplan.Ordinal=1 AND insplan.MonthRenew=" + POut.Int(monthStart) + " ";
}
if (aboveAmount > 0)
{
command += " AND (annualMax.PlanNum IS NULL OR ((annualMax.AnnualMaxInd=-1 OR annualMax.AnnualMaxInd-COALESCE(IndividualInfo.AmtUsed,0) > "
+ POut.Double(aboveAmount) + @")
AND (annualMax.AnnualMaxFam=-1 OR annualMax.AnnualMaxFam-COALESCE(FamilyInfo.AmtUsed,0) > " + POut.Double(aboveAmount) + "))) ";
}
if (listPatNums.Count > 0)
{
command += " AND patient.PatNum IN (" + string.Join(",", listPatNums) + ") ";
}
else if (!patsWithAppts)
{
command += cmdFutureApt;
}
command += @"HAVING $TreatmentPlan > 0 ";
command += @"ORDER BY $TreatmentPlan DESC";
DataTable rawtable = Db.GetTable(command);
#if DEBUG
sw.Stop();
Console.WriteLine("Finishing retreiving query: {0}", sw.ElapsedMilliseconds);
sw = Stopwatch.StartNew();
#endif
ContactMethod contmeth;
for (int i = 0; i < rawtable.Rows.Count; i++)
{
row = table.NewRow();
row["PatNum"] = PIn.Long(rawtable.Rows[i]["PatNum"].ToString());
row["LName"] = rawtable.Rows[i]["LName"].ToString();
row["FName"] = rawtable.Rows[i]["FName"].ToString();
contmeth = (ContactMethod)PIn.Long(rawtable.Rows[i]["PreferRecallMethod"].ToString());
if (contmeth == ContactMethod.None)
{
if (PrefC.GetBool(PrefName.RecallUseEmailIfHasEmailAddress)) //if user only wants to use email if contact method is email
{
if (rawtable.Rows[i]["Email"].ToString() != "")
{
row["contactMethod"] = rawtable.Rows[i]["Email"].ToString();
}
else
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
}
else
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
}
else if (contmeth == ContactMethod.HmPhone)
{
row["contactMethod"] = Lans.g("FormRecallList", "Hm:") + rawtable.Rows[i]["HmPhone"].ToString();
}
else if (contmeth == ContactMethod.WkPhone)
{
row["contactMethod"] = Lans.g("FormRecallList", "Wk:") + rawtable.Rows[i]["WkPhone"].ToString();
}
else if (contmeth == ContactMethod.WirelessPh)
{
row["contactMethod"] = Lans.g("FormRecallList", "Cell:") + rawtable.Rows[i]["WirelessPhone"].ToString();
}
else if (contmeth == ContactMethod.Email)
{
row["contactMethod"] = rawtable.Rows[i]["Email"].ToString();
}
else if (contmeth == ContactMethod.Mail)
{
row["contactMethod"] = Lans.g("FormRecallList", "Mail");
}
else if (contmeth == ContactMethod.DoNotCall || contmeth == ContactMethod.SeeNotes)
{
row["contactMethod"] = Lans.g("enumContactMethod", contmeth.ToString());
}
row["address"] = rawtable.Rows[i]["Address"].ToString();
if (rawtable.Rows[i]["Address2"].ToString() != "")
{
row["address"] += "\r\n" + rawtable.Rows[i]["Address2"].ToString();
}
row["City"] = rawtable.Rows[i]["City"].ToString();
row["State"] = rawtable.Rows[i]["State"].ToString();
row["Zip"] = rawtable.Rows[i]["Zip"].ToString();
row["annualMaxInd"] = (PIn.Double(rawtable.Rows[i]["AnnualMaxInd"].ToString())).ToString("N");
row["annualMaxFam"] = (PIn.Double(rawtable.Rows[i]["AnnualMaxFam"].ToString())).ToString("N");
row["amountUsedInd"] = (PIn.Double(rawtable.Rows[i]["AmountUsedInd"].ToString())).ToString("N");
row["amountUsedFam"] = (PIn.Double(rawtable.Rows[i]["AmountUsedFam"].ToString())).ToString("N");
row["amountPendingInd"] = (PIn.Double(rawtable.Rows[i]["AmountPendingInd"].ToString())).ToString("N");
row["amountPendingFam"] = (PIn.Double(rawtable.Rows[i]["AmountPendingFam"].ToString())).ToString("N");
row["amountRemainingInd"] = (PIn.Double(rawtable.Rows[i]["$AmtRemainingInd"].ToString())).ToString("N");
row["amountRemainingFam"] = (PIn.Double(rawtable.Rows[i]["$AmtRemainingFam"].ToString())).ToString("N");
row["treatmentPlan"] = (PIn.Double(rawtable.Rows[i]["$TreatmentPlan"].ToString())).ToString("N");
row["carrierName"] = rawtable.Rows[i]["CarrierName"].ToString();
row["clinicAbbr"] = rawtable.Rows[i]["clinicAbbr"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
#if DEBUG
sw.Stop();
Console.WriteLine("Finished Filling query result: {0}", sw.ElapsedMilliseconds);
#endif
return(table);
}
///<summary>Builds WHERE clauses appropriate to the type of GlobalFilterType. Returns empty string if not filtering. Pass filterClinicFkey=0
///and filterRegionFkey=0 to intentionally bypass filtering.</summary>
public static string BuildFilterWhereClause(long currentUserNum, long filterClinicFkey, long filterRegionFkey)
{
string command = string.Empty;
//Only add WHERE clauses if filtering. Filtering will never happen if clinics are turned off, because regions link via clinics.
if ((GlobalTaskFilterType)PrefC.GetInt(PrefName.TasksGlobalFilterType) == GlobalTaskFilterType.Disabled ||
(filterClinicFkey == 0 && filterRegionFkey == 0) || !PrefC.HasClinicsEnabled)
{
return(command);
}
List <Clinic> listUnrestrictedClinics = Clinics.GetAllForUserod(Userods.GetUser(currentUserNum));
List <long> listClinicNums = new List <long>()
{
0
}; //All users can see Tasks associated to HQ clinic or "0" region.
List <long> listClinicNumsInRegion = new List <long>()
{
0
}; //All users can see Tasks associated to HQ clinic or "0" region.
List <long> listUnrestrictedClinicNums = listUnrestrictedClinics.Select(x => x.ClinicNum).ToList(); //User can view these clinicnums.
List <long> listUnrestrictedClinicNumsInRegion = listUnrestrictedClinics.FindAll(x => x.Region == filterRegionFkey).Select(x => x.ClinicNum).ToList();
if (filterClinicFkey.In(listUnrestrictedClinicNums)) //Make sure user is not restricted for this clinic.
{
listClinicNums.Add(filterClinicFkey);
}
listClinicNumsInRegion.AddRange(listUnrestrictedClinicNumsInRegion);
string strClinicFilterNums = string.Join(",", listClinicNums.Select(x => POut.Long(x)));
string strRegionFilterNums = string.Join(",", listClinicNumsInRegion.Select(x => POut.Long(x)));
//Clause for TaskLists that have Default filter.
string cmdFilterTaskListByDefault = "(tasklistfortask.GlobalTaskFilterType=" + POut.Long((long)GlobalTaskFilterType.Default)
+ GetDefaultFilterTypeString((GlobalTaskFilterType)PrefC.GetInt(PrefName.TasksGlobalFilterType), strClinicFilterNums, strRegionFilterNums) + ") ";
//Clause for TaskLists that have None filter.
string cmdFilterTaskListByNone = "(tasklistfortask.GlobalTaskFilterType=" + POut.Long((long)GlobalTaskFilterType.None) + ")";
//Clause for TaskLists that have Clinic filter.
string cmdFilterTaskListByClinic = "(tasklistfortask.GlobalTaskFilterType=" + POut.Long((long)GlobalTaskFilterType.Clinic)
+ " AND (patient.ClinicNum IN (" + strClinicFilterNums + ") OR appointment.ClinicNum IN (" + strClinicFilterNums + "))) ";
//Clause for TaskLists that have Region filter.
string cmdFilterTaskListByRegion = "(tasklistfortask.GlobalTaskFilterType=" + POut.Long((long)GlobalTaskFilterType.Region)
+ " AND (patient.ClinicNum IN (" + strRegionFilterNums + ") OR appointment.ClinicNum IN (" + strRegionFilterNums + "))) ";
//Clause for Tasks that are not connected to a patient or clinic.
string cmdTaskClinicIsNull = "((patient.ClinicNum IS NULL) AND (appointment.ClinicNum IS NULL))";
command = " AND (" + cmdFilterTaskListByDefault + " OR " + cmdFilterTaskListByNone + " OR " + cmdFilterTaskListByClinic + " OR "
+ cmdFilterTaskListByRegion + "OR " + cmdTaskClinicIsNull + ") ";
return(command);
}
public static string GetToothLabelGraphic(string tooth_id)
{
return(GetToothLabelGraphic(tooth_id, (ToothNumberingNomenclature)PrefC.GetInt(PrefName.UseInternationalToothNumbers)));
}
public static DataTable GetOverPaidProcs(long patNum, List <long> listProvNums, List <long> listClinics, DateTime dateStart, DateTime dateEnd)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum, listProvNums, listClinics, dateStart, dateEnd));
}
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
#region Completed Procs
string command = "SELECT ";
if (PrefC.GetBool(PrefName.ReportsShowPatNum))
{
command += DbHelper.Concat("CAST(patient.PatNum AS CHAR)", "'-'", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
command += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
command += @" AS 'patientName',
procedurelog.ProcDate,
procedurecode.ProcCode,
procedurelog.ToothNum,
provider.Abbr,
(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) AS fee,
patient.PatNum,
procedurelog.ProcNum
FROM procedurelog
INNER JOIN patient ON patient.PatNum=procedurelog.PatNum
INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum
INNER JOIN provider ON provider.ProvNum=procedurelog.ProvNum
WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " AND "
+ DbHelper.BetweenDates("procedurelog.ProcDate", dateStart, dateEnd) + " "
+ "AND procedurelog.ProcFee>0 ";
if (listProvNums != null && listProvNums.Count > 0)
{
command += "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
if (listClinics != null && listClinics.Count > 0)
{
command += "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinics.Select(x => POut.Long(x))) + ") ";
}
if (patNum > 0)
{
command += "AND procedurelog.PatNum=" + POut.Long(patNum) + " ";
}
command += "ORDER BY procedurelog.ProcDate,patientName,procedurecode.ProcCode,provider.Abbr";
DataTable rawCompletedProcTable = Db.GetTable(command);
Dictionary <long, DataRow> dictCompletedProcRows = rawCompletedProcTable.Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
DataTable table = new DataTable();
if (dictCompletedProcRows.Count == 0)
{
return(table);
}
#region ClaimProcs
List <long> listPatNums = rawCompletedProcTable.Select().Select(x => PIn.Long(x["PatNum"].ToString())).Distinct().ToList();
command = @"SELECT MIN(claimproc.ProcNum) ProcNum,MIN(claimproc.PatNum) PatNum,MIN(claimproc.ProcDate) ProcDate,SUM(claimproc.InsPayAmt) insPayAmt,
SUM(claimproc.Writeoff) writeoff
FROM claimproc
WHERE claimproc.Status NOT IN(" + string.Join(",", new List <int> {
(int)ClaimProcStatus.Preauth,
(int)ClaimProcStatus.CapEstimate, (int)ClaimProcStatus.CapComplete, (int)ClaimProcStatus.Estimate, (int)ClaimProcStatus.InsHist
}
.Select(x => POut.Int(x))) + ") "
+ "AND " + DbHelper.BetweenDates("claimproc.ProcDate", dateStart, dateEnd) + " "
+ "AND claimproc.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ") "
+ @"GROUP BY claimproc.ProcNum
HAVING SUM(claimproc.InsPayAmt+claimproc.Writeoff)>0
ORDER BY NULL" ;
Dictionary <long, DataRow> dictClaimProcRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
#region Patient Payments
command = @"SELECT paysplit.ProcNum,SUM(paysplit.SplitAmt) ptAmt
FROM paysplit
WHERE paysplit.ProcNum>0
AND paysplit.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + $@") ";
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += @"
GROUP BY paysplit.ProcNum
ORDER BY NULL" ;
Dictionary <long, DataRow> dictPatPayRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
#region Adjustments
command = @"SELECT adjustment.ProcNum,SUM(adjustment.AdjAmt) AdjAmt
FROM adjustment
WHERE adjustment.ProcNum>0
AND adjustment.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + @")
GROUP BY adjustment.ProcNum
ORDER BY NULL" ;
Dictionary <long, DataRow> dictAdjRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("patientName");
table.Columns.Add("ProcDate", typeof(DateTime));
table.Columns.Add("ProcCode");
table.Columns.Add("ToothNum");
table.Columns.Add("Abbr");
table.Columns.Add("fee");
table.Columns.Add("insPaid");
table.Columns.Add("wo");
table.Columns.Add("ptPaid");
table.Columns.Add("adjAmt");
table.Columns.Add("overPay");
table.Columns.Add("PatNum");
DataRow row;
foreach (KeyValuePair <long, DataRow> kvp in dictCompletedProcRows)
{
long procNum = kvp.Key;
decimal procFeeAmt = PIn.Decimal(kvp.Value["fee"].ToString());
decimal insPaidAmt = 0;
decimal woAmt = 0;
decimal ptPaidAmt = 0;
decimal adjAmt = 0;
if (dictClaimProcRows.ContainsKey(procNum))
{
insPaidAmt = PIn.Decimal(dictClaimProcRows[procNum]["insPayAmt"].ToString());
woAmt = PIn.Decimal(dictClaimProcRows[procNum]["writeoff"].ToString());
}
if (dictPatPayRows.ContainsKey(procNum))
{
ptPaidAmt = PIn.Decimal(dictPatPayRows[procNum]["ptAmt"].ToString());
}
if (dictAdjRows.ContainsKey(procNum))
{
adjAmt = PIn.Decimal(dictAdjRows[procNum]["AdjAmt"].ToString());
}
decimal overPay = procFeeAmt - insPaidAmt - woAmt - ptPaidAmt + adjAmt;
if (!overPay.IsLessThanZero())
{
continue; //No overpayment. Not need to continue;
}
row = table.NewRow();
row["patientName"] = PIn.String(kvp.Value["patientName"].ToString());
row["ProcDate"] = PIn.Date(kvp.Value["ProcDate"].ToString());
row["ProcCode"] = PIn.String(kvp.Value["ProcCode"].ToString());
row["ToothNum"] = PIn.String(kvp.Value["ToothNum"].ToString());
row["Abbr"] = PIn.String(kvp.Value["Abbr"].ToString());;
row["fee"] = procFeeAmt.ToString();
row["insPaid"] = insPaidAmt.ToString();
row["wo"] = woAmt.ToString();
row["ptPaid"] = ptPaidAmt.ToString();
row["adjAmt"] = adjAmt.ToString();
row["overPay"] = overPay.ToString();
row["PatNum"] = PIn.Long(kvp.Value["PatNum"].ToString());
table.Rows.Add(row);
}
return(table);
}
