koperasi/script/lap/page/bukubesarutama.js

import { _Report } from '{js}lap-page-module.js?v={time}';

window.BukuBesar = 
_Report
.debug(false)
.qr(`
        {khusus}
        UNION ALL
        SELECT * FROM (
            SELECT 
                trx
                , kode
                , tgl
                , keterangan
                , debit
                , kredit
                , if(dk = 'D', debit - kredit, kredit - debit) total 
                , userlog
            FROM (

                SELECT lap_bb.userlog, {dk} 1 trx, lap_bb.kode, tgl, keterangan, debit, 0 kredit FROM lap_bb
                LEFT JOIN ( SELECT * FROM acc WHERE app = '{app}' ) akn ON akn.kode = lap_bb.rekdebit 
                WHERE akn.app = '{app}' AND akn.main = '{akun}'  {tgl}

                UNION ALL
                SELECT lap_bb.userlog, {dk} 1 trx, lap_bb.kode, tgl, keterangan, 0 debit, kredit FROM lap_bb 
                LEFT JOIN ( SELECT * FROM acc WHERE app = '{app}' ) akn ON akn.kode = lap_bb.rekkredit
                WHERE akn.app = '{app}' AND akn.main = '{akun}'  {tgl}

            ) a ORDER BY trx ASC, tgl ASC, userlog ASC, kode ASC, debit DESC, kredit DESC
        ) a ORDER BY trx ASC, tgl ASC,userlog ASC, kode ASC
    `)
