<?
Header('Cache-Control: no-cache');
Header('Pragma: no-cache');
session_start();


//include("./g.php");
//require("authen.php");
///$db_name = "ARS";
set_time_limit(99000);
//Change the 3 follow lines for database setup.
///$db_host = "172.16.177.14";
//$db_host = "172.16.128.14";
$db_user = "report";
$db_pass = "xreport2008";

$to_email0 ="panya.p@jasmine.com";
$to_email1="pamnuay@jasmine.com";
$to_email2="canat@jasmine.com";
$to_email3="saksit.s@jasmine.com";
$to_email4="vkamol@jasmine.com";

function fnc_date_calc($this_date,$num_days){
   
   $my_time = strtotime ($this_date); //converts date string to UNIX timestamp
   $timestamp = $my_time + ($num_days * 86400); //calculates # of days passed ($num_days) * # seconds in a day (86400)
     $return_date = date("Y-m-d",$timestamp);  //puts the UNIX timestamp back into string format
   
   return $return_date;//exit function and return string
}//end of function

function isValidDateTime($dateTime)
{
    if (preg_match("/^(\d{4})-(\d{2})-(\d{2}) ([01][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$/", $dateTime, $matches)) {
            if (checkdate($matches[2], $matches[3], $matches[1])) {
                return true;
        }
    }

    return false;
}

function UnixTime($mysql_timestamp){ 
    if (preg_match('/(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})/', $mysql_timestamp, $pieces) 
        || preg_match('/(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})/', $mysql_timestamp, $pieces)) { 
        $unix_time = mktime($pieces[4], $pieces[5], $pieces[6], $pieces[2], $pieces[3], $pieces[1]); 
    } elseif (preg_match('/\d{4}\-\d{2}\-\d{2} \d{2}:\d{2}:\d{2}/', $mysql_timestamp) 
        || preg_match('/\d{2}\-\d{2}\-\d{2} \d{2}:\d{2}:\d{2}/', $mysql_timestamp) 
        || preg_match('/\d{4}\-\d{2}\-\d{2}/', $mysql_timestamp) 
        || preg_match('/\d{2}\-\d{2}\-\d{2}/', $mysql_timestamp)) { 
        $unix_time = strtotime($mysql_timestamp); 
    } elseif (preg_match('/(\d{4})(\d{2})(\d{2})/', $mysql_timestamp, $pieces) 
        || preg_match('/(\d{2})(\d{2})(\d{2})/', $mysql_timestamp, $pieces)) { 
        $unix_time = mktime(0, 0, 0, $pieces[2], $pieces[3], $pieces[1]); 
    } 
  return $unix_time; 
}

$date_today = Date("Y-m-d");
$days_to_add = 5;

$report_data_date = fnc_date_calc($date_today,(($days_to_add)*-1));

if(!empty($_GET["startdate"])){
    $startdate = date($_GET["startdate"]);
} else{
//    $startdate = $date_today;
      $startdate = $report_data_date;
}

if(!empty($_GET["to_date"])){
    $to_date = $_GET["to_date"];
}else{
    $to_date = $date_today;
}



if(!empty($_GET["starttime"])){
    $starttime = $_GET["starttime"];
} else{
    $starttime = "00:00:00";
}

if(!empty($_GET["totime"])){
    $totime = $_GET["totime"];
}else{
    $totime = "23:59:59";
}

$_SESSION['startdate'] = $startdate;
$_SESSION['todate'] = $to_date;

$startdate = $startdate." ".$starttime;
$to_date = $to_date." ".$totime;
//$sroute = $sroute."%";
//$scountry = $scountry."%";

if(!isValidDateTime($startdate)){
    die("Invalid date format");
}
if(!isValidDateTime($to_date)){
    die("Invalid date format");
}




//    echo($sql);
//Send Mail
   //add From: header
$headers = "From: panya.p@jasmine.com\r\n";

//specify MIME version 1.0
$headers .= "MIME-Version: 1.0\r\n";

//unique boundary
$boundary = uniqid("HTMLMAIL");

//tell e-mail client this e-mail contains//alternate versions
$headers .= "Content-Type: multipart/mixed; boundary = $boundary\r\n\r\n";

//plain text version of message
//$body = "--$boundary\r\n" .
//   "Content-Type: text/plain; charset=windows-874\r\n" .
//   "Content-Transfer-Encoding: base64\r\n\r\n";
//$body .= chunk_split(base64_encode("This is the plain text version! ทดสอบครับ"));

//HTML version of message
$body .= "--$boundary\r\n" .
   "Content-Type: text/html; charset=tis-620\r\n" .
   "Content-Transfer-Encoding: base64\r\n\r\n";
//$body .= "--$boundary\r\n" .
//   "Content-Type: text/html; charset=tis620\r\n" ;
$body_msg = '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<TITLE>Airtime Report </TITLE>
<META http-equiv=Content-Type content="text/html; charset=tis-620">
<style>
<!--
.TABLEBORDER { font-size: xx-small; font-weight: normal }
.TABLETEXT { font-size: xx-small; font-weight: normal }

.TABLEBG { font-size: xx-small; font-weight: normal }
.TABLETITLE { font-size: xx-small; font-weight: bold }

-->
</style>
<style type="text/css">
BODY, TABLE, TR, TD, TH {
	font-family : Tahoma, "Microsoft Sans Serif", "MS Sans Serif", System; /*this is syntax form comment*/
	font-size : 8pt; }
 </style>
</HEAD>
<BODY bgcolor="#FFFFFF">

<center><B>Airtime Report  </B><br><br>

<form name="formdate" id "formdate" action="'.$_SERVER['PHP_SELF'].'" method="get">
    Start Date:<input type="text" name="startdate" value='.$startdate.'>
    Start Time:<input type="text" name="starttime" value='.$starttime.'><br>
    To Date:<input type="text" name="to_date" value='.$to_date.'>
    To Time:<input type="text" name="totime" value='.$totime.'><br>
    <input type=hidden name=orderby value='.$orderby.'>
    <br>
                            
    <input type=submit value="S u b m i t">
</form>';



$body_msg .= '<BR> <BR> <B> <a href="./sbdplus_terminal_rpt.php"> คลิกเพื่อดูข้อมูล Sabaydee Plus Terminals Activation report </a> </B> ';
//$body_msg .= '<BR> <BR> <B> <a href="http://www.jasfone.com/dailyusage_sbd_byship.jsp?stdate='.substr($startdate,0,10).'&endate='.substr($to_date,0,10).'&sowner=" target="_blank">New! Sabaydee Chart Daily by Ship Group </a> </B><BR> ';
$body_msg .= '<BR> <B> <a href="./roaming-register.php"> Roaming Register Report </a> </B><BR> ';



$body_msg .='                 
  <BR>
  <BR>
  <B>Prepaid Airtime Report '.$startdate.' To '.$to_date. ' </B>    
  <BR><BR>
  <TABLE >
  <TR>';

