CREATE TEMPORARY TABLE tmp_Process (
  `departmentName` varchar(255) NOT NULL,
  `subDepartmentName` varchar(255) NOT NULL,
  `processName` varchar(255) NOT NULL,
  `departmentId` INT NOT NULL,
  `processId` INT NOT NULL,
  `layoutId` INT NOT NULL,
  `isMedia` char(1) NOT NULL,
  `height` INT NOT NULL
);

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

TRUNCATE TABLE cf_Department;
TRUNCATE TABLE cf_Process;
TRUNCATE TABLE cf_DepartmentAndProcessSorter;
TRUNCATE TABLE cf_FormElement;
TRUNCATE TABLE cf_FormElementPropertyValue;

INSERT INTO cf_Department (parentId, `name`, `added`, `changed`)
SELECT DISTINCT 0, departmentName, NOW(), NOW()
FROM tmp_Process
WHERE departmentName != '' AND departmentName != 'none';

UPDATE cf_Department SET sorter = id;

#ALTER TABLE `cf_Department` ADD INDEX ( `name` );

CREATE TEMPORARY TABLE tmp_SubDepartment
SELECT DISTINCT d.id, p.subDepartmentName
FROM cf_Department d
    JOIN tmp_Process p ON p.departmentName = d.name
WHERE subDepartmentName != 'none' AND subDepartmentName != '';

INSERT INTO cf_Department (parentId, `name`, `added`, `changed`)
SELECT id, subDepartmentName, NOW(), NOW()
FROM tmp_SubDepartment;

INSERT INTO cf_Process (departmentId, `name`, isMedia, height, `new`, added, `changed`)
SELECT d.id, processName, isMedia, MAX(height), 'n', NOW(), NOW()
    FROM tmp_Process p
    JOIN cf_Department d ON d.name = IF(p.subDepartmentName = 'none', p.departmentName, p.subDepartmentName)
WHERE processName != '' AND processName != 'none'
GROUP BY 1, 2;

SET @sorter = 0;
CREATE TABLE tmp_DepartmentProcessSorter
SELECT IFNULL(d.id, sd.id) AS depId, sd.id AS subDepId, p.id AS processId, @sorter:= @sorter + 1 AS depSorter,
    @sorter:= @sorter + 1 AS subDepSorter, @sorter:= @sorter + 1 AS processSorter
FROM cf_Process p
    JOIN cf_Department sd ON p.departmentId = sd.id
    LEFT JOIN cf_Department d ON sd.parentId = d.id;

INSERT INTO cf_DepartmentAndProcessSorter
SELECT DISTINCT NULL, depId, NULL, MIN(depSorter) AS sorter FROM tmp_DepartmentProcessSorter
  WHERE depId = subDepId GROUP BY subDepId
UNION ALL SELECT DISTINCT NULL, subDepId, NULL, MIN(subDepSorter) AS sorter FROM tmp_DepartmentProcessSorter
  WHERE depId != subDepId GROUP BY subDepId
UNION ALL SELECT NULL, NULL, processId, processSorter AS sorter FROM tmp_DepartmentProcessSorter
  GROUP BY processId
ORDER BY sorter ASC;

UPDATE cf_DepartmentAndProcessSorter SET sorter = id;

DROP TABLE tmp_DepartmentProcessSorter;

CREATE TEMPORARY TABLE tmp_ProcessElements (
  `departmentName` varchar(255) NOT NULL,
  `subDepartmentName` varchar(255) NOT NULL,
  `processName` varchar(255) NOT NULL,
  `controlId` INT NOT NULL,
  `departmentId` INT NOT NULL,
  `processId` INT NOT NULL,
  `layoutId` INT NOT NULL,
  `typeOldId` INT NOT NULL,
  `typeId` INT NOT NULL,
  `left` INT NOT NULL,
  `top` INT NOT NULL,
  `width` INT NOT NULL,
  `height` INT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `defaultValue` VARCHAR(255) NOT NULL,
  `defaultValuesSize` INT NOT NULL,
  `zIndex` INT NOT NULL,
  `maxLength` VARCHAR(255) NOT NULL,
  `fontName` VARCHAR(255) NOT NULL,
  `fontSize` INT NOT NULL
);

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

UPDATE tmp_ProcessElements pe
    JOIN cf_Process p ON p.name = pe.processName
SET p.sidebar = CONCAT(`defaultValue`, '.jpg')
WHERE typeId = 7 AND defaultValue LIKE '%.bmp';

DELETE FROM tmp_ProcessElements WHERE typeId = 0 OR processName = 'none' OR processName = '';

ALTER TABLE tmp_ProcessElements ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

