function sql_record()
{
global $o70_anousu, $o70_codrec, $o57_fonte, $o57_descr, $janeiro, $fevereiro, $marco, $abril, $maio, $junho, $julho, $agosto, $setembro, $outubro, $novembro, $dezembro, $o70_valor, $adicional;
global $prev_jan, $prev_fev, $prev_mar, $prev_abr, $prev_mai, $prev_jun, $prev_jul, $prev_ago, $prev_set, $prev_out, $prev_nov, $prev_dez;
require_once "libs/db_utils.php";
if ($this->sql == null) {
$this->sql_query($this->receita);
}
// echo($this->sql . "<br>");
pg_exec("begin");
pg_exec("create temporary table work_plano as " . $this->sql);
pg_exec("create index work_plano_estrut on work_plano(o57_fonte)");
$result = pg_query("select * from work_plano");
for ($i = 0; $i < pg_numrows($result); $i++) {
db_fieldsmemory($result, $i);
$sSqlSaldoPrevisao = "SELECT mes,coalesce(o34_valor, 0) as valor, {$o70_codrec} as receita \n from generate_series(1,12) as mes";
$sSqlSaldoPrevisao .= " left join orcprevrec ";
$sSqlSaldoPrevisao .= " on mes = o34_mes and o34_anousu = {$this->anousu} and o34_codrec = {$o70_codrec}";
$sSqlSaldoPrevisao .= " order by mes";
if ($this->lPrevisaoCronograma) {
$sSqlSaldoPrevisao = "SELECT coalesce(valor, 0) as valor, {$o70_codrec} as receita, mes";
$sSqlSaldoPrevisao .= " from generate_series(1,12) as mes";
$sSqlSaldoPrevisao .= " left join (SELECT coalesce(o127_valor, 0) as valor, ";
$sSqlSaldoPrevisao .= " o127_mes ";
$sSqlSaldoPrevisao .= " from cronogramaperspectivareceita ";
$sSqlSaldoPrevisao .= " inner join orcreceita on o70_codrec = o126_codrec ";
$sSqlSaldoPrevisao .= " and o126_anousu = {$this->anousu}";
$sSqlSaldoPrevisao .= " inner join cronogramametareceita on o127_cronogramaperspectivareceita = o126_sequencial";
$sSqlSaldoPrevisao .= " where o126_codrec = {$o70_codrec} ";
$sSqlSaldoPrevisao .= " ) as bases on mes = o127_mes ";
$sSqlSaldoPrevisao .= " order by mes ";
}
//die($sSqlSaldoPrevisao);
$rsSaldoPrevisao = db_query($sSqlSaldoPrevisao);
$aMeses = db_utils::getColectionByRecord($rsSaldoPrevisao);
$aValorPrevMes = array();
foreach ($aMeses as $oSaldoMes) {
$aValorPrevMes[$oSaldoMes->mes] = $oSaldoMes->valor;
}
$prev_jan += $aValorPrevMes[1];
$prev_fev += $aValorPrevMes[2];
$prev_mar += $aValorPrevMes[3];
$prev_abr += $aValorPrevMes[4];
$prev_mai += $aValorPrevMes[5];
$prev_jun += $aValorPrevMes[6];
$prev_jul += $aValorPrevMes[7];
$prev_ago += $aValorPrevMes[8];
$prev_set += $aValorPrevMes[9];
$prev_out += $aValorPrevMes[10];
$prev_nov += $aValorPrevMes[11];
$prev_dez += $aValorPrevMes[12];
$estrutural = $o57_fonte;
if ($this->lPrevisaoCronograma) {
pg_query("update work_plano set \n prev_jan = prev_jan +{$prev_jan},\n prev_fev = prev_fev +{$prev_fev}+0.0,\n prev_mar = prev_mar +{$prev_mar}+0.0,\n prev_abr = prev_abr +{$prev_abr}+0.0,\n prev_mai = prev_mai +{$prev_mai}+0.0,\n prev_jun = prev_jun +{$prev_jun}+0.0,\n prev_jul = prev_jul +{$prev_jul}+0.0,\n prev_ago = prev_ago +{$prev_ago}+0.0,\n prev_set = prev_set +{$prev_set}+0.0,\n prev_out = prev_out +{$prev_out}+0.0,\n prev_nov = prev_nov +{$prev_nov}+0.0,\n prev_dez = prev_dez +{$prev_dez}+0.0 \n \n \n where o70_codrec = {$o70_codrec}");
}
for ($ii = 1; $ii < 10; $ii++) {
///o zé colocou isso... 19042005
if ($estrutural == "") {
continue;
}
$estrutural = db_le_mae_conplano($estrutural);
$nivel = db_le_mae_conplano($estrutural, true);
$result_estrut = pg_query("select o57_descr from work_plano where o57_fonte = '{$estrutural}'");
// db_criatabela($result_estrut); exit;
//if ($estrutural == '411229900000000'){
// echo ($estrutural." ->" .$o70_valor ." <br>");
// }
if (pg_numrows($result_estrut) == 0) {
$result_estrut = pg_query("select o57_descr from orcfontes where o57_anousu = " . $this->anousu . " and o57_fonte = '{$estrutural}'");
if (pg_numrows($result_estrut) == 0) {
echo "Conta não encontrada nas fontes de Receita Comando:" . "select o57_descr from orcfontes where o57_anousu = " . $this->anousu . " and o57_fonte = '{$estrutural}'";
exit;
}
db_fieldsmemory($result_estrut, 0);
$sInsert = "insert into work_plano values(\n " . $this->anousu . ",\n 0,\n 0,\n '{$estrutural}',\n '{$o57_descr}',\n {$o70_valor},\n {$adicional},\n {$janeiro},\n {$fevereiro},\n {$marco},\n {$abril},\n {$maio},\n {$junho},\n {$julho},\n {$agosto},\n {$setembro},\n {$outubro},\n {$novembro},\n {$dezembro},\n {$prev_jan},\n {$prev_fev},\n {$prev_mar},\n {$prev_abr},\n {$prev_mai},\n {$prev_jun},\n {$prev_jul},\n {$prev_ago},\n {$prev_set},\n {$prev_out},\n {$prev_nov},\n {$prev_dez}\n )\n ";
$result_1 = pg_query($sInsert);
} else {
pg_query("update work_plano set \n o70_valor = o70_valor + {$o70_valor},\n adicional= adicional +{$adicional},\n janeiro = janeiro +{$janeiro},\n fevereiro= fevereiro +{$fevereiro},\n marco = marco +{$marco},\n abril = abril +{$abril},\n maio = maio +{$maio},\n junho = junho +{$junho},\n julho = julho +{$julho},\n agosto = agosto +{$agosto},\n setembro = setembro +{$setembro},\n outubro = outubro +{$outubro},\n novembro = novembro +{$novembro},\n dezembro = dezembro +{$dezembro},\n prev_jan = prev_jan +{$prev_jan},\n prev_fev = prev_fev +{$prev_fev}+0.0,\n prev_mar = prev_mar +{$prev_mar}+0.0,\n prev_abr = prev_abr +{$prev_abr}+0.0,\n prev_mai = prev_mai +{$prev_mai}+0.0,\n prev_jun = prev_jun +{$prev_jun}+0.0,\n prev_jul = prev_jul +{$prev_jul}+0.0,\n prev_ago = prev_ago +{$prev_ago}+0.0,\n prev_set = prev_set +{$prev_set}+0.0,\n prev_out = prev_out +{$prev_out}+0.0,\n prev_nov = prev_nov +{$prev_nov}+0.0,\n prev_dez = prev_dez +{$prev_dez}+0.0 \n \n \n where o57_fonte = '{$estrutural}'");
}
/*
o70_valor = o70_valor+$o70_valor,
adicional= adicional+$adicional,
*/
if ($nivel == 1) {
break;
}
}
}
//exit;
$sql = " \n SELECT O70_ANOUSU,O70_CODREC,o70_instit,O57_FONTE,O57_DESCR,round(O70_VALOR,2) as o70_valor,\n round(adicional,2) as adicional,\n round(JANEIRO,2 ) as JANEIRO,\n round(FEVEREIRO,2) as FEVEREIRO,\n round(MARCO,2 ) as MARCO,\n round(ABRIL,2 ) as ABRIL,\n round(MAIO, 2 ) as MAIO,\n round(JUNHO,2 ) as JUNHO,\n round(JULHO,2 ) as JULHO,\n round(AGOSTO,2 ) as AGOSTO,\n round(SETEMBRO,2) as SETEMBRO,\n round(OUTUBRO,2 ) as OUTUBRO,\n round(NOVEMBRO,2) as NOVEMBRO,\n round(DEZEMBRO,2) as DEZEMBRO,\n prev_jan,\n prev_fev,\n prev_mar,\n prev_abr,\n prev_mai,\n prev_jun,\n prev_jul,\n prev_ago,\n prev_set,\n prev_out,\n prev_nov,\n prev_dez\n \n from work_plano ";
//--
if ($this->estrut != null) {
$sql .= "where O57_FONTE IN " . $this->estrut;
}
$sql .= "order by o57_fonte ";
$this->result = pg_query($sql);
if ($this->anousu == 2010) {
//db_criatabela($this->result);
}
if ($this->result != false) {
$this->numrows = pg_numrows($this->result);
} else {
$this->numrows = 0;
}
}
function db_receitappa($anoini, $db_where = false, $retsql = false)
{
$ano1 = $anoini;
$ano2 = $anoini + 1;
$ano3 = $anoini + 2;
$ano4 = $anoini + 3;
if ($db_where != false) {
$where = $db_where;
} else {
$where = '';
}
$sql = "\n select \n o57_fonte as estrut_mae,\n o57_fonte as estrut,\n o57_descr as descr_rece,\n o70_codigo as recurso,\n o15_descr as descr_recu,\n sum(case when o27_exercicio = " . $ano1 . " then o27_valor else 0 end) as a1,\n sum(case when o27_exercicio = " . $ano2 . " then o27_valor else 0 end) as a2,\n sum(case when o27_exercicio = " . $ano3 . " then o27_valor else 0 end) as a3,\n sum(case when o27_exercicio = " . $ano4 . " then o27_valor else 0 end) as a4\n from\n (select\n o57_fonte ,\n o57_descr ,\n o70_codigo ,\n o15_descr ,\n o27_exercicio,\n case when substr(o57_fonte,1,2) = '49' \n then ABS(o27_valor) * (-1) \n else o27_valor end as o27_valor\n from orcpparec\n left join orcfontes on o57_codfon = o27_codfon and o57_anousu = " . db_getsession("DB_anousu") . "\n left join orcreceita on o70_codfon = o27_codfon and o70_anousu = " . db_getsession("DB_anousu") . "\n inner join orctiporec on o15_codigo = o70_codigo\n {$where} ) as x\n group by o57_fonte,\n o57_descr,\n o70_codigo,\n o15_descr\n order by o57_fonte\n ";
//echo $sql;exit;
// db_criatabela(pg_query($sql));exit;
pg_exec("create temporary table work_pl(\n estrut_mae varchar(15), \n estrut varchar(15),\n descr_rece varchar(50),\n recurso integer,\n descr_recu varchar(50),\n a1 float8,\n a2 float8,\n a3 float8,\n a4 float8\n ) ");
// pg_exec("create temporary table work_plano as $sql");
pg_exec("create index work_pl_estrut on work_pl(estrut)");
pg_exec("create index work_pl_estrutmae on work_pl(estrut_mae)");
$result = pg_query($sql);
// db_criatabela($result);exit;
$tot_a1 = 0;
$tot_a2 = 0;
$tot_a3 = 0;
$tot_a4 = 0;
global $seq;
global $estrut_mae;
global $estrut;
global $descr_rece;
global $recurso;
global $descr_recu;
global $a1;
global $a2;
global $a3;
global $a4;
global $saldo_anterior;
global $saldo_anterior_debito;
global $saldo_anterior_credito;
global $saldo_final;
global $result_estrut;
global $sinal_anterior;
global $sinal_final;
$work_planomae = array();
$work_planoestrut = array();
$work_plano = array();
$seq = 0;
for ($i = 0; $i < pg_numrows($result); $i++) {
// for($i = 0;$i < 20;$i++){
db_fieldsmemory($result, $i);
$tot_a1 = $a1;
$tot_a2 = $a2;
$tot_a3 = $a3;
$tot_a4 = $a4;
$key = array_search("{$estrut_mae}", $work_planomae);
if ($key === false) {
// não achou
$work_planomae[$seq] = $estrut_mae;
$work_planoestrut[$seq] = $estrut;
$work_plano[$seq] = array(0 => "{$descr_rece}", 1 => "{$recurso} ", 2 => "{$descr_recu}", 3 => "{$a1} ", 4 => "{$a2} ", 5 => "{$a3} ", 6 => "{$a4} ");
$seq = $seq + 1;
} else {
$work_plano[$key][3] += $a1;
$work_plano[$key][4] += $a2;
$work_plano[$key][5] += $a3;
$work_plano[$key][6] += $a4;
}
$estrutural = $estrut;
for ($ii = 1; $ii < 10; $ii++) {
$estrutural = db_le_mae_conplano($estrutural);
$nivel = db_le_mae_conplano($estrutural, true);
$key = array_search("{$estrutural}", $work_planomae);
if ($key === false) {
// não achou
// busca no banco e inclui
//echo "\n".$estrutural;
//echo "\n".$descr_rece;exit;
$res = pg_query("select c60_descr as descr_rece,c60_finali,c60_codcon from conplano where c60_anousu = " . db_getsession("DB_anousu") . " and c60_estrut = '{$estrutural}'");
if ($res == false || pg_numrows($res) == 0) {
db_redireciona("db_erros.php?fechar=true&db_erro=Está faltando cadastrar esse estrutural na contabilidade. Nível : {$nivel} Estrutural : {$estrutural}");
exit;
}
db_fieldsmemory($res, 0);
$work_planomae[$seq] = $estrutural;
$work_planoestrut[$seq] = '';
$work_plano[$seq] = array(0 => $descr_rece, 1 => $recurso, 2 => $descr_recu, 3 => $a1, 4 => $a2, 5 => $a3, 6 => $a4);
$seq++;
} else {
$work_plano[$key][3] += $tot_a1;
$work_plano[$key][4] += $tot_a2;
$work_plano[$key][5] += $tot_a3;
$work_plano[$key][6] += $tot_a4;
}
if ($nivel == 1) {
break;
}
}
}
for ($i = 0; $i < sizeof($work_planomae); $i++) {
$mae = $work_planomae[$i];
$estrut = $work_planoestrut[$i];
$descr_rece = $work_plano[$i][0];
$recurso = $work_plano[$i][1];
$descr_recu = $work_plano[$i][2];
$a1 = $work_plano[$i][3];
$a2 = $work_plano[$i][4];
$a3 = $work_plano[$i][5];
$a4 = $work_plano[$i][6];
$sql = "insert into work_pl \n values ('{$mae}',\n '{$estrut}',\n '{$descr_rece}', \n {$recurso},\n '{$descr_recu}',\n {$a1},\n {$a2},\n {$a3},\n {$a4})\n \n ";
pg_exec($sql);
}
$sql = "select *\n from work_pl \n order by estrut_mae,estrut";
if ($retsql == false) {
$result_final = pg_exec($sql);
// db_criatabela($result_final); exit;
return $result_final;
} else {
return $sql;
}
}
