<?php

/* * ***********************  Change History  *****************************
  10/26/2006 - Paulo kuong
  - File Created

  12/07/2006 - Paulo Kuong
  -Fix pagging bug
 * ********************************************************************** */

require_once(__DIR__ . '/../../../zf_loader.php');
$wrapperObj = getPDOQueryWrapper();

$no_url = isset($no_url) ? $no_url : false;

$query = 'SELECT SQL_CALC_FOUND_ROWS v.vendor_id, v.name, v.company, v.is_owned, v.label_identifier, v.login, IF(n.note_date is null, v.last_update, 
        IF(v.last_update > n.note_date, v.last_update, n.note_date)) as last_updated, v.owner, v.priority, v.status, country.name as country,
        c.contact_email, country.name as country, CONCAT_WS(" ", ou.f_name, ou.l_name) AS assigned_to,
        if(CONCAT_WS(" ", ou2.f_name, ou2.l_name) is null or CONCAT_WS(" ", ou2.f_name, ou2.l_name) = "", CONCAT_WS(" ", ou3.f_name, ou3.l_name), CONCAT_WS(" ", ou2.f_name, ou2.l_name)) AS orchrep,
        c.address_city, c.address_zip, vc.master, co2.name AS contact_country, s.name AS state, c.alt_email, c.contact_phone, c.address_street, c.address2, v.website, v.referral, v.est_total_releases,
        v.est_total_tracks, genre, v.label_summary, c.alt_email, c.contact_phone_2, v.checks, v.tax_form_received,
        if(vcon.orchrep_name is null or vcon.orchrep_name = "", vcon2.orchrep_name,vcon.orchrep_name) as orchrep_name
    FROM vendor v
    INNER JOIN vend_contact vc ON v.vendor_id = vc.vendor_id
    LEFT JOIN contact c ON vc.contact_id = c.contact_id
    LEFT JOIN country ON v.country = country.id
    LEFT JOIN country co2 ON c.orchard_country = co2.id
    LEFT JOIN orchard_state s ON c.address_state = s.id
    LEFT JOIN genre on v.primary_genre = genre.genre_id
    LEFT JOIN vendor_notes vn ON v.vendor_id = vn.vendor_id
    LEFT JOIN note n ON n.note_id = vn.note_id
    LEFT JOIN (SELECT vc1.vendor_id, vc1.cont_start, vc1.cont_end, vc1.contract_type, vc1.release_term, vc1.id, vc1.orchrep_name
        FROM vendor_contract vc1
        WHERE vc1.cont_start IN (
            SELECT MAX(vc2.cont_start)
            FROM vendor_contract vc2
            WHERE vc2.vendor_id = vc1.vendor_id
            )
        ) vcon on vcon.vendor_id = v.vendor_id
    LEFT JOIN (SELECT vc3.vendor_id, vc3.cont_start, vc3.cont_end, vc3.vendor_proposed_term_id, vc3.orchrep_name
        FROM vendor_proposed_term vc3
        WHERE vc3.cont_start IN (
            SELECT MAX(vc4.cont_start)
            FROM vendor_proposed_term vc4
            WHERE vc4.vendor_id = vc3.vendor_id
            )
        ) vcon2 on vcon2.vendor_id = v.vendor_id
    LEFT JOIN orchadmin_users ou ON ou.id = v.assigned_to
    LEFT JOIN orchadmin_users ou2 ON ou2.id = vcon.orchrep_name
    LEFT JOIN orchadmin_users ou3 ON ou3.id = vcon2.orchrep_name';

$query .= ' WHERE vc.master = "Y" ';

$data_params = [];

if (!empty($venname)) {
    $query .= ' AND v.name like ?';
    $data_params[] = '%' . $venname . '%';
}

if (!empty($vendor_company)) {
    $query .= ' AND v.company like ?';
    $data_params[] = '%' . $vendor_company . '%';
}

if (!empty($is_owned)) {
    $query .= ' AND v.is_owned = ?';
    $data_params[] = $is_owned;
}

