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