private void butPatByZip_Click(object sender,EventArgs e)
{
if(!DateIsValid()) {
return;
}
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query="SELECT SUBSTR(Zip,1,5) 'Zip Code',COUNT(*) 'Patients' "//Column headings "Zip Code" and "Patients" are provided by the USD 2010 Manual.
+"FROM patient pat "
+"WHERE "+DbHelper.Regexp("Zip","^[0-9]{5}")+" "//Starts with five numbers
+"AND PatNum IN ( "
+"SELECT PatNum FROM procedurelog "
+"WHERE ProcStatus="+POut.Int((int)ProcStat.C)+" "
+"AND DateEntryC >= "+POut.Date(DateFrom)+" "
+"AND DateEntryC <= "+POut.Date(DateTo)+") "
+"GROUP BY Zip "
+"HAVING COUNT(*) >= 10 "//Has more than 10 patients in that zip code for the given time frame.
+"ORDER BY Zip";
FormQuery FormQ=new FormQuery(report);
FormQ.IsReport=true;
FormQ.SubmitQuery();
FormQ.textQuery.Text=report.Query;
report.Title="Patients By ZIP CODE";
report.SubTitle.Add("From "+DateFrom.ToShortDateString()+" to "+DateTo.ToShortDateString());
report.Summary.Add("Other Zip Codes: "+Patients.GetZipOther(DateFrom,DateTo));
report.Summary.Add("Unknown Residence: "+Patients.GetZipUnknown(DateFrom,DateTo));
report.Summary.Add("TOTAL: "+Patients.GetPatCount(DateFrom,DateTo));
FormQ.ShowDialog();
}
private void butOK_Click(object sender, System.EventArgs e)
{
Cursor=Cursors.WaitCursor;
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=@"SELECT procedurelog.PatNum,"+DbHelper.Concat("patient.LName","', '","patient.FName")+@" patname,
procedurelog.ProcDate,
SUM(procedurelog.ProcFee) ""$sumfee"",
SUM((SELECT SUM(claimproc.InsPayAmt + claimproc.Writeoff) FROM claimproc WHERE claimproc.ProcNum=procedurelog.ProcNum)) AS
""$PaidAndWriteoff""
FROM procedurelog
LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
LEFT JOIN patient ON patient.PatNum=procedurelog.PatNum
WHERE procedurelog.ProcStatus=2/*complete*/
AND procedurelog.ProcFee > 0
GROUP BY procedurelog.PatNum,"+DbHelper.Concat("patient.LName","', '","patient.FName")+@",procedurelog.ProcDate
HAVING ROUND($sumfee,3) < ROUND($PaidAndWriteoff,3)
ORDER BY patname,ProcDate";
FormQuery FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
report.Title="INSURANCE OVERPAID REPORT";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this,0,"PatNum",60);
report.SetColumn(this,1,"Pat Name",150);
report.SetColumn(this,2,"Date",80);
report.SetColumn(this,3,"Fee",80,HorizontalAlignment.Right);
report.SetColumn(this,4,"InsPd+W/O",90,HorizontalAlignment.Right);
Cursor=Cursors.Default;
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void 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)
{
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=@"SELECT ScreenDate,ProvName,County,county.CountyCode,
GradeSchool,school.SchoolCode,PlaceService,GradeLevel,Age,Birthdate,Race,Gender,Urgency,
HasCaries,EarlyChildCaries,CariesExperience,ExistingSealants,NeedsSealants,MissingAllTeeth,
Comments FROM screen
LEFT JOIN school ON screen.GradeSchool=school.SchoolName
LEFT JOIN county ON screen.County=county.CountyName
WHERE ScreenDate >= "+POut.Date(date1.SelectionStart)+" "
+"AND ScreenDate <= " +POut.Date(date2.SelectionStart);
FormQuery FormQuery2=new FormQuery(report);
FormQuery2.textTitle.Text="RawProcedureData"+DateTime.Today.ToString("MMddyyyy");
//FormQuery2.IsReport=true;
//FormQuery2.SubmitReportQuery();
FormQuery2.SubmitQuery();
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)
{
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)
{
string bDate;
string eDate;
decimal rcvStart = 0;
decimal rcvProd = 0;
decimal rcvAdj = 0;
decimal rcvWriteoff = 0;
decimal rcvPayment = 0;
decimal rcvInsPayment = 0;
decimal runningRcv = 0;
decimal rcvDaily = 0;
decimal[] ColTotal=new decimal[8];
string wMonth;
string wYear;
string wDay = "01";
string wDate;
// Get the year / month and instert the 1st of the month for stop point for calculated running balance
wYear = date1.SelectionStart.Year.ToString();
wMonth = date1.SelectionStart.Month.ToString();
if(wMonth.Length<2) {
wMonth = "0" + wMonth;
}
wDate = wYear +"-"+ wMonth +"-"+ wDay;
ReportSimpleGrid report = new ReportSimpleGrid();
//
// Create temperary tables for sorting data
//
DataTable TableCharge = new DataTable(); //charges
DataTable TableCapWriteoff = new DataTable(); //capComplete writeoffs
DataTable TableInsWriteoff = new DataTable(); //ins writeoffs
DataTable TablePay = new DataTable(); //payments - Patient
DataTable TableIns = new DataTable(); //payments - Ins, added SPK
DataTable TableAdj = new DataTable(); //adjustments
//
// Main Loop: This will loop twice 1st loop gets running balance to start of month selected
// 2nd will break the numbers dow by day and calculate the running balances
//
for(int j = 0;j <= 1;j++) {
if(j == 0) {
bDate = "0001-01-01";
eDate = wDate;
}
else {
bDate = wDate;
eDate = POut.Date(date1.SelectionStart.AddDays(1)).Substring(1,10);// Needed because all Queries are < end date to get correct Starting AR
}
string whereProv;//used as the provider portion of the where clauses.
//each whereProv needs to be set up separately for each query
string whereProvx; //Extended for more than 4 names
whereProv = "";
if(listProv.SelectedIndices[0] != 0) {
for(int i = 0;i < listProv.SelectedIndices.Count;i++) {
if(i == 0) {
whereProv += " AND (";
}
else {
whereProv += "OR ";
}
whereProv += "procedurelog.ProvNum = "
+ POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
}
whereProv += ") ";
}
report.Query = "SELECT procedurelog.ProcDate, "
+ "SUM(procedurelog.ProcFee*(CASE procedurelog.UnitQty+procedurelog.BaseUnits WHEN 0 THEN 1 ELSE procedurelog.UnitQty+procedurelog.BaseUnits END)) "
+ "FROM procedurelog "
+ "WHERE procedurelog.ProcDate >= '" + bDate + "' "
+ "AND procedurelog.ProcDate < '" + eDate + "' "
+ "AND procedurelog.ProcStatus = '2' "
+ whereProv
+ "GROUP BY procedurelog.ProcDate "
+ "ORDER BY procedurelog.ProcDate";
TableCharge = report.GetTempTable();
whereProv = "";
if(listProv.SelectedIndices[0] != 0) {
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] - 1].ProvNum) + " ";
}
whereProv += ") ";
}
if(radioWriteoffPay.Checked) {
report.Query = "SELECT DateCP, "
+ "SUM(WriteOff) FROM claimproc WHERE "
+ "DateCP >= '" + bDate + "' "
+ "AND DateCP < '" + eDate + "' "
+ "AND Status = '7' "//CapComplete
+ whereProv
+ " GROUP BY DateCP "
+ "ORDER BY DateCP";
}
else {
report.Query = "SELECT ProcDate, "
+ "SUM(WriteOff) FROM claimproc WHERE "
+ "ProcDate >= '" + bDate + "' "
+ "AND ProcDate < '" + eDate + "' "
+ "AND Status = '7' "//CapComplete
+ whereProv
+ " GROUP BY ProcDate "
+ "ORDER BY ProcDate";
}
TableCapWriteoff = report.GetTempTable();
whereProv = "";
if(listProv.SelectedIndices[0] != 0) {
for(int i = 0;i < listProv.SelectedIndices.Count;i++) {
if(i == 0) {
whereProv += " AND (";
}
else {
whereProv += "OR ";
}
whereProv += "ProvNum = "
+ POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
}
whereProv += ") ";
}
if(radioWriteoffPay.Checked) {
report.Query = "SELECT DateCP, "
+ "SUM(WriteOff) FROM claimproc WHERE "
+ "DateCP >= '" + bDate + "' "
+ "AND DateCP < '" + eDate + "' "
+ "AND (Status = '1' OR Status = 4) "//Recieved or supplemental. Otherwise, it's only an estimate.
+ whereProv
+ " GROUP BY DateCP "
+ "ORDER BY DateCP";
}
else {
report.Query = "SELECT ProcDate, "
+ "SUM(WriteOff) FROM claimproc WHERE "
+ "ProcDate >= '" + bDate + "' "
+ "AND ProcDate < '" + eDate + "' "
+ "AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " //received or supplemental or notreceived
+ whereProv
+ " GROUP BY ProcDate "
+ "ORDER BY ProcDate";
}
TableInsWriteoff = report.GetTempTable();
whereProv = "";
if(listProv.SelectedIndices[0] != 0) {
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] - 1].ProvNum) + " ";
}
whereProv += ") ";
}
report.Query = "SELECT paysplit.DatePay,SUM(paysplit.splitamt) FROM paysplit "
+ "WHERE paysplit.IsDiscount = '0' "
+ "AND paysplit.DatePay >= '" + bDate + "' "
+ "AND paysplit.DatePay < '" + eDate + "' "
+ whereProv
+ " GROUP BY paysplit.DatePay ORDER BY DatePay";
TablePay = report.GetTempTable();
whereProv = "";
if(listProv.SelectedIndices[0] != 0) {
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] - 1].ProvNum) + " ";
}
whereProv += ") ";
}
report.Query = "SELECT DateCP,SUM(InsPayamt) "
+ "FROM claimproc WHERE "
+ "Status IN (1,4) "//received or supplemental
+ "AND DateCP >= '" + bDate + "' "
+ "AND DateCP < '" + eDate + "' "
+ whereProv
+ " GROUP BY DateCP ORDER BY DateCP";
TableIns = report.GetTempTable();
whereProv = "";
if(listProv.SelectedIndices[0] != 0) {
for(int i = 0;i < listProv.SelectedIndices.Count;i++) {
if(i == 0) {
whereProv += " AND (";
}
else {
whereProv += "OR ";
}
whereProv += "ProvNum = "
+ POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
}
whereProv += ") ";
}
report.Query = "SELECT adjdate, SUM(adjamt) FROM adjustment WHERE "
+ "adjdate >= '" + bDate + "' "
+ "AND adjdate < '" + eDate + "' "
+ whereProv
+ " GROUP BY adjdate ORDER BY adjdate";
TableAdj = report.GetTempTable();
//1st Loop Calculate running Accounts Receivable upto the 1st of the Month Selected
//2nd Loop Calculate the Daily Accounts Receivable upto the Date Selected
//Finaly Generate Report showing the breakdown upto the date specified with totals for what is on the report
if(j == 0) {
for(int k = 0;k < TableCharge.Rows.Count;k++) {
rcvProd += PIn.Decimal(TableCharge.Rows[k][1].ToString());
}
for(int k = 0;k < TableCapWriteoff.Rows.Count;k++) {
rcvWriteoff += PIn.Decimal(TableCapWriteoff.Rows[k][1].ToString());
}
for(int k = 0;k < TableInsWriteoff.Rows.Count;k++) {
rcvWriteoff += PIn.Decimal(TableInsWriteoff.Rows[k][1].ToString());
}
for(int k = 0;k < TablePay.Rows.Count;k++) {
rcvPayment += PIn.Decimal(TablePay.Rows[k][1].ToString());
}
for(int k = 0;k < TableIns.Rows.Count;k++) {
rcvInsPayment += PIn.Decimal(TableIns.Rows[k][1].ToString());
}
for(int k = 0;k < TableAdj.Rows.Count;k++) {
rcvAdj += PIn.Decimal(TableAdj.Rows[k][1].ToString());
}
TableCharge.Clear();
TableCapWriteoff.Clear();
TableInsWriteoff.Clear();
TablePay.Clear();
TableIns.Clear();
TableAdj.Clear();
rcvStart = (rcvProd + rcvAdj - rcvWriteoff) - (rcvPayment + rcvInsPayment);
}
else {
rcvAdj = 0;
rcvInsPayment = 0;
rcvPayment = 0;
rcvProd = 0;
rcvWriteoff = 0;
rcvDaily = 0;
runningRcv = rcvStart;
report.TableQ = new DataTable(null);//new table with 7 columns
for(int l = 0;l < 8;l++) { //add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
eDate = POut.Date(date1.SelectionStart).Substring(1,10);// Reset EndDate to Selected Date
DateTime[] dates = new DateTime[(PIn.Date(eDate) - PIn.Date(bDate)).Days + 1];
for(int i = 0;i < dates.Length;i++) {//usually 31 days in loop
dates[i] = PIn.Date(bDate).AddDays(i);
//create new row called 'row' based on structure of TableQ
DataRow row = report.TableQ.NewRow();
row[0] = dates[i].ToShortDateString();
for(int k = 0;k < TableCharge.Rows.Count;k++) {
if(dates[i] == (PIn.Date(TableCharge.Rows[k][0].ToString()))) {
rcvProd += PIn.Decimal(TableCharge.Rows[k][1].ToString());
}
}
for(int k = 0;k < TableCapWriteoff.Rows.Count;k++) {
if(dates[i] == (PIn.Date(TableCapWriteoff.Rows[k][0].ToString()))) {
rcvWriteoff += PIn.Decimal(TableCapWriteoff.Rows[k][1].ToString());
}
}
for(int k = 0;k < TableAdj.Rows.Count;k++) {
if(dates[i] == (PIn.Date(TableAdj.Rows[k][0].ToString()))) {
rcvAdj += PIn.Decimal(TableAdj.Rows[k][1].ToString());
}
}
for(int k = 0;k < TableInsWriteoff.Rows.Count;k++) {
if(dates[i] == (PIn.Date(TableInsWriteoff.Rows[k][0].ToString()))) {
rcvWriteoff += PIn.Decimal(TableInsWriteoff.Rows[k][1].ToString());
}
}
for(int k = 0;k < TablePay.Rows.Count;k++) {
if(dates[i] == (PIn.Date(TablePay.Rows[k][0].ToString()))) {
rcvPayment += PIn.Decimal(TablePay.Rows[k][1].ToString());
}
}
for(int k = 0;k < TableIns.Rows.Count;k++) {
if(dates[i] == (PIn.Date(TableIns.Rows[k][0].ToString()))) {
rcvInsPayment += PIn.Decimal(TableIns.Rows[k][1].ToString());
}
}
rcvDaily = (rcvProd + rcvAdj - rcvWriteoff) - (rcvPayment + rcvInsPayment);
runningRcv += (rcvProd + rcvAdj - rcvWriteoff) - (rcvPayment + rcvInsPayment);
row[1] = rcvProd.ToString("n");
row[2] = rcvAdj.ToString("n");
row[3] = rcvWriteoff.ToString("n");
row[4] = rcvPayment.ToString("n");
row[5] = rcvInsPayment.ToString("n");
row[6] = rcvDaily.ToString("n");
row[7] = runningRcv.ToString("n");
ColTotal[1] += rcvProd;
ColTotal[2] += rcvAdj;
ColTotal[3] += rcvWriteoff;
ColTotal[4] += rcvPayment;
ColTotal[5] += rcvInsPayment;
ColTotal[6] += rcvDaily;
ColTotal[7] = runningRcv;
report.TableQ.Rows.Add(row); //adds row to table Q
rcvAdj = 0;
rcvInsPayment = 0;
rcvPayment = 0;
rcvProd = 0;
rcvWriteoff = 0;
}
report.ColTotal[1]=PIn.Double(ColTotal[1].ToString("n"));
report.ColTotal[2]=PIn.Double(ColTotal[2].ToString("n"));
report.ColTotal[3]=PIn.Double(ColTotal[3].ToString("n"));
report.ColTotal[4]=PIn.Double(ColTotal[4].ToString("n"));
report.ColTotal[5]=PIn.Double(ColTotal[5].ToString("n"));
report.ColTotal[6]=PIn.Double(ColTotal[6].ToString("n"));
report.ColTotal[7]=PIn.Double(ColTotal[7].ToString("n"));
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.ResetGrid();
report.Title = "Receivables Breakdown Report";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
whereProv = "Report for: Practice";
whereProvx = "";
if(listProv.SelectedIndices[0] != 0) {
int nameCount = 0;
whereProv = "Report Includes: ";
for(int i = 0;i < listProv.SelectedIndices.Count;i++) {
if(nameCount < 3) {
whereProv += " "+ProviderC.ListShort[listProv.SelectedIndices[i] - 1].GetFormalName()+" /";
}
else {
whereProvx += " "+ProviderC.ListShort[listProv.SelectedIndices[i] - 1].GetFormalName()+" /";
}
nameCount += 1;
}
whereProv = whereProv.Substring(0,whereProv.Length-1);
if(whereProvx.Length > 0) {
whereProvx = whereProvx.Substring(0,whereProvx.Length-1);
}
}
report.SubTitle.Add(whereProv);
report.SubTitle.Add(whereProvx);
report.SetColumnPos(this,0,"Day",80);
report.SetColumnPos(this,1,"Production",160,HorizontalAlignment.Right);
report.SetColumnPos(this,2,"Adjustment",260,HorizontalAlignment.Right);
report.SetColumnPos(this,3,"Writeoff",360,HorizontalAlignment.Right);
report.SetColumnPos(this,4,"Payment",470,HorizontalAlignment.Right);
report.SetColumnPos(this,5,"InsPayment",570,HorizontalAlignment.Right);
report.SetColumnPos(this,6,"Daily A/R",680,HorizontalAlignment.Right);
report.SetColumnPos(this,7,"Ending A/R",779,HorizontalAlignment.Right);
report.Summary.Add(
Lan.g(this,"Receivables Calculation: (Production + Adjustments - Writeoffs) - (Payments + Insurance Payments)"));
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}//END If
}// END For Loop
}
private void butReport_Click(object sender,EventArgs e) {
if(!Security.IsAuthorized(Permissions.UserQuery)) {
return;
}
//Basically a preview of gridMain (every employee on one page), allow user to export as excel sheet or print it.
string query=ClockEvents.GetTimeCardManageCommand(DateStart,DateStop,true);//true to get extra columns for printing.
ReportSimpleGrid rsg=new ReportSimpleGrid();
rsg.Query=query;
FormQuery FormQ=new FormQuery(rsg);
FormQ.textQuery.Text=query;
FormQ.SubmitQuery();
FormQ.ShowDialog();
}
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 RunMonthly(){
dateFrom=PIn.Date(textDateFrom.Text);
dateTo=PIn.Date(textDateTo.Text);
/* There are 8 temp tables
* TableCharge: Holds sum of all charges for a certain date.
* TableCapWriteoff: Holds capComplete writeoffs which will be subtracted from Charges. They are subtracted from charges to give the illusion of not so much being charged in the first place. Calculated using dateCP, which should be same as DateProc.
* TableInsWriteoff: (not implemented yet) Writeoffs from claims received. Displayed in separate column so it won't mysteriously change the charges and all reports will match.
* TableSched: Holds Scheduled but not charged procedures
* TableCapEstWriteoff: (not implemented yet) capEstimate writeoffs. These will be subtracted from scheduled treatment
* TablePay: Holds sum of all Patient payments for a certain date.
* TableIns: Holds sum of all Insurance payments for a certain date.--- added by SPK 3/16/04
* TableAdj: Holds sum of all adjustments for a certain date.
* GROUP BY is used to group dates together so that amounts are summed for each date
*/
DataTable TableCharge= new DataTable(); //charges
DataTable TableCapWriteoff=new DataTable(); //capComplete writeoffs
DataTable TableInsWriteoff=new DataTable(); //ins writeoffs
DataTable TablePay= new DataTable(); //payments - Patient
DataTable TableIns= new DataTable(); //payments - Ins, added SPK
DataTable TableAdj= new DataTable(); //adjustments
DataTable TableSched= new DataTable();
//TableCharge---------------------------------------------------------------------------------------
/*
Select procdate, sum(procfee) From procedurelog
Group By procdate Order by procdate desc
*/
ReportSimpleGrid report=new ReportSimpleGrid();
string whereProv,whereClin;//used as the provider portion of the where clauses.
//each whereProv needs to be set up separately for each query
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+=") ";
}
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+=") ";
}
report.Query="SELECT "+DbHelper.DateColumn("procedurelog.ProcDate")+" ProcDate, "
+"SUM(procedurelog.ProcFee*(CASE procedurelog.UnitQty+procedurelog.BaseUnits WHEN 0 THEN 1 ELSE procedurelog.UnitQty+procedurelog.BaseUnits END)) "
+"FROM procedurelog "
+"WHERE "+DbHelper.DateColumn("procedurelog.ProcDate")+" >= "+POut.Date(dateFrom)+" "
+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" <= "+POut.Date(dateTo)+" "
+"AND procedurelog.ProcStatus = '2' "//complete
+whereProv
+whereClin
+"GROUP BY "+DbHelper.DateColumn("procedurelog.ProcDate")+" "
+"ORDER BY "+DbHelper.DateColumn("procedurelog.ProcDate");
TableCharge=report.GetTempTable();
//NEXT is TableCapWriteoff--------------------------------------------------------------------------
/*
SELECT DateCP, SUM(WriteOff) From claimproc
WHERE Status='7'
GROUP BY DateCP Order by DateCP
*/
report=new ReportSimpleGrid();
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 DateCP, SUM(WriteOff) FROM claimproc WHERE "
+"DateCP >= "+POut.Date(dateFrom)+" "
+"AND DateCP <= "+POut.Date(dateTo)+" "
+"AND Status = '7' "//CapComplete
+whereProv
+whereClin
+" GROUP BY DateCP "
+"ORDER BY DateCP";
TableCapWriteoff=report.GetTempTable();
//NEXT is TableInsWriteoff--------------------------------------------------------------------------
/*
SELECT DateCP, SUM(WriteOff) From claimproc
WHERE Status='1'
GROUP BY DateCP Order by DateCP
*/
report=new ReportSimpleGrid();
whereProv="";
if(!checkAllProv.Checked) {
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i==0){
whereProv+=" AND (";
}
else{
whereProv+="OR ";
}
whereProv+="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 DateCP,SUM(WriteOff) FROM claimproc WHERE "
+"DateCP >= "+POut.Date(dateFrom)+" "
+"AND DateCP <= "+POut.Date(dateTo)+" "
+"AND (Status = '1' OR Status = 4) "//Recieved or supplemental. Otherwise, it's only an estimate.
+whereProv
+whereClin
+" GROUP BY DateCP "
+"ORDER BY DateCP";
}
else{
report.Query="SELECT ProcDate,SUM(WriteOff) FROM claimproc WHERE "
+"ProcDate >= "+POut.Date(dateFrom)+" "
+"AND ProcDate <= "+POut.Date(dateTo)+" "
+"AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " //received or supplemental or notreceived
+whereProv
+whereClin
+" GROUP BY ProcDate "
+"ORDER BY ProcDate";
}
TableInsWriteoff=report.GetTempTable();
// NEXT is TableSched------------------------------------------------------------------------------
/*
SELECT FROM_DAYS(TO_DAYS(Appointment.AptDateTime)) AS
SchedDate,SUM(Procedurelog.procfee) FROM Appointment, Procedurelog
Where Appointment.aptnum = Procedurelog.aptnum && Appointment.AptStatus = 1
|| Appointment.AptStatus=4 && FROM_DAYS(TO_DAYS(Appointment.AptDateTime)) <= '2003-05-12'
GROUP BY SchedDate
*/
report=new ReportSimpleGrid();
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+=") ";
}
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+=") ";
}
report.Query= "SELECT "+DbHelper.DateColumn("t.AptDateTime")+" SchedDate,SUM(t.Fee-t.WriteoffEstimate) "
+"FROM (SELECT appointment.AptDateTime,IFNULL(procedurelog.ProcFee,0) Fee,";
if(PrefC.GetBool(PrefName.ReportPandIschedProdSubtractsWO)) {
report.Query+="SUM(IFNULL(CASE WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END,0)) WriteoffEstimate ";
}
else {
report.Query+="0 WriteoffEstimate ";
}
report.Query+=
"FROM appointment "
+"LEFT JOIN procedurelog ON appointment.AptNum = procedurelog.AptNum "
+"LEFT JOIN claimproc ON procedurelog.ProcNum = claimproc.ProcNum AND Status=6 AND (WriteOffEst != -1 OR WriteOffEstOverride != -1) "
+"WHERE (appointment.AptStatus = 1 OR "//stat=scheduled
+"appointment.AptStatus = 4) "//or stat=ASAP
+"AND "+DbHelper.DateColumn("appointment.AptDateTime")+" >= "+POut.Date(dateFrom)+" "
+"AND "+DbHelper.DateColumn("appointment.AptDateTime")+" <= "+POut.Date(dateTo)+" "
+whereProv
+whereClin
+" GROUP BY procedurelog.ProcNum) t "//without this, there can be duplicate proc rows due to the claimproc join with dual insurance.
+"GROUP BY SchedDate "
+"ORDER BY SchedDate";
TableSched=report.GetTempTable();
// NEXT is TablePay----------------------------------------------------------------------------------
//must join the paysplit to the payment to eliminate the discounts.
/*
Select paysplit.procdate,sum(paysplit.splitamt) from paysplit,payment where paysplit.procdate < '2003-08-12'
&& paysplit.paynum = payment.paynum
group by procdate union all
Select claimpayment.checkdate,sum(claimproc.inspayamt) from claimpayment,claimproc where
claimproc.claimpaymentnum = claimpayment.claimpaymentnum
&& claimpayment.checkdate < '2003-08-12'
group by claimpayment.checkdate order by procdate
*/
report=new ReportSimpleGrid();
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,SUM(paysplit.splitamt) FROM paysplit "
+"WHERE paysplit.IsDiscount = '0' "
+"AND paysplit.DatePay >= "+POut.Date(dateFrom)+" "
+"AND paysplit.DatePay <= "+POut.Date(dateTo)+" "
+whereProv
+whereClin
+" GROUP BY paysplit.DatePay ORDER BY DatePay";
TablePay=report.GetTempTable();
// NEXT is TableIns, added by SPK 3/16/04-----------------------------------------------------------
/*
Select claimpayment.checkdate,sum(claimproc.inspayamt) from claimpayment,claimproc where
claimproc.claimpaymentnum = claimpayment.claimpaymentnum
&& claimpayment.checkdate < '2003-08-12'
group by claimpayment.checkdate order by procdate
*/
report=new ReportSimpleGrid();
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,SUM(claimproc.InsPayamt) "
+"FROM claimpayment,claimproc WHERE "
+"claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum "
+"AND (claimproc.Status=1 OR claimproc.Status=4) "//received or supplemental
+"AND claimpayment.CheckDate >= " + POut.Date(dateFrom)+" "
+"AND claimpayment.CheckDate <= " + POut.Date(dateTo)+" "
+whereProv
+whereClin
+" GROUP BY claimpayment.CheckDate ORDER BY checkdate";
TableIns=report.GetTempTable();
// End TableIns, SPK 3/16/04
// LAST is TableAdj--------------------------------------------------------------------------------
/*
SELECT adjustment.adjdate,CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI),adjustment.adjtype,adjustment.adjnote,adjustment.adjamt
FROM adjustment,patient,definition
WHERE adjustment.adjtype=definition.defnum && patient.patnum=adjustment.patnum
ORDER BY adjdate DESC
*/
report=new ReportSimpleGrid();
whereProv="";
if(!checkAllProv.Checked) {
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i==0){
whereProv+=" AND (";
}
else{
whereProv+="OR ";
}
whereProv+="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 adjdate, SUM(adjamt) FROM adjustment WHERE "
+"adjdate >= "+POut.Date(dateFrom)+" "
+"AND adjdate <= "+POut.Date(dateTo)+" "
+whereProv
+whereClin
+" GROUP BY adjdate ORDER BY adjdate";
TableAdj=report.GetTempTable();
//Now to fill Table Q from the temp tables
report.TableQ=new DataTable(null);//new table with 10 columns
for(int i=0;i<10;i++){ //add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
decimal[] colTotals=new decimal[report.ColTotal.Length];
decimal production;
decimal scheduled;
decimal adjust;
decimal inswriteoff; //spk 5/19/05
decimal totalproduction;
decimal ptincome;
decimal insincome;
decimal totalincome;
DateTime[] dates=new DateTime[(dateTo-dateFrom).Days+1];
//MessageBox.Show(dates.Length.ToString());
//.ToString("yyyy-MM-dd")+"' "
// +"&& procdate <= '" + datePickerTo.Value
for(int i=0;i<dates.Length;i++){//usually 31 days in loop
//AddDays() starts to calculate a few seconds short over the course of 6 months or so. We make a correction here to work around.
dates[i]=dateFrom.AddDays(i).AddHours(6).Date;
//create new row called 'row' based on structure of TableQ
DataRow row = report.TableQ.NewRow();
row[0]=dates[i].ToShortDateString();
row[1]=dates[i].DayOfWeek.ToString();
production=0;
scheduled=0;
adjust=0;
inswriteoff=0; //spk 5/19/05
totalproduction=0;
ptincome=0;//spk
insincome=0;
totalincome=0;
for(int j=0;j<TableCharge.Rows.Count;j++) {
if(dates[i]==(PIn.Date(TableCharge.Rows[j][0].ToString()))){
production+=PIn.Decimal(TableCharge.Rows[j][1].ToString());
}
}
for(int j=0;j<TableCapWriteoff.Rows.Count;j++) {
if(dates[i]==(PIn.Date(TableCapWriteoff.Rows[j][0].ToString()))){
production-=PIn.Decimal(TableCapWriteoff.Rows[j][1].ToString());
}
}
for(int j=0; j<TableSched.Rows.Count; j++) {
if(dates[i]==(PIn.Date(TableSched.Rows[j][0].ToString()))){
scheduled+=PIn.Decimal(TableSched.Rows[j][1].ToString());
}
}
for(int j=0; j<TableAdj.Rows.Count; j++){
if(dates[i]==(PIn.Date(TableAdj.Rows[j][0].ToString()))){
adjust+=PIn.Decimal(TableAdj.Rows[j][1].ToString());
}
}
// ***** spk 5/19/05
for(int j=0; j<TableInsWriteoff.Rows.Count; j++) { // added for ins. writeoff, spk 5/19/05
if(dates[i]==(PIn.Date(TableInsWriteoff.Rows[j][0].ToString()))){
inswriteoff-=PIn.Decimal(TableInsWriteoff.Rows[j][1].ToString());
}
}
for(int j=0; j<TablePay.Rows.Count; j++){
if(dates[i]==(PIn.Date(TablePay.Rows[j][0].ToString()))){
ptincome+=PIn.Decimal(TablePay.Rows[j][1].ToString());
}
}
for(int j=0; j<TableIns.Rows.Count; j++){// new TableIns, SPK
if(dates[i]==(PIn.Date(TableIns.Rows[j][0].ToString()))){
insincome+=PIn.Decimal(TableIns.Rows[j][1].ToString());
}
}
totalproduction=production+scheduled+adjust+inswriteoff;
totalincome=ptincome+insincome;
row[2]=production.ToString("n");
row[3]=scheduled.ToString("n");
row[4]=adjust.ToString("n");
row[5]=inswriteoff.ToString("n"); //spk 5/19/05
row[6]=totalproduction.ToString("n");
row[7]=ptincome.ToString("n"); // spk
row[8]=insincome.ToString("n"); // spk
row[9]=totalincome.ToString("n");
colTotals[2]+=production;
colTotals[3]+=scheduled;
colTotals[4]+=adjust;
colTotals[5]+=inswriteoff; //spk 5/19/05
colTotals[6]+=totalproduction;
colTotals[7]+=ptincome; // spk
colTotals[8]+=insincome; // spk
colTotals[9]+=totalincome;
report.TableQ.Rows.Add(row); //adds row to table Q
}
for(int i=0;i<colTotals.Length;i++){
report.ColTotal[i]=PIn.Decimal(colTotals[i].ToString("n"));
}
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.ResetGrid();//necessary won't work without
report.Title="Production and Income";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(textDateFrom.Text+" - "+textDateTo.Text);
if(checkAllProv.Checked) {
report.SubTitle.Add(Lan.g(this,"All Providers"));
}
else{
string str="";
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i>0){
str+=", ";
}
str+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(str);
}
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.Summary.Add(
//=Lan.g(this,"Total Production (Production + Scheduled + Adjustments):")+" "
//+(colTotals[2]+colTotals[3]
//+colTotals[4]).ToString("c"); //spk 5/19/05
Lan.g(this,"Total Production (Production + Scheduled + Adj - Writeoff):")+" "
+(colTotals[2]+colTotals[3]+colTotals[4]
+colTotals[5]).ToString("c"));
report.Summary.Add("");
report.Summary.Add(
Lan.g(this,"Total Income (Pt Income + Ins Income):")+" "
+(colTotals[7]+colTotals[8]).ToString("c"));
report.ColPos[0]=20;
report.ColPos[1]=110;
report.ColPos[2]=190;
report.ColPos[3]=270;
report.ColPos[4]=350;
report.ColPos[5]=420;
report.ColPos[6]=490;
report.ColPos[7]=560;
report.ColPos[8]=630;
report.ColPos[9]=700;
report.ColPos[10]=770;
report.ColCaption[0]=Lan.g(this,"Date");
report.ColCaption[1]=Lan.g(this,"Weekday");
report.ColCaption[2]=Lan.g(this,"Production");
report.ColCaption[3]=Lan.g(this,"Sched");
report.ColCaption[4]=Lan.g(this,"Adj");
report.ColCaption[5]=Lan.g(this,"Writeoff"); //spk 5/19/05
report.ColCaption[6]=Lan.g(this,"Tot Prod");
report.ColCaption[7]=Lan.g(this,"Pt Income"); // spk
report.ColCaption[8]=Lan.g(this,"Ins Income"); // spk
report.ColCaption[9]=Lan.g(this,"Tot Income");
report.ColAlign[2]=HorizontalAlignment.Right;
report.ColAlign[3]=HorizontalAlignment.Right;
report.ColAlign[4]=HorizontalAlignment.Right;
report.ColAlign[5]=HorizontalAlignment.Right;
report.ColAlign[6]=HorizontalAlignment.Right;
report.ColAlign[7]=HorizontalAlignment.Right;
report.ColAlign[8]=HorizontalAlignment.Right;
report.ColAlign[9]=HorizontalAlignment.Right;
FormQuery2.ShowDialog();
}
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) {
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(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 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();
report.Query=@"SELECT ScreenDate,ProvName,County,county.CountyCode,
site.Description AS schoolName,site.Note AS schoolCode,PlaceService,GradeLevel,Age,Birthdate,RaceOld,Gender,Urgency,
HasCaries,EarlyChildCaries,CariesExperience,ExistingSealants,NeedsSealants,MissingAllTeeth,
Comments FROM screen
LEFT JOIN site ON screen.GradeSchool=site.Description
LEFT JOIN county ON screen.County=county.CountyName
WHERE ScreenDate >= "+POut.Date(date1.SelectionStart)+" "
+"AND ScreenDate <= " +POut.Date(date2.SelectionStart);
FormQuery2=new FormQuery(report);
FormQuery2.textTitle.Text="RawScreeningData"+DateTime.Today.ToString("MMddyyyy");
//FormQuery2.IsReport=true;
//FormQuery2.SubmitReportQuery();
FormQuery2.SubmitQuery();
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butUserQuery_Click(object sender,EventArgs e) {
if(!Security.IsAuthorized(Permissions.UserQuery)) {
return;
}
if(DataConnection.DBtype==DatabaseType.Oracle) {
MsgBox.Show(this,"Not allowed while using Oracle.");
return;
}
FormQuery FormQuery2=new FormQuery(null);
FormQuery2.ShowDialog();
SecurityLogs.MakeLogEntry(Permissions.UserQuery,0,"");
}
private void butOK_Click(object sender, System.EventArgs e)
{
if( textDateFrom.errorProvider1.GetError(textDateFrom)!=""
|| textDateTo.errorProvider1.GetError(textDateTo)!=""
){
MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
return;
}
if(listProv.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one provider must be selected.");
return;
}
if(listProv.SelectedIndices[0]==0 && listProv.SelectedIndices.Count>1){
MsgBox.Show(this,"You cannot select 'all' providers as well as specific providers.");
return;
}
DateTime dateFrom=PIn.Date(textDateFrom.Text);
DateTime dateTo=PIn.Date(textDateTo.Text);
string whereProv="";
if(listProv.SelectedIndices[0]!=0){
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i==0){
whereProv+=" WHERE (";
}
else{
whereProv+="OR ";
}
whereProv+="patient.PriProv = "
+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]-1].ProvNum)+" ";
}
whereProv+=") ";
}
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=@"SET @pos=0;
SELECT @pos:=@pos+1 patCount,result.* FROM (SELECT dateFirstProc,patient.LName,patient.FName,"
+DbHelper.Concat("referral.LName","IF(referral.FName='','',',')","referral.FName")+" refname,SUM(procedurelog.ProcFee) ";//\"$HowMuch\"";
if(DataConnection.DBtype==DatabaseType.MySql) {
report.Query+="$HowMuch";
}
else { //Oracle needs quotes.
report.Query+="\"$HowMuch\"";
}
if(checkAddress.Checked){
report.Query+=",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip";
}
report.Query+=@" FROM
(SELECT PatNum, MIN(ProcDate) dateFirstProc FROM procedurelog
WHERE ProcStatus=2 GROUP BY PatNum
HAVING dateFirstProc >= "+POut.Date(dateFrom)+" "
+"AND DATE(dateFirstProc) <= "+POut.Date(dateTo)+" ) table1 "
+@"INNER JOIN patient ON table1.PatNum=patient.PatNum
LEFT JOIN procedurelog ON patient.PatNum=procedurelog.PatNum AND procedurelog.ProcStatus=2
LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.IsFrom=1
AND refattach.ItemOrder=(SELECT MIN(ra.ItemOrder) FROM refattach ra WHERE ra.PatNum=refattach.PatNum AND ra.IsFrom=1)
LEFT JOIN referral ON referral.ReferralNum=refattach.ReferralNum "
+whereProv;
report.Query+="GROUP BY patient.LName,patient.FName,patient.PatNum,"+DbHelper.Concat("referral.LName","IF(referral.FName='','',',')","referral.FName");
if(checkAddress.Checked) {
report.Query+=",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip ";
}
if(checkProd.Checked){
if(DataConnection.DBtype==DatabaseType.MySql) {
report.Query+="HAVING $HowMuch > 0 ";
}
else {//Oracle needs quotes.
report.Query+="HAVING \"$HowMuch\" > 0 ";
}
}
report.Query+="ORDER BY dateFirstProc,patient.LName,patient.FName) result";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
report.Title="New Patients";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if(listProv.SelectedIndices[0]==0){
report.SubTitle.Add(Lan.g(this,"All Providers"));
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
else if(listProv.SelectedIndices.Count==1){
report.SubTitle.Add(Lan.g(this,"Prov: ")+ProviderC.ListShort[listProv.SelectedIndices[0]-1].GetLongDesc());
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
else{
//I'm too lazy to build a description for multiple providers as well as ensure that it fits the space.
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
report.SetColumnPos(this,0,"#",40);
report.SetColumnPos(this,1,"Date",120);
report.SetColumnPos(this,2,"Last Name",210);
report.SetColumnPos(this,3,"First Name",300);
report.SetColumnPos(this,4,"Referral",380);
report.SetColumnPos(this,5,"Production",450,HorizontalAlignment.Right);
if(checkAddress.Checked){
report.SetColumnPos(this,6,"Pref'd",500);
report.SetColumnPos(this,7,"Address",570);
report.SetColumnPos(this,8,"Add2",630);
report.SetColumnPos(this,9,"City",680);
report.SetColumnPos(this,10,"ST",730);
report.SetColumnPos(this,11,"Zip",880);//off the right side
}
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
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) {
/*
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)
{
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query="SELECT claim.dateservice,claim.claimnum,claim.claimtype,claim.claimstatus,"
+"CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),carrier.CarrierName,claim.claimfee "
+"FROM patient,claim,insplan,carrier "
+"WHERE patient.patnum=claim.patnum AND insplan.plannum=claim.plannum "
+"AND insplan.CarrierNum=carrier.CarrierNum "
+"AND (claim.claimstatus = 'U' OR claim.claimstatus = 'H' OR claim.claimstatus = 'W')";
if(radioRange.Checked==true){
report.Query
+=" AND claim.dateservice >= '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"' "
+"AND claim.dateservice <= '" + date2.SelectionStart.ToString("yyyy-MM-dd")+"'";
}
else{
report.Query
+=" AND claim.dateservice = '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"'";
}
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
DataTable tempT=report.GetTempTable();
report.TableQ=new DataTable(null);//new table no name
for(int i=0;i<6;i++){//add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
for(int i=0;i<tempT.Rows.Count;i++) {//loop through data rows
DataRow row=report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
row[0]=(PIn.Date(tempT.Rows[i][0].ToString())).ToShortDateString();//claim dateservice
if(PIn.String(tempT.Rows[i][2].ToString())=="P")
row[1]="Primary";
if(PIn.String(tempT.Rows[i][2].ToString())=="S")
row[1]="Secondary";
if(PIn.String(tempT.Rows[i][2].ToString())=="PreAuth")
row[1]="PreAuth";
if(PIn.String(tempT.Rows[i][2].ToString())=="Other")
row[1]="Other";
if(tempT.Rows[i][3].ToString().Equals("H"))
row[2]="Holding";//Claim Status
else if(tempT.Rows[i][3].ToString().Equals("W"))
row[2]="WaitQ";//Claim Status, added SPK 7/15/04
else
row[2]="Unsent";//Claim Status
row[3]=tempT.Rows[i][4];//Patient name
row[4]=tempT.Rows[i][5];//Ins Carrier
row[5]=PIn.Double(tempT.Rows[i][6].ToString()).ToString("F");//claim fee
report.ColTotal[5]+=PIn.Double(tempT.Rows[i][6].ToString());
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();//this is a method in FormQuery2;
report.Title="Claims Not Sent";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if(radioRange.Checked==true){
report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
}
else{
report.SubTitle.Add(date1.SelectionStart.ToString("d"));
}
report.ColPos[0]=20;
report.ColPos[1]=145;
report.ColPos[2]=270;
report.ColPos[3]=395;
report.ColPos[4]=520;
report.ColPos[5]=645;
report.ColPos[6]=770;
report.ColCaption[0]="Date";
report.ColCaption[1]="Type";
report.ColCaption[2]="Claim Status";
report.ColCaption[3]="Patient Name";
report.ColCaption[4]="Insurance Carrier";
report.ColCaption[5]="Amount";
report.ColAlign[5]=HorizontalAlignment.Right;
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e) {
if(listAdjType.SelectedIndices.Count==0){
MessageBox.Show("At least one adjustment type must be selected.");
return;
}
ReportSimpleGrid report=new ReportSimpleGrid();
string types="";
for(int i=0;i<listAdjType.SelectedIndices.Count;i++){
if(i==0){
types+="(";
}
else{
types+="OR ";
}
types+="AdjType='"
+DefC.Short[(int)DefCat.AdjTypes][listAdjType.SelectedIndices[i]].DefNum.ToString()
+"' ";
}
types+=")";
report.Query=@"
CREATE TEMPORARY TABLE tempbroken(
PatNum bigint unsigned NOT NULL,
NumberBroken smallint NOT NULL,
PRIMARY KEY (PatNum)
);
INSERT INTO tempbroken SELECT PatNum,COUNT(*)
FROM adjustment WHERE "+types
+"AND AdjDate >= "+POut.Date(date1.SelectionStart)+" "
+"AND AdjDate <= " +POut.Date(date2.SelectionStart)+" "
+@"GROUP BY PatNum;
SELECT patient.PatNum,MIN(procedurelog.ProcDate) AS ProcDate,
CONCAT(CONCAT(provider.LName,', '),provider.FName) as ProvName,
County,county.CountyCode,
site.Description AS gradeschool,site.Note AS schoolCode,GradeLevel,Birthdate,Race,Gender,Urgency,BillingType,
patient.PlannedIsDone,tempbroken.NumberBroken
FROM patient
LEFT JOIN procedurelog ON procedurelog.PatNum=patient.PatNum
LEFT JOIN provider ON procedurelog.ProvNum=provider.ProvNum
LEFT JOIN site ON patient.SiteNum=site.SiteNum
LEFT JOIN county ON patient.County=county.CountyName
LEFT JOIN tempbroken ON tempbroken.PatNum=patient.PatNum
WHERE (procedurelog.ProcStatus='2'
AND procedurelog.ProcDate >= "+POut.Date(date1.SelectionStart)+" "
+"AND procedurelog.ProcDate <= " +POut.Date(date2.SelectionStart)+" )"
+"OR tempbroken.NumberBroken>0 "
+@"GROUP BY patient.PatNum
ORDER By ProcDate;
DROP TABLE tempbroken;";
/*
CREATE TEMPORARY TABLE tempbroken(
PatNum mediumint unsigned NOT NULL,
NumberBroken smallint NOT NULL,
PRIMARY KEY (PatNum)
);
INSERT INTO tempbroken
SELECT PatNum,COUNT(*)
FROM adjustment
WHERE AdjType='14'
&& AdjDate='2004-05-03'
GROUP BY PatNum;
SELECT MIN(procedurelog.ProcDate) AS ProcDate,
CONCAT(provider.LName,', ',provider.FName) as ProvName,
County,county.CountyCode,
GradeSchool,school.SchoolCode,GradeLevel,Birthdate,Race,Gender,Urgency,BillingType,
patient.NextAptNum='-1' AS Done,tempbroken.NumberBroken
FROM patient,procedurelog,provider,tempbroken
LEFT JOIN school ON patient.GradeSchool=school.SchoolName
LEFT JOIN county ON patient.County=county.CountyName
WHERE procedurelog.ProcStatus='2'
&& patient.PatNum=procedurelog.PatNum
&& procedurelog.ProvNum=provider.ProvNum
&& tempbroken.PatNum=patient.PatNum
&& procedurelog.ProcDate >= '2004-05-03'
&& procedurelog.ProcDate <= '2004-05-03'
GROUP BY procedurelog.PatNum
ORDER By ProcDate;
DROP TABLE tempbroken;
*/
FormQuery2=new FormQuery(report);
FormQuery2.textTitle.Text="RawPopulationData"+DateTime.Today.ToString("MMddyyyy");
//FormQuery2.IsReport=true;
//FormQuery2.SubmitReportQuery();
FormQuery2.SubmitQuery();
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( textDateFrom.errorProvider1.GetError(textDateFrom)!=""
|| textDateTo.errorProvider1.GetError(textDateTo)!=""
){
MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
return;
}
if(listProv.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one provider must be selected.");
return;
}
if(listProv.SelectedIndices[0]==0 && listProv.SelectedIndices.Count>1){
MsgBox.Show(this,"You cannot select 'all' providers as well as specific providers.");
return;
}
DateTime dateFrom=PIn.Date(textDateFrom.Text);
DateTime dateTo=PIn.Date(textDateTo.Text);
if(dateTo<dateFrom) {
MsgBox.Show(this,"To date cannot be before From date.");
return;
}
string whereProv="";
if(listProv.SelectedIndices[0]!=0){
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i==0){
whereProv+=" AND (";
}
else{
whereProv+="OR ";
}
whereProv+="procedurelog.ProvNum = "
+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]-1].ProvNum)+" ";
}
whereProv+=") ";
}
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=@"SELECT referral.LName,referral.FName,
COUNT(DISTINCT refattach.PatNum) HowMany,
SUM(procedurelog.ProcFee) ""$HowMuch""";
if(checkAddress.Checked){
report.Query+=",referral.Title,referral.Address,referral.Address2,referral.City,"
+"referral.ST,referral.Zip,referral.Specialty";
}
report.Query+=@" FROM referral,refattach,procedurelog,patient
WHERE referral.ReferralNum=refattach.ReferralNum
AND procedurelog.PatNum=refattach.PatNum
AND procedurelog.PatNum=patient.PatNum
AND refattach.IsFrom=1
AND procedurelog.ProcStatus=2
AND procedurelog.ProcDate >= "+POut.Date(dateFrom)+" "
+"AND procedurelog.ProcDate <= "+POut.Date(dateTo)+" "
+whereProv;
if(checkNewPat.Checked){
report.Query+="AND patient.DateFirstVisit >= "+POut.Date(dateFrom)+" "
+"AND patient.DateFirstVisit <= "+POut.Date(dateTo)+" ";
}
report.Query+=@"GROUP BY referral.ReferralNum
ORDER BY HowMany Desc";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
report.Title="Referral Analysis";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if(listProv.SelectedIndices[0]==0){
report.SubTitle.Add(Lan.g(this,"All Providers"));
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
else if(listProv.SelectedIndices.Count==1){
report.SubTitle.Add(Lan.g(this,"Prov: ")+ProviderC.ListShort[listProv.SelectedIndices[0]-1].GetLongDesc());
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
else{
//I'm too lazy to build a description for multiple providers as well as ensure that it fits the space.
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
if(checkAddress.Checked){
report.SetColumnPos(this,0,"Last Name",100);
report.SetColumnPos(this,1,"First Name",200);
report.SetColumnPos(this,2,"Count",270);
report.SetColumnPos(this,3,"Production",350,HorizontalAlignment.Right);
report.SetColumnPos(this,4,"Title",390);
report.SetColumnPos(this,5,"Address",490);
report.SetColumnPos(this,6,"Add2",530);
report.SetColumnPos(this,7,"City",590);
report.SetColumnPos(this,8,"ST",630);
report.SetColumnPos(this,9,"Zip",680);
report.SetColumnPos(this,10,"Specialty",880);//off the right side
}
else{
report.SetColumnPos(this,0,"Last Name",100);
report.SetColumnPos(this,1,"First Name",200);
report.SetColumnPos(this,2,"Count",270);
report.SetColumnPos(this,3,"Production",350,HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
string[] fieldsSelected=new string[listSelect.SelectedItems.Count+listSelect2.SelectedItems.Count];
if(listSelect.SelectedItems.Count==0 && listSelect2.SelectedItems.Count==0){
MsgBox.Show(this,"At least one field must be selected.");
return;
}
listSelect.SelectedItems.CopyTo(fieldsSelected,0);
listSelect2.SelectedItems.CopyTo(fieldsSelected,listSelect.SelectedItems.Count);
string command="SELECT ";
for(int i=0;i<fieldsSelected.Length;i++){
if(i>0){
command+=",";
}
if(fieldsSelected[i]=="AptDateTime"){
command+="appointment.AptDateTime";
}
else if(fieldsSelected[i]=="PriCarrier"){
command+="(SELECT carrier.CarrierName FROM patplan,inssub,insplan,carrier WHERE patplan.PatNum=patient.PatNum "
+"AND patplan.InsSubNum=inssub.InsSubNum AND inssub.PlanNum=insplan.PlanNum AND insplan.CarrierNum=carrier.CarrierNum AND patplan.Ordinal=1) PriCarrier";
}
else if(fieldsSelected[i]=="PriRelationship") {
command+="(SELECT Relationship FROM patplan WHERE patplan.PatNum=patient.PatNum AND patplan.Ordinal=1) PriRelationship";
}
else if(fieldsSelected[i]=="SecCarrier") {
command+="(SELECT carrier.CarrierName FROM patplan,inssub,insplan,carrier WHERE patplan.PatNum=patient.PatNum "
+"AND patplan.InsSubNum=inssub.InsSubNum AND inssub.PlanNum=insplan.PlanNum AND insplan.CarrierNum=carrier.CarrierNum AND patplan.Ordinal=2) SecCarrier";
}
else if(fieldsSelected[i]=="SecRelationship") {
command+="(SELECT Relationship FROM patplan WHERE patplan.PatNum=patient.PatNum AND patplan.Ordinal=2) SecRelationship";
}
else{
command+="patient."+fieldsSelected[i];
}
}
command+=" FROM patient,appointment "
+"WHERE patient.PatNum=appointment.PatNum AND(";
for(int i=0;i<AptNums.Length;i++){
if(i>0){
command+=" OR";
}
command+=" appointment.AptNum='"+AptNums[i]+"'";
}
command+=")";
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=command;
FormQuery FormQ=new FormQuery(report);
FormQ.IsReport=false;
FormQ.SubmitQuery();
FormQ.textQuery.Text=report.Query;
FormQ.ShowDialog();
DialogResult=DialogResult.OK;
}
private void RunAnnual(){
dateFrom=PIn.Date(textDateFrom.Text);
dateTo=PIn.Date(textDateTo.Text);
/* There are 4 temp tables
* TableProduction: Sum of all charges for each month - CapComplete Writeoffs
* TableAdj: Sum of all adjustments for each month
* TablePay: Sum of all Patient payments for each month
* TableIns: Sum of all Insurance payments for each month
* GROUP BY is used to group dates together so that amounts are summed for each month
*/
DataTable TableProduction= new DataTable();
DataTable TableAdj= new DataTable();
DataTable TableInsWriteoff=new DataTable(); //ins writeoffs, added spk 5/19/05
DataTable TablePay= new DataTable();
DataTable TableIns= new DataTable();
//Month
//Production
//Adjustments
//InsWriteoff
//Total Production
//Pt Income
//Ins Income
//Total Income
ReportSimpleGrid report=new ReportSimpleGrid();
//Procedures------------------------------------------------------------------------------
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+=") ";
}
report.Query="SELECT "
+"procedurelog.ProcDate,"
+"SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(claimproc.WriteOff),0) "
+"FROM procedurelog "
+"LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum "
+"AND claimproc.Status='7' "//only CapComplete writeoffs are subtracted here.
+"WHERE procedurelog.ProcStatus = '2' "
+whereProv
+whereClin
+"AND procedurelog.ProcDate >= " +POut.Date(dateFrom)+" "
+"AND procedurelog.ProcDate <= " +POut.Date(dateTo)+" "
+"GROUP BY MONTH(procedurelog.ProcDate)";
//MessageBox.Show(report.Query);
TableProduction=report.GetTempTable();
//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,"
+"SUM(adjustment.AdjAmt) "
+"FROM adjustment "
+"WHERE adjustment.AdjDate >= "+POut.Date(dateFrom)+" "
+"AND adjustment.AdjDate <= "+POut.Date(dateTo)+" "
+whereProv
+whereClin
+"GROUP BY MONTH(adjustment.AdjDate)";
TableAdj=report.GetTempTable();
//TableInsWriteoff--------------------------------------------------------------------------
whereProv="";
if(!checkAllProv.Checked) {
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i==0){
whereProv+=" AND (";
}
else{
whereProv+="OR ";
}
whereProv+="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,"
+"SUM(claimproc.WriteOff) "
+"FROM claimproc "
+"WHERE claimproc.DateCP >= "+POut.Date(dateFrom)+" "
+"AND claimproc.DateCP <= "+POut.Date(dateTo)+" "
+whereProv
+whereClin
+"AND (claimproc.Status=1 OR claimproc.Status=4) "//Received or supplemental
+"GROUP BY MONTH(claimproc.DateCP)";
}
else{
report.Query="SELECT "
+"claimproc.ProcDate,"
+"SUM(claimproc.WriteOff) "
+"FROM claimproc "
+"WHERE claimproc.ProcDate >= "+POut.Date(dateFrom)+" "
+"AND claimproc.ProcDate <= "+POut.Date(dateTo)+" "
+whereProv
+whereClin
+"AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " //received or supplemental or notreceived
+"GROUP BY MONTH(claimproc.ProcDate)";
}
TableInsWriteoff=report.GetTempTable();
//PtIncome--------------------------------------------------------------------------------
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,"
+"SUM(paysplit.SplitAmt) "
+"FROM paysplit "
+"WHERE paysplit.IsDiscount=0 "//AND paysplit.PayNum=payment.PayNum "
+whereProv
+whereClin
+"AND paysplit.DatePay >= "+POut.Date(dateFrom)+" "
+"AND paysplit.DatePay <= "+POut.Date(dateTo)+" "
+"GROUP BY MONTH(paysplit.DatePay)";
TablePay=report.GetTempTable();
//InsIncome---------------------------------------------------------------------------------
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,SUM(claimproc.InsPayamt) "
+"FROM claimpayment,claimproc WHERE "
+"claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum "
+"AND claimpayment.CheckDate >= " + POut.Date(dateFrom)+" "
+"AND claimpayment.CheckDate <= " + POut.Date(dateTo)+" "
+whereProv
+whereClin
+" GROUP BY claimpayment.CheckDate ORDER BY checkdate";
TableIns=report.GetTempTable();
report.TableQ=new DataTable(null);//new table with 7 columns
for(int i=0;i<8;i++){ //add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
decimal[] colTotals=new decimal[report.ColTotal.Length];
decimal production;
decimal adjust;
decimal inswriteoff; //spk 5/19/05
decimal totalproduction;
decimal ptincome;
decimal insincome;
decimal totalincome;
//lenth of array is number of months between the two dates plus one.
//MessageBox.Show((dateTo.Year*12+dateTo.Month-dateFrom.Year*12-dateFrom.Month+1).ToString());
DateTime[] dates=new DateTime[dateTo.Year*12+dateTo.Month-dateFrom.Year*12-dateFrom.Month+1];//1st of each month
//MessageBox.Show(dates.Length.ToString());
//.ToString("yyyy-MM-dd")+"' "
// +"&& procdate <= '" + datePickerTo.Value
for(int i=0;i<dates.Length;i++){//usually 12 months in loop
dates[i]=dateFrom.AddMonths(i);//only the month and year are important
//create new row called 'row' based on structure of TableQ
DataRow row=report.TableQ.NewRow();
row[0]=dates[i].ToString("MMM yy");
production=0;
adjust=0;
inswriteoff=0; //spk 5/19/05
totalproduction=0;
ptincome=0;
insincome=0;
totalincome=0;
for(int j=0;j<TableProduction.Rows.Count;j++) {
if(dates[i].Year==PIn.Date(TableProduction.Rows[j][0].ToString()).Year
&& dates[i].Month==PIn.Date(TableProduction.Rows[j][0].ToString()).Month){
production+=PIn.Decimal(TableProduction.Rows[j][1].ToString());
}
}
for(int j=0;j<TableAdj.Rows.Count; j++){
if(dates[i].Year==PIn.Date(TableAdj.Rows[j][0].ToString()).Year
&& dates[i].Month==PIn.Date(TableAdj.Rows[j][0].ToString()).Month){
adjust+=PIn.Decimal(TableAdj.Rows[j][1].ToString());
}
}
for(int j=0;j<TableInsWriteoff.Rows.Count; j++){
if(dates[i].Year==PIn.Date(TableInsWriteoff.Rows[j][0].ToString()).Year
&& dates[i].Month==PIn.Date(TableInsWriteoff.Rows[j][0].ToString()).Month){
inswriteoff-=PIn.Decimal(TableInsWriteoff.Rows[j][1].ToString());
}
}
for(int j=0;j<TablePay.Rows.Count; j++){
if(dates[i].Year==PIn.Date(TablePay.Rows[j][0].ToString()).Year
&& dates[i].Month==PIn.Date(TablePay.Rows[j][0].ToString()).Month){
ptincome+=PIn.Decimal(TablePay.Rows[j][1].ToString());
}
}
for(int j=0; j<TableIns.Rows.Count; j++){//
if(dates[i].Year==PIn.Date(TableIns.Rows[j][0].ToString()).Year
&& dates[i].Month==PIn.Date(TableIns.Rows[j][0].ToString()).Month){
insincome+=PIn.Decimal(TableIns.Rows[j][1].ToString());
}
}
totalproduction=production+adjust+inswriteoff;
totalincome=ptincome+insincome;
row[1]=production.ToString("n");
row[2]=adjust.ToString("n");
row[3]=inswriteoff.ToString("n");
row[4]=totalproduction.ToString("n");
row[5]=ptincome.ToString("n");
row[6]=insincome.ToString("n");
row[7]=totalincome.ToString("n");
colTotals[1]+=production;
colTotals[2]+=adjust;
colTotals[3]+=inswriteoff;
colTotals[4]+=totalproduction;
colTotals[5]+=ptincome;
colTotals[6]+=insincome;
colTotals[7]+=totalincome;
report.TableQ.Rows.Add(row); //adds row to table Q
}
for(int i=0;i<colTotals.Length;i++){
report.ColTotal[i]=PIn.Decimal(colTotals[i].ToString("n"));
}
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.ResetGrid();//necessary won't work without
report.Title=Lan.g(this,"Annual Production and Income");
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(textDateFrom.Text+" - "+textDateTo.Text);
if(checkAllProv.Checked) {
report.SubTitle.Add(Lan.g(this,"All Providers"));
}
else{
string str="";
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i>0){
str+=", ";
}
str+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(str);
}
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.ColPos[0]=20;
report.ColPos[1]=120;
report.ColPos[2]=210;
report.ColPos[3]=300;
report.ColPos[4]=390;
report.ColPos[5]=480;
report.ColPos[6]=570;
report.ColPos[7]=660;
report.ColPos[8]=750;
//Month
//Production
//Adjustments
//Total Production
//Pt Income
//Ins Income
//Total Income
report.ColCaption[0]=Lan.g(this,"Month");
report.ColCaption[1]=Lan.g(this,"Production");
report.ColCaption[2]=Lan.g(this,"Adjustments");
report.ColCaption[3]=Lan.g(this,"Writeoff"); //spk
report.ColCaption[4]=Lan.g(this,"Tot Prod");
report.ColCaption[5]=Lan.g(this,"Pt Income");
report.ColCaption[6]=Lan.g(this,"Ins Income");
report.ColCaption[7]=Lan.g(this,"Total Income");
report.ColAlign[1]=HorizontalAlignment.Right;
report.ColAlign[2]=HorizontalAlignment.Right;
report.ColAlign[3]=HorizontalAlignment.Right;
report.ColAlign[4]=HorizontalAlignment.Right;
report.ColAlign[5]=HorizontalAlignment.Right;
report.ColAlign[6]=HorizontalAlignment.Right;
report.ColAlign[7]=HorizontalAlignment.Right;
FormQuery2.ShowDialog();
}
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 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)
{
FormQuery FormQuery2;
string phrase = textPharse.Text.Replace("\t","").Replace("\n","");
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendFormat("SELECT LName,FName,Preferred,PatStatus,Gender,Birthdate,Address,Address2,City,State,zip,HmPhone,Wkphone,",phrase);
sbSQL.AppendFormat("WirelessPhone,Guarantor,PriProv,AddrNote,FamFinUrgNote,MedUrgNote,ApptModNote,DateFirstVisit",phrase);
sbSQL.AppendFormat(" FROM patient WHERE AddrNote LIKE '%{0}%' ", phrase);
sbSQL.AppendFormat("or FamFinUrgNote LIKE '%{0}%' ",phrase);
sbSQL.AppendFormat("or MedUrgNote LIKE '%{0}%' ",phrase);
sbSQL.AppendFormat("or ApptModNote LIKE '%{0}%' ",phrase);
sbSQL.AppendFormat("or EmploymentNote LIKE '%{0}%' ",phrase);
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query= sbSQL.ToString();
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=false;
FormQuery2.SubmitQuery();
FormQuery2.textQuery.Text=report.Query;
FormQuery2.ShowDialog();
}
private void butOK_Click(object sender, System.EventArgs e) {
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=SQLstatement;
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=false;
FormQuery2.SubmitQuery();
FormQuery2.textQuery.Text=report.Query;
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;
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;
}