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();
}
