-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `invs`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`invs`@`%` PROCEDURE `calBuyPlan` ()   BEGIN

declare cal_y int ;
declare M int;
declare Y int;
declare em3 char(2);
declare sm3 VARCHAR(2);
declare sy3 char(4);
declare Dept varchar(6); 
declare emy3 VARCHAR(6);
declare sym3 VARCHAR(6);
declare ey2 CHAR(4);
declare sy2 CHAR(4);
declare ey1 CHAR(4);
declare sy1 CHAR(4);
declare eym2 char(6);
declare sym2 char(6);
declare eym1 char(6);
declare sym1 char(6);


set Dept='BAY01' ;
set Y=2018 ;
set M='5' ;
set cal_y=2019 ;


set sm3 = M+1 ;



if sm3 = 13 then set sm3=1 ;
end if ;
if sm3 < 10 then set sm3 =concat('0',sm3) ;
end if;


set sy3=Y-1 ;
set ey2=sy3 ;
set sy2=Y-2 ;
set ey1=sy2 ;
set sy1=Y-3 ;

if M=12  then

  set sy3=Y ;
  set ey2=Y-1 ;
  set sy2=ey2 ;
  set ey1=Y-2 ;
  set sy1=ey1  ;

END if ;



set em3=M ;
if M < 10 then set em3 =concat('0',convert(M,char)) ; end if ;
set emy3=concat(convert(Y,char),em3) ;



set sym3=concat(sy3,'10') ;
set eym2=concat(ey2,'09') ;
set sym2=concat(sy2,'10') ;
set eym1=concat(ey1,'09') ;
set sym1=concat(sy1,'10') ;




/*insert INTO BUYPLAN_C (DEPT_ID,YEAR,WORKING_CODE,CAL_MONTH,RATE_1_YEAR,RATE_2_YEAR,RATE_3_YEAR,AVG_3_YEAR,QTY_ON_HAND,PACK_RATIO,MIN_LEVEL,QTY_THIS_YEAR,LAST_UPDATE,PACK_UNIT_COST)*/
insert INTO BUYPLAN_C (DEPT_ID,`YEAR`,WORKING_CODE,CAL_MONTH,RATE_1_YEAR,RATE_2_YEAR,RATE_3_YEAR,QTY_ON_HAND,PACK_RATIO,LAST_UPDATE,PACK_UNIT_COST,FORECAST)


select Dept as DEPT_ID,cal_y as 'YEAR',m.WORKING_CODE,M as 'CAL_MONTH',
SUM(CASE when left(m.R_S_DATE,6) BETWEEN sym1 AND eym1  then IFnull(m.ACTIVE_QTY,0) else 0 end) as RATE_1_YEAR,
SUM(CASE when left(m.R_S_DATE,6) BETWEEN sym2 AND eym2  then IFnull(m.ACTIVE_QTY,0) else 0 end) as RATE_2_YEAR,
(SUM(CASE when left(m.R_S_DATE,6) BETWEEN sym3 AND  emy3  then IFnull(m.ACTIVE_QTY,0) else 0 end))*12/(PERIOD_DIFF(emy3,sym3)+1) as RATE_3_YEAR,
d.QTY_ON_HAND,

ifnull((select  v1.LAST_PACK_RATIO from  DRUG_VN v1  where v1.WORKING_CODE=m.WORKING_CODE  and v1.LAST_BUY_COST > 0 order by v1.LAST_BUY DESC limit 1),
(select  CARD.ACTIVE_PACK from CARD where WORKING_CODE=m.WORKING_CODE  and R_S_STATUS='R' and CARD.ACTIVE_PACK > 0
order by R_S_DATE DESC limit 1)
) as PACK_RATIO,


CURRENT_TIMESTAMP() as LAST_UPDATE,
ifnull((select  c.BUY_UNIT_COST from MS_IVO_C c left join MS_IVO i on (c.RECEIVE_NO = i.RECEIVE_NO) WHERE c.WORKING_CODE=m.WORKING_CODE order by i.RECEIVE_DATE DESC limit 1  )
,ifnull((select v.LAST_BUY_COST from DRUG_VN v  where v.WORKING_CODE=m.WORKING_CODE order by v.LAST_BUY DESC limit 1),
(select  g1.LAST_BUY_COST from DRUG_GN g1 where g1.WORKING_CODE=m.WORKING_CODE
 limit 1 )
))as PACK_UNIT_COST,d.FORECAST
from  

(
select c.WORKING_CODE,c.R_S_DATE,c.STOCK_ID,
CASE when c.R_S_STATUS in ('T','S')  then c.ACTIVE_QTY
when (c.R_S_STATUS='C') then c.ACTIVE_QTY*-1 else 0 end as ACTIVE_QTY
from CARD c   
where left(c.R_S_DATE,6) between sym1 and  emy3
and c.STOCK_ID=Dept
and c.WORKING_CODE is not null and c.WORKING_CODE <>''
and exists(select * from DRUG_GN gn1 where  gn1.WORKING_CODE=c.WORKING_CODE )
and c.R_S_STATUS in ('T','S','C')
) as m 

left join INV_MD d  on (d.DEPT_ID=m.STOCK_ID and d.FIRST_PACK_RATIO is not null and d.WORKING_CODE=m.WORKING_CODE)
group by m.WORKING_CODE,d.FIRST_PACK_RATIO,d.QTY_ON_HAND,d.FORECAST;



update BUYPLAN_C set AVG_3_YEAR=ROUND((RATE_1_YEAR
+RATE_2_YEAR
+RATE_3_YEAR)/(IF(RATE_1_YEAR <=0 , 0 , 1 )+IF( RATE_2_YEAR <=0 , 0 , 1 )+IF( RATE_3_YEAR <=0 , 0 , 1 )),0)

where
(IF(RATE_1_YEAR <=0 , 0 , 1 )+IF( RATE_2_YEAR <=0 , 0 , 1 )+IF( RATE_3_YEAR <=0 , 0 , 1 )) > 0
and `YEAR`=cal_y and DEPT_ID=Dept ;


update BUYPLAN_C set QTY_PRE_PLAN=AVG_3_YEAR+ROUND((RATE_3_YEAR/12),0)-QTY_ON_HAND
where `YEAR`=cal_y and DEPT_ID=Dept ;

update BUYPLAN_C set QTY_PRE_PLAN=0
where `YEAR`=cal_y and DEPT_ID=Dept  and QTY_PRE_PLAN < 0 ;

update BUYPLAN_C set QTY_FORECAST=QTY_PRE_PLAN*ifnull(FORECAST,1)
where `YEAR`=cal_y and DEPT_ID=Dept ;

update BUYPLAN_C set VALUE_FREE_Y3 = 
     (select 
      SUM(cx.`VALUE`) 
      from CARD cx 
      where cx.STOCK_ID =BUYPLAN_C.DEPT_ID 
      and cx.R_S_STATUS in ('R','A')
      and cx.FREE_FLAG='Y'
      and cx.WORKING_CODE=BUYPLAN_C.WORKING_CODE
      and left(cx.R_S_DATE,6) BETWEEN sym3 AND  emy3)
where  BUYPLAN_C.`YEAR`= cal_y and BUYPLAN_C.DEPT_ID=Dept;


update BUYPLAN_C set QTY_FREE_Y3 =
     (select 
      SUM(cx.ACTIVE_QTY) 
      from CARD cx 
      where cx.STOCK_ID =BUYPLAN_C.DEPT_ID 
      and cx.R_S_STATUS in ('R','A')
      and cx.FREE_FLAG='Y'
      and cx.WORKING_CODE=BUYPLAN_C.WORKING_CODE
      and left(cx.R_S_DATE,6) BETWEEN sym3 AND  emy3)
where  BUYPLAN_C.`YEAR`= cal_y and BUYPLAN_C.DEPT_ID=Dept;


update BUYPLAN_C set QTY_THIS_YEAR=(ifnull(QTY_PRE_PLAN,0) + ifnull(QTY_FORECAST,0)- ifnull(QTY_FREE_Y3,0))/ifnull(PACK_RATIO,1)
where `YEAR`=cal_y and DEPT_ID=Dept;

update BUYPLAN_C set QTY_THIS_YEAR= floor((QTY_THIS_YEAR+ 9) / 10) * 10     
where `YEAR`=cal_y and DEPT_ID=Dept;

update BUYPLAN_C set QTY_THIS_YEAR= QTY_THIS_YEAR + 10
where `YEAR`=cal_y and DEPT_ID=Dept  and  (convert(QTY_THIS_YEAR,decimal)%4 > 0 ) ;

update BUYPLAN_C set QTY_THIS_YEAR= QTY_THIS_YEAR*ifnull(PACK_RATIO,1)
where `YEAR`=cal_y and DEPT_ID=Dept  ;

update BUYPLAN_C set QTY_THIS_YEAR= 0
where `YEAR`=cal_y and DEPT_ID=Dept and QTY_THIS_YEAR < 0   ;

update BUYPLAN_C set MIN_LEVEL=ROUND((QTY_THIS_YEAR/12),0)
where `YEAR`=cal_y and DEPT_ID=Dept;

update BUYPLAN_C set VALUE_THIS_YEAR=ROUND(ifnull(QTY_THIS_YEAR/ifnull(PACK_RATIO,1),0)*ifnull(PACK_UNIT_COST,0),0),
REMAIN_QTY=QTY_THIS_YEAR
where `YEAR`=cal_y and DEPT_ID=Dept ;

update BUYPLAN_C set VALUE_DEFENCE=VALUE_THIS_YEAR
where `YEAR`=cal_y and DEPT_ID=Dept ;

/*
update BUYPLAN_C set VALUE_DEFENCE = VALUE_THIS_YEAR - ifnull(VALUE_FREE_Y3,0),REMAIN_VALUE=VALUE_THIS_YEAR - ifnull(VALUE_FREE_Y3,0)
where  BUYPLAN_C.`YEAR`= cal_y and BUYPLAN_C.DEPT_ID=Dept;
*/

update BUYPLAN_C set TRIMESTER1= VALUE_DEFENCE/4,TRIMESTER2=VALUE_DEFENCE/4,TRIMESTER3= VALUE_DEFENCE/4,TRIMESTER4= VALUE_DEFENCE/4,
RM_TRIMES1= VALUE_DEFENCE/4,RM_TRIMES2= VALUE_DEFENCE/4,RM_TRIMES3= VALUE_DEFENCE/4,RM_TRIMES4= VALUE_DEFENCE/4
where YEAR=cal_y and DEPT_ID=Dept;



