<?php

//$conn = new mysqli( '172.20.60.11', 'thdbuser', 'thdbuser', 'db2' );
//if ( $conn->connect_errno ) {
//  die( "Failed to connect to MySQL: (" . $conn->connect_errno . ") " . $conn->connect_error );
//}

//db2 express c (v10.5)
$database = "172.20.60.11";
$user = "thdbuser";
$password = "thdbuser";

//$conn = db2_connect($database, $user, $password);
$connDB2 = odbc_connect('th03dta','THDBUSER','THDBUSER');

if($connDB2) {
    echo "DB2 Connection succeeded.<br/>rn";
} else {
    exit("failed".db2_conn_errormsg());
}

/**************************************/
/***** BEGIN // Declare VARIABLES *****/
/**************************************/
//$gendate  =   "VARCHAR_FORMAT(CURRENT TIMESTAMP + 5 DAYS, 'YYYYMMDD')";
$gendate    =   "VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYYMMDD')";
//$gendate  =   '20180718';
$gentime    =   "04:00:00";
/***** *END* // Declare VARIABLES *****/

$fp = fopen('D:DocumentsSMS Blastresult.ahk', 'w');

/**********************************************************/
/***** BEGIN // Initial Statement for AutoHotKey File *****/
/**********************************************************/
$initStmt = <<< InitialStatement
#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
; ^L:: run www.bing.com Sleep, 3000 run www.google.com Sleep, 3000 run www.yahoo.com


InitialStatement;
/***** *END* // Initial Statement for AutoHotKey File *****/

// Write Inital Statement in file
fwrite($fp, $initStmt);

/**********************************************************/
/***** BEGIN // SQL for Get Total KT and put in Array *****/
/**********************************************************/
// Declare SQL Statement
$bilKT = <<< SQLBilKT
SELECT DISTINCT(LEFT(A.S7KECF,4)) NOKT
--SELECT *
FROM TH03DTA.HJS7REP A
LEFT JOIN TH03DTA.HJKGCPP B ON A.S7KECF = B.KGXHCD
WHERE B.KGAEST = '2'
AND rtrim(CHAR(year(B.KGISDZ-2 days)))||substr(DIGITS(month(B.KGISDZ-2 days)),9)||substr(DIGITS(day(B.KGISDZ-2 days)),9) = $gendate
AND B.KGAUTZ > '$gentime'
SQLBilKT;

// debug:
echo $bilKT;

//run query
$querybilKT = odbc_exec($connDB2,$bilKT);

//set array
$arrayKT = array();

// look through query
while($myrow = odbc_fetch_array($querybilKT)){  // <--lots of rows

  // add each row returned into an array
  $arrayKT[] = $myrow;

}

