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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
|
<?php
$dir = ".";
include_once($dir."/_common.php");
if($eid==''){
alert('e천년경영 로그인 후에 이용가능합니다.',"index.php"); exit;
}
if($sdate==''){ $sdate = date("Y-m-",time())."01"; }
if($edate==''){ $edate = date("Y-m-d",time()); }
$order_key = 'a.dealdate desc, data_created desc ';
if($smode=='comp'){
$where = " and a.dealdate >= '{$sdate}' and a.dealdate <= '{$edate}' and b.taxno <> '' ";
$where1 = " and aa.dealdate >= '{$sdate}' and aa.dealdate <= '{$edate}' and bb.taxno <> '' ";
$where2 = " and aaa.dealdate >= '{$sdate}' and aaa.dealdate <= '{$edate}' and bbb.taxno <> '' ";
} else {
$where = " and a.dealdate >= '{$sdate}' and a.dealdate <= '{$edate}' and b.taxno is null ";
$where1 = " and aa.dealdate >= '{$sdate}' and aa.dealdate <= '{$edate}' and bb.taxno is null ";
$where2 = " and aaa.dealdate >= '{$sdate}' and aaa.dealdate <= '{$edate}' and bbb.taxno is null ";
}
if($stx!=''){
$where .= " and {$sti} like '%{$stx}%' ";
}
if($stc!=''){
$where .= " and a.ccode = '{$stc}' ";
$where1 .= " and aa.ccode = '{$stc}' ";
$where2 .= " and aaa.ccode = '{$stc}' ";
}
if($stpc!=''){
$where .= " and prod_cate_code2 = '{$stpc}' ";
}
$sql = "select e.deal_month, a.ccode, c.comp_name, c.code1, c.comp_sn, b.tax_type,e.pname,
count(b.qty) as pcnt, sum(b.sup_amt) as sum_sup_amt, sum(b.vat) as sum_vat, sum(b.tot_amt) as sum_tot_amt
from sale_m a left join sale_d b on a.midx = b.midx
left join customers c on a.ccode = c.code
left join
((select b.tax_type as tax_type, substring(a.dealdate,1,7) as deal_month, pname
from sale_m a left join sale_d b on a.midx = b.midx
where 1=1 {$where} and b.tax_type = 0 order by a.dealdate desc limit 1)
union
(select bb.tax_type as tax_type, substring(aa.dealdate,1,7) as deal_month, pname
from sale_m aa left join sale_d bb on aa.midx = bb.midx
where 1=1 {$where1} and bb.tax_type = 1 order by aa.dealdate desc limit 1)
union
(select bbb.tax_type as tax_type, substring(aaa.dealdate,1,7) as deal_month, pname
from sale_m aaa left join sale_d bbb on aaa.midx = bbb.midx
where 1=1 {$where2} and bbb.tax_type = 2 order by aaa.dealdate desc limit 1)
)
e on b.tax_type = e.tax_type
where 1=1 {$where} and deal_month is not null
group by a.ccode, c.comp_name, b.tax_type
order by a.ccode, tax_type;
";
$result = mysql_query($sql,$connect_e1000y);
_pr($sql);
$cnt = 0;
while($cus_info=mysql_fetch_array($result)){
$cus_info["pos"] = $cnt;
$cus_info["type"] = "n";
$list_array[] = $cus_info;
$cnt++;
}
$qstr = $_SERVER['QUERY_STRING'];
$tmp_ccode = "0"; $old_pos = -2;
for($i=0;$i<count($list_array);$i++){
$info = $list_array[$i];
$info_sum = null; $info_cust = null;
if($tmp_ccode!=$info['code1']){ // 거래처가 바꼈을때
if($i>0){
$info_sum["pos"] = ($i-1).".5";
$info_sum["type"] = "s";
$info_sum["sum_sup_amt"] = $s_amt;
$info_sum["sum_vat"] = $s_vat;
$info_sum["sum_tot_amt"] = $s_tot;
$v = $i.".5";
$list_array[$v] = $info_sum;
$s_amt = 0; $s_vat = 0; $s_tot = 0;
}
$info_cust["pos"] = $old_pos;;
$info_cust["type"] = "c";;
$info_cust["comp_name"] = $list_array[$i-1]["comp_name"];
$info_cust["ccode"] = $list_array[$i-1]["ccode"];
$info_cust["code1"] = $list_array[$i-1]["code1"];
$v = $i.".9";
if($i>0){
$list_array[$v] = $info_cust;
}
$old_pos = ($i-1).".8";
$tmp_ccode = $info['ccode']; $cnt++;
}
$s_amt += $info['sum_sup_amt']; $s_vat += $info['sum_vat']; $s_tot += $info['sum_tot_amt'];
}
$vv = array_sort($list_array,"pos", SORT_ASC);
_pr($vv);
exit;
require_once dirname(__FILE__) . '/lib/phpexcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
$title_array = Array("거래일자","시간","거래처명","건수","총수량","합계금액","출금액","건별미불","계산서","전표비고");
for($i=0;$i<count($title_array);$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.'1', $title_array[$i]);
$objPHPExcel->getActiveSheet()->getColumnDimension($alpha)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension($alpha)->setAutoSize(true);
}
for($i=0;$i<count($list_array);$i++){
$it = $list_array[$i];
$x = 0;
$xx = 0;
$dcnt_temp = 0;
foreach($it as $key => $val){
if($xx%2==0){
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($key=='3') { $dcnt_temp = $val; }
if($key=='1'){
$val = substr($val,11,8);
}
if($key=='8'){
if($val==0){ $val = "미발행"; }
else if($dcnt_temp>$val){ $val = "부분발행"; }
else if($dcnt_temp==$val){ $val = "발행"; }
}
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue($alpha.($i+2), $val);
$x++;
}
$xx++;
}
}
$objPHPExcel->getActiveSheet()->setTitle('customers');
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="sale_list_'.date("Ymd",time()).'.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');
?>
|