<?

require_once('../include/orm.php');
require_once('../include/globals.php');
require_once('../include/util.php'); 
require_once("../include/phpexcel/Classes/PHPExcel/IOFactory.php");


$_import_download_db_connected = $Link == ''; 
if($_import_download_db_connected) $Link = connectDb();
define('DBCONNECTED', 1);


debugstartpage();


if( isset($_x) ) {
//if( $_x != '' ) {
    $_x = $_x == '' ? array() : StringTokenize($_x, ",");
    $_result = array();
    $_result['version'] = array('actual' => 47);
    $_result['visibility'] = array();
    $_initialvars = arrayExclude(get_defined_vars(), array("_*", "form_*", "s_*", "GLOBALS", "Link", "thisname"));
}


// isbasewindow: 


// On Open
while( ($__action == "" or $__action == "executedonopen") and (empty($_x) or in('onopen', $_x)) ) {
  $__action = "executedonopen";  
  
  $ddform = DDFORM;
$ddfield = DDFIELD;
debug("[import-download] cols $cols", sizeof($data));
// Carga datos
// cols
// data
if( $tit == "" ) {  
    $tit = $_mode == 'export' ? "Exportacion" : "Importacion"; 
} 
$fieldstooltips = array();
$fieldsprops = array();
$fieldstypes = array();
$fieldstitles = array();
$fieldsname = array();
$fieldsmultiple = array();
if( $form != '' ) { 
    $f2array = array();
    $combos = array(); 
    $subforms = array(); 
    $subformscant = array();
    $onlists = array();
    if( $window != '' ) {

$__tempresult = sqlexec($Link, "select concat('../', module, '/', window, '.php'), name, title, dbtable,dbwhere from " . mysql_escape($ddform) . " where concat(module, '.', name) = '" . mysql_escape($form) . "' and window = '" . mysql_escape($window) . "'");
while( $__temprow = mysql_fetch_array($__tempresult)) {
$formurl = $__temprow[0];
 $formname = $__temprow[1];
 $formtitle = $__temprow[2];
 $formdbtable = $__temprow[3];
 $formdbwhere = $__temprow[4];

}

$__tempresult = sqlexec($Link, "select group_concat(if(locate('.', f.form)=0,f.name,concat(substr(f.form, locate('.', f.form) + 1), '_', f.name )) order by f.numero) from " . mysql_escape($ddfield) . " as f where (concat(f.module, '.', f.form) = '" . mysql_escape($form) . "' or concat(f.module, '.', f.form) like '" . mysql_escape($form) . ".%') and f.name != 'id' and f.type != 'table'  and f.window = '" . mysql_escape($window) . "' and (f.props is null or f.props not like '%invisible%')");
while( $__temprow = mysql_fetch_array($__tempresult)) {
$f = $__temprow[0];

}
        // , if( (select f2.type from $ddfield as f2 where f2.module = f.module and f2.window = f.window and concat(f2.form, '.', f2.name) = f.form )='table' , '(1)', '')

$__tempresult = sqlexec($Link, "select concat(substr(form, locate('.', form) + 1), '_', name), info, props, '', type, title from " . mysql_escape($ddfield) . " where (concat(module, '.', form) = '" . mysql_escape($form) . "' or concat(module, '.', form) like '" . mysql_escape($form) . ".%') and name != 'id' and type != 'table' and window = '" . mysql_escape($window) . "' and (props is null or props not like '%invisible%') order by numero");
while( $__temprow = mysql_fetch_array($__tempresult)) {
array_push($fieldsname, $__temprow[0]);
array_push($fieldstooltips, $__temprow[1]);
array_push($fieldsprops, $__temprow[2]);
array_push($fieldsmultiple, $__temprow[3]);
array_push($fieldstypes, $__temprow[4]);
array_push($fieldstitles, $__temprow[5]);

}

$__tempresult = sqlexec($Link, "select name from " . mysql_escape($ddfield) . " where concat(module, '.', form) = '" . mysql_escape($form) . "' and window = '" . mysql_escape($window) . "' and type = 'combo' order by numero");
while( $__temprow = mysql_fetch_array($__tempresult)) {
array_push($combos, $__temprow[0]);

}

$__tempresult = sqlexec($Link, "select name, size from " . mysql_escape($ddfield) . " where concat(module, '.', form) = '" . mysql_escape($form) . "' and window = '" . mysql_escape($window) . "' and type = 'table' order by numero");
while( $__temprow = mysql_fetch_array($__tempresult)) {
array_push($subforms, $__temprow[0]);
array_push($subformscant, $__temprow[1]);

}

$__tempresult = sqlexec($Link, "select name from " . mysql_escape($ddfield) . " where concat(module, '.', form) = '" . mysql_escape($form) . "' and window = '" . mysql_escape($window) . "' order by numero limit 4");
while( $__temprow = mysql_fetch_array($__tempresult)) {
array_push($f2array, $__temprow[0]);

}

$__tempresult = sqlexec($Link, "select name from " . mysql_escape($ddfield) . " as f where (concat(f.module, '.', f.form) = '" . mysql_escape($form) . "' or concat(f.module, '.', f.form) like '" . mysql_escape($form) . ".%') and (f.props is null or f.props not like '%invisible%') and list like '%onfilter%'");
while( $__temprow = mysql_fetch_array($__tempresult)) {
array_push($onlists, $__temprow[0]);

}
        
    } else {

$__tempresult = sqlexec($Link, "select concat('../', module, '/', window, '.php'), name, title, dbtable, dbwhere from " . mysql_escape($ddform) . " where concat(module, '.', name) = '" . mysql_escape($form) . "'");
while( $__temprow = mysql_fetch_array($__tempresult)) {
$formurl = $__temprow[0];
 $formname = $__temprow[1];
 $formtitle = $__temprow[2];
 $formdbtable = $__temprow[3];
 $formdbwhere = $__temprow[4];

}

$__tempresult = sqlexec($Link, "select group_concat(if(locate('.', form)=0,name,concat(substr(form, locate('.', form) + 1), '_', name)) order by numero) from " . mysql_escape($ddfield) . " where (concat(module, '.', form) = '" . mysql_escape($form) . "' or concat(module, '.', form) like '" . mysql_escape($form) . ".%') and name != 'id' and type != 'table' and (props is null or props not like '%invisible%')");
while( $__temprow = mysql_fetch_array($__tempresult)) {
$f = $__temprow[0];

}

$__tempresult = sqlexec($Link, "select concat(substr(form, locate('.', form) + 1), '_', name), info, props, '', type, title from ddfield where (concat(module, '.', form) = '" . mysql_escape($form) . "' or concat(module, '.', form) like '" . mysql_escape($form) . ".%') and name != 'id' and type != 'table' and (props is null or props not like '%invisible%') order by numero");
while( $__temprow = mysql_fetch_array($__tempresult)) {
array_push($fieldsname, $__temprow[0]);
array_push($fieldstooltips, $__temprow[1]);
array_push($fieldsprops, $__temprow[2]);
array_push($fieldsmultiple, $__temprow[3]);
array_push($fieldstypes, $__temprow[4]);
array_push($fieldstitles, $__temprow[5]);

}
    

$__tempresult = sqlexec($Link, "select name from " . mysql_escape($ddfield) . " where concat(module, '.', form) = '" . mysql_escape($form) . "' and type = 'combo' and (props is null or props not like '%invisible%') order by numero");
while( $__temprow = mysql_fetch_array($__tempresult)) {
array_push($combos, $__temprow[0]);

}

$__tempresult = sqlexec($Link, "select name, size from " . mysql_escape($ddfield) . " where concat(module, '.', form) = '" . mysql_escape($form) . "' and type = 'table'  and (props is null or props not like '%invisible%') order by numero");
while( $__temprow = mysql_fetch_array($__tempresult)) {
array_push($subforms, $__temprow[0]);
array_push($subformscant, $__temprow[1]);

}

$__tempresult = sqlexec($Link, "select distinct name from " . mysql_escape($ddfield) . " where concat(module, '.', form) = '" . mysql_escape($form) . "' order by numero limit 4");
while( $__temprow = mysql_fetch_array($__tempresult)) {
array_push($f2array, $__temprow[0]);

}

$__tempresult = sqlexec($Link, "select name from " . mysql_escape($ddfield) . " where concat(module, '.', form) = '" . mysql_escape($form) . "' and (props is null or props not like '%invisible%') and list like '%onfilter%' order by numero");
while( $__temprow = mysql_fetch_array($__tempresult)) {
array_push($onlists, $__temprow[0]);

}
        
    }
    if(  $tit == "Importacion" ) {
        $tit .= " " . plural(trim($formtitle));
    }
} 
$fieldslist = StringTokenize($f, ',');
 
foreach($subforms as $ii => $subform) {
    $scant = $subformscant[$ii];
    if( $scant == '-1' ) {
        //print("FIELDSNAME:");
        //print_r($fieldsname);
        foreach($fieldsname as $ii0 => $field) {
            if( startsWith($field, $subform . '_') ) {
                $fieldsmultiple[$ii0] = 'yes';
            }
        }
        continue;
    }
}
debug("[import-download] create excel");
 
 
// Crea planilla
$objPHPExcel = new PHPExcel();
//echo date('H:i:s') , " Set document properties" , EOL;
$objPHPExcel->getProperties()->setCreator("Flexa Gestión")
							 ->setLastModifiedBy("Flexa Gestión")
							 ->setTitle("Documento $tit")
							 ->setSubject("Documento $tit")
							 ->setDescription("Documento $tit generado por Flexa Gestión")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Documento XLS Planitilla");
$objPHPExcel->setActiveSheetIndex(0);
$acols = json_decode($cols); //StringTokenize($cols, ',');
$adata  = json_decode($data);
// Escribe titulos
debug("ACOLS: ", $acols);
$i0 = 0;
$i1 = -1;
if( $_mode == 'export' ) {
    $i0++;
    $objPHPExcel->getActiveSheet()->setCellValue("A1", "ID");
    $objPHPExcel->getActiveSheet()->getStyle("A1")->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle("A1")->getFill()->getStartColor()->setARGB('FFCDDEDE');
}
/**
if( isset($hasresults) ) {
    $objPHPExcel->getActiveSheet()->getStyle("A1")->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->setCellValue("A1", "Resultado");    
    $objPHPExcel->getActiveSheet()->getColumnDimension("A1")->setWidth(18);    
    $objPHPExcel->getActiveSheet()->getStyle("A1")->getFill()->getStartColor()->setARGB('FFCDDEDE');
    $objPHPExcel->getActiveSheet()->getStyle("B1")->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->setCellValue("B1", "Errores");    
    $objPHPExcel->getActiveSheet()->getColumnDimension("B1")->setWidth(18);    
    $objPHPExcel->getActiveSheet()->getStyle("B1")->getFill()->getStartColor()->setARGB('FFCDDEDE');
}
**/
foreach($acols as $ii => $col) {
    if( trim($col) == '' ) continue;
    $col2 = $col;
    if( strpos($col2, '(') !== false ) $col2 = substr($col2, 0, strpos($col2, '('));
    $index = searchInArray($col2, $fieldslist);
    $props = $fieldsprops[$index];
    $tooltip = $fieldstooltips[$index];
    if( $tooltip == '' ) $tooltip = $fieldstitles[$index];
    $tooltip .= ' (' . translatetype($fieldstypes[$index]) . ')';
    if( $fieldsmultiple[$ii]  == 'yes' ) {
        $tooltip .= ' (Se expande hacia abajo en la planilla)';
    }
    
    
    //debug("Col $col col2 $col2 index $index tooltip $tooltip");
    $cellcol = chr(65+$i0);
    if( $i1 >= 0 )
        $cellcol = chr(65+$i1) . $cellcol;        
    $cell = $cellcol . '1';
    //debug("Setting acol $ii : Cell $cell : $col");
    $objPHPExcel->getActiveSheet()->getStyle($cell)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->setCellValue($cell, $col);    
    $objPHPExcel->getActiveSheet()->getColumnDimension($cellcol)->setWidth(18);    
    $objPHPExcel->getActiveSheet()->getStyle($cell)->getFill()->getStartColor()->setARGB((strpos($props, 'required') ? 'FFFFCC00' : 'FFCDDEDE'));
    if( $fieldsmultiple[$ii]  == 'yes' ) {
        $objPHPExcel->getActiveSheet()->getStyle($cell)->getFill()->getStartColor()->setARGB((strpos($props, 'required') ? 'FFFFCC00' : 'FFCDDEDE'));
        $objPHPExcel->getActiveSheet()->getStyle($cell)->applyFromArray(
        	        array(
        		          'borders' => array(
        								'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM),
        								'right'		=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM),
        								'top'	    => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM),
        								'left'		=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
        							)
        		    )
        	    );
        
        
    }
    
    // Comments
    if( $tooltip != "" ) {
        $objPHPExcel->getActiveSheet()->getComment($cell)->setAuthor('Flexa Gestión');
        //$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment($cell)->getText()->createTextRun('PHPExcel:');
        //$objCommentRichText->getFont()->setBold(true);
        //$objPHPExcel->getActiveSheet()->getComment($cell)->getText()->createTextRun("\r\n");
        $objPHPExcel->getActiveSheet()->getComment($cell)->getText()->createTextRun($tooltip);
    }
    
    // Siguiente columna
    $i0++;
    if( $i0 > 25 ) {
        $i0 = 0;
        $i1++;
    }
}
// Set fills
$objPHPExcel->getActiveSheet()->getStyle('A1:' . $cell)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
// Escribe celdas
foreach($adata as $irow => $acells) {
    $i0 = 0;
    $i1 = -1;
    foreach($acells as $ii => $col) {
        if( $acols[$ii] == '' ) continue;
        $cell = chr(65+$i0) . ($irow + 2);
        if( $i1 >= 0 )
            $cell = chr(65+$i1) . $cell;        
        //debug("Setting acol $ii : Cell $cell : $col");
        $objPHPExcel->getActiveSheet()->setCellValue($cell, $col);    
        $i0++;
        if( $i0 > 25 ) {
            $i0 = 0;
            $i1++;
        }
    }
}
//$objPHPExcel->getActiveSheet()->setCellValue('D1', PHPExcel_Shared_Date::PHPToExcel( gmmktime(0,0,0,date('m'),date('d'),date('Y')) ));
//$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);
// Guarda excel
debug("PHPExcel creating writer");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$dir = "../files/$_filesfolder/import/tmp/";
$file = $tit . ".xlsx";
mkdir($dir, 0777, true);
debug("PHPExcel saving to $dir $file");
/**
header("Content-Disposition: attachment; filename=$file");
header("Content-Type: application/vnd.ms-excel;");
header("Cache-Control: max-age=0");
$objWriter->save('php://output');
**/
$objWriter->save($dir . $file);
print($dir . $file);
disconnectDb();
exit(0);

