<?php
/* dev by https://www.facebook.com/botdeen.xx [Nurutdin Maetimoh] */
date_default_timezone_set("Asia/Bangkok");
$date_now = date("Y-m-d H:i:s");

/*  setting database */
$ip = "127.0.0.1";
$portdatabase = "*";
$username = "*";
$password = "*";
$dbname = "*";


/*  setting detail */
$servername = "";
$token = "";
$portapi = "443";

function connectDb()
{
    $connectionInfo = array( "Database"=>$GLOBALS['dbname'], "UID"=>$GLOBALS['username'], "PWD"=>$GLOBALS['password'], "CharacterSet" => "UTF-8" );
    $conn = sqlsrv_connect($GLOBALS['ip'].",".$GLOBALS['portdatabase'], $connectionInfo);
    return $conn;
}

$db = connectDb();
$sql_useronline = "SELECT uc.LastUpdateDate, uc.Gamertag, uc.CustomerID, ud.CustomerID, ud.IsDeveloper, ud.AccountType From UsersChars as uc JOIN UsersData as ud ON uc.CustomerID = ud.CustomerID
WHERE DATEDIFF(MINUTE, uc.LastUpdateDate, GETDATE()) <= 1";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_useronline = sqlsrv_query( $db, $sql_useronline , $params, $options );
$online = sqlsrv_num_rows($stmt_useronline );

$sql_devonline = "SELECT uc.LastUpdateDate, uc.Gamertag, uc.CustomerID, ud.CustomerID, ud.IsDeveloper, ud.AccountType From UsersChars as uc JOIN UsersData as ud ON uc.CustomerID = ud.CustomerID
WHERE DATEDIFF(MINUTE, uc.LastUpdateDate, GETDATE()) <= 1 AND ud.IsDeveloper > 1 ";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_devonline = sqlsrv_query( $db, $sql_devonline , $params, $options );
$dev = sqlsrv_num_rows($stmt_devonline );

$sql_acc = "SELECT * FROM UsersData";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_acc = sqlsrv_query( $db, $sql_acc , $params, $options );
$userall_acc = sqlsrv_num_rows( $stmt_acc );

$sql_ban = "SELECT * FROM UsersData WHERE AccountStatus = 200";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_ban = sqlsrv_query( $db, $sql_ban , $params, $options );
$user_ban = sqlsrv_num_rows( $stmt_ban );



$date_yy = date("Y");
$date_mm = date("m");
$date_dd = date("d");
$date_bb = date("d",strtotime("-1 days"));

$sql_sum_aa = "SELECT SUM(amount) FROM iWarZ_refill_tmpay WHERE status = 'success'";
$sql_sum_aa = sqlsrv_query( $db, $sql_sum_aa);
if( $sql_sum_aa === false ) {
     die( print_r( sqlsrv_errors(), true));
}
if( sqlsrv_fetch( $sql_sum_aa ) === false) {
     die( print_r( sqlsrv_errors(), true));
}
$sum_aa = sqlsrv_get_field( $sql_sum_aa, 0);

$sql_sum = "SELECT SUM(amount) FROM iWarZ_refill_tmpay
WHERE  (DATEPART(yy, [time]) = ".$date_yy."
AND    DATEPART(mm, [time]) = ".$date_mm."
AND    DATEPART(dd, [time]) = ".$date_dd.")
AND status = 'success'
";
$sql_sum = sqlsrv_query( $db, $sql_sum);
if( $sql_sum === false ) {
     die( print_r( sqlsrv_errors(), true));
}
if( sqlsrv_fetch( $sql_sum ) === false) {
     die( print_r( sqlsrv_errors(), true));
}
$sumx = sqlsrv_get_field( $sql_sum, 0);



$sql_sum_B = "SELECT SUM(amount) FROM iWarZ_refill_tmpay
WHERE  (DATEPART(yy, [time]) = ".$date_yy."
AND    DATEPART(mm, [time]) = ".$date_mm."
AND    DATEPART(dd, [time]) = ".$date_bb.")
AND status = 'success'
";
$sql_sum_B = sqlsrv_query( $db, $sql_sum_B);
if( $sql_sum_B === false ) {
     die( print_r( sqlsrv_errors(), true));
}
if( sqlsrv_fetch( $sql_sum_B ) === false) {
     die( print_r( sqlsrv_errors(), true));
}
$sum_B = sqlsrv_get_field( $sql_sum_B, 0);