echo count($arrayKT) ."rn";
// debug:
foreach($arrayKT as $row) {
   foreach($row as $key => $arrKTNo) {
//       echo $key . ': '. $value . "rn";
       echo "". $arrKTNo . "rn";


        /********************************************************/
        /***** BEGIN // SQL for Get All the KTs for the day *****/
        /********************************************************/
        // Declare SQL Statement
        $sql    =   <<< SQLSMSBlast
SELECT THSMS_NoTel, THFlightNo, THSMS_TptLapor, THSMS_LaporDT, THSMS_LaporTime, THSMS_WaktuLapor, THFlightDate
FROM (SELECT trim(A.NOKT) as THFlightNo, trim(A.NO_ACCT) as THSMS_NoAcc, trim(A.NAMA) as THSMS_Nama,
trim(A.TEL_BIMBIT) as THSMS_NoTel, trim(B.KGBPCE) as THSMS_FltNo, trim(B.KGATCD) as THSMS_DepOrigin, trim(B.KGPUCF) as THSMS_DepDest,
-- trim(CHAR(B.KGISDZ, USA)) as THSMS_LaporDT,
trim(REPLACE(CHAR(B.KGISDZ,EUR),'.','/')) as THSMS_LaporDT,
(SELECT CASE TRIM(AND4TW)

-- 20180702 KT2363   WHEN 'TH HOTEL KELANA JAYA (KOMPLEKS TABUNG HAJI KELANA JAYA)' THEN 'TH HOTEL KELANA JAYA'
WHEN 'MOVENPICK HOTEL & CONVENTION CENTRE KLIA, KOMPLEKS TH SEPANG' THEN 'MHCC KLIA'
WHEN 'TH HOTEL KOTA KINABALU (KOMPLEKS TABUNG HAJI KOTA KINABALU)' THEN 'TH HOTEL KOTA KINABALU'
WHEN 'TH HOTEL PENANG (KOMPLEKS TABUNG HAJI BAYAN LEPAS)' THEN 'TH HOTEL PENANG'
WHEN 'LAPANGAN TERBANG ANTARABANGSA SENAI' THEN 'LAPANGAN TERBANG SENAI'
WHEN 'TH HOTEL & CONVENTION CENTRE ALOR SETAR' THEN 'TH HOTEL ALOR SETAR'
WHEN 'LAPANGAN TERBANG ANTARABANGSA KUCHING' THEN 'LAPANGAN TERBANG KUCHING'
WHEN 'TH HOTEL & CONVENTION CENTRE, KUALA TERENGGANU' THEN 'TH HOTEL KUALA TERENGGANU'
WHEN 'KOMPLEKS ISLAM JUBLI PERAK SULTAN ISMAIL PETRA, PANJI KOTA BHARU' THEN 'KOMPLEKS ISLAM, PANJI KOTA BHARU'
WHEN 'KLIA, KAUNTER THTS, ARAS 5, BERDEKATAN OLDTOWN WHITE COFFEE,' THEN 'KLIA, KAUNTER THTS, ARAS 5'
ELSE AND4TW END
--SELECT *
FROM TH03DTA.HJANCPP WHERE ANATCD = KGATCD) AS THSMS_TptLapor,
SUBSTR(trim(CHAR(B.KGAUTZ, USA)),1,5) as THSMS_LaporTime,
CASE WHEN (CHAR(B.KGAUTZ) <='11:59:59') THEN 'PAGI'
WHEN(CHAR(B.KGAUTZ) <='12:59:59') THEN 'TENGAHARI'
WHEN(CHAR(B.KGAUTZ) <='18:59:59') THEN 'PETANG' ELSE 'MALAM'
END THSMS_WaktuLapor,
-- trim(CHAR(B.KGITDZ, USA)) as THSMS_DepDate,
trim(REPLACE(CHAR(B.KGITDZ,EUR),'.','/')) as THSMS_DepDate,
SUBSTR(trim(CHAR(B.KGAVTZ, USA)),1,5) as THSMS_DepTime,
CASE WHEN(CHAR(B.KGAVTZ) <='11:59:59') THEN 'PAGI'
WHEN(CHAR(B.KGAVTZ) <='12:59:59') THEN 'TENGAHARI'
WHEN(CHAR(B.KGAVTZ) <='18:59:59') THEN 'PETANG'
ELSE 'MALAM' END as THSMS_WaktuDep,
rtrim(CHAR(year(KGISDZ-2 days)))||substr(DIGITS(month(KGISDZ-2 days)),9)||substr(DIGITS(day(KGISDZ-2 days)),9) as THFlightDate
FROM (SELECT A.S7KECF NOKT, A.S7EECH NO_ACCT, A.S7EFCH NO_DAFTAR, A.S7KFCF NO_PASSPORT, A.S7D2TY NAMA, A.S7MBST JANTINA, A.S7KICF WARGANEGARA, A.S7AWCD JNS_JEMAAH,
A.S7KJCF NO_SIRI,B.JSLWCD ALAMAT1, B.JSLXCD ALAMAT2, B.JSLYCD ALAMAT3, B.JSL2CD ALAMAT4, B.JSJLCD TEL_RUMAH,'6'||B.JSGKCD TEL_BIMBIT
FROM TH03DTA.HJS7REP A  -- Manifest
left outer join TH03DTA.HJJSCPP B ON A.S7EECH = B.JSBFCD
AND A.S7EFCH = B.JSWRCD
AND  JSIHCE = '01'
WHERE B.JSGKCD != ''
AND S7Q5ST IN ('','0','2')      -- Jemaah Confirm
) A
LEFT JOIN TH03DTA.HJKGCPP B ON A.NOKT = B.KGXHCD
WHERE KGAEST = '2'      -- Jenis Penerbangan = DOMESTIK
AND A.NOKT LIKE '$arrKTNo%'
--AND trim(char(year(KGISDZ-2 days)))||substr(digits(month(KGISDZ-2 days)),9)||substr(digits(day(KGISDZ-2 days)),9) = VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYYMMDD')  --Flight Date
--AND trim(char(year(KGISDZ-2 days)))||substr(digits(month(KGISDZ-2 days)),9)||substr(digits(day(KGISDZ-2 days)),9) = 'gendate'  --Flight Date
) A
ORDER BY THFLIGHTNO, THSMS_NAMA
SQLSMSBlast;

        // <-- debug:
        //echo $sql;

        $query = odbc_exec($connDB2,$sql);
        //echo $query;
        //$result = $connDB2->query($query);    //dont mind these

        $sep = "',";
        $irow = 0;
        $ktno = "";


            // Declare all the Admin Phone Numbers
            $ADMIN_TEL          =   array("6084086", "601922203", "601147090", "601920060", "601964549", "601329092", "60277100", "6318451", "633184250", "643235649", "6017630", "6013386", "6012996");

            // Get
            $arrlength          =   count($ADMIN_TEL);
        /***** *END* Admin Name and Phone Number *****/

        /**/

        while(odbc_fetch_row($query)){

            $THSMS_NOTEL        =   odbc_result($query,'THSMS_NOTEL');
            $THFLIGHTNO         =   odbc_result($query,'THFLIGHTNO');
            $THSMS_TPTLAPOR     =   odbc_result($query,'THSMS_TPTLAPOR');
            $THSMS_LAPORDT      =   odbc_result($query,'THSMS_LAPORDT');
            $THSMS_LAPORTIME    =   odbc_result($query,'THSMS_LAPORTIME');
            $THSMS_WAKTULAPOR   =   odbc_result($query,'THSMS_WAKTULAPOR');
            $THSMS_START        =   "UrlDownloadToFile, https://www.etracker.cc/bulksms/mesapi.aspx?user=lthbulk&pass=lthbulk9001&type=0&to=$THSMS_NOTEL&from=LTH&text=";
            $THSMS_END          =   "&servid=MES01, createtemp";

            // FOR ADMIN
            if ( $irow == 0 || $ktno <> $THFLIGHTNO ) {
                $ktno   =   $THFLIGHTNO;
                for($x = 0; $x < $arrlength; $x++) {
                    $TXTADMIN_START =   "UrlDownloadToFile, https://www.etracker.cc/bulksms/mesapi.aspx?user=lthbulk&pass=lthbulk9001&type=0&to=".$ADMIN_TEL[$x]."&from=LTH&text=";
                    $adminteks = "INFO HAJI(ADMIN):Tn/Pn dikehendaki lapor diri ". $THFLIGHTNO . " di " . $THSMS_TPTLAPOR . " pada " . $THSMS_LAPORDT . " jam " . $THSMS_LAPORTIME . $THSMS_WAKTULAPOR . ".Jika ada sebarang pertanyaan,sila hubungi 03-62071919.";
                    $adminteks = str_replace(",","`,",$adminteks);
                    $adminresult = "Sleep, 2000rn".$TXTADMIN_START.$adminteks.$THSMS_END."rn";
                    fwrite($fp, $adminresult);
                }
            }

        //  $result = $THSMS_NOTEL . "$||$" . $THFLIGHTNO . "$||$" . $THSMS_TPTLAPOR . "$||$" . $THSMS_LAPORDT . "$||$" . $THSMS_LAPORTIME . "$||$" . $THSMS_WAKTULAPOR . "rn";
        //  Sleep, 3000
        //  run https://www.etracker.cc/bulksms/mesapi.aspx?user=lthbulk&pass=lthbulk9001&type=0&to=.$THSMS_NOTEL.&from=LTH&text=Test.&servid=MES01

            $teks   = "INFO HAJI:Tn/Pn dikehendaki lapor diri ". $THFLIGHTNO . " di " . $THSMS_TPTLAPOR . " pada " . $THSMS_LAPORDT . " jam " . $THSMS_LAPORTIME . $THSMS_WAKTULAPOR . ".Jika ada sebarang pertanyaan,sila hubungi 03-62071919.";
            $teks   = str_replace(",","`,",$teks);
            $result = "Sleep, 2000rn".$THSMS_START.$teks.$THSMS_END."rn";

            $irow++;

            fwrite($fp, $result);

        //  $result = mysqli_query($connMYS,$insSql) ;

        }

   }
}
/***** *END* // SQL for Get Total KT and put in Array *****/

fwrite($fp, "Sleep, 60000rnFileDelete, createtemp");

fclose($fp);

//$connDB2->close();
odbc_close($connDB2);

echo "rnAll result DONE!";

//sleep(5);

// THSMS_NOTEL
// INFO HAJI:Tn/Pn dikehendaki lapor diri
// --> THFLIGHTNO = KT01B1
// di
// --> THSMS_TPTLAPOR = TH HOTEL & CONVENTION CENTRE ALOR SETAR
// pada
// --> THSMS_LAPORDT = 24/07/2017
// jam
// --> THSMS_LAPORTIME = 01:50
// --> THSMS_WAKTULAPOR = pagi
// . Jika ada sebarang pertanyaan, sila hubungi 03-XXXXXXX.

?>

Fatal error: Uncaught Error: Call to undefined function odbc_connect() in C:xampphtdocsconnection.php:14 Stack trace: #0 C:xampphtdocsmainsms.php(2): include() #1 {main} thrown in C:xampphtdocsconnection.php on line 14