BMS-HOSxP Community

HOSxP => การเขียน SQL Script => ข้อความที่เริ่มโดย: manoi ที่ กันยายน 04, 2006, 07:05:51 AM

หัวข้อ: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 04, 2006, 07:05:51 AM

มาดูวิธีการใช้ variable ช่วยทำรายงานครับ ปกติรายงานต่างๆ สามารถใช้คำสั่ง SQL ดึงข้อมูลมาจัดรูปแบบในระบบรายงานได้อยู่แล้ว แต่มีบางกรณีที่วิธีการปกติทำไม่ได้หรือทำได้แต่ใช้เวลานานครับ ยกตัวอย่างเช่น รายงานสรุปยอดการใช้ยาทุกตัวในปี 2548  แยกจำนวนใบสั่ง และ มูลค่า โดยต้องการแสดงผลลัพท์ดังนี้

-----------------------------------------
รหัส |  ชื่อ  | จำนวนใบสั่ง  | มูลค่า
-----------------------------------------

สำหรับคนที่เข้าใจ datadictionary และเขียนคำสั่ง SQL ได้คงจะยิ้มแล้วนึกในใจว่าหมูมาก แค่เขียนคำสั่งดังนี้

โค๊ด: SQL

1. SELECT i.icode,i.name,i.strength,i.units,COUNT(DISTINCT o.vn) AS prescribe_count,COUNT(o.icode) AS item_count, SUM(o.qty) AS sum_qty,SUM(o.sum_price) AS tot_price
2. FROM drugitems i
3. LEFT OUTER JOIN opitemrece o ON o.icode = i.icode
4. WHERE o.rxdate BETWEEN '2005-01-01' AND '2005-12-31'
5. GROUP BY i.icode,i.name,i.strength,i.units
6.  

ก็เอาไปทำรายงานได้แล้ว  แต่ปัญหาก็คือหากข้อมูลที่นำมาใช้ทำรายงานมีจำนวนเยอะมาก ระยะเวลาที่ใช้ทำรายงานก็จะนานมากเช่นเดียวกัน นั่งรอ 3 ชั่วโมง ยังไม่ได้รายงานเลย (ถาม อ.สุชัย ดูก็ได้ครับ นั่งรอทั้งคืนก็ยังไม่เสร็จ) 

ถ้างั้นลองเปลี่ยนคำสั่งด้านบนให้ดึงรายงานของยาแค่ตัวเดียวมาก่อน ด้วยคำสั่งนี้

โค๊ด: SQL

1. SELECT i.icode,i.name,i.strength,i.units,COUNT(DISTINCT o.vn) AS prescribe_count,COUNT(o.icode) AS item_count, SUM(o.qty) AS sum_qty,SUM(o.sum_price) AS tot_price
2. FROM drugitems i
3. LEFT OUTER JOIN opitemrece o ON o.icode = i.icode
4. WHERE o.icode='1470038' AND o.rxdate BETWEEN '2005-01-01' AND '2005-12-31'
5. GROUP BY i.icode,i.name,i.strength,i.units
6.  

ลองเอาไป run ดูใหม่ ปรากฎว่าใช้เวลาแค่อึดใจเดียวก็ได้ผลแล้ว แต่มันได้มาแค่ยาตัวเดียว มันไม่มาทั้งหมด ครั้นจะเอาไป copy แล้วเรียกใช้งานกับยาทุกตัว ก็คงจะไม่สะดวก สู้นั่งรอเหมือนเดิมดีกว่า

แล้วมาดูว่า Variable จะช่วยแก้ปัญหานี้ได้อย่างไร เริ่มต้นจากเปลี่ยนคำสั่งให้แสดงแค่ข้อมูลยาทุกตัวก่อน แล้วค่อยไปคำนวนจำนวนในระบบรายงานครับ โดยการเปลี่ยนคำสั่งที่ใช้ทำรายงานเป็นแบบ simple ๆ แบบนี้

