<?php

//Hak Cipta dilindungi Undang-undang. Hak Cipta : Khamdi Rahmani, Reg. Kemenkum HAM No : 000121327. Dilarang merubah isi File tanpa ijin tertulis dari pemegang Hak Cipta. 

class migrasi
{
    public function cekTabel($dataBase, $table)
    {
        $db = database::getInstance();
        $query = $db->runQuery("SHOW TABLES  FROM {$dataBase} LIKE '%{$table}%'")->rowCount();
        return $query;
    }

    private function getTabel($tabel, $tahun)
    {
        $tahun_tabel = substr($tahun, 2, 2) . substr($tahun, 7, 2);
        $value = $tabel . $tahun_tabel;
        return $value;
    }

    private function create($tabel, $tahun, $array)
    {
        $tabel = $this->getTabel($tabel, $tahun);
        $cekDB = new database();
        $cekDB = $cekDB->cekDatabase();

        $cek = $this->cekTabel($cekDB, $tabel);
        $db = database::getInstance();
        $query = "CREATE table {$tabel} ({$array});";
        if ($cek == 0) {
            $create = $db->runQuery($query);
            $sql_index = "ALTER TABLE `{$tabel}` ADD INDEX `id` (`id`);";
            $create =  $db->runQuery($sql_index);
        }
    }

    public function install($tahun)
    {
        $this->install_siswa('tb_siswa', $tahun);
        $this->install_siswa_min('tb_siswa_min', $tahun);
        $this->install_transaksi_aks('db_transaksi', $tahun);
        $this->install_dbsetor('db_setor', $tahun);
        $this->install_simakom_akun('simakom_akun', $tahun);
        $this->install_simakom_detail('simakom_detail', $tahun);
        $this->install_simakom_kegiatan('simakom_kegiatan', $tahun);
        $this->install_simakom_komponen('simakom_komponen', $tahun);
        $this->install_simakom_transaksi('simakom_transaksi', $tahun);
        $this->install_simakom_lpj('simakom_lpj', $tahun);
        $this->install_simakom_jurnal('simakom_jurnal', $tahun);
        $this->install_revisi('simakom_revisi', $tahun);
        $this->install_tabungan_siswa('tabungan_siswa', $tahun);
        $this->install_tabungan_transaksi('tabungan_transaksi', $tahun);
        $this->install_payment('payment_list', $tahun);
        //uUpdate
        $this->updateTabel($tahun);
    }

