USE [master]
GO
/****** Object:  Database [MyHawk]    Script Date: 06/02/2024 11:45:24 ******/
CREATE DATABASE [MyHawk]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'MyHawk', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MyHawk.mdf' , SIZE = 197376KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'MyHawk_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MyHawk_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [MyHawk] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MyHawk].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [MyHawk] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [MyHawk] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [MyHawk] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [MyHawk] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [MyHawk] SET ARITHABORT OFF 
GO
ALTER DATABASE [MyHawk] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [MyHawk] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [MyHawk] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [MyHawk] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [MyHawk] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [MyHawk] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [MyHawk] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [MyHawk] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [MyHawk] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [MyHawk] SET  DISABLE_BROKER 
GO
ALTER DATABASE [MyHawk] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [MyHawk] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [MyHawk] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [MyHawk] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [MyHawk] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [MyHawk] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [MyHawk] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [MyHawk] SET RECOVERY FULL 
GO
ALTER DATABASE [MyHawk] SET  MULTI_USER 
GO
ALTER DATABASE [MyHawk] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [MyHawk] SET DB_CHAINING OFF 
GO
ALTER DATABASE [MyHawk] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [MyHawk] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [MyHawk] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [MyHawk] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
EXEC sys.sp_db_vardecimal_storage_format N'MyHawk', N'ON'
GO
ALTER DATABASE [MyHawk] SET QUERY_STORE = OFF
GO
USE [MyHawk]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CONNECTION_ID]() RETURNS smallint AS
            BEGIN
              DECLARE @var smallint
              SELECT @var = @@SPID
              RETURN @Var
            END