update BUYPLAN_C,
(
select T2.WORKING_CODE,T2.TRI1/TRI_ALL as R1,
T2.TRI2/TRI_ALL as R2,
T2.TRI3/TRI_ALL as R3,
T2.TRI4/TRI_ALL as R4
from
(select T.WORKING_CODE,
SUM(CASE when T.M  in ('10','11','12') then T.QTY else 0 end) as TRI1,
SUM(CASE when T.M  in ('01','02','03') then T.QTY else 0 end) as TRI2,
SUM(CASE when T.M  in ('04','05','06') then T.QTY else 0 end) as TRI3,
SUM(CASE when T.M  in ('07','08','09') then T.QTY else 0 end) as TRI4,
SUM(T.QTY) as TRI_ALL
from
(select c.WORKING_CODE, substring(c.R_S_DATE,5,2) as M,
CASE when c.R_S_STATUS in ('T','S')  then c.ACTIVE_QTY
when (c.R_S_STATUS='C') then c.ACTIVE_QTY*-1 else 0 end as QTY
from CARD c 
where c.STOCK_ID=Dept
and left(c.R_S_DATE,6) between sym3  and emy3 
and c.R_S_STATUS in ('T','S','C')
) as T  
group by T.WORKING_CODE
) as T2
WHERE T2.TRI_ALL > 0
) as T3
set BUYPLAN_C.DISP_RATIO1 = T3.R1,BUYPLAN_C.DISP_RATIO2 = T3.R2,BUYPLAN_C.DISP_RATIO3 = T3.R3,BUYPLAN_C.DISP_RATIO4 = T3.R4
where BUYPLAN_C.WORKING_CODE=T3.WORKING_CODE
and BUYPLAN_C.`YEAR`= cal_y and BUYPLAN_C.DEPT_ID=Dept;

update BUYPLAN_C set BUYMET_CODE = 0
where YEAR=cal_y and  DEPT_ID=Dept ;

update BUYPLAN_C set BUYMET_CODE = (select  BUYMET_CODE from BUYMETHOD 
where   MIN_AMT <= BUYPLAN_C.VALUE_DEFENCE and   MAX_AMT >= BUYPLAN_C.VALUE_DEFENCE  and HIDE = 'N' limit 1)
where YEAR=cal_y and  DEPT_ID=Dept  and BUYPLAN_C.VALUE_DEFENCE > 0 ;

END$$

CREATE DEFINER=`rangae`@`%` PROCEDURE `drugVN` ()   BEGIN
  if 'N' = 'Y' then

     if LENGTH( NULL ) >= 13  then

       select  g.WORKING_CODE,g.DRUG_NAME,v.TRADE_CODE,v.TRADE_NAME,c.COMPANY_NAME as MANUFAC_NAME,  g.SALE_UNIT_ID,
        v.MANUFAC_CODE,g.IS_ED,g.COLOR,(select ifnull(p.PACK_CODE,'')
        from PACK_RATIO p   where p.BAR_CODE=NULL ) as PACK_CODE,h.PROD_TYPE,
        d.DFORM_NAME,u.SALE_UNIT
        from DRUG_GN g
        left join DRUG_VN v on (g.WORKING_CODE=v.WORKING_CODE)
        left join COMPANY c on (v.MANUFAC_CODE=convert(c.COMPANY_CODE,char))
        left join DOSAGE_FORM d on (d.DFORM_ID = g.DFORM_ID)
        left join SALE_UNIT u on (u.SU_ID = g.SALE_UNIT_ID)
        left join HOSP_LIST h  on (h.HOSP_LIST_CODE = g.HOSP_LIST)
        where
        (
         v.TRADE_CODE=(select p.TRADE_CODE from PACK_RATIO p   where p.BAR_CODE=NULL limit 1 )
        )
        and v.MOD_SYS='MED'
        and IFNULL(v.HIDE,'N')='N'
        and IFNULL(g.HIDE,'N')='N'
        order by  v.LAST_BUY DESC,g.DRUG_NAME ASC,v.TRADE_NAME  ASC  limit 20 ;

 
     else
   

        select  g.WORKING_CODE,g.DRUG_NAME,v.TRADE_CODE,v.TRADE_NAME,c.COMPANY_NAME as MANUFAC_NAME, g.SALE_UNIT_ID,
        v.MANUFAC_CODE,g.IS_ED,g.COLOR,0 as PACK_CODE,h.PROD_TYPE,
        d.DFORM_NAME,u.SALE_UNIT
        from DRUG_GN g  
        left join DRUG_VN v on (g.WORKING_CODE=v.WORKING_CODE)
        left join COMPANY c on (v.MANUFAC_CODE=convert(c.COMPANY_CODE,char))
        left join DOSAGE_FORM d on (d.DFORM_ID = g.DFORM_ID)
        left join SALE_UNIT u on (u.SU_ID = g.SALE_UNIT_ID)
        left join HOSP_LIST h  on (h.HOSP_LIST_CODE = g.HOSP_LIST)
        where
        (
        g.WORKING_CODE=NULL  or v.TRADE_CODE=NULL
        )
        and v.MOD_SYS='MED'
        and IFNULL(v.HIDE,'N')='N'
        and IFNULL(g.HIDE,'N')='N'
        order by  v.LAST_BUY DESC,g.DRUG_NAME ASC,v.TRADE_NAME  ASC    limit 20 ;
    
     end if;

  else

    select  g.WORKING_CODE,g.DRUG_NAME,v.TRADE_CODE,v.TRADE_NAME,c.COMPANY_NAME as MANUFAC_NAME,g.SALE_UNIT_ID,
    v.MANUFAC_CODE,g.IS_ED,g.COLOR,0 as PACK_CODE,h.PROD_TYPE,
    d.DFORM_NAME,u.SALE_UNIT
    from DRUG_GN g  
    left join DRUG_VN v   on (g.WORKING_CODE=v.WORKING_CODE)
    left join COMPANY c   on (v.MANUFAC_CODE=convert(c.COMPANY_CODE,char))
    left join DOSAGE_FORM d on (d.DFORM_ID = g.DFORM_ID)
    left join SALE_UNIT u on (u.SU_ID = g.SALE_UNIT_ID)
    left join HOSP_LIST h  on (h.HOSP_LIST_CODE = g.HOSP_LIST)
    where 
    (
    g.WORKING_CODE = 'ราง' 
    or upper(g.DRUG_NAME) like '%ราง%' 
    or upper(v.TRADE_NAME) like '%ราง%'
    or g.WORKING_CODE in (select distinct REF_CODE from INST_NAME  where INST_NAME like 'ราง%' )
    or v.TRADE_CODE in (select distinct REF_CODE from INST_NAME  where INST_NAME like 'ราง%' )
    )
    and v.MOD_SYS='MED'
    and IFNULL(v.HIDE,'N')='N'
    and IFNULL(g.HIDE,'N')='N'
    order by  v.LAST_BUY DESC,g.DRUG_NAME ASC,v.TRADE_NAME  ASC  limit 20 ;

  end if ;
END$$

CREATE DEFINER=`dlict`@`%` PROCEDURE `insert_data_invs` ()   BEGIN

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id)
select 'INVS','menuPreRCV', '', 'Y', '2', '65' from menucon
where not EXISTS(select * from menucon where menu='menuPreRCV') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id) 
select 'INVS','menuDocFlow', 'รับส่งเอกสารด้วยบาร์โค้ด', 'Y', '5', '69'  from menucon
where not EXISTS(select * from menucon where menu='menuDocFlow') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id) 
select 'INVS','menuAddInPrograms', 'เพิ่ม/ลบ โปรแกรมเสริม', 'Y', '0', '70'  from menucon
where not EXISTS(select * from menucon where menu='menuAddInPrograms') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id) 
select 'INVS','menuInvReturn', 'รับยาคืน', 'Y', '2', '71'  from menucon
where not EXISTS(select * from menucon where menu='menuInvReturn') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id) 
select 'INVS','menuDistX', 'จ่ายยาด่วน', 'Y', '2', '72'  from menucon
where not EXISTS(select * from menucon where menu='menuDistX') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id) 
select 'INVS','menuDisType', 'ประเภทเบิกจ่าย', 'Y', '0', '73'  from menucon
where not EXISTS(select * from menucon where menu='menuDisType') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id) 
select 'INVS','menuRtnReas', 'เหตุผลยาคืน', 'Y', '0', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='menuRtnReas') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id) 
select 'INVS','menuRtnAct', 'แผนดำเนินการ', 'Y', '0', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='menuRtnAct') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id) 
select 'INVS','canSetEPO', 'ตั้งค่าการส่งอีเมล์สั่งซื้อ', 'Y', '1', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='canSetEPO') limit 1 ;


INSERT INTO menucon  (module,menu,des,`Enable`,parent,id) 
select 'INVS','menuReqList', 'ตั้งค่าบัญชีขอเบิก', 'Y', '2', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='menuReqList') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id) 
select 'INVS','menuGFMIS', 'ข้อมูล GFMIS', 'Y', '1', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='menuGFMIS') limit 1 ;


INSERT INTO menucon  (module,menu,des,`Enable`,parent,id)
select 'INVS','CanAdjOnhand', 'ปรับจำนวนคงคลัง', 'Y', '0', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='CanAdjOnhand') limit 1 ;


INSERT INTO menucon  (module,menu,des,`Enable`,parent,id)
select 'INVS','AppBuyPlan', 'ยืนยันแผน/เปิดแก้ไข', 'Y', '0', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='AppBuyPlan') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id)
select 'INVS','menuSumInv', 'ประมวลผลข้อมูลบริหารคลัง', 'Y', '0', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='menuSumInv') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id)
select 'INVS','setUserSock', 'ตั้งค่า INVS Client', 'Y', '0', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='setUserSock') limit 1 ;


INSERT INTO menucon  (module,menu,des,`Enable`,parent,id)
select 'INVS','menuCancelPO', 'เหตุผลยกเลิกซื้อ', 'Y', '0', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='menuCancelPO') limit 1 ;

INSERT INTO menucon  (module,menu,des,`Enable`,parent,id)
select 'INVS','canSetMinMAX', 'ตั้งค่าระบบ Min-Max ได้', 'Y', '0', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='canSetMinMAX') limit 1 ;

