2a647b138a
- Enabled SMTP debugging in PHPMailer for better error tracking. - Added a "Test send email" link in the Inventory Detail View for quick email testing. - Implemented automatic PDF generation and email sending upon Sales Order creation. - Created a new action for sending Sales Order emails with attached PDFs. - Added a new AJAX action for testing outgoing email server configurations. - Updated outgoing server settings to use new SMTP credentials. - Improved email templates for better user experience. - Added test scripts for validating PDF generation and email sending.
641 lines
13 KiB
PHP
641 lines
13 KiB
PHP
<?php
|
|
|
|
//ob_Start();
|
|
|
|
require_once 'include/utils/utils.php';
|
|
|
|
require_once 'includes/Loader.php';
|
|
|
|
vimport ('includes.runtime.EntryPoint');
|
|
|
|
require_once 'SUtiles.php';
|
|
|
|
@session_start();
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
$current_user = Users_Record_Model::getCurrentUserModel();
|
|
|
|
$roleid = $current_user->get('roleid');
|
|
|
|
$userId = $current_user->get('id');
|
|
|
|
$currentDate = new DateTime();
|
|
|
|
$year = $currentDate->format("Y");
|
|
|
|
$month = $currentDate->format("m");
|
|
|
|
$day = $currentDate->format("d");
|
|
|
|
global $adb;
|
|
|
|
|
|
|
|
function getProduits(){
|
|
|
|
global $adb;
|
|
|
|
$query = "SELECT COUNT(*) as nbr
|
|
|
|
FROM vtiger_products
|
|
|
|
INNER JOIN vtiger_crmentity ON vtiger_products.productid = vtiger_crmentity.crmid
|
|
|
|
WHERE vtiger_crmentity.deleted=0 AND vtiger_products.productid > 0";
|
|
|
|
$result = $adb->query($query);
|
|
|
|
|
|
|
|
$res=array();
|
|
|
|
while ($recordinfo = $adb->fetch_array($result)) {
|
|
|
|
$res[] = $recordinfo;
|
|
|
|
}
|
|
|
|
|
|
|
|
return $res[0];
|
|
|
|
}
|
|
|
|
|
|
|
|
function getCompte($type){
|
|
|
|
global $adb;
|
|
|
|
|
|
|
|
$query ="SELECT count(*) as nbr
|
|
|
|
FROM vtiger_account
|
|
|
|
INNER JOIN vtiger_crmentity ON vtiger_account.accountid = vtiger_crmentity.crmid
|
|
|
|
WHERE vtiger_crmentity.deleted=0 AND (( vtiger_account.industry = '{$type}')
|
|
|
|
and ( vtiger_account.account_type <> 'all') ) AND vtiger_account.accountid > 0";
|
|
|
|
$result = $adb->query($query);
|
|
|
|
|
|
|
|
$res=array();
|
|
|
|
while ($recordinfo = $adb->fetch_array($result)) {
|
|
|
|
$res[] = $recordinfo;
|
|
|
|
}
|
|
|
|
|
|
|
|
return $res[0];
|
|
|
|
}
|
|
|
|
|
|
|
|
function getUsers($role){
|
|
|
|
global $adb;
|
|
|
|
|
|
|
|
switch ($role) {
|
|
|
|
|
|
|
|
case "DELEGUE COMMERCIAL":
|
|
|
|
$roles = "'DELEGUE COMMERCIAL CENTRE' ,'DELEGUE COMMERCIAL EST' ,'DELEGUE COMMERCIAL EST2' ,'DELEGUE COMMERCIAL OUEST'";
|
|
|
|
break;
|
|
|
|
case "DELEGUE MEDICAL":
|
|
|
|
$roles = "'DELEGUE MEDICAL CENTRE','DELEGUE MEDICAL CENTRE1','DELEGUE MEDICAL CENTRE2','DELEGUE MEDICAL EST1',
|
|
|
|
'DELEGUE MEDICAL EST2','DELEGUE MEDICAL EST3','DELEGUE MEDICAL OUEST','DELEGUE MEDICAL OUEST1','DELEGUE MEDICAL OUEST2'";
|
|
|
|
break;
|
|
|
|
case "SUPERVISEUR COMMERCIAL":
|
|
|
|
$roles = "'SUPERVISEUR COMMERCIAL EST2','SUPERVISEUR COMMERCIAL CENTRE'";
|
|
|
|
break;
|
|
|
|
case "SUPERVISEUR MEDICAL":
|
|
|
|
$roles = "'SUPERVISEUR MEDICAL CENTRE1','SUPERVISEUR MEDICAL CENTRE2','SUPERVISEUR MEDICAL EST1',
|
|
|
|
'SUPERVISEUR MEDICAL EST2','SUPERVISEUR MEDICAL EST3','SUPERVISEUR MEDICAL OUEST1','SUPERVISEUR MEDICAL OUEST2'";
|
|
|
|
break;
|
|
|
|
case "kam":
|
|
|
|
$roles = "'KEY ACCOUNT MANAGER CENTRE','KEY ACCOUNT MANAGER EST'";
|
|
|
|
break;
|
|
|
|
}
|
|
|
|
$query = "SELECT count(*) as nbr
|
|
|
|
FROM vtiger_users
|
|
|
|
LEFT JOIN vtiger_user2role ON (vtiger_user2role.userid = vtiger_users.id )
|
|
|
|
LEFT JOIN vtiger_role ON (vtiger_role.roleid = vtiger_user2role.roleid )
|
|
|
|
where vtiger_role.rolename in (".$roles.") and vtiger_users.status = 'Active' ";
|
|
|
|
$result = $adb->query($query);
|
|
|
|
|
|
|
|
$res=array();
|
|
|
|
while ($recordinfo = $adb->fetch_array($result)) {
|
|
|
|
$res[] = $recordinfo;
|
|
|
|
}
|
|
|
|
|
|
|
|
return $res[0];
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
function getPackCA(){
|
|
|
|
global $adb;
|
|
|
|
$query = "SELECT vtiger_potential.potentialname as pack ,sum(vtiger_salesorder.subtotal) as total
|
|
|
|
FROM vtiger_crmentity
|
|
|
|
LEFT JOIN vtiger_salesorder ON (vtiger_salesorder.salesorderid = vtiger_crmentity.crmid )
|
|
|
|
LEFT JOIN vtiger_salesordercf ON (vtiger_salesordercf.salesorderid = vtiger_crmentity.crmid )
|
|
|
|
LEFT JOIN vtiger_account ON (vtiger_salesorder.accountid = vtiger_account.accountid )
|
|
|
|
LEFT JOIN vtiger_potential ON (vtiger_salesorder.potentialid = vtiger_potential.potentialid )
|
|
|
|
WHERE vtiger_salesorder.potentialid <> 0 and vtiger_potential.closingdate = '2021-07-10' and vtiger_crmentity.deleted = 0
|
|
|
|
GROUP by vtiger_salesorder.potentialid";
|
|
|
|
$result = $adb->query($query);
|
|
|
|
|
|
|
|
$res=array();
|
|
|
|
while ($recordinfo = $adb->fetch_array($result)) {
|
|
|
|
$res[] = $recordinfo;
|
|
|
|
}
|
|
|
|
|
|
|
|
return $res;
|
|
|
|
}
|
|
|
|
|
|
|
|
function getCompteParRegion($type){
|
|
|
|
global $adb;
|
|
|
|
$query = "SELECT vtiger_accountscf.cf_990 as region , COUNT(*) as nbr FROM `vtiger_account`
|
|
|
|
LEFT JOIN vtiger_accountscf ON (vtiger_accountscf.accountid =vtiger_account.accountid)
|
|
|
|
INNER JOIN vtiger_crmentity ON vtiger_account.accountid = vtiger_crmentity.crmid
|
|
|
|
WHERE vtiger_account.industry = '{$type}' and vtiger_accountscf.cf_990 <> '' and vtiger_crmentity.deleted = 0
|
|
|
|
GROUP by vtiger_accountscf.cf_990 ORDER BY `region` ASC";
|
|
|
|
$result = $adb->query($query);
|
|
|
|
|
|
|
|
$res=array();
|
|
|
|
while ($recordinfo = $adb->fetch_array($result)) {
|
|
|
|
$res[] = $recordinfo;
|
|
|
|
}
|
|
|
|
|
|
|
|
return $res;
|
|
|
|
}
|
|
|
|
|
|
|
|
function getUserParRegion($role){
|
|
|
|
global $adb;
|
|
|
|
|
|
|
|
|
|
|
|
$query = "SELECT count(*) as nbr ,vtiger_role.rolename
|
|
|
|
FROM vtiger_users LEFT JOIN vtiger_user2role ON (vtiger_user2role.userid = vtiger_users.id )
|
|
|
|
LEFT JOIN vtiger_role ON (vtiger_role.roleid = vtiger_user2role.roleid )
|
|
|
|
where vtiger_users.status = 'Active' AND vtiger_role.rolename like '{$role}%'
|
|
|
|
group by vtiger_role.rolename
|
|
|
|
ORDER BY `vtiger_role`.`rolename` ASC ";
|
|
|
|
$result = $adb->query($query);
|
|
|
|
|
|
|
|
$res=array();
|
|
|
|
while ($recordinfo = $adb->fetch_array($result)) {
|
|
|
|
$res[] = $recordinfo;
|
|
|
|
}
|
|
|
|
|
|
|
|
$all=array();
|
|
|
|
switch ($role) {
|
|
|
|
|
|
|
|
case "DELEGUE COMMERCIAL":
|
|
|
|
$vpc=$vpe=$vpo=array();
|
|
|
|
|
|
|
|
for( $t=0; $t < count($res) ;$t++){
|
|
|
|
if( $res[$t]['rolename'] == 'DELEGUE COMMERCIAL CENTRE'){
|
|
|
|
array_push($vpc,$res[$t]['nbr']);
|
|
|
|
}
|
|
|
|
if( $res[$t]['rolename'] == 'DELEGUE COMMERCIAL EST' ||
|
|
|
|
$res[$t]['rolename'] == 'DELEGUE COMMERCIAL EST2' ){
|
|
|
|
array_push($vpe,$res[$t]['nbr']);
|
|
|
|
}
|
|
|
|
if( $res[$t]['rolename'] == 'DELEGUE COMMERCIAL OUEST' ){
|
|
|
|
array_push($vpo,$res[$t]['nbr']);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
array_push($all ,["CENTRE",array_sum($vpc)]) ;
|
|
|
|
array_push($all ,["EST",array_sum($vpe)]) ;
|
|
|
|
array_push($all ,["OUEST",array_sum($vpo)]) ;
|
|
|
|
break;
|
|
|
|
case "DELEGUE MEDICAL":
|
|
|
|
$vmc=$vme=$vmo=array();
|
|
|
|
|
|
|
|
for( $t=0; $t < count($res) ;$t++){
|
|
|
|
if( $res[$t]['rolename'] == 'DELEGUE MEDICAL CENTRE' ||
|
|
|
|
$res[$t]['rolename'] == 'DELEGUE MEDICAL CENTRE1' ||
|
|
|
|
$res[$t]['rolename'] == 'DELEGUE MEDICAL CENTRE2'){
|
|
|
|
array_push($vmc,$res[$t]['nbr']);
|
|
|
|
}
|
|
|
|
if( $res[$t]['rolename'] == 'DELEGUE MEDICAL EST1' ||
|
|
|
|
$res[$t]['rolename'] == 'DELEGUE MEDICAL EST2' ||
|
|
|
|
$res[$t]['rolename'] == 'DELEGUE MEDICAL EST3'){
|
|
|
|
array_push($vme,$res[$t]['nbr']);
|
|
|
|
}
|
|
|
|
if( $res[$t]['rolename'] == 'DELEGUE MEDICAL OUEST' ||
|
|
|
|
$res[$t]['rolename'] == 'DELEGUE MEDICAL OUEST1' ||
|
|
|
|
$res[$t]['rolename'] == 'DELEGUE MEDICAL OUEST2'){
|
|
|
|
array_push($vmo,$res[$t]['nbr']);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
array_push($all ,["CENTRE",array_sum($vmc)]) ;
|
|
|
|
array_push($all ,["EST",array_sum($vme),]) ;
|
|
|
|
array_push($all ,["OUEST",array_sum($vmo)]) ;
|
|
|
|
break;
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
return $all;
|
|
|
|
}
|
|
|
|
|
|
|
|
function getCaParRegion(){
|
|
|
|
global $adb;
|
|
|
|
|
|
|
|
|
|
|
|
$query = "SELECT sum(vtiger_salesorder.total) as total, vtiger_role.rolename
|
|
|
|
FROM vtiger_salesorder
|
|
|
|
INNER JOIN vtiger_crmentity ON vtiger_salesorder.salesorderid = vtiger_crmentity.crmid
|
|
|
|
LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id
|
|
|
|
LEFT JOIN vtiger_user2role ON vtiger_crmentity.smownerid = vtiger_user2role.userid
|
|
|
|
LEFT JOIN vtiger_role ON vtiger_user2role.roleid = vtiger_role.roleid
|
|
|
|
INNER JOIN vtiger_sobillads ON vtiger_salesorder.salesorderid = vtiger_sobillads.sobilladdressid
|
|
|
|
INNER JOIN vtiger_salesordercf ON vtiger_salesorder.salesorderid = vtiger_salesordercf.salesorderid
|
|
|
|
LEFT JOIN vtiger_crmentity_user_field ON vtiger_salesorder.salesorderid = vtiger_crmentity_user_field.recordid
|
|
|
|
AND vtiger_crmentity_user_field.userid=1
|
|
|
|
WHERE vtiger_crmentity.deleted=0 AND vtiger_salesorder.salesorderid > 0 AND vtiger_salesorder.sostatus <> 'Cancelled'
|
|
|
|
GROUP by vtiger_crmentity.smownerid";
|
|
|
|
$result = $adb->query($query);
|
|
|
|
|
|
|
|
$res=array();
|
|
|
|
while ($recordinfo = $adb->fetch_array($result)) {
|
|
|
|
$res[] = $recordinfo;
|
|
|
|
}
|
|
|
|
|
|
|
|
$all=array();
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
$total_est=$total_centre=$total_ouest=array();
|
|
|
|
$res_ligns = count($res) ;
|
|
|
|
for( $t=0; $t < $res_ligns ;$t++){
|
|
|
|
if( $res[$t]['rolename'] == 'DELEGUE COMMERCIAL OUEST' ||
|
|
|
|
$res[$t]['rolename'] == "DIRECTEUR GENERAL" ||
|
|
|
|
$res[$t]['rolename'] == "KEY ACCOUNT MANAGER OUEST"){
|
|
|
|
array_push($total_ouest,$res[$t]['total']);
|
|
|
|
}
|
|
|
|
if( $res[$t]['rolename'] == 'DELEGUE COMMERCIAL EST' ||
|
|
|
|
$res[$t]['rolename'] == 'DELEGUE COMMERCIAL EST2' ||
|
|
|
|
$res[$t]['rolename'] == 'SUPERVISEUR COMMERCIAL EST2' ||
|
|
|
|
$res[$t]['rolename'] == 'KEY ACCOUNT MANAGER EST'){
|
|
|
|
array_push($total_est,$res[$t]['total']);
|
|
|
|
}
|
|
|
|
if( $res[$t]['rolename'] == 'KEY ACCOUNT MANAGER CENTRE' ||
|
|
|
|
$res[$t]['rolename'] == 'SUPERVISEUR COMMERCIAL CENTRE' ||
|
|
|
|
$res[$t]['rolename'] == 'DELEGUE COMMERCIAL CENTRE'){
|
|
|
|
array_push($total_centre,$res[$t]['total']);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
array_push($all ,["CENTRE",array_sum($total_centre)]) ;
|
|
|
|
array_push($all ,["EST",array_sum($total_est)]) ;
|
|
|
|
array_push($all ,["OUEST",array_sum($total_ouest)]) ;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
return $all;
|
|
|
|
}
|
|
|
|
|
|
|
|
function getCaParWilaya($year){
|
|
|
|
global $adb;
|
|
|
|
|
|
|
|
|
|
|
|
$query = "SELECT vtiger_accountscf.cf_992 as wilaya ,sum(vtiger_salesorder.total) as total
|
|
|
|
FROM vtiger_salesorder
|
|
|
|
LEFT JOIN vtiger_crmentity ON vtiger_salesorder.salesorderid = vtiger_crmentity.crmid
|
|
|
|
LEFT JOIN vtiger_salesordercf ON vtiger_salesorder.salesorderid = vtiger_salesordercf.salesorderid
|
|
|
|
LEFT JOIN vtiger_accountscf ON vtiger_accountscf.accountid = vtiger_salesorder.accountid
|
|
|
|
WHERE vtiger_crmentity.deleted=0 AND vtiger_salesorder.salesorderid > 0
|
|
|
|
and EXTRACT(YEAR FROM vtiger_salesorder.duedate) = ".$year." AND vtiger_salesorder.sostatus <> 'Cancelled'
|
|
|
|
and vtiger_accountscf.cf_992 is not null
|
|
|
|
GROUP by vtiger_accountscf.cf_992
|
|
|
|
ORDER BY `total` DESC LIMIT 0,25";
|
|
|
|
$result = $adb->query($query);
|
|
|
|
|
|
|
|
$res=array();
|
|
|
|
while ($recordinfo = $adb->fetch_array($result)) {
|
|
|
|
$res[] = $recordinfo;
|
|
|
|
}
|
|
|
|
|
|
|
|
return $res;
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
switch ($_GET['action']) {
|
|
|
|
|
|
|
|
case "all":
|
|
|
|
$allcharts =[];
|
|
|
|
$allcharts['nbr_produits'] = getProduits();
|
|
|
|
$allcharts['nbr_pharmacie'] = getCompte('Pharmacie');
|
|
|
|
$allcharts['nbr_medecin'] = getCompte('Medecin');
|
|
|
|
$allcharts['nbr_delegue_commercial'] = getUsers('DELEGUE COMMERCIAL');
|
|
|
|
$allcharts['nbr_delegue_medical'] = getUsers('DELEGUE MEDICAL');
|
|
|
|
$allcharts['nbr_superviseur_commercial'] = getUsers('SUPERVISEUR COMMERCIAL');
|
|
|
|
$allcharts['nbr_superviseur_medical'] = getUsers('SUPERVISEUR MEDICAL');
|
|
|
|
$allcharts['nbr_kam'] = getUsers('kam');
|
|
|
|
$allcharts['pack_ca'] = getPackCA();
|
|
|
|
$allcharts['pharmacie_region'] = getCompteParRegion('Pharmacie');
|
|
|
|
$allcharts['medecin_region'] = getCompteParRegion('Medecin');
|
|
|
|
$allcharts['nbr_vp_region'] = getUserParRegion('DELEGUE COMMERCIAL');
|
|
|
|
$allcharts['nbr_vm_region'] = getUserParRegion('DELEGUE MEDICAL');
|
|
|
|
$allcharts['ca_par_region'] = getCaParRegion();
|
|
|
|
$allcharts['ca_par_wilaya'] = getCaParWilaya($year);
|
|
|
|
|
|
|
|
$result = preg_replace('/[\x00-\x1F\x80-\xFF]/', '', json_encode($allcharts));
|
|
|
|
/*
|
|
|
|
$result = preg_replace('{^\xEF\xBB\xBF|\x1A}', '', json_encode($allcharts));
|
|
|
|
$result = preg_replace('/[[:^print:]]/', "", $result);
|
|
|
|
$result = ltrim($result, chr(239).chr(187).chr(191));
|
|
|
|
echo preg_replace( '/[^[:print:]]/', '',json_encode($allcharts));
|
|
|
|
*/
|
|
|
|
//header("Content-Type: application/json; charset=UTF-8");
|
|
|
|
header('Content-Type: text/html; charset=UTF-8');
|
|
|
|
echo $result;
|
|
|
|
|
|
|
|
|
|
|
|
break;
|
|
|
|
|
|
|
|
} |