thmz/src/main/resources/otherSource/update_table.sql

/*
 Navicat Premium Data Transfer

 Source Server         : 172.16.32.179
 Source Server Type    : SQL Server
 Source Server Version : 10506000
 Source Host           : 172.16.32.179:1433
 Source Catalog        : thxyhisdb
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 10506000
 File Encoding         : 65001

 Date: 24/04/2025 15:54:05
*/


-- ----------------------------
-- Table structure for mz_dzfp_upload
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[mz_dzfp_upload]') AND type IN ('U'))
	DROP TABLE [dbo].[mz_dzfp_upload]
GO

CREATE TABLE [dbo].[mz_dzfp_upload] (
  [patient_id] varchar(100) COLLATE Chinese_PRC_BIN  NULL,
  [times] int  NULL,
  [receipt_no] int  NULL,
  [type_flag] int  NULL,
  [request_id] varchar(100) COLLATE Chinese_PRC_BIN  NULL,
  [res_code] int  NULL,
  [qrcode] text COLLATE Chinese_PRC_BIN  NULL,
  [res_url] varchar(1000) COLLATE Chinese_PRC_BIN  NULL,
  [fp_no] varchar(250) COLLATE Chinese_PRC_BIN  NULL,
  [res_message] varchar(1000) COLLATE Chinese_PRC_BIN  NULL,
  [del_flag] int DEFAULT ((0)) NULL,
  [blue_issue_date] varchar(250) COLLATE Chinese_PRC_BIN  NULL,
  [op_id] varchar(50) COLLATE Chinese_PRC_BIN  NULL,
  [pdf_file_str] text COLLATE Chinese_PRC_BIN  NULL,
  [op_code] varchar(100) COLLATE Chinese_PRC_BIN  NULL
)
GO

ALTER TABLE [dbo].[mz_dzfp_upload] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'发票类型 1 门诊蓝字发票  2 门诊红字发票 3住院蓝字发票 4住院红字发票',
'SCHEMA', N'dbo',
'TABLE', N'mz_dzfp_upload',
'COLUMN', N'type_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'上传发票唯一标识',
'SCHEMA', N'dbo',
'TABLE', N'mz_dzfp_upload',
'COLUMN', N'request_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'-1 失败 0 成功',
'SCHEMA', N'dbo',
'TABLE', N'mz_dzfp_upload',
'COLUMN', N'res_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'二维码 base64',
'SCHEMA', N'dbo',
'TABLE', N'mz_dzfp_upload',
'COLUMN', N'qrcode'
GO

EXEC sp_addextendedproperty
'MS_Description', N'内网下载链接',
'SCHEMA', N'dbo',
'TABLE', N'mz_dzfp_upload',
'COLUMN', N'res_url'
GO

EXEC sp_addextendedproperty
'MS_Description', N'发票号',
'SCHEMA', N'dbo',
'TABLE', N'mz_dzfp_upload',
'COLUMN', N'fp_no'
GO

EXEC sp_addextendedproperty
'MS_Description', N'0 有效 1作废',
'SCHEMA', N'dbo',
'TABLE', N'mz_dzfp_upload',
'COLUMN', N'del_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开具日期',
'SCHEMA', N'dbo',
'TABLE', N'mz_dzfp_upload',
'COLUMN', N'blue_issue_date'
GO

EXEC sp_addextendedproperty
'MS_Description', N'操作员',
'SCHEMA', N'dbo',
'TABLE', N'mz_dzfp_upload',
'COLUMN', N'op_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'pdf文档',
'SCHEMA', N'dbo',
'TABLE', N'mz_dzfp_upload',
'COLUMN', N'pdf_file_str'
GO

EXEC sp_addextendedproperty
'MS_Description', N'电子发票上传记录',
'SCHEMA', N'dbo',
'TABLE', N'mz_dzfp_upload'
GO


-- ----------------------------
-- Indexes structure for table mz_dzfp_upload
-- ----------------------------
CREATE NONCLUSTERED INDEX [mz_dzfp_upload_index_1]
ON [dbo].[mz_dzfp_upload] (
  [patient_id] ASC,
  [times] ASC,
  [receipt_no] ASC,
  [request_id] ASC
)
GO


-- ----------------------------
-- Uniques structure for table mz_dzfp_upload
-- ----------------------------
ALTER TABLE [dbo].[mz_dzfp_upload] ADD CONSTRAINT [mz_dzfp_upload_wy1] UNIQUE NONCLUSTERED ([patient_id] ASC, [times] ASC, [receipt_no] ASC, [type_flag] ASC, [del_flag] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

/*
 Navicat Premium Data Transfer

 Source Server         : 172.16.32.179
 Source Server Type    : SQL Server
 Source Server Version : 10506000
 Source Host           : 172.16.32.179:1433
 Source Catalog        : thxyhisdb
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 10506000
 File Encoding         : 65001

 Date: 28/04/2025 10:06:24
*/


-- ----------------------------
-- Table structure for t_wxmall_package
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_wxmall_package]') AND type IN ('U'))
	DROP TABLE [dbo].[t_wxmall_package]
GO

CREATE TABLE [dbo].[t_wxmall_package] (
  [id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [name] nvarchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [origin_price] money  NOT NULL,
  [discount_price] money  NOT NULL,
  [thumb_path] varchar(128) COLLATE Chinese_PRC_BIN DEFAULT ('https://staticweb.hnthyy.cn/images/wxmall-package/blank.png') NOT NULL,
  [create_time] datetime DEFAULT (getdate()) NOT NULL,
  [create_staff_code] varchar(6) COLLATE Chinese_PRC_BIN  NOT NULL,
  [create_staff_name] nvarchar(16) COLLATE Chinese_PRC_BIN  NOT NULL,
  [state] varchar(16) COLLATE Chinese_PRC_BIN DEFAULT ('ACTIVATED') NOT NULL,
  [description] nvarchar(256) COLLATE Chinese_PRC_BIN  NULL
)
GO

ALTER TABLE [dbo].[t_wxmall_package] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Table structure for t_wxmall_package_item
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_wxmall_package_item]') AND type IN ('U'))
	DROP TABLE [dbo].[t_wxmall_package_item]
GO

CREATE TABLE [dbo].[t_wxmall_package_item] (
  [package_id] nvarchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [his_code] varchar(12) COLLATE Chinese_PRC_BIN  NOT NULL,
  [his_name] nvarchar(128) COLLATE Chinese_PRC_BIN  NOT NULL,
  [type] varchar(2) COLLATE Chinese_PRC_BIN  NULL,
  [origin_price] money  NOT NULL,
  [discount_price] money  NOT NULL,
  [quantity] smallint DEFAULT ((1)) NOT NULL,
  [cost_amt] money  NULL,
  [gender_restriction] tinyint  NULL,
  [min_age_restriction] smallint  NULL,
  [max_age_restriction] smallint  NULL
)
GO

