private void butViewImported_Click(object sender, EventArgs e) { Cursor = Cursors.WaitCursor; ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = "SELECT TransactionDateTime,TransType,ClerkID,ItemNum,PatNum,CreditCardNum,Expiration,Result," + "CASE WHEN ResultCode IN('000','010') THEN Amount ELSE 0 END AS Amount " + "FROM xchargetransaction " + "WHERE " + DbHelper.DtimeToDate("TransactionDateTime") + " BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart); FormQuery FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); report.Title = "XCharge Transactions From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString(); report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SetColumn(this, 0, "Transaction Date/Time", 170); report.SetColumn(this, 1, "Transaction Type", 120); report.SetColumn(this, 2, "Clerk ID", 80); report.SetColumn(this, 3, "Item#", 50); report.SetColumn(this, 4, "Pat", 50); //This name is used to ensure FormQuery does not replace the patnum with the patient name. report.SetColumn(this, 5, "Credit Card Num", 140); report.SetColumn(this, 6, "Exp", 50); report.SetColumn(this, 7, "Result", 50); report.SetColumn(this, 8, "Amount", 60, HorizontalAlignment.Right); Cursor = Cursors.Default; FormQuery2.ShowDialog(); }
private void butExtra_Click(object sender, EventArgs e) { Cursor = Cursors.WaitCursor; string programNum = ProgramProperties.GetPropVal(Programs.GetCur(ProgramName.Xcharge).ProgramNum, "PaymentType"); ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = "SELECT payment.PatNum, LName, FName, payment.DateEntry,payment.PayDate, payment.PayNote,payment.PayAmt " + "FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum " + "LEFT JOIN (SELECT TransactionDateTime,ClerkID,BatchNum,ItemNum,PatNum,CCType,CreditCardNum,Expiration,Result,Amount FROM xchargetransaction " + "WHERE DATE(TransactionDateTime) BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart) + @") AS X " + "ON X.PatNum=payment.PatNum AND DATE(X.TransactionDateTime)=payment.DateEntry AND X.Amount=payment.PayAmt " + "WHERE PayType=" + programNum + " AND DateEntry BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart) + " " + "AND X.TransactionDateTime IS NULL " + "ORDER BY PayDate ASC, patient.LName"; FormQuery FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); report.Title = "Payments From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString(); report.SubTitle.Add("No Matching X-Charge Transactions for these Payments"); report.SetColumn(this, 0, "PatNum", 50); report.SetColumn(this, 1, "LName", 100); report.SetColumn(this, 2, "FName", 100); report.SetColumn(this, 3, "DateEntry", 100); report.SetColumn(this, 4, "PayDate", 100); report.SetColumn(this, 5, "PayNote", 150); report.SetColumn(this, 6, "PayAmt", 70, HorizontalAlignment.Right); Cursor = Cursors.Default; FormQuery2.ShowDialog(); }
private void butViewImported_Click(object sender, EventArgs e) { Cursor = Cursors.WaitCursor; ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = "SELECT TransactionDateTime,ClerkID,BatchNum,ItemNum,PatNum,CCType,CreditCardNum,Expiration,Result,Amount FROM xchargetransaction " + "WHERE DATE(TransactionDateTime) BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart); FormQuery FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); report.Title = "XCharge Transactions From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString(); report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SetColumn(this, 0, "Transaction Date/Time", 170); report.SetColumn(this, 1, "Clerk ID", 80); report.SetColumn(this, 2, "Batch#", 50); report.SetColumn(this, 3, "Item#", 50); report.SetColumn(this, 4, "PatNum", 50); report.SetColumn(this, 5, "CC Type", 55); report.SetColumn(this, 6, "Credit Card Num", 140); report.SetColumn(this, 7, "Exp", 50); report.SetColumn(this, 8, "Result", 50); report.SetColumn(this, 9, "Amount", 60, HorizontalAlignment.Right); Cursor = Cursors.Default; FormQuery2.ShowDialog(); }
private void 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 butPayments_Click(object sender, EventArgs e) { Cursor = Cursors.WaitCursor; string programNum = ProgramProperties.GetPropVal(Programs.GetCur(ProgramName.Xcharge).ProgramNum, "PaymentType"); ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = "SET @pos=0; " + "SELECT @pos:=@pos+1 as 'Count', patient.PatNum, LName, FName, DateEntry,PayDate, PayNote,PayAmt " + "FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum " + "WHERE PayType=" + programNum + " AND DateEntry BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart) + "ORDER BY PayDate ASC, patient.LName"; FormQuery FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); report.Title = "Payments From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString(); report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SetColumn(this, 0, "Count", 50); report.SetColumn(this, 1, "PatNum", 50); report.SetColumn(this, 2, "LName", 100); report.SetColumn(this, 3, "FName", 100); report.SetColumn(this, 4, "DateEntry", 100); report.SetColumn(this, 5, "PayDate", 100); report.SetColumn(this, 6, "PayNote", 150); report.SetColumn(this, 7, "PayAmt", 70, HorizontalAlignment.Right); Cursor = Cursors.Default; FormQuery2.ShowDialog(); }
private void butMissing_Click(object sender, EventArgs e) { Cursor = Cursors.WaitCursor; string programNum = ProgramProperties.GetPropVal(Programs.GetCur(ProgramName.Xcharge).ProgramNum, "PaymentType"); ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = "SELECT TransactionDateTime,ClerkID,BatchNum,ItemNum,xchargetransaction.PatNum,CCType,CreditCardNum,Expiration,Result,Amount " + " FROM xchargetransaction LEFT JOIN (" + " SELECT patient.PatNum,LName,FName,DateEntry,PayDate,PayAmt,PayNote" + " FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum" + " WHERE PayType=" + programNum + " AND DateEntry BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart) + " ) AS P ON xchargetransaction.PatNum=P.PatNum AND DATE(xchargetransaction.TransactionDateTime)=P.DateEntry AND xchargetransaction.Amount=P.PayAmt " + " WHERE DATE(TransactionDateTime) BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart) + " AND P.PatNum IS NULL;"; FormQuery FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); report.Title = "XCharge Transactions From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString(); report.SubTitle.Add("No Matching Transaction Found in Open Dental"); report.SetColumn(this, 0, "Transaction Date/Time", 170); report.SetColumn(this, 1, "Clerk ID", 80); report.SetColumn(this, 2, "Batch#", 50); report.SetColumn(this, 3, "Item#", 50); report.SetColumn(this, 4, "PatNum", 50); report.SetColumn(this, 5, "CC Type", 55); report.SetColumn(this, 6, "Credit Card Num", 140); report.SetColumn(this, 7, "Exp", 50); report.SetColumn(this, 8, "Result", 50); report.SetColumn(this, 9, "Amount", 60, HorizontalAlignment.Right); Cursor = Cursors.Default; FormQuery2.ShowDialog(); }
private void butOK_Click(object sender, System.EventArgs e) { //if(textDate.errorProvider1.GetError(textDate)!=""){ // MessageBox.Show(Lan.g(this,"Please fix data entry errors first.")); // return; //} ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = "SELECT " + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ",adjamt " + "FROM patient,adjustment " + "WHERE patient.patnum=adjustment.patnum " + "AND adjustment.adjdate = " + POut.Date(PrefC.GetDate(PrefName.FinanceChargeLastRun)) + "AND adjustment.adjtype = '" + POut.Long(PrefC.GetLong(PrefName.FinanceChargeAdjustmentType)) + "'"; FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); report.Title = "FINANCE CHARGE REPORT"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SubTitle.Add("Date of Charges: " + PrefC.GetDate(PrefName.FinanceChargeLastRun).ToShortDateString()); report.SetColumn(this, 0, "Patient Name", 180); report.SetColumn(this, 1, "Amount", 100, HorizontalAlignment.Right); FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
private void butPayments_Click(object sender, EventArgs e) { Cursor = Cursors.WaitCursor; ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = "SET @pos=0; " + "SELECT @pos:=@pos+1 AS 'Count',patient.PatNum,LName,FName,DateEntry,PayDate,PayNote,PayAmt " + "FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum " + "INNER JOIN (" + "SELECT ClinicNum,PropertyValue AS PaymentType FROM programproperty " + "WHERE ProgramNum=" + POut.Long(Programs.GetProgramNum(ProgramName.Xcharge)) + " AND PropertyDesc='PaymentType'" + ") paytypes ON paytypes.ClinicNum=payment.ClinicNum AND paytypes.PaymentType=payment.PayType " //Must be DateEntry here. PayDate will not work with recurring charges + "WHERE DateEntry BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart) + " " + "ORDER BY Count ASC"; FormQuery FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); report.Title = "Payments From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString(); report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SetColumn(this, 0, "Count", 50); report.SetColumn(this, 1, "Pat", 50); //This name is used to ensure FormQuery does not replace the patnum with the patient name. report.SetColumn(this, 2, "LName", 100); report.SetColumn(this, 3, "FName", 100); report.SetColumn(this, 4, "DateEntry", 100); report.SetColumn(this, 5, "PayDate", 100); report.SetColumn(this, 6, "PayNote", 150); report.SetColumn(this, 7, "PayAmt", 70, HorizontalAlignment.Right); Cursor = Cursors.Default; FormQuery2.ShowDialog(); }
private void CreateIndividual() { //added plfname for ordering purposes, spk 3/13/04 // added CapCoPay for Capitation, SPK 7/15/04 (js rewrote it on 10/4/04 due to db change) // changed procedurecode.adacode to procedurelog.adacode & added Procnum to retrieve all codes Queries.CurReport.Query = "SELECT procedurelog.ProcDate,CONCAT(CONCAT(CONCAT(CONCAT" + "(patient.LName,', '),patient.FName),' '),patient.MiddleI) AS plfname, procedurelog.ADACode," + "procedurelog.ToothNum,procedurecode.Descript,provider.Abbr," + "procedurelog.ProcFee-IFNULL(SUM(claimproc.WriteOff),0) $fee "//if no writeoff, then subtract 0 + "FROM patient,procedurecode,provider,procedurelog " + "LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum " + "AND claimproc.Status='7' "//only CapComplete writeoffs are subtracted here. + "WHERE procedurelog.ProcStatus = '2' " + "AND patient.PatNum=procedurelog.PatNum " + "AND procedurelog.ADACode=procedurecode.ADACode " + "AND provider.ProvNum=procedurelog.ProvNum " + "AND " + whereProv + " " + "AND procedurelog.ADACode LIKE '%" + POut.PString(textADACode.Text) + "%' " + "AND procedurelog.ProcDate >= " + POut.PDate(date1.SelectionStart) + " " + "AND procedurelog.ProcDate <= " + POut.PDate(date2.SelectionStart) + " " + "GROUP BY procedurelog.ProcNum " + "ORDER BY procedurelog.ProcDate,plfname"; FormQuery2 = new FormQuery(); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "Daily Procedures"; Queries.CurReport.SubTitle = new string[2]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"); // col[5] from 590 to 640, 680, 770, spk 7/20/04 Queries.CurReport.ColPos = new int[9]; Queries.CurReport.ColCaption = new string[8]; Queries.CurReport.ColAlign = new HorizontalAlignment[8]; Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 100; Queries.CurReport.ColPos[2] = 250; Queries.CurReport.ColPos[3] = 325; Queries.CurReport.ColPos[4] = 370; Queries.CurReport.ColPos[5] = 640; Queries.CurReport.ColPos[6] = 680; Queries.CurReport.ColPos[7] = 770; Queries.CurReport.ColPos[8] = 820; // spk Queries.CurReport.ColCaption[0] = "Date"; Queries.CurReport.ColCaption[1] = "Patient Name"; Queries.CurReport.ColCaption[2] = "ADA Code"; Queries.CurReport.ColCaption[3] = "Tooth"; Queries.CurReport.ColCaption[4] = "Description"; Queries.CurReport.ColCaption[5] = "Provider"; Queries.CurReport.ColCaption[6] = "Fee"; Queries.CurReport.ColAlign[6] = HorizontalAlignment.Right; Queries.CurReport.ColCaption[7] = " "; // spk Queries.CurReport.Summary = new string[0]; FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { Queries.CurReport = new ReportOld(); //if(radioRange.Checked){ Queries.CurReport.Query = "SELECT CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI)," + "procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee " + "FROM patient,procedurecode,procedurelog,claimproc " + "WHERE claimproc.procnum=procedurelog.procnum " + "AND patient.PatNum=procedurelog.PatNum " + "AND procedurelog.CodeNum=procedurecode.CodeNum " + "AND claimproc.NoBillIns=0 " + "AND procedurelog.ProcFee>0 " + "AND claimproc.Status=6 "//estimate + "AND procedurelog.procstatus=2 " + "AND procedurelog.ProcDate >= " + POut.PDate(date1.SelectionStart) + " " + "AND procedurelog.ProcDate <= " + POut.PDate(date2.SelectionStart) + " " + "GROUP BY procedurelog.ProcNum"; /*} * else{ * Queries.CurReport.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(); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "Procedures Not Billed to Insurance"; Queries.CurReport.SubTitle = new string[3]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"); Queries.CurReport.ColPos = new int[5]; Queries.CurReport.ColCaption = new string[4]; Queries.CurReport.ColAlign = new HorizontalAlignment[4]; Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 205; Queries.CurReport.ColPos[2] = 390; Queries.CurReport.ColPos[3] = 575; Queries.CurReport.ColPos[4] = 760; Queries.CurReport.ColCaption[0] = "Patient Name"; Queries.CurReport.ColCaption[1] = "Procedure Date"; Queries.CurReport.ColCaption[2] = "Procedure Description"; Queries.CurReport.ColCaption[3] = "Procedure Amount"; Queries.CurReport.ColAlign[3] = HorizontalAlignment.Right; Queries.CurReport.Summary = new string[3]; FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { ReportSimpleGrid report = new ReportSimpleGrid(); //if(radioRange.Checked){ report.Query = "SELECT "; if (PrefC.GetBool(PrefName.ReportsShowPatNum)) { report.Query += DbHelper.Concat("CAST(patient.PatNum AS CHAR)", "'-'", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI"); } else { report.Query += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI"); } report.Query += " AS 'PatientName',procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee " + "FROM patient,procedurecode,procedurelog,claimproc,insplan " + "WHERE claimproc.procnum=procedurelog.procnum " + "AND patient.PatNum=procedurelog.PatNum " + "AND procedurelog.CodeNum=procedurecode.CodeNum " + "AND claimproc.PlanNum=insplan.PlanNum " + "AND insplan.IsMedical=0 " + "AND claimproc.NoBillIns=0 " + "AND procedurelog.ProcFee>0 " + "AND claimproc.Status=6 " //estimate + "AND procedurelog.procstatus=2 " + "AND procedurelog.ProcDate >= " + POut.Date(date1.SelectionStart) + " " + "AND procedurelog.ProcDate <= " + POut.Date(date2.SelectionStart) + " " + "GROUP BY procedurelog.ProcNum " + "ORDER BY patient.LName,patient.FName"; /*} * else{ * report.Query="SELECT CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI)," +"procedurelog.ProcDate,procedurecode.Descript,procedurelog.ProcFee FROM patient,procedurecode," +"procedurelog LEFT JOIN claimproc ON claimproc.procnum = procedurelog.procnum " +"WHERE claimproc.procnum IS NULL " +"&& patient.patnum=procedurelog.patnum && procedurelog.codenum=procedurecode.codenum " +"&& patient.priplannum > 0 " +"&& procedurelog.nobillins = 0 && procedurelog.procstatus = 2 " +"&& procedurelog.ProcDate = '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"'"; * }*/ FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); report.Title = "Procedures Not Billed to Insurance"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SubTitle.Add(date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d")); report.SetColumn(this, 0, "Patient Name", 185); report.SetColumn(this, 1, "Procedure Date", 185); report.SetColumn(this, 2, "Procedure Description", 185); report.SetColumn(this, 3, "Procedure Amount", 185, HorizontalAlignment.Right); FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { /* * SELECT CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI),rxpat.rxdate, * rxpat.drug,rxpat.sig,rxpat.disp,provider.abbr FROM patient,rxpat,provider * WHERE patient.patnum=rxpat.patnum && provider.provnum=rxpat.provnum */ ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = "SELECT CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName)," + "' '),patient.MiddleI),rxpat.rxdate," + "rxpat.drug,rxpat.sig,rxpat.disp,provider.abbr FROM patient,rxpat,provider " + "WHERE patient.patnum=rxpat.patnum AND provider.provnum=rxpat.provnum "; if (radioPatient.Checked == true) { report.Query += "AND patient.lname like '" + textBoxInput.Text + "%'" + " ORDER BY patient.lname,patient.fname,rxpat.rxdate"; } else { report.Query += "AND rxpat.drug like '" + textBoxInput.Text + "%'" + " ORDER BY patient.lname,rxpat.drug,rxpat.rxdate"; } FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); report.Title = "Prescriptions"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); if (radioPatient.Checked == true) { report.SubTitle.Add("By Patient"); } else { report.SubTitle.Add("By Drug"); } report.SetColumn(this, 0, "Patient Name", 120); report.SetColumn(this, 1, "Date", 95); report.SetColumn(this, 2, "Drug Name", 100); report.SetColumn(this, 3, "Sig", 300); report.SetColumn(this, 4, "Disp", 100); report.SetColumn(this, 5, "Prov Name", 100); FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { carrier = PIn.PString(textBoxCarrier.Text); Queries.CurReport = new ReportOld(); /* * 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 * */ Queries.CurReport.Query = "SELECT carrier.CarrierName" + ",CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),carrier.Phone," + "insplan.Groupname " + "FROM insplan,patient,carrier,patplan " + "WHERE insplan.Subscriber=patient.PatNum " + "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(Queries.CurReport.Query); FormQuery2 = new FormQuery(); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "Insurance Plan List"; Queries.CurReport.SubTitle = new string[2]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.ColPos = new int[5]; Queries.CurReport.ColCaption = new string[4]; Queries.CurReport.ColAlign = new HorizontalAlignment[4]; Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 250; Queries.CurReport.ColPos[2] = 425; Queries.CurReport.ColPos[3] = 600; Queries.CurReport.ColPos[4] = 765; Queries.CurReport.ColCaption[0] = "Carrier Name"; Queries.CurReport.ColCaption[1] = "Subscriber Name"; Queries.CurReport.ColCaption[2] = "Carrier Phone#"; Queries.CurReport.ColCaption[3] = "Group Name"; Queries.CurReport.Summary = new string[1]; Queries.CurReport.Summary[0] = Lan.g(this, "Total: ") + Queries.TableQ.Rows.Count.ToString(); FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
private void CreateGrouped() { //this would require a temporary table to be able to handle capitation. Queries.CurReport.Query = "SELECT definition.ItemName,procedurelog.ADACode,procedurecode.Descript," + "Count(*),AVG(procedurelog.ProcFee) $AvgFee,SUM(procedurelog.ProcFee) AS $TotFee " + "FROM procedurelog,procedurecode,definition " + "WHERE procedurelog.ProcStatus = '2' " + "AND procedurelog.ADACode=procedurecode.ADACode " + "AND definition.DefNum=procedurecode.ProcCat " + "AND " + whereProv + " " + "AND procedurelog.ADACode LIKE '%" + POut.PString(textADACode.Text) + "%' " + "AND procedurelog.ProcDate >= '" + date1.SelectionStart.ToString("yyyy-MM-dd") + "' " + "AND procedurelog.ProcDate <= '" + date2.SelectionStart.ToString("yyyy-MM-dd") + "' " + "GROUP BY procedurelog.ADACode " + "ORDER BY definition.ItemOrder,procedurelog.ADACode"; FormQuery2 = new FormQuery(); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "Procedures By Procedure Code"; Queries.CurReport.SubTitle = new string[2]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"); Queries.CurReport.ColPos = new int[7]; Queries.CurReport.ColCaption = new string[6]; Queries.CurReport.ColAlign = new HorizontalAlignment[6]; Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 170; Queries.CurReport.ColPos[2] = 260; Queries.CurReport.ColPos[3] = 440; Queries.CurReport.ColPos[4] = 490; Queries.CurReport.ColPos[5] = 600; Queries.CurReport.ColPos[6] = 700; Queries.CurReport.ColCaption[0] = "Category"; Queries.CurReport.ColCaption[1] = "ADA Code"; Queries.CurReport.ColCaption[2] = "Description"; Queries.CurReport.ColCaption[3] = "Quantity"; Queries.CurReport.ColCaption[4] = "Average Fee"; Queries.CurReport.ColCaption[5] = "Total Fees"; Queries.CurReport.ColAlign[3] = HorizontalAlignment.Right; Queries.CurReport.ColAlign[4] = HorizontalAlignment.Right; Queries.CurReport.ColAlign[5] = HorizontalAlignment.Right; //Queries.CurReport.ColCaption[7]=" "; Queries.CurReport.Summary = new string[0]; 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) { //if(textDate.errorProvider1.GetError(textDate)!=""){ // MessageBox.Show(Lan.g(this,"Please fix data entry errors first.")); // return; //} Queries.CurReport = new ReportOld(); Queries.CurReport.Query = "SELECT CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),adjamt " + "FROM patient,adjustment " + "WHERE patient.patnum=adjustment.patnum " + "AND adjustment.adjdate = '" + ((Pref)PrefB.HList["FinanceChargeLastRun"]).ValueString + "'" + "AND adjustment.adjtype = '" + ((Pref)PrefB.HList["FinanceChargeAdjustmentType"]).ValueString + "'"; FormQuery2 = new FormQuery(); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "FINANCE CHARGE REPORT"; Queries.CurReport.SubTitle = new string[4]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.SubTitle[1] = "Date of Charges: " + PIn.PDate(((Pref)PrefB.HList["FinanceChargeLastRun"]).ValueString).ToShortDateString(); //Queries.CurReport.SubTitle[2]="Adjustment type: "+PIn.PDate(textDate.Text).ToShortDateString(); Queries.CurReport.ColPos = new int[3]; Queries.CurReport.ColCaption = new string[2]; Queries.CurReport.ColAlign = new HorizontalAlignment[2]; Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 200; Queries.CurReport.ColPos[2] = 300; Queries.CurReport.ColCaption[0] = "Patient Name"; Queries.CurReport.ColCaption[1] = "Amount"; Queries.CurReport.ColAlign[1] = HorizontalAlignment.Right; Queries.CurReport.Summary = new string[0]; 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 (listProv.SelectedIndices.Count == 0) { MsgBox.Show(this, "At least one provider must be selected."); return; } if (listType.SelectedIndices.Count == 0) { MsgBox.Show(this, "At least one type must be selected."); return; } string whereProv = "("; for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i > 0) { whereProv += "OR "; } whereProv += "adjustment.ProvNum = '" + POut.PInt(Providers.List[listProv.SelectedIndices[i]].ProvNum) + "' "; } whereProv += ")"; string whereType = "("; for (int i = 0; i < listType.SelectedIndices.Count; i++) { if (i > 0) { whereType += "OR "; } whereType += "adjustment.AdjType = '" + POut.PInt(DefB.Short[(int)DefCat.AdjTypes][listType.SelectedIndices[i]].DefNum) + "' "; } whereType += ")"; Queries.CurReport = new ReportOld(); Queries.CurReport.Query = "SELECT adjustment.AdjDate," + "CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI)," + "definition.ItemName,adjustment.AdjNote,adjustment.AdjAmt FROM " + "adjustment,patient,definition WHERE adjustment.AdjType=definition.DefNum " + "AND patient.PatNum=adjustment.PatNum " + "AND " + whereProv + " " + "AND " + whereType + " " + "AND adjustment.AdjDate >= " + POut.PDate(date1.SelectionStart) + " " + "AND adjustment.AdjDate <= " + POut.PDate(date2.SelectionStart); Queries.CurReport.Query += " ORDER BY adjustment.AdjDate"; FormQuery2 = new FormQuery(); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "Daily Adjustments"; Queries.CurReport.SubTitle = new string[2]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"); Queries.CurReport.ColPos = new int[6]; Queries.CurReport.ColCaption = new string[5]; Queries.CurReport.ColAlign = new HorizontalAlignment[5]; Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 120; Queries.CurReport.ColPos[2] = 270; Queries.CurReport.ColPos[3] = 495; Queries.CurReport.ColPos[4] = 645; Queries.CurReport.ColPos[5] = 720; Queries.CurReport.ColCaption[0] = "Date"; Queries.CurReport.ColCaption[1] = "Patient Name"; Queries.CurReport.ColCaption[2] = "Adjustment Type"; Queries.CurReport.ColCaption[3] = "Adjustment Note"; Queries.CurReport.ColCaption[4] = "Amount"; Queries.CurReport.ColAlign[4] = HorizontalAlignment.Right; Queries.CurReport.Summary = new string[0]; 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) { int feeSched = DefB.Short[(int)DefCat.FeeSchedNames][listFeeSched.SelectedIndex].DefNum; string catName = ""; //string to hold current category name Fees fee = new Fees(); Queries.CurReport = new ReportOld(); Queries.CurReport.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(); FormQuery2.IsReport = true; if (radioCode.Checked == true) { FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "Procedure Codes"; Queries.CurReport.SubTitle = new string[2]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.SubTitle[1] = DefB.GetName(DefCat.FeeSchedNames, feeSched); Queries.CurReport.ColPos = new int[6]; Queries.CurReport.ColCaption = new string[5]; Queries.CurReport.ColAlign = new HorizontalAlignment[5]; Queries.CurReport.ColPos[0] = 60; Queries.CurReport.ColPos[1] = 130; Queries.CurReport.ColPos[2] = 200; Queries.CurReport.ColPos[3] = 220; Queries.CurReport.ColPos[4] = 420; Queries.CurReport.ColPos[5] = 620; Queries.CurReport.ColCaption[0] = "Code"; Queries.CurReport.ColCaption[1] = "Fee Amount"; Queries.CurReport.ColCaption[2] = " "; //otherwise, the amount gets bunched up next to the description. Queries.CurReport.ColCaption[3] = "Description"; Queries.CurReport.ColCaption[4] = "Abbr Description"; //Queries.CurReport.ColCaption[3]="Fee Amount"; Queries.CurReport.ColAlign[1] = HorizontalAlignment.Right; Queries.CurReport.Summary = new string[0]; FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; } else //categories { Queries.SubmitTemp(); //create TableTemp which is not actually used ProcedureCode[] ProcList = ProcedureCodes.GetProcList(); Queries.TableQ = new DataTable(null); for (int i = 0; i < 5; i++) //add columns { Queries.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns } Queries.CurReport.ColTotal = new double[Queries.TableQ.Columns.Count]; DataRow row = Queries.TableQ.NewRow();//add first row by hand to get value for temp row[0] = DefB.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"); Queries.CurReport.ColTotal[4] += PIn.PDouble(row[4].ToString()); Queries.TableQ.Rows.Add(row); for (int i = 1; i < ProcList.Length; i++) //loop through data rows { row = Queries.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ row[0] = DefB.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"); //Queries.CurReport.ColTotal[4]+=PIn.PDouble(row[4].ToString()); Queries.TableQ.Rows.Add(row); } Queries.CurReport.ColWidth = new int[Queries.TableQ.Columns.Count]; Queries.CurReport.ColPos = new int[Queries.TableQ.Columns.Count + 1]; Queries.CurReport.ColPos[0] = 0; Queries.CurReport.ColCaption = new string[Queries.TableQ.Columns.Count]; Queries.CurReport.ColAlign = new HorizontalAlignment[Queries.TableQ.Columns.Count]; FormQuery2.ResetGrid(); //this is a method in FormQuery2; Queries.CurReport.Title = "Procedure Codes"; Queries.CurReport.SubTitle = new string[5]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.SubTitle[1] = DefB.GetName(DefCat.FeeSchedNames, feeSched); Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 120; Queries.CurReport.ColPos[2] = 270; Queries.CurReport.ColPos[3] = 470; Queries.CurReport.ColPos[4] = 620; Queries.CurReport.ColPos[5] = 770; Queries.CurReport.ColCaption[0] = "Category"; Queries.CurReport.ColCaption[1] = "Code"; Queries.CurReport.ColCaption[2] = "Description"; Queries.CurReport.ColCaption[3] = "Abbr Description"; Queries.CurReport.ColCaption[4] = "Fee Amount"; Queries.CurReport.ColAlign[4] = HorizontalAlignment.Right; Queries.CurReport.Summary = new string[5]; FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; } }
/*private void butToday_Click(object sender, System.EventArgs e) { * textDateFrom.Text=DateTime.Today.ToShortDateString(); * textDateTo.Text=DateTime.Today.ToShortDateString(); * } * * private void butLeft_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; || } || DateTime dateFrom=PIn.PDate(textDateFrom.Text); || DateTime dateTo=PIn.PDate(textDateTo.Text); || textDateFrom.Text=dateFrom.AddDays(-1).ToShortDateString(); || textDateTo.Text=dateTo.AddDays(-1).ToShortDateString(); ||} || ||private void butRight_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; || } || DateTime dateFrom=PIn.PDate(textDateFrom.Text); || DateTime dateTo=PIn.PDate(textDateTo.Text); || textDateFrom.Text=dateFrom.AddDays(1).ToShortDateString(); || textDateTo.Text=dateTo.AddDays(1).ToShortDateString(); ||}*/ private void butOK_Click(object sender, System.EventArgs e) { /* * SELECT PayDate,CONCAT(patient.LName,',',patient.FName,' ',patient.MiddleI),CheckNum,BankBranch,PayAmt * FROM payment,patient * WHERE payment.PatNum=patient.PatNum * GROUP BY PayDate * UNION * SELECT CheckDate,insplan.Carrier,CheckNum,BankBranch,CheckAmt * FROM claimproc,claimpayment,insplan * WHERE claimproc.ClaimPaymentNum=claimpayment.ClaimPaymentNum && claimproc.PlanNum=insplan.PlanNum * GROUP BY CheckDate * ORDER BY PayDate */ /* * SELECT PayDate,CONCAT(patient.LName,', ',patient.FName,' ', * patient.MiddleI) AS plfname,' ',PayType, * PayNum,CheckNum,BankBranch,PayAmt * FROM payment,patient WHERE * payment.PatNum = patient.PatNum AND ( * PayType = '69' * ) AND PayDate = '2005-01-10' * UNION SELECT CheckDate,CONCAT(patient.LName,', ',patient.FName,' ', * patient.MiddleI) AS plfname,CarrierName,'Ins', * claimpayment.ClaimPaymentNum, * CheckNum,BankBranch,CheckAmt * FROM claimpayment,claimproc,insplan,carrier,patient * WHERE claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum * AND claimproc.PlanNum = insplan.PlanNum * AND claimproc.PatNum=patient.PatNum * AND insplan.CarrierNum = carrier.CarrierNum * AND (claimproc.status = '1' OR claimproc.status = '4') * AND CheckDate = '2005-01-10' * ORDER BY PayDate, plfname */ if (listPayType.SelectedIndices.Count == 0 && !checkBoxIns.Checked) { MessageBox.Show("Must either select a payment type and/or include insurance checks."); return; } Queries.CurReport = new ReportOld(); string cmd = ""; //if(checkBoxIns.Checked){ cmd = "SELECT PayDate,CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' ')," + "patient.MiddleI) AS plfname,' ',PayType," + "PayNum,CheckNum,BankBranch,PayAmt " + "FROM payment,patient WHERE "; //added plfname,paynum spk 4/14/04 if (listPayType.SelectedIndices.Count == 0) { cmd += "1=0 "; //none } else { cmd += "payment.PatNum = patient.PatNum AND ("; for (int i = 0; i < listPayType.SelectedIndices.Count; i++) { if (i > 0) { cmd += " OR "; } cmd += "PayType = '" + DefB.Short[(int)DefCat.PaymentTypes][listPayType.SelectedIndices[i]].DefNum + "'"; } cmd += ") AND PayDate >= " + POut.PDate(monthCal1.SelectionStart) + " " + "AND PayDate <= " + POut.PDate(monthCal2.SelectionStart) + " "; if (!PrefB.GetBool("EasyNoClinics")) { if (comboClinic.SelectedIndex == 0) { cmd += "AND payment.ClinicNum=0 "; } else { cmd += "AND payment.ClinicNum=" + POut.PInt(Clinics.List[comboClinic.SelectedIndex - 1].ClinicNum) + " "; } } } if (checkBoxIns.Checked) { cmd += "UNION SELECT CheckDate,CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' ')," + "patient.MiddleI) AS plfname,CarrierName,'Ins'," + "claimpayment.ClaimPaymentNum," + "CheckNum,BankBranch,CheckAmt " //spk added claimpaymentnum //+"claimpayment.ClaimPaymentNum " + "FROM claimpayment,claimproc,insplan,carrier,patient " + "WHERE claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum " + "AND claimproc.PlanNum = insplan.PlanNum " + "AND claimproc.PatNum=patient.PatNum " + "AND insplan.CarrierNum = carrier.CarrierNum " + "AND (claimproc.status = '1' OR claimproc.status = '4') " + "AND CheckDate >= " + POut.PDate(monthCal1.SelectionStart) + " " + "AND CheckDate <= " + POut.PDate(monthCal2.SelectionStart) + " "; //added plfname,spk 4/30/04 if (!PrefB.GetBool("EasyNoClinics")) { if (comboClinic.SelectedIndex == 0) { cmd += "AND claimpayment.ClinicNum=0 "; } else { cmd += "AND claimpayment.ClinicNum=" + POut.PInt(Clinics.List[comboClinic.SelectedIndex - 1].ClinicNum) + " "; } } cmd += "GROUP BY claimpayment.ClaimPaymentNum "; //MessageBox.Show(Queries.CurReport.Query); } //cmd+="ORDER BY PayDate, plfname";//FIXME:UNION-ORDER-BY cmd += "ORDER BY 1, 2"; Queries.CurReport.Query = cmd; FormQuery2 = new FormQuery(); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "Deposit Slip"; Queries.CurReport.SubTitle = new string[3]; if (!PrefB.GetBool("EasyNoClinics")) { Queries.CurReport.SubTitle = new string[4]; if (comboClinic.SelectedIndex == 0) { Queries.CurReport.SubTitle[3] = Lan.g(this, "Clinic") + ": none"; } else { Queries.CurReport.SubTitle[3] = Lan.g(this, "Clinic") + ": " + Clinics.List[comboClinic.SelectedIndex - 1].Description; } } Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.SubTitle[1] = monthCal1.SelectionStart.ToShortDateString() + " - " + monthCal2.SelectionStart.ToShortDateString(); if (listPayType.SelectedIndices.Count > 0) { Queries.CurReport.SubTitle[2] = "Payment Type(s): "; for (int i = 0; i < listPayType.SelectedIndices.Count; i++) { if (i > 0) { Queries.CurReport.SubTitle[2] += ", "; } Queries.CurReport.SubTitle[2] += DefB.Short[(int)DefCat.PaymentTypes][listPayType.SelectedIndices[i]].ItemName; } if (checkBoxIns.Checked) { Queries.CurReport.SubTitle[2] += " Insurance Claim Checks"; } } else { if (checkBoxIns.Checked) { Queries.CurReport.SubTitle[2] = "Payment Type: Insurance Claim Checks"; } } Queries.CurReport.ColPos = new int[9]; Queries.CurReport.ColCaption = new string[8]; Queries.CurReport.ColAlign = new HorizontalAlignment[8]; Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 100; Queries.CurReport.ColPos[2] = 210; Queries.CurReport.ColPos[3] = 320; Queries.CurReport.ColPos[4] = 420; Queries.CurReport.ColPos[5] = 480; Queries.CurReport.ColPos[6] = 590; Queries.CurReport.ColPos[7] = 680; Queries.CurReport.ColPos[8] = 760; Queries.CurReport.ColCaption[0] = "Date"; Queries.CurReport.ColCaption[1] = "Patient"; Queries.CurReport.ColCaption[2] = "Carrier"; Queries.CurReport.ColCaption[3] = "Type"; //this column can be eliminated when the new reporting framework is complete: Queries.CurReport.ColCaption[4] = "Pay #"; Queries.CurReport.ColCaption[5] = "Check Number"; Queries.CurReport.ColCaption[6] = "Bank-Branch"; Queries.CurReport.ColCaption[7] = "Amount"; //Queries.CurReport.ColAlign[4]=HorizontalAlignment.Right; Queries.CurReport.ColAlign[7] = HorizontalAlignment.Right; if (PrefB.GetBool("EasyNoClinics") || comboClinic.SelectedIndex == 0) { Queries.CurReport.Summary = new string[3]; Queries.CurReport.Summary[0] = "For Deposit to Account of " + ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.Summary[2] = "Account number: " + ((Pref)PrefB.HList["PracticeBankNumber"]).ValueString; } else { Queries.CurReport.Summary = new string[3]; Queries.CurReport.Summary[0] = "For Deposit to Account of " + Clinics.List[comboClinic.SelectedIndex - 1].Description; Queries.CurReport.Summary[2] = "Account number: " + Clinics.List[comboClinic.SelectedIndex - 1].BankNumber; } 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; } Queries.CurReport = new ReportOld(); Queries.CurReport.Query = "SELECT CONCAT(CONCAT(CONCAT(CONCAT(LName,', '),FName),' '),MiddleI)" + ",Bal_0_30,Bal_31_60,Bal_61_90,BalOver90" + ",BalTotal " + ",InsEst" + ",BalTotal-InsEst AS $pat " + "FROM patient "; if (checkOnlyNeg.Checked) { Queries.CurReport.Query += "WHERE BalTotal < '-.005'"; } else { Queries.CurReport.Query += "WHERE "; if (checkExcludeInactive.Checked) { Queries.CurReport.Query += "(patstatus != 2) AND "; } if (radioAny.Checked) { Queries.CurReport.Query += "(Bal_0_30 > '.005' OR Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'"; } else if (radio30.Checked) { Queries.CurReport.Query += "(Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'"; } else if (radio60.Checked) { Queries.CurReport.Query += "(Bal_61_90 > '.005' OR BalOver90 > '.005'"; } else if (radio90.Checked) { Queries.CurReport.Query += "(BalOver90 > '.005'"; } if (checkIncludeNeg.Checked) { Queries.CurReport.Query += " OR BalTotal < '-.005'"; } Queries.CurReport.Query += ") "; } if (!checkBillTypesAll.Checked) { for (int i = 0; i < listBillType.SelectedIndices.Count; i++) { if (i == 0) { Queries.CurReport.Query += " AND (billingtype = "; } else { Queries.CurReport.Query += " OR billingtype = "; } Queries.CurReport.Query += POut.PInt(DefB.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].DefNum); } Queries.CurReport.Query += ") "; } if (!checkProvAll.Checked) { for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i == 0) { Queries.CurReport.Query += " AND (PriProv = "; } else { Queries.CurReport.Query += " OR PriProv = "; } Queries.CurReport.Query += POut.PInt(Providers.List[listProv.SelectedIndices[i]].ProvNum); } Queries.CurReport.Query += ") "; } Queries.CurReport.Query += "ORDER BY LName,FName"; FormQuery2 = new FormQuery(); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "AGING REPORT"; Queries.CurReport.SubTitle = new string[4]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.SubTitle[1] = "As of " + textDate.Text; if (radioAny.Checked) { Queries.CurReport.SubTitle[2] = "Any Balance"; } if (radio30.Checked) { Queries.CurReport.SubTitle[2] = "Over 30 Days"; } if (radio60.Checked) { Queries.CurReport.SubTitle[2] = "Over 60 Days"; } if (radio90.Checked) { Queries.CurReport.SubTitle[2] = "Over 90 Days"; } if (listBillType.SelectedIndices.Count == DefB.Short[(int)DefCat.BillingTypes].Length) { Queries.CurReport.SubTitle[3] = "All Billing Types"; } else { Queries.CurReport.SubTitle[3] = DefB.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[0]].ItemName; for (int i = 1; i < listBillType.SelectedIndices.Count; i++) { Queries.CurReport.SubTitle[3] += ", " + DefB.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].ItemName; } } Queries.CurReport.ColPos = new int[9]; Queries.CurReport.ColCaption = new string[8]; Queries.CurReport.ColAlign = new HorizontalAlignment[8]; Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 180; Queries.CurReport.ColPos[2] = 260; Queries.CurReport.ColPos[3] = 340; Queries.CurReport.ColPos[4] = 420; Queries.CurReport.ColPos[5] = 500; Queries.CurReport.ColPos[6] = 585; Queries.CurReport.ColPos[7] = 670; Queries.CurReport.ColPos[8] = 755; Queries.CurReport.ColCaption[0] = "GUARANTOR"; Queries.CurReport.ColCaption[1] = "0-30 DAYS"; Queries.CurReport.ColCaption[2] = "30-60 DAYS"; Queries.CurReport.ColCaption[3] = "60-90 DAYS"; Queries.CurReport.ColCaption[4] = "> 90 DAYS"; Queries.CurReport.ColCaption[5] = "TOTAL"; Queries.CurReport.ColCaption[6] = "-INS EST"; Queries.CurReport.ColCaption[7] = "=PATIENT"; Queries.CurReport.ColAlign[1] = HorizontalAlignment.Right; Queries.CurReport.ColAlign[2] = HorizontalAlignment.Right; Queries.CurReport.ColAlign[3] = HorizontalAlignment.Right; Queries.CurReport.ColAlign[4] = HorizontalAlignment.Right; Queries.CurReport.ColAlign[5] = HorizontalAlignment.Right; Queries.CurReport.ColAlign[6] = HorizontalAlignment.Right; Queries.CurReport.ColAlign[7] = HorizontalAlignment.Right; Queries.CurReport.Summary = new string[0]; 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 */ Queries.CurReport = new ReportOld(); Queries.CurReport.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) { Queries.CurReport.Query += "AND patient.lname like '" + textBoxInput.Text + "%'" + " ORDER BY patient.lname,patient.fname,rxpat.rxdate"; } else { Queries.CurReport.Query += "AND rxpat.drug like '" + textBoxInput.Text + "%'" + " ORDER BY patient.lname,rxpat.drug,rxpat.rxdate"; } FormQuery2 = new FormQuery(); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "Prescriptions"; Queries.CurReport.SubTitle = new string[2]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; if (radioPatient.Checked == true) { Queries.CurReport.SubTitle[1] = "By Patient"; } else { Queries.CurReport.SubTitle[1] = "By Drug"; } Queries.CurReport.ColPos = new int[7]; Queries.CurReport.ColCaption = new string[6]; Queries.CurReport.ColAlign = new HorizontalAlignment[6]; Queries.CurReport.ColPos[0] = 10; Queries.CurReport.ColPos[1] = 130; Queries.CurReport.ColPos[2] = 225; Queries.CurReport.ColPos[3] = 325; Queries.CurReport.ColPos[4] = 625; Queries.CurReport.ColPos[5] = 725; Queries.CurReport.ColPos[6] = 825; Queries.CurReport.ColCaption[0] = "Patient Name"; Queries.CurReport.ColCaption[1] = "Date"; Queries.CurReport.ColCaption[2] = "Drug Name"; Queries.CurReport.ColCaption[3] = "Sig"; Queries.CurReport.ColCaption[4] = "Disp"; Queries.CurReport.ColCaption[5] = "Prov Name"; Queries.CurReport.Summary = new string[0]; 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 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 (!checkBillTypesAll.Checked && listBillType.SelectedIndices.Count == 0) { MsgBox.Show(this, "At least one billing type must be selected."); return; } if (!checkProvAll.Checked && listProv.SelectedIndices.Count == 0) { MsgBox.Show(this, "At least one provider must be selected."); return; } if (textDate.errorProvider1.GetError(textDate) != "") { MsgBox.Show(this, "Invalid date."); return; } DateTime asOfDate = PIn.Date(textDate.Text); //The aging report always show historical numbers based on the date entered. Ledgers.ComputeAging(0, asOfDate, true); ReportSimpleGrid report = new ReportSimpleGrid(); string cmd = "SELECT "; if (PrefC.GetBool(PrefName.ReportsShowPatNum)) { cmd += DbHelper.Concat("Cast(PatNum AS CHAR)", "'-'", "LName", "', '", "FName", "' '", "MiddleI"); } else { cmd += DbHelper.Concat("LName", "', '", "FName", "' '", "MiddleI"); } cmd += ",Bal_0_30,Bal_31_60,Bal_61_90,BalOver90" + ",BalTotal " + ",InsEst" + ",BalTotal-InsEst AS "; //\"$pat\" "; if (DataConnection.DBtype == DatabaseType.MySql) { cmd += "$pat "; } else //Oracle needs quotes. { cmd += "\"$pat\" "; } cmd += "FROM patient " + "WHERE "; if (checkExcludeInactive.Checked) { cmd += "(patstatus != 2) AND "; } if (checkBadAddress.Checked) { cmd += "(zip !='') AND "; } if (checkOnlyNeg.Checked) { cmd += "BalTotal < '-.005' "; } else { if (radioAny.Checked) { cmd += "(Bal_0_30 > '.005' OR Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'"; } else if (radio30.Checked) { cmd += "(Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'"; } else if (radio60.Checked) { cmd += "(Bal_61_90 > '.005' OR BalOver90 > '.005'"; } else if (radio90.Checked) { cmd += "(BalOver90 > '.005'"; } if (checkIncludeNeg.Checked) { cmd += " OR BalTotal < '-.005'"; } cmd += ") "; } if (!checkBillTypesAll.Checked) { for (int i = 0; i < listBillType.SelectedIndices.Count; i++) { if (i == 0) { cmd += " AND (billingtype = "; } else { cmd += " OR billingtype = "; } cmd += POut.Long(DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].DefNum); } cmd += ") "; } if (!checkProvAll.Checked) { for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i == 0) { cmd += " AND (PriProv = "; } else { cmd += " OR PriProv = "; } cmd += POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum); } cmd += ") "; } cmd += "ORDER BY LName,FName"; report.Query = cmd; FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); //Recompute aging in a non-historical way, so that the numbers are returned to the way they //are normally used in other parts of the program. Ledgers.RunAging(); //if(Prefs.UpdateString(PrefName.DateLastAging",POut.PDate(asOfDate,false))) { // DataValid.SetInvalid(InvalidType.Prefs); //} report.Title = "AGING REPORT"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); report.SubTitle.Add("As of " + textDate.Text); if (radioAny.Checked) { report.SubTitle.Add("Any Balance"); } if (radio30.Checked) { report.SubTitle.Add("Over 30 Days"); } if (radio60.Checked) { report.SubTitle.Add("Over 60 Days"); } if (radio90.Checked) { report.SubTitle.Add("Over 90 Days"); } if (checkBillTypesAll.Checked) { report.SubTitle.Add("All Billing Types"); } else { string subt = DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[0]].ItemName; for (int i = 1; i < listBillType.SelectedIndices.Count; i++) { subt += ", " + DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].ItemName; } report.SubTitle.Add(subt); } //report.InitializeColumns(8); report.SetColumn(this, 0, "GUARANTOR", 160); report.SetColumn(this, 1, "0-30 DAYS", 80, HorizontalAlignment.Right); report.SetColumn(this, 2, "30-60 DAYS", 80, HorizontalAlignment.Right); report.SetColumn(this, 3, "60-90 DAYS", 80, HorizontalAlignment.Right); report.SetColumn(this, 4, "> 90 DAYS", 80, HorizontalAlignment.Right); report.SetColumn(this, 5, "TOTAL", 85, HorizontalAlignment.Right); report.SetColumn(this, 6, "-INS EST", 85, HorizontalAlignment.Right); report.SetColumn(this, 7, "=PATIENT", 85, HorizontalAlignment.Right); FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { if (listProv.SelectedIndices.Count == 0) { MsgBox.Show(this, "At least one provider must be selected."); return; } if (listPayType.SelectedIndices.Count == 0 && !checkIncludeIns.Checked) { MsgBox.Show(this, "Must either select a payment type and/or include insurance checks."); return; } string whereProv = "("; for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i > 0) { whereProv += "OR "; } whereProv += "paysplit.ProvNum = '" + POut.PInt(Providers.List[listProv.SelectedIndices[i]].ProvNum) + "' "; } whereProv += ")"; string whereType = "("; for (int i = 0; i < listPayType.SelectedIndices.Count; i++) { if (i > 0) { whereType += "OR "; } whereType += "payment.PayType = '" + POut.PInt(DefB.Short[(int)DefCat.PaymentTypes][listPayType.SelectedIndices[i]].DefNum) + "' "; } whereType += ")"; Queries.CurReport = new ReportOld(); Queries.CurReport.Query = "(SELECT " + "paysplit.DatePay AS mydate," //0. Date + "CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) AS plfname," //1. name + "' '," //2. Carrier. this is long so union won't get trunc. + "payment.PayType," //3. paytype + "provider.Abbr," //4. Prov + "payment.CheckNum," //5. CheckNum + "SUM(paysplit.SplitAmt) $amt, " //6. amt + "payment.PayNum " //7. PayNum. Not visible + "FROM payment,patient,provider,paysplit " + "WHERE "; if (listPayType.SelectedIndices.Count == 0) { Queries.CurReport.Query += "1=0 "; //none } else { Queries.CurReport.Query += "payment.PayNum=paysplit.PayNum " + "AND patient.PatNum=paysplit.PatNum " + "AND provider.ProvNum=paysplit.ProvNum " + "AND " + whereProv + " " + "AND " + whereType + " " + "AND paysplit.DatePay >= " + POut.PDate(date1.SelectionStart) + " " + "AND paysplit.DatePay <= " + POut.PDate(date2.SelectionStart) + " "; } Queries.CurReport.Query += "GROUP BY payment.PayNum,patient.PatNum,provider.ProvNum)"; if (checkIncludeIns.Checked) { whereProv = "("; for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i > 0) { whereProv += "OR "; } whereProv += "claimproc.ProvNum = '" + POut.PInt(Providers.List[listProv.SelectedIndices[i]].ProvNum) + "' "; } whereProv += ")"; Queries.CurReport.Query += " UNION (" + "SELECT claimproc.DateCP AS mydate," //0. Date + "CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) AS plfname," //1. Name + "carrier.CarrierName," //2. Carrier + "''," //3. PayType + "provider.Abbr," //4. Prov + "claimpayment.CheckNum," //5. CheckNum + "SUM(claimproc.InsPayAmt), " //6. Amt + "claimproc.ClaimNum " //7. Num(not visible) + "FROM claimproc,insplan,patient,carrier,provider,claimpayment " + "WHERE claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum " + "AND provider.ProvNum=claimproc.ProvNum " + "AND claimproc.PlanNum = insplan.PlanNum " + "AND claimproc.PatNum = patient.PatNum " + "AND carrier.CarrierNum = insplan.CarrierNum " + "AND " + whereProv + " " + "AND (claimproc.Status=1 OR claimproc.Status=4) " //received or supplemental + "AND claimpayment.CheckDate >= " + POut.PDate(date1.SelectionStart) + " " + "AND claimpayment.CheckDate <= " + POut.PDate(date2.SelectionStart) + " "; if (radioPatient.Checked) //by patient { Queries.CurReport.Query += "GROUP BY claimproc.ClaimNum,patient.PatNum,provider.ProvNum "; } else //by check { Queries.CurReport.Query += "GROUP BY claimproc.ClaimPaymentNum,provider.ProvNum "; } Queries.CurReport.Query += ")"; //end of union } //insurance section Queries.CurReport.Query += //" ORDER BY mydate,PayType,plfname";//FIXME:UNION-ORDER-BY " ORDER BY 1,4,2"; FormQuery2 = new FormQuery(); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "Daily Payments"; Queries.CurReport.SubTitle = new string[5]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"); Queries.CurReport.SubTitle[2] = Lan.g(this, "Patient Payment Type(s): "); if (listPayType.SelectedIndices.Count == listPayType.Items.Count) { Queries.CurReport.SubTitle[2] += Lan.g(this, "All"); } else if (listPayType.SelectedIndices.Count == 0) { Queries.CurReport.SubTitle[2] += Lan.g(this, "None"); } else { for (int i = 0; i < listPayType.SelectedIndices.Count; i++) { if (i > 0) { Queries.CurReport.SubTitle[2] += ", "; } Queries.CurReport.SubTitle[2] += DefB.Short[(int)DefCat.PaymentTypes][listPayType.SelectedIndices[i]].ItemName; } } Queries.CurReport.SubTitle[3] = Lan.g(this, "Insurance Payments: "); if (checkIncludeIns.Checked) { Queries.CurReport.SubTitle[3] += "Included"; } else { Queries.CurReport.SubTitle[3] += "Not included"; } Queries.CurReport.SubTitle[4] = Lan.g(this, "Providers: "); if (listProv.SelectedIndices.Count == listProv.Items.Count) { Queries.CurReport.SubTitle[4] += Lan.g(this, "All"); } else { for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i > 0) { Queries.CurReport.SubTitle[4] += ", "; } Queries.CurReport.SubTitle[4] += Providers.List[listProv.SelectedIndices[i]].Abbr; } } Queries.CurReport.ColPos = new int[9]; Queries.CurReport.ColCaption = new string[8]; Queries.CurReport.ColAlign = new HorizontalAlignment[8]; Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 100; Queries.CurReport.ColPos[2] = 200; Queries.CurReport.ColPos[3] = 380; Queries.CurReport.ColPos[4] = 470; Queries.CurReport.ColPos[5] = 540; Queries.CurReport.ColPos[6] = 640; Queries.CurReport.ColPos[7] = 700; Queries.CurReport.ColPos[8] = 900; //off the right edge Queries.CurReport.ColCaption[0] = "Date"; Queries.CurReport.ColCaption[1] = "Patient Name"; Queries.CurReport.ColCaption[2] = "Carrier"; Queries.CurReport.ColCaption[3] = "Payment Type"; Queries.CurReport.ColCaption[4] = "Provider"; Queries.CurReport.ColCaption[5] = "Check #"; Queries.CurReport.ColCaption[6] = "Amount"; Queries.CurReport.ColCaption[7] = "Payment #"; //not shown Queries.CurReport.ColAlign[6] = HorizontalAlignment.Right; Queries.CurReport.ColAlign[7] = HorizontalAlignment.Right; Queries.CurReport.Summary = new string[0]; FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { if (listProv.SelectedIndices.Count == 0) { MsgBox.Show(this, "At least one provider must be selected."); return; } string whereProv = "("; for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i > 0) { whereProv += "OR "; } whereProv += "claimproc.ProvNum = '" + POut.PInt(Providers.List[listProv.SelectedIndices[i]].ProvNum) + "' "; } whereProv += ")"; Queries.CurReport = new ReportOld(); Queries.CurReport.Query = "SET @FromDate=" + POut.PDate(date1.SelectionStart) + ", @ToDate=" + POut.PDate(date2.SelectionStart) + ";"; Queries.CurReport.Query += @"SELECT claimproc.DateCP, CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI), carrier.CarrierName, provider.Abbr, SUM(claimproc.WriteOff), claimproc.ClaimNum FROM claimproc,insplan,patient,carrier,provider WHERE provider.ProvNum = claimproc.ProvNum AND claimproc.PlanNum = insplan.PlanNum AND claimproc.PatNum = patient.PatNum AND carrier.CarrierNum = insplan.CarrierNum AND " + whereProv + @" AND (claimproc.Status=1 OR claimproc.Status=4) /*received or supplemental*/ AND claimproc.DateCP >= @FromDate AND claimproc.DateCP <= @ToDate GROUP BY claimproc.ClaimNum ORDER BY claimproc.DateCP" ; FormQuery2 = new FormQuery(); FormQuery2.IsReport = true; FormQuery2.SubmitReportQuery(); Queries.CurReport.Title = "Daily Writeoffs"; Queries.CurReport.SubTitle = new string[2]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"); Queries.CurReport.ColPos = new int[7]; Queries.CurReport.ColCaption = new string[6]; Queries.CurReport.ColAlign = new HorizontalAlignment[6]; Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 120; Queries.CurReport.ColPos[2] = 270; Queries.CurReport.ColPos[3] = 495; Queries.CurReport.ColPos[4] = 645; Queries.CurReport.ColPos[5] = 720; Queries.CurReport.ColPos[6] = 900; //off the right side Queries.CurReport.ColCaption[0] = "Date"; Queries.CurReport.ColCaption[1] = "Patient Name"; Queries.CurReport.ColCaption[2] = "Carrier"; Queries.CurReport.ColCaption[3] = "Provider"; Queries.CurReport.ColCaption[4] = "Amount"; Queries.CurReport.ColCaption[5] = ""; Queries.CurReport.ColAlign[5] = HorizontalAlignment.Right; Queries.CurReport.Summary = new string[0]; 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; }
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; }