public bool Execute()
        {

            Dbconn conn = new Dbconn("MDM");

            //取到用户信息
            DataTable dt_User = conn.GetDataTable(
                @"  SELECT  t1.AM_User_id ,
                            t1.TfgMachineNo ,
                            t1.ComputerName ,
                            t2.AM_LOGIN
                    FROM    dbo.AM_TFG_Relation t1 WITH ( NOLOCK )
                            LEFT JOIN dbo.AM_User t2 WITH ( NOLOCK ) ON t1.AM_User_id = t2.AM_User_id
             
                     ", new string[0]);

            DbconnMysql connMysqlTFG = new DbconnMysql("TFG_infoguard");

            //取到部门信息,只取到两级目录,并且只会室与部门
            DataTable dt_user_dep = conn.GetDataTable(@" 
                        
                     SELECT DISTINCT
                            t1.AM_User_id ,
                            CASE WHEN t3.AM_Dept1_ID IS NULL THEN t3.AM_Company
                                 ELSE t3.AM_Dept1_ID
                            END AM_S_Dept_ID
                     FROM   dbo.AM_TFG_Relation t1 WITH ( NOLOCK )
                            LEFT JOIN dbo.AM_User_Dept t2 WITH ( NOLOCK ) ON t1.AM_User_id = t2.AM_User_id
                            LEFT JOIN dbo.AM_Dept t3 WITH ( NOLOCK ) ON t2.AM_S_Dept_ID = t3.AM_S_Dept_ID
                            LEFT JOIN dbo.AM_Dept t4 WITH ( NOLOCK ) ON t3.AM_Dept1_ID = t4.AM_S_Dept_ID
                     WHERE  t3.[AM_Dept_NAME_All] LIKE '01%'
                            AND ( t4.AM_Dept_NAME IS NULL
                                  OR t4.AM_Dept_NAME LIKE '%部'
                                  OR t4.AM_Dept_NAME LIKE '%室'
                                )
                        ", new string[0]);
            try
            {

                ArrayList tableList = new ArrayList();
                connMysqlTFG.BeginTransaction();


                tableList.Add("Info_Term");
                connMysqlTFG.TableLock(tableList);

                //更新TFG服务器的用户姓名
                for (int i = 0; i < dt_User.Rows.Count; i++)
                {
                    DataTable dt_Update_Info_Term = new DataTable();
                    dt_Update_Info_Term.Columns.Add("sn");
                    dt_Update_Info_Term.Columns.Add("name");

                    DataRow dr = dt_Update_Info_Term.NewRow();
                    dr["sn"] = dt_User.Rows[i]["TfgMachineNo"];
                    dr["name"] = dt_User.Rows[i]["AM_LOGIN"];
                    dt_Update_Info_Term.Rows.Add(dr);

                    string strWhere = "sn='" + dt_User.Rows[i]["TfgMachineNo"] + "'";

                    connMysqlTFG.Update("Info_Term", dt_Update_Info_Term, strWhere);

                }

                //删除用户与部门数据
                connMysqlTFG.Excute("Delete FROM info_dep_term");

                //插入用户与部门数据
                DataTable dt_trem_dep = new DataTable();
                dt_trem_dep.Columns.Add("term_id");
                dt_trem_dep.Columns.Add("dep_id");

                for (int i = 0; i < dt_User.Rows.Count; i++)
                {

                    string strSN = dt_User.Rows[i]["TfgMachineNo"].ToString();
                    string strcn_id = dt_User.Rows[i]["AM_User_id"].ToString();
                    string[] strParam = new string[1];


                    strParam[0] = dt_User.Rows[i]["TfgMachineNo"].ToString();

                    DataTable dt_info_term = connMysqlTFG.GetDataTable(@"
                                                SELECT id,
                                                    sn 
                                                FROM info_term
                                                WHERE sn=?Param0", strParam);
                    for (int j = 0; j < dt_info_term.Rows.Count; j++)
                    {
                        string strID = dt_info_term.Rows[j]["id"].ToString();

                        DataRow[] drList = dt_user_dep.Select("AM_User_id=" + strcn_id, "AM_S_Dept_ID ASC");
                        foreach (DataRow dr in drList)
                        {
                            DataRow drAdd = dt_trem_dep.NewRow();
                            drAdd["term_id"] = strID;
                            drAdd["dep_id"] = dr["AM_S_Dept_ID"];
                            dt_trem_dep.Rows.Add(drAdd);
                        }

                    }

                }

                if (dt_trem_dep.Rows.Count > 0)
                {
                    connMysqlTFG.Insert("info_dep_term", dt_trem_dep);
                }

                //取到TFG的所有有效用户的没有小翅膀组织架构信息的铁卷用户
                DataTable dt_NO_dept = connMysqlTFG.GetDataTable(@"select name,id from info_term
where enabled<>2
and not exists (select 1 from info_dep_term where info_term.id=info_dep_term.term_id)"
                    , new string[0]);


                for (int i = 0; i < dt_NO_dept.Rows.Count; i++)
                {
                    string strName = dt_NO_dept.Rows[i]["name"].ToString();
                    string strID = dt_NO_dept.Rows[i]["id"].ToString();

                    //找到小翅膀对应的用户的组织架构
                    DataTable dt_User_temp = conn.GetDataTable(@"  
                                    
                                   SELECT  DISTINCT CASE WHEN t3.am_dept1_id IS NULL THEN t3.AM_Company ELSE t3.am_dept1_id END AM_S_Dept_ID
                                    FROM    dbo.AM_User t1 WITH ( NOLOCK )
                                            JOIN dbo.AM_User_Dept t2 WITH ( NOLOCK ) ON t1.AM_User_id = t2.AM_USER_ID
                                            JOIN dbo.AM_Dept t3 WITH ( NOLOCK ) ON t2.AM_S_Dept_ID = t3.AM_S_Dept_ID
                                            LEFT JOIN dbo.AM_Dept t4 WITH(NOLOCK) ON t3.am_dept1_id=t4.AM_S_Dept_ID
                                    WHERE   t1.AM_login ='******'
                                    AND (t4.AM_S_Dept_ID IS NULL --公司 
                                    OR t4.AM_Dept_NAME LIKE '%部'
                                    OR t4.AM_Dept_NAME LIKE '%室'
                                    )
                                    AND t3.AM_Dept_NAME_All like '01%'
", new string[0]);

                    if (dt_User_temp.Rows.Count > 0)
                    {

                        DataTable dtUPdate = new DataTable();
                        dtUPdate.Columns.Add("term_id");
                        dtUPdate.Columns.Add("dep_id");
                        foreach (DataRow dr in dt_User_temp.Rows)
                        {
                            DataRow drNew = dtUPdate.NewRow();
                            drNew["dep_id"] = dr["AM_S_Dept_ID"];
                            drNew["term_id"] = strID;
                            dtUPdate.Rows.Add(drNew);
                        }

                        connMysqlTFG.Insert("info_dep_term", dtUPdate);
                    }
                }

                connMysqlTFG.CommitTransaction();
                return true;

            }
            catch
            {
                connMysqlTFG.RollbackTransaction();
                throw;
                
            }
        }
Exemplo n.º 2
0
        public bool Execute()
        {

            Dbconn conn = new Dbconn("MDM");
            DataTable dt = conn.GetDataTable( //只取一级目录,并且名称为部或者室的部门
                @"  

               SELECT 
                    t1.AM_S_Dept_ID AS ID,
                    t1.AM_P_Dept_ID AS superior_dep,
                    t1.AM_Dept_NAME AS name,
                    '1' AS enabled,
                    '1' AS auto_approve
                FROM dbo.AM_Dept t1
                LEFT JOIN dbo.AM_Dept t2 WITH(NOLOCK) ON t1.am_dept1_id=t2.AM_S_Dept_ID
                WHERE t1.am_dept2_id IS NULL
                AND t1.[AM_Dept_NAME_All] LIKE '01%'
                AND (t1.am_dept1_id IS NULL OR
                t2.AM_Dept_NAME LIKE '%室'
                OR t2.AM_Dept_NAME LIKE '%中心'
                OR t2.AM_Dept_NAME LIKE'%部')
                    
                    ", new string[0]);


            //过滤非01开始的公司组织架构


            //将一级公司的父节点改成1
            DataRow[] drList = dt.Select("superior_dep=0");
            foreach (DataRow dr1 in drList)
            {
                dr1["superior_dep"] = "1";
            }


            //添加一行主目录
            DataRow dr = dt.NewRow();
            dr["ID"] = "1";
            dr["superior_dep"] = "0";
            dr["name"] = "华之毅集团";
            dr["enabled"] = "1";
            dr["auto_approve"] = "1";
            dt.Rows.Add(dr);



            DbconnMysql connMysqlTFG = new DbconnMysql("TFG_infoguard");

            try
            {


                ArrayList tableList = new ArrayList();
                connMysqlTFG.BeginTransaction();
 
                tableList.Add("info_dep");
                connMysqlTFG.TableLock(tableList);
                
                connMysqlTFG.ExcuteQuerry("DELETE FROM info_dep;");

                connMysqlTFG.Insert("info_dep", dt);

                connMysqlTFG.CommitTransaction();
                return true;

            }
            catch
            {
                connMysqlTFG.RollbackTransaction();
                throw;
            }
        }