#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
use Win32::ODBC;
use CGI;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
$form 		= new CGI;
$lan		= $form->param('lan');
$appid		= $form->param('appid');
$clase		= $form->param('clase');
#---------------------------------------------------------------
#-------------------------------------------------------------------
#Define los subdirectorios a utilizar en el programa
$PATH		= "../".$lan."/";
$file   	= "es_saldo_rubros_rept.html";
$titulo_clase   = $clase."S";
$titulo_clase   =~ tr/a-z/A-Z/;
#---------------------------------------------------------------
#---------------------------------------------------------------
if ( !( $db = new Win32::ODBC($appid) ) ) {print "Error connecting to $db <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
#-------------------------------------------------------
%rubros_mora	 = get_rubros_mora();
$dt_generado	 = get_dt_generado();
$table		 = construct_table();

construct_page();
$db->Close();
exit;
#---------------------------------------------------------------
#---------------------------------------------------------------
#funcion para obtener expedientes
sub get_rubros_mora {
	my %secs  = ();
	my $table = "";
	my $sql   = "select a.c16_tarifa_id,
					sum(a.d82_val),
					sum(a.d82_fie),
					sum(a.d82_int),
					sum(a.d82_mul)
			 from sdd_".$clase."_estados_mensuales a
				 left join sdm_inmuebles c on a.i8_inmueble_id=c.i8_inmueble_id				 
			 where a.i4_dias_mora > 0 and  a.d82_val > 0 and c.c16_estado='ACEPTADO'
			 group by substring(a.c16_tarifa_id,1,13)
			 order by a.c16_tarifa_id
		   ";	
	if($clase eq "empresa"){
		$sql = "select a.c16_tarifa_id,
				sum(a.d82_val),
				sum(a.d82_fie),
				sum(a.d82_int),
				sum(a.d82_mul)
			 from sdd_".$clase."_estados_mensuales a
				 left join sdd_empresa_ubicacion b on a.i8_empresa_id = b.i8_empresa_id
				 left join sdm_empresas c on a.i8_empresa_id=c.i8_empresa_id				 
			 where a.i4_dias_mora > 0 and  a.d82_val > 0 and c.c16_estado='ACEPTADO'
			 group by substring(a.c16_tarifa_id,1,13)
			 order by a.c16_tarifa_id
		   ";
	}	
	if ( $db->Sql($sql) ){SQL_fail($sql,$db,'sub get_rubros_mora')};
	while($db->FetchRow()) 	{
		undef my @row;
		@row = $db->Data();
		$secs{$row[0]}    = [@row];
		$secs{$row[0]}[5] = sprintf("%.2f",$row[1]+$row[2]+$row[3]+$row[4]);
	}
	return %secs;
}
sub get_dt_generado {
	my $str  = ();	
	my $sql	 = "select dt_generado
			 from sdd_".$clase."_estados_mensuales
			 where i4_dias_mora > 0 and  d82_val > 0
			 group by dt_generado
		   ";
	if ( $db->Sql($sql) ){SQL_fail($sql,$db,'sub get_rubros_mora')};
	while($db->FetchRow()) 	{
		my @row = $db->Data();
		$str = $row[0];
	}
	return $str;
}
sub construct_table {
	my $rows  = "";
	my $count = 1;
	my $table = "<table cellspacing=0 cellpadding=0 width=100% border=1 bordercolor=#E5E5E5 style=border-width:0.5>
		      <tr class=tdheader2>
			  <td class=tdheader2>No.</td>
			  <TD class=tdheader2>RUBRO ID</TD>
			  <td class=tdheader2>DESCRIPCION</td>
			  <td class=tdheader2>RUBRO</td>
			  <td class=tdheader2>FIESTAS</td>
			  <td class=tdheader2>INTERES</td>
			  <td class=tdheader2>MULTA</td>
			  <td class=tdheader2>TOTAL</td>
		       </tr>
		       {rows}
		     </table>
		   ";
	my $row  = "<tr>
			<td class=my_style2>{count}</td>
			<TD class=my_style2>{rubro}</TD>
			<TD class=my_style2>{rubro_label}</TD>
			<td class=my_style2 align=right>{val}</td>
			<td class=my_style2 align=right>{fie}</td>
			<td class=my_style2 align=right>{int}</td>
			<td class=my_style2 align=right>{mul}</td>
			<td class=my_style2 align=right>{tot}</td>
		     </tr>
		    ";
	my $footrow = "<tr>
			<td class=my_style2>&nbsp;</td>
			<TD class=my_style2>&nbsp;</TD>
			<td class=my_style2 align=right>TOTALES</td>
			<td class=my_style2 align=right>{total_val}</td>
			<td class=my_style2 align=right>{total_fie}</td>
			<td class=my_style2 align=right>{total_int}</td>
			<td class=my_style2 align=right>{total_mul}</td>
			<td class=my_style2 align=right>{total_tot}</td>
		     </tr>
		    ";


	foreach my $k (sort keys %rubros_mora){
		if($rubros_mora{$k}[1] eq "" or $rubros_mora{$k}[1] eq "null"){$rubros_mora{$k}[1]=0.00}else{$rubros_mora{$k}[1]=sprintf("%.2f",$rubros_mora{$k}[1])}
		if($rubros_mora{$k}[2] eq "" or $rubros_mora{$k}[2] eq "null"){$rubros_mora{$k}[2]=0.00}else{$rubros_mora{$k}[2]=sprintf("%.2f",$rubros_mora{$k}[2])}
		if($rubros_mora{$k}[3] eq "" or $rubros_mora{$k}[3] eq "null"){$rubros_mora{$k}[3]=0.00}else{$rubros_mora{$k}[3]=sprintf("%.2f",$rubros_mora{$k}[3])}
		if($rubros_mora{$k}[4] eq "" or $rubros_mora{$k}[4] eq "null"){$rubros_mora{$k}[4]=0.00}else{$rubros_mora{$k}[4]=sprintf("%.2f",$rubros_mora{$k}[4])}
		if($rubros_mora{$k}[5] eq "" or $rubros_mora{$k}[5] eq "null"){$rubros_mora{$k}[5]=0.00}else{$rubros_mora{$k}[5]=sprintf("%.2f",$rubros_mora{$k}[5])}

		$total_val	+= $rubros_mora{$k}[1];
		$total_fie	+= $rubros_mora{$k}[2];
		$total_int	+= $rubros_mora{$k}[3];		
		$total_mul	+= $rubros_mora{$k}[4];
		$total_tot	+= $rubros_mora{$k}[5];

		($rubro_label,$dtd)  = get_rubro_label($k);

		$line = $row;
		$line =~ s/\{count}/$count/g;
		$line =~ s/\{rubro}/$k/g;
		$line =~ s/\{rubro_label}/$rubro_label/g;
		$line =~ s/\{val}/$rubros_mora{$k}[1]/g;
		$line =~ s/\{fie}/$rubros_mora{$k}[2]/g;
		$line =~ s/\{int}/$rubros_mora{$k}[3]/g;
		$line =~ s/\{mul}/$rubros_mora{$k}[4]/g;
		$line =~ s/\{tot}/$rubros_mora{$k}[5]/g;

		$rows .= $line;
		$count++;
	}
	#$total_val = big_money($total_val);
	#$total_fie = big_money($total_fie);
	#$total_int = big_money($total_int);		
	#$total_mul = big_money($total_mul);
	#$total_tot = big_money($total_tot);

	$rows .= $footrow;
	$rows =~ s/\{total_val}/$total_val/g;
	$rows =~ s/\{total_fie}/$total_fie/g;
	$rows =~ s/\{total_int}/$total_int/g;
	$rows =~ s/\{total_mul}/$total_mul/g;
	$rows =~ s/\{total_tot}/$total_tot/g;

	$table=~ s/\{rows}/$rows/g;
	return $table;
}
sub construct_page {
	open (DATA, "$PATH$file");
	while ($line = <DATA>) {	
                $line =~ s/\{lan}/$lan/g;
                $line =~ s/\{appid}/$appid/g;
                $line =~ s/\{clase}/$clase/g;
                $line =~ s/\{titulo_clase}/$titulo_clase/g;
                $line =~ s/\{dt_generado}/$dt_generado/g;
		$line =~ s/\{table}/$table/g;
		print "$line";
	}
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Error: ".$dbh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    $dbh->Close();
    exit;
}
sub big_money {
  my $number = sprintf "%.2f", shift @_;
  # Add one comma each time through the do-nothing loop
  1 while $number =~ s/^(-?\d+)(\d\d\d)/$1,$2/;
  # Put the dollar sign in the right place
  $number =~ s/^(-?)/$1\$/;
  $number;
}
sub get_rubro_label {
	my ($id)=@_;
	my $label = "";
	my $desde = "";
	my $sql	  = "SELECT v128_tarifa,dt_desde FROM sdm_tarifas where c16_tarifa_id='$id'";
	if ( $db->Sql($sql) ){SQL_fail($sql,$db,'sub get_rubro_label')};
	if($db->FetchRow()) 	{
		undef my @row;
		@row = $db->Data();
		$label = $row[0];
		$desde = $row[1];
	}
	return $label,$desde;	
}