function generateXLS($data)
{
global $mysqli, $ano;
//include "connect.php";
require_once dirname(__FILE__) . '/PHPExcel/Classes/PHPExcel/IOFactory.php';
$filename = 'fluxocaixa_' . $ano . '.xlsx';
//$filename = md5(time()). '.xlsx';
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment; filename=\"{$filename}\"");
header("Cache-Control: max-age=0");
$excelObj = new PHPExcel();
$excelObj->getProperties()->setCreator("Gestor Financeiro Web")->setLastModifiedBy("Vinícius Hacebe")->setTitle("Fluxo de Caixa")->setSubject("Fluxo de Caixa")->setDescription("Dados exportados do fluxo de caixa")->setKeywords("fluxo de caixa")->setCategory("fluxo de caixa");
$sheet = $excelObj->setActiveSheetIndex(0);
$sheet->setCellValue('A1', 'Classificação')->setCellValue('B1', 'Descrição')->setCellValue('C1', 'Saldo')->setCellValue('D1', 'Jan/' . $ano)->setCellValue('E1', 'Fev/' . $ano)->setCellValue('F1', 'Mar/' . $ano)->setCellValue('G1', 'Abr/' . $ano)->setCellValue('H1', 'Mai/' . $ano)->setCellValue('I1', 'Jun/' . $ano)->setCellValue('J1', 'Jul/' . $ano)->setCellValue('K1', 'Ago/' . $ano)->setCellValue('L1', 'Set/' . $ano)->setCellValue('M1', 'Out/' . $ano)->setCellValue('N1', 'Nov/' . $ano)->setCellValue('O1', 'Dez/' . $ano);
$recordIndex = 2;
for ($i = 0; $i < sizeof($data); $i++) {
writeToSheet($recordIndex, $data, $i, $sheet);
}
foreach (range('A', 'O') as $columnID) {
$excelObj->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}
$excelObj->getActiveSheet()->getStyle('A1:A2560')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objWriter = PHPExcel_IOFactory::createWriter($excelObj, 'Excel2007');
//$objWriter->save($filename);
$objWriter->save('php://output');
//print_r($data);
}
$excelObj->getProperties()->setCreator("Gestor Financeiro Web")->setLastModifiedBy("Vinícius Hacebe")->setTitle("Extrato Conta Corrente")->setSubject("Extrato Conta Corrente")->setDescription("Dados exportados dos extratos de conta corrente")->setKeywords("extrato")->setCategory("extrato");
$doc_types['G'] = "Guia";
$doc_types['R'] = "Recibo";
$doc_types['N'] = "Nota Fiscal";
$doc_types['C'] = "Cheque";
$doc_types['E'] = "Extrato";
$natureza = getContaFromTesouraria($fonte);
$saldo = getSaldoAnterior($fonte, $natureza, $mes, $ano);
$saldoAnt = $saldo;
$sql = getExtrato($fonte, $mes, $ano);
$sheet = $excelObj->setActiveSheetIndex(0);
$sheet->setCellValue('A3', 'Data')->setCellValue('B3', 'Historico')->setCellValue('C3', 'Entrada')->setCellValue('D3', 'Saída')->setCellValue('E3', 'Saldo')->setCellValue('D1', 'Saldo Anterior')->setCellValue('E1', $saldoAnt);
$sheet->getStyle('E1')->getNumberFormat()->setFormatCode('#,##0.00');
$recordIndex = 4;
while ($r = $sql->fetch_assoc()) {
writeToSheet($recordIndex, $r, $sheet, $saldo);
//$recordIndex++;
$_HIST = null;
}
function writeToSheet(&$recordIndex, $r, &$sheet, &$saldo)
{
global $doc_types, $replace, $fonte;
$_HIST["tipo"] = $r['tipo'] == 'P' ? 'Pagamento' : ($r['tipo'] == 'R' ? 'Recebimento' : 'Transferencia');
$_HIST["tipo_doc"] = " " . $doc_types[$r['tipo_doc']];
$_HIST["ref"] = " " . str_replace("-", '', $r['ref']);
$_HIST["complemento"] = $r['complemento'] ? " " . str_replace(array_keys($replace), $replace, $r['complemento']) : "";
$_HIST["n_doc"] = $r['n_doc'] > 0 ? " " . $r['n_doc'] : "";
if ($r['tipo'] == 'R') {
$_HIST['entrada'] = $r['valor_total'];
$_HIST['saida'] = 0;
$saldo += $r['valor_total'];
and
year(data) = '$ano'
order by n_lanc asc");*/
$sql = $mysqli->query("SELECT\n\t\t\t\t*\n\t\t\t\tFROM \n\t\t\t\tcacp_gestor.parametros\n\t\t\t\tWHERE \n\t\t\t\tempresa_id = '{$empresaId}'\n\t\t\t\t");
$parametros = new stdClass();
while ($r_param = $sql->fetch_assoc()) {
foreach ($r_param as $key => $value) {
$parametros->{$key} = $value;
}
}
$sql = $mysqli->query("SELECT\n\t\t\t\tl.*, if(p.nome = \"Pro-Labore\" OR p.nome = \"Lucros Distribuidos\", IF(s.empresa_id = l.empresa_id, s.nome, null), IF(f.empresa_id = l.empresa_id, f.nome, null)) as favorecido_nome, p.nome as natureza_titulo, t.descricao as fonte_financeira_nome, t.contabil as fonte_contabil, p.contacontabil as plano_contabil, s.contacontabil as socio_contabil\n\t\t\t\tFROM \n\t\t\t\tcacp_gestor.lancamentos as l \n\t\t\t\tLEFT JOIN cacp_gestor.favorecidos as f ON f.id = l.favorecido\n\t\t\t\tLEFT JOIN cacp_gestor.socios as s ON s.id = l.favorecido\n\t\t\t\tLEFT JOIN cacp_gestor.tesouraria as t ON t.empresa_id = l.empresa_id\n\t\t\t\tLEFT JOIN cacp_gestor.plano_contas_empresa as p ON p.empresa_id = l.empresa_id\n\t\t\t\tWHERE \n\t\t\t\tMONTH(`data`) = '{$mes}'\n\t\t\t\tAND\n\t\t\t\tYEAR(`data`) = '{$ano}'\n\t\t\t\tAND\n\t\t\t\tl.empresa_id = '{$empresaId}'\n\t\t\t\tAND \n\t\t\t\tp.cod = l.natureza_financeira\n\t\t\t\tAND\n\t\t\t\tt.id = l.fonte_financeira\n\t\t\t\t\n\t\t\t\tORDER BY `n_lanc` ASC;");
$sheet = $excelObj->setActiveSheetIndex(0);
$sheet->setCellValue('A1', 'Data')->setCellValue('B1', 'Debito')->setCellValue('C1', 'Credito')->setCellValue('D1', 'Cod')->setCellValue('E1', 'Historico')->setCellValue('F1', 'Valor');
$recordIndex = 2;
while ($r = $sql->fetch_assoc()) {
writeToSheet($recordIndex, $r, $sheet);
//$recordIndex++;
$_HIST = null;
}
function getContaFromFavorecido($id, $tipo = 'adiantamento')
{
global $mysqli, $empresaId;
/*if($tipo != "socio"){*/
$sql = $mysqli->query("SELECT {$tipo} FROM favorecidos WHERE empresa_id = '{$empresaId}' AND id='{$id}'");
/*}else{
$sql = $mysqli->query("SELECT contacontabil FROM socios WHERE empresa_id = '$empresaId' AND id='$id'");
}*/
//echo $sql->num_rows;
if ($sql->num_rows > 0) {
$row = $sql->fetch_array();
//echo $row[0];