ALTER TABLE `cf_FormElement` ADD `controlId` INT NULL DEFAULT NULL ;

ALTER TABLE `cf_FormElement` ADD INDEX `tmpFormId` ( `formId` , `controlId` ) ;

ALTER TABLE `cf_Process` ADD INDEX ( `name` ) ;

INSERT INTO cf_FormElement (id, formId, typeId, `left`, top, zIndex, added, changed, controlId)
SELECT pe.id, p.id, `typeId`, `left`, `top`, `zIndex`, NOW(), NOW(), controlId
FROM tmp_ProcessElements pe
    JOIN cf_Process p ON p.name = pe.processName
    JOIN cf_Department d ON d.name = IF(pe.subDepartmentName = 'none', pe.departmentName, pe.subDepartmentName) AND p.departmentId = d.id
WHERE typeId > 0 AND pe.processName != 'none' AND pe.processName != '';

CREATE TEMPORARY TABLE tmp_ProcessElementsWidthHeight (
  `typeId` INT NOT NULL PRIMARY KEY,
  `widthPropertyId` INT NULL,
  `heightPropertyId` INT NULL,
  `name` VARCHAR(255) NULL
);

INSERT INTO tmp_ProcessElementsWidthHeight VALUES
(1, 12, NULL, 'input'), (2, 13, 14, 'textarea'), (4, 15, NULL, 'label'), (5, NULL, NULL, 'checkbox'),
(6, 24, NULL, 'select'), (7, 25, 26, 'file'), (8, 27, NULL, NULL), (9, 38, NULL, NULL);

INSERT INTO `cf_FormElementPropertyValue` (`elementId`, `propertyId`, `value`)
SELECT pe.id, widthPropertyId, `width`
FROM tmp_ProcessElements pe
    JOIN tmp_ProcessElementsWidthHeight pewh ON pe.typeId = pewh.typeId
WHERE widthPropertyId IS NOT NULL;

INSERT INTO `cf_FormElementPropertyValue` (`elementId`, `propertyId`, `value`)
SELECT pe.id, heightPropertyId, `height`
FROM tmp_ProcessElements pe
    JOIN tmp_ProcessElementsWidthHeight pewh ON pe.typeId = pewh.typeId
WHERE heightPropertyId IS NOT NULL;

INSERT INTO `cf_FormElementPropertyValue` (`elementId`, `propertyId`, `value`)
SELECT pe.id, 8, CONCAT(pewh.name, pe.id)
FROM tmp_ProcessElements pe
    JOIN tmp_ProcessElementsWidthHeight pewh ON pe.typeId = pewh.typeId
WHERE pewh.name IS NOT NULL;

INSERT INTO `cf_FormElementPropertyValue` (`elementId`, `propertyId`, `value`)
SELECT pe.id, 1, title
FROM tmp_ProcessElements pe
WHERE title != '';

INSERT INTO `cf_FormElementPropertyValue` (`elementId`, `propertyId`, `value`)
SELECT pe.id, 4, `fontName`
FROM tmp_ProcessElements pe
WHERE `fontName` != '';

INSERT INTO `cf_FormElementPropertyValue` (`elementId`, `propertyId`, `value`)
SELECT pe.id, 7, ROUND(`fontSize`*1.4)
FROM tmp_ProcessElements pe
WHERE `fontSize` != '';

UPDATE cf_FormElementPropertyValue fepv
    JOIN cf_FormElementPropertyValue fepvh ON fepvh.elementId = fepv.elementId AND fepvh.propertyId = 14 AND fepvh.value = -1
    JOIN cf_FormElement fe ON fepv.elementId = fe.id
    JOIN cf_Process p ON fe.formId = p.id
SET fepv.value = 600, fepvh.value = IF(p.height != 0, p.height, 300), p.height = IF(p.height != 0, p.height, 300)
WHERE fepv.propertyId = 13 AND fepv.value = -1 ;

CREATE TEMPORARY TABLE tmpElementNewName
SELECT pe.id, REPLACE(pe.description, ' ', '_') AS name
FROM tmp_ProcessElements pe
    JOIN tmp_ProcessElementsWidthHeight pewh ON pe.typeId = pewh.typeId
    JOIN cf_FormElementPropertyValue fepv ON pe.id = fepv.elementId
    JOIN cf_Process p ON p.name = pe.processName
WHERE pewh.name IS NOT NULL AND pe.description != '' AND fepv.propertyId = 8
GROUP BY CONCAT(p.id, REPLACE(pe.description, ' ', '_'));

UPDATE tmpElementNewName enn
    JOIN cf_FormElementPropertyValue fepv ON enn.id = fepv.elementId
SET fepv.value = enn.name
WHERE fepv.propertyId = 8;