<?php
/**
 * Created by PhpStorm.
 * User: mike
 * Date: 17/12/14
 * Time: 10:25
 */

// Error log
ini_set('display_errors', 1);
error_reporting(E_ALL ^ E_WARNING);

// Hosting data
//$myHost = 'YDAWBH119';
//$myDb = 'spire2_kms';
//$myUser = 'MikeVanDerSanden';
//$myPass = 'Oh&d6vX3#d*ikS!';
$myHost = 'localhost';
$myDb = 'spirecorp';
$myUser = 'spirecorpuser';
$myPass = 'Qfn7Hgxx77';

// on / off switch
$on = false;

$table = 'tabletpc';
$label = 'tablet pc';


if($on) {

// Make a database connection
    $myPdo = new PDO('mysql:host=' . $myHost . ';dbname=' . $myDb, $myUser, $myPass);



// Query all items
    $query = 'SELECT DISTINCT item.id, item.x2, item.title, item.productCode
          FROM  page_' . $table . '_items as item';

// Loop through database image-rows
// Save results in data[]
    $items = array();
    if ($stmt = $myPdo->prepare($query)) {
        if ($stmt->execute()) {
            if ($stmt->rowCount() > 0) {
                while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
                    $items[] = $result;
                }
            }
        }
    }

// Add images to array
    foreach ($items as $key => $item) {
        // Add images
        $query = 'SELECT DISTINCT img.id, img.filename_original, img.filename_thumb, img.kms_thumb
              FROM page_' . $table . '_images as img
              WHERE img.itemId = :itemId';

        $items[$key]['images'] = array();
        if ($stmt = $myPdo->prepare($query)) {
            $param = array('itemId' => $item['id']);

            if ($stmt->execute($param)) {
                if ($stmt->rowCount() > 0) {
                    while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
                        $items[$key]['images'][] = $result;
                    }
                }
            }
        }
    }

// Loop through all items
    foreach ($items as $item) {
        foreach ($item['images'] as $imgKey => $image) {
            $catName = $label; // f.e. PC Accessories
            $prodTitle = $item['title']; // f.e. Cassi 323
            $prodCode = $item['productCode']; // f.e. SP323-AL-WT

            // Manipulate data for fileName
            $catName = encodeFilename($catName);
            $prodTitle = encodeFilename($prodTitle);
            $prodCode = encodeFilename($prodCode);
            isset($item['x2']) && $item['x2'] == 1 ? $company = 'x2products' : $company = 'spire';

            $dbName = $company . '_' . $catName . '_' . $prodTitle . '_' . $prodCode;
            if (strlen($dbName) > 200) $dbName = substr($dbName, 0, 200);

            // Get file extension
            $temp = explode('.', $image['filename_original']);
            $fileExt = strtolower($temp[count($temp) - 1]);

            // Define new names
            $shortCode = $item['id'] . $imgKey . time(); // "key" is num image uploaded + timestamp
            $largeName = $dbName . '_' . $shortCode . '.' . $fileExt;
            $thumbName = $dbName . '_' . $shortCode . '_thumb.' . $fileExt;
            $kmsthumbName = $dbName . '_' . $shortCode . '_kmsthumb.' . $fileExt;

            // Rename the file
            $uploadsDir = $_SERVER['DOCUMENT_ROOT'] . '/kms/images/uploads/';
            if (rename_if_free($uploadsDir . $image['filename_original'], $uploadsDir . $largeName)) {
                // Update large name
                $updateQuery = 'UPDATE page_' . $table . '_images
                        SET filename_original = "' . $largeName . '"
                        WHERE id = :imageId LIMIT 1';
                if ($stmt = $myPdo->prepare($updateQuery)) {
                    $param = array('imageId' => $image['id']);
                    if (!$stmt->execute($param)) {
                        print_r($stmt->errorInfo());
                    }
                }
                echo $image['filename_original'] . ' <strong>renamed to</strong> ' . $largeName . '!<br />';
            }


            if (rename_if_free($uploadsDir . $image['filename_thumb'], $uploadsDir . $thumbName)) {
                // Update thumb name
                $updateQuery = 'UPDATE page_' . $table . '_images
                        SET filename_thumb = "' . $thumbName . '"
                        WHERE id = :imageId LIMIT 1';
                if ($stmt = $myPdo->prepare($updateQuery)) {
                    $param = array('imageId' => $image['id']);
                    if (!$stmt->execute($param)) {
                        print_r($stmt->errorInfo());
                    }
                }
                echo $image['filename_thumb'] . ' <strong>renamed to</strong> ' . $thumbName . '!<br />';
            }

            if (rename_if_free($uploadsDir . $image['kms_thumb'], $uploadsDir . $kmsthumbName)) {
                // Update kms thumb
                $updateQuery = 'UPDATE page_' . $table . '_images
                        SET kms_thumb = "' . $kmsthumbName . '"
                        WHERE id = :imageId LIMIT 1';
                if ($stmt = $myPdo->prepare($updateQuery)) {
                    $param = array('imageId' => $image['id']);
                    if (!$stmt->execute($param)) {
                        print_r($stmt->errorInfo());
                    }
                }
                echo $image['kms_thumb'] . ' <strong>renamed to</strong> ' . $kmsthumbName . '!<br />';
            }

            // Update shortcode
            $updateQuery = 'UPDATE page_' . $table . '_images
                        SET shortcode = "' . $shortCode . '"
                        WHERE id = :imageId LIMIT 1';
            if ($stmt = $myPdo->prepare($updateQuery)) {
                $param = array('imageId' => $image['id']);
                if (!$stmt->execute($param)) {
                    print_r($stmt->errorInfo());
                }
            }
        }
    }
}
else
{
    echo 'conversion is turned off..';
}




/*
 * Encode function
 */
function encodeFilename($input)
{
    // Remove whitespace
    $output = trim($input);

    $output = htmlentities($output, ENT_NOQUOTES, 'utf-8');

    // Lowercase
    $output = strtolower($output);

    // Replace &amp; with "_"
    $output = str_replace('&amp;', '_', $output);

    // Replace special letters with normal letters
    $output = preg_replace( "`&([a-z])(acute|uml|circ|grave|ring|cedil|slash|tilde|caron|lig|quot|rsquo);`i","\\1", $output );

    $output = str_replace(':','_',$output);

    // Remove html entities
    $pattern = '#(&)([a-z]*)([;$])#';
    $output = preg_replace($pattern,'',$output);

    // Remove all special characters
    $output = preg_replace('/[^a-zA-Z0-9_ %\[\]\.\(\)%&-]/s', '', $output);

    // Remove ( )
    $output = str_replace('(','',$output);
    $output = str_replace(')','',$output);
    $output = str_replace('.','',$output);

    // Replace spaces with a underscore
    $output = str_replace(' ','_',$output);

    // Replace multiple underscores with one underscore
    $output = preg_replace('/_{2,}/','_',$output);

    return $output;
}

function rename_if_free($oldPath,$newPath) {
    if ( ! file_exists($newPath))
    {
        if(rename($oldPath, $newPath)) return true;
    }
    return false;
}