/**
* @return \PHPExcel
*/
protected function mockWorkbook()
{
$workbook = new \PHPExcel();
$workbook->disconnectWorksheets();
$workbook->getProperties()->setTitle('mocked');
$sheet = new \PHPExcel_Worksheet($workbook);
$sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]);
$workbook->addSheet($sheet);
$sheet = new \PHPExcel_Worksheet($workbook);
$sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]);
$workbook->addSheet($sheet);
$workbook->setActiveSheetIndex(0);
return $workbook;
}
function catalog_to_xlsx()
{
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Boy Gruv")->setLastModifiedBy("Boy Gruv")->setTitle("PHPExcel Document")->setSubject("PHPExcel Document")->setDescription("Document for PHPExcel, generated using PHP classes.")->setKeywords("office PHPExcel php")->setCategory("Result file");
// Заголовок страницы Categories
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'category_id')->setCellValue('B1', 'parent_id')->setCellValue('C1', 'name(en)')->setCellValue('D1', 'name(ru)')->setCellValue('E1', 'top')->setCellValue('F1', 'columns')->setCellValue('G1', 'sort_order')->setCellValue('H1', 'image_name')->setCellValue('I1', 'date_added')->setCellValue('J1', 'date_modified')->setCellValue('K1', 'seo_keyword')->setCellValue('L1', 'description(en)')->setCellValue('M1', 'description(ru)')->setCellValue('N1', 'meta_title(en)')->setCellValue('O1', 'meta_title(ru)')->setCellValue('P1', 'meta_description(en)')->setCellValue('Q1', 'meta_description(ru)')->setCellValue('R1', 'meta_keywords(en)')->setCellValue('S1', 'meta_keywords(ru)')->setCellValue('T1', 'store_ids')->setCellValue('U1', 'layout')->setCellValue('V1', 'status');
// Заполняем данные
$i = 2;
$arr = SQL_get_catalog();
for ($j = 0; $j < count($arr); $j++) {
$i = $j + 2;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $i, $arr[$j]["id_cat"] + 100)->setCellValue('B' . $i, $arr[$j]["parent_id"] + 100)->setCellValue('D' . $i, $arr[$j]["name"])->setCellValue('E' . $i, 'true')->setCellValue('F' . $i, '1')->setCellValue('G' . $i, '0')->setCellValue('I' . $i, date('Y-m-d H:i:s'))->setCellValue('J' . $i, date('Y-m-d H:i:s'))->setCellValue('K' . $i, '')->setCellValue('M' . $i, '')->setCellValue('T' . $i, '0')->setCellValue('V' . $i, 'true');
}
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Categories');
//Добавляем новую страницу
$MyWorkSheet = new PHPExcel_Worksheet($objPHPExcel, 'CategoryFilters');
$objPHPExcel->addSheet($MyWorkSheet, 1);
// Заголовок страницы CategoryFilters
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A1', 'category_id')->setCellValue('B1', 'filter_group')->setCellValue('C1', 'filter');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 2007 file
$callStartTime = microtime(true);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
//echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
$ret_str = date('H:i:s') . " File written to " . str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME));
return $ret_str;
}
protected function _write(array $data){
//Umwandeln in numerischen Array
foreach($data AS $k => $dataEntry){
if(
$this->_charset != 'UTF-8'
){
//Umwandeln des Zeichensatzes
foreach($dataEntry AS $dataEntryK => $dataEntryV){
$dataEntry[$dataEntryK] = iconv('UTF-8', $this->_charset.'//TRANSLIT', $dataEntryV);
}
}
$data[$k] = array_values($dataEntry);
}
if(
$this->_useFirstRowAsTitles()
){
$data = array_merge(array($this->_titles), $data);
}
$this->_phpExcel = new PHPExcel();
$this->_phpExcel->addSheet();
$this->_phpExcel->getActiveSheet()->fromArray($data);
$this->_getWriter()->save($this->_file);
}
/**
* @param string $text
*
* @return Cell
*/
protected function mockCell($text = 'text', $settings = false)
{
$workbook = new \PHPExcel();
$workbook->disconnectWorksheets();
$worksheet = new \PHPExcel_Worksheet($workbook);
$workbook->addSheet($worksheet);
$cell = $worksheet->setCellValue('A1', $pValue = $text, $returnCell = true);
$settings = $settings ?: new ParserSettings();
$cell = new Cell($cell, 1, $settings);
return $cell;
}
/**
* (non-PHPdoc)
* @see \scipper\Datatransfer\TransferService::generateEmptyDocument()
*/
public function generateDocument(Map $map)
{
if (!class_exists("PHPExcel")) {
throw new GenerationException("dependency 'PHPExcel' not found");
}
$excel = new \PHPExcel();
$excel->removeSheetByIndex(0);
$excel->getProperties()->setCreator($map->getCreator());
$excel->getProperties()->setTitle($map->getTitle());
$protectedStyle = new \PHPExcel_Style();
$protectedStyle->applyFromArray(array("fill" => array("type" => \PHPExcel_Style_Fill::FILL_SOLID, "color" => array("argb" => "55CCCCCC")), "borders" => array("bottom" => array("style" => \PHPExcel_Style_Border::BORDER_THIN), "right" => array("style" => \PHPExcel_Style_Border::BORDER_MEDIUM))));
$i = 0;
foreach ($map->getSheets() as $sheet) {
$active = $excel->addSheet(new \PHPExcel_Worksheet(NULL, $sheet->getTitle()), $i);
$active->getProtection()->setSheet(true);
$active->getStyle("A1:Z30")->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
foreach ($sheet->getCells() as $cell) {
//Convert content to list format ist necessary
if ($cell->getType() == "select") {
$dataValidation = $active->getCell($cell->getCoord())->getDataValidation();
$dataValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST);
$dataValidation->setAllowBlank(false);
$dataValidation->setShowInputMessage(true);
$dataValidation->setShowDropDown(true);
$dataValidation->setFormula1($cell->getContent());
} else {
$active->setCellValue($cell->getCoord(), $cell->getValue());
}
//Add protection is necessary
if ($cell->isProtected()) {
$active->protectCells($cell->getCoord(), "123");
$active->setSharedStyle($protectedStyle, $cell->getCoord());
// } elseif(!$cell->isProtected() && $active->getProtection()->isProtectionEnabled()) {
// $active->unprotectCells($cell->getCoord());
}
$active->getColumnDimension($cell->getX())->setAutoSize(true);
if (!$cell->isVisible()) {
$active->getColumnDimension($cell->getX())->setVisible(false);
}
}
$i++;
}
$excel->setActiveSheetIndex(0);
$writer = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$filename = $this->documentRoot . $excel->getProperties()->getTitle() . ".xlsx";
$writer->save($filename);
return $filename;
}
/**
*
* @param PHPExcel_Worksheet $hoja
* @param int $pk representa el índice de la columna en el archivo de excel que está asociada con la llave primaria de la tabla
* @return string rerpesenta el nombre la ruta del archivo creado. Si no se pudo crear el archivo se regresa otra cosa :p
*/
function prepararArchivo($hoja, $pk = false, $incluirPrimeraFila = false)
{
$objetoExcel = new PHPExcel();
$hojaInsertar = $objetoExcel->getSheet(0);
$hojaInsertar->setTitle('Insertar');
if ($objetoExcel->getSheetCount() > 1) {
$hojaActualizar = $objetoExcel->getSheet(1);
$hojaActualizar->setTitle('Actualizar');
} else {
$hojaActualizar = new PHPExcel_Worksheet();
$hojaActualizar->setTitle('Actualizar');
$objetoExcel->addSheet($hojaActualizar);
}
$rango = $hoja->calculateWorksheetDataDimension();
if (!$incluirPrimeraFila) {
$rango[1] = '2';
}
$contenidoExcel = $hoja->rangeToArray($rango);
$datos = array();
$datos['insertar'] = array();
$datos['actualizar'] = array();
if ($pk) {
$db = new DbConnection();
$db->abrirConexion();
$llavePrimaria = $_SESSION['pk'];
foreach ($contenidoExcel as $fila) {
$existe = $db->existeRegistro($_SESSION['tabla'], $llavePrimaria, $fila[$pk]);
if ($existe) {
$datos['actualizar'][] = $fila;
} else {
$datos['insertar'][] = $fila;
}
}
$db->cerrarConexion();
} else {
foreach ($contenidoExcel as $fila) {
$datos['insertar'][] = $fila;
}
}
$hojaInsertar->fromArray($datos['insertar'], null, 'A1', true);
$hojaActualizar->fromArray($datos['actualizar'], null, 'A1', true);
$escritorExcel = PHPExcel_IOFactory::createWriter($objetoExcel, 'Excel2007');
$escritorExcel->save('excelTmp/tmp_import_upload.xlsx');
return 'excelTmp/tmp_import_upload.xlsx';
}
private function addAnalysis(\PHPExcel $ea)
{
$ews2 = new \PHPExcel_Worksheet($ea, 'Summary');
$ea->addSheet($ews2, 0);
$ews2->setTitle('Summary');
$ews2->setCellValue('a1', 'Lakers 2013-2014 Season');
$style = array('font' => array('bold' => true, 'size' => 20), 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
$ews2->mergeCells('a1:b1');
$ews2->getStyle('a1')->applyFromArray($style);
$ews2->getColumnDimension('a')->setAutoSize(true);
$ews2->setCellValue('a2', 'Win:');
$ews2->setCellValue('a3', 'Loss:');
$ews2->setCellValue('a4', '%:');
$ews2->setCellValue('b2', '=COUNTIF(Data!G2:G91, "W")-COUNTIF(Data!G2:G9, "W")');
$ews2->setCellValue('b3', '=COUNTIF(Data!G2:G91, "L")-COUNTIF(Data!G2:G9, "L")');
$ews2->setCellValue('b4', '=b2/(b2+b3)');
//$ews2->getStyle('b4')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
return $ews2;
}
function excel_writer($row_data_excel)
{
$objPHPEXCEL = new PHPExcel();
$row_len = count($row_data_excel);
$row_count = 0;
$myWorkSheet = new PHPExcel_WorkSheet($objPHPEXCEL, "班級");
$objPHPEXCEL->addSheet($myWorkSheet, 0);
$objPHPEXCEL->getSheet(0)->setCellValueByColumnAndRow(0, 1, "班級");
$objPHPEXCEL->getSheet(0)->getColumnDimension('A')->setWidth(12);
$column = 2;
while ($row_count < $row_len) {
$objPHPEXCEL->getSheet(0)->setCellValueByColumnAndRow(0, $column, $row_data_excel[$row_count]["class"]);
$row_count++;
$column++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPEXCEL, 'Excel5');
$objWriter->save("/var/www/sports/67/admin/excel/class.xls");
$objPHPEXCEL->disconnectWorksheets();
unset($objPHPEXCEL);
header("Location: http://dpo.nttu.edu.tw/sports/67/admin/excel/class.xls");
}
/**
* 生成多个sheet的excel
*/
public function exportSheetExcel($listArr1 , $listArr2 , $headArr1 , $headArr2){
//if(!$listArr || !$headArr) return false;
set_time_limit(0);
// 关闭YII的自动加载功能，改用手动加载，否则会出错，PHPExcel有自己的自动加载功能
Yii::$enableIncludePath=false;
/*引入PHPExcel.php文件*/
Yii::import('application.extensions.PHPExcel.PHPExcel', 1);
$obj_phpexcel = new PHPExcel();
$obj_phpexcel->setActiveSheetIndex(0); //设置第一个工作表为活动工作表
$obj_phpexcel->getActiveSheet()->setTitle('orderhead'); //设置工作表名称
$objActSheet = $obj_phpexcel->getActiveSheet();
//设置字体
$objActSheet->getDefaultStyle()->getFont()->setName('微软雅黑');
//设置字体大小
$objActSheet->getDefaultStyle()->getFont()->setSize(9);
//设置自动行高
$objActSheet->getDefaultRowDimension()->setRowHeight(15);
$key = 0;
$keyArr = array();
foreach($headArr1 as $v){
$index = PHPExcel_Cell::stringFromColumnIndex($key);
$keyArr[] = $index;
//设置页头填充色
$objStyle = $objActSheet ->getStyle($index.'1');
$objFill = $objStyle->getFill();
$objFill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objFill->getStartColor()->setARGB('#FFFF00');
//设置页头边框色
$objBorder = $objStyle->getBorders();
$objBorder->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objBorder->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objBorder->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objBorder->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//设置自动列宽
$objActSheet->getColumnDimension($index)->setAutoSize(true);
//设置对其方式
$objAlign = $objStyle->getAlignment();
$objAlign->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objAlign->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$obj_phpexcel->setActiveSheetIndex(0)->setCellValue($index.'1' , $v);
$key++;
}
$keyWordArr = CDict::$title_key_word;
$keyWordArr2 = CDict::$title_hscode_word;
$key2 = 2;
foreach($listArr1 as $l_key =>$info){ //行写入
$key3 = 0;
foreach($info as $c_key=>$value){// 列写入
$span = $keyArr[$key3];
// $objStyle = $objActSheet ->getStyle($span.$key2);
// $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
//设置单元格避免大数字科学计数显示
// if(in_array($c_key, array('ORDERCOMMITTIME','PAY_TIME','USER_ID','IDENTIFY_CODE'))){
// $objStyle = $objActSheet ->getStyle($span.$key2);
// $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
// }
//$objActSheet->setCellValue($span.$key2, $value);
if(is_numeric($value) && $c_key != 'TRX_SERIAL_NO' && $c_key!='IDENTIFY_CODE' && $c_key!='USER_ID' && $c_key != 'GOODNO' && $c_key != 'HSCODE' && $c_key != 'RECEIVEZIPCODE' && $c_key != 'RECZIP' && $c_key != 'MERCHANTORDERID'){
$objStyle = $objActSheet ->getStyle($span.$key2);
if(strpos($value,'.')){
$objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
}else{
$objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
}
$objActSheet->setCellValue($span.$key2,$value);
}else{
$objActSheet->setCellValueExplicit($span.$key2,$value,PHPExcel_Cell_DataType::TYPE_STRING);
}
//$objActSheet->setCellValueExplicit($span.$key2,$value,PHPExcel_Cell_DataType::TYPE_STRING);
$key3++;
}
$key2++;
}
//创建第二个工作表
$newWorkSheet = new PHPExcel_Worksheet($obj_phpexcel, 'orderlist'); //创建一个工作表
$obj_phpexcel->addSheet($newWorkSheet); //插入工作表
$obj_phpexcel->setActiveSheetIndex(1); //切换到新创建的工作表
$objActSheet1 = $obj_phpexcel->getActiveSheet();
//设置字体
$objActSheet1->getDefaultStyle()->getFont()->setName('微软雅黑');
//设置字体大小
$objActSheet1->getDefaultStyle()->getFont()->setSize(9);
//设置自动行高
$objActSheet1->getDefaultRowDimension()->setRowHeight(15);
$key = 0;
$keyArr = array();
foreach($headArr2 as $v){
$index = PHPExcel_Cell::stringFromColumnIndex($key);
$keyArr[] = $index;
//设置页头填充色
$objStyle = $objActSheet1 ->getStyle($index.'1');
$objFill = $objStyle->getFill();
$objFill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objFill->getStartColor()->setARGB('#FFFF00');
//设置页头边框色
$objBorder = $objStyle->getBorders();
$objBorder->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objBorder->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objBorder->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objBorder->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//设置自动列宽
$objActSheet1->getColumnDimension($index)->setAutoSize(true);
//设置对其方式
$objAlign = $objStyle->getAlignment();
$objAlign->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objAlign->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$obj_phpexcel->setActiveSheetIndex(1)->setCellValue($index.'1' , $v);
$key++;
}
$key2 = 2;
foreach($listArr2 as $l_key =>$info){ //行写入
$key3 = 0;
foreach($info as $c_key=>$value){// 列写入
$span = $keyArr[$key3];
//设置关键字高亮
if($c_key == 'NAMEZH'){
foreach ($keyWordArr as $word){
if(strpos($value, $word)!==false){
$objStyle = $objActSheet1 ->getStyle($span.$key2);
$objStyle->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$objStyle->getFont()->setSize(10);
}
}
}
if($c_key == 'HSCODE'){
//设置关键字高亮
foreach ($keyWordArr2 as $word2){
if(strpos($value, $word2)!==false){
$objStyle = $objActSheet1 ->getStyle($span.$key2);
$objStyle->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$objStyle->getFont()->setSize(10);
}
}
}
// $objStyle = $objActSheet ->getStyle($span.$key2);
// $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
//设置单元格避免大数字科学计数显示
// $objStyle = $objActSheet1 ->getStyle($span.$key2);
// $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
//$objActSheet1->setCellValue($span.$key2, $value);
if(is_numeric($value) && $c_key != 'GOODNO' && $c_key != 'HSCODE' && $c_key != 'RECEIVEZIPCODE' && $c_key != 'RECZIP'){
$objStyle = $objActSheet1 ->getStyle($span.$key2);
if(strpos($value,'.')){
$objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
}else{
$objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
}
$objActSheet1->setCellValue($span.$key2,$value);
}else{
$objActSheet1->setCellValueExplicit($span.$key2,$value,PHPExcel_Cell_DataType::TYPE_STRING);
}
//$objActSheet1->setCellValueExplicit($span.$key2,$value,PHPExcel_Cell_DataType::TYPE_STRING);
$key3++;
}
$key2++;
}
$obj_phpexcel->setActiveSheetIndex(0); //设置第一个工作表为活动工作表
$obj_Writer=new PHPExcel_Writer_Excel5($obj_phpexcel);//用于其他版本格式
$date = date("YmdHis");
$filename = "export_".$date.".xls";
ob_end_clean();//清除缓冲区,避免乱码
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="'.$filename.'"');
header("Content-Transfer-Encoding: binary");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$obj_Writer->save('php://output');
}
/**
* Class constructor
* @param PHPExcel_Worksheet $worksheet
*/
public function __construct(PHPExcel $spreadsheet, PHPExcel_Worksheet $worksheet = NULL)
{
if ($worksheet === NULL) {
$this->_worksheet = new PHPExcel_Worksheet($spreadsheet);
} else {
$this->_worksheet = $worksheet;
}
// Add worksheet to a spreadsheet
$spreadsheet->addSheet($this->_worksheet);
// Set worksheet title
if ($this->title !== NULL) {
$this->title($this->title);
}
}
/**
* Constructs one Moodle Worksheet.
*
* @param string $name The name of the file
* @param PHPExcel $workbook The internal Workbook object we are creating.
*/
public function __construct($name, PHPExcel $workbook)
{
// Replace any characters in the name that Excel cannot cope with.
$name = strtr($name, '[]*/\\?:', ' ');
// Shorten the title if necessary.
$name = core_text::substr($name, 0, 31);
if ($name === '') {
// Name is required!
$name = 'Sheet' . ($workbook->getSheetCount() + 1);
}
$this->worksheet = new PHPExcel_Worksheet($workbook, $name);
$this->worksheet->setPrintGridlines(false);
$workbook->addSheet($this->worksheet);
}
$pr = $modelo->campania_001_Vodafon_one($in);
if (is_array($pr['datos'])) {
$ou = $modelo->campania_001_Vodafon_one_process($pr);
} else {
$ou = null;
}
}
// -------------------------------------------------------- TEST
// Utilidades::printr($in);
// Utilidades::printr($ou);
// -------------------------------------------------------- OUT
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Claudio Rodriguez Ore");
$objPHPExcel->getActiveSheet()->setTitle('Total');
$myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, 'Supervisores');
$objPHPExcel->addSheet($myWorkSheet, 1);
$myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, 'Asesores');
$objPHPExcel->addSheet($myWorkSheet, 2);
$border_style = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_HAIR, 'color' => array('argb' => '000000'))));
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getColumnDimension(c0(3))->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension(c0(5))->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension(c0(8))->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension(c0(10))->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension(c0(12))->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension(c0(15))->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension(c0(17))->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension(c0(20))->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension(c0(22))->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension(c0(24))->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension(c0(26))->setWidth(6);
function downloadPHPExcelFile()
{
$sheet = new PHPExcel();
$sheet->getProperties()->setCreator('business.usa.gov')->setLastModifiedBy('business.usa.gov')->setTitle('Expired Event Summary')->setKeywords('events');
$sheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$sheet->getDefaultStyle()->getFont()->setName('Arial');
$sheet->getDefaultStyle()->getFont()->setSize(12);
$sheet->setActiveSheetIndex(0);
$activeSheet = $sheet->getActiveSheet();
$activeSheet->setTitle('Events Summary');
$activeSheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE)->setFitToWidth(1)->setFitToHeight(0);
$activeSheet->getHeaderFooter()->setOddHeader('&C&B&16' . $sheet->getProperties()->getTitle())->setOddFooter('&CPage &P of &N');
/**
* Print cell value 'Expired Event Count'
*/
$activeSheet->setCellValue('A1', 'Events Expired');
foreach ($results = get_expired_event_count_mysql() as $result) {
$activeSheet->setCellValue('A2', $result->count);
}
/**
* Current event count page
*/
$activeSheet->setCellValue('B1', 'Events Current');
foreach ($results = get_current_event_count_mysql() as $result) {
$activeSheet->setCellValue('B2', $result->count);
}
// Format cells A & B
$styleArray = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('argb' => 'EBEBEBEB')));
$allBorders = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$activeSheet->getStyle('A1:B1')->applyFromArray($styleArray);
$activeSheet->getStyle('A4:B4')->applyFromArray($styleArray);
$activeSheet->getStyle('D4:E4')->applyFromArray($styleArray);
$activeSheet->getStyle('G4:H4')->applyFromArray($styleArray);
$activeSheet->getStyle('J4:K4')->applyFromArray($styleArray);
// Loop to autoSize culumns width
for ($col = 'A'; $col <= 'K'; $col++) {
$activeSheet->getColumnDimension($col)->setAutoSize(true);
}
//Border formating
$activeSheet->getStyle('A1:B2')->applyFromArray($allBorders);
/**
* Count by event type
*/
// Print column table labels
$header = array('Events Expired', 'Event Type');
foreach ($header as $key => $value) {
$activeSheet->setCellValueByColumnAndRow($key, 4, $value);
}
$rowNum = 5;
$colNum = 0;
foreach ($results = get_expired_event_count_by_type_mysql() as $result) {
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count);
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->event_type != '' ? $result->event_type : 'None');
$rowNum++;
$colNum = 0;
}
$resultsCount = 4 + count($results);
$activeSheet->getStyle('A4:B' . $resultsCount)->applyFromArray($allBorders);
$header = array('Events Current', 'Event Type');
$rowNum = $resultsCount + 2;
$initialRowNum = $rowNum;
$colNum = 0;
foreach ($header as $value) {
$activeSheet->setCellValueByColumnAndRow($colNum, $rowNum, $value);
$colNum++;
}
$rowNum = $initialRowNum + 1;
$colNum = 0;
foreach ($results = get_current_event_count_by_type_mysql() as $result) {
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count);
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->event_type != '' ? $result->event_type : 'None');
$rowNum++;
$colNum = 0;
}
$resultsCount = $initialRowNum + count($results);
$activeSheet->getStyle('A' . $initialRowNum . ':B' . $resultsCount)->applyFromArray($allBorders);
$activeSheet->getStyle('A' . $initialRowNum . ':B' . $initialRowNum)->applyFromArray($styleArray);
/**
* event count by agency
*/
$header = array('Event Expired Count by', 'Agency');
$colNum = 3;
foreach ($header as $value) {
$activeSheet->setCellValueByColumnAndRow($colNum, 4, $value);
$colNum++;
}
$rowNum = 5;
$colNum = 3;
$final_list = GetEventsByAgencies(0);
/**
* Loop through the new array after organization mapping and print the output on the excel sheet.
*/
foreach ($final_list as $key => $value) {
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $value);
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $key);
$rowNum++;
$colNum = 3;
}
$resultsCount = 4 + count($final_list);
$activeSheet->getStyle('D4:E' . $resultsCount)->applyFromArray($allBorders);
$header = array('Event Current Count by', 'Agency');
$rowNum = $resultsCount + 2;
$initialRowNum = $rowNum;
$colNum = 3;
foreach ($header as $value) {
$activeSheet->setCellValueByColumnAndRow($colNum, $rowNum, $value);
$colNum++;
}
$rowNum = $initialRowNum + 1;
$colNum = 3;
$final_list = GetEventsByAgencies(1);
foreach ($final_list as $key => $value) {
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $value);
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $key);
$rowNum++;
$colNum = 3;
}
$resultsCount = $initialRowNum + count($final_list);
$activeSheet->getStyle('D' . $initialRowNum . ':E' . $resultsCount)->applyFromArray($allBorders);
$activeSheet->getStyle('D' . $initialRowNum . ':E' . $initialRowNum)->applyFromArray($styleArray);
/**
* print value for events entered manually
*/
$header = array('Events Expired', 'User Name');
$colNum = 6;
foreach ($header as $value) {
$activeSheet->setCellValueByColumnAndRow($colNum, 4, $value);
$colNum++;
}
$rowNum = 5;
$colNum = 6;
foreach ($results = get_expired_event_manual_entry_users_mysql() as $result) {
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count);
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->user_name != null ? $result->user_name : 'Anonymous');
$rowNum++;
$colNum = 6;
}
$resultsCount = 4 + count($results);
$activeSheet->getStyle('G4:H' . $resultsCount)->applyFromArray($allBorders);
$header = array('Events Current', 'User Name');
$rowNum = $resultsCount + 2;
$initialRowNum = $rowNum;
$colNum = 6;
foreach ($header as $value) {
$activeSheet->setCellValueByColumnAndRow($colNum, $rowNum, $value);
$colNum++;
}
$rowNum = $initialRowNum + 1;
$colNum = 6;
foreach ($results = get_current_event_manual_entry_users_mysql() as $result) {
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count);
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->user_name);
$rowNum++;
$colNum = 6;
}
$resultsCount = $initialRowNum + count($results);
$activeSheet->getStyle('G' . $initialRowNum . ':H' . $resultsCount)->applyFromArray($allBorders);
$activeSheet->getStyle('G' . $initialRowNum . ':H' . $initialRowNum)->applyFromArray($styleArray);
/**
* Count and names of Feed Importers
*/
$header = array('Events Expired', 'Feed Importer');
$colNum = 9;
foreach ($header as $value) {
$activeSheet->setCellValueByColumnAndRow($colNum, 4, $value);
$colNum++;
}
$rowNum = 5;
$colNum = 9;
foreach ($results = get_expired_event_feed_importers_name_mysql() as $result) {
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count);
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->user_name);
$rowNum++;
$colNum = 9;
}
$resultsCount = 4 + count($results);
$activeSheet->getStyle('J4:K' . $resultsCount)->applyFromArray($allBorders);
$header = array('Events Current', 'Feed Importer');
$rowNum = $resultsCount + 2;
$initialRowNum = $rowNum;
$colNum = 9;
foreach ($header as $value) {
$activeSheet->setCellValueByColumnAndRow($colNum, $rowNum, $value);
$colNum++;
}
$rowNum = $initialRowNum + 1;
$colNum = 9;
foreach ($results = get_current_event_feed_importers_name_mysql() as $result) {
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count);
$activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->user_name != '' ? $result->user_name : 'Anonymous');
$rowNum++;
$colNum = 9;
}
$resultsCount = $initialRowNum + count($results);
$activeSheet->getStyle('J' . $initialRowNum . ':K' . $resultsCount)->applyFromArray($allBorders);
$activeSheet->getStyle('J' . $initialRowNum . ':K' . $initialRowNum)->applyFromArray($styleArray);
/**
* Create a new worksheet called "Event Detail" $secondSheet
*/
$secondSheet = new PHPExcel_Worksheet($sheet, 'Event Detail');
$sheet->addSheet($secondSheet, 1);
$secondSheet = $sheet->getSheet(1);
$secondSheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$secondSheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$secondSheet->getStyle('A1:N1')->applyFromArray($styleArray);
// Loop to autoSize culumns width
for ($col = 'A'; $col <= 'M'; $col++) {
$secondSheet->getColumnDimension($col)->setAutoSize(true);
}
$secondSheet->getColumnDimension('N')->setWidth(60);
// Print column table labels
$header = array('Event Title', 'Detail Description', 'Event Start Date', 'Event End Date', 'Start Time', 'End Time', 'Address 1', 'Address 2', 'City', 'State', 'Zip Code', 'Country', 'Organization', 'Learn More URL');
foreach ($header as $key => $value) {
$secondSheet->setCellValueByColumnAndRow($key, 1, $value);
}
// Print cell values
$rowNum = 2;
$colNum = 0;
foreach ($results = current_events_detail_list_mysql() as $result) {
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->title);
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, strip_tags($result->field_event_detail_desc_value));
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, date('M-d-Y', strtotime($result->field_event_date_value)));
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_date_value2 != null ? date('M-d-Y', strtotime($result->field_event_date_value2)) : $result->field_event_date_value2);
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, date('H:i:s', strtotime($result->field_event_date_value)));
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, date('H:i:s', strtotime($result->field_event_date_value2)));
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_address_1_value);
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_address_2_value);
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_city_value);
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_state_value);
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_zip_value);
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_country_value);
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_program_org_tht_owns_prog_value);
$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_url_url);
//$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $GLOBALS['base_url'] . '/node/' . $result->nid);
$rowNum++;
$colNum = 0;
}
$resultsCount = 1 + count($results);
$secondSheet->getStyle('A1:N' . $resultsCount)->applyFromArray($allBorders);
/**
* redirect output to client browser
*/
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="event-summary.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($sheet, 'Excel5');
$objWriter->save('php://output');
return;
}
// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', 'Miscellaneous glyphs')->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');
$objPHPExcel->getActiveSheet()->setCellValue('A8', "Hello\nWorld");
$objPHPExcel->getActiveSheet()->getRowDimension(8)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setWrapText(true);
// Rename worksheet
echo date('H:i:s'), " Rename worksheet", EOL;
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Clone worksheet
echo date('H:i:s'), " Clone worksheet", EOL;
$clonedSheet = clone $objPHPExcel->getActiveSheet();
$clonedSheet->setCellValue('A1', 'Goodbye')->setCellValue('A2', 'cruel')->setCellValue('C1', 'Goodbye')->setCellValue('C2', 'cruel');
// Rename cloned worksheet
echo date('H:i:s'), " Rename cloned worksheet", EOL;
$clonedSheet->setTitle('Simple Clone');
$objPHPExcel->addSheet($clonedSheet);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 2007 file
echo date('H:i:s'), " Write to Excel2007 format", EOL;
$callStartTime = microtime(true);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
echo date('H:i:s'), " File written to ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
echo 'Call time to write Workbook was ', sprintf('%.4f', $callTime), " seconds", EOL;
// Echo memory usage
echo date('H:i:s'), ' Current memory usage: ', memory_get_usage(true) / 1024 / 1024, " MB", EOL;
// Echo memory peak usage
echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", EOL;
function reporte($bls)
{
global $linkSrv, $linkDB, $linkUsr, $linkPass;
// -------------------------------------------------------------------------------
// ODBC
// El sistema se trata de conectar mediante ODBC nativo a la base de datos MscLink
// -------------------------------------------------------------------------------
$dsn = "Driver={SQL Server};Server={$linkSrv};Database={$linkDB};Integrated Security=SSPI;Persist Security Info=False;";
// Se realiza la conexón con los datos especificados anteriormente
$conn = odbc_connect($dsn, $linkUsr, $linkPass);
$conn2 = odbc_connect($dsn, $linkUsr, $linkPass);
// Incluir la libreria PHPExcel
require '../include/PHPExcel/PHPExcel.php';
// Reservar memoria en servidor PHP
// Si el archivo final tiene 5Mb, reservar 500Mb
// Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria
ini_set("memory_limit", "512M");
// Estilos Arreglo
$styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
$styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
// ----------------------------
// Hoja 1
// Nota : No usar acentos!!!
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Dep.Garantia');
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);
// HOJA 2
$workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Demoras');
$objPHPExcel->addSheet($workSheet2, 1);
$workSheet2->getDefaultStyle()->getFont()->setSize(8);
$workSheet2->getDefaultStyle()->getFont()->setName('Arial');
// -----------------------------------------------
// ENCABEZADOS
// -----------------------------------------------
// Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas.
$arrHojas[] = 0;
//$arrHojas[]=1;
foreach ($arrHojas as $hoja) {
$objPHPExcel->setActiveSheetIndex($hoja);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:Q7')->applyFromArray($styleEnc);
// Encabezados
$headings = array('Concepto', 'Referencia', 'Nota', 'Nota1', 'Nota2', 'Nota3', 'Monto');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:G7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'MEDITERRANEAN SHIPPING COMPANY MEXICO, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', '.');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', '.');
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A4', ".");
$objPHPExcel->getActiveSheet()->getStyle('A4')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Depositos en Garantia MscLink');
$objPHPExcel->getActiveSheet()->getStyle('A5')->applyFromArray($styleSubtitulo);
}
// -------------------------
// Encabezado de la hoja 2
// -------------------------
$objPHPExcel->setActiveSheetIndex(1);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:S7')->applyFromArray($styleEnc);
// Encabezados
$headings = array('Bl', 'Contenedor', 'Concepto', 'Evento', 'Ev.Tiempo', 'EventoFin', 'EventoFin.Tiempo', 'POD', 'TMSCodeShp', 'Shipper', 'Inv.Company', 'DiasLibres', 'Invoice', 'Doc.Date', 'Amount', 'InvNum', 'TotInv', 'PaidInv', 'OustInv');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:S7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(25);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'MEDITERRANEAN SHIPPING COMPANY MEXICO, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', '.');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', '.');
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A4', ".");
$objPHPExcel->getActiveSheet()->getStyle('A4')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Depositos en Garantia MscLink');
$objPHPExcel->getActiveSheet()->getStyle('A5')->applyFromArray($styleSubtitulo);
// ----------------------------------------------------------------------------
// BL DE PRUEBA
// MSCUXL570178
// -----------------------------------
// HOJA 1
// -----------------------------------
$objPHPExcel->setActiveSheetIndex(0);
// Simple query
$sql = "\n SELECT\n A.Name AS CONCEPTO,\n T.ReferenceNumber AS REFERENCIA,\n T2.Note AS NOTA,\n T2.Reference1 AS NOTA1,\n T2.Reference2 AS NOTA2,\n T2.Reference3 AS NOTA3,\n T.Available_Amount AS MONTO\n FROM (\n SELECT T.ReferenceNumber, SUM(T.Currency_Amount) Available_Amount\n FROM Interlink_Accounting90.Accounting.Accounts A (NOLOCK)\n INNER JOIN Interlink_Accounting90.Accounting.Transactions T (NOLOCK) ON T.Account_id = A.Account_id\n WHERE Name = 'Depositos en Garantia MXN'\n GROUP BY T.ReferenceNumber, T.Note, T.Reference1, T.Reference2, T.Reference3\n HAVING SUM(T.Currency_Amount) < 0\n ) T\n INNER JOIN Interlink_Accounting90.Accounting.Transactions T2 (NOLOCK) ON T.ReferenceNumber = T2.ReferenceNumber\n INNER JOIN Interlink_Accounting90.Accounting.Accounts A (NOLOCK) ON A.Account_id = T2.Account_id AND Name = 'Depositos en Garantia MXN'\n WHERE T.Available_Amount < 0\n AND T2.Amount < 0\n ";
$sql .= "AND (";
unset($arrBls);
$arrBls = explode("\n", $bls);
$r = 1;
foreach ($arrBls as $bl) {
if (!empty($bl)) {
$bl = str_replace("\n", "", $bl);
$bl = str_replace("\r", "", $bl);
$bl = trim($bl);
if ($r == 1) {
$sql .= "T.ReferenceNumber like '%{$bl}%' ";
} elseif ($r > 1) {
$sql .= "OR T.ReferenceNumber like '%{$bl}%' ";
}
$r++;
}
}
$sql .= ")";
$rs = odbc_exec($conn, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$lin = 1;
$rowNumber = 8;
// donde inicia los datos
while (odbc_fetch_row($rs)) {
$concepto = odbc_result($rs, "CONCEPTO");
$refer = odbc_result($rs, "REFERENCIA");
$nota = odbc_result($rs, "NOTA");
$nota1 = odbc_result($rs, "NOTA1");
$nota2 = odbc_result($rs, "NOTA2");
$nota3 = odbc_result($rs, "NOTA3");
$monto = odbc_result($rs, "MONTO");
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $concepto)->setCellValue('B' . $rowNumber, $refer)->setCellValue('C' . $rowNumber, $nota)->setCellValue('D' . $rowNumber, $nota1)->setCellValue('E' . $rowNumber, $nota2)->setCellValue('F' . $rowNumber, $nota3)->setCellValue('G' . $rowNumber, $monto);
$rowNumber++;
}
// ------------------------------
// Hoja 2 - DEMORAS
// ------------------------------
$sqlBL = "";
foreach ($arrBls as $bl) {
if (!empty($bl)) {
$bl = str_replace("\n", "", $bl);
$bl = str_replace("\r", "", $bl);
$bl = trim($bl);
$sqlBL .= "'{$bl}',";
}
}
$blsInTxt = substr($sqlBL, 0, -1);
$objPHPExcel->setActiveSheetIndex(1);
// Simple query
$sql = "\n SELECT\n CH.Show_as AS CONCEPTO,\n CH.Start_Event AS EVENTO,\n CH.start_event_datetime AS EVENTOTIEMPO,\n CH.End_Event AS ENVENTOFIN,\n CH.end_event_datetime AS EVENTOFINTIEMPO,\n CH.POD_name AS PODNAME,\n CH.Bill_Of_Lading_Number AS BL,\n CH.Equipment_Number AS CONTE,\n ADCSH.MSCCode AS TMSCODESHI,\n ADCCN.Company AS SHIPPER,\n ADCIC.Company AS INVCOMPANY,\n CH.Charge_Level_0_Days AS DIASLIBRES,\n CH.invoice_number AS INVOICE,\n I.Doc_Date AS DOCDATE,\n CH.Charge_Amount AS AMOUNTX,\n\n (select I.Invoice_Number\n from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )\n left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id\n where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)\n where Invoice_Number=CH.invoice_number)) as InvNum,\n (select (AI . Inv_Total_Inv_Currency + AI. Inv_Total_VAT_Inv_Currency) Total\n from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )\n left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id\n where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)\n where Invoice_Number=CH.invoice_number)) as TotInv,\n (select I.Paid\n from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )\n left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id\n where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)\n where Invoice_Number=CH.invoice_number)) as PaidInv,\n (select ( AI .Inv_Total_Inv_Currency + AI. Inv_Total_VAT_Inv_Currency - I .Paid )\n from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )\n left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id\n where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)\n where Invoice_Number=CH.invoice_number )) as OustInv\n\n FROM\n Interlink_DFeed90.dfeed.ILIM_FNTB_Charges () CH\n INNER JOIN Interlink_Main90.dbo.Invoice I (NOLOCK) ON I.Invoice_id = CH.Invoice_id\n LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('CONSIGN') ADCCN ON ADCCN.Bill_Of_Lading_id = CH.bill_of_lading_id\n LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('SHIPPER') ADCSH ON ADCSH.Bill_Of_Lading_id = CH.bill_of_lading_id\n LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('INVCMPBL') ADCIC ON ADCIC.Bill_Of_Lading_id = CH.bill_of_lading_id\n WHERE\n Show_as = 'DEMURRAGE'\n AND CH.Bill_Of_Lading_Number in ({$blsInTxt})\n AND Charge_Status IN ('INVOICED')\n ";
$rs = odbc_exec($conn, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$rowNumber = 8;
// donde inicia los datos
while (odbc_fetch_row($rs)) {
$c1 = odbc_result($rs, "BL");
$c2 = odbc_result($rs, "CONTE");
$c3 = odbc_result($rs, "CONCEPTO");
$c4 = odbc_result($rs, "EVENTO");
$c5 = odbc_result($rs, "EVENTOTIEMPO");
$c6 = odbc_result($rs, "ENVENTOFIN");
$c7 = odbc_result($rs, "EVENTOFINTIEMPO");
$c8 = odbc_result($rs, "PODNAME");
$c9 = odbc_result($rs, "TMSCODESHI");
$c10 = odbc_result($rs, "SHIPPER");
$c11 = odbc_result($rs, "INVCOMPANY");
$c12 = odbc_result($rs, "DIASLIBRES");
$c13 = odbc_result($rs, "INVOICE");
$c14 = odbc_result($rs, "DOCDATE");
$c15 = odbc_result($rs, "AMOUNTX");
$c16 = odbc_result($rs, "InvNum");
$c17 = odbc_result($rs, "TotInv");
$c18 = odbc_result($rs, "PaidInv");
$c19 = odbc_result($rs, "OustInv");
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $c1)->setCellValue('B' . $rowNumber, $c2)->setCellValue('C' . $rowNumber, $c3)->setCellValue('D' . $rowNumber, $c4)->setCellValue('E' . $rowNumber, $c5)->setCellValue('F' . $rowNumber, $c6)->setCellValue('G' . $rowNumber, $c7)->setCellValue('H' . $rowNumber, $c8)->setCellValue('I' . $rowNumber, $c9)->setCellValue('J' . $rowNumber, $c10)->setCellValue('K' . $rowNumber, $c11)->setCellValue('L' . $rowNumber, $c12)->setCellValue('M' . $rowNumber, $c13)->setCellValue('N' . $rowNumber, $c14)->setCellValue('O' . $rowNumber, $c15)->setCellValue('P' . $rowNumber, $c16)->setCellValue('Q' . $rowNumber, $c17)->setCellValue('R' . $rowNumber, $c18)->setCellValue('S' . $rowNumber, $c19);
$rowNumber++;
}
// Loregreso a la hoja 1.
$objPHPExcel->setActiveSheetIndex(0);
// Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attachment;filename="repForCapitania.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
}
$start_date = $start_date_day . "." . $start_date_month . "." . $start_date['year'];
$end_date = getdate($end);
$end_date_day = $end_date["mday"];
if ($end_date_day < 10) {
$end_date_day = "0" . $end_date_day;
}
$end_date_month = $end_date["mon"];
if ($end_date_month < 10) {
$end_date_month = "0" . $end_date_month;
}
$end_date = $end_date_day . "." . $end_date_month . "." . $end_date['year'];
$objPHPExcel = new PHPExcel();
$objPHPExcel->removeSheetByIndex(0);
$worksheet = new PHPExcel_Worksheet($objPHPExcel, 'График поездок');
$objPHPExcel->addSheet($worksheet, 1);
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$con = oci_connect('purchases', 'PURCHASES', '192.168.50.52/ORCLWORK', 'AL32UTF8');
if (!$con) {
oci_close($con);
die('Ошибка подключения к базе');
}
date_default_timezone_set("Etc/GMT-4");
$query = "SELECT *\n FROM V_AUTO_REQUESTS\n WHERE V_AUTO_REQUESTS.START_TIME >= '{$start}' AND\n V_AUTO_REQUESTS.START_TIME <= '{$end}' AND\n V_AUTO_REQUESTS.REQUEST_DEPARTMENT = {$department} AND\n V_AUTO_REQUESTS.REQUEST_REJECT_RECIEVED = 0\n ORDER BY V_AUTO_REQUESTS.REQUEST_ID ASC ";
$statement = oci_parse($con, $query);
if (oci_execute($statement, OCI_DEFAULT)) {
while ($res = oci_fetch_array($statement)) {
$requests[] = $res;
}
function reporte($fec1, $fec2, $impoExpo)
{
global $connI;
$reportName = "BLS SUMMARY FROM MSCLINK";
// Incluir la libreria PHPExcel
require '../include/PHPExcel/PHPExcel.php';
// Reservar memoria en servidor PHP
// Si el archivo final tiene 5Mb, reservar 500Mb
// Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria
ini_set("memory_limit", "512M");
// Estilos Arreglo
$styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
$styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
// ----------------------------
// Hoja 1
// Nota : No usar acentos!!!
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Hoja1');
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);
// HOJA 2
$workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Hoja2');
$objPHPExcel->addSheet($workSheet2, 1);
$workSheet2->getDefaultStyle()->getFont()->setSize(8);
$workSheet2->getDefaultStyle()->getFont()->setName('Arial');
// -----------------------------------------------
// ENCABEZADOS
// -----------------------------------------------
// Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas.
$objPHPExcel->setActiveSheetIndex(0);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:Z7')->applyFromArray($styleEnc);
// Encabezados
$headings = array('Booking_Number', 'Bill_Of_Lading_Number', 'POL', 'POD', 'Service', 'Trade', 'Agent_Location', 'Vessel_Name', 'Voyage', 'Departure_dateTime', 'DTX_File_Name', 'Collection_Office_Code', 'Inttra_Booking', 'Shipper', 'FFW', 'Sales_Exe', 'Doc_Exe', 'SI_User', 'Created_by_User', 'container_count', 'Origin', 'FDest', 'Express_release', 'Telex_Release', 'EDI_Wizard', 'Created By');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:X7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(15);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Solo como Agentes / As Agent only');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', '$reportName');
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A6', "{$buque} {$viaje}");
$objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo);
// -----------------------------------
// HOJA 1
// -----------------------------------
$objPHPExcel->setActiveSheetIndex(0);
// Simple query
$sql = "\n\tSELECT\n\tb.Booking_Number as Booking_Number,\n\tb.Bill_Of_Lading_Number as Bill_Of_Lading_Number,\n\tb.POL as POL,\n\tb.POD as POD,\n\tb.Service as Service,\n\tB.Trade as Trade,\n\ta.LOC_Full AS Agent_Location,\n\tb.Vessel_Name as Vessel_Name,\n\tb.Voyage as Voyage,\n\tb.Departure_dateTime as Departure_dateTime,\n\tb.DTX_File_Name as DTX_File_Name,\n\tb.Collection_Office_Code as Collection_Office_Code,\n\tc.Inttra_Booking as Inttra_Booking,\n\ta1.Company AS Shipper,\n\ta2.Company AS FFW,\n\tc.Local_STR_1 AS Sales_Exe,\n\tc.Local_STR_2 AS Doc_Exe,\n\tu.User_Name AS SI_User,\n\ts.User_Name AS Created_by_User,\n\tb.[# CSC] AS container_count,\n\tb.Origin as Origin,\n\tb.FDest as FDest,\n\tb.Express_release as Express_release,\n\tB.Telex_Release as Telex_Release,\n\tU.First_Name +' '+U.Last_Name as EDI_Wizard,\n\tu2.First_Name +' '+u2.Last_Name as Created_by\n\tFROM\n\t\tInterlink_DFeed90.dfeed.FNTB_Booking_BL (0, 300) B\n\t\tLEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ('Booking Agent') a ON a.Cargo_Shipment_id = b.Cargo_Shipment_id\n\t\tLEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ('Shipper') a1 ON a1.Bill_Of_Lading_id = b.Bill_Of_Lading_id\n\t\tLEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ('Freight Fwd (BL) Shipper') a2 ON a2.Bill_Of_Lading_id = b.Bill_Of_Lading_id\n\t\tINNER JOIN Interlink_Main90.dbo.Cargo_Shipment (nolock) c ON c.Cargo_Shipment_id = b.Cargo_Shipment_id\n\t\tLEFT JOIN Interlink_Main90.dbo.Cargo_Shipment_Comment o (nolock) ON b.Cargo_Shipment_id = o.Cargo_Shipment_id AND o.Comment LIKE 'bill of lading created from si%'\n\t\tLEFT JOIN interlink_main90.dbo.gissec_user (nolock) U ON o.User_id = U.SecUser_id\n\t\tLEFT JOIN Interlink_Main90.dbo.GISSec_User (nolock) s ON s.SecUser_id = b.Clerk_User_id\n\t\tLEFT JOIN interlink_main90.dbo.bill_of_lading (nolock) BL ON B.Bill_Of_Lading_id = BL.Bill_Of_Lading_Id\n\t\tLEFT JOIN interlink_main90.dbo.gissec_user (nolock) u2 ON BL.Created_By_User_id = u2.SecUser_id\n\tWHERE\n\t\tb.I_E = '{$impoExpo}'\n\tAND b.Departure_dateTime BETWEEN '{$fec1}' AND '{$fec2}'\n\t-- AND b.Bill_Of_Lading_Number in ('MSCUAU931745','MSCUAU925986')\n\tORDER BY\n\tb.Departure_dateTime,\n\tb.Booking_Number\n\n ";
$rs = odbc_exec($connI, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$lin = 1;
$rowNumber = 8;
// donde inicia los datos
while (odbc_fetch_row($rs)) {
$Booking_Number = odbc_result($rs, 'Booking_Number');
$Bill_Of_Lading_Number = odbc_result($rs, 'Bill_Of_Lading_Number');
$POL = odbc_result($rs, 'POL');
$POD = odbc_result($rs, 'POD');
$Service = odbc_result($rs, 'Service');
$Trade = odbc_result($rs, 'Trade');
$Agent_Location = odbc_result($rs, 'Agent_Location');
$Vessel_Name = odbc_result($rs, 'Vessel_Name');
$Voyage = odbc_result($rs, 'Voyage');
$Departure_dateTime = odbc_result($rs, 'Departure_dateTime');
$DTX_File_Name = odbc_result($rs, 'DTX_File_Name');
$Collection_Office_Code = odbc_result($rs, 'Collection_Office_Code');
$Inttra_Booking = odbc_result($rs, 'Inttra_Booking');
$Shipper = odbc_result($rs, 'Shipper');
$FFW = odbc_result($rs, 'FFW');
$Sales_Exe = odbc_result($rs, 'Sales_Exe');
$Doc_Exe = odbc_result($rs, 'Doc_Exe');
$SI_User = odbc_result($rs, 'SI_User');
$Created_by_User = odbc_result($rs, 'Created_by_User');
$container_count = odbc_result($rs, 'container_count');
$Origin = odbc_result($rs, 'Origin');
$FDest = odbc_result($rs, 'FDest');
$Express_release = odbc_result($rs, 'Express_release');
$Telex_Release = odbc_result($rs, 'Telex_Release');
$EDI_Wizard = odbc_result($rs, 'EDI_Wizard');
$Created_by = odbc_result($rs, 'Created_by');
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $Booking_Number)->setCellValue('B' . $rowNumber, $Bill_Of_Lading_Number)->setCellValue('C' . $rowNumber, $POL)->setCellValue('D' . $rowNumber, $POD)->setCellValue('E' . $rowNumber, $Service)->setCellValue('F' . $rowNumber, $Trade)->setCellValue('G' . $rowNumber, $Agent_Location)->setCellValue('H' . $rowNumber, $Vessel_Name)->setCellValue('I' . $rowNumber, $Voyage)->setCellValue('J' . $rowNumber, $Departure_dateTime)->setCellValue('K' . $rowNumber, $DTX_File_Name)->setCellValue('L' . $rowNumber, $Collection_Office_Code)->setCellValue('M' . $rowNumber, $Inttra_Booking)->setCellValue('N' . $rowNumber, $Shipper)->setCellValue('O' . $rowNumber, $FFW)->setCellValue('P' . $rowNumber, $Sales_Exe)->setCellValue('Q' . $rowNumber, $Doc_Exe)->setCellValue('R' . $rowNumber, $SI_User)->setCellValue('S' . $rowNumber, $Created_by_User)->setCellValue('T' . $rowNumber, $container_count)->setCellValue('U' . $rowNumber, $Origin)->setCellValue('V' . $rowNumber, $FDest)->setCellValue('W' . $rowNumber, $Express_release)->setCellValue('X' . $rowNumber, $Telex_Release)->setCellValue('Y' . $rowNumber, $EDI_Wizard)->setCellValue('Z' . $rowNumber, $Created_by);
$rowNumber++;
$lin++;
}
/*
// ------------------------------
// Hoja 2 - TRANSBORDOS
// ------------------------------
$objPHPExcel->setActiveSheetIndex(1);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:T7')->applyFromArray($styleEnc);
// Encabezados
$headings = Array('No', 'Original_pol');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:T7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.')
->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Solo como Agentes / As agents Only');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', '$reportName');
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A6', "$buque $viaje");
$objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo);
$objPHPExcel->setActiveSheetIndex(1);
// Simple query
$sql = "
SELECT
e.Equipment_Number as CONTE,
e.Bill_Of_Lading_Number as BL,
e.Origin_Booking_Number,
e.EQ_Type as EQ_Type,
e.Load_Terminal,
e.VPCLoad_Vessel_Name,
e.VPCLoad_Voyage,
e.Origin AS Inland_Origin,
e.FDest AS Inland_Final_Dest,
e.Orig_POLUN AS Original_POL,
e.VPCLoad_UNCode AS Current_POL,
e.Prev_POLUN AS Previous_Port,
e.Next_PODUN AS Next_POD,
e.PODUN AS Final_POD,
e.TSPorts AS All_TS_Ports,
e.NVOCC_AMS_CERTIFIED,
e.High_Priority_Flag,
e.Hold_Indicator,
d.UN_Number as UNNumber,
d.IMO_Class as ImoClass,
e.Shipper_Owned,
e.Master_Tare_Weight_Kg as TaraKg,
e.Cntr_Weight,
e.Cargo_Weight_Kgs as CargoKg,
e.Master_Payload_Kg,
e.Cargo_Volume_CMT,
e.Ex_Cargo_Short_Description as Producto,
e.Cargo_Packages as Paquetes,
e.Ex_Cargo_Package_Type as Embalaje,
e.Ex_Harmonized_Code,
e.OOG_Height,
e.OOG_Length_Back,
e.OOG_Length_Front,
e.OOG_Width_Left,
e.OOG_Width_Right,
e.Tank_Residue,
e.Max_Reefer_Temp,
e.Min_Reefer_Temp,
e.Temp_Unit,
e.Ventilated_Container_Flag,
e.Air_Fresh_Vents_Setting,
e.Cold_Treatment,
e.Control_Atmosphere,
e.Opening_Percentage,
e.Bulb_Mode,
e.IsEmpty as IsEmpty,
e.Fumigation,
e.De_Fumigation,
e.Fumigation_Residue_IMO_Class,
e.Fumigation_Residue_UN_Number,
e.Custom_Status_Desc,
a.company AS shipper_name,
a.city AS shipper_city,
a.Country AS shipper_country,
a.MSCCode AS shipper_msccode
FROM
interlink_dfeed90.dfeed.TSHIP_FNTB_Equipment ('2015-05-01', 'A') e
LEFT JOIN Interlink_DFeed90.dfeed.TSHIP_FNTB_Dangerous_Cargo () d ON e.Equipment_ID = d.Equipment_ID
LEFT JOIN Interlink_DFeed90.dfeed.TSHIP_FNTB_AssocDocCompany ('shipper') a ON e.Bill_Of_Lading_Number = a.Bill_Of_Lading_Number
WHERE
E.VPCDisch_Vessel_Name = '$buque'
AND E.VPCDisch_Voyage = '$viaje'
AND E.VPCDisch_UNCode = '$codPuerto'
";
$rs = odbc_exec($connI, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$lin=1;
$rowNumber = 8; // donde inicia los datos
while (odbc_fetch_row($rs)) {
$polOri = odbc_result($rs, "Original_POL");
$polCur = odbc_result($rs, "Current_POL");
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue(
'A'.$rowNumber,$lin)
->setCellValue('B'.$rowNumber,$polOri)
->setCellValue('C'.$rowNumber,$polPrev)
->setCellValue('D'.$rowNumber,$x)
->setCellValue('E'.$rowNumber,$x)
->setCellValue('F'.$rowNumber,$x)
->setCellValue('G'.$rowNumber,$x)
->setCellValue('H'.$rowNumber,$x)
->setCellValue('I'.$rowNumber,$x)
->setCellValue('J'.$rowNumber,$x)
->setCellValue('K'.$rowNumber,$x)
->setCellValue('L'.$rowNumber,$x)
->setCellValue('M'.$rowNumber,$x)
->setCellValue('N'.$rowNumber,$x)
->setCellValue('O'.$rowNumber,$x)
->setCellValue('P'.$rowNumber,$x)
->setCellValue('Q'.$rowNumber,$x)
->setCellValue('R'.$rowNumber,$x)
->setCellValue('S'.$rowNumber,$x)
->setCellValue('T'.$rowNumber,$x)
;
$rowNumber++;
$lin++;
}
// Loregreso a la hoja 1.
$objPHPExcel->setActiveSheetIndex(0);
*/
// Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attachment;filename="blSummary.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
/**
* Get a more complete report- a data dump for analysis
* @global type $CFG
* @param type $formID
* @return PHPExcel an Excel spreadsheet containing a detailed report of the results of an assessment session
*/
public function getFullReportAsExcel($session_ID)
{
$overviewXML = simplexml_load_string($this->getSummaryReportForExamInstance($session_ID));
// print("form ID is: $session_ID <br/>");
global $CFG;
// questions labels.
// alphabet array
$alphabetArr = array();
foreach (range('A', 'Z') as $i) {
$alphabetArr[] = $i;
}
// this extends the possible spreadsheet cells a bit.
foreach (range('A', 'Z') as $i) {
$alphabetArr[] = "A" . $i;
}
foreach (range('A', 'Z') as $i) {
$alphabetArr[] = "B" . $i;
}
foreach (range('A', 'Z') as $i) {
$alphabetArr[] = "C" . $i;
}
// There shouldn't be more than, say, 70
$j = 0;
// get student sessions
$sessionsXMLStr = "<data>";
try {
$conn = new PDO("mysql:host={$CFG->db};dbname={$CFG->schema}", $CFG->dbuser, $CFG->dbuserpass);
} catch (PDOException $e) {
die('<data><error>failed connecting to database</error><detail>' . $e->getMessage() . '</detail></data>');
}
$query = "SELECT a.ID, a.student_id, a.overall_rating, a.additional_rating, a.site_ID,\n (SELECT fname FROM students WHERE students.ID = a.student_id) as fname, \n (SELECT lname FROM students WHERE students.ID = a.student_id) as lname, \n (SELECT studentnum FROM students WHERE students.ID = a.student_id) as studentnum, a.form_id, a.start_timestamp\n from student_exam_sessions a inner join exam_instances b on a.form_ID = b.ID WHERE a.form_ID = :session_ID";
$stmt = $conn->prepare($query);
$stmt->bindValue(':session_ID', $session_ID, PDO::PARAM_INT);
$stmt->execute() or die('<data><error>getSummaryReportForExamInstance query failed</error><detail>' . $stmt->errorCode() . '</detail></data>');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$sessionsXMLStr .= "<session><id>{$row['ID']}</id><student_id>{$row['student_id']}</student_id><fname>{$row['fname']}</fname><lname>{$row['lname']}</lname><studentnum>{$row['studentnum']}</studentnum><siteid>{$row['site_ID']}</siteid><created_timedate>{$row['start_timestamp']}</created_timedate></session>";
}
$sessionsXMLStr .= "</data>";
// print($query);
$sessionsXML = simplexml_load_string($sessionsXMLStr);
// get question definitions for this session
$enumlib = new EnumLib();
$questionsXML = simplexml_load_string($enumlib->getQuestionsForSession($session_ID));
// create excel sheet
$phpexcelObj = new PHPExcel();
// make a new Excel sheet
$quantitativeWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Quantitative Cohort Outcomes');
$phpexcelObj->addSheet($quantitativeWorksheet, 0);
// set labels
$quantitativeWorksheet->getColumnDimension("A")->setWidth(200);
$quantitativeWorksheet->setCellValue('A1', "Quantitative Cohort Outcomes for: {$overviewXML->summary->description} {$overviewXML->summary->examdate}, n=" . count($sessionsXML->session));
$quantitativeWorksheet->getStyle('A1')->getFont()->setSize(16);
$quantitativeWorksheet->setCellValue('A2', "Item");
$quantitativeWorksheet->getStyle("A2")->getFont()->setBold(true);
// $quantitativeWorksheet->setCellValue('C2', "Percentage");
// $quantitativeWorksheet->getStyle("C2")->getFont()->setBold(true);
// $quantitativeWorksheet->getColumnDimension("C")->setAutoSize(true);
$questionscount = 0;
$questionsArr = array();
$k = 3;
//
foreach ($questionsXML->question as $question) {
//set all criteria labels
$quantitativeWorksheet->setCellValue("A{$k}", $question->text);
// $quantitativeWorksheet->getStyle("A$k")->getFont()->setBold(true);
$quantitativeWorksheet->getColumnDimension("A")->setWidth(150);
// get the criteria
$currentColumn = 1;
$criteriaDef = simplexml_load_string($enumlib->getCriteriaScaleItems($overviewXML->summary->scale_id));
foreach ($criteriaDef->item as $item) {
$quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn]}2", "n marked {$item->short_description}");
$quantitativeWorksheet->getStyle("{$alphabetArr[$currentColumn]}2")->getFont()->setBold(true);
$quantitativeWorksheet->getColumnDimension("{$alphabetArr[$currentColumn]}")->setAutoSize(true);
$quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn + 1]}2", "% marked {$item->short_description}");
$quantitativeWorksheet->getStyle("{$alphabetArr[$currentColumn + 1]}2")->getFont()->setBold(true);
$quantitativeWorksheet->getColumnDimension("{$alphabetArr[$currentColumn + 1]}")->setAutoSize(true);
$query = "SELECT COUNT(answer) as total FROM student_exam_sessions_responses WHERE question_ID = :id AND answer = :answer AND student_exam_sessions_responses.student_exam_session_ID IN(SELECT ID FROM student_exam_sessions WHERE student_exam_sessions.form_ID = :session_ID)";
$stmt = $conn->prepare($query);
$stmt->bindValue(':id', $question->id, PDO::PARAM_INT);
$stmt->bindValue(':answer', $item->value, PDO::PARAM_STR);
$stmt->bindValue(':session_ID', $session_ID, PDO::PARAM_INT);
$stmt->execute() or die('<data><error>select query failed query failed</error><detail>' . $stmt->errorCode() . '</detail></data>');
$resultArr = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn]}{$k}", $row['total']);
$quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn + 1]}{$k}", round($row['total'] / count($sessionsXML->session), 2) * 100 . "%");
}
$currentColumn += 2;
$stmt->closeCursor();
}
$k++;
}
// qualitative data
$criteriaWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Criteria Comments');
$phpexcelObj->addSheet($criteriaWorksheet, 1);
// set labels
$criteriaWorksheet->getColumnDimension("A")->setWidth(150);
$criteriaWorksheet->setCellValue('A1', "Qualitative Comments on Criteria Requiring Comments: {$overviewXML->summary->description} {$overviewXML->summary->examdate}, n=" . count($sessionsXML->session));
$criteriaWorksheet->getStyle('A1')->getFont()->setSize(16);
$criteriaWorksheet->setCellValue('A2', "Item");
$criteriaWorksheet->getStyle("A2")->getFont()->setBold(true);
$criteriaWorksheet->setCellValue('B2', "Comments");
$criteriaWorksheet->getStyle("B2")->getFont()->setBold(true);
$criteriaWorksheet->getColumnDimension("B")->setAutoSize(true);
$k = 3;
//
foreach ($questionsXML->question as $question) {
//set all criteria labels
// add up questions here
//$currentrow = 4;
//print('num'.$studentexamsession->studentnum);
$sql = "SELECT COUNT(*) FROM student_exam_sessions_responses WHERE question_ID = :id AND answer = 0 AND student_exam_sessions_responses.student_exam_session_ID IN(SELECT ID FROM student_exam_sessions WHERE student_exam_sessions.form_ID = :session_ID)";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':id', $question->id, PDO::PARAM_INT);
$stmt->bindValue(':session_ID', $session_ID, PDO::PARAM_INT);
if ($stmt->execute()) {
if ($stmt->fetchColumn() > 0) {
$stmt->closeCursor();
$criteriaWorksheet->setCellValue("A{$k}", html_entity_decode($question->text, ENT_QUOTES, 'UTF-8'));
// $quantitativeWorksheet->getStyle("A$k")->getFont()->setBold(true);
$criteriaWorksheet->getColumnDimension("A")->setAutoSize(true);
$query = "SELECT comments FROM student_exam_sessions_responses WHERE question_ID = :id AND answer IN (SELECT value FROM assessment_criteria_scales_items WHERE assessment_criteria_scale_typeID = :criteriaid AND needs_comment ='true')";
$stmt = $conn->prepare($query);
$stmt->bindValue(':id', $question->id, PDO::PARAM_INT);
$stmt->bindValue(':criteriaid', $overviewXML->summary->scale_id, PDO::PARAM_STR);
$stmt->execute() or die('<data><error>select query failed query failed</error><detail>' . $stmt->errorCode() . '</detail></data>');
$resultArr = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$criteriaWorksheet->setCellValue("B{$k}", $row['comments']);
$k++;
}
$stmt->closeCursor();
}
}
}
// qualitative data
$overallCommentsWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Overall Comments');
$phpexcelObj->addSheet($overallCommentsWorksheet, 2);
// set labels
$overallCommentsWorksheet->getColumnDimension("A")->setWidth(200);
$overallCommentsWorksheet->setCellValue('A1', "Qualitative Overall Comments: {$overviewXML->summary->description} {$overviewXML->summary->examdate}, n=" . count($sessionsXML->session));
$overallCommentsWorksheet->getStyle('A1')->getFont()->setSize(16);
$overallCommentsWorksheet->setCellValue('A2', "Comments");
$overallCommentsWorksheet->getStyle("A2")->getFont()->setBold(true);
$k = 3;
//
foreach ($overviewXML->session as $session) {
if (strlen($session->comments) > 0) {
$overallCommentsWorksheet->setCellValue("A{$k}", html_entity_decode($session->comments, ENT_QUOTES, 'UTF-8'));
// $quantitativeWorksheet->getStyle("A$k")->getFont()->setBold(true);
$k++;
}
}
$rawWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Raw Scores Data Dump');
$phpexcelObj->addSheet($rawWorksheet, 3);
// assessment raw data
$rawWorksheet->setCellValue('A1', "Assessment Raw Data: {$overviewXML->summary->description} {$overviewXML->summary->examdate}");
$rawWorksheet->getStyle('A1')->getFont()->setSize(16);
$rawWorksheet->setCellValue('A2', "Student Number");
$rawWorksheet->getStyle('A2')->getFont()->setBold(true);
$rawWorksheet->setCellValue('B2', "Student Name");
$rawWorksheet->getStyle('B2')->getFont()->setBold(true);
$rawWorksheet->setCellValue('C2', "Site ID");
$rawWorksheet->getStyle('C2')->getFont()->setBold(true);
$rawWorksheet->setCellValue('D2', "Assessment Timestamp");
$rawWorksheet->getStyle('D2')->getFont()->setBold(true);
// $rawWorksheet->getColumnDimension('B')->setAutoSize(true);
// $rawWorksheet->getColumnDimension('C')->setAutoSize(true);
$questionscount = 0;
$questionsArr = array();
$k = 0;
// get all criteria labels
foreach ($questionsXML->question as $question) {
// $criterias = simplexml_load_string($enumlib->getCriteriaForQuestion($question->id, false));
// foreach ($criterias as $criteria) {
// print($criteria->text . '<br/>');
if ($question->type != 'label') {
$rawWorksheet->setCellValue($alphabetArr[$k] . '2', $question->text);
$rawWorksheet->getStyle($alphabetArr[$k] . '2')->getFont()->setBold(true);
$rawWorksheet->getColumnDimension($alphabetArr[$k])->setAutoSize(true);
$questionsArr[] = array("id" => (string) $question->id, "column" => $alphabetArr[$k]);
// get student results, loop to populate all answers to this criteria
// can probably do this better...
$questionscount++;
$k++;
}
// }
}
// print('questionsArr:<br/>');
// print_r($questionsArr);
// populate student details
$currentrow = 4;
foreach ($sessionsXML->session as $studentexamsession) {
//print('num'.$studentexamsession->studentnum);
$rawWorksheet->setCellValue('A' . $currentrow, $studentexamsession->studentnum);
$rawWorksheet->setCellValue('B' . $currentrow, $studentexamsession->fname . ' ' . $studentexamsession->lname);
$rawWorksheet->setCellValue('C' . $currentrow, $studentexamsession->siteid);
$rawWorksheet->setCellValue('D' . $currentrow, $studentexamsession->created_timedate);
$rawWorksheet->setCellValue('E' . $currentrow, $studentexamsession->created_timedate);
$query = "SELECT * FROM student_exam_sessions_responses WHERE student_exam_session_ID = :sessionid";
$stmt = $conn->prepare($query);
$stmt->bindValue(':sessionid', $studentexamsession->id, PDO::PARAM_INT);
$stmt->execute() or die('<data><error>>select query failed query failed</error><detail>' . $stmt->errorCode() . '</detail></data>');
$resultArr = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$resultArr[] = array("id" => $row['question_ID'], "answer" => $row['answer']);
}
for ($i = 0; $i < count($questionsArr); $i++) {
$rawWorksheet->setCellValue($questionsArr[$i]['column'] . $currentrow, $this->findAnswer($questionsArr[$i]['id'], $resultArr));
}
$currentrow++;
$stmt->closeCursor();
}
$phpexcelObj->setActiveSheetIndex(0);
return $phpexcelObj;
}
function reporte($buque, $viaje, $codPuerto)
{
global $linkSrv, $linkDB, $linkUsr, $linkPass, $connI;
// -------------------------------------------------------------------------------
// ODBC
// El sistema se trata de conectar mediante ODBC nativo a la base de datos MscLink
// -------------------------------------------------------------------------------
//$dsn = "Driver={SQL Server};Server=$linkSrv;Database=$linkDB;Integrated Security=SSPI;Persist Security Info=False;";
// Se realiza la conexón con los datos especificados anteriormente
//$connI = odbc_connect($dsn, $linkUsr, $linkPass);
//$connI2 = odbc_connect($dsn, $linkUsr, $linkPass);
// Incluir la libreria PHPExcel
require '../include/PHPExcel/PHPExcel.php';
// Reservar memoria en servidor PHP
// Si el archivo final tiene 5Mb, reservar 500Mb
// Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria
ini_set("memory_limit", "512M");
// Estilos Arreglo
$styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
$styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
// ----------------------------
// Hoja 1
// Nota : No usar acentos!!!
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Importacion');
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);
// HOJA 2
$workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Transbordos');
$objPHPExcel->addSheet($workSheet2, 1);
$workSheet2->getDefaultStyle()->getFont()->setSize(8);
$workSheet2->getDefaultStyle()->getFont()->setName('Arial');
// -----------------------------------------------
// ENCABEZADOS
// -----------------------------------------------
// Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas.
$arrHojas[] = 0;
$arrHojas[] = 1;
foreach ($arrHojas as $hoja) {
$objPHPExcel->setActiveSheetIndex($hoja);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:Q7')->applyFromArray($styleEnc);
// Encabezados
$headings = array('no', 'origen', 'regimen', 'destino', 'bl', 'contenedor', 'tipo', 'medida', 'iso', 'producto', 'paquetes', 'embalaje', 'peso', 'sello', 'transporte', 'un', 'imo');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:Q7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'MEDITERRANEAN SHIPPING COMPANY MEXICO, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'CONSTITUCION No.7 PLANTA BAJA');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'COL.BUROCRATA, MANZANILLO');
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A4', "{$buque} {$viaje}");
$objPHPExcel->getActiveSheet()->getStyle('A4')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Importacion / Transbordo');
$objPHPExcel->getActiveSheet()->getStyle('A5')->applyFromArray($styleSubtitulo);
}
// -----------------------------------
// HOJA 1
// -----------------------------------
$objPHPExcel->setActiveSheetIndex(0);
// Simple query
$sql = "\n SELECT\n -- EC.EventType,\n\tEC.POL as POL,\n\tBL.FDest as FDest,\n\tBL.Bill_Of_Lading_Number as Bl1,\n\tEC.EC_BIll_Of_Lading_Number as Bl2,\n\tEC.Equipment_Number as Conte,\n\tEC.EqType as TipoConte,\n\tEC.ET_Full_or_Empty as ISO,\n\tC.Ex_Short_Description as Producto,\n\tC.BL_Cargo_Packages as Paquetes,\n\tC.Ex_Package_Type_Code as Embalaje,\n\t-- C.Packages,\n\tC.TotCntr_Cargo_Weight_Kgs as Peso,\n\tC.Seal_No as Sello,\n\tdbo.FN_MX_ConcatenaBOLREP_Transport_Mode (C.BC_BOLREP_id, DEFAULT) AS TransMode,\n\t-- DTX_MOF_Name,\n\tR.IMO_Code as ImoCode,\n\tR.IMO_Class_Number as ImoCodeClass,\n\tR.UN_Number as UNNumber\n\t-- TS1.*\n FROM Interlink_DFeed90. dfeed.FNTB_Equipment_Cycle (0,null) EC\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Booking_BL_CSC (0,null) BL on BL.EQ_Cycle_id=EC.Eq_Cycle_id\n LEFT JOIN Interlink_Main90.dbo.Cargo_Shipment S (nolock) on S.Cargo_Shipment_id=BL.Cargo_Shipment_id\n left join Interlink_DFeed90. dfeed.FNTB_BOLREP_Container () C on EC.Equipment_Number=C.Container_Number and C.BC_BOLREP_id in (select max(BC_BOLREP_id) from Interlink_DFeed90.dfeed.FNTB_BOLREP_Container () where Container_Number=EC.Equipment_Number )\n LEFT JOIN Interlink_DFeed90. dfeed.FNTB_BOLREP_Cargo () R on R.BOLREP_Container_id=C.BOLREP_Container_id\n LEFT JOIN Interlink_DFeed90. dfeed.FNTB_AssocDocCompany( 'SHIPPER') ADC on EC.bill_of_lading_id=ADC.Bill_Of_Lading_id\n LEFT JOIN (\n SELECT TS.cargo_shipment_container_id , P1. EDI_Code TS1, P2.EDI_Code TS2, P3.EDI_Code TS3 , P4. EDI_Code TS4, P5 .EDI_Code TS5,\n CASE WHEN 'MX' IN (LEFT(P1. EDI_Code,2 ),LEFT(P2. EDI_Code,2 ),LEFT(P3. EDI_Code,2 ),LEFT(P4. EDI_Code,2 ),LEFT(P5. EDI_Code,2 )) THEN 1 ELSE 0 END Transshipment\n FROM Interlink_Main90. dbo.FNTB_TSPorts_CSC () TS\n LEFT JOIN Interlink_DFeed90.dfeed .FNTB_Port_EDI( 1,'UN CODE' ) P1 ON P1.port_id = TS.TS1\n LEFT JOIN Interlink_DFeed90.dfeed .FNTB_Port_EDI( 1,'UN CODE' ) P2 ON P2.port_id = TS.TS2\n LEFT JOIN Interlink_DFeed90.dfeed .FNTB_Port_EDI( 1,'UN CODE' ) P3 ON P3.port_id = TS.TS3\n LEFT JOIN Interlink_DFeed90.dfeed .FNTB_Port_EDI( 1,'UN CODE' ) P4 ON P4.port_id = TS.TS4\n LEFT JOIN Interlink_DFeed90.dfeed .FNTB_Port_EDI( 1,'UN CODE' ) P5 ON P5.port_id = TS.TS5\n ) TS1 ON TS1.cargo_shipment_container_id=C.Cargo_Shipment_Container_idx\n where EC.Vessel_Name='{$buque}' AND EC.Voyage='{$viaje}' and EC.PODUN='{$codPuerto}'\n AND EC.EventType in ('IDV','MDV','ILV')\n ";
$rs = odbc_exec($connI, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$lin = 1;
$rowNumber = 8;
// donde inicia los datos
while (odbc_fetch_row($rs)) {
$bl1 = odbc_result($rs, "Bl1");
$bl2 = odbc_result($rs, "Bl2");
$pol = odbc_result($rs, "POL");
$dest = odbc_result($rs, "FDest");
$conte = odbc_result($rs, "Conte");
$tipoConte = odbc_result($rs, "TipoConte");
$iso = odbc_result($rs, "ISO");
$producto = odbc_result($rs, "Producto");
$paquetes = odbc_result($rs, "Paquetes");
$embalaje = odbc_result($rs, "Embalaje");
$peso = odbc_result($rs, "Peso");
$sello = odbc_result($rs, "Sello");
$transMode = odbc_result($rs, "TransMode");
$UNNumber = odbc_result($rs, "UNNumber");
$imoCode = odbc_result($rs, "ImoCode");
$imoCodeClass = odbc_result($rs, "ImoCodeClass");
if (!empty($bl1)) {
$bl = $bl1;
} else {
$bl = $bl2;
}
if (preg_match("/(\\d{2,2})(.+)/i", $tipoConte, $parts)) {
$tipo = $parts[1];
$medida = $parts[2];
}
unset($parts);
// Regimen
if (preg_match("/MAZATLAN|GUAYMAS/i", $dest)) {
$regimen = "T-S";
} else {
$regimen = "IMP";
}
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $lin)->setCellValue('B' . $rowNumber, $pol)->setCellValue('C' . $rowNumber, $regimen)->setCellValue('D' . $rowNumber, $dest)->setCellValue('E' . $rowNumber, $bl)->setCellValue('F' . $rowNumber, $conte)->setCellValue('G' . $rowNumber, $tipo)->setCellValue('H' . $rowNumber, $medida)->setCellValue('I' . $rowNumber, $iso)->setCellValue('J' . $rowNumber, $producto)->setCellValue('K' . $rowNumber, $paquetes)->setCellValue('L' . $rowNumber, $embalaje)->setCellValue('M' . $rowNumber, $peso)->setCellValue('N' . $rowNumber, $sello)->setCellValue('O' . $rowNumber, $transMode)->setCellValue('P' . $rowNumber, $UNNumber)->setCellValue('Q' . $rowNumber, $imoCodeClass)->setCellValue('R' . $rowNumber, $imoCode);
$rowNumber++;
$lin++;
}
// ------------------------------
// Hoja 2 - TRANSBORDOS
// ------------------------------
$objPHPExcel->setActiveSheetIndex(1);
// Simple query
$sql = "\n SELECT\n\tE.Orig_POL as POL,\n\tE.POD as FDest,\n\tE.Bill_of_Lading_Number as Bl1,\n\tE.Equipment_Number as Conte,\n\tE.EQ_Type as TipoConte,\n\tE.IsEmpty as IsEmpty,\n\tE.Ex_Cargo_Short_Description as Producto,\n\tE.Cargo_Packages as Paquetes,\n\tE.Ex_Cargo_Package_Type as Embalaje,\n\tE.Cargo_Weight_Kgs as Peso,\n\tdbo.FN_MX_ConcatenaBOLREP_Transport_Mode (C.BC_BOLREP_id, DEFAULT) AS TransMode,\n\tR.IMO_Code as ImoCode,\n\tR.IMO_Class_Number as ImoCodeClass,\n\tR.UN_Number as UNNumber,\n\tC.Seal_No as Sello\n FROM\n Interlink_DFeed90.dfeed.TSHIP_FNTB_Equipment (NULL, NULL) E\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_BOLREP_Container () C ON E.Equipment_Number = C.Container_Number\n AND C.BC_BOLREP_id IN (\n SELECT\n MAX (BC_BOLREP_id)\n FROM\n Interlink_DFeed90.dfeed.FNTB_BOLREP_Container ()\n WHERE\n Container_Number = E.Equipment_Number\n )\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_BOLREP_Cargo () R ON C.BOLREP_Container_id = R.BOLREP_Container_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Booking_BL (NULL, NULL) Bl ON E.Bill_Of_Lading_Number = Bl.Bill_Of_Lading_Number\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ('SHIPPER') ADS ON Bl.Bill_Of_Lading_id = ADS.Bill_Of_Lading_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ('CONSIGN') ADC ON Bl.Bill_Of_Lading_id = ADC.Bill_Of_Lading_id\n WHERE\n E.VPCDisch_Vessel_Name = '{$buque}'\n AND E.VPCDisch_Voyage = '{$viaje}'\n AND E.VPCDisch_UNCode = '{$codPuerto}'\n ";
$rs = odbc_exec($connI, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$lin = 1;
$rowNumber = 8;
// donde inicia los datos
while (odbc_fetch_row($rs)) {
$bl1 = odbc_result($rs, "Bl1");
//$bl2 = odbc_result($rs, "Bl2");
$pol = odbc_result($rs, "POL");
$dest = odbc_result($rs, "FDest");
$conte = odbc_result($rs, "Conte");
$tipoConte = odbc_result($rs, "TipoConte");
$isEmpty = odbc_result($rs, "IsEmpty");
if ($isEmpty == 1) {
$iso = "E";
} else {
$iso = "F";
}
$producto = odbc_result($rs, "Producto");
$paquetes = odbc_result($rs, "Paquetes");
$embalaje = odbc_result($rs, "Embalaje");
$peso = odbc_result($rs, "Peso");
$sello = odbc_result($rs, "Sello");
$transMode = odbc_result($rs, "TransMode");
$UNNumber = odbc_result($rs, "UNNumber");
$imoCode = odbc_result($rs, "ImoCode");
$imoCodeClass = odbc_result($rs, "ImoCodeClass");
if (!empty($bl1)) {
$bl = $bl1;
} else {
$bl = $bl2;
}
if (preg_match("/(\\d{2,2})(.+)/i", $tipoConte, $parts)) {
$tipo = $parts[1];
$medida = $parts[2];
}
unset($parts);
// Regimen
if (preg_match("/MAZATLAN|GUAYMAS/i", $dest)) {
$regimen = "T-S";
} else {
$regimen = "IMP";
}
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $lin)->setCellValue('B' . $rowNumber, $pol)->setCellValue('C' . $rowNumber, $regimen)->setCellValue('D' . $rowNumber, $dest)->setCellValue('E' . $rowNumber, $bl)->setCellValue('F' . $rowNumber, $conte)->setCellValue('G' . $rowNumber, $tipo)->setCellValue('H' . $rowNumber, $medida)->setCellValue('I' . $rowNumber, $iso)->setCellValue('J' . $rowNumber, $producto)->setCellValue('K' . $rowNumber, $paquetes)->setCellValue('L' . $rowNumber, $embalaje)->setCellValue('M' . $rowNumber, $peso)->setCellValue('N' . $rowNumber, $sello)->setCellValue('O' . $rowNumber, $transMode)->setCellValue('P' . $rowNumber, $UNNumber)->setCellValue('Q' . $rowNumber, $imoCodeClass)->setCellValue('R' . $rowNumber, $imoCode);
$rowNumber++;
$lin++;
}
// Loregreso a la hoja 1.
$objPHPExcel->setActiveSheetIndex(0);
// Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attachment;filename="repForCapitania.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
function reporte($fec1 = "", $fec2 = "", $IE, $oficina = "", $usrCap = "", $dtxFlg = "")
{
global $connI, $searchConte, $dbS, $dbS2;
$reportName = "Costos por contenedor";
// Incluir la libreria PHPExcel
require '../include/PHPExcel/PHPExcel.php';
// Reservar memoria en servidor PHP
// Si el archivo final tiene 5Mb, reservar 500Mb
// Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria
ini_set("memory_limit", "512M");
// Estilos Arreglo
$styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
$styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
// ----------------------------
// Hoja 1
// Nota : No usar acentos!!!
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Hoja1');
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);
// HOJA 2
$workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Hoja2');
$objPHPExcel->addSheet($workSheet2, 1);
$workSheet2->getDefaultStyle()->getFont()->setSize(8);
$workSheet2->getDefaultStyle()->getFont()->setName('Arial');
// -----------------------------------------------
// ENCABEZADOS
// -----------------------------------------------
// Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas.
$objPHPExcel->setActiveSheetIndex(0);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:AK7')->applyFromArray($styleEnc);
// Encabezados
$headings = array('No', 'Contenedeor', 'Tipo', 'BL', 'Buque', 'Viaje', 'ETA', 'POL', 'POD', 'IE', 'Terr_PP', 'Terr_CC', 'Terr_EE', '20s', '40s', 'SH/CNG', 'BKP', 'I-ONC', 'CUR', 'CH.Applic', 'Payment_Type', 'E-PRE', 'CUR', 'CH.Applic', 'Payment_Type', 'THC', 'CUR', 'CH.Applic', 'Payment_Type', 'Coor.Logistica', 'CUR', 'CH.Applic', 'Payment_Type', 'Pago de Maniobras', 'CUR', 'CH.Applic', 'Payment_Type');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:AK7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(45);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(45);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('AF')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('AG')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('AH')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('AI')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('AJ')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('AK')->setWidth(15);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', "Solo como Agentes / As Agent only");
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', "{$reportName}");
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A6', "{$buque} {$viaje}");
$objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo);
// -----------------------------------
// HOJA 1
// -----------------------------------
$objPHPExcel->setActiveSheetIndex(0);
unset($arrBls);
if (!empty($searchConte)) {
$arrBls = explode("\r\n", $searchConte);
foreach ($arrBls as $cte) {
$cte = trim($cte);
$in .= "'{$cte}',";
}
$in = substr($in, 0, -1);
}
//echo $in;
$sql = "\n select c.numero as contenedor,q.equipo,\n b.Nombre as buque,b.Viaje,b.Eta1 as ETA,b.Impo_expo as IE,\n e.Bl,Shipper,C20,C40,Loading as POL,Discharge as POD,Precarr_pp,Precarr_cc,Precarr_else \n from EXPO e\n INNER JOIN BARCO b ON e.Id_barco = b.Id_barco\n inner join BL_CONTE bc ON e.Id_expo = bc.id_bl\n inner JOIN CONTENEDOR c ON bc.id_contenedor = c.id_contenedor\n INNER JOIN EQUIPO q ON c.id_equipo=q.id_equipo\n where b.Eta1 >= '2015-01-01' and c.numero in ({$in})\n ";
$dbS->query($sql);
$lin = 1;
$rowNumber = 8;
// donde inicia los datos
while ($dbS->next_record()) {
$user = $dbS->f('Nombre');
$bl = $dbS->f('Bl');
$conte = $dbS->f('contenedor');
$sql2 = "select bkg_party from DOC_CTRL_EXPO where bl='{$bl}'";
$dbS2->query($sql2);
while ($dbS2->next_record()) {
$bkgParty = $dbS2->f('bkg_party');
}
// --------------------
// Consultas a MSCLINK
// Bl-Contenedor forzoso
// --------------------
// $sqlL = "
// SELECT
// tt.Remarks as Remarks,
// t.Bill_Of_Lading_Number as BL,
// t.Booking_Number as BKG,
// t.Container_Number as CONTE,
// t.I_E as IE,
// t.Charge as Charge,
// t.Result_Std as Result_Std,
// t.Payment_Type as Payment_Type,
// t.Currency as Currency,
// t.Charge_Applicable_At,
// t.Charge_DTX_Code as dtxCode
// FROM Interlink_Dfeed90. dfeed.FNTB_Charge(0,null) t
// inner JOIN Interlink_Main90.dbo.Charge tt ON t.Charge_id = tt.Charge_id
// where
// t.Container_Number='$conte'
// and
// t.Bill_Of_Lading_Number like '%$bl';
// ";
$sqlL = "\n SELECT \n t.Bill_Of_Lading_Number as BL,\n t.Booking_Number as BKG,\n t.Container_Number as CONTE,\n t.I_E as IE,\n t.Charge as Charge,\n t.Result_Std as Result_Std,\n t.Payment_Type as Payment_Type,\n t.Currency as Currency,\n t.Charge_Applicable_At,\n t.Charge_DTX_Code as dtxCode\n FROM Interlink_Dfeed90. dfeed.FNTB_Charge(0,null) t \n where \n t.Container_Number='{$conte}'\n and \n t.Bill_Of_Lading_Number in ('MSCU{$bl}','MEDU{$bl}'); \n ";
$rs = odbc_exec($connI, $sqlL);
unset($cl);
unset($clCUR);
unset($clAP);
unset($clPT);
unset($mani);
unset($maniCUR);
unset($maniAP);
unset($maniPT);
while (odbc_fetch_row($rs)) {
$dtxCode = odbc_result($rs, 'dtxCode');
// $Remarks = odbc_result( $rs ,'Remarks');
$Charge = odbc_result($rs, 'Charge');
$Result_Std = odbc_result($rs, 'Result_Std');
$Payment_Type = odbc_result($rs, 'Payment_Type');
$Currency = odbc_result($rs, 'Currency');
$Charge_Applicable_At = odbc_result($rs, 'Charge_Applicable_At');
// $ = odbc_result($rs,'');
if ($Charge == 'COORDINACION LOGISTICA') {
$cl = $Result_Std;
$clCUR = $Currency;
$clAP = $Charge_Applicable_At;
$clPT = $Payment_Type;
} elseif ($Charge == 'PAGO MANIOBRAS') {
$mani = $Result_Std;
$maniCUR = $Currency;
$maniAP = $Charge_Applicable_At;
$maniPT = $Payment_Type;
}
}
// --------------------------------------------------------------------------------------------------------------
// OncCarriage
// Nota: Se tuvo que realizar otro query por que no se especifica contenedor cuando los cargos son manifestados.
// Att. nestor.
// --------------------------------------------------------------------------------------------------------------
// $sqlL="
// select
// t.Result_Std as Result_Std,
// t.Payment_Type as Payment_Type,
// t.Currency as Currency,
// t.Charge_Applicable_At,
// t.Charge_DTX_Code
// FROM Interlink_Dfeed90. dfeed.FNTB_Charge(0,null) t
// inner JOIN Interlink_Main90.dbo.Charge tt ON t.Charge_id = tt.Charge_id
// where
// t.Bill_Of_Lading_Number like '%$bl'
// ";
$sqlL = "\n select\n t.Result_Std as Result_Std,\n t.Payment_Type as Payment_Type,\n t.Currency as Currency,\n t.Charge_Applicable_At,\n t.Charge_DTX_Code\n FROM Interlink_Dfeed90. dfeed.FNTB_Charge(0,null) t \n where \n t.Bill_Of_Lading_Number in ('MSCU{$bl}','MEDU{$bl}')\n ";
$rs = odbc_exec($connI, $sqlL);
unset($onc);
unset($oncCUR);
unset($oncAP);
unset($oncPT);
unset($pre);
unset($preCUR);
unset($preAP);
unset($prePT);
while (odbc_fetch_row($rs)) {
$dtxCode = odbc_result($rs, 'Charge_DTX_Code');
switch ($dtxCode) {
case 'ONC':
$onc = odbc_result($rs, 'Result_Std');
$oncCUR = odbc_result($rs, 'Currency');
$oncAP = odbc_result($rs, 'Charge_Applicable_At');
$oncPT = odbc_result($rs, 'Payment_Type');
break;
case 'PRE':
$pre = odbc_result($rs, 'Result_Std');
$preCUR = odbc_result($rs, 'Currency');
$preAP = odbc_result($rs, 'Charge_Applicable_At');
$prePT = odbc_result($rs, 'Payment_Type');
break;
case 'THC':
$thc = odbc_result($rs, 'Result_Std');
$thcCUR = odbc_result($rs, 'Currency');
$thcAP = odbc_result($rs, 'Charge_Applicable_At');
$thcPT = odbc_result($rs, 'Payment_Type');
break;
default:
# code...
break;
}
}
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $lin)->setCellValue('B' . $rowNumber, $dbS->f('contenedor'))->setCellValue('C' . $rowNumber, $dbS->f('equipo'))->setCellValue('D' . $rowNumber, $bl)->setCellValue('E' . $rowNumber, $dbS->f('buque'))->setCellValue('F' . $rowNumber, $dbS->f('Viaje'))->setCellValue('G' . $rowNumber, $dbS->f('ETA'))->setCellValue('H' . $rowNumber, $dbS->f('POL'))->setCellValue('I' . $rowNumber, $dbS->f('POD'))->setCellValue('J' . $rowNumber, $dbS->f('IE'))->setCellValue('K' . $rowNumber, $dbS->f('Precarr_pp'))->setCellValue('L' . $rowNumber, $dbS->f('Precarr_cc'))->setCellValue('M' . $rowNumber, $dbS->f('Precarr_else'))->setCellValue('N' . $rowNumber, $dbS->f('C20'))->setCellValue('O' . $rowNumber, $dbS->f('C40'))->setCellValue('P' . $rowNumber, $dbS->f('Shipper'))->setCellValue('Q' . $rowNumber, $bkgParty)->setCellValue('R' . $rowNumber, $onc)->setCellValue('S' . $rowNumber, $oncCUR)->setCellValue('T' . $rowNumber, $oncAP)->setCellValue('U' . $rowNumber, $oncPT)->setCellValue('V' . $rowNumber, $pre)->setCellValue('W' . $rowNumber, $preCUR)->setCellValue('X' . $rowNumber, $preAP)->setCellValue('Y' . $rowNumber, $prePT)->setCellValue('Z' . $rowNumber, $thc)->setCellValue('AA' . $rowNumber, $thcCUR)->setCellValue('AB' . $rowNumber, $thcAP)->setCellValue('AC' . $rowNumber, $thcPT)->setCellValue('AD' . $rowNumber, $cl)->setCellValue('AE' . $rowNumber, $clCUR)->setCellValue('AF' . $rowNumber, $clAP)->setCellValue('AG' . $rowNumber, $clPT)->setCellValue('AH' . $rowNumber, $mani)->setCellValue('AI' . $rowNumber, $maniCUR)->setCellValue('AJ' . $rowNumber, $maniAP)->setCellValue('AK' . $rowNumber, $maniPT);
$rowNumber++;
$lin++;
}
// Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attachment;filename="repFactuMLink.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
$value = $current_sheet->getCell($adr)->getValue();
if ($c_i == 'B' && empty($value)) {
break;
}
if (is_object($value)) {
$value = $value->__toString();
}
$c_arr[$c_i] = $value;
}
$c_arr && ($all_arr[] = $c_arr);
}
//读取完毕=============================================
//准备写入
$msgWorkSheet = new PHPExcel_Worksheet($excel_obj, strval($i));
//创建一个工作表
$excel_obj->addSheet($msgWorkSheet);
//插入工作表
$excel_obj->setActiveSheetIndex($i);
$act_sheet_obj = $excel_obj->getActiveSheet();
//内容处理，并写入
foreach ($all_arr as $k => $v) {
if ($k != 0) {
$act_sheet_obj->setCellValue('A' . ($k + 1), $v['A']);
$act_sheet_obj->setCellValue('B' . ($k + 1), base_decode($v['B']));
$act_sheet_obj->setCellValue('C' . ($k + 1), $v['C']);
$act_sheet_obj->setCellValue('D' . ($k + 1), date('Y-m-d', $v['D']));
$act_sheet_obj->setCellValue('E' . ($k + 1), date('Y-m-d H:i:s', $v['E']));
$act_sheet_obj->setCellValue('F' . ($k + 1), $reg_host[$v['F']]);
$act_sheet_obj->setCellValue('G' . ($k + 1), $v['G']);
$act_sheet_obj->setCellValue('H' . ($k + 1), $v['H']);
$act_sheet_obj->setCellValue('I' . ($k + 1), date('Y-m-d H:i:s', $v['I']));
function reporte($vessel, $voyage, $pol = '', $pod = '')
{
global $linkSrv, $linkDB, $linkUsr, $linkPass;
// -------------------------------------------------------------------------------
// ODBC
// El sistema se trata de conectar mediante ODBC nativo a la base de datos MscLink
// -------------------------------------------------------------------------------
$dsn = "Driver={SQL Server};Server={$linkSrv};Database={$linkDB};Integrated Security=SSPI;Persist Security Info=False;";
// Se realiza la conexón con los datos especificados anteriormente
$conn = odbc_connect($dsn, $linkUsr, $linkPass);
$conn2 = odbc_connect($dsn, $linkUsr, $linkPass);
// Incluir la libreria PHPExcel
require '../include/PHPExcel/PHPExcel.php';
// Reservar memoria en servidor PHP
// Si el archivo final tiene 5Mb, reservar 500Mb
// Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria
ini_set("memory_limit", "512M");
// Estilos Arreglo
$styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
$styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
// ----------------------------
// Hoja 1
// Nota : No usar acentos!!!
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Hoja1');
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);
// HOJA 2
$workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Hoja2');
$objPHPExcel->addSheet($workSheet2, 1);
$workSheet2->getDefaultStyle()->getFont()->setSize(8);
$workSheet2->getDefaultStyle()->getFont()->setName('Arial');
// -----------------------------------------------
// ENCABEZADOS
// -----------------------------------------------
// Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas.
$arrHojas[] = 0;
//$arrHojas[]=1;
foreach ($arrHojas as $hoja) {
$objPHPExcel->setActiveSheetIndex($hoja);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:M7')->applyFromArray($styleEnc);
// Encabezados
$headings = array('BL', 'Vessel', 'Voyage', 'Date', 'Amount', 'CheckNumber', 'PayerName', 'Tracking_Number', 'Received_Via', 'Remarks', 'POL', 'POD', 'Comments');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:M7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(100);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. de C.V.')->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'X-Tractor Version 1.0.');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Reporte de cobranza por Barco');
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A4', "");
$objPHPExcel->getActiveSheet()->getStyle('A4')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A5', '');
$objPHPExcel->getActiveSheet()->getStyle('A5')->applyFromArray($styleSubtitulo);
}
/*
// -------------------------
// Encabezado de la hoja 2
// -------------------------
$objPHPExcel->setActiveSheetIndex(1);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:S7')->applyFromArray($styleEnc);
// Encabezados
$headings = array('Bl', 'Contenedor', 'Concepto', 'Evento', 'Ev.Tiempo', 'EventoFin', 'EventoFin.Tiempo','POD','TMSCodeShp','Shipper','Inv.Company','DiasLibres','Invoice','Doc.Date','Amount','InvNum','TotInv','PaidInv','OustInv');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:S7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(25);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'MEDITERRANEAN SHIPPING COMPANY MEXICO, S.A. DE C.V.')
->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', '.');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', '.');
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A4', ".");
$objPHPExcel->getActiveSheet()->getStyle('A4')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Depositos en Garantia MscLink');
$objPHPExcel->getActiveSheet()->getStyle('A5')->applyFromArray($styleSubtitulo);
// ----------------------------------------------------------------------------
*/
// -----------------------------------
// HOJA 1
// -----------------------------------
$vessel = trim($vessel);
$voyage = trim($voyage);
$objPHPExcel->setActiveSheetIndex(0);
// Simple query
$sql = "\n SELECT\n\tBL.Bill_Of_Lading_Number as bl,\n\tBL2.Vessel_Name as vessel,\n\tBL2.Voyage as voyage,\n\tM.DATE as fecha,\n\tM.Amount as amount,\n\tM.Check_Number as checkNum,\n\tM.Payer_Name as payer,\n\tM.Tracking_Number as tnumber,\n\tM.Received_Via as received_via,\n\tM.Remarks as remarks,\n\tBL2.POLUN as polun,\n\tBL2.PODUN as podun\n\n FROM\n Interlink_DFeed90.dfeed.FNTB_Bill_Of_Lading_to_Booking () BL\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Bill_Of_Lading (NULL, NULL) BL2 ON BL.Bill_Of_Lading_id = BL2.Bill_Of_Lading_id\n LEFT JOIN Interlink_Main90.dbo.BL_Money_Received M (NOLOCK) ON BL.Bill_Of_Lading_id = M.Bill_Of_Lading_id\n -- LEFT JOIN Interlink_Main90.dbo.BL_Comment C (NOLOCK) ON BL.Bill_Of_Lading_id = C.Bill_Of_Lading_id\n WHERE\n BL2.Vessel_Name='{$vessel}'\n AND BL2.Voyage='{$voyage}' ";
if (!empty($pod)) {
$sql .= "AND BL2.PODUN='{$pod}' ";
}
if (!empty($pol)) {
$sql .= "AND BL2.POLUN='{$pol}' ";
}
$rs = odbc_exec($conn, $sql);
$rowNumber = 8;
// donde inicia los datos
while (odbc_fetch_row($rs)) {
$c1 = odbc_result($rs, "bl");
$c2 = odbc_result($rs, "vessel");
$c3 = odbc_result($rs, "voyage");
$c4 = odbc_result($rs, "fecha");
$c5 = odbc_result($rs, "amount");
$c6 = odbc_result($rs, "checkNum");
$c7 = odbc_result($rs, "payer");
$c8 = odbc_result($rs, "tnumber");
$c9 = odbc_result($rs, "received_via");
$c10 = odbc_result($rs, "remarks");
$pol = odbc_result($rs, "polun");
$pod = odbc_result($rs, "podun");
// Buscar y concatenar los comentarios del BL.
$nota = "";
$sql2 = "\n SELECT\n C.Comment as nota\n --,C.CreatedTime\n FROM\n Interlink_DFeed90.dfeed.FNTB_Bill_Of_Lading_to_Booking () BL\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Bill_Of_Lading (NULL, NULL) BL2 ON BL.Bill_Of_Lading_id = BL2.Bill_Of_Lading_id\n -- LEFT JOIN Interlink_Main90.dbo.BL_Money_Received M (NOLOCK) ON BL.Bill_Of_Lading_id = M.Bill_Of_Lading_id\n LEFT JOIN Interlink_Main90.dbo.BL_Comment C (NOLOCK) ON BL.Bill_Of_Lading_id = C.Bill_Of_Lading_id\n WHERE\n BL.Bill_Of_Lading_Number = '{$c1}';\n ";
$rs2 = odbc_exec($conn2, $sql2);
while (odbc_fetch_row($rs2)) {
$nota .= odbc_result($rs2, "nota");
}
$nota = addslashes($nota);
$nota = normaliza($nota);
$c7 = normaliza($c7);
// Quitar acentos
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $c1)->setCellValue('B' . $rowNumber, $c2)->setCellValue('C' . $rowNumber, $c3)->setCellValue('D' . $rowNumber, $c4)->setCellValue('E' . $rowNumber, $c5)->setCellValue('F' . $rowNumber, $c6)->setCellValue('G' . $rowNumber, $c7)->setCellValue('H' . $rowNumber, $c8)->setCellValue('I' . $rowNumber, $c9)->setCellValue('J' . $rowNumber, $c10)->setCellValue('K' . $rowNumber, $pol)->setCellValue('L' . $rowNumber, $pod)->setCellValue('M' . $rowNumber, $nota);
$rowNumber++;
}
/*
// ------------------------------
// Hoja 2 - DEMORAS
// ------------------------------
$sqlBL="";
foreach( $arrBls as $bl ) {
if (!empty($bl)) {
$bl = str_replace("\n", "", $bl);
$bl = str_replace("\r", "", $bl);
$bl = trim($bl);
$sqlBL.="'$bl',";
}
}
$blsInTxt = substr($sqlBL,0,-1);
$objPHPExcel->setActiveSheetIndex(1);
// Simple query
$sql = "
SELECT
CH.Show_as AS CONCEPTO,
CH.Start_Event AS EVENTO,
CH.start_event_datetime AS EVENTOTIEMPO,
CH.End_Event AS ENVENTOFIN,
CH.end_event_datetime AS EVENTOFINTIEMPO,
CH.POD_name AS PODNAME,
CH.Bill_Of_Lading_Number AS BL,
CH.Equipment_Number AS CONTE,
ADCSH.MSCCode AS TMSCODESHI,
ADCCN.Company AS SHIPPER,
ADCIC.Company AS INVCOMPANY,
CH.Charge_Level_0_Days AS DIASLIBRES,
CH.invoice_number AS INVOICE,
I.Doc_Date AS DOCDATE,
CH.Charge_Amount AS AMOUNTX,
(select I.Invoice_Number
from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )
left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id
where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)
where Invoice_Number=CH.invoice_number)) as InvNum,
(select (AI . Inv_Total_Inv_Currency + AI. Inv_Total_VAT_Inv_Currency) Total
from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )
left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id
where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)
where Invoice_Number=CH.invoice_number)) as TotInv,
(select I.Paid
from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )
left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id
where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)
where Invoice_Number=CH.invoice_number)) as PaidInv,
(select ( AI .Inv_Total_Inv_Currency + AI. Inv_Total_VAT_Inv_Currency - I .Paid )
from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )
left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id
where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)
where Invoice_Number=CH.invoice_number )) as OustInv
FROM
Interlink_DFeed90.dfeed.ILIM_FNTB_Charges () CH
INNER JOIN Interlink_Main90.dbo.Invoice I (NOLOCK) ON I.Invoice_id = CH.Invoice_id
LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('CONSIGN') ADCCN ON ADCCN.Bill_Of_Lading_id = CH.bill_of_lading_id
LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('SHIPPER') ADCSH ON ADCSH.Bill_Of_Lading_id = CH.bill_of_lading_id
LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('INVCMPBL') ADCIC ON ADCIC.Bill_Of_Lading_id = CH.bill_of_lading_id
WHERE
Show_as = 'DEMURRAGE'
AND CH.Bill_Of_Lading_Number in ($blsInTxt)
AND Charge_Status IN ('INVOICED')
";
$rs = odbc_exec($conn, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$rowNumber = 8; // donde inicia los datos
while ( odbc_fetch_row($rs) ) {
$c1 = odbc_result($rs, "BL");
$c2 = odbc_result($rs, "CONTE");
$c3 = odbc_result($rs, "CONCEPTO");
$c4 = odbc_result($rs, "EVENTO");
$c5 = odbc_result($rs, "EVENTOTIEMPO");
$c6 = odbc_result($rs, "ENVENTOFIN");
$c7 = odbc_result($rs, "EVENTOFINTIEMPO");
$c8 = odbc_result($rs, "PODNAME");
$c9 = odbc_result($rs, "TMSCODESHI");
$c10 = odbc_result($rs, "SHIPPER");
$c11 = odbc_result($rs, "INVCOMPANY");
$c12 = odbc_result($rs, "DIASLIBRES");
$c13 = odbc_result($rs, "INVOICE");
$c14 = odbc_result($rs, "DOCDATE");
$c15 = odbc_result($rs, "AMOUNTX");
$c16 = odbc_result($rs, "InvNum");
$c17 = odbc_result($rs, "TotInv");
$c18 = odbc_result($rs, "PaidInv");
$c19 = odbc_result($rs, "OustInv");
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$rowNumber,$c1)
->setCellValue('B'.$rowNumber,$c2)
->setCellValue('C'.$rowNumber,$c3)
->setCellValue('D'.$rowNumber,$c4)
->setCellValue('E'.$rowNumber,$c5)
->setCellValue('F'.$rowNumber,$c6)
->setCellValue('G'.$rowNumber,$c7)
->setCellValue('H'.$rowNumber,$c8)
->setCellValue('I'.$rowNumber,$c9)
->setCellValue('J'.$rowNumber,$c10)
->setCellValue('K'.$rowNumber,$c11)
->setCellValue('L'.$rowNumber,$c12)
->setCellValue('M'.$rowNumber,$c13)
->setCellValue('N'.$rowNumber,$c14)
->setCellValue('O'.$rowNumber,$c15)
->setCellValue('P'.$rowNumber,$c16)
->setCellValue('Q'.$rowNumber,$c17)
->setCellValue('R'.$rowNumber,$c18)
->setCellValue('S'.$rowNumber,$c19)
;
$rowNumber++;
}
// Loregreso a la hoja 1.
$objPHPExcel->setActiveSheetIndex(0);
*/
// Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attachment;filename="x-tractor.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
function almacenarCSV()
{
global $connI;
if (is_uploaded_file($_FILES['userfile']['tmp_name'])) {
copy($_FILES['userfile']['tmp_name'], "../files/blConteIMO{$mscIdUsuario}.csv");
} else {
$msg = "<h1><font color=\"red\">Error en el envio del archivo, vuela a intentarlo! " . $_FILES['userfile']['name'];
$msg .= "</font></h1>";
echo $msg;
}
$fp = fopen("../files/blConteIMO{$mscIdUsuario}.csv", "r");
$validFile = "True";
$l = 1;
while ($data = fgetcsv($fp, 1000, ";")) {
// La linea 4 es donde esta el encabezado
if ($l == 1) {
foreach ($data as $campo) {
$campo = addslashes($campo);
//echo $campo."<br>";
$campo = ereg_replace("\n", "", $campo);
$campo = ereg_replace("\r", "", $campo);
$data[] = $campo;
}
$data = $data;
// Encabezado del archivo de Excel.
if ($data[0] != "BL") {
$validFile = "False";
break;
}
if ($data[1] != "CONTENEDOR") {
$validFile = "False";
break;
}
$data = "";
// rompe para que ya no siga con las demas lineas, solo queriamos la linea 3.
// y con esto aguilizamos el proceso.
break;
}
// fin del if
$l++;
// incrementa la linea.
}
// fin del while
fclose($fp);
// Verificar si el encabezado del archivo es correcto.
if ($validFile == "False") {
$msg = "Archivo corrupto, o los encabezados han cambiado, favor de verificar.";
$error[archivo] = $msg;
echo "<font color=\"red\">{$msg}<p></p></font>";
return $error;
} else {
$reportName = "IMO / Sobredimensionado en MscLink";
// Incluir la libreria PHPExcel
require '../include/PHPExcel/PHPExcel.php';
// Reservar memoria en servidor PHP
// Si el archivo final tiene 5Mb, reservar 500Mb
// Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria
ini_set("memory_limit", "512M");
// Estilos Arreglo
$styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
$styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
// ----------------------------
// Hoja 1
// Nota : No usar acentos!!!
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Hoja1');
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);
// HOJA 2
$workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Hoja2');
$objPHPExcel->addSheet($workSheet2, 1);
$workSheet2->getDefaultStyle()->getFont()->setSize(8);
$workSheet2->getDefaultStyle()->getFont()->setName('Arial');
// -----------------------------------------------
// ENCABEZADOS
// -----------------------------------------------
// Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas.
$objPHPExcel->setActiveSheetIndex(0);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:K7')->applyFromArray($styleEnc);
// Encabezados
$headings = array('BL', 'Contenedor', 'IMO_Number', 'Fumigation_Residue_IMO_Class', 'OD_Height', 'OD_Length_Front', 'OD_Length_Back', 'OD_Width_Right', 'OD_Width_Left', 'Dimension_Unit', 'Is_OverDimension');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:K7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', "Solo como Agentes / As Agent only");
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', "{$reportName}");
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
/*$objPHPExcel->getActiveSheet()->setCellValue('A6', "$buque $viaje");
$objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo);*/
$rowNumber = 8;
// donde inicia los datos
//echo "<b>Ok.......</b><br>";
// por default flgRec es igual a 1.
// Asume que estan bien los datos para proceder a grabar.
// Hasta que en la validacion se cambie a 0, entonces ya no graba, (ver mas abajo).
$flgRec = 1;
$fp = fopen("../files/blConteIMO{$mscIdUsuario}.csv", "r");
$linea = 1;
$e = 0;
// elemento del arreglo.
while ($dataY = fgetcsv($fp, 1000, ";")) {
// Se inicializan las vars como vacias para evitar errores.
$flgRec = "";
//echo $dataY[0]."<br>";
// La linea 1 es donde esta el encabezado
if ($linea > 1) {
for ($i = 0; $i < count($dataY); $i++) {
$campo = ereg_replace("\n", "", $dataY[$i]);
$campo = ereg_replace("\r", "", $dataY[$i]);
$data[$i] = $campo;
}
$data = $data;
$datoX[contenedor] = trim(strtoupper($data[1]));
$bl = trim(strtoupper($data[0]));
$datoX[bl] = getValidBl($bl);
// --- INICIA PROCESO DE VALIDACION ---
// VALIDAR CONTENEDOR
$conte = $datoX[contenedor];
// --- VALIDAR BL ---
$bl = $datoX[bl];
//echo "$bl / $conte <br>";
// -----------------------------------
// HOJA 1
// -----------------------------------
$objPHPExcel->setActiveSheetIndex(0);
$sql = "SELECT BL.Bill_Of_Lading_Number, BL.Assigned_Container_Number, BL.IMO_Number, BL.Fumigation_Residue_IMO_Class, BL. OD_Height, BL.OD_Length_Front, BL.OD_Length_Back, BL. OD_Width_Right, \n BL. OD_Width_Left, BL.OD_Unit as Dimension_Unit, BL.Is_OverDimension\n FROM Interlink_DFeed90.dfeed .FNTB_Booking_BL_CSC (0, NULL) BL\n WHERE BL.Bill_Of_Lading_Number='{$bl}' and bl.Assigned_Container_Number='{$conte}' \n ";
$rs = odbc_exec($connI, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$lin = 1;
//$rowNumber = 8; // donde inicia los datos
while (odbc_fetch_row($rs)) {
$Bill_Of_Lading_Number = odbc_result($rs, "Bill_Of_Lading_Number");
$Assigned_Container_Number = odbc_result($rs, "Assigned_Container_Number");
$IMO_Number = odbc_result($rs, "IMO_Number");
$Fumigation_Residue_IMO_Class = odbc_result($rs, "Fumigation_Residue_IMO_Class");
$OD_Height = odbc_result($rs, "OD_Height");
$OD_Length_Front = odbc_result($rs, "OD_Length_Front");
$OD_Length_Back = odbc_result($rs, "OD_Length_Back");
$OD_Width_Right = odbc_result($rs, "OD_Width_Right");
$OD_Width_Left = odbc_result($rs, "OD_Width_Left");
$Dimension_Unit = odbc_result($rs, "Dimension_Unit");
$Is_OverDimension = odbc_result($rs, "Is_OverDimension");
//echo "$Bill_Of_Lading_Number - $Assigned_Container_Number | $IMO_Number | $Fumigation_Residue_IMO_Class | $Dimension_Unit | $Is_OverDimension <br>";
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $Bill_Of_Lading_Number)->setCellValue('B' . $rowNumber, $Assigned_Container_Number)->setCellValue('C' . $rowNumber, $IMO_Number)->setCellValue('D' . $rowNumber, $Fumigation_Residue_IMO_Class)->setCellValue('E' . $rowNumber, $OD_Height)->setCellValue('F' . $rowNumber, $OD_Length_Front)->setCellValue('G' . $rowNumber, $OD_Length_Back)->setCellValue('H' . $rowNumber, $OD_Width_Right)->setCellValue('I' . $rowNumber, $OD_Width_Left)->setCellValue('J' . $rowNumber, $Dimension_Unit);
$rowNumber++;
//$lin++;
}
}
$linea++;
}
// fin del while
// Loregreso a la hoja 1.
$objPHPExcel->setActiveSheetIndex(0);
// Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attachment;filename="repFactuMLink.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
fclose($fp);
}
// fin else
// --------------- Reporte de errores ---------------------------
echo "<br>";
if (!empty($error[contenedor])) {
foreach ($error[contenedor] as $err) {
echo $err . "<br>";
}
}
echo "<br>";
if (!empty($error[equipo])) {
foreach ($error[equipo] as $err) {
echo $err . "<br>";
}
}
echo "<br>";
if (!empty($error[bl])) {
foreach ($error[bl] as $err) {
echo $err . "<br>";
}
}
echo "<br>";
}
$objPHPExcel->getProperties()->setTitle("Raumbelegungsplan")->setSubject("Ferienschule")->setDescription("Belegungsplan für die einzelnen Räume");
$objPHPExcel = new PHPExcel();
//$objPHPExcel->setActiveSheetIndex(0);
$days = array("Mo", "Di", "Mi", "Do", "Fr");
$slots = array(1, 2, 3);
$times = array("9:00-\n10:30", "10:45-\n12:15", "12:30-\n14:00");
$cells = array('A', 'B', 'C', 'D', 'C', 'D', 'E', 'F', 'G');
$sql_rooms = "SELECT * from rooms";
//$rooms = mysqli_query($conn, $sql_rooms) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error('ferienschule'), E_USER_ERROR);
$rooms = get_sql_result($sql_rooms);
$sheet = 1;
while ($room = mysqli_fetch_assoc($rooms)) {
//create new worksheet for every room and make it active
$objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
$objWorksheet->setTitle('' . $room["room_name"]);
$objPHPExcel->addSheet($objWorksheet);
$objPHPExcel->setActiveSheetIndex($sheet);
$sheet++;
$rowHeight = 130;
$columnWidth = 60;
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight($rowHeight);
$objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight($rowHeight);
$objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight($rowHeight);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth($columnWidth);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth($columnWidth);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth($columnWidth);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth($columnWidth);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth($columnWidth);
$rowCount = 1;
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $room["room_name"]);
/**
* @author caochunhui@dachuwang.com
* @description 用数组和地址直接生成excel文件
* 每一个数组占一个sheet
*/
private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '', $barcode_arr = array())
{
//下面的代码是抄的。
//set cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
//open excel file
$write_objPHPExcel = new PHPExcel();
$write_objPHPExcel->getDefaultStyle()->getFont()->setName('simsun')->setSize(10);
//下面要循环了
$sheet_cnt = 0;
foreach ($arr as $item) {
//用订单id.csv来命名每一个sheet
$out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]);
//$out_sheet->setTitle($item);
//row index start from 1
$row_index = 0;
foreach ($item as $row) {
$row_index++;
//$cellIterator = $row->getCellIterator();
//$cellIterator->setIterateOnlyExistingCells(false);
//column index start from 0
$column_index = -1;
foreach ($row as $cell) {
$column_index++;
//var_dump($cell);
$out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell, PHPExcel_Cell_DataType::TYPE_STRING);
}
}
//如果条码数组不为空，那么说明需要在sheet里插入条码
if (!empty($barcode_arr) && isset($barcode_arr[$sheet_cnt])) {
$barcode_download_res = $this->_download_barcode($barcode_arr[$sheet_cnt]);
if ($barcode_download_res['code'] == 200) {
//no pic you say a jb
$pic_path = $barcode_download_res['file'];
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('barcode');
$objDrawing->setDescription('');
$objDrawing->setPath($pic_path);
$objDrawing->setHeight(50);
$objDrawing->setCoordinates('D26');
//$objDrawing->setOffsetX(10);
//$objDrawing->getShadow()->setVisible(true);
//$objDrawing->getShadow()->setDirection(36);
$objDrawing->setWorksheet($out_sheet);
//no pic you say a jb
}
}
$write_objPHPExcel->addSheet($out_sheet);
$sheet_cnt++;
}
$write_objPHPExcel->removeSheetByIndex(0);
//删除第一个空sheet
//上面要循环了
//上面的代码是抄的
//write excel file
$objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel);
$dir_name = dirname($out_name);
if (!is_dir($dir_name)) {
$res = mkdir($dir_name, 0777, TRUE);
}
$objWriter->save($out_name);
}
$ews->setCellValue('A1', 'RUT');
$ews->setCellValue('B1', 'ROL');
$ews->setCellValue('C1', 'CARATULADO');
$ews->setCellValue('D1', 'FECHA INGRESO');
$ews->setCellValue('E1', 'ESTADO ADMINISTRATIVO');
$ews->setCellValue('F1', 'PROCEDIMIENTO');
$ews->setCellValue('G1', 'UBICACION');
$ews->setCellValue('H1', 'ETAPA');
$ews->setCellValue('I1', 'ESTADO_PROCEDIMIENTO');
$ews->setCellValue('J1', 'TRIBUNAL');
for ($col = ord('a'); $col <= ord('j'); $col++) {
$ews->getColumnDimension(chr($col))->setAutoSize(true);
}
$ews->fromArray($causas, ' ', 'A2');
$ews2 = new \PHPExcel_Worksheet($ea, 'Causa Historia');
$ea->addSheet($ews2, 1);
$ews2->setTitle('Causa Historia');
//echo count($causa_hisotoria);
$ews2->setCellValue('A1', 'RUT');
$ews2->setCellValue('B1', 'ROL');
$ews2->setCellValue('C1', 'FOLIO');
$ews2->setCellValue('D1', 'ETAPA');
$ews2->setCellValue('E1', 'TRAMITE');
$ews2->setCellValue('F1', 'DESC_TRAMITE');
$ews2->setCellValue('G1', 'FECHA_TRAMITE');
$ews2->setCellValue('H1', 'FOJA');
$ews2->setCellValue('I1', 'DOCUMENTO_LINK');
$ews2->fromArray($causa_hisotoria, ' ', 'A2');
for ($col = ord('a'); $col <= ord('i'); $col++) {
$ews2->getColumnDimension(chr($col))->setAutoSize(true);
}
function reporte($fec1, $fec2)
{
global $connI;
$reportName = "Intermodal :: Rep. Hojas de Instruccion en MscLink";
// Incluir la libreria PHPExcel
require '../include/PHPExcel/PHPExcel.php';
// Reservar memoria en servidor PHP
// Si el archivo final tiene 5Mb, reservar 500Mb
// Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria
ini_set("memory_limit", "512M");
// Estilos Arreglo
$styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
$styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
// ----------------------------
// Hoja 1
// Nota : No usar acentos!!!
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Hoja1');
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);
// HOJA 2
$workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Hoja2');
$objPHPExcel->addSheet($workSheet2, 1);
$workSheet2->getDefaultStyle()->getFont()->setSize(8);
$workSheet2->getDefaultStyle()->getFont()->setName('Arial');
// -----------------------------------------------
// ENCABEZADOS
// -----------------------------------------------
// Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas.
$objPHPExcel->setActiveSheetIndex(0);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:R7')->applyFromArray($styleEnc);
// Encabezados
$headings = array('No', 'Intermodal_Leg_id,', 'Purchase_Order_Number,', 'Global_Name,', 'Transport_Mode,', 'IL_Estimated_Cost,', 'ISC_Customer_Appointment_Date,', 'Booking_Number,', 'Bill_Of_Lading_Number,', 'Vessel_Name,', 'Voyage,', 'POL,', 'POD,', 'Container_Number,', 'Req_Assign_Container,', 'IC_Req_Assign_Container,', 'EQ_Type,', 'Tot_Container_Packages_Weight,', 'LocalLogisticLocation,', 'Cust_Company,', 'DStart_Depot,', 'DEnd_Depot,', 'Booking_Type_Desc,', 'Ex_Short_Description,', 'Shipment_Method,', 'Origin,', 'FDest,', 'I_E,', 'Charge,', 'Result_Std,', 'Seal_No');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:R7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Solo como Agentes / As Agent only');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', '$reportName');
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A6', "{$buque} {$viaje}");
$objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo);
// -----------------------------------
// HOJA 1
// -----------------------------------
$objPHPExcel->setActiveSheetIndex(0);
// Simple query
$sql = "\n SELECT\n PT.Intermodal_Leg_id,\n PT.Purchase_Order_Number,\n GC.Global_Name,\n IC.Transport_Mode,\n IC.IL_Estimated_Cost,\n IC.ISC_Customer_Appointment_Date,\n IC.Booking_Number,\n IC.Bill_Of_Lading_Number,\n IC.Vessel_Name,\n IC.Voyage,\n IC.POL,\n IC.POD,\n IC.Container_Number,\n IC.Req_Assign_Container,\n IC.IC_Req_Assign_Container,\n IC.EQ_Type,\n IC.Tot_Container_Packages_Weight,\n IC.LocalLogisticLocation,\n IC.Cust_Company,\n IC.DStart_Depot,\n IC.DEnd_Depot,\n IC.Booking_Type_Desc,\n IC.Ex_Short_Description,\n IC.Shipment_Method,\n IC.Origin,\n IC.FDest,\n IC.I_E,\n CH.Charge,\n CH.Result_Std,\n s.Seal_No\n FROM\n Interlink_DFeed90.dfeed.FNTB_Intermodal_Customer (NULL, NULL) IC\n LEFT JOIN Interlink_Main90.dbo.Assoc_Doc_Company DC (nolock) ON DC.Intermodal_Leg_id = IC.Intermodal_Leg_id\n LEFT JOIN Interlink_Main90.dbo.GISCompany_Address GA (nolock) ON GA.Company_Address_id = DC.Company_Address_id\n LEFT JOIN Interlink_Main90.dbo.GISCompany GC (nolock) ON GC.company_id = GA.Company_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Intermodal_EDI_Request_Truck (NULL, NULL) PT ON IC.Intermodal_Leg_id = PT.Intermodal_Leg_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Charge (1, NULL) CH ON CH.Cargo_Shipment_Container_id = IC.Cargo_Shipment_Container_id\n AND CH.Charge_DTX_Code = (\n CASE\n WHEN CH.I_E = 'E' THEN\n 'PRE'\n ELSE\n 'ONC'\n END\n )\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_BOLREP_Container () s ON IC.Container_Number = s.Container_Number\n AND s.Cargo_Shipment_Container_idx = IC.Cargo_Shipment_Container_id\n AND s.BC_BOLREP_id = (\n SELECT\n MAX (BC_BOLREP_id)\n FROM\n Interlink_DFeed90.dfeed.FNTB_BOLREP_Container ()\n WHERE\n Container_Number = IC.Container_Number\n AND Cargo_Shipment_Container_idx = IC.Cargo_Shipment_Container_id\n )\n WHERE\n IC.ISC_Customer_Appointment_Date BETWEEN '{$fec1}' AND '{$fec2}'\n\n ";
$rs = odbc_exec($connI, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$lin = 1;
$rowNumber = 8;
// donde inicia los datos
while (odbc_fetch_row($rs)) {
$Intermodal_Leg_id = odbc_result($rs, 'Intermodal_Leg_id');
$Purchase_Order_Number = odbc_result($rs, 'Purchase_Order_Number');
$Global_Name = odbc_result($rs, 'Global_Name');
$Transport_Mode = odbc_result($rs, 'Transport_Mode');
$IL_Estimated_Cost = odbc_result($rs, 'IL_Estimated_Cost');
$ISC_Customer_Appointment_Date = odbc_result($rs, 'ISC_Customer_Appointment_Date');
$Booking_Number = odbc_result($rs, 'Booking_Number');
$Bill_Of_Lading_Number = odbc_result($rs, 'Bill_Of_Lading_Number');
$Vessel_Name = odbc_result($rs, 'Vessel_Name');
$Voyage = odbc_result($rs, 'Voyage');
$POL = odbc_result($rs, 'POL');
$POD = odbc_result($rs, 'POD');
$Container_Number = odbc_result($rs, 'Container_Number');
$Req_Assign_Container = odbc_result($rs, 'Req_Assign_Container');
$IC_Req_Assign_Container = odbc_result($rs, 'IC_Req_Assign_Container');
$EQ_Type = odbc_result($rs, 'EQ_Type');
$Tot_Container_Packages_Weight = odbc_result($rs, 'Tot_Container_Packages_Weight');
$LocalLogisticLocation = odbc_result($rs, 'LocalLogisticLocation');
$Cust_Company = odbc_result($rs, 'Cust_Company');
$DStart_Depot = odbc_result($rs, 'DStart_Depot');
$DEnd_Depot = odbc_result($rs, 'DEnd_Depot');
$Booking_Type_Desc = odbc_result($rs, 'Booking_Type_Desc');
$Ex_Short_Description = odbc_result($rs, 'Ex_Short_Description');
$Shipment_Method = odbc_result($rs, 'Shipment_Method');
$Origin = odbc_result($rs, 'Origin');
$FDest = odbc_result($rs, 'FDest');
$I_E = odbc_result($rs, 'I_E');
$Charge = odbc_result($rs, 'Charge');
$Result_Std = odbc_result($rs, 'Result_Std');
$Seal_No = odbc_result($rs, 'Seal_No');
$ISC_Customer_Appointment_Date = substr($ISC_Customer_Appointment_Date, 0, -4);
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $lin)->setCellValue('B' . $rowNumber, $Intermodal_Leg_id)->setCellValue('C' . $rowNumber, $Purchase_Order_Number)->setCellValue('D' . $rowNumber, $Global_Name)->setCellValue('E' . $rowNumber, $Transport_Mode)->setCellValue('F' . $rowNumber, $IL_Estimated_Cost)->setCellValue('G' . $rowNumber, $ISC_Customer_Appointment_Date)->setCellValue('H' . $rowNumber, $Booking_Number)->setCellValue('I' . $rowNumber, $Bill_Of_Lading_Number)->setCellValue('J' . $rowNumber, $Vessel_Name)->setCellValue('K' . $rowNumber, $POL)->setCellValue('L' . $rowNumber, $POD)->setCellValue('M' . $rowNumber, $Container_Number)->setCellValue('N' . $rowNumber, $Req_Assign_Container)->setCellValue('O' . $rowNumber, $IC_Req_Assign_Container)->setCellValue('P' . $rowNumber, $EQ_Type)->setCellValue('Q' . $rowNumber, $Tot_Container_Packages_Weight)->setCellValue('R' . $rowNumber, $LocalLogisticLocation)->setCellValue('S' . $rowNumber, $Cust_Company)->setCellValue('T' . $rowNumber, $DStart_Depot)->setCellValue('U' . $rowNumber, $DEnd_Depot)->setCellValue('V' . $rowNumber, $Booking_Type_Desc)->setCellValue('W' . $rowNumber, $Ex_Short_Description)->setCellValue('X' . $rowNumber, $Shipment_Method)->setCellValue('Y' . $rowNumber, $Origin)->setCellValue('Z' . $rowNumber, $FDest)->setCellValue('AA' . $rowNumber, $I_E)->setCellValue('AB' . $rowNumber, $Charge)->setCellValue('AC' . $rowNumber, $Result_Std)->setCellValue('AD' . $rowNumber, $Seal_No);
$rowNumber++;
$lin++;
}
/*
// ------------------------------
// Hoja 2 - TRANSBORDOS
// ------------------------------
$objPHPExcel->setActiveSheetIndex(1);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:T7')->applyFromArray($styleEnc);
// Encabezados
$headings = Array('No', 'Original_pol');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:T7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.')
->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Solo como Agentes / As agents Only');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', '$reportName');
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A6', "$buque $viaje");
$objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo);
$objPHPExcel->setActiveSheetIndex(1);
// Simple query
$sql = "
SELECT
e.Equipment_Number as CONTE,
e.Bill_Of_Lading_Number as BL,
e.Origin_Booking_Number,
e.EQ_Type as EQ_Type,
e.Load_Terminal,
e.VPCLoad_Vessel_Name,
e.VPCLoad_Voyage,
e.Origin AS Inland_Origin,
e.FDest AS Inland_Final_Dest,
e.Orig_POLUN AS Original_POL,
e.VPCLoad_UNCode AS Current_POL,
e.Prev_POLUN AS Previous_Port,
e.Next_PODUN AS Next_POD,
e.PODUN AS Final_POD,
e.TSPorts AS All_TS_Ports,
e.NVOCC_AMS_CERTIFIED,
e.High_Priority_Flag,
e.Hold_Indicator,
d.UN_Number as UNNumber,
d.IMO_Class as ImoClass,
e.Shipper_Owned,
e.Master_Tare_Weight_Kg as TaraKg,
e.Cntr_Weight,
e.Cargo_Weight_Kgs as CargoKg,
e.Master_Payload_Kg,
e.Cargo_Volume_CMT,
e.Ex_Cargo_Short_Description as Producto,
e.Cargo_Packages as Paquetes,
e.Ex_Cargo_Package_Type as Embalaje,
e.Ex_Harmonized_Code,
e.OOG_Height,
e.OOG_Length_Back,
e.OOG_Length_Front,
e.OOG_Width_Left,
e.OOG_Width_Right,
e.Tank_Residue,
e.Max_Reefer_Temp,
e.Min_Reefer_Temp,
e.Temp_Unit,
e.Ventilated_Container_Flag,
e.Air_Fresh_Vents_Setting,
e.Cold_Treatment,
e.Control_Atmosphere,
e.Opening_Percentage,
e.Bulb_Mode,
e.IsEmpty as IsEmpty,
e.Fumigation,
e.De_Fumigation,
e.Fumigation_Residue_IMO_Class,
e.Fumigation_Residue_UN_Number,
e.Custom_Status_Desc,
a.company AS shipper_name,
a.city AS shipper_city,
a.Country AS shipper_country,
a.MSCCode AS shipper_msccode
FROM
interlink_dfeed90.dfeed.TSHIP_FNTB_Equipment ('2015-05-01', 'A') e
LEFT JOIN Interlink_DFeed90.dfeed.TSHIP_FNTB_Dangerous_Cargo () d ON e.Equipment_ID = d.Equipment_ID
LEFT JOIN Interlink_DFeed90.dfeed.TSHIP_FNTB_AssocDocCompany ('shipper') a ON e.Bill_Of_Lading_Number = a.Bill_Of_Lading_Number
WHERE
E.VPCDisch_Vessel_Name = '$buque'
AND E.VPCDisch_Voyage = '$viaje'
AND E.VPCDisch_UNCode = '$codPuerto'
";
$rs = odbc_exec($connI, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$lin=1;
$rowNumber = 8; // donde inicia los datos
while (odbc_fetch_row($rs)) {
$polOri = odbc_result($rs, "Original_POL");
$polCur = odbc_result($rs, "Current_POL");
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue(
'A'.$rowNumber,$lin)
->setCellValue('B'.$rowNumber,$polOri)
->setCellValue('C'.$rowNumber,$polPrev)
->setCellValue('D'.$rowNumber,$x)
->setCellValue('E'.$rowNumber,$x)
->setCellValue('F'.$rowNumber,$x)
->setCellValue('G'.$rowNumber,$x)
->setCellValue('H'.$rowNumber,$x)
->setCellValue('I'.$rowNumber,$x)
->setCellValue('J'.$rowNumber,$x)
->setCellValue('K'.$rowNumber,$x)
->setCellValue('L'.$rowNumber,$x)
->setCellValue('M'.$rowNumber,$x)
->setCellValue('N'.$rowNumber,$x)
->setCellValue('O'.$rowNumber,$x)
->setCellValue('P'.$rowNumber,$x)
->setCellValue('Q'.$rowNumber,$x)
->setCellValue('R'.$rowNumber,$x)
->setCellValue('S'.$rowNumber,$x)
->setCellValue('T'.$rowNumber,$x)
;
$rowNumber++;
$lin++;
}
// Loregreso a la hoja 1.
$objPHPExcel->setActiveSheetIndex(0);
*/
// Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attachment;filename="hojaIns_msclink.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
function reporte($fec1, $fec2)
{
global $connI;
$reportName = "";
// Incluir la libreria PHPExcel
require '../include/PHPExcel/PHPExcel.php';
// Reservar memoria en servidor PHP
// Si el archivo final tiene 5Mb, reservar 500Mb
// Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria
ini_set("memory_limit", "512M");
// Estilos Arreglo
$styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF')));
$styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
$styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
// ----------------------------
// Hoja 1
// Nota : No usar acentos!!!
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Hoja1');
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);
// HOJA 2
$workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Hoja2');
$objPHPExcel->addSheet($workSheet2, 1);
$workSheet2->getDefaultStyle()->getFont()->setSize(8);
$workSheet2->getDefaultStyle()->getFont()->setName('Arial');
// -----------------------------------------------
// ENCABEZADOS
// -----------------------------------------------
// Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas.
$objPHPExcel->setActiveSheetIndex(0);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:R7')->applyFromArray($styleEnc);
// Encabezados
$headings = array('No', 'POL', 'POD');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:R7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Solo como Agentes / As Agent only');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', '$reportName');
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A6', "{$buque} {$viaje}");
$objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo);
// -----------------------------------
// HOJA 1
// -----------------------------------
$objPHPExcel->setActiveSheetIndex(0);
// Simple query
$sql = "";
$rs = odbc_exec($connI, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$lin = 1;
$rowNumber = 8;
// donde inicia los datos
while (odbc_fetch_row($rs)) {
$pol = odbc_result($rs, "POLUN");
$pod = odbc_result($rs, "PODUN");
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $lin)->setCellValue('B' . $rowNumber, $pol)->setCellValue('C' . $rowNumber, $pod)->setCellValue('D' . $rowNumber, $x)->setCellValue('E' . $rowNumber, $x)->setCellValue('F' . $rowNumber, $x)->setCellValue('G' . $rowNumber, $x)->setCellValue('H' . $rowNumber, $x)->setCellValue('I' . $rowNumber, $x)->setCellValue('J' . $rowNumber, $x)->setCellValue('K' . $rowNumber, $x)->setCellValue('L' . $rowNumber, $x)->setCellValue('M' . $rowNumber, $x)->setCellValue('N' . $rowNumber, $x)->setCellValue('O' . $rowNumber, $x)->setCellValue('P' . $rowNumber, $x)->setCellValue('Q' . $rowNumber, $x)->setCellValue('R' . $rowNumber, $x);
$rowNumber++;
$lin++;
}
/*
// ------------------------------
// Hoja 2 - TRANSBORDOS
// ------------------------------
$objPHPExcel->setActiveSheetIndex(1);
// Se aplica estilo al encabezado
$objPHPExcel->getActiveSheet()->getStyle('A7:T7')->applyFromArray($styleEnc);
// Encabezados
$headings = Array('No', 'Original_pol');
// Escribe los encabezados
$rowNumber = 7;
// Freeze pane so that the heading line won't scroll
$objPHPExcel->getActiveSheet()->freezePane('A8');
$col = 'A';
foreach ($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
$col++;
}
// AutoFiltro
$objPHPExcel->getActiveSheet()->setAutoFilter('A7:T7');
// Auto Ajuste de Ancho en Columna
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15);
// Titulo
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.')
->getStyle('A1')->applyFromArray($styleTitulo);
// SUBTitulo
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Solo como Agentes / As agents Only');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A3', '$reportName');
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo);
$objPHPExcel->getActiveSheet()->setCellValue('A6', "$buque $viaje");
$objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo);
$objPHPExcel->setActiveSheetIndex(1);
// Simple query
$sql = "
SELECT
e.Equipment_Number as CONTE,
e.Bill_Of_Lading_Number as BL,
e.Origin_Booking_Number,
e.EQ_Type as EQ_Type,
e.Load_Terminal,
e.VPCLoad_Vessel_Name,
e.VPCLoad_Voyage,
e.Origin AS Inland_Origin,
e.FDest AS Inland_Final_Dest,
e.Orig_POLUN AS Original_POL,
e.VPCLoad_UNCode AS Current_POL,
e.Prev_POLUN AS Previous_Port,
e.Next_PODUN AS Next_POD,
e.PODUN AS Final_POD,
e.TSPorts AS All_TS_Ports,
e.NVOCC_AMS_CERTIFIED,
e.High_Priority_Flag,
e.Hold_Indicator,
d.UN_Number as UNNumber,
d.IMO_Class as ImoClass,
e.Shipper_Owned,
e.Master_Tare_Weight_Kg as TaraKg,
e.Cntr_Weight,
e.Cargo_Weight_Kgs as CargoKg,
e.Master_Payload_Kg,
e.Cargo_Volume_CMT,
e.Ex_Cargo_Short_Description as Producto,
e.Cargo_Packages as Paquetes,
e.Ex_Cargo_Package_Type as Embalaje,
e.Ex_Harmonized_Code,
e.OOG_Height,
e.OOG_Length_Back,
e.OOG_Length_Front,
e.OOG_Width_Left,
e.OOG_Width_Right,
e.Tank_Residue,
e.Max_Reefer_Temp,
e.Min_Reefer_Temp,
e.Temp_Unit,
e.Ventilated_Container_Flag,
e.Air_Fresh_Vents_Setting,
e.Cold_Treatment,
e.Control_Atmosphere,
e.Opening_Percentage,
e.Bulb_Mode,
e.IsEmpty as IsEmpty,
e.Fumigation,
e.De_Fumigation,
e.Fumigation_Residue_IMO_Class,
e.Fumigation_Residue_UN_Number,
e.Custom_Status_Desc,
a.company AS shipper_name,
a.city AS shipper_city,
a.Country AS shipper_country,
a.MSCCode AS shipper_msccode
FROM
interlink_dfeed90.dfeed.TSHIP_FNTB_Equipment ('2015-05-01', 'A') e
LEFT JOIN Interlink_DFeed90.dfeed.TSHIP_FNTB_Dangerous_Cargo () d ON e.Equipment_ID = d.Equipment_ID
LEFT JOIN Interlink_DFeed90.dfeed.TSHIP_FNTB_AssocDocCompany ('shipper') a ON e.Bill_Of_Lading_Number = a.Bill_Of_Lading_Number
WHERE
E.VPCDisch_Vessel_Name = '$buque'
AND E.VPCDisch_Voyage = '$viaje'
AND E.VPCDisch_UNCode = '$codPuerto'
";
$rs = odbc_exec($connI, $sql);
if (!$rs) {
exit("Error en la consulta SQL");
}
$lin=1;
$rowNumber = 8; // donde inicia los datos
while (odbc_fetch_row($rs)) {
$polOri = odbc_result($rs, "Original_POL");
$polCur = odbc_result($rs, "Current_POL");
// Por manejo de memoria se deben de meter los datos con las menos operaciones posibles.
$objPHPExcel->getActiveSheet()->setCellValue(
'A'.$rowNumber,$lin)
->setCellValue('B'.$rowNumber,$polOri)
->setCellValue('C'.$rowNumber,$polPrev)
->setCellValue('D'.$rowNumber,$x)
->setCellValue('E'.$rowNumber,$x)
->setCellValue('F'.$rowNumber,$x)
->setCellValue('G'.$rowNumber,$x)
->setCellValue('H'.$rowNumber,$x)
->setCellValue('I'.$rowNumber,$x)
->setCellValue('J'.$rowNumber,$x)
->setCellValue('K'.$rowNumber,$x)
->setCellValue('L'.$rowNumber,$x)
->setCellValue('M'.$rowNumber,$x)
->setCellValue('N'.$rowNumber,$x)
->setCellValue('O'.$rowNumber,$x)
->setCellValue('P'.$rowNumber,$x)
->setCellValue('Q'.$rowNumber,$x)
->setCellValue('R'.$rowNumber,$x)
->setCellValue('S'.$rowNumber,$x)
->setCellValue('T'.$rowNumber,$x)
;
$rowNumber++;
$lin++;
}
// Loregreso a la hoja 1.
$objPHPExcel->setActiveSheetIndex(0);
*/
// Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attachment;filename="repForCapitania.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
$recap[$elm->id_produit][3] += $elm->quantite_commande;
}
}
$sheet = new PHPExcel_Worksheet($xls,"commande ".$commande->id_commande);
$sheet->fromArray(
$grid2, // The data to set
NULL, // Array values with this value will not be set
'A1' // Top left coordinate of the worksheet range where we want to set these values (default is A1)
);
$sheet->fromArray(
$grid, // The data to set
NULL, // Array values with this value will not be set
'A4' // Top left coordinate of the worksheet range where we want to set these values (default is A1)
);
$xls->addSheet($sheet);
$nbre_commandes = $nbre_commandes+1;
}
$recapsheet->SetCellValue('F1', $nbre_commandes);
$recapsheet->fromArray(
$recap, // The data to set
NULL, // Array values with this value will not be set
'A1' // Top left coordinate of the worksheet range where we want to set these values (default is A1)
);
$xlsWriter = new PHPExcel_Writer_Excel2007($xls);
$file = tempnam(sys_get_temp_dir(), 'Tux');
$xlsWriter->save($file);
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=Preparations.xlsx');
/**
* @author caochunhui@dachuwang.com
* @description 用数组和地址直接生成excel文件
* 每一个数组占一个sheet
*/
private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '')
{
//下面的代码是抄的。
//set cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
//open excel file
$write_objPHPExcel = new PHPExcel();
//下面要循环了
$sheet_cnt = 0;
foreach ($arr as $item) {
//用订单id.csv来命名每一个sheet
$out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]);
//$out_sheet->setTitle($item);
//row index start from 1
$row_index = 0;
foreach ($item as $row) {
$row_index++;
//$cellIterator = $row->getCellIterator();
//$cellIterator->setIterateOnlyExistingCells(false);
//column index start from 0
$column_index = -1;
foreach ($row as $cell) {
$column_index++;
//var_dump($cell);
$out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell);
}
}
$write_objPHPExcel->addSheet($out_sheet);
$sheet_cnt++;
}
$write_objPHPExcel->removeSheetByIndex(0);
//删除第一个空sheet
//上面要循环了
//上面的代码是抄的
//write excel file
$objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel);
$dir_name = dirname($out_name);
if (!is_dir($dir_name)) {
$res = mkdir($dir_name, 0777, TRUE);
}
$objWriter->save($out_name);
}
