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