<?php
require_once(__DIR__ . '/../../../zf_loader.php');
use Orchard\Mysqli\QueryData;
use Orchard\Mysqli\Wrapper as MysqliWrapper;
$wrapperObj = new MysqliWrapper($mysqli);

$orchadmin_user_owner = $_SESSION['S_OA_OWNER'];
switch ($choice) {
    case "no_split_entry":
        $report_query = "SELECT SQL_CALC_FOUND_ROWS v.vendor_id, v.name, v.company, v.owner, vc.digital_split, vc.physical_split FROM vendor v LEFT JOIN vendor_contract vc ON v.vendor_id = vc.vendor_id WHERE (v.owner = \"odd\" OR v.owner = \"emd\") AND (vc.digital_split IS NULL or vc.physical_split IS NULL)";
        break;
    case "deleted_titles":
        $report_query = "SELECT SQL_CALC_FOUND_ROWS v.vendor_id, v.name, sum(if(r.deletions=\"Y\", 1,0)) as total_deleted, sum(if(r.deletions=\"N\", 1,0)) as total_not_deleted FROM vendor v, artist_info a, releases r WHERE r.artist_id= a.artist_id AND a.vendor_id = v.vendor_ID GROUP BY v.vendor_id HAVING total_deleted > 0 AND total_not_deleted > 0 ";
        break;
    case "missing_ssn":
        $fieldTin = 'v.encrypted_tin';
        $report_query = "SELECT SQL_CALC_FOUND_ROWS v.vendor_id, v.name, oc.name as country FROM vendor v, vend_contact vc, contact c LEFT JOIN country oc ON oc.id = c.orchard_country WHERE (" . $fieldTin . " IS NULL or " . $fieldTin . " = \"\") and c.orchard_country = 1 AnD v.vendor_id = vc.vendor_id AND vc.contact_id = c.contact_id AND vc.master = 'Y'";
        break;
    case "incomplete_address":
        $report_query = " SELECT SQL_CALC_FOUND_ROWS v.vendor_id, v.name, c.contact_phone, c.address_street, c.address_city, c.address_zip, v.checks, co.name AS country, os.name AS state FROM vendor v, vend_contact vc, contact c LEFT JOIN country co ON co.id = c.orchard_country LEFT JOIN orchard_state os ON os.id = c.address_state WHERE (c.contact_phone = \"\" OR c.contact_phone = \"\" OR c.address_street = \"\" OR c.address_city=\"\" OR c.address_zip=\"\"  OR v.checks=\"\" OR c.orchard_country= \"\" OR c.address_state = \"\") AND v.vendor_id = vc.vendor_id AND vc.contact_id = c.contact_id AND vc.master = 'Y' ";
        break;
    case "invalid_email":
        $report_query = " SELECT SQL_CALC_FOUND_ROWS v.vendor_id, v.name, c.contact_email FROM vendor v inner join vend_contact vco on v.vendor_id = vco.vendor_id inner join contact c on c.contact_id = vco.contact_id WHERE c.contact_email not regexp '[a-zA-Z0-9._-]+@+[a-zA-z0-9._-]+[.a-zA-Z]{2,3}' ";
        break;
    case 'expiring_contract':
        $report_query = 'SELECT SQL_CALC_FOUND_ROWS v.vendor_id, v.name, v.company, v.owner, IF(vcon.contract_type = "per_release_term",if(vcon.cont_start = "0000-00-00" or vcon.release_term <= 0,"0000-00-00",vcon.cont_start + interval vcon.release_term month),vcon.cont_end) as expiration_date FROM vendor v INNER JOIN (SELECT vc1.vendor_id, vc1.cont_start, vc1.cont_end, vc1.contract_type, vc1.release_term 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 WHERE v.status = "signed" GROUP BY v.vendor_id HAVING expiration_date != "0000-00-00" and expiration_date is not null and expiration_date <= curdate() + interval 270 day and expiration_date >= curdate()';
        break;
    case 'expired_contract':
        $report_query = 'SELECT SQL_CALC_FOUND_ROWS v.vendor_id, v.name, v.company, v.owner, IF(vcon.contract_type = "per_release_term",if(vcon.cont_start = "0000-00-00" or vcon.release_term <= 0,"0000-00-00",vcon.cont_start + interval vcon.release_term month),vcon.cont_end) as expiration_date FROM vendor v INNER JOIN (SELECT vc1.vendor_id, vc1.cont_start, vc1.cont_end, vc1.contract_type, vc1.release_term 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 WHERE v.status = "signed" GROUP BY v.vendor_id HAVING expiration_date != "0000-00-00" and expiration_date is not null and expiration_date < curdate()';
        break;
}
$report_query .= " ORDER BY ";
switch ($sort_by) {
    case "vendor_id":
        $report_query .= "v.vendor_id";
        break;
    case "vendor_name":
        $report_query .= "v.name";
        break;
    case "company":
        $report_query .= "v.company";
        break;
    case "owner":
        $report_query .= "v.owner";
        break;
    case "dig_split":
        $report_query .= "vc.digital_split";
        break;
    case 'phys_split':
        $report_query .= 'vc.physical_split';
        break;
    case "total_deleted":
        $report_query .= "total_deleted";
        break;
    case "total_not_deleted":
        $report_query .= "total_not_deleted";
        break;
    case "address_street":
        $report_query .= "address_street";
        break;
    case "address_city":
        $report_query .= "address_city";
        break;
    case "address_zip":
        $report_query .= "address_zip";
        break;
    case "state":
        $report_query .= "state";
        break;
    case "country":
        $report_query .= "country";
        break;
    case "checks":
        $report_query .= "checks";
        break;
    case "contact_phone":
        $report_query .= "contact_phone";
        break;
    case "contact_email":
        $report_query .= "c.contact_email";
        break;
    default:
        $report_query .= "v.name";
        $sort_by = " vendor_name";
        $sort_desc = 1;
}
$page = (isset($page) && is_numeric($page)) ? $page : 1;
$pageRecord = (isset($pageRecord) && is_numeric($pageRecord)) ? $pageRecord : 50;

