效率接口-组织人员同步接口

• 引言：此篇文章阐述如何效率编写组织人员同步接口，用于企业内部各个系统间的组织和人员的统一

• 说明：insertAllSql与executeList是组装sql和执行sql的方法，因为我们这边是用java的连接池就不在此处写出详细方法

• 需要注意的是插入的时候insert name value (‘a’),(‘b’),(‘c’)这种效率比较高，更新的时候使用insert into user (id,name) values (1,’2’),(2,’3’),…(x,’y’) on duplicate key update name=values(name);这种方法是通过主键来识别相同数据 然后更新相同数据能提升效率。
• 插入或者更新数据时候为了提升效率需要分隔数组每1000提交一次

• 最终测试结果1W数据不到0.5秒

• 思维导图

• 中间数据库
  部门表 z_group：

字段名称 字段说明 备注 id 主键 groupid 部门id 如为最顶层部门设为0 groupname 部门名称 pgroupid 父部门id remark 预留字段

用户表 z_user：

字段名称 字段说明 备注 id 主键 userid 登录名 groupid 部门id 如为最顶层部门设为0 remark 预留字段

---

• 坑人的组织机构表
  由于我们自身系统中含有部门路径，也就是说当前部门的信息在插入的时候需要把它自己的父部门路径都写出来，所以下面程序中用到了递归。如果不需要可以不用。
• 递归代码

