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 IN (1,4,5) "//Recieved, supplemental, capclaim. Otherwise, it's only an estimate. 7-CapCompl handled above. + whereProv + " GROUP BY DateCP " + "ORDER BY DateCP"; } else { report.Query = "SELECT ProcDate, " + "SUM(WriteOff) FROM claimproc WHERE " + "ProcDate >= '" + bDate + "' " + "AND ProcDate < '" + eDate + "' " + "AND Status IN (0,1,4,5) " //Notreceived, received, supplemental, capclaim. 7-CapCompl handled above. + 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.PayPlanNum=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,5,7) "//Received, supplemental, capclaim, capcomplete. + "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.Decimal(ColTotal[1].ToString("n")); report.ColTotal[2] = PIn.Decimal(ColTotal[2].ToString("n")); report.ColTotal[3] = PIn.Decimal(ColTotal[3].ToString("n")); report.ColTotal[4] = PIn.Decimal(ColTotal[4].ToString("n")); report.ColTotal[5] = PIn.Decimal(ColTotal[5].ToString("n")); report.ColTotal[6] = PIn.Decimal(ColTotal[6].ToString("n")); report.ColTotal[7] = PIn.Decimal(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 } //END OK button Clicked
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) { 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,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 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, System.EventArgs e) { if (textDateFrom.errorProvider1.GetError(textDateFrom) != "" || textDateTo.errorProvider1.GetError(textDateTo) != "" ) { MessageBox.Show(Lan.g(this, "Please fix data entry errors first.")); return; } if (listProv.SelectedIndices.Count == 0) { MsgBox.Show(this, "At least one provider must be selected."); return; } if (listProv.SelectedIndices[0] == 0 && listProv.SelectedIndices.Count > 1) { MsgBox.Show(this, "You cannot select 'all' providers as well as specific providers."); return; } DateTime dateFrom = PIn.Date(textDateFrom.Text); DateTime dateTo = PIn.Date(textDateTo.Text); string whereProv = ""; if (listProv.SelectedIndices[0] != 0) { for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i == 0) { whereProv += " AND ("; } else { whereProv += "OR "; } whereProv += "procedurelog.ProvNum = " + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " "; } whereProv += ") "; } ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = @"SELECT referral.LName,referral.FName, COUNT(DISTINCT refattach.PatNum) HowMany, SUM(procedurelog.ProcFee) ""$HowMuch"""; if (checkAddress.Checked) { report.Query += ",referral.Title,referral.Address,referral.Address2,referral.City," + "referral.ST,referral.Zip,referral.Specialty"; } report.Query += @" FROM referral,refattach,procedurelog,patient WHERE referral.ReferralNum=refattach.ReferralNum AND procedurelog.PatNum=refattach.PatNum AND procedurelog.PatNum=patient.PatNum AND refattach.IsFrom=1 AND procedurelog.ProcStatus=2 AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " " + "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " " + whereProv; if (checkNewPat.Checked) { report.Query += "AND patient.DateFirstVisit >= " + POut.Date(dateFrom) + " " + "AND patient.DateFirstVisit <= " + POut.Date(dateTo) + " "; } report.Query += @"GROUP BY referral.ReferralNum ORDER BY HowMany Desc"; FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); report.Title = "Referral Analysis"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); if (listProv.SelectedIndices[0] == 0) { report.SubTitle.Add(Lan.g(this, "All Providers")); report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d")); } else if (listProv.SelectedIndices.Count == 1) { report.SubTitle.Add(Lan.g(this, "Prov: ") + ProviderC.ListShort[listProv.SelectedIndices[0] - 1].GetLongDesc()); report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d")); } else { //I'm too lazy to build a description for multiple providers as well as ensure that it fits the space. report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d")); } if (checkAddress.Checked) { report.SetColumnPos(this, 0, "Last Name", 100); report.SetColumnPos(this, 1, "First Name", 200); report.SetColumnPos(this, 2, "Count", 270); report.SetColumnPos(this, 3, "Production", 350, HorizontalAlignment.Right); report.SetColumnPos(this, 4, "Title", 390); report.SetColumnPos(this, 5, "Address", 490); report.SetColumnPos(this, 6, "Add2", 530); report.SetColumnPos(this, 7, "City", 590); report.SetColumnPos(this, 8, "ST", 630); report.SetColumnPos(this, 9, "Zip", 680); report.SetColumnPos(this, 10, "Specialty", 880); //off the right side } else { report.SetColumnPos(this, 0, "Last Name", 100); report.SetColumnPos(this, 1, "First Name", 200); report.SetColumnPos(this, 2, "Count", 270); report.SetColumnPos(this, 3, "Production", 350, HorizontalAlignment.Right); } FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }