DECLARE @Infobar INFOBARTYPE;
EXEC dbo.SetSiteSp @Site = 'PVC',              -- SiteType
                   @Infobar = @Infobar OUTPUT -- InfobarType

DECLARE @year INT, @month INT,@whse VARCHAR(5), @fromDate DATETIME,@toDate DATETIME
SET @year = 2019
SET @month = 6
SET @whse = 'BTL'
SET @fromDate = '2019-06-01'
SET @toDate = '2019-07-01'

-- Tồn đầu
SELECT N'00-Tồn đầu','', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)--, ISNULL(SUM(m.qty*m.cost),0)/ISNULL(SUM(m.qty),1)
FROM dbo.matltran m
WHERE
m.trans_date < @fromDate
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.whse = @whse
AND m.loc IS NOT NULL

UNION
SELECT N'05-Tồn cuối','', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
FROM dbo.matltran m
WHERE
m.trans_date < @toDate
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.whse = @whse
AND m.loc IS NOT NULL

UNION
SELECT N'01-Nhập kho sản xuất','', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
FROM dbo.matltran m
LEFT JOIN job j ON m.ref_num = j.job AND m.ref_line_suf = j.suffix
WHERE
dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.ref_type = 'J'
AND loc IS NOT NULL
AND m.whse = @whse
AND j.rework = 0

UNION
SELECT N'02-Nhập điều chỉnh',N'Nhập kho hoán chuyển', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
FROM dbo.matltran m
LEFT JOIN job j ON m.ref_num = j.job AND m.ref_line_suf = j.suffix
WHERE
dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.ref_type = 'J'
AND loc IS NOT NULL
AND m.whse = @whse
AND j.rework = 1

UNION
SELECT N'02-Nhập điều chỉnh',N'KHGB => BTL',ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
FROM dbo.matltran m
LEFT JOIN dbo.trnitem t ON m.ref_num = t.trn_num AND t.trn_line = m.ref_line_suf
WHERE
dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.loc IS NOT NULL
AND m.whse = @whse
AND m.ref_type = 'T'
AND t.from_whse = 'KHGB'
AND t.to_whse = @whse

UNION
SELECT N'02-Nhập điều chỉnh',N'Khác => BTL',ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
FROM dbo.matltran m
LEFT JOIN dbo.trnitem t ON m.ref_num = t.trn_num AND t.trn_line = m.ref_line_suf
WHERE
dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.loc IS NOT NULL
AND m.whse = @whse
AND m.ref_type = 'T'
AND t.from_whse != 'KHGB'
AND t.to_whse = @whse

UNION
SELECT N'02-Nhập điều chỉnh',N'Misc Receipt', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
FROM dbo.matltran m
WHERE
dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.loc IS NOT NULL
AND m.whse = @whse
AND m.ref_type = 'I'
AND m.trans_type = 'H'

UNION
SELECT N'03-Xuất cọc công trường',N'BTL => KHGB', ISNULL(SUM(qty),0), ISNULL(SUM(m.qty*m.cost),0)
FROM dbo.matltran m
LEFT JOIN dbo.trnitem t ON m.ref_num = t.trn_num AND t.trn_line = m.ref_line_suf
WHERE
dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.loc IS NOT NULL
AND m.whse = @whse
AND m.ref_type = 'T'
AND t.from_whse = @whse
AND t.to_whse = 'KHGB'

UNION
SELECT N'04-Xuất điều chỉnh',N'BTL => Khác', ISNULL(SUM(qty),0), ISNULL(SUM(m.qty*m.cost),0)
FROM dbo.matltran m
LEFT JOIN dbo.trnitem t ON m.ref_num = t.trn_num AND t.trn_line = m.ref_line_suf
WHERE
dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.loc IS NOT NULL
AND m.whse = @whse
AND m.ref_type = 'T'
AND t.from_whse = @whse
AND t.to_whse != 'KHGB'

UNION
SELECT N'04-Xuất điều chỉnh',N'Misc Issue', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
FROM dbo.matltran m
WHERE
dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.loc IS NOT NULL
AND m.whse = @whse
AND m.ref_type = 'I'
AND m.trans_type = 'G'

UNION
SELECT N'02-Nhập điều chỉnh',N'PO', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
FROM dbo.matltran m
WHERE
dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.loc IS NOT NULL
AND m.whse = @whse
AND m.ref_type = 'P'

UNION
SELECT N'04-Xuất điều chỉnh',N'CO - Xuất Hủy',ISNULL(SUM(qty),0), ISNULL(SUM(m.qty*m.cost),0)
FROM dbo.matltran m
WHERE
dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
AND LEFT(m.item,2) IN ('AA','AC','AN')
AND m.loc IS NOT NULL
AND m.whse = @whse
AND m.ref_type = 'O'