set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Rpts_GetGoldIndentOrdersInfo]
 -- Add the parameters for the stored procedure here
 @IndetOrdNum varchar(50)='',
 @IsAmount varchar='1'
AS
BEGIN

SELECT
g.Pm_Id
,Amount_All=SUM(g.Amount_All)
,g.Weight_All
,ReMark=replace(replace(ISNULL(g.ReMark,''),char(10),''),char(13),'')
,p.PNumber,p.Name,p.StylePic,p.StorageCount  INTO #INFO
from dbo.GoldIndentOrdersInfo as g
inner join dbo.GoldIndentOrders as a on a.Gi_Id=g.Gi_Id
inner join dbo.ProductModelInfo as p on p.Pm_Id=g.Pm_Id
where a.IndetOrdNum=@IndetOrdNum and
((@IsAmount='1' and p.StorageCount>0) or
 (@IsAmount='0' and p.StorageCount=0) or
  @IsAmount='2')
GROUP BY
g.Pm_Id,g.Weight_All
,replace(replace(ISNULL(g.ReMark,''),char(10),''),char(13),'')
,p.PNumber,p.Name,p.StylePic,p.StorageCount

SELECT * INTO #A_W_INFO
FROM
(
 SELECT DISTINCT
 Pm_Id
 FROM
 (
  SELECT *
  FROM #INFO
 ) AS V
)V OUTER APPLY(
SELECT
Out_Sum_W= STUFF(REPLACE(REPLACE(
 (
  SELECT Out_Sum_W= cast(Amount_All as varchar) + '*' +  cast(cast(Weight_All as float) as varchar)+'g' + CASE WHEN ReMark='' THEN '' ELSE '['+ReMark+']' END
  FROM      
  (
   SELECT *
   FROM #INFO
  ) AS VV
  WHERE VV.Pm_Id = V.Pm_Id
  FOR XML AUTO
 ), '<VV Out_Sum_W="', ';'), '"/>', ''), 1, 1, '')
)N1

SELECT
A.*
,B.Out_Sum_W
,ROW_NUMBER() OVER (ORDER BY A.PNumber) AS ROW_INDEX
FROM
(
SELECT DISTINCT
A.Pm_Id
,A.PNumber,A.Name,A.StylePic,A.StorageCount
FROM #INFO A
)AS A
INNER JOIN #A_W_INFO B ON B.Pm_Id=A.Pm_Id

  
--   select g.Pm_Id,g.Amount_All,g.Weight_All,g.ReMark,
--    p.PNumber,p.Name,p.StylePic,p.StorageCount,
--    ROW_NUMBER() OVER (ORDER BY g.Gi_Id) AS ROW_INDEX
--   from dbo.GoldIndentOrdersInfo as g
--   inner join dbo.GoldIndentOrders as a on a.Gi_Id=g.Gi_Id
--   inner join dbo.ProductModelInfo as p on p.Pm_Id=g.Pm_Id
--   where a.IndetOrdNum=@IndetOrdNum and
--   ((@IsAmount='1' and p.StorageCount>0) or
--    (@IsAmount='0' and p.StorageCount=0) or
--     @IsAmount='2')
   

END