<?php
session_start();
date_default_timezone_set('America/New_York');
require('../../../../../shell/gvs_cf.php');
require('sub_ec_db_connection_price.php');
require('../../../php_plugins/php-excel-reader/excel_reader2.php');
require('../../../php_plugins/SpreadsheetReader/SpreadsheetReader.php');

date_default_timezone_set('UTC');

$StartMem = memory_get_usage();

$price_mgt=mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_PRICE_MGMT) or die(mysqli_error($price_mgt));
$reports_conn = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_REPORTS) or die(mysqli_error($reports_conn));
$notify_conn = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_NOTIFY_DASHBOARD) or die(mysqli_error($notify_conn));
$notion = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_FCSUSDBADMIN_NOTIONS_DROPSHIP) or die(mysqli_error($notion));
$price_approval = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_PRICE_APPROVAL) or die(mysqli_error($price_approval));
$econ_conn = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_ECONTHEN_PORTAL) or die(mysqli_error($econ_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('Ebay EC Price 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('Ebay EC Price 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()) . "',
'Ebay EC Price Update script has been started','$alert_id')");

$reports_conn->query("insert into gvs_scripts_execution_log(script_name,start_on)
values('Ebay EC price 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;
   }

//get filenames
// $get_file_ec="Select current_file_name from file_name_new where filename='ec_l1'";
// $result_ec=mysqli_query($reports_conn,$get_file_ec);
// while($rows_ec=mysqli_fetch_array($result_ec)){
//   $ec_file_name=trim($rows_ec[0]);
// }
$ec_file_name="ec_l1.csv";
// $get_file_hw="Select current_file_name from file_name_new where filename='hw_l1'";
// $result_hw=mysqli_query($reports_conn,$get_file_hw);
// while($rows_hw=mysqli_fetch_array($result_hw)){
//   $hw_file_name=trim($rows_hw[0]);
// }
$hw_file_name="hw_l1.csv";

   $lowest_skus=array();

   if (($handle = fopen("../../../amazon/generate_ec_hw_l1_reports/".$ec_file_name, "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()) . "',
         'Fetched lowest price from amazon ec l1 file','$alert_id')");

           while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
             {
               if($data[0]!='sku')
                 {
                 	if(strlen($data[4])>0)
               		$lowest_skus[$data[0]]=$data[4];
                 }
             }
       } 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()) . "',
         'Couldnt find amazon ec l1 file','$alert_id')");

         $q2 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to Find amazon ec_l1 file', action = 'Issue' WHERE id = '$alert_id'";
         $err_query = mysqli_query($notify_conn, $q2);
         exit();
       }
   if (($handle = fopen("../../../amazon/generate_ec_hw_l1_reports/".$hw_file_name, "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()) . "',
         'Fetch lowest price from amazon hw l1 file','$alert_id')");
           while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
             {
               if($data[0]!='sku')
                 {
                 	if(strlen($data[4])>0)
               		$lowest_skus[$data[0]]=$data[4];
                 }
             }
       }
       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()) . "',
         'Couldnt find amazon hw l1 file','$alert_id')");

         $q2 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to Find amazon hw_l1 file', action = 'Issue' WHERE id = '$alert_id'";
         $err_query = mysqli_query($notify_conn, $q2);
         exit();
       }


  $min_price=array();
	$max_price=array();
	$final_price=array();
	$rule_sku=array();

	$b2b_sku_prefix1='';
$b2b_sku_prefix2='';
$b2b_amz_store1='';
$b2b_amz_store2='';

$b2c_sku_prefix1='';
$b2c_sku_prefix2='';
$b2c_amz_store1='';
$b2c_amz_store2='';

$b2b_rule='';
$b2c_rule='';


$sql="select *from price_rule_stores where store='ebay_ec' and sku_type='b2b' ";
$result=mysqli_query($price_mgt,$sql);
while($row=mysqli_fetch_array($result))
		{
				$b2b_sku_prefix1=$row['sku_prefix1'];
				$b2b_sku_prefix2=$row['sku_prefix2'];
				$b2b_amz_store1=$row['amazon_store1'];
				$b2b_amz_store2=$row['amazon_store2'];
				$b2b_rule=$row['rule_percentage']."_".$row['add_subtract'];
        $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()) . "',
        'Fetch price rules from price_management.price_rule_stores for ebay_ec store and sku type b2b.','$alert_id')");

		}


