database.sql

USE [master]
GO
/****** Object:  Database [NHOM3]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE DATABASE [NHOM3]
 CONTAINMENT = NONE
WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [NHOM3] SET COMPATIBILITY_LEVEL = 150
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [NHOM3].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [NHOM3] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [NHOM3] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [NHOM3] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [NHOM3] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [NHOM3] SET ARITHABORT OFF 
GO
ALTER DATABASE [NHOM3] SET AUTO_CLOSE ON 
GO
ALTER DATABASE [NHOM3] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [NHOM3] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [NHOM3] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [NHOM3] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [NHOM3] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [NHOM3] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [NHOM3] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [NHOM3] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [NHOM3] SET  DISABLE_BROKER 
GO
ALTER DATABASE [NHOM3] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [NHOM3] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [NHOM3] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [NHOM3] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [NHOM3] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [NHOM3] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [NHOM3] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [NHOM3] SET RECOVERY FULL 
GO
ALTER DATABASE [NHOM3] SET  MULTI_USER 
GO
ALTER DATABASE [NHOM3] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [NHOM3] SET DB_CHAINING OFF 
GO
ALTER DATABASE [NHOM3] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [NHOM3] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [NHOM3] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [NHOM3] SET QUERY_STORE = OFF
GO
USE [NHOM3]
GO

CREATE LOGIN phamhoang2000 WITH PASSWORD='hoang123456789'
CREATE LOGIN phamhoang2001 WITH PASSWORD='hoang123456789'
/****** Object:  User [test]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE USER [test] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [phamhoang2001]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE USER [phamhoang2001] FOR LOGIN [phamhoang2001] WITH DEFAULT_SCHEMA=[phamhoang2001]
GO
/****** Object:  User [phamhoang2000]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE USER [phamhoang2000] FOR LOGIN [phamhoang2000] WITH DEFAULT_SCHEMA=[phamhoang2000]
GO
/****** Object:  User [nhanvien4]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE USER [nhanvien4] FOR LOGIN [nhanvien4] WITH DEFAULT_SCHEMA=[nhanvien4]
GO
/****** Object:  User [nhanvien3]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE USER [nhanvien3] FOR LOGIN [nhanvien3] WITH DEFAULT_SCHEMA=[nhanvien3]
GO
/****** Object:  User [nhanvien2]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE USER [nhanvien2] FOR LOGIN [nhanvien2] WITH DEFAULT_SCHEMA=[nhanvien2]
GO
/****** Object:  User [nhanvien1]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE USER [nhanvien1] FOR LOGIN [nhanvien1] WITH DEFAULT_SCHEMA=[nhanvien1]
GO
/****** Object:  User [Admin]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE USER [Admin] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[Admin]
GO
/****** Object:  User [ABC]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE USER [ABC] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[ABC]
GO
/****** Object:  DatabaseRole [NhanVien]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE ROLE [NhanVien]
GO
ALTER ROLE [NhanVien] ADD MEMBER [nhanvien4]
GO
ALTER ROLE [NhanVien] ADD MEMBER [nhanvien3]
GO
ALTER ROLE [NhanVien] ADD MEMBER [nhanvien2]
GO
ALTER ROLE [NhanVien] ADD MEMBER [nhanvien1]
GO
/****** Object:  Schema [ABC]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE SCHEMA [ABC]
GO
/****** Object:  Schema [Admin]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE SCHEMA [Admin]
GO
/****** Object:  Schema [NhanVien]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE SCHEMA [NhanVien]
GO
/****** Object:  Schema [nhanvien1]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE SCHEMA [nhanvien1]
GO
/****** Object:  Schema [nhanvien2]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE SCHEMA [nhanvien2]
GO
/****** Object:  Schema [nhanvien3]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE SCHEMA [nhanvien3]
GO
/****** Object:  Schema [nhanvien4]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE SCHEMA [nhanvien4]
GO
/****** Object:  Schema [phamhoang2000]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE SCHEMA [phamhoang2000]
GO
/****** Object:  Schema [phamhoang2001]    Script Date: 12/19/2020 2:52:17 AM ******/
CREATE SCHEMA [phamhoang2001]
GO
/****** Object:  UserDefinedFunction [dbo].[DateRange]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DateRange]
(     
      @Increment              CHAR(1),
      @StartDate              DATE,
      @EndDate                DATE
)
RETURNS  
@SelectedRange    TABLE 
(IndividualDate DATETIME)
AS 
BEGIN
      ;WITH cteRange (DateRange) AS (
            SELECT @StartDate
            UNION ALL
            SELECT 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
                        WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
                        WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
                  END
            FROM cteRange
            WHERE DateRange <= 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
                        WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
                  END)
          
      INSERT INTO @SelectedRange (IndividualDate)
      SELECT DateRange
      FROM cteRange
      OPTION (MAXRECURSION 3660);
      RETURN
END
GO
/****** Object:  UserDefinedFunction [dbo].[hamtinhngay]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Bảng hóa đơn

create FUNCTION [dbo].[hamtinhngay]
(@ngay date)
RETURNS INT
as 
BEGIN
    return DATEDIFF(day, @ngay, CURRENT_TIMESTAMP)
    
END
GO
/****** Object:  UserDefinedFunction [dbo].[hamtinhtuoi]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- bảng nhân viên
create FUNCTION [dbo].[hamtinhtuoi]
(@nam_sinh date)
RETURNS INT
as 
BEGIN
    return DATEDIFF(day, @nam_sinh, CURRENT_TIMESTAMP)/365
    
END
GO
/****** Object:  UserDefinedFunction [dbo].[KCNGAY]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- Bảo làm -----------------------------------------------------------------------------------------------------------------------------------------------------





CREATE FUNCTION [dbo].[KCNGAY] (@BD DATE, @KT DATE)
RETURNS INT
AS
BEGIN 
    RETURN DATEDIFF(DAY, @BD, @KT)
END
GO
/****** Object:  Table [dbo].[CHI_TIET_DON_NHAP_HANG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CHI_TIET_DON_NHAP_HANG](
	[MADNH] [varchar](50) NOT NULL,
	[MAHANG] [varchar](50) NOT NULL,
	[SOLUONG] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[MADNH] ASC,
	[MAHANG] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CHI_TIET_HOA_DON]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CHI_TIET_HOA_DON](
	[MAMH] [varchar](50) NOT NULL,
	[MAHD] [varchar](50) NOT NULL,
	[SOLUONG] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[MAHD] ASC,
	[MAMH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CHUC_VU]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CHUC_VU](
	[MACHUCVU] [varchar](50) NOT NULL,
	[TENCHUCVU] [nvarchar](200) NULL,
	[LUONG] [real] NULL,
PRIMARY KEY CLUSTERED 
(
	[MACHUCVU] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DON_NHAP_HANG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DON_NHAP_HANG](
	[MADNH] [varchar](50) NOT NULL,
	[MANCC] [varchar](50) NULL,
	[NGAYNHAP] [date] NULL,
PRIMARY KEY CLUSTERED 
(
	[MADNH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[GIAM_GIA]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GIAM_GIA](
	[MAGG] [varchar](50) NOT NULL,
	[TEN] [varchar](50) NULL,
	[NGAYBD] [date] NULL,
	[NGAYKT] [date] NULL,
	[PT_GIAM] [int] NULL,
	[TIENGIAM] [real] NULL,
	[LANSUDUNG] [int] NULL,
	[LANSUDUNGMAX] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[MAGG] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[HOA_DON]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HOA_DON](
	[MAHD] [varchar](50) NOT NULL,
	[MAKH] [varchar](50) NOT NULL,
	[MANV] [varchar](50) NOT NULL,
	[NGAYLHD] [date] NULL,
	[TONGTIEN] [real] NULL,
	[MAGIAMGIA] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[MAHD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[KHACH_HANG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KHACH_HANG](
	[MAKH] [varchar](50) NOT NULL,
	[TENKH] [nvarchar](200) NULL,
	[GIOITINH] [varchar](50) NULL,
	[NGAYSINH] [date] NULL,
	[CMND] [varchar](50) NULL,
	[SDT] [varchar](50) NULL,
	[DIACHI] [nvarchar](200) NULL,
	[LOAIKH] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[MAKH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[KHO]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KHO](
	[MAMH] [varchar](50) NOT NULL,
	[TRANGTHAI] [bit] NULL,
	[SOLUONG] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[MAMH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[LOAI_KHACH_HANG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LOAI_KHACH_HANG](
	[MALOAIKH] [varchar](50) NOT NULL,
	[TENLOAI] [nvarchar](200) NULL,
PRIMARY KEY CLUSTERED 
(
	[MALOAIKH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[MAT_HANG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MAT_HANG](
	[MAMH] [varchar](50) NOT NULL,
	[TENMH] [nvarchar](200) NULL,
	[NGAYSX] [date] NULL,
	[HANSD] [date] NULL,
	[LOAI] [varchar](50) NULL,
	[GIA] [real] NULL,
PRIMARY KEY CLUSTERED 
(
	[MAMH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[NHA_CUNG_CAP]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NHA_CUNG_CAP](
	[MANCC] [varchar](50) NOT NULL,
	[TENNCC] [nvarchar](200) NULL,
	[DIACHI] [nvarchar](200) NULL,
	[SDT] [varchar](50) NULL,
	[STK] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[MANCC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[NHAN_VIEN]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NHAN_VIEN](
	[MANV] [varchar](50) NOT NULL,
	[TENNV] [nvarchar](200) NULL,
	[GIOITINH] [varchar](50) NULL,
	[NGAYSINH] [date] NULL,
	[CMND] [varchar](50) NULL,
	[SDT] [varchar](50) NULL,
	[DIACHI] [nvarchar](200) NULL,
	[NGAYLAMVIEC] [date] NULL,
	[CHUCVU] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[MANV] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PHAN_LOAI]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PHAN_LOAI](
	[MALOAIMH] [varchar](50) NOT NULL,
	[TENLOAIMH] [nvarchar](200) NULL,
PRIMARY KEY CLUSTERED 
(
	[MALOAIMH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'18b41a64-68a8-4be6-9560-416657a97b9b', N'BANHKEO1', 2)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'2487f5f3-26b1-4082-b1c9-526c5f272139', N'BANHKEO1', 10)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'35f89e59-7115-4db5-b540-4a3b1398b428', N'TSDL-16122020', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'35f89e59-7115-4db5-b540-4a3b1398b428', N'TSTD-16122020', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'35f89e59-7115-4db5-b540-4a3b1398b428', N'TSTT-16122020', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'35f89e59-7115-4db5-b540-4a3b1398b428', N'TXHC-18122020', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'540ba889-b577-4044-a562-87e9588741c1', N'BANHKEO1', 500)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'8d37628a-4a2e-4255-a724-199aec90e7c2', N'DHHT', 10)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'ba229b87-8502-4e83-930c-c494a3deda93', N'BANHKEO1', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'ba229b87-8502-4e83-930c-c494a3deda93', N'BBLPM-18122020', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'ba229b87-8502-4e83-930c-c494a3deda93', N'CCY-16122020', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'ba229b87-8502-4e83-930c-c494a3deda93', N'DHHT', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'ba229b87-8502-4e83-930c-c494a3deda93', N'KTSCC-18122020', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'ba229b87-8502-4e83-930c-c494a3deda93', N'KTSCD-16122020', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'ba229b87-8502-4e83-930c-c494a3deda93', N'MTT-16122020', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'ba229b87-8502-4e83-930c-c494a3deda93', N'RCHDB-16122020', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'ba229b87-8502-4e83-930c-c494a3deda93', N'Revive', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'cc318350-3b6b-453a-ae6a-420bbdea426c', N'BANHKEO1', 100)
INSERT [dbo].[CHI_TIET_DON_NHAP_HANG] ([MADNH], [MAHANG], [SOLUONG]) VALUES (N'f34da47b-03af-4994-b390-4c7a79b719dd', N'BANHKEO1', 12)
GO
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'0b10d73b-4c82-47b4-960a-36268a5c1a76', 1)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'147bc17f-89af-40e9-92c8-76b027791646', 1)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'19127ea5-ea37-4d1d-8e95-973b3914a094', 10)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'1967ccd9-0f14-4a3e-84b5-200470b0316e', 10)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'CCY-16122020', N'1967ccd9-0f14-4a3e-84b5-200470b0316e', 50)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'2b4a281b-f8c2-4b9e-a19b-aa76d38fafd8', 12)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BBLPM-18122020', N'2b4a281b-f8c2-4b9e-a19b-aa76d38fafd8', 10)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'380b5642-a047-47fc-b609-bcc536031680', 2)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'MTT-16122020', N'380b5642-a047-47fc-b609-bcc536031680', 9)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'4540bd76-6191-4d2d-8523-130dbbbd1c17', 6)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BBLPM-18122020', N'4540bd76-6191-4d2d-8523-130dbbbd1c17', 2)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'5bcebd42-e0ee-4c6d-b8fe-7dbb695d5ddd', 1)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'CCY-16122020', N'5bcebd42-e0ee-4c6d-b8fe-7dbb695d5ddd', 7)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'CCY-16122020', N'64ae14c1-3325-4d20-91fb-3bc0e496f8a2', 3)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'TXHC-18122020', N'64ae14c1-3325-4d20-91fb-3bc0e496f8a2', 5)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'67b2b388-c67e-473e-8f7c-db32a0391f8a', 20)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'MTT-16122020', N'67b2b388-c67e-473e-8f7c-db32a0391f8a', 50)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'7bb645fb-4489-4c81-a87e-55fdeb194518', 20)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'9438bb63-cc53-4a71-9a9c-df7437d0c636', 30)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'a804b2d7-95e5-4362-8867-fa5419a538c4', 1)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'DHHT', N'a804b2d7-95e5-4362-8867-fa5419a538c4', 6)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'a8845246-23cd-4b91-8cfa-01c107383c76', 20)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'DHHT', N'a8845246-23cd-4b91-8cfa-01c107383c76', 4)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'ab09c1d8-27ca-4855-8276-90523f346330', 1)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'b24d63dd-0c1c-426e-81fd-666697906199', 1)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'bda4c812-0a35-44a1-aec5-3be5fa6767d6', 3)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'DHHT', N'bda4c812-0a35-44a1-aec5-3be5fa6767d6', 5)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'MTT-16122020', N'bda4c812-0a35-44a1-aec5-3be5fa6767d6', 3)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'c1e48a1f-b196-4530-8334-25c7588c3f75', 10)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'd15cf4a4-4f0e-47ff-8744-8c52b802a656', 3)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BBLPM-18122020', N'd15cf4a4-4f0e-47ff-8744-8c52b802a656', 4)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'd964a4fc-25fb-4baa-b083-fa90956d311d', 50)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'e04c9934-7f52-44dd-99a9-97020b8f7ee8', 10)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'TSTT-16122020', N'e04c9934-7f52-44dd-99a9-97020b8f7ee8', 20)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'ed690edf-d868-49c6-8c9b-7e9c11e03d63', 3)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'CCY-16122020', N'ed690edf-d868-49c6-8c9b-7e9c11e03d63', 3)
INSERT [dbo].[CHI_TIET_HOA_DON] ([MAMH], [MAHD], [SOLUONG]) VALUES (N'BANHKEO1', N'fc4cbbc3-ffc5-4196-9303-9fb37492b297', 1)
GO
INSERT [dbo].[CHUC_VU] ([MACHUCVU], [TENCHUCVU], [LUONG]) VALUES (N'Admin', N'người quản trị cao cấp', 100000)
INSERT [dbo].[CHUC_VU] ([MACHUCVU], [TENCHUCVU], [LUONG]) VALUES (N'NV1', N'NHÂN VIÊN HẠNG 1', 100000)
INSERT [dbo].[CHUC_VU] ([MACHUCVU], [TENCHUCVU], [LUONG]) VALUES (N'NV2', N'NHÂN VIÊN HẠNG 2', 150000)
GO
INSERT [dbo].[DON_NHAP_HANG] ([MADNH], [MANCC], [NGAYNHAP]) VALUES (N'18b41a64-68a8-4be6-9560-416657a97b9b', N'VG', CAST(N'2020-12-16' AS Date))
INSERT [dbo].[DON_NHAP_HANG] ([MADNH], [MANCC], [NGAYNHAP]) VALUES (N'2487f5f3-26b1-4082-b1c9-526c5f272139', N'VG', CAST(N'2020-12-14' AS Date))
INSERT [dbo].[DON_NHAP_HANG] ([MADNH], [MANCC], [NGAYNHAP]) VALUES (N'35f89e59-7115-4db5-b540-4a3b1398b428', N'sdvn', CAST(N'2020-12-18' AS Date))
INSERT [dbo].[DON_NHAP_HANG] ([MADNH], [MANCC], [NGAYNHAP]) VALUES (N'540ba889-b577-4044-a562-87e9588741c1', N'fdvn', CAST(N'2020-12-19' AS Date))
INSERT [dbo].[DON_NHAP_HANG] ([MADNH], [MANCC], [NGAYNHAP]) VALUES (N'8d37628a-4a2e-4255-a724-199aec90e7c2', N'VG', CAST(N'2020-12-14' AS Date))
INSERT [dbo].[DON_NHAP_HANG] ([MADNH], [MANCC], [NGAYNHAP]) VALUES (N'ba229b87-8502-4e83-930c-c494a3deda93', N'fdvn', CAST(N'2020-12-18' AS Date))
INSERT [dbo].[DON_NHAP_HANG] ([MADNH], [MANCC], [NGAYNHAP]) VALUES (N'cc318350-3b6b-453a-ae6a-420bbdea426c', N'VG', CAST(N'2020-12-19' AS Date))
INSERT [dbo].[DON_NHAP_HANG] ([MADNH], [MANCC], [NGAYNHAP]) VALUES (N'f34da47b-03af-4994-b390-4c7a79b719dd', N'VG', CAST(N'2020-12-14' AS Date))
GO
INSERT [dbo].[GIAM_GIA] ([MAGG], [TEN], [NGAYBD], [NGAYKT], [PT_GIAM], [TIENGIAM], [LANSUDUNG], [LANSUDUNGMAX]) VALUES (N'GG1', N'khuyen mai mung mo cua', CAST(N'2020-12-18' AS Date), CAST(N'2020-12-30' AS Date), 20, 50000, 0, 1000)
INSERT [dbo].[GIAM_GIA] ([MAGG], [TEN], [NGAYBD], [NGAYKT], [PT_GIAM], [TIENGIAM], [LANSUDUNG], [LANSUDUNGMAX]) VALUES (N'GGNM', N'Gi?m giá m?ng nam m?i', CAST(N'2020-12-30' AS Date), CAST(N'2021-01-02' AS Date), 50, 200000, 3, 100)
INSERT [dbo].[GIAM_GIA] ([MAGG], [TEN], [NGAYBD], [NGAYKT], [PT_GIAM], [TIENGIAM], [LANSUDUNG], [LANSUDUNGMAX]) VALUES (N'GIAGGG', N'Huong Nguyen Dong', CAST(N'2020-12-13' AS Date), CAST(N'2020-12-31' AS Date), 100, 120000, 2, 100)
INSERT [dbo].[GIAM_GIA] ([MAGG], [TEN], [NGAYBD], [NGAYKT], [PT_GIAM], [TIENGIAM], [LANSUDUNG], [LANSUDUNGMAX]) VALUES (N'sale1212', N'siuu sale 12/12', CAST(N'2020-12-10' AS Date), CAST(N'2020-12-16' AS Date), 50, 100000, 2, 2)
GO
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'0b10d73b-4c82-47b4-960a-36268a5c1a76', N'6a73cc35-8887-4224-9692-2d670301d7fc', N'phamhoang2000', CAST(N'2020-12-17' AS Date), 1000000, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'147bc17f-89af-40e9-92c8-76b027791646', N'6a73cc35-8887-4224-9692-2d670301d7fc', N'Admin', CAST(N'2020-12-14' AS Date), 1000000, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'19127ea5-ea37-4d1d-8e95-973b3914a094', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'nhanvien2', CAST(N'2020-06-06' AS Date), 1E+07, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'1967ccd9-0f14-4a3e-84b5-200470b0316e', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'nhanvien3', CAST(N'2020-04-10' AS Date), 1.035E+07, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'2b4a281b-f8c2-4b9e-a19b-aa76d38fafd8', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'nhanvien1', CAST(N'2020-11-19' AS Date), 1.198E+07, N'GGNM')
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'380b5642-a047-47fc-b609-bcc536031680', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'nhanvien3', CAST(N'2020-12-13' AS Date), 2270000, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'4540bd76-6191-4d2d-8523-130dbbbd1c17', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'nhanvien1', CAST(N'2020-12-16' AS Date), 5880000, N'GIAGGG')
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'5159be19-6e68-4ecf-8185-8d19990fedf5', N'6a73cc35-8887-4224-9692-2d670301d7fc', N'Admin', CAST(N'2020-12-14' AS Date), 10000, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'5bcebd42-e0ee-4c6d-b8fe-7dbb695d5ddd', N'dba16a61-de4f-4f36-88e2-98d3e843f22a', N'nhanvien1', CAST(N'2020-12-12' AS Date), 1049000, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'64ae14c1-3325-4d20-91fb-3bc0e496f8a2', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'nhanvien2', CAST(N'2020-12-15' AS Date), 95800, N'GGNM')
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'67b2b388-c67e-473e-8f7c-db32a0391f8a', N'17585485-7ba8-49f5-893a-2ea40ee1360c', N'nhanvien2', CAST(N'2020-08-19' AS Date), 2.15E+07, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'7bb645fb-4489-4c81-a87e-55fdeb194518', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'nhanvien1', CAST(N'2020-09-20' AS Date), 2E+07, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'9438bb63-cc53-4a71-9a9c-df7437d0c636', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'phamhoang2001', CAST(N'2020-07-11' AS Date), 3E+07, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'a804b2d7-95e5-4362-8867-fa5419a538c4', N'b8502b91-83fc-48d3-b31d-bc82524b9104', N'nhanvien1', CAST(N'2020-12-10' AS Date), 1060000, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'a8845246-23cd-4b91-8cfa-01c107383c76', N'95df9817-358e-49e4-ad6e-f2e42c8dae12', N'nhanvien2', CAST(N'2020-05-19' AS Date), 2.004E+07, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'ab09c1d8-27ca-4855-8276-90523f346330', N'6af25a5c-cf6a-4659-8c72-1a0927ed8f05', N'nhanvien3', CAST(N'2020-12-11' AS Date), 1000000, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'b24d63dd-0c1c-426e-81fd-666697906199', N'6a73cc35-8887-4224-9692-2d670301d7fc', N'phamhoang2000', CAST(N'2020-12-17' AS Date), 1000000, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'bda4c812-0a35-44a1-aec5-3be5fa6767d6', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'nhanvien4', CAST(N'2020-12-15' AS Date), 3040000, N'sale1212')
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'c1e48a1f-b196-4530-8334-25c7588c3f75', N'b8502b91-83fc-48d3-b31d-bc82524b9104', N'nhanvien4', CAST(N'2020-03-05' AS Date), 9800000, N'GGNM')
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'd15cf4a4-4f0e-47ff-8744-8c52b802a656', N'5ad8018f-fe18-4bdb-bce3-0a5b82c5f686', N'nhanvien3', CAST(N'2020-12-18' AS Date), 3072000, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'd964a4fc-25fb-4baa-b083-fa90956d311d', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'nhanvien4', CAST(N'2020-02-05' AS Date), 4.988E+07, N'GIAGGG')
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'e04c9934-7f52-44dd-99a9-97020b8f7ee8', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'phamhoang2000', CAST(N'2020-10-19' AS Date), 1.032E+07, NULL)
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'ed690edf-d868-49c6-8c9b-7e9c11e03d63', N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'nhanvien2', CAST(N'2020-12-11' AS Date), 2921000, N'sale1212')
INSERT [dbo].[HOA_DON] ([MAHD], [MAKH], [MANV], [NGAYLHD], [TONGTIEN], [MAGIAMGIA]) VALUES (N'fc4cbbc3-ffc5-4196-9303-9fb37492b297', N'6a73cc35-8887-4224-9692-2d670301d7fc', N'phamhoang2000', CAST(N'2020-12-17' AS Date), 1000000, NULL)
GO
INSERT [dbo].[KHACH_HANG] ([MAKH], [TENKH], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [LOAIKH]) VALUES (N'1', N'1', N'1', CAST(N'2000-01-01' AS Date), N'11111111', N'1111111111', N'1', NULL)
INSERT [dbo].[KHACH_HANG] ([MAKH], [TENKH], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [LOAIKH]) VALUES (N'12a3bdf5-0dac-4176-8c45-d3e0d506150f', N'Angelia Zolie', N'nam', CAST(N'2000-12-01' AS Date), N'1154696841336', N'1233454513587', N'Washington DC', N'VIP1')
INSERT [dbo].[KHACH_HANG] ([MAKH], [TENKH], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [LOAIKH]) VALUES (N'17585485-7ba8-49f5-893a-2ea40ee1360c', N'Ngô Bá Khá', N'nam', CAST(N'1995-01-16' AS Date), N'1234567890', N'84326946745', N'Juventus', N'VIP1')
INSERT [dbo].[KHACH_HANG] ([MAKH], [TENKH], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [LOAIKH]) VALUES (N'5ad8018f-fe18-4bdb-bce3-0a5b82c5f686', N'Gducky', N'nam', CAST(N'1995-06-15' AS Date), N'077200001901', N'84326946745', N'RapViet', N'VIP1')
INSERT [dbo].[KHACH_HANG] ([MAKH], [TENKH], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [LOAIKH]) VALUES (N'6a73cc35-8887-4224-9692-2d670301d7fc', N'Dương Trung Bảoa', N'nam', CAST(N'2001-01-01' AS Date), N'077200001901', N'0326946745', N'12345', N'VIP1')
INSERT [dbo].[KHACH_HANG] ([MAKH], [TENKH], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [LOAIKH]) VALUES (N'6af25a5c-cf6a-4659-8c72-1a0927ed8f05', N'Huấn Hoa Hòe', N'nu', CAST(N'1990-01-18' AS Date), N'1234567890', N'0326946745', N'Juventus', N'VIP3')
INSERT [dbo].[KHACH_HANG] ([MAKH], [TENKH], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [LOAIKH]) VALUES (N'95df9817-358e-49e4-ad6e-f2e42c8dae12', N'DonalTrum', N'nam', CAST(N'1986-05-18' AS Date), N'077200001901', N'0326946745', N'America', N'VIP1')
INSERT [dbo].[KHACH_HANG] ([MAKH], [TENKH], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [LOAIKH]) VALUES (N'b8502b91-83fc-48d3-b31d-bc82524b9104', N'Kudo Shinichi', N'nam', CAST(N'1996-04-05' AS Date), N'1154696841336', N'12345678915', N'Tokyo', N'VIP1')
INSERT [dbo].[KHACH_HANG] ([MAKH], [TENKH], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [LOAIKH]) VALUES (N'dba16a61-de4f-4f36-88e2-98d3e843f22a', N'Binladen', N'nu', CAST(N'1942-09-18' AS Date), N'1234567890', N'84326946745', N'Iran', N'VIP1')
GO
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'BANHKEO1', 1, 458)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'BBLPM-18122020', 0, 84)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'CCY-16122020', 0, 37)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'DHHT', 0, 95)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'KTSCC-18122020', 0, 100)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'KTSCD-16122020', 0, 100)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'MTT-16122020', 0, 38)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'RCHDB-16122020', 0, 100)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'Revive', 1, 100)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'TSDL-16122020', 0, 100)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'TSTD-16122020', 0, 100)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'TSTT-16122020', 0, 80)
INSERT [dbo].[KHO] ([MAMH], [TRANGTHAI], [SOLUONG]) VALUES (N'TXHC-18122020', 0, 95)
GO
INSERT [dbo].[LOAI_KHACH_HANG] ([MALOAIKH], [TENLOAI]) VALUES (N'VIP1', N'VIP Kim Cương')
INSERT [dbo].[LOAI_KHACH_HANG] ([MALOAIKH], [TENLOAI]) VALUES (N'VIP2', N'VIP Bạch Kim')
INSERT [dbo].[LOAI_KHACH_HANG] ([MALOAIKH], [TENLOAI]) VALUES (N'VIP3', N'VIP Vàng')
INSERT [dbo].[LOAI_KHACH_HANG] ([MALOAIKH], [TENLOAI]) VALUES (N'VIP4', N'VIP Bạc')
GO
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'BANHKEO1', N'Bánh chocola', CAST(N'2021-01-09' AS Date), CAST(N'2021-01-09' AS Date), N'DA', 1000000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'BBLPM-18122020', N'Bánh bông lan phô mai', CAST(N'2020-12-30' AS Date), CAST(N'2020-12-30' AS Date), N'DA', 18000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'CCY-16122020', N'Chả cá Yuba', CAST(N'2020-12-16' AS Date), CAST(N'2020-12-30' AS Date), N'DA', 7000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'DHHT', N'but chi', CAST(N'2020-12-07' AS Date), CAST(N'2020-12-30' AS Date), N'DGD', 10000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'KTSCC-18122020', N'Kem tươi sữa chua cam', CAST(N'2020-12-18' AS Date), CAST(N'2020-12-30' AS Date), N'DU', 8000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'KTSCD-16122020', N'Kem tươi sữa chua dâu', CAST(N'2020-12-16' AS Date), CAST(N'2020-12-30' AS Date), N'DA', 8000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'MTT-16122020', N'Mì Tan Tan', CAST(N'2020-12-16' AS Date), CAST(N'2020-12-30' AS Date), N'DA', 30000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'RCHDB-16122020', N'Rau cau hoa đậu biếc', CAST(N'2020-12-16' AS Date), CAST(N'2020-12-30' AS Date), N'DA', 9000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'Revive', N'Revive bù nước bù khoáng', CAST(N'2020-12-06' AS Date), CAST(N'2020-12-31' AS Date), N'DU', 10000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'TSDL-16122020', N'Trà sữa dưa lưới', CAST(N'2020-12-16' AS Date), CAST(N'2020-12-30' AS Date), N'DU', 17000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'TSTD-16122020', N'Trà sữa trà đen ', CAST(N'2020-12-17' AS Date), CAST(N'2020-12-30' AS Date), N'DU', 15000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'TSTT-16122020', N'Trà sữa than tre', CAST(N'2020-12-18' AS Date), CAST(N'2020-12-30' AS Date), N'DU', 16000)
INSERT [dbo].[MAT_HANG] ([MAMH], [TENMH], [NGAYSX], [HANSD], [LOAI], [GIA]) VALUES (N'TXHC-18122020', N'Trà xoài hạt chia', CAST(N'2020-12-18' AS Date), CAST(N'2020-12-30' AS Date), N'DU', 15000)
GO
INSERT [dbo].[NHA_CUNG_CAP] ([MANCC], [TENNCC], [DIACHI], [SDT], [STK]) VALUES (N'fdvn', N'Foody.VN', N'109 Đường Dân Chủ Thủ Đức', N'0903534741', N'12345678')
INSERT [dbo].[NHA_CUNG_CAP] ([MANCC], [TENNCC], [DIACHI], [SDT], [STK]) VALUES (N'sdvn', N'sendovn', N'109 Đường Dân Chủ Tân Phú', N'0903534744', N'2484451357')
INSERT [dbo].[NHA_CUNG_CAP] ([MANCC], [TENNCC], [DIACHI], [SDT], [STK]) VALUES (N'spvn', N'shoppee', N'109 Đường Dân Chủ Quận 9', N'0903534749', N'2484451356')
INSERT [dbo].[NHA_CUNG_CAP] ([MANCC], [TENNCC], [DIACHI], [SDT], [STK]) VALUES (N'tiki', N'Tiki.VN', N'109 Đường Dân Chủ Gò Vấp', N'012356884877', N'12345679')
INSERT [dbo].[NHA_CUNG_CAP] ([MANCC], [TENNCC], [DIACHI], [SDT], [STK]) VALUES (N'VG', N'VinGroup1', N'Việt Nam', N'0326946745', N'19001560')
GO
INSERT [dbo].[NHAN_VIEN] ([MANV], [TENNV], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [NGAYLAMVIEC], [CHUCVU]) VALUES (N'Admin', N'Admin', N'nam', CAST(N'2000-01-02' AS Date), N'077200001901', N'+84326946745', N'Việt Nam', CAST(N'2020-12-06' AS Date), N'Admin')
INSERT [dbo].[NHAN_VIEN] ([MANV], [TENNV], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [NGAYLAMVIEC], [CHUCVU]) VALUES (N'nhanvien1', N'Nguyễn Thanh Nhân', N'nam', CAST(N'2000-01-01' AS Date), N'123456789', N'12345678910', N'Tân Bình', CAST(N'2020-12-18' AS Date), N'NV1')
INSERT [dbo].[NHAN_VIEN] ([MANV], [TENNV], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [NGAYLAMVIEC], [CHUCVU]) VALUES (N'nhanvien2', N'Lương Thế Anh', N'nam', CAST(N'2000-01-02' AS Date), N' 123456789', N'12345678911', N'Quận 9', CAST(N'2020-12-17' AS Date), N'NV1')
INSERT [dbo].[NHAN_VIEN] ([MANV], [TENNV], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [NGAYLAMVIEC], [CHUCVU]) VALUES (N'nhanvien3', N'Trương Thị Thảo Nguyên', N'nam', CAST(N'2000-01-03' AS Date), N'1154696841335', N'12345678912', N'Thủ Đức', CAST(N'2020-12-16' AS Date), N'NV2')
INSERT [dbo].[NHAN_VIEN] ([MANV], [TENNV], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [NGAYLAMVIEC], [CHUCVU]) VALUES (N'nhanvien4', N'Dương Trung Bảo', N'nam', CAST(N'2000-01-04' AS Date), N'1154696841334', N'12345678914', N'Quận 11', CAST(N'2020-12-16' AS Date), N'NV1')
INSERT [dbo].[NHAN_VIEN] ([MANV], [TENNV], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [NGAYLAMVIEC], [CHUCVU]) VALUES (N'phamhoang2000', N'Phạm Minh Hoàng', N'nam', CAST(N'2000-01-11' AS Date), N'215514629', N'0903534741', N'38 Đường Cây Keo Chung Cư Đạt Gia', CAST(N'2020-12-15' AS Date), N'Admin')
INSERT [dbo].[NHAN_VIEN] ([MANV], [TENNV], [GIOITINH], [NGAYSINH], [CMND], [SDT], [DIACHI], [NGAYLAMVIEC], [CHUCVU]) VALUES (N'phamhoang2001', N'Phạm Hoàng', N'nam', CAST(N'2000-01-01' AS Date), N'dsadsaddsadsadsada', N'0903534741', N'sdsadsadas', CAST(N'2020-01-12' AS Date), N'NV1')
GO
INSERT [dbo].[PHAN_LOAI] ([MALOAIMH], [TENLOAIMH]) VALUES (N'DA', N'Đồ Ăn')
INSERT [dbo].[PHAN_LOAI] ([MALOAIMH], [TENLOAIMH]) VALUES (N'DGD', N'Đồ Gia Dụng')
INSERT [dbo].[PHAN_LOAI] ([MALOAIMH], [TENLOAIMH]) VALUES (N'DU', N'Đồ Uống')
GO
ALTER TABLE [dbo].[CHI_TIET_DON_NHAP_HANG]  WITH CHECK ADD FOREIGN KEY([MADNH])
REFERENCES [dbo].[DON_NHAP_HANG] ([MADNH])
GO
ALTER TABLE [dbo].[CHI_TIET_DON_NHAP_HANG]  WITH CHECK ADD FOREIGN KEY([MAHANG])
REFERENCES [dbo].[MAT_HANG] ([MAMH])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[CHI_TIET_HOA_DON]  WITH CHECK ADD FOREIGN KEY([MAHD])
REFERENCES [dbo].[HOA_DON] ([MAHD])
GO
ALTER TABLE [dbo].[CHI_TIET_HOA_DON]  WITH CHECK ADD FOREIGN KEY([MAMH])
REFERENCES [dbo].[MAT_HANG] ([MAMH])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[DON_NHAP_HANG]  WITH CHECK ADD FOREIGN KEY([MANCC])
REFERENCES [dbo].[NHA_CUNG_CAP] ([MANCC])
GO
ALTER TABLE [dbo].[HOA_DON]  WITH CHECK ADD FOREIGN KEY([MAKH])
REFERENCES [dbo].[KHACH_HANG] ([MAKH])
GO
ALTER TABLE [dbo].[HOA_DON]  WITH CHECK ADD FOREIGN KEY([MANV])
REFERENCES [dbo].[NHAN_VIEN] ([MANV])
GO
ALTER TABLE [dbo].[HOA_DON]  WITH CHECK ADD  CONSTRAINT [FK_HOA_DON_GIAM_GIA] FOREIGN KEY([MAGIAMGIA])
REFERENCES [dbo].[GIAM_GIA] ([MAGG])
GO
ALTER TABLE [dbo].[HOA_DON] CHECK CONSTRAINT [FK_HOA_DON_GIAM_GIA]
GO
ALTER TABLE [dbo].[KHACH_HANG]  WITH CHECK ADD FOREIGN KEY([LOAIKH])
REFERENCES [dbo].[LOAI_KHACH_HANG] ([MALOAIKH])
GO
ALTER TABLE [dbo].[KHO]  WITH CHECK ADD  CONSTRAINT [FK_KHO_MAT_HANG] FOREIGN KEY([MAMH])
REFERENCES [dbo].[MAT_HANG] ([MAMH])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[KHO] CHECK CONSTRAINT [FK_KHO_MAT_HANG]
GO
ALTER TABLE [dbo].[MAT_HANG]  WITH CHECK ADD FOREIGN KEY([LOAI])
REFERENCES [dbo].[PHAN_LOAI] ([MALOAIMH])
GO
ALTER TABLE [dbo].[NHAN_VIEN]  WITH CHECK ADD FOREIGN KEY([CHUCVU])
REFERENCES [dbo].[CHUC_VU] ([MACHUCVU])
GO
ALTER TABLE [dbo].[CHI_TIET_DON_NHAP_HANG]  WITH CHECK ADD  CONSTRAINT [CH_SLNHAP] CHECK  (([CHI_TIET_DON_NHAP_HANG].[SOLUONG]>(0)))
GO
ALTER TABLE [dbo].[CHI_TIET_DON_NHAP_HANG] CHECK CONSTRAINT [CH_SLNHAP]
GO
ALTER TABLE [dbo].[DON_NHAP_HANG]  WITH CHECK ADD  CONSTRAINT [CH_NGAYNHAPHANG] CHECK  (([dbo].[KCNGAY]([DON_NHAP_HANG].[NGAYNHAP],getdate())>=(0)))
GO
ALTER TABLE [dbo].[DON_NHAP_HANG] CHECK CONSTRAINT [CH_NGAYNHAPHANG]
GO
ALTER TABLE [dbo].[GIAM_GIA]  WITH CHECK ADD  CONSTRAINT [CH_GIAM_GIA] CHECK  (([dbo].[KCNGAY]([GIAM_GIA].[NGAYBD],[GIAM_GIA].[NGAYKT])>(0)))
GO
ALTER TABLE [dbo].[GIAM_GIA] CHECK CONSTRAINT [CH_GIAM_GIA]
GO
ALTER TABLE [dbo].[KHO]  WITH CHECK ADD  CONSTRAINT [CH_SLKHO] CHECK  (([KHO].[SOLUONG]>=(0)))
GO
ALTER TABLE [dbo].[KHO] CHECK CONSTRAINT [CH_SLKHO]
GO
ALTER TABLE [dbo].[MAT_HANG]  WITH CHECK ADD  CONSTRAINT [CH_GIA] CHECK  (([MAT_HANG].[GIA]>(0)))
GO
ALTER TABLE [dbo].[MAT_HANG] CHECK CONSTRAINT [CH_GIA]
GO
ALTER TABLE [dbo].[NHA_CUNG_CAP]  WITH CHECK ADD  CONSTRAINT [CH_SDT] CHECK  ((len([NHA_CUNG_CAP].[SDT])>=(10)))
GO
ALTER TABLE [dbo].[NHA_CUNG_CAP] CHECK CONSTRAINT [CH_SDT]
GO
/****** Object:  StoredProcedure [dbo].[CHI_TIET_DON_NHAP_HANG_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[CHI_TIET_DON_NHAP_HANG_CREATE]
(@MA_DNH VARCHAR(50),
 @MA_MH VARCHAR(50),
 @SOLUONG INT
)
AS
BEGIN
	INSERT INTO CHI_TIET_DON_NHAP_HANG
	VALUES(@MA_DNH,@MA_MH,@SOLUONG)
END
GO
/****** Object:  StoredProcedure [dbo].[CHI_TIET_DON_NHAP_HANG_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CHI_TIET_DON_NHAP_HANG_DELETE]
(
    @MA_DNH VARCHAR(50)
)
AS
BEGIN

    DELETE FROM CHI_TIET_DON_NHAP_HANG
    WHERE CHI_TIET_DON_NHAP_HANG.MADNH = @MA_DNH
END
GO
/****** Object:  StoredProcedure [dbo].[CHI_TIET_DON_NHAP_HANG_DELETE_MA_DNH_MA_MH]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CHI_TIET_DON_NHAP_HANG_DELETE_MA_DNH_MA_MH]
(
    @MA_DNH VARCHAR(50),
    @MA_MH VARCHAR(50)
)
AS
BEGIN

    DELETE FROM CHI_TIET_DON_NHAP_HANG
    WHERE CHI_TIET_DON_NHAP_HANG.MADNH = @MA_DNH AND CHI_TIET_DON_NHAP_HANG.MAHANG = @MA_MH
END
GO
/****** Object:  StoredProcedure [dbo].[CHI_TIET_DON_NHAP_HANG_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CHI_TIET_DON_NHAP_HANG_UPDATE]
(
@MA_DNH_OLD VARCHAR(50),
 @MA_DNH_NEW VARCHAR(50),
 @MA_MH_OLD VARCHAR(50),
 @MA_MH_NEW VARCHAR(50),
@SOLUONG INT
)
AS
BEGIN 

    UPDATE CHI_TIET_DON_NHAP_HANG
    SET 
        MADNH = @MA_DNH_NEW,
        MAHANG = @MA_MH_NEW,
        SOLUONG = @SOLUONG    
    WHERE CHI_TIET_DON_NHAP_HANG.MADNH = @MA_DNH_OLD AND CHI_TIET_DON_NHAP_HANG.MAHANG = @MA_MH_OLD
END
GO
/****** Object:  StoredProcedure [dbo].[CHI_TIET_HOA_DON_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CHI_TIET_HOA_DON_CREATE]
(
    @MAMH VARCHAR(50),
    @MAHD NVARCHAR(50),
    @SOLUONG INT 
)

AS
BEGIN
    INSERT INTO CHI_TIET_HOA_DON
    VALUES
    (
        @MAMH  ,
        @MAHD  ,
        @SOLUONG 
    )
END
GO
/****** Object:  StoredProcedure [dbo].[CHI_TIET_HOA_DON_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CHI_TIET_HOA_DON_DELETE]
(@MAHD VARCHAR(50))
AS
BEGIN
    DELETE FROM CHI_TIET_HOA_DON
    WHERE CHI_TIET_HOA_DON.MAHD = @MAHD
END
GO
/****** Object:  StoredProcedure [dbo].[CHI_TIET_HOA_DON_DELETE_MAT_HANG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CHI_TIET_HOA_DON_DELETE_MAT_HANG]
(
    @MAMH VARCHAR(50) ,
    @MAHD VARCHAR(50)
)
AS
BEGIN
    DELETE FROM CHI_TIET_HOA_DON
    WHERE CHI_TIET_HOA_DON.MAMH = @MAMH AND CHI_TIET_HOA_DON.MAHD = @MAHD
END
GO
/****** Object:  StoredProcedure [dbo].[CHI_TIET_HOA_DON_GET]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[CHI_TIET_HOA_DON_GET]
(@MAHD VARCHAR(50))
AS 
BEGIN
    SELECT * 
    FROM CHI_TIET_HOA_DON
    WHERE MAHD = @MAHD
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[CHI_TIET_HOA_DON_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CHI_TIET_HOA_DON_GETALL]
AS
BEGIN
    SELECT * FROM CHI_TIET_HOA_DON
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[CHI_TIET_HOA_DON_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[CHI_TIET_HOA_DON_UPDATE]
(
    @MAMH VARCHAR(50),
    @MAHD VARCHAR(50),
    @SOLUONG INT 
)
AS
BEGIN
    UPDATE CHI_TIET_HOA_DON
    SET 
        MAMH = @MAMH ,
        MAHD = @MAHD ,
        SOLUONG = @SOLUONG 
    WHERE CHI_TIET_HOA_DON.MAHD = @MAHD
END
GO
/****** Object:  StoredProcedure [dbo].[CHUC_VU_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CHUC_VU_CREATE]
(@MA_CHUC_VU VARCHAR(50),
 @TEN_CHUC_VU NVARCHAR(200),
 @LUONG REAL)
 AS
 BEGIN
    INSERT INTO CHUC_VU
    VALUES(@MA_CHUC_VU,@TEN_CHUC_VU,@LUONG)
 END
GO
/****** Object:  StoredProcedure [dbo].[CHUC_VU_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 CREATE PROCEDURE [dbo].[CHUC_VU_DELETE]
(@MA_CHUC_VU VARCHAR(50))
AS
BEGIN
    DELETE FROM CHUC_VU
    WHERE MACHUCVU = @MA_CHUC_VU

END
GO
/****** Object:  StoredProcedure [dbo].[CHUC_VU_GET]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CHUC_VU_GET]
(@MA_CHUC_VU varchar(50))
AS
BEGIN
    SELECT * 
    FROM CHUC_VU
    WHERE MACHUCVU = @MA_CHUC_VU
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[CHUC_VU_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CHUC_VU_GETALL]
AS
BEGIN
    SELECT * FROM CHUC_VU
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[CHUC_VU_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CHUC_VU_UPDATE]
(@MA_CHUC_VU_OLD varchar(50),
 @MA_CHUC_VU_NEW VARCHAR(50),
 @TEN_CHUC_VU NVARCHAR(200),
 @LUONG REAL
)
AS
BEGIN
    UPDATE CHUC_VU
    SET 
        MACHUCVU = @MA_CHUC_VU_NEW,
        TENCHUCVU = @TEN_CHUC_VU,
        LUONG = @LUONG
    WHERE MACHUCVU = @MA_CHUC_VU_OLD   
END
GO
/****** Object:  StoredProcedure [dbo].[COUNT_HOA_DON]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[COUNT_HOA_DON]
AS
BEGIN
    SELECT COUNT(*) FROM HOA_DON
END

GO
/****** Object:  StoredProcedure [dbo].[COUNT_KHACH_HANG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[COUNT_KHACH_HANG]
AS
BEGIN
    SELECT COUNT(*) FROM KHACH_HANG
END
GO
/****** Object:  StoredProcedure [dbo].[COUNT_NHAN_VIEN]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[COUNT_NHAN_VIEN]
AS
BEGIN
    SELECT COUNT(*) FROM NHAN_VIEN
END

GO
/****** Object:  StoredProcedure [dbo].[DOANH_THU_10_NGAY_GAN_NHAT]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DOANH_THU_10_NGAY_GAN_NHAT]
AS
BEGIN
    SELECT a.IndividualDate,ISNULL(b.TONG,0) AS TONG
    FROM 
    DateRange('d', GETDATE()-10, GETDATE()) AS a
    LEFT JOIN (SELECT NGAYLHD,SUM(TONGTIEN)as TONG FROM HOA_DON GROUP BY NGAYLHD) as b
    ON a.IndividualDate = b.NGAYLHD
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[DOANH_THU_10_THANG_GAN_NHAT]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DOANH_THU_10_THANG_GAN_NHAT]
AS
BEGIN
    SELECT a.[MONTH],ISNULL(TONG,0) AS TONG
    FROM (
        SELECT MONTH(IndividualDate) as MONTH 
        FROM  DateRange('m',GETDATE()-304,GETDATE())
        ) as a
    LEFT JOIN (
         SELECT MONTH(NGAYLHD) AS MONTH,Sum(TONGTIEN) as TONG 
         FROM HOA_DON
        GROUP BY MONTH(NGAYLHD)
        ) AS b
    ON a.[MONTH] = b.[MONTH]
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[DON_NHAP_HANG_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DON_NHAP_HANG_CREATE]
(@MA_DNH VARCHAR(50),
 @MA_NCC VARCHAR(50),
 @NGAYNHAP DATE
)
AS
BEGIN
	INSERT INTO DON_NHAP_HANG
	VALUES(@MA_DNH,@MA_NCC,@NGAYNHAP)
END
GO
/****** Object:  StoredProcedure [dbo].[DON_NHAP_HANG_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DON_NHAP_HANG_DELETE]
(@MA_DNH VARCHAR(50))
AS
BEGIN
	DELETE FROM DON_NHAP_HANG
	WHERE MADNH = @MA_DNH
END
GO
/****** Object:  StoredProcedure [dbo].[DON_NHAP_HANG_GET_DETAILS]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[DON_NHAP_HANG_GET_DETAILS](@MADNH VARCHAR(50))
AS
BEGIN
    SELECT DON_NHAP_HANG.MADNH,
    NHA_CUNG_CAP.MANCC,
    NHA_CUNG_CAP.TENNCC,
    DON_NHAP_HANG.NGAYNHAP,
    CHI_TIET_DON_NHAP_HANG.MAHANG,
    MAT_HANG.TENMH,
    CHI_TIET_DON_NHAP_HANG.SOLUONG
    FROM DON_NHAP_HANG,NHA_CUNG_CAP,
    CHI_TIET_DON_NHAP_HANG,MAT_HANG
    WHERE DON_NHAP_HANG.MANCC = NHA_CUNG_CAP.MANCC 
    AND DON_NHAP_HANG.MADNH = CHI_TIET_DON_NHAP_HANG.MADNH 
    AND CHI_TIET_DON_NHAP_HANG.MAHANG = MAT_HANG.MAMH
    AND DON_NHAP_HANG.MADNH = @MADNH
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[DON_NHAP_HANG_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DON_NHAP_HANG_GETALL]
as 
BEGIN
      select * FROM DON_NHAP_HANG
      FOR json auto
end
GO
/****** Object:  StoredProcedure [dbo].[DON_NHAP_HANG_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DON_NHAP_HANG_UPDATE]
(@MA_DNH_OLD VARCHAR(50),
 @MA_DNH_NEW VARCHAR(50),
 @MA_NCC VARCHAR(50),
 @NGAYNHAP DATE
)
AS
BEGIN
	UPDATE DON_NHAP_HANG
	SET
		MADNH = @MA_DNH_NEW,
		MANCC = @MA_NCC,
		NGAYNHAP = @NGAYNHAP
	WHERE MADNH = @MA_DNH_OLD
END
GO
/****** Object:  StoredProcedure [dbo].[GIAM_GIA_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GIAM_GIA_CREATE]
(
    @MAGG VARCHAR(50),
    @TEN NVARCHAR(50),
    @NGAYBD DATE,
    @NGAYKT DATE,
    @PT_GIAM INT,
    @TIENGIAM REAL,
    @LANSUDUNGMAX INT
)
AS
BEGIN
    INSERT INTO GIAM_GIA
    VALUES
    (
        @MAGG ,
        @TEN ,
        @NGAYBD ,
        @NGAYKT ,
        @PT_GIAM ,
        @TIENGIAM ,
        0,
        @LANSUDUNGMAX 
    )
END
GO
/****** Object:  StoredProcedure [dbo].[GIAM_GIA_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GIAM_GIA_DELETE]
(@MAGG VARCHAR(50))
AS
BEGIN
    DELETE FROM GIAM_GIA
    WHERE GIAM_GIA.MAGG = @MAGG
END
GO
/****** Object:  StoredProcedure [dbo].[GIAM_GIA_GET]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GIAM_GIA_GET]
(@MAGG VARCHAR(50))
AS 
BEGIN
    SELECT * 
    FROM GIAM_GIA
    WHERE MAGG = @MAGG
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[GIAM_GIA_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GIAM_GIA_GETALL]
AS
BEGIN
    SELECT MAGG,TEN,NGAYBD,NGAYKT,PT_GIAM,TIENGIAM,LANSUDUNGMAX-LANSUDUNG AS CONLAI
    FROM GIAM_GIA
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[GIAM_GIA_ISEXITS]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GIAM_GIA_ISEXITS]
(@MAGG VARCHAR(50))
AS 
BEGIN
    
    SELECT Count(*) as IS_EXIST FROM GIAM_GIA
    WHERE @MAGG = GIAM_GIA.MAGG
    for JSON auto
END
GO
/****** Object:  StoredProcedure [dbo].[GIAM_GIA_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	CREATE PROC [dbo].[GIAM_GIA_UPDATE]
	(
	@MAGG VARCHAR(50),	
	@TEN NVARCHAR(200),
	@NGAYBD DATE,
	@NGAYKT DATE,
	@PT_GIAM INT,
	@TIENGIAM REAL,
	@LANSUDUNGMAX INT
	)
	AS
	BEGIN
		UPDATE GIAM_GIA
		SET 

			TEN = @TEN,
			NGAYBD =@NGAYBD,
			NGAYKT = @NGAYKT,
			PT_GIAM = @PT_GIAM,
			TIENGIAM = @TIENGIAM,
			LANSUDUNGMAX = @LANSUDUNGMAX

		WHERE MAGG = @MAGG
	END
GO
/****** Object:  StoredProcedure [dbo].[HOA_DON_CHECK_ENOUGH_QUANTITY_PRODUCT]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[HOA_DON_CHECK_ENOUGH_QUANTITY_PRODUCT] 
(@MAMH VARCHAR(50),@SOLUONG INT) 
AS
BEGIN
    DECLARE @SOLUONGTON INT
    SELECT @SOLUONGTON = KHO.SOLUONG
    FROM KHO
    WHERE KHO.MAMH = @MAMH
    IF( @SOLUONGTON- @SOLUONG < 20)
    BEGIN
        SELECT '0' AS KETQUA , KHO.SOLUONG AS SOLUONGTON
        FROM KHO
        WHERE KHO.MAMH = @MAMH
        FOR JSON AUTO
    END
    ELSE
    BEGIN
        SELECT '1' AS KETQUA , KHO.SOLUONG AS SOLUONGTON
        FROM KHO
        WHERE KHO.MAMH = @MAMH
        FOR JSON AUTO
    END
    
END
GO
/****** Object:  StoredProcedure [dbo].[HOA_DON_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[HOA_DON_CREATE]
(
    @MAHD VARCHAR(50),
    @MAKH VARCHAR(50),
    @MANV VARCHAR(50),
    @NGAYLHD DATE,
    @TONGTIEN REAL,
    @MAGIAMGIA VARCHAR(50)
)
AS
BEGIN
    INSERT INTO HOA_DON
    VALUES
    (
        @MAHD ,
        @MAKH ,
        @MANV ,
        @NGAYLHD ,
        @TONGTIEN ,
        @MAGIAMGIA
    )
END
GO
/****** Object:  StoredProcedure [dbo].[HOA_DON_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HOA_DON_DELETE]
(@MAHD VARCHAR(50))
AS
BEGIN
    DELETE FROM HOA_DON
    WHERE HOA_DON.MAHD = @MAHD
END
GO
/****** Object:  StoredProcedure [dbo].[HOA_DON_GET]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[HOA_DON_GET]
(@MAHD VARCHAR(50))
AS 
BEGIN
    SELECT * 
    FROM HOA_DON
    WHERE MAHD = @MAHD
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[HOA_DON_GET_DETAILS]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HOA_DON_GET_DETAILS](@MAHD VARCHAR(50))
AS
BEGIN
    select HOA_DON.MAHD, 
        HOA_DON.MANV, 
        NHAN_VIEN.TENNV, 
        HOA_DON.MAKH, 
        HOA_DON.NGAYLHD,
        KHACH_HANG.TENKH, 
        CHI_TIET_HOA_DON.MAMH,
        CHI_TIET_HOA_DON.SOLUONG,
        MAT_HANG.TENMH,
        MAT_HANG.GIA,
        HOA_DON.TONGTIEN,
        HOA_DON.MAGIAMGIA,
        (CHI_TIET_HOA_DON.SOLUONG * MAT_HANG.GIA ) as 'TONGGIASANPHAM'
    from HOA_DON,CHI_TIET_HOA_DON,KHACH_HANG,NHAN_VIEN, MAT_HANG
        where HOA_DON.MAHD = CHI_TIET_HOA_DON.MAHD
        and HOA_DON.MAKH = KHACH_HANG.MAKH 
        and HOA_DON.MANV = NHAN_VIEN.MANV 
        and CHI_TIET_HOA_DON.MAMH = MAT_HANG.MAMH and HOA_DON.MAHD = @MAHD
        for JSON auto
END
GO
/****** Object:  StoredProcedure [dbo].[HOA_DON_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[HOA_DON_GETALL]
AS
BEGIN
    SELECT MAHD,MAKH,MANV,NGAYLHD,ISNULL(TONGTIEN,0) AS TONGTIEN ,ISNULL(MAGIAMGIA,'NO')  AS MAGIAMGIA
    FROM HOA_DON
    for JSON auto
END
GO
/****** Object:  StoredProcedure [dbo].[HOA_DON_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[HOA_DON_UPDATE]
(
    @MAHD VARCHAR(50),
    @MAKH VARCHAR(50),
    @MANV VARCHAR(50),
    @NGAYLHD DATE,
    @TONGTIEN REAL
)
AS
BEGIN
    UPDATE HOA_DON
    SET 
        MAHD = @MAHD ,
        MAKH = @MAKH ,
        MANV = @MANV ,
        NGAYLHD = @NGAYLHD ,
        TONGTIEN = @TONGTIEN 
    WHERE HOA_DON.MAHD = @MAHD
END
GO
/****** Object:  StoredProcedure [dbo].[KHACH_HANG_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[KHACH_HANG_CREATE]
(
    @MAKH VARCHAR(50),
    @TENKH NVARCHAR(200),
    @GIOITINH VARCHAR(50),
    @NGAYSINH DATE,
    @CMND VARCHAR(50),
    @SDT VARCHAR(50),
    @DIACHI NVARCHAR(200),
    @LOAIKH VARCHAR(50)
)
AS
BEGIN
    INSERT INTO KHACH_HANG
    VALUES
    (
        @MAKH ,
        @TENKH ,
        @GIOITINH ,
        @NGAYSINH ,
        @CMND ,
        @SDT ,
        @DIACHI ,
        @LOAIKH
    )
END
GO
/****** Object:  StoredProcedure [dbo].[KHACH_HANG_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[KHACH_HANG_DELETE]
(@MAKH VARCHAR(50))
AS
BEGIN
    DELETE FROM KHACH_HANG
    WHERE KHACH_HANG.MAKH = @MAKH
END
GO
/****** Object:  StoredProcedure [dbo].[KHACH_HANG_GET]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[KHACH_HANG_GET]
(@MAKH VARCHAR(50))
AS 
BEGIN
    SELECT * 
    FROM KHACH_HANG
    WHERE MAKH = @MAKH
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[KHACH_HANG_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[KHACH_HANG_GETALL]
as
BEGIN
    SELECT MAKH,TENKH,GIOITINH,CMND,SDT,DIACHI,dbo.hamtinhtuoi(NGAYSINH) as TUOI,TENLOAI
    FROM KHACH_HANG,LOAI_KHACH_HANG
    WHERE KHACH_HANG.LOAIKH = LOAI_KHACH_HANG.MALOAIKH
    FOR JSON auto
END
GO
/****** Object:  StoredProcedure [dbo].[KHACH_HANG_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[KHACH_HANG_UPDATE]
(
    @MAKH VARCHAR(50),
    @TENKH NVARCHAR(200),
    @GIOITINH VARCHAR(50),
    @NGAYSINH DATE,
    @CMND VARCHAR(50),
    @SDT VARCHAR(50),
    @DIACHI NVARCHAR(200),
    @LOAIKH VARCHAR(50)
)
AS
BEGIN
    UPDATE KHACH_HANG
    SET 
        MAKH = @MAKH, 
        TENKH = @TENKH ,
        GIOITINH = @GIOITINH ,
        NGAYSINH = @NGAYSINH,
        CMND = @CMND ,
        SDT = @SDT ,
        DIACHI = @DIACHI ,
        LOAIKH = @LOAIKH 
    WHERE KHACH_HANG.MAKH = @MAKH
END
GO
/****** Object:  StoredProcedure [dbo].[KHO_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[KHO_GETALL]
as
BEGIN
      SELECT kho.MAMH,MAT_HANG.TENMH as TEN,SOLUONG
      from KHO,MAT_HANG
      WHERE KHO.MAMH = MAT_HANG.MAMH
      for JSON auto
end
GO
/****** Object:  StoredProcedure [dbo].[LOAI_KHACH_HANG_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LOAI_KHACH_HANG_CREATE]
(
    @MALOAIKH VARCHAR(50),
    @TENLOAI NVARCHAR(200)
)
AS
BEGIN
    INSERT INTO LOAI_KHACH_HANG
    VALUES
    (
       @MALOAIKH,
       @TENLOAI
    )
END
GO
/****** Object:  StoredProcedure [dbo].[LOAI_KHACH_HANG_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LOAI_KHACH_HANG_DELETE]
(@MALOAIKH VARCHAR(50))
AS
BEGIN
    DELETE FROM LOAI_KHACH_HANG
    WHERE LOAI_KHACH_HANG.MALOAIKH = @MALOAIKH
END
GO
/****** Object:  StoredProcedure [dbo].[LOAI_KHACH_HANG_GET]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[LOAI_KHACH_HANG_GET]
(@MALOAIKH VARCHAR(50))
AS 
BEGIN
    SELECT * 
    FROM LOAI_KHACH_HANG
    WHERE MALOAIKH  = @MALOAIKH
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[LOAI_KHACH_HANG_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LOAI_KHACH_HANG_GETALL]
AS
BEGIN
    SELECT * 
    FROM LOAI_KHACH_HANG
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[LOAI_KHACH_HANG_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LOAI_KHACH_HANG_UPDATE]
(
    @MALOAIKHOLD VARCHAR(50),
    @MALOAIKH VARCHAR(50),
    @TENLOAI NVARCHAR(200)
)
AS 
BEGIN 
    UPDATE LOAI_KHACH_HANG
    SET 
        MALOAIKH = @MALOAIKH,
        TENLOAI = @TENLOAI
    WHERE MALOAIKH = @MALOAIKHOLD
        
END
GO
/****** Object:  StoredProcedure [dbo].[MAT_HANG_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MAT_HANG_CREATE]
(@MAMH VARCHAR(50),
 @TENMH NVARCHAR(200),
 @NGAYSX DATE,
 @HANSD DATE,
 @LOAIMH VARCHAR(50),
 @GIA REAL)
 AS
 BEGIN
    INSERT INTO MAT_HANG
    VALUES(@MAMH,@TENMH, @NGAYSX,@HANSD,@LOAIMH,@GIA)
 END
GO
/****** Object:  StoredProcedure [dbo].[MAT_HANG_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MAT_HANG_DELETE]
(@MA_MH VARCHAR(50))
AS
BEGIN
    DELETE FROM MAT_HANG
    WHERE MAMH = @MA_MH

END
GO
/****** Object:  StoredProcedure [dbo].[MAT_HANG_GET]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MAT_HANG_GET]
(@MAMH VARCHAR(50))
AS
BEGIN
      SELECT * 
      FROM MAT_HANG
      WHERE MAMH = @MAMH
      for json auto
END
GO
/****** Object:  StoredProcedure [dbo].[MAT_HANG_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MAT_HANG_GETALL]
AS
BEGIN
    SELECT MAMH,TENMH,NGAYSX,HANSD,TENLOAIMH,GIA 
    FROM MAT_HANG
    LEFT JOIN PHAN_LOAI
    ON PHAN_LOAI.MALOAIMH = MAT_HANG.LOAI
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[MAT_HANG_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MAT_HANG_UPDATE]
(@MA_MH_OLD varchar(50),
 @MA_MH_NEW VARCHAR(50),
 @TEN_MH NVARCHAR(200),
 @NGAYSX DATE,
 @HANSD DATE,
 @LOAIMH VARCHAR(50),
 @GIA REAL
)
AS
BEGIN
    UPDATE MAT_HANG
    SET 
        MAMH = @MA_MH_NEW,
		TENMH = @TEN_MH,
		NGAYSX = @NGAYSX,
		HANSD = @HANSD,
		LOAI = @LOAIMH,
		GIA = @GIA
    WHERE MAMH = @MA_MH_OLD  
END
GO
/****** Object:  StoredProcedure [dbo].[NHA_CUNG_CAP_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----- CREATE NHÀ CUNG CẤP
CREATE PROCEDURE [dbo].[NHA_CUNG_CAP_CREATE]
(
@MA_NHA_CUNG_CAP VARCHAR(50),
@TEN_NHA_CUNG_CAP NVARCHAR(200),
@DIA_CHI NVARCHAR(200),
@SDT VARCHAR(50),
@STK VARCHAR(50)
)
AS
BEGIN
    INSERT INTO NHA_CUNG_CAP
    VALUES(
        @MA_NHA_CUNG_CAP,
        @TEN_NHA_CUNG_CAP,
        @DIA_CHI,
        @SDT,
        @STK
        )
END
GO
/****** Object:  StoredProcedure [dbo].[NHA_CUNG_CAP_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---DELETE NHA CUNG CAP
CREATE PROCEDURE [dbo].[NHA_CUNG_CAP_DELETE]
(@MA_NHA_CUNG_CAP VARCHAR(50))
AS
BEGIN
    DELETE FROM NHA_CUNG_CAP
    WHERE NHA_CUNG_CAP.MANCC = @MA_NHA_CUNG_CAP
END
GO
/****** Object:  StoredProcedure [dbo].[NHA_CUNG_CAP_GET]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[NHA_CUNG_CAP_GET](@MA_NHA_CUNG_CAP VARCHAR(50))
AS
BEGIN 
 SELECT * FROM NHA_CUNG_CAP
 WHERE MANCC =@MA_NHA_CUNG_CAP
 FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[NHA_CUNG_CAP_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[NHA_CUNG_CAP_GETALL]
AS
BEGIN
    SELECT * FROM NHA_CUNG_CAP
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[NHA_CUNG_CAP_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----UPDATE NHÀ CUNG CẤP
CREATE PROC [dbo].[NHA_CUNG_CAP_UPDATE]
(@MA_NHA_CUNG_CAP_OLD VARCHAR(50),
@MA_NHA_CUNG_CAP_NEW VARCHAR(50),
@TEN_NHA_CUNG_CAP NVARCHAR(200),
@DIA_CHI NVARCHAR(200),
@SDT VARCHAR(50),
@STK VARCHAR(50)
)
AS
BEGIN
    UPDATE NHA_CUNG_CAP
    SET 
        MANCC = @MA_NHA_CUNG_CAP_NEW,
        TENNCC = @TEN_NHA_CUNG_CAP,
        DIACHI = @DIA_CHI,
        SDT = @SDT,
        STK = @STK
    WHERE NHA_CUNG_CAP.MANCC  = @MA_NHA_CUNG_CAP_OLD
END
GO
/****** Object:  StoredProcedure [dbo].[NHAN_VIEN_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[NHAN_VIEN_CREATE](
    @TENNV NVARCHAR(200),
    @GIOITINH VARCHAR(50),
    @NGAYSINH DATE,
    @CMND VARCHAR(50),
    @SDT VARCHAR(50),
    @DIACHI NVARCHAR(200),
    @USERNAME VARCHAR(200),
    @NVPASSWORD VARCHAR(200),
    @NGAYLAMVIEC DATE,
    @CHUCVU  VARCHAR(50)
)
AS
BEGIN
    EXEC sp_addlogin @USERNAME,@NVPASSWORD; -- tạo login với username và password tương ứng
    EXEC sp_adduser @USERNAME,@USERNAME -- tạo user với username tương ứng
    IF @CHUCVU ='Admin' -- nếu chức vụ khác admin
    BEGIN
        EXEC sp_addrolemember 'sysadmin',@USERNAME -- thì gán role nhân viên cho username đó
    END
	ELSE
	BEGIN
        EXEC sp_addrolemember 'NhanVien',@USERNAME -- thì gán role nhân viên cho username đó
    END

    INSERT into NHAN_VIEN VALUES
    (@USERNAME,@TENNV,@GIOITINH,@NGAYSINH,@CMND,@SDT,@DIACHI,@NGAYLAMVIEC,@CHUCVU)
END
GO
/****** Object:  StoredProcedure [dbo].[NHAN_VIEN_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[NHAN_VIEN_DELETE](@MANV VARCHAR(50))
 as 
 BEGIN
    
     EXEC sp_dropuser @MANV;
     EXEC sp_droplogin @MANV;
     DELETE NHAN_VIEN WHERE NHAN_VIEN.MANV = @MANV

 end
GO
/****** Object:  StoredProcedure [dbo].[NHAN_VIEN_GET]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[NHAN_VIEN_GET]
(@MANV VARCHAR(50))
AS 
BEGIN
    SELECT * 
    FROM NHAN_VIEN
    WHERE MANV = @MANV
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[NHAN_VIEN_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[NHAN_VIEN_GETALL]
AS
BEGIN
    SELECT MANV,TENNV,NGAYLAMVIEC,TENCHUCVU
	FROM NHAN_VIEN,CHUC_VU
	WHERE NHAN_VIEN.CHUCVU = CHUC_VU.MACHUCVU
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[NHAN_VIEN_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[NHAN_VIEN_UPDATE]
(
    @MANV VARCHAR(50),
    @TENNV NVARCHAR(200),
    @GIOITINH VARCHAR(50),
    @NGAYSINH DATE,
    @CMND VARCHAR(50),
    @SDT VARCHAR(50),
    @DIACHI NVARCHAR(200),
    @NGAYLAMVIEC DATE,
    @CHUCVU VARCHAR(50)
    
)
AS
BEGIN
    UPDATE NHAN_VIEN
    SET 
        TENNV= @TENNV,
        GIOITINH = @GIOITINH,
        NGAYSINH = @NGAYSINH,
        CMND = @CMND,
        SDT = @SDT,
        DIACHI = @DIACHI,
        NGAYLAMVIEC = @NGAYLAMVIEC,
        CHUCVU = @CHUCVU
    WHERE MANV = @MANV
END
GO
/****** Object:  StoredProcedure [dbo].[PHAN_LOAI_CREATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PHAN_LOAI_CREATE]
(@MA_PHAN_LOAI VARCHAR(50),
 @TEN_MA_PHAN_LOAI NVARCHAR(200)
)
AS
BEGIN
	INSERT INTO PHAN_LOAI
	VALUES(@MA_PHAN_LOAI,@TEN_MA_PHAN_LOAI)
END
GO
/****** Object:  StoredProcedure [dbo].[PHAN_LOAI_DELETE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PHAN_LOAI_DELETE]
(@MA_PL VARCHAR(50))
AS
BEGIN
	DELETE FROM PHAN_LOAI
	WHERE MALOAIMH = @MA_PL
END
GO
/****** Object:  StoredProcedure [dbo].[PHAN_LOAI_GET]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[PHAN_LOAI_GET]
(@MA_PHAN_LOAI VARCHAR(50))
AS 
BEGIN
    SELECT * 
    FROM PHAN_LOAI
    WHERE MALOAIMH = @MA_PHAN_LOAI
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[PHAN_LOAI_GETALL]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[PHAN_LOAI_GETALL]
AS
BEGIN
    SELECT * FROM PHAN_LOAI
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[PHAN_LOAI_UPDATE]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PHAN_LOAI_UPDATE]
(@MA_PL_OLD VARCHAR(50),
 @MA_PL_NEW VARCHAR(50),
 @TEN_PL NVARCHAR(200)
)
AS
BEGIN
	UPDATE PHAN_LOAI
	SET 
		MALOAIMH = @MA_PL_NEW,
		TENLOAIMH = @TEN_PL
	WHERE MALOAIMH = @MA_PL_OLD
END 
GO
/****** Object:  StoredProcedure [dbo].[SUM_HOA_DON]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SUM_HOA_DON]
AS
BEGIN
    SELECT SUM(TONGTIEN) FROM HOA_DON
END
GO
/****** Object:  StoredProcedure [dbo].[THONG_KE_10_KHACH_HANG_MUA_NHIEU_NHAT]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[THONG_KE_10_KHACH_HANG_MUA_NHIEU_NHAT]
AS 
BEGIN
    -- lấy 10 khách hàng mua nhiều nhất
    select top (10)
    TENKH,ISNULL(Sum(TONGTIEN),0) as TONG from 
    KHACH_HANG 
    LEFT JOIN HOA_DON
    on KHACH_HANG.MAKH = HOA_DON.MAKH
    GROUP BY TENKH
    ORDER BY TONG DESC
    FOR JSON AUTO
END

GO
/****** Object:  StoredProcedure [dbo].[THONG_KE_10_NHAN_VIEN_DOANH_THU_CAO_NHAT]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[THONG_KE_10_NHAN_VIEN_DOANH_THU_CAO_NHAT]
AS
BEGIN
-- lấy 10 nhân viên có doanh thu cáo nhất KÈM THEO SỐ LƯỢNG ĐƠN
    SELECT TOP 10
    TENNV,COUNT(MAHD) as SOLUONGDONG,ISNULL(SUM(TONGTIEN),0) AS TONGTIEN
    from NHAN_VIEN
    LEFT JOIN HOA_DON
    ON HOA_DON.MANV = NHAN_VIEN.MANV
    GROUP BY TENNV
    ORDER BY SOLUONGDONG DESC,TONGTIEN DESC
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[THONG_KE_10_SAN_PHAM_MUA_NHIEU_NHAT]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[THONG_KE_10_SAN_PHAM_MUA_NHIEU_NHAT]
AS
BEGIN
--10 SẢN PHẨM ĐƯỢC  MUA NHIỀU NHẤT
    SELECT TOP 5
    TENMH,ISNULL(SL,0) AS SL
    FROM
    MAT_HANG
    LEFT JOIN(
                SELECT MAMH,SUM(SOLUONG) AS SL 
                FROM CHI_TIET_HOA_DON
                GROUP BY MAMH
            ) AS B
    ON MAT_HANG.MAMH = B.MAMH
    ORDER BY SL DESC
    FOR JSON AUTO
END
GO
/****** Object:  StoredProcedure [dbo].[THONG_KE_DOANH_THU_THANG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[THONG_KE_DOANH_THU_THANG](@THANG INT)
AS
BEGIN
    select NGAYLHD,SUM(TONGTIEN) as TONG from HOA_DON
    WHERE MONTH(NGAYLHD) = @THANG
    GROUP BY NGAYLHD
    for JSON auto
END
GO
/****** Object:  Trigger [dbo].[TG_INCREASE_SO_LUONG_KHO]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create TRIGGER [dbo].[TG_INCREASE_SO_LUONG_KHO] ON [dbo].[CHI_TIET_DON_NHAP_HANG] FOR INSERT
AS
BEGIN
        DECLARE @MAMH VARCHAR(50) , @DEM INT , @SOLUONG INT
        
        SELECT @MAMH = inserted.MAHANG , @SOLUONG = inserted.SOLUONG
        FROM inserted

        SELECT @DEM = COUNT(KHO.MAMH) 
        FROM KHO
        WHERE KHO.MAMH = @MAMH
        IF(@DEM = 0)
        BEGIN
            INSERT INTO KHO VALUES(@MAMH,1,@SOLUONG)
        END
        ELSE
        BEGIN
            UPDATE KHO
            SET KHO.SOLUONG = KHO.SOLUONG + @SOLUONG
            WHERE KHO.MAMH = @MAMH
        END 
END
GO
ALTER TABLE [dbo].[CHI_TIET_DON_NHAP_HANG] ENABLE TRIGGER [TG_INCREASE_SO_LUONG_KHO]
GO
/****** Object:  Trigger [dbo].[TG_DECREASE_SO_LUONG_KHO]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TG_DECREASE_SO_LUONG_KHO] ON [dbo].[CHI_TIET_HOA_DON] FOR INSERT
AS
BEGIN
        DECLARE @SOLUONGTON INT , @MAMH VARCHAR(50)
        UPDATE KHO
        SET KHO.SOLUONG = KHO.SOLUONG - (SELECT inserted.SOLUONG FROM inserted) , @SOLUONGTON = KHO.SOLUONG , @MAMH = KHO.MAMH
        WHERE KHO.MAMH = (
        SELECT inserted.MAMH
        FROM inserted)
        
        IF(@SOLUONGTON = 0)
        BEGIN
            UPDATE KHO
            SET KHO.TRANGTHAI = 0
            WHERE KHO.MAMH = @MAMH
        END
END
GO
ALTER TABLE [dbo].[CHI_TIET_HOA_DON] ENABLE TRIGGER [TG_DECREASE_SO_LUONG_KHO]
GO
/****** Object:  Trigger [dbo].[TG_SOLUONG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Bảng hóa đơn 

-- Bảng chi tiết hóa đơn

CREATE TRIGGER [dbo].[TG_SOLUONG] ON [dbo].[CHI_TIET_HOA_DON] FOR INSERT,UPDATE AS
BEGIN
    DECLARE @SOLUONG INT;
    SELECT @SOLUONG = inserted.SOLUONG
    FROM inserted
    IF(@SOLUONG<=0)
    BEGIN
        RAISERROR('SỐ LƯỢNG CỦA MỖI MẶT HÀNG TRONG HÓA ĐƠN PHẢI LỚN HƠN 0',16,1)
        ROLLBACK TRANSACTION
    END
END


-- bảng chi tiết hóa đơn

-- BANG CHUC VU
GO
ALTER TABLE [dbo].[CHI_TIET_HOA_DON] ENABLE TRIGGER [TG_SOLUONG]
GO
/****** Object:  Trigger [dbo].[TG_LUONG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TG_LUONG] ON [dbo].[CHUC_VU] FOR INSERT,UPDATE 
AS
BEGIN
DECLARE @LUONG INT;
SELECT @LUONG = LUONG
FROM INSERTED
IF(@LUONG<=0) 
    BEGIN
    RAISERROR('LUONG PHAI LON HON 0',16,1)
    ROLLBACK TRANSACTION
END
END

-- BANG CHUC VU

-- BANG KHACH HANG
GO
ALTER TABLE [dbo].[CHUC_VU] ENABLE TRIGGER [TG_LUONG]
GO
/****** Object:  Trigger [dbo].[TG_DECREASE_LAN_SU_DUNG_MAGG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TG_DECREASE_LAN_SU_DUNG_MAGG] ON [dbo].[HOA_DON] FOR INSERT
AS
BEGIN
        DECLARE @lansudung int , @MA_GIAM_GIA VARCHAR(50) , @LANSUDUNGMAX INT
        UPDATE GIAM_GIA
        SET GIAM_GIA.LANSUDUNG = GIAM_GIA.LANSUDUNG + 1 , 
        @lansudung = GIAM_GIA.LANSUDUNG , 
        @MA_GIAM_GIA = GIAM_GIA.MAGG,
        @LANSUDUNGMAX = GIAM_GIA.LANSUDUNGMAX
        WHERE GIAM_GIA.MAGG = (
            SELECT inserted.MAGIAMGIA
            FROM inserted
        )
        IF(@lansudung = @LANSUDUNGMAX)
        BEGIN
                DELETE FROM GIAM_GIA
                WHERE GIAM_GIA.MAGG = @MA_GIAM_GIA
        END   
END
GO
ALTER TABLE [dbo].[HOA_DON] ENABLE TRIGGER [TG_DECREASE_LAN_SU_DUNG_MAGG]
GO
/****** Object:  Trigger [dbo].[TG_NGAYLHD]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[TG_NGAYLHD] ON [dbo].[HOA_DON] FOR INSERT,UPDATE AS
BEGIN
    DECLARE @NGAYLHD DATE;
    SELECT @NGAYLHD = inserted.NGAYLHD
    FROM inserted
    IF(dbo.hamtinhngay(@NGAYLHD)<0)
    BEGIN
        RAISERROR('NGÀY LẬP HÓA ĐƠN PHẢI TRƯỚC NGÀY HIỆN  TẠI',16,1)
        ROLLBACK TRANSACTION
    END
END
GO
ALTER TABLE [dbo].[HOA_DON] ENABLE TRIGGER [TG_NGAYLHD]
GO
/****** Object:  Trigger [dbo].[TG_NGAYSINH]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TG_NGAYSINH] ON [dbo].[KHACH_HANG] FOR INSERT,UPDATE 
AS
BEGIN
DECLARE @NGAYSINH DATE;
SELECT @NGAYSINH = inserted.NGAYSINH
FROM INSERTED
IF(dbo.hamtinhtuoi(@NGAYSINH) <12 ) 
    BEGIN
    RAISERROR('KHÁCH HÀNG PHẢI TỪ 12 TUỔI TRỞ LÊN',16,1)
    ROLLBACK TRANSACTION
    END
END

GO
ALTER TABLE [dbo].[KHACH_HANG] ENABLE TRIGGER [TG_NGAYSINH]
GO
/****** Object:  Trigger [dbo].[TG_SDT_KHACH_HANG]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[TG_SDT_KHACH_HANG] ON [dbo].[KHACH_HANG] FOR INSERT,UPDATE AS
BEGIN
    DECLARE @SDT VARCHAR(50);
    SELECT @SDT = inserted.SDT
    FROM inserted
    IF(LEN(@SDT) <10)
    BEGIN
        RAISERROR('SỐ ĐIỆN THOẠI PHẢI TỪ 10 SỐ TRỞ LÊN ',16,1)
        ROLLBACK TRANSACTION
    END
END
GO
ALTER TABLE [dbo].[KHACH_HANG] ENABLE TRIGGER [TG_SDT_KHACH_HANG]
GO
/****** Object:  Trigger [dbo].[DELETE_KHO]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create TRIGGER [dbo].[DELETE_KHO] 
ON [dbo].[MAT_HANG]
FOR DELETE
AS
BEGIN
      DECLARE @m  VARCHAR(50);
      SELECT @m = MAMH
      from deleted

      delete KHO
      WHERE kho.MAMH = @m
END
GO
ALTER TABLE [dbo].[MAT_HANG] ENABLE TRIGGER [DELETE_KHO]
GO
/****** Object:  Trigger [dbo].[INSERT_KHO]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create TRIGGER [dbo].[INSERT_KHO] 
ON [dbo].[MAT_HANG]
FOR INSERT
AS
BEGIN
      DECLARE @m  VARCHAR(50);
      SELECT @m = inserted.MAMH
      from inserted

      INSERT INTO KHO
      VALUES (@m,0,0)
END
GO
ALTER TABLE [dbo].[MAT_HANG] ENABLE TRIGGER [INSERT_KHO]
GO
/****** Object:  Trigger [dbo].[TG_CMND]    Script Date: 12/19/2020 2:52:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TG_CMND] ON [dbo].[NHAN_VIEN] FOR INSERT,UPDATE AS
BEGIN
    DECLARE @CMND VARCHAR(50);
    SELECT @CMND = inserted.CMND
    FROM inserted
    IF(LEN(@CMND) <5)
    BEGIN
        RAISERROR('CHỨNG MINH NHÂN DÂN PHẢI TỪ 5 SỐ TRỞ LÊN ',16,1)
        ROLLBACK TRANSACTION
    END
END
GO
ALTER TABLE [dbo].[NHAN_VIEN] ENABLE TRIGGER [TG_CMND]
GO
/****** Object:  Trigger [dbo].[TG_NGAY_SINH]    Script Date: 12/19/2020 2:52:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TG_NGAY_SINH] ON [dbo].[NHAN_VIEN] FOR INSERT,UPDATE AS
BEGIN
    DECLARE @NGAY_SINH DATE;
    SELECT @NGAY_SINH = inserted.NGAYSINH
    FROM inserted
    IF(dbo.hamtinhtuoi(@NGAY_SINH) <18)
    BEGIN
        RAISERROR('TUỔI CỦA NHÂN VIÊN PHẢI LỚN HƠN 18',16,1)
        ROLLBACK TRANSACTION
    END
END

GO
ALTER TABLE [dbo].[NHAN_VIEN] ENABLE TRIGGER [TG_NGAY_SINH]
GO
/****** Object:  Trigger [dbo].[TG_SDT]    Script Date: 12/19/2020 2:52:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE TRIGGER [dbo].[TG_SDT] ON [dbo].[NHAN_VIEN] FOR INSERT,UPDATE AS
BEGIN
    DECLARE @SDT VARCHAR(50);
    SELECT @SDT = inserted.SDT
    FROM inserted
    IF(LEN(@SDT) <10)
    BEGIN
        RAISERROR('SỐ ĐIỆN THOẠI PHẢI TỪ 10 SỐ TRỞ LÊN ',16,1)
        ROLLBACK TRANSACTION
    END
END
GO
ALTER TABLE [dbo].[NHAN_VIEN] ENABLE TRIGGER [TG_SDT]
GO
USE [master]
GO
ALTER DATABASE [NHOM3] SET  READ_WRITE 
GO