CREATE TEMPORARY TABLE tmp_ProcessParts (
  `partId` INT NOT NULL,
  `departmentId` INT NOT NULL,
  `processId` INT NOT NULL,
  `departmentName` varchar(255) NOT NULL,
  `subDepartmentName` varchar(255) NOT NULL,
  `processName` varchar(255) NOT NULL
);

LOAD DATA LOCAL INFILE '/home/cfis/t150/02-details-process.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/02-details-process.csv'
REPLACE INTO TABLE `tmp_ProcessParts`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

TRUNCATE TABLE cf_Part2Process;

INSERT INTO cf_Part2Process (partId, processId)
SELECT p2.id, p.id
FROM tmp_ProcessParts pp
    JOIN cf_Process p ON p.name = pp.processName
    JOIN cf_Department d ON d.name = IF(pp.subDepartmentName = 'none', pp.departmentName, pp.subDepartmentName) AND p.departmentId = d.id
    JOIN cf_Part p2 ON p2.oldPartId = pp.partId
ORDER BY p2.id, d.id, p.id;


CREATE TEMPORARY TABLE tmp_ProcessData (
  `partId` INT NOT NULL,
  `partNumber` varchar(255) NOT NULL,
  `departmentName` varchar(255) NOT NULL,
  `subDepartmentName` varchar(255) NOT NULL,
  `processName` varchar(255) NOT NULL,
  `controlId` INT NOT NULL,
  `pageNum` INT NOT NULL,
  `departmentId` INT NOT NULL,
  `departmentCaption` varchar(255) NOT NULL,
  `processId` INT NOT NULL,
  `processCaption` varchar(255) NOT NULL,
  `layoutId` INT NOT NULL,
  `typeOldId` INT NOT NULL,
  `typeId` INT NOT NULL,
  `width` INT NOT NULL,
  `height` INT NOT NULL,
  `fieldName` VARCHAR(255) NOT NULL,
  `resultValue` text,
  `fileSize` INT NOT NULL
);

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-0.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-0.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-1.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-1.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-2.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-2.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-3.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-3.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-4.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-4.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-5.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-5.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-6.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-6.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-7.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-7.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-8.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-8.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-9.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-9.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-10.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-10.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/cfis/t150/04-formdata-901.csv'
#LOAD DATA LOCAL INFILE '/home/borey/public_html/carleyfoundry/trunk/import/t150/04-formdata-901.csv'
REPLACE INTO TABLE `tmp_ProcessData`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

CREATE TEMPORARY TABLE tmpMediaProcesses
SELECT p.id, p.name, MAX(IF(( `resultValue` LIKE '%.jpg%' OR `resultValue` = ''), 0, 1)) AS 'pregJpg', tpd.resultValue
    FROM cf_Process p
JOIN tmp_ProcessData tpd ON p.name = tpd.processName
GROUP BY id
HAVING `pregJpg` = 0;

UPDATE cf_Process p
JOIN tmpMediaProcesses tmp ON p.id = tmp.id
SET p.isMedia = 'y' ;

TRUNCATE TABLE cf_Media;

INSERT INTO cf_Media (`processId`, `partId`, `revisionStart`, `revisionEnd`, `title`, `description`, `type`, `image`, `video`, `sorter`, `added`, `changed`)
SELECT p.id, p2.id, 0, NULL, '', '', 'image', pd.resultValue, '', pd.pageNum, NOW(), NOW()
FROM tmp_ProcessData pd
    JOIN cf_Process p ON p.name = pd.processName AND p.isMedia = 'y'
    JOIN cf_Department d ON d.name = IF(pd.subDepartmentName = 'none', pd.departmentName, pd.subDepartmentName) AND p.departmentId = d.id
    JOIN cf_Part p2 ON p2.oldPartId = pd.partId
    JOIN cf_PartVersion pv ON pv.id = p2.currentVersion
WHERE pd.resultValue != '';

TRUNCATE TABLE cf_Part2ProcessData;
TRUNCATE TABLE cf_FormSubmissionValue;

#ALTER TABLE `cf_Part2ProcessData` ADD INDEX ( `processId` , `versionId` , `setId` ) ;

CREATE TEMPORARY TABLE tmpMultyProcessTable
SELECT processName, partId, COUNT(pageNum) AS totalSets
FROM `tmp_ProcessData`
WHERE pageNum <> -1
GROUP BY processName, partId;

UPDATE cf_Process p
JOIN tmpMultyProcessTable tmpt ON p.name = tmpt.processName
SET p.isMultiply = 'y'
WHERE tmpt.totalSets > 1;

