#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
use Win32::ODBC;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
#-------------------------------------------------------------------
#-------------------------------------------------------------------
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_sectores_tarifas_saldos_p.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;}
#-------------------------------------------------------
%tarifas_mora	 = get_tarifas_mora();
$dt_generado	 = get_dt_generado();
$table		 = construct_table();

construct_page();
$db->Close();
exit;
#---------------------------------------------------------------
#---------------------------------------------------------------
#funcion para obtener expedientes
sub get_tarifas_mora {
	my %secs  = ();
	my $table = "";
	my $sql   = "select c.c11_sector_id,
						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 c.c11_sector_id,a.c16_tarifa_id
			 order by c.c11_sector_id,a.c16_tarifa_id
		   ";	
	if($clase eq "empresa"){
		$sql = "select  b.c11_sector_id,
						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 b.c11_sector_id,a.c16_tarifa_id
			 order by b.c11_sector_id,a.c16_tarifa_id
		   ";
	}	
	if ( $db->Sql($sql) ){SQL_fail($sql,$db,'sub get_tarifas_mora')};
	while($db->FetchRow()) 	{
		undef my @row;
		@row = $db->Data();
		$secs{$row[0]}{$row[1]}    = [@row];
		$secs{$row[0]}{$row[1]}[6] = sprintf("%.2f",$row[2]+$row[3]+$row[4]+$row[5]);
	}
	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_tarifas_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>SECTOR ID</TD>
			  <TD class=tdheader2>SECTOR</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>{sector}</TD>
			<TD class=my_style2>{sector_label}</TD>			
			<TD class=my_style2>{tarifa}</TD>
			<TD class=my_style2>{tarifa_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>&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 %tarifas_mora){
		foreach my $kk (sort keys %{$tarifas_mora{$k}}){
			if($tarifas_mora{$k}{$kk}[2] eq "" or $tarifas_mora{$k}{$kk}[2] eq "null"){$tarifas_mora{$k}{$kk}[2]=0.00}else{$tarifas_mora{$k}{$kk}[2]=sprintf("%.2f",$tarifas_mora{$k}{$kk}[2])}
			if($tarifas_mora{$k}{$kk}[3] eq "" or $tarifas_mora{$k}{$kk}[3] eq "null"){$tarifas_mora{$k}{$kk}[3]=0.00}else{$tarifas_mora{$k}{$kk}[3]=sprintf("%.2f",$tarifas_mora{$k}{$kk}[3])}
			if($tarifas_mora{$k}{$kk}[4] eq "" or $tarifas_mora{$k}{$kk}[4] eq "null"){$tarifas_mora{$k}{$kk}[4]=0.00}else{$tarifas_mora{$k}{$kk}[4]=sprintf("%.2f",$tarifas_mora{$k}{$kk}[4])}
			if($tarifas_mora{$k}{$kk}[5] eq "" or $tarifas_mora{$k}{$kk}[5] eq "null"){$tarifas_mora{$k}{$kk}[5]=0.00}else{$tarifas_mora{$k}{$kk}[5]=sprintf("%.2f",$tarifas_mora{$k}{$kk}[5])}
			if($tarifas_mora{$k}{$kk}[6] eq "" or $tarifas_mora{$k}{$kk}[6] eq "null"){$tarifas_mora{$k}{$kk}[6]=0.00}else{$tarifas_mora{$k}{$kk}[6]=sprintf("%.2f",$tarifas_mora{$k}{$kk}[6])}
	
			$total_val	+= $tarifas_mora{$k}{$kk}[2];
			$total_fie	+= $tarifas_mora{$k}{$kk}[3];
			$total_int	+= $tarifas_mora{$k}{$kk}[4];		
			$total_mul	+= $tarifas_mora{$k}{$kk}[5];
			$total_tot	+= $tarifas_mora{$k}{$kk}[6];

	
			my $sector_label		= get_sector_label($k);
			my ($tarifa_label,$dtd) = get_tarifa_label($kk);
	
			$line = $row;
			$line =~ s/\{count}/$count/g;
			$line =~ s/\{sector}/$k/g;
			$line =~ s/\{sector_label}/$sector_label/g;
			$line =~ s/\{tarifa}/$kk/g;
			$line =~ s/\{tarifa_label}/$tarifa_label/g;
			$line =~ s/\{val}/$tarifas_mora{$k}{$kk}[2]/g;
			$line =~ s/\{fie}/$tarifas_mora{$k}{$kk}[3]/g;
			$line =~ s/\{int}/$tarifas_mora{$k}{$kk}[4]/g;
			$line =~ s/\{mul}/$tarifas_mora{$k}{$kk}[5]/g;
			$line =~ s/\{tot}/$tarifas_mora{$k}{$kk}[6]/g;
	
			$rows .= $line;
			$count++;
		}
	}
	$total_val	= round($total_val,2);
	$total_fie	= round($total_fie,2);	
	$total_int	= round($total_int,2);	
	$total_mul	= round($total_mul,2);
	$total_tot	= round($total_tot,2);	

	$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_tarifa_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_tarifa_label')};
	if($db->FetchRow()) 	{
		undef my @row;
		@row = $db->Data();
		$label = $row[0];
		$desde = $row[1];
	}
	return $label,$desde;	
}
sub get_sector_label {
	my ($id)=@_;
	my $label = "";
	my $desde = "";
	my $sql	  = "SELECT v64_sector FROM sdm_sectores where c11_sector_id='$id'";
	if ( $db->Sql($sql) ){SQL_fail($sql,$db,'sub get_sectores_label')};
	if($db->FetchRow()) 	{
		undef my @row;
		@row = $db->Data();
		$label = $row[0];
	}
	return $label;	
}
sub round {
    my ($n, $places) = @_;
    my $abs = abs $n;
    my $val = substr($abs + ('0.' . '0' x $places . '5'),0,length(int($abs)) +(($places > 0) ? $places + 1 : 0));
    ($n < 0) ? "-" . $val : $val;
}