$sql="select *from price_rule_stores where store='ebay_ec' and sku_type='b2c' ";
$result=mysqli_query($price_mgt,$sql);
if($result){
  $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()) . "',
  'Fetch price rules from price_management.price_rule_stores for ebay_ec store and sku type b2c.','$alert_id')");
}
while($row=mysqli_fetch_array($result))
		{
				$b2c_sku_prefix1=$row['sku_prefix1'];
				$b2c_sku_prefix2=$row['sku_prefix2'];
				$b2c_amz_store1=$row['amazon_store1'];
				$b2c_amz_store2=$row['amazon_store2'];
				$b2c_rule=$row['rule_percentage']."_".$row['add_subtract'];

		}


	$rule_sku_flag=array();
	$sql="select *from price_updates_on_marketplaces where  store='ebay_ec' and as_per_rule='0'";
	$result=mysqli_query($price_mgt,$sql) or die(mysqli_error($price_mgt));

  if($result){
    $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()) . "',
    'Fetch details from price_management.price_updates_on_marketplaces for ebay_ec store and as_per_rule=0','$alert_id')");
  }

	while($row=mysqli_fetch_array($result))
	{
		$sku=$row['sku'];
		$price=$row['price'];
		$store=$row['store'];
		$date=$row['valid_date'];
		$flag=$row['price_band_override'];
		$today=date('Y-m-d');
		if(strtotime($today) <= strtotime($date))
		{
			$rule_sku[$sku]=$price;
			$rule_sku_flag[$sku]=$flag;
		}
	}

$sql="SELECT  seller_sku,price  FROM   ec_all_listing ";
$result=mysqli_query($reports_conn,$sql);
if($result){
$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 sku and current price from Reports.ec_all_listing table','$alert_id')");
}
$ec_current_price=array();
$hw_current_price=array();
while($row=mysqli_fetch_array($result))
	{
			$ec_current_price[$row['seller_sku']]=$row['price'];
	}


$sql="SELECT  seller_sku,price  FROM   hw_all_listing";
$result=mysqli_query($reports_conn,$sql);
if($result){
$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 sku and current price from Reports.hw_all_listing table','$alert_id')");
}

while($row=mysqli_fetch_array($result))
	{
			$hw_current_price[$row['seller_sku']]=$row['price'];
	}

	$sql="select sku,Minimum_price,Maximum_Price,Our_final_price
  from Essential_ebay";
  $result=mysqli_query($notion,$sql);
if($result){
  $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()) . "',
  'Fetch sku,Minimum_price,Maximum_price,Our_final_price from
fcsusdbadmin_notions_dropship.Essential_ebay table','$alert_id')");
}
  while($row=mysqli_fetch_array($result))
	 {
			$min_price[$row['sku']]=$row['Minimum_price'];
			$max_price[$row['sku']]=$row['Maximum_Price'];
			$final_price[$row['sku']]=$row['Our_final_price'];
	}

$sql="select sku,Minimum_price,Maximum_Price,Our_final_price from
hd_supply_ebay";
$result=mysqli_query($notion,$sql);
if($result){
  $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()) . "',
  'Fetch sku,Minimum_price,Maximum_price,Our_final_price from
fcsusdbadmin_notions_dropship.hd_supply_ebay table','$alert_id')");
}

while($row=mysqli_fetch_array($result))
	{
			$min_price[$row['sku']]=$row['Minimum_price'];
			$max_price[$row['sku']]=$row['Maximum_Price'];
			$final_price[$row['sku']]=$row['Our_final_price'];
	}

$sql="select sku,Minimum_price,Maximum_Price,Our_final_price from
 buy_season_ebay";
$result=mysqli_query($notion,$sql);

if($result){
  $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()) . "',
  'Fetch sku,Minimum_price,Maximum_price,Our_final_price from
fcsusdbadmin_notions_dropship.buy_season_ebay table','$alert_id')");
}

while($row=mysqli_fetch_array($result))
	{
			$min_price[$row['sku']]=$row['Minimum_price'];
			$max_price[$row['sku']]=$row['Maximum_Price'];
			$final_price[$row['sku']]=$row['Our_final_price'];
	}

$sql="select sku,Minimum_price,Maximum_Price,Our_final_price from
 notions_sd300_ebay_new";
$result=mysqli_query($notion,$sql);
if($result){
  $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()) . "',
  'Fetch sku,Minimum_price,Maximum_price,Our_final_price from
