001.<?
002.require_once "class/class.writeexcel_workbook.inc.php";
003.require_once "class/class.writeexcel_worksheet.inc.php";
004.include("connect.php");
005.$sdate=$_POST["sdate"];
006. $nsdate=$_POST["sdate"];
007. $nsy=substr($nsdate,0,4);
008. $nsm=substr($nsdate,5,2);
009. $nsd=substr($nsdate,8,2);
010. $nsdate=$nsd."/".$nsm."/".$nsy;
011.$edate=$_POST["edate"];
012. $nedate=$_POST["edate"];
013. $ney=substr($nedate,0,4);
014. $nem=substr($nedate,5,2);
015. $ned=substr($nedate,8,2);
016. $nedate=$ned."/".$nem."/".$ney;
017.$outcode=$_POST["outcode"];
018.
019. $sqloutname="select * from outlet where outcode='$outcode'";
020. $queryoutname=mysql_query($sqloutname);
021. $rows=mysql_fetch_array($queryoutname);
022.$token = md5(uniqid(rand(), true));
023.$fname= "Report.xls";
024.$workbook =& new writeexcel_workbook($fname);
025.
026.$worksheet =& $workbook->addworksheet("รายงานการขาย");
027.$worksheet->set_margin_right(0.50);
028.$worksheet->set_margin_bottom(1.10);
029.
030.## Set Format ##
031.
032.$xlscelldesc_header =& $workbook->addformat();
033.
034.$xlscelldesc_header->set_font('Angsana New');
035.
036.$xlscelldesc_header->set_size(14);
037.
038.$xlscelldesc_header->set_color('blue');
039.
040.$xlscelldesc_header->set_bold(1);
041.
042.$xlscelldesc_header->set_text_v_align(1);
043.
044.$xlscelldesc_header->set_merge(1);
045.
046.$xlsCellDesc =& $workbook->addformat();
047.
048.$xlsCellDesc->set_font('Angsana New');
049.
050.$xlsCellDesc->set_size(12);
051.
052.$xlsCellDesc->set_color('black');
053.
054.
055.
056.$xlsCellDesc->set_align('left');
057.
058.$xlsCellDesc->set_text_v_align(1);
059.
060.## End of Set Format ##
061.
062.
063.
064.## Set Column Width & Height กำหนดความกว้างของ Cell
065.
066.$worksheet->set_column('A:A', 6.5);
067.$worksheet->set_column('B:B', 7.6 );
068.$worksheet->set_column('C:C', 5);
069.$worksheet->set_column('D:D', 5);
070.$worksheet->set_column('E:E', 12);
071.$worksheet->set_column('F:F', 12);
072.$worksheet->set_column('G:G', 12);
073.$worksheet->set_column('H:H', 12);
074.$worksheet->set_column('I:I', 15);
075.$worksheet->set_column('J:J', 8.5);
076.$worksheet->set_column('K:K', 9);
077.$worksheet->set_column('L:L', 10);
078.$worksheet->set_column('M:M', 12);
079.$worksheet->set_column('N:N', 13);
080.$worksheet->set_column('O:O', 13);
081.
082.
083.$celldesc_h = 16.50;
084.
085.
086.## Writing Data เพิ่มข้อมูลลงใน Cell
087.
088.
089.
090.# กำหนดความสูงของ Cell
091.
092.$worksheet->set_row(1, $celldesc_h);
093.
094.$worksheet->set_row(2, $celldesc_h);
095.
096.$worksheet->set_row(3, $celldesc_h);
097.
098.$worksheet->set_row(4, $celldesc_h);
099.
100.$worksheet->set_row(5, $celldesc_h);
101.
102.
103.
104.
105.$worksheet->write(G3, " รายงานการขายประจำวันที่ : ".$nsdate. " ถึงวันที่ ".$nedate." ห้าง ".$rows["OutName"], $xlscelldesc_header);
106.$worksheet->write_blank(F6,$xlscelldesc_header);
107.
108.
109.$worksheet->write(A5,"วันที่", $xlscelldesc_header);
110.
111.$worksheet->write(B5,"รหัสสินค้า", $xlscelldesc_header);
112.
113.$worksheet->write(C5,"ชื่อสินค้า", $xlscelldesc_header);
114.
115.$worksheet->write(D5,"จำนวน", $xlscelldesc_header);
116.
117.$worksheet->write(E5,"ราคาขายปลีกต่อชิ้น", $xlscelldesc_header);
118.
119.$worksheet->write(F5,"ส่วนลดต่อชิ้น (บาท)", $xlscelldesc_header);
120.
121.$worksheet->write(G5,"ราคาเก็บเงินต่อชิ้น", $xlscelldesc_header);
122.
123.$worksheet->write(H5,"จำนวนเงินรวมที่เก็บได้", $xlscelldesc_header);
124.
125.$worksheet->write(I5,"ส่วนลดการค้า", $xlscelldesc_header);
126.
127.$worksheet->write(J5,"ส่วนลด (%)", $xlscelldesc_header);
128.
129.$worksheet->write(K5,"ส่วนลดคูปอง (%)", $xlscelldesc_header);
130.$worksheet->write(L5,"ส่วนลดคูปอง ( บาท )", $xlscelldesc_header);
131.$worksheet->write(M5,"หมายเหตุ", $xlscelldesc_header);
132.$worksheet->write(N5,"รหัสจุดขาย", $xlscelldesc_header);
133.$worksheet->write(O5,"ชื่อจุดขาย", $xlscelldesc_header);
134.$worksheet->write(P5,"วันที่บันทึก", $xlscelldesc_header);
135.$worksheet->write(Q5,"PC", $xlscelldesc_header);
136.
137.
138.
139.
140.
141.
142.
143.
144.$xlsRow = 6;
145.
146.
147.
148.# ตรงนี้คือดึงข้อมูลจาก mysql มาใส่ใน Cell
149.if($outcode=="0000000"){
150.
151.$sql="select *
152.from saler s
153.inner join outlet o on (s.outcode=o.outcode)
154.inner join user u on (s.user_name=u.user_name)
155.inner join product p on (p.pdcode = s.pdcode)
156.inner join discount d ON (d.discode = s.discode)
157.where onsale between '$sdate' and '$edate'
158.order by s.onsale,s.discode,s.pdcode asc ";
159.
160.}else{
161.
162.$sql="select *
163.from saler s
164.inner join outlet o on (s.outcode=o.outcode)
165.inner join user u on (s.user_name=u.user_name)
166.inner join product p on (p.pdcode = s.pdcode)
167.inner join discount d ON (d.discode = s.discode)
168.where onsale between '$sdate' and '$edate'
169.and o.outcode='$outcode'
170.order by s.onsale,s.discode,s.pdcode asc ";
171.
172.}
173.
174.
175.$query=mysql_query($sql);
176.$num=mysql_num_rows($query);
177.if($num==0){
178.echo "<script language='javascript'>alert('ไม่พบข้อมูล');</script>";
179.print "<meta http-equiv=refresh content=0;URL=admin_1.php>";
180.
181.exit();
182.}else{
183.
184. while($result= mysql_fetch_array($query)){
185. $ndate=$result["Onsale"];
186. $y=substr($ndate,0,4);
187. $m=substr($ndate,5,2);
188. $d=substr($ndate,8,2);
189. $onsale=$d."/".$m."/".$y;
190. $ondate=$result["Onrecord"];
191. $oy=substr($ondate,0,4);
192. $om=substr($ondate,5,2);
193. $od=substr($ondate,8,2);
194. $onrecord=$od."/".$om."/".$oy;
195. $namena=$result["UName"];
196. $testna=$result["Outcode"];
197. if($testna==00000000){
198. $testna=$namena;
199. }else{
200. $testna="";
201. }
202.
203.
204.++$i;
205.
206.$test=$result["PDCode"];
207.$test=$test;
208.
209.$worksheet->set_row($xlsRow, 19.80);
210.$worksheet->write("A$xlsRow",$onsale, $xlsCellDesc);
211.$worksheet->write("B$xlsRow",(string)$result["PDCode"], $xlsCellDesc);
212.
213.$worksheet->write("C$xlsRow", $result["PDName"], $xlsCellDesc);
214.
215.$worksheet->write("D$xlsRow", $result["Quantity"], $xlsCellDesc);
216.
217.$worksheet->write("E$xlsRow", $result["Price"], $xlsCellDesc);
218.
219.$worksheet->write("F$xlsRow", $result["Dis_baht"], $xlsCellDesc);
220.
221.$worksheet->write("G$xlsRow", $result["PricePerPiece"], $xlsCellDesc);
222.
223.$worksheet->write("H$xlsRow", $result["Amount"], $xlsCellDesc);
224.
225.$worksheet->write("I$xlsRow", $result["DisName"], $xlsCellDesc);
226.
227.$worksheet->write("J$xlsRow", $result["Dispercent"], $xlsCellDesc);
228.
229.$worksheet->write("K$xlsRow", $result["couponspercent"], $xlsCellDesc);
230.
231.$worksheet->write("L$xlsRow", $result["couponsbaht"], $xlsCellDesc);
232.
233.$worksheet->write("M$xlsRow", $result["Note"], $xlsCellDesc);
234.$worksheet->write("N$xlsRow","\r".$result["OutCode"], $xlsCellDesc);
235.$worksheet->write("O$xlsRow", $result["OutName"], $xlsCellDesc);
236.$worksheet->write("P$xlsRow", $onrecord, $xlsCellDesc);
237.$worksheet->write("Q$xlsRow", $testna, $xlsCellDesc);
238.
239.
240.
241.$xlsRow++;
242.
243.
244.}
245.}
246.
247.# เสร็จแล้วก็ส่งไฟล์ไปยัง Browser ครับแค่นี้ก็เสร็จแล้ว
248.
249.$workbook->close();
250.
251.header("Pragma: public");
252.
253.header("Expires: 0");
254.
255.header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
256.
257.header("Content-Type: application/force-download");
258.
259.header("Content-Type: application/octet-stream");
260.
261.header("Content-Type: application/download");
262.
263.header("Content-Disposition: attachment; filename=".basename("DreamtoyReport.xls").";");
264.
265.header("Content-Transfer-Encoding: binary\r\n");
266.
267.header("Content-Length: ".filesize($fname));
268.
269.readfile($fname);
270.
271.unlink($fname);
272.
273.exit();
274.
275.?>
Quote:
