SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[View_1]
AS
SELECT        FOROSHID, TedadFo, UnitPriceFo, FieldN2
FROM            dbo.[TRANSACTION]
WHERE        (NOT (FOROSHID IS NULL))
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[QSubPFrm2_CloseShift](@VFlag BIT, @dateFo NVARCHAR(255))
AS
SELECT        T_Forosh.FOROSHID, T_Forosh.DateFo, T_Forosh.Flag1, dbo.HesabName(T_Forosh.KOLID, T_Forosh.HesabID, T_Forosh.THESABID) AS NameHesab, TRANSACTIONKala.FieldN2, SUM(CONVERT(MONEY, 
                         TRANSACTIONKala.TedadFo * TRANSACTIONKala.UnitPriceFo)) AS sumation, CONVERT(NVARCHAR(50), T_Forosh.Sharh) AS sharh, T_Forosh.Takhfif, F_MabHazine_Sandogh.mabhazine, SUM(CONVERT(MONEY, 
                         CASE WHEN dbo.T_Forosh.KOLID IS NULL THEN ([TRANSACTIONKala].TedadFo * [TRANSACTIONKala].UnitPriceFo) ELSE 0 END)) AS MabSaleSandogh, SUM(CONVERT(MONEY, CASE WHEN dbo.T_Forosh.KOLID IS NOT NULL 
                         THEN ([TRANSACTIONKala].TedadFo * [TRANSACTIONKala].UnitPriceFo) ELSE 0 END)) AS MabSaleCust, CASE WHEN dbo.T_Forosh.KOLID IS NOT NULL THEN (1) ELSE 0 END AS CountSaleCust, T_Forosh.NumberKH, 
                         T_Forosh.ForoshNumbr
FROM            T_Forosh INNER JOIN
                         View_1 AS TRANSACTIONKala ON T_Forosh.FOROSHID = TRANSACTIONKala.FOROSHID LEFT OUTER JOIN
                         dbo.F_MabHazine_Sandogh() AS F_MabHazine_Sandogh ON T_Forosh.FOROSHID = F_MabHazine_Sandogh.Factid LEFT OUTER JOIN
                         T_SA2 ON T_Forosh.PrimarySaid2 = T_SA2.SAID2
WHERE        (T_SA2.FieldN2 = 26) OR
                         (T_SA2.FieldN2 IS NULL)
GROUP BY T_Forosh.FOROSHID, T_Forosh.DateFo, dbo.HesabName(T_Forosh.KOLID, T_Forosh.HesabID, T_Forosh.THESABID), TRANSACTIONKala.FieldN2, T_Forosh.Flag1, CONVERT(NVARCHAR(50), T_Forosh.Sharh), T_Forosh.Takhfif, 
                         F_MabHazine_Sandogh.mabhazine, CASE WHEN dbo.T_Forosh.KOLID IS NOT NULL THEN (1) ELSE 0 END, T_Forosh.NumberKH, T_Forosh.ForoshNumbr
HAVING        (T_Forosh.Flag1 = @VFlag) AND (T_Forosh.DateFo = @dateFo) OR
                         (T_Forosh.Flag1 = @VFlag) AND (T_Forosh.DateFo = @dateFo)
ORDER BY T_Forosh.DateFo DESC, T_Forosh.FOROSHID DESC
GO
