create table  IF NOT EXISTS qhead_2013 as select * from qhead where FORMDATE<='2013-12-31';
create table  IF NOT EXISTS qhead_2014 as select * from qhead where FORMDATE>='2014-01-01' and FORMDATE<='2014-12-31';

delete from qhead where FORMDATE<='2014-12-31';

insert into qhead_2014 (
    TR,
    ID,
    QKIND,
    FORMNO,
    HANDNO,
    FORMDATE,
    CREATEDATETIME,
    ISCONFIRM,
    ISCHECK,
    ISLIST,
    EMPID,
    EMPNAME,
    USER,
    USERNAME,
    TRADETYPE,
    DEPTNO,
    ORDCASH,
    ORDCARD,
    CASH,
    CARD,
    ATOT,
    ORDDATE,
    FINEDATE,
    ASUM,
    BSUM,
    BILLRATE,
    COSTS,
    TURESUM,
    DISMONEY,
    BANKNO,
    BANKNAME,
    ATM,
    ATMDATE,
    TRCASH,
    PS,
    PS1,
    PS2
) 
select 'Y0',0,QKIND,MAX(FORMNO),'+0'
,'2013-12-31','131231235959','Y','N','','SYS','SYS',
USER,USERNAME,TRADETYPE,DEPTNO,sum(ORDCASH),sum(ORDCARD),sum(CASH),sum(CARD),sum(ATOT),
max(ORDDATE),max(FINEDATE),sum(ASUM),sum(BSUM),max(BILLRATE),sum(COSTS),0,sum(DISMONEY),
1,'AA0',sum(ATM),max(ATMDATE),sum(TRCASH),'','','' from qhead_2013 group byQKIND,USER,USERNAME,TRADETYPE,DEPTNO;


insert into qhead (
    TR,
    ID,
    QKIND,
    FORMNO,
    HANDNO,
    FORMDATE,
    CREATEDATETIME,
    ISCONFIRM,
    ISCHECK,
    ISLIST,
    EMPID,
    EMPNAME,
    USER,
    USERNAME,
    TRADETYPE,
    DEPTNO,
    ORDCASH,
    ORDCARD,
    CASH,
    CARD,
    ATOT,
    ORDDATE,
    FINEDATE,
    ASUM,
    BSUM,
    BILLRATE,
    COSTS,
    TURESUM,
    DISMONEY,
    BANKNO,
    BANKNAME 

) select 'Y0',0,QKIND,MAX(FORMNO),'+0'
,'2014-12-31','141231235959','Y','N','','SYS','SYS',
USER,USERNAME,TRADETYPE,DEPTNO,sum(ORDCASH),sum(ORDCARD),sum(CASH),sum(CARD),sum(ATOT),
max(ORDDATE),max(FINEDATE),sum(ASUM),sum(BSUM),max(BILLRATE),sum(COSTS),0,sum(DISMONEY),
1,'AA0',sum(ATM),max(ATMDATE),sum(TRCASH),'','','' from qhead_2014
 group by  QKIND,USER,USERNAME,TRADETYPE,DEPTNO;


create table  IF NOT EXISTS qitems_2013 as select * from qitems where FORMDATE<='2013-12-31';
create table  IF NOT EXISTS qitems_2014 as select * from qitems where FORMDATE>='2014-01-01' and FORMDATE<='2014-12-31';

delete from qitems where FORMDATE<='2014-12-31';


