
/****** Object:  View [dbo].[ViwAccWareHouse]    Script Date: 28/06/2022 01:35:04 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW[dbo].[ViwAccWareHouse]
AS
SELECT     Viw.InOROut AS FormType, 
          CASE Viw.InOROut WHEN 0 THEN N'رسيد' WHEN 1 THEN N'حواله' WHEN 2 THEN N'اول دوره' WHEN 3 THEN N'ب خريد' WHEN 4 THEN N'ب مصرف' WHEN 5 THEN N'اماني ما' WHEN
           6 THEN N'ب اماني ما' WHEN 7 THEN N'اماني ديگران' WHEN 8 THEN N'ب اماني ديگران' WHEN 9 THEN N'توليد' WHEN 10 THEN N'ب توليد' WHEN 11 THEN N'حواله فروش' WHEN 12 THEN
           N'انتقالی از' WHEN 120 THEN N'انتقالی به' WHEN 121 THEN N'ب فروش' ELSE N'نامشخص' END AS InOrOut, Viw.Number, Viw.UserNumber, Viw.Date, dbo.FARSI_DATE2(Viw.Date) AS DatePer, 
          Viw.WarehouseID AS WarehouseNo, Viw.PersonTotalID, Viw.PersonControlID, Viw.PersonDetailID1, Viw.PersonDetailID2, Viw.Description, Viw.UpdateDate, 
          Viw.Updater, COALESCE (dbo.AccDetail.Title, Viw.PersonTitle) AS PersonTitle, Detail.ID, Detail.WarehouseID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, 
          Detail.RequestUnit, Detail.UnitName, Detail.OrderTitle, Detail.OrderTotalID, Detail.OrderControlID, Detail.OrderDetailID1, Detail.OrderDetailID2, 
          CASE WHEN Viw.InOROut = 0 OR
          Viw.InOROut = 2 OR
          Viw.InOROut = 4 OR
          Viw.InOROut = 6 OR
          Viw.InOROut = 7 OR
          Viw.InOROut = 9 OR
          Viw.InOROut = 120 OR
          Viw.InOROut = 121 THEN Detail.Request ELSE 0 END AS RequestIn, CASE WHEN Viw.InOROut = 1 OR
          Viw.InOROut = 3 OR
          Viw.InOROut = 5 OR
          Viw.InOROut = 8 OR
          Viw.InOROut = 10 OR
          Viw.InOROut = 11 OR
          Viw.InOROut = 12 THEN Detail.Request ELSE 0 END AS RequestOut, Detail.DocItemID, Detail.DocHeaderID, Detail.Status, Detail.Selected, COALESCE (Detail.Unit2, 0) 
          AS Unit2, COALESCE (Detail.Value2, 0) AS Value2, COALESCE (Detail.Unit2Name, N'') AS UnitName2, CASE WHEN Viw.InOROut = 0 OR
          Viw.InOROut = 2 OR
          Viw.InOROut = 4 OR
          Viw.InOROut = 6 OR
          Viw.InOROut = 7 OR
          Viw.InOROut = 9 OR
          Viw.InOROut = 120 OR
          Viw.InOROut = 121 THEN COALESCE (Detail.Value2, 0) ELSE 0 END AS RequestIn2, CASE WHEN Viw.InOROut = 1 OR
          Viw.InOROut = 3 OR
          Viw.InOROut = 5 OR
          Viw.InOROut = 8 OR
          Viw.InOROut = 10 OR
          Viw.InOROut = 11 OR
          Viw.InOROut = 12 THEN COALESCE (Detail.Value2, 0) ELSE 0 END AS RequestOut2, Detail.UsedDocNumber, Detail.DocNumber, Detail.OtherTotalID, 
          Detail.OtherControlID, Detail.OtherDetailID1, Detail.OtherDetailID2, Detail.OtherTitle, Viw.WarehouseIDTo, Viw.FormID, Viw.FromForm, Detail.SerialFrom, 
          Detail.SerialTo, Detail.Oft, Detail.Routobat, Detail.OftPayeh, Detail.RoutobatPayeh, Detail.RequestPayeh, Detail.Time, Detail.Description AS DetailDescription, 
          Detail.UserNumber AS DetailUserNumber, Viw.OutID, Viw.Takhfif, Viw.HamlAndOther, Viw.TakhfifDocItemID, Viw.TakhfifDocHeaderID, Viw.HamlDocItemID, 
          Viw.HamlDocHeaderID, Detail.Haml AS DetailHaml, Detail.Takhfif AS DetailTakhfif, viw.CostCenter, viw.Project 
FROM         dbo.ViwAccWareHousePhas1 AS Viw WITH (NOLOCK) LEFT OUTER JOIN
          dbo.ViwAccWarehouseDetails AS Detail ON Viw.ID = Detail.WarehouseID LEFT OUTER JOIN
          dbo.AccDetail ON Viw.PersonDetailID2 = dbo.AccDetail.ID
WHERE     (NOT (Detail.ID IS NULL))
union
SELECT     120 AS FormType, N'انتقالی به' AS InOrOut, Viw.Number, Viw.UserNumber, Viw.Date, dbo.FARSI_DATE2(Viw.Date) AS DatePer, Viw.WarehouseIDTo AS WarehouseNo, 
          Viw.PersonTotalID, Viw.PersonControlID, Viw.PersonDetailID1, Viw.PersonDetailID2, Viw.Description, Viw.UpdateDate, Viw.Updater, COALESCE (AccDetail_1.Title, 
          Viw.PersonTitle) AS PersonTitle, Detail.ID, Detail.WarehouseID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, Detail.RequestUnit, Detail.UnitName, 
          Detail.OrderTitle, Detail.OrderTotalID, Detail.OrderControlID, Detail.OrderDetailID1, Detail.OrderDetailID2, Detail.Request AS RequestIn, 0 AS RequestOut, 
          Detail.DocItemID, Detail.DocHeaderID, Detail.Status, Detail.Selected, COALESCE (Detail.Unit2, 0) AS Unit2, COALESCE (Detail.Value2, 0) AS Value2, 
          COALESCE (Detail.Unit2Name, N'') AS UnitName2, COALESCE (Detail.Value2, 0) AS RequestIn2, 0 AS RequestOut2, Detail.UsedDocNumber, Detail.DocNumber, 
          Detail.OtherTotalID, Detail.OtherControlID, Detail.OtherDetailID1, Detail.OtherDetailID2, Detail.OtherTitle, Viw.WarehouseID AS Expr1, Viw.FormID, Viw.FromForm, 
          Detail.SerialFrom, Detail.SerialTo, Detail.Oft, Detail.Routobat, Detail.OftPayeh, Detail.RoutobatPayeh, Detail.RequestPayeh, Detail.Time, 
          Detail.Description AS DetailDescription, Detail.UserNumber AS DetailUserNumber, Viw.OutID, Viw.Takhfif, Viw.HamlAndOther, Viw.TakhfifDocItemID, 
          Viw.TakhfifDocHeaderID, Viw.HamlDocItemID, Viw.HamlDocHeaderID, Detail.Haml as DetailHaml, Detail.Takhfif AS DetailTakhfif, viw.CostCenter, viw.Project 
FROM         dbo.ViwAccWareHousePhas1  AS Viw WITH (NOLOCK) LEFT OUTER JOIN
          dbo.ViwAccWarehouseDetails AS Detail ON Viw.ID = Detail.WarehouseID LEFT OUTER JOIN
          dbo.AccDetail AS AccDetail_1 ON Viw.PersonDetailID2 = AccDetail_1.ID
WHERE     (NOT (Detail.ID IS NULL)) AND (Viw.InOROut = 120 or Viw.InOROut = 1) AND (Viw.WarehouseIDTo > 0)
GO


