SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Installments]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Installments](
	[InstallmentID] [int] IDENTITY(1,1) NOT NULL,
	[InstallmentNumber] [nvarchar](50) NULL,
	[DateCreate] [smalldatetime] NULL,
	[DateModified] [smalldatetime] NULL,
	[VSabt] [int] NULL,
	[HESABID] [int] NULL,
	[THESABID] [int] NULL,
	[Mablagh] [decimal](19, 4) NULL,
	[MablaghPish] [decimal](19, 4) NULL,
	[SharhTazmin] [nvarchar](255) NULL,
	[DarSad] [decimal](19, 4) NULL,
	[ShSeryal] [nvarchar](255) NULL,
	[DARID2] [int] NULL,
	[DateStart] [nvarchar](255) NULL,
	[DateSabt] [nvarchar](255) NULL,
	[Sharh] [nvarchar](255) NULL,
	[ZamenName] [nvarchar](255) NULL,
	[ZamenAddress] [nvarchar](255) NULL,
	[ZamenPhone] [nvarchar](255) NULL,
	[FieldN1] [int] NULL,
	[FieldN2] [int] NULL,
	[FieldN3] [int] NULL,
	[FieldN4] [int] NULL,
	[FieldT1] [nvarchar](255) NULL,
	[Flag1] [bit] NULL,
	[Flag2] [bit] NULL,
 CONSTRAINT [PK_Installments] PRIMARY KEY CLUSTERED 
(
	[InstallmentID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO

/****** Object:  Table [dbo].[Installments_DetailPayment]    Script Date: 07/17/2023 15:41:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Installments_DetailPayment]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Installments_DetailPayment](
	[InstsubPayID] [int] IDENTITY(1,1) NOT NULL,
	[InstallmentID] [int] NULL,
	[DateCreate] [smalldatetime] NULL,
	[DateModified] [smalldatetime] NULL,
	[Mablagh] [decimal](19, 4) NULL,
	[MabTakhfif] [decimal](19, 4) NULL,
	[MabEzafePardakht] [decimal](19, 4) NULL,
	[DARID1] [int] NULL,
	[DateVosool] [nvarchar](255) NULL,
	[Sharh] [nvarchar](255) NULL,
	[ShSeryal] [nvarchar](255) NULL,
	[FieldN1] [int] NULL,
	[FieldN2] [int] NULL,
	[FieldN3] [int] NULL,
	[FieldN4] [int] NULL,
	[FieldT1] [nvarchar](255) NULL,
	[FlagPasManuelly] [bit] NULL,
	[FlagPas] [bit] NULL,
	[RowNum] [int] NULL,
	[FlagActive] [int] NULL,
 CONSTRAINT [PK_Installments_DetailPayment] PRIMARY KEY CLUSTERED 
(
	[InstsubPayID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[Installments_Detail]    Script Date: 07/17/2023 15:41:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Installments_Detail]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Installments_Detail](
	[InstallmentsubID] [int] IDENTITY(1,1) NOT NULL,
	[InstallmentID] [int] NULL,
	[DateCreate] [smalldatetime] NULL,
	[DateModified] [smalldatetime] NULL,
	[MablaghGhest] [decimal](19, 4) NULL,
	[MablaghGhestinline] [decimal](19, 4) NULL,
	[Mablagh] [decimal](19, 4) NULL,
	[Mablaghinline] [decimal](19, 4) NULL,
	[MabSood] [decimal](19, 4) NULL,
	[MabSoodinline] [decimal](19, 4) NULL,
	[MabEzafePardakht] [decimal](19, 4) NULL,
	[MabTakhfif] [decimal](19, 4) NULL,
	[MabDirkard] [decimal](19, 4) NULL,
	[DARID2] [int] NULL,
	[DateSR] [nvarchar](255) NULL,
	[DateVosool] [nvarchar](255) NULL,
	[Sharh] [nvarchar](255) NULL,
	[ShSeryal] [nvarchar](255) NULL,
	[FieldN1] [int] NULL,
	[FieldN2] [int] NULL,
	[FieldN3] [int] NULL,
	[FieldN4] [int] NULL,
	[FieldT1] [nvarchar](255) NULL,
	[FlagPasManuelly] [bit] NULL,
	[FlagPas] [bit] NULL,
	[FlagFromoldyear] [bit] NULL,
	[RowNum] [int] NULL,
	[FlagActive] [int] NULL,
	[Balance] [decimal](19, 4) NULL,
	[Mabpayment] [decimal](19, 4) NULL,
 CONSTRAINT [PK_Installments_Detail] PRIMARY KEY CLUSTERED 
(
	[InstallmentsubID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'InstallmentID'
      AND Object_ID = Object_ID(N'T_Dar1'))
BEGIN
	
    ALTER TABLE dbo.T_Dar1
    ADD InstallmentID INT NULL
END
GO
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'InstsubPayIDInstallment'
      AND Object_ID = Object_ID(N'T_Dar1'))
BEGIN
	
    ALTER TABLE dbo.T_Dar1
    ADD InstsubPayIDInstallment INT NULL
END
GO
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'InstallmentID'
      AND Object_ID = Object_ID(N'T_Forosh'))
BEGIN
	
    ALTER TABLE dbo.T_Forosh
    ADD InstallmentID INT NULL
END
GO
/****** Object:  ForeignKey [FK_Installments_Detail_Installments]    Script Date: 07/17/2023 15:41:38 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Installments_Detail_Installments]') AND type = 'F')
ALTER TABLE [dbo].[Installments_Detail]  WITH CHECK ADD  CONSTRAINT [FK_Installments_Detail_Installments] FOREIGN KEY([InstallmentID])
REFERENCES [dbo].[Installments] ([InstallmentID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Installments_Detail_Installments]') AND type = 'F')
ALTER TABLE [dbo].[Installments_Detail] CHECK CONSTRAINT [FK_Installments_Detail_Installments]
GO
/****** Object:  ForeignKey [FK_Installments_DetailPayment_Installments]    Script Date: 07/17/2023 15:41:38 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Installments_DetailPayment_Installments]') AND type = 'F')
ALTER TABLE [dbo].[Installments_DetailPayment]  WITH CHECK ADD  CONSTRAINT [FK_Installments_DetailPayment_Installments] FOREIGN KEY([InstallmentID])
REFERENCES [dbo].[Installments] ([InstallmentID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Installments_DetailPayment_Installments]') AND type = 'F')
ALTER TABLE [dbo].[Installments_DetailPayment] CHECK CONSTRAINT [FK_Installments_DetailPayment_Installments]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_DeleteInstallments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Q_DeleteInstallments]
GO
/****** Object:  StoredProcedure [dbo].[Q_SaveInstallments_Details]    Script Date: 07/17/2023 15:40:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_SaveInstallments_Details]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Q_SaveInstallments_Details]
GO
/****** Object:  StoredProcedure [dbo].[Q_SaveInstallments_Details_Balance]    Script Date: 07/17/2023 15:40:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_SaveInstallments_Details_Balance]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Q_SaveInstallments_Details_Balance]
GO
/****** Object:  StoredProcedure [dbo].[Q_GetInstallments]    Script Date: 07/17/2023 15:40:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_GetInstallments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Q_GetInstallments]
GO
/****** Object:  StoredProcedure [dbo].[Q_GetInstallments_Details]    Script Date: 07/17/2023 15:40:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_GetInstallments_Details]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Q_GetInstallments_Details]
GO
/****** Object:  StoredProcedure [dbo].[Q_GetInstallments_Details_Payments]    Script Date: 07/17/2023 15:40:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_GetInstallments_Details_Payments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Q_GetInstallments_Details_Payments]
GO
/****** Object:  UserDefinedFunction [dbo].[PPMT]    Script Date: 07/17/2023 15:40:07 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PPMT]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[PPMT]
GO
/****** Object:  StoredProcedure [dbo].[Q_ChangeInstallments_DetailRowStatus]    Script Date: 07/17/2023 15:40:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_ChangeInstallments_DetailRowStatus]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Q_ChangeInstallments_DetailRowStatus]
GO
/****** Object:  StoredProcedure [dbo].[Q_GetInstallments_Print]    Script Date: 07/17/2023 15:40:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_GetInstallments_Print]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Q_GetInstallments_Print]
GO
/****** Object:  UserDefinedFunction [dbo].[GetInstallmentAdditionalPayment]    Script Date: 07/17/2023 15:40:07 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GetInstallmentAdditionalPayment]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[GetInstallmentAdditionalPayment]
GO
/****** Object:  UserDefinedFunction [dbo].[GetInstallmentPayment]    Script Date: 07/17/2023 15:40:07 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GetInstallmentPayment]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[GetInstallmentPayment]
GO
/****** Object:  StoredProcedure [dbo].[Q_SaveInstallments]    Script Date: 07/17/2023 15:40:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_SaveInstallments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Q_SaveInstallments]
GO
/****** Object:  UserDefinedFunction [dbo].[IPMT]    Script Date: 07/17/2023 15:40:07 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[IPMT]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[IPMT]
GO
/****** Object:  UserDefinedFunction [dbo].[sgfn_DateToShamsiDate]    Script Date: 07/17/2023 15:40:07 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sgfn_DateToShamsiDate]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[sgfn_DateToShamsiDate]
GO
/****** Object:  UserDefinedFunction [dbo].[sgfn_DateToShamsiDatePart]    Script Date: 07/17/2023 15:40:07 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sgfn_DateToShamsiDatePart]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[sgfn_DateToShamsiDatePart]
GO
/****** Object:  UserDefinedFunction [dbo].[PersianToGregorian]    Script Date: 07/17/2023 15:40:07 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PersianToGregorian]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[PersianToGregorian]
GO
/****** Object:  UserDefinedFunction [dbo].[PMT]    Script Date: 07/17/2023 15:40:07 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PMT]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[PMT]
GO
/****** Object:  StoredProcedure [dbo].[Q_GetInstallments_Details_Preview]    Script Date: 07/17/2023 15:40:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_GetInstallments_Details_Preview]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Q_GetInstallments_Details_Preview]
GO
/****** Object:  UserDefinedFunction [dbo].[Date_MD2SHD]    Script Date: 07/17/2023 15:40:07 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Date_MD2SHD]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[Date_MD2SHD]
GO
/****** Object:  UserDefinedFunction [dbo].[FV]    Script Date: 07/17/2023 15:40:07 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FV]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[FV]
GO
/****** Object:  UserDefinedFunction [dbo].[FV]    Script Date: 07/17/2023 15:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FV]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[FV]
	(@Rate FLOAT
	,@Periods INT
	,@Payment FLOAT
	,@Value FLOAT
	,@Type INT)  
RETURNS FLOAT
    BEGIN   
	    SET @Type = ISNULL(@Type,0);
		SET @Value = ISNULL(@Value,0);
		DECLARE @Result AS FLOAT=0;
		DECLARE @Term AS FLOAT = 0;

		IF @Rate=0
		BEGIN
			SET @Result=(@Value+@Payment)*@Periods;
		END /****** IF @Rate=0 ******/
		ELSE
		BEGIN /* IF @Rate <> 0 */
			SET @term = POWER(1+@rate,@periods);
			
			IF @Type=1
			BEGIN
				SET @Result=@Value*@Term+@Payment*(1+@Rate)*(@Term-1.0)/@Rate
			END
			ELSE
			BEGIN
				SET @Result=@value*@term+@Payment*(@Term-1)/@Rate	
			END
		END /* IF @Rate <> 0 */

        RETURN -@Result  
    END;
' 
END
GO

/****** Object:  UserDefinedFunction [dbo].[Date_MD2SHD]    Script Date: 07/17/2023 15:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Date_MD2SHD]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE function [dbo].[Date_MD2SHD](@inputdate DATETIME )
Returns varchar(10) 
As Begin

Declare	@Year 		int,
	@Mon 		int,
	@Day 		int,
	@MiladiDay 	int,
	@M_SH_DayDiff 	int,
	@Div400 	int,
	@Div100 	int,
	@Div4 		int,
	@KabCount 	int,
	@NormalCount 	int,
	@YearDayCount 	int,
	@MonDayCount 	int,
	@IsLeap 	int,
	@Value 		int,
	@Div33 		int,
	@Mod33 		int,
	@Mod4 		int,
	@Div1 		int,
	@Mod1 		int,
	@FinallYear	varchar(4),
	@FinallMon	varchar(2),
	@FinallDay	varchar(2),
	@SHD varchar(10)
	
	Set	@SHD	=	rtrim(ltrim(convert(char,Datepart(yyyy,@inputdate))))+''/''+  right( ''0''+ rtrim( ltrim(convert(char(2),Datepart(mm,@inputdate)))),2)+''/'' + right( ''0''+ rtrim( ltrim(convert(char(2),Datepart(dd,@inputdate)))),2)

	

Select	@M_SH_DayDiff 	= 226894, /* Days difference between SHamsi and Milladi Calender */
	@SHD		= Replace(@SHD , ''-'' , ''/''),
	@Year		= Cast( SubString(@SHD , 1 , CharIndex (''/'' ,@SHD)-1) As Int),
	@SHD		= SubString(@SHD , CharIndex (''/'' , @SHD) + 1, Len(@SHD) ),
	@Mon		= Cast(SubString(@SHD , 1 , CharIndex (''/'' , @SHD) - 1) As Int),
	@SHD		= SubString(@SHD, CharIndex ( ''/'' , @SHD) + 1, Len(@SHD)),
	@Day 		= Cast(@SHD As Int),
	@Div400		= (@Year - 1) /400,
	@Div100		= (@Year - 1) /100,
	@Div4		= (@Year - 1) / 4,
	@KabCount	= @Div4 - @Div100 + @Div400,
	@NormalCount	= @Year - @KabCount - 1,
	@YearDayCount	= @KabCount * 366 + @NormalCount * 365,
	@IsLeap		= Case
				When (@Year % 4 = 0) And ((@Year % 100 <> 0) Or (@Year % 400 = 0))	Then	1
				Else									0
			  End,
	@MonDayCount 	= Case 
				When @Mon = 1 	Then 0
				When @Mon = 2 	Then 31
				When @Mon = 3 	Then 28 + 31
				When @Mon = 4 	Then 28 + 2 * 31
				When @Mon = 5 	Then 28 + 2 * 31 + 30
				When @Mon = 6 	Then 28 + 3 * 31 + 30
				When @Mon = 7 	Then 28 + 3 * 31 + 2 * 30
				When @Mon = 8 	Then 28 + 4 * 31 + 2 * 30
				When @Mon = 9 	Then 28 + 5 * 31 + 2 * 30
				When @Mon = 10 	Then 28 + 5 * 31 + 3 * 30
				When @Mon = 11 	Then 28 + 6 * 31 + 3 * 30
				When @Mon = 12 	Then 28 + 6 * 31 + 4 * 30
			  End,
	@MonDayCount	= Case
				When @IsLeap = 1 And @Mon > 2	Then 	@MonDayCount + 1
				Else					@MonDayCount
			  End,
	@MiladiDay	= @YearDayCount + @MonDayCount + @Day,
	@Value 		= @MiladiDay - @M_SH_DayDiff,
	@Div33		= (@Value - 1) / (8 * 366 + 25 * 365),
	@Mod33		= (@Value - 1) % (8 * 366 + 25 * 365)

If @Mod33 < 5 * 1461 + 365

	Select	@Div4	= @Mod33 / 1461,
		@Mod4	= @Mod33 % 1461,
		@Div1	= 0
Else
	Select 	@Div4	= (@Mod33 - 5 * 1461 - 365) / 1461  + 5,
		@Mod4	= (@Mod33 - 5 * 1461 - 365) % 1461,
		@Div1	= 1

If @Mod4 >= 366
	Select 	@Div1	= @Div1 +(@Mod4 - 366)/365 + 1,
		@Mod1	= (@Mod4 - 366) % 365 
Else
	Select 	@Mod1	= @Mod4
	
If @Mod1<(6*31) 
	Select	@Mon	= @Mod1 / 31 + 1,
		@Day	= (@Mod1 % 31) + 1
Else
	Select 	@Mon	= (@Mod1 -6 * 31) / 30 + 6 + 1,
		@Day	= (@Mod1 -6 * 31) % 30 + 1

Select	@Year		= @Div33 * 33 + @Div4 * 4 + @Div1 + 1,
	@FinallYear	= Cast(@Year as varchar(4)),
	@FinallMon 	= Replicate(''0'' , 2 - Len(@Mon)) + Cast(@Mon As varchar),
	@FinallDay 	= Replicate(''0'' , 2 - Len(@Day)) + Cast(@Day As varchar)

Return  (@FinallYear +''/''+ @FinallMon +''/''+ @FinallDay)

end
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Q_GetInstallments_Details_Preview]    Script Date: 07/17/2023 15:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_GetInstallments_Details_Preview]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Q_GetInstallments_Details_Preview]
(
    @guid VARCHAR(128)
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @rowcount INT, @error INT
		
						
SELECT  rownum  AS Radif,  InstallmentID, Mablagh, MabSood, MabTakhfif, MabDirkard,[MablaghGhest],
		DateSR, Sharh  
	FROM ##FileListHeaders
	WHERE ShSeryal=@guid

    /* commit changes and return 0 code indicating successful completion */
	RETURN 0
END
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[PMT]    Script Date: 07/17/2023 15:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PMT]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[PMT]
	(@Rate FLOAT
	,@Periods INT
	,@Present FLOAT
	,@Future FLOAT
	,@Type INT)  
