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 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 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 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 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 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 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 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(textDate.errorProvider1.GetError(textDate)!=""){
// MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
// return;
//}
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query =
"SELECT " + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ",adjamt "
+ "FROM patient,adjustment "
+ "WHERE patient.patnum=adjustment.patnum "
+ "AND adjustment.adjdate = " + POut.Date(PrefC.GetDate(PrefName.FinanceChargeLastRun))
+ "AND adjustment.adjtype = '" + POut.Long(PrefC.GetLong(PrefName.FinanceChargeAdjustmentType)) + "'";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "FINANCE CHARGE REPORT";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add("Date of Charges: " + PrefC.GetDate(PrefName.FinanceChargeLastRun).ToShortDateString());
report.SetColumn(this, 0, "Patient Name", 180);
report.SetColumn(this, 1, "Amount", 100, HorizontalAlignment.Right);
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butPayments_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SET @pos=0; "
+ "SELECT @pos:=@pos+1 AS 'Count',patient.PatNum,LName,FName,DateEntry,PayDate,PayNote,PayAmt "
+ "FROM patient INNER JOIN payment ON payment.PatNum=patient.PatNum "
+ "INNER JOIN ("
+ "SELECT ClinicNum,PropertyValue AS PaymentType FROM programproperty "
+ "WHERE ProgramNum=" + POut.Long(Programs.GetProgramNum(ProgramName.Xcharge)) + " AND PropertyDesc='PaymentType'"
+ ") paytypes ON paytypes.ClinicNum=payment.ClinicNum AND paytypes.PaymentType=payment.PayType "
//Must be DateEntry here. PayDate will not work with recurring charges
+ "WHERE DateEntry BETWEEN " + POut.Date(date1.SelectionStart) + " AND " + POut.Date(date2.SelectionStart) + " "
+ "ORDER BY Count ASC";
FormQuery FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Payments From " + date1.SelectionStart.ToShortDateString() + " To " + date2.SelectionStart.ToShortDateString();
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this, 0, "Count", 50);
report.SetColumn(this, 1, "Pat", 50); //This name is used to ensure FormQuery does not replace the patnum with the patient name.
report.SetColumn(this, 2, "LName", 100);
report.SetColumn(this, 3, "FName", 100);
report.SetColumn(this, 4, "DateEntry", 100);
report.SetColumn(this, 5, "PayDate", 100);
report.SetColumn(this, 6, "PayNote", 150);
report.SetColumn(this, 7, "PayAmt", 70, HorizontalAlignment.Right);
Cursor = Cursors.Default;
FormQuery2.ShowDialog();
}
///<summary>Shows and error message and returns false if there is a problem creating the data file; Otherwise true.</summary>
private bool CreateDataFile(string fileName, LetterMerge letter)
{
DataTable table;
try {
table = LetterMergesQueries.GetLetterMergeInfo(PatCur, letter);
}
catch (Exception ex) {
string message = Lan.g(this, "There was a error getting letter merge info:");
MessageBox.Show(message + "\r\n" + ex.Message);
return(false);
}
table = FormQuery.MakeReadable(table, null, false);
try{
using (StreamWriter sw = new StreamWriter(fileName, false)){
string line = "";
for (int i = 0; i < letter.Fields.Count; i++)
{
if (letter.Fields[i].StartsWith("referral."))
{
line += "Ref" + letter.Fields[i].Substring(9);
}
else
{
line += letter.Fields[i];
}
if (i < letter.Fields.Count - 1)
{
line += "\t";
}
}
sw.WriteLine(line);
string cell;
for (int i = 0; i < table.Rows.Count; i++)
{
line = "";
for (int j = 0; j < table.Columns.Count; j++)
{
cell = table.Rows[i][j].ToString();
cell = cell.Replace("\r", "");
cell = cell.Replace("\n", "");
cell = cell.Replace("\t", "");
cell = cell.Replace("\"", "");
line += cell;
if (j < table.Columns.Count - 1)
{
line += "\t";
}
}
sw.WriteLine(line);
}
}
}
catch {
MsgBox.Show(this, "File in use by another program. Close and try again.");
return(false);
}
return(true);
}
public static IQueryable <Form> Filter(this IQueryable <Form> formsQuery, FormQuery query)
{
formsQuery = FilterByScheme(formsQuery, query);
formsQuery = FilterBySearchText(formsQuery, query);
formsQuery = FilterByNotSeen(formsQuery, query);
return(formsQuery);
}
private void CreateIndividual()
{
//added plfname for ordering purposes, spk 3/13/04
// added CapCoPay for Capitation, SPK 7/15/04 (js rewrote it on 10/4/04 due to db change)
// changed procedurecode.adacode to procedurelog.adacode & added Procnum to retrieve all codes
Queries.CurReport.Query = "SELECT procedurelog.ProcDate,CONCAT(CONCAT(CONCAT(CONCAT"
+ "(patient.LName,', '),patient.FName),' '),patient.MiddleI) AS plfname, procedurelog.ADACode,"
+ "procedurelog.ToothNum,procedurecode.Descript,provider.Abbr,"
+ "procedurelog.ProcFee-IFNULL(SUM(claimproc.WriteOff),0) $fee "//if no writeoff, then subtract 0
+ "FROM patient,procedurecode,provider,procedurelog "
+ "LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum "
+ "AND claimproc.Status='7' "//only CapComplete writeoffs are subtracted here.
+ "WHERE procedurelog.ProcStatus = '2' "
+ "AND patient.PatNum=procedurelog.PatNum "
+ "AND procedurelog.ADACode=procedurecode.ADACode "
+ "AND provider.ProvNum=procedurelog.ProvNum "
+ "AND " + whereProv + " "
+ "AND procedurelog.ADACode LIKE '%" + POut.PString(textADACode.Text) + "%' "
+ "AND procedurelog.ProcDate >= " + POut.PDate(date1.SelectionStart) + " "
+ "AND procedurelog.ProcDate <= " + POut.PDate(date2.SelectionStart) + " "
+ "GROUP BY procedurelog.ProcNum "
+ "ORDER BY procedurelog.ProcDate,plfname";
FormQuery2 = new FormQuery();
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
Queries.CurReport.Title = "Daily Procedures";
Queries.CurReport.SubTitle = new string[2];
Queries.CurReport.SubTitle[0] = ((Pref)PrefB.HList["PracticeTitle"]).ValueString;
Queries.CurReport.SubTitle[1] = date1.SelectionStart.ToString("d")
+ " - " + date2.SelectionStart.ToString("d");
// col[5] from 590 to 640, 680, 770, spk 7/20/04
Queries.CurReport.ColPos = new int[9];
Queries.CurReport.ColCaption = new string[8];
Queries.CurReport.ColAlign = new HorizontalAlignment[8];
Queries.CurReport.ColPos[0] = 20;
Queries.CurReport.ColPos[1] = 100;
Queries.CurReport.ColPos[2] = 250;
Queries.CurReport.ColPos[3] = 325;
Queries.CurReport.ColPos[4] = 370;
Queries.CurReport.ColPos[5] = 640;
Queries.CurReport.ColPos[6] = 680;
Queries.CurReport.ColPos[7] = 770;
Queries.CurReport.ColPos[8] = 820; // spk
Queries.CurReport.ColCaption[0] = "Date";
Queries.CurReport.ColCaption[1] = "Patient Name";
Queries.CurReport.ColCaption[2] = "ADA Code";
Queries.CurReport.ColCaption[3] = "Tooth";
Queries.CurReport.ColCaption[4] = "Description";
Queries.CurReport.ColCaption[5] = "Provider";
Queries.CurReport.ColCaption[6] = "Fee";
Queries.CurReport.ColAlign[6] = HorizontalAlignment.Right;
Queries.CurReport.ColCaption[7] = " "; // spk
Queries.CurReport.Summary = new string[0];
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
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 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 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 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();
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();
report.Query = textSQL.Text;
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = false;
FormQuery2.SubmitQuery();
FormQuery2.textQuery.Text = report.Query;
FormQuery2.ShowDialog();
//DialogResult=DialogResult.OK;
}
private FormQuery <OAuthToken> CreateOAuthTokenRequestQuery(string applicationControlPlane,
IEnumerable <KeyValuePair <string, string> > requestFormData, string subdomain = "secure")
{
var url = string.Format("https://{0}.{1}/oauth/token", subdomain, applicationControlPlane);
var oauthTokenQuery = new FormQuery <OAuthToken>(ShareFileClient)
.Ids(url)
.QueryString("requirev3", "true") as FormQuery <OAuthToken>;
oauthTokenQuery.Body = requestFormData;
oauthTokenQuery.HttpMethod = "POST";
return(oauthTokenQuery);
}
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 btnQuery_Click(object sender, EventArgs e)
{
var button = (Button)sender;
FormQuery formQuery;
if (button == btnFstQuery)
{
formQuery = new FormQuery(1);
}
else
{
formQuery = new FormQuery(2);
}
formQuery.Show();
}
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;
}
public async Task <IHttpActionResult> Get([FromUri] FormQuery query)
{
var serviceRes = await _formService.Get(new FormListRequest()
{
RequestOwner = User,
Query = query
});
IHttpActionResult res = BadRequest();
if (serviceRes.Access == ResponseAccess.Granted)
{
res = Ok(new { items = serviceRes.Models, count = serviceRes.Count });
}
if (serviceRes.Access == ResponseAccess.Deny)
{
res = Unauthorized(serviceRes.Message);
}
return(res);
}
private Task<OAuthToken> RequestOAuthTokenAsync(FormQuery<OAuthToken> oauthTokenQuery)
{
for (int i = 0; i < 2; i++)
{
try
{
return oauthTokenQuery.ExecuteAsync();
}
catch (OAuthErrorException)
{
throw;
}
catch (Exception)
{
if (i >= 1)
{
throw;
}
}
}
throw new Exception();
}
private FormQuery<OAuthToken> CreateOAuthTokenRequestQuery(string applicationControlPlane,
IEnumerable<KeyValuePair<string, string>> requestFormData, string subdomain = "secure")
{
var url = string.Format("https://{0}.{1}/oauth/token", subdomain, applicationControlPlane);
var oauthTokenQuery = new FormQuery<OAuthToken>(ShareFileClient)
.Ids(url)
.QueryString("requirev3", "true") as FormQuery<OAuthToken>;
oauthTokenQuery.Body = requestFormData;
oauthTokenQuery.HttpMethod = "POST";
return oauthTokenQuery;
}