โค๊ด: SQL

1. SELECT icode,name,strength,units FROM drugitems ORDER BY name
2.  

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 04, 2006, 07:17:45 AM

จากนั้นเอาไปเป็นคำสั่งสำหรับทำรายงาน ดังรูป

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 04, 2006, 07:21:16 AM

ออกแบบหน้าจอให้เรียบร้อยดังรูป แต่ยังติดปัญหาตรงที่จะเอาจำนวน และ มูลค่าของยาแต่ละตัวมาใส่ในรายงานได้อย่างไร ในเมื่อคำสั่ง SQL ที่ใช้ ไม่ได้ดึงข้อมูลนี้มาเลย

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 04, 2006, 07:22:50 AM

มาดูกันต่อครับ

อันดับแรก วาง Variable ลงไปใน Detail Band ดังรูป จากนั้นคลิกขวา เลือก Calculations

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 04, 2006, 09:34:39 AM

จะปรากฎหน้าต่าง Calculations ดังรูป

1 คือช่องสำหรับเขียน Pascal code
2 คือช่องสำหรับเลือกข้อมูลจะเปลี่ยนตาม tab ของ 3
3 คือชนิดของข้อมูลที่จะเลือกมาใช้ มี 3 แบบคือ Data , Objects และ Language

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 04, 2006, 09:37:09 AM

เลือก tab Language แล้วที่ช่อง 2 เลือก Math จากนั้นลากแถวที่เขียนว่า GetSQLIntegerData จากช่อง 3 มาไว้ในช่อง 1

หากทำตามไม่ถูก ก็เขียนคำสั่งเข้าไปตรงๆ ในช่อง 1 ก็ได้ครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 04, 2006, 09:40:22 AM

จากนั้นแก้ไข pascal code ใหม่ให้เป็นดังรูปครับ

โค๊ด: Delphi

1.  
2.   Value := GetSQLIntegerData('select count(*) as cc from opitemrece where icode="'+
3.    
4.    DBPipeline['icode'] +'" and rxdate between "2005-01-01" and "2005-12-31" ');
5.  

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 04, 2006, 09:44:32 AM

จากนั้นกดปุ่ม Ok ก็จะกลับมาที่ Designer ให้ลองคลิกที่ Tab preview เพื่อดูตัวอย่าง ก็จะได้ผลดังรูปครับ

ยังไม่ค่อยสวยเพราะยังไม่ได้จัดตำแหน่ง แต่ได้จำนวนมาแสดงแล้วใช่ไหมครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 04, 2006, 09:50:02 AM

เอ แล้วคำสั่งด้านบน

โค๊ด: Pascal

1.  Value := GetSQLIntegerData('select count(*) as cc from opitemrece where icode="'+
2.    
3.    DBPipeline['icode'] +'" and rxdate between "2005-01-01" and "2005-12-31" ');
4.  

 มันคืออะไรกัน 

ปกติ Variable จะำนำค่าที่ได้จากตัวแปรที่ชื่อ Value ไปใช้งานครับ Value จะเป็นตัวแปรชนิด Variant หมายความว่าจะกำหนด datatype ให้เป็นชนิดไหน ก็ได้ 
ส่วน Function GetSQLIntegerData เป็น function ที่นำคำสั่ง sql ส่งไปให้ database server ประมวลผล และส่งผลที่ได้ field แรก จาก record แรก มาครับ (จากคำสั่งจะได้ผลลัพท์ 1 record และ 1 field) 
แล้ว DBPipeLine['icode'] ใน function GetSQLintegerData คืออะไร  มันคล้ายๆกับ Dataset ของ delphi ครับ หากระบุ DBPipeLine['icode'] หมายความว่า นำข้อมูลใน field icode ที่อยู่ใน dataset มาใช้ (dataset ที่ได้มา ก็จากคำสั่ง select * from drugitems ครับ) ซึ่งก็ขึ้นอยู่กับว่า cursor ปัจจุบันอยู่ตรงตำแหน่งไหน ปกติตัว report engine จะทำการวนจาก record แรก ไปยัง record สุดท้าย ดังนั้นค่าของ DBPipeline['icode'] ก็จะเปลี่ยนไปเรื่อยๆ ครับ (จาก record แรก ไปยัง record สุดท้าย) ที่สำคัญต้องวาง Variable ให้ถูกตำแหน่งด้วยครับ จากตัวอย่างเราจะวางไว้ใน Detail Band ครับ 

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 04, 2006, 09:57:02 AM

