#!/perl/bin/perl.exe
$|=1;
#------------------------------------------------------
#Perl Modules
use Win32::ODBC;
use CGI;
#------------------------------------------------------
#------------------------------------------------------
#HTML printing activation
print "Content-type: text/html\n\n";
#------------------------------------------------------
#------------------------------------------------------
#Get parameters
$form	 = new CGI;
$lan	 = $form->param("lan");
$appid	 = $form->param("appid");
#------------------------------------------------------
#------------------------------------------------------
#Define subdirectories and general variables
$PATH1 		= "../".$lan."/";
$PATH 		= "../".$lan."/inmuebles_files/";##los archivos de sectores y parcelas estan aqui
$file_html	= $lan."_get_calles".".html";
#------------------------------------------------------
#------------------------------------------------------
# ODBC
if (!($db = new Win32::ODBC($appid))){print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
if (!($db1 = new Win32::ODBC($appid))){print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}

#------------------------------------------------------
#------------------------------------------------------
#Main Program
#init_file_table();
#update_record();
#print $PATH.$filename1;
print "<table border=1><tr><td>Sector</td><td>Calle SIM</td><td></tr>";
get_calles();
print "</table>";
##insert_inmuebles_rurales();##pendiente por ni poseer los nits
construct_page();
$db->Close();
exit;
#------------------------------------------------------
#------------------------------------------------------
# SUB-ROUTINES

sub get_calles{
	
	#print $PATH.$filename1;
	#insertando los registros de los usuarios desde el archivo de excel que esta en la carpeta es:usuarios
		$sql="select c11_sector_id,c4_avenida_id,v64_avenida from sdd_sector_avenidas where c8_cnr_calle like '%S/N%'";
		#print $sql."<br>";
		if ($db->Sql($sql)){print "SQL failed. insert inmuebles<br>";print "$sql<br>";print "Error: ".$db->Error()."<br>";$db->Close();exit;}							
		while($db->FetchRow()){
			undef @rown;
			@rown=$db->Data();
			$c11_sector_id=@rown[0];
			$c4_avenida_id=@rown[1];
			$v64_avenida=$rown[2];
			$sql="select v64_sector from sdm_sectores where c11_sector_id='$c11_sector_id'";
			if ($db1->Sql($sql)){print "SQL failed. insert inmuebles<br>";print "$sql<br>";print "Error: ".$db1->Error()."<br>";$db1->Close();exit;}							
			while($db1->FetchRow()){undef @dt;@dt=$db1->Data();$v64_sector=$dt[0];}
			
			print "<tr><td>$v64_sector</td><td>$v64_avenida</td></tr>";
		}

		$sql="select c11_sector_id,c4_calle_id,v64_calle from sdd_sector_calles where c8_cnr_calle like '%S/N%'";
		#print $sql."<br>";
		if ($db->Sql($sql)){print "SQL failed. insert inmuebles<br>";print "$sql<br>";print "Error: ".$db->Error()."<br>";$db->Close();exit;}							
		while($db->FetchRow()){
			undef @rown;
			@rown=$db->Data();
			$c11_sector_id=@rown[0];
			$c4_calle_id=@rown[1];
			$v64_calle=$rown[2];
			$sql="select v64_sector from sdm_sectores where c11_sector_id='$c11_sector_id'";
			if ($db1->Sql($sql)){print "SQL failed. insert inmuebles<br>";print "$sql<br>";print "Error: ".$db1->Error()."<br>";$db1->Close();exit;}							
			while($db1->FetchRow()){undef @dt;@dt=$db1->Data();$v64_sector=$dt[0];}
			
			print "<tr><td>$v64_sector</td><td>$v64_calle</td></tr>";
		}

		$sql="select c11_sector_id,c2_pasaje_id,v64_pasaje from sdd_sector_pasajes where c8_cnr_calle like '%S/N%'";
		#print $sql."<br>";
		if ($db->Sql($sql)){print "SQL failed. insert inmuebles<br>";print "$sql<br>";print "Error: ".$db->Error()."<br>";$db->Close();exit;}							
		while($db->FetchRow()){
			undef @rown;
			@rown=$db->Data();
			$c11_sector_id=@rown[0];
			$c2_pasaje_id=@rown[1];
			$v64_pasaje=@rown[2];
			$sql="select v64_sector from sdm_sectores where c11_sector_id='$c11_sector_id'";
			if ($db1->Sql($sql)){print "SQL failed. insert inmuebles<br>";print "$sql<br>";print "Error: ".$db1->Error()."<br>";$db1->Close();exit;}							
			while($db1->FetchRow()){undef @dt;@dt=$db1->Data();$v64_sector=$dt[0];}
			
			print "<tr><td>$v64_sector</td><td>$v64_pasaje</td></tr>";
		}

		$sql="select c11_sector_id,c2_senda_id,v64_senda from sdd_sector_sendas where c8_cnr_calle like '%S/N%'";
		#print $sql."<br>";
		if ($db->Sql($sql)){print "SQL failed. insert inmuebles<br>";print "$sql<br>";print "Error: ".$db->Error()."<br>";$db->Close();exit;}							
		while($db->FetchRow()){
			undef @rown;
			@rown=$db->Data();
			$c11_sector_id=@rown[0];
			$c2_senda_id=@rown[1];
			$v64_senda=@rown[2];
			$sql="select v64_sector from sdm_sectores where c11_sector_id='$c11_sector_id'";
			if ($db1->Sql($sql)){print "SQL failed. insert inmuebles<br>";print "$sql<br>";print "Error: ".$db1->Error()."<br>";$db1->Close();exit;}							
			while($db1->FetchRow()){undef @dt;@dt=$db1->Data();$v64_sector=$dt[0];}
			
			
			print "<tr><td>$v64_sector</td><td>$v64_senda</td></tr>";
		}

	
}
##insertar los frentes del inmueble


sub construct_page {
	open (DATA, "$PATH1$file_html");
		while ($line = <DATA>){
			$line =~ s/{lan}/$lan/g;
			$line =~ s/{appid}/$appid/g;
			$line =~ s/{total_contribuyentes}/$i/g;
			$line =~ s/{total_direcciones}/$ii/g;
			print $line;
		}		
	close (DATA);
}