lccsw
94 lines
6.9 KiB
SQL
94 lines
6.9 KiB
SQL
-- ----------------------------
|
|
-- Title:行政区域表
|
|
-- Author:lcc
|
|
-- Table:lc_items
|
|
-- ---------------------------
|
|
drop table if exists lc_items;
|
|
create table lc_items (
|
|
id int(10) unsigned not null auto_increment comment '自增id',
|
|
brand_id int(10) unsigned not null default '0' comment '品牌id',
|
|
s_id int(10) unsigned not null default '0' comment '车系id',
|
|
v_id int(10) unsigned not null default '0' comment '车型id',
|
|
cor_id int(10) unsigned not null default '0' comment '车身颜色id',
|
|
incor_id int(10) unsigned not null default '0' comment '内饰颜色',
|
|
ori_price decimal(10,2) not null default '0.00' comment '指导价',
|
|
sale_price decimal(10,2) not null default '0.00' comment '售价',
|
|
bill_price decimal(10,2) not null default '0.00' comment '开票金额',
|
|
buy_price decimal(10,2) not null default '0.00' comment '采购成本',
|
|
dis_price decimal(10,2) not null default '0.00' comment '折扣价',
|
|
discount tinyint(3) unsigned not null default '0' comment '折扣百分比',
|
|
address varchar(100) not null default '' comment '送货地址',
|
|
vin varchar(100) not null default '' comment 'vin码',
|
|
frame_num varchar(100) not null default '' comment '车架号',
|
|
engine_num varchar(100) not null default '' comment '发动机号',
|
|
stdard_num varchar(100) not null default '' comment '合格证号',
|
|
bill_num varchar(100) not null default '' comment '开票号',
|
|
if_pack tinyint(1) unsigned not null default '0' comment '是否需要装包(0否 1是)',
|
|
status tinyint(1) not null default '1' comment '状态值(0下架 1正常 2已售)',
|
|
pro_time timestamp not null default '0000-00-00 00:00:00' comment '生产时间',
|
|
p_time timestamp not null default '0000-00-00 00:00:00' comment '分配时间',
|
|
out_time timestamp not null default '0000-00-00 00:00:00' comment '出库时间',
|
|
bill_time timestamp not null default '0000-00-00 00:00:00' comment '开票时间',
|
|
c_time int(10) not null default '0' comment '创建时间',
|
|
u_time timestamp not null default current_timestamp on update current_timestamp comment '更新时间',
|
|
primary key (id)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品表';
|
|
alter table lc_items drop column frame_num;
|
|
alter table lc_items add column addr_id int unsigned not null default 0 comment '存放地址ID' after address;
|
|
alter table lc_items add column in_time timestamp not null default '0000-00-00 00:00:00' comment '入库时间' after out_time;
|
|
alter table lc_items add company_id int(11) not null default 0 comment '公司id' after biz_id;
|
|
|
|
-- ----------------------------
|
|
-- Title:商品关联
|
|
-- Author:xusir
|
|
-- Table:lc_items_relate
|
|
-- ---------------------------
|
|
drop table if exists lc_items_relate;
|
|
create table lc_items_relate (
|
|
item_id int unsigned not null comment '商品id',
|
|
type varchar(50) not null default '0' comment '1-精品',
|
|
type_id int unsigned not null comment '类型ID',
|
|
status tinyint(1) not null default 1 comment '状态：-1删除，1正常',
|
|
primary key (item_id,type,type_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品关联';
|
|
|
|
-- ----------------------------
|
|
-- Title:商品成本表
|
|
-- Author:lcc
|
|
-- Table:lc_items_cost
|
|
-- Desc: cost_json(包含字段:select_price-选装成本,labor_price-选装工时费)
|
|
-- promotion_json(包含字段:sale_price-销售佣金,other-其他,factory_price-厂家补贴)
|
|
-- srv_json(包含字段:business_product-保险公司,business_price-商业险金额,business_fd-商业险返点,business_ins_price-商业险佣金,insurance_price-交强险金额,
|
|
-- insurance_fd-交强险返点,insurance_ins_price-交强险佣金,back_price-客户退点金额,fee_carno-挂牌收入,cb_fee_carno-挂牌成本,loan_product-按揭银行,
|
|
-- loan_price-按揭金额,loan_num-按揭期数,loan_srv_price-按揭服务费,loan_offset_price-解抵服务费,loan_subsidy_price-利息补贴收入,loan_in_price-其他收入金额,
|
|
-- loan_out_price-其他支出金额,commission_price-金融公司手续费收入,other_in_price-其他收入,other_out_price-其他支出,ori_price-指导价,if_pay-是否齐款(1是 0否))
|
|
-- ---------------------------
|
|
create table lc_items_cost (
|
|
id int(10) unsigned not null auto_increment comment '自增id',
|
|
item_id int(10) unsigned not null default '0' comment '商品id',
|
|
o_id int(10) unsigned not null default '0' comment '订单id',
|
|
price decimal(12,2) not null default '0.00' comment '实际售价(扣除优惠)',
|
|
bill_name varchar(128) not null default '' comment '开票方',
|
|
bill_time timestamp not null default '0000-00-00 00:00:00' comment '开票时间(发票上日期)',
|
|
bill_price decimal(12,2) not null default '0.00' comment '开票价(发票)',
|
|
sale_price decimal(12,2) not null default '0.00' comment '销售收入',
|
|
buy_price decimal(12,2) not null default '0.00' comment '采购及加装成本合计',
|
|
promotion_price decimal(12,2) not null default '0.00' comment '促销成本合计',
|
|
car_buy_price decimal(12,2) not null default '0.00' comment '整车采购成本',
|
|
car_profit_price decimal(12,2) not null default '0.00' comment '整车采购毛利',
|
|
insurance_price decimal(12,2) not null default '0.00' comment '保险收入合计',
|
|
fee_carno_price decimal(12,2) not null default '0.00' comment '挂牌收入合计',
|
|
loan_price decimal(12,2) not null default '0.00' comment '按揭收入合计',
|
|
srv_price decimal(12,2) not null default '0.00' comment '水平业务总毛利',
|
|
car_price decimal(12,2) not null default '0.00' comment '单车总毛利',
|
|
cost_json json default null comment '采购成本',
|
|
promotion_json json default null comment '促销成本',
|
|
srv_json json default null comment '水品业务',
|
|
c_time int(10) unsigned not null default '0' comment '创建时间',
|
|
u_time timestamp not null default current_timestamp on update current_timestamp comment '更新时间',
|
|
primary key (id)
|
|
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci comment='商品成本表'
|
|
alter table lc_items_cost add brand_car_price decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '品牌单车总毛利' after car_price;
|
|
alter table lc_items_cost add dl_car_price decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '直营店单车毛利/代理店单车毛利/合伙店采购毛利' after car_price;
|
|
|