<?php
ini_set('max_execution_time', 0);
date_default_timezone_set("America/New_York");
include '../../../shell/gvs_cf.php';

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;


$reports_conn= mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_REPORTS) or die(mysqli_error($reports_conn));
$econthen_conn=mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE,DATABASE_ECONTHEN_PORTAL) or die(mysqli_error($econthen_conn));
$price_mgt_conn=mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE,DATABASE_PRICE_MGMT) or die(mysqli_error($price_mgt_conn));
$notion_conn=mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE,DATABASE_FCSUSDBADMIN_NOTIONS_DROPSHIP) or die(mysqli_error($notion_conn));
$price_approval=mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE,DATABASE_FCSUSDBADMIN_PRICE_APPROVAL) or die(mysqli_error($price_approval));
$morris_conn = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE,DATABASE_GREATVAL_MORRIS_HW)  or die(mysqli_error($morris_conn));
$notify_conn = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_NOTIFY_DASHBOARD) or die(mysqli_error($notify_conn));


$date = date('Y-m-d H:i:s', time());
$curr_file_path = __FILE__;
$cur_day_time = date("Y-m-d H:i:s");
$script_name = '';
$get_details = mysqli_query($notify_conn, "select report_name, responsible, link_to_report_onedrive, supervisor from `script_details_dashboard` where cron_path='$curr_file_path'");
if (!$get_details) {
    $ins_alert = "Insert into alert_success_error_dashboard(script_name,link_to_onedrive, report_time, responsible, action, error_msg)values('Etsy price and inventory update','','$cur_day_time','','Issue','Issue with cron_scripts_applications.script_details_dashboard table')";
    $ins_query = mysqli_query($notify_conn, $ins_alert) or die(mysqli_error($notify_conn));
exit;
}
while ($get_row = mysqli_fetch_array($get_details))
{
    $script_name = trim($get_row['report_name']);
    $responsible = trim($get_row['responsible']);
    $main_link_to_onedrive = trim($get_row['link_to_report_onedrive']);
    $supervisor = trim($get_row['supervisor']);
}
if ($script_name == '') {
    $ins_alert = "Insert into alert_success_error_dashboard(script_name,link_to_onedrive, report_time, responsible, action, error_msg)values('Etsy price and inventory update','','$cur_day_time','','Issue','Script name not found')";
    $ins_query = mysqli_query($notify_conn, $ins_alert);
    echo 'Script name not found';
    exit;
}

$ins_alert = "Insert into alert_success_error_dashboard(script_name,link_to_onedrive, report_time, responsible, action,supervisor,error_msg)values('$script_name','$main_link_to_onedrive','$cur_day_time','$responsible','Issue','$supervisor','Unexpected error occured')";
$ins_query = mysqli_query($notify_conn, $ins_alert) or die(mysqli_error($notify_conn));
$alert_id = $notify_conn->insert_id;

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log, alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Etsy price and inventory update script has been started','$alert_id')");

$reports_conn->query("insert into gvs_scripts_execution_log(script_name,start_on) values('Etsy price and inventory update',NOW())");
$last_id = $reports_conn->insert_id;
if (!$last_id) {
    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Unable to connect table Reports.gvs_scripts_execution_log','$alert_id')");
}

if ((($handle = fopen("../python_scripts/delete_file_onedrive_thirty.py", "r")) == false) || (($handle = fopen("../python_scripts/upload_file_onedrive.py", "r")) == false))
    {
      $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Unable to find python onedrive folder','$alert_id')");
      $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to found python onedrive folder', action = 'Issue' WHERE id = '$alert_id'";
      $err_query = mysqli_query($notify_conn, $q1);
      exit;
   }


    if ((($handle = fopen("../php_plugins/phpmailer/vendor/autoload.php", "r")) == false) ||
        (($handle = fopen("../php_plugins/phpmailer/vendor/phpmailer/phpmailer/src/Exception.php", "r")) == false) ||
        (($handle = fopen("../php_plugins/phpmailer/vendor/phpmailer/phpmailer/src/PHPMailer.php", "r")) == false)||
        (($handle = fopen("../php_plugins/phpmailer/vendor/phpmailer/phpmailer/src/SMTP.php", "r")) == false))
        {
        $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Unable to find phpmailer folder','$alert_id')");
         $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to found phpmailer folder', action = 'Issue' WHERE id = '$alert_id'";
                    $err_query = mysqli_query($notify_conn, $q1);
            exit;

    }else{

    require '../php_plugins/phpmailer/vendor/autoload.php';

    require '../php_plugins/phpmailer/vendor/phpmailer/phpmailer/src/Exception.php';
    require '../php_plugins/phpmailer/vendor/phpmailer/phpmailer/src/PHPMailer.php';
    require '../php_plugins/phpmailer/vendor/phpmailer/phpmailer/src/SMTP.php';
    }


    $to_emails = array();
    $cc_emails = array();
    $sql = "select *from  fcsus_scripts_details WHERE id = '95'";
    $result = mysqli_query($reports_conn, $sql);
    		while ($row = mysqli_fetch_array($result))
    		{
    		    $toemails = explode(",", $row['to_emails']);
    		    $ccemails = explode(",", $row['cc_emails']);
    		    $reply_to = $row['reply_to'];
    		}
    									foreach ($toemails as $value)
    									{
    									    $email = explode('@', $value);
    									    $to_emails[substr_replace($email[0], "", -1) ] = $value;
    									}
    									foreach ($ccemails as $value)
    									{
    									    $email = explode('@', $value);
    									    $cc_emails[substr_replace($email[0], "", -1) ] = $value;
    									}

