@Wahson
2020-03-15T16:37:15.000000Z
字数 10369
阅读 848
周报
Wanbo周报
- crm迁移 F_Crm_V1.0
- 原子方法开发,模型见下文 曹佳林
- 公司列表,新增公司、基础信息页面、用料管理、银行管理、地址管理页面开发 陈均活
- 授信、三证模型确定 梁华生
- 上周优化项测试上线 梁华生
- 报价迁移测试 梁华生
- 运营系统优化 F_Order_V1.11
- 付款认领增加销售单号 陈均活
- 发送司机信息,增加手动触发模式 陈均活
- 批量查询物流指令 陈均活
- 库存单资金占用统计,库存供应商额度占用统计 陈均活 梁华生
create table company
(
id bigint not null primary key,
name varchar(100) not null comment '公司名称',
address varchar(100) not null comment '联系地址',
type smallint(2) default '1' not null comment '公司类型, 1: 采购商(purchaser); 2: 供应商(supplier)',
-- 状态待定
-- status smallint(2) default 10 not null comment '状态, 10:待审核(to_be_approve);20:审批通过(approved)',
city_code char(6) not null comment '城市代码',
city_name char(6) not null comment '城市名称',
in_blacklist smallint(1) default '0' not null comment '是否在黑名单, 0: 否(no); 1: 是(yes)',
is_deleted smallint(1) default '0' not null comment '是否已删除, 0: 否(no); 1: 是(yes)',
property smallint(1) not null comment '企业性质(非工商标准),1:国有非上市企业(nonStateOwnedEnterprises);2:国有上市企业(stateOwnedEnterprises);3:民营非上市企业(privateNonListedCompany);4:民营上市企业(privateListedCompany);5:中外合资(非港澳台)企业(jointVentures);6:港澳台合资企业(hkMacaoAndTaiwanJointVenture)',
business_property smallint(1) null comment '公司性质(工商标准), 1: 有限责任公司(limitedLiability); 2: 股份有限公司(colTd); 3: 个人独资企业(personalOwned); 4: 合伙企业(partnerShip)',
registered_capital decimal(13, 2) default '0.00' not null comment '注册资金(单位万元)',
paid_in_capital decimal(13, 2) default '0.00' not null comment '实收资本(单位万元)',
employee_cnt int(3) default 0 not null comment '员工人数',
main_business varchar(255) null comment '主营业务',
found_date date not null comment '成立日期',
business_term int(3) default 0 not null comment '营业期限,单位年',
-- 审核通过的公司这两字段必须有值
taxpayer_identity_no varchar(50) default null comment '纳税人识别号',
business_license_attachment varchar(128) default null comment '营业执照附件',
-- 领用人发生变化,都需要记录到公司领用记录表
allocated_by bigint default null comment '领用人',
allocated_at int default null comment '领用时间',
created_at timestamp default current_timestamp() not null,
created_by int default 0 not null,
updated_at timestamp default current_timestamp() not null,
updated_by int default 0 not null,
remark varchar(64) null comment '备注',
constraint name_UNIQUE
unique (name)
) engine = innoDb charset = utf8mb4 comment '公司表';
create table company_purchaser_property (
company_id bigint not null comment '公司id' primary key,
purchaser_type smallint default '1' not null comment '采购商类型,1:贸易商(trafficker); 2:次终端(second_terminal); 3:终端(terminal)',
purchaser_maturity smallint(1) default '0' not null comment '采购商成熟度,0:线索客户(clue_customer);1:高意向客户(high_intention_customer);2:成交客户(deal_customer)',
factory_type smallint(2) null comment '工厂类型, 1:改性厂(汽车、家电、功能改性)2:注塑厂(汽车/家电配件、工业零部件、物流周转用具、日用品、玩具)3:建材厂(管材、板材、型材、建筑装饰品及相关保护用品)4:日用五金家电厂
5:塑料纺织厂(编制袋、集装袋)6:化纤厂7:无纺布厂8:发泡厂9:中空制品厂10:普通吹膜厂11:CPP吹膜厂12:BOPP吹膜厂13:其他类型工厂',
other_factory_type_remark varchar(255) default '' null comment '其他工厂类型备注',
experience int(255) null comment '经营者经验,单位:年',
-- sales_form smallint(2) null comment '产品销售形式,1:70%以上产成品出口外销(percent70); 2:50%-70%产成品出口外销(percent50Percent70); 3:30%-50%产成品出口外销(percent30Percent50)',
factory_ownership smallint(1) null comment '厂房所有权,1:自购(buy);2:租用(rent)',
device_ownership smallint(1) null comment '设备所有权,1:自购(buy);2:租用(rent)',
real_estate_mortgage varchar(255) null comment '不动产抵押',
payment_cycle int null comment '下游客户回款周期,单位:天',
downstream_customer varchar(255) default null comment '主要下游客户',
product_category varchar(255) default null comment '产品类别,如: 注塑制品,吹塑制品,',
productions varchar(50) default null comment '生产产品, 如:购物袋',
device_cnt int default '0' null comment '生产设备数量',
device_total_value decimal(13,2) default null comment '生产设备价值,单位: 万元',
device_work_rate decimal(13,2) default null comment '设备开工率',
judicial_records varchar(255) default null comment '诉讼记录',
competitor_name varchar(50) null comment '同行竞争对手名称',
production_remark varchar(255) default null comment '生产信息备注',
device_type varchar(255) default null comment '设备类型; 具体值 待定',
factory_layout smallint(1) default null comment '工厂布局, 0: 集中(concentrate); 1: 分散(dispersion)',
factory_district varchar(50) default null comment '工厂区域',
factory_area varchar(255) default null comment '工厂面积',
annual_trading_amount decimal(13,2) null comment '年交易额,单位:万元, 对应company表的 year_trade',
annual_revenue decimal(13,2) default null comment '年营业收入',
created_at timestamp default current_timestamp() not null,
created_by int default 0 not null,
updated_at timestamp default current_timestamp() not null,
updated_by int default 0 not null,
remark varchar(64) null comment '备注',
constraint company foreign key (company_id) references company (id)
) engine = innoDb charset = utf8mb4 comment '采购商属性表';
create table company_material_record
(
id bigint not null auto_increment primary key,
company_id bigint not null comment '公司id',
product_id int not null,
category varchar(12) not null comment '产品分类',
designation varchar(32) not null comment '产品牌号',
manufacturer_id int not null comment '生产商ID',
manufacturer_name varchar(32) not null comment '生产商名称',
fare_increase decimal(13, 2) default '0.00' not null comment '加价幅度',
is_master_material smallint(1) default '0' not null comment '是否主用料, 0: 否(no); 1: 是(yes)',
is_deleted smallint(1) default '0' not null comment '是否已删除, 0: 否(no); 1: 是(yes)',
research_monthly_quantity decimal(13, 2) default '0.00' not null comment '调查月用量',
purchase_date varchar(64) default null comment '采购日期,1-31内,用,隔开',
is_subscribe_offer tinyint(1) default '1' not null comment '是否订阅报价推送',
created_at timestamp default current_timestamp() not null,
created_by int default 0 not null,
updated_at timestamp default current_timestamp() not null,
updated_by int default 0 not null,
remark varchar(255) null comment '备注',
constraint mr_2_company foreign key (company_id) references company (id)
) engine = innoDb
charset = utf8mb4 comment '公司用料记录';
create table company_address
(
id bigint not null auto_increment primary key,
company_id bigint not null comment '公司id',
is_default smallint(1) default '0' not null comment '是否默认地址, 0: 否(no); 1: 是(yes)',
is_deleted smallint(1) default '0' not null comment '是否已删除, 0: 否(no); 1: 是(yes)',
name varchar(20) not null comment '联系人',
phone varchar(20) not null comment '联系电话',
province_code varchar(50) not null comment '省份',
city_code char(6) not null comment '城市代码',
area_code varchar(9) not null comment '地址编码',
post_code varchar(10) null comment '邮编',
address varchar(128) not null comment '收货地址',
created_at timestamp default current_timestamp() not null,
created_by int default 0 not null,
updated_at timestamp default current_timestamp() not null,
updated_by int default 0 not null,
remark varchar(255) null comment '备注',
constraint address_2_company foreign key (company_id) references company (id)
) engine = innoDb
charset = utf8mb4 comment '公司收货地址';
create table company_bank
(
id bigint not null auto_increment primary key,
company_id bigint not null comment '公司id',
is_deleted smallint(1) default '0' not null comment '是否已删除, 0: 否(no); 1: 是(yes)',
bank_name varchar(64) not null comment '开户行',
bank_account varchar(32) not null comment '银行账号',
bank_account_name varchar(64) not null comment '银行户名',
created_at timestamp default current_timestamp() not null,
created_by int default 0 not null,
updated_at timestamp default current_timestamp() not null,
updated_by int default 0 not null,
constraint bank_2_company foreign key (company_id) references company (id),
remark varchar(255) null comment '备注'
) engine = innoDb
charset = utf8mb4 comment '公司银行';
create table company_allocate_record
(
id bigint not null auto_increment primary key ,
company_id bigint not null comment '公司id',
type smallint(2) not null default 1 comment '领用类型,1:领用(allocation);2:弃用(abandon)',
allocated_by int default 0 not null comment '领用人, 弃用:当前领用人,领用:新领用人',
created_at timestamp default current_timestamp() not null,
created_by int default 0 not null,
remark varchar(255) null comment '备注',
constraint vr_2_company foreign key (company_id) references company (id)
) engine = innoDb
charset = utf8mb4 comment '公司领用记录';
-- 以上表 增、删、改原子方法
-- 公司领用、弃用原子方法
-- 公司id 通过取号服务获取
-- listCompany 返回 TCompanyResp
-- listMaterialRecordsByCompanyId
-- listAddressByCompanyId
-- listBankByCompanyId
-- queryCompanyInvoice 返回 TCompanyResp
-- queryCompanyCredit 返回 TCompanyResp
-- queryCompanyPurchaserProperty 返回 TCompanyResp
-- 统一返回体
struct TCompanyResp {
1: TCompanyExt company,
2: list<TCompanyMaterialRecord> materialRecords
3: list<TCompanyAddress> addresses
4: list<TCompanyBank> banks,
5: optional TCompanyCredit credit,
6: optional TCompanyInvoice invoice,
7: optional TCompanyPurchaserProperty purchaserProperty
}
-- 以下待定
create table company_credit
(
company_id bigint not null comment '公司id' primary key,
status smallint(2) default 10 not null comment '状态, 10:待审核(to_be_approve);20:审批通过(approved)',
created_at timestamp default current_timestamp() not null,
created_by int default 0 not null,
updated_at timestamp default current_timestamp() not null,
updated_by int default 0 not null,
remark varchar(255) null comment '备注',
constraint credit_2_company foreign key (company_id) references company (id)
) engine = innoDb
charset = utf8mb4 comment '公司授信';
-- 待定, 报价数量基准 estimated_tonnage
create table company_material_info
(
id bigint not null primary key,
company_id bigint not null comment '公司id',
created_at timestamp default current_timestamp() not null,
created_by int default 0 not null,
updated_at timestamp default current_timestamp() not null,
updated_by int default 0 not null,
remark varchar(255) null comment '备注',
constraint mi_2_company foreign key (company_id) references company (id)
) engine = innoDb
charset = utf8mb4 comment '公司用料信息';
create table customer
(
id bigint not null primary key,
company_id bigint not null comment '公司id',
is_deleted smallint(1) default '0' not null comment '是否已删除, 0: 否(no); 1: 是(yes)',
wx_user_id bigint default null comment '微信用户id, wechat_db.wx_user.id',
created_at timestamp default current_timestamp() not null,
created_by int default 0 not null,
updated_at timestamp default current_timestamp() not null,
updated_by int default 0 not null,
remark varchar(255) null comment '备注',
constraint customer_2_company foreign key (company_id) references company (id)
) engine = innoDb
charset = utf8mb4 comment '客户表';
create table company_visit_record
(
id bigint not null auto_increment primary key,
company_id bigint not null comment '公司id',
created_at timestamp default current_timestamp() not null,
created_by int default 0 not null,
updated_at timestamp default current_timestamp() not null,
updated_by int default 0 not null,
remark varchar(255) null comment '备注',
constraint vr_2_company foreign key (company_id) references company (id)
) engine = innoDb
charset = utf8mb4 comment '公司回访记录';
-- 三证是否需要支持
create table company_invoice
(
company_id bigint not null comment '公司id' primary key,
status smallint(2) default 10 not null comment '状态, 10:待审核(to_be_approve);20:审批通过(approved);20:审批不通过(approved_fail)',
--
address varchar(200) not null comment '开票公司地址',
phone varchar(50) null comment '联系电话',
attachment_file_name int null comment '开票资料附件',
bank_name varchar(100) null comment '开户行名称',
bank_account varchar(30) null comment '银行卡号',
audited_by int default null comment '审核人',
audited_at timestamp default null comment '审核时间',
created_at timestamp default current_timestamp() not null,
created_by int default 0 not null,
updated_at timestamp default current_timestamp() not null,
updated_by int default 0 not null,
remark varchar(255) null comment '备注',
constraint invoice_2_company foreign key (company_id) references company (id)
) engine = innoDb
charset = utf8mb4 comment '公司开票信息';