if($sumx > $sum_B){
    //ยอดขึ้น
    $avg = $sumx-$sum_B;
    $avgx = "↑ ขึ้น";
}else{
    //ยอดลง
    $avg = $sum_B-$sumx;
    $avgx = "↓ ตก";
}


$codea = $sum_aa*50/100;
$deena = $sum_aa*35/100;
$topa = $sum_aa*15/100;

$code = $sumx*50/100;
$deen = $sumx*35/100;
$top = $sumx*15/100;

$codeb = $sum_B*50/100;
$deenb = $sum_B*35/100;
$topb = $sum_B*15/100;

/* ปัจจุบัน */
$sql_gc = "SELECT *
FROM iWarZ_refill_tmpay
WHERE (DATEPART(yy, [time]) = ".$date_yy."
AND DATEPART(mm, [time]) = ".$date_mm."
AND DATEPART(dd, [time]) = ".$date_dd.")
AND topup_to = 'gc'
AND status = 'success'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmtsql_item = sqlsrv_query( $db, $sql_gc , $params, $options );
$itemday = sqlsrv_num_rows( $stmtsql_item );

$sql_gc = "SELECT *
FROM iWarZ_refill_tmpay
WHERE (DATEPART(yy, [time]) = ".$date_yy."
AND DATEPART(mm, [time]) = ".$date_mm."
AND DATEPART(dd, [time]) = ".$date_dd.")
AND topup_to = 'item'
AND status = 'success'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmtsql_gc = sqlsrv_query( $db, $sql_gc , $params, $options );
$gcday = sqlsrv_num_rows( $stmtsql_gc );


$allitemgc = $itemday+$gcday;


/* AVG */

$sql_item_avg = "SELECT *
FROM iWarZ_refill_tmpay
WHERE topup_to = 'item'
AND status = 'success'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_item_avg = sqlsrv_query( $db, $sql_item_avg , $params, $options );
$item_avg = sqlsrv_num_rows( $stmt_item_avg );

$sql_gc_avg = "SELECT *
FROM iWarZ_refill_tmpay
WHERE topup_to = 'gc'
AND status = 'success'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_sql_avg = sqlsrv_query( $db, $sql_gc_avg , $params, $options );
$gc_avg = sqlsrv_num_rows( $stmt_sql_avg );

$sql_avg_hero = "SELECT *
FROM iWarZ_refill_tmpay
WHERE topup_to = 'hero'
AND status = 'success'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_avg_hero = sqlsrv_query( $db, $sql_avg_hero , $params, $options );
$avg_hero = sqlsrv_num_rows( $stmt_avg_hero );


$allavgitemgc = $item_avg+$gc_avg;


$sql_hro = "SELECT *
FROM iWarZ_refill_tmpay
WHERE (DATEPART(yy, [time]) = ".$date_yy."
AND DATEPART(mm, [time]) = ".$date_mm."
AND DATEPART(dd, [time]) = ".$date_dd.")
AND topup_to = 'hero'
AND status = 'success'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmtsql_hro = sqlsrv_query( $db, $sql_hro , $params, $options );
$hroday = sqlsrv_num_rows( $stmtsql_hro );

$sql_success = "SELECT *
FROM iWarZ_refill_tmpay
WHERE (DATEPART(yy, [time]) = ".$date_yy."
AND DATEPART(mm, [time]) = ".$date_mm."
AND DATEPART(dd, [time]) = ".$date_dd.")
AND status = 'success'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_sql_success = sqlsrv_query( $db, $sql_success , $params, $options );
$sql_success = sqlsrv_num_rows( $stmt_sql_success );

$sql_invaild = "SELECT *
FROM iWarZ_refill_tmpay
WHERE (DATEPART(yy, [time]) = ".$date_yy."
AND DATEPART(mm, [time]) = ".$date_mm."
AND DATEPART(dd, [time]) = ".$date_dd.")
AND status = 'invaild'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_sql_invaild = sqlsrv_query( $db, $sql_invaild , $params, $options );
$sql_invaild = sqlsrv_num_rows( $stmt_sql_invaild );

$sql_used = "SELECT *
FROM iWarZ_refill_tmpay
WHERE (DATEPART(yy, [time]) = ".$date_yy."
AND DATEPART(mm, [time]) = ".$date_mm."
AND DATEPART(dd, [time]) = ".$date_dd.")
AND status = 'used'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_sql_used = sqlsrv_query( $db, $sql_used , $params, $options );
$sql_used = sqlsrv_num_rows( $stmt_sql_used );

