<?
////////////////////////////////////////////////////
// Conciliación: gestión de conciliación de activos
// Version: 1.0
//
// Copyright (C) 2014  Eustotem Multimedia, S.L.
//
// License: MIT, see LICENSE
////////////////////////////////////////////////////


error_reporting(E_ALL);
ini_set('error_reporting', E_ALL);
$mapping=array(
	"smk_denominaciones"=>array(
		"sheet"=>6,
		"row"=>2,
		"col"=>1,
		"fields"=>array(
			array("col"=>0, "name"=>"id", "type"=>"number"),
			array("col"=>1, "name"=>"denominacion", "type"=>"string"),
		)
	),
	"smk_identificadores"=>array(
		"sheet"=>6,
		"row"=>2,
		"col"=>4,
		"fields"=>array(
			array("col"=>0, "name"=>"cod_identificador", "type"=>"string"),
			array("col"=>1, "name"=>"tipo_identificador", "type"=>"string"),
			array("col"=>2, "name"=>"inv_asociados", "type"=>"string"),
			array("col"=>3, "name"=>"comentarios", "type"=>"string"),
		)
	),
	"smk_cuentas"=>array(
		"sheet"=>6,
		"row"=>2,
		"col"=>9,
		"fields"=>array(
			array("col"=>0, "name"=>"codigo_cuenta", "type"=>"number"),
			array("col"=>1, "name"=>"nombre_cuenta", "type"=>"string"),
		)
	),
	"smk_ubicaciones"=>array(
		"sheet"=>3,
		"row"=>2,
		"col"=>1,
		"fields"=>array(
			array("col"=>0, "name"=>"nif", "type"=>"string"),
			array("col"=>1, "name"=>"cod_ubicacion", "type"=>"number"),
			array("col"=>2, "name"=>"denominacion", "type"=>"string"),
			array("col"=>3, "name"=>"direccion", "type"=>"string"),
			array("col"=>4, "name"=>"cpostal", "type"=>"string"),
			array("col"=>5, "name"=>"ciudad", "type"=>"string"),
			array("col"=>6, "name"=>"provincia", "type"=>"string"),
			array("col"=>7, "name"=>"pais", "type"=>"string"),
			array("col"=>8, "name"=>"num_expediente", "type"=>"string"),
			array("col"=>9, "name"=>"mes_valor", "type"=>"string"),
			array("col"=>10, "name"=>"anno_valor", "type"=>"number"),
			array("col"=>11, "name"=>"moneda", "type"=>"string"),
			array("col"=>12, "name"=>"tipo_produccion", "type"=>"string"),
			array("col"=>13, "name"=>"mes_visita", "type"=>"string"),
			array("col"=>14, "name"=>"dia_visita", "type"=>"number"),
			array("col"=>15, "name"=>"anno_certif", "type"=>"number"),
			array("col"=>16, "name"=>"mes_certif", "type"=>"string"),
			array("col"=>17, "name"=>"dia_certif", "type"=>"number"),
		)
	),
	"smk_conciliacion"=>array(
		"sheet"=>5,
		"row"=>5,
		"col"=>1,
		"fields"=>array(
			array("col"=>0, "name"=>"id", "type"=>"number"),
			array("col"=>1, "name"=>"grupo", "type"=>"number"),
			array("col"=>2, "name"=>"orden", "type"=>"number"),
			array("col"=>3, "name"=>"af_id_activo", "type"=>"number"),
			array("col"=>7, "name"=>"anno_capit", "type"=>"number"),
			array("col"=>8, "name"=>"descripcion", "type"=>"string"),
			array("col"=>9, "name"=>"val_adq", "type"=>"number"),
			array("col"=>10, "name"=>"amort_acum", "type"=>"number"),
			array("col"=>11, "name"=>"val_cont", "type"=>"number"),
			array("col"=>12, "name"=>"moneda", "type"=>"string"),
			array("col"=>13, "name"=>"af_num_activo", "type"=>"number"),
			array("col"=>14, "name"=>"id_ubic", "type"=>"number"),
			array("col"=>15, "name"=>"id_cuenta", "type"=>"number"),
			array("col"=>16, "name"=>"id_denominacion", "type"=>"number"),
			array("col"=>18, "name"=>"id_galtier", "type"=>"number"),
			array("col"=>19, "name"=>"id_subactivo", "type"=>"string"),
			array("col"=>20, "name"=>"descripcion_aux", "type"=>"string"),
			array("col"=>21, "name"=>"id_cuenta_aux", "type"=>"number"),
			array("col"=>23, "name"=>"val_adq_aux", "type"=>"number"),
			array("col"=>24, "name"=>"val_cont_aux", "type"=>"number"),
			array("col"=>25, "name"=>"id_ubic_aux", "type"=>"string"),
			array("col"=>26, "name"=>"id_elemento_aux", "type"=>"number"),
			array("col"=>27, "name"=>"id_padre_aux", "type"=>"number"),
			array("col"=>28, "name"=>"identif_aux", "type"=>"string"),
			array("col"=>29, "name"=>"anno_pem", "type"=>"number"),
			array("col"=>30, "name"=>"area", "type"=>"string"),
			array("col"=>31, "name"=>"contabilizacion", "type"=>"string"),
			array("col"=>32, "name"=>"val_resid", "type"=>"number"),
			array("col"=>33, "name"=>"annos_amort", "type"=>"number"),
			array("col"=>34, "name"=>"tipo_amort", "type"=>"number"),
			/*array("col"=>35, "name"=>"id_padre", "type"=>"number"),*/
		)
	),
	"smk_inventario"=>array(
		"sheet"=>4,
		"row"=>3,
		"col"=>1,
		"fields"=>array(
			array("col"=>0, "name"=>"nif", "type"=>"string"),
			array("col"=>1, "name"=>"id_ubicacion", "type"=>"number"),
			array("col"=>2, "name"=>"id_galtier", "type"=>"number"),
			array("col"=>3, "name"=>"elemento", "type"=>"number"),
			array("col"=>4, "name"=>"padre", "type"=>"number"),
			array("col"=>5, "name"=>"id_cuenta", "type"=>"number"),
			array("col"=>7, "name"=>"identificacion", "type"=>"string"),
			array("col"=>8, "name"=>"tipo_identif", "type"=>"string"),
			array("col"=>9, "name"=>"descripcion", "type"=>"string"),
			array("col"=>10, "name"=>"anno", "type"=>"number"),
			array("col"=>11, "name"=>"area", "type"=>"string"),
			array("col"=>12, "name"=>"contabilizacion", "type"=>"string"),
		)
	),
);

if($_REQUEST['purgar']=="1"){
	$q0=mysql_query("DROP INDEX denominacion ON smk_denominaciones");
	$q1=mysql_query("ALTER IGNORE TABLE smk_denominaciones ADD UNIQUE INDEX idx_denominacion (denominacion);");
	$tablas=array("smk_conciliacion", "smk_inventario", "smk_valores", "smk_adjuntos", "smk_cuentas", "smk_denominaciones", "smk_identificadores", "smk_preferencias", "smk_ubicaciones");
	foreach($tablas as $tabla){
		$sql="TRUNCATE $tabla";
		$q=mysql_query($sql) or die("------".mysql_error());
	}
	$files = glob('../../assets/activos/*');
	foreach($files as $file){
		if(is_file($file))
		unlink($file);
	}
}
$excel=new ExcelImporter();
$excel->debugMode=FALSE;
$excel->insertMode=$_REQUEST['coincidencia']=="IGNORE" ? "IGNORE" : "REPLACE";
$excel->obj=new Spreadsheet_Excel_Reader("../../assets/imports/conciliacion.xls", false, "CP1251");
$excel->model=$mapping;
$q=mysql_query("DELETE FROM smk_inventario");
$excel->import();

$sql="ALTER IGNORE TABLE smk_denominaciones ADD UNIQUE (denominacion)";

$sql="UPDATE smk_conciliacion SET 
	descripcion=IF(descripcion IS NULL OR descripcion='', descripcion_aux, descripcion),
	val_adq=IF(val_adq IS NULL, val_adq_aux, val_adq),
	val_adq_aux=0,
	porcent_anual=0,
	amort_inicial=0,
	val_cont=IF(val_cont IS NULL, val_cont_aux, val_cont),
	id_cuenta=IF(id_cuenta IS NULL, id_cuenta_aux, id_cuenta),
	orden=IF(orden IS NULL, 0, orden),
	val_resid=IF(val_resid IS NULL, 0, val_resid),
	annos_amort=IF(annos_amort IS NULL, 10, annos_amort),
	tipo_amort=IF(tipo_amort IS NULL, 1, tipo_amort),
	-- campos calculados
	amort_acum=val_adq - val_cont,
	-- asignación de valores fijos
	moneda='€', descripcion_aux=NULL, id_cuenta_aux=NULL, val_adq_aux=NULL, val_cont_aux=NULL";
$q=mysql_query($sql);
$sql="UPDATE smk_conciliacion c, smk_conciliacion a 
	SET c.id_padre=a.id
	WHERE c.grupo=a.grupo AND c.af_id_activo IS NULL AND a.af_id_activo IS NOT NULL";
$q=mysql_query($sql);
$sql="UPDATE smk_conciliacion SET id_padre=0 WHERE id_padre IS NULL";
$q=mysql_query($sql);


$sql="UPDATE smk_conciliacion c, smk_inventario i SET 
	c.anno_pem=i.anno,
	c.contabilizacion=i.contabilizacion,
	c.area=i.area,
	c.id_ubic=i.id_ubicacion,
	c.id_padre_aux=i.padre,
	c.identif_aux=i.tipo_identif
WHERE 
	i.id_galtier=c.id_galtier 
	and i.elemento is NULL";
$q=mysql_query($sql);
$sql="delete from smk_inventario where 
	elemento is null 
	and id_galtier in(
		select id_galtier from smk_conciliacion c WHERE id_galtier=c.id_galtier 
	)";
$q=mysql_query($sql);

if(!isset($_REQUEST['mantener']) || $_REQUEST['mantener']!='1'){
	@unlink($uploadfile);
}


$sql="TRUNCATE smk_valores";
$q=mysql_query($sql);
$sql="TRUNCATE smk_valores_anual";
$q=mysql_query($sql);

include("../sanmarko/Amortizacion.class.php");
$activos=new Amortizacion();
$sql="SELECT * FROM smk_conciliacion c WHERE af_num_activo IS NULL OR (SELECT COUNT(id) FROM smk_conciliacion WHERE id_padre=c.id)=0";
$q=mysql_query($sql);
while($f=mysql_fetch_assoc($q)){
	$activos->recordset=$f;
	$activos->initHistorial();
}
?>