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)
{
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) {
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) {
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)
{
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 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(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)
{
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();
}
