1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
<?php
include("./_common.php");
if($order_key==''){
if($_COOKIE['ok_product']!=''){
$_COOKIE['ok_product'] = str_replace("%20"," ",$_COOKIE['ok_product']);
$order_key = $_COOKIE['ok_product'];
} else {
$order_key = 'a.code1 desc';
}
}
$where2 = " ";
if($ordstate_tab!=''){
if($ordstate_tab=='9'){
$where2 .= " and ord_cancel = '1' ";
} else if(strlen($ordstate_tab)==1){
$where2 .= " and ordstate = '".substr($ordstate_tab,0,1)."' and ord_cancel = '0' ";
} else {
$where2 .= " and ordstate = '".substr($ordstate_tab,0,1)."' and deposit = '".substr($ordstate_tab,1,1)."' and ord_cancel = '0' ";
}
}
if($sdate!=''){
$where2 .= " and b.orddate >= '{$sdate}' ";
}
if($edate!=''){
$where2 .= " and b.orddate <= '{$edate}' ";
}
if($pay_type!=''){
$where2 .= " and pay_type = '{$pay_type}' ";
}
$stx = urldecode($stx);
if($stx!=''){
if($sti=='pname'){ // 상품명일때 처리
$pname_s = $stx;
} else {
$where2 .= " and {$sti} like '%{$stx}%' ";
}
}
if ($page < 1) { $page = 1; } // 페이지가 없으면 첫 페이지 (1 페이지)
$sql = "SELECT a.s_name, a.s_telno, a.s_hpno,a.s_email, b.consignee_name, b.consignee_telno, b.consignee_hpno, b.consignee_zipcode,
CONCAT(b.consignee_addr_m,b.consignee_addr_d) AS consignee_addr, a.od_id, sd.pname, sd.qty, pm.code1, a.deli_company, b.tracking_number, b.transport_msg
FROM sale_ord_s a
inner JOIN sale_ord_m b ON a.midx = b.midx
inner join sale_ord_d sd on a.midx = sd.midx
inner JOIN shop_cart sc ON sd.remarks = sc.idx
left join product_m pm on sd.pcode = pm.code
WHERE 1=1 {$where2}
ORDER BY a.midx DESC
LIMIT 0,999999";
//echo $sql; exit;
$res = mysql_query($sql,$connect_j3);
while($ordinfo=mysql_fetch_object($res)){
$info = null;
$col_cnt = 0;
foreach($ordinfo as $key=>$val){
$info[$key] = $val;
$col_cnt++;
}
$list_array[] = $info;
}
$qstr = $_SERVER['QUERY_STRING'];
$title_array = Array("주문자명","주문자전화","주문자핸드폰","이메일","배송자명","배송자전화","배송자핸드폰","우편번호","배송주소","주문번호","상품명","수량","상품코드","택배사명","송장번호","배송메모");
require_once $j3_lib_path."/phpexcel/PHPExcel.php";
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:j1');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1','* 주문 리스트 엑셀');
$objPHPExcel->getActiveSheet()->getStyle('A1:AZ1')->getAlignment()->setWrapText(true); // 워드랩 기능 추가
$objPHPExcel->getActiveSheet()->getStyle('A1:AZ1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(140); // 높이 조절
for($i=0;$i<$col_cnt;$i++){
if($i>25){
$vv = floor($i/26);
$v = $i-(26*$vv);
$alpha = chr(65+$v);
$alpha = chr(64+$vv).$alpha;
} else {
$alpha = chr(65+$i);
}
$objPHPExcel->getActiveSheet()->getColumnDimension($alpha)->setAutoSize(true);
}
for($i=0;$i<$col_cnt;$i++){
if($i>25){
$vv = floor($i/26);
$v = $i-(26*$vv);
$alpha = chr(65+$v);
$alpha = chr(64+$vv).$alpha;
} else {
$alpha = chr(65+$i);
}
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue($alpha.'2', $title_array[$i]);
}
for($i=0;$i<count($list_array);$i++){
$info = $list_array[$i];
$x = 0;
foreach($info as $key => $val){
if($x>25){
$vv = floor($x/26);
$v = $x-(26*$vv);
$alpha = chr(65+$v);
$alpha = chr(64+$vv).$alpha;
} else {
$alpha = chr(65+$x);
}
//if($x==16){ $val = addslashes($val); }
if($alpha=="J"){
$objPHPExcel->getActiveSheet()->setCellValueExplicit($alpha.($i+3), $val,PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($alpha.($i+3), $val);
}
$x++;
}
}
$objPHPExcel->getActiveSheet()->setTitle('주문리스트');
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="orderlist_xls'.$limit.'.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
?>
|