#!/perl/bin/perl.exe
$|=1;
#---------------------------------------------------------------------------
#--------------------------------------------------------------------------
# Web Software SA
# Date In: 2013-01-21
# Updates: 2013-01-21,2016-03-22
# Author:Rodolfo Machon
# File Name: mora_sector_rept.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;
use DBI;
use XML::Simple;
use Spreadsheet::WriteExcel;
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
#GET PARAMETERS
$cgi = new CGI;
$id	 = $cgi->param('id');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS

#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
get_app_data();
open_database_connections();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES

#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
$result = get_mora_contribuyentes();
print $result;
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_mora_contribuyentes {
	# Create an Excel workbook and worksheet
	my $tipo = substr($id,0,3);
	my $per  = substr($id,3,6);
	my $filename = "../archivos_mora/Mora_".$id.".xls";
	if($tipo eq "INM"){inmuebles_excel($filename,$per);}
	if($tipo eq "EMP"){empresas_excel($filename,$per);}
    return 1;	
}

#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 2
sub inmuebles_excel{
	my ($filename,$per)= @_;
	my $workbook = Spreadsheet::WriteExcel->new($filename);
	my $worksheet = $workbook->add_worksheet();
		$worksheet->write(0, 0, "INM");
		$worksheet->write(0, 1, "CONTRIBUYENTE");
		$worksheet->write(0, 2, "REFERENCIA");
		$worksheet->write(0, 3, "ASEO_TASA");
		$worksheet->write(0, 4, "ASEO_FIESTA");
		$worksheet->write(0, 5, "ASEO_INTERES");
		$worksheet->write(0, 6, "ASEO_MULTA");
		$worksheet->write(0, 7, "ASEO1");
		$worksheet->write(0, 8, "ASEO1_FIESTA");
		$worksheet->write(0, 9, "ASEO1_INTERES");
		$worksheet->write(0, 10,"ASEO1_MULTA");
		$worksheet->write(0, 11,"ALUMBRADO");
		$worksheet->write(0, 12,"ALUMBRADO_FIESTA");
		$worksheet->write(0, 13,"ALUMBRADO_INTERES");
		$worksheet->write(0, 14,"ALUMBRADO_MULTA");
		$worksheet->write(0, 15,"PAVIMENTO");
		$worksheet->write(0, 16,"PAVIMENTO_FIEST");
		$worksheet->write(0, 17,"PAVIMENTO_INTERES");
		$worksheet->write(0, 18,"PAVIMENTO_MULTA");
		$worksheet->write(0, 19,"DESECHO");
		$worksheet->write(0, 20,"DESECHO_FIESTA");
		$worksheet->write(0, 21,"DESECHO_INTERES");
		$worksheet->write(0, 22,"DESECHO_MULTA");
		$worksheet->write(0, 23,"BARRIDO");
		$worksheet->write(0, 24,"BARRIDO_FIESTA");
		$worksheet->write(0, 25,"BARRIDO_INTERES");
		$worksheet->write(0, 26,"BARRIDO_MULTA");
		$worksheet->write(0, 27,"AGUA");
		$worksheet->write(0, 28,"AGUA_FIESTA");
		$worksheet->write(0, 29,"AGUA_INTERES");
		$worksheet->write(0, 30,"AGUA_MULTA");
		$worksheet->write(0, 31,"DESDE");
		$worksheet->write(0, 32,"HASTA");
    my $sql  = "
				select a.`i8_inmueble_id`
					   ,d.v128_contribuyente
					   ,b.`txt_referencia`
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMASE',a.`d82_val`,0)) as 'ASEO_TASA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMASE',a.`d82_fie`,0)) as 'ASEO_FIESTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMASE',a.`d82_int`,0)) as 'ASEO_INTERES'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMASE',a.`d82_mul`,0)) as 'ASEO_MULTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAS1',a.`d82_val`,0)) as 'ASEO1 1'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAS1',a.`d82_fie`,0)) as 'ASEO1_FIESTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAS1',a.`d82_int`,0)) as 'ASEO1_INTERES'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAS1',a.`d82_mul`,0)) as 'ASEO1_MULTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMALU',a.`d82_val`,0)) as 'ALUMBRADO'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMALU',a.`d82_fie`,0)) as 'ALUMBRADO_FIESTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMALU',a.`d82_int`,0)) as 'ALUMBRADO_INTERES'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMALU',a.`d82_mul`,0)) as 'ALUMBRADO_MULTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMCAL',a.`d82_val`,0)) as 'PAVIMENTO'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMCAL',a.`d82_fie`,0)) as 'PAVIMENTO_FIESTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMCAL',a.`d82_int`,0)) as 'PAVIMENTO_INTERES'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMCAL',a.`d82_mul`,0)) as 'PAVIMENTO_MULTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMDES',a.`d82_val`,0)) as 'DESECHO'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMDES',a.`d82_fie`,0)) as 'DESECHO_FIESTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMDES',a.`d82_int`,0)) as 'DESECHO_INTERES'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMDES',a.`d82_mul`,0)) as 'DESECHO_MULTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMBAR',a.`d82_val`,0)) as 'BARRIDO'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMBAR',a.`d82_fie`,0)) as 'BARRIDO_FIESTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMBAR',a.`d82_int`,0)) as 'BARRIDO_INTERES'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMBAR',a.`d82_mul`,0)) as 'BARRIDO_MULTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAGU',a.`d82_val`,0)) as 'AGUA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAGU',a.`d82_fie`,0)) as 'AGUA_FIESTA'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAGU',a.`d82_int`,0)) as 'AGUA_INTERES'
						,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAGU',a.`d82_mul`,0)) as 'AGUA_MULTA'
						,MIN(a.`i8_periodo`) as 'DESDE'
						,MAX(a.`i8_periodo`) as 'HASTA'
				from sdr_inmuebles_mora_detalle_".$per." a
					  left join sdm_inmuebles b on b.i8_inmueble_id= a.`i8_inmueble_id`
					  left join sdd_inmueble_contribuyentes c on b.i8_inmueble_id = c.i8_inmueble_id
					  left join sdm_contribuyentes d on c.i8_contribuyente_id = d.i8_contribuyente_id
				where a.i4_dias_mora > 0 and
					  a.d82_val > 0 and
					  b.c16_estado = 'ACEPTADO'
				group by a.`i8_inmueble_id`
				";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_mora_contribuyente');
	my $row = 1;
	while (my @row = $sth->fetchrow_array) {
		$worksheet->write($row, 0, $row[0]);
		$worksheet->write($row, 1, $row[1]);
		$worksheet->write($row, 2, $row[2]);
		$worksheet->write($row, 3, $row[3]);
		$worksheet->write($row, 4, $row[4]);
		$worksheet->write($row, 5, $row[5]);
		$worksheet->write($row, 6, $row[6]);
		$worksheet->write($row, 7, $row[7]);
		$worksheet->write($row, 8, $row[8]);
		$worksheet->write($row, 9, $row[9]);
		$worksheet->write($row, 10, $row[10]);
		$worksheet->write($row, 11, $row[11]);
		$worksheet->write($row, 12, $row[12]);
		$worksheet->write($row, 13, $row[13]);
		$worksheet->write($row, 14, $row[14]);
		$worksheet->write($row, 15, $row[15]);
		$worksheet->write($row, 16, $row[16]);
		$worksheet->write($row, 17, $row[17]);
		$worksheet->write($row, 18, $row[18]);
		$worksheet->write($row, 19, $row[19]);
		$worksheet->write($row, 20, $row[20]);
		$worksheet->write($row, 21, $row[21]);
		$worksheet->write($row, 22, $row[22]);
		$worksheet->write($row, 23, $row[23]);
		$worksheet->write($row, 24, $row[24]);
		$worksheet->write($row, 25, $row[25]);
		$worksheet->write($row, 26, $row[26]);
		$worksheet->write($row, 27, $row[27]);
		$worksheet->write($row, 28, $row[28]);
		$worksheet->write($row, 29, $row[29]);
		$worksheet->write($row, 30, $row[30]);
		$worksheet->write($row, 31, $row[31]);
		$worksheet->write($row, 32, $row[32]);
		$row++
    }	
	$workbook->close();
}
sub empresas_excel{
	my ($filename,$per)= @_;
	my $workbook = Spreadsheet::WriteExcel->new($filename);
	my $worksheet = $workbook->add_worksheet();
		$worksheet->write(0,  0,"EMP");
		$worksheet->write(0,  1,"CONTRIBUYENTE");
		$worksheet->write(0,  2,"REFERENCIA");	
		$worksheet->write(0,  3,"ASEO_TASA");
		$worksheet->write(0,  4,"ASEO_FIESTA");
		$worksheet->write(0,  5,"ASEO_INTERES");
		$worksheet->write(0,  6,"ASEO_MULTA");
		$worksheet->write(0,  7,"ASEO1 1");
		$worksheet->write(0,  8,"ASEO1_FIESTA");
		$worksheet->write(0,  9,"ASEO1_INTERES");
		$worksheet->write(0, 10,"ASEO1_MULTA");
		$worksheet->write(0, 11,"ALUMBRADO");
		$worksheet->write(0, 12,"ALUMBRADO_FIESTA");
		$worksheet->write(0, 13,"ALUMBRADO_INTERES");
		$worksheet->write(0, 14,"ALUMBRADO_MULTA");
		$worksheet->write(0, 15,"PAVIMENTO");
		$worksheet->write(0, 16,"PAVIMENTO_FIESTA");
		$worksheet->write(0, 17,"PAVIMENTO_INTERES");
		$worksheet->write(0, 18,"PAVIMENTO_MULTA");
		$worksheet->write(0, 19,"DESECHO");
		$worksheet->write(0, 20,"DESECHO_FIESTA");
		$worksheet->write(0, 21,"DESECHO_INTERES");
		$worksheet->write(0, 22,"DESECHO_MULTA");
		$worksheet->write(0, 23,"BARRIDO");
		$worksheet->write(0, 24,"BARRIDO_FIESTA");
		$worksheet->write(0, 25,"BARRIDO_INTERES");
		$worksheet->write(0, 26,"BARRIDO_MULTA");
		$worksheet->write(0, 27,"COMERCIO");
		$worksheet->write(0, 28,"COMERCIO_FIESTA");
		$worksheet->write(0, 29,"COMERCIO_INTERES");
		$worksheet->write(0, 31,"COMERCIO_MULTA");
		$worksheet->write(0, 32,"FINANCIERO");
		$worksheet->write(0, 33,"FINANCIERO_FIESTA");
		$worksheet->write(0, 34,"FINANCIERO_INTERES");
		$worksheet->write(0, 35,"FINANCIERO_MULTA");
		$worksheet->write(0, 36,"SERVICIO");
		$worksheet->write(0, 37,"SERVICIO_FIESTA");
		$worksheet->write(0, 38,"SERVICIO_INTERES");
		$worksheet->write(0, 39,"SERVICIO_MULTA");
		$worksheet->write(0, 40,"INDUSTRIA");
		$worksheet->write(0, 41,"INDUSTRIA_FIESTA");
		$worksheet->write(0, 42,"INDUSTRIA_INTERES");
		$worksheet->write(0, 43,"INDUSTRIA_MULTA");
		$worksheet->write(0, 44,"POSTES");
		$worksheet->write(0, 45,"POSTES_FIESTA");
		$worksheet->write(0, 46,"POSTES_INTERES");
		$worksheet->write(0, 47,"POSTES_MULTA");
		$worksheet->write(0, 48,"POSTES1");
		$worksheet->write(0, 49,"POSTES1_FIESTA");
		$worksheet->write(0, 50,"POSTES1_INTERES");
		$worksheet->write(0, 51,"POSTES1_MULTA");
		$worksheet->write(0, 52,"VALLAS");
		$worksheet->write(0, 53,"VALLAS_FIESTA");
		$worksheet->write(0, 54,"VALLAS_INTERES");
		$worksheet->write(0, 55,"VALLAS_MULTA");
		$worksheet->write(0, 56,"ROTULOS");
		$worksheet->write(0, 57,"ROTULOS_FIESTA");
		$worksheet->write(0, 58,"ROTULOS_INTERES");
		$worksheet->write(0, 59,"ROTULOS_MULTA");
		$worksheet->write(0, 60,"ROTULOS2");
		$worksheet->write(0, 61,"ROTULOS2_FIESTA");
		$worksheet->write(0, 62,"ROTULOS2_INTERES");
		$worksheet->write(0, 63,"ROTULOS2_MULTA");
		$worksheet->write(0, 64,"ROTULOS3");
		$worksheet->write(0, 65,"ROTULOS3_FIESTA");
		$worksheet->write(0, 66,"ROTULOS3_INTERES");
		$worksheet->write(0, 67,"ROTULOS3_MULTA");
		$worksheet->write(0, 68,"MOTOTAXI");
		$worksheet->write(0, 69,"MOTOTAXI_FIESTA");
		$worksheet->write(0, 70,"MOTOTAXI_INTERES");
		$worksheet->write(0, 71,"MOTOTAXI_MULTA");
		$worksheet->write(0, 72,"DESDE");
		$worksheet->write(0, 73,"HASTA");
		
    my $sql  = "
			select a.`i8_empresa_id`
				,d.v128_contribuyente
				,e.txt_referencia
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMASE',a.`d82_val`,0)) as 'ASEO_TASA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMASE',a.`d82_fie`,0)) as 'ASEO_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMASE',a.`d82_int`,0)) as 'ASEO_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMASE',a.`d82_mul`,0)) as 'ASEO_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAS1',a.`d82_val`,0)) as 'ASEO1 1'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAS1',a.`d82_fie`,0)) as 'ASEO1_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAS1',a.`d82_int`,0)) as 'ASEO1_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMAS1',a.`d82_mul`,0)) as 'ASEO1_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMALU',a.`d82_val`,0)) as 'ALUMBRADO'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMALU',a.`d82_fie`,0)) as 'ALUMBRADO_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMALU',a.`d82_int`,0)) as 'ALUMBRADO_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMALU',a.`d82_mul`,0)) as 'ALUMBRADO_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMCAL',a.`d82_val`,0)) as 'PAVIMENTO'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMCAL',a.`d82_fie`,0)) as 'PAVIMENTO_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMCAL',a.`d82_int`,0)) as 'PAVIMENTO_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMCAL',a.`d82_mul`,0)) as 'PAVIMENTO_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMDES',a.`d82_val`,0)) as 'DESECHO'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMDES',a.`d82_fie`,0)) as 'DESECHO_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMDES',a.`d82_int`,0)) as 'DESECHO_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMDES',a.`d82_mul`,0)) as 'DESECHO_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMBAR',a.`d82_val`,0)) as 'BARRIDO'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMBAR',a.`d82_fie`,0)) as 'BARRIDO_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMBAR',a.`d82_int`,0)) as 'BARRIDO_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSINMBAR',a.`d82_mul`,0)) as 'BARRIDO_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPCME',a.`d82_val`,0)) as 'COMERCIO'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPCME',a.`d82_fie`,0)) as 'COMERCIO_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPCME',a.`d82_int`,0)) as 'COMERCIO_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPCME',a.`d82_mul`,0)) as 'COMERCIO_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPFIN',a.`d82_val`,0)) as 'FINANCIERO'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPFIN',a.`d82_fie`,0)) as 'FINANCIERO_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPFIN',a.`d82_int`,0)) as 'FINANCIERO_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPFIN',a.`d82_mul`,0)) as 'FINANCIERO_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPIMS',a.`d82_val`,0)) as 'SERVICIO'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPIMS',a.`d82_fie`,0)) as 'SERVICIO_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPIMS',a.`d82_int`,0)) as 'SERVICIO_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPIMS',a.`d82_mul`,0)) as 'SERVICIO_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPIND',a.`d82_val`,0)) as 'INDUSTRIA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPIND',a.`d82_fie`,0)) as 'INDUSTRIA_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPIND',a.`d82_int`,0)) as 'INDUSTRIA_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPIND',a.`d82_mul`,0)) as 'INDUSTRIA_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSEMPPOS',a.`d82_val`,0)) as 'POSTES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSEMPPOS',a.`d82_fie`,0)) as 'POSTES_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSEMPPOS',a.`d82_int`,0)) as 'POSTES_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSEMPPOS',a.`d82_mul`,0)) as 'POSTES_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSEMPPO1',a.`d82_val`,0)) as 'POSTES1'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSEMPPO1',a.`d82_fie`,0)) as 'POSTES1_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSEMPPO1',a.`d82_int`,0)) as 'POSTES1_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSEMPPO1',a.`d82_mul`,0)) as 'POSTES1_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPVAL',a.`d82_val`,0)) as 'VALLAS'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPVAL',a.`d82_fie`,0)) as 'VALLAS_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPVAL',a.`d82_int`,0)) as 'VALLAS_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPVAL',a.`d82_mul`,0)) as 'VALLAS_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPROT',a.`d82_val`,0)) as 'ROTULOS'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPROT',a.`d82_fie`,0)) as 'ROTULOS_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPROT',a.`d82_int`,0)) as 'ROTULOS_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPROT',a.`d82_mul`,0)) as 'ROTULOS_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPRO2',a.`d82_val`,0)) as 'ROTULOS2'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPRO2',a.`d82_fie`,0)) as 'ROTULOS2_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPRO2',a.`d82_int`,0)) as 'ROTULOS2_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPRO2',a.`d82_mul`,0)) as 'ROTULOS2_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPRO3',a.`d82_val`,0)) as 'ROTULOS3'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPRO3',a.`d82_fie`,0)) as 'ROTULOS3_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPRO3',a.`d82_int`,0)) as 'ROTULOS3_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='TSIMPRO3',a.`d82_mul`,0)) as 'ROTULOS3_MULTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPMTX',a.`d82_val`,0)) as 'MOTOTAXI'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPMTX',a.`d82_fie`,0)) as 'MOTOTAXI_FIESTA'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPMTX',a.`d82_int`,0)) as 'MOTOTAXI_INTERES'
				,SUM(if(substring(a.c16_tarifa_id,1,8)='IMEMPMTX',a.`d82_mul`,0)) as 'MOTOTAXI_MULTA'
				,MIN(a.`i8_periodo`) as 'DESDE'
				,MAX(a.`i8_periodo`) as 'HASTA'
			from sdr_empresas_mora_detalle_".$per." a
			   left join sdm_empresas b on b.i8_empresa_id= a.`i8_empresa_id`
			   left join sdd_empresa_ubicacion e on e.i8_empresa_id= b.i8_empresa_id
			   left join sdd_empresa_contribuyentes c on b.i8_empresa_id = c.i8_empresa_id
			   left join sdm_contribuyentes d on c.i8_contribuyente_id = d.i8_contribuyente_id
			where a.i4_dias_mora > 0 and
			   a.d82_val > 0 and
			   b.c16_estado = 'ACEPTADO'
			group by a.`i8_empresa_id`
			";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_mora_contribuyente');
	my $row = 1;
	while (my @row = $sth->fetchrow_array) {
		$worksheet->write($row, 0, $row[0]);
		$worksheet->write($row, 1, $row[1]);
		$worksheet->write($row, 2, $row[2]);
		$worksheet->write($row, 3, $row[3]);
		$worksheet->write($row, 4, $row[4]);
		$worksheet->write($row, 5, $row[5]);
		$worksheet->write($row, 6, $row[6]);
		$worksheet->write($row, 7, $row[7]);
		$worksheet->write($row, 8, $row[8]);
		$worksheet->write($row, 9, $row[9]);
		$worksheet->write($row, 10, $row[10]);
		$worksheet->write($row, 11, $row[11]);
		$worksheet->write($row, 12, $row[12]);
		$worksheet->write($row, 13, $row[13]);
		$worksheet->write($row, 14, $row[14]);
		$worksheet->write($row, 15, $row[15]);
		$worksheet->write($row, 16, $row[16]);
		$worksheet->write($row, 17, $row[17]);
		$worksheet->write($row, 18, $row[18]);
		$worksheet->write($row, 19, $row[19]);
		$worksheet->write($row, 20, $row[20]);
		$worksheet->write($row, 21, $row[21]);
		$worksheet->write($row, 22, $row[22]);
		$worksheet->write($row, 23, $row[23]);
		$worksheet->write($row, 24, $row[24]);
		$worksheet->write($row, 25, $row[25]);
		$worksheet->write($row, 26, $row[26]);
		$worksheet->write($row, 27, $row[27]);
		$worksheet->write($row, 28, $row[28]);
		$worksheet->write($row, 30, $row[30]);
		$worksheet->write($row, 31, $row[31]);
		$worksheet->write($row, 32, $row[32]);
		$worksheet->write($row, 33, $row[33]);
		$worksheet->write($row, 34, $row[34]);
		$worksheet->write($row, 35, $row[35]);
		$worksheet->write($row, 36, $row[36]);
		$worksheet->write($row, 37, $row[37]);
		$worksheet->write($row, 38, $row[38]);
		$worksheet->write($row, 39, $row[39]);
		$worksheet->write($row, 40, $row[40]);
		$worksheet->write($row, 41, $row[41]);
		$worksheet->write($row, 42, $row[42]);
		$worksheet->write($row, 43, $row[43]);
		$worksheet->write($row, 44, $row[44]);
		$worksheet->write($row, 45, $row[45]);
		$worksheet->write($row, 46, $row[46]);
		$worksheet->write($row, 47, $row[47]);
		$worksheet->write($row, 48, $row[48]);
		$worksheet->write($row, 49, $row[49]);
		$worksheet->write($row, 50, $row[50]);
		$worksheet->write($row, 51, $row[51]);
		$worksheet->write($row, 52, $row[52]);
		$worksheet->write($row, 53, $row[53]);
		$worksheet->write($row, 54, $row[54]);
		$worksheet->write($row, 55, $row[55]);
		$worksheet->write($row, 56, $row[56]);
		$worksheet->write($row, 57, $row[57]);
		$worksheet->write($row, 58, $row[58]);
		$worksheet->write($row, 59, $row[59]);
		$worksheet->write($row, 60, $row[60]);
		$worksheet->write($row, 61, $row[61]);
		$worksheet->write($row, 62, $row[62]);
		$worksheet->write($row, 63, $row[63]);
		$worksheet->write($row, 64, $row[64]);
		$worksheet->write($row, 65, $row[65]);
		$worksheet->write($row, 66, $row[66]);
		$worksheet->write($row, 67, $row[67]);
		$worksheet->write($row, 68, $row[68]);
		$worksheet->write($row, 69, $row[69]);
		$worksheet->write($row, 70, $row[70]);
		$worksheet->write($row, 71, $row[71]);
		$worksheet->write($row, 72, $row[72]);
		$worksheet->write($row, 73, $row[73]);
		$row++;
    }	
	$workbook->close();
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub get_app_data {
    my $schema   = "app.data.xml";
    my $xml      = new XML::Simple;
    my $data 	  = $xml->XMLin("../xml/".$schema);
    $appid	  = $data->{portal}->{app};
    #$lan	  = $data->{portal}->{lang};
}
sub open_database_connections {
	$dbh 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
	if (!$dbh) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
	$dbh->{LongReadLen} = 512 * 1024;	
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
#---------------------------------------------------------------------------
    #my $file_size = -s qq{$fileName};
    #print $cgi->header({
    #    'content-type'        => 'application/excel',
    #    'content-disposition' => qq{attachment; filename='data.xls'}
    #});
    
	# Set response headers
	#print $cgi->header(
	#	'-type'  => 'application/vnd.ms-excel',
	#	'-attachment' => 'data.xls'
	#);

	#open my $fh, '<', 'data.xls' or die "Cannot open file: $!";
	#binmode $fh;
	#print while <$fh>;
	#close $fh;