Untitled

public function index()
{
    set_time_limit(0);
    $agency_mesh_shop_pos = [];
    $agency_ca_shop_pos = [];
    $agency_both_shop_pos = [];
    $emails = [];
    $emails_mesh = [];
    $emails_ca = [];

    $au_shops = $this->getFile('C:\Users\appgo\PhpstormProjects\au_shops.csv');

    foreach ($au_shops as $row) {
        switch ($row[11]) {
            case 'Only Mesh Work In':
                $agency_mesh_shop_pos[] = $row[6];
                break;
            case 'CA Only work in':
                $agency_ca_shop_pos[] = $row[6];
                break;
            case 'Mesh and CA Work in':
                $agency_both_shop_pos[] = $row[6];
                break;
        }
    }

    $mesh_id_shops = $this->getIdShops($agency_mesh_shop_pos);
    $ca_id_shops = $this->getIdShops($agency_ca_shop_pos);
    $both_id_shops = $this->getIdShops($agency_both_shop_pos);

    $resellers = $this->getFile('C:\Users\appgo\PhpstormProjects\resellers.csv');

    foreach ($resellers as $row2) {
        $emails[] = $row2[0];
        switch ($row2[1]) {
            case 'Mesh':
                $emails_mesh[] = $row2[0];
                break;
            case 'CA':
                $emails_ca[] = $row2[0];
                break;
        }
    }

    $mesh_id_reseller = $this->getIdResellers($emails_mesh);
    $ca_id_reseller = $this->getIdResellers($emails_ca);
    $both_id_reseller = $this->getIdResellers($emails);

    $mesh_SQL = $this->getSQL($mesh_id_reseller, $mesh_id_shops);
    $ca_SQL = $this->getSQL($ca_id_reseller, $ca_id_shops);
    $both_SQL = $this->getSQL($both_id_reseller, $both_id_shops);

    $sql = array_unique(array_merge($mesh_SQL, $ca_SQL, $both_SQL));

    file_put_contents('C:\Users\appgo\PhpstormProjects\update_resellers_AU.sql', $sql);
}

public function getIdShops($shop_pos): array
{
    return Shop::with([])
        ->whereIn('shop_pos', $shop_pos)
        ->pluck('id_shop')
        ->toArray();
}

public function getIdResellers($emails): array
{
    return Reseller::with([])
        ->whereIn('reseller_email', $emails)
        ->pluck('id_reseller')
        ->toArray();
}

public function getResellerShops($id_reseller): array
{
    return Reseller::with([])
        ->where('id_reseller', $id_reseller)
        ->pluck('reseller_shops')
        ->flatten()
        ->toArray();
}

public function getSQL($id_resellers, $id_shops): array
{
    $sql = [];
    foreach ($id_resellers as $id_reseller) {
        $current_shops = $this->getResellerShops($id_reseller);
        $new_shops = array_unique(array_map(function ($item) {
            return (string)$item;
        }, array_merge($current_shops, $id_shops)));
        $new_shops_to_JSON = json_encode(array_values($new_shops));

        $sql[] = "UPDATE `reseller` SET `reseller_shops` = '$new_shops_to_JSON' WHERE `id_reseller` = $id_reseller; \r\n";
    }
    return $sql;
}

public function getFile($filename): array
{
    $f = fopen($filename, 'r');
    $data = [];
    while (($line = fgetcsv($f)) !== false) {
        $data[] = $line;
    }
    fclose($f);

    return $data;
}