const data = JSON.parse(_id('data').innerHTML);
console.log(data);
const kekayaan = _id('kekayaan');
const pj = [
    {
        kode: 'REG',
        nama: 'USP Reguler'
    }
    , {
        kode: 'USP1',
        nama: 'USP1 (Pembiayaan Elektronik)'
    }
    , {
        kode: 'USP2',
        nama: 'USP2 (Pembiayaan Elektronik)'
    }
    , {
        kode: 'USP3',
        nama: 'USP3 (Pembiayaan Haji / Umroh)'
    }
    , {
        kode: 'KHUSUS',
        nama: 'KHUSUS'
    }
];

const namaBulan = [
    "Januari",
    "Februari",
    "Maret",
    "April",
    "Mei",
    "Juni",
    "Juli",
    "Agustus",
    "September",
    "Oktober",
    "November",
    "Desember"
];


const cekdata = function (data) {
    return {
        data : typeof data === 'object'? data:{},
        search : function (cari) {
            this.data = this.data && this.data[cari]? this.data[cari] : null;
            return this;            
        }
    }
}

const tableCreator = function (mydata) {
    let {anggota, simp, simpanan, pinjaman} = mydata;

    let header = [
        {
            name: 'NO URUT',
            rowspan: 2
        }
        , {
            name: 'NO INDUK',
            rowspan: 2
        }
        , {
            name: 'NAMA',
            rowspan: 2
        }
        , {
            name: 'TEMTALA',
            rowspan: 2
        }
        , {
            name: 'JENIS KELAMIN',
            rowspan: 2
        }
        , {
            name: 'NO. TELP',
            rowspan: 2
        }
        , {
            name: 'ALAMAT RUMAH',
            rowspan: 2
        }
        , {
            name: 'UNIT KERJA',
            rowspan: 2
        }
        , {
            name: 'TAHUN MASUK',
            rowspan: 2
        }
    ];

    let subheader = [];

    simp.forEach(function (x) {
        header.push({
            name: x.nama.toUpperCase(),
            colspan: x.nama.toLowerCase() == 'tabungan khusus'? 16 : 15
        })
        subheader.push({
            name: 'Awal Tahun',
            colspan: 1
        })
        namaBulan.forEach(function (bln) {
            subheader.push({
                name: bln,
                colspan: 1
            })
        })
        subheader.push({
            name: 'Tahun Berjalan',
            colspan: 1
        })
        if(x.nama.toLowerCase() == 'tabungan khusus'){
            subheader.push({
                name: 'Dibagi',
                colspan: 1
            })
        }
        subheader.push({
            name: 'Akhir Tahun',
            colspan: 1
        })
    });

    pj.forEach(function (x) {
        header.push({
            name: x.nama.toUpperCase(),
            colspan: 6
        })
        let y = [
            'SALDO AWAL'
            , 'ANGSURAN/ PELUNASAN SHU'
            , 'PELUNASAN NON SHU'
            , 'JASA USP REGULER'
            , 'PINJAMAN BARU'
            , 'SALDO AKHIR'
        ];
        y.forEach(function (g) {
            subheader.push({
                name: g,
                colspan: 1
            })
        })

    });


    let sumData = [];

    let headerExcel = [];

    headerExcel.push(header.map(function (q) {
        return {
            name: q.name,
            colspan: q.colspan != undefined ? q.colspan : 1,
            rowspan: q.rowspan != undefined ? q.rowspan : 1
        }
    }))
    headerExcel.push(subheader.map(function (q) {
        return {
            name: q.name,
            colspan: q.colspan != undefined ? q.colspan : 1
        }
    }));

    let dataObject = [];

    let dataSimpanan = {};

    simpanan.forEach(function (x) {
        if(!dataSimpanan[x.kodesp]){
            dataSimpanan[x.kodesp] = {};
        }
        if(!dataSimpanan[x.kodesp][x.bulan]){
            dataSimpanan[x.kodesp][x.bulan] = {};   
        }
        if(!dataSimpanan[x.kodesp][x.bulan][x.kode]){
            dataSimpanan[x.kodesp][x.bulan][x.kode] = [];   
        }
        dataSimpanan[x.kodesp][x.bulan][x.kode].push(x);
    });
    
    let dataPinjaman = {};
    let dataSHU = {};
    let dataNONSHU = {};
    pinjaman.forEach(function (x) {
        if(!dataPinjaman[x.kodesp]){
            dataPinjaman[x.kodesp] = {};
        }
        if(!dataPinjaman[x.kodesp][x.kode]){
            dataPinjaman[x.kodesp][x.kode] = [];   
        }
        if(!dataSHU[x.kodesp]){
            dataSHU[x.kodesp] = {};
        }
        if(!dataSHU[x.kodesp][x.kode]){
            dataSHU[x.kodesp][x.kode] = [];   
        }
        if(!dataNONSHU[x.kodesp]){
            dataNONSHU[x.kodesp] = {};
        }
        if(!dataNONSHU[x.kodesp][x.kode]){
            dataNONSHU[x.kodesp][x.kode] = [];   
        }
        dataPinjaman[x.kodesp][x.kode].push(x);
        if(x.tr == 'G'){
            dataSHU[x.kodesp][x.kode].push(x);
        }
        if(x.tr == 'L'){
            dataNONSHU[x.kodesp][x.kode].push(x);
        }
    })

    let x = 0;
    for (let ang of anggota) {
        let tmp = [];
        tmp.push(x + 1);
        tmp.push(ang.kode);
        tmp.push(ang.nama);
        tmp.push(`${ang.kota}, ${tanggal(ang.tgllahir).sekarang}`);
        tmp.push(ang.jk);
        tmp.push(ang.telp);
        tmp.push(ang.alamat);
        tmp.push(ang.unitkerja);
        tmp.push(ang.masuk.split('-')[0]);

        for(let h of simp){
            let jln = 0;
            
            let datAwl = data.simplast
            .cond(h.kode, 'kodesp')
            .cond(ang.kode, 'kode');
            
            let [dw] = datAwl; 
            let awl = dw? datAwl.sum('jumlah') : 0;

            sumData.push({
                kodesp: h.kode,
                bulan: '00',
                kode: 'awl',
                ang: ang.kode,
                value: Number(awl)
            });

            tmp.push(awl);
            
            namaBulan.forEach(function (b, i) {
                let bl = i + 1;
                let dat = dataSimpanan[h.kode] ? dataSimpanan[h.kode] : null;
                dat = dat && dat[bl] ? dat[bl] : null;
                dat = dat && dat[ang.kode] ? dat[ang.kode] : null;
                let [data] = dat?dat:[];
                if (data != undefined) {
                    sumData.push({
                        kodesp: h.kode,
                        bulan: bl.pad(2),
                        kode: 'sim',
                        ang: ang.kode,
                        value: Number(data.jumlah)
                    })
                    jln += Number(data.jumlah);
                    tmp.push(Number(data.jumlah));
                } else {
                    sumData.push({
                        kodesp: h.kode,
                        bulan: bl.pad(2),
                        kode: 'sim',
                        ang: ang.kode,
                        value: 0
                    })
                    tmp.push(0)
                }
            });
            sumData.push({
                kodesp: h.kode,
                bulan: '00',
                kode: 'jln',
                ang: ang.kode,
                value: Number(jln)
            });
            let dataTk = data.tk.cond(ang.kode, 'kode');
            let [tk] = dataTk;
            sumData.push({
                kodesp: h.kode,
                bulan: '00',
                kode: 'akr',
                ang: ang.kode,
                value: Number(awl + jln)
            });
            if(h.kode == 'TK'){
                h.kode == 'TK' && tk ?
                sumData.push({
                    kodesp: h.kode,
                    bulan: '00',
                    kode: 'bagi',
                    ang: ang.kode,
                    value: dataTk.sum('jumlah')
                })
                :
                sumData.push({
                    kodesp: h.kode,
                    bulan: '00',
                    kode: 'bagi',
                    ang: ang.kode,
                    value: 0
                })
            }
            tmp.push(Number(jln));
            if (h.kode == 'TK') {
                h.kode == 'TK' && tk?
                tmp.push(dataTk.sum('jumlah'))
                :
                tmp.push(0)
                ;
            }
            tmp.push(Number(awl + jln - (h.kode == 'TK' && tk?dataTk.sum('jumlah'):0) ));
        }

        pj.forEach(function (q) {
            let y = ['awl'
                , 'shu'
                , 'nonshu'
                , 'bunga'
                , 'baru'
                , 'akhir'
            ];

            function shu(q) {
                let { kode } = q;
                let dat = cekdata(dataSHU).search(kode).search(ang.kode).data;
                let g = dat?dat:[];
                let [data] = g;
                if (data != undefined) {
                    let { angpokok, kode } = data;
                    if (angpokok != null && angpokok != undefined) {
                        return {
                            pokok: angpokok.number(2)
                            ,kode : kode
                        };
                    }
                    return 0;
                }
                return 0;
            }

            function nonshu(q) {
                let { kode } = q;
                let dat = cekdata(dataNONSHU).search(kode).search(ang.kode).data;
                let g = dat?dat:[];
                let [data] = g;
                if (data != undefined) {
                    let { angpokok, kode } = data;
                    if (angpokok != null && angpokok != undefined) {
                        return {
                            pokok: angpokok.number(2)
                            , kode : kode
                        };
                    }
                    return 0;
                }
                return 0;
            }

            function bunga(q) {
                let { kode } = q;
                let dat = cekdata(dataPinjaman).search(kode).search(ang.kode).data;
                let g = dat?dat:[];
                if (g.length > 1) {
                    let v = g.map(function (cj) {
                        return {
                            pokok : cj.angbunga ? cj.angbunga.number(2) : 0
                            ,kode : cj.kode
                        }
                    }).sum('pokok');
                    return v;
                }
                let [data] = g;
                if (data != undefined) {
                    let { angbunga } = data;
                    if (angbunga != null && angbunga != undefined) {
                        return angbunga.number(2);
                    }
                    return 0;
                }
                return 0;
            }

            function pokok(q, ts = 0) {
                let { kode } = q;
                let dat = cekdata(dataPinjaman).search(kode).search(ang.kode).data;
                if(ts == 1){
                }
                let g = dat?dat:[];
                if (g.length > 1) {
                    let v = g.map(function (cj) {
                        return {
                            pokok : cj.pokok.number(2)
                            ,kode : cj.kode
                        }
                    }).sum('pokok');
                    return v;
                }
                let [data] = g;
                if (data != undefined) {
                    let { pokok } = data;
                    return pokok.number(2);
                }
                return 0;
            }

            y.forEach(function (s) {
                if (s == 'shu') {
                    sumData.push({
                        kodesp: q.kode,
                        bulan: '00',
                        kode: 'shu',
                        ang: typeof shu(q) == 'object' ? shu(q).kode : null,
                        value: typeof shu(q) == 'object' ? shu(q).pokok : shu(q)
                    })
                    tmp.push(shu(q));
                } else if (s == 'nonshu') {
                    sumData.push({
                        kodesp: q.kode,
                        bulan: '00',
                        kode: 'nonshu',
                        ang: typeof nonshu(q) == 'object' ? nonshu(q).kode : null,
                        value: typeof nonshu(q) == 'object' ? nonshu(q).pokok : nonshu(q)
                    })
                    tmp.push(nonshu(q));
                } else if (s == 'bunga') {
                    sumData.push({
                        kodesp: q.kode,
                        bulan: '00',
                        kode: 'bunga',
                        ang: typeof bunga(q) == 'object' ? bunga(q).kode : null,
                        value: typeof bunga(q) == 'object' ? bunga(q).pokok : bunga(q)
                    })
                    if (typeof bunga(q) == 'object') {
                    }
                    tmp.push(bunga(q));
                } else if (s == 'baru') {
                    sumData.push({
                        kodesp: q.kode,
                        bulan: '00',
                        kode: 'baru',
                        ang: typeof pokok(q) == 'object' ? pokok(q).kode : pokok(q),
                        value: typeof pokok(q) == 'object' ? pokok(q).pokok : pokok(q)
                    });
                    if (typeof pokok(q) == 'object'){
                    }
                    tmp.push(pokok(q));
                } else if (s == 'akhir') {
                    let vpokok = typeof pokok(q) == 'object' ? pokok(q).pokok : pokok(q);
                    let vnonshu = typeof nonshu(q) == 'object' ? nonshu(q).pokok : nonshu(q);
                    let vshu = typeof shu(q) == 'object' ? shu(q).pokok : shu(q);
                    let vang = typeof shu(q) == 'object' ? shu(q).kode : null;
                    vang = vang ? vang : ( typeof nonshu(q) == 'object' ? nonshu(q).kode : null);
                    vang = vang ? vang : ( typeof bunga(q) == 'object' ? bunga(q).kode : null);
                    vang = vang ? vang : ( typeof pokok(q) == 'object' ? pokok(q).kode : null);
                    sumData.push({
                        kodesp: q.kode,
                        bulan: '00',
                        kode: 'akhir',
                        ang: vang,
                        value: (vpokok - vnonshu - vshu)
                    })
                    if (isNaN(vpokok - vnonshu - vshu)){
                    }
                    tmp.push((vpokok - vnonshu - vshu));
                } else {
                    let vang = typeof shu(q) == 'object' ? shu(q).kode : null;
                    vang = vang ? vang : (typeof nonshu(q) == 'object' ? nonshu(q).kode : null);
                    vang = vang ? vang : (typeof bunga(q) == 'object' ? bunga(q).kode : null);
                    vang = vang ? vang : (typeof pokok(q) == 'object' ? pokok(q).kode : null);
                    sumData.push({
                        kodesp: q.kode,
                        bulan: '00',
                        kode: 'awl',
                        ang: vang,
                        value: 0
                    })
                    tmp.push(0);
                }
            });
        })

        dataObject.push(tmp);
        x++;
    }


    let dataSum = {};
    for(let itemOf of  sumData){
        if(itemOf.ang){
            if(!dataSum[itemOf.ang]){
                dataSum[itemOf.ang] = {};
            }
            if(!dataSum[itemOf.ang][itemOf.kodesp]){
                dataSum[itemOf.ang][itemOf.kodesp] = {};
            }
            if(!dataSum[itemOf.ang][itemOf.kodesp][itemOf.kode]){
                dataSum[itemOf.ang][itemOf.kodesp][itemOf.kode] = [];
            }
            dataSum[itemOf.ang][itemOf.kodesp][itemOf.kode].push(itemOf);
        }
    }

    return {
        header: headerExcel,
        body: dataObject,
        sum: sumData,
        sumData:dataSum
    };

}

