#!/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');
$c16_tarifa_id  = $form->param('c16_tarifa_id');
#---------------------------------------------------------------
#-------------------------------------------------------------------
#Define los subdirectorios a utilizar en el programa
$PATH		= "../".$lan."/";
$file   	= "es_mora_tarifa_sectores_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;}
#-------------------------------------------------------
%sectores = get_tarifa_sectores();
$table	  = construct_table();
construct_page();
$db->Close();
exit;
#---------------------------------------------------------------
#---------------------------------------------------------------
#funcion para obtener expedientes
sub get_tarifa_sectores {
	my %secs = ();
	my $sql  = "select a.c11_sector_id,
				count(DISTINCT(a.i8_inmueble_id)),
				SUM(b.d82_val),
				SUM(b.d82_fie),
				SUM(b.d82_int),
				SUM(b.d82_mul)
			 from sdm_inmuebles a
			      left join sdd_inmueble_estados_mensuales b on a.i8_inmueble_id =  b.i8_inmueble_id
			 where b.i4_dias_mora>0 and b.d82_val>0  and a.c16_estado='ACEPTADO' and b.c16_tarifa_id='$c16_tarifa_id'
			 group by a.c11_sector_id
			 order by a.c11_sector_id
		    ";
	if($clase eq "empresa"){
	   $sql  = "select a.c11_sector_id,
				count(DISTINCT(a.i8_empresa_id)),
				SUM(b.d82_val),
				SUM(b.d82_fie),
				SUM(b.d82_int),
				SUM(b.d82_mul)
			 from sdd_empresa_ubicacion a
			      left join `sdd_empresa_estados_mensuales` b on a.`i8_empresa_id` =  b.`i8_empresa_id`
			 where b.i4_dias_mora>0 and b.`d82_val>0 and b.`c16_tarifa_id='$c16_tarifa_id
			 group by a.c11_sector_id
			 order by a.c11_sector_id
		   ";
	}	    
	if ( $db->Sql($sql) ){SQL_fail($sql,$db,'sub get_sectores')};
	while($db->FetchRow()) 	{
		undef my @row;
		@row = $db->Data();
		$secs{$row[0]} = [@row];
		$secs{$row[0]}[6] = sprintf("%.2f",$row[2]+$row[3]+$row[4]+$row[5]);		
	}
	return %secs;
}
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></td>
			  <TD class=tdheader2></TD>
			  <td class=tdheader2></td>
			  <td class=tdheader2 colspan=6>EXPEDIENTES</td>
			  <td class=tdheader2 colspan=6>CAESS</td>			  
		       </tr>
		      <tr class=tdheader2>
			  <td class=tdheader2>No.</td>
			  <TD class=tdheader2>TIPO SECTOR</TD>
			  <td class=tdheader2>DESCRIPCION</td>
			  <td class=tdheader2>CANTIDAD</td>
			  <td class=tdheader2>MONTO</td>
			  <td class=tdheader2>FIESTA</td>
			  <td class=tdheader2>INTERES</td>
			  <td class=tdheader2>MULTA</td>
			  <td class=tdheader2>MORA</td>
			  <td class=tdheader2>CANTIDAD</td>
			  <td class=tdheader2>MONTO</td>
			  <td class=tdheader2>FIESTA</td>
			  <td class=tdheader2>INTERES</td>
			  <td class=tdheader2>MULTA</td>
			  <td class=tdheader2>MORA</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 align=right>{exp}</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_exp}</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 %sectores){
		if($sectores{$k}[2] eq "" or $sectores{$k}[2] eq "null"){$sectores{$k}[2]=0.00}else{$sectores{$k}[2]=sprintf("%.2f",$sectores{$k}[2])}
		if($sectores{$k}[3] eq "" or $sectores{$k}[3] eq "null"){$sectores{$k}[3]=0.00}else{$sectores{$k}[3]=sprintf("%.2f",$sectores{$k}[3])}
		if($sectores{$k}[4] eq "" or $sectores{$k}[4] eq "null"){$sectores{$k}[4]=0.00}else{$sectores{$k}[4]=sprintf("%.2f",$sectores{$k}[4])}
		if($sectores{$k}[5] eq "" or $sectores{$k}[5] eq "null"){$sectores{$k}[5]=0.00}else{$sectores{$k}[5]=sprintf("%.2f",$sectores{$k}[5])}
		if($sectores{$k}[6] eq "" or $sectores{$k}[6] eq "null"){$sectores{$k}[6]=0.00}else{$sectores{$k}[6]=sprintf("%.2f",$sectores{$k}[6])}

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

		$val		 = big_money($sectores{$k}[2]);
                $fie             = big_money($sectores{$k}[3]);
                $int             = big_money($sectores{$k}[4]);
                $mul             = big_money($sectores{$k}[5]);
                $tot             = big_money($sectores{$k}[6]);		
		
		$sector_label    = get_label($k);

		$line = $row;
		$line =~ s/\{count}/$count/g;
		$line =~ s/\{sector}/$k/g;
		$line =~ s/\{sector_label}/$sector_label/g;	 
		$line =~ s/\{exp}/$sectores{$k}[1]/g;
		$line =~ s/\{val}/$val/g;	 
		$line =~ s/\{fie}/$fie/g;	 
		$line =~ s/\{int}/$int/g;	 
		$line =~ s/\{mul}/$mul/g;
		$line =~ s/\{tot}/$tot/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_exp}/$total_exp/g;
	$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");
	my ($v128_tarifa,$dtd) = get_rubro_label($c16_tarifa_id);	
	while ($line = <DATA>) {	
                $line =~ s/\{lan}/$lan/g;
                $line =~ s/\{appid}/$appid/g;
                $line =~ s/\{clase}/$clase/g;
                $line =~ s/\{c16_tarifa_id}/$c16_tarifa_id/g;
                $line =~ s/\{v128_tarifa}/$v128_tarifa/g;
                $line =~ s/\{dtd}/$dtd/g;	       
                $line =~ s/\{titulo_clase}/$titulo_clase/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_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_rubro_label')};
	if($db->FetchRow()) 	{
		undef my @row;
		@row = $db->Data();
		$label = $row[0];
	}
	return $label;	
}
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;
}