platform-b2b/db/ddl.sql

-- 项目ddl SQL语句统一放在这里记录，包括表结构、关联关系、索引、函数、存储过程等

-- 每段sql语句必须要按规范注释，注释规范如下：
-- date: 2018-12-11
-- author: suntg
-- content: 内容备注xxx

-- date: 2018-12-12 17:51
-- author: hejq
-- content: 供应商关系表增加字段，需要对账金额，次更新时间，
-- 采用字符串是因为需要分币别统计处理
  alter table `purc$vendors`
  add column todo_apchek_count VARCHAR(100) default '' comment '需要对账金额',
  add column update_time datetime comment '上一次更新时间';

-- date: 2018-12-18 14:42
-- author: hejq
-- 更新purc$vendors 字段todo_apchek_count长度
  alter table purc$vendors modify todo_apchek_count varchar(200);

-- date: 2018-12-20 11:34
-- author: hejq
-- content: 对账明细增加单据来源时间
  ALTER TABLE purc$apcheckitem ADD COLUMN pai_source_date DATETIME COMMENT '单据来源时间';

-- date: 2018-12-20 11:53
-- author: hejq
-- content: 更新类型为采购验收单单据来源时间
  update purc$apcheckitem
  left join purc$acceptitem  on purc$apcheckitem.pai_sourcetable = 'purc$acceptitem' and purc$apcheckitem.pai_sourceid = purc$acceptitem.pai_id
  left join `purc$accept`  on purc$acceptitem.pai_paid = purc$accept.pa_id
  set purc$apcheckitem.pai_source_date = purc$accept.pa_date where purc$apcheckitem.pai_sourcetable is null;

-- date: 2018-12-20 13:38
-- author: hejq
-- content: 更新类型为货款调账单据来源时间
  update purc$apcheckitem
  left join purc$apbilladjustment on pai_sourcetable = 'purc$apbilladjustment' and pai_sourceid = aa_id
  set pai_source_date = aa_orderdate where pai_sourcetable = 'purc$apbilladjustment' and pai_source_date is null;

-- date: 2018-12-20 13:46
-- author: hejq
-- content: 更新类型为采购验退单单据来源时间
  update purc$apcheckitem
  left join purc$returnitem on pai_sourcetable = 'purc$returnitem' and pai_sourceid = pri_id
  left join `purc$return` on pri_prid = pr_id
  set pai_source_date = pr_date where pai_sourcetable = 'purc$returnitem' and pai_source_date is null;

-- date: 2018-12-20 13:49
-- author: hejq
-- content: 更新类型为不良品入库单单据来源时间
  update purc$apcheckitem
  left join purc$badinitem on pai_sourcetable = 'purc$badinitem' and pai_sourceid = pbi_id
  left join `purc$badin` on pbi_pbid = pb_id
  set pai_source_date = pb_date where pai_sourcetable = 'purc$badinitem' and pai_source_date is null;

-- date: 2018-12-20 13:53
-- author: hejq
-- content: 更新类型为不良品出库单单据来源时间
update purc$apcheckitem
  left join purc$badoutitem on pai_sourcetable = 'purc$badoutitem' and pai_sourceid = poi_id
  left join `purc$badout` on poi_poid = po_id
set pai_source_date = po_date where pai_sourcetable = 'purc$badoutitem' and pai_source_date is null;

-- date: 2018-12-20 13:57
-- author: hejq
-- content: 更新类型为委外验收单单据来源时间
update purc$apcheckitem
  left join make$acceptitem on pai_sourcetable = 'make$acceptitem' and pai_sourceid = mai_id
  left join `make$accept` on mai_paid = ma_id
set pai_source_date = ma_date where pai_sourcetable = 'make$acceptitem' and pai_source_date is null;

-- date: 2018-12-20 13:58
-- author: hejq
-- content: 更新类型为委外验退单单据来源时间
update purc$apcheckitem
  left join make$returnitem on pai_sourcetable = 'make$returnitem' and pai_sourceid = mri_id
  left join `make$return` on mri_paid = mr_id
set pai_source_date = mr_date where pai_sourcetable = 'make$returnitem' and pai_source_date is null;