$dataParams = new QueryData();
$report_query .= $sort_desc ? ' DESC' : '';

if (!$no_url) {
    $report_query .= ' LIMIT ?, ?';
    $dataParams->addDataParam('i', (($page - 1) * $pageRecord));
    $dataParams->addDataParam('i', $pageRecord);
}
$report_result = $wrapperObj->executeQuery($report_query, $dataParams);
if ($report_result === false) {
    showierror($report_query);
}
if (!$no_url) {
    $sql = 'SELECT FOUND_ROWS() AS total';
    $dataParams = new QueryData();
    $total_count = $wrapperObj->executeQuery($sql, $dataParams);
    if ($total_count === false) {
        showierror($sql);
    }
    $total_count_row = $total_count[0];
    $total = $total_count_row['total'];
    $pageNum = ceil($total / $pageRecord);
}

$varTable .= '<table' . ($no_url ? ' border=1' : ' class="data"') . '>
                <tr>';
if ($choice == "no_split_entry") {
    $varTable .= '<th>' . ($no_url ? 'Label ID' : sortColumnHeader('Label ID', 'vendor_id', $url)) . '</th>
                  <th>' . ($no_url ? 'Contact Name' : sortColumnHeader('Contact Name', 'vendor_name', $url)) . '</th>
		  <th>' . ($no_url ? 'Company' : sortColumnHeader('Company', 'company', $url)) . '</th>
		  <th>' . ($no_url ? 'Owner' : sortColumnHeader('Owner', 'owner', $url)) . '</th>
		  <th>' . ($no_url ? 'Digital Split' : sortColumnHeader('Digital Split', 'dig_split', $url)) . '</th>
		  <th>' . ($no_url ? 'Physical Split' : sortColumnHeader('Physical Split', 'phys_split', $url)) . '</th>';
} elseif ($choice == "deleted_titles") {
    $varTable .= '<th>' . ($no_url ? 'Label ID' : sortColumnHeader('Label ID', 'vendor_id', $url)) . '</th>
                  <th>' . ($no_url ? 'Contact Name' : sortColumnHeader('Contact Name', 'vendor_name', $url)) . '</th>
		  <th>' . ($no_url ? '# of Deleted Releases' : sortColumnHeader('# of Deleted Releases', 'total_deleted', $url)) . '</th>
		  <th>' . ($no_url ? '# of Not Deleted Releases' : sortColumnHeader('# of Not Deleted Releases', 'total_not_deleted', $url)) . '</th>';
} elseif ($choice == "missing_ssn") {
    $varTable .= '<th>' . ($no_url ? 'Label ID' : sortColumnHeader('Label ID', 'vendor_id', $url)) . '</th>
		  <th>' . ($no_url ? 'Contact Name' : sortColumnHeader('Contact Name', 'vendor_name', $url)) . '</th>
		  <th>' . ($no_url ? 'Country' : sortColumnHeader('Country', 'country', $url)) . '</th>';
} elseif ($choice == "incomplete_address") {
    $varTable .= '<th>' . ($no_url ? 'Label ID' : sortColumnHeader('Label ID', 'vendor_id', $url)) . '</th>
		  <th>' . ($no_url ? 'Contact Name' : sortColumnHeader('Contact Name', 'vendor_name', $url)) . '</th>
		  <th>' . ($no_url ? 'Address 1' : sortColumnHeader('Address 1', 'address_street', $url)) . '</th>
		  <th>' . ($no_url ? 'City' : sortColumnHeader('City', 'address_city', $url)) . '</th>
		  <th>' . ($no_url ? 'Zip' : sortColumnHeader('Zip', 'address_zip', $url)) . '</th>
		  <th>' . ($no_url ? 'State' : sortColumnHeader('State', 'state', $url)) . '</th>
		  <th>' . ($no_url ? 'Country' : sortColumnHeader('Country', 'country', $url)) . '</th>
		  <th>' . ($no_url ? 'Check Payable Name' : sortColumnHeader('Check Payable Name', 'checks', $url)) . '</th>
		  <th>' . ($no_url ? 'Phone' : sortColumnHeader('Phone', 'contact_phone', $url)) . '</th>';
} elseif ($choice == "invalid_email") {
    $varTable .= '<th>' . ($no_url ? 'Label ID' : sortColumnHeader('Label ID', 'vendor_id', $url)) . '</th>
                  <th>' . ($no_url ? 'Contact Name' : sortColumnHeader('Contact Name', 'vendor_name', $url)) . '</th>
		  <th>' . ($no_url ? 'Email Address' : sortColumnHeader('Email Address', 'contact_email', $url)) . '</th>';
} elseif ($choice == "expiring_contract" or $choice == 'expired_contract') {
    $varTable .= '<th>' . ($no_url ? 'Label ID' : sortColumnHeader('Label ID', 'vendor_id', $url)) . '</th>
		  <th>' . ($no_url ? 'Contact Name' : sortColumnHeader('Contact Name', 'vendor_name', $url)) . '</th>
		  <th>' . ($no_url ? 'Company' : sortColumnHeader('Company', 'company', $url)) . '</th>
		  <th>' . ($no_url ? 'Owner' : sortColumnHeader('Owner', 'owner', $url)) . '</th>
		  <th>' . ($no_url ? 'Contract Expiration' : sortColumnHeader('Contract Expiration', 'cont_end', $url)) . '</th>';
}
$varTable .= '</tr>';
if (count($report_result)) {
    $rci = 0;
    foreach ($report_result as $report_row) {
        $row_class = ( ++$rci & 1) ? '' : 'alt';
        $varTable .= '<tr class="' . $row_class . '">';
        if ($choice == "no_split_entry") {
            $varTable .= '<td>' . ($no_url ? $report_row['vendor_id'] : '<a href="/cont_mgmt/view_vendor.php?vendor_id=' . $report_row["vendor_id"] . '">' . $report_row["vendor_id"] . '</a>') . '</td>
			  <td align="left">' . $report_row["name"] . '</td>
			  <td align="left">' . $report_row["company"] . '</td>
			  <td>' . $report_row["owner"] . '</td>
			  <td>' . $report_row["digital_split"] . '</td>
			  <td>' . $report_row["physical_split"] . '</td>';
        } elseif ($choice == "deleted_titles") {
            $varTable .= '<td>' . ($no_url ? $report_row['vendor_id'] : '<a href="/cont_mgmt/view_vendor.php?vendor_id=' . $report_row["vendor_id"] . '">' . $report_row["vendor_id"] . '</a>') . '</td>
			  <td align="left">' . $report_row["name"] . '</td>
			  <td align="left">' . $report_row["total_deleted"] . '</td>
			  <td align="left">' . $report_row["total_not_deleted"] . '</td>';
        } elseif ($choice == "missing_ssn") {
            $varTable .= '<td>' . ($no_url ? $report_row['vendor_id'] : '<a href="/cont_mgmt/view_vendor.php?vendor_id=' . $report_row["vendor_id"] . '">' . $report_row["vendor_id"] . '</a>') . '</td>
			  <td align="left">' . $report_row["name"] . '</td>
			  <td align="left">' . $report_row["country"] . '</td>';
        } elseif ($choice == "incomplete_address") {
            $varTable .= '<td>' . ($no_url ? $report_row['vendor_id'] : '<a href="/cont_mgmt/view_vendor.php?vendor_id=' . $report_row["vendor_id"] . '">' . $report_row["vendor_id"] . '</a>') . '</td>
			  <td align="left">' . $report_row["name"] . '</td>
			  <td align="left">' . $report_row["address_street"] . '</td>
			  <td align="left">' . $report_row["address_city"] . '</td>
			  <td align="left">' . $report_row["address_zip"] . '</td>
			  <td align="left">' . $report_row["state"] . '</td>
			  <td align="left">' . $report_row["country"] . '</td>
			  <td align="left">' . $report_row["checks"] . '</td>
			  <td align="left">' . $report_row["contact_phone"] . '</td>';
        } elseif ($choice == "invalid_email") {
            $varTable .= '<td>' . ($no_url ? $report_row['vendor_id'] : '<a href="/cont_mgmt/view_vendor.php?vendor_id=' . $report_row["vendor_id"] . '">' . $report_row["vendor_id"] . '</a>') . '</td>
			  <td align="left">' . $report_row["name"] . '</td>
			  <td align="left">' . $report_row["contact_email"] . '</td>';
        } elseif ($choice == "expiring_contract" or $choice == 'expired_contract') {
            $varTable .= '<td>' . ($no_url ? $report_row['vendor_id'] : '<a href="/cont_mgmt/view_vendor.php?vendor_id=' . $report_row["vendor_id"] . '">' . $report_row["vendor_id"] . '</a>') . '</td>
			  <td align="left">' . $report_row["name"] . '</td>
			  <td align="left">' . $report_row["company"] . '</td>
			  <td>' . $report_row["owner"] . '</td>
			  <td>' . format_date($report_row["expiration_date"]) . '</td>';
        }
        $varTable .= '</tr>';
    }
} else {
    $varTable .= '<tr><td colspan="9">No errors found.</td></tr>';
}
$varTable .= '</table>';