koperasi/views/file/simpanan.blade.php

@extends('temp.admin')
@php
use NN\Module\DB;
use NN\Module\View;
@endphp
@section("content")

<div class="content-header row">
    <div class="content-header-left col-md-6 col-12 mb-2 breadcrumb-new">
        <h3 class="content-header-title mb-0 d-inline-block">Import Data</h3>
        <div class="row breadcrumbs-top d-inline-block">
            <div class="breadcrumb-wrapper col-12">
                <ol class="breadcrumb">
                    <li class="breadcrumb-item active" id="importdata"></li>
                </ol>
            </div>
        </div>
    </div>
    <div class="content-header-right col-md-6 col-12">
        <div class="btn-group float-md-right">
            
        </div>
    </div>
</div>
<div class="content-body">
    <section id="dasbor">
        <div class="row mb-4">
            
            <div class="col-sm-12">
                <div class="card">
                    <div class="card-content">
                        <div class="card-body sales-growth-chart" id="fomr-d">
                            <p style="padding: 5px 10px;border-radius:10px; border: 1px solid #ddddff;"><i class="la la-info-circle"></i> 
                                Import data melalui excel akan mereset data anggota. jika ada transaksi terkait kode anggota maka akan mempengaruhi laporan.
                                sebelum import silahkan download template excel <a href="{{ASSET}}/excel/anggota.xlsx">disini</a>
                            </p>
                            <div>
                                <div style="display: grid; grid-template-columns: auto;">
                                    <div class="card-body sales-growth-chart">
                                        <div onclick="_id('demo').click()" style="padding: 20px; border: 1px solid #ddd; border-radius: 10px; cursor: pointer; text-align :center;"> Upload file excel </div>
                                    </div>
                                    <input type="file" style="display:none;" class="form-control" id="demo" accept=".xls,.xlsx">
                                </div>
                                <div>
                                    <div>
                                        <input id="tgl" type="text" class="form-control" style="width: 300px;display:inline-block;" placeholder="tanggal" />
                                        <button class="btn btn-primary" onclick="window._uploadData()">Update Data Anggota</button>
                                    </div>
                                    <div id="button-n">

                                    </div>
                                    <div  id="table-x">
                                        
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

            </div>
           
        </div>
    </section>
</div>


@endsection

@section('css')
<style>
    td{
        white-space:nowrap;
    }
</style>
@endsection
@section('script')
<script>
    $("#tgl").datepicker({ format: 'dd-mm-yyyy' });
    $("#tgl").datepicker("update", new Date( tanggal().normal ));
</script>
<script>

