DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

ALTER   PROCEDURE [dbo].[rpt_TR_REQ_GOODS_Inventory]

@p_REQ_PAY_CODE	varchar(50)	= NULL,

@p_REQ_TYPE	varchar(15)	= NULL,

@p_REF_ID	varchar(15)	= NULL,

--@p_DEP_ID	varchar(15)	= NULL,

@p_BRANCH_ID	varchar(15)	= NULL,

@p_LEVEL varchar(10) = NULL,

@p_FromDate VARCHAR(20) = NULL,

@p_ToDate VARCHAR(20) = NULL,

--@p_PO_CODE	varchar(15)	= NULL,

@p_REQ_REASON nvarchar(MAX)	= NULL,

@p_BRANCH_CREATE	varchar(15)	= NULL,

@p_DEP_CREATE VARCHAR(15) = NULL,

--@p_REF_ID varchar(15) = NULL,

@p_BRANCH_LOGIN VARCHAR(15) = NULL,

@p_USER_LOGIN VARCHAR(15)= NULL,

@p_SO_TO_TRINH VARCHAR(15) = NULL

AS

declare @tmp table(BRANCH_ID varchar(15))

insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)

DECLARE @DEP_ID_LOGIN VARCHAR(15), @BRANCH_TYPE_LOGIN VARCHAR(15), @ROLE_USER_LOGIN VARCHAR(15)

SET @DEP_ID_LOGIN=(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)

SET @BRANCH_TYPE_LOGIN=(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN)

SET @ROLE_USER_LOGIN =  (SELECT RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)

BEGIN 

	SELECT 

	ROW_NUMBER() OVER (ORDER BY A.REQ_DT DESC) AS STT,

	A.REQ_ID,

	A.REQ_CODE,

	HHT.HH_TYPE_CODE AS MA_LOAI_HH,	

	HHT.HH_TYPE_NAME AS TEN_LOAI_HH,	

	HH.HH_CODE AS MA_HH,

	HH.HH_NAME AS TEN_HH,	

	DT.DESCRIPTION AS QUY_CACH_KT,

	DT.QUANTITY AS SL_MS,

	DT.PRICE AS DON_GIA,

	--U.UNIT_NAME AS DONVI,

	DT.UNIT_NAME AS DON_VI_TINH,

	DT.TOTAL_AMT AS THANH_TIEN, 

	S.SUP_NAME AS NCC,

	A.REQ_DT AS NGAYMUA

	FROM TR_REQUEST_DOC A

	LEFT JOIN PL_REQUEST_DOC PLRD ON A.PL_REQ_ID = PLRD.REQ_ID

	LEFT JOIN TR_REQUEST_DOC_DT DT ON A.REQ_ID = DT.REQ_DOC_ID

	LEFT JOIN CM_HANGHOA HH ON HH.HH_ID = DT.HANGHOA_ID AND DT.HANGHOA_ID IS NOT NULL

	LEFT JOIN CM_HANGHOA_TYPE HHT ON HHT.HH_TYPE_ID = HH.HH_TYPE_ID

	--LEFT JOIN CM_UNIT U ON U.UNIT_ID = HH.UNIT_ID

	LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = DT.SUP_ID

	WHERE 1=1

	--Thiếu những field không biết lấy như nào nên em để lại 

	AND(A.REQ_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)

	AND(PLRD.REQ_CODE LIKE N'%'+@p_SO_TO_TRINH+'%' or @p_SO_TO_TRINH='' OR @p_SO_TO_TRINH IS NULL)

	AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' or @p_REQ_TYPE='' OR @p_REQ_TYPE IS NULL)

	AND CONVERT(DATE, A.CREATE_DT, 103) >= CONVERT(DATE, @p_FromDate, 103)

	AND CONVERT(DATE, A.CREATE_DT, 103) <= CONVERT(DATE, @p_ToDate, 103)

	AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' or @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)

	--AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp))

	--OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_ID)OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL)))

	AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '')

	AND (A.DEP_CREATE LIKE '%' + @p_DEP_CREATE + '%' OR @p_DEP_CREATE IS NULL OR @p_DEP_CREATE = '')

END