ꦫꦣꦺꦤ꧀ꦄꦤ꧀ꦠꦱꦺꦤ

PATH: home / lab2454c / costbloc.com / database / migrations

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class ProductTrigger extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {

// -- trigger on after insert wishlist
        \DB::unprepared('
            CREATE TRIGGER `wishlists_AINS` AFTER INSERT ON `wishlists`
            FOR EACH ROW UPDATE products SET total_wish = (select count(product_id) from wishlists WHERE product_id = products.id) WHERE products.id = NEW.product_id
        ');

// -- trigger on after delete wishlist
        \DB::unprepared('
            CREATE TRIGGER `wishlists_ADEL` AFTER DELETE ON `wishlists`
            FOR EACH ROW UPDATE products SET total_wish = (select count(product_id) from wishlists WHERE product_id = products.id) WHERE products.id = OLD.product_id
        ');

// -- trigger on after insert review
        \DB::unprepared('
            CREATE TRIGGER `reviews_AINS` AFTER INSERT ON `reviews`
            FOR EACH ROW UPDATE products SET review_count = (select count(product_id) from reviews WHERE product_id = products.id AND is_public = "1" AND status = "Active"), review_average = (select avg(rating) from reviews WHERE product_id = products.id AND is_public = "1" AND status = "Active") WHERE products.id = NEW.product_id
        ');

// -- trigger on after update review
        \DB::unprepared('
            CREATE TRIGGER `reviews_AUPD` AFTER UPDATE ON `reviews`
            FOR EACH ROW UPDATE products SET review_count = (select count(product_id) from reviews WHERE product_id = products.id AND is_public = "1" AND status = "Active"), review_average = (select avg(rating) from reviews WHERE product_id = products.id AND is_public = "1" AND status = "Active") WHERE products.id = NEW.product_id
        ');

// -- trigger on after delete review
        \DB::unprepared('
            CREATE TRIGGER `reviews_ADEL` AFTER DELETE ON `reviews`
            FOR EACH ROW UPDATE products SET review_count = (select count(product_id) from reviews WHERE product_id = products.id AND is_public = "1" AND status = "Active"), review_average = (select avg(rating) from reviews WHERE product_id = products.id AND is_public = "1" AND status = "Active") WHERE products.id = OLD.product_id
        ');

\DB::unprepared('
            CREATE TRIGGER `order_details_product_stats_ins` AFTER INSERT ON `order_details` FOR EACH ROW
            BEGIN IF NEW.parent_id IS NOT NULL THEN SET @mpid = NEW.parent_id; ELSE SET @mpid = NEW.product_id; END IF; INSERT INTO product_stats (`product_id`,`count_sales`,`date`) VALUES (@mpid, 1, CURDATE()) ON DUPLICATE KEY UPDATE count_sales = count_sales + 1; END;
        ');

\DB::unprepared('
            CREATE TRIGGER `product_stats_counter_upd` AFTER UPDATE ON `product_stats` FOR EACH ROW BEGIN update products set products.total_views = CASE WHEN OLD.count_views = NEW.count_views THEN products.total_views ELSE products.total_views + 1 END, products.total_sales = CASE WHEN OLD.count_sales = NEW.count_sales THEN products.total_sales ELSE products.total_sales + 1 END WHERE id = NEW.product_id;
            IF (select `brand_id` from products where id = NEW.product_id) is not null THEN IF NEW.count_views != OLD.count_views THEN INSERT INTO brand_stats (`brand_id`,`count_views`,`date`) VALUES ((select `brand_id` from products where id = NEW.product_id), 1, CURDATE()) ON DUPLICATE KEY UPDATE count_views = count_views + 1; END IF;
            IF NEW.count_sales != OLD.count_sales THEN INSERT INTO brand_stats (`brand_id`,`count_sales`,`date`) VALUES ((select `brand_id` from products where id = NEW.product_id), 1, CURDATE()) ON DUPLICATE KEY UPDATE count_sales = count_sales + 1; END IF; END IF; END
        ');

\DB::unprepared('
            CREATE TRIGGER `product_stats_counter_ins` AFTER INSERT ON `product_stats`
            FOR EACH ROW BEGIN update products set products.total_views = CASE WHEN NEW.count_views = 1 THEN products.total_views + 1 ELSE products.total_views END, products.total_sales = CASE WHEN NEW.count_sales = 1 THEN products.total_sales + 1 ELSE products.total_sales END WHERE id = NEW.product_id;
            IF (select `brand_id` from products where id = NEW.product_id) is not null THEN IF NEW.count_views != 0 THEN INSERT INTO brand_stats (`brand_id`,`count_views`,`date`) VALUES ((select `brand_id` from products where id = NEW.product_id), 1, CURDATE()) ON DUPLICATE KEY UPDATE count_views = count_views + 1; END IF;
            IF NEW.count_sales != 0 THEN INSERT INTO brand_stats (`brand_id`,`count_sales`,`date`) VALUES ((select `brand_id` from products where id = NEW.product_id), 1, CURDATE()) ON DUPLICATE KEY UPDATE count_sales = count_sales + 1; END IF; END IF; END
        ');
    }

/**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        \DB::unprepared('DROP TRIGGER `order_details_AINS`');
        \DB::unprepared('DROP TRIGGER `reviews_ADEL`');
        \DB::unprepared('DROP TRIGGER `reviews_AINS`');
        \DB::unprepared('DROP TRIGGER `reviews_AUPD`');
        \DB::unprepared('DROP TRIGGER `wishlists_ADEL`');
        \DB::unprepared('DROP TRIGGER `wishlists_AINS`');
        \DB::unprepared('DROP TRIGGER `product_stats_counter_ins`');
        \DB::unprepared('DROP TRIGGER `product_stats_counter_upd`');
    }
}

[+] ..
[-] 2021_10_10_065928_add_foreign_keys_to_favourites_table.php [edit]
[-] 2021_10_10_065927_create_vendor_users_table.php [edit]
[-] 2021_10_10_065927_create_flash_sales_table.php [edit]
[-] 2022_01_18_072726_create_brand_stats_table.php [edit]
[-] 2022_02_15_0000042_add_foreign_keys_to_user_withdrawal_settings_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_attributes_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_subscription_invoice_items_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_reviews_table.php [edit]
[-] 2022_09_05_073603_add_foreign_keys_to_orders_meta_table.php [edit]
[-] 2016_06_01_000005_create_oauth_personal_access_clients_table.php [edit]
[-] 2021_10_10_065927_create_languages_table.php [edit]
[-] 2022_01_31_093341_create_order_status_roles_table.php [edit]
[-] 2021_10_10_065928_create_addresses_table.php [edit]
[-] 2021_10_10_065927_create_categories_table.php [edit]
[-] 2021_10_10_065927_create_reviews_table.php [edit]
[-] 2021_10_10_065927_create_email_templates_table.php [edit]
[-] 2021_11_25_072726_category_trigger.php [edit]
[-] 2022_02_15_0000041_create_user_withdrawal_settings_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_product_tags_table.php [edit]
[-] 2022_02_15_0000043_create_transactions_table.php [edit]
[-] 2016_06_01_000001_create_oauth_auth_codes_table.php [edit]
[-] 2022_05_18_094636_create_metas.php [edit]
[-] 2022_05_18_092820_create_user_searches_table.php [edit]
[-] 2021_10_10_065927_create_products_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_products_table.php [edit]
[-] 2021_10_10_065927_create_category_attributes_table.php [edit]
[-] 2021_10_10_065927_create_product_tags_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_product_cross_sales_table.php [edit]
[-] 2021_10_10_065927_create_preferences_table.php [edit]
[-] 2022_05_18_074840_create_users_meta.php [edit]
[-] 2021_10_10_065927_create_role_users_table.php [edit]
[-] 2021_10_10_065927_create_subscription_invoices_table.php [edit]
[-] 2021_10_10_065927_create_vendors_table.php [edit]
[-] 2021_10_10_065927_create_currencies_table.php [edit]
[-] 2021_10_10_065927_create_email_configurations_table.php [edit]
[-] 2016_06_01_000004_create_oauth_clients_table.php [edit]
[-] 2021_12_16_111713_add_foreign_keys_to_order_details_table.php [edit]
[-] 2022_02_13_0000035_create_wallets_table.php [edit]
[-] 2021_10_10_065927_create_product_relates_table.php [edit]
[-] 2022_02_15_0000044_add_foreign_keys_to_transactions_table.php [edit]
[-] 2022_01_18_072726_create_category_stats_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_product_relates_table.php [edit]
[-] 2022_06_01_052414_add_foreign_keys_to_order_note_histories_table.php [edit]
[-] 2021_10_10_065927_create_attribute_groups_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_attribute_values_table.php [edit]
[-] 2021_10_10_065927_create_users_table.php [edit]
[-] 2021_10_10_065929_add_foreign_keys_to_addresses_table.php [edit]
[-] 2021_10_10_065927_create_locations_table.php [edit]
[-] 2021_11_25_072702_tag_trigger.php [edit]
[-] 2021_10_10_065927_create_product_categories_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_categories_table.php [edit]
[-] 2021_10_10_065927_create_tags_table.php [edit]
[-] 2021_10_10_065927_create_product_cross_sales_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_permission_roles_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_role_users_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_product_categories_table.php [edit]
[-] 2022_06_07_050839_create_products_meta_table.php [edit]
[-] 2021_10_10_065927_create_translations_table.php [edit]
[-] 2021_10_10_065927_create_brands_table.php [edit]
[-] 2016_06_01_000003_create_oauth_refresh_tokens_table.php [edit]
[-] 2022_10_30_062626_create_activity_log_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_vendor_users_table.php [edit]
[-] 2021_10_10_065927_create_backups_table.php [edit]
[-] 2021_10_10_065927_create_subscription_invoice_items_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_email_templates_table.php [edit]
[-] 2021_10_10_065927_create_password_resets_table.php [edit]
[-] 2022_01_31_093342_add_foreign_keys_to_order_status_roles_table.php [edit]
[-] 2022_04_06_104629_add_foreign_keys_to_seos_table.php [edit]
[-] 2022_01_18_072726_product_trigger.php [edit]
[-] 2021_12_16_111710_create_orders_table.php [edit]
[-] 2021_11_25_072727_product_category_trigger.php [edit]
[-] 2021_10_10_065927_create_failed_jobs_table.php [edit]
[-] 2021_10_10_065927_create_favourites_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_wishlists_table.php [edit]
[-] 2022_01_31_093451_add_foreign_keys_to_order_status_histories_table.php [edit]
[-] 2016_06_01_000002_create_oauth_access_tokens_table.php [edit]
[-] 2022_02_13_0000037_add_foreign_keys_to_wallets_table.php [edit]
[-] 2021_12_16_111712_create_order_details_table.php [edit]
[-] 2021_10_10_065927_create_wishlists_table.php [edit]
[-] 2021_10_10_065927_create_countries_table.php [edit]
[-] 2022_08_22_073603_add_foreign_keys_to_products_meta_table.php [edit]
[-] 2021_10_10_065927_create_permissions_table.php [edit]
[-] 2022_09_04_050839_create_orders_meta_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_category_attributes_table.php [edit]
[-] 2022_06_01_052314_create_order_note_histories_table.php [edit]
[-] 2021_12_16_111707_create_order_statuses_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_brands_table.php [edit]
[-] 2022_02_15_0000040_create_withdrawal_methods_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_flash_sales_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_attribute_groups_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_roles_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_tags_table.php [edit]
[-] 2021_10_10_065927_create_attributes_table.php [edit]
[-] 2022_01_31_093450_create_order_status_histories_table.php [edit]
[-] 2021_10_10_065928_add_foreign_keys_to_product_upsales_table.php [edit]
[-] 2022_05_18_092821_add_foreign_keys_to_user_searches_table.php [edit]
[-] 2022_04_06_104628_create_seos_table.php [edit]
[-] 2022_01_18_072726_create_product_stats_table.php [edit]
[-] 2021_12_16_111711_add_foreign_keys_to_orders_table.php [edit]
[-] 2021_10_10_065927_create_permission_roles_table.php [edit]
[-] 2021_10_10_065927_create_product_upsales_table.php [edit]
[-] 2022_05_18_092750_create_searches_table.php [edit]
[-] 2021_10_10_065927_create_subscription_quotes_table.php [edit]
[-] 2021_10_10_065927_create_attribute_values_table.php [edit]
[-] 2022_10_03_060019_create_vendors_meta_table.php [edit]
[-] 2021_10_10_065927_create_roles_table.php [edit]