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
|
<?php
include 'config.php';
// Check if the user is logged in
if ((!isSet($_SESSION['loginname'])) || ($loggin <> '1'))
{
header("Location: login.php");
exit;
}
require("configure.php");
@set_time_limit(0);
//ini_set('max_execution_time', '999');
ini_set('post_max_size', '10M');
ini_set('memory_limit','1024M');
require('Classes/PHPExcel.php');
$objPHPexcel = PHPExcel_IOFactory::load('Classes/template/Report.xls');
$objWorksheet = $objPHPexcel->getActiveSheet();
$year = (int)$_GET["year"];
$date_form = htmlspecialchars($_POST["year"],ENT_QUOTES)."-01-01";
$date_to = htmlspecialchars($_POST["year"],ENT_QUOTES)."-12-31";
$index = $_POST['index'];
$companyid = $_POST['companyid'];
$sql = "SELECT * FROM file_company WHERE companyid=:companyid";
$sth = Db::getDbh()->prepare($sql);
$sth->execute(array(":companyid" => $companyid));
if( $error = $sth->getError(array(":companyid" => $companyid)) ){
var_dump($error);
}
$row = $sth->fetch(PDO::FETCH_ASSOC);
$excel_row=1;
$objWorksheet->setCellValueByColumnAndRow(0, $excel_row, $row{'title'});
$excel_row=5;
$sql = "SELECT * FROM report_content WHERE companyid=:companyid AND date>=:date_form AND date<=:date_to AND status = '1'";
$sth = Db::getDbh()->prepare($sql);
$sth->execute(array(":companyid" => $companyid, ":date_form" => $date_form, ":date_to" => $date_to));
if( $error = $sth->getError(array(":companyid" => $companyid, ":date_form" => $date_form, ":date_to" => $date_to)) ){
var_dump($error);
}
while ($row = $sth->fetch(PDO::FETCH_ASSOC)){
$field_a = $row{'field_a'};
$total_a[] = $field_a;
$field_b = $row{'field_b'};
$total_b[] = $field_b;
$field_c = $row{'field_c'};
$total_c[] = $field_c;
$field_d = $row{'field_d'};
$total_d[] = $field_d;
$field_e = $row{'field_a'} + $row{'field_c'};
$total_e[] = $field_e;
$field_f = $row{'field_b'} + $row{'field_d'};
$total_f[] = $field_f;
$field_g = round(($field_e / $field_f * 100), 2);
$objWorksheet->insertNewRowBefore($excel_row+1, 1);
$objWorksheet->setCellValueByColumnAndRow(0, $excel_row, date("Y-m-d",strtotime($row{'date'})));
$objWorksheet->setCellValueByColumnAndRow(1, $excel_row, $field_a);
$objWorksheet->setCellValueByColumnAndRow(2, $excel_row, $field_b);
$objWorksheet->setCellValueByColumnAndRow(3, $excel_row, $field_c);
$objWorksheet->setCellValueByColumnAndRow(4, $excel_row, $field_d);
$objWorksheet->setCellValueByColumnAndRow(5, $excel_row, $field_e);
$objWorksheet->setCellValueByColumnAndRow(6, $excel_row, $field_f);
$objWorksheet->setCellValueByColumnAndRow(7, $excel_row, $field_g);
$excel_row++;
}
if(is_array($total_a)){
$display_a = array_sum($total_a);
} else {
$display_a = 0;
}
if(is_array($total_b)){
$display_b = array_sum($total_b);
} else {
$display_b = 0;
}
if(is_array($total_c)){
$display_c = array_sum($total_c);
} else {
$display_c = 0;
}
if(is_array($total_d)){
$display_d = array_sum($total_d);
} else {
$display_d = 0;
}
if(is_array($total_e)){
$display_e = array_sum($total_e);
} else {
$display_e = 0;
}
if(is_array($total_f)){
$display_f = array_sum($total_f);
} else {
$display_f = 0;
}
if(is_array($total_e) && is_array($total_f)){
$display_g = round((array_sum($total_e) / array_sum($total_f) * 100), 2);
} else {
$display_g = 0;
}
$objWorksheet->insertNewRowBefore($excel_row+1, 1);
$objWorksheet->setCellValueByColumnAndRow(0, $excel_row, "TOTAL");
$objWorksheet->setCellValueByColumnAndRow(1, $excel_row, $display_a);
$objWorksheet->setCellValueByColumnAndRow(2, $excel_row, $display_b);
$objWorksheet->setCellValueByColumnAndRow(3, $excel_row, $display_c);
$objWorksheet->setCellValueByColumnAndRow(4, $excel_row, $display_d);
$objWorksheet->setCellValueByColumnAndRow(5, $excel_row, $display_e);
$objWorksheet->setCellValueByColumnAndRow(6, $excel_row, $display_f);
$objWorksheet->setCellValueByColumnAndRow(7, $excel_row, $display_g);
$objWorksheet->getStyle("A".$excel_row.":H".$excel_row)->getFont()->setBold(true);
$objWorksheet->getStyle("A5:H".$excel_row)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Report_'.$date_form.'~'.$date_to.'.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
flush();
?>
|