生产订单产品主表数据同步 Normal 0 / ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID TRANSNAME Y TRANSNAME STATUS Y STATUS LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS STARTDATE Y STARTDATE ENDDATE Y ENDDATE LOGDATE Y LOGDATE DEPDATE Y DEPDATE REPLAYDATE Y REPLAYDATE LOG_FIELD Y LOG_FIELD EXECUTING_SERVER N EXECUTING_SERVER EXECUTING_USER N EXECUTING_USER CLIENT N CLIENT
ID_BATCH Y ID_BATCH SEQ_NR Y SEQ_NR LOGDATE Y LOGDATE TRANSNAME Y TRANSNAME STEPNAME Y STEPNAME STEP_COPY Y STEP_COPY LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS INPUT_BUFFER_ROWS Y INPUT_BUFFER_ROWS OUTPUT_BUFFER_ROWS Y OUTPUT_BUFFER_ROWS
ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE LOGGING_OBJECT_TYPE Y LOGGING_OBJECT_TYPE OBJECT_NAME Y OBJECT_NAME OBJECT_COPY Y OBJECT_COPY REPOSITORY_DIRECTORY Y REPOSITORY_DIRECTORY FILENAME Y FILENAME OBJECT_ID Y OBJECT_ID OBJECT_REVISION Y OBJECT_REVISION PARENT_CHANNEL_ID Y PARENT_CHANNEL_ID ROOT_CHANNEL_ID Y ROOT_CHANNEL_ID
ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE TRANSNAME Y TRANSNAME STEPNAME Y STEPNAME STEP_COPY Y STEP_COPY LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS LOG_FIELD N LOG_FIELD
ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE METRICS_DATE Y METRICS_DATE METRICS_CODE Y METRICS_CODE METRICS_DESCRIPTION Y METRICS_DESCRIPTION METRICS_SUBJECT Y METRICS_SUBJECT METRICS_TYPE Y METRICS_TYPE METRICS_VALUE Y METRICS_VALUE
0.0 0.0 10000 50 50 N Y 50000 Y N 1000 100 - 2024/01/09 15:07:19.021 - 2024/01/09 15:07:19.021 H4sIAAAAAAAAAAMAAAAAAAAAAAA= N MOM ${mysql_host} MYSQL Native ${mysql_dbname} 3306 ${mysql_username} ${mysql_password} EXTRA_OPTION_MYSQL.characterEncoding utf-8 EXTRA_OPTION_MYSQL.defaultFetchSize 5000 EXTRA_OPTION_MYSQL.rewriteBatchedStatements true EXTRA_OPTION_MYSQL.useCompression true EXTRA_OPTION_MYSQL.useCursorFetch true EXTRA_OPTION_MYSQL.useServerPrepStmts true FORCE_IDENTIFIERS_TO_LOWERCASE N FORCE_IDENTIFIERS_TO_UPPERCASE N IS_CLUSTERED N PORT_NUMBER 3306 PRESERVE_RESERVED_WORD_CASE Y QUOTE_ALL_FIELDS N STREAM_RESULTS N SUPPORTS_BOOLEAN_DATA_TYPE Y SUPPORTS_TIMESTAMP_DATA_TYPE Y USE_POOLING N U8 ${U8DB_HOST} GENERIC Native ${U8DB_NAME} 1433 ${sqlserver_username} ${sqlserver_password} CUSTOM_DRIVER_CLASS ${sqlserver_driverclassname} CUSTOM_URL ${sqlserver_url} DATABASE_DIALECT_ID Generic database FORCE_IDENTIFIERS_TO_LOWERCASE N FORCE_IDENTIFIERS_TO_UPPERCASE N IS_CLUSTERED N PORT_NUMBER 1433 PRESERVE_RESERVED_WORD_CASE Y QUOTE_ALL_FIELDS N SUPPORTS_BOOLEAN_DATA_TYPE Y SUPPORTS_TIMESTAMP_DATA_TYPE Y USE_POOLING N 插入 / 更新 复制记录到结果 Y Java 代码-id 插入 / 更新 Y 表输入 Java 代码-id Y 获取变量 表输入 Y Java 代码-id UserDefinedJavaClass N 1 none TRANSFORM_CLASS Processor public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException { Object[] r = getRow(); if (r == null) { setOutputDone(); return false; } r = createOutputRow(r, data.outputRowMeta.size()); if (snowflakeIdWorker == null) { snowflakeIdWorker = new SnowflakeIdWorker(1, 1); // 设置你的workerId和datacenterId } String id = String.valueOf(snowflakeIdWorker.nextId()); get(Fields.Out, "id").setValue(r, id); putRow(data.outputRowMeta, r); return true; } // 定义全局变量(放在类顶部) private static SnowflakeIdWorker snowflakeIdWorker = null; // 内部类:雪花算法实现 public static class SnowflakeIdWorker { private final long twepoch = 1288834974657L; private final long workerIdBits = 5L; private final long datacenterIdBits = 5L; private final long maxWorkerId = -1L ^ (-1L << workerIdBits); private final long maxDatacenterId = -1L ^ (-1L << datacenterIdBits); private final long sequenceBits = 12L; private final long workerIdShift = sequenceBits; private final long datacenterIdShift = sequenceBits + workerIdBits; private final long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits; private final long sequenceMask = -1L ^ (-1L << sequenceBits); private long workerId; private long datacenterId; private long sequence = 0L; private long lastTimestamp = -1L; public SnowflakeIdWorker(long workerId, long datacenterId) { if (workerId > maxWorkerId || workerId < 0) { throw new IllegalArgumentException(String.format("worker Id can't be greater than %d or less than 0", maxWorkerId)); } if (datacenterId > maxDatacenterId || datacenterId < 0) { throw new IllegalArgumentException(String.format("datacenter Id can't be greater than %d or less than 0", maxDatacenterId)); } this.workerId = workerId; this.datacenterId = datacenterId; } public synchronized long nextId() { long timestamp = timeGen(); if (timestamp < lastTimestamp) { throw new RuntimeException("Clock moved backwards. Refusing to generate id for " + (lastTimestamp - timestamp) + " milliseconds"); } if (lastTimestamp == timestamp) { sequence = (sequence + 1) & sequenceMask; if (sequence == 0) { timestamp = tilNextMillis(lastTimestamp); } } else { sequence = 0L; } lastTimestamp = timestamp; return ((timestamp - twepoch) << timestampLeftShift) | (datacenterId << datacenterIdShift) | (workerId << workerIdShift) | sequence; } private long tilNextMillis(long lastTimestamp) { long timestamp = timeGen(); while (timestamp <= lastTimestamp) { timestamp = timeGen(); } return timestamp; } private long timeGen() { return System.currentTimeMillis(); } } id String -1 -1 N 288 128 Y 复制记录到结果 RowsToResult Y 1 none 592 128 Y 插入 / 更新 InsertUpdate Y 1 none MOM 1000 Y
wms_task_in
interface_id interface_id = task_date task_date = dept_code dept_code = interface_id interface_id N interface_code interface_code N id id N bill_type_code bill_type N task_type task_type N task_date task_date N task_code task_code N dept_code dept_code N create_id sync_id N create_by sync_name N create_time sync_time N 448 128 Y 表输入 TableInput Y 1 none U8 DECLARE @USER_NAME VARCHAR(50),@USER_ID INT,@BEGIN_DATE DATE,@END_DATE DATE,@DEPT_CODE VARCHAR(50) SET @USER_NAME = CASE '${USER_NAME}' WHEN '' THEN '管理员(kettle)' ELSE '${USER_NAME}' END SET @USER_ID = CASE 0${USER_ID} WHEN 0 THEN 1 ELSE 0${USER_ID} END SET @BEGIN_DATE = CASE '${BEGIN_DATE}' WHEN '' THEN CONVERT(VARCHAR(100), GETDATE(), 23) ELSE '${BEGIN_DATE}' END SET @END_DATE = CASE '${BEGIN_DATE}' WHEN '' THEN CONVERT(VARCHAR(100), DATEADD(DAY, 1, GETDATE()), 23) ELSE '${END_DATE}' END SET @DEPT_CODE = '${DEPT_CODE}' SELECT T1.MoId AS interface_id ,T1.MoCode AS interface_code ,1 AS task_type --1入库、-1入库退、-1出库、1出库退 ,1 AS bill_type --1生产订单、2委外订单、3委外到货单、4采购订单、5采购到货单、6销售订单、7销售发货单 ,'IT1-'+T1.MoCode+ CASE T2.RowID WHEN 1 THEN '' ELSE +'-'+CONVERT(VARCHAR,T2.RowID-1) END AS task_code ,T2.StartDate AS task_date ,T2.MDeptCode AS dept_code ,@USER_NAME AS sync_name ,@USER_ID AS sync_id ,GETDATE() AS sync_time FROM mom_order T1 JOIN (SELECT K1.MoId,ISNULL(K1.MDeptCode,'') MDeptCode,CONVERT(DATE,K2.StartDate) StartDate ,RANK()OVER(PARTITION BY K1.MoId ORDER BY ISNULL(K1.MDeptCode,''),CONVERT(DATE,K2.StartDate)) RowID FROM mom_orderdetail K1,mom_morder K2 WHERE K1.MoDId =K2.MoDId AND ISNULL(K1.QualifiedInQty,0) =0 --AND K1.Status =3 --3已审核4关闭 AND ((@DEPT_CODE ='' AND 1=1)OR(@DEPT_CODE ='${DEPT_CODE}' AND 1=1)OR(@DEPT_CODE<>'' AND @DEPT_CODE =ISNULL(K1.MDeptCode,''))) GROUP BY K1.MoId,ISNULL(K1.MDeptCode,''),CONVERT(DATE,K2.StartDate)) T2 ON T1.MoId =T2.MoId WHERE 1=1 --AND T2.Status =3 --3已审核4关闭 --AND EXISTS (SELECT 1 FROM mom_orderdetail T2 WHERE T1.MoId =T2.MoId AND ISNULL(T2.QualifiedInQty,0) =0) AND CONVERT(DATE,T2.StartDate) BETWEEN @BEGIN_DATE AND @END_DATE 0 N Y N N Integer normal interface_id 9 0 表输入 interface_id ####0;-####0 . , none N Y 0 N N N zh_CN Asia/Shanghai N String normal interface_code 30 -1 表输入 interface_code . , none N Y 0 N N N zh_CN Asia/Shanghai N Integer normal task_type 9 0 表输入 task_type ####0;-####0 . , none N Y 0 N N N zh_CN Asia/Shanghai N Integer normal bill_type 9 0 表输入 bill_type ####0;-####0 . , none N Y 0 N N N zh_CN Asia/Shanghai N String normal task_code 65 -1 表输入 task_code . , none N Y 0 N N N zh_CN Asia/Shanghai N String normal task_date 10 -1 表输入 task_date . , none N Y 0 N N N zh_CN Asia/Shanghai N String normal dept_code 12 -1 表输入 dept_code . , none N Y 0 N N N zh_CN Asia/Shanghai N String normal sync_name 50 -1 表输入 sync_name . , none N Y 0 N N N zh_CN Asia/Shanghai N Integer normal sync_id 9 0 表输入 sync_id ####0;-####0 . , none N Y 0 N N N zh_CN Asia/Shanghai N Timestamp normal sync_time 3 -1 表输入 sync_time . , none N Y 0 N N N zh_CN Asia/Shanghai N 144 128 Y 获取变量 GetVariable Y 1 none mysql_driverclassname ${mysql_driverclassname} String -1 -1 none mysql_password ${mysql_password} String -1 -1 none mysql_url ${mysql_url} String -1 -1 none mysql_username ${mysql_username} String -1 -1 none USER_ID ${USER_ID} String -1 -1 none sqlserver_driverclassname ${sqlserver_driverclassname} String -1 -1 none sqlserver_password ${sqlserver_password} String -1 -1 none sqlserver_url ${sqlserver_url} String -1 -1 none sqlserver_username ${sqlserver_username} String -1 -1 none USER_NAME ${USER_NAME} String -1 -1 none BEGIN_DATE ${BEGIN_DATE} String -1 -1 none END_DATE ${END_DATE} String -1 -1 none DEPT_CODE ${DEPT_CODE} String -1 -1 none mysql_host ${mysql_host} String -1 -1 none mysql_dbname ${mysql_dbname} String -1 -1 none 160 576 Y N