INSERT INTO `dept_id`(DEPT_ID,DEPT_NAME,DEPT_TYPE,MOD_SYS,HIDE)
SELECT 'PT01' , 'จ่ายผู้ป่วย','4', 'MED','N' FROM DEPT_ID
where not EXISTS(select * from dept_id where DEPT_ID='PT01') limit 1 ;


INSERT INTO menucon  (module,menu,des,`Enable`,parent,id)
select 'INVS','ChangeSTC', 'เปลี่ยนคลังในหน้า stock card', 'Y', '0', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='ChangeSTC') limit 1 ;


INSERT INTO menucon  (module,menu,des,`Enable`,parent,id)
select 'INVS','CheckMapHIS', 'ตรวจสอบการจับคู่รหัสยา HIS', 'Y', '0', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='CheckMapHIS') limit 1 ;


INSERT INTO menucon  (module,menu,des,`Enable`,parent,id)
select 'INVS','PrintOrder', 'พิมพ์ใบสั่งซื้อ', 'Y', '1', (select max(id)+1 from menucon)  from menucon
where not EXISTS(select * from menucon where menu='PrintOrder') limit 1 ;


  IF (SELECT COUNT(*) FROM exchange_info) = 0 THEN
    INSERT INTO `exchange_info` VALUES ('0', '', 'N');
    INSERT INTO `exchange_info` VALUES ('1', 'แลกเปลี่ยนไม่ได้', 'N');
    INSERT INTO `exchange_info` VALUES ('2', 'แลกเปลี่ยนได้หลังหมดอายุ', 'N');
    INSERT INTO `exchange_info` VALUES ('3', 'แลกเปลี่ยนได้อายุ > 3 เดือน', 'N');
    INSERT INTO `exchange_info` VALUES ('4', 'แลกเปลี่ยนได้อายุ > 6 เดือน', 'N');
    INSERT INTO `exchange_info` VALUES ('5', 'แลกเปลี่ยนได้อายุ > 12 เดือน', 'N');
  END IF;

END$$

CREATE DEFINER=`sa`@`%` PROCEDURE `SearchDrugItem` ()   BEGIN

  if 'N' = 'Y' then

     if LENGTH( NULL ) >= 13  then

      select  DISTINCT  i.*,d.DRUG_NAME,s.SALE_UNIT,d.IS_ED,d.USE_TRADE_DIS,
      IFNULL((select px.PACK_RATIO from PACK_RATIO px
      where px.WORKING_CODE=i.WORKING_CODE  and px.PACK_RATIO is not NULL  and px.BAR_CODE=NULL order by  LAST_BUY DESC limit 1),IFNULL(i.FIRST_PACK_RATIO,IFNULL(i.LAST_PACK_RATIO,1))) as PACK_RATIO,
      ifnull(d.COLOR,16777215) as COLOR
      from DRUG_GN d
      left join INV_MD i  on (i.WORKING_CODE=d.WORKING_CODE)
      left join SALE_UNIT s on (d.SALE_UNIT_ID=s.SU_ID)
      where
      i.WORKING_CODE=(select   WORKING_CODE from PACK_RATIO where BAR_CODE=NULL limit 1 )
      and i.DEPT_ID='STOCK1'
      and (i.HIDE='N' or i.HIDE='' or i.HIDE is null)
      and exists(select x.WORKING_CODE from INV_SITE x where x.WORKING_CODE=i.WORKING_CODE and x.DEPT_ID='DIS01' )
      order by d.DRUG_NAME ;

 
     else
   

      select  DISTINCT  i.*,d.DRUG_NAME,s.SALE_UNIT,d.IS_ED,d.USE_TRADE_DIS,
      CASE when  (i.FIRST_PACK_RATIO is null) then
      (select  px.PACK_RATIO from PACK_RATIO px
      where px.WORKING_CODE=i.WORKING_CODE  and px.PACK_RATIO is not NULL order by  LAST_BUY DESC limit 1 ) else
      i.FIRST_PACK_RATIO end as PACK_RATIO,ifnull(d.COLOR,16777215) as COLOR
      from  DRUG_GN d
      left join INV_MD i   on (i.WORKING_CODE=d.WORKING_CODE)
      left join SALE_UNIT s on (d.SALE_UNIT_ID=s.SU_ID)
      where
      (i.WORKING_CODE='8370'
       or
       i.WORKING_CODE= (select  WORKING_CODE from DRUG_VN where TRADE_CODE='8370' limit 1  )
       )
      and i.DEPT_ID='STOCK1'
      and (i.HIDE='N' or i.HIDE='' or i.HIDE is null)
      and exists(select x.WORKING_CODE from INV_SITE x where x.WORKING_CODE=i.WORKING_CODE and x.DEPT_ID='DIS01' )
      order by d.DRUG_NAME ;
    
     end if;

  else

    select  DISTINCT  i.*,d.DRUG_NAME,s.SALE_UNIT,d.IS_ED,d.USE_TRADE_DIS,
    CASE when  (i.FIRST_PACK_RATIO is null) then
    (select  px.PACK_RATIO from PACK_RATIO px 
    where px.WORKING_CODE=i.WORKING_CODE  and px.PACK_RATIO is not NULL order by  LAST_BUY DESC limit 1 ) else
    i.FIRST_PACK_RATIO end as PACK_RATIO,ifnull(d.COLOR,16777215) as COLOR
    from DRUG_GN d
    left join INV_MD i  on (i.WORKING_CODE=d.WORKING_CODE)
    left join SALE_UNIT s on (d.SALE_UNIT_ID=s.SU_ID)
    where
    ( d.WORKING_CODE='osel'
    or upper(d.DRUG_NAME) like '%OSEL%' 
    or i.WORKING_CODE in (select distinct t.REF_CODE from INST_NAME  t  
    where t.INST_NAME like 'OSEL%' ) )

    and i.DEPT_ID='STOCK1'
    and (i.HIDE='N' or i.HIDE='' or i.HIDE is null)
    and exists(select x.WORKING_CODE from INV_SITE x where x.WORKING_CODE=i.WORKING_CODE and x.DEPT_ID='DIS01' )
    order by d.DRUG_NAME ;

  end if ;
  
END$$

CREATE DEFINER=`dlict`@`%` PROCEDURE `UPDATE_TABLE` ()   BEGIN

CREATE TABLE IF NOT EXISTS `PRCV_CON` (
  `PRCV_DATE` char(8)  DEFAULT NULL,
  `PRCV_CON` int(11) DEFAULT NULL,
  KEY `PRCV_CON0` (`PRCV_DATE`,`PRCV_CON`)
) ENGINE=InnoDB ;


CREATE TABLE IF NOT EXISTS `PRE_MS_IVO` (
  `ARRIVE_DATE` char(8)  DEFAULT NULL,
  `INVOICE_NO` varchar(25)  DEFAULT NULL,
  `VENDOR_CODE` char(6)  DEFAULT NULL,
  `GROSS_WEIGHT` double DEFAULT NULL,
  `QTY` int(11) DEFAULT NULL,
  `AMOUNT` double DEFAULT NULL,
  `USER_ID` varchar(30)  DEFAULT NULL,
  `PRE_RECEIVE_NO` char(7)  NOT NULL,
  `LAST_UPDATE` datetime DEFAULT NULL,
  `PO_NO` varchar(15)  DEFAULT NULL,
  `RCV_FLAG` char(1)  DEFAULT NULL,
  `CANCEL_FLAG` char(1)  DEFAULT NULL,
  `REMARK` varchar(255)  DEFAULT NULL,
  PRIMARY KEY (`PRE_RECEIVE_NO`),
  KEY `PRE_MS_IVO` (`PRE_RECEIVE_NO`)
) ENGINE=InnoDB ;