/*
  
$link = mysql_connect($db_host,$db_user,$db_pass) ;
if (!$link) {
   die('Could not connect: ' . mysql_error());
}

    mysql_select_db($db_name);
//	left(cdr_st_time,10) as CallDate,

$sql = "
    select count(distinct if(cg_id in(1,2,4,6),right(cdr_src,7),NULL)) as SPS_Subs,
		count(distinct if(cg_id in(26,27,28) AND cdr_spotbeam <= 120,right(cdr_src,7),NULL)) as APAC_Subs,
		COUNT(DISTINCT IF(cdr_tarrif_code='GLO' AND cdr_spotbeam>120,cdr_src,null)) AS  EMEA_subs,
    count(distinct if(cg_id = 25,right(cdr_src,7),NULL)) as GSPSVT_Subs,
    count(distinct right(cdr_src,7)) as Subs,
		count(*) as Calls,
  	sum(cdr_rated_usage/60)/count(*) as 'Min/call', sum(cdr_rated_usage/60)/count(distinct right(cdr_src,7)) as 'Min/Subs'
  	from cdr left join customer using(cus_id)
    where cdr_st_time >='".$startdate."' and cdr_st_time <='".$to_date."' 
    and (hour(cdr_st_time) >= '$starttime' and hour(cdr_st_time) <= '$totime') and cdr_rated_usage > 0 ";

 $sql = "
    select count(distinct if(cdr_tarrif_code='STD' and cg_id <> 25,right(cdr_src,7),NULL)) as SPS_Subs,
		count(distinct if(cg_id in(26,27,28) AND cdr_spotbeam <= 120,right(cdr_src,7),NULL)) as APAC_Subs,
		COUNT(DISTINCT IF(cdr_tarrif_code='GLO' AND cdr_spotbeam>120,cdr_src,null)) AS  EMEA_subs,
    count(distinct if(cg_id = 25,right(cdr_src,7),NULL)) as GSPSVT_Subs,
    count(distinct right(cdr_src,7)) as Subs,
		count(*) as Calls,
  	sum(cdr_rated_usage/60)/count(*) as 'Min/call', sum(cdr_rated_usage/60)/count(distinct right(cdr_src,7)) as 'Min/Subs'
  	from cdr left join customer using(cus_id)
    where cdr_st_time >='".$startdate."' and cdr_st_time <='".$to_date."' 
    and (hour(cdr_st_time) >= '$starttime' and hour(cdr_st_time) <= '$totime') and cdr_rated_usage > 0 ";


//echo $sql;
//set_time_limit(99000);

$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }


if ($row = mysql_fetch_array($s)) {
  $totsps = $row[0];
  $totapac = $row[1];
  $totemea = $row[2];
  $totgspsvt = $row[3];
  $totsubs = $row[4];
  $totcalls = $row[5];
  $totminpercall = $row[6];
  $totminpersubs = $row[7];
}

/*
$sql = "
    select date(cdr_st_time) as CallDate,
	sum(if(cg_id in(1,2,4,6) and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as IC_S_M,
	sum(if(cg_id in(1,2,4,6) and cdr_calltype like '%OC',cdr_rated_usage/60,0)) as OG_S_M,
	sum(if(cg_id in(26,27,28) and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as IC_G_M,
	sum(if(cg_id in(26,27,28) and cdr_calltype like '%OC',cdr_rated_usage/60,0)) as OG_G_M,
	sum(if(cg_id in(25) and cdr_dest  like '84009%' and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as IC_VOIP_M,
	sum(if(cg_id in(25) and cdr_dest not like '84009%' and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as IC_V_M,
	sum(if(cg_id in(25) and cdr_calltype like '%OC',cdr_rated_usage/60,0)) as OG_V_M,
	sum(if(cg_id in(25) and right(cdr_calltype,2) not in ('IC','OC','MG') ,cdr_rated_usage/60,0)) as InterVT_M,
	sum(if(right(cdr_calltype,2) not in ('IC','OC','MG') ,cdr_rated_usage/60,0)) as Inter_M,
	sum(cdr_rated_usage/60) as TotalM,
	sum(if(cg_id in(1,2,4,6) and cdr_calltype like '%IC',cdr_rated,0)) as IC_S_B,
	sum(if(cg_id in(1,2,4,6) and cdr_calltype like '%OC',cdr_rated,0)) as OG_S_B,
	sum(if(cg_id in(26,27,28) and cdr_calltype like '%IC',cdr_rated,0)) as IC_G_B,
	sum(if(cg_id in(26,27,28) and cdr_calltype like '%OC',cdr_rated,0)) as OG_G_B,
	sum(if(cg_id in(25) and cdr_dest  like '84009%' and cdr_calltype like '%IC',cdr_rated,0)) as IC_VOIP_B,
	sum(if(cg_id in(25) and cdr_dest not like '84009%' and cdr_calltype like '%IC',cdr_rated,0)) as IC_V_B,
	sum(if(cg_id in(25) and cdr_calltype like '%OC',cdr_rated,0)) as OG_V_B,
	sum(if(cg_id in(25) and right(cdr_calltype,2) not in ('IC','OC','MG') ,cdr_rated,0)) as InterVT_B,
	sum(if(right(cdr_calltype,2) not in ('IC','OC','MG') ,cdr_rated,0)) as Inter_B,
	sum(cdr_rated) as TotalB,count(distinct if(cg_id in(1,2,4,6),right(cdr_src,7),NULL)) as SPS_Subs,
	count(distinct if(cg_id in(26,27,28) AND cdr_spotbeam <= 120,right(cdr_src,7),NULL)) as APAC_Subs,
	COUNT(DISTINCT IF(cdr_tarrif_code='GLO' AND cdr_spotbeam>120,cdr_src,null)) AS  EMEA_subs,
	count(distinct if(cg_id in (25),right(cdr_src,7),NULL)) as GSPSVT_Subs,
	count(distinct right(cdr_src,7)) as Subs, 
	count(*) as Calls,
  sum(cdr_rated_usage/60)/count(*) as 'Min/call', sum(cdr_rated_usage/60)/count(distinct right(cdr_src,7)) as 'Min/Subs'
  from cdr left join customer using(cus_id)
    where cdr_st_time >='".$startdate."' and cdr_st_time <='".$to_date."' 
    and (hour(cdr_st_time) >= '$starttime' and hour(cdr_st_time) <= '$totime') and cdr_rated_usage > 0 
    group by CallDate
    order by CallDate desc";
*/
/* $sql = "
    select date(cdr_st_time) as CallDate,
	sum(if(cdr_tarrif_code='STD' and cg_id <> 25 and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as IC_S_M,
	sum(if(cdr_tarrif_code='STD' and cg_id <> 25 and cdr_calltype like '%OC',cdr_rated_usage/60,0)) as OG_S_M,
	sum(if(cdr_tarrif_code<>'STD' and cg_id <> 25 and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as IC_G_M,
	sum(if(cdr_tarrif_code<>'STD' and cg_id <> 25 and cdr_calltype like '%OC',cdr_rated_usage/60,0)) as OG_G_M,
	sum(if(cg_id in(25) and cdr_dest  like '84009%' and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as IC_VOIP_M,
	sum(if(cg_id in(25) and cdr_dest not like '84009%' and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as IC_V_M,
	sum(if(cg_id in(25) and cdr_calltype like '%OC',cdr_rated_usage/60,0)) as OG_V_M,
	sum(if(cg_id in(25) and right(cdr_calltype,2) not in ('IC','OC','MG') ,cdr_rated_usage/60,0)) as InterVT_M,
	sum(if(right(cdr_calltype,2) not in ('IC','OC','MG') ,cdr_rated_usage/60,0)) as Inter_M,
	sum(cdr_rated_usage/60) as TotalM,
	sum(if(cdr_tarrif_code='STD' and cg_id <> 25 and cdr_calltype like '%IC',cdr_rated,0)) as IC_S_B,
	sum(if(cdr_tarrif_code='STD' and cg_id <> 25 and cdr_calltype like '%OC',cdr_rated,0)) as OG_S_B,
	sum(if(cdr_tarrif_code<>'STD' and cg_id <> 25 and cdr_calltype like '%IC',cdr_rated,0)) as IC_G_B,
	sum(if(cdr_tarrif_code<>'STD' and cg_id <> 25 and cdr_calltype like '%OC',cdr_rated,0)) as OG_G_B,
	sum(if(cg_id in(25) and cdr_dest  like '84009%' and cdr_calltype like '%IC',cdr_rated,0)) as IC_VOIP_B,
	sum(if(cg_id in(25) and cdr_dest not like '84009%' and cdr_calltype like '%IC',cdr_rated,0)) as IC_V_B,
	sum(if(cg_id in(25) and cdr_calltype like '%OC',cdr_rated,0)) as OG_V_B,
	sum(if(cg_id in(25) and right(cdr_calltype,2) not in ('IC','OC','MG') ,cdr_rated,0)) as InterVT_B,
	sum(if(right(cdr_calltype,2) not in ('IC','OC','MG') ,cdr_rated,0)) as Inter_B,
	sum(cdr_rated) as TotalB,count(distinct if(cdr_tarrif_code='STD' and cg_id <> 25,right(cdr_src,7),NULL)) as SPS_Subs,
	count(distinct if(cdr_tarrif_code<>'STD' and cg_id <> 25 AND cdr_spotbeam <= 120,right(cdr_src,7),NULL)) as APAC_Subs,
	COUNT(DISTINCT IF(cdr_tarrif_code='GLO' AND cdr_spotbeam>120,cdr_src,null)) AS  EMEA_subs,
	count(distinct if(cg_id in (25),right(cdr_src,7),NULL)) as GSPSVT_Subs,
	count(distinct right(cdr_src,7)) as Subs, 
	count(*) as Calls,
  sum(cdr_rated_usage/60)/count(*) as 'Min/call', sum(cdr_rated_usage/60)/count(distinct right(cdr_src,7)) as 'Min/Subs'
  from cdr left join customer using(cus_id)
    where cdr_st_time >='".$startdate."' and cdr_st_time <='".$to_date."' 
    and (hour(cdr_st_time) >= '$starttime' and hour(cdr_st_time) <= '$totime') and cdr_rated_usage > 0 
    group by CallDate
    order by CallDate desc";



//echo $sql;
//set_time_limit(99000);

$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }
  $fields = mysql_num_fields ( $s );

	$head_column = array();
	$head_column[] = "";
	$head_column[0] = "Call";
	$head_column[1] = "SPS Minutes";
	$head_column[2] = "GSPS Minutes";
	$head_column[3] = "VT Minutes";
	$head_column[4] = "All Minutes";
	$head_column[5] = "SPS Baht";
	$head_column[6] = "GSPS Baht";
	$head_column[7] = "VT Baht";
	$head_column[8] = "All Baht";
	$head_column[9] = "Active Subs";
	$head_column[10] = "Total";
	$head_column[11] = "Minutes per";
	$cspan= 1;

	for($i=0;$i<12;$i++){
		 //col span
		 if(($i>=1 & $i<3) | ($i>=4 & $i<7) | ($i>=8 & $i<9) | $i==11 ) $cspan=2; 
		 else if($i==3 |$i==7 ) $cspan=4; else if($i==9) $cspan=5; else $cspan=1;
		 //BGcolor
		 if(($i>=1 & $i<=4) | $i==11) $bgcolor="lightgreen"; 
		 else if($i>=5 & $i<=8) $bgcolor="lightblue";
		 else if($i==9) $bgcolor="orange";
		 else $bgcolor="grey"; 
		 
	    $body_msg .='<TD colspan='.$cspan.' align=center bgcolor='.$bgcolor.'><b>'.	$head_column[$i].'</a></b></TD>';
	    	    
	}
  
  $body_msg .='<TR>';
	$head_column1 = array();
	$head_column1[] = "";
	$head_column1[0] = "Date";
	$head_column1[1] = "IC";
	$head_column1[2] = "OG";
	$head_column1[3] = "IC";
	$head_column1[4] = "OG";
	$head_column1[5]  = "ICVoIP";
	$head_column1[6]  = "ICLand"; 
	$head_column1[7]  = "OG";      
	$head_column1[8]  = "VT";      
	$head_column1[9]  = "Inter";   
	$head_column1[10] = "Total";   
	$head_column1[11] = "IC";      
	$head_column1[12] = "OG";      
	$head_column1[13] = "IC";      
	$head_column1[14] = "OG";      
	$head_column1[15] = "ICVoIP";  
	$head_column1[16] = "ICLand";  
	$head_column1[17] = "OG";        
	$head_column1[18] = "VT";        
	$head_column1[19] = "Inter";     
	$head_column1[20] = "Total";     
	$head_column1[21] = "SPS";       
	$head_column1[22] = "APAC";      
	$head_column1[23] = "EMEA";    
	$head_column1[24] = "VT";        
	$head_column1[25] = "Total";     
	$head_column1[26] = "Calls";     
	$head_column1[27] = "Call";    	  	
	$head_column1[28] = "Subs";  
	 	
	for($i=0;$i<$fields;$i++){
	    $body_msg .='<TD  align=center bgcolor=grey><b>'.$head_column1[$i].'</a></b></TD>';
	    	    
	}

	$sum_column = array();
//	$yMinute = array();
//	$yTotal = array();
//	$xDate = array();
	$yMinute = '';
	$yTotal = '';
	$yContax = '';
	$yMobile = '';
	$xDate = '';
	$yOther = '';
	$yGSPS = '';
	
//	$yMinute[] = 0;
	$sum_column[] = 0;
	
	$irow = 0;
	while ($row = mysql_fetch_assoc($s)) {
	$body_msg .='<TR>';

	$irow++;
	$ireset = 0;
	
	foreach( $row as $value )
    {
	
		// array value
/*		
		//Date
		if($ireset == 0) {
		    if($xDate == ''){
			//$xDate = "'".UnixTime($value)."'";
			$xDate = UnixTime($value);
		    } else {
			$xDate .= ",".UnixTime($value); 
		    }
		}
		
		//Mobile
		if($ireset == 5) {
		    if($yMobile == ''){
			$yMobile .= $value;
		    } else {
			$yMobile .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		
		//Contax
		if($ireset == 6) {
		    if($yContax == ''){
			$yContax .= $value;
		    } else {
			$yContax .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		//Contax
		if($ireset == 7) {
		    if($yGSPS == ''){
			$yGSPS .= $value;
		    } else {
			$yGSPS .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		//Other
		if($ireset == 8) {
		    if($yOther == ''){
			$yOther .= $value;
		    } else {
			$yOther .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		
		//Total Minute
		if($ireset == 4) {
		    if($yMinute == ''){
			$yMinute .= $value;
		    } else {
			$yMinute .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		if($ireset == 8) {
		    if($yTotal == ''){
			$yTotal .= $value;
		    } else {
			$yTotal .= ','.$value; 
		    }
		    //$yTotal[] = $nvalue; 
		}
	*/	