globalThis._nw = (function () {
    return {
        code: function (a = {}, callback = null) {
            var code = a.code;
            var code1 = tanggal().normal.split('-')[0].substring(2, 4);
            var table = a.table;
            var row = a.row;
            return `
                SELECT concat('${code}/${a.data}/${code1}/',lpad( ifnull(
                (SELECT substring_index(${row}, '${code1}/',-1) FROM ${table} WHERE ${row} LIKE '${code}/${a.data}/${code1}/%' ORDER BY ${row} DESC LIMIT 1)
                ,0
                ) + 1,6,0)) ${row}
                `;
        }
        ,codex: function (a = {}) {
            var code = a.code;
            var code1 = tanggal().normal.split('-')[0].substring(2, 4);
            var code2 = tanggal().normal.split('-')[0];
            var table = a.table;
            var row = a.row;
            return `
                SELECT concat('${code}/${a.data}/${code1}/',lpad( ifnull(
                (SELECT substring_index(${row}, '${code1}/',-1) FROM ${table} WHERE ${row} LIKE '${code}/${a.data}/${code1}/%' ORDER BY ${row} DESC LIMIT 1)
                ,0
                ) + 1,6,0)) ${row}
                `
        },
    }
})();

    Array.prototype.groupByKodesp = function(kodespProp) {
        const groupedData = {};

        this.forEach(item => {
            const kodesp = item[kodespProp];
            if (!groupedData[kodesp]) {
            groupedData[kodesp] = [];
            }
            groupedData[kodesp].push(item);
        });

        return Object.keys(groupedData).map(function(q){
            return groupedData[q];
        });
    };

    setTimeout(() => {
        document.querySelector('.modern-nav-toggle').click();
    }, 1000);
    const buttonN = _id('button-n');
    const xTable = _id('table-x');

    const catchData = function(range, worksheet){
        let data = [];
        for (let row=range.s.r; row<=range.e.r; row++) {
            let i = data.length;
            data.push([]);
            for (let col=range.s.c; col<=range.e.c; col++) {
                let cell = worksheet[XLSX.utils.encode_cell({r:row, c:col})];
                if(cell != undefined){
                    data[i].push(cell.v);
                }else{
                    data[i].push('-');
                }
            }
        };
        data = data.filter(function(w){
            let [s] = w;
            if(typeof s != 'undefined' && s != '-'){
                return w;
            }
        });
        return data;
    };

    const showTable = function(nod, sArray){
        var nId = 'table-'+Date.now();
        xTable.innerHTML = ``;
        xTable.innerHTML = `
            <table id="${nId}" style="width:100%;" class="table table-bordered">
                <thead class="bg-primary text-white">
                    <tr>
                        ${nod.map(function(o){
                            return `<th>${o.val}</th>`;
                        }).join('')}
                    </tr>
                </thead>
            </table>
        `;
        var op = {
            dom: 'Bfrtip',
            scrollY: '250px',
            scrollX: true,
            scrollCollapse: true,
            paging: true,
            "lengthChange": true,
            "lengthMenu": [[25, 50, 100, 500, 1000], [25, 50, 100, 500, "Max"]],
            "pageLength": 50
        };
        var tbx = $('#'+nId).DataTable(op);
        sArray.forEach(function(d){
            tbx.row.add(d);
        });
        tbx.draw(true);
    };

    const getData = function(data){
        let nod = [];
        data[0].forEach(function(s, i){
            if(s != '-'){
                nod.push({
                    id: i,
                    val: s
                });
            }
        });
        let snod = data;
        snod.shift();
        let sObject = snod.map(function(df, i){
            let f ={};
            nod.forEach(function(o){
                if(o.val == 'tgllahir'){
                    if(typeof df[o.id] === 'number'){
                        f[o.val] = tanggal(ExcelDateToJSDate(df[o.id])).normal;
                    }else{
                        f[o.val] = df[o.id].fixMonth();
                    }
                }else if(o.val == 'masuk'){
                    let q = df[o.id].toString().replaceMonth().split(' ');
                    if(q.length > 1){
                        f[o.val] = q[1]+'-'+q[0]+'-01';
                    }else{
                        f[o.val] = q[0]+'-01-01';
                    }
                }else{
                    f[o.val] = df[o.id];
                }
            });
            return f;
        });
        let sArray = snod.map(function(df, i){
            let f =[];
            nod.forEach(function(o){
                if(o.val.indexOf('tgl') != -1 ){
                    if(typeof df[o.id] === 'number'){
                        f.push(tanggal(ExcelDateToJSDate(df[o.id])).normal);
                    }else{
                        if(df[o.id] == '-'){
                            f.push('0000-00-00');
                        }else{
                            f.push(df[o.id].fixMonth());
                        }
                    };
                }else if(o.val == 'masuk'){
                    let q = df[o.id].toString().replaceMonth().split(' ');
                    if(q.length > 1){
                        f.push(q[1]+'-'+q[0]+'-01');
                    }else{
                        f.push(q[0]+'-01-01');
                    }
                }else{
                    f.push(df[o.id]);
                }
            });
            return f;
        });

        return {
            nod : nod,
            sObject: sObject,
            sArray: sArray
        }

    };

    document.getElementById("demo").onchange = evt => {
    let _ld = cssLoader();
    let reader = new FileReader();
        reader.addEventListener("loadend", evt => {
            let workbook = XLSX.read(evt.target.result, {type: "binary"});
            let [wpinjam, wsimpan] = workbook.SheetNames;
            let worksheet = workbook.Sheets[wpinjam];
            let worksheet2 = workbook.Sheets[wsimpan];
            let range = XLSX.utils.decode_range(worksheet["!ref"]);
            let range2 = XLSX.utils.decode_range(worksheet2["!ref"]);
            let data = catchData(range, worksheet);
            let data2 = catchData(range2, worksheet2);

            if(data[0] != undefined){
                
                window.dataSheetS = {
                    sheet1 : getData(data),
                    sheet2 : getData(data2)
                };
                _ld.remove();

                let {nod, sArray} = dataSheetS.sheet1;

                buttonN.appendChild(
                    el('div')
                    .css({
                        marginTop: '10px',
                        padding: '10px',
                        border: '1px solid #ddd',
                    })
                    .child(
                        el('button').class('btn btn-sm btn-success mr-1').addModule('sheet', dataSheetS.sheet1).html('<i class="la la-file-excel-o"></i> Pinjaman')
                        .click(function(){
                            let {nod, sArray} = this.sheet;
                            showTable(nod, sArray);
                        })
                    )
                    .child(
                        el('button').class('btn btn-sm btn-success').addModule('sheet', dataSheetS.sheet2).html('<i class="la la-file-excel-o"></i> Simpanan')
                        .click(function(){
                            let {nod, sArray} = this.sheet;
                            showTable(nod, sArray);
                        })
                    )
                    .get()
                )


                showTable(nod, sArray);
                
            };
        });
        reader.readAsArrayBuffer(evt.target.files[0]);
    };


    function convertTgl(){
        let [tgl,bln,thn] = _id('tgl').value.split('-');
        return `${thn}-${bln}-${tgl}`;
    }

    window._uploadData = function(){
        let data = window.dataSheetS;
        let {sheet1,sheet2} = data;
        let tgl = convertTgl();

        let smpn = ['BW'
        ,'SM'
        ,'SP'
        ,'SW'
        ,'SWP'
        ,'TK'];

        let mj = ['REG', 'USP1', 'USP2', 'USP3', 'KHUSUS'];
        let newCode  = mj.map(function(mjk){
            let code1 = _id('tgl').value.split('-')[2].substring(2,4);
            return `
            SELECT concat('BKK/${mjk}/${code1}/',lpad( ifnull(
            (SELECT substring_index(faktur, '${code1}/',-1) FROM mpinjam WHERE faktur LIKE 'BKK/${mjk}/${code1}/%' ORDER BY faktur DESC LIMIT 1)
            ,0
            ) + 1,6,0)) faktur
            `
        }).join(' [;] ');

        let rm = smpn.map(function(r){
            return globalThis._nw.code({
                code: 'BKM',
                row: 'faktur',
                table: 'msimpan',
                data: r,
            });
        }).join('[;]')+'[;]'+globalThis._nw.codex({
            code: 'BKM',
            row: 'faktur',
            row2: 'nomor',
            table: 'mags',
            data: 'P' + tanggal().normal.split('-')[0].substring(0, 2),
        });

        let ldr = cssLoader();
        AuditDevQuery(`
        DELETE FROM mpinjam WHERE keterangan LIKE '%data awal%'[;]
        DELETE FROM msimpan WHERE keterangan LIKE '%data awal%'[;]
        DELETE FROM mags WHERE keterangan LIKE '%data awal%'
        `, function(){
        AuditDevQuery(newCode, function(q){
        AuditDevQuery(rm, function(qs){
            let newAng = qs.pop()[0].faktur;
            let codeBaru = [];
            q.forEach(function(s){
                let [data] = s;
                let {faktur} = data;
                codeBaru.push({
                    faktur: faktur,
                    code: faktur.split('/')[1],
                });
            });
            let codeSimpan = [];
            qs.forEach(function(s){
                let [data] = s;
                let {faktur} = data;
                codeSimpan.push({
                    faktur: faktur,
                    code: faktur.split('/')[1],
                });
            });

            let saveToAngsuran = (function(data, tgl){
                let h = data.map(function(v){
                    return {
                        tgl: convertTgl()
                        , pokok: v.besarpinjaman - v.sisapokok
                        , bunga: 0
                        , ke: v.angsakhirke
                        , lama: v.masa
                        , kodesp: v.kodesp
                        , shu: (function(d,s){
                            if(s.toLowerCase() == 'g'){
                                return d;
                            }
                            return 0;
                        })(v.besarpinjaman - v.sisapokok, v.status)
                        , nonshu: (function(d,s){
                            if(s.toLowerCase() == 'l'){
                                return d;
                            }
                            return 0;
                        })(v.besarpinjaman - v.sisapokok, v.status)
                        , kode: v.no
                        , keterangan: 'Posting: Data Awal'
                        , posting: 'dataawal'
                        , user: username
                        , userlog: timestamp()
                    }
                }).filter(function(w){
                    if(w.kodesp.toLowerCase() != 'pt'){
                        return w;
                    }
                }).groupByKodesp('kodesp');

                let releasePenjualan = [];
                let numeris = 0;
                h.forEach(function(c){
                    c.map(function(w, i){
                        let {faktur} = codeBaru.cond(w.kodesp, 'code')[0];
                        let newCode = faktur.split('/');
                        let nv = (Number(newCode.pop()) + i).pad(6);
                        let releaseCode = newCode.join('/')+'/'+nv;
                        numeris++;
                        w.fkt = releaseCode;
                        releasePenjualan.push(w)
                    })
                });

                releasePenjualan = releasePenjualan.map(function(e,i){
                        let newCode = newAng.split('/');
                        let nv = (Number(newCode.pop()) + i).pad(6);
                        let releaseCode = newCode.join('/')+'/'+nv;
                        e.faktur = releaseCode;
                        let u = releaseCode.split('/');
                        u.shift();
                        u = u.join('')
                        e.nomor = u;
                        delete e.kodesp;
                        return e;
                })

                return releasePenjualan.ToInsert('mags');
            })(sheet1.sObject, tgl);

            let saveToPinjaman = (function(data, tgl){
                let h = data.map(function(v){
                    return {
                        tgl: tanggal(ExcelDateToJSDate(v.tglpinjaman)).normal
                        , jumlah: v.besarpinjaman
                        , pokok: v.besarpinjaman
                        , bunga: (v.besarpinjaman * v.bungaperth/100 ) 
                        , angsuran: (v.besarpinjaman/v.masa) + (v.besarpinjaman * v.bungaperth/100 /v.masa)
                        , lama: v.masa
                        , bungath: v.bungaperth
                        , user: username
                        , kodesp: v.kodesp
                        , tr: v.status
                        , jenis: 1
                        , kode: v.no
                        , tglmohon: tanggal(ExcelDateToJSDate(v.tglpinjaman)).normal
                        , keterangan: 'Posting: Data Awal'
                        , userlog: timestamp()
                    }
                }).filter(function(w){
                    if(w.kodesp.toLowerCase() != 'pt'){
                        return w;
                    }
                }).groupByKodesp('kodesp');

                let releasePenjualan = [];

                h.forEach(function(c){
                    c.map(function(w, i){
                        let {faktur} = codeBaru.cond(w.kodesp, 'code')[0];
                        let newCode = faktur.split('/');
                        let nv = (Number(newCode.pop()) + i).pad(6);
                        let releaseCode = newCode.join('/')+'/'+nv;
                        w.faktur = releaseCode;
                        releasePenjualan.push(w)
                    })
                });

                return releasePenjualan.ToInsert('mpinjam');
            })(sheet1.sObject, tgl);

            let saveToSimpan = (function(data, tgl){
                let h = data.map(function(v){
                    return {
                        tgl: convertTgl()
                        , kode: v.no
                        , jumlah: v.saldoakhir
                        , kodesp: v.kodesp
                        , dk: 'D'
                        , jam: timestamp().split(' ')[1]
                        , keterangan: 'Posting: Data Awal'
                        , posting: 'dataawal'
                        , username : username
                        , kasir : usernama
                        , userlog: timestamp()
                    }
                }).filter(function(w){
                    if(w.kodesp.toLowerCase() != 'pt'){
                        return w;
                    }
                }).groupByKodesp('kodesp');

                let releasePenjualan = [];

                h.forEach(function(c){
                    c.map(function(w, i){
                        let {faktur} = codeSimpan.cond(w.kodesp, 'code')[0];
                        let newCode = faktur.split('/');
                        let nv = (Number(newCode.pop()) + i).pad(6);
                        let releaseCode = newCode.join('/')+'/'+nv;
                        w.faktur = releaseCode;
                        releasePenjualan.push(w)
                    })
                });


                return releasePenjualan.ToInsert('msimpan');
            })(sheet2.sObject, tgl);

            let dx = saveToPinjaman;
            dx += ' [;] '+ saveToSimpan;
            dx += ' [;] '+ saveToAngsuran;

            AuditDevQuery(dx,function(){
                ldr.remove();
                Swal('Info', 'Data berhasil di import', 'success');
            })
        
        });
        });
        });


    };
</script>
@endsection