260 lines
8.8 KiB
PHP
260 lines
8.8 KiB
PHP
<?php
|
|
|
|
|
|
|
|
$dbvp = true;
|
|
|
|
require_once 'MonitoringVMHeader.php';
|
|
|
|
require_once 'MonitoringDBRequest.php';
|
|
|
|
|
|
|
|
$userId = $current_user->get('id');
|
|
|
|
if(isTopDG($roleid)){$roleid="H10";}
|
|
|
|
|
|
|
|
|
|
|
|
global $adb;
|
|
|
|
|
|
|
|
//DELEGUE COMMERCIAL
|
|
|
|
if(isVP($roleid)){
|
|
|
|
|
|
|
|
$query = "SELECT id,CONCAT(first_name,' ', last_name) as fullname ,ro.rolename as role FROM vtiger_users US
|
|
|
|
JOIN vtiger_user2role usr ON usr.userid = US.id
|
|
|
|
JOIN vtiger_role ro ON ro.roleid = usr.roleid and US.id ='".$userId."' ";
|
|
|
|
|
|
|
|
}else{
|
|
|
|
$query = "SELECT id,CONCAT(first_name,' ', last_name) as fullname ,ro.rolename as role FROM vtiger_users US
|
|
|
|
JOIN vtiger_user2role usr ON usr.userid = US.id
|
|
|
|
JOIN vtiger_role ro ON ro.roleid = usr.roleid and ro.roleid in (SELECT roleid FROM `vtiger_role` WHERE `parentrole` like '%::{$roleid}::%' OR `parentrole` like '%{$roleid}' OR ro.roleid = '".$roleid."' ) ";
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
|
|
|
|
$result1 = array();
|
|
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
|
|
$result1[] = $recordinfo;
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
$query = "SELECT b. usersid, COUNT(b.usersid) as total_Compte from
|
|
|
|
(SELECT vtiger_usersvm3_id.id as id1, vtiger_users.id as id2, vtiger_usersvm2_id.id as id3,roo.rolename as role
|
|
|
|
FROM vtiger_account
|
|
|
|
INNER JOIN vtiger_crmentity ON vtiger_account.accountid = vtiger_crmentity.crmid
|
|
|
|
INNER JOIN vtiger_accountscf ON vtiger_account.accountid = vtiger_accountscf.accountid
|
|
|
|
|
|
|
|
LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id
|
|
|
|
LEFT JOIN vtiger_user2role as ro on ro.userid =vtiger_users.id
|
|
|
|
LEFT JOIN vtiger_role as roo on roo.roleid =ro.roleid
|
|
|
|
LEFT JOIN vtiger_users AS vtiger_usersvm2_id ON vtiger_account.vm2_id = vtiger_usersvm2_id.id
|
|
|
|
LEFT JOIN vtiger_users AS vtiger_usersvm3_id ON vtiger_account.vm3_id = vtiger_usersvm3_id.id
|
|
|
|
WHERE vtiger_crmentity.deleted=0 AND vtiger_account.accountid > 0 ) a
|
|
|
|
INNER JOIN
|
|
|
|
(select id as usersid FROM vtiger_users
|
|
|
|
) b
|
|
|
|
ON (b.usersid = a.id1 or b.usersid = a.id2 or b.usersid = a.id3) GROUP by b.usersid";
|
|
|
|
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
|
|
|
|
$result2 = array();
|
|
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
|
|
$result2[] = $recordinfo;
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
$query = "SELECT b. usersid, COUNT(b.usersid) as total_Compte from
|
|
|
|
(SELECT vtiger_usersvm3_id.id as id1, vtiger_users.id as id2, vtiger_usersvm2_id.id as id3,roo.rolename as role
|
|
|
|
FROM vtiger_account
|
|
|
|
INNER JOIN vtiger_crmentity ON vtiger_account.accountid = vtiger_crmentity.crmid
|
|
|
|
INNER JOIN vtiger_accountscf ON vtiger_account.accountid = vtiger_accountscf.accountid
|
|
|
|
|
|
|
|
LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id
|
|
|
|
LEFT JOIN vtiger_user2role as ro on ro.userid =vtiger_users.id
|
|
|
|
LEFT JOIN vtiger_role as roo on roo.roleid =ro.roleid
|
|
|
|
LEFT JOIN vtiger_users AS vtiger_usersvm2_id ON vtiger_account.vm2_id = vtiger_usersvm2_id.id
|
|
|
|
LEFT JOIN vtiger_users AS vtiger_usersvm3_id ON vtiger_account.vm3_id = vtiger_usersvm3_id.id
|
|
|
|
WHERE vtiger_crmentity.deleted=0 AND vtiger_account.accountid <> 0 and LENGTH(vtiger_accountscf.cf_986) > 0 and LENGTH(vtiger_accountscf.cf_988) > 0 ) a
|
|
|
|
INNER JOIN
|
|
|
|
(select id as usersid FROM vtiger_users
|
|
|
|
) b
|
|
|
|
ON (b.usersid = a.id1 or b.usersid = a.id2 or b.usersid = a.id3) GROUP by b.usersid";
|
|
|
|
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
|
|
|
|
$result3 = array();
|
|
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
|
|
$result3[] = $recordinfo;
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
function arraySearch($var, $array){
|
|
|
|
for($i=0;$i <= sizeof($array)-1;$i++){
|
|
|
|
if($var==$array[$i][0]){
|
|
|
|
return $array[$i][1];
|
|
|
|
exit;
|
|
|
|
}
|
|
|
|
}
|
|
|
|
return 0;
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
$temp=array();
|
|
|
|
$result=array();
|
|
|
|
for($i=0;$i <= sizeof($result1)-1;$i++){
|
|
|
|
$temp[0]=$result1[$i][0];//userid
|
|
|
|
$temp['fullname']=$result1[$i][1];//fullname
|
|
|
|
$temp['NbrComptesLocaliser']=arraySearch($temp[0],$result3); //nbr total des comptes localiser
|
|
|
|
$temp['TotalCompte']=arraySearch($temp[0],$result2); //nbr total des comptes
|
|
|
|
$temp['NbrComptesNonLocaliser']=$temp['TotalCompte']-$temp['NbrComptesLocaliser']; //nbr total des comptes non localiser
|
|
|
|
//post
|
|
|
|
if($result1[$i][2]=="RESPONSABLE COMMERCIAL"){
|
|
|
|
$temp[5]="RESPONSABLE COMMERCIAL";
|
|
|
|
$temp[6]="OUEST";
|
|
|
|
}
|
|
|
|
if(isKAM($result1[$i][2])){
|
|
|
|
$temp[5]="KEY ACCOUNT MANAGER";
|
|
|
|
}
|
|
|
|
if(isVPText($result1[$i][2])){
|
|
|
|
$temp[5]="DELEGUE COMMERCIAL";
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
//région
|
|
|
|
if(isOuestVPText($result1[$i][2])){
|
|
|
|
$temp[6]="OUEST";
|
|
|
|
}
|
|
|
|
if(isCentreVPText($result1[$i][2])){
|
|
|
|
$temp[6]="CENTRE";
|
|
|
|
}
|
|
|
|
if(isEstVPText($result1[$i][2])){
|
|
|
|
$temp[6]="EST";
|
|
|
|
}
|
|
|
|
|
|
|
|
$result[$i]=$temp;
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
echo getMonitoringMainBarVP(7);
|
|
|
|
|
|
|
|
echo '<div class="main-container main-container-Accounts">
|
|
|
|
|
|
|
|
<div style="margin:15px 15px 15px 15px; border:1px solid #ecf0f1; ">
|
|
|
|
<div style=" text-align: center"><h3>Nombre des comptes localisé/Non localisé</h3></div>
|
|
|
|
|
|
|
|
<hr>
|
|
|
|
|
|
|
|
<div class="form-row">
|
|
|
|
<div class="form-group col-md-4">
|
|
|
|
<label for="inputCity">Nom & Prénom</label>
|
|
|
|
<input class="form-control mb-4 w-20" type="text" name="nomprenom" placeholder="Rechercher" onKeyUp="fullNameChange(this)">
|
|
|
|
</div>'.getRegionSelectSubmit($roleid,false).getRolesSelectVP().'
|
|
|
|
</div>';
|
|
|
|
|
|
|
|
|
|
|
|
echo '<table id="tabcenter" class="table" >
|
|
|
|
|
|
|
|
<tr class="center borderline">
|
|
|
|
<th id="role" >Poste</th>
|
|
|
|
<th id="region" >Région</th>
|
|
|
|
<th id="fullname" > Nom & Prénom</th>
|
|
|
|
<th>Compte localisé</th>
|
|
|
|
<th>Compte non localisé</th>
|
|
|
|
<th style="text-align: center;"> total</th>
|
|
|
|
</tr>
|
|
|
|
|
|
|
|
</table></div></div>';
|
|
|
|
|
|
|
|
$viewer->view('CustomDashboardFooter.tpl');
|
|
|
|
?>
|
|
|
|
|
|
|
|
|
|
|
|
<script>
|
|
|
|
|
|
|
|
function loadTable(data)
|
|
|
|
{
|
|
|
|
$('#tabcenter tr').slice(1).remove();
|
|
|
|
|
|
|
|
if(data.length > 0) {
|
|
|
|
|
|
|
|
for(var i = 0; i < data.length; i++) {
|
|
|
|
var row = data[i];
|
|
|
|
var click = "";
|
|
|
|
|
|
|
|
$('#tabcenter').append(
|
|
|
|
"<tr class='center borderline'"+click+">"
|
|
|
|
+"<td>"+row[5]+"</td>"
|
|
|
|
+"<td>"+row[6]+"</td>"
|
|
|
|
+"<td >"+row['fullname'].trim()+"</td>"
|
|
|
|
+"<td>"+row['NbrComptesLocaliser']+"</td>"
|
|
|
|
+"<td><span class='fieldValue'><span class='value'><a href='?module=Accounts&view=List&app=INVENTORY&search_params=[[[%22assigned_user_id%22,%22c%22,%22"+row['fullname']+"%22],[%22cf_986%22,%22l%22,%220%22]]]'>"+row['NbrComptesNonLocaliser']+"</a></span></span></td>"
|
|
|
|
+"<td style=' background:#7f8fa6; color: #ffffff;text-align: center;'>"+row['TotalCompte']+"</td>"
|
|
|
|
|
|
|
|
|
|
|
|
+"</tr>"
|
|
|
|
);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
var result = <?php echo json_encode($result); ?>;
|
|
|
|
|
|
|
|
function loadByFullName() {
|
|
|
|
|
|
|
|
var fullnames = Array.prototype.slice.call(result).sort(function(a, b){
|
|
|
|
if(a.fullname < b.fullname) { return -1; }
|
|
|
|
if(a.fullname > b.fullname) { return 1; }
|
|
|
|
return 0;
|
|
|
|
});
|
|
|
|
|
|
|
|
|
|
|
|
loadTable(fullnames);
|
|
|
|
}
|
|
|
|
|
|
|
|
roleChange("DELEGUE COMMERCIAL");
|
|
|
|
|
|
|
|
//filtre nom/prenom
|
|
|
|
function fullNameChange(e) {
|
|
|
|
document.getElementById("role").value = "All";
|
|
|
|
document.getElementById("region").value = "All";
|
|
|
|
var res = result.filter(a => a.fullname.toUpperCase().includes(e.value.toUpperCase()) );
|
|
|
|
loadTable(res);
|
|
|
|
|
|
|
|
}
|
|
|
|
//filtre region
|
|
|
|
function regionChange(e) {
|
|
|
|
document.getElementById("role").value = "All";
|
|
|
|
var arrayFiltre=result;
|
|
|
|
var filteredValue = arrayFiltre.filter(function (item) {
|
|
|
|
return item[6] === e ;
|
|
|
|
});
|
|
|
|
if(e== "All"){filteredValue=arrayFiltre;}
|
|
|
|
loadTable(filteredValue);
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
//filtre Role
|
|
|
|
function roleChange(e) {
|
|
|
|
if(document.getElementById("region").options.length > 1)
|
|
|
|
document.getElementById("region").value = "All";
|
|
|
|
var arrayFiltre=result;
|
|
|
|
|
|
|
|
var filteredValue = arrayFiltre.filter(function (item) {
|
|
|
|
return item[5] === e ;
|
|
|
|
});
|
|
|
|
if(e== "All"){filteredValue=arrayFiltre;}
|
|
|
|
loadTable(filteredValue);
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
</script>
|
|
|
|
|
|
|
|
</body>
|
|
|
|
|
|
|
|
</html>
|