list($d, $m, $y) = explode(".", $date);
$y += 2000;
$time = mktime(0, 0, 0, $m, $d, $y);
foreach ($v['suborder_list'] as $v1) {
$d = $v1['usluga'] . " " . $v1['order_id'];
$v['note'] .= $v['note'] ? "\n" . $d : $d;
}
$sheet->setCellValue('A' . $col_num, $v['order_id'])->setCellValue('B' . $col_num, iconv_utf($v['object_name']))->setCellValue('C' . $col_num, PHPExcel_Shared_Date::PHPToExcel($time))->setCellValue('D' . $col_num, iconv_utf(@$v['fio'][0]))->setCellValue('E' . $col_num, iconv_utf(@$v['pasport'][0]))->setCellValue('F' . $col_num, iconv_utf(@$v['birthday'][0]))->setCellValue('G' . $col_num, iconv_utf(@$v['birthday_city'][0]))->setCellValue('H' . $col_num, iconv_utf(@$v['tphone'][0]))->setCellValue('I' . $col_num, iconv_utf($v['korpus']))->setCellValue('J' . $col_num, iconv_utf($v['extra']))->setCellValue('K' . $col_num, iconv_utf($v['note']));
$sheet->getStyle('C' . $col_num)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
if (@count($v['fio']) > 1) {
foreach ($v['fio'] as $kk => $vv) {
if (!$kk) {
continue;
}
$col_num++;
$sheet->setCellValue('D' . $col_num, iconv_utf(@$v['fio'][$kk]))->setCellValue('E' . $col_num, iconv_utf(@$v['pasport'][$kk]))->setCellValue('F' . $col_num, iconv_utf(@$v['birthday'][$kk]))->setCellValue('G' . $col_num, iconv_utf(@$v['birthday_city'][$kk]))->setCellValue('H' . $col_num, iconv_utf(@$v['tphone'][$kk]));
}
}
}
// Rename worksheet
$sheet->setTitle('Сводка для встреч');
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $filename . '"');
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');
$summa1 = '';
$summa2 = $v['summa'];
} else {
$summa2 = '';
$summa1 = $v['summa'];
}
$sheet->setCellValue('A' . ($k + 2), PHPExcel_Shared_Date::PHPToExcel($time))->setCellValue('B' . ($k + 2), $summa1)->setCellValue('C' . ($k + 2), $summa2)->setCellValue('D' . ($k + 2), iconv_utf($v['name']));
$sheet->getStyle('A' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
if ($summa1) {
$sheet->getStyle('B' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
}
if ($summa2) {
$sheet->getStyle('C' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
}
} else {
$sheet->setCellValue('A' . ($k + 2), $v['order_id'])->setCellValue('B' . ($k + 2), PHPExcel_Shared_Date::PHPToExcel($time))->setCellValue('C' . ($k + 2), iconv_utf($v['user_name']))->setCellValue('D' . ($k + 2), iconv_utf($v['object_name']))->setCellValue('E' . ($k + 2), iconv_utf($v['client']))->setCellValue('F' . ($k + 2), iconv_utf($v['summa']));
$sheet->getStyle('B' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
$sheet->getStyle('F' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
}
}
if (@$replace['inventory']) {
$sheet->setCellValue('A' . ($k + 4), 'Приход: ')->setCellValue('B' . ($k + 4), '=SUM(B2:B' . ($k + 2) . ')')->setCellValue('A' . ($k + 5), 'Расход: ')->setCellValue('B' . ($k + 5), '=SUM(C2:C' . ($k + 2) . ')')->setCellValue('A' . ($k + 6), 'Баланс: ')->setCellValue('B' . ($k + 6), '=SUM(B' . ($k + 4) . ':B' . ($k + 5) . ')');
} else {
$sheet->setCellValue('E' . ($k + 4), 'Сумма: ')->setCellValue('F' . ($k + 4), '=SUM(F2:F' . ($k + 2) . ')');
}
// Rename worksheet
if (@$replace['inventory']) {
$sheet->setTitle('Учет средств');
} else {
$sheet->setTitle('Платежи');
}
$sheet->getColumnDimension('Q')->setAutoSize(TRUE);
$sheet->getColumnDimension('R')->setAutoSize(TRUE);
$sheet->getColumnDimension('S')->setAutoSize(TRUE);
$sheet->getColumnDimension('T')->setAutoSize(TRUE);
// Add some data
$sheet->setCellValue('A1', 'Заявка')->setCellValue('B1', 'Офис')->setCellValue('C1', 'Сотрудник')->setCellValue('D1', 'Город')->setCellValue('E1', 'Объект размещения')->setCellValue('F1', 'Категория номера')->setCellValue('G1', 'Дата заезда')->setCellValue('H1', 'Заказчик')->setCellValue('I1', 'Поставщик')->setCellValue('J1', 'Счет поставщика')->setCellValue('K1', 'Сумма к оплате')->setCellValue('L1', 'Сумма оплаты поставщику')->setCellValue('M1', 'Сумма нетто')->setCellValue('N1', 'Баланс')->setCellValue('O1', 'Долг')->setCellValue('P1', 'Номероночи')->setCellValue('Q1', 'Примечание')->setCellValue('R1', 'Аннуляция')->setCellValue('S1', 'Дата оплаты');
foreach ($list as $k => $v) {
$date = $v['date_from'];
list($d, $m, $y) = explode(".", $date);
$y += 2000;
$time = mktime(0, 0, 0, $m, $d, $y);
$date_pay = $v['date_pay'];
list($d, $m, $y) = explode(".", $date);
$y += 2000;
$time1 = mktime(0, 0, 0, $m, $d, $y);
$sheet->setCellValue('A' . ($k + 2), $v['order_id'])->setCellValue('B' . ($k + 2), iconv_utf($v['office']))->setCellValue('C' . ($k + 2), iconv_utf($v['user_name']))->setCellValue('D' . ($k + 2), iconv_utf($v['city_name']))->setCellValue('E' . ($k + 2), iconv_utf($v['object_name']))->setCellValue('F' . ($k + 2), iconv_utf($v['suite']))->setCellValue('G' . ($k + 2), PHPExcel_Shared_Date::PHPToExcel($time))->setCellValue('H' . ($k + 2), iconv_utf($v['client']))->setCellValue('I' . ($k + 2), iconv_utf($v['provider']))->setCellValue('J' . ($k + 2), iconv_utf($v['provider_bill']))->setCellValue('K' . ($k + 2), iconv_utf($v['inall']))->setCellValue('L' . ($k + 2), iconv_utf($v['provider_sum']))->setCellValue('M' . ($k + 2), iconv_utf($v['net']))->setCellValue('N' . ($k + 2), iconv_utf($v['balance']))->setCellValue('O' . ($k + 2), iconv_utf($v['debt']))->setCellValue('P' . ($k + 2), iconv_utf($v['nomeronochi']))->setCellValue('Q' . ($k + 2), iconv_utf($v['note']))->setCellValue('R' . ($k + 2), iconv_utf($v['annul']))->setCellValue('S' . ($k + 2), PHPExcel_Shared_Date::PHPToExcel($time1));
$sheet->getStyle('G' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
$sheet->getStyle('S' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
$sheet->getStyle('K' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$sheet->getStyle('L' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$sheet->getStyle('M' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$sheet->getStyle('N' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$sheet->getStyle('O' . ($k + 2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
}
$sheet->setCellValue('A' . ($k + 4), 'Общая сумма оплаты: ')->setCellValue('B' . ($k + 4), '=SUM(K2:K' . ($k + 2) . ')')->setCellValue('A' . ($k + 5), 'Сумма оплаты поставщику: ')->setCellValue('B' . ($k + 5), '=SUM(L2:L' . ($k + 2) . ')')->setCellValue('A' . ($k + 6), 'Баланс: ')->setCellValue('B' . ($k + 6), '=SUM(N2:N' . ($k + 2) . ')');
// Rename worksheet
$sheet->setTitle('Отчет');
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
