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