    private function install_payment($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            tanggal DATE NOT NULL,
            nis VARCHAR( 18 ) NOT NULL,
            nama VARCHAR( 120 ) NOT NULL,
            jenjang VARCHAR( 10 ) NOT NULL,
            paralel VARCHAR( 50 ) NOT NULL,
            hp VARCHAR( 18 ) NOT NULL,
            email VARCHAR( 100 ) NOT NULL,
            jumlah VARCHAR( 15 ) NOT NULL,
            status VARCHAR( 15 ) NOT NULL,
            keterangan TEXT NULL,
            payment_id VARCHAR( 50 ) NOT NULL,
            tahun VARCHAR( 9 ) NOT NULL,
            created_at DATETIME NOT NULL,
            id VARCHAR( 50 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }

    private function install_dbsetor($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            tanggal_draf DATETIME NOT NULL,
            jumlah VARCHAR( 15 ) NOT NULL,
            uraian TEXT NOT NULL,
            setor_status VARCHAR( 10 ) NOT NULL,
            setor_tanggal DATETIME NOT NULL,
            terima_status VARCHAR( 10 ) NOT NULL,
            terima_tanggal DATETIME NOT NULL,
            penyetor VARCHAR( 50 ) NOT NULL,
            penerima VARCHAR( 50 ) NOT NULL,
            id_rkas VARCHAR( 50 ) NOT NULL,
            tahun VARCHAR( 10 ) NOT NULL,
            id_setor VARCHAR( 50 ) NOT NULL,
            created_by VARCHAR( 50 ) NOT NULL,
            created_at DATETIME NOT NULL,
            id VARCHAR( 50 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }

    private function install_siswa($tabel, $tahun)
    {
        $array = "
            no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            nama VARCHAR( 100 ) NOT NULL,
            nis VARCHAR( 18 ) NOT NULL,
            kelamin VARCHAR( 20 ) NOT NULL,
            jenjang VARCHAR( 10 ) NOT NULL,
            paralel VARCHAR( 20 ) NOT NULL,
            ortu VARCHAR( 75 ) NULL,
            alamat TEXT NULL,
            tang_A VARCHAR( 10 ) NOT NULL,
            tang_B VARCHAR( 10 ) NOT NULL,
            tang_C VARCHAR( 10 ) NOT NULL,
            tang_D VARCHAR( 10 ) NOT NULL,
            tang_E VARCHAR( 10 ) NOT NULL,
            tang_F VARCHAR( 10 ) NOT NULL,
            tang_G VARCHAR( 10 ) NOT NULL,
            tang_H VARCHAR( 10 ) NOT NULL,
            tang_I VARCHAR( 10 ) NOT NULL,
            tang_J VARCHAR( 10 ) NOT NULL,
            tang_K VARCHAR( 10 ) NOT NULL,
            tang_L VARCHAR( 10 ) NOT NULL,
            tang_M VARCHAR( 10 ) NOT NULL,
            tang_N VARCHAR( 10 ) NOT NULL,
            tang_O VARCHAR( 10 ) NOT NULL,
            tang_P VARCHAR( 10 ) NOT NULL,
            tang_Q VARCHAR( 10 ) NOT NULL,
            tang_R VARCHAR( 10 ) NOT NULL,
            tang_S VARCHAR( 10 ) NOT NULL,
            tang_T VARCHAR( 10 ) NOT NULL,
            tang_U VARCHAR( 10 ) NOT NULL,
            tang_V VARCHAR( 10 ) NOT NULL,
            tang_W VARCHAR( 10 ) NOT NULL,
            tang_X VARCHAR( 10 ) NOT NULL,
            tang_Y VARCHAR( 10 ) NOT NULL,
            tang_Z VARCHAR( 10 ) NOT NULL,
            status VARCHAR( 15 ) NOT NULL,
            email VARCHAR( 75 ) NOT NULL,
            hp VARCHAR( 15 ) NOT NULL,
            kategori VARCHAR( 200 ) NOT NULL,
            startAt VARCHAR( 2 ) NOT NULL,
            id VARCHAR( 50 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }
    private function install_siswa_min($tabel, $tahun)
    {
        $array = "
            no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            nama VARCHAR( 100 ) NOT NULL,
            nis VARCHAR( 18 ) NOT NULL,
            jenjang VARCHAR( 10 ) NOT NULL,
            paralel VARCHAR( 20 ) NOT NULL,
            id VARCHAR( 50 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }


    private function install_revisi($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            revisi FLOAT NOT NULL,
            tanggal DATE NOT NULL,
            time DATETIME NOT NULL,
            id VARCHAR( 100 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }
    private function install_transaksi_aks($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            tanggal DATETIME NOT NULL,
            nis VARCHAR( 18 ) NOT NULL,
            nama VARCHAR( 100 ) NOT NULL,
            kelamin VARCHAR( 2 ) NOT NULL,
            jenjang VARCHAR( 5 ) NOT NULL,
            paralel VARCHAR( 50 ) NOT NULL,
            kategori VARCHAR( 30 ) NOT NULL,
            jenis VARCHAR( 2 ) NOT NULL,
            tahap FLOAT NOT NULL,
            jumlah VARCHAR( 15 ) NOT NULL,
            random VARCHAR( 20 ) NOT NULL,
            penerima VARCHAR( 100 ) NOT NULL,
            setor VARCHAR( 10 ) NOT NULL,
            id_setor VARCHAR( 50 ) NOT NULL,
            metode VARCHAR( 20 ) NOT NULL,
            catatan TEXT NULL,
            time DATETIME NOT NULL,
            ip VARCHAR( 30 ) NOT NULL,
            browser TEXT NULL,
            id_tr VARCHAR( 50 ) NOT NULL,
            id VARCHAR( 50 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }
    private function install_simakom_detail($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            kategori VARCHAR( 1 ) NOT NULL,
            kegiatan VARCHAR( 4 ) NOT NULL,
            komponen VARCHAR( 5 ) NOT NULL,
            akun FLOAT NOT NULL,
            detail FLOAT NOT NULL,
            detail_id VARCHAR( 20 ) NOT NULL,
            uraian TEXT NOT NULL,
            volume VARCHAR( 15 ) NOT NULL,
            satuan VARCHAR( 50 ) NOT NULL,
            unit VARCHAR( 50 ) NOT NULL,
            total VARCHAR( 15 ) NOT NULL,
            komponen_1 VARCHAR( 10 ) NOT NULL,
            komponen_2 VARCHAR( 10 ) NOT NULL,
            komponen_3 VARCHAR( 10 ) NOT NULL,
            komponen_4 VARCHAR( 10 ) NOT NULL,
            unit_1 VARCHAR( 100 ) NOT NULL,
            unit_2 VARCHAR( 100 ) NOT NULL,
            unit_3 VARCHAR( 100 ) NOT NULL,
            unit_4 VARCHAR( 100 ) NOT NULL,
            pembiayaan_id VARCHAR( 50 ) NOT NULL,
            pembiayaan_kegiatan VARCHAR( 4 ) NOT NULL,
            pembiayaan_komponen VARCHAR( 5 ) NOT NULL,
            pembiayaan_akun VARCHAR( 6 ) NOT NULL,
            revisi VARCHAR( 10 ) NOT NULL,
            time DATETIME NOT NULL,
            kode_id VARCHAR( 20 ) NOT NULL,
            id VARCHAR( 100 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }
    private function install_simakom_akun($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            kategori VARCHAR( 1 ) NOT NULL,
            kegiatan VARCHAR( 4 ) NOT NULL,
            komponen VARCHAR( 5 ) NOT NULL,
            akun FLOAT NOT NULL,
            uraian TEXT NOT NULL,
            kode_id VARCHAR( 20 ) NOT NULL,
            akun_id VARCHAR( 15 ) NOT NULL,
            id VARCHAR( 100 ) NOT NULL, 
            revisi VARCHAR( 10 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }
    private function install_simakom_kegiatan($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            kategori VARCHAR( 1 ) NOT NULL,
            kegiatan VARCHAR( 4 ) NOT NULL,
            uraian TEXT NOT NULL,
            kode_id VARCHAR( 20 ) NOT NULL,
            id VARCHAR( 100 ) NOT NULL,
            revisi VARCHAR( 10 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }
    private function install_simakom_komponen($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            kategori VARCHAR( 1 ) NOT NULL,
            kegiatan VARCHAR( 4 ) NOT NULL,
            komponen VARCHAR( 5 ) NOT NULL,
            uraian TEXT NOT NULL,
            kode_id VARCHAR( 20 ) NOT NULL,
            id VARCHAR( 100 ) NOT NULL,
            revisi VARCHAR( 10 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }
    private function install_simakom_jurnal($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            tanggal DATE NOT NULL,
            jenis VARCHAR( 2 ) NOT NULL,
            jumlah VARCHAR( 15 ) NOT NULL,
            uraian TEXT NOT NULL,
            id VARCHAR( 40 ) NOT NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            created_by VARCHAR( 50 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }
    private function install_simakom_transaksi($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            tanggal DATE NOT NULL,    
            bukti FLOAT NOT NULL,  
            bukti_sistem FLOAT NOT NULL, 
            jenis FLOAT NOT NULL,       
            kategori VARCHAR(3) NOT NULL,
            rkas_kategori VARCHAR(1) NOT NULL,
            rkas_kegiatan VARCHAR(5) NOT NULL,
            rkas_komponen VARCHAR(5) NOT NULL,
            rkas_akun FLOAT NOT NULL,
            rkas_detail_id VARCHAR( 25 )  NOT NULL,
            uraian TEXT NOT NULL,
            debet VARCHAR( 15 ) NOT NULL,
            kredit VARCHAR( 15 ) NOT NULL,
            penerima VARCHAR( 100 ) NULL,
            penerima_jabatan VARCHAR( 100 ) NULL,
            pembiayaan_id VARCHAR( 50 ) NOT NULL,
            pembiayaan_kegiatan VARCHAR( 4 ) NOT NULL,
            pembiayaan_komponen VARCHAR( 5 ) NOT NULL,
            pembiayaan_akun VARCHAR( 6 ) NOT NULL,
            pajak TINYINT ( 1 ) NOT NULL,
            pajak_akun VARCHAR( 10 ) NOT NULL,
            revisi VARCHAR( 10 ) NOT NULL,
            id_setor VARCHAR( 25 ) NOT NULL,
            time DATETIME NOT NULL,
            id VARCHAR( 100 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }
    private function install_simakom_lpj($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            tanggal DATE NOT NULL, 
            periode VARCHAR(2) NOT NULL,   
            tahun VARCHAR(9) NOT NULL,
            bku_awal VARCHAR(15) NOT NULL,
            bku_debet VARCHAR(15) NOT NULL,
            bku_kredit VARCHAR(15) NOT NULL,
            bku_akhir VARCHAR(15) NOT NULL,
            bank_awal VARCHAR(15) NOT NULL,
            bank_debet VARCHAR(15) NOT NULL,
            bank_kredit VARCHAR(15) NOT NULL,
            bank_akhir VARCHAR(15) NOT NULL,
            tunai_awal VARCHAR(15) NOT NULL,
            tunai_debet VARCHAR(15) NOT NULL,
            tunai_kredit VARCHAR(15) NOT NULL,
            tunai_akhir VARCHAR(15) NOT NULL,
            bank_riil VARCHAR(15) NOT NULL,
            tunai_riil VARCHAR(15) NOT NULL,
            bank_selisih VARCHAR(15) NOT NULL,
            tunai_selisih VARCHAR(15) NOT NULL,
            selisihtunai_keterangan1 TEXT NOT NULL,  
            selisihtunai_keterangan2 TEXT NOT NULL,  
            selisihbank_keterangan1 TEXT NOT NULL,  
            selisihbank_keterangan2 TEXT NOT NULL, 
            rekening_nama1 TEXT NOT NULL, 
            rekening_jumlah1 VARCHAR(15) NOT NULL,
            rekening_nama2 TEXT NOT NULL, 
            rekening_jumlah2 VARCHAR(15) NOT NULL,
            rekening_nama3 TEXT NOT NULL, 
            rekening_jumlah3 VARCHAR(15) NOT NULL,
            catatan TEXT NOT NULL, 
            pecahan_100 VARCHAR(10) NOT NULL,  
            pecahan_75 VARCHAR(10) NOT NULL,  
            pecahan_50 VARCHAR(10) NOT NULL,      
            pecahan_20 VARCHAR(10) NOT NULL,      
            pecahan_10 VARCHAR(10) NOT NULL,      
            pecahan_5 VARCHAR(10) NOT NULL,      
            pecahan_2 VARCHAR(10) NOT NULL,      
            pecahan_1 VARCHAR(10) NOT NULL,      
            pecahan_05 VARCHAR(10) NOT NULL,  
            pecahan_02 VARCHAR(10) NOT NULL,  
            pecahan_01 VARCHAR(10) NOT NULL,
            pecahan_005 VARCHAR(10) NOT NULL,        
            id VARCHAR( 30 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }
    private function install_tabungan_siswa($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            nama VARCHAR(100) NOT NULL, 
            nis VARCHAR( 18 ) NOT NULL, 
            rekening VARCHAR( 20 ) NOT NULL, 
            kelamin VARCHAR( 2 ) NOT NULL,     
            saldo VARCHAR( 15 ) NOT NULL, 
            status VARCHAR( 15 ) NOT NULL, 
            id VARCHAR( 50 ) NOT NULL,                      
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            created_by VARCHAR( 50 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }
    private function install_tabungan_transaksi($tabel, $tahun)
    {
        $array = "no INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            tanggal DATE NOT NULL,  
            nis VARCHAR( 18 ) NOT NULL,   
            nama VARCHAR(100) NOT NULL, 
            jenis VARCHAR( 2 ) NOT NULL, 
            jumlah VARCHAR( 15 ) NOT NULL, 
            jurnal VARCHAR( 1 )  NOT NULL,    
            catatan TEXT NOT NULL,        
            created_by VARCHAR( 50 ) NOT NULL,
            created_at DATETIME NOT NULL,
            id VARCHAR( 50 ) NOT NULL";
        $create = $this->create($tabel, $tahun, $array);
        return $create;
    }

    //UPDATE TABEL
    public function updateTabel($tahun)
    {
        $this->update_tb_setor('db_setor', $tahun);
        $this->update_payment_list('payment_list', $tahun);
        $this->update_siswa_min('tb_siswa_min', $tahun);
        $this->update_whatsapp();
        $this->update_whatsapp2('whatsapp');
        $this->update_mailer();
        $this->update_tb_transaksi('db_transaksi', $tahun);
        $this->update_sekolah('sekolah');
    }


    private function update_tb_setor($tabel, $tahun)
    {
        $field = "metode";
        $tipe = "VARCHAR(2) NOT NULL";
        $afterfield = "penerima";
        $create = $this->update($tabel, $tahun, $field, $tipe, $afterfield);
        return $create;
    }
    private function update_payment_list($tabel, $tahun)
    {
        $field = "keterangan";
        $tipe = "TEXT NULL";
        $afterfield = "status";
        $create = $this->update($tabel, $tahun, $field, $tipe, $afterfield);
        return $create;
    }
    private function update_siswa_min($tabel, $tahun)
    {
        $field = "id";
        $tipe = "VARCHAR(50) NOT NULL";
        $afterfield = "paralel";
        $create = $this->update($tabel, $tahun, $field, $tipe, $afterfield);
        return $create;
    }

    public function cekField($table, $field)
    {
        $db = database::getInstance();
        $query = $db->runQuery("SHOW COLUMNS FROM {$table} LIKE '%{$field}%'")->rowCount();
        return $query;
    }

    private function update($tabel, $tahun, $field, $tipe, $afterfield)
    {
        $tabel = $this->getTabel($tabel, $tahun);
        $cek = $this->cekField($tabel, $field);
        $db = database::getInstance();
        if ($cek == 0) {
            $sql_index = "ALTER TABLE `{$tabel}` ADD `{$field}` {$tipe} AFTER `{$afterfield}`;";
            $create =  $db->runQuery($sql_index);
        }
    }
    private function update_sekolah($tabel)
    {
        $field = "webaddress";
        $tipe = "TEXT NOT NULL";
        $afterfield = "tp";
        $create = $this->update($tabel, '', $field, $tipe, $afterfield);
        return $create;
    }
    private function update_whatsapp2($tabel)
    {
        $field = "idinstance";
        $tipe = "TEXT NOT NULL";
        $afterfield = "device";
        $create = $this->update($tabel, '', $field, $tipe, $afterfield);
        return $create;
    }
    private function update_whatsapp()
    {
        $db = database::getInstance();
        $sql_index = "ALTER TABLE `whatsapp` CHANGE `aktif` `aktif` VARCHAR(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL; ";
        $create =  $db->runQuery($sql_index);
    }
    private function update_mailer()
    {
        $db = database::getInstance();
        $sql_index = "ALTER TABLE `mailer` CHANGE `aktif` `aktif` VARCHAR(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL; ";
        $create =  $db->runQuery($sql_index);
    }
    private function update_tb_transaksi($tabel, $tahun)
    {
        $field = "kategori";
        $tipe = "VARCHAR(30) NOT NULL";
        $afterfield = "paralel";
        $create = $this->update($tabel, $tahun, $field, $tipe, $afterfield);
        return $create;
    }
}