// Si puso header location para ir a otra pagina cierra el script
if( !isset($_x) and $__exiting ) { 
   disconnectDb($Link); exit(0);
} 

  
  break;
}


// PHP Vars 
while( ($__action == "" or $__action == "executedonopen") and isset($_GET['_x']) and empty($_x) ) {
    $__row0 = array();
    
    $_result['morevars'] = $__row0;
    break;
}


if( has($_x, 'pdf') or has($_x, 'pdfprint') or has($_x, 'pdfprint2') ) { 
    require_once('../include/fpdfreport.php');
    $_pdf = new PDF((isset($_orientation) ? $_orientation : 'P'),'pt', (isset($_size) ? $_size : 'A4' ));
    $_pdf->initialPage = ($_page != '' ? $_page : 1);
    //$_pdf->SetAutoPageBreak(true);
    $_pdf->title = "";
    $_pdf->SetTopMargin(50);
    $_pdf->AliasNbPages();
    $_pdf->AddPage();
}


if( has($_x, 'pdf') or has($_x, 'pdfprint') or has($_x, 'pdfprint2') ) { 
    $_pdf->Output();
}

?><? 

// isbasewidnow!: 

if( !isset($_x) ) { 
?>


<? if( isset($_export) ) { ?>
<?
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=download.xls");
//echo('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">');
//echo('<html xmlns="http://www.w3.org/1999/xhtml">');
echo('<meta http-equiv="Content-type" content="text/html;charset=utf-8" />');
?><? } else { ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html lang="en" >
    <head>    
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">	<meta http-equiv="X-UA-Compatible" content="IE=edge">	<meta name="viewport" content="width=device-width, initial-scale=1">	<link rel="shortcut icon" href="favicon.ico" />

    
    <link href="../import/style.1527286207.css" rel="stylesheet" type="text/css">    

    
    <!-- isbasewindow  -->
    
        
    <script type="text/javascript" src="../include/util.1744242079.js"></script>        
        
    
    <!--    <script type="text/javascript" src="../aaaainclude/jquery.min.js"></script>    -->    


    </head>    <body         data-version="47"
        data-name="download"        class=" <? if( isset($_popup) ) { ?> popup<? } ?>" 
        
        <? if( isset($_popup) ) { ?> class="popup" <? } ?>
        
        >        
        <form id='form' name='form' method='POST' >        
        <input id="_rowid" type="hidden" name="_rowid" value="">        <input id="_rowidname" type="hidden" name="_rowidname" value="">                
                
        
        <style>            
<? if( (has($_x, 'pdf') or has($_x, 'pdfprint') or has($_x, 'pdfprint2')) and (isset($_orientation) or isset($_size)) ) {  ?>
/* A4 Landscape*/@page {    size: A4 portrait;
}<? } ?>
            
        </style>
<? }  // Export
?>
        
<? if( !isset($_export) ) { ?>

        
        </form>        
                
            
        <!-- begin mainfile include JSs -->        
        <!-- end mainfile include JSs -->    
    </body>
        
<? } ?>

<? if( !isset($_export) ) { ?>
</html><? } ?>


<script type="text/javascript">
// To render template init.js
// window.init.js 


$(function() {
    

    <? if( (isset($messages) and $messages != "") or (isset($message) and $message != "") ) { ?>
    message('