unit MyIPDReport;
FUNCTION getsqlsubquerydatax(SQL:string):string;
BEGIN
RESULT:='';
zquery.close;
zquery.SQL.text:=SQL;
zquery.OPEN;
zquery.FIRST;
while NOT zquery.eof do
BEGIN
IF RESULT='' THEN RESULT:=''''+zquery.FIELDS[0].asstring+'''' ELSE
RESULT:=RESULT+','''+zquery.FIELDS[0].asstring+'''';
zquery.NEXT;
END;
zquery.close;
IF RESULT='' THEN RESULT:='''''';
END;
PROCEDURE main;
var d1,d2:tdatetime;
people_distinct_count1 : INTEGER;
people_count1 : INTEGER;
ds1,ds2:string;
pttype_list1 : string;
pttype,pttype_name:string;
hospcode_list:string;
money1:currency;
i:INTEGER;
clinic,cln_cor:string;
visit_hn : string;
BEGIN
//showmessage('à¡ÕèÂǡѺÃÒ§ҹ'+#13+'Custom OPD-NK2');
clinic:='';
clinic:= getpickuplist('select name from clinic where chronic="Y" order by name');
IF clinic='' THEN exit;
pttype_name:=clinic;
clinic:=getsqldata('select clinic from clinic where name="'+clinic+'"');
IF NOT getdaterange() THEN exit;
d1:=date_result1();
d2:=date_result2();
// showmessage(formatdatetime('yyyy-mm-dd',d1)+' - '+
// formatdatetime('yyyy-mm-dd',d2));
ds1:=formatdatetime('yyyy-mm-dd',d1);
ds2:=formatdatetime('yyyy-mm-dd',d2);
visit_hn:=getsqlsubquerydata('select hn from clinicmember where clinic="'+clinic+'" and regdate between "'+ds1+'" and "'+ds2+'" ');
// visit_hn := getsqlsubquerydata('select distinct hn from ovst where vstdate between "'+ds1+'" and "'+ds2+'" and hn in ('+
// visit_hn+') ');
zquery.SQL.text:='delete from tempreport where id = "CUSTOM-CHRONIC2" ';
zquery.execsql;
fcds.close;
fcds.datarequest('select * from tempreport where id = "CUSTOM-CHRONIC2" ');
fcds.OPEN;
fcds2.close;
fcds2.datarequest('select * from clinic_cormobidity where clinic = "'+clinic+'" '+
' order by cormobidity');
fcds2.OPEN;
setstatuslabel('Open result : '+inttostr(fcds2.recordcount)+' Records');
fcds2.FIRST;
i:=0;
i:=1;
fcds.INSERT;
fcds['id']:='CUSTOM-CHRONIC2';
fcds['reportname']:='CUSTOM-CHRONIC2';
// fcds['name']:=fcds2['vn'];
fcds['name1']:=pttype_name;
fcds['num']:=1;
fcds['num2']:=i;
fcds['date3']:=d1;
fcds['date4']:=d2;
fcds['name2']:= pttype_name+' ÃÇÁ';
fcds['mon1']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y<40 and sex="1" and hn in ('+visit_hn+') ');
fcds['mon2']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=40 and age_y<=49 and sex="1" and hn in ('+visit_hn+') ');
fcds['mon3']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=50 and age_y<=59 and sex="1" and hn in ('+visit_hn+') ');
fcds['mon4']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=60 and sex="1" and hn in ('+visit_hn+') ');
fcds['mon5']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and sex="1" and hn in ('+visit_hn+') ');
fcds['mon6']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and hn in ('+visit_hn+') ');
fcds.post;
fcds.INSERT;
fcds['id']:='CUSTOM-CHRONIC2';
fcds['reportname']:='CUSTOM-CHRONIC2';
// fcds['name']:=fcds2['vn'];
fcds['name1']:=pttype_name;
fcds['num']:=2;
fcds['num2']:=i;
fcds['date3']:=d1;
fcds['date4']:=d2;
fcds['name2']:= pttype_name+' ÃÇÁ';
fcds['mon1']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y<40 and sex="2" and hn in ('+visit_hn+') ');
fcds['mon2']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=40 and age_y<=49 and sex="2" and hn in ('+visit_hn+') ');
fcds['mon3']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=50 and age_y<=59 and sex="2" and hn in ('+visit_hn+') ');
fcds['mon4']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=60 and sex="2" and hn in ('+visit_hn+') ');
fcds['mon5']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and sex="2" and hn in ('+visit_hn+') ');
fcds.post;
i:=2;
cln_cor:=getsqlsubquerydata('select distinct hn from clinic_cormobidity_list where clinic="'+clinic+'"');
fcds.INSERT;
fcds['id']:='CUSTOM-CHRONIC2';
fcds['reportname']:='CUSTOM-CHRONIC2';
// fcds['name']:=fcds2['vn'];
fcds['name1']:=pttype_name;
fcds['num']:=1;
fcds['num2']:=i;
fcds['date3']:=d1;
fcds['date4']:=d2;
fcds['name2']:= 'äÁèÁÕÀÒÇÐá·Ã¡«é͹ à¡èÒ';
fcds['mon1']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y<40 and sex="1" and hn not in ('+cln_cor+') and (new_case is NULL or new_case="N" or new_case="") and hn in ('+visit_hn+') ');
fcds['mon2']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=40 and age_y<=49 and sex="1" and hn not in ('+cln_cor+') and (new_case is NULL or new_case="N" or new_case="") and hn in ('+visit_hn+') ');
fcds['mon3']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=50 and age_y<=59 and sex="1" and hn not in ('+cln_cor+') and (new_case is NULL or new_case="N" or new_case="") and hn in ('+visit_hn+') ');
fcds['mon4']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=60 and sex="1" and hn not in ('+cln_cor+') and (new_case is NULL or new_case="N" or new_case="") and hn in ('+visit_hn+') ');
fcds['mon5']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and sex="1" and hn not in ('+cln_cor+') and (new_case is NULL or new_case="N" or new_case="") and hn in ('+visit_hn+') ');
fcds['mon6']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and hn not in ('+cln_cor+') and (new_case is NULL or new_case="N" or new_case="") and hn in ('+visit_hn+') ');
fcds.post;
fcds.INSERT;
fcds['id']:='CUSTOM-CHRONIC2';
fcds['reportname']:='CUSTOM-CHRONIC2';
// fcds['name']:=fcds2['vn'];
fcds['name1']:=pttype_name;
fcds['num']:=2;
fcds['num2']:=i;
fcds['date3']:=d1;
fcds['date4']:=d2;
fcds['name2']:= 'äÁèÁÕÀÒÇÐá·Ã¡«é͹ à¡èÒ';
fcds['mon1']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y<40 and sex="2" and hn not in ('+cln_cor+') and (new_case is NULL or new_case="N" or new_case="") and hn in ('+visit_hn+') ');
fcds['mon2']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=40 and age_y<=49 and sex="2" and hn not in ('+cln_cor+') and (new_case is NULL or new_case="N" or new_case="") and hn in ('+visit_hn+') ');
fcds['mon3']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=50 and age_y<=59 and sex="2" and hn not in ('+cln_cor+') and (new_case is NULL or new_case="N" or new_case="") and hn in ('+visit_hn+') ');
fcds['mon4']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=60 and sex="2" and hn not in ('+cln_cor+') and (new_case is NULL or new_case="N" or new_case="") and hn in ('+visit_hn+') ');
fcds['mon5']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and sex="2" and hn not in ('+cln_cor+') and (new_case is NULL or new_case="N" or new_case="") and hn in ('+visit_hn+') ');
fcds.post;
i:=3;
cln_cor:=getsqlsubquerydata('select distinct hn from clinic_cormobidity_list where clinic="'+clinic+'"');
fcds.INSERT;
fcds['id']:='CUSTOM-CHRONIC2';
fcds['reportname']:='CUSTOM-CHRONIC2';
// fcds['name']:=fcds2['vn'];
fcds['name1']:=pttype_name;
fcds['num']:=1;
fcds['num2']:=i;
fcds['date3']:=d1;
fcds['date4']:=d2;
fcds['name2']:= 'äÁèÁÕÀÒÇÐá·Ã¡«é͹ ãËÁè';
fcds['mon1']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y<40 and sex="1" and hn not in ('+cln_cor+') and new_case="Y" and hn in ('+visit_hn+') ');
fcds['mon2']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=40 and age_y<=49 and sex="1" and hn not in ('+cln_cor+') and new_case="Y" and hn in ('+visit_hn+') ');
fcds['mon3']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=50 and age_y<=59 and sex="1" and hn not in ('+cln_cor+') and new_case="Y" and hn in ('+visit_hn+') ');
fcds['mon4']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=60 and sex="1" and hn not in ('+cln_cor+') and new_case="Y" and hn in ('+visit_hn+') ');
fcds['mon5']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and sex="1" and hn not in ('+cln_cor+') and new_case="Y" and hn in ('+visit_hn+') ');
fcds['mon6']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and hn not in ('+cln_cor+') and new_case="Y" and hn in ('+visit_hn+') ');
fcds.post;
fcds.INSERT;
fcds['id']:='CUSTOM-CHRONIC2';
fcds['reportname']:='CUSTOM-CHRONIC2';
// fcds['name']:=fcds2['vn'];
fcds['name1']:=pttype_name;
fcds['num']:=2;
fcds['num2']:=i;
fcds['date3']:=d1;
fcds['date4']:=d2;
fcds['name2']:= 'äÁèÁÕÀÒÇÐá·Ã¡«é͹ ãËÁè';
fcds['mon1']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y<40 and sex="2" and hn not in ('+cln_cor+') and new_case="Y" and hn in ('+visit_hn+') ');
fcds['mon2']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=40 and age_y<=49 and sex="2" and hn not in ('+cln_cor+') and new_case="Y" and hn in ('+visit_hn+') ');
fcds['mon3']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=50 and age_y<=59 and sex="2" and hn not in ('+cln_cor+') and new_case="Y" and hn in ('+visit_hn+') ');
fcds['mon4']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and age_y>=60 and sex="2" and hn not in ('+cln_cor+') and new_case="Y" and hn in ('+visit_hn+') ');
fcds['mon5']:= getsqldata('select count(hn) as cc '+
' from clinicmember c1 '+
' where clinic="'+clinic+'" and sex="2" and hn not in ('+cln_cor+') and new_case="Y" and hn in ('+visit_hn+') ');
fcds.post;
while NOT fcds2.eof do
BEGIN
i:=i+1;
setprogressbar(i,fcds2.recordcount);
fcds.INSERT;
fcds['id']:='CUSTOM-CHRONIC2';
fcds['reportname']:='CUSTOM-CHRONIC2';
// fcds['name']:=fcds2['vn'];
fcds['name1']:=pttype_name;
fcds['num']:=1;
fcds['num2']:=i;
fcds['date3']:=d1;
fcds['date4']:=d2;
fcds['name2']:= fcds2['name'];
fcds['mon1']:= getsqldata('select count(c3.hn) as cc '+
' from clinic_cormobidity c1 '+
' left outer join clinicmember c2 on c2.clinic=c1.clinic and c2.age_y < 40 and c2.sex="1" '+
' left outer join clinic_cormobidity_list c3 on c3.hn=c2.hn and c3.clinic=c2.clinic and c3.cormobidity=c1.cormobidity and c3.hn in ('+visit_hn+') '+
' where c1.name="'+fcds2['name']+'" ');
fcds['mon2']:= getsqldata('select count(c3.hn) as cc '+
' from clinic_cormobidity c1 '+
' left outer join clinicmember c2 on c2.clinic=c1.clinic and c2.age_y >= 40 and c2.age_y <= 49 and c2.sex="1" '+
' left outer join clinic_cormobidity_list c3 on c3.hn=c2.hn and c3.clinic=c2.clinic and c3.cormobidity=c1.cormobidity and c3.hn in ('+visit_hn+') '+
' where c1.name="'+fcds2['name']+'" ');
fcds['mon3']:= getsqldata('select count(c3.hn) as cc '+
' from clinic_cormobidity c1 '+
' left outer join clinicmember c2 on c2.clinic=c1.clinic and c2.age_y >= 50 and c2.age_y <= 59 and c2.sex="1" '+
' left outer join clinic_cormobidity_list c3 on c3.hn=c2.hn and c3.clinic=c2.clinic and c3.cormobidity=c1.cormobidity and c3.hn in ('+visit_hn+') '+
' where c1.name="'+fcds2['name']+'" ');
fcds['mon4']:= getsqldata('select count(c3.hn) as cc '+
' from clinic_cormobidity c1 '+
' left outer join clinicmember c2 on c2.clinic=c1.clinic and c2.age_y >= 60 and c2.sex="1" '+
' left outer join clinic_cormobidity_list c3 on c3.hn=c2.hn and c3.clinic=c2.clinic and c3.cormobidity=c1.cormobidity and c3.hn in ('+visit_hn+') '+
' where c1.name="'+fcds2['name']+'" ');
fcds['mon5']:= getsqldata('select count(c3.hn) as cc '+
' from clinic_cormobidity c1 '+
' left outer join clinicmember c2 on c2.clinic=c1.clinic and c2.sex="1" '+
' left outer join clinic_cormobidity_list c3 on c3.hn=c2.hn and c3.clinic=c2.clinic and c3.cormobidity=c1.cormobidity and c3.hn in ('+visit_hn+') '+
' where c1.name="'+fcds2['name']+'" ');
fcds['mon6']:= getsqldata('select count(c3.hn) as cc '+
' from clinic_cormobidity c1 '+
' left outer join clinicmember c2 on c2.clinic=c1.clinic '+
' left outer join clinic_cormobidity_list c3 on c3.hn=c2.hn and c3.clinic=c2.clinic and c3.cormobidity=c1.cormobidity and c3.hn in ('+visit_hn+') '+
' where c1.name="'+fcds2['name']+'" ');
//fcds['date1']:=d1;
//fcds['date2']:=d2;
fcds.post;
fcds.INSERT;
fcds['id']:='CUSTOM-CHRONIC2';
fcds['reportname']:='CUSTOM-CHRONIC2';
fcds['num']:=2;
fcds['num2']:=i;
fcds['date3']:=d1;
fcds['date4']:=d2;
fcds['mon1']:= getsqldata('select count(c3.hn) as cc '+
' from clinic_cormobidity c1 '+
' left outer join clinicmember c2 on c2.clinic=c1.clinic and c2.age_y < 40 and c2.sex="2" '+
' left outer join clinic_cormobidity_list c3 on c3.hn=c2.hn and c3.clinic=c2.clinic and c3.cormobidity=c1.cormobidity and c3.hn in ('+visit_hn+') '+
' where c1.name="'+fcds2['name']+'" ');
fcds['mon2']:= getsqldata('select count(c3.hn) as cc '+
' from clinic_cormobidity c1 '+
' left outer join clinicmember c2 on c2.clinic=c1.clinic and c2.age_y >= 40 and c2.age_y <= 49 and c2.sex="2" '+
' left outer join clinic_cormobidity_list c3 on c3.hn=c2.hn and c3.clinic=c2.clinic and c3.cormobidity=c1.cormobidity and c3.hn in ('+visit_hn+') '+
' where c1.name="'+fcds2['name']+'" ');
fcds['mon3']:= getsqldata('select count(c3.hn) as cc '+
' from clinic_cormobidity c1 '+
' left outer join clinicmember c2 on c2.clinic=c1.clinic and c2.age_y >= 50 and c2.age_y <= 59 and c2.sex="2" '+
' left outer join clinic_cormobidity_list c3 on c3.hn=c2.hn and c3.clinic=c2.clinic and c3.cormobidity=c1.cormobidity and c3.hn in ('+visit_hn+') '+
' where c1.name="'+fcds2['name']+'" ');
fcds['mon4']:= getsqldata('select count(c3.hn) as cc '+
' from clinic_cormobidity c1 '+
' left outer join clinicmember c2 on c2.clinic=c1.clinic and c2.age_y >= 60 and c2.sex="2" '+
' left outer join clinic_cormobidity_list c3 on c3.hn=c2.hn and c3.clinic=c2.clinic and c3.cormobidity=c1.cormobidity and c3.hn in ('+visit_hn+') '+
' where c1.name="'+fcds2['name']+'" ');
fcds['mon5']:= getsqldata('select count(c3.hn) as cc '+
' from clinic_cormobidity c1 '+
' left outer join clinicmember c2 on c2.clinic=c1.clinic and c2.sex="2" '+
' left outer join clinic_cormobidity_list c3 on c3.hn=c2.hn and c3.clinic=c2.clinic and c3.cormobidity=c1.cormobidity and c3.hn in ('+visit_hn+') '+
' where c1.name="'+fcds2['name']+'" ');
fcds.post;
fcds2.NEXT;
END;
fcds.datarequest('select * from tempreport where id = "CUSTOM-CHRONIC2" ');
applyupdate_fcds();
END;
END.