//get price_rule from db
$sql="select *from price_rule_stores where store='etsy' and sku_type='b2b'";
$result=mysqli_query($price_mgt_conn,$sql) or die(mysqli_error($price_mgt_conn));
while($row=mysqli_fetch_array($result))
  {
    $rule_per_b2b= $row['rule_percentage'];
    $add_sub_b2b= $row['add_subtract'];
    $store1 =$row['amazon_store1'];
    $prefix1 = strtolower($row['sku_prefix1']);
    $prefix1_b2b= array();
    //converting string to array
    if($prefix1 !=''){
       $prefix1_b2b= explode(',', $prefix1);
    }
    $store2= $row['amazon_store2'];
    $prefix2= strtolower($row['sku_prefix2']);
    $prefix2_b2b= array();
    if($prefix2 !=''){
      //converting string to array
        $prefix2_b2b = explode(',', $prefix2);
          }
  }

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','get price rules from price_management.price_rule_stores for etsy store and sku_type=b2b','$alert_id')");

  $sql="select *from price_rule_stores where store='etsy' and sku_type='b2c'";
  $result=mysqli_query($price_mgt_conn,$sql) or die(mysqli_error($price_mgt_conn));
  while($row=mysqli_fetch_array($result))
    {
      $rule_per_b2c= $row['rule_percentage'];
      $add_sub_b2c= $row['add_subtract'];
      $store3=$row['amazon_store1'];
      $prefix3= strtolower($row['sku_prefix1']);
      $prefix3_b2c = array();
        if($prefix3 !=''){
      $prefix3_b2c=explode(',', $prefix3);
    }
      $store4= $row['amazon_store2'];
      $prefix4= strtolower($row['sku_prefix2']);
      $prefix4_b2c= array();
      if($prefix4 != ''){
          $prefix4_b2c=explode(',', $prefix4);
        }
    }

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','get price rules from price_management.price_rule_stores for etsy store and sku_type=b2c','$alert_id')");

    //used just for checking if b2b or b2c skus
      /*  $sql="select sku from leemarpet_fbm_new";
        $result=mysqli_query($notion_conn,$sql) or die(mysqli_error($notion_conn));
        while($row=mysqli_fetch_array($result))
          {
                $lsku = strtolower($row['sku']);
                $current_price_lm[$lsku]="";
        }*/
    //used just for checking if b2b or b2c skus
          $sql="select sku from notions_sd300_fbm_new";
          $result=mysqli_query($notion_conn,$sql) or die(mysqli_error($notion_conn));
          while($row=mysqli_fetch_array($result))
            {   $lsku = strtolower($row['sku']);
                $current_price_nm[$lsku]="";
            }
            //used just for checking if b2b or b2c skus
            $sql="select sku from Essential_fbm";
            $result=mysqli_query($notion_conn,$sql) or die(mysqli_error($notion_conn));
            while($row=mysqli_fetch_array($result))
              {   $lsku = strtolower($row['sku']);
                  $current_price_es[$lsku]="";
              }
    //used just for checking if b2b or b2c skus
              $sql="select sku from buy_season_bonanza_new";
              $result=mysqli_query($notion_conn,$sql) or die(mysqli_error($notion_conn));
              while($row=mysqli_fetch_array($result))
                {   $lsku = strtolower($row['sku']);
                    $current_price_wi[$lsku]="";
                }
                //used just for checking if b2b or b2c skus
                $sql="select sku from KW01_bonanza";
                $result=mysqli_query($notion_conn,$sql) or die(mysqli_error($notion_conn));
                while($row=mysqli_fetch_array($result))
                  {   $lsku = strtolower($row['sku']);
                      $current_price_kw[$lsku]="";
                  }
                  //used just for checking if b2b or b2c skus
                  $sql="select Our_productID from morris_bonanza";
                  $result=mysqli_query($morris_conn,$sql) or die(mysqli_error($morris_conn));
                  while($row=mysqli_fetch_array($result))
                    {   $lsku = strtolower($row['Our_productID']);
                        $current_price_mr[$lsku]="";
                    }
    //used just for checking if b2b or b2c skus
            $sql="select sku from all_price_approval_new";
            $result=mysqli_query($price_approval,$sql) or die(mysqli_error($price_approval));
            while($row=mysqli_fetch_array($result))
              {  $lsku = strtolower($row['sku']);
                 $current_price_fc[$lsku]="";
              }
    //used just for checking if b2b or b2c skus
              $sql="select sku from all_price_approval_NM01_new";
              $result=mysqli_query($price_approval,$sql) or die(mysqli_error($price_approval));
              while($row=mysqli_fetch_array($result))
                {  $lsku = strtolower($row['sku']);
                   $current_price_b2b[$lsku]="";
                }
                //used just for checking if b2b or b2c skus
                $sql="select sku from all_price_PET_b2b";
                $result=mysqli_query($price_approval,$sql) or die(mysqli_error($price_approval));
                while($row=mysqli_fetch_array($result))
                  {
                      $lsku = strtolower($row['sku']);
                      $current_price_pet[$lsku]="";
                }
    //used just for checking if b2b or b2c skus
                $sql="select sku from product_master_catalog";
                $result=mysqli_query($price_approval,$sql) or die(mysqli_error($price_approval));
                while($row=mysqli_fetch_array($result))
                  {
                      $lsku = strtolower($row['sku']);
                      $current_price_pmc[$lsku]="";
                }
    //used just for checking if b2b or b2c skus
                $sql="select sku from product_master_catalog_NM01";
                $result=mysqli_query($price_approval,$sql) or die(mysqli_error($price_approval));
                while($row=mysqli_fetch_array($result))
                  {
                      $lsku = strtolower($row['sku']);
                      $current_price_pmc_nm[$lsku]="";
                }

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','make an sku list from tables (fcsusdbadmin_notions_dropship.leemarpet_fbm_new,fcsusdbadmin_notions_dropship.notions_sd300_fbm_new,fcsusdbadmin_notions_dropship.Essential_fbm,buy_season_bonanza_new.buy_season_bonanza_new,buy_season_bonanza_new.KW01_bonanza,greatval_morris_on_HW.morris_bonanza,
price_approval.all_price_approval_new,price_approval.all_price_approval_NM01_new,price_approval.all_price_PET_b2b,price_approval.product_master_catalog,price_approval.prouct_master_catalog_NM01) FOR checking if sku is b2b or b2c.','$alert_id')");

$cp_ec1=array();
$cp_ec2=array();
$cp_hw1=array();
$cp_hw2=array();