INSERT INTO cf_Part2ProcessData (`processId`, `versionId`, `setId`, `added`, `changed`)
SELECT DISTINCT p.id, pv.id, IF(p.isMultiply = 'y', IF(pageNum = -1, NULL, pageNum), NULL), NOW(), NOW()
FROM tmp_ProcessData pd
    JOIN cf_Process p ON p.name = pd.processName AND p.isMedia = 'n'
    JOIN cf_Department d ON d.name = IF(pd.subDepartmentName = 'none', pd.departmentName, pd.subDepartmentName) AND p.departmentId = d.id
    JOIN cf_Part p2 ON p2.oldPartId = pd.partId
    JOIN cf_PartVersion pv ON pv.id = p2.currentVersion;

INSERT INTO cf_FormSubmissionValue (submissionId, elementId, `value`)
SELECT p2pd.id, fe.id, CONCAT('"', REPLACE(pd.resultValue, '"', '\\\"'), '"')
FROM tmp_ProcessData pd
    JOIN cf_Process p ON p.name = pd.processName
    JOIN cf_Department d ON d.name = IF(pd.subDepartmentName = 'none', pd.departmentName, pd.subDepartmentName) AND p.departmentId = d.id
    JOIN cf_Part p2 ON p2.oldPartId = pd.partId
    JOIN cf_PartVersion pv ON pv.id = p2.currentVersion
    JOIN cf_Part2ProcessData p2pd ON p2pd.processId = p.id AND p2pd.versionId = pv.id AND (p2pd.setId = pageNum OR ((pageNum = -1 OR pageNum = 1) AND p2pd.setId IS NULL))
    JOIN cf_FormElement fe ON fe.formId = p.id AND fe.controlId = pd.controlId;

INSERT INTO cf_zz_File (`name`, nameFs, `type`, `size`, added)
SELECT resultValue, resultValue, 'image/jpeg', fileSize, NOW()
FROM tmp_ProcessData
WHERE fileSize > 0;

CREATE TEMPORARY TABLE tmp_ElementsName (
  `elementId` INT NOT NULL,
  `name` varchar(255) NOT NULL
);

INSERT INTO tmp_ElementsName (`elementId`, `name`)
SELECT fe.id, pd.fieldName
FROM tmp_ProcessData pd
    JOIN cf_Process p ON p.name = pd.processName
    JOIN cf_Department d ON d.name = IF(pd.subDepartmentName = 'none', pd.departmentName, pd.subDepartmentName) AND p.departmentId = d.id
    JOIN cf_FormElement fe ON fe.formId = p.id AND fe.controlId = pd.controlId
    JOIN cf_FormElementPropertyValue fepv ON fepv.elementId = fe.id AND fepv.propertyId = 8
WHERE pd.fieldName != ''
GROUP BY CONCAT(p.id, pd.fieldName);

UPDATE `tmp_ElementsName` AS ten
    JOIN cf_FormElementPropertyValue fepv ON fepv.elementId = ten.elementId
SET fepv.value = ten.name
WHERE fepv.propertyId = 8;

CREATE TEMPORARY TABLE tmpMultyProcess
SELECT processId, versionId, COUNT(setId) totalSets
FROM `cf_Part2ProcessData`
WHERE setId IS NOT NULL
GROUP BY processId, versionId
HAVING totalSets > 1;

UPDATE cf_Process p
    JOIN tmpMultyProcess mp ON p.id = mp.processId
SET isMultiply = 'y';

UPDATE cf_Part2ProcessData p2dd
    JOIN cf_Process p ON p.id = p2dd.processId
SET setId = NULL
WHERE p.isMultiply = 'n';

CREATE TEMPORARY TABLE tmpP2dd
SELECT p2dd.id, p2dd.processId, p2dd.versionId, p2dd.setId
FROM `cf_Part2ProcessData` p2dd
JOIN cf_Part2ProcessData p2dd2 ON p2dd2.processId = p2dd.processId AND p2dd2.versionId = p2dd.versionId;

CREATE TEMPORARY TABLE tmpNewSet (
  `id` INT NOT NULL,
  `versionId` INT NOT NULL,
  `newSubmissionId` INT NOT NULL
);

INSERT INTO tmpNewSet (id, versionId, newSubmissionId)
SELECT p.id, p2dd.versionId, p2dd.id
FROM cf_Part2ProcessData p2dd
    JOIN cf_Process p ON p.id = p2dd.processId
    JOIN tmpP2dd p2dd2 ON p2dd2.id = p2dd.id
