Vendor Templates - the vendors an MSP's clients use again and
* again, set up once: the ISPs, the hardware makers, the distributors, the
* line-of-business software. Each row shows what it is, its support line,
* hours and SLA, how many clients have a vendor made from it, when one was
* last added and when it last changed, and by whom - with how many of the
* clients it is required of have it, and how many of its vendors are out of
* step with it (the Required and Out of step tabs). Each opens its page
* (vendor_template.php). A template can be added to several clients at once,
* changed, copied as the start of another, archived - not offered for new
* vendors, while the vendors made from it stay - or deleted, and they go to
* and from a file (Export and Import). The work is in
* functions/vendor_template.php; the handlers are
* admin/post/vendor_template.php.
*/
// Default Column Sortby Filter
$sort = "vendor_template_name";
$order = "ASC";
require_once "includes/inc_all_admin.php";
// The templates with vendors out of step with them, and how many (vendorTemplateOutOfStep())
$made_from = [];
$sql = mysqli_query($mysqli, "SELECT DISTINCT vendor_template_id FROM vendors WHERE vendor_template_id > 0");
while ($row = mysqli_fetch_row($sql)) {
$made_from[] = intval($row[0]);
}
$out_of_step = vendorTemplateOutOfStep($made_from);
$out_ids = implode(',', array_keys($out_of_step) ?: [0]);
// '' current, 'required' the current ones someone should have, 'out' the current ones with vendors out of step, 'archived'
$show = (string) ($_GET['show'] ?? '');
if (($_GET['archived'] ?? '') === '1') {
$show = 'archived';
}
$show = in_array($show, ['required', 'out', 'archived'], true) ? $show : '';
$show_archived = $show === 'archived';
$show_where = [
'' => 'vendor_template_archived_at IS NULL',
'required' => 'vendor_template_archived_at IS NULL AND vendor_template_required > 0',
'out' => "vendor_template_archived_at IS NULL AND vendor_templates.vendor_template_id IN ($out_ids)",
'archived' => 'vendor_template_archived_at IS NOT NULL',
][$show];
// Tab title - the file is named for the table, the page for what it holds
$page_title = 'Vendor Templates';
// Only these sort - an unknown column would take the page down
$sortable_columns = [
'vendor_template_name' => "vendor_template_name $order",
'used_count' => "used_count $order, vendor_template_name ASC",
'last_used' => "last_used IS NULL, last_used $order, vendor_template_name ASC",
'changed_at' => "changed_at $order, vendor_template_name ASC",
];
if (!isset($sortable_columns[$sort])) {
$sort = 'vendor_template_name';
}
$phone_search = preg_replace('/[^0-9]/', '', (string) ($_GET['q'] ?? ''));
$search = "(vendor_template_name LIKE '%$q%' OR vendor_template_description LIKE '%$q%' OR vendor_template_website LIKE '%$q%' OR vendor_template_email LIKE '%$q%'
OR vendor_template_status_page LIKE '%$q%' OR vendor_template_portal LIKE '%$q%' OR vendor_template_contact_email LIKE '%$q%'
OR vendor_template_hours LIKE '%$q%' OR vendor_template_sla LIKE '%$q%' OR vendor_template_contact_name LIKE '%$q%' OR vendor_template_account_number LIKE '%$q%'
OR vendor_template_notes LIKE '%$q%'" . ($phone_search !== '' ? " OR vendor_template_phone LIKE '%$phone_search%'" : '') . ")";
// Its use is the current vendors made from it for current clients - as vendorTemplateUsage() counts them
$sql = mysqli_query($mysqli, "SELECT SQL_CALC_FOUND_ROWS vendor_templates.vendor_template_id, vendor_template_name, vendor_template_description, vendor_template_phone_country_code,
vendor_template_phone, vendor_template_extension, vendor_template_email, vendor_template_website, vendor_template_status_page, vendor_template_hours, vendor_template_sla,
vendor_template_created_at, vendor_template_updated_at, vendor_template_archived_at, vendor_template_required,
COALESCE(vendor_template_updated_at, vendor_template_created_at) AS changed_at, COALESCE(updater.user_name, creator.user_name) AS changed_by,
COALESCE(used.used_count, 0) AS used_count, used.last_used
FROM vendor_templates
LEFT JOIN users creator ON creator.user_id = vendor_template_created_by AND vendor_template_created_by > 0
LEFT JOIN users updater ON updater.user_id = vendor_template_updated_by AND vendor_template_updated_by > 0
LEFT JOIN (
SELECT vendor_template_id AS used_template_id, COUNT(DISTINCT vendor_client_id) AS used_count, MAX(vendor_created_at) AS last_used
FROM vendors JOIN clients ON client_id = vendor_client_id AND client_archived_at IS NULL
WHERE vendor_template_id > 0 AND vendor_archived_at IS NULL GROUP BY vendor_template_id
) used ON used.used_template_id = vendor_templates.vendor_template_id
WHERE $search AND $show_where
ORDER BY {$sortable_columns[$sort]}, vendor_templates.vendor_template_id ASC
LIMIT $record_from, $record_to");
$num_rows = mysqli_fetch_row(mysqli_query($mysqli, "SELECT FOUND_ROWS()"));
$rows = [];
while ($row = mysqli_fetch_assoc($sql)) {
$rows[intval($row['vendor_template_id'])] = $row;
}
$usage = vendorTemplateUsage(array_keys($rows));
// How many of the clients a required one is for have it
$required_tags = vendorTemplateRequiredTags(array_keys($rows));
$coverage = [];
foreach ($rows as $template_id => $row) {
if (intval($row['vendor_template_required']) && !$row['vendor_template_archived_at']) {
$coverage[$template_id] = vendorTemplateCoverage($template_id, $row['vendor_template_required'], array_keys($required_tags[$template_id]));
}
}
$counts = mysqli_fetch_row(mysqli_query($mysqli, "SELECT COALESCE(SUM(vendor_template_archived_at IS NULL), 0), COALESCE(SUM(vendor_template_archived_at IS NOT NULL), 0),
COALESCE(SUM(vendor_template_archived_at IS NULL AND vendor_template_required > 0), 0), COALESCE(SUM(vendor_template_archived_at IS NULL AND vendor_template_id IN ($out_ids)), 0)
FROM vendor_templates WHERE $search"));
$sort_link = function ($column, $label) use ($url_query_strings_sort, $sort, $disp, $order_icon) {
return ''
. $label . ($sort === $column ? " $order_icon" : '') . '';
};
$pill_url = function ($show) {
return '?' . http_build_query(array_filter(['q' => $_GET['q'] ?? '', 'show' => $show]));
};
$add_url = 'modals/vendor_template/vendor_template_add.php';
$clients_url = '/agent/modals/vendor/vendor_template_clients.php';
$import_url = 'modals/vendor_template/vendor_template_import.php';
$export_url = 'modals/vendor_template/vendor_template_export.php';
$csrf = $_SESSION['csrf_token'];
?>
'vendor templates',
'icon' => 'fa-building',
'filtered' => $q !== '',
'archived' => $show_archived,
'views' => ['show'],
'view' => ['required' => ['title' => 'No required templates', 'text' => 'Set one up from its page - Who should have it - to see which clients are missing a vendor from it.'],
'out' => ['title' => 'Every vendor is in step', 'text' => 'Every client\'s vendor made from a template says what the template says.']][$show] ?? null,
'text' => 'A vendor your clients use again and again - an ISP, a hardware maker, the software they run - set up once with its support line, hours and site, ready to add to any client. Agents start one from New Vendor on a client\'s Vendors.',
'actions' => [['label' => 'New Vendor Template', 'modal' => $add_url, 'size' => 'lg'], ['label' => 'Import', 'modal' => $import_url, 'icon' => 'fa-file-upload'],
['label' => 'Starter vendor templates', 'href' => 'starter_content.php', 'icon' => 'fa-seedling']],
];
require_once "../includes/filter_footer.php";
?>