fcsusdbadmin_notions_dropship.notions_sd300_ebay_new table','$alert_id')");
}

while($row=mysqli_fetch_array($result))
	{
			$min_price[$row['sku']]=$row['Minimum_price'];
			$max_price[$row['sku']]=$row['Maximum_Price'];
			$final_price[$row['sku']]=$row['Our_final_price'];
	}

$sql="select sku,Minimum_price,Maximum_Price,Our_final_price from
KW01_ebay_dropship";
$result=mysqli_query($notion,$sql);
if($result){
  $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()) . "',
  'Fetch sku,Minimum_price,Maximum_price,Our_final_price from
fcsusdbadmin_notions_dropship.KW01_ebay_dropship table','$alert_id')");
}

while($row=mysqli_fetch_array($result))
	{
			$min_price[$row['sku']]=$row['Minimum_price'];
			$max_price[$row['sku']]=$row['Maximum_Price'];
			$final_price[$row['sku']]=$row['Our_final_price'];
	}


$sql="select sku,Minimum_price,Maximum_Price,Our_final_price from
leemarpet_ebay_dropship";
$result=mysqli_query($notion,$sql);
if($result){
  $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()) . "',
  'Fetch sku,Minimum_price,Maximum_price,Our_final_price from
fcsusdbadmin_notions_dropship.Leemarpet_ebay_dropship table','$alert_id')");
}
while($row=mysqli_fetch_array($result))
	{

			$min_price[$row['sku']]=$row['Minimum_price'];
			$max_price[$row['sku']]=$row['Maximum_Price'];
			$final_price[$row['sku']]=$row['Our_final_price'];
	}


	$sql="select sku,min_price_ebay,max_price_ebay,final_price_ebay from b2b_price_mgt";
$result=mysqli_query($econ_conn,$sql);
if($result){
  $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()) . "',
  'Fetch sku,min_price_ebay,max_price_ebay,final_price_ebay from
econthen_portal.b2b_price_mgt table','$alert_id')");
}
while($row=mysqli_fetch_array($result))
	{

			$min_price[$row['sku']]=$row['min_price_ebay'];
			$max_price[$row['sku']]=$row['max_price_ebay'];
			$final_price[$row['sku']]=$row['final_price_ebay'];
	}

$sql="select sku,Min_price_ebay,Max_price_ebay,final_price_ebay from
 all_price_PET_b2b";
$result=mysqli_query($price_approval,$sql);
if($result){
  $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()) . "',
  'Fetch sku,Min_price_ebay,Max_price_ebay,final_price_ebay from
price_approval.all_price_PET_b2b table','$alert_id')");
}

while($row=mysqli_fetch_array($result))
	{
			$min_price[$row['sku']]=$row['Min_price_ebay'];
			$max_price[$row['sku']]=$row['Max_price_ebay'];
			$final_price[$row['sku']]=$row['final_price_ebay'];
	}


$sql="select sku,Min_price_ebay,Max_price_ebay,final_price_ebay from
all_price_approval_NM01_new";
$result=mysqli_query($price_approval,$sql);
if($result){
  $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()) . "',
  'Fetch sku,Min_price_ebay,Max_price_ebay,final_price_ebay from
price_approval.all_price_approval_NM01_new table','$alert_id')");
}

while($row=mysqli_fetch_array($result))
	{
			$min_price[$row['sku']]=$row['Min_price_ebay'];
			$max_price[$row['sku']]=$row['Max_price_ebay'];
			$final_price[$row['sku']]=$row['final_price_ebay'];
	}


$del_query= mysqli_query($reports_conn,"delete from ebay_ec_price_mgt");
if($del_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()) . "',
  'Delete values from Reports.ebay_ec_price_mgt','$alert_id')");
}

	try
	{

		$estTime = (new DateTime('America/New_York'))->format('H');
		$est =new DateTime('America/New_York');

        $day=$est->format('l');
		$hour= $est->format('H');
		$EST= FALSE;
		$Filepath='../../ebay_ec/files_to_compare/ActiveInventoryReport.csv';
		$db=new DBController(DATABASE_REPORTS);
    $db_query="SELECT  sku, item_id, current_price FROM ebay_ec_price_mgt";

		$db_data=$db->getdata($db_query);
    print_r($db_data);

    $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()) . "',
    'Fetch sku,item_id and current_price from Reports.ebay_ec_price_mgt','$alert_id')");

		foreach($db_data as $db_row)
		{
			$db_records[$db_row['sku']]=$db_row;
		}

		$inv_arr=$final_lm_array=$final_kw_array=$final_array=$dup=$insert_data=$all_inv_arr=$final_array_res=array();

		$lm01_data=array();
		$kw01_data=array();
		$es01_data=array();
		$nm01_data=array();

		$Spreadsheet = new SpreadsheetReader($Filepath);
		$BaseMem = memory_get_usage();

		$Sheets = $Spreadsheet -> Sheets();
    $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()) . "',
    'Read ActiveInventoryReport.csv file (downloaded from Ebay EC)','$alert_id')");

		foreach ($Sheets as $Index => $Name)
		{
			$Spreadsheet -> ChangeSheet($Index);
			$is_first_row=0;
			foreach ($Spreadsheet as $Key => $Row)
			{

				if ($is_first_row==0)
				{
					$is_first_row=1;
					continue;
				}

				$all_inv_arr[]=$Row;
				$search='LM01';
				$lm = strpos($Row[0], $search);
				$kw=strpos($Row[0], 'KW01');
				$es=strpos($Row[0], 'ES01');
				$nm=strpos($Row[0], 'NM01');


				if ($lm !== false)
				{
					$lm01_data[]=$Row;
					$inv_arr[]=$Row;

					$price_data[$Row[0]]=$Row;
					continue;
				}else if ($kw !== false)
				{
					$kw01_data[]=$Row;
					$inv_arr[]=$Row;
					$price_data[$Row[0]]=$Row;
					continue;
				}else if ($nm !== false)
				{
					$nm01_data[]=$Row;
					$inv_arr[]=$Row;
					$price_data[$Row[0]]=$Row;
					continue;
				}
				else if ($es !== false)
				{
					$es01_data[]=$Row;
					$inv_arr[]=$Row;
					$price_data[$Row[0]]=$Row;
					continue;

				}


			}
		}


    $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()) . "',
    'Search and store skus of type LM01,KW01,ES01 and NM01 along with their price from file','$alert_id')");

foreach($all_inv_arr as $price_rec)
{
	$is_found=0;
	if(isset($db_records[$price_rec[0]]))
	{
		$is_found=1;
		continue;

	}
	if($is_found === 0)
	{
		$insert_data[]= $price_rec;
	}
}
$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 if prices are set for file sku.if not set, then insert to insert_data array','$alert_id')");