ALTER TABLE [dbo].[t_wxmall_package_item] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'所属套餐ID',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_item',
'COLUMN', N'package_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'HIS收费编码',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_item',
'COLUMN', N'his_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'HIS收费名称',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_item',
'COLUMN', N'his_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'项目类别',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_item',
'COLUMN', N'type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'原价',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_item',
'COLUMN', N'origin_price'
GO

EXEC sp_addextendedproperty
'MS_Description', N'优惠价',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_item',
'COLUMN', N'discount_price'
GO

EXEC sp_addextendedproperty
'MS_Description', N'数量',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_item',
'COLUMN', N'quantity'
GO

EXEC sp_addextendedproperty
'MS_Description', N'总价',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_item',
'COLUMN', N'cost_amt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'服务号套餐明细',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_item'
GO


-- ----------------------------
-- Table structure for t_wxmall_package_purchased
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_wxmall_package_purchased]') AND type IN ('U'))
	DROP TABLE [dbo].[t_wxmall_package_purchased]
GO

CREATE TABLE [dbo].[t_wxmall_package_purchased] (
  [id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [pat_no] varchar(12) COLLATE Chinese_PRC_BIN  NOT NULL,
  [wx_trade_no] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [package_id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [package_name] nvarchar(64) COLLATE Chinese_PRC_BIN  NOT NULL,
  [purchase_price] money  NOT NULL,
  [purchase_time] datetime DEFAULT (getdate()) NOT NULL,
  [state] varchar(12) COLLATE Chinese_PRC_BIN DEFAULT ('NOT_USED') NOT NULL,
  [coupon_amt] money DEFAULT ((0)) NOT NULL,
  [cashpay_amt] money  NOT NULL
)
GO

ALTER TABLE [dbo].[t_wxmall_package_purchased] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'主键',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_purchased',
'COLUMN', N'id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'门诊ID',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_purchased',
'COLUMN', N'pat_no'
GO

EXEC sp_addextendedproperty
'MS_Description', N'微信支付订单号',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_purchased',
'COLUMN', N'wx_trade_no'
GO

EXEC sp_addextendedproperty
'MS_Description', N'套餐ID',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_purchased',
'COLUMN', N'package_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'套餐名称',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_purchased',
'COLUMN', N'package_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'购买价格',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_purchased',
'COLUMN', N'purchase_price'
GO

EXEC sp_addextendedproperty
'MS_Description', N'购买时间',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_purchased',
'COLUMN', N'purchase_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'套餐使用状态：USABLE:可使用；RAN_OUT:已用完',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_purchased',
'COLUMN', N'state'
GO

EXEC sp_addextendedproperty
'MS_Description', N'患者已购服务号套餐目录',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_purchased'
GO


-- ----------------------------
-- Table structure for t_wxmall_package_purchased_item
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_wxmall_package_purchased_item]') AND type IN ('U'))
	DROP TABLE [dbo].[t_wxmall_package_purchased_item]
GO

CREATE TABLE [dbo].[t_wxmall_package_purchased_item] (
  [id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [wx_trade_no] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [pat_no] varchar(12) COLLATE Chinese_PRC_BIN  NOT NULL,
  [package_id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [his_code] varchar(12) COLLATE Chinese_PRC_BIN  NOT NULL,
  [his_name] nvarchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [quantity] smallint DEFAULT ((1)) NOT NULL,
  [usable_quantity] smallint DEFAULT ((1)) NOT NULL,
  [last_use_time] datetime  NULL,
  [price] money  NULL,
  [cost_amt] money  NULL,
  [gender_restriction] tinyint  NULL,
  [min_age_restriction] smallint  NULL,
  [max_age_restriction] smallint  NULL
)
GO

ALTER TABLE [dbo].[t_wxmall_package_purchased_item] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Table structure for t_wxmall_package_use_history
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_wxmall_package_use_history]') AND type IN ('U'))
	DROP TABLE [dbo].[t_wxmall_package_use_history]
GO

CREATE TABLE [dbo].[t_wxmall_package_use_history] (
  [id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [pat_no] varchar(12) COLLATE Chinese_PRC_BIN  NOT NULL,
  [times] smallint  NOT NULL,
  [wx_trade_no] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [package_id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [package_item_id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [use_quantity] smallint DEFAULT ((1)) NOT NULL,
  [use_time] datetime DEFAULT (getdate()) NOT NULL,
  [doctor_code] varchar(6) COLLATE Chinese_PRC_BIN  NOT NULL
)
GO

ALTER TABLE [dbo].[t_wxmall_package_use_history] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Table structure for t_wxmall_package_used
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_wxmall_package_used]') AND type IN ('U'))
	DROP TABLE [dbo].[t_wxmall_package_used]
GO

CREATE TABLE [dbo].[t_wxmall_package_used] (
  [id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [pat_no] varchar(12) COLLATE Chinese_PRC_BIN  NOT NULL,
  [times] smallint  NOT NULL,
  [wx_trade_no] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [package_id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [package_item_id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [use_quantity] smallint DEFAULT ((1)) NOT NULL,
  [use_time] datetime DEFAULT (getdate()) NOT NULL,
  [doctor_code] varchar(6) COLLATE Chinese_PRC_BIN  NOT NULL
)
GO

ALTER TABLE [dbo].[t_wxmall_package_used] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'主键',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_used',
'COLUMN', N'id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'患者门诊ID',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_used',
'COLUMN', N'pat_no'
GO

EXEC sp_addextendedproperty
'MS_Description', N'就诊次数',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_used',
'COLUMN', N'times'
GO

EXEC sp_addextendedproperty
'MS_Description', N'微信支付订单号',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_used',
'COLUMN', N'wx_trade_no'
GO

EXEC sp_addextendedproperty
'MS_Description', N'套餐ID',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_used',
'COLUMN', N'package_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'套餐项目id',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_used',
'COLUMN', N'package_item_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'使用数量',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_used',
'COLUMN', N'use_quantity'
GO

EXEC sp_addextendedproperty
'MS_Description', N'使用时间',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_used',
'COLUMN', N'use_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开单医生',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_used',
'COLUMN', N'doctor_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'套餐使用记录',
'SCHEMA', N'dbo',
'TABLE', N't_wxmall_package_used'
GO


-- ----------------------------
-- Primary Key structure for table t_wxmall_package
-- ----------------------------
ALTER TABLE [dbo].[t_wxmall_package] ADD CONSTRAINT [t_wxmall_package_pk] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table t_wxmall_package_item
-- ----------------------------
ALTER TABLE [dbo].[t_wxmall_package_item] ADD CONSTRAINT [t_wxmall_package_item_pk] PRIMARY KEY CLUSTERED ([package_id], [his_code])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO


-- ----------------------------
-- Indexes structure for table t_wxmall_package_purchased
-- ----------------------------
CREATE NONCLUSTERED INDEX [t_wxmall_package_purchased_pat_no_index]
ON [dbo].[t_wxmall_package_purchased] (
  [pat_no] ASC
)
GO

CREATE NONCLUSTERED INDEX [t_wxmall_package_purchased_wx_trade_no_index]
ON [dbo].[t_wxmall_package_purchased] (
  [wx_trade_no] ASC
)
GO

CREATE NONCLUSTERED INDEX [t_wxmall_package_purchased_package_id_index]
ON [dbo].[t_wxmall_package_purchased] (
  [package_id] ASC
)
GO


-- ----------------------------
-- Primary Key structure for table t_wxmall_package_purchased
-- ----------------------------
ALTER TABLE [dbo].[t_wxmall_package_purchased] ADD CONSTRAINT [t_wxmall_package_purchased_pk] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO


-- ----------------------------
-- Indexes structure for table t_wxmall_package_purchased_item
-- ----------------------------
CREATE NONCLUSTERED INDEX [t_wxmall_package_purchased_item_pat_no_index]
ON [dbo].[t_wxmall_package_purchased_item] (
  [pat_no] ASC
)
GO

CREATE NONCLUSTERED INDEX [t_wxmall_package_purchased_item_wx_trade_no_index]
ON [dbo].[t_wxmall_package_purchased_item] (
  [wx_trade_no] ASC
)
GO

CREATE NONCLUSTERED INDEX [t_wxmall_package_purchased_item_package_id_index]
ON [dbo].[t_wxmall_package_purchased_item] (
  [package_id] ASC
)
GO


-- ----------------------------
-- Primary Key structure for table t_wxmall_package_purchased_item
-- ----------------------------
ALTER TABLE [dbo].[t_wxmall_package_purchased_item] ADD CONSTRAINT [t_wxmall_package_purchased_item_pk] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO


-- ----------------------------
-- Indexes structure for table t_wxmall_package_use_history
-- ----------------------------
CREATE NONCLUSTERED INDEX [t_wxmall_package_use_history_pat_no_times_index]
ON [dbo].[t_wxmall_package_use_history] (
  [pat_no] ASC,
  [times] ASC
)
GO

CREATE NONCLUSTERED INDEX [t_wxmall_package_use_history_wx_trade_no_index]
ON [dbo].[t_wxmall_package_use_history] (
  [wx_trade_no] ASC
)
GO

CREATE NONCLUSTERED INDEX [t_wxmall_package_use_history_package_id_index]
ON [dbo].[t_wxmall_package_use_history] (
  [package_id] ASC
)
GO


-- ----------------------------
-- Primary Key structure for table t_wxmall_package_use_history
-- ----------------------------
ALTER TABLE [dbo].[t_wxmall_package_use_history] ADD CONSTRAINT [t_wxmall_package_use_history_pk] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO


-- ----------------------------
-- Indexes structure for table t_wxmall_package_used
-- ----------------------------
CREATE NONCLUSTERED INDEX [t_wxmall_package_used_pat_no_times_index]
ON [dbo].[t_wxmall_package_used] (
  [pat_no] ASC,
  [times] ASC
)
GO

CREATE NONCLUSTERED INDEX [t_wxmall_package_used_wx_trade_no_index]
ON [dbo].[t_wxmall_package_used] (
  [wx_trade_no] ASC
)
GO

CREATE NONCLUSTERED INDEX [t_wxmall_package_used_package_id_index]
ON [dbo].[t_wxmall_package_used] (
  [package_id] ASC
)
GO


-- ----------------------------
-- Primary Key structure for table t_wxmall_package_used
-- ----------------------------
ALTER TABLE [dbo].[t_wxmall_package_used] ADD CONSTRAINT [t_wxmall_package_used_pk] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO


/*
 Navicat Premium Data Transfer

 Source Server         : 172.16.32.179
 Source Server Type    : SQL Server
 Source Server Version : 10506000
 Source Host           : 172.16.32.179:1433
 Source Catalog        : thxyhisdb
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 10506000
 File Encoding         : 65001

 Date: 28/04/2025 10:26:15
*/


-- ----------------------------
-- Table structure for zd_rx_medicine
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[zd_rx_medicine]') AND type IN ('U'))
	DROP TABLE [dbo].[zd_rx_medicine]
GO

CREATE TABLE [dbo].[zd_rx_medicine] (
  [id] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [med_list_codg] varchar(64) COLLATE Chinese_PRC_BIN  NOT NULL,
  [nat_drug_no] varchar(64) COLLATE Chinese_PRC_BIN  NULL,
  [genname] nvarchar(128) COLLATE Chinese_PRC_BIN  NULL,
  [prodname] nvarchar(128) COLLATE Chinese_PRC_BIN  NULL,
  [reg_name] nvarchar(128) COLLATE Chinese_PRC_BIN  NOT NULL,
  [list_type] varchar(6) COLLATE Chinese_PRC_BIN  NULL,
  [list_type_name] nvarchar(12) COLLATE Chinese_PRC_BIN  NULL,
  [spec_name] varchar(256) COLLATE Chinese_PRC_BIN  NULL,
  [prdr_name] nvarchar(128) COLLATE Chinese_PRC_BIN  NULL,
  [aprvno] nvarchar(128) COLLATE Chinese_PRC_BIN  NULL,
  [dosform_name] nvarchar(64) COLLATE Chinese_PRC_BIN  NULL,
  [min_pacunt] nvarchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [poolarea_no] varchar(12) COLLATE Chinese_PRC_BIN  NULL,
  [poolarea_name] nvarchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [dualchnl_flag] tinyint  NULL,
  [min_prepunt] nvarchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [begntime] datetime  NULL,
  [endtime] datetime  NULL,
  [py_code] varchar(128) COLLATE Chinese_PRC_BIN  NOT NULL,
  [page_no] int  NOT NULL
)
GO

ALTER TABLE [dbo].[zd_rx_medicine] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Primary Key structure for table zd_rx_medicine
-- ----------------------------
ALTER TABLE [dbo].[zd_rx_medicine] ADD CONSTRAINT [zd_rx_medicine_pk] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO


/*
 Navicat Premium Data Transfer

 Source Server         : 172.16.32.179
 Source Server Type    : SQL Server
 Source Server Version : 10506000
 Source Host           : 172.16.32.179:1433
 Source Catalog        : thxyhisdb
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 10506000
 File Encoding         : 65001

 Date: 28/04/2025 10:26:55
*/


-- ----------------------------
-- Table structure for t_rx_druginfo
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_rx_druginfo]') AND type IN ('U'))
	DROP TABLE [dbo].[t_rx_druginfo]
GO

CREATE TABLE [dbo].[t_rx_druginfo] (
  [hosp_rxno] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [med_list_codg] varchar(128) COLLATE Chinese_PRC_BIN  NOT NULL,
  [fixmedins_hilist_id] varchar(128) COLLATE Chinese_PRC_BIN  NULL,
  [hosp_prep_flag] int DEFAULT ((0)) NOT NULL,
  [rx_item_type_code] int  NOT NULL,
  [rx_item_type_name] nvarchar(12) COLLATE Chinese_PRC_BIN  NULL,
  [tcmdrug_type_code] int  NULL,
  [tcmdrug_type_name] nvarchar(12) COLLATE Chinese_PRC_BIN  NULL,
  [tcmherb_foote] nvarchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [medn_type_code] varchar(12) COLLATE Chinese_PRC_BIN  NULL,
  [medn_type_name] nvarchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [main_medc_flag] int  NULL,
  [urgt_flag] int  NULL,
  [bas_medn_flag] int  NULL,
  [imp_drug_flag] int  NULL,
  [otc_flag] int  NULL,
  [drug_genname] nvarchar(128) COLLATE Chinese_PRC_BIN  NOT NULL,
  [drug_dosform] nvarchar(30) COLLATE Chinese_PRC_BIN  NOT NULL,
  [drug_spec] nvarchar(40) COLLATE Chinese_PRC_BIN  NOT NULL,
  [drug_prodname] nvarchar(128) COLLATE Chinese_PRC_BIN  NULL,
  [prdr_name] nvarchar(128) COLLATE Chinese_PRC_BIN  NULL,
  [medc_way_codg] int  NULL,
  [medc_way_dscr] nvarchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [medc_begntime] datetime  NOT NULL,
  [medc_endtime] datetime  NOT NULL,
  [medc_days] int  NOT NULL,
  [sin_dosunt] varchar(12) COLLATE Chinese_PRC_BIN  NULL,
  [sin_doscnt] decimal(16,4)  NULL,
  [used_frqu_codg] int  NULL,
  [drug_dosunt] nvarchar(12) COLLATE Chinese_PRC_BIN  NOT NULL,
  [drug_cnt] decimal(16,4)  NOT NULL,
  [drug_pric] decimal(16,4)  NULL,
  [drug_sumamt] decimal(16,2)  NULL,
  [hosp_appr_flag] int  NOT NULL,
  [self_pay_rea] int  NULL,
  [real_dscr] nvarchar(512) COLLATE Chinese_PRC_BIN  NULL,
  [used_frqu_name] varchar(250) COLLATE Chinese_PRC_BIN  NULL
)
GO

ALTER TABLE [dbo].[t_rx_druginfo] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'疗机构处方编号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'hosp_rxno'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医保药品编码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'med_list_codg'
GO

EXEC sp_addextendedproperty
'MS_Description', N'院内药品编码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'fixmedins_hilist_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医疗机构制剂标志（0-否、1-是，默 认否）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'hosp_prep_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'处方项目分类代码(from zd_rx_all where label=''rx_item_type'')',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'rx_item_type_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'处方项目分类名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'rx_item_type_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中药类别代码（中药处方必填，中药饮片固定传3）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'tcmdrug_type_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中药类别名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'tcmdrug_type_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'草药脚注',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'tcmherb_foote'
GO

EXEC sp_addextendedproperty
'MS_Description', N'药物类型代码(label = rx_medn_type)',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'medn_type_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'药物类型名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'medn_type_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'主要用药标志（0-否、1-是）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'main_medc_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'加急标志（0-否、1-是）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'urgt_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'基本药物标志（0-否、1-是）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'bas_medn_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'是否进口药品（0-否、1-是）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'imp_drug_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'是否OTC药品（0-否、1-是）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'otc_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'药品通用名',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'drug_genname'
GO

EXEC sp_addextendedproperty
'MS_Description', N'药品剂型',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'drug_dosform'
GO

EXEC sp_addextendedproperty
'MS_Description', N'药品规格',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'drug_spec'
GO

EXEC sp_addextendedproperty
'MS_Description', N'药品商品名（非必填，可按通用名开方）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'drug_prodname'
GO

EXEC sp_addextendedproperty
'MS_Description', N'生产厂家',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'prdr_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'用药途径代码（rx_item_type_code为西药、中成药时必填）(from zd_rx_all where label=''rx_used_way'')',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'medc_way_codg'
GO

EXEC sp_addextendedproperty
'MS_Description', N'用药途径名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'medc_way_dscr'
GO

EXEC sp_addextendedproperty
'MS_Description', N'用药开始时间',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'medc_begntime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'用药结束时间',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'medc_endtime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'用药天数',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'medc_days'
GO

EXEC sp_addextendedproperty
'MS_Description', N'单次剂量单位（rx_item_type_code为西药、中成药时必填）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'sin_dosunt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'单次用量（rx_item_type_code为西药、中成药时必填）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'sin_doscnt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'使用频次编码（rx_item_type_code为西药、中成药时必填）(from zd_rx_all where label=''rx_used_frqu'')',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'used_frqu_codg'
GO

EXEC sp_addextendedproperty
'MS_Description', N'药品总用药量单位（即发药计价单位，如”片“，”盒“）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'drug_dosunt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'药品发药总量（根据单次剂量及单位、频次等和drugDosunt按院内“药品单位转换系数“换算）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'drug_cnt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'药品单价',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'drug_pric'
GO

EXEC sp_addextendedproperty
'MS_Description', N'药品总金额',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'drug_sumamt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医院审批标志(1-报销，2-自费)',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'hosp_appr_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'自费原因类型（hospApprFlag=2时必填）（from zd_rx_all where label=''rx_self_pay_rea''）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'self_pay_rea'
GO

EXEC sp_addextendedproperty
'MS_Description', N'自费原因描述（自费原因类型为6时必填）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'real_dscr'
GO

EXEC sp_addextendedproperty
'MS_Description', N'使用频次名称  (rx_item_type_code为西药、中成药时必填）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo',
'COLUMN', N'used_frqu_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'电子处方药品',
'SCHEMA', N'dbo',
'TABLE', N't_rx_druginfo'
GO


-- ----------------------------
-- Primary Key structure for table t_rx_druginfo
-- ----------------------------
ALTER TABLE [dbo].[t_rx_druginfo] ADD CONSTRAINT [t_rx_druginfo_pk] PRIMARY KEY CLUSTERED ([hosp_rxno], [med_list_codg])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

/*
 Navicat Premium Data Transfer

 Source Server         : 172.16.32.179
 Source Server Type    : SQL Server
 Source Server Version : 10506000
 Source Host           : 172.16.32.179:1433
 Source Catalog        : thxyhisdb
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 10506000
 File Encoding         : 65001

 Date: 28/04/2025 10:27:10
*/


-- ----------------------------
-- Table structure for t_rx_mdtrtinfo
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_rx_mdtrtinfo]') AND type IN ('U'))
	DROP TABLE [dbo].[t_rx_mdtrtinfo]
GO

CREATE TABLE [dbo].[t_rx_mdtrtinfo] (
  [hosp_rxno] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [ipt_otp_no] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [otp_ipt_flag] int DEFAULT ((1)) NOT NULL,
  [patn_name] nvarchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [psn_cert_type] varchar(6) COLLATE Chinese_PRC_BIN  NULL,
  [certno] varchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [patn_age] decimal(4,1)  NULL,
  [patn_hgt] decimal(6,2)  NULL,
  [patn_wt] decimal(6,2)  NULL,
  [gend] varchar(6) COLLATE Chinese_PRC_BIN  NULL,
  [birctrl_type] varchar(6) COLLATE Chinese_PRC_BIN  NULL,
  [birctrl_matn_date] datetime  NULL,
  [matn_type] varchar(6) COLLATE Chinese_PRC_BIN  NULL,
  [geso_val] int  NULL,
  [nwb_flag] varchar(6) COLLATE Chinese_PRC_BIN  NULL,
  [nwb_age] varchar(20) COLLATE Chinese_PRC_BIN  NULL,
  [suck_prd_flag] varchar(6) COLLATE Chinese_PRC_BIN  NULL,
  [algs_his] nvarchar(1000) COLLATE Chinese_PRC_BIN  NULL,
  [prsc_dept_code] varchar(30) COLLATE Chinese_PRC_BIN  NOT NULL,
  [prsc_dept_name] nvarchar(50) COLLATE Chinese_PRC_BIN  NOT NULL,
  [dr_code] varchar(20) COLLATE Chinese_PRC_BIN  NOT NULL,
  [prsc_dr_name] nvarchar(50) COLLATE Chinese_PRC_BIN  NULL,
  [prsc_dr_cert_type] varchar(6) COLLATE Chinese_PRC_BIN  NULL,
  [prsc_dr_certno] varchar(50) COLLATE Chinese_PRC_BIN  NULL,
  [dr_profttl_codg] varchar(20) COLLATE Chinese_PRC_BIN  NOT NULL,
  [dr_profttl_name] nvarchar(20) COLLATE Chinese_PRC_BIN  NOT NULL,
  [dr_dept_code] varchar(30) COLLATE Chinese_PRC_BIN  NOT NULL,
  [dr_dept_name] nvarchar(30) COLLATE Chinese_PRC_BIN  NOT NULL,
  [caty] varchar(10) COLLATE Chinese_PRC_BIN  NOT NULL,
  [mdtrt_time] datetime  NOT NULL,
  [dise_codg] varchar(30) COLLATE Chinese_PRC_BIN  NULL,
  [dise_name] nvarchar(60) COLLATE Chinese_PRC_BIN  NULL,
  [sp_dise_flag] varchar(6) COLLATE Chinese_PRC_BIN DEFAULT ('0') NULL,
  [maindiag_code] varchar(30) COLLATE Chinese_PRC_BIN  NOT NULL,
  [maindiag_name] nvarchar(60) COLLATE Chinese_PRC_BIN  NOT NULL,
  [dise_cond_dscr] nvarchar(2000) COLLATE Chinese_PRC_BIN  NULL,
  [hi_feesetl_type] varchar(6) COLLATE Chinese_PRC_BIN  NULL,
  [hi_feesetl_name] nvarchar(20) COLLATE Chinese_PRC_BIN  NULL,
  [rgst_fee] decimal(16,2)  NULL,
  [medfee_sumamt] decimal(16,2)  NULL,
  [fstdiag_flag] varchar(3) COLLATE Chinese_PRC_BIN  NULL
)
GO

ALTER TABLE [dbo].[t_rx_mdtrtinfo] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'医疗机构处方编号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'hosp_rxno'
GO

EXEC sp_addextendedproperty
'MS_Description', N'门诊/住院号 ',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'ipt_otp_no'
GO

EXEC sp_addextendedproperty
'MS_Description', N'门诊住院标识（1-门诊、2-住院，默认为1）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'otp_ipt_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'患者姓名',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'patn_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'人员证件类型（from zd_rx_all where label=''rx_psn_cert_type''）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'psn_cert_type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'证件号码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'certno'
GO

EXEC sp_addextendedproperty
'MS_Description', N'年龄',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'patn_age'
GO

EXEC sp_addextendedproperty
'MS_Description', N'患者身高',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'patn_hgt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'患者体重',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'patn_wt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'性别',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'gend'
GO

EXEC sp_addextendedproperty
'MS_Description', N'计划生育手术类别',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'birctrl_type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'计划生育手术或生育日期',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'birctrl_matn_date'
GO

EXEC sp_addextendedproperty
'MS_Description', N'生育类别',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'matn_type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'妊娠(孕周',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'geso_val'
GO

EXEC sp_addextendedproperty
'MS_Description', N'新生儿标志（0-否、1-是）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'nwb_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'新生儿日、月龄',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'nwb_age'
GO

EXEC sp_addextendedproperty
'MS_Description', N'哺乳期标志（0-否、1-是）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'suck_prd_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'过敏史',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'algs_his'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开方科室编码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'prsc_dept_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开方科室名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'prsc_dept_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开方医保医师代 码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'dr_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开方医师姓名',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'prsc_dr_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开方医师证件类型（from zd_rx_all where label=''rx_psn_cert_type''）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'prsc_dr_cert_type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开方医师证件号码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'prsc_dr_certno'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医生职称编码（from zd_rx_all where label=''rx_dr_profttl''）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'dr_profttl_codg'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医生职称名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'dr_profttl_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医生科室编码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'dr_dept_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医生科室名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'dr_dept_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'科别',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'caty'
GO

EXEC sp_addextendedproperty
'MS_Description', N'就诊时间',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'mdtrt_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'病种编码（medType为门诊慢特病时必传）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'dise_codg'
GO

EXEC sp_addextendedproperty
'MS_Description', N'病种名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'dise_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'特殊病种标志（0-否、1-是）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'sp_dise_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'主诊断代码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'maindiag_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'主诊断名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'maindiag_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'疾病病情描述',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'dise_cond_dscr'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医保费用结算类型（from zd_rx_all where label=''rx_setl_type''）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'hi_feesetl_type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医保费用类别名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'hi_feesetl_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'挂号费',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'rgst_fee'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医疗费总额',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'medfee_sumamt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'是否初诊（0-否、1-是）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo',
'COLUMN', N'fstdiag_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'电子处方就诊信息',
'SCHEMA', N'dbo',
'TABLE', N't_rx_mdtrtinfo'
GO


-- ----------------------------
-- Primary Key structure for table t_rx_mdtrtinfo
-- ----------------------------
ALTER TABLE [dbo].[t_rx_mdtrtinfo] ADD CONSTRAINT [t_rx_mdtrtinfo_pk] PRIMARY KEY CLUSTERED ([hosp_rxno])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

/*
 Navicat Premium Data Transfer

 Source Server         : 172.16.32.179
 Source Server Type    : SQL Server
 Source Server Version : 10506000
 Source Host           : 172.16.32.179:1433
 Source Catalog        : thxyhisdb
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 10506000
 File Encoding         : 65001

 Date: 28/04/2025 10:27:22
*/


-- ----------------------------
-- Table structure for t_rx_receiptinfo
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_rx_receiptinfo]') AND type IN ('U'))
	DROP TABLE [dbo].[t_rx_receiptinfo]
GO

CREATE TABLE [dbo].[t_rx_receiptinfo] (
  [hosp_rxno] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [pat_no] varchar(12) COLLATE Chinese_PRC_BIN  NOT NULL,
  [times] int  NOT NULL,
  [init_rxno] varchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [rx_type_code] int  NOT NULL,
  [prsc_time] datetime  NULL,
  [rx_drug_cnt] int  NULL,
  [rx_used_way_codg] int  NULL,
  [rx_used_way_name] nvarchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [rx_frqu_codg] int  NULL,
  [rx_frqu_name] nvarchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [rx_dosunt] varchar(16) COLLATE Chinese_PRC_BIN  NULL,
  [rx_doscnt] decimal(16,2)  NULL,
  [rx_drord_dscr] nvarchar(128) COLLATE Chinese_PRC_BIN  NULL,
  [vali_days] int  NULL,
  [vali_end_time] datetime  NULL,
  [rept_flag] int  NULL,
  [max_rept_cnt] int  NULL,
  [reptd_cnt] int  NULL,
  [min_inrv_days] int  NULL,
  [rx_cotn_flag] tinyint DEFAULT ((0)) NOT NULL,
  [long_rx_flag] tinyint DEFAULT ((0)) NOT NULL,
  [zy_no] varchar(250) COLLATE Chinese_PRC_BIN  NULL,
  [zy_times] int  NULL
)
GO

ALTER TABLE [dbo].[t_rx_receiptinfo] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'处方编号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'hosp_rxno'
GO

EXEC sp_addextendedproperty
'MS_Description', N'门诊号/住院号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'pat_no'
GO

EXEC sp_addextendedproperty
'MS_Description', N'就诊次数',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'times'
GO

EXEC sp_addextendedproperty
'MS_Description', N'续方的原处方编号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'init_rxno'
GO

EXEC sp_addextendedproperty
'MS_Description', N'处方类别代码(from zd_rx_all where label=''rx_type'')',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'rx_type_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开方时间',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'prsc_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'非中药时为处方药品类目数量，中药时为药品总剂数',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'rx_drug_cnt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中药汤剂处方整剂用法编号(from zd_rx_all where label=''rx_used_way'')',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'rx_used_way_codg'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中药汤剂处方整剂用法名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'rx_used_way_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中药汤剂处方整剂频次编号(from zd_rx_all where label=''rx_used_frqu'')',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'rx_frqu_codg'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中药汤剂处方整剂频次名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'rx_frqu_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中药汤剂处方整剂剂量单位',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'rx_dosunt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中药汤剂处方整剂单次剂量数',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'rx_doscnt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中药汤剂处方整剂医嘱说明',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'rx_drord_dscr'
GO

EXEC sp_addextendedproperty
'MS_Description', N'处方有效天数',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'vali_days'
GO

EXEC sp_addextendedproperty
'MS_Description', N'有效截止时间（开方时间+处方有效天数）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'vali_end_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'复用（多次）使用标志(0-否、1-是)',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'rept_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'最大使用次数',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'max_rept_cnt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'已使用次数',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'reptd_cnt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'使用最小间隔（天数）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'min_inrv_days'
GO

EXEC sp_addextendedproperty
'MS_Description', N'续方标志(0-否、1-是，默认为否)',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'rx_cotn_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'长期处方标志(0-否、1-是，默认为否)',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'long_rx_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'住院号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'zy_no'
GO

EXEC sp_addextendedproperty
'MS_Description', N'住院次数',
'SCHEMA', N'dbo',
'TABLE', N't_rx_receiptinfo',
'COLUMN', N'zy_times'
GO


-- ----------------------------
-- Indexes structure for table t_rx_receiptinfo
-- ----------------------------
CREATE NONCLUSTERED INDEX [t_rx_receiptinfo_pat_no_times_index]
ON [dbo].[t_rx_receiptinfo] (
  [pat_no] ASC,
  [times] ASC
)
GO

CREATE NONCLUSTERED INDEX [t_rx_receiptinfo_zy_no_zy_times_index]
ON [dbo].[t_rx_receiptinfo] (
  [zy_no] ASC,
  [zy_times] ASC
)
GO


-- ----------------------------
-- Primary Key structure for table t_rx_receiptinfo
-- ----------------------------
ALTER TABLE [dbo].[t_rx_receiptinfo] ADD CONSTRAINT [t_rx_receiptinfo_pk] PRIMARY KEY CLUSTERED ([hosp_rxno])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

/*
 Navicat Premium Data Transfer

 Source Server         : 172.16.32.179
 Source Server Type    : SQL Server
 Source Server Version : 10506000
 Source Host           : 172.16.32.179:1433
 Source Catalog        : thxyhisdb
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 10506000
 File Encoding         : 65001

 Date: 28/04/2025 10:27:37
*/


-- ----------------------------
-- Table structure for zd_rx_all
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[zd_rx_all]') AND type IN ('U'))
	DROP TABLE [dbo].[zd_rx_all]
GO

CREATE TABLE [dbo].[zd_rx_all] (
  [label] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [code] varchar(6) COLLATE Chinese_PRC_BIN  NOT NULL,
  [name] nvarchar(32) COLLATE Chinese_PRC_BIN  NULL,
  [times] int  NULL,
  [his_code] varchar(150) COLLATE Chinese_PRC_BIN  NULL
)
GO

ALTER TABLE [dbo].[zd_rx_all] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'字典标签',
'SCHEMA', N'dbo',
'TABLE', N'zd_rx_all',
'COLUMN', N'label'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编码',
'SCHEMA', N'dbo',
'TABLE', N'zd_rx_all',
'COLUMN', N'code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'名称',
'SCHEMA', N'dbo',
'TABLE', N'zd_rx_all',
'COLUMN', N'name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'处方类别（电子处方流转）',
'SCHEMA', N'dbo',
'TABLE', N'zd_rx_all'
GO

/*
 Navicat Premium Data Transfer

 Source Server         : 172.16.32.179
 Source Server Type    : SQL Server
 Source Server Version : 10506000
 Source Host           : 172.16.32.179:1433
 Source Catalog        : thxyhisdb
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 10506000
 File Encoding         : 65001

 Date: 28/04/2025 10:27:53
*/


-- ----------------------------
-- Table structure for t_rx_precheck
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_rx_precheck]') AND type IN ('U'))
	DROP TABLE [dbo].[t_rx_precheck]
GO

CREATE TABLE [dbo].[t_rx_precheck] (
  [hosp_rxno] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [hi_rxno] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [rx_trace_code] varchar(20) COLLATE Chinese_PRC_BIN  NOT NULL,
  [mdtrt_id] varchar(30) COLLATE Chinese_PRC_BIN  NOT NULL,
  [patn_name] nvarchar(40) COLLATE Chinese_PRC_BIN  NOT NULL,
  [psn_cert_type] varchar(6) COLLATE Chinese_PRC_BIN  NOT NULL,
  [certno] varchar(64) COLLATE Chinese_PRC_BIN  NOT NULL,
  [dr_code] varchar(20) COLLATE Chinese_PRC_BIN  NOT NULL,
  [prsc_dr_name] nvarchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [phar_dept_code] varchar(30) COLLATE Chinese_PRC_BIN  NULL,
  [phar_dept_name] nvarchar(64) COLLATE Chinese_PRC_BIN  NULL,
  [phar_profttl_codg] varchar(6) COLLATE Chinese_PRC_BIN  NULL,
  [phar_profttl_name] nvarchar(16) COLLATE Chinese_PRC_BIN  NULL,
  [phar_code] varchar(20) COLLATE Chinese_PRC_BIN  NULL,
  [phar_name] nvarchar(64) COLLATE Chinese_PRC_BIN  NULL,
  [phar_cert_type] varchar(6) COLLATE Chinese_PRC_BIN  NULL,
  [phar_certno] varchar(64) COLLATE Chinese_PRC_BIN  NULL,
  [phar_prac_cert_no] int  NULL,
  [phar_chk_time] datetime  NULL,
  [sign_digest] varchar(4000) COLLATE Chinese_PRC_BIN  NULL,
  [original_rx_file] text COLLATE Chinese_PRC_BIN  NOT NULL,
  [rx_state] varchar(12) COLLATE Chinese_PRC_BIN DEFAULT ('PRE_CHECKED') NOT NULL
)
GO

ALTER TABLE [dbo].[t_rx_precheck] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'医疗机构处方编号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'hosp_rxno'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医保处方编号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'hi_rxno'
GO

EXEC sp_addextendedproperty
'MS_Description', N'处方追溯码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'rx_trace_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医保就诊ID',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'mdtrt_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'患者姓名',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'patn_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'人员证件类型',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'psn_cert_type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'证件号码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'certno'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开方医保医师代码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'dr_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开方医师姓名',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'prsc_dr_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'审方药师科室编号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'phar_dept_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'审方药师科室名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'phar_dept_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'审方药师职称编码（from zd_rx_all where label=''rx_phar_pro_tech''）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'phar_profttl_codg'
GO

EXEC sp_addextendedproperty
'MS_Description', N'审方药师职称名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'phar_profttl_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'审方医保药师代码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'phar_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'审方药师姓名',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'phar_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'审方药师证件类型（from zd_rx_all where label=''rx_psn_cert_type''）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'phar_cert_type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'审方药师证件号码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'phar_certno'
GO

EXEC sp_addextendedproperty
'MS_Description', N'审方药师执业资格证号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'phar_prac_cert_no'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医疗机构药师审方时间',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'phar_chk_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'处方信息签名值',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'sign_digest'
GO

EXEC sp_addextendedproperty
'MS_Description', N'处方原件',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'original_rx_file'
GO

EXEC sp_addextendedproperty
'MS_Description', N'处方状态',
'SCHEMA', N'dbo',
'TABLE', N't_rx_precheck',
'COLUMN', N'rx_state'
GO


-- ----------------------------
-- Primary Key structure for table t_rx_precheck
-- ----------------------------
ALTER TABLE [dbo].[t_rx_precheck] ADD CONSTRAINT [t_rx_precheck_pk] PRIMARY KEY CLUSTERED ([hosp_rxno], [hi_rxno])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

/*
 Navicat Premium Data Transfer

 Source Server         : 172.16.32.179
 Source Server Type    : SQL Server
 Source Server Version : 10506000
 Source Host           : 172.16.32.179:1433
 Source Catalog        : thxyhisdb
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 10506000
 File Encoding         : 65001

 Date: 28/04/2025 10:26:43
*/


-- ----------------------------
-- Table structure for t_rx_diseinfo
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_rx_diseinfo]') AND type IN ('U'))
	DROP TABLE [dbo].[t_rx_diseinfo]
GO

CREATE TABLE [dbo].[t_rx_diseinfo] (
  [hosp_rxno] varchar(32) COLLATE Chinese_PRC_BIN  NOT NULL,
  [diag_type] varchar(3) COLLATE Chinese_PRC_BIN  NOT NULL,
  [maindiag_flag] varchar(3) COLLATE Chinese_PRC_BIN  NOT NULL,
  [diag_srt_no] int  NOT NULL,
  [diag_code] varchar(30) COLLATE Chinese_PRC_BIN  NOT NULL,
  [diag_name] nvarchar(100) COLLATE Chinese_PRC_BIN  NOT NULL,
  [diag_dept] nvarchar(50) COLLATE Chinese_PRC_BIN  NOT NULL,
  [diag_dept_code] varchar(20) COLLATE Chinese_PRC_BIN  NOT NULL,
  [diag_dr_no] varchar(30) COLLATE Chinese_PRC_BIN  NOT NULL,
  [diag_dr_name] nvarchar(30) COLLATE Chinese_PRC_BIN  NOT NULL,
  [diag_time] datetime  NOT NULL,
  [tcm_dise_code] varchar(30) COLLATE Chinese_PRC_BIN  NULL,
  [tcm_dise_name] nvarchar(300) COLLATE Chinese_PRC_BIN  NULL,
  [tcmsymp_code] varchar(30) COLLATE Chinese_PRC_BIN  NULL,
  [tcmsymp] nvarchar(300) COLLATE Chinese_PRC_BIN  NULL
)
GO

ALTER TABLE [dbo].[t_rx_diseinfo] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'医疗机构处方编号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'hosp_rxno'
GO

EXEC sp_addextendedproperty
'MS_Description', N'诊断类别（from zd_rx_all where label=''rx_diag_type''）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'diag_type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'主诊断标志（0-否、1-是）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'maindiag_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'诊断排序号',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'diag_srt_no'
GO

EXEC sp_addextendedproperty
'MS_Description', N'诊断代码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'diag_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'诊断名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'diag_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'诊断科室名称',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'diag_dept'
GO

EXEC sp_addextendedproperty
'MS_Description', N'诊断科室代码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'diag_dept_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'诊断医生医保编码',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'diag_dr_no'
GO

EXEC sp_addextendedproperty
'MS_Description', N'诊断医生姓名',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'diag_dr_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'诊断时间',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'diag_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中医病名代码（diagType不为西医诊断时上传）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'tcm_dise_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中医病名（diagType不为西医诊断时上传）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'tcm_dise_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中医证候代码（diagType不为西医诊断时上传）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'tcmsymp_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中医证候（diagType不为西医诊断时上传）',
'SCHEMA', N'dbo',
'TABLE', N't_rx_diseinfo',
'COLUMN', N'tcmsymp'
GO


-- ----------------------------
-- Primary Key structure for table t_rx_diseinfo
-- ----------------------------
ALTER TABLE [dbo].[t_rx_diseinfo] ADD CONSTRAINT [t_rx_diseinfo_pk] PRIMARY KEY CLUSTERED ([hosp_rxno], [diag_srt_no])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
;

alter table mzy_zd_charge_type
    add charge_code varchar(10) ;

alter table t_workspace_config
    add crb_history_flag int ;

alter table t_discount
	add time_type varchar(255) default '0'
go

alter table t_discount
	add week_day varchar(255)
go

alter table t_discount
	add month_day varchar(100)
go

alter table t_discount
	add always_day varchar(100)
go

alter table t_discount
	add month_num varchar(100)
go

alter table t_discount
	add start_time varchar(100)
go

alter table t_discount
	add end_time varchar(100);

alter table zy_receipt
    add dzfp_receipt_no varchar(32) ;

alter table yp_zd_dict add bar_code varchar(30);
alter table yp_zd_dict add buy_flag varchar(1);
alter table yp_zd_dict add health_flag varchar(1);
alter table yp_zd_dict add health_content varchar(1000);

alter table t_common_params alter column params_content varchar(800) null
go


--药房增加字段 2025-06-16
alter table yp_zd_group_name
    add zy_flag varchar(5)
go

exec sp_addextendedproperty 'MS_Description', '住院药房标识 0 否 1 是', 'SCHEMA', 'dbo', 'TABLE', 'yp_zd_group_name', 'COLUMN', 'zy_flag'
go

alter table yp_zd_group_name
    add xy_flag varchar(5)
go

exec sp_addextendedproperty 'MS_Description', '是否 西药药房 0 否 1是', 'SCHEMA', 'dbo', 'TABLE', 'yp_zd_group_name', 'COLUMN', 'xy_flag'
go

alter table yp_zd_group_name
    add zcy_flag varchar(5)
go

exec sp_addextendedproperty 'MS_Description', '是否中草药药房 0 否 1是', 'SCHEMA', 'dbo', 'TABLE', 'yp_zd_group_name', 'COLUMN', 'zcy_flag'
go

alter table yp_zd_group_name
    add del_flag varchar(1)
go

exec sp_addextendedproperty 'MS_Description', '删除标识 0有效 1删除 ', 'SCHEMA', 'dbo', 'TABLE', 'yp_zd_group_name', 'COLUMN', 'del_flag'
go

alter table yp_zd_group_name
    add start_time varchar(150)
go

exec sp_addextendedproperty 'MS_Description', '有效开始时间', 'SCHEMA', 'dbo', 'TABLE', 'yp_zd_group_name', 'COLUMN', 'start_time'
go

alter table yp_zd_group_name
    add end_time varchar(150)
go

exec sp_addextendedproperty 'MS_Description', '有效结束时间', 'SCHEMA', 'dbo', 'TABLE', 'yp_zd_group_name', 'COLUMN', 'end_time';

alter table yp_zd_group_name
    add yc_flag varchar(5)
go

exec sp_addextendedproperty 'MS_Description', '医材药房 0 否 1是', 'SCHEMA', 'dbo', 'TABLE', 'yp_zd_group_name', 'COLUMN', 'yc_flag'
go

alter table yp_zd_group_name
    add kl_flag varchar(5)
go

exec sp_addextendedproperty 'MS_Description', '颗粒药房 0 否 1 是', 'SCHEMA', 'dbo', 'TABLE', 'yp_zd_group_name', 'COLUMN', 'kl_flag';




--新增中医诊断字典表
create table zd_tcm_diag
(
    code varchar(250),
    name varchar(250),
    py_code varchar(100),
    wb_code varchar(100),
    del_flag varchar(1) default '0'
)
go

exec sp_addextendedproperty 'MS_Description', '中医诊断字典', 'SCHEMA', 'dbo', 'TABLE', 'zd_tcm_diag'
go

exec sp_addextendedproperty 'MS_Description', '0 有效 1 删除', 'SCHEMA', 'dbo', 'TABLE', 'zd_tcm_diag', 'COLUMN', 'del_flag';

--住院证增加押金
alter table mz_zy_req
    add deposit decimal(18,2)
go

exec sp_addextendedproperty 'MS_Description', '押金', 'SCHEMA', 'dbo', 'TABLE', 'mz_zy_req', 'COLUMN', 'deposit';
exec sp_addextendedproperty 'MS_Description', '押金', 'SCHEMA', 'dbo', 'TABLE', 'mz_zy_req', 'COLUMN', 'deposit';


--增加退费权限按钮 2025-06-20
insert into t_button (name,parent_id, button_code, sort_value, create_time, create_id)
values ( '门诊退费',3,'mz_refund',999,getdate(),1);
insert into t_button (name,parent_id, button_code, sort_value, create_time, create_id)
values ( '挂号退费',3,'gh_refund',998,getdate(),1);

--天资要求新增隐藏开外配药处方药隐藏 其他 可以不隐藏  20250620
insert into t_dict_data (dict_type, dict_value, dict_name, remark)
values ('wpy','1','wpy_hide','外配药和指引单提示隐藏 0 否 1 隐藏');


--新增中医诊断 20250624
alter table mz_visit_table
    add tcm_icd_code varchar(2500)
go

exec sp_addextendedproperty 'MS_Description', '中医诊断编码', 'SCHEMA', 'dbo', 'TABLE', 'mz_visit_table', 'COLUMN', 'tcm_icd_code'
go

alter table mz_visit_table
    add tcm_icd_text varchar(2500)
go

exec sp_addextendedproperty 'MS_Description', '中医诊断名称', 'SCHEMA', 'dbo', 'TABLE', 'mz_visit_table', 'COLUMN', 'tcm_icd_text'
go
--自动收取挂号费标识  20250630
alter table mz_order_detail
    add gh_fee_flag varchar(5)
go

exec sp_addextendedproperty 'MS_Description', '0 开处方不自动收取挂号费 1 开处方自动收取挂号费', 'SCHEMA', 'dbo', 'TABLE', 'mz_order_detail', 'COLUMN', 'gh_fee_flag'
go


--新增处方类型 20250709
insert into mz_zd_order_type (code, name, py_code, d_code, del_flag) values ('6','医技录入处方','YJLRCF','YJLRCF','0')

--挂号记录 新增推荐人 20250718
alter table mzy_reqrec
    add rec_staff varchar(100)
go

exec sp_addextendedproperty 'MS_Description', N'推荐人编码', 'SCHEMA', 'dbo', 'TABLE', 'mzy_reqrec', 'COLUMN',
     'rec_staff'
go