CREATE TABLE IF NOT EXISTS `quali_goal` (
  `QUALI_ID` int(11) NOT NULL,
  `WORKING_CODE` varchar(18)  NOT NULL,
  `MIN_VALUE` varchar(30)  DEFAULT NULL,
  `MAX_VALUE` varchar(30)  DEFAULT NULL,
  `UOM_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`QUALI_ID`,`WORKING_CODE`)
) ENGINE=InnoDB ;


CREATE TABLE IF NOT EXISTS `quali_item` (
  `QUALI_ID` int(11) NOT NULL,
  `QUALI_DES` varchar(60)  DEFAULT NULL,
  PRIMARY KEY (`QUALI_ID`)
) ENGINE=InnoDB ;

CREATE TABLE IF NOT EXISTS `quali_rcv` (
  `RECEIVE_NO` char(10) NOT NULL,
  `WORKING_CODE` varchar(18) NOT NULL,
  `TRADE_CODE` varchar(18) NOT NULL,
  `LOT_NO` varchar(20) NOT NULL,
  `QUALI_ID` int(11) NOT NULL,
  `QUALI_VALUE` varchar(20) default NULL,
  `USER_ID` varchar(30) default NULL,
  PRIMARY KEY  (`RECEIVE_NO`,`WORKING_CODE`,`TRADE_CODE`,`LOT_NO`,`QUALI_ID`)
) ENGINE=InnoDB ;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'quali_rcv' AND COLUMN_NAME='LOT_NO' and table_schema = DATABASE()) THEN
 ALTER TABLE quali_rcv ADD LOT_NO VARCHAR(20) NULL  ;
 ALTER TABLE quali_rcv drop primary key, ADD PRIMARY KEY (`RECEIVE_NO`,`WORKING_CODE`,`TRADE_CODE`,`LOT_NO`,`QUALI_ID`) ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'FORM' AND COLUMN_NAME='MOD_SYS' and table_schema = DATABASE()) THEN
 ALTER TABLE FORM ADD MOD_SYS VARCHAR(10) NULL  ;
 UPDATE FORM SET MOD_SYS='MED' WHERE MOD_SYS IS NULL;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'FORM' AND COLUMN_NAME='HIDE' and table_schema = DATABASE()) THEN
 ALTER TABLE FORM ADD HIDE VARCHAR(1) DEFAULT 'N'  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='URL_DC' and table_schema = DATABASE()) THEN

 ALTER TABLE HOSP_INV ADD URL_DC VARCHAR(255) NULL  ;

END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='TMTRF_VERSION' and table_schema = DATABASE()) THEN

 ALTER TABLE HOSP_INV ADD TMTRF_VERSION VARCHAR(8) NULL  ;

END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='URL_DC' and table_schema = DATABASE()) THEN
 ALTER TABLE HOSP_INV ADD URL_DC VARCHAR(255) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='INVSCL_USER' and table_schema = DATABASE()) THEN
 ALTER TABLE HOSP_INV ADD INVSCL_USER VARCHAR(50) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='INVSCL_PASS' and table_schema = DATABASE()) THEN
 ALTER TABLE HOSP_INV ADD INVSCL_PASS VARCHAR(50) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='SYNC_MED_ITEMS' and table_schema = DATABASE()) THEN
 ALTER TABLE HOSP_INV ADD SYNC_MED_ITEMS smallint NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='TOKEN' and table_schema = DATABASE()) THEN
 ALTER TABLE HOSP_INV ADD TOKEN VARCHAR(200) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='TMTAPI_USERNAME' and table_schema = DATABASE()) THEN
 ALTER TABLE HOSP_INV ADD TMTAPI_USERNAME VARCHAR(200) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='TMTAPI_PASSWORD' and table_schema = DATABASE()) THEN
 ALTER TABLE HOSP_INV ADD TMTAPI_PASSWORD VARCHAR(200) NULL  ;
END IF;

IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='HOSP_ID' and CHARACTER_MAXIMUM_LENGTH < 9 and table_schema = DATABASE() ) THEN
 ALTER TABLE HOSP_INV MODIFY COLUMN  HOSP_ID VARCHAR(9) NULL  ;
END IF;



IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'PRE_MS_IVO' AND COLUMN_NAME='QTY' and table_schema = DATABASE()) THEN
 ALTER TABLE PRE_MS_IVO ADD QTY  int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'PRE_MS_IVO' AND COLUMN_NAME='DEPT_ID' and table_schema = DATABASE()) THEN
 ALTER TABLE PRE_MS_IVO ADD DEPT_ID  varchar(6) NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'BUYPLAN' AND COLUMN_NAME='MOD_SYS' and table_schema = DATABASE()) THEN
 ALTER TABLE BUYPLAN ADD MOD_SYS VARCHAR(10) NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'BUYPLAN_C' AND COLUMN_NAME='BUYMET_CODE' and table_schema = DATABASE()) THEN
 ALTER TABLE BUYPLAN_C ADD BUYMET_CODE  int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'BUYPLAN_C' AND COLUMN_NAME='QTY_FREE_Y3' and table_schema = DATABASE()) THEN
 ALTER TABLE BUYPLAN_C ADD QTY_FREE_Y3  float NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'BUYPLAN_C' AND COLUMN_NAME='HIDE' and table_schema = DATABASE()) THEN
 ALTER TABLE BUYPLAN_C ADD HIDE  VARCHAR(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'BUYPLAN_C' AND COLUMN_NAME='PLAN_TYPE' and table_schema = DATABASE()) THEN
 ALTER TABLE BUYPLAN_C ADD PLAN_TYPE  int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'BUYPLAN_C' AND COLUMN_NAME='MOD_SYS' and table_schema = DATABASE()) THEN
 ALTER TABLE BUYPLAN_C ADD MOD_SYS VARCHAR(10) NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'BDG_AMT' AND COLUMN_NAME = 'LIMIT_FLAG' and table_schema = DATABASE()) THEN
  ALTER TABLE BDG_AMT ADD  LIMIT_FLAG   CHAR(1)  NULL ;
     ALTER TABLE BDG_AMT ADD  BUDGET_AMT_TRI1   float  NULL ;
     ALTER TABLE BDG_AMT ADD  BUDGET_AMT_TRI2   float  NULL ;
     ALTER TABLE BDG_AMT ADD  BUDGET_AMT_TRI3   float  NULL ;
     ALTER TABLE BDG_AMT ADD  BUDGET_AMT_TRI4   float  NULL ;
     ALTER TABLE BDG_AMT ADD  TOTAL_BUY_TRI1   float  NULL ;
     ALTER TABLE BDG_AMT ADD  TOTAL_BUY_TRI2   float  NULL ;
     ALTER TABLE BDG_AMT ADD  TOTAL_BUY_TRI3   float  NULL ;
     ALTER TABLE BDG_AMT ADD  TOTAL_BUY_TRI4   float  NULL ;
     ALTER TABLE BDG_AMT ADD  BUDGET_RM_TRI1   float  NULL ;
     ALTER TABLE BDG_AMT ADD  BUDGET_RM_TRI2   float  NULL ;
     ALTER TABLE BDG_AMT ADD  BUDGET_RM_TRI3   float  NULL ;
     ALTER TABLE BDG_AMT ADD  BUDGET_RM_TRI4   float  NULL ;
END IF;



CREATE TABLE IF NOT EXISTS `DOC_FLOW` (
    `REF_NO` varchar(7)  NOT NULL,
  `SEND_DATE` char(8)  DEFAULT NULL,
  `RCV_DATE` char(8)  DEFAULT NULL,
  `SEND_DEPT` char(6)  DEFAULT NULL,
  `RCV_DEPT` char(6)  DEFAULT NULL,
  `USER_ID` varchar(30)  DEFAULT NULL,
  `LAST_UPDATE` datetime DEFAULT NULL,
  `TOTAL_INVOICE` int(11) DEFAULT NULL,
  `CONFIRM_FLAG` char(1)  DEFAULT NULL,
  `BUDGET_TYPE` int(11) DEFAULT NULL,
  PRIMARY KEY (`REF_NO`)
) ENGINE=InnoDB ;


CREATE TABLE IF NOT EXISTS `doc_flow_c` (
  `REF_NO` varchar(7)  NOT NULL,
  `INVOICE_NO` varchar(25)  NOT NULL,
  `PO_NO` varchar(25)  NOT NULL,
  `RCV_FLAG` char(1)  DEFAULT NULL,
  `USER_SEND` varchar(30)  DEFAULT NULL,
  `USER_RCV` varchar(30)  DEFAULT NULL,
  `LAST_UPDATE` datetime DEFAULT NULL,
  PRIMARY KEY (`INVOICE_NO`,`REF_NO`)
) ENGINE=InnoDB ;


CREATE TABLE IF NOT EXISTS `dist_type` (
  `DIST_TYPE_CODE` int(11) NOT NULL,
  `DIST_TYPE_NAME` varchar(60)  NOT NULL,
  `HIDE` char(1)  NOT NULL,
  PRIMARY KEY (`DIST_TYPE_CODE`)
) ENGINE=InnoDB ;

CREATE TABLE IF NOT EXISTS `ADD_IN_PROGRAMS` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `BAR_CAPTION` varchar(30)  DEFAULT NULL,
  `MENU_CAPTION` varchar(30)  DEFAULT NULL,
  `ICON` longblob,
  `USER_ID` varchar(35)  DEFAULT NULL,
  `ISSUE_DATE` datetime DEFAULT NULL,
  `PROGRAM_DATA` longblob,
  `CHANGE_DATE` datetime DEFAULT NULL,
  `PROGRAM_NAME` varchar(30)  DEFAULT NULL,
  `VERSION` float DEFAULT 0,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB ;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO_C' AND COLUMN_NAME='CHG_REF' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO_C ADD CHG_REF  VARCHAR(13) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO_C' AND COLUMN_NAME='MSIVOC_RECNO' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO_C ADD MSIVOC_RECNO  int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO_C' AND COLUMN_NAME='BUY_COMMON' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO_C ADD BUY_COMMON  VARCHAR(2) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO_C' AND COLUMN_NAME='EDI_FLAG' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO_C ADD EDI_FLAG  VARCHAR(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO_C' AND COLUMN_NAME='USER_CANCEL' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO_C ADD USER_CANCEL  VARCHAR(30) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO_C' AND COLUMN_NAME='REASON_CANCEL' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO_C ADD REASON_CANCEL  int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO' AND COLUMN_NAME='REF_NO' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO ADD REF_NO VARCHAR(15) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO' AND COLUMN_NAME='SUB_PO_UNO' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO ADD SUB_PO_UNO VARCHAR(10) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO' AND COLUMN_NAME='RES_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO ADD RES_DATE CHAR(8) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO' AND COLUMN_NAME='REQ_INTER' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO ADD REQ_INTER CHAR(1) DEFAULT 'N'  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO' AND COLUMN_NAME='CFM_INTER' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO ADD CFM_INTER CHAR(1) DEFAULT 'N'  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO' AND COLUMN_NAME='PROJECT_NO' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO ADD PROJECT_NO VARCHAR(30) DEFAULT null  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO' AND COLUMN_NAME='EGP_NO' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO ADD EGP_NO VARCHAR(30) DEFAULT null  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO' AND COLUMN_NAME='GF_NO' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO ADD GF_NO VARCHAR(10) DEFAULT null  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO' AND COLUMN_NAME='EDI_FLAG' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO ADD EDI_FLAG VARCHAR(1) DEFAULT null  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO' AND COLUMN_NAME='EDI_RES' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO ADD EDI_RES VARCHAR(50) DEFAULT null  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO' AND COLUMN_NAME='SEND_PO_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO ADD SEND_PO_DATE VARCHAR(8) DEFAULT null  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_PO' AND COLUMN_NAME='ACK_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_PO ADD ACK_DATE VARCHAR(8) DEFAULT null  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO_C' AND COLUMN_NAME='REMARK' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO_C ADD REMARK VARCHAR(100) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO_C' AND COLUMN_NAME='CLIENT_IP' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO_C ADD CLIENT_IP VARCHAR(15) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO_C' AND COLUMN_NAME='SEND_CANCEL' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO_C ADD SEND_CANCEL VARCHAR(1) NULL  ;
END IF;



IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO_C' AND COLUMN_NAME='QTY_CFM' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO_C ADD  QTY_CFM  VARCHAR(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO_C' AND COLUMN_NAME='LAST_UPD' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO_C ADD  LAST_UPD  DateTime NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO_C' AND COLUMN_NAME='REV_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO_C ADD  REV_DATE  DateTime NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO_C' AND COLUMN_NAME='APP_VERSION' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO_C ADD  APP_VERSION  VARCHAR(10) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO_C' AND COLUMN_NAME='WA_COST' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO_C ADD  WA_COST  decimal NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO_C' AND COLUMN_NAME='DISP_RECNO' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO_C ADD  DISP_RECNO  INT NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_IVO_C' AND COLUMN_NAME='REMAIN_QTY' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_IVO_C ADD REMAIN_QTY float NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_IVO_C' AND COLUMN_NAME='PO_ITEM_TYPE' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_IVO_C ADD PO_ITEM_TYPE int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_IVO_C' AND COLUMN_NAME='SEND_FLAG' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_IVO_C ADD SEND_FLAG varchar(1) DEFAULT 'N'  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_IVO' AND COLUMN_NAME='SEND_FLAG' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_IVO ADD SEND_FLAG varchar(1) DEFAULT 'N'  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_IVO' AND COLUMN_NAME='PRE_RECEIVE_NO' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_IVO ADD PRE_RECEIVE_NO VARCHAR(15) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_IVO' AND COLUMN_NAME='RCV_TIME' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_IVO ADD RCV_TIME CHAR(4) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'MS_IVO' AND COLUMN_NAME='ASN_NO' and table_schema = DATABASE()) THEN
 ALTER TABLE MS_IVO ADD ASN_NO VARCHAR(10) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'PRE_MS_IVO' AND COLUMN_NAME='QTY' and table_schema = DATABASE()) THEN
 ALTER TABLE PRE_MS_IVO ADD QTY int NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'PRE_MS_IVO' AND COLUMN_NAME='AMOUNT' and table_schema = DATABASE()) THEN
 ALTER TABLE PRE_MS_IVO ADD AMOUNT double NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO' AND COLUMN_NAME='CONFIRM_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO ADD CONFIRM_DATE VARCHAR(8) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO' AND COLUMN_NAME='CONFIRM_TIME' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO ADD CONFIRM_TIME VARCHAR(4) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'SM_PO' AND COLUMN_NAME='DIST_TYPE' and table_schema = DATABASE()) THEN
 ALTER TABLE SM_PO ADD DIST_TYPE int NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_MD' AND COLUMN_NAME='FORECAST' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_MD ADD FORECAST  float NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_MD' AND COLUMN_NAME='LAST_VENDOR_CODE' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_MD ADD LAST_VENDOR_CODE  int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_MD' AND COLUMN_NAME='HIS_RATE' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_MD ADD HIS_RATE float NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_MD' AND COLUMN_NAME='LAST_VENDOR_CODE' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_MD ADD LAST_VENDOR_CODE int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_MD' AND COLUMN_NAME='KANBAN_RATIO' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_MD ADD KANBAN_RATIO int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_MD' AND COLUMN_NAME='USE_MINMAX' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_MD ADD USE_MINMAX varchar(1) DEFAULT 'N'  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_MD' AND COLUMN_NAME='WA_COST' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_MD ADD WA_COST decimal DEFAULT 0  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_MD' AND COLUMN_NAME='COUNT_ON_HAND' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_MD ADD COUNT_ON_HAND int DEFAULT NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_MD' AND COLUMN_NAME='LAST_TRADE_CODE' AND table_schema = DATABASE()) THEN
 ALTER TABLE INV_MD ADD LAST_TRADE_CODE varchar(18) DEFAULT NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='IS_HAD' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD IS_HAD CHAR(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='REF_PRICE_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD REF_PRICE_DATE CHAR(8) NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='USE_TRADE_DIS' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD USE_TRADE_DIS CHAR(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='LAST_NOTIFY_HAD' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD LAST_NOTIFY_HAD datetime NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='USER_ID' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD USER_ID varchar(32) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='MIT' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD MIT varchar(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='CONT_VALUE' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD CONT_VALUE float NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='CONT_UNIT_ID' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD CONT_UNIT_ID int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='CHANGE_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD CHANGE_DATE datetime NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='STD_PRICE4' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD STD_PRICE4 double NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='STD_PRICE5' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD STD_PRICE5 double NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='STD_RATIO4' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD STD_RATIO4 double NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_GN' AND COLUMN_NAME='STD_RATIO5' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_GN ADD STD_RATIO5 double NULL  ;
END IF;




IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_VN' AND COLUMN_NAME='USER_ID' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_VN ADD USER_ID varchar(32) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_VN' AND COLUMN_NAME='CHANGE_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_VN ADD CHANGE_DATE datetime NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_VN' AND COLUMN_NAME='PREMA_FLAG' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_VN ADD PREMA_FLAG varchar(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_VN' AND COLUMN_NAME='DEF_BUYCOM' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_VN ADD DEF_BUYCOM int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_VN' AND COLUMN_NAME='REMARK' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_VN ADD REMARK varchar(255) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_VN' AND COLUMN_NAME='USE_IMPORT_PROD' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_VN ADD USE_IMPORT_PROD varchar(255) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_VN' AND COLUMN_NAME='GPSC' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_VN ADD GPSC varchar(20) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DRUG_VN' AND COLUMN_NAME='exchange_code' and table_schema = DATABASE()) THEN
 ALTER TABLE DRUG_VN ADD exchange_code varchar(6) NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'COMPANY' AND COLUMN_NAME='ISSUE_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE COMPANY ADD ISSUE_DATE CHAR(8) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'COMPANY' AND COLUMN_NAME='EMAIL' and table_schema = DATABASE()) THEN
 ALTER TABLE COMPANY ADD EMAIL varCHAR(20) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'COMPANY' AND COLUMN_NAME='BANK_ACC_NAME' and table_schema = DATABASE()) THEN
 ALTER TABLE COMPANY ADD BANK_ACC_NAME varCHAR(30) NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'COMPANY' AND COLUMN_NAME='BANK_NAME' and table_schema = DATABASE()) THEN
 ALTER TABLE COMPANY ADD BANK_NAME varCHAR(30) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'COMPANY' AND COLUMN_NAME='FAX' and table_schema = DATABASE()) THEN
 ALTER TABLE COMPANY ADD FAX varCHAR(40) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'COMPANY' AND COLUMN_NAME='DETAILER' and table_schema = DATABASE()) THEN
 ALTER TABLE COMPANY ADD DETAILER varCHAR(40) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'COMPANY' AND COLUMN_NAME='EDI_VENDOR_CODE' and table_schema = DATABASE()) THEN
 ALTER TABLE COMPANY ADD EDI_VENDOR_CODE varCHAR(10) NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DEPT_ID' AND COLUMN_NAME='SUPPLY_DIRECTOR_ID' and table_schema = DATABASE()) THEN
 ALTER TABLE DEPT_ID ADD SUPPLY_DIRECTOR_ID int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DEPT_ID' AND COLUMN_NAME='SUPPLY_OFFICER_ID' and table_schema = DATABASE()) THEN
 ALTER TABLE DEPT_ID ADD SUPPLY_OFFICER_ID int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DEPT_ID' AND COLUMN_NAME='INV_DIRECTOR_ID' and table_schema = DATABASE()) THEN
 ALTER TABLE DEPT_ID ADD INV_DIRECTOR_ID int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DEPT_ID' AND COLUMN_NAME='DEPT_UID' and table_schema = DATABASE()) THEN
 ALTER TABLE DEPT_ID ADD DEPT_UID VARCHAR(10) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DEPT_ID' AND COLUMN_NAME='LINE_TOKEN' and table_schema = DATABASE()) THEN
 ALTER TABLE DEPT_ID ADD LINE_TOKEN VARCHAR(50) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DEPT_ID' AND COLUMN_NAME='CAL_RATE' and table_schema = DATABASE()) THEN
 ALTER TABLE DEPT_ID ADD CAL_RATE VARCHAR(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DEPT_ID' AND COLUMN_NAME='EMAIL' and table_schema = DATABASE()) THEN
 ALTER TABLE DEPT_ID ADD EMAIL VARCHAR(60) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'dept_id' AND COLUMN_NAME='RCV_TRANSFER' and table_schema = DATABASE()) THEN
 ALTER TABLE dept_id ADD RCV_TRANSFER VARCHAR(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'dept_id' AND COLUMN_NAME='cost_center_id' and table_schema = DATABASE()) THEN
 ALTER TABLE dept_id ADD cost_center_id VARCHAR(6) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ADD_IN_PROGRAMS' AND COLUMN_NAME='VERSION' and table_schema = DATABASE()) THEN
 ALTER TABLE ADD_IN_PROGRAMS ADD VERSION float NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CNT_C' AND COLUMN_NAME='TRADE_CODE' and table_schema = DATABASE()) THEN
 ALTER TABLE CNT_C ADD TRADE_CODE varchar(18) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CNT_C' AND COLUMN_NAME='BUY_COMMON' and table_schema = DATABASE()) THEN
 ALTER TABLE CNT_C ADD BUY_COMMON varchar(2) NULL  ;
END IF;




IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='REMAIN_QTY_T' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD REMAIN_QTY_T decimal NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='REMAIN_COST_T' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD REMAIN_COST_T decimal NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='REMAIN_VALUE_T' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD REMAIN_VALUE_T decimal NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='APP_VERSION' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD APP_VERSION varchar(10) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='SMPOC_RECNO' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD SMPOC_RECNO int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='REMAIN_QTY_LOT' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD REMAIN_QTY_LOT decimal NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='REMAIN_COST_LOT' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD REMAIN_COST_LOT decimal NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='REMAIN_VALUE_LOT' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD REMAIN_VALUE_LOT decimal NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='VENDOR_CODE' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD VENDOR_CODE int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='PACK_CODE' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD PACK_CODE int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='PACK_COST' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD PACK_COST decimal NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='WT_AVG' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD WT_AVG decimal NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='HN' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD HN varchar(7) NULL  ;
END IF;

 IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='VN' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD VN varchar(4) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CARD' AND COLUMN_NAME='DISP_NO' and table_schema = DATABASE()) THEN
 ALTER TABLE CARD ADD DISP_NO int NULL  ;
END IF;



CREATE TABLE IF NOT EXISTS `SM_PO_E` (
  `RECORD_NUMBER` int(11) NOT NULL AUTO_INCREMENT,
  `SUB_PO_NO` char(12)  DEFAULT NULL,
  `WORKING_CODE` char(7)  DEFAULT NULL,
  `QTY_DIST` double DEFAULT NULL,
  `QTY_DIST_E` double DEFAULT NULL,
  `PACK_RATIO` double DEFAULT NULL,
  `COST` double DEFAULT NULL,
  `TRADE_CODE` varchar(18)  DEFAULT NULL,
  `EXPIRED_DATE` char(8)  DEFAULT NULL,
  `LOCATION` char(5)  DEFAULT NULL,
  `USER_ID` varchar(30)  DEFAULT NULL,
  `COMPLETE_FLAG` varchar(1)  DEFAULT NULL,
  `LOT_NO` varchar(40)  DEFAULT NULL,
  `CANCEL_DIST` char(1)  DEFAULT NULL,
  `INV_NO` varchar(25)  DEFAULT NULL,
  `INV_REMAIN_QTY` double DEFAULT NULL,
  `RECEIVE_NO` varchar(10)  DEFAULT NULL,
  `DEPT_ID` varchar(6)  DEFAULT NULL,
  `DIST_DATE` varchar(8)  DEFAULT NULL,
  `DIST_TIME` varchar(2)  DEFAULT NULL,
  `MSIVOC_RECORD` int(11) DEFAULT NULL,
  `STOCK_ID` varchar(6)  DEFAULT NULL,
  PRIMARY KEY (`RECORD_NUMBER`)
) ENGINE=InnoDB ;


CREATE TABLE IF NOT EXISTS `INVS_REF` (
  `RETURN_DATE` char(8)  DEFAULT NULL,
  `RETURN_CON` int(11) DEFAULT NULL
) ENGINE=InnoDB ;

CREATE TABLE IF NOT EXISTS `RTN_REASON` (
  `ID` int(11) NOT NULL,
  `RETURN_REASON` varchar(60)  DEFAULT NULL,
  `HIDE` char(1)  DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB ;

CREATE TABLE IF NOT EXISTS `INV_RTN` (
  `RETURN_NO` char(10)  DEFAULT NULL,
  `DEPT_ID` varchar(6)  DEFAULT NULL,
  `STOCK_ID` varchar(6)  DEFAULT NULL,
  `RETURN_DATE` char(8)  DEFAULT NULL,
  `RETURN_TIME` char(4)  DEFAULT NULL,
  `USER_ID` varchar(16)  DEFAULT NULL,
  `TOTAL_ITEM` int(11) DEFAULT NULL,
  `TOTAL_VALUE_RETURN` double DEFAULT NULL,
  `TOTAL_VALUE_ALL` varchar(255)  DEFAULT NULL,
  `CONFIRM_FLAG` char(1)  DEFAULT NULL,
  KEY `inv_return0` (`RETURN_NO`),
  KEY `inv_return1` (`RETURN_NO`,`DEPT_ID`,`STOCK_ID`,`RETURN_DATE`)
) ENGINE=InnoDB ;


CREATE TABLE IF NOT EXISTS `INV_RTN_C` (
  `RETURN_NO` char(10)  DEFAULT NULL,
  `WORKING_CODE` varchar(18)  DEFAULT NULL,
  `TRADE_CODE` varchar(18)  DEFAULT NULL,
  `TOTAL_QTY` double DEFAULT NULL,
  `RETURN_QTY` double DEFAULT NULL,
  `PACK_RATIO` int(11) DEFAULT NULL,
  `UNIT_COST` double DEFAULT NULL,
  `EXPIRED_DATE` char(8)  DEFAULT NULL,
  `LOT_NO` varchar(10)  DEFAULT NULL,
  `TOTAL_COST` double DEFAULT NULL,
  `RETURN_COST` double DEFAULT NULL,
  `REASON_ID` int(11) DEFAULT NULL,
  `REF_DATA` varchar(10)  DEFAULT NULL,
  `USER_ID` varchar(16)  DEFAULT NULL,
  `RETURN_DATE` char(8)  DEFAULT NULL,
  `RETURN_TIME` char(4)  DEFAULT NULL,
  `ID` char(24)  NOT NULL,
  `USER_CONFIRM` varchar(16)  DEFAULT NULL,
  `USER_CANCEL` varchar(16)  DEFAULT NULL,
  `RCV_FLAG` char(1)  DEFAULT NULL,
  `LOCATION` varchar(10)  DEFAULT NULL,
  `CANCEL_FLAG` char(1)  DEFAULT NULL,
  `ACTION_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `INV_RETURN_C0` (`RETURN_NO`,`WORKING_CODE`,`TRADE_CODE`)
) ENGINE=InnoDB ;

