<?php
function vdie()
{
    header('Content-Type: text/html; charset=utf-8');
    $backtrace = debug_backtrace();
    ob_start();
    echo $backtrace[0]['file'], ' (', $backtrace[0]['line'], '):<br />';
    $vars = func_get_args();
    foreach ($vars as $var) {
        echo '<pre>';
        if (is_scalar($var) || is_null($var)) {
            var_dump($var);
        } else {
            print_r($var);
        }
        echo '</pre>';
    }
    echo '<hr />BACKTRACE<hr />';
    array_shift($backtrace);

    function _removeObjects(&$array)
    {
        foreach ($array as $key => &$value) {
            if (is_array($value)) {
                _removeObjects($value);
            } elseif (is_object($value)) {
                unset($array[$key]);
                $array[$key] = get_class($value) . ' Object';
            }
        }
    }

    _removeObjects($backtrace);
    echo '<pre>';
    print_r($backtrace);
    echo '</pre>';
    $html = ob_get_contents();
    ob_end_clean();
    echo $html;
    exit();
}

function csv2array($file)
{
    $row = 1;
    $result = array();
    if (($handle = fopen($file, "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            if ($row != 1) {
                $result[] = array('department' => $data[4], 'subdepartment' => $data[5], 'process' => $data[6]);
            }
            $row++;
        }
        fclose($handle);
    }
    return $result;
}

function csvForm2array($file)
{
    $row = 1;
    $result = array();
    if (($handle = fopen($file, "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            if ($row != 1) {
                $alias = $data[0] . '-' . $data[1] . '-' . $data[2];
                $result[$alias] = array('alias' => $alias, 'isMedia' => $data[6], 'height' => $data[7]);
            }
            $row++;
        }
        fclose($handle);
    }
    return $result;
}

function csvFormElement2array($file)
{
    $row = 1;
    $header = false;
    $result = array();
    if (($handle = fopen($file, "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            if ($header && $data[8] != 0 && $data[2] != '' && $data[2] != 'none') {
                $result[($row + 1)] = array('alias' => $data[0] . '-' . $data[1] . '-' . $data[2], 
                    'typeId' => $data[8], 'left' => $data[9], 'top' => $data[10], 'width' => $data[11], 
                    'height' => $data[12], 'title' => $data[13], 'defaultValues' => $data[15], 'zIndex' => $data[16]);
                $row++;
            }
            $header = true;
        }
        fclose($handle);
    }
    return $result;
}

$a = csv2array('process-mapping.csv');
$processForm = csvForm2array('03-forms-process.csv');
$processFormElement = csvFormElement2array('03-forms-elements.csv');

$subdepartment = array();
$department = array();
$process = array();
$sorter = array();

$departmentSql = 'INSERT INTO `cf_Department` (`id`, `parentId`, `name`, `sorter`, `added`, `changed`) VALUES' . "\n";
$processSql = 'INSERT INTO `cf_Process` (`id`, `departmentId`, `name`, `isMedia`, `sorter`, `blockedBy`, `blockedAt`, `height`, `sidebar`, `new`, `added`, `changed`) VALUES' . "\n";
$sorterSql = 'INSERT INTO `cf_DepartmentAndProcessSorter` (`id`, `departmentId`, `processId`, `sorter`) VALUES' . "\n";
$sorter = 0;
$int = 0;

foreach($a as $row)
{
    if ($row['department'] != 'none' && $row['process'] != 'none') {
        if (!isset($department[$row['department']])) {
            $department[$row['department']] = array(
                'id' => count($department) + 1, 
                'name' => $row['department'],
                'parentId' => 0,
                'sorter' => 0,
            );
            $sorter++;
            $departmentSql .= "(" . $department[$row['department']]['id'] . ", 0, '" . $row['department'] . "', " 
                . $sorter . ", NULL, NULL),\n" ;
            $sorterSql .= "(NULL, " . $department[$row['department']]['id'] . ", NULL, " . $sorter . "),\n";
        }
        $departmentId = $department[$row['department']]['id'];
        if ($row['subdepartment'] != 'none') {
            if (!isset($department[$row['subdepartment']])) {
                $department[$row['subdepartment']] = array(
                    'id' => count($department) + 1, 
                    'name' => $row['subdepartment'],
                    'parentId' => $department[$row['department']]['id'],
                    'sorter' => 0,
                );
                $sorter++;
                $departmentSql .= "(" . $department[$row['subdepartment']]['id'] . ", " 
                    . $department[$row['department']]['id'] . ", '" . $row['subdepartment'] . "', " . $sorter 
                    . ", NULL, NULL),\n";
                $departmentId = $department[$row['subdepartment']]['id'];
                $sorterSql .= "(NULL, " . $department[$row['subdepartment']]['id'] . ", NULL, " . $sorter . "),\n";
            }
        }
        $sorter++;
        $alias = $row['department'] . '-' . $row['subdepartment'] . '-' . $row['process'];
        if (!isset($process[$alias])) {
            $process[$alias] = $processId =  count($process) + 1;
            $processSql .= "(" . $processId . ", " . $departmentId . ", '" . $row['process'] . "', 'n', 0, 0, NULL, " 
                . $processForm[$alias]['height'] . ", '', '" . $processForm[$alias]['isMedia'] . "', NULL, NULL),\n" ;
            $sorterSql .= "(NULL, NULL, " . $processId . ", " . $sorter . "),\n";
        }
    }
}

