public static function GetSLA($siteID, $userID, $categoryID = null, $coupon = "")
{
global $DB;
$err_mess = CAllTicketSLA::err_mess() . "<br>Function: GetSLA<br>Line: ";
$userID = intval($userID);
if (strlen($coupon) > 0) {
$rsCoupon = CSupportSuperCoupon::GetList(false, array('COUPON' => $coupon));
if ($arCoupon = $rsCoupon->Fetch()) {
if (intval($arCoupon['SLA_ID']) > 0) {
return intval($arCoupon['SLA_ID']);
}
}
}
$slaID = COption::GetOptionString("support", "SUPPORT_DEFAULT_SLA_ID");
$OLD_FUNCTIONALITY = COption::GetOptionString("support", "SUPPORT_OLD_FUNCTIONALITY", "Y");
if ($OLD_FUNCTIONALITY == "Y") {
$categoryID = null;
}
$JOIN = "";
$fields = "1";
if ($categoryID != null) {
$categoryID = intval($categoryID);
$fields = "CASE\n\t\t\t\t\t\tWHEN SC.SLA_ID IS NOT NULL THEN 1\n\t\t\t\t\t\tELSE 0\n\t\t\t\t\tEND";
$JOIN .= "\t\n\t\t\t\t\tLEFT JOIN b_ticket_sla_2_category SC\n\t\t\t\t\t\tON S.ID = SC.SLA_ID\n\t\t\t\t\t\t\tAND ( SC.CATEGORY_ID = 0 OR SC.CATEGORY_ID = {$categoryID} )";
}
if ($userID === 0) {
// guest: default site sla for usergroup #2
$groupJoin = "UG.GROUP_ID = 2";
} else {
// sla for this user
$groupJoin = "((UG.USER_ID = {$userID} ";
$groupJoin .= " AND ((UG.DATE_ACTIVE_FROM IS NULL) OR (UG.DATE_ACTIVE_FROM <= " . $DB->CurrentTimeFunction() . ")) ";
$groupJoin .= " AND ((UG.DATE_ACTIVE_TO IS NULL) OR (UG.DATE_ACTIVE_TO >= " . $DB->CurrentTimeFunction() . ")) ";
$groupJoin .= ") OR UG.GROUP_ID = 2)";
}
$strSql = "\n\t\t\tSELECT\n\t\t\t\tPZ.SLA_ID\n\t\t\tFROM\n\t\t\t(\n\t\t\t\tSELECT\n\t\t\t\t\tSG.SLA_ID SLA_ID,\n\t\t\t\t\t{$fields} PRIORITY1,\n\t\t\t\t\tS.PRIORITY PRIORITY2\n\t\t\t\tFROM\n\t\t\t\t\tb_ticket_sla S\n\t\t\t\t\tINNER JOIN b_ticket_sla_2_site SS\n\t\t\t\t\t\tON S.ID = SS.SLA_ID\n\t\t\t\t\t\t\tAND ( SS.SITE_ID = 'ALL' OR SS.SITE_ID = '{$siteID}' ) \n\t\t\t\t\tINNER JOIN b_ticket_sla_2_user_group SG\n\t\t\t\t\t\tON S.ID = SG.SLA_ID\n\t\t\t\t\tINNER JOIN b_user_group UG\n\t\t\t\t\t\tON SG.GROUP_ID = UG.GROUP_ID\n\t\t\t\t\t\t\tAND {$groupJoin}\n\t\t\t\t\t{$JOIN}\n\t\t\t) PZ\n\t\t\tGROUP BY\n\t\t\t\tPZ.SLA_ID, PZ.PRIORITY1, PZ.PRIORITY2\n\t\t\tORDER BY\n\t\t\t\tPZ.PRIORITY1 DESC, PZ.PRIORITY2 DESC\n\t\t\t\t\t\t\t\n\t\t";
$rs = $DB->Query($strSql, false, $err_mess . __LINE__);
if ($ar = $rs->Fetch()) {
if (is_array($ar) && array_key_exists("SLA_ID", $ar)) {
$slaID = $ar["SLA_ID"];
}
}
return $slaID;
}
function GetSLA( $siteID, $userID, $categoryID = null, $coupon = "" )
{
global $DB;
$err_mess = (CAllTicketSLA::err_mess())."<br>Function: GetSLA<br>Line: ";
$userID = intval($userID);
if( strlen( $coupon ) > 0 )
{
$rsCoupon = CSupportSuperCoupon::GetList( false, array( 'COUPON' => $coupon ) );
if($arCoupon = $rsCoupon->Fetch())
{
if(intval($arCoupon['SLA_ID'] ) > 0)
{
return intval( $arCoupon['SLA_ID'] );
}
}
}
$slaID = COption::GetOptionString( "support", "SUPPORT_DEFAULT_SLA_ID" );
$OLD_FUNCTIONALITY = COption::GetOptionString( "support", "SUPPORT_OLD_FUNCTIONALITY", "Y" );
if( $OLD_FUNCTIONALITY == "Y" )
{
$categoryID = null;
}
$JOIN = "";
$fields = "1";
if( $categoryID != null )
{
$categoryID = intval( $categoryID );
$fields = "CASE
WHEN SC.SLA_ID IS NOT NULL THEN 1
ELSE 0
END";
$JOIN .= "
LEFT JOIN b_ticket_sla_2_category SC
ON S.ID = SC.SLA_ID
AND ( SC.CATEGORY_ID = 0 OR SC.CATEGORY_ID = $categoryID )";
}
if ($userID === 0)
{
// guest: default site sla for usergroup #2
$groupJoin = "UG.GROUP_ID = 2";
}
else
{
// sla for this user
$groupJoin = "(UG.USER_ID = $userID OR UG.GROUP_ID = 2)";
}
$strSql = "
SELECT
PZ.SLA_ID
FROM
(
SELECT
SG.SLA_ID SLA_ID,
$fields PRIORITY1,
S.PRIORITY PRIORITY2
FROM
b_ticket_sla S
INNER JOIN b_ticket_sla_2_site SS
ON S.ID = SS.SLA_ID
AND ( SS.SITE_ID = 'ALL' OR SS.SITE_ID = '$siteID' )
INNER JOIN b_ticket_sla_2_user_group SG
ON S.ID = SG.SLA_ID
INNER JOIN b_user_group UG
ON SG.GROUP_ID = UG.GROUP_ID
AND $groupJoin
$JOIN
) PZ
GROUP BY
PZ.SLA_ID, PZ.PRIORITY1, PZ.PRIORITY2
ORDER BY
PZ.PRIORITY1 DESC, PZ.PRIORITY2 DESC
";
$rs = $DB->Query($strSql, false, $err_mess.__LINE__);
if( $ar = $rs->Fetch() )
{
if( is_array( $ar ) && array_key_exists( "SLA_ID", $ar ) )
{
$slaID = $ar["SLA_ID"];
}
}
return $slaID;
}