GO
/****** Object:  UserDefinedFunction [dbo].[GREATEST]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GREATEST](@op1 sql_variant, @op2 sql_variant) RETURNS sql_variant AS
            BEGIN
              DECLARE @result sql_variant
              SET @result = CASE WHEN @op1 >= @op2 THEN @op1 ELSE @op2 END
              RETURN @result
            END
GO
/****** Object:  UserDefinedFunction [dbo].[IF]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[IF](@expr1 sql_variant, @expr2 sql_variant, @expr3 sql_variant) RETURNS sql_variant AS
            BEGIN
              DECLARE @result sql_variant
              SET @result = CASE WHEN CAST(@expr1 AS int) != 0 THEN @expr2 ELSE @expr3 END
              RETURN @result
            END
GO
/****** Object:  UserDefinedFunction [dbo].[LPAD]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[LPAD](@str nvarchar(max), @len int, @padstr nvarchar(max)) RETURNS nvarchar(4000) AS
            BEGIN
	            RETURN left(@str + replicate(@padstr,@len),@len)
            END
GO
/****** Object:  UserDefinedFunction [dbo].[MD5]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[MD5](@value varchar(255)) RETURNS varchar(32) AS
            BEGIN
	            RETURN SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @value)),3,32)
            END
GO
/****** Object:  UserDefinedFunction [dbo].[SUBSTRING]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SUBSTRING](@op1 nvarchar(max), @op2 sql_variant, @op3 sql_variant) RETURNS nvarchar(max) AS
BEGIN
  RETURN CAST(SUBSTRING(CAST(@op1 AS nvarchar(max)), CAST(@op2 AS int), CAST(@op3 AS int)) AS nvarchar(max))
END
GO
/****** Object:  UserDefinedFunction [dbo].[SUBSTRING_INDEX]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SUBSTRING_INDEX](@string varchar(8000), @delimiter char(1), @count int) RETURNS varchar(8000) AS
            BEGIN
              DECLARE @result varchar(8000)
              DECLARE @end int
              DECLARE @part int
              SET @end = 0
              SET @part = 0
              IF (@count = 0)
              BEGIN
                SET @result = ''
              END
              ELSE
              BEGIN
                IF (@count < 0)
                BEGIN
                  SET @string = REVERSE(@string)
                END
                WHILE (@part < ABS(@count))
                BEGIN
                  SET @end = CHARINDEX(@delimiter, @string, @end + 1)
                  IF (@end = 0)
                  BEGIN
                    SET @end = LEN(@string) + 1
                    BREAK
                  END
                  SET @part = @part + 1
                END
                SET @result = SUBSTRING(@string, 1, @end - 1)
                IF (@count < 0)
                BEGIN
                  SET @result = REVERSE(@result)
                END
              END
              RETURN @result
            END
GO
/****** Object:  Table [dbo].[AR_Customer]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AR_Customer](
	[CUSTOMER_ID] [int] NOT NULL,
	[CUSTOMER_NUMBER] [varchar](15) NOT NULL,
	[CUSTOMER_NAME] [varchar](60) NULL,
	[CUSTOMER_SITE_ID] [int] NULL,
	[BUSINESS_NAME] [varchar](60) NULL,
	[ADDRESS_1] [varchar](60) NULL,
	[ADDRESS_2] [varchar](60) NULL,
	[Customer_Group_ID] [int] NULL,
	[Term_Id] [int] NOT NULL,
 CONSTRAINT [PK_AR_CUSTOMER] PRIMARY KEY CLUSTERED 
(
	[CUSTOMER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[AR_Customer_Site]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AR_Customer_Site](
	[CUSTOMER_SITE_ID] [int] NOT NULL,
	[CUSTOMER_ID] [int] NOT NULL,
	[COMMERCIAL] [nvarchar](1) NULL,
	[BUSINESS_NAME] [varchar](60) NOT NULL,
	[ADDRESS_1] [varchar](60) NULL,
	[ADDRESS_2] [varchar](60) NULL,
 CONSTRAINT [PK_AR_Customer_Site] PRIMARY KEY CLUSTERED 
(
	[CUSTOMER_SITE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[AR_INVOICE]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AR_INVOICE](
	[ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[INVOICE_ID] [int] NOT NULL,
	[INVOICE_NUMBER] [int] NULL,
	[TYPE_JSCO] [varchar](1) NULL,
	[CUSTOMER_ID] [int] NOT NULL,
	[CUSTOMER_SITE_ID] [int] NULL,
	[Invoice_Date] [datetime] NULL,
	[Issue_Date] [datetime] NULL,
	[SERVICE_START_DATE] [datetime] NULL,
	[SERVICE_END_DATE] [datetime] NULL,
	[AMOUNT] [money] NULL,
	[PAYMENT] [money] NULL,
	[Net_Due] [money] NULL,
	[Lst_Payment_Date] [datetime] NULL,
	[Lst_Update_Date] [datetime] NULL,
 CONSTRAINT [PK_AR_INVOICE] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Errors]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Errors](
	[ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[Error_ID] [int] NOT NULL,
	[Error_Descr] [varchar](50) NOT NULL,
	[Crtd_DateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_Errors] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[GP_TranscationLog]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GP_TranscationLog](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	[Token] [varchar](50) NOT NULL,
	[CustNo] [varchar](15) NOT NULL,
	[GP_RefNo] [varchar](100) NOT NULL,
	[Trx_Value] [decimal](18, 2) NOT NULL,
	[GP_Response] [varchar](10) NOT NULL,
	[Create_DateTime] [datetime] NOT NULL,
	[RowID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 CONSTRAINT [PK_GP_TranscationLog] PRIMARY KEY CLUSTERED 
(
	[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Log]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Log](
	[ID] [uniqueidentifier] NOT NULL,
	[MH_Transaction_ID] [uniqueidentifier] NOT NULL,
	[TYPE] [varchar](15) NOT NULL,
	[DESCRIPTION] [varchar](100) NOT NULL,
	[CRTD_DATE] [datetime] NOT NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[MH_PAYMENT_SOURCE]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MH_PAYMENT_SOURCE](
	[TYPE] [int] NOT NULL,
	[DESCRIPTION] [varchar](30) NOT NULL,
	[CRTD_DATE] [nchar](10) NOT NULL,
	[LST_UPDATED_DATE] [nchar](10) NULL,
 CONSTRAINT [PK_MH_PAYMENT_SOURCE] PRIMARY KEY CLUSTERED 
(
	[TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[MH_Transactions]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MH_Transactions](
	[ID] [uniqueidentifier] NOT NULL,
	[CUSTOMER_ID] [int] NOT NULL,
	[INVOICE_NO] [varchar](10) NOT NULL,
	[CUSTOMER_SITE_ID] [varchar](10) NOT NULL,
	[PAYMENT_SOURCE] [int] NOT NULL,
	[STATUS] [varchar](10) NOT NULL,
	[AMOUNT_PAID] [decimal](18, 3) NOT NULL,
	[REF_NO] [varchar](100) NOT NULL,
	[COMMENT] [varchar](30) NULL,
	[DATE_PAID] [datetime] NOT NULL,
	[Crtd_Date] [datetime] NOT NULL,
	[Lst_Updated_Date] [datetime] NOT NULL,
	[Token] [varchar](50) NOT NULL,
 CONSTRAINT [PK_MH_Transactions] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[MH_Users]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MHToken](
	[Customer_Number] [varchar](15) NOT NULL,
	[Token] [varchar](50) NOT NULL,
	[isValid] [char](1) NULL,
	[Crtd_DateTime] [datetime] NOT NULL,
	[Expiration_DateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_MHToken_1] PRIMARY KEY CLUSTERED 
(
	[Customer_Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Permissions]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Permissions](
	[ID] [uniqueidentifier] NOT NULL,
	[ITEM] [varchar](30) NOT NULL,
	[SETTING] [varchar](100) NOT NULL,
	[CRTD_DATE] [datetime] NOT NULL,
	[LST_UPDATED_DATE] [datetime] NULL,
 CONSTRAINT [PK_Permissions] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Temp_MH_Transactions]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Temp_MH_Transactions](
	[ID] [uniqueidentifier] NOT NULL,
	[CUSTOMER_ID] [int] NOT NULL,
	[INVOICE_NO] [varchar](10) NOT NULL,
	[CUSTOMER_SITE_ID] [varchar](10) NOT NULL,
	[PAYMENT_SOURCE] [int] NOT NULL,
	[STATUS] [varchar](10) NULL,
	[AMOUNT_PAID] [decimal](18, 3) NOT NULL,
	[REF_NO] [varchar](100) NULL,
	[COMMENT] [varchar](30) NULL,
	[DATE_PAID] [datetime] NOT NULL,
	[Crtd_Date] [datetime] NOT NULL,
	[Lst_Updated_Date] [datetime] NOT NULL,
	[Token] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Temp_MH_Transactions] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AR_INVOICE] ADD  CONSTRAINT [DF_AR_INVOICE_ID]  DEFAULT (newid()) FOR [ID]
GO
ALTER TABLE [dbo].[Errors] ADD  CONSTRAINT [DF_Errors_ID]  DEFAULT (newid()) FOR [ID]
GO
ALTER TABLE [dbo].[GP_TranscationLog] ADD  CONSTRAINT [DF_GP_TranscationLog_Trx_Value]  DEFAULT ((0)) FOR [Trx_Value]
GO
ALTER TABLE [dbo].[GP_TranscationLog] ADD  CONSTRAINT [DF_GP_TranscationLog_RowID]  DEFAULT (newid()) FOR [RowID]
GO
ALTER TABLE [dbo].[Temp_MH_Transactions] ADD  CONSTRAINT [DF_Temp_MH_Transactions_ID]  DEFAULT (newid()) FOR [ID]
GO
ALTER TABLE [dbo].[AR_Customer_Site]  WITH CHECK ADD  CONSTRAINT [FK_AR_Customer_Site_AR_CUSTOMER] FOREIGN KEY([CUSTOMER_ID])
REFERENCES [dbo].[AR_Customer] ([CUSTOMER_ID])
GO
ALTER TABLE [dbo].[AR_Customer_Site] CHECK CONSTRAINT [FK_AR_Customer_Site_AR_CUSTOMER]
GO
ALTER TABLE [dbo].[MH_Transactions]  WITH CHECK ADD  CONSTRAINT [FK_MH_Transactions_AR_CUSTOMER] FOREIGN KEY([CUSTOMER_ID])
REFERENCES [dbo].[AR_Customer] ([CUSTOMER_ID])
GO
ALTER TABLE [dbo].[MH_Transactions] CHECK CONSTRAINT [FK_MH_Transactions_AR_CUSTOMER]
GO
ALTER TABLE [dbo].[MH_Transactions]  WITH CHECK ADD  CONSTRAINT [FK_MH_Transactions_MH_PAYMENT_SOURCE] FOREIGN KEY([PAYMENT_SOURCE])
REFERENCES [dbo].[MH_PAYMENT_SOURCE] ([TYPE])
GO
ALTER TABLE [dbo].[MH_Transactions] CHECK CONSTRAINT [FK_MH_Transactions_MH_PAYMENT_SOURCE]
GO
ALTER TABLE [dbo].[MH_Users]  WITH CHECK ADD  CONSTRAINT [FK_MH_Users_AR_Customer_Site] FOREIGN KEY([Customer_ID])
REFERENCES [dbo].[AR_Customer_Site] ([CUSTOMER_SITE_ID])
GO
ALTER TABLE [dbo].[MH_Users] CHECK CONSTRAINT [FK_MH_Users_AR_Customer_Site]
GO
ALTER TABLE [dbo].[Temp_MH_Transactions]  WITH CHECK ADD  CONSTRAINT [FK_Temp_MH_Transactions_MH_PAYMENT_SOURCE] FOREIGN KEY([PAYMENT_SOURCE])
REFERENCES [dbo].[MH_PAYMENT_SOURCE] ([TYPE])
GO
ALTER TABLE [dbo].[Temp_MH_Transactions] CHECK CONSTRAINT [FK_Temp_MH_Transactions_MH_PAYMENT_SOURCE]
GO
/****** Object:  StoredProcedure [dbo].[sp_AddGPTransaction]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_AddGPTransaction]
@Token varchar(50),
@CustNo varchar(15),
@GPRefNo varchar(100),
@TrxVal decimal(18,2),
@GPResp varchar(10)

as

insert into GP_TranscationLog (Token,CustNo,GP_RefNo,Trx_Value,GP_Response,Create_DateTime)
values(@Token,@CustNo,@GPRefNo,@TrxVal,@GPResp,getdate())
GO
/****** Object:  StoredProcedure [dbo].[sp_AddToCart]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_AddToCart]
@Flag int,
@CustNo varchar(15),
@SiteID int,
@InvoiceNo int,
@Pay_Source int,
@AmountPaid decimal(18,3),
@Comment varchar(200),
@Token varchar(50)

as
-- Flag==> 1 = individual open invoice per site, 2 = All open invoices at a site, 3 = all open invoices for an account
declare @Exist as int,@ValToken as int,@ExpiredToken as int,@MyCart as int
declare @Source as int
declare @Status as varchar(10),@RefNo varchar(20)

set @Source=-1

select @ValToken=count(*) from MHToken where isValid='Y' and Token=@Token and Customer_Number=@CustNo
select @ExpiredToken=count(*) from MHToken where isValid='Y' and Token=@Token and Customer_Number=@CustNo and DATEDIFF(SECOND,getdate(),Expiration_DateTime)<0
select @MyCart=count(*) from Temp_MH_Transactions where 0=0 and CUSTOMER_ID=@CustNo and CUSTOMER_SITE_ID=@SiteID and INVOICE_NO=@InvoiceNo

select @Exist=count(*) from Temp_MH_Transactions where 0=0 and Token=@Token and CUSTOMER_ID=@CustNo and INVOICE_NO=@InvoiceNo

 
if @ValToken=1 and @Exist=0 and @ExpiredToken=0 and @Flag=1 and @MyCart=0 --Individual open invoice for a site
	begin
		--insert into Temp_MH_Transactions (Customer_ID,Invoice_No,Customer_Site_ID,PAYMENT_SOURCE,STATUS,AMOUNT_PAID, REF_NO,COMMENT,DATE_PAID,Crtd_Date,Lst_Updated_Date,Token)
		--values(@CustNo,@InvoiceNo,@SiteID,@Source,'Test',@AmountPaid,@RefNo,@Comment,getdate(),getdate(),getdate(),@Token)
		insert into Temp_MH_Transactions (Customer_ID,Invoice_No,Customer_Site_ID,PAYMENT_SOURCE,STATUS,AMOUNT_PAID, REF_NO,COMMENT,DATE_PAID,Crtd_Date,Lst_Updated_Date,Token)
		select Customer_ID,invoice_number,Customer_Site_ID,@Pay_Source,'Test',Net_Due, @REFNO,@Comment,getdate(),getdate(),getdate(),@Token
		from AR_INVOICE
		where 0=0
		and INVOICE_NUMBER=@InvoiceNo
		and CUSTOMER_ID=@CustNo
		and CUSTOMER_SITE_ID=@SiteID
		and Net_Due>0
		and INVOICE_NUMBER not in (select INVOICE_No from Temp_MH_Transactions where CUSTOMER_ID=@CustNo and CUSTOMER_SITE_ID=@SiteID and Token=@Token)

		select 200 as Status,@Token as Token
	end
else if @ValToken=1 and @Exist=0 and @ExpiredToken=0 and @Flag=2  --All open invoices for a site 
	begin
		insert into Temp_MH_Transactions (Customer_ID,Invoice_No,Customer_Site_ID,PAYMENT_SOURCE,STATUS,AMOUNT_PAID, REF_NO,COMMENT,DATE_PAID,Crtd_Date,Lst_Updated_Date,Token)
		select Customer_ID,invoice_number,Customer_Site_ID,@Pay_Source,'Test',Net_Due, @REFNO,@Comment,getdate(),getdate(),getdate(),@Token
		from AR_INVOICE
		where 0=0
		and CUSTOMER_ID=@CustNo
		and CUSTOMER_SITE_ID=@SiteID
		and Net_Due>0
		and INVOICE_NUMBER not in (select INVOICE_No from Temp_MH_Transactions where CUSTOMER_ID=@CustNo and CUSTOMER_SITE_ID=@SiteID and Token=@Token)

		
		select 200 as Status,@Token as Token
	end
else if @ValToken=1 and @Exist=0 and @ExpiredToken=0 and @Flag=3 --All open invoices for an account
	begin
		insert into Temp_MH_Transactions (Customer_ID,Invoice_No,Customer_Site_ID,PAYMENT_SOURCE,STATUS,AMOUNT_PAID, REF_NO,COMMENT,DATE_PAID,Crtd_Date,Lst_Updated_Date,Token)
		select Customer_ID,invoice_number,Customer_Site_ID,@Pay_Source,'Test',Net_Due, @REFNO,@Comment,getdate(),getdate(),getdate(),@Token
		from AR_INVOICE
		where 0=0
		and CUSTOMER_ID=@CustNo
		--and CUSTOMER_SITE_ID=@SiteID
		and Net_Due>0
		and INVOICE_NUMBER not in (select INVOICE_No from Temp_MH_Transactions where CUSTOMER_ID=@CustNo and Token=@Token)
		
		select 200 as Status,@Token as Token
	end
else if @ValToken=0
		begin			
			select 414 as Status,@Token as Token
		end
else if @ExpiredToken>0
		begin			
			select 415 as Status,@Token as Token
		end
else if @Exist=1
		begin			
			select 416 as Status,@Token as Token
		end
GO
/****** Object:  StoredProcedure [dbo].[SP_AR_CUSTOMER_SITE_UPDATE]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_AR_CUSTOMER_SITE_UPDATE]
AS


INSERT INTO AR_Customer_Site (CUSTOMER_SITE_ID,CUSTOMER_ID,COMMERCIAL,BUSINESS_NAME,ADDRESS_1,ADDRESS_2)
SELECT CUSTOMER_SITE_ID,CUSTOMER_ID,COMMERCIAL,BUSINESS_NAME,ADDRESS_1,ADDRESS_2
from [10.16.0.20].[Hawkeye_Final].[dbo].[AR_Customer_Site]
WHERE INACTIVE='N'
AND Customer_Site_ID NOT IN (SELECT Customer_Site_ID FROM AR_Customer_Site)
GO
/****** Object:  StoredProcedure [dbo].[SP_AR_CUSTOMER_UPDATE]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_AR_CUSTOMER_UPDATE]
AS

INSERT INTO AR_Customer (CUSTOMER_ID,CUSTOMER_NUMBER,CUSTOMER_NAME,CUSTOMER_GROUP_ID,TERM_ID)
SELECT CUSTOMER_ID,CUSTOMER_NUMBER,CUSTOMER_NAME,CUSTOMER_GROUP_ID,TERM_ID
FROM [10.16.0.20].[Hawkeye_Final].[dbo].[AR_Customer]
WHERE CUSTOMER_ID NOT IN (SELECT CUSTOMER_ID FROM AR_Customer)



GO
/****** Object:  StoredProcedure [dbo].[SP_CreateInvoices]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[SP_CreateInvoices]
as

insert into AR_INVOICE
(ID,[INVOICE_ID],
      [INVOICE_NUMBER]
      ,[TYPE_JSCO]
      ,[CUSTOMER_ID]
      ,[CUSTOMER_SITE_ID]
      ,[Invoice_Date]
      ,[Issue_Date]
      ,[SERVICE_START_DATE]
      ,[SERVICE_END_DATE]
      ,[AMOUNT]
      ,[PAYMENT]
      ,[Net_Due]
      ,[Lst_Payment_Date]
      ,[Lst_Update_Date]
	  )
select --TOP (1000)
     newid(),
	 [INVOICE_ID],
      [INVOICE_NUMBER]
      ,[TYPE_JSCO]
      ,[CUSTOMER_ID]
      ,[CUSTOMER_SITE_ID]
      ,[Invoice_Date]
      ,[Issue_Date]
      ,[SERVICE_START_DATE]
      ,[SERVICE_END_DATE]
      ,[AMOUNT]
      ,[PAYMENT]
      ,[Net_Due]
      ,Payment_Date
      ,Posting_Date
  --FROM [MyHawk].[dbo].[AR_INVOICE]
    FROM [10.16.0.20].[Hawkeye_Final].[dbo].[AR_Invoice]
	where [INVOICE_NUMBER] not in (select invoice_number from AR_INVOICE)
GO
/****** Object:  StoredProcedure [dbo].[sp_FinalizeCart]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_FinalizeCart]
@CustNo varchar(15),
@RefNo varchar(100),
@Comment varchar(30),
@Token varchar(50)

as
declare @res1 as int, @res2 as int

--Updates temp records with valid RefNo and Comment from GP
update Temp_MH_Transactions
set Ref_No=@RefNo, Comment=@Comment
where 0=0
and Customer_ID = @CustNo
and Token=@Token

--Moves temp records to permanent after payment confirmation
insert into MH_Transactions
select *
from Temp_MH_Transactions
where 0=0
and Customer_ID = @CustNo
and Token=@Token

--clears temp table
delete from Temp_MH_Transactions
where 0=0
and Customer_ID = @CustNo
and Token=@Token

select @res1=count(*) 
from Temp_MH_Transactions
where 0=0
and Customer_ID = @CustNo
and Token=@Token 

select @res2=count(*) 
from MH_Transactions
where 0=0
and Customer_ID = @CustNo
and Token=@Token 

if @res1=0 and @res2>0
	begin 
		select 200 as Status,@Token as Token
	end
else
	begin
		select 401 as Status,@Token as Token
	end
GO
/****** Object:  StoredProcedure [dbo].[SP_GET_CUSTOMER]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_GET_CUSTOMER] AS
SELECT CUSTOMER_ID,CUSTOMER_NUMBER,CUSTOMER_NAME,CUSTOMER_GROUP_ID,TERM_ID
FROM [10.16.0.20].[Hawkeye_Final].[dbo].[AR_Customer]
WHERE CUSTOMER_ID NOT IN (SELECT CUSTOMER_ID FROM AR_Customer)
GO
/****** Object:  StoredProcedure [dbo].[SP_GET_CUSTOMER_SITE_Finale]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_GET_CUSTOMER_SITE_Finale] AS
SELECT CUSTOMER_SITE_ID,CUSTOMER_ID,COMMERCIAL,BUSINESS_NAME,ADDRESS_1,ADDRESS_2
from [10.16.0.20].[Hawkeye_Final].[dbo].[AR_Customer_Site]
WHERE INACTIVE='N'
AND Customer_Site_ID NOT IN (SELECT Customer_Site_ID FROM AR_Customer_Site)
GO
/****** Object:  StoredProcedure [dbo].[sp_Get_Error_Desc]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create procedure [dbo].[sp_Get_Error_Desc]
@Error_ID int
as

select Error_ID, Error_Descr 
from Errors
where 0=0
and Error_ID = @Error_ID
GO
/****** Object:  StoredProcedure [dbo].[sp_GetAccountSummary]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_GetAccountSummary]
@Customer_No varchar(15),
@Token varchar(50)

as

select B.Customer_Name,A.* 
from
(select [CUSTOMER_ID],Sum([Net_Due])as Net_Due
from AR_INVOICE
where 0=0
and Customer_ID = @Customer_No
and CUSTOMER_ID in 
(select Customer_Number from MHToken where Customer_Number=@Customer_No and DATEDIFF(SECOND,GETDATE(),Expiration_DateTime)>=0 and isValid='Y') 
--and Customer_ID in (select customer_ID from AR_Customer where CUSTOMER_NUMBER=@customer_no)
group by [CUSTOMER_ID]
)A left join AR_Customer B on A.Customer_ID=B.CUSTOMER_ID

GO
/****** Object:  StoredProcedure [dbo].[sp_GetCart]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_GetCart]
@Flag int,
@CustNo varchar(15),
@Token varchar(50)

as


if @Flag = 1
	begin
		select * from Temp_MH_Transactions where CUSTOMER_ID=@CustNo and Token = @Token
	end
else if @Flag = 2
		begin
			delete from Temp_MH_Transactions where CUSTOMER_ID=@CustNo and Token = @Token

			select * from Temp_MH_Transactions where CUSTOMER_ID=@CustNo and Token = @Token
		end
GO
/****** Object:  StoredProcedure [dbo].[sp_GetCustomerDetails]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_GetCustomerDetails]
@AccountNo varchar(15)=null,
@Email varchar(100)=null

as 

select [Customer_ID],[Customer_Number],[Customer_Name],[Email] from MH_Users where (Customer_Number=@AccountNo or Email=@Email)
GO
/****** Object:  StoredProcedure [dbo].[sp_GetCustomerSites]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_GetCustomerSites]
@customer_no varchar(15),
@flag int
as

declare @customer_id int,@qty int

--Flag ==> 1= Get site count, 2 = site details
select @qty= count([CUSTOMER_SITE_ID]) from AR_Customer_Site where Customer_ID in (select customer_ID from AR_Customer where CUSTOMER_NUMBER=@customer_no)

if @flag=1 and @Qty>0 -- checks if site exist for customer
	begin
		return 200
	end
else if @flag=1 and @Qty=0 
	begin
		return 406
	end
	else if @flag=2 and @Qty>0
			begin
				select [CUSTOMER_SITE_ID],[CUSTOMER_ID],[COMMERCIAL],[BUSINESS_NAME],[ADDRESS_1],[ADDRESS_2] 
				from ar_customer_site
				where 0=0
				and customer_id in (select customer_ID from AR_Customer where CUSTOMER_NUMBER=@customer_no)
			end
			else
				return 404
GO
/****** Object:  StoredProcedure [dbo].[sp_GetInvoiceListing]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_GetInvoiceListing]
@Customer_No varchar(15),
@Token varchar(50)

as

select [CUSTOMER_ID],[CUSTOMER_SITE_ID],[INVOICE_ID],[INVOICE_NUMBER],[Invoice_Date],[Issue_Date],[SERVICE_START_DATE],
[SERVICE_END_DATE],[AMOUNT],[PAYMENT],[Net_Due],[Lst_Payment_Date],[Lst_Update_Date] 
from AR_INVOICE
where 0=0
and Customer_ID = @Customer_No
--and Customer_ID in (select customer_ID from AR_Customer where CUSTOMER_NUMBER=@customer_no)
and CUSTOMER_ID in 
(select Customer_Number from MHToken where Customer_Number=@Customer_No and DATEDIFF(SECOND,GETDATE(),Expiration_DateTime)>=0 and isValid='Y') 
GO
/****** Object:  StoredProcedure [dbo].[sp_GetOpenInvoiceListing]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_GetOpenInvoiceListing]
@Customer_No varchar(15),
@Token varchar(50)

as

select [CUSTOMER_ID],[CUSTOMER_SITE_ID],[INVOICE_ID],[INVOICE_NUMBER],[Invoice_Date],[Issue_Date],[SERVICE_START_DATE],
[SERVICE_END_DATE],[AMOUNT],[PAYMENT],[Net_Due],[Lst_Payment_Date],[Lst_Update_Date] 
from AR_INVOICE
where 0=0
and Customer_ID = @Customer_No
and Net_Due>0
--and Customer_ID in (select customer_ID from AR_Customer where CUSTOMER_NUMBER=@customer_no)
and CUSTOMER_ID in 
(select Customer_Number from MHToken where Customer_Number=@Customer_No and DATEDIFF(SECOND,GETDATE(),Expiration_DateTime)>=0 and isValid='Y') 
GO
/****** Object:  StoredProcedure [dbo].[sp_getOTP]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_getOTP]
@CustomerNo varchar(15),
@Email varchar(100)

as

SELECT '200' AS STATUS, OTP, OTP_CRTD_DATE 
FROM MH_USERS 
WHERE  0=0 
AND (EMAIL=@EMAIL OR Customer_Number=@CustomerNo)
GO
/****** Object:  StoredProcedure [dbo].[sp_GetSiteSummary]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_GetSiteSummary]
@Customer_No varchar(15),
@Token varchar(50)

as

select [CUSTOMER_ID],[CUSTOMER_SITE_ID],Sum([Net_Due])as Net_Due
from AR_INVOICE
where 0=0
and Customer_ID = @Customer_No
and CUSTOMER_ID in 
(select Customer_Number from MHToken where Customer_Number=@Customer_No and DATEDIFF(SECOND,GETDATE(),Expiration_DateTime)>0 and isValid='Y')
--and Customer_ID in (select customer_ID from AR_Customer where CUSTOMER_NUMBER=@customer_no)
group by [CUSTOMER_ID],[CUSTOMER_SITE_ID]
GO
/****** Object:  StoredProcedure [dbo].[sp_GP_Print]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_GP_Print]
@CustNo varchar(15),
@Token varchar(50)

as

declare @End as int

select @End=count(*)
from MH_Transactions
where 0=0
and Token=@Token
and CUSTOMER_ID=@CustNo

if @End>0 
	begin
		select 2001 as Status, Token, GP_RefNo,GP_Response 
		from GP_TranscationLog where Token=@Token and CustNo=@CustNo
	end
else
	begin
		select 401 as Status, @Token as Token,''as GP_RefNo,''as GP_Response
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_GPPayment]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_GPPayment]
@CustNo varchar(15),
@Token varchar(50)

as

declare @GPRefNo as varchar(100), @GPResp as varchar(30), @Token2 as varchar(50)
declare @sp_call as int,@sp_call1 as int

insert into GP_TranscationLog (Token,CustNo,GP_RefNo,Trx_Value,GP_Response,Create_DateTime)
select
A.Token,A.CUSTOMER_ID,NEWID() AS REFNO,A.AmtPaid,'200'AS RESPONSE,GETDATE()AS CRTD_DATETIME
from Temp_MH_Transactions B
LEFT JOIN 
(select Token,CUSTOMER_ID,sum(Amount_Paid)as AmtPaid
from Temp_MH_Transactions
where 0=0
and Token=@Token
and CUSTOMER_ID=@CustNo
group by token,customer_id
)A
ON B.TOKEN=A.TOKEN AND B.CUSTOMER_ID=A.CUSTOMER_ID

declare GP_Cursor cursor
	for select GP_RefNo,GP_Response,@Token as Token from GP_TranscationLog where Token=@Token and CustNo=@CustNo
	open GP_Cursor
	fetch next from GP_Cursor into @GPRefNo,@GPResp,@Token2
	while @@FETCH_STATUS=0
		begin			
			exec @sp_call = sp_FinalizeCart @CustNo, @GPRefNo, @GPResp, @Token2	
			fetch next from GP_Cursor into 
				@GPRefNo,
				@GPResp,
				@Token2
		end
	close GP_Cursor	
	deallocate GP_Cursor

--exec @sp_call1= sp_GP_Print @CustNo,@Token
GO
/****** Object:  StoredProcedure [dbo].[sp_InvoiceCart]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from MH_Transactions

CREATE procedure [dbo].[sp_InvoiceCart]
@CustNo varchar(15),
@Token varchar(50)

as

select * from Temp_MH_Transactions
where Token=@Token and CUSTOMER_ID=@CustNo

GO
/****** Object:  StoredProcedure [dbo].[sp_isAccountVerified]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_isAccountVerified]
@AccountNo int

As 

declare @isVerfied int

select @isVerfied =count(Customer_ID) from MH_Users where 0=0 and Customer_Number=@AccountNo and isVerified='Y'

if @isVerfied=1 
	begin
		select '200'as Status
	end
else
	begin
		select '403'as Status
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_Login]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_LoginToken]
@cust_no varchar(15),
@token varchar(50)
as

declare @Exist int

select @Exist=count(*) from MHToken where Customer_Number=@cust_No and Token=@token and isValid='Y'
if @Exist>0 
	begin
		select '200'
	end
else
	begin
		select '404'
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_MHTokens]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[sp_MHTokens]
@flag int,
@Customer_Number varchar(15),
@token varchar(50)

as 

declare @MYTOKEN varchar(100)
declare @exist int,@Exist_Customer int


select @MYTOKEN= cast(newid() as varchar(50))
select @Exist_Customer=count(customer_number) from MHToken where customer_number=@Customer_Number

select @exist=count(token) from MHToken where token =@token

-- flag ==> 1=new token, 2=refresh token, 3 = terminate token

if @flag=1 and @Exist_Customer=0 --creates a new token
	begin
		insert into MHToken (customer_number,token,isValid,crtd_datetime,Expiration_DateTime) values(@customer_number,@mytoken,'Y',getdate(),dateadd(second,600,getdate()))
		SELECT '200' STATUS,CUSTOMER_NUMBER, TOKEN,EXPIRATION_DATETIME FROM MHTOKEN WHERE TOKEN=@MYTOKEN
	end
else if @flag=1 and @Exist_Customer=1 --return current token
	begin 
		update MHToken set Token=@MYTOKEN, Crtd_DateTime=getdate(),Expiration_DateTime=dateadd(second,600,getdate()) where customer_number=@Customer_Number 
		SELECT '200' STATUS,CUSTOMER_NUMBER, TOKEN,EXPIRATION_DATETIME FROM MHTOKEN WHERE customer_number=@Customer_Number
	end
	else if @flag=2 and @Exist_Customer=1 and @exist=1 --extends the token life by 10 minutes
			begin
				update MHToken set Expiration_DateTime=dateadd(second,600,getdate())where token=@token
				SELECT '200' STATUS,CUSTOMER_NUMBER, TOKEN,EXPIRATION_DATETIME FROM MHTOKEN WHERE TOKEN=@MYTOKEN
			end
		else if @flag=3 and @Exist_Customer=1 and @exist=1
				begin
					update MHToken set isValid='N', Expiration_DateTime=getdate() where token=@token
				SELECT '200' STATUS,CUSTOMER_NUMBER, TOKEN,EXPIRATION_DATETIME FROM MHTOKEN WHERE TOKEN=@MYTOKEN
				end
			else
				begin
					SELECT '401' STATUS,@CUSTOMER_NUMBER AS CUSTOMER_NUMBER, 'BAD TOKEN' AS TOKEN, GETDATE() CRTD_DATETIME
				end
GO
/****** Object:  StoredProcedure [dbo].[sp_otp]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_otp] 
as
--SET NOCOUNT ON;  
declare @OTP int;
set @OTP = (select CONVERT(NUMERIC(12,0),RAND() * 99999) + 10000 );
--print @otp

if len(@OTP)=5
	begin 
		set @OTP= @OTP*10
	end
return @OTP
GO
/****** Object:  StoredProcedure [dbo].[sp_UpdateCart]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_UpdateCart]
@Flag int,
@CustNo varchar(15),
@Token varchar(50),
@InvoiceNo varchar(10)

as
--Flag==> 1= All items in the customer cart, 2 = Specified invoice from customer cart


declare @Exist as int,@AllRecs as int

select @Exist= count(*) from Temp_MH_Transactions where Token=@Token and CUSTOMER_ID=@CustNo and INVOICE_NO=@InvoiceNo
select @AllRecs=count(*) from Temp_MH_Transactions where Token=@Token and CUSTOMER_ID=@CustNo 	

if @Flag=1 and @AllRecs>0 
	begin
		delete from Temp_MH_Transactions where Token=@Token and CUSTOMER_ID=@CustNo and INVOICE_NO=@InvoiceNo
		select @Flag as Flag, 200 as Status
	end
else if @Flag=1 and @AllRecs=0 
	begin
		select @Flag as Flag, 404 as Status
	end
else if @Flag=2 and @Exist=1 
	begin
		delete from Temp_MH_Transactions where Token=@Token and CUSTOMER_ID=@CustNo and INVOICE_NO=@InvoiceNo
		select @Flag as Flag, 200 as Status
	end
else if @Flag=2 and @Exist=0
	begin
		select 404 as Status
	end
else
	begin
		select @Flag as Flag, 410 as Status
	end
	
GO
/****** Object:  StoredProcedure [dbo].[sp_verify_user]    Script Date: 06/02/2024 11:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[spValidateOTP]
@CustNum varchar(15),
@OTP int

AS

declare @Exist int

select @Exist = count(Customer_Number) 
from MH_Users 
where 0=0
and Customer_Number = @CustNum
and OTP = @OTP

if @Exist=1
	begin
		Select '200' as Status
	end
else
	begin
		Select '408' as Status
	end


GO
USE [master]
GO
ALTER DATABASE [MyHawk] SET  READ_WRITE 
GO