function get_cp1($prefix,$sql,$reports_conn,$sub,$rule){
    global $cp_ec1;

      $size_prefix1 = count($prefix);
      if($size_prefix1 !=0){
      $last_element1= end($prefix);
      foreach($prefix as $prefix1a){
        $prefix1= strtolower(substr($prefix1a,0,4));
        $last1= strtolower(substr($prefix1a,-3));
         $len1= strlen($prefix1a);

      $sql.=" seller_sku LIKE '$prefix1%' ";
      if($len1> 4 && $last1=='b2b'){
      $sql.=" and seller_sku LIKE '%$last1' ";
      }else {
        $sql.=" and seller_sku NOT LIKE '%b2b' ";
      }
      if($prefix1a != $last_element1){
        $sql.= " OR ";
      }
    }//foreach
      
	  
      $result=mysqli_query($reports_conn,$sql) or die(mysqli_error($reports_conn));
      while($row=mysqli_fetch_array($result))
        {
          $lsku = strtolower($row['seller_sku']);
          $price = $row['price'];
          if($sub ==0){
            if($rule!='0.00' || $rule!='0')
            $cp_ec1[$lsku]=round($price-(($rule/100)*$price),2);
            else
            $cp_ec1[$lsku]=$price;
             }
           else {
            if($rule!='0.00' || $rule!='0')
            $cp_ec1[$lsku]= round($price+(($rule/100)*$price),2);
            else
            $cp_ec1[$lsku]=$price;
            }
        }//while


      }//if

    }//func

    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','create function get_cp1 which fetches prices for sku by applying the rules.','$alert_id')");

    function get_cp2($prefix, $sql,$reports_conn,$sub,$rule){
      global $cp_ec2;

      $size_prefix1 = count($prefix);
      if($size_prefix1 !=0){
      $last_element1= end($prefix);
      foreach($prefix as $prefix1a){
        $prefix1= strtolower(substr($prefix1a,0,4));
        $last1= strtolower(substr($prefix1a,-3));
         $len1= strlen($prefix1a);

      $sql.=" seller_sku LIKE '$prefix1%' ";
      if($len1> 4 && $last1=='b2b'){
      $sql.=" and seller_sku LIKE '%$last1' ";
    }else {
      $sql.=" and seller_sku NOT LIKE '%b2b' ";
    }
      if($prefix1a != $last_element1){
        $sql.= " OR ";
      }
    }//foreach


      $result=mysqli_query($reports_conn,$sql) or die(mysqli_error($reports_conn));
      while($row=mysqli_fetch_array($result))
        {
          $lsku = strtolower($row['seller_sku']);
          $price = $row['price'];
          if($sub ==0){
              if($rule!='0.00' || $rule!='0')
            $cp_ec2[$lsku]=round($price-(($rule/100)*$price),2);
            else
             $cp_ec2[$lsku]=$price;
             }
           else {
               if($rule!='0.00' || $rule!='0')
            $cp_ec2[$lsku]= round($price+(($rule/100)*$price),2);
            else
             $cp_ec2[$lsku]=$price;
            }
        }//while
      }//if

    }//func


    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','create function get_cp2 which fetches prices for sku by applying the rules.','$alert_id')");

    function get_hw1($prefix, $sql,$reports_conn,$sub,$rule){
      global $cp_hw1;
      $size_prefix1 = count($prefix);
      if($size_prefix1 !=0){
      $last_element1= end($prefix);
      foreach($prefix as $prefix1a){
        $prefix1= strtolower(substr($prefix1a,0,4));
        $last1= strtolower(substr($prefix1a,-3));
         $len1= strlen($prefix1a);

      $sql.=" seller_sku LIKE '$prefix1%' ";
      if($len1> 4 && $last1=='b2b'){
      $sql.=" and seller_sku LIKE '%$last1' ";
      }else {
        $sql.=" and seller_sku NOT LIKE '%b2b' ";
      }
      if($prefix1a != $last_element1){
        $sql.= " OR ";
      }
    }//foreach

      $result=mysqli_query($reports_conn,$sql) or die(mysqli_error($reports_conn));
      while($row=mysqli_fetch_array($result))
        {
          $lsku = strtolower($row['seller_sku']);
          $price = $row['price'];
          if($sub ==0){
             if($rule!='0.00' || $rule!='0')
            $cp_hw1[$lsku]=round($price-(($rule/100)*$price),2);
            else
           $cp_hw1[$lsku]= $price;
             }
           else {
              if($rule!='0.00' || $rule!='0')
            $cp_hw1[$lsku]= round($price+(($rule/100)*$price),2);
            else
            $cp_hw1[$lsku]=$price;
            }
        }//while
      }//if

    }//func


    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','create function get_hw1 which fetches prices for sku by applying the rules.','$alert_id')");

    function get_hw2($prefix, $sql,$reports_conn,$sub,$rule){
      global $cp_hw2;
      $size_prefix1 = count($prefix);
      if($size_prefix1 !=0){
      $last_element1= end($prefix);
      foreach($prefix as $prefix1a){
        $prefix1= strtolower(substr($prefix1a,0,4));
        $last1= strtolower(substr($prefix1a,-3));
         $len1= strlen($prefix1a);

      $sql.=" seller_sku LIKE '$prefix1%' ";
      if($len1> 4 && $last1=='b2b'){
      $sql.=" and seller_sku LIKE '%$last1' ";
    }else {
      $sql.=" and seller_sku NOT LIKE '%b2b' ";
    }
      if($prefix1a != $last_element1){
        $sql.= " OR ";
      }
    }//foreach


      $result=mysqli_query($reports_conn,$sql) or die(mysqli_error($reports_conn));
      while($row=mysqli_fetch_array($result))
        {
          $lsku = strtolower($row['seller_sku']);
          $price = $row['price'];
          if($sub ==0){
              if($rule!='0.00' || $rule!='0')
            $cp_hw2[$lsku]=round($price-(($rule/100)*$price),2);
            else
            $cp_hw2[$lsku]=$price;
             }
           else {
               if($rule!='0.00' || $rule!='0')
            $cp_hw2[$lsku]= round($price+(($rule/100)*$price),2);
            else
            $cp_hw2[$lsku]=$price;
            }
        }//while
      }//if

    }//func

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','create function get_hw2 which fetches prices for sku by applying the rules.','$alert_id')");

    $sql="select seller_sku,price from ec_all_listing where";
    if($store1=='ec'){
      get_cp1($prefix1_b2b,$sql,$reports_conn,$add_sub_b2b,$rule_per_b2b);
      $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Since store1 is ec, then call function get_cp1','$alert_id')");
    }


    if($store2=='ec'){
      get_cp1($prefix2_b2b,$sql,$reports_conn,$add_sub_b2b,$rule_per_b2b);
      $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Since store2 is ec, then call function get_cp1','$alert_id')");
    }


    if($store3=='ec'){
      get_cp2($prefix3_b2c,$sql,$reports_conn,$add_sub_b2c,$rule_per_b2c);
      $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Since store3 is ec, then call function get_cp2','$alert_id')");
    }


    if($store4=='ec'){
      get_cp2($prefix4_b2c,$sql,$reports_conn,$add_sub_b2c,$rule_per_b2c);
      $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Since store4 is ec, then call function get_cp2','$alert_id')");
    }


    $sql2="select seller_sku,price from hw_all_listing where";
    if($store1=='hw'){
    get_hw1($prefix1_b2b,$sql2,$reports_conn,$add_sub_b2b,$rule_per_b2b);
    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Since store1 is HW, then call function get_hw1','$alert_id')");
    }


    if($store2=='hw'){
    get_hw1($prefix2_b2b,$sql2,$reports_conn,$add_sub_b2b,$rule_per_b2b);
    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Since store2 is HW, then call function get_hw1','$alert_id')");
    }

    if($store3=='hw'){
    get_hw2($prefix3_b2c,$sql2,$reports_conn,$add_sub_b2c,$rule_per_b2c);
    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Since store3 is HW, then call function get_hw2','$alert_id')");
    }
    if($store4=='hw'){
    get_hw2($prefix4_b2c,$sql2,$reports_conn,$add_sub_b2c,$rule_per_b2c);
    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Since store4 is HW, then call function get_hw2','$alert_id')");
    }

