<?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;

 
		}