跳至主要內容

上传数据

chanchaw大约 6 分钟染厂项目web

概述

线下 windows 服务器制作了后台服务 data_transmitter，用于上传缸单刷卡数据、成品打卷数据，以便在微信H5页面中查询统计。

制作

创建两个表

CREATE TABLE pademisaccount.upload_record (
    iid          INT AUTO_INCREMENT PRIMARY KEY,
    upload_table VARCHAR(30)  DEFAULT ''                NULL COMMENT '上传数据的表名称',
    table_iid    INT          DEFAULT 0                 NULL COMMENT '业务数据表自增主键值',
    create_time  DATETIME     DEFAULT CURRENT_TIMESTAMP NULL COMMENT '创建时间，DB自动填充',
    remark       VARCHAR(100) DEFAULT ''                NULL COMMENT '备注',
    update_count INT          DEFAULT 0                 NULL COMMENT '本批次遍历的行数'
) COMMENT '数据上传的进度'
    COLLATE = utf8_croatian_ci;

CREATE TABLE pademisaccount.upload_process (
    iid           INT AUTO_INCREMENT COMMENT '自增主键' PRIMARY KEY,
    bill_code     VARCHAR(50)  DEFAULT ''                NULL COMMENT '订单单据编号 - 订单主表主键',
    order_iid     INT          DEFAULT 0                 NULL COMMENT '订单明细表主键',
    plan_iid      INT          DEFAULT 0                 NULL COMMENT 'plan主键',
    vat_num       VARCHAR(50)  DEFAULT ''                NULL COMMENT '缸号',
    process_iid   INT          DEFAULT 0                 NULL COMMENT '工序产量表主键',
    process_name  VARCHAR(100) DEFAULT ''                NULL COMMENT '工序名称',
    process_index INT          DEFAULT 0                 NULL COMMENT '工序序号',
    yield_time    DATETIME     DEFAULT CURRENT_TIMESTAMP NULL COMMENT '刷卡时间',
    create_time   DATETIME     DEFAULT CURRENT_TIMESTAMP NULL COMMENT '创建时间，DB自动填充',
    update_time   DATETIME     DEFAULT CURRENT_TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '最后一次修改时间',
    update_level  INT          DEFAULT 0                 NULL COMMENT '更新等级，默认0表示不要更新云端数据，1表示只更新client_pushlist_detail，2表示同时更新client_pushlist_detail 和 client_pushlist'
) COMMENT 'ERP内记录工序刷卡上传'
    COLLATE = utf8_croatian_ci;

创建多个存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `usp_getClientPushlistDetail4JrkBill`(IN vat_num      VARCHAR(20) CHARACTER SET utf8,
                                                                     IN process_name VARCHAR(20) CHARACTER SET utf8,
                                                                     IN dtrk         DATETIME)
BEGIN
    # 2020年6月12日 15:44:49
    # 根据表 g_cjflowbilldetailprocess.b_itemid 构建复合信息
    # 字段匹配云端数据库表 client_pushlist
    SET @vatCount = 0;
    SELECT count(*) INTO @vatCount FROM plan
    WHERE plan.fid = (SELECT fid FROM plan a WHERE a.vatNum = vat_num);

    SET @pairs = 0;
    SET @kilo = 0;
    SELECT B_PiShuFact,B_QtyFact INTO @pairs,@kilo FROM plan b WHERE b.vatNum = vat_num;


    SELECT a.clientId AS client_id,c.name AS client,
           d.B_Name AS mill,a.colorClothName AS product,a.cp_specification AS specification,
           a.color,a.colorNoId AS color_no,@vatCount AS vat_count,process_name AS progress,
           a.vatNum AS vat_num,dtrk AS finish_date,'' AS craft,a.orderNo AS order_no,
           @pairs AS pairs,@kilo AS kilo,b.sId AS orderdtlpk
    FROM plan a
    LEFT JOIN billorderdetail b ON a.fid = b.sId
    LEFT JOIN contactcompany c ON a.clientId = c.sId
    LEFT JOIN g_printingdyeingname d ON 1=1
    WHERE a.vatNum = vat_num
        ;

END

CREATE DEFINER=`root`@`%` PROCEDURE `usp_getUploadJrkStatistics`(IN iid INT)
BEGIN
    # 2020年6月13日 17:06:20
    # 上传打卷数据时的统计数据
    
    SELECT max(B_ItemID) AS maxIId,count(*) AS uploadCount
    FROM g_jrkbill WHERE B_ItemID>iid;
END

