从浙政钉-企业微信-钉钉同步数据步骤

• 1. 1. 1. 概述
• 本章节介绍如何把数据同步到开源的驰骋工作流引擎上去。
• 在于浙政钉-企业微信-钉钉进行组织结构同步的代码步骤.
• 首先需要理解ccbpm的部门、人员、以及部门于人员对应关系的表结构.
• 理解OrgNo的组织编号概念.
• 理解集团模式，SAAS模式的概念.
  1. 1. 1. 同步步骤.

1. 创建空白的表结构, Port_Dept_TB, Port_Emp_TB,Port_DeptEmp_TB, 表结构与现有的表结构一致。
2. 把数据同步到这几个临时表里.
3. 执行如下代码逻辑进行同步到ccbpm的组织结构里.
   1. 1. 1. 代码同步逻辑.

• 每个表都涉及到，新增部分，删除部分，变更部分三个操作.
• 因为部门与人员对应关系都是外键，所以不涉及到修改部分.
• c#代码结构.

 string orgNo = BP.Web.WebUser.OrgNo;

            //1. 删除全部的数据.

            string sql = "DELETE FROM Port_Dept_TB WHERE OrgNo='"+orgNo+"'";

            DBAccess.RunSQL(sql);

            sql = "DELETE FROM Port_Emp_TB WHERE OrgNo='" + orgNo + "'";

            DBAccess.RunSQL(sql);

            sql = "DELETE FROM Port_DeptEmp_TB WHERE OrgNo='" + orgNo + "'";

            DBAccess.RunSQL(sql);

            //2. 把获得数据写入到这三个表.

            //3. 开始写入人员数据表.

            //3.1 更新部门,查询出来新增部分.

            sql = "SELECT * FROM Port_Dept_TB WHERE No Not IN (SELECT No from Port_Dept where OrgNo='" + orgNo+"') AND OrgNo='"+orgNo+"'";

            DataTable dtDept = DBAccess.RunSQLReturnTable(sql);

            foreach (DataRow dr in dtDept.Rows)

            {

                Dept dept = new Dept();

                dept.No = dr[0].ToString();

                dept.Name = dr[1].ToString();

                dept.ParentNo = dr[2].ToString();

                dept.Insert();

            }

            //3.2 查询出来删除的.

            sql = "SELECT * FROM Port_Dept WHERE No Not IN ( SELECT No from Port_Dept_TB where OrgNo='" + orgNo + "') AND OrgNo='" + orgNo + "'";

            dtDept = DBAccess.RunSQLReturnTable(sql);

            foreach (DataRow dr in dtDept.Rows)

            {

                Dept dept = new Dept();

                dept.No = dr[0].ToString();

                dept.Delete();

            }

            //3.3 查询出来名称变化的.

            sql = "SELECT A.No,A.Name FROM Port_Dept_TB A, Port_Dept B WHERE A.No=B.No AND A.Name!=B.Name ";

            dtDept = DBAccess.RunSQLReturnTable(sql);

            foreach (DataRow dr in dtDept.Rows)

            {

                Dept dept = new Dept();

                dept.No = dr[0].ToString();

                dept.Retrieve();

                dept.Name = dr[1].ToString();

                dept.Update();

            }

            //4. 同步人员数据.

            //4.1 更新部门,查询出来新增部分.

            sql = "SELECT * FROM Port_Emp_TB WHERE No Not IN (SELECT No from Port_Emp where OrgNo='" + orgNo + "') AND OrgNo='" + orgNo + "'";

            DataTable dtEmp = DBAccess.RunSQLReturnTable(sql);

            foreach (DataRow dr in dtEmp.Rows)

            {

                Emp emp = new Emp();

                emp.No = dr[0].ToString();

                emp.Name = dr[1].ToString(); 

                emp.OrgNo = dr[2].ToString();

                emp.Pass = "";

                emp.Insert();

            }

            //4.2 查询出来删除的.

            sql = "SELECT * FROM Port_Emp WHERE No Not IN ( SELECT No from Port_Emp_TB where OrgNo='" + orgNo + "') AND OrgNo='" + orgNo + "'";

            dtEmp = DBAccess.RunSQLReturnTable(sql);

            foreach (DataRow dr in dtEmp.Rows)

            {

                Emp emp = new Emp();

                emp.No = dr[0].ToString();

                emp.Delete();

            }

            //4.3 查询出来名称变化的.

            sql = "SELECT A.No,A.Name FROM Port_Emp_TB A, Port_Emp B WHERE A.No=B.No AND A.Name!=B.Name ";

            dtEmp = DBAccess.RunSQLReturnTable(sql);

            foreach (DataRow dr in dtEmp.Rows)

            {

                Emp emp = new Emp();

                emp.No = dr[0].ToString();

                emp.Retrieve();

                emp.Name = dr[1].ToString();

                emp.Update();

            }

            //5. 同步人员部门数据.

            //5.1 更新部门,查询出来新增部分.

            sql = "SELECT * FROM Port_DeptEmp_TB WHERE MyPK Not IN (SELECT MyPK from Port_DeptEmp WHERE OrgNo='" + orgNo + "') AND OrgNo='" + orgNo + "'";

            DataTable dtDeptEmp = DBAccess.RunSQLReturnTable(sql);

            foreach (DataRow dr in dtDeptEmp.Rows)

            {

                DeptEmp de = new DeptEmp();

                de.MyPK = dr[0].ToString();

                de.FK_Emp = dr[1].ToString();

                de.FK_Dept = dr[2].ToString();

                de.OrgNo = dr[3].ToString();

                de.Insert();

            }

            //5.2 查询出来删除的.

            sql = "SELECT * FROM Port_DeptEmp WHERE No Not IN ( SELECT No from Port_DeptEmp_TB where OrgNo='" + orgNo + "') AND OrgNo='" + orgNo + "'";

            dtDeptEmp = DBAccess.RunSQLReturnTable(sql);

            foreach (DataRow dr in dtDeptEmp.Rows)

            {

                DeptEmp de = new DeptEmp();

                de.MyPK = dr[0].ToString();

                de.Delete();

            }