
Use ATNDBTest
Go


BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.FactorHeader ADD
	Sum_TotalPrice bigint NULL,
	Sum_TotalPriceInForeignCurrency money NULL,
	Sum_TotalPriceForTax bigint NULL,
	Sum_TotalPriceForTaxInForeignCurrency money NULL
GO
ALTER TABLE dbo.FactorHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT









-- ابتدا ویوهای زیر را ایجاد می کنیم. چون ممکن است حذف شده باشد یا در ستنهایی به آنها
--                                اضافه شده باشند که در زیر مورد استفاده قرار گرفته است

/*=============================================================================================================*/
Set Ansi_Nulls On
Go
Set Quoted_Identifier On
Go
If Exists(Select * From sys.Views Where name = 'PartList') Drop View dbo.PartList
Go
Create View dbo.PartList
With Encryption
As
	Select
		dbo.Part.Serial As PartSerial, 
		dbo.Part.Code As PartCode, 
		dbo.Part.Barcode As PartBarcode, 
		dbo.Part.BarcodeImg As PartBarcodeImage, 
		dbo.Part.PartImg As PartImage, 
		dbo.Part.AddedTaxInBuy As AddedTaxInBuy, 
		dbo.Part.AddedTaxInSales As AddedTaxInSales, 
		dbo.Part.Name As PartName, 
		dbo.Part.ENName As PartENName, 
		dbo.Part.OEM, 
		dbo.Storages.Serial As StorageSerial, 
		dbo.Storages.Code As StorageCode, 
		dbo.Storages.Detail As StorageName, 
		dbo.Groups.Serial As GroupSerial, 
		dbo.Groups.Code As GroupCode, 
		dbo.Groups.Detail As GroupName, 
		dbo.Part.Active,
		Users_1.Name As Recorder, 
		dbo.Part.RecordDate,
		Users_2.Name As Editor, 
		dbo.Part.EditDate, 
		dbo.PartKind.Serial As PartKindSerial, 
		dbo.PartKind.Description As PartKindDescription, 
		dbo.Part.Reserve, 
		dbo.Part.Remain, 
		dbo.Part.Orders,
		dbo.Part.MaxRemain, 
		dbo.Part.MinRemain, 
		dbo.Units.Serial As UnitSerial, 
		dbo.Units.Code As UnitCode, 
		dbo.Units.Detail As UnitName, 
		dbo.Part.ShelfNo, 
		dbo.Part.UnitWeight, 
		dbo.PartBuyPriceKind.Serial As PartBuyPriceKindSerial, 
		dbo.PartBuyPriceKind.Description As PartBuyPriceKindDescription, 
		dbo.Part.BuyUnitPrice, 
		dbo.Part.BuyUnitPriceInForeignCurrency, 
		dbo.Part.SaleDependToBuy, 
		dbo.PartProfitPercentKind.Serial As PartProfitPercentKindSerial, 
		dbo.PartProfitPercentKind.Description As PartProfitPercentKindDescription, 
		dbo.PartSalePriceScope.Serial As PartSalePriceScopeSerial, 
		dbo.PartSalePriceScope.Description As PartSalePriceScopeDescription, 
		dbo.Part.ForeignCurrencyForBuy, 
		dbo.Part.ForeignCurrencyForSale, 
		dbo.Part.Comment, 
		dbo.Part.OfficeDetailRef
	From	
		dbo.Part Inner Join
		dbo.Storages On dbo.Part.StorageRef = dbo.Storages.Serial Left Join
		dbo.Groups On dbo.Part.GroupRef = dbo.Groups.Serial Inner Join
		dbo.Units On dbo.Part.UnitRef = dbo.Units.Serial Inner Join
		dbo.PartBuyPriceKind On dbo.Part.PartBuyPriceKindRef = dbo.PartBuyPriceKind.Serial Inner Join
		dbo.PartKind On dbo.Part.PartKindRef = dbo.PartKind.Serial Inner Join
		dbo.PartProfitPercentKind On dbo.Part.PartProfitPercentKindRef = dbo.PartProfitPercentKind.Serial Inner Join
		dbo.PartSalePriceScope On dbo.Part.PartSalePriceScopeRef = dbo.PartSalePriceScope.Serial Left Join
		dbo.Users As Users_1 On Users_1.Serial = dbo.Part.RecorderRef Left Join
		dbo.Users As Users_2 On Users_2.Serial = dbo.Part.EditorRef
