USE [unionpay.knowfar.net.cn]
GO
/****** Object:  Table [dbo].[jpay_app]    Script Date: 2021/2/13 13:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[jpay_app](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[appid] [nvarchar](20) NOT NULL CONSTRAINT [DF__jpay_app__appid__1273C1CD]  DEFAULT (''),
	[appkey] [nvarchar](40) NOT NULL CONSTRAINT [DF__jpay_app__appkey__1367E606]  DEFAULT (''),
	[appname] [nvarchar](40) NOT NULL CONSTRAINT [DF__jpay_app__appnam__145C0A3F]  DEFAULT (''),
	[applogo] [nvarchar](150) NULL,
	[appurl] [nvarchar](150) NULL,
	[notify_url] [nvarchar](150) NOT NULL CONSTRAINT [DF__jpay_app__notify__15502E78]  DEFAULT (''),
	[return_url] [nvarchar](150) NULL,
	[return_url_mobile] [nvarchar](150) NULL,
	[state] [int] NOT NULL CONSTRAINT [DF__jpay_app__state__164452B1]  DEFAULT ((0)),
	[account_alipay] [nvarchar](50) NULL,
	[account_wxpay] [nvarchar](50) NULL,
 CONSTRAINT [PK_pay_app] PRIMARY KEY CLUSTERED 
(
	[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].[jpay_order]    Script Date: 2021/2/13 13:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[jpay_order](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[appid] [nvarchar](20) NULL,
	[order_id] [nvarchar](40) NOT NULL DEFAULT (''),
	[pay_method] [nvarchar](10) NOT NULL DEFAULT (''),
	[pay_amount] [numeric](18, 2) NOT NULL DEFAULT ((0)),
	[order_time] [datetime] NOT NULL DEFAULT (getdate()),
	[pay_status] [int] NOT NULL DEFAULT ((0)),
	[product_name] [nvarchar](50) NULL,
	[product_desc] [nvarchar](200) NULL,
	[out_trade_no] [nvarchar](40) NULL,
	[pay_order_id] [nvarchar](40) NULL,
	[NoticeTimes] [int] NOT NULL DEFAULT ((0)),
	[notified] [int] NOT NULL DEFAULT ((0)),
	[pay_time] [datetime] NULL,
	[MustCheckPayState] [int] NOT NULL DEFAULT ((0)),
	[buyer_account] [nvarchar](100) NULL,
 CONSTRAINT [PK_pay_order] PRIMARY KEY CLUSTERED 
(
	[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].[jpay_payment_account]    Script Date: 2021/2/13 13:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[jpay_payment_account](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[account_name] [nvarchar](40) NOT NULL DEFAULT (''),
	[account_alipay] [nvarchar](50) NULL,
	[account_wxpay] [nvarchar](50) NULL
) ON [PRIMARY]

GO
/****** Object:  View [dbo].[View_App]    Script Date: 2021/2/13 13:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create VIEW [dbo].[View_App]
AS
SELECT   a.*
                ,a_a.account_name as account_alipay_name
                          ,a_w.account_name as account_wxpay_name
FROM      dbo.jpay_app AS a
left join jpay_payment_account a_a ON a_a.account_alipay=a.account_alipay
left join jpay_payment_account a_w ON a_w.account_wxpay=a.account_wxpay

GO
/****** Object:  View [dbo].[View_Order]    Script Date: 2021/2/13 13:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[View_Order]
AS
SELECT   o.*, a.account_alipay, a.account_wxpay
                ,a_a.account_name as account_alipay_name
                          ,a_w.account_name as account_wxpay_name
FROM      dbo.jpay_order AS o 

LEFT OUTER JOIN dbo.jpay_app AS a ON o.appid = a.appid
left join jpay_payment_account a_a ON a_a.account_alipay=a.account_alipay
left join jpay_payment_account a_w ON a_w.account_wxpay=a.account_wxpay




GO
/****** Object:  View [dbo].[View_Top_Order]    Script Date: 2021/2/13 13:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create VIEW [dbo].[View_Top_Order]
AS
SELECT  top 100 * from jpay_order order by ID desc


GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [idx_account_alipay]    Script Date: 2021/2/13 13:23:36 ******/
CREATE NONCLUSTERED INDEX [idx_account_alipay] ON [dbo].[jpay_app]
(
	[account_alipay] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [idx_account_wxpay]    Script Date: 2021/2/13 13:23:36 ******/
CREATE NONCLUSTERED INDEX [idx_account_wxpay] ON [dbo].[jpay_app]
(
	[account_wxpay] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [idx_appid]    Script Date: 2021/2/13 13:23:36 ******/
CREATE NONCLUSTERED INDEX [idx_appid] ON [dbo].[jpay_order]
(
	[appid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [idx_MustCheckPayState]    Script Date: 2021/2/13 13:23:36 ******/
CREATE NONCLUSTERED INDEX [idx_MustCheckPayState] ON [dbo].[jpay_order]
(
	[MustCheckPayState] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [idx_order_id]    Script Date: 2021/2/13 13:23:36 ******/
CREATE NONCLUSTERED INDEX [idx_order_id] ON [dbo].[jpay_order]
(
	[order_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [idx_order_time]    Script Date: 2021/2/13 13:23:36 ******/
CREATE NONCLUSTERED INDEX [idx_order_time] ON [dbo].[jpay_order]
(
	[order_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [idx_out_trade_no]    Script Date: 2021/2/13 13:23:36 ******/
CREATE NONCLUSTERED INDEX [idx_out_trade_no] ON [dbo].[jpay_order]
(
	[out_trade_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [idx_pay_method]    Script Date: 2021/2/13 13:23:36 ******/
CREATE NONCLUSTERED INDEX [idx_pay_method] ON [dbo].[jpay_order]
(
	[pay_method] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [idx_pay_status]    Script Date: 2021/2/13 13:23:36 ******/
CREATE NONCLUSTERED INDEX [idx_pay_status] ON [dbo].[jpay_order]
(
	[pay_status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [idx_account_alipay]    Script Date: 2021/2/13 13:23:36 ******/
CREATE NONCLUSTERED INDEX [idx_account_alipay] ON [dbo].[jpay_payment_account]
(
	[account_alipay] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [idx_account_wxpay]    Script Date: 2021/2/13 13:23:36 ******/
CREATE NONCLUSTERED INDEX [idx_account_wxpay] ON [dbo].[jpay_payment_account]
(
	[account_wxpay] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'后台通知回调页面' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'jpay_app', @level2type=N'COLUMN',@level2name=N'notify_url'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'前台跳转回调页面' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'jpay_app', @level2type=N'COLUMN',@level2name=N'return_url'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'前台跳转回调页面' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'jpay_app', @level2type=N'COLUMN',@level2name=N'return_url_mobile'
GO