--select * from dbo.F_S_GetDraftReceipt(30,'','',0,0,'',0,0,'','',0,0,'1',0,0,'')
alter function F_S_GetDraftReceipt(
	@Fc_ID int=0,
	@strPro_ID_VahedMali nvarchar(200)='',
	@strPro_ID_Anbar nvarchar(max) ='',
	@groupKalaAz int=0,
	@groupKalaEla int = 0,
	@strPro_ID_GroupKala nvarchar(max) ='',
	@kal_CodeAz int=0,
	@kal_CodeEla int=0,
	@strKal_IDs nvarchar(max) ='',
	@strDom_ID_Restoration nvarchar(100) ='',
	@CodeAz int=0, 
	@CodeEla int=0,
	@strCodes nvarchar(max) ='', -- strSDAI_Code 
	@TarikhAz int=0, 
	@TarikhEla int=0,
	@strTarikh nvarchar(max)=''
	) returns table
return(
/*
گردش ریالی اقلام کالا به ریز حواله|رسید
*/
select 
	sdi.id SDI_ID,
	sdai.id SDAI_ID,
	
	sd.Pro_ID_Anbar,
	sdi.Kal_ID,
	sdi.Pro_ID_Unit,
	sdi.Dom_ID_Restoration,
	sdi.Quantity,
	
	
	--sdai.Dom_ID_PriceType,
	--sdai.Price,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Dom_ID_Detail = 896 /*موجودي اوليه*/
	),0.0) PrimaryInventory,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Dom_ID_Detail = 897 /*رسيد خريد*/
	),0.0) PurchaseReceipt,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Dom_ID_Detail = 898 /*رسيد انتقال*/
	),0.0) TransferReceipt,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Dom_ID_Detail = 903 /*رسید ضایعات تولید*/
	),0.0) ProductionWasteReceipt,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Dom_ID_Detail = 904 /*رسید تولید*/
	),0.0) ProductionReceipt,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Dom_ID_Detail = 906 /*رسید برگشت از فروش*/
	),0.0) SaleBackReceipt,
	
	---------------------- حواله
	
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Dom_ID_Detail = 891 /*حواله فروش*/
	),0.0) SaleDraft,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Dom_ID_Detail = 892 /*حواله انتقال*/
	),0.0) TransferDraft,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Dom_ID_Detail = 893 /*حواله مونتاژ*/
	),0.0) ProductDraft,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Dom_ID_Detail = 894 /*حواله اموالي*/
	),0.0) PossessionDraft,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Dom_ID_Detail = 905 /*حواله برگشت از خرید*/
	),0.0) PurchaseBackDraft,
	-------------------------------------- sum
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Priority = 886 /*افزايشي*/
	),0.0) SumReceipt,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Priority = 887 /*کاهشي*/
	),0.0) SumDraft,
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Priority = 886 /*افزايشي*/
	),0.0) -
	ISNULL((
		select sdai.Price
		from StoreDocument a
			inner join StoreDocumentItem b on a.ID = b.SD_ID
			inner join StoreDocumentAccountItem	c on b.ID = c.SDI_ID
			inner join StoreDocumentAccount d on c.SDA_ID = d.ID
			inner join OtherProperties op on a.Pro_ID_DocType = op.ID
		where
			b.ID = sdi.ID and
			op.Priority = 887 /*کاهشي*/
	),0.0) Remind
	
from StoreDocument sd
	inner join StoreDocumentItem sdi on sd.ID = sdi.SD_ID
	inner join Kala k on sdi.Kal_ID = k.id
	inner join otherProperties opGroupkKala on k.Pro_Id_grooh = opGroupkKala.ID
	inner join StoreDocumentAccountItem	sdai on sdi.ID = sdai.SDI_ID
	inner join StoreDocumentAccount sda on sdai.SDA_ID = sda.ID
where
	sd.fc_id = @Fc_ID and	
	(@strPro_ID_Anbar = '' or sd.pro_Id_Anbar in (select * from dbo.CastStringToTable(@strPro_ID_Anbar))) and
	(@groupKalaAz = 0 or opGroupkKala.Code >= @groupKalaAz) and
	(@groupKalaEla = 0 or opGroupkKala.Code <= @groupKalaEla) and
	(@strPro_ID_GroupKala = '' or k.Pro_Id_grooh in (select * from dbo.CastStringToTable(@strPro_ID_GroupKala))) and
	(@kal_CodeAz = 0 or cast(k.Code as int) >= @kal_CodeAz) and
	(@kal_CodeEla = 0 or cast(k.Code as int) <= @kal_CodeEla) and
	(@strKal_IDs = '' or k.Id in (select * from dbo.CastStringToTable(@strKal_IDs))) and	
	(@strDom_ID_Restoration = '' or sdi.Dom_ID_Restoration in (select * from dbo.CastStringToTable(@strDom_ID_Restoration))) and
	(@CodeAz = 0 or sda.Code >= @CodeAz) and
	(@CodeEla = 0 or sda.Code <= @CodeEla) and
	(@strCodes = '' or sda.ID in (select * from dbo.CastStringToTable(@strCodes))) and
	(@TarikhAz = 0 or sd.Tarikh >= @TarikhAz) and
	(@TarikhEla = 0 or sd.Tarikh <= @TarikhEla) and
	(@strTarikh = '' or sd.tarikh in (select * from dbo.CastStringToTable(@strTarikh)))
)