Go
/*=============================================================================================================*/
Set Ansi_Nulls On
Go
Set Quoted_Identifier On
Go
If Exists(Select * From sys.Views Where name = 'FactorHeaderAndDetailList') Drop View dbo.FactorHeaderAndDetailList
Go
Create View dbo.FactorHeaderAndDetailList
With Encryption
As
Select 
	*,
	VATForEachRow = Cast((Case When Sum_TotalPriceForTax > 0 Then VAT * AddedTaxFactor * (Cast(TotalPrice As Decimal(35,15)) / Cast(Sum_TotalPriceForTax As Decimal(35,15))) Else 0 End) As Bigint),
	TollForEachRow = Cast((Case When Sum_TotalPriceForTax > 0 Then Toll * AddedTaxFactor * (Cast(TotalPrice As Decimal(35,15)) / Cast(Sum_TotalPriceForTax As Decimal(35,15))) Else 0 End) As Bigint),
	Cost1thForEachRow = Cast((Case When Sum_TotalPriceForTax > 0 Then Cost1th * AddedTaxFactor * (Cast(TotalPrice As Decimal(35,15)) / Cast(Sum_TotalPriceForTax As Decimal(35,15))) Else 0 End) As Bigint),
	Cost2thForEachRow = Cast((Case When Sum_TotalPrice > 0 Then Cost2th * (Cast(TotalPrice As Decimal(35,15)) / Cast(Sum_TotalPrice As Decimal(35,15))) Else 0 End) As Bigint),
	RebateForEachRow = Cast((Case When Sum_TotalPrice > 0 Then Rebate * (Cast(TotalPrice As Decimal(35,15)) / Cast(Sum_TotalPrice As Decimal(35,15))) Else 0 End) As Bigint)
