koperasi/module/http/mysql.php

<?php

use NN\files;
use NN\Post;
use NN\Session;
use NN\Module\DB;
use NN\Module\DD;
use NN\Module\Help;
use NN\Module\Datatable;
use NN\Link;
use NN\Enc;
use NN\module\View;

class Mysql{
    
    public static function do(){
        
        DB::query("
            CREATE VIEW akn AS
            select `mainacc`.`ekuitas` AS `ekuitas`,`acc`.`kode` AS `kode`,`acc`.`app` AS `app`,`acc`.`main` AS `main`,`acc`.`nama_main` AS `nama_main`,`acc`.`dk` AS `dk`,`acc`.`nr` AS `nr`,`acc`.`nama` AS `nama`,`acc`.`username` AS `username`,`acc`.`userlog` AS `userlog` from (`acc` left join `mainacc` on((`mainacc`.`kode` = `acc`.`main`)))
        ");

        DB::query("
            CREATE VIEW tahun_awal AS
            select ifnull((select max(year(`totbb`.`tgl`)) from `totbb` where (`totbb`.`keterangan` like 'SALDO NERACA%')),'-') AS `tahun_awal`
        ");

        DB::query("
            CREATE VIEW total_angsuran AS
            select substring_index(substring_index(`mags`.`fkt`,'/',2),'/',-(1)) AS `kodesp`,(sum(`mags`.`pokok`) * -(1)) AS `pokok` from `mags` group by substring_index(substring_index(`mags`.`fkt`,'/',2),'/',-(1))
        ");

        DB::query("
            CREATE VIEW total_penarikan AS
            select `msimpan`.`kodesp` AS `kodesp`,(sum(`msimpan`.`jumlah`) * -(1)) AS `total` from `msimpan` where (`msimpan`.`dk` = 'K') group by `msimpan`.`kodesp` union all select 'BW' AS `kodesp`,(sum(`totsl`.`debet`) * -(1)) AS `total` from `totsl` where ((not((`totsl`.`faktur` like 'FS%'))) and (`totsl`.`debet` <> 0))
        ");

        DB::query("
            CREATE VIEW total_pinjaman AS
            select `mpinjam`.`kodesp` AS `kodesp`,sum(`mpinjam`.`pokok`) AS `pokok` from `mpinjam` group by `mpinjam`.`kodesp`
        ");

        DB::query("
            CREATE VIEW total_simpanan AS
            select `msimpan`.`kodesp` AS `kodesp`,sum(`msimpan`.`jumlah`) AS `total` from `msimpan` where (`msimpan`.`dk` = 'D') group by `msimpan`.`kodesp`
        ");
        
        // produk
        
        DB::query("
            CREATE VIEW view_saldo AS
            select `view_stk`.`kode` AS `kode`,`view_stk`.`gudang` AS `gudang`,ifnull(sum((if(((`view_stk`.`trx` = 'sl') or (`view_stk`.`trx` = 'sl2') or (`view_stk`.`trx` = 'pcr')),-(1),1) * `view_stk`.`qty`)),0) AS `akhir` from `view_stk` group by `view_stk`.`kode`,`view_stk`.`gudang`
        ");
        
        DB::query("
            CREATE VIEW view_stk AS
            select 'aw' AS `trx`,`stk`.`kode` AS `kode`,`stk`.`gudang` AS `gudang`,`stk`.`awal` AS `qty` from `stk` where (`stk`.`gudang` <> '') union select 'sl' AS `trx`,`a`.`kode` AS `kode`,`a`.`gudang` AS `gudang`,sum(`a`.`qty`) AS `qty` from (`sl` `a` left join `stock` `b` on((`a`.`kode` = `b`.`kode`))) group by `a`.`kode`,`a`.`gudang` union select 'slr' AS `trx`,`a`.`kode` AS `kode`,`a`.`gudang` AS `gudang`,sum(`a`.`qty`) AS `qty` from (`slr` `a` left join `stock` `b` on((`a`.`kode` = `b`.`kode`))) group by `a`.`kode`,`a`.`gudang` union select 'pc' AS `trx`,`a`.`kode` AS `kode`,`a`.`gudang` AS `gudang`,sum(`a`.`qty`) AS `qty` from (`pc` `a` left join `stock` `b` on((`a`.`kode` = `b`.`kode`))) group by `a`.`kode`,`a`.`gudang` union select 'pcr' AS `trx`,`a`.`kode` AS `kode`,`a`.`gudang` AS `gudang`,sum(`a`.`qty`) AS `qty` from (`pcr` `a` left join `stock` `b` on((`a`.`kode` = `b`.`kode`))) group by `a`.`kode`,`a`.`gudang` union select 'ad' AS `trx`,`a`.`kode` AS `kode`,`a`.`gudang` AS `gudang`,sum(`a`.`qty`) AS `qty` from (`adjst` `a` left join `stock` `b` on((`a`.`kode` = `b`.`kode`))) group by `a`.`kode`,`a`.`gudang`
        ");

        // total sisa

        DB::query("
            CREATE VIEW sisa_pinjaman AS
            select `b`.`kodesp` AS `kodesp`,(`b`.`pokok` - `a`.`pokok`) AS `total` from (`total_pinjaman` `b` left join `total_angsuran` `a` on((`b`.`kodesp` = `a`.`kodesp`)))
        ");

        DB::query("
            CREATE VIEW sisa_simpanan AS
            select `a`.`kodesp` AS `kodesp`,(`a`.`total` - `b`.`total`) AS `total` from (`total_simpanan` `a` left join `total_penarikan` `b` on((`a`.`kodesp` = `b`.`kodesp`)))
        ");
    }
}