Users

CREATE view [dbo].[UsersOverview]
as
select u.Name,u.UserName,a.AccountNumber as CashAccountNumber,c.Name as ClientName
from dbo.Users u
left join dbo.Accounts a on a.Id = u.CashAccountId
left join dbo.Clients c on c.Id = u.ClientId

GO

CREATE proc [dbo].[InsertUser]
(
	@Name nvarchar(400)
	,@UserName varchar(30)
	,@Password varchar(30)
	,@CashAccountId bigint = null
	,@ClientId bigint = null
	,@res bigint output
)
AS
begin
	set nocount on
	if(ISNULL(@Name,'')<>'')
	begin
		if exists(select * from Accounts where Id = @CashAccountId) or @CashAccountId is NULL
		and exists(select * from Clients where Id = @ClientId) or @ClientId is NULL
		begin
			insert into Users(Name,UserName,[Password],CashAccountId,ClientId)
			values (@Name,@UserName, @Password,@CashAccountId,@ClientId)

			set @res = @@ROWCOUNT
		end
		else
			Raiserror('Invalid CashAccountId or ClientId',16,1)
	end
	else
	begin
		Raiserror('Name is null!',16,1)
	end

	set nocount off
end
GO
create procedure UpdateUser
(
	@Id bigint,
	@Name nvarchar(400)
	,@UserName varchar(30)
	,@Password varchar(30)
	,@CashAccountId bigint = null
	,@ClientId bigint	= null
)
as
BEGIN
	set nocount on
	if(ISNULL(@Name,'')<>'')
	begin
		update Users
		set Name=ISNULL(@Name,Name)
			,UserName = ISNULL(@UserName,UserName)
			,[Password] = ISNULL(@Password,[Password])
			,CashAccountId = ISNULL(@CashAccountId,CashAccountId)
			,ClientId = ISNULL(@ClientId,ClientId)
		where Id = @Id
	end
	else
	begin
		Raiserror('Name is null!',16,1)
	end

	set nocount off
END

Clients
CREATE view dbo.ClientsOverview
as
select Name
	,case when [Type] = 0 then 'Individual' else 'Organization' end as [Type]
	,IdentificationNumber
	,[Address]
	,Tel
	,Mob
	,Email
	,Web
from dbo.Clients

GO

create procedure InsertClient
(
	@Name nvarchar(400)
	,@Type bit
	,@IdentificationNumber varchar(36)
	,@Address varchar(100)
	,@Tel varchar(30)
	,@Mob varchar(30)
	,@Email varchar(30)
	,@Web varchar(100)
)
AS
begin
	set nocount on

	insert into Clients(Name,[Type],IdentificationNumber,[Address],Tel,Mob,Email,Web)
	values (@Name,@Type, @IdentificationNumber,@Address,@Tel,@Mob,@Email,@Web)

	set nocount off
end

GO



create procedure UpdateClient
(
	@Id bigint
	,@Name nvarchar(400)
	,@Type bit
	,@IdentificationNumber varchar(36)
	,@Address varchar(100)
	,@Tel varchar(30)
	,@Mob varchar(30)
	,@Email varchar(30)
	,@Web varchar(100)
)
as
BEGIN
	set nocount on

	update Clients
	set Name=ISNULL(@Name,Name)
		,[Type] = ISNULL(@Type,[Type])
		,IdentificationNumber = ISNULL(@IdentificationNumber,IdentificationNumber)
		,[Address] = ISNULL(@Address,[Address])
		,Tel = ISNULL(@Tel,Tel)
		,Mob = ISNULL(@Mob,Mob)
		,Email = ISNULL(@Email,Email)
		,Web = ISNULL(@Web,Web)
	where Id = @Id

	set nocount off
END

Currencies
CREATE view dbo.CurrenciesOverview
as
select Currency, Name, [Image], Symbol
from dbo.Currencies

GO

