<?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 App\Models\Notification;
use App\Jobs\KHRMSLib;

use Input;

use Illuminate\Database\Schema\Blueprint;

class bulkServerUpload_5 extends Command {

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

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

  /**
   * Execute the console command.
   *
   * @return mixed
   */
  public function handle()
  {

    //echo "\n".date('Y-m-d')."\n"; 

    echo "6";
    echo "\n".date('Y-m-d H:i:s')."\n";
    $server_ip=env('app_ip');
    $central_ip=env('central_ip');


    $wakka = new KHRMSLib();

    $kformlib=new \App\Jobs\KFormLib($wakka->HRCoreVars["HRFiledsStr"]);
    $kformlib->gthis=$wakka;

    $themehome=$wakka->GetThemePath('/');
    $updatetime=time();

    $clientlst=$wakka->GetBBBUserData("clientslist");

    $isadmin=$wakka->IsAdmin();
    $username=$wakka->GetUserName();
    $triggers=Input::get("triggers");
    $tmpstr=explode(",",$kformlib->HRFiledsStr);

    $success="";$message="";$successcnt=0;$duplicatecount=0;


    $conn = array(
      'driver'    => 'mysql',
      'host'      => $central_ip,
      'database'  => env('DB_DATABASE', 'kstych_flexydial'),
      'username'  => env('DB_USERNAME', 'root'),
      'password'  => env('DB_PASSWORD', 'yb9738z'),
      '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 location from server_details where server_ip='$server_ip'"));
    $location=$serverclist[0]->location;


    $excelarray = DB::connection("conn")->select(DB::raw("select * from bz_record_upload_uat where SERVER_IP='$server_ip' order by auto_id asc limit 100001,120000"));

    $conn='';

    $tcol=0;$fieldsarr=array();$extrahdrarr=array();

    foreach($excelarray as $key => $array){
      $excelarray[$key] = (array)$array;
    }

    $highestColumn = DB::connection("conn")->select(DB::raw("select count(*) as cnt from information_schema.columns where table_name='bz_record_upload_uat'"));
    $highestColumn = $highestColumn[0]->cnt;

    $highestrow = count($excelarray);

    $flag = 0;
    $editflag=0;

    for($i=0;$i<$highestrow;$i++)
    {
      if($excelarray[$i]["id"]!="")
      {
        if($excelarray[$i]["id"]=="CREATE")
        {
          $excelarray[$i]["id"]=$wakka->Query("insert into","","records",array('created'=>date('Y-m-d H:i:s')));
        }
        else $excelarray[$i]["id"]=intval($excelarray[$i]["id"]);
        if($wakka->getCount("records","id='".$excelarray[$i]["id"]."'")==1)
        {
          $empdata=$wakka->getPerson($excelarray[$i]["id"]);
          $ppldata=$empdata["peopledata"];
          $createdlog=$empdata['modifylog'];
          $fdirty=$empdata['dirty'];

          $createdlog[$updatetime]=$username."::";
          $createdlog["updated"]=$updatetime;

          $newdata=$ppldata;
          foreach($excelarray[$i] as $key => $value)
          {
            if($value!="")
            {
              if("A".$ppldata[$key]!="A".$value)//forcing string comparrision //MAGIC
              {
                $value=str_replace("'"," ",$value);
                if(strstr($createdlog[$updatetime],$key)==FALSE)$createdlog[$updatetime].="$key|".str_replace(array("|",",")," ",$ppldata[$key])."|".str_replace(array("|",",")," ",$value).",";

                $fdirty[$key]=1;

                $newdata[$key]=$value;
              }
            }
          }

          /*Start - Changes need to be done*/

          if($excelarray[$i]["status"]==null)
          {
            $empdata['status'] = "rom";
          }

          if($excelarray[$i]["dialer_status"]==null)
          {
            $empdata['dialer_status'] = "rom";
          }

          if($excelarray[$i]["dialer_substatus"]==null)
          {
            $empdata['dialer_substatus'] = "rom";
          }

          /*End - Changes need to be done*/

          $empdata["peopledata"]=$newdata;
          $empdata['modifylog']=$createdlog;
          $empdata['dirty']=$fdirty;

          $wakka->setPerson($excelarray[$i]["id"],$empdata);
          $excelarray[$i]['modified']=date('Y-m-d H:i:s');
          $successArr[] = $excelarray[$i];

        }
        else
          {
            $reason = "";

              $reason .= "Record ID is not on local server,";

            $excelarray[$i]['server_ip'] =$server_ip;
            $excelarray[$i]['location'] =$location;

            if($excelarray[$i]["clientcode"]!="")
              $excelarray[$i]['cust_id'] =$excelarray[$i]["clientcode"];
	       $excelarray[$i]['record_id'] = $excelarray[$i]["id"];

            $excelarray[$i]['Reason'] = $reason;

            $failureArr[] = $excelarray[$i];

          }
      }
      else
      {

        $reason = "";

        if($excelarray[$i]["id"]=="")
          $reason .= "Column ID is blank,";

        $excelarray[$i]['server_ip'] =$server_ip;
        $excelarray[$i]['location'] =$location;

        if($excelarray[$i]["clientcode"]!="")
          $excelarray[$i]['cust_id'] =$excelarray[$i]["clientcode"];

        $excelarray[$i]['Reason'] = $reason;

        $failureArr[] = $excelarray[$i];

      }

    }
    if(!empty($successArr)){
      foreach($successArr as $succes)
      {
        $setSuccess=array();

        $setSuccess[] = "server_ip='$server_ip'";
        $setSuccess[] = "location='$location'";
        $setSuccess[] = "record_id='".$succes['id']."'";
        $setSuccess[] = "cust_id='".$succes['clientcode']."'";
        $setSuccess[] = "modified='".$succes['modified']."'";

        $setSuccess = implode(",",$setSuccess);

        DB::connection("conn")->insert(DB::raw("insert into bz_record_upload_uat_success set $setSuccess"));
      }
    }
    if(!empty($failureArr)){
      foreach($failureArr as $failur)
      {
        $setFailure=array();

        $setFailure[] = "server_ip='$server_ip'";
        $setFailure[] = "location='$location'";
        $setFailure[] = "cust_id='".$failur['clientcode']."'";
	$setFailure[] = "record_id='".$failur['record_id']."'";
        $setFailure[] = "reason='".$failur['Reason']."'";

        $setFailure = implode(",",$setFailure);

        DB::connection("conn")->insert(DB::raw("insert into bz_record_upload_uat_failure set $setFailure"));
      }
    }
    
    DB::connection("conn")->disconnect();  
  }
}