private void butViewImported_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SELECT TransactionDateTime,TransType,ClerkID,ItemNum,PatNum,CreditCardNum,Expiration,Result,"
+ "CASE WHEN ResultCode IN('000','010') THEN Amount ELSE 0 END AS Amount "
+ "FROM xchargetransaction "
+ "WHERE " + DbHelper.DtimeToDate("TransactionDateTime") + " BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart);
FormQuery FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "XCharge Transactions From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString();
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this, 0, "Transaction Date/Time", 170);
report.SetColumn(this, 1, "Transaction Type", 120);
report.SetColumn(this, 2, "Clerk ID", 80);
report.SetColumn(this, 3, "Item#", 50);
report.SetColumn(this, 4, "Pat", 50); //This name is used to ensure FormQuery does not replace the patnum with the patient name.
report.SetColumn(this, 5, "Credit Card Num", 140);
report.SetColumn(this, 6, "Exp", 50);
report.SetColumn(this, 7, "Result", 50);
report.SetColumn(this, 8, "Amount", 60, HorizontalAlignment.Right);
Cursor = Cursors.Default;
FormQuery2.ShowDialog();
}
private void butExtra_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
string programNum = ProgramProperties.GetPropVal(Programs.GetCur(ProgramName.Xcharge).ProgramNum, "PaymentType");
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SELECT payment.PatNum, LName, FName, payment.DateEntry,payment.PayDate, payment.PayNote,payment.PayAmt "
+ "FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum "
+ "LEFT JOIN (SELECT TransactionDateTime,ClerkID,BatchNum,ItemNum,PatNum,CCType,CreditCardNum,Expiration,Result,Amount FROM xchargetransaction "
+ "WHERE DATE(TransactionDateTime) BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart) + @") AS X "
+ "ON X.PatNum=payment.PatNum AND DATE(X.TransactionDateTime)=payment.DateEntry AND X.Amount=payment.PayAmt "
+ "WHERE PayType=" + programNum + " AND DateEntry BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart) + " "
+ "AND X.TransactionDateTime IS NULL "
+ "ORDER BY PayDate ASC, patient.LName";
FormQuery FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Payments From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString();
report.SubTitle.Add("No Matching X-Charge Transactions for these Payments");
report.SetColumn(this, 0, "PatNum", 50);
report.SetColumn(this, 1, "LName", 100);
report.SetColumn(this, 2, "FName", 100);
report.SetColumn(this, 3, "DateEntry", 100);
report.SetColumn(this, 4, "PayDate", 100);
report.SetColumn(this, 5, "PayNote", 150);
report.SetColumn(this, 6, "PayAmt", 70, HorizontalAlignment.Right);
Cursor = Cursors.Default;
FormQuery2.ShowDialog();
}
private void butViewImported_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SELECT TransactionDateTime,ClerkID,BatchNum,ItemNum,PatNum,CCType,CreditCardNum,Expiration,Result,Amount FROM xchargetransaction "
+ "WHERE DATE(TransactionDateTime) BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart);
FormQuery FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "XCharge Transactions From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString();
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this, 0, "Transaction Date/Time", 170);
report.SetColumn(this, 1, "Clerk ID", 80);
report.SetColumn(this, 2, "Batch#", 50);
report.SetColumn(this, 3, "Item#", 50);
report.SetColumn(this, 4, "PatNum", 50);
report.SetColumn(this, 5, "CC Type", 55);
report.SetColumn(this, 6, "Credit Card Num", 140);
report.SetColumn(this, 7, "Exp", 50);
report.SetColumn(this, 8, "Result", 50);
report.SetColumn(this, 9, "Amount", 60, HorizontalAlignment.Right);
Cursor = Cursors.Default;
FormQuery2.ShowDialog();
}
private void butOK_Click(object sender, System.EventArgs e)
{
Cursor = Cursors.WaitCursor;
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = @"SELECT procedurelog.PatNum," + DbHelper.Concat("patient.LName", "', '", "patient.FName") + @" patname,
procedurelog.ProcDate,
SUM(procedurelog.ProcFee) ""$sumfee"",
SUM((SELECT SUM(claimproc.InsPayAmt + claimproc.Writeoff) FROM claimproc WHERE claimproc.ProcNum=procedurelog.ProcNum)) AS
""$PaidAndWriteoff""
FROM procedurelog
LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
LEFT JOIN patient ON patient.PatNum=procedurelog.PatNum
WHERE procedurelog.ProcStatus=2/*complete*/
AND procedurelog.ProcFee > 0
GROUP BY procedurelog.PatNum," + DbHelper.Concat("patient.LName", "', '", "patient.FName") + @",procedurelog.ProcDate
HAVING ROUND($sumfee,3) < ROUND($PaidAndWriteoff,3)
ORDER BY patname,ProcDate";
FormQuery FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "INSURANCE OVERPAID REPORT";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this, 0, "PatNum", 60);
report.SetColumn(this, 1, "Pat Name", 150);
report.SetColumn(this, 2, "Date", 80);
report.SetColumn(this, 3, "Fee", 80, HorizontalAlignment.Right);
report.SetColumn(this, 4, "InsPd+W/O", 90, HorizontalAlignment.Right);
Cursor = Cursors.Default;
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butPayments_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
string programNum = ProgramProperties.GetPropVal(Programs.GetCur(ProgramName.Xcharge).ProgramNum, "PaymentType");
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SET @pos=0; "
+ "SELECT @pos:=@pos+1 as 'Count', patient.PatNum, LName, FName, DateEntry,PayDate, PayNote,PayAmt "
+ "FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum "
+ "WHERE PayType=" + programNum + " AND DateEntry BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart)
+ "ORDER BY PayDate ASC, patient.LName";
FormQuery FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Payments From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString();
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this, 0, "Count", 50);
report.SetColumn(this, 1, "PatNum", 50);
report.SetColumn(this, 2, "LName", 100);
report.SetColumn(this, 3, "FName", 100);
report.SetColumn(this, 4, "DateEntry", 100);
report.SetColumn(this, 5, "PayDate", 100);
report.SetColumn(this, 6, "PayNote", 150);
report.SetColumn(this, 7, "PayAmt", 70, HorizontalAlignment.Right);
Cursor = Cursors.Default;
FormQuery2.ShowDialog();
}
private void butMissing_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
string programNum = ProgramProperties.GetPropVal(Programs.GetCur(ProgramName.Xcharge).ProgramNum, "PaymentType");
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SELECT TransactionDateTime,ClerkID,BatchNum,ItemNum,xchargetransaction.PatNum,CCType,CreditCardNum,Expiration,Result,Amount "
+ " FROM xchargetransaction LEFT JOIN ("
+ " SELECT patient.PatNum,LName,FName,DateEntry,PayDate,PayAmt,PayNote"
+ " FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum"
+ " WHERE PayType=" + programNum + " AND DateEntry BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart)
+ " ) AS P ON xchargetransaction.PatNum=P.PatNum AND DATE(xchargetransaction.TransactionDateTime)=P.DateEntry AND xchargetransaction.Amount=P.PayAmt "
+ " WHERE DATE(TransactionDateTime) BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart)
+ " AND P.PatNum IS NULL;";
FormQuery FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "XCharge Transactions From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString();
report.SubTitle.Add("No Matching Transaction Found in Open Dental");
report.SetColumn(this, 0, "Transaction Date/Time", 170);
report.SetColumn(this, 1, "Clerk ID", 80);
report.SetColumn(this, 2, "Batch#", 50);
report.SetColumn(this, 3, "Item#", 50);
report.SetColumn(this, 4, "PatNum", 50);
report.SetColumn(this, 5, "CC Type", 55);
report.SetColumn(this, 6, "Credit Card Num", 140);
report.SetColumn(this, 7, "Exp", 50);
report.SetColumn(this, 8, "Result", 50);
report.SetColumn(this, 9, "Amount", 60, HorizontalAlignment.Right);
Cursor = Cursors.Default;
FormQuery2.ShowDialog();
}
private void butOK_Click(object sender, System.EventArgs e)
{
//if(textDate.errorProvider1.GetError(textDate)!=""){
// MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
// return;
//}
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query =
"SELECT " + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ",adjamt "
+ "FROM patient,adjustment "
+ "WHERE patient.patnum=adjustment.patnum "
+ "AND adjustment.adjdate = " + POut.Date(PrefC.GetDate(PrefName.FinanceChargeLastRun))
+ "AND adjustment.adjtype = '" + POut.Long(PrefC.GetLong(PrefName.FinanceChargeAdjustmentType)) + "'";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "FINANCE CHARGE REPORT";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add("Date of Charges: " + PrefC.GetDate(PrefName.FinanceChargeLastRun).ToShortDateString());
report.SetColumn(this, 0, "Patient Name", 180);
report.SetColumn(this, 1, "Amount", 100, HorizontalAlignment.Right);
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butPayments_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SET @pos=0; "
+ "SELECT @pos:=@pos+1 AS 'Count',patient.PatNum,LName,FName,DateEntry,PayDate,PayNote,PayAmt "
+ "FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum "
+ "INNER JOIN ("
+ "SELECT ClinicNum,PropertyValue AS PaymentType FROM programproperty "
+ "WHERE ProgramNum=" + POut.Long(Programs.GetProgramNum(ProgramName.Xcharge)) + " AND PropertyDesc='PaymentType'"
+ ") paytypes ON paytypes.ClinicNum=payment.ClinicNum AND paytypes.PaymentType=payment.PayType "
//Must be DateEntry here. PayDate will not work with recurring charges
+ "WHERE DateEntry BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart) + " "
+ "ORDER BY Count ASC";
FormQuery FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Payments From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString();
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this, 0, "Count", 50);
report.SetColumn(this, 1, "Pat", 50); //This name is used to ensure FormQuery does not replace the patnum with the patient name.
report.SetColumn(this, 2, "LName", 100);
report.SetColumn(this, 3, "FName", 100);
report.SetColumn(this, 4, "DateEntry", 100);
report.SetColumn(this, 5, "PayDate", 100);
report.SetColumn(this, 6, "PayNote", 150);
report.SetColumn(this, 7, "PayAmt", 70, HorizontalAlignment.Right);
Cursor = Cursors.Default;
FormQuery2.ShowDialog();
}
private void CreateIndividual()
{
//added plfname for ordering purposes, spk 3/13/04
// added CapCoPay for Capitation, SPK 7/15/04 (js rewrote it on 10/4/04 due to db change)
// changed procedurecode.adacode to procedurelog.adacode & added Procnum to retrieve all codes
Queries.CurReport.Query = "SELECT procedurelog.ProcDate,CONCAT(CONCAT(CONCAT(CONCAT"
+ "(patient.LName,', '),patient.FName),' '),patient.MiddleI) AS plfname, procedurelog.ADACode,"
+ "procedurelog.ToothNum,procedurecode.Descript,provider.Abbr,"
+ "procedurelog.ProcFee-IFNULL(SUM(claimproc.WriteOff),0) $fee "//if no writeoff, then subtract 0
+ "FROM patient,procedurecode,provider,procedurelog "
+ "LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum "
+ "AND claimproc.Status='7' "//only CapComplete writeoffs are subtracted here.
+ "WHERE procedurelog.ProcStatus = '2' "
+ "AND patient.PatNum=procedurelog.PatNum "
+ "AND procedurelog.ADACode=procedurecode.ADACode "
+ "AND provider.ProvNum=procedurelog.ProvNum "
+ "AND " + whereProv + " "
+ "AND procedurelog.ADACode LIKE '%" + POut.PString(textADACode.Text) + "%' "
+ "AND procedurelog.ProcDate >= " + POut.PDate(date1.SelectionStart) + " "
+ "AND procedurelog.ProcDate <= " + POut.PDate(date2.SelectionStart) + " "
+ "GROUP BY procedurelog.ProcNum "
+ "ORDER BY procedurelog.ProcDate,plfname";
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "Daily Procedures";
Queries.CurReport.SubTitle = new string[2];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d")
+ " - " + date2.SelectionStart.ToString("d");
// col[5] from 590 to 640, 680, 770, spk 7/20/04
Queries.CurReport.ColPos = new int[9];
Queries.CurReport.ColCaption = new string[8];
Queries.CurReport.ColAlign = new HorizontalAlignment[8];
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 100;
Queries.CurReport.ColPos[2] = 250;
Queries.CurReport.ColPos[3] = 325;
Queries.CurReport.ColPos[4] = 370;
Queries.CurReport.ColPos[5] = 640;
Queries.CurReport.ColPos[6] = 680;
Queries.CurReport.ColPos[7] = 770;
Queries.CurReport.ColPos[8] = 820; // spk
Queries.CurReport.ColCaption[0] = "Date";
Queries.CurReport.ColCaption[1] = "Patient Name";
Queries.CurReport.ColCaption[2] = "ADA Code";
Queries.CurReport.ColCaption[3] = "Tooth";
Queries.CurReport.ColCaption[4] = "Description";
Queries.CurReport.ColCaption[5] = "Provider";
Queries.CurReport.ColCaption[6] = "Fee";
Queries.CurReport.ColAlign[6] = HorizontalAlignment.Right;
Queries.CurReport.ColCaption[7] = " "; // spk
Queries.CurReport.Summary = new string[0];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
Queries.CurReport = new ReportOld();
//if(radioRange.Checked){
Queries.CurReport.Query = "SELECT CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),"
+ "procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee "
+ "FROM patient,procedurecode,procedurelog,claimproc "
+ "WHERE claimproc.procnum=procedurelog.procnum "
+ "AND patient.PatNum=procedurelog.PatNum "
+ "AND procedurelog.CodeNum=procedurecode.CodeNum "
+ "AND claimproc.NoBillIns=0 "
+ "AND procedurelog.ProcFee>0 "
+ "AND claimproc.Status=6 "//estimate
+ "AND procedurelog.procstatus=2 "
+ "AND procedurelog.ProcDate >= " + POut.PDate(date1.SelectionStart) + " "
+ "AND procedurelog.ProcDate <= " + POut.PDate(date2.SelectionStart) + " "
+ "GROUP BY procedurelog.ProcNum";
/*}
* else{
* Queries.CurReport.Query="SELECT CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI),"
+"procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee FROM patient,procedurecode,"
+"procedurelog LEFT JOIN claimproc ON claimproc.procnum = procedurelog.procnum "
+"WHERE claimproc.procnum IS NULL "
+"&& patient.patnum=procedurelog.patnum && procedurelog.codenum=procedurecode.codenum "
+"&& patient.priplannum > 0 "
+"&& procedurelog.nobillins = 0 && procedurelog.procstatus = 2 "
+"&& procedurelog.ProcDate = '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"'";
* }*/
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "Procedures Not Billed to Insurance";
Queries.CurReport.SubTitle = new string[3];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d")
+ " - " + date2.SelectionStart.ToString("d");
Queries.CurReport.ColPos = new int[5];
Queries.CurReport.ColCaption = new string[4];
Queries.CurReport.ColAlign = new HorizontalAlignment[4];
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 205;
Queries.CurReport.ColPos[2] = 390;
Queries.CurReport.ColPos[3] = 575;
Queries.CurReport.ColPos[4] = 760;
Queries.CurReport.ColCaption[0] = "Patient Name";
Queries.CurReport.ColCaption[1] = "Procedure Date";
Queries.CurReport.ColCaption[2] = "Procedure Description";
Queries.CurReport.ColCaption[3] = "Procedure Amount";
Queries.CurReport.ColAlign[3] = HorizontalAlignment.Right;
Queries.CurReport.Summary = new string[3];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
ReportSimpleGrid report = new ReportSimpleGrid();
//if(radioRange.Checked){
report.Query = "SELECT ";
if (PrefC.GetBool(PrefName.ReportsShowPatNum))
{
report.Query += DbHelper.Concat("CAST(patient.PatNum AS CHAR)", "'-'", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
report.Query += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
report.Query += " AS 'PatientName',procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee "
+ "FROM patient,procedurecode,procedurelog,claimproc,insplan "
+ "WHERE claimproc.procnum=procedurelog.procnum "
+ "AND patient.PatNum=procedurelog.PatNum "
+ "AND procedurelog.CodeNum=procedurecode.CodeNum "
+ "AND claimproc.PlanNum=insplan.PlanNum "
+ "AND insplan.IsMedical=0 "
+ "AND claimproc.NoBillIns=0 "
+ "AND procedurelog.ProcFee>0 "
+ "AND claimproc.Status=6 " //estimate
+ "AND procedurelog.procstatus=2 "
+ "AND procedurelog.ProcDate >= " + POut.Date(date1.SelectionStart) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(date2.SelectionStart) + " "
+ "GROUP BY procedurelog.ProcNum "
+ "ORDER BY patient.LName,patient.FName";
/*}
* else{
* report.Query="SELECT CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI),"
+"procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee FROM patient,procedurecode,"
+"procedurelog LEFT JOIN claimproc ON claimproc.procnum = procedurelog.procnum "
+"WHERE claimproc.procnum IS NULL "
+"&& patient.patnum=procedurelog.patnum && procedurelog.codenum=procedurecode.codenum "
+"&& patient.priplannum > 0 "
+"&& procedurelog.nobillins = 0 && procedurelog.procstatus = 2 "
+"&& procedurelog.ProcDate = '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"'";
* }*/
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Procedures Not Billed to Insurance";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"));
report.SetColumn(this, 0, "Patient Name", 185);
report.SetColumn(this, 1, "Procedure Date", 185);
report.SetColumn(this, 2, "Procedure Description", 185);
report.SetColumn(this, 3, "Procedure Amount", 185, HorizontalAlignment.Right);
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
/*
* SELECT CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI),rxpat.rxdate,
* rxpat.drug,rxpat.sig,rxpat.disp,provider.abbr FROM patient,rxpat,provider
* WHERE patient.patnum=rxpat.patnum && provider.provnum=rxpat.provnum
*/
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SELECT CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName)," +
"' '),patient.MiddleI),rxpat.rxdate,"
+ "rxpat.drug,rxpat.sig,rxpat.disp,provider.abbr FROM patient,rxpat,provider "
+ "WHERE patient.patnum=rxpat.patnum AND provider.provnum=rxpat.provnum ";
if (radioPatient.Checked == true)
{
report.Query
+= "AND patient.lname like '" + textBoxInput.Text + "%'"
+ " ORDER BY patient.lname,patient.fname,rxpat.rxdate";
}
else
{
report.Query
+= "AND rxpat.drug like '" + textBoxInput.Text + "%'"
+ " ORDER BY patient.lname,rxpat.drug,rxpat.rxdate";
}
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Prescriptions";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if (radioPatient.Checked == true)
{
report.SubTitle.Add("By Patient");
}
else
{
report.SubTitle.Add("By Drug");
}
report.SetColumn(this, 0, "Patient Name", 120);
report.SetColumn(this, 1, "Date", 95);
report.SetColumn(this, 2, "Drug Name", 100);
report.SetColumn(this, 3, "Sig", 300);
report.SetColumn(this, 4, "Disp", 100);
report.SetColumn(this, 5, "Prov Name", 100);
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
carrier = PIn.PString(textBoxCarrier.Text);
Queries.CurReport = new ReportOld();
/*
* SELECT insplan.subscriber,insplan.carrier,patient.hmphone,
* insplan.groupname FROM insplan,patient WHERE insplan.subscriber=patient.patnum
* && insplan.carrier like +carrier+'%'
* Order By patient.lname,patient.fname
*
*/
Queries.CurReport.Query = "SELECT carrier.CarrierName"
+ ",CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),carrier.Phone,"
+ "insplan.Groupname "
+ "FROM insplan,patient,carrier,patplan "
+ "WHERE insplan.Subscriber=patient.PatNum "
+ "AND insplan.PlanNum=patplan.PlanNum "
+ "AND patplan.PatNum=patient.PatNum "
+ "AND patplan.Ordinal=1 "
+ "AND carrier.CarrierNum=insplan.CarrierNum "
+ "AND carrier.CarrierName LIKE '" + carrier + "%' "
+ "ORDER BY carrier.CarrierName,patient.LName";
//Debug.WriteLine(Queries.CurReport.Query);
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "Insurance Plan List";
Queries.CurReport.SubTitle = new string[2];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.ColPos = new int[5];
Queries.CurReport.ColCaption = new string[4];
Queries.CurReport.ColAlign = new HorizontalAlignment[4];
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 250;
Queries.CurReport.ColPos[2] = 425;
Queries.CurReport.ColPos[3] = 600;
Queries.CurReport.ColPos[4] = 765;
Queries.CurReport.ColCaption[0] = "Carrier Name";
Queries.CurReport.ColCaption[1] = "Subscriber Name";
Queries.CurReport.ColCaption[2] = "Carrier Phone#";
Queries.CurReport.ColCaption[3] = "Group Name";
Queries.CurReport.Summary = new string[1];
Queries.CurReport.Summary[0] = Lan.g(this, "Total: ") + Queries.TableQ.Rows.Count.ToString();
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void CreateGrouped()
{
//this would require a temporary table to be able to handle capitation.
Queries.CurReport.Query = "SELECT definition.ItemName,procedurelog.ADACode,procedurecode.Descript,"
+ "Count(*),AVG(procedurelog.ProcFee) $AvgFee,SUM(procedurelog.ProcFee) AS $TotFee "
+ "FROM procedurelog,procedurecode,definition "
+ "WHERE procedurelog.ProcStatus = '2' "
+ "AND procedurelog.ADACode=procedurecode.ADACode "
+ "AND definition.DefNum=procedurecode.ProcCat "
+ "AND " + whereProv + " "
+ "AND procedurelog.ADACode LIKE '%" + POut.PString(textADACode.Text) + "%' "
+ "AND procedurelog.ProcDate >= '" + date1.SelectionStart.ToString("yyyy-MM-dd") + "' "
+ "AND procedurelog.ProcDate <= '" + date2.SelectionStart.ToString("yyyy-MM-dd") + "' "
+ "GROUP BY procedurelog.ADACode "
+ "ORDER BY definition.ItemOrder,procedurelog.ADACode";
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "Procedures By Procedure Code";
Queries.CurReport.SubTitle = new string[2];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d")
+ " - " + date2.SelectionStart.ToString("d");
Queries.CurReport.ColPos = new int[7];
Queries.CurReport.ColCaption = new string[6];
Queries.CurReport.ColAlign = new HorizontalAlignment[6];
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 170;
Queries.CurReport.ColPos[2] = 260;
Queries.CurReport.ColPos[3] = 440;
Queries.CurReport.ColPos[4] = 490;
Queries.CurReport.ColPos[5] = 600;
Queries.CurReport.ColPos[6] = 700;
Queries.CurReport.ColCaption[0] = "Category";
Queries.CurReport.ColCaption[1] = "ADA Code";
Queries.CurReport.ColCaption[2] = "Description";
Queries.CurReport.ColCaption[3] = "Quantity";
Queries.CurReport.ColCaption[4] = "Average Fee";
Queries.CurReport.ColCaption[5] = "Total Fees";
Queries.CurReport.ColAlign[3] = HorizontalAlignment.Right;
Queries.CurReport.ColAlign[4] = HorizontalAlignment.Right;
Queries.CurReport.ColAlign[5] = HorizontalAlignment.Right;
//Queries.CurReport.ColCaption[7]=" ";
Queries.CurReport.Summary = new string[0];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, EventArgs e)
{
ReportSimpleGrid report = new ReportSimpleGrid();
if (radioDateRange.Checked)
{
report.Query = "SELECT DatePay," + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ",ItemName,SplitAmt "
+ "FROM paysplit,patient,definition "
+ "WHERE paysplit.PatNum=patient.PatNum "
+ "AND definition.DefNum=paysplit.UnearnedType "
+ "AND paysplit.DatePay >= " + POut.Date(date1.SelectionStart) + " "
+ "AND paysplit.DatePay <= " + POut.Date(date2.SelectionStart) + " "
+ "AND UnearnedType > 0 GROUP BY paysplit.SplitNum "
+ "ORDER BY DatePay";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Unearned Income Activity";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this, 0, "Date", 100);
report.SetColumn(this, 1, "Patient", 140);
report.SetColumn(this, 2, "Type", 110);
report.SetColumn(this, 3, "Amount", 80, HorizontalAlignment.Right);
}
else
{
report.Query = "SELECT " + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ",";
report.Query += DbHelper.GroupConcat("ItemName", true) + ",";
report.Query += "SUM(SplitAmt) Amount "
+ "FROM paysplit,patient,definition "
+ "WHERE paysplit.PatNum=patient.PatNum "
+ "AND definition.DefNum=paysplit.UnearnedType "
+ "AND UnearnedType > 0 GROUP BY paysplit.PatNum HAVING Amount != 0"; //still won't work for oracle
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Unearned Income Liabilities";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this, 0, "Patient", 140);
report.SetColumn(this, 1, "Type(s)", 110);
report.SetColumn(this, 2, "Amount", 80, HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
//if(textDate.errorProvider1.GetError(textDate)!=""){
// MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
// return;
//}
Queries.CurReport = new ReportOld();
Queries.CurReport.Query =
"SELECT CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),adjamt "
+ "FROM patient,adjustment "
+ "WHERE patient.patnum=adjustment.patnum "
+ "AND adjustment.adjdate = '" + ((Pref)PrefB.HList["FinanceChargeLastRun"]).ValueString + "'"
+ "AND adjustment.adjtype = '" + ((Pref)PrefB.HList["FinanceChargeAdjustmentType"]).ValueString + "'";
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "FINANCE CHARGE REPORT";
Queries.CurReport.SubTitle = new string[4];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = "Date of Charges: "
+ PIn.PDate(((Pref)PrefB.HList["FinanceChargeLastRun"]).ValueString).ToShortDateString();
//Queries.CurReport.SubTitle[2]="Adjustment type: "+PIn.PDate(textDate.Text).ToShortDateString();
Queries.CurReport.ColPos = new int[3];
Queries.CurReport.ColCaption = new string[2];
Queries.CurReport.ColAlign = new HorizontalAlignment[2];
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 200;
Queries.CurReport.ColPos[2] = 300;
Queries.CurReport.ColCaption[0] = "Patient Name";
Queries.CurReport.ColCaption[1] = "Amount";
Queries.CurReport.ColAlign[1] = HorizontalAlignment.Right;
Queries.CurReport.Summary = new string[0];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
carrier = PIn.String(textBoxCarrier.Text);
ReportSimpleGrid report = new ReportSimpleGrid();
/*
* SELECT insplan.subscriber,insplan.carrier,patient.hmphone,
* insplan.groupname FROM insplan,patient WHERE insplan.subscriber=patient.patnum
* && insplan.carrier like +carrier+'%'
* Order By patient.lname,patient.fname
*
*/
report.Query = "SELECT carrier.CarrierName"
+ ",CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),carrier.Phone,"
+ "insplan.Groupname "
+ "FROM insplan,inssub,patient,carrier " //,patplan "//we only include patplan to make sure insurance is active for a patient. We don't want any info from patplan.
+ "WHERE inssub.Subscriber=patient.PatNum "
+ "AND inssub.PlanNum=insplan.PlanNum "
+ "AND EXISTS (SELECT * FROM patplan WHERE patplan.InsSubNum=inssub.InsSubNum) "
//+"AND insplan.PlanNum=patplan.PlanNum "
//+"AND patplan.PatNum=patient.PatNum "
//+"AND patplan.Ordinal=1 "
+ "AND carrier.CarrierNum=insplan.CarrierNum "
+ "AND carrier.CarrierName LIKE '" + carrier + "%' "
+ "ORDER BY carrier.CarrierName,patient.LName";
//Debug.WriteLine(report.Query);
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Insurance Plan List";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this, 0, "Carrier Name", 230);
report.SetColumn(this, 1, "Subscriber Name", 175);
report.SetColumn(this, 2, "Carrier Phone#", 175);
report.SetColumn(this, 3, "Group Name", 165);
report.Summary.Add(Lan.g(this, "Total: ") + report.TableQ.Rows.Count.ToString());
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (listType.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one type must be selected.");
return;
}
string whereProv = "(";
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i > 0)
{
whereProv += "OR ";
}
whereProv += "adjustment.ProvNum = '"
+ POut.PInt(Providers.List[listProv.SelectedIndices[i]].ProvNum) + "' ";
}
whereProv += ")";
string whereType = "(";
for (int i = 0; i < listType.SelectedIndices.Count; i++)
{
if (i > 0)
{
whereType += "OR ";
}
whereType += "adjustment.AdjType = '"
+ POut.PInt(DefB.Short[(int)DefCat.AdjTypes][listType.SelectedIndices[i]].DefNum) + "' ";
}
whereType += ")";
Queries.CurReport = new ReportOld();
Queries.CurReport.Query = "SELECT adjustment.AdjDate,"
+ "CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),"
+ "definition.ItemName,adjustment.AdjNote,adjustment.AdjAmt FROM "
+ "adjustment,patient,definition WHERE adjustment.AdjType=definition.DefNum "
+ "AND patient.PatNum=adjustment.PatNum "
+ "AND " + whereProv + " "
+ "AND " + whereType + " "
+ "AND adjustment.AdjDate >= " + POut.PDate(date1.SelectionStart) + " "
+ "AND adjustment.AdjDate <= " + POut.PDate(date2.SelectionStart);
Queries.CurReport.Query += " ORDER BY adjustment.AdjDate";
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "Daily Adjustments";
Queries.CurReport.SubTitle = new string[2];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d");
Queries.CurReport.ColPos = new int[6];
Queries.CurReport.ColCaption = new string[5];
Queries.CurReport.ColAlign = new HorizontalAlignment[5];
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 120;
Queries.CurReport.ColPos[2] = 270;
Queries.CurReport.ColPos[3] = 495;
Queries.CurReport.ColPos[4] = 645;
Queries.CurReport.ColPos[5] = 720;
Queries.CurReport.ColCaption[0] = "Date";
Queries.CurReport.ColCaption[1] = "Patient Name";
Queries.CurReport.ColCaption[2] = "Adjustment Type";
Queries.CurReport.ColCaption[3] = "Adjustment Note";
Queries.CurReport.ColCaption[4] = "Amount";
Queries.CurReport.ColAlign[4] = HorizontalAlignment.Right;
Queries.CurReport.Summary = new string[0];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (!checkAllProv.Checked && listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
if (!checkAllClin.Checked && listClin.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one clinic must be selected.");
return;
}
}
string whereProv = "";
if (!checkAllProv.Checked)
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereProv += " AND (";
}
else
{
whereProv += "OR ";
}
whereProv += "claimproc.ProvNum = "
+ POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum) + " ";
}
whereProv += ") ";
}
string whereClin = "";
if (!checkAllClin.Checked)
{
for (int i = 0; i < listClin.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereClin += " AND (";
}
else
{
whereClin += "OR ";
}
if (listClin.SelectedIndices[i] == 0)
{
whereClin += "claimproc.ClinicNum = 0 ";
}
else
{
whereClin += "claimproc.ClinicNum = " + POut.Long(Clinics.List[listClin.SelectedIndices[i] - 1].ClinicNum) + " ";
}
}
whereClin += ") ";
}
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SET @FromDate=" + POut.Date(date1.SelectionStart) + ", @ToDate=" + POut.Date(date2.SelectionStart) + ";";
if (radioWriteoffPay.Checked)
{
report.Query += "SELECT " + DbHelper.DateColumn("claimproc.DateCP") + " date,"
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ","
+ "carrier.CarrierName,"
+ "provider.Abbr,";
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
report.Query += "clinic.Description,";
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "SUM(claimproc.WriteOff) $amount,";
}
else //Oracle needs quotes.
{
report.Query += "SUM(claimproc.WriteOff) \"$amount\",";
}
report.Query += "claimproc.ClaimNum "
+ "FROM claimproc " //,insplan,patient,carrier,provider "
+ "LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum "
+ "LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
+ "LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
+ "LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
+ "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+ "WHERE (claimproc.Status=1 OR claimproc.Status=4) " /*received or supplemental*/
+ whereProv
+ whereClin
+ "AND claimproc.DateCP >= @FromDate "
+ "AND claimproc.DateCP <= @ToDate "
+ "AND claimproc.WriteOff > 0 "
+ "GROUP BY claimproc.ProvNum,claimproc.DateCP,claimproc.ClinicNum,claimproc.PatNum "
+ "ORDER BY claimproc.DateCP,claimproc.PatNum";
}
else //using procedure date
{
report.Query += "SELECT " + DbHelper.DateColumn("claimproc.ProcDate") + " date, "
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ", "
+ "carrier.CarrierName, "
+ "provider.Abbr,";
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
report.Query += "clinic.Description,";
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "SUM(claimproc.WriteOff) $amount, ";
}
else //Oracle needs quotes.
{
report.Query += "SUM(claimproc.WriteOff) \"$amount\", ";
}
report.Query += "claimproc.ClaimNum "
+ "FROM claimproc " //,insplan,patient,carrier,provider "
+ "LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum "
+ "LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
+ "LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
+ "LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
+ "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+ "WHERE (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " /*received or supplemental or notreceived*/
+ whereProv
+ whereClin
+ "AND claimproc.ProcDate >= @FromDate "
+ "AND claimproc.ProcDate <= @ToDate "
+ "AND claimproc.WriteOff > 0 "
+ "GROUP BY claimproc.ProvNum,claimproc.ProcDate,claimproc.ClinicNum,claimproc.PatNum "
+ "ORDER BY claimproc.ProcDate,claimproc.PatNum";
}
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Daily Writeoffs";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"));
if (checkAllProv.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Providers"));
}
else
{
string provNames = "";
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i > 0)
{
provNames += ", ";
}
provNames += ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(provNames);
}
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
if (checkAllClin.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Clinics"));
}
else
{
string clinNames = "";
for (int i = 0; i < listClin.SelectedIndices.Count; i++)
{
if (i > 0)
{
clinNames += ", ";
}
if (listClin.SelectedIndices[i] == 0)
{
clinNames += Lan.g(this, "Unassigned");
}
else
{
clinNames += Clinics.List[listClin.SelectedIndices[i] - 1].Description;
}
}
report.SubTitle.Add(clinNames);
}
}
report.SetColumn(this, 0, "Date", 100);
report.SetColumn(this, 1, "Patient Name", 150);
report.SetColumn(this, 2, "Carrier", 225);
report.SetColumn(this, 3, "Provider", 60);
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
report.SetColumn(this, 4, "Clinic", 80);
report.SetColumn(this, 5, "Amount", 75, HorizontalAlignment.Right);
report.SetColumn(this, 6, "", 280, HorizontalAlignment.Right);
}
else
{
report.SetColumn(this, 4, "Amount", 75, HorizontalAlignment.Right);
report.SetColumn(this, 5, "", 280, HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
int feeSched = DefB.Short[(int)DefCat.FeeSchedNames][listFeeSched.SelectedIndex].DefNum;
string catName = ""; //string to hold current category name
Fees fee = new Fees();
Queries.CurReport = new ReportOld();
Queries.CurReport.Query = "SELECT procedurecode.ProcCode,fee.Amount,' ',procedurecode.Descript,"
+ "procedurecode.AbbrDesc FROM procedurecode,fee "
+ "WHERE procedurecode.CodeNum=fee.CodeNum AND fee.FeeSched='" + feeSched.ToString()
+ "' ORDER BY procedurecode.ProcCode";
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
if (radioCode.Checked == true)
{
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "Procedure Codes";
Queries.CurReport.SubTitle = new string[2];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = DefB.GetName(DefCat.FeeSchedNames, feeSched);
Queries.CurReport.ColPos = new int[6];
Queries.CurReport.ColCaption = new string[5];
Queries.CurReport.ColAlign = new HorizontalAlignment[5];
Queries.CurReport.ColPos[0] = 60;
Queries.CurReport.ColPos[1] = 130;
Queries.CurReport.ColPos[2] = 200;
Queries.CurReport.ColPos[3] = 220;
Queries.CurReport.ColPos[4] = 420;
Queries.CurReport.ColPos[5] = 620;
Queries.CurReport.ColCaption[0] = "Code";
Queries.CurReport.ColCaption[1] = "Fee Amount";
Queries.CurReport.ColCaption[2] = " "; //otherwise, the amount gets bunched up next to the description.
Queries.CurReport.ColCaption[3] = "Description";
Queries.CurReport.ColCaption[4] = "Abbr Description";
//Queries.CurReport.ColCaption[3]="Fee Amount";
Queries.CurReport.ColAlign[1] = HorizontalAlignment.Right;
Queries.CurReport.Summary = new string[0];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
else //categories
{
Queries.SubmitTemp(); //create TableTemp which is not actually used
ProcedureCode[] ProcList = ProcedureCodes.GetProcList();
Queries.TableQ = new DataTable(null);
for (int i = 0; i < 5; i++) //add columns
{
Queries.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns
}
Queries.CurReport.ColTotal = new double[Queries.TableQ.Columns.Count];
DataRow row = Queries.TableQ.NewRow();//add first row by hand to get value for temp
row[0] = DefB.GetName(DefCat.ProcCodeCats, ProcList[0].ProcCat);
catName = row[0].ToString();
row[1] = ProcList[0].ProcCode;
row[2] = ProcList[0].Descript;
row[3] = ProcList[0].AbbrDesc;
row[4] = ((double)Fees.GetAmount0(ProcList[0].CodeNum, feeSched)).ToString("F");
Queries.CurReport.ColTotal[4] += PIn.PDouble(row[4].ToString());
Queries.TableQ.Rows.Add(row);
for (int i = 1; i < ProcList.Length; i++) //loop through data rows
{
row = Queries.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ
row[0] = DefB.GetName(DefCat.ProcCodeCats, ProcList[i].ProcCat);
if (catName == row[0].ToString())
{
row[0] = "";
}
else
{
catName = row[0].ToString();
}
row[1] = ProcList[i].ProcCode.ToString();
row[2] = ProcList[i].Descript;
row[3] = ProcList[i].AbbrDesc.ToString();
row[4] = ((double)Fees.GetAmount0(ProcList[i].CodeNum, feeSched)).ToString("F");
//Queries.CurReport.ColTotal[4]+=PIn.PDouble(row[4].ToString());
Queries.TableQ.Rows.Add(row);
}
Queries.CurReport.ColWidth = new int[Queries.TableQ.Columns.Count];
Queries.CurReport.ColPos = new int[Queries.TableQ.Columns.Count + 1];
Queries.CurReport.ColPos[0] = 0;
Queries.CurReport.ColCaption = new string[Queries.TableQ.Columns.Count];
Queries.CurReport.ColAlign = new HorizontalAlignment[Queries.TableQ.Columns.Count];
FormQuery2.ResetGrid(); //this is a method in FormQuery2;
Queries.CurReport.Title = "Procedure Codes";
Queries.CurReport.SubTitle = new string[5];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = DefB.GetName(DefCat.FeeSchedNames, feeSched);
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 120;
Queries.CurReport.ColPos[2] = 270;
Queries.CurReport.ColPos[3] = 470;
Queries.CurReport.ColPos[4] = 620;
Queries.CurReport.ColPos[5] = 770;
Queries.CurReport.ColCaption[0] = "Category";
Queries.CurReport.ColCaption[1] = "Code";
Queries.CurReport.ColCaption[2] = "Description";
Queries.CurReport.ColCaption[3] = "Abbr Description";
Queries.CurReport.ColCaption[4] = "Fee Amount";
Queries.CurReport.ColAlign[4] = HorizontalAlignment.Right;
Queries.CurReport.Summary = new string[5];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
}
/*private void butToday_Click(object sender, System.EventArgs e) {
* textDateFrom.Text=DateTime.Today.ToShortDateString();
* textDateTo.Text=DateTime.Today.ToShortDateString();
* }
*
* private void butLeft_Click(object sender, System.EventArgs e) {
* if( textDateFrom.errorProvider1.GetError(textDateFrom)!=""
|| textDateTo.errorProvider1.GetError(textDateTo)!=""
|| ){
|| MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
|| return;
|| }
|| DateTime dateFrom=PIn.PDate(textDateFrom.Text);
|| DateTime dateTo=PIn.PDate(textDateTo.Text);
|| textDateFrom.Text=dateFrom.AddDays(-1).ToShortDateString();
|| textDateTo.Text=dateTo.AddDays(-1).ToShortDateString();
||}
||
||private void butRight_Click(object sender, System.EventArgs e) {
|| if( textDateFrom.errorProvider1.GetError(textDateFrom)!=""
|| textDateTo.errorProvider1.GetError(textDateTo)!=""
|| ){
|| MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
|| return;
|| }
|| DateTime dateFrom=PIn.PDate(textDateFrom.Text);
|| DateTime dateTo=PIn.PDate(textDateTo.Text);
|| textDateFrom.Text=dateFrom.AddDays(1).ToShortDateString();
|| textDateTo.Text=dateTo.AddDays(1).ToShortDateString();
||}*/
private void butOK_Click(object sender, System.EventArgs e)
{
/*
* SELECT PayDate,CONCAT(patient.LName,',',patient.FName,' ',patient.MiddleI),CheckNum,BankBranch,PayAmt
* FROM payment,patient
* WHERE payment.PatNum=patient.PatNum
* GROUP BY PayDate
* UNION
* SELECT CheckDate,insplan.Carrier,CheckNum,BankBranch,CheckAmt
* FROM claimproc,claimpayment,insplan
* WHERE claimproc.ClaimPaymentNum=claimpayment.ClaimPaymentNum && claimproc.PlanNum=insplan.PlanNum
* GROUP BY CheckDate
* ORDER BY PayDate
*/
/*
* SELECT PayDate,CONCAT(patient.LName,', ',patient.FName,' ',
* patient.MiddleI) AS plfname,' ',PayType,
* PayNum,CheckNum,BankBranch,PayAmt
* FROM payment,patient WHERE
* payment.PatNum = patient.PatNum AND (
* PayType = '69'
* ) AND PayDate = '2005-01-10'
* UNION SELECT CheckDate,CONCAT(patient.LName,', ',patient.FName,' ',
* patient.MiddleI) AS plfname,CarrierName,'Ins',
* claimpayment.ClaimPaymentNum,
* CheckNum,BankBranch,CheckAmt
* FROM claimpayment,claimproc,insplan,carrier,patient
* WHERE claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum
* AND claimproc.PlanNum = insplan.PlanNum
* AND claimproc.PatNum=patient.PatNum
* AND insplan.CarrierNum = carrier.CarrierNum
* AND (claimproc.status = '1' OR claimproc.status = '4')
* AND CheckDate = '2005-01-10'
* ORDER BY PayDate, plfname
*/
if (listPayType.SelectedIndices.Count == 0 &&
!checkBoxIns.Checked)
{
MessageBox.Show("Must either select a payment type and/or include insurance checks.");
return;
}
Queries.CurReport = new ReportOld();
string cmd = "";
//if(checkBoxIns.Checked){
cmd = "SELECT PayDate,CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),"
+ "patient.MiddleI) AS plfname,' ',PayType,"
+ "PayNum,CheckNum,BankBranch,PayAmt "
+ "FROM payment,patient WHERE "; //added plfname,paynum spk 4/14/04
if (listPayType.SelectedIndices.Count == 0)
{
cmd += "1=0 "; //none
}
else
{
cmd += "payment.PatNum = patient.PatNum AND (";
for (int i = 0; i < listPayType.SelectedIndices.Count; i++)
{
if (i > 0)
{
cmd += " OR ";
}
cmd += "PayType = '"
+ DefB.Short[(int)DefCat.PaymentTypes][listPayType.SelectedIndices[i]].DefNum + "'";
}
cmd +=
") AND PayDate >= " + POut.PDate(monthCal1.SelectionStart) + " "
+ "AND PayDate <= " + POut.PDate(monthCal2.SelectionStart) + " ";
if (!PrefB.GetBool("EasyNoClinics"))
{
if (comboClinic.SelectedIndex == 0)
{
cmd += "AND payment.ClinicNum=0 ";
}
else
{
cmd += "AND payment.ClinicNum="
+ POut.PInt(Clinics.List[comboClinic.SelectedIndex - 1].ClinicNum) + " ";
}
}
}
if (checkBoxIns.Checked)
{
cmd += "UNION SELECT CheckDate,CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),"
+ "patient.MiddleI) AS plfname,CarrierName,'Ins',"
+ "claimpayment.ClaimPaymentNum,"
+ "CheckNum,BankBranch,CheckAmt " //spk added claimpaymentnum
//+"claimpayment.ClaimPaymentNum "
+ "FROM claimpayment,claimproc,insplan,carrier,patient "
+ "WHERE claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum "
+ "AND claimproc.PlanNum = insplan.PlanNum "
+ "AND claimproc.PatNum=patient.PatNum "
+ "AND insplan.CarrierNum = carrier.CarrierNum "
+ "AND (claimproc.status = '1' OR claimproc.status = '4') "
+ "AND CheckDate >= " + POut.PDate(monthCal1.SelectionStart) + " "
+ "AND CheckDate <= " + POut.PDate(monthCal2.SelectionStart) + " "; //added plfname,spk 4/30/04
if (!PrefB.GetBool("EasyNoClinics"))
{
if (comboClinic.SelectedIndex == 0)
{
cmd += "AND claimpayment.ClinicNum=0 ";
}
else
{
cmd += "AND claimpayment.ClinicNum="
+ POut.PInt(Clinics.List[comboClinic.SelectedIndex - 1].ClinicNum) + " ";
}
}
cmd += "GROUP BY claimpayment.ClaimPaymentNum ";
//MessageBox.Show(Queries.CurReport.Query);
}
//cmd+="ORDER BY PayDate, plfname";//FIXME:UNION-ORDER-BY
cmd += "ORDER BY 1, 2";
Queries.CurReport.Query = cmd;
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "Deposit Slip";
Queries.CurReport.SubTitle = new string[3];
if (!PrefB.GetBool("EasyNoClinics"))
{
Queries.CurReport.SubTitle = new string[4];
if (comboClinic.SelectedIndex == 0)
{
Queries.CurReport.SubTitle[3] = Lan.g(this, "Clinic") + ": none";
}
else
{
Queries.CurReport.SubTitle[3] = Lan.g(this, "Clinic") + ": "
+ Clinics.List[comboClinic.SelectedIndex - 1].Description;
}
}
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = monthCal1.SelectionStart.ToShortDateString() + " - "
+ monthCal2.SelectionStart.ToShortDateString();
if (listPayType.SelectedIndices.Count > 0)
{
Queries.CurReport.SubTitle[2] = "Payment Type(s): ";
for (int i = 0; i < listPayType.SelectedIndices.Count; i++)
{
if (i > 0)
{
Queries.CurReport.SubTitle[2] += ", ";
}
Queries.CurReport.SubTitle[2]
+= DefB.Short[(int)DefCat.PaymentTypes][listPayType.SelectedIndices[i]].ItemName;
}
if (checkBoxIns.Checked)
{
Queries.CurReport.SubTitle[2] += " Insurance Claim Checks";
}
}
else
{
if (checkBoxIns.Checked)
{
Queries.CurReport.SubTitle[2] = "Payment Type: Insurance Claim Checks";
}
}
Queries.CurReport.ColPos = new int[9];
Queries.CurReport.ColCaption = new string[8];
Queries.CurReport.ColAlign = new HorizontalAlignment[8];
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 100;
Queries.CurReport.ColPos[2] = 210;
Queries.CurReport.ColPos[3] = 320;
Queries.CurReport.ColPos[4] = 420;
Queries.CurReport.ColPos[5] = 480;
Queries.CurReport.ColPos[6] = 590;
Queries.CurReport.ColPos[7] = 680;
Queries.CurReport.ColPos[8] = 760;
Queries.CurReport.ColCaption[0] = "Date";
Queries.CurReport.ColCaption[1] = "Patient";
Queries.CurReport.ColCaption[2] = "Carrier";
Queries.CurReport.ColCaption[3] = "Type";
//this column can be eliminated when the new reporting framework is complete:
Queries.CurReport.ColCaption[4] = "Pay #";
Queries.CurReport.ColCaption[5] = "Check Number";
Queries.CurReport.ColCaption[6] = "Bank-Branch";
Queries.CurReport.ColCaption[7] = "Amount";
//Queries.CurReport.ColAlign[4]=HorizontalAlignment.Right;
Queries.CurReport.ColAlign[7] = HorizontalAlignment.Right;
if (PrefB.GetBool("EasyNoClinics") || comboClinic.SelectedIndex == 0)
{
Queries.CurReport.Summary = new string[3];
Queries.CurReport.Summary[0] = "For Deposit to Account of " + ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.Summary[2] = "Account number: " + ((Pref)PrefB.HList["PracticeBankNumber"]).ValueString;
}
else
{
Queries.CurReport.Summary = new string[3];
Queries.CurReport.Summary[0] = "For Deposit to Account of " + Clinics.List[comboClinic.SelectedIndex - 1].Description;
Queries.CurReport.Summary[2] = "Account number: " + Clinics.List[comboClinic.SelectedIndex - 1].BankNumber;
}
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (!checkBillTypesAll.Checked && listBillType.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one billing type must be selected.");
return;
}
if (!checkProvAll.Checked && listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
Queries.CurReport = new ReportOld();
Queries.CurReport.Query = "SELECT CONCAT(CONCAT(CONCAT(CONCAT(LName,', '),FName),' '),MiddleI)"
+ ",Bal_0_30,Bal_31_60,Bal_61_90,BalOver90"
+ ",BalTotal "
+ ",InsEst"
+ ",BalTotal-InsEst AS $pat "
+ "FROM patient ";
if (checkOnlyNeg.Checked)
{
Queries.CurReport.Query += "WHERE BalTotal < '-.005'";
}
else
{
Queries.CurReport.Query += "WHERE ";
if (checkExcludeInactive.Checked)
{
Queries.CurReport.Query += "(patstatus != 2) AND ";
}
if (radioAny.Checked)
{
Queries.CurReport.Query +=
"(Bal_0_30 > '.005' OR Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if (radio30.Checked)
{
Queries.CurReport.Query +=
"(Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if (radio60.Checked)
{
Queries.CurReport.Query +=
"(Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if (radio90.Checked)
{
Queries.CurReport.Query +=
"(BalOver90 > '.005'";
}
if (checkIncludeNeg.Checked)
{
Queries.CurReport.Query += " OR BalTotal < '-.005'";
}
Queries.CurReport.Query += ") ";
}
if (!checkBillTypesAll.Checked)
{
for (int i = 0; i < listBillType.SelectedIndices.Count; i++)
{
if (i == 0)
{
Queries.CurReport.Query += " AND (billingtype = ";
}
else
{
Queries.CurReport.Query += " OR billingtype = ";
}
Queries.CurReport.Query += POut.PInt(DefB.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].DefNum);
}
Queries.CurReport.Query += ") ";
}
if (!checkProvAll.Checked)
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i == 0)
{
Queries.CurReport.Query += " AND (PriProv = ";
}
else
{
Queries.CurReport.Query += " OR PriProv = ";
}
Queries.CurReport.Query += POut.PInt(Providers.List[listProv.SelectedIndices[i]].ProvNum);
}
Queries.CurReport.Query += ") ";
}
Queries.CurReport.Query += "ORDER BY LName,FName";
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "AGING REPORT";
Queries.CurReport.SubTitle = new string[4];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = "As of " + textDate.Text;
if (radioAny.Checked)
{
Queries.CurReport.SubTitle[2] = "Any Balance";
}
if (radio30.Checked)
{
Queries.CurReport.SubTitle[2] = "Over 30 Days";
}
if (radio60.Checked)
{
Queries.CurReport.SubTitle[2] = "Over 60 Days";
}
if (radio90.Checked)
{
Queries.CurReport.SubTitle[2] = "Over 90 Days";
}
if (listBillType.SelectedIndices.Count == DefB.Short[(int)DefCat.BillingTypes].Length)
{
Queries.CurReport.SubTitle[3] = "All Billing Types";
}
else
{
Queries.CurReport.SubTitle[3] = DefB.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[0]].ItemName;
for (int i = 1; i < listBillType.SelectedIndices.Count; i++)
{
Queries.CurReport.SubTitle[3] += ", " + DefB.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].ItemName;
}
}
Queries.CurReport.ColPos = new int[9];
Queries.CurReport.ColCaption = new string[8];
Queries.CurReport.ColAlign = new HorizontalAlignment[8];
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 180;
Queries.CurReport.ColPos[2] = 260;
Queries.CurReport.ColPos[3] = 340;
Queries.CurReport.ColPos[4] = 420;
Queries.CurReport.ColPos[5] = 500;
Queries.CurReport.ColPos[6] = 585;
Queries.CurReport.ColPos[7] = 670;
Queries.CurReport.ColPos[8] = 755;
Queries.CurReport.ColCaption[0] = "GUARANTOR";
Queries.CurReport.ColCaption[1] = "0-30 DAYS";
Queries.CurReport.ColCaption[2] = "30-60 DAYS";
Queries.CurReport.ColCaption[3] = "60-90 DAYS";
Queries.CurReport.ColCaption[4] = "> 90 DAYS";
Queries.CurReport.ColCaption[5] = "TOTAL";
Queries.CurReport.ColCaption[6] = "-INS EST";
Queries.CurReport.ColCaption[7] = "=PATIENT";
Queries.CurReport.ColAlign[1] = HorizontalAlignment.Right;
Queries.CurReport.ColAlign[2] = HorizontalAlignment.Right;
Queries.CurReport.ColAlign[3] = HorizontalAlignment.Right;
Queries.CurReport.ColAlign[4] = HorizontalAlignment.Right;
Queries.CurReport.ColAlign[5] = HorizontalAlignment.Right;
Queries.CurReport.ColAlign[6] = HorizontalAlignment.Right;
Queries.CurReport.ColAlign[7] = HorizontalAlignment.Right;
Queries.CurReport.Summary = new string[0];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
/*
* SELECT CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI),rxpat.rxdate,
* rxpat.drug,rxpat.sig,rxpat.disp,provider.abbr FROM patient,rxpat,provider
* WHERE patient.patnum=rxpat.patnum && provider.provnum=rxpat.provnum
*/
Queries.CurReport = new ReportOld();
Queries.CurReport.Query = "SELECT CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName)," +
"' '),patient.MiddleI),rxpat.rxdate,"
+ "rxpat.drug,rxpat.sig,rxpat.disp,provider.abbr FROM patient,rxpat,provider "
+ "WHERE patient.patnum=rxpat.patnum AND provider.provnum=rxpat.provnum ";
if (radioPatient.Checked == true)
{
Queries.CurReport.Query
+= "AND patient.lname like '" + textBoxInput.Text + "%'"
+ " ORDER BY patient.lname,patient.fname,rxpat.rxdate";
}
else
{
Queries.CurReport.Query
+= "AND rxpat.drug like '" + textBoxInput.Text + "%'"
+ " ORDER BY patient.lname,rxpat.drug,rxpat.rxdate";
}
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "Prescriptions";
Queries.CurReport.SubTitle = new string[2];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
if (radioPatient.Checked == true)
{
Queries.CurReport.SubTitle[1] = "By Patient";
}
else
{
Queries.CurReport.SubTitle[1] = "By Drug";
}
Queries.CurReport.ColPos = new int[7];
Queries.CurReport.ColCaption = new string[6];
Queries.CurReport.ColAlign = new HorizontalAlignment[6];
Queries.CurReport.ColPos[0] = 10;
Queries.CurReport.ColPos[1] = 130;
Queries.CurReport.ColPos[2] = 225;
Queries.CurReport.ColPos[3] = 325;
Queries.CurReport.ColPos[4] = 625;
Queries.CurReport.ColPos[5] = 725;
Queries.CurReport.ColPos[6] = 825;
Queries.CurReport.ColCaption[0] = "Patient Name";
Queries.CurReport.ColCaption[1] = "Date";
Queries.CurReport.ColCaption[2] = "Drug Name";
Queries.CurReport.ColCaption[3] = "Sig";
Queries.CurReport.ColCaption[4] = "Disp";
Queries.CurReport.ColCaption[5] = "Prov Name";
Queries.CurReport.Summary = new string[0];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
long feeSched = FeeSchedC.ListShort[listFeeSched.SelectedIndex].FeeSchedNum;
string catName = ""; //string to hold current category name
Fees fee = new Fees();
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SELECT procedurecode.ProcCode,fee.Amount,' ',procedurecode.Descript,"
+ "procedurecode.AbbrDesc FROM procedurecode,fee "
+ "WHERE procedurecode.CodeNum=fee.CodeNum AND fee.FeeSched='" + feeSched.ToString()
+ "' ORDER BY procedurecode.ProcCode";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
if (radioCode.Checked == true)
{
FormQuery2.SubmitReportQuery();
report.Title = "Procedure Codes";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(FeeScheds.GetDescription(feeSched));
report.SetColumn(this, 0, "Code", 70);
report.SetColumn(this, 1, "Fee Amount", 70, HorizontalAlignment.Right);
report.SetColumn(this, 2, " ", 80); //otherwise, the amount gets bunched up next to the description.
report.SetColumn(this, 3, "Description", 200);
report.SetColumn(this, 4, "Abbr Description", 200);
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
else //categories
//report.SubmitTemp();//create TableTemp which is not actually used
{
ProcedureCode[] ProcList = ProcedureCodes.GetProcList();
report.TableQ = new DataTable(null);
for (int i = 0; i < 5; i++) //add columns
{
report.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns
}
report.InitializeColumns();
DataRow row = report.TableQ.NewRow();//add first row by hand to get value for temp
row[0] = DefC.GetName(DefCat.ProcCodeCats, ProcList[0].ProcCat);
catName = row[0].ToString();
row[1] = ProcList[0].ProcCode;
row[2] = ProcList[0].Descript;
row[3] = ProcList[0].AbbrDesc;
row[4] = ((double)Fees.GetAmount0(ProcList[0].CodeNum, feeSched)).ToString("F");
report.ColTotal[4] += PIn.Decimal(row[4].ToString());
report.TableQ.Rows.Add(row);
for (int i = 1; i < ProcList.Length; i++) //loop through data rows
{
row = report.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ
row[0] = DefC.GetName(DefCat.ProcCodeCats, ProcList[i].ProcCat);
if (catName == row[0].ToString())
{
row[0] = "";
}
else
{
catName = row[0].ToString();
}
row[1] = ProcList[i].ProcCode.ToString();
row[2] = ProcList[i].Descript;
row[3] = ProcList[i].AbbrDesc.ToString();
row[4] = ((double)Fees.GetAmount0(ProcList[i].CodeNum, feeSched)).ToString("F");
//report.ColTotal[4]+=PIn.PDouble(row[4].ToString());
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid(); //this is a method in FormQuery2;
report.Title = "Procedure Codes";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(FeeScheds.GetDescription(feeSched));
report.ColPos[0] = 20;
report.ColPos[1] = 120;
report.ColPos[2] = 270;
report.ColPos[3] = 470;
report.ColPos[4] = 620;
report.ColPos[5] = 770;
report.ColCaption[0] = "Category";
report.ColCaption[1] = "Code";
report.ColCaption[2] = "Description";
report.ColCaption[3] = "Abbr Description";
report.ColCaption[4] = "Fee Amount";
report.ColAlign[4] = HorizontalAlignment.Right;
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
}
private void CreateGrouped(ReportSimpleGrid report)
{
//this would require a temporary table to be able to handle capitation.
report.Query = "SELECT definition.ItemName,procedurecode.ProcCode,procedurecode.Descript,";
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "Count(*),AVG(procedurelog.ProcFee) $AvgFee,SUM(procedurelog.ProcFee) AS $TotFee ";
}
else //Oracle needs quotes.
{
report.Query += "Count(*),AVG(procedurelog.ProcFee) \"$AvgFee\",SUM(procedurelog.ProcFee) AS \"$TotFee\" ";
}
report.Query += "FROM procedurelog,procedurecode,definition "
+ "WHERE procedurelog.ProcStatus = '2' "
+ "AND procedurelog.CodeNum=procedurecode.CodeNum "
+ "AND definition.DefNum=procedurecode.ProcCat "
+ whereProv
+ whereClin
+ "AND procedurecode.ProcCode LIKE '%" + POut.String(textCode.Text) + "%' "
+ "AND " + DbHelper.DateColumn("procedurelog.ProcDate") + " >= '" + date1.SelectionStart.ToString("yyyy-MM-dd") + "' "
+ "AND " + DbHelper.DateColumn("procedurelog.ProcDate") + " <= '" + date2.SelectionStart.ToString("yyyy-MM-dd") + "' "
+ "GROUP BY procedurecode.ProcCode "
+ "ORDER BY definition.ItemOrder,procedurecode.ProcCode";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Procedures By Procedure Code";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"));
if (checkAllProv.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Providers"));
}
else
{
string provNames = "";
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i > 0)
{
provNames += ", ";
}
provNames += ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(provNames);
}
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
if (checkAllClin.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Clinics"));
}
else
{
string clinNames = "";
for (int i = 0; i < listClin.SelectedIndices.Count; i++)
{
if (i > 0)
{
clinNames += ", ";
}
if (listClin.SelectedIndices[i] == 0)
{
clinNames += Lan.g(this, "Unassigned");
}
else
{
clinNames += Clinics.List[listClin.SelectedIndices[i] - 1].Description;
}
}
report.SubTitle.Add(clinNames);
}
}
report.SetColumn(this, 0, "Category", 150);
report.SetColumn(this, 1, "Code", 90);
report.SetColumn(this, 2, "Description", 180);
report.SetColumn(this, 3, "Quantity", 60, HorizontalAlignment.Right);
report.SetColumn(this, 4, "Average Fee", 110, HorizontalAlignment.Right);
report.SetColumn(this, 5, "Total Fees", 100, HorizontalAlignment.Right);
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (!checkBillTypesAll.Checked && listBillType.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one billing type must be selected.");
return;
}
if (!checkProvAll.Checked && listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (textDate.errorProvider1.GetError(textDate) != "")
{
MsgBox.Show(this, "Invalid date.");
return;
}
DateTime asOfDate = PIn.Date(textDate.Text);
//The aging report always show historical numbers based on the date entered.
Ledgers.ComputeAging(0, asOfDate, true);
ReportSimpleGrid report = new ReportSimpleGrid();
string cmd = "SELECT ";
if (PrefC.GetBool(PrefName.ReportsShowPatNum))
{
cmd += DbHelper.Concat("Cast(PatNum AS CHAR)", "'-'", "LName", "', '", "FName", "' '", "MiddleI");
}
else
{
cmd += DbHelper.Concat("LName", "', '", "FName", "' '", "MiddleI");
}
cmd += ",Bal_0_30,Bal_31_60,Bal_61_90,BalOver90"
+ ",BalTotal "
+ ",InsEst"
+ ",BalTotal-InsEst AS "; //\"$pat\" ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
cmd += "$pat ";
}
else //Oracle needs quotes.
{
cmd += "\"$pat\" ";
}
cmd += "FROM patient "
+ "WHERE ";
if (checkExcludeInactive.Checked)
{
cmd += "(patstatus != 2) AND ";
}
if (checkBadAddress.Checked)
{
cmd += "(zip !='') AND ";
}
if (checkOnlyNeg.Checked)
{
cmd += "BalTotal < '-.005' ";
}
else
{
if (radioAny.Checked)
{
cmd +=
"(Bal_0_30 > '.005' OR Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if (radio30.Checked)
{
cmd +=
"(Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if (radio60.Checked)
{
cmd +=
"(Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if (radio90.Checked)
{
cmd +=
"(BalOver90 > '.005'";
}
if (checkIncludeNeg.Checked)
{
cmd += " OR BalTotal < '-.005'";
}
cmd += ") ";
}
if (!checkBillTypesAll.Checked)
{
for (int i = 0; i < listBillType.SelectedIndices.Count; i++)
{
if (i == 0)
{
cmd += " AND (billingtype = ";
}
else
{
cmd += " OR billingtype = ";
}
cmd += POut.Long(DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].DefNum);
}
cmd += ") ";
}
if (!checkProvAll.Checked)
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i == 0)
{
cmd += " AND (PriProv = ";
}
else
{
cmd += " OR PriProv = ";
}
cmd += POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum);
}
cmd += ") ";
}
cmd += "ORDER BY LName,FName";
report.Query = cmd;
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
//Recompute aging in a non-historical way, so that the numbers are returned to the way they
//are normally used in other parts of the program.
Ledgers.RunAging();
//if(Prefs.UpdateString(PrefName.DateLastAging",POut.PDate(asOfDate,false))) {
// DataValid.SetInvalid(InvalidType.Prefs);
//}
report.Title = "AGING REPORT";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add("As of " + textDate.Text);
if (radioAny.Checked)
{
report.SubTitle.Add("Any Balance");
}
if (radio30.Checked)
{
report.SubTitle.Add("Over 30 Days");
}
if (radio60.Checked)
{
report.SubTitle.Add("Over 60 Days");
}
if (radio90.Checked)
{
report.SubTitle.Add("Over 90 Days");
}
if (checkBillTypesAll.Checked)
{
report.SubTitle.Add("All Billing Types");
}
else
{
string subt = DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[0]].ItemName;
for (int i = 1; i < listBillType.SelectedIndices.Count; i++)
{
subt += ", " + DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].ItemName;
}
report.SubTitle.Add(subt);
}
//report.InitializeColumns(8);
report.SetColumn(this, 0, "GUARANTOR", 160);
report.SetColumn(this, 1, "0-30 DAYS", 80, HorizontalAlignment.Right);
report.SetColumn(this, 2, "30-60 DAYS", 80, HorizontalAlignment.Right);
report.SetColumn(this, 3, "60-90 DAYS", 80, HorizontalAlignment.Right);
report.SetColumn(this, 4, "> 90 DAYS", 80, HorizontalAlignment.Right);
report.SetColumn(this, 5, "TOTAL", 85, HorizontalAlignment.Right);
report.SetColumn(this, 6, "-INS EST", 85, HorizontalAlignment.Right);
report.SetColumn(this, 7, "=PATIENT", 85, HorizontalAlignment.Right);
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (listPayType.SelectedIndices.Count == 0 && !checkIncludeIns.Checked)
{
MsgBox.Show(this, "Must either select a payment type and/or include insurance checks.");
return;
}
string whereProv = "(";
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i > 0)
{
whereProv += "OR ";
}
whereProv += "paysplit.ProvNum = '"
+ POut.PInt(Providers.List[listProv.SelectedIndices[i]].ProvNum) + "' ";
}
whereProv += ")";
string whereType = "(";
for (int i = 0; i < listPayType.SelectedIndices.Count; i++)
{
if (i > 0)
{
whereType += "OR ";
}
whereType += "payment.PayType = '"
+ POut.PInt(DefB.Short[(int)DefCat.PaymentTypes][listPayType.SelectedIndices[i]].DefNum) + "' ";
}
whereType += ")";
Queries.CurReport = new ReportOld();
Queries.CurReport.Query = "(SELECT "
+ "paysplit.DatePay AS mydate," //0. Date
+ "CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) AS plfname," //1. name
+ "' '," //2. Carrier. this is long so union won't get trunc.
+ "payment.PayType," //3. paytype
+ "provider.Abbr," //4. Prov
+ "payment.CheckNum," //5. CheckNum
+ "SUM(paysplit.SplitAmt) $amt, " //6. amt
+ "payment.PayNum " //7. PayNum. Not visible
+ "FROM payment,patient,provider,paysplit "
+ "WHERE ";
if (listPayType.SelectedIndices.Count == 0)
{
Queries.CurReport.Query += "1=0 "; //none
}
else
{
Queries.CurReport.Query +=
"payment.PayNum=paysplit.PayNum "
+ "AND patient.PatNum=paysplit.PatNum "
+ "AND provider.ProvNum=paysplit.ProvNum "
+ "AND " + whereProv + " "
+ "AND " + whereType + " "
+ "AND paysplit.DatePay >= " + POut.PDate(date1.SelectionStart) + " "
+ "AND paysplit.DatePay <= " + POut.PDate(date2.SelectionStart) + " ";
}
Queries.CurReport.Query += "GROUP BY payment.PayNum,patient.PatNum,provider.ProvNum)";
if (checkIncludeIns.Checked)
{
whereProv = "(";
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i > 0)
{
whereProv += "OR ";
}
whereProv += "claimproc.ProvNum = '"
+ POut.PInt(Providers.List[listProv.SelectedIndices[i]].ProvNum) + "' ";
}
whereProv += ")";
Queries.CurReport.Query +=
" UNION ("
+ "SELECT claimproc.DateCP AS mydate," //0. Date
+ "CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) AS plfname," //1. Name
+ "carrier.CarrierName," //2. Carrier
+ "''," //3. PayType
+ "provider.Abbr," //4. Prov
+ "claimpayment.CheckNum," //5. CheckNum
+ "SUM(claimproc.InsPayAmt), " //6. Amt
+ "claimproc.ClaimNum " //7. Num(not visible)
+ "FROM claimproc,insplan,patient,carrier,provider,claimpayment "
+ "WHERE claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum "
+ "AND provider.ProvNum=claimproc.ProvNum "
+ "AND claimproc.PlanNum = insplan.PlanNum "
+ "AND claimproc.PatNum = patient.PatNum "
+ "AND carrier.CarrierNum = insplan.CarrierNum "
+ "AND " + whereProv + " "
+ "AND (claimproc.Status=1 OR claimproc.Status=4) " //received or supplemental
+ "AND claimpayment.CheckDate >= " + POut.PDate(date1.SelectionStart) + " "
+ "AND claimpayment.CheckDate <= " + POut.PDate(date2.SelectionStart) + " ";
if (radioPatient.Checked) //by patient
{
Queries.CurReport.Query += "GROUP BY claimproc.ClaimNum,patient.PatNum,provider.ProvNum ";
}
else //by check
{
Queries.CurReport.Query += "GROUP BY claimproc.ClaimPaymentNum,provider.ProvNum ";
}
Queries.CurReport.Query +=
")"; //end of union
} //insurance section
Queries.CurReport.Query +=
//" ORDER BY mydate,PayType,plfname";//FIXME:UNION-ORDER-BY
" ORDER BY 1,4,2";
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "Daily Payments";
Queries.CurReport.SubTitle = new string[5];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d");
Queries.CurReport.SubTitle[2] = Lan.g(this, "Patient Payment Type(s): ");
if (listPayType.SelectedIndices.Count == listPayType.Items.Count)
{
Queries.CurReport.SubTitle[2] += Lan.g(this, "All");
}
else if (listPayType.SelectedIndices.Count == 0)
{
Queries.CurReport.SubTitle[2] += Lan.g(this, "None");
}
else
{
for (int i = 0; i < listPayType.SelectedIndices.Count; i++)
{
if (i > 0)
{
Queries.CurReport.SubTitle[2] += ", ";
}
Queries.CurReport.SubTitle[2]
+= DefB.Short[(int)DefCat.PaymentTypes][listPayType.SelectedIndices[i]].ItemName;
}
}
Queries.CurReport.SubTitle[3] = Lan.g(this, "Insurance Payments: ");
if (checkIncludeIns.Checked)
{
Queries.CurReport.SubTitle[3] += "Included";
}
else
{
Queries.CurReport.SubTitle[3] += "Not included";
}
Queries.CurReport.SubTitle[4] = Lan.g(this, "Providers: ");
if (listProv.SelectedIndices.Count == listProv.Items.Count)
{
Queries.CurReport.SubTitle[4] += Lan.g(this, "All");
}
else
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i > 0)
{
Queries.CurReport.SubTitle[4] += ", ";
}
Queries.CurReport.SubTitle[4] += Providers.List[listProv.SelectedIndices[i]].Abbr;
}
}
Queries.CurReport.ColPos = new int[9];
Queries.CurReport.ColCaption = new string[8];
Queries.CurReport.ColAlign = new HorizontalAlignment[8];
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 100;
Queries.CurReport.ColPos[2] = 200;
Queries.CurReport.ColPos[3] = 380;
Queries.CurReport.ColPos[4] = 470;
Queries.CurReport.ColPos[5] = 540;
Queries.CurReport.ColPos[6] = 640;
Queries.CurReport.ColPos[7] = 700;
Queries.CurReport.ColPos[8] = 900; //off the right edge
Queries.CurReport.ColCaption[0] = "Date";
Queries.CurReport.ColCaption[1] = "Patient Name";
Queries.CurReport.ColCaption[2] = "Carrier";
Queries.CurReport.ColCaption[3] = "Payment Type";
Queries.CurReport.ColCaption[4] = "Provider";
Queries.CurReport.ColCaption[5] = "Check #";
Queries.CurReport.ColCaption[6] = "Amount";
Queries.CurReport.ColCaption[7] = "Payment #"; //not shown
Queries.CurReport.ColAlign[6] = HorizontalAlignment.Right;
Queries.CurReport.ColAlign[7] = HorizontalAlignment.Right;
Queries.CurReport.Summary = new string[0];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
string whereProv = "(";
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i > 0)
{
whereProv += "OR ";
}
whereProv += "claimproc.ProvNum = '"
+ POut.PInt(Providers.List[listProv.SelectedIndices[i]].ProvNum) + "' ";
}
whereProv += ")";
Queries.CurReport = new ReportOld();
Queries.CurReport.Query = "SET @FromDate=" + POut.PDate(date1.SelectionStart) + ", @ToDate=" + POut.PDate(date2.SelectionStart) + ";";
Queries.CurReport.Query += @"SELECT claimproc.DateCP,
CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),
carrier.CarrierName,
provider.Abbr,
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 " + whereProv
+ @" AND (claimproc.Status=1 OR claimproc.Status=4) /*received or supplemental*/
AND claimproc.DateCP >= @FromDate
AND claimproc.DateCP <= @ToDate
GROUP BY claimproc.ClaimNum
ORDER BY claimproc.DateCP" ;
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "Daily Writeoffs";
Queries.CurReport.SubTitle = new string[2];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d");
Queries.CurReport.ColPos = new int[7];
Queries.CurReport.ColCaption = new string[6];
Queries.CurReport.ColAlign = new HorizontalAlignment[6];
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 120;
Queries.CurReport.ColPos[2] = 270;
Queries.CurReport.ColPos[3] = 495;
Queries.CurReport.ColPos[4] = 645;
Queries.CurReport.ColPos[5] = 720;
Queries.CurReport.ColPos[6] = 900; //off the right side
Queries.CurReport.ColCaption[0] = "Date";
Queries.CurReport.ColCaption[1] = "Patient Name";
Queries.CurReport.ColCaption[2] = "Carrier";
Queries.CurReport.ColCaption[3] = "Provider";
Queries.CurReport.ColCaption[4] = "Amount";
Queries.CurReport.ColCaption[5] = "";
Queries.CurReport.ColAlign[5] = HorizontalAlignment.Right;
Queries.CurReport.Summary = new string[0];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (textDateFrom.errorProvider1.GetError(textDateFrom) != "" ||
textDateTo.errorProvider1.GetError(textDateTo) != ""
)
{
MessageBox.Show(Lan.g(this, "Please fix data entry errors first."));
return;
}
if (listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (listProv.SelectedIndices[0] == 0 && listProv.SelectedIndices.Count > 1)
{
MsgBox.Show(this, "You cannot select 'all' providers as well as specific providers.");
return;
}
DateTime dateFrom = PIn.Date(textDateFrom.Text);
DateTime dateTo = PIn.Date(textDateTo.Text);
string whereProv = "";
if (listProv.SelectedIndices[0] != 0)
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereProv += " AND (";
}
else
{
whereProv += "OR ";
}
whereProv += "procedurelog.ProvNum = "
+ POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
}
whereProv += ") ";
}
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = @"SELECT referral.LName,referral.FName,
COUNT(DISTINCT refattach.PatNum) HowMany,
SUM(procedurelog.ProcFee) ""$HowMuch""";
if (checkAddress.Checked)
{
report.Query += ",referral.Title,referral.Address,referral.Address2,referral.City,"
+ "referral.ST,referral.Zip,referral.Specialty";
}
report.Query += @" FROM referral,refattach,procedurelog,patient
WHERE referral.ReferralNum=refattach.ReferralNum
AND procedurelog.PatNum=refattach.PatNum
AND procedurelog.PatNum=patient.PatNum
AND refattach.IsFrom=1
AND procedurelog.ProcStatus=2
AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " "
+ whereProv;
if (checkNewPat.Checked)
{
report.Query += "AND patient.DateFirstVisit >= " + POut.Date(dateFrom) + " "
+ "AND patient.DateFirstVisit <= " + POut.Date(dateTo) + " ";
}
report.Query += @"GROUP BY referral.ReferralNum
ORDER BY HowMany Desc";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Referral Analysis";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if (listProv.SelectedIndices[0] == 0)
{
report.SubTitle.Add(Lan.g(this, "All Providers"));
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
else if (listProv.SelectedIndices.Count == 1)
{
report.SubTitle.Add(Lan.g(this, "Prov: ") + ProviderC.ListShort[listProv.SelectedIndices[0] - 1].GetLongDesc());
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
else
{
//I'm too lazy to build a description for multiple providers as well as ensure that it fits the space.
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
if (checkAddress.Checked)
{
report.SetColumnPos(this, 0, "Last Name", 100);
report.SetColumnPos(this, 1, "First Name", 200);
report.SetColumnPos(this, 2, "Count", 270);
report.SetColumnPos(this, 3, "Production", 350, HorizontalAlignment.Right);
report.SetColumnPos(this, 4, "Title", 390);
report.SetColumnPos(this, 5, "Address", 490);
report.SetColumnPos(this, 6, "Add2", 530);
report.SetColumnPos(this, 7, "City", 590);
report.SetColumnPos(this, 8, "ST", 630);
report.SetColumnPos(this, 9, "Zip", 680);
report.SetColumnPos(this, 10, "Specialty", 880); //off the right side
}
else
{
report.SetColumnPos(this, 0, "Last Name", 100);
report.SetColumnPos(this, 1, "First Name", 200);
report.SetColumnPos(this, 2, "Count", 270);
report.SetColumnPos(this, 3, "Production", 350, HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (textDateFrom.errorProvider1.GetError(textDateFrom) != "" ||
textDateTo.errorProvider1.GetError(textDateTo) != ""
)
{
MessageBox.Show(Lan.g(this, "Please fix data entry errors first."));
return;
}
if (listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (listProv.SelectedIndices[0] == 0 && listProv.SelectedIndices.Count > 1)
{
MsgBox.Show(this, "You cannot select 'all' providers as well as specific providers.");
return;
}
DateTime dateFrom = PIn.Date(textDateFrom.Text);
DateTime dateTo = PIn.Date(textDateTo.Text);
string whereProv = "";
if (listProv.SelectedIndices[0] != 0)
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereProv += " WHERE (";
}
else
{
whereProv += "OR ";
}
whereProv += "patient.PriProv = "
+ POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
}
whereProv += ") ";
}
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = @"SET @pos=0;
SELECT @pos:=@pos+1 patCount,result.* FROM (SELECT dateFirstProc,patient.LName,patient.FName,"
+ DbHelper.Concat("referral.LName", "IF(referral.FName='','',',')", "referral.FName") + " refname,SUM(procedurelog.ProcFee) ";//\"$HowMuch\"";
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "$HowMuch";
}
else //Oracle needs quotes.
{
report.Query += "\"$HowMuch\"";
}
if (checkAddress.Checked)
{
report.Query += ",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip";
}
report.Query += @" FROM
(SELECT PatNum, MIN(ProcDate) dateFirstProc FROM procedurelog
WHERE ProcStatus=2 GROUP BY PatNum
HAVING dateFirstProc >= " + POut.Date(dateFrom) + " "
+ "AND DATE(dateFirstProc) <= " + POut.Date(dateTo) + " ) table1 "
+ @"INNER JOIN patient ON table1.PatNum=patient.PatNum
LEFT JOIN procedurelog ON patient.PatNum=procedurelog.PatNum AND procedurelog.ProcStatus=2
LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.IsFrom=1
AND refattach.ItemOrder=(SELECT MIN(ra.ItemOrder) FROM refattach ra WHERE ra.PatNum=refattach.PatNum AND ra.IsFrom=1)
LEFT JOIN referral ON referral.ReferralNum=refattach.ReferralNum "
+ whereProv;
report.Query += "GROUP BY patient.LName,patient.FName,patient.PatNum," + DbHelper.Concat("referral.LName", "IF(referral.FName='','',',')", "referral.FName");
if (checkAddress.Checked)
{
report.Query += ",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip ";
}
if (checkProd.Checked)
{
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "HAVING $HowMuch > 0 ";
}
else //Oracle needs quotes.
{
report.Query += "HAVING \"$HowMuch\" > 0 ";
}
}
report.Query += "ORDER BY dateFirstProc,patient.LName,patient.FName) result";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "New Patients";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if (listProv.SelectedIndices[0] == 0)
{
report.SubTitle.Add(Lan.g(this, "All Providers"));
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
else if (listProv.SelectedIndices.Count == 1)
{
report.SubTitle.Add(Lan.g(this, "Prov: ") + ProviderC.ListShort[listProv.SelectedIndices[0] - 1].GetLongDesc());
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
else
{
//I'm too lazy to build a description for multiple providers as well as ensure that it fits the space.
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
report.SetColumnPos(this, 0, "#", 40);
report.SetColumnPos(this, 1, "Date", 120);
report.SetColumnPos(this, 2, "Last Name", 210);
report.SetColumnPos(this, 3, "First Name", 300);
report.SetColumnPos(this, 4, "Referral", 380);
report.SetColumnPos(this, 5, "Production", 450, HorizontalAlignment.Right);
if (checkAddress.Checked)
{
report.SetColumnPos(this, 6, "Pref'd", 500);
report.SetColumnPos(this, 7, "Address", 570);
report.SetColumnPos(this, 8, "Add2", 630);
report.SetColumnPos(this, 9, "City", 680);
report.SetColumnPos(this, 10, "ST", 730);
report.SetColumnPos(this, 11, "Zip", 880); //off the right side
}
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}