dev 数据清洗

P15GEN2\59518

2025-10-18 56638c01bb2cc61a92f5e03c9a1001be5b5d3699

dev 数据清洗

 ai/WebRoot/WEB-INF/config/config.xml

@@ -25,6 +25,7 @@
    <param name = "PathInterfaceLog" value = "D:/repository/log/" remark="接口信息存放目录"/>
    <param name = "PathVersion" value = "D:/repository/version/" remark="版本文件存放目录"/>
    <param name = "PathWebTemp" value = "D:/server/nginx-1.16.1/html/dist/" remark="前端页面存放目录"/>
    <param name="ServerName" value=""http://ai.highdatas.com/"  remark="服务地址" />
    

</config>

 ai/WebRoot/WEB-INF/config/server.xml

@@ -102,6 +102,9 @@
        <initializer name="code" classname="biz.code.CodeLoader" active="T"/>
         <initializer name="stateMachine" classname="foundation.state.StateMachineLoader" active="F"/>
          <initializer name="io" classname="foundation.io.IOLoader" active="T"/>
          <initializer name="json" classname="foundation.dao.JSONPackageLoader" active="T"/>
          <initializer name="icall" classname="foundation.icall.ICallLoader" active="T"/>
          <initializer name="clean" classname="biz.clean.CleanLoader" active="T"/>
        <initializer name="dbAdapter" classname="foundation.persist.adapter.DBAdapterLoader" active="T"/>
    </initializers>
    

 ai/WebRoot/WEB-INF/sql/dataio-interface.xml

@@ -2,929 +2,26 @@
    