if (!empty($label_identifier)) {
    $query .= ' AND v.label_identifier = ?';
    $data_params[] = $label_identifier;
}

if (!empty($status)) {
    $query .= ' AND v.status IN (' . implode(',', array_fill(0, count($status), '?')) . ')';
    foreach ($status as $val) {
        $data_params[] = $val;
    }
}

if ($status_change_date_from or $status_change_date_to) {
    if ($status_change_date_from) {
        $query .= ' AND DATE(n.note_date) >= ?';
        $data_params[] = $status_change_date_from;
    }
    if ($status_change_date_to) {
        $query .= ' AND DATE(n.note_date) <= ?';
        $data_params[] = $status_change_date_to;
    }
    if ($status_changed_to) {
        $status_changed_to_text = 'Label status changed from % to ' . $status_changed_to . '.';
        $query .= ' AND n.note_text LIKE ?';
        $data_params[] = $status_changed_to_text;
    } else {
        $query .= ' AND n.note_text LIKE "Label status changed from%"';
    }
}

if (!empty($priority)) {
    $query .= ' AND v.priority = ?';
    $data_params[] = $priority;
}
if ($ca_rep) {
    $query .= ' AND vcon.orchrep_name = ?';
    $data_params[] = $ca_rep;
}
if ($assigned_to) {
    $query .= ' AND v.assigned_to = ?';
    $data_params[] = $assigned_to;
}
//check what company user belongs to and then search accordingly.
if (isOwnerInSession('orchard')) {
    if ($owner != "-1" && !empty($owner)) {
        $query .= ' AND v.owner IN (' . implode(',', array_fill(0, count($owner), '?')) . ')';
        foreach ($owner as $val) {
            $data_params[] = $val;
        }
    }
} else {
    $query .= ownerFilterSubQuery(' AND v.owner');
    $data_params = addSessionOwnerDataParam($data_params);
}

if ($country != "-1" && !empty($country) && is_array($country) && sizeof($country) > 0) {
    $query .= ' AND v.country IN (' . implode(',', array_fill(0, count($country), '?')) . ')';
    foreach ($country as $val) {
        $data_params[] = $val;
    }
}

if (!empty($from_date_created)) {
    $query .= ' AND v.date_created >= ?';
    $data_params[] = $from_date_created;
}

if (!empty($to_date_created)) {
    $query .= ' AND v.date_created <= ?';
    $data_params[] = $to_date_created;
}

if (!empty($city)) {
    $query .= ' AND c.address_city like ?';
    $data_params[] = '%' . $city . '%';
}

if ($state != "-1" && !empty($state)) {
    $query .= ' AND c.address_state = ?';
    $data_params[] = $state;
}

if (!empty($email)) {
    $query .= ' AND (c.contact_email like ? OR c.alt_email like ?)';
    $data_params[] = '%' . $email . '%';
    $data_params[] = '%' . $email . '%';
}

if (!empty($contphone)) {
    $query .= ' AND (c.contact_phone = ? OR c.contact_fax = ? OR c.contact_cell = ? OR c.contact_phone_2 = ?)';
    $data_params[] = $contphone;
    $data_params[] = $contphone;
    $data_params[] = $contphone;
    $data_params[] = $contphone;
}

if (!empty($checks)) {
    $query .= ' AND v.checks like ?';
    $data_params[] = '%' . $checks . '%';
}

if ($contact_country != "-1" && !empty($contact_country) && is_numeric($contact_country)) {
    $query .= ' AND c.orchard_country = ?';
    $data_params[] = $contact_country;
}

if (!empty($other_state)) {
    $query .= ' AND c.address_other_state like ?';
    $data_params[] = '%' . $other_state . '%';
}

if (!empty($tax_form_received)) {
    $query .= ' and v.tax_form_received = ?';
    $data_params[] = $tax_form_received;
}

$query .= " AND v.vendor_id = vc.vendor_id AND vc.contact_id = c.contact_id";
$query .= " GROUP BY vendor_id ";
$query .= " ORDER BY ";

