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 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 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;
}
///<summary>Can pass in null if not a report.</summary>
public FormQuery(ReportSimpleGrid report){
InitializeComponent();// Required for Windows Form Designer support
Lan.F(this,new System.Windows.Forms.Control[] {
//exclude:
labelTotPages,
});
this.report=report;
}
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)
{
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+=",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";
/*}
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, 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)
{
ReportSimpleGrid report=new ReportSimpleGrid();
report.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.Date(date1.SelectionStart)+" "
+"AND ScreenDate <= " +POut.Date(date2.SelectionStart);
FormQuery FormQuery2=new FormQuery(report);
FormQuery2.textTitle.Text="RawProcedureData"+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)
{
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";//Negative proc fees should not show up on this report. We have one office that uses negative proc fees as internal adjustments.
if (radioGroupByProc.Checked)
{
report.Query += @"
GROUP BY procedurelog.ProcNum";
}
else if (radioGroupByPat.Checked)
{
report.Query += @"
GROUP BY procedurelog.PatNum,procedurelog.ProcDate";
}
report.Query += @"
HAVING ROUND($sumfee,3) < ROUND($PaidAndWriteoff,3)
ORDER BY patname,ProcDate";
FormQuery FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "INSURANCE OVERPAID REPORT";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this, 0, "PatNum", 60);
report.SetColumn(this, 1, "Pat Name", 150);
report.SetColumn(this, 2, "Date", 80);
report.SetColumn(this, 3, "Fee", 80, HorizontalAlignment.Right);
report.SetColumn(this, 4, "InsPd+W/O", 90, HorizontalAlignment.Right);
Cursor = Cursors.Default;
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender,EventArgs e)
{
ReportSimpleGrid report=new ReportSimpleGrid();
if(radioDateRange.Checked) {
report.Query="SELECT DatePay,"+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+",ItemName,SplitAmt "
+"FROM paysplit,patient,definition "
+"WHERE paysplit.PatNum=patient.PatNum "
+"AND definition.DefNum=paysplit.UnearnedType "
+"AND paysplit.DatePay >= "+POut.Date(date1.SelectionStart)+" "
+"AND paysplit.DatePay <= "+POut.Date(date2.SelectionStart)+" "
+"AND UnearnedType > 0 GROUP BY paysplit.SplitNum "
+"ORDER BY DatePay";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
report.Title="Unearned Income Activity";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this,0,"Date",100);
report.SetColumn(this,1,"Patient",140);
report.SetColumn(this,2,"Type",110);
report.SetColumn(this,3,"Amount",80,HorizontalAlignment.Right);
}
else {
report.Query="SELECT "+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+",";
report.Query+=DbHelper.GroupConcat("ItemName",true);
report.Query+="SUM(SplitAmt) Amount "
+"FROM paysplit,patient,definition "
+"WHERE paysplit.PatNum=patient.PatNum "
+"AND definition.DefNum=paysplit.UnearnedType "
+"AND UnearnedType > 0 GROUP BY paysplit.PatNum HAVING Amount != 0";//still won't work for oracle
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
report.Title="Unearned Income Liabilities";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SetColumn(this,0,"Patient",140);
report.SetColumn(this,1,"Type(s)",110);
report.SetColumn(this,2,"Amount",80,HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = @"SELECT ScreenDate,ProvName,County,county.CountyCode,
site.Description AS schoolName,site.Note AS schoolCode,PlaceService,GradeLevel,Age,Birthdate,Race,Gender,Urgency,
HasCaries,EarlyChildCaries,CariesExperience,ExistingSealants,NeedsSealants,MissingAllTeeth,
Comments FROM screen
LEFT JOIN site ON screen.GradeSchool=site.Description
LEFT JOIN county ON screen.County=county.CountyName
WHERE ScreenDate >= " + POut.Date(date1.SelectionStart) + " "
+ "AND ScreenDate <= " + POut.Date(date2.SelectionStart);
FormQuery2 = new FormQuery(report);
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)
{
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)
{
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 CreateGrouped(ReportSimpleGrid report) {
//this would require a temporary table to be able to handle capitation.
report.Query="SELECT definition.ItemName,procedurecode.ProcCode,procedurecode.Descript,";
if(DataConnection.DBtype==DatabaseType.MySql) {
report.Query+="Count(*),AVG(procedurelog.ProcFee) $AvgFee,SUM(procedurelog.ProcFee) AS $TotFee ";
}
else {//Oracle needs quotes.
report.Query+="Count(*),AVG(procedurelog.ProcFee) \"$AvgFee\",SUM(procedurelog.ProcFee) AS \"$TotFee\" ";
}
report.Query+="FROM procedurelog,procedurecode,definition "
+"WHERE procedurelog.ProcStatus = '2' "
+"AND procedurelog.CodeNum=procedurecode.CodeNum "
+"AND definition.DefNum=procedurecode.ProcCat "
+whereProv
+whereClin
+"AND procedurecode.ProcCode LIKE '%"+POut.String(textCode.Text)+"%' "
+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" >= '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"' "
+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" <= '" + date2.SelectionStart.ToString("yyyy-MM-dd")+"' "
+"GROUP BY procedurecode.ProcCode "
+"ORDER BY definition.ItemOrder,procedurecode.ProcCode";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
report.Title="Procedures By Procedure Code";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
if(checkAllProv.Checked) {
report.SubTitle.Add(Lan.g(this,"All Providers"));
}
else {
string provNames="";
for(int i=0;i<listProv.SelectedIndices.Count;i++) {
if(i>0) {
provNames+=", ";
}
provNames+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(provNames);
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(checkAllClin.Checked) {
report.SubTitle.Add(Lan.g(this,"All Clinics"));
}
else {
string clinNames="";
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i>0) {
clinNames+=", ";
}
if(listClin.SelectedIndices[i]==0) {
clinNames+=Lan.g(this,"Unassigned");
}
else {
clinNames+=Clinics.List[listClin.SelectedIndices[i]-1].Description;
}
}
report.SubTitle.Add(clinNames);
}
}
report.SetColumn(this,0,"Category",150);
report.SetColumn(this,1,"Code",90);
report.SetColumn(this,2,"Description",180);
report.SetColumn(this,3,"Quantity",60,HorizontalAlignment.Right);
report.SetColumn(this,4,"Average Fee",110,HorizontalAlignment.Right);
report.SetColumn(this,5,"Total Fees",100,HorizontalAlignment.Right);
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butReport_Click(object sender,EventArgs e) {
if(!Security.IsAuthorized(Permissions.UserQuery)) {
return;
}
//Basically a preview of gridMain (every employee on one page), allow user to export as excel sheet or print it.
string query=ClockEvents.GetTimeCardManageCommand(DateStart,DateStop,true);//true to get extra columns for printing.
ReportSimpleGrid rsg=new ReportSimpleGrid();
rsg.Query=query;
FormQuery FormQ=new FormQuery(rsg);
FormQ.textQuery.Text=query;
FormQ.SubmitQuery();
FormQ.ShowDialog();
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (textDateFrom.errorProvider1.GetError(textDateFrom) != "" ||
textDateTo.errorProvider1.GetError(textDateTo) != ""
)
{
MessageBox.Show(Lan.g(this, "Please fix data entry errors first."));
return;
}
if (listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (listProv.SelectedIndices[0] == 0 && listProv.SelectedIndices.Count > 1)
{
MsgBox.Show(this, "You cannot select 'all' providers as well as specific providers.");
return;
}
DateTime dateFrom = PIn.Date(textDateFrom.Text);
DateTime dateTo = PIn.Date(textDateTo.Text);
string whereProv = "";
if (listProv.SelectedIndices[0] != 0)
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereProv += " AND (";
}
else
{
whereProv += "OR ";
}
whereProv += "procedurelog.ProvNum = "
+ POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
}
whereProv += ") ";
}
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = @"SELECT referral.LName,referral.FName,
COUNT(DISTINCT refattach.PatNum) HowMany,
SUM(procedurelog.ProcFee) ""$HowMuch""";
if (checkAddress.Checked)
{
report.Query += ",referral.Title,referral.Address,referral.Address2,referral.City,"
+ "referral.ST,referral.Zip,referral.Specialty";
}
report.Query += @" FROM referral,refattach,procedurelog,patient
WHERE referral.ReferralNum=refattach.ReferralNum
AND procedurelog.PatNum=refattach.PatNum
AND procedurelog.PatNum=patient.PatNum
AND refattach.IsFrom=1
AND procedurelog.ProcStatus=2
AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " "
+ whereProv;
if (checkNewPat.Checked)
{
report.Query += "AND patient.DateFirstVisit >= " + POut.Date(dateFrom) + " "
+ "AND patient.DateFirstVisit <= " + POut.Date(dateTo) + " ";
}
report.Query += @"GROUP BY referral.ReferralNum
ORDER BY HowMany Desc";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Referral Analysis";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if (listProv.SelectedIndices[0] == 0)
{
report.SubTitle.Add(Lan.g(this, "All Providers"));
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
else if (listProv.SelectedIndices.Count == 1)
{
report.SubTitle.Add(Lan.g(this, "Prov: ") + ProviderC.ListShort[listProv.SelectedIndices[0] - 1].GetLongDesc());
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
else
{
//I'm too lazy to build a description for multiple providers as well as ensure that it fits the space.
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
if (checkAddress.Checked)
{
report.SetColumnPos(this, 0, "Last Name", 100);
report.SetColumnPos(this, 1, "First Name", 200);
report.SetColumnPos(this, 2, "Count", 270);
report.SetColumnPos(this, 3, "Production", 350, HorizontalAlignment.Right);
report.SetColumnPos(this, 4, "Title", 390);
report.SetColumnPos(this, 5, "Address", 490);
report.SetColumnPos(this, 6, "Add2", 530);
report.SetColumnPos(this, 7, "City", 590);
report.SetColumnPos(this, 8, "ST", 630);
report.SetColumnPos(this, 9, "Zip", 680);
report.SetColumnPos(this, 10, "Specialty", 880); //off the right side
}
else
{
report.SetColumnPos(this, 0, "Last Name", 100);
report.SetColumnPos(this, 1, "First Name", 200);
report.SetColumnPos(this, 2, "Count", 270);
report.SetColumnPos(this, 3, "Production", 350, HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void CreateIndividual(ReportSimpleGrid report) {
//added Procnum to retrieve all codes
report.Query="SELECT procedurelog.ProcDate,"
+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+" "
+"AS plfname, procedurecode.ProcCode,"
+"procedurelog.ToothNum,procedurecode.Descript,provider.Abbr,"
+"procedurelog.ClinicNum,"
+"procedurelog.ProcFee-IFNULL(SUM(claimproc.WriteOff),0) ";//\"$fee\" " //if no writeoff, then subtract 0
if(DataConnection.DBtype==DatabaseType.MySql) {
report.Query+="$fee ";
}
else {//Oracle needs quotes.
report.Query+="\"$fee\" ";
}
report.Query+="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.CodeNum=procedurecode.CodeNum "
+"AND provider.ProvNum=procedurelog.ProvNum "
+whereProv
+whereClin
+"AND procedurecode.ProcCode LIKE '%"+POut.String(textCode.Text)+"%' "
+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" >= " +POut.Date(date1.SelectionStart)+" "
+"AND "+DbHelper.DateColumn("procedurelog.ProcDate")+" <= " +POut.Date(date2.SelectionStart)+" "
+"GROUP BY procedurelog.ProcNum "
+"ORDER BY "+DbHelper.DateColumn("procedurelog.ProcDate")+",plfname,procedurecode.ProcCode,ToothNum";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
DataTable table=report.GetTempTable();
report.TableQ=new DataTable(null);
int colI=7;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
colI=8;
}
for(int i=0;i<colI;i++) { //add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
DataRow row;
decimal dec=0;
decimal total=0;
for(int i=0;i<table.Rows.Count;i++) {
row = report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
row[0]=PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
row[1]=table.Rows[i][1].ToString();//name
row[2]=table.Rows[i][2].ToString();//adacode
row[3]=Tooth.ToInternat(table.Rows[i][3].ToString());//tooth
row[4]=table.Rows[i][4].ToString();//descript
row[5]=table.Rows[i][5].ToString();//prov
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
row[6]=Clinics.GetDesc(PIn.Long(table.Rows[i][6].ToString()));//clinic
dec=PIn.Decimal(table.Rows[i][7].ToString());//fee
row[7]=dec.ToString("n");
}
else {
dec=PIn.Decimal(table.Rows[i][7].ToString());//fee
row[6]=dec.ToString("n");
}
total+=dec;
report.TableQ.Rows.Add(row);
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
report.ColTotal[7]=total;
}
else {
report.ColTotal[6]=total;
}
FormQuery2.ResetGrid();
report.Title="Daily Procedures";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
if(checkAllProv.Checked) {
report.SubTitle.Add(Lan.g(this,"All Providers"));
}
else {
string provNames="";
for(int i=0;i<listProv.SelectedIndices.Count;i++) {
if(i>0) {
provNames+=", ";
}
provNames+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(provNames);
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(checkAllClin.Checked) {
report.SubTitle.Add(Lan.g(this,"All Clinics"));
}
else {
string clinNames="";
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i>0) {
clinNames+=", ";
}
if(listClin.SelectedIndices[i]==0) {
clinNames+=Lan.g(this,"Unassigned");
}
else {
clinNames+=Clinics.List[listClin.SelectedIndices[i]-1].Description;
}
}
report.SubTitle.Add(clinNames);
}
}
report.SetColumn(this,0,"Date",80);
report.SetColumn(this,1,"Patient Name",130);
report.SetColumn(this,2,"ADA Code",75);
report.SetColumn(this,3,"Tooth",45);
report.SetColumn(this,4,"Description",200);
report.SetColumn(this,5,"Provider",50);
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
report.SetColumn(this,6,"Clinic",70);
report.SetColumn(this,7,"Fee",90,HorizontalAlignment.Right);
}
else{
report.SetColumn(this,6,"Fee",90,HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender,System.EventArgs e) {
if(date2.SelectionStart<date1.SelectionStart) {
MsgBox.Show(this,"End date cannot be before start date.");
return;
}
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=@"SELECT ScreenDate,ProvName,County,county.CountyCode,
site.Description AS schoolName,site.Note AS schoolCode,PlaceService,GradeLevel,Age,Birthdate,RaceOld,Gender,Urgency,
HasCaries,EarlyChildCaries,CariesExperience,ExistingSealants,NeedsSealants,MissingAllTeeth,
Comments FROM screen
LEFT JOIN site ON screen.GradeSchool=site.Description
LEFT JOIN county ON screen.County=county.CountyName
WHERE ScreenDate >= "+POut.Date(date1.SelectionStart)+" "
+"AND ScreenDate <= " +POut.Date(date2.SelectionStart);
FormQuery2=new FormQuery(report);
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(!checkBillTypesAll.Checked && listBillType.SelectedIndices.Count==0){
MsgBox.Show(this,"At least one billing type must be selected.");
return;
}
if(!checkProvAll.Checked && listProv.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one provider must be selected.");
return;
}
if(textDate.errorProvider1.GetError(textDate) != "") {
MsgBox.Show(this,"Invalid date.");
return;
}
DateTime asOfDate=PIn.Date(textDate.Text);
//The aging report always show historical numbers based on the date entered.
Ledgers.ComputeAging(0,asOfDate,true);
ReportSimpleGrid report=new ReportSimpleGrid();
string cmd="SELECT ";
if(PrefC.GetBool(PrefName.ReportsShowPatNum)){
cmd+=DbHelper.Concat("Cast(PatNum AS CHAR)","'-'","LName","', '","FName","' '","MiddleI");
}
else{
cmd+=DbHelper.Concat("LName","', '","FName","' '","MiddleI");
}
cmd+=",Bal_0_30,Bal_31_60,Bal_61_90,BalOver90"
+",BalTotal "
+",InsEst"
+",BalTotal-InsEst AS ";//\"$pat\" ";
if(DataConnection.DBtype==DatabaseType.MySql) {
cmd+="$pat ";
}
else { //Oracle needs quotes.
cmd+="\"$pat\" ";
}
cmd+="FROM patient "
+"WHERE ";
if(checkExcludeInactive.Checked) {
cmd+="(patstatus != 2) AND ";
}
if(checkBadAddress.Checked) {
cmd+="(zip !='') AND ";
}
if(checkOnlyNeg.Checked){
cmd+="BalTotal < '-.005' ";
}
else{
if(radioAny.Checked){
cmd+=
"(Bal_0_30 > '.005' OR Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if(radio30.Checked){
cmd+=
"(Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if(radio60.Checked){
cmd+=
"(Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if(radio90.Checked){
cmd+=
"(BalOver90 > '.005'";
}
if(checkIncludeNeg.Checked){
cmd+=" OR BalTotal < '-.005'";
}
cmd+=") ";
}
if(!checkBillTypesAll.Checked){
for(int i=0;i<listBillType.SelectedIndices.Count;i++) {
if(i==0) {
cmd+=" AND (billingtype = ";
}
else {
cmd+=" OR billingtype = ";
}
cmd+=POut.Long(DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].DefNum);
}
cmd+=") ";
}
if(!checkProvAll.Checked) {
for(int i=0;i<listProv.SelectedIndices.Count;i++) {
if(i==0) {
cmd+=" AND (PriProv = ";
}
else {
cmd+=" OR PriProv = ";
}
cmd+=POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum);
}
cmd+=") ";
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
cmd+=" AND ClinicNum IN(";
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i>0) {
cmd+=",";
}
if(Security.CurUser.ClinicIsRestricted) {
cmd+=POut.Long(_listClinics[listClin.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
}
else {
if(listClin.SelectedIndices[i]==0) {
cmd+="0";
}
else {
cmd+=POut.Long(_listClinics[listClin.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
}
}
}
cmd+=") ";
}
cmd+="ORDER BY LName,FName";
report.Query=cmd;
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
//Recompute aging in a non-historical way, so that the numbers are returned to the way they
//are normally used in other parts of the program.
Ledgers.RunAging();
//if(Prefs.UpdateString(PrefName.DateLastAging",POut.PDate(asOfDate,false))) {
// DataValid.SetInvalid(InvalidType.Prefs);
//}
report.Title="AGING OF ACCOUNTS RECEIVABLE REPORT";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add("As of "+textDate.Text);
if(radioAny.Checked){
report.SubTitle.Add("Any Balance");
}
if(radio30.Checked){
report.SubTitle.Add("Over 30 Days");
}
if(radio60.Checked){
report.SubTitle.Add("Over 60 Days");
}
if(radio90.Checked){
report.SubTitle.Add("Over 90 Days");
}
if(checkBillTypesAll.Checked){
report.SubTitle.Add("All Billing Types");
}
else{
string subt=DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[0]].ItemName;
for(int i=1;i<listBillType.SelectedIndices.Count;i++){
subt+=", "+DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].ItemName;
}
report.SubTitle.Add(subt);
}
//report.InitializeColumns(8);
report.SetColumn(this,0,"GUARANTOR",160);
report.SetColumn(this,1,"0-30 DAYS",80,HorizontalAlignment.Right);
report.SetColumn(this,2,"30-60 DAYS",80,HorizontalAlignment.Right);
report.SetColumn(this,3,"60-90 DAYS",80,HorizontalAlignment.Right);
report.SetColumn(this,4,"> 90 DAYS",80,HorizontalAlignment.Right);
report.SetColumn(this,5,"TOTAL",85,HorizontalAlignment.Right);
report.SetColumn(this,6,"-INS EST",85,HorizontalAlignment.Right);
report.SetColumn(this,7,"=PATIENT",85,HorizontalAlignment.Right);
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butFormulate_Click(object sender, System.EventArgs e) {//is now the 'Favorites' button
FormQueryFavorites FormQF=new FormQueryFavorites();
FormQF.UserQueryCur=UserQueryCur;
FormQF.ShowDialog();
if(FormQF.DialogResult==DialogResult.OK){
textQuery.Text=FormQF.UserQueryCur.QueryText;
//grid2.CaptionText=UserQueries.Cur.Description;
textTitle.Text=FormQF.UserQueryCur.Description;
UserQueryCur=FormQF.UserQueryCur;
report=new ReportSimpleGrid();
report.Query=textQuery.Text;
SubmitQuery();
//this.butSaveChanges.Enabled=true;
}
else{
//butSaveChanges.Enabled=false;
}
}
private void butOK_Click(object sender, EventArgs e)
{
if (textDateStart.errorProvider1.GetError(textDateStart) != "")
{
MsgBox.Show(this, "Please fix data entry errors first.");
return;
}
if (textDateStart.Text == "")
{
MsgBox.Show(this, "Please enter a start date.");
return;
}
if (textDateEnd.errorProvider1.GetError(textDateEnd) != "")
{
MsgBox.Show(this, "Please fix data entry errors first.");
return;
}
if (textDateEnd.Text == "")
{
MsgBox.Show(this, "Please enter an end date.");
return;
}
if (gridCourses.SelectedIndices.Length < 1)
{
MsgBox.Show(this, "At least one course must be selected to run a report. Please select a row from the course grid.");
return;
}
if (gridInstructors.SelectedIndices.Length < 1)
{
MsgBox.Show(this, "At least one instructor must be selected to run a report. Please select a row from the instructor grid.");
return;
}
if (gridStudents.SelectedIndices.Length < 1)
{
MsgBox.Show(this, "At least one student must be selected to run a report. Please select a row from the student grid.");
return;
}
DateTime dateStart = PIn.Date(textDateStart.Text);
DateTime dateEnd = PIn.Date(textDateEnd.Text);
string whereCourses = "";
if (!checkAllCourses.Checked)
{
for (int i = 0; i < gridCourses.SelectedIndices.Length; i++)
{
if (i == 0)
{
whereCourses += " AND evaluation.SchoolCourseNum IN(";
}
whereCourses += gridCourses.Rows[gridCourses.SelectedIndices[i]].Tag;
if (i != gridCourses.SelectedIndices.Length - 1)
{
whereCourses += ",";
}
}
whereCourses += ")";
}
string whereInstructors = "";
if (!checkAllInstructors.Checked)
{
for (int i = 0; i < gridInstructors.SelectedIndices.Length; i++)
{
if (i == 0)
{
whereInstructors += " AND evaluation.InstructNum IN(";
}
whereInstructors += gridInstructors.Rows[gridInstructors.SelectedIndices[i]].Tag;
if (i != gridInstructors.SelectedIndices.Length - 1)
{
whereInstructors += ",";
}
}
whereInstructors += ")";
}
//No checkbox for students
string whereStudents = " AND evaluation.StudentNum IN(";
for (int i = 0; i < gridStudents.SelectedIndices.Length; i++)
{
whereStudents += gridStudents.Rows[gridStudents.SelectedIndices[i]].Tag;
if (i != gridStudents.SelectedIndices.Length - 1)
{
whereStudents += ",";
}
}
whereStudents += ")";
ReportSimpleGrid report = new ReportSimpleGrid();
//Evaluations------------------------------------------------------------------------------
report.Query = "SELECT " + DbHelper.Concat("students.LName", "', '", "students.FName") + " StudentName,evaluation.DateEval,"
+ "courses.CourseID," + DbHelper.Concat("instructors.LName", "', '", "instructors.FName") + " InstructorName,"
+ "evaluation.EvalTitle,gradeScales.ScaleType,evaluation.OverallGradeShowing,evaluation.OverallGradeNumber";
report.Query += " FROM evaluation"
+ " INNER JOIN provider students ON evaluation.StudentNum=students.ProvNum"
+ " INNER JOIN provider instructors ON evaluation.InstructNum=instructors.ProvNum"
+ " INNER JOIN gradingscale gradeScales ON evaluation.GradingScaleNum=gradeScales.GradingScaleNum"
+ " INNER JOIN schoolcourse courses ON evaluation.SchoolCourseNum=courses.SchoolCourseNum"
+ " WHERE evaluation.DateEval BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd)
+ whereCourses
+ whereInstructors
+ whereStudents
+ " ORDER BY StudentName,evaluation.DateEval";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
DataTable table = report.GetTempTable();
report.TableQ = new DataTable();
int colI = 10;
for (int i = 0; i < colI; i++) //add columns
{
report.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns
}
report.InitializeColumns();
DataRow row;
for (int i = 0; i < table.Rows.Count; i++)
{
row = report.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ
row[0] = table.Rows[i]["StudentName"].ToString();
row[1] = PIn.Date(table.Rows[i]["DateEval"].ToString()).ToShortDateString();
row[2] = table.Rows[i]["CourseID"].ToString();
row[3] = table.Rows[i]["InstructorName"].ToString();
row[4] = table.Rows[i]["EvalTitle"].ToString();
switch ((EnumScaleType)PIn.Int(table.Rows[i]["ScaleType"].ToString()))
{
case EnumScaleType.PickList:
row[5] = Enum.GetName(typeof(EnumScaleType), (int)EnumScaleType.PickList);
break;
case EnumScaleType.Percentage:
row[5] = Enum.GetName(typeof(EnumScaleType), (int)EnumScaleType.Percentage);
break;
case EnumScaleType.Weighted:
row[5] = Enum.GetName(typeof(EnumScaleType), (int)EnumScaleType.Weighted);
break;
}
row[6] = table.Rows[i]["OverallGradeShowing"].ToString();
row[7] = table.Rows[i]["OverallGradeNumber"].ToString();
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();
report.Title = Lan.g(this, "Course Average");
report.SubTitle.Add(dateStart.ToShortDateString() + " - " + dateEnd.ToShortDateString());
if (checkAllInstructors.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Instructors"));
}
if (checkAllCourses.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Courses"));
}
report.SetColumn(this, 0, "Student", 120);
report.SetColumn(this, 1, "Date", 80);
report.SetColumn(this, 2, "Course", 100);
report.SetColumn(this, 3, "Instructor", 120);
report.SetColumn(this, 4, "Evaluation", 90);
report.SetColumn(this, 5, "Scale Type", 90);
report.SetColumn(this, 6, "Grade Showing", 100);
report.SetColumn(this, 7, "Grade Number", 100);
FormQuery2.ShowDialog();
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (!checkAllProv.Checked && listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
if (!checkAllClin.Checked && listClin.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one clinic must be selected.");
return;
}
}
if (listType.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one type must be selected.");
return;
}
string whereProv = "";
if (!checkAllProv.Checked)
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereProv += " AND (";
}
else
{
whereProv += "OR ";
}
whereProv += "adjustment.ProvNum = " + POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum) + " ";
}
whereProv += ") ";
}
string whereClin = "";
if (!checkAllClin.Checked)
{
for (int i = 0; i < listClin.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereClin += " AND (";
}
else
{
whereClin += "OR ";
}
if (listClin.SelectedIndices[i] == 0)
{
whereClin += "adjustment.ClinicNum = 0 ";
}
else
{
whereClin += "adjustment.ClinicNum = " + POut.Long(Clinics.List[listClin.SelectedIndices[i] - 1].ClinicNum) + " ";
}
}
whereClin += ") ";
}
string whereType = "(";
for (int i = 0; i < listType.SelectedIndices.Count; i++)
{
if (i > 0)
{
whereType += "OR ";
}
whereType += "adjustment.AdjType = '"
+ POut.Long(DefC.Short[(int)DefCat.AdjTypes][listType.SelectedIndices[i]].DefNum) + "' ";
}
whereType += ")";
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SELECT adjustment.AdjDate,"
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "', '", "patient.MiddleI") + ","
+ "adjustment.ProvNum,adjustment.ClinicNum,"
+ "definition.ItemName,adjustment.AdjNote,adjustment.AdjAmt FROM "
+ "adjustment,patient,definition WHERE adjustment.AdjType=definition.DefNum "
+ "AND patient.PatNum=adjustment.PatNum "
+ whereProv
+ whereClin
+ "AND " + whereType + " "
+ "AND adjustment.AdjDate >= " + POut.Date(date1.SelectionStart) + " "
+ "AND adjustment.AdjDate <= " + POut.Date(date2.SelectionStart);
report.Query += " ORDER BY adjustment.AdjDate";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
DataTable table = report.GetTempTable();
report.TableQ = new DataTable(null);
int colI = 6;
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
colI = 7;
}
for (int i = 0; i < colI; i++) //add columns
{
report.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns
}
report.InitializeColumns();
DataRow row;
double dbl;
for (int i = 0; i < table.Rows.Count; i++)
{
row = report.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ
row[0] = PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
row[1] = table.Rows[i][1].ToString(); //name
row[2] = Providers.GetAbbr(PIn.Long(table.Rows[i][2].ToString())); //prov
colI = 3;
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
row[colI] = Clinics.GetDesc(PIn.Long(table.Rows[i][3].ToString())); //clinic
colI++;
}
row[colI] = table.Rows[i][4].ToString(); //Type
colI++;
row[colI] = table.Rows[i][5].ToString(); //Note
colI++;
dbl = PIn.Double(table.Rows[i][6].ToString()); //Amount
row[colI] = dbl.ToString("n");
report.ColTotal[colI] += dbl;
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();
report.Title = "Daily Adjustments";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"));
if (checkAllProv.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Providers"));
}
else
{
string provNames = "";
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i > 0)
{
provNames += ", ";
}
provNames += ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(provNames);
}
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
if (checkAllClin.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Clinics"));
}
else
{
string clinNames = "";
for (int i = 0; i < listClin.SelectedIndices.Count; i++)
{
if (i > 0)
{
clinNames += ", ";
}
if (listClin.SelectedIndices[i] == 0)
{
clinNames += Lan.g(this, "Unassigned");
}
else
{
clinNames += Clinics.List[listClin.SelectedIndices[i] - 1].Description;
}
}
report.SubTitle.Add(clinNames);
}
}
report.SetColumn(this, 0, "Date", 90);
report.SetColumn(this, 1, "Patient Name", 130);
report.SetColumn(this, 2, "Prov", 60);
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
report.SetColumn(this, 3, "Clinic", 70);
report.SetColumn(this, 4, "Adjustment Type", 150);
report.SetColumn(this, 5, "Note", 150);
report.SetColumn(this, 6, "Amount", 75, HorizontalAlignment.Right);
}
else
{
report.SetColumn(this, 3, "Adjustment Type", 150);
report.SetColumn(this, 4, "Note", 150);
report.SetColumn(this, 5, "Amount", 75, HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (textDateFrom.errorProvider1.GetError(textDateFrom) != "" ||
textDateTo.errorProvider1.GetError(textDateTo) != ""
)
{
MessageBox.Show(Lan.g(this, "Please fix data entry errors first."));
return;
}
if (listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (listProv.SelectedIndices[0] == 0 && listProv.SelectedIndices.Count > 1)
{
MsgBox.Show(this, "You cannot select 'all' providers as well as specific providers.");
return;
}
DateTime dateFrom = PIn.Date(textDateFrom.Text);
DateTime dateTo = PIn.Date(textDateTo.Text);
string whereProv = "";
if (listProv.SelectedIndices[0] != 0)
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereProv += " WHERE (";
}
else
{
whereProv += "OR ";
}
whereProv += "patient.PriProv = "
+ POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i] - 1].ProvNum) + " ";
}
whereProv += ") ";
}
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = @"SET @pos=0;
SELECT @pos:=@pos+1 patCount,result.* FROM (SELECT dateFirstProc,patient.LName,patient.FName,"
+ DbHelper.Concat("referral.LName", "IF(referral.FName='','',',')", "referral.FName") + " refname,SUM(procedurelog.ProcFee) ";//\"$HowMuch\"";
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "$HowMuch";
}
else //Oracle needs quotes.
{
report.Query += "\"$HowMuch\"";
}
if (checkAddress.Checked)
{
report.Query += ",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip";
}
report.Query += @" FROM
(SELECT PatNum, MIN(ProcDate) dateFirstProc FROM procedurelog
WHERE ProcStatus=2 GROUP BY PatNum
HAVING dateFirstProc >= " + POut.Date(dateFrom) + " "
+ "AND DATE(dateFirstProc) <= " + POut.Date(dateTo) + " ) table1 "
+ @"INNER JOIN patient ON table1.PatNum=patient.PatNum
LEFT JOIN procedurelog ON patient.PatNum=procedurelog.PatNum AND procedurelog.ProcStatus=2
LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.IsFrom=1
AND refattach.ItemOrder=(SELECT MIN(ra.ItemOrder) FROM refattach ra WHERE ra.PatNum=refattach.PatNum AND ra.IsFrom=1)
LEFT JOIN referral ON referral.ReferralNum=refattach.ReferralNum "
+ whereProv;
report.Query += "GROUP BY patient.LName,patient.FName,patient.PatNum," + DbHelper.Concat("referral.LName", "IF(referral.FName='','',',')", "referral.FName");
if (checkAddress.Checked)
{
report.Query += ",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip ";
}
if (checkProd.Checked)
{
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "HAVING $HowMuch > 0 ";
}
else //Oracle needs quotes.
{
report.Query += "HAVING \"$HowMuch\" > 0 ";
}
}
report.Query += "ORDER BY dateFirstProc,patient.LName,patient.FName) result";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "New Patients";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if (listProv.SelectedIndices[0] == 0)
{
report.SubTitle.Add(Lan.g(this, "All Providers"));
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
else if (listProv.SelectedIndices.Count == 1)
{
report.SubTitle.Add(Lan.g(this, "Prov: ") + ProviderC.ListShort[listProv.SelectedIndices[0] - 1].GetLongDesc());
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
else
{
//I'm too lazy to build a description for multiple providers as well as ensure that it fits the space.
report.SubTitle.Add(dateFrom.ToString("d") + " - " + dateTo.ToString("d"));
}
report.SetColumnPos(this, 0, "#", 40);
report.SetColumnPos(this, 1, "Date", 120);
report.SetColumnPos(this, 2, "Last Name", 210);
report.SetColumnPos(this, 3, "First Name", 300);
report.SetColumnPos(this, 4, "Referral", 380);
report.SetColumnPos(this, 5, "Production", 450, HorizontalAlignment.Right);
if (checkAddress.Checked)
{
report.SetColumnPos(this, 6, "Pref'd", 500);
report.SetColumnPos(this, 7, "Address", 570);
report.SetColumnPos(this, 8, "Add2", 630);
report.SetColumnPos(this, 9, "City", 680);
report.SetColumnPos(this, 10, "ST", 730);
report.SetColumnPos(this, 11, "Zip", 880); //off the right side
}
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if( textDateFrom.errorProvider1.GetError(textDateFrom)!=""
|| textDateTo.errorProvider1.GetError(textDateTo)!=""
){
MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
return;
}
if(listProv.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one provider must be selected.");
return;
}
if(listProv.SelectedIndices[0]==0 && listProv.SelectedIndices.Count>1){
MsgBox.Show(this,"You cannot select 'all' providers as well as specific providers.");
return;
}
DateTime dateFrom=PIn.Date(textDateFrom.Text);
DateTime dateTo=PIn.Date(textDateTo.Text);
string whereProv="";
if(listProv.SelectedIndices[0]!=0){
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i==0){
whereProv+=" WHERE (";
}
else{
whereProv+="OR ";
}
whereProv+="patient.PriProv = "
+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]-1].ProvNum)+" ";
}
whereProv+=") ";
}
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=@"SET @pos=0;
SELECT @pos:=@pos+1 patCount,result.* FROM (SELECT dateFirstProc,patient.LName,patient.FName,"
+DbHelper.Concat("referral.LName","IF(referral.FName='','',',')","referral.FName")+" refname,SUM(procedurelog.ProcFee) ";//\"$HowMuch\"";
if(DataConnection.DBtype==DatabaseType.MySql) {
report.Query+="$HowMuch";
}
else { //Oracle needs quotes.
report.Query+="\"$HowMuch\"";
}
if(checkAddress.Checked){
report.Query+=",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip";
}
report.Query+=@" FROM
(SELECT PatNum, MIN(ProcDate) dateFirstProc FROM procedurelog
WHERE ProcStatus=2 GROUP BY PatNum
HAVING dateFirstProc >= "+POut.Date(dateFrom)+" "
+"AND DATE(dateFirstProc) <= "+POut.Date(dateTo)+" ) table1 "
+@"INNER JOIN patient ON table1.PatNum=patient.PatNum
LEFT JOIN procedurelog ON patient.PatNum=procedurelog.PatNum AND procedurelog.ProcStatus=2
LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.IsFrom=1
AND refattach.ItemOrder=(SELECT MIN(ra.ItemOrder) FROM refattach ra WHERE ra.PatNum=refattach.PatNum AND ra.IsFrom=1)
LEFT JOIN referral ON referral.ReferralNum=refattach.ReferralNum "
+whereProv;
report.Query+="GROUP BY patient.LName,patient.FName,patient.PatNum,"+DbHelper.Concat("referral.LName","IF(referral.FName='','',',')","referral.FName");
if(checkAddress.Checked) {
report.Query+=",patient.Preferred,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip ";
}
if(checkProd.Checked){
if(DataConnection.DBtype==DatabaseType.MySql) {
report.Query+="HAVING $HowMuch > 0 ";
}
else {//Oracle needs quotes.
report.Query+="HAVING \"$HowMuch\" > 0 ";
}
}
report.Query+="ORDER BY dateFirstProc,patient.LName,patient.FName) result";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
report.Title="New Patients";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if(listProv.SelectedIndices[0]==0){
report.SubTitle.Add(Lan.g(this,"All Providers"));
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
else if(listProv.SelectedIndices.Count==1){
report.SubTitle.Add(Lan.g(this,"Prov: ")+ProviderC.ListShort[listProv.SelectedIndices[0]-1].GetLongDesc());
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
else{
//I'm too lazy to build a description for multiple providers as well as ensure that it fits the space.
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
report.SetColumnPos(this,0,"#",40);
report.SetColumnPos(this,1,"Date",120);
report.SetColumnPos(this,2,"Last Name",210);
report.SetColumnPos(this,3,"First Name",300);
report.SetColumnPos(this,4,"Referral",380);
report.SetColumnPos(this,5,"Production",450,HorizontalAlignment.Right);
if(checkAddress.Checked){
report.SetColumnPos(this,6,"Pref'd",500);
report.SetColumnPos(this,7,"Address",570);
report.SetColumnPos(this,8,"Add2",630);
report.SetColumnPos(this,9,"City",680);
report.SetColumnPos(this,10,"ST",730);
report.SetColumnPos(this,11,"Zip",880);//off the right side
}
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butSubmit_Click(object sender, System.EventArgs e) {
if(!IsSafeSql()) {
return;
}
bool isCommand=IsCommandSql(textQuery.Text);
if(isCommand && !Security.IsAuthorized(Permissions.UserQueryAdmin)) {
return;
}
if(isCommand) {
SecurityLogs.MakeLogEntry(Permissions.UserQueryAdmin,0,"Command query run.");
}
report=new ReportSimpleGrid();
report.Query=textQuery.Text;
SubmitQuery();
}
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;
}
if(!checkAllProv.Checked && listProv.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one provider must be selected.");
return;
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(!checkAllClin.Checked && listClin.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one clinic must be selected.");
return;
}
}
if(listType.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one type must be selected.");
return;
}
string whereProv="";
if(!checkAllProv.Checked) {
for(int i=0;i<listProv.SelectedIndices.Count;i++) {
if(i==0) {
whereProv+=" AND (";
}
else {
whereProv+="OR ";
}
whereProv+="adjustment.ProvNum = "+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
}
whereProv+=") ";
}
string whereClin="";
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
whereClin+=" AND adjustment.ClinicNum IN(";
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i>0) {
whereClin+=",";
}
if(Security.CurUser.ClinicIsRestricted) {
whereClin+=POut.Long(_listClinics[listClin.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
}
else {
if(listClin.SelectedIndices[i]==0) {
whereClin+="0";
}
else {
whereClin+=POut.Long(_listClinics[listClin.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
}
}
}
whereClin+=") ";
}
string whereType="(";
for(int i=0;i<listType.SelectedIndices.Count;i++) {
if(i>0) {
whereType+="OR ";
}
whereType+="adjustment.AdjType = '"
+POut.Long(DefC.Short[(int)DefCat.AdjTypes][listType.SelectedIndices[i]].DefNum)+"' ";
}
whereType+=")";
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query="SELECT adjustment.AdjDate,"
+DbHelper.Concat("patient.LName","', '","patient.FName","', '","patient.MiddleI")+","
+"adjustment.ProvNum,adjustment.ClinicNum,"
+"definition.ItemName,adjustment.AdjNote,adjustment.AdjAmt FROM "
+"adjustment,patient,definition WHERE adjustment.AdjType=definition.DefNum "
+"AND patient.PatNum=adjustment.PatNum "
+whereProv
+whereClin
+"AND "+whereType+" "
+"AND adjustment.AdjDate >= "+POut.Date(date1.SelectionStart)+" "
+"AND adjustment.AdjDate <= "+POut.Date(date2.SelectionStart);
report.Query += " ORDER BY adjustment.AdjDate";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
DataTable table=report.GetTempTable();
report.TableQ=new DataTable(null);
int colI=6;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
colI=7;
}
for(int i=0;i<colI;i++) { //add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
DataRow row;
decimal dec;
for(int i=0;i<table.Rows.Count;i++) {
row = report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
row[0]=PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
row[1]=table.Rows[i][1].ToString();//name
row[2]=Providers.GetAbbr(PIn.Long(table.Rows[i][2].ToString()));//prov
colI=3;
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
row[colI]=Clinics.GetDesc(PIn.Long(table.Rows[i][3].ToString()));//clinic
colI++;
}
row[colI]=table.Rows[i][4].ToString();//Type
colI++;
row[colI]=table.Rows[i][5].ToString();//Note
colI++;
dec=PIn.Decimal(table.Rows[i][6].ToString());//Amount
row[colI]=dec.ToString("n");
report.ColTotal[colI]+=dec;
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();
report.Title="Daily Adjustments";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
if(checkAllProv.Checked) {
report.SubTitle.Add(Lan.g(this,"All Providers"));
}
else {
string provNames="";
for(int i=0;i<listProv.SelectedIndices.Count;i++) {
if(i>0) {
provNames+=", ";
}
provNames+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(provNames);
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(checkAllClin.Checked) {
report.SubTitle.Add(Lan.g(this,"All Clinics"));
}
else {
string clinNames="";
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i>0) {
clinNames+=", ";
}
if(Security.CurUser.ClinicIsRestricted) {
clinNames+=_listClinics[listClin.SelectedIndices[i]].Description;
}
else {
if(listClin.SelectedIndices[i]==0) {
clinNames+=Lan.g(this,"Unassigned");
}
else {
clinNames+=_listClinics[listClin.SelectedIndices[i]-1].Description;//Minus 1 from the selected index
}
}
}
report.SubTitle.Add(clinNames);
}
}
report.SetColumn(this,0,"Date",90);
report.SetColumn(this,1,"Patient Name",130);
report.SetColumn(this,2,"Prov",60);
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
report.SetColumn(this,3,"Clinic",70);
report.SetColumn(this,4,"Adjustment Type",150);
report.SetColumn(this,5,"Note",150);
report.SetColumn(this,6,"Amount",75,HorizontalAlignment.Right);
}
else {
report.SetColumn(this,3,"Adjustment Type",150);
report.SetColumn(this,4,"Note",150);
report.SetColumn(this,5,"Amount",75,HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
long feeSched = FeeSchedC.ListShort[listFeeSched.SelectedIndex].FeeSchedNum;
string catName = ""; //string to hold current category name
Fees fee = new Fees();
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SELECT procedurecode.ProcCode,fee.Amount,' ',procedurecode.Descript,"
+ "procedurecode.AbbrDesc FROM procedurecode,fee "
+ "WHERE procedurecode.CodeNum=fee.CodeNum AND fee.FeeSched='" + feeSched.ToString()
+ "' ORDER BY procedurecode.ProcCode";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
if (radioCode.Checked == true)
{
FormQuery2.SubmitReportQuery();
report.Title = "Procedure Codes";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(FeeScheds.GetDescription(feeSched));
report.SetColumn(this, 0, "Code", 70);
report.SetColumn(this, 1, "Fee Amount", 70, HorizontalAlignment.Right);
report.SetColumn(this, 2, " ", 80); //otherwise, the amount gets bunched up next to the description.
report.SetColumn(this, 3, "Description", 200);
report.SetColumn(this, 4, "Abbr Description", 200);
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
else //categories
//report.SubmitTemp();//create TableTemp which is not actually used
{
ProcedureCode[] ProcList = ProcedureCodes.GetProcList();
report.TableQ = new DataTable(null);
for (int i = 0; i < 5; i++) //add columns
{
report.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns
}
report.InitializeColumns();
DataRow row = report.TableQ.NewRow();//add first row by hand to get value for temp
row[0] = DefC.GetName(DefCat.ProcCodeCats, ProcList[0].ProcCat);
catName = row[0].ToString();
row[1] = ProcList[0].ProcCode;
row[2] = ProcList[0].Descript;
row[3] = ProcList[0].AbbrDesc;
row[4] = ((double)Fees.GetAmount0(ProcList[0].CodeNum, feeSched)).ToString("F");
report.ColTotal[4] += PIn.Decimal(row[4].ToString());
report.TableQ.Rows.Add(row);
for (int i = 1; i < ProcList.Length; i++) //loop through data rows
{
row = report.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ
row[0] = DefC.GetName(DefCat.ProcCodeCats, ProcList[i].ProcCat);
if (catName == row[0].ToString())
{
row[0] = "";
}
else
{
catName = row[0].ToString();
}
row[1] = ProcList[i].ProcCode.ToString();
row[2] = ProcList[i].Descript;
row[3] = ProcList[i].AbbrDesc.ToString();
row[4] = ((double)Fees.GetAmount0(ProcList[i].CodeNum, feeSched)).ToString("F");
//report.ColTotal[4]+=PIn.PDouble(row[4].ToString());
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid(); //this is a method in FormQuery2;
report.Title = "Procedure Codes";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(FeeScheds.GetDescription(feeSched));
report.ColPos[0] = 20;
report.ColPos[1] = 120;
report.ColPos[2] = 270;
report.ColPos[3] = 470;
report.ColPos[4] = 620;
report.ColPos[5] = 770;
report.ColCaption[0] = "Category";
report.ColCaption[1] = "Code";
report.ColCaption[2] = "Description";
report.ColCaption[3] = "Abbr Description";
report.ColCaption[4] = "Fee Amount";
report.ColAlign[4] = HorizontalAlignment.Right;
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
}
///<summary>Starting to use this externally as well. OK to pass in a null report.</summary>
public static DataTable MakeReadable(DataTable tableIn,ReportSimpleGrid reportIn){
//this can probably be improved upon later for speed
if(hListPlans==null){
hListPlans=InsPlans.GetHListAll();
}
DataTable tableOut=tableIn.Clone();//copies just the structure
for(int j=0;j<tableOut.Columns.Count;j++){
tableOut.Columns[j].DataType=typeof(string);
}
DataRow thisRow;
//copy data from tableInput to tableOutput while converting to strings
//string str;
//Type t;
for(int i=0;i<tableIn.Rows.Count;i++){
thisRow=tableOut.NewRow();//new row with new schema
for(int j=0;j<tableIn.Columns.Count;j++){
thisRow[j]=PIn.ByteArray(tableIn.Rows[i][j]);
//str=tableIn.Rows[i][j].ToString();
//t=tableIn.Rows[i][j].GetType();
//thisRow[j]=str;
}
tableOut.Rows.Add(thisRow);
}
DateTime date;
decimal[] colTotals=new decimal[tableOut.Columns.Count];
for(int j=0;j<tableOut.Columns.Count;j++){
for(int i=0;i<tableOut.Rows.Count;i++){
try{
if(tableOut.Columns[j].Caption.Substring(0,1)=="$"){
tableOut.Rows[i][j]=PIn.Double(tableOut.Rows[i][j].ToString()).ToString("F");
if(reportIn!=null) {
reportIn.ColAlign[j]=HorizontalAlignment.Right;
colTotals[j]+=PIn.Decimal(tableOut.Rows[i][j].ToString());
}
}
else if(tableOut.Columns[j].Caption.ToLower().StartsWith("date")){
date=PIn.Date(tableOut.Rows[i][j].ToString());
if(date.Year<1880){
tableOut.Rows[i][j]="";
}
else{
tableOut.Rows[i][j]=date.ToString("d");
}
}
else switch(tableOut.Columns[j].Caption.ToLower())
{
//bool
case "isprosthesis":
case "ispreventive":
case "ishidden":
case "isrecall":
case "usedefaultfee":
case "usedefaultcov":
case "isdiscount":
case "removetooth":
case "setrecall":
case "nobillins":
case "isprosth":
case "ishygiene":
case "issecondary":
case "orpribool":
case "orsecbool":
case "issplit":
case "ispreauth":
case "isortho":
case "releaseinfo":
case "assignben":
case "enabled":
case "issystem":
case "usingtin":
case "sigonfile":
case "notperson":
case "isfrom":
tableOut.Rows[i][j]=PIn.Bool(tableOut.Rows[i][j].ToString()).ToString();
break;
//date. Some of these are actually handled further up.
case "adjdate":
case "baldate":
case "dateservice":
case "datesent":
case "datereceived":
case "priordate":
case "date":
case "dateviewing":
case "datecreated":
case "dateeffective":
case "dateterm":
case "paydate":
case "procdate":
case "rxdate":
case "birthdate":
case "monthyear":
case "accidentdate":
case "orthodate":
case "checkdate":
case "screendate":
case "datedue":
case "dateduecalc":
case "datefirstvisit":
case "mydate"://this is a workaround for the daily payment report
tableOut.Rows[i][j]=PIn.Date(tableOut.Rows[i][j].ToString()).ToString("d");
break;
//age
case "birthdateforage":
tableOut.Rows[i][j]=PatientLogic.DateToAgeString(PIn.Date(tableOut.Rows[i][j].ToString()));
break;
//time
case "aptdatetime":
case "nextschedappt":
case "starttime":
case "stoptime":
tableOut.Rows[i][j]=PIn.DateT(tableOut.Rows[i][j].ToString()).ToString("t")+" "
+PIn.DateT(tableOut.Rows[i][j].ToString()).ToString("d");
break;
//TimeCardManage
case "adjevent":
case "adjreg":
case "adjotime":
case "breaktime":
case "temptotaltime":
case "tempreghrs":
case "tempovertime":
if(PrefC.GetBool(PrefName.TimeCardsUseDecimalInsteadOfColon)) {
tableOut.Rows[i][j]=PIn.Time(tableOut.Rows[i][j].ToString()).TotalHours.ToString("n");
}
else {
tableOut.Rows[i][j]=PIn.Time(tableOut.Rows[i][j].ToString()).ToStringHmm();
}
break;
//double
case "adjamt":
case "monthbalance":
case "claimfee":
case "inspayest":
case "inspayamt":
case "dedapplied":
case "amount":
case "payamt":
case "splitamt":
case "balance":
case "procfee":
case "overridepri":
case "overridesec":
case "priestim":
case "secestim":
case "procfees":
case "claimpays":
case "insest":
case "paysplits":
case "adjustments":
case "bal_0_30":
case "bal_31_60":
case "bal_61_90":
case "balover90":
case "baltotal":
tableOut.Rows[i][j]=PIn.Double(tableOut.Rows[i][j].ToString()).ToString("F");
if(reportIn!=null) {
reportIn.ColAlign[j]=HorizontalAlignment.Right;
colTotals[j]+=PIn.Decimal(tableOut.Rows[i][j].ToString());
}
break;
case "toothnum":
tableOut.Rows[i][j]=Tooth.ToInternat(tableOut.Rows[i][j].ToString());
break;
//definitions:
case "adjtype":
tableOut.Rows[i][j]
=DefC.GetName(DefCat.AdjTypes,PIn.Long(tableOut.Rows[i][j].ToString()));
break;
case "confirmed":
tableOut.Rows[i][j]
=DefC.GetValue(DefCat.ApptConfirmed,PIn.Long(tableOut.Rows[i][j].ToString()));
break;
case "dx":
tableOut.Rows[i][j]
=DefC.GetName(DefCat.Diagnosis,PIn.Long(tableOut.Rows[i][j].ToString()));
break;
case "discounttype":
tableOut.Rows[i][j]
=DefC.GetName(DefCat.DiscountTypes,PIn.Long(tableOut.Rows[i][j].ToString()));
break;
case "doccategory":
tableOut.Rows[i][j]
=DefC.GetName(DefCat.ImageCats,PIn.Long(tableOut.Rows[i][j].ToString()));
break;
case "op":
tableOut.Rows[i][j]
=Operatories.GetAbbrev(PIn.Long(tableOut.Rows[i][j].ToString()));
break;
case "paytype":
tableOut.Rows[i][j]
=DefC.GetName(DefCat.PaymentTypes,PIn.Long(tableOut.Rows[i][j].ToString()));
break;
case "proccat":
tableOut.Rows[i][j]
=DefC.GetName(DefCat.ProcCodeCats,PIn.Long(tableOut.Rows[i][j].ToString()));
break;
case "unschedstatus":
case "recallstatus":
tableOut.Rows[i][j]
=DefC.GetName(DefCat.RecallUnschedStatus,PIn.Long(tableOut.Rows[i][j].ToString()));
break;
case "billingtype":
tableOut.Rows[i][j]
=DefC.GetName(DefCat.BillingTypes,PIn.Long(tableOut.Rows[i][j].ToString()));
break;
//patnums:
case "patnum":
case "guarantor":
case "pripatnum":
case "secpatnum":
case "subscriber":
case "withpat":
if(patientNames.ContainsKey(PIn.Long(tableOut.Rows[i][j].ToString()))) {
//MessageBox.Show((string)Patients.HList[PIn.PInt(tableOut.Rows[i][j].ToString())]);
tableOut.Rows[i][j]=patientNames[PIn.Long(tableOut.Rows[i][j].ToString())];
}
else
tableOut.Rows[i][j]="";
break;
//plannums:
case "plannum":
case "priplannum":
case "secplannum":
if(hListPlans.ContainsKey(PIn.Long(tableOut.Rows[i][j].ToString())))
tableOut.Rows[i][j]=hListPlans[PIn.Long(tableOut.Rows[i][j].ToString())];
else
tableOut.Rows[i][j]="";
break;
//referralnum
case "referralnum":
if(PIn.Long(tableOut.Rows[i][j].ToString())!=0){
Referral referral=Referrals.GetReferral
(PIn.Long(tableOut.Rows[i][j].ToString()));
tableOut.Rows[i][j]
=referral.LName+", "+referral.FName+" "+referral.MName;
}
else
tableOut.Rows[i][j]="";
break;
//enumerations:
case "aptstatus":
tableOut.Rows[i][j]
=((ApptStatus)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "category":
tableOut.Rows[i][j]=((DefCat)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "renewmonth":
tableOut.Rows[i][j]=((Month)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "patstatus":
tableOut.Rows[i][j]
=((PatientStatus)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "gender":
tableOut.Rows[i][j]
=((PatientGender)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
//case "lab":
// tableOut.Rows[i][j]
// =((LabCaseOld)PIn.PInt(tableOut.Rows[i][j].ToString())).ToString();
// break;
case "position":
tableOut.Rows[i][j]
=((PatientPosition)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "deductwaivprev":
case "flocovered":
case "misstoothexcl":
case "procstatus":
tableOut.Rows[i][j]=((ProcStat)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "majorwait":
case "hascaries":
case "needssealants":
case "cariesexperience":
case "earlychildcaries":
case "existingsealants":
case "missingallteeth":
tableOut.Rows[i][j]=((YN)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "prirelationship":
case "secrelationship":
tableOut.Rows[i][j]=((Relat)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "treatarea":
tableOut.Rows[i][j]
=((TreatmentArea)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "specialty":
tableOut.Rows[i][j]
=((DentalSpecialty)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "placeservice":
tableOut.Rows[i][j]
=((PlaceOfService)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "employrelated":
tableOut.Rows[i][j]
=((YN)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "schedtype":
tableOut.Rows[i][j]
=((ScheduleType)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "dayofweek":
tableOut.Rows[i][j]
=((DayOfWeek)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "race":
//TODO: Update this to return the patient's race(s) in a comma delimited list. Very important to bring this to Jordan's attention when reviewing.
tableOut.Rows[i][j]
=((PatientRaceOld)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "gradelevel":
tableOut.Rows[i][j]
=((PatientGrade)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
case "urgency":
tableOut.Rows[i][j]
=((TreatmentUrgency)PIn.Long(tableOut.Rows[i][j].ToString())).ToString();
break;
//miscellaneous:
case "provnum":
case "provhyg":
case "priprov":
case "secprov":
case "provtreat":
case "provbill":
tableOut.Rows[i][j]=Providers.GetAbbr(PIn.Long(tableOut.Rows[i][j].ToString()));
break;
case "covcatnum":
tableOut.Rows[i][j]=CovCats.GetDesc(PIn.Long(tableOut.Rows[i][j].ToString()));
break;
case "referringprov":
// tableOut.Rows[i][j]=CovCats.GetDesc(PIn.PInt(tableOut.Rows[i][j].ToString()));
break;
case "addtime":
if(tableOut.Rows[i][j].ToString()!="0")
tableOut.Rows[i][j]+="0";
break;
case "feesched":
case "feeschednum":
tableOut.Rows[i][j]=FeeScheds.GetDescription(PIn.Long(tableOut.Rows[i][j].ToString()));
break;
}//end switch column caption
}//end try
catch{
//return tableOut;
}
}//end for i rows
}//end for j cols
if(reportIn!=null){
for(int k=0;k<colTotals.Length;k++){
reportIn.ColTotal[k]=PIn.Decimal(colTotals[k].ToString("n"));
}
}
return tableOut;
}
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.AptDateTime";
}
else if (fieldsSelected[i] == "PriCarrier")
{
command += "(SELECT carrier.CarrierName FROM patplan,inssub,insplan,carrier WHERE patplan.PatNum=patient.PatNum "
+ "AND patplan.InsSubNum=inssub.InsSubNum AND inssub.PlanNum=insplan.PlanNum AND insplan.CarrierNum=carrier.CarrierNum AND patplan.Ordinal=1 " + DbHelper.LimitAnd(1) + ") PriCarrier";
}
else if (fieldsSelected[i] == "PriRelationship")
{
command += "(SELECT Relationship FROM patplan WHERE patplan.PatNum=patient.PatNum AND patplan.Ordinal=1 " + DbHelper.LimitAnd(1) + ") PriRelationship";
}
else if (fieldsSelected[i] == "SecCarrier")
{
command += "(SELECT carrier.CarrierName FROM patplan,inssub,insplan,carrier WHERE patplan.PatNum=patient.PatNum "
+ "AND patplan.InsSubNum=inssub.InsSubNum AND inssub.PlanNum=insplan.PlanNum AND insplan.CarrierNum=carrier.CarrierNum AND patplan.Ordinal=2 " + DbHelper.LimitAnd(1) + ") SecCarrier";
}
else if (fieldsSelected[i] == "SecRelationship")
{
command += "(SELECT Relationship FROM patplan WHERE patplan.PatNum=patient.PatNum AND patplan.Ordinal=2 " + DbHelper.LimitAnd(1) + ") SecRelationship";
}
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 += ")";
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = command;
FormQuery FormQ = new FormQuery(report);
FormQ.IsReport = false;
FormQ.SubmitQuery();
FormQ.textQuery.Text = report.Query;
FormQ.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (!checkBillTypesAll.Checked && listBillType.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one billing type must be selected.");
return;
}
if (!checkProvAll.Checked && listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (textDate.errorProvider1.GetError(textDate) != "")
{
MsgBox.Show(this, "Invalid date.");
return;
}
DateTime asOfDate = PIn.Date(textDate.Text);
//The aging report always show historical numbers based on the date entered.
Ledgers.ComputeAging(0, asOfDate, true);
ReportSimpleGrid report = new ReportSimpleGrid();
string cmd = "SELECT ";
if (PrefC.GetBool(PrefName.ReportsShowPatNum))
{
cmd += DbHelper.Concat("Cast(PatNum AS CHAR)", "'-'", "LName", "', '", "FName", "' '", "MiddleI");
}
else
{
cmd += DbHelper.Concat("LName", "', '", "FName", "' '", "MiddleI");
}
cmd += ",Bal_0_30,Bal_31_60,Bal_61_90,BalOver90"
+ ",BalTotal "
+ ",InsEst"
+ ",BalTotal-InsEst AS "; //\"$pat\" ";
if (DataConnection.DBtype == DatabaseType.MySql)
{
cmd += "$pat ";
}
else //Oracle needs quotes.
{
cmd += "\"$pat\" ";
}
cmd += "FROM patient "
+ "WHERE ";
if (checkExcludeInactive.Checked)
{
cmd += "(patstatus != 2) AND ";
}
if (checkBadAddress.Checked)
{
cmd += "(zip !='') AND ";
}
if (checkOnlyNeg.Checked)
{
cmd += "BalTotal < '-.005' ";
}
else
{
if (radioAny.Checked)
{
cmd +=
"(Bal_0_30 > '.005' OR Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if (radio30.Checked)
{
cmd +=
"(Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if (radio60.Checked)
{
cmd +=
"(Bal_61_90 > '.005' OR BalOver90 > '.005'";
}
else if (radio90.Checked)
{
cmd +=
"(BalOver90 > '.005'";
}
if (checkIncludeNeg.Checked)
{
cmd += " OR BalTotal < '-.005'";
}
cmd += ") ";
}
if (!checkBillTypesAll.Checked)
{
for (int i = 0; i < listBillType.SelectedIndices.Count; i++)
{
if (i == 0)
{
cmd += " AND (billingtype = ";
}
else
{
cmd += " OR billingtype = ";
}
cmd += POut.Long(DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].DefNum);
}
cmd += ") ";
}
if (!checkProvAll.Checked)
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i == 0)
{
cmd += " AND (PriProv = ";
}
else
{
cmd += " OR PriProv = ";
}
cmd += POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum);
}
cmd += ") ";
}
cmd += "ORDER BY LName,FName";
report.Query = cmd;
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
//Recompute aging in a non-historical way, so that the numbers are returned to the way they
//are normally used in other parts of the program.
Ledgers.RunAging();
//if(Prefs.UpdateString(PrefName.DateLastAging",POut.PDate(asOfDate,false))) {
// DataValid.SetInvalid(InvalidType.Prefs);
//}
report.Title = "AGING REPORT";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add("As of " + textDate.Text);
if (radioAny.Checked)
{
report.SubTitle.Add("Any Balance");
}
if (radio30.Checked)
{
report.SubTitle.Add("Over 30 Days");
}
if (radio60.Checked)
{
report.SubTitle.Add("Over 60 Days");
}
if (radio90.Checked)
{
report.SubTitle.Add("Over 90 Days");
}
if (checkBillTypesAll.Checked)
{
report.SubTitle.Add("All Billing Types");
}
else
{
string subt = DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[0]].ItemName;
for (int i = 1; i < listBillType.SelectedIndices.Count; i++)
{
subt += ", " + DefC.Short[(int)DefCat.BillingTypes][listBillType.SelectedIndices[i]].ItemName;
}
report.SubTitle.Add(subt);
}
//report.InitializeColumns(8);
report.SetColumn(this, 0, "GUARANTOR", 160);
report.SetColumn(this, 1, "0-30 DAYS", 80, HorizontalAlignment.Right);
report.SetColumn(this, 2, "30-60 DAYS", 80, HorizontalAlignment.Right);
report.SetColumn(this, 3, "60-90 DAYS", 80, HorizontalAlignment.Right);
report.SetColumn(this, 4, "> 90 DAYS", 80, HorizontalAlignment.Right);
report.SetColumn(this, 5, "TOTAL", 85, HorizontalAlignment.Right);
report.SetColumn(this, 6, "-INS EST", 85, HorizontalAlignment.Right);
report.SetColumn(this, 7, "=PATIENT", 85, HorizontalAlignment.Right);
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender,System.EventArgs e)
{
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 = '1' OR Status = 4) "//Recieved or supplemental. Otherwise, it's only an estimate.
+ whereProv
+ " GROUP BY DateCP "
+ "ORDER BY DateCP";
}
else {
report.Query = "SELECT ProcDate, "
+ "SUM(WriteOff) FROM claimproc WHERE "
+ "ProcDate >= '" + bDate + "' "
+ "AND ProcDate < '" + eDate + "' "
+ "AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " //received or supplemental or notreceived
+ 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.IsDiscount = '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) "//received or supplemental
+ "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.Double(ColTotal[1].ToString("n"));
report.ColTotal[2]=PIn.Double(ColTotal[2].ToString("n"));
report.ColTotal[3]=PIn.Double(ColTotal[3].ToString("n"));
report.ColTotal[4]=PIn.Double(ColTotal[4].ToString("n"));
report.ColTotal[5]=PIn.Double(ColTotal[5].ToString("n"));
report.ColTotal[6]=PIn.Double(ColTotal[6].ToString("n"));
report.ColTotal[7]=PIn.Double(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
}
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;
}
if(!checkAllProv.Checked && listProv.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one provider must be selected.");
return;
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(!checkAllClin.Checked && listClin.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one clinic must be selected.");
return;
}
}
whereProv="";
if(!checkAllProv.Checked) {
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]].ProvNum)+" ";
}
whereProv+=") ";
}
whereClin="";
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
whereClin+=" AND procedurelog.ClinicNum IN(";
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i>0) {
whereClin+=",";
}
if(Security.CurUser.ClinicIsRestricted) {
whereClin+=POut.Long(_listClinics[listClin.SelectedIndices[i]].ClinicNum);//we know that the list is a 1:1 to _listClinics
}
else {
if(listClin.SelectedIndices[i]==0) {
whereClin+="0";
}
else {
whereClin+=POut.Long(_listClinics[listClin.SelectedIndices[i]-1].ClinicNum);//Minus 1 from the selected index
}
}
}
whereClin+=") ";
}
ReportSimpleGrid report=new ReportSimpleGrid();
if(radioIndividual.Checked){
CreateIndividual(report);
}
else{
CreateGrouped(report);
}
}
private void butOK_Click(object sender, System.EventArgs e)
{
ReportSimpleGrid report=new ReportSimpleGrid();
report.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 "
+"AND (claim.claimstatus = 'U' OR claim.claimstatus = 'H' OR claim.claimstatus = 'W')";
if(radioRange.Checked==true){
report.Query
+=" AND claim.dateservice >= '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"' "
+"AND claim.dateservice <= '" + date2.SelectionStart.ToString("yyyy-MM-dd")+"'";
}
else{
report.Query
+=" AND claim.dateservice = '" + date1.SelectionStart.ToString("yyyy-MM-dd")+"'";
}
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
DataTable tempT=report.GetTempTable();
report.TableQ=new DataTable(null);//new table no name
for(int i=0;i<6;i++){//add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
for(int i=0;i<tempT.Rows.Count;i++) {//loop through data rows
DataRow row=report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
row[0]=(PIn.Date(tempT.Rows[i][0].ToString())).ToShortDateString();//claim dateservice
if(PIn.String(tempT.Rows[i][2].ToString())=="P")
row[1]="Primary";
if(PIn.String(tempT.Rows[i][2].ToString())=="S")
row[1]="Secondary";
if(PIn.String(tempT.Rows[i][2].ToString())=="PreAuth")
row[1]="PreAuth";
if(PIn.String(tempT.Rows[i][2].ToString())=="Other")
row[1]="Other";
if(tempT.Rows[i][3].ToString().Equals("H"))
row[2]="Holding";//Claim Status
else if(tempT.Rows[i][3].ToString().Equals("W"))
row[2]="WaitQ";//Claim Status, added SPK 7/15/04
else
row[2]="Unsent";//Claim Status
row[3]=tempT.Rows[i][4];//Patient name
row[4]=tempT.Rows[i][5];//Ins Carrier
row[5]=PIn.Double(tempT.Rows[i][6].ToString()).ToString("F");//claim fee
report.ColTotal[5]+=PIn.Double(tempT.Rows[i][6].ToString());
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();//this is a method in FormQuery2;
report.Title="Claims Not Sent";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if(radioRange.Checked==true){
report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
}
else{
report.SubTitle.Add(date1.SelectionStart.ToString("d"));
}
report.ColPos[0]=20;
report.ColPos[1]=145;
report.ColPos[2]=270;
report.ColPos[3]=395;
report.ColPos[4]=520;
report.ColPos[5]=645;
report.ColPos[6]=770;
report.ColCaption[0]="Date";
report.ColCaption[1]="Type";
report.ColCaption[2]="Claim Status";
report.ColCaption[3]="Patient Name";
report.ColCaption[4]="Insurance Carrier";
report.ColCaption[5]="Amount";
report.ColAlign[5]=HorizontalAlignment.Right;
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
ReportSimpleGrid report = new ReportSimpleGrid();
report.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 "
+ "AND (claim.claimstatus = 'U' OR claim.claimstatus = 'H' OR claim.claimstatus = 'W')";
if (radioRange.Checked == true)
{
report.Query
+= " AND claim.dateservice >= '" + date1.SelectionStart.ToString("yyyy-MM-dd") + "' "
+ "AND claim.dateservice <= '" + date2.SelectionStart.ToString("yyyy-MM-dd") + "'";
}
else
{
report.Query
+= " AND claim.dateservice = '" + date1.SelectionStart.ToString("yyyy-MM-dd") + "'";
}
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
DataTable tempT = report.GetTempTable();
report.TableQ = new DataTable(null); //new table no name
for (int i = 0; i < 6; i++) //add columns
{
report.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns
}
report.InitializeColumns();
for (int i = 0; i < tempT.Rows.Count; i++) //loop through data rows
{
DataRow row = report.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ
row[0] = (PIn.Date(tempT.Rows[i][0].ToString())).ToShortDateString(); //claim dateservice
if (PIn.String(tempT.Rows[i][2].ToString()) == "P")
{
row[1] = "Primary";
}
if (PIn.String(tempT.Rows[i][2].ToString()) == "S")
{
row[1] = "Secondary";
}
if (PIn.String(tempT.Rows[i][2].ToString()) == "PreAuth")
{
row[1] = "PreAuth";
}
if (PIn.String(tempT.Rows[i][2].ToString()) == "Other")
{
row[1] = "Other";
}
if (tempT.Rows[i][3].ToString().Equals("H"))
{
row[2] = "Holding"; //Claim Status
}
else if (tempT.Rows[i][3].ToString().Equals("W"))
{
row[2] = "WaitQ"; //Claim Status, added SPK 7/15/04
}
else
{
row[2] = "Unsent"; //Claim Status
}
row[3] = tempT.Rows[i][4]; //Patient name
row[4] = tempT.Rows[i][5]; //Ins Carrier
row[5] = PIn.Double(tempT.Rows[i][6].ToString()).ToString("F"); //claim fee
report.ColTotal[5] += PIn.Double(tempT.Rows[i][6].ToString());
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid(); //this is a method in FormQuery2;
report.Title = "Claims Not Sent";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if (radioRange.Checked == true)
{
report.SubTitle.Add(date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"));
}
else
{
report.SubTitle.Add(date1.SelectionStart.ToString("d"));
}
report.ColPos[0] = 20;
report.ColPos[1] = 145;
report.ColPos[2] = 270;
report.ColPos[3] = 395;
report.ColPos[4] = 520;
report.ColPos[5] = 645;
report.ColPos[6] = 770;
report.ColCaption[0] = "Date";
report.ColCaption[1] = "Type";
report.ColCaption[2] = "Claim Status";
report.ColCaption[3] = "Patient Name";
report.ColCaption[4] = "Insurance Carrier";
report.ColCaption[5] = "Amount";
report.ColAlign[5] = HorizontalAlignment.Right;
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butSubmit_Click(object sender, System.EventArgs e) {
report=new ReportSimpleGrid();
report.Query=textQuery.Text;
SubmitQuery();
}
private void butOK_Click(object sender, System.EventArgs e) {
if( textDateFrom.errorProvider1.GetError(textDateFrom)!=""
|| textDateTo.errorProvider1.GetError(textDateTo)!=""
){
MessageBox.Show(Lan.g(this,"Please fix data entry errors first."));
return;
}
if(listProv.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one provider must be selected.");
return;
}
if(listProv.SelectedIndices[0]==0 && listProv.SelectedIndices.Count>1){
MsgBox.Show(this,"You cannot select 'all' providers as well as specific providers.");
return;
}
DateTime dateFrom=PIn.Date(textDateFrom.Text);
DateTime dateTo=PIn.Date(textDateTo.Text);
if(dateTo<dateFrom) {
MsgBox.Show(this,"To date cannot be before From date.");
return;
}
string whereProv="";
if(listProv.SelectedIndices[0]!=0){
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i==0){
whereProv+=" AND (";
}
else{
whereProv+="OR ";
}
whereProv+="procedurelog.ProvNum = "
+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]-1].ProvNum)+" ";
}
whereProv+=") ";
}
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=@"SELECT referral.LName,referral.FName,
COUNT(DISTINCT refattach.PatNum) HowMany,
SUM(procedurelog.ProcFee) ""$HowMuch""";
if(checkAddress.Checked){
report.Query+=",referral.Title,referral.Address,referral.Address2,referral.City,"
+"referral.ST,referral.Zip,referral.Specialty";
}
report.Query+=@" FROM referral,refattach,procedurelog,patient
WHERE referral.ReferralNum=refattach.ReferralNum
AND procedurelog.PatNum=refattach.PatNum
AND procedurelog.PatNum=patient.PatNum
AND refattach.IsFrom=1
AND procedurelog.ProcStatus=2
AND procedurelog.ProcDate >= "+POut.Date(dateFrom)+" "
+"AND procedurelog.ProcDate <= "+POut.Date(dateTo)+" "
+whereProv;
if(checkNewPat.Checked){
report.Query+="AND patient.DateFirstVisit >= "+POut.Date(dateFrom)+" "
+"AND patient.DateFirstVisit <= "+POut.Date(dateTo)+" ";
}
report.Query+=@"GROUP BY referral.ReferralNum
ORDER BY HowMany Desc";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
report.Title="Referral Analysis";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
if(listProv.SelectedIndices[0]==0){
report.SubTitle.Add(Lan.g(this,"All Providers"));
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
else if(listProv.SelectedIndices.Count==1){
report.SubTitle.Add(Lan.g(this,"Prov: ")+ProviderC.ListShort[listProv.SelectedIndices[0]-1].GetLongDesc());
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
else{
//I'm too lazy to build a description for multiple providers as well as ensure that it fits the space.
report.SubTitle.Add(dateFrom.ToString("d")+" - "+dateTo.ToString("d"));
}
if(checkAddress.Checked){
report.SetColumnPos(this,0,"Last Name",100);
report.SetColumnPos(this,1,"First Name",200);
report.SetColumnPos(this,2,"Count",270);
report.SetColumnPos(this,3,"Production",350,HorizontalAlignment.Right);
report.SetColumnPos(this,4,"Title",390);
report.SetColumnPos(this,5,"Address",490);
report.SetColumnPos(this,6,"Add2",530);
report.SetColumnPos(this,7,"City",590);
report.SetColumnPos(this,8,"ST",630);
report.SetColumnPos(this,9,"Zip",680);
report.SetColumnPos(this,10,"Specialty",880);//off the right side
}
else{
report.SetColumnPos(this,0,"Last Name",100);
report.SetColumnPos(this,1,"First Name",200);
report.SetColumnPos(this,2,"Count",270);
report.SetColumnPos(this,3,"Production",350,HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (date2.SelectionStart < date1.SelectionStart)
{
MsgBox.Show(this, "End date cannot be before start date.");
return;
}
if (listAdjType.SelectedIndices.Count == 0)
{
MessageBox.Show("At least one adjustment type must be selected.");
return;
}
ReportSimpleGrid report = new ReportSimpleGrid();
string types = "";
for (int i = 0; i < listAdjType.SelectedIndices.Count; i++)
{
if (i == 0)
{
types += "(";
}
else
{
types += "OR ";
}
types += "AdjType='"
+ _listAdjTypeDefs[listAdjType.SelectedIndices[i]].DefNum.ToString()
+ "' ";
}
types += ")";
report.Query = @"SELECT patient.PatNum,MIN(procedurelog.ProcDate) AS ProcDate,
CONCAT(CONCAT(provider.LName,', '),provider.FName) as ProvName,
County,county.CountyCode,
site.Description AS gradeschool,site.Note AS schoolCode,GradeLevel,Birthdate,"
+ DbHelper.GroupConcat("patientrace.Race", true) //distinct races from the patient race table in a comma delimited list of ints
+ @" Race,Gender,Urgency,BillingType,
patient.PlannedIsDone,broken.NumberBroken
FROM patient
LEFT JOIN patientrace ON patient.PatNum=patientrace.PatNum
LEFT JOIN procedurelog ON procedurelog.PatNum=patient.PatNum
LEFT JOIN provider ON procedurelog.ProvNum=provider.ProvNum
LEFT JOIN site ON patient.SiteNum=site.SiteNum
LEFT JOIN county ON patient.County=county.CountyName
LEFT JOIN (
SELECT PatNum,COUNT(*) NumberBroken
FROM adjustment WHERE " + types
+ "AND AdjDate >= " + POut.Date(date1.SelectionStart) + " "
+ "AND AdjDate <= " + POut.Date(date2.SelectionStart) + " "
+ @"GROUP BY PatNum
) broken ON broken.PatNum=patient.PatNum
WHERE (procedurelog.ProcStatus='2'
AND procedurelog.ProcDate >= " + POut.Date(date1.SelectionStart) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(date2.SelectionStart) + " )"
+ "OR broken.NumberBroken>0 "
+ @"GROUP BY patient.PatNum
ORDER By ProcDate;" ;
FormQuery2 = new FormQuery(report);
FormQuery2.textTitle.Text = "RawPopulationData" + 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 (!checkAllProv.Checked && listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
if (!checkAllClin.Checked && listClin.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one clinic must be selected.");
return;
}
}
string whereProv = "";
if (!checkAllProv.Checked)
{
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereProv += " AND (";
}
else
{
whereProv += "OR ";
}
whereProv += "claimproc.ProvNum = "
+ POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum) + " ";
}
whereProv += ") ";
}
string whereClin = "";
if (!checkAllClin.Checked)
{
for (int i = 0; i < listClin.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereClin += " AND (";
}
else
{
whereClin += "OR ";
}
if (listClin.SelectedIndices[i] == 0)
{
whereClin += "claimproc.ClinicNum = 0 ";
}
else
{
whereClin += "claimproc.ClinicNum = " + POut.Long(Clinics.List[listClin.SelectedIndices[i] - 1].ClinicNum) + " ";
}
}
whereClin += ") ";
}
ReportSimpleGrid report = new ReportSimpleGrid();
report.Query = "SET @FromDate=" + POut.Date(date1.SelectionStart) + ", @ToDate=" + POut.Date(date2.SelectionStart) + ";";
if (radioWriteoffPay.Checked)
{
report.Query += "SELECT " + DbHelper.DateColumn("claimproc.DateCP") + " date,"
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ","
+ "carrier.CarrierName,"
+ "provider.Abbr,";
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
report.Query += "clinic.Description,";
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "SUM(claimproc.WriteOff) $amount,";
}
else //Oracle needs quotes.
{
report.Query += "SUM(claimproc.WriteOff) \"$amount\",";
}
report.Query += "claimproc.ClaimNum "
+ "FROM claimproc " //,insplan,patient,carrier,provider "
+ "LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum "
+ "LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
+ "LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
+ "LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
+ "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+ "WHERE (claimproc.Status=1 OR claimproc.Status=4) " /*received or supplemental*/
+ whereProv
+ whereClin
+ "AND claimproc.DateCP >= @FromDate "
+ "AND claimproc.DateCP <= @ToDate "
+ "AND claimproc.WriteOff > 0 "
+ "GROUP BY claimproc.ProvNum,claimproc.DateCP,claimproc.ClinicNum,claimproc.PatNum "
+ "ORDER BY claimproc.DateCP,claimproc.PatNum";
}
else //using procedure date
{
report.Query += "SELECT " + DbHelper.DateColumn("claimproc.ProcDate") + " date, "
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ", "
+ "carrier.CarrierName, "
+ "provider.Abbr,";
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
report.Query += "clinic.Description,";
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "SUM(claimproc.WriteOff) $amount, ";
}
else //Oracle needs quotes.
{
report.Query += "SUM(claimproc.WriteOff) \"$amount\", ";
}
report.Query += "claimproc.ClaimNum "
+ "FROM claimproc " //,insplan,patient,carrier,provider "
+ "LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum "
+ "LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
+ "LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
+ "LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
+ "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+ "WHERE (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " /*received or supplemental or notreceived*/
+ whereProv
+ whereClin
+ "AND claimproc.ProcDate >= @FromDate "
+ "AND claimproc.ProcDate <= @ToDate "
+ "AND claimproc.WriteOff > 0 "
+ "GROUP BY claimproc.ProvNum,claimproc.ProcDate,claimproc.ClinicNum,claimproc.PatNum "
+ "ORDER BY claimproc.ProcDate,claimproc.PatNum";
}
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Daily Writeoffs";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"));
if (checkAllProv.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Providers"));
}
else
{
string provNames = "";
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i > 0)
{
provNames += ", ";
}
provNames += ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(provNames);
}
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
if (checkAllClin.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Clinics"));
}
else
{
string clinNames = "";
for (int i = 0; i < listClin.SelectedIndices.Count; i++)
{
if (i > 0)
{
clinNames += ", ";
}
if (listClin.SelectedIndices[i] == 0)
{
clinNames += Lan.g(this, "Unassigned");
}
else
{
clinNames += Clinics.List[listClin.SelectedIndices[i] - 1].Description;
}
}
report.SubTitle.Add(clinNames);
}
}
report.SetColumn(this, 0, "Date", 100);
report.SetColumn(this, 1, "Patient Name", 150);
report.SetColumn(this, 2, "Carrier", 225);
report.SetColumn(this, 3, "Provider", 60);
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
report.SetColumn(this, 4, "Clinic", 80);
report.SetColumn(this, 5, "Amount", 75, HorizontalAlignment.Right);
report.SetColumn(this, 6, "", 280, HorizontalAlignment.Right);
}
else
{
report.SetColumn(this, 4, "Amount", 75, HorizontalAlignment.Right);
report.SetColumn(this, 5, "", 280, HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e) {
if(textDateFrom.errorProvider1.GetError(textDateFrom)!=""
|| textDateTo.errorProvider1.GetError(textDateTo)!="")
{
MsgBox.Show(this,"Please fix data entry errors first.");
return;
}
DateTime dateFrom=PIn.Date(textDateFrom.Text);
DateTime dateTo=PIn.Date(textDateTo.Text);
if(dateTo<dateFrom) {
MsgBox.Show(this,"To date cannot be before From date.");
return;
}
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=
"SELECT "+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+",AdjAmt "
+"FROM patient,adjustment "
+"WHERE patient.PatNum=adjustment.PatNum "
+"AND adjustment.AdjDate BETWEEN "+POut.Date(dateFrom)+" AND "+POut.Date(dateTo)+" "
+"AND adjustment.AdjType = '"+POut.Long(PrefC.GetLong(PrefName.FinanceChargeAdjustmentType))+"'";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
report.Title=Lans.g(this,"FINANCE CHARGE REPORT");
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(textDateFrom.Text+" - "+textDateTo.Text);
report.SetColumn(this,0,"Patient Name",180);
report.SetColumn(this,1,"Amount",100,HorizontalAlignment.Right);
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e) {
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=SQLstatement;
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)
{
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.AptDateTime";
}
else if(fieldsSelected[i]=="PriCarrier"){
command+="(SELECT carrier.CarrierName FROM patplan,inssub,insplan,carrier WHERE patplan.PatNum=patient.PatNum "
+"AND patplan.InsSubNum=inssub.InsSubNum AND inssub.PlanNum=insplan.PlanNum AND insplan.CarrierNum=carrier.CarrierNum AND patplan.Ordinal=1) PriCarrier";
}
else if(fieldsSelected[i]=="PriRelationship") {
command+="(SELECT Relationship FROM patplan WHERE patplan.PatNum=patient.PatNum AND patplan.Ordinal=1) PriRelationship";
}
else if(fieldsSelected[i]=="SecCarrier") {
command+="(SELECT carrier.CarrierName FROM patplan,inssub,insplan,carrier WHERE patplan.PatNum=patient.PatNum "
+"AND patplan.InsSubNum=inssub.InsSubNum AND inssub.PlanNum=insplan.PlanNum AND insplan.CarrierNum=carrier.CarrierNum AND patplan.Ordinal=2) SecCarrier";
}
else if(fieldsSelected[i]=="SecRelationship") {
command+="(SELECT Relationship FROM patplan WHERE patplan.PatNum=patient.PatNum AND patplan.Ordinal=2) SecRelationship";
}
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+=")";
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query=command;
FormQuery FormQ=new FormQuery(report);
FormQ.IsReport=false;
FormQ.SubmitQuery();
FormQ.textQuery.Text=report.Query;
FormQ.ShowDialog();
DialogResult=DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (listAdjType.SelectedIndices.Count == 0)
{
MessageBox.Show("At least one adjustment type must be selected.");
return;
}
ReportSimpleGrid report = new ReportSimpleGrid();
string types = "";
for (int i = 0; i < listAdjType.SelectedIndices.Count; i++)
{
if (i == 0)
{
types += "(";
}
else
{
types += "OR ";
}
types += "AdjType='"
+ DefC.Short[(int)DefCat.AdjTypes][listAdjType.SelectedIndices[i]].DefNum.ToString()
+ "' ";
}
types += ")";
report.Query = @"
CREATE TEMPORARY TABLE tempbroken(
PatNum bigint unsigned NOT NULL,
NumberBroken smallint NOT NULL,
PRIMARY KEY (PatNum)
);
INSERT INTO tempbroken SELECT PatNum,COUNT(*)
FROM adjustment WHERE " + types
+ "AND AdjDate >= " + POut.Date(date1.SelectionStart) + " "
+ "AND AdjDate <= " + POut.Date(date2.SelectionStart) + " "
+ @"GROUP BY PatNum;
SELECT patient.PatNum,MIN(procedurelog.ProcDate) AS ProcDate,
CONCAT(CONCAT(provider.LName,', '),provider.FName) as ProvName,
County,county.CountyCode,
site.Description AS gradeschool,site.Note AS schoolCode,GradeLevel,Birthdate,Race,Gender,Urgency,BillingType,
patient.PlannedIsDone,tempbroken.NumberBroken
FROM patient
LEFT JOIN procedurelog ON procedurelog.PatNum=patient.PatNum
LEFT JOIN provider ON procedurelog.ProvNum=provider.ProvNum
LEFT JOIN site ON patient.SiteNum=site.SiteNum
LEFT JOIN county ON patient.County=county.CountyName
LEFT JOIN tempbroken ON tempbroken.PatNum=patient.PatNum
WHERE (procedurelog.ProcStatus='2'
AND procedurelog.ProcDate >= " + POut.Date(date1.SelectionStart) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(date2.SelectionStart) + " )"
+ "OR tempbroken.NumberBroken>0 "
+ @"GROUP BY patient.PatNum
ORDER By ProcDate;
DROP TABLE tempbroken;" ;
/*
* CREATE TEMPORARY TABLE tempbroken(
* PatNum mediumint unsigned NOT NULL,
* NumberBroken smallint NOT NULL,
* PRIMARY KEY (PatNum)
* );
* INSERT INTO tempbroken
* SELECT PatNum,COUNT(*)
* FROM adjustment
* WHERE AdjType='14'
* && AdjDate='2004-05-03'
* GROUP BY PatNum;
* SELECT MIN(procedurelog.ProcDate) AS ProcDate,
* CONCAT(provider.LName,', ',provider.FName) as ProvName,
* County,county.CountyCode,
* GradeSchool,school.SchoolCode,GradeLevel,Birthdate,Race,Gender,Urgency,BillingType,
* patient.NextAptNum='-1' AS Done,tempbroken.NumberBroken
* FROM patient,procedurelog,provider,tempbroken
* LEFT JOIN school ON patient.GradeSchool=school.SchoolName
* LEFT JOIN county ON patient.County=county.CountyName
* WHERE procedurelog.ProcStatus='2'
* && patient.PatNum=procedurelog.PatNum
* && procedurelog.ProvNum=provider.ProvNum
* && tempbroken.PatNum=patient.PatNum
* && procedurelog.ProcDate >= '2004-05-03'
* && procedurelog.ProcDate <= '2004-05-03'
* GROUP BY procedurelog.PatNum
* ORDER By ProcDate;
* DROP TABLE tempbroken;
*
*
*/
FormQuery2 = new FormQuery(report);
FormQuery2.textTitle.Text = "RawPopulationData" + 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(listAdjType.SelectedIndices.Count==0){
MessageBox.Show("At least one adjustment type must be selected.");
return;
}
ReportSimpleGrid report=new ReportSimpleGrid();
string types="";
for(int i=0;i<listAdjType.SelectedIndices.Count;i++){
if(i==0){
types+="(";
}
else{
types+="OR ";
}
types+="AdjType='"
+DefC.Short[(int)DefCat.AdjTypes][listAdjType.SelectedIndices[i]].DefNum.ToString()
+"' ";
}
types+=")";
report.Query=@"
CREATE TEMPORARY TABLE tempbroken(
PatNum bigint unsigned NOT NULL,
NumberBroken smallint NOT NULL,
PRIMARY KEY (PatNum)
);
INSERT INTO tempbroken SELECT PatNum,COUNT(*)
FROM adjustment WHERE "+types
+"AND AdjDate >= "+POut.Date(date1.SelectionStart)+" "
+"AND AdjDate <= " +POut.Date(date2.SelectionStart)+" "
+@"GROUP BY PatNum;
SELECT patient.PatNum,MIN(procedurelog.ProcDate) AS ProcDate,
CONCAT(CONCAT(provider.LName,', '),provider.FName) as ProvName,
County,county.CountyCode,
site.Description AS gradeschool,site.Note AS schoolCode,GradeLevel,Birthdate,Race,Gender,Urgency,BillingType,
patient.PlannedIsDone,tempbroken.NumberBroken
FROM patient
LEFT JOIN procedurelog ON procedurelog.PatNum=patient.PatNum
LEFT JOIN provider ON procedurelog.ProvNum=provider.ProvNum
LEFT JOIN site ON patient.SiteNum=site.SiteNum
LEFT JOIN county ON patient.County=county.CountyName
LEFT JOIN tempbroken ON tempbroken.PatNum=patient.PatNum
WHERE (procedurelog.ProcStatus='2'
AND procedurelog.ProcDate >= "+POut.Date(date1.SelectionStart)+" "
+"AND procedurelog.ProcDate <= " +POut.Date(date2.SelectionStart)+" )"
+"OR tempbroken.NumberBroken>0 "
+@"GROUP BY patient.PatNum
ORDER By ProcDate;
DROP TABLE tempbroken;";
/*
CREATE TEMPORARY TABLE tempbroken(
PatNum mediumint unsigned NOT NULL,
NumberBroken smallint NOT NULL,
PRIMARY KEY (PatNum)
);
INSERT INTO tempbroken
SELECT PatNum,COUNT(*)
FROM adjustment
WHERE AdjType='14'
&& AdjDate='2004-05-03'
GROUP BY PatNum;
SELECT MIN(procedurelog.ProcDate) AS ProcDate,
CONCAT(provider.LName,', ',provider.FName) as ProvName,
County,county.CountyCode,
GradeSchool,school.SchoolCode,GradeLevel,Birthdate,Race,Gender,Urgency,BillingType,
patient.NextAptNum='-1' AS Done,tempbroken.NumberBroken
FROM patient,procedurelog,provider,tempbroken
LEFT JOIN school ON patient.GradeSchool=school.SchoolName
LEFT JOIN county ON patient.County=county.CountyName
WHERE procedurelog.ProcStatus='2'
&& patient.PatNum=procedurelog.PatNum
&& procedurelog.ProvNum=provider.ProvNum
&& tempbroken.PatNum=patient.PatNum
&& procedurelog.ProcDate >= '2004-05-03'
&& procedurelog.ProcDate <= '2004-05-03'
GROUP BY procedurelog.PatNum
ORDER By ProcDate;
DROP TABLE tempbroken;
*/
FormQuery2=new FormQuery(report);
FormQuery2.textTitle.Text="RawPopulationData"+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 (!checkAllProv.Checked && listProv.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one provider must be selected.");
return;
}
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
if (!checkAllClin.Checked && listClin.SelectedIndices.Count == 0)
{
MsgBox.Show(this, "At least one clinic must be selected.");
return;
}
}
whereProv = "";
if (!checkAllProv.Checked)
{
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]].ProvNum) + " ";
}
whereProv += ") ";
}
whereClin = "";
if (!checkAllClin.Checked)
{
for (int i = 0; i < listClin.SelectedIndices.Count; i++)
{
if (i == 0)
{
whereClin += " AND (";
}
else
{
whereClin += "OR ";
}
if (listClin.SelectedIndices[i] == 0)
{
whereClin += "procedurelog.ClinicNum = 0 ";
}
else
{
whereClin += "procedurelog.ClinicNum = " + POut.Long(Clinics.List[listClin.SelectedIndices[i] - 1].ClinicNum) + " ";
}
}
whereClin += ") ";
}
ReportSimpleGrid report = new ReportSimpleGrid();
if (radioIndividual.Checked)
{
CreateIndividual(report);
}
else
{
CreateGrouped(report);
}
}
private void butOK_Click(object sender, System.EventArgs e) {
long feeSched=FeeSchedC.ListShort[listFeeSched.SelectedIndex].FeeSchedNum;
string catName=""; //string to hold current category name
Fees fee=new Fees();
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query= "SELECT procedurecode.ProcCode,fee.Amount,' ',procedurecode.Descript,"
+"procedurecode.AbbrDesc FROM procedurecode,fee "
+"WHERE procedurecode.CodeNum=fee.CodeNum AND fee.FeeSched='"+feeSched.ToString()
+"' ORDER BY procedurecode.ProcCode";
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
if (radioCode.Checked==true) {
FormQuery2.SubmitReportQuery();
report.Title="Procedure Codes";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(FeeScheds.GetDescription(feeSched));
report.SetColumn(this,0,"Code",70);
report.SetColumn(this,1,"Fee Amount",70,HorizontalAlignment.Right);
report.SetColumn(this,2," ",80);//otherwise, the amount gets bunched up next to the description.
report.SetColumn(this,3,"Description",200);
report.SetColumn(this,4,"Abbr Description",200);
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
else {//categories
//report.SubmitTemp();//create TableTemp which is not actually used
ProcedureCode[] ProcList=ProcedureCodes.GetProcList();
report.TableQ=new DataTable(null);
for(int i=0;i<5;i++){//add columns
report.TableQ.Columns.Add(new System.Data.DataColumn());//blank columns
}
report.InitializeColumns();
DataRow row=report.TableQ.NewRow();//add first row by hand to get value for temp
row[0]=DefC.GetName(DefCat.ProcCodeCats,ProcList[0].ProcCat);
catName=row[0].ToString();
row[1]=ProcList[0].ProcCode;
row[2]=ProcList[0].Descript;
row[3]=ProcList[0].AbbrDesc;
row[4]=((double)Fees.GetAmount0(ProcList[0].CodeNum,feeSched)).ToString("F");
report.ColTotal[4]+=PIn.Decimal(row[4].ToString());
report.TableQ.Rows.Add(row);
for(int i=1;i<ProcList.Length;i++){//loop through data rows
row=report.TableQ.NewRow();//create new row called 'row' based on structure of TableQ
row[0]=DefC.GetName(DefCat.ProcCodeCats,ProcList[i].ProcCat);
if(catName==row[0].ToString()){
row[0]="";
}
else {
catName=row[0].ToString();
}
row[1]=ProcList[i].ProcCode.ToString();
row[2]=ProcList[i].Descript;
row[3]=ProcList[i].AbbrDesc.ToString();
row[4]=((double)Fees.GetAmount0(ProcList[i].CodeNum,feeSched)).ToString("F");
//report.ColTotal[4]+=PIn.PDouble(row[4].ToString());
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();//this is a method in FormQuery2;
report.Title="Procedure Codes";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(FeeScheds.GetDescription(feeSched));
report.ColPos[0]=20;
report.ColPos[1]=120;
report.ColPos[2]=270;
report.ColPos[3]=470;
report.ColPos[4]=620;
report.ColPos[5]=770;
report.ColCaption[0]="Category";
report.ColCaption[1]="Code";
report.ColCaption[2]="Description";
report.ColCaption[3]="Abbr Description";
report.ColCaption[4]="Fee Amount";
report.ColAlign[4]=HorizontalAlignment.Right;
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
}
private void CreateIndividual(ReportSimpleGrid report)
{
//added Procnum to retrieve all codes
report.Query = "SELECT procedurelog.ProcDate,"
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + " "
+ "AS plfname, procedurecode.ProcCode,"
+ "procedurelog.ToothNum,procedurecode.Descript,provider.Abbr,"
+ "procedurelog.ClinicNum,"
+ "procedurelog.ProcFee-IFNULL(SUM(claimproc.WriteOff),0) "; //\"$fee\" " //if no writeoff, then subtract 0
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "$fee ";
}
else //Oracle needs quotes.
{
report.Query += "\"$fee\" ";
}
report.Query += "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.CodeNum=procedurecode.CodeNum "
+ "AND provider.ProvNum=procedurelog.ProvNum "
+ whereProv
+ whereClin
+ "AND procedurecode.ProcCode LIKE '%" + POut.String(textCode.Text) + "%' "
+ "AND " + DbHelper.DateColumn("procedurelog.ProcDate") + " >= " + POut.Date(date1.SelectionStart) + " "
+ "AND " + DbHelper.DateColumn("procedurelog.ProcDate") + " <= " + POut.Date(date2.SelectionStart) + " "
+ "GROUP BY procedurelog.ProcNum "
+ "ORDER BY " + DbHelper.DateColumn("procedurelog.ProcDate") + ",plfname,procedurecode.ProcCode,ToothNum";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
DataTable table = report.GetTempTable();
report.TableQ = new DataTable(null);
int colI = 7;
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
colI = 8;
}
for (int i = 0; i < colI; i++) //add columns
{
report.TableQ.Columns.Add(new System.Data.DataColumn()); //blank columns
}
report.InitializeColumns();
DataRow row;
double dbl = 0;
for (int i = 0; i < table.Rows.Count; i++)
{
row = report.TableQ.NewRow(); //create new row called 'row' based on structure of TableQ
row[0] = PIn.Date(table.Rows[i][0].ToString()).ToShortDateString();
row[1] = table.Rows[i][1].ToString(); //name
row[2] = table.Rows[i][2].ToString(); //adacode
row[3] = Tooth.ToInternat(table.Rows[i][3].ToString()); //tooth
row[4] = table.Rows[i][4].ToString(); //descript
row[5] = table.Rows[i][5].ToString(); //prov
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
row[6] = Clinics.GetDesc(PIn.Long(table.Rows[i][6].ToString())); //clinic
dbl = PIn.Double(table.Rows[i][7].ToString()); //fee
row[7] = dbl.ToString("n");
report.ColTotal[7] += dbl;
}
else
{
dbl = PIn.Double(table.Rows[i][7].ToString()); //fee
row[6] = dbl.ToString("n");
report.ColTotal[6] += dbl;
}
report.TableQ.Rows.Add(row);
}
FormQuery2.ResetGrid();
report.Title = "Daily Procedures";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"));
if (checkAllProv.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Providers"));
}
else
{
string provNames = "";
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i > 0)
{
provNames += ", ";
}
provNames += ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(provNames);
}
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
if (checkAllClin.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Clinics"));
}
else
{
string clinNames = "";
for (int i = 0; i < listClin.SelectedIndices.Count; i++)
{
if (i > 0)
{
clinNames += ", ";
}
if (listClin.SelectedIndices[i] == 0)
{
clinNames += Lan.g(this, "Unassigned");
}
else
{
clinNames += Clinics.List[listClin.SelectedIndices[i] - 1].Description;
}
}
report.SubTitle.Add(clinNames);
}
}
report.SetColumn(this, 0, "Date", 80);
report.SetColumn(this, 1, "Patient Name", 130);
report.SetColumn(this, 2, "ADA Code", 75);
report.SetColumn(this, 3, "Tooth", 45);
report.SetColumn(this, 4, "Description", 200);
report.SetColumn(this, 5, "Provider", 50);
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
report.SetColumn(this, 6, "Clinic", 70);
report.SetColumn(this, 7, "Fee", 90, HorizontalAlignment.Right);
}
else
{
report.SetColumn(this, 6, "Fee", 90, HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e) {
if(!checkAllProv.Checked && listProv.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one provider must be selected.");
return;
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(!checkAllClin.Checked && listClin.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one clinic must be selected.");
return;
}
}
string whereProv="";
if(!checkAllProv.Checked) {
for(int i=0;i<listProv.SelectedIndices.Count;i++){
if(i==0){
whereProv+=" AND (";
}
else{
whereProv+="OR ";
}
whereProv+="claimproc.ProvNum = "
+POut.Long(ProviderC.ListShort[listProv.SelectedIndices[i]].ProvNum)+" ";
}
whereProv+=") ";
}
string whereClin="";
if(!checkAllClin.Checked) {
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i==0) {
whereClin+=" AND (";
}
else {
whereClin+="OR ";
}
if(listClin.SelectedIndices[i]==0) {
whereClin+="claimproc.ClinicNum = 0 ";
}
else {
whereClin+="claimproc.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
}
}
whereClin+=") ";
}
ReportSimpleGrid report=new ReportSimpleGrid();
report.Query="SET @FromDate="+POut.Date(date1.SelectionStart)+", @ToDate="+POut.Date(date2.SelectionStart)+";";
if(radioWriteoffPay.Checked){
report.Query+="SELECT "+DbHelper.DateColumn("claimproc.DateCP")+" date,"
+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+","
+"carrier.CarrierName,"
+"provider.Abbr,";
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
report.Query+="clinic.Description,";
}
if(DataConnection.DBtype==DatabaseType.MySql) {
report.Query+="SUM(claimproc.WriteOff) $amount,";
}
else {//Oracle needs quotes.
report.Query+="SUM(claimproc.WriteOff) \"$amount\",";
}
report.Query+="claimproc.ClaimNum "
+"FROM claimproc "//,insplan,patient,carrier,provider "
+"LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum "
+"LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
+"LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
+"LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
+"LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+"WHERE (claimproc.Status=1 OR claimproc.Status=4) " /*received or supplemental*/
+whereProv
+whereClin
+"AND claimproc.DateCP >= @FromDate "
+"AND claimproc.DateCP <= @ToDate "
+"AND claimproc.WriteOff > 0 "
+"GROUP BY claimproc.ProvNum,claimproc.DateCP,claimproc.ClinicNum,claimproc.PatNum "
+"ORDER BY claimproc.DateCP,claimproc.PatNum";
}
else{//using procedure date
report.Query+="SELECT "+DbHelper.DateColumn("claimproc.ProcDate")+" date, "
+DbHelper.Concat("patient.LName","', '","patient.FName","' '","patient.MiddleI")+", "
+"carrier.CarrierName, "
+"provider.Abbr,";
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
report.Query+="clinic.Description,";
}
if(DataConnection.DBtype==DatabaseType.MySql) {
report.Query+="SUM(claimproc.WriteOff) $amount, ";
}
else {//Oracle needs quotes.
report.Query+="SUM(claimproc.WriteOff) \"$amount\", ";
}
report.Query+="claimproc.ClaimNum "
+"FROM claimproc "//,insplan,patient,carrier,provider "
+"LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum "
+"LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
+"LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
+"LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
+"LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+"WHERE (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " /*received or supplemental or notreceived*/
+whereProv
+whereClin
+"AND claimproc.ProcDate >= @FromDate "
+"AND claimproc.ProcDate <= @ToDate "
+"AND claimproc.WriteOff > 0 "
+"GROUP BY claimproc.ProvNum,claimproc.ProcDate,claimproc.ClinicNum,claimproc.PatNum "
+"ORDER BY claimproc.ProcDate,claimproc.PatNum";
}
FormQuery2=new FormQuery(report);
FormQuery2.IsReport=true;
FormQuery2.SubmitReportQuery();
report.Title="Daily Writeoffs";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(date1.SelectionStart.ToString("d")+" - "+date2.SelectionStart.ToString("d"));
if(checkAllProv.Checked) {
report.SubTitle.Add(Lan.g(this,"All Providers"));
}
else {
string provNames="";
for(int i=0;i<listProv.SelectedIndices.Count;i++) {
if(i>0) {
provNames+=", ";
}
provNames+=ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(provNames);
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(checkAllClin.Checked) {
report.SubTitle.Add(Lan.g(this,"All Clinics"));
}
else {
string clinNames="";
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i>0) {
clinNames+=", ";
}
if(listClin.SelectedIndices[i]==0) {
clinNames+=Lan.g(this,"Unassigned");
}
else {
clinNames+=Clinics.List[listClin.SelectedIndices[i]-1].Description;
}
}
report.SubTitle.Add(clinNames);
}
}
report.SetColumn(this,0,"Date",100);
report.SetColumn(this,1,"Patient Name",150);
report.SetColumn(this,2,"Carrier",225);
report.SetColumn(this,3,"Provider",60);
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
report.SetColumn(this,4,"Clinic",80);
report.SetColumn(this,5,"Amount",75,HorizontalAlignment.Right);
report.SetColumn(this,6,"",280,HorizontalAlignment.Right);
}
else {
report.SetColumn(this,4,"Amount",75,HorizontalAlignment.Right);
report.SetColumn(this,5,"",280,HorizontalAlignment.Right);
}
FormQuery2.ShowDialog();
DialogResult=DialogResult.OK;
}
private void CreateGrouped(ReportSimpleGrid report)
{
//this would require a temporary table to be able to handle capitation.
report.Query = "SELECT definition.ItemName,procedurecode.ProcCode,procedurecode.Descript,";
if (DataConnection.DBtype == DatabaseType.MySql)
{
report.Query += "Count(*),AVG(procedurelog.ProcFee) $AvgFee,SUM(procedurelog.ProcFee) AS $TotFee ";
}
else //Oracle needs quotes.
{
report.Query += "Count(*),AVG(procedurelog.ProcFee) \"$AvgFee\",SUM(procedurelog.ProcFee) AS \"$TotFee\" ";
}
report.Query += "FROM procedurelog,procedurecode,definition "
+ "WHERE procedurelog.ProcStatus = '2' "
+ "AND procedurelog.CodeNum=procedurecode.CodeNum "
+ "AND definition.DefNum=procedurecode.ProcCat "
+ whereProv
+ whereClin
+ "AND procedurecode.ProcCode LIKE '%" + POut.String(textCode.Text) + "%' "
+ "AND " + DbHelper.DateColumn("procedurelog.ProcDate") + " >= '" + date1.SelectionStart.ToString("yyyy-MM-dd") + "' "
+ "AND " + DbHelper.DateColumn("procedurelog.ProcDate") + " <= '" + date2.SelectionStart.ToString("yyyy-MM-dd") + "' "
+ "GROUP BY procedurecode.ProcCode "
+ "ORDER BY definition.ItemOrder,procedurecode.ProcCode";
FormQuery2 = new FormQuery(report);
FormQuery2.IsReport = true;
FormQuery2.SubmitReportQuery();
report.Title = "Procedures By Procedure Code";
report.SubTitle.Add(PrefC.GetString(PrefName.PracticeTitle));
report.SubTitle.Add(date1.SelectionStart.ToString("d") + " - " + date2.SelectionStart.ToString("d"));
if (checkAllProv.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Providers"));
}
else
{
string provNames = "";
for (int i = 0; i < listProv.SelectedIndices.Count; i++)
{
if (i > 0)
{
provNames += ", ";
}
provNames += ProviderC.ListShort[listProv.SelectedIndices[i]].Abbr;
}
report.SubTitle.Add(provNames);
}
if (!PrefC.GetBool(PrefName.EasyNoClinics))
{
if (checkAllClin.Checked)
{
report.SubTitle.Add(Lan.g(this, "All Clinics"));
}
else
{
string clinNames = "";
for (int i = 0; i < listClin.SelectedIndices.Count; i++)
{
if (i > 0)
{
clinNames += ", ";
}
if (listClin.SelectedIndices[i] == 0)
{
clinNames += Lan.g(this, "Unassigned");
}
else
{
clinNames += Clinics.List[listClin.SelectedIndices[i] - 1].Description;
}
}
report.SubTitle.Add(clinNames);
}
}
report.SetColumn(this, 0, "Category", 150);
report.SetColumn(this, 1, "Code", 90);
report.SetColumn(this, 2, "Description", 180);
report.SetColumn(this, 3, "Quantity", 60, HorizontalAlignment.Right);
report.SetColumn(this, 4, "Average Fee", 110, HorizontalAlignment.Right);
report.SetColumn(this, 5, "Total Fees", 100, HorizontalAlignment.Right);
FormQuery2.ShowDialog();
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
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) {
if(!checkAllProv.Checked && listProv.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one provider must be selected.");
return;
}
if(!PrefC.GetBool(PrefName.EasyNoClinics)) {
if(!checkAllClin.Checked && listClin.SelectedIndices.Count==0) {
MsgBox.Show(this,"At least one clinic must be selected.");
return;
}
}
whereProv="";
if(!checkAllProv.Checked) {
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]].ProvNum)+" ";
}
whereProv+=") ";
}
whereClin="";
if(!checkAllClin.Checked) {
for(int i=0;i<listClin.SelectedIndices.Count;i++) {
if(i==0) {
whereClin+=" AND (";
}
else {
whereClin+="OR ";
}
if(listClin.SelectedIndices[i]==0) {
whereClin+="procedurelog.ClinicNum = 0 ";
}
else {
whereClin+="procedurelog.ClinicNum = "+POut.Long(Clinics.List[listClin.SelectedIndices[i]-1].ClinicNum)+" ";
}
}
whereClin+=") ";
}
ReportSimpleGrid report=new ReportSimpleGrid();
if(radioIndividual.Checked){
CreateIndividual(report);
}
else{
CreateGrouped(report);
}
}
