227 lines
9.2 KiB
MySQL
227 lines
9.2 KiB
MySQL
|
USE vn;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`route`
|
||
|
|
CHANGE COLUMN `kmStart` `kmStart` MEDIUMINT(9) NULL DEFAULT NULL ,
|
||
|
|
CHANGE COLUMN `kmEnd` `kmEnd` MEDIUMINT(9) NULL DEFAULT NULL ;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS `vn`.`routeCommission`;
|
||
|
|
CREATE TABLE `vn`.`routeCommission` (
|
||
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
||
|
|
`routeFk` int(10) unsigned NOT NULL,
|
||
|
|
`workCenterFk` int(11) DEFAULT NULL,
|
||
|
|
`km` decimal(5,2) DEFAULT NULL,
|
||
|
|
`m3` decimal(5,2) DEFAULT NULL,
|
||
|
|
`yearlyKm` decimal(5,2) DEFAULT NULL,
|
||
|
|
`yearlyM3` decimal(5,2) DEFAULT NULL,
|
||
|
|
`cat4m3` decimal(5,2) DEFAULT NULL,
|
||
|
|
`cat5m3` decimal(5,2) DEFAULT NULL,
|
||
|
|
`freelanceYearlyM3` decimal(5,2) DEFAULT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
KEY `routeCommission_routeFk_idx` (`routeFk`),
|
||
|
|
KEY `routeCommission_workCenterFk_idx` (`workCenterFk`),
|
||
|
|
CONSTRAINT `routeCommission_routeFk` FOREIGN KEY (`routeFk`) REFERENCES `route` (`id`) ON UPDATE CASCADE,
|
||
|
|
CONSTRAINT `routeCommission_workCenterFk` FOREIGN KEY (`workCenterFk`) REFERENCES `workCenter` (`id`) ON UPDATE CASCADE
|
||
|
|
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`routeConfig`
|
||
|
|
ADD COLUMN `kmHeavy` decimal(5,2) DEFAULT NULL COMMENT 'Comisión por kilometro vehículo pesado',
|
||
|
|
ADD COLUMN `kmLight` decimal(5,2) DEFAULT NULL COMMENT 'Comisión por kilometro vehículo ligero',
|
||
|
|
ADD COLUMN `kmYearly` decimal(5,2) DEFAULT NULL COMMENT 'Comisión por kilometro objetivo anual',
|
||
|
|
ADD COLUMN `m3Yearly` decimal(5,2) DEFAULT NULL COMMENT 'Comisión por m3 objetivo anual',
|
||
|
|
ADD COLUMN `deliveryM3Cat4` decimal(5,2) DEFAULT NULL COMMENT 'Comisión por m3 repartido Cat.IV',
|
||
|
|
ADD COLUMN `deliveryM3Cat5` decimal(5,2) DEFAULT NULL COMMENT 'Comisión por m3 repartido Cat. V',
|
||
|
|
ADD COLUMN `plusCat2Fixed` decimal(5,2) DEFAULT NULL COMMENT 'Plus fijo Cat.II vehículo ligero',
|
||
|
|
ADD COLUMN `plusCat2Variable` decimal(5,2) DEFAULT NULL COMMENT 'Plus variable Cat.II vehículo ligero',
|
||
|
|
ADD COLUMN `plusCat3Fixed` decimal(5,2) DEFAULT NULL COMMENT 'Plus fijo Cat.III vehículo pesado',
|
||
|
|
ADD COLUMN `plusCat3Variable` decimal(5,2) DEFAULT NULL COMMENT 'Plus variable Cat.III vehículo pesado',
|
||
|
|
ADD COLUMN `distributionCat4M3` decimal(5,2) DEFAULT NULL COMMENT 'Comisión por gestión de la distribución Cat IV',
|
||
|
|
ADD COLUMN `distributionCat5M3` decimal(5,2) DEFAULT NULL COMMENT 'Comisión por gestión de la distribución Cat V',
|
||
|
|
ADD COLUMN `rateCat4` decimal(5,2) DEFAULT NULL COMMENT 'Ratio aplicado a la media de venta de los repartidores para el cálculo de las comisiones de enrutadores Cat IV',
|
||
|
|
ADD COLUMN `rateCat5` decimal(5,2) DEFAULT NULL COMMENT 'Ratio aplicado a la media de venta de los repartidores para el cálculo de las comisiones de enrutadores Cat V',
|
||
|
|
ADD COLUMN `freelanceM3` decimal(5,2) DEFAULT NULL COMMENT 'Comisión para enrutadores sobre rutas de autónomos',
|
||
|
|
ADD COLUMN `freelanceMinM3` decimal(5,2) DEFAULT NULL COMMENT 'Metros mínimos para considerar las rutas de autónomos',
|
||
|
|
ADD COLUMN `mainlineDelivered` decimal(5,2) DEFAULT NULL COMMENT 'Comisión para entregas troncales',
|
||
|
|
ADD COLUMN `cutoffDated` DATE DEFAULT NULL COMMENT 'Fecha a partir de la cual se autoriza calcular la comisión',
|
||
|
|
ADD COLUMN `defaultWorkCenterFk` INT DEFAULT 9 COMMENT 'Para el cálculo de las comisiones, en caso de el creador de la ruta no tenga workCenter',
|
||
|
|
ADD COLUMN `kmMax` INT NULL DEFAULT 4000 COMMENT 'Máximo número de km validos para una ruta';
|
||
|
|
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`route`
|
||
|
|
ADD COLUMN `commissionWorkCenterFk` INT(11) NULL COMMENT 'WorkerCenter que gestiona la ruta' AFTER `beachFk`,
|
||
|
|
ADD INDEX `route_WorkCenterFk_idx` (`commissionWorkCenterFk` ASC);
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`route`
|
||
|
|
ADD CONSTRAINT `route_WorkCenterFk`
|
||
|
|
FOREIGN KEY (`commissionWorkCenterFk`)
|
||
|
|
REFERENCES `vn`.`workCenter` (`id`)
|
||
|
|
ON DELETE RESTRICT
|
||
|
|
ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
UPDATE `vn`.`routeConfig`
|
||
|
|
SET `kmHeavy` = '0.12',
|
||
|
|
`kmLight` = '0.11',
|
||
|
|
`kmYearly` = '0.01',
|
||
|
|
`m3Yearly` = '0.25',
|
||
|
|
`deliveryM3Cat4` = '6.54',
|
||
|
|
`deliveryM3Cat5` = '6.54',
|
||
|
|
`plusCat2Fixed` = '75',
|
||
|
|
`plusCat2Variable` = '75',
|
||
|
|
`plusCat3Fixed` = '150',
|
||
|
|
`plusCat3Variable` = '150',
|
||
|
|
`distributionCat4M3` = '1.53',
|
||
|
|
`distributionCat5M3` = '1.7',
|
||
|
|
`rateCat4` = '3',
|
||
|
|
`rateCat5` = '4.66',
|
||
|
|
`freelanceM3` = '0.10',
|
||
|
|
`freelanceMinM3` = '1.5',
|
||
|
|
`mainlineDelivered` = '1',
|
||
|
|
`cutoffDated` = '2021-12-01'
|
||
|
|
WHERE (`id` = '1');
|
||
|
|
|
||
|
|
|
||
|
|
UPDATE vn.route r
|
||
|
|
JOIN(SELECT r.id, wl.workcenterFk
|
||
|
|
FROM vn.route r
|
||
|
|
JOIN vn.routeLog rl ON rl.originFk = r.id
|
||
|
|
JOIN vn.workerLabour wl ON wl.workerFk = rl.userFk
|
||
|
|
AND r.created BETWEEN wl.started AND IFNULL(wl.ended, r.created)
|
||
|
|
WHERE r.created BETWEEN '2021-12-01' AND util.CURDATE()
|
||
|
|
AND rl.action = 'insert'
|
||
|
|
)sub ON sub.id = r.id
|
||
|
|
SET r.commissionWorkCenterFk = sub.workcenterFk;
|
||
|
|
|
||
|
|
UPDATE vn.route
|
||
|
|
SET commissionWorkCenterFk = 9
|
||
|
|
WHERE id = 125360; -- la ruta no tiene valor de insert en la tabla vn.routeLog
|
||
|
|
|
||
|
|
ALTER TABLE `vn`.`absenceType`
|
||
|
|
ADD COLUMN `isNaturalDay` TINYINT(1) NULL DEFAULT 0 COMMENT 'Para el cálculo de los salarios de los repartidores' AFTER `holidayEntitlementRate`,
|
||
|
|
ADD COLUMN `isCalculate` TINYINT(1) NULL DEFAULT 0 COMMENT 'Para el cálculo de los salarios de los repartidores' AFTER `isNaturalDay`;
|
||
|
|
|
||
|
|
UPDATE `vn`.`absenceType` SET `isNaturalDay` = '1' WHERE (`id` = '1');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isNaturalDay` = '1' WHERE (`id` = '6');
|
||
|
|
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '1' WHERE (`id` = '1');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '1' WHERE (`id` = '2');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '1' WHERE (`id` = '6');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '0' WHERE (`id` = '7');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '1' WHERE (`id` = '10');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '1' WHERE (`id` = '11');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '0' WHERE (`id` = '12');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '0' WHERE (`id` = '14');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '1' WHERE (`id` = '15');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '0' WHERE (`id` = '16');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '0' WHERE (`id` = '17');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '0' WHERE (`id` = '13');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '0' WHERE (`id` = '22');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '0' WHERE (`id` = '21');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '0' WHERE (`id` = '20');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '0' WHERE (`id` = '19');
|
||
|
|
UPDATE `vn`.`absenceType` SET `isCalculate` = '0' WHERE (`id` = '18');
|
||
|
|
|
||
|
|
DROP PROCEDURE IF EXISTS `vn`.`route_calcCommission`;
|
||
|
|
DELIMITER $$
|
||
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `vn`.`route_calcCommission`(vSelf INT)
|
||
|
|
proc: BEGIN
|
||
|
|
/**
|
||
|
|
* Calcula las comisión para una ruta y guarda los valores en la tabla routeCommission
|
||
|
|
*
|
||
|
|
* @param vSelf id de ruta
|
||
|
|
*/
|
||
|
|
DECLARE vIsUpdatable BOOL;
|
||
|
|
DECLARE vIsFreelance BOOL;
|
||
|
|
|
||
|
|
SELECT r.created >= rc.cutoffDated INTO vIsUpdatable
|
||
|
|
FROM route r
|
||
|
|
JOIN routeConfig rc
|
||
|
|
WHERE r.id = vSelf;
|
||
|
|
|
||
|
|
IF vIsUpdatable THEN
|
||
|
|
|
||
|
|
DELETE FROM routeCommission
|
||
|
|
WHERE routeFk = vSelf;
|
||
|
|
|
||
|
|
SELECT wl.workerFk IS NULL INTO vIsFreelance
|
||
|
|
FROM route r
|
||
|
|
LEFT JOIN workerLabour wl ON wl.workerFk = r.workerFk
|
||
|
|
WHERE r.id = vSelf
|
||
|
|
LIMIT 1;
|
||
|
|
|
||
|
|
IF vIsFreelance THEN
|
||
|
|
INSERT INTO routeCommission (routeFk, workCenterFk, freelanceYearlyM3)
|
||
|
|
SELECT vSelf,
|
||
|
|
r.commissionWorkCenterFk,
|
||
|
|
rc.freelanceM3 * IF(IFNULL(r.m3, 0) >= rc.freelanceMinM3, IFNULL(r.m3, 0), 0)
|
||
|
|
FROM route r
|
||
|
|
JOIN vehicle v ON v.id = r.vehicleFk
|
||
|
|
JOIN routeConfig rc
|
||
|
|
WHERE r.id = vSelf
|
||
|
|
AND r.workerFk
|
||
|
|
AND r.commissionWorkCenterFk
|
||
|
|
AND r.m3;
|
||
|
|
ELSE
|
||
|
|
INSERT INTO routeCommission (routeFk, workCenterFk, km, m3, yearlyKm, yearlyM3, cat4m3, cat5m3)
|
||
|
|
SELECT vSelf,
|
||
|
|
r.commissionWorkCenterFk,
|
||
|
|
FORMAT((r.kmEnd - r.kmStart) * IF(v.isKmTruckRate, rc.kmHeavy, rc.kmLight), 2),
|
||
|
|
FORMAT(IFNULL(r.m3, 0) * IF(v.isKmTruckRate, rc.deliveryM3Cat5, rc.deliveryM3Cat4), 2),
|
||
|
|
FORMAT((r.kmEnd - r.kmStart) * rc.kmYearly, 2),
|
||
|
|
FORMAT(IFNULL(r.m3, 0) * rc.m3Yearly, 2),
|
||
|
|
FORMAT(distributionCat4M3 * IFNULL(r.m3, 0), 2),
|
||
|
|
FORMAT(rc.distributionCat5M3 * IFNULL(r.m3, 0), 2)
|
||
|
|
FROM route r
|
||
|
|
JOIN vehicle v ON v.id = r.vehicleFk
|
||
|
|
JOIN routeConfig rc
|
||
|
|
WHERE r.id = vSelf
|
||
|
|
AND r.kmStart
|
||
|
|
AND r.kmEnd
|
||
|
|
AND r.workerFk
|
||
|
|
AND r.commissionWorkCenterFk
|
||
|
|
AND r.m3;
|
||
|
|
END IF;
|
||
|
|
END IF;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|
||
|
|
|
||
|
|
DROP PROCEDURE IF EXISTS `vn`.`routeCommission_loadData`;
|
||
|
|
|
||
|
|
DELIMITER $$
|
||
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `vn`.`routeCommission_loadData`()
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Procedimiento temporal para realizar primera carga de datos de la comisiones de las rutas
|
||
|
|
*/
|
||
|
|
DECLARE vDone INT DEFAULT FALSE;
|
||
|
|
DECLARE vRouteFk INT;
|
||
|
|
DECLARE vCursor CURSOR FOR
|
||
|
|
SELECT r.id
|
||
|
|
FROM route r
|
||
|
|
JOIN routeConfig rc
|
||
|
|
WHERE r.created BETWEEN rc.cutoffDated AND util.CURDATE()
|
||
|
|
AND r.kmEnd - r.kmStart <= rc.kmMax;
|
||
|
|
|
||
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;
|
||
|
|
|
||
|
|
OPEN vCursor;
|
||
|
|
|
||
|
|
read_loop: LOOP
|
||
|
|
|
||
|
|
FETCH vCursor INTO vRouteFk;
|
||
|
|
|
||
|
|
IF vDone THEN
|
||
|
|
LEAVE read_loop;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
CALL route_calcCommission(vRouteFk);
|
||
|
|
|
||
|
|
END LOOP;
|
||
|
|
|
||
|
|
CLOSE vCursor;
|
||
|
|
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|
||
|
|
|
||
|
|
CALL vn.routeCommission_loadData();
|
||
|
|
|
||
|
|
DROP PROCEDURE `vn`.`routeCommission_loadData`;
|