create procedure InsertCurrencies
(
	@Currency varchar(3)
	,@Name varchar(30)
	,@Image varbinary(8000)
	,@Symbol nvarchar(10)
)
AS
begin
	set nocount on

	if(ISNULL(@Currency,'')<>'')
	begin
		if exists(select * from Currencies where Currency = @Currency)
		begin
			Raiserror('Currency already exists!',16,1)
		end
		else
		begin
			insert into Currencies(Currency,Name,[Image],Symbol)
			values (@Currency,@Name, @Image,@Symbol)
		end
	end
	else
		Raiserror('Invalid Currency!',16,1)

	set nocount off
end

GO

create procedure UpdateCurrencies
(
	@Currency varchar(3)
	,@Name varchar(30)
	,@Image varbinary(8000)
	,@Symbol nvarchar(10)
)
as
BEGIN
	set nocount on

	update Currencies
	set Name = ISNULL(@Name,Name)
		,[Image] = ISNULL(@Image,[Image])
		,Symbol = ISNULL(@Symbol,Symbol)
	where Currency = @Currency

	set nocount off
END


AccountClasses
CREATE view dbo.AccountClassesOverview
as
select Name
	,[Description]
	,case when [Type] = 1 then 'Cash Account'
		when [Type] = 2 then 'Current Account'
		when [Type]=3 then 'Deposit Account'
		else 'Credit Account'
	end as [Type]
from dbo.AccountClasses

GO

create procedure InsertAccountClasses
(
	@Name varchar(30)
	,@Description nvarchar(200)
	,@Type int
)
AS
begin
	set nocount on

	if(@Type in (1,2,3,4))
	begin
		if exists(select * from AccountClasses where [Type] = @Type)
		begin
			Raiserror('Account class exists!',16,1)
		end
		else
		begin
			insert into AccountClasses(Name,[Description],[Type])
			values (@Name,@Description, @Type)
		end
	end
	else
		Raiserror('Invalid Type value!',16,1)

	set nocount off
end

GO

create procedure UpdateAccountClasses
(
	@Id bigint
	,@Name varchar(30)
	,@Description nvarchar(200)
	,@Type int
)
as
BEGIN
	set nocount on

	if(@Type in (1,2,3,4))
	begin
		if exists(select * from AccountClasses where [Type] = @Type and Id<>@Id)
		begin
			Raiserror('Account class exists!',16,1)
		end
		else
		begin
			update AccountClasses
			set Name = ISNULL(@Name,Name)
				,[Description] = ISNULL(@Description,[Description])
				,[Type] = ISNULL(@Type,[Type])
			where Id = @Id
		end
	end
	else
		Raiserror('Invalid Type value!',16,1)



	set nocount off
END

AccountTypes
CREATE view dbo.AccountTypesOverview
as
select ac.Name as AccountClass, at.Name,at.[Description]
from dbo.AccountTypes at
left join dbo.AccountClasses ac on ac.Id = at.ClassId

GO

create procedure UpdateAccountTypes
(
	@Id bigint
	,@ClassId bigint = null
	,@Description nvarchar(200)
	,@Name varchar(30)
)
as
BEGIN
	set nocount on

	update AccountTypes
	set Name = ISNULL(@Name,Name)
		,[Description] = ISNULL(@Description,[Description])
		,ClassId = ISNULL(@ClassId,ClassId)
	where Id = @Id

	set nocount off
END

GO

create procedure InsertAccountTypes
(
	@ClassId bigint = null
	,@Description nvarchar(200)
	,@Name varchar(30)
)
AS
begin
	set nocount on

	insert into AccountTypes(Name,[Description],ClassId)
	values (@Name,@Description, @ClassId)

	set nocount off
end


Accounts
CREATE view dbo.AccountsOverview
as
select at.Name as AccountType, a.AccountNumber, a.Currency, c.Name as Client,
	case when a.[Status] = 'A' then 'Active' when a.[Status] = 'C' then 'Closed' when a.[Status] = 'O' then 'Opened' else NULL end as [Status],
	a.Blocked, a.Balance, a.OverdraftLimit, a.AmountOnHold, a.AvailableBalance