/*		if($ireset> 26 & $ireset< 29){
		    $nvalue = number_format($value,2);
		}else{
			 if($ireset>0) {
		    $nvalue = number_format($value,0);
		    } else {
		    $nvalue = $value;
		  }
		}
		
		
		if(($irow%2)==0 ){
			$body_msg .='<TD bgcolor="#B8B8B8" align="right" nowrap> ';
			if($value == 't'){
			    $body_msg .= '<font color = "green"> ';
			}
			 $body_msg .= $nvalue.'</font></TD>';

			
		}else{
			$body_msg .='<TD bgcolor="#F7F7F7" align="right" nowrap> ';
			if($value == 't'){
			    $body_msg .= '<font color = "green"> ';
			}
			$body_msg .= $nvalue.'</font></TD>';
			
			
		}
		if($ireset == 0){ // for activated = 't' count
		//    if ($nvalue == 't'){
		//	$sum_column[$ireset] += 1;
		//    }
		} else{
		    $sum_column[$ireset] += $value;
		}
		$ireset++;
		
	}
//	$ireset=0;
	
	} //while
	$body_msg .= "</TR><TR>";

//    mysql_close($link);
// Sum value
$sum_column[0] = "Total";
$sum_column[21] = $totsps;
$sum_column[22] = $totapac;
$sum_column[23] = $totemea;
$sum_column[24] = $totgspsvt;
$sum_column[25] = $totsubs;
$sum_column[26] = $totcalls;
$sum_column[27] = $totminpercall;
$sum_column[28] = $totminpersubs;

for($i=0;$i<$fields;$i++){
		if(($i >=1) && ($i < 27)){
		
//		if(($i ==1) || ($i == 2)|| ($i == 4)|| ($i == 5)|| ($i >= 7)){
//		    $body_msg .='<TD  align=right bgcolor="#989894"><b>'.number_format($sum_column[$i],2).'</b></TD>';
		    $body_msg .='<TD  align=right bgcolor="#989894"><b>'.number_format($sum_column[$i],0).'</b></TD>';
		}else if(($i >=27) && ($i < 29)) {   
		    $body_msg .='<TD  align=right bgcolor="#989894"><b>'.number_format($sum_column[$i],2).'</b></TD>';			
		}else{
			if($i==0 ){
			    $body_msg .='<TD  align=right bgcolor="#989894"><b>'.$sum_column[$i].'&nbsp;</b></TD>';
			} else {
			    $body_msg .='<TD align=right bgcolor="#989894"><b>&nbsp;</b></TD>';
			}
		}
}

$body_msg .='</TR>';
//$body_msg .=' <TR><TD align=right width="80" bgcolor="#858585" ><b>'.mysql_field_name($s, 0).'</b></TD>';
//	for($i=1;$i<$fields;$i++){
//	    $body_msg .='<TD  align=right bgcolor="#858585"><b>'.mysql_field_name($s, $i).'</b></TD>';
//	    	    
//	};

$sql = "
    select date(cdr_st_time) as CallDate,
	sum(if(cdr_tarrif_code='STD' ,cdr_rated_usage/60,0)) as SPS_S_M,
	sum(if(cdr_tarrif_code='HOM' and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as H_IC_G_M,
	sum(if(cdr_tarrif_code='HOM' and cdr_calltype not like '%IC',cdr_rated_usage/60,0)) as H_OG_G_M,
	sum(if(cdr_tarrif_code='GLO' and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as G_IC_G_M,
	sum(if(cdr_tarrif_code='GLO' and cdr_calltype not like '%IC',cdr_rated_usage/60,0)) as G_OG_G_M,
	sum(if(cdr_tarrif_code='HOM' and cg_id in(25) and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as H_IC_V_M,
	sum(if(cdr_tarrif_code='HOM' and cg_id in(25) and cdr_calltype not like '%IC' ,cdr_rated_usage/60,0)) as H_OG_V_M,
	sum(if(cdr_tarrif_code='GLO' and cg_id in(25) and cdr_calltype like '%IC',cdr_rated_usage/60,0)) as G_IC_V_M,
	sum(if(cdr_tarrif_code='GLO' and cg_id in(25) and cdr_calltype not like '%IC' ,cdr_rated_usage/60,0)) as G_OG_V_M,
	sum(cdr_rated_usage/60) as TotalM,
	sum(if(cdr_tarrif_code='STD' ,cdr_rated,0)) as SPS_S_B,
	sum(if(cdr_tarrif_code='HOM' and cdr_calltype like '%IC',cdr_rated,0)) as H_IC_S_B,
	sum(if(cdr_tarrif_code='HOM' and cdr_calltype not like '%IC',cdr_rated,0)) as H_OG_B,
	sum(if(cdr_tarrif_code='GLO' and cdr_calltype like '%IC',cdr_rated,0)) as G_IC_B,
	sum(if(cdr_tarrif_code='GLO' and cdr_calltype not like '%IC',cdr_rated,0)) as G_OG_B,
	sum(if(cdr_tarrif_code='HOM' and cg_id in(25) and cdr_calltype like '%IC',cdr_rated,0)) as H_IC_V_B,
	sum(if(cdr_tarrif_code='HOM' and cg_id in(25) and cdr_calltype not like '%IC' ,cdr_rated,0)) as H_OG_V_B,
	sum(if(cdr_tarrif_code='GLO' and cg_id in(25) and cdr_calltype like '%IC',cdr_rated,0)) as G_IC_V_B,
	sum(if(cdr_tarrif_code='GLO' and cg_id in(25) and cdr_calltype not like '%IC' ,cdr_rated,0)) as G_OG_V_B,
	sum(cdr_rated) as TotalB,count(distinct if(cdr_tarrif_code='STD',right(cdr_src,7),NULL)) as SPS_Subs,
	count(distinct if(cdr_tarrif_code<>'STD' AND cdr_spotbeam <= 120,right(cdr_src,7),NULL)) as APAC_Subs,
	COUNT(DISTINCT IF(cdr_tarrif_code='GLO' AND cdr_spotbeam>120,cdr_src,null)) AS  EMEA_subs,
	count(distinct if(cg_id in (25) ,right(cdr_src,7),NULL)) as GSPSVT_Subs,
	count(distinct right(cdr_src,7)) as Subs, 
	count(*) as Calls,
  sum(cdr_rated_usage/60)/count(*) as 'Min/call', sum(cdr_rated_usage/60)/count(distinct right(cdr_src,7)) as 'Min/Subs'
  from cdr left join customer using(cus_id)
    where cdr_st_time >='".$startdate."' and cdr_st_time <='".$to_date."' 
    and (hour(cdr_st_time) >= '$starttime' and hour(cdr_st_time) <= '$totime') and cdr_rated_usage > 0 
    group by CallDate
    order by CallDate desc";



//echo $sql;
//set_time_limit(99000);

$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }
  $fields = mysql_num_fields ( $s );

  $body_msg .='<TR>';
	$head_column = array();
	$head_column[] = "";
	$head_column[0] = "NEW AT";
	$head_column[1] = "Minutes";
	$head_column[2] = "Baht";
	$head_column[3] = "Active";
	$head_column[4] = "Total";
	$head_column[5] = "Minutes";
	$cspan= 1;

	for($i=0;$i<6;$i++){
		 //col span
		 if($i==1 |$i==2 ) $cspan=10; 
		 else if($i==5) $cspan=2; else if($i==3) $cspan=5; else $cspan=1;
		 //BGcolor
		 if($i==1 | $i==5) $bgcolor="lightgreen"; 
		 else if($i==2) $bgcolor="lightblue";
		 else if($i==3) $bgcolor="orange";
		 else $bgcolor="grey"; 
		 
	    $body_msg .='<TD colspan='.$cspan.' align=center bgcolor='.$bgcolor.'><b>'.	$head_column[$i].'</a></b></TD>';
	    	    
	}
  $body_msg .='</TR><TR>';
	$head_column0 = array();
	$head_column0[] = "";
	$head_column0[0] = "Call";
	$head_column0[1] = "SPS";
	$head_column0[2] = "HOME";
	$head_column0[3] = "GLOB";
	$head_column0[4] = "VT HOME";
	$head_column0[5] = "VT GLOB";
	$head_column0[6] = "All";
	$head_column0[7] = "SPS";
	$head_column0[8] = "HOME";
	$head_column0[9] = "GLOB";
	$head_column0[10] = "VT HOME";
	$head_column0[11] = "VT GLOB";
	$head_column0[12] = "All";
	$head_column0[13] = "Subscribers";
	$head_column0[14] = "No.of";
	$head_column0[15] = "Avg.per";
	$cspan= 1;

	for($i=0;$i<16;$i++){
		 //col span
		 if(($i>=2 & $i<=5) |($i>=8 & $i<=11) | $i==15 ) $cspan=2; else if($i==13) $cspan=5; else $cspan=1;
		 //BGcolor
		 if(($i>=1 & $i<=6) | $i==15) $bgcolor="lightgreen"; 
		 else if($i>=7 & $i<=12) $bgcolor="lightblue";
		 else if($i==13) $bgcolor="orange";
		 else $bgcolor="grey"; 
		 
	    $body_msg .='<TD colspan='.$cspan.' align=center bgcolor='.$bgcolor.'><b>'.	$head_column0[$i].'</a></b></TD>';
	    	    
	}

  $body_msg .='</TR><TR>';  
	$head_column1 = array();
	$head_column1[]   = "";
	$head_column1[0]  = "Date";
	$head_column1[1]  = "All";
	$head_column1[2]  = "IC";
	$head_column1[3]  = "OG";
	$head_column1[4]  = "IC";
	$head_column1[5]  = "OG";
	$head_column1[6]  = "IC"; 
	$head_column1[7]  = "OG";      
	$head_column1[8]  = "IC";      
	$head_column1[9]  = "OG";   
	$head_column1[10] = "Total";   
	$head_column1[11] = "All";      
	$head_column1[12] = "IC";      
	$head_column1[13] = "OG";      
	$head_column1[14] = "IC";      
	$head_column1[15] = "OG";  
	$head_column1[16] = "IC";  
	$head_column1[17] = "OG";        
	$head_column1[18] = "IC";        
	$head_column1[19] = "OG";     
	$head_column1[20] = "Total";     
	$head_column1[21] = "SPS";       
	$head_column1[22] = "APAC";      
	$head_column1[23] = "EMEA";    
	$head_column1[24] = "VT";        
	$head_column1[25] = "Total";     
	$head_column1[26] = "Calls";     
	$head_column1[27] = "Call";    	  	
	$head_column1[28] = "Subs";  
	 	
	for($i=0;$i<$fields;$i++){
	    $body_msg .='<TD  align=center bgcolor=grey><b>'.$head_column1[$i].'</a></b></TD>';
	    	    
	}

	$sum_column = array();
//	$yMinute = array();
//	$yTotal = array();
//	$xDate = array();
	$yMinute = '';
	$yTotal = '';
	$yContax = '';
	$yMobile = '';
	$xDate = '';
	$yOther = '';
	$yGSPS = '';
	
//	$yMinute[] = 0;
	$sum_column[] = 0;
	
	$irow = 0;
	while ($row = mysql_fetch_assoc($s)) {
	$body_msg .='<TR>';

	$irow++;
	$ireset = 0;
	
	foreach( $row as $value )
    {
	
		// array value
/*		
		//Date
		if($ireset == 0) {
		    if($xDate == ''){
			//$xDate = "'".UnixTime($value)."'";
			$xDate = UnixTime($value);
		    } else {
			$xDate .= ",".UnixTime($value); 
		    }
		}
		
		//Mobile
		if($ireset == 5) {
		    if($yMobile == ''){
			$yMobile .= $value;
		    } else {
			$yMobile .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		
		//Contax
		if($ireset == 6) {
		    if($yContax == ''){
			$yContax .= $value;
		    } else {
			$yContax .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		//Contax
		if($ireset == 7) {
		    if($yGSPS == ''){
			$yGSPS .= $value;
		    } else {
			$yGSPS .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		//Other
		if($ireset == 8) {
		    if($yOther == ''){
			$yOther .= $value;
		    } else {
			$yOther .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		
		//Total Minute
		if($ireset == 4) {
		    if($yMinute == ''){
			$yMinute .= $value;
		    } else {
			$yMinute .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		if($ireset == 8) {
		    if($yTotal == ''){
			$yTotal .= $value;
		    } else {
			$yTotal .= ','.$value; 
		    }
		    //$yTotal[] = $nvalue; 
		}
	
		if($ireset> 26 & $ireset< 29){
		    $nvalue = number_format($value,2);
		}else{
			 if($ireset>0) {
		    $nvalue = number_format($value,0);
		    } else {
		    $nvalue = $value;
		  }
		}
		
		
		if(($irow%2)==0 ){
			$body_msg .='<TD bgcolor="#B8B8B8" align="right" nowrap> ';
			if($value == 't'){
			    $body_msg .= '<font color = "green"> ';
			}
			 $body_msg .= $nvalue.'</font></TD>';

			
		}else{
			$body_msg .='<TD bgcolor="#F7F7F7" align="right" nowrap> ';
			if($value == 't'){
			    $body_msg .= '<font color = "green"> ';
			}
			$body_msg .= $nvalue.'</font></TD>';
			
			
		}
		if($ireset == 0){ // for activated = 't' count
		//    if ($nvalue == 't'){
		//	$sum_column[$ireset] += 1;
		//    }
		} else{
		    $sum_column[$ireset] += $value;
		}
		$ireset++;
		
	}
//	$ireset=0;
	
	} //while
	$body_msg .= "</TR><TR>";

//    mysql_close($link);
// Sum value
$sum_column[0] = "Total";
$sum_column[21] = $totsps;
$sum_column[22] = $totapac;
$sum_column[23] = $totemea;
$sum_column[24] = $totgspsvt;
$sum_column[25] = $totsubs;
$sum_column[26] = $totcalls;
$sum_column[27] = $totminpercall;
$sum_column[28] = $totminpersubs;

for($i=0;$i<$fields;$i++){
		if(($i >=1) && ($i < 27)){
		
//		if(($i ==1) || ($i == 2)|| ($i == 4)|| ($i == 5)|| ($i >= 7)){
//		    $body_msg .='<TD  align=right bgcolor="#989894"><b>'.number_format($sum_column[$i],2).'</b></TD>';
		    $body_msg .='<TD  align=right bgcolor="#989894"><b>'.number_format($sum_column[$i],0).'</b></TD>';
		}else if(($i >=27) && ($i < 29)) {   
		    $body_msg .='<TD  align=right bgcolor="#989894"><b>'.number_format($sum_column[$i],2).'</b></TD>';			
		}else{
			if($i==0 ){
			    $body_msg .='<TD  align=right bgcolor="#989894"><b>'.$sum_column[$i].'&nbsp;</b></TD>';
			} else {
			    $body_msg .='<TD align=right bgcolor="#989894"><b>&nbsp;</b></TD>';
			}
		}
}

$body_msg .='</TR>';


$_SESSION['xdate'] = $xDate;
$_SESSION['yMinute'] = $yMinute;
$_SESSION['yTotal'] = $yTotal; 
$_SESSION['yMobile'] = $yMobile;
$_SESSION['yContax'] = $yContax;
$_SESSION['yGSPS'] = $yGSPS;
$_SESSION['yOther'] = $yOther;

$body_msg .='</TABLE>';

$body_msg .= '<!-- <BR><BR><img alt="ACeS Airtime" src="acesg2.php"/><BR> -->';
//gengraph();
/*
$body_msg .= $xDate;
$body_msg .= $yMinute;
$body_msg .= $yTotal;
*/

//$_SESSION['yTotal'] = $yTotal; 
/*
print_r($_SESSION['xdate']);
print_r($_SESSION['yTotal']);
print_r($_SESSION['yMinute']);

mysql_close($link);
*/
// Sabaydee Active Terminals report

$db_name = "a2billing";
$db_host = "172.16.130.212:3307";
//$db_host = "172.16.128.233";
$db_server = "172.16.130.212";
$last_line = system('ping -c1 -w1 '.$db_server.' |grep 64', $retval);
if( $retval <> 0 ) { 
	$db_host = "172.16.128.233";
}
$link = mysql_connect($db_host,$db_user,$db_pass) ;
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
mysql_select_db($db_name);

$slavestatus = "ON";
//echo $slavestatus;
if ($db_host != "172.16.129.233") {
$sql = "show status like 'Slave_running'";

$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }
	if ($row = mysql_fetch_assoc($s)) $slavestatus = $row['Value'];
	
//echo $sql.$slavestatus.$row['Value'];	
}

// Sabaydee+ Active Terminals report

//$db_name = "a2billing";
//$db_host = "172.16.128.233";

//$link = mysql_connect($db_host,$db_user,$db_pass) ;
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
mysql_select_db($db_name);
//    select left(starttime,10) as CallDate,

