Gecko [ gandhischool.edu.np ]

Name	Size	Permission
2014_07_02_230147_migration_ca...	3.83 KB	-rw-r--r--
2017_07_10_054918_create_repai...	1.09 KB	-rw-r--r--
2017_07_10_061027_create_compa...	1.34 KB	-rw-r--r--
2017_07_10_104930_create_conta...	1.24 KB	-rw-r--r--
2017_07_10_105351_create_recei...	1.44 KB	-rw-r--r--
2017_07_10_105352_add_isBilled...	674 B	-rw-r--r--
2017_07_10_105711_create_other...	817 B	-rw-r--r--
2017_07_10_105847_create_recei...	1.38 KB	-rw-r--r--
2017_07_10_110233_create_repai...	1.33 KB	-rw-r--r--
2017_07_10_110640_create_repai...	1.72 KB	-rw-r--r--
2017_07_10_111132_create_remar...	749 B	-rw-r--r--
2017_07_10_111431_create_statu...	795 B	-rw-r--r--
2017_07_10_153358_create_setti...	779 B	-rw-r--r--
2017_07_14_155029_create_user_...	1.07 KB	-rw-r--r--
2017_07_15_014159_create_modul...	697 B	-rw-r--r--
2017_07_15_100753_create_activ...	911 B	-rw-r--r--
2017_07_23_142710_create_notif...	816 B	-rw-r--r--
2017_08_02_090956_add_status_l...	724 B	-rw-r--r--
2017_09_10_105635_create_rate_...	820 B	-rw-r--r--
2017_09_10_114106_create_cylin...	817 B	-rw-r--r--
2017_09_12_020245_create_recei...	1.34 KB	-rw-r--r--
2017_09_14_042819_CreateCompan...	2.93 KB	-rw-r--r--
2017_09_14_043818_CreateFactor...	2.17 KB	-rw-r--r--
2017_10_14_053149_create_pages...	808 B	-rw-r--r--
2017_10_14_063625_create_banne...	757 B	-rw-r--r--
2017_11_07_161634_create_confi...	1.12 KB	-rw-r--r--
2017_12_08_014442_create_payme...	1.15 KB	-rw-r--r--
2018_01_01_061700_create_produ...	1.17 KB	-rw-r--r--
2018_01_01_063324_create_custo...	792 B	-rw-r--r--
2018_01_01_063613_create_order...	1.1 KB	-rw-r--r--
2018_01_03_132049_add_remark_c...	688 B	-rw-r--r--
2018_01_04_010020_add_barcode_...	808 B	-rw-r--r--
2018_01_04_152906_add_column_i...	688 B	-rw-r--r--
2018_02_05_093827_add_tax_opti...	920 B	-rw-r--r--
2018_02_16_161553_add_remarks_...	683 B	-rw-r--r--
2018_03_09_151221_add_columns_...	838 B	-rw-r--r--
2018_03_10_062011_add_shipping...	688 B	-rw-r--r--
2018_03_11_013333_add_columns_...	773 B	-rw-r--r--
2018_03_11_071659_add_image_co...	647 B	-rw-r--r--
2018_03_18_012402_add_necessar...	822 B	-rw-r--r--
2018_04_14_010308_add_confirm_...	663 B	-rw-r--r--
2018_05_13_004258_create_order...	1.17 KB	-rw-r--r--
2018_05_14_011533_add_new_colu...	744 B	-rw-r--r--
2018_05_22_052238_add_new_colu...	1.05 KB	-rw-r--r--
2018_07_03_011344_create_table...	983 B	-rw-r--r--
2018_07_09_025647_create_order...	919 B	-rw-r--r--
2018_07_10_161854_create_recei...	883 B	-rw-r--r--
2018_07_11_045539_create_recei...	899 B	-rw-r--r--
2018_07_12_012713_create_recei...	907 B	-rw-r--r--

Code Editor : 2017_09_14_042819_CreateCompaniesView.php

<?php

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

class CreateCompaniesView extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement("
            CREATE OR REPLACE VIEW view_company_rate AS (
            SELECT
            repair_rate.*,
            company.name as company_name,
            company.id as company_id,
            company.phone,
            company.address,
            company.user_id,
            contact_person.name as contact_person,
            receive.date as date,
            receive.total_cylinder,
            receive.with_cap,
            receive.without_cap,
            receive.status,
            receive.isBilled,
            receive.exchange_rate,
            receive.id as receive_id,
            repair.remarks,
            repair.id as repair_id,
            COALESCE(sum(repair_details.body_leakage),0) as sum_body_leakage,
            COALESCE(sum(repair_details.nozzle_change),0) as sum_nozzle_change,
            COALESCE(sum(repair_details.nozzle_repair),0) as sum_nozzle_repair,
            COALESCE(sum(repair_details.cap_change),0) as sum_cap_change,
            COALESCE(sum(repair_details.washer_change),0) as sum_washer_change,
            COALESCE(sum(repair_details.wash),0) as sum_wash,
            COALESCE(sum(repair_details.refill),0) as sum_refill,
            (sum(repair_details.nozzle_change) * repair_rate.nozzle_change) as 'price_nozzle_change',
            (sum(repair_details.nozzle_repair) * repair_rate.nozzle_repair) as 'price_nozzle_repair',
            (sum(repair_details.cap_change) * repair_rate.cap_change) as 'price_cap_change',
            (sum(repair_details.washer_change) * repair_rate.washer_change) as 'price_washer_change',
            (sum(repair_details.refill) * repair_rate.refill) as 'price_refill',
            (COALESCE(sum(repair_details.body_leakage),0) + COALESCE(sum(repair_details.nozzle_change),0) + COALESCE(sum(repair_details.nozzle_repair),0) + COALESCE(sum(repair_details.cap_change),0) + COALESCE(sum(repair_details.washer_change),0) + COALESCE(sum(repair_details.wash),0) + COALESCE(sum(repair_details.refill),0)) as total_quantity
            FROM
            repair
            INNER JOIN repair_details  ON repair_details.repair_id = repair.id
            INNER JOIN repair_rate  ON repair_rate.id = repair.company_rate_id
            INNER JOIN receive  ON receive.id = repair.receive_id
            INNER JOIN company  ON receive.company_id = company.id
            LEFT JOIN contact_person  ON contact_person.company_id = company.id
            group by receive.id 
            )");
    }

/**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement("DROP VIEW view_company_rate");
    }
}

${this.title}

Code Editor : 2017_09_14_042819_CreateCompaniesView.php