mingQuanJiTuan/src/com/kingdee/eas/custom/calcdailypay/task/SyncMesTpmInsRateFacadeControllerBean.java

package com.kingdee.eas.custom.calcdailypay.task;

import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.bos.dao.ormapping.ObjectUuidPK;
import com.kingdee.bos.rabbitmq.guava.Lists;
import com.kingdee.bos.util.BOSUuid;
import com.kingdee.eas.base.permission.UserInfo;
import com.kingdee.eas.basedata.org.AdminOrgUnitInfo;
import com.kingdee.eas.basedata.org.CtrlUnitInfo;
import com.kingdee.eas.basedata.org.PositionInfo;
import com.kingdee.eas.basedata.person.PersonInfo;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.custom.calcdailypay.IInspectionRate;
import com.kingdee.eas.custom.calcdailypay.InspectionRateFactory;
import com.kingdee.eas.custom.calcdailypay.InspectionRateInfo;
import com.kingdee.eas.framework.CoreBaseCollection;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.jdbc.rowset.impl.JdbcRowSet;
import com.kingdee.util.db.SQLUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import java.io.FileInputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;
import java.util.Properties;

public class SyncMesTpmInsRateFacadeControllerBean extends AbstractSyncMesTpmInsRateFacadeControllerBean {
    private static Logger logger =
            Logger.getLogger("com.kingdee.eas.custom.calcdailypay.task.SyncMesTpmInsRateFacadeControllerBean");
    private static Properties propt = new Properties();

    public static void main(String[] args) throws BOSException {
        System.setProperty("EAS_HOME","D:/project/kingdeeV90/Project_hty/mingQuanJiTuan");
        SyncMesTpmInsRateFacadeControllerBean mes = new SyncMesTpmInsRateFacadeControllerBean();
        String str = mes._syncMesToShr(new Context(),null,null,null);
        System.out.println(str);
    }

