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 butOK_Click(object sender, System.EventArgs e) { if (date2.SelectionStart < date1.SelectionStart) { MsgBox.Show(this, "End date cannot be before start date."); return; } ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = @"SELECT SGDate,ProvName,County,county.CountyCode, site.Description AS schoolName,site.Note AS schoolCode,site.PlaceService,screen.GradeLevel,Age,Birthdate,RaceOld,Gender,Urgency," ; if (!Clinics.IsMedicalPracticeOrClinic(Clinics.ClinicNum)) { report.Query += "HasCaries,EarlyChildCaries,CariesExperience,ExistingSealants,NeedsSealants,MissingAllTeeth,"; } report.Query += @"Comments FROM screen LEFT JOIN screengroup ON screengroup.ScreenGroupNum=screen.ScreenGroupNum LEFT JOIN site ON screengroup.GradeSchool=site.Description LEFT JOIN county ON screengroup.County=county.CountyName WHERE SGDate >= " + POut.Date(date1.SelectionStart) + " " + "AND SGDate <= " + POut.Date(date2.SelectionStart); FormQuery2 = new FormQuery(report); FormQuery2.textTitle.Text = "RawScreeningData" + DateTime.Today.ToString("MMddyyyy"); FormQuery2.SubmitQuery(); FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
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) { 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 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 butUserQuery_Click(object sender,EventArgs e) { if(!Security.IsAuthorized(Permissions.UserQuery)) { return; } if(DataConnection.DBtype==DatabaseType.Oracle) { MsgBox.Show(this,"Not allowed while using Oracle."); return; } FormQuery FormQ; if(Security.IsAuthorized(Permissions.UserQueryAdmin,true)) { FormQ = new FormQuery(null); FormQ.ShowDialog(); SecurityLogs.MakeLogEntry(Permissions.UserQuery,0,""); } else { FormQueryFavorites FormQF = new FormQueryFavorites(); FormQF.ShowDialog(); if(FormQF.DialogResult == DialogResult.OK) { FormQ=new FormQuery(null,true); FormQ.textQuery.Text=FormQF.UserQueryCur.QueryText; FormQ.textTitle.Text=FormQF.UserQueryCur.FileName; SecurityLogs.MakeLogEntry(Permissions.UserQuery,0,Lan.g(this,"User query form accessed.")); FormQ.ShowDialog(); } } }
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 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 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; 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 butPatByZip_Click(object sender, EventArgs e) { if (!DateIsValid()) { return; } ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = "SELECT SUBSTR(Zip,1,5) 'Zip Code',COUNT(*) 'Patients' " //Column headings "Zip Code" and "Patients" are provided by the USD 2010 Manual. + "FROM patient pat " + "WHERE " + DbHelper.Regexp("Zip", "^[0-9]{5}") + " " //Starts with five numbers + "AND PatNum IN ( " + "SELECT PatNum FROM procedurelog " + "WHERE ProcStatus=" + POut.Int((int)ProcStat.C) + " " + "AND DateEntryC >= " + POut.Date(DateFrom) + " " + "AND DateEntryC <= " + POut.Date(DateTo) + ") " + "GROUP BY Zip " + "HAVING COUNT(*) >= 10 " //Has more than 10 patients in that zip code for the given time frame. + "ORDER BY Zip"; FormQuery FormQ = new FormQuery(report); FormQ.IsReport = true; FormQ.SubmitQuery(); FormQ.textQuery.Text = report.Query; report.Title = "Patients By ZIP CODE"; report.SubTitle.Add("From " + DateFrom.ToShortDateString() + " to " + DateTo.ToShortDateString()); report.Summary.Add("Other Zip Codes: " + Patients.GetZipOther(DateFrom, DateTo)); report.Summary.Add("Unknown Residence: " + Patients.GetZipUnknown(DateFrom, DateTo)); report.Summary.Add("TOTAL: " + Patients.GetPatCount(DateFrom, DateTo)); FormQ.ShowDialog(); }
private void 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 butUserQuery_Click(object sender, EventArgs e) { if (!Security.IsAuthorized(Permissions.UserQuery)) { return; } FormQuery FormQuery2 = new FormQuery(); FormQuery2.ShowDialog(); SecurityLogs.MakeLogEntry(Permissions.UserQuery, 0, ""); }
private void btnAlterar_Click(object sender, EventArgs e) { FormQuery fs = new FormQuery(); fs.Icon = this.Icon; fs.OnSearch = this.Search; if (fs.ShowDialog() == System.Windows.Forms.DialogResult.OK) { OnAlterRecord(fs.Grid); } }
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) { Queries.CurReport = new ReportOld(); Queries.CurReport.Query = textSQL.Text; FormQuery2 = new FormQuery(); FormQuery2.IsReport = false; FormQuery2.SubmitQuery(); FormQuery2.textQuery.Text = Queries.CurReport.Query; 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) { ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = textSQL.Text; FormQuery2 = new FormQuery(report); FormQuery2.IsReport = false; FormQuery2.SubmitQuery(); FormQuery2.textQuery.Text = report.Query; 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) { string[] fieldsSelected = new string[listSelect.SelectedItems.Count + listSelect2.SelectedItems.Count]; if (listSelect.SelectedItems.Count == 0 && listSelect2.SelectedItems.Count == 0) { MsgBox.Show(this, "At least one field must be selected."); return; } listSelect.SelectedItems.CopyTo(fieldsSelected, 0); listSelect2.SelectedItems.CopyTo(fieldsSelected, listSelect.SelectedItems.Count); string command = "SELECT "; for (int i = 0; i < fieldsSelected.Length; i++) { if (i > 0) { command += ","; } if (fieldsSelected[i] == "AptDateTime") { command += "appointment." + fieldsSelected[i]; } else { command += "patient." + fieldsSelected[i]; } } command += " FROM patient,appointment " + "WHERE patient.PatNum=appointment.PatNum AND("; for (int i = 0; i < AptNums.Length; i++) { if (i > 0) { command += " OR"; } command += " appointment.AptNum='" + AptNums[i] + "'"; } command += ")"; Queries.CurReport = new ReportOld(); Queries.CurReport.Query = command; FormQuery FormQ = new FormQuery(); FormQ.IsReport = false; FormQ.SubmitQuery(); FormQ.textQuery.Text = Queries.CurReport.Query; FormQ.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 butUserQuery_Click(object sender, EventArgs e) { if (!Security.IsAuthorized(Permissions.UserQuery)) { return; } if (DataConnection.DBtype == DatabaseType.Oracle) { MsgBox.Show(this, "Not allowed while using Oracle."); return; } FormQuery FormQuery2 = new FormQuery(null); FormQuery2.ShowDialog(); SecurityLogs.MakeLogEntry(Permissions.UserQuery, 0, ""); }
private void 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) { Queries.CurReport = new ReportOld(); Queries.CurReport.Query = @"SELECT ScreenDate,ProvName,County,county.CountyCode, GradeSchool,school.SchoolCode,PlaceService,GradeLevel,Age,Birthdate,Race,Gender,Urgency, HasCaries,EarlyChildCaries,CariesExperience,ExistingSealants,NeedsSealants,MissingAllTeeth, Comments FROM screen LEFT JOIN school ON screen.GradeSchool=school.SchoolName LEFT JOIN county ON screen.County=county.CountyName WHERE ScreenDate >= " + POut.PDate(date1.SelectionStart) + " " + "AND ScreenDate <= " + POut.PDate(date2.SelectionStart); FormQuery2 = new FormQuery(); FormQuery2.textTitle.Text = "RawScreeningData" + DateTime.Today.ToString("MMddyyyy"); //FormQuery2.IsReport=true; //FormQuery2.SubmitReportQuery(); FormQuery2.SubmitQuery(); FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { //if(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) { FormQuery FormQuery2; string phrase = textPharse.Text.Replace("\t", "").Replace("\n", ""); StringBuilder sbSQL = new StringBuilder(); sbSQL.AppendFormat("SELECT LName,FName,Preferred,PatStatus,Gender,Birthdate,Address,Address2,City,State,zip,HmPhone,Wkphone,", phrase); sbSQL.AppendFormat("WirelessPhone,Guarantor,PriProv,AddrNote,FamFinUrgNote,MedUrgNote,ApptModNote,DateFirstVisit", phrase); sbSQL.AppendFormat(" FROM patient WHERE AddrNote LIKE '%{0}%' ", phrase); sbSQL.AppendFormat("or FamFinUrgNote LIKE '%{0}%' ", phrase); sbSQL.AppendFormat("or MedUrgNote LIKE '%{0}%' ", phrase); sbSQL.AppendFormat("or ApptModNote LIKE '%{0}%' ", phrase); sbSQL.AppendFormat("or EmploymentNote LIKE '%{0}%' ", phrase); ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = sbSQL.ToString(); FormQuery2 = new FormQuery(report); FormQuery2.IsReport = false; FormQuery2.SubmitQuery(); FormQuery2.textQuery.Text = report.Query; FormQuery2.ShowDialog(); }
private void butOK_Click(object sender, System.EventArgs e) { string bDate; string eDate; decimal rcvStart = 0; decimal rcvProd = 0; decimal rcvAdj = 0; decimal rcvWriteoff = 0; decimal rcvPayment = 0; decimal rcvInsPayment = 0; decimal runningRcv = 0; decimal rcvDaily = 0; decimal[] ColTotal = new decimal[8]; string wMonth; string wYear; string wDay = "01"; string wDate; // Get the year / month and instert the 1st of the month for stop point for calculated running balance wYear = date1.SelectionStart.Year.ToString(); wMonth = date1.SelectionStart.Month.ToString(); if (wMonth.Length < 2) { wMonth = "0" + wMonth; } wDate = wYear + "-" + wMonth + "-" + wDay; ReportSimpleGrid report = new ReportSimpleGrid(); // // Create temperary tables for sorting data // DataTable TableCharge = new DataTable(); //charges DataTable TableCapWriteoff = new DataTable(); //capComplete writeoffs DataTable TableInsWriteoff = new DataTable(); //ins writeoffs DataTable TablePay = new DataTable(); //payments - Patient DataTable TableIns = new DataTable(); //payments - Ins, added SPK DataTable TableAdj = new DataTable(); //adjustments // // Main Loop: This will loop twice 1st loop gets running balance to start of month selected // 2nd will break the numbers dow by day and calculate the running balances // for (int j = 0; j <= 1; j++) { if (j == 0) { bDate = "0001-01-01"; eDate = wDate; } else { bDate = wDate; eDate = POut.Date(date1.SelectionStart.AddDays(1)).Substring(1, 10); // Needed because all Queries are < end date to get correct Starting AR } string whereProv; //used as the provider portion of the where clauses. //each whereProv needs to be set up separately for each query string whereProvx; //Extended for more than 4 names whereProv = ""; if (listProv.SelectedIndices[0] != 0) { for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i == 0) { whereProv += " AND ("; } else { whereProv += "OR "; } whereProv += "procedurelog.ProvNum = " + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " "; } whereProv += ") "; } report.Query = "SELECT procedurelog.ProcDate, " + "SUM(procedurelog.ProcFee*(CASE procedurelog.UnitQty+procedurelog.BaseUnits WHEN 0 THEN 1 ELSE procedurelog.UnitQty+procedurelog.BaseUnits END)) " + "FROM procedurelog " + "WHERE procedurelog.ProcDate >= '" + bDate + "' " + "AND procedurelog.ProcDate < '" + eDate + "' " + "AND procedurelog.ProcStatus = '2' " + whereProv + "GROUP BY procedurelog.ProcDate " + "ORDER BY procedurelog.ProcDate"; TableCharge = report.GetTempTable(); whereProv = ""; if (listProv.SelectedIndices[0] != 0) { for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i == 0) { whereProv += " AND ("; } else { whereProv += "OR "; } whereProv += "claimproc.ProvNum = " + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " "; } whereProv += ") "; } if (radioWriteoffPay.Checked) { report.Query = "SELECT DateCP, " + "SUM(WriteOff) FROM claimproc WHERE " + "DateCP >= '" + bDate + "' " + "AND DateCP < '" + eDate + "' " + "AND Status = '7' "//CapComplete + whereProv + " GROUP BY DateCP " + "ORDER BY DateCP"; } else { report.Query = "SELECT ProcDate, " + "SUM(WriteOff) FROM claimproc WHERE " + "ProcDate >= '" + bDate + "' " + "AND ProcDate < '" + eDate + "' " + "AND Status = '7' "//CapComplete + whereProv + " GROUP BY ProcDate " + "ORDER BY ProcDate"; } TableCapWriteoff = report.GetTempTable(); whereProv = ""; if (listProv.SelectedIndices[0] != 0) { for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i == 0) { whereProv += " AND ("; } else { whereProv += "OR "; } whereProv += "ProvNum = " + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " "; } whereProv += ") "; } if (radioWriteoffPay.Checked) { report.Query = "SELECT DateCP, " + "SUM(WriteOff) FROM claimproc WHERE " + "DateCP >= '" + bDate + "' " + "AND DateCP < '" + eDate + "' " + "AND Status IN (1,4,5) "//Recieved, supplemental, capclaim. Otherwise, it's only an estimate. 7-CapCompl handled above. + whereProv + " GROUP BY DateCP " + "ORDER BY DateCP"; } else { report.Query = "SELECT ProcDate, " + "SUM(WriteOff) FROM claimproc WHERE " + "ProcDate >= '" + bDate + "' " + "AND ProcDate < '" + eDate + "' " + "AND Status IN (0,1,4,5) " //Notreceived, received, supplemental, capclaim. 7-CapCompl handled above. + whereProv + " GROUP BY ProcDate " + "ORDER BY ProcDate"; } TableInsWriteoff = report.GetTempTable(); whereProv = ""; if (listProv.SelectedIndices[0] != 0) { for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i == 0) { whereProv += " AND ("; } else { whereProv += "OR "; } whereProv += "paysplit.ProvNum = " + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " "; } whereProv += ") "; } report.Query = "SELECT paysplit.DatePay,SUM(paysplit.splitamt) FROM paysplit " + "WHERE paysplit.PayPlanNum=0 " + "AND paysplit.DatePay >= '" + bDate + "' " + "AND paysplit.DatePay < '" + eDate + "' " + whereProv + " GROUP BY paysplit.DatePay ORDER BY DatePay"; TablePay = report.GetTempTable(); whereProv = ""; if (listProv.SelectedIndices[0] != 0) { for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i == 0) { whereProv += " AND ("; } else { whereProv += "OR "; } whereProv += "claimproc.ProvNum = " + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " "; } whereProv += ") "; } report.Query = "SELECT DateCP,SUM(InsPayamt) " + "FROM claimproc WHERE " + "Status IN (1,4,5,7) "//Received, supplemental, capclaim, capcomplete. + "AND DateCP >= '" + bDate + "' " + "AND DateCP < '" + eDate + "' " + whereProv + " GROUP BY DateCP ORDER BY DateCP"; TableIns = report.GetTempTable(); whereProv = ""; if (listProv.SelectedIndices[0] != 0) { for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (i == 0) { whereProv += " AND ("; } else { whereProv += "OR "; } whereProv += "ProvNum = " + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " "; } whereProv += ") "; } report.Query = "SELECT adjdate, SUM(adjamt) FROM adjustment WHERE " + "adjdate >= '" + bDate + "' " + "AND adjdate < '" + eDate + "' " + whereProv + " GROUP BY adjdate ORDER BY adjdate"; TableAdj = report.GetTempTable(); //1st Loop Calculate running Accounts Receivable upto the 1st of the Month Selected //2nd Loop Calculate the Daily Accounts Receivable upto the Date Selected //Finaly Generate Report showing the breakdown upto the date specified with totals for what is on the report if (j == 0) { for (int k = 0; k < TableCharge.Rows.Count; k++) { rcvProd += PIn.Decimal(TableCharge.Rows[k][1].ToString()); } for (int k = 0; k < TableCapWriteoff.Rows.Count; k++) { rcvWriteoff += PIn.Decimal(TableCapWriteoff.Rows[k][1].ToString()); } for (int k = 0; k < TableInsWriteoff.Rows.Count; k++) { rcvWriteoff += PIn.Decimal(TableInsWriteoff.Rows[k][1].ToString()); } for (int k = 0; k < TablePay.Rows.Count; k++) { rcvPayment += PIn.Decimal(TablePay.Rows[k][1].ToString()); } for (int k = 0; k < TableIns.Rows.Count; k++) { rcvInsPayment += PIn.Decimal(TableIns.Rows[k][1].ToString()); } for (int k = 0; k < TableAdj.Rows.Count; k++) { rcvAdj += PIn.Decimal(TableAdj.Rows[k][1].ToString()); } TableCharge.Clear(); TableCapWriteoff.Clear(); TableInsWriteoff.Clear(); TablePay.Clear(); TableIns.Clear(); TableAdj.Clear(); rcvStart = (rcvProd + rcvAdj - rcvWriteoff) - (rcvPayment + rcvInsPayment); } else { rcvAdj = 0; rcvInsPayment = 0; rcvPayment = 0; rcvProd = 0; rcvWriteoff = 0; rcvDaily = 0; runningRcv = rcvStart; report.TableQ = new DataTable(null); //new table with 7 columns for (int l = 0; l < 8; l++) //add columns { report.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns } report.InitializeColumns(); eDate = POut.Date(date1.SelectionStart).Substring(1, 10); // Reset EndDate to Selected Date DateTime[] dates = new DateTime[(PIn.Date(eDate) - PIn.Date(bDate)).Days + 1]; for (int i = 0; i < dates.Length; i++) //usually 31 days in loop { dates[i] = PIn.Date(bDate).AddDays(i); //create new row called 'row' based on structure of TableQ DataRow row = report.TableQ.NewRow(); row[0] = dates[i].ToShortDateString(); for (int k = 0; k < TableCharge.Rows.Count; k++) { if (dates[i] == (PIn.Date(TableCharge.Rows[k][0].ToString()))) { rcvProd += PIn.Decimal(TableCharge.Rows[k][1].ToString()); } } for (int k = 0; k < TableCapWriteoff.Rows.Count; k++) { if (dates[i] == (PIn.Date(TableCapWriteoff.Rows[k][0].ToString()))) { rcvWriteoff += PIn.Decimal(TableCapWriteoff.Rows[k][1].ToString()); } } for (int k = 0; k < TableAdj.Rows.Count; k++) { if (dates[i] == (PIn.Date(TableAdj.Rows[k][0].ToString()))) { rcvAdj += PIn.Decimal(TableAdj.Rows[k][1].ToString()); } } for (int k = 0; k < TableInsWriteoff.Rows.Count; k++) { if (dates[i] == (PIn.Date(TableInsWriteoff.Rows[k][0].ToString()))) { rcvWriteoff += PIn.Decimal(TableInsWriteoff.Rows[k][1].ToString()); } } for (int k = 0; k < TablePay.Rows.Count; k++) { if (dates[i] == (PIn.Date(TablePay.Rows[k][0].ToString()))) { rcvPayment += PIn.Decimal(TablePay.Rows[k][1].ToString()); } } for (int k = 0; k < TableIns.Rows.Count; k++) { if (dates[i] == (PIn.Date(TableIns.Rows[k][0].ToString()))) { rcvInsPayment += PIn.Decimal(TableIns.Rows[k][1].ToString()); } } rcvDaily = (rcvProd + rcvAdj - rcvWriteoff) - (rcvPayment + rcvInsPayment); runningRcv += (rcvProd + rcvAdj - rcvWriteoff) - (rcvPayment + rcvInsPayment); row[1] = rcvProd.ToString("n"); row[2] = rcvAdj.ToString("n"); row[3] = rcvWriteoff.ToString("n"); row[4] = rcvPayment.ToString("n"); row[5] = rcvInsPayment.ToString("n"); row[6] = rcvDaily.ToString("n"); row[7] = runningRcv.ToString("n"); ColTotal[1] += rcvProd; ColTotal[2] += rcvAdj; ColTotal[3] += rcvWriteoff; ColTotal[4] += rcvPayment; ColTotal[5] += rcvInsPayment; ColTotal[6] += rcvDaily; ColTotal[7] = runningRcv; report.TableQ.Rows.Add(row); //adds row to table Q rcvAdj = 0; rcvInsPayment = 0; rcvPayment = 0; rcvProd = 0; rcvWriteoff = 0; } report.ColTotal[1] = PIn.Decimal(ColTotal[1].ToString("n")); report.ColTotal[2] = PIn.Decimal(ColTotal[2].ToString("n")); report.ColTotal[3] = PIn.Decimal(ColTotal[3].ToString("n")); report.ColTotal[4] = PIn.Decimal(ColTotal[4].ToString("n")); report.ColTotal[5] = PIn.Decimal(ColTotal[5].ToString("n")); report.ColTotal[6] = PIn.Decimal(ColTotal[6].ToString("n")); report.ColTotal[7] = PIn.Decimal(ColTotal[7].ToString("n")); FormQuery2 = new FormQuery(report); FormQuery2.IsReport = true; FormQuery2.ResetGrid(); report.Title = "Receivables Breakdown Report"; report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle)); whereProv = "Report for: Practice"; whereProvx = ""; if (listProv.SelectedIndices[0] != 0) { int nameCount = 0; whereProv = "Report Includes: "; for (int i = 0; i < listProv.SelectedIndices.Count; i++) { if (nameCount < 3) { whereProv += " " + ProviderC.ListShort[listProv.SelectedIndices[i] - 1].GetFormalName() + " /"; } else { whereProvx += " " + ProviderC.ListShort[listProv.SelectedIndices[i] - 1].GetFormalName() + " /"; } nameCount += 1; } whereProv = whereProv.Substring(0, whereProv.Length - 1); if (whereProvx.Length > 0) { whereProvx = whereProvx.Substring(0, whereProvx.Length - 1); } } report.SubTitle.Add(whereProv); report.SubTitle.Add(whereProvx); report.SetColumnPos(this, 0, "Day", 80); report.SetColumnPos(this, 1, "Production", 160, HorizontalAlignment.Right); report.SetColumnPos(this, 2, "Adjustment", 260, HorizontalAlignment.Right); report.SetColumnPos(this, 3, "Writeoff", 360, HorizontalAlignment.Right); report.SetColumnPos(this, 4, "Payment", 470, HorizontalAlignment.Right); report.SetColumnPos(this, 5, "InsPayment", 570, HorizontalAlignment.Right); report.SetColumnPos(this, 6, "Daily A/R", 680, HorizontalAlignment.Right); report.SetColumnPos(this, 7, "Ending A/R", 779, HorizontalAlignment.Right); report.Summary.Add( Lan.g(this, "Receivables Calculation: (Production + Adjustments - Writeoffs) - (Payments + Insurance Payments)")); FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; } //END If } // END For Loop } //END OK button Clicked
private void butOK_Click(object sender, System.EventArgs e) { Queries.CurReport = new ReportOld(); // replaced insplan.carrier by carrier.carrierName, SPK 7/15/04 Queries.CurReport.Query = "SELECT claim.dateservice,claim.claimnum,claim.claimtype,claim.claimstatus," + "CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI),carrier.CarrierName,claim.claimfee " + "FROM patient,claim,insplan,carrier " + "WHERE patient.patnum=claim.patnum AND insplan.plannum=claim.plannum " + "AND insplan.CarrierNum=carrier.CarrierNum " // added SPK + "AND (claim.claimstatus = 'U' OR claim.claimstatus = 'H' OR claim.claimstatus = 'W')"; if (radioRange.Checked == true) // added 'W', SPK 7/15/04 { Queries.CurReport.Query += " AND claim.dateservice >= '" + date1.SelectionStart.ToString("yyyy-MM-dd") + "' " + "AND claim.dateservice <= '" + date2.SelectionStart.ToString("yyyy-MM-dd") + "'"; } else { Queries.CurReport.Query += " AND claim.dateservice = '" + date1.SelectionStart.ToString("yyyy-MM-dd") + "'"; } FormQuery2 = new FormQuery(); FormQuery2.IsReport = true; Queries.SubmitTemp(); //create TableTemp Queries.TableQ = new DataTable(null); //new table no name for (int i = 0; i < 6; i++) //add columns { Queries.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns } Queries.CurReport.ColTotal = new double[Queries.TableQ.Columns.Count]; for (int i = 0; i < Queries.TableTemp.Rows.Count; i++) //loop through data rows { DataRow row = Queries.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ row[0] = (PIn.PDate(Queries.TableTemp.Rows[i][0].ToString())).ToShortDateString(); //claim dateservice if (PIn.PString(Queries.TableTemp.Rows[i][2].ToString()) == "P") { row[1] = "Primary"; } if (PIn.PString(Queries.TableTemp.Rows[i][2].ToString()) == "S") { row[1] = "Secondary"; } if (PIn.PString(Queries.TableTemp.Rows[i][2].ToString()) == "PreAuth") { row[1] = "PreAuth"; } if (PIn.PString(Queries.TableTemp.Rows[i][2].ToString()) == "Other") { row[1] = "Other"; } if (Queries.TableTemp.Rows[i][3].ToString().Equals("H")) { row[2] = "Holding"; //Claim Status } else if (Queries.TableTemp.Rows[i][3].ToString().Equals("W")) { row[2] = "WaitQ"; //Claim Status, added SPK 7/15/04 } else { row[2] = "Unsent"; //Claim Status } row[3] = Queries.TableTemp.Rows[i][4]; //Patient name row[4] = Queries.TableTemp.Rows[i][5]; //Ins Carrier row[5] = PIn.PDouble(Queries.TableTemp.Rows[i][6].ToString()).ToString("F"); //claim fee Queries.CurReport.ColTotal[5] += PIn.PDouble(Queries.TableTemp.Rows[i][6].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 = "Claims Not Sent"; Queries.CurReport.SubTitle = new string[3]; Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString; if (radioRange.Checked == true) { Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"); } else { Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d"); } Queries.CurReport.ColPos[0] = 20; Queries.CurReport.ColPos[1] = 145; Queries.CurReport.ColPos[2] = 270; Queries.CurReport.ColPos[3] = 395; Queries.CurReport.ColPos[4] = 520; Queries.CurReport.ColPos[5] = 645; Queries.CurReport.ColPos[6] = 770; Queries.CurReport.ColCaption[0] = "Date"; Queries.CurReport.ColCaption[1] = "Type"; Queries.CurReport.ColCaption[2] = "Claim Status"; Queries.CurReport.ColCaption[3] = "Patient Name"; Queries.CurReport.ColCaption[4] = "Insurance Carrier"; Queries.CurReport.ColCaption[5] = "Amount"; Queries.CurReport.ColAlign[5] = HorizontalAlignment.Right; Queries.CurReport.Summary = new string[3]; FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }