-- این آپدیت لیست فاکتورهایی را که تایید شده اند ولی برای آنها سند حسابداری صادر نشده است را پیدا می کند
-- این فاکتورها حتما باید قبل از آپدیت برنامه از تایید خارج شده و پس از آپدیت مجددا تایید گردد
Use ATNDBTest
Go


/*=============================================================================================================*/
Set Ansi_Nulls On
Go
Set Quoted_Identifier On
Go
If Exists(Select * From sys.Views Where name = 'FactorCategoryList') Drop View dbo.FactorCategoryList
Go
Create View dbo.FactorCategoryList
With Encryption
As
	Select 
		FactorCategory.Serial As FactorCategorySerial,
		FactorCategory.Code As FactorCategoryCode,
		FactorCategory.FADes As FactorCategoryFADescription,
		FactorCategory.ENDes As FactorCategoryENDescription,
		Kind As FactorCategoryKind,
		Recorder.Serial As RecorderSerial, 
		Recorder.Name As RecorderName, 
		FactorCategory.RecordDate,
		Editor.Serial As EditorSerial, 
		Editor.Name As EditorName, 
		FactorCategory.EditDate,
		FactorCategory.OfficeDetailRef
	From
		dbo.FactorCategory Inner Join
		dbo.Users AS Recorder On Recorder.Serial = FactorCategory.RecorderRef Left Join
		dbo.Users AS Editor On Editor.Serial = FactorCategory.EditorRef
Go




Set Ansi_Nulls On
Go
Set Quoted_Identifier On
Go
If Exists(Select * From sys.Views Where name = 'GetterOrPayerFactorCategoryList') Drop View dbo.GetterOrPayerFactorCategoryList
Go
Create View dbo.GetterOrPayerFactorCategoryList
With Encryption
As
	Select 
		GetterOrPayerFactorCategory.Serial As GetterOrPayerFactorCategorySerial,
		GetterOrPayerFactorCategory.Code As GetterOrPayerFactorCategoryCode,
		GetterOrPayerFactorCategory.FADescription As GetterOrPayerFactorCategoryFADescription,
		GetterOrPayerFactorCategory.ENDescription As GetterOrPayerFactorCategoryENDescription,
		GetterOrPayerFactorCategory.Comment As GetterOrPayerFactorCategoryComment,
		Recorder.Serial As RecorderSerial, 
		Recorder.Name As RecorderName, 
		GetterOrPayerFactorCategory.RecordDate,
		Editor.Serial As EditorSerial, 
		Editor.Name As EditorName, 
		GetterOrPayerFactorCategory.EditDate,
		GetterOrPayerFactorCategory.OfficeDetailRef
	From
		dbo.GetterOrPayerFactorCategory Inner Join
		dbo.Users AS Recorder On Recorder.Serial = GetterOrPayerFactorCategory.RecorderRef Left Join
		dbo.Users AS Editor On Editor.Serial = GetterOrPayerFactorCategory.EditorRef
Go


If Exists(Select * From sys.Views Where name = 'GetterOrPayerFactorList') Drop View dbo.GetterOrPayerFactorList
Go
Create View dbo.GetterOrPayerFactorList
With Encryption
As
Select        
	dbo.GetterOrPayerFactor.Serial As GetterOrPayerFactorSerial, 
	dbo.GetterOrPayerFactor.Code As GetterOrPayerFactorCode, 
	dbo.GetterOrPayerFactor.FAName As GetterOrPayerFactorFAName, 
	dbo.GetterOrPayerFactor.ENName As GetterOrPayerFactorENName, 
	dbo.GetterOrPayerFactorKind.Serial As KindSerial, 
	dbo.GetterOrPayerFactorKind.Description As KindDescription, 
	dbo.GetterOrPayerFactor.Manager, 
	dbo.GetterOrPayerFactor.Active, 
	dbo.Degree.Serial As DegreeSerial, 
	dbo.Degree.FADescription As FADegreeDescription, 
	dbo.Degree.ENDescription As ENDegreeDescription, 
	dbo.GetterOrPayerFactorCategoryList.GetterOrPayerFactorCategorySerial,
	dbo.GetterOrPayerFactorCategoryList.GetterOrPayerFactorCategoryCode,
	dbo.GetterOrPayerFactorCategoryList.GetterOrPayerFactorCategoryENDescription,
	dbo.GetterOrPayerFactorCategoryList.GetterOrPayerFactorCategoryFADescription,
	dbo.GetterOrPayerFactor.Address, 
	dbo.GetterOrPayerFactor.PostCode, 
	dbo.GetterOrPayerFactor.Email,
	dbo.GetterOrPayerFactor.Website,
	dbo.GetterOrPayerFactor.RegNo,
	dbo.GetterOrPayerFactor.SSN,
	dbo.GetterOrPayerFactor.EconomicCode,
	dbo.GetterOrPayerFactor.Tel1, 
	dbo.GetterOrPayerFactor.Tel2, 
	dbo.GetterOrPayerFactor.Fax, 
	dbo.GetterOrPayerFactor.Mobile, 
	dbo.GetterOrPayerFactor.BankName,
	dbo.GetterOrPayerFactor.OffshootCode,
	dbo.GetterOrPayerFactor.OffshootName,
	dbo.GetterOrPayerFactor.BankAccountNo,
	dbo.GetterOrPayerFactor.IBAN,
	dbo.GetterOrPayerFactor.BankAddress,
	dbo.GetterOrPayerFactor.AccountMaximum,
	dbo.GetterOrPayerFactor.SaleProfitPercent,
	dbo.GetterOrPayerFactor.SaleRebatePercent,
	dbo.AccEntityKind.Serial As EntityKindSerial,
	dbo.AccEntityKind.Description As EntityKindDescription,
	dbo.AccEntityL1CodeBook.Serial As EntityL1Serial,
	dbo.AccEntityL1CodeBook.Code As EntityL1Code,
	dbo.AccEntityL1CodeBook.FADetail As EntityL1FADetail,
	dbo.AccEntityL1CodeBook.ENDetail As EntityL1ENDetail,
	dbo.AccEntityL2CodeBook.Serial As EntityL2Serial,
	dbo.AccEntityL2CodeBook.Code As EntityL2Code,
	dbo.AccEntityL2CodeBook.FADetail As EntityL2FADetail,
	dbo.AccEntityL2CodeBook.ENDetail As EntityL2ENDetail,
	Recorder.Serial As RecorderSerial, 
	Recorder.Name As RecorderName, 
	dbo.GetterOrPayerFactor.RecordDate, 
	Editor.Serial As EditorSerial, 
	Editor.Name As EditorName, 
	dbo.GetterOrPayerFactor.EditDate, 
	dbo.GetterOrPayerFactor.Description, 
	dbo.GetterOrPayerFactor.OfficeDetailRef