function smail($arr,$listingid,$err){

    $mail2 = new PHPMailer(true);
    try
      {

       $mail2->isSMTP();                                      // Set mailer to use SMTP
$mail2->SMTPAuth = true;                               // Enable SMTP authentication
$mail2->Host =EMAIL_HOST;  // Specify main and backup SMTP servers
$mail2->Username = EMAIL_USERNAME;                 // SMTP username
$mail2->Password =EMAIL_PASSWORD;
$mail2->SMTPSecure = 'tls';                            // Enable TLS encryption, `ssl` also accepted
$mail2->Port = EMAIL_PORT;                                    // TCP port to connect to

$email_set_from = EMAIL_SET_FROM;
$mail2->setFrom($email_set_from, 'Mailer');

//$mail2->addAddress('namritab@fcsus.com', 'Namrita');
$mail2->addAddress('sachink@fcsus.com', 'Sachin');
//$mail2->addAddress('deepakg@fcsus.com', 'Deepak');

$mail2->isHTML(true);                                  // Set email format to HTML
$mail2->Subject = "Etsy listing id $listingid error";
$mail2->Body = "Hi,<br> PFA.<br> Listing id $listingid error:$err.<br>".print_r( $arr, true );
$mail2->AltBody = 'Checking if this email comes or not';

$mail2->send();
$notify_conn = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_NOTIFY_DASHBOARD) or die(mysqli_error($notify_conn));
echo 'Message has been sent';
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Mail has been sent to the respective users as error has occured in the script','$alert_id')");
      }
catch (Exception $e) {
          echo 'Message could not be sent.'.$e;
          echo 'Mailer Error: ' . $mail->ErrorInfo;
          $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Unable to send mail $mail->ErrorInfo','$alert_id')");
         }
    }


//get refresh_token
function get_token(){

$curl = curl_init();
curl_setopt_array($curl, [
  CURLOPT_URL => "https://api.etsy.com/v3/public/oauth/token",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_SSL_VERIFYPEER=>false,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "POST",
  CURLOPT_POSTFIELDS => "grant_type=refresh_token&client_id=".X_API_KEY."&refresh_token=".ETSY_REFRESH_TOKEN,
  CURLOPT_HTTPHEADER => [
    "content-type: application/x-www-form-urlencoded",
    "x-api-key:".X_API_KEY,
  ],
]);

$response = curl_exec($curl);

$err = curl_error($curl);

curl_close($curl);

  $refresh_token = json_decode($response,TRUE);
  print_r($refresh_token);
  $tokens=$refresh_token['access_token'];

  return $tokens;
  }
$token=  get_token();
echo $token;
if($token!=''){
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Etsy token is generated','$alert_id')");
}else {
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Etsy token couldnt be generated','$alert_id')");
exit();
}
$time1 = strtotime(date("h:i:s"));

//listing id

$curl = curl_init();

curl_setopt_array($curl, array(
    CURLOPT_URL => 'https://openapi.etsy.com/v3/application/shops/15561632/listings/active?limit=100&offset=0',
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_SSL_VERIFYPEER=>false,
    CURLOPT_ENCODING => '',
    CURLOPT_MAXREDIRS => 10,
    CURLOPT_TIMEOUT => 0,
    CURLOPT_FOLLOWLOCATION => true,
    CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
    CURLOPT_CUSTOMREQUEST => 'GET',
    CURLOPT_HTTPHEADER => array(
        'x-api-key:'.X_API_KEY
    ),
));

$response = curl_exec($curl);
curl_close($curl);

$listings = json_decode($response,TRUE);
for($i=0;$i<100;$i++){
  $listing_ids[$i]=$listings['results'][$i]['listing_id'];

}
 $count_listings=$listings['count'];
 if($count_listings==0){
   $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Listing id count is 0','$alert_id')");
   exit();
 }else {
   $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Listing ids  generated','$alert_id')");
   }
 $k=100;
 $per_count=100;
 $page=1;

$total_page=ceil($count_listings/$per_count);//exit;

while($total_page!=0 && $per_count<=$count_listings){

  $curl = curl_init();
  curl_setopt_array($curl, array(
      CURLOPT_URL => "https://openapi.etsy.com/v3/application/shops/15561632/listings/active?limit=100&offset=".$per_count,
      CURLOPT_RETURNTRANSFER => true,
      CURLOPT_SSL_VERIFYPEER=>false,
      CURLOPT_ENCODING => '',
      CURLOPT_MAXREDIRS => 10,
      CURLOPT_TIMEOUT => 0,
      CURLOPT_FOLLOWLOCATION => true,
      CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
      CURLOPT_CUSTOMREQUEST => 'GET',
      CURLOPT_HTTPHEADER => array(
          'x-api-key:'. X_API_KEY
      ),
  ));

  $response = curl_exec($curl);
  curl_close($curl);
  $listings = json_decode($response,TRUE);
   $j=$per_count;

  for($i=0;$i<$k;$i++){
    $listing_ids[$j]=isset($listings['results'][$i]['listing_id'])?$listings['results'][$i]['listing_id']:'';
    $j++;
  }
  $per_count+=100;
    $total_page--;
}

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','using token and api to get listing id fron etsy','$alert_id')");

$file ="etsy_before_push_".date("Y_m_d_h_i_s") .".txt";
$f = fopen($file,"w");
$delimiter = "\t";
//set column headers
$fields = "Listing_id"."\t". "Sku" ."\t". "Inventory" ."\t". "Price".PHP_EOL;
fwrite($f, $fields);

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','created file named etsy_before_push with fields like, listing id, sku, inventory and price.','$alert_id')");

$file2 ="etsy_after_push_".date("Y_m_d_h_i_s") .".txt";
$f2 = fopen($file2,"w");
$delimiter = "\t";
//set column headers
$fields2 = "Listing_id"."\t". "Sku" ."\t". "Inventory" ."\t". "Price".PHP_EOL;
fwrite($f2, $fields2);
$b2c_sku= $b2b_sku=$blank_sku=array();

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','created file named etsy_before_push with fields like, listing id, sku, inventory and price.','$alert_id')");
$new_file_disp=fopen('etsy_today_local.csv','w');

$cin = 0;
foreach($listing_ids as $value){
  if($value!=""){
    $sku=$value;
    $listing_id[$cin]=$value;
    fputcsv($new_file_disp,array($sku));
    $cin++;

  }


}
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','store listing ids in file named etsy_today_local.csv','$alert_id')");

print "<br>Start Time:".date('Y-m-d H:i:s'); print "<br>"; // Comment this line before make this file live
$count_listings= count($listing_id);
echo "count:$count_listings<br>";
if($count_listings==0){
  $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Total Listing id count is 0','$alert_id')");
  exit();
}else {
  $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Total Listing ids generated are $count_listings','$alert_id')") or die(mysqli_error($notify_conn));
  }

$cnt = 0;

