USE [Prince]
GO
/****** Object:  StoredProcedure [Common].[TroubleShooting_Fix]    Script Date: 12/14/2025 15:19:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Ghasem Babaie
-- Create date: 2025/06/26 | 1404/04/05
-- Description: تصحیح ایراد داده هایی که در گزارش نقض اطلاعات به کاربر نمایش داده ایم
--				هر اطلاعاتی که امکان پذیر باشد را تصحیح می کنیم
-- =============================================
ALTER PROCEDURE [Common].[TroubleShooting_Fix]
(
    @TroubleShootingDataType	[Common].[_SMALL_]
)
AS
BEGIN
       SET NOCOUNT ON;
       ------------------------------------------------		
		
		-- استخراج سال مالی باز
		DECLARE @FNY_Name	[Common].[_SMALL_]
		SET @FNY_Name = (SELECT [FNY_Name] FROM [Common].[FinancialYear] WHERE ISNULL([FNY_CloseDate_Int], 0) = 0)		
		
		-------------------------------------------
		-- بررسی اینکه سیستم مرکز و شعب است یا خیر؟
		-- چون برخی موارد فقط باید بر روی سیستم های مرکز و شعب بررسی شوند
		-------------------------------------------
		/*
			0 -> Normal
			1 -> Branch (VIP)
			2 -> Center (VIP)
		*/
		-------------------------------------------
		DECLARE	@CenterBranchState	[Common].[_INT_] = 0
		SELECT @CenterBranchState = SST_Value FROM [Common].[SystemSetting]	WHERE SST_Title = 'BranchSystemState' AND SST_UserID = 0
		
		-- این متغییرها فقط بر روی سیستم های مرکز و شعب ساخته می شوند
		IF @CenterBranchState in (1,2)
		BEGIN	
			DECLARE @VMA_TableID		[Common].[_INT_]
			DECLARE @VMA_TableName		[Common].[_SMALL_]
			DECLARE @Table_AbbName		[Common].[_TINY_]
			DECLARE @Table_PersianName	[Common].[_TINY_]				
			DECLARE @SQLString			NVARCHAR(MAX) = ''
		END		
		
		----------------------------------------------------------
		----------------------------------------------------------
		----------------------------------------------------------			
		
		IF ISNULL(@TroubleShootingDataType, '') = ''
			SET @TroubleShootingDataType = 'CPN,CMD,LostData'

		-- اشخاص
		IF ',' + @TroubleShootingDataType + ',' LIKE '%,CPN,%'
		BEGIN
			-- فقط در سیستم های معمولی (غیر از مرکز و شعب) اجرا شود
			IF @CenterBranchState = 0
			BEGIN
				-- ایجاد جدول موقت
				DECLARE @Persons TABLE (
					_CPN_ID INT
				)
				
				DECLARE @_CPN_ID	[Common].[_INT_]
			
				-- _________________________________________________
				-- _________________________________________________
				-- شماره موبایل هایی که ایراد دارند، استخراج می شوند
				-- و در جدول ریخته می شوند
				-- _________________________________________________
				-- _________________________________________________
				INSERT INTO @Persons
				EXEC [Common].[SYS_UpdateDefaultMobileNumber] 1, NULL 
			
				-- شماره موبایل هایی که در مرحله قبلی استخراج شده اند
				-- در این مرحله پیشمایش می شوند و ایراد اطلاعات تماس آنها برطرف می شود
				DECLARE _BadPersonMobiles CURSOR for
					   SELECT _CPN_ID
					   FROM @Persons 
				OPEN _BadPersonMobiles
				FETCH NEXT FROM _BadPersonMobiles INTO @_CPN_ID
				WHILE @@FETCH_STATUS = 0
				BEGIN 
				   -- تصحیح ایراد شماره موبایل شخص
				   EXEC [Common].[SYS_UpdateDefaultMobileNumber] 2, @_CPN_ID
						
				   FETCH NEXT FROM _BadPersonMobiles INTO @_CPN_ID
				END
				CLOSE _BadPersonMobiles
				DEALLOCATE _BadPersonMobiles
				-- _________________________________________________
				-- _________________________________________________					
			END				
		END
		
		----------------------------------------------------------
		----------------------------------------------------------
		----------------------------------------------------------		
		
		-- کالاها
		IF ',' + @TroubleShootingDataType + ',' LIKE '%,CMD,%'
		BEGIN
			-- _______________________________
			-- _______________________________
			-- تصحیح مشکل چند خطی بودن نام کالا	
			-- _______________________________
			-- _______________________________
			UPDATE [Common].[Commodity]
			SET [CMD_NAME] = LTRIM(RTRIM(SUBSTRING([CMD_NAME], 1, CHARINDEX(CHAR(10), [CMD_NAME]) - 2)))  
			WHERE CHARINDEX(CHAR(10), [CMD_NAME]) > 0 

			UPDATE [Common].[Commodity]
			SET [CMD_AbbName] = LTRIM(RTRIM(SUBSTRING([CMD_AbbName], 1, CHARINDEX(CHAR(10), [CMD_AbbName]) - 2)))  
			WHERE CHARINDEX(CHAR(10), [CMD_AbbName]) > 0  
			
			UPDATE [Common].[Commodity]
			SET [CMD_Description] = REPLACE(REPLACE([CMD_Description], CHAR(13), ' '), CHAR(10), ' ')
			WHERE CHARINDEX(CHAR(10), [CMD_Description]) > 0 
			
			UPDATE [Common].[Commodity]
			SET [CMD_FoodContents] = REPLACE(REPLACE([CMD_FoodContents], CHAR(13), ' '), CHAR(10), ' ')
			WHERE CHARINDEX(CHAR(10), [CMD_FoodContents]) > 0 
			 
			-- _______________________________
			-- _______________________________
		
		
			-- فقط در سیستم های معمولی (غیر از مرکز و شعب) اجرا شود
			IF @CenterBranchState = 0
			BEGIN			
				DECLARE @BRC_ID				INT,
						@BRC_CMD_ID			INT,
						@BRC_TYPE			INT,
						@CMD_Code			NVARCHAR(2000),
						@CMD_ID				INT,
						@CUM_ID				INT,       
						@CNU_ID				INT,
						@CNU_ID_Temp		INT,
						@CNU_CUM_ID_Temp	INT,
						@CNU_CNU_ID			INT,
						@CNU_Coefficient	FLOAT										

				-- __________________________________________
				-- __________________________________________
				-- تصحیح مشکل کالاهایی که بارکد غیر عددی دارند	
				-- __________________________________________
				-- __________________________________________
				DECLARE _Barcodes CURSOR for
					SELECT [BRC_ID], [BRC_CMD_ID], [BRC_TYPE] 
					FROM [Common].[CommodityBarcode] 
					WHERE [BRC_CODE] LIKE '%[^0-9]%' AND
							[BRC_TYPE] = 0
				OPEN _Barcodes
				FETCH NEXT FROM _Barcodes INTO @BRC_ID, @BRC_CMD_ID, @BRC_TYPE
				WHILE @@FETCH_STATUS = 0
				BEGIN     
					IF @BRC_TYPE <> 0
					BEGIN
						DELETE FROM [Common].[CommodityBarcode]  
						WHERE BRC_ID = @BRC_ID
					END
					ELSE
					BEGIN
						EXEC [Common].[CMD_GetCode] @CMD_Code OUT	

						UPDATE [Common].[CommodityBarcode]
						SET [BRC_CODE] = @CMD_Code
						WHERE [BRC_ID] = @BRC_ID

						UPDATE [Common].[Commodity]
						SET [CMD_CODE] = @CMD_Code
						WHERE [CMD_ID] = @BRC_CMD_ID
					END 

					FETCH NEXT FROM _Barcodes INTO @BRC_ID, @BRC_CMD_ID, @BRC_TYPE
				END
				CLOSE _Barcodes
				DEALLOCATE _Barcodes
				-- __________________________________________
				-- __________________________________________	
				
				
				-- _______________________________________________
				-- _______________________________________________
				-- افزودن سطح قیمت برای کالاهایی که سطح قیمت ندارند	
				-- _______________________________________________
				-- _______________________________________________	
				DECLARE @PriceLevel		INT 
				SET @PriceLevel = (SELECT TOP 1 [LKP_ID] 
									FROM [Common].[Lookup] 
									WHERE [LKP_Type] = 16 AND 
											[LKP_Prince] = 1
									ORDER BY [LKP_ID] ASC)

				DECLARE _PriceLevels CURSOR for
					SELECT [CMD_ID], [CMD_Code] 
					FROM [Common].[Commodity] 
					WHERE [CMD_ID] NOT IN (SELECT [PRL_CMD_ID] 
											FROM [Common].[PriceLevel])
				OPEN _PriceLevels
				FETCH NEXT FROM _PriceLevels INTO @CMD_ID, @CMD_CODE
				WHILE @@FETCH_STATUS = 0
				BEGIN   
					SET @BRC_ID = 0
					SELECT @BRC_ID = [BRC_ID] 
					FROM [Common].[CommodityBarcode]
					WHERE [BRC_CMD_ID] = @CMD_ID AND 
							[BRC_Type] = 0
										
					IF @BRC_ID = 0 
					BEGIN
						EXEC [Common].[BRC_INSERT] @CMD_ID, @CMD_CODE, 0, NULL, NULL, 0, @BRC_ID OUTPUT 
					END
					
					---

					SET @CUM_ID = 0
					SELECT @CUM_ID = [CUM_ID] 
					FROM [Common].[CountingUnitMultiple] 
					WHERE [CUM_CMD_ID] = @CMD_ID AND 
							[CUM_IsDelete] = 0 AND 
							[CUM_IsDefault] = 1
					
					IF @CUM_ID = 0 
					BEGIN
						EXEC [Common].[CUM_Insert] N'سطح پيش فرض', 1, @CMD_ID, @CUM_ID OUTPUT 
					END
					
					---

					SET @CNU_ID = 0
					SELECT @CNU_ID = [CNU_ID] 
					FROM [Common].[CountingUnit] 
					WHERE [CNU_CMD_ID] = @CMD_ID AND 
							[CNU_CUM_ID] = @CUM_ID AND 
							[CNU_Coefficient] IS NULL
					
					IF @CNU_ID = 0 
					BEGIN
						EXEC [Common].CNU_Insert @CMD_ID, NULL, NULL, 402, NULL, 1, 1, @CUM_ID, @CNU_ID OUTPUT, NULL
					END	
					
					---

					DECLARE CNU CURSOR for
						SELECT [CNU_ID], [CNU_CUM_ID] 
						FROM [Common].[CountingUnit] 
						WHERE [CNU_CMD_ID] = @CMD_ID
					OPEN CNU
					FETCH NEXT FROM CNU INTO @CNU_ID_Temp, @CNU_CUM_ID_Temp
					WHILE @@FETCH_STATUS = 0
					BEGIN  	
						EXEC [Common].[PRL_Insert] @CMD_ID, @PriceLevel, 0, 0, 0, 0, 1, @CNU_ID_Temp, @CNU_CUM_ID_Temp, 0, NULL	 

						FETCH NEXT FROM CNU INTO @CNU_ID_Temp, @CNU_CUM_ID_Temp
					END
					CLOSE CNU
					DEALLOCATE CNU	   		 

					FETCH NEXT FROM _PriceLevels INTO @CMD_ID, @CMD_CODE
				END
				CLOSE _PriceLevels
				DEALLOCATE _PriceLevels
				-- _______________________________________________
				-- _______________________________________________								
				

				-- ________________________________________________
				-- ________________________________________________
				-- افزودن سطح قیمت برای سطوح واحد دوم به بعد
				-- برای کالاهایی که این سطوح واحدشان، سطح قیمت ندارد	
				-- ________________________________________________
				-- ________________________________________________
				DECLARE _CountingUnits CURSOR for
					SELECT [CNU_ID], [CNU_CMD_ID], [CNU_CNU_ID], [CNU_Coefficient], [CNU_CUM_ID]
					FROM [Common].[CountingUnit]
					WHERE [CNU_ID] NOT IN (SELECT [PRL_CNU_ID] 
											FROM [Common].[PriceLevel]) AND 
							ISNULL([CNU_CNU_ID], 0) > 0
				OPEN _CountingUnits
				FETCH NEXT FROM _CountingUnits INTO @CNU_ID, @CMD_ID, @CNU_CNU_ID, @CNU_Coefficient, @CUM_ID
				WHILE @@FETCH_STATUS = 0
				BEGIN
					INSERT INTO [Common].[PriceLevel]
					SELECT [PRL_CMD_ID], [PRL_LKP_Title], CAST(([PRL_Profit] * ISNULL(@CNU_Coefficient,0)) AS DECIMAL(38,0)),
							CAST(([PRL_BuyPrice] * ISNULL(@CNU_Coefficient,0)) AS DECIMAL(38,0)),
							CAST(([PRL_SalePrice] * ISNULL(@CNU_Coefficient,0)) AS DECIMAL(38,0)),
							[PRL_IsPercent], [PRL_IsDefault], @CNU_ID, @CUM_ID, [PRL_CalculatePriceType], [PRL_LastEditedTime]
					FROM [Common].[PriceLevel]
					WHERE [PRL_CNU_ID] = @CNU_CNU_ID

					FETCH NEXT FROM _CountingUnits INTO @CNU_ID, @CMD_ID, @CNU_CNU_ID, @CNU_Coefficient, @CUM_ID
				END
				CLOSE _CountingUnits
				DEALLOCATE _CountingUnits	
				-- ________________________________________________
				-- ________________________________________________							
			END	
		END	
		
		----------------------------------------------------------
		----------------------------------------------------------
		----------------------------------------------------------			
		
		-- داده های گم شده
		-- وجود دارند، ولی داده متناظر با آنها در جدول اصلی وجود ندارد [VIP].[VipMapping] استخراج اطلاعاتی که در جدول
		-- ×××××××××××××××
		-- وجود دارند رو حذف کنیم [VIP].[VipMapping] برای تصحیح این ایراد، بایستی اطلاعاتی که به صورت مازاد در جدول
		IF ',' + @TroubleShootingDataType + ',' LIKE '%,LostData,%'
		BEGIN
			-- این کد فقط بر روی سیستم های مرکز و شعب اجرا می شود
			IF @CenterBranchState in (1,2)
			BEGIN			
				DECLARE _VIPMapping CURSOR for
					   SELECT DISTINCT [VMA_TableID], [VMA_TableName] 
					   FROM [VIP].[VipMapping]
					   WHERE [VMA_BranchSchema] = 'Common'
				OPEN _VIPMapping
				FETCH NEXT FROM _VIPMapping INTO @VMA_TableID, @VMA_TableName
				WHILE @@FETCH_STATUS = 0
				BEGIN 
					SET @Table_AbbName = NULL
					
					SELECT @Table_AbbName = [TBL_AbbName], 
							@Table_PersianName = [TBL_PersianName] 
					FROM [Common].[Table] 
					WHERE [TBL_Name] = @VMA_TableName
					
					IF ISNULL(@Table_AbbName, '') <> ''                
					BEGIN
						-- شعبه
						IF @CenterBranchState = 1
						BEGIN
							SET @SQLString = 'DELETE FROM [VIP].[VipMapping]
											  WHERE [VMA_TableName] = ''' + @VMA_TableName + ''' AND 
													[VMA_BranchItemID] NOT IN (SELECT [' + @Table_AbbName + '_ID] 
																				FROM [Common].[' + @VMA_TableName + '])'
						END
						-- مرکز
						ELSE
						BEGIN
							SET @SQLString = 'DELETE FROM [VIP].[VipMapping]
											  WHERE [VMA_TableName] = ''' + @VMA_TableName + ''' AND 
													[VMA_ServerItemID] NOT IN (SELECT [' + @TABLE_AbbNAME + '_ID] 
																				FROM [Common].[' + @VMA_TableName + '])'
						END
						
						--PRINT(@SQLString)
												
						EXEC(@SQLString)
					END
						
				   FETCH NEXT FROM _VIPMapping INTO @VMA_TableID, @VMA_TableName
				END
				CLOSE _VIPMapping
				DEALLOCATE _VIPMapping	
			END
		END
		
       ------------------------------------------------
       SET NOCOUNT OFF;
END
