executeQuery($query, []); if ($result === false) { show_pdo_error($query, $wrapperObj->getLastStatement()); } foreach ($result as $row) { $restricted_upcs[] = $row['upc']; } $query = 'select t.id from track t inner join track_restriction tr on tr.upc = t.upc and tr.cd = t.cd and tr.track_id = t.track_id where tr.restriction_type = "territory" and territory_or_dms_id = 1'; $result = $wrapperObj->executeQuery($query, []); if ($result === false) { show_pdo_error($query, $wrapperObj->getLastStatement()); } foreach ($result as $row) { $restricted_track_ids[] = $row['id']; } $query = "select vendor_id from vendor_contract where find_in_set(1, territory_carve_out) <> 0 and (cont_start <= CURDATE() and (cont_end >= CURDATE() OR cont_end = '0000-00-00' OR cont_end IS NULL)) group by vendor_id"; $dataParams = []; $result = $wrapperObj->executeQuery($query, $dataParams); if ($result === false) { show_pdo_error($query, $wrapperObj->getLastStatement()); } foreach ($result as $row) { $restricted_vendor_ids[] = $row['vendor_id']; } } $dataParams = []; $select = "SELECT SQL_CALC_FOUND_ROWS v.vendor_id, v.company, a.artist_id, a.name as artist_name, r.upc,r.display_upc, r.release_name, DATE_FORMAT(r.ingestion_completed, '%m-%d-%Y') as date_added, r.deletions , t.id as track_unique_id, t.track_name, t.cd, t.track_id, t.isrc, t.length_minute, t.length_seconds, group_concat(ta.name) as track_artists, t.publisher, t.writer "; $from = "FROM vendor v "; $where = " WHERE 1 AND r.not_for_distribution != 'SMEAnalyticsDummy' "; switch ($sort_by){ case 'vendor_id': $order_by = 'vendor_id'; break; case 'company': $order_by = 'company'; break; case 'artist_name': $order_by = "a.name"; break; case 'release_name': $order_by = 'r.release_name'; break; case 'artist_id': $order_by = 'a.artist_id'; break; case 'upc': $order_by = 'r.upc'; break; case 'date_added': $order_by = 'r.ingestion_completed'; break; case 'deletion': $order_by = 'r.deletions'; break; case 'track_unique_id': $order_by = 't.id'; break; case 'isrc': $order_by = 't.isrc'; break; case 'track_name': $order_by = 't.track_name'; break; case 'cd': $order_by = 't.cd'; break; case 'track_id': $order_by = 't.track_id'; break; case 'length_minutes': $order_by = 't.length_minute'; break; case 'length_seconds': $order_by = 't.length_seconds'; case 'publisher': $order_by = 't.publisher'; break; case 'writer': $order_by = 't.writer'; break; default: $order_by = " artist_name, release_name, cd, track_id"; } $err_msg = ""; switch ($input_type) { case 'track_ids': $track_id_arr = explode("\n", $input_ids); if($oms_labels_only != "on" && (trim($input_ids) == "" || !is_array($track_id_arr) || sizeof($input_ids) <= 0)) { $err_msg .= "

You must either select OMS Only Labels or provide a list of Track IDs.

"; } break; case 'upcs': $upc_arr = explode("\n", $input_ids); if($oms_labels_only != "on" && (trim($input_ids) == "" || !is_array($upc_arr) || sizeof($input_ids) <= 0)) { $err_msg .= "

You must either select OMS Only Labels or provide a list of UPCs.

"; } break; case 'isrcs': $isrc_arr = explode("\n", $input_ids); if($oms_labels_only != "on" && (trim($input_ids) == "" || !is_array($isrc_arr) || sizeof($input_ids) <= 0)) { $err_msg .= "

You must either select OMS Only Labels or provide a list of ISRCs.

"; } break; case 'artist_ids': $artist_id_arr = explode("\n", $input_ids); if($oms_labels_only != "on" && (trim($input_ids) == "" || !is_array($artist_id_arr) || sizeof($input_ids) <= 0)) { $err_msg .= "

You must either select OMS Only Labels or provide a list of Artist IDs.

"; } break; default: $vendor_id_arr = explode("\n", $input_ids); if($oms_labels_only != "on" && (trim($input_ids) == "" || !is_array($vendor_id_arr) || sizeof($input_ids) <= 0)) { $err_msg .= "

You must either select OMS Only Labels or provide a list of labels.

"; } } if($oms_labels_only == "on") { $from .= " INNER JOIN (select vc.vendor_id,vc.id,vc.cont_start,vc.cont_end from vendor_contract vc where (vc.cont_start <= CURDATE() or (vc.cont_end >= CURDATE() OR vc.cont_end = '0000-00-00' OR vc.cont_end IS NULL) ) AND oms_type != 'none' AND oms_type IS NOT NULL GROUP BY vendor_id ) vc ON vc.vendor_id = v.vendor_id"; } $from .= " INNER JOIN artist_info a force index (vendor_id) ON a.vendor_id = v.vendor_id INNER JOIN releases r ON a.artist_id = r.artist_id INNER JOIN track t ON t.upc = r.upc LEFT JOIN track_artist ta on ta.track_id = t.id "; if($sales_territory == "us_only") { //Used tmp table to handle large number of restricted_upcs which do not work inside IN clause $temp_table = 'TEMP_restricted_upcs_' . $_SESSION['S_USERID']; $sql = 'DROP TABLE IF EXISTS ' . $temp_table; if ($wrapperObj->executeQuery($sql, []) === false) { show_pdo_error($sql, $wrapperObj->getLastStatement()); } $sql = 'CREATE TEMPORARY TABLE ' . $temp_table . ' ( restricted_upc_id bigint(20) NOT NULL, PRIMARY KEY (`restricted_upc_id`) )'; if ($wrapperObj->executeQuery($sql, []) === false) { show_pdo_error($sql, $wrapperObj->getLastStatement()); } $restricted_upcs = array_unique($restricted_upcs); $upc_ids = array_chunk($restricted_upcs, 1000); foreach ($upc_ids as $ids) { $insertDataParams = []; $sql = 'INSERT INTO ' . $temp_table . ' VALUES ' . implode(',', array_fill(0, count($ids), '(?)')); foreach ($ids as $id) { $insertDataParams[] = $id; } if ($wrapperObj->executeQuery($sql, $insertDataParams, QueryWrapper::RETURN_INSERT_ID) === false) { show_pdo_error($sql, $wrapperObj->getLastStatement()); } } $where .= ' AND r.upc not in (SELECT restricted_upc_id FROM ' . $temp_table . ' ) AND t.id not in (' . implode(',', array_fill(0, count($restricted_track_ids), '?')) . ') AND v.vendor_id not in (' . implode(',', array_fill(0, count($restricted_vendor_ids), '?')) . ')'; foreach ($restricted_track_ids as $val) { $dataParams[] = $val; } foreach ($restricted_vendor_ids as $val) { $dataParams[] = $val; } } if (trim($input_ids) != "") { switch ($input_type) { case 'track_ids': if(is_array($track_id_arr) && sizeof($input_ids) > 0) { $tmp_str = ""; foreach($track_id_arr as $track_id) { $track_id = trim($track_id); if($track_id != "") { if (is_numeric($track_id)) { $tmp_str .= ', ?'; $dataParams[] = $track_id; } else { $err_msg .= "

Invalid Track ID provided.

"; break; } } } $tmp_str = trim($tmp_str, ","); $where .= " AND t.id IN ($tmp_str) "; } break; case 'upcs': if(is_array($upc_arr) && sizeof($input_ids) > 0) { $tmp_str = ""; foreach($upc_arr as $upc) { $upc = trim($upc); if($upc != "") { if (checkUPC($upc)) { $tmp_str .= ', ?'; $dataParams[] = $upc; } else { $err_msg .= "

Invalid UPC provided.

"; break; } } } $tmp_str = trim($tmp_str, ","); $where .= " AND r.upc IN ($tmp_str) "; } break; case 'isrcs': if(is_array($isrc_arr) && sizeof($input_ids) > 0) { $tmp_str = ""; foreach($isrc_arr as $isrc) { $isrc = trim($isrc); if($isrc != "") { $tmp_str .= ', ?'; $dataParams[] = $isrc; } } $tmp_str = trim($tmp_str, ","); $where .= " AND t.isrc IN ($tmp_str) "; } break; case 'artist_ids': if(is_array($artist_id_arr) && sizeof($input_ids) > 0) { $tmp_str = ""; foreach($artist_id_arr as $artist_id) { $artist_id = trim($artist_id); if($artist_id != "") { if (is_numeric($artist_id)) { $tmp_str .= ', ?'; $dataParams[] = $artist_id; } else { $err_msg .= "

Invalid Artist ID provided.

"; break; } } } $tmp_str = trim($tmp_str, ","); $where .= " AND a.artist_id IN ($tmp_str) "; } break; default: if(is_array($vendor_id_arr) && sizeof($input_ids) > 0) { $tmp_str = ""; foreach($vendor_id_arr as $vendor_id) { $vendor_id = trim($vendor_id); if($vendor_id != "") { if (is_numeric($vendor_id)) { $tmp_str .= ', ?'; $dataParams[] = $vendor_id; } else { $err_msg .= "

Invalid Vendor ID provided.

"; break; } } } $tmp_str = trim($tmp_str, ","); $where .= " AND v.vendor_id IN ($tmp_str) "; } } } if(trim($from_date_added) != "") { $where .= ' AND r.ingestion_completed >= ?'; $dataParams[] = $from_date_added; } if(trim($to_date_added) != "") { $where .= ' AND r.ingestion_completed <= ?'; $dataParams[] = $to_date_added; } $where .= " GROUP BY t.id "; $query = $select . $from . $where; $query .= ' ORDER BY ' . $order_by . ($sort_desc ? ' DESC' : ''); if (!$no_url) { $query .= ' LIMIT ?, ?'; $dataParams[] = (($page - 1) * $pageRecord); $dataParams[] = $pageRecord; } if($err_msg == "") { $result = $wrapperObj->executeQuery($query, $dataParams); if ($result === false) { show_pdo_error($query, $wrapperObj->getLastStatement()); } $dataParams = []; $sql = 'SELECT FOUND_ROWS() AS total'; $total_count = $wrapperObj->executeQuery($sql, $dataParams); 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); $varTable = ""; $varTable .= ""; $i = 0; $rci = 0; if (count($result) > 0) { $rci = 0; foreach ($result as $row) { $row_class = (++$rci & 1) ? '' : 'alt'; $varTable .= ""; } $varTable .= "

".($no_url ? "Label ID" : sortColumnHeader('Label ID', 'vendor_id', $url))."	".($no_url ? "Label" : sortColumnHeader('Label', 'company', $url))."	".($no_url ? "Artist ID" : sortColumnHeader('Artist ID', 'artist_id', $url))."	".($no_url ? "Artist Name" : sortColumnHeader('Artist Name', 'artist_name', $url))."	Track Artist(s)	".($no_url ? "UPC" : sortColumnHeader('UPC', 'upc', $url))."	".($no_url ? "Release Name" : sortColumnHeader('Release Name', 'release_name', $url))."	".($no_url ? "Date Added" : sortColumnHeader('Date Added', 'date_added', $url))."	".($no_url ? "Deletion" : sortColumnHeader('Deletion', 'deletion', $url))."	".($no_url ? "Track Unique ID" : sortColumnHeader('Track Unique ID', 'track_unique_id', $url))."	".($no_url ? "ISRC" : sortColumnHeader('ISRC', 'isrc', $url))."	".($no_url ? "Track Name" : sortColumnHeader('Track Name', 'track_name', $url))."	".($no_url ? "CD" : sortColumnHeader('CD', 'cd', $url))."	".($no_url ? "Track #" : sortColumnHeader('Track #', 'track_id', $url))."	".($no_url ? "Length Minutes" : sortColumnHeader('Length Minutes', 'length_minutes', $url))."	".($no_url ? "Length Seconds" : sortColumnHeader('Length Seconds', 'length_seconds', $url))."	".($no_url ? "Publisher" : sortColumnHeader('Publisher', 'publisher', $url))."	".($no_url ? "Songwriter" : sortColumnHeader('Songwriter', 'writer', $url))."
{$row['vendor_id']}	".($no_url?$row['company']:"{$row['company']}")."	{$row['artist_id']}	".($no_url?$row['artist_name']:"{$row['artist_name']}")."	".$row['track_artists']."	" . ($no_url ? '"'.$row['display_upc'].'"' : $row['display_upc']) ."	".($no_url?$row['release_name']:"{$row['release_name']}")."	{$row['date_added']}	{$row['deletions']}	{$row['track_unique_id']}	{$row['isrc']}	{$row['track_name']}	{$row['cd']}	{$row['track_id']}	{$row['length_minute']}	{$row['length_seconds']}	{$row['publisher']}	{$row['writer']}

"; } else { $varTable .= "

No catalogs found.

"; } } ?>