for($p=0;$p<$count_listings;$p++){


  $time2= strtotime(date("h:i:s"));//current time
  $difference= abs($time2-$time1);
  if($difference >='3000'){
  $token=  get_token();
  $time1=strtotime(date("h:i:s"));//reset time1
  echo "token reset"."<br>";
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Check time, for the script, if 1 hour is about to end, then we regenerate the new token','$alert_id')");
  }

 $sqlgetresponse_data = "SELECT response FROM etsy_listing_structure WHERE listingid = '".$listing_id[$p]."'";
 $resultres=mysqli_query($reports_conn,$sqlgetresponse_data) or die(mysqli_error($reports_conn));
  $rowresponse=mysqli_fetch_array($resultres);
  $response = stripslashes($rowresponse['response']);

  if($response == ''){continue;}

    $sku_list = json_decode($response,TRUE);

  $err="";
switch (json_last_error()) {
  case JSON_ERROR_NONE:

  $err="No errors";
  break;
  case JSON_ERROR_DEPTH:

  $err="Maximum stack depth exceeded";
  smail($sku_list,$listing_id[$p],$err);
  break;
  case JSON_ERROR_STATE_MISMATCH:

  $err="Invalid or malformed JSON";
  smail($sku_list,$listing_id[$p],$err);
  break;
  case JSON_ERROR_CTRL_CHAR:

  $err="Control character error";
  smail($sku_list,$listing_id[$p],$err);
  break;
  case JSON_ERROR_SYNTAX:

  $err="Syntax error";
  smail($sku_list,$listing_id[$p],$err);
  break;
  case JSON_ERROR_UTF8:

  $err="Malformed UTF-8 characters";
  smail($sku_list,$listing_id[$p],$err);
  break;
  default:

  $err="Unknown error";
  smail($sku_list,$listing_id[$p],$err);
  break;
}


$empty_array= array();
$products=isset($sku_list['products'])?$sku_list['products']:$empty_array;
$count_sku= is_array($products) ? count($products) : 0 ;


if($count_sku=='0'){
smail($sku_list,$listing_id[$p],$err);
break;
}

$flag_qty=0;$sum_qty=0;$skus_f=array();

#######################################################
/////////////Multiple SKU////////////////////////////
#######################################################
  if($count_sku >1){

   foreach($products as $key=>$vals){
      $price1 = 0;$price = 0;
      $qty1 = $vals['offerings'][0]['quantity']; //old qty
    $sku=$vals['sku'];
    $skus_f[]=$sku;
    $sql="select qt from 4p_inventory where sku='".$sku."'";
    $qty=0;
    $result=mysqli_query($econthen_conn,$sql);
    while($row=mysqli_fetch_array($result)){
      $qty=(int)$row['qt'];
    }
  if($qty<=0) { $qty=0; }
   if($qty >= 999) { $qty= 999; }
  $sum_qty +=$qty;
 $price1=($vals['offerings'][0]['price']['amount'])/100;

   $lower_sku=strtolower($sku);
   if(array_key_exists($lower_sku, $current_price_lm) || array_key_exists($lower_sku, $current_price_nm) ||
     array_key_exists($lower_sku, $current_price_es) ||  array_key_exists($lower_sku, $current_price_kw)  ||
     array_key_exists($lower_sku, $current_price_wi) || array_key_exists($lower_sku, $current_price_mr) )
    {
      $b2c_sku[] = $lower_sku;
      if($store3 =='ec' || $store4 =='ec'){
         if(array_key_exists($lower_sku,$cp_ec2))
           $price=$cp_ec2[$lower_sku];
       }
       if($store3 =='hw' || $store4 =='hw'){
          if(array_key_exists($lower_sku,$cp_hw2))
           $price=$cp_hw2[$lower_sku];
        }

    }

    elseif(array_key_exists($lower_sku, $current_price_fc)|| array_key_exists($lower_sku, $current_price_b2b)||
           array_key_exists($lower_sku, $current_price_pet) || array_key_exists($lower_sku, $current_price_pmc)||
           array_key_exists($lower_sku, $current_price_pmc_nm))
           {
            $b2b_sku[] = $lower_sku;
         if($store1 =='ec' || $store2 =='ec'){
            if(array_key_exists($lower_sku,$cp_ec1)){
              $price=$cp_ec1[$lower_sku];
            }


           }
           if($store1 =='hw' || $store2 =='hw'){
              if(array_key_exists($lower_sku,$cp_hw1))
             $price=$cp_hw1[$lower_sku];
           }
          }

     else {
  //
                $blank_sku[]= $lower_sku;
           }


    if($price==''||$price==0){$price = $price1; /*print "In case Price Null";*/} // never make etsy price null or 0

    if($qty!=$qty1 || $price!=$price1){$update = 1;} // If qty or price not same then update in Etsy


    $lineData = $listing_id[$p]."\t". $sku ."\t". $qty ."\t". $price.PHP_EOL;
    fwrite($f, $lineData);

    $property_id=isset($vals['property_values'][0]['property_id'])?$vals['property_values'][0]['property_id']:'';
    $property_name=isset($vals['property_values'][0]['property_name'])?$vals['property_values'][0]['property_name']:'';
    $scale_id=isset($vals['property_values'][0]['scale_id'])?$vals['property_values'][0]['scale_id']:null;
    $value_ids=isset($vals['property_values'][0]['value_ids'][0])?$vals['property_values'][0]['value_ids'][0]:'';
    $values=isset($vals['property_values'][0]['values'][0])?$vals['property_values'][0]['values'][0]:'';
    $is_enabled=isset($vals['offerings'][0]['is_enabled'])?$vals['offerings'][0]['is_enabled']:'';
    if($is_enabled==1){ $tr='true'; }else { $tr='false';}
    $new_array[]=array('sku'=>$sku,'property_values'=>array(array("property_id"=>$property_id,"value_ids"=>array($value_ids),"scale_id"=>$scale_id,
    "property_name"=>$property_name,"values"=>array($values))),
    "offerings"=>array(array('price'=>$price,'quantity'=>$qty,'is_enabled'=>$tr),));

    }

    $price_on_property=isset($sku_list['price_on_property'][0])?$sku_list['price_on_property'][0]:'';
    $quantity_on_property=isset($sku_list['quantity_on_property'][0])?$sku_list['quantity_on_property'][0]:'';
    $sku_on_property=isset($sku_list['sku_on_property'][0])?$sku_list['sku_on_property'][0]:'';

    $data= array('products'=>$new_array, 'price_on_property'=>array($price_on_property),'quantity_on_property'=>array($quantity_on_property),
    'sku_on_property'=>array($sku_on_property));
$display_json=json_encode($data);
  if($sum_qty==0){
    //if all qty is0, then set flag for deactivate
    $flag_qty=1;
  }
}

#######################################################
/////////////  Single SKU  ////////////////////////////
#######################################################
elseif($count_sku==1) {
  foreach($products as $key=>$vals){
      $price1 = 0;$price = 0;
      $qty1 = $vals['offerings'][0]['quantity']; //old qty
      $sku=$vals['sku'];
      $skus_f[]=$sku;
    $sql="select qt from 4p_inventory where sku='".$sku."'";
      $qty=0;
      $result=mysqli_query($econthen_conn,$sql);
      while($row=mysqli_fetch_array($result)){
    $qty=(int)$row['qt'];// new qty
      }
      if($qty<=0) { $flag_qty=1;}
      if($qty >= 999) { $qty= 999; }
       $price1=($vals['offerings'][0]['price']['amount'])/100; // old price esty

       //filtering as b2c or b2b sku
    $lower_sku=strtolower($sku);//print "<br>";
         if(array_key_exists($lower_sku, $current_price_lm) || array_key_exists($lower_sku, $current_price_nm) ||
           array_key_exists($lower_sku, $current_price_es) ||  array_key_exists($lower_sku, $current_price_kw)  ||
           array_key_exists($lower_sku, $current_price_wi) || array_key_exists($lower_sku, $current_price_mr) )
          {
            $b2c_sku[] = $lower_sku;
            if($store3 =='ec' || $store4 =='ec'){
               if(array_key_exists($lower_sku,$cp_ec2))
                 $price=$cp_ec2[$lower_sku];
             }
             if($store3 =='hw' || $store4 =='hw'){
                if(array_key_exists($lower_sku,$cp_hw2))
                 $price=$cp_hw2[$lower_sku];
              }

          }
          elseif(array_key_exists($lower_sku, $current_price_fc)|| array_key_exists($lower_sku, $current_price_b2b)||
                 array_key_exists($lower_sku, $current_price_pet) || array_key_exists($lower_sku, $current_price_pmc)||
                 array_key_exists($lower_sku, $current_price_pmc_nm))
                 {
                  $b2b_sku[] = $lower_sku;
               if($store1 =='ec' || $store2 =='ec'){
                   if(array_key_exists($lower_sku,$cp_ec1)){
                      $price=$cp_ec1[$lower_sku];
                   }

                 }
                 if($store1 =='hw' || $store2 =='hw'){
                    if(array_key_exists($lower_sku,$cp_hw1))
                   $price=$cp_hw1[$lower_sku];
                 }
                }

           else {
       //
                      $blank_sku[]= $lower_sku;
                 }


      $lineData = $listing_id[$p]."\t". $sku ."\t". $qty1 ."\t". $price1.PHP_EOL;
      fwrite($f, $lineData);
      $is_enabled=isset($vals['offerings'][0]['is_enabled'])?$vals['offerings'][0]['is_enabled']:'';
      if($is_enabled==1){ $tr='true'; }else { $tr='false';}

}

  if($price==''||$price==0){$price = $price1;} // never make etsy price null or 0
  if($qty!=$qty1 || $price!=$price1){$update = 1;} // If qty or price not same then update in Etsy

  $new_array[]=array('sku'=>$sku,'property_values'=>array(),"offerings"=>array(array('price'=>$price,'quantity'=>$qty,'is_enabled'=>$tr)),);

  $data= array('products'=>$new_array, 'price_on_property'=>array(),'quantity_on_property'=>array(),
  'sku_on_property'=>array());

$display_json=json_encode($data);

}


if($flag_qty==1){

//deactivate listing id
  $curl = curl_init();

  curl_setopt_array($curl, array(
      CURLOPT_URL => "https://openapi.etsy.com/v3/application/shops/15561632/listings/".$listing_id[$p],
      CURLOPT_RETURNTRANSFER => true,
      CURLOPT_SSL_VERIFYPEER=>false,
      CURLOPT_ENCODING => '',
      CURLOPT_MAXREDIRS => 10,
      CURLOPT_TIMEOUT => 0,
      CURLOPT_FOLLOWLOCATION => true,
      CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
      CURLOPT_CUSTOMREQUEST => 'PUT',
      CURLOPT_POSTFIELDS => 'state=inactive',

      CURLOPT_HTTPHEADER => array(
          'Content-Type: application/x-www-form-urlencoded',
          'x-api-key:'.X_API_KEY,
          'Authorization: Bearer  '.$token
      ),
  ));

  $response = curl_exec($curl);

  curl_close($curl);


foreach($skus_f as $f_s){
  $states='inactive';
  $lineData2 = $listing_id[$p]."\t". $f_s ."\t". $states ."\t". $states .PHP_EOL;
  fwrite($f2, $lineData2);
}

}else {

if($update){
   $cnt++; // Counter for checking how many products got updated.


  $curl = curl_init();
  curl_setopt_array($curl, array(

    CURLOPT_URL => "https://openapi.etsy.com/v3/application/listings/".$listing_id[$p]."/inventory",
      CURLOPT_RETURNTRANSFER => true,
      CURLOPT_SSL_VERIFYPEER=>false,
      CURLOPT_ENCODING => '',
      CURLOPT_MAXREDIRS => 10,
      CURLOPT_TIMEOUT => 0,
      CURLOPT_FOLLOWLOCATION => true,
      CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
      CURLOPT_CUSTOMREQUEST => 'PUT',
      CURLOPT_POSTFIELDS => $display_json,
      CURLOPT_HTTPHEADER => array(
        'Content-Type: application/json',
        'x-api-key:'.X_API_KEY,
        'Authorization: Bearer '.  $token
      ),
  ));

  $response = curl_exec($curl);
  curl_close($curl);

  $api_resp=json_decode($response,TRUE);

  $error="";
  $error=isset($api_resp['error'])?$api_resp['error']:'';

  $prod=isset($api_resp['products'])?$api_resp['products']:'';

  $count_prod= is_array($prod) ? count($prod) : 10 ;
  if($count_prod>0){
  foreach($prod as $pp){
    $sku2=isset($pp['sku'])?$pp['sku']:'';

    $qty2=isset($pp['offerings'][0]['quantity'])?$pp['offerings'][0]['quantity']:'';

    $price_old=isset($pp['offerings'][0]['price']['amount'])?$pp['offerings'][0]['price']['amount']:'';
    if($price_old!='')
    $price2=$price_old/100;
    else
    $price2='';
    $lineData2 = $listing_id[$p]."\t". $sku2 ."\t". $qty2 ."\t". $price2.PHP_EOL;
    fwrite($f2, $lineData2);
}
}else {

  $lineData2 = $listing_id[$p]."\t". $sku2 ."\t". $qty2 ."\t". $price2.PHP_EOL;
  fwrite($f2, $lineData2);
  smail($prod,$listing_id[$p],$error);
  break;
}

}
}
 unset($price1);unset($qty1);unset($price);unset($qty);unset($update);unset($new_array);


}//forloop

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log, alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','For each listing id,get response(column name) from Reports.etsy_listing_structure table','$alert_id')");
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log, alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','If result is product array, we check if its single sku or multisku listing id','$alert_id')");
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log, alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','For each listing id, we calculate price using price rule functions and  get its inventory from econthen_portal.4p_inventory table','$alert_id')");

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log, alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','if prev qty and new qty are different OR previous price and new price are different,then we update that particular listing id and update in after_push.txt file','$alert_id')");
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log, alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','if prev qty and new qty are different OR previous price and new price are different,then we update that particular listing id and update in after_push.txt file','$alert_id')");

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log, alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','if any product qty is fully 0,then we deactivate the Listing id.','$alert_id')");
print "<br>Cnt = ".$cnt; // Comment this line before make it live
print "<br><br>End Time:".date('Y-m-d H:i:s'); // Comment this line before make this file live


  $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log, alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Count of Listing id updated in etsy site:$cnt','$alert_id')");