$sql = "
    select date(starttime) as CallDate, count(distinct if(length(calledsub) in (4,7),calledsub,0)) as active_terminals,if(calledprovider in ('SBDPLUS','PUBLIC','PRIVATE','SATFWD'),calledprovider ,if(calledprovider = 'SBDACES','ACeS',if(left(destination,4) = 'SFW-' ,'SATFWD','SBDLAND'))) as Service, sum(if(terminatecause='ANSWER',1,0)) AS succcalls, 
    round(sum(if(terminatecause='ANSWER',1,0))/(count(*)-sum(if(terminatecause='NOANSWER',1,0)))*100,2) AS percent_succ  from cc_call
    where starttime >='".$startdate."' and starttime <= '".$to_date."' 
    and (time(starttime) >= '$starttime' and time(starttime) <= '$totime')
    AND username <> '0001112006'
    group by CallDate,Service
    order by CallDate desc
";


$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }

//	$actsubsc = array();
	$actsubsc = 0;
	$actservice = array();
	$actservice[] = 0;

	$percent_succ = array();
	$percent_succ[] = 0;

	$i = 1;
	while ($row = mysql_fetch_assoc($s)) {
//			$actsubsc[$i]= $row['active_terminals'];
			$actservice[$i]= $row['Service'];
			$percent_succ[$i]= $row['percent_succ'];
			$i++;
	}

//Get subsc in the week or selected period
$sql = "
    select  count(distinct if(length(calledsub) in (4,7),calledsub,0)) as active_terminals, sum(if(terminatecause='ANSWER',1,0)) AS succcalls, 
    round(sum(if(terminatecause='ANSWER',1,0))/(count(*)-sum(if(terminatecause='NOANSWER',1,0)))*100,2) AS percent_succ  from cc_call
    where starttime >='".$startdate."' and starttime <= '".$to_date."' AND username <> '0001112006'
    and (time(starttime) >= '$starttime' and time(starttime) <= '$totime')
";


$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }

	$avgactsubsc = 0;

	$avgpercent_succ = 0;

	while ($row = mysql_fetch_assoc($s)) {
			$avgactsubsc= $row['active_terminals'];
			$avgpercent_succ= $row['percent_succ'];
	}

// Sabaydee+ report

//$db_name = "a2billing";
//$db_host = "172.16.129.233";

//$link = mysql_connect($db_host,$db_user,$db_pass) ;
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
mysql_select_db($db_name);
//    select left(starttime,10) as CallDate,
/*
 //with Free Minute report
$sql = "
    select date(starttime) as CallDate,
    sum(if(destination in('SBD+SBDPLUS-SP','SBD+SBDPLUS-LAND','SBD+SBDPLUS','SBD+SBDPLUS-CBK','SBD+ACeS','SBD+ACeSSBD','ACeS','ACeSSBD','SFW-ACeS','SFW-SATFWDIC','SFW-SBDPLUS') and sessiontime>5,ceiling(sessiontime/60),0)) as SBD_Min,
    sum(if(destination like '%THAILAND%' and destination not like '%-SP' and sessiontime>5,ceiling(sessiontime/60),0)) as 'TH_Min',
    sum(if(destination like '%-SP' and sessiontime>5,ceiling(sessiontime/60),0)) as SP_Min,
    sum(if(destination not like '%-SP' and destination not in('SBD+ACeS','SBD+ACeSSBD','ACeS','ACeSSBD','SBD+SBDPLUS-SP','SBD+SBDPLUS-LAND','SBD+SBDPLUS','SBD+SBDPLUS-CBK','SBD+THAILAND-MOBILE','SBD+THAILAND','SBD+THAILAND-BKK','SBD+THAILAND (66)','SFW-THAILAND-MOBILE','SFW-THAILAND','SFW-THAILAND-BKK','SFW-THAILAND (66)','SFW-THAILAND-JASMINE','SFW-SATFWDIC','SFW-SBDPLUS') and sessiontime>5,ceiling(sessiontime/60),0)) as Oth_Min,
    sum(if(sessiontime>5,ceiling(sessiontime/60),0)) as Total_Min,
    sum(if(destination in('SBD+SBDPLUS-SP','SBD+SBDPLUS-LAND','SBD+SBDPLUS','SBD+SBDPLUS-CBK','SBD+ACeS','SBD+ACeSSBD','ACeS','ACeSSBD','SFW-ACeS','SFW-SATFWDIC','SFW-SBDPLUS'),sessionbill,0)) as SBD_Bht,
    sum(if(destination like '%THAILAND%' and destination not like '%-SP',sessionbill,0)) as 'TH_Bht',
    sum(if(destination like '%-SP',sessionbill,0)) as SP_Bht,
    sum(if(destination not like '%-SP' and destination not in('SBD+ACeS','SBD+ACeSSBD','ACeS','ACeSSBD','SBD+SBDPLUS-SP','SBD+SBDPLUS-LAND','SBD+SBDPLUS','SBD+THAILAND-MOBILE','SBD+THAILAND','SBD+THAILAND-BKK','SBD+SBDPLUS-CBK','SBD+THAILAND (66)','SFW-THAILAND-MOBILE','SFW-THAILAND','SFW-THAILAND-BKK','SFW-THAILAND (66)','SFW-THAILAND-JASMINE','SFW-SATFWDIC','SFW-SBDPLUS'),sessionbill,0)) as Oth_Bht,
    sum(sessionbill) as Total_Bht,32-MIN(IF(sessionid like 'SS7/msc%',cast(MID(sessionid,9,length(sessionid)-8) as signed),32)) AS CICmax, count(distinct username) as sbdacct,count(distinct if(length(calledsub) in (4,7),calledsub,NULL)) as Terminals,
    sum(ceiling(sessiontime/60))/count(*) AS 'Min/call',sum(ceiling(sessiontime/60))/count(distinct username) as 'Min/sbdac', count(*) as Calls,if(calledprovider in ('SBDPLUS','PUBLIC','PRIVATE','SATFWD'),calledprovider ,if(calledprovider = 'SBDACES','ACeS',if(left(destination,4) = 'SFW-' ,'SATFWD','SBDLAND'))) as Service, '' as '%succ_call',
    (count(distinct (if(sipiax>0 or ((starttime >='2016-12-01' and starttime < '2016-12-06 20:54') and calledprovider in ('PUBLIC','PRIVATE') and sipiax=0 and ceiling(`sessiontime` / 60)>3 ),username,null)))) as FreeAccts,
    sum(sipiax)+0.8942*if((starttime >='2016-12-01' and starttime < '2016-12-06 20:54') and calledprovider in ('PUBLIC','PRIVATE') and sipiax=0 AND username <> '0001112006',(sum(ceiling((`sessiontime` / 60))) - sum(if((ceiling((`sessiontime` / 60)) <= 3),ceiling((`sessiontime` / 60)),if((`calledprovider` in ('PUBLIC','PRIVATE')),3,ceiling((`sessiontime` / 60)))))),0) as FreeMin,
    sum(sipiax * calledrate)+0.8942*if((starttime >='2016-12-01' and starttime < '2016-12-06 20:54') and calledprovider in ('PUBLIC','PRIVATE') and sipiax=0 AND username <> '0001112006',(sum(ceiling(`sessiontime` / 60)*calledrate) - sum(if((ceiling((`sessiontime` / 60)) <= 3),ceiling(`sessiontime` / 60)*calledrate,if((`calledprovider` in ('PUBLIC','PRIVATE')),3,ceiling(`sessiontime` / 60))*calledrate))),0) as FreeBaht,
    sum(sessionbill)-(sum(sipiax * calledrate)+0.8942*if((starttime >='2016-12-01' and starttime < '2016-12-06 20:54') and calledprovider in ('PUBLIC','PRIVATE') and sipiax=0 AND username <> '0001112006',(sum(ceiling(`sessiontime` / 60)*calledrate) - sum(if((ceiling((`sessiontime` / 60)) <= 3),ceiling(`sessiontime` / 60)*calledrate,if((`calledprovider` in ('PUBLIC','PRIVATE')),3,ceiling(`sessiontime` / 60))*calledrate))),0)) as Revenue
    from cc_call
    where starttime >='".$startdate."' and starttime <= '".$to_date."' 
    and (time(starttime) >= '$starttime' and time(starttime) <= '$totime')
    and left(destination,4) in ('SBD+','ACeS','SFW-') and sessiontime > 0 AND username <> '0001112006'
    group by CallDate, Service
    order by CallDate desc
";
*/
 //without Free Minute report
$sql = "
    select date(starttime) as CallDate,
    sum(if(destination in('SBD+SBDPLUS-SP','SBD+SBDPLUS-LAND','SBD+SBDPLUS','SBD+SBDPLUS-CBK','SBD+ACeS','SBD+ACeSSBD','ACeS','ACeSSBD','SFW-ACeS','SFW-SATFWDIC','SFW-SBDPLUS') and sessiontime>5,ceiling(sessiontime/60),0)) as SBD_Min,
    sum(if(destination like '%THAILAND%' and destination not like '%-SP' and sessiontime>5,ceiling(sessiontime/60),0)) as 'TH_Min',
    sum(if(destination like '%-SP' and sessiontime>5,ceiling(sessiontime/60),0)) as SP_Min,
    sum(if(destination not like '%-SP' and destination not in('SBD+ACeS','SBD+ACeSSBD','ACeS','ACeSSBD','SBD+SBDPLUS-SP','SBD+SBDPLUS-LAND','SBD+SBDPLUS','SBD+SBDPLUS-CBK','SBD+THAILAND-MOBILE','SBD+THAILAND','SBD+THAILAND-BKK','SBD+THAILAND (66)','SFW-THAILAND-MOBILE','SFW-THAILAND','SFW-THAILAND-BKK','SFW-THAILAND (66)','SFW-THAILAND-JASMINE','SFW-SATFWDIC','SFW-SBDPLUS') and sessiontime>5,ceiling(sessiontime/60),0)) as Oth_Min,
    sum(if(sessiontime>5,ceiling(sessiontime/60),0)) as Total_Min,
    sum(if(destination in('SBD+SBDPLUS-SP','SBD+SBDPLUS-LAND','SBD+SBDPLUS','SBD+SBDPLUS-CBK','SBD+ACeS','SBD+ACeSSBD','ACeS','ACeSSBD','SFW-ACeS','SFW-SATFWDIC','SFW-SBDPLUS'),sessionbill,0)) as SBD_Bht,
    sum(if(destination like '%THAILAND%' and destination not like '%-SP',sessionbill,0)) as 'TH_Bht',
    sum(if(destination like '%-SP',sessionbill,0)) as SP_Bht,
    sum(if(destination not like '%-SP' and destination not in('SBD+ACeS','SBD+ACeSSBD','ACeS','ACeSSBD','SBD+SBDPLUS-SP','SBD+SBDPLUS-LAND','SBD+SBDPLUS','SBD+THAILAND-MOBILE','SBD+THAILAND','SBD+THAILAND-BKK','SBD+SBDPLUS-CBK','SBD+THAILAND (66)','SFW-THAILAND-MOBILE','SFW-THAILAND','SFW-THAILAND-BKK','SFW-THAILAND (66)','SFW-THAILAND-JASMINE','SFW-SATFWDIC','SFW-SBDPLUS'),sessionbill,0)) as Oth_Bht,
    sum(sessionbill) as Total_Bht,32-MIN(IF(sessionid like 'SS7/msc%',cast(MID(sessionid,9,length(sessionid)-8) as signed),32)) AS CICmax, count(distinct username) as sbdacct,count(distinct if(length(calledsub) in (4,7),calledsub,NULL)) as Terminals,
    sum(ceiling(sessiontime/60))/count(*) AS 'Min/call',sum(ceiling(sessiontime/60))/count(distinct username) as 'Min/sbdac', count(*) as Calls,if(calledprovider in ('SBDPLUS','PUBLIC','PRIVATE','SATFWD'),calledprovider ,if(calledprovider = 'SBDACES','ACeS',if(left(destination,4) = 'SFW-' ,'SATFWD','SBDLAND'))) as Service, '' as '%succ_call'
    from cc_call
    where starttime >='".$startdate."' and starttime <= '".$to_date."' 
    and (time(starttime) >= '$starttime' and time(starttime) <= '$totime')
    and left(destination,4) in ('SBD+','ACeS','SFW-') and sessiontime > 0 AND username <> '0001112006'
    group by CallDate, Service
    order by CallDate desc
";
//*/
//    where starttime >='".$startdate." ".$starttime."' and starttime <='".$to_date." ".$totime."' 
//    where starttime >='".$startdate."' and starttime <= '".$to_date." 23:59:59' 


//echo $sql;
//set_time_limit(99000);

$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }
$fields = mysql_num_fields ( $s );
$body_msg .= ' <hr>
  <BR>
  <B>Sabaydee (ACeS & SBD-LAND & SF2500) Airtime Usage (Round Minutes) Report '.$startdate.' To '.$to_date.' </B>     
  <BR><BR>
  <TABLE>
  <TR>';
	for($i=0;$i<$fields;$i++){
	    $body_msg .='<TD  align=right bgcolor="orange"><b>'.mysql_field_name($s, $i).'</a></b></TD>';
	    	    
	}
	
			
	$ysbdpMinute = '';
	$ysbdpTotal = '';
	$ysbdpTHMobile = '';
	$ysbdpTH = '';
	$xsbdpDate = '';
	$ysbdpOther = '';
  $newdateflag = 0;
  
	$sum_subcolumn = array();
	$sum_subcolumn[] = 0;
	
	$sum_column = array();
	$sum_column[] = 0;
	$sum_dailyamt = 0;
	$irow = 0;
	$actrow = 0;
	while ($row = mysql_fetch_assoc($s)) {
	$body_msg .='<TR>';

	$irow++;
	$ireset = 0;
	$actrow++;
	
	foreach( $row as $value )
    {
	

		// array value

//	$ireset=0;
		//Date
		if($ireset == 0) {
		    if($xsbdpDate == '' | $xsbdpDate == UnixTime($value)){
			//$xDate = "'".UnixTime($value)."'";
					$newdateflag = 0;
		    } else  {
			//$xsbdpDate .= ",".UnixTime($value); 
					$newdateflag = 1;
		    }
			$xsbdpDate = UnixTime($value);
		}
 
 if($newdateflag == 1 & $xsbdpDate != '' ) {
//	$body_msg .= "</TR><TR>";	
	$sum_subcolumn[0] = "Subtotal";
	for($i=0;$i<$fields;$i++){
		if(($i >=1) && ($i < 11)|| ($i == 13) || $i == 19 || ($i == 20) ){
		
//		if(($i ==1) || ($i == 2)|| ($i == 4)|| ($i == 5)|| ($i == 7)){
			$body_msg .='<TD  align=right bgcolor="#FFFFB3"> <b>'.number_format($sum_subcolumn[$i]).'</b></TD>';
		}else if(($i ==21 || $i == 22) && $i){
			$body_msg .='<TD  align=right bgcolor="#FFFFB3"><b>'.number_format($sum_subcolumn[$i]).' ('.round($sum_subcolumn[$i]*100/$sum_dailyamt,2).'%)</b></TD>';
		}else{
			if($i==0){
			    $body_msg .='<TD  align=right bgcolor="#FFFFB3"><b>'.$sum_subcolumn[0].'&nbsp;</b></TD>';
			} else {
			    $body_msg .='<TD align=right bgcolor="#FFFFB3"><b>&nbsp;</b></TD>';
			}
		}
	 $sum_subcolumn[$i] = 0; //reset value
	 $xsbdpDate = '';
	 $newdateflag = 0;
	}
	$body_msg .= "</TR><TR>";	
}
		
		
		//THMobile
		if($ireset == 7) {
		    if($ysbdpTHMobile == ''){
			$ysbdpTHMobile .= $value;
		    } else {
			$ysbdpTHMobile .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		
		//TH
		if($ireset == 8) {
		    if($ysbdpTH == ''){
			$ysbdpTH .= $value;
		    } else {
			$ysbdpTH .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		//Other
		if($ireset == 9) {
		    if($ysbdpOther == ''){
			$ysbdpOther .= $value;
		    } else {
			$ysbdpOther .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		
		//Total Minute
		if($ireset == 5) {
		    if($ysbdpMinute == ''){
			$ysbdpMinute .= $value;
		    } else {
			$ysbdpMinute .= ','.$value; 
		    }
		    //$yMinute[] = $nvalue; 
		}
		if($ireset == 10) {
		    if($ysbdpTotal == ''){
			$ysbdpTotal .= $value;
		    } else {
			$ysbdpTotal .= ','.$value; 
		    }
		    //$yTotal[] = $nvalue; 
		}
    
		if(($ireset >=1) && ($ireset < 11) || $ireset == 19 || ($ireset == 20)|| ($ireset == 21) || ($ireset == 22)){
		    $nvalue = number_format($value);
		}else{
		    $nvalue = $value;
 				if($ireset == 17) {
		    	$svcvalue = $nvalue; 
				} else 
				if($ireset == 18 ) {
				 if($svcvalue==$actservice[$actrow]) {	
		    	$nvalue = $percent_succ[$actrow];
//		    	$actrow++; 
		     } else {
		    	$actrow++; 
		    	$nvalue = $percent_succ[$actrow];
		     }
				} else 
				if($ireset == 15 || ($ireset == 14) ) {
//		    	$nvalue =  number_format($nvalue/$actsubsc[$irow],2); 
		    		$nvalue = number_format($value,2);
				}

		}

		if(($irow%3)==0 ){
			$body_msg .='<TD bgcolor="#D0D2D2" align="right" nowrap> ';
			if($value == 't'){
			    $body_msg .= '<font color = "green"> ';
			}
			 $body_msg .= $nvalue.'</font></TD>';

			
		}else{
			$body_msg .='<TD bgcolor="#F7F7F7" align="right" nowrap> ';
			if($value == 't'){
			    $body_msg .= '<font color = "green"> ';
			}
			$body_msg .= $nvalue.'</font></TD>';
			
			
		}
		if($ireset == 0){ // Check Date
		//	$body_msg .= "<TR>";
		//    if ($nvalue == 't'){
		//	$sum_column[$ireset] += 1;
		//    }
		} else{
	 			$sum_dailyamt = $sum_subcolumn[10]; 	
		    $sum_column[$ireset] += $value;
	    	$sum_subcolumn[$ireset] += $value;    
		}
		$ireset++;
		
	 }	
	} //while

//Last subtotal
	$body_msg .= "</TR><TR>";	
	$sum_subcolumn[0] = "Subtotal";
	for($i=0;$i<$fields;$i++){
		if(($i >=1) && ($i < 11) || ($i == 13)|| $i == 19 || ($i == 20)  ){
		
//		if(($i ==1) || ($i == 2)|| ($i == 4)|| ($i == 5)|| ($i == 7)){
			$body_msg .='<TD  align=right bgcolor="#FFFFB3"> <b>'.number_format($sum_subcolumn[$i]).'</b></TD>';
		}else if($i ==21|| ($i == 22)){
			$body_msg .='<TD  align=right bgcolor="#FFFFB3"><b>'.number_format($sum_subcolumn[$i]).' ('.round($sum_subcolumn[$i]*100/$sum_dailyamt,2).'%)</b></TD>';
//			$body_msg .='<TD  align=right bgcolor="#FFFFB3"><b>'.number_format($sum_subcolumn[$i]).'xx'.$sum_subcolumn[$i].'xx'.$sum_subcolumn[10].'xxx'.$sum_dailyamt.' ('.round(number_format($sum_subcolumn[$i])*100/$sum_dailyamt,2).'%)</b></TD>';
		}else{
			if($i==0){
			    $body_msg .='<TD  align=right bgcolor="#FFFFB3"><b>'.$sum_subcolumn[0].'&nbsp;</b></TD>';
			} else {
			    $body_msg .='<TD align=right bgcolor="#FFFFB3"><b>&nbsp;</b></TD>';
			}
		}
	 $sum_subcolumn[$i] = 0; //reset value
	 $xsbdpDate = '';
	 $newdateflag = 0;
	}


	$body_msg .= "</TR><TR>";

//    mysql_close($link);

// Sum value
$sum_column[0] = "Total";
$sum_column[13]= $avgactsubsc;
$sum_column[18]= $avgpercent_succ;
$sum_column[19] = 868;

for($i=0;$i<$fields;$i++){
		if(($i >=1) && ($i < 11) || ($i == 13)|| ($i == 18)|| $i == 19 || ($i == 20) || ($i == 21)|| ($i == 22)  ){
		
		if($i ==18){
			$body_msg .='<TD  align=right bgcolor="#9E8785"><b>'.number_format($sum_column[$i],2).'</b></TD>';
		}else if($i ==21|| ($i == 22)){
			$body_msg .='<TD  align=right bgcolor="#9E8785"><b>'.number_format($sum_column[$i]).' ('.round($sum_column[$i]*100/$sum_column[10],2).'%)</b></TD>';
		}else $body_msg .='<TD  align=right bgcolor="#9E8785"><b>'.number_format($sum_column[$i]).'</b></TD>';
		}else{
			if($i==0){
			    $body_msg .='<TD  align=right bgcolor="#9E8785"><b>'.$sum_column[0].'&nbsp;</b></TD>';
			} else {
			    $body_msg .='<TD align=right bgcolor="#9E8785"><b>&nbsp;</b></TD>';
			}
		}
}

$body_msg .='</TR>';
//$body_msg .=' <TR><TD align=right width="80" bgcolor="#858585" ><b>'.mysql_field_name($s, 0).'</b></TD>';
//	for($i=1;$i<$fields;$i++){
//	    $body_msg .='<TD  align=right bgcolor="#858585"><b>'.mysql_field_name($s, $i).'</b></TD>';
//	    	    
//	};


$body_msg .='</TABLE>';




$body_msg .= '<BR><HR>';

//-----start----20190509----
/*$sql = "select date(starttime) as dd, sum(if(nasipaddress like 'SP%',1,0)) as SPeedcalls ,
count(*) as totalcalls, round(sum(if(nasipaddress like 'SP%',1,0))/count(*)*100,2) as '%speed',
sum(if(nasipaddress like 'SP%',CEILING(sessiontime/60),0)) as speedminutes,
sum(CEILING(sessiontime/60)) as totalminutes ,
round(sum(if(nasipaddress like 'SP%',CEILING(sessiontime/60),0))/sum(CEILING(sessiontime/60))*100,2) as '%speedmin', count(DISTINCT if(nasipaddress like 'SP%',username,0)) as speedusr,
count(DISTINCT username) as allusr, ROUND(count(DISTINCT if(nasipaddress like 'SP%',username,0))*100/count(DISTINCT username),2) AS '%speedusr'
from cc_call
where starttime > '".$startdate."' and sessiontime>0
group by dd ";
*/

$sql = "select 'Total' AS dd ,NULL as owner,count(distinct phoneid) as actterm,
(select COUNT(DISTINCT username)  from cc_call a
where starttime >= '".$startdate."' AND  (starttime < '".$todate."'  and  starttime < date(now()))
AND sessiontime>0) AS sbdacct,sum(calls) AS Calls,
sum(if(calltype='MO',minutes,NULL)) as MO,sum(if(calltype='MT',minutes,NULL)) as MT,
sum(if(calltype='MM',minutes,NULL)) as MM,sum(minutes) AS Minutes,sum(baht) AS  Baht
from a2billing.usage_phoneiddly a where date>= '".$startdate."' AND date <= '".$todate."'
";


$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }

//	$actsubsc = array();
     $sum_column = array();
     $sum_column[] = 0;

	while ($row = mysql_fetch_assoc($s)) {
		  $sum_column[2] = $row['actterm'];
			$sum_column[3] = $row['sbdacct'];
		  $sum_column[4] = $row['Calls'];
			$sum_column[5] = $row['MO'];
		  $sum_column[6] = $row['MT'];
			$sum_column[7] = $row['MM'];
		  $sum_column[8] = $row['Minutes'];
			$sum_column[9] = $row['Baht'];
	}

$sql = "select date AS dd ,if(isnull(owner),'Other',owner) as owner,count(distinct phoneid) as actterm,
sum(if(calltype='MO',sbdacct,NULL)) AS sbdacct,sum(calls) AS Calls,
sum(if(calltype='MO',minutes,NULL)) as MO,sum(if(calltype='MT',minutes,NULL)) as MT,
sum(if(calltype='MM',minutes,NULL)) as MM,sum(minutes) AS Minutes,sum(baht) AS  Baht
from a2billing.usage_phoneiddly a where date>= '".$startdate."' AND date <= '".$todate."'
GROUP BY dd,owner
ORDER BY date desc, Minutes desc, owner";
$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());

  }
$fields = mysql_num_fields ( $s );
$rowcount = mysql_num_rows ( $s );
$body_msg .= '
  <BR>
  <B> Sabaydee report by ship owner  <BR> <a href="http://www.jasfone.com/dailyusage_sbd_byship.jsp?stdate='.substr($startdate,0,10).'&endate='.substr($to_date,0,10).'&sowner=" target="_blank"> 
 	<IMG SRC="http://www.jasfone.com/dailyusage_sbd_byship.jsp?stdate='.substr($startdate,0,10).'&endate='.substr($to_date,0,10).'" alt="Preview SBD Chart=>Click at Chart to Zoom-in" style="width:100%"/> </a> </B> <BR>
  <TABLE>
  <BR>(Click link at Owner Name below to see its chart)<TR>';
        for($i=0;$i<$fields;$i++){
            $body_msg .='<TD  align=center bgcolor="Orange"><b>'.mysql_field_name($s, $i).'</a></b></TD>';

        }


        $irow = 0;
	$sum_subcolumn = array();
        $sum_subcolumn[] = 0;
	$sum_subcolumn[0] = 'SubTotal';
	$lastdate = '';
	$newdateflag = 0;
        while ($row = mysql_fetch_assoc($s)) {
        $body_msg .='<TR>';

        $irow++;
        $ireset = 0;

        foreach( $row as $value )
    {

//	$ireset=0;
		//Date
		if($ireset == 0) {
		    if($xsbdpDate == '' | $xsbdpDate == UnixTime($value)){
			//$xDate = "'".UnixTime($value)."'";
					$newdateflag = 0;
		    } else  {
			//$xsbdpDate .= ",".UnixTime($value); 
					$newdateflag = 1;
		    }
			$xsbdpDate = UnixTime($value);
		}
 
 if($newdateflag == 1 & $xsbdpDate != '' ) {
//	$body_msg .= "</TR><TR>";	
	$sum_subcolumn[0] = "Subtotal";
	for($i=0;$i<$fields;$i++){
		if(($i >=2) && ($i < 11)|| ($i == 13) || $i == 19 || ($i == 20) ){
		
//		if(($i ==1) || ($i == 2)|| ($i == 4)|| ($i == 5)|| ($i == 7)){
			$body_msg .='<TD  align=right bgcolor="#FFFFB3"> <b>'.number_format($sum_subcolumn[$i]).'</b></TD>';
		}else if(($i ==21 || $i == 22) && $i){
			$body_msg .='<TD  align=right bgcolor="#FFFFB3"><b>'.number_format($sum_subcolumn[$i]).' ('.round($sum_subcolumn[$i]*100/$sum_dailyamt,2).'%)</b></TD>';
		}else{
			if($i==0){
			    $body_msg .='<TD  align=right bgcolor="#FFFFB3"><b>'.$sum_subcolumn[0].'&nbsp;</b></TD>';
			} else {
			    $body_msg .='<TD align=right bgcolor="#FFFFB3"><b>&nbsp;</b></TD>';
			}
		}
	 $sum_subcolumn[$i] = 0; //reset value
	 $xsbdpDate = '';
	 $newdateflag = 0;
	}
	$body_msg .= "</TR><TR>";	
}

                $nvalue = $value;

                if($ireset == 12) $nvalue = $irow;

     //           if($ireset == 1) {
     //                   $sum_column[$ireset] += $nvalue;
     //           }
		if($ireset >= 2){
                    $nvalue = number_format($value,0);
		}


		if($ireset == 0) {
                    if($lastdate == '' | $lastdate == UnixTime($value)){
                        //$xDate = "'".UnixTime($value)."'";
                                        $newdateflag = 0;
                    } else  {
                        //$xsbdpDate .= ",".UnixTime($value);
                                        $newdateflag = 1;
                    }
                        $lastdate = UnixTime($value);
                }



                if(($irow%2)==0 ){
                        $body_msg .='<TD bgcolor="#B8B8B8" align="right" nowrap> <B> ';
//                        $body_msg .= '<font color = "brown"> ';
                     if($ireset == 1)  
                         $body_msg .=' <a href="http://www.jasfone.com/dailyusage_sbdmo_byship.jsp?stdate='.substr($startdate,0,10).'&endate='.substr($to_date,0,10).'&sowner='.substr($nvalue,0,5).'" target="_blank"> '.$nvalue.'</a></font></B></TD>';  
                     else
                         $body_msg .= $nvalue.'</font></B></TD>';


                }else{
                        $body_msg .='<TD bgcolor="#F7F7F7" align="right" nowrap> <B> ';
//                        $body_msg .= '<font color = "brown"> ';
                     if($ireset == 1)  
                         $body_msg .=' <a href="http://www.jasfone.com/dailyusage_sbdmo_byship.jsp?stdate='.substr($startdate,0,10).'&endate='.substr($to_date,0,10).'&sowner='.substr($nvalue,0,5).'" target="_blank"> '.$nvalue.'</a></font></B></TD>';  
                     else                        
                        $body_msg .= $nvalue.'</font></B></TD>';


                }
//	 if($ireset > 1){ // for activated = 't' count
 //                   $sum_column[$ireset] += $value;
//        }
		if($ireset == 0){ // Check Date
		//	$body_msg .= "<TR>";
		//    if ($nvalue == 't'){
		//	$sum_column[$ireset] += 1;
		//    }
		} else{
	 			//$sum_dailyamt = $sum_subcolumn[10]; 	
		    //$sum_column[$ireset] += $value;
	    	$sum_subcolumn[$ireset] += $value;    
		}
 	$ireset++;

        }
     } //while
//Last subtotal
	$body_msg .= "</TR><TR>";	
	$sum_subcolumn[0] = "Subtotal";
	for($i=0;$i<$fields;$i++){
		if(($i >=2) && ($i < 11) || ($i == 13)|| $i == 19 || ($i == 20)  ){
		
//		if(($i ==1) || ($i == 2)|| ($i == 4)|| ($i == 5)|| ($i == 7)){
			$body_msg .='<TD  align=right bgcolor="#FFFFB3"> <b>'.number_format($sum_subcolumn[$i]).'</b></TD>';
		}else if($i ==21|| ($i == 22)){
			$body_msg .='<TD  align=right bgcolor="#FFFFB3"><b>'.number_format($sum_subcolumn[$i]).' ('.round($sum_subcolumn[$i]*100/$sum_dailyamt,2).'%)</b></TD>';
//			$body_msg .='<TD  align=right bgcolor="#FFFFB3"><b>'.number_format($sum_subcolumn[$i]).'xx'.$sum_subcolumn[$i].'xx'.$sum_subcolumn[10].'xxx'.$sum_dailyamt.' ('.round(number_format($sum_subcolumn[$i])*100/$sum_dailyamt,2).'%)</b></TD>';
		}else{
			if($i==0){
			    $body_msg .='<TD  align=right bgcolor="#FFFFB3"><b>'.$sum_subcolumn[0].'&nbsp;</b></TD>';
			} else {
			    $body_msg .='<TD align=right bgcolor="#FFFFB3"><b>&nbsp;</b></TD>';
			}
		}
	 $sum_subcolumn[$i] = 0; //reset value
	 $xsbdpDate = '';
	 $newdateflag = 0;
	}


	$body_msg .= "</TR><TR>";
// Sum value
$sum_column[0] = "Total";
$sum_column[1] = "";
$body_msg .='</TR>';
//for($i=0;$i<$fields;$i++){
//                        $body_msg .='<TD  align=right bgcolor="#858585"><b>'.$sum_column[$i].'</b></TD>';
//}
for($i=0;$i<$fields;$i++){
		if(($i >=2) && ($i < 11) || ($i == 13)|| ($i == 18)|| $i == 19 || ($i == 20) || ($i == 21)|| ($i == 22)  ){
		
		if($i ==18){
			$body_msg .='<TD  align=right bgcolor="#9E8785"><b>'.number_format($sum_column[$i],2).'</b></TD>';
		}else if($i ==21|| ($i == 22)){
			$body_msg .='<TD  align=right bgcolor="#9E8785"><b>'.number_format($sum_column[$i]).' ('.round($sum_column[$i]*100/$sum_column[10],2).'%)</b></TD>';
		}else $body_msg .='<TD  align=right bgcolor="#9E8785"><b>'.number_format($sum_column[$i]).'</b></TD>';
		}else{
			if($i==0){
			    $body_msg .='<TD  align=right bgcolor="#9E8785"><b>'.$sum_column[0].'&nbsp;</b></TD>';
			} else {
			    $body_msg .='<TD align=right bgcolor="#9E8785"><b>&nbsp;</b></TD>';
			}
		}
}


$body_msg .='</TR></TABLE>';


//-----end----20190509------

//$body_msg .= '<BR><BR><img alt="ACeS Airtime" src="acesg2.php"/><BR>';
//$body_msg .= '<BR><BR><img alt="Sabaydee Airtime" src="sbdg2.php"/><BR>';
//$body_msg .= '<BR><BR><img alt="Sabaydee Plus Airtime" src="sbdpg2.php"/><BR>';


// Prepaid new subscriber report
/*
$db_name = "ARS";
$db_host = "172.16.177.14";
//$db_host = "172.16.128.14";

$link = mysql_connect($db_host,$db_user,$db_pass) ;
if (!$link) {
   die('Could not connect: ' . mysql_error());
}

mysql_select_db($db_name);
//    select left(starttime,10) as CallDate,
$sql = "
    select sum(if(cg_id =1,1,0)) as 'Mobile', sum(if(cg_id =2,1,0)) as 'Contax' , 
    sum(if(cg_id IN (25,26,27,28),1,0)) as 'GSPS', count(*) as TotalSubsc
    from customer where status_id in (4) and cg_id in (1,2,25,26,27,28)
    ";
    
    

$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }

	$value_column = array();
	$value_column[] = 0;
	if ($row = mysql_fetch_assoc($s)) {
   		$value_column[0] = $row['Mobile'];
   		$value_column[1] = $row['Contax'];
   		$value_column[2] = $row['GSPS'];
   		$value_column[3] = $row['TotalSubsc'];
	}

$sql = "
    select year(cus_start_date) as yy, month(cus_start_date) as mm , 
    sum(if(cg_id =1,1,0)) as 'newmobile', sum(if(cg_id =2,1,0)) as 'newcontax' , 
    sum(if(cg_id IN (25,26,27,28),1,0)) as 'newgsps', count(*) as newsubsc
    from customer a where status_id in (4,7) and cg_id in (1,2,25,26,27,28) and 
    cus_start_date > DATE_SUB(date(CONCAT_WS('-', cast(year(now()) as char),cast(month(now()) as char),'01')),INTERVAL 6 month)
    group by yy,mm ";


$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }

	$actsubsc = array();
	$actsubsc[] = 0;
	$i = 1;
	while ($row = mysql_fetch_assoc($s)) {
			$newmobile[$i]= $row['newmobile'];
			$newcontax[$i]= $row['newcontax'];
			$newgsps[$i]= $row['newgsps'];
			$newsubsc[$i]= $row['newsubsc'];
			$i++;
	}


mysql_select_db($db_name);

$sql = "
    select year(cus_status_date) as yy, month(cus_status_date) as mm ,'' as 'New ', 
    sum(if(cg_id =1,1,0)) as 'Expired ', '' as New, sum(if(cg_id =2,1,0)) as 'Expired' , 
    '' as 'New   ', sum(if(cg_id IN (25,26,27,28),1,0)) as 'Expired   ','' as 'New  ',count(*) as 'Expired  '
    from customer where status_id in (7) and cg_id in (1,2,25,26,27,28) and 
    cus_status_date > DATE_SUB(date(CONCAT_WS('-', cast(year(now()) as char),cast(month(now()) as char),'01')),INTERVAL 6 month)
    group by yy,mm ";

$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }
$fields = mysql_num_fields ( $s );
$body_msg .= ' <hr>
  <BR>
  <B>Prepaid new & expired subscribers</B>    
  <BR><BR>
  <TABLE>
  <TR>';
  
  $body_msg .='<TR> <TD> </TD> <TD> </TD> <TD colspan="2" align=center bgcolor="Green"><b> MOBILE </a></b></TD> <TD colspan="2" align=center bgcolor="Orange"><b> Contax </a></b></TD> <TD colspan="2" align=center bgcolor="cyan"><b> GSPS </a></b></TD><TD colspan="2" align=center bgcolor="Gray"><b> Total </a></b></TD></TR>';
  $body_msg .='<TR> <TD><B> Current </TD> <TD><B> Active </TD> <TD colspan="2" align=center bgcolor="Green"><b>'.$value_column[0].'</a></b></TD> <TD colspan="2" align=center bgcolor="Orange"><b>'.$value_column[1].'</a></b></TD> <TD colspan="2" align=center bgcolor="cyan"><b>'.$value_column[2].'</a></b></TD> <TD colspan="2" align=center bgcolor="Gray"><b>'.$value_column[3].'</a></b></TD></TR>';
	for($i=0;$i<$fields;$i++){
		if($i == 2 || $i == 4 || $i == 6|| $i == 8)
		    $body_msg .='<TD  align=right bgcolor="lightgreen"><b>'.mysql_field_name($s, $i).'</a></b></TD>';
		else 
	    $body_msg .='<TD  align=right bgcolor="#858585"><b>'.mysql_field_name($s, $i).'</a></b></TD>';
	    	    
	}
	
			
	$ysbdMinute = '';
	$ysbdTotal = '';
	$ysbdTHMobile = '';
	$ysbdTH = '';
	$xsbdDate = '';
	$ysbdOther = '';
	
	$sum_column = array();
	$sum_column[] = 0;
	
	$irow = 0;
	while ($row = mysql_fetch_assoc($s)) {
	$body_msg .='<TR>';

	$irow++;
	$ireset = 0;
	
	foreach( $row as $value )
    {
	

		if($ireset == 2){
		    $nvalue = $newmobile[$irow];
		} else if($ireset == 4){
		    $nvalue = $newcontax[$irow];
		} else if($ireset == 6){
		    $nvalue = $newgsps[$irow];
		} else if($ireset == 8){
		    $nvalue = $newsubsc[$irow];
		} else {
		    $nvalue = $value;
		} 



		if(($irow%2)==0 ){
			$body_msg .='<TD bgcolor="#B8B8B8" align="right" nowrap> ';
			if($ireset == 2 || $ireset == 4 || $ireset == 6 || $ireset == 8){
			    $body_msg .= '<B><font color = "green"> ';
			} else if($ireset >=3) {
			    $body_msg .= '<B><font color = "red"> ';				
			}
			 $body_msg .= $nvalue.'</font></TD>';

			
		}else{
			$body_msg .='<TD bgcolor="#F7F7F7" align="right" nowrap> ';
			if($ireset == 2 || $ireset == 4 || $ireset == 6 || $ireset == 8){
			    $body_msg .= '<B><font color = "green"> ';
			} else if($ireset >=3) {
			    $body_msg .= '<B><font color = "red"> ';				
			}
			$body_msg .= $nvalue.'</font></TD>';
			
			
		}
		if($ireset == 0){ // for activated = 't' count
		//    if ($nvalue == 't'){
		//	$sum_column[$ireset] += 1;
		//    }
		} else{
		    $sum_column[$ireset] += $value;
		}
		$ireset++;
		
	}
//	$ireset=0;
	
	} //while
	$body_msg .= "</TR><TR>";

//    mysql_close($link);

// Sum value
$sum_column[0] = "Total";
for($i=0;$i<$fields;$i++){
		if(($i >1) && ($i < 1)){
		
//		if(($i ==1) || ($i == 2)|| ($i == 4)|| ($i == 5)|| ($i == 7)){
			$body_msg .='<TD  align=right bgcolor="#858585"><b>'.number_format($sum_column[$i],2).'</b></TD>';
		}else{
			if($i==0){
			    $body_msg .='<TD  align=right bgcolor="#858585"><b>'.$sum_column[0].'&nbsp;</b></TD>';
			} else {
			    $body_msg .='<TD  align=right bgcolor="#858585"><b>&nbsp;</b></TD>';
			}
		}
}

$body_msg .='</TR>';
//$body_msg .=' <TR><TD align=right width="80" bgcolor="#858585" ><b>'.mysql_field_name($s, 0).'</b></TD>';
//	for($i=1;$i<$fields;$i++){
//	    $body_msg .='<TD  align=right bgcolor="#858585"><b>'.mysql_field_name($s, $i).'</b></TD>';
//	    	    
//	};


$body_msg .='</TABLE>';

$_SESSION['xsbddate'] = $xsbdDate;
$_SESSION['ysbdMinute'] = $ysbdMinute;
$_SESSION['ysbdTotal'] = $ysbdTotal; 
$_SESSION['ysbdTHMobile'] = $ysbdTHMobile;
$_SESSION['ysbdTH'] = $ysbdTH;
$_SESSION['ysbdOther'] = $ysbdOther;
*/
// end section

// Sabaydee+ Terminals monthly Activation report
$db_name = "a2billing";
$db_host = "172.16.130.212:3307";
//$db_host = "172.16.128.233";
$db_server = "172.16.130.212";
//$last_line = system('ping -c1 -w1 '.$db_server.' |grep 64', $retval);
if( $retval <> 0 ) { 
	$db_host = "172.16.128.233";
}
$link = mysql_connect($db_host,$db_user,$db_pass) ;
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
mysql_select_db($db_name);


// Sabaydee+ CC report
//Success Topup record
$sql = "SELECT date(request_date) AS requestdt, sum(IF(amount=500,1,0)) AS succ500B, sum(IF(amount=1000,1,0)) 
    AS succ1000B, sum(IF(amount=1500,1,0)) AS succ1500B, sum(IF(amount=4000,1,0)) AS succ4000B, sum(IF(amount=5000,1,0)) AS succ5000B FROM sbd_req_topup 
    WHERE request_date >='".$startdate."' and request_date <= '".$to_date."' 
    and (time(request_date) >= '$starttime' and time(request_date) <= '$totime')  AND flag='s'
    GROUP BY requestdt
";


$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }

	$topupdate = array();
	$topupsucc = array();
	$sumsucc = array();
//	$topup1000 = array();
//	$topup1500 = array();
	$topupdate[] = '';
	$topupsucc[][] = 0;
//	$topup1000[] = 0;
//	$topup1500[] = 0;
	$sumsucc[] = 0; // Total success topup
	
	$i = 1;
	while ($row = mysql_fetch_assoc($s)) {
			$topupdate[$i]= $row['requestdt'];
			$topupsucc[$i][1]= $row['succ500B'];
			$topupsucc[$i][2]= $row['succ1000B'];
			$topupsucc[$i][3]= $row['succ1500B'];
			$topupsucc[$i][4]= $row['succ4000B'];
			$topupsucc[$i][5]= $row['succ5000B'];
//			$topup1000[$i]= $row['succ1000B'];
//			$topup1500[$i]= $row['succ1500B'];
			$sumsucc[1] += $row['succ500B'];
			$sumsucc[2] += $row['succ1000B'];
			$sumsucc[3] += $row['succ1500B'];
			$sumsucc[4] += $row['succ4000B'];
			$sumsucc[5] += $row['succ5000B'];
///		echo $topupsucc[$i][1].'|'.$topupsucc[$i][2].'|'.$topupsucc[$i][3].'-';
			$i++;
	}
  $rowtopup = $i-1; 
  
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
mysql_select_db($db_name);
//    select left(starttime,10) as CallDate,
$sql = "
    SELECT date(calldate) AS CallDate, sum(IF(MID(userfield,14,1)='1',1,0)) AS 'req500B/succ', 
    sum(IF(MID(userfield,14,1)='2',1,0)) AS 'req1000B/succ',
    sum(IF(MID(userfield,14,1)='3',1,0)) AS 'req1500B/succ',sum(IF(MID(userfield,14,1)='4',1,0)) AS 'req4000B/succ',
    sum(IF(MID(userfield,14,1)='5',1,0)) AS 'req5000B/succ',sum(IF(MID(userfield,14,1)='0',1,0)) AS cc_calls,
    sum(IF(MID(userfield,14,1)='0' AND dst in ('990999916070','990999904000'),billsec,0))/60 AS cc_free_min,
    sum(IF(MID(userfield,14,1)='0' AND dst in ('9909021016070','9909021004000'),billsec,0))/60 AS cc_charged_min, 
    sum(IF(MID(userfield,14,1)='0',billsec,0))/60 AS cc_total_min,
    sum(IF(MID(userfield,14,1)='0' AND dst in ('990999916070','990999904000'),billsec,0))/sum(IF(MID(userfield,14,1)='0',billsec,0))*100.0 AS '%cc_free',
    sum(IF(MID(userfield,14,1)='0' AND dst in ('9909021016070','9909021004000'),billsec,0))/sum(IF(MID(userfield,14,1)='0',billsec,0))*100.0 AS '%cc_charged'
    FROM asterisk.cdr230 WHERE calldate > '".$startdate."' AND calldate < '".$to_date."' and userfield like  '%-CCpress-%'
    AND MID(userfield,14,1) IN ('0','1','2','3','4','5')
    GROUP BY date(calldate)
";



//echo $sql;
//set_time_limit(99000);

$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }
$fields = mysql_num_fields ( $s );
$body_msg .= ' <hr>
  <BR>
  <B>Sabaydee Plus CC (1030) Report '.$startdate.' To '.$to_date.' </B>    
  <BR><BR>
  <TABLE>
  <TR>';
	for($i=0;$i<$fields;$i++){
	    $body_msg .='<TD  align=right bgcolor="orange"><b>'.mysql_field_name($s, $i).'</a></b></TD>';
	    	    
	}
	
			
	$sum_column = array();
	$sum_column[] = 0;
	
	$irow = 0;
	while ($row = mysql_fetch_assoc($s)) {
	$body_msg .='<TR>';

	$irow++;
	$ireset = 0;
	$reqdate = $startdate; 
	
	foreach( $row as $value )
    {
	

		// array value

		if($ireset == 0){
		    $nvalue = $value;
		    $reqdate = $nvalue;
		}else if($ireset > 6){
		    $nvalue = number_format($value,2);
		}else if($ireset >=1 && $ireset <=5) { //num of Topup500,1000,1500,4000,5000Baht   
//			echo $reqdate.$topupdate[$irow].$topupsucc[$irow][$ireset];
			$i = 1;
			while ($reqdate != $topupdate[$i] && $i<=$rowtopup) {//until match date or reach rowcount
				$i++;
			}
			if($reqdate == $topupdate[$i] ) {
			 $nvalue = $value.'/'.$topupsucc[$i][$ireset];
			} else $nvalue = $value.'/0';
		}else{
		    $nvalue = $value;

		}

		if(($irow%2)==0 ){
			$body_msg .='<TD bgcolor="#B8B8B8" align="right" nowrap> ';
			if($value == 't'){
			    $body_msg .= '<font color = "green"> ';
			}
			 $body_msg .= $nvalue.'</font></TD>';

			
		}else{
			$body_msg .='<TD bgcolor="#F7F7F7" align="right" nowrap> ';
			if($value == 't'){
			    $body_msg .= '<font color = "green"> ';
			}
			$body_msg .= $nvalue.'</font></TD>';
			
			
		}
		if($ireset == 0){ // for activated = 't' count
		//    if ($nvalue == 't'){
		//	$sum_column[$ireset] += 1;
		//    }
		} else{
		    $sum_column[$ireset] += $value;
		}
		$ireset++;
		
	}
//	$ireset=0;
	
	} //while
	$body_msg .= "</TR><TR>";

//    mysql_close($link);
// Sum value
$sum_column[0] = "Total";
$sum_column[1] = $sum_column[1].'/'.$sumsucc[1]; 
$sum_column[2] = $sum_column[2].'/'.$sumsucc[2]; 
$sum_column[3] = $sum_column[3].'/'.$sumsucc[3]; 
$sum_column[4] = $sum_column[4].'/'.$sumsucc[4]; 
$sum_column[5] = $sum_column[5].'/'.$sumsucc[5]; 
$sum_column[10] = $sum_column[7]/$sum_column[9]*100; 
$sum_column[11] = $sum_column[8]/$sum_column[9]*100; 
for($i=0;$i<$fields;$i++){
		if(($i >=7) && ($i < 11)){
		
//		if(($i ==1) || ($i == 2)|| ($i == 4)|| ($i == 5)|| ($i == 7)){
			$body_msg .='<TD  align=right bgcolor="#858585"><b>'.number_format($sum_column[$i],2).'</b></TD>';
		}else{
			if($i<6){
			    $body_msg .='<TD  align=right bgcolor="#858585"><b>'.$sum_column[$i].'&nbsp;</b></TD>';
			} else {
			    $body_msg .='<TD  align=right bgcolor="#858585"><b>'.number_format($sum_column[$i],0).'</b></TD>';
			}
		}
}

$body_msg .='</TR>';
//$body_msg .=' <TR><TD align=right width="80" bgcolor="#858585" ><b>'.mysql_field_name($s, 0).'</b></TD>';
//	for($i=1;$i<$fields;$i++){
//	    $body_msg .='<TD  align=right bgcolor="#858585"><b>'.mysql_field_name($s, $i).'</b></TD>';
//	    	    
//	};


$body_msg .='</TABLE>';
//END SBDPLUS CC Statistic



// Sabaydee+ Call Monitoring
$sql = "SELECT HOUR(calldate) AS HR, ROUND(COUNT(*)/7,0) AS Avg_Calls , SUM(IF(date(calldate)= DATE_SUB(date(now()),INTERVAL 1 day) ,1,0)) AS 'CallsDay-1', SUM(IF(date(calldate)= date(NOW()),1,0)) AS Calls,  Round((SUM(IF(date(calldate)= date(NOW()),1,0))-ROUND(COUNT(*)/7,0))*100/ROUND(COUNT(*)/7,0),2) AS '%(Calls-Avg)', 
ROUND(SUM(billsec)/60/7,2) AS Avg_Min,Round(SUM(IF(date(calldate)= DATE_SUB(date(now()),INTERVAL 1 day),billsec,0))/60,2) AS 'MinDay-1', Round(SUM(IF(date(calldate)= date(NOW()),billsec,0))/60,2) AS Minutes, round((SUM(IF(date(calldate)= date(NOW()),billsec,0))/60-ROUND(SUM(billsec)/60/7,2))*100/ROUND(SUM(billsec)/60/7,2),2) AS '%(Minutes-Avg)' 
FROM asterisk.cdr230
WHERE date(calldate) >= DATE_SUB(date(now()),INTERVAL 7 day) AND dcontext='sabaydee+' and hour(calldate) <=HOUR(DATE_SUB(NOW(), INTERVAL 60 minute))  AND (channel LIKE 'DAHDI%' or channel LIKE 'SS7/msc%' or channel LIKE 'SIP/sip218%' or channel LIKE 'SIP/sip235%')  AND LEFT(userfield,4) not in ('2299','2999') #AND MINUTE(calldate) <= MINUTE(NOW()) AND  dst LIKE '9909%'
GROUP BY HR ";

$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }
$fields = mysql_num_fields ( $s );
$rowcount = mysql_num_rows ( $s );
$body_msg .= ' 
  <BR>
  <B> Sabaydee Plus Realtime Hourly Call Monitoring </B>    
  <BR><BR>
  <TABLE>
  <TR>';
	for($i=0;$i<$fields;$i++){
	    $body_msg .='<TD  align=center bgcolor="Orange"><b>'.mysql_field_name($s, $i).'</a></b></TD>';
	    	    
	}
	
	$sum_column = array();
	$sum_column[] = 0;
	
	$irow = 0;
	while ($row = mysql_fetch_assoc($s)) {
	$body_msg .='<TR>';

	$irow++;
	$ireset = 0;
	
	foreach( $row as $value )
    {
	

		$nvalue = $value;
		
		if($ireset >= 1) {
			$sum_column[$ireset] += $nvalue;
		}
		 
		if(($irow%2)==0 ){
			$body_msg .='<TD bgcolor="#B8B8B8" align="right" nowrap> <B> ';
			if($value < -30){
			    $body_msg .= '<font color = "red"> ';
			} else if($value < -5){
			    $body_msg .= '<font color = "brown"> ';
			} else if($value >= 100){
			    $body_msg .= '<font color = "green"> ';
			} 
			 $body_msg .= $nvalue.'</font></B></TD>';

			
		}else{
			$body_msg .='<TD bgcolor="#F7F7F7" align="right" nowrap> <B> ';
			if($value < -30){
			    $body_msg .= '<font color = "red"> ';
			} else if($value < -5){
			    $body_msg .= '<font color = "brown"> ';
			} else if($value >= 100){
			    $body_msg .= '<font color = "green"> ';
			} 
			$body_msg .= $nvalue.'</font></B></TD>';
			
			
		}
		$ireset++;
		
	}
//	$ireset=0;
	
	} //while
	$body_msg .= "</TR><TR>";