switch ($sort_by) {
    case 'id':
        $query .= 'v.vendor_id';
        break;
    case "name":
        $query .="v.name";
        break;
    case "company":
        $query .="v.company";
        break;
    case "is_owned":
        $query .="v.is_owned";
        break;
    case "label_type":
        $query .="v.label_identifier";
        break;
    case "login":
        $query .="v.login";
        break;
    case "prio":
        $query .="priority";
        break;
    case "orchrep":
        $query .="orchrep";
        break;
    case "email":
        $query .="c.contact_email";
        break;
    case "owner":
        $query .="v.owner";
        break;
    case "country":
        $query .="country.name";
        break;
    case "contact_country":
        $query .="co2.name";
        break;
    case 'user_name':
        $query .= 'assigned_to';
        break;
    case 'status':
        $query .= 'status';
        break;
    case 'last_update':
        $query .= 'last_updated';
        break;
    case 'date_created':
        $query .= 'v.date_created';
        break;
    case 'address_street':
        $query .= 'c.address_street';
        break;
    case 'contact_phone':
        $query .= 'c.contact_phone';
        break;
    case 'website':
        $query .= 'v.website';
        break;
    case 'referral':
        $query .= 'v.referral';
        break;
    case 'est_total_releases':
        $query .= 'v.est_total_releases';
        break;
    case 'est_total_tracks':
        $query .= 'v.est_total_tracks';
        break;
    case 'primary_genre':
        $query .= 'genre';
        break;
    case 'payment_type':
        $query .= 'v.payment_type';
        break;
    case 'checks':
        $query .= 'v.checks';
        break;
    case 'tax_form_received':
        $query .= 'v.tax_form_received';
        break;
    case 'wire_info':
        $query .= 'v.wire_info';
        break;
    case 'label_summary':
        $query .= 'v.label_summary';
        break;
    case 'contact_phone_2':
        $query .='c.contact_phone_2';
        break;
    default:
        $query .="v.company";
        $sort_by = 'company';
        break;
}
$query .= ($sort_desc) ? ' DESC' : '';

if (!$no_url) {
    $query .= ' LIMIT ' . intval(($page - 1) * $pageRecord) . ', ' . intval($pageRecord);
}

$result = $wrapperObj->executeQuery($query, $data_params);
if ($result === false) {
    show_pdo_error($query, $wrapperObj->getLastStatement());
}

$sql = 'SELECT FOUND_ROWS() AS total';
$total_count = $wrapperObj->executeQuery($sql, []);
if ($total_count === false) {
    show_pdo_error($sql, $wrapperObj->getLastStatement());
}
$total_count_row = $total_count[0];
$total = $total_count_row['total'];
$pageNum = ceil($total / $pageRecord);

$headers = [
    ['title' => 'Label ID', 'field' => 'id'],
    ['title' => 'Contact Name', 'field' => 'name'],
    ['title' => 'Label/Company', 'field' => 'company'],
    ['title' => 'Owned and Operated', 'field' => 'is_owned'],
    ['title' => 'Label Type', 'field' => 'label_type'],
    ['title' => 'PM/ALR Assignment', 'field' => 'user_name'],
    ['title' => 'Initial Priority', 'field' => 'prio', 'noUrlTitle' => 'Label Priority'],
    ['title' => 'Label Country', 'field' => 'country'],
    ['title' => 'Email', 'field' => 'email'],
    ['title' => 'Closer', 'field' => 'orchrep'],
    ['title' => 'Address', 'field' => 'address_street'],
    ['title' => 'Contact\'s country ', 'field' => 'country'],
    ['title' => 'Owner', 'field' => 'owner'],
    ['title' => 'Status', 'field' => 'status'],
    ['title' => 'Contact Phone', 'field' => 'contact_phone'],
    ['title' => 'Checks Payable', 'field' => 'checks'],
    ['title' => 'Tax Form Received', 'field' => 'tax_form_received'],
    ['title' => 'Website', 'field' => 'website'],
    ['title' => 'EST Total Releases', 'field' => 'est_total_releases'],
    ['title' => 'EST Total Tracks', 'field' => 'est_total_tracks'],
    ['title' => 'Primary Genre', 'field' => 'primary_genre'],
    ['title' => 'Label Summary', 'field' => 'label_summary'],
    ['title' => 'Last Updated', 'field' => 'last_update']
];