$token=get_token();
//get expired products list

$url='https://openapi.etsy.com/v3/application/shops/15561632/listings?state=expired';
$curl = curl_init();

curl_setopt_array($curl, array(
    CURLOPT_URL => $url."&limit=100&offset=0",

    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_ENCODING => '',
    CURLOPT_MAXREDIRS => 10,
    CURLOPT_TIMEOUT => 0,
    CURLOPT_FOLLOWLOCATION => true,
    CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
    CURLOPT_CUSTOMREQUEST => 'GET',
    CURLOPT_HTTPHEADER => array(
        'x-api-key:'. X_API_KEY,
        'Authorization: Bearer '.  $token
    ),
));

$response = curl_exec($curl);
curl_close($curl);
$listings = json_decode($response,TRUE);

$count_api= $listings['count'];
for($i=0;$i<100;$i++){
  $lid=$listings['results'][$i]['listing_id'];

  $listing_id[$i]=$listings['results'][$i]['listing_id'];
  $state_timestamp=$listings['results'][$i]['ending_timestamp'];
  $date_time=date('d-m-Y', $state_timestamp);
$skuss=$listings['results'][$i]['skus'];
$cc_count=count($skuss);

$count_exp_sku[$i]=$cc_count;
for($pp=0;$pp<$cc_count;$pp++){
  $sku_list[$pp]= $skuss[$pp];
  $mix_both[]=array("listingid"=>$lid,"sku"=>$skuss[$pp],"date_time"=>$date_time);
}
$get_sku[$i]=$listings['results'][$i]['skus'][0];

$price_from_api=$listings['results'][$i]['price']['amount'];
$price_api[$i]=($price_from_api/100);
}

 $count_listings=$listings['count'];
 $k=100;
 $per_count=100;
 $page=1;

