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