text: CREATE DEFINER=`root`@`localhost` PROCEDURE `prc_getVendorDescReportAll`(
  	IN `p_CompanyID` INT,
  	IN `p_CompanyGatewayID` INT,
  	IN `p_AccountID` INT,
  	IN `p_CurrencyID` INT,
  	IN `p_StartDate` DATETIME,
  	IN `p_EndDate` DATETIME,
  	IN `p_AreaPrefix` VARCHAR(50),
  	IN `p_Trunk` VARCHAR(50),
  	IN `p_CountryID` INT,
  	IN `p_UserID` INT,
  	IN `p_isAdmin` INT,
  	IN `p_PageNumber` INT,
  	IN `p_RowspPage` INT,
  	IN `p_lSortCol` VARCHAR(50),
  	IN `p_SortOrder` VARCHAR(5),
  	IN `p_isExport` INT
  )
  BEGIN
  
  	DECLARE v_Round_ INT;
  	DECLARE v_OffSet_ INT;
  
  	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
  	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
  
  	SELECT fnGetRoundingPoint(p_CompanyID) INTO v_Round_;
  
  	CALL fngetDefaultCodes(p_CompanyID);
  
  	CALL fnUsageVendorSummary(p_CompanyID,p_CompanyGatewayID,p_AccountID,p_CurrencyID,p_StartDate,p_EndDate,p_AreaPrefix,p_Trunk,p_CountryID,p_UserID,p_isAdmin,2);
  
  	/* grid display*/
  	IF p_isExport = 0
  	THEN
  
  		/* Description by call count */	
  			
  		SELECT IFNULL(Description,'Other') AS Description ,FORMAT(SUM(NoOfCalls),0) AS CallCount,FORMAT(COALESCE(SUM(TotalBilledDuration),0)/ 60,0) AS TotalSeconds,FORMAT(COALESCE(SUM(TotalCharges),0), v_Round_) AS TotalCost,IF(SUM(NoOfCalls)>0,fnDurationmmss(COALESCE(SUM(TotalBilledDuration),0)/SUM(NoOfCalls)),0) AS ACD , IF(SUM(NoOfCalls)>0,ROUND(SUM(NoOfCalls)/(SUM(NoOfCalls)+SUM(NoOfFailCalls))*100,v_Round_),0) AS ASR,
  			FORMAT(COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0), v_Round_) as TotalMargin,
  			FORMAT( (COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0)) / SUM(TotalSales)*100, v_Round_) as MarginPercentage
  		FROM tmp_tblUsageVendorSummary_ us
  		LEFT JOIN tmp_codes_ c ON c.Code = us.AreaPrefix
  		GROUP BY c.Description   
  		ORDER BY
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CallCountDESC') THEN SUM(NoOfCalls)
  		END DESC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CallCountASC') THEN SUM(NoOfCalls)
  		END ASC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TotalMinutesDESC') THEN COALESCE(SUM(TotalBilledDuration),0)
  		END DESC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TotalMinutesASC') THEN COALESCE(SUM(TotalBilledDuration),0)
  		END ASC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionDESC') THEN Description
  		END DESC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionASC') THEN Description
  		END ASC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TotalCostDESC') THEN ROUND(COALESCE(SUM(TotalCharges),0), v_Round_)
  		END DESC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TotalCostASC') THEN ROUND(COALESCE(SUM(TotalCharges),0), v_Round_)
  		END ASC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ACDDESC') THEN (COALESCE(SUM(TotalBilledDuration),0)/SUM(NoOfCalls))
  		END DESC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ACDASC') THEN (COALESCE(SUM(TotalBilledDuration),0)/SUM(NoOfCalls))
  		END ASC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ASRDESC') THEN SUM(NoOfCalls)/(SUM(NoOfCalls)+SUM(NoOfFailCalls))*100
  		END DESC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ASRASC') THEN SUM(NoOfCalls)/(SUM(NoOfCalls)+SUM(NoOfFailCalls))*100
  		END ASC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TotalMarginDESC') THEN ROUND(COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0), v_Round_)
  		END DESC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TotalMarginASC') THEN ROUND(COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0), v_Round_)
  		END ASC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'MarginPercentageDESC') THEN ROUND( (COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0)) / SUM(TotalSales)*100, v_Round_)
  		END DESC,
  		CASE
  			WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'MarginPercentageASC') THEN ROUND( (COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0)) / SUM(TotalSales)*100, v_Round_)
  		END ASC
  		LIMIT p_RowspPage OFFSET v_OffSet_;
  
  		SELECT COUNT(*) AS totalcount,FORMAT(SUM(CallCount),0) AS TotalCall,FORMAT(SUM(TotalSeconds)/60,0) AS TotalDuration,FORMAT(SUM(TotalCost),v_Round_) AS TotalCost,FORMAT(SUM(TotalMargin),v_Round_) AS TotalMargin FROM (
  			SELECT IFNULL(Description,'Other') AS Description ,SUM(NoOfCalls) AS CallCount,COALESCE(SUM(TotalBilledDuration),0) AS TotalSeconds,ROUND(COALESCE(SUM(TotalCharges),0), v_Round_) AS TotalCost,IF(SUM(NoOfCalls)>0,fnDurationmmss(COALESCE(SUM(TotalBilledDuration),0)/SUM(NoOfCalls)),0) AS ACD , IF(SUM(NoOfCalls)>0,ROUND(SUM(NoOfCalls)/(SUM(NoOfCalls)+SUM(NoOfFailCalls))*100,v_Round_),0) AS ASR,
  				ROUND(COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0), v_Round_) as TotalMargin,
  				ROUND( (COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0)) / SUM(TotalSales)*100, v_Round_) as MarginPercentage
  			FROM tmp_tblUsageVendorSummary_ us
  			LEFT JOIN tmp_codes_ c ON c.Code = us.AreaPrefix
  			GROUP BY c.Description
  		)tbl;
  
  	END IF;
  
  	/* export data*/
  	IF p_isExport = 1
  	THEN
  
  		SELECT
  			SQL_CALC_FOUND_ROWS IFNULL(Description,'Other') AS Description,
  			SUM(NoOfCalls) AS `No. of Calls`,
  			ROUND(COALESCE(SUM(TotalBilledDuration),0)/ 60,0) as `Billed Duration (Min.)`,
  			ROUND(COALESCE(SUM(TotalCharges),0), v_Round_) as `Charged Amount`,
  			IF(SUM(NoOfCalls)>0,fnDurationmmss(COALESCE(SUM(TotalBilledDuration),0)/SUM(NoOfCalls)),0) as `ACD (mm:ss)`,
  			ROUND(SUM(NoOfCalls)/(SUM(NoOfCalls)+SUM(NoOfFailCalls))*100,v_Round_) as `ASR (%)`,
  			ROUND(COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0), v_Round_) as `Margin`,
  			ROUND( (COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0)) / SUM(TotalSales)*100, v_Round_) as `Margin (%)`
  		FROM tmp_tblUsageVendorSummary_ us
  		LEFT JOIN tmp_codes_ c ON c.Code = us.AreaPrefix
  		GROUP BY Description;
  
  	END IF;
  
  	/* chart display*/
  	IF p_isExport = 2
  	THEN
  
  		/* top 10 Description by call count */
  		
  		SELECT Description AS ChartVal ,SUM(NoOfCalls) AS CallCount,IF(SUM(NoOfCalls)>0,fnDurationmmss(COALESCE(SUM(TotalBilledDuration),0)/SUM(NoOfCalls)),0) AS ACD , ROUND(SUM(NoOfCalls)/(SUM(NoOfCalls)+SUM(NoOfFailCalls))*100,v_Round_) AS ASR,
  			ROUND(COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0), v_Round_) as TotalMargin,
  			ROUND( (COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0)) / SUM(TotalSales)*100, v_Round_) as MarginPercentage
  		FROM tmp_tblUsageVendorSummary_ us
  		INNER JOIN tmp_codes_ c ON c.Code = us.AreaPrefix
  		GROUP BY Description HAVING SUM(NoOfCalls) > 0 ORDER BY CallCount DESC LIMIT 10;
  
  		/* top 10 Description by call cost */
  		
  		SELECT Description AS ChartVal,ROUND(COALESCE(SUM(TotalCharges),0), v_Round_) AS TotalCost,IF(SUM(NoOfCalls)>0,fnDurationmmss(COALESCE(SUM(TotalBilledDuration),0)/SUM(NoOfCalls)),0) AS ACD , ROUND(SUM(NoOfCalls)/(SUM(NoOfCalls)+SUM(NoOfFailCalls))*100,v_Round_) AS ASR,
  			ROUND(COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0), v_Round_) as TotalMargin,
  			ROUND( (COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0)) / SUM(TotalSales)*100, v_Round_) as MarginPercentage
  		FROM tmp_tblUsageVendorSummary_ us
  		INNER JOIN tmp_codes_ c ON c.Code = us.AreaPrefix
  		GROUP BY Description HAVING SUM(TotalCharges) > 0 ORDER BY TotalCost DESC LIMIT 10;
  
  		/* top 10 Description by call minutes */
  		
  		SELECT Description AS ChartVal,ROUND(COALESCE(SUM(TotalBilledDuration),0)/ 60,0) AS TotalMinutes,IF(SUM(NoOfCalls)>0,fnDurationmmss(COALESCE(SUM(TotalBilledDuration),0)/SUM(NoOfCalls)),0) AS ACD , ROUND(SUM(NoOfCalls)/(SUM(NoOfCalls)+SUM(NoOfFailCalls))*100,v_Round_) AS ASR,
  			ROUND(COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0), v_Round_) as TotalMargin,
  			ROUND( (COALESCE(SUM(TotalSales),0) - COALESCE(SUM(TotalCharges),0)) / SUM(TotalSales)*100, v_Round_) as MarginPercentage
  		FROM tmp_tblUsageVendorSummary_ us
  		INNER JOIN tmp_codes_ c ON c.Code = us.AreaPrefix
  		GROUP BY Description HAVING SUM(TotalBilledDuration) > 0  ORDER BY TotalMinutes DESC LIMIT 10;
  
  	END IF;
  
  	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  
  END