From
	(Select	
		TOP (100) PERCENT 
		dbo.FactorDetail.Serial As FactorDetailSerial, 
		dbo.FactorHeader.Serial As FactorHeaderSerial, 
		dbo.FactorHeader.Code As FactorHeaderCode, 
		dbo.FactorKind.Serial As FactorKindSerial,
		dbo.FactorKind.FA_Description As FactorKindFA_Description, 
		dbo.FactorKind.EN_Description As FactorKindEN_Description, 
		dbo.FactorKind.Stream As FactorKindStream, 
		dbo.FactorKind.Priority,
		dbo.FactorHeader.AccDocumentHeaderRefOnVerification As DocumentHeaderSerialOnVerification,
		dbo.FactorHeader.AccDocumentHeaderRefOnUpdating As DocumentHeaderSerialOnUpdating,
		DocumentHeaderTempCode = 
			(Select dbo.Concatenate(TempCode) From dbo.AccDocumentHeader Where (Serial = dbo.FactorHeader.AccDocumentHeaderRefOnVerification Or Serial = dbo.FactorHeader.AccDocumentHeaderRefOnUpdating)), 
		DocumentHeaderFinalCode = 
			(Select IsNull(Ltrim(Rtrim(Cast(FinalCode1 As Varchar(max)))),'') + Case When (FinalCode1 > 0 And FinalCode2 > 0) Then ', ' Else '' End + IsNull(Ltrim(Rtrim(Cast(FinalCode2 As Varchar(max)))),'') From (Select dbo.Extract_DocumentHeaderFinalCode(dbo.FactorHeader.AccDocumentHeaderRefOnVerification) As FinalCode1 , dbo.Extract_DocumentHeaderFinalCode(dbo.FactorHeader.AccDocumentHeaderRefOnUpdating) As FinalCode2)As Tbl1),
		dbo.GetterOrPayerFactorList.GetterOrPayerFactorSerial, 
		dbo.GetterOrPayerFactorList.GetterOrPayerFactorCode, 
		dbo.GetterOrPayerFactorList.GetterOrPayerFactorFAName, 
		dbo.GetterOrPayerFactorList.GetterOrPayerFactorENName, 
		dbo.GetterOrPayerFactorList.Manager As GetterOrPayerFactorManager, 
		dbo.GetterOrPayerFactorList.DegreeSerial, 
		dbo.GetterOrPayerFactorList.FADegreeDescription, 
		dbo.GetterOrPayerFactorList.ENDegreeDescription, 
		dbo.GetterOrPayerFactorList.GetterOrPayerFactorCategorySerial,
		dbo.GetterOrPayerFactorList.GetterOrPayerFactorCategoryCode,
		dbo.GetterOrPayerFactorList.GetterOrPayerFactorCategoryFADescription,
		dbo.GetterOrPayerFactorList.GetterOrPayerFactorCategoryENDescription,
		dbo.GetterOrPayerFactorList.Address As GetterOrPayerFactorAddress, 
		dbo.GetterOrPayerFactorList.PostCode, 
		dbo.GetterOrPayerFactorList.Email,
		dbo.GetterOrPayerFactorList.Website,
		dbo.GetterOrPayerFactorList.RegNo,
		dbo.GetterOrPayerFactorList.SSN,
		dbo.GetterOrPayerFactorList.EconomicCode,
		dbo.GetterOrPayerFactorList.Tel1 As GetterOrPayerFactorTel1, 
		dbo.GetterOrPayerFactorList.Tel2 As GetterOrPayerFactorTel2, 
		dbo.GetterOrPayerFactorList.Fax As GetterOrPayerFactorFax, 
		dbo.GetterOrPayerFactorList.Mobile As GetterOrPayerFactorMobile, 
		dbo.GetterOrPayerFactorList.BankName AS GetterOrPayerFactorBankName,
		dbo.GetterOrPayerFactorList.OffshootCode AS GetterOrPayerFactorOffshootCode,
		dbo.GetterOrPayerFactorList.OffshootName AS GetterOrPayerFactorOffshootName,
		dbo.GetterOrPayerFactorList.BankAccountNo AS GetterOrPayerFactorBankAccountNo,
		dbo.GetterOrPayerFactorList.IBAN AS GetterOrPayerFactorIBAN,
		dbo.GetterOrPayerFactorList.BankAddress AS GetterOrPayerFactorBankAddress,
		dbo.GetterOrPayerFactorList.AccountMaximum As GetterOrPayerFactorAccountMaximum,
		dbo.GetterOrPayerFactorList.SaleProfitPercent As GetterOrPayerFactorSaleProfitPercent,
		dbo.GetterOrPayerFactorList.SaleRebatePercent As GetterOrPayerFactorSaleRebatePercent,
		dbo.GetterOrPayerFactorList.EntityL1Serial,
		dbo.GetterOrPayerFactorList.EntityL1Code,
		dbo.GetterOrPayerFactorList.EntityL1FADetail,
		dbo.GetterOrPayerFactorList.EntityL1ENDetail,
		dbo.GetterOrPayerFactorList.EntityL2Serial,
		dbo.GetterOrPayerFactorList.EntityL2Code,
		dbo.GetterOrPayerFactorList.EntityL2FADetail,
		dbo.GetterOrPayerFactorList.EntityL2ENDetail,
		dbo.FactorHeader.ControlNo, 
		dbo.Warehouse.Serial As WarehouseSerial, 
		dbo.Warehouse.Code As WarehouseCode, 
		dbo.Warehouse.Detail As WarehouseDetail, 
		dbo.FactorCategoryList.FactorCategorySerial,
		dbo.FactorCategoryList.FactorCategoryCode,
		dbo.FactorCategoryList.FactorCategoryFADescription,
		dbo.FactorCategoryList.FactorCategoryENDescription,
		dbo.FactorHeader.EffectiveDate, 
		dbo.FactorExtendedOption.Expression1,
		dbo.FactorExtendedOption.Expression2,
		dbo.FactorExtendedOption.Expression3,
		dbo.FactorExtendedOption.Expression4,
		dbo.FactorExtendedOption.Expression5,
		dbo.FactorHeader.RecorderRef As RecorderSerial,
		Users_1.Name As RecorderName, 
		dbo.FactorHeader.RecordDate, 
		dbo.FactorHeader.EditorRef As EditorSerial,
		Users_2.Name As EditorName, 
		dbo.FactorHeader.EditDate, 
		dbo.FactorHeader.VerifierRef As VerifierSerial,
		Users_3.Name As VerifierName, 
		dbo.FactorHeader.VerificationDate, 
		dbo.FactorHeader.UpdatingUserRef As UpdatingUserSerial,
		Users_4.Name As UpdatingUserName, 
		dbo.FactorHeader.UpdatingDate, 
		AddedTaxFactor = Case When (dbo.FactorKind.Serial = 11 Or dbo.FactorKind.Serial = 12 Or dbo.FactorKind.Serial = 13 Or dbo.FactorKind.Serial = 14) Then dbo.PartList.AddedTaxInBuy
		    				  When (dbo.FactorKind.Serial = 21 Or dbo.FactorKind.Serial = 22 Or dbo.FactorKind.Serial = 23 Or dbo.FactorKind.Serial = 24) Then dbo.PartList.AddedTaxInSales 
		    				  Else Cast(0 As Bit) End, -- چون فقط در خرید و فروش مالیات و عوارض داریم، الباقی حالات را صفر کردم که محاسبه نگردد
		VAT = IsNull(dbo.FactorHeader.VAT,0),
		Toll = IsNull(dbo.FactorHeader.Toll,0),
		Cost1th = IsNull(dbo.FactorHeader.VAT,0) + IsNull(dbo.FactorHeader.Toll,0), 
		Cost2th = IsNull(dbo.FactorHeader.Cost2th,0),
		Rebate = IsNull(dbo.FactorHeader.Rebate,0),
		dbo.FactorHeader.RebateRound, 
		dbo.FactorHeader.ForeignCurrencyFactor,
		dbo.FactorHeader.Sum_TotalPrice, 
		dbo.FactorHeader.Sum_TotalPriceInForeignCurrency, 
		dbo.FactorHeader.Sum_TotalPriceForTax, 
		dbo.FactorHeader.Sum_TotalPriceForTaxInForeignCurrency, 
		dbo.FactorHeader.TotalPaid, 
		dbo.FactorHeader.TotalPaidInForeignCurrency,
		TblCashGePay.CashGetPayAmount,
		TblSettlementGePay.SettlementGetPayAmount,
		TblCheckGetPay.CheckGetPayAmount,
		IsNull(TblCashGePay.CashGetPayAmount,0) + IsNull(TblSettlementGePay.SettlementGetPayAmount,0) + IsNull(TblCheckGetPay.CheckGetPayAmount,0) As TotalAmountPaid,
		IsNull(FactorHeader.TotalPaid,0) - (IsNull(TblCashGePay.CashGetPayAmount,0) + IsNull(TblSettlementGePay.SettlementGetPayAmount,0) + IsNull(TblCheckGetPay.CheckGetPayAmount,0)) As TotalAmountRemain,
		dbo.FactorHeader.PayDate, 
		dbo.FactorHeader.Mechanize, 
		dbo.FactorHeader.Description, 
		dbo.FactorHeader.SaleBrokerPercent,
		dbo.FactorHeader.SaleBrokerPrice,
		dbo.FactorHeader.WarehouseVerificationCode, 
		dbo.FactorHeader.WarehouseVerificationDate, 
		dbo.FactorHeader.WarehousePrintDateTime,
		dbo.FactorHeader.WarehousePrintRowVersion,
		dbo.FactorHeader.WarehousePrintedCounter,
		dbo.ForeignCurrencySymbol.Serial As ForeignCurrencySymbolSerial,
		dbo.ForeignCurrencySymbol.Code As ForeignCurrencySymbolCode,
		dbo.ForeignCurrencySymbol.FADes As ForeignCurrencySymbolFADes,
		dbo.ForeignCurrencySymbol.ENDes As ForeignCurrencySymbolENDes,
		dbo.ForeignCurrencySymbol.Symbol As ForeignCurrencySymbol,
		dbo.Broker.Serial As BrokerSerial,
		dbo.Broker.Code As BrokerCode,
		dbo.Broker.Name As BrokerName,
		dbo.Broker.BrokerPercentTypeRef As BrokerPercentTypeSerial,
		dbo.PartList.PartSerial, 
		dbo.PartList.PartCode, 
		dbo.PartList.PartBarcode, 
		dbo.PartList.PartBarcodeImage, 
		dbo.PartList.PartImage, 
		dbo.PartList.AddedTaxInBuy, 
		dbo.PartList.AddedTaxInSales, 
		dbo.PartList.PartName, 
		dbo.PartList.PartENName, 
		dbo.PartList.OEM, 
		dbo.PartList.StorageSerial, 
		dbo.PartList.StorageCode, 
		dbo.PartList.StorageName, 
		dbo.PartList.GroupSerial, 
		dbo.PartList.GroupCode, 
		dbo.PartList.GroupName, 
		dbo.PartList.UnitSerial, 
		dbo.PartList.UnitCode, 
		dbo.PartList.UnitName, 
		dbo.PartList.UnitWeight, 
		dbo.PartList.PartBuyPriceKindSerial, 
		dbo.PartList.PartBuyPriceKindDescription, 
		dbo.PartList.ShelfNo, 
		dbo.PartList.Remain, 
		dbo.PartList.Comment As PartListComment, 
		dbo.PartList.PartKindSerial, 
		dbo.PartList.PartKindDescription,
		dbo.PartList.ForeignCurrencyForBuy, 
		dbo.PartList.ForeignCurrencyForSale, 
		dbo.FactorDetail.Quantity, 
		dbo.FactorDetail.UnitPrice, 
		dbo.FactorDetail.ApprovedUnitPrice,
		dbo.FactorDetail.TotalPrice,
		dbo.FactorDetail.BuyUnitPriceInPartTable, 
		dbo.FactorDetail.UnitPriceInForeignCurrency, 
		dbo.FactorDetail.ApprovedUnitPriceInForeignCurrency,
		dbo.FactorDetail.TotalPriceInForeignCurrency,
		dbo.FactorDetail.BuyUnitPriceInForeignCurrencyInPartTable, 
		dbo.FactorDetail.RebatePrice,
		dbo.FactorDetail.RebatePercent,
		dbo.FactorDetail.Description As FactorDetailDescription,
		dbo.FactorHeader.RowVersion As FactorHeaderRowVersion,
		dbo.FactorHeader.Tag As FactorHeaderTag,
		dbo.FactorDetail.Tag As FactorDetailTag,
		dbo.FactorHeader.OfficeDetailRef, 
		dbo.FactorHeader.OfficePeriodRef
	From 
		dbo.FactorHeader Inner Join
		dbo.FactorKind On dbo.FactorHeader.FactorKindRef = dbo.FactorKind.Serial Inner Join
		dbo.FactorDetail On dbo.FactorHeader.Serial = dbo.FactorDetail.FactorHeaderRef Inner Join
		dbo.GetterOrPayerFactorList On dbo.FactorHeader.GetterOrPayerFactorRef = GetterOrPayerFactorSerial Inner Join
		dbo.PartList On dbo.FactorDetail.PartRef = dbo.PartList.PartSerial Left Join
		dbo.Users As Users_1 On Users_1.Serial = dbo.FactorHeader.RecorderRef Left Join
		dbo.Users As Users_2 On Users_2.Serial = dbo.FactorHeader.EditorRef Left Join
		dbo.Users As Users_3 On Users_3.Serial = dbo.FactorHeader.VerifierRef Left Join
		dbo.Users As Users_4 On Users_4.Serial = dbo.FactorHeader.UpdatingUserRef Left Join
		dbo.Warehouse On dbo.FactorHeader.WarehouseRef = dbo.Warehouse.serial Left Join
		dbo.FactorCategoryList On dbo.FactorHeader.FactorCategoryRef = dbo.FactorCategoryList.FactorCategorySerial Left Join
		dbo.Broker On dbo.FactorHeader.BrokerRef = dbo.Broker.Serial Left Join
		dbo.ForeignCurrencySymbol On dbo.ForeignCurrencySymbol.Serial = dbo.FactorHeader.ForeignCurrencySymbolRef Left Join
		dbo.FactorExtendedOption On dbo.FactorHeader.Serial = dbo.FactorExtendedOption.FactorHeaderRef  Left Join
		(Select FactorCashSettlementCheckBridge.FactorHeaderRef, Sum(CashGetPayAmount) As CashGetPayAmount From dbo.FactorCashSettlementCheckBridge Group By FactorCashSettlementCheckBridge.FactorHeaderRef)TblCashGePay On TblCashGePay.FactorHeaderRef = FactorHeader.Serial Left Join
		(Select FactorCashSettlementCheckBridge.FactorHeaderRef, Sum(SettlementGetPayAmount) As SettlementGetPayAmount From dbo.FactorCashSettlementCheckBridge Group By FactorCashSettlementCheckBridge.FactorHeaderRef)TblSettlementGePay On TblSettlementGePay.FactorHeaderRef = FactorHeader.Serial Left Join
		(Select FactorCashSettlementCheckBridge.FactorHeaderRef, Sum(CheckGetPayAmount) As CheckGetPayAmount From dbo.FactorCashSettlementCheckBridge Group By FactorCashSettlementCheckBridge.FactorHeaderRef)TblCheckGetPay On TblCheckGetPay.FactorHeaderRef = FactorHeader.Serial
	Order By 
		FactorHeaderSerial, 
		FactorDetailSerial
) As K
Go
/*=============================================================================================================*/

-- دستور زیر دقیقا پروسیجر به روز رسانی مبالغ فاکتورهاست که نام آن نیز در زیر نوشته شده است
-- Factor_Header_TotalPrice_Update 

	Update 
		FactorHeader
	Set 
		TotalPaid = IsNull(K.Sum_TotalPrice,0) + IsNull(VAT,0) + IsNull(Toll,0) + IsNull(Cost2th,0) - IsNull(Rebate,0),
		TotalPaidInForeignCurrency = IsNull(K.Sum_TotalPriceInForeignCurrency,0),

		Sum_TotalPrice = IsNull(K.Sum_TotalPrice,0),
		Sum_TotalPriceInForeignCurrency = IsNull(K.Sum_TotalPriceInForeignCurrency,0),

		Sum_TotalPriceForTax = IsNull(K.Sum_TotalPriceForTax,0),
		Sum_TotalPriceForTaxInForeignCurrency = IsNull(K.Sum_TotalPriceForTaxInForeignCurrency,0)
	From 
		FactorHeader Inner Join 
		(Select 
			FactorHeaderSerial,
				
			Sum(TotalPrice) As Sum_TotalPrice, 
			Sum(TotalPriceInForeignCurrency) As Sum_TotalPriceInForeignCurrency,
				
			Sum(TotalPrice * AddedTaxFactor) As Sum_TotalPriceForTax, 
			Sum(TotalPriceInForeignCurrency * AddedTaxFactor) As Sum_TotalPriceForTaxInForeignCurrency
		From 
			FactorHeaderAndDetailList
		--Where 
		--	(@FactorHeaderSerial = 0 Or FactorHeaderSerial = @FactorHeaderSerial) And
		--	(@OfficePeriodSerial = 0 Or OfficePeriodRef = @OfficePeriodSerial)
		Group By 
			FactorHeaderSerial) K On K.FactorHeaderSerial = FactorHeader.Serial 