/** - Created by PhpStorm. - User: rex - Date: 2017/11/8 0008 - Time: 上午 9:26 */namespace Interfaces\Model;use Unis\UnisModel;class TreeModel extends UnisModel{    /**     * 架构函数 读取数据库配置信息     * @access public     * @param array $config 数据库配置数组     */    public function __construct($name='db',$config=''){        parent::__construct($name,$config);    }    /**     * @access private     */    var $ret = '';    function tree($arr=array())    {        $this->arr = $arr;        $this->ret = '';        return is_array($arr);    }    /**     * 得到子部门数组     * @param string $myid 部门id     * @return array     */    function get_child($myid)    {        $a = $newarr = array();        if(is_array($this->arr))        {            foreach($this->arr as $id => $a)            {                if($a['groupid'] == ((string)$myid)) $newarr[$id] = $a;            }        }        return $newarr ? $newarr : false;    }    /**     * 处理所有部门信息     * @param string  $myid             部门id     * @param string  $pathname         部门id     * @param string  $pathid       部门id路径     * @param string  $topid        顶级部门id     * @param string  $topname      顶级部门名称     * @return array     */    function getArraya($myid=0, $pathname='',$pathid='',$topid='',$topname='',$level=1){        $number=1;        $child = $this->get_child($myid);        $spathname = $pathname;        $spathid = $pathid;        $slevel = $level;        if(is_array($child)) {            foreach($child as $id=>$a) {                if($myid==0){//顶级单位                    $topid=$a['id'];                    $topname=$a['groupname'];                }                if(is_array($this->get_child($a['id']))){//是否有子类                    $a['haschild'] = 1;                }else{                    $a['haschild'] = 0;                }                $a['pathname'] = $pathname.'/'.$a['groupname'];                $a['pathid'] = $pathid.'/'.$a['id'];                $a['topid'] = $topid;                $a['topname'] = $topname;                $a['level'] = $level;                $a['fatherid'] = $myid;                $pathname = $a['pathname'];                $pathid = $a['pathid'];                $this->ret[$a['id']] = $a;           //     @extract($a);                $level++;                $this->getArraya($a['id'], $pathname,$pathid,$topid,$topname,$level);                $pathname=$spathname;                $pathid = $spathid;                $level = $slevel;                $number++;            }        }        return $this->ret;    }}

• 主要方法

namespace Interfaces\Controller;use Unis\UnisSoap;use Interfaces\Model\RestModel;use Interfaces\Model\OrgMysqlModel;use Interfaces\Model\TreeModel;        /*         *  @name 组织机构同步通用接口：单全宗        *  @type 组织机构同步接口        *  @method 中间库        */class OrgMysqlController extends UnisSoap {     protected $OrgMysqlModel;     protected $restModel;     protected $deptData;     public function __construct() {        parent::__construct ();        $this->restModel = new RestModel();                $this->OrgMysqlModel = new OrgMysqlModel();    }    public function index(){        @set_time_limit(3000);        @ini_set('memory_limit', '-1');        header("content-Type: text/html; charset=utf-8");//      $t1 = microtime(true);        $this->getDept();        $this->getUser();//      $t2 = microtime(true);//      echo '耗时'.round($t2-$t1,3).'秒<br>';die;    }    public function getUser(){        //获取oa的用户名        $allsql = " select * from z_user ";        $oa_all_user_info = $this->OrgMysqlModel->mysqlSelect($allsql);        $oa_all_user_info = array_column($oa_all_user_info, NULL, 'userid');        //获取档案系统用户        $da_all_user_info = $this->restModel->select('s_user', "oaid!=''", array('field' => 'id'));        $da_all_user_info = array_column($da_all_user_info, NULL, 'id');        //数据库中有差异的数据        $in_diffdata = array();        $up_diffdata = array();        //进行比较        foreach($oa_all_user_info as $value){            if(strstr($value['userid'],'admin') !==false){                continue;            }            $password = '123456';            $userid = $value['userid'];            //档案数据库中有的进行更新，没有就新增            $dbdata = array();            if(array_key_exists($userid,$da_all_user_info)){            //  $dbdata['oaid'] = $value['id'];                $dbdata['id'] = $value['userid'];                $dbdata['name'] = $value['username'];                $dbdata['email'] = $value['email'];                $dbdata['mobileTel'] = $value['mobile_tel'];                $dbdata['status'] = '1';                $dbdata['deptid'] = $this->deptData[$value['groupid']]['id'];                $dbdata['deptname'] = $this->deptData[$value['groupid']]['groupname'];                $dbdata['groupdeptid'] = $this->deptData[$value['groupid']]['topid'];                $dbdata['groupdeptname'] = $this->deptData[$value['groupid']]['topname'];                $dbdata['fulldeptid'] = $this->deptData[$value['groupid']]['pathid'].'/';                $dbdata['fulldeptname'] = $this->deptData[$value['groupid']]['pathname'].'/';                $up_diffdata[] = $dbdata;            }else{                $dbdata['id'] = $value['userid'];                $dbdata['name'] = $value['username'];                $dbdata['email'] = $value['email'];                $dbdata['mobileTel'] = $value['mobile_tel'];                $dbdata['status'] = '1';                $dbdata['createtime'] = $dbdata['beginDate'] = date('Y-m-d');                $dbdata['endDate ']   = date('Y-m-d',time()+5*365*24*3600);                $dbdata['creater'] = 'oa_admin';                $dbdata['comid'] = '1';                $dbdata['deptid'] = $this->deptData[$value['groupid']]['id'];                $dbdata['deptname'] = $this->deptData[$value['groupid']]['groupname'];                $dbdata['groupdeptid'] = $this->deptData[$value['groupid']]['topid'];                $dbdata['groupdeptname'] = $this->deptData[$value['groupid']]['topname'];                $dbdata['fulldeptid'] = $this->deptData[$value['groupid']]['pathid'].'/';                $dbdata['fulldeptname'] = $this->deptData[$value['groupid']]['pathname'].'/';                $dbdata['password'] = md5($password);                $dbdata['oaid'] = $value['id'];                $in_diffdata[] = $dbdata;            }        }            $sql = array(); //执行插入的sql语句            //生成更新sql            if(!empty($up_diffdata)){                $key = array_keys($up_diffdata[0]);                $duplicate = '';                $duplicate .= "on duplicate key update ";                foreach($key as $kk=>$vv){                    $duplicate .= $vv.'=values('.$vv.') ,';                }                $duplicate  = rtrim($duplicate,',');                $duplicate .=  ';';                $up_execData = array_chunk($up_diffdata, 200); //将数组每一百条分一个数组                foreach ($up_execData as $k => $exec) {                        $sql[] = $this->getProxy('db')->insertAllSql($exec, array('table' => 's_user', 'fetch_sql' => true,'duplicate'=>$duplicate));                }            }            if(!empty($in_diffdata)){                $in_execData = array_chunk($in_diffdata, 200); //将数组每一百条分一个数组                foreach ($in_execData as $k => $exec) {                    $sql[] = $this->getProxy('db')->insertAllSql($exec, array('table' => 's_user', 'fetch_sql' => true), false);                }            }            $result = $this->getProxy('db')->executeList($sql);    }    public function getDept()    {        //获取oa所有部门信息        $allsql = " select * from z_group ";        $oa_all_dept_info = $this->OrgMysqlModel->mysqlSelect($allsql);        //获取档案所有部门信息        $da_all_dept_info = $this->restModel->select('organization', "oaid!=''", array('field' => 'id'));        $da_all_dept_info = array_column($da_all_dept_info, NULL, 'id');        //处理中间库部门数据        $tree = new TreeModel();        $tree->tree($oa_all_dept_info);        $oa_all_dept_info = $tree->getArraya();        //保存此信息后面用户信息处理需要用到        $this->deptData = $oa_all_dept_info;        //数据库中有差异的数据        $diffdata = array();        foreach ($oa_all_dept_info as $oak => $oav) {            if (array_key_exists($oak, $da_all_dept_info)) {//档案系统存在此组织(更新)                continue;            } else {//新增                $opdata = array();                $opdata['id'] = $oak;                $opdata['name'] = $oav['groupname'];                $opdata['shortname'] = $oav['groupname'];                $opdata['fatherid'] = $oav['fatherid'];                $opdata['level'] = $oav['level'];                $opdata['path'] = $oav['pathid'] . '/';                $opdata['status'] = 1;                $opdata['memo'] = $oav['remark'];                $opdata['pathname'] = $oav['pathname'].'/';                $opdata['type'] = 1;                $opdata['comid'] = 1;                $opdata['orderindex'] = $oav['pgroupid'];                $opdata['effectivechild'] = $oav['haschild'];                $opdata['haschild'] = $oav['haschild'];                $opdata['oaid'] = $oak;                $diffdata[] = $opdata;            }        }        if (empty($diffdata)) {            return array('status' => true, 'num' => '0');        }        $execData = array_chunk($diffdata, 10); //将数组每一百条分一个数组        $sql = array(); //执行插入的sql语句        $where = array();        $where['oaid'] !== '';        foreach ($execData as $k => $exec) {            $sql[] = $this->getProxy('db')->insertAllSql($exec, array('table' => 'organization', 'fetch_sql' => true), false);        }        $result = $this->getProxy('db')->executeList($sql);        return array('status' => true, 'num' => $result);    }}