salix/db/routines/vn/procedures/sectorProductivity_add.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`sectorProductivity_add`()
BEGIN
	DECLARE vDatedFrom DATETIME;
	DECLARE vDatedTo DATETIME;

	SELECT DATE_SUB(util.VN_CURDATE(),INTERVAL 1 DAY), CONCAT(DATE_SUB(util.VN_CURDATE(),INTERVAL 1 DAY),' 23:59:59') INTO vDatedFrom,	vDatedTo;

	DROP TEMPORARY TABLE IF EXISTS tmp.timeControlCalculate;
	DROP TEMPORARY TABLE IF EXISTS tmp.errorsByChecker;
	DROP TEMPORARY TABLE IF EXISTS tmp.previousErrors;

	CALL timeControl_calculateAll(vDatedFrom, vDatedTo);

	CREATE TEMPORARY TABLE tmp.errorsByChecker
		ENGINE = MEMORY
		SELECT sc.userFk workerFk, COUNT(DISTINCT s.ticketFk) errorsByChecker
			FROM saleMistake sm
				JOIN vn.saleGroupDetail sgd on sgd.saleFk = sm.saleFk
				JOIN vn.sectorCollectionSaleGroup scsg on scsg.saleGroupFk = sgd.saleGroupFk
				JOIN vn.sectorCollection sc on sc.id = scsg.sectorCollectionFk
				JOIN sale s ON s.id = sm.saleFk
				JOIN ticket t on t.id = s.ticketFk
				WHERE (t.shipped BETWEEN vDatedFrom AND vDatedTo)
		GROUP BY sc.userFk ;

	CREATE TEMPORARY TABLE tmp.previousErrors -- Errores de previa, derivadores de los revisadores (por reclamación)
		ENGINE = MEMORY
		SELECT tt.userFk, COUNT(c.ticketFk) errorsByClaim
			FROM claimDevelopment cd
				JOIN claim c ON cd.claimFk = c.id
				JOIN ticket t ON c.ticketFk = t.id
				JOIN claimResponsible cr ON cd.claimResponsibleFk = cr.id
				JOIN ticketTracking tt ON tt.ticketFk = t.id
				JOIN `state` s ON s.id = tt.stateFk
			WHERE t.shipped BETWEEN vDatedFrom AND vDatedTo AND cr.description = 'Revisadores' AND s.code = 'OK PREVIOUS'
			GROUP BY tt.userFk;

	DELETE FROM sectorProductivity
			WHERE dated = vDatedFrom
				AND sector IN ('Algemesi Artificial','Algemesi Complementos');

	INSERT INTO sectorProductivity(workerFk, firstName, lastName, sector, ticketCount, saleCount, error, volume, hourWorked, dated)
		SELECT  w.id workerFk,
				w.firstName,
				w.lastName,
				se.description sector,
				COUNT(DISTINCT s.ticketFk) ticketCount,
				COUNT(sgd.id) saleCount,
				IFNULL(ec2.errorsByChecker,0) + IFNULL(pe.errorsByClaim, 0) errors,
				wp.volume,
				IFNULL(CAST(tc.timeWorkDecimal AS DECIMAL (10,2)) , 0) AS hourWorked,
				DATE(vDatedFrom) dated
			FROM vn.saleGroupDetail sgd
				JOIN vn.saleGroup sg on sg.id = sgd.saleGroupFk
				JOIN vn.sectorCollectionSaleGroup scsg on scsg.saleGroupFk = sgd.saleGroupFk
				JOIN vn.sectorCollection sc on sc.id = scsg.sectorCollectionFk
				join vn.sector se on se.id = sc.sectorFk
				JOIN vn.worker w ON w.id = sc.userFk
				LEFT JOIN vn.sale s ON s.id = sgd.saleFk
				LEFT JOIN tmp.timeControlCalculate tc ON tc.userFk = w.id
		 		LEFT JOIN bs.workerProductivity wp ON wp.workerFk = w.id
		 		LEFT JOIN `state` s2 ON s2.id = wp.stateFk AND s2.code = 'OK PREVIOUS'
		 		LEFT JOIN tmp.errorsByChecker ec2 ON ec2.workerFk = w.id
		 		LEFT JOIN tmp.previousErrors pe ON pe.userFk = w.id
			WHERE DATE(sc.created) = vDatedFrom
				AND wp.dated = vDatedFrom
			GROUP BY w.id;

	DROP TEMPORARY TABLE tmp.timeControlCalculate;
	DROP TEMPORARY TABLE tmp.errorsByChecker;
	DROP TEMPORARY TABLE tmp.previousErrors;
END$$
DELIMITER ;