from dbo.Accounts a
left join dbo.AccountTypes at on at.Id = a.TypeId
left join dbo.Clients c on c.Id = a.ClientId

GO

create procedure InsertAccount
(
      @TypeId bigint = null
      ,@AccountNumber varchar(30)
      ,@Currency varchar(3) = null
      ,@ClientId bigint = null
      ,@Status varchar(1)
      ,@Blocked bit
      ,@Balance decimal(19,2)
      ,@OverdraftLimit decimal(19,2)
      ,@AmountOnHold decimal(19,2)
)
AS
begin
      set nocount on

	  if(isnull(@Status,'') in ('A','C','O') or @Status is null)
	  begin

		insert into Accounts (TypeId,AccountNumber,Currency,ClientId,[Status],Blocked,Balance,OverdraftLimit,AmountOnHold)
		values
(@TypeId,@AccountNumber, @Currency,@ClientId,@Status,@Blocked,@Balance,@OverdraftLimit,@AmountOnHold)
      	  end
	  else
	  begin
		raiserror('Invalid Status!',16,1)
	  end


      set nocount off
end

GO

create procedure UpdateAccount
(
      @Id bigint
      ,@TypeId bigint = null
      ,@AccountNumber varchar(30)
      ,@Currency varchar(3) = null
      ,@ClientId bigint = null
      ,@Status varchar(1)
      ,@Blocked bit
      ,@Balance decimal(19,2)
      ,@OverdraftLimit decimal(19,2)
      ,@AmountOnHold decimal(19,2)
)
as
BEGIN
      set nocount on

	  if(isnull(@Status,'') in ('A','C','O') or @Status is null)
	  begin
		update Accounts
		set TypeId = ISNULL(@Name,TypeId)
            		,AccountNumber = ISNULL(@Description,AccountNumber)
            		,Currency = ISNULL(@ClassId,Currency)
			,ClientId = ISNULL(@Description,ClientId)
            		,[Status] = ISNULL(@ClassId,[Status])
			,Blocked = ISNULL(@Description,Blocked)
            		,Balance = ISNULL(@ClassId,Balance)
			,OverdraftLimit = ISNULL(@Description,OverdraftLimit)
            		,AmountOnHold = ISNULL(@ClassId,AmountOnHold)
		where Id = @Id
	  end
	  else
	  begin
		raiserror('Invalid Status!',16,1)
	  end


      set nocount off
END

GO


create proc CreateNewStatement
(
	@AccountId bigint
)
AS
BEGIN
	if exists (select * from PostingEntries where AccountId = @AccountId)
	begin
		declare @stmNo int

		select @stmNo = isnull(StatementNo,0) from PostingEntries where AccountId = @AccountId

		update PostingEntries
		set StatementNo = @stmNo+1
		where AccountId = @AccountId

		select @stmNo
	end
END

GO

create proc GetStatement
(
	@AccountId bigint
)
AS
BEGIN
	select t.[Description] as [Transaction], a.AccountNumber as Account,
		case when pe.DebitCredit = -1 then 'Debit' when DebitCredit = 1 then 'Credit' when DebitCredit = 0 then 'Nonfinancial' else NULL end as DebitCredit,
		ca.AccountNumber as ContraAccount, pe.Currency, pe.Amount, pe.StatementNo
	from PostingEntries pe
	left join Transactions t on t.Id = pe.TransactionId
	left join Accounts a on a.Id = pe.AccountId
	left join Accounts ca on ca.Id = pe.ContraAccountId
	where pe.AccountId = @AccountId
END



TransactionTypes
CREATE view dbo.TransactionTypesOverview
as
select tt.Name,tt.[Description], acd.Name as AccountClassDebit, acc.Name as AccountClassCredit, tt.ReflectsAmountOnHold, tt.HasFee
from dbo.TransactionTypes tt
left join dbo.AccountClasses acd on acd.Id = tt.AccountClassDebit
left join dbo.AccountClasses acc on acc.Id = tt.AccountClassCredit

