Imes 计件工资

   

1.测试机新增表权限

grant delete,insert on jjgzhs to public ;

 

2.正式机执行生成后 插入到测试机

delete imes.jjgzhs@METEST;

INSERT INTO imes.jjgzhs@METEST (user_id, work_shop, work_center, jobs, organization_team, organization_team_desc, post, post_desc, work_date, classes, class_start_time, class_end_time, ycqsc, dbqqsc, operation, shop_order, accrued_qty, real_qty, price_spc, sfcid, item, unit_price, jsdj, jsjjje, jxxs, jjgzje, gdhs, zgsc, sgsj, lgsj, cjssc, sxbclx, cjssxbz, cjskssj, cjsjssj, cjsbcje, tszt, tssjc, ytpzh, zspzh, created_date_time, created_user, modified_date_time, modified_user, object_version_number, hrdksc, cjssxsc, cost_center)

select user_id, work_shop, work_center, jobs, organization_team, organization_team_desc, post, post_desc, work_date, classes, class_start_time, class_end_time, ycqsc, dbqqsc, operation, shop_order, accrued_qty, real_qty, price_spc, sfcid, item, unit_price, jsdj, jsjjje, jxxs, jjgzje, gdhs, zgsc, sgsj, lgsj, cjssc, sxbclx, cjssxbz, cjskssj, cjsjssj, cjsbcje, tszt, tssjc, ytpzh, zspzh, created_date_time, created_user, modified_date_time, modified_user, object_version_number, hrdksc, cjssxsc, cost_center from jjgzhs

commit;

 

 

--抓取 demo产线数据 生成sql到测试区 插入数据

select user_id, work_shop, work_center, jobs, organization_team, organization_team_desc, post, post_desc, work_date, classes, class_start_time, class_end_time, ycqsc, dbqqsc, operation, shop_order, accrued_qty, real_qty, price_spc, sfcid, item, unit_price, jsdj, jsjjje, jxxs, jjgzje, gdhs, zgsc, sgsj, lgsj, cjssc, sxbclx, cjssxbz, cjskssj, cjsjssj, cjsbcje, tszt, tssjc, ytpzh, zspzh, created_date_time, created_user, modified_date_time, modified_user, object_version_number, hrdksc, cjssxsc, cost_center from jjgzhs  

WHERE WORK_CENTER = '11300101';  

用dbeaver 生成sql 后;

--truncate table jjgzhs;

   

       

update CLASS_RECORD_LINE x set start_TIME =(select min(CHECKTIME) from CHECKINOUT c where c.USERID = x.user_id

and c.CHECKTIME between x.stand_start_time-4/24 and x.stand_end_time +2/24 )

/*WHERE x.ENABLED = 1

AND x.MASTER_ID is not null

and x.STAND_START_TIME is not null

and END_TIME is NULL */

WHERE id in ('10805','8115','11580','11630','11862','11554','11677','10538','8494','12001','12002','6550','6660','7070','9505','8035','7664','12052','9047')

       

       

/*更新员工上岗 排班时间*/

       

select x.*, x.rowid

from imes.class_record_line x

/*update IMES.CLASS_RECORD_LINE x

set (x.stand_start_time, x.stand_end_time,x.work_date,x.classes) =

(select c.STAND_START_TIME, c.STAND_END_TIME,c.work_date,c.classes

from CHECKINOUTPLAN c , CLASS_RECORD_MASTER m ,class_record_line line

where c.user_id = x.user_id

and c.work_date = m.work_date

and c.classes = m.classes

and m.id=line.master_id

and line.enabled=1

and line.id=x.id

)*/

where id in ('12777','12779','12788','12789','12568'

,'12569','12570','12571','12576','12580','12584','12595'

,'12599','12606','12613','12615','12623','12627','12560'

,'12630','12664','12676','12679','12688','12705','12706'

,'12708','12791','12798','12802','12820','12823','12835'

,'12838','12844','12845','12863','12882','12887','12897'

,'12900','12901','12911','12913','12914','12917','12739'

,'12746','12754','12756','12759','12760','12769','12772'

,'12927','12928','12930','12725','12944','12779','12679','12820','12823','12911','12927','12928'

)

       

select

USER_ID,

WORK_DATE,

CLASSES,

STAND_START_TIME,

STAND_END_TIME

from IMES.CLASS_RECORD_LINE x

WHERE x.ENABLED = 1

AND x.MASTER_ID is not null

and x.STAND_START_TIME is null

order by WORK_DATE

       

update CLASS_RECORD_LINE x set ( STAND_START_TIME,

STAND_END_TIME ) =(select STAND_START_TIME,

STAND_END_TIME from CHECKINOUTPLAN c where c.user_id = x.user_id

and c.work_date = x.work_date

and c.classes = x.classes )

WHERE x.ENABLED = 1

AND x.MASTER_ID is not null

and x.STAND_START_TIME is null

       

insert into CHECKINOUT (USERID,    CHECKTIME,    WORK_CENTER

)

       

select USERID,to_date (    CHECKTIME ,'yyyy-mm-dd hh24:mi:ss') ,    WORK_CENTER

from T31 t

select * from SHOP_ORDER_TIME t where 人工工时=0 or 生产角色 is null