if exists(select name from sysobjects where name ='fGetKalaVaredeSadere')
	drop function fGetKalaVaredeSadere
go
create function fGetKalaVaredeSadere() returns table
return (
-----------موجودی کالا در انبارها   
select
	k.codeKala[Code],
	case when mojoodiAvaldoreh>0 then mojoodiAvalDoreh else 0 end[Varede],
	case when mojoodiAvaldoreh<0 then -mojoodiAvalDoreh else 0 end[Sadere],
	k.CodeAnbar[CodeAnbar]
from
	kalaDarAnbar k
-----
union all
-----تعداد کالاهای وارده در برگه های خرید و برگشت از فروش

select
	d.codeKala,
	d.tedad,
	0,
	d.CodeAnbar
from
	Bargeh m join BargehDetail d
	on m.code=d.codebargeh
where
	d.isKala = 1 and
	m.noe in(31,29) and /*kharid va bargashAzForoosh*/
	m.vaziat != 101
-----
union all
-----تعداد کالاهای صادره در برگه های فروش و برگشت از خرید

select
	d.codeKala,
	0,
	d.tedad,
	d.CodeAnbar
from
	Bargeh m join BargehDetail d
	on m.code=d.codebargeh
where
	d.isKala = 1 and
	m.noe in(28,32) and /*foroosh va bargashAzKharid*/
	m.vaziat != 101
-----
union all
-----انبار گردانی کالاهای اضافی

select
	d.codeKala,
	d.tedad,
	0,
	d.CodeAnbar
from
	anbarGardani m join AnbarGardaniDetail d
	on m.code = d.codeAnbarGardani
where
	m.Vaziat != 101 and
	d.noe=254  /*daraamad*/
-----
union all
-----انبارگردانی کالاهای کسری

select
	d.codeKala,
	0,
	d.tedad,
	d.CodeAnbar
from
	anbarGardani m join AnbarGardaniDetail d
	on m.code = d.codeAnbarGardani
where
	m.Vaziat != 101 and
	d.noe=253  /*hazineh*/

-------
--union all
--------- تولید کالا  و تبدیل کالا
--select
--	t.codeKala,
--	case t.noe
--		when 264 then t.Tedad
--		when 265 then 0
--	end ,
--	case t.noe
--		when 264 then 0
--		when 265 then t.Tedad
--	end,
--	t.CodeAnbar 
--from
--	Tolid t
--where
--	t.vaziat != 101
-------
union all
------- کالاهای مصرفی تولید و تبدیل
select
	d.kal_ID,
	0,
	d.tedad,
	d.anb_ID
from
	Product m join ProductDetail d
	on m.id = d.prod_id
where
	m.dom_ID_vaziat != 101 and
	d.dom_ID_Noe = 260 -- kalaMasrafi
-------
union all
------ ضایعات تولید و تبدیل
select
	d.kal_ID,
	(d.tedad),
	0,
	d.anb_ID
from
	Product m join ProductDetail d
	on m.id = d.prod_ID
where
	m.dom_ID_vaziat != 101 and
	d.dom_ID_Noe in (259,463) -- Zayeaat
-------
union all
------ حواله انتقالی بین انبار صادره
select
	d.codeKala,
	0,
	d.tedad,
	d.CodeAnbar
from
	Bargeh m join BargehDetail d
	on m.code=d.codebargeh
where
	d.isKala = 1 and
	m.noe = 36 and /*EnteghalBeynAnbar*/
	m.vaziat != 101

union all
------ حواله انتقالی بین انبار وارده
select
	d.codeKala,
	d.tedad,
	0,
	d.CodeAnbarMaghsad
from
	Bargeh m join BargehDetail d
	on m.code=d.codebargeh
where
	d.isKala = 1 and
	m.noe = 36 and /*EnteghalBeynAnbar*/
	m.vaziat != 101
	
union all
-------- مصرف کالا
select
	d.codeKala,
	0,
	d.tedad,
	d.CodeAnbar
from
	UseProductionKala m join UseProductionKalaDetail d
	on m.code = d.codeBargeh
where
	m.noe = 406 and /*Use*/
	m.vaziat != 101

union all
--------- تولید کالا (در مصرف کالا) ؛
select
	d.codeKala,
	d.tedad,
	0,
	d.CodeAnbar
from
	UseProductionKala m join UseProductionKalaDetail d
	on m.code = d.codeBargeh
where
	m.noe = 407 and /*Prouduct*/
	m.vaziat != 101
)
-----------------------------------------------------------------------------------------------
go
-----------------------------------------------------------------------------------------------
--prcKalaView
if exists(select name from sysobjects where name ='prcKalaView')
	drop proc prcKalaView
go
create proc prcKalaView as
begin
select
        k.Code,
        k.CodeKala,
        k.Name,
        k.EnglishName,
        sum(isnull(f.varede,0.0)-isnull(f.sadere,0.0))[Mojoodi],
        isnull(k.GheymatForoosh1,0.0)[GheimatForoosh1],
        isnull(k.GheymatForoosh2,0.0)[GheimatForoosh2],
        isnull(k.GheymatForoosh3,0.0)[GheimatForoosh3],
	isnull(k.GheymatKharid1,0.0)[GheymatKharid1],
	isnull(k.GheymatKharid2,0.0)[GheymatKharid2],
	isnull(k.GheymatKharid3,0.0)[GheymatKharid3],
        g.name[NameGrooh],
        k.IsActive,
        k.CodeGrooh,
        isnull(k.gheimatForoosh,0.0)[GheimatMasrafKonandeh],
        isnull(k.TedadVahedMotevaset,0.0)[TedadVahedMotevaset],
        isnull(k.TedadVahedBozorg,0.0)[TedadVahedBozorg]
from
	kala k left outer join dbo.fGetKalaVaredeSadere() f on k.code=f.code, groohKala g
where
        k.codeGrooh=g.code
group by
        k.code,k.name,
        k.codekala,k.englishname,k.gheymatForoosh1,k.gheymatForoosh2,k.gheymatForoosh3,k.GheymatKharid1,k.GheymatKharid2,k.GheymatKharid3,
        g.name,
        k.isActive,k.codegrooh,k.gheimatforoosh,k.tedadvahedmotevaset,k.tedadvahedBozorg
order by k.Code
end