GO

create procedure UpdateTransactionTypes
(
	@Id bigint
	,@AccountClassDebit bigint = null
	,@AccountClassCredit bigint = null
	,@Description nvarchar(200)
	,@Name varchar(30)
	,@ReflectsAmountOnHold bit
	,@HasFee bit
)
as
BEGIN
	set nocount on

	update TransactionTypes
	set Name = ISNULL(@Name,Name)
		,[Description] = ISNULL(@Description,[Description])
		,AccountClassDebit = ISNULL(@AccountClassDebit,AccountClassDebit)
		,AccountClassCredit = ISNULL(@AccountClassCredit,AccountClassCredit)
		,ReflectsAmountOnHold = ISNULL(@ReflectsAmountOnHold,ReflectsAmountOnHold)
		,HasFee = ISNULL(@HasFee,HasFee)
	where Id = @Id

	set nocount off
END

GO

create procedure InsertTransactionTypes
(
	@AccountClassDebit bigint = null
	,@AccountClassCredit bigint = null
	,@Description nvarchar(200)
	,@Name varchar(30)
	,@ReflectsAmountOnHold bit
	,@HasFee bit
)
AS
begin
	set nocount on

	insert into TransactionTypes(Name,[Description],AccountClassDebit,AccountClassCredit,ReflectsAmountOnHold,HasFee)
	values (@Name,@Description, @AccountClassDebit,@AccountClassCredit,@ReflectsAmountOnHold,@HasFee)

	set nocount off
end


Transactions
CREATE view dbo.TransactionsOverview
as
select tt.Name as TransactionType, u.Name as [User], t.EntryDate, t.[Date], t.Currency, ad.AccountNumber as AccountDebit, ac.AccountNumber as AccountCredit,t.Amount, t.Fee, t.[Description],
	case when t.[Status] = 'A' then 'Active' when t.[Status] = 'C' then 'Cancelled' else NULL end as [Status]
from dbo.Transactions t
left join dbo.TransactionTypes tt on tt.Id = t.TransactionTypeId
left join dbo.Users u on u.Id = t.UserId
left join dbo.Accounts ad on ad.Id = t.AccountIdDebit
left join dbo.Accounts ac on ac.Id = t.AccountIdDebit

GO

create procedure UpdateTransactions
(
	@Id bigint
	,@TransactionTypeId bigint = NULL
	,@UserId bigint = NULL
	,@EntryDate date
	,@Date date
	,@Currency varchar(3) = NULL
	,@AccountIdDebit bigint = NULL
	,@AccountIdCredit bigint = NULL
	,@Fee decimal(19,2)
	,@Amount decimal(19,2)
	,@Description nvarchar(200)
	,@Status varchar(1)
)
as
BEGIN
	set nocount on

	if(@Status is null or @Status in ('A', 'C'))
	begin
		update Transactions
		set TransactionTypeId = ISNULL(@TransactionTypeId,TransactionTypeId)
			,UserId = ISNULL(@UserId,UserId)
			,EntryDate = ISNULL(@EntryDate,EntryDate)
			,[Date] = ISNULL(@Date,[Date])
			,Currency = ISNULL(@Currency,Currency)
			,AccountIdDebit = ISNULL(@AccountIdDebit,AccountIdDebit)
			,AccountIdCredit = ISNULL(@AccountIdCredit,AccountIdCredit)
			,Fee = ISNULL(@Fee,Fee)
			,Amount = ISNULL(@Amount,Amount)
			,[Description] = ISNULL(@Description,[Description])
			,[Status] = ISNULL(@Status,[Status])
		where Id = @Id
	end
	else
	begin
		raiserror('Invalid status!',16,1)
	end

	set nocount off
END

GO

