USE [Prince]
GO
/****** Object:  StoredProcedure [Gold].[WebApi_GetGoldCommodity]    Script Date: 12/18/2025 10:01:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		zareie
-- Create date: 2025-05-28 | 1404-03-07
-- Description:	تهیه لیست کالا ها
-- =============================================
ALTER PROCEDURE [Gold].[WebApi_GetGoldCommodity]
    @GCM_ID      [Common].[_INT_]

AS
BEGIN
	SET NOCOUNT ON;
    
	DECLARE @Res							[Common].[_INT_] = 0, -- 0: Success
			@ResMsg							[Common].[_WIDE_]= N'دریافت اطلاعات کالا با موفقیت انجام شد.',
			@GCM_Type_Gold					[Common].[_INT_] = 1, --طلای ساخته
			@GCM_Type_Coin					[Common].[_INT_] = 2, --سکه
			@GCM_Type_Rock					[Common].[_INT_] = 3, -- سنگ
			@GCM_Type_Commodity				[Common].[_INT_] = 4, -- کالا
			@GCM_Type_Service				[Common].[_INT_] = 5, -- خدمات
			@GCM_Type_Jewel					[Common].[_INT_] = 6,  -- جواهر 
			@LKP_CmdType_Services			[Common].[_INT_] = 0,
			@LKP_CmdType_Commodity			[Common].[_INT_] = 0,
			@DefaultApi_Gold_ProfitKind		[Common].[_INT_] = 0,
			@DefaultApi_Gold_ProfitValue	[Common].[_MONEY_] = 0,
			@LKP_Unit_Gr	                [Common].[_INT_] = 0,
		    @LKP_Unit_Number	            [Common].[_INT_] = 0,
		    @LKP_Unit_Carat 	            [Common].[_INT_] = 0 
			
		      
			
	BEGIN TRY  
	    
		SET @DefaultApi_Gold_ProfitKind = (SELECT SST_Value FROM Common.SystemSetting WHERE SST_Title = 'DefaultSiteGoldApiProfitKind' AND SST_UserID = 0)
        SET @DefaultApi_Gold_ProfitValue = (SELECT SST_Value FROM Common.SystemSetting WHERE SST_Title = 'DefaultSiteGoldApiProfitValue' AND SST_UserID = 0)
		SET @LKP_Unit_Gr = [Common].[LKP_EngCaptionToID]('Unit_Gr', N'گرم', 101)
		SET @LKP_Unit_Number = [Common].[LKP_EngCaptionToID]('Unit_Number', N'عدد', 402)
	    SET @LKP_Unit_Carat = [Common].[LKP_EngCaptionToID]('Unit_Carat', N'قیراط', 1054)
	    
	    
		IF ISNULL(@GCM_ID, 0) <= 0
		BEGIN
			SET @Res = -2
			SET @ResMsg = N'شناسه کالا به درستی وارد نشده است.'
        SELECT @Res AS Res,
               @ResMsg AS ResMsg,
               0 AS ProductID,			   
			   0 AS ProductType,
			   '' AS ProductTypeTitle,
			   '' AS ProductCode,			   
			   '' AS ProductBarcode,
			   '' AS ProductName,
               0  AS ProductGroupID,
               '' AS [ProductGroupName],
               0  AS ProductGroupItemID,
               '' AS [ProductGroupItemName],
               0.0 AS ProductCutie,
               0 AS ProductUnitID,
               '' AS ProductUnit,               
               0 AS ProductSaleFeeType,
               '' AS ProductSaleFeeTypeTitle ,
               0 AS ProductSaleFeeValue,
               0 AS ProductSaleFeePercent,
               0 AS ProductPriceCalculationType, 
			   '' AS ProductPriceCalculationTypeTitle ,
			   0 AS ProductPrimaryWeight,
			   0 AS ProductRockRialCost,
			   0 AS ProductRockWeight,
               0 AS ProductRockCount,
			   0.00 AS ProductToll,
			   0.00 AS ProductTax,			   
			   0 AS ProductExemptOnSale,
			   0 AS ProductIsDisabled,
			   0 AS ProductIsInteger,			   
			   0 AS ProductSalePrice,			   
			   '' AS productDescription,
			   0 AS ProductLeatherRialCost,
			   0 AS ProductSilverRialCost,			   
			   0 AS ProductCalcTaxTollType,
			   0 AS ProductRefCurrentAmount_Count ,
               0 AS ProductRefCurrentAmount_Gold,
               0 AS ProductProfitKind,
               0 AS ProductProfitValue
			RETURN
		END		
	    
	    
	    IF ISNULL(( SELECT GCM_PrimaryWeight FROM Gold.GoldCommodity WHERE (GCM_ID = ISNULL(@GCM_ID, 0))),0) <= 0
		BEGIN
			SET @Res = -3
			SET @ResMsg = N'وزن پیش فرض کالا وارد نشده.'
        SELECT @Res AS Res,
               @ResMsg AS ResMsg,
               0 AS ProductID,			   
			   0 AS ProductType,
			   '' AS ProductTypeTitle,
			   '' AS ProductCode,			   
			   '' AS ProductBarcode,
			   '' AS ProductName,
               0  AS ProductGroupID,
               '' AS [ProductGroupName],
               0  AS ProductGroupItemID,
               '' AS [ProductGroupItemName],
               0.0 AS ProductCutie,
               0 AS ProductUnitID,
               '' AS ProductUnit,               
               0 AS ProductSaleFeeType,
               '' AS ProductSaleFeeTypeTitle ,
               0 AS ProductSaleFeeValue,
               0 AS ProductSaleFeePercent,
               0 AS ProductPriceCalculationType, 
			   '' AS ProductPriceCalculationTypeTitle ,
			   0 AS ProductPrimaryWeight,
			   0 AS ProductRockRialCost,
			   0 AS ProductRockWeight,
               0 AS ProductRockCount,
			   0.00 AS ProductToll,
			   0.00 AS ProductTax,			   
			   0 AS ProductExemptOnSale,
			   0 AS ProductIsDisabled,
			   0 AS ProductIsInteger,			   
			   0 AS ProductSalePrice,			   
			   '' AS productDescription,
			   0 AS ProductLeatherRialCost,
			   0 AS ProductSilverRialCost,			   
			   0 AS ProductCalcTaxTollType,
			   0 AS ProductRefCurrentAmount_Count ,
               0 AS ProductRefCurrentAmount_Gold,
               0 AS ProductProfitKind,
               0 AS ProductProfitValue
			RETURN
		END

		SET @LKP_CmdType_Services = (SELECT [Common].[LKP_EngCaptionToID]('CmdType_Services', N'خدمات', 146)) 
		SET @LKP_CmdType_Commodity = (SELECT[Common].[LKP_EngCaptionToID]('CmdType_Commodity', N'كالا', 145))  


		;WITH CTE_Amount 
		AS
		(
			SELECT GSA_GCM_ID,
                SUM(GSA_RefCurrentAmount_Count) AS GSA_RefCurrentAmount_Count ,
                SUM(GSA_RefCurrentAmount_Gold) AS GSA_RefCurrentAmount_Gold
	       FROM Gold.GoldStoreCommodityAmount
	      WHERE GSA_GCM_ID = @GCM_ID
	   GROUP BY GSA_GCM_ID 
		)

		SELECT @Res AS Res,
			   @ResMsg AS ResMsg,
			   GCM_ID AS ProductID,
			   GCM_Type AS ProductType,
			   (SELECT [GCG_Name] FROM [Gold].[GoldCommodityGroup] WHERE [GCG_ID] = [GCM_Type]) AS ProductTypeTitle,
			   GCM_Code AS ProductCode,			   
			   GCM_Barcode AS ProductBarcode,
			   LTRIM(RTRIM(REPLACE(REPLACE(GCM_Name, N'ي', N'ی'), N'ك', N'ک'))) AS ProductName,
               isnull([GCM_GCG_GroupID],0)  AS ProductGroupID,
               isnull((SELECT [GCG_Name] FROM [Gold].[GoldCommodityGroup] WHERE [GCG_ID] = [GCM_GCG_GroupID]),'') AS [ProductGroupName],
               isnull([GCM_GCG_GroupItemID],0)  AS ProductGroupItemID,
               isnull((SELECT [GCG_Name] FROM [Gold].[GoldCommodityGroup] WHERE [GCG_ID] = [GCM_GCG_GroupItemID]),'') AS [ProductGroupItemName],
               [GCM_Cutie] AS ProductCutie,
               ProductUnitID = CASE WHEN ISNULL([GCM_LKP_UnitID] , 0) = 0 THEN 
                                         CASE WHEN GCM_Type IN (1,6) THEN @LKP_Unit_Gr
                                              WHEN GCM_Type IN (2,4,5) THEN @LKP_Unit_Number
                                              ELSE @LKP_Unit_Carat 
                                         END
                                    ELSE [GCM_LKP_UnitID]
                               END,
               ProductUnit = (SELECT [LKP_Caption] FROM [Common].[Lookup] WHERE [LKP_ID] = 
                               CASE WHEN ISNULL([GCM_LKP_UnitID] , 0) = 0 THEN 
                                         CASE WHEN GCM_Type IN (1,6) THEN @LKP_Unit_Gr
                                              WHEN GCM_Type IN (2,4,5) THEN @LKP_Unit_Number
                                              ELSE @LKP_Unit_Carat 
                                         END
                                    ELSE [GCM_LKP_UnitID]
                               END),
               [GCM_SaleFeeType] AS ProductSaleFeeType,
               ProductSaleFeeTypeTitle = CASE [GCM_SaleFeeType] WHEN 1 THEN N'وزنی' WHEN 2 THEN N'تعدادی' END,
               [GCM_SaleFeeValue] AS ProductSaleFeeValue,
               [GCM_SaleFeePercent] AS ProductSaleFeePercent,
               [GCM_PriceCalculationType] AS ProductPriceCalculationType, 
			   ProductPriceCalculationTypeTitle = CASE [GCM_PriceCalculationType] WHEN 1 THEN N'وزنی' WHEN 2 THEN N'تعدادی' END,
			   [GCM_PrimaryWeight] AS ProductPrimaryWeight,
			   ISNULL([GCM_RockRialCost],0) AS ProductRockRialCost,
			   [GCM_RockWeight] AS ProductRockWeight,
               [GCM_RockCount] AS ProductRockCount,
			   CAST(GCM_Toll AS DECIMAL(38,2)) AS ProductToll,
			   CAST(GCM_Tax AS DECIMAL(38,2)) AS ProductTax, 			  
			   GCM_ExemptOnSale AS ProductExemptOnSale,
			   GCM_IsDisabled AS ProductIsDisabled,
			   GCM_IsInteger AS ProductIsInteger,			   
			   GCM_SalePrice AS ProductSalePrice, 			   
			   GCM_Comment AS productDescription,
			   ISNULL(GCM_LeatherRialCost,0) AS ProductLeatherRialCost,
			   ISNULL(GCM_SilverRialCost,0) AS ProductSilverRialCost,			   
			   GCM_CalcTaxTollType AS ProductCalcTaxTollType, 
			   ISNULL(GSA_RefCurrentAmount_Count,0) AS ProductRefCurrentAmount_Count ,
               ISNULL(GSA_RefCurrentAmount_Gold,0) AS ProductRefCurrentAmount_Gold,
			   @DefaultApi_Gold_ProfitKind AS  ProductProfitKind,
			   @DefaultApi_Gold_ProfitValue AS ProductProfitValue
			    
		FROM [Gold].[GoldCommodity]
   LEFT JOIN CTE_Amount ON (GSA_GCM_ID = GCM_ID)
       WHERE (GCM_ShareInApi = 1) AND GSA_GCM_ID = @GCM_ID     

	END TRY  
	BEGIN CATCH  
		SET @ResMsg = N'خطای ناشناس »» ' + (SELECT ERROR_MESSAGE() AS ErrorMessage)
        SET @Res = -1;
        SELECT @Res AS Res,
               @ResMsg AS ResMsg,
               0 AS ProductID,			   
			   0 AS ProductType,
			   '' AS ProductTypeTitle,
			   '' AS ProductCode,			   
			   '' AS ProductBarcode,
			   '' AS ProductName,
               0  AS ProductGroupID,
               '' AS [ProductGroupName],
               0  AS ProductGroupItemID,
               '' AS [ProductGroupItemName],
               0.0 AS ProductCutie,
               0 AS ProductUnitID,
               '' AS ProductUnit,               
               0 AS ProductSaleFeeType,
               '' AS ProductSaleFeeTypeTitle ,
               0 AS ProductSaleFeeValue,
               0 AS ProductSaleFeePercent,
               0 AS ProductPriceCalculationType, 
			   '' AS ProductPriceCalculationTypeTitle ,
			   0 AS ProductPrimaryWeight,
			   0 AS ProductRockRialCost,
			   0 AS ProductRockWeight,
               0 AS ProductRockCount,
			   0.00 AS ProductToll,
			   0.00 AS ProductTax,			   
			   0 AS ProductExemptOnSale,
			   0 AS ProductIsDisabled,
			   0 AS ProductIsInteger,			   
			   0 AS ProductSalePrice,			   
			   '' AS productDescription,
			   0 AS ProductLeatherRialCost,
			   0 AS ProductSilverRialCost,			   
			   0 AS ProductCalcTaxTollType,
			   0 AS ProductRefCurrentAmount_Count ,
               0 AS ProductRefCurrentAmount_Gold,
               0 AS ProductProfitKind,
               0 AS ProductProfitValue
			   
	END CATCH;
END
