private void ExecuteReport(){
ReportLikeCrystal report=new ReportLikeCrystal();
report.AddTitle("INCOMPLETE PROCEDURE NOTES");
report.AddSubTitle(PrefC.GetString(PrefName.PracticeTitle));
report.Query=@"(SELECT procedurelog.ProcDate,
CONCAT(CONCAT(patient.LName,', '),patient.FName),
procedurecode.ProcCode,procedurecode.Descript,
procedurelog.ToothNum,procedurelog.Surf
FROM procedurelog,patient,procedurecode,procnote n1
WHERE procedurelog.PatNum = patient.PatNum
AND procedurelog.CodeNum = procedurecode.CodeNum
AND procedurelog.ProcStatus = "+POut.Int((int)ProcStat.C)+@"
AND procedurelog.ProcNum=n1.ProcNum "
+"AND n1.Note LIKE '%\"\"%' "//looks for ""
+@"AND n1.EntryDateTime=(SELECT MAX(n2.EntryDateTime)
FROM procnote n2
WHERE n1.ProcNum = n2.ProcNum))
UNION ALL
(SELECT procedurelog.ProcDate,
CONCAT(CONCAT(patient.LName,', '),patient.FName),
procedurecode.ProcCode,procedurecode.Descript,
procedurelog.ToothNum,procedurelog.Surf
FROM procedurelog,patient,procedurecode,procnote n1
WHERE procedurelog.PatNum = patient.PatNum
AND procedurelog.CodeNum = procedurecode.CodeNum
AND procedurelog.ProcStatus = "+POut.Int((int)ProcStat.EC)+@"
AND procedurelog.ProcNum=n1.ProcNum "
+"AND n1.Note LIKE '%\"\"%' "//looks for ""
+@"AND n1.EntryDateTime=(SELECT MAX(n2.EntryDateTime)
FROM procnote n2
WHERE n1.ProcNum = n2.ProcNum)
AND procedurecode.ProcCode='~GRP~')
ORDER BY ProcDate";
report.AddColumn("Date",80,FieldValueType.Date);
report.AddColumn("Patient",120,FieldValueType.String);
report.AddColumn("Code",50,FieldValueType.String);
report.AddColumn("Description",120,FieldValueType.String);
report.AddColumn("Tth",30,FieldValueType.String);
report.AddColumn("Surf",40,FieldValueType.String);
report.AddPageNum();
if(!report.SubmitQuery()){
DialogResult=DialogResult.Cancel;
return;
}
FormReportLikeCrystal FormR=new FormReportLikeCrystal(report);
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
private void ExecuteGroup() {
ReportLikeCrystal report=new ReportLikeCrystal();
report.AddTitle(Lan.g(this,"PPO WRITEOFFS"));
report.AddSubTitle(PrefC.GetString(PrefName.PracticeTitle));
report.AddSubTitle(date1.SelectionStart.ToShortDateString()+" - "+date2.SelectionStart.ToShortDateString());
report.AddSubTitle(Lan.g(this,"Grouped by Carrier"));
if(textCarrier.Text!="") {
report.AddSubTitle(Lan.g(this,"Carrier like: ")+textCarrier.Text);
}
if(radioWriteoffPay.Checked){
report.Query="SET @DateFrom="+POut.Date(date1.SelectionStart)+", @DateTo="+POut.Date(date2.SelectionStart)
+", @CarrierName='%"+POut.String(textCarrier.Text)+"%';"
+@"SELECT carrier.CarrierName,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,carrier
WHERE claimproc.PlanNum = insplan.PlanNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4) /*received or supplemental*/
AND claimproc.DateCP >= @DateFrom
AND claimproc.DateCP <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY carrier.CarrierNum
ORDER BY carrier.CarrierName";
}
else{
report.Query="SET @DateFrom="+POut.Date(date1.SelectionStart)+", @DateTo="+POut.Date(date2.SelectionStart)
+", @CarrierName='%"+POut.String(textCarrier.Text)+"%';"
+@"SELECT carrier.CarrierName,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,carrier
WHERE claimproc.PlanNum = insplan.PlanNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) /*received or supplemental or notreceived*/
AND claimproc.ProcDate >= @DateFrom
AND claimproc.ProcDate <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY carrier.CarrierNum
ORDER BY carrier.CarrierName";
}
report.AddColumn("Carrier",180,FieldValueType.String);
report.AddColumn("Stand Fee",80,FieldValueType.Number);
report.AddColumn("PPO Fee",80,FieldValueType.Number);
report.AddColumn("Writeoff",80,FieldValueType.Number);
if(!report.SubmitQuery()) {
DialogResult=DialogResult.Cancel;
return;
}
FormReportLikeCrystal FormR=new FormReportLikeCrystal(report);
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
private void ExecuteReport(){
ReportLikeCrystal report=new ReportLikeCrystal();
report.IsLandscape=true;
report.AddTitle("CAPITATION UTILIZATION");
report.AddSubTitle(PrefC.GetString(PrefName.PracticeTitle));
//incomplete: Need more flexible default values, eg based on current date instead of fixed date:
DateTime DateTimeFirst=new DateTime(DateTime.Today.Year,DateTime.Today.Month,1);
report.AddParameter("carrier",FieldValueType.String,""
,"Enter a few letters of the name of the insurance carrier"
,"carrier.CarrierName LIKE '%?%'"); // SPK 8/04
report.AddParameter("date1",FieldValueType.Date,DateTimeFirst
,"From Date"
,"procedurelog.ProcDate >= '?'");
report.AddParameter("date2",FieldValueType.Date
,DateTimeFirst.AddMonths(1).AddDays(-1)
,"To Date"
,"procedurelog.ProcDate <= '?'"); // added carrierNum, SPK
report.Query=@"SELECT carrier.CarrierName,CONCAT(CONCAT(patSub.LName,', '),patSub.FName)
,patSub.SSN,CONCAT(CONCAT(patPat.LName,', '),patPat.FName)
,patPat.Birthdate,procedurecode.ProcCode,procedurecode.Descript
,procedurelog.ToothNum,procedurelog.Surf,procedurelog.ProcDate
,procedurelog.ProcFee,procedurelog.ProcFee-claimproc.WriteOff
FROM procedurelog,patient AS patSub,patient AS patPat
,insplan,inssub,carrier,procedurecode,claimproc
WHERE procedurelog.PatNum = patPat.PatNum
AND claimproc.InsSubNum = inssub.InsSubNum
AND procedurelog.ProcNum = claimproc.ProcNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.Status = 7
AND claimproc.NoBillIns = 0
AND inssub.Subscriber = patSub.PatNum
AND insplan.CarrierNum = carrier.CarrierNum
AND procedurelog.CodeNum = procedurecode.CodeNum
AND ?carrier
AND ?date1
AND ?date2
AND insplan.PlanType = 'c'
AND procedurelog.ProcStatus = 2";
report.AddColumn("Carrier",150,FieldValueType.String);
report.GetLastRO(ReportObjectKind.FieldObject).SuppressIfDuplicate=true;
report.AddColumn("Subscriber",120,FieldValueType.String);
report.GetLastRO(ReportObjectKind.FieldObject).SuppressIfDuplicate=true;
report.AddColumn("Subsc SSN",70,FieldValueType.String);
report.GetLastRO(ReportObjectKind.FieldObject).SuppressIfDuplicate=true;
report.AddColumn("Patient",120,FieldValueType.String);
report.AddColumn("Pat DOB",80,FieldValueType.Date);
report.AddColumn("Code",50,FieldValueType.String);
report.AddColumn("Proc Description",120,FieldValueType.String);
report.AddColumn("Tth",30,FieldValueType.String);
report.AddColumn("Surf",40,FieldValueType.String);
report.AddColumn("Date",80,FieldValueType.Date);
report.AddColumn("UCR Fee",70,FieldValueType.Number);
report.AddColumn("Co-Pay",70,FieldValueType.Number);
report.AddPageNum();
if(!report.SubmitQuery()){
DialogResult=DialogResult.Cancel;
return;
}
//incomplete: Add functionality for using parameter values in textObjects, probably using inline XML:
report.AddSubTitle(((DateTime)report.ParameterFields["date1"].CurrentValues[0]).ToShortDateString()+" - "+((DateTime)report.ParameterFields["date2"].CurrentValues[0]).ToShortDateString());
//incomplete: Implement formulas for situations like this:
for(int i=0;i<report.ReportTable.Rows.Count;i++){
if(PIn.Double(report.ReportTable.Rows[i][11].ToString())==-1){
report.ReportTable.Rows[i][11]="0";
}
}
FormReportLikeCrystal FormR=new FormReportLikeCrystal(report);
//FormR.MyReport=report;
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
private void ExecuteIndividual(){
ReportLikeCrystal report=new ReportLikeCrystal();
report.AddTitle(Lan.g(this,"PPO WRITEOFFS"));
report.AddSubTitle(PrefC.GetString(PrefName.PracticeTitle));
report.AddSubTitle(date1.SelectionStart.ToShortDateString()+" - "+date2.SelectionStart.ToShortDateString());
report.AddSubTitle(Lan.g(this,"Individual Claims"));
if(textCarrier.Text!=""){
report.AddSubTitle(Lan.g(this,"Carrier like: ")+textCarrier.Text);
}
report.Query="SET @DateFrom="+POut.Date(date1.SelectionStart)+", @DateTo="+POut.Date(date2.SelectionStart)
+", @CarrierName='%"+POut.String(textCarrier.Text)+"%';";
if(radioWriteoffPay.Checked){
report.Query+=@"SELECT claimproc.DateCP,
CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),
carrier.CarrierName,
provider.Abbr,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,patient,carrier,provider
WHERE provider.ProvNum = claimproc.ProvNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.PatNum = patient.PatNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4) /*received or supplemental*/
AND claimproc.DateCP >= @DateFrom
AND claimproc.DateCP <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY claimproc.ClaimNum
ORDER BY claimproc.DateCP";
}
else{//use procedure date
report.Query+=@"SELECT claimproc.ProcDate,
CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),
carrier.CarrierName,
provider.Abbr,
SUM(claimproc.FeeBilled),
SUM(claimproc.FeeBilled-claimproc.WriteOff),
SUM(claimproc.WriteOff),
claimproc.ClaimNum
FROM claimproc,insplan,patient,carrier,provider
WHERE provider.ProvNum = claimproc.ProvNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.PatNum = patient.PatNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) /*received or supplemental or notreceived*/
AND claimproc.ProcDate >= @DateFrom
AND claimproc.ProcDate <= @DateTo
AND insplan.PlanType='p'
AND carrier.CarrierName LIKE @CarrierName
GROUP BY claimproc.ClaimNum
ORDER BY claimproc.ProcDate";
}
report.AddColumn("Date",80,FieldValueType.Date);
report.AddColumn("Patient",120,FieldValueType.String);
report.AddColumn("Carrier",150,FieldValueType.String);
report.AddColumn("Provider",60,FieldValueType.String);
report.AddColumn("Stand Fee",80,FieldValueType.Number);
report.AddColumn("PPO Fee",80,FieldValueType.Number);
report.AddColumn("Writeoff",80,FieldValueType.Number);
if(!report.SubmitQuery()){
DialogResult=DialogResult.Cancel;
return;
}
FormReportLikeCrystal FormR=new FormReportLikeCrystal(report);
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
//validate user input
if(textDateFrom.errorProvider1.GetError(textDateFrom) != ""
|| textDateTo.errorProvider1.GetError(textDateTo) != "")
{
MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
return;
}
if(textDateFrom.Text.Length == 0
|| textDateTo.Text.Length == 0)
{
MessageBox.Show(Lan.g(this,"From and To dates are required."));
return;
}
DateTime dateFrom=PIn.Date(textDateFrom.Text);
DateTime dateTo=PIn.Date(textDateTo.Text);
if(dateTo < dateFrom)
{
MessageBox.Show(Lan.g(this,"To date cannot be before From date."));
return;
}
if(listProv.SelectedIndices.Count==0)
{
MessageBox.Show(Lan.g(this,"You must select at least one provider."));
return;
}
string whereProv;//used as the provider portion of the where clauses.
//each whereProv needs to be set up separately for each query
whereProv="(appointment.ProvNum IN (";
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i>0){
whereProv+=",";
}
whereProv+="'"+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+"'";
}
whereProv += ") ";
whereProv+="OR appointment.ProvHyg IN (";
for(int i=0;i<listProv.SelectedIndices.Count;i++) {
if(i>0) {
whereProv+=",";
}
whereProv+="'"+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+"'";
}
whereProv += ")) ";
//create the report
ReportLikeCrystal report=new ReportLikeCrystal();
report.IsLandscape=true;
report.ReportName="Appointments";
report.AddTitle("Appointments");
report.AddSubTitle(PrefC.GetString(PrefName.PracticeTitle));
report.AddSubTitle(dateFrom.ToShortDateString()+" - "+dateTo.ToShortDateString());
//setup query
report.Query=@"SELECT appointment.AptDateTime,
trim(CONCAT(CONCAT(CONCAT(CONCAT(concat(patient.LName,', '),case when length(patient.Preferred) > 0
then CONCAT(CONCAT('(',patient.Preferred),') ') else '' end),patient.fname), ' '),patient.middlei))
AS PatName,
patient.Birthdate,
appointment.AptDateTime,
length(appointment.Pattern)*5,
appointment.ProcDescript,
patient.HmPhone, patient.WkPhone, patient.WirelessPhone
FROM appointment INNER JOIN patient ON appointment.PatNum = patient.PatNum
WHERE appointment.AptDateTime between " + POut.Date(dateFrom) + " AND "
+POut.Date(dateTo.AddDays(1)) + " AND " +
"AptStatus != '" + (int)ApptStatus.UnschedList + "' AND " +
"AptStatus != '" + (int)ApptStatus.Planned + "' AND " +
whereProv + " " +
"ORDER BY appointment.AptDateTime, 2";
// add columns to report
report.AddColumn("Date", 75, FieldValueType.Date);
report.GetLastRO(ReportObjectKind.FieldObject).SuppressIfDuplicate = true;
report.GetLastRO(ReportObjectKind.FieldObject).FormatString="d";
report.AddColumn("Patient", 175, FieldValueType.String);
report.AddColumn("Age", 45, FieldValueType.Age);
// remove the total column
//if(report.ReportObjects[report.ReportObjects.Count-1].SummarizedField == "Age")
// report.ReportObjects.RemoveAt(report.ReportObjects.Count-1);
//report.GetLastRO(ReportObjectKind.FieldObject).FormatString = "###0";
//report.GetLastRO(ReportObjectKind.FieldObject).TextAlign = ContentAlignment.MiddleCenter;
//report.GetLastRO(ReportObjectKind.TextObject).TextAlign = ContentAlignment.MiddleCenter;
report.AddColumn("Time", 65, FieldValueType.Date);
report.GetLastRO(ReportObjectKind.FieldObject).FormatString="t";
report.GetLastRO(ReportObjectKind.TextObject).TextAlign = ContentAlignment.MiddleRight;
report.GetLastRO(ReportObjectKind.FieldObject).TextAlign = ContentAlignment.MiddleRight;
report.AddColumn("Length", 60, FieldValueType.Integer);
report.GetLastRO(ReportObjectKind.TextObject).Location=new Point(
report.GetLastRO(ReportObjectKind.TextObject).Location.X+6,
report.GetLastRO(ReportObjectKind.TextObject).Location.Y);
report.GetLastRO(ReportObjectKind.FieldObject).Location=new Point(
report.GetLastRO(ReportObjectKind.FieldObject).Location.X+8,
report.GetLastRO(ReportObjectKind.FieldObject).Location.Y);
report.AddColumn("Description", 170, FieldValueType.String);
report.AddColumn("Home Ph.", 120, FieldValueType.String);
report.AddColumn("Work Ph.", 120, FieldValueType.String);
report.AddColumn("Cell Ph.", 120, FieldValueType.String);
report.AddPageNum();
// execute query
if(!report.SubmitQuery()){
return;
}
// display report
FormReportLikeCrystal FormR=new FormReportLikeCrystal(report);
//FormR.MyReport=report;
FormR.ShowDialog();
DialogResult=DialogResult.OK;
}