Base_de_datos.sql

/*
SQLyog Ultimate v12.5.0 (64 bit)
MySQL - 5.7.12-log : Database - inventario
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`inventario` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `inventario`;

/*Table structure for table `activo_estado` */

DROP TABLE IF EXISTS `activo_estado`;

CREATE TABLE `activo_estado` (
  `id_estado` int(11) NOT NULL AUTO_INCREMENT,
  `desc_estado` varchar(128) NOT NULL,
  PRIMARY KEY (`id_estado`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `activo_estado` */

LOCK TABLES `activo_estado` WRITE;

insert  into `activo_estado`(`id_estado`,`desc_estado`) values 
(1,'ACTIVO'),
(2,'DADO DE BAJA'),
(3,'EN REPARACIÓN'),
(4,'DISPONIBLE'),
(5,'ASIGNADO');

UNLOCK TABLES;

/*Table structure for table `activo_tipo` */

DROP TABLE IF EXISTS `activo_tipo`;

CREATE TABLE `activo_tipo` (
  `id_tipo` int(11) NOT NULL AUTO_INCREMENT,
  `desc_tipo` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id_tipo`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

/*Data for the table `activo_tipo` */

LOCK TABLES `activo_tipo` WRITE;

insert  into `activo_tipo`(`id_tipo`,`desc_tipo`) values 
(1,'TERRENOS Y BIENES NATURALES'),
(2,'CONSTRUCCIONES'),
(3,'INSTALACIONES TÉCNICAS'),
(4,'MAQUINARIA'),
(5,'MOBILIARIO'),
(6,'EQUIPOS PARA PROCESOS INFORMÁTICOS'),
(7,'ELEMENTOS DE TRANSPORTE'),
(8,'OTROS');

UNLOCK TABLES;

/*Table structure for table `activos` */

DROP TABLE IF EXISTS `activos`;

CREATE TABLE `activos` (
  `id_act` varchar(36) NOT NULL COMMENT 'Identificador en base de datos del activo',
  `nom_act` varchar(256) NOT NULL COMMENT 'Nombre del activo',
  `desc_act` text COMMENT 'Descripcion del activo',
  `tipo_id` int(11) DEFAULT NULL COMMENT 'Tipo de activo',
  `srl_act` varchar(64) DEFAULT NULL COMMENT 'Serial activo',
  `num_inv_int_act` int(11) DEFAULT '1' COMMENT 'Numero interno de inventario',
  `peso_act` decimal(10,2) DEFAULT NULL COMMENT 'Peso en Kilogramos',
  `alto_act` decimal(10,2) DEFAULT NULL COMMENT 'Altura en Metros',
  `ancho_act` decimal(10,2) DEFAULT NULL COMMENT 'Ancho en Metros',
  `largo_act` decimal(10,2) DEFAULT NULL COMMENT 'Largo en Metros',
  `val_comp_act` decimal(10,2) DEFAULT NULL COMMENT 'Valor de compra del activo',
  `fecha_comp_act` date DEFAULT NULL COMMENT 'Fecha de compra del activo',
  `estado_id` int(11) DEFAULT NULL COMMENT 'Estado actual del activo',
  `fecha_baja_act` date DEFAULT NULL COMMENT 'Fecha en que se dio de baja',
  `color_act` varchar(64) DEFAULT NULL COMMENT 'Color del activo',
  `ultima_mod` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha y hora en que se actualizo el registro por ultima vez',
  PRIMARY KEY (`id_act`),
  KEY `ESTADO_ID` (`estado_id`),
  KEY `TIPO_ID` (`tipo_id`),
  CONSTRAINT `activos_ibfk_1` FOREIGN KEY (`estado_id`) REFERENCES `activo_estado` (`id_estado`),
  CONSTRAINT `activos_ibfk_2` FOREIGN KEY (`tipo_id`) REFERENCES `activo_tipo` (`id_tipo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `activos` */

LOCK TABLES `activos` WRITE;

insert  into `activos`(`id_act`,`nom_act`,`desc_act`,`tipo_id`,`srl_act`,`num_inv_int_act`,`peso_act`,`alto_act`,`ancho_act`,`largo_act`,`val_comp_act`,`fecha_comp_act`,`estado_id`,`fecha_baja_act`,`color_act`,`ultima_mod`) values 
('38ed6880-cf38-4c2f-804f-3084e928d705','COMPUTADOR ALL IN ONE','',6,'18920',1,0.00,0.00,0.00,0.00,0.00,'2018-06-18',5,NULL,'','2018-06-28 10:48:59'),
('caf7aa80-5b01-428d-87d9-6726084a7ef4','AIRE ACONDICIONADO','LENNOX',3,'D202308260915102150052',1,32.50,0.00,0.00,0.00,0.00,'2018-06-04',5,'2018-06-29','','2018-06-28 10:51:05');

UNLOCK TABLES;

/*Table structure for table `areas` */

DROP TABLE IF EXISTS `areas`;

CREATE TABLE `areas` (
  `id_area` varchar(36) NOT NULL COMMENT 'Identificador unico del area en base de datos',
  `emp_id` varchar(36) DEFAULT NULL COMMENT 'Identificador de la empresa a la que pertenece',
  `mun_id` int(11) NOT NULL COMMENT 'Identificador del municipio donde se encuentra el area',
  `nom_area` varchar(128) DEFAULT NULL COMMENT 'Nombre del area',
  `cod_area` varchar(16) DEFAULT NULL COMMENT 'Codigo o siglas',
  PRIMARY KEY (`id_area`),
  KEY `EMP_ID` (`emp_id`),
  KEY `MUN_ID` (`mun_id`),
  CONSTRAINT `areas_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `empresa` (`id_emp`),
  CONSTRAINT `areas_ibfk_2` FOREIGN KEY (`mun_id`) REFERENCES `aux_mpio` (`id_mpio`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `areas` */

LOCK TABLES `areas` WRITE;

insert  into `areas`(`id_area`,`emp_id`,`mun_id`,`nom_area`,`cod_area`) values 
('6a1eada4-495c-4651-9b03-782339baa7c0','e1774c92-78a0-11e8-aaee-00fff8a19e36',5711001,'SALA DE PRENSA','SP'),
('e4c2f8f1-7aea-11e8-894b-00fff8a19e36','e1774c92-78a0-11e8-aaee-00fff8a19e36',5705093,'CENTRO DE DATOS','CPD');

UNLOCK TABLES;

/*Table structure for table `asignaciones` */

DROP TABLE IF EXISTS `asignaciones`;

CREATE TABLE `asignaciones` (
  `id_asignacion` varchar(36) NOT NULL COMMENT 'Identificador unico en base de datos de la asignacion',
  `activo_id` varchar(36) DEFAULT NULL COMMENT 'Id del activo asignadio',
  `area_id` varchar(36) DEFAULT NULL COMMENT 'Id del area si es que fue asignado a un area',
  `persona_id` varchar(36) DEFAULT NULL COMMENT 'Id de persona si es que fue asignado a una persona',
  `fecha_asignacion` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha en que se realizó la asignacion',
  PRIMARY KEY (`id_asignacion`),
  KEY `ACTIVO_ID` (`activo_id`),
  KEY `AREA_ID` (`area_id`),
  KEY `PERSONA_ID` (`persona_id`),
  CONSTRAINT `asignaciones_ibfk_1` FOREIGN KEY (`activo_id`) REFERENCES `activos` (`id_act`),
  CONSTRAINT `asignaciones_ibfk_2` FOREIGN KEY (`area_id`) REFERENCES `areas` (`id_area`),
  CONSTRAINT `asignaciones_ibfk_3` FOREIGN KEY (`persona_id`) REFERENCES `personas` (`id_per`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `asignaciones` */

LOCK TABLES `asignaciones` WRITE;

insert  into `asignaciones`(`id_asignacion`,`activo_id`,`area_id`,`persona_id`,`fecha_asignacion`) values 
('7eb165ce-1e0b-47e2-b304-51fdd6041e54','38ed6880-cf38-4c2f-804f-3084e928d705',NULL,'f73c9d94-6934-4864-9430-dcbe1a907514','2018-06-28 10:48:59'),
('bd49f869-edf6-42d1-b5c0-0559b5b2842e','caf7aa80-5b01-428d-87d9-6726084a7ef4','e4c2f8f1-7aea-11e8-894b-00fff8a19e36',NULL,'2018-06-28 10:51:05');

UNLOCK TABLES;

/*Table structure for table `aux_dpto` */

DROP TABLE IF EXISTS `aux_dpto`;

CREATE TABLE `aux_dpto` (
  `id_depto` int(11) NOT NULL AUTO_INCREMENT,
  `nom_depto` varchar(40) NOT NULL,
  PRIMARY KEY (`id_depto`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

/*Data for the table `aux_dpto` */

LOCK TABLES `aux_dpto` WRITE;

insert  into `aux_dpto`(`id_depto`,`nom_depto`) values 
(5,'Antioquia'),
(8,'Atlántico'),
(11,'Bogotá D.C'),
(13,'Bolívar'),
(15,'Boyacá'),
(17,'Caldas'),
(18,'Caquetá'),
(19,'Cauca'),
(20,'Cesar'),
(23,'Córdoba'),
(25,'Cundinamarca'),
(27,'Chocó'),
(41,'Huila'),
(44,'La Guajira'),
(47,'Magdalena'),
(50,'Meta'),
(52,'Nariño'),
(54,'Norte de Santander'),
(63,'Quindio'),
(66,'Risaralda'),
(68,'Santander'),
(70,'Sucre'),
(73,'Tolima'),
(76,'Valle del Cauca'),
(81,'Arauca'),
(85,'Casanare'),
(86,'Putumayo'),
(88,'San Andrés'),
(91,'Amazonas'),
(94,'Guainía'),
(95,'Guaviare'),
(97,'Vaupés'),
(99,'Vichada'),
(100,'Dpto No Aplica');

UNLOCK TABLES;

/*Table structure for table `aux_mpio` */

DROP TABLE IF EXISTS `aux_mpio`;

CREATE TABLE `aux_mpio` (
  `id_mpio` int(11) NOT NULL AUTO_INCREMENT,
  `nom_mpio` varchar(40) NOT NULL,
  `depto_id` int(11) NOT NULL,
  PRIMARY KEY (`id_mpio`),
  KEY `ide_aux_dpto` (`depto_id`),
  CONSTRAINT `aux_mpio_ibfk_1` FOREIGN KEY (`depto_id`) REFERENCES `aux_dpto` (`id_depto`)
) ENGINE=InnoDB AUTO_INCREMENT=5799774 DEFAULT CHARSET=utf8;

/*Data for the table `aux_mpio` */

LOCK TABLES `aux_mpio` WRITE;

insert  into `aux_mpio`(`id_mpio`,`nom_mpio`,`depto_id`) values 
(10001,'MUN NO APLICA',100),
(5705001,'Medellín',5),
(5705002,'Abejorral',5),
(5705004,'Abriaquí',5),
(5705021,'Alejandría',5),
(5705030,'Amagá',5),
(5705031,'Amalfi',5),
(5705034,'Andes',5),
(5705036,'Angelópolis',5),
(5705038,'Angostura',5),
(5705040,'Anorí',5),
(5705042,'Santafé De Antioquia',5),
(5705044,'Anza',5),
(5705045,'Apartadó',5),
(5705051,'Arboletes',5),
(5705055,'Argelia',5),
(5705059,'Armenia',5),
(5705079,'Barbosa',5),
(5705086,'Belmira',5),
(5705088,'Bello',5),
(5705091,'Betania',5),
(5705093,'Betulia',5),
(5705101,'Ciudad Bolívar',5),
(5705107,'Briceño',5),
(5705113,'Buriticá',5),
(5705120,'Cáceres',5),
(5705125,'Caicedo',5),
(5705129,'Caldas',5),
(5705134,'Campamento',5),
(5705138,'Cañasgordas',5),
(5705142,'Caracolí',5),
(5705145,'Caramanta',5),
(5705147,'Carepa',5),
(5705148,'El Carmen De Viboral',5),
(5705150,'Carolina',5),
(5705154,'Caucasia',5),
(5705172,'Chigorodó',5),
(5705190,'Cisneros',5),
(5705197,'Cocorná',5),
(5705206,'Concepción',5),
(5705209,'Concordia',5),
(5705212,'Copacabana',5),
(5705234,'Dabeiba',5),
(5705237,'Don Matías',5),
(5705240,'Ebéjico',5),
(5705250,'El Bagre',5),
(5705264,'Entrerrios',5),
(5705266,'Envigado',5),
(5705282,'Fredonia',5),
(5705284,'Frontino',5),
(5705306,'Giraldo',5),
(5705308,'Girardota',5),
(5705310,'Gómez Plata',5),
(5705313,'Granada',5),
(5705315,'Guadalupe',5),
(5705318,'Guarne',5),
(5705321,'Guatape',5),
(5705347,'Heliconia',5),
(5705353,'Hispania',5),
(5705360,'Itagui',5),
(5705361,'Ituango',5),
(5705364,'Jardín',5),
(5705368,'Jericó',5),
(5705376,'La Ceja',5),
(5705380,'La Estrella',5),
(5705390,'La Pintada',5),
(5705400,'La Unión',5),
(5705411,'Liborina',5),
(5705425,'Maceo',5),
(5705440,'Marinilla',5),
(5705467,'Montebello',5),
(5705475,'Murindó',5),
(5705480,'Mutatá',5),
(5705483,'Nariño',5),
(5705490,'Necoclí',5),
(5705495,'Nechí',5),
(5705501,'Olaya',5),
(5705541,'Peñol',5),
(5705543,'Peque',5),
(5705576,'Pueblorrico',5),
(5705579,'Puerto Berrío',5),
(5705585,'Puerto Nare',5),
(5705591,'Puerto Triunfo',5),
(5705604,'Remedios',5),
(5705607,'Retiro',5),
(5705615,'Rionegro',5),
(5705628,'Sabanalarga',5),
(5705631,'Sabaneta',5),
(5705642,'Salgar',5),
(5705647,'San Andrés De Cuerquía',5),
(5705649,'San Carlos',5),
(5705652,'San Francisco',5),
(5705656,'San Jerónimo',5),
(5705658,'San José De La Montaña',5),
(5705659,'San Juan De Urabá',5),
(5705660,'San Luis',5),
(5705664,'San Pedro',5),
(5705665,'San Pedro De Uraba',5),
(5705667,'San Rafael',5),
(5705670,'San Roque',5),
(5705674,'San Vicente',5),
(5705679,'Santa Bárbara',5),
(5705686,'Santa Rosa De Osos',5),
(5705690,'Santo Domingo',5),
(5705697,'El Santuario',5),
(5705736,'Segovia',5),
(5705756,'Sonson',5),
(5705761,'Sopetrán',5),
(5705789,'Támesis',5),
(5705790,'Tarazá',5),
(5705792,'Tarso',5),
(5705809,'Titiribí',5),
(5705819,'Toledo',5),
(5705837,'Turbo',5),
(5705842,'Uramita',5),
(5705847,'Urrao',5),
(5705854,'Valdivia',5),
(5705856,'Valparaíso',5),
(5705858,'Vegachí',5),
(5705861,'Venecia',5),
(5705873,'Vigía Del Fuerte',5),
(5705885,'Yalí',5),
(5705887,'Yarumal',5),
(5705890,'Yolombó',5),
(5705893,'Yondó',5),
(5705895,'Zaragoza',5),
(5708001,'Barranquilla',8),
(5708078,'Baranoa',8),
(5708137,'Campo De La Cruz',8),
(5708141,'Candelaria',8),
(5708296,'Galapa',8),
(5708372,'Juan De Acosta',8),
(5708421,'Luruaco',8),
(5708433,'Malambo',8),
(5708436,'Manatí',8),
(5708520,'Palmar De Varela',8),
(5708549,'Piojó',8),
(5708558,'Polonuevo',8),
(5708560,'Ponedera',8),
(5708573,'Puerto Colombia',8),
(5708606,'Repelón',8),
(5708634,'Sabanagrande',8),
(5708638,'Sabanalarga',8),
(5708675,'Santa Lucía',8),
(5708685,'Santo Tomás',8),
(5708758,'Soledad',8),
(5708770,'Suan',8),
(5708832,'Tubará',8),
(5708849,'Usiacurí',8),
(5711001,'Bogotá',11),
(5713001,'Cartagena',13),
(5713006,'Achí',13),
(5713030,'Altos Del Rosario',13),
(5713042,'Arenal',13),
(5713052,'Arjona',13),
(5713062,'Arroyohondo',13),
(5713074,'Barranco De Loba',13),
(5713140,'Calamar',13),
(5713160,'Cantagallo',13),
(5713188,'Cicuco',13),
(5713212,'Córdoba',13),
(5713222,'Clemencia',13),
(5713244,'El Carmen De Bolívar',13),
(5713248,'El Guamo',13),
(5713268,'El Peñón',13),
(5713300,'Hatillo De Loba',13),
(5713430,'Magangué',13),
(5713433,'Mahates',13),
(5713440,'Margarita',13),
(5713442,'María La Baja',13),
(5713458,'Montecristo',13),
(5713468,'Mompós',13),
(5713473,'Morales',13),
(5713549,'Pinillos',13),
(5713580,'Regidor',13),
(5713600,'Río Viejo',13),
(5713620,'San Cristóbal',13),
(5713647,'San Estanislao',13),
(5713650,'San Fernando',13),
(5713654,'San Jacinto',13),
(5713655,'San Jacinto Del Cauca',13),
(5713657,'San Juan Nepomuceno',13),
(5713667,'San Martín De Loba',13),
(5713670,'San Pablo',13),
(5713673,'Santa Catalina',13),
(5713683,'Santa Rosa',13),
(5713688,'Santa Rosa Del Sur',13),
(5713744,'Simití',13),
(5713760,'Soplaviento',13),
(5713780,'Talaigua Nuevo',13),
(5713810,'Tiquisio',13),
(5713836,'Turbaco',13),
(5713838,'Turbaná',13),
(5713873,'Villanueva',13),
(5713894,'Zambrano',13),
(5715001,'Tunja',15),
(5715022,'Almeida',15),
(5715047,'Aquitania',15),
(5715051,'Arcabuco',15),
(5715087,'Belén',15),
(5715090,'Berbeo',15),
(5715092,'Betéitiva',15),
(5715097,'Boavita',15),
(5715104,'Boyacá',15),
(5715106,'Briceño',15),
(5715109,'Buenavista',15),
(5715114,'Busbanzá',15),
(5715131,'Caldas',15),
(5715135,'Campohermoso',15),
(5715162,'Cerinza',15),
(5715172,'Chinavita',15),
(5715176,'Chiquinquirá',15),
(5715180,'Chiscas',15),
(5715183,'Chita',15),
(5715185,'Chitaraque',15),
(5715187,'Chivatá',15),
(5715189,'Ciénega',15),
(5715204,'Cómbita',15),
(5715212,'Coper',15),
(5715215,'Corrales',15),
(5715218,'Covarachía',15),
(5715223,'Cubará',15),
(5715224,'Cucaita',15),
(5715226,'Cuítiva',15),
(5715232,'Chíquiza',15),
(5715236,'Chivor',15),
(5715238,'Duitama',15),
(5715244,'El Cocuy',15),
(5715248,'El Espino',15),
(5715272,'Firavitoba',15),
(5715276,'Floresta',15),
(5715293,'Gachantivá',15),
(5715296,'Gameza',15),
(5715299,'Garagoa',15),
(5715317,'Guacamayas',15),
(5715322,'Guateque',15),
(5715325,'Guayatá',15),
(5715332,'Güicán',15),
(5715362,'Iza',15),
(5715367,'Jenesano',15),
(5715368,'Jericó',15),
(5715377,'Labranzagrande',15),
(5715380,'La Capilla',15),
(5715401,'La Victoria',15),
(5715403,'La Uvita',15),
(5715407,'Villa De Leyva',15),
(5715425,'Macanal',15),
(5715442,'Maripí',15),
(5715455,'Miraflores',15),
(5715464,'Mongua',15),
(5715466,'Monguí',15),
(5715469,'Moniquirá',15),
(5715476,'Motavita',15),
(5715480,'Muzo',15),
(5715491,'Nobsa',15),
(5715494,'Nuevo Colón',15),
(5715500,'Oicatá',15),
(5715507,'Otanche',15),
(5715511,'Pachavita',15),
(5715514,'Páez',15),
(5715516,'Paipa',15),
(5715518,'Pajarito',15),
(5715522,'Panqueba',15),
(5715531,'Pauna',15),
(5715533,'Paya',15),
(5715537,'Paz De Río',15),
(5715542,'Pesca',15),
(5715550,'Pisba',15),
(5715572,'Puerto Boyacá',15),
(5715580,'Quípama',15),
(5715599,'Ramiriquí',15),
(5715600,'Ráquira',15),
(5715621,'Rondón',15),
(5715632,'Saboyá',15),
(5715638,'Sáchica',15),
(5715646,'Samacá',15),
(5715660,'San Eduardo',15),
(5715664,'San José De Pare',15),
(5715667,'San Luis De Gaceno',15),
(5715673,'San Mateo',15),
(5715676,'San Miguel De Sema',15),
(5715681,'San Pablo De Borbur',15),
(5715686,'Santana',15),
(5715690,'Santa María',15),
(5715693,'Santa Rosa De Viterbo',15),
(5715696,'Santa Sofía',15),
(5715720,'Sativanorte',15),
(5715723,'Sativasur',15),
(5715740,'Siachoque',15),
(5715753,'Soatá',15),
(5715755,'Socotá',15),
(5715757,'Socha',15),
(5715759,'Sogamoso',15),
(5715761,'Somondoco',15),
(5715762,'Sora',15),
(5715763,'Sotaquirá',15),
(5715764,'Soracá',15),
(5715774,'Susacón',15),
(5715776,'Sutamarchán',15),
(5715778,'Sutatenza',15),
(5715790,'Tasco',15),
(5715798,'Tenza',15),
(5715804,'Tibaná',15),
(5715806,'Tibasosa',15),
(5715808,'Tinjacá',15),
(5715810,'Tipacoque',15),
(5715814,'Toca',15),
(5715816,'Togüí',15),
(5715820,'Tópaga',15),
(5715822,'Tota',15),
(5715832,'Tununguá',15),
(5715835,'Turmequé',15),
(5715837,'Tuta',15),
(5715839,'Tutazá',15),
(5715842,'Umbita',15),
(5715861,'Ventaquemada',15),
(5715879,'Viracachá',15),
(5715897,'Zetaquira',15),
(5717001,'Manizales',17),
(5717013,'Aguadas',17),
(5717042,'Anserma',17),
(5717050,'Aranzazu',17),
(5717088,'Belalcázar',17),
(5717174,'Chinchiná',17),
(5717272,'Filadelfia',17),
(5717380,'La Dorada',17),
(5717388,'La Merced',17),
(5717433,'Manzanares',17),
(5717442,'Marmato',17),
(5717444,'Marquetalia',17),
(5717446,'Marulanda',17),
(5717486,'Neira',17),
(5717495,'Norcasia',17),
(5717513,'Pácora',17),
(5717524,'Palestina',17),
(5717541,'Pensilvania',17),
(5717614,'Riosucio',17),
(5717616,'Risaralda',17),
(5717653,'Salamina',17),
(5717662,'Samaná',17),
(5717665,'San José',17),
(5717777,'Supía',17),
(5717867,'Victoria',17),
(5717873,'Villamaría',17),
(5717877,'Viterbo',17),
(5718001,'Florencia',18),
(5718029,'Albania',18),
(5718094,'Belén De Los Andaquies',18),
(5718150,'Cartagena Del Chairá',18),
(5718205,'Curillo',18),
(5718247,'El Doncello',18),
(5718256,'El Paujil',18),
(5718410,'La Montañita',18),
(5718460,'Milán',18),
(5718479,'Morelia',18),
(5718592,'Puerto Rico',18),
(5718610,'San José Del Fragua',18),
(5718753,'San Vicente Del Caguán',18),
(5718756,'Solano',18),
(5718785,'Solita',18),
(5718860,'Valparaíso',18),
(5719001,'Popayán',19),
(5719022,'Almaguer',19),
(5719050,'Argelia',19),
(5719075,'Balboa',19),
(5719100,'Bolívar',19),
(5719110,'Buenos Aires',19),
(5719130,'Cajibío',19),
(5719137,'Caldono',19),
(5719142,'Caloto',19),
(5719212,'Corinto',19),
(5719256,'El Tambo',19),
(5719290,'Florencia',19),
(5719318,'Guapi',19),
(5719355,'Inzá',19),
(5719364,'Jambaló',19),
(5719392,'La Sierra',19),
(5719397,'La Vega',19),
(5719418,'López',19),
(5719450,'Mercaderes',19),
(5719455,'Miranda',19),
(5719473,'Morales',19),
(5719513,'Padilla',19),
(5719517,'Paez',19),
(5719532,'Patía',19),
(5719533,'Piamonte',19),
(5719548,'Piendamó',19),
(5719573,'Puerto Tejada',19),
(5719585,'Puracé',19),
(5719622,'Rosas',19),
(5719693,'San Sebastián',19),
(5719698,'Santander De Quilichao',19),
(5719701,'Santa Rosa',19),
(5719743,'Silvia',19),
(5719760,'Sotara',19),
(5719780,'Suárez',19),
(5719785,'Sucre',19),
(5719807,'Timbío',19),
(5719809,'Timbiquí',19),
(5719821,'Toribio',19),
(5719824,'Totoró',19),
(5719845,'Villa Rica',19),
(5720001,'Valledupar',20),
(5720011,'Aguachica',20),
(5720013,'Agustín Codazzi',20),
(5720032,'Astrea',20),
(5720045,'Becerril',20),
(5720060,'Bosconia',20),
(5720175,'Chimichagua',20),
(5720178,'Chiriguaná',20),
(5720228,'Curumaní',20),
(5720238,'El Copey',20),
(5720250,'El Paso',20),
(5720295,'Gamarra',20),
(5720310,'González',20),
(5720383,'La Gloria',20),
(5720400,'La Jagua De Ibirico',20),
(5720443,'Manaure',20),
(5720517,'Pailitas',20),
(5720550,'Pelaya',20),
(5720570,'Pueblo Bello',20),
(5720614,'Río De Oro',20),
(5720621,'La Paz',20),
(5720710,'San Alberto',20),
(5720750,'San Diego',20),
(5720770,'San Martín',20),
(5720787,'Tamalameque',20),
(5723001,'Montería',23),
(5723068,'Ayapel',23),
(5723079,'Buenavista',23),
(5723090,'Canalete',23),
(5723162,'Cereté',23),
(5723168,'Chimá',23),
(5723182,'Chinú',23),
(5723189,'Ciénaga De Oro',23),
(5723300,'Cotorra',23),
(5723350,'La Apartada',23),
(5723417,'Lorica',23),
(5723419,'Los Córdobas',23),
(5723464,'Momil',23),
(5723466,'Montelíbano',23),
(5723500,'Moñitos',23),
(5723555,'Planeta Rica',23),
(5723570,'Pueblo Nuevo',23),
(5723574,'Puerto Escondido',23),
(5723580,'Puerto Libertador',23),
(5723586,'Purísima',23),
(5723660,'Sahagún',23),
(5723670,'San Andrés Sotavento',23),
(5723672,'San Antero',23),
(5723675,'San Bernardo Del Viento',23),
(5723678,'San Carlos',23),
(5723686,'San Pelayo',23),
(5723807,'Tierralta',23),
(5723855,'Valencia',23),
(5725001,'Agua De Dios',25),
(5725019,'Albán',25),
(5725035,'Anapoima',25),
(5725040,'Anolaima',25),
(5725053,'Arbeláez',25),
(5725086,'Beltrán',25),
(5725095,'Bituima',25),
(5725099,'Bojacá',25),
(5725120,'Cabrera',25),
(5725123,'Cachipay',25),
(5725126,'Cajicá',25),
(5725148,'Caparrapí',25),
(5725151,'Caqueza',25),
(5725154,'Carmen De Carupa',25),
(5725168,'Chaguaní',25),
(5725175,'Chía',25),
(5725178,'Chipaque',25),
(5725181,'Choachí',25),
(5725183,'Chocontá',25),
(5725200,'Cogua',25),
(5725214,'Cota',25),
(5725224,'Cucunubá',25),
(5725245,'El Colegio',25),
(5725258,'El Peñón',25),
(5725260,'El Rosal',25),
(5725269,'Facatativá',25),
(5725279,'Fomeque',25),
(5725281,'Fosca',25),
(5725286,'Funza',25),
(5725288,'Fúquene',25),
(5725290,'Fusagasugá',25),
(5725293,'Gachala',25),
(5725295,'Gachancipá',25),
(5725297,'Gachetá',25),
(5725299,'Gama',25),
(5725307,'Girardot',25),
(5725312,'Granada',25),
(5725317,'Guachetá',25),
(5725320,'Guaduas',25),
(5725322,'Guasca',25),
(5725324,'Guataquí',25),
(5725326,'Guatavita',25),
(5725328,'Guayabal De Siquima',25),
(5725335,'Guayabetal',25),
(5725339,'Gutiérrez',25),
(5725368,'Jerusalén',25),
(5725372,'Junín',25),
(5725377,'La Calera',25),
(5725386,'La Mesa',25),
(5725394,'La Palma',25),
(5725398,'La Peña',25),
(5725402,'La Vega',25),
(5725407,'Lenguazaque',25),
(5725426,'Macheta',25),
(5725430,'Madrid',25),
(5725436,'Manta',25),
(5725438,'Medina',25),
(5725473,'Mosquera',25),
(5725483,'Nariño',25),
(5725486,'Nemocón',25),
(5725488,'Nilo',25),
(5725489,'Nimaima',25),
(5725491,'Nocaima',25),
(5725506,'Venecia',25),
(5725513,'Pacho',25),
(5725518,'Paime',25),
(5725524,'Pandi',25),
(5725530,'Paratebueno',25),
(5725535,'Pasca',25),
(5725572,'Puerto Salgar',25),
(5725580,'Pulí',25),
(5725592,'Quebradanegra',25),
(5725594,'Quetame',25),
(5725596,'Quipile',25),
(5725599,'Apulo',25),
(5725612,'Ricaurte',25),
(5725645,'San Antonio Del Tequendama',25),
(5725649,'San Bernardo',25),
(5725653,'San Cayetano',25),
(5725658,'San Francisco',25),
(5725662,'San Juan De Río Seco',25),
(5725718,'Sasaima',25),
(5725736,'Sesquilé',25),
(5725740,'Sibaté',25),
(5725743,'Silvania',25),
(5725745,'Simijaca',25),
(5725754,'Soacha',25),
(5725758,'Sopó',25),
(5725769,'Subachoque',25),
(5725772,'Suesca',25),
(5725777,'Supatá',25),
(5725779,'Susa',25),
(5725781,'Sutatausa',25),
(5725785,'Tabio',25),
(5725793,'Tausa',25),
(5725797,'Tena',25),
(5725799,'Tenjo',25),
(5725805,'Tibacuy',25),
(5725807,'Tibirita',25),
(5725815,'Tocaima',25),
(5725817,'Tocancipá',25),
(5725823,'Topaipí',25),
(5725839,'Ubalá',25),
(5725841,'Ubaque',25),
(5725843,'Villa De San Diego De Ubate',25),
(5725845,'Une',25),
(5725851,'Útica',25),
(5725862,'Vergara',25),
(5725867,'Vianí',25),
(5725871,'Villagómez',25),
(5725873,'Villapinzón',25),
(5725875,'Villeta',25),
(5725878,'Viotá',25),
(5725885,'Yacopí',25),
(5725898,'Zipacón',25),
(5725899,'Zipaquirá',25),
(5727001,'Quibdó',27),
(5727006,'Acandí',27),
(5727025,'Alto Baudo',27),
(5727050,'Atrato',27),
(5727073,'Bagadó',27),
(5727075,'Bahía Solano',27),
(5727077,'Bajo Baudó',27),
(5727086,'Belén De Bajirá',27),
(5727099,'Bojaya',27),
(5727135,'El Cantón Del San Pablo',27),
(5727150,'Carmen Del Darien',27),
(5727160,'Cértegui',27),
(5727205,'Condoto',27),
(5727245,'El Carmen De Atrato',27),
(5727250,'El Litoral Del San Juan',27),
(5727361,'Istmina',27),
(5727372,'Juradó',27),
(5727413,'Lloró',27),
(5727425,'Medio Atrato',27),
(5727430,'Medio Baudó',27),
(5727450,'Medio San Juan',27),
(5727491,'Nóvita',27),
(5727495,'Nuquí',27),
(5727580,'Río Iro',27),
(5727600,'Río Quito',27),
(5727615,'Riosucio',27),
(5727660,'San José Del Palmar',27),
(5727745,'Sipí',27),
(5727787,'Tadó',27),
(5727800,'Unguía',27),
(5727810,'Unión Panamericana',27),
(5741001,'Neiva',41),
(5741006,'Acevedo',41),
(5741013,'Agrado',41),
(5741016,'Aipe',41),
(5741020,'Algeciras',41),
(5741026,'Altamira',41),
(5741078,'Baraya',41),
(5741132,'Campoalegre',41),
(5741206,'Colombia',41),
(5741244,'Elías',41),
(5741298,'Garzón',41),
(5741306,'Gigante',41),
(5741319,'Guadalupe',41),
(5741349,'Hobo',41),
(5741357,'Iquira',41),
(5741359,'Isnos',41),
(5741378,'La Argentina',41),
(5741396,'La Plata',41),
(5741483,'Nátaga',41),
(5741503,'Oporapa',41),
(5741518,'Paicol',41),
(5741524,'Palermo',41),
(5741530,'Palestina',41),
(5741548,'Pital',41),
(5741551,'Pitalito',41),
(5741615,'Rivera',41),
(5741660,'Saladoblanco',41),
(5741668,'San Agustín',41),
(5741676,'Santa María',41),
(5741770,'Suaza',41),
(5741791,'Tarqui',41),
(5741797,'Tesalia',41),
(5741799,'Tello',41),
(5741801,'Teruel',41),
(5741807,'Timaná',41),
(5741872,'Villavieja',41),
(5741885,'Yaguará',41),
(5744001,'Riohacha',44),
(5744035,'Albania',44),
(5744078,'Barrancas',44),
(5744090,'Dibulla',44),
(5744098,'Distracción',44),
(5744110,'El Molino',44),
(5744279,'Fonseca',44),
(5744378,'Hatonuevo',44),
(5744420,'La Jagua Del Pilar',44),
(5744430,'Maicao',44),
(5744560,'Manaure',44),
(5744650,'San Juan Del Cesar',44),
(5744847,'Uribia',44),
(5744855,'Urumita',44),
(5744874,'Villanueva',44),
(5747001,'Santa Marta',47),
(5747030,'Algarrobo',47),
(5747053,'Aracataca',47),
(5747058,'Ariguaní',47),
(5747161,'Cerro San Antonio',47),
(5747170,'Chibolo',47),
(5747189,'Ciénaga',47),
(5747205,'Concordia',47),
(5747245,'El Banco',47),
(5747258,'El Piñon',47),
(5747268,'El Retén',47),
(5747288,'Fundación',47),
(5747318,'Guamal',47),
(5747460,'Nueva Granada',47),
(5747541,'Pedraza',47),
(5747545,'Pijiño Del Carmen',47),
(5747551,'Pivijay',47),
(5747555,'Plato',47),
(5747570,'Puebloviejo',47),
(5747605,'Remolino',47),
(5747660,'Sabanas De San Angel',47),
(5747675,'Salamina',47),
(5747692,'San Sebastián De Buenavista',47),
(5747703,'San Zenón',47),
(5747707,'Santa Ana',47),
(5747720,'Santa Bárbara De Pinto',47),
(5747745,'Sitionuevo',47),
(5747798,'Tenerife',47),
(5747960,'Zapayán',47),
(5747980,'Zona Bananera',47),
(5750001,'Villavicencio',50),
(5750006,'Acacías',50),
(5750110,'Barranca De Upía',50),
(5750124,'Cabuyaro',50),
(5750150,'Castilla La Nueva',50),
(5750223,'Cubarral',50),
(5750226,'Cumaral',50),
(5750245,'El Calvario',50),
(5750251,'El Castillo',50),
(5750270,'El Dorado',50),
(5750287,'Fuente De Oro',50),
(5750313,'Granada',50),
(5750318,'Guamal',50),
(5750325,'Mapiripán',50),
(5750330,'Mesetas',50),
(5750350,'La Macarena',50),
(5750370,'Uribe',50),
(5750400,'Lejanías',50),
(5750450,'Puerto Concordia',50),
(5750568,'Puerto Gaitán',50),
(5750573,'Puerto López',50),
(5750577,'Puerto Lleras',50),
(5750590,'Puerto Rico',50),
(5750606,'Restrepo',50),
(5750680,'San Carlos De Guaroa',50),
(5750683,'San Juan De Arama',50),
(5750686,'San Juanito',50),
(5750689,'San Martín',50),
(5750711,'Vistahermosa',50),
(5752001,'Pasto',52),
(5752019,'Albán',52),
(5752022,'Aldana',52),
(5752036,'Ancuyá',52),
(5752051,'Arboleda',52),
(5752079,'Barbacoas',52),
(5752083,'Belén',52),
(5752110,'Buesaco',52),
(5752203,'Colón',52),
(5752207,'Consaca',52),
(5752210,'Contadero',52),
(5752215,'Córdoba',52),
(5752224,'Cuaspud',52),
(5752227,'Cumbal',52),
(5752233,'Cumbitara',52),
(5752240,'Chachagüí',52),
(5752250,'El Charco',52),
(5752254,'El Peñol',52),
(5752256,'El Rosario',52),
(5752258,'El Tablón De Gómez',52),
(5752260,'El Tambo',52),
(5752287,'Funes',52),
(5752317,'Guachucal',52),
(5752320,'Guaitarilla',52),
(5752323,'Gualmatán',52),
(5752352,'Iles',52),
(5752354,'Imués',52),
(5752356,'Ipiales',52),
(5752378,'La Cruz',52),
(5752381,'La Florida',52),
(5752385,'La Llanada',52),
(5752390,'La Tola',52),
(5752399,'La Unión',52),
(5752405,'Leiva',52),
(5752411,'Linares',52),
(5752418,'Los Andes',52),
(5752427,'Magüi',52),
(5752435,'Mallama',52),
(5752473,'Mosquera',52),
(5752480,'Nariño',52),
(5752490,'Olaya Herrera',52),
(5752506,'Ospina',52),
(5752520,'Francisco Pizarro',52),
(5752540,'Policarpa',52),
(5752560,'Potosí',52),
(5752565,'Providencia',52),
(5752573,'Puerres',52),
(5752585,'Pupiales',52),
(5752612,'Ricaurte',52),
(5752621,'Roberto Payán',52),
(5752678,'Samaniego',52),
(5752683,'Sandoná',52),
(5752685,'San Bernardo',52),
(5752687,'San Lorenzo',52),
(5752693,'San Pablo',52),
(5752694,'San Pedro De Cartago',52),
(5752696,'Santa Bárbara',52),
(5752699,'Santacruz',52),
(5752720,'Sapuyes',52),
(5752786,'Taminango',52),
(5752788,'Tangua',52),
(5752835,'Tumaco',52),
(5752838,'Túquerres',52),
(5752885,'Yacuanquer',52),
(5754001,'Cúcuta',54),
(5754003,'Abrego',54),
(5754051,'Arboledas',54),
(5754099,'Bochalema',54),
(5754109,'Bucarasica',54),
(5754125,'Cácota',54),
(5754128,'Cachirá',54),
(5754172,'Chinácota',54),
(5754174,'Chitagá',54),
(5754206,'Convención',54),
(5754223,'Cucutilla',54),
(5754239,'Durania',54),
(5754245,'El Carmen',54),
(5754250,'El Tarra',54),
(5754261,'El Zulia',54),
(5754313,'Gramalote',54),
(5754344,'Hacarí',54),
(5754347,'Herrán',54),
(5754377,'Labateca',54),
(5754385,'La Esperanza',54),
(5754398,'La Playa',54),
(5754405,'Los Patios',54),
(5754418,'Lourdes',54),
(5754480,'Mutiscua',54),
(5754498,'Ocaña',54),
(5754518,'Pamplona',54),
(5754520,'Pamplonita',54),
(5754553,'Puerto Santander',54),
(5754599,'Ragonvalia',54),
(5754660,'Salazar',54),
(5754670,'San Calixto',54),
(5754673,'San Cayetano',54),
(5754680,'Santiago',54),
(5754720,'Sardinata',54),
(5754743,'Silos',54),
(5754800,'Teorama',54),
(5754810,'Tibú',54),
(5754820,'Toledo',54),
(5754871,'Villa Caro',54),
(5754874,'Villa Del Rosario',54),
(5763001,'Armenia',63),
(5763111,'Buenavista',63),
(5763130,'Calarca',63),
(5763190,'Circasia',63),
(5763212,'Córdoba',63),
(5763272,'Filandia',63),
(5763302,'Génova',63),
(5763401,'La Tebaida',63),
(5763470,'Montenegro',63),
(5763548,'Pijao',63),
(5763594,'Quimbaya',63),
(5763690,'Salento',63),
(5766001,'Pereira',66),
(5766045,'Apía',66),
(5766075,'Balboa',66),
(5766088,'Belén De Umbría',66),
(5766170,'Dosquebradas',66),
(5766318,'Guática',66),
(5766383,'La Celia',66),
(5766400,'La Virginia',66),
(5766440,'Marsella',66),
(5766456,'Mistrató',66),
(5766572,'Pueblo Rico',66),
(5766594,'Quinchía',66),
(5766682,'Santa Rosa De Cabal',66),
(5766687,'Santuario',66),
(5768001,'Bucaramanga',68),
(5768013,'Aguada',68),
(5768020,'Albania',68),
(5768051,'Aratoca',68),
(5768077,'Barbosa',68),
(5768079,'Barichara',68),
(5768081,'Barrancabermeja',68),
(5768092,'Betulia',68),
(5768101,'Bolívar',68),
(5768121,'Cabrera',68),
(5768132,'California',68),
(5768147,'Capitanejo',68),
(5768152,'Carcasí',68),
(5768160,'Cepitá',68),
(5768162,'Cerrito',68),
(5768167,'Charalá',68),
(5768169,'Charta',68),
(5768176,'Chima',68),
(5768179,'Chipatá',68),
(5768190,'Cimitarra',68),
(5768207,'Concepción',68),
(5768209,'Confines',68),
(5768211,'Contratación',68),
(5768217,'Coromoro',68),
(5768229,'Curití',68),
(5768235,'El Carmen De Chucurí',68),
(5768245,'El Guacamayo',68),
(5768250,'El Peñón',68),
(5768255,'El Playón',68),
(5768264,'Encino',68),
(5768266,'Enciso',68),
(5768271,'Florián',68),
(5768276,'Floridablanca',68),
(5768296,'Galán',68),
(5768298,'Gambita',68),
(5768307,'Girón',68),
(5768318,'Guaca',68),
(5768320,'Guadalupe',68),
(5768322,'Guapotá',68),
(5768324,'Guavatá',68),
(5768327,'Güepsa',68),
(5768344,'Hato',68),
(5768368,'Jesús María',68),
(5768370,'Jordán',68),
(5768377,'La Belleza',68),
(5768385,'Landázuri',68),
(5768397,'La Paz',68),
(5768406,'Lebríja',68),
(5768418,'Los Santos',68),
(5768425,'Macaravita',68),
(5768432,'Málaga',68),
(5768444,'Matanza',68),
(5768464,'Mogotes',68),
(5768468,'Molagavita',68),
(5768498,'Ocamonte',68),
(5768500,'Oiba',68),
(5768502,'Onzaga',68),
(5768522,'Palmar',68),
(5768524,'Palmas Del Socorro',68),
(5768533,'Páramo',68),
(5768547,'Piedecuesta',68),
(5768549,'Pinchote',68),
(5768572,'Puente Nacional',68),
(5768573,'Puerto Parra',68),
(5768575,'Puerto Wilches',68),
(5768615,'Rionegro',68),
(5768655,'Sabana De Torres',68),
(5768669,'San Andrés',68),
(5768673,'San Benito',68),
(5768679,'San Gil',68),
(5768682,'San Joaquín',68),
(5768684,'San José De Miranda',68),
(5768686,'San Miguel',68),
(5768689,'San Vicente De Chucurí',68),
(5768705,'Santa Bárbara',68),
(5768720,'Santa Helena Del Opón',68),
(5768745,'Simacota',68),
(5768755,'Socorro',68),
(5768770,'Suaita',68),
(5768773,'Sucre',68),
(5768780,'Suratá',68),
(5768820,'Tona',68),
(5768855,'Valle De San José',68),
(5768861,'Vélez',68),
(5768867,'Vetas',68),
(5768872,'Villanueva',68),
(5768895,'Zapatoca',68),
(5770001,'Sincelejo',70),
(5770110,'Buenavista',70),
(5770124,'Caimito',70),
(5770204,'Coloso',70),
(5770215,'Corozal',70),
(5770221,'Coveñas',70),
(5770230,'Chalán',70),
(5770233,'El Roble',70),
(5770235,'Galeras',70),
(5770265,'Guaranda',70),
(5770400,'La Unión',70),
(5770418,'Los Palmitos',70),
(5770429,'Majagual',70),
(5770473,'Morroa',70),
(5770508,'Ovejas',70),
(5770523,'Palmito',70),
(5770670,'Sampués',70),
(5770678,'San Benito Abad',70),
(5770702,'San Juan De Betulia',70),
(5770708,'San Marcos',70),
(5770713,'San Onofre',70),
(5770717,'San Pedro',70),
(5770742,'San Luis De Sincé',70),
(5770771,'Sucre',70),
(5770820,'Santiago De Tolú',70),
(5770823,'Tolú Viejo',70),
(5773001,'Ibagué',73),
(5773024,'Alpujarra',73),
(5773026,'Alvarado',73),
(5773030,'Ambalema',73),
(5773043,'Anzoátegui',73),
(5773055,'Armero',73),
(5773067,'Ataco',73),
(5773124,'Cajamarca',73),
(5773148,'Carmen De Apicalá',73),
(5773152,'Casabianca',73),
(5773168,'Chaparral',73),
(5773200,'Coello',73),
(5773217,'Coyaima',73),
(5773226,'Cunday',73),
(5773236,'Dolores',73),
(5773268,'Espinal',73),
(5773270,'Falan',73),
(5773275,'Flandes',73),
(5773283,'Fresno',73),
(5773319,'Guamo',73),
(5773347,'Herveo',73),
(5773349,'Honda',73),
(5773352,'Icononzo',73),
(5773408,'Lérida',73),
(5773411,'Líbano',73),
(5773443,'Mariquita',73),
(5773449,'Melgar',73),
(5773461,'Murillo',73),
(5773483,'Natagaima',73),
(5773504,'Ortega',73),
(5773520,'Palocabildo',73),
(5773547,'Piedras',73),
(5773555,'Planadas',73),
(5773563,'Prado',73),
(5773585,'Purificación',73),
(5773616,'Rioblanco',73),
(5773622,'Roncesvalles',73),
(5773624,'Rovira',73),
(5773671,'Saldaña',73),
(5773675,'San Antonio',73),
(5773678,'San Luis',73),
(5773686,'Santa Isabel',73),
(5773770,'Suárez',73),
(5773854,'Valle De San Juan',73),
(5773861,'Venadillo',73),
(5773870,'Villahermosa',73),
(5773873,'Villarrica',73),
(5776001,'Cali',76),
(5776020,'Alcalá',76),
(5776036,'Andalucía',76),
(5776041,'Ansermanuevo',76),
(5776054,'Argelia',76),
(5776100,'Bolívar',76),
(5776109,'Buenaventura',76),
(5776111,'Guadalajara De Buga',76),
(5776113,'Bugalagrande',76),
(5776122,'Caicedonia',76),
(5776126,'Calima',76),
(5776130,'Candelaria',76),
(5776147,'Cartago',76),
(5776233,'Dagua',76),
(5776243,'El Águila',76),
(5776246,'El Cairo',76),
(5776248,'El Cerrito',76),
(5776250,'El Dovio',76),
(5776275,'Florida',76),
(5776306,'Ginebra',76),
(5776318,'Guacarí',76),
(5776364,'Jamundí',76),
(5776377,'La Cumbre',76),
(5776400,'La Unión',76),
(5776403,'La Victoria',76),
(5776497,'Obando',76),
(5776520,'Palmira',76),
(5776563,'Pradera',76),
(5776606,'Restrepo',76),
(5776616,'Riofrío',76),
(5776622,'Roldanillo',76),
(5776670,'San Pedro',76),
(5776736,'Sevilla',76),
(5776823,'Toro',76),
(5776828,'Trujillo',76),
(5776834,'Tuluá',76),
(5776845,'Ulloa',76),
(5776863,'Versalles',76),
(5776869,'Vijes',76),
(5776890,'Yotoco',76),
(5776892,'Yumbo',76),
(5776895,'Zarzal',76),
(5781001,'Arauca',81),
(5781065,'Arauquita',81),
(5781220,'Cravo Norte',81),
(5781300,'Fortul',81),
(5781591,'Puerto Rondón',81),
(5781736,'Saravena',81),
(5781794,'Tame',81),
(5785001,'Yopal',85),
(5785010,'Aguazul',85),
(5785015,'Chameza',85),
(5785125,'Hato Corozal',85),
(5785136,'La Salina',85),
(5785139,'Maní',85),
(5785162,'Monterrey',85),
(5785225,'Nunchía',85),
(5785230,'Orocué',85),
(5785250,'Paz De Ariporo',85),
(5785263,'Pore',85),
(5785279,'Recetor',85),
(5785300,'Sabanalarga',85),
(5785315,'Sácama',85),
(5785325,'San Luis De Palenque',85),
(5785400,'Támara',85),
(5785410,'Tauramena',85),
(5785430,'Trinidad',85),
(5785440,'Villanueva',85),
(5786001,'Mocoa',86),
(5786219,'Colón',86),
(5786320,'Orito',86),
(5786568,'Puerto Asís',86),
(5786569,'Puerto Caicedo',86),
(5786571,'Puerto Guzmán',86),
(5786573,'Leguízamo',86),
(5786749,'Sibundoy',86),
(5786755,'San Francisco',86),
(5786757,'San Miguel',86),
(5786760,'Santiago',86),
(5786865,'Valle Del Guamuez',86),
(5786885,'Villagarzón',86),
(5788001,'Providencia Y Santa Catalina\"',88),
(5788564,'Providencia Y Santa Catalina\"',88),
(5791001,'Leticia',91),
(5791263,'El Encanto',91),
(5791405,'La Chorrera',91),
(5791407,'La Pedrera',91),
(5791430,'La Victoria',91),
(5791460,'Miriti - Paraná',91),
(5791530,'Puerto Alegría',91),
(5791536,'Puerto Arica',91),
(5791540,'Puerto Nariño',91),
(5791669,'Puerto Santander',91),
(5791798,'Tarapacá',91),
(5794001,'Inírida',94),
(5794343,'Barranco Minas',94),
(5794663,'Mapiripana',94),
(5794883,'San Felipe',94),
(5794884,'Puerto Colombia',94),
(5794885,'La Guadalupe',94),
(5794886,'Cacahual',94),
(5794887,'Pana Pana',94),
(5794888,'Morichal',94),
(5795001,'San José Del Guaviare',95),
(5795015,'Calamar',95),
(5795025,'El Retorno',95),
(5795200,'Miraflores',95),
(5797001,'Mitú',97),
(5797161,'Caruru',97),
(5797511,'Pacoa',97),
(5797666,'Taraira',97),
(5797777,'Papunaua',97),
(5797889,'Yavaraté',97),
(5799001,'Puerto Carreño',99),
(5799524,'La Primavera',99),
(5799624,'Santa Rosalía',99),
(5799773,'Cumaribo',99);

UNLOCK TABLES;

/*Table structure for table `aux_tipodoc` */

DROP TABLE IF EXISTS `aux_tipodoc`;

CREATE TABLE `aux_tipodoc` (
  `id_tipo_doc` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Identificador unico en base de datos',
  `nom_tipo_doc` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Nombre del tipo de documento',
  `sigla_tipo_doc` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Nombre corto',
  PRIMARY KEY (`id_tipo_doc`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1 DELAY_KEY_WRITE=1;

/*Data for the table `aux_tipodoc` */

LOCK TABLES `aux_tipodoc` WRITE;

insert  into `aux_tipodoc`(`id_tipo_doc`,`nom_tipo_doc`,`sigla_tipo_doc`) values 
(1,'NUMERO DE IDENTIFICACIÓN TRIBUTARIA','NIT'),
(2,'CÉDULA DE CIUDADANÍA','CC'),
(3,'CARNET DIPLOMÁTICO','CD'),
(4,'CÉDULA DE EXTRANJERÍA','CE'),
(5,'FIDEICOMISO','FD'),
(6,'OTROS','O'),
(7,'PASAPORTE','PS'),
(8,'REGISTRO CIVIL DE NACIMIENTO','RC'),
(9,'TARJETA SEGURO SOCIAL EXTRANJERO','TSSE'),
(10,'TARJETA DE IDENTIDAD','TI');

UNLOCK TABLES;

/*Table structure for table `empresa` */

DROP TABLE IF EXISTS `empresa`;

CREATE TABLE `empresa` (
  `id_emp` varchar(36) NOT NULL COMMENT 'Identificador unico de empresa en base de datos',
  `nom_emp` varchar(512) NOT NULL COMMENT 'Nombre de la empresa',
  `mun_emp` int(11) DEFAULT NULL COMMENT 'Municipio de la empresa',
  `nit_emp` varchar(36) NOT NULL COMMENT 'Nit de la empresa',
  `dir_emp` varchar(512) DEFAULT NULL COMMENT 'Direccion principal',
  `tel_emp` varchar(24) DEFAULT NULL COMMENT 'Numero de teléfono',
  PRIMARY KEY (`id_emp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `empresa` */

LOCK TABLES `empresa` WRITE;

insert  into `empresa`(`id_emp`,`nom_emp`,`mun_emp`,`nit_emp`,`dir_emp`,`tel_emp`) values 
('e1774c92-78a0-11e8-aaee-00fff8a19e36','GRUPO ASD S.A.S',5711001,'860510031-7','Calle 32 No.13 - 07','340 2501');

UNLOCK TABLES;

/*Table structure for table `personas` */

DROP TABLE IF EXISTS `personas`;

CREATE TABLE `personas` (
  `id_per` varchar(36) NOT NULL COMMENT 'Identificador unico de persona en base de datos',
  `num_doc_per` varchar(64) NOT NULL COMMENT 'Numero de documento identificador de persona (CC,TP,NUIP,ETC...)',
  `tipo_doc_id` int(11) DEFAULT NULL COMMENT 'Tipo de identificacion',
  `mun_exp_doc_id` int(11) DEFAULT NULL COMMENT 'Municipio donde se expidio documento de identificacion',
  `gen_per` enum('M','F') DEFAULT NULL COMMENT 'Sexo biológico de una persona',
  `nom1_per` varchar(255) NOT NULL COMMENT 'Primer nombre',
  `nom2_per` varchar(255) DEFAULT NULL COMMENT 'Segundo Nombre',
  `ape1_per` varchar(255) NOT NULL COMMENT 'Primer apellido',
  `ape2_per` varchar(255) DEFAULT NULL COMMENT 'Segundo Apellido',
  `fecha_nac_per` date DEFAULT NULL COMMENT 'Fecha de nacimiento de la persona',
  `mun_res_per_id` int(11) DEFAULT NULL COMMENT 'Municipio donde reside',
  `dir_per` varchar(512) DEFAULT NULL COMMENT 'Direccion donde reside',
  `cel_per` varchar(24) DEFAULT NULL COMMENT 'Numero de celular',
  `email_per` varchar(256) DEFAULT NULL COMMENT 'Email de persona',
  PRIMARY KEY (`id_per`),
  KEY `ID_MUN_EXP_DOC` (`mun_exp_doc_id`),
  KEY `ID_MUN_RES_PER` (`mun_res_per_id`),
  KEY `TIPO_DOC_PER` (`tipo_doc_id`),
  CONSTRAINT `personas_ibfk_1` FOREIGN KEY (`mun_exp_doc_id`) REFERENCES `aux_mpio` (`id_mpio`),
  CONSTRAINT `personas_ibfk_2` FOREIGN KEY (`mun_res_per_id`) REFERENCES `aux_mpio` (`id_mpio`),
  CONSTRAINT `personas_ibfk_3` FOREIGN KEY (`tipo_doc_id`) REFERENCES `aux_tipodoc` (`id_tipo_doc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `personas` */

LOCK TABLES `personas` WRITE;

insert  into `personas`(`id_per`,`num_doc_per`,`tipo_doc_id`,`mun_exp_doc_id`,`gen_per`,`nom1_per`,`nom2_per`,`ape1_per`,`ape2_per`,`fecha_nac_per`,`mun_res_per_id`,`dir_per`,`cel_per`,`email_per`) values 
('6832548b-7a1c-11e8-8786-00fff8a19e36','101020',NULL,NULL,NULL,'CARLOS','REIMUNDO','RAMIREZ','MOLINA',NULL,5786001,'BARRIO 5 DE SEPTIEMBRE','3183946068',NULL),
('f73c9d94-6934-4864-9430-dcbe1a907514','100001',2,5786571,'M','WILMER','H','RAMIREZ','GAMEZ','1996-04-10',5711001,'81A # 81-55','3183999967','wilmerhmg@gmail.com');

UNLOCK TABLES;

/* Trigger structure for table `asignaciones` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `INSERT_ASIGNACION` */$$

/*!50003 CREATE */ /*!50003 TRIGGER `INSERT_ASIGNACION` AFTER INSERT ON `asignaciones` FOR EACH ROW BEGIN
	#Actualiza el estado del activo
	update activos set estado_id = 5 where id_act = new.activo_id;
    END */$$


DELIMITER ;

/* Trigger structure for table `asignaciones` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `DELETE_ASIGNACION` */$$

/*!50003 CREATE */ /*!50003 TRIGGER `DELETE_ASIGNACION` AFTER DELETE ON `asignaciones` FOR EACH ROW BEGIN
	UPDATE activos SET estado_id = 4 WHERE id_act=old.activo_id;
    END */$$


DELIMITER ;

/*Table structure for table `view_asignaciones` */

DROP TABLE IF EXISTS `view_asignaciones`;

/*!50001 DROP VIEW IF EXISTS `view_asignaciones` */;
/*!50001 DROP TABLE IF EXISTS `view_asignaciones` */;

/*!50001 CREATE TABLE  `view_asignaciones`(
 `id_asignacion` varchar(36) ,
 `activo_id` varchar(36) ,
 `area_id` varchar(36) ,
 `persona_id` varchar(36) ,
 `fecha_asignacion` timestamp ,
 `nom_act` varchar(323) ,
 `Asignado` text 
)*/;

/*View structure for view view_asignaciones */

/*!50001 DROP TABLE IF EXISTS `view_asignaciones` */;
/*!50001 DROP VIEW IF EXISTS `view_asignaciones` */;

/*!50001 CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_asignaciones` AS (select `asignaciones`.`id_asignacion` AS `id_asignacion`,`asignaciones`.`activo_id` AS `activo_id`,`asignaciones`.`area_id` AS `area_id`,`asignaciones`.`persona_id` AS `persona_id`,`asignaciones`.`fecha_asignacion` AS `fecha_asignacion`,concat_ws(' - ',`activos`.`srl_act`,`activos`.`nom_act`) AS `nom_act`,if(isnull(`asignaciones`.`area_id`),concat(`personas`.`num_doc_per`,' - ',`personas`.`nom1_per`,' ',`personas`.`ape1_per`),concat_ws(' - ',`areas`.`nom_area`,`aux_mpio`.`nom_mpio`)) AS `Asignado` from ((((`asignaciones` left join `areas` on((`asignaciones`.`area_id` = `areas`.`id_area`))) join `activos` on((`asignaciones`.`activo_id` = `activos`.`id_act`))) left join `personas` on((`asignaciones`.`persona_id` = `personas`.`id_per`))) left join `aux_mpio` on((`areas`.`mun_id` = `aux_mpio`.`id_mpio`)))) */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;