const dataJson = tableCreator(data);
const dJson = dataJson.sumData;

let coloms = el('div').class('row');

let mapSelect2Anggota = data.anggota.map(function (q) {
    return {
        id: q.kode
        , text: q.nama
    }
});


$('#anggota').select2({
    data: mapSelect2Anggota,
    placeholder: 'Pilih Anggota',
    dropdownAutoWidth : true,
    multiple: true,
    dropdownCssClass: "select2-dropdown-scroll",
    width: '100%',
    containerCssClass: "select2-scroll",
    minimumResultsForSearch: Infinity
}).change(function () {
    let val = $(this).val();
    let getData = document.querySelectorAll('[data-ang]');
    if(val.length > 0){
        for (let item of getData) {
            let kode = item.dataset.ang;
            let cek = val.indexOf(kode) >= 0?true:false;
            item.style.display = cek?'block':'none';
        }
    }else{
        for (let item of getData) {
            item.style.display = 'block';
        }
    }
});

let totalsSimpanan = _json('totals');

for(let itemAng of data.anggota){   

    let dataInfo = el('div').class('row');
    if(dJson[itemAng.kode]){
        for(let itemKode of Object.keys(dJson[itemAng.kode])){
            let [akr] = dJson[itemAng.kode][itemKode].akr?dJson[itemAng.kode][itemKode].akr:[];
            let [ttl] = totalsSimpanan.cond(itemAng.kode,'kode').cond(itemKode, 'kodesp')
            dataInfo.child(
                el('div').class('col-sm-12 col-md-6 col-lg-6')
                .child(
                    el('div').css({
                        borderBottom: '1px solid #ddd'
                        , display: 'grid', gridTemplateColumns: '70px calc(100% - 80px)'
                        , gridGap: '10px'
                    })
                    .child(
                        el('h6').css({
                            display: 'flex',
                            color: 'black',
                            alignItems: 'center',
                            textAlign: 'left',
                            padding: '0px 10px',
                            margin: '0',
                            height: '24px',
                            whiteSpace: 'nowrap'
                        }).class('btn btn-sm btn-success').text(itemKode)
                    )
                    .child(
                        el('div').child(
                            el('p').margin('0px').css({textAlign: 'right'})
                            .html(`<span style="float:left">Rp</span>`+(ttl && ttl.jumlah? ttl.jumlah: 0).currency(2))
                        )
                    )
                )
            )
        }    
    }

    let card = el('div').data('ang', itemAng.kode).css('margin-bottom', '10px').class('col-sm-12 col-lg-6');
    card.child(
        el('div').class('card').height("100%").margin("10px 0px").child(
            el('div').class('card-body').child(
                el('div').class('row')
                .child(
                    el('div').class('text-center').class('col-sm-2 col-lg-3')
                    .child(
                        el('img').css({maxWidth:'80px'}).class('img-fluid').attr('src', "https://cdn-icons-png.flaticon.com/512/9187/9187604.png")
                    )
                )
                .child(
                    el('div').class('col-sm-10 col-lg-9')
                    .child(
                        el('h5').margin('0px').class('card-title').text(itemAng.nama)
                    )
                    .child(
                        el('p').margin(0).css({
                            display: 'grid',
                            gridTemplateColumns: '70px calc(100% - 80px)',
                            gridGap: '10px'
                        })
                        .child(
                            el('span').text(`Kode`)
                        )
                        .child(
                            el('p').margin('0px').text(': '+itemAng.kode)
                        )
                    )
                    .child(
                        el('p').margin(0).css({
                            display: 'grid',
                            gridTemplateColumns: '70px calc(100% - 80px)',
                            gridGap: '10px'
                        })
                        .child(
                            el('span').text(`Alamat`)
                        )
                        .child(
                            el('p').margin('0px').text(`: `+itemAng.alamat)
                        )
                    )
                )
                .child(
                    el('div').css({
                        marginTop: '10px'
                    }).class('col-12')
                    .child(dataInfo)
                )
            )
        )
    );

    coloms.child(card);
}

