Business ERP Part 1 Database
Hi, Dear's here we learn how to implement Auto Time Table Generator in Visual Studio using C# Windows Form. Ilyasoft software company provide full project step by step training on our YouTube Channel ilyasoft software company so now subscribe, share and like for more project base tutorials
Sql-Server > Go to Sql Server and click new query and then watch video.
USE [master]
GO
/****** Object: Database [CloudErpV1] Script Date: 09/08/2021 1:01:43 PM ******/
CREATE DATABASE [CloudErpV1]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'CloudErpV1', FILENAME = N'D:\FYP Company Mgt Sys(Cloud ERP) Small Business\Database\CloudErpV1.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'CloudErpV1_log', FILENAME = N'D:\FYP Company Mgt Sys(Cloud ERP) Small Business\Database\CloudErpV1_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [CloudErpV1] SET COMPATIBILITY_LEVEL = 120
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [CloudErpV1].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [CloudErpV1] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [CloudErpV1] SET ANSI_NULLS OFF
GO
ALTER DATABASE [CloudErpV1] SET ANSI_PADDING OFF
GO
ALTER DATABASE [CloudErpV1] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [CloudErpV1] SET ARITHABORT OFF
GO
ALTER DATABASE [CloudErpV1] SET AUTO_CLOSE ON
GO
ALTER DATABASE [CloudErpV1] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [CloudErpV1] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [CloudErpV1] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [CloudErpV1] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [CloudErpV1] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [CloudErpV1] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [CloudErpV1] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [CloudErpV1] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [CloudErpV1] SET DISABLE_BROKER
GO
ALTER DATABASE [CloudErpV1] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [CloudErpV1] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [CloudErpV1] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [CloudErpV1] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [CloudErpV1] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [CloudErpV1] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [CloudErpV1] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [CloudErpV1] SET RECOVERY SIMPLE
GO
ALTER DATABASE [CloudErpV1] SET MULTI_USER
GO
ALTER DATABASE [CloudErpV1] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [CloudErpV1] SET DB_CHAINING OFF
GO
ALTER DATABASE [CloudErpV1] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [CloudErpV1] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [CloudErpV1] SET DELAYED_DURABILITY = DISABLED
GO
USE [CloudErpV1]
GO
/****** Object: Table [dbo].[tblAccountActivity] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblAccountActivity](
[AccountActivityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_tblAccountActivity] PRIMARY KEY CLUSTERED
(
[AccountActivityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblAccountControl] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblAccountControl](
[AccountControlID] [int] IDENTITY(1,1) NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[AccountHeadID] [int] NOT NULL,
[AccountControlName] [varchar](50) NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblAccountControl] PRIMARY KEY CLUSTERED
(
[AccountControlID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblAccountHead] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblAccountHead](
[AccountHeadID] [int] IDENTITY(1,1) NOT NULL,
[AccountHeadName] [varchar](50) NOT NULL,
[Code] [int] NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblAccountHead] PRIMARY KEY CLUSTERED
(
[AccountHeadID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblAccountSetting] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblAccountSetting](
[AccountSettingID] [int] IDENTITY(1,1) NOT NULL,
[AccountHeadID] [int] NOT NULL,
[AccountControlID] [int] NOT NULL,
[AccountSubControlID] [int] NOT NULL CONSTRAINT [DF_tblAccountSetting_AccountSubControlID] DEFAULT ((0)),
[AccountActivityID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
CONSTRAINT [PK_tblAccountSetting] PRIMARY KEY CLUSTERED
(
[AccountSettingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblAccountSubControl] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblAccountSubControl](
[AccountSubControlID] [int] IDENTITY(1,1) NOT NULL,
[AccountHeadID] [int] NOT NULL,
[AccountControlID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[AccountSubControlName] [varchar](50) NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblAccountSubControl] PRIMARY KEY CLUSTERED
(
[AccountSubControlID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblAgent] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblAgent](
[AgentID] [int] NOT NULL,
[UserID] [int] NULL,
[AgentName] [nvarchar](250) NOT NULL,
[ContactNo] [nvarchar](50) NOT NULL,
[PhoneNo] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[Email] [nvarchar](250) NOT NULL,
[Agent_Commission] [float] NOT NULL,
[CountryID] [int] NOT NULL,
[StateID] [int] NOT NULL,
[CityID] [int] NOT NULL,
[BranchID] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblBranch] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblBranch](
[BranchID] [int] IDENTITY(1,1) NOT NULL,
[BranchTypeID] [int] NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[BranchContact] [nvarchar](50) NOT NULL,
[BranchAddress] [varchar](300) NOT NULL,
[CompanyID] [int] NOT NULL,
[BrchID] [int] NULL,
CONSTRAINT [PK_tblBranch] PRIMARY KEY CLUSTERED
(
[BranchID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblBranchType] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblBranchType](
[BranchTypeID] [int] IDENTITY(1,1) NOT NULL,
[BranchType] [varchar](50) NOT NULL,
CONSTRAINT [PK_tblBranchType] PRIMARY KEY CLUSTERED
(
[BranchTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblCategory] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCategory](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[categoryName] [varchar](50) NOT NULL,
[BranchID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblCategory] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblCity] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCity](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[CityName] [nvarchar](150) NOT NULL,
[StateID] [int] NOT NULL,
[CountryID] [int] NOT NULL,
[CityZone] [nvarchar](150) NOT NULL,
[CityCode] [nvarchar](50) NULL,
[CityPinCode] [nvarchar](50) NULL,
CONSTRAINT [PK_tblCity] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCompany](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[Logo] [nvarchar](200) NULL,
CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblCountry] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCountry](
[CountryID] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [nvarchar](250) NOT NULL,
[Photo] [nvarchar](250) NULL,
[Title] [nvarchar](50) NOT NULL,
[Keyword] [nvarchar](50) NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblCountry] PRIMARY KEY CLUSTERED
(
[CountryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblCustomer] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCustomer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[Customername] [varchar](150) NOT NULL,
[CustomerContact] [nvarchar](150) NOT NULL,
[CustomerArea] [varchar](50) NOT NULL,
[CustomerAddress] [varchar](300) NOT NULL,
[Description] [varchar](300) NOT NULL,
[BranchID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblCustomerInvoice] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCustomerInvoice](
[CustomerInvoiceID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[InvoiceNo] [nvarchar](150) NOT NULL,
[Title] [nvarchar](150) NOT NULL,
[TotalAmount] [float] NOT NULL,
[InvoiceDate] [date] NOT NULL,
[Description] [varchar](500) NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblCustomerInvoice] PRIMARY KEY CLUSTERED
(
[CustomerInvoiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblCustomerInvoiceDetail] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCustomerInvoiceDetail](
[CustomerInvoiceDetailID] [int] IDENTITY(1,1) NOT NULL,
[CustomerInvoiceID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[SaleQuantity] [int] NOT NULL,
[SaleUnitPrice] [float] NOT NULL,
CONSTRAINT [PK_tblCustomerInvoiceDetail] PRIMARY KEY CLUSTERED
(
[CustomerInvoiceDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblCustomerPayment] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCustomerPayment](
[CustomerPaymentID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[CustomerInvoiceID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[invoiceNo] [nvarchar](150) NOT NULL,
[TotalAmount] [float] NOT NULL,
[PaidAmount] [float] NOT NULL,
[RemainingBalance] [float] NOT NULL,
[UserID] [int] NOT NULL,
[InvoiceDate] [date] NULL,
CONSTRAINT [PK_tblCustomerPayment] PRIMARY KEY CLUSTERED
(
[CustomerPaymentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblCustomerReturnInvoice] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCustomerReturnInvoice](
[CustomerReturnInvoiceID] [int] IDENTITY(1,1) NOT NULL,
[CustomerInvoiceID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[InvoiceNo] [nvarchar](150) NOT NULL,
[TotalAmount] [float] NOT NULL,
[InvoiceDate] [date] NOT NULL,
[Description] [nvarchar](500) NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblCustomerReturnInvoice] PRIMARY KEY CLUSTERED
(
[CustomerReturnInvoiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblCustomerReturnInvoiceDetail] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCustomerReturnInvoiceDetail](
[CustomerReturnInvoiceDetailID] [int] IDENTITY(1,1) NOT NULL,
[CustomerInvoiceDetailID] [int] NOT NULL,
[CustomerInvoiceID] [int] NOT NULL,
[CustomerReturnInvoiceID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[SaleReturnQuantity] [int] NOT NULL,
[SaleReturnUnitPrice] [float] NOT NULL,
CONSTRAINT [PK_tblCustomerReturnInvoiceDetail] PRIMARY KEY CLUSTERED
(
[CustomerReturnInvoiceDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblCustomerReturnPayment] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCustomerReturnPayment](
[CustomerReturnPaymentID] [int] IDENTITY(1,1) NOT NULL,
[CustomerReturnInvoiceID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[CustomerInvoiceID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[InvoiceNo] [nvarchar](150) NOT NULL,
[TotalAmount] [float] NOT NULL,
[PaidAmount] [float] NOT NULL,
[RemainingBalance] [float] NOT NULL,
[UserID] [int] NOT NULL,
[InvoiceDate] [date] NULL,
CONSTRAINT [PK_tblCustomerReturnPayment] PRIMARY KEY CLUSTERED
(
[CustomerReturnPaymentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblEmployee] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblEmployee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](150) NOT NULL,
[ContactNo] [nvarchar](50) NOT NULL,
[Photo] [nvarchar](150) NULL,
[Email] [nvarchar](150) NOT NULL,
[Address] [varchar](300) NOT NULL,
[CNIC] [nvarchar](50) NOT NULL,
[Designation] [nvarchar](150) NOT NULL,
[Description] [nvarchar](500) NOT NULL,
[MonthlySalary] [float] NOT NULL,
[BranchID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[UserID] [int] NULL,
CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblFinancialYear] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblFinancialYear](
[FinancialYearID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[FinancialYear] [nvarchar](150) NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_tblFinancialYear_IsActive] DEFAULT ((0)),
CONSTRAINT [PK_tblFinancialYear] PRIMARY KEY CLUSTERED
(
[FinancialYearID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPayroll] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblPayroll](
[PayrollID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[TransferAmount] [float] NOT NULL,
[PayrollInvoiceNo] [nvarchar](150) NOT NULL,
[PaymentDate] [date] NOT NULL,
[SalaryMonth] [nvarchar](50) NOT NULL,
[SalaryYear] [nvarchar](50) NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblPayroll] PRIMARY KEY CLUSTERED
(
[PayrollID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPurchaseCartDetail] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblPurchaseCartDetail](
[PurchaseCartDetailID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[PurchaseQuantity] [int] NOT NULL,
[purchaseUnitPrice] [float] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblPurchaseCartDetailTable] PRIMARY KEY CLUSTERED
(
[PurchaseCartDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblSaleCartDetail] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSaleCartDetail](
[SaleCartDetailID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[SaleQuantity] [int] NOT NULL,
[SaleUnitPrice] [float] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblSaleCartDetail] PRIMARY KEY CLUSTERED
(
[SaleCartDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblState] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblState](
[StateID] [int] IDENTITY(1,1) NOT NULL,
[StateName] [nvarchar](250) NOT NULL,
[CountryID] [int] NOT NULL,
CONSTRAINT [PK_tblState] PRIMARY KEY CLUSTERED
(
[StateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblStock] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblStock](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[ProductName] [nvarchar](80) NOT NULL,
[Quantity] [int] NOT NULL,
[SaleUnitPrice] [float] NOT NULL,
[CurrentPurchaseUnitPrice] [float] NOT NULL,
[ExpiryDate] [date] NOT NULL,
[Manufacture] [date] NOT NULL,
[StockTreshHoldQuantity] [int] NOT NULL,
[Description] [nvarchar](300) NULL,
[UserID] [int] NOT NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_tblStock_IsActive] DEFAULT ((0)),
CONSTRAINT [PK_tblStock] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblSupplier] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSupplier](
[SupplierID] [int] IDENTITY(1,1) NOT NULL,
[SupplierName] [nvarchar](150) NOT NULL,
[SupplierConatctNo] [nvarchar](20) NOT NULL,
[SupplierAddress] [nvarchar](150) NULL,
[SupplierEmail] [nvarchar](150) NULL,
[Discription] [nvarchar](300) NULL,
[BranchID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblSupplier] PRIMARY KEY CLUSTERED
(
[SupplierID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblSupplierInvoice] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSupplierInvoice](
[SupplierInvoiceID] [int] IDENTITY(1,1) NOT NULL,
[SupplierID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[InvoiceNo] [nvarchar](150) NOT NULL,
[TotalAmount] [float] NOT NULL,
[InvoiceDate] [date] NOT NULL,
[Description] [nvarchar](150) NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblSupplierInvoiceTable] PRIMARY KEY CLUSTERED
(
[SupplierInvoiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblSupplierInvoiceDetail] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSupplierInvoiceDetail](
[SupplierInvoiceDetailID] [int] IDENTITY(1,1) NOT NULL,
[SupplierInvoiceID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[PurchaseQuantity] [int] NOT NULL,
[purchaseUnitPrice] [float] NOT NULL,
CONSTRAINT [PK_tblSupplierInvoiceDetailTable] PRIMARY KEY CLUSTERED
(
[SupplierInvoiceDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblSupplierPayment] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSupplierPayment](
[SupplierPaymentID] [int] IDENTITY(1,1) NOT NULL,
[SupplierID] [int] NOT NULL,
[SupplierInvoiceID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[InvoiceNo] [nvarchar](150) NOT NULL,
[TotalAmount] [float] NOT NULL,
[PaymentAmount] [float] NOT NULL,
[RemainingBalance] [float] NOT NULL,
[UserID] [int] NOT NULL,
[InvoiceDate] [date] NULL,
CONSTRAINT [PK_tblSupplierPayment] PRIMARY KEY CLUSTERED
(
[SupplierPaymentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblSupplierReturnInvoice] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSupplierReturnInvoice](
[SupplierReturnInvoiceID] [int] IDENTITY(1,1) NOT NULL,
[SupplierInvoiceID] [int] NOT NULL,
[SupplierID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[InvoiceNo] [nvarchar](100) NOT NULL,
[TotalAmount] [float] NOT NULL,
[InvoiceDate] [date] NOT NULL,
[Description] [nvarchar](500) NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_tblSupplierReturnInvoice] PRIMARY KEY CLUSTERED
(
[SupplierReturnInvoiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblSupplierReturnInvoiceDetail] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSupplierReturnInvoiceDetail](
[SupplierReturnInvoiceDetailID] [int] IDENTITY(1,1) NOT NULL,
[SupplierInvoiceID] [int] NOT NULL,
[SupplierReturnInvoiceID] [int] NOT NULL,
[SupplierInvoiceDetailID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[PurchaseReturnQuantity] [int] NOT NULL,
[PurchaseReturnUnitPrice] [float] NOT NULL,
CONSTRAINT [PK_tblSupplierReturnInvoiceDetail] PRIMARY KEY CLUSTERED
(
[SupplierReturnInvoiceDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblSupplierReturnPayment] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSupplierReturnPayment](
[SupplierReturnPaymentID] [int] IDENTITY(1,1) NOT NULL,
[SupplierReturnInvoiceID] [int] NOT NULL,
[SupplierInvoiceID] [int] NOT NULL,
[SupplierID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[InvoiceNo] [nvarchar](150) NOT NULL,
[TotalAmount] [float] NOT NULL,
[PaymentAmount] [float] NOT NULL,
[RemainingBalance] [float] NOT NULL,
[UserID] [int] NOT NULL,
[InvoiceDate] [date] NULL,
CONSTRAINT [PK_tblSupplierReturnPayment] PRIMARY KEY CLUSTERED
(
[SupplierReturnPaymentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblTransaction] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTransaction](
[TransactionID] [int] IDENTITY(1,1) NOT NULL,
[FinancialYearID] [int] NOT NULL,
[AccountHeadID] [int] NOT NULL,
[AccountControlID] [int] NOT NULL,
[AccountSubControlID] [int] NOT NULL,
[InvoiceNo] [nvarchar](150) NOT NULL,
[CompanyID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[Credit] [float] NOT NULL,
[Debit] [float] NOT NULL,
[TransectionDate] [datetime] NOT NULL,
[TransectionTitle] [nvarchar](150) NOT NULL,
[UserID] [int] NOT NULL,
CONSTRAINT [PK_TransectionTable] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblUser] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblUser](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserTypeID] [int] NOT NULL,
[FullName] [nvarchar](150) NOT NULL,
[Email] [nvarchar](150) NOT NULL,
[ContactNo] [nvarchar](20) NOT NULL,
[UserName] [nvarchar](150) NOT NULL,
[Password] [nvarchar](150) NOT NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_tblUser_IsActive] DEFAULT ((0)),
CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblUserType] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblUserType](
[UserTypeID] [int] IDENTITY(1,1) NOT NULL,
[UserType] [nvarchar](150) NOT NULL,
CONSTRAINT [PK_tblUserType] PRIMARY KEY CLUSTERED
(
[UserTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: View [dbo].[v_Transaction] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_Transaction]
AS
SELECT dbo.tblTransaction.TransactionID, dbo.tblTransaction.FinancialYearID, dbo.tblTransaction.AccountHeadID, dbo.tblTransaction.AccountControlID, dbo.tblTransaction.AccountSubControlID,
dbo.tblAccountHead.AccountHeadName + '-/-' + dbo.tblAccountControl.AccountControlName + '-/-' + dbo.tblAccountSubControl.AccountSubControlName AS AccountTitle, dbo.tblTransaction.InvoiceNo,
dbo.tblTransaction.CompanyID, dbo.tblTransaction.BranchID, dbo.tblTransaction.Debit, dbo.tblTransaction.Credit, dbo.tblTransaction.TransectionDate, dbo.tblTransaction.TransectionTitle, dbo.tblTransaction.UserID
FROM dbo.tblTransaction INNER JOIN
dbo.tblAccountSubControl ON dbo.tblTransaction.AccountSubControlID = dbo.tblAccountSubControl.AccountSubControlID INNER JOIN
dbo.tblAccountHead ON dbo.tblTransaction.AccountHeadID = dbo.tblAccountHead.AccountHeadID AND dbo.tblTransaction.AccountHeadID = dbo.tblAccountHead.AccountHeadID INNER JOIN
dbo.tblAccountControl ON dbo.tblTransaction.AccountControlID = dbo.tblAccountControl.AccountControlID
GO
ALTER TABLE [dbo].[tblAccountControl] WITH CHECK ADD CONSTRAINT [FK_tblAccountControl_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblAccountControl] CHECK CONSTRAINT [FK_tblAccountControl_tblBranch]
GO
ALTER TABLE [dbo].[tblAccountControl] WITH CHECK ADD CONSTRAINT [FK_tblAccountControl_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblAccountControl] CHECK CONSTRAINT [FK_tblAccountControl_tblCompany]
GO
ALTER TABLE [dbo].[tblAccountControl] WITH CHECK ADD CONSTRAINT [FK_tblAccountControl_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblAccountControl] CHECK CONSTRAINT [FK_tblAccountControl_tblUser]
GO
ALTER TABLE [dbo].[tblAccountHead] WITH CHECK ADD CONSTRAINT [FK_tblAccountHead_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblAccountHead] CHECK CONSTRAINT [FK_tblAccountHead_tblUser]
GO
ALTER TABLE [dbo].[tblAccountSetting] WITH CHECK ADD CONSTRAINT [FK_tblAccountSetting_tblAccountActivity] FOREIGN KEY([AccountActivityID])
REFERENCES [dbo].[tblAccountActivity] ([AccountActivityID])
GO
ALTER TABLE [dbo].[tblAccountSetting] CHECK CONSTRAINT [FK_tblAccountSetting_tblAccountActivity]
GO
ALTER TABLE [dbo].[tblAccountSetting] WITH CHECK ADD CONSTRAINT [FK_tblAccountSetting_tblAccountControl] FOREIGN KEY([AccountControlID])
REFERENCES [dbo].[tblAccountControl] ([AccountControlID])
GO
ALTER TABLE [dbo].[tblAccountSetting] CHECK CONSTRAINT [FK_tblAccountSetting_tblAccountControl]
GO
ALTER TABLE [dbo].[tblAccountSetting] WITH CHECK ADD CONSTRAINT [FK_tblAccountSetting_tblAccountHead] FOREIGN KEY([AccountHeadID])
REFERENCES [dbo].[tblAccountHead] ([AccountHeadID])
GO
ALTER TABLE [dbo].[tblAccountSetting] CHECK CONSTRAINT [FK_tblAccountSetting_tblAccountHead]
GO
ALTER TABLE [dbo].[tblAccountSetting] WITH NOCHECK ADD CONSTRAINT [FK_tblAccountSetting_tblAccountSubControl] FOREIGN KEY([AccountSubControlID])
REFERENCES [dbo].[tblAccountSubControl] ([AccountSubControlID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tblAccountSetting] NOCHECK CONSTRAINT [FK_tblAccountSetting_tblAccountSubControl]
GO
ALTER TABLE [dbo].[tblAccountSetting] WITH CHECK ADD CONSTRAINT [FK_tblAccountSetting_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblAccountSetting] CHECK CONSTRAINT [FK_tblAccountSetting_tblBranch]
GO
ALTER TABLE [dbo].[tblAccountSetting] WITH CHECK ADD CONSTRAINT [FK_tblAccountSetting_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblAccountSetting] CHECK CONSTRAINT [FK_tblAccountSetting_tblCompany]
GO
ALTER TABLE [dbo].[tblAccountSubControl] WITH CHECK ADD CONSTRAINT [FK_tblAccountSubControl_tblAccountControl] FOREIGN KEY([AccountControlID])
REFERENCES [dbo].[tblAccountControl] ([AccountControlID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblAccountSubControl] CHECK CONSTRAINT [FK_tblAccountSubControl_tblAccountControl]
GO
ALTER TABLE [dbo].[tblAccountSubControl] WITH CHECK ADD CONSTRAINT [FK_tblAccountSubControl_tblAccountHead] FOREIGN KEY([AccountHeadID])
REFERENCES [dbo].[tblAccountHead] ([AccountHeadID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblAccountSubControl] CHECK CONSTRAINT [FK_tblAccountSubControl_tblAccountHead]
GO
ALTER TABLE [dbo].[tblAccountSubControl] WITH CHECK ADD CONSTRAINT [FK_tblAccountSubControl_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblAccountSubControl] CHECK CONSTRAINT [FK_tblAccountSubControl_tblBranch]
GO
ALTER TABLE [dbo].[tblAccountSubControl] WITH CHECK ADD CONSTRAINT [FK_tblAccountSubControl_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblAccountSubControl] CHECK CONSTRAINT [FK_tblAccountSubControl_tblUser]
GO
ALTER TABLE [dbo].[tblBranch] WITH CHECK ADD CONSTRAINT [FK_tblBranch_tblBranchType] FOREIGN KEY([BranchTypeID])
REFERENCES [dbo].[tblBranchType] ([BranchTypeID])
GO
ALTER TABLE [dbo].[tblBranch] CHECK CONSTRAINT [FK_tblBranch_tblBranchType]
GO
ALTER TABLE [dbo].[tblCategory] WITH CHECK ADD CONSTRAINT [FK_tblCategory_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblCategory] CHECK CONSTRAINT [FK_tblCategory_tblBranch]
GO
ALTER TABLE [dbo].[tblCategory] WITH CHECK ADD CONSTRAINT [FK_tblCategory_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblCategory] CHECK CONSTRAINT [FK_tblCategory_tblCompany]
GO
ALTER TABLE [dbo].[tblCategory] WITH CHECK ADD CONSTRAINT [FK_tblCategory_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblCategory] CHECK CONSTRAINT [FK_tblCategory_tblUser]
GO
ALTER TABLE [dbo].[tblCustomer] WITH CHECK ADD CONSTRAINT [FK_tblCustomer_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblCustomer] CHECK CONSTRAINT [FK_tblCustomer_tblBranch]
GO
ALTER TABLE [dbo].[tblCustomer] WITH CHECK ADD CONSTRAINT [FK_tblCustomer_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblCustomer] CHECK CONSTRAINT [FK_tblCustomer_tblCompany]
GO
ALTER TABLE [dbo].[tblCustomer] WITH CHECK ADD CONSTRAINT [FK_tblCustomer_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblCustomer] CHECK CONSTRAINT [FK_tblCustomer_tblUser]
GO
ALTER TABLE [dbo].[tblCustomerInvoice] WITH CHECK ADD CONSTRAINT [FK_tblCustomerInvoice_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblCustomerInvoice] CHECK CONSTRAINT [FK_tblCustomerInvoice_tblBranch]
GO
ALTER TABLE [dbo].[tblCustomerInvoice] WITH CHECK ADD CONSTRAINT [FK_tblCustomerInvoice_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblCustomerInvoice] CHECK CONSTRAINT [FK_tblCustomerInvoice_tblCompany]
GO
ALTER TABLE [dbo].[tblCustomerInvoice] WITH CHECK ADD CONSTRAINT [FK_tblCustomerInvoice_tblCustomer] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[tblCustomer] ([CustomerID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblCustomerInvoice] CHECK CONSTRAINT [FK_tblCustomerInvoice_tblCustomer]
GO
ALTER TABLE [dbo].[tblCustomerInvoice] WITH CHECK ADD CONSTRAINT [FK_tblCustomerInvoice_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblCustomerInvoice] CHECK CONSTRAINT [FK_tblCustomerInvoice_tblUser]
GO
ALTER TABLE [dbo].[tblCustomerInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblCustomerInvoiceDetail_tblCustomerInvoice] FOREIGN KEY([CustomerInvoiceID])
REFERENCES [dbo].[tblCustomerInvoice] ([CustomerInvoiceID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblCustomerInvoiceDetail] CHECK CONSTRAINT [FK_tblCustomerInvoiceDetail_tblCustomerInvoice]
GO
ALTER TABLE [dbo].[tblCustomerInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblCustomerInvoiceDetail_tblStock] FOREIGN KEY([ProductID])
REFERENCES [dbo].[tblStock] ([ProductID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblCustomerInvoiceDetail] CHECK CONSTRAINT [FK_tblCustomerInvoiceDetail_tblStock]
GO
ALTER TABLE [dbo].[tblCustomerPayment] WITH CHECK ADD CONSTRAINT [FK_tblCustomerPayment_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblCustomerPayment] CHECK CONSTRAINT [FK_tblCustomerPayment_tblBranch]
GO
ALTER TABLE [dbo].[tblCustomerPayment] WITH CHECK ADD CONSTRAINT [FK_tblCustomerPayment_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblCustomerPayment] CHECK CONSTRAINT [FK_tblCustomerPayment_tblCompany]
GO
ALTER TABLE [dbo].[tblCustomerPayment] WITH CHECK ADD CONSTRAINT [FK_tblCustomerPayment_tblCustomerInvoice] FOREIGN KEY([CustomerInvoiceID])
REFERENCES [dbo].[tblCustomerInvoice] ([CustomerInvoiceID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblCustomerPayment] CHECK CONSTRAINT [FK_tblCustomerPayment_tblCustomerInvoice]
GO
ALTER TABLE [dbo].[tblCustomerPayment] WITH CHECK ADD CONSTRAINT [FK_tblCustomerPayment_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblCustomerPayment] CHECK CONSTRAINT [FK_tblCustomerPayment_tblUser]
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoice] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnInvoice_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoice] CHECK CONSTRAINT [FK_tblCustomerReturnInvoice_tblBranch]
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoice] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnInvoice_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoice] CHECK CONSTRAINT [FK_tblCustomerReturnInvoice_tblCompany]
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoice] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnInvoice_tblCustomer] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[tblCustomer] ([CustomerID])
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoice] CHECK CONSTRAINT [FK_tblCustomerReturnInvoice_tblCustomer]
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoice] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnInvoice_tblCustomerInvoice] FOREIGN KEY([CustomerInvoiceID])
REFERENCES [dbo].[tblCustomerInvoice] ([CustomerInvoiceID])
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoice] CHECK CONSTRAINT [FK_tblCustomerReturnInvoice_tblCustomerInvoice]
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoice] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnInvoice_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoice] CHECK CONSTRAINT [FK_tblCustomerReturnInvoice_tblUser]
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnInvoiceDetail_tblCustomerInvoice] FOREIGN KEY([CustomerInvoiceID])
REFERENCES [dbo].[tblCustomerInvoice] ([CustomerInvoiceID])
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoiceDetail] CHECK CONSTRAINT [FK_tblCustomerReturnInvoiceDetail_tblCustomerInvoice]
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnInvoiceDetail_tblCustomerInvoiceDetail] FOREIGN KEY([CustomerInvoiceDetailID])
REFERENCES [dbo].[tblCustomerInvoiceDetail] ([CustomerInvoiceDetailID])
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoiceDetail] CHECK CONSTRAINT [FK_tblCustomerReturnInvoiceDetail_tblCustomerInvoiceDetail]
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnInvoiceDetail_tblCustomerReturnInvoice] FOREIGN KEY([CustomerReturnInvoiceID])
REFERENCES [dbo].[tblCustomerReturnInvoice] ([CustomerReturnInvoiceID])
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoiceDetail] CHECK CONSTRAINT [FK_tblCustomerReturnInvoiceDetail_tblCustomerReturnInvoice]
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnInvoiceDetail_tblStock] FOREIGN KEY([ProductID])
REFERENCES [dbo].[tblStock] ([ProductID])
GO
ALTER TABLE [dbo].[tblCustomerReturnInvoiceDetail] CHECK CONSTRAINT [FK_tblCustomerReturnInvoiceDetail_tblStock]
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnPayment_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] CHECK CONSTRAINT [FK_tblCustomerReturnPayment_tblBranch]
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnPayment_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] CHECK CONSTRAINT [FK_tblCustomerReturnPayment_tblCompany]
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnPayment_tblCustomer] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[tblCustomer] ([CustomerID])
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] CHECK CONSTRAINT [FK_tblCustomerReturnPayment_tblCustomer]
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnPayment_tblCustomerInvoice] FOREIGN KEY([CustomerInvoiceID])
REFERENCES [dbo].[tblCustomerInvoice] ([CustomerInvoiceID])
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] CHECK CONSTRAINT [FK_tblCustomerReturnPayment_tblCustomerInvoice]
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnPayment_tblCustomerReturnInvoice] FOREIGN KEY([CustomerReturnInvoiceID])
REFERENCES [dbo].[tblCustomerReturnInvoice] ([CustomerReturnInvoiceID])
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] CHECK CONSTRAINT [FK_tblCustomerReturnPayment_tblCustomerReturnInvoice]
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblCustomerReturnPayment_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblCustomerReturnPayment] CHECK CONSTRAINT [FK_tblCustomerReturnPayment_tblUser]
GO
ALTER TABLE [dbo].[tblEmployee] WITH CHECK ADD CONSTRAINT [FK_tblEmployee_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblEmployee] CHECK CONSTRAINT [FK_tblEmployee_tblBranch]
GO
ALTER TABLE [dbo].[tblEmployee] WITH CHECK ADD CONSTRAINT [FK_tblEmployee_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblEmployee] CHECK CONSTRAINT [FK_tblEmployee_tblCompany]
GO
ALTER TABLE [dbo].[tblFinancialYear] WITH CHECK ADD CONSTRAINT [FK_tblFinancialYear_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblFinancialYear] CHECK CONSTRAINT [FK_tblFinancialYear_tblUser]
GO
ALTER TABLE [dbo].[tblPayroll] WITH CHECK ADD CONSTRAINT [FK_tblPayroll_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblPayroll] CHECK CONSTRAINT [FK_tblPayroll_tblBranch]
GO
ALTER TABLE [dbo].[tblPayroll] WITH CHECK ADD CONSTRAINT [FK_tblPayroll_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblPayroll] CHECK CONSTRAINT [FK_tblPayroll_tblCompany]
GO
ALTER TABLE [dbo].[tblPayroll] WITH CHECK ADD CONSTRAINT [FK_tblPayroll_tblEmployee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[tblEmployee] ([EmployeeID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblPayroll] CHECK CONSTRAINT [FK_tblPayroll_tblEmployee]
GO
ALTER TABLE [dbo].[tblPayroll] WITH CHECK ADD CONSTRAINT [FK_tblPayroll_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblPayroll] CHECK CONSTRAINT [FK_tblPayroll_tblUser]
GO
ALTER TABLE [dbo].[tblPurchaseCartDetail] WITH CHECK ADD CONSTRAINT [FK_tblPurchaseCartDetail_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblPurchaseCartDetail] CHECK CONSTRAINT [FK_tblPurchaseCartDetail_tblBranch]
GO
ALTER TABLE [dbo].[tblPurchaseCartDetail] WITH CHECK ADD CONSTRAINT [FK_tblPurchaseCartDetail_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblPurchaseCartDetail] CHECK CONSTRAINT [FK_tblPurchaseCartDetail_tblCompany]
GO
ALTER TABLE [dbo].[tblPurchaseCartDetail] WITH CHECK ADD CONSTRAINT [FK_tblPurchaseCartDetail_tblStock] FOREIGN KEY([ProductID])
REFERENCES [dbo].[tblStock] ([ProductID])
GO
ALTER TABLE [dbo].[tblPurchaseCartDetail] CHECK CONSTRAINT [FK_tblPurchaseCartDetail_tblStock]
GO
ALTER TABLE [dbo].[tblPurchaseCartDetail] WITH CHECK ADD CONSTRAINT [FK_tblPurchaseCartDetail_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblPurchaseCartDetail] CHECK CONSTRAINT [FK_tblPurchaseCartDetail_tblUser]
GO
ALTER TABLE [dbo].[tblSaleCartDetail] WITH CHECK ADD CONSTRAINT [FK_tblSaleCartDetail_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblSaleCartDetail] CHECK CONSTRAINT [FK_tblSaleCartDetail_tblCompany]
GO
ALTER TABLE [dbo].[tblSaleCartDetail] WITH CHECK ADD CONSTRAINT [FK_tblSaleCartDetail_tblStock] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblSaleCartDetail] CHECK CONSTRAINT [FK_tblSaleCartDetail_tblStock]
GO
ALTER TABLE [dbo].[tblSaleCartDetail] WITH CHECK ADD CONSTRAINT [FK_tblSaleCartDetail_tblStock1] FOREIGN KEY([ProductID])
REFERENCES [dbo].[tblStock] ([ProductID])
GO
ALTER TABLE [dbo].[tblSaleCartDetail] CHECK CONSTRAINT [FK_tblSaleCartDetail_tblStock1]
GO
ALTER TABLE [dbo].[tblSaleCartDetail] WITH CHECK ADD CONSTRAINT [FK_tblSaleCartDetail_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblSaleCartDetail] CHECK CONSTRAINT [FK_tblSaleCartDetail_tblUser]
GO
ALTER TABLE [dbo].[tblStock] WITH CHECK ADD CONSTRAINT [FK_tblStock_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblStock] CHECK CONSTRAINT [FK_tblStock_tblBranch]
GO
ALTER TABLE [dbo].[tblStock] WITH CHECK ADD CONSTRAINT [FK_tblStock_tblCategory] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[tblCategory] ([CategoryID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblStock] CHECK CONSTRAINT [FK_tblStock_tblCategory]
GO
ALTER TABLE [dbo].[tblStock] WITH CHECK ADD CONSTRAINT [FK_tblStock_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblStock] CHECK CONSTRAINT [FK_tblStock_tblCompany]
GO
ALTER TABLE [dbo].[tblStock] WITH CHECK ADD CONSTRAINT [FK_tblStock_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblStock] CHECK CONSTRAINT [FK_tblStock_tblUser]
GO
ALTER TABLE [dbo].[tblSupplier] WITH CHECK ADD CONSTRAINT [FK_tblSupplier_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblSupplier] CHECK CONSTRAINT [FK_tblSupplier_tblBranch]
GO
ALTER TABLE [dbo].[tblSupplier] WITH CHECK ADD CONSTRAINT [FK_tblSupplier_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblSupplier] CHECK CONSTRAINT [FK_tblSupplier_tblCompany]
GO
ALTER TABLE [dbo].[tblSupplier] WITH CHECK ADD CONSTRAINT [FK_tblSupplier_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblSupplier] CHECK CONSTRAINT [FK_tblSupplier_tblUser]
GO
ALTER TABLE [dbo].[tblSupplierInvoice] WITH CHECK ADD CONSTRAINT [FK_tblSupplierInvoice_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblSupplierInvoice] CHECK CONSTRAINT [FK_tblSupplierInvoice_tblUser]
GO
ALTER TABLE [dbo].[tblSupplierInvoice] WITH CHECK ADD CONSTRAINT [FK_tblSupplierInvoiceTable_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblSupplierInvoice] CHECK CONSTRAINT [FK_tblSupplierInvoiceTable_tblBranch]
GO
ALTER TABLE [dbo].[tblSupplierInvoice] WITH CHECK ADD CONSTRAINT [FK_tblSupplierInvoiceTable_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblSupplierInvoice] CHECK CONSTRAINT [FK_tblSupplierInvoiceTable_tblCompany]
GO
ALTER TABLE [dbo].[tblSupplierInvoice] WITH CHECK ADD CONSTRAINT [FK_tblSupplierInvoiceTable_tblSupplier] FOREIGN KEY([SupplierID])
REFERENCES [dbo].[tblSupplier] ([SupplierID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblSupplierInvoice] CHECK CONSTRAINT [FK_tblSupplierInvoiceTable_tblSupplier]
GO
ALTER TABLE [dbo].[tblSupplierInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblSupplierInvoiceDetail_tblStock] FOREIGN KEY([ProductID])
REFERENCES [dbo].[tblStock] ([ProductID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblSupplierInvoiceDetail] CHECK CONSTRAINT [FK_tblSupplierInvoiceDetail_tblStock]
GO
ALTER TABLE [dbo].[tblSupplierInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblSupplierInvoiceDetail_tblSupplierInvoice] FOREIGN KEY([SupplierInvoiceID])
REFERENCES [dbo].[tblSupplierInvoice] ([SupplierInvoiceID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblSupplierInvoiceDetail] CHECK CONSTRAINT [FK_tblSupplierInvoiceDetail_tblSupplierInvoice]
GO
ALTER TABLE [dbo].[tblSupplierPayment] WITH CHECK ADD CONSTRAINT [FK_tblSupplierPayment_tblSupplier] FOREIGN KEY([SupplierID])
REFERENCES [dbo].[tblSupplier] ([SupplierID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblSupplierPayment] CHECK CONSTRAINT [FK_tblSupplierPayment_tblSupplier]
GO
ALTER TABLE [dbo].[tblSupplierPayment] WITH CHECK ADD CONSTRAINT [FK_tblSupplierPayment_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblSupplierPayment] CHECK CONSTRAINT [FK_tblSupplierPayment_tblUser]
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoice] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnInvoice_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoice] CHECK CONSTRAINT [FK_tblSupplierReturnInvoice_tblBranch]
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoice] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnInvoice_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoice] CHECK CONSTRAINT [FK_tblSupplierReturnInvoice_tblCompany]
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoice] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnInvoice_tblSupplier] FOREIGN KEY([SupplierID])
REFERENCES [dbo].[tblSupplier] ([SupplierID])
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoice] CHECK CONSTRAINT [FK_tblSupplierReturnInvoice_tblSupplier]
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoice] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnInvoice_tblSupplierInvoice] FOREIGN KEY([SupplierInvoiceID])
REFERENCES [dbo].[tblSupplierInvoice] ([SupplierInvoiceID])
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoice] CHECK CONSTRAINT [FK_tblSupplierReturnInvoice_tblSupplierInvoice]
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoice] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnInvoice_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoice] CHECK CONSTRAINT [FK_tblSupplierReturnInvoice_tblUser]
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnInvoiceDetail_tblStock] FOREIGN KEY([ProductID])
REFERENCES [dbo].[tblStock] ([ProductID])
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoiceDetail] CHECK CONSTRAINT [FK_tblSupplierReturnInvoiceDetail_tblStock]
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnInvoiceDetail_tblSupplierInvoice] FOREIGN KEY([SupplierInvoiceID])
REFERENCES [dbo].[tblSupplierInvoice] ([SupplierInvoiceID])
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoiceDetail] CHECK CONSTRAINT [FK_tblSupplierReturnInvoiceDetail_tblSupplierInvoice]
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnInvoiceDetail_tblSupplierInvoiceDetail] FOREIGN KEY([SupplierInvoiceDetailID])
REFERENCES [dbo].[tblSupplierInvoiceDetail] ([SupplierInvoiceDetailID])
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoiceDetail] CHECK CONSTRAINT [FK_tblSupplierReturnInvoiceDetail_tblSupplierInvoiceDetail]
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoiceDetail] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnInvoiceDetail_tblSupplierReturnInvoice] FOREIGN KEY([SupplierReturnInvoiceID])
REFERENCES [dbo].[tblSupplierReturnInvoice] ([SupplierReturnInvoiceID])
GO
ALTER TABLE [dbo].[tblSupplierReturnInvoiceDetail] CHECK CONSTRAINT [FK_tblSupplierReturnInvoiceDetail_tblSupplierReturnInvoice]
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnPayment_tblBranch] FOREIGN KEY([BranchID])
REFERENCES [dbo].[tblBranch] ([BranchID])
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] CHECK CONSTRAINT [FK_tblSupplierReturnPayment_tblBranch]
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnPayment_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[tblCompany] ([CompanyID])
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] CHECK CONSTRAINT [FK_tblSupplierReturnPayment_tblCompany]
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnPayment_tblSupplier] FOREIGN KEY([SupplierID])
REFERENCES [dbo].[tblSupplier] ([SupplierID])
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] CHECK CONSTRAINT [FK_tblSupplierReturnPayment_tblSupplier]
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnPayment_tblSupplierInvoice] FOREIGN KEY([SupplierInvoiceID])
REFERENCES [dbo].[tblSupplierInvoice] ([SupplierInvoiceID])
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] CHECK CONSTRAINT [FK_tblSupplierReturnPayment_tblSupplierInvoice]
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnPayment_tblSupplierReturnInvoice] FOREIGN KEY([SupplierReturnInvoiceID])
REFERENCES [dbo].[tblSupplierReturnInvoice] ([SupplierReturnInvoiceID])
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] CHECK CONSTRAINT [FK_tblSupplierReturnPayment_tblSupplierReturnInvoice]
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] WITH CHECK ADD CONSTRAINT [FK_tblSupplierReturnPayment_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblSupplierReturnPayment] CHECK CONSTRAINT [FK_tblSupplierReturnPayment_tblUser]
GO
ALTER TABLE [dbo].[tblTransaction] WITH CHECK ADD CONSTRAINT [FK_tblTransaction_tblAccountControl] FOREIGN KEY([AccountControlID])
REFERENCES [dbo].[tblAccountControl] ([AccountControlID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblTransaction] CHECK CONSTRAINT [FK_tblTransaction_tblAccountControl]
GO
ALTER TABLE [dbo].[tblTransaction] WITH CHECK ADD CONSTRAINT [FK_tblTransaction_tblAccountHead] FOREIGN KEY([AccountHeadID])
REFERENCES [dbo].[tblAccountHead] ([AccountHeadID])
GO
ALTER TABLE [dbo].[tblTransaction] CHECK CONSTRAINT [FK_tblTransaction_tblAccountHead]
GO
ALTER TABLE [dbo].[tblTransaction] WITH CHECK ADD CONSTRAINT [FK_tblTransaction_tblFinancialYear] FOREIGN KEY([FinancialYearID])
REFERENCES [dbo].[tblFinancialYear] ([FinancialYearID])
GO
ALTER TABLE [dbo].[tblTransaction] CHECK CONSTRAINT [FK_tblTransaction_tblFinancialYear]
GO
ALTER TABLE [dbo].[tblTransaction] WITH CHECK ADD CONSTRAINT [FK_tblTransaction_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO
ALTER TABLE [dbo].[tblTransaction] CHECK CONSTRAINT [FK_tblTransaction_tblUser]
GO
ALTER TABLE [dbo].[tblTransaction] WITH CHECK ADD CONSTRAINT [FK_tblTransection_tblAccountHead] FOREIGN KEY([AccountHeadID])
REFERENCES [dbo].[tblAccountHead] ([AccountHeadID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblTransaction] CHECK CONSTRAINT [FK_tblTransection_tblAccountHead]
GO
ALTER TABLE [dbo].[tblTransaction] WITH CHECK ADD CONSTRAINT [FK_tblTransection_tblAccountSubControl] FOREIGN KEY([AccountSubControlID])
REFERENCES [dbo].[tblAccountSubControl] ([AccountSubControlID])
GO
ALTER TABLE [dbo].[tblTransaction] CHECK CONSTRAINT [FK_tblTransection_tblAccountSubControl]
GO
ALTER TABLE [dbo].[tblTransaction] WITH CHECK ADD CONSTRAINT [FK_tblTransection_tblFinancialYear] FOREIGN KEY([FinancialYearID])
REFERENCES [dbo].[tblFinancialYear] ([FinancialYearID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblTransaction] CHECK CONSTRAINT [FK_tblTransection_tblFinancialYear]
GO
ALTER TABLE [dbo].[tblUser] WITH CHECK ADD CONSTRAINT [FK_tblUser_tblUserType] FOREIGN KEY([UserTypeID])
REFERENCES [dbo].[tblUserType] ([UserTypeID])
GO
ALTER TABLE [dbo].[tblUser] CHECK CONSTRAINT [FK_tblUser_tblUserType]
GO
/****** Object: StoredProcedure [dbo].[GetAccountHeadDetials] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GetAccountHeadDetials](@HeadID AS INT, @FinancialYearID AS INT, @BranchID AS INT, @CompanyID AS INT)
AS
BEGIN
SELECT v_Transaction.AccountTitle,
CASE WHEN SUM(v_Transaction.Debit) > SUM(v_Transaction.Credit) THEN ISNULL(SUM(v_Transaction.Debit) - SUM(v_Transaction.Credit),0)
WHEN SUM(v_Transaction.Debit) < SUM(v_Transaction.Credit) THEN ISNULL(SUM(v_Transaction.Credit) - SUM(v_Transaction.Debit),0) ELSE 0 END AS [Total],
CASE WHEN SUM(v_Transaction.Debit) > SUM(v_Transaction.Credit) THEN 'Debit'
WHEN SUM(v_Transaction.Debit) < SUM(v_Transaction.Credit) THEN 'Credit' ELSE '0' END AS [Status]
FROM
v_Transaction WHERE v_Transaction.AccountHeadID = ISNULL(@HeadID,0) AND v_Transaction.FinancialYearID = ISNULL(@FinancialYearID,0) AND
v_Transaction.BranchID = @BranchID AND v_Transaction.CompanyID = @CompanyID GROUP BY v_Transaction.AccountTitle
END
GO
/****** Object: StoredProcedure [dbo].[GetAccountTotalAmount] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GetAccountTotalAmount](@FromDate AS NVARCHAR(20),@ToDate AS NVARCHAR(20),@HeadID AS INT, @BranchID AS INT, @CompanyID AS INT)
AS
BEGIN
SELECT v_Transaction.AccountTitle,
CASE WHEN SUM(v_Transaction.Debit) > SUM(v_Transaction.Credit) THEN ISNULL(SUM(v_Transaction.Debit) - SUM(v_Transaction.Credit),0)
WHEN SUM(v_Transaction.Debit) < SUM(v_Transaction.Credit) THEN ISNULL(SUM(v_Transaction.Credit) - SUM(v_Transaction.Debit),0) ELSE 0 END AS [Total],
CASE WHEN SUM(v_Transaction.Debit) > SUM(v_Transaction.Credit) THEN 'Debit'
WHEN SUM(v_Transaction.Debit) < SUM(v_Transaction.Credit) THEN 'Credit' ELSE '0' END AS [Status]
FROM
v_Transaction WHERE v_Transaction.TransectionDate > DATEADD(day,-1,@FromDate) AND v_Transaction.TransectionDate < DATEADD(day,1,@ToDate) AND
v_Transaction.AccountHeadID = ISNULL(@HeadID,0) AND v_Transaction.BranchID = @BranchID AND v_Transaction.CompanyID = @CompanyID GROUP BY v_Transaction.AccountTitle
END
GO
/****** Object: StoredProcedure [dbo].[GetAllAccounts] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GetAllAccounts](@BranchID as int, @CompanyID as int)
AS
BEGIN
SELECT AHC.AccountHeadID,
AHC.AccountHeadName,
AHC.AccountControlID,
AHC.AccountControlName,
AHC.BranchID,
AHC.CompanyID,
ACS.AccountSubControlID,
AHC.AccountControl + '-/- ' + ACS.AccountSubControlName [AccountSubControl]
FROM tblAccountSubControl ACS
INNER JOIN
(SELECT AH.AccountHeadID,
AH.AccountHeadName,
AC.AccountControlID,
AC.AccountControlName,
AH.AccountHeadName + '-/-' + AC.AccountControlName [AccountControl],
AC.BranchID,
AC.CompanyID
FROM tblAccountHead AH
INNER JOIN tblAccountControl AC
ON AH.AccountHeadID = AC.AccountHeadID) AHC
ON ACS.AccountControlID = AHC.AccountControlID
WHERE
AHC.BranchID = @BranchID AND
AHC.CompanyID = @CompanyID
ORDER BY AHC.AccountHeadID
END
GO
/****** Object: StoredProcedure [dbo].[GetCustomerPaymentHistory] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetCustomerPaymentHistory](@CustomerInvoiceID as int)
AS
BEGIN
Select CI.CustomerInvoiceID,CI.BranchID, CI.CompanyID,CI.InvoiceDate, CI.CustomerID, CP.InvoiceNo, cast(CI.TotalAmount as decimal(10, 2)), CP.PaidAmount, CP.RemainingBalance,CP.UserID
from tblCustomerInvoice CI
inner Join
(select tblCustomerPayment.CustomerInvoiceID,tblCustomerPayment.BranchID,tblCustomerPayment.CompanyID,tblCustomerPayment.PaidAmount,tblCustomerPayment.RemainingBalance,tblCustomerPayment.UserID,tblCustomerPayment.invoiceNo
from tblCustomerPayment) CP
on CI.CustomerInvoiceID = CP.CustomerInvoiceID
where CI.CustomerInvoiceID = ISNULL(@CustomerInvoiceID,0)
END
GO
/****** Object: StoredProcedure [dbo].[GetCustomerRemainingPaymentRecord] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GetCustomerRemainingPaymentRecord](@BranchID AS INT, @CompanyID AS INT)
AS
BEGIN
SELECT CI.CustomerInvoiceID,CI.BranchID, CI.CompanyID,CI.InvoiceDate, CI.CustomerID, CI.InvoiceNo, cast(CI.TotalAmount as decimal(10, 2)), CP.Payment, (cast(CI.TotalAmount as decimal(10, 2)) - CP.Payment) [ReamingBalance] from tblCustomerInvoice CI FUll Join (select CustomerInvoiceID,BranchID,CompanyID, sum(PaidAmount) as [Payment]
from tblCustomerPayment group by CustomerInvoiceID,BranchID,CompanyID) CP
on CI.CustomerInvoiceID = CP.CustomerInvoiceID
where CI.CompanyID = @CompanyID and CI.BranchID = @BranchID AND
cast(CI.TotalAmount as decimal(10, 2)) > ISNUll(CP.Payment, 0)
END
GO
/****** Object: StoredProcedure [dbo].[GetCustomerReturnSalePaidPending] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[GetCustomerReturnSalePaidPending](@CustomerInvoiceID as int)
AS
BEGIN
Select RCI.CustomerReturnInvoiceID, RCI.CustomerInvoiceID,RCI.BranchID, RCI.CompanyID,RCI.InvoiceDate,
RCI.CustomerID, RCI.InvoiceNo, ISNULL(cast(RCI.TotalAmount as decimal(10, 2)),0) [ReturnTotal], ISNULL(CP.SPayment,0) [ReturnPayment],
( ISNULL(cast(RCI.TotalAmount as decimal(10, 2)),0)- ISNULL(CP.SPayment,0)) [ReturnRemainingPayment], RCI.UserID
from tblCustomerReturnInvoice RCI
Full Join
(select CustomerReturnInvoiceID,
sum(PaidAmount) [SPayment]
from tblCustomerReturnPayment
group by CustomerReturnInvoiceID) CP
on RCI.CustomerReturnInvoiceID = CP.CustomerReturnInvoiceID
where RCI.CustomerInvoiceID = ISNULL(@CustomerInvoiceID,0) AND
( ISNULL(cast(RCI.TotalAmount as decimal(10, 2)),0)- ISNULL(CP.SPayment,0)) > 0
END
GO
/****** Object: StoredProcedure [dbo].[GETDashboardValues] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GETDashboardValues](@BranchID AS INT, @CompanyID AS INT)
AS
BEGIN
DECLARE @CurrentMonthExpenses as FLOAT = 0;
DECLARE @NetIncome FLOAT = 0;
DECLARE @CashplusBankaccountBalance FLOAT = 0;
DECLARE @TotalReceivable FLOAT = 0;
DECLARE @TotalPayable FLOAT = 0;
DECLARE @Capital FLOAT = 0;
DECLARE @CurrentMonthRevenue as FLOAT = 0;
DECLARE @CurrentMonthRecovery FLOAT = 0;
DECLARE @CurrentMonthSale FLOAT = 0;
DECLARE @CurrentMonthSalePaymentSucceed FLOAT = 0;
DECLARE @CurrentMonthSalePaymentPending FLOAT = 0;
DECLARE @CurrentMonthReturnSale as FLOAT = 0;
DECLARE @CurrentMonthReturnSalePaymentPending FLOAT = 0;
DECLARE @CurrentMonthReturnSalePaymentSucceed FLOAT = 0;
DECLARE @CurrentMonthPurchase FLOAT = 0;
DECLARE @CurrentMonthPurchasePaidPayment FLOAT = 0;
DECLARE @CurrentMonthPurchaseRemainingPayment FLOAT = 0;
DECLARE @CurrentMonthReturnPurchase as FLOAT = 0;
DECLARE @CurrentMonthReturnPurchasePaymentPending FLOAT = 0;
DECLARE @CurrentMonthReturnPurchasePaymentSucceed FLOAT = 0;
DECLARE @DaySale FLOAT = 0;
DECLARE @DaySalePaymentSucceed FLOAT = 0;
DECLARE @DaySalePaymentPending FLOAT = 0;
DECLARE @DayReturnSale as FLOAT = 0;
DECLARE @DayReturnSalePaymentPending FLOAT = 0;
DECLARE @DayReturnSalePaymentSucceed FLOAT = 0;
DECLARE @DayPurchase FLOAT = 0;
DECLARE @DayPurchasePaidPayment FLOAT = 0;
DECLARE @DayPurchaseRemainingPayment FLOAT = 0;
DECLARE @DayReturnPurchase as FLOAT = 0;
DECLARE @DayReturnPurchasePaymentPending FLOAT = 0;
DECLARE @DayReturnPurchasePaymentSucceed FLOAT = 0;
DECLARE @MonthStartDate AS DATE;
DECLARE @MonthEndDate AS DATE;
SET @MonthStartDate = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) , 0) as StartDate);
SET @MonthEndDate = (SELECT DATEADD(SECOND, -1, DATEADD(MONTH, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) , 0) ) ) as EndDate);
-- Current Month Revenue--
SET @CurrentMonthRevenue = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountHeadID = 5 AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Sale--
SET @CurrentMonthSale = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 1)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Sale--
SET @DaySale = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 1)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Sale Pending Payment--
SET @CurrentMonthSalePaymentPending = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 10)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Sale Pending Payment--
SET @DaySalePaymentPending = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 10)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Sale Succeed Payment--
SET @CurrentMonthSalePaymentSucceed = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 11)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Sale Succeed Payment--
SET @DaySalePaymentSucceed = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 11)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Sale Return--
SET @CurrentMonthReturnSale = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 2)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Sale Return--
SET @DayReturnSale = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 2)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Sale Return Pending Payment--
SET @CurrentMonthReturnSalePaymentPending = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 14)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Sale Return Pending Payment--
SET @DayReturnSalePaymentPending = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 14)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Sale Return Succeed Payment--
SET @CurrentMonthReturnSalePaymentSucceed = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 15)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Sale Return Succeed Payment--
SET @DayReturnSalePaymentSucceed = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 15)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Purchase--
SET @CurrentMonthPurchase = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 3)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Purchase--
SET @CurrentMonthPurchase = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 3)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Purchase Paid Payment--
SET @CurrentMonthPurchasePaidPayment = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 9)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Purchase Paid Payment--
SET @DayPurchasePaidPayment = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 9)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Purchase Remaining Payment--
SET @CurrentMonthPurchaseRemainingPayment = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 8)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Purchase Remaining Payment--
SET @DayPurchaseRemainingPayment = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 8)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Purchase Return --
SET @CurrentMonthReturnPurchase = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 4)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Purchase Return --
SET @DayReturnPurchase = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 4)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Purchase Return RemainingPayment --
SET @CurrentMonthReturnPurchasePaymentPending = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 12)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Purchase Return RemainingPayment --
SET @DayReturnPurchasePaymentPending = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 12)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Purchase Return Succeed Payment --
SET @CurrentMonthReturnPurchasePaymentSucceed = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 13)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Day Purchase Return Succeed Payment --
SET @DayReturnPurchasePaymentSucceed = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,GetDate()) AND TransectionDate < DATEADD(day,1,GetDate()) AND
AccountSubControlID = (select top 1 AccountSubControlID From tblAccountSetting where AccountActivityID = 13)
AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Month Expenses--
SET @CurrentMonthExpenses = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountHeadID = 3 AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Current Net Income/loss--
SET @NetIncome = ((Select (SUM(Credit)-SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE AccountHeadID = 5 AND BranchID = @BranchID AND CompanyID = @CompanyID)
-
(Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE AccountHeadID = 3 AND BranchID = @BranchID AND CompanyID = @CompanyID))
-- Current Month Recovery--
DECLARE @TotalAccountR as Float = (Select SUM(Credit) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountControlID = (select top 1 AccountControlID From tblAccountSetting where AccountActivityID = 17) AND BranchID = @BranchID AND CompanyID = @CompanyID)
DECLARE @TotalNoteR as Float = (Select SUM(Credit) AS [TotalAmount] from v_Transaction
WHERE TransectionDate > DATEADD(day,-1,@MonthStartDate) AND TransectionDate < DATEADD(day,1,@MonthEndDate) AND
AccountControlID = (select top 1 AccountControlID From tblAccountSetting where AccountActivityID = 18) AND BranchID = @BranchID AND CompanyID = @CompanyID)
SET @CurrentMonthRecovery = ISNULL(@TotalAccountR,0) + ISNULL(@TotalNoteR,0)
-- Current Cash / Bank Balance--
SET @CashplusBankaccountBalance = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE AccountHeadID = (select top 1 AccountControlID From tblAccountSetting where AccountActivityID = 21) AND BranchID = @BranchID AND CompanyID = @CompanyID)
-- Total Receivable--
DECLARE @TotalAccountRPending as Float = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE
AccountControlID = (select top 1 AccountControlID From tblAccountSetting where AccountActivityID = 17) AND BranchID = @BranchID AND CompanyID = @CompanyID)
DECLARE @TotalNoteRPending as Float = (Select (SUM(Debit) - SUM(Credit)) AS [TotalAmount] from v_Transaction
WHERE AccountControlID = (select top 1 AccountControlID From tblAccountSetting where AccountActivityID = 18) AND BranchID = @BranchID AND CompanyID = @CompanyID)
SET @TotalReceivable = ISNULL(@TotalAccountRPending,0) + ISNULL(@TotalNoteRPending,0)
-- Total Payable--
DECLARE @TotalAccountPPending as Float = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE
AccountControlID = (select top 1 AccountControlID From tblAccountSetting where AccountActivityID = 19) AND BranchID = @BranchID AND CompanyID = @CompanyID)
DECLARE @TotalNotePPending as Float = (Select (SUM(Credit) - SUM(Debit)) AS [TotalAmount] from v_Transaction
WHERE
AccountControlID = (select top 1 AccountControlID From tblAccountSetting where AccountActivityID = 20) AND BranchID = @BranchID AND CompanyID = @CompanyID)
SET @TotalPayable = ISNULL(@TotalAccountPPending,0) + ISNULL(@TotalNotePPending,0)
-- Total Capital --
set @Capital = (SELECT
CASE WHEN SUM(TA.Debit) > SUM(TA.Credit) THEN SUM(TA.Debit) - SUM(TA.Credit)
WHEN SUM(TA.Debit) < SUM(TA.Credit) THEN SUM(TA.Credit) - SUM(TA.Debit) ELSE 0 END AS TOTAL
FROM
(SELECT
CASE WHEN SUM(v_Transaction.Debit) > SUM(v_Transaction.Credit) THEN ISNULL(SUM(v_Transaction.Debit) - SUM(v_Transaction.Credit),0) ELSE 0 END AS [Debit],
CASE WHEN SUM(v_Transaction.Debit) < SUM(v_Transaction.Credit) THEN ISNULL(SUM(v_Transaction.Credit) - SUM(v_Transaction.Debit),0) ELSE 0 END AS [Credit]
FROM
v_Transaction WHERE v_Transaction.AccountHeadID = 4 AND
v_Transaction.BranchID = @BranchID AND
v_Transaction.CompanyID = @CompanyID
GROUP BY v_Transaction.AccountTitle) TA
)
Select
ISNULL(@CurrentMonthExpenses,0) as [Current Month Expenses],
ISNULL(@NetIncome,0) as [Net Income],
ISNULL(@Capital,0) as [Capital],
ISNULL(@CashplusBankaccountBalance,0) as [Cash/Bank Balance],
ISNULL(@TotalReceivable,0) as [Total Receivable],
ISNULL(@TotalPayable,0) as [Total Payable],
ISNULL(@CurrentMonthRevenue,0) as [Current Month Revenue],
ISNULL(@CurrentMonthSale,0) as [Current Month Sale],
ISNULL(@CurrentMonthSalePaymentSucceed,0) as [Current Month Sale Payment Succeed],
ISNULL(@CurrentMonthSalePaymentPending,0) as [Current Month Sale Payment Pending],
ISNULL(@CurrentMonthReturnSale,0) as [Current Month Return Sale],
ISNULL(@CurrentMonthReturnSalePaymentPending,0) as [Current Month Return Sale Payment Pending],
ISNULL(@CurrentMonthReturnSalePaymentSucceed,0) as [Current Month Return Sale Payment Succeed],
ISNULL(@CurrentMonthPurchase,0) AS [Current Month Purchase],
ISNULL(@CurrentMonthPurchasePaidPayment,0) AS [Current Month Purchase Paid Payment],
ISNULL(@CurrentMonthPurchaseRemainingPayment,0) AS [Current Month Purchase Remaining Payment],
ISNULL(@CurrentMonthReturnPurchase,0) AS [Current Month Return Purchase],
ISNULL(@CurrentMonthReturnPurchasePaymentPending,0) AS [Current Month Purchase Payment Pending],
ISNULL(@CurrentMonthReturnPurchasePaymentSucceed,0) AS [Current Month Purchase Succeed Payment]
,ISNULL(@DaySale,0) AS [Day Sale]
,ISNULL(@DaySalePaymentSucceed ,0) AS [Day Sale Payment Succeed]
,ISNULL(@DaySalePaymentPending ,0) AS [Day Sale Payment Pending]
,ISNULL(@DayReturnSale ,0) AS [Day Return Sale]
,ISNULL(@DayReturnSalePaymentPending ,0) AS [Day Return Sale Payment Pending]
,ISNULL(@DayReturnSalePaymentSucceed,0) AS [Day Return Sale Payment Succeed]
,ISNULL(@DayPurchase ,0) AS [Day Purchase]
,ISNULL(@DayPurchasePaidPayment ,0) AS [Day Purchase Payment Paid]
,ISNULL(@DayPurchaseRemainingPayment,0) AS [Day Purchase Remaining Payment]
,ISNULL(@DayReturnPurchase ,0) AS [Day Return Purchase]
,ISNULL(@DayReturnPurchasePaymentPending ,0) AS [Day Return Purchase Payment Pending]
,ISNULL(@DayReturnPurchasePaymentSucceed,0) AS [Day Return Purchase Payment Succed]
END
GO
/****** Object: StoredProcedure [dbo].[GetJournal] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GetJournal](@BranchID as int, @CompanyID as int, @FromDate as Date, @ToDate as Date)
AS
BEGIN
Select
TR.TransectionDate,
ACTS.AccountSubControl,
TR.TransectionTitle,
ACTS.AccountSubControlID,
TR.InvoiceNo,
TR.Debit,
TR.Credit
From tblTransaction TR
INNER JOIN
(SELECT AHC.AccountHeadID,
AHC.AccountHeadName,
AHC.AccountControlID,
AHC.AccountControlName,
AHC.BranchID,
AHC.CompanyID,
ACS.AccountSubControlID,
AHC.AccountControl + '-/- ' + ACS.AccountSubControlName [AccountSubControl]
FROM tblAccountSubControl ACS
INNER JOIN
(SELECT AH.AccountHeadID,
AH.AccountHeadName,
AC.AccountControlID,
AC.AccountControlName,
AH.AccountHeadName + '-/-' + AC.AccountControlName [AccountControl],
AC.BranchID,
AC.CompanyID
FROM tblAccountHead AH
INNER JOIN tblAccountControl AC
ON AH.AccountHeadID = AC.AccountHeadID) AHC
ON ACS.AccountControlID = AHC.AccountControlID) ACTS
on TR.AccountSubControlID = ACTS.AccountSubControlID
WHERE
TR.BranchID = @BranchID AND
TR.CompanyID = @CompanyID AND
TR.TransectionDate > DATEADD(day, -1,@FromDate) AND
TR.TransectionDate < DATEADD(day,1,@ToDate)
ORDER BY TR.TransactionID DESC
END
GO
/****** Object: StoredProcedure [dbo].[GetLedger] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GetLedger](@BranchID AS INT, @CompanyID AS INT, @FinancialYearID AS INT)
AS
BEGIN
SELECT TransactionID,FinancialYearID,BranchID,
CompanyID,AccountHeadID,AccountControlID,
AccountSubControlID, AccountTitle,InvoiceNo,
TransectionDate,TransectionTitle,Debit,Credit
FROM v_Transaction
WHERE
BranchID = ISNULL(@BranchID,0)
AND CompanyID = ISNULL(@CompanyID,0)
AND FinancialYearID = ISNULL(@FinancialYearID,0)
ORDER BY AccountSubControlID
END
GO
/****** Object: StoredProcedure [dbo].[GetPurchasesHistory] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetPurchasesHistory](@BranchID as int, @CompanyID as int, @FromDate as Date, @ToDate as Date)
AS
BEGIN
Select SI.SupplierInvoiceID,SI.BranchID, SI.CompanyID,SI.InvoiceDate, SI.SupplierID, SI.InvoiceNo,
cast(SI.TotalAmount as decimal(10, 2)) [BeforeReturnTotal],
ISNULL(SR.ReturnTotal,0) [ReturnTotal],
ISNULL(cast(SI.TotalAmount as decimal(10, 2))-ISNULL(SR.ReturnTotal,0),0) [AfterReturnTotal],
ISNULL(SP.Payment,0) [PaidAmount],
ISNULL(PR.ReturnPayment,0) [ReturnPayment],
(ISNULL(cast(SI.TotalAmount as decimal(10, 2))-ISNULL(SR.ReturnTotal,0),0) - (ISNULL(SP.Payment,0)- ISNULL(PR.ReturnPayment,0))) [ReamingBalance]
from tblSupplierInvoice SI FUll Join (select SupplierInvoiceID,BranchID,CompanyID, sum(PaymentAmount) as [Payment]
from tblSupplierPayment group by SupplierInvoiceID,BranchID,CompanyID) SP
on SI.SupplierInvoiceID = SP.SupplierInvoiceID
Full Join (select SupplierInvoiceID, ISNULL(sum(TotalAmount),0) [ReturnTotal] from [dbo].[tblSupplierReturnInvoice]
Group by SupplierInvoiceID ) SR
on SR.SupplierInvoiceID = SI.SupplierInvoiceID
Full Join (select SupplierInvoiceID, ISNULL(sum(PaymentAmount),0) [ReturnPayment] from [dbo].[tblSupplierReturnPayment]
group by SupplierInvoiceID) PR
on PR.SupplierInvoiceID = SI.SupplierInvoiceID
where SI.CompanyID = @CompanyID and SI.BranchID = @BranchID AND
SI.InvoiceDate >= @FromDate AND SI.InvoiceDate <=@ToDate
END
GO
/****** Object: StoredProcedure [dbo].[GetReturnPurchasePaymentPending] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetReturnPurchasePaymentPending] (@BranchID as int, @CompanyID as int)
AS
BEGIN
Select SI.SupplierInvoiceID,SI.BranchID, SI.CompanyID,SI.InvoiceDate, SI.SupplierID, SI.InvoiceNo,
cast(SI.TotalAmount as decimal(10, 2)) [BeforeReturnTotal],
ISNULL(SR.ReturnTotal,0) [ReturnTotal],
ISNULL(cast(SI.TotalAmount as decimal(10, 2))-ISNULL(SR.ReturnTotal,0),0) [AfterReturnTotal],
ISNULL(SP.Payment,0) [PaidAmount],
ISNULL(PR.ReturnPayment,0) [ReturnPayment],
(ISNULL(cast(SI.TotalAmount as decimal(10, 2))-ISNULL(SR.ReturnTotal,0),0) - (ISNULL(SP.Payment,0)- ISNULL(PR.ReturnPayment,0))) [ReamingBalance]
from tblSupplierInvoice SI FUll Join (select SupplierInvoiceID,BranchID,CompanyID, sum(PaymentAmount) as [Payment]
from tblSupplierPayment group by SupplierInvoiceID,BranchID,CompanyID) SP
on SI.SupplierInvoiceID = SP.SupplierInvoiceID
Full Join (select SupplierInvoiceID, ISNULL(sum(TotalAmount),0) [ReturnTotal] from [dbo].[tblSupplierReturnInvoice]
Group by SupplierInvoiceID ) SR
on SR.SupplierInvoiceID = SI.SupplierInvoiceID
Full Join (select SupplierInvoiceID, ISNULL(sum(PaymentAmount),0) [ReturnPayment] from [dbo].[tblSupplierReturnPayment]
group by SupplierInvoiceID) PR
on PR.SupplierInvoiceID = SI.SupplierInvoiceID
where SI.CompanyID = @CompanyID and SI.BranchID = @BranchID
AND ISNULL(PR.ReturnPayment,0) < ISNULL(SR.ReturnTotal,0)
END
GO
/****** Object: StoredProcedure [dbo].[GetReturnSaleAmountPending] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROC [dbo].[GetReturnSaleAmountPending](@BranchID AS INT, @CompanyID AS INT)
AS
BEGIN
Select CI.CustomerInvoiceID,CI.BranchID, CI.CompanyID,CI.InvoiceDate, CI.CustomerID, CI.InvoiceNo,
cast(CI.TotalAmount as decimal(10, 2)) [BeforeReturnTotal],
ISNULL(CR.ReturnTotal,0) [ReturnTotal],
ISNULL(cast(CI.TotalAmount as decimal(10, 2))-ISNULL(CR.ReturnTotal,0),0) [AfterReturnTotal],
ISNULL(CP.Payment,0) [PaidAmount],
ISNULL(PR.ReturnPayment,0) [ReturnPayment],
(ISNULL(cast(CI.TotalAmount as decimal(10, 2))-ISNULL(CR.ReturnTotal,0),0) - (ISNULL(CP.Payment,0)- ISNULL(PR.ReturnPayment,0))) [ReamingBalance]
from tblCustomerInvoice CI FUll Join (select CustomerInvoiceID,BranchID,CompanyID, sum(PaidAmount) as [Payment]
from tblCustomerPayment group by CustomerInvoiceID,BranchID,CompanyID) CP
on CI.CustomerInvoiceID = CP.CustomerInvoiceID
Full Join (select CustomerInvoiceID, ISNULL(sum(TotalAmount),0) [ReturnTotal] from [dbo].tblCustomerReturnInvoice
Group by CustomerInvoiceID ) CR
on CR.CustomerInvoiceID = CI.CustomerInvoiceID
Full Join (select CustomerInvoiceID, ISNULL(sum(PaidAmount),0) [ReturnPayment] from [dbo].tblCustomerReturnPayment
group by CustomerInvoiceID) PR
on PR.CustomerInvoiceID = CI.CustomerInvoiceID
where CI.CompanyID = @CompanyID and CI.BranchID = @BranchID AND
ISNULL(PR.ReturnPayment,0) < ISNULL(CR.ReturnTotal,0)
END
GO
/****** Object: StoredProcedure [dbo].[GetSalesHistory] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GetSalesHistory](@BranchID AS INT, @CompanyID AS INT, @FromDate as Date, @ToDate as Date)
AS
BEGIN
Select CI.CustomerInvoiceID,CI.BranchID, CI.CompanyID,CI.InvoiceDate, CI.CustomerID, CI.InvoiceNo,
cast(CI.TotalAmount as decimal(10, 2)) [BeforeReturnTotal],
ISNULL(CR.ReturnTotal,0) [ReturnTotal],
ISNULL(cast(CI.TotalAmount as decimal(10, 2))-ISNULL(CR.ReturnTotal,0),0) [AfterReturnTotal],
ISNULL(CP.Payment,0) [PaidAmount],
ISNULL(PR.ReturnPayment,0) [ReturnPayment],
(ISNULL(cast(CI.TotalAmount as decimal(10, 2))-ISNULL(CR.ReturnTotal,0),0) - (ISNULL(CP.Payment,0)- ISNULL(PR.ReturnPayment,0))) [ReamingBalance]
from tblCustomerInvoice CI FUll Join (select CustomerInvoiceID,BranchID,CompanyID, sum(PaidAmount) as [Payment]
from tblCustomerPayment group by CustomerInvoiceID,BranchID,CompanyID) CP
on CI.CustomerInvoiceID = CP.CustomerInvoiceID
Full Join (select CustomerInvoiceID, ISNULL(sum(TotalAmount),0) [ReturnTotal] from [dbo].tblCustomerReturnInvoice
Group by CustomerInvoiceID ) CR
on CR.CustomerInvoiceID = CI.CustomerInvoiceID
Full Join (select CustomerInvoiceID, ISNULL(sum(PaidAmount),0) [ReturnPayment] from [dbo].tblCustomerReturnPayment
group by CustomerInvoiceID) PR
on PR.CustomerInvoiceID = CI.CustomerInvoiceID
where CI.CompanyID = @CompanyID and CI.BranchID = @BranchID AND
CI.InvoiceDate >= @FromDate AND CI.InvoiceDate <=@ToDate
END
GO
/****** Object: StoredProcedure [dbo].[GetSupplierPaymentHistory] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetSupplierPaymentHistory](@SupplierInvoiceID as int)
AS
BEGIN
Select SI.SupplierInvoiceID,SI.BranchID, SI.CompanyID,SI.InvoiceDate, SI.SupplierID, SP.InvoiceNo, cast(SI.TotalAmount as decimal(10, 2)), SP.PaymentAmount, SP.RemainingBalance,SP.UserID
from tblSupplierInvoice SI
inner Join
(select tblSupplierPayment.SupplierInvoiceID,tblSupplierPayment.BranchID,tblSupplierPayment.CompanyID,tblSupplierPayment.PaymentAmount,tblSupplierPayment.RemainingBalance,tblSupplierPayment.UserID,tblSupplierPayment.InvoiceNo
from tblSupplierPayment) SP
on SI.SupplierInvoiceID = SP.SupplierInvoiceID
where SI.SupplierInvoiceID = ISNULL(@SupplierInvoiceID,0)
END
GO
/****** Object: StoredProcedure [dbo].[GetSupplierRemainingPaymentRecord] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetSupplierRemainingPaymentRecord](@BranchID as int, @CompanyID as int)
AS
BEGIN
Select SI.SupplierInvoiceID,
SI.BranchID,
SI.CompanyID,
SI.InvoiceDate,
SI.SupplierID,
SI.InvoiceNo,
cast(SI.TotalAmount as decimal(10, 2)) [BeforeReturnTotal],
ISNULL(SR.ReturnTotal,0) [ReturnTotal],
ISNULL(cast(SI.TotalAmount as decimal(10, 2))-ISNULL(SR.ReturnTotal,0),0) [AfterReturnTotal],
ISNULL(SP.Payment,0) [PaidAmount],
ISNULL(PR.ReturnPayment,0) [ReturnPayment],
(ISNULL(cast(SI.TotalAmount as decimal(10, 2))-ISNULL(SR.ReturnTotal,0),0) - (ISNULL(SP.Payment,0)- ISNULL(PR.ReturnPayment,0))) [ReamingBalance]
from tblSupplierInvoice SI
FUll Join (select SupplierInvoiceID,BranchID,CompanyID, sum(PaymentAmount) as [Payment]
from tblSupplierPayment group by SupplierInvoiceID,BranchID,CompanyID) SP
on SI.SupplierInvoiceID = SP.SupplierInvoiceID
Full Join (select SupplierInvoiceID, ISNULL(sum(TotalAmount),0) [ReturnTotal] from [dbo].[tblSupplierReturnInvoice]
Group by SupplierInvoiceID ) SR
on SR.SupplierInvoiceID = SI.SupplierInvoiceID
Full Join (select SupplierInvoiceID, ISNULL(sum(PaymentAmount),0) [ReturnPayment] from [dbo].[tblSupplierReturnPayment]
group by SupplierInvoiceID) PR
on PR.SupplierInvoiceID = SI.SupplierInvoiceID
where SI.CompanyID = @CompanyID and SI.BranchID = @BranchID AND
(ISNULL(cast(SI.TotalAmount as decimal(10, 2))-ISNULL(SR.ReturnTotal,0),0) - (ISNULL(SP.Payment,0)- ISNULL(PR.ReturnPayment,0))) > 0
END
GO
/****** Object: StoredProcedure [dbo].[GetSupplierReturnPurchasePaymentPending] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetSupplierReturnPurchasePaymentPending](@SupplierInvoiceID as int)
AS
BEGIN
Select RSI.SupplierReturnInvoiceID, RSI.SupplierInvoiceID,RSI.BranchID, RSI.CompanyID,RSI.InvoiceDate,
RSI.SupplierID, RSI.InvoiceNo, ISNULL(cast(RSI.TotalAmount as decimal(10, 2)),0) [ReturnTotal], ISNULL(SP.SPayment,0) [ReturnPayment],
( ISNULL(cast(RSI.TotalAmount as decimal(10, 2)),0)- ISNULL(SP.SPayment,0)) [ReturnRemainingPayment], RSI.UserID
from tblSupplierReturnInvoice RSI
Full Join
(select SupplierReturnInvoiceID,
sum(PaymentAmount) [SPayment]
from tblSupplierReturnPayment
group by SupplierReturnInvoiceID) SP
on RSI.SupplierReturnInvoiceID = SP.SupplierReturnInvoiceID
where RSI.SupplierInvoiceID = ISNULL(@SupplierInvoiceID,0) AND
( ISNULL(cast(RSI.TotalAmount as decimal(10, 2)),0)- ISNULL(SP.SPayment,0)) > 0
END
GO
/****** Object: StoredProcedure [dbo].[GETTotalBYHeadAccount] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GETTotalBYHeadAccount](@HeadID AS INT, @FinancialYearID AS INT, @BranchID AS INT, @CompanyID AS INT)
AS
BEGIN
SELECT
CASE WHEN SUM(TA.Debit) > SUM(TA.Credit) THEN SUM(TA.Debit) - SUM(TA.Credit)
WHEN SUM(TA.Debit) < SUM(TA.Credit) THEN SUM(TA.Credit) - SUM(TA.Debit) ELSE 0 END AS TOTAL
FROM
(SELECT v_Transaction.AccountTitle,
CASE WHEN SUM(v_Transaction.Debit) > SUM(v_Transaction.Credit) THEN ISNULL(SUM(v_Transaction.Debit) - SUM(v_Transaction.Credit),0) ELSE 0 END AS [Debit],
CASE WHEN SUM(v_Transaction.Debit) < SUM(v_Transaction.Credit) THEN ISNULL(SUM(v_Transaction.Credit) - SUM(v_Transaction.Debit),0) ELSE 0 END AS [Credit]
FROM
v_Transaction WHERE v_Transaction.AccountHeadID = ISNULL(@HeadID,0) AND v_Transaction.FinancialYearID = ISNULL(@FinancialYearID,0) AND
v_Transaction.BranchID = @BranchID AND v_Transaction.CompanyID = @CompanyID GROUP BY v_Transaction.AccountTitle) TA
END
GO
/****** Object: StoredProcedure [dbo].[GetTrialBalance] Script Date: 09/08/2021 1:01:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GetTrialBalance](@BranchID as int, @CompanyID as int, @FinancialYearID as int)
AS
BEGIN
SELECT JR.FinancialYearID, JR.AccountSubControl, JR.AccountSubControlID,
CASE WHEN JR.Debit > JR.Credit THEN JR.Debit - JR.Credit ELSE NULL END as [Debit],
CASE WHEN JR.Debit < JR.Credit THEN JR.Credit - JR.Debit ELSE NULL END as [Credit],
JR.BranchID,
JR.CompanyID
FROM
(Select
TR.FinancialYearID,
ACTS.AccountSubControl,
ACTS.AccountSubControlID,
SUM(TR.Debit) [Debit],
SUM(TR.Credit) [Credit],
TR.BranchID,
TR.CompanyID
From tblTransaction TR
INNER JOIN
(SELECT AHC.AccountHeadID,
AHC.AccountHeadName,
AHC.AccountControlID,
AHC.AccountControlName,
AHC.BranchID,
AHC.CompanyID,
ACS.AccountSubControlID,
AHC.AccountControl + '-/- ' + ACS.AccountSubControlName [AccountSubControl]
FROM tblAccountSubControl ACS
INNER JOIN
(SELECT AH.AccountHeadID,
AH.AccountHeadName,
AC.AccountControlID,
AC.AccountControlName,
AH.AccountHeadName + '-/-' + AC.AccountControlName [AccountControl],
AC.BranchID,
AC.CompanyID
FROM tblAccountHead AH
INNER JOIN tblAccountControl AC
ON AH.AccountHeadID = AC.AccountHeadID) AHC
ON ACS.AccountControlID = AHC.AccountControlID) ACTS
on TR.AccountSubControlID = ACTS.AccountSubControlID
group by TR.FinancialYearID,
ACTS.AccountSubControl,
ACTS.AccountSubControlID,
TR.BranchID,
TR.CompanyID ) JR
WHERE
JR.BranchID = @BranchID AND
JR.CompanyID = @CompanyID AND
JR.FinancialYearID = @FinancialYearID
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[42] 4[7] 2[31] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = -96
Left = 0
End
Begin Tables =
Begin Table = "tblTransaction"
Begin Extent =
Top = 23
Left = 309
Bottom = 198
Right = 514
End
DisplayFlags = 280
TopColumn = 1
End
Begin Table = "tblAccountSubControl"
Begin Extent =
Top = 19
Left = 575
Bottom = 190
Right = 801
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "tblAccountHead"
Begin Extent =
Top = 40
Left = 17
Bottom = 173
Right = 211
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "tblAccountControl"
Begin Extent =
Top = 174
Left = 38
Bottom = 304
Right = 244
End
DisplayFlags = 280
TopColumn = 2
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 15
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1200
Width = 4995
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 117' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_Transaction'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'0
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_Transaction'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_Transaction'
GO
USE [master]
GO
ALTER DATABASE [CloudErpV1] SET READ_WRITE
GO
Can you share BOTH SOURCE Code with database
ReplyDelete