get('id'); if(isTopDG($roleid)){$roleid="H10";} if(isset($_POST['year'])) $year = $_POST['year']; function getResultat($year, $roleid ,$userId){ $rolestr = "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.status <> 'Inactive' and ro.roleid in (SELECT roleid FROM `vtiger_role` WHERE `parentrole` like '%{$roleid}:%' OR `parentrole` like '%{$roleid}' OR ro.roleid = '".$roleid."')"; if(isVP($roleid)) $rolestr = "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.status <> 'Inactive' where US.id = ".$userId.""; global $adb; $query = "SELECT b.fullname, a.month, a.nbr_bc as bc ,role FROM ( SELECT us.id ,CONCAT(first_name,' ', last_name) as fullname ,EXTRACT(YEAR FROM so.duedate) as YEAR,EXTRACT(MONTH FROM so.duedate) as month,count(*) as nbr_bc FROM vtiger_users us JOIN vtiger_user2role usr ON usr.userid = us.id JOIN vtiger_role ro ON ro.roleid = usr.roleid JOIN vtiger_crmentity crm on crm.smownerid = us.id and crm.setype='SalesOrder' and crm.deleted <> 1 and ro.roleid in (SELECT roleid FROM `vtiger_role` WHERE `parentrole` like '%{$roleid}:%' OR `parentrole` like '%{$roleid}' OR ro.roleid = '".$roleid."') JOIN vtiger_salesorder so ON so.salesorderid = crm.crmid Where EXTRACT(YEAR FROM so.duedate)=".$year." GROUP by us.id ,month) a RIGHT JOIN (".$rolestr.") b ON a.id = b.id ORDER by fullname"; $sql_get_result = $adb->query($query); $result = array(); while ($recordinfo = $adb->fetch_array($sql_get_result)) { $result[] = $recordinfo; } return $result; } if(isset($_POST['year'])) $year = $_POST['year']; $result = getResultat($year,$roleid ,$userId); echo getMonitoringMainBarVP(3). '
'. getTopTextSelect('Total des bons de commande crée en', $year). '
'.getRegionSelectSubmit($roleid,false).getRolesSelectVP().'
'; echo '
Post Region Nom & Prenom janvier février mars avril mai juin juillet août septembre octobre novembre décembre Total
'; $viewer->view('CustomDashboardFooter.tpl'); ?>