/****** Object:  StoredProcedure [dbo].[AccountIsUsed]    Script Date: 11/07/2022 01:48:24 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Azimi
-- Create date: 2022/6/5
-- Description:	Check if ACCOUNT IS USED
-- =============================================
ALTER PROCEDURE [dbo].[AccountIsUsed]
	@TotalID bigint = 0, 
	@ControlID bigint = 0, 
	@DetailID1 bigint = 0, 
	@DetailID2 bigint = 0,
	@ForRelation bit = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @NUM as int
	SET @NUM = 0

	IF @DetailID2 > 0
	BEGIN
		Select @Num = Sum(Num) from (
			select Count(*) as NUM from [dbo].[AccChequeIn] WITH(NOLOCK) where DebitDetailID2=@DetailID2 or CreditDetailID2=@DetailID2 or OwnerDetailID2 =@DetailID2 
			UNION
			select Count(*) as NUM from [dbo].[AccChequeInCredit] WITH(NOLOCK) where DetailID2=@DetailID2
			UNION
			select Count(*) as NUM from [dbo].[AccChequeInfo] WITH(NOLOCK) where DetailID2=@DetailID2
			UNION
			select Count(*) as NUM from [dbo].[AccChequeInOwner] WITH(NOLOCK) where DetailID2=@DetailID2
			UNION
			select Count(*) as NUM from [dbo].[AccChequeOut] WITH(NOLOCK) where DebitDetailID2=@DetailID2 or CreditDetailID2=@DetailID2
			UNION
			select Count(*) as NUM from [dbo].[AccChequeInOwner] WITH(NOLOCK) where DetailID2=@DetailID2
			UNION
			select Count(*) as NUM from [dbo].[AccChequeOutItemInfo] WITH(NOLOCK) where DetailID2=@DetailID2
			UNION
			select Count(*) as NUM from [dbo].[AccDocItem] WITH(NOLOCK) where DetailID2=@DetailID2
			UNION
			select Count(*) as NUM from [dbo].[AccFactor] WITH(NOLOCK) where PersonDetailID2=@DetailID2 or DiscontDetailID2=@DetailID2 or AvarezDetailID2=@DetailID2 or HamlDetailID2=@DetailID2 or VizitorDetailID2=@DetailID2 
			UNION
			select Count(*) as NUM from [dbo].[AccFactorDetail] WITH(NOLOCK) where OrderDetailID2=@DetailID2 or OtherDetailID2=@DetailID2 
			UNION
			select Count(*) as NUM from [dbo].[AccSellFactor] WITH(NOLOCK) where OrderDetailID2=@DetailID2 or BuyerDetailID2=@DetailID2 or SellerDetailID2=@DetailID2 
			UNION
			select Count(*) as NUM from [dbo].[AccSellFactorShip] WITH(NOLOCK) where KalaDetailID2=@DetailID2 
			UNION
			select Count(*) as NUM from [dbo].[AccSellFactorTrucks] WITH(NOLOCK) where ChaperDetailID2=@DetailID2 OR ChaperDetailID2 =@DetailID2 OR HamlDetailID2=@DetailID2 
			UNION
			select Count(*) as NUM from [dbo].[AccWarehouse] WITH(NOLOCK) where PersonDetailID2=@DetailID2 
			UNION
			select Count(*) as NUM from [dbo].[AccWarehouseDetails] WITH(NOLOCK) where OrderDetailID2=@DetailID2 OR OtherDetailID2 =@DetailID2 
			UNION
			select Count(*) as NUM from [dbo].[AccWarehouseInfo] WITH(NOLOCK) where DetailID2=@DetailID2 
			UNION
			select Count(*) as NUM from [dbo].[AccWareProductTree] WITH(NOLOCK) where DetailID2=@DetailID2
			UNION
			select Count(*) as NUM from [dbo].[Chicken] WITH(NOLOCK) where BuyerDetailID2=@DetailID2 OR OrderDetailID2=@DetailID2 OR SellerDetailID2=@DetailID2 OR TruckDetailID2 =@DetailID2   
			UNION
			select Count(*) as NUM from [dbo].[POSDetail] WITH(NOLOCK) where OrderDetailID2=@DetailID2 
			UNION
			select Count(*) as NUM from [dbo].[POSMain] WITH(NOLOCK) where PersonDetailID2=@DetailID2 
			UNION
			select Count(*) as NUM from [dbo].[WareCount] WITH(NOLOCK) where DetailID2=@DetailID2 
		) t
	END
	ELSE
		IF @DetailID1 > 0
		BEGIN
			Select @Num = @Num + Sum(Num) from (
				select Count(*) as NUM from [dbo].[AccChequeIn] WITH(NOLOCK) where DebitDetailID1=@DetailID1 or CreditDetailID1=@DetailID1 or OwnerDetailID1 =@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInCredit] WITH(NOLOCK) where DetailID1=@DetailID1
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInfo] WITH(NOLOCK) where DetailID1=@DetailID1
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInOwner] WITH(NOLOCK) where DetailID1=@DetailID1
				UNION
				select Count(*) as NUM from [dbo].[AccChequeOut] WITH(NOLOCK) where DebitDetailID1=@DetailID1 or CreditDetailID1=@DetailID1
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInOwner] WITH(NOLOCK) where DetailID1=@DetailID1
				UNION
				select Count(*) as NUM from [dbo].[AccChequeOutItemInfo] WITH(NOLOCK) where DetailID1=@DetailID1
				UNION
				select Count(*) as NUM from [dbo].[AccDocItem] WITH(NOLOCK) where DetailID1=@DetailID1
				UNION
				select Count(*) as NUM from [dbo].[AccFactor] WITH(NOLOCK) where PersonDetailID1=@DetailID1 or DiscontDetailID1=@DetailID1 or AvarezDetailID1=@DetailID1 or HamlDetailID1=@DetailID1 or VizitorDetailID1=@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[AccFactorDetail] WITH(NOLOCK) where OrderDetailID1=@DetailID1 or OtherDetailID1=@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[AccSellFactor] WITH(NOLOCK) where OrderDetailID1=@DetailID1 or BuyerDetailID1=@DetailID1 or SellerDetailID1=@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[AccSellFactorShip] WITH(NOLOCK) where KalaDetailID1=@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[AccSellFactorTrucks] WITH(NOLOCK) where ChaperDetailID1=@DetailID1 OR ChaperDetailID1 =@DetailID1 OR HamlDetailID1=@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[AccWarehouse] WITH(NOLOCK) where PersonDetailID1=@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[AccWarehouseDetails] WITH(NOLOCK) where OrderDetailID1=@DetailID1 OR OtherDetailID1 =@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[AccWarehouseInfo] WITH(NOLOCK) where DetailID1=@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[AccWareProductTree] WITH(NOLOCK) where DetailID1=@DetailID1
				UNION
				select Count(*) as NUM from [dbo].[Chicken] WITH(NOLOCK) where BuyerDetailID1=@DetailID1 OR OrderDetailID1=@DetailID1 OR SellerDetailID1=@DetailID1 OR TruckDetailID1 =@DetailID1   
				UNION
				select Count(*) as NUM from [dbo].[POSDetail] WITH(NOLOCK) where OrderDetailID1=@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[POSMain] WITH(NOLOCK) where PersonDetailID1=@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[WareCount] WITH(NOLOCK) where DetailID1=@DetailID1 
				UNION
				select Count(*) as NUM from [dbo].[AccDetailDetail]  WITH(NOLOCK) where @ForRelation=0 and DetailID1=@DetailID1 
			) t
		END
	ELSE 
		IF @ControlID > 0
		BEGIN
			Select @Num = @Num + Sum(Num) from (
				select Count(*) as NUM from [dbo].[AccChequeIn] WITH(NOLOCK) where DebitControlID=@ControlID or CreditControlID=@ControlID or OwnerControlID =@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInCredit] WITH(NOLOCK) where ControlID=@ControlID
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInfo] WITH(NOLOCK) where ControlID=@ControlID
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInOwner] WITH(NOLOCK) where ControlID=@ControlID
				UNION
				select Count(*) as NUM from [dbo].[AccChequeOut] WITH(NOLOCK) where DebitControlID=@ControlID or CreditControlID=@ControlID
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInOwner] WITH(NOLOCK) where ControlID=@ControlID
				UNION
				select Count(*) as NUM from [dbo].[AccChequeOutItemInfo] WITH(NOLOCK) where ControlID=@ControlID
				UNION
				select Count(*) as NUM from [dbo].[AccDocItem] WITH(NOLOCK) where ControlID=@ControlID
				UNION
				select Count(*) as NUM from [dbo].[AccFactor] WITH(NOLOCK) where PersonControlID=@ControlID or DiscontControlID=@ControlID or AvarezControlID=@ControlID or HamlControlID=@ControlID or VizitorControlID=@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[AccFactorDetail] WITH(NOLOCK) where OrderControlID=@ControlID or OtherControlID=@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[AccSellFactor] WITH(NOLOCK) where OrderControlID=@ControlID or BuyerControlID=@ControlID or SellerControlID=@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[AccSellFactorShip] WITH(NOLOCK) where KalaControlID=@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[AccSellFactorTrucks] WITH(NOLOCK) where ChaperControlID=@ControlID OR ChaperControlID =@ControlID OR HamlControlID=@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[AccWarehouse] WITH(NOLOCK) where PersonControlID=@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[AccWarehouseDetails] WITH(NOLOCK) where OrderControlID=@ControlID OR OtherControlID =@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[AccWarehouseInfo] WITH(NOLOCK) where ControlID=@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[AccWareProductTree] WITH(NOLOCK) where ControlID=@ControlID
				UNION
				select Count(*) as NUM from [dbo].[Chicken] WITH(NOLOCK) where BuyerControlID=@ControlID OR OrderControlID=@ControlID OR SellerControlID=@ControlID OR TruckControlID =@ControlID   
				UNION
				select Count(*) as NUM from [dbo].[POSDetail] WITH(NOLOCK) where OrderControlID=@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[POSMain] WITH(NOLOCK) where PersonControlID=@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[WareCount] WITH(NOLOCK) where ControlID=@ControlID 
				UNION
				select Count(*) as NUM from [dbo].[AccControlDetail] WITH(NOLOCK) where @ForRelation=0 and DetailID=@ControlID 
			) t
		END
	ELSE
		IF @TotalID > 0
		BEGIN
			Select @Num = @Num + Sum(Num) from (
				select Count(*) as NUM from [dbo].[AccChequeIn] WITH(NOLOCK) where DebitTotalID=@TotalID or CreditTotalID=@TotalID or OwnerTotalID =@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInCredit] WITH(NOLOCK) where TotalID=@TotalID
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInfo] WITH(NOLOCK) where TotalID=@TotalID
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInOwner] WITH(NOLOCK) where TotalID=@TotalID
				UNION
				select Count(*) as NUM from [dbo].[AccChequeOut] WITH(NOLOCK) where DebitTotalID=@TotalID or CreditTotalID=@TotalID
				UNION
				select Count(*) as NUM from [dbo].[AccChequeInOwner] WITH(NOLOCK) where TotalID=@TotalID
				UNION
				select Count(*) as NUM from [dbo].[AccChequeOutItemInfo] WITH(NOLOCK) where TotalID=@TotalID
				UNION
				select Count(*) as NUM from [dbo].[AccDocItem] WITH(NOLOCK) where TotalID=@TotalID
				UNION
				select Count(*) as NUM from [dbo].[AccFactor] WITH(NOLOCK) where PersonTotalID=@TotalID or DiscontTotalID=@TotalID or AvarezTotalID=@TotalID or HamlTotalID=@TotalID or VizitorTotalID=@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[AccFactorDetail] WITH(NOLOCK) where OrderTotalID=@TotalID or OtherTotalID=@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[AccSellFactor] WITH(NOLOCK) where OrderTotalID=@TotalID or BuyerTotalID=@TotalID or SellerTotalID=@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[AccSellFactorShip] WITH(NOLOCK) where KalaTotalID=@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[AccSellFactorTrucks] WITH(NOLOCK) where ChaperTotalID=@TotalID OR ChaperTotalID =@TotalID OR HamlTotalID=@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[AccWarehouse] WITH(NOLOCK) where PersonTotalID=@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[AccWarehouseDetails] WITH(NOLOCK) where OrderTotalID=@TotalID OR OtherTotalID =@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[AccWarehouseInfo] WITH(NOLOCK) where TotalID=@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[AccWareProductTree] WITH(NOLOCK) where TotalID=@TotalID
				UNION
				select Count(*) as NUM from [dbo].[Chicken] WITH(NOLOCK) where BuyerTotalID=@TotalID OR OrderTotalID=@TotalID OR SellerTotalID=@TotalID OR TruckTotalID =@TotalID   
				UNION
				select Count(*) as NUM from [dbo].[POSDetail] WITH(NOLOCK) where OrderTotalID=@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[POSMain] WITH(NOLOCK) where PersonTotalID=@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[WareCount] WITH(NOLOCK) where TotalID=@TotalID 
				UNION
				select Count(*) as NUM from [dbo].[AccTotalControl] WITH(NOLOCK) where @ForRelation=0 and TotalID=@ControlID 
			) t
		END


	select @Num
END