insert into qitems_2014 (
    TR,
    ID,
    QKIND,
    FORMNO,
    HANDNO,
    FORMDATE,
    CREATEDATETIME,
    ISCONFIRM,
    ISCHECK,
    EMPID,
    EMPNAME,
    USER,
    USERNAME,
    TRADETYPE,
    DEPTNO,
    GOODSNO,
    GOODSNOS,
    SOURCENO,
    UNIT1,
    UNIT2,
    UNIT,
    UNITPRICE,
    DISCOUNT,
    ACOST,
    RATE,
    RATEPRICE,
    REALSUM,
    SUBPRICE,
    P0,
    P1,
    P2,
    P3,
    P4,
    P5,
    COLOR,
    SIZE,
    ORDDATE,
    FINEDATE,
    BATCH,
    SUPPLY,
    GOODSTYPE,
    GOODSNAME,
    COLORS,
    SIZES,
    UNIT10,
    UNIT20,
    UNIT30,
    UNIT40,
    PIC,
    PS,
    SEASON,
    COUNTRY,
    YEAR 
) select 'Y0',0,QKIND,MAX(FORMNO),'+0'
,'2013-12-31','131231235959','Y','N','SYS','SYS',
USER,USERNAME,TRADETYPE,DEPTNO,GOODSNO,GOODSNOS,SOURCENO,UNIT1,UNIT2,sum(UNIT),UNITPRICE,100
,sum(ACOST),max(RATE),sum(RATEPRICE),sum(REALSUM),sum(SUBPRICE),max(P0),max(P1),max(P2)
,max(P3),max(P4),max(P5),max(COLOR),max(SIZE),max(ORDDATE),max(FINEDATE),max(BATCH),SUPPLY
,GOODSTYPE,max(GOODSNAME),max(COLORS),max(SIZES),sum(UNIT10),sum(UNIT20),sum(UNIT30),sum(UNIT40)
,max(PIC),'',SEASON,COUNTRY,YEAR
 from qitems_2013  WHERE GOODSNO in (select DISTINCT GOODSNO from qitems_2014) 
 group by  QKIND,USER,USERNAME,TRADETYPE,DEPTNO,GOODSNO,GOODSNOS,SOURCENO,UNIT1
 ,UNIT2,UNITPRICE,SUPPLY,GOODSTYPE,SEASON,COUNTRY,YEAR;


insert into qitems (
    TR,
    ID,
    QKIND,
    FORMNO,
    HANDNO,
    FORMDATE,
    CREATEDATETIME,
    ISCONFIRM,
    ISCHECK,
    EMPID,
    EMPNAME,
    USER,
    USERNAME,
    TRADETYPE,
    DEPTNO,
    GOODSNO,
    GOODSNOS,
    SOURCENO,
    UNIT1,
    UNIT2,
    UNIT,
    UNITPRICE,
    DISCOUNT,
    ACOST,
    RATE,
    RATEPRICE,
    REALSUM,
    SUBPRICE,
    P0,
    P1,
    P2,
    P3,
    P4,
    P5,
    COLOR,
    SIZE,
    ORDDATE,
    FINEDATE,
    BATCH,
    SUPPLY,
    GOODSTYPE,
    GOODSNAME,
    COLORS,
    SIZES,
    UNIT10,
    UNIT20,
    UNIT30,
    UNIT40,
    PIC,
    PS,
    SEASON,
    COUNTRY,
    YEAR 
) select 'Y0',0,QKIND,MAX(FORMNO),'+0'
,'2014-12-31','141231235959','Y','N','SYS','SYS',
USER,USERNAME,TRADETYPE,DEPTNO,GOODSNO,GOODSNOS,SOURCENO,UNIT1,UNIT2,sum(UNIT),UNITPRICE,100
,sum(ACOST),max(RATE),sum(RATEPRICE),sum(REALSUM),sum(SUBPRICE),max(P0),max(P1),max(P2)
,max(P3),max(P4),max(P5),max(COLOR),max(SIZE),max(ORDDATE),max(FINEDATE),max(BATCH),SUPPLY
,GOODSTYPE,max(GOODSNAME),max(COLORS),max(SIZES),sum(UNIT10),sum(UNIT20),sum(UNIT30),sum(UNIT40)
,max(PIC),'',SEASON,COUNTRY,YEAR
 from qitems_2014  WHERE GOODSNO in (select DISTINCT GOODSNO from qitems) 
 group by  QKIND,USER,USERNAME,TRADETYPE,DEPTNO,GOODSNO,GOODSNOS,SOURCENO,UNIT1
 ,UNIT2,UNITPRICE,SUPPLY,GOODSTYPE,SEASON,COUNTRY,YEAR;


create table  IF NOT EXISTS qdetail_2013 as select * from qdetail where GOODSNO in (select DISTINCT GOODSNO from qitems_2013);

create table  IF NOT EXISTS qdetail_2014 as select * from qdetail where GOODSNO in (select DISTINCT GOODSNO from qitems_2014);

delete from  qdetail where GOODSNO not in (select DISTINCT GOODSNO from qitems) and GOODSNO not in (select DISTINCT GOODSNO from qitems_2014) and CREATEDATETIME<'2015-01-01';