WHERE p.isMultiply = 'y' AND p2dd.setId IS NULL ;

INSERT INTO cf_FormSubmissionValue (submissionId, elementId, `value`)
SELECT p2pd.id as sumbissionId, fsv.elementId, fsv.value
FROM tmpNewSet AS tns
JOIN cf_Part2ProcessData p2pd ON p2pd.processId = tns.id AND p2pd.versionId = tns.versionId
JOIN cf_FormSubmissionValue fsv ON tns.newSubmissionId = fsv.submissionId
WHERE tns.newSubmissionId != p2pd.id
GROUP BY CONCAT(sumbissionId, fsv.elementId);

CREATE TEMPORARY TABLE tmpDubleValue
SELECT p2pd.*, p2pdd.id as newSubmissionId
FROM cf_Part2ProcessData p2pd
JOIN cf_Part2ProcessData p2pdd ON p2pd.processId = p2pdd.processId
AND p2pd.versionId = p2pdd.versionId
WHERE p2pd.processId =59
AND p2pd.versionId =1874
AND p2pd.setId IS NULL
AND p2pdd.setId IS NULL
GROUP BY p2pd.id;

INSERT INTO cf_FormSubmissionValue (submissionId, elementId, `value`)
SELECT tdv.newSubmissionId AS submissionId, fesv.elementId, fesv.value
FROM tmpDubleValue tdv
    JOIN cf_FormSubmissionValue fesv ON fesv.submissionId = tdv.id
WHERE tdv.id != tdv.newSubmissionId;

UPDATE cf_FormSubmissionValue fsv
    JOIN cf_FormElement fe ON fe.id = fsv.elementId
SET fsv.value = IF(fsv.value = '"1"', '"y"', '"n"')
WHERE fe.typeId = 5;

UPDATE `cf_FormSubmissionValue` SET `value` = REPLACE(`value`, "\r", "") WHERE `value` LIKE "%\r%" ;
UPDATE `cf_FormSubmissionValue` SET `value` = REPLACE(`value`, "\n", "\\n") WHERE `value` LIKE "%\n%" ;

INSERT INTO cf_FormElementPropertyValue (elementId, propertyId, value)
SELECT fe.id, 2, (CHAR_LENGTH(fepv.value)*7)
FROM cf_FormElement fe
    LEFT JOIN cf_FormElementPropertyValue fepv ON fepv.elementId = fe.id AND fepv.propertyId = 1
    LEFT JOIN cf_FormElementPropertyValue fepvw ON fepvw.elementId = fe.id AND fepvw.propertyId = 2
WHERE fe.typeId = 5 AND fepvw.value IS NULL ;

INSERT INTO cf_FormElementPropertyValue (elementId, propertyId, value)
SELECT fe.id, 11, 'right'
FROM cf_FormElement fe
WHERE fe.typeId = 5;

UPDATE cf_FormElement fe
    LEFT JOIN cf_FormElementPropertyValue fepvw ON fepvw.elementId = fe.id AND fepvw.propertyId = 12
SET fepvw.value = fepvw.value-6
WHERE fe.typeId = 1;

-- UPDATE cf_Part2ProcessData p2dd
--     JOIN cf_Process p ON p.id = p2dd.processId
-- SET setId = 0
-- WHERE p.isMultiply = 'y' AND p2dd.setId IS NULL ;

CREATE TEMPORARY TABLE tmpNotNullSet
SELECT `p2pd`.`id`
FROM `cf_Part2ProcessData` `p2pd`
INNER JOIN `cf_Process` `p` ON `p2pd`.`processId`=`p`.`id`
WHERE `p`.`isMultiply` = "y" AND `p2pd`.`setId` IS NULL
GROUP BY `processId`, `versionId`
HAVING COUNT(*) = 1 ;

UPDATE `cf_Part2ProcessData` `p2pd`
    INNER JOIN `tmpNotNullSet` `nns` ON `p2pd`.`id` = `nns`.`id`
SET `p2pd`.`setId` = 1;

UPDATE `cf_FormSubmissionValue` SET `value`=REPLACE(`value`, '	', '    ') ;
UPDATE `cf_Process` SET `height` = `height` + 10 WHERE 1;
UPDATE `cf_FormElementPropertyValue` SET `value` = `value` - 1 WHERE `cf_FormElementPropertyValue`.`propertyId` = 7;

ALTER TABLE `cf_Part` DROP `oldPartId`;
ALTER TABLE `cf_FormElement` DROP `controlId`;
ALTER TABLE `cf_FormElement` DROP INDEX `tmpFormId`;