.conf({
    
    khusus: function (obj, e) {
        let khusus = ['3151','3152'];
        let cek = khusus.indexOf(e.getVal('akun')) != -1? true : false;
        return cek? `
            SELECT * FROM (
                SELECT 
                    trx
                    , 'Saldo Awal' kode
                    , max(tgl) tgl
                    , 'Saldo Awal' keterangan
                    , ifnull(sum(debit),0) debit
                    , ifnull(sum(kredit),0) kredit
                    , if(dk = 'D', ifnull(sum(debit),0) - ifnull(sum(kredit),0), ifnull(sum(kredit),0) - ifnull(sum(debit),0)) total 
                    , max(userlog) userlog
                FROM (
                    
                    SELECT '{log}' userlog, dk, 0 trx, 'Saldo Awal' kode, '{start}' tgl, 'Saldo Awal' keterangan, IF(dk='D', total, 0) debit, IF(dk='K', total, 0) kredit 
                    FROM bbtahunan WHERE main = '{akun}' AND tahun = '{thnawal}'
                    UNION ALL
    
                    SELECT lap_bb.userlog, {dk} 0 trx, lap_bb.kode, tgl, keterangan, debit, 0 kredit FROM lap_bb 
                    LEFT JOIN ( SELECT * FROM acc WHERE app = '{app}' ) akn ON akn.kode = lap_bb.rekdebit
                    WHERE akn.app = '{app}' AND akn.main = '{akun}'  {tglbetween}
    
                    UNION ALL
                    SELECT lap_bb.userlog, {dk} 0 trx, lap_bb.kode, tgl, keterangan, 0 debit, kredit FROM lap_bb 
                    LEFT JOIN ( SELECT * FROM acc WHERE app = '{app}' ) akn ON akn.kode = lap_bb.rekkredit
                    WHERE akn.app = '{app}' AND akn.main = '{akun}'  {tglbetween}
    
                ) a ORDER BY trx ASC, tgl ASC, kode ASC, debit DESC, kredit DESC
            ) a
        `:`
            SELECT * FROM (
                SELECT 
                    trx
                    , 'Saldo Awal' kode
                    , tgl
                    , 'Saldo Awal' keterangan
                    , ifnull(sum(debit),0) debit
                    , ifnull(sum(kredit),0) kredit
                    , if(dk = 'D', ifnull(sum(debit),0) - ifnull(sum(kredit),0), ifnull(sum(kredit),0) - ifnull(sum(debit),0)) total 
                    , max(userlog) userlog
                FROM (
    
                    SELECT lap_bb.userlog, {dk} 0 trx, lap_bb.kode, tgl, keterangan, debit, 0 kredit FROM lap_bb 
                    LEFT JOIN ( SELECT * FROM acc WHERE app = '{app}' ) akn ON akn.kode = lap_bb.rekdebit
                    WHERE akn.app = '{app}' AND akn.main = '{akun}'  {tgl1}
    
                    UNION ALL
                    SELECT lap_bb.userlog, {dk} 0 trx, lap_bb.kode, tgl, keterangan, 0 debit, kredit FROM lap_bb 
                    LEFT JOIN ( SELECT * FROM acc WHERE app = '{app}' ) akn ON akn.kode = lap_bb.rekkredit
                    WHERE akn.app = '{app}' AND akn.main = '{akun}'  {tgl1}
    
                ) a ORDER BY trx ASC, tgl ASC, kode ASC, debit DESC, kredit DESC
            ) a
        `;
    },
    app: function (obj, e) {
        return e.getVal('app');
    }
    , tglbetween: function (obj, e) {
        return ` AND tgl >= '${e.getVal('startdate').split('-').shift()}-01-01' AND tgl <= '${e.getVal('startdate')}' `;
    }
    , log: function (obj, e) {
        return tanggal(e.getVal('startdate').split('-').shift()+'-12').normal3+' 00:00:00';
    }
    , thnawal: function (obj, e) {
        return Number(e.getVal('startdate').split('-').shift()) - 1;
    }
    , start: function (obj, e) {
        return tanggal(e.getVal('startdate').split('-').shift()+'-12').normal3;
    }
    , tgl1: function (obj, e) {
        return ` AND tgl < '${e.getVal('startdate')}' `;
    }
    , tgl: function (obj, e) {
        return ` AND tgl >= '${e.getVal('startdate')}' AND tgl <= '${e.getVal('enddate')}' `;
    }
    , akun: function (obj, e) {
        return e.getVal('akun');
    }
    , dk: function (obj, e) {
        var x = acc2
            .cond(obj.globalEvent.getVal('akun'), 'id');
        if (x.length > 0) {
            return `"${x[0].dk}" dk,`;
        }
        return '';
    }
})
.filterConf([
    {
        title: 'Tanggal Mulai',
        type: 'date',
        name: 'startdate',
        nomax: true,
        row: 3,
        readonly: false,
        info: 'Tanggal mulai data ditampilkan',
        action: function () {

        },
    }
    , {
        title: 'Tanggal Akhir',
        type: 'date',
        name: 'enddate',
        row: 3,
        nomax: true,
        info: 'Tanggal akhir data ditampilkan',
        readonly: false,
        action: function () {

        },
    }
    , {
        title: 'APP',
        type: 'select',
        name: 'app',
        row: 3,
        data: AppData2,
        info: 'Pilihan aplikasi',
        textonly: true,
        readonly: false,
        action: function () {

        },
    }
    , {
        title: 'Akun Utama',
        type: 'select',
        name: 'akun',
        row: 3,
        data: [],
        info: 'Menampikan pilihan akun utama berdasarkan app',
        readonly: false,
        action: function () {

        },
    }
])
.filterOnLoad(function (e, status) {
    /*event action*/
    e.change('app', function () {
        let val = this.value;
        let acc = acc2;
        if (val == 'acc' || val == 'usp') {
            acc = acc.like('app', val)
        }
        if (acc.length > 0) {
            e.setOption('akun', acc);
            e.setVal('akun', acc[0].id);
            return false;
        }
        if (acc.length > 0) {
            e.setOption('akun', acc);
            e.setVal('akun', acc[0].id);
            return false;
        }
        return false;
    });
    if (status != false) {
        e.setVal('startdate', tanggal().normal2);
        e.setVal('enddate', tanggal().normal3);
        e.setVal('app', 'usp');
    }
})
.head([
    [
        {
            text: function () {
                return `
                        <div>
                            <img width="75px" src="{{ASSET}}/logo/koperasi.png" />
                        </div>
                    `;
            },
            css: {
                width: '95px',
                border: 'none',
                whiteSpace: 'nowrap',
                textAlign: 'center'
            }
        },
        {
            text: function (obj) {
                return "<div style=\"margin-bottom:5px;font-size:28px;\">BUKU BESAR UTAMA</div>"
                    + "<div style=\"margin-bottom:5px;font-size:18px;\">" +
                    (function (obj) {
                        if (obj.globalEvent != undefined) {
                            var x = acc2
                                .cond(obj.globalEvent.getVal('app'), 'app')
                                .cond(obj.globalEvent.getVal('akun'), 'id');
                            if (x.length > 0) {
                                return x[0].text + ' (' + x[0].id + ')';
                            }
                        };
                        return '-';
                    })(obj)
                    + "</div>"
                    + 'Periode ' + startdate.parent.value
                    + ' s/d '
                    + enddate.parent.value
            },
            colspan: 8,
            css: {
                textAlign: 'center',
                border: 'none',
                whiteSpace: 'nowrap',
            }
        }
    ],
    [
        {
            text: '',
            colspan: 8,
            css: {
                color: '#ffffff',
                height: '10px',
                whiteSpace: 'nowrap',
                textAlign: 'center'
            }
        }
    ],
    [{
        text: 'Kode Transaksi',
        colspan: 2,
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
            textAlign: 'center'
        }
    }
        , {
        text: 'Tanggal',
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
            textAlign: 'center'
        }
    }
        , {
        text: 'Uraian',
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
        }
    }
        , {
        text: 'Debit',
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
            textAlign: 'right'
        }
    }
        , {
        text: 'Kredit',
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
            textAlign: 'right'
        }
    }
        , {
            text: 'Saldo',
            css: {
                borderBottom: '1px solid #ddd',
                whiteSpace: 'nowrap',
                textAlign: 'right'
            }
        }
        , {
            text: 'User Log',
            css: {
                borderBottom: '1px solid #ddd',
                whiteSpace: 'nowrap',
                textAlign: 'right'
            }
        }
    ]
])
.name([
    {
        name: 'kode',
        colspan: 2,
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
        }
    }
    , {
        name: 'tgl',
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
        }
        , custome: function (a, b) {
            if(b.kode == 'Saldo Awal'){
                let [t,m,y] = _val('startdate').split('-');
                let nDate = [y,m,t].join('-');
                let [c,b,a] = tanggal(tanggal(nDate).milisecond - tanggal().oneDayMilisecond)
                .normal.split('-');
                return [a,b,c].join('-');
            }
            return tanggal(a).sekarang;
        }
    }
    , {
        name: 'keterangan',
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
        }
        , custome: function (a, b, obj) {
            a = a.replace(', ,', ',');
            return a;
        }
    }
    , {
        name: 'debit',
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
            textAlign: 'right'
        }
        , custome: function (a, b, obj) {
            if (obj.dataStore['debit'] == undefined) {
                obj.dataStore['debit'] = [];
            }
            obj.dataStore['debit'].push(a.number(2));
            if (b.kode.toLocaleLowerCase().indexOf('saldo awal') != -1) {
                return '';
            }
            return 'Rp. ' + a.number(2).currency(0)
        }
    }
    , {
        name: 'kredit',
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
            textAlign: 'right'
        }
        , custome: function (a, b, obj) {
            if (obj.dataStore['kredit'] == undefined) {
                obj.dataStore['kredit'] = [];
            }
            obj.dataStore['kredit'].push(a.number(2));
            if (b.kode.toLocaleLowerCase().indexOf('saldo awal') != -1) {
                return '';
            }
            return 'Rp. ' + a.number(2).currency(0)
        }
    }
    , {
        name: 'total',
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
            textAlign: 'right'
        }
        , custome: function (a, b, obj) {
            if (obj.dataStore['total'] == undefined) {
                obj.dataStore['total'] = [];
            }
            let [bbc] = acc.cond(_val('akun'), 'main');
            if(bbc.nr == 'N'){
                obj.dataStore['total'].push(a.number(2));
                return 'Rp. ' + obj.dataStore['total'].sum().currency(0);
            }else{
                if(b.kode == 'Saldo Awal'){
                    obj.dataStore['total'].push(Number(0));
                    return 'Rp. ' + Number(0).currency(0);
                }else{
                    obj.dataStore['total'].push(a.number(2));
                    return 'Rp. ' + obj.dataStore['total'].sum().currency(0);
                }
            }
        }
    }
    , {
        name: 'userlog',
        css: {
            borderBottom: '1px solid #ddd',
            whiteSpace: 'nowrap',
            textAlign: 'right'
        }
        , custome: function (a, b, obj) {
            return a;
        }
    }
])
.footer([
    [{
        text: 'Grand Total',
        colspan: 2,
        css: {
            whiteSpace: 'nowrap',
            textAlign: 'left'
        }
    }
        , {
        text: '',
        css: {
            whiteSpace: 'nowrap',
            textAlign: 'center'
        }
    }
        , {
        text: '',
        css: {
            whiteSpace: 'nowrap',
            textAlign: 'center'
        }
    }
        , {
        text: function (obj) {
            obj.dataStore.debit.shift();
            return 'Rp. ' + obj.dataStore.debit.sum().currency(0);
        },
        css: {
            whiteSpace: 'nowrap',
            textAlign: 'right'
        }
    }
        , {
        text: function (obj) {
            obj.dataStore.kredit.shift();
            return 'Rp. ' + obj.dataStore.kredit.sum().currency(0);
        },
        css: {
            whiteSpace: 'nowrap',
            textAlign: 'right'
        }
    }
        , {
            text: function (obj) {
                return 'Rp. ' + obj.dataStore.total.sum().currency(0);
            },
            css: {
                whiteSpace: 'nowrap',
                textAlign: 'right'
            }
        }
        , {
            text: function (obj) {
                return '';
            },
            css: {
                whiteSpace: 'nowrap',
                textAlign: 'right'
            }
        }
    ]
])
.call()