/* avg */
$sql_success_avg = "SELECT *
FROM iWarZ_refill_tmpay
WHERE status = 'success'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_sql_success_avg = sqlsrv_query( $db, $sql_success_avg , $params, $options );
$success_avg = sqlsrv_num_rows( $stmt_sql_success_avg );

$sql_invaild_avg = "SELECT *
FROM iWarZ_refill_tmpay
WHERE status = 'invaild'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_sql_invaild_avg = sqlsrv_query( $db, $sql_invaild_avg , $params, $options );
$invaild_avg = sqlsrv_num_rows( $stmt_sql_invaild_avg );

$sql_used_avg = "SELECT *
FROM iWarZ_refill_tmpay
WHERE status = 'used'
";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt_sql_used_avg = sqlsrv_query( $db, $sql_used_avg , $params, $options );
$used_avg = sqlsrv_num_rows( $stmt_sql_used_avg );

















/* ส่วนรวมยอดแต่ละรายการอย่าสับสนเข้าใจไหม avg */
$sql_sum_hero_avg = "SELECT SUM(amount) FROM iWarZ_refill_tmpay
WHERE topup_to = 'hero'
AND status = 'success'
";
$sql_sum_hero_avg = sqlsrv_query( $db, $sql_sum_hero_avg);
if( $sql_sum_hero_avg === false ) {
     die( print_r( sqlsrv_errors(), true));
}
if( sqlsrv_fetch( $sql_sum_hero_avg ) === false) {
     die( print_r( sqlsrv_errors(), true));
}
$sum_hero_avg = sqlsrv_get_field( $sql_sum_hero_avg, 0);



/* ส่วนรวมยอดแต่ละรายการอย่าสับสนเข้าใจไหม */


$sql_sum_hero = "SELECT SUM(amount) FROM iWarZ_refill_tmpay
WHERE  (DATEPART(yy, [time]) = ".$date_yy."
AND    DATEPART(mm, [time]) = ".$date_mm."
AND    DATEPART(dd, [time]) = ".$date_dd.")
AND topup_to = 'hero'
AND status = 'success'
";
$sql_sum_hero = sqlsrv_query( $db, $sql_sum_hero);
if( $sql_sum_hero === false ) {
     die( print_r( sqlsrv_errors(), true));
}
if( sqlsrv_fetch( $sql_sum_hero ) === false) {
     die( print_r( sqlsrv_errors(), true));
}
$sum_hero = sqlsrv_get_field( $sql_sum_hero, 0);

$sql_sum_gc = "SELECT SUM(amount) FROM iWarZ_refill_tmpay
WHERE  (DATEPART(yy, [time]) = ".$date_yy."
AND    DATEPART(mm, [time]) = ".$date_mm."
AND    DATEPART(dd, [time]) = ".$date_dd.")
AND topup_to = 'gc'
AND status = 'success'
";
$sql_sum_gc = sqlsrv_query( $db, $sql_sum_gc);
if( $sql_sum_gc === false ) {
     die( print_r( sqlsrv_errors(), true));
}
if( sqlsrv_fetch( $sql_sum_gc ) === false) {
     die( print_r( sqlsrv_errors(), true));
}
$um_gc = sqlsrv_get_field( $sql_sum_gc, 0);

$sql_sum_item = "SELECT SUM(amount) FROM iWarZ_refill_tmpay
WHERE  (DATEPART(yy, [time]) = ".$date_yy."
AND    DATEPART(mm, [time]) = ".$date_mm."
AND    DATEPART(dd, [time]) = ".$date_dd.")
AND topup_to = 'item'
AND status = 'success'
";
$sql_sum_item = sqlsrv_query( $db, $sql_sum_item);
if( $sql_sum_item === false ) {
     die( print_r( sqlsrv_errors(), true));
}
if( sqlsrv_fetch( $sql_sum_item ) === false) {
     die( print_r( sqlsrv_errors(), true));
}
$um_item = sqlsrv_get_field( $sql_sum_item, 0);



$allitemgcsum = $um_gc+$um_item;

