304 lines
12 KiB
PHP
304 lines
12 KiB
PHP
<?php
|
|
|
|
class Upgrade31 extends UpgradeNew
|
|
{
|
|
public function checkRightfulness_1()
|
|
{
|
|
return findModule('templates') && $this->checkTableExists('templates');
|
|
}
|
|
|
|
/** New module 'templates' */
|
|
public function makeChanges_1()
|
|
{
|
|
sqlQuery('
|
|
CREATE TABLE IF NOT EXISTS `templates` (
|
|
`id` INT(11) NOT NULL,
|
|
`id_category` INT(11) NOT NULL,
|
|
`name` VARCHAR(50) NOT NULL,
|
|
`text` TEXT NOT NULL
|
|
) ENGINE=InnoDB AUTO_INCREMENT=12 ;
|
|
|
|
CREATE TABLE IF NOT EXISTS `templates_categories` (
|
|
`id` INT(11) NOT NULL,
|
|
`name` VARCHAR(50) NOT NULL,
|
|
`position` INT(11) NOT NULL
|
|
) ENGINE=InnoDB AUTO_INCREMENT=3 ;
|
|
|
|
CREATE TABLE IF NOT EXISTS `templates_products` (
|
|
`id_template` INT(11) NOT NULL,
|
|
`id_product` INT(11) NOT NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
ALTER TABLE `templates`
|
|
ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`), ADD KEY `id_template_category` (`id_category`);
|
|
|
|
ALTER TABLE `templates_categories`
|
|
ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`);
|
|
|
|
ALTER TABLE `templates_products`
|
|
ADD PRIMARY KEY (`id_template`,`id_product`), ADD KEY `id_product` (`id_product`);
|
|
|
|
ALTER TABLE `templates`
|
|
MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=12;
|
|
|
|
ALTER TABLE `templates_categories`
|
|
MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
|
|
|
|
ALTER TABLE `templates`
|
|
ADD CONSTRAINT `templates_ibfk_1` FOREIGN KEY (`id_category`) REFERENCES `templates_categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE `templates_products`
|
|
ADD CONSTRAINT `templates_products_ibfk_2` FOREIGN KEY (`id_product`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT `templates_products_ibfk_1` FOREIGN KEY (`id_template`) REFERENCES `templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
');
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
|
|
public function checkRightfulness_6()
|
|
{
|
|
return $this->checkConstraintExists('users', 'email');
|
|
}
|
|
|
|
/** Users - add unique to email */
|
|
public function makeChanges_6()
|
|
{
|
|
// make email case insensitive
|
|
sqlQuery('ALTER TABLE `users` CHANGE `email` `email` VARCHAR(100) NOT NULL DEFAULT ""');
|
|
|
|
// Merge users with identical email
|
|
$duplicates = sqlQuery('SELECT GROUP_CONCAT(id ORDER BY id DESC) AS ids FROM users GROUP BY email HAVING COUNT(*) > 1');
|
|
foreach ($duplicates as $duplicate) {
|
|
$ids = explode(',', $duplicate['ids']);
|
|
$master_id = array_shift($ids);
|
|
sqlQuery('UPDATE orders SET id_user = :new_id WHERE id_user IN (:old_ids)',
|
|
['old_ids' => $ids, 'new_id' => $master_id], ['old_ids' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]);
|
|
sqlQuery('DELETE FROM users WHERE id IN (:old_ids)',
|
|
['old_ids' => $ids], ['old_ids' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]);
|
|
}
|
|
|
|
sqlQuery('ALTER TABLE users ADD UNIQUE(`email`)');
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
|
|
public function checkRightfulness_7()
|
|
{
|
|
return findModule('order_payment') && $this->checkForeignKeyExists('order_payments', 'id_order');
|
|
}
|
|
|
|
/** Order Payments - add missing constraint */
|
|
public function makeChanges_7()
|
|
{
|
|
var_dump(sqlFetchAssoc(sqlQuery('SELECT COUNT(*) FROM '.getTableName('order_payments').' op LEFT JOIN '.getTableName('orders').' o ON o.id=op.id_order WHERE o.id IS NULL')));
|
|
sqlQuery('DELETE op FROM '.getTableName('order_payments').' op LEFT JOIN '.getTableName('orders').' o ON o.id=op.id_order WHERE o.id IS NULL');
|
|
|
|
sqlQuery('ALTER TABLE '.getTableName('order_payments').' ADD FOREIGN KEY (`id_order`) REFERENCES `orders`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
|
|
public function checkRightfulness_8()
|
|
{
|
|
return findModule('price_levels') && $this->checkColumnExists('price_levels', 'combine');
|
|
}
|
|
|
|
/** Price Levels - add 'discount_discount' and 'combine' */
|
|
public function makeChanges_8()
|
|
{
|
|
sqlQuery('ALTER TABLE '.getTableName('price_levels').' ADD `discount_discount` FLOAT NULL DEFAULT NULL AFTER `discount`');
|
|
|
|
sqlQuery('ALTER TABLE '.getTableName('price_levels').' ADD `combine` ENUM("Y","N") NOT NULL DEFAULT "Y"');
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
|
|
public function checkRightfulness_10()
|
|
{
|
|
return findModule('products_favorites') && $this->checkForeignKeyExists('products_favorites', 'id_user');
|
|
}
|
|
|
|
/** Products Favorites - add missing constraint */
|
|
public function makeChanges_10()
|
|
{
|
|
sqlQuery('DELETE pf FROM '.getTableName('products_favorites').' pf
|
|
LEFT JOIN '.getTableName('users').' u ON u.id=pf.id_user
|
|
LEFT JOIN '.getTableName('products').' p ON p.id=pf.id_product
|
|
WHERE u.id IS NULL OR p.id IS NULL');
|
|
|
|
sqlQuery('ALTER TABLE '.getTableName('products_favorites').' ENGINE = InnoDB');
|
|
sqlQuery('ALTER TABLE '.getTableName('products_favorites').' CHANGE `id_user` `id_user` INT(11) UNSIGNED NOT NULL DEFAULT "0"');
|
|
sqlQuery('ALTER TABLE '.getTableName('products_favorites').' ADD FOREIGN KEY (`id_user`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');
|
|
sqlQuery('ALTER TABLE '.getTableName('products_favorites').' ADD FOREIGN KEY (`id_product`) REFERENCES `products`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
|
|
public function checkRightfulness_11()
|
|
{
|
|
return findModule(\Modules::ORDER_PAYMENT) && $this->checkColumnExists('order_payments', 'admin');
|
|
}
|
|
|
|
/** POS payments - add missing columns */
|
|
public function makeChanges_11()
|
|
{
|
|
sqlQuery('ALTER TABLE '.getTableName('order_payments').' ADD `admin` INT NULL');
|
|
sqlQuery('ALTER TABLE '.getTableName('order_payments').' ADD `method` INT NOT NULL');
|
|
sqlQuery('ALTER TABLE '.getTableName('order_payments').' CHANGE `id_order` `id_order` INT(11) UNSIGNED NULL');
|
|
sqlQuery('ALTER TABLE '.getTableName('order_payments').' CHANGE `date` `date` DATETIME NOT NULL;');
|
|
sqlQuery('ALTER TABLE '.getTableName('order_payments').' ADD FOREIGN KEY (`admin`) REFERENCES `admins`(`id`) ON DELETE SET NULL ON UPDATE CASCADE');
|
|
|
|
sqlQuery('UPDATE '.getTableName('admins')." SET privilege=REPLACE(privilege, 'ORDER_PAYMENT', 'POS_ADD|POS_EDIT|POS_ERASE')");
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
|
|
public function checkRightfulness_12()
|
|
{
|
|
return $this->checkColumnExists('producers', 'position');
|
|
}
|
|
|
|
/** Producers position - for sorting producers*/
|
|
public function makeChanges_12()
|
|
{
|
|
sqlQuery('ALTER TABLE '.getTableName('producers').' ADD `position` INT(11) NULL');
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
|
|
public function checkRightfulness_13()
|
|
{
|
|
return findModule('abra') && $this->checkTableExists('abra_settings');
|
|
}
|
|
|
|
/** Abra sync - add abra sync tables */
|
|
public function makeChanges_13()
|
|
{
|
|
sqlQuery('CREATE TABLE '.getTableName('abra_products').' (
|
|
`id_abra` VARCHAR(30) NOT NULL,
|
|
`id_product` INT(11) NOT NULL
|
|
) ENGINE=InnoDB');
|
|
|
|
sqlQuery('CREATE TABLE `abra_sections` (
|
|
`id_abra` VARCHAR(30) NOT NULL,
|
|
`id_section` INT(11) NOT NULL
|
|
) ENGINE=InnoDB');
|
|
|
|
sqlQuery('CREATE TABLE `abra_settings` (
|
|
`name` VARCHAR(20) NOT NULL,
|
|
`value` VARCHAR(100) NOT NULL
|
|
) ENGINE=InnoDB');
|
|
|
|
sqlQuery('ALTER TABLE `abra_products`
|
|
ADD KEY `id_abra` (`id_abra`), ADD KEY `id_product` (`id_product`)');
|
|
|
|
sqlQuery('ALTER TABLE `abra_sections`
|
|
ADD KEY `id_abra` (`id_abra`), ADD KEY `id_section` (`id_section`)');
|
|
|
|
sqlQuery('ALTER TABLE `abra_settings`
|
|
ADD KEY `name` (`name`)');
|
|
|
|
sqlQuery('ALTER TABLE `abra_products`
|
|
ADD CONSTRAINT `abra_products_ibfk_1` FOREIGN KEY (`id_product`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE');
|
|
|
|
sqlQuery('ALTER TABLE `abra_sections`
|
|
ADD CONSTRAINT `abra_sections_ibfk_1` FOREIGN KEY (`id_section`) REFERENCES `sections` (`id`) ON DELETE CASCADE ON UPDATE CASCADE');
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
|
|
public function checkRightfulness_14()
|
|
{
|
|
return findModule('abra') && $this->checkTableExists('abra_orders');
|
|
}
|
|
|
|
/** Abra sync - add abra order sync tables */
|
|
public function makeChanges_14()
|
|
{
|
|
sqlQuery('CREATE TABLE '.getTableName('abra_orders').' (
|
|
`id_abra` VARCHAR(30) NOT NULL,
|
|
`id_order` INT(10) UNSIGNED NOT NULL
|
|
) ENGINE=InnoDB');
|
|
|
|
sqlQuery('ALTER TABLE `abra_orders`
|
|
ADD KEY `id_abra` (`id_abra`), ADD KEY `id_order` (`id_order`)');
|
|
|
|
sqlQuery('ALTER TABLE `abra_orders`
|
|
ADD CONSTRAINT `abra_orders_ibfk_1` FOREIGN KEY (`id_order`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE');
|
|
}
|
|
|
|
public function checkRightfulness_15()
|
|
{
|
|
return !$this->checkFulltextConstraintExists('articles', 'lead_in');
|
|
}
|
|
|
|
/** Articles - innodb */
|
|
public function makeChanges_15()
|
|
{
|
|
sqlQuery('ALTER TABLE '.getTableName('articles').' DROP KEY `lead_in`');
|
|
|
|
sqlQuery('ALTER TABLE '.getTableName('articles').' ENGINE = InnoDB');
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
|
|
public function checkRightfulness_16()
|
|
{
|
|
return $this->checkForeignKeyExists('photos_articles_relation', 'id_art');
|
|
}
|
|
|
|
/** Articles - add FK */
|
|
public function makeChanges_16()
|
|
{
|
|
if ($this->verbose) {
|
|
var_dump(sqlFetchAll(sqlQuery('SELECT * FROM photos_articles_relation par LEFT JOIN photos ph ON ph.id=par.id_photo WHERE ph.id IS NULL')));
|
|
var_dump(sqlFetchAll(sqlQuery('SELECT * FROM photos_articles_relation par LEFT JOIN articles a ON a.id=par.id_art WHERE a.id IS NULL')));
|
|
}
|
|
|
|
sqlQuery('DELETE par FROM photos_articles_relation par LEFT JOIN photos ph ON ph.id=par.id_photo WHERE ph.id IS NULL');
|
|
sqlQuery('DELETE par FROM photos_articles_relation par LEFT JOIN articles a ON a.id=par.id_art WHERE a.id IS NULL');
|
|
|
|
if ($this->checkForeignKeyExists('photos_articles_relation', 'id_photo')) {
|
|
sqlQuery('ALTER TABLE `photos_articles_relation`
|
|
ADD CONSTRAINT `photos_articles_relation_ibfk_1` FOREIGN KEY (`id_photo`) REFERENCES `photos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE');
|
|
}
|
|
|
|
sqlQuery("ALTER TABLE `photos_articles_relation` CHANGE `id_art` `id_art` INT(11) NOT NULL DEFAULT '0'");
|
|
|
|
sqlQuery('ALTER TABLE `photos_articles_relation`
|
|
ADD CONSTRAINT `photos_articles_relation_ibfk_2` FOREIGN KEY (`id_art`) REFERENCES `articles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE');
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
|
|
public function check_abraDelayedUpdates()
|
|
{
|
|
return findModule('abra') && $this->checkTableExists('abra_delayed_update');
|
|
}
|
|
|
|
/** Create abra_delayed_update table */
|
|
public function upgrade_abraDelayedUpdates()
|
|
{
|
|
sqlQuery('CREATE TABLE IF NOT EXISTS abra_delayed_update (
|
|
related_id INT NOT NULL,
|
|
type VARCHAR(25) NOT NULL,
|
|
CONSTRAINT delayed_update UNIQUE (related_id, type)
|
|
)');
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
|
|
public function check_abraDelayedUpdatesStringId()
|
|
{
|
|
return findModule('abra') && $this->checkColumnType('abra_delayed_update', 'related_id', 'VARCHAR(50)');
|
|
}
|
|
|
|
/** Change abra_delayed_update.related_id to varchar */
|
|
public function upgrade_abraDelayedUpdatesStringId()
|
|
{
|
|
sqlQuery('ALTER TABLE abra_delayed_update MODIFY related_id VARCHAR(50) NOT NULL');
|
|
|
|
$this->upgradeOK();
|
|
}
|
|
}
|