lyfz_repos/其他源码/lyfzems.sql

USE [master]
GO
/****** Object:  Database [lyfzems]    Script Date: 2017-05-29 15:54:12 ******/
CREATE DATABASE [lyfzems] ON  PRIMARY 
( NAME = N'lyfzems', FILENAME = N'E:\lyfzClassicSoft\服务端\数据\lyfzems.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'lyfzems_log', FILENAME = N'E:\lyfzClassicSoft\服务端\数据\lyfzems_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [lyfzems] SET COMPATIBILITY_LEVEL = 90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [lyfzems].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [lyfzems] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [lyfzems] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [lyfzems] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [lyfzems] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [lyfzems] SET ARITHABORT OFF 
GO
ALTER DATABASE [lyfzems] SET AUTO_CLOSE ON 
GO
ALTER DATABASE [lyfzems] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [lyfzems] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [lyfzems] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [lyfzems] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [lyfzems] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [lyfzems] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [lyfzems] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [lyfzems] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [lyfzems] SET  DISABLE_BROKER 
GO
ALTER DATABASE [lyfzems] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [lyfzems] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [lyfzems] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [lyfzems] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [lyfzems] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [lyfzems] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [lyfzems] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [lyfzems] SET  MULTI_USER 
GO
ALTER DATABASE [lyfzems] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [lyfzems] SET DB_CHAINING OFF 
GO
USE [lyfzems]
GO
/****** Object:  Table [dbo].[branch]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[branch](
	[enterprise_id] [nvarchar](10) NOT NULL,
	[head_office] [bit] NOT NULL,
	[branch_id] [nvarchar](15) NOT NULL,
	[branch_name] [nvarchar](64) NOT NULL,
	[branch_phone] [nvarchar](32) NULL,
	[branch_superintendent] [nvarchar](32) NULL,
	[branch_address] [nvarchar](128) NULL,
	[branch_account] [nvarchar](16) NULL,
	[branch_password] [nvarchar](16) NULL,
	[residual_flow] [bigint] NULL,
	[branch_note] [nvarchar](255) NULL,
	[charges_type] [smallint] NOT NULL,
	[date_of_expiry] [nvarchar](32) NULL,
 CONSTRAINT [PK_branch] PRIMARY KEY CLUSTERED 
(
	[branch_id] 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].[downloaded_image]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[downloaded_image](
	[upload_id] [nvarchar](50) NOT NULL,
	[download_branch_id] [nvarchar](15) NOT NULL,
	[download_image_name] [nvarchar](255) NOT NULL,
	[download_image_size] [int] NOT NULL,
	[download_datetime] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_tbl_download_record] PRIMARY KEY CLUSTERED 
(
	[upload_id] ASC,
	[download_image_name] 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].[enterprise]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[enterprise](
	[industry_id] [nvarchar](4) NOT NULL,
	[enterprise_id] [nvarchar](10) NOT NULL,
	[enterprise_name] [nvarchar](64) NOT NULL,
	[enterprise_address] [nvarchar](128) NULL,
	[legal_person] [nvarchar](32) NULL,
	[company_website] [nvarchar](255) NULL,
	[corporate_phone] [nvarchar](32) NULL,
	[enterprise_note] [nvarchar](255) NULL,
	[access_key_id] [nvarchar](32) NULL,
	[access_key_secret] [nvarchar](64) NULL,
	[dndpoint] [nvarchar](128) NULL,
	[bucket_name] [nvarchar](36) NULL,
 CONSTRAINT [PK_enterprise] PRIMARY KEY CLUSTERED 
(
	[enterprise_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UQ_enterprise_name] UNIQUE NONCLUSTERED 
(
	[enterprise_name] 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].[industry]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[industry](
	[industry_id] [nvarchar](4) NOT NULL,
	[industry_name] [nvarchar](32) NOT NULL,
	[industry_note] [nvarchar](255) NULL,
 CONSTRAINT [PK_industry] PRIMARY KEY CLUSTERED 
(
	[industry_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UQ_industry_name] UNIQUE NONCLUSTERED 
(
	[industry_name] 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].[recharge_record]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[recharge_record](
	[branch_id] [nvarchar](32) NOT NULL,
	[recharge_datetime] [nvarchar](32) NOT NULL,
	[recharge_money] [money] NOT NULL,
	[recharge_flow] [bigint] NOT NULL,
	[recharge_note] [nvarchar](255) NULL,
	[charges_type] [smallint] NOT NULL,
	[recharge_month] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[system_config]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[system_config](
	[system_config_id] [int] IDENTITY(1,1) NOT NULL,
	[system_config_key] [nvarchar](255) NOT NULL,
	[system_config_value] [nvarchar](255) NULL,
 CONSTRAINT [PK_system_config] PRIMARY KEY CLUSTERED 
(
	[system_config_id] ASC,
	[system_config_key] 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].[upload_order]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[upload_order](
	[upload_id] [nvarchar](64) NOT NULL,
	[upload_datetime] [nvarchar](32) NOT NULL,
	[upload_branch_id] [nvarchar](32) NOT NULL,
	[download_branch_id] [nvarchar](32) NOT NULL,
	[upload_image_type] [smallint] NOT NULL,
	[upload_order] [nvarchar](32) NOT NULL,
	[upload_images] [nvarchar](max) NOT NULL,
	[upload_status] [smallint] NOT NULL,
	[auto_download] [bit] NOT NULL,
	[download_storage_dir] [nvarchar](255) NULL,
	[download_status] [smallint] NOT NULL,
 CONSTRAINT [PK_upload_order] PRIMARY KEY CLUSTERED 
(
	[upload_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[uploaded_image]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[uploaded_image](
	[upload_id] [nvarchar](50) NOT NULL,
	[upload_branch_id] [nvarchar](15) NOT NULL,
	[upload_image_name] [nvarchar](255) NOT NULL,
	[upload_key] [nvarchar](64) NOT NULL,
	[upload_image_size] [int] NOT NULL,
	[upload_datetime] [nvarchar](32) NOT NULL,
	[download_times] [int] NOT NULL,
 CONSTRAINT [PK_uploaded_image] PRIMARY KEY CLUSTERED 
(
	[upload_id] ASC,
	[upload_image_name] 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].[branch_detail]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[branch_detail]
AS
SELECT   dbo.industry.industry_id, dbo.industry.industry_name, dbo.enterprise.enterprise_id, 
                dbo.enterprise.enterprise_name, dbo.branch.head_office, dbo.branch.branch_id, 
                dbo.branch.branch_name, dbo.branch.branch_phone, 
                dbo.branch.branch_superintendent, dbo.branch.branch_address, 
                dbo.branch.branch_account, dbo.branch.branch_password, 
                dbo.branch.residual_flow, dbo.branch.branch_note, dbo.branch.charges_type, 
                dbo.branch.date_of_expiry
FROM      dbo.branch INNER JOIN
                dbo.enterprise ON dbo.branch.enterprise_id = dbo.enterprise.enterprise_id INNER JOIN
                dbo.industry ON dbo.enterprise.industry_id = dbo.industry.industry_id
GO
/****** Object:  View [dbo].[enterprise_detail]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[enterprise_detail]
AS
SELECT   
dbo.industry.industry_id, 
dbo.industry.industry_name, 
dbo.enterprise.enterprise_id, 
dbo.enterprise.enterprise_name, 
dbo.enterprise.enterprise_address, 
dbo.enterprise.legal_person, 
dbo.enterprise.company_website, 
dbo.enterprise.corporate_phone, 
dbo.enterprise.access_key_id, 
dbo.enterprise.access_key_secret, 
dbo.enterprise.dndpoint, 
dbo.enterprise.bucket_name,  
dbo.enterprise.enterprise_note
FROM      dbo.industry INNER JOIN
                dbo.enterprise ON dbo.industry.industry_id = dbo.enterprise.industry_id
GO
/****** Object:  View [dbo].[need_download]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[need_download]
AS
with myexcept
as
(
select upload_id,upload_image_name from uploaded_image except select upload_id,download_image_name from downloaded_image
)
select * from uploaded_image where upload_image_name in (select upload_image_name from myexcept)
GO
/****** Object:  View [dbo].[recharge_detail]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[recharge_detail]
AS
SELECT   
dbo.industry.industry_id, 
dbo.industry.industry_name, 
dbo.enterprise.enterprise_id, 
dbo.enterprise.enterprise_name, 
dbo.branch.branch_id, 
dbo.branch.branch_name, 
dbo.recharge_record.recharge_datetime, 
dbo.recharge_record.recharge_money, 
dbo.recharge_record.recharge_flow, 
dbo.recharge_record.recharge_note, 
dbo.recharge_record.charges_type, 
dbo.recharge_record.recharge_month
FROM      dbo.recharge_record INNER JOIN
                dbo.branch ON dbo.recharge_record.branch_id = dbo.branch.branch_id INNER JOIN
                dbo.enterprise ON dbo.branch.enterprise_id = dbo.enterprise.enterprise_id INNER JOIN
                dbo.industry ON dbo.enterprise.industry_id = dbo.industry.industry_id
GO
/****** Object:  View [dbo].[upload_order_detail]    Script Date: 2017-05-29 15:54:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





CREATE VIEW [dbo].[upload_order_detail]
AS
select 
upload_order.upload_id, 
upload_order.upload_branch_id, 
substring(upload_order.upload_branch_id,0,10) as upload_enterprise_id,
(select enterprise_name from enterprise where enterprise.enterprise_id = substring(upload_order.upload_branch_id,0,10) )as upload_enterprise_name,
(select branch_name from branch where branch.branch_id = upload_order.upload_branch_id)as upload_branch_name,
upload_order.download_branch_id, 
substring(upload_order.download_branch_id,0,10) as download_enterprise_id,
(select enterprise_name from enterprise where enterprise.enterprise_id = substring(upload_order.download_branch_id,0,10) )as download_enterprise_name,
(select branch_name from branch where branch.branch_id = upload_order.download_branch_id)as download_branch_name,
upload_order.upload_image_type,
upload_order.upload_order,
upload_order.upload_images,
upload_order.upload_status,
upload_order.upload_datetime,
upload_order.auto_download,
upload_order.download_storage_dir,
upload_order.download_status
from upload_order 
GO
ALTER TABLE [dbo].[branch] ADD  CONSTRAINT [DF_branch_charges_type]  DEFAULT ((0)) FOR [charges_type]
GO
ALTER TABLE [dbo].[recharge_record] ADD  CONSTRAINT [DF_tbl_recharge_RechargeMonth]  DEFAULT ((0)) FOR [recharge_month]
GO
ALTER TABLE [dbo].[uploaded_image] ADD  CONSTRAINT [DF__tbl_uploa__Downl__1367E606]  DEFAULT ((0)) FOR [download_times]
GO
ALTER TABLE [dbo].[branch]  WITH CHECK ADD  CONSTRAINT [FK_enterprise_id] FOREIGN KEY([enterprise_id])
REFERENCES [dbo].[enterprise] ([enterprise_id])
GO
ALTER TABLE [dbo].[branch] CHECK CONSTRAINT [FK_enterprise_id]
GO
ALTER TABLE [dbo].[enterprise]  WITH CHECK ADD  CONSTRAINT [FK_industry_id] FOREIGN KEY([industry_id])
REFERENCES [dbo].[industry] ([industry_id])
GO
ALTER TABLE [dbo].[enterprise] CHECK CONSTRAINT [FK_industry_id]
GO
USE [master]
GO
ALTER DATABASE [lyfzems] SET  READ_WRITE 
GO