/*
--==========================================================================================
-- Generated By: Changyong Jiang
-- Create Date: 2013/03/25
-- Description: 返回取得的DataInconsistency Alert的數據
-- Generated By: Changyong Jiang
-- Create Date: 2013/05/24
-- Description:修改時間由原來的獲取上本周的數據，改為獲取昨天的數據
-- Generated By: Changyong Jiang
-- Create Date: 2013/06/05
-- Description: data dump dumpload order by
--==========================================================================================
CREATE PROCEDURE [dbo].[Alt_Read_ROHSDataDUMP_SP]
@TransType VARCHAR(100) = '',
@UserName VARCHAR(100) = '',
@ProgramName VARCHAR(MAX) = '',
@Owner VARCHAR(MAX) = '',
@strdate VARCHAR(10) = null,
@DataType VARCHAR(20)='',
@PageIndex INT=1,
@PageSize INT=20
AS
SET NOCOUNT ON
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @_RowCount INT
DECLARE @_PageCount INT
DECLARE @_StartRow INT
CREATE TABLE #Temp(
Iden BIGINT IDENTITY(1, 1),
[DataType] VARCHAR(30),
[FileName] VARCHAR(200),
[FilePath] VARCHAR(2000),
[CreatedDate] varchar(10)
)
CREATE TABLE #newTemp(
[DataType] VARCHAR(30),
[FileName] VARCHAR(200),
[FilePath] VARCHAR(2000),
[CreatedDate] varchar(10)
)
set @strdate=convert(varchar(10),@strdate,120)
--SELECT convert(varchar(10),GETDATE(),120)
--SET @strdate='2013-05-08'
IF @strdate IS NULL
BEGIN
IF @TransType = 'ROHSDATADUMP_ALERT'
OR @TransType = 'ROHSDATADUMP_DOWNLOAD'
BEGIN
SET @strdate = convert(varchar(10),DATEADD(DAY,-1, GETDATE()),120)
END
END
IF @TransType='ROHSDATADUMP_ALERT'
BEGIN
--解決前臺傳入多個owner
DECLARE @TempOwner TABLE (_Owner VARCHAR(30))
DECLARE @TempProgramName TABLE (_ProgramName VARCHAR(30))
INSERT INTO @TempOwner
(
_Owner
)
SELECT adapt_object FROM [dbo].[StringToArray](@Owner,',')
INSERT INTO @TempProgramName
(
_ProgramName
)
SELECT adapt_object FROM [dbo].[StringToArray](@ProgramName,',')
SELECT
rh.[MPasite] AS [MPa site],
rh.[Program] AS [Program],
rh.[SKU] AS [SKU],
rh.[BOM_Level] AS [BOM Level],
rh.[HPPN] AS [HP PN],
rh.[Description] AS [Description],
rh.[Material_Group] AS [Material Group],
rh.[Control_Code] AS [Control Code],
rh.[Mat_Type] AS [Mat_Type],
rh.[Make2Buy] AS [Make/Buy],
rh.[MFGR] AS [MFGR],
rh.[MPN] AS [MPN],
rh.[Lowest_RoHS_ID] AS [Lowest_RoHS_ID],
rh.[Highest_RoHS_ID] AS [Highest_RoHS_ID],
rh.[Last_Upload_Date] AS [Last Upload Date],
rh.[Change_By] AS [Change By] ,
rh.[SDoC] AS [SDoC] ,
rh.[G_template] AS [G-template]
FROM RoHSDataDump_History_BomTree(NOLOCK) rh
--WHERE CONVERT(varchar(10),rh.[strDate])=@strdate
WHERE rh.[strDate]=@strdate
AND rh.[MPasite] in (SELECT _Owner FROM @TempOwner)
and rh.[Program] in (SELECT _ProgramName FROM @TempProgramName)
ORDER BY rh.[MPasite],rh.[Program],rh.[SKU],rh.[intShowNo]
END
IF @TransType='ROHSDATADUMP_DOWNLOAD'
begin
DECLARE @_ControlValue nvarchar(100) SET @_ControlValue='2'
Declare @_QueryValue varchar(100) set @_QueryValue='8'
DECLARE @maxcount int set @maxcount=0
DECLARE @maxdate nvarchar(10)
select @_ControlValue=ControlValue from ADControlValueM WHERE
ControlName='ROHSDATA_FILE' and CustomField1='DOWNLOADVALIDWEEKS'
select @_QueryValue=ControlValue from ADControlValueM WHERE
ControlName='ROHSDATA_FILE' and CustomField1='ProgramQueryReportTimes'
declare @_topnum int
set @_topnum=CONVERT(int ,@_QueryValue)
truncate table #Temp
INSERT INTO #Temp
select
a.[DataType] as [DataType],
a.[FileName] as [FileName],
a.[FilePath] as [FilePath],
Convert(varchar(10),a.[CreatedDate],103) as [CreatedDate]
from
(
select
ddlu.[DataType] as [DataType],
ddlu.[FileName] as [FileName],
ddlu.[FilePath] as [FilePath],
ddlu.[CreatedDate] as [CreatedDate]
from DataDownLoadURL ddlu
where ddlu.[username] =@UserName
and ddlu.[CreatedDate] between DATEADD(week, -CONVERT (INT ,@_ControlValue),
CONVERT(DATETIME,getdate())) and CONVERT(DATETIME,getdate())
and ddlu.[DataType]='ROHSDATADUMP'
union
select Top (@_topnum)
ddlu.[DataType] as [DataType],
ddlu.[FileName] as [FileName],
ddlu.[FilePath] as [FilePath],
ddlu.[CreatedDate] as [CreatedDate]
from DataDownLoadURL ddlu
where ddlu.[username] =@UserName
and ddlu.[DataType]='ROHSPROGRAMQUERYREPORT'
order by ddlu.[CreatedDate] desc
) a
order by
--a.[DataType] asc,
a.[CreatedDate] DESC
SELECT @maxcount=COUNT(1) FROM #Temp
IF (@maxcount<=0)
BEGIN
select @maxdate=Convert(nvarchar(10),max([CreatedDate]),121) from DataDownLoadURL --獲取表中最大的數據，顯示其中最最後的2周數據
truncate table #newTemp
INSERT INTO #newTemp
select
ddlu.[DataType] as [DataType],
ddlu.[FileName] as [FileName],
ddlu.[FilePath] as [FilePath],
Convert(varchar(10),ddlu.[CreatedDate],103)as [CreatedDate]
from DataDownLoadURL ddlu
where ddlu.[username] =@UserName Or (@UserName is null )
and ddlu.[CreatedDate] between DATEADD(week, -2,CONVERT(DATETIME,@maxdate)) and CONVERT(DATETIME,@maxdate)
and ddlu.[DataType]=@DataType
order by
--ddlu.[DataType] asc,
ddlu.[CreatedDate] DESC
INSERT INTO #Temp SELECT *FROM #newTemp
END
SELECT @_RowCount = COUNT(1) FROM #Temp
SET @PageIndex = @PageIndex - 1
IF @PageSize = 0
SET @PageSize = @_RowCount
IF @_RowCount > 0
BEGIN
IF @_RowCount % @PageSize > 0
SET @_PageCount = @_RowCount / @PageSize + 1
ELSE
SET @_PageCount = @_RowCount / @PageSize
SET @_StartRow = @PageSize * @PageIndex + 1
END
ELSE
BEGIN
SET @_PageCount = 0
SET @_StartRow = 0
END
SELECT TOP(@PageSize) * FROM #Temp WHERE Iden >= @_StartRow
SELECT @_PageCount AS TotalPages
DROP TABLE #Temp
DROP TABLE #newTemp
end
IF @TransType='ROHSDATADUMP_PARA'
BEGIN
SELECT a.ControlValue,a.CustomField1
from ADControlValueM a (Nolock)
WHERE ControlName='ROHSDATA_FILE' order by a.SeqNo
END