//    mysql_close($link);

// Sum value
$sum_column[0] = "Total";
$sum_column[4] = round(($sum_column[3]-$sum_column[1])*100/$sum_column[1],2) ;
$sum_column[8] = round(($sum_column[7]-$sum_column[5])*100/$sum_column[5],2) ;
for($i=0;$i<$fields;$i++){
			$body_msg .='<TD  align=right bgcolor="#858585"><b>'.$sum_column[$i].'</b></TD>';
}

$body_msg .='</TR>';
//$body_msg .=' <TR><TD align=right width="80" bgcolor="#858585" ><b>'.mysql_field_name($s, 0).'</b></TD>';
//	for($i=1;$i<$fields;$i++){
//	    $body_msg .='<TD  align=right bgcolor="#858585"><b>'.mysql_field_name($s, $i).'</b></TD>';
//	    	    
//	};


$body_msg .='</TABLE>';

// END Sabaydee+ Call Monitoring
// Sabaydee+ Call Monitoring by Active PhoneID
$sql = "SELECT if(userfield like '%-CCpress-%',LEFT(userfield,locate('-',userfield)-1),LEFT(userfield,locate(',',userfield)-1)) AS phoneid, ROUND(COUNT(*)/7,0) AS Avg_Calls , SUM(IF(date(calldate)= DATE_SUB(date(now()),INTERVAL 1 day) ,1,0)) AS 'CallsDay-1', SUM(IF(date(calldate)= date(NOW()),1,0)) AS Calls,  Round((SUM(IF(date(calldate)= date(NOW()),1,0))-ROUND(COUNT(*)/7,0))*100/ROUND(COUNT(*)/7,0),2) AS '%(Calls-Avg)', 
ROUND(SUM(billsec)/60/7,2) AS Avg_Min,Round(SUM(IF(date(calldate)= DATE_SUB(date(now()),INTERVAL 1 day),billsec,0))/60,2) AS 'MinDay-1', Round(SUM(IF(date(calldate)= date(NOW()),billsec,0))/60,2) AS Minutes, round((SUM(IF(date(calldate)= date(NOW()),billsec,0))/60-ROUND(SUM(billsec)/60/7,2))*100/ROUND(SUM(billsec)/60/7,2),2) AS '%(Minutes-Avg)' 
, count(DISTINCT accountcode) AS accts7days , count(DISTINCT IF(date(calldate)= DATE_SUB(date(now()),INTERVAL 1 day),accountcode,0)) AS 'acctsday-1', count(DISTINCT IF(date(calldate)= date(now()),accountcode,0)) AS 'acctstoday', '' as 'Act#' FROM asterisk.cdr230
WHERE date(calldate) >= DATE_SUB(date(now()),INTERVAL 7 day) AND  dcontext='sabaydee+' and hour(calldate) <= HOUR(NOW()) AND (channel LIKE 'DAHDI%' or channel LIKE 'SS7/msc%' or  channel LIKE 'SIP/sip218%' or channel LIKE 'SIP/sip235%') AND LEFT(userfield,4) not in ('2299','2999') #AND MINUTE(calldate) <= MINUTE(NOW()) and  dst LIKE '9909%'
GROUP BY phoneid
having SUM(IF(date(calldate)= date(NOW()),1,0)) > 0  ";