<sqls>
    <dataSpace name="interface-io-account">
        <sql name="interfaceUpdateGspId">
        <sql name="importUpdateTaskId">
            <![CDATA[
             update md_org_account 
             set gsp_id = (
                 select distinct gsp_id from temp_md_org_account 
                 where temp_md_org_account.data_id = md_org_account.id
             update ocr_apply 
             set baidu_task_id = (
                 select distinct baidu_task_id from temp_ocr_apply
                 where temp_ocr_apply.data_id = ocr_apply.id order by update_time desc limit 1
             )
             where id = '@{docId}'
            ]]>
        </sql>
    </dataSpace>
	
    <dataSpace name="interface-io-employee">
        <sql name="interfaceAppendNewEmployee">
        <![CDATA[
            INSERT INTO md_employee (id, idx, org_id, org_name, account_type_code, actor_code, code, name,   job_title, seniority_level, mail, phone, secret, is_error, error_message, is_active, creator_id, creator_name, create_time, update_time) 
            select id, null idx, '1181'org_id, '第一三共' org_name, '主账号' account_type_code, null actor_code, employee_code code, name,   position_level job_title, null seniority_level, email mail, mobile phone, null secret, 'F'is_error, null error_message, case when organization_employee.employ_status = 'ON' then 'T' else 'F' end as is_active, 'Admin'creator_id, 'Admin' creator_name, now() create_time, now() update_time from dscn_crm.organization_employee  
            where not exists (select 1 from md_employee where md_employee.id = organization_employee.id)
        ]]>
        </sql>
        </sql> 
        
        <sql name="interfaceUpdateEmployeeInfo">
        <![CDATA[
            UPDATE md_employee
            inner join temp_md_employee on temp_md_employee.id = md_employee.id 
            set md_employee.org_id = temp_md_employee.org_id, md_employee.org_name = temp_md_employee.org_name,
            md_employee.account_type_code = temp_md_employee.account_type_code, md_employee.actor_code = temp_md_employee.actor_code,
            md_employee.name = temp_md_employee.name, md_employee.job_number = temp_md_employee.job_number,
            md_employee.avatar = temp_md_employee.avatar, md_employee.job_title = temp_md_employee.job_title,
            md_employee.mail = temp_md_employee.mail, md_employee.org_mail = temp_md_employee.org_mail,
            md_employee.dingtalk_union_id = temp_md_employee.dingtalk_union_id, 
            md_employee.phone = temp_md_employee.phone, 
            md_employee.dingtalk_workplace = temp_md_employee.dingtalk_workplace,
            md_employee.telephone = temp_md_employee.telephone,md_employee.phone_area_code = temp_md_employee.phone_area_code,
            md_employee.dept_order = temp_md_employee.dept_order,
            md_employee.is_active = temp_md_employee.is_active, md_employee.is_admin = temp_md_employee.is_admin,
            md_employee.is_boss = temp_md_employee.is_boss, md_employee.is_leader = temp_md_employee.is_leader,
            md_employee.is_exclusive_account = temp_md_employee.is_exclusive_account, md_employee.remark = temp_md_employee.remark, 
        ]]>
        </sql>
		
        <sql name="interfaceAppendEmployeeDepartmentToTemp">
        <![CDATA[
            INSERT INTO temp_md_employee_department (id, idx, department_id,  employee_id, remark) 
            select md5(UUID_SHORT()) id, null idx, md_department.id department_id, employee_id,  md_department.`name` remark
            from (
                select temp_md_employee.id employee_id, replace(SUBSTRING_INDEX(SUBSTRING_INDEX(dingtalk_department_ids,',',b.help_topic_id+1),',',-1),' ','') department_id  
            from temp_md_employee  
            left join mysql.help_topic b ON b.help_topic_id<(length(temp_md_employee.dingtalk_department_ids)-length(REPLACE(dingtalk_department_ids,',',''))+1) ) v
            left join md_department on md_department.dingtalk_id = v.department_id
        ]]>
        </sql>
		
        <sql name="interfaceAppendExistsEmployeeDepartment">
        <![CDATA[
            INSERT INTO md_employee_department (id, idx, department_id,  employee_id, remark) 
            select temp_md_employee_department.id, temp_md_employee_department.idx, temp_md_employee_department.department_id,  
            temp_md_employee_department.employee_id, temp_md_employee_department.remark 
            from temp_md_employee_department 
            left join md_employee_department on md_employee_department.employee_id = temp_md_employee_department.employee_id 
                    and md_employee_department.department_id = temp_md_employee_department.department_id 
            where md_employee_department.id is null
        ]]>
        </sql>
		
        <sql name="interfaceDeleteNotExistsEmployeeDepartment">
        <![CDATA[
            delete from md_employee_department
            where exists ( 
                    select 1 from temp_md_employee_department
                    where md_employee_department.employee_id = temp_md_employee_department.employee_id ) 
                AND not exists (
                    select 1 from temp_md_employee_department
                    where md_employee_department.department_id = temp_md_employee_department.department_id 
                        and  md_employee_department.employee_id = temp_md_employee_department.employee_id )
        ]]>
        </sql>
		
        <sql name="interfaceAppendNewUser">
        <![CDATA[
            insert into sys_user ( id, org_id, code, name, password, pass_need_change,
                is_active, remark, create_time, update_time ) 
            select @{guid} id, max(md_employee.org_id) org_id, md_employee.code, md_employee.code name, 'HRE1TIUAjNDU2DS' password,
                  'F' pass_need_change, 'T' is_active, max(md_employee.name) remark,
                 max(md_employee.create_time) create_time, max(md_employee.update_time) update_time 
             from md_employee  
             where md_employee.is_active = 'T' and not exists (
                  select 1 from sys_user where md_employee.code = sys_user.code and sys_user.is_active = 'T' 
                 )
             group by md_employee.code
        ]]>
        </sql>	
		
        <sql name="interfaceUpdateInvalidUser">
        <![CDATA[		
            update sys_user 
            set is_active = 'F' 
             where exists (select 1 from md_org where md_org.is_master = 'T' and md_org.id = sys_user.org_id ) and not exists (
                 select 1 from md_employee 
                 where md_employee.code = sys_user.code 
                     and sys_user.is_active = 'T'  
                      and  md_employee.is_active = 'T' 
             )
        ]]>
        </sql>	
		
        <sql name="interfaceEmptyNotExistsEmployeePosition">
        <![CDATA[
            delete from md_position_employee
            where not exists (
                select 1 from md_employee
                where md_employee.id = md_position_employee.employee_id
                        and md_employee.is_active = 'T'
                ) and id != 'admin'
        ]]>
        </sql>
		
        <sql name="interfaceInValidNotExistsEmployeeUser">
        <![CDATA[
            delete from sys_user_employee
            where not exists (
                select 1 from md_employee
                where md_employee.id = sys_user_employee.employee_id
                        and md_employee.is_active = 'T'
                ) and id != 'admin'
        ]]>
        </sql>
		
        <sql name="interfaceAppendNewEmployeeUser">
        <![CDATA[
            insert into sys_user_employee (id, user_id,employee_id)
            select @{guid} id, sys_user.id user_id, md_employee.id
            from sys_user
            inner join md_employee on md_employee.code = sys_user.code
            left join sys_user_employee on md_employee.id = sys_user_employee.employee_id 
                and md_employee.is_active = 'T' and sys_user_employee.user_id = sys_user.id
            where sys_user_employee.id is null and md_employee.is_active = 'T' and sys_user.is_active = 'T'
        ]]>
        </sql>
		
        <sql name="interfaceSetEmployeeExternalCode">
        <![CDATA[
            update md_employee
            SET external_code = (select mirror_gsp_v_rs_ry_jbxx.zybm from mirror_gsp_v_rs_ry_jbxx
                where md_employee.name  = mirror_gsp_v_rs_ry_jbxx.zymc)
            where exists (select 1 from mirror_gsp_v_rs_ry_jbxx
                where md_employee.name  = mirror_gsp_v_rs_ry_jbxx.zymc)
        ]]>
        </sql>
		
        <sql name="interfaceSetEmployeeGspId">
        <![CDATA[
            update temp_md_employee_gsp 
            set id = ifnull((select md_employee_gsp.id from md_employee_gsp 
                where md_employee_gsp.code = temp_md_employee_gsp.code), @{guid}) 
        ]]>
        </sql>
		
    </dataSpace>
	
    <dataSpace name="interface-io-department">
        <sql name="interfaceAppendNewDepartment">
        <sql name="importUpdateBaiduResult">
            <![CDATA[
            INSERT INTO md_department (id, idx, parent_id, dingtalk_id, create_time, update_time, is_active)  
            select md5(UUID_SHORT()) id,null idx, '@{parentId}' parent_id, dingtalk_id, now() create_time, now()  update_time,'T' is_active 
            from (
                select distinct parent_id, replace(SUBSTRING_INDEX(SUBSTRING_INDEX(temp_child_dept,',',b.help_topic_id+1),',',-1),' ','') dingtalk_id  
                from temp_md_department  
                left join mysql.help_topic b ON b.help_topic_id<(length(temp_child_dept)-length(REPLACE(temp_child_dept,',',''))+1)
                where @{IfEmpty}(temp_child_dept,'') != '' ) v 
            where not exists (select 1 from md_department  where md_department.dingtalk_id = v.dingtalk_id) 
             update ocr_apply 
             set baidu_file_url = (
                 select baidu_file_url from temp_ocr_apply
                 where temp_ocr_apply.data_id = ocr_apply.id and temp_ocr_apply.baidu_file_url is not null order by update_time desc limit 1
             )
             where id = '@{docId}'
            ]]>
        </sql>
		
        <sql name="interfaceSetDepartmentInfo">
            <![CDATA[
            update md_department 
            inner join temp_md_department on temp_md_department.dingtalk_id = md_department.dingtalk_id
            set md_department.code = temp_md_department.code, 
                md_department.name = temp_md_department.name, 
                md_department.duty = temp_md_department.duty, 
                md_department.level_code = temp_md_department.level_code, 
                md_department.dept_manager_userid_list = temp_md_department.dept_manager_userid_list 
            ]]>
        </sql>
		
    </dataSpace>
	
    <dataSpace name="interface-io-product">
        <sql name="interfaceAppendNewProdcuct">
        <![CDATA[
            INSERT INTO md_product (id, idx, gsp_id, code, name, price, register_no, remark, state_code, state_name)  
            select md5(UUID_SHORT())id, null idx, cplb gsp_id , concat('P0000', code_next_sequence('Code-Product')) code,cpmc name, null price,
            ylzd1 register_no, CONCAT(cpmc, '（注册证编号：',ylzd1,'）') remark, 'Open' state_code, '生效'state_name from mirror_gsp_v_cpxx
            where not exists (
            select 1 from md_product
            where  mirror_gsp_v_cpxx.cplb = md_product.gsp_id)
            group by cplb
        ]]>
        </sql>
		
        <sql name="interfaceUpdateProdcuct">
        <![CDATA[
            update md_product
            set name = ( 
                    select distinct cpmc
                    from mirror_gsp_v_cpxx
                    where mirror_gsp_v_cpxx.cplb = md_product.gsp_id ),
                register_no = ( 
                    select distinct ylzd1
                    from mirror_gsp_v_cpxx
                    where mirror_gsp_v_cpxx.cplb = md_product.gsp_id )
            where EXISTS (select 1 from mirror_gsp_v_cpxx
                where mirror_gsp_v_cpxx.cplb = md_product.gsp_id)
        ]]>
        </sql>
		
        <sql name="interfaceAppendNewSku">
        <![CDATA[
            INSERT INTO md_prod_sku ( 
                id, idx, gsp_id, gsp_code, product_id, product_code, product_name, spec, 
                package_unit, md_prod_unit.name unit, is_active, gsp_category, register_no, registrant_name, 
                record_person, bar_code, device_category, quality_category, material_name, 
                category_third, description, price_refer_cost, price_refer_sale, tax_rate_purchase, tax_rate_sale, tax_classification_no, 
                supplier_name, manufacturer_name, manufacturer_license_no, manufacturer_phone, manufacturer_address, 
                is_active_batch_no, is_active_sn, storage_condition,  remark, create_time, update_time) 
            select @{guid} id, null idx, cpid gsp_id, cpbm gsp_code, md_product.id product_id, md_product.code product_code, cpmc product_name,
                cpxh spec, zxgg package_unit,  jldw unit, if(cpzt, 'T','F') is_active, cplb gsp_category, 
                ylzd1 register_no, cpxxcjr registrant_name, cpxxcjr record_person, null bar_code, qxfl device_category, zgfl quality_category, clmcdh material_name, null category_third, cpms description,cbj price_refer_cost, csj price_refer_sale, jxse tax_rate_purchase, if(xxsl = '', 0.00, xxsl) tax_rate_sale, ssflbm tax_classification_no,gys supplier, ylzd2 manufacturer_name, ylzd3 manufacturer_license_no,null manufacturer_phone, ylzd4 manufacturer_address, 'T'is_active_batch_no,'T' is_active_sn, cctj storage_condition, yt remark, now() create_time,now() update_time
            from  mirror_gsp_v_cpxx 
            inner join md_product on md_product.gsp_id = mirror_gsp_v_cpxx.cplb
            left join md_prod_unit on md_prod_unit.id = mirror_gsp_v_cpxx.jldw
            where not EXISTS (
            select 1 from md_prod_sku 
            where CONCAT(mirror_gsp_v_cpxx.cpid,'') = md_prod_sku.gsp_id )
        ]]>
        </sql>
		
        <sql name="interfaceUpdateSku">
        <![CDATA[
            update md_prod_sku
            inner join mirror_gsp_v_cpxx on CONCAT(mirror_gsp_v_cpxx.cpid,'') = md_prod_sku.gsp_id
            inner join md_product on md_product.gsp_id = mirror_gsp_v_cpxx.cplb
            left join md_prod_unit on md_prod_unit.id = mirror_gsp_v_cpxx.jldw
            set gsp_code = cpbm, 
            product_id = md_product.id, 
            product_code = md_product.code,
            product_name =  cpmc , spec = cpxh, package_unit = zxgg,  
            unit = md_prod_unit.name , 
            is_active = if(cpzt, 'T','F') ,
            gsp_category =  cplb, 
            register_no = ylzd1, registrant_name = cpxxcjr, record_person= cpxxcjr,
             device_category = qxfl, quality_category = zgfl, material_name = clmcdh,
             description = cpms ,price_refer_cost = cbj , price_refer_sale = csj, 
             tax_rate_purchase = jxse, tax_rate_sale = if(xxsl = '', 0.00, xxsl)  ,  tax_classification_no = ssflbm,supplier_name = gys, manufacturer_name = ylzd2, manufacturer_license_no = ylzd3 , manufacturer_address = ylzd4,  storage_condition = cctj, remark = yt
        ]]>
        </sql>
    </dataSpace>

    <dataSpace name="interface-io-oa">
        <sql name="interfaceUpdateOAId">
            <![CDATA[
                 update sys_state_working_user 
                 set oa_id = (
                     select distinct oa_id from temp_sys_state_working_user 
                     where temp_sys_state_working_user.id = sys_state_working_user.id and oa_id is not null
                 )
                 where id = '@{docId}'
            ]]>
        </sql>
    </dataSpace>
	
    <dataSpace name="interface-io-order">
        <sql name="interfaceUpdateOrderCustomerId">
            <![CDATA[
                update temp_so_order
                inner join md_org_account on md_org_account.gsp_id = temp_so_order.customer_external_id 
                set temp_so_order.org_id = md_org_account.org_id,
                    customer_id = md_org_account.id ,
                    customer_code = md_org_account.code,
                    customer_name = md_org_account.account_name
                where temp_so_order.customer_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderWarehouseId">
            <![CDATA[
                update temp_so_order
                inner join wm_warehouse on wm_warehouse.account_id = temp_so_order.customer_id and wm_warehouse.type_code = 'Master' 
                set warehouse_id = wm_warehouse.id,
                    warehouse_code = wm_warehouse.code,
                    warehouse_name = wm_warehouse.name
                where warehouse_code is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderPositionEmployeeName">
            <![CDATA[
                update temp_so_order 
                inner join md_position_gsp on temp_so_order.position_employee_external_id = md_position_gsp.gsp_employee_code  
                inner join md_position on md_position.id = md_position_gsp.position_id
                inner join md_position_employee on md_position_employee.position_id = md_position.id
                SET temp_so_order.position_employee_name = md_position_employee.remark,
                    temp_so_order.position_id = md_position.id ,
                    temp_so_order.position_name = md_position.name,
                    temp_so_order.position_region = md_position.region,
                    temp_so_order.position_employee_id = md_position_employee.employee_id
                where temp_so_order.position_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderPositionHierarchy">
            <![CDATA[
                UPDATE temp_so_order
                INNER JOIN md_position_hierarchy ON md_position_hierarchy.position_id = temp_so_order.position_id 
                SET temp_so_order.position_cn_id = md_position_hierarchy.level1,
                temp_so_order.position_region_id = md_position_hierarchy.level2,
                temp_so_order.position_area_id = md_position_hierarchy.level3
                where temp_so_order.position_cn_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderState">
            <![CDATA[
                update temp_so_order
                set state_name = '已出库'  
                where state_code = '2' and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderDetailParentId">
            <![CDATA[
                update temp_so_order_detail
                SET temp_so_order_detail.parent_id = (select temp_so_order.id from temp_so_order
                    where temp_so_order.external_id = temp_so_order_detail.parent_external_id and temp_so_order.io_batch_id = '@{ioBatchId}')
                where temp_so_order_detail.parent_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderDetailSku">
            <![CDATA[
                update temp_so_order_detail 
                inner join v_prod_sku on v_prod_sku.gsp_id = temp_so_order_detail.sku_external_id 
                SET temp_so_order_detail.bu_id = v_prod_sku.bu_id , 
                    temp_so_order_detail.bu_name = v_prod_sku.bu_name, 
                    temp_so_order_detail.sku_id = v_prod_sku.id , 
                    temp_so_order_detail.product_id = v_prod_sku.product_id, 
                    temp_so_order_detail.product_code = v_prod_sku.product_code, 
                    temp_so_order_detail.product_name = v_prod_sku.product_name, 
                    temp_so_order_detail.spec = v_prod_sku.spec, 
                    temp_so_order_detail.unit = v_prod_sku.unit,
                    temp_so_order_detail.batch_sn = case when v_prod_sku.is_active_sn = 'T' then batch_sn else '--' end ,
                    temp_so_order_detail.batch_no = if(ifnull(batch_no, '') = '', if(ifnull(batch_sn ,'') = '', '--', batch_sn), batch_no) 
                 where temp_so_order_detail.sku_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderDetailBu">
            <![CDATA[
                update temp_so_order_detail 
                inner join (
                    select parent_id order_id, max(bu_id) bu_id 
                    from temp_so_order_detail where temp_so_order_detail.io_batch_id = '@{ioBatchId}' group by parent_id 
                ) order_bu on order_bu.order_id = temp_so_order_detail.parent_id 
                left join md_bu on md_bu.id = @{IfEmpty}(order_bu.bu_id, 'CP')
                SET temp_so_order_detail.bu_id = md_bu.id , 
                    temp_so_order_detail.bu_name = md_bu.name
                 where temp_so_order_detail.bu_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderPeriod">
            <![CDATA[
                update temp_so_order
                inner join md_peroid on md_peroid.date_from <= temp_so_order.doc_date and md_peroid.date_to >= temp_so_order.doc_date
                set temp_so_order.year = md_peroid.year, temp_so_order.quarter = md_peroid.quarter, temp_so_order.month = md_peroid.month 
                where temp_so_order.year is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderDetailState">
            <![CDATA[
                update temp_so_order_detail 
                set state_name = '已出库' 
                where state_code = '2' and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderCustomer">
            <![CDATA[
                update so_order
                inner join md_org_account on md_org_account.gsp_id = so_order.customer_external_id 
                set so_order.org_id = md_org_account.org_id,
                    so_order.customer_id = md_org_account.id ,
                    so_order.customer_code = md_org_account.code,
                    so_order.customer_name = md_org_account.account_name
                where so_order.customer_id is null 
            ]]>
        </sql>
		
        <sql name="interfaceComplementOrderParentId">
            <![CDATA[
                update so_order_detail
                inner join so_order on so_order.external_id = so_order_detail.parent_external_id 
                set so_order_detail.parent_id = so_order.id
                where so_order_detail.parent_id is null 
            ]]>
        </sql>
		
        <sql name="interfaceOrderWriteFlow">
            <![CDATA[
                INSERT INTO wm_book_flow (id, idx, batch_mark, record_operator, doc_detail_id, 
                    document_doc_type, config_doc_type, doc_type, doc_code, doc_date, book_date, bu_id, bu_name, 
                    org_id, org_code, org_name, account_id, warehouse_id, warehouse_name, warehouse_code, 
                    category_code, category_name, sku_id, product_id, product_code, product_name, spec, 
                    batch_no, batch_sn, valid_from, valid_to, document_order_right, config_order_right, order_right_code, 
                    order_right_name, document_stock_type, config_stock_type, stock_type_code, stock_type_name, 
                    qty_add, type_code, type_name, create_time, update_time)
                select md5(UUID_SHORT()) id, null idx, sys_interface_log.id batch_mark, 'insert' record_operator, 
                    so_order_detail.id doc_detail_id, '普通销售入库' document_doc_type,'采购单' config_doc_type, '普通销售入库'doc_type, 
                    so_order.code doc_code, so_order.doc_date, CURRENT_DATE book_date, so_order_detail.bu_id, 
                    so_order_detail.bu_name, so_order.org_id org_id, null org_code, so_order.customer_name org_name, 
                    so_order.customer_id account_id, so_order.warehouse_id, so_order.warehouse_name, 
                    so_order.warehouse_code, null category_code, null category_name, so_order_detail.sku_id,  
                    so_order_detail.product_id, so_order_detail.product_code, so_order_detail.product_name, 
                    so_order_detail.spec, 
                    if(@{IfEmpty}(so_order_detail.batch_no,'')= '', if(@{IfEmpty}(so_order_detail.batch_sn,'')= '', '--', so_order_detail.batch_sn), so_order_detail.batch_no) batch_no, 
                    if(@{IfEmpty}(so_order_detail.batch_sn,'')= '', '--', so_order_detail.batch_sn) batch_sn,
                    so_order_detail.valid_from, so_order_detail.valid_to, 'normal' document_order_right, 
                    'Normal' config_order_right, 'normal'order_right_code, '标准库存' order_right_name, 'Standard'document_stock_type, 
                    'Standard' config_stock_type, 'Standard'stock_type_code, '标准库存' stock_type_name, so_order_detail.qty qty_add, 
                    'Master'type_code, '主仓' type_name, now() create_time, now() update_time 
                from so_order_detail 
                inner join so_order on so_order.id = so_order_detail.parent_id
                left join (select id from sys_interface_log 
                    where sys_interface_log.interface_id = 'gsp-2-04-v_jxcxskd' order by create_time desc limit 1) sys_interface_log on 1=1
                where so_order.state_name = '已出库' and so_order_detail.state_name = '已出库' and so_order.customer_id is not null and so_order_detail.parent_id 
            ]]>
        </sql>
		
        <sql name="interfaceOrderWriteFlowOrg">
            <![CDATA[
                update wm_book_flow 
                SET org_code = (select code from md_org where md_org.id = wm_book_flow.org_id )
                WHERE batch_mark = (select id from sys_interface_log 
                    where sys_interface_log.interface_id = 'gsp-2-04-v_jxcxskd' order by create_time desc limit 1)
            ]]>
        </sql>
		
        <sql name="interfaceOrderStockOperator">
            <![CDATA[
                update wm_book_flow
                inner join wm_book_detail on wm_book_detail.account_id = wm_book_flow.account_id 
                    and wm_book_detail.warehouse_id = wm_book_flow.warehouse_id 
                    and wm_book_detail.batch_no = wm_book_flow.batch_no 
                    and wm_book_detail.batch_sn = wm_book_flow.batch_sn 
                    and wm_book_detail.sku_id = wm_book_flow.sku_id 
                SET book_detail_id = wm_book_detail.id, record_operator = 'update'
                WHERE wm_book_flow.batch_mark = (select id from sys_interface_log
                    where sys_interface_log.interface_id = 'gsp-2-04-v_jxcxskd' order by create_time desc limit 1)
            ]]>
        </sql>
		
        <sql name="interfaceOrderAddStock">
            <![CDATA[
                INSERT INTO wm_book_detail (id, idx, batch_mark, org_id, org_code, org_name, account_id, 
                    warehouse_id, warehouse_code, warehouse_name, bu_id, bu_name, sku_id, product_id, product_code, product_name, spec,  
                    batch_no, batch_sn, valid_from, valid_to, order_right_code, order_right_name, stock_type_code, stock_type_name, 
                    qty_total, qty_frozen, qty_available, type_code, type_name, create_time, update_time) 
                select 
                    MD5(UUID_SHORT()) id, null idx, batch_mark, org_id, org_code, org_name, account_id, warehouse_id, 
                    warehouse_code, warehouse_name, bu_id, bu_name, sku_id, product_id, product_code, product_name, spec,  
                    batch_no, batch_sn, valid_from, valid_to, order_right_code, order_right_name, stock_type_code, stock_type_name, 
                    sum(qty_add) qty_total, 0 qty_frozen, sum(qty_add) qty_available, type_code, type_name, create_time, update_time 
                from wm_book_flow 
                where batch_mark = (select id from sys_interface_log where sys_interface_log.interface_id = 'gsp-2-04-v_jxcxskd' 
                    order by create_time desc limit 1) and record_operator = 'insert'
                group by warehouse_id, account_id, bu_id, sku_id, batch_no, batch_sn,`order_right_code`, `stock_type_code`
            ]]>
        </sql>
		
        <sql name="interfaceOrderUpdateStock">
            <![CDATA[
                update wm_book_detail
                inner join (select book_detail_id id , sum(qty_add) qty_change from wm_book_flow 
                where batch_mark = (select id from sys_interface_log 
                    where sys_interface_log.interface_id = 'gsp-2-04-v_jxcxskd' order by create_time desc limit 1) 
                    and record_operator = 'update'
                    group by book_detail_id ) v_book_detail on v_book_detail.id = wm_book_detail.id
                set qty_total = qty_total + qty_change, qty_available = qty_available + qty_change
            ]]>
        </sql>
		
        <sql name="interfaceSetOrderOpenState">
            <![CDATA[
                update so_order 
                set state_code = 'Open', state_name = '已完成'
                where state_name = '已出库' and so_order.customer_id is not null and exists (select 1 from so_order_detail where so_order_detail.parent_id = so_order.id)
            ]]>
        </sql>
		
        <sql name="interfaceSetOrderDetailOpenState">
            <![CDATA[
                update so_order_detail 
                set state_code = 'Open', state_name = '已完成'
                where state_name = '已出库' and so_order.customer_id is not null
            ]]>
        </sql>
    </dataSpace>
	
    <dataSpace name="interface-io-orderReturn">	
        <sql name="interfaceUpdateOrderReturnCustomerId">
            <![CDATA[
                update temp_so_order_return
                inner join md_org_account on md_org_account.gsp_id = temp_so_order_return.customer_external_id 
                set customer_id = md_org_account.id ,
                customer_code = md_org_account.code,
                customer_name = md_org_account.account_name ,
                temp_so_order_return.org_id = md_org_account.org_id
                where temp_so_order_return.customer_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderReturnWarehouseId">
            <![CDATA[
                update temp_so_order_return
                inner join wm_warehouse on wm_warehouse.account_id = temp_so_order_return.customer_id and wm_warehouse.type_code = 'Master' 
                set warehouse_id = wm_warehouse.id, warehouse_code = wm_warehouse.code, warehouse_name = wm_warehouse.name
                where warehouse_code is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderReturnState">
            <![CDATA[
                update temp_so_order_return set state_name = '已出库'  
                where state_code = '2' and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderReturnDetailParentId">
            <![CDATA[
                update temp_so_order_return_detail
                SET temp_so_order_return_detail.parent_id = (select temp_so_order_return.id from temp_so_order_return
                    where temp_so_order_return.external_id = temp_so_order_return_detail.parent_external_id and temp_so_order_return.io_batch_id = '@{ioBatchId}')
                where temp_so_order_return_detail.parent_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderReturnDetailOrderId">
            <![CDATA[
                update temp_so_order_return_detail 
                SET temp_so_order_return_detail.order_id = (select temp_so_order.id from temp_so_order
                    where temp_so_order.external_id = temp_so_order_return_detail.order_external_id)
                where temp_so_order_return_detail.order_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderReturnDetailOrderDetailId">
            <![CDATA[
                update temp_so_order_return_detail 
                SET temp_so_order_return_detail.order_detail_id = (select temp_so_order_detail.id from temp_so_order_detail
                    where temp_so_order_detail.external_id = temp_so_order_return_detail.order_detail_external_id)
                where temp_so_order_return_detail.order_detail_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderReturnDetailSku">
            <![CDATA[
                update temp_so_order_return_detail 
                inner join v_prod_sku on v_prod_sku.gsp_id = temp_so_order_return_detail.sku_external_id  
                SET temp_so_order_return_detail.bu_id = v_prod_sku.bu_id , 
                    temp_so_order_return_detail.bu_name = v_prod_sku.bu_name,
                    temp_so_order_return_detail.sku_id = v_prod_sku.id , 
                    temp_so_order_return_detail.product_id = v_prod_sku.product_id, 
                    temp_so_order_return_detail.product_code = v_prod_sku.product_code, 
                    temp_so_order_return_detail.product_name = v_prod_sku.product_name, 
                    temp_so_order_return_detail.spec = v_prod_sku.spec, 
                    temp_so_order_return_detail.unit = v_prod_sku.unit ,
                    temp_so_order_return_detail.batch_sn = case when v_prod_sku.is_active_sn = 'T' then batch_sn else '--' end ,
                    temp_so_order_return_detail.batch_no = if(ifnull(batch_no, '') = '', if(ifnull(batch_sn ,'') = '', '--', batch_sn), batch_no) 
                where temp_so_order_return_detail.sku_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderReturnDetailBu">
            <![CDATA[
                update temp_so_order_return 
                inner join (
                    select parent_id order_id, max(bu_id) bu_id 
                    from temp_so_order_return_detail where temp_so_order_return_detail.io_batch_id = '@{ioBatchId}' group by parent_id 
                ) order_bu on order_bu.order_id = temp_so_order_detail.parent_id 
                left join md_bu on md_bu.id = @{IfEmpty}(order_bu.bu_id, 'CP')
                SET temp_so_order_return_detail.bu_id = md_bu.id , 
                    temp_so_order_return_detail.bu_name = md_bu.name
                 where temp_so_order_return_detail.bu_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderReturnPeriod">
            <![CDATA[
                update temp_so_order_return 
                inner join md_peroid on md_peroid.date_from <= temp_so_order_return.doc_date and md_peroid.date_to >= temp_so_order_return.doc_date
                set temp_so_order_return.year = md_peroid.year, temp_so_order_return.quarter = md_peroid.quarter, temp_so_order_return.month = md_peroid.month 
                where temp_so_order_return.year is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderReturnDetailState">
            <![CDATA[
                update temp_so_order_return_detail 
                set state_name = '已出库'
                where state_code = '2' and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceComplementOrderReturnParentId">
            <![CDATA[
                update so_order_return_detail
                inner join so_order_return on so_order_return.external_id = so_order_return_detail.parent_external_id 
                set so_order_return_detail.parent_id = so_order_return.id
                where so_order_return_detail.parent_id is null 
            ]]>
        </sql>
		
        <sql name="interfaceOrderReturnWriteFlow">
            <![CDATA[
                INSERT INTO wm_book_flow (id, idx, batch_mark, record_operator, doc_detail_id, 
                    document_doc_type, config_doc_type, doc_type, doc_code, doc_date, book_date, bu_id, bu_name, 
                    org_id, org_code, org_name, account_id, warehouse_id, warehouse_name, warehouse_code, 
                    category_code, category_name, sku_id, product_id, product_code, product_name, spec, 
                    batch_no, batch_sn, valid_from, valid_to, document_order_right, config_order_right, 
                    order_right_code, order_right_name, document_stock_type, config_stock_type, stock_type_code, stock_type_name, 
                    qty_delete, type_code, type_name, create_time, update_time) 
                select 
                    md5(UUID_SHORT()) id, null idx, sys_interface_log.id batch_mark, 
                    'insert' record_operator, so_order_return_detail.id doc_detail_id, '普通销售出库' document_doc_type,'退货单' config_doc_type, 
                    '普通销售出库'doc_type, so_order_return.code doc_code, so_order_return.doc_date, CURRENT_DATE book_date, 
                    so_order_return_detail.bu_id, so_order_return_detail.bu_name, so_order_return.org_id org_id, null org_code, 
                    so_order_return.customer_name org_name, so_order_return.customer_id account_id, so_order_return.warehouse_id, 
                    so_order_return.warehouse_name, so_order_return.warehouse_code, null category_code, null category_name, 
                    so_order_return_detail.sku_id,  so_order_return_detail.product_id, so_order_return_detail.product_code, 
                    so_order_return_detail.product_name, so_order_return_detail.spec, 
                    if(@{IfEmpty}(so_order_return_detail.batch_no,'')= '', if(@{IfEmpty}(so_order_return_detail.batch_sn,'')= '', '--', so_order_return_detail.batch_sn), so_order_return_detail.batch_no) batch_no, 
                    if(@{IfEmpty}(so_order_return_detail.batch_sn,'')= '', '--', so_order_return_detail.batch_sn)  batch_sn, so_order_return_detail.valid_from, so_order_return_detail.valid_to, 'normal'document_order_right, 
                    'Normal' config_order_right, 'normal'order_right_code, '标准库存'order_right_name, 'Standard'document_stock_type, 'Standard' config_stock_type, 
                    'Standard'stock_type_code, '标准库存' stock_type_name, so_order_return_detail.qty qty_delete, 'Master'type_code, '主仓'type_name, 
                    now() create_time, now() update_time 
                from so_order_return_detail 
                left join so_order_return on so_order_return.id = so_order_return_detail.parent_id
                left join (select id from sys_interface_log 
                    where sys_interface_log.interface_id = 'gsp-2-03-v_jxcxsdh' 
                    order by create_time desc limit 1) sys_interface_log on 1=1
                where so_order_return_detail.is_delete = '0' and so_order_return.customer_id is not null and so_order_return.state_name = '已出库'
            ]]>
        </sql>
		
        <sql name="interfaceOrderReturnWriteFlowOrg">
            <![CDATA[
                update wm_book_flow 
                SET org_code = (select code from md_org where md_org.id = wm_book_flow.org_id )
                WHERE batch_mark = (select id from sys_interface_log 
                    where sys_interface_log.interface_id = 'gsp-2-03-v_jxcxsdh' order by create_time desc limit 1)
            ]]>
        </sql>
		
        <sql name="interfaceOrderReturnStockOperator">
            <![CDATA[
                update wm_book_flow
                inner join wm_book_detail on wm_book_detail.account_id = wm_book_flow.account_id 
                    and wm_book_detail.warehouse_id = wm_book_flow.warehouse_id 
                    and wm_book_detail.batch_no = wm_book_flow.batch_no 
                    and wm_book_detail.batch_sn = wm_book_flow.batch_sn 
                    and wm_book_detail.sku_id = wm_book_flow.sku_id
                SET book_detail_id = wm_book_detail.id, record_operator = 'update'
                WHERE wm_book_flow.batch_mark = (select id from sys_interface_log 
                    where sys_interface_log.interface_id = 'gsp-2-03-v_jxcxsdh' order by create_time desc limit 1)
            ]]>
        </sql>
		
        <sql name="interfaceOrderReturnAddStock">
            <![CDATA[
                INSERT INTO wm_book_detail (id, idx, batch_mark, 
                    org_id, org_code, org_name, account_id, warehouse_id, warehouse_code, warehouse_name, 
                    bu_id, bu_name, sku_id, product_id, product_code, product_name, spec,  batch_no, batch_sn, 
                    valid_from, valid_to, order_right_code, order_right_name, stock_type_code, stock_type_name, 
                    qty_total, qty_frozen, qty_available, type_code, type_name, create_time, update_time) 
                select MD5(UUID_SHORT()) id, null idx, batch_mark, 
                    org_id, org_code, org_name, account_id, warehouse_id, warehouse_code, warehouse_name, 
                    bu_id, bu_name, sku_id, product_id, product_code, product_name, spec,  batch_no, batch_sn, 
                    valid_from, valid_to, order_right_code, order_right_name, stock_type_code, stock_type_name,
                    -sum(qty_delete) qty_total, 0 qty_frozen, -sum(qty_delete)qty_available, type_code, type_name, create_time, update_time 
                from wm_book_flow 
                where batch_mark = (select id from sys_interface_log 
                    where sys_interface_log.interface_id = 'gsp-2-03-v_jxcxsdh' order by create_time desc limit 1) 
                    and record_operator = 'insert'
                group by warehouse_id, account_id, bu_id, sku_id, batch_no, batch_sn,`order_right_code`, `stock_type_code`
            ]]>
        </sql>
		
        <sql name="interfaceOrderReturnUpdateStock">
            <![CDATA[
                update wm_book_detail
                inner join (select book_detail_id id , sum(qty_delete) qty_change from wm_book_flow 
                where batch_mark = (select id from sys_interface_log 
                    where sys_interface_log.interface_id = 'gsp-2-03-v_jxcxsdh' order by create_time desc limit 1) 
                    and record_operator = 'update'
                    group by book_detail_id ) v_book_detail on v_book_detail.id = wm_book_detail.id
                set qty_total = qty_total - qty_change, qty_available = qty_available - qty_change
            ]]>
        </sql>
		
        <sql name="interfaceClearEmptyStock">
            <![CDATA[
                delete from wm_book_detail
                where qty_total = 0 and qty_available = 0
            ]]>
        </sql>
		 
        <sql name="interfaceUpdateOrderReturnPositionEmployeeName">
            <![CDATA[
                update temp_so_order_return
                inner join md_position_gsp on temp_so_order_return.position_employee_external_id = md_position_gsp.gsp_employee_code  
                inner join md_position on md_position.id = md_position_gsp.position_id
                inner join md_position_employee on md_position_employee.position_id = md_position.id
                SET temp_so_order_return.position_employee_name = md_position_employee.remark,
                    temp_so_order_return.position_id = md_position.id ,
                    temp_so_order_return.position_name = md_position.name,
                    temp_so_order_return.position_region = md_position.region,
                    temp_so_order_return.position_employee_id = md_position_employee.employee_id
                where temp_so_order_return.position_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateOrderReturnPositionHierarchy">
            <![CDATA[
                UPDATE temp_so_order_return
                INNER JOIN md_position_hierarchy ON md_position_hierarchy.position_id = temp_so_order_return.position_id 
                SET temp_so_order_return.position_cn_id = md_position_hierarchy.level1,
                temp_so_order_return.position_region_id = md_position_hierarchy.level2,
                temp_so_order_return.position_area_id = md_position_hierarchy.level3
                where temp_so_order_return.position_cn_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceSetOrderReturnOpenState">
            <![CDATA[
                update so_order_return 
                set state_code = 'Open', state_name = '已完成'
                where state_name = '已出库'  and so_order_return.customer_id is not null 
                    and exists (select 1 from so_order_return_detail where so_order_return_detail.parent_id = so_order_return.id)
            ]]>
        </sql>
		
    </dataSpace>
	
    <dataSpace name="interface-io-delivery">	
        <sql name="interfaceUpdateDeliverySupplier">
            <![CDATA[
                update temp_so_delivery
                set supplier_name = (select name from md_org where md_org.gsp_id = temp_so_delivery.supplier_id) 
                where supplier_name is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateDeliveryState">
            <![CDATA[
                update temp_so_delivery set state_name = '已出库'  
                where state_code = '2' and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateDeliveryDetailParentId">
            <![CDATA[
                update temp_so_delivery_detail
                SET temp_so_delivery_detail.parent_id = (select temp_so_delivery.id from temp_so_delivery
                    where temp_so_delivery.external_id = temp_so_delivery_detail.parent_external_id and temp_so_delivery_detail.io_batch_id = '@{ioBatchId}')
                where temp_so_delivery_detail.parent_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateDeliveryDetailOrderId">
            <![CDATA[
                update temp_so_delivery_detail  
                set temp_so_delivery_detail.order_id = (
                    select so_order.id from so_order 
                    where so_order.external_id = temp_so_delivery_detail.order_external_id )
                where order_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateDeliveryPositionEmployeeName">
            <![CDATA[
                update temp_so_delivery 
                inner join md_employee_gsp on md_employee_gsp.code = temp_so_delivery.position_employee_name
                SET position_employee_name = md_employee_gsp.name 
                where io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateDeliveryDetailOrderDetailId">
            <![CDATA[
                update temp_so_delivery_detail  
                set temp_so_delivery_detail.order_detail_id = (
                    select id from so_order_detail 
                    where  so_order_detail.external_id = temp_so_delivery_detail.order_detail_external_id )
                where order_detail_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateDeliveryDetailSku">
            <![CDATA[
                update temp_so_delivery_detail 
                inner join md_prod_sku on md_prod_sku.gsp_id = temp_so_delivery_detail.sku_external_id 
                SET temp_so_delivery_detail.sku_id = md_prod_sku.id , 
                    temp_so_delivery_detail.product_id = md_prod_sku.product_id, 
                    temp_so_delivery_detail.product_code = md_prod_sku.product_code, 
                    temp_so_delivery_detail.product_name = md_prod_sku.product_name, 
                    temp_so_delivery_detail.spec = md_prod_sku.spec, 
                    temp_so_delivery_detail.unit = md_prod_sku.unit ,
                    temp_so_delivery_detail.batch_sn = case when md_prod_sku.is_active_sn = 'T' then batch_sn else '--' end
                where sku_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateDeliveryPeriod">
            <![CDATA[
                update temp_so_delivery
                inner join md_peroid on md_peroid.date_from <= temp_so_delivery.doc_date and md_peroid.date_to >= temp_so_delivery.doc_date
                set temp_so_delivery.year = md_peroid.year, temp_so_delivery.quarter = md_peroid.quarter, temp_so_delivery.month = md_peroid.month 
                where temp_so_delivery.year is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateDeliveryDetailState">
            <![CDATA[
                update temp_so_delivery_detail set state_name = '已出库'
                where state_code = '2' and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateDeliveryDetailParentIdFromFormal">
            <![CDATA[
                update so_delivery_detail
                SET so_delivery_detail.parent_id = (select so_delivery.id from so_delivery
                    where so_delivery.external_id = so_delivery_detail.parent_external_id)
                where so_delivery_detail.parent_id is null
            ]]>
        </sql>
    </dataSpace>
	
    <dataSpace name="interface-io-salesReturn">	
        <sql name="interfaceUpdateSalesReturnSupplier">
            <![CDATA[
                update temp_so_sales_return
                set supplier_name = (select name from md_org where md_org.gsp_id = temp_so_sales_return.supplier_id) ,
                    supplier_address = (select business_address from md_org where md_org.gsp_id = temp_so_sales_return.supplier_id) 
                where supplier_name is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateSalesReturnState">
            <![CDATA[
                update temp_so_sales_return set state_name = '已出库' 
                where state_code = '2' and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateSalesReturnDetailParentId">
            <![CDATA[
                update temp_so_sales_return_detail
                SET temp_so_sales_return_detail.parent_id = (select temp_so_sales_return.id from temp_so_sales_return
                    where temp_so_sales_return.external_id = temp_so_sales_return_detail.parent_external_id and temp_so_sales_return.io_batch_id = '@{ioBatchId}')
                where temp_so_sales_return_detail.parent_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateSalesReturnDetailSku">
            <![CDATA[
                update temp_so_sales_return_detail 
                inner join md_prod_sku on md_prod_sku.gsp_id = temp_so_sales_return_detail.sku_external_id 
                SET temp_so_sales_return_detail.sku_id = md_prod_sku.id , 
                    temp_so_sales_return_detail.product_id = md_prod_sku.product_id, 
                    temp_so_sales_return_detail.product_code = md_prod_sku.product_code, 
                    temp_so_sales_return_detail.product_name = md_prod_sku.product_name, 
                    temp_so_sales_return_detail.spec = md_prod_sku.spec, 
                    temp_so_sales_return_detail.unit = md_prod_sku.unit ,
                    temp_so_sales_return_detail.batch_sn = case when md_prod_sku.is_active_sn = 'T' then batch_sn else '--' end
                where sku_id is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateSalesReturnPeriod">
            <![CDATA[
                update temp_so_sales_return
                inner join md_peroid on md_peroid.date_from <= temp_so_sales_return.doc_date and md_peroid.date_to >= temp_so_sales_return.doc_date
                set temp_so_sales_return.year = md_peroid.year, temp_so_sales_return.quarter = md_peroid.quarter, temp_so_sales_return.month = md_peroid.month 
                where temp_so_sales_return.year is null and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateSalesReturnDetailState">
            <![CDATA[
                update temp_so_sales_return_detail set state_name = '已出库' 
                where state_code = '2' and io_batch_id = '@{ioBatchId}'
            ]]>
        </sql>
		
        <sql name="interfaceUpdateSalesReturnDetailParentIdFromFormal">
            <![CDATA[
                update so_sales_return_detail
                SET so_sales_return_detail.parent_id = (select so_sales_return.id from so_sales_return
                    where so_sales_return.external_id = so_sales_return_detail.parent_external_id )
                where so_sales_return_detail.parent_id is null
            ]]>
        </sql>
		
        <sql name="interfaceComplementSalesReturnParentId">
            <![CDATA[
                update so_sales_return_detail
                inner join so_sales_return on so_sales_return.external_id = so_sales_return_detail.parent_external_id 
                set so_sales_return_detail.parent_id = so_sales_return.id
                where so_sales_return_detail.parent_id is null 
            ]]>
        </sql>
        </sql> 
    </dataSpace>
</sqls>

 ai/src/ai/AIResult.java

New file
@@ -0,0 +1,18 @@
package ai;

import foundation.json.IJSONProvider;
import foundation.json.IJSONWriter;

public class AIResult implements IJSONProvider {
    private String content;

    public AIResult(String jsonContent) {
        this.content = jsonContent;
    }

    @Override
    public void writeJSON(IJSONWriter writer) {
        writer.writeJSON(content);
    }

}

 ai/src/ai/AiHandler.java

@@ -1,20 +1,19 @@
package ai;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.nio.charset.StandardCharsets;

import org.apache.pdfbox.multipdf.Splitter;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.hc.client5.http.classic.methods.HttpGet;
import org.apache.hc.client5.http.impl.classic.CloseableHttpClient;
import org.apache.hc.client5.http.impl.classic.CloseableHttpResponse;
import org.apache.hc.client5.http.impl.classic.HttpClients;
import org.apache.hc.core5.http.io.entity.EntityUtils;

import com.aliyun.ocr20191230.Client;
import com.aliyun.ocr20191230.models.RecognizePdfResponseBody;
import com.aliyun.tea.TeaException;

import foundation.dao.DataPackage;
import foundation.data.entity.Entity;
import foundation.icall.ICall;
import foundation.icall.ICallBucket;
import foundation.icall.ICallCenter;
import foundation.util.Util;
import foundation.workflow.ActionProvider;

public class AiHandler extends ActionProvider {
@@ -22,110 +21,74 @@
    @Override
    protected void publishMethod() {
        addMethod("pdf");
		
        addMethod("writerResult");
    }
	
    
    public void pdf() throws Exception {
        Client client = createClient();
        String filePath = dataReader.getString("filePath");
        File file = new File(filePath);
        PDDocument document = PDDocument.load(file);
        int numberOfPages = document.getNumberOfPages();


        List<File> fileList = new ArrayList<>();
        if (numberOfPages > 5) {
            fileList.addAll(splitFiles(document));
        } else {
            fileList.add(file);
        }
        document.close();

        for (int i = 0; i < fileList.size(); i++) {
            File oneSubFile = fileList.get(i);
            logger.info("总共：{} 开始读取第{}个 文件名：{} ", fileList.size(), i+1, oneSubFile.getName());
            getPDFText(client, oneSubFile);
        }

         AliyunOcrApiDirect ocr = new AliyunOcrApiDirect();
		 
         DataPackage dataPackage = dataReader.getDataPackage();
         dataPackage.loadOneDataFromDB();
         Entity master = dataPackage.getMasterEntity();
         String fileUrl = master.getString("file_url");
         // PDF识别
         String result = ocr.recognizePdf(fileUrl, 1);
         System.out.println("PDF识别结果: " + result);
    }

    private List<File> splitFiles(PDDocument document) {
        List<File> fileList = new ArrayList<>();
        //1 创建拆分器并设置每5页拆分一次
        Splitter splitter = new Splitter();
        splitter.setSplitAtPage(5); // 关键参数设置
        // 3. 执行拆分操作
        try {
            List<PDDocument> splitDocuments = splitter.split(document);

            // 4. 保存拆分后的文件
            String outputDir = "output/"; // 输出目录
            new File(outputDir).mkdirs(); // 创建目录

            for (int i = 0; i < splitDocuments.size(); i++) {
                String outputPath = outputDir + "split_" + (i + 1) + ".pdf";
                splitDocuments.get(i).save(outputPath);
                splitDocuments.get(i).close();
                File file = new File(outputPath);
                fileList.add(file);
                System.out.println("生成文件: " + outputPath);
	 
    public void writerResult() throws Exception {
        DataPackage dataPackage = dataReader.getDataPackage();
        dataPackage.loadOneDataFromDB();
        Entity master = dataPackage.getMasterEntity();
		
        int index = 0;
		
        String baiduFileUrl = master.getString("baidu_file_url");
        ICallCenter icallCenter = ICallCenter.getInstance();
        ICallBucket callBucket = ICallBucket.getInstance();
        ICall iCall = callBucket.getOne("document-parser-quary");
		
        while (Util.isEmpty(baiduFileUrl) && index < 3) {
            step.setDataPackage(dataPackage);
            icallCenter.callRemote(step, iCall);
			
            dataPackage.loadOneDataFromDB(true);
            master = dataPackage.getMasterEntity();
            index ++;
			
            Thread.sleep(2000);
            baiduFileUrl = master.getString("baidu_file_url");
        }
		
        String jsonContent = fetchJsonWithHttpClient(baiduFileUrl);
        AIResult result = new AIResult(jsonContent);
        dataWriter.addValue("content", result);
        dataWriter.addValue("data", master);
    }
	
    public static String fetchJsonWithHttpClient(String url) {
        try (CloseableHttpClient httpClient = HttpClients.createDefault()) {
            HttpGet request = new HttpGet(url);
            
            // 设置请求头
            request.setHeader("Accept", "application/json; charset=UTF-8");
            request.setHeader("Accept-Charset", "UTF-8");
            request.setHeader("User-Agent", "Mozilla/5.0");
            
            try (CloseableHttpResponse response = httpClient.execute(request)) {
                int statusCode = response.getCode();
                
                if (statusCode == 200) {
                    return EntityUtils.toString(response.getEntity(), StandardCharsets.UTF_8);
                } else {
                    System.err.println("请求失败，状态码: " + statusCode);
                    return null;
                }
            }

            System.out.println("拆分完成，共生成" + splitDocuments.size() + "个文件");

        } catch (IOException e) {
        } catch (Exception e) {
            e.printStackTrace();
        }
        return fileList;
    }

    private void getPDFText(Client client, File oneSubFile) throws FileNotFoundException {
        com.aliyun.ocr20191230.models.RecognizePdfAdvanceRequest recognizePdfRequest = new com.aliyun.ocr20191230.models.RecognizePdfAdvanceRequest();
        InputStream inputStream = new FileInputStream(oneSubFile);
        recognizePdfRequest.setFileURLObject(inputStream);
        com.aliyun.teautil.models.RuntimeOptions runtime = new com.aliyun.teautil.models.RuntimeOptions();
        try {
            com.aliyun.ocr20191230.models.RecognizePdfResponse resp = client.recognizePdfAdvance(recognizePdfRequest, runtime);
            RecognizePdfResponseBody body = resp.getBody();
            RecognizePdfResponseBody.RecognizePdfResponseBodyData data = body.getData();
            List<RecognizePdfResponseBody.RecognizePdfResponseBodyDataWordsInfo> wordsInfo = data.getWordsInfo();
            for (RecognizePdfResponseBody.RecognizePdfResponseBodyDataWordsInfo recognizePdfResponseBodyDataWordsInfo : wordsInfo) {
                String word = recognizePdfResponseBodyDataWordsInfo.word;
                logger.info("文字：{}", word);
            }


            com.aliyun.teaconsole.Client.log(com.aliyun.teautil.Common.toJSONString(resp));
        } catch (TeaException error) {
            error.printStackTrace();
            logger.info(error.getMessage());
            // 此处仅做打印展示，请谨慎对待异常处理，在工程项目中切勿直接忽略异常。
            // 错误 message
            System.out.println(error.getMessage());
            // 诊断地址
            System.out.println(error.getData().get("Recommend"));
            com.aliyun.teautil.Common.assertAsString(error.message);
        } catch (Exception _error) {
            TeaException error = new TeaException(_error.getMessage(), _error);
            // 此处仅做打印展示，请谨慎对待异常处理，在工程项目中切勿直接忽略异常。
            // 错误 message
            System.out.println(error.getMessage());
            // 诊断地址
            System.out.println(error.getData().get("Recommend"));
            com.aliyun.teautil.Common.assertAsString(error.message);
            return null;
        }
    }
	
	
	
      public static com.aliyun.ocr20191230.Client createClient() throws Exception {
            // 工程代码建议使用更安全的无AK方式，凭据配置方式请参见：https://help.aliyun.com/document_detail/378657.html。
            com.aliyun.teaopenapi.models.Config config = new com.aliyun.teaopenapi.models.Config()
                    .setAccessKeyId("LTAI5tCSkZYYhkUCsk4v4CCu")
                    .setAccessKeySecret("vhJBGvKQKmKFIpUq6WQndYYMwwRaP7");
            // Endpoint 请参考 https://api.aliyun.com/product/ocr
            config.endpoint = "ocr.cn-shanghai.aliyuncs.com";
            return new com.aliyun.ocr20191230.Client(config);
        }
	
}

 ai/src/ai/AliyunOcrApiDirect.java

New file
@@ -0,0 +1,162 @@
package ai;

import java.io.BufferedReader;
import java.io.File;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.util.Base64;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

public class AliyunOcrApiDirect {
    
    private final String accessKeyId;
    private final String accessKeySecret;
    private final String baseUrl;
    
    public AliyunOcrApiDirect() {
        this.accessKeyId = "LTAI5tCSkZYYhkUCsk4v4CCu";
        this.accessKeySecret = "vhJBGvKQKmKFIpUq6WQndYYMwwRaP7";
        this.baseUrl = "https://ocr.cn-shanghai.aliyuncs.com?";
    }
    
    /**
     * PDF识别
     */
    public String recognizePdf(String fileUrl, int pageNumber) throws Exception {
        Map<String, Object> params = new HashMap<>();
        params.put("PdfOcr", true);
        params.put("PdfPageNumber", pageNumber);
        return callOcrApi("RecognizeAdvanced", fileUrl, params);
    }
    
    private String callOcrApi(String action, String fileUrl, Map<String, Object> extraParams) throws Exception {
        // 1. 准备基本参数
        String timestamp = getTimestamp();
        String signatureNonce = UUID.randomUUID().toString();
        
        // 2. 构建查询参数
        StringBuilder queryParams = new StringBuilder();
        queryParams.append("Action=").append(action);
        queryParams.append("&Format=JSON");
        queryParams.append("&Version=2021-07-07");
        queryParams.append("&AccessKeyId=").append(accessKeyId);
        queryParams.append("&SignatureMethod=HMAC-SHA1");
        queryParams.append("&Timestamp=").append(encode(timestamp));
        queryParams.append("&SignatureVersion=1.0");
        queryParams.append("&SignatureNonce=").append(signatureNonce);
        queryParams.append("&RegionId=cn-shanghai");
        
        // 3. 生成签名
        String signature = generateSignature("POST", queryParams.toString());
        queryParams.append("&Signature=").append(encode(signature));
        
        // 4. 构建完整URL
        String url = baseUrl + queryParams;
        
        // 5. 构建请求体
        String requestBody = buildRequestBody(fileUrl, extraParams);
        
        // 6. 发送请求
        return sendHttpRequest(url, requestBody);
    }
    
    private String generateSignature(String method, String queryParams) throws Exception {
        String stringToSign = method + "&%2F&" + encode(queryParams);
        
        javax.crypto.Mac mac = javax.crypto.Mac.getInstance("HmacSHA1");
        mac.init(new javax.crypto.spec.SecretKeySpec(
                (accessKeySecret + "&").getBytes(StandardCharsets.UTF_8), "HmacSHA1"));
        byte[] signData = mac.doFinal(stringToSign.getBytes(StandardCharsets.UTF_8));
        
        return Base64.getEncoder().encodeToString(signData);
    }
    
    private String buildRequestBody(File file, Map<String, Object> extraParams) throws Exception {
        byte[] fileBytes = Files.readAllBytes(file.toPath());
        String base64Data = Base64.getEncoder().encodeToString(fileBytes);
        
        StringBuilder json = new StringBuilder();
        json.append("{\"ImageData\":\"").append(base64Data).append("\"");
        
        for (Map.Entry<String, Object> entry : extraParams.entrySet()) {
            json.append(",\"").append(entry.getKey()).append("\":");
            if (entry.getValue() instanceof Boolean) {
                json.append(entry.getValue());
            } else if (entry.getValue() instanceof Number) {
                json.append(entry.getValue());
            } else {
                json.append("\"").append(entry.getValue()).append("\"");
            }
        }
        json.append("}");
        
        return json.toString();
    }
    
    private String buildRequestBody(String fileUrl, Map<String, Object> extraParams) throws Exception {
        StringBuilder json = new StringBuilder();
        json.append("{\"url\":\"").append(fileUrl).append("\"");
        
        for (Map.Entry<String, Object> entry : extraParams.entrySet()) {
            json.append(",\"").append(entry.getKey()).append("\":");
            if (entry.getValue() instanceof Boolean) {
                json.append(entry.getValue());
            } else if (entry.getValue() instanceof Number) {
                json.append(entry.getValue());
            } else {
                json.append("\"").append(entry.getValue()).append("\"");
            }
        }
        json.append("}");
        
        return json.toString();
    }
    
    private String sendHttpRequest(String urlStr, String requestBody) throws Exception {
        URL url = new URL(urlStr);
        HttpURLConnection connection = (HttpURLConnection) url.openConnection();
        
        connection.setRequestMethod("POST");
        connection.setDoOutput(true);
        connection.setRequestProperty("Content-Type", "application/json");
        connection.setRequestProperty("Accept", "application/json");
        
        try (OutputStream os = connection.getOutputStream()) {
            os.write(requestBody.getBytes(StandardCharsets.UTF_8));
        }
        
        int responseCode = connection.getResponseCode();
        if (responseCode == 200) {
            try (BufferedReader br = new BufferedReader(
                    new InputStreamReader(connection.getInputStream(), StandardCharsets.UTF_8))) {
                StringBuilder response = new StringBuilder();
                String line;
                while ((line = br.readLine()) != null) {
                    response.append(line);
                }
                return response.toString();
            }
        } else {
            throw new RuntimeException("API请求失败，状态码: " + responseCode);
        }
    }
    
    private String getTimestamp() {
        return java.time.Instant.now().atZone(java.time.ZoneOffset.UTC)
                .format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss'Z'"));
    }
    
    private String encode(String value) {
        return URLEncoder.encode(value)
                .replace("+", "%20")
                .replace("*", "%2A")
                .replace("%7E", "~");
    }
}

 foundation.biz/src/biz/clean/CleanBucket.java

@@ -38,4 +38,14 @@
    public static CleanTarget getTarget(String id) {
        return targets.get(id);
    }
	
    public CleanEngine getOne(String code) throws Exception {
        CleanEngine engine = get(code);
		
        if (engine != null) {
            engine.checkUpdate();
        }
		
        return engine;
    }
}

 foundation.biz/src/biz/clean/CleanEngine.java

@@ -1,29 +1,46 @@
package biz.clean;

import java.util.Date;

import foundation.data.entity.Entity;
import foundation.data.entity.EntitySet;
import foundation.data.entity.Filter;
import foundation.data.object.DataObject;
import foundation.json.JSONReader;
import foundation.preload.Bucket;

public class CleanEngine {
    private String id;
    private String field;
    private String name;
    private Bucket<Rule> ruleBucket;
    private String remark;
    private Date lastUpdateTime;

    public void load(Entity entity) throws Exception {
        id = entity.getString("id");
        field = entity.getString("field_name");
        name = entity.getString("name");
        remark = entity.getString("remark");
		
        lastUpdateTime = new Date();
    }
    
    public void loadOneRule(Entity entity) throws Exception {
        Rule oneRule = new Rule();
        oneRule.load(entity);
		
        if (ruleBucket == null) {
            ruleBucket = new Bucket<Rule>();
        }
        ruleBucket.loadOne(entity.getId(), oneRule);
		
        String ruleId = entity.getId();
        boolean active = entity.getBoolean("is_active", true);
        if (!active) {
            ruleBucket.remove(ruleId);
            return ;
        }
		
        Rule oneRule = new Rule();
        oneRule.load(entity);
        ruleBucket.loadOne(ruleId, oneRule);
    }

    public String modify(String value) throws Exception {
@@ -36,6 +53,22 @@
            }
        }
        return null;
    }

    public void checkUpdate() throws Exception {
        DataObject dataObject = DataObject.getInstance("sys_clean_detail");
        Filter filter = new Filter("last_update_time", ">=" , lastUpdateTime);
        EntitySet detailSet = dataObject.getTableEntitySet(filter);
		
        for (Entity detail : detailSet) {
            String parentId = detail.getParentId();
			
            if (id.equalsIgnoreCase(parentId)) {
                loadOneRule(detail);
            }
        }
		
        lastUpdateTime = new Date();
    }

    public JSONReader modify(JSONReader data, String field) throws Exception {
@@ -71,6 +104,24 @@
        return null;
    }

    public Entity modify(Entity entity) throws Exception {
        Entity data = entity;
		
        String value = data.getString(field);
		
        for (Rule rule: ruleBucket) {
            String result = rule.match(value);
			
            if (result != null) {
                // return entity value if match anyone rule
                data.set(field, result);
                return data;
            }
        }
		
        return null;
    }

    public String getId() {
        return id;
    }
@@ -86,5 +137,4 @@
    public String getRemark() {
        return remark;
    }
	
}

 foundation.biz/src/biz/clean/CleanLoader.java

@@ -13,7 +13,7 @@
    public void startUp() throws Exception {
        loadCleanEngine();
        
        loadCleanTarget();
//        loadCleanTarget();
    }
    
    public void loadCleanEngine() throws Exception {

 foundation.biz/src/biz/clean/Rule.java

@@ -5,6 +5,7 @@
import java.util.regex.Pattern;

import foundation.data.entity.Entity;
import foundation.util.Util;

public class Rule {
    private String id;
@@ -45,6 +46,10 @@
    }

    public String match(String value) throws Exception {
        if (Util.isEmpty(value)) {
            return null;
        }

        if (trimSpace) {
            value = value.replace(" ", "");
        }

 foundation.data/src/foundation/data/entity/Entity.java

@@ -17,6 +17,7 @@
import foundation.json.IJSONProvider;
import foundation.json.IJSONWriter;
import foundation.json.JSONReader;
import foundation.json.JType;
import foundation.server.config.DBaseType;
import foundation.util.ContentBuilder;
import foundation.util.IEntity;
@@ -81,13 +82,22 @@
        }
    }
     
    public void copyFrom(JSONReader from, List<FieldMapping> fieldMappings) {
        if (from == null) {
    public void copyFrom(JSONReader json, List<FieldMapping> fieldMappings) {
        if (json == null) {
            return;
        }
        
        JSONReader from = json;
		
        for (FieldMapping mapping : fieldMappings) {
            String fromName = mapping.getFromName(true);
            int pos = fromName.indexOf(".");
            if (pos >= 0) {
                String jsonName = fromName.substring(0, pos);
                fromName = fromName.substring(pos + 1);
						
                from = json.getReader(jsonName, JType.Object);
            }
            
            String toName = mapping.getToName();
            Integer idx = fieldsMeta.getIndex(toName);

 foundation.icall/src/foundation/icall/callout/JSONResponse.java

@@ -12,11 +12,17 @@
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import biz.clean.CleanBucket;
import biz.clean.CleanEngine;
import foundation.dao.DataPackage;
import foundation.dao.DataSource;
import foundation.dao.JSONPackage;
import foundation.dao.JSONPackageBucket;
import foundation.dao.PackageItem;
import foundation.dao.PackageItemType;
import foundation.dao.bizlogic.IJSONResponse;
import foundation.data.entity.Entity;
import foundation.data.entity.EntitySet;
import foundation.io.FileCenter;
import foundation.io.FileField;
import foundation.io.FileMeta;
@@ -202,6 +208,34 @@
        }
        this.dataPackage = dataPackage;
        this.dataPackage.loadDataFromJSON(stepOriginalId, reader, jsonPackage);
		
        // 待优化
        CleanBucket cleanBucket = CleanBucket.getInstance();
        CleanEngine cleanEngine = cleanBucket.getOne("product_name_clean");

        for (PackageItem packageItem: dataPackage.getSlaveItems()) {
            PackageItemType itemType = packageItem.getType();
			
            if (itemType == PackageItemType.Master) {
                continue;
            }
            else {
                EntitySet entitySet = packageItem.getEntitySet(DataSource.Result);
                EntitySet newEntitySet = new EntitySet(entitySet.getEntityMeta());
				
                if (entitySet != null) {
                    for (Entity entity : entitySet) {
                        entity = cleanEngine.modify(entity);
						
                        if(entity != null) {
                            newEntitySet.append(entity);
                        }
                    }
                }
				
                packageItem.setEntitySet(newEntitySet, DataSource.Result);
            }
        }
    }


@@ -269,7 +303,20 @@
        
        writer.endArray();
        
//        writer.write("content", content);
        if (dataPackage != null) {
            writer.beginObject("data");
            for (PackageItem item : dataPackage) {
                String itemName = item.getName();
				
                if (item.isEntity(DataSource.Result)) {
                    writer.write(itemName, item.getEntity(DataSource.Result));
                }
                else {
                    writer.write(itemName, item.getEntitySet(DataSource.Result));
                }
            }
            writer.endObject();
        }
        writer.write("resultCode", resultCode);
        writer.endObject();
    }

 foundation.icall/src/foundation/icall/callout/RemoteServerCallProvider.java

@@ -100,13 +100,11 @@
        DataWriter dataWriter = step.getDataWriter();
        
        if (dataWriter != null) {
            dataWriter.addValue("icall_result", response);
            dataWriter.addValue(iCall.getName(), response);
        }
    
        ICallSender sender = new ICallSender(iCall.getName(), String.valueOf(response.getResultCode()));
        outboundResult.setSender(sender);
		
        logger.debug("send {} result: {}", request.getURL(), responseValue);
    }

    private void moveLocalMirrorToTarget(IOSQLContext context) throws Exception {

 foundation.icall/src/foundation/icall/callout/ai/InvoiceVerificationDao.java

File was deleted

 foundation.icall/src/foundation/icall/callout/ai/InvoiceVerificationDetailDao.java

File was deleted

 foundation.icall/src/foundation/icall/callout/ai/OCRResult.java

File was deleted

 foundation.icall/src/foundation/icall/callout/ai/OCRResultKingDee.java

File was deleted

 foundation.icall/src/foundation/icall/callout/ai/VatInvoiceVerifyDao.java

File was deleted

 foundation.icall/src/foundation/icall/callout/ai/VatInvoiceVerifyLogic.java

File was deleted

 foundation.icall/src/foundation/icall/connector/BaiduAIConn.java

New file
@@ -0,0 +1,130 @@
package foundation.icall.connector;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.Objects;

import foundation.icall.ICall;
import foundation.icall.callout.ICallRequest;
import foundation.icall.callout.JSONResponse;
import foundation.util.Util;
import foundation.workflow.WorkStep;
import okhttp3.MediaType;
import okhttp3.OkHttpClient;
import okhttp3.Request;
import okhttp3.RequestBody;
import okhttp3.Response;
import sun.misc.BASE64Encoder;

public class BaiduAIConn extends HttpServerConn {
	
    private static BaiduAIConn instance;
    private static String monitorId = "BaiduAIConn";
    private static int TimeOutHour = 2;
    public Date lastTime;
    private String token;
	
    private BaiduAIConn() {
		
    }
	
    public static synchronized BaiduAIConn getInstance() {
        if (instance == null) {
            instance = new BaiduAIConn();
        }
		
        return instance;
    }

    @Override
    public void login(WorkStep step, ICall iCall) throws Exception {
//        if (!tokenExpired()) {
//            return;
//        }
		
        getToken();
    }

    @Override
    public void logout(WorkStep step, ICall iCall) throws Exception {
		
    }

    @Override
    public ICallRequest createRequest(String url) {
        String host = meta.getString("base_url");
        ICallRequest request = new ICallRequest(host + url + token);
        return request;
    }
	
    private boolean tokenExpired() {
        if (Util.isEmpty(token)) {
            return true;
        }
		
        Date now = new Date();
        boolean result = now.getTime() - lastTime.getTime() >= TimeOutHour * 60 * 60 * 1000;
		
        return result;
    }
	
    private void getToken() throws Exception {
        // 1. build request
        String clientId = meta.getString("client_id");
        String clientSecret = meta.getString("client_secret");
        String grantType = meta.getString("grant_type");
        String bodyStr =  "grant_type=" + grantType + "&client_id=" + clientId + "&client_secret=" + clientSecret;

        String tokenUrl = meta.getString("token_url");
        Request request = new Request.Builder()
                .url(tokenUrl)
                .addHeader("Content-Type", "application/x-www-form-urlencoded")
                .post(RequestBody.create(MediaType.get("application/x-www-form-urlencoded"), bodyStr))
                .build();
		
        // 2. send request
        OkHttpClient httpClient = new OkHttpClient();
        Response response = httpClient.newCall(request).execute();
		
        if (!response.isSuccessful()) {
            return ;
        }
		
        JSONResponse result = new JSONResponse(response);
        token = result.getString("access_token");
    }
	
    private static String imageToBase64(File file) {
        byte[] data = null;

        InputStream in = null;
        try {
            // 1. bytes
            in = new FileInputStream(file);
            data = new byte[in.available()];
            in.read(data);
            in.close();

            // 2. encode
            BASE64Encoder encoder = new BASE64Encoder();
            return encoder.encode(Objects.requireNonNull(data));
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    public String getName() {
        return meta.getName();
    }
}

 foundation.icall/src/foundation/icall/connector/HttpServerConn.java

@@ -38,7 +38,7 @@
    protected static Logger logger;
    public static int ConnectTimeout = 60;
    public static int WriteTimeout = 60;
    public static int ReadTimeout = 60;
    public static int ReadTimeout = 600;
    
    protected HttpServerMeta meta;
    private static OkHttpClient httpClient = null;
@@ -88,6 +88,7 @@

    public IRequest buildBody(IRequest request, WorkStep step, ICall iCall) throws Exception {
        DataPackage dataPackage = step.getDataPackage();
        dataPackage.loadOneDataFromDB(true);
        MapList<String, Template> templateList = iCall.getRequestTemplateList();
        Entity master = dataPackage.getMasterEntity(iCall.getRequestDataSources());
        
@@ -101,9 +102,7 @@
        for (Entry<String, Template> entry : templateList.getItemMap().entrySet()) {
            Template template = entry.getValue();
            String key = entry.getKey();
			
            String value = template.fillVariants(dataPackage);
            System.out.println("value:" + value);
            if ("jsonbody".equals(key)) {
                request.setJSONBody(value);
            }
@@ -113,6 +112,8 @@
            else {
                request.addFormData(entry.getKey(), value);
            }

            logger.info("key :{}, value:{}", key, value);
        }
        
        if (master != null) {

 foundation.icall/src/foundation/icall/connector/KingdeeAIConn.java

@@ -24,6 +24,7 @@
import foundation.io.template.Template;
import foundation.util.MD5Utils;
import foundation.util.MapList;
import foundation.util.Util;
import foundation.workflow.WorkStep;
import okhttp3.MediaType;
import okhttp3.OkHttpClient;
@@ -62,8 +63,8 @@

    @Override
    public ICallRequest createRequest(String url) {
        String host = meta.getString("host");
        ICallRequest request = new ICallRequest(host + url);
        String host = meta.getString("url");
        ICallRequest request = new ICallRequest(host + url + "?access_token=" + token);
        request.addHeader("access_token", token);
        
        return request;
@@ -71,14 +72,15 @@

    @Override
    public IRequest buildBody(IRequest request, WorkStep step, ICall iCall) throws Exception {
        DataReader dataReader = step.getDataReader();
		
        String jsonBody = null;
        ContentType contentType = iCall.getContentType();
		
        if (ContentType.TextPlain == contentType) {
            FileRecord fileRecord = FileCenter.uploadOneFile(dataReader);
            File file = fileRecord.getFile();
        DataPackage dataPackage = step.getDataPackage();
        dataPackage.loadOneDataFromDB();
        String fileId = dataPackage.getString("file_id", null);
			
        if (!Util.isEmpty(fileId) && ContentType.TextPlain == contentType) {
            FileRecord fileRecord = FileCenter.getFile(dataPackage.getName(), fileId);
            File file = fileRecord.getFile(); 
            
            request.setDocDescription(new DocDescription(fileRecord.getIndexId(), fileRecord.getFileName()));
            jsonBody = imageToBase64(file);
@@ -87,10 +89,8 @@
                logger.error("无法对图片{}进行Base64编码，无法进行AI识别", file);
                return null;
            }
			
        }
        else {
            DataPackage dataPackage = step.getDataPackage();
            dataPackage.loadDataFromRequest();
            Entity master = dataPackage.getMasterEntity(DataSource.Request);

@@ -104,20 +104,20 @@
            if (master != null) {
                request.setDocDescription(new DocDescription(master.getId(), master.getString("code")));
            }
			
        }
	
        request.setContentType(contentType.getCode());

        request.setJSONBody(jsonBody);
		
        request.setContentType(contentType.getCode());
        return request;
    }
    
    private void getToken() throws Exception {
        long currentTimeStamp = System.currentTimeMillis();
        String client_id = meta.getString("client_id");
        String client_secret = meta.getString("client_secret");
        String host = meta.getString("host");
        String token_path = meta.getString("token_path");
        String client_id = meta.getString("clientid");
        String client_secret = meta.getString("clientsecret");
        String host = meta.getString("url");
        String token_path = meta.getString("tokenpath");
        String sign = MD5Utils.MD5Encode(client_id + client_secret + currentTimeStamp, "UTF-8");

        // 1. build request

 foundation.icall/src/foundation/icall/connector/TencentAIConn.java

@@ -1,7 +1,5 @@
package foundation.icall.connector;

import java.util.List;

import com.tencentcloudapi.common.Credential;
import com.tencentcloudapi.common.profile.ClientProfile;
import com.tencentcloudapi.common.profile.HttpProfile;
@@ -10,20 +8,14 @@

import foundation.dao.DataPackage;
import foundation.dao.DataSource;
import foundation.dao.Settings;
import foundation.dao.bizlogic.DocDescription;
import foundation.dao.bizlogic.IRequest;
import foundation.data.entity.Entity;
import foundation.icall.ContentType;
import foundation.icall.ICall;
import foundation.icall.callout.ICallRequest;
import foundation.icall.callout.JSONResponse;
import foundation.icall.callout.ai.InvoiceVerificationDao;
import foundation.icall.callout.ai.OCRResult;
import foundation.icall.callout.ai.OCRResultKingDee;
import foundation.io.template.Template;
import foundation.util.MapList;
import foundation.util.Util;
import foundation.workflow.WorkStep;

public class TencentAIConn extends HttpServerConn {
@@ -91,38 +83,6 @@
        request.setJSONBody(jsonBody);
        
        return request;
    }
	
    public OCRResult format(WorkStep step, JSONResponse response) throws Exception {
        boolean isReal = false, isBill = false;
        OCRResultKingDee resultKingDee = new OCRResultKingDee(step.getInstanceId(), response);
        List<InvoiceVerificationDao> invoiceVerificationList = resultKingDee.getInvoiceVerificationList();
		
        // 1. 发票唯一性校验
        boolean checkDistinct = Settings.getBoolean("InvoiceDistinctCheck", false);
		
        if (checkDistinct) {	
            for (InvoiceVerificationDao invoice : invoiceVerificationList) {
                boolean distincCheck = invoice.distinctCheck();
                invoice.setDistinct(distincCheck);
            }
        }
		
        if (Util.isEmpty(invoiceVerificationList) || !resultKingDee.isSuccess()) {
            isReal = false;
            isBill = false;
        }
		
        String value = Util.ocrJson(response.toString());
        OCRResult result = new OCRResult(value);
        result.setErrcode(resultKingDee.getErrcode());
        result.setDescription(resultKingDee.getDescription());
        result.setTraceId(resultKingDee.getTraceId());
        result.setIs_real(isReal);
        result.setIs_bill(isBill);
        result.setInvoiceList(invoiceVerificationList);
		
        return result;
    }
    
    private void createClient() throws Exception {

 foundation.io/src/foundation/io/FileRepository.java

@@ -18,7 +18,7 @@

public class FileRepository {
    
    private static String Server_Name = "https://dms.hh-healthcare.cn:82/";
    private static String Server_Name = Configer.getParam("ServerUrl");
    protected static Logger logger;
    public static String Default_Template = "empty.xlsx";
    

 foundation.io/src/foundation/io/IOHandler.java

@@ -103,7 +103,7 @@
        addMethod("createOneImportTask");
        
        //15. 创建一个导出任务
        addMethod("createOneExportTask");	
        addMethod("createOneExportTask");
    }
    
    public void uploadOneFile() throws Exception {

 foundation.workflow/src/foundation/dao/DataPackage.java

@@ -190,7 +190,7 @@
        for (PackageItem packageItem: slaveItems) {
            mapping = mappings.get(packageItem.getName());
            jsonReader = mapping.getJSONReader(packageReader);
            packageItem.loadOneDataFromJSON(dataId, packageReader, mapping, DataSource.Result);
            packageItem.loadOneDataFromJSON(dataId, jsonReader, mapping, DataSource.Result);
        }
        
        //3. set state
@@ -451,8 +451,12 @@
    }
    
    public void loadOneDataFromDB() throws Exception {
        loadOneDataFromDB(false);
    }
	
    public void loadOneDataFromDB(boolean force) throws Exception {
        DBState dbState = state.getDbState();
        if (dbState.isOneDataLoaded() || master == null) {
        if (!force && (dbState.isOneDataLoaded() || master == null)) {
            return;
        }
        

 foundation.workflow/src/foundation/dao/JSONMapping.java

@@ -53,7 +53,7 @@
                continue;
            }
            
            String indexStr = objectName.substring(listStartPos, listEndPos);
            String indexStr = objectName.substring(listStartPos + 1, listEndPos);
            String listName = objectName.substring(0, listStartPos);
            //2. 当前层级为列表且不指定索引，则返回列表；如data[index]
            if (!Util.isInteger(indexStr)) {

 foundation.workflow/src/foundation/dao/PackageItem.java

@@ -81,7 +81,7 @@
    public void loadOneDataFromJSON(String dataId, JSONReader packageReader, JSONMapping mapping, DataSource dataSource) throws Exception {
        String name = getName();
        
        JType valueType = mapping.getType();
        JType valueType = packageReader.getType();
        List<FieldMapping> fieldMappings = mapping.getFieldMappings();
        
        //1. 如果JSON中是Object，加载Entity
@@ -91,6 +91,7 @@
            if (itemReader == null) {
                itemReader = (JObjectReader) packageReader;
            }
			
            loadEntityFromJSON(dataId, itemReader, fieldMappings, dataSource);
            return;
        }
@@ -712,6 +713,10 @@
    public void setResultData(DataSource dataSource) {
        dataCells.setResult(dataSource);
    }

    public void setEntitySet(EntitySet entitySet, DataSource dataSource) {
        dataCells.setData(entitySet, dataSource);
    }
    
    public Page getPage() {
        return null;
@@ -732,5 +737,4 @@
    public String toString() {
        return parent.getName() + "." + meta.getName();
    }

}