<?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 App\Models\Customers;

use Input;

use Illuminate\Database\Schema\Blueprint;

class bulkServerUpload extends Command {

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

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

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

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

    echo "4";
    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('CENTRAL_DB', '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 0,20000"));

    $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;

    $unique_ids = DB::table("customers")->select('unique_id')->pluck('unique_id');

    $highestrow = count($excelarray);
echo "\n".$highestrow."\n";
    $flag = 0;
    $editflag=0;
    for($i=0;$i<$highestrow;$i++)
    {
      if($excelarray[$i]["id"]!="" || $excelarray[$i]["id"]!="0" || $excelarray[$i]["id"]!=0 )
      {
        if(in_array($excelarray[$i]["unique_id"], $unique_ids)){
          $unique_id=$unique_ids;
        }else{
          $unique_id="";
        }
        if($excelarray[$i]["id"]=="CREATE"|| !isset($unique_id))
        {
          $customers = new Customers();
          $excelarray[$i]["id"]=$customers->createNewEntry();
        }
        else $excelarray[$i]["id"]=intval($excelarray[$i]["id"]);

        $customers = new Customers();
        if($customers->checkRecordIfExist($excelarray[$i]["id"])!="")
        {
          $customers = new Customers();
          $customers = $customers->where('id', '=', $excelarray[$i]["id"])->select('peopledata')->get();
          $empdata=$customers->toArray();

          $ppldata=unserialize($empdata[0]["peopledata"]);

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

          $newdata=$ppldata;
          /*if(strstr($clientlst,$ppldata['client']))
	  {*/
            $editflag=0;
            foreach($excelarray[$i] as $key => $value)
            {
              /*if(isset($kformlib->HRFieldNames[$key]))
              {*/
                if($value!="")
                {
                if("A".$ppldata[$key]!="A".$value && $value!=null)//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;$editflag=1;
                  
                  
                  $newdata[$key]=$value;
                }
                //else $message.="same $excelarray[$i][id]:$key:$value, ";
              }
              //else $message.="empty $excelarray[$i][id]:$key, ";
            /*}
            else
            {
//              $message.="Invalid key :$key, ";
            }*/
          }
          
          if($editflag)
          {
            $empdata["peopledata"]=$newdata;

            $customers = new Customers();
            $customers->updateExistingRecord($excelarray[$i]["id"], $empdata);

            $successArr[] = $excelarray[$i]["id"];
          }
        //}
        else
        {
//          $message.="No access to $excelarray[$i][id], ";
        }
      }
      else
      {
//        $message.="ID not found $excelarray[$i][id], ";
      }
    }
    else
    {
      //$message.="Incorrect ID '$excelarray[$i][id]', ";
      $failureArr[] = $excelarray[$i]["id"];
    }
  }
    
    /*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();  
  }
}