$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }
$fields = mysql_num_fields ( $s );
$rowcount = mysql_num_rows ( $s );
$body_msg .= ' 
  <BR>
  <B> Sabaydee Plus Realtime Call Monitoring by Active PhoneID </B>    
  <BR><BR>
  <TABLE>
  <TR>';
	for($i=0;$i<$fields;$i++){
	    $body_msg .='<TD  align=center bgcolor="Orange"><b>'.mysql_field_name($s, $i).'</a></b></TD>';
	    	    
	}
	
	$sum_column = array();
	$sum_column[] = 0;
	
	$irow = 0;
	while ($row = mysql_fetch_assoc($s)) {
	$body_msg .='<TR>';

	$irow++;
	$ireset = 0;
	
	foreach( $row as $value )
    {
	

		$nvalue = $value;
		
		if($ireset == 12) $nvalue = $irow;
		
		if($ireset >= 1) {
			$sum_column[$ireset] += $nvalue;
		}
		 
		if(($irow%2)==0 ){
			$body_msg .='<TD bgcolor="#B8B8B8" align="right" nowrap> <B> ';
			if($value < -30){
			    $body_msg .= '<font color = "red"> ';
			} else if($value < -5){
			    $body_msg .= '<font color = "brown"> ';
			} else if($value >= 100){
			    $body_msg .= '<font color = "green"> ';
			} 
			 $body_msg .= $nvalue.'</font></B></TD>';

			
		}else{
			$body_msg .='<TD bgcolor="#F7F7F7" align="right" nowrap> <B> ';
			if($value < -30){
			    $body_msg .= '<font color = "red"> ';
			} else if($value < -5){
			    $body_msg .= '<font color = "brown"> ';
			} else if($value >= 100){
			    $body_msg .= '<font color = "green"> ';
			} 
			$body_msg .= $nvalue.'</font></B></TD>';
			
			
		}
		$ireset++;
		
	}
//	$ireset=0;
	
	} //while
	$body_msg .= "</TR><TR>";