$total_page=ceil($count_listings/$per_count);
while($total_page!=0 && $per_count<=$count_listings){
  sleep(10);
  $curl = curl_init();
  curl_setopt_array($curl, array(
      CURLOPT_URL => $url."&limit=100&offset=".$per_count,
      CURLOPT_RETURNTRANSFER => true,
      CURLOPT_ENCODING => '',
      CURLOPT_MAXREDIRS => 10,
      CURLOPT_TIMEOUT => 0,
      CURLOPT_FOLLOWLOCATION => true,
      CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
      CURLOPT_CUSTOMREQUEST => 'GET',
      CURLOPT_HTTPHEADER => array(
          'x-api-key:'.X_API_KEY,
          'Authorization: Bearer '.  $token
      ),
  ));

  $response = curl_exec($curl);
  curl_close($curl);
  $listings = json_decode($response,TRUE);
   $j=$per_count;

  for($i=0;$i<$k;$i++){
    $lid=isset($listings['results'][$i]['listing_id'])?$listings['results'][$i]['listing_id']:'';
    $listing_id[$j]=isset($listings['results'][$i]['listing_id'])?$listings['results'][$i]['listing_id']:'';
    $skuss=isset($listings['results'][$i]['skus'])?$listings['results'][$i]['skus']:0;
    $state_timestamp=isset($listings['results'][$i]['ending_timestamp'])?$listings['results'][$i]['ending_timestamp']:'';
    if($state_timestamp!='')
    $date_time=date('d-m-Y', $state_timestamp);
    else
    $date_time="";

if($skuss!=0){
  $count_exp_sku[$j]=count($skuss);
  $cc_count=count($skuss);

  for($pp=0;$pp<$cc_count;$pp++){
    $sku_list[$pp]= $skuss[$pp];
    $mix_both[]=array("listingid"=>$lid,"sku"=>$skuss[$pp],"date_time"=>$date_time);
  }

$get_sku[$j]=isset($listings['results'][$i]['skus'][0])?$listings['results'][$i]['skus'][0]:'';
$price_from_api=isset($listings['results'][$i]['price']['amount'])?$listings['results'][$i]['price']['amount']:0;
$price_api[$j]=($price_from_api/100);
}else {
  $count_exp_sku[$j]=0;
  $get_sku[$j]='';
  $price_api[$j]=0;
  $ss_sku=isset($listings['results'][$i]['skus'][0])?$listings['results'][$i]['skus'][0]:'';
      $mix_both[]=array("listingid"=>$lid,"sku"=>$ss_sku,"date_time"=>$date_time);
  }
  $j++;
  }
  $per_count+=100;

    $total_page--;
}

$count_mix_both=count($mix_both);
echo "count:".$count_mix_both."<Br>";

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log, alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Get list of etsy expired products and update the same in fcsusdbadmin_notions_dropship.etsy_expired_products table','$alert_id')");
$delete_query="Delete from etsy_expired_products";
mysqli_query($notion_conn,$delete_query) or die("couldnt connect");

    $today=date("d-m-Y");
$query = 'INSERT INTO etsy_expired_products (`listing_id`, `sku`,`expired_date`,`today_date`) VALUES ';
    $query_parts = array();
foreach($mix_both as $key_m=>$val_m){
  $lid=$val_m['listingid'];
  $sku=$val_m['sku'];
  $date=$val_m['date_time'];


if($sku!='')
    $query_parts[] = "('" . $lid . "', '" . $sku . "', '" . $date . "','" . $today . "')";

}
 $query .= implode(',', $query_parts);

 $count_qp=count($query_parts);
 echo "count_Qp:".$count_qp."<Br>";
  if($count_qp!=0)
 $queries=mysqli_query($notion_conn,$query) or die("couldnt connect");

$sendfile_name="etsy_expired_products_".date("Y_m_d_h_i_s") .".txt";
$send_file=fopen($sendfile_name,'w');
$fields33 = "Listing_id"."\t". "Sku" ."\t". "Expired Date".PHP_EOL;
fwrite($send_file, $fields33);

$expired_list_today=array();

$todays=date('Y-m-d');
$prev_date=date('Y-m-d', strtotime(' -7 day'));
$old_today=date('d-m-Y');
$get_value="SELECT * FROM `etsy_expired_products`
 WHERE STR_TO_DATE(`expired_date`, '%d-%m-%Y')
 BETWEEN  '".$prev_date."'  AND  '".$todays. "' AND
 today_date='".$old_today."'  ORDER BY expired_date";


$results=mysqli_query($notion_conn,$get_value);
while($rows=mysqli_fetch_array($results)){
  $sku=$rows['sku'];
$lid=$rows['listing_id'];
$date=$rows['expired_date'];
$expired_list_today[]=$lid;
$lineData34 = $lid."\t". $sku ."\t". $date.PHP_EOL;
fwrite($send_file, $lineData34);

}
fclose($send_file);
$count_expired_today=count($expired_list_today);

