BMS-HOSxP Community
HOSxP => Report Exchange => ข้อความที่เริ่มโดย: yokyai ที่ มีนาคม 05, 2010, 10:53:06 AM
-
เป็นรายงานส่งต่อ 21 กลุ่มโรค ของคุณ nahos ให้ไว้ แต่อยากทราบความหมายว่าทั้งหมดนี้มาจากอะไร
เพื่อที่จะได้ทำเองได้บ้างคับ พอมีความรู้เดิมบ้างนิดหน่อย แต่ยังไม่ถึงขั้น advance
และถ้าทำเองจะมีผลต่อข้อมูลที่เราดึงมาใช้หรือเปล่า เช่น ทำให้ข้อมูลในระบบเสียหายโดยรู้เท่าไม่ถึงการณ์หรือไม่ ต้องระวังตรงส่วนไหนบ้าง หรือคำสั่งไหนบ้างหน่ะคับ
ปล.ยกมาทั้งรายงานเลยคับ ยังไงก็ขอขอบคุณล่วงหน้าไว้ก่อนนะคับ หวังว่าคงไม่รบกวนเกินไป :)
unit MyIPDReport;
var dent_in,dent_out : string;
opd_in,opd_out:string;
er_in,er_out:string;
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;
function GetICDRangeCount_UC30_InRegion(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
uc30_pcode+') and v.pttype_in_region="Y" and v.vn in (select vn from ovst where o_refer_number is not null) ');
except
on e:exception do
begin
showmessage(e.message);
end;
end;
end;
function GetICDRangeCount_UC30_OutRegion(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
uc30_pcode+') and v.pttype_in_region="N" and v.vn in (select vn from ovst where o_refer_number is not null) ');
except
on e:exception do
begin
showmessage(e.message);
end;
end;
end;
function GetICDRangeCount_UC_InRegion(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
uc_pcode+') and v.pttype_in_region="Y" and v.vn in (select vn from ovst where o_refer_number is not null) ');
except
on e:exception do
begin
showmessage(e.message);
end;
end;
end;
function GetICDRangeCount_UC_OutRegion(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
uc_pcode+') and v.pttype_in_region="N" and v.vn in (select vn from ovst where o_refer_number is not null) ' );
except
on e:exception do
begin
showmessage(e.message);
end;
end;
end;
function GetICDRangeCount_Other(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
other_pcode+') and v.vn in (select vn from ovst where o_refer_number is not null) ');
except
on e:exception do
begin
showmessage(e.message);
end;
end;
end;
function GetICDRangeCount_Foreign(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
foreign_pcode+') and v.vn in (select vn from ovst where o_refer_number is not null) ');
except
on e:exception do
begin
showmessage(e.message);
end;
end;
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;
fonlineid:string;
begin
if not getdaterange() then exit;
d1:=date_result1();
d2:=date_result2();
setstatuslabel('Processing...');
if getsqldata('select count(*) as cc from rpt_504_code')=0 then
fcds.datarequest('EXEC INSERT INTO rpt_504_code (id, code1, code2) VALUES '+
' (1,''A00'',''B9999''), '+
' (2,''C00'',''D4899''), '+
' (3,''D50'',''D8999''), '+
' (4,''E00'',''E9099''), '+
' (5,''F00'',''F9999''), '+
' (6,''G00'',''G9999''), '+
' (7,''H00'',''H5999''), '+
' (8,''H60'',''H9599''), '+
' (9,''I00'',''I9999''), '+
' (10,''J00'',''J9999''), '+
' (11,''K00'',''K939''), '+
' (12,''L00'',''L9999''), '+
' (13,''M00'',''M9999''), '+
' (14,''N00'',''N9999''), '+
' (15,''O00'',''O9999''), '+
' (16,''P00'',''P9699''), '+
' (17,''Q00'',''Q9999''), '+
' (18,''R00'',''R9999''), '+
' (19,''X40'',''X4999''), '+
' (20,''V01'',''V9999''), '+
' (21,''W00'',''Y9999'') ');
if getsqldata('select count(*) as cc from rpt_504_name')=0 then
fcds.datarequest('EXEC INSERT INTO rpt_504_name (id, name1, name2) VALUES '+
' (1,''âäµÔ´àª×éÍáÅлÃÊÔµ'',''Certain infectious and parasitic diseases''), '+
' (2,''à¹×éͧ͡ (ÃÇÁÁÐàÃç§)'',''Neoplasms''), '+
' (3,''âäàÅ×Í´áÅÐÍÇÑÂÇÐÊÃéÒ§àÅ×Í´'',''Diseases of the blood and blood forming organs and certain disorders involving the immune mechanism''), '+
' (4,''âäà¡ÕèÂǡѺµèÍÁäÃé·èÍ âÀª¹Ò¡Òà áÅÐàÁµÐºÍÅÔÊÑÁ'',''Endocrine, nutritional and metabolic diseases''), '+
' (5,''ÀÒÇÐá»Ã»Ãǹ·Ò§¨ÔµáÅоĵԡÃÃÁ'',''Mental and behavioural disorders''), '+
' (6,''âäÃкº»ÃÐÊÒ·'',''Diseases of the nervous system''), '+
' (7,''âäµÒÃÇÁÊèǹ»ÃСͺ¢Í§µÒ'',''Diseases of the eye and adnexa''), '+
' (8,''âäËÙáÅлØèÁ¡¡ËÙ'',''Diseases of the ear and mastoid process''), '+
' (9,''âäÃкºäËÅàÇÕ¹àÅ×Í´'',''Diseases of the circulatory system''), '+
' (10,''âäÃкºËÒÂã¨'',''Diseases of the respiratory system''), '+
' (11,''âäÃкºÂèÍÂÍÒËÒà ÃÇÁâä㹪èͧ»Ò¡'',''Diseases of the digestive system''), '+
' (12,''âä¼ÔÇ˹ѧáÅÐà¹×éÍàÂ×èÍãµé¼ÔÇ˹ѧ'',''Diseases of the skin and subcutaneous tissue''), '+
' (13,''âäÃкº¡ÅéÒÁà¹×éÍ ÃÇÁâ¤Ã§ÃèÒ§ áÅÐà¹×éÍÂÖ´àÊÃÔÁ'',''Diseases of the musculoskeletal system and connective tissue''), '+
' (14,''âäÃкºÊ׺¾Ñ¹¸ØìÃèÇÁ»ÑÊÊÒÇÐ'',''Diseases of the genitourinary system''), '+
' (15,''ÀÒÇÐá·Ã¡ã¹¡ÒõÑ駤ÃÃÀì¡ÒäÅÍ´'',''Complication of pregnancy, childbirth and the puerperium''), '+
' (16,''ÀÒÇмԴ»¡µÔ¢Í§·Òá·Õèà¡Ô´¢Öé¹ã¹ÃÐÂлÃÔ¡Ó˹´'',''Certain conditions originating in the perinatal period''), '+
' (17,''ÃÙ»ÃèÒ§¼Ô´»¡µÔáÅСÓà¹Ô´'',''Congenital malformations, deformations and chromosomal abnormalities''), '+
' (18,''ÍÒ¡ÒÃ, ÍÒ¡ÒÃáÊ´§áÅÐÊÔ觼Դ»¡µÔ·Õ辺ä´é¨Ò¡¡ÒõÃǨ·Ò§¤ÅÕ¹Ô¡ áÅзҧËéͧ»¯ÔºÑµÔ¡ÒÃ'',''Symptoms, signs and abnormality clinical and laboratory findings, not elsewhere classified''), '+
' (19,''¡ÒÃà»ç¹¾ÔÉáÅмŷÕèµÒÁÁÒ'',''Poisoning, toxic effect, and their sequelae''), '+
' (20,''ÍغѵÔà˵بҡ¡Òâ¹Êè§ áÅмŷÕèµÒÁÁÒ'',''Transport accidents and their sequelae''), '+
' (21,''ÊÒà˵بҡÀÒ¹͡Í×è¹æ ·Õè·ÓãËé»èÇÂËÃ×͵ÒÂ'',''Other external causes of morbidity and mortality'') ');
uc30_pcode :='"UC","UA"';
uc_pcode := '"AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","UB"';
foreign_pcode := '"AL"';
other_pcode := getsqlsubquerydata('select pcode from pttype where pcode not in ('+uc30_pcode+','+uc_pcode+','+foreign_pcode+') ');
// showmessage(formatdatetime('yyyy-mm-dd',d1)+' - '+
// formatdatetime('yyyy-mm-dd',d2));
ds1:=formatdatetime('yyyy-mm-dd',d1);
ds2:=formatdatetime('yyyy-mm-dd',d2);
fonlineid:=report_value(1);
// fonlineid:=get_onlineid ;
//showmessage(fonlineid);
zquery.sql.text:='delete from tempreport where id = '''+fonlineid+''' ';
zquery.execsql;
fcds.close;
fcds.datarequest('select * from tempreport where id = '''+fonlineid+''' ');
fcds.open;
fcds2.close;
fcds2.datarequest('select r.* '+
' from rpt_504_name r '+
' ');
fcds2.open;
i:=0;
fcds2.first;
while not fcds2.eof do
begin
setstatuslabel('Processing... '+fcds2['name1']);
i:=i+1;
setprogressbar(i,fcds2.recordcount);
fcds.insert;
fcds['id']:=fonlineid;
fcds['reportname']:='CUSTOM-RG504';
fcds['name1']:=fcds2['name1'];
fcds['name2']:=fcds2['name2'];
fcds['num1']:=fcds2['id'];
fcds3.close;
fcds.datarequest('select * from rpt_504_code where id = '+fcds2.fieldbyname('id').asstring);
fcds3.open;
fcds['mon1']:=0;
fcds['mon2']:=0;
fcds['mon3']:=0;
fcds['mon4']:=0;
fcds['mon5']:=0;
fcds['mon6']:=0;
fcds['mon7']:=0;
while not fcds3.eof do
begin
fcds['mon1']:=fcds['mon1']+GetICDRangeCount_UC30_InRegion(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon2']:=fcds['mon2']+GetICDRangeCount_UC30_OutRegion(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon3']:=fcds['mon3']+GetICDRangeCount_UC_InRegion(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon4']:=fcds['mon4']+GetICDRangeCount_UC_OutRegion(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon5']:=fcds['mon5']+GetICDRangeCount_Other(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon6']:=fcds['mon6']+GetICDRangeCount_Foreign(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon7']:=fcds['mon1']+fcds['mon2']+fcds['mon3']+fcds['mon4']+fcds['mon5']+fcds['mon6'] ;
fcds3.next;
end;
fcds['mon7']:=fcds['mon1']+fcds['mon2']+fcds['mon3']+fcds['mon4']+fcds['mon5']+fcds['mon6'] ;
fcds['date1']:=d1;
fcds['date2']:=d2;
fcds.post;
fcds.datarequest('select * from tempreport where id = '''+fonlineid+''' ');
applyupdate_fcds();
fcds2.next;
end;
setstatuslabel('Done.');
end;
end.
-
มันคือ pascal script ครับ รู้จักแค่นั้น :D ;D :D ;)
ดึงข้อมูล ยัดลงตาราง tempreport แล้ว ดึงข้อมูลจาก tempreport มาแสดงอีกทีครับ
-
เอาเท่าที่พอทราบนะครับ
ยังไงก็ลองไปแกะ code ดูครับ
unit MyIPDReport;
// ตัวแปรแบบ Gobal มองเห็นทุกฟังก์ชัน
var dent_in,dent_out : string;
opd_in,opd_out:string;
er_in,er_out:string;
// จัดเรียงข้อมูลเพื่อนำไปใส่แทน Subquery เพื่อลดการทำ subquery ซ้ำ ๆ กันหลายรอบ
// รับค่า SQL
// ส่งค่ากลับเป็น String
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;
// ฟังก์ชันสำหรับนับผู้ป่วย UC30 อยู่ในเขต (ดูจากคำสั่ง SQL)
// รับค่าช่วงของ icd10 และช่วงของวันที่รับบริการ
// ส่งค่ากลับเป็นตัวเลข (ค่าที่นับได้)
// กรณีที่มี Error จะมีหน้าต่างแสดงบอก
// ฟังก์ชันอื่น ๆ ทำงานคล้ายๆ กันแต่ต่างกันที่เงื่อนไขในการนับ
function GetICDRangeCount_UC30_InRegion(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
uc30_pcode+') and v.pttype_in_region="Y" and v.vn in (select vn from ovst where o_refer_number is not null) ');
except
on e:exception do
begin
showmessage(e.message);
end;
end;
end;
function GetICDRangeCount_UC30_OutRegion(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
uc30_pcode+') and v.pttype_in_region="N" and v.vn in (select vn from ovst where o_refer_number is not null) ');
except
on e:exception do
begin
showmessage(e.message);
end;
end;
end;
function GetICDRangeCount_UC_InRegion(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
uc_pcode+') and v.pttype_in_region="Y" and v.vn in (select vn from ovst where o_refer_number is not null) ');
except
on e:exception do
begin
showmessage(e.message);
end;
end;
end;
function GetICDRangeCount_UC_OutRegion(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
uc_pcode+') and v.pttype_in_region="N" and v.vn in (select vn from ovst where o_refer_number is not null) ' );
except
on e:exception do
begin
showmessage(e.message);
end;
end;
end;
function GetICDRangeCount_Other(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
other_pcode+') and v.vn in (select vn from ovst where o_refer_number is not null) ');
except
on e:exception do
begin
showmessage(e.message);
end;
end;
end;
function GetICDRangeCount_Foreign(icd1,icd2:string;bd1,bd2:TDateTime):integer;
begin
result:=0;
try
result:=getsqldata('select count(v.hn) as cc from vn_stat v,ovstdiag o where v.vstdate between "'+formatdatetime('yyyy-mm-dd',bd1)+'" '+
' and "'+formatdatetime('yyyy-mm-dd',bd2)+'" and v.vn=o.vn and o.icd10 between "'+icd1+'" and "'+icd2+'" and v.pcode in ('+
foreign_pcode+') and v.vn in (select vn from ovst where o_refer_number is not null) ');
except
on e:exception do
begin
showmessage(e.message);
end;
end;
end;
// Procedure หลักของ Script จะเริ่มทำงานที่ Procedure นี้
procedure main;
// Local Variable ตัวแปรสามารถมองเห็นได้เฉพาะ 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;
fonlineid:string;
begin
// เลือกช่วงวันที่ โดยแสดงเป็นหน้าต่างเลือกช่วงวันที่
if not getdaterange() then exit;
d1:=date_result1();
d2:=date_result2();
// กำหนด Label เป็น Processing
setstatuslabel('Processing...');
// ถ้าในตาราง rpt_504_code ไม่มีข้อมูลจะทำการเพิ่มข้อมูลเข้าไป
if getsqldata('select count(*) as cc from rpt_504_code')=0 then
fcds.datarequest('EXEC INSERT INTO rpt_504_code (id, code1, code2) VALUES '+
' (1,''A00'',''B9999''), '+
' (2,''C00'',''D4899''), '+
' (3,''D50'',''D8999''), '+
' (4,''E00'',''E9099''), '+
' (5,''F00'',''F9999''), '+
' (6,''G00'',''G9999''), '+
' (7,''H00'',''H5999''), '+
' (8,''H60'',''H9599''), '+
' (9,''I00'',''I9999''), '+
' (10,''J00'',''J9999''), '+
' (11,''K00'',''K939''), '+
' (12,''L00'',''L9999''), '+
' (13,''M00'',''M9999''), '+
' (14,''N00'',''N9999''), '+
' (15,''O00'',''O9999''), '+
' (16,''P00'',''P9699''), '+
' (17,''Q00'',''Q9999''), '+
' (18,''R00'',''R9999''), '+
' (19,''X40'',''X4999''), '+
' (20,''V01'',''V9999''), '+
' (21,''W00'',''Y9999'') ');
// ถ้าในตาราง rpt_504_name ไม่มีข้อมูลจะทำการเพิ่มข้อมูลเข้าไป
if getsqldata('select count(*) as cc from rpt_504_name')=0 then
fcds.datarequest('EXEC INSERT INTO rpt_504_name (id, name1, name2) VALUES '+
' (1,''âäµÔ´àª×éÍáÅлÃÊÔµ'',''Certain infectious and parasitic diseases''), '+
' (2,''à¹×éͧ͡ (ÃÇÁÁÐàÃç§)'',''Neoplasms''), '+
' (3,''âäàÅ×Í´áÅÐÍÇÑÂÇÐÊÃéÒ§àÅ×Í´'',''Diseases of the blood and blood forming organs and certain disorders involving the immune mechanism''), '+
' (4,''âäà¡ÕèÂǡѺµèÍÁäÃé·èÍ âÀª¹Ò¡Òà áÅÐàÁµÐºÍÅÔÊÑÁ'',''Endocrine, nutritional and metabolic diseases''), '+
' (5,''ÀÒÇÐá»Ã»Ãǹ·Ò§¨ÔµáÅоĵԡÃÃÁ'',''Mental and behavioural disorders''), '+
' (6,''âäÃкº»ÃÐÊÒ·'',''Diseases of the nervous system''), '+
' (7,''âäµÒÃÇÁÊèǹ»ÃСͺ¢Í§µÒ'',''Diseases of the eye and adnexa''), '+
' (8,''âäËÙáÅлØèÁ¡¡ËÙ'',''Diseases of the ear and mastoid process''), '+
' (9,''âäÃкºäËÅàÇÕ¹àÅ×Í´'',''Diseases of the circulatory system''), '+
' (10,''âäÃкºËÒÂã¨'',''Diseases of the respiratory system''), '+
' (11,''âäÃкºÂèÍÂÍÒËÒà ÃÇÁâä㹪èͧ»Ò¡'',''Diseases of the digestive system''), '+
' (12,''âä¼ÔÇ˹ѧáÅÐà¹×éÍàÂ×èÍãµé¼ÔÇ˹ѧ'',''Diseases of the skin and subcutaneous tissue''), '+
' (13,''âäÃкº¡ÅéÒÁà¹×éÍ ÃÇÁâ¤Ã§ÃèÒ§ áÅÐà¹×éÍÂÖ´àÊÃÔÁ'',''Diseases of the musculoskeletal system and connective tissue''), '+
' (14,''âäÃкºÊ׺¾Ñ¹¸ØìÃèÇÁ»ÑÊÊÒÇÐ'',''Diseases of the genitourinary system''), '+
' (15,''ÀÒÇÐá·Ã¡ã¹¡ÒõÑ駤ÃÃÀì¡ÒäÅÍ´'',''Complication of pregnancy, childbirth and the puerperium''), '+
' (16,''ÀÒÇмԴ»¡µÔ¢Í§·Òá·Õèà¡Ô´¢Öé¹ã¹ÃÐÂлÃÔ¡Ó˹´'',''Certain conditions originating in the perinatal period''), '+
' (17,''ÃÙ»ÃèÒ§¼Ô´»¡µÔáÅСÓà¹Ô´'',''Congenital malformations, deformations and chromosomal abnormalities''), '+
' (18,''ÍÒ¡ÒÃ, ÍÒ¡ÒÃáÊ´§áÅÐÊÔ觼Դ»¡µÔ·Õ辺ä´é¨Ò¡¡ÒõÃǨ·Ò§¤ÅÕ¹Ô¡ áÅзҧËéͧ»¯ÔºÑµÔ¡ÒÃ'',''Symptoms, signs and abnormality clinical and laboratory findings, not elsewhere classified''), '+
' (19,''¡ÒÃà»ç¹¾ÔÉáÅмŷÕèµÒÁÁÒ'',''Poisoning, toxic effect, and their sequelae''), '+
' (20,''ÍغѵÔà˵بҡ¡Òâ¹Êè§ áÅмŷÕèµÒÁÁÒ'',''Transport accidents and their sequelae''), '+
' (21,''ÊÒà˵بҡÀÒ¹͡Í×è¹æ ·Õè·ÓãËé»èÇÂËÃ×͵ÒÂ'',''Other external causes of morbidity and mortality'') ');
// กำหนด code ให้กับตัวแปร(สามารถนำไปใส่ใน subquery ได้เลย)
uc30_pcode :='"UC","UA"';
uc_pcode := '"AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","UB"';
foreign_pcode := '"AL"';
other_pcode := getsqlsubquerydata('select pcode from pttype where pcode not in ('+uc30_pcode+','+uc_pcode+','+foreign_pcode+') ');
// showmessage(formatdatetime('yyyy-mm-dd',d1)+' - '+
// formatdatetime('yyyy-mm-dd',d2));
// แปลงวันที่ให้อยู่ในรูปแบบของ String เพื่อไปเอาไปใส่ในคำสั่ง SQL
ds1:=formatdatetime('yyyy-mm-dd',d1);
ds2:=formatdatetime('yyyy-mm-dd',d2);
// กำหนด online id
fonlineid:=report_value(1);
// fonlineid:=get_onlineid ;
//showmessage(fonlineid);
// เคลียค่าในตาราง tempreport ที่มี online id ตามที่เรากำหนด
zquery.sql.text:='delete from tempreport where id = '''+fonlineid+''' ';
zquery.execsql;
// Initiall fcds สำหรับการเพิ่มข้อมูลในตาราง tempreport
fcds คือตัวหลักที่จะนำข้อมูลเข้ามาใส่
โดยข้อมุลที่เอามาใส่ก็จะมาจากที่เรา query จาก fcds2 fcds3 และส่ง paremeter เข้าไปยังฟังก์ชันที่ใช้นับ
fcds.close;
fcds.datarequest('select * from tempreport where id = '''+fonlineid+''' ');
fcds.open;
// กำหนดให้ fcds2 มีข้อมูลตามคำสั่ง sql
fcds2.close;
fcds2.datarequest('select r.* '+
' from rpt_504_name r '+
' ');
fcds2.open;
i:=0;
// ทำการวนลูป (คือวนจนครบโรคตามตาราง rpt_504_name)
fcds2.first;
while not fcds2.eof do
begin
// ให้ status แสดงคำว่า Processing... ชื่อโรค
setstatuslabel('Processing... '+fcds2['name1']);
i:=i+1;
setprogressbar(i,fcds2.recordcount);
fcds.insert;
fcds['id']:=fonlineid;
fcds['reportname']:='CUSTOM-RG504';
fcds['name1']:=fcds2['name1'];
fcds['name2']:=fcds2['name2'];
fcds['num1']:=fcds2['id'];
fcds3.close;
fcds.datarequest('select * from rpt_504_code where id = '+fcds2.fieldbyname('id').asstring);
fcds3.open;
fcds['mon1']:=0;
fcds['mon2']:=0;
fcds['mon3']:=0;
fcds['mon4']:=0;
fcds['mon5']:=0;
fcds['mon6']:=0;
fcds['mon7']:=0;
while not fcds3.eof do
begin
fcds['mon1']:=fcds['mon1']+GetICDRangeCount_UC30_InRegion(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon2']:=fcds['mon2']+GetICDRangeCount_UC30_OutRegion(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon3']:=fcds['mon3']+GetICDRangeCount_UC_InRegion(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon4']:=fcds['mon4']+GetICDRangeCount_UC_OutRegion(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon5']:=fcds['mon5']+GetICDRangeCount_Other(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon6']:=fcds['mon6']+GetICDRangeCount_Foreign(fcds3['code1'],fcds3['code2'],d1,d2);
fcds['mon7']:=fcds['mon1']+fcds['mon2']+fcds['mon3']+fcds['mon4']+fcds['mon5']+fcds['mon6'] ;
fcds3.next;
end;
fcds['mon7']:=fcds['mon1']+fcds['mon2']+fcds['mon3']+fcds['mon4']+fcds['mon5']+fcds['mon6'] ;
fcds['date1']:=d1;
fcds['date2']:=d2;
fcds.post;
fcds.datarequest('select * from tempreport where id = '''+fonlineid+''' ');
applyupdate_fcds();
fcds2.next;
end;
setstatuslabel('Done.');
end;
end.
-
ขอบคุณครับ สุดยอดไปเลย :D ;D
-
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:string;
status: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+'"');
// clinic_member_status
status:='';
status:= getpickuplist('select clinic_member_status_name from clinic_member_status order by clinic_member_status_id ');
if status='' then exit;
status:=getsqldata('select clinic_member_status_id from clinic_member_status where clinic_member_status_name ="'+status+'" limit 1');
//
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);
zquery.sql.text:='delete from tempreport where id = "CUSTOM-CHRONIC1" ';
zquery.execsql;
fcds.close;
fcds.datarequest('select * from tempreport where id = "CUSTOM-CHRONIC1" ');
fcds.open;
fcds2.close;
fcds2.datarequest('select * from clinicmember where clinic = "'+clinic+'" and clinic_member_status_id = "'+status+'" '+
'and regdate between "'+ds1+'" and "'+ds2+'" order by regdate,hn ');
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-CHRONIC1';
fcds['reportname']:='CUSTOM-CHRONIC1';
// fcds['name']:=fcds2['vn'];
fcds['name1']:=pttype_name;
fcds['name10']:=status;
fcds['num']:=1;
fcds['num2']:=i;
fcds['name2']:=getsqldata('select concat(pname,fname," ",lname) as name from patient where hn="'+
fcds2['hn']+'"');
fcds['name3']:=fcds2['hn'];
if getsqldata('select sex from patient where hn="'+fcds2['hn']+'"')='1' then
fcds['name4']:='ª' else
fcds['name4']:='';
if vartostr(fcds2['new_case'])='Y' then
fcds['name5']:='ãËÁè' else
fcds['name5']:='à¡èÒ';
fcds['name6']:= getsqlsubquerydata('select icd10 from clinic_persist_icd where hn="'+
fcds2['hn']+'" ')+' '+
getsqlsubquerydata('select name from icd101 where code in ('+
getsqlsubquerydata('select icd10 from clinic_persist_icd where hn="'+
fcds2['hn']+'" ') +') ');
fcds['name7']:=vartostr(getsqldata('select age_y from vn_stat where vn="'+
vartostr(getsqldata('select max(vn) as cc from ovst where hn="'+fcds2['hn']+'"')) +'"'));
fcds['name8']:=vartostr(getsqldata('select death from patient where hn="'+fcds2['hn']+'"'));
if fcds['name8']='Y' then
fcds['name9']:= 'µÒÂ '+
formatdatetime('dd/mm/ee',getsqldata('select deathday from patient where hn="'+fcds2['hn']+'"'));
fcds['date1']:=d1;
fcds['date2']:=d2;
fcds['date3']:=fcds2['regdate'];
fcds.post;
fcds.insert;
fcds['id']:='CUSTOM-CHRONIC1';
fcds['reportname']:='CUSTOM-CHRONIC1';
fcds['num']:=2;
fcds['num2']:=i;
fcds['name1']:=vartostr(getsqldata('select cardno from ptcardno where hn="'+fcds2['hn']+'" and cardtype="01" '));
fcds.post;
fcds2.next;
end;
fcds.datarequest('select * from tempreport where id = "CUSTOM-CHRONIC1" ');
applyupdate_fcds();
end;
end.
//////////////
Error in unit 'MyIPDReport' on line 77 : Type mismatch.
ทดลองเปลี่ยน status เป็น 3 ไม่ error ทดสอบ status ก็ออกเป็น 3 ก็น่าจะเป็น string
fcds2.datarequest('select * from clinicmember where clinic = "'+clinic+'" and clinic_member_status_id = "3" '+
-
บรรทัดที่ 52 ลองแบบนี้ครับ
status:=inttostr(getsqldata('select clinic_member_status_id from clinic_member_status where clinic_member_status_name ="'+status+'"'));
;D ;D ;D
-
;D จริงด้วย ต้องเปลี่ยนเป็น string
-
ขอบคุณคุณ imnuke มากคับ เข้าใจมากๆเลย :)
-
จริง ๆ ถ้าเขียน code แล้วมี comment มาด้วย
จะมีประโยชน์มากเลยครับ เวลาจะแกก็แก้ง่าย คนเอาโค้ดไปดูต่อก็ดูง่าย ;D ;D
แต่แค่มีสคริปมาให้ดูก็สุดยอดแล้วครับ
-
ออ ผมเอาของอาจารย์ อาร์ม ด่านซ้ายมาครับ
ผมก็ไม่รู้เรื่องหรอก 555
-
onlineid:=get_onlineid;
// showmessage('"'+onlineid+'"' );
if onlineid='' then onlineid:='CUSTOM-IPT1'; // fake id but will correct at runtime
มาแปลก วันนี้เอารายงานมาแก้ไข กลับเจอปัญหา get_onlineid
ไม่เป็น ตามเงื่อนไข onlineid='' ได้ค่า {ID_ONLINEID} มา
อ้าว ข่อยก็บ่ได้ไปยุงอะไรกับมันเลยนี่น่า
restore เอาของเก่ามาใช้ก็ไม่หาย
ก็เลยปิด onlineid:=get_onlineid; มันซะเลย 555
ที่สงสัยคือแล้วทำไมเมื่อก่อนมันใช้ได้
พอเอามาแก้กับ HOSxp 3.53.5.19 แล้วใช้งานไม่ได้ซะงั้น
-
ต้องไป run ใน hosxp ครับ get_onlineid จึงจะได้ค่าครับ
-
;D ใช่เลย