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,sex_list:string;
pcode_show:string;
money1,inc_limit:currency;
i:integer;
s1,s2:integer;
diag1,diag2,drug,moo: string;
dd1 : BLOB;
begin
//showmessage('???????????????'+#13+'Custom OPD-NK2');
//pttype:= getpickuplist('select name from pttype where isuse="Y" order by name');
//showmessage('use name = '+pttype);
//if pttype='' then exit;
//pttype_name:=pttype;
//pttype:=getsqldata('select pcode from pttype where name="'+pttype+'"');
//if pttype='UA' then pttype:='UC';
//if pttype='UB' then pttype:='UC';
//if pttype='UC' then pttype_name:=getsqlsubquerydata('select name from pttype where pcode in ("UC","UA","UB")') else
// pttype_name := getsqlsubquerydata('select name from pttype where pcode="'+pttype+'"');
//showmessage('dd1 = '+dd1);
//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);
//hospcode_list:=getsqlsubquerydata('select distinct hospmain from vn_stat where vstdate between "'+ds1+'" and "'+ds2+'" '); // pcode = "'+pttype+'" and
//if dd1='' then
hospcode_list := getpickuplist('select concat(depcode,":",department) from kskdepartment') ;
//else
//begin
//person_id := getsqldata('select village_id from person where person_id="'+copy(dd1,1,5)+'" ');
//hospcode_list := getpickuplist('select concat(village_id,":",village_moo," ",village_name) as name from village where village_id="'++'" ');
//end;
if preset_value(1)='' then
begin
if not inputquery('ãÊèÃËÑÊ¡ÒÃÇÔ¹Ô¨©ÑÂàÃÔèÁµé¹','diag',diag1) then exit;
end else
begin
diag1:='');
end;
if preset_value(2)='' then
begin
if not inputquery('ãÊèÃËÑÊ¡ÒÃÇÔ¹Ô¨©ÑÂÊÔé¹ÊØ´','diag',diag2) then exit;
end else
begin
diag2:='');
end;
if not getdaterange() then exit;
d1 := date_result1();
d2 := date_result2();
// fonlineid := report_value(2);
ds1 := formatdatetime('yyyy-mm-dd', d1);
ds2 := formatdatetime('yyyy-mm-dd', d2);
//showmessage('????1 = '+age1+' ????2 ?'+age2);
// sex_list := getpickuplist('select concat(code,":",name) as name from sex ');
// in ('+ hospcode_list+') ');
zquery.sql.text:='delete from tempreport where id = "CUSTOM-opd-arr1" ';
zquery.execsql;
fcds.close;
fcds.datarequest('select * from tempreport where id = "CUSTOM-opd-arr1" ');
fcds.open;
fcds2.close;
//showmessage('???????????? = '+copy(hospcode_list,1,2));
//if hospcode_list='' then
fcds2.datarequest('select v.hn,concat(p.pname,p.fname," ",p.lname)as name,count(distinct v.vn)as t1,max(v.vstdate)as vs,v.pdx,o.main_dep from vn_stat v '+
' left outer join ovst o on o.vn=v.vn '+
' left outer join patient p on p.hn=v.hn '+
' where o.an is null and o.main_dep="'+copy(hospcode_list,1,3)+'" and v.pdx between"'+diag1+'"and"'+diag2+'"and v.vstdate between"'+ds1+'"and"'+ds2+'" '+
' group by v.hn order by t1');
// select r.hn,concat(p.pname,p.fname," ",p.lname) as ptname, r.*, o.vstdate,o.vsttime,t.name as pttype_name ,o.pttype,ptda.pname '+
// ' from rcpt_arrear r left outer join ovst o on o.vn=r.vn left outer join patient p on p.hn=r.hn left outer join pttype t on t.pttype = o.pttype '+
// ' left outer join pttype_dsmain ptds on ptds.pttype=t.pttype '+
// ' left outer join pttype_dansai ptda on ptda.fee_code=ptds.fee_code where r.arrear_date between "'+ds1+'" and "'+ds2+'" '+
// ' and r.paid in ("Y","N") group by r.hn order by pttype_name ');
//else
//fcds2.datarequest('select * from pttype inner join an_stat on pttype.pttype=an_stat.pttype inner join pttype_dansai on pttype.fee_code=pttype_dansai.fee_code where an_stat.dchdate between "'+ds1+'" and "'+ds2+'" '+ // pcode = "'+pttype+'" and
//' and hospmain="'+copy(hospcode_list,1,5)+'" order by pttype '); // vn
fcds2.open;
setstatuslabel('Open result : '+inttostr(fcds2.recordcount)+' Records');
fcds2.first;
i:=0;
while not fcds2.eof do
begin
i:=i+1;
setprogressbar(i,fcds2.recordcount);
fcds.insert;
fcds['id']:='CUSTOM-opd-arr1';
fcds['reportname']:='CUSTOM-opd-arr11';
fcds['name']:=fcds2['name'];
fcds['num1']:=fcds2['t1'];
fcds['name2']:=fcds2['hn'];
fcds['name3']:=fcds2['vs'];
fcds['name4']:=ds1;
fcds['name5']:=ds2;
fcds['name6']:=fcds2['main_dep'];
fcds['name7']:=diag1;
fcds['name8']:=diag2;
//if fcds2['pttype_name']='' then
//fcds['name8']:=''
//else
//fcds['name8']:=fcds2['pttype_name'];
//fcds['name1']:=getsqldata('select concat(pttype," ",name) as name from pttype where pttype="'+
//fcds2['pttype']+'"');
//fcds['name2']:=getsqldata('select concat(pname,fname," ",lname) as name from patient where hn="'+
// fcds2['hn']+'"');
//fcds['name3']:=fcds2['hn'];
//fcds['name4']:=fcds2['pttypeno'];
//fcds['date3']:=fcds2['pttype_expire'];
// fcds['name5']:=fcds2['an'];
//fcds['name5']:=getsqldata('select concat(name,", ",hosptype) as name from hospcode where hospcode="'+
// fcds2['hospmain']+'"');
//fcds['name6']:=fcds2['cid'];
//if fcds2['sex']='1' then
// fcds['name7']:='?' else
// fcds['name7']:='?';
//fcds['num1']:=fcds2['age_y'];
//fcds['name8']:=fcds2['pdx']+' '+fcds2['dx0'];
// fcds['name9']:=fcds2['op0']+' '+fcds2['op1'];
// fcds['name']:=getsqldata('select i_refer_number from ovst where vn="'+fcds2['vn']+'"');
{
fcds['mon11']:=getsqldata('select sum(sum_price) as cc from opitemrece where an="'+fcds2['an']+'" and income="09" group by income');
//fcds2['inc06'];
//fcds2['inc00']+fcds2['inc18'];
fcds['mon18']:=getsqldata('select sum(sum_price) as cc from opitemrece where an="'+fcds2['an']+'"')+fcds2['inc17'];
//fcds2['income'];
s1:=getsqldata('select sum(rcptamt) as cc from rcpt_print_detail where vn="'+fcds2['an']+'" and paidst="04"');
s2:=getsqldata('select bill_amount from rcpt_print where vn="'+fcds2['an']+'" ');
fcds['mon20']:=s2;
fcds['mon19']:=s1;
}
//fcds['mon19']:=inc_limit;
//fcds['mon20']:=fcds2['rcpt_money'];
//try
// fcds['date1']:=ds1;
// except
// end;
//try
// fcds['date2']:=ds2;
// except
// end;
//fcds['date4']:=fcds2['vstdate'];
fcds2.next;
end;
fcds.post;
fcds.datarequest('select * from tempreport where id = "CUSTOM-opd-arr1" ');
applyupdate_fcds();
end;
end.
หัวข้อ: ช่วยดูรายงานให้หน่อยครับ (อ่าน 8280 ครั้ง)