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-19 09:51
-- author: huj
-- content: 采购明细单增加替代料号、替代料名称、替代料规格三个字段（帝显LED物料需求）
  alter table `purc$orderitems`
  add column pd_repprodcode VARCHAR(255)  comment '替代料号',
  add column pd_repprodtitle VARCHAR(255)  comment '替代料名称',
  add column pd_repprodspec VARCHAR(255)  comment '替代料规格';

-- date: 2018-12-25 10:51
-- author: huj
-- content: 采购明细单增加旧料编号、旧料名称、旧料规格三个字段（帝显LED物料需求）
alter table `purc$orderitems`
  add column pd_prodoldcode VARCHAR(255)  comment '旧料编号',
  add column pd_prodoldtitle VARCHAR(255)  comment '旧料名称',
  add column pd_prodoldspec VARCHAR(255)  comment '旧料规格';


-- 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_source_date 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;

-- date: 2018-12-20 15:18
-- author: hejq
-- content: 1、 优软商城采购对接人为空，插入记录，对接人为陈艳平；
--          2、 优软商城采购对接人为18320952803（UU:2000012807）的更新为陈艳平
insert into sale$distribute (pd_distribute, pd_istransfer, pd_useruu, pd_vdid)
  select 1,0,1000004274,ve_id from `purc$vendors` where ve_myenuu = 10042875
and not exists (select 1 from sale$distribute where ve_id = pd_vdid);

update `sale$distribute` set pd_useruu = 1000004274 where pd_useruu = 2000012807 and
pd_vdid in (select ve_id from `purc$vendors` where ve_myenuu = 10042875);

-- date: 2018-12-25 9:10
-- author: hejq
-- content: 供应商客户关系表增加对账总额字段
alter table `purc$vendors`
  add column apcheck_count VARCHAR(200) default '' comment '对账总额（已对账和未对账之和）';

-- date: 2018-12-28 17:19
-- author: hejq
-- content: 更新发货提醒判断逻辑
-- 新增函数，返回可发货日期
CREATE DEFINER=`sa`@`%` FUNCTION `b2b_mysql_test`.`purc_notice_delivery`(v_pn_delivery date,
                                                                         v_pr_ltinstock int) RETURNS date
  begin

    if v_pr_ltinstock = -1 then
      -- 	这里设置减去两个月最后一天再加上26天即指定月份的26号
      return last_day(v_pn_delivery) + interval (-2) month + interval (26) day;
    else
      return v_pn_delivery - interval (v_pr_ltinstock + 1) day;
    end if;
  end;
-- 更新判断方法，根据生成的可发货日期与当前日期比较，大于或等于可以发货，其他状态不能发货
CREATE DEFINER=`sa`@`%` FUNCTION `b2b_mysql_test`.`PURC_NOTICE_WAIT_TO_SEND`(v_pn_delivery date,
    v_pr_ltinstock int) RETURNS smallint(6)
begin
-- 	可以发货的时间
declare enable_delivery date;

set enable_delivery := purc_notice_delivery(v_pn_delivery, coalesce(v_pr_ltinstock, 0));

if to_days(current_date()) - to_days(enable_delivery) >= 0 then
return 0;
else
return 1;
end if;
end


-- date: 2019-01-02 18:51
-- author: huj
-- content: ERP主动收料通知明细id
alter table `sale$senditem`
  add column si_sourceid bigint(20)  comment 'ERP主动收料通知明细id';

-- date: 2019-01-02 18:51
-- author: huj
-- content: ERP主动收料通知id
alter table `sale$send`
  add column ss_sourceid bigint(20)  comment 'ERP主动收料通知明细id';

alter table `purc$acceptitem`
  add column b2b_si_id bigint(20)  comment '送货单明细id';
end;

-- date: 2019-01-03 13:40
-- author: hejq
-- content: 出入库单据添加字段收款金额，收款日期
-- 采购验收单
alter table `purc$acceptitem`
  add column pai_payment double comment '付款金额',
  add column pai_paydate datetime comment '付款日期';
-- 采购验退单
alter table `purc$returnitem`
  add column pri_payment double comment '付款金额',
  add column pri_paydate datetime comment '付款日期';
-- 不良品入库单
alter table `purc$badinitem`
  add column pbi_payment double comment '付款金额',
  add column pbi_paydate datetime comment '付款日期';
-- 不良品出库单
alter table `purc$badoutitem`
  add column poi_payment double comment '付款金额',
  add column poi_paydate datetime comment '付款日期';
-- 委外验收单
alter table `make$acceptitem`
  add column mai_payment double comment '付款金额',
  add column mai_paydate datetime comment '付款日期';
-- 委外验退单
alter table `make$returnitem`
  add column mri_payment double comment '付款金额',
  add column mri_paydate datetime comment '付款日期';
-- 货款调账
alter table `purc$apbilladjustment`
  add column aa_payment double comment '付款金额',
  add column aa_paydate datetime comment '付款日期';

-- date: 2019-01-03 15:48
-- author: hejq
-- content: 增加出入库单付款数据更新传输记录
create table `prodiopay` (
  `pi_id` bigint(20) not null auto_increment,
  `pi_code` varchar(100) default null COMMENT '出入库单号',
  `pi_class` varchar(100) default null COMMENT '出库单类型',
  `pi_detno` smallint(6) default null COMMENT '明细序号',
  `pi_enuu` bigint(20) default null COMMENT '录入企业UU',
  `pi_amount` double default null COMMENT '付款金额',
  `pi_source_date` datetime default null COMMENT '付款日期',
  `pi_source_id` bigint(20) default null COMMENT 'erp明细id',
  `pi_date` datetime default null COMMENT '录入日期',
  primary key (`pi_id`)
) ENGINE=InnoDB auto_increment=1 default CHARSET=utf8;

-- date: 2019-01-04 9:49
-- author: hejq
-- content: 对账明细增加付款金额
alter table purc$apcheckitem add column pai_payment double COMMENT '付款金额';

-- date: 2019-01-04 13:59
-- author: hejq
-- content: 对账单明细增加送货明细id,送货数量和单价
alter table purc$apcheckitem
  add column pai_send_id BIGINT COMMENT '送货单明细id',
  add column pai_send_qty DOUBLE COMMENT '送货数量',
  add column pai_send_price DOUBLE COMMENT '送货单价';

-- date: 2019-01-15 16:51
-- author: huj
-- content: 漳州万利达需求新增物料承认状态
alter table `purc$orderitems`
  add column pd_prmaterial VARCHAR(255)  comment '物料承认状态';