$data=array();
if(count($insert_data) > 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()) . "',
  'By applying the rules fetched from price_rule_Stores,we calculate the new price accordingly.','$alert_id')");

	foreach($insert_data as $ins_rec)
	{

		$cp=$ins_rec[1];
		$sku=$ins_rec[0];
		if(strpos($sku,'LM01')!==False)
		{
			$sku=strtoupper($sku);
			$sku=str_replace("_B2B", "_b2b", $sku);
		}
		$prev_price=$cp;
		$cp=0;
		$done=0;
		$b2b_skus=explode(",", $b2b_sku_prefix1);


		if(count($b2b_skus))
				{
						$amz_rule=explode("_",$b2b_rule);

						$amz_store=$b2b_amz_store1;
						if(strpos($sku,"NM01")!==False && in_array('NM01_b2b',$b2b_skus)){
						if(strpos($sku,"NM01")!==False &&  strpos($sku,"b2b")!==False)
								{
										$sk=substr($sku,0,4)."_b2b";
										if(in_array($sk,$b2b_skus)){
										if($amz_store=='ec')
												{
													if(array_key_exists($sku,$ec_current_price)){
													$done=1;
													if($amz_rule[1]=='1')
														$cp=$ec_current_price[$sku]+($ec_current_price[$sku]*($amz_rule[0]/100));
													else
														$cp=$ec_current_price[$sku]-($ec_current_price[$sku]*($amz_rule[0]/100));
												}
												}
										else if($amz_store=='hw')
												{
													if(array_key_exists($sku,$hw_current_price)){
													$done=1;
													if($amz_rule[1]=='1')
														$cp=$hw_current_price[$sku]+($hw_current_price[$sku]*($amz_rule[0]/100));
													else
														$cp=$hw_current_price[$sku]-($hw_current_price[$sku]*($amz_rule[0]/100));
												}
												}
											}
								}
							}
						else
								{
										 $sk=substr($sku,0,4);
										$sk_b2b=$sk."_b2b";
										if(in_array($sk, $b2b_skus) || in_array($sk_b2b, $b2b_skus))
												{

														if($amz_store=='ec')
																{
																	if(array_key_exists($sku,$ec_current_price)){
																	$done=1;
																	if($amz_rule[1]=='1')
																		$cp=$ec_current_price[$sku]+($ec_current_price[$sku]*($amz_rule[0]/100));
																	else
																		$cp=$ec_current_price[$sku]-($ec_current_price[$sku]*($amz_rule[0]/100));
																}
																}
													else if($amz_store=='hw')
															{
																if(array_key_exists($sku,$hw_current_price)){
																$done=1;
																if($amz_rule[1]=='1')
																	$cp=$hw_current_price[$sku]+($hw_current_price[$sku]*($amz_rule[0]/100));
																else
																	$cp=$hw_current_price[$sku]-($hw_current_price[$sku]*($amz_rule[0]/100));
															}
															}
												}

								}

				}

		$b2b_skus=explode(",", $b2b_sku_prefix2);

		if(count($b2b_skus))
				{
						$amz_rule=explode("_",$b2b_rule);
						$amz_store=$b2b_amz_store2;
						if(strpos($b2b_sku_prefix2,"NM01")!==False && in_array('NM01_b2b',$b2b_skus)){
						if(strpos($sku,"NM01")!==False &&  strpos($sku,"b2b")!==False)
								{
										$sk=substr($sku,0,4)."_b2b";
										if(in_array($sk,$b2b_skus)){
										if($amz_store=='ec')
												{
													if(array_key_exists($sku,$ec_current_price)){
													$done=1;
													if($amz_rule[1]=='1')
														$cp=$ec_current_price[$sku]+($ec_current_price[$sku]*($amz_rule[0]/100));
													else
														$cp=$ec_current_price[$sku]-($ec_current_price[$sku]*($amz_rule[0]/100));
												}
												}
										else if($amz_store=='hw')
												{
													if(array_key_exists($sku,$hw_current_price)){
													$done=1;
													if($amz_rule[1]=='1')
														$cp=$hw_current_price[$sku]+($hw_current_price[$sku]*($amz_rule[0]/100));
													else
														$cp=$hw_current_price[$sku]-($hw_current_price[$sku]*($amz_rule[0]/100));
												}
												}
								}
							}
							}
						else
								{
										$sk=substr($sku,0,4);
										$sk_b2b=$sk."_b2b";
										if(in_array($sk, $b2b_skus) || in_array($sk_b2b, $b2b_skus))
												{
														if($amz_store=='ec')
																{
																	if(array_key_exists($sku,$ec_current_price)){
																	$done=1;
																	if($amz_rule[1]=='1')
																		$cp=$ec_current_price[$sku]+($ec_current_price[$sku]*($amz_rule[0]/100));
																	else
																		$cp=$ec_current_price[$sku]-($ec_current_price[$sku]*($amz_rule[0]/100));
																}
																}
													else if($amz_store=='hw')
															{
																if(array_key_exists($sku,$hw_current_price)){
																$done=1;
																if($amz_rule[1]=='1')
																	$cp=$hw_current_price[$sku]+($hw_current_price[$sku]*($amz_rule[0]/100));
																else
																	$cp=$hw_current_price[$sku]-($hw_current_price[$sku]*($amz_rule[0]/100));
															}
															}
												}

								}

				}


			$b2c_skus=explode(",", $b2c_sku_prefix1);

		if(count($b2c_skus))
				{
						$amz_rule=explode("_",$b2c_rule);
						$amz_store=$b2c_amz_store1;
						if(strpos($b2c_sku_prefix1,"NM01")!==False && in_array('NM01',$b2c_skus) ){
							$sk=substr($sku,0,4);
										if(in_array($sk,$b2c_skus)){
									if(strpos($sku,"b2b")!==False)
											{
											}
									else{
										if($amz_store=='ec')
												{
													if(array_key_exists($sku,$ec_current_price)){
													$done=1;
													if($amz_rule[1]=='1')
														$cp=$ec_current_price[$sku]+($ec_current_price[$sku]*($amz_rule[0]/100));
													else
														$cp=$ec_current_price[$sku]-($ec_current_price[$sku]*($amz_rule[0]/100));
												}
												}
										else if($amz_store=='hw')
												{
													if(array_key_exists($sku,$hw_current_price)){
													$done=1;
													if($amz_rule[1]=='1')
														$cp=$hw_current_price[$sku]+($hw_current_price[$sku]*($amz_rule[0]/100));
													else
														$cp=$hw_current_price[$sku]-($hw_current_price[$sku]*($amz_rule[0]/100));
												}
												}
								}
							}
							}
						else
								{
									$sk=substr($sku,0,4);

										if(in_array($sk, $b2c_skus))
												{
														if($amz_store=='ec')
																{
																	if(array_key_exists($sku,$ec_current_price)){
																	$done=1;
																	if($amz_rule[1]=='1')
																		$cp=$ec_current_price[$sku]+($ec_current_price[$sku]*($amz_rule[0]/100));
																	else
																		$cp=$ec_current_price[$sku]-($ec_current_price[$sku]*($amz_rule[0]/100));
																}
																}
													else if($amz_store=='hw')
															{
																if(array_key_exists($sku,$hw_current_price)){
																$done=1;
																if($amz_rule[1]=='1')
																	$cp=$hw_current_price[$sku]+($hw_current_price[$sku]*($amz_rule[0]/100));
																else
																	$cp=$hw_current_price[$sku]-($hw_current_price[$sku]*($amz_rule[0]/100));
															}
															}
												}

								}

				}


			$b2c_skus=explode(",", $b2c_sku_prefix2);

		if(count($b2c_skus))
				{
						$amz_rule=explode("_",$b2c_rule);
						$amz_store=$b2c_amz_store2;
						if(strpos($b2c_sku_prefix2,"NM01")!==False && in_array('NM01',$b2c_skus)){
							$sk=substr($sku,0,4);
										if(in_array($sk,$b2c_skus)){
									if(strpos($sku,"b2b")!==False)
											{
											}
									else{
										if($amz_store=='ec')
												{
													if(array_key_exists($sku,$ec_current_price)){
													$done=1;
													if($amz_rule[1]=='1')
														$cp=$ec_current_price[$sku]+($ec_current_price[$sku]*($amz_rule[0]/100));
													else
														$cp=$ec_current_price[$sku]-($ec_current_price[$sku]*($amz_rule[0]/100));
													}
												}
										else if($amz_store=='hw')
												{
													if(array_key_exists($sku,$hw_current_price)){
													$done=1;
													if($amz_rule[1]=='1')
														$cp=$hw_current_price[$sku]+($hw_current_price[$sku]*($amz_rule[0]/100));
													else
														$cp=$hw_current_price[$sku]-($hw_current_price[$sku]*($amz_rule[0]/100));
												}
												}
								}
							}
							}
						else
								{
									$sk=substr($sku,0,4);

										if(in_array($sk, $b2c_skus))
												{
														if($amz_store=='ec')
																{

																	if(array_key_exists($sku,$ec_current_price)){
																		$done=1;
																	if($amz_rule[1]=='1')
																		$cp=$ec_current_price[$sku]+($ec_current_price[$sku]*($amz_rule[0]/100));
																	else
																		$cp=$ec_current_price[$sku]-($ec_current_price[$sku]*($amz_rule[0]/100));
																	}
																}
													else if($amz_store=='hw')
															{
																if(array_key_exists($sku,$hw_current_price)){
																	$done=1;
																	if($amz_rule[1]=='1')
																		$cp=$hw_current_price[$sku]+($hw_current_price[$sku]*($amz_rule[0]/100));
																	else
																		$cp=$hw_current_price[$sku]-($hw_current_price[$sku]*($amz_rule[0]/100));
																}
															}
												}

								}

				}

		if($done==0){

		if(array_key_exists($sku, $lowest_skus))
			{
				$cp=$lowest_skus[$sku];
				$done=1;
			}
		}
		if($done==0)

			$cp=$final_price[$sku];
		$f=0;
		if(array_key_exists($sku,$rule_sku)){
			$cp=$rule_sku[$sku];
			if($rule_sku_flag[$sku]>0)
				$f=1;
		}


		if($f==0){
		if(array_key_exists($sku, $min_price))
			{
				if($cp<$min_price[$sku])
				{
					//$prev_price=$cp;
					$cp=$min_price[$sku];
				}

			}
		if(array_key_exists($sku, $max_price))
			{
				if($cp>$max_price[$sku]){

					$cp=$max_price[$sku];
				}

			}
		}

		if($cp<='0')
			$cp=$prev_price;

			$date=date("Y-m-d H:i:s");
		$time=date("H:i:s");
		$data[]="('".$sku."','".$ins_rec[3]."','".$cp."','".$prev_price."','0','".$date."','".$date."')";

	}
}