    public SyncMesTpmInsRateFacadeControllerBean() {
        try {
            propt.load(new FileInputStream(String.valueOf(System.getProperty("EAS_HOME")) + "/server/properties/mingquan/syncMes.properties"));
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e);
        }
    }

    /**
     * 开始日期、结束日期为空，则结束日期默认取当天，开始日期默认取当天减一天
     * @description:
     * @author: lhbj
     * @date: 2025/5/30 16:48
     * @param: null
     * @return:null
     **/
    @Override
    protected String _syncMesToShr(Context ctx, String startDdjdate, String endDdjDate, String personNum) throws BOSException{
        super._syncMesToShr(ctx,startDdjdate,endDdjDate,personNum);
        try {
            SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
            List<String> param = Lists.newArrayList();
            StringBuffer selSql = new StringBuffer();
            selSql.append(" select id,max(dcreatetime) dcreatetime,bsysdel,cgsmc, ");
            selSql.append("        cgw,isbs,iydj,iwdj,iwcl, ");
            selSql.append("        ddjdate,cusercode,cusername,cfzname,cgwlx, ");
            selSql.append("        cjndj,csfxc,cbzrz,cgzxl ");
            selSql.append(" from vi_tpm_dj_wcllist where isnull(cusercode,'')!='' ");
            selSql.append(" and ddjdate>=? and ddjdate<=? ");
            //开始日期、结束日期为空，则结束日期默认取当天，开始日期默认取当天减一天
            if (StringUtils.isNotBlank(startDdjdate) && StringUtils.isNotBlank(endDdjDate)) {
                param.add(startDdjdate);
                param.add(endDdjDate);
            } else {
                Calendar calendar = Calendar.getInstance();
                endDdjDate = sf.format(calendar.getTime());
                calendar.add(Calendar.DAY_OF_MONTH, -1);
                startDdjdate = sf.format(calendar.getTime());
                param.add(startDdjdate);
                param.add(endDdjDate);
            }
            if (StringUtils.isNotBlank(personNum)) {
                selSql.append(" and cusercode=? ");
                param.add(personNum);
            }
            selSql.append(" group by  id,bsysdel,cgsmc, ");
            selSql.append("        cgw,isbs,iydj,iwdj,iwcl, ");
            selSql.append("        ddjdate,cusercode,cusername,cfzname,cgwlx, ");
            selSql.append("        cjndj,csfxc,cbzrz,cgzxl ");
            selSql.append("  order by ddjdate ");
            IRowSet rs = this.executeQuery(selSql.toString(), param.toArray());
            IInspectionRate iir = InspectionRateFactory.getLocalInstance(ctx);
            CoreBaseCollection upddata = new CoreBaseCollection();
            CoreBaseCollection newdata = new CoreBaseCollection();
            while (rs.next()) {
                String id = rs.getString("id");
                String cusercode = rs.getString("cusercode");
                Date ddjdate = rs.getDate("ddjdate");
                String ddjdateStr = sf.format(ddjdate);
                String cleekKey = cusercode+"_"+ddjdateStr;
                StringBuilder selPerson = new StringBuilder();
                selPerson.append("  ");
                selPerson.append(" select p.fid pId,p.Fname_l2 pName,p.FNumber pNumber, ");
                selPerson.append(" company.fid companyId,dep.fid depId,pos.fid posId, ");
                selPerson.append(" ir.fid irId,ir.fsimpleName mesId,ir.CFBizDate irBizDate ");
                selPerson.append(" from t_bd_person p ");
                selPerson.append(" left join t_hr_Personposition pn on pn.fpersonid=p.fid ");
                selPerson.append(" left join t_org_admin dep on dep.fid=pn.FPersonDep ");
                selPerson.append(" left join t_org_admin company on company.fid=pn.fcompanyid ");
                selPerson.append(" left join t_org_position pos on pos.fid=pn.FPrimaryPositionID ");
                selPerson.append(" left join CT_CAL_InspectionRate ir on p.fid=ir.cfpersonid and ir.CFBizDate=? ");
                selPerson.append(" where p.fnumber=? ");
                IRowSet prs = DbUtil.executeQuery(ctx,selPerson.toString(), new String[]{ddjdateStr,cusercode});
                if (prs.next()){
                    String pId = prs.getString("pId");
                    String pName = prs.getString("pName");
                    String pNumber = prs.getString("pNumber");
                    String companyId = prs.getString("companyId");
                    String depId = prs.getString("depId");
                    String posId = prs.getString("posId");
                    String irId = prs.getString("irId");
                    String mesId = prs.getString("mesId");
                    String irBizDate = prs.getString("irBizDate");
                    InspectionRateInfo irc = null;
                    //当存在中间表数据，且MES系统id不一致时才更新
                    if(StringUtils.isNotBlank(irId)&&(!id.equals(mesId))){
                        irc = iir.getInspectionRateInfo(new ObjectUuidPK(irId));
                        //日期
                        irc.setBizDate(ddjdate);
                        //点检完成率
                        irc.setComrate(rs.getBigDecimal("iwcl"));
                        //技能等级
                        irc.setSkillLevel(rs.getString("cjndj"));
                        //岗位类型
                        irc.setPositiontype(rs.getString("cgwlx"));
                        //是否现场作业
                        irc.setOnSite(rs.getString("csfxc"));
                        //班组任职
                        irc.setTeamApp(rs.getString("cbzrz"));
                        //工资序列
                        irc.setSalarysEq(rs.getString("cgzxl"));
                        //MES 的id
                        irc.setSimpleName(id);
                        Timestamp createtime = new Timestamp(System.currentTimeMillis());
                        irc.setLastUpdateTime(createtime);
                        upddata.add(irc);
                    }else if(StringUtils.isBlank(irId)){
                        irc = new InspectionRateInfo();
                        //行政组织
                        //部门
                        AdminOrgUnitInfo adminOrgUnitInfo = new AdminOrgUnitInfo();
                        adminOrgUnitInfo.setId(BOSUuid.read(depId));
                        irc.setAdminOrgUnit(adminOrgUnitInfo);
                        irc.setDepart(adminOrgUnitInfo);
                        //职位
                        PositionInfo position = new PositionInfo();
                        position.setId(BOSUuid.read(posId));
                        irc.setPosition(position);
                        //公司
                        AdminOrgUnitInfo cmpany = new AdminOrgUnitInfo();
                        cmpany.setId(BOSUuid.read(companyId));
                        irc.setCompany(cmpany);
                        //员工编码
                        irc.setPersonNum(pNumber);
                        //员工
                        PersonInfo personInfo = new PersonInfo();
                        personInfo.setId(BOSUuid.read(pId));
                        irc.setPerson(personInfo);
                        //名称
                        irc.setName(pName);
                        //编码
                        irc.setNumber(id);
                        //日期
                        irc.setBizDate(ddjdate);
                        //点检完成率
                        irc.setComrate(rs.getBigDecimal("iwcl"));
                        //技能等级
                        irc.setSkillLevel(rs.getString("cjndj"));
                        //岗位类型
                        irc.setPositiontype(rs.getString("cgwlx"));
                        //是否现场作业
                        irc.setOnSite(rs.getString("csfxc"));
                        //班组任职
                        irc.setTeamApp(rs.getString("cbzrz"));
                        //工资序列
                        irc.setSalarysEq(rs.getString("cgzxl"));
                        //MES 的id
                        irc.setSimpleName(id);
                        UserInfo userInfo = new UserInfo();
                        userInfo.setId(BOSUuid.read("256c221a-0106-1000-e000-10d7c0a813f413B7DE7F"));
                        CtrlUnitInfo cu = new CtrlUnitInfo();
                        cu.setId(BOSUuid.read("00000000-0000-0000-0000-000000000000CCE7AED4"));
                        irc.setCU(cu);
                        irc.setCreator(userInfo);
                        Timestamp createtime = new Timestamp(System.currentTimeMillis());
                        irc.setCreateTime(createtime);
                        irc.setLastUpdateUser(userInfo);
                        irc.setLastUpdateTime(createtime);
                        newdata.add(irc);
                    }
                }
                if(upddata.size()>=100){
                    iir.updateBatchData(upddata);
                    upddata.clear();
                }
                if(newdata.size()>=100){
                    iir.addnewBatchData(newdata);
                    newdata.clear();
                }
            }
            if(!upddata.isEmpty()){
                iir.updateBatchData(upddata);
                upddata.clear();
            }
            if(!newdata.isEmpty()){
                iir.addnewBatchData(newdata);
                newdata.clear();
            }
        }catch (SQLException e){
            e.printStackTrace();
            logger.error(e);
            return "err";
        } catch (EASBizException e) {
            e.printStackTrace();
            logger.error(e);
            return "err";
        }
        return "ok";
    }



    /**
     * 建立Mes系统数据库连接
     * @description:
     * @author: lhbj
     * @date: 2025/5/30 15:55
     * @param: null
     * @return:null
     **/
    private Connection getConnection() {
        String MesUrl = propt.getProperty("MesUrl");
        String MesUserName = propt.getProperty("MesUserName");
        String MesPassWord = propt.getProperty("MesPassWord");
        Connection connection = null;
        try {
            // 加载驱动程序
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            // 建立连接
            connection = DriverManager.getConnection(MesUrl, MesUserName, MesPassWord);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            logger.error(e);
        } finally {
        }
        return connection;
    }

    /**
     * Mes数据库查询
     * @description:
     * @author: lhbj
     * @date: 2025/5/30 15:55
     * @param: null
     * @return:null
     **/
    public IRowSet executeQuery(String sql, Object[] params) throws BOSException {
        Connection conn = this.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        JdbcRowSet var18;
        try {
            ps = conn.prepareStatement(sql);

            for (int i = 0; i < params.length; ++i) {
                ps.setObject(i + 1, params[i]);
            }

            rs = ps.executeQuery();
            JdbcRowSet rowset = new JdbcRowSet();
            rowset.populate(rs);
            var18 = rowset;
        } catch (SQLException exc) {
            StringBuffer sb = new StringBuffer("");

            for (int i = 0; i < params.length; ++i) {
                sb.append("param ").append(i).append(" is:").append(params[i]);
            }

            logger.error("sql is:" + sql + " param is:" + sb.toString());
            logger.error("sql error!", exc);
            throw new BOSException("Sql execute exception : " + sql, exc);
        } finally {
            cleanup(rs, ps, conn);
        }
        return var18;
    }
    /**
     * Mes数据库查询
     * @description:
     * @author: lhbj
     * @date: 2025/5/30 15:55
     * @param: null
     * @return:null
     **/
    public IRowSet executeQuery(String sql) throws BOSException {
        Connection conn =  this.getConnection();
        Statement statement = null;
        ResultSet rs = null;

        JdbcRowSet var6;
        try {
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            JdbcRowSet rowset = new JdbcRowSet();
            rowset.populate(rs);
            var6 = rowset;
        } catch (SQLException exc) {
            logger.error("333 sql is:" + sql, exc);
            throw new BOSException("Sql3 execute exception : " + sql, exc);
        } finally {
            cleanup(rs, statement, conn);
        }

        return var6;
    }
    /**
     * Mes数据库执行
     * @description:
     * @author: lhbj
     * @date: 2025/5/30 15:55
     * @param: null
     * @return:null
     **/
    public void execute(String sql) throws BOSException {
        Connection conn = this.getConnection();
        Statement statement = null;

        try {
            statement = conn.createStatement();
            statement.execute(sql);
        } catch (SQLException exc) {
            logger.error("sql 1 sql is:" + sql, exc);
            throw new BOSException("Sql1 execute exception : " + sql, exc);
        } finally {
            cleanup(statement, conn);
        }

    }
    /**
     * Mes数据库执行
     * @description:
     * @author: lhbj
     * @date: 2025/5/30 15:55
     * @param: null
     * @return:null
     **/
    public void execute(String sql, Object[] params) throws BOSException {
        Connection conn =  this.getConnection();
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql);

            for(int i = 0; i < params.length; ++i) {
                ps.setObject(i + 1, params[i]);
            }

            ps.execute();
        } catch (SQLException exc) {
            StringBuffer sb = new StringBuffer("");

            for(int i = 0; i < params.length; ++i) {
                sb.append("param ").append(i).append(" is:").append(params[i]);
            }

            logger.error("222 sql is:" + sql + " param is:" + sb.toString(), exc);
            throw new BOSException("Sql222 execute exception : " + sql, exc);
        } finally {
            cleanup(ps, conn);
        }

    }
    public static final void cleanup(Connection cn) {
        cleanup((ResultSet) null, (Statement) null, cn);
    }

    public static final void cleanup(Statement stmt) {
        cleanup((ResultSet) null, stmt, (Connection) null);
    }

    public static final void cleanup(Statement stmt, Connection cn) {
        cleanup((ResultSet) null, stmt, cn);
    }

    public static final void cleanup(ResultSet rs) {
        cleanup(rs, (Statement) null, (Connection) null);
    }

    public static final void cleanup(ResultSet rs, Statement stmt) {
        cleanup(rs, stmt, (Connection) null);
    }

    public static final void cleanup(ResultSet rs, Statement stmt, Connection cn) {
        SQLUtils.cleanup(rs, stmt, cn);
    }
}