<?php
//Load up the configuration
$config_path = realpath(dirname(__FILE__) . '/../config.ini');
$config = load_config($config_path);
$db = get_db_connection();
exec_queries(array("DROP TABLE IF EXISTS weekend_events", "CREATE TABLE `weekend_events` (\n `event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,\n `category` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,\n `event_type` enum('processed','bounce','open','delivered','click','spamreport','dropped','deferred','unsubscribe') COLLATE utf8_unicode_ci DEFAULT NULL,\n `mailing_id` int(10) unsigned DEFAULT NULL,\n `job_id` int(10) unsigned DEFAULT NULL,\n `queue_id` int(10) unsigned DEFAULT NULL,\n `instance` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,\n `install_class` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,\n `servername` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,\n `dt_created` datetime DEFAULT NULL,\n `dt_received` datetime DEFAULT NULL,\n `is_test` tinyint(1) DEFAULT '0',\n PRIMARY KEY (`event_id`),\n KEY `event_type` (`event_type`),\n KEY `servername` (`servername`),\n KEY `is_test` (`is_test`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci", "SELECT @next := AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sendgridstats' AND TABLE_NAME = 'incoming'", 'SELECT @stmt := CONCAT("ALTER TABLE weekend_events AUTO_INCREMENT = ", @next)', 'PREPARE transfer_auto_inc FROM @stmt', 'EXECUTE transfer_auto_inc'));
$result = mysql_query("SELECT * FROM incoming_innodb", $db);
while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $key => $value) {
$row[$key] = mysql_real_escape_string($value, $db);
}
exec_queries(array("SET autocommit=0", "begin", "insert into weekend_events (email, category, event_type, mailing_id, job_id, queue_id, instance, install_class, servername, dt_created, dt_received, is_test) VALUES ('{$row['email']}', '{$row['category']}', '{$row['event_type']}', {$row['mailing_id']}, {$row['job_id']}, {$row['queue_id']}, '{$row['instance']}', '{$row['install_class']}', '{$row['servername']}', '{$row['dt_created']}', '{$row['dt_received']}', {$row['is_test']})", "SET @new_id := LAST_INSERT_ID()", "update {$row['event_type']} SET event_id=@new_id WHERE event_id={$row['event_id']}", "commit"));
}
exec_queries(array("SELECT @next := AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sendgridstats' AND TABLE_NAME = 'weekend_events'", 'SELECT @stmt := CONCAT("ALTER TABLE incoming AUTO_INCREMENT = ", @next)', 'PREPARE transfer_auto_inc FROM @stmt', 'EXECUTE transfer_auto_inc'));
///////////////////////////////////////////////////
// Copy pasta from callback.php past this point
//
function load_config($config_file)
{
// If we can't find and load the configuration file just die immediately
// SendGrid will put the event into a deferred queue and try again later
if (!($config = parse_ini_file($config_file, true))) {
error_out(500, "Configuration file not found at '{$config_file}'.");
}
if (!array_key_exists('database', $config)) {
_log(500, "Invalid config file. '{$section}' section required");
}
if (!array_key_exists('debug', $config)) {
$config['debug'] = array('debug_level' => 1);
}
// update phrase group list
$query[] = "UPDATE " . TABLE_PREFIX . "phrasetype SET title='{$phrasetype['accessmask']}', editrows=3, fieldname='accessmask' WHERE phrasetypeid=29";
$explain[] = sprintf($vbphrase['update_table'], TABLE_PREFIX . "phrasetype");
$query[] = "UPDATE " . TABLE_PREFIX . "phrasetype SET title='{$phrasetype['cron']}', editrows=3, fieldname='cron' WHERE phrasetypeid=30";
$explain[] = sprintf($vbphrase['update_table'], TABLE_PREFIX . "phrasetype");
$query[] = "UPDATE " . TABLE_PREFIX . "phrasetype SET title='{$phrasetype['moderator']}', editrows=3, fieldname='moderator' WHERE phrasetypeid=31";
$explain[] = sprintf($vbphrase['update_table'], TABLE_PREFIX . "phrasetype");
$query[] = "UPDATE " . TABLE_PREFIX . "phrasetype SET title='{$phrasetype['cpoption']}', editrows=3, fieldname='cpoption' WHERE phrasetypeid=32";
$explain[] = sprintf($vbphrase['update_table'], TABLE_PREFIX . "phrasetype");
$query[] = "UPDATE " . TABLE_PREFIX . "phrasetype SET title='{$phrasetype['cprank']}', editrows=3, fieldname='cprank' WHERE phrasetypeid=33";
$explain[] = sprintf($vbphrase['update_table'], TABLE_PREFIX . "phrasetype");
$query[] = "UPDATE " . TABLE_PREFIX . "phrasetype SET title='{$phrasetype['cpusergroup']}', editrows=3, fieldname='cpusergroup' WHERE phrasetypeid=34";
$explain[] = sprintf($vbphrase['update_table'], TABLE_PREFIX . "phrasetype");
$query[] = "UPDATE " . TABLE_PREFIX . "setting SET value = 'images/icons/icon1.gif' WHERE varname = 'showdeficon' AND value = 1";
$explain[] = sprintf($vbphrase['update_table'], TABLE_PREFIX . "setting");
exec_queries();
}
// #############################################################################
// step 2
if ($vbulletin->GPC['step'] == 2) {
require_once DIR . '/includes/functions_misc.php';
// change post title field
echo_flush("<p>" . sprintf($upgrade_phrases['upgrade_300b5.php']['alter_post_title'], TABLE_PREFIX) . "</i>");
$t = microtime();
$db->query_write("\n\t\tALTER TABLE " . TABLE_PREFIX . "post\n\t\tCHANGE title title VARCHAR(250) NOT NULL DEFAULT ''\n\t");
echo_flush('<br />' . sprintf($vbphrase['query_took'], number_format(fetch_microtime_difference($t), 2)) . '</p>');
// change thread title field
echo_flush("<p>" . sprintf($upgrade_phrases['upgrade_300b5.php']['alter_thread_title'], TABLE_PREFIX) . "</i>");
$t = microtime();
$db->query_write("\n\t\tALTER TABLE " . TABLE_PREFIX . "thread\n\t\tCHANGE title title VARCHAR(250) NOT NULL DEFAULT ''\n\t");
echo_flush('<br />' . sprintf($vbphrase['query_took'], number_format(fetch_microtime_difference($t), 2)) . '</p>');
/**
* Let's declare database initialization:
* my_pinjam_init_db
* is_installing: boolean, true for installing and false for removing.
*/
function my_pinjam_init_db($is_installing)
{
$tables = array();
$triggers = array();
// DO NOT FORGET TO CHANGE MYSQL COMMAND DELIMITER before executing triggers creation!
/**
Check triggers existence before creating them:
SELECT TRIGGER_NAME
FROM information_schema.triggers
WHERE TRIGGER_SCHEMA = 'db_name' AND TRIGGER_NAME = 'trigger_name';
or
SELECT TRIGGER_NAME
FROM information_schema.triggers
WHERE TRIGGER_SCHEMA = 'db_name' AND (TRIGGER_NAME in ('trigger_name_1','trigger_name_2','trigger_name_n'));
*/
$views = array();
$drops = array();
//master data inkubator
$tables['inkubator_master'] = "CREATE TABLE if not exists `inkubator_master` \n (\n \t`id` BIGINT(20) NOT NULL,\n \t`nama` VARCHAR(50) NOT NULL,\n \t`jumlah` INT(2) NOT NULL DEFAULT '0',\n \t`panjang` INT(2) NOT NULL DEFAULT '0' COMMENT 'cm',\n \t`lebar` INT(2) NOT NULL DEFAULT '0' COMMENT 'cm',\n \t`tinggi` INT(2) NOT NULL DEFAULT '0' COMMENT 'cm',\n \t`berat` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT 'kg',\n \t`tipe` TEXT NULL,\n \tPRIMARY KEY (`id`)\n )\n COLLATE='utf8_general_ci'\n ENGINE=MyISAM;";
// peminjaman
$tables['inkubator_pinjam'] = "CREATE TABLE if not exists `inkubator_pinjam` \n (\n \t`id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0', /* UUID_SHORT() */\n \t`kode_pinjam` VARCHAR(20) NOT NULL DEFAULT '',\n \t`id_inkubator` BIGINT(20) NOT NULL DEFAULT '0' COMMENT 'Diisi saat verifikasi peminjaman.', /* UUID_SHORT() */\n \t`tgl_pinjam` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n \t`nama_bayi` VARCHAR(75) NOT NULL DEFAULT '',\n \t`kembar` ENUM('Y','N') NOT NULL DEFAULT 'N',\n \t`tgl_lahir` DATE NULL DEFAULT NULL,\n \t`berat_lahir` DECIMAL(10,2) NOT NULL DEFAULT '0.00',\n \t`panjang_lahir` DECIMAL(10,2) NOT NULL DEFAULT '0.00',\n \t`kondisi` ENUM('SEHAT','SAKIT') NOT NULL DEFAULT 'SEHAT',\n \t`rumah_sakit` VARCHAR(50) NOT NULL DEFAULT '',\n \t`nama_dokter` VARCHAR(75) NOT NULL DEFAULT '',\n \t`tgl_pulang` DATE NULL DEFAULT NULL,\n \t`no_kk` VARCHAR(50) NOT NULL DEFAULT '',\n \t`alamat` TEXT NULL,\n \t`nama_ibu` VARCHAR(50) NOT NULL DEFAULT '',\n \t`hp_ibu` VARCHAR(20) NOT NULL DEFAULT '',\n \t`email_ibu` VARCHAR(50) NOT NULL DEFAULT '',\n \t`nama_ayah` VARCHAR(50) NOT NULL DEFAULT '',\n \t`hp_ayah` VARCHAR(20) NOT NULL DEFAULT '',\n \t`email_ayah` VARCHAR(50) NOT NULL DEFAULT '',\n \t`jumlah_pinjam` INT(2) NOT NULL DEFAULT '1',\n \t`status_pinjam` ENUM('Ditunda','Disetujui','Ditolak') NOT NULL DEFAULT 'Ditunda',\n \t`tgl_update_status_pinjam` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n \t`keterangan_status_pinjam` VARCHAR(200) NOT NULL DEFAULT '',\n \t`konfirmasi` ENUM('Y','N') NOT NULL DEFAULT 'N',\n `ktp_ibu` VARCHAR(16) NOT NULL DEFAULT '',\n\t `ktp_ayah` VARCHAR(16) NOT NULL DEFAULT '',\n `jenis_kelamin` ENUM('Laki-Laki','Perempuan') NOT NULL DEFAULT 'Laki-Laki',\n \tPRIMARY KEY (`id`)\n )\n COLLATE='utf8_general_ci'\n ENGINE=MyISAM;";
// pengembalian
$tables['inkubator_kembali'] = "CREATE TABLE if not exists `inkubator_kembali` \n (\n \t`id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',\n \t`kode_pinjam` VARCHAR(20) NOT NULL DEFAULT '',\n \t`id_inkubator` BIGINT(20) NOT NULL DEFAULT '0',\n \t`tgl_kembali` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n \t`jumlah_kembali` INT(2) NOT NULL DEFAULT '1',\n \t`status_kembali` ENUM('Ditunda','Diterima','Ditolak') NOT NULL DEFAULT 'Ditunda',\n \t`tgl_update_status_kembali` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n \t`keterangan_status_kembali` VARCHAR(200) NOT NULL DEFAULT '',\n \t`berat_kembali` DECIMAL(10,2) NOT NULL DEFAULT '0.00',\n \t`panjang_kembali` DECIMAL(10,2) NOT NULL DEFAULT '0.00',\n \t`kondisi_kembali` ENUM('SEHAT','SAKIT') NOT NULL DEFAULT 'SEHAT',\n \tPRIMARY KEY (`id`)\n )\n COLLATE='utf8_general_ci'\n ENGINE=MyISAM;";
// monitoring
$tables['inkubator_monitoring'] = "CREATE TABLE if not exists `inkubator_monitoring` \n (\n \t`id` BIGINT(20) NOT NULL,\n \t`kode_pinjam` VARCHAR(20) NOT NULL DEFAULT '',\n \t`tgl_input` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n \t`panjang_bayi` DECIMAL(10,2) NOT NULL DEFAULT '0.00',\n \t`berat_bayi` DECIMAL(10,2) NOT NULL DEFAULT '0.00',\n \t`kondisi` ENUM('SEHAT','SAKIT') NOT NULL DEFAULT 'SEHAT',\n \t`skor` DECIMAL(10,2) NOT NULL DEFAULT '0.00',\n \t`keterangan` TEXT NULL,\n \tPRIMARY KEY (`id`)\n )\n COLLATE='utf8_general_ci'\n ENGINE=MyISAM;";
// Views:
$views['vw_inkubator_perkembangan'] = "create or replace view `vw_inkubator_perkembangan` as select \n \tp.id,\n \ti.nama,\n \tp.kode_pinjam,\n \tp.id_inkubator,\n \tp.tgl_pinjam,\n \tp.nama_bayi,\n \tp.kembar,\n \tp.tgl_lahir,\n \tp.berat_lahir,\n \tp.panjang_lahir,\n \tp.kondisi,\n \tp.rumah_sakit,\n \tp.nama_dokter,\n \tp.tgl_pulang,\n \tp.no_kk,\n \tp.alamat,\n \tp.nama_ibu,\n \tp.hp_ibu,\n \tp.email_ibu,\n \tp.nama_ayah,\n \tp.hp_ayah,\n \tp.email_ayah,\n \tp.jumlah_pinjam,\n \tp.status_pinjam,\n \tp.tgl_update_status_pinjam,\n \tp.keterangan_status_pinjam,\n \tp.konfirmasi,\n \tcoalesce(count(m.id),0) as jumlah_data_monitor,\n \tcoalesce(sum(coalesce(m.skor,0)),0) as jumlah_skor_monitor,\n \t(case \n \t\twhen (coalesce(sum(coalesce(m.skor,0)),0) - coalesce(count(m.id),0) ) >= 0 then \n \t\t\t'Positif'\n \t\telse \n \t\t\t'Negatif'\t\n \tend) as perkembangan\n from inkubator_pinjam p\n inner join inkubator_master i on i.id = p.id_inkubator\n left join inkubator_monitoring m on m.kode_pinjam = p.kode_pinjam \n group by p.kode_pinjam ";
$views['vw_inkubator_pinjam'] = "create or replace view `vw_inkubator_pinjam` as select \n \tp.id,\n \ti.nama,\n \tp.kode_pinjam,\n \tp.id_inkubator,\n \tp.tgl_pinjam,\n \tp.nama_bayi,\n \tp.kembar,\n \tp.tgl_lahir,\n \tp.berat_lahir,\n \tp.panjang_lahir,\n \tp.kondisi,\n \tp.rumah_sakit,\n \tp.nama_dokter,\n \tp.tgl_pulang,\n \tp.no_kk,\n \tp.alamat,\n \tp.nama_ibu,\n \tp.hp_ibu,\n \tp.email_ibu,\n \tp.nama_ayah,\n \tp.hp_ayah,\n \tp.email_ayah,\n \tp.jumlah_pinjam,\n \tp.status_pinjam,\n \tp.tgl_update_status_pinjam,\n \tp.keterangan_status_pinjam,\n \tp.konfirmasi,\n \tk.tgl_kembali,\n \tk.berat_kembali,\n \tk.panjang_kembali,\n \tk.kondisi_kembali,\n \tk.jumlah_kembali,\n \tk.status_kembali,\n \tk.tgl_update_status_kembali,\n \tk.keterangan_status_kembali\n \t\n from inkubator_pinjam p\n inner join inkubator_master i on i.id = p.id_inkubator\n left join inkubator_kembali k on k.kode_pinjam = p.kode_pinjam ";
$views['vw_inkubator_tersedia'] = "create or replace view `vw_inkubator_tersedia` as select \n \ti.id,\n \ti.nama,\n \ti.panjang,\n \ti.lebar,\n \ti.tinggi,\n \ti.berat,\n \ti.tipe,\n \t(i.jumlah - sum(coalesce(p.jumlah_pinjam,0)) + sum(coalesce(k.jumlah_kembali,0)) ) as stok_inkubator\n from inkubator_master i\n left join inkubator_pinjam p on p.id_inkubator = i.id and p.status_pinjam = 'Disetujui'\n left join inkubator_kembali k on k.id_inkubator = i.id and k.status_kembali = 'Diterima'\n group by i.id ";
/**
* Do the job:
*/
$multi_query = "";
$check_triggers = "";
$trg_query = "";
if ($is_installing) {
// $f = fopen('d:/test-query.txt','w');
// create database items:
foreach ($views as $view => $query) {
$multi_query .= "drop view if exists `{$view}`;\n";
}
// no need to drop exisiting tables. You decide this.
// dropping tables also drop their triggers.
foreach ($tables as $table => $query) {
$multi_query .= $query . (substr($query, strlen($query) - 1) != ";" ? ";" : "") . "\n";
}
// Execute table and views first:
foreach ($views as $view => $query) {
$multi_query .= $query . (substr($query, strlen($query) - 1) != ";" ? ";" : "") . "\n";
}
// debug: fputs($f, $multi_query);
exec_queries($multi_query);
//
//
//
//
//
// Execute triggers:
foreach ($triggers as $trigger => $query) {
$check_triggers .= ",lower('{$trigger}')";
}
$check_triggers = "SELECT lower(TRIGGER_NAME) as trgn FROM information_schema.triggers WHERE TRIGGER_SCHEMA = '" . DB_DATABASE . "' \n AND (lower(TRIGGER_NAME) in (" . substr($check_triggers, 1) . "))";
$old_triggers = fetch_query($check_triggers);
$check_triggers = array();
foreach ($old_triggers as $idx => $col) {
$check_triggers[] = $old_triggers[$idx]['trgn'];
}
foreach ($triggers as $trigger => $query) {
if (!in_array(strtolower($trigger), $check_triggers)) {
// debug: fputs($f, $query .(substr($query, strlen($query)-1)!=";"?";":""));
exec_query($query . (substr($query, strlen($query) - 1) != ";" ? ";" : ""));
}
}
unset($old_triggers);
// indicate sucess:
// debug: fclose($f);
return true;
} else {
// remove database items:
return true;
}
}