CREATE DEFINER=`root`@`%` PROCEDURE `usp_getOrderPlanByVat`(IN vat_num VARCHAR(20) CHARACTER SET utf8,
    process_name VARCHAR(20) CHARACTER SET utf8,dtrk DATETIME)
BEGIN
    # 2020年6月12日 09:54:08
    # 根据表 g_cjflowbilldetailprocess 的主键值查询获取相关的订单、计划信息
    SELECT b.billCode AS bill_code,b.sId AS order_iid,a.id AS plan_iid,a.vatNum AS vat_num,
           NULL AS process_iid,process_name,dtrk AS yield_time
    FROM plan a
    LEFT JOIN billorderdetail b ON a.fid = b.sId
    WHERE a.vatNum = vat_num;

END

CREATE DEFINER=`root`@`%` PROCEDURE `usp_getJrkAfter`(IN iid INT)
BEGIN
    SET @mill = '匿名染厂';
    SELECT B_Name INTO @mill FROM g_printingdyeingname;

    # 2020年6月13日 14:42:16
    # 从ERP表 G_JRKBill 中大于指定主键值的汇总数据 - 按照入库时间汇总
    SELECT plan.vatNum AS vat_num,t1.process_name AS ProcessName,
           plan.clientId AS client_id,c.name AS client,
           plan.cp_specification AS guige,plan.colorClothName AS pinming,
           plan.colorNoId AS sehao,plan.color AS Color,
           t.ps AS PS,t.GJ AS GJ,t.B_MS AS B_MS,plan.craftId AS craft,
           @mill AS mill,t.B_DTRK AS b_DTRk
    FROM (
        SELECT a.B_ID,a.B_DTRK,count(*) AS PS,sum(B_GJ) AS GJ,
               sum(a.B_MS) AS B_MS
        FROM g_jrkbill a
        WHERE a.B_ItemID> iid
        GROUP BY a.B_ID,a.B_DTRK
    ) t
    LEFT JOIN plan ON t.B_ID=plan.id
    LEFT JOIN contactcompany c ON plan.clientId = c.sId
    LEFT JOIN (# 每个缸单的工序列表中取第一个是“打卷”类型的工序名称
        SELECT min(a.sId) as sId,a.vatNum,p.name as process_name
        FROM craftdetail_plan a
        LEFT JOIN process p ON a.processId = p.sId
        WHERE p.isRoll=1
        GROUP BY a.vatNum
    ) t1 ON plan.vatNum = t1.vatNum
    ;
END

CREATE DEFINER=`root`@`%` PROCEDURE `usp_getProcessYieldAfter`(IN process_iid INT)
BEGIN
    # 2020年6月13日 13:37:52
    # 查询表 G_CJFlowBillDetailProcess 主键值大于一定值的所有记录
    # 要过滤掉不需要上传的客户的工序

    SELECT * FROM g_cjflowbilldetailprocess a
    LEFT JOIN plan p ON a.B_ID = p.vatNum
    WHERE a.B_ItemID>process_iid
        AND exists(
            SELECT *  FROM process_mapping b WHERE b.client_id=p.clientId
            AND b.process_name = a.B_ProcessName
        )
        ;
END

CREATE DEFINER=`root`@`%` PROCEDURE `usp_getClientPushlist`(IN process_iid INT)
BEGIN
    # 2020年6月12日 15:44:49
    # 根据表 g_cjflowbilldetailprocess.b_itemid 构建复合信息
    # 字段匹配云端数据库表 client_pushlist
    SET @vatCount = 0;
    SELECT count(*) INTO @vatCount FROM plan
    WHERE plan.fid = (
        SELECT fid FROM plan WHERE vatNum = (SELECT B_ID FROM g_cjflowbilldetailprocess WHERE B_ItemID=process_iid)
        );


    SELECT p.clientId AS client_id,c.name AS client,
        b.B_Name AS mill,p.colorClothName AS product,p.cp_specification AS specification,
        p.color,p.colorNoId AS color_no,@vatCount AS vat_count,a.B_ProcessName AS progress,
        a.B_ID AS vat_num,a.B_Date AS finish_date,p.clientCraft AS craft,p.orderNo AS order_no,
           p.B_PiShuFact AS pairs,p.B_QtyFact AS kilo,p.fid AS orderdtlpk
    FROM g_cjflowbilldetailprocess a
    LEFT JOIN plan p ON a.B_ID = p.vatNum
    LEFT JOIN contactcompany c ON p.clientId = c.sId
    LEFT JOIN g_printingdyeingname b ON 1=1
    WHERE a.B_ItemID=process_iid;
END

