<?php
use Phalcon\Mvc\Model\Migration;

class LaporanGratifikasiMigration_228 extends Migration
{
    public function up()
    {
        self::$connection->execute("
            ALTER TABLE jaga.gratifikasi_laporan ADD COLUMN uang DOUBLE PRECISION;
            ALTER TABLE jaga.gratifikasi_laporan ADD COLUMN barang DOUBLE PRECISION;

            UPDATE jaga.gratifikasi_laporan SET jumlah = 1, barang = 5210843 WHERE wilayah_id = 23 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 2, uang = 2000000 WHERE wilayah_id = 27 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 1, uang = 14008058 WHERE wilayah_id = 5 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 84, uang = 293689682, barang = 209589049 WHERE wilayah_id = 2 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 1, uang = 3500000 WHERE wilayah_id = 3 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 6, uang = 4200000, barang = 1075000 WHERE wilayah_id = 4 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 7, uang = 17946542 WHERE wilayah_id = 6 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 2, uang = 4000000, barang = 979000 WHERE wilayah_id = 14 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 12, uang = 975000, barang = 3654000 WHERE wilayah_id = 17 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 3, barang = 5786500 WHERE wilayah_id = 35 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 2, uang = 11679133 WHERE wilayah_id = 32 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 2, uang = 500000, barang = 1000000 WHERE wilayah_id = 26 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 1, barang = 9000000 WHERE wilayah_id = 18 AND year = 2018;

            INSERT INTO jaga.gratifikasi_laporan(year, jenis, provinsi, provinsi_code, jumlah, wilayah_id, uang, barang)
            VALUES
                (2019,'milik_negara','Bali','51',NULL,23,NULL,NULL),
                (2019,'milik_negara','Bangka Belitung','19',NULL,30,NULL,NULL),
                (2019,'milik_negara','Banten','36',1,29,NULL,5000000),
                (2019,'milik_negara','Bengkulu','17',NULL,27,NULL,NULL),
                (2019,'milik_negara','DI Yogyakarta','34',30,5,36422115,17600000),
                (2019,'milik_negara','DKI Jakarta','31',22,2,7750000,14719485),
                (2019,'milik_penerima','DKI Jakarta','31',1,2,7750000,14719485),
                (2019,'sebagian_negara','DKI Jakarta','31',1,2,7750000,14719485),
                (2019,'milik_negara','Gorontalo','75',NULL,31,NULL,NULL),
                (2019,'milik_negara','Jambi','15',NULL,11,NULL,NULL),
                (2019,'milik_negara','Jawa Barat','32',1,3,1000000,1000000),
                (2019,'milik_negara','Jawa Tengah','33',3,4,400000,100000),
                (2019,'milik_negara','Jawa Timur','35',1,6,1200000,NULL),
                (2019,'milik_penerima','Jawa Timur','35',3,6,1200000,NULL),
                (2019,'milik_negara','Kalimantan Barat','61',NULL,14,NULL,NULL),
                (2019,'milik_negara','Kalimantan Selatan','63',NULL,16,NULL,NULL),
                (2019,'milik_negara','Kalimantan Tengah','62',NULL,15,NULL,NULL),
                (2019,'milik_negara','Kalimantan Timur','61',NULL,14,NULL,NULL),
                (2019,'milik_negara','Kalimantan Utara','65',NULL,35,NULL,NULL),
                (2019,'milik_negara','Kepulauan Riau','21',1,32,1000000,NULL),
                (2019,'sebagian_negara','Lampung','18',1,13,NULL,NULL),
                (2019,'milik_negara','Maluku','81',NULL,22,NULL,NULL),
                (2019,'milik_negara','Maluku Utara','82',NULL,28,NULL,NULL),
                (2019,'milik_negara','Nangroe Aceh Darussalam','11',NULL,7,NULL,NULL),
                (2019,'milik_negara','Nusa Tenggara Barat','52',NULL,24,NULL,NULL),
                (2019,'milik_negara','Nusa Tenggara Timur','53',NULL,25,NULL,NULL),
                (2019,'milik_negara','Papua','94',NULL,26,NULL,NULL),
                (2019,'milik_negara','Papua Barat','91',NULL,33,NULL,NULL),
                (2019,'milik_negara','Riau','14',2,10,78669060,1850000),
                (2019,'milik_negara','Sulawesi Barat','76',NULL,34,NULL,NULL),
                (2019,'milik_negara','Sulawesi Selatan','73',NULL,20,NULL,NULL),
                (2019,'milik_negara','Sulawesi Tengah','72',NULL,19,NULL,NULL),
                (2019,'milik_negara','Sulawesi Tenggara',NULL,1,NULL,NULL,30300000),
                (2019,'milik_negara','Sulawesi Utara','71',NULL,18,NULL,NULL),
                (2019,'milik_negara','Sumatera Barat','13',1,9,2000000,NULL),
                (2019,'milik_negara','Sumatera Selatan','16',NULL,12,NULL,NULL),
                (2019,'milik_negara','Sumatera Utara','12',NULL,8,NULL,NULL);
        ");
    }
    public function down()
    {
        self::$connection->execute("
            ALTER TABLE jaga.gratifikasi_laporan DROP COLUMN uang;
            ALTER TABLE jaga.gratifikasi_laporan DROP COLUMN barang;
            DELETE FROM jaga.gratifikasi_laporan WHERE year = 2019;

            UPDATE jaga.gratifikasi_laporan SET jumlah = 19 WHERE wilayah_id = 23 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 10 WHERE wilayah_id = 27 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 44 WHERE wilayah_id = 5 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 603 WHERE wilayah_id = 2 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 32 WHERE wilayah_id = 3 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 33 WHERE wilayah_id = 4 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 35 WHERE wilayah_id = 6 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 18 WHERE wilayah_id = 14 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 47 WHERE wilayah_id = 17 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 4 WHERE wilayah_id = 35 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 16 WHERE wilayah_id = 32 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 14 WHERE wilayah_id = 26 AND year = 2018;
            UPDATE jaga.gratifikasi_laporan SET jumlah = 10 WHERE wilayah_id = 18 AND year = 2018;
        ");
    }
}