#!/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');
$table 		= "<table cellspacing=0 cellpadding=0 width=100% border=1 bordercolor=#E5E5E5 style=border-width:0.5>";
#---------------------------------------------------------------
#-------------------------------------------------------------------
#Define los subdirectorios a utilizar en el programa
$PATH		= "../".$lan."/";
$file   	= "es_mora_sector_empresa_rept.html";          
#---------------------------------------------------------------
#---------------------------------------------------------------
if ( !( $db = new Win32::ODBC($appid) ) ) {print "Error connecting to $db <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
if ( !( $db1 = new Win32::ODBC($appid) ) ) {print "Error connecting to $db1 <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
#-------------------------------------------------------
get_records();
construct_page();
$db->Close();
$db1->Close();
exit;
#---------------------------------------------------------------
#---------------------------------------------------------------
#funcion para obtener expedientes
sub get_records {
	$table.= "<tr class=tdheader2><td class=tdheader2>No.</td>
	<TD class=tdheader2>TIPO SECTOR</TD>
	<td class=tdheader2>DESCRIPCION</td>
        <td class=tdheader2>TOTAL&nbsp;EXPEDIENTES</td>
	<td class=tdheader2>EXPEDIENTES&nbsp;MORA</td>
	<td class=tdheader2>MONTO&nbsp;MORA</td>
	<td class=tdheader2>% EXPEDIENTES&nbsp;MORA</td></tr>";
        $sql="SELECT a.c11_sector_id, a.v64_sector, b.v64_tipo_sector from sdm_sectores a inner join sdm_tipo_sectores b
        on a.i2_tipo_sector_id = b.i2_tipo_sector_id and a.c11_sector_id!='SV0105U01124' order by a.c11_sector_id";
        $cnt = 1;
	if ( $db->Sql($sql) ){SQL_fail($sql,$db,'sub get_records - Sectores')};
	while($db->FetchRow()) 	{
		undef my @row1;
		@row1	= $db->Data();
                $c11_sector_id  = $row1[0];
		$v64_sector     = $row1[1];
                $v64_tipo_sector= $row1[2];
                $total_inm      = get_count_expedientes($c11_sector_id,'');
                $total_expedientes+=$total_inm;
                $table.= "<tr><td class=my_style2>$cnt</td>
			      <td class=my_style2>$c11_sector_id</td>
			      <td class=my_style2><a href=javascript:OpenSector('$c11_sector_id')>$v64_sector</a></td>
			      <td class=my_style2>$total_inm</td>";                
                $total_inm_srv  = get_count_expedientes($c11_sector_id,'mora');
                $total_expedientes_mora+=$total_inm_srv;
                $table.="<td class=my_style2><center><a href=javascript:OpenMoraSector('$c11_sector_id','1')>$total_inm_srv&nbsp;</a></center></td>";
                $mora=get_mora_sector($c11_sector_id);
		$mora = sprintf("%.2f",$mora);
                $total_mora+=$mora;
                if($total_inm>0){$percent_mora=($total_inm_srv/$total_inm)*100}else{$percent_mora=0};
                $percent_mora=sprintf("%.2f",$percent_mora);
                $table.="<td class=my_style2 align=right><a href=javascript:OpenMoraSector('$c11_sector_id','2')>$mora</a>&nbsp;</td>";
                $table.="<td class=my_style2><center>$percent_mora&nbsp;</center></td>";
                $totales[$i] = $totales[$i] + $total_inm_srv;
                $table.="</tr>";                
                $cnt = $cnt + 1;
	}
        $table.= "<tr><td colspan=3 class=my_style2>&nbsp;Totales</td><td class=my_style2>$total_expedientes</td><td class=my_style2>$total_expedientes_mora</td><td class=my_style2>$total_mora</td><td>&nbsp;</td></tr>";
        $table.= "</table>";
        #print $table;
}
sub get_count_expedientes {
    my($c11_sector_id, $type)=@_;
    $sql = "select count(*)
	    from sdm_empresas a
	    inner join sdd_empresa_ubicacion b
	    on a.i8_empresa_id=b.i8_empresa_id
	    where b.c11_sector_id = '$c11_sector_id' and a.c16_estado='ACEPTADO'";
    if($type eq "mora"){
    $sql = "select count(distinct(a.i8_empresa_id))
	     from sdd_empresa_estados_mensuales a
	     inner join sdm_empresas  b
	     on a.i8_empresa_id=b.i8_empresa_id
	     inner join sdd_empresa_ubicacion c on a.i8_empresa_id=c.i8_empresa_id
             where c.c11_sector_id='$c11_sector_id' and a.i4_dias_mora>0";}
    if ( $db1->Sql($sql) ){SQL_fail($db1,$sql,'sub get_records - obtener Numero expedientes')};
    while($db1->FetchRow()) {
		undef my @row1;
		@row	= $db1->Data();	
    }
    return ($row[0]);

}
sub get_mora_sector {
        my($c11_sector_id)=@_;
        $sql="select (sum(a.d82_val)+sum(a.d82_fie)+sum(a.d82_int)+sum(a.d82_mul))
	      from sdd_empresa_estados_mensuales a
	      inner join sdm_empresas b on a.i8_empresa_id = b.i8_empresa_id
	      inner join sdd_empresa_ubicacion c on b.i8_empresa_id = c.i8_empresa_id
              where c.c11_sector_id='$c11_sector_id' and a.i4_dias_mora>0";
        if ( $db1->Sql($sql) ){SQL_fail($sql,$db1,'Get Mora sector $c11_Sector_id')};
        while($db1->FetchRow()){
                    undef my @row1;
                    @row	= $db1->Data();	
        }
        return ($row[0]);
}
sub construct_page {
	open (DATA, "$PATH$file");
        #print "$PATH$file";
	while ($line = <DATA>)
        {	
		$line =~ s/\{table}/$table/g;
                $line =~ s/\{appid}/$appid/g;
                $line =~ s/\{lan}/$lan/g;
                $line =~ s/\{clase}/$clase/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;
}
#funcion para crear la pagina