CREATE DEFINER=`root`@`%` PROCEDURE `usp_getOrderPlanByProcessIId`(IN iid INT)
BEGIN
    # 2020年6月12日 09:54:08
    # 根据表 g_cjflowbilldetailprocess 的主键值查询获取相关的订单、计划信息
    SELECT b2.billCode AS bill_code, b2.sId AS order_iid, b.id AS plan_iid, a.B_ID AS vat_num,
        a.B_ItemID AS process_iid, a.B_ProcessName AS process_name,a.B_Date AS yield_time
    FROM g_cjflowbilldetailprocess a
             LEFT JOIN plan b ON a.B_ID = b.vatNum
             LEFT JOIN billorderdetail b2 ON b.fid = b2.sId
    WHERE a.B_ItemID = iid;
END

CREATE DEFINER=`root`@`%` PROCEDURE `usp_getProcessByVatnum`(IN vatnum VARCHAR(50) CHARACTER SET utf8)
BEGIN
    # 2020年6月12日 10:19:50
    # 根据缸号查询获取其所有工序 - 带序号
    SELECT a.sId AS plan_process_iid,a.vatNum AS vat_num,a.processId AS process_sid,p.name AS process_name,
           @rownum:=@rownum+1 as seq
    from craftdetail_plan a
    LEFT JOIN process p ON a.processId = p.sId
    LEFT JOIN (select @rownum:=0) seq_table ON 1=1
    WHERE a.vatNum = vatnum
    ORDER BY a.sId;
END

CREATE DEFINER=`root`@`%` PROCEDURE `usp_getOrderPlanByVatnum`(IN vatnum VARCHAR(50) CHARACTER SET utf8)
BEGIN
    # 2020年6月12日 09:04:29
    # 根据缸号查询获取关联的订单、计划单信息
    SELECT b.billCode AS bill_code,b.sId AS order_detail_iid,a.id AS plan_iid,
           a.vatNum AS vat_num
        FROM plan a
    LEFT JOIN billorderdetail b ON a.fid = b.sId
    WHERE 1=1 and a.vatNum = vatnum;
END

开发人员工具

根据刷卡主键查看对应进度

下面的参数 @yieldPK 是ERP中表 G_CJFlowBillDetailProcess 的主键字段的值，第二个参数不用输入，后面会自动填充。脚本作用是查询刷卡产量记录对应的缸号刷卡进度、颜色刷卡进度，由此判断对于云端数据库 client_pushlist 和 client_pushlist_detail 更新一个表或者两个表。

# 根据工序产量表主键查询获得当前缸单的进度和颜色维度的进度
DELIMITER $$
SET @yieldPK = 183002;
SET @orderIId = 123;

SELECT p.fid INTO @orderIId FROM g_cjflowbilldetailprocess a
LEFT JOIN plan p ON a.B_ID = p.vatNum
WHERE a.B_ItemID=@yieldPK;

SELECT a.B_ItemID,a.B_ID AS vat_num,
       a.B_Date,p.sId AS process_sid,p.name AS process_name,
#        @rowIndex:=@rowIndex+1 AS row_index,
       CASE WHEN p.name = a.B_ProcessName THEN '✔' ELSE '' END AS yield_process,
       CASE WHEN p.name = c.process_name THEN '✔' ELSE '' END AS gd_process,
       CASE WHEN p.name = d.process_name THEN '✔' ELSE '' END AS color_process,
       c.order_iid
    FROM g_cjflowbilldetailprocess a
LEFT JOIN craftdetail_plan b ON a.B_ID=b.vatNum
LEFT JOIN process p ON b.processId = p.sId
# LEFT JOIN (SELECT @rowIndex:=0) ri ON 1=1
LEFT JOIN upload_process c ON a.B_ID=c.vat_num

LEFT JOIN (
    SELECT dd.* FROM
    (
        SELECT max(iid) AS iid
        FROM
        (
            SELECT order_iid,max(process_index) AS process_index
            FROM upload_process
            WHERE order_iid=@orderIId
            GROUP BY order_iid
        ) aa
        LEFT JOIN upload_process bb
        ON aa.order_iid = bb.order_iid
        AND aa.process_index = bb.process_index
    ) cc
    LEFT JOIN upload_process dd
    ON cc.iid=dd.iid
) d ON p.name = d.process_name

WHERE a.B_ItemID=@yieldPK
ORDER BY b.sId asc;
DELIMITER ;

手动调整上传进度

表 upload_record 记录了刷卡数据、打卷数据上传的进度，字段 upload_table 填写表名（g_jrkbill,g_cjflowbilldetailprocess），字段 table_iid 填写当前上传到的最后一个主键，下次上传时会继续之后的数据进行上传。

不积跬步无以至千里
Copyright © 2026 chanchaw