//    mysql_close($link);

// Sum value
$sum_column[0] = "Total";
$sum_column[4] = round(($sum_column[3]-$sum_column[1])*100/$sum_column[1],2) ;
$sum_column[8] = round(($sum_column[7]-$sum_column[5])*100/$sum_column[5],2) ;
$sum_column[12] = '';
for($i=0;$i<$fields;$i++){
			$body_msg .='<TD  align=right bgcolor="#858585"><b>'.$sum_column[$i].'</b></TD>';
}

$body_msg .='</TR>';
//$body_msg .=' <TR><TD align=right width="80" bgcolor="#858585" ><b>'.mysql_field_name($s, 0).'</b></TD>';
//	for($i=1;$i<$fields;$i++){
//	    $body_msg .='<TD  align=right bgcolor="#858585"><b>'.mysql_field_name($s, $i).'</b></TD>';
//	    	    
//	};


$body_msg .='</TABLE>';

// END Sabaydee+ Call Monitoring by PhoneID
// Sabaydee+ Speed Dial Performance 
$sql = "select date(starttime) as dd, sum(if(nasipaddress like 'SP%',1,0)) as SPeedcalls ,
count(*) as totalcalls, round(sum(if(nasipaddress like 'SP%',1,0))/count(*)*100,2) as '%speed',
sum(if(nasipaddress like 'SP%',CEILING(sessiontime/60),0)) as speedminutes,
sum(CEILING(sessiontime/60)) as totalminutes , 
round(sum(if(nasipaddress like 'SP%',CEILING(sessiontime/60),0))/sum(CEILING(sessiontime/60))*100,2) as '%speedmin', count(DISTINCT if(nasipaddress like 'SP%',username,0)) as speedusr, 
count(DISTINCT username) as allusr, ROUND(count(DISTINCT if(nasipaddress like 'SP%',username,0))*100/count(DISTINCT username),2) AS '%speedusr' 
from cc_call 
where starttime > '".$startdate."' and sessiontime>0  
group by dd ";

$s = mysql_query($sql);
if ($s == 0)
  {
      die('Failed to query data to database:<br>'."\n".mysql_error());
          
  }
$fields = mysql_num_fields ( $s );
$rowcount = mysql_num_rows ( $s );
$body_msg .= ' 
  <BR>
  <B> Sabaydee Plus Speed Dial Daily Performance </B>    
  <BR><BR>
  <TABLE>
  <TR>';
	for($i=0;$i<$fields;$i++){
	    $body_msg .='<TD  align=center bgcolor="Orange"><b>'.mysql_field_name($s, $i).'</a></b></TD>';
	    	    
	}
	
	$sum_column = array();
	$sum_column[] = 0;
	
	$irow = 0;
	while ($row = mysql_fetch_assoc($s)) {
	$body_msg .='<TR>';

	$irow++;
	$ireset = 0;
	
	foreach( $row as $value )
    {
	

		$nvalue = $value;
		
		if($ireset == 12) $nvalue = $irow;
		
		if($ireset >= 1) {
			$sum_column[$ireset] += $nvalue;
		}
		 
		if(($irow%2)==0 ){
			$body_msg .='<TD bgcolor="#B8B8B8" align="right" nowrap> <B> ';
			if($value < -30){
			    $body_msg .= '<font color = "red"> ';
			} else if($value < -5){
			    $body_msg .= '<font color = "brown"> ';
			} 
			 $body_msg .= $nvalue.'</font></B></TD>';

			
		}else{
			$body_msg .='<TD bgcolor="#F7F7F7" align="right" nowrap> <B> ';
			if($value < -30){
			    $body_msg .= '<font color = "red"> ';
			} else if($value < -5){
			    $body_msg .= '<font color = "brown"> ';
			} 
			$body_msg .= $nvalue.'</font></B></TD>';
			
			
		}
		$ireset++;
		
	}
//	$ireset=0;
	
	} //while
//	$body_msg .= "</TR><TR>";

//    mysql_close($link);
/*
// Sum value
$sum_column[0] = "Total";
$sum_column[4] = round(($sum_column[3]-$sum_column[1])*100/$sum_column[1],2) ;
$sum_column[8] = round(($sum_column[7]-$sum_column[5])*100/$sum_column[5],2) ;
$sum_column[12] = '';
for($i=0;$i<$fields;$i++){
			$body_msg .='<TD  align=right bgcolor="#858585"><b>'.$sum_column[$i].'</b></TD>';
}
*/
$body_msg .='</TR>';
//$body_msg .=' <TR><TD align=right width="80" bgcolor="#858585" ><b>'.mysql_field_name($s, 0).'</b></TD>';
//	for($i=1;$i<$fields;$i++){
//	    $body_msg .='<TD  align=right bgcolor="#858585"><b>'.mysql_field_name($s, $i).'</b></TD>';
//	    	    
//	};


$body_msg .='</TABLE>';

// END Sabaydee+ Call Monitoring by PhoneID

$body_msg .= '<BR> <BR> <B> <a href="./sbdplus_terminal_rpt.php"> คลิกเพื่อดูข้อมูล Sabaydee Plus Terminals Activation report </a> </B> ';
$body_msg .= '<BR> <B> <a href="./roaming-register.php"> Roaming Register Report </a> </B><BR><BR><BR> ';




$body_msg .='</center></BODY></HTML>';

$body .= chunk_split(base64_encode($body_msg));
mysql_close($link);

//send message
//mail($to_email0, "Jasfone daily usage report", $body, $headers);
//mail($to_email1, "Jasfone daily usage report", $body, $headers);
//mail($to_email2, "Jasfone daily usage report", $body, $headers);
//mail($to_email3, "Jasfone daily usage report", $body, $headers);
//mail($to_email4, "Jasfone daily usage report", $body, $headers);
//mail($to_email5, "Jasfone daily usage report", $body, $headers);

//echo "Report Send.<BR>";
echo $body_msg;


?>