$sql="insert into ebay_ec_price_mgt values".implode(",",$data);
$ins_result=mysqli_query($reports_conn,$sql) or die(mysqli_error($price_mgt));
if($ins_result){
  $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()) . "',
  'Insert the newly calculated price into Reports.ebay_ec_price_mgt table','$alert_id')");
}

$price_mgt=new DBController(DATABASE_REPORTS);


		$db_query="SELECT sku, item_id, current_price FROM ebay_ec_price_mgt WHERE current_price <> COALESCE (previous_price,0) ";

		$final_array_res=$price_mgt->getdata($db_query);
    // print_r($final_array_res);
    $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()) . "',
    'Fetch sku,item_id,current_price from Reports.ebay_ec_price_mgt table for current price != previous price and note equal to 0','$alert_id')");
		foreach($final_array_res as $final_rec)
		{
			$b2c_check_skus1=explode(",", $b2c_sku_prefix1);
			$b2c_check_skus2=explode(",", $b2c_sku_prefix2);
			$b2b_check_skus1=explode(",", $b2b_sku_prefix1);
			$b2b_check_skus2=explode(",", $b2b_sku_prefix2);
			$sku=$final_rec['sku'];
			$sk=substr($sku,0,4);

			$check=0;

			if(in_array($sk,$b2c_check_skus1))
				$check=1;
			if(in_array($sk,$b2c_check_skus2))
				$check=1;
			if(in_array($sk,$b2b_check_skus1))
				$check=1;
			if(in_array($sk,$b2b_check_skus2))
				$check=1;


			$sk_b2b=$sk."_b2b";
			if(in_array($sk_b2b,$b2c_check_skus1))
				$check=1;
			if(in_array($sk_b2b,$b2c_check_skus2))
				$check=1;
			if(in_array($sk_b2b,$b2b_check_skus1))
				$check=1;
			if(in_array($sk_b2b,$b2b_check_skus2))
				$check=1;

			if($check==0)
				continue;
			$final_array[]=array('*Action(SiteID=US|Country=US|Currency=USD|Version=941)' => 'Revise',
								'ItemID'=>$final_rec['item_id'],
								'*StartPrice'=> $final_rec['current_price']);
		}

