<?php namespace App\Console\Commands;

use Illuminate\Console\Command;
//use Mail;
use DB;
use Config;

use App\Models\User;
use App\Models\Accesslog;

use App\Models\CRMCall;
use Schema;
use PDO;

use Illuminate\Database\Schema\Blueprint;

class monthly_updatecalllog extends Command {

	/**
	 * The console command name.
	 *
	 * @var string
	 */
	protected $signature = 'monthly_updatecalllog';

	/**
	 * The console command description.
	 *
	 * @var string
	 */
	protected $description = 'App Main Daily Task for CreportEight';

	/**
	 * Execute the console command.
	 *
	 * @return mixed
	 */
	public function handle()
	{
		$nowts=time();
		$date=date('F_Y');
		echo "\n".date('Y-m-d H:i:s')."\n";		
		
			$logdate=strtotime('0 day');

			$tcol=0;$fieldsarr=array();$extrahdrarr=array();
			$central_ip=env('central_ip');
			$server_ip=env('app_ip');
			$calllog_report = "calllog_report_".$date;
			$conn = array(
				'driver'    => 'mysql',
				'host'      => $central_ip,
				'database'  => env('DB_DATABASE', 'kstych_flexydial'),
				'username'  => env('DB_USERNAME', 'root'),
				'password'  => env('DB_PASSWORD', ''),
				'charset'   => 'utf8',
				'collation' => 'utf8_unicode_ci',
				'prefix'    => '',
				'options'   => array(
					PDO::ATTR_TIMEOUT => 5,
					),
				);
			Config::set("database.connections.conn", $conn);

			DB::connection("conn")->getDatabaseName();

			$serverclist=DB::connection("conn")->select(DB::raw("select id from server_details where server_ip='$server_ip'"));     
                        $server_id=$serverclist[0]->id;
                        if($server_id<10){
                        $server_id="0".$server_id;
                        }


			$maxid=DB::connection("conn")->select(DB::raw("SELECT max(crmcall_id) as maxid from $calllog_report where server='$server_id'"));

                       $maxids=$maxid[0]->maxid;

			//$alist=DB::select(DB::raw("SELECT * from crmcalls where id>'$maxids' and created_at<'".date("Y-m-d H:i:s",$logdate-(60*60))."'"));
			$alist=DB::select(DB::raw("SELECT * from crmcalls where id>'$maxids'"));

			$userarr=array();
			$shortinsert="";
			foreach($alist as $aline)
			{
				$setstrarr=array();


				$clientcode="";$currentstatus="";$legalstatus="";$record_id="";
				if($aline->crm_id>0)
				{
				$user=DB::select(DB::raw("select id,clientcode,currentstatus,legalstatus from records where id='".$aline->crm_id."' limit 1;"));
					if(isset($user[0]))
					{
						$record_id=$user[0]->id;
						$clientcode=$user[0]->clientcode;
						$currentstatus=$user[0]->currentstatus;
						$legalstatus=$user[0]->legalstatus;
					}
				}
				$tpostdata=json_decode($aline->data,true);
				$fulldate=date("Y-m-d H:i:s",strtotime($aline->created_at)+330*60);
				$talktime=$aline->talkSec+$aline->recstartSec+$aline->recendSec;
				$length=round(($aline->waitSec+$aline->callSec+$talktime+$aline->dispoSec)/1000,2);

				if(!isset($userarr[$aline->user_id])&&$aline->user_id>0)$userarr[$aline->user_id]=User::find($aline->user_id);
				$dispname="";if(isset($userarr[$aline->user_id]))$dispname=$userarr[$aline->user_id]->dispname();
				$username="";if(isset($userarr[$aline->user_id]))$username=$userarr[$aline->user_id]->username;
				


				$server = $server_id;
				$record_id = $record_id;
				$crmcall_id = $aline->id;
				$globalid = $globalid;
				$created_at = $aline->created_at;
				$start = $fulldate;
				$length = $length;
				$user = $username;
				$name = $dispname;
				$dispo = $aline->userstatus;
				$subdispo = $aline->usersubstatus;
				$callback = $aline->usercallback;
				$number = $aline->number;
				$clientcode = $clientcode;
				$currentstatus = $currentstatus;
				$legalstatus = $legalstatus;
				$client = $aline->client;
				$department = $aline->department;
				$state = $aline->state;
				$hsource = $aline->hsource;
				$type = $aline->type;
				$status = $aline->status;
				$statuscode = $aline->statuscode;
				$statusstr = $aline->substatus;
				$dialline = $aline->dialline_id;
				$did = $aline->did;
				$waitsec = round($aline->waitSec/1000,2);
				$callsec = round($aline->callSec/1000,2);
				$talksec = round($talktime/1000,2);
				$disposec = round($aline->dispoSec/1000,2);
				$remarks = str_replace("'","",$aline->userremarks);
				$userdata = $aline->userdata;
				$attempt = $aline->attempt;
				$priority = $aline->priority;
				$question = $aline->question;

				$setstr=implode(",",$setstrarr);
				$shortinsert.="('$server' ,'$record_id','$crmcall_id','$globalid','$created_at','$start','$length','$user','$name','$dispo','$subdispo','$callback','$number','$clientcode','$currentstatus','$legalstatus','$client','$department','$state','$hsource','$type','$status','$statuscode','$statusstr','$dialline','$did','$waitsec','$callsec','$talksec','$disposec','$remarks','$userdata','$attempt','$priority','$question'),"
				
				//DB::connection("conn")->insert(DB::raw("insert into ".$calllog_report." set $setstr"));
			}
			$shortinsert=substr($shortinsert,0,-1);
			DB::connection("conn")->insert(DB::raw("INSERT INTO ".$calllog_report." (`client`, `server_ip`, `record_id`, `cust_id`, `records_status`, `group_id`, `location`, `mobile`, `question`,`firstname`, `priority`, `modified`) VALUES $shortinsert"));

	}
}