addDataParam('s', $from_date); $data_params->addDataParam('s', $to_date); $result = $wrapperObj->executeQuery($sql, $data_params); if ($result === false) showierror($sql); $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 .= ' ' . ($no_url ? 'Label/Company' : sortColumnHeader('Label/Company', 'label', $url)) . ' ' . ($no_url ? 'Owner' : sortColumnHeader('Owner', 'owner', $url)) . ' ' . ($no_url ? 'Artist' : sortColumnHeader('Artist', 'artist', $url)) . ' ' . ($no_url ? 'UPC' : sortColumnHeader('UPC', 'upc', $url)) . ' ' . ($no_url ? 'Release' : sortColumnHeader('Relase', 'release', $url)) . ' ' . ($no_url ? 'Total Tracks' : sortColumnHeader('Total Tracks', 'ttl_tracks', $url)) . ' ' . ($no_url ? 'Date Received' : sortColumnHeader('Date Received', 'date_rec', $url)) . ' ' . ($no_url ? 'Date Ripped' : sortColumnHeader('Date Ripped', 'date_rip', $url)) . ' ' . ($no_url ? 'Days Taken' : sortColumnHeader('Days Taken', 'days_taken', $url)) . ' '; $rci = 0; foreach ($result as $row) { $row_class = (++$rci & 1) ? '' : 'alt'; $varTable .= ' ' . ($no_url ? $row['vendor_name'] : '' . $row['vendor_name'] . '') . ' ' . $row['owner'] . ' ' . $row['artist_name'] . ' ' . $row['display_upc'] . ' ' . $row['release_name'] . ' ' . $row['total_tracks'] . ' ' . format_date($row['date_received']) . ' ' . format_date($row['encoding_date']) . ' ' . $row['days_taken'] . ' '; } $varTable .= ' '; break; case 2: switch ($sort_by) { case 'owner': $order_by = 'v.owner'; break; case 'artist': $order_by = 'cd.artist_name'; break; case 'upc': $order_by = 'cd.upc'; break; case 'release': $order_by = 'cd.release_name'; break; case 'ttl_tracks': $order_by = 'cd.total_tracks'; break; case 'date_rec': $order_by = 'cd.date_received'; break; case 'date_upload': $order_by = 'r.ingestion_completed'; break; case 'days_taken': $order_by = 'days_taken'; break; default: case 'label': $sort_by = 'label'; $order_by = 'vendor_name'; } $sql = 'SELECT SQL_CALC_FOUND_ROWS cd.upc,ear.display_upc, v.vendor_id, IF((v.company IS NULL OR v.company = ""), v.name, v.company) AS vendor_name, v.owner, cd.artist_name, cd.release_name, cd.total_tracks, cd.encoding_date, cd.date_received, DATEDIFF(ear.ingestion_completed, cd.date_received) AS days_taken, ear.ingestion_completed FROM cd_receive cd INNER JOIN vendor v ON cd.vendor_id = v.vendor_id INNER JOIN releases ear ON ear.upc = cd.upc WHERE ear.upc > 0 AND ear.not_for_distribution != "SMEAnalyticsDummy" AND ear.ingestion_completed BETWEEN ? AND ? AND DATEDIFF(ear.ingestion_completed, cd.date_received) <= 25 ORDER BY ' . $order_by . ($sort_desc ? ' DESC' : ''); $sql .= $no_url ? '' : ' LIMIT ' . ($page-1) * $pageRecord . ', ' . $pageRecord; $data_params = new QueryData(); $data_params->addDataParam('s', $from_date); $data_params->addDataParam('s', $to_date); $result = $wrapperObj->executeQuery($sql, $data_params); if ($result === false) showierror($sql); $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 .= ' ' . ($no_url ? 'Label/Company' : sortColumnHeader('Label/Company', 'label', $url)) . ' ' . ($no_url ? 'Owner' : sortColumnHeader('Owner', 'owner', $url)) . ' ' . ($no_url ? 'Artist' : sortColumnHeader('Artist', 'artist', $url)) . ' ' . ($no_url ? 'UPC' : sortColumnHeader('UPC', 'upc', $url)) . ' ' . ($no_url ? 'Release' : sortColumnHeader('Relase', 'release', $url)) . ' ' . ($no_url ? 'Total Tracks' : sortColumnHeader('Total Tracks', 'ttl_tracks', $url)) . ' ' . ($no_url ? 'Date Received' : sortColumnHeader('Date Received', 'date_rec', $url)) . ' ' . ($no_url ? 'Date Uploaded' : sortColumnHeader('Date Uploaded', 'date_upload', $url)) . ' ' . ($no_url ? 'Days Taken' : sortColumnHeader('Days Taken', 'days_taken', $url)) . ' '; $rci = 0; foreach ($result as $row) { $row_class = (++$rci & 1) ? '' : 'alt'; $varTable .= ' ' . ($no_url ? $row['vendor_name'] : '' . $row['vendor_name'] . '') . ' ' . $row['owner'] . ' ' . $row['artist_name'] . ' ' . $row['display_upc'] . ' ' . $row['release_name'] . ' ' . $row['total_tracks'] . ' ' . format_date($row['date_received']) . ' ' . format_date($row['ingestion_completed']) . ' ' . $row['days_taken'] . ' '; } $varTable .= ' '; break; case 3: switch ($sort_by) { case 'owner': $order_by = 'v.owner'; break; case 'artist': $order_by = 'a.name'; break; case 'upc': $order_by = 'r.upc'; break; case 'release': $order_by = 'r.release_name'; break; case 'rel_date': $order_by = 'r.release_date'; break; case 'ingestion_completed': $order_by = 'r.ingestion_completed'; break; case 'date_upload': $order_by = 'ears.date'; break; case 'days_taken': $order_by = 'days_taken'; break; default: case 'label': $sort_by = 'label'; $order_by = 'vendor_name'; } $sql = 'SELECT SQL_CALC_FOUND_ROWS r.upc,r.display_upc, r.release_name, r.deletions, a.name, IF((v.company IS NULL OR v.company = ""), v.name, v.company) AS vendor_name, v.owner, v.vendor_id, a.artist_id, r.ingestion_completed, r.release_date, ears.date, DATEDIFF(IF(r.ingestion_completed IS NULL OR r.ingestion_completed = "0000-00-00", r.release_date, r.ingestion_completed), ears.date) AS days_taken FROM releases ear INNER JOIN releases r ON ear.upc = r.upc INNER JOIN release_status ears ON ear.release_id = ears.release_id INNER JOIN artist_info a ON a.artist_id = r.artist_id INNER JOIN vendor v ON v.vendor_id = a.vendor_id WHERE r.ingestion_completed BETWEEN ? AND ? AND ear.not_for_distribution != "SMEAnalyticsDummy" AND ears.status = "orchard_processing" ORDER BY ' . $order_by . ($sort_desc ? ' DESC' : ''); $sql .= $no_url ? '' : ' LIMIT ' . ($page-1) * $pageRecord . ', ' . $pageRecord; $data_params = new QueryData(); $data_params->addDataParam('s', $from_date); $data_params->addDataParam('s', $to_date); $result = $wrapperObj->executeQuery($sql, $data_params); if ($result === false) showierror($sql); $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 .= ' ' . ($no_url ? 'Label/Company' : sortColumnHeader('Label/Company', 'label', $url)) . ' ' . ($no_url ? 'Owner' : sortColumnHeader('Owner', 'owner', $url)) . ' ' . ($no_url ? 'Artist' : sortColumnHeader('Artist', 'artist', $url)) . ' ' . ($no_url ? 'UPC' : sortColumnHeader('UPC', 'upc', $url)) . ' ' . ($no_url ? 'Release' : sortColumnHeader('Relase', 'release', $url)) . ' ' . ($no_url ? 'Release Date' : sortColumnHeader('Release Date', 'rel_date', $url)) . ' ' . ($no_url ? 'Date Added' : sortColumnHeader('Date Added', 'ingestion_completed', $url)) . ' ' . ($no_url ? 'Date Uploaded' : sortColumnHeader('Date Uploaded', 'date_upload', $url)) . ' ' . ($no_url ? 'Days Taken' : sortColumnHeader('Days Taken', 'days_taken', $url)) . ' '; $rci = 0; foreach ($result as $row) { $row_class = (++$rci & 1) ? '' : 'alt'; $varTable .= ' ' . ($no_url ? $row['vendor_name'] : '' . $row['vendor_name'] . '') . ' ' . $row['owner'] . ' ' . ($no_url ? $row['name'] : '' . $row['name'] . '') . ' ' . ($no_url ? '"' . $row['display_upc'] . '"' : '' . ($row['deletions'] == 'Y' ? '' . $row['display_upc'] . '' : $row['display_upc']) . '') . ' ' . ($no_url ? $row['release_name'] : '' . ($row['deletions'] == 'Y' ? '' . $row['release_name'] . '' : $row['release_name']) . '') . ' ' . format_date($row['release_date']) . ' ' . format_date($row['ingestion_completed']) . ' ' . format_date($row['date']) . ' ' . $row['days_taken'] . ' '; } $varTable .= ' '; break; case 4: switch ($sort_by) { case 'owner': $order_by = 'v.owner'; break; case 'artist': $order_by = 'a.name'; break; case 'upc': $order_by = 'r.upc'; break; case 'release': $order_by = 'r.release_name'; break; case 'rel_date': $order_by = 'r.release_date'; break; case 'ingestion_completed': $order_by = 'r.ingestion_completed'; break; case 'date_delivered': $order_by = 'eo.encoder_delivery_date'; break; case 'days_taken': $order_by = 'days_taken'; break; default: case 'label': $sort_by = 'label'; $order_by = 'vendor_name'; } $sql = 'SELECT SQL_CALC_FOUND_ROWS r.upc,r.display_upc, r.release_name, r.ingestion_completed, r.release_date, r.deletions, a.name, IF((v.company IS NULL OR v.company = ""), v.name, v.company) AS vendor_name, v.owner, v.vendor_id, a.artist_id, MIN(eo.encoder_delivery_date) AS encoder_delivery_date, DATEDIFF(MIN(eo.encoder_delivery_date), IF(r.ingestion_completed IS NULL OR r.ingestion_completed = "0000-00-00", r.release_date, r.ingestion_completed)) AS days_taken FROM encoding_order eo INNER JOIN encoding_order_detail eod ON eo.encoding_order_id = eod.encoding_order_id INNER JOIN releases r ON r.upc = eod.upc INNER JOIN artist_info a ON a.artist_id = r.artist_id INNER JOIN vendor v ON v.vendor_id = a.vendor_id WHERE eo.encoder_delivery_date BETWEEN ? AND ? AND r.not_for_distribution != "SMEAnalyticsDummy" AND eo.type = "track" AND eo.order_status = "close" AND r.upc NOT IN ( SELECT eodx.upc FROM encoding_order eox INNER JOIN encoding_order_detail eodx ON eox.encoding_order_id = eodx.encoding_order_id WHERE eox.encoder_delivery_date < ? AND eox.type = "track" AND eox.order_status = "close" AND eodx.upc = eod.upc ) GROUP BY r.upc HAVING days_taken <= 25 ORDER BY ' . $order_by . ($sort_desc ? ' DESC' : ''); $sql .= $no_url ? '' : ' LIMIT ' . ($page-1) * $pageRecord . ', ' . $pageRecord; $data_params = new QueryData(); $data_params->addDataParam('s', $from_date); $data_params->addDataParam('s', $to_date); $data_params->addDataParam('s', $from_date); $result = $wrapperObj->executeQuery($sql, $data_params); if ($result === false) showierror($sql); $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 .= ' ' . ($no_url ? 'Label/Company' : sortColumnHeader('Label/Company', 'label', $url)) . ' ' . ($no_url ? 'Owner' : sortColumnHeader('Owner', 'owner', $url)) . ' ' . ($no_url ? 'Artist' : sortColumnHeader('Artist', 'artist', $url)) . ' ' . ($no_url ? 'UPC' : sortColumnHeader('UPC', 'upc', $url)) . ' ' . ($no_url ? 'Release' : sortColumnHeader('Relase', 'release', $url)) . ' ' . ($no_url ? 'Release Date' : sortColumnHeader('Release Date', 'rel_date', $url)) . ' ' . ($no_url ? 'Date Added' : sortColumnHeader('Date Added', 'ingestion_completed', $url)) . ' ' . ($no_url ? 'Date Delivered' : sortColumnHeader('Date Delivered', 'date_delivered', $url)) . ' ' . ($no_url ? 'Days Taken' : sortColumnHeader('Days Taken', 'days_taken', $url)) . ' '; $rci = 0; foreach ($result as $row) { $row_class = (++$rci & 1) ? '' : 'alt'; $varTable .= ' ' . ($no_url ? $row['vendor_name'] : '' . $row['vendor_name'] . '') . ' ' . $row['owner'] . ' ' . ($no_url ? $row['name'] : '' . $row['name'] . '') . ' ' . ($no_url ? '"' . $row['display_upc'] . '"' : '' . ($row['deletions'] == 'Y' ? '' . $row['display_upc'] . '' : $row['display_upc']) . '') . ' ' . ($no_url ? $row['release_name'] : '' . ($row['deletions'] == 'Y' ? '' . $row['release_name'] . '' : $row['release_name']) . '') . ' ' . format_date($row['release_date']) . ' ' . format_date($row['ingestion_completed']) . ' ' . format_date($row['encoder_delivery_date']) . ' ' . $row['days_taken'] . ' '; } $varTable .= ' '; break; case 5: switch ($sort_by) { case 'owner': $order_by = 'v.owner'; break; case 'artist': $order_by = 'a.name'; break; case 'upc': $order_by = 'r.upc'; break; case 'release': $order_by = 'r.release_name'; break; case 'rel_date': $order_by = 'r.release_date'; break; case 'ingestion_completed': $order_by = 'r.ingestion_completed'; break; case 'date_submitted': $order_by = 'encoder_delivery_date'; break; case 'date_delivered': $order_by = 'dms_delivery_date'; break; case 'days_taken': $order_by = 'days_taken'; break; default: case 'label': $sort_by = 'label'; $order_by = 'vendor_name'; } $sql = 'SELECT SQL_CALC_FOUND_ROWS r.upc,r.display_upc, r.release_name, r.ingestion_completed, r.release_date, r.deletions, a.name, IF((v.company IS NULL OR v.company = ""), v.name, v.company) AS vendor_name, v.owner, v.vendor_id, a.artist_id, MIN(dd.date_delivered) AS dms_delivery_date, MAX(eo.encoder_delivery_date) AS encoder_delivery_date, DATEDIFF(MIN(dd.date_delivered), MAX(eo.encoder_delivery_date)) AS days_taken FROM encoding_order eo INNER JOIN encoding_order_detail eod ON eo.encoding_order_id = eod.encoding_order_id INNER JOIN releases r ON eod.upc = r.upc INNER JOIN delivery_history dd ON dd.upc = r.upc INNER JOIN artist_info a ON a.artist_id = r.artist_id INNER JOIN vendor v ON v.vendor_id = a.vendor_id WHERE eo.type = "track" AND eo.order_status = "close" AND eo.encoder_id = dd.encoder_id AND eo.encoder_id IN (1,9) AND eo.encoder_delivery_date < dd.date_delivered AND dd.date_delivered BETWEEN ? AND ? AND r.not_for_distribution != "SMEAnalyticsDummy" GROUP BY r.upc ORDER BY ' . $order_by . ($sort_desc ? ' DESC' : ''); $sql .= $no_url ? '' : ' LIMIT ' . ($page-1) * $pageRecord . ', ' . $pageRecord; $data_params = new QueryData(); $data_params->addDataParam('s', $from_date); $data_params->addDataParam('s', $to_date); $result = $wrapperObj->executeQuery($sql, $data_params); if ($result === false) showierror($sql); $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 .= ' ' . ($no_url ? 'Label/Company' : sortColumnHeader('Label/Company', 'label', $url)) . ' ' . ($no_url ? 'Owner' : sortColumnHeader('Owner', 'owner', $url)) . ' ' . ($no_url ? 'Artist' : sortColumnHeader('Artist', 'artist', $url)) . ' ' . ($no_url ? 'UPC' : sortColumnHeader('UPC', 'upc', $url)) . ' ' . ($no_url ? 'Release' : sortColumnHeader('Relase', 'release', $url)) . ' ' . ($no_url ? 'Release Date' : sortColumnHeader('Release Date', 'rel_date', $url)) . ' ' . ($no_url ? 'Date Added' : sortColumnHeader('Date Added', 'ingestion_completed', $url)) . ' ' . ($no_url ? 'Date Submitted' : sortColumnHeader('Date Submitted', 'date_submitted', $url)) . ' ' . ($no_url ? 'Date Delivered' : sortColumnHeader('Date Delivered', 'date_delivered', $url)) . ' ' . ($no_url ? 'Days Taken' : sortColumnHeader('Days Taken', 'days_taken', $url)) . ' '; $rci = 0; foreach ($result as $row) { $row_class = (++$rci & 1) ? '' : 'alt'; $varTable .= ' ' . ($no_url ? $row['vendor_name'] : '' . $row['vendor_name'] . '') . ' ' . $row['owner'] . ' ' . ($no_url ? $row['name'] : '' . $row['name'] . '') . ' ' . ($no_url ? '"' . $row['display_upc'] . '"': '' . ($row['deletions'] == 'Y' ? '' . $row['display_upc'] . '' : $row['display_upc']) . '') . ' ' . ($no_url ? $row['release_name'] : '' . ($row['deletions'] == 'Y' ? '' . $row['release_name'] . '' : $row['release_name']) . '') . ' ' . format_date($row['release_date']) . ' ' . format_date($row['ingestion_completed']) . ' ' . format_date($row['encoder_delivery_date']) . ' ' . format_date($row['dms_delivery_date']) . ' ' . $row['days_taken'] . ' '; } $varTable .= ' '; break; } ?>