· 9 years ago · Nov 30, 2016, 09:04 AM
1<?php
2 ini_set( 'max_execution_time', 90000);
3 echo "\xEF\xBB\xBF"; // UTF-8 BOM
4
5 // generate report
6
7 $customer_id = "";
8 $customer_name = "";
9 $demarcation = "";
10 $municipality = "";
11 $current_user_id = 1;
12
13
14// parsing POST payload body
15$request_body = file_get_contents('php://input');
16$json_decoded = json_decode($request_body, true);
17$data = $json_decoded['data'];
18// var_dump($data['customer_id']);
19
20 if( isset( $data['start_date'] ) && isset( $data['end_date'] ) )
21 {
22 $start_date = $data['start_date'];
23 $end_date = $data['end_date'];
24
25 if ( isset( $data['current_user_id'] ) && !empty($data['current_user_id']) )
26 {
27 $current_user_id = $data['current_user_id'];
28 }
29
30 if ( isset( $data['customer_id'] ) )
31 {
32 $customer_id = $data['customer_id'];
33 }
34
35 if ( isset( $data['demarcation'] ) )
36 {
37 $demarcation = $data['demarcation'];
38 }
39
40 if ( isset( $data['municipality'] ) )
41 {
42 $municipality = $data['municipality'];
43 }
44
45 $xls_report_path = generateReport( $adb, $demarcation, $municipality, $customer_id, $start_date, $end_date, $current_user_id );
46 echo json_encode( array( "url" => $xls_report_path ) );
47 die();
48 }
49 else
50 {
51 echo json_encode( array( "url" => "" ) );
52 die();
53 }
54
55
56 /**
57 * Generate Excel Report
58 *
59 * @param `adb` obj - connection
60 * @return str
61 */
62 function generateReport( $adb, $demarcation = "", $municipality = "", $customer_id = "", $start_date = "", $end_date = "", $current_user_id = 0 ) {
63
64
65 $root_path = getcwd();
66 $root_path = str_replace( "\\", "/", $root_path );
67
68 /*==================================
69 * Create directory
70 *==================================*/
71 $output_path = "vendor/php_excel/templates/output/";
72
73 /*==================================
74 * Queries
75 *==================================*/
76
77 if ( truncateStomaReport( $adb ) ) {
78 try
79 {
80 setExportDetails( $adb, $demarcation, $municipality, $customer_id, $start_date, $end_date, $current_user_id );
81 }
82 catch ( Exception $e )
83 {
84 echo json_encode( array( "url" => "" ) );
85 die();
86 }
87 }
88
89 // get report details
90 $start = microtime(true);
91 $report_details = getStomaReport( $adb );
92 $time_elapsed_secs = microtime(true) - $start;
93 echo "getStomaReport: ".$time_elapsed_secs."\n";
94
95 $start = microtime(true);
96 /*==================================
97 * PHPExcel_IOFactory
98 *==================================*/
99 require_once $root_path . '/vendor/php_excel/PHPExcel/IOFactory.php';
100 $objReader = PHPExcel_IOFactory::createReader('Excel5');
101
102 /*==================================
103 * Excel Template
104 *==================================*/
105 // get entry count
106
107 $objPHPExcel = $objReader->load( $root_path . "/vendor/php_excel/templates/template-stoma-report.xls");
108 $file_name = "report-stoma-" . time();
109
110 /*==================================
111 * Plotting Starts Here
112 *==================================*/
113
114 $active_sheet = 0;
115 $objPHPExcel->setActiveSheetIndex( $active_sheet );
116
117 // Bhans
118 $activeSheet = $objPHPExcel->getActiveSheet();
119
120 $date = date( "m/d/Y" );
121 $activeSheet->setCellValue('F3', date( 'Y/m/d', strtotime( $start_date ) ).'～'.date( 'Y/m/d', strtotime( $end_date ) )); // plot date
122
123 // demarcation
124 $demarcation_str = "";
125 if( !empty( $demarcation ) )
126 {
127 if( $demarcation == 10 ){
128 $demarcation_str = "ä»™å°";
129 }
130 if( $demarcation == 20 ){
131 $demarcation_str = "山形";
132 }
133 }
134
135 $activeSheet->setCellValue( 'B3', $demarcation_str );
136
137 // STYLES
138 $borders = array(
139 'borders' => array(
140 'top' => array(
141 'style' => PHPExcel_Style_Border::BORDER_HAIR,
142 'color' => array( 'rgb' => '000' )
143 ),
144 'right' => array(
145 'style' => PHPExcel_Style_Border::BORDER_HAIR,
146 'color' => array( 'rgb' => '000' )
147 ),
148 'bottom' => array(
149 'style' => PHPExcel_Style_Border::BORDER_HAIR,
150 'color' => array( 'rgb' => '000' )
151 ),
152 'left' => array(
153 'style' => PHPExcel_Style_Border::BORDER_HAIR,
154 'color' => array( 'rgb' => '000' )
155 )
156 )
157 );
158 $align_left = array(
159 'alignment' => array(
160 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
161 )
162 );
163
164 $align_right = array(
165 'alignment' => array(
166 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
167 'wrap' => true
168 )
169 );
170
171 $negative = array(
172 'font' => array(
173 'color' => array( 'rgb' => 'F00' )
174 )
175 );
176 $default = array(
177 'font' => array(
178 'color' => array( 'rgb' => '000' )
179 )
180 );
181 $style_header_fill = array(
182 'fill' => array(
183 'type' => PHPExcel_Style_Fill::FILL_SOLID,
184 'color' => array('rgb' => 'DDEBF7')
185 )
186 );
187 $style_header_alignment = array(
188 'alignment' => array(
189 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
190 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
191 )
192 );
193
194 // set header fill color
195 $activeSheet->getStyle( 'A5:AJ5' )->applyFromArray( array_merge( $borders, $style_header_alignment, $default, $style_header_fill ) );
196 $activeSheet->getStyle( 'A6:AJ6' )->applyFromArray( array_merge( $borders, $style_header_alignment, $default, $style_header_fill ) );
197
198 $lastline = array();
199
200 // initiate total values
201 $total_sumexpense = 0;
202 $total_parentsumexpense = 0;
203 $total_returnexpense = 0;
204 $total_finalsumexpense = 0;
205 $total_sumindividual = 0;
206 $total_summunicipality = 0;
207 $total_customerbillingamount = 0;
208 $total_customerdeposit1 = 0;
209 $total_customerdeposit2 = 0;
210 $total_customerdeposit3 = 0;
211 $total_customerdeposit4 = 0;
212 $total_customerremaining = 0;
213 $total_munbillingamount = 0;
214 $total_mundeposit = 0;
215 $total_muntotalremaining = 0;
216 $total_totalremaining = 0;
217
218 $grand_total_sumexpense = 0;
219 $grand_total_parentsumexpense = 0;
220 $grand_total_returnexpense = 0;
221 $grand_total_finalsumexpense = 0;
222 $grand_total_sumindividual = 0;
223 $grand_total_summunicipality = 0;
224 $grand_total_customerbillingamount = 0;
225 $grand_total_customerdeposit1 = 0;
226 $grand_total_customerdeposit2 = 0;
227 $grand_total_customerdeposit3 = 0;
228 $grand_total_customerdeposit4 = 0;
229 $grand_total_customerremaining = 0;
230 $grand_total_munbillingamount = 0;
231 $grand_total_mundeposit = 0;
232 $grand_total_muntotalremaining = 0;
233 $grand_total_totalremaining = 0;
234
235 $iter = 7;
236 // report details
237 foreach ( $report_details as $key => $value ) {
238 # code...
239
240 // set row height
241 $activeSheet->getRowDimension( $iter )->setRowHeight( 24 );
242
243 unset( $report_details[ $key ][ 'upd_user' ] );
244 unset( $report_details[ $key ][ 'upd_date' ] );
245 unset( $report_details[ $key ][ 'add_date' ] );
246
247 $raw_total_remaining = explode( "|", $report_details[ $key ][ 'total_remaining' ]);
248 $report_details[ $key ][ 'total_remaining' ] = $raw_total_remaining[ 0 ];
249 $report_details[ $key ][ 'deposits_count_flag' ] = isset( $raw_total_remaining[ 1 ] )? $raw_total_remaining[ 1 ] : 0;
250
251 $activeSheet->setCellValue( 'A' . $iter , formatValue( $report_details[ $key ][ 'customer_cd' ] ) );
252 $activeSheet->getStyle( 'A'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
253
254 $activeSheet->setCellValue( 'B' . $iter , formatValue( $report_details[ $key ][ 'customer_name' ] ) );
255 $activeSheet->getStyle( 'B'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
256
257 $activeSheet->setCellValue( 'C' . $iter , formatValue( $report_details[ $key ][ 'municipality' ] ) );
258 $activeSheet->getStyle( 'C'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
259
260 $activeSheet->setCellValue( 'D' . $iter , $report_details[ $key ][ 'personal_number' ] );
261 $activeSheet->getStyle( 'D'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
262
263 $activeSheet->setCellValue( 'E' . $iter , formatValue( $report_details[ $key ][ 'order_no' ] ) );
264 $activeSheet->getStyle( 'E'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
265
266 $activeSheet->setCellValue( 'F' . $iter , formatValue( $report_details[ $key ][ 'order_date' ] ) );
267 $activeSheet->getStyle( 'F'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
268
269 $activeSheet->setCellValue( 'G' . $iter , formatValue( $report_details[ $key ][ 'sum_expense' ] ) );
270 $cellformat = cellFormat( $report_details[ $key ][ 'sum_expense' ] ) ? $negative : $default;
271 $activeSheet->getStyle( 'G'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
272 $total_sumexpense = $total_sumexpense + $report_details[ $key ][ 'sum_expense' ];
273
274 $activeSheet->setCellValue( 'H' . $iter , formatValue( $report_details[ $key ][ 'delivery_date' ] ) );
275 $activeSheet->getStyle( 'H'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
276
277 $activeSheet->setCellValue( 'I' . $iter , formatValue( $report_details[ $key ][ 'parent_order_sum_expense' ] ) );
278 $cellformat = cellFormat( $report_details[ $key ][ 'parent_order_sum_expense' ] ) ? $negative : $default;
279 $activeSheet->getStyle( 'I'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
280 $total_parentsumexpense = $total_parentsumexpense + $report_details[ $key ][ 'parent_order_sum_expense' ];
281
282 $activeSheet->setCellValue( 'J' . $iter , formatValue( $report_details[ $key ][ 'children_order_date' ] ) );
283 $activeSheet->getStyle( 'J'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
284
285 $activeSheet->setCellValue( 'K' . $iter , formatValue( $report_details[ $key ][ 'return_expense' ] ) );
286 $cellformat = cellFormat( $report_details[ $key ][ 'return_expense' ] ) ? $negative : $default;
287 $activeSheet->getStyle( 'K'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
288 $total_returnexpense = $total_returnexpense + $report_details[ $key ][ 'return_expense' ];
289
290 $activeSheet->setCellValue( 'L' . $iter , formatValue( $report_details[ $key ][ 'final_sum_expense' ] ) );
291 $cellformat = cellFormat( $report_details[ $key ][ 'final_sum_expense' ] ) ? $negative : $default;
292 $activeSheet->getStyle( 'L'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
293 $total_finalsumexpense = $total_finalsumexpense + $report_details[ $key ][ 'final_sum_expense' ];
294
295 $activeSheet->setCellValue( 'M' . $iter , formatValue( $report_details[ $key ][ 'sum_individual' ] ) );
296 $cellformat = cellFormat( $report_details[ $key ][ 'sum_individual' ] ) ? $negative : $default;
297 $activeSheet->getStyle( 'M'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
298 $total_sumindividual = $total_sumindividual + $report_details[ $key ][ 'sum_individual' ];
299
300 $activeSheet->setCellValue( 'N' . $iter , formatValue( $report_details[ $key ][ 'sum_municipality' ] ) );
301 $cellformat = cellFormat( $report_details[ $key ][ 'sum_municipality' ] ) ? $negative : $default;
302 $activeSheet->getStyle( 'N'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
303 $total_summunicipality = $total_summunicipality + $report_details[ $key ][ 'sum_municipality' ];
304
305 $activeSheet->setCellValue( 'O' . $iter , formatValue( $report_details[ $key ][ 'customer_billing_date' ] ) );
306 $activeSheet->getStyle( 'O'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
307
308 $activeSheet->setCellValue( 'P' . $iter , formatValue( $report_details[ $key ][ 'customer_billing_amount' ] ) );
309 $cellformat = cellFormat( $report_details[ $key ][ 'customer_billing_amount' ] ) ? $negative : $default;
310 $activeSheet->getStyle( 'P'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
311 $total_customerbillingamount = $total_customerbillingamount + $report_details[ $key ][ 'customer_billing_amount' ];
312
313 $activeSheet->setCellValue( 'Q' . $iter , formatValue( $report_details[ $key ][ 'customer_payment_kbn1' ] ) );
314 $activeSheet->getStyle( 'Q'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
315
316 $activeSheet->setCellValue( 'R' . $iter , formatValue( $report_details[ $key ][ 'customer_deposit_date1' ] ) );
317 $activeSheet->getStyle( 'R'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
318
319 $activeSheet->setCellValue( 'S' . $iter , formatValue( $report_details[ $key ][ 'customer_deposit1' ] ) );
320 $cellformat = cellFormat( $report_details[ $key ][ 'customer_deposit1' ] ) ? $negative : $default;
321 $objPHPExcel->getActiveSheet()->getStyle( 'S'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
322 $total_customerdeposit1 = $total_customerdeposit1 + $report_details[ $key ][ 'customer_deposit1' ];
323
324 $activeSheet->setCellValue( 'T' . $iter , formatValue( $report_details[ $key ][ 'customer_payment_kbn2' ] ) );
325 $activeSheet->getStyle( 'T'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
326
327 $activeSheet->setCellValue( 'U' . $iter , formatValue( $report_details[ $key ][ 'customer_deposit_date2' ] ) );
328 $activeSheet->getStyle( 'U'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
329
330 $activeSheet->setCellValue( 'V' . $iter , formatValue( $report_details[ $key ][ 'customer_deposit2' ] ) );
331 $cellformat = cellFormat( $report_details[ $key ][ 'customer_deposit2' ] ) ? $negative : $default;
332 $activeSheet->getStyle( 'V'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
333 $total_customerdeposit2 = $total_customerdeposit2 + $report_details[ $key ][ 'customer_deposit2' ];
334
335 $activeSheet->setCellValue( 'W' . $iter , formatValue( $report_details[ $key ][ 'customer_payment_kbn3' ] ) );
336 $activeSheet->getStyle( 'W'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
337
338 $activeSheet->setCellValue( 'X' . $iter , formatValue( $report_details[ $key ][ 'customer_deposit_date3' ] ) );
339 $activeSheet->getStyle( 'X'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
340
341 $activeSheet->setCellValue( 'Y' . $iter , formatValue( $report_details[ $key ][ 'customer_deposit3' ] ) );
342 $cellformat = cellFormat( $report_details[ $key ][ 'customer_deposit3' ] ) ? $negative : $default;
343 $activeSheet->getStyle( 'Y'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
344 $total_customerdeposit3 = $total_customerdeposit3 + $report_details[ $key ][ 'customer_deposit3' ];
345
346 $activeSheet->setCellValue( 'Z' . $iter , formatValue( $report_details[ $key ][ 'customer_payment_kbn4' ] ) );
347 $activeSheet->getStyle( 'Z'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
348
349 $activeSheet->setCellValue( 'AA' . $iter , formatValue( $report_details[ $key ][ 'customer_deposit_date4' ] ) );
350 $activeSheet->getStyle( 'AA'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
351
352 $activeSheet->setCellValue( 'AB' . $iter , formatValue( $report_details[ $key ][ 'customer_deposit4' ] ) );
353 $cellformat = cellFormat( $report_details[ $key ][ 'customer_deposit4' ] ) ? $negative : $default;
354 $activeSheet->getStyle( 'AB'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
355 $total_customerdeposit4 = $total_customerdeposit4 + $report_details[ $key ][ 'customer_deposit4' ];
356
357 $activeSheet->setCellValue( 'AC' . $iter , formatValue( $report_details[ $key ][ 'customer_total_remaining' ] ) );
358 $cellformat = cellFormat( $report_details[ $key ][ 'customer_total_remaining' ] ) ? $negative : $default;
359 $activeSheet->getStyle( 'AC'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
360 $total_customerremaining = $total_customerremaining + $report_details[ $key ][ 'customer_total_remaining' ];
361
362 $activeSheet->setCellValue( 'AD' . $iter , formatValue( $report_details[ $key ][ 'municipality_billing_date' ] ) );
363 $activeSheet->getStyle( 'AD'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
364
365 $activeSheet->setCellValue( 'AE' . $iter , formatValue( $report_details[ $key ][ 'benefit_ticket_name' ] ) );
366 $activeSheet->getStyle( 'AE'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
367
368 $activeSheet->setCellValue( 'AF' . $iter , formatValue( $report_details[ $key ][ 'municipality_billing_amount' ] ) );
369 $cellformat = cellFormat( $report_details[ $key ][ 'municipality_billing_amount' ] ) ? $negative : $default;
370 $activeSheet->getStyle( 'AF'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
371 $total_munbillingamount = $total_munbillingamount + $report_details[ $key ][ 'municipality_billing_amount' ];
372
373 $activeSheet->setCellValue( 'AG' . $iter , formatValue( $report_details[ $key ][ 'municipality_deposit_date' ] ) );
374 $activeSheet->getStyle( 'AG'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
375
376 $activeSheet->setCellValue( 'AH' . $iter , formatValue( $report_details[ $key ][ 'municipality_deposit' ] ) );
377 $cellformat = cellFormat( $report_details[ $key ][ 'municipality_deposit' ] ) ? $negative : $default;
378 $activeSheet->getStyle( 'AH'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
379 $total_mundeposit = $total_mundeposit + $report_details[ $key ][ 'municipality_deposit' ];
380
381 $activeSheet->setCellValue( 'AI' . $iter , formatValue( $report_details[ $key ][ 'municipality_total_remaining' ] ) );
382 $cellformat = cellFormat( $report_details[ $key ][ 'municipality_total_remaining' ] ) ? $negative : $default;
383 $activeSheet->getStyle( 'AI'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
384 $total_muntotalremaining = $total_muntotalremaining + $report_details[ $key ][ 'municipality_total_remaining' ];
385
386 $activeSheet->setCellValue( 'AJ' . $iter , formatValue( $report_details[ $key ][ 'total_remaining' ] ) );
387 $cellformat = cellFormat( $report_details[ $key ][ 'total_remaining' ] ) ? $negative : $default;
388 $activeSheet->getStyle( 'AJ'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //>getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
389 $total_totalremaining = $total_totalremaining + $report_details[ $key ][ 'total_remaining' ];
390
391 if( $report_details[ $key ][ 'deposits_count_flag' ] > 4 ) {
392 $activeSheet->setCellValue( 'AK' . $iter , '※' );
393 $activeSheet->getStyle( 'AK'. $iter )->applyFromArray( array_merge( $align_left, $negative, $lastline ) );
394 }
395
396 /***********************************************/
397 /*** FOR TOTAL *********************************/
398 /***********************************************/
399
400 if( ( !isset( $report_details[ $key + 1 ] ) ) || ( isset( $report_details[ $key + 1 ] ) && $report_details[ $key ][ 'customer_cd' ] != $report_details[ $key + 1 ][ 'customer_cd' ] ) ) {
401
402 $iter++;
403
404 // set row height
405 $activeSheet->getRowDimension( $iter )->setRowHeight( 24 );
406
407 $lastline = array(
408 'fill' => array(
409 'type' => PHPExcel_Style_Fill::FILL_SOLID,
410 'color' => array('rgb' => 'D9D9D9')
411 )
412 );
413
414 $activeSheet->setCellValue( 'A' . $iter , formatValue( $report_details[ $key ][ 'customer_cd' ] ) );
415 $activeSheet->getStyle( 'A'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
416
417 $activeSheet->setCellValue( 'B' . $iter , "" );
418 $activeSheet->getStyle( 'B'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
419
420 $activeSheet->setCellValue( 'C' . $iter , "" );
421 $activeSheet->getStyle( 'C'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
422
423 $activeSheet->setCellValue( 'D' . $iter , "" );
424 $activeSheet->getStyle( 'D'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
425
426 $activeSheet->setCellValue( 'E' . $iter , "" );
427 $activeSheet->getStyle( 'E'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
428
429 $activeSheet->setCellValue( 'F' . $iter , "å°è¨ˆ" );
430 $activeSheet->getStyle( 'F'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
431
432 $activeSheet->setCellValue( 'G' . $iter , formatValue( $total_sumexpense ) );
433 $cellformat = cellFormat( $total_sumexpense ) ? $negative : $default;
434 $activeSheet->getStyle( 'G'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
435
436 $activeSheet->setCellValue( 'H' . $iter , "" );
437 $activeSheet->getStyle( 'H'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
438
439 $activeSheet->setCellValue( 'I' . $iter , formatValue( $total_parentsumexpense ) );
440 $cellformat = cellFormat( $total_parentsumexpense ) ? $negative : $default;
441 $activeSheet->getStyle( 'I'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
442
443 $activeSheet->setCellValue( 'J' . $iter , "" );
444 $activeSheet->getStyle( 'J'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
445
446 $activeSheet->setCellValue( 'K' . $iter , formatValue( $total_returnexpense ) );
447 $cellformat = cellFormat( $total_returnexpense ) ? $negative : $default;
448 $activeSheet->getStyle( 'K'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
449
450 $activeSheet->setCellValue( 'L' . $iter , formatValue( $total_finalsumexpense ) );
451 $cellformat = cellFormat( $total_finalsumexpense ) ? $negative : $default;
452 $activeSheet->getStyle( 'L'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
453
454 $activeSheet->setCellValue( 'M' . $iter , formatValue( $total_sumindividual ) );
455 $cellformat = cellFormat( $total_sumindividual ) ? $negative : $default;
456 $activeSheet->getStyle( 'M'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
457
458 $activeSheet->setCellValue( 'N' . $iter , formatValue( $total_summunicipality ) );
459 $cellformat = cellFormat( $total_summunicipality ) ? $negative : $default;
460 $activeSheet->getStyle( 'N'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
461
462 $activeSheet->setCellValue( 'O' . $iter , "" );
463 $activeSheet->getStyle( 'O'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
464
465 $activeSheet->setCellValue( 'P' . $iter , formatValue( $total_customerbillingamount ) );
466 $cellformat = cellFormat( $total_customerbillingamount ) ? $negative : $default;
467 $activeSheet->getStyle( 'P'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
468
469 $activeSheet->setCellValue( 'Q' . $iter , "" );
470 $activeSheet->getStyle( 'Q'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
471
472 $activeSheet->setCellValue( 'R' . $iter , "" );
473 $activeSheet->getStyle( 'R'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
474
475 $activeSheet->setCellValue( 'S' . $iter , formatValue( $total_customerdeposit1 ) );
476 $cellformat = cellFormat( $total_customerdeposit1 ) ? $negative : $default;
477 $activeSheet->getStyle( 'S'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
478
479 $activeSheet->setCellValue( 'T' . $iter , "" );
480 $activeSheet->getStyle( 'T'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
481
482 $activeSheet->setCellValue( 'U' . $iter , "" );
483 $activeSheet->getStyle( 'U'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
484
485 $activeSheet->setCellValue( 'V' . $iter , formatValue( $total_customerdeposit2 ) );
486 $cellformat = cellFormat( $total_customerdeposit2 ) ? $negative : $default;
487 $activeSheet->getStyle( 'V'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
488
489 $activeSheet->setCellValue( 'W' . $iter , "" );
490 $activeSheet->getStyle( 'W'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
491
492 $activeSheet->setCellValue( 'X' . $iter , "" );
493 $activeSheet->getStyle( 'X'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
494
495 $activeSheet->setCellValue( 'Y' . $iter , formatValue( $total_customerdeposit3 ) );
496 $cellformat = cellFormat( $total_customerdeposit3 ) ? $negative : $default;
497 $activeSheet->getStyle( 'Y'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
498
499 $activeSheet->setCellValue( 'Z' . $iter , "" );
500 $activeSheet->getStyle( 'Z'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
501
502 $activeSheet->setCellValue( 'AA' . $iter , "" );
503 $activeSheet->getStyle( 'AA'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
504
505 $activeSheet->setCellValue( 'AB' . $iter , formatValue( $total_customerdeposit4 ) );
506 $cellformat = cellFormat( $total_customerdeposit4 ) ? $negative : $default;
507 $activeSheet->getStyle( 'AB'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
508
509 $activeSheet->setCellValue( 'AC' . $iter , formatValue( $total_customerremaining ) );
510 $cellformat = cellFormat( $total_customerremaining ) ? $negative : $default;
511 $activeSheet->getStyle( 'AC'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
512
513 $activeSheet->setCellValue( 'AD' . $iter , "" );
514 $activeSheet->getStyle( 'AD'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
515
516 $activeSheet->setCellValue( 'AE' . $iter , "" );
517 $activeSheet->getStyle( 'AE'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
518
519 $activeSheet->setCellValue( 'AF' . $iter , formatValue( $total_munbillingamount ) );
520 $cellformat = cellFormat( $total_munbillingamount ) ? $negative : $default;
521 $activeSheet->getStyle( 'AF'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
522
523 $activeSheet->setCellValue( 'AG' . $iter , "" );
524 $activeSheet->getStyle( 'AG'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
525
526 $activeSheet->setCellValue( 'AH' . $iter , formatValue( $total_mundeposit ) );
527 $cellformat = cellFormat( $total_mundeposit ) ? $negative : $default;
528 $activeSheet->getStyle( 'AH'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
529
530 $activeSheet->setCellValue( 'AI' . $iter , formatValue( $total_muntotalremaining ) );
531 $cellformat = cellFormat( $total_muntotalremaining ) ? $negative : $default;
532 $activeSheet->getStyle( 'AI'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
533
534 $activeSheet->setCellValue( 'AJ' . $iter , formatValue( $total_totalremaining ) );
535 $cellformat = cellFormat( $total_totalremaining ) ? $negative : $default;
536 $activeSheet->getStyle( 'AJ'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
537
538 $grand_total_sumexpense += $total_sumexpense;
539 $grand_total_parentsumexpense += $total_parentsumexpense;
540 $grand_total_returnexpense += $total_returnexpense;
541 $grand_total_finalsumexpense += $total_finalsumexpense;
542 $grand_total_sumindividual += $total_sumindividual;
543 $grand_total_summunicipality += $total_summunicipality;
544 $grand_total_customerbillingamount += $total_customerbillingamount;
545 $grand_total_customerdeposit1 += $total_customerdeposit1;
546 $grand_total_customerdeposit2 += $total_customerdeposit2;
547 $grand_total_customerdeposit3 += $total_customerdeposit3;
548 $grand_total_customerdeposit4 += $total_customerdeposit4;
549 $grand_total_customerremaining += $total_customerremaining;
550 $grand_total_munbillingamount += $total_munbillingamount;
551 $grand_total_mundeposit += $total_mundeposit;
552 $grand_total_muntotalremaining += $total_muntotalremaining;
553 $grand_total_totalremaining += $total_totalremaining;
554
555 // reset total values
556 $total_sumexpense = 0;
557 $total_parentsumexpense = 0;
558 $total_returnexpense = 0;
559 $total_finalsumexpense = 0;
560 $total_sumindividual = 0;
561 $total_summunicipality = 0;
562 $total_customerbillingamount = 0;
563 $total_customerdeposit1 = 0;
564 $total_customerdeposit2 = 0;
565 $total_customerdeposit3 = 0;
566 $total_customerdeposit4 = 0;
567 $total_customerremaining = 0;
568 $total_munbillingamount = 0;
569 $total_mundeposit = 0;
570 $total_muntotalremaining = 0;
571 $total_totalremaining = 0;
572
573 $lastline = array();
574 }
575
576 $iter++;
577 }
578
579
580 // set row height
581 $activeSheet->getRowDimension( $iter )->setRowHeight( 24 );
582
583 // STOMA REPORT GRAND TOTAL
584
585 $lastline = array(
586 'fill' => array(
587 'type' => PHPExcel_Style_Fill::FILL_SOLID,
588 'color' => array('rgb' => 'D9D9D9')
589 )
590 );
591
592 $activeSheet->setCellValue( 'A' . $iter , "" );
593 $activeSheet->getStyle( 'A'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
594
595 $activeSheet->setCellValue( 'B' . $iter , "" );
596 $activeSheet->getStyle( 'B'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
597
598 $activeSheet->setCellValue( 'C' . $iter , "" );
599 $activeSheet->getStyle( 'C'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
600
601 $activeSheet->setCellValue( 'D' . $iter , "" );
602 $activeSheet->getStyle( 'D'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
603
604 $activeSheet->setCellValue( 'E' . $iter , "" );
605 $activeSheet->getStyle( 'E'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
606
607 $activeSheet->setCellValue( 'F' . $iter , "ç·åˆè¨ˆ" );
608 $activeSheet->getStyle( 'F'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
609
610 $activeSheet->setCellValue( 'G' . $iter , $grand_total_sumexpense );
611 $cellformat = cellFormat( $total_sumexpense ) ? $negative : $default;
612 $activeSheet->getStyle( 'G'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
613
614 $activeSheet->setCellValue( 'H' . $iter , "" );
615 $activeSheet->getStyle( 'H'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
616
617 $activeSheet->setCellValue( 'I' . $iter , $grand_total_parentsumexpense );
618 $cellformat = cellFormat( $grand_total_parentsumexpense ) ? $negative : $default;
619 $activeSheet->getStyle( 'I'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
620
621 $activeSheet->setCellValue( 'J' . $iter , "" );
622 $activeSheet->getStyle( 'J'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
623
624 $activeSheet->setCellValue( 'K' . $iter , $grand_total_returnexpense );
625 $cellformat = cellFormat( $grand_total_returnexpense ) ? $negative : $default;
626 $activeSheet->getStyle( 'K'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
627
628 $activeSheet->setCellValue( 'L' . $iter , $grand_total_finalsumexpense );
629 $cellformat = cellFormat( $grand_total_finalsumexpense ) ? $negative : $default;
630 $activeSheet->getStyle( 'L'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //>getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
631
632 $activeSheet->setCellValue( 'M' . $iter , $grand_total_sumindividual );
633 $cellformat = cellFormat( $grand_total_sumindividual ) ? $negative : $default;
634 $activeSheet->getStyle( 'M'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
635
636 $activeSheet->setCellValue( 'N' . $iter , $grand_total_summunicipality );
637 $cellformat = cellFormat( $grand_total_summunicipality ) ? $negative : $default;
638 $activeSheet->getStyle( 'N'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
639
640 $activeSheet->setCellValue( 'O' . $iter , "" );
641 $activeSheet->getStyle( 'O'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
642
643 $activeSheet->setCellValue( 'P' . $iter , $grand_total_customerbillingamount );
644 $cellformat = cellFormat( $grand_total_customerbillingamount ) ? $negative : $default;
645 $activeSheet->getStyle( 'P'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
646
647 $activeSheet->setCellValue( 'Q' . $iter , "" );
648 $activeSheet->getStyle( 'Q'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
649
650 $activeSheet->setCellValue( 'R' . $iter , "" );
651 $activeSheet->getStyle( 'R'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
652
653 $activeSheet->setCellValue( 'S' . $iter , $grand_total_customerdeposit1 );
654 $cellformat = cellFormat( $grand_total_customerdeposit1 ) ? $negative : $default;
655 $activeSheet->getStyle( 'S'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
656
657 $activeSheet->setCellValue( 'T' . $iter , "" );
658 $activeSheet->getStyle( 'T'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
659
660 $activeSheet->setCellValue( 'U' . $iter , "" );
661 $activeSheet->getStyle( 'U'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
662
663 $activeSheet->setCellValue( 'V' . $iter , $grand_total_customerdeposit2 );
664 $cellformat = cellFormat( $grand_total_customerdeposit2 ) ? $negative : $default;
665 $activeSheet->getStyle( 'V'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
666
667 $activeSheet->setCellValue( 'W' . $iter , "" );
668 $activeSheet->getStyle( 'W'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
669
670 $activeSheet->setCellValue( 'X' . $iter , "" );
671 $activeSheet->getStyle( 'X'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
672
673 $activeSheet->setCellValue( 'Y' . $iter , $grand_total_customerdeposit3 );
674 $cellformat = cellFormat( $grand_total_customerdeposit3 ) ? $negative : $default;
675 $activeSheet->getStyle( 'Y'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
676
677 $activeSheet->setCellValue( 'Z' . $iter , "" );
678 $activeSheet->getStyle( 'Z'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
679
680 $activeSheet->setCellValue( 'AA' . $iter , "" );
681 $activeSheet->getStyle( 'AA'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
682
683 $activeSheet->setCellValue( 'AB' . $iter , $grand_total_customerdeposit4 );
684 $cellformat = cellFormat( $grand_total_customerdeposit4 ) ? $negative : $default;
685 $activeSheet->getStyle( 'AB'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
686
687 $activeSheet->setCellValue( 'AC' . $iter , $grand_total_customerremaining );
688 $cellformat = cellFormat( $grand_total_customerremaining ) ? $negative : $default;
689 $activeSheet->getStyle( 'AC'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
690
691 $activeSheet->setCellValue( 'AD' . $iter , "" );
692 $activeSheet->getStyle( 'AD'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
693
694 $activeSheet->setCellValue( 'AE' . $iter , "" );
695 $activeSheet->getStyle( 'AE'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
696
697 $activeSheet->setCellValue( 'AF' . $iter , $grand_total_munbillingamount );
698 $cellformat = cellFormat( $grand_total_munbillingamount ) ? $negative : $default;
699 $activeSheet->getStyle( 'AF'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
700
701 $activeSheet->setCellValue( 'AG' . $iter , "" );
702 $activeSheet->getStyle( 'AG'. $iter )->applyFromArray( array_merge( $borders, $align_left, $default, $lastline ) );
703
704 $activeSheet->setCellValue( 'AH' . $iter , $grand_total_mundeposit );
705 $cellformat = cellFormat( $grand_total_mundeposit ) ? $negative : $default;
706 $activeSheet->getStyle( 'AH'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
707
708 $activeSheet->setCellValue( 'AI' . $iter , $grand_total_muntotalremaining );
709 $cellformat = cellFormat( $grand_total_muntotalremaining ) ? $negative : $default;
710 $activeSheet->getStyle( 'AI'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
711
712 $activeSheet->setCellValue( 'AJ' . $iter , $grand_total_totalremaining );
713 $cellformat = cellFormat( $grand_total_totalremaining ) ? $negative : $default;
714 $activeSheet->getStyle( 'AJ'. $iter )->applyFromArray( array_merge( $borders, $align_right, $cellformat, $lastline ) ); //->getNumberFormat()->setFormatCode('_(* #,###_);_(* (#,###);_(* "0"_);_(@_)');
715
716 // headers default fit width
717 $headersFitWidth = array(
718 'G' => 8.29,
719 'I' => 7.14,
720 'K' => 9.57,
721 'L' => 8.29,
722 'M' => 11,
723 'N' => 11,
724 'P' => 7.14,
725 'S' => 7.14,
726 'V' => 8.14,
727 'Y' => 8.14,
728 'AB' => 8.14,
729 'AC' => 4.86,
730 'AF' => 7.14,
731 'AH' => 7.14,
732 'AI' => 4.86,
733 'AJ' => 14.14
734 );
735
736 // set column width, workaround since autoFit is no available in PHP Excel and setAutoSize produces a wider width than the fit size
737 foreach ( $headersFitWidth as $column => $columnWidth )
738 {
739 $cellValue = $activeSheet->getCell( $column ."". $iter )->getValue();
740 $calculateWidth = strlen( $cellValue ) + 3;
741
742 if ( $calculateWidth > $columnWidth )
743 {
744 $columnWidth = $calculateWidth;
745 }
746
747 $activeSheet->getColumnDimension( $column )->setWidth( ( int ) $columnWidth );
748 $activeSheet->getColumnDimension( $column )->setAutoSize( false );
749 }
750
751 // set print range
752 if( $iter > 38 ) {
753 $iter + 1;
754 $printRange = "A1:AK$iter";
755 $activeSheet->getPageSetup()->setPrintArea( $printRange );
756 }
757
758 /*==================================
759 * Export File
760 *==================================*/
761 $rand_num = rand(100,999);
762 $file_path_name = $root_path . "/" . $output_path . $file_name . $rand_num . ".xls";
763 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
764 $objWriter->save( $file_path_name );
765
766 /*==================================
767 * Update Estimate Status in DB
768 *==================================*/
769 // updateOutputStatus( $adb, $estimateID );
770
771 /*==================================
772 * Return
773 *==================================*/
774 $time_elapsed_secs = microtime(true) - $start;
775 echo "writeToExcel: ".$time_elapsed_secs."\n";
776 return $output_path . $file_name . $rand_num . ".xls";
777
778 } // end function
779
780 /**
781 * Clean Values
782 * @param val
783 * @return cleanval
784 */
785
786 function formatValue( $val = "" ) {
787 $cleanval = trim( $val );
788 if( is_numeric( $cleanval ) && ( !empty( $cleanval ) ) )
789 {
790 if( $cleanval < 0 )
791 {
792 // $cleanval = "(" . number_format( abs( $cleanval ) ) . ")";
793 $cleanval = $cleanval;
794 }
795 else
796 {
797 // $cleanval = number_format( $cleanval );
798 $cleanval = $cleanval;
799 }
800 }
801 if( validateDate( $cleanval ) )
802 {
803 $cleanval = date( "Y/m/d", strtotime( $cleanval ) );
804 }
805
806 if( $cleanval == "0000-00-00" )
807 {
808 $cleanval = "";
809 }
810
811 return $cleanval;
812 }
813
814 /**
815 * Clean Values
816 * @param val
817 * @return cleanval
818 */
819
820 function cellFormat( $val = "" )
821 {
822 $cleanval = trim( $val );
823 if( is_numeric( $cleanval ) && ( !empty( $cleanval ) && $cleanval < 0 ) ) {
824 return true;
825 }
826
827 return false;
828 }
829
830 /**
831 * validate date
832 * @param date
833 * @return cleanval
834 */
835
836 function validateDate( $date )
837 {
838 $d = DateTime::createFromFormat('Y-m-d', $date);
839 return $d && $d->format('Y-m-d') === $date;
840 }
841
842 /**
843 * SQL result to formatted array
844 * @param $sql_result array
845 * @return array
846 */
847
848 function sqlResultsToFormattedArray( $sql_result = array() )
849 {
850 $result_array = array();
851 foreach ( $sql_result as $k => $v )
852 {
853 foreach ( $v as $rowk => $rowv )
854 {
855 if( is_string( $rowk ) )
856 {
857 $result_array[ $k ][ $rowk ] = $rowv;
858 }
859 }
860 }
861
862 return $result_array;
863 }
864
865 /**
866 * Set export details for stoma report
867 * @param adb
868 * @param demarcation
869 * @param municipality
870 * @param customer_id
871 * @param start_date
872 * @param end_date
873 * @param current_user_id
874 * @return boolean
875 */
876
877 function setExportDetails( $adb, $demarcation = 0, $municipality = 0, $customer_id = 0, $start_date = "", $end_date = "", $current_user_id = 0 )
878 {
879
880 $ext = "";
881 $insert_array = array();
882 $order_ids = array();
883
884 if ( !empty( $demarcation ) || $demarcation != "" )
885 {
886 $ext .= " AND vtiger_jcustomer.personal_number LIKE '". $demarcation ."%'";
887 }
888
889 if ( !empty( $municipality ) && $municipality != 0 )
890 {
891 $ext .= " AND vtiger_jcustomer.municipality = $municipality";
892 }
893
894 if ( !empty( $customer_id ) || $customer_id != "" )
895 {
896 $ext .= " AND vtiger_jcustomer.jcustomerid IN ( $customer_id )";
897 }
898
899 // get orders
900
901 $orders = getOrders( $adb, $ext, $start_date, $end_date );
902 $start = microtime(true);
903 foreach( $orders as $o_key => $o_value )
904 {
905 array_push( $order_ids, $o_value[ 'order_id' ] );
906 $insert_data = array();
907
908 $insert_data[ 0 ][ "customer_cd" ] = $o_value[ 'customer_cd' ];
909 $insert_data[ 0 ][ "customer_name" ] = addslashes( $o_value[ 'customer_name' ] );
910 $insert_data[ 0 ][ "municipality_no" ] = $o_value[ 'municipality_no' ];
911 $insert_data[ 0 ][ "municipality" ] = $o_value[ 'municipality' ];
912 $insert_data[ 0 ][ "personal_number" ] = $o_value[ 'personal_number' ];
913 $insert_data[ 0 ][ "sort_date" ] = $o_value[ 'sort_date' ];
914 $insert_data[ 0 ][ "order_no" ] = $o_value[ 'order_no' ];
915 $insert_data[ 0 ][ "order_date" ] = $o_value[ 'order_date' ];
916 $insert_data[ 0 ][ "sum_expense" ] = $o_value[ 'sum_expense' ];
917 $insert_data[ 0 ][ "delivery_date" ] = $o_value[ 'delivery_date' ];
918 $insert_data[ 0 ][ "parent_order_sum_expense" ] = $o_value[ 'parent_order_sum_expense' ];
919 $insert_data[ 0 ][ "children_order_date" ] = $o_value[ 'children_order_date' ];
920 $insert_data[ 0 ][ "return_expense" ] = $o_value[ 'return_expense' ];
921 $insert_data[ 0 ][ "final_sum_expense" ] = $o_value[ 'final_sum_expense' ];
922 $insert_data[ 0 ][ "sum_individual" ] = $o_value[ 'sum_individual' ];
923 $insert_data[ 0 ][ "sum_municipality" ] = $o_value[ 'sum_municipality' ];
924 $insert_data[ 0 ][ "customer_total_remaining" ] = 0;
925 $insert_data[ 0 ][ "total_remaining" ] = 0;
926
927 // billing data
928
929 if ( $o_value[ "customer_billing" ] )
930 {
931 list( $customer_billing_id, $customer_billing_date, $customer_billing_amount ) = explode( "|", $o_value[ "customer_billing" ] );
932 $insert_data[ 0 ][ "customer_billing_date" ] = $customer_billing_date;
933 $insert_data[ 0 ][ "customer_billing_amount" ] = $customer_billing_amount;
934 }
935
936 // deposit data
937
938 $deposit_ctr = 0;
939 $deposit_iterator = 1;
940 $deposit = 0;
941
942 if ( $o_value[ "order_deposit" ] )
943 {
944 $deposits = explode( ",", $o_value[ "order_deposit" ] );
945 $deposit_ctr = count( $deposits ); // count all the deposit data in this order
946
947 foreach ( $deposits as $d_key => $d_value )
948 {
949 if ( $deposit_iterator <= 4 )
950 {
951 list( $customer_payment_kbn, $customer_deposit_date, $customer_deposit ) = explode( "|", $d_value );
952 $insert_data[ 0 ][ "customer_payment_kbn". $deposit_iterator ] = $customer_payment_kbn;
953 $insert_data[ 0 ][ "customer_deposit_date". $deposit_iterator ] = $customer_deposit_date;
954 $insert_data[ 0 ][ "customer_deposit". $deposit_iterator ] = $customer_deposit;
955 $deposit = $deposit + ( int ) $customer_deposit;
956 $deposit_iterator++;
957 }
958 else
959 {
960 $deposit = $deposit + ( int ) $d_value[ 2 ]; // if there are more than 4 order deposit, include remaining deposit data in the calculation for total deposit
961 }
962 }
963
964 if ( $deposit_iterator <= 4 && $o_value[ "balance_used" ] != "0" )
965 {
966 list( $customer_payment_kbn, $customer_deposit_date, $customer_deposit ) = explode( "|", $o_value[ "balance_used" ] );
967 $insert_data[ 0 ][ "customer_payment_kbn". $deposit_iterator ] = $customer_payment_kbn;
968 $insert_data[ 0 ][ "customer_deposit_date". $deposit_iterator ] = $customer_deposit_date;
969 $insert_data[ 0 ][ "customer_deposit". $deposit_iterator ] = $customer_deposit;
970 $deposit = $deposit + ( int ) $customer_deposit;
971 }
972
973 // if there are more than 4 order deposit data and there is also a balance used
974
975 else if ( $deposit_iterator > 4 && $o_value[ "balance_used" ] != "0" )
976 {
977 $deposit = $deposit + ( int ) $o_value[ "balance_used" ][ 2 ];
978 $deposit_ctr += 1; // increment count of all the deposit data in this order
979 }
980 }
981 else
982 {
983 if ( $o_value[ "balance_used" ] != "0" )
984 {
985 list( $customer_payment_kbn, $customer_deposit_date, $customer_deposit ) = explode( "|", $o_value[ "balance_used" ] );
986 $insert_data[ 0 ][ "customer_payment_kbn". $deposit_iterator ] = $customer_payment_kbn;
987 $insert_data[ 0 ][ "customer_deposit_date". $deposit_iterator ] = $customer_deposit_date;
988 $insert_data[ 0 ][ "customer_deposit". $deposit_iterator ] = $customer_deposit;
989 $deposit = $deposit + ( int ) $customer_deposit;
990 }
991 }
992
993 $insert_data[ 0 ][ "customer_total_remaining" ] = ( int ) $o_value[ "sum_individual" ] - $deposit;
994 $insert_data[ 0 ][ "total_remaining" ] = ( ( int ) $o_value[ "final_sum_expense" ] - $deposit ) .'|'. $deposit_ctr;
995 $insert_data[ 0 ][ "upd_user" ] = $current_user_id;
996 $insert_data[ 0 ][ "upd_date" ] = date( 'Y-m-d' );
997 $insert_data[ 0 ][ "add_date" ] = date( 'Y-m-d' );
998
999 // insert order information
1000
1001 insertData( $adb, $insert_data[ 0 ] );
1002 }
1003 $time_elapsed_secs = microtime(true) - $start;
1004 echo "setExportDetails: ".$time_elapsed_secs."\n";
1005 // insert benefit tickets
1006
1007 $order_ids = implode( ",", $order_ids );
1008
1009 $start = microtime(true);
1010 insertBenefitTickets( $adb, $ext, $start_date, $end_date, $current_user_id );
1011 $time_elapsed_secs = microtime(true) - $start;
1012 echo "insertBenefitTickets: ".$time_elapsed_secs."\n";
1013 }
1014
1015 function getOrders( $adb, $ext = "", $start_date = "", $end_date = "" )
1016 {
1017 $start = microtime(true);
1018
1019 $query = " SET SESSION group_concat_max_len = 1000000 ";
1020 $set_session = $adb->pquery( $query );
1021 $result_array = array();
1022
1023 if ( $set_session )
1024 {
1025
1026 $query = "CREATE TEMPORARY TABLE IF NOT EXISTS `deposit_total_stoma_tmp`
1027 (`billing_id` int not null,`deposit_id` int not null, `deposit_date` date, `deposit` int not null,
1028 INDEX `index1` (`billing_id`), INDEX `index2` (`deposit`))ENGINE = MEMORY;";
1029 $adb->pquery( $query, array());
1030 $query = "TRUNCATE TABLE `deposit_total_stoma_tmp`;";
1031 $adb->pquery( $query, array());
1032 $query = "INSERT INTO `deposit_total_stoma_tmp`
1033 SELECT
1034 vtiger_jorderdeposit.bill_no AS `billing_id`,
1035 vtiger_jorderdeposit.jorderdepositid AS `deposit_id`,
1036 vtiger_jorderdeposit.deposit_date AS `deposit_date`,
1037 vtiger_jorderdeposit.deposit AS `deposit`
1038 FROM vtiger_jorderdeposit
1039 LEFT JOIN vtiger_crmentity AS crm_orderdeposit ON vtiger_jorderdeposit.jorderdepositid = crm_orderdeposit.crmid
1040 WHERE crm_orderdeposit.deleted = 0
1041 AND vtiger_jorderdeposit.deposit_date BETWEEN '$start_date' AND '$end_date';";
1042
1043 if($adb->pquery( $query, array())){
1044
1045 $query = " SELECT
1046 vtiger_jorder.jorderid AS `order_id`,
1047 vtiger_jcustomer.customer_no AS `customer_cd`,
1048 vtiger_jcustomer.lastname AS `customer_name`,
1049 municipality.municipality_no AS `municipality_no`,
1050 municipality.name AS `municipality`,
1051 vtiger_jcustomer.personal_number AS `personal_number`,
1052 vtiger_jorder.delivery_date AS `sort_date`,
1053 IF( parent_order.parent_order_id is not null, parent_order.parent_order_no, vtiger_jorder.order_no ) AS `order_no`,
1054 IF( parent_order.parent_order_id is not null, parent_order.parent_order_date, vtiger_jorder.order_date ) AS `order_date`,
1055 vtiger_jorder.sum_expense AS `sum_expense`,
1056 vtiger_jorder.delivery_date AS `delivery_date`,
1057 IF( parent_order.parent_order_id is not null, parent_order.parent_order_sum_expense, vtiger_jorder.sum_expense ) AS `parent_order_sum_expense`,
1058 IF( parent_order.parent_order_id is not null, vtiger_jorder.order_date, '' ) AS `children_order_date`,
1059 IF( parent_order.parent_order_id is not null, vtiger_jorder.sum_expense - parent_order.parent_order_sum_expense, 0 ) AS `return_expense`,
1060 vtiger_jorder.sum_expense AS `final_sum_expense`,
1061 vtiger_jorder.sum_individual + vtiger_jorder.balance_used AS `sum_individual`,
1062 vtiger_jorder.sum_municipality AS `sum_municipality`,
1063 IF( parent_order.parent_order_id is not null, parent_order.parent_order_billing, order_billing_deposit.order_billing ) AS `customer_billing`,
1064 GROUP_CONCAT( CONCAT( IF( order_billing_deposit.deposit < 0, '返金', IF( vtiger_jorder.jorder_payment = '0', '郵便振替', IF( vtiger_jorder.jorder_payment = '1', '銀行振り込ã¿', IF( vtiger_jorder.jorder_payment = '2', '代引ã', IF( vtiger_jorder.jorder_payment = '3', 'å…¬è²»è² æ‹…', IF( vtiger_jorder.jorder_payment = '4', '集金', '' ) ) ) ) ) ), '|', order_billing_deposit.deposit_date, '|', order_billing_deposit.deposit ) ORDER BY order_billing_deposit.deposit_date, order_billing_deposit.deposit_id separator ',' ) AS `order_deposit`,
1065 IF( parent_order.parent_order_id is not null, parent_order.parent_order_balance_used, IF( vtiger_jorder.balance_used > 0, CONCAT( '充当', '|', vtiger_jorder.order_date, '|', vtiger_jorder.balance_used ), 0 ) ) AS `balance_used`
1066 FROM vtiger_jorder
1067 LEFT JOIN vtiger_crmentity AS crm_order ON vtiger_jorder.jorderid = crm_order.crmid
1068 LEFT JOIN (
1069 SELECT
1070 vtiger_jbill.order_no AS `order_id`,
1071 CONCAT( vtiger_jbill.jbillid, '|', vtiger_jbill.billing_date, '|', vtiger_jbill.billing_payment ) AS `order_billing`,
1072 order_deposit.deposit_id AS `deposit_id`,
1073 order_deposit.deposit_date AS `deposit_date`,
1074 order_deposit.deposit AS `deposit`,
1075 vtiger_jbill.billing_date AS `billing_date`
1076 FROM vtiger_jbill
1077 LEFT JOIN vtiger_crmentity AS crm_bill ON vtiger_jbill.jbillid = crm_bill.crmid
1078 LEFT JOIN `deposit_total_stoma_tmp` AS order_deposit ON vtiger_jbill.jbillid = order_deposit.billing_id
1079 WHERE crm_bill.deleted = 0
1080 AND vtiger_jbill.billing_date BETWEEN '$start_date' AND '$end_date'
1081 ) AS order_billing_deposit ON vtiger_jorder.jorderid = order_billing_deposit.order_id
1082 LEFT JOIN (
1083 SELECT
1084 vtiger_jorder.jorderid AS `parent_order_id`,
1085 vtiger_jorder.order_no AS `parent_order_no`,
1086 vtiger_jorder.order_date AS `parent_order_date`,
1087 ROUND( SUM( IF( commodity.tax_kbn = '1', ( vtiger_jorderdetail.unit_pice * vtiger_jorderdetail.quantity ) * ( ( vtiger_jorder.tax_rate / 100 ) + 1 ), vtiger_jorderdetail.unit_pice * vtiger_jorderdetail.quantity ) ) ) AS `parent_order_sum_expense`,
1088 IF( vtiger_jorder.balance_used > 0, CONCAT( '充当', '|', vtiger_jorder.order_date, '|', vtiger_jorder.balance_used ), 0 ) AS `parent_order_balance_used`,
1089 order_billing.order_billing AS `parent_order_billing`
1090 FROM vtiger_jorder
1091 LEFT JOIN vtiger_crmentity AS crm_order ON vtiger_jorder.jorderid = crm_order.crmid
1092 LEFT JOIN (
1093 SELECT
1094 vtiger_jbill.order_no AS `order_id`,
1095 CONCAT( vtiger_jbill.jbillid, '|', vtiger_jbill.billing_date, '|', vtiger_jbill.billing_payment ) AS `order_billing`
1096 FROM vtiger_jbill
1097 LEFT JOIN vtiger_crmentity AS crm_bill ON vtiger_jbill.jbillid = crm_bill.crmid
1098 WHERE crm_bill.deleted = 0
1099 GROUP BY vtiger_jbill.order_no
1100 ) AS order_billing ON vtiger_jorder.jorderid = order_billing.order_id
1101 LEFT JOIN vtiger_jorderdetail ON vtiger_jorder.jorderid = vtiger_jorderdetail.order_no
1102 LEFT JOIN vtiger_crmentity AS crm_orderdetail ON vtiger_jorderdetail.jorderdetailid = crm_orderdetail.crmid
1103 LEFT JOIN (
1104 SELECT
1105 vtiger_jcommodity.jcommodityid AS `commodity_id`,
1106 vtiger_jcommodity.tax_kbn AS `tax_kbn`
1107 FROM vtiger_jcommodity
1108 LEFT JOIN vtiger_crmentity AS crm_commodity ON vtiger_jcommodity.jcommodityid = crm_commodity.crmid
1109 WHERE crm_commodity.deleted = 0
1110 ) AS commodity ON vtiger_jorderdetail.commodity_no = commodity.commodity_id
1111 WHERE crm_order.deleted = 0
1112 AND vtiger_jorder.jorder_cancel = 2
1113 GROUP BY vtiger_jorder.jorderid
1114 ) AS parent_order ON vtiger_jorder.parent_order_id = parent_order.parent_order_id
1115 LEFT JOIN vtiger_jcustomer ON vtiger_jorder.customer_no = vtiger_jcustomer.jcustomerid
1116 LEFT JOIN vtiger_crmentity AS crm_customer ON vtiger_jcustomer.jcustomerid = crm_customer.crmid
1117 LEFT JOIN (
1118 SELECT
1119 vtiger_jmunicipality.jmunicipalityid,
1120 vtiger_jmunicipality.municipality_no,
1121 vtiger_jmunicipality.name
1122 FROM vtiger_jmunicipality
1123 LEFT JOIN vtiger_crmentity AS crm_municipality ON vtiger_jmunicipality.jmunicipalityid = crm_municipality.crmid
1124 WHERE crm_municipality.deleted = 0
1125 ) AS municipality ON vtiger_jcustomer.municipality = municipality.jmunicipalityid
1126 WHERE crm_order.deleted = 0
1127 AND crm_customer.deleted = 0
1128 AND vtiger_jorder.jorder_cancel = 0
1129 $ext
1130 AND vtiger_jorder.delivery_date BETWEEN '$start_date' AND '$end_date'
1131 GROUP BY vtiger_jorder.jorderid
1132 ORDER BY vtiger_jorder.jorderid ";
1133
1134 $result = $adb->pquery( $query, array() );
1135
1136 $time_elapsed_secs = microtime(true) - $start;
1137 echo "getOrders: ".$time_elapsed_secs."\n";
1138
1139 return sqlResultsToFormattedArray( $result );
1140 } else {
1141 return sqlResultsToFormattedArray(array());
1142 }
1143 }
1144
1145 return $result_array;
1146 }
1147
1148 /**
1149 * insert Benefit Tickets
1150 * @param adb
1151 * @param start_date
1152 * @param end_date
1153 * @param order_ids
1154 * @param current_user_id
1155 * @return boolean
1156 */
1157
1158 function insertBenefitTickets( $adb, $ext = "", $start_date = "", $end_date = "", $current_user_id = 0 )
1159 {
1160 $query = " SET SESSION group_concat_max_len = 1000000 ";
1161 $set_session = $adb->pquery( $query );
1162 $result = false;
1163
1164 if ( $set_session )
1165 {
1166 $query = " INSERT INTO stoma_report
1167 ( `sort_date`, `customer_cd`, `customer_name`, `municipality_no`, `municipality`, `personal_number`, `municipality_billing_date`, `benefit_ticket_name`, `municipality_billing_amount`, `municipality_deposit_date`, `municipality_deposit`, `municipality_total_remaining`, `total_remaining`, `upd_user`, `upd_date`, `add_date` )
1168 SELECT
1169 vtiger_jbenefitticket.billing_date,
1170 vtiger_jcustomer.customer_no,
1171 vtiger_jcustomer.lastname,
1172 municipality.municipality_no,
1173 municipality.name,
1174 vtiger_jcustomer.personal_number,
1175 vtiger_jbenefitticket.billing_date,
1176 IF( MONTH( vtiger_jbenefitticket.month_start ) = MONTH( vtiger_jbenefitticket.month_end ), CONCAT( MONTH( vtiger_jbenefitticket.month_start ), '月分' ), CONCAT( MONTH( vtiger_jbenefitticket.month_start ), '月-', MONTH( vtiger_jbenefitticket.month_end ), '月分' ) ),
1177 vtiger_jbenefitticket.billing_payment,
1178 IF( ticketdeposit.deposit_date BETWEEN '$start_date' AND '$end_date', ticketdeposit.deposit_date, '' ) AS deposit_date,
1179 IF( ticketdeposit.deposit_date BETWEEN '$start_date' AND '$end_date', ticketdeposit.deposit, '' ) AS deposit,
1180 vtiger_jbenefitticket.billing_payment - ticketdeposit.deposit,
1181 0 - ticketdeposit.deposit,
1182 $current_user_id,
1183 CURDATE(),
1184 CURDATE()
1185 FROM vtiger_jbenefitticket
1186 LEFT JOIN vtiger_crmentity AS crm_benefitticket ON vtiger_jbenefitticket.jbenefitticketid = crm_benefitticket.crmid
1187 LEFT JOIN (
1188 SELECT
1189 vtiger_jticketdeposit.benefit_no,
1190 vtiger_jticketdeposit.deposit_date,
1191 vtiger_jticketdeposit.deposit
1192 FROM vtiger_jticketdeposit
1193 LEFT JOIN vtiger_crmentity AS crm_ticketdeposit ON vtiger_jticketdeposit.jticketdepositid = crm_ticketdeposit.crmid
1194 WHERE crm_ticketdeposit.deleted = 0
1195 ) AS ticketdeposit ON vtiger_jbenefitticket.jbenefitticketid = ticketdeposit.benefit_no
1196 LEFT JOIN vtiger_jcustomer ON vtiger_jbenefitticket.customer_no = vtiger_jcustomer.jcustomerid
1197 LEFT JOIN vtiger_crmentity AS crm_customer ON vtiger_jcustomer.jcustomerid = crm_customer.crmid
1198 LEFT JOIN (
1199 SELECT
1200 vtiger_jmunicipality.jmunicipalityid,
1201 vtiger_jmunicipality.municipality_no,
1202 vtiger_jmunicipality.name
1203 FROM
1204 vtiger_jmunicipality
1205 LEFT JOIN vtiger_crmentity AS crm_municipality ON vtiger_jmunicipality.jmunicipalityid = crm_municipality.crmid
1206 WHERE crm_municipality.deleted = 0
1207 ) AS municipality ON vtiger_jcustomer.municipality = municipality.jmunicipalityid
1208 WHERE crm_benefitticket.deleted = 0
1209 AND crm_customer.deleted = 0
1210 AND vtiger_jcustomer.municipality IS NOT NULL
1211 AND (
1212 vtiger_jbenefitticket.billing_date BETWEEN '$start_date' AND '$end_date'
1213 OR
1214 ticketdeposit.deposit_date BETWEEN '$start_date' AND '$end_date'
1215 )
1216 AND vtiger_jbenefitticket.billing_date IS NOT NULL
1217 AND vtiger_jbenefitticket.billing_date != '0000-00-00'
1218 $ext
1219 GROUP BY vtiger_jbenefitticket.jbenefitticketid
1220 ORDER BY vtiger_jbenefitticket.billing_date, vtiger_jbenefitticket.jbenefitticketid ";
1221
1222 return $adb->pquery( $query );
1223 }
1224
1225 return $result;
1226 }
1227
1228 /**
1229 * Truncate stoma report table
1230 * @param adb
1231 * @return boolean
1232 */
1233
1234 function truncateStomaReport( $adb ){
1235 $sql = " TRUNCATE TABLE `stoma_report` ";
1236 return $adb->pquery( $sql );
1237 }
1238
1239 /**
1240 * Insert Data
1241 * @param adb
1242 * @param insdata
1243 * @return boolean
1244 */
1245
1246 function insertData( $adb, $insdata ){
1247 $columns = implode( ", ", array_keys( $insdata ) );
1248 $values = implode( "', '", array_values( $insdata ) );
1249 $sql = " INSERT INTO `stoma_report` ( $columns ) VALUES ( '$values' ) ";
1250 return $adb->pquery( $sql );
1251 }
1252
1253 /**
1254 * Get stoma report details
1255 * @param adb
1256 * @return array
1257 */
1258
1259 function getStomaReport( $adb )
1260 {
1261 $query = " SELECT *
1262 FROM stoma_report AS sr
1263 ORDER BY sr.municipality_no, sr.customer_cd, sr.order_date IS NULL, sr.order_date, sr.municipality_billing_date, sr.order_no ";
1264
1265 $result = $adb->pquery( $query );
1266 $result = sqlResultsToFormattedArray( $result );
1267 return $result;
1268 }
1269
1270?>