$_SESSION['final_array_ec']=count($final_array);
		if(count($final_array) <= 0)
		{
			$final_array[]=array('*Action(SiteID=US|Country=US|Currency=USD|Version=941)' => '',
												'ItemID'=> '',
												'*StartPrice'=> '');
      $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()) . "',
      'Final_array count is less than or equal to zero,we place only the header in it.','$alert_id')");

		}

			$file='price_update_ec_'. date("Y_m_d_h_i_s") . ".csv";
      $_SESSION['price_file_name']=$file;

			$fp = fopen($file, 'w');
			$array_columns=array_keys($final_array[0]);
			fputcsv($fp, $array_columns);
			foreach ($final_array as $row) {
				fputcsv($fp, $row);
			}

			fclose($fp);

      $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()) . "',
      'Price_Update file is created, and we insert the final array values.','$alert_id')");

      $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 file in onedrive Successfully','$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 file in onedrive folder','$alert_id')");
      $q2 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to upload file in onedrive', action = 'Issue' WHERE id = '$alert_id'";
      $succ_query = mysqli_query($notify_conn, $q2);
      }
			include('sub_ec_upload_prices.php');
      if(file_exists("error_log"))
			unlink('error_log');

	}
	catch (Exception $E)
	{
		echo $E->getMessage();
    $emsgs=$E->getMessage();
    $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()) . "',
    'Exception occured,$emsgs','$alert_id')");
	}

  // 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')");
$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'";
    $succ_query = mysqli_query($notify_conn, $q2);
    $del_count++;
    }
}


  $link_to_onedrive = $foldername . '/' . $file;
  if($del_count==0){
  $q2 = "UPDATE alert_success_error_dashboard SET success_msg = 'Successfully generated csv 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);
}

unlink($file);

$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()) . "','Ebay EC price update script completed','$alert_id')");

?>