Exemplo n.º 1
0
        void btn_Click(object sender, EventArgs e)
        {
            LinkBtn btn = sender as LinkBtn;

            if (btn.ID.StartsWith(NamesOfBtn.DataCheck + "_"))
            {
                #region //检查正确性
                string  table = btn.ID.Substring((NamesOfBtn.DataCheck + "_").Length);
                string  sql   = Pub1.GetTBByID("TB_" + table).Text;
                string  srcno = Pub1.GetDDLByID("DDL_DBSrcs").SelectedItemStringVal;
                SFDBSrc src   = new SFDBSrc(srcno);
                string  error = CheckSQL(src, gvarSModel.Val, table, sql);

                if (string.IsNullOrWhiteSpace(error))
                {
                    //如果配置正确,则把此次的SQL语句存储上
                    GloVar gvar = gvars.GetEntityByKey(table + "_Temp") as GloVar;
                    gvar.Val = sql;
                    gvar.Update();

                    Alert("配置正确!");
                }
                else
                {
                    Alert("成功获取数据,但有如下错误:<br />" + error.TrimEnd(','), "error");
                }
                #endregion
            }
            else if (btn.ID.StartsWith(NamesOfBtn.Open + "_"))
            {
                #region //打开数据源
                string    item  = btn.ID.Substring((NamesOfBtn.Open + "_").Length);
                string    value = Pub1.GetTBByID("TB_" + item).Text;
                string    srcno = Pub1.GetDDLByID("DDL_DBSrcs").SelectedItemStringVal;
                SFDBSrc   src   = new SFDBSrc(srcno);
                DataTable dt    = null;

                try
                {
                    dt = src.RunSQLReturnTable(value, 0, 100);
                }
                catch (Exception ex)
                {
                    Alert(ex.Message, "error");
                    return;
                }

                Dictionary <string, string> coldefs = gvarSModel.Val == "1" ? _oneones[item] : _onemores[item];

                Pub2.Add("<table class='easyui-datagrid' data-options='fit:true'>");

                Pub2.Add("  <thead>");
                Pub2.Add("      <tr>");

                foreach (KeyValuePair <string, string> coldef in coldefs)
                {
                    Pub2.Add(string.Format("          <th data-options=\"field:'{0}'\">{0}</th>", coldef.Key));
                }

                Pub2.Add("      </tr>");
                Pub2.Add("  </thead>");

                Pub2.Add("  <tbody>");

                foreach (DataRow row in dt.Rows)
                {
                    Pub2.Add("      <tr>");

                    foreach (KeyValuePair <string, string> coldef in coldefs)
                    {
                        if (dt.Columns.Contains(coldef.Key))
                        {
                            Pub2.Add(string.Format("          <td>{0}</td>",
                                                   row[coldef.Key] == null || row[coldef.Key] == DBNull.Value
                                                       ? ""
                                                       : row[coldef.Key]));
                        }
                        else
                        {
                            Pub2.Add("          <td></td>");
                        }
                    }

                    Pub2.Add("      </tr>");
                }

                Pub2.Add("  </tbody>");

                Pub2.Add("</table>");
                ClientScript.RegisterClientScriptBlock(this.GetType(), "showwindow", "$(function(){$('#datawin').window('open');});",
                                                       true);
                #endregion
            }
            else
            {
                switch (btn.ID)
                {
                case NamesOfBtn.Save:
                    #region     //保存并继续
                    switch (Step)
                    {
                    case 1:
                        GloVar gvar = new GloVar("StructureModel");
                        gvar.Val = Pub1.GetRadioButtonListByID("Rads_StructureModel").SelectedValue;
                        gvar.Update();
                        Response.Redirect("Integration.aspx?step=2", true);
                        break;

                    case 2:
                        gvar     = new GloVar("StructureMngKind");
                        gvar.Val = Pub1.GetRadioButtonListByID("Rads_StructureMngKind").SelectedValue;
                        gvar.Update();
                        Response.Redirect("Integration.aspx?step=3", true);
                        break;

                    case 3:
                        gvar     = new GloVar("StructureSource");
                        gvar.Val = Pub1.GetRadioButtonListByID("Rads_StructureSource").SelectedValue;
                        gvar.Update();
                        Response.Redirect("Integration.aspx?step=4", true);
                        break;
                    }
                    #endregion
                    break;

                case NamesOfBtn.Back:
                    #region     //上一步
                    switch (Step)
                    {
                    case 2:
                        Response.Redirect("Integration.aspx?step=1", true);
                        break;

                    case 3:
                        Response.Redirect("Integration.aspx?step=2", true);
                        break;

                    case 4:
                        Response.Redirect("Integration.aspx?step=3", true);
                        break;
                    }
                    #endregion
                    break;

                case NamesOfBtn.Setting:
                    #region     //设置全部
                    //循环文本控件,保存所有SQL
                    Dictionary <string, Dictionary <string, string> > coldefs = gvarSModel.Val == "1" ? _oneones : _onemores;
                    SFDBSrc src      = new SFDBSrc(gvarDBSrc.Val);
                    SFDBSrc srcLocal = new SFDBSrc("local");

                    TB            tb           = null;
                    GloVar        tvar         = null;
                    string        sql          = string.Empty;
                    string        exists       = string.Empty;
                    string        successViews = string.Empty;
                    string        errorViews   = string.Empty;
                    string        error        = string.Empty;
                    int           successIdx   = 0;
                    int           errorIdx     = 0;
                    List <string> existsObjs   = new List <string>();

                    #region checkSqls
                    Dictionary <string, string> checkSqlsOneOne = new Dictionary <string, string>
                    {
                        {
                            "Port_Emp,Port_Dept",
                            "SELECT * FROM {Port_Emp} t1 WHERE t1.FK_Dept NOT IN (SELECT t2.No FROM {Port_Dept} t2)"
                        },
                        {
                            "Port_Station,Sys_Enum",
                            "SELECT * FROM {Port_Station} t1 WHERE t1.StaGrade NOT IN ({Sys_Enum})"
                        },
                        {
                            "Port_EmpStation,Port_Emp",
                            "SELECT * FROM {Port_EmpStation} t1 WHERE t1.FK_Emp NOT IN (SELECT t2.No FROM {Port_Emp} t2)"
                        },
                        {
                            "Port_EmpStation,Port_Station",
                            "SELECT * FROM {Port_EmpStation} t1 WHERE t1.FK_Station NOT IN (SELECT t2.No FROM {Port_Station} t2)"
                        },
                        {
                            "Port_EmpDept,Port_Emp",
                            "SELECT * FROM {Port_EmpDept} t1 WHERE t1.FK_Emp NOT IN (SELECT t2.No FROM {Port_Emp} t2)"
                        },
                        {
                            "Port_EmpDept,Port_Dept",
                            "SELECT * FROM {Port_EmpDept} t1 WHERE t1.FK_Dept NOT IN (SELECT t2.No FROM {Port_Dept} t2)"
                        }
                    };
                    Dictionary <string, string> checkSqlsOneMore = new Dictionary <string, string>
                    {
                        {
                            "Port_Station,Port_StationType",
                            "SELECT * FROM {Port_Station} ps WHERE ps.FK_StationType NOT IN (SELECT pst.No FROM {Port_StationType} pst)"
                        },
                        {
                            "Port_DeptDuty,Port_Dept",
                            "SELECT * FROM {Port_DeptDuty} pdd WHERE pdd.FK_Dept NOT IN (SELECT pd.No FROM {Port_Dept} pd)"
                        },
                        {
                            "Port_DeptDuty,Port_Duty",
                            "SELECT * FROM {Port_DeptDuty} pdd WHERE pdd.FK_Duty NOT IN (SELECT pd.No FROM {Port_Duty} pd)"
                        },
                        {
                            "Port_DeptStation,Port_Dept",
                            "SELECT * FROM {Port_DeptStation} pds WHERE pds.FK_Dept NOT IN (SELECT pd.No FROM {Port_Dept} pd)"
                        },
                        {
                            "Port_DeptStation,Port_Station",
                            "SELECT * FROM {Port_DeptStation} pds WHERE pds.FK_Station NOT IN (SELECT ps.No FROM {Port_Station} ps)"
                        },
                        {
                            "Port_Emp,Port_Dept",
                            "SELECT * FROM {Port_Emp} pe WHERE pe.FK_Dept NOT IN (SELECT pd.No FROM {Port_Dept} pd)"
                        },
                        {
                            "Port_Emp,Port_Duty",
                            "SELECT * FROM {Port_Emp} pe WHERE pe.FK_Duty NOT IN (SELECT pd.No FROM {Port_Duty} pd)"
                        },
                        {
                            "Port_DeptEmpStation,Port_Dept",
                            "SELECT * FROM {Port_DeptEmpStation} pdes WHERE pdes.FK_Dept NOT IN (SELECT pd.No FROM {Port_Dept} pd)"
                        },
                        {
                            "Port_DeptEmpStation,Port_Station",
                            "SELECT * FROM {Port_DeptEmpStation} pdes WHERE pdes.FK_Station NOT IN (SELECT ps.No FROM {Port_Station} ps)"
                        },
                        {
                            "Port_DeptEmpStation,Port_Emp",
                            "SELECT * FROM {Port_DeptEmpStation} pdes WHERE pdes.FK_Emp NOT IN (SELECT pe.No FROM {Port_Emp} pe)"
                        },
                        {
                            "Port_DeptEmp,Port_Dept",
                            "SELECT * FROM {Port_DeptEmp} pde WHERE pde.FK_Dept NOT IN (SELECT pd.No FROM {Port_Dept} pd)"
                        },
                        {
                            "Port_DeptEmp,Port_Emp",
                            "SELECT * FROM {Port_DeptEmp} pde WHERE pde.FK_Emp NOT IN (SELECT pe.No FROM {Port_Emp} pe)"
                        },
                        {
                            "Port_DeptEmp,Port_Duty",
                            "SELECT * FROM {Port_DeptEmp} pde WHERE pde.FK_Duty NOT IN (SELECT pd.No FROM {Port_Duty} pd)"
                        }
                    };
                    #endregion

                    Dictionary <string, string> checkSqls = gvarSModel.Val == "1" ? checkSqlsOneOne : checkSqlsOneMore;

                    try
                    {
                        #region     //保存配置,并初步检查各数据表返回数据的有效性
                        foreach (KeyValuePair <string, Dictionary <string, string> > def in coldefs)
                        {
                            tb       = Pub1.GetTBByID("TB_" + def.Key);
                            tvar     = gvars.GetEntityByKey(def.Key + "_Temp") as GloVar;
                            tvar.Val = tb.Text;
                            tvar.Update();

                            error = CheckSQL(src, gvarSModel.Val, def.Key, tvar.Val);

                            if (string.IsNullOrWhiteSpace(error))
                            {
                                successViews += (++successIdx) + ". " + def.Key + "<br />";
                            }
                            else
                            {
                                errorViews += (++errorIdx) + ". " + def.Key + "<br />&nbsp;&nbsp;&nbsp;错误信息:" + error + "<br />";
                            }
                        }

                        if (!string.IsNullOrWhiteSpace(errorViews))
                        {
                            Alert("配置SQL检查结果:<br />配置正确的表有:<br />"
                                  + successViews
                                  + "<br />配置错误的表有:<br />"
                                  + errorViews
                                  + "<br />请检查后重新设置!", "error");
                            return;
                        }
                        #endregion

                        #region     //检查admin,如果没有admin则允许创建视图;如果admin不属于顶级部门,则也不允许创建视图
                        sql = "SELECT * FROM {Port_Emp} pe WHERE pe.No = 'admin'".Replace("{Port_Emp}",
                                                                                          "(" + (gvars.GetEntityByKey("Port_Emp_Temp") as GloVar).Val + ")");

                        DataTable dt = src.RunSQLReturnTable(sql);
                        if (dt.Rows.Count == 0)
                        {
                            Alert("配置的Port_Emp中,必须含有No='admin'的超级管理员数据!", "error");
                            return;
                        }

                        if (dt.Rows[0]["FK_Dept"] == null || dt.Rows[0]["FK_Dept"] == DBNull.Value || string.IsNullOrWhiteSpace(dt.Rows[0]["FK_Dept"].ToString()))
                        {
                            Alert("配置的Port_Emp中,No='admin'的超级管理员数据中,FK_Dept部门字段必须有值!", "error");
                            return;
                        }

                        sql = "SELECT pd.ParentNo FROM {Port_Dept} pd WHERE pd.No = '" + dt.Rows[0]["FK_Dept"] + "'";
                        dt  = src.RunSQLReturnTable(sql.Replace("{Port_Dept}",
                                                                "(" + (gvars.GetEntityByKey("Port_Dept_Temp") as GloVar).Val + ")"));

                        if (dt.Rows.Count == 0)
                        {
                            Alert("配置的Port_Emp中,No='admin'的超级管理员数据中,FK_Dept部门不存在!", "error");
                            return;
                        }

                        if (dt.Rows[0][0] != null && dt.Rows[0][0] != DBNull.Value)
                        {
                            sql = "SELECT * FROM {Port_Dept} pd WHERE pd.No = '" + dt.Rows[0][0] + "'";
                            dt  = src.RunSQLReturnTable(sql.Replace("{Port_Dept}",
                                                                    "(" + (gvars.GetEntityByKey("Port_Dept_Temp") as GloVar).Val + ")"));

                            if (dt.Rows.Count > 0)
                            {
                                Alert("配置的Port_Emp中,No='admin'的超级管理员数据中,FK_Dept部门必须是顶级部门!", "error");
                                return;
                            }
                        }
                        #endregion

                        #region     //根据SQL,检查数据完整性,有错误数据,给出提示
                        string[] tables = null;
                        foreach (KeyValuePair <string, string> cs in checkSqls)
                        {
                            tables = cs.Key.Split(',');
                            sql    = cs.Value;

                            foreach (string table in tables)
                            {
                                if (!sql.Contains("{" + table + "}"))
                                {
                                    continue;
                                }

                                if (table == "Sys_Enum")
                                {
                                    SysEnums enums  = new SysEnums("StaGrade");
                                    string   grades = string.Empty;

                                    foreach (SysEnum en in enums)
                                    {
                                        grades += en.IntKey + ",";
                                    }

                                    sql = sql.Replace("{" + table + "}", grades.TrimEnd(','));
                                }
                                else
                                {
                                    sql = sql.Replace("{" + table + "}",
                                                      "(" + (gvars.GetEntityByKey(table + "_Temp") as GloVar).Val + ")");
                                }
                            }

                            dt = src.RunSQLReturnTable(sql);

                            if (dt.Rows.Count > 0)
                            {
                                errorViews += (++errorIdx) + ". " + sql + "<br />";
                            }
                        }
                        #endregion

                        #region     //创建两方数据库的组织结构视图

                        GloVar gvar = null;
                        foreach (KeyValuePair <string, Dictionary <string, string> > def in coldefs)
                        {
                            tvar     = gvars.GetEntityByKey(def.Key + "_Temp") as GloVar;
                            gvar     = gvars.GetEntityByKey(def.Key) as GloVar;
                            gvar.Val = tvar.Val;
                            gvar.Update();

                            //判断数据源上是否已经存在同名的表或视图
                            exists = src.IsExistsObj(def.Key);
                            if (!string.IsNullOrEmpty(exists))
                            {
                                if (exists == "TABLE" || exists == "VIEW")
                                {
                                    src.Rename(exists, def.Key,
                                               def.Key + "_Bak" + DateTime.Now.ToString("MMddHHmmss"));
                                }
                                existsObjs.Add(def.Key);
                            }

                            //在数据源所在数据库上建立视图
                            sql = "CREATE VIEW " + def.Key + Environment.NewLine +
                                  "AS" + Environment.NewLine + tvar.Val;
                            src.RunSQL(sql);

                            //在CCFlow主库上建立与数据源库视图的联接,也建一个视图
                            //判断主数据库里是否已经存在同名的表或视图,如果有,则改名
                            exists = srcLocal.IsExistsObj(def.Key);
                            if (!string.IsNullOrEmpty(exists))
                            {
                                if (exists == "TABLE" || exists == "VIEW")
                                {
                                    srcLocal.Rename(exists, def.Key, def.Key + "_Bak" + DateTime.Now.ToString("MMddHHmmss"));
                                }
                            }

                            sql = string.Format("CREATE VIEW {0} AS SELECT * FROM {1}", def.Key, src.GetLinkedServerObjName(def.Key));
                            BP.DA.DBAccess.RunSQL(sql);
                        }
                        #endregion

                        //todo:oneone下部门人员表SQL自动写入,待处理
                        //tvar = gvars.GetEntityByKey("StructureDBOver") as GloVar;
                        //tvar.Val = true.ToString();
                        //tvar.Update();

                        if (!string.IsNullOrWhiteSpace(errorViews))
                        {
                            Alert("创建成功,但检查到数据完整性有以下错误:<br />"
                                  + errorViews, "error");
                        }
                        else
                        {
                            Alert("创建成功!");
                        }
                    }
                    catch (Exception ex)
                    {
                        Alert(ex.Message, "error");
                    }
                    #endregion
                    break;
                }
            }
        }