$departmentSql = substr($departmentSql, 0, -2) . ';';
$processSql = substr($processSql, 0, -2) . ';';
$sorterSql = substr($sorterSql, 0, -2) . ';';

$elementsSql = $elementsSqlInsert = "INSERT INTO `cf_FormElement` (`id`, `formId`, `formType`, `typeId`, `left`, `top`, `zIndex`, `draftFor`, `added`, `changed`) VALUES" . "\n";
$count = 0;

$names = array(
    '1' => 'input',
    '2' => 'textarea',
    '3' => 'label',
    '4' => 'select',
    '5' => 'checkbox',
    '6' => 'radio',
    '7' => 'file'
);
$width = array(
    '1' => 12,
    '2' => 13,
    '4' => 15,
    '6' => 24,
    '7' => 25,
    '8' => 27,
    '9' => 38,
);
$height = array(
    '2' => 14,
    '7' => 26
);
$property = array();
foreach($processFormElement as $elementId => $row) {
    $elementsSql .= "(" . $elementId . ", " . $process[$row['alias']] . ", 'process', " . $row['typeId'] . ", " 
        . $row['left'] . ", " . $row['top'] . ", " . $row['zIndex'] . ", NULL, NULL, NULL),\n";
    $count++;
    if ($count % 100 == 0) {
        $elementsSql = substr($elementsSql, 0, -2) . ";\n" . $elementsSqlInsert;
    }
    
    $property[] = array('elementId' => $elementId, 'propertyId' => 8, 'value' => $names[$row['typeId']] . $elementId);
    if ($row['title'] != '') {
        $property[] = array('elementId' => $elementId, 'propertyId' => 1, 
            'value' => str_replace('\'', '', $row['title']));
    }
    if (isset($width[$row['typeId']]) && (bool) $row['width']) {
        $property[] = array('elementId' => $elementId, 'propertyId' => $width[$row['typeId']], 
            'value' => $row['width']);
    }
    if (isset($height[$row['typeId']]) && (bool) $row['height']) {
        $property[] = array('elementId' => $elementId, 'propertyId' => $height[$row['typeId']], 
            'value' => $row['height']);
    }
}
$elementsSql = substr($elementsSql, 0, -2) . ';';

$propertySql = $propertySqlInsert = "INSERT INTO `cf_FormElementPropertyValue` (`elementId`, `propertyId`, `value`) VALUES" . "\n";
$propertyCount = 0;
foreach($property as $row) {
    $propertyCount++;
    $propertySql .= "(" . $row['elementId']. ", " . $row['propertyId']. ", '" . $row['value']. "'),\n";
    if ($propertyCount % 100 == 0) {
        $propertySql = substr($propertySql, 0, -2) . ";\n" . $propertySqlInsert;
    }
}
$propertySql = substr($propertySql, 0, -2) . ';';


$file = "importProcesses.sql"; 
$handle = fopen($file, 'w');
fwrite($handle, $departmentSql . "\n" . $processSql . "\n" . $sorterSql); 
fclose($handle); 
echo ('File "' . $file . '" has been created<br />');

$fileElements = "importElements.sql"; 
$handleElements = fopen($fileElements, 'w');
fwrite($handleElements, $elementsSql); 
fclose($handleElements); 
echo ('File "' . $fileElements . '" has been created<br />');

$fileProperty = "importProperty.sql"; 
$handleProperty = fopen($fileProperty, 'w');
fwrite($handleProperty, $propertySql); 
fclose($handleProperty); 
echo ('File "' . $fileProperty . '" has been created<br />');


?>