private void butOK_Click(object sender, System.EventArgs e) { //if(textDate.errorProvider1.GetError(textDate)!=""){ // MessageBox.Show(Lan.g(this,"Please fix data entry errors first.")); // return; //} ReportSimpleGrid report=new ReportSimpleGrid(); report.Query= "SELECT "+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+",adjamt " +"FROM patient,adjustment " +"WHERE patient.patnum=adjustment.patnum " +"AND adjustment.adjdate = "+POut.Date(PrefC.GetDate(PrefName.FinanceChargeLastRun)) +"AND adjustment.adjtype = '"+POut.Long(PrefC.GetLong(PrefName.FinanceChargeAdjustmentType))+"'"; FormQuery2=new FormQuery(report); FormQuery2.IsReport=true; FormQuery2.SubmitReportQuery(); report.Title="FINANCE CHARGE REPORT"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SubTitle.Add("Date of Charges: "+PrefC.GetDate(PrefName.FinanceChargeLastRun).ToShortDateString()); report.SetColumn(this,0,"Patient Name",180); report.SetColumn(this,1,"Amount",100,HorizontalAlignment.Right); FormQuery2.ShowDialog(); DialogResult=DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { Cursor=Cursors.WaitCursor; ReportSimpleGrid report=new ReportSimpleGrid(); report.Query=@"SELECT procedurelog.PatNum,"+DbHelper.Concat("patient.LName","', '","patient.FName")+@" patname, procedurelog.ProcDate, SUM(procedurelog.ProcFee) ""$sumfee"", SUM((SELECT SUM(claimproc.InsPayAmt + claimproc.Writeoff) FROM claimproc WHERE claimproc.ProcNum=procedurelog.ProcNum)) AS ""$PaidAndWriteoff"" FROM procedurelog LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum LEFT JOIN patient ON patient.PatNum=procedurelog.PatNum WHERE procedurelog.ProcStatus=2/*complete*/ AND procedurelog.ProcFee > 0 GROUP BY procedurelog.PatNum,"+DbHelper.Concat("patient.LName","', '","patient.FName")+@",procedurelog.ProcDate HAVING ROUND($sumfee,3) < ROUND($PaidAndWriteoff,3) ORDER BY patname,ProcDate"; FormQuery FormQuery2=new FormQuery(report); FormQuery2.IsReport=true; FormQuery2.SubmitReportQuery(); report.Title="INSURANCE OVERPAID REPORT"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SetColumn(this,0,"PatNum",60); report.SetColumn(this,1,"Pat Name",150); report.SetColumn(this,2,"Date",80); report.SetColumn(this,3,"Fee",80,HorizontalAlignment.Right); report.SetColumn(this,4,"InsPd+W/O",90,HorizontalAlignment.Right); Cursor=Cursors.Default; FormQuery2.ShowDialog(); DialogResult=DialogResult.OK; }
private void 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) { if(!checkAllProv.Checked && listProv.SelectedIndices.Count==0) { MsgBox.Show(this,"At least one provider must be selected."); return; } if(!PrefC.GetBool(PrefName.EasyNoClinics)) { if(!checkAllClin.Checked && listClin.SelectedIndices.Count==0) { MsgBox.Show(this,"At least one clinic must be selected."); return; } } string whereProv=""; if(!checkAllProv.Checked) { for(int i=0;i<listProv.SelectedIndices.Count;i++){ if(i==0){ whereProv+=" AND ("; } else{ whereProv+="OR "; } whereProv+="claimproc.ProvNum = " +POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" "; } whereProv+=") "; } string whereClin=""; if(!checkAllClin.Checked) { for(int i=0;i<listClin.SelectedIndices.Count;i++) { if(i==0) { whereClin+=" AND ("; } else { whereClin+="OR "; } if(listClin.SelectedIndices[i]==0) { whereClin+="claimproc.ClinicNum = 0 "; } else { whereClin+="claimproc.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" "; } } whereClin+=") "; } ReportSimpleGrid report=new ReportSimpleGrid(); report.Query="SET @FromDate="+POut.Date(date1.SelectionStart)+", @ToDate="+POut.Date(date2.SelectionStart)+";"; if(radioWriteoffPay.Checked){ report.Query+="SELECT "+DbHelper.DateColumn("claimproc.DateCP")+" date," +DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+"," +"carrier.CarrierName," +"provider.Abbr,"; if(!PrefC.GetBool(PrefName.EasyNoClinics)) { report.Query+="clinic.Description,"; } if(DataConnection.DBtype==DatabaseType.MySql) { report.Query+="SUM(claimproc.WriteOff) $amount,"; } else {//Oracle needs quotes. report.Query+="SUM(claimproc.WriteOff) \"$amount\","; } report.Query+="claimproc.ClaimNum " +"FROM claimproc "//,insplan,patient,carrier,provider " +"LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum " +"LEFT JOIN patient ON claimproc.PatNum = patient.PatNum " +"LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum " +"LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum " +"LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum " +"WHERE (claimproc.Status=1 OR claimproc.Status=4) " /*received or supplemental*/ +whereProv +whereClin +"AND claimproc.DateCP >= @FromDate " +"AND claimproc.DateCP <= @ToDate " +"AND claimproc.WriteOff > 0 " +"GROUP BY claimproc.ProvNum,claimproc.DateCP,claimproc.ClinicNum,claimproc.PatNum " +"ORDER BY claimproc.DateCP,claimproc.PatNum"; } else{//using procedure date report.Query+="SELECT "+DbHelper.DateColumn("claimproc.ProcDate")+" date, " +DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+", " +"carrier.CarrierName, " +"provider.Abbr,"; if(!PrefC.GetBool(PrefName.EasyNoClinics)) { report.Query+="clinic.Description,"; } if(DataConnection.DBtype==DatabaseType.MySql) { report.Query+="SUM(claimproc.WriteOff) $amount, "; } else {//Oracle needs quotes. report.Query+="SUM(claimproc.WriteOff) \"$amount\", "; } report.Query+="claimproc.ClaimNum " +"FROM claimproc "//,insplan,patient,carrier,provider " +"LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum " +"LEFT JOIN patient ON claimproc.PatNum = patient.PatNum " +"LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum " +"LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum " +"LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum " +"WHERE (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " /*received or supplemental or notreceived*/ +whereProv +whereClin +"AND claimproc.ProcDate >= @FromDate " +"AND claimproc.ProcDate <= @ToDate " +"AND claimproc.WriteOff > 0 " +"GROUP BY claimproc.ProvNum,claimproc.ProcDate,claimproc.ClinicNum,claimproc.PatNum " +"ORDER BY claimproc.ProcDate,claimproc.PatNum"; } FormQuery2=new FormQuery(report); FormQuery2.IsReport=true; FormQuery2.SubmitReportQuery(); report.Title="Daily Writeoffs"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d")); if(checkAllProv.Checked) { report.SubTitle.Add(Lan.g(this,"All Providers")); } else { string provNames=""; for(int i=0;i<listProv.SelectedIndices.Count;i++) { if(i>0) { provNames+=", "; } provNames+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr; } report.SubTitle.Add(provNames); } if(!PrefC.GetBool(PrefName.EasyNoClinics)) { if(checkAllClin.Checked) { report.SubTitle.Add(Lan.g(this,"All Clinics")); } else { string clinNames=""; for(int i=0;i<listClin.SelectedIndices.Count;i++) { if(i>0) { clinNames+=", "; } if(listClin.SelectedIndices[i]==0) { clinNames+=Lan.g(this,"Unassigned"); } else { clinNames+=Clinics.List[listClin.SelectedIndices[i]-1].Description; } } report.SubTitle.Add(clinNames); } } report.SetColumn(this,0,"Date",100); report.SetColumn(this,1,"Patient Name",150); report.SetColumn(this,2,"Carrier",225); report.SetColumn(this,3,"Provider",60); if(!PrefC.GetBool(PrefName.EasyNoClinics)) { report.SetColumn(this,4,"Clinic",80); report.SetColumn(this,5,"Amount",75,HorizontalAlignment.Right); report.SetColumn(this,6,"",280,HorizontalAlignment.Right); } else { report.SetColumn(this,4,"Amount",75,HorizontalAlignment.Right); report.SetColumn(this,5,"",280,HorizontalAlignment.Right); } FormQuery2.ShowDialog(); DialogResult=DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { long feeSched=FeeSchedC.ListShort[listFeeSched.SelectedIndex].FeeSchedNum; string catName=""; //string to hold current category name Fees fee=new Fees(); ReportSimpleGrid report=new ReportSimpleGrid(); report.Query= "SELECT procedurecode.ProcCode,fee.Amount,' ',procedurecode.Descript," +"procedurecode.AbbrDesc FROM procedurecode,fee " +"WHERE procedurecode.CodeNum=fee.CodeNum AND fee.FeeSched='"+feeSched.ToString() +"' ORDER BY procedurecode.ProcCode"; FormQuery2=new FormQuery(report); FormQuery2.IsReport=true; if (radioCode.Checked==true) { FormQuery2.SubmitReportQuery(); report.Title="Procedure Codes"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SubTitle.Add(FeeScheds.GetDescription(feeSched)); report.SetColumn(this,0,"Code",70); report.SetColumn(this,1,"Fee Amount",70,HorizontalAlignment.Right); report.SetColumn(this,2," ",80);//otherwise, the amount gets bunched up next to the description. report.SetColumn(this,3,"Description",200); report.SetColumn(this,4,"Abbr Description",200); FormQuery2.ShowDialog(); DialogResult=DialogResult.OK; } else {//categories //report.SubmitTemp();//create TableTemp which is not actually used ProcedureCode[] ProcList=ProcedureCodes.GetProcList(); report.TableQ=new DataTable(null); for(int i=0;i<5;i++){//add columns report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns } report.InitializeColumns(); DataRow row=report.TableQ.NewRow();//add first row by hand to get value for temp row[0]=DefC.GetName(DefCat.ProcCodeCats,ProcList[0].ProcCat); catName=row[0].ToString(); row[1]=ProcList[0].ProcCode; row[2]=ProcList[0].Descript; row[3]=ProcList[0].AbbrDesc; row[4]=((double)Fees.GetAmount0(ProcList[0].CodeNum,feeSched)).ToString("F"); report.ColTotal[4]+=PIn.Decimal(row[4].ToString()); report.TableQ.Rows.Add(row); for(int i=1;i<ProcList.Length;i++){//loop through data rows row=report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ row[0]=DefC.GetName(DefCat.ProcCodeCats,ProcList[i].ProcCat); if(catName==row[0].ToString()){ row[0]=""; } else { catName=row[0].ToString(); } row[1]=ProcList[i].ProcCode.ToString(); row[2]=ProcList[i].Descript; row[3]=ProcList[i].AbbrDesc.ToString(); row[4]=((double)Fees.GetAmount0(ProcList[i].CodeNum,feeSched)).ToString("F"); //report.ColTotal[4]+=PIn.PDouble(row[4].ToString()); report.TableQ.Rows.Add(row); } FormQuery2.ResetGrid();//this is a method in FormQuery2; report.Title="Procedure Codes"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SubTitle.Add(FeeScheds.GetDescription(feeSched)); report.ColPos[0]=20; report.ColPos[1]=120; report.ColPos[2]=270; report.ColPos[3]=470; report.ColPos[4]=620; report.ColPos[5]=770; report.ColCaption[0]="Category"; report.ColCaption[1]="Code"; report.ColCaption[2]="Description"; report.ColCaption[3]="Abbr Description"; report.ColCaption[4]="Fee Amount"; report.ColAlign[4]=HorizontalAlignment.Right; FormQuery2.ShowDialog(); DialogResult=DialogResult.OK; } }
private void butOK_Click(object sender, System.EventArgs e) { if( 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(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(!checkBillTypesAll.Checked && listBillType.SelectedIndices.Count==0){ MsgBox.Show(this,"At least one billing type must be selected."); return; } if(!checkProvAll.Checked && listProv.SelectedIndices.Count==0) { MsgBox.Show(this,"At least one provider must be selected."); return; } if(textDate.errorProvider1.GetError(textDate) != "") { MsgBox.Show(this,"Invalid date."); return; } DateTime asOfDate=PIn.Date(textDate.Text); //The aging report always show historical numbers based on the date entered. Ledgers.ComputeAging(0,asOfDate,true); ReportSimpleGrid report=new ReportSimpleGrid(); string cmd="SELECT "; if(PrefC.GetBool(PrefName.ReportsShowPatNum)){ cmd+=DbHelper.Concat("Cast(PatNum AS CHAR)","'-'","LName","', '","FName","' '","MiddleI"); } else{ cmd+=DbHelper.Concat("LName","', '","FName","' '","MiddleI"); } cmd+=",Bal_0_30,Bal_31_60,Bal_61_90,BalOver90" +",BalTotal " +",InsEst" +",BalTotal-InsEst AS ";//\"$pat\" "; if(DataConnection.DBtype==DatabaseType.MySql) { cmd+="$pat "; } else { //Oracle needs quotes. cmd+="\"$pat\" "; } cmd+="FROM patient " +"WHERE "; if(checkExcludeInactive.Checked) { cmd+="(patstatus != 2) AND "; } if(checkBadAddress.Checked) { cmd+="(zip !='') AND "; } if(checkOnlyNeg.Checked){ cmd+="BalTotal < '-.005' "; } else{ if(radioAny.Checked){ cmd+= "(Bal_0_30 > '.005' OR Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'"; } else if(radio30.Checked){ cmd+= "(Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'"; } else if(radio60.Checked){ cmd+= "(Bal_61_90 > '.005' OR BalOver90 > '.005'"; } else if(radio90.Checked){ cmd+= "(BalOver90 > '.005'"; } if(checkIncludeNeg.Checked){ cmd+=" OR BalTotal < '-.005'"; } cmd+=") "; } if(!checkBillTypesAll.Checked){ for(int i=0;i<listBillType.SelectedIndices.Count;i++) { if(i==0) { cmd+=" AND (billingtype = "; } else { cmd+=" OR billingtype = "; } cmd+=POut.Long(DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].DefNum); } cmd+=") "; } if(!checkProvAll.Checked) { for(int i=0;i<listProv.SelectedIndices.Count;i++) { if(i==0) { cmd+=" AND (PriProv = "; } else { cmd+=" OR PriProv = "; } cmd+=POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum); } cmd+=") "; } if(!PrefC.GetBool(PrefName.EasyNoClinics)) { cmd+=" AND ClinicNum IN("; for(int i=0;i<listClin.SelectedIndices.Count;i++) { if(i>0) { cmd+=","; } if(Security.CurUser.ClinicIsRestricted) { cmd+=POut.Long(_listClinics[listClin.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics } else { if(listClin.SelectedIndices[i]==0) { cmd+="0"; } else { cmd+=POut.Long(_listClinics[listClin.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index } } } cmd+=") "; } cmd+="ORDER BY LName,FName"; report.Query=cmd; FormQuery2=new FormQuery(report); FormQuery2.IsReport=true; FormQuery2.SubmitReportQuery(); //Recompute aging in a non-historical way, so that the numbers are returned to the way they //are normally used in other parts of the program. Ledgers.RunAging(); //if(Prefs.UpdateString(PrefName.DateLastAging",POut.PDate(asOfDate,false))) { // DataValid.SetInvalid(InvalidType.Prefs); //} report.Title="AGING OF ACCOUNTS RECEIVABLE REPORT"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SubTitle.Add("As of "+textDate.Text); if(radioAny.Checked){ report.SubTitle.Add("Any Balance"); } if(radio30.Checked){ report.SubTitle.Add("Over 30 Days"); } if(radio60.Checked){ report.SubTitle.Add("Over 60 Days"); } if(radio90.Checked){ report.SubTitle.Add("Over 90 Days"); } if(checkBillTypesAll.Checked){ report.SubTitle.Add("All Billing Types"); } else{ string subt=DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[0]].ItemName; for(int i=1;i<listBillType.SelectedIndices.Count;i++){ subt+=", "+DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].ItemName; } report.SubTitle.Add(subt); } //report.InitializeColumns(8); report.SetColumn(this,0,"GUARANTOR",160); report.SetColumn(this,1,"0-30 DAYS",80,HorizontalAlignment.Right); report.SetColumn(this,2,"30-60 DAYS",80,HorizontalAlignment.Right); report.SetColumn(this,3,"60-90 DAYS",80,HorizontalAlignment.Right); report.SetColumn(this,4,"> 90 DAYS",80,HorizontalAlignment.Right); report.SetColumn(this,5,"TOTAL",85,HorizontalAlignment.Right); report.SetColumn(this,6,"-INS EST",85,HorizontalAlignment.Right); report.SetColumn(this,7,"=PATIENT",85,HorizontalAlignment.Right); FormQuery2.ShowDialog(); DialogResult=DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { if(textDateFrom.errorProvider1.GetError(textDateFrom)!="" || textDateTo.errorProvider1.GetError(textDateTo)!="") { MsgBox.Show(this,"Please fix data entry errors first."); return; } DateTime dateFrom=PIn.Date(textDateFrom.Text); DateTime dateTo=PIn.Date(textDateTo.Text); if(dateTo<dateFrom) { MsgBox.Show(this,"To date cannot be before From date."); return; } ReportSimpleGrid report=new ReportSimpleGrid(); report.Query= "SELECT "+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+",AdjAmt " +"FROM patient,adjustment " +"WHERE patient.PatNum=adjustment.PatNum " +"AND adjustment.AdjDate BETWEEN "+POut.Date(dateFrom)+" AND "+POut.Date(dateTo)+" " +"AND adjustment.AdjType = '"+POut.Long(PrefC.GetLong(PrefName.FinanceChargeAdjustmentType))+"'"; FormQuery2=new FormQuery(report); FormQuery2.IsReport=true; FormQuery2.SubmitReportQuery(); report.Title=Lans.g(this,"FINANCE CHARGE REPORT"); report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SubTitle.Add(textDateFrom.Text+" - "+textDateTo.Text); report.SetColumn(this,0,"Patient Name",180); report.SetColumn(this,1,"Amount",100,HorizontalAlignment.Right); FormQuery2.ShowDialog(); DialogResult=DialogResult.OK; }
private void CreateGrouped(ReportSimpleGrid report) { //this would require a temporary table to be able to handle capitation. report.Query="SELECT definition.ItemName,procedurecode.ProcCode,procedurecode.Descript,"; if(DataConnection.DBtype==DatabaseType.MySql) { report.Query+="Count(*),AVG(procedurelog.ProcFee) $AvgFee,SUM(procedurelog.ProcFee) AS $TotFee "; } else {//Oracle needs quotes. report.Query+="Count(*),AVG(procedurelog.ProcFee) \"$AvgFee\",SUM(procedurelog.ProcFee) AS \"$TotFee\" "; } report.Query+="FROM procedurelog,procedurecode,definition " +"WHERE procedurelog.ProcStatus = '2' " +"AND procedurelog.CodeNum=procedurecode.CodeNum " +"AND definition.DefNum=procedurecode.ProcCat " +whereProv +whereClin +"AND procedurecode.ProcCode LIKE '%"+POut.String(textCode.Text)+"%' " +"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" >= '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"' " +"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" <= '" + date2.SelectionStart.ToString("yyyy-MM-dd")+"' " +"GROUP BY procedurecode.ProcCode " +"ORDER BY definition.ItemOrder,procedurecode.ProcCode"; FormQuery2=new FormQuery(report); FormQuery2.IsReport=true; FormQuery2.SubmitReportQuery(); report.Title="Procedures By Procedure Code"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d")); if(checkAllProv.Checked) { report.SubTitle.Add(Lan.g(this,"All Providers")); } else { string provNames=""; for(int i=0;i<listProv.SelectedIndices.Count;i++) { if(i>0) { provNames+=", "; } provNames+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr; } report.SubTitle.Add(provNames); } if(!PrefC.GetBool(PrefName.EasyNoClinics)) { if(checkAllClin.Checked) { report.SubTitle.Add(Lan.g(this,"All Clinics")); } else { string clinNames=""; for(int i=0;i<listClin.SelectedIndices.Count;i++) { if(i>0) { clinNames+=", "; } if(listClin.SelectedIndices[i]==0) { clinNames+=Lan.g(this,"Unassigned"); } else { clinNames+=Clinics.List[listClin.SelectedIndices[i]-1].Description; } } report.SubTitle.Add(clinNames); } } report.SetColumn(this,0,"Category",150); report.SetColumn(this,1,"Code",90); report.SetColumn(this,2,"Description",180); report.SetColumn(this,3,"Quantity",60,HorizontalAlignment.Right); report.SetColumn(this,4,"Average Fee",110,HorizontalAlignment.Right); report.SetColumn(this,5,"Total Fees",100,HorizontalAlignment.Right); FormQuery2.ShowDialog(); DialogResult=DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { if( 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) { /* 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 butViewImported_Click(object sender,EventArgs e) { Cursor=Cursors.WaitCursor; ReportSimpleGrid report=new ReportSimpleGrid(); report.Query="SELECT TransactionDateTime,TransType,ClerkID,ItemNum,PatNum,CreditCardNum,Expiration,Result,CASE WHEN ResultCode='000' OR ResultCode='010' THEN Amount ELSE Amount=0 END AS Amount " +"FROM xchargetransaction " +"WHERE DATE(TransactionDateTime) BETWEEN "+POut.Date(date1.SelectionStart)+" AND "+POut.Date(date2.SelectionStart)+" " +"AND TransType!='CCVoid'"; FormQuery FormQuery2=new FormQuery(report); FormQuery2.IsReport=true; FormQuery2.SubmitReportQuery(); report.Title="XCharge Transactions From "+date1.SelectionStart.ToShortDateString()+" To "+date2.SelectionStart.ToShortDateString(); report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SetColumn(this,0,"Transaction Date/Time",170); report.SetColumn(this,1,"Transaction Type",120); report.SetColumn(this,2,"Clerk ID",80); report.SetColumn(this,3,"Item#",50); report.SetColumn(this,4,"Pat",50);//This name is used to ensure FormQuery does not replace the patnum with the patient name. report.SetColumn(this,5,"Credit Card Num",140); report.SetColumn(this,6,"Exp",50); report.SetColumn(this,7,"Result",50); report.SetColumn(this,8,"Amount",60,HorizontalAlignment.Right); Cursor=Cursors.Default; FormQuery2.ShowDialog(); }
private void butPayments_Click(object sender,EventArgs e) { Cursor=Cursors.WaitCursor; string paymentType=ProgramProperties.GetPropVal(Programs.GetCur(ProgramName.Xcharge).ProgramNum,"PaymentType"); ReportSimpleGrid report=new ReportSimpleGrid(); report.Query="SET @pos=0; " +"SELECT @pos:=@pos+1 AS 'Count',patient.PatNum,LName,FName,DateEntry,PayDate,PayNote,PayAmt,PayType " +"FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum " //Must be DateEntry here. PayDate will not work with recurring charges +"WHERE PayType="+paymentType+" AND (DateEntry BETWEEN "+POut.Date(date1.SelectionStart)+" AND "+POut.Date(date2.SelectionStart)+") " +"ORDER BY Count ASC"; FormQuery FormQuery2=new FormQuery(report); FormQuery2.IsReport=true; FormQuery2.SubmitReportQuery(); report.Title="Payments From "+date1.SelectionStart.ToShortDateString()+" To "+date2.SelectionStart.ToShortDateString(); report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SetColumn(this,0,"Count",50); report.SetColumn(this,1,"Pat",50);//This name is used to ensure FormQuery does not replace the patnum with the patient name. report.SetColumn(this,2,"LName",100); report.SetColumn(this,3,"FName",100); report.SetColumn(this,4,"DateEntry",100); report.SetColumn(this,5,"PayDate",100); report.SetColumn(this,6,"PayNote",150); report.SetColumn(this,7,"PayAmt",70,HorizontalAlignment.Right); Cursor=Cursors.Default; FormQuery2.ShowDialog(); }