create procedure InsertTransactions
(
	@TransactionTypeId bigint = NULL
	,@UserId bigint = NULL
	,@EntryDate date
	,@Date date
	,@Currency varchar(3) = NULL
	,@AccountIdDebit bigint = NULL
	,@AccountIdCredit bigint = NULL
	,@Fee decimal(19,2)
	,@Amount decimal(19,2)
	,@Description nvarchar(200)
	,@Status varchar(1)
)
AS
begin
	set nocount on

	if(@Status is null or @Status in ('A', 'C'))
	begin
		insert into Transactions(TransactionTypeId,UserId,EntryDate,[Date],Currency, AccountIdDebit, AccountIdCredit, Fee, Amount, [Description], [Status])
		values (@TransactionTypeId,@UserId, @EntryDate,@Date, @Currency,@AccountIdDebit,@AccountIdCredit,@Fee,@Amount,@Description,@Status)
	end
	else
	begin
		raiserror('Invalid status!',16,1)
	end

	set nocount off
end



PostingEntries

CREATE view dbo.PostingEntriesOverview
as
select t.[Description] as [Transaction], a.AccountNumber as Account,
	case when pe.DebitCredit = -1 then 'Debit' when DebitCredit = 1 then 'Credit' when DebitCredit = 0 then 'Nonfinancial' else NULL end as DebitCredit,
	ca.AccountNumber as ContraAccount, pe.Currency, pe.Amount, pe.StatementNo
from dbo.PostingEntries pe
left join Transactions t on t.Id = pe.TransactionId
left join Accounts a on a.Id = pe.AccountId
left join Accounts ca on ca.Id = pe.ContraAccountId

GO

create procedure InsertPostingEntries
(
	@TransactionId bigint = NULL
	,@AccountId bigint = NULL
	,@DebitCredit smallint
	,@ContraAccountId bigint = NULL
	,@Currency varchar(3) = NULL
	,@Amount decimal(19,2)
	,@StatementNo int
)
AS
begin
	set nocount on

	if(@DebitCredit is null or @DebitCredit in (0,1,-1))
	begin
		insert into PostingEntries(TransactionId,AccountId,DebitCredit,ContraAccountId,Currency, Amount, StatementNo)
		values (@TransactionId,@AccountId, @DebitCredit,@ContraAccountId, @Currency,@Amount,@StatementNo)
	end
	else
	begin
		raiserror('Invalid DebitCredit!',16,1)
	end

	set nocount off
end

GO

create procedure UpdatePostingEntries
(
	@Id bigint
	,@TransactionId bigint = NULL
	,@AccountId bigint = NULL
	,@DebitCredit smallint
	,@ContraAccountId bigint = NULL
	,@Currency varchar(3) = NULL
	,@Amount decimal(19,2)
	,@StatementNo int
)
as
BEGIN
	set nocount on

	if(@DebitCredit is null or @DebitCredit in (0,1,-1))
	begin
		update PostingEntries
		set TransactionId = ISNULL(@TransactionId,TransactionId)
			,AccountId = ISNULL(@AccountId,AccountId)
			,DebitCredit = ISNULL(@DebitCredit,DebitCredit)
			,ContraAccountId = ISNULL(@ContraAccountId,ContraAccountId)
			,Currency = ISNULL(@Currency,Currency)
			,Amount = ISNULL(@Amount,Amount)
			,StatementNo = ISNULL(@StatementNo,StatementNo)
		where Id = @Id
	end
	else
	begin
		raiserror('Invalid status!',16,1)
	end

	set nocount off
END




1.	PostingEntries,  IUD, Update Balance in Accounts table

create trigger Insert_PostingEntriesAccount
on PostingEntries
after insert
as
begin
	update a
	set a.Balance = ISNULL(a.Balance,0)+ (select sum(DebitCredit*Amount) from inserted where Id = i.Id)
	from Accounts a
	join inserted i on i.AccountId = a.Id
end

GO

