671 lines
33 KiB
SQL
671 lines
33 KiB
SQL
--RTOS小程序数据库 sql文档
|
||
|
||
-- Database: rtos-wx-applet
|
||
-- DROP DATABASE IF EXISTS rtos-wx-applet;
|
||
|
||
CREATE DATABASE rtos_wx_applet
|
||
WITH
|
||
OWNER = postgres
|
||
ENCODING = 'UTF8'
|
||
LC_COLLATE = 'Chinese (Simplified)_China.936'
|
||
LC_CTYPE = 'Chinese (Simplified)_China.936'
|
||
TABLESPACE = pg_default
|
||
CONNECTION LIMIT = -1
|
||
IS_TEMPLATE = False;
|
||
COMMENT ON DATABASE rtos_wx_applet IS 'RTOS小程序数据库';
|
||
|
||
|
||
--1-用户（司机）信息
|
||
CREATE TABLE IF NOT EXISTS public.applet_users(
|
||
id serial,
|
||
users_name varchar(32) DEFAULT NULL,
|
||
wx_openid varchar(32) DEFAULT NULL,
|
||
wx_unionid varchar(32) DEFAULT NULL,
|
||
wx_nick varchar(32) DEFAULT NULL,
|
||
wx_sex varchar(8) DEFAULT NULL,
|
||
wx_head_sculpture varchar(256) DEFAULT NULL,
|
||
tel_number varchar(32) NOT NULL,
|
||
id_code varchar(32) DEFAULT NULL,
|
||
users_state integer DEFAULT 0,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_users_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_users.id IS '用户id';
|
||
COMMENT ON COLUMN applet_users.users_name IS '用户姓名,默认为微信昵称';
|
||
COMMENT ON COLUMN applet_users.wx_openid IS '微信获取openid-用户小程序唯一标识';
|
||
COMMENT ON COLUMN applet_users.wx_unionid IS '微信获取unionid-用户微信应用唯一标识';
|
||
COMMENT ON COLUMN applet_users.wx_nick IS '微信获取昵称';
|
||
COMMENT ON COLUMN applet_users.wx_sex IS '微信获取性别';
|
||
COMMENT ON COLUMN applet_users.wx_head_sculpture IS '微信获取头像';
|
||
COMMENT ON COLUMN applet_users.tel_number IS '手机号码';
|
||
COMMENT ON COLUMN applet_users.id_code IS '用户身分证号码';
|
||
COMMENT ON COLUMN applet_users.users_state IS '用户状态（0-正常，1-停用）';
|
||
COMMENT ON COLUMN applet_users.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_users.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_users.is_del IS '是否软删除';
|
||
|
||
|
||
--2-板车信息（用户扫描行驶证获取）
|
||
CREATE TABLE IF NOT EXISTS public.applet_truck(
|
||
id serial,
|
||
users_id integer,
|
||
truck_number varchar(16) DEFAULT NULL,
|
||
driving_license_id varchar(32) DEFAULT NULL,
|
||
tel_number varchar(16) DEFAULT NULL,
|
||
head_frame_number varchar(32) DEFAULT NULL,
|
||
vehicle_weight integer DEFAULT NULL,
|
||
vehicle_frame_weight integer DEFAULT NULL,
|
||
customs_declaration_no varchar(32) DEFAULT NULL,
|
||
driving_license varchar(256) DEFAULT NULL,
|
||
truck_state integer default 0,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_truck_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_truck.id IS '板车id';
|
||
COMMENT ON COLUMN applet_truck.users_id IS '用户id';
|
||
COMMENT ON COLUMN applet_truck.truck_number IS '车牌号（数据唯一性约束）';
|
||
COMMENT ON COLUMN applet_truck.driving_license_id IS '行驶证ID';
|
||
COMMENT ON COLUMN applet_truck.tel_number IS '司机电话';
|
||
COMMENT ON COLUMN applet_truck.head_frame_number IS '车头车架号';
|
||
COMMENT ON COLUMN applet_truck.vehicle_weight IS '车辆自重';
|
||
COMMENT ON COLUMN applet_truck.vehicle_frame_weight IS '车架重';
|
||
COMMENT ON COLUMN applet_truck.driving_license IS '行驶证（扫描件）';
|
||
COMMENT ON COLUMN applet_truck.truck_state IS '板车状态（1-默认，0-其他，4-拉黑）';
|
||
COMMENT ON COLUMN applet_truck.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_truck.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_truck.is_del IS '是否软删除';
|
||
|
||
|
||
--3-公告信息（小程序/客服后台推送）
|
||
CREATE TABLE IF NOT EXISTS public.applet_announcements(
|
||
id serial,
|
||
title varchar(64) DEFAULT NULL,
|
||
abstracts varchar(256) DEFAULT NULL,
|
||
announcement text COLLATE pg_catalog."default" DEFAULT 'NULL::text',
|
||
author varchar(32) DEFAULT NULL,
|
||
reading_volume integer DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_announcements_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_announcements.id IS '公告id';
|
||
COMMENT ON COLUMN applet_announcements.title IS '标题';
|
||
COMMENT ON COLUMN applet_announcements.abstracts IS '摘要';
|
||
COMMENT ON COLUMN applet_announcements.announcement IS '公告内容';
|
||
COMMENT ON COLUMN applet_announcements.author IS '作者';
|
||
COMMENT ON COLUMN applet_announcements.reading_volume IS '阅读量';
|
||
COMMENT ON COLUMN applet_announcements.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_announcements.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_announcements.is_del IS '是否软删除';
|
||
|
||
|
||
--4-预约信息
|
||
CREATE TABLE IF NOT EXISTS public.applet_appointment(
|
||
id serial,
|
||
users_id integer DEFAULT NULL,
|
||
transport_type integer DEFAULT NULL,
|
||
truck_number varchar(16) DEFAULT NULL,
|
||
tel_number varchar(16) DEFAULT NULL,
|
||
|
||
//head_frame_number varchar(32) DEFAULT NULL,
|
||
vehicle_weight integer,
|
||
vehicle_frame_weight integer,
|
||
driving_license varchar(32) DEFAULT NULL,
|
||
approach_date date DEFAULT NULL,
|
||
port_area_id integer DEFAULT NULL,
|
||
biz_type integer DEFAULT NULL,
|
||
enter_type integer DEFAULT NULL,
|
||
goods_type integer DEFAULT NULL,
|
||
goods_Quantity integer,
|
||
appointment_state integer default 0,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_appointment_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_appointment.id IS '预约信息id';
|
||
COMMENT ON COLUMN applet_appointment.users_id IS '用户id';
|
||
COMMENT ON COLUMN applet_appointment.transport_type IS '运输方式（1/2/3）';//
|
||
COMMENT ON COLUMN applet_appointment.truck_number IS '板车号';//
|
||
COMMENT ON COLUMN applet_appointment.tel_number IS '手机号';//
|
||
COMMENT ON COLUMN applet_appointment.head_frame_number IS '车头车架号';//
|
||
COMMENT ON COLUMN applet_appointment.vehicle_weight IS '板车自重（吨）';//
|
||
COMMENT ON COLUMN applet_appointment.vehicle_frame_weight IS '车架重（吨）';//
|
||
COMMENT ON COLUMN applet_appointment.driving_license IS '行驶证编号';//
|
||
COMMENT ON COLUMN applet_appointment.approach_date IS '预约进场日期';//
|
||
COMMENT ON COLUMN applet_punch_clock.biz_type IS '业务类型（1-全部，2-码头业务，3-整车物流，4-特保区预约）';
|
||
COMMENT ON COLUMN applet_appointment.port_area_id IS '进港港区Id';//
|
||
COMMENT ON COLUMN applet_appointment.enter_type IS '进港类型（1-外贸出口,2-外贸进口,3-整车物流,4-特保区出口,5-特保区进口,6-内贸出口,7-内贸进口）';//
|
||
COMMENT ON COLUMN applet_appointment.goods_type IS '货物类型（1-商品车,2-设备,3-其他）';//
|
||
COMMENT ON COLUMN applet_appointment.goods_Quantity IS '货物数量（件数）';//
|
||
COMMENT ON COLUMN applet_appointment.appointment_state IS '预约打卡状态（1-已预约，2-已签到，3-已进港，5-操作中，6-已完成，7-已离港，4-已取消，9-异常：预期未完成操作的）';//
|
||
COMMENT ON COLUMN applet_appointment.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_appointment.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_appointment.is_del IS '是否软删除';
|
||
需要 司机生份证
|
||
|
||
|
||
-- 5-预约明细
|
||
CREATE TABLE IF NOT EXISTS public.applet_appointment_detail(
|
||
id serial,
|
||
appointment_id integer DEFAULT NULL,
|
||
ship_name varchar(16) DEFAULT NULL,
|
||
voy_number varchar(16) DEFAULT NULL,
|
||
destination_port varchar(32) DEFAULT NULL,
|
||
brand_name varchar(32) DEFAULT NULL,
|
||
vin varchar(32) DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_appointment_detail_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_appointment_detail.id IS '预约详细id';
|
||
COMMENT ON COLUMN applet_appointment_detail.appointment_id IS '预约id';
|
||
COMMENT ON COLUMN applet_appointment_detail.ship_name IS '船名';//
|
||
COMMENT ON COLUMN applet_appointment_detail.voy_number IS '航次号';
|
||
COMMENT ON COLUMN applet_appointment_detail.destination_port IS '目的港';//
|
||
COMMENT ON COLUMN applet_appointment_detail.brand_name IS '品牌/名称';//
|
||
COMMENT ON COLUMN applet_appointment_detail.vin IS '车架号';//
|
||
COMMENT ON COLUMN applet_appointment_detail.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_appointment_detail.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_appointment_detail.is_del IS '是否软删除';
|
||
|
||
|
||
--6-（签到/完成）打卡信息
|
||
CREATE TABLE IF NOT EXISTS public.applet_punch_clock(
|
||
id serial,
|
||
users_id integer DEFAULT NULL,
|
||
appointment_id integer DEFAULT NULL,
|
||
biz_type integer DEFAULT NULL,
|
||
queue_number varchar(16),
|
||
--approach_date date DEFAULT NULL,
|
||
punch_start_time time DEFAULT NULL,
|
||
punch_over_time time DEFAULT NULL,
|
||
port_area_id integer DEFAULT NULL,
|
||
punch_address varchar(256) DEFAULT NULL,
|
||
over_address varchar(256) DEFAULT NULL,
|
||
punch_clock_type integer,
|
||
punch_clock_poi varchar(64) DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_punch_clock_pkey PRIMARY KEY (id)
|
||
);
|
||
|
||
COMMENT ON COLUMN applet_punch_clock.id IS '打卡信息id';
|
||
COMMENT ON COLUMN applet_punch_clock.users_id IS '用户id';
|
||
COMMENT ON COLUMN applet_punch_clock.appointment_id IS '预约Id';
|
||
COMMENT ON COLUMN applet_punch_clock.biz_type IS '业务类型（1-全部，2-码头业务，3-整车物流，4-特保区预约）';
|
||
COMMENT ON COLUMN applet_punch_clock.queue_number IS '排队编号';
|
||
--COMMENT ON COLUMN applet_punch_clock.approach_date IS '打卡日期';
|
||
COMMENT ON COLUMN applet_punch_clock.start_time IS '签到日期时间';
|
||
COMMENT ON COLUMN applet_punch_clock.punch_over_time IS '完成日期时间';
|
||
COMMENT ON COLUMN applet_punch_clock.port_area_id IS '打卡港区(ID)';
|
||
COMMENT ON COLUMN applet_punch_clock.punch_address IS '签到打卡地点';
|
||
COMMENT ON COLUMN applet_punch_clock.over_address IS '完成打卡地点';
|
||
COMMENT ON COLUMN applet_punch_clock.punch_clock_type IS '打卡类别（1-签到，2-已进港，3-操作中，4-已完成，5-已离港）';
|
||
COMMENT ON COLUMN applet_punch_clock.punch_clock_poi IS '打卡（位置坐标）{"1":poi2,"2",poi2}';
|
||
COMMENT ON COLUMN applet_punch_clock.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_punch_clock.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_punch_clock.is_del IS '是否软删除';
|
||
|
||
|
||
--7-收车信息
|
||
CREATE TABLE IF NOT EXISTS public.applet_receiving_vehicle(
|
||
id serial,
|
||
users_id integer DEFAULT NULL,
|
||
ship_name varchar(64) DEFAULT NULL,
|
||
voy_number varchar(64) DEFAULT NULL,
|
||
complete_project_time timestamp(0) DEFAULT NULL,
|
||
task_number varchar(32) DEFAULT NULL,
|
||
transport_type varchar(64) DEFAULT NULL,
|
||
truck_number varchar(32) DEFAULT NULL,
|
||
enter_port_area varchar(32) DEFAULT NULL,
|
||
enter_port_time timestamp(0) DEFAULT NULL,
|
||
operation_team varchar(16) DEFAULT NULL,
|
||
goods_info text DEFAULT NULL,
|
||
vehicle_frame_data varchar(2048) DEFAULT NULL,
|
||
receiving_state integer DEFAULT 0,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_receiving_vehicle_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_receiving_vehicle.id IS '收车信息id';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.users_id IS '用户id';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.ship_name IS '船名';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.voy_number IS '航次';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.complete_project_time IS '完工日期时间';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.task_number IS '作业编号';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.transport_type IS '运输方式';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.truck_number IS '车牌号（板车号）';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.enter_port_area IS '进港港区';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.enter_port_time IS '进港日期时间';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.operation_team IS '作业工班';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.goods_info IS '货物信息（type:vehicle/others,
|
||
vehicle{品牌/型号/车型/数量/港口或国家/车辆类型/卸货方式：港区作业或客户自提，Json数据},
|
||
others{货名/长/宽/高/重量/数量/港口、国家/卸货方式（港区作业/客户自卸）的Json数据}）';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.vehicle_frame_data IS '车架号数据（[品牌,车架号]的Json数据）';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.receiving_state IS '收车状态（0-默认，未收车，1-已收车，2-已核对)';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_receiving_vehicle.is_del IS '是否软删除';
|
||
|
||
|
||
--8-发车信息
|
||
CREATE TABLE IF NOT EXISTS public.applet_departure_vehicle(
|
||
id serial,
|
||
users_id integer DEFAULT NULL,
|
||
ship_name varchar(64) DEFAULT NULL,
|
||
voy_number varchar(64) DEFAULT NULL,
|
||
complete_project_time timestamp(0) DEFAULT NULL,
|
||
task_number varchar(32) DEFAULT NULL,
|
||
truck_number varchar(32) DEFAULT NULL,
|
||
receiving_unit varchar(64) DEFAULT NULL,
|
||
lading_no varchar(64) DEFAULT NULL,
|
||
customs_clearance_nature varchar(32) DEFAULT NULL,
|
||
customs_declaration_no varchar(32) DEFAULT NULL,
|
||
vehicle_frame_data varchar(1024) DEFAULT NULL,
|
||
goods_name varchar(32) DEFAULT NULL,
|
||
goods_Quantity integer,
|
||
certificate_Preparation_date timestamp(0) DEFAULT NULL,
|
||
liberator varchar(32) DEFAULT NULL,
|
||
signee varchar(32) DEFAULT NULL,
|
||
remarks varchar(256) DEFAULT NULL,
|
||
departure_state integer default 0,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_departure_vehicle_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_departure_vehicle.id IS '发车信息Id';
|
||
COMMENT ON COLUMN applet_departure_vehicle.users_id IS '用户id';
|
||
COMMENT ON COLUMN applet_departure_vehicle.ship_name IS '船名';
|
||
COMMENT ON COLUMN applet_departure_vehicle.voy_number IS '航次';
|
||
COMMENT ON COLUMN applet_departure_vehicle.complete_project_time IS '完工日期时间';
|
||
COMMENT ON COLUMN applet_departure_vehicle.task_number IS '作业编号';
|
||
COMMENT ON COLUMN applet_departure_vehicle.truck_number IS '车牌号（板车号）';
|
||
COMMENT ON COLUMN applet_departure_vehicle.receiving_unit IS '收货单位';
|
||
COMMENT ON COLUMN applet_departure_vehicle.lading_no IS '提单号';
|
||
COMMENT ON COLUMN applet_departure_vehicle.customs_clearance_nature IS '通关性质';
|
||
COMMENT ON COLUMN applet_departure_vehicle.customs_declaration_no IS '报关单号';
|
||
COMMENT ON COLUMN applet_departure_vehicle.vehicle_frame_data IS '车架号';
|
||
COMMENT ON COLUMN applet_departure_vehicle.goods_name IS '货名';
|
||
COMMENT ON COLUMN applet_departure_vehicle.goods_Quantity IS '件数';
|
||
COMMENT ON COLUMN applet_departure_vehicle.certificate_Preparation_date IS '制证日期';
|
||
COMMENT ON COLUMN applet_departure_vehicle.liberator IS '放行者';
|
||
COMMENT ON COLUMN applet_departure_vehicle.signee IS '签收者';
|
||
COMMENT ON COLUMN applet_departure_vehicle.remarks IS '备注';
|
||
COMMENT ON COLUMN applet_departure_vehicle.departure_state IS '发车状态（0-默认，未发车，1-已收车，2-已核对）';
|
||
COMMENT ON COLUMN applet_departure_vehicle.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_departure_vehicle.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_departure_vehicle.is_del IS '是否软删除';
|
||
|
||
|
||
--9-意见反馈
|
||
CREATE TABLE IF NOT EXISTS public.applet_feedback(
|
||
id serial,
|
||
users_id integer DEFAULT NULL,
|
||
nike_name varchar(32) DEFAULT NULL,
|
||
feedback_info varchar(1024) DEFAULT NULL,
|
||
pictures varchar(256) DEFAULT NULL,
|
||
tel_number varchar(64) DEFAULT NULL,
|
||
contact_info varchar(64) DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT feedback_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_feedback.id IS '意见反馈id';
|
||
COMMENT ON COLUMN applet_feedback.users_id IS '用户id';
|
||
COMMENT ON COLUMN applet_feedback.nike_name IS '昵称';
|
||
COMMENT ON COLUMN applet_feedback.feedback_info IS '意见（限500字）';
|
||
COMMENT ON COLUMN applet_feedback.pictures IS '图片文件名集合，逗号分隔';
|
||
COMMENT ON COLUMN applet_feedback.tel_number IS '联系电话';
|
||
COMMENT ON COLUMN applet_feedback.contact_info IS '联系方式（微信号/qq/邮箱）';
|
||
COMMENT ON COLUMN applet_feedback.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_feedback.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_feedback.is_del IS '是否软删除';
|
||
|
||
|
||
--10-新手攻略（小程序/客服平台/生产APP操作指南）
|
||
CREATE TABLE IF NOT EXISTS public.applet_novice_introduction(
|
||
id serial,
|
||
sort integer DEFAULT NULL,
|
||
title varchar(64) DEFAULT NULL,
|
||
content text DEFAULT NULL,
|
||
sequence_number int DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT novice_introduction_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_novice_introduction.id IS '攻略_id';
|
||
COMMENT ON COLUMN applet_novice_introduction.sort IS '类别（1-小程序，2-客服平台，3-生产APP）';
|
||
COMMENT ON COLUMN applet_novice_introduction.title IS '小标题';
|
||
COMMENT ON COLUMN applet_novice_introduction.content IS '内容（html文本，允许包含图片标签）';
|
||
COMMENT ON COLUMN applet_novice_introduction.sequence_number IS '顺序号';
|
||
COMMENT ON COLUMN applet_novice_introduction.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_novice_introduction.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_novice_introduction.is_del IS '是否软删除';
|
||
|
||
|
||
--11-运输方式
|
||
CREATE TABLE IF NOT EXISTS public.applet_transport_types(
|
||
id serial,
|
||
transport_type_name varchar(64) DEFAULT NULL,
|
||
transport_type varchar(64) DEFAULT NULL,
|
||
sequence_number int DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT transport_types_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_transport_types.id IS '运输方式id';
|
||
COMMENT ON COLUMN applet_transport_types.transport_type_name IS '运输方式名称（板车运输/自开）';
|
||
COMMENT ON COLUMN applet_transport_types.transport_type IS '运输方式';
|
||
COMMENT ON COLUMN applet_transport_types.sequence_number IS '顺序号';
|
||
COMMENT ON COLUMN applet_transport_types.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_transport_types.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_transport_types.is_del IS '是否软删除';
|
||
|
||
|
||
--12-港区
|
||
CREATE TABLE IF NOT EXISTS public.applet_port_areas(
|
||
id serial,
|
||
port_area varchar(64) DEFAULT NULL,
|
||
area_center_poi varchar(32) DEFAULT NULL,
|
||
punch_range integer DEFAULT NULL,
|
||
sequence_number int DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT port_areas_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_port_areas.id IS '港区id';
|
||
COMMENT ON COLUMN applet_port_areas.port_area IS '港区名';
|
||
COMMENT ON COLUMN applet_port_areas.area_center_poi IS '港区坐标';
|
||
COMMENT ON COLUMN applet_port_areas.punch_range IS '打卡范围（距离港区中心坐标的半径，米）';
|
||
COMMENT ON COLUMN applet_port_areas.sequence_number IS '顺序号';
|
||
COMMENT ON COLUMN applet_port_areas.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_port_areas.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_port_areas.is_del IS '是否软删除';
|
||
|
||
|
||
--13-作业工班
|
||
CREATE TABLE IF NOT EXISTS public.applet_operation_teams(
|
||
id serial,
|
||
operation_team varchar(64) DEFAULT NULL,
|
||
sequence_number int DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT operation_teams_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_operation_teams.id IS '作业工班id';
|
||
COMMENT ON COLUMN applet_operation_teams.operation_team IS '作业工班';
|
||
COMMENT ON COLUMN applet_operation_teams.sequence_number IS '顺序号';
|
||
COMMENT ON COLUMN applet_operation_teams.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_operation_teams.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_operation_teams.is_del IS '是否软删除';
|
||
|
||
|
||
--14-车辆管理
|
||
CREATE TABLE IF NOT EXISTS public.applet_truck_management(
|
||
id serial,
|
||
users_id integer DEFAULT NULL,
|
||
truck_number varchar(32) DEFAULT NULL,
|
||
cause varchar(256) DEFAULT NULL,
|
||
drivers_name varchar(32) DEFAULT NULL,
|
||
drivers_phone varchar(32) DEFAULT NULL,
|
||
administrators_name varchar(32) DEFAULT NULL,
|
||
administrators_phone varchar(32) DEFAULT NULL,
|
||
enter_time timestamp(0) DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_truck_management_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_truck_management.id IS '车辆管理id';
|
||
COMMENT ON COLUMN applet_truck_management.users_id IS '用户id';
|
||
COMMENT ON COLUMN applet_truck_management.truck_number IS '（加入黑名单的）车牌号';
|
||
COMMENT ON COLUMN applet_truck_management.cause IS '原因';
|
||
COMMENT ON COLUMN applet_truck_management.drivers_name IS '司机姓名';
|
||
COMMENT ON COLUMN applet_truck_management.drivers_phone IS '司机电话';
|
||
COMMENT ON COLUMN applet_truck_management.administrators_name IS '管理员姓名';
|
||
COMMENT ON COLUMN applet_truck_management.administrators_phone IS '管理员电话';
|
||
COMMENT ON COLUMN applet_truck_management.enter_time IS '加入黑名单时间';
|
||
COMMENT ON COLUMN applet_truck_management.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_truck_management.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_truck_management.is_del IS '是否软删除';
|
||
|
||
|
||
--15-货物类型
|
||
CREATE TABLE IF NOT EXISTS public.applet_goods_types(
|
||
id serial,
|
||
goods_type_name varchar(64) DEFAULT NULL,
|
||
goods_type integer DEFAULT NULL,
|
||
sequence_number integer,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT goods_types_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_goods_types.id IS '货物类型id';
|
||
COMMENT ON COLUMN applet_goods_types.goods_type_name IS '货物类型（1-商品车/2-设备/3-其他...）';
|
||
COMMENT ON COLUMN applet_goods_types.goods_type IS '货物类型（1-商品车/2-设备/3-其他...）';
|
||
COMMENT ON COLUMN applet_goods_types.sequence_number IS '顺序号';
|
||
COMMENT ON COLUMN applet_goods_types.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_goods_types.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_goods_types.is_del IS '是否软删除';
|
||
|
||
|
||
--16-进港类型
|
||
CREATE TABLE IF NOT EXISTS public.applet_enter_type(
|
||
id serial,
|
||
enter_port_type_name varchar(32) DEFAULT NULL,
|
||
enter_port_type integer DEFAULT NULL,
|
||
sequence_number integer,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_enter_types_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_enter_type.id IS '进港类型id';
|
||
COMMENT ON COLUMN applet_enter_type.enter_port_type_name IS '进港类型名称（1-外贸出口/2-内贸出口/3-外贸进口/4-内贸进口/5-特保区出口/6-特保区进口...）';
|
||
COMMENT ON COLUMN applet_enter_type.enter_port_type IS '进港类型（1/2/3/4/5/6/...）';
|
||
COMMENT ON COLUMN applet_enter_type.sequence_number IS '顺序号';
|
||
COMMENT ON COLUMN applet_enter_type.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_enter_type.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_enter_type.is_del IS '是否软删除';
|
||
|
||
|
||
--17-船名航次
|
||
CREATE TABLE IF NOT EXISTS public.applet_ship_voy(
|
||
id serial,
|
||
ship_name varchar(32) DEFAULT NULL,
|
||
voy_number varchar(32) DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_ship_voy_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_ship_voy.id IS '船名航次id';
|
||
COMMENT ON COLUMN applet_ship_voy.ship_name IS '船名';
|
||
COMMENT ON COLUMN applet_ship_voy.voy_number IS '航次编号';
|
||
COMMENT ON COLUMN applet_ship_voy.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_ship_voy.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_ship_voy.is_del IS '是否软删除';
|
||
|
||
|
||
--18-（运抵）港口
|
||
CREATE TABLE IF NOT EXISTS public.applet_port(
|
||
id serial,
|
||
ship_voy_id integer DEFAULT NULL,
|
||
port_name varchar(64) DEFAULT NULL,
|
||
sequence_number int DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT ports_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_port.id IS '港口id';
|
||
COMMENT ON COLUMN applet_port.ship_voy_id IS '船名航次id';
|
||
COMMENT ON COLUMN applet_port.port_name IS '港口名称';
|
||
COMMENT ON COLUMN applet_port.sequence_number IS '顺序号';
|
||
COMMENT ON COLUMN applet_port.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_port.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_port.is_del IS '是否软删除';
|
||
|
||
|
||
--19-商品车品牌
|
||
CREATE TABLE IF NOT EXISTS public.applet_brand(
|
||
id serial,
|
||
ship_voy_id integer DEFAULT NULL,
|
||
brand varchar(64) DEFAULT NULL,
|
||
sequence_number int DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_brand_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_brand.id IS '品牌id';
|
||
COMMENT ON COLUMN applet_brand.ship_voy_id IS '船名航次id';
|
||
COMMENT ON COLUMN applet_brand.brand IS '品牌名称';
|
||
COMMENT ON COLUMN applet_brand.sequence_number IS '顺序号';
|
||
COMMENT ON COLUMN applet_brand.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_brand.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_brand.is_del IS '是否软删除';
|
||
|
||
|
||
--20-进港指令
|
||
CREATE TABLE IF NOT EXISTS public.applet_entry_instruct(
|
||
id serial,
|
||
appointment_id integer DEFAULT NULL,
|
||
users_id integer DEFAULT NULL,
|
||
users_name varchar(32) DEFAULT NULL,
|
||
truck_number varchar(32) DEFAULT NULL,
|
||
yard_name varchar(32) DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_entry_instruct_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_entry_instruct.id IS '进港指令id';
|
||
COMMENT ON COLUMN applet_entry_instruct.appointment_id IS '预约id';
|
||
COMMENT ON COLUMN applet_entry_instruct.users_id IS '司机id';
|
||
COMMENT ON COLUMN applet_entry_instruct.users_name IS '司机姓名';
|
||
COMMENT ON COLUMN applet_entry_instruct.truck_number IS '车牌号';
|
||
COMMENT ON COLUMN applet_entry_instruct.yard_name IS '堆场';
|
||
COMMENT ON COLUMN applet_entry_instruct.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_entry_instruct.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_entry_instruct.is_del IS '是否软删除';
|
||
|
||
|
||
--21-服务配置
|
||
CREATE TABLE IF NOT EXISTS public.applet_service_config(
|
||
id serial,
|
||
config_name varchar(32) default null,
|
||
config_code varchar(32) default null,
|
||
config_content varchar(256) default null,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_service_config_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_service_config.id IS '自增id';
|
||
COMMENT ON COLUMN applet_service_config.config_name IS '配置名称（系统服务地址,图片文件存储路径,图片访问路径,小程序appid,小程序密钥secret)';
|
||
COMMENT ON COLUMN applet_service_config.config_code IS '配置代码（100-系统服务地址,200-图片文件存储路径,300-图片访问路径,400-小程序appid,500-小程序密钥secret)';
|
||
COMMENT ON COLUMN applet_service_config.config_content IS '配置信息(文本)';
|
||
COMMENT ON COLUMN applet_service_config.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_service_config.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_service_config.is_del IS '是否软删除';
|
||
|
||
--22-请求第三方接口
|
||
CREATE TABLE IF NOT EXISTS public.applet_requester(
|
||
id serial,
|
||
url varchar(256) default null,
|
||
param_map varchar(256) default null,
|
||
request_type integer default null,
|
||
type_description varchar(32) default null,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_requester_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_requester.id IS '自增id';
|
||
COMMENT ON COLUMN applet_requester.url IS 'url（请求服务地址)';
|
||
COMMENT ON COLUMN applet_requester.param_map IS '请求参数（值为空时，需要依赖实参传值。如小程序登录参数：{"appid":"","code":"","secret":"","grant_type":"authorization_code"}）';
|
||
COMMENT ON COLUMN applet_requester.request_type IS '请求类型(1001-1999)';
|
||
COMMENT ON COLUMN applet_requester.type_description IS '类别说明';
|
||
COMMENT ON COLUMN applet_requester.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_requester.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_requester.is_del IS '是否软删除';
|
||
|
||
--23-用户登录日志
|
||
CREATE TABLE IF NOT EXISTS public.applet_login_log(
|
||
id serial,
|
||
wx_nike varchar(32),
|
||
wx_openid varchar(32) DEFAULT NULL,
|
||
login_time timestamp default null,
|
||
ip varchar(32) default null,
|
||
area varchar(32) default null,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_login_log_pkey PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_login_log.id IS '自增id';
|
||
COMMENT ON COLUMN applet_login_log.wx_nike IS '微信昵称';
|
||
COMMENT ON COLUMN applet_login_log.wx_openid IS '微信openid';
|
||
COMMENT ON COLUMN applet_login_log.login_time IS '登录时间';
|
||
COMMENT ON COLUMN applet_login_log.ip IS 'ip地址';
|
||
COMMENT ON COLUMN applet_login_log.area IS '地区（省市）';
|
||
COMMENT ON COLUMN applet_login_log.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_login_log.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_login_log.is_del IS '是否软删除';
|
||
|
||
--24-自定义表头
|
||
CREATE TABLE IF NOT EXISTS public.applet_tb_head(
|
||
id serial,
|
||
table_name varchar(32),
|
||
table_code varchar(32) default null,
|
||
head_jason varchar(512) default null,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0,
|
||
CONSTRAINT applet_tb_head PRIMARY KEY (id)
|
||
);
|
||
COMMENT ON COLUMN applet_tb_head.id IS '自增id';
|
||
COMMENT ON COLUMN applet_tb_head.table_name IS '表名';
|
||
COMMENT ON COLUMN applet_tb_head.table_code IS '标识代码';
|
||
COMMENT ON COLUMN applet_tb_head.head_jason IS '表头Json';
|
||
COMMENT ON COLUMN applet_tb_head.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_tb_head.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_tb_head.is_del IS '是否软删除';
|
||
|
||
|
||
--25--消息表
|
||
CREATE TABLE IF NOT EXISTS public.applet_message(
|
||
id varchar(32),
|
||
type integer DEFAULT NULL,
|
||
type_content varchar(256)DEFAULT NULL,
|
||
truck_number varchar(32) DEFAULT NULL,
|
||
is_read integer DEFAULT NULL,
|
||
create_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
update_time timestamp(3) default ('now'::text)::timestamp(0) with time zone,
|
||
is_del integer DEFAULT 0
|
||
);
|
||
COMMENT ON COLUMN applet_message.id IS 'id';
|
||
COMMENT ON COLUMN applet_message.type IS '1-收车 2-提车';
|
||
COMMENT ON COLUMN applet_message.type_content IS '1-收车 2-提车';
|
||
COMMENT ON COLUMN applet_message.truck_number IS '板车号';
|
||
COMMENT ON COLUMN applet_message.is_read IS '0-未读 1-已读';
|
||
COMMENT ON COLUMN applet_message.create_time IS '创建时间';
|
||
COMMENT ON COLUMN applet_message.update_time IS '更新时间';
|
||
COMMENT ON COLUMN applet_message.is_del IS '是否软删除'; |