select 
	dbo.conv2barcode(a.productcode) as barcode ,
		a.storecode,d.description as storedesc,
		convert(date,min(qibt.tglrcv)) as tglrcv,
		dbo.ceknull(sum(qibt.qreceipt6)) -  dbo.ceknull(sum(qissue.qissue6)) as qibt6,
		dbo.ceknull(sum(qibt.qreceipt8))  - dbo.ceknull(sum(qissue.qissue8)) as qibt8,
		dbo.ceknull(sum(qibt.qreceipt10))- dbo.ceknull(sum(qissue.qissue10)) as qibt10,
		dbo.ceknull(sum(qibt.qreceipt12))-dbo.ceknull(sum(qissue.qissue12)) as qibt12,
		dbo.ceknull(sum(qibt.qreceipt14))-dbo.ceknull(sum(qissue.qissue14)) as qibt14,
		dbo.ceknull(sum(qibt.qreceipt16))-dbo.ceknull(sum(qissue.qissue16)) as qibt16,
		dbo.ceknull(sum(qibt.qreceipt))-dbo.ceknull(sum(qissue.qissue))  as qibttot,		
	max(c.saletime) as tglsale ,
	sum( dbo.ceknull(a.quantity) ) as qsale,
	sum(case when rtrim(SUBSTRING(a.productcode,13,2))='6' then a.quantity else 0 end) as qsale6,
	sum(case when rtrim(SUBSTRING(a.productcode,13,2))='8' then a.quantity else 0 end) as qsale8,
	sum(case when rtrim(SUBSTRING(a.productcode,13,2))='10' then a.quantity else 0 end) as qsale10,
	sum(case when rtrim(SUBSTRING(a.productcode,13,2))='12' then a.quantity else 0 end) as qsale12,
	sum(case when rtrim(SUBSTRING(a.productcode,13,2))='14' then a.quantity else 0 end) as qsale14,
	sum(case when rtrim(SUBSTRING(a.productcode,13,2))='16' then a.quantity else 0 end) as qsale16,
	convert(int,MAX(c.saletime) - min(qibt.tglrcv) ) as NOD,
	(( sum( a.quantity )/( dbo.if0to1(
	dbo.ceknull(sum(qreceipt))-dbo.ceknull(sum(qissue.qissue)))) ) * 100 )as ach,
	dbo.getschema(LEFT(a.productcode,2),
		dbo.ceknull(sum(qibt.qreceipt))-dbo.ceknull(sum(qissue.qissue)),
		convert(int,(MAX(c.saletime) - min(tglrcv)))
		,(( sum( a.quantity )/( dbo.if0to1(
				dbo.ceknull(sum(qreceipt))-dbo.ceknull(sum(qissue.qissue)))) ) * 100 )) as sch,

	sum(case when (a.linevalue/dbo.if0to1( e.unitcode))*100 >=80  then a.quantity else 0 end) as normal,
	sum(case when  (a.linevalue/dbo.if0to1( e.unitcode))*100>=50 and (a.linevalue/dbo.if0to1( e.unitcode))*100 <80 then a.quantity else 0 end)  as disc,
	sum(case when (a.linevalue/dbo.if0to1( e.unitcode))*100 <50 then a.quantity else 0 end) as ob
from 
	saleline as a LEFT OUTER JOIN
	sale AS c ON a.salenumber = c.salenumber and a.storecode=c.storecode
	left join store as d
	on a.storecode = d.storecode
	LEFT join product as e
	on a.productcode = e.productcode
	left join (	
			--- mendapatkan data receive
			select 
				a.tostorecode as storecode,b.productcode,
				dbo.conv2barcode(b.productcode) as barcode ,
				min( b.receivedtime) as tglrcv,
				sum(b.quantity) as qreceipt,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='6' then b.quantity else 0 end) as qreceipt6,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='8' then b.quantity else 0 end) as qreceipt8,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='10' then b.quantity else 0 end) as qreceipt10,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='12' then b.quantity else 0 end) as qreceipt12,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='14' then b.quantity else 0 end) as qreceipt14,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='16' then b.quantity else 0 end) as qreceipt16
			from 
				ibt as a left join ibtline as b
				on a.storecode = b.storecode and a.ibtnumber = b.ibtnumber 
				and b.receivedtime is not null AND	a.statuscode ='CO' 			
			where  				
				SUBSTRING(b.productcode,6,4) = @coll	 
				and  LEFT(b.productcode,2) = @brand and
				b.receivedtime <= @period
							
			group by a.tostorecode,b.productcode,
				dbo.conv2barcode(b.productcode)
				
	) as qibt ON a.storecode = qibt.storecode and a.productcode=qibt.productcode
	
	left join (	
			--- mendapatkan data issue
			select 
				a.fromstorecode as storecode,b.productcode,
				dbo.conv2barcode(b.productcode) as barcode ,
				min( a.senttime) as tglsent,
				sum(b.quantity) as qissue,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='6' then b.quantity else 0 end) as qissue6,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='8' then b.quantity else 0 end) as qissue8,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='10' then b.quantity else 0 end) as qissue10,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='12' then b.quantity else 0 end) as qissue12,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='14' then b.quantity else 0 end) as qissue14,
				sum(case when rtrim(SUBSTRING(b.productcode,13,2))='16' then b.quantity else 0 end) as qissue16
			from 
				ibt as a left join ibtline as b
				on a.storecode = b.storecode and a.ibtnumber = b.ibtnumber 	
				and a.senttime is not null			
			where
				SUBSTRING(b.productcode,6,4) = @coll and
				a.senttime <= @period and
				LEFT(b.productcode,2) = @brand 												  
			group by a.fromstorecode,b.productcode,
			dbo.conv2barcode(b.productcode)
				
	) as qissue ON a.storecode = qissue.storecode and a.productcode=qissue.productcode
	
where 
	SUBSTRING(a.productcode,6,4) = @coll 
	 and
	LEFT(a.productcode,2) =@brand and
	c.saletime<= @period
group by 
	dbo.conv2barcode(a.productcode),d.description,a.storecode,LEFT(a.productcode,2)