function getExtrato($fonte, $mes, $ano)
{
global $mysqli, $empresaId;
$contabil = getContaFromTesouraria($fonte);
$result = $mysqli->query("\n\t\tSELECT \n\t\tDATE_FORMAT(l.data, '%Y-%m-%d 00:00:00') as unixdata,\n\t\tl.tipo, \n\t\ttipo_doc, \n\t\tn_lanc, \n\t\tn_doc, \n\t\tref, \n\t\tl.fonte_financeira,\n\t\tf.descricao as fonte,\n\t\tcomplemento, \n\t\tif(pce.nome != null or t.descricao != null, if(t.contabil = l.natureza_financeira, t.descricao, pce.nome), if(pce.cod = l.natureza_financeira, pce.nome, t.descricao)) as natureza_financeira, \n\t\tvalor_total \n\t\tFROM \n\t\tcacp_gestor.lancamentos as l \n\t\tLEFT JOIN cacp_gestor.plano_contas_empresa as pce ON pce.empresa_id = l.empresa_id\n\t\tLEFT join cacp_gestor.tesouraria as t on t.empresa_id = l.empresa_id\n\t\tLEFT join cacp_gestor.tesouraria as f on f.empresa_id = l.empresa_id\n\t\tWHERE \n\t\tl.empresa_id = '{$empresaId}'\n\t\tAND\n\t\t(\n\t\t\tt.contabil = l.natureza_financeira\n\t\t\tor\n\t\t\tpce.cod = l.natureza_financeira\n\t\t)\n\t\tAND\n\t\tf.id = l.fonte_financeira\n\t\tAND\n\t\t(\n\t\t\tl.fonte_financeira = '{$fonte}'\n\t\t\tOR\n\t\t\tl.natureza_financeira = (select pce.cod from plano_contas_empresa as pce, tesouraria as t where t.empresa_id = '{$empresaId}' and pce.empresa_id = t.empresa_id and natureza = 'M' and contacontabil = t.contabil and t.id = '{$fonte}')\n\t\t)\n\t\tAND\n\t\tMONTH(`data`) = '{$mes}' \n\t\tAND \n\t\tYEAR(`data`) = '{$ano}'\n\t\tGROUP BY l.id\n\t\tORDER BY n_lanc ASC;\n\t");
return $result;
}
$ano = $_GET['ano'];
$fonte = $_GET['fonte'];
require_once dirname(__FILE__) . '/PHPExcel/Classes/PHPExcel/IOFactory.php';
$filename = 'extrato_' . $mes . '_' . $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("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;
function writeToSheet(&$recordIndex, $r, &$sheet)
{
global $doc_types, $parametros, $replace;
$_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['natureza_titulo'] == "Lucros Distribuidos"){
if ($r['natureza_titulo'] == "Pro-Labore" || $r['natureza_titulo'] == "Lucros Distribuidos") {
$_HIST["deb"] = $r['tipo'] == 'P' ? $r['socio_contabil'] : $r['fonte_contabil'];
$_HIST["cred"] = $r['tipo'] == 'P' ? $r['fonte_contabil'] : $r['socio_contabil'];
$_HIST["fav_text"] = "Socio ";
} else {
//echo " [plano contabil] = $r[plano_contabil] [natureza] = $r[natureza_titulo] ";
//$_HIST["deb"] = ($r['tipo'] == 'P') ? $r['plano_contabil'] : $r['fonte_contabil'];
if ($r['tipo'] == 'M') {
$_HIST["deb"] = getContaFromTesouraria($r['fonte_financeira']);
$_HIST["cred"] = $r['natureza_financeira'];
} else {
$_HIST["deb"] = $r['tipo'] == 'P' ? $r['plano_contabil'] : $r['fonte_contabil'];
$_HIST["cred"] = $r['tipo'] == 'P' ? $r['fonte_contabil'] : $r['plano_contabil'];
}
if ($r['tipo_doc'] == 'N') {
$_HIST["deb"] = $r['tipo'] == 'P' ? getContaFromFavorecido($r['favorecido'], 'contabil') : $r['fonte_contabil'];
$_HIST["cred"] = $r['tipo'] == 'P' ? $r['fonte_contabil'] : getContaFromFavorecido($r['favorecido'], 'contabil');
if ($r['tipo'] == 'P') {
$_HIST["fav_text"] = "Fornecedor ";
} else {
$_HIST["fav_text"] = "Cliente ";
}
}
if ($r['natureza_titulo'] == "Pro-Labore") {
$_HIST["fav_text"] = "Socio ";
}
}
$sheet->setCellValue('A' . $recordIndex, substr($r['data'], 8, 2) . '/' . substr($r['data'], 5, 2) . "/" . substr($r['data'], 0, 4))->setCellValue('B' . $recordIndex, $_HIST['deb'])->setCellValue('C' . $recordIndex, $_HIST['cred'])->setCellValueExplicit('D' . $recordIndex, '99.01')->setCellValue('E' . $recordIndex, 'NL:' . $r['n_lanc'] . " " . $_HIST['tipo'] . $_HIST['tipo_doc'] . $_HIST['n_doc'] . $_HIST['ref'] . $_HIST['complemento'] . " " . str_replace(array_keys($replace), $replace, $r['natureza_titulo']) . " " . $_HIST['fav_text'] . $r['favorecido_nome'])->setCellValue('F' . $recordIndex, $r['valor_doc']);
$sheet->getStyle('F' . $recordIndex)->getNumberFormat()->setFormatCode('#,##0.00');
if ($r['valor_desconto_adiantamento'] > 0) {
$recordIndex++;
$sheet->setCellValue('A' . $recordIndex, substr($r['data'], 8, 2) . '/' . substr($r['data'], 5, 2) . "/" . substr($r['data'], 0, 4));
if ($r['tipo'] == 'P') {
//echo $parametros;
$sheet->setCellValue('B' . $recordIndex, $_HIST['deb']);
//conta debito
$sheet->setCellValue('C' . $recordIndex, getContaFromFavorecido($r['favorecido']));
//conta credito
} else {
if ($r['tipo'] == 'R') {
$sheet->setCellValue('B' . $recordIndex, getContaFromFavorecido($r['favorecido']));
//conta debito
$sheet->setCellValue('C' . $recordIndex, $_HIST['cred']);
//conta credito
}
}
$sheet->setCellValueExplicit('D' . $recordIndex, '99.01')->setCellValue('E' . $recordIndex, 'NL:' . $r['n_lanc'] . " " . $_HIST['tipo'] . " Desconto Adiantamento" . $_HIST['tipo_doc'] . $_HIST['n_doc'] . $_HIST['ref'] . $_HIST['complemento'] . " " . str_replace(array_keys($replace), $replace, $r['natureza_titulo']) . " " . $_HIST['fav_text'] . $r['favorecido_nome'])->setCellValue('F' . $recordIndex, $r['valor_desconto_adiantamento']);
$sheet->getStyle('F' . $recordIndex)->getNumberFormat()->setFormatCode('#,##0.00');
}
if ($r['valor_juros'] > 0) {
$recordIndex++;
$sheet->setCellValue('A' . $recordIndex, substr($r['data'], 8, 2) . '/' . substr($r['data'], 5, 2) . "/" . substr($r['data'], 0, 4));
if ($r['tipo'] == 'P') {
//echo $parametros;
$sheet->setCellValue('B' . $recordIndex, $parametros->pagamento_conta_juros);
//conta debito
$sheet->setCellValue('C' . $recordIndex, $_HIST['cred']);
//conta credito
} else {
if ($r['tipo'] == 'R') {
$sheet->setCellValue('B' . $recordIndex, $_HIST['deb']);
//conta debito
$sheet->setCellValue('C' . $recordIndex, $parametros->recebimento_conta_juros);
//conta credito
}
}
$sheet->setCellValueExplicit('D' . $recordIndex, '99.01')->setCellValue('E' . $recordIndex, 'NL:' . $r['n_lanc'] . " " . $_HIST['tipo'] . " Juros" . $_HIST['tipo_doc'] . $_HIST['n_doc'] . $_HIST['ref'] . $_HIST['complemento'] . " " . str_replace(array_keys($replace), $replace, $r['natureza_titulo']) . " " . $_HIST['fav_text'] . $r['favorecido_nome'])->setCellValue('F' . $recordIndex, $r['valor_juros']);
$sheet->getStyle('F' . $recordIndex)->getNumberFormat()->setFormatCode('#,##0.00');
}
if ($r['valor_multa'] > 0) {
$recordIndex++;
$sheet->setCellValue('A' . $recordIndex, substr($r['data'], 8, 2) . '/' . substr($r['data'], 5, 2) . "/" . substr($r['data'], 0, 4));
if ($r['tipo'] == 'P') {
//echo $parametros;
$sheet->setCellValue('B' . $recordIndex, $parametros->pagamento_conta_multa);
//conta debito
$sheet->setCellValue('C' . $recordIndex, $_HIST['cred']);
//conta credito
} else {
if ($r['tipo'] == 'R') {
$sheet->setCellValue('B' . $recordIndex, $_HIST['deb']);
//conta debito
$sheet->setCellValue('C' . $recordIndex, $parametros->recebimento_conta_multa);
//conta credito
}
}
$sheet->setCellValueExplicit('D' . $recordIndex, '99.01')->setCellValue('E' . $recordIndex, 'NL:' . $r['n_lanc'] . " " . $_HIST['tipo'] . " Multa" . $_HIST['tipo_doc'] . $_HIST['n_doc'] . $_HIST['ref'] . $_HIST['complemento'] . " " . str_replace(array_keys($replace), $replace, $r['natureza_titulo']) . " " . $_HIST['fav_text'] . $r['favorecido_nome'])->setCellValue('F' . $recordIndex, $r['valor_multa']);
$sheet->getStyle('F' . $recordIndex)->getNumberFormat()->setFormatCode('#,##0.00');
}
if ($r['valor_desconto'] > 0) {
$recordIndex++;
$sheet->setCellValue('A' . $recordIndex, substr($r['data'], 8, 2) . '/' . substr($r['data'], 5, 2) . "/" . substr($r['data'], 0, 4));
if ($r['tipo'] == 'P') {
$sheet->setCellValue('B' . $recordIndex, $_HIST['deb']);
//conta debito
$sheet->setCellValue('C' . $recordIndex, $parametros->pagamento_conta_desconto);
//conta credito
} else {
if ($r['tipo'] == 'R') {
$sheet->setCellValue('B' . $recordIndex, $parametros->recebimento_conta_desconto);
//conta debito
$sheet->setCellValue('C' . $recordIndex, $_HIST['cred']);
//conta credito
}
}
$sheet->setCellValueExplicit('D' . $recordIndex, '99.01')->setCellValue('E' . $recordIndex, 'NL:' . $r['n_lanc'] . " " . $_HIST['tipo'] . " Desconto" . $_HIST['tipo_doc'] . $_HIST['n_doc'] . $_HIST['ref'] . $_HIST['complemento'] . " " . str_replace(array_keys($replace), $replace, $r['natureza_titulo']) . " " . $_HIST['fav_text'] . $r['favorecido_nome'])->setCellValue('F' . $recordIndex, $r['valor_desconto']);
$sheet->getStyle('F' . $recordIndex)->getNumberFormat()->setFormatCode('#,##0.00');
}
$recordIndex++;
}