อ้าวยังไม่ครบนี่ครับ เหลือมูลค่า จะทำอย่างไรจึงจะได้มูลค่ามาแสดงครับ

ให้สมาชิกลองฝึกทำตามดูดีกว่าดีไหมครับ ดีกว่าอ่านอย่างเดียว  เอาเป็นว่าผมให้เวลา 3 วันให้สามาชิกฝึกฝีมือโดยการนำมูลค่าของยามาใส่ในรายงาน จากนั้นให้ส่ง รายงานที่ทำเสร็จแล้วเข้ามาใน กระทู้ นี้ แล้วผมจะมาพาทำต่อครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: nahos ที่ กันยายน 04, 2006, 19:26:38 PM

ขอให้มี Part II  III IV  V  ......


ขอใหวตให้ครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 04, 2006, 23:09:20 PM

ยังไม่มีใครส่งงานเข้ามาเลยครับ เดี๋ยวจะพาทำต่อครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: nahos ที่ กันยายน 04, 2006, 23:51:40 PM

Value := GetSQLIntegerData('select sum(o.sum_price) AS tot_price from opitemrece where icode="'+
   
   DBPipeline['icode'] +'" and rxdate between "2005-01-01" and "2005-12-31" ');

ได้ไหมครับ (ลอกมาทั้งดุ้นเลย )

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 05, 2006, 00:13:39 AM

ขอเป็นรายงานที่ export มาได้หรือเปล่าครับ :)

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 05, 2006, 04:32:50 AM

เอาล่ะมาดูต่อดีกว่าครับ ถ้าสังเกตุจะเห็นว่ารายงานนี้ มีการกำหนดช่วงวันที่เอาไว้ในคำสั่งที่ดึงข้อมูลมา ถ้าเกิดว่าผู้ใช้ต้องการรายงานของปี 2549 ล่ะจะทำอย่างไร ต้องมาออกแบบใหม่แล้วตั้งชื่อเป็น รายงาน...ของปี...2549 หรือ   ยังก่อนครับ ยังไม่ต้องลำบากแบบนั้น

ในระบบรายงานนอกจากจะเขียน variable ให้ดึงอะไรต่อมิอะไรในฐานข้อมูลมาใช้งานได้แล้ว เรายังสามารถกำหนดให้ระบบรายงานทำการสอบถามผู้ใช้ให้ใส่วันที่และนำข้อมูลวันที่ๆ ผู้ใช้ใส่นำไปช่วยประมวลผลในการทำรายงานได้ด้วยครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 05, 2006, 04:37:37 AM

อันดับแรก คลิกที่แทบ Calc แล้วเลือกเมนู View -> Module

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 05, 2006, 04:39:54 AM

จากนั้นคลิกขวาที่ Variables แล้วเลือก New

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 05, 2006, 04:44:48 AM

เขียน Code ที่ช่องด้านล่าง ดังนี้

โค๊ด: Delphi

1. var
2.   ds1,ds2 : String;
3.  

เพื่อประกาศตัวแปรขึ้นมา 2 ตัว ชื่อ ds1 กับ ds2 เป็นประเภท String (ตัวอักษร) เพื่อที่จะนำมาใช้เก็บวันที่ๆ จะให้ผู้ใช้เลือกช่วงวันที่ของรายงานครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 05, 2006, 04:47:40 AM

