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(1,3) and /*kharid va bargashAzForoosh*/
	m.vaziat != 1
-----
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(0,4) and /*foroosh va bargashAzKharid*/
	m.vaziat != 1
-----
union all
-----انبار گردانی کالاهای اضافی

select
	d.codeKala,
	d.tedad,
	0,
	d.CodeAnbar
from
	anbarGardani m join AnbarGardaniDetail d
	on m.code = d.codeAnbarGardani
where
	m.Vaziat != 1 and
	d.noe=1  /*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 != 1 and
	d.noe=0  /*hazineh*/

-------
union all
------- تولید کالا  و تبدیل کالا
select
	t.codeKala,
	case t.noe
		when 0 then t.Tedad
		when 1 then 0
	end ,
	case t.noe
		when 0 then 0
		when 1 then t.Tedad
	end,
	t.CodeAnbar 
from
	Tolid t
where
	t.vaziat != 1
-------
union all
------- کالاهای مصرفی تولید و تبدیل
select
	d.codeKala,
	0,
	(d.tedad * m.tedad),
	d.CodeAnbar
from
	Tolid m join TolidDetail d
	on m.code = d.CodeTolid
where
	m.vaziat != 1 and
	d.Noe = 1 -- kalaMasrafi
-------
union all
------ ضایعات تولید و تبدیل
select
	d.codeKala,
	(d.tedad * m.tedad),
	0,
	d.CodeAnbar
from
	Tolid m join TolidDetail d
	on m.code = d.CodeTolid
where
	m.vaziat != 1 and
	d.Noe = 0 -- 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 = 8 and /*EnteghalBeynAnbar*/
	m.vaziat != 1

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 = 8 and /*EnteghalBeynAnbar*/
	m.vaziat != 1
	
union all
-------- مصرف کالا
select
	d.codeKala,
	0,
	d.tedad,
	d.CodeAnbar
from
	UseProductionKala m join UseProductionKalaDetail d
	on m.code = d.codeBargeh
where
	m.noe = 0 and /*Use*/
	m.vaziat != 1

union all
--------- تولید کالا (در مصرف کالا) ؛
select
	d.codeKala,
	d.tedad,
	0,
	d.CodeAnbar
from
	UseProductionKala m join UseProductionKalaDetail d
	on m.code = d.codeBargeh
where
	m.noe = 1 and /*Prouduct*/
	m.vaziat != 1
)
-----------------------------------------------------------------------------------------------
go
-----------------------------------------------------------------------------------------------

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