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 butReport_Click(object sender, System.EventArgs e)
{
if (errorProvider1.GetError(textDateFrom) != "" ||
errorProvider1.GetError(textDateTo) != "")
{
MsgBox.Show(this, "Please fix data entry errors first.");
return;
}
DateTime dateFrom = PIn.Date(textDateFrom.Text);
DateTime dateTo = PIn.Date(textDateTo.Text);
if (dateTo < dateFrom)
{
MsgBox.Show(this, "To date cannot be before From date.");
return;
}
ReportLikeCrystal report = new ReportLikeCrystal();
report.ReportName = Lan.g(this, "Birthdays");
report.AddTitle(Lan.g(this, "Birthdays"));
report.AddSubTitle(PrefC.GetString(PrefName.PracticeTitle));
report.AddSubTitle(dateFrom.ToString("MM/dd") + " - " + dateTo.ToString("MM/dd"));
/*report.Query=@"SELECT LName,FName,Address,Address2,City,State,Zip,Birthdate,Birthdate
* FROM patient
* WHERE SUBSTRING(Birthdate,6,5) >= '"+dateFrom.ToString("MM-dd")+"' "
+"AND SUBSTRING(Birthdate,6,5) <= '"+dateTo.ToString("MM-dd")+"' "
+"AND PatStatus=0 ORDER BY LName,FName";*/
report.AddColumn("LName", 90, FieldValueType.String);
report.AddColumn("FName", 90, FieldValueType.String);
report.AddColumn("Preferred", 90, FieldValueType.String);
report.AddColumn("Address", 90, FieldValueType.String);
report.AddColumn("Address2", 90, FieldValueType.String);
report.AddColumn("City", 75, FieldValueType.String);
report.AddColumn("State", 60, FieldValueType.String);
report.AddColumn("Zip", 75, FieldValueType.String);
report.AddColumn("Birthdate", 75, FieldValueType.Date);
report.GetLastRO(ReportObjectKind.FieldObject).FormatString = "d";
report.AddColumn("Age", 45, FieldValueType.Integer);
report.AddPageNum();
report.ReportTable = Patients.GetBirthdayList(dateFrom, dateTo);
//if(!report.SubmitQuery()){
// 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;
}
private void butReport_Click(object sender, System.EventArgs e)
{
//if(errorProvider1.GetError(textDateFrom) != ""
// || errorProvider1.GetError(textDateTo) != "")
//{
// MsgBox.Show(this,"Please fix data entry errors first.");
// return;
//}
//DateTime dateFrom=PIn.PDate(textDateFrom.Text);
//DateTime dateTo=PIn.PDate(textDateTo.Text);
//if(dateTo < dateFrom)
//{
// MsgBox.Show(this,"To date cannot be before From date.");
// return;
//}
ReportLikeCrystal report = new ReportLikeCrystal();
report.ReportName = Lan.g(this, "PaymentPlans");
report.AddTitle(Lan.g(this, "Payment Plans"));
report.AddSubTitle(PrefC.GetString(PrefName.PracticeTitle));
report.AddSubTitle(DateTime.Today.ToShortDateString());
DataTable table = new DataTable();
//table.Columns.Add("date");
table.Columns.Add("guarantor");
table.Columns.Add("ins");
table.Columns.Add("princ");
table.Columns.Add("paid");
table.Columns.Add("due");
table.Columns.Add("dueTen");
DataRow row;
string datesql = "CURDATE()";
if (DataConnection.DBtype == DatabaseType.Oracle)
{
datesql = "(SELECT CURRENT_DATE FROM dual)";
}
string command = @"SELECT FName,LName,MiddleI,PlanNum,Preferred,
(SELECT SUM(Principal+Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum
AND ChargeDate <= " + datesql + @") ""_accumDue"",
(SELECT SUM(Principal+Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum
AND ChargeDate <= " + DbHelper.DateAddDay(datesql, POut.Long(PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays))) + @") ""_dueTen"",
(SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.PayPlanNum=payplan.PayPlanNum) ""_paid"",
(SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum) ""_principal""
FROM payplan
LEFT JOIN patient ON patient.PatNum=payplan.Guarantor "
//WHERE SUBSTRING(Birthdate,6,5) >= '"+dateFrom.ToString("MM-dd")+"' "
//+"AND SUBSTRING(Birthdate,6,5) <= '"+dateTo.ToString("MM-dd")+"' "
+ "GROUP BY FName,LName,MiddleI,Preferred,payplan.PayPlanNum ORDER BY LName,FName";
DataTable raw = Reports.GetTable(command);
//DateTime payplanDate;
Patient pat;
double princ;
double paid;
double accumDue;
double dueTen;
for (int i = 0; i < raw.Rows.Count; i++)
{
princ = PIn.Double(raw.Rows[i]["_principal"].ToString());
paid = PIn.Double(raw.Rows[i]["_paid"].ToString());
accumDue = PIn.Double(raw.Rows[i]["_accumDue"].ToString());
dueTen = PIn.Double(raw.Rows[i]["_dueTen"].ToString());
row = table.NewRow();
//payplanDate=PIn.PDate(raw.Rows[i]["PayPlanDate"].ToString());
//row["date"]=raw.Rows[i]["PayPlanDate"].ToString();//payplanDate.ToShortDateString();
pat = new Patient();
pat.LName = raw.Rows[i]["LName"].ToString();
pat.FName = raw.Rows[i]["FName"].ToString();
pat.MiddleI = raw.Rows[i]["MiddleI"].ToString();
pat.Preferred = raw.Rows[i]["Preferred"].ToString();
row["guarantor"] = pat.GetNameLF();
if (raw.Rows[i]["PlanNum"].ToString() == "0")
{
row["ins"] = "";
}
else
{
row["ins"] = "X";
}
row["princ"] = princ.ToString("f");
row["paid"] = paid.ToString("f");
row["due"] = (accumDue - paid).ToString("f");
row["dueTen"] = (dueTen - paid).ToString("f");
table.Rows.Add(row);
}
report.ReportTable = table;
//report.AddColumn("Date",90,FieldValueType.Date);
report.AddColumn("Guarantor", 160, FieldValueType.String);
report.AddColumn("Ins", 40, FieldValueType.String);
report.GetLastRO(ReportObjectKind.TextObject).TextAlign = ContentAlignment.MiddleCenter;
report.GetLastRO(ReportObjectKind.FieldObject).TextAlign = ContentAlignment.MiddleCenter;
report.AddColumn("Princ", 100, FieldValueType.String);
report.GetLastRO(ReportObjectKind.TextObject).TextAlign = ContentAlignment.MiddleRight;
report.GetLastRO(ReportObjectKind.FieldObject).TextAlign = ContentAlignment.MiddleRight;
report.AddColumn("Paid", 100, FieldValueType.String);
report.GetLastRO(ReportObjectKind.TextObject).TextAlign = ContentAlignment.MiddleRight;
report.GetLastRO(ReportObjectKind.FieldObject).TextAlign = ContentAlignment.MiddleRight;
report.AddColumn("Due Now", 100, FieldValueType.String);
report.GetLastRO(ReportObjectKind.TextObject).TextAlign = ContentAlignment.MiddleRight;
report.GetLastRO(ReportObjectKind.FieldObject).TextAlign = ContentAlignment.MiddleRight;
report.AddColumn("Due in " + PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays).ToString()
+ " Days", 100, FieldValueType.String);
report.GetLastRO(ReportObjectKind.TextObject).TextAlign = ContentAlignment.MiddleRight;
report.GetLastRO(ReportObjectKind.FieldObject).TextAlign = ContentAlignment.MiddleRight;
//report.GetLastRO(ReportObjectKind.FieldObject).FormatString="d";
report.AddPageNum();
//report.SubmitQuery();
//report.ReportTable=Patients.GetBirthdayList(dateFrom,dateTo);
//if(!report.SubmitQuery()){
// 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 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 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;
}