CREATE TABLE IF NOT EXISTS `RTN_ACTION` (
  `ID` int(11) NOT NULL,
  `ACTION_DES` varchar(60)  DEFAULT NULL,
  `HIDE` char(1)  DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB ;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_RTN_C' AND COLUMN_NAME='ACTION_ID' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_RTN_C ADD ACTION_ID int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_RTN_C' AND COLUMN_NAME='PACK_RATIO' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_RTN_C ADD PACK_RATIO int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_RTN_C' AND COLUMN_NAME='PACK_CODE' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_RTN_C ADD PACK_CODE int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_RTN_C' AND COLUMN_NAME='FREE_FLAG' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_RTN_C ADD FREE_FLAG varchar(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'BUYMETHOD' AND COLUMN_NAME='DEFAULT_FLAG' and table_schema = DATABASE()) THEN
 ALTER TABLE BUYMETHOD ADD DEFAULT_FLAG varchar(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'BUYCOMMON' AND COLUMN_NAME='DEFAULT_FLAG' and table_schema = DATABASE()) THEN
 ALTER TABLE BUYCOMMON ADD DEFAULT_FLAG varchar(1) NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'BDG_TYPE' AND COLUMN_NAME='DEFAULT_FLAG' and table_schema = DATABASE()) THEN
 ALTER TABLE BDG_TYPE ADD DEFAULT_FLAG varchar(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CNT' AND COLUMN_NAME='GF_NO' and table_schema = DATABASE()) THEN
 ALTER TABLE CNT ADD GF_NO varchar(10) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'CNT' AND COLUMN_NAME='CNT_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE CNT ADD CNT_DATE varchar(8) NULL  ;
END IF;



CREATE TABLE IF NOT EXISTS `location` (
  `LOCATION_ID` varchar(6)  DEFAULT NULL,
  `TRADE_CODE` varchar(10)  DEFAULT NULL,
  `WORKING_CODE` varchar(10)  DEFAULT NULL,
  `DEPT_ID` varchar(6)  DEFAULT NULL,
  KEY `location0` (`LOCATION_ID`,`TRADE_CODE`)
) ENGINE=InnoDB ;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='PREFIX' and table_schema = DATABASE()) THEN
 ALTER TABLE HOSP_INV ADD PREFIX varchar(80) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'HOSP_INV' AND COLUMN_NAME='DMSIC_TOKEN' and table_schema = DATABASE()) THEN
 ALTER TABLE HOSP_INV ADD DMSIC_TOKEN varchar(1000) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DEPT_MAP' AND COLUMN_NAME='AUTO_DISP' and table_schema = DATABASE()) THEN
 ALTER TABLE DEPT_MAP ADD AUTO_DISP varchar(1)  DEFAULT 'N'  ;