kekayaan.appendChild(coloms.get());


let myObject = [];


function toAA(col,row){
    var dd = "";
    var aa = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"];

    while(col>0){
        var mod = (col-1) % 26;
        col = parseInt((col-1)/26);
        dd = aa[mod] + dd;
    }

    return dd + row;
}
let headName = [];
let merge = [];
let started = 0;
let [dataHead, subHead] = dataJson.header;
started++;
let n = 0;
let p = [];
let subT = [];
for(let itemH of dataHead){
    let c = n;
    let x = itemH.colspan ? itemH.colspan : started;
    let y = itemH.rowspan ? itemH.rowspan : started;
    n +=  x;
    if(x>1){
        p.push(itemH.name);
        for(let i=0;i<(x-1);i++) {
            p.push("");
        }; //x
        merge.push({ s: toAA(c + 1, started), e: toAA(n, started) });
    }else{
        p.push(itemH.name);
        subT.push("");
        merge.push({ s: toAA(c+1,1), e: toAA(c+1,y) });
    }
}

for (let sH of subHead){
    subT.push(sH.name);
}

myObject.push(p);
myObject.push(subT);

myObject = myObject.concat(dataJson.body);

let excelTyle = {
    header : {
        fill: {
            fgColor: {
                rgb: '0070C0'
            },
            patternType: 'solid'
        },
        font: {
            color: {
                rgb: 'FFFFFF'
            }
        },
        border: {
            left: {
                style: 'thin',
                color: { rgb: 'FFFFFF' }
            },
            right: {
                style: 'thin',
                color: { rgb: 'FFFFFF' }
            },
            top: {
                style: 'thin',
                color: { rgb: 'FFFFFF' }
            },
            bottom: {
                style: 'thin',
                color: { rgb: 'FFFFFF' }
            }
        },
        alignment: {
            horizontal: 'center',
            vertical: 'center',
            wrapText: true
        }
    }
    ,body : {
        fill: {
            fgColor: {
                rgb: 'FFFFFF'
            },
            patternType: 'solid'
        },
        font: {
            sz: 10,
            color: {
                rgb: '000000'
            }
        },
        border: {
            left: {
                style: 'thin',
                color: { rgb: 'C0C0C0' }
            },
            right: {
                style: 'thin',
                color: { rgb: 'C0C0C0' }
            },
            top: {
                style: 'thin',
                color: { rgb: 'C0C0C0' }
            },
            bottom: {
                style: 'thin',
                color: { rgb: 'C0C0C0' }
            }
        },
        alignment: {
            horizontal: 'left',
            vertical: 'center',
        },
    }
    ,body2 : {
        fill: {
            fgColor: {
                rgb: 'FFFFFF'
            },
            patternType: 'solid'
        },
        font: {
            sz: 10,
            color: {
                rgb: '000000'
            }
        },
        border: {
            left: {
                style: 'thin',
                color: { rgb: 'C0C0C0' }
            },
            right: {
                style: 'thin',
                color: { rgb: 'C0C0C0' }
            },
            top: {
                style: 'thin',
                color: { rgb: 'C0C0C0' }
            },
            bottom: {
                style: 'thin',
                color: { rgb: 'C0C0C0' }
            }
        },
        alignment: {
            horizontal: 'right',
            vertical: 'center',
        },
    }
}


