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 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 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 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 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 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 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 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 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 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 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)
{
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)
{
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, 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)
{
/*
* 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 RunDaily(){
dateFrom=PIn.Date(textDateFrom.Text);
dateTo=PIn.Date(textDateTo.Text);
//Date
//PatientName
//Description
//Prov
//Clinic
//Production
//Adjustments
//Pt Income
//Ins Income
//last column is a unique id that is not displayed
string whereProv="";
if(!checkAllProv.Checked){
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]].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+="procedurelog.ClinicNum = 0 ";
}
else {
whereClin+="procedurelog.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
}
}
whereClin+=") ";
}
ReportSimpleGrid report=new ReportSimpleGrid();
//Procedures------------------------------------------------------------------------------
report.Query="(SELECT "
+DbHelper.DateColumn("procedurelog.ProcDate")+" procdate,"
+"CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) namelf,"
+"procedurecode.Descript,"
+"provider.Abbr,"
+"procedurelog.ClinicNum,"
+"procedurelog.ProcFee*(CASE procedurelog.UnitQty+procedurelog.BaseUnits WHEN 0 THEN 1 ELSE procedurelog.UnitQty+procedurelog.BaseUnits END)-IFNULL(SUM(claimproc.WriteOff),0) ";//if no writeoff, then subtract 0
if(DataConnection.DBtype==DatabaseType.MySql) {
report.Query+="$fee,"
+"0 $Adj,"
+"0 $InsW,"
+"0 $PtInc,"
+"0 $InsInc,";
}
else {//Oracle needs quotes.
report.Query+="\"$fee\","
+"0 \"$Adj\","
+"0 \"$InsW\","
+"0 \"$PtInc\","
+"0 \"$InsInc\",";
}
report.Query+="procedurelog.ProcNum "
+"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.CodeNum=procedurecode.CodeNum "
+"AND provider.ProvNum=procedurelog.ProvNum "
+whereProv
+whereClin
+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" >= " +POut.Date(dateFrom)+" "
+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" <= " +POut.Date(dateTo)+" "
+"GROUP BY procedurelog.ProcNum "
+") UNION ALL (";
//Adjustments-----------------------------------------------------------------------------
whereProv="";
if(!checkAllProv.Checked) {
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i==0){
whereProv+=" AND (";
}
else{
whereProv+="OR ";
}
whereProv+="adjustment.ProvNum = "+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
}
whereProv+=") ";
}
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+="adjustment.ClinicNum = 0 ";
}
else {
whereClin+="adjustment.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
}
}
whereClin+=") ";
}
report.Query+="SELECT "
+"adjustment.AdjDate,"
+"CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),"
+"definition.ItemName,"
+"provider.Abbr,"
+"adjustment.ClinicNum,"
+"0,"
+"adjustment.AdjAmt,"
+"0,"
+"0,"
+"0,"
+"adjustment.AdjNum "
+"FROM adjustment,patient,definition,provider "
+"WHERE adjustment.AdjType=definition.DefNum "
+"AND provider.ProvNum=adjustment.ProvNum "
+"AND patient.PatNum=adjustment.PatNum "
+whereProv
+whereClin
+"AND adjustment.AdjDate >= "+POut.Date(dateFrom)+" "
+"AND adjustment.AdjDate <= "+POut.Date(dateTo)
+") UNION ALL (";
//Insurance Writeoff----------------------------------------------------------
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+=") ";
}
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+=") ";
}
if(radioWriteoffPay.Checked){
report.Query+="SELECT claimproc.DateCP,"
+"CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),"
+"CONCAT(CONCAT(procedurecode.AbbrDesc,' '),carrier.CarrierName),"//AbbrDesc might be null, which is ok.
+"provider.Abbr,"
+"claimproc.ClinicNum,"
+"0,"
+"0,"
+"-SUM(claimproc.WriteOff),"
+"0,"
+"0,"
+"claimproc.ClaimNum "
+"FROM claimproc "
+"LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
+"LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
+"LEFT JOIN insplan ON insplan.PlanNum = claimproc.PlanNum "
+"LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
+"LEFT JOIN procedurelog ON procedurelog.ProcNum=claimproc.ProcNum "
+"LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum "
+"WHERE (claimproc.Status=1 OR claimproc.Status=4) "//received or supplemental
+whereProv
+whereClin
+"AND claimproc.WriteOff > '.0001' "
+"AND claimproc.DateCP >= "+POut.Date(dateFrom)+" "
+"AND claimproc.DateCP <= "+POut.Date(dateTo)+" ";
}
else{
report.Query+="SELECT claimproc.ProcDate,"
+"CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),"
+"CONCAT(CONCAT(procedurecode.AbbrDesc,' '),carrier.CarrierName),"
+"provider.Abbr,"
+"claimproc.ClinicNum,"
+"0,"
+"0,"
+"-SUM(claimproc.WriteOff),"
+"0,"
+"0,"
+"claimproc.ClaimNum "
+"FROM claimproc "
+"LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
+"LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
+"LEFT JOIN insplan ON insplan.PlanNum = claimproc.PlanNum "
+"LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
+"LEFT JOIN procedurelog ON procedurelog.ProcNum=claimproc.ProcNum "
+"LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum "
+"WHERE (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) "//received or supplemental or notreceived
+whereProv
+whereClin
+"AND claimproc.WriteOff > '.0001' "
+"AND claimproc.ProcDate >= "+POut.Date(dateFrom)+" "
+"AND claimproc.ProcDate <= "+POut.Date(dateTo)+" ";
}
report.Query+="GROUP BY claimproc.ClaimProcNum"
+") UNION ALL (";
//Patient Income------------------------------------------------------------------------------
whereProv="";
if(!checkAllProv.Checked) {
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i==0){
whereProv+=" AND (";
}
else{
whereProv+="OR ";
}
whereProv+="paysplit.ProvNum = "
+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
}
whereProv+=") ";
}
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+="paysplit.ClinicNum = 0 ";
}
else {
whereClin+="paysplit.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
}
}
whereClin+=") ";
}
report.Query+="SELECT "
+"paysplit.DatePay,"
//+"GROUP_CONCAT(DISTINCT CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI)),"
+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+","
+"definition.ItemName,"
//+"GROUP_CONCAT(DISTINCT provider.Abbr),"
+"provider.Abbr,"
+"paysplit.ClinicNum,"
+"0,"
+"0,"
+"0,"
+"SUM(paysplit.SplitAmt),"
+"0,"
+"payment.PayNum "
+"FROM paysplit "//can't do cartesian join because income transfers would be excluded
+"LEFT JOIN payment ON payment.PayNum=paysplit.PayNum "
+"LEFT JOIN patient ON patient.PatNum=paysplit.PatNum "
+"LEFT JOIN provider ON provider.ProvNum=paysplit.ProvNum "
+"LEFT JOIN definition ON payment.PayType=definition.DefNum "
//notice that patient and prov are accurate, but if more than one, then only one shows//should be 'fixed'
+"WHERE payment.PayDate >= "+POut.Date(dateFrom)+" "
+"AND payment.PayDate <= "+POut.Date(dateTo)+" "
+whereProv
+whereClin
//+"GROUP BY payment.PayNum"
+"GROUP BY paysplit.PatNum,paysplit.ProvNum,paysplit.ClinicNum,PayType,paysplit.DatePay"
+") UNION ALL (";
//Insurance Income----------------------------------------------------------------------------
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+=") ";
}
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+=") ";
}
report.Query+="SELECT "
+"claimpayment.CheckDate,"
+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+","
+"carrier.CarrierName,"
+"provider.Abbr,"
+"claimproc.ClinicNum,"
+"0,"
+"0,"
+"0,"
+"0,"
+"SUM(claimproc.InsPayAmt),"
+"claimproc.ClaimNum "
+"FROM claimproc,insplan,patient,carrier,provider,claimpayment "
//claimpayment date is used because DateCP was not forced to be the same until version 3.0
+"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 "
+whereProv
+whereClin
+"AND (claimproc.Status=1 OR claimproc.Status=4) "//received or supplemental
+"AND claimpayment.CheckDate >= "+POut.Date(dateFrom)+" "
+"AND claimpayment.CheckDate <= "+POut.Date(dateTo)+" "
//+"GROUP BY claimproc.ClaimNum"
+"GROUP BY claimproc.PatNum,claimproc.ProvNum,claimproc.PlanNum,claimproc.ClinicNum,claimpayment.CheckDate"//same as claimproc.DateCP
+") ORDER BY "+DbHelper.UnionOrderBy("procdate",1)+","+DbHelper.UnionOrderBy("namelf",2);
//MessageBox.Show(report.Query);
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
DataTable table=report.GetTempTable();
report.TableQ=new DataTable(null);
int colI=10;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
colI=11;
}
for(int i=0;i<colI;i++) { //add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
decimal[] colTotals=new decimal[report.ColTotal.Length];
DataRow row;
Decimal dbl;
for(int i=0;i<table.Rows.Count;i++) {
row = report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
row[0]=PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
row[1]=table.Rows[i][1].ToString();//name
row[2]=table.Rows[i][2].ToString();//desc
row[3]=table.Rows[i][3].ToString();//prov
colI=4;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
row[colI]=Clinics.GetDesc(PIn.Long(table.Rows[i][4].ToString()));//clinic
colI++;
}
dbl=PIn.Decimal(table.Rows[i][5].ToString());//Prod
row[colI]=dbl.ToString("n");
colTotals[colI]+=dbl;
colI++;
dbl=PIn.Decimal(table.Rows[i][6].ToString());//Adj
row[colI]=dbl.ToString("n");
colTotals[colI]+=dbl;
colI++;
dbl=PIn.Decimal(table.Rows[i][7].ToString());//Writeoff
row[colI]=dbl.ToString("n");
colTotals[colI]+=dbl;
colI++;
dbl=PIn.Decimal(table.Rows[i][8].ToString());//PtInc
row[colI]=dbl.ToString("n");
colTotals[colI]+=dbl;
colI++;
dbl=PIn.Decimal(table.Rows[i][9].ToString());//InsInc
row[colI]=dbl.ToString("n");
colTotals[colI]+=dbl;
colI++;
row[colI]=table.Rows[i][10].ToString();
report.TableQ.Rows.Add(row);
}
for(int i=0;i<colTotals.Length;i++){
report.ColTotal[i]=PIn.Decimal(colTotals[i].ToString("n"));
}
FormQuery2.ResetGrid();
//FormQuery2.SubmitReportQuery();
report.Title="Daily Production and Income";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.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)) {
report.IsLandscape=true;
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",80);
report.SetColumn(this,1,"Patient Name",130);
report.SetColumn(this,2,"Description",165);
report.SetColumn(this,3,"Prov",55);
colI=4;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
report.SetColumn(this,colI,"Clinic",70);
colI++;
}
report.SetColumn(this,colI,"Production",65,HorizontalAlignment.Right);
colI++;
report.SetColumn(this,colI,"Adjust",70,HorizontalAlignment.Right);
colI++;
report.SetColumn(this,colI,"Writeoff",65,HorizontalAlignment.Right);
colI++;
report.SetColumn(this,colI,"Pt Income",65,HorizontalAlignment.Right);
colI++;
report.SetColumn(this,colI,"Ins Income",65,HorizontalAlignment.Right);
colI++;
report.SetColumn(this,colI,"",540,HorizontalAlignment.Right);
decimal total;
if(PrefC.GetBool(PrefName.EasyNoClinics)) {
total=colTotals[4]+colTotals[5]+colTotals[6];
}
else {
total=colTotals[5]+colTotals[6]+colTotals[7];
}
report.Summary.Add(Lan.g(this,"Total Production (Production + Adjustments - Writeoffs):")+" "+total.ToString("c"));
report.Summary.Add("");
if(PrefC.GetBool(PrefName.EasyNoClinics)) {
total=colTotals[7]+colTotals[8];
}
else {
total=colTotals[8]+colTotals[9];
}
report.Summary.Add(Lan.g(this,"Total Income (Pt Income + Ins Income):")+" "+total.ToString("c"));
FormQuery2.ShowDialog();
}
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) {
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+=") ";
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
cmd+=" AND ClinicNum IN(";
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i>0) {
cmd+=",";
}
if(Security.CurUser.ClinicIsRestricted) {
cmd+=POut.Long(_listClinics[listClin.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
}
else {
if(listClin.SelectedIndices[i]==0) {
cmd+="0";
}
else {
cmd+=POut.Long(_listClinics[listClin.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
}
}
}
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 OF ACCOUNTS RECEIVABLE 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(textDateFrom.errorProvider1.GetError(textDateFrom)!=""
|| textDateTo.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;
}
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 BETWEEN "+POut.Date(dateFrom)+" AND "+POut.Date(dateTo)+" "
+"AND adjustment.AdjType = '"+POut.Long(PrefC.GetLong(PrefName.FinanceChargeAdjustmentType))+"'";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
report.Title=Lans.g(this,"FINANCE CHARGE REPORT");
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(textDateFrom.Text+" - "+textDateTo.Text);
report.SetColumn(this,0,"Patient Name",180);
report.SetColumn(this,1,"Amount",100,HorizontalAlignment.Right);
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender,System.EventArgs e) {
if(date2.SelectionStart<date1.SelectionStart) {
MsgBox.Show(this,"End date cannot be before start date.");
return;
}
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;
}
}
if(listType.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one type 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+="adjustment.ProvNum = "+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
}
whereProv+=") ";
}
string whereClin="";
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
whereClin+=" AND adjustment.ClinicNum IN(";
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i>0) {
whereClin+=",";
}
if(Security.CurUser.ClinicIsRestricted) {
whereClin+=POut.Long(_listClinics[listClin.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
}
else {
if(listClin.SelectedIndices[i]==0) {
whereClin+="0";
}
else {
whereClin+=POut.Long(_listClinics[listClin.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
}
}
}
whereClin+=") ";
}
string whereType="(";
for(int i=0;i<listType.SelectedIndices.Count;i++) {
if(i>0) {
whereType+="OR ";
}
whereType+="adjustment.AdjType = '"
+POut.Long(DefC.Short[(int)DefCat.AdjTypes][listType.SelectedIndices[i]].DefNum)+"' ";
}
whereType+=")";
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query="SELECT adjustment.AdjDate,"
+DbHelper.Concat("patient.LName","', '","patient.FName","', '","patient.MiddleI")+","
+"adjustment.ProvNum,adjustment.ClinicNum,"
+"definition.ItemName,adjustment.AdjNote,adjustment.AdjAmt FROM "
+"adjustment,patient,definition WHERE adjustment.AdjType=definition.DefNum "
+"AND patient.PatNum=adjustment.PatNum "
+whereProv
+whereClin
+"AND "+whereType+" "
+"AND adjustment.AdjDate >= "+POut.Date(date1.SelectionStart)+" "
+"AND adjustment.AdjDate <= "+POut.Date(date2.SelectionStart);
report.Query += " ORDER BY adjustment.AdjDate";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
DataTable table=report.GetTempTable();
report.TableQ=new DataTable(null);
int colI=6;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
colI=7;
}
for(int i=0;i<colI;i++) { //add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
DataRow row;
decimal dec;
for(int i=0;i<table.Rows.Count;i++) {
row = report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
row[0]=PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
row[1]=table.Rows[i][1].ToString();//name
row[2]=Providers.GetAbbr(PIn.Long(table.Rows[i][2].ToString()));//prov
colI=3;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
row[colI]=Clinics.GetDesc(PIn.Long(table.Rows[i][3].ToString()));//clinic
colI++;
}
row[colI]=table.Rows[i][4].ToString();//Type
colI++;
row[colI]=table.Rows[i][5].ToString();//Note
colI++;
dec=PIn.Decimal(table.Rows[i][6].ToString());//Amount
row[colI]=dec.ToString("n");
report.ColTotal[colI]+=dec;
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();
report.Title="Daily Adjustments";
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(Security.CurUser.ClinicIsRestricted) {
clinNames+=_listClinics[listClin.SelectedIndices[i]].Description;
}
else {
if(listClin.SelectedIndices[i]==0) {
clinNames+=Lan.g(this,"Unassigned");
}
else {
clinNames+=_listClinics[listClin.SelectedIndices[i]-1].Description;//Minus 1 from the selected index
}
}
}
report.SubTitle.Add(clinNames);
}
}
report.SetColumn(this,0,"Date",90);
report.SetColumn(this,1,"Patient Name",130);
report.SetColumn(this,2,"Prov",60);
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
report.SetColumn(this,3,"Clinic",70);
report.SetColumn(this,4,"Adjustment Type",150);
report.SetColumn(this,5,"Note",150);
report.SetColumn(this,6,"Amount",75,HorizontalAlignment.Right);
}
else {
report.SetColumn(this,3,"Adjustment Type",150);
report.SetColumn(this,4,"Note",150);
report.SetColumn(this,5,"Amount",75,HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
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='000' OR ResultCode='010' THEN Amount ELSE Amount=0 END AS Amount "
+"FROM xchargetransaction "
+"WHERE DATE(TransactionDateTime) BETWEEN "+POut.Date(date1.SelectionStart)+" AND "+POut.Date(date2.SelectionStart)+" "
+"AND TransType!='CCVoid'";
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 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) {
if(date2.SelectionStart<date1.SelectionStart) {
MsgBox.Show(this,"End date cannot be before start date.");
return;
}
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 ";
if(!checkMedical.Checked) {
//Users would have no way to see why patients are being excluded from their billing list(s) without this check box when using the 'Exclude if insurance pending' option.
report.Query+="AND insplan.IsMedical=0 ";
}
report.Query+="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 butPayments_Click(object sender,EventArgs e) {
Cursor=Cursors.WaitCursor;
string paymentType=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,PayType "
+"FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum "
//Must be DateEntry here. PayDate will not work with recurring charges
+"WHERE PayType="+paymentType+" AND (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(ReportSimpleGrid report) {
//added Procnum to retrieve all codes
report.Query="SELECT procedurelog.ProcDate,"
+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+" "
+"AS plfname, procedurecode.ProcCode,"
+"procedurelog.ToothNum,procedurecode.Descript,provider.Abbr,"
+"procedurelog.ClinicNum,"
+"procedurelog.ProcFee-IFNULL(SUM(claimproc.WriteOff),0) ";//\"$fee\" " //if no writeoff, then subtract 0
if(DataConnection.DBtype==DatabaseType.MySql) {
report.Query+="$fee ";
}
else {//Oracle needs quotes.
report.Query+="\"$fee\" ";
}
report.Query+="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.CodeNum=procedurecode.CodeNum "
+"AND provider.ProvNum=procedurelog.ProvNum "
+whereProv
+whereClin
+"AND procedurecode.ProcCode LIKE '%"+POut.String(textCode.Text)+"%' "
+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" >= " +POut.Date(date1.SelectionStart)+" "
+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" <= " +POut.Date(date2.SelectionStart)+" "
+"GROUP BY procedurelog.ProcNum "
+"ORDER BY "+DbHelper.DateColumn("procedurelog.ProcDate")+",plfname,procedurecode.ProcCode,ToothNum";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
DataTable table=report.GetTempTable();
report.TableQ=new DataTable(null);
int colI=7;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
colI=8;
}
for(int i=0;i<colI;i++) { //add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
DataRow row;
decimal dec=0;
decimal total=0;
for(int i=0;i<table.Rows.Count;i++) {
row = report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
row[0]=PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
row[1]=table.Rows[i][1].ToString();//name
row[2]=table.Rows[i][2].ToString();//adacode
row[3]=Tooth.ToInternat(table.Rows[i][3].ToString());//tooth
row[4]=table.Rows[i][4].ToString();//descript
row[5]=table.Rows[i][5].ToString();//prov
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
row[6]=Clinics.GetDesc(PIn.Long(table.Rows[i][6].ToString()));//clinic
dec=PIn.Decimal(table.Rows[i][7].ToString());//fee
row[7]=dec.ToString("n");
}
else {
dec=PIn.Decimal(table.Rows[i][7].ToString());//fee
row[6]=dec.ToString("n");
}
total+=dec;
report.TableQ.Rows.Add(row);
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
report.ColTotal[7]=total;
}
else {
report.ColTotal[6]=total;
}
FormQuery2.ResetGrid();
report.Title="Daily Procedures";
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",80);
report.SetColumn(this,1,"Patient Name",130);
report.SetColumn(this,2,"ADA Code",75);
report.SetColumn(this,3,"Tooth",45);
report.SetColumn(this,4,"Description",200);
report.SetColumn(this,5,"Provider",50);
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
report.SetColumn(this,6,"Clinic",70);
report.SetColumn(this,7,"Fee",90,HorizontalAlignment.Right);
}
else{
report.SetColumn(this,6,"Fee",90,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 CreateIndividual(ReportSimpleGrid report)
{
//added Procnum to retrieve all codes
report.Query = "SELECT procedurelog.ProcDate,"
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + " "
+ "AS plfname, procedurecode.ProcCode,"
+ "procedurelog.ToothNum,procedurecode.Descript,provider.Abbr,"
+ "procedurelog.ClinicNum,"
+ "procedurelog.ProcFee-IFNULL(SUM(claimproc.WriteOff),0) "; //\"$fee\" " //if no writeoff, then subtract 0
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "$fee ";
}
else //Oracle needs quotes.
{
report.Query += "\"$fee\" ";
}
report.Query += "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.CodeNum=procedurecode.CodeNum "
+ "AND provider.ProvNum=procedurelog.ProvNum "
+ whereProv
+ whereClin
+ "AND procedurecode.ProcCode LIKE '%" + POut.String(textCode.Text) + "%' "
+ "AND " + DbHelper.DateColumn("procedurelog.ProcDate") + " >= " + POut.Date(date1.SelectionStart) + " "
+ "AND " + DbHelper.DateColumn("procedurelog.ProcDate") + " <= " + POut.Date(date2.SelectionStart) + " "
+ "GROUP BY procedurelog.ProcNum "
+ "ORDER BY " + DbHelper.DateColumn("procedurelog.ProcDate") + ",plfname,procedurecode.ProcCode,ToothNum";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
DataTable table = report.GetTempTable();
report.TableQ = new DataTable(null);
int colI = 7;
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
colI = 8;
}
for (int i = 0; i < colI; i++) //add columns
{
report.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns
}
report.InitializeColumns();
DataRow row;
double dbl = 0;
for (int i = 0; i < table.Rows.Count; i++)
{
row = report.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ
row[0] = PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
row[1] = table.Rows[i][1].ToString(); //name
row[2] = table.Rows[i][2].ToString(); //adacode
row[3] = Tooth.ToInternat(table.Rows[i][3].ToString()); //tooth
row[4] = table.Rows[i][4].ToString(); //descript
row[5] = table.Rows[i][5].ToString(); //prov
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
row[6] = Clinics.GetDesc(PIn.Long(table.Rows[i][6].ToString())); //clinic
dbl = PIn.Double(table.Rows[i][7].ToString()); //fee
row[7] = dbl.ToString("n");
report.ColTotal[7] += dbl;
}
else
{
dbl = PIn.Double(table.Rows[i][7].ToString()); //fee
row[6] = dbl.ToString("n");
report.ColTotal[6] += dbl;
}
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();
report.Title = "Daily Procedures";
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", 80);
report.SetColumn(this, 1, "Patient Name", 130);
report.SetColumn(this, 2, "ADA Code", 75);
report.SetColumn(this, 3, "Tooth", 45);
report.SetColumn(this, 4, "Description", 200);
report.SetColumn(this, 5, "Provider", 50);
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
report.SetColumn(this, 6, "Clinic", 70);
report.SetColumn(this, 7, "Fee", 90, HorizontalAlignment.Right);
}
else
{
report.SetColumn(this, 6, "Fee", 90, HorizontalAlignment.Right);
}
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 butOK_Click(object sender, EventArgs e)
{
if (textDateStart.errorProvider1.GetError(textDateStart) != "")
{
MsgBox.Show(this, "Please fix data entry errors first.");
return;
}
if (textDateStart.Text == "")
{
MsgBox.Show(this, "Please enter a start date.");
return;
}
if (textDateEnd.errorProvider1.GetError(textDateEnd) != "")
{
MsgBox.Show(this, "Please fix data entry errors first.");
return;
}
if (textDateEnd.Text == "")
{
MsgBox.Show(this, "Please enter an end date.");
return;
}
if (gridCourses.SelectedIndices.Length < 1)
{
MsgBox.Show(this, "At least one course must be selected to run a report. Please select a row from the course grid.");
return;
}
if (gridInstructors.SelectedIndices.Length < 1)
{
MsgBox.Show(this, "At least one instructor must be selected to run a report. Please select a row from the instructor grid.");
return;
}
if (gridStudents.SelectedIndices.Length < 1)
{
MsgBox.Show(this, "At least one student must be selected to run a report. Please select a row from the student grid.");
return;
}
DateTime dateStart = PIn.Date(textDateStart.Text);
DateTime dateEnd = PIn.Date(textDateEnd.Text);
string whereCourses = "";
if (!checkAllCourses.Checked)
{
for (int i = 0; i < gridCourses.SelectedIndices.Length; i++)
{
if (i == 0)
{
whereCourses += " AND evaluation.SchoolCourseNum IN(";
}
whereCourses += gridCourses.Rows[gridCourses.SelectedIndices[i]].Tag;
if (i != gridCourses.SelectedIndices.Length - 1)
{
whereCourses += ",";
}
}
whereCourses += ")";
}
string whereInstructors = "";
if (!checkAllInstructors.Checked)
{
for (int i = 0; i < gridInstructors.SelectedIndices.Length; i++)
{
if (i == 0)
{
whereInstructors += " AND evaluation.InstructNum IN(";
}
whereInstructors += gridInstructors.Rows[gridInstructors.SelectedIndices[i]].Tag;
if (i != gridInstructors.SelectedIndices.Length - 1)
{
whereInstructors += ",";
}
}
whereInstructors += ")";
}
//No checkbox for students
string whereStudents = " AND evaluation.StudentNum IN(";
for (int i = 0; i < gridStudents.SelectedIndices.Length; i++)
{
whereStudents += gridStudents.Rows[gridStudents.SelectedIndices[i]].Tag;
if (i != gridStudents.SelectedIndices.Length - 1)
{
whereStudents += ",";
}
}
whereStudents += ")";
ReportSimpleGrid report = new ReportSimpleGrid();
//Evaluations------------------------------------------------------------------------------
report.Query = "SELECT " + DbHelper.Concat("students.LName", "', '", "students.FName") + " StudentName,evaluation.DateEval,"
+ "courses.CourseID," + DbHelper.Concat("instructors.LName", "', '", "instructors.FName") + " InstructorName,"
+ "evaluation.EvalTitle,gradeScales.ScaleType,evaluation.OverallGradeShowing,evaluation.OverallGradeNumber";
report.Query += " FROM evaluation"
+ " INNER JOIN provider students ON evaluation.StudentNum=students.ProvNum"
+ " INNER JOIN provider instructors ON evaluation.InstructNum=instructors.ProvNum"
+ " INNER JOIN gradingscale gradeScales ON evaluation.GradingScaleNum=gradeScales.GradingScaleNum"
+ " INNER JOIN schoolcourse courses ON evaluation.SchoolCourseNum=courses.SchoolCourseNum"
+ " WHERE evaluation.DateEval BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd)
+ whereCourses
+ whereInstructors
+ whereStudents
+ " ORDER BY StudentName,evaluation.DateEval";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
DataTable table = report.GetTempTable();
report.TableQ = new DataTable();
int colI = 10;
for (int i = 0; i < colI; i++) //add columns
{
report.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns
}
report.InitializeColumns();
DataRow row;
for (int i = 0; i < table.Rows.Count; i++)
{
row = report.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ
row[0] = table.Rows[i]["StudentName"].ToString();
row[1] = PIn.Date(table.Rows[i]["DateEval"].ToString()).ToShortDateString();
row[2] = table.Rows[i]["CourseID"].ToString();
row[3] = table.Rows[i]["InstructorName"].ToString();
row[4] = table.Rows[i]["EvalTitle"].ToString();
switch ((EnumScaleType)PIn.Int(table.Rows[i]["ScaleType"].ToString()))
{
case EnumScaleType.PickList:
row[5] = Enum.GetName(typeof(EnumScaleType), (int)EnumScaleType.PickList);
break;
case EnumScaleType.Percentage:
row[5] = Enum.GetName(typeof(EnumScaleType), (int)EnumScaleType.Percentage);
break;
case EnumScaleType.Weighted:
row[5] = Enum.GetName(typeof(EnumScaleType), (int)EnumScaleType.Weighted);
break;
}
row[6] = table.Rows[i]["OverallGradeShowing"].ToString();
row[7] = table.Rows[i]["OverallGradeNumber"].ToString();
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();
report.Title = Lan.g(this, "Course Average");
report.SubTitle.Add(dateStart.ToShortDateString() + " - " + dateEnd.ToShortDateString());
if (checkAllInstructors.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Instructors"));
}
if (checkAllCourses.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Courses"));
}
report.SetColumn(this, 0, "Student", 120);
report.SetColumn(this, 1, "Date", 80);
report.SetColumn(this, 2, "Course", 100);
report.SetColumn(this, 3, "Instructor", 120);
report.SetColumn(this, 4, "Evaluation", 90);
report.SetColumn(this, 5, "Scale Type", 90);
report.SetColumn(this, 6, "Grade Showing", 100);
report.SetColumn(this, 7, "Grade Number", 100);
FormQuery2.ShowDialog();
}
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)
{
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 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;
}
}
if (listType.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one type 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 += "adjustment.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 += "adjustment.ClinicNum = 0 ";
}
else
{
whereClin += "adjustment.ClinicNum = " + POut.Long(Clinics.List[listClin.SelectedIndices[i] - 1].ClinicNum) + " ";
}
}
whereClin += ") ";
}
string whereType = "(";
for (int i = 0; i < listType.SelectedIndices.Count; i++)
{
if (i > 0)
{
whereType += "OR ";
}
whereType += "adjustment.AdjType = '"
+ POut.Long(DefC.Short[(int)DefCat.AdjTypes][listType.SelectedIndices[i]].DefNum) + "' ";
}
whereType += ")";
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SELECT adjustment.AdjDate,"
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "', '", "patient.MiddleI") + ","
+ "adjustment.ProvNum,adjustment.ClinicNum,"
+ "definition.ItemName,adjustment.AdjNote,adjustment.AdjAmt FROM "
+ "adjustment,patient,definition WHERE adjustment.AdjType=definition.DefNum "
+ "AND patient.PatNum=adjustment.PatNum "
+ whereProv
+ whereClin
+ "AND " + whereType + " "
+ "AND adjustment.AdjDate >= " + POut.Date(date1.SelectionStart) + " "
+ "AND adjustment.AdjDate <= " + POut.Date(date2.SelectionStart);
report.Query += " ORDER BY adjustment.AdjDate";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
DataTable table = report.GetTempTable();
report.TableQ = new DataTable(null);
int colI = 6;
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
colI = 7;
}
for (int i = 0; i < colI; i++) //add columns
{
report.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns
}
report.InitializeColumns();
DataRow row;
double dbl;
for (int i = 0; i < table.Rows.Count; i++)
{
row = report.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ
row[0] = PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
row[1] = table.Rows[i][1].ToString(); //name
row[2] = Providers.GetAbbr(PIn.Long(table.Rows[i][2].ToString())); //prov
colI = 3;
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
row[colI] = Clinics.GetDesc(PIn.Long(table.Rows[i][3].ToString())); //clinic
colI++;
}
row[colI] = table.Rows[i][4].ToString(); //Type
colI++;
row[colI] = table.Rows[i][5].ToString(); //Note
colI++;
dbl = PIn.Double(table.Rows[i][6].ToString()); //Amount
row[colI] = dbl.ToString("n");
report.ColTotal[colI] += dbl;
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();
report.Title = "Daily Adjustments";
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", 90);
report.SetColumn(this, 1, "Patient Name", 130);
report.SetColumn(this, 2, "Prov", 60);
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
report.SetColumn(this, 3, "Clinic", 70);
report.SetColumn(this, 4, "Adjustment Type", 150);
report.SetColumn(this, 5, "Note", 150);
report.SetColumn(this, 6, "Amount", 75, HorizontalAlignment.Right);
}
else
{
report.SetColumn(this, 3, "Adjustment Type", 150);
report.SetColumn(this, 4, "Note", 150);
report.SetColumn(this, 5, "Amount", 75, 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 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,EventArgs e) {
if(textDateStart.errorProvider1.GetError(textDateStart)!="") {
MsgBox.Show(this,"Please fix data entry errors first.");
return;
}
if(textDateStart.Text=="") {
MsgBox.Show(this,"Please enter a start date.");
return;
}
if(textDateEnd.errorProvider1.GetError(textDateEnd)!="") {
MsgBox.Show(this,"Please fix data entry errors first.");
return;
}
if(textDateEnd.Text=="") {
MsgBox.Show(this,"Please enter an end date.");
return;
}
if(gridCourses.SelectedIndices.Length<1) {
MsgBox.Show(this,"At least one course must be selected to run a report. Please select a row from the course grid.");
return;
}
if(gridInstructors.SelectedIndices.Length<1) {
MsgBox.Show(this,"At least one instructor must be selected to run a report. Please select a row from the instructor grid.");
return;
}
if(gridStudents.SelectedIndices.Length<1) {
MsgBox.Show(this,"At least one student must be selected to run a report. Please select a row from the student grid.");
return;
}
DateTime dateStart=PIn.Date(textDateStart.Text);
DateTime dateEnd=PIn.Date(textDateEnd.Text);
string whereCourses="";
if(!checkAllCourses.Checked) {
for(int i=0;i<gridCourses.SelectedIndices.Length;i++) {
if(i==0) {
whereCourses+=" AND evaluation.SchoolCourseNum IN(";
}
whereCourses+=gridCourses.Rows[gridCourses.SelectedIndices[i]].Tag;
if(i!=gridCourses.SelectedIndices.Length-1) {
whereCourses+=",";
}
}
whereCourses+=")";
}
string whereInstructors="";
if(!checkAllInstructors.Checked) {
for(int i=0;i<gridInstructors.SelectedIndices.Length;i++) {
if(i==0) {
whereInstructors+=" AND evaluation.InstructNum IN(";
}
whereInstructors+=gridInstructors.Rows[gridInstructors.SelectedIndices[i]].Tag;
if(i!=gridInstructors.SelectedIndices.Length-1) {
whereInstructors+=",";
}
}
whereInstructors+=")";
}
//No checkbox for students
string whereStudents=" AND evaluation.StudentNum IN(";
for(int i=0;i<gridStudents.SelectedIndices.Length;i++) {
whereStudents+=gridStudents.Rows[gridStudents.SelectedIndices[i]].Tag;
if(i!=gridStudents.SelectedIndices.Length-1) {
whereStudents+=",";
}
}
whereStudents+=")";
ReportSimpleGrid report=new ReportSimpleGrid();
//Evaluations------------------------------------------------------------------------------
report.Query="SELECT "+DbHelper.Concat("students.LName","', '","students.FName")+" StudentName,evaluation.DateEval,"
+"courses.CourseID,"+DbHelper.Concat("instructors.LName","', '","instructors.FName")+" InstructorName,"
+"evaluation.EvalTitle,gradeScales.ScaleType,evaluation.OverallGradeShowing,evaluation.OverallGradeNumber";
report.Query+=" FROM evaluation"
+" INNER JOIN provider students ON evaluation.StudentNum=students.ProvNum"
+" INNER JOIN provider instructors ON evaluation.InstructNum=instructors.ProvNum"
+" INNER JOIN gradingscale gradeScales ON evaluation.GradingScaleNum=gradeScales.GradingScaleNum"
+" INNER JOIN schoolcourse courses ON evaluation.SchoolCourseNum=courses.SchoolCourseNum"
+" WHERE evaluation.DateEval BETWEEN "+POut.Date(dateStart)+" AND "+POut.Date(dateEnd)
+whereCourses
+whereInstructors
+whereStudents
+" ORDER BY StudentName,evaluation.DateEval";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
DataTable table=report.GetTempTable();
report.TableQ=new DataTable();
int colI=10;
for(int i=0;i<colI;i++) { //add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
DataRow row;
for(int i=0;i<table.Rows.Count;i++) {
row = report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
row[0]=table.Rows[i]["StudentName"].ToString();
row[1]=PIn.Date(table.Rows[i]["DateEval"].ToString()).ToShortDateString();
row[2]=table.Rows[i]["CourseID"].ToString();
row[3]=table.Rows[i]["InstructorName"].ToString();
row[4]=table.Rows[i]["EvalTitle"].ToString();
switch((EnumScaleType)PIn.Int(table.Rows[i]["ScaleType"].ToString())) {
case EnumScaleType.PickList:
row[5]=Enum.GetName(typeof(EnumScaleType),(int)EnumScaleType.PickList);
break;
case EnumScaleType.Percentage:
row[5]=Enum.GetName(typeof(EnumScaleType),(int)EnumScaleType.Percentage);
break;
case EnumScaleType.Weighted:
row[5]=Enum.GetName(typeof(EnumScaleType),(int)EnumScaleType.Weighted);
break;
}
row[6]=table.Rows[i]["OverallGradeShowing"].ToString();
row[7]=table.Rows[i]["OverallGradeNumber"].ToString();
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();
report.Title=Lan.g(this,"Course Average");
report.SubTitle.Add(dateStart.ToShortDateString()+" - "+dateEnd.ToShortDateString());
if(checkAllInstructors.Checked) {
report.SubTitle.Add(Lan.g(this,"All Instructors"));
}
if(checkAllCourses.Checked) {
report.SubTitle.Add(Lan.g(this,"All Courses"));
}
report.SetColumn(this,0,"Student",120);
report.SetColumn(this,1,"Date",80);
report.SetColumn(this,2,"Course",100);
report.SetColumn(this,3,"Instructor",120);
report.SetColumn(this,4,"Evaluation",90);
report.SetColumn(this,5,"Scale Type",90);
report.SetColumn(this,6,"Grade Showing",100);
report.SetColumn(this,7,"Grade Number",100);
FormQuery2.ShowDialog();
}