END IF;

CREATE TABLE IF NOT EXISTS`focus_list` (
  `LIST_CODE` int(11) DEFAULT NULL,
  `LIST_TYPE` smallint(6) DEFAULT NULL,
  `USER_ID` varchar(32)  DEFAULT NULL,
  `DEPT_ID` varchar(6)  DEFAULT NULL,
  `LIST_NAME` varchar(25)  DEFAULT NULL,
  KEY `focus_list0` (`LIST_CODE`,`USER_ID`)
) ENGINE=InnoDB ;


CREATE TABLE IF NOT EXISTS`focus_list_c` (
  `WORKING_CODE` varchar(18)  NOT NULL,
  `LIST_CODE` int(11) DEFAULT NULL,
  `USER_ID` varchar(32)  DEFAULT NULL,
  KEY `focus_list_c0` (`LIST_CODE`,`WORKING_CODE`)
) ENGINE=InnoDB ;


CREATE TABLE IF NOT EXISTS`inv_site` (
  `WORKING_CODE` char(18)  DEFAULT NULL,
  `LOCATION` char(5)  DEFAULT NULL,
  `TRADE_CODE` varchar(18)  NOT NULL,
  `DEPT_ID` char(6)  NOT NULL,
  `USER_ID` varchar(30)  DEFAULT NULL,
  `AUTO_DISP` char(1)  DEFAULT 'Y',
  PRIMARY KEY (`DEPT_ID`,`TRADE_CODE`),
  KEY `inv_site0` (`TRADE_CODE`,`DEPT_ID`) USING BTREE
) ENGINE=InnoDB ;


 CREATE TABLE IF NOT EXISTS`e_po` (
  `EMAIL_ACC` varchar(30)  DEFAULT NULL,
  `EMAIL_PASS` varchar(32)  DEFAULT NULL,
  `EMAIL_CC` varchar(60)  DEFAULT NULL,
  `EMAIL_SUBJECT` varchar(100)  DEFAULT NULL,
  `DEPT_ID` varchar(6)  DEFAULT NULL,
  `EMAIL_MSG` varchar(255)  DEFAULT NULL,
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`),
  KEY `e_po0` (`DEPT_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 ;

CREATE TABLE IF NOT EXISTS `gf` (
  `GF_NO` varchar(10)  NOT NULL,
  `GF_DATE` varchar(8)  DEFAULT NULL,
  `PJT_NO` varchar(20)  DEFAULT NULL,
  `RCV_NO` varchar(10)  DEFAULT NULL,
  `AMOUNT` double DEFAULT NULL,
  `RUNNO` smallint(6) NOT NULL,
  PRIMARY KEY (`GF_NO`,`RUNNO`)
) ENGINE=InnoDB ;


CREATE TABLE IF NOT EXISTS `gf` (
  `GF_NO` varchar(10)  NOT NULL,
  `GF_DATE` varchar(8)  DEFAULT NULL,
  `PJT_NO` varchar(20)  DEFAULT NULL,
  `RCV_NO` varchar(10)  DEFAULT NULL,
  `AMOUNT` double DEFAULT NULL,
  `RUNNO` smallint(6) NOT NULL,
  PRIMARY KEY (`GF_NO`,`RUNNO`)
) ENGINE=InnoDB ;

CREATE TABLE IF NOT EXISTS`drug_compos` (
  `WORKING_CODE` varchar(7) DEFAULT NULL,
  `COMPOS_NAME` varchar(100) DEFAULT NULL,
  `COMPOS_CODE` int(11) NOT NULL,
  `TMTID` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`COMPOS_CODE`),
  KEY `drug_compos0` (`WORKING_CODE`),
  KEY `drug_compos1` (`TMTID`)
) ENGINE=InnoDB ;

CREATE TABLE IF NOT EXISTS `drug_spec` (
  `WORKING_CODE` varchar(7) NOT NULL,
  `ATTACH_FILE` longblob,
  `FILE_NAME` varchar(30) DEFAULT NULL,
  KEY `drug_spec0` (`WORKING_CODE`)
) ENGINE=InnoDB ;

