USE [DALZ]
GO
/****** Object:  StoredProcedure [dbo].[CustomsAction]    Script Date: 03.11.2018 20:14:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CustomsAction]
    @Option [int],
	@pCustomID [uniqueidentifier],
	@pParentCustomID [uniqueidentifier],
	@pCustomNo [nvarchar](20),
	@pCustomTime [datetime],
	@pStatus [smallint],
	@pAgentID [int],
	@pAgentName [nvarchar](50),
	@pAddressID [int],
	@pAddress [nvarchar](30),
	@pContactID [int],
	@pContactName [nvarchar](100),
	@pDealID [int],
	@pDealName [nvarchar](50),
	@pDealContactID [int],
	@pDealContactName [nvarchar](100),
	@pWorkPlace [bit],
	@pDogovorID [int],
	@pWorkTime [nvarchar](20),
	@pNote [nvarchar](100),
	@pCustomType [int],
	@pCustomTerm [int],
	@pSLANo [nvarchar](20),
	@pBestTime [nvarchar](20),
	@pisEntryDoc [bit],
	@pCreateDoc [smallint],
	@pJobTime [datetime],
	@pBorderJobTime [datetime],
	@pisPayWait [bit],
	@pCustomSum [money],
	@pisCountNeed [bit],
	@pFinDoc [nvarchar](50),
	@pisFinDoc [bit],
	@pisZipNeed [bit],
	@pZipCount [nvarchar](30),
	@pisZipPresent [bit],
	@pWaitTime [int],
	@pisPay [bit],
	@pDogovorNo [nvarchar](20),
	@pContinueTime [datetime],
    @pSortStr [nvarchar](150)
 
 AS

BEGIN
DECLARE @SQL nvarchar(max);

IF @Option = 0
 BEGIN
  SELECT 0 as [Action], c.CustomID, c.ParentCustomID, c.CustomNo, CAST(c.CustomTime as datetime) CustomTime, c.[Status],
         CASE WHEN c.[Status] = 1 THEN 'Предварительая Заявка - ПЗ' ELSE
          CASE WHEN c.[Status] = 2 THEN	'Заявка в Работе - ЗР' ELSE
		   CASE WHEN c.[Status] = 3	THEN 'Распределенная Заявка - Раб' ELSE
		    CASE WHEN c.[Status] = 7 THEN 'Отмененая заявка - Отм' ELSE
			 CASE WHEN c.[Status] = 5 THEN 'Выполненная Заявка - Вып' ELSE
			  CASE WHEN c.[Status] = 6 THEN 'Закрытая Заявка - Зак' ELSE
			   CASE WHEN c.[Status] = 8 THEN 'Рекомендовано - Рек' ELSE
			    CASE WHEN c.[Status] = 4 THEN 'Отложенная Заявка - Отл' ELSE ''
				 END END END END END END END END StatusName,
	c.AgentID, c.AgentName, c.AddressID, c.Address,	c.ContactID, c.ContactName,	c.DealID, c.DealName, c.DealContactID, c.DealContactName, c.WorkPlace,
	c.DogovorID, c.WorkTime, c.Note, c.CustomType, ct.CustomTypeName, c.CustomTerm, ctt.CustomTermName, c.SLANo, c.BestTime, c.isEntryDoc, c.CreateDoc,
	CAST(c.JobTime as datetime) JobTime, Cast(c.BorderJobTime as datetime) BorderJobTime, c.isPayWait, c.isPay,c.CustomSum,	c.isCountNeed, c.FinDoc, 
	c.isFinDoc, c.isZipNeed, c.ZipCount, c.isZipPresent, dog.DogovorNo, CAST(c.ContinueTime as datetime) ContinueTime,
	(SELECT TOP 1 con.FIO FROM  dbo.CustomsEng ce left outer join dbo.Contacts con on con.ContactID = ce.EngID WHERE ce.CustomID = c.CustomID) Eng,
    (SELECT TOP 1 cd.BrandName + ' ' + cd.ModelName + '-' + cd.Serial FROM dbo.CustomsDevice cd WHERE cd.CustomID = c.CustomID) Device
  FROM dbo.Customs c left outer join dbo.CustomTypes ct on ct.CustomType = c.CustomType
                     left outer join dbo.CustomTerms ctt on ctt.CustomTerm = c.CustomTerm
					 left outer join dbo.Dogovor dog on dog.DogovorID = c.DogovorID
 END

 BEGIN TRANSACTION


IF @Option = 1
 INSERT INTO dbo.Customs
 VALUES ( @pCustomID, @pParentCustomID, @pCustomNo, @pCustomTime, @pStatus, @pAgentID,	@pAgentName, @pAddressID, @pAddress, @pContactID, @pContactName, @pDealID, @pDealName,
	      @pDealContactID, @pDealContactName, @pWorkPlace, @pDogovorID,	@pWorkTime,	@pNote,	@pCustomType, @pCustomTerm,	@pSLANo, @pBestTime, @pisEntryDoc, @pCreateDoc,
	      @pJobTime, @pBorderJobTime, @pisPayWait, @pCustomSum, @pisCountNeed, @pFinDoc, @pisFinDoc,	@pisZipNeed, @pZipCount, @pisZipPresent, @pWaitTime, @pisPay, @pContinueTime)
 
IF @Option = 4
 UPDATE dbo.Customs
 SET ParentCustomID = @pParentCustomID,
	 CustomNo = @pCustomNo,
	 CustomTime = @pCustomTime,
	 [Status] = @pStatus,
	 AgentID = @pAgentID,
	 AgentName = @pAgentName,
	 AddressID = @pAddressID,
	 [Address] = @pAddress,
	 ContactID = @pContactID,
	 ContactName = @pContactName,
	 DealID = @pDealID,
	 DealName = @pDealName,
	 DealContactID = @pDealContactID,
	 DealContactName = @pDealContactName,
	 WorkPlace = @pWorkPlace,
	 DogovorID = @pDogovorID,
	 WorkTime = @pWorkTime,
	 Note = @pNote,
	 CustomType = @pCustomType,
	 CustomTerm = @pCustomTerm,
	 SLANo = @pSLANo,
	 BestTime = @pBestTime,
	 isEntryDoc = @pisEntryDoc,
	 CreateDoc = @pCreateDoc,
	 JobTime = @pJobTime,
	 BorderJobTime = @pBorderJobTime,
	 isPayWait = @pisPayWait,
	 CustomSum = @pCustomSum,
	 isCountNeed = @pisCountNeed,
	 FinDoc = @pFinDoc,
	 isFinDoc = @pisFinDoc,
	 isZipNeed = @pisZipNeed,
	 ZipCount = @pZipCount,
	 isZipPresent = @pisZipPresent,
	 WaitTime = @pWaitTime,
	 isPay = @pisPay,
	 ContinueTime = @pContinueTime
 WHERE CustomID = @pCustomID

COMMIT TRANSACTION
 END