vars["title"]; ?>
vars["description"]; ?>
"tenants_table",
"scrollY" => "calc(100vh - 200px)", // Adjust the height according to your preference
"scrollCollapse" => true,
"onReady" => "function() {
// Code executed when the DataTable is ready
tenants_table.on('length.dt', function(e, settings, len) {
document.querySelector('input[name=\"pagelength\"').value = len;
len = document.querySelector('input[name=\"pagelength\"').value;
// document.querySelector('form').submit();// This would do a page refresh
});
tenants_table.on('xhr.dt', function(e, settings, json, xhr) {
if(actSearch == 0) {
actSearch = 1;
/* Check if we have a hash, then parse that */
var queryString = {};
var query = window.location.hash.substring( 1 );
var queryparts = query.split(\"=\");
var search = queryparts[1];
var search_found = 0;
search = queryparts[1];
if (typeof search !== 'undefined') {
search = search.split(':')
for (let i = 0; i < search.length; i++) {
if (search[i].startsWith('f')) {
search = search[i].substring(1);
search_found = 1;
break;
}
}
}
if (search_found == 1) {
search = decodeURIComponent(search);
tenants_table.columns([]).search(search).draw();
tenants_table.search(search).draw(true);
}
}
if(actCount == 0) {
actCount = 1;
/* Check if we have a hash, then parse that */
var queryString = {};
var query = window.location.hash.substring( 1 );
var queryparts = query.split(\"=\");
var length = queryparts[1];
var length_found = 0;
if (length == '_') {
length = 20;
} else {
length = queryparts[1];
if (typeof length !== 'undefined') {
length = length.split(':')
for (let i = 0; i < length.length; i++) {
if (length[i].startsWith('l')) {
length = length[i].substring(1);
length_found = 1;
break;
}
}
}
}
if (length_found == 1) {
if(isNaN(length)) {
length = 20;
}
/* Now we have the length, let's
* - update the length drop down,
* - redraw the table
*/
tenants_table.page.len(length).draw();
tenants_table.draw();
}
}
if(actSort == 0) {
actSort = 1;
/* Check if we have a hash, then parse that */
var queryString = {};
var query = window.location.hash.substring( 1 );
var queryparts = query.split(\"=\");
var sort = queryparts[1];
var sort_found = 0;
var direction = \"\";
sort = queryparts[1];
if (typeof sort !== 'undefined') {
sort = sort.split(':')
for (let i = 0; i < sort.length; i++) {
if (sort[i].startsWith('o')) {
direction = sort[i].substring(1,2);
sort = sort[i].substring(2);
sort_found = 1
break;
}
}
}
if (sort_found == 1) {
sort = decodeURIComponent(sort);
var sortColumn = parseInt(sort);
if (direction == 'a') {
direction = 'asc';
} else {
direction = 'desc';
}
tenants_table.order([[sortColumn, direction]]).draw();
}
}
});
}",
"dataSource" => function () {
if (isset($_GET['business_partner_id'])) {
return $this->src('tc')
->query("
WITH RECURSIVE seq AS (
SELECT 1 AS num
UNION ALL
SELECT num + 1
FROM seq
WHERE num <= 100 -- Adjust this limit to match the maximum expected number of comma-separated values
)
SELECT -- distinct
op.business_partner_id as `BPID`
, op.customer_name as `Customer`
, tc.lob as `LOB`
, op.entitlement_set_id as `ES`
, op.tpt_description as `TPT Desc.`
, op.zh_code as `ZH`
, op.quantity as `Q Allowed`
, op.tenants_live as `Q Actual`
, SUBSTRING_INDEX(SUBSTRING_INDEX(op.tenant_list, ',', seq.num), ',', -1) AS `Tenants`
, SUBSTRING_INDEX(SUBSTRING_INDEX(op.tenant_external_names, ',', seq.num), ',', -1) AS `Tenants Ext.`
, tc.created_on as `Created On`
,tc.phase_to as `Final Phase Date`
, tc.last_access as `Last Access`
, group_concat(concat(tc.product_id, ' ', tc.product_name) SEPARATOR ', ') as `Products`
, tc.system_data_center as `DC`
, dc.country_region as `DC Region`
, dc.sap_dc_name as `DC NAME`
, dc.infrastructure_service_provider as `DC Infra`
, op.zh_code_description as `Code`
, op.tpt as `TPT`
FROM over_provisioning as op
JOIN seq ON seq.num <= 1 + LENGTH(op.tenant_list) - LENGTH(REPLACE(op.tenant_list, ',', ''))
AND seq.num <= 1 + COALESCE (LENGTH(op.tenant_external_names) - LENGTH(REPLACE(op.tenant_external_names, ',', '')),0)
JOIN ___tc as tc ON tc.tenant_id = SUBSTRING_INDEX(SUBSTRING_INDEX(op.tenant_list, ',', seq.num), ',', -1)
AND tc.phase_cur = 1
JOIN datacenters dc on tc.system_data_center = dc.data_center_id
WHERE op.ovr_live > 0
AND op.business_partner_id = \"" . $_GET['business_partner_id'] . "\"
group by
`BPID`, `Customer`, `ES`, `Tenants`, `Tenants Ext.`
, `ZH`, `Code`, `Q Allowed`, `Q Actual`, `TPT`
, `TPT Desc.`, `Created On`, `Last Access`, `DC`
, `DC NAME`, `DC Infra`, `DC Region`
ORDER BY op.customer_name, op.tpt, op.zh_code
");
} else {
return $this->src('tc')
->query("
WITH RECURSIVE seq AS (
SELECT 1 AS num
UNION ALL
SELECT num + 1
FROM seq
WHERE num <= 100 -- Adjust this limit to match the maximum expected number of comma-separated values
)
SELECT -- distinct
op.business_partner_id as `BPID`
, op.customer_name as `Customer`
, tc.lob as `LOB`
, op.entitlement_set_id as `ES`
, op.tpt_description as `TPT Desc.`
, op.zh_code as `ZH`
, op.quantity as `Q Allowed`
, op.tenants_live as `Q Actual`
, SUBSTRING_INDEX(SUBSTRING_INDEX(op.tenant_list, ',', seq.num), ',', -1) AS `Tenants`
, SUBSTRING_INDEX(SUBSTRING_INDEX(op.tenant_external_names, ',', seq.num), ',', -1) AS `Tenants Ext.`
, tc.created_on as `Created On`
,tc.phase_to as `Final Phase Date`
, tc.last_access as `Last Access`
, group_concat(concat(tc.product_id, ' ', tc.product_name) SEPARATOR ', ') as `Products`
, tc.system_data_center as `DC`
, dc.country_region as `DC Region`
, dc.sap_dc_name as `DC NAME`
, dc.infrastructure_service_provider as `DC Infra`
, op.zh_code_description as `Code`
, op.tpt as `TPT`
FROM over_provisioning as op
JOIN seq ON seq.num <= 1 + LENGTH(op.tenant_list) - LENGTH(REPLACE(op.tenant_list, ',', ''))
AND seq.num <= 1 + COALESCE (LENGTH(op.tenant_external_names) - LENGTH(REPLACE(op.tenant_external_names, ',', '')),0)
JOIN ___tc as tc ON tc.tenant_id = SUBSTRING_INDEX(SUBSTRING_INDEX(op.tenant_list, ',', seq.num), ',', -1)
AND tc.phase_cur = 1
JOIN datacenters dc on tc.system_data_center = dc.data_center_id
WHERE op.ovr_live > 0
AND op.business_partner_id IN (SELECT business_partner_id FROM over_provisioning)
group by
`BPID`, `Customer`, `ES`, `Tenants`, `Tenants Ext.`
, `ZH`, `Code`, `Q Allowed`, `Q Actual`, `TPT`
, `TPT Desc.`, `Created On`, `Last Access`, `DC`
, `DC NAME`, `DC Infra`, `DC Region`
ORDER BY op.customer_name, op.tpt, op.zh_code
");
}
},
"options" => array(
"paging" => true,
"searching" => true,
"lengthMenu" => [[5, 10, 20, 50, -1], [5, 10, 20, 50, "All"]],
"keepConditions" => true,
"dom" => "Blfrtip",
"buttons" => [
'copyHtml5',
[
"extend" => 'excelHtml5',
"customize" => "function(xlsx) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];
var numrows = 5;
var clR = $('row', sheet);
//update Row
clR.each(function () {
var attr = $(this).attr('r');
var ind = parseInt(attr);
ind = ind + numrows;
$(this).attr('r', ind);
});
// Create row before data
$('row c ', sheet).each(function () {
var attr = $(this).attr('r');
var pre = attr.substring(0, 1);
var ind = parseInt(attr.substring(1, attr.length));
ind = ind + numrows;
$(this).attr('r', pre + ind);
});
function Addrow(index, data) {
var msg = '
'
for (var i = 0; i < data.length; i++) {
var key = data[i].key;
var value = data[i].value;
msg += '';
msg += '';
msg += '' + value + '';
msg += '';
msg += '';
}
msg += '
';
return msg;
}
//insert
var r1 = Addrow(2, [{ key: 'A', value: 'BPID' }, { key: 'B', value: 'TEST' }]);
var r2 = Addrow(3, [ { key: 'A', value: 'Customer' }, { key: 'B', value: 'TEST' }]);
var r3 = Addrow(4, [{ key: 'A', value: 'LOB' }, { key: 'B', value: 'TEST' }]);
var rows = [r1, r2, r3];
sheet.childNodes[0].childNodes[1].innerHTML = rows.join('') + sheet.childNodes[0].childNodes[1].innerHTML;
$('row c[r^=\"A2\"]', sheet).attr('s', '48');
$('row c[r^=\"A3\"]', sheet).attr('s', '48');
$('row c[r^=\"A4\"]', sheet).attr('s', '48');
// $('row c[r*=\"10\"]', sheet).attr( 's', '48' );
}"
],
'csvHtml5',
[
"extend" => 'pdfHtml5',
"orientation" => 'landscape',
"pageSize" => 'A4'
],
"print"
],
),
"cssClass" => array(
"table" => "table table-striped table-hover table-bordered",
"th" => "cssHeader"
),
]);