CREATE TABLE IF NOT EXISTS `df_con` (
  `REF_NO_CON` int(11) NOT NULL,
  `LAST_UPDATE` char(8)  DEFAULT NULL,
  PRIMARY KEY (`REF_NO_CON`)
) ENGINE=InnoDB ;


CREATE TABLE IF NOT EXISTS `po_reason` (
  `ID` int(11) NOT NULL,
  `REASON_DES` varchar(60)  DEFAULT NULL,
  `HIDE` varchar(1)  DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB ;


CREATE TABLE IF NOT EXISTS `server_config`  (
  `section` varchar(50)  NULL DEFAULT NULL,
  `ident` varchar(50)  NULL DEFAULT NULL,
  `value` varchar(50)  NULL DEFAULT NULL,
  UNIQUE INDEX `server_config0`(`section` ASC, `ident` ASC) USING BTREE
) ENGINE = InnoDB ROW_FORMAT = Compact;



IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'drug_spec' AND COLUMN_NAME='SPEC_TEXT' and table_schema = DATABASE()) THEN
 ALTER TABLE drug_spec ADD SPEC_TEXT text  DEFAULT NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'INV_SITE' AND COLUMN_NAME='EFFECT_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE INV_SITE ADD EFFECT_DATE datetime NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DOC_FLOW_C' AND COLUMN_NAME='RECEIVE_NO' and table_schema = DATABASE()) THEN
 ALTER TABLE DOC_FLOW_C ADD RECEIVE_NO varchar(15) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DOC_FLOW_C' AND COLUMN_NAME='PO_NO' and table_schema = DATABASE()) THEN
 ALTER TABLE DOC_FLOW_C ADD PO_NO varchar(15)  NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'profile' AND COLUMN_NAME='DEPT_ID' and table_schema = DATABASE()) THEN
 ALTER TABLE profile ADD DEPT_ID char(6) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'profile' AND COLUMN_NAME='accept_term' and table_schema = DATABASE()) THEN
 ALTER TABLE profile ADD accept_term varchar(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ed_group' AND COLUMN_NAME='hide' and table_schema = DATABASE()) THEN
 ALTER TABLE ed_group ADD hide varchar(1) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'pack_ratio' AND COLUMN_NAME='USER_ID' and table_schema = DATABASE()) THEN
 ALTER TABLE pack_ratio ADD USER_ID varchar(32) NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'aic' AND COLUMN_NAME='MOD_SYS' and table_schema = DATABASE()) THEN
 ALTER TABLE aic ADD MOD_SYS varchar(10) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'DEPT_ID' AND COLUMN_NAME='ADDRESS' and table_schema = DATABASE()) THEN
 ALTER TABLE DEPT_ID ADD ADDRESS varchar(150) NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'Reports' AND COLUMN_NAME='CREATE_DATE' and table_schema = DATABASE()) THEN
 ALTER TABLE Reports ADD CREATE_DATE varchar(8) NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'Reports' AND COLUMN_NAME='VIEW_COUNT' and table_schema = DATABASE()) THEN
 ALTER TABLE Reports ADD VIEW_COUNT int NULL  ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'Reports' AND COLUMN_NAME='LAST_VIEW' and table_schema = DATABASE()) THEN
 ALTER TABLE Reports ADD LAST_VIEW datetime NULL  ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'dispensed' AND COLUMN_NAME='record_number' and table_schema = DATABASE()) THEN
  ALTER TABLE dispensed ADD RECORD_NUMBER INT PRIMARY KEY  NOT NULL AUTO_INCREMENT ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'dispensed' AND COLUMN_NAME='hn' and table_schema = DATABASE()) THEN
  ALTER TABLE dispensed ADD hn varchar(13) NULL ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'dispensed' AND COLUMN_NAME='vn' and table_schema = DATABASE()) THEN
  ALTER TABLE dispensed ADD vn varchar(17) NULL ;
END IF;

IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'dispensed' AND COLUMN_NAME='operate_date' and table_schema = DATABASE()) THEN
  ALTER TABLE dispensed ADD operate_date datetime NULL ;
END IF;


IF NOT EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'E_PO' AND COLUMN_NAME='GoogleToken' and table_schema = DATABASE()) THEN
  ALTER TABLE E_PO ADD GoogleToken varchar(255) null ;
END IF;


CREATE TABLE IF NOT EXISTS`mbs_re_m` (
  `RECORD_NUMBER` int(11) NOT NULL AUTO_INCREMENT,
  `YEAR` char(4)  DEFAULT NULL,
  `MONTH` char(2)  DEFAULT NULL,
  `RCV_QUAN` double DEFAULT NULL,
  `RCV_VALUE` double DEFAULT NULL,
  `SALE_QUAN` double DEFAULT NULL,
  `SALE_VALUE` double DEFAULT NULL,
  `REMAIN_QUAN` double DEFAULT NULL,
  `REMAIN_VALUE` double DEFAULT NULL,
  `DEPT_ID` char(6)  DEFAULT NULL,
  `LAST_UPDATE` datetime DEFAULT NULL,
  `BFW_QTY` double DEFAULT NULL,
  `BFW_VALUE` double DEFAULT NULL,
  `IS_ED` smallint(6) DEFAULT NULL,
  `BUY_FLAG` varchar(1)  DEFAULT NULL,
  PRIMARY KEY (`RECORD_NUMBER`),
  KEY `mbs_re_m` (`YEAR`,`MONTH`,`DEPT_ID`,`IS_ED`,`BUY_FLAG`)
) ENGINE=InnoDB ;



CREATE TABLE IF NOT EXISTS `edi_drug_vn` (
  `VENDOR_TRADE_CODE` varchar(10)  NOT NULL,
  `INVS_TRADE_CODE` varchar(18)  NOT NULL,
  `VENDOR_CODE` varchar(10)  NOT NULL,
  `TMTID` varchar(18)  DEFAULT NULL,
  PRIMARY KEY (`VENDOR_TRADE_CODE`,`INVS_TRADE_CODE`,`VENDOR_CODE`)
) ENGINE=InnoDB ;

CREATE TABLE IF NOT EXISTS `item_type` (
  `WORKING_CODE` varchar(10)  NOT NULL,
  `HOSP_LIST` smallint(6) NOT NULL,
  `FIRST_DATE` varchar(8)  NOT NULL,
  `LAST_DATE` varchar(8)  NOT NULL,
  `USER_ID` varchar(32)  DEFAULT NULL,
  PRIMARY KEY (`WORKING_CODE`,`HOSP_LIST`,`FIRST_DATE`,`LAST_DATE`)
) ENGINE=InnoDB ;

CREATE TABLE IF NOT EXISTS `exchange_info` (
  `exchange_code` varchar(2) NOT NULL,
  `exchange_des` varchar(100) NULL,
  `hide` varchar(1) NULL DEFAULT 'N',
  PRIMARY KEY (`exchange_code`)
) ENGINE=InnoDB ;


IF NOT EXISTS(SELECT * FROM information_schema.statistics   WHERE  table_name = 'BDG_AMT' and INDEX_NAME='bdg_amt0'  and COLUMN_NAME ='MOD_SYS' and table_schema = DATABASE()
) THEN

    IF EXISTS(SELECT * FROM information_schema.statistics   WHERE  table_name = 'BDG_AMT' and INDEX_NAME='bdg_amt0'  and table_schema = DATABASE())
    THEN
      ALTER TABLE `BDG_AMT` DROP INDEX `bdg_amt0`;
    END IF ;

  ALTER TABLE `BDG_AMT` ADD INDEX  `bdg_amt0` (`BUDGET_TYPE`,`FISCAL_YEAR`,`MOD_SYS`);

END IF ;


END$$

--
-- Functions
--
CREATE DEFINER=`dlict`@`%` FUNCTION `begin_fy` (`date` DATETIME) RETURNS CHAR(8) CHARSET utf8mb4 COLLATE utf8mb4_general_ci  BEGIN
  DECLARE monthx int;
  DECLARE yearx int;

  Set monthx = month(date);
  Set yearx = year(date);

	if  monthx < 10 then set yearx=yearx-1; end if ;

	 RETURN concat(convert(yearx,char),'1001') ;
END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `ce2cymd` (`date` DATETIME) RETURNS CHAR(15) CHARSET utf8 COLLATE utf8_unicode_ci  BEGIN

	 DECLARE dayx int;
   DECLARE monthx int;
   DECLARE yearx int; 
   DECLARE cmonth varchar(6) ;

   Set dayx = day(date);
   Set monthx = month(date);
   SEt yearx = year(date);

   RETURN CONCAT(convert(yearx,char) ,right(convert(100+monthx,char),2) , right(convert(100+dayx,char),2));

    


END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `cymd2be` (`date` CHAR(8)) RETURNS CHAR(15) CHARSET utf8 COLLATE utf8_unicode_ci  BEGIN

	DECLARE dayx int;
  DECLARE monthx int;
  DECLARE yearx int;
  DECLARE cmonth varchar(6);
    
    Set dayx =convert(mid(date,7,2),UNSIGNED);
    Set monthx = convert( mid(date,5,2),UNSIGNED);
    SEt yearx = convert(mid(date,1,4),UNSIGNED)+543;
 
    set cmonth = (select case monthx when 1 then 'ม.ค.'
        when 2 then 'ก.พ.'
        when 3 then 'มี.ค.'
        when 4 then 'เม.ย.'
        when 5 then 'พ.ค.'
        when 6 then 'มิ.ย.'
        when 7 then 'ก.ค.'
        when 8 then 'ส.ค.'
        when 9 then 'ก.ย.'
        when 10 then 'ต.ค.'
        when 11 then 'พ.ย.'
        when 12 then 'ธ.ค.'  end);
    
    RETURN   concat(convert(dayx,char), ' ' , cmonth , ' ' ,convert(yearx,char)) ;


END$$

CREATE DEFINER=`dlict`@`%` FUNCTION `cymd2tdmy` (`date` CHAR(8)) RETURNS VARCHAR(15) CHARSET utf8mb4 COLLATE utf8mb4_general_ci  BEGIN

  DECLARE dayx int;
  DECLARE monthx int;
  DECLARE yearx int;

    Set dayx =convert(mid(date,7,2),UNSIGNED);
    Set monthx = convert( mid(date,5,2),UNSIGNED);
    SEt yearx = convert(mid(date,1,4),UNSIGNED)+543;

  RETURN   concat(convert(dayx,char), '/' , monthx , '/' ,convert(yearx,char)) ;
