CREATE PROCEDURE [dbo].[SalesReport] @AFromDate DATETIME, @AToDate DATETIME, @AHotelNumberStr VARCHAR(8000), @ACustomerNumberStr VARCHAR(8000), @ACategoryGroupNumberStr VARCHAR(8000), 
  @ACategoryNumberStr VARCHAR(8000), @AUserIdNumberStr VARCHAR(8000) AS
SET NOCOUNT ON

/*
  Created by Kent Johansen April 2008

  Changes:
  KJ 20081128: Ext: Category group text
  KJ 20080429: Bug: Fix for "Cannot convert the varchar value '0,1,2' to int"

*/

DECLARE @CentralBookingType INT
SELECT @CentralBookingType = CONVERT(INT, ISNULL(KeyValue, 0)) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'CentralBookingType'

SELECT COUNT(DISTINCT f.followupnumber) AS cnt, p.UserIdCode, p.UserIdNumber, c.Text, cg.GText
FROM Followup f WITH (NOLOCK)
INNER JOIN CustomerXTabFollowup cfx WITH (NOLOCK) ON f.FollowupNumber = cfx.FollowupNumber
  AND cfx.CustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ACustomerNumberStr))
INNER JOIN FollowupCategoryGroupXTab fcgx WITH (NOLOCK) ON f.FollowupNumber = fcgx.FollowupNumber
  AND fcgx.CategoryGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ACategoryGroupNumberStr))
INNER JOIN CategoryGroup cg WITH (NOLOCK) ON cg.GroupNumber = fcgx.CategoryGroupNumber
INNER JOIN FollowupCategoryXTab fcx WITH (NOLOCK) ON f.FollowupNumber = fcx.FollowupNumber
  AND fcx.CategoryNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ACategoryNumberStr))
INNER JOIN Category c WITH (NOLOCK) ON c.CategoryNumber = fcx.CategoryNumber
INNER JOIN PersonalFollowupXTab pfx WITH (NOLOCK) ON f.FollowupNumber = pfx.FollowupNumber
  AND pfx.UserIdNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AUserIdNumberStr))
INNER JOIN Personel p WITH (nolock) ON p.UserIdNumber = pfx.UserIdNumber
WHERE (@CentralBookingType = 0 OR (@CentralBookingType = 7 AND f.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
AND f.Date BETWEEN @AFromDate AND @AToDate+1
GROUP BY p.UserIdCode, p.UserIdNumber, c.Text, cg.GText