$varTable = '<table class="data" ' . ($no_url ? " border=1" : "") . '><tr>';
foreach ($headers as $header) {
    $show = (!isset($header['show']) || $header['show']);
    if ($show) {
        $noUrlTitle = (isset($header['noUrlTitle'])) ? $header['noUrlTitle'] : $header['title'];
        $varTable .= '<th class="center">' . (($no_url) ? $noUrlTitle : sortColumnHeader($header['title'], $header['field'], $url)) . '</th>';
    }
}
$varTable .= '</tr>';

if (count($result)) {
    $bgi = 0;
    foreach ($result as $row) {
        $row_class = ( ++$bgi & 1) ? '' : 'alt';
        $varTable .= '<tr class="' . (($no_url) ? "" : $row_class) . '">
                        <td>' . (($no_url) ? $row["vendor_id"] : '<a href="/cont_mgmt/view_vendor.php?vendor_id=' . $row["vendor_id"] . '" title="View Label">' . ($row['status'] == 'deletion' ? '<del>' . $row["vendor_id"] . '</del>' : $row['vendor_id']) . '</a>') . '</td>
                        <td>' . $row["name"] . '</td>
                        <td>' . (($no_url) ? $row["company"] : '<a href="/cont_mgmt/view_vendor.php?vendor_id=' . $row["vendor_id"] . '" title="View Label">' . ($row['status'] == 'deletion' ? '<del>' . $row["company"] . '</del>' : $row['company']) . '</a>') . '</td>
                                    <td>' . $row["is_owned"] . '</td>
                                    <td>' . $row["label_identifier"] . '</td>
                        <td>' . $row['assigned_to'] . '</td>
                        <td align="center">' . $row["priority"] . '</td>
                        <td>' . $row["country"] . '</td>
                        <td>' . (($no_url) ? ($row["contact_email"] . (($row["alt_email"]) ? (", " . $row["alt_email"]) : "")) : '<a href="mailto:' . $row["contact_email"] . '" title="Send Email">' . $row["contact_email"] . '</a>' . (($row["alt_email"]) ? ("<br>Alt. <a href=\"mailto:" . $row["alt_email"] . "\">" . $row["alt_email"] . "</a>") : "")) . '</td>
                        <td>' . $row["orchrep"] . '</td>
                        <td>';
        if ($contact_row["address_street"]) {
            $varTable .= $row["address_street"] . '<br />';
        }
        if ($contact_row["address2"]) {
            $varTable .= $row["address2"] . '<br />';
        }
        $varTable .= $row["address_city"] . ', ' . $row["state"] . ' ' . $row["address_zip"] . '</td>
                        <td>' . $row["contact_country"] . '</td>
                        <td>' . $row["owner"] . '</td>
                        <td>' . ucwords($row['status']) . '</td>
                        <td>' . $row["contact_phone"] . (($row["contact_phone_2"]) ? (", " . $row["contact_phone_2"]) : "") . '</td>
                        <td>' . $row['checks'] . '</td>
                        <td>' . $row['tax_form_received'] . '</td>
                        <td>' . $row["website"] . '</td>
                        <td>' . $row["est_total_releases"] . '</td>
                        <td>' . $row["est_total_tracks"] . '</td>
                        <td>' . $row["genre"] . '</td>
                        <td>' . $row["label_summary"] . '</td>
                        <td>' . (($row["last_updated"]) ? format_date($row["last_updated"], true) : '') . '</td>
                                </tr>';
    }
} else {
    $varTable .='<tr><td colspan="' .count($headers). '">No labels found.</td></tr>';
}
$varTable .= '</table>';