END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `ED_LIST` (`ed_list` INT) RETURNS VARCHAR(5) CHARSET utf8 COLLATE utf8_unicode_ci  BEGIN

  declare returnx varchar(5) ;
  
  set returnx = (
	select CASE ed_list
	when 1 then 'ก'
	when 2 then 'ข'
	when 3 then 'ค'
	when 4 then 'ง'
	when 5 then 'จ (1)' 
  when 6 then 'จ (2)' 
  else '' end) ;


  RETURN returnx ;
 

END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `FiscalYear` (`date_cymd` CHAR(8)) RETURNS CHAR(4) CHARSET utf8 COLLATE utf8_unicode_ci  BEGIN


	  declare resultx varchar(15);

	   if substring(date_cymd,5,2) <= 09 then
        set resultx = CONCAT( 'ปีงบประมาณ ' , convert(substring(date_cymd,1,4)+543,char)) ;
     else
        set resultx = CONCAT(  'ปีงบประมาณ ' ,convert(substring(date_cymd,1,4)+544,char)) ;
     end if ;

	Return resultx ;


END$$

CREATE DEFINER=`dlict`@`%` FUNCTION `getOnhandInfo` (`working_code` VARCHAR(10), `stock_id` VARCHAR(6), `date_onhand` DATETIME) RETURNS TEXT CHARSET utf8mb4 COLLATE utf8mb4_general_ci DETERMINISTIC BEGIN
     DECLARE onhandInfo TEXT;

    SELECT GROUP_CONCAT(
        CONCAT(
            '[', i.lot_no, ' : ',
            DATE_FORMAT(i.expired_date, '%d/%m/%y'), ' : ',
            FLOOR(c.remain_qty_lot / c.active_pack), ' x ', c.active_pack,
            CASE
                WHEN c.remain_qty_lot MOD c.active_pack > 0
                THEN CONCAT(' (', c.remain_qty_lot MOD c.active_pack, ')')
                ELSE ''
            END,
            ']'
						) SEPARATOR 0x0A
    ) INTO onhandInfo
    FROM (
        SELECT ci.trade_code, ci.lot_no, ci.expired_date,
               MAX(ci.record_number) AS max_record_number
        FROM card ci
        WHERE ci.operate_date <= date_onhand
          AND ci.stock_id = stock_id
          AND ci.working_code = working_code
          AND ci.active_qty > 0
        GROUP BY ci.trade_code, ci.lot_no, ci.expired_date
    ) AS i
    LEFT JOIN card c ON c.record_number = i.max_record_number
    LEFT JOIN drug_gn g ON g.working_code = c.working_code
    LEFT JOIN drug_vn v ON v.trade_code = c.trade_code
    WHERE c.remain_qty_lot > 0 AND c.remain_qty_lot IS NOT NULL;

    RETURN onhandInfo;
END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `IS_ED` (`ised` INT) RETURNS VARCHAR(5) CHARSET utf8 COLLATE utf8_unicode_ci  BEGIN

  declare returnx varchar(3) ;
  
  set returnx = (
	select CASE ised
	when 1 then 'ED'
	when 2 then 'NED'
	when 3 then 'NDMS'
	when 4 then 'CM'
	when 5 then 'PS' end) ;


  RETURN returnx ;
 

END$$

CREATE DEFINER=`dlict`@`%` FUNCTION `NLEM` (`ed` INT) RETURNS VARCHAR(30) CHARSET utf8mb4 COLLATE utf8mb4_general_ci  BEGIN

   declare returnx varchar(30) ;

   If ed=1 then
          Set returnx = 'ยาในบัญชียาหลักแห่งชาติ'  ;
   elseIf ed=2 then
          Set returnx = 'ยานอกบัญชียาหลักแห่งชาติ'  ;
   elseIf ed=3 then
          Set returnx = 'เวชภัณฑ์มิใช่ยา' ;
   elseIf ed=4 then
          Set returnx = 'เคมีภัณฑ์' ;
   elseIf ed=5 then
          Set returnx = 'วัสดุห้องปฏิบัติการ' ;
   elseIf ed=6 then
          Set returnx = 'วัสดุเภสัชกรรม' ;
   elseIf ed=7 then
          Set returnx = 'วัสดุทางทันตกรรม' ;
   elseIf ed=8 then
          Set returnx = 'วัสดุการแพทย์ทั่วไป' ;
   elseIf ed=9 then
          Set returnx = 'วัสดุทางรังสี' ;
   elseIf ed=10 then
          Set returnx = 'วัสดุทั่วไป' ;
   elseIf ed=11 then
          Set returnx = 'ครุภัณฑ์' ;
   elseIf ed=12 then
          Set returnx = 'ครุภัณฑ์การแพทย์' ;
   else
           Set returnx = '' ;
   end if ;

   RETURN returnx ;
END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `NowFiscalYear` (`YearType` CHAR(1)) RETURNS CHAR(4) CHARSET utf8 COLLATE utf8_unicode_ci  BEGIN

	  declare resultx char(4);


	if  DATE_FORMAT(CURDATE(),'%m') > 9 then
		set resultx = YEAR(CURDATE()+1 );
	else
		set resultx = YEAR(CURDATE()) ;
	end if;
	
   	if YearType='B' then set resultx=resultx+543; end if;

	Return resultx ;


END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `ThaiFiscalYear` (`date_cymd` CHAR(8)) RETURNS CHAR(4) CHARSET utf8 COLLATE utf8_unicode_ci  BEGIN

	  declare resultx char(4);

	   if substring(date_cymd,5,2) <= 09 then
        set resultx = convert(substring(date_cymd,1,4)+543,char);
     else
        set resultx = convert(substring(date_cymd,1,4)+544,char);
     end if ;

	Return resultx ;


END$$

CREATE DEFINER=`dlict`@`%` FUNCTION `thaiquarter` (`datex` CHAR(8)) RETURNS SMALLINT(6)  BEGIN
   DECLARE resx smallint ;

	if  QUARTER(convert(datex,date))=4 then set resx = 1 ;
	elseif  QUARTER(convert(datex,date))=1 then set resx = 2 ;
  elseif  QUARTER(convert(datex,date))=2 then set resx = 3 ;
	elseif  QUARTER(convert(datex,date))=3 then set resx = 4	 ;
	else set resx = 0 ;
  end if;

	 RETURN resx ;
END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `ymd2cbe` (`date` CHAR(8)) RETURNS CHAR(15) CHARSET utf8 COLLATE utf8_unicode_ci  BEGIN

	DECLARE dayx int;
  DECLARE monthx int;
  DECLARE yearx int;
  DECLARE cmonth varchar(6);
    
    Set dayx =convert(mid(date,7,2),UNSIGNED);
    Set monthx = convert( mid(date,5,2),UNSIGNED);
    SEt yearx = convert(mid(date,1,4),UNSIGNED)+543;
 
    set cmonth = (select case monthx when 1 then 'ม.ค.'
        when 2 then 'ก.พ.'
        when 3 then 'มี.ค.'
        when 4 then 'เม.ย.'
        when 5 then 'พ.ค.'
        when 6 then 'มิ.ย.'
        when 7 then 'ก.ค.'
        when 8 then 'ส.ค.'
        when 9 then 'ก.ย.'
        when 10 then 'ต.ค.'
        when 11 then 'พ.ย.'
        when 12 then 'ธ.ค.'  end);
    
    RETURN   concat(convert(dayx,char), ' ' , cmonth , ' ' ,convert(yearx,char)) ;


END$$

CREATE DEFINER=`dlict`@`%` FUNCTION `ymd2dmy` (`date` CHAR(8)) RETURNS DATETIME  BEGIN
  Declare md Char(4);
  Set md=Right(date,4);
  IF md='0000' then
  Set md='0101' ; END IF ;
   
  RETURN   convert(concat(left(date,4),md),datetime);
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `accr_disp`
--

CREATE TABLE `accr_disp` (
  `REQ_NO` char(12) NOT NULL,
  `WORKING_CODE` char(7) NOT NULL,
  `QTY_REQ` double DEFAULT NULL,
  `PACK_RATIO` double DEFAULT NULL,
  `USER_ID` varchar(30) DEFAULT NULL,
  `REQ_DATE` char(8) DEFAULT NULL,
  `CONFIRM_DATE` char(8) DEFAULT NULL,
  `DEPT_ID` varchar(6) DEFAULT NULL,
  `STOCK_ID` varchar(6) DEFAULT NULL,
  `DIST_NO` char(12) DEFAULT NULL,
  `QTY_DIST` double DEFAULT NULL,
  `DEL_FLAG` char(1) DEFAULT NULL,
  `MOD_SYS` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `accr_disp`
--

INSERT INTO `accr_disp` (`REQ_NO`, `WORKING_CODE`, `QTY_REQ`, `PACK_RATIO`, `USER_ID`, `REQ_DATE`, `CONFIRM_DATE`, `DEPT_ID`, `STOCK_ID`, `DIST_NO`, `QTY_DIST`, `DEL_FLAG`, `MOD_SYS`) VALUES
('6811030009', '8370', 300, 1, '4009', '20251103', NULL, 'DIS01', 'STOCK1', NULL, 0, 'N', 'MED');

-- --------------------------------------------------------

--
-- Table structure for table `add_in_programs`
--

CREATE TABLE `add_in_programs` (
  `ID` int(11) NOT NULL,
  `BAR_CAPTION` varchar(30) DEFAULT NULL,
  `MENU_CAPTION` varchar(30) DEFAULT NULL,
  `ICON` longblob DEFAULT NULL,
  `USER_ID` varchar(35) DEFAULT NULL,
  `ISSUE_DATE` datetime DEFAULT NULL,
  `PROGRAM_DATA` longblob DEFAULT NULL,
  `CHANGE_DATE` datetime DEFAULT NULL,
  `PROGRAM_NAME` varchar(30) DEFAULT NULL,
  `VERSION` float DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `add_in_programs`
--

INSERT INTO `add_in_programs` (`ID`, `BAR_CAPTION`, `MENU_CAPTION`, `ICON`, `USER_ID`, `ISSUE_DATE`, `PROGRAM_DATA`, `CHANGE_DATE`, `PROGRAM_NAME`, `VERSION`) VALUES
(1, 'Fvo', 'นำเข้า HOSxP', 0x[encoded data, 192064 characters], '9013', '2019-06-26 00:00:00', 0x[encoded data, 257881 characters]