$sql_sum_gc_avg = "SELECT SUM(amount) FROM iWarZ_refill_tmpay
WHERE topup_to = 'gc'
AND status = 'success'
";
$sql_sum_gc_avg = sqlsrv_query( $db, $sql_sum_gc_avg);
if( $sql_sum_gc_avg === false ) {
     die( print_r( sqlsrv_errors(), true));
}
if( sqlsrv_fetch( $sql_sum_gc_avg ) === false) {
     die( print_r( sqlsrv_errors(), true));
}
$um_gc_avg = sqlsrv_get_field( $sql_sum_gc_avg, 0);

$sql_sum_item_avg = "SELECT SUM(amount) FROM iWarZ_refill_tmpay
WHERE topup_to = 'item'
AND status = 'success'
";
$sql_sum_item_avg = sqlsrv_query( $db, $sql_sum_item_avg);
if( $sql_sum_item_avg === false ) {
     die( print_r( sqlsrv_errors(), true));
}
if( sqlsrv_fetch( $sql_sum_item_avg ) === false) {
     die( print_r( sqlsrv_errors(), true));
}
$um_item_avg = sqlsrv_get_field( $sql_sum_item_avg, 0);

$allitemgcsumavg = $um_gc_avg+$um_item_avg;


define('LINE_API',"https://notify-api.line.me/api/notify");

if( $db === false )
{
    $str = "ไม่สามารถเชื่อมต่อ Database";
}else{



$str = "\n==== Server ====\nจำนวนไอดี: ".number_format($userall_acc)." ไอดี \nถูกแบน : ".number_format($user_ban)." ไอดี \nผู้เล่นออนไลน์ : ".$online." \nDev ออนไลน์ : ".$dev."\n==== การเงิน ==== \n---- ข้อมูลวันนี้ ----\nเติม item&gc : ".number_format($allitemgc)." รายการ [".number_format($allitemgcsum)." บาท]\nเติม hero : ".number_format($hroday)." รายการ [".number_format($sum_hero)." บาท] \nเติมเงินสำเร็จ : ".number_format($sql_success)." รายการ\nเติมเงินโมฆะ : ".number_format($sql_invaild)." รายการ\nเติมเงินบัตรใช้แล้ว : ".number_format($sql_used)." รายการ\nยอดเติมเงินวันนี้ ".date("Y-m-d")." : ".number_format($sumx)." บาท\nA[50%] : ".number_format($code)." บาท\nB[50%] : ".number_format($deen)." บาท\nC[50%] : ".number_format($top)." บาท\n---- ข้อมูลเมื่อวาน----\nยอดเติมเงิน ".date("Y-m-d",strtotime("-1 days"))." : ".number_format($sum_B)." บาท\nA[50%] : ".number_format($codeb)." บาท\nB[50%] : ".number_format($deenb)." บาท\nC[50%] : ".number_format($topb)." บาท\n==== การเงิน AVG ====\nส่วนต่างวันนี้กับเมื่อวาน [".$avgx."]  ".number_format($avg)." บาท\nเติม item&gc : ".number_format($allavgitemgc)." รายการ [".number_format($allitemgcsumavg)." บาท]\nเติม hero : ".number_format($avg_hero)." รายการ [".number_format($sum_hero_avg)." บาท] \nเติมเงินสำเร็จ : ".number_format($success_avg)." รายการ\nเติมเงินโมฆะ : ".number_format($invaild_avg)." รายการ\nเติมเงินบัตรใช้แล้ว : ".number_format($used_avg)." รายการ\nยอดเติมเงินทั้งหมด[2016-11-12 -  ".date("Y-m-d")."] : ".number_format($sum_aa)." บาท\nA[50%] : ".number_format($codea)." บาท\nB[50%] : ".number_format($deena)." บาท\nC[50%] : ".number_format($topa)." บาท\nเวลารายงาน : ".$date_now."\nDev by Nurutdin";


}

$res = notify_message($str,$token);
print_r($res);

function notify_message($message,$token){
 $queryData = array('message' => $message);
 $queryData = http_build_query($queryData,'','&');
 $headerOptions = array(
         'http'=>array(
            'method'=>'POST',
            'header'=> "Content-Type: application/x-www-form-urlencoded\r\n"
                      ."Authorization: Bearer ".$token."\r\n"
                      ."Content-Length: ".strlen($queryData)."\r\n",
            'content' => $queryData
         ),
 );
 $context = stream_context_create($headerOptions);
 $result = file_get_contents(LINE_API,FALSE,$context);
 $res = json_decode($result);
 return $res;
}
?>