จากนั้นในช่อง Module View ให้คลิกที่ Events แล้วคลิกขวา ตรง onCreate แล้วเลือก New

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 05, 2006, 04:53:55 AM

จากนั้นเขียน Code ในช่องด้านล่าง ดังนี้

โค๊ด: Delphi

1. Procedure GlobalOnCreate;
2. var date1,date2 : TDateTime;
3. begin
4.     GetDateRangeDialog(date1, date2);
5.     ds1 := Formatdatetime('yyyy-mm-dd',date1);
6.     ds2 := Formatdatetime('yyyy-mm-dd',date2);
7. end;
8.  

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 05, 2006, 04:58:24 AM

จากนั้นกลับมาที่ Tab Design แล้วคลิกขวาตรง Variable เพื่อแก้ไขคำสั่งที่ดึงข้อมูลมาแสดง โดยแก้ไขคำสั่งให้เป็น

โค๊ด: Delphi

1. Value := GetSQLIntegerData('select count(*) as cc from opitemrece where icode="'+
2.    
3.    DBPipeline['icode'] +'" and rxdate between "'+ds1+'" and "'+ds2+'" ');
4.  

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กันยายน 05, 2006, 05:01:29 AM

เสร็จแล้วก็ลองคลิกที่ tab Preview เพื่อทดสอบรายงาน จะปรากฎหน้าต่างแสดงให้ผู้ใช้เลือกวันที่ครับ และเมื่อผู้ใช้กดปุ่มตกลง ระบบก็จะนำข้อมูลตามช่วงวันที่ๆ เลือกเอาไว้มาทำรายงานครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: rtumayo ที่ กรกฎาคม 04, 2007, 10:49:33 AM

ขอบคุณครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: chinaga ที่ กรกฎาคม 05, 2007, 11:50:42 AM

ขอให้สอนวิธีการทำเพิ่มอีกครับ
ถ้าทำเสร็จแล้วของนำไปทดลองใช้ด้วยนะครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: manoi ที่ กรกฎาคม 05, 2007, 20:47:19 PM

วิธีทำอยู่ใน Yasothon admin training video นะครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: rtumayo ที่ กรกฎาคม 14, 2007, 18:18:19 PM

กำลังทำอยู่ครับ ไม่ค่อยมีเวลาทำ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: rtumayo ที่ กรกฎาคม 15, 2007, 08:35:10 AM

ทดลองครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: doramon ที่ กรกฎาคม 15, 2007, 09:10:10 AM

ดีมากเลยครับ
 ;D

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: thannawe ที่ กันยายน 12, 2007, 23:06:15 PM

สนใจมากๆครับ ขอบพระคุณครับ  ;)

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: suput ที่ สิงหาคม 06, 2009, 17:10:25 PM

ขอบคุณครับ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: mcbeau1234 ที่ มีนาคม 24, 2010, 13:43:59 PM

ขอบคุณมากค่ะ

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: LDHP ที่ สิงหาคม 11, 2010, 23:29:02 PM

มีปัญหาค่ะ ถ้าใช้ Variable จะส่งออก Excel ได้ไหมทำงัยดี  :'( :'( :'(

หัวข้อ: Re: การใช้ Variable ช่วยทำรายงาน Part I
เริ่มหัวข้อโดย: Bordin ที่ สิงหาคม 12, 2010, 08:05:50 AM

อ้างจาก: LDHP ที่ สิงหาคม 11, 2010, 23:29:02 PM

มีปัญหาค่ะ ถ้าใช้ Variable จะส่งออก Excel ได้ไหมทำงัยดี  :'( :'( :'(

ถ้าจะส่งออกเป็น Excel ให้ print to file เอาครับ..มีให้เลือกเวลาสั่ง print

คลิ๊กปุ่ม print ใน print preview นะครับ  ;D

ขึ้น print Dialog มา เลือก print to file เลือก Type เป็น Excel File ลองดูนะครับ