create trigger Update_PostingEntriesAccount
on PostingEntries
after update
as
begin
	if update(Amount)
	begin
		update a
		set a.Balance = ISNULL(a.Balance,0)+ (select sum(DebitCredit*Amount) from inserted where Id = i.Id) - (select sum(DebitCredit*Amount) from deleted where Id = d.Id)
		from Accounts a
		join inserted i on i.AccountId = a.Id
		join deleted d on d.AccountId = a.Id
	end
end

GO

create trigger Delete_PostingEntriesAccount
on PostingEntries
after delete
as
begin
	update a
	set a.Balance = ISNULL(a.Balance,0) - (select sum(DebitCredit*Amount) from deleted where Id = d.Id)		from Accounts a
	join deleted d on d.AccountId = a.Id
end




















2.	Posting Entries, IUD, Update AmountOnHold in Accounts table

create trigger Insert_PostingEntriesAccountAmountOnHold
on PostingEntries
after insert
as
begin
	update a
	set a.AmountOnHold = ISNULL(a.AmountOnHold,0)+ (select sum(Amount) from inserted where Id = i.Id and i.DebitCredit = 0)
	from Accounts a
	join inserted i on i.AccountId = a.Id
	where i.DebitCredit = 0
end

GO

create trigger Update_PostingEntriesAccountAmountOnHold
on PostingEntries
after update
as
begin
	if update(Amount)
	begin
		update a
		set a.AmountOnHold = ISNULL(a.AmountOnHold,0)+ (select sum(Amount) from inserted where Id = i.Id and i.DebitCredit = 0) - (select sum(Amount) from deleted where Id = d.Id and d.DebitCredit = 0)
		from Accounts a
		join inserted i on i.AccountId = a.Id
		join deleted d on d.AccountId = a.Id
		where i.DebitCredit = 0 and d.DebitCredit = 0
	end
end

GO

create trigger Delete_PostingEntriesAccountAmountOnHold
on PostingEntries
after delete
as
begin
	update a
	set a.AmountOnHold = ISNULL(a.AmountOnHold,0) - (select sum(Amount) from deleted where Id = d.Id and d.DebitCredit = 0)
	from Accounts a
	join deleted d on d.AccountId = a.Id
	where d.DebitCredit = 0
end



















3.	Transactions, IUD, Generate entries in PostingEntries

create trigger Insert_PostingEntries
on Transactions
after insert
as
begin

	select * into #TransactionTmp from inserted

	alter table #TransactionTmp add ReflectsAmountOnHold bit

	update t
	set t.ReflectsAmountOnHold = tt.ReflectsAmountOnHold
	from #TransactionTmp t
	join TransactionTypes tt on tt.id = t.TransactionTypeId

	insert into PostingEntries(TransactionId,AccountId,DebitCredit,ContraAccountId,Currency,Amount,StatementNo)
	select Id,AccountIdDebit,0,AccountIdCredit,Currency,Amount,null from #temp
	where ReflectsAmountOnHold = 1

	insert into PostingEntries (TransactionId,AccountId,DebitCredit,ContraAccountId,Currency,Amount,StatementNo)
	select Id,AccountIdDebit,-1,AccountIdCredit,Currency,Amount,null from #temp
	where ReflectsAmountOnHold = 0

	insert into PostingEntries (TransactionId,AccountId,DebitCredit,ContraAccountId,Currency,Amount,StatementNo)
	select Id,AccountIdCredit,1,AccountIdDebit,Currency,Amount,null from #temp
	where ReflectsAmountOnHold = 0

end

GO

create trigger Update_PostingEntries
on Transactions
after update
as
begin

	if exists(select * from deleted d join inserted i on i.Id = d.Id and i.Amount<>d.Amount)
	begin

		update pe
		set pe.Amount = t.Amount
		from inserted t
		join PostingEntries pe on pe.TransactionId = t.Id

	end

end

GO

create trigger Delete_PostingEntries
on Transactions
after delete
as
begin
	delete pe
	from deleted d
	join PostingEntries pe on pe.TransactionId = d.Id
end