/*
exec prcPersonCardDetail 34 , 8 ,228,206,209
select sum(Bestankar) - sum(bedehkar) from prcPersonCardDetail 18 , 0,209
*/
alter proc dbo.prcPersonCardDetail @Fc_ID int ,@Pro_ID_VahedMali int ,@Taf_ID int , @NoeGardesh smallint , @NoeGozaresh int as
begin
declare @NoeTaf smallInt;
set @NoeTaf = (select Dom_ID_noe from Tafsili where ID = @Taf_ID)
select ------------------------------------------- مانده اول دوره
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
tr.Tarikh [Tarikh],
0[Saat],
cast('مانده اول دوره' as nvarchar(max))[Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case t.Dom_ID_noe
when 50 then -- shakhs
case when tr.Dom_ID_Nature=799 /*بدهکار*/ then isnull(tr.Price,0.0) else 0.0 end
when 49 then isnull(tr.Price,0.0)--bank
when 51 then isnull(tr.Price,0.0)--Sandoogh
else 0.0
end as [Bedehkar],
case t.Dom_ID_noe
when 50 then -- Shakhs
case when tr.Dom_ID_Nature=800 /*بستانکار*/ then isnull(tr.Price,0.0) else 0.0 end
else 0.0
end as [Bestankar],
0.0[Mandeh],
cast('' as nvarchar(200))[Mahiat],
166 [NoeRadif],
tr.ID [Refrence_ID],
CAST(0 as int) TarikhSabt
from Tafsili t
join TafsiliRemind tr on t.ID = tr.Taf_ID
inner join OtherProperties opVahedMali on t.Pro_ID_VahedMali = opVahedMali.ID
where
t.ID = @Taf_ID and
(@Fc_ID = 0 or tr.FC_ID = @Fc_ID) and
(@Pro_ID_VahedMali = 0 or tr.Pro_ID_VahedMali = @Pro_ID_VahedMali)
union all
------------------------------------------- دریافت|پرداخت
select
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh [Tarikh],
m.Saat[Saat],
case m.Dom_ID_NoeMaster
when 40 then (case when m.Taf_ID= @Taf_ID then 'شناسایی ' + (select t.title from Tafsili t where t.ID = m.Taf_ID_BedehKar) else 'هزینه' end)
when 41 then 'حواله حسابداري'
when 42 then 'درآمد'
else ''
end +
case
when m.Dom_ID_NoeMaster in (38,39) and isnull(m.Dom_ID_noeDetail,0)=45 then
' حواله بانکي' +
(case
when isnull(m.Bar_ID,0) > 0 then ' طی ' + (select d.Title+' ('+CAST(b.Code as nvarchar)+')' from Bargeh b join Domain d on b.Dom_ID_Noe = d.ID where b.ID = m.Bar_ID)
else ''
end)
when m.Dom_ID_NoeMaster in (38,39) and isnull(m.Dom_ID_noeDetail,0) = 46 then
' وجه نقد'
else ''
end+
case
when m.Dom_ID_NoeMaster in (40,41) then
' '+(case when m.Taf_ID_BedehKar = @Taf_ID then ' از ' else ' به ' end)+(select Title from tafsili where ID =(case when m.Taf_ID_BestanKar=@Taf_ID then m.Taf_ID_BedehKar else m.Taf_ID_BestanKar end))
else ''
end +
case
when isnull(m.Bar_ID,0) > 0 then (' طی ' + (select dom.Title from Domain dom where dom.ID = (select b.Dom_ID_Noe from Bargeh b where b.ID = m.Bar_ID)) + ' ('+ cast((select b.Code from Bargeh b where b.ID = m.Bar_ID)as nvarchar) +')')
else ' ('+cast(m.Code as nvarchar)+')'
end +
case
when m.Dom_ID_NoeMaster in (38,39) and m.Dom_ID_noeDetail=45 then ' ش.حواله ('+m.ShomarehSanad+')'+(case when m.Taf_ID_BedehKar = @Taf_ID then ' از ' else ' به ' end)+(select Title from tafsili where ID = (case when m.Taf_ID_BestanKar=@Taf_ID then m.Taf_ID_BedehKar else m.Taf_ID_BestanKar end))
when m.Dom_ID_NoeMaster in (38,39) and m.Dom_ID_noeDetail=46 then /*وجه نقد*/ ' ' +(case when m.Taf_ID_BedehKar = @Taf_ID then ' از ' else ' به ' end)+(select Title from tafsili where ID = (case when m.Taf_ID_BestanKar=@Taf_ID then m.Taf_ID_BedehKar else m.Taf_ID_BestanKar end) )
else ''
end+
(case when len(isnull(m.sharh,'')) > 0 then ' - ' else '' end) +isnull(m.sharh,'')
as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case when (m.Taf_ID_BedehKar = @Taf_ID or m.Taf_ID= @Taf_ID) then m.mablagh else 0.0 end [Bedehkar],
case when (m.Taf_ID_BestanKar = @Taf_ID or m.Taf_ID= @Taf_ID ) then m.mablagh else 0.0 end [Bestankar],
0.0[Mandeh],
''[Mahiat],
case
when isnull(m.Bar_ID,0) > 0 then
(case (select b.Dom_ID_Noe from Bargeh b where b.ID = m.Bar_ID)
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 --BargashtAzForoosh
when 30 then 182 -- PishFactorForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end)
else
(case m.Dom_ID_NoeMaster
when 38 then
case m.Dom_ID_noeDetail
when 45 then 168 --DaryaftHavaleh
when 46 then 169 --DaryaftVajh
end
when 39 then
case m.Dom_ID_noeDetail
when 45 then 172 --PardakhtHavaleh
when 46 then 173 -- PardakhtVajh
end
when 40 then 174--Hazineh
when 41 then 175--HavalehHesabdari
when 42 then 176 --DarAmad
end)
end
[NoeRadif],
ISNULL(m.bar_ID, m.ID) [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from daryaftpardakht m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noeDetail != 44 /*چک*/ and
(m.Taf_ID_BedehKar = @Taf_ID or m.Taf_ID_BestanKar = @Taf_ID)and
(@Fc_ID = 0 or @FC_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
------------------------------------------- برگه هزینه - ذینفع هزینه - بستانکار
select
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh [Tarikh],
m.Saat[Saat],
'شناسایی ' + (select t.title from Tafsili t where t.ID = m.Taf_ID_BedehKar)+' ('+cast(m.Code as nvarchar)+')' + (case when len(isnull(m.sharh,'')) > 0 then ' - ' else '' end) +isnull(m.sharh,'')
as [sharh],
0 [Meghdar],
''[Vahed],
0.0[Fi],
0.0 [Bedehkar],
m.mablagh [Bestankar],
0.0[Mandeh],
''[Mahiat],
174 [NoeRadif], --Hazineh
m.ID [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from daryaftpardakht m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_NoeMaster = 40 and
(m.Taf_ID = @Taf_ID)and
(@Fc_ID = 0 or @FC_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
------------------------------------------- برگه پرداخت - کارمزد در حواله های پرداختی
select
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh [Tarikh],
m.Saat[Saat],
'کارمزد انتقال در پرداخت ' +' ('+cast(m.Code as nvarchar)+') به ' + (select t.title from Tafsili t where t.ID = m.Taf_ID_BedehKar) + (case when len(isnull(m.sharh,'')) > 0 then ' - ' else '' end) +isnull(m.sharh,'')
as [sharh],
0 [Meghdar],
''[Vahed],
0.0[Fi],
case when m.Taf_ID = @Taf_ID then isnull(m.PriceWage,0.0) else 0.0 end [Bedehkar],
case when m.Taf_ID_BestanKar = @Taf_ID then isnull(m.PriceWage,0.0) else 0.0 end [Bestankar],
0.0[Mandeh],
''[Mahiat],
172 [NoeRadif], --PardakhtHavaleh
m.ID [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from daryaftpardakht m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_NoeMaster = 39 and
(m.Taf_ID = @Taf_ID or m.Taf_ID_BestanKar = @Taf_ID)and
(@Fc_ID = 0 or @FC_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali) and
isnull(m.PriceWage,0) > 0
union all
------------------------------------------- برگه هزینه - ذینفع هزینه - بدهکار
select
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh [Tarikh],
m.Saat[Saat],
'پرداخت از ' + (select t.title from Tafsili t where t.ID = m.Taf_ID_BestanKar)+' ('+cast(m.Code as nvarchar)+')'+ ' - بابت ' + (select t.title from Tafsili t where t.ID = m.Taf_ID_BedehKar) + (case when len(isnull(m.sharh,'')) > 0 then ' - ' else '' end) +isnull(m.sharh,'')
as [sharh],
0 [Meghdar],
''[Vahed],
0.0[Fi],
m.mablagh [Bedehkar],
0.0 [Bestankar],
0.0[Mandeh],
''[Mahiat],
174 [NoeRadif], --Hazineh
m.ID [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from daryaftpardakht m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_NoeMaster = 40 and
(m.Taf_ID = @Taf_ID)and
(@Fc_ID = 0 or @FC_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
------------------------------------------- برگه دریافت - تخفیف
select
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh [Tarikh],
m.Saat[Saat],
case
when m.Taf_ID = @Taf_ID then (select t.title from Tafsili t where t.ID = m.Taf_ID)+' در دریافت '+(select d.title from Domain d where d.ID = m.Dom_ID_noeDetail)+' ('+cast(m.Code as nvarchar)+')'+ (case when len(isnull(m.sharh,'')) > 0 then ' - ' else '' end) +isnull(m.sharh,'')
when m.Taf_ID_BestanKar = @Taf_ID then 'تخفیف در دریافت '+(select d.title from Domain d where d.ID = m.Dom_ID_noeDetail)+' ('+cast(m.Code as nvarchar)+')'+ (case when len(isnull(m.sharh,'')) > 0 then ' - ' else '' end) +isnull(m.sharh,'')
else ''
end [sharh],
--'پرداخت از ' + (select t.title from Tafsili t where t.ID = m.Taf_ID_BestanKar)+' ('+cast(m.Code as nvarchar)+')'+ ' - بابت ' + (select t.title from Tafsili t where t.ID = m.Taf_ID_BedehKar) + (case when len(isnull(m.sharh,'')) > 0 then ' - ' else '' end) +isnull(m.sharh,'')
--as [sharh],
0 [Meghdar],
''[Vahed],
0.0[Fi],
case
when m.Taf_ID = @Taf_ID then isnull(m.PriceDiscount,0.0)
else 0.0
end [Bedehkar],
case
when m.Taf_ID_BestanKar = @Taf_ID then isnull(m.PriceDiscount,0.0)
else 0.0
end [Bestankar],
0.0[Mandeh],
''[Mahiat],
case m.Dom_ID_noeDetail
when 45 then 168 --DaryaftHavaleh
when 46 then 169 --DaryaftVajh
when 44 then 204 --DaryaftCheque
end [NoeRadif], --
m.ID [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from daryaftpardakht m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_NoeMaster = 38 and
(m.Taf_ID = @Taf_ID or m.Taf_ID_BestanKar = @Taf_ID) and
isnull(m.PriceDiscount,0.0) > 0 and
(@Fc_ID = 0 or @FC_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
--------------------------------------------------------------------------------- گردش چک Taf_ID_BedehKar
select
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
g.Tarikh [Tarikh],
g.Saat[Saat],
case m.Dom_ID_NoeMaster
when 38 then 'دریافت چک'
when 39 then 'پرداخت چک'
else ''
end+
case when m.IsAvalDoreh=1 then ' (اول دوره)' else '' end+
' ('+cast(m.shomarehSanad as nvarchar)+')'+
case m.Dom_ID_NoeMaster
when 38 then ' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(g.sharh , '')+' تاریخ سررسید چک ('+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')'
when 39 then ' از بانک ' + (select tt.Title from Tafsili tt where tt.ID = m.Taf_ID_BestanKar) + ' تاریخ سررسید چک ('+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')'
else ''
end+
' وضعیت نهایی چک: '+(select Title from Domain dom where dom.ID = m.Dom_ID_VaziatChek)
[Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
m.mablagh as [BedehKar],
0.0 as [BestanKar],
0.0 as [Mandeh],
'' [Mahiat],
case m.Dom_ID_NoeMaster
when 38 then -- گردش چک دریافتی
case
--when g.IsFirst = 1 then --
when g.Dom_ID_Vaziat = 106 then 188 --GardeshChekDaryaftiDarJaryanVosool
when g.Dom_ID_Vaziat = 107 then 189 --GardeshChekDaryaftiBargashtChek
when g.Dom_ID_Vaziat = 108 then 190 --GardeshChekDaryaftiDeleted
when g.Dom_ID_Vaziat = 109 then 191 --GardeshChekDaryaftiKharjShodeh
--when g.Dom_ID_Vaziat = 110 then 192 --GardeshChekDaryaftiMojood
when g.Dom_ID_Vaziat = 110 then (
case
when ISNULL(m.Bar_ID,0) > 0 then case (select b.dom_Id_Noe from bargeh b where b.id = m.bar_ID) when 28 then 177 /*Foroosh*/ when 32 then 179 /*BargashtAzKharid*/ end
when g.IsFirst = 1 then case when isnull(m.IsAvalDoreh,0) = 1 then 168 /*DaryaftChekAvalDoreh*/ else 204 /*DaryaftChek*/ end
else 192 --GardeshChekDaryaftiMojood
end
)
when g.Dom_ID_Vaziat = 111 then 193 --GardeshChekDaryaftiBargashtBeMoshtari
when g.Dom_ID_Vaziat = 112 then 194 --GardeshChekDaryaftiPassShodeh
--when g.Dom_ID_Vaziat = 113 then -- در پرداخت کاربرد دارد
when g.Dom_ID_Vaziat = 114 then 195 --GardeshChekDaryaftiDaryaftNaghdi
when g.Dom_ID_Vaziat = 115 then 196 --GardeshChekDaryaftiPasDadanChekBargashti
when g.Dom_ID_Vaziat = 116 then 197 --GardeshChekDaryaftiNaghdShodanChekBargashti
when g.Dom_ID_Vaziat = 117 then 198 --GardeshChekDaryaftiNaghdKardanChek
when g.Dom_ID_Vaziat = 118 then 199 --GardeshDaryaftMojoodShodanChekKharjShodeh
else 10000
end
when 39 then -- گردش چک پرداختی
case
--when g.Dom_ID_Vaziat = 106 then 184 --GardeshChekPardakhtiDarJaryanVosool
when g.Dom_ID_Vaziat = 106 then (
case
when ISNULL(m.Bar_ID,0) > 0 then case (select b.dom_Id_Noe from bargeh b where b.id = m.bar_ID) when 29 then 178 /*Kharid*/ when 31 then 180 /*BargashtAzForoosh*/ end
when g.IsFirst = 1 then case when isnull(m.IsAvalDoreh,0) = 1 then 170 /*PardakhtChekAvalDoreh*/ else 171 /*PardakhtChek*/ end
else 184 --GardeshPardakhtDarJaryanVosool
end
)
when g.Dom_ID_Vaziat = 107 then 185 --GardeshChekPardakhtiBargashtChek
when g.Dom_ID_Vaziat = 112 then 186 --GardeshChekPardakhtiPassShodan
when g.Dom_ID_Vaziat = 113 then 187 --GardeshChekPardakhtiBargashtAzMoshtari
else 10000
end
end as [NoeRadif],
--m.ID [Refrence_ID],
case
when g.Dom_ID_Vaziat = 110 then (
case
when ISNULL(m.Bar_ID,0) > 0 then ISNULL(m.Bar_ID,0)
when g.IsFirst = 1 then m.ID
else m.ID
end)
when g.Dom_ID_Vaziat = 106 then (
case
when ISNULL(m.Bar_ID,0) > 0 then ISNULL(m.Bar_ID,0)
when g.IsFirst = 1 then m.ID
else m.ID
end)
else m.ID
end [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from daryaftpardakht m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join GardesheChek g on m.ID = g.DaryaftPardakht_ID
join tafsili t on g.Taf_ID_BedehKar = t.ID
where
m.Dom_ID_Vaziat != 101 and
m.Dom_ID_noeDetail = 44 /*چک*/ and
g.Taf_ID_BedehKar = @Taf_ID and
(@Fc_ID =0 or @Fc_ID =g.Fc_ID) and
(@Pro_ID_VahedMali =0 or @Pro_ID_VahedMali = g.Pro_ID_VahedMali) and
(
case
when g.Dom_ID_Vaziat = 106 /*DarJarianVosool*/ then -- اول دوره مهم است
(case
when m.dom_ID_NoeMaster = 38 /*دریافت*/ then
(case
when isnull(m.isAvaldoreh,0) = 1 then 0 -- در این حالت، دریافت کننده بانک است
else 0 -- در این حالت، دریافت کننده بانک است
end)
when m.dom_ID_NoeMaster = 39 /*پرداخت*/ then -- در این حالت، دریافت کننده یا شخص است یا هزینه
(case
when isnull(m.isAvaldoreh,0) = 1 then 0 -- در این حالت، دریافت کننده شخص است یا هزینه
else 1 -- در این حالت، دریافت کننده شخص است یا هزینه
end)
end)
when g.Dom_ID_Vaziat = 107 /*Bargasht*/ then -- هم دریافت هم پرداخت
(case
when m.dom_ID_NoeMaster = 38 /*دریافت*/ then 1 -- دریافت کننده شخص است یا هزینه
when m.dom_ID_NoeMaster = 39 /*پرداخت*/ then 0 -- دریافت کننده بانک است
end)
when g.Dom_ID_Vaziat = 108 /*Deleted*/ then 0 -- نداریم اصلا
when g.Dom_ID_Vaziat = 109 /*KharjShode*/ then -- فقط دریافت
(case
when t.Dom_ID_Noe in (49 /*بانک*/,51) then 0
else 1
end)
when g.Dom_ID_Vaziat = 110 /*Mojood*/ then 0 -- فقط دریافت -- در این حالت همیشه دریافت کننده، صندوق است
when g.Dom_ID_Vaziat = 111 /*BargashtBeMoshtari*/ then -- فقط دریافت
(case
when t.Dom_ID_Noe = 50 /*Shakhs*/ then 1
else 0
end)
when g.Dom_ID_Vaziat = 112 /*PassShodeh*/ then -- هم دریافت هم پرداخت
(case
when m.dom_ID_NoeMaster = 38 /*دریافت*/ then 1 -- در این حالت همیشه دریافت کننده، بانک است که مبلغ چک واریز می شود
when m.dom_ID_NoeMaster = 39 /*پرداخت*/ then 0
end)
when g.Dom_ID_Vaziat = 113 /*BargashtAzMoshtari*/ then 0 -- فقط پرداخت -- در این حالت، دریافت کننده همیشه بانک است
when g.Dom_ID_Vaziat = 114 /*DaryaftNaghdAzMoshtary*/ then -- فقط دریافت
(case
when t.Dom_ID_Noe = 51 /*Sandoogh*/ then 1
else 0
end)
when g.Dom_ID_Vaziat = 115 /*PasDadanChekBargashti*/ then 0 -- هیچ وقت ارزش مالی ندارد
when g.Dom_ID_Vaziat = 116 /*NaghdShodanChekBargashti*/ then 1 -- فقط دریافت - در این حالت، دریافت کننده همیشه صندوق است
when g.Dom_ID_Vaziat = 117 /*NaghdShodanChek*/ then 1 -- فقط دریافت - در این حالت، دریافت کننده همیشه صندوق است
when g.Dom_ID_Vaziat = 118 /*MojoodShdanChekKharjShodeh*/ then 0 -- فقط دریافت - در این حالت، دریافت کننده همیشه صندوق است
else 0
end
) = 1
union all
--------------------------------------------------------------------------------- گردش چک Taf_ID_BestanKar
select
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
g.Tarikh as [Tarikh],
g.Saat[Saat],
case m.Dom_ID_NoeMaster
when 38 then 'دریافت چک'
when 39 then 'پرداخت چک'
else ''
end+
case when m.IsAvalDoreh=1 then ' (اول دوره)' else '' end+
' ('+cast(m.shomarehSanad as nvarchar)+')'+
case m.Dom_ID_NoeMaster
when 38 then ' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(g.sharh , '')+' تاریخ سررسید چک ('+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')'
when 39 then ''
else ''
end+
' وضعیت نهایی چک: '+(select Title from Domain dom where dom.ID = m.Dom_ID_VaziatChek)
[Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
0.0 as [BedehKar],
m.mablagh as [BestanKar],
0.0 as [Mandeh],
'' [Mahiat],
case m.Dom_ID_NoeMaster
when 38 then -- گردش چک دریافتی
case
when g.Dom_ID_Vaziat = 106 then 188 --GardeshChekDaryaftiDarJaryanVosool
when g.Dom_ID_Vaziat = 107 then 189 --GardeshChekDaryaftiBargashtChek
when g.Dom_ID_Vaziat = 108 then 190 --GardeshChekDaryaftiDeleted
when g.Dom_ID_Vaziat = 109 then 191 --GardeshChekDaryaftiKharjShodeh
--when g.Dom_ID_Vaziat = 110 then 192 --GardeshChekDaryaftiMojood
when g.Dom_ID_Vaziat = 110 then (
case
when ISNULL(m.Bar_ID,0) > 0 then case (select b.dom_Id_Noe from bargeh b where b.id = m.bar_ID) when 28 then 177 /*Foroosh*/ when 32 then 179 /*BargashtAzKharid*/ end
when g.IsFirst = 1 then case when isnull(m.IsAvalDoreh,0) = 1 then 168 /*DaryaftChekAvalDoreh*/ else 204 /*DaryaftChek*/ end
else 192 --GardeshChekDaryaftiMojood
end
)
when g.Dom_ID_Vaziat = 111 then 193 --GardeshChekDaryaftiBargashtBeMoshtari
when g.Dom_ID_Vaziat = 112 then 194 --GardeshChekDaryaftiPassShodeh
--when g.Dom_ID_Vaziat = 113 then -- در پرداخت کاربرد دارد
when g.Dom_ID_Vaziat = 114 then 195 --GardeshChekDaryaftiDaryaftNaghdi
when g.Dom_ID_Vaziat = 115 then 196 --GardeshChekDaryaftiPasDadanChekBargashti
when g.Dom_ID_Vaziat = 116 then 197 --GardeshChekDaryaftiNaghdShodanChekBargashti
when g.Dom_ID_Vaziat = 117 then 198 --GardeshChekDaryaftiNaghdKardanChek
when g.Dom_ID_Vaziat = 118 then 199 --GardeshDaryaftMojoodShodanChekKharjShodeh
else 1000
end
when 39 then -- گردش چک پرداختی
case g.Dom_ID_Vaziat
when 106 then 184 --GardeshChekPardakhtiDarJaryanVosool
when 107 then 185 --GardeshChekPardakhtiBargashtChek
when 112 then 186 --GardeshChekPardakhtiPassShodan
when 113 then 187 --GardeshChekPardakhtiBargashtAzMoshtari
else 1000
end
end as [NoeRadif],
--m.ID [Refrence_ID],
case
when g.Dom_ID_Vaziat = 110 then (
case
when ISNULL(m.Bar_ID,0) > 0 then ISNULL(m.Bar_ID,0)
when g.IsFirst = 1 then m.ID
else m.ID
end)
else m.ID
end [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from daryaftpardakht m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join GardesheChek g on m.ID = g.DaryaftPardakht_ID
join tafsili t on g.Taf_ID_BestanKar = t.ID
where
m.Dom_ID_Vaziat != 101 and
m.Dom_ID_noeDetail = 44 /*چک*/ and
g.Taf_ID_BestanKar = @Taf_ID and
(@Fc_ID = 0 or @Fc_ID = g.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = g.Pro_ID_VahedMali) and
(
case
when g.Dom_ID_Vaziat = 106 /*DarJarianVosool*/ then 0 -- هم دریافت هم پرداخت
when g.Dom_ID_Vaziat = 107 /*Bargasht*/ then -- هم دریافت هم پرداخت
(case
when m.dom_ID_NoeMaster = 38 /*دریافت*/ then 0 -- در این حالت بستانکار همیشه بانک است.
when m.dom_ID_NoeMaster = 39 /*پرداخت*/ then 1 -- در این حالت معمولا (99 درصد) بستانکار شخص است
end)
when g.Dom_ID_Vaziat = 108 /*Deleted*/ then 0 -- اصلا نداریم
when g.Dom_ID_Vaziat = 109 /*KharjShode*/ then 0 -- فقط دریافت
when g.Dom_ID_Vaziat = 110 /*Mojood*/ then -- فقط دریافت - اول دوره مهم است
(case
when isnull(m.isAvaldoreh,0) = 1 then 0
when isnull(m.isAvaldoreh,0) = 0 then (case when t.Dom_ID_Noe = 50 /*Shakhs*/ then 1 else 0 end)
end)
when g.Dom_ID_Vaziat = 111 /*BargashtBeMoshtari*/ then 0 -- فقط دریافت - در این حالت، بستانکار صندوق است
when g.Dom_ID_Vaziat = 112 /*PassShodeh*/ then -- -- هم دریافت هم پرداخت
(case
when m.dom_ID_NoeMaster = 38 /*دریافت*/ then 0 -- دراین حالت بستانکار نال است
when m.dom_ID_NoeMaster = 39 /*پرداخت*/ then 1 -- در این حالت، بستانکار همیشه بانک است
end)
when g.Dom_ID_Vaziat = 113 /*BargashtAzMoshtari*/ then 1 -- فقط پرداخت - در این حالت بستانکار یا شخص است یا هزینه
when g.Dom_ID_Vaziat = 114 /*DaryaftNaghdAzMoshtary*/ then 0 -- -- فقط دریافت
when g.Dom_ID_Vaziat = 115 /*PasDadanChekBargashti*/ then 0 -- فقط دریافت
when g.Dom_ID_Vaziat = 116 /*NaghdShodanChekBargashti*/ then 1 -- فقط دریافت - در این حالت، بستانکار یا شخص است یا هزینه
when g.Dom_ID_Vaziat = 117 /*NaghdShodanChek*/ then 0 -- فقط دریافت
when g.Dom_ID_Vaziat = 118 /*MojoodShdanChekKharjShodeh*/ then 1 -- فقط دریافت - در این حالت، بستانکار یا شخص است یا هزینه
else 0
end
) = 1
union all
select ------------------------------------------- هزینه در گردش چک
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
g.Tarikh[Tarikh],
g.Saat[Saat],
'هزینه پاس چک (' +d.shomarehSanad +') '+isnull(g.sharh , '') [Sharh],
1[Meghdar],
(select isnull(name,'') from OtherProperties v where v.ID = (select p.Pro_ID_Unit from Tafsili t join OtherProperties p on t.ID = p.Taf_ID where t.ID = g.Taf_ID_Hazineh))[Vahed],
g.Mablagh[Fi],
g.mablagh as [Bedehkar],
0.0 as [Bestankar],
0.0[Mandeh],
''[Mahiat],
case d.Dom_ID_noeMaster
when 38 then 194 -- GardeshDaryaftPassShodan
when 39 then 186 -- GardeshPardakhtPassShodan
end [NoeRadif],
g.DaryaftPardakht_ID [Refrence_ID],
CAST(d.TarikhSabt as int) TarikhSabt
from gardesheChek g
join DaryaftPardakht d on d.ID = g.DaryaftPardakht_ID
inner join OtherProperties opVahedMali on d.Pro_ID_VahedMali = opVahedMali.ID
where
cast(g.Taf_ID_Hazineh as int) = @Taf_ID and
d.Dom_ID_vaziat != 101 and
g.mablagh > 0 and
d.Dom_ID_noeDetail = 44 and
(@Fc_ID = 0 or @Fc_ID = g.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = g.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- مبلغ فاکتور برای مشتری - بصورت کلی گزارش می گیرد
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
dom.Title + ' (' + cast(m.Code as nvarchar)+ ') ' +
case
when m.Dom_ID_Noe in (2053) then (select con.Title from Contract con where con.ID = m.Con_ID) + ' '
when m.Dom_ID_Noe in (2079) then (select pp.Title from PlanningProgram pp where pp.ID = m.PP_ID) +' '
else ''
end +
isnull(m.Sharh , '')
[sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case
when m.Dom_ID_noe in(28,32,1325) then
isnull
(
cast((select sum(d.mablagh * d.tedad - isnull(d.MablagheTakhfif , 0.0))
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.MablaghArzeshAfzoodeh)
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0) -
case when m.Taf_ID_Takhfif > 0 then isnull(m.MablaghTakhfif,0) else 0.0 end +
case when m.Taf_ID_Ezafeh > 0 then isnull(m.MablaghEzafeh,0) else 0.0 end
when m.Dom_ID_noe in (1275,2071) /*کالای مصرفی پروژه*/ then -- باید مبلغ از گردش انبار بیاید
isnull
(cast((
select sum(sdai.Price * sdi.Quantity)
from StoreDocumentItem sdi
inner join StoreDocumentItemRefrenceID v on sdi.ID = v.SDI_ID
inner join BargehDetail d on v.BD_ID = d.ID
inner join StoreDocumentAccountItem sdai on sdi.ID = sdai.SDI_ID
where
d.Bar_ID = m.ID
) as decimal(19,4)),0.0)
when m.Dom_ID_noe = 792 /* کسورات انبارگرداني */then
isnull
(
cast((select sum(d.mablagh * d.tedad)
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0)
when m.Dom_ID_noe = 1163 /*TrustedGoodsSale*/ then
isnull
(
cast((select sum(d.mablagh * d.tedad - isnull(d.MablagheTakhfif , 0.0))
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.MablaghArzeshAfzoodeh)
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0) -
case when m.Taf_ID_Takhfif > 0 then isnull(m.MablaghTakhfif,0) else 0.0 end +
case when m.Taf_ID_Ezafeh > 0 then isnull(m.MablaghEzafeh,0) else 0.0 end
when m.Dom_ID_noe = 1190 then
isnull
(
cast((select sum(d.mablagh * d.tedad - isnull(d.MablagheTakhfif , 0.0))
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.MablaghArzeshAfzoodeh)
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0) -
case when m.Taf_ID_Takhfif > 0 then isnull(m.MablaghTakhfif,0) else 0.0 end +
case when m.Taf_ID_Ezafeh > 0 then isnull(m.MablaghEzafeh,0) else 0.0 end
else 0.0
end as [Bedehkar],
case
when m.Dom_ID_noe in (29,31,1276) then
isnull
(
cast((select sum(d.mablagh * d.tedad - isnull(d.MablagheTakhfif , 0.0))
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.MablaghArzeshAfzoodeh)
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0) -
case when m.Taf_ID_Takhfif > 0 then isnull(m.MablaghTakhfif,0) else 0.0 end +
case when m.Taf_ID_Ezafeh > 0 then isnull(m.MablaghEzafeh,0) else 0.0 end
when m.Dom_ID_noe in (2053,2079) then
isnull
(
cast((select sum(d.mablagh * d.tedad)
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0)
when m.Dom_ID_noe = 791 /* اضافات انبارگرداني */then
isnull
(
cast((select sum(d.mablagh * d.tedad)
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0)
when m.Dom_ID_noe = 1162 /*TrustedGoodsPurchase*/ then
isnull
(
cast((select sum(d.mablagh * d.tedad - isnull(d.MablagheTakhfif , 0.0))
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.MablaghArzeshAfzoodeh)
from BargehDetail d
where d.Bar_ID = m.ID) as decimal(19,4))
,0.0) -
case when m.Taf_ID_Takhfif > 0 then isnull(m.MablaghTakhfif,0) else 0.0 end +
case when m.Taf_ID_Ezafeh > 0 then isnull(m.MablaghEzafeh,0) else 0.0 end
else 0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 --BargashtAzForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 791 then 795 -- اضافات انبارگرداني
when 792 then 796 -- کسورات انبارگرداني
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
when 1275 then 1290 -- کالای مصرفی پروژه
when 1276 then 1291 -- کالای مانده از پروژه
when 2053 then 2085 -- صورت هزینه قرارداد
when 2079 then 2084 -- صورت هزینه پروژه
when 2071 then 2087 -- کالای مصرفی قرارداد
else 0
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
inner join Domain dom on m.Dom_ID_noe = dom.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (28,29,31,32,791,792,1162,1163,1190,1275,1276,1325,2053,2071,2079) and
cast(m.Taf_ID_Moshtari as int) = @Taf_ID and
@NoeGardesh = 206 and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- فاکتور مشتری - بصورت تک تک ردیف کالا گزارش می گیرد
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
dom.Title +
' (' + cast(m.Code as nvarchar)+ ') '+
case
when d.Dom_ID_Noe in (666,1871) then
(select name from Kala where ID = d.Kal_ID)+' ('+(select Code from Kala where ID = d.Kal_ID) +')'
when d.Dom_ID_Noe=667 or d.Dom_ID_Noe = 668 then
(select Title from tafsili where ID = d.Taf_ID_Khadamat)+' ('+ cast((select Code from Tafsili where ID = d.Taf_ID_Khadamat)as nvarchar) +')'
when d.Dom_ID_Noe= 2074 then isnull(d.Title,'') -- مفاد
end + isnull(d.sharheRadif,'') as [sharh],
d.Tedad[Meghdar],
case
when d.Dom_ID_Noe in (666,1871) then -- Kala
(select isnull(name,'') from OtherProperties v where v.ID = (select isnull(k.Pro_ID_Vahed,0) from Kala k where k.ID = d.Kal_ID))
when d.Dom_ID_Noe=667 or d.Dom_ID_Noe = 668 then --Khadamat,Hazineh
(select isnull(name,'') from OtherProperties v where v.ID = (select isnull(p.Pro_ID_unit,0) from tafsili t join OtherProperties p on t.ID = p.Taf_ID where t.ID = d.Taf_ID_Khadamat))
when d.Dom_ID_Noe= 2074 then '' -- مفاد
end [Vahed],
case
when m.Dom_ID_Noe in (1275,2071) then
isnull
(cast((
select sdai.Price
from StoreDocumentItem sdi
inner join StoreDocumentItemRefrenceID v on sdi.ID = v.SDI_ID
inner join StoreDocumentAccountItem sdai on sdi.ID = sdai.SDI_ID
where
v.BD_ID = d.ID
) as decimal(19,4)),0.0)
else d.Mablagh
end [Fi],
case
when m.Dom_ID_noe in (28 ,32 ,792,1163,1190,1325) then
cast((d.mablagh * d.Tedad ) as decimal(19,4))
when m.Dom_ID_Noe in (1275,2071) then
isnull
(cast((
select sum(sdai.Price * sdi.Quantity)
from StoreDocumentItem sdi
inner join StoreDocumentItemRefrenceID v on sdi.ID = v.SDI_ID
inner join StoreDocumentAccountItem sdai on sdi.ID = sdai.SDI_ID
where
v.BD_ID = d.ID
) as decimal(19,4)),0.0)
else 0.0
end as [Bedehkar],
case
when m.Dom_ID_noe in (29 ,31 ,791,1162,1276,2053,2079) then
cast((d.mablagh * d.Tedad ) as decimal(19,4))
else 0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 -- BargashtAzForoosh
when 30 then 182 -- PishFactorForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 791 then 795 -- اضافات انبارگرداني
when 792 then 796 -- کسورات انبارگرداني
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
when 1275 then 1290 -- کالای مصرفی پروژه
when 1276 then 1291 -- کالای مانده از پروژه
when 2053 then 2085 -- صورت هزینه قرارداد
when 2079 then 2084 -- صورت هزینه پروژه
when 2071 then 2087 -- صورت هزینه قرارداد
else 0
end as [NoeRadif],
m.ID [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join BargehDetail d on m.ID = d.Bar_ID
join Domain dom on m.Dom_ID_Noe = dom.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (28,29,31,32,791,792,1162,1163,1190,1275,1276,1325,2053,2071,2079)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid , اضافات انبارگرداني , کسورات انبارگرداني*/ and
@NoeGardesh = 207 and
cast(m.Taf_ID_Moshtari as int) = @Taf_ID and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- تخفیف ردیف فاکتور برای مشتری - بصورت تک تک کالا گزارش می گیرد
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'تخفیف ردیف '+dom.Title + ' ('+ cast(m.Code as nvarchar)+ ') ' +isnull(d.sharheRadif,'') as [sharh],
0 [Meghdar],
'' [Vahed],
isnull(d.MablagheTakhfif,0.0) [Fi],
case when m.Dom_ID_noe in (29,31,1162) then isnull(d.MablagheTakhfif,0.0) else 0.0 end as [Bedehkar],
case when m.Dom_ID_noe in (28,32,1163,1190,1325) then isnull(d.MablagheTakhfif,0.0) else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 -- BargashtAzForoosh
when 30 then 182 -- PishFactorForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join BargehDetail d on m.ID = d.Bar_ID
join Domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
@NoeGardesh = 207 and
d.MablagheTakhfif > 0 and
cast(m.Taf_ID_Moshtari as int) = @Taf_ID and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- ارزش افزوده مشتری - بصورت تک تک کالا گزارش می گیرد
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'ارزش افزوده ردیف '+ dom.title + ' (' + cast(m.Code as nvarchar)+ ') ' +isnull(d.sharheRadif,'') as [sharh],
0 [Meghdar],
'' [Vahed],
isnull(d.MablaghArzeshAfzoodeh,0.0)[Fi],
case when m.Dom_ID_noe in (28 ,32,1163,1190,1325) then isnull(d.MablaghArzeshAfzoodeh,0.0) else 0.0 end as [Bedehkar],
case when m.Dom_ID_noe in (29 ,31,1162) then isnull(d.MablaghArzeshAfzoodeh,0.0) else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 -- BargashtAzForoosh
when 30 then 182 -- PishFactorForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join BargehDetail d on m.ID = d.Bar_ID
join Domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
@NoeGardesh = 207 and
d.MablaghArzeshAfzoodeh > 0 and
cast(m.Taf_ID_Moshtari as int) = @Taf_ID and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
-------------------------------------------- وجه نقد فاکتور برای مشتری
select
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
b.tarikh as [Tarikh],
isnull(b.Saat,b.SaatSabt) [Saat],
'وجه نقد طی ' + dom.Title + ' (' + cast(b.Code as nvarchar) + ')' as [Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case when b.Dom_ID_Noe in (29,31,1162) then b.MablaghSandoogh else 0.0 end as [BedehKar],
case when b.Dom_ID_Noe in (28,30,1163,1190,1325) then b.MablaghSandoogh else 0.0 end as [BestanKar],
0.0 as [Mandeh],
'' [Mahiat],
case b.Dom_ID_Noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 --BargashtAzForoosh
when 30 then 182 -- PishFactorForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
b.ID as [Refrence_ID],
CAST(b.TarikhSabt as int) TarikhSabt
from Bargeh b
inner join OtherProperties opVahedMali on b.Pro_ID_VahedMali = opVahedMali.ID
inner join Domain dom on b.Dom_ID_Noe = dom.ID
where
b.Dom_ID_Vaziat != 101 and
b.Taf_ID_Moshtari = @Taf_ID and
b.MablaghSandoogh > 0 and
(@fc_ID = 0 or @fc_ID = b.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = b.Pro_ID_VahedMali)
union all
-------------------------------------------- وجه نقد فاکتور
select
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
b.tarikh as [Tarikh],
isnull(b.Saat,b.SaatSabt) [Saat],
'وجه نقد طی ' + dom.title + ' (' + cast(b.Code as nvarchar) + ')' as [Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case when b.Dom_ID_Noe in(28,30,32,1163,1190,1325) then b.MablaghSandoogh else 0.0 end as [BedehKar],
case when b.Dom_ID_Noe in(29,31,1162) then b.MablaghSandoogh else 0.0 end as [BestanKar],
0.0 as [Mandeh],
'' [Mahiat],
case b.Dom_ID_Noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 --BargashtAzForoosh
when 30 then 182 -- PishFactorForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
b.ID as [Refrence_ID],
CAST(b.TarikhSabt as int) TarikhSabt
from Bargeh b
inner join OtherProperties opVahedMali on b.Pro_ID_VahedMali = opVahedMali.ID
inner join Domain dom on b.Dom_ID_Noe = dom.ID
where
b.Dom_ID_Vaziat != 101 and
b.Taf_ID_Sandoogh = @Taf_ID and
b.MablaghSandoogh > 0 and
(@fc_ID = 0 or @fc_ID = b.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = b.Pro_ID_VahedMali)
union all
-------------------------------------------------------- Taf_ID_Khadamat برگه
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
dom.title+ ' ('+ cast(m.Code as nvarchar)+ ') ' +
case
when m.Dom_ID_Noe in (2053) then (select con.Title from Contract con where con.ID = m.Con_ID)
when m.Dom_ID_Noe in (2079) then (select pp.Title from PlanningProgram pp where pp.ID = m.PP_ID)
else (select Title from tafsili where ID = d.Taf_ID_Khadamat)+' ('+ cast((select Code from Tafsili where ID = d.Taf_ID_Khadamat)as nvarchar) +') '
end +
isnull(d.sharheRadif,'')
as [sharh],
d.Tedad[Meghdar],
(select isnull(name,'') from OtherProperties v where v.ID =
case
when d.Dom_ID_Noe=666 then -- Kala
(select isnull(k.Pro_ID_Vahed,0) from Kala k where k.ID = d.Kal_ID)
when d.Dom_ID_Noe in (667,668) then --Khadamat,Hazineh
(select isnull(p.Pro_ID_Unit,0) from tafsili t join OtherProperties p on t.ID = p.Taf_ID where t.ID = d.Taf_ID_Khadamat)
else 0
end) [Vahed],
d.Mablagh[Fi],
case
when m.Dom_ID_Noe in (2053,2079) then cast((d.mablagh * d.Tedad) as decimal(19,4))
else (case when d.Dom_ID_Noe in(668) then cast((d.mablagh * d.Tedad - isnull(d.MablagheTakhfif , 0.0)) as decimal(19,4)) else 0.0 end)
end
[Bedehkar],
case when d.Dom_ID_Noe = 667 then cast((d.mablagh * d.Tedad - isnull(d.MablagheTakhfif , 0.0)) as decimal(19,4)) else 0.0 end [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_Noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 -- BargashtAzForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1190 then 1197 -- StationFactor
when 2053 then 2085 -- صورت هزینه قرارداد
when 2079 then 2084 -- صورت هزینه پروژه
else 0
end as [NoeRadif],
m.ID [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join BargehDetail d on m.ID = d.Bar_ID
join domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat <> 101 and
m.Dom_ID_noe in (28,29,31,32,1190,1325,2053,2079)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
d.mablagh > 0 and
cast(d.Taf_ID_Khadamat as int) = @Taf_ID and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
------------------------------------------- هزینه تخفیف ردیف برگه
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'تخفیف ردیف '+dom.title+ ' ('++ cast(m.Code as nvarchar)+ ')' + isnull(d.sharheRadif,'') as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case when m.Dom_ID_noe in (28,32,1163,1190,1325) then d.mablagheTakhfif else 0.0 end as [Bedehkar],
case when m.Dom_ID_noe in (29,31,1162) then d.mablagheTakhfif else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 -- BargashtAzForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join BargehDetail d on m.ID = d.Bar_ID
join domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat <> 101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
d.mablagheTakhfif > 0 and
cast(d.Taf_ID_Takhfif as int) = @Taf_ID and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- هزینه تخفیف نهایی برگه
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'تخفیف '+dom.title+' ('+ cast(m.Code as nvarchar)+ ') ' as [sharh],
0 [Meghdar],
'' [Vahed],
isnull(m.MablaghTakhfif,0.0) [Fi],
case when m.Dom_ID_noe in (28,32,1163,1190,1325) then isnull(m.MablaghTakhfif,0.0) else 0.0 end as [Bedehkar],
case when m.Dom_ID_noe in(29,31,1162) then isnull(m.MablaghTakhfif,0.0) else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 -- BargashtAzForoosh
when 30 then 182 -- PishFactorForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
--@NoeGardesh = 207 and
m.MablaghTakhfif > 0 and
cast(m.Taf_ID_Takhfif as int) = @Taf_ID and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
--------------------------------------------------------------------------- تخفیف نهایی برگه برای مشتری
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'تخفیف نهایی '+dom.title+' ('+ cast(m.Code as nvarchar)+ ')' as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case when m.Dom_ID_noe in (29,31,1162) then cast((m.MablaghTakhfif) as decimal(19,4)) else 0.0 end as [Bedehkar],
case when m.Dom_ID_noe in (28,32,1163,1190,1325) then cast((m.MablaghTakhfif) as decimal(19,4)) else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 --BargashtAzForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
@NoeGardesh = 207 and
--d.Dom_ID_NoeMaster = 40 /*Hazine*/ and
m.MablaghTakhfif > 0 and
cast(m.Taf_ID_Moshtari as int) = @Taf_ID and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- اضافات نهایی برگه برای مشتری
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'اضافات ' + dom.title+' ('+cast(m.Code as nvarchar)+ ')' as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case when m.Dom_ID_noe in (28,32,1163,1190,1325) then cast((m.MablaghEzafeh) as decimal(19,4)) else 0.0 end as [Bedehkar],
case when m.Dom_ID_noe in (29,31,1162) then cast((m.MablaghEzafeh) as decimal(19,4)) else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 --BargashtAzForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat != 101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
@NoeGardesh = 207 and
m.MablaghEzafeh > 0 and
cast(m.Taf_ID_Moshtari as int) = @Taf_ID and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- اضافات نهایی برگه برای عطف
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'اضافات نهایی '+dom.title+' ('+ cast(m.Code as nvarchar)+ ')' as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
0.0 [Bedehkar],
case when m.Dom_ID_noe in(28,29,31,32,1162,1163,1190,1325) then cast((m.MablaghEzafeh) as decimal(19,4)) else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 --BargashtAzForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat != 101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
m.MablaghEzafeh > 0 and
cast(m.Taf_ID_Ezafeh as int) = @Taf_ID and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select ------------------------------------------------------------------------- ويزيتوري کل فاکتور
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
b.Tarikh[tarikh],
isnull(b.Saat,b.SaatSabt)[Saat],
case
when b.Dom_ID_Noe in (28,31,1163,1190,1325) then 'ويزيتوري نهایی '
when b.Dom_ID_Noe in (29,32,1162) then 'برگشت نهایی ويزيتوري در '
end + dom.Title + ' ('+ cast( b.Code as nvarchar) + ') - ' + (select t.Title from Tafsili t where t.ID = b.Taf_ID_Moshtari) as[Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case when b.Dom_ID_noe in (29,32,1162) then b.MablagheVisitor else 0.0 end [BedehKar],
case when b.Dom_ID_noe in (28,31,1163,1190,1325) then b.MablagheVisitor else 0.0 end [BestanKar],
0.0 [Mandeh],
''[Mahiat],
case b.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 --BargashtAzForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
b.ID [Refrence_ID],
CAST(b.TarikhSabt as int) TarikhSabt
from Bargeh b
inner join OtherProperties opVahedMali on b.Pro_ID_VahedMali = opVahedMali.ID
join Domain dom on dom.ID = b.Dom_ID_Noe
where
b.Dom_ID_Vaziat !=101 and
b.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
b.Taf_ID_Visitor = @Taf_ID and
b.MablagheVisitor is not null and
b.MablagheVisitor > 0 and
b.MablagheVisitor is not null and
(@fc_ID = 0 or @fc_ID = b.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = b.Pro_ID_VahedMali)
union all
------------------------------------------------------------------------- ويزيتوري اقلام
select
0[Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.tarikh[Tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
case
when m.Dom_ID_Noe in (28,31,1162,1163,1190,1325) then 'ويزيتوري '
when m.Dom_ID_Noe in (29,32) then 'برگشت ويزيتوري '
end+
dom.title+' ('+cast( m.Code as nvarchar)+') - '+ (select t.Title from Tafsili t where t.ID = m.Taf_ID_Moshtari)+' - '+
case d.Dom_ID_Noe
when 667 then -- Khadamat
'نام خدمات :'+(select Title +'('+ cast (Code as nvarchar)+')' from tafsili where ID = d.Taf_ID_Khadamat)
when 666 then --Kala
'نام کالا :' + (select name +'('+ cast (Code as nvarchar)+')' from Kala where ID = d.Kal_ID)
else ''
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case when m.Dom_ID_noe in (29,32) then d.MablagheVisitor else 0.0 end [Bedehkar],
case when m.Dom_ID_noe in (28,31,1162,1163,1190,1325) then d.MablagheVisitor else 0.0 end [Bestankar],
0.0[Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 -- BargashtAzForoosh
when 31 then 179 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join BargehDetail d on m.ID = d.Bar_ID
join domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat != 101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
cast(d.Taf_ID_Visitor as int) = @Taf_ID and
d.DarsadeVisitor is not null and
d.MablagheVisitor > 0 and
d.MablagheVisitor is not null and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
------------------------------------------------------------------------------ هزینه ویزیتوری در برگه
select -- هزینه ویزیتوری ردیف برگه
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
case when m.Dom_ID_noe in(28,31,1162,1163,1190,1325) then 'هزینه ویزیتوری ' when m.Dom_ID_noe in(29,32) then 'برگشت هزینه ویزیتوری در ' else '' end+
dom.title+' ('+ cast(m.Code as nvarchar)+ ') ' +
case
when d.Dom_ID_Noe = 666 then 'عنوان کالا: ' + (select name +'('+ cast (Code as nvarchar)+')' from Kala where ID = d.Kal_ID)
when d.Dom_ID_Noe = 667 then 'عنوان خدمات: ' + (select Title +'('+ cast (Code as nvarchar)+')' from Tafsili where ID = d.Taf_ID_Khadamat)
end
as [sharh],
0[Meghdar],
''[Vahed],
d.MablagheVisitor[Fi],
case when m.Dom_ID_noe in (28,31,1162,1163,1190,1325) then d.MablagheVisitor else 0.0 end as [Bedehkar],
case when m.Dom_ID_noe in (29,32) then d.MablagheVisitor else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 -- BargashtAzForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join BargehDetail d on m.ID = d.Bar_ID
join domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat <> 101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
d.MablagheVisitor > 0 and
cast(d.Taf_ID_HazinehVisitor as int) = @Taf_ID and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select -- هزینه ويزيتوري کل برگه
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
case when m.Dom_ID_noe in (28,31,1162,1163,1190,1325) then 'هزینه ویزیتوری ' when m.Dom_ID_noe in (29,32) then 'برگشت هزینه ویزیتوری در ' else '' end+
dom.Title + '('+ cast(m.Code as nvarchar)+ ')' as [sharh],
0[Meghdar],
''[Vahed],
m.MablagheVisitor[Fi],
case when m.Dom_ID_noe in(28,31,1162,1163,1190,1325) then m.MablagheVisitor else 0.0 end as [Bedehkar],
case when m.Dom_ID_noe in(29,32) then m.MablagheVisitor else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 -- BargashtAzForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat <> 101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
m.MablagheVisitor > 0 and
cast(m.Taf_ID_HazineVisitor as int) = @Taf_ID and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
------------------------------------------------------------------------------ Taf_ID_ArzeshAfzoodeh
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'ارزش افزوده '+dom.title+' (' + cast(m.Code as nvarchar)+ ')' + isnull(d.sharheRadif,'') as [sharh],
0[Meghdar],
''[Vahed],
d.MablaghArzeshAfzoodeh[Fi],
case when m.Dom_ID_noe in(29,32) then d.MablaghArzeshAfzoodeh else 0.0 end as [Bedehkar],
case when m.Dom_ID_noe in(28,31,1162,1163,1190,1325) then d.MablaghArzeshAfzoodeh else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 -- BargashtAzForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join BargehDetail d on m.ID = d.Bar_ID
join domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat <> 101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325)/*FactorForoosh,BargashAzFactorForoosh , FactorKharid,BargashtAzFactorKharid*/ and
d.MablaghArzeshAfzoodeh > 0 and
cast(d.Taf_ID_ArzeshAfzoodeh as int) = @Taf_ID and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
------------------------------------------------------------------------------ تخصيص مبلغ هزینه های فاکتور به اشخاص
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'تخصیص مبلغ '+(select Title from Tafsili where ID = d.taf_ID_Khadamat)+ ' طی '+ dom.Title +'('+ cast(m.Code as nvarchar)+ ') ' + isnull(d.sharheRadif,'') as [sharh],
0[Meghdar],
''[Vahed],
d.MablaghArzeshAfzoodeh[Fi],
0.0 [Bedehkar],
case when m.Dom_ID_noe In (28,29,31,32,1162,1163,1190,1325) then bdp.Mablagh else 0.0 end [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
when 1325 then 1437 --carwash
when 29 then 180 -- BargashtAzForoosh
when 31 then 178 -- Kharid
when 32 then 179 -- BargashtAzKharid
when 1162 then 1168 -- TrustedGoodsPurchase
when 1163 then 1169 -- TrustedGoodsSale
when 1190 then 1197 -- StationFactor
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join BargehDetail d on m.ID = d.Bar_ID
join BargehDetailProperty bdp on d.ID = bdp.BD_ID and bdp.Dom_ID_Type = 670 --تخصيص مبلغ به اشخاص
join Domain dom on m.Dom_ID_Noe = dom.ID
where
m.Dom_ID_Vaziat <> 101 and
m.Dom_ID_noe in (28,29,31,32,1162,1163,1190,1325) and
bdp.Mablagh > 0 and
cast(bdp.Taf_ID as int) = @Taf_ID and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
------------------------------------------------------------------------------ متمم خرید
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'متمم ' +
dom.title+' ('+ cast(m.Code as nvarchar)+ ') ' as [sharh],
0 [Meghdar],
''[Vahed],
p.Mablagh[Fi],
case when cast(p.Taf_ID as int) = @Taf_ID then p.Mablagh else 0.0 end as [Bedehkar],
case when cast(p.Taf_ID_Atf as int) = @Taf_ID then p.Mablagh else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
--when 28 then 177 --Foroosh
--when 29 then 180 -- BargashtAzForoosh
when 31 then 178 -- Kharid
--when 32 then 179 -- BargashtAzKharid
--when 1162 then 1168 -- TrustedGoodsPurchase
--when 1163 then 1169 -- TrustedGoodsSale
--when 1190 then 1197 -- StationFactor
else 0
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join BargehProperty p on m.ID = p.Bar_ID
join domain dom on dom.ID = m.Dom_ID_Noe
where
m.Dom_ID_Vaziat <> 101 and
m.Dom_ID_noe in (31) and
p.Mablagh > 0 and
(cast(p.Taf_ID as int) = @Taf_ID or cast(p.Taf_ID_Atf as int) = @Taf_ID) and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
------------------------------------------------------------------------------ باشگاه مشتریان
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
dom.title + ' در فاکتور فروش ('+ cast(m.Code as nvarchar)+ ') ' as [sharh],
0 [Meghdar],
''[Vahed],
p.Mablagh[Fi],
case when cast(p.Taf_ID as int) = @Taf_ID then p.Mablagh else 0.0 end as [Bedehkar],
case when cast(m.Taf_ID_Moshtari as int) = @Taf_ID then p.Mablagh else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_noe
when 28 then 177 --Foroosh
--when 29 then 180 -- BargashtAzForoosh
--when 31 then 178 -- Kharid
--when 32 then 179 -- BargashtAzKharid
--when 1162 then 1168 -- TrustedGoodsPurchase
--when 1163 then 1169 -- TrustedGoodsSale
--when 1190 then 1197 -- StationFactor
else 0
end as [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
join BargehProperty p on m.ID = p.Bar_ID and p.dom_ID_Type in (1709,1710)
join domain dom on dom.ID = p.dom_ID_Type
where
m.Dom_ID_Vaziat <> 101 and
m.Dom_ID_noe in (28) and
p.Mablagh > 0 and
(cast(p.Taf_ID as int) = @Taf_ID or cast(m.Taf_ID_Moshtari as int)= @Taf_ID) and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
-------------------------------------------------------------------- تولید
-- در تولید فقط باید دستمزد تولید اعمال شود و مابقی (هزینه تولید) اعمال نمی شود
-- مابقی به عنوان سربار در قیمت تمام شده لحاظ می گردد
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
d.Tarikh[tarikh],
m.SaatSabt[Saat],
'دستمزد تولید (' + cast(m.Code as nvarchar)+ ') ' + (select top 1 title from Domain dd where dd.ID = m.Dom_ID_Type) + isnull(d.sharh,'') [sharh],
d.Tedad [Meghdar],
'' [Vahed],
d.Price [Fi],
case when isnull(d.Taf_ID2,0) = @Taf_ID then ISNULL(d.Tedad, 1) * isnull(d.Price,0.0) else 0.0 end [Bedehkar],
case when isnull(d.Taf_ID,0) = @Taf_ID then ISNULL(d.Tedad, 1) * isnull(d.Price,0.0) else 0.0 end [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case d.Dom_ID_Noe
--when 261 /*Hazineh*/ then 1375
when 262 /*Dastmozd*/ then 200
else 0
end [NoeRadif], -- Tolid
d.ID [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Product m
join ProductDetail d on m.ID= d.Prod_ID
inner join OtherProperties opVahedMali on d.Pro_ID_VahedMali = opVahedMali.ID
where
m.Dom_ID_Type = 1583 /*فرمول متغیر*/ and
(@fc_ID = 0 or @fc_ID = d.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = d.Pro_ID_VahedMali) and
m.Dom_ID_Vaziat <> 101 and
d.Dom_ID_Noe in(262) /*دستمزد*/ and
(isnull(d.Taf_ID,0) = @Taf_ID or isnull(d.Taf_ID2,0) = @Taf_ID)
union all
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
m.SaatSabt[Saat],
'دستمزد تولید (' +
case
when m.Dom_ID_Type = 1584 then cast((select pp.code from Product pp where pp.ID = m.Prod_ID) as nvarchar)
else cast(m.Code as nvarchar)
end
+ ') ' + (select top 1 title from Domain dd where dd.ID = m.Dom_ID_Type) +' ' + isnull(d.sharh,'') [sharh],
d.Tedad [Meghdar],
'' [Vahed],
d.Price [Fi],
case when isnull(d.Taf_ID2,0) = @Taf_ID then ISNULL(d.Tedad, 1) * isnull(d.Price,0.0) else 0.0 end [Bedehkar],
case when isnull(d.Taf_ID,0) = @Taf_ID then ISNULL(d.Tedad, 1) * isnull(d.Price,0.0) else 0.0 end [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.Dom_ID_Type
when 1582 /*فرمول ثابت*/ then 1586
when 1584 /*فرمول ترکیبی*/ then 1588
else 0
end [NoeRadif], -- ProductFixed
case m.Dom_ID_Type
when 1582 /*فرمول ثابت*/ then m.ID
when 1584 /*فرمول ترکیبی*/ then m.Prod_ID
else 0
end [Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Product m
join ProductDetail d on m.ID= d.Prod_ID
inner join OtherProperties opVahedMali on d.Pro_ID_VahedMali = opVahedMali.ID
where
m.Dom_ID_Type in ( 1582 /*فرمول ثابت*/,1584 /*فرمول ترکیبی*/) and
(@fc_ID = 0 or @fc_ID = m.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali) and
m.Dom_ID_Vaziat <> 101 and
d.Dom_ID_Noe in(262) /*دستمزد*/ and
(isnull(d.Taf_ID,0) = @Taf_ID or isnull(d.Taf_ID2,0) = @Taf_ID)
union all
-------------------------------------------------------------------- حقوق دستمزد
-------------------------------------------------------------------- بستانکاری شخص
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
isnull((select MAX(fcd.jdate) from Finance_Cycle_Detail fcd where fcd.FC_ID = sr.FC_ID and fcd.[Month] = domMonth.Priority),0) [tarikh],
0 [Saat],
'حقوق ' + domMonth.Title [sharh],
1 [Meghdar],
'' [Vahed],
0.0 [Fi],
0.0 [Bedehkar],
isnull((select SUM(i.Price) from SalaryReceiptItem i where i.SR_ID = sr.ID and i.Dom_ID_DocType = 1600 /*افزایشی*/),0) - isnull((select SUM(i.Price) from SalaryReceiptItem i where i.SR_ID = sr.ID and i.Dom_ID_DocType = 1601 /*کاهشی*/),0) [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1884 [NoeRadif], -- SalaryReceipt
sr.ID [Refrence_ID],
CAST(mat.TarikhSabt as int) TarikhSabt
from SalaryReceipt sr
inner join OtherProperties opVahedMali on sr.Pro_ID_VahedMali = opVahedMali.ID
inner join Matter mat on mat.ID = sr.Mat_ID
inner join Domain domMonth on sr.Dom_ID_Month = domMonth.ID
where
(@fc_ID = 0 or @fc_ID = sr.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = sr.Pro_ID_VahedMali) and
isnull(sr.Taf_ID,0) = @Taf_ID
union all
-------------------------------------------------------------------- بدهکاری هزینه حقوق
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
isnull((select MAX(fcd.jdate) from Finance_Cycle_Detail fcd where fcd.FC_ID = sr.FC_ID and fcd.[Month] = domMonth.Priority),0) [tarikh],
0 [Saat],
'هزینه حقوق ' + domMonth.Title +' '+(select t.title from tafsili t where t.id = sr.taf_Id) [sharh],
1 [Meghdar],
'' [Vahed],
0.0 [Fi],
isnull((select SUM(i.Price) from SalaryReceiptItem i where i.SR_ID = sr.ID and i.Dom_ID_DocType in (1600 /*افزایشی*/, 1606/*خنثی*/)),0) [Bedehkar],
0.0 [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1884 [NoeRadif], -- SalaryReceipt
sr.ID [Refrence_ID],
CAST(mat.TarikhSabt as int) TarikhSabt
from SalaryReceipt sr
inner join OtherProperties opVahedMali on sr.Pro_ID_VahedMali = opVahedMali.ID
inner join Matter mat on mat.ID = sr.Mat_ID
inner join Domain domMonth on sr.Dom_ID_Month = domMonth.ID
where
(@fc_ID = 0 or @fc_ID = sr.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = sr.Pro_ID_VahedMali) and
isnull(sr.Taf_ID_SalaryCost,0) = @Taf_ID
union all
-------------------------------------------------------------------- بستانکاری مالیات
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
isnull((select MAX(fcd.jdate) from Finance_Cycle_Detail fcd where fcd.FC_ID = sr.FC_ID and fcd.[Month] = domMonth.Priority),0) [tarikh],
0 [Saat],
sri.Description + ' حقوق '+(select t.title from tafsili t where t.id = sr.taf_Id) [sharh],
1 [Meghdar],
'' [Vahed],
sri.Price [Fi],
0.0 [Bedehkar],
sri.Price [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1884 [NoeRadif], -- SalaryReceipt
sr.ID [Refrence_ID],
CAST(mat.TarikhSabt as int) TarikhSabt
from SalaryReceipt sr
inner join OtherProperties opVahedMali on sr.Pro_ID_VahedMali = opVahedMali.ID
inner join SalaryReceiptItem sri on sr.ID = sri.SR_ID
inner join Matter mat on mat.ID = sr.Mat_ID
inner join Domain domMonth on sr.Dom_ID_Month = domMonth.ID
where
(@fc_ID = 0 or @fc_ID = sr.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = sr.Pro_ID_VahedMali) and
sri.Dom_ID_RowType = 318 /*ماليات*/ and
isnull(sr.Taf_ID_Tax,0) = @Taf_ID
union all
-------------------------------------------------------------------- بستانکاری بیمه تامین اجتماعی
select
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
isnull((select MAX(fcd.jdate) from Finance_Cycle_Detail fcd where fcd.FC_ID = sr.FC_ID and fcd.[Month] = domMonth.Priority),0) [tarikh],
0 [Saat],
sri.Description [sharh],
1 [Meghdar],
'' [Vahed],
sri.Price [Fi],
0.0 [Bedehkar],
sri.Price [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1884 [NoeRadif], -- SalaryReceipt
sr.ID [Refrence_ID],
CAST(mat.TarikhSabt as int) TarikhSabt
from SalaryReceipt sr
inner join OtherProperties opVahedMali on sr.Pro_ID_VahedMali = opVahedMali.ID
inner join SalaryReceiptItem sri on sr.ID = sri.SR_ID
inner join Matter mat on mat.ID = sr.Mat_ID
inner join Domain domMonth on sr.Dom_ID_Month = domMonth.ID
where
(@fc_ID = 0 or @fc_ID = sr.fc_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = sr.Pro_ID_VahedMali) and
sri.Dom_ID_RowType in (330,329,1604,1605) /*بيمه سهم کارگر، بيمه سهم کارفرما، بیمه بیکاری، بیمه -سختی کار*/ and
isnull(sr.Taf_ID_Insurance,0) = @Taf_ID
-------------------------------------------------------------------- املاک
union all
select --------------------------------------------------------------------------- بستانکاری خدمات کمیسیون
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
(select d.Title from domain d where d.id = isnull((select b.dom_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1906),0)) + ' (' + cast(m.Code as nvarchar)+ ') بین ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1892),0)) + ' و ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1893),0))
[sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
0 [Bedehkar],
ISNULL(bp.Mablagh,0)/*-ISNULL(bp.Darsad,0)*/ [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1910 [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
inner join BargehProperty bp on m.ID = bp.Bar_ID and bp.Dom_ID_Type = 1901 /*کمیسیون بنگاه*/
inner join Domain dom on m.Dom_ID_noe = dom.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (1889)/*املاک*/ and
cast(m.Taf_ID_Moshtari as int) = @Taf_ID and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- بدهکاری مالک بابت کمیسیون
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'کمیسیون '+dom.title + ' (' + cast(m.Code as nvarchar)+ ')' [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
--ISNULL(bp.Mablagh,0) - isnull((select b.mablagh from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1895 /*تخفیف کمیسون فروشنده*/),0) [Bedehkar],
ISNULL(bpTafsili.Mablagh,0) -ISNULL(bpTafsili.Darsad,0) [Bedehkar],
0 [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1910 [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
inner join BargehProperty bpTafsili on m.ID = bpTafsili.Bar_ID and bpTafsili.Dom_ID_Type = 1892 /*کد فروشنده*/
--inner join BargehProperty bp on m.ID = bp.Bar_ID and bp.Dom_ID_Type = 1894 /*کمیسیون فروشنده*/
inner join Domain dom on m.Dom_ID_noe = dom.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (1889)/*املاک*/ and
cast(bpTafsili.Taf_ID as int) = @Taf_ID and
ISNULL(bpTafsili.Mablagh,0) > 0 and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- بدهکاری مستاجر بابت کمیسیون
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'کمیسیون '+dom.title + ' (' + cast(m.Code as nvarchar)+ ')' [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
--ISNULL(bp.Mablagh,0) - isnull((select b.mablagh from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1897 /*تخفیف کمیسون مستاجر*/),0) [Bedehkar],
ISNULL(bpTafsili.Mablagh,0) - ISNULL(bpTafsili.Darsad,0) [Bedehkar],
0 [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1910 [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
inner join BargehProperty bpTafsili on m.ID = bpTafsili.Bar_ID and bpTafsili.Dom_ID_Type = 1893 /*کد مستاجر*/
--inner join BargehProperty bp on m.ID = bp.Bar_ID and bp.Dom_ID_Type = 1896 /*کمیسیون مستاجر*/
inner join Domain dom on m.Dom_ID_noe = dom.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (1889)/*املاک*/ and
cast(bpTafsili.Taf_ID as int) = @Taf_ID and
ISNULL(bpTafsili.Mablagh,0) > 0 and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- بستانکاری ارزش افزوده
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
--'ارزش افزوده '+dom.title + ' (' + cast(m.Code as nvarchar)+ ') ' +
'ارزش افزوده '+ (select d.Title from domain d where d.id = isnull((select b.dom_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1906),0)) + ' (' + cast(m.Code as nvarchar)+ ') ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1892),0)) + ' - ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1893),0))
[sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
0 [Bedehkar],
ISNULL(bp.Mablagh,0) [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1910 [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
inner join BargehProperty bp on m.ID = bp.Bar_ID and bp.Dom_ID_Type = 1907 /*ارزش افزوده*/
inner join Domain dom on m.Dom_ID_noe = dom.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (1889)/*املاک*/ and
cast(m.Taf_ID_Ezafeh as int) = @Taf_ID and
ISNULL(bp.Mablagh,0) > 0 and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- بستانکاری ویزیتورها
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
--'ارزش افزوده '+dom.title + ' (' + cast(m.Code as nvarchar)+ ') ' +
case
when isnull(bp.Darsad,0) > 0 then cast(cast(isnull(bp.Darsad,0) as int) as nvarchar(10)) + ' درصد '
else ''
end + 'ویزیتوری '+ (select d.Title from domain d where d.id = isnull((select b.dom_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1906),0)) + ' (' + cast(m.Code as nvarchar)+ ') ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1892),0)) + ' - ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1893),0))
[sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
0 [Bedehkar],
ISNULL(bp.Mablagh,0) [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1910 [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
inner join BargehProperty bp on m.ID = bp.Bar_ID and bp.Dom_ID_Type = 627 /*ویزیتور*/
inner join Domain dom on m.Dom_ID_noe = dom.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (1889)/*املاک*/ and
cast(bp.Taf_ID as int) = @Taf_ID and
ISNULL(bp.Mablagh,0) > 0 and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- بدهکاری هزینه ویزیتورها
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'هزینه ویزیتوری '+ (select d.Title from domain d where d.id = isnull((select b.dom_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1906),0)) + ' (' + cast(m.Code as nvarchar)+ ') ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1892),0)) + ' - ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1893),0))
[sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
ISNULL(bp.Mablagh,0) [Bedehkar],
0 [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1910 [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
inner join BargehProperty bp on m.ID = bp.Bar_ID and bp.Dom_ID_Type = 627 /*ویزیتور*/
inner join Domain dom on m.Dom_ID_noe = dom.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (1889)/*املاک*/ and
cast(m.Taf_ID_HazineVisitor as int) = @Taf_ID and
ISNULL(bp.Mablagh,0) > 0 and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- بدهکاری تخفیف کمیسیون بنگاه
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'تخفیف کمیسیون بنگاه در '+ (select d.Title from domain d where d.id = isnull((select b.dom_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1906),0)) + ' (' + cast(m.Code as nvarchar)+ ') ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1892),0)) + ' - ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1893),0))
[sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
ISNULL(bp.Darsad,0) [Bedehkar],
0 [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1910 [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
inner join BargehProperty bp on m.ID = bp.Bar_ID and bp.Dom_ID_Type = 1901 /*کمیسیون بنگاه*/
inner join Domain dom on m.Dom_ID_noe = dom.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (1889)/*املاک*/ and
cast(m.Taf_ID_Takhfif as int) = @Taf_ID and
ISNULL(bp.Mablagh,0) > 0 and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- بدهکاری تخفیف کمیسیون مالک/فروشنده
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'تخفیف کمیسیون '+
case (select b.dom_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1906)
when 1904 then 'فروشنده'
else 'مالک'
end+
' در '+ (select d.Title from domain d where d.id = isnull((select b.dom_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1906),0)) + ' (' + cast(m.Code as nvarchar)+ ') ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1892),0)) + ' - ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1893),0))
[sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
ISNULL(bp.Darsad,0) [Bedehkar],
0 [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1910 [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
inner join BargehProperty bp on m.ID = bp.Bar_ID and bp.Dom_ID_Type = 1892 /*کد فروشنده | مالک*/
inner join Domain dom on m.Dom_ID_noe = dom.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (1889)/*املاک*/ and
cast(m.Taf_ID_Takhfif as int) = @Taf_ID and
ISNULL(bp.Mablagh,0) > 0 and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- بدهکاری تخفیف کمیسیون مستاجر/خریدار
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
m.Tarikh[tarikh],
isnull(m.Saat,m.SaatSabt)[Saat],
'تخفیف کمیسیون '+
case (select b.dom_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1906)
when 1904 then 'خریدار'
else 'مستاجر'
end+
' در '+ (select d.Title from domain d where d.id = isnull((select b.dom_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1906),0)) + ' (' + cast(m.Code as nvarchar)+ ') ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1892),0)) + ' - ' +
(select t.Title from Tafsili t where t.id = isnull((select b.Taf_ID from BargehProperty b where b.bar_ID = m.id and b.Dom_ID_Type = 1893),0))
[sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
ISNULL(bp.Darsad,0) [Bedehkar],
0 [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
1910 [NoeRadif],
m.ID[Refrence_ID],
CAST(m.TarikhSabt as int) TarikhSabt
from Bargeh m
inner join OtherProperties opVahedMali on m.Pro_ID_VahedMali = opVahedMali.ID
inner join BargehProperty bp on m.ID = bp.Bar_ID and bp.Dom_ID_Type = 1893 /*کد خریدار | مستاجر*/
inner join Domain dom on m.Dom_ID_noe = dom.ID
where
m.Dom_ID_Vaziat !=101 and
m.Dom_ID_noe in (1889)/*املاک*/ and
cast(m.Taf_ID_Takhfif as int) = @Taf_ID and
ISNULL(bp.Mablagh,0) > 0 and
(@Fc_ID = 0 or @Fc_ID = m.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = m.Pro_ID_VahedMali)
union all
select --------------------------------------------------------------------------- سند حسابداری
0 as [Radif],
opVahedMali.Title Pro_Title_VahedMali,
vou.Tarikh[tarikh],
vou.Saat [Saat],
'سند حسابداری ('+CAST(vou.Code as nvarchar(10))+')' [sharh],
0[Meghdar],
''[Vahed],
case when vi.Debit > 0 then vi.Debit else vi.Credit end [Fi],
ISNULL(vi.Debit,0) [Bedehkar],
ISNULL(vi.Credit,0) [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
2097 [NoeRadif],
vou.ID [Refrence_ID],
CAST(vou.TarikhSabt as int) TarikhSabt
from Tafsili taf
inner join OtherProperties opVahedMali on taf.Pro_ID_VahedMali = opVahedMali.ID
inner join TafsiliProperty tp on taf.ID = tp.Taf_ID and tp.Dom_ID = 851 and tp.AI_ID is not null
join VoucherItem vi on tp.AI_ID = vi.AI_ID_Taf1
join Voucher vou on vi.Vou_ID = vou.ID and vou.Dom_ID_Marja = 127 /*حسابداری عمومی*/ and vou.Dom_ID_Noe = 159 /*طی دوره*/
where
taf.ID = @Taf_ID and
(@Fc_ID = 0 or @Fc_ID = vou.FC_ID) and
(@Pro_ID_VahedMali = 0 or @Pro_ID_VahedMali = vou.Pro_ID_VahedMali)
end