public function ImportarRegistro($Row)
{
$resultado = new ResultadoLote();
//$resultado->Registros[] = $Row;
$Seccion = strtoupper(trim($Row['SECCION'], ' .'));
$MacizoNum = trim($Row['MACIZO_NUM'], ' .');
$MacizoAlfa = trim($Row['MACIZO_ALFA'], ' .');
$ParcelaNum = trim($Row['PARCELA_NUM'], ' .');
$ParcelaAlfa = trim($Row['PARCELA_ALFA'], ' .');
$SubparcelaNum = trim($Row['SUBPARC_NUM'], ' .');
$SubparcelaAlfa = trim($Row['SUBPARC_ALFA'], ' .');
$UnidadFuncional = (int) $Row['UNID_FUNC'];
$entity = $this->em->getRepository('YacareCatastroBundle:Partida')->findOneBy(array('ImportSrc' => 'dbmunirg.TR3A100', 'ImportId' => $Row['TR3A100_ID']));
if (!$entity) {
$entity = $this->em->getRepository('YacareCatastroBundle:Partida')->findOneBy(array('Seccion' => $Seccion, 'MacizoNum' => $MacizoNum, 'MacizoAlfa' => $MacizoAlfa, 'ParcelaNum' => $ParcelaNum, 'ParcelaAlfa' => $ParcelaAlfa, 'SubparcelaNum' => $SubparcelaNum, 'SubparcelaAlfa' => $SubparcelaAlfa, 'UnidadFuncional' => $UnidadFuncional));
}
if (!$entity) {
$entity = $this->em->getRepository('YacareCatastroBundle:Partida')->findOneBy(array('Numero' => (int) $Row['PARTIDA']));
}
if (!$entity) {
$entity = new \Yacare\CatastroBundle\Entity\Partida();
$entity->setSeccion($Seccion);
$entity->setMacizoAlfa($MacizoAlfa);
$entity->setMacizoNum($MacizoNum);
$entity->setParcelaAlfa($ParcelaAlfa);
$entity->setParcelaNum($ParcelaNum);
$entity->setSubparcelaAlfa($SubparcelaAlfa);
$entity->setSubparcelaNum($SubparcelaNum);
$entity->setUnidadFuncional($UnidadFuncional);
$resultado->RegistrosNuevos++;
} else {
$resultado->RegistrosActualizados++;
}
$CodigoCalle = $this->ArreglarCodigoCalle($Row['CODIGO_CALLE']);
if ($CodigoCalle) {
$entity->setDomicilioCalle($this->em->getReference('YacareCatastroBundle:Calle', $CodigoCalle));
} else {
$entity->setDomicilioCalle(null);
}
if ($Row['ZONA_CODIGO']) {
$ZonaId = @$this->Zonas[$Row['ZONA_CODIGO']];
if ($ZonaId) {
$entity->setZona($this->em->getReference('YacareCatastroBundle:Zona', $ZonaId));
} else {
$entity->setZona(null);
}
} else {
$entity->setZona(null);
}
$Titulares = $this->ObtenerTitularesPorId($Row['TR3A100_ID']);
if (count($Titulares) >= 1) {
$entity->setTitular($Titulares[0]);
//} else {
// $entity->setTitular(null);
}
$entity->setUnidadFuncional($UnidadFuncional);
$entity->setDomicilioNumero((int) $Row['NUMERO']);
$entity->setDomicilioPiso(trim($Row['PISO']));
$entity->setDomicilioPuerta(trim($Row['DEPTO']));
$entity->setNumero((int) $Row['PARTIDA']);
// Guardo los TG06100_ID de los titulares de las partidas, para debug
$Tg06100Id = trim($Row['TG06100_TG06100_ID']);
$Tg06100IdActual = $entity->getTg06100Id();
if ($Tg06100IdActual) {
if (strpos($Tg06100IdActual, $Tg06100Id) === false) {
$entity->setTg06100Id($Tg06100IdActual . ',' . $Tg06100Id);
}
} else {
$entity->setTg06100Id($Tg06100Id);
}
$entity->setImportSrc('dbmunirg.TR3A100');
$entity->setImportId($Row['TR3A100_ID']);
$entity->setSuprimido(0);
$this->em->persist($entity);
$this->em->flush();
return $resultado;
}
/**
* @Route("partidas/")
* @Template("YacareMunirgBundle:Importar:importar.html.twig")
*/
public function importarPartidasAction(Request $request)
{
// DELETE FROM Catastro_Partida WHERE id NOT IN (SELECT DISTINCT Partida_id FROM
// Inspeccion_RelevamientoAsignacionDetalle);
$desde = (int) $request->query->get('desde');
$cant = 500;
mb_internal_encoding('UTF-8');
ini_set('display_errors', 1);
set_time_limit(600);
ini_set('memory_limit', '2048M');
$Zonas = array('ZC' => 2, 'ZCB' => 7, 'ZCM' => 4, 'ZCP' => 5, 'CRT1' => 3, 'ZCRT1' => 3, 'ZCS' => 6, 'ZMC' => 1, 'ZC-MC' => 1, 'ZPE' => 15, 'ZR1' => 8, 'ZR2' => 9, 'ZR3' => 10, 'ZR4' => 11, 'ZR5' => 12, 'ZR6' => 13, 'ZREU' => 16, 'ZRM' => 14, 'ZSEU' => 18, 'Z extra urb. zona costera' => 19, 'Z residencial extraurbano 2' => 17, 'ZEIA' => null, 'ZEIS' => null, 'ZEIU' => null);
$Dbmunirg = $this->ConectarOracle();
$em = $this->getDoctrine()->getManager();
$em->getConnection()->beginTransaction();
$importar_importados = 0;
$importar_actualizados = 0;
$importar_procesados = 0;
$log = array();
$sql = "\n SELECT * FROM (\n SELECT a.*, ROWNUM rnum FROM (\n SELECT tr3a100.tr3a100_id,\n tr3a100.catastro_id,\n tr3a100.nomenclatura_cat,\n tr3a100.estado,\n tr3a100.categoria,\n tr3a100.zona_codigo,\n tr3a100\$rgr.chacra,\n tr3a100\$rgr.seccion,\n tr3a100\$rgr.macizo_num,\n tr3a100\$rgr.macizo_alfa,\n tr3a100\$rgr.parcela_num,\n tr3a100\$rgr.parcela_alfa,\n tr3a100\$rgr.subparc_num,\n tr3a100\$rgr.subparc_alfa,\n tr3a100\$rgr.unid_func,\n tr3a100\$rgr.legajo,\n TG06300.CODIGO_CALLE,\n TG06300.CALLE,\n TG06300.NUMERO,\n TG06300.PISO,\n TG06300.DEPARTAMENTO,\n TR3A100.ZONA_CURB,\n tr02100.TIT_TG06100_ID,\n j.IDENTIFICACION_TRIBUTARIA,\n \t\t doc.DOCUMENTO_NRO\n FROM tr3a100\n JOIN tr3a100\$rgr ON (tr3a100.tr3a100_id = tr3a100\$rgr.tr3a100_tr3a100_id)\n JOIN TG06300 ON (TG06300.TG06300_ID = tr3a100.TG06300_TG06300_ID)\n LEFT JOIN tr02100 ON (tr02100.tr02100_id = tr3a100.tr02100_tr02100_id)\n \t\t LEFT JOIN TG06110 p ON tr02100.TIT_TG06100_ID = p.TG06100_TG06100_ID\n \t\t LEFT JOIN TG06120 j ON tr02100.TIT_TG06100_ID = j.TG06100_TG06100_ID\n \t\t LEFT JOIN TG06111 doc ON tr02100.TIT_TG06100_ID = doc.TG06110_TG06100_TG06100_ID\n WHERE tr3a100.estado='AL'\n AND tr3a100.lugar='RGR'\n AND tr02100.DEFINITIVO='D'\n AND tr02100.IMPONIBLE_TIPO='INM'\n \n ORDER BY tr3a100.catastro_id\n ) a \n WHERE ROWNUM <=" . ($desde + $cant) . ")\n WHERE rnum >" . $desde . "\n ";
foreach ($Dbmunirg->query($sql) as $Row) {
$Seccion = strtoupper(trim($Row['SECCION'], ' .'));
$MacizoNum = trim($Row['MACIZO_NUM'], ' .');
$MacizoAlfa = trim($Row['MACIZO_ALFA'], ' .');
$ParcelaNum = trim($Row['PARCELA_NUM'], ' .');
$ParcelaAlfa = trim($Row['PARCELA_ALFA'], ' .');
$Macizo = trim($MacizoNum . $MacizoAlfa);
$Parcela = trim($ParcelaNum . $ParcelaAlfa);
$UnidadFuncional = (int) $Row['UNID_FUNC'];
$entity = null;
/*
* $entity = $em->getRepository('YacareCatastroBundle:Partida')->findOneBy(array( 'ImportSrc' =>
* 'dbmunirg.TR3A100', 'ImportId' => $Row['TR3A100_ID'] ));
*/
if (!$entity) {
$entity = $em->getRepository('YacareCatastroBundle:Partida')->findOneBy(array('Seccion' => $Seccion, 'Macizo' => $Macizo, 'Parcela' => $Parcela, 'UnidadFuncional' => $UnidadFuncional));
}
if (!$entity) {
$entity = $em->getRepository('YacareCatastroBundle:Partida')->findOneBy(array('Numero' => (int) $Row['CATASTRO_ID']));
}
if (!$entity) {
$entity = new \Yacare\CatastroBundle\Entity\Partida();
$entity->setSeccion($Seccion);
$entity->setMacizoAlfa($MacizoAlfa);
$entity->setMacizoNum($MacizoNum);
$entity->setMacizo($Macizo);
$entity->setParcelaAlfa($ParcelaAlfa);
$entity->setParcelaNum($ParcelaNum);
$entity->setParcela($Parcela);
$importar_importados++;
} else {
$importar_actualizados++;
}
$CodigoCalle = $this->ArreglarCodigoCalle($Row['CODIGO_CALLE']);
if ($entity && $Seccion) {
if ($CodigoCalle) {
$entity->setDomicilioCalle($em->getReference('YacareCatastroBundle:Calle', $CodigoCalle));
} else {
$entity->setDomicilioCalle(null);
}
if ($Row['ZONA_CURB']) {
$ZonaId = @$Zonas[$Row['ZONA_CURB']];
if ($ZonaId) {
$entity->setZona($em->getReference('YacareCatastroBundle:Zona', $ZonaId));
} else {
$entity->setZona(null);
}
} else {
$entity->setZona(null);
}
$Row['DOCUMENTO_NRO'] = str_replace(array(' ', '-', '.'), '', $Row['DOCUMENTO_NRO']);
$Row['IDENTIFICACION_TRIBUTARIA'] = str_replace(array(' ', '-', '.'), '', $Row['IDENTIFICACION_TRIBUTARIA']);
if ($Row['TIT_TG06100_ID'] || $Row['DOCUMENTO_NRO'] || $Row['IDENTIFICACION_TRIBUTARIA']) {
$titular = $em->getRepository('YacareBaseBundle:Persona')->findOneBy(array('Tg06100Id' => $Row['TIT_TG06100_ID']));
if (!$titular && $Row['DOCUMENTO_NRO']) {
$titular = $em->getRepository('YacareBaseBundle:Persona')->findOneBy(array('DocumentoNumero' => $Row['DOCUMENTO_NRO']));
if (!$titular) {
$titular = $em->getRepository('YacareBaseBundle:Persona')->findOneBy(array('Cuilt' => $Row['DOCUMENTO_NRO']));
}
}
if (!$titular && $Row['IDENTIFICACION_TRIBUTARIA']) {
$titular = $em->getRepository('YacareBaseBundle:Persona')->findOneBy(array('Cuilt' => $Row['IDENTIFICACION_TRIBUTARIA']));
if (!$titular) {
$titular = $em->getRepository('YacareBaseBundle:Persona')->findOneBy(array('Cuilt' => $Row['IDENTIFICACION_TRIBUTARIA']));
}
}
$entity->setTitular($titular);
if ($titular) {
$log[] = "titular encontrado " . $Row['TIT_TG06100_ID'] . ': ' . $titular;
} else {
$log[] = "titular NO encontrado " . $Row['TIT_TG06100_ID'] . ', doc ' . $Row['DOCUMENTO_NRO'] . ', it ' . $Row['IDENTIFICACION_TRIBUTARIA'];
}
} else {
$log[] = "*** Sin titular " . $Row['TIT_TG06100_ID'];
$entity->setTitular(null);
}
$entity->setUnidadFuncional($UnidadFuncional, $CodigoCalle);
$entity->setDomicilioNumero((int) $Row['NUMERO']);
$entity->setDomicilioPiso(trim($Row['PISO']));
$entity->setDomicilioPuerta(trim($Row['DEPARTAMENTO']));
$entity->setLegajo((int) $Row['LEGAJO']);
$entity->setNumero((int) $Row['CATASTRO_ID']);
// $entity->setImportSrc('dbmunirg.TR3A100');
// $entity->setImportId($Row['TR3A100_ID']);
$em->persist($entity);
$em->flush();
// $log[] = $Row['CATASTRO_ID'] . " SMP($Seccion-$Macizo-$Parcela-$UnidadFuncional) ${Row['CALLE']}
// #${Row['NUMERO']} --- " . $entity->getTitular();
}
$importar_procesados++;
}
$em->getConnection()->commit();
return array('importar_importados' => $importar_importados, 'importar_actualizados' => $importar_actualizados, 'importar_procesados' => $importar_procesados, 'redir_desde' => $importar_procesados == $cant ? $desde + $cant : 0, 'log' => $log);
}