window.exportWSPlus = function() {
    let myFile = "Kekayaan Anggota " + tanggal(Date.now()).sekarang2 +'-'+Date.now()+".xlsx";

    let jjAr = [];
    for (let jj = 0; jj < n; jj++) {
        jjAr.push('');
    }

    myObject.push(jjAr);

    let myWorkSheet = XLSX.utils.aoa_to_sheet(myObject);

    let wscols = [
        { wch: 6 }
        , { wch: 6 }
        , { wch: 20 }
        , { wch: 20 }
        , { wch: 8 }
        , { wch: 15 }
        , { wch: 28 }
        , { wch: 26 }
        , { wch: 7 }
    ];
    let ws = wscols.length;
    let sisa = n - wscols.length;

    while(sisa > 0){
        wscols.push({ wch: 16 });
        sisa--;
    }

    myWorkSheet['!cols'] = wscols;
    
    for(let i=1;i <= n;i++){
        myWorkSheet[ toAA(i,1) ].s = excelTyle.header;
        myWorkSheet[ toAA(i,2) ].s = excelTyle.header;
        for (let s = 0; s < dataJson.body.length; s++) {
            if(i >= ws ){
                myWorkSheet[toAA(i, (3 + s))] ? myWorkSheet[ toAA(i, (3+s) ) ].s = excelTyle.body2:null;
                myWorkSheet[toAA(i, (3 + s))] ? myWorkSheet[toAA(i, (3 + s))].z = '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)':null;
            }else{
                myWorkSheet[toAA(i, (3 + s))] ? myWorkSheet[ toAA(i, (3+s) ) ].s = excelTyle.body:null;
            }
        }
    }
    for (let ix = 1; ix <= n; ix++) {
        if(ix > 9){
            myWorkSheet[ toAA(ix, (myObject.length)) ].f = "SUM("+toAA(ix,3)+":"+toAA(ix, (myObject.length-1) )+")";
            myWorkSheet[ toAA(ix, (myObject.length)) ].s = excelTyle.body2;
            myWorkSheet[toAA(ix, (myObject.length))].z = '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)'; // format currency IDR
        }
    }
    

    myWorkSheet['!merges'] = merge;
    var myWorkBook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(myWorkBook, myWorkSheet, "Kekayaan Anggota");
    XLSX.writeFile(myWorkBook, myFile);
}