<?php namespace App\Console\Commands;

use Illuminate\Console\Command;
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 relationship_tag extends Command {

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

/**
* The console command description.
*
* @var string
*/
protected $description = 'relationship_tag';

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

	$tcol=0;$fieldsarr=array();$extrahdrarr=array();
	$server_ip=env('app_ip');
	$central_ip=env('central_ip');	

	$conn = array(
		'driver'    => 'mysql',
		'host'      => $central_ip,
		'database'  => env('CENTRAL_DB'),
		'username'  => env('CENTRAL_USERNAME'),
		'password'  => env('CENTRAL_PASSWORD'),
		'charset'   => 'utf8',
		'collation' => 'utf8_unicode_ci',
		'prefix'    => '',
		'options'   => array(
			PDO::ATTR_TIMEOUT => 5,
			),
		);
	Config::set("database.connections.conn", $conn);


	if(DB::connection("conn")->getDatabaseName())
	{

		$qlist=DB::connection("conn")->select(DB::raw("SELECT * from relationship_tagging"));

		foreach($qlist as $qline)
		{

					
			$idqlist=DB::select(DB::raw("SELECT title,id from relationship_service where title='$qline->service' and id=$qline->id"));

			if(!empty($idqlist)){
				$servid=$idqlist[0]->id;
			}else{
				$servid='';
			}
			$setstrarr=array();

			$setstrarr[]="title='$qline->service'";
			$setstrarr[]="status='$qline->status'";

			$setstr=implode(",",$setstrarr);
			if($servid!='')
			{
				DB::update(DB::raw("update relationship_service set $setstr where id=$servid"));
				
			}else{
				DB::insert(DB::raw("insert into relationship_service set $setstr"));
				
			}
		}
		
		$service_id=DB::select(DB::raw("SELECT title,id from relationship_service"));


		foreach($qlist as $qline)
		{
			foreach ($service_id as $sid) {
				$idrlist=DB::select(DB::raw("SELECT id,title from relationship_category where title='$qline->category' and service_id='$sid->id'"));


				if(!empty($idrlist)){
					
					$cateid=$idrlist[0]->id;
				}else{
					$cateid='';
				}
				$setcatarr=array();

				$setcatarr[]="service_id='$sid->id'";
				$setcatarr[]="title='$qline->category'";
				$setcatarr[]="status='$qline->status'";

				$setcat=implode(",",$setstrarr);
				if($cateid!='')
				{
					DB::update(DB::raw("update relationship_category set $setcat where id=$cateid"));
				}else{
					DB::insert(DB::raw("insert into relationship_category set $setcat"));

				}
			}

		}
		
		$category_id=DB::select(DB::raw("SELECT title,id from relationship_category"));

		foreach($qlist as $qline)
		{
			foreach ($category_id as $cid) {
				$idslist=DB::select(DB::raw("SELECT id,title from relationship_sub_category where title='$qline->category' and category_id='$cid->id'"));

			if(!empty($idslist)){
				print_r($idslist);
				$subcateid=$idslist[0]->id;
			}else{
				$subcateid='';
			}
			$subcatarr[]="category_id='$cid->id'";
			$subcatarr[]="title='$qline->sub_category'";
			$subcatarr[]="status='$qline->status'";

			$setsubcat=implode(",",$subcatarr);
			if($$subcateid!='')
			{
				DB::update(DB::raw("update relationship_sub_category set $setsubcat where id=$subcateid"));
			}else{
				DB::insert(DB::raw("insert into relationship_sub_category set $setsubcat"));

			}

			}

		}
		echo "prashant3<br>";

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

	}

}		


}