$p = getcwd();
$foldername = $main_link_to_onedrive;

//https://back.greatvaluestores.com/python_scripts/upload_file_onedrive.py
$command1 = escapeshellcmd("python ../python_scripts/upload_file_onedrive.py $p $foldername $file");
$output1 = shell_exec($command1);
echo $output1;

if(trim($output1) == 'done'){
    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Uploaded etsy_before_push.txt file to onedrive folder.','$alert_id')");
}else{
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Unable to upload etsy_before_push.txt file to onedrive','$alert_id')");
$q2 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to upload EC file in onedrive', action = 'Issue' WHERE id = '$alert_id'";
$succ_query = mysqli_query($notify_conn, $q2);
}

$command2 = escapeshellcmd("python ../python_scripts/upload_file_onedrive.py $p $foldername $file2");
$output2 = shell_exec($command2);
echo $output2;

if(trim($output2) == 'done'){
    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Uploaded etsy_after_push.txt file to onedrive folder.','$alert_id')");
}else{
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Unable to upload etsy_after_push.txt file to onedrive','$alert_id')");
$q2 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to upload EC file in onedrive', action = 'Issue' WHERE id = '$alert_id'";
$succ_query = mysqli_query($notify_conn, $q2);
}

$command3 = escapeshellcmd("python ../python_scripts/upload_file_onedrive.py $p $foldername $sendfile_name");
$output3 = shell_exec($command3);
echo $output3;

if(trim($output3) == 'done'){
    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Uploaded $sendfile_name file to onedrive folder.','$alert_id')");
}else{
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Unable to upload $sendfile_name file to onedrive','$alert_id')");
$q2 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to upload EC file in onedrive', action = 'Issue' WHERE id = '$alert_id'";
$succ_query = mysqli_query($notify_conn, $q2);
}


 $mail = new PHPMailer(true);
try {

  $mail->isSMTP();                                      // Set mailer to use SMTP
$mail->SMTPAuth = true;                               // Enable SMTP authentication
$mail->Host = EMAIL_HOST; // Specify main and backup SMTP servers
$mail->Username = EMAIL_USERNAME; // SMTP username
$mail->Password = EMAIL_PASSWORD; // SMTP password
$mail->SMTPSecure = 'tls'; // Enable TLS encryption, `ssl` also accepted
$mail->Port = EMAIL_PORT; // TCP port to connect to
//Recipients
$email_set_from = EMAIL_SET_FROM;
$mail->setFrom($email_set_from, 'Mailer');

if ($reply_to != '')
    $mail->addReplyTo($reply_to, 'Information');

    foreach ($to_emails as $key => $email)
    {
        $mail->addAddress($email, $key);
    }

    foreach ($cc_emails as $key => $email)
    {
        $mail->AddCC($email, $key);
    }

    //Attachments
//    $mail->addAttachment("etsy_before_push.txt");
//    $mail->addAttachment("etsy_after_push.txt");
  //  if($count_expired_today >0)
  //  $mail->addAttachment($sendfile_name);

$mail_link1 = "https://strategicisus-my.sharepoint.com/:x:/r/personal/reports_fcsus_com1/Documents/".$main_link_to_onedrive."/".$file;
$mail_link2 = "https://strategicisus-my.sharepoint.com/:x:/r/personal/reports_fcsus_com1/Documents/".$main_link_to_onedrive."/".$file2;
$mail_link3 = "https://strategicisus-my.sharepoint.com/:x:/r/personal/reports_fcsus_com1/Documents/".$main_link_to_onedrive."/".$sendfile_name;
    //Content
    $mail->isHTML(true);                                  // Set email format to HTML
    $mail->Subject = 'ETSY local qty & price update issue listing';
    $mail->Body =  "<p>Hi Team ,</p>
    <p>PFA</p>";
    if($count_expired_today==0)
    $mail->Body .="<p>Today No products have expired in Etsy.</p>";
    else {
    $mail->Body .="<p><b>$count_expired_today</b> skus have expired between  $prev_date and $todays.";
    }

    $mail->Body .= "<p>File attachment links are: </p>
                  <p><a href='$mail_link1'>$file</a> </p><br/>
<p><a href='$mail_link2'>$file2</a> </p><br/>
<p><a href='$mail_link3'>$sendfile_name</a> </p><br/>
</p>";

   // $mail->send();
    echo 'Message has been sent';
    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log, alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Mail Function has been stop by sachin','$alert_id')");
} catch (Exception $e) {
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;
    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Unable to send mail $mail->ErrorInfo','$alert_id')");
}


unlink("etsy_today_local.csv");
unlink($file);
unlink($sendfile_name);
unlink($file2);

$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log, alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Deleted files from the server.','$alert_id')");
$del_count=0;
$foldertocheck = $main_link_to_onedrive;

if ($foldertocheck != '')
{
    //https://back.greatvaluestores.com/python_scripts/delete_file_onedrive_thirty.py
    echo "python ../python_scripts/delete_file_onedrive_thirty.py $foldertocheck";
    $command1 = escapeshellcmd("python ../python_scripts/delete_file_onedrive_thirty.py $foldertocheck");
    $output1 = shell_exec($command1);
    if(trim($output1) == 'done'){
        $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Deleted data from onedrive above 30 days','$alert_id')");
    }else{
    $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Unable to delete onedrive files','$alert_id')");
    $q2 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to delete file in onedrive', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q2);
    $del_count++;
    }

}

// delete data in execution log
$del_data = "Delete from script_execution ne WHERE DATEDIFF(CURRENT_DATE, ne.execution_time) > 30 and ne.script_name ='$script_name' ";
$del = $notify_conn->query($del_data);
if ($del)
 $notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id) values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Deleted data from log table above 30 days','$alert_id')");


 $link_to_onedrive = $main_link_to_onedrive . '/' . $file.",".$main_link_to_onedrive . '/' . $file2.",".$main_link_to_onedrive . '/' . $sendfile_name;
 if($del_count==0){
 $q2 = "UPDATE alert_success_error_dashboard SET success_msg = 'Successfully generated txt file', action = 'No Action Needed', link_to_onedrive = '$link_to_onedrive', error_msg = '' WHERE id = '$alert_id'";
 $succ_query = mysqli_query($notify_conn, $q2);
}
else {
  $q2 = "UPDATE alert_success_error_dashboard SET link_to_onedrive = '$link_to_onedrive' WHERE id = '$alert_id'";
  $err_query = mysqli_query($notify_conn, $q2);
}

$sql_query="UPDATE gvs_scripts_execution_log  set closed_on=NOW() where  id='$last_id' ";
mysqli_query($reports_conn,$sql_query) ;
$notify_conn->query("insert into script_execution(script_name,execution_time,execution_log,alert_id)
 values('$script_name','" . date('Y-m-d H:i:s', time()) . "','Etsy price and inventory update script is completed','$alert_id')");


 ?>