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) { if (date2.SelectionStart < date1.SelectionStart) { MsgBox.Show(this, "End date cannot be before start date."); return; } ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = @"SELECT SGDate,ProvName,County,county.CountyCode, site.Description AS schoolName,site.Note AS schoolCode,site.PlaceService,screen.GradeLevel,Age,Birthdate,RaceOld,Gender,Urgency," ; if (!Clinics.IsMedicalPracticeOrClinic(Clinics.ClinicNum)) { report.Query += "HasCaries,EarlyChildCaries,CariesExperience,ExistingSealants,NeedsSealants,MissingAllTeeth,"; } report.Query += @"Comments FROM screen LEFT JOIN screengroup ON screengroup.ScreenGroupNum=screen.ScreenGroupNum LEFT JOIN site ON screengroup.GradeSchool=site.Description LEFT JOIN county ON screengroup.County=county.CountyName WHERE SGDate >= " + POut.Date(date1.SelectionStart) + " " + "AND SGDate <= " + POut.Date(date2.SelectionStart); FormQuery2 = new FormQuery(report); FormQuery2.textTitle.Text = "RawScreeningData" + DateTime.Today.ToString("MMddyyyy"); FormQuery2.SubmitQuery(); FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { Queries.CurReport = new ReportOld(); Queries.CurReport.Query = textSQL.Text; FormQuery2 = new FormQuery(); FormQuery2.IsReport = false; FormQuery2.SubmitQuery(); FormQuery2.textQuery.Text = Queries.CurReport.Query; FormQuery2.ShowDialog(); //DialogResult=DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = textSQL.Text; FormQuery2 = new FormQuery(report); FormQuery2.IsReport = false; FormQuery2.SubmitQuery(); FormQuery2.textQuery.Text = report.Query; FormQuery2.ShowDialog(); //DialogResult=DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { string[] fieldsSelected = new string[listSelect.SelectedItems.Count + listSelect2.SelectedItems.Count]; if (listSelect.SelectedItems.Count == 0 && listSelect2.SelectedItems.Count == 0) { MsgBox.Show(this, "At least one field must be selected."); return; } listSelect.SelectedItems.CopyTo(fieldsSelected, 0); listSelect2.SelectedItems.CopyTo(fieldsSelected, listSelect.SelectedItems.Count); string command = "SELECT "; for (int i = 0; i < fieldsSelected.Length; i++) { if (i > 0) { command += ","; } if (fieldsSelected[i] == "AptDateTime") { command += "appointment." + fieldsSelected[i]; } else { command += "patient." + fieldsSelected[i]; } } command += " FROM patient,appointment " + "WHERE patient.PatNum=appointment.PatNum AND("; for (int i = 0; i < AptNums.Length; i++) { if (i > 0) { command += " OR"; } command += " appointment.AptNum='" + AptNums[i] + "'"; } command += ")"; Queries.CurReport = new ReportOld(); Queries.CurReport.Query = command; FormQuery FormQ = new FormQuery(); FormQ.IsReport = false; FormQ.SubmitQuery(); FormQ.textQuery.Text = Queries.CurReport.Query; FormQ.ShowDialog(); DialogResult = DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { Queries.CurReport = new ReportOld(); Queries.CurReport.Query = @"SELECT ScreenDate,ProvName,County,county.CountyCode, GradeSchool,school.SchoolCode,PlaceService,GradeLevel,Age,Birthdate,Race,Gender,Urgency, HasCaries,EarlyChildCaries,CariesExperience,ExistingSealants,NeedsSealants,MissingAllTeeth, Comments FROM screen LEFT JOIN school ON screen.GradeSchool=school.SchoolName LEFT JOIN county ON screen.County=county.CountyName WHERE ScreenDate >= " + POut.PDate(date1.SelectionStart) + " " + "AND ScreenDate <= " + POut.PDate(date2.SelectionStart); FormQuery2 = new FormQuery(); FormQuery2.textTitle.Text = "RawScreeningData" + DateTime.Today.ToString("MMddyyyy"); //FormQuery2.IsReport=true; //FormQuery2.SubmitReportQuery(); FormQuery2.SubmitQuery(); FormQuery2.ShowDialog(); DialogResult = DialogResult.OK; }
private void butOK_Click(object sender, System.EventArgs e) { FormQuery FormQuery2; string phrase = textPharse.Text.Replace("\t", "").Replace("\n", ""); StringBuilder sbSQL = new StringBuilder(); sbSQL.AppendFormat("SELECT LName,FName,Preferred,PatStatus,Gender,Birthdate,Address,Address2,City,State,zip,HmPhone,Wkphone,", phrase); sbSQL.AppendFormat("WirelessPhone,Guarantor,PriProv,AddrNote,FamFinUrgNote,MedUrgNote,ApptModNote,DateFirstVisit", phrase); sbSQL.AppendFormat(" FROM patient WHERE AddrNote LIKE '%{0}%' ", phrase); sbSQL.AppendFormat("or FamFinUrgNote LIKE '%{0}%' ", phrase); sbSQL.AppendFormat("or MedUrgNote LIKE '%{0}%' ", phrase); sbSQL.AppendFormat("or ApptModNote LIKE '%{0}%' ", phrase); sbSQL.AppendFormat("or EmploymentNote LIKE '%{0}%' ", phrase); ReportSimpleGrid report = new ReportSimpleGrid(); report.Query = sbSQL.ToString(); FormQuery2 = new FormQuery(report); FormQuery2.IsReport = false; FormQuery2.SubmitQuery(); FormQuery2.textQuery.Text = report.Query; FormQuery2.ShowDialog(); }
private void butOK_Click(object sender, System.EventArgs e) { string[] 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 (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 (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; }