/****** Object:  View [dbo].[ViwFactorTatbigSanad]    Script Date: 10/22/2025 11:55:51 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*
use [darokhane zavare 1404] 
select * from [dbo].[ViwFactorTatbigSanad]
*/
CREATE VIEW [dbo].[ViwFactorTatbigSanad]
AS

select N'سند بند' as DocType, Fact.Number, Fact.ID as FactID, Item.id as ItemID, SUM(Doc.Debit) as DocDebit, SUM(Doc.Credit) as DocCredit, Avg(Item.TotalPrice+Item.Discont) as ItemPrice
from (select * from AccDocItem with(nolock) where RelationTable=12 and not Description like N'بابت تخفيف %')as Doc   left outer join
AccFactorDetail as Item with(nolock)  on Doc.RelationTable=12 and Doc.RelationTableID=Item.ID left outer join
AccFactor as Fact with(nolock) on Fact.ID=Item.FactorID 
group by Fact.Number, Fact.id, Item.id
having  SUM(Doc.Credit) <> Avg(Item.TotalPrice+Item.Discont) and SUM(Doc.Debit) <> Avg(Item.TotalPrice+Item.Discont)

union

select N'سند تخفیف و هزینه حمل' as DocType, Fact.Number, Fact.ID as FactID, Item.id as ItemID, SUM(Doc.Debit) as DocDebit, SUM(Doc.Credit) as DocCredit, Avg(Fact.Discont) as FactDiscont
from (select * from AccDocItem with(nolock) where RelationTable=12 and Description like N'بابت تخفيف %')as Doc   left outer join
AccFactorDetail as Item with(nolock)  on Doc.RelationTable=12 and Doc.RelationTableID=Item.ID left outer join
AccFactor as Fact with(nolock) on Fact.ID=Item.FactorID 
group by Fact.Number, Fact.id, Item.id
having  SUM(Doc.Credit) <>  Avg(Fact.Discont) or SUM(Doc.Debit) <>  Avg(Fact.Discont)

GO