RETURNS FLOAT
    BEGIN   
	    SET @Type = ISNULL(@Type, 0);
		SET @Future = ISNULL(@Future, 0);
		DECLARE @Result AS FLOAT = 0;
		DECLARE @Term AS FLOAT = 0;

		IF @Rate=0
		BEGIN
			SET @Result=(@Present+@Future)/@Periods;
		END /* IF @Rate=0 */
		ELSE
		BEGIN /* IF @Rate <> 0 */
			SET @term = POWER(1+@rate, @periods);
			
			IF @Type=1
			BEGIN
				SET @Result = (@Future*@Rate / (@Term-1) + @Present*@Rate / (1-1/@Term)) / (1+@Rate)
			END
			
			ELSE
			BEGIN
				SET @Result = @Future*@Rate/(@term-1)+@Present*@Rate / (1-1/@Term)
			END

		END /* IF @Rate <> 0 */

        RETURN -@Result  
    END;
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[PersianToGregorian]    Script Date: 07/17/2023 15:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PersianToGregorian]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
execute dbo.sp_executesql @statement = N' CREATE FUNCTION [dbo].[PersianToGregorian](@DateStr varchar(10)) RETURNS DATEtime AS  BEGIN    declare @YYear int    declare @MMonth int    declare @DDay int    declare @epbase int    declare @epyear int    declare @mdays int    declare @persian_jdn int    declare @i int    declare @j int    declare @l int    declare @n int    declare @TMPRESULT varchar(10)    declare @IsValideDate int    declare @TempStr varchar(20)    DECLARE @TmpDateStr varchar(10)     SET @i=charindex(''/'',@DateStr)     IF LEN(@DateStr) - CHARINDEX(''/'', @DateStr,CHARINDEX(''/'', @DateStr,1)+1) = 4    BEGIN    /* SET @TmpDateStr = dbo.ReversDate(@DateStr)*/      IF ( ISDATE(@TmpDateStr) =1 )         RETURN @TmpDateStr      ELSE         RETURN NULL    END    ELSE      SET @TmpDateStr = @DateStr     IF ((@i<>0) and      /* (dbo.SubStrCount(''/'', @TmpDateStr)=2) and*/         (ISNUMERIC(REPLACE(@TmpDateStr,''/'',''''))=1) and         (charindex(''.'',@TmpDateStr)=0)        )    BEGIN        SET @YYear=CAST(SUBSTRING(@TmpDateStr,1,@i-1) AS INT)                 IF ( @YYear< 1300 )                       SET @YYear =@YYear + 1300                 IF @YYear > 9999                   RETURN NULL            SET @TempStr= SUBSTRING(@TmpDateStr,@i+1,Len(@TmpDateStr))            SET @i=charindex(''/'',@TempStr)        SET @MMonth=CAST(SUBSTRING(@TempStr,1,@i-1) AS INT)        SET @MMonth=@MMonth /* -1*/               SET @TempStr= SUBSTRING(@TempStr,@i+1,Len(@TempStr))              SET @DDay=CAST(@TempStr AS INT)        SET @DDay=@DDay                             IF ( @YYear >= 0 )                      SET @epbase = @YYear - 474                  Else                      SET @epbase = @YYear - 473                  SET @epyear = 474 + (@epbase % 2820)          IF (@MMonth <= 7 )                        SET @mdays = ((@MMonth) - 1) * 31         Else             SET @mdays = ((@MMonth) - 1) * 30 + 6          SET @persian_jdn =(@DDay)  + @mdays + CAST((((@epyear * 682) - 110) / 2816) as int)  + (@epyear - 1) * 365  +  CAST((@epbase / 2820)  as int ) * 1029983  + (1948321 - 1)          IF (@persian_jdn > 2299160)                  BEGIN             SET @l = @persian_jdn + 68569             SET @n = CAST(((4 * @l) / 146097) as int)             SET @l = @l -  CAST(((146097 * @n + 3) / 4) as int)             SET @i =  CAST(((4000 * (@l + 1)) / 1461001) as int)             SET @l = @l - CAST( ((1461 * @i) / 4) as int) + 31             SET @j =  CAST(((80 * @l) / 2447) as int)             SET @DDay = @l - CAST( ((2447 * @j) / 80) as int)              SET @l =  CAST((@j / 11) as int)             SET @MMonth = @j + 2 - 12 * @l             SET @YYear = 100 * (@n - 49) + @i + @l                  END             SET @TMPRESULT=Cast(@MMonth as varchar(2))+''/''+CAST(@DDay as Varchar(2))+''/''+CAST(@YYear as varchar(4))             RETURN Cast(@TMPRESULT as DATETIME)      END     RETURN NULL       END   
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[sgfn_DateToShamsiDatePart]    Script Date: 07/17/2023 15:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sgfn_DateToShamsiDatePart]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
execute dbo.sp_executesql @statement = N'  Create FUNCTION [dbo].[sgfn_DateToShamsiDatePart] (@MiDate DateTime , @ADatePart CHAR) RETURNS INT AS BEGIN    Declare @TmpY int, @Leap int    Declare @Sh_Y int , @Sh_M int , @Sh_D int, @Result int    if @MiDate is null       return 0     Set @Result = convert(int, convert(float,@MiDate))    if @Result <= 78    begin      Set @Sh_Y = 1278      Set @Sh_M = (@Result + 10) / 30 + 10       Set @Sh_D = (@Result + 10) % 30 + 1    end     else    begin      Set @Result = @Result - 78      Set @Sh_Y = 1279      while 1 = 1       begin        Set @TmpY = @Sh_Y + 11        Set @TmpY = @TmpY - ( @TmpY / 33) * 33        if  (@TmpY <> 32) and ( (@TmpY / 4) * 4 = @TmpY )          Set @Leap = 1        else          Set @Leap = 0        if @Result <= (365+@Leap)          break        Set @Result = @Result -  (365+@Leap)        Set @Sh_Y = @Sh_Y + 1      end      if @Result <= 31*6      begin        Set @Sh_M = (@Result-1) / 31 + 1        Set @Sh_D = (@Result-1) % 31 + 1      end      else      begin        Set @Sh_M = ((@Result-1) - 31*6) / 30 + 7        Set @Sh_D = ((@Result-1) - 31*6) % 30 + 1      end    end    return      case @ADatePart        when ''Y'' then  @Sh_Y        when ''M'' then @Sh_M        when ''D'' then @Sh_D      else  0      end  end 
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[sgfn_DateToShamsiDate]    Script Date: 07/17/2023 15:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sgfn_DateToShamsiDate]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
execute dbo.sp_executesql @statement = N'  CREATE FUNCTION [dbo].[sgfn_DateToShamsiDate](@ChirsDate SmallDateTime) returns Char(10)  as  begin    declare @SolarDate char(10)    declare @Day  Char(2)    declare @Mon Char(2)    declare @SDay  Int    declare @SMon Int    declare @SYear Int    set @SYear = dbo.sgfn_DateToShamsiDatePart(@ChirsDate, ''Y'')    set @SMon = dbo.sgfn_DateToShamsiDatePart(@ChirsDate, ''M'')    set @SDay = dbo.sgfn_DateToShamsiDatePart(@ChirsDate, ''D'')     if @SMon <= 9      select @Mon = ''0''+Convert(Char(1),@SMon)    else      select @Mon = Convert(Char(2),@SMon)    if @SDay <= 9      select @Day = ''0''+Convert(Char(1),@SDay)    else      select @Day = Convert(Char(2),@SDay)    select  @SolarDate = SubString(Convert(Char(4),@SYear),1,4)+''/''+@Mon+''/''+@Day    return @SolarDate  end 
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[IPMT]    Script Date: 07/17/2023 15:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[IPMT]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[IPMT]
	(@Rate FLOAT
	,@Period INT
	,@Periods INT
	,@Present FLOAT
	,@Future FLOAT
	,@Type INT)  
RETURNS FLOAT
    BEGIN   
	    SET @Type = ISNULL(@Type,0);
		DECLARE @Payment AS FLOAT = (SELECT dbo.PMT(@Rate,@Periods,@Present,@Future,@Type))
		DECLARE @Interest AS FLOAT = 0.0;
		
		IF @Period=1
		BEGIN
			IF @Type=1
			BEGIN
				SET @Interest=0
			END /* IF @Type=1 */
			ELSE
			BEGIN
				SET @Interest=-@Present
			END 
		END /* IF @Period=1 */
		ELSE /* IF @Period <> 1 */
		BEGIN
			IF @Type=1
			BEGIN
				SET @Interest=dbo.FV(@Rate,@Period-2,@Payment,@Present,1)-@Payment
			END /* IF @Type=1 */
			ELSE /* IF @Type<>1 */
			BEGIN
				SET @Interest = dbo.FV(@Rate,@Period-1,@Payment,@Present,0)
			END /*  IF @Type<>1 */
		END /* IF @Period <> 1 */
		
        RETURN @Interest*@Rate
    END;
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Q_SaveInstallments]    Script Date: 07/17/2023 15:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_SaveInstallments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[Q_SaveInstallments]
(
    @userid INT,
    @InstallmentID INT = NULL output,
    @InstallmentNumber nvarchar(50) = NULL,
    @VSabt int = NULL,
    @HESABID int = NULL,
    @THESABID int = NULL,
    @Mablagh decimal(19,4) = NULL,
    @MablaghPish decimal(19,4) = NULL,
    @SharhTazmin nvarchar(255) = NULL,
    @DarSad decimal(19,4) = NULL,
    @ShSeryal nvarchar(255) = NULL,
    @DARID2 int = NULL,
    @DateStart nvarchar(255) = NULL,
    @DateSabt nvarchar(255) = NULL,
    @Sharh nvarchar(255) = NULL,
    @ZamenName nvarchar(255) = NULL	,
    @ZamenPhone nvarchar(255) = NULL	,
    @ZamenAddress nvarchar(255) = NULL	
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @rowcount INT, @error INT, @id INT

    /* start transaction */
	BEGIN TRANSACTION

    /*  check if the specified record already exists, if yes, update it, if no, create it */
    IF NOT EXISTS 
    (    
         SELECT * 
         FROM [dbo].[Installments]
         WHERE  InstallmentID = @InstallmentID
    )
    BEGIN 
         /* insert new record */
         INSERT INTO [dbo].[Installments]
         (
             InstallmentNumber,
             DateCreate,
             VSabt,
             HESABID,
             THESABID,
             Mablagh,
             MablaghPish,
             SharhTazmin,
             DarSad,
             ShSeryal,
             DARID2,
             DateStart,
             DateSabt,
             Sharh,
             ZamenName,
             ZamenPhone,
              ZamenAddress
         )
         VALUES 
         (
             @InstallmentNumber,
             GETDATE(),
             @VSabt,
             @HESABID,
             @THESABID,
             @Mablagh,
             @MablaghPish,
             @SharhTazmin,
             @DarSad,
             @ShSeryal,
             @DARID2,
             @DateStart,
             @DateSabt,
             @Sharh,
             @ZamenName,
             @ZamenPhone, 
             @ZamenAddress
         )
         SET @InstallmentID=SCOPE_IDENTITY()
    END 
    ELSE
    BEGIN
         /* update existing record */
         UPDATE [dbo].[Installments]
         SET InstallmentNumber = @InstallmentNumber,
             DateModified = GETDATE(),
             VSabt = @VSabt,
             HESABID = @HESABID,
             THESABID = @THESABID,
             Mablagh = @Mablagh,
             MablaghPish = @MablaghPish,
             SharhTazmin = @SharhTazmin,
             DarSad = @DarSad,
             ShSeryal = @ShSeryal,
             DARID2 = @DARID2,
             DateStart = @DateStart,
             DateSabt = @DateSabt,
             Sharh = @Sharh,
             ZamenName = @ZamenName,
             ZamenPhone = @ZamenPhone,
             ZamenAddress = @ZamenAddress
         WHERE  InstallmentID = @InstallmentID
    END

    /* capture operation completion code and number of records affected */
	SELECT @rowcount = @@ROWCOUNT, 
           @error = @@ERROR,
           @id = SCOPE_IDENTITY()

	IF @error != 0
    BEGIN
        /* cancel transaction, undo changes */
        ROLLBACK TRANSACTION

		/* report error and exit with non-zero exit code */
        RAISERROR(''Unable to update or insert new record. See previous message for details.'', 16, 1) 
		RETURN @error
    END

    /* commit changes and return 0 code indicating successful completion */
    COMMIT TRANSACTION

	/* if operation type ''Add record'', return result set with the last inserted column value  */
    RETURN 0
END
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[GetInstallmentPayment]    Script Date: 07/17/2023 15:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GetInstallmentPayment]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[GetInstallmentPayment]
(
	@InstallmentID   INT,
	@rownum      INT
)
RETURNS TABLE 
AS
RETURN(
	SELECT dvt.Mablagh, dvt.MabTakhfif, 0 AS MabEzafePardakht, dvt.DateVosool,total,
			dvt.Rownum
	FROM
	(
		SELECT ROW_NUMBER() OVER (ORDER BY ISNULL(DateVosool,''99999999''),InstsubPayID) AS Rownum ,tsub.InstsubPayID,
		       tsub.Mablagh, tsub.MabTakhfif, tsub.MabEzafePardakht,
		       tsub.DateVosool,total
		  FROM(
		SELECT InstsubPayID, Mablagh, MabTakhfif, MabEzafePardakht,ISNULL(Mablagh,0)+ ISNULL(MabTakhfif,0)+ ISNULL(MabEzafePardakht,0) AS total, DateVosool
		FROM            Installments_DetailPayment
		WHERE        (InstallmentID = @InstallmentID)
		
	UNION
SELECT        NULL AS Expr1, SUM(Mablagh) AS MablaghGhest, 0 AS Expr2, 0 AS Expr4,SUM(Mablagh) AS Total, MIN(DateSR) AS Expr3
FROM            Installments_Detail
WHERE        (InstallmentID = @InstallmentID) AND (FlagPasManuelly = 1)		
		)AS tsub) AS dvt
	WHERE dvt.Rownum=@rownum AND dvt.total>0  )
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[GetInstallmentAdditionalPayment]    Script Date: 07/17/2023 15:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GetInstallmentAdditionalPayment]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
execute dbo.sp_executesql @statement = N'

CREATE FUNCTION [dbo].[GetInstallmentAdditionalPayment]
(
	@InstallmentID   INT,
	@DateVosool NVARCHAR(50)
)
RETURNS DECIMAL(19,4) 
AS
BEGIN
	
	DECLARE @Mablagh DECIMAL(19,4)
	
	SELECT   @Mablagh=  SUM(MabEzafePardakht)
	FROM            Installments_DetailPayment
	WHERE InstallmentID=@InstallmentID

	RETURN ISNULL(@Mablagh,0)

END

' 
END
GO
/****** Object:  StoredProcedure [dbo].[Q_GetInstallments_Print]    Script Date: 07/17/2023 15:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_GetInstallments_Print]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE PROCEDURE [dbo].[Q_GetInstallments_Print]
(
    @userid INT,
    @InstallmentID INT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @rowcount INT, @error INT


		/* search and return records */
SELECT        Installments_Detail.RowNum AS Radif, Installments.InstallmentID, Installments.InstallmentNumber, Installments.VSabt, Installments.HESABID, Installments.THESABID, Installments_Detail.Mablaghinline AS Mablagh, 
                         Installments.MablaghPish, Installments.SharhTazmin, Installments.DarSad, Installments.ShSeryal, Installments.DARID2, Installments.DateStart, Installments.DateSabt, Installments.Sharh, 
                         Installments.ZamenName AS NameHesabZamen, Installments.FieldN1, Installments.FieldN2, Installments.FieldN3, Installments.FieldN4, Installments.FieldT1, Installments.Flag1, Installments.Flag2, T_T_Heasb.NameHesabT, 
                         T_T_Heasb.Address, T_T_Heasb.Mob, T_T_Heasb.Phone1, T_T_Heasb.Phone2, Installments.ZamenAddress, Installments.ZamenPhone, Installments_Detail.InstallmentsubID AS InstallmentCnt, 
                         Installments_Detail.DateSR AS InstallmentLastDate, T_T_Heasb.GOID, T_T_Heasb.KolID, Installments_Detail.InstallmentsubID, Installments_Detail.FlagPas, Installments_Detail.FlagFromoldyear, 
                         Installments_Detail.MabDirkard, Installments_Detail.MabSoodinline AS MabSood, Installments_Detail.MabEzafePardakht, Installments_Detail.FlagPasManuelly, Installments_Detail.DateVosool, Installments_Detail.Balance, 
                         Installments_Detail.Mabpayment, T_T_Heasb.Address2, T_T_Heasb.Note, T_T_Heasb.Shahr, T_T_Heasb.Telex, T_T_Heasb.Ostan, T_T_Heasb.CodPosti, T_T_Heasb.CodEghtesdi, T_T_Heasb.Keshvar, T_T_Heasb.BankHesab, 
                         T_T_Heasb.Websit, T_T_Heasb.Email, T_T_Heasb.CodeMelli, derivedtbl_1.sumMabSoodAndghest, derivedtbl_1.sumMabSood, derivedtbl_1.sumMablaghGhest, Installments_Detail.MablaghGhest
FROM            Installments INNER JOIN
                         T_T_Heasb ON Installments.THESABID = T_T_Heasb.THESABID LEFT OUTER JOIN
                             (SELECT        InstallmentID, SUM(MabSood) AS sumMabSood, SUM(MablaghGhest) AS sumMablaghGhest, SUM(MablaghGhest + MabSood) AS sumMabSoodAndghest
                                FROM            Installments_Detail AS Installments_Detail_1
                                GROUP BY InstallmentID) AS derivedtbl_1 ON Installments.InstallmentID = derivedtbl_1.InstallmentID LEFT OUTER JOIN
                         Installments_Detail ON Installments.InstallmentID = Installments_Detail.InstallmentID
WHERE        (Installments.InstallmentID = @InstallmentID) AND (Installments_Detail.FlagActive = 1)
ORDER BY Radif

     /* capture operation completion code and number of records affected */
	SELECT @rowcount = @@ROWCOUNT, 
           @error = @@ERROR

	IF @error != 0
    BEGIN
		/* report error and exit with non-zero exit code */
        RAISERROR(''Unable to retrieve records. See previous message for details.'', 16, 1) 
		RETURN @error
    END
    /* commit changes and return 0 code indicating successful completion */
	RETURN 0
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Q_ChangeInstallments_DetailRowStatus]    Script Date: 07/17/2023 15:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_ChangeInstallments_DetailRowStatus]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE PROCEDURE [dbo].[Q_ChangeInstallments_DetailRowStatus]
(
    @InstallmentsubID INT = NULL 
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @FlagPasManuelly BIT


    /* check if the specified record already exists, if yes, update it, if no, create it */
         SELECT @FlagPasManuelly=FlagPasManuelly 
         FROM [dbo].Installments_Detail 
         WHERE InstallmentsubID = @InstallmentsubID
        
        IF(ISNULL(@FlagPasManuelly,0)=0)
			SET @FlagPasManuelly=1
        ELSE
			SET @FlagPasManuelly=0
			
         /* update existing record */
         UPDATE [dbo].Installments_Detail
         SET FlagPasManuelly =  @FlagPasManuelly
         WHERE InstallmentsubID = @InstallmentsubID

	/* if operation type ''Add record'', return result set with the last inserted column value  */
    RETURN 0
END
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[PPMT]    Script Date: 07/17/2023 15:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PPMT]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[PPMT]
	(@Rate FLOAT
	,@Period INT
	,@Periods INT
	,@Present FLOAT
	,@Future FLOAT
	,@Type INT)  
RETURNS FLOAT
    BEGIN   
        RETURN dbo.PMT(@Rate,@Periods,@Present,@Future,@Type) - dbo.IPMT(@Rate,@Period,@Periods,@Present,@Future,@Type)
    END;
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Q_GetInstallments_Details_Payments]    Script Date: 07/17/2023 15:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_GetInstallments_Details_Payments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[Q_GetInstallments_Details_Payments]
(
    @userid INT,
    @InstallmentID INT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @rowcount INT, @error INT
		
SELECT    ROW_NUMBER() OVER (ORDER BY Installments_DetailPayment.InstsubPayID) AS Rownum,    Installments.InstallmentID, Installments.InstallmentNumber, Installments.VSabt, Installments.HESABID, Installments.THESABID, Installments.MablaghPish, Installments.SharhTazmin, Installments.DarSad, 
                         Installments.ShSeryal, Installments.DARID2, Installments.DateStart, Installments.DateSabt, Installments.ZamenName AS NameHesabZamen, Installments.FieldN1, Installments.FieldN2, Installments.FieldN3, 
                         Installments.FieldN4, Installments.FieldT1, Installments.Flag1, Installments.Flag2, T_T_Heasb.NameHesabT, T_T_Heasb.Address, T_T_Heasb.Mob, T_T_Heasb.Phone1, T_T_Heasb.Phone2, Installments.ZamenAddress, 
                         Installments.ZamenPhone, T_T_Heasb.GOID, T_T_Heasb.KolID, Installments_DetailPayment.Mablagh + ISNULL(Installments_DetailPayment.MabTakhfif, 0) AS Mablagh, Installments_DetailPayment.MabTakhfif, 
                         Installments_DetailPayment.MabEzafePardakht, Installments_DetailPayment.DateVosool, Installments_DetailPayment.Sharh, Installments_DetailPayment.InstsubPayID, T_SA2.SAID2Number, T_Dar1.DARID1
FROM            T_SA2 INNER JOIN
                         T_Dar1 ON T_SA2.SAID2 = T_Dar1.PrimarySaid2 RIGHT OUTER JOIN
                         Installments INNER JOIN
                         T_T_Heasb ON Installments.THESABID = T_T_Heasb.THESABID INNER JOIN
                         Installments_DetailPayment ON Installments.InstallmentID = Installments_DetailPayment.InstallmentID ON T_Dar1.InstsubPayIDInstallment = Installments_DetailPayment.InstsubPayID
WHERE        (Installments.InstallmentID = @InstallmentID)
ORDER BY Installments_DetailPayment.InstsubPayID

/* capture operation completion code and number of records affected */
	SELECT @rowcount = @@ROWCOUNT, 
           @error = @@ERROR

	IF @error != 0
    BEGIN
		/* report error and exit with non-zero exit code
        RAISERROR(''Unable to retrieve records. See previous message for details.'', 16, 1)  */
		RETURN @error
    END
    /*  commit changes and return 0 code indicating successful completion */
	RETURN 0
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Q_GetInstallments_Details]    Script Date: 07/17/2023 15:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_GetInstallments_Details]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Q_GetInstallments_Details]
(
    @userid INT,
    @InstallmentID INT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @rowcount INT, @error INT
		
SELECT        Installments_Detail.RowNum AS Radif, Installments.InstallmentID, Installments.InstallmentNumber, Installments.VSabt, Installments.HESABID, Installments.THESABID, Installments_Detail.Mablaghinline AS Mablagh, 
                         Installments.MablaghPish, Installments.SharhTazmin, Installments.DarSad, Installments.ShSeryal, Installments.DARID2, Installments.DateStart, Installments.DateSabt, Installments.Sharh, 
                         Installments.ZamenName AS NameHesabZamen, Installments.FieldN1, Installments.FieldN2, Installments.FieldN3, Installments.FieldN4, Installments.FieldT1, Installments.Flag1, Installments.Flag2, T_T_Heasb.NameHesabT, 
                         T_T_Heasb.Address, T_T_Heasb.Mob, T_T_Heasb.Phone1, T_T_Heasb.Phone2, Installments.ZamenAddress, Installments.ZamenPhone, Installments_Detail.InstallmentsubID AS InstallmentCnt, 
                         Installments_Detail.DateSR AS InstallmentLastDate, T_T_Heasb.GOID, T_T_Heasb.KolID, Installments_Detail.InstallmentsubID, Installments_Detail.FlagPas, Installments_Detail.FlagFromoldyear, 
                         Installments_Detail.MabDirkard, Installments_Detail.MabSoodinline AS MabSood, Installments_Detail.MabEzafePardakht, Installments_Detail.FlagPasManuelly, Installments_Detail.MablaghGhestinline AS MablaghGhest, 
                         Installments_Detail.DateVosool, Installments_Detail.Balance, Installments_Detail.Mabpayment
FROM            Installments INNER JOIN
                         T_T_Heasb ON Installments.THESABID = T_T_Heasb.THESABID LEFT OUTER JOIN
                         Installments_Detail ON Installments.InstallmentID = Installments_Detail.InstallmentID
WHERE        (Installments.InstallmentID = @InstallmentID) AND (Installments_Detail.FlagActive = 1)
ORDER BY Radif

/* capture operation completion code and number of records affected */
	SELECT @rowcount = @@ROWCOUNT, 
           @error = @@ERROR

	IF @error != 0
    BEGIN
		/*  report error and exit with non-zero exit code */
        RAISERROR(''Unable to retrieve records. See previous message for details.'', 16, 1) 
		RETURN @error
    END
    /* commit changes and return 0 code indicating successful completion */
	RETURN 0
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Q_GetInstallments]    Script Date: 07/17/2023 15:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_GetInstallments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE PROCEDURE [dbo].[Q_GetInstallments]
(
    @userid INT,
    @InstallmentID INT,
    @StartDateList NVARCHAR(50),
    @EndDateList NVARCHAR(50),
    @showList BIT=1
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @rowcount INT, @error INT


	IF(@showList=0)
		/* search and return records */
SELECT        Installments.InstallmentID, Installments.InstallmentNumber, Installments.DateCreate, Installments.DateModified, Installments.VSabt, Installments.HESABID, Installments.THESABID, Installments.Mablagh, 
                         Installments.MablaghPish, Installments.SharhTazmin, Installments.DarSad, Installments.ShSeryal, Installments.DARID2, Installments.DateStart, Installments.DateSabt, Installments.Sharh, Installments.ZamenName, 
                         Installments.FieldN1, Installments.FieldN2, Installments.FieldN3, Installments.FieldN4, Installments.FieldT1, Installments.Flag1, Installments.Flag2, Installments.ZamenAddress, Installments.ZamenPhone, T_T_Heasb.GOID, 
                         T_T_Heasb.KolID, T_T_Heasb.NameHesabT, T_Dar1.DARID1
FROM            Installments INNER JOIN
                         T_T_Heasb ON Installments.THESABID = T_T_Heasb.THESABID LEFT OUTER JOIN
                         T_Dar1 ON Installments.InstallmentID = T_Dar1.InstallmentID
WHERE        (Installments.InstallmentID = @InstallmentID)

ELSE
		
SELECT        Installments.InstallmentID, Installments.InstallmentNumber, Installments.DateCreate, Installments.DateModified, Installments.VSabt, Installments.HESABID, Installments.THESABID, Installments.Mablagh, 
                         Installments.MablaghPish, Installments.SharhTazmin, Installments.DarSad, Installments.ShSeryal, Installments.DARID2, Installments.DateStart, Installments.DateSabt, Installments.Sharh, 
                         Installments.ZamenName AS NameHesabZamen, Installments.FieldN1, Installments.FieldN2, Installments.FieldN3, Installments.FieldN4, Installments.FieldT1, Installments.Flag1, Installments.Flag2, T_T_Heasb.NameHesabT, 
                         T_T_Heasb.Address, T_T_Heasb.Mob, T_T_Heasb.Phone1, T_T_Heasb.Phone2, Installments.ZamenAddress, Installments.ZamenPhone, COUNT(Installments_Detail.InstallmentsubID) AS InstallmentCnt, 
                         MAX(Installments_Detail.DateSR) AS InstallmentLastDate, SUM(CAST(CASE WHEN FlagPas = 1 THEN 0 ELSE 1 END AS int)) AS CountnoPassed, 
                         SUM(CAST(CASE WHEN FlagPas = 1 THEN 0 ELSE Installments_Detail.Mablaghinline END AS money)) AS MabMandeAghasat
FROM            Installments INNER JOIN
                         T_T_Heasb ON Installments.THESABID = T_T_Heasb.THESABID LEFT OUTER JOIN
                         Installments_Detail ON Installments.InstallmentID = Installments_Detail.InstallmentID
WHERE        (Installments.DateSabt BETWEEN @StartDateList AND @EndDateList) OR
                         (Installments.DateStart BETWEEN @StartDateList AND @EndDateList)
GROUP BY Installments.InstallmentID, Installments.InstallmentNumber, Installments.DateCreate, Installments.DateModified, Installments.VSabt, Installments.HESABID, Installments.THESABID, Installments.Mablagh, 
                         Installments.MablaghPish, Installments.SharhTazmin, Installments.DarSad, Installments.ShSeryal, Installments.DARID2, Installments.DateStart, Installments.DateSabt, Installments.Sharh, Installments.ZamenName, 
                         Installments.FieldN1, Installments.FieldN2, Installments.FieldN3, Installments.FieldN4, Installments.FieldT1, T_T_Heasb.NameHesabT, T_T_Heasb.Address, T_T_Heasb.Mob, T_T_Heasb.Phone1, T_T_Heasb.Phone2, 
                         Installments.ZamenAddress, Installments.ZamenPhone, Installments.Flag1, Installments.Flag2                         
                                                                                                                             
     /* capture operation completion code and number of records affected */
	SELECT @rowcount = @@ROWCOUNT, 
           @error = @@ERROR

	IF @error != 0
    BEGIN
		/*  report error and exit with non-zero exit code */
        RAISERROR(''Unable to retrieve records. See previous message for details.'', 16, 1) 
		RETURN @error
    END
    /* commit changes and return 0 code indicating successful completion */
	RETURN 0
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Q_SaveInstallments_Details_Balance]    Script Date: 07/17/2023 15:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_SaveInstallments_Details_Balance]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[Q_SaveInstallments_Details_Balance]
(
    @InstallmentID INT       
)
AS
BEGIN
    SET NOCOUNT ON
    
   /* DECLARE @InstallmentID INT =8  */

DECLARE @DateVosool NVARCHAR(50),@DateSR NVARCHAR(50), @MabPardakhtiInline DECIMAL(19,4), @CalcMabGhest DECIMAL(19,4),@Mabsood DECIMAL(19,4), @MabPPmt  DECIMAL(19,4),@DarSad DECIMAL(19,4),@countofInstallment INT,
		@MabEzafePardakht  DECIMAL(19,4), @MandeTasviyehhesab  DECIMAL(19,4)=0,@rownum INT=1 ,@InstallmentsubID INT,@MabPmt DECIMAL(19,4),@Mabpayment DECIMAL(19,4),@flagPassed BIT=0;                           

	SELECT      @countofInstallment= COUNT(*) 
	FROM            Installments_Detail
	WHERE        (InstallmentID = @InstallmentID);

	SELECT @MandeTasviyehhesab=inst.Mablagh ,@DarSad=inst.DarSad FROM dbo.Installments AS inst
	WHERE inst.InstallmentID=@InstallmentID;

	SET @MabPmt =ROUND(dbo.PMT(((@DarSad/100)/12), @countofInstallment, -(@MandeTasviyehhesab),NULL,null),0)

/* پرکردن مقادیر پیشفرص برای ستون های محاسبات */

	PRINT N''پرکردن مقادیر پیشفرص برای ستون های محاسبات''
	PRINT N''@MabPmt=''+CAST(@MabPmt AS NVARCHAR(255))

	UPDATE dbo.Installments_Detail
	SET Mablaghinline = 0, 
	 MabSoodinline = Installments_Detail.MabSood,
	 MablaghGhestinline = Installments_Detail.MablaghGhest, 
	 Balance = 0,
	 Mabpayment =0 ,
	 FlagPas = 0
	WHERE InstallmentID=@InstallmentID;    

/* استخراج اولین واریزی */
	SELECT @MabPardakhtiInline=dvt.Mablagh+ISNULL(dvt.MabTakhfif,0),@MabEzafePardakht= dvt.MabEzafePardakht,@DateVosool= dvt.DateVosool,@rownum= dvt.Rownum
	FROM
	dbo.GetInstallmentPayment(@InstallmentID, @rownum) AS dvt;	

	
	SET @MabEzafePardakht= dbo.GetInstallmentAdditionalPayment(@InstallmentID, @DateSR);

	PRINT N''استخراج اولین واریزی ''
	PRINT N''@MabPardakhtiInline=''+CAST(@MabPardakhtiInline AS NVARCHAR(255))
	PRINT N''@MabEzafePardakht=''+CAST(@MabEzafePardakht AS NVARCHAR(255))

DECLARE InstDetail_cursor CURSOR READ_ONLY FOR    
	SELECT       InstallmentsubID, Mablagh,DateSR,MabSood,MablaghGhest
	FROM            dbo.Installments_Detail
	WHERE        (InstallmentID = @InstallmentID)
	ORDER BY RowNum;
	  
OPEN InstDetail_cursor;
FETCH NEXT FROM InstDetail_cursor   
INTO  @InstallmentsubID, @CalcMabGhest,@DateSR,@Mabsood,@MabPPmt;

WHILE @@FETCH_STATUS = 0  
BEGIN  
	PRINT N''===========> fetch record instalment''+CAST(@InstallmentsubID AS NVARCHAR(255))

	/* قسمت مربوط به بروز رسانی مانده حساب تسویه وام و مبلغ هرقسط */
	
	IF(@MandeTasviyehhesab>=@MabPmt)
		SET @CalcMabGhest=@MabPmt
	ELSE IF(@MandeTasviyehhesab>0)
		BEGIN
			SET @MabPPmt=@MandeTasviyehhesab
			SET @CalcMabGhest=@MandeTasviyehhesab+@Mabsood
		END
	ELSE IF(@MandeTasviyehhesab<=0)
		BEGIN
		SET @CalcMabGhest=0
		SET @MabPPmt=0	
		SET @Mabsood=0

		END
		
		
		PRINT N''@CalcMabGhest''+CAST(@CalcMabGhest AS NVARCHAR(255)) 
			
	/* بررسی اینکه آیا پرداخت استخراجی سقف مبلغ قسط را پوشش می دهد یا خیر */
		IF((@MabPardakhtiInline)>=@CalcMabGhest)  
		BEGIN
			
			SET @Mabpayment=@CalcMabGhest						 
			SET @MandeTasviyehhesab=@MandeTasviyehhesab-(@mabppmt+@MabEzafePardakht)
			SET @MabPardakhtiInline=(@MabPardakhtiInline)-@CalcMabGhest						
			SET @flagPassed=1
			IF(@MandeTasviyehhesab<0)
				BEGIN
					SET @MandeTasviyehhesab=0
					/* SET @Mabsood=0 */
				END
			
		
		PRINT N''***(@MabPardakhtiInline)>=@CalcMabGhest**''
		PRINT N''@MandeTasviyehhesab''+CAST(@MandeTasviyehhesab AS NVARCHAR(255)) 
		PRINT N''@Mabpayment''+CAST(@Mabpayment AS NVARCHAR(255)) 
		PRINT N''@MabPardakhtiInline''+CAST(@MabPardakhtiInline AS NVARCHAR(255)) 

		END
		ELSE
		BEGIN
				DECLARE @TmpMab DECIMAL(19,4)=0;
				
				WHILE(@CalcMabGhest>@MabPardakhtiInline)
				BEGIN	
					PRINT N''***inner while**''
	
					/* SET @MabPardakhtiInline=0 */
			    	SET @rownum=@rownum+1
					
					SELECT @TmpMab=dvt.Mablagh+ISNULL(dvt.MabTakhfif,0),@DateVosool= dvt.DateVosool
					FROM
					dbo.GetInstallmentPayment(@InstallmentID, @rownum) AS dvt
					
					
					PRINT N''@rownum''+CAST(@rownum AS NVARCHAR(255)) 
					PRINT N''@TmpMab''+CAST(@TmpMab AS NVARCHAR(255)) 
					PRINT N''@MabPardakhtiInline''+CAST(@MabPardakhtiInline AS NVARCHAR(255)) 

					
					IF(ISNULL(@TmpMab,0)=0) 
						BREAK;
					
					SET @MabPardakhtiInline= @MabPardakhtiInline+ ISNULL(@TmpMab,0)	
					SET @TmpMab=0						
				END
				
				PRINT N''***-------end innerloop-------------**''
				
				
				IF((@MabPardakhtiInline)>=@CalcMabGhest)  
						BEGIN
							SET @Mabpayment=@CalcMabGhest
							SET @MandeTasviyehhesab=@MandeTasviyehhesab-(@mabppmt+@MabEzafePardakht)
							SET @MabPardakhtiInline=(@MabPardakhtiInline)-@CalcMabGhest
							SET @flagPassed=1
						END
					ELSE
					BEGIN
							SET @Mabpayment=@MabPardakhtiInline
							/* استثنا در زمانی که هنوز قسط سر رسید نشده یا مبلغ پرداختی برای پاس کردن قسط وجود ندارد */
							/* IF(@Mabpayment=0 AND @flagPassed=0)	*/							
							/*	SET @MandeTasviyehhesab=@MandeTasviyehhesab-(@mabppmt+@MabEzafePardakht) */
							/* ELSE */
							SET @MandeTasviyehhesab=@MandeTasviyehhesab-(@mabppmt+@MabEzafePardakht)
							SET @MabPardakhtiInline=0
							SET @flagPassed=0
							IF(@MandeTasviyehhesab<0)
								BEGIN
									SET @MandeTasviyehhesab=0
									/* SET @Mabsood=0 */
									/* SET @MabPPmt=0 */
								END
									
					END
					
					PRINT N''***befor update**''
					PRINT N''@MandeTasviyehhesab''+CAST(@MandeTasviyehhesab AS NVARCHAR(255)) 
					PRINT N''@Mabpayment''+CAST(@Mabpayment AS NVARCHAR(255)) 
					PRINT N''@MabPardakhtiInline''+CAST(@MabPardakhtiInline AS NVARCHAR(255)) 
				
			
		END		
		
		
	
	UPDATE dbo.Installments_Detail
	SET   [Balance]=@MandeTasviyehhesab,Mablaghinline = @CalcMabGhest,MablaghGhestinline =@MabPPmt,DateVosool = @DateVosool,Mabpayment = @Mabpayment,FlagPas = @flagPassed,MabSoodinline = @Mabsood,MabEzafePardakht = @MabEzafePardakht
	WHERE InstallmentsubID=@InstallmentsubID;    

	SET @MabEzafePardakht=0
	IF(@MabPardakhtiInline<=0)
		SET @DateVosool=NULL
	
    FETCH NEXT FROM InstDetail_cursor   
		INTO  @InstallmentsubID, @CalcMabGhest ,@DateSR,@Mabsood,@MabPPmt
END  
CLOSE InstDetail_cursor; 
DEALLOCATE InstDetail_cursor;  

    RETURN 0
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Q_SaveInstallments_Details]    Script Date: 07/17/2023 15:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_SaveInstallments_Details]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Q_SaveInstallments_Details]
(
    @userid INT,
    @InstallmentID INT = NULL output,
    @InstallmentNumber nvarchar(50) = NULL,
    @VSabt int = NULL,
    @HESABID int = NULL,
    @THESABID int = NULL,
    @Mablagh decimal(19,4) = NULL,
    @MablaghPish decimal(19,4) = NULL,
    @SharhTazmin nvarchar(255) = NULL,
    @DarSad decimal(19,4) = NULL,
    @ShSeryal nvarchar(255) = NULL,
    @DARID2 int = NULL,
    @DateStart nvarchar(255) = NULL,
    @DateSabt nvarchar(255) = NULL,
    @Sharh nvarchar(255) = NULL,
    @ZamenName nvarchar(255) = NULL	,
    @ZamenPhone nvarchar(255) = NULL	,
    @ZamenAddress nvarchar(255) = NULL	,
    @countofInstallment INT,
    @AddSoodInstraction INT,
    @addDateValue INT,
    @dateAddtype INT,
    @CalcOnTempTable BIT=0,
    @guid varchar(128) OUTPUT,
    @FOROSHID INT
    
    
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @rowcount INT, @error INT, @id INT,@tmpdbName nvarchar(128)

	IF(@CalcOnTempTable=1)
	BEGIN
		SET @guid=CAST( newid() AS VARCHAR(128))
		
		SET @tmpdbName=''tempdb..##FileListHeaders''
	IF (OBJECT_ID(''tempdb..##FileListHeaders'') IS NOT NULL )
		DROP TABLE ##FileListHeaders

	CREATE TABLE ##FileListHeaders (  
		
	[InstallmentsubID] [int] IDENTITY(1,1) NOT NULL,
	[InstallmentID] [int] NULL,
	[DateCreate] [smalldatetime] NULL,
	[DateModified] [smalldatetime] NULL,
	[Mablagh] [decimal](19, 4) NULL,
	[MablaghGhest] [decimal](19, 4) NULL,
	[MabSood] [decimal](19, 4) NULL,
	[MabTakhfif] [decimal](19, 4) NULL,
	[MabDirkard] [decimal](19, 4) NULL,
	[DARID2] [int] NULL,
	[DateSR] [nvarchar](255) NULL,
	[DateVosool] [nvarchar](255) NULL,
	[Sharh] [nvarchar](255) NULL,
	[ShSeryal] [nvarchar](255) NULL,
	[FieldN1] [int] NULL,
	[FieldN2] [int] NULL,
	[FieldN3] [int] NULL,
	[FieldN4] [int] NULL,
	[FieldT1] [nvarchar](255) NULL,
	[FlagPasManuelly] [bit] NULL,
	[FlagPas] [bit] NULL,
	[rownum] [INT] NULL,
	[FlagFromoldyear] [bit] NULL)
	
	END
	ELSE
	BEGIN
		         /*  insert new record */
         INSERT INTO [dbo].[Installments]
         (
             InstallmentNumber,
             DateCreate,
             VSabt,
             HESABID,
             THESABID,
             Mablagh,
             MablaghPish,
             SharhTazmin,
             DarSad,
             ShSeryal,
             DARID2,
             DateStart,
             DateSabt,
             Sharh,
             ZamenName,ZamenPhone, ZamenAddress
         )
         VALUES 
         (
             @InstallmentNumber,
             GETDATE(),
             @VSabt,
             @HESABID,
             @THESABID,
             @Mablagh,
             @MablaghPish,
             @SharhTazmin,
             @DarSad,
             @ShSeryal,
             @DARID2,
             @DateStart,
             @DateSabt,
             @Sharh,
             @ZamenName,@ZamenPhone, @ZamenAddress
         )
         SET @InstallmentID=SCOPE_IDENTITY()
         
         IF( (NOT (@FOROSHID IS NULL)))
			UPDATE       T_Forosh
			SET                InstallmentID = @InstallmentID
			WHERE        (FOROSHID = @FOROSHID)

	END

		 DECLARE @RoundMablagh DECIMAL(19,4), @Mablaghsat DECIMAL(19,4), @Mablaghsood DECIMAL(19,4), @MablaghAslghest DECIMAL(19,4)
				,@Roundsood DECIMAL(19,4), @MabSoodGhest DECIMAL(19,4),@DateSR nvarchar(255) ,@DateSRen DATETIME,@rownum INT=1
		/* محاسبه مبلغ سود براساس کل مبلغ  */		
		SET @Mablaghsood =((@Mablagh* @DarSad)/100)		
		/*  محاسبه مبلغ سود هرقسط براساس  تعداد کل اقساط*/
		/* SET @Roundsood=(@Mablaghsood%@countofInstallment)   رند کردن مبلغ سود */
		/* SET @MabSoodGhest=((@Mablaghsood-@Roundsood)/@countofInstallment) */
		
		/* درصورتی که بخواهیم مبلغ سود به کل مبلغ اضافه شودو سپس مبلغ اقساط محاسبه شود*/
		IF(@AddSoodInstraction=2)
		BEGIN
			SET @Mablagh= @Mablagh+@Mablaghsood
			SET @MabSoodGhest=ROUND(@Mablaghsood/@countofInstallment,0)		
		END
		
		/* محاسبه مبلغ هرقسط */
		SET @RoundMablagh= @Mablagh % @countofInstallment /*  این مبلغ در این حالت به قسط آخر اضافه میشود*/
		SET @Mablaghsat = ((@Mablagh-@RoundMablagh)/ @countofInstallment)
		
		/* در حالتی که بخواهیم مبلغ سود را براساس کل مبلغ حساب کنیم */
		/* وهمان مبلغ سود را به ازای هر قسط بصورت جداگانه اضافه کنیم*/
		IF(@AddSoodInstraction=1)
		BEGIN
			SET @Mablaghsat = @Mablaghsat+ROUND(@Mablaghsood,0)
			SET @MabSoodGhest=ROUND(@Mablaghsood,0)
		END

		 
		/* SET @DateSRen =[dbo].PersianToGregorian(@DateStart) */
		
		/* PRINT @DateSRen */
		/* PRINT dbo.Date_MD2SHD(@DateSRen) */
		/* SET @DateSRen= DATEADD(MONTH,1,@DateSRen) */
		/* PRINT @DateSRen */
		/* PRINT dbo.Date_MD2SHD(@DateSRen) */
		SET @DateStart=LEFT(@DateStart,4)+''/''+SUBSTRING(@DateStart,5,2)+''/''+RIGHT(@DateStart,2)
		
WHILE(@rownum<=@countofInstallment)
BEGIN
	

		/* محاسبه سود از مانده حساب ماهیانه*/
		IF(@AddSoodInstraction=3)
		BEGIN
			
			SET @RoundMablagh=0
			/* SET @MablaghTemp=@Mablagh-@MablaghTemp*/
			/* SET @Mablaghsood =ROUND(((@MablaghTemp* @DarSad	)/100),0) */
			/* SET @Mablaghsat = @Mablaghsat+@Mablaghsood */
			
			SET @Mablaghsat =ROUND(dbo.PMT(((@DarSad/100)/12), @countofInstallment, -(@Mablagh),NULL,null),0)
			SET @MabSoodGhest=ROUND(dbo.IPMT(((@DarSad/100)/12),@rownum,@countofInstallment,-(@Mablagh),NULL,NULL),0)		
			SET @MablaghAslghest=ROUND(dbo.PPMT(((@DarSad/100)/12),@rownum,@countofInstallment,-(@Mablagh),NULL,NULL),0)
		
		END
	
	/* افزودن رند حساب به مبلغ آخرین قسط*/
	IF(@countofInstallment=1)
	BEGIN
		SET @Mablaghsat = @Mablaghsat +@RoundMablagh		
	END
		

		IF(@CalcOnTempTable=1)
			INSERT INTO ##FileListHeaders
						([InstallmentID]
						,[DateCreate]
						,[Mablagh]
						,[MablaghGhest]
						,[MabDirkard]
						,[MabSood]
						,[DARID2]
						,[DateSR]
						,[DateVosool]
						,[Sharh]
						,[ShSeryal]
						,[FlagPas]
						,[FlagFromoldyear]
						,[rownum],
						FlagPasManuelly)
					VALUES
						(@InstallmentID
						,GETDATE()
						,@Mablaghsat
						,@MablaghAslghest
						,0
						,@MabSoodGhest
						,@DARID2
						,REPLACE(@DateStart, ''/'', '''') 
						,NULL
						,@Sharh
						,@guid
						,0
						,0
						,@rownum
						,0)
		ELSE
			INSERT INTO [dbo].[Installments_Detail]
						([InstallmentID]
						,[DateCreate]
						,[Mablagh]
						,[MablaghGhest]
						,[MabDirkard]
						,[MabSood]
						,[DARID2]
						,[DateSR]
						,[DateVosool]
						,[Sharh]
						,[ShSeryal]
						,[FlagPas]
						,[FlagFromoldyear]
						,RowNum,
						FlagActive)
					VALUES
						(@InstallmentID
						,GETDATE()
						,@Mablaghsat
						,@MablaghAslghest
						,0
						,@MabSoodGhest
						,@DARID2
						,REPLACE(@DateStart, ''/'', '''') 
						,NULL
						,@Sharh
						,@ShSeryal
						,0
						,0
						,@rownum,
						1)

 		SET @DateSRen =[dbo].PersianToGregorian(@DateStart)
		IF(@dateAddtype=3)  
			SET @DateSRen= DATEADD(day,@addDateValue,@DateSRen)
		ELSE
			SET @DateSRen= DATEADD(month,@addDateValue,@DateSRen)

		SET @DateStart= dbo.Date_MD2SHD(@DateSRen)	
		
		SET @rownum=@rownum+1

END


    /* capture operation completion code and number of records affected */
	SELECT @rowcount = @@ROWCOUNT, 
           @error = @@ERROR,
           @id = SCOPE_IDENTITY()

	IF @error != 0
    BEGIN

		/* report error and exit with non-zero exit code */
        RAISERROR(''Unable to update or insert new record. See previous message for details.'', 16, 1) 
		RETURN @error
    END

	/* if operation type ''Add record'', return result set with the last inserted column value 	*/		
    RETURN 0
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Q_DeleteInstallments]    Script Date: 07/17/2023 15:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Q_DeleteInstallments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
	CREATE PROCEDURE [dbo].[Q_DeleteInstallments]
	(
		@userid INT,
		@InstallmentID INT,
		@Errorchecking INT OUTPUT
	)
	AS
	BEGIN
		SET NOCOUNT ON
		DECLARE @rowcount INT, @error INT

		/* start transaction */
		/* BEGIN TRANSACTION */

		/*  delete record using the specified criteria, 1 record deletion is expected  */
   
	   SELECT     @rowcount =COUNT(*) 
		FROM            Installments_Detail
		WHERE        (InstallmentID = @InstallmentID) AND (FlagPas=1)


		SET @Errorchecking=@rowcount
		
		IF(@rowcount=0)
		 BEGIN
		   DELETE FROM [dbo].[Installments]
			 WHERE  InstallmentID = @InstallmentID
			 
		 		DELETE FROM T_SA2
					WHERE        (SAID2 IN (SELECT        PrimarySaid2
											FROM            T_Dar1
											WHERE        (T_Dar1.InstallmentID = @InstallmentID)))
					
				DELETE FROM [TRANSACTION]
				FROM            [TRANSACTION] INNER JOIN
										 T_Dar1 ON [TRANSACTION].DARID1 = T_Dar1.DARID1
				WHERE        (T_Dar1.InstallmentID = @InstallmentID)

				DELETE FROM T_Dar1		 
				WHERE        (T_Dar1.InstallmentID = @InstallmentID) 
		 		
		 END			 
		ELSE
			RETURN 1
		
		
		/* capture operation completion code and number of records affected */
		SELECT @rowcount = @@ROWCOUNT, 
			   @error = @@ERROR
	
		/* check for errors */
		IF @error != 0
		BEGIN
			/* cancel transaction, undo changes */
		   /*  ROLLBACK TRANSACTION */

			/* report error and exit with non-zero exit code
			RAISERROR(''Unable to delete record. See previous message for details.'', 16, 1) */
			RETURN @error
		END

		/* commit changes and return 0 code indicating successful completion */
	   /*  COMMIT TRANSACTION */
		RETURN 0
	END
' 
END
GO
IF NOT EXISTS(
    SELECT *
    FROM [T_RepGen] 
    WHERE [TypeID]= N'159')
BEGIN
	INSERT [dbo].[T_RepGen] ([RepID], [TypeID], [RepTitle], [FlagShow], [Description], [OrgDataBinary], [ChgDataBinary], [SelectFrom], [StrFrom], [WhereFiled], [GroupFileld], [HavingField], [OrderField], [FlagDef], [TypeRep], [FlagSendP], [FlagShowDlgPrn], [PrnCnt], [PrnName], [FlagCh], [RepStoredProc], [storedprocedPararmeter]) VALUES (151, N'159', N'چاپ قرارداد', 1, N'', NULL,  , N'', N'', N'', N'', N'', N'', NULL, NULL, 0, 0, NULL, NULL, N'1', N'Q_GetInstallments_Print', N'@Userid INT|@InstallmentID int')
END
GO





