791 lines
39 KiB
PHP
791 lines
39 KiB
PHP
<?php
|
|
|
|
function getResultatVMVisite($year,$roleid){
|
|
|
|
global $adb;
|
|
$query = "SELECT b.fullname as fullname , a.month as month , a.nbr_visite as visite ,role, a.nbr_visite_held as visiteheld FROM
|
|
( SELECT us.id ,EXTRACT(YEAR FROM ac.date_start) as YEAR,EXTRACT(MONTH FROM ac.date_start) as month, count(*) as nbr_visite, count(case when ac.eventstatus = 'Held' then 1 end) as nbr_visite_held
|
|
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='Calendar' and crm.deleted <> 1 and crm.smownerid <> 1
|
|
and ro.roleid in (SELECT roleid FROM `vtiger_role` WHERE `parentrole` like '%{$roleid}:%' or `parentrole` like '%{$roleid}')
|
|
JOIN vtiger_activity ac ON ac.activityid = crm.crmid
|
|
WHERE EXTRACT(YEAR FROM ac.date_start) = ".$year."
|
|
GROUP by us.id ,month) a
|
|
|
|
RIGHT JOIN
|
|
( 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}')) 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;
|
|
}
|
|
|
|
|
|
function getResultatVMPlanning($year,$roleid){
|
|
|
|
global $adb;
|
|
$query = "SELECT b.fullname as fullname , a.month as month , nbrvisite, a.nbr_visite as visite ,role FROM
|
|
( SELECT userid as id, month, nbrvisite FROM `planning`
|
|
where month like '%".$year."%') c
|
|
RIGHT JOIN
|
|
( SELECT us.id ,EXTRACT(YEAR FROM ac.date_start) as YEAR,EXTRACT(MONTH FROM ac.date_start) as month, count(case when ac.eventstatus = 'Held' then 1 end) as nbr_visite
|
|
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.deleted <> 1
|
|
and ro.roleid in (SELECT roleid FROM `vtiger_role` WHERE `parentrole` like '%{$roleid}:%' or `parentrole` like '%{$roleid}')
|
|
JOIN vtiger_activity ac ON ac.activityid = crm.crmid
|
|
WHERE EXTRACT(YEAR FROM ac.date_start) = ".$year."
|
|
GROUP by us.id ,month) a ON a.id = c.id and concat(a.month, '-',".$year.") like c.month
|
|
RIGHT JOIN
|
|
( 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}')) 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;
|
|
}
|
|
|
|
|
|
function getResultatVMVisiteDuo($year,$roleid) {
|
|
|
|
global $adb;
|
|
$query = "SELECT b.fullname as fullname , month , a.nbrcount as visite ,role FROM
|
|
(SELECT us.id, YEAR, month, count(us.id) as nbrcount
|
|
from vtiger_seactivityrel vsa
|
|
Join vtiger_activity va on vsa.`activityid` = va.activityid
|
|
JOIN vtiger_crmentity ve on vsa.activityid = ve.crmid and ve.deleted = 0
|
|
Join vtiger_users us on ve.smownerid = us.id
|
|
Join
|
|
(SELECT EXTRACT(YEAR FROM a.date_start) as YEAR,EXTRACT(MONTH FROM a.date_start) as month, a.date_start, ac.accountname, ac.accountid, count(DISTINCT e.smownerid)
|
|
FROM `vtiger_seactivityrel` sa
|
|
Join vtiger_activity a on sa.`activityid` = a.activityid
|
|
Join vtiger_account ac on sa.crmid = ac.accountid
|
|
JOIN vtiger_crmentity e on sa.activityid = e.crmid and e.deleted = 0
|
|
Join vtiger_users us on us.id = e.smownerid
|
|
GROUP by sa.crmid, a.date_start
|
|
HAVING count(DISTINCT e.smownerid)>1) a
|
|
on vsa.crmid = a.accountid and va.date_start = a.date_start
|
|
WHERE EXTRACT(YEAR FROM a.date_start) = ".$year."
|
|
GROUP by us.id, month) a
|
|
RIGHT JOIN
|
|
( 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}:%')) 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;
|
|
}
|
|
|
|
function getResultat2VMVisiteDuo($year,$roleid) {
|
|
|
|
global $adb;
|
|
$query = "SELECT b.fullname as fullname , month , a.nbrduo as nbrduo ,role FROM
|
|
|
|
(SELECT usr.id, EXTRACT(YEAR FROM va.date_start) as YEAR, EXTRACT(MONTH FROM va.date_start) as month, count(usr.id) as nbrduo
|
|
from vtiger_activity va JOIN vtiger_crmentity ve on va.activityid = ve.crmid and ve.deleted = 0
|
|
Join vtiger_users usr on ve.smownerid = usr.id
|
|
where va.duo_id is not null and va.duo_id != '' and EXTRACT(YEAR FROM va.date_start) = ".$year." GROUP by usr.id, month) a
|
|
|
|
RIGHT JOIN
|
|
( 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}:%')) b
|
|
ON a.id = b.id ";
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result2 = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result2[] = $recordinfo;
|
|
}
|
|
|
|
return $result2;
|
|
}
|
|
|
|
function getResultat3VMVisiteDuo($year,$roleid) {
|
|
|
|
global $adb;
|
|
$query = "SELECT a.fullname as fullname , month , a.visite as visite ,role FROM
|
|
(SELECT US.id, CONCAT(first_name,' ', last_name) as fullname, EXTRACT(YEAR FROM va.date_start) as YEAR, EXTRACT(MONTH FROM va.date_start) as month, count(US.id) as visite, ro.rolename as role
|
|
from vtiger_activity va
|
|
Join vtiger_users US on va.duo_id = US.id
|
|
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}:%')
|
|
JOIN vtiger_crmentity ve on va.activityid = ve.crmid and ve.deleted = 0
|
|
WHERE EXTRACT(YEAR FROM va.date_start) = ".$year."
|
|
GROUP by US.id, month) a";
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result3 = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result3[] = $recordinfo;
|
|
}
|
|
|
|
return $result3;
|
|
}
|
|
|
|
|
|
function getResultatVMTypeActivite($year,$roleid,$sql) {
|
|
|
|
global $adb;
|
|
$query = "SELECT b.activitytype as activitytype,a.month as month, a.nbr as nbr FROM
|
|
( SELECT act.activitytype as activitytype, EXTRACT(YEAR FROM act.date_start) as YEAR, EXTRACT(MONTH FROM act.date_start) as month,count(*) as nbr
|
|
FROM vtiger_crmentity crm
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.setype='Calendar' and crm.deleted <> 1 and crm.smownerid <> 1
|
|
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}') ".$sql."
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid
|
|
WHERE EXTRACT(YEAR FROM act.date_start) = ".$year." and EXTRACT(MONTH FROM act.date_start) <> 0
|
|
GROUP by act.activitytype,month) a
|
|
RIGHT JOIN
|
|
(SELECT activitytype FROM vtiger_activitytype) b
|
|
ON a.activitytype = b.activitytype";
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
|
|
function getResultatVMSpecialite($year,$roleid,$sql) {
|
|
|
|
global $adb;
|
|
$query = " SELECT b.accounttype AS accounttype, a.month as month , a.nbr as nbr,b.accounttypeid as accounttypeid from
|
|
|
|
(SELECT ac.account_type as accounttype,EXTRACT(YEAR FROM act.date_start) as YEAR,EXTRACT(MONTH FROM act.date_start ) as month, count(*) as nbr
|
|
|
|
FROM vtiger_crmentity crm
|
|
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.deleted <> 1 and crm.smownerid <> 1
|
|
|
|
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}') ".$sql."
|
|
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid AND crm.setype='Calendar'
|
|
|
|
JOIN vtiger_seactivityrel se on act.activityid=se.activityid
|
|
|
|
JOIN vtiger_account ac on se.crmid =ac.accountid
|
|
|
|
WHERE EXTRACT(YEAR FROM act.date_start)=".$year." and EXTRACT(MONTH FROM act.date_start) <> 0 and crm.deleted <> 1 and crm.smownerid <> 1
|
|
|
|
GROUP by ac.account_type,month ) a
|
|
|
|
RIGHT JOIN
|
|
|
|
(SELECT accounttype,accounttypeid FROM vtiger_accounttype) b
|
|
|
|
ON a.accounttype = b.accounttype";
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
|
|
function getResultatVMSpecialiteW($datedeb,$datefin,$roleid,$sql,$userid) {
|
|
global $adb;
|
|
$query = "SELECT b.fullname, a.accounttype, a.month as month , a.nbr as nbr from
|
|
(SELECT usr.userid, ac.account_type as accounttype, EXTRACT(YEAR FROM act.date_start) as YEAR,EXTRACT(MONTH FROM act.date_start ) as month, count(*) as nbr
|
|
FROM vtiger_crmentity crm
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.deleted <> 1
|
|
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}') ".$sql."
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid
|
|
JOIN vtiger_seactivityrel se on act.activityid=se.activityid
|
|
JOIN vtiger_account ac on se.crmid =ac.accountid";
|
|
$query = $query." WHERE act.date_start BETWEEN '".$datedeb."' and '".$datefin."'";
|
|
$query = $query." GROUP by usr.userid, accounttype";
|
|
|
|
$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}') ".$sql;
|
|
|
|
if($roleid=="H7" || $roleid=="H13" || $roleid=="H14" || $roleid=="H35" || $roleid=="H43" || $roleid=="H45")
|
|
$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}'";
|
|
|
|
$query = $query.") a
|
|
RIGHT JOIN
|
|
(".$rolestr.") b
|
|
ON a.userid = 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;
|
|
}
|
|
|
|
|
|
function getAccountsTypeSpecialite() {
|
|
|
|
global $adb;
|
|
$query = "SELECT * FROM vtiger_accounttype";
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
function getWilayas(){
|
|
|
|
global $adb;
|
|
$query = "SELECT DISTINCT cf_992 as wilaya FROM vtiger_accountscf";
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
|
|
function getResultatVMProduit($year,$roleid) {
|
|
|
|
global $adb;
|
|
$query = " SELECT b.productname as productname , a.month as month,a.nbr as nbr FROM
|
|
(SELECT p.productname, EXTRACT(YEAR FROM act.date_start) as YEAR, EXTRACT(MONTH FROM act.date_start) as month,count(*) as nbr from
|
|
vtiger_crmentity crm
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.setype='Calendar' and crm.deleted <> 1 and crm.smownerid <> 1
|
|
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}')
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid
|
|
JOIN vtiger_products p on (p.productid = act.produit_id or p.productid = act.produit2_id or p.productid = act.produit3_id or p.productid = act.produit4_id or p.productid = act.produit5_id)
|
|
WHERE EXTRACT(YEAR FROM act.date_start) =".$year." and EXTRACT(MONTH FROM act.date_start) <> 0
|
|
GROUP by p.productname, month) a
|
|
RIGHT JOIN
|
|
(SELECT p.productname AS productname FROM `vtiger_products` p
|
|
JOIN vtiger_crmentity e on p.productid = e.crmid and e.deleted = 0) b
|
|
ON a.productname = b.productname ";
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
|
|
function getResultatVMPoructSpec($year,$roleid) {
|
|
|
|
global $adb;
|
|
$query = " SELECT b.productname as productname , a.account_type as account_type ,a.accounttypeid as accounttypeid, a.nbr as nbr FROM
|
|
(SELECT p.productname as productname,EXTRACT(YEAR FROM act.date_start) as YEAR,acc.account_type as account_type, acty.accounttypeid as accounttypeid ,count(*) as nbr from
|
|
vtiger_crmentity crm
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.setype='Calendar' and crm.deleted <> 1 and crm.smownerid <> 1
|
|
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}')
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid
|
|
JOIN vtiger_seactivityrel sc on sc.activityid=act.activityid
|
|
JOIN vtiger_account acc on sc.crmid=acc.accountid
|
|
JOIN vtiger_accounttype acty on acc.account_type =acty.accounttype
|
|
JOIN vtiger_products p on (p.productid = act.produit_id or p.productid = act.produit2_id or p.productid = act.produit3_id or p.productid = act.produit4_id or p.productid = act.produit5_id)
|
|
WHERE EXTRACT(YEAR FROM act.date_start)=".$year."
|
|
GROUP by p.productname, acc.account_type ) a
|
|
RIGHT JOIN
|
|
(SELECT p.productname AS productname FROM vtiger_products p
|
|
JOIN vtiger_crmentity e on p.productid = e.crmid and e.deleted = 0) b
|
|
ON a.productname = b.productname and account_type<>'Pharmacie hospitalière' and account_type<>'Pharmacie'
|
|
order by b.productname";
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
function getAccountsType() {
|
|
|
|
global $adb;
|
|
$query = "SELECT accounttype,accounttypeid
|
|
FROM vtiger_accounttype where accounttype<>'Pharmacie' and accounttype<>'Pharmacie hospitalière' ";
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$accounttype = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$accounttype[] = $recordinfo;
|
|
}
|
|
return $accounttype;
|
|
}
|
|
|
|
|
|
function getResultatVMWillayaSpe($year,$roleid) {
|
|
|
|
global $adb;
|
|
$query = " SELECT ac.account_type as accounttype,EXTRACT(YEAR FROM act.date_start) as year,
|
|
IF(cf.cf_992 = 'Boumerdès' , 'Boumerdes',cf.cf_992) as accountwilaya,
|
|
count(*) as nbrvisite , acty.accounttypeid
|
|
FROM vtiger_crmentity crm
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.deleted <> 1 and crm.smownerid <> 1
|
|
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}')
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid and crm.setype='Calendar'
|
|
JOIN vtiger_seactivityrel se on act.activityid=se.activityid
|
|
JOIN vtiger_account ac on se.crmid =ac.accountid and ac.account_type not in ('Pharmacie' , 'Pharmacie hospitalière' , 'Grossiste')
|
|
JOIN vtiger_accountscf cf on cf.accountid = ac.accountid
|
|
JOIN vtiger_accounttype acty on acty.accounttype = ac.account_type
|
|
WHERE EXTRACT(YEAR FROM act.date_start) = ".$year."
|
|
GROUP by accounttype , accountwilaya";
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
|
|
function getSpecialties() {
|
|
|
|
global $adb;
|
|
$query = " SELECT accounttype as specialite, accounttypeid
|
|
FROM `vtiger_accounttype` where accounttype not in ('Pharmacie' , 'Pharmacie hospitalière' , 'Grossiste')
|
|
ORDER BY `specialite` ASC ";
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
|
|
function getResultatVMComptes($userId,$roleid) {
|
|
|
|
global $adb;
|
|
//DELEGUE MEDICAL
|
|
if(isVM($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;
|
|
}
|
|
return $result1;
|
|
}
|
|
|
|
function getResultatVM2Comptes() {
|
|
|
|
global $adb;
|
|
$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;
|
|
}
|
|
return $result2;
|
|
}
|
|
|
|
function getResultatVM3Comptes() {
|
|
|
|
global $adb;
|
|
$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;
|
|
}
|
|
return $result3;
|
|
}
|
|
|
|
|
|
function getRatings(){
|
|
|
|
global $adb;
|
|
$query = "SELECT rating FROM vtiger_rating";
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$ratings= array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$ratings[] = $recordinfo;
|
|
}
|
|
return $ratings;
|
|
}
|
|
|
|
function getResultatVMPotentialites($NomWilaya ,$roleid ,$userId){
|
|
|
|
global $adb;
|
|
if(isDRMedical($roleid)){
|
|
$rolestr = "SELECT id as usersid,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}') ";
|
|
}else if(!isDRMedical($roleid)){
|
|
$rolestr = "SELECT id as usersid,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."";
|
|
}
|
|
|
|
$query = "SELECT b.usersid, COUNT(b.usersid) as nbr ,a.wilaya_account,a.region_account ,a.rating,b.fullname,b.role from
|
|
(SELECT vtiger_usersvm3_id.id as id1, vtiger_users.id as id2, vtiger_usersvm2_id.id as id3,roo.rolename as role ,
|
|
vtiger_account.rating as rating ,vtiger_accountscf.cf_992 as wilaya_account,vtiger_accountscf.cf_990 as region_account
|
|
FROM vtiger_account
|
|
INNER JOIN vtiger_crmentity ON vtiger_account.accountid = vtiger_crmentity.crmid and vtiger_crmentity.deleted=0
|
|
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_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
|
|
LEFT JOIN vtiger_user2role as ro on ro.userid =vtiger_users.id
|
|
LEFT JOIN vtiger_role as roo on roo.roleid =ro.roleid
|
|
) a
|
|
RIGHT JOIN
|
|
(".$rolestr.") b ";
|
|
|
|
if( $NomWilaya == "ALL")
|
|
$query = $query." ON (b.usersid = a.id1 or b.usersid = a.id2 or b.usersid = a.id3) GROUP by b.usersid, a.rating HAVING LENGTH(a.rating) > 0 ";
|
|
else
|
|
$query = $query." ON (b.usersid = a.id1 or b.usersid = a.id2 or b.usersid = a.id3) GROUP by b.usersid, a.rating ,a.wilaya_account HAVING LENGTH(a.rating) > 0 AND a.wilaya_account = '".$NomWilaya."'";
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
function getResultatPresctription($datedeb,$datefin, $roleid,$sql,$userid) {
|
|
|
|
global $adb;
|
|
$query = "SELECT b.fullname, a.productname, a.month as month, SUM(a.somme) as nbr from
|
|
(SELECT k.userid, k.productname, SUM(sommep) as somme, k.YEAR, k.month from (SELECT usr.userid, comp.accountid, p.productname, SUM(actf.cf_1029) as sommep, EXTRACT(YEAR FROM act.date_start) as YEAR, EXTRACT(MONTH FROM act.date_start) as month from
|
|
vtiger_crmentity crm
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.setype='Calendar' and crm.deleted <> 1 and crm.smownerid <> 1
|
|
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}')
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid
|
|
LEFT JOIN vtiger_activitycf actf on actf.activityid = act.activityid
|
|
LEFT JOIN vtiger_products p on (p.productid = act.produit_id)
|
|
LEFT JOIN vtiger_seactivityrel sea on (sea.activityid = act.activityid)
|
|
LEFT JOIN vtiger_account comp on (comp.accountid = sea.crmid)";
|
|
$query = $query." WHERE act.date_start BETWEEN '".$datedeb."' and '".$datefin."' AND comp.industry = 'Medecin'";
|
|
$query = $query." GROUP by p.productid, comp.accountid, usr.userid";
|
|
|
|
$query = $query." UNION
|
|
SELECT usr.userid, comp.accountid, p.productname, SUM(actf.cf_1031) as sommep, EXTRACT(YEAR FROM act.date_start) as YEAR, EXTRACT(MONTH FROM act.date_start) as month from
|
|
vtiger_crmentity crm
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.setype='Calendar' and crm.deleted <> 1 and crm.smownerid <> 1
|
|
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}')
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid
|
|
LEFT JOIN vtiger_activitycf actf on actf.activityid = act.activityid
|
|
LEFT JOIN vtiger_products p on (p.productid = act.produit2_id)
|
|
LEFT JOIN vtiger_seactivityrel sea on (sea.activityid = act.activityid)
|
|
LEFT JOIN vtiger_account comp on (comp.accountid = sea.crmid)";
|
|
$query = $query." WHERE act.date_start BETWEEN '".$datedeb."' and '".$datefin."' AND comp.industry = 'Medecin'";
|
|
$query = $query." GROUP by p.productid, comp.accountid, usr.userid";
|
|
|
|
$query = $query." UNION
|
|
SELECT usr.userid, comp.accountid, p.productname, SUM(actf.cf_1033) as sommep, EXTRACT(YEAR FROM act.date_start) as YEAR, EXTRACT(MONTH FROM act.date_start) as month from
|
|
vtiger_crmentity crm
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.setype='Calendar' and crm.deleted <> 1 and crm.smownerid <> 1
|
|
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}')
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid
|
|
LEFT JOIN vtiger_activitycf actf on actf.activityid = act.activityid
|
|
LEFT JOIN vtiger_products p on (p.productid = act.produit3_id)
|
|
LEFT JOIN vtiger_seactivityrel sea on (sea.activityid = act.activityid)
|
|
LEFT JOIN vtiger_account comp on (comp.accountid = sea.crmid)";
|
|
$query = $query." WHERE act.date_start BETWEEN '".$datedeb."' and '".$datefin."' AND comp.industry = 'Medecin'";
|
|
$query = $query." GROUP by p.productid, comp.accountid, usr.userid";
|
|
|
|
$query = $query." UNION
|
|
SELECT usr.userid, comp.accountid, p.productname, SUM(actf.cf_1035) as sommep, EXTRACT(YEAR FROM act.date_start) as YEAR, EXTRACT(MONTH FROM act.date_start) as month from
|
|
vtiger_crmentity crm
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.setype='Calendar' and crm.deleted <> 1 and crm.smownerid <> 1
|
|
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}')
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid
|
|
LEFT JOIN vtiger_activitycf actf on actf.activityid = act.activityid
|
|
LEFT JOIN vtiger_products p on (p.productid = act.produit4_id)
|
|
LEFT JOIN vtiger_seactivityrel sea on (sea.activityid = act.activityid)
|
|
LEFT JOIN vtiger_account comp on (comp.accountid = sea.crmid)";
|
|
$query = $query." WHERE act.date_start BETWEEN '".$datedeb."' and '".$datefin."' AND comp.industry = 'Medecin'";
|
|
$query = $query." GROUP by p.productid, comp.accountid, usr.userid";
|
|
|
|
$query = $query." UNION
|
|
SELECT usr.userid, comp.accountid, p.productname, SUM(actf.cf_1037) as sommep, EXTRACT(YEAR FROM act.date_start) as YEAR, EXTRACT(MONTH FROM act.date_start) as month from
|
|
vtiger_crmentity crm
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.setype='Calendar' and crm.deleted <> 1 and crm.smownerid <> 1
|
|
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}')
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid
|
|
LEFT JOIN vtiger_activitycf actf on actf.activityid = act.activityid
|
|
LEFT JOIN vtiger_products p on (p.productid = act.produit5_id)
|
|
LEFT JOIN vtiger_seactivityrel sea on (sea.activityid = act.activityid)
|
|
LEFT JOIN vtiger_account comp on (comp.accountid = sea.crmid)";
|
|
$query = $query." WHERE act.date_start BETWEEN '".$datedeb."' and '".$datefin."' AND comp.industry = 'Medecin'";
|
|
$query = $query." GROUP by p.productid, comp.accountid, usr.userid) k GROUP BY k.productname, k.accountid, k.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}') ".$sql;
|
|
|
|
if($roleid=="H7" || $roleid=="H13" || $roleid=="H14" || $roleid=="H43" || $roleid=="H45")
|
|
$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}'";
|
|
|
|
$query = $query.") a
|
|
RIGHT JOIN (".$rolestr.") b
|
|
ON a.userid = b.id ";
|
|
|
|
if($roleid == "H9"){
|
|
$query = $query. "and a.userid = $userid";
|
|
}
|
|
|
|
$query = $query." WHERE productname IS NOT NULL GROUP BY fullname,productname ORDER by fullname";
|
|
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
|
|
function getProduits() {
|
|
|
|
global $adb;
|
|
$query = "SELECT * FROM vtiger_products p LEFT JOIN vtiger_crmentity crm on crm.crmid = p.productid WHERE discontinued = 1 and deleted = 0";
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$produits = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$produits[] = $recordinfo;
|
|
}
|
|
return $produits;
|
|
}
|
|
|
|
|
|
function getResultatSPWilaya($year,$roleid) {
|
|
|
|
global $adb;
|
|
$query = " SELECT b.cf_992 as productname , a.month as month,a.nbr as nbr FROM
|
|
(SELECT cf_992, EXTRACT(YEAR FROM act.date_start) as YEAR, EXTRACT(MONTH FROM act.date_start) as month,count(*) as nbr from
|
|
vtiger_crmentity crm
|
|
JOIN vtiger_user2role usr ON usr.userid = crm.smownerid and crm.setype='Calendar' and crm.deleted <> 1 and crm.smownerid <> 1
|
|
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}')
|
|
JOIN vtiger_activity act on act.activityid = crm.crmid and crm.setype='Calendar'
|
|
JOIN vtiger_seactivityrel se on act.activityid=se.activityid
|
|
JOIN vtiger_account ac on se.crmid =ac.accountid and ac.account_type not in ('Pharmacie' , 'Pharmacie hospitalière' , 'Grossiste')
|
|
JOIN vtiger_accountscf cf on cf.accountid = ac.accountid
|
|
WHERE EXTRACT(YEAR FROM act.date_start) =".$year." and EXTRACT(MONTH FROM act.date_start) <> 0
|
|
GROUP by cf_992, month) a
|
|
RIGHT JOIN
|
|
(SELECT DISTINCT cf_992 FROM vtiger_accountscf) b
|
|
ON a.cf_992 = b.cf_992 ";
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
function getResultatSPReseau($year,$roleid){
|
|
|
|
global $adb;
|
|
$query = "SELECT b.res_id as fullname , a.month as month , a.nbr_visite as visite ,role FROM
|
|
( SELECT us.res_id ,EXTRACT(YEAR FROM ac.date_start) as YEAR,EXTRACT(MONTH FROM ac.date_start) as month, count(*) as nbr_visite
|
|
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='Calendar' and crm.deleted <> 1 and crm.smownerid <> 1
|
|
and ro.roleid in (SELECT roleid FROM `vtiger_role` WHERE `parentrole` like '%{$roleid}:%' or `parentrole` like '%{$roleid}')
|
|
JOIN vtiger_activity ac ON ac.activityid = crm.crmid
|
|
WHERE EXTRACT(YEAR FROM ac.date_start) = ".$year."
|
|
GROUP by us.res_id ,month) a
|
|
|
|
RIGHT JOIN
|
|
( SELECT res_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}')) b
|
|
ON a.res_id = b.res_id ORDER by fullname";
|
|
|
|
$sql_get_result = $adb->query($query);
|
|
|
|
$result = array();
|
|
while ($recordinfo = $adb->fetch_array($sql_get_result)) {
|
|
$result[] = $recordinfo;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
function getResultatBCTotal($year, $roleid ,$userId){
|
|
|
|
$aStatus = '';
|
|
if($year == (new DateTime())->format("Y"))
|
|
$aStatus = 'Inactive';
|
|
$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 <> '".$aStatus."'
|
|
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 <> '".$aStatus."'
|
|
where US.id = ".$userId." ";
|
|
|
|
global $adb;
|
|
$query = "SELECT b.fullname, a.month, a.total_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, sum(subtotal) as total_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}')
|
|
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;
|
|
}
|
|
|
|
|
|
function getResultatBCobjective($year, $roleid ,$userId){
|
|
|
|
$aStatus = '';
|
|
if($year == (new DateTime())->format("Y"))
|
|
$aStatus = 'Inactive';
|
|
$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 <> '".$aStatus."'
|
|
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 <> '".$aStatus."'
|
|
where US.id = ".$userId." ";
|
|
|
|
global $adb;
|
|
$query = "SELECT b.fullname, a.month, a.total_bc as bc ,role,b.id 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, sum(subtotal) as total_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}')
|
|
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)) {
|
|
$objective = getVPObjectif($recordinfo['id'],$recordinfo['month']."-".$year);
|
|
$result[] = $recordinfo+["mntobjectif"=>$objective['mntobjectif']];
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
function getVPObjectif($userId,$periode){
|
|
global $adb;
|
|
$query = "SELECT montant_objectif as mntobjectif FROM objectif WHERE user_id = ".$userId." and periode = '".$periode."';";
|
|
$sql_get_result = $adb->query($query);
|
|
return $adb->fetch_array($sql_get_result);
|
|
}
|
|
?>
|