/** * @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); }