<?php
include '../../../../shell/gvs_cf.php';

$reports_conn = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_REPORTS) or die(mysqli_error($reports_conn));
$greatval_test_sachin = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_GREATVAL_TEST_SACHIN) or die(mysqli_error($greatval_test_sachin));
$econ_conn = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_ECONTHEN_PORTAL) or die(mysqli_error($econ_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));
$notify_conn = mysqli_connect(DATABASE_SERVER, DATABASE_USER_ONE, DATABASE_PASSWORD_ONE, DATABASE_NOTIFY_DASHBOARD) or die(mysqli_error($notify_conn));

ini_set('max_execution_time', 0);
ini_set("memory_limit", "-1");

date_default_timezone_set('America/New_York');
$cur_day_time = date("Y-m-d H:i:s");
$curr_file_path = __FILE__;

$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('Amazon EC Audit','','$cur_day_time','','Issue','Issue with script_details_dashboard table')";
    $ins_query = mysqli_query($notify_conn, $ins_alert);
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']);
}

$ins_alert = "Insert into alert_success_error_dashboard(script_name,link_to_onedrive, report_time, responsible, action,supervisor)values('$script_name','$main_link_to_onedrive','$cur_day_time','$responsible','Issue','$supervisor')";
$ins_query = mysqli_query($notify_conn, $ins_alert);
$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()) . "','Amazon EC Audit script has been started','$alert_id')");

/*=================delete from one drive if mail over 30 days=====================================================*/
$foldertocheck = $main_link_to_onedrive;

if ($foldertocheck != '')
{
    $command1 = escapeshellcmd("python ../../../python_scripts/delete_file_onedrive_thirty.py $foldertocheck");
    $output1 = shell_exec($command1);
    echo "<br>delthirty:" . $output1 . "<br>";
    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 found 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($output1 != ''){
        $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 folder','$alert_id')");
    }
}

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

    }

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


$to_emails = array();
$cc_emails = array();
$sql = "select *from fcsus_scripts_details where id='107'";
$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()) . "','Unable to connect table fcsus_scripts_details','$alert_id')");    
}
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;
}

$amazon_file = "Amazon_ec_all_listing.csv";

$cur_day_time = date("Y-m-d H:i:s");
if (($handle = fopen($amazon_file, "r")) !== false)
{
    $dd = 0;

    while (($data = fgetcsv($handle, 1000, ",")) !== false)

    {
        if ($dd == 0)
        {
            $dd++;
            $delete_data = "Delete from Amazon_ec_Listing";
            $delte_res = $greatval_test_sachin->query($delete_data);
            if (!$delte_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()) . "','Unable to delete table Amazon_ec_Listing','$alert_id')"); 
                $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to delete table Amazon_ec_Listing', action = 'Issue' WHERE id = '$alert_id'";
                $err_query = mysqli_query($notify_conn, $q1);
                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()) . "','Amazon EC listing file found and deleted data in table Amazon_ec_Listing', '$alert_id')");                
            }

            continue;
        }

        $qty = $data[5];
        $sku = $data[3];
        $update = "INSERT INTO `Amazon_ec_Listing` (sku,qty)
SELECT * FROM( SELECT '$sku' AS sku,'$qty' AS qty) AS tmp
WHERE NOT EXISTS (SELECT * FROM Amazon_ec_Listing WHERE sku = '$sku')";
         $query = $greatval_test_sachin->query($update);
            if (!$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()) . "','Unable to insert in table Amazon_ec_Listing','$alert_id')"); 
                $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to insert in table Amazon_ec_Listing', action = 'Issue' WHERE id = '$alert_id'";
                $err_query = mysqli_query($notify_conn, $q1);
                exit;
            }

    }

}
else
{
    $link_to_onedrive = "";
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'File Not Found For Amazon EC Audit', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
                $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()) . "','File not found for Amazon EC Audit','$alert_id')"); 

    exit;
}
$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()) . "','Inserted sku, qty details in Amazon_ec_Listing table', '$alert_id')");

$sku_restricted_all = array();
$rest_ca10 = array();
$rest_de01 = array();
$rest_fc01 = array();
$rest_mi01 = array();
$rest_kw01b2b = array();
$rest_es01b2b = array();
$rest_mr01b2b = array();
$rest_lm01b2b = array();
$rest_nm01b2b = array();
$rest_wi01b2b = array();
$rest_kw01 = array();
$rest_nm01 = array();
$rest_es01 = array();
$rest_lm01 = array();

//get restricted products
$sql = "SELECT sku,market_place FROM `restricted_products`  WHERE market_place LIKE 'All%' OR market_place LIKE '%amazon%' OR market_place LIKE 'amzon%' OR market_place LIKE 'Discontinued%'";
$query = $price_approval->query($sql);
if (!$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()) . "','Unable to get restricted products','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to get restricted products', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}
while ($row = $query->fetch_assoc())
{
    $sku = trim($row['sku']);
    $sku_restricted_all[] = $sku;
    $prefix = substr($sku, 0, 4);
    $end_prefix = substr($sku, -3);
    if ($prefix == 'CA10')
    {

        $rest_ca10[] = trim($sku);
    }
    if ($prefix == 'DE01')
    {

        $rest_de01[] = trim($sku);
    }
    if ($prefix == 'FC01')
    {
        if (strpos($sku, ",") !== False)
        {
            $new_sku = explode(",", $sku);
            foreach ($new_sku as $sku)
            {
                $prefix = substr($sku, 0, 4);
                if ($sku != '' && $prefix == 'FC01') $rest_fc01[] = trim($sku);
            }
        }
        else
        {

            $rest_fc01[] = trim($sku);
        }
    }

    if ($prefix == 'MI01')
    {

        $rest_mi01[] = trim($sku);
    }
    if ($prefix == 'KW01' && $end_prefix == 'b2b')
    {

        $rest_kw01b2b[] = trim($sku);
    }
    if ($prefix == 'ES01' && $end_prefix == 'b2b')
    {

        $rest_es01b2b[] = trim($sku);
    }
    if ($prefix == 'MR01' && $end_prefix == 'b2b')
    {

        $rest_mr01b2b[] = trim($sku);
    }
    if ($prefix == 'LM01' && $end_prefix == 'b2b')
    {

        $rest_lm01b2b[] = trim($sku);
    }
    if ($prefix == 'NM01' && $end_prefix == 'b2b')
    {

        $rest_nm01b2b[] = trim($sku);
    }
    if ($prefix == 'WI01' && $end_prefix == 'b2b')
    {

        $rest_wi01b2b[] = trim($sku);
    }
    if ($prefix == 'NM01' && $end_prefix != 'b2b')
    {

        $rest_nm01[] = trim($sku);
    }
    if ($prefix == 'LM01' && $end_prefix != 'b2b')
    {

        $rest_lm01[] = trim(strtoupper($sku));
    }
    if ($prefix == 'ES01' && $end_prefix != 'b2b')
    {
        $rest_es01[] = trim($sku);
    }
    if ($prefix == 'KW01' && $end_prefix != 'b2b')
    {
        $rest_kw01[] = trim($sku);
    }

}

$disp = array();
$ignored_sku_list = array();
$data = array();

//CA10 SKU
$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'CA10%'";
$query = $econ_conn->query($sql);
if (!$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()) . "','Unable to get CA10 sku details','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to get CA10 details', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}
echo $CA10_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'CA10%' AND qt> 0";
$query = $econ_conn->query($sql);
$ca10_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $ca10_4p_active[] = trim($row['sku']);

}

$ca10_4p_active = array_diff($ca10_4p_active, $rest_ca10);
$data = array_merge($data, $ca10_4p_active);
$CA10_count_catalog_active = count($ca10_4p_active);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'CA10%'";
$query = $greatval_test_sachin->query($sql);
$CA10_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'CA10%' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$ca10_amz_active = array();
$CA10_count_amazon_ec_active = 0;
while ($row = $query->fetch_assoc())
{
    $ca10_amz_active[] = trim($row['sku']);
}
$ca10_amz_active = array_diff($ca10_amz_active, $rest_ca10);

if (count($ca10_amz_active) == 0)
{
    $CA10_count_amazon_ec_active = 0;
}
else
{
    foreach ($ca10_amz_active as $val)
    {
        if (in_array($val, $ca10_4p_active))
        {
            $CA10_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            elseif ($count_val_all == 2 || $count_val_all == 3 || $count_val_all == 4)
            {

                $ignored_sku_list[] = $val;
            }
        }
    }
}

//
$CA10_count_amazon_ec_inactive = $CA10_count_catalog_active - $CA10_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$CA10_count_catalog' ,`count_amazon_ec`='$CA10_count_amazon_ec' ,`count_catalog_active`='$CA10_count_catalog_active',`count_amazon_ec_active`='$CA10_count_amazon_ec_active',`count_amazon_ec_inactive`='$CA10_count_amazon_ec_inactive' WHERE `sku`='CA10' ";
$query = $greatval_test_sachin->query($sql);

if (!$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()) . "','Unable to update values in amazon ec audit for CA10','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for CA10', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku CA10','$alert_id')");

}


// DE01 SKU
$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'DE01%'";
$query = $econ_conn->query($sql);
echo $DE01_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'DE01%' AND qt>0";
$query = $econ_conn->query($sql);

$de01_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $de01_4p_active[] = trim($row['sku']);

}
$de01_4p_active = array_diff($de01_4p_active, $rest_de01);
$data = array_merge($data, $de01_4p_active);
$DE01_count_catalog_active = count($de01_4p_active);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'DE01%'";
$query = $greatval_test_sachin->query($sql);
$DE01_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'DE01%' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$DE01_count_amazon_ec_active = 0;
$de01_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $de01_amz_active[] = trim($row['sku']);
}
$de01_amz_active = array_diff($de01_amz_active, $rest_de01);
if (count($de01_amz_active) == 0)
{
    $DE01_count_amazon_ec_active = 0;
}
else
{
    foreach ($de01_amz_active as $val)
    {
        if (in_array($val, $de01_4p_active))
        {
            $DE01_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            elseif ($count_val_all == 2 || $count_val_all == 3 || $count_val_all == 4)
            {

                $ignored_sku_list[] = $val;
            }
        }
    }
}
$DE01_count_amazon_ec_inactive = $DE01_count_catalog_active - $DE01_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$DE01_count_catalog' ,`count_amazon_ec`='$DE01_count_amazon_ec' ,`count_catalog_active`='$DE01_count_catalog_active',`count_amazon_ec_active`='$DE01_count_amazon_ec_active',`count_amazon_ec_inactive`='$DE01_count_amazon_ec_inactive' WHERE `sku`='DE01' ";
$query = $greatval_test_sachin->query($sql);

if (!$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()) . "','Unable to update values in amazon ec audit for DE01','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for DE01', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}

$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku DE01','$alert_id')");

// FC01 SKU
$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'FC01%'";
$query = $econ_conn->query($sql);
echo $FC01_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'FC01%' AND qt>0";
$query = $econ_conn->query($sql);
$fc01_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $fc01_4p_active[] = trim($row['sku']);
}

$fc01_4p_active = array_diff($fc01_4p_active, $rest_fc01);
$data = array_merge($data, $fc01_4p_active);
$FC01_count_catalog_active = count($fc01_4p_active);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'FC01%'";
$query = $greatval_test_sachin->query($sql);
$FC01_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'FC01%' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$fc01_amz_active = array();
$FC01_count_amazon_ec_active = 0;
while ($row = $query->fetch_assoc())
{
    $fc01_amz_active[] = trim($row['sku']);
}
$fc01_amz_active = array_diff($fc01_amz_active, $rest_fc01);
if (count($fc01_amz_active) == 0)
{
    $FC01_count_amazon_ec_active = 0;
}
else
{
    foreach ($fc01_amz_active as $val)
    {
        if (in_array($val, $fc01_4p_active))
        {
            $FC01_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            elseif ($count_val_all == 2 || $count_val_all == 3 || $count_val_all == 4)
            {

                $ignored_sku_list[] = $val;
            }
        }
    }
}

$FC01_count_amazon_ec_inactive = $FC01_count_catalog_active - $FC01_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$FC01_count_catalog' ,`count_amazon_ec`='$FC01_count_amazon_ec' ,`count_catalog_active`='$FC01_count_catalog_active',`count_amazon_ec_active`='$FC01_count_amazon_ec_active',`count_amazon_ec_inactive`='$FC01_count_amazon_ec_inactive' WHERE `sku`='FC01' ";
$query = $greatval_test_sachin->query($sql);

if (!$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()) . "','Unable to update values in amazon ec audit for FC01','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for FC01', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}

$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku FC01','$alert_id')");

// MI01 SKU
$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'MI01%'";
$query = $econ_conn->query($sql);
echo $MI01_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'MI01%' AND qt>0";
$query = $econ_conn->query($sql);

$mi01_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $mi01_4p_active[] = trim($row['sku']);

}
$mi01_4p_active = array_diff($mi01_4p_active, $rest_mi01);
$data = array_merge($data, $mi01_4p_active);
$MI01_count_catalog_active = count($mi01_4p_active);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'MI01%'";
$query = $greatval_test_sachin->query($sql);
$MI01_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'MI01%' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$MI01_count_amazon_ec_active = 0;
$mi01_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $mi01_amz_active[] = trim($row['sku']);
}
$mi01_amz_active = array_diff($mi01_amz_active, $rest_mi01);

if (count($mi01_amz_active) == 0)
{
    $MI01_count_amazon_ec_active = 0;
}
else
{
    foreach ($mi01_amz_active as $val)
    {
        if (in_array($val, $mi01_4p_active))
        {
            $MI01_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            elseif ($count_val_all == 2 || $count_val_all == 3 || $count_val_all == 4)
            {

                $ignored_sku_list[] = $val;
            }
        }
    }
}

$MI01_count_amazon_ec_inactive = $MI01_count_catalog_active - $MI01_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$MI01_count_catalog' ,`count_amazon_ec`='$MI01_count_amazon_ec' ,`count_catalog_active`='$MI01_count_catalog_active',`count_amazon_ec_active`='$MI01_count_amazon_ec_active',`count_amazon_ec_inactive`='$MI01_count_amazon_ec_inactive' WHERE `sku`='MI01' ";
$query = $greatval_test_sachin->query($sql);

if (!$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()) . "','Unable to update values in amazon ec audit for MI01','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for MI01', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}

$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku MI01','$alert_id')");

//KW01_b2b
$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'KW01%' AND `sku` LIKE '%_b2b'";
$query = $econ_conn->query($sql);
echo $KW01_b2b_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'KW01%' AND `sku` LIKE '%_b2b' AND qt>0";
$query = $econ_conn->query($sql);

$kw01_b2b_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $kw01_b2b_4p_active[] = trim($row['sku']);

}

$kw01_b2b_4p_active = array_diff($kw01_b2b_4p_active, $rest_kw01b2b);
$data = array_merge($data, $kw01_b2b_4p_active);
$KW01_b2b_count_catalog_active = count($kw01_b2b_4p_active);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'KW01%' AND `sku` LIKE '%_b2b'";
$query = $greatval_test_sachin->query($sql);
$KW01_b2b_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'KW01%' AND `sku` LIKE '%_b2b' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$KW01_b2b_count_amazon_ec_active = 0;
$kw01_b2b_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $kw01_b2b_amz_active[] = trim($row['sku']);
}
$kw01_b2b_amz_active = array_diff($kw01_b2b_amz_active, $rest_kw01b2b);
if (count($kw01_b2b_amz_active) == 0)
{
    $KW01_b2b_count_amazon_ec_active = 0;
}
else
{
    foreach ($kw01_b2b_amz_active as $val)
    {
        if (in_array($val, $kw01_b2b_4p_active))
        {
            $KW01_b2b_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            else if ($count_val_all == 2)
            {
                if ($val_all[1] == 'b2b')
                {
                    $disp[] = $val;
                }
            }
            elseif ($count_val_all == 3 || $count_val_all == 4)
            {
                if ($val_all[1] == 'b2b')
                {
                    if (is_numeric($val_all[2]))
                    {

                        $ignored_sku_list[] = $val;
                    }
                }
            }
        }
    }
}
$KW01_b2b_count_amazon_ec_inactive = $KW01_b2b_count_catalog_active - $KW01_b2b_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$KW01_b2b_count_catalog' ,`count_amazon_ec`='$KW01_b2b_count_amazon_ec' ,`count_catalog_active`='$KW01_b2b_count_catalog_active',`count_amazon_ec_active`='$KW01_b2b_count_amazon_ec_active',`count_amazon_ec_inactive`='$KW01_b2b_count_amazon_ec_inactive' WHERE `sku`='KW01_b2b' ";
$query = $greatval_test_sachin->query($sql);

if (!$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()) . "','Unable to update values in amazon ec audit for KW01_b2b','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for KW01_b2b', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}

$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku KW01_b2b','$alert_id')");

// NM01_b2b SKU
$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'NM01%' AND `sku` LIKE '%b2b%'";
$query = $econ_conn->query($sql);
echo $NM01_b2b_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'NM01%' AND `sku` LIKE '%b2b%' AND qt>0";
$query = $econ_conn->query($sql);

$nm01_b2b_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $nm01_b2b_4p_active[] = trim($row['sku']);

}

$nm01_b2b_4p_active = array_diff($nm01_b2b_4p_active, $rest_nm01b2b);
$data = array_merge($data, $nm01_b2b_4p_active);
$NM01_b2b_count_catalog_active = count($nm01_b2b_4p_active);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'NM01%' AND `sku` LIKE '%b2b%'";
$query = $greatval_test_sachin->query($sql);
$NM01_b2b_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'NM01%' AND `sku` LIKE '%b2b%' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$NM01_b2b_count_amazon_ec_active = 0;
$nm01_b2b_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $nm01_b2b_amz_active[] = trim($row['sku']);
}

$nm01_b2b_amz_active = array_diff($nm01_b2b_amz_active, $rest_nm01b2b);

if (count($nm01_b2b_amz_active) == 0)
{
    $NM01_b2b_count_amazon_ec_active = 0;
}
else
{
    foreach ($nm01_b2b_amz_active as $val)
    {
        if (in_array($val, $nm01_b2b_4p_active))
        {
            $NM01_b2b_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            else if ($count_val_all == 2)
            {
                if ($val_all[1] == 'b2b')
                {
                    $disp[] = $val;
                }
            }
            elseif ($count_val_all == 3 || $count_val_all == 4)
            {
                if ($val_all[1] == 'b2b')
                {
                    if (is_numeric($val_all[2]))
                    {

                        $ignored_sku_list[] = $val;
                    }
                }
            }
        }
    }
}

$NM01_b2b_count_amazon_ec_inactive = $NM01_b2b_count_catalog_active - $NM01_b2b_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$NM01_b2b_count_catalog' ,`count_amazon_ec`='$NM01_b2b_count_amazon_ec' ,`count_catalog_active`='$NM01_b2b_count_catalog_active',`count_amazon_ec_active`='$NM01_b2b_count_amazon_ec_active',`count_amazon_ec_inactive`='$NM01_b2b_count_amazon_ec_inactive' WHERE `sku`='NM01_b2b' ";
$query = $greatval_test_sachin->query($sql);
if (!$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()) . "','Unable to update values in amazon ec audit for NM01_b2b','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for NM01_b2b', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}
$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku NM01_b2b','$alert_id')");

// LM01_b2b SKU
$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'LM01%' AND `sku` LIKE '%b2b%'";
$query = $econ_conn->query($sql);
echo $LM01_b2b_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'LM01%' AND `sku` LIKE '%b2b%' AND qt>0";
$query = $econ_conn->query($sql);
$lm01_b2b_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $lm01_b2b_4p_active[] = trim($row['sku']);

}

$lm01_b2b_4p_active = array_diff($lm01_b2b_4p_active, $rest_lm01b2b);
$data = array_merge($data, $lm01_b2b_4p_active);
$LM01_b2b_count_catalog_active = count($lm01_b2b_4p_active);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'LM01%' AND `sku` LIKE '%b2b%'";
$query = $greatval_test_sachin->query($sql);
$LM01_b2b_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'LM01%' AND `sku` LIKE '%b2b%' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$LM01_b2b_count_amazon_ec_active = 0;
$lm01_b2b_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $lm01_b2b_amz_active[] = trim($row['sku']);
}
$lm01_b2b_amz_active = array_diff($lm01_b2b_amz_active, $rest_lm01b2b);

if (count($lm01_b2b_amz_active) == 0)
{
    $LM01_b2b_count_amazon_ec_active = 0;
}
else
{
    foreach ($lm01_b2b_amz_active as $val)
    {
        if (in_array($val, $lm01_b2b_4p_active))
        {
            $LM01_b2b_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            else if ($count_val_all == 2)
            {
                if ($val_all[1] == 'b2b')
                {
                    $disp[] = $val;
                }
            }
            elseif ($count_val_all == 3 || $count_val_all == 4)
            {
                if ($val_all[1] == 'b2b')
                {
                    if (is_numeric($val_all[2]))
                    {

                        $ignored_sku_list[] = $val;
                    }
                }
            }
        }
    }
}

$LM01_b2b_count_amazon_ec_inactive = $LM01_b2b_count_catalog_active - $LM01_b2b_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$LM01_b2b_count_catalog' ,`count_amazon_ec`='$LM01_b2b_count_amazon_ec' ,`count_catalog_active`='$LM01_b2b_count_catalog_active',`count_amazon_ec_active`='$LM01_b2b_count_amazon_ec_active',`count_amazon_ec_inactive`='$LM01_b2b_count_amazon_ec_inactive' WHERE `sku`='LM01_b2b' ";
$query = $greatval_test_sachin->query($sql);
if (!$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()) . "','Unable to update values in amazon ec audit for LM01_b2b','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for LM01_b2b', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}

$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku LM01_b2b','$alert_id')");

// WI01_b2b SKU
$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'WI01%' AND `sku` LIKE '%b2b%'";
$query = $econ_conn->query($sql);
echo $WI01_b2b_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'WI01%' AND `sku` LIKE '%b2b%' AND qt>0";
$query = $econ_conn->query($sql);

$wi01_b2b_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $wi01_b2b_4p_active[] = trim($row['sku']);

}
$wi01_b2b_4p_active = array_diff($wi01_b2b_4p_active, $rest_wi01b2b);
$data = array_merge($data, $wi01_b2b_4p_active);
$WI01_b2b_count_catalog_active = count($wi01_b2b_4p_active);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'WI01%' AND `sku` LIKE '%b2b%'";
$query = $greatval_test_sachin->query($sql);
$WI01_b2b_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'WI01%' AND `sku` LIKE '%b2b%' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$WI01_b2b_count_amazon_ec_active = 0;
$wi01_b2b_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $wi01_b2b_amz_active[] = trim($row['sku']);
}
$wi01_b2b_amz_active = array_diff($wi01_b2b_amz_active, $rest_wi01b2b);

if (count($wi01_b2b_amz_active) == 0)
{
    $WI01_b2b_count_amazon_ec_active = 0;
}
else
{
    foreach ($wi01_b2b_amz_active as $val)
    {
        if (in_array($val, $wi01_b2b_4p_active))
        {
            $WI01_b2b_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            else if ($count_val_all == 2)
            {
                if ($val_all[1] == 'b2b')
                {
                    $disp[] = $val;
                }
            }
            elseif ($count_val_all == 3 || $count_val_all == 4)
            {
                if ($val_all[1] == 'b2b')
                {
                    if (is_numeric($val_all[2]))
                    {

                        $ignored_sku_list[] = $val;
                    }
                }
            }
        }
    }
}

$WI01_b2b_count_amazon_ec_inactive = $WI01_b2b_count_catalog_active - $WI01_b2b_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$WI01_b2b_count_catalog' ,`count_amazon_ec`='$WI01_b2b_count_amazon_ec' ,`count_catalog_active`='$WI01_b2b_count_catalog_active',`count_amazon_ec_active`='$WI01_b2b_count_amazon_ec_active',`count_amazon_ec_inactive`='$WI01_b2b_count_amazon_ec_inactive' WHERE `sku`='WI01_b2b' ";
$query = $greatval_test_sachin->query($sql);
if (!$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()) . "','Unable to update values in amazon ec audit for WI01_b2b','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for WI01_b2b', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}
$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku WI01_b2b','$alert_id')");

//ES01_B2B
$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'ES01%' AND `sku` LIKE '%b2b%'";
$query = $econ_conn->query($sql);
echo $ES01_b2b_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'ES01%' AND `sku` LIKE '%b2b%' AND qt>0";
$query = $econ_conn->query($sql);

$es01_b2b_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $es01_b2b_4p_active[] = trim($row['sku']);

}
$es01_b2b_4p_active = array_diff($es01_b2b_4p_active, $rest_es01b2b);
$data = array_merge($data, $es01_b2b_4p_active);
$ES01_b2b_count_catalog_active = count($es01_b2b_4p_active);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'ES01%' AND `sku` LIKE '%b2b%'";
$query = $greatval_test_sachin->query($sql);
$ES01_b2b_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'ES01%' AND `sku` LIKE '%b2b%' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$ES01_b2b_count_amazon_ec_active = 0;
$es01_b2b_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $es01_b2b_amz_active[] = trim($row['sku']);

}
$es01_b2b_amz_active = array_diff($es01_b2b_amz_active, $rest_es01b2b);

if (count($es01_b2b_amz_active) == 0)
{
    $ES01_b2b_count_amazon_ec_active = 0;
}
else
{
    foreach ($es01_b2b_amz_active as $val)
    {
        if (in_array($val, $es01_b2b_4p_active))
        {
            $ES01_b2b_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            else if ($count_val_all == 2)
            {
                if ($val_all[1] == 'b2b')
                {
                    $disp[] = $val;
                }
            }
            elseif ($count_val_all == 3 || $count_val_all == 4)
            {
                if ($val_all[1] == 'b2b')
                {
                    if (is_numeric($val_all[2]))
                    {

                        $ignored_sku_list[] = $val;
                    }
                }
            }
        }
    }
}

$ES01_b2b_count_amazon_ec_inactive = $ES01_b2b_count_catalog_active - $ES01_b2b_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$ES01_b2b_count_catalog' ,`count_amazon_ec`='$ES01_b2b_count_amazon_ec' ,`count_catalog_active`='$ES01_b2b_count_catalog_active',`count_amazon_ec_active`='$ES01_b2b_count_amazon_ec_active',`count_amazon_ec_inactive`='$ES01_b2b_count_amazon_ec_inactive' WHERE `sku`='ES01_b2b' ";
$query = $greatval_test_sachin->query($sql);
if (!$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()) . "','Unable to update values in amazon ec audit for ES01_b2b','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for ES01_b2b', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}
$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku ES01_b2b','$alert_id')");

//MR01_B2B
$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'MR01%' AND `sku` LIKE '%b2b%'";
$query = $econ_conn->query($sql);
echo $MR01_b2b_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `4p_inventory`  WHERE `sku` LIKE 'MR01%' AND `sku` LIKE '%b2b%' AND qt>0";
$query = $econ_conn->query($sql);

$mr01_b2b_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $mr01_b2b_4p_active[] = trim($row['sku']);

}
$mr01_b2b_4p_active = array_diff($mr01_b2b_4p_active, $rest_mr01b2b);
$data = array_merge($data, $mr01_b2b_4p_active);
$MR01_b2b_count_catalog_active = count($mr01_b2b_4p_active);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'MR01%' AND `sku` LIKE '%b2b%'";
$query = $greatval_test_sachin->query($sql);
$MR01_b2b_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku FROM Amazon_ec_Listing WHERE `sku` LIKE 'MR01%' AND `sku` LIKE '%b2b%' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$MR01_b2b_count_amazon_ec_active = 0;
$mr01_b2b_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $mr01_b2b_amz_active[] = trim($row['sku']);
}
$mr01_b2b_amz_active = array_diff($mr01_b2b_amz_active, $rest_mr01b2b);

if (count($mr01_b2b_amz_active) == 0)
{
    $MR01_b2b_count_amazon_ec_active = 0;
}
else
{
    foreach ($mr01_b2b_amz_active as $val)
    {
        if (in_array($val, $mr01_b2b_4p_active))
        {
            $MR01_b2b_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            else if ($count_val_all == 2)
            {
                if ($val_all[1] == 'b2b')
                {
                    $disp[] = $val;
                }
            }
            elseif ($count_val_all == 3 || $count_val_all == 4)
            {
                if ($val_all[1] == 'b2b')
                {
                    if (is_numeric($val_all[2]))
                    {

                        $ignored_sku_list[] = $val;
                    }
                }
            }
        }
    }
}

$MR01_b2b_count_amazon_ec_inactive = $MR01_b2b_count_catalog_active - $MR01_b2b_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$MR01_b2b_count_catalog' ,`count_amazon_ec`='$MR01_b2b_count_amazon_ec' ,`count_catalog_active`='$MR01_b2b_count_catalog_active',`count_amazon_ec_active`='$MR01_b2b_count_amazon_ec_active',`count_amazon_ec_inactive`='$MR01_b2b_count_amazon_ec_inactive' WHERE `sku`='MR01_b2b' ";
$query = $greatval_test_sachin->query($sql);

if (!$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()) . "','Unable to update values in amazon ec audit for MR01_b2b','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for MR01_b2b', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}

$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku MR01_b2b','$alert_id')");

// NM01 Notion SKU
$sql = "SELECT sku FROM `sd300_notinv_inv`  WHERE `sku` LIKE 'NM01%'  ";
$query = $notion->query($sql);
echo $NM01_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `sd300_notinv_inv`  WHERE `sku` LIKE 'NM01%' AND amz_inv_current_count>0";
$query = $notion->query($sql);

$nm01_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $nm01_4p_active[] = trim($row['sku']);

}

$nm01_4p_active = array_diff($nm01_4p_active, $rest_nm01);

$data = array_merge($data, $nm01_4p_active);
$NM01_count_catalog_active = count($nm01_4p_active);

$sql = "SELECT sku,qty FROM Amazon_ec_Listing WHERE `sku` LIKE 'NM01%' AND `sku` NOT LIKE '%b2b%'";
$query = $greatval_test_sachin->query($sql);
$NM01_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku,qty FROM Amazon_ec_Listing WHERE `sku` LIKE 'NM01%' AND `sku` NOT LIKE '%b2b%' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$NM01_count_amazon_ec_active = 0;
$nm01_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $nm01_amz_active[] = trim($row['sku']);
}
$nm01_amz_active = array_diff($nm01_amz_active, $rest_nm01);

if (count($nm01_amz_active) == 0)
{
    $NM01_count_amazon_ec_active = 0;
}
else
{
    foreach ($nm01_amz_active as $val)
    {
        if (in_array($val, $nm01_4p_active))
        {
            $NM01_count_amazon_ec_active++;

        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            elseif ($count_val_all == 2 || $count_val_all == 3 || $count_val_all == 4)
            {

                $ignored_sku_list[] = $val;
            }
        }
    }
}

$NM01_count_amazon_ec_inactive = $NM01_count_catalog_active - $NM01_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$NM01_count_catalog' ,`count_amazon_ec`='$NM01_count_amazon_ec' ,`count_catalog_active`='$NM01_count_catalog_active',`count_amazon_ec_active`='$NM01_count_amazon_ec_active',`count_amazon_ec_inactive`='$NM01_count_amazon_ec_inactive' WHERE `sku`='NM01' ";

$query = $greatval_test_sachin->query($sql);

if (!$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()) . "','Unable to update values in amazon ec audit for NM01','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for NM01', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}

$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku NM01','$alert_id')");

//LM01
$sql = "SELECT sku FROM `sd300_notinv_inv`  WHERE `sku` LIKE 'LM01%'  ";
$query = $notion->query($sql);
echo $LM01_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `sd300_notinv_inv`  WHERE `sku` LIKE 'LM01%' AND current_count>0";
$query = $notion->query($sql);

$lm01_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $lm01_4p_active[] = trim(strtoupper($row['sku']));

}

$lm01_4p_active = array_diff($lm01_4p_active, $rest_lm01);
$data = array_merge($data, $lm01_4p_active);
$LM01_count_catalog_active = count($lm01_4p_active);

$sql = "SELECT sku,qty FROM Amazon_ec_Listing WHERE `sku` LIKE 'LM01%' AND `sku` NOT LIKE '%b2b%'";
$query = $greatval_test_sachin->query($sql);
$LM01_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku,qty FROM Amazon_ec_Listing WHERE `sku` LIKE 'LM01%' AND `sku` NOT LIKE '%b2b%' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$LM01_count_amazon_ec_active = 0;
$lm01_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $lm01_amz_active[] = trim(strtoupper($row['sku']));
}

$lm01_amz_active = array_diff($lm01_amz_active, $rest_lm01);

if (count($lm01_amz_active) == 0)
{
    $LM01_count_amazon_ec_active = 0;

}
else
{
    foreach ($lm01_amz_active as $val)
    {
        if (in_array($val, $lm01_4p_active))
        {
            $LM01_count_amazon_ec_active++;

        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            elseif ($count_val_all == 2 || $count_val_all == 3 || $count_val_all == 4)
            {

                $ignored_sku_list[] = $val;
            }
        }
    }
}

$LM01_count_amazon_ec_inactive = $LM01_count_catalog_active - $LM01_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$LM01_count_catalog' ,`count_amazon_ec`='$LM01_count_amazon_ec' ,`count_catalog_active`='$LM01_count_catalog_active',`count_amazon_ec_active`='$LM01_count_amazon_ec_active',`count_amazon_ec_inactive`='$LM01_count_amazon_ec_inactive' WHERE `sku`='LM01' ";

$query = $greatval_test_sachin->query($sql);

if (!$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()) . "','Unable to update values in amazon ec audit for LM01','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for LM01', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}

$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku LM01','$alert_id')");

//KW01 SKU
$sql = "SELECT sku FROM `sd300_notinv_inv`  WHERE `sku` LIKE 'KW01%'  ";
$query = $notion->query($sql);
echo $KW01_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `sd300_notinv_inv`  WHERE `sku` LIKE 'KW01%' AND amz_inv_current_count>0";
$query = $notion->query($sql);

$kw01_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $kw01_4p_active[] = trim($row['sku']);

}

$kw01_4p_active = array_diff($kw01_4p_active, $rest_kw01);
$data = array_merge($data, $kw01_4p_active);
$KW01_count_catalog_active = count($kw01_4p_active);

$sql = "SELECT sku,qty FROM Amazon_ec_Listing WHERE `sku` LIKE 'KW01%' AND `sku` NOT LIKE '%_b2b'";
$query = $greatval_test_sachin->query($sql);
$KW01_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku,qty FROM Amazon_ec_Listing WHERE `sku` LIKE 'KW01%' AND `sku` NOT LIKE '%_b2b' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$KW01_count_amazon_ec_active = 0;
$kw01_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $kw01_amz_active[] = $row['sku'];
}
$kw01_amz_active = array_diff($kw01_amz_active, $rest_kw01);

if (count($kw01_amz_active) == 0)
{
    $KW01_count_amazon_ec_active = 0;
}
else
{
    foreach ($kw01_amz_active as $val)
    {
        if (in_array($val, $kw01_4p_active))
        {
            $KW01_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            elseif ($count_val_all == 2 || $count_val_all == 3 || $count_val_all == 4)
            {

                $ignored_sku_list[] = $val;
            }
        }
    }
}
$KW01_count_amazon_ec_inactive = $KW01_count_catalog_active - $KW01_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$KW01_count_catalog' ,`count_amazon_ec`='$KW01_count_amazon_ec' ,`count_catalog_active`='$KW01_count_catalog_active',`count_amazon_ec_active`='$KW01_count_amazon_ec_active',`count_amazon_ec_inactive`='$KW01_count_amazon_ec_inactive' WHERE `sku`='KW01' ";

$query = $greatval_test_sachin->query($sql);

if (!$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()) . "','Unable to update values in amazon ec audit for KW01','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for KW01', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}
$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku KW01','$alert_id')");

//ES01 sku
$sql = "SELECT sku FROM `sd300_notinv_inv`  WHERE `sku` LIKE 'ES01%'  ";
$query = $notion->query($sql);
echo $ES01_count_catalog = mysqli_num_rows($query);

$sql = "SELECT sku FROM `sd300_notinv_inv`  WHERE `sku` LIKE 'ES01%' AND amz_inv_current_count>0";
$query = $notion->query($sql);

$es01_4p_active = array();
while ($row = $query->fetch_assoc())
{
    $es01_4p_active[] = $row['sku'];

}

$es01_4p_active = array_diff($es01_4p_active, $rest_es01);
$data = array_merge($data, $es01_4p_active);
$ES01_count_catalog_active = count($es01_4p_active);

$sql = "SELECT sku,qty FROM Amazon_ec_Listing WHERE `sku` LIKE 'ES01%' AND `sku` NOT LIKE '%_b2b'";
$query = $greatval_test_sachin->query($sql);
$ES01_count_amazon_ec = mysqli_num_rows($query);

$sql = "SELECT sku,qty FROM Amazon_ec_Listing WHERE `sku` LIKE 'ES01%' AND `sku` NOT LIKE '%_b2b' AND qty>0";
$query = $greatval_test_sachin->query($sql);
$ES01_count_amazon_ec_active = 0;
$es01_amz_active = array();
while ($row = $query->fetch_assoc())
{
    $es01_amz_active[] = trim($row['sku']);
}
$es01_amz_active = array_diff($es01_amz_active, $rest_es01);

if (count($es01_amz_active) == 0)
{
    $ES01_count_amazon_ec_active = 0;
}
else
{
    foreach ($es01_amz_active as $val)
    {
        if (in_array($val, $es01_4p_active))
        {
            $ES01_count_amazon_ec_active++;
        }
        else
        {
            $val_all = explode("_", $val);
            $count_val_all = count($val_all);
            if ($count_val_all == 1)
            {
                $disp[] = $val;
            }
            elseif ($count_val_all == 2 || $count_val_all == 3 || $count_val_all == 4)
            {

                $ignored_sku_list[] = $val;
            }
        }
    }
}

$ES01_count_amazon_ec_inactive = $ES01_count_catalog_active - $ES01_count_amazon_ec_active;
echo $sql = "UPDATE `Amazon_ec_audit` SET `count_catalog`='$ES01_count_catalog' ,`count_amazon_ec`='$ES01_count_amazon_ec' ,`count_catalog_active`='$ES01_count_catalog_active',`count_amazon_ec_active`='$ES01_count_amazon_ec_active',`count_amazon_ec_inactive`='$ES01_count_amazon_ec_inactive' WHERE `sku`='ES01' ";

$query = $greatval_test_sachin->query($sql);

if (!$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()) . "','Unable to update values in amazon ec audit for ES01','$alert_id')"); 
    $q1 = "UPDATE alert_success_error_dashboard SET error_msg = 'Unable to update values in amazon ec audit for ES01', action = 'Issue' WHERE id = '$alert_id'";
    $err_query = mysqli_query($notify_conn, $q1);
    exit;
}

$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()) . "','Updated active count ,inactive count,count_catalog in Amazon_ec_audit for sku ES01','$alert_id')");

$sku_not_listed_sds = array();
$amz_ec_list = array();
$sql = "SELECT sku from Amazon_ec_Listing";
$query = $greatval_test_sachin->query($sql);
while ($row = $query->fetch_assoc())
{
    $ss_sku = $row['sku'];
    $prefix_lm01 = substr($ss_sku, 0, 4);
    if ($prefix_lm01 == 'LM01')
    {
        $amz_ec_list[] = strtoupper($ss_sku);
    }
    else
    {
        $amz_ec_list[] = $ss_sku;
    }
}

foreach ($data as $single)
{
    $sku = $single;
    $orig_sku = $sku;

    $prefix_lm = substr($sku, 0, 4);
    if ($prefix_lm == 'LM01')
    {
        $sku = strtoupper($sku);
    }
    if (!in_array($sku, $amz_ec_list))
    {
        $sku_not_listed_sds[] = $orig_sku;
    }
}
$file1 = 'amazon_EC_sku_not_listed' . date("Y-m-d_h_i_s") . '.csv';
$file2 = 'amazon_ec_problematic' . date("Y-m-d_h_i_s") . '.csv';
$file3 = 'amazon_ec_ignored_sku_list' . date("Y-m-d_h_i_s") . '.csv';
$output = fopen($file1, 'w');
foreach ($sku_not_listed_sds as $single_sku)
{
    $sku = $single_sku;
    fputcsv($output, array(
        $sku
    ));
}
$new_file_disp = fopen($file2, 'w');
foreach ($disp as $value)
{
    $sku = $value;
    fputcsv($new_file_disp, array(
        $sku
    ));
}
$ignored_list_file = fopen($file3, 'w');
foreach ($ignored_sku_list as $newvalue)
{
    $sku = $newvalue;
    fputcsv($ignored_list_file, array(
        $sku
    ));
}

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

echo $p . ' opt: ' . $foldername;
$command1 = escapeshellcmd("python ../../../python_scripts/upload_file_onedrive.py $p $foldername $file1");
$output1 = shell_exec($command1);
echo $output1;

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

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

if(trim($output1) == 'done' && trim($output2) == 'done' && 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 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 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);
}


$link_to_onedrive = $foldername . '/' . $file1 . ', ' . $foldername . '/' . $file2 . ', ' . $foldername . '/' . $file3;

$q2 = "UPDATE alert_success_error_dashboard SET success_msg = 'Successfully genereated amazon ec files', action = 'No Action Needed', link_to_onedrive = '$link_to_onedrive', error_msg = '' WHERE id = '$alert_id'";
$succ_query = mysqli_query($notify_conn, $q2);

$new_array = array();
$sql = "SELECT `source`,`sku`,`supplier`,`count_catalog`,`count_catalog_active`,`count_amazon_ec_active`,`count_amazon_ec_inactive` FROM `Amazon_ec_audit`";
$query = $greatval_test_sachin->query($sql);
while ($row = $query->fetch_assoc())
{
    $new_array[] = $row;
}

$last_array = array();
$sql = "SELECT SUM(`count_catalog`),SUM(`count_catalog_active`),  SUM(`count_amazon_ec_active`),SUM(`count_amazon_ec_inactive`) FROM `Amazon_ec_audit`";
$query = $greatval_test_sachin->query($sql);
while ($row = $query->fetch_assoc())
{
    $last_array[] = $row;
}

function build_table($new_array, $last_array)
{

    $html = '<style>
        table, th, td {
      border: 1px solid black;
      text-align:center;
    }

    table {
      width: 100%;
        border-collapse: collapse;
        color: black;
        font-size: 13px;
        font-weight: 600;
    }
    </style><table>';

    $html .= '<tr>';
    foreach ($new_array[0] as $key => $value)
    {
        $html .= '<th >' . htmlspecialchars($key) . '</th>';
    }
    $html .= '</tr>';

    foreach ($new_array as $key => $value)
    {
        $html .= '<tr>';
        foreach ($value as $key2 => $value2)
        {
            $html .= '<td>' . htmlspecialchars($value2) . '</td>';
        }
        $html .= '</tr>';
    }
    foreach ($last_array as $key => $value)
    {
        $html .= '<tr><td>Total</td><td></td><td></td>';
        foreach ($value as $key2 => $value2)
        {

            $html .= '<td>' . htmlspecialchars($value2) . '</td>';
        }
        $html .= '</tr>';
    }

    $html .= '</table>';
    return $html;
}

$table = build_table($new_array, $last_array);

unlink($file1);
unlink($file2);
unlink($file3);

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

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

$mail = new PHPMailer(true);
try
{

    $mail->isSMTP();
    $mail->SMTPAuth = true;
    $mail->Host = EMAIL_HOST;
    $mail->Username = EMAIL_USERNAME;
    $mail->Password = EMAIL_PASSWORD;
    $mail->SMTPSecure = 'tls';
    $mail->Port = EMAIL_PORT;
    $email_set_from = EMAIL_SET_FROM;
    $mail->setFrom($email_set_from, 'Mailer');

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

    //Recipients
    $mail->setFrom('reports@fcsus.com', 'Mailer');

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

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

    //Content
    $mail_link1 = "https://strategicisus-my.sharepoint.com/:x:/r/personal/reports_fcsus_com1/Documents/Marketplace_audit_Files/Amazon_ec_audit/".$file1;
    $mail_link2 = "https://strategicisus-my.sharepoint.com/:x:/r/personal/reports_fcsus_com1/Documents/Marketplace_audit_Files/Amazon_ec_audit/".$file2;
    $mail_link3 = "https://strategicisus-my.sharepoint.com/:x:/r/personal/reports_fcsus_com1/Documents/Marketplace_audit_Files/Amazon_ec_audit/".$file3;
    $mail_content = "";
    $mail->isHTML(true);
    $mail->Subject = 'Amazon EC Audit';
    $mail->Body = "<p>Hi Team </p>
    <p>PFA</p>$table
    <p>File attachment links are: </p>
    <p><a href='$mail_link1'>$file1</a> </p><br/>
    <p><a href='$mail_link2'>$file2</a> </p><br/>
    <p><a href='$mail_link3'>$file3</a> </p><br/>

    ";

    $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 has been sent to the respective users', '$alert_id')");

}
catch(Exception $e)
{
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;

}
$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()) . "','Updated execution log and Amazon EC Audit script has been completed','$alert_id')");
' . htmlspecialchars($key) . '
' . htmlspecialchars($value2) . '
Total			' . htmlspecialchars($value2) . '