License Templates - the licenses an MSP sells or looks after again
* and again, ready to start a client's from: Microsoft 365 Business Premium,
* an endpoint protection agent, a backup product. Each row shows what it is,
* its type and license type, what a license from it starts with (seats, how
* often it renews, its vendor, the product it is sold as), how many licenses
* have been made from it and for how many clients, with their seats, when it
* was last used and last changed. A template every client, or the clients
* with a tag, should have shows how many of them have it; the Required tab
* lists those. New licenses can be made from one for a client or several at
* once. They can be exported to a file and imported from one, and the MSP
* starter pack added from Maintenance > Starter Content.
* A template can be changed, copied as the start of another, archived - not
* offered for new licenses, while the licenses made from it stay - or
* deleted. The work is in functions/software_template.php; the handlers are
* admin/post/software_template.php.
*/
// Default Column Sortby Filter
$sort = "software_template_name";
$order = "ASC";
require_once "includes/inc_all_admin.php";
// '' current, 'required' the current ones someone should have, 'archived'
$show = (string) ($_GET['show'] ?? '');
$show = in_array($show, ['required', 'archived'], true) ? $show : '';
$show_archived = $show === 'archived';
$show_where = [
'' => 'software_template_archived_at IS NULL',
'required' => 'software_template_archived_at IS NULL AND software_template_required > 0',
'archived' => 'software_template_archived_at IS NOT NULL',
][$show];
// Tab title - the file is named for the table, the page for what it holds
$page_title = 'License Templates';
// Only these sort - an unknown column would take the page down
$sortable_columns = [
'software_template_name' => "software_template_name $order",
'software_template_type' => "software_template_type $order, software_template_name ASC",
'software_template_license_type' => "software_template_license_type = '', software_template_license_type $order, software_template_name ASC",
'used_count' => "used_count $order, software_template_name ASC",
'last_used' => "last_used IS NULL, last_used $order, software_template_name ASC",
'changed_at' => "changed_at $order, software_template_name ASC",
];
if (!isset($sortable_columns[$sort])) {
$sort = 'software_template_name';
}
$search = "(software_template_name LIKE '%$q%' OR software_template_version LIKE '%$q%' OR software_template_description LIKE '%$q%' OR software_template_type LIKE '%$q%'
OR software_template_license_type LIKE '%$q%' OR software_template_notes LIKE '%$q%' OR vendor_template_name LIKE '%$q%' OR product_name LIKE '%$q%')";
// Its vendor template and product - searched, and shown under the name
$joins = "LEFT JOIN vendor_templates ON vendor_template_id = software_template_vendor_template_id AND software_template_vendor_template_id > 0
LEFT JOIN products ON product_id = software_template_product_id AND software_template_product_id > 0";
// Its use is the current licenses made from it, for current clients - as softwareTemplateUsage() counts them
$sql = mysqli_query($mysqli, "SELECT SQL_CALC_FOUND_ROWS software_templates.software_template_id, software_template_name, software_template_version, software_template_description,
software_template_type, software_template_license_type, software_template_notes, software_template_created_at, software_template_updated_at, software_template_archived_at,
software_template_seats, software_template_term, software_template_required, vendor_template_name, product_name, product_price, product_currency_code, product_frequency, product_seat_source,
COALESCE(software_template_updated_at, software_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 software_templates
$joins
LEFT JOIN users creator ON creator.user_id = software_template_created_by AND software_template_created_by > 0
LEFT JOIN users updater ON updater.user_id = software_template_updated_by AND software_template_updated_by > 0
LEFT JOIN (
SELECT software_template_id AS used_template_id, COUNT(*) AS used_count, MAX(software_created_at) AS last_used
FROM software JOIN clients ON client_id = software_client_id AND client_archived_at IS NULL
WHERE software_template_id > 0 AND software_archived_at IS NULL GROUP BY software_template_id
) used ON used.used_template_id = software_templates.software_template_id
WHERE $search AND $show_where
ORDER BY {$sortable_columns[$sort]}, software_templates.software_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['software_template_id'])] = $row;
}
$usage = softwareTemplateUsage(array_keys($rows));
// How many of the clients a required one is for have it
$required_tags = softwareTemplateRequiredTags(array_keys($rows));
$coverage = [];
foreach ($rows as $template_id => $row) {
if (intval($row['software_template_required']) && !$row['software_template_archived_at']) {
$coverage[$template_id] = softwareTemplateCoverage($template_id, $row['software_template_required'], array_keys($required_tags[$template_id]));
}
}
$counts = mysqli_fetch_row(mysqli_query($mysqli, "SELECT COALESCE(SUM(software_template_archived_at IS NULL), 0), COALESCE(SUM(software_template_archived_at IS NOT NULL), 0),
COALESCE(SUM(software_template_archived_at IS NULL AND software_template_required > 0), 0) FROM software_templates $joins 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/software_template/software_template_add.php';
$csrf = $_SESSION['csrf_token'];
?>
'license templates',
'icon' => 'fa-cube',
'filtered' => $q !== '',
'archived' => $show_archived,
'views' => ['show'],
'view' => $show === 'required' ? ['title' => 'No required templates', 'text' => 'Set one up from its page - Who should have it - to see which clients are missing a license from it.'] : null,
'text' => 'A license you sell or look after again and again - Microsoft 365 Business Premium, an endpoint protection agent, a backup product - ready to start a client\'s from. Agents start one from New License on a client\'s Licenses.',
'actions' => [['label' => 'New License Template', 'modal' => $add_url, 'size' => 'lg'], ['label' => 'Import', 'modal' => 'modals/software_template/software_template_import.php', 'icon' => 'fa-file-upload'],
['label' => 'Starter license templates', 'href' => 'starter_content.php', 'icon' => 'fa-seedling']],
];
require_once "../includes/filter_footer.php";
?>