Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [Adayroi_TMS]
- GO
- /****** Object: StoredProcedure [dbo].[APIClient_Sodetail_GetProductTms_V01] Script Date: 12/15/2017 9:42:53 AM ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --====================================================================================
- -- Entity Name: APIClient_Sodetail_GetProductTms_V01
- -- Author: Trần an Bình
- -- Create date: 15/12/2014 8:32:27 PM
- -- Description: lấy danh sách serail
- -- ================================================================================
- ALTER PROCEDURE [dbo].[APIClient_Sodetail_GetProductTms_V01]
- (
- @Soid bigint,
- @Deliveryfrom datetime,
- @SourceClientId int = 0
- )
- AS
- BEGIN
- If (@SourceClientId is null OR @SourceClientId = 0)
- BEGIN
- Set @SourceClientId = 1
- END
- If(@SourceClientId = 1)
- BEGIN
- SELECT DISTINCT
- [Soid],
- [PoNumber],
- [ProductItemId],
- [ProductName],
- [ProductType],
- [Quantity]
- FROM
- (SELECT DISTINCT
- sd.ClientSoId as [Soid],
- sd.ClientPoId as [PoNumber],
- cp.ProductItemId as [ProductItemId],
- cp.ProductName as [ProductName],
- sd.ProductTypeID as [ProductType],
- sd.Amount as [Quantity]
- FROM
- dbo.SO_Detail sd WITH (NOLOCK)
- INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = sd.ClientProductId
- INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.ProductItemId = cp.ProductItemId AND pcdt.Soid = sd.ClientSoId)
- WHERE
- sd.ClientSoId = @Soid AND pcdt.Push = 0
- AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
- AND DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
- --ORDER BY
- --cp.ClientProductId DESC
- UNION ALL
- SELECT DISTINCT
- @Soid as [Soid],
- pofm.ClientPoId as [PoNumber],
- pofm.ProductItemId as [ProductItemId],
- pof.PoFeeName as [ProductName],
- 1 AS [ProductType], --hàng binh thường
- 1 as [Quantity]
- FROM
- dbo.PurchaseOrder po WITH (NOLOCK)
- INNER JOIN dbo.PurchaseOrder_Fee_Map pofm WITH(NOLOCK) ON pofm.ClientPoId= po.ClientPoId
- INNER JOIN dbo.PurchaseOrder_Fee pof WITH(NOLOCK) ON pof.PoFeeId =pofm.PoFeeId
- INNER JOIN dbo.SO s WITH(NOLOCK) ON s.SOID = po.SoId
- INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.Soid = s.ClientSoId AND po.PoId= pcdt.PoNumber)
- WHERE
- s.ClientSoId = @Soid
- AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
- AND DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
- AND pofm.Status =1
- ) AS t
- UPDATE PushCsDateTime
- SET NumCount = NumCount + 1
- FROM PushCsDateTime push
- INNER JOIN dbo.SO s WITH(nolock) ON push.Soid =s.ClientSoId
- WHERE push.Soid =@Soid AND push.Push = 0
- AND s.SourceClientId =@SourceClientId
- END
- ELSE
- BEGIN
- SELECT DISTINCT
- [Soid],
- [PoNumber],
- [ProductItemId],
- [ProductName],
- [ProductType],
- [Quantity]
- FROM
- (SELECT DISTINCT
- sd.ClientSoId as [Soid],
- sd.ClientPoId as [PoNumber],
- cp.ProductItemId as [ProductItemId],
- cp.ProductName as [ProductName],
- sd.ProductTypeID as [ProductType],
- sd.Amount as [Quantity]
- FROM
- dbo.SO_Detail sd WITH (NOLOCK)
- INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = sd.ClientProductId
- INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.Soid = sd.ClientSoId AND pcdt.SODetailID = sd.SODetailID AND sd.Visible =1)
- WHERE
- sd.ClientSoId = @Soid AND pcdt.Push = 0
- AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
- AND DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
- --ORDER BY
- --cp.ClientProductId DESC
- UNION ALL
- SELECT DISTINCT
- @Soid as [Soid],
- pofm.ClientPoId as [PoNumber],
- pofm.ProductItemId as [ProductItemId],
- pof.PoFeeName as [ProductName],
- 1 AS [ProductType], --hàng binh thường
- 1 as [Quantity]
- FROM
- dbo.PurchaseOrder po WITH (NOLOCK)
- INNER JOIN dbo.PurchaseOrder_Fee_Map pofm WITH(NOLOCK) ON pofm.ClientPoId= po.ClientPoId
- INNER JOIN dbo.PurchaseOrder_Fee pof WITH(NOLOCK) ON pof.PoFeeId =pofm.PoFeeId
- INNER JOIN dbo.SO s WITH(NOLOCK) ON s.SOID = po.SoId
- INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.Soid = s.ClientSoId AND po.PoId= pcdt.PoNumber)
- WHERE
- s.ClientSoId = @Soid
- AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
- AND DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
- AND pofm.Status =1
- ) AS t
- END
- END
- --SELECT * FROM dbo.SO_Detail sd WHERE sd.ClientSoId =160146296
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement