244 lines
8.3 KiB
MySQL
244 lines
8.3 KiB
MySQL
|
USE vn;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`business`
|
||
|
|
ADD COLUMN `workerFk` INT(10) UNSIGNED NOT NULL AFTER `occupationCodeFk`;
|
||
|
|
|
||
|
|
UPDATE postgresql.business b
|
||
|
|
JOIN postgresql.profile p ON p.profile_id = b.client_id
|
||
|
|
SET b.workerFk = p.workerFk
|
||
|
|
WHERE p.workerFk IS NOT NULL;
|
||
|
|
|
||
|
|
-- 5 líneas afectadas contratos 2014 (info en A3)
|
||
|
|
DELETE FROM postgresql.business
|
||
|
|
WHERE NOT workerFk;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`business`
|
||
|
|
ADD INDEX `business_workerFk_idx` (`workerFk` ASC) ;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`business`
|
||
|
|
ADD CONSTRAINT `business_workerFk`
|
||
|
|
FOREIGN KEY (`workerFk`)
|
||
|
|
REFERENCES `vn`.`worker` (`id`)
|
||
|
|
ON DELETE RESTRICT
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
CREATE TABLE `business` (
|
||
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`companyCodeFk` char(3) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
|
||
|
|
`started` date DEFAULT NULL,
|
||
|
|
`ended` date DEFAULT NULL,
|
||
|
|
`workerBusiness` longtext DEFAULT NULL,
|
||
|
|
`reasonEndFk` int(11) DEFAULT NULL,
|
||
|
|
`payedHolidays` decimal(5,2) NOT NULL DEFAULT 0.00,
|
||
|
|
`occupationCodeFk` varchar(1) DEFAULT NULL,
|
||
|
|
`workerFk` int(10) unsigned NOT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
KEY `business_occupationCodeFk` (`occupationCodeFk`),
|
||
|
|
KEY `business_companyCodeFk` (`companyCodeFk`),
|
||
|
|
KEY `business_workerFk_idx` (`workerFk`),
|
||
|
|
CONSTRAINT `business_companyCodeFk` FOREIGN KEY (`companyCodeFk`) REFERENCES `vn`.`company` (`code`) ON UPDATE CASCADE,
|
||
|
|
CONSTRAINT `business_occupationCodeFk` FOREIGN KEY (`occupationCodeFk`) REFERENCES `vn`.`occupationCode` (`code`) ON UPDATE CASCADE,
|
||
|
|
CONSTRAINT `business_workerFk` FOREIGN KEY (`workerFk`) REFERENCES `vn`.`worker` (`id`) ON UPDATE CASCADE
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
|
||
|
|
|
||
|
|
INSERT INTO vn.business (id,
|
||
|
|
companyCodeFk,
|
||
|
|
started,
|
||
|
|
ended,
|
||
|
|
workerBusiness,
|
||
|
|
reasonEndFk,
|
||
|
|
payedHolidays,
|
||
|
|
occupationCodeFk,
|
||
|
|
workerFk )
|
||
|
|
SELECT business_id,
|
||
|
|
companyCodeFk,
|
||
|
|
date_start,
|
||
|
|
date_end,
|
||
|
|
workerBusiness,
|
||
|
|
reasonEndFk,
|
||
|
|
IFNULL(payedHolidays, 0),
|
||
|
|
occupationCodeFk,
|
||
|
|
workerFk
|
||
|
|
FROM postgresql.business;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`worker`
|
||
|
|
DROP FOREIGN KEY `worker_business`;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`worker`
|
||
|
|
ADD CONSTRAINT `worker_businessFk`
|
||
|
|
FOREIGN KEY (`businessFk`)
|
||
|
|
REFERENCES `vn`.`business` (`id`)
|
||
|
|
ON DELETE SET NULL
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`business_labour_payroll`
|
||
|
|
DROP FOREIGN KEY `business_labour_payroll_fk1`;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`business_labour_payroll`
|
||
|
|
ADD CONSTRAINT `business_labour_payroll_business_id`
|
||
|
|
FOREIGN KEY (`business_id`)
|
||
|
|
REFERENCES `vn`.`business` (`id`)
|
||
|
|
ON DELETE CASCADE
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`business_labour`
|
||
|
|
DROP FOREIGN KEY `bus_restriction`;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`business_labour`
|
||
|
|
ADD CONSTRAINT `business_labour_business_id`
|
||
|
|
FOREIGN KEY (`business_id`)
|
||
|
|
REFERENCES `vn`.`business` (`id`)
|
||
|
|
ON DELETE CASCADE
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- Es necesario hacerlo aquí porque falla en otras vistas donde se utiliza esta
|
||
|
|
CREATE
|
||
|
|
OR REPLACE ALGORITHM = UNDEFINED
|
||
|
|
DEFINER = `root`@`localhost`
|
||
|
|
SQL SECURITY DEFINER
|
||
|
|
VIEW `postgresql`.`calendar_employee` AS
|
||
|
|
SELECT
|
||
|
|
`ce`.`id` AS `id`,
|
||
|
|
`ce`.`businessFk` AS `businessFk`,
|
||
|
|
`ce`.`dayOffTypeFk` AS `calendar_state_id`,
|
||
|
|
`ce`.`dated` AS `date`
|
||
|
|
FROM
|
||
|
|
`vn`.`calendar` `ce`;
|
||
|
|
|
||
|
|
DROP TABLE `vn`.`profile`;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD COLUMN `notes` LONGTEXT NULL DEFAULT NULL AFTER `workerFk`,
|
||
|
|
ADD COLUMN `departmentFk` INT(11) NULL DEFAULT NULL AFTER `notes`,
|
||
|
|
ADD COLUMN `workerBusinessProfessionalCategoryFk` INT(11) NULL DEFAULT NULL AFTER `departmentFk`,
|
||
|
|
ADD COLUMN `calendarTypeFk` INT(11) NULL DEFAULT 1 AFTER `workerBusinessProfessionalCategoryFk`,
|
||
|
|
ADD COLUMN `isHourlyLabor` tinyint(1) NOT NULL DEFAULT FALSE COMMENT 'Contrato por horas' AFTER `calendarTypeFk`,
|
||
|
|
ADD COLUMN `workcenterFk` INT(11) NOT NULL AFTER `isHourlyLabor`,
|
||
|
|
ADD COLUMN `rate` INT(11) NULL DEFAULT NULL AFTER `workcenterFk`,
|
||
|
|
ADD COLUMN `workerBusinessCategoryFk` INT(11) NULL DEFAULT NULL AFTER `rate`,
|
||
|
|
ADD COLUMN `workerBusinessTypeFk` INT(11) NULL DEFAULT NULL AFTER `workerBusinessCategoryFk`,
|
||
|
|
ADD COLUMN `amount` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Importe pactado' AFTER `workerBusinessTypeFk`,
|
||
|
|
ADD COLUMN `workerBusinessAgreementFk` INT(11) NULL DEFAULT NULL AFTER `amount`;
|
||
|
|
|
||
|
|
UPDATE vn.business b
|
||
|
|
LEFT JOIN postgresql.business_labour bl ON bl.business_id = b.id
|
||
|
|
LEFT JOIN postgresql.business_labour_payroll blp ON blp.business_id = b.id
|
||
|
|
SET b.notes = bl.notes,
|
||
|
|
b.departmentFk = bl.department_id,
|
||
|
|
b.workerBusinessProfessionalCategoryFk = bl.professional_category_id,
|
||
|
|
b.calendarTypeFk = bl.calendar_labour_type_id,
|
||
|
|
b.isHourlyLabor = bl.porhoras,
|
||
|
|
b.workcenterFk = bl.workcenter_id,
|
||
|
|
b.rate = blp.cod_tarifa,
|
||
|
|
b.workerBusinessCategoryFk = blp.cod_categoria,
|
||
|
|
b.workerBusinessTypeFk = blp.cod_contrato,
|
||
|
|
b.amount = blp.importepactado,
|
||
|
|
b.workerBusinessAgreementFk = bl.labour_agreement_id;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD INDEX `business_departmentFk_idx` (`departmentFk` ASC);
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD CONSTRAINT `business_departmentFk`
|
||
|
|
FOREIGN KEY (`departmentFk`)
|
||
|
|
REFERENCES `vn`.`department` (`id`)
|
||
|
|
ON DELETE RESTRICT
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD INDEX `business_workerBusinessProfessionalCategoryFk_idx` (`workerBusinessProfessionalCategoryFk` ASC) ;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD CONSTRAINT `business_workerBusinessProfessionalCategoryFk`
|
||
|
|
FOREIGN KEY (`workerBusinessProfessionalCategoryFk`)
|
||
|
|
REFERENCES `postgresql`.`professional_category` (`professional_category_id`)
|
||
|
|
ON DELETE RESTRICT
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD INDEX `business_calendarTypeFk_idx` (`calendarTypeFk` ASC);
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD CONSTRAINT `business_calendarTypeFk`
|
||
|
|
FOREIGN KEY (`calendarTypeFk`)
|
||
|
|
REFERENCES `postgresql`.`calendar_labour_type` (`calendar_labour_type_id`)
|
||
|
|
ON DELETE RESTRICT
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD CONSTRAINT `business_workerBusinessCategoryFk`
|
||
|
|
FOREIGN KEY (`workerBusinessCategoryFk`)
|
||
|
|
REFERENCES `vn2008`.`payroll_categorias` (`codcategoria`)
|
||
|
|
ON DELETE RESTRICT
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD INDEX `business_workerBusinessTypeFk_idx` (`workerBusinessTypeFk` ASC);
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD CONSTRAINT `business_workerBusinessTypeFk`
|
||
|
|
FOREIGN KEY (`workerBusinessTypeFk`)
|
||
|
|
REFERENCES `vn`.`workerBusinessType` (`id`)
|
||
|
|
ON DELETE RESTRICT
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD INDEX `business_workerBusinessAgreementFk_idx` (`workerBusinessAgreementFk` ASC);
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`business`
|
||
|
|
ADD CONSTRAINT `business_workerBusinessAgreementFk`
|
||
|
|
FOREIGN KEY (`workerBusinessAgreementFk`)
|
||
|
|
REFERENCES `postgresql`.`labour_agreement` (`labour_agreement_id`)
|
||
|
|
ON DELETE RESTRICT
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`profile`
|
||
|
|
RENAME TO `postgresql`.`profile__` ;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`business`
|
||
|
|
RENAME TO `postgresql`.`business__` ;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`business_labour`
|
||
|
|
RENAME TO `postgresql`.`business_labour__` ;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`business_labour_payroll`
|
||
|
|
RENAME TO `postgresql`.`business_labour_payroll__` ;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`journey`
|
||
|
|
DROP FOREIGN KEY `business_journey`;
|
||
|
|
ALTER TABLE `postgresql`.`journey`
|
||
|
|
ADD INDEX `journey_business_id_idx` (`business_id` ASC) ,
|
||
|
|
DROP INDEX `fki_business_journey` ;
|
||
|
|
|
||
|
|
ALTER TABLE `postgresql`.`journey`
|
||
|
|
ADD CONSTRAINT `journey_business_id`
|
||
|
|
FOREIGN KEY (`business_id`)
|
||
|
|
REFERENCES `vn`.`business` (`id`)
|
||
|
|
ON DELETE RESTRICT
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`calendar`
|
||
|
|
DROP FOREIGN KEY `calendar_businessFk`;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`calendar`
|
||
|
|
DROP INDEX `business_id_date` ;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`calendar`
|
||
|
|
ADD CONSTRAINT `calendar_businessFk`
|
||
|
|
FOREIGN KEY (`businessFk`)
|
||
|
|
REFERENCES `vn`.`business` (`id`)
|
||
|
|
ON DELETE RESTRICT
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`workerJourney`
|
||
|
|
DROP FOREIGN KEY `workerJourney_businessFk`;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`workerJourney`
|
||
|
|
DROP INDEX `workerJourney_businessFk_idx` ;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`workerJourney`
|
||
|
|
ADD CONSTRAINT `workerJourney_businessFk`
|
||
|
|
FOREIGN KEY (`businessFk`)
|
||
|
|
REFERENCES `vn`.`business` (`id`)
|
||
|
|
ON DELETE RESTRICT
|
||
|
|
ON UPDATE CASCADE;
|