ALTER proc [dbo].[crm_repReclamation]
@XmlParam xml = null,
@DrillDownField varchar(max) = null,
@DrillDownParams varchar(max) = null,
@UserID int
as
/* Получение параметров фильтра виджета */
declare @Manager int = null, @Department int = null, @ContactType varchar(max) = null
declare @Params table ([Name] varchar(max), [From] varchar(max), [To] varchar(max), [Value] varchar(max))
insert into @Params ([Name], [From], [To], [Value])
exec crm_repGetParams @XML = @XmlParam
select @Manager = nullif([Value],0)
from @Params where Name = 'Manager'
select @Department = nullif([Value],0)
from @Params where Name = 'Department'
select @ContactType = nullif([Value],'')
from @Params where Name = 'ContactType'
declare @UrlMTF varchar(max) = '../MainTaskForm.aspx?TaskID='
declare @Managers table (ManagerID int, ManagerName varchar(max))
insert into @Managers
exec crm_repGetManagers @UserID = @UserID, @ManagerID = @Manager, @UnitID = @Department
select r.TaskID,
@UrlMTF + cast(r.TaskID as varchar) TaskUrl,
r.CreatedTime, r.CompanyID, r.CompanyName, c.ManagerName, r.TaskText, r.StateDescription,
r.Comments,
r.ClientNotify, r.AffirmationReceive, r.DecisionMade,
@UrlMTF + cast(c.TaskID as varchar) CompanyUrl
from vCRMReclamations r
join vCRMCompanies c on c.TaskID = r.CompanyID
join @Managers m on m.ManagerID = c.ManagerID
where r.IsClosed = 0
|