From
	dbo.GetterOrPayerFactor Inner Join
	dbo.GetterOrPayerFactorKind On dbo.GetterOrPayerFactor.KindRef = dbo.GetterOrPayerFactorKind.Serial Left Join
	dbo.Degree On dbo.Degree.Serial = dbo.GetterOrPayerFactor.DegreeRef Left Join
	dbo.GetterOrPayerFactorCategoryList On dbo.GetterOrPayerFactorCategoryList.GetterOrPayerFactorCategorySerial = dbo.GetterOrPayerFactor.GetterOrPayerFactorCategoryRef Left Join
	dbo.Users AS Recorder On Recorder.Serial = dbo.GetterOrPayerFactor.RecorderRef  Left Join
	dbo.Users AS Editor On Editor.Serial = dbo.GetterOrPayerFactor.EditorRef Left Join
	dbo.AccEntityL1CodeBook On dbo.AccEntityL1CodeBook.Serial = dbo.GetterOrPayerFactor.AccEntityL1CodeBookRef Left Join
	dbo.AccEntityKind On dbo.AccEntityL1CodeBook.AccEntityKindRef = dbo.AccEntityKind.Serial Left Join
	dbo.AccEntityL2CodeBook On dbo.AccEntityL2CodeBook.Serial =  dbo.GetterOrPayerFactor.AccEntityL2CodeBookRef
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	
	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, 
	dbo.FactorHeader.Cost1th, 
	dbo.FactorHeader.Cost2th, 
	dbo.FactorHeader.Rebate, 
	dbo.FactorHeader.RebateRound, 
	dbo.FactorHeader.ForeignCurrencyFactor,
	dbo.FactorHeader.TotalPaid, 
	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.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.PartName, 
	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.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.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
Go




If Exists(Select 
			* 
		  From 
			FactorHeaderAndDetailList 
		  Where 
			VerificationDate is not null And
			DocumentHeaderSerialOnVerification is null And
			FactorKindSerial In (Select FactorKindRef From SystemAccCodeDefaultBase As K Where K.OnVerification = 1))
Begin
	Declare @ErrorString varchar(Max)
	Select @ErrorString = dbo.Concatenate(IsNull(ErrorFactors,'')) From
		(Select Distinct
			'سند ' + FactorKindFA_Description + ' شماره ' + Cast(FactorHeaderCode As varchar(max)) + ' در دفتر مالی ' + Char(39) + OfficeDetail.OfficeName + Char(39) + ' دوره مالی '  + Char(39) + OfficePeriod.PeriodName + Char(39) + char(13) + char(10) As ErrorFactors
		From 
			FactorHeaderAndDetailList Inner Join 
			OfficeDetail On FactorHeaderAndDetailList.OfficeDetailRef = OfficeDetail.Serial Inner Join
			OfficePeriod On FactorHeaderAndDetailList.OfficePeriodRef = OfficePeriod.Serial
		Where 
			VerificationDate is not null And
			DocumentHeaderSerialOnVerification is null And
			FactorKindSerial In (Select FactorKindRef From SystemAccCodeDefaultBase As K Where K.OnVerification = 1)) As K
	Set @ErrorString = 'خطا: اسنادی تایید شده داریم که برای آنها سند حسابداری صادر نشده است. لطفا ابتدا فاکتورها را برگشت بزنید' + char(13) + char(10) + 'لیست فاکتورها عبارتند از:' + char(13) + char(10) + @ErrorString
	--print @ErrorString
	RaisError(@ErrorString,16,1)
End
