using System;
using System.Collections.Generic;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using EKETEAM.Data;
using EKETEAM.FrameWork;

namespace eFrameWork.Manage
{
    public partial class Compare : System.Web.UI.Page
    {
        public string olddb = "";
        public string newdb = "";
        public string pattern = "";
        public string alltable = "";
        private void add()
        {
            string table = eParameters.QueryString("table");
            string code = eParameters.QueryString("code");
            eBase.DataBase.Execute("use " + newdb + ";");
            DataTable dt = eBase.DataBase.getSchemaColumns(table);
            DataRow[] rows = dt.Select("COLUMN_NAME='" + code + "'");
            if (rows.Length > 0)
            {
                eBase.DataBase.Execute("use " + olddb + ";");
                eBase.DataBase.addSchemaColumn(table, rows[0]);
            }
            string ajax = eParameters.QueryString("ajax");
            if (ajax == "true")
            {
                Response.End();

            }
            else
            {
                Response.Redirect(eBase.getAspxFileName(), true);
            }
        }
        private void create()
        {
            string table = eParameters.QueryString("table");
            eBase.DataBase.Execute("use " + newdb + ";");
            DataTable dt = eBase.DataBase.getSchemaColumns(table);
            eBase.DataBase.Execute("use " + olddb + ";");
            eBase.DataBase.SchemaCreate(dt);
            string ajax = eParameters.QueryString("ajax");
            if (ajax == "true")
            {
                Response.End();

            }
            else
            {
                Response.Redirect(eBase.getAspxFileName(), true);
            }
        }
        private void upcreate()
        {
            string table = eParameters.QueryString("table");
            eBase.DataBase.Execute("use " + newdb + ";");
            DataTable dt = eBase.DataBase.getSchemaColumns(table, false);//最新结构
            string pktype = "string";
            DataRow[] rs = dt.Select("Convert(PrimaryKey, 'System.String') in ('true','1')");
            if (rs.Length > 0)
            {
                pktype = rs[0]["DATA_TYPE"].ToString().ToLower();
            }            

            if (dt.Rows.Count > 0)
            {
                eBase.DataBase.Execute("use " + olddb + ";");
                string temp = eBase.DataBase.getValue("select count(1) from " + eBase.DataBase.StartNameSplitChar + table + eBase.DataBase.EndNameSplitChar);
                int count = Convert.ToInt32(temp);


               
                eBase.DataBase.renameSchemaTable(table, table + "_BAK");//重命名原来表               
                temp = eBase.DataBase.getValue("select count(1) from " + eBase.DataBase.StartNameSplitChar + table + "_BAK" + eBase.DataBase.EndNameSplitChar);
                if (temp.Length > 0) //重命名成功
                {
                    DataTable dt2 = eBase.DataBase.getSchemaColumns(table + "_BAK", false);//本库结构

                    #region 原表增加字段
                    int maxpos = 0;
                    foreach (DataRow dr in dt2.Rows)
                    {
                        string name = dr["COLUMN_NAME"].ToString();
                        DataRow[] _rs = dt.Select("COLUMN_NAME='" + name + "'");
                        if (_rs.Length == 1)
                        {
                            maxpos = Convert.ToInt32(_rs[0]["ORDINAL_POSITION"]);
                            continue;
                        }
                        _rs = dt.Select("ORDINAL_POSITION>" + maxpos.ToString());
                        foreach (DataRow _dr in _rs)
                        {
                            _dr["ORDINAL_POSITION"] = Convert.ToInt32(_dr["ORDINAL_POSITION"]) + 1;
                        }

                        maxpos++;
                        DataRow row = dt.Rows.Add();
                        row["TABLE_NAME"] = dr["TABLE_NAME"];
                        row["COLUMN_NAME"] = dr["COLUMN_NAME"];
                        row["ORDINAL_POSITION"] = maxpos;
                        row["COLUMN_DEFAULT"] = dr["COLUMN_DEFAULT"];
                        row["IS_NULLABLE"] = dr["IS_NULLABLE"];
                        row["DATA_TYPE"] = dr["DATA_TYPE"];
                        row["CHARACTER_MAXIMUM_LENGTH"] = dr["CHARACTER_MAXIMUM_LENGTH"];
                        row["NUMERIC_PRECISION"] = dr["NUMERIC_PRECISION"];
                        row["NUMERIC_SCALE"] = dr["NUMERIC_SCALE"];
                        row["DESCRIPTION"] = dr["DESCRIPTION"];
                        row["PrimaryKey"] = dr["PrimaryKey"];
                    }
                    dt = dt.Select("", "ORDINAL_POSITION").toDataTable();
                    #endregion
                    //eBase.Writeln( dt.ExtendedProperties["name"].ToString()  +":A");
                    //eBase.End();
                    eBase.DataBase.SchemaCreate(dt);
                    temp = eBase.DataBase.getValue("select count(1) from " + eBase.DataBase.StartNameSplitChar + table + eBase.DataBase.EndNameSplitChar);
                    if (temp.Length == 0) //新表创建失败
                    {
                        eBase.DataBase.renameSchemaTable(table + "_BAK", table);
                    }
                    else //新表创建成功
                    {
                        if (count == 0)//空表
                        {
                            eBase.DataBase.removeSchemaTable(table + "_BAK");
                        }
                        else
                        {
                            #region 有数据
                            

                            List<string> fields = new List<string>();
                            string sql = "insert into " + eBase.DataBase.StartNameSplitChar + table + eBase.DataBase.EndNameSplitChar;
                            for (int i = 0; i < dt2.Rows.Count; i++)
                            {
                                string field = dt2.Rows[i]["COLUMN_NAME"].ToString();
                                DataRow[] rows = dt.Select("COLUMN_NAME='" + field + "'");
                                if (rows.Length > 0) fields.Add(eBase.DataBase.StartNameSplitChar + field + eBase.DataBase.EndNameSplitChar);
                            }
                            sql += " (" + string.Join(",", fields) + ") ";
                            sql += "select " + string.Join(",", fields) + " from " + eBase.DataBase.StartNameSplitChar + table + "_BAK" + eBase.DataBase.EndNameSplitChar;

                            if (eBase.DataBase.DataBaseType == eDataBaseType.SQLServer && pktype.IndexOf("int") > -1) eBase.DataBase.Execute("SET IDENTITY_INSERT [dbo].[" + table + "] ON;");
                            eBase.DataBase.Execute(sql);
                            if (eBase.DataBase.DataBaseType == eDataBaseType.SQLServer && pktype.IndexOf("int") > -1) eBase.DataBase.Execute("SET IDENTITY_INSERT [dbo].[" + table + "] OFF;");


                            temp = eBase.DataBase.getValue("select count(1) from " + eBase.DataBase.StartNameSplitChar + table + eBase.DataBase.EndNameSplitChar);
                            int count2 = temp.Length > 0 ? Convert.ToInt32(temp) : 0;
                            if (count == count2)//成功
                            {
                                eBase.DataBase.removeSchemaTable(table + "_BAK");
                            }
                            else//数据不对
                            {
                                //删除新建
                                eBase.DataBase.removeSchemaTable(table);
                                //重命名原来
                                eBase.DataBase.renameSchemaTable(table + "_BAK", table);
                            }

                            //eBase.PrintDataTable(dt);
                            //eBase.PrintDataTable(dt2);
                            #endregion
                        }
                    }
                }
            }
            string ajax = eParameters.QueryString("ajax");
            if (ajax == "true")
            {
                Response.End();

            }
            else
            {
                Response.Redirect(eBase.getAspxFileName(), true);
            }
        }
        private void upcreate_replace()//完全按照新格式，原表增加的字段被忽略
        {
            //方式一：eBase.DataBase.SchemaCreate(dtn);自动判断更新
            //方式二：
            string table = eParameters.QueryString("table");
            eBase.DataBase.Execute("use " + newdb + ";");
            DataTable dt = eBase.DataBase.getSchemaColumns(table, false);//最新结构
            string pktype = "string";
            DataRow[] rs = dt.Select("Convert(PrimaryKey, 'System.String') in ('true','1')");
            if (rs.Length > 0)
            {
                pktype = rs[0]["DATA_TYPE"].ToString().ToLower();
            }          
            if (dt.Rows.Count > 0)
            {
                eBase.DataBase.Execute("use " + olddb + ";");
                string temp = eBase.DataBase.getValue("select count(1) from " + eBase.DataBase.StartNameSplitChar + table +  eBase.DataBase.EndNameSplitChar);
                int count = Convert.ToInt32(temp);
                eBase.DataBase.renameSchemaTable(table, table + "_BAK");//重命名原来表               
                temp = eBase.DataBase.getValue("select count(1) from " + eBase.DataBase.StartNameSplitChar + table + "_BAK" + eBase.DataBase.EndNameSplitChar);            
                if (temp.Length > 0) //重命名成功
                {
                    //eBase.Writeln( dt.ExtendedProperties["name"].ToString()  +":A");
                    //eBase.End();
                    eBase.DataBase.SchemaCreate(dt);
                    temp = eBase.DataBase.getValue("select count(1) from " + eBase.DataBase.StartNameSplitChar + table + eBase.DataBase.EndNameSplitChar);
                    if (temp.Length == 0) //新表创建失败
                    {
                        eBase.DataBase.renameSchemaTable(table + "_BAK", table);
                    }
                    else //新表创建成功
                    {
                        if (count == 0)//空表
                        {
                            eBase.DataBase.removeSchemaTable(table + "_BAK");
                        }
                        else
                        {
                            #region 有数据
                            DataTable dt2 = eBase.DataBase.getSchemaColumns(table + "_BAK",false);//本库结构
                            List<string> fields = new List<string>();
                            string sql = "insert into " + eBase.DataBase.StartNameSplitChar + table + eBase.DataBase.EndNameSplitChar;
                            for (int i = 0; i < dt2.Rows.Count; i++)
                            {
                                string field = dt2.Rows[i]["COLUMN_NAME"].ToString();
                                DataRow[] rows = dt.Select("COLUMN_NAME='" + field + "'");
                                if (rows.Length > 0) fields.Add(eBase.DataBase.StartNameSplitChar + field + eBase.DataBase.EndNameSplitChar);
                            }
                            sql += " (" + string.Join(",", fields) + ") ";
                            sql += "select " + string.Join(",", fields) + " from " + eBase.DataBase.StartNameSplitChar + table + "_BAK" + eBase.DataBase.EndNameSplitChar;

                            if (eBase.DataBase.DataBaseType == eDataBaseType.SQLServer && pktype.IndexOf("int") > -1) eBase.DataBase.Execute("SET IDENTITY_INSERT [dbo].[" + table + "] ON;");
                            eBase.DataBase.Execute(sql);
                            if (eBase.DataBase.DataBaseType == eDataBaseType.SQLServer && pktype.IndexOf("int") > -1) eBase.DataBase.Execute("SET IDENTITY_INSERT [dbo].[" + table + "] OFF;");


                            temp = eBase.DataBase.getValue("select count(1) from " + eBase.DataBase.StartNameSplitChar + table + eBase.DataBase.EndNameSplitChar);
                            int count2 = temp.Length > 0 ? Convert.ToInt32(temp) : 0;
                            if (count == count2)//成功
                            {
                                eBase.DataBase.removeSchemaTable(table + "_BAK");
                            }
                            else//数据不对
                            {
                                //删除新建
                                eBase.DataBase.removeSchemaTable(table);
                                //重命名原来
                                eBase.DataBase.renameSchemaTable(table + "_BAK", table);
                            }

                            //eBase.PrintDataTable(dt);
                            //eBase.PrintDataTable(dt2);
                            #endregion
                        }
                    }
                }
            }
            string ajax = eParameters.QueryString("ajax");
            if (ajax == "true")
            {
                Response.End();

            }
            else
            {
                Response.Redirect(eBase.getAspxFileName(), true);
            }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            string sql = "";
            olddb = eParameters.Request("olddb");
            newdb = eParameters.Request("newdb");
            string act = eParameters.QueryString("act");

            if (act == "add")
            {
                add();
                return;
            }
            if (act == "create")
            {
                create();
                return;
            }
            if (act == "upcreate")
            {
                upcreate();
                return;
            }
            pattern = eParameters.Request("pattern");
            if (pattern.Length == 0) pattern = "1";
            alltable = eParameters.Request("alltable");
            if (alltable.Length == 0) alltable = "1";

            if (olddb.Length == 0) olddb = eBase.DataBase.DataBase;
            StringBuilder sb = new StringBuilder();
            DataTable tb = eBase.DataBase.getDataBases();

            //eBase.PrintDataTable(tb);
            foreach (DataRow dr in tb.Rows)
            {
                sb.Append("<option value=\"" + dr["name"].ToString() + "\"" + (dr["name"].ToString().ToLower() == olddb.ToLower() ? " selected=\"true\"" : "") + ">" + dr["name"].ToString() + "</option>");
            }
            Litold.Text = sb.ToString();

            sb = new StringBuilder();

            foreach (DataRow dr in tb.Rows)
            {
                sb.Append("<option value=\"" + dr["name"].ToString() + "\"" + (dr["name"].ToString().ToLower() == newdb.ToLower() ? " selected=\"true\"" : "") + ">" + dr["name"].ToString() + "</option>");
            }
            Litnew.Text = sb.ToString();



            if (olddb.Length > 0 && newdb.Length > 0)
            {
                sb = new StringBuilder();

                sb.Append("<table width=\"600\" border=\"0\" cellspacing=\"1\" cellpadding=\"0\" bgcolor=\"#CCCCCC\" clas1s=\"eDataTable\" style=\"margin-top:10px;\">\r\n");
                sb.Append("<thead>\r\n");
                sb.Append("<tr bgcolor=\"#f2f2f2\">\r\n");
                sb.Append("<td>&nbsp;" + newdb + "</td>\r\n");
                sb.Append("<td>&nbsp;" + olddb + "</td>\r\n");
                sb.Append("</tr>\r\n");
                sb.Append("</thead>\r\n");
                sb.Append("<tbody>\r\n");
                sb.Append("<tr valign=\"top\" bgcolor=\"#ffffff\">\r\n");
                sb.Append("<td heigh1t=\"25\">\r\n");

                //DataTable tb1 = eOleDB.getTables(newdb);
                //DataTable tb2 = eOleDB.getTables(olddb);

                sql = "SELECT * FROM [" + newdb + "]..sysobjects";
                sql += " WHERE xtype = 'U' and name not in ('dtproperties','sysdiagrams')";
                if(alltable != "2") sql += " and (name like 'a_eke_sys%' or name in ('Organizationals','Dictionaries')) ";
                sql += " order by name";
                //eBase.Writeln(sql);
                DataTable tb1 = eBase.DataBase.getDataTable(sql);

                sql = "SELECT * FROM [" + olddb + "]..sysobjects";
                sql += " WHERE xtype = 'U' and name not in ('dtproperties','sysdiagrams')";
                if (alltable != "2") sql += " and (name like 'a_eke_sys%' or name in ('Organizationals','Dictionaries')) ";//a_eke_%
                sql += " order by name";
                DataTable tb2 = eBase.DataBase.getDataTable(sql);
                
                //eBase.Writeln(tb1.Rows.Count.ToString() + "::" + tb2.Rows.Count.ToString());
                StringBuilder sbr = new StringBuilder();
                foreach (DataRow dr in tb1.Rows)
                {
                    //if (dr["name"].ToString().ToLower().IndexOf("a_eke_sys") == -1) continue;
                    DataRow[] rows = tb2.Select("name='" + dr["name"].ToString() + "'");
                    //DataTable dt1 = eOleDB.getColumns(newdb, dr["name"].ToString());
                    //DataTable dt2 = eOleDB.getColumns(olddb, dr["name"].ToString());


                    sql = " SELECT a.name as code,b.name as type,a.length,a.colid as PX,d.text as def";
                    sql += " from [" + newdb + "]..syscolumns a";
                    sql += " inner join [" + newdb + "]..systypes b on a.xtype=b.xusertype ";
                    sql += " left join [" + newdb + "]..sysobjects c on a.cdefault=c.id and a.cdefault>0";
                    sql += " left join [" + newdb + "]..syscomments d on c.id=d.id";
                    sql += " where a.id=(SELECT id from [" + newdb + "]..sysobjects where name='" + dr["name"].ToString() + "' and xtype = 'U')";
                    sql += " order by a.colid";
                    DataTable dt1 = eBase.DataBase.getDataTable(sql);

                    sql = " SELECT a.name as code,b.name as type,a.length,a.colid as PX,d.text as def";
                    sql += " from [" + olddb + "]..syscolumns a";
                    sql += " inner join [" + olddb + "]..systypes b on a.xtype=b.xusertype ";
                    sql += " left join [" + olddb + "]..sysobjects c on a.cdefault=c.id and a.cdefault>0";
                    sql += " left join [" + olddb + "]..syscomments d on c.id=d.id";

                    sql += " where a.id=(SELECT id from [" + olddb + "]..sysobjects where name='" + dr["name"].ToString() + "' and xtype = 'U')";
                    sql += " order by a.colid";
                    DataTable dt2 = eBase.DataBase.getDataTable(sql);
                    //if(dr["Name"].ToString()=="a_eke_sysAccounts")  eBase.PrintDataTable(dt2);
                    //eBase.PrintDataTable(dt1);
                    //eBase.PrintDataTable(dt2);
                    bool changed = false;
                    bool has = rows.Length == 0 ? false : true;
                    if (dt1.Rows.Count == dt2.Rows.Count || 1>0)
                    {
                        foreach (DataRow _dr in dt1.Rows)
                        {
                            DataRow[] _rs = new DataTable().Select();
                            if (pattern == "2")//详细
                            {
                                _rs = dt2.Select("code='" + _dr["code"].ToString() + "' and type='" + _dr["type"].ToString() + "' and length='" + _dr["length"].ToString() + "'");
                            }
                            else
                            {
                                _rs = dt2.Select("code='" + _dr["code"].ToString() + "' and type='" + _dr["type"].ToString() + "'");
                            }
                            if (_rs.Length == 0)
                            {
                                changed = true;
                                break;
                            }
                            else
                            {
                                if (_dr["def"].ToString() != _rs[0]["def"].ToString() && pattern=="2")
                                {
                                    changed = true;
                                    break;
                                }
                            }
                        }
                    }
                    else
                    {
                        changed = true;
                    }
                    sb.Append("<div class=\"close\" onclick=\"show(this);\">" + dr["name"].ToString() + "</div>");
                    //表名
                    sbr.Append("<div class=\"close\"" + (rows.Length == 0 || changed ? " style=\"color:#ff0000;\"" : "") + " onclick=\"show(this);\">" + dr["name"].ToString());

                    //sbr.Append(has ? "" : "<a href=\"javascript:;\" onclick=\"createTable(this,'" + dr["name"].ToString() + "');\" _href=\"?act=create&table=" + dr["name"].ToString() + "\" style=\"display:inline-block;margin-left:20px;\">同步</a>");
                    if (has)
                    {
                        if (changed) sbr.Append("<a href=\"javascript:;\" onclick=\"upcreateTable(this,'" + dr["name"].ToString() + "');\" _href=\"?act=upcreate&table=" + dr["name"].ToString() + "\" style=\"display:inline-block;margin-left:20px;\">同步</a>");
                    }
                    else
                    {
                        sbr.Append("<a href=\"javascript:;\" onclick=\"createTable(this,'" + dr["name"].ToString() + "');\" _href=\"?act=create&table=" + dr["name"].ToString() + "\" style=\"display:inline-block;margin-left:20px;\">创建</a>");
                    }                    
                    sbr.Append("</div>");
                    sb.Append("<ul style=\"display:none;margin-left:40px;\">\r\n");
                    sbr.Append("<ul style=\"display:none;margin-left:40px;\">\r\n");
                    foreach (DataRow _dr in dt1.Rows)
                    {
                        bool _has = dt2.Select("code='" + _dr["code"].ToString() + "'").Length == 0 ? false : true;
                        changed = false;
                        if (dt2.Rows.Count > 0)
                        {
                            DataRow[] rs = new DataTable().Select();
                            if (pattern == "2")//详细
                            {
                                rs = dt2.Select("code='" + _dr["code"].ToString() + "' and type='" + _dr["type"].ToString() + "' and length='" + _dr["length"].ToString() + "'");
                            }
                            else
                            {
                                rs = dt2.Select("code='" + _dr["code"].ToString() + "' and type='" + _dr["type"].ToString() + "'");
                            }
                            if (rs.Length > 0)
                            {
                                if (_dr["def"].ToString() != rs[0]["def"].ToString() && pattern == "2")
                                {
                                    changed = true;
                                }
                            }
                            else
                            {
                                changed = true;
                            }
                        }

                        sb.Append("<li>" + _dr["code"].ToString() + "</li>");
                        //列名
                        sbr.Append("<li" + (changed ? " style=\"color:#ff0000;\"" : "") + ">" + _dr["code"].ToString());
                        sbr.Append(has && !_has ? "<a href=\"javascript:;\" onclick=\"addField(this,'" + dr["name"].ToString() + "','" + _dr["code"].ToString() + "');\" _href=\"?act=add&table=" + dr["name"].ToString() + "&code=" + _dr["code"].ToString() + "\" style=\"display:inline-block;margin-left:20px;\">同步</a>" : "");
                        sbr.Append("</li>");


                    }
                    sb.Append("</ul>\r\n");
                    sbr.Append("</ul>\r\n");                  

                }

                sb.Append("</td>\r\n");
                sb.Append("<td>\r\n");
                sb.Append(sbr.ToString());
                sb.Append("</td>\r\n");
                sb.Append("</tr>\r\n");
                sb.Append("</tbody>\r\n");
                sb.Append("</table>\r\n");



                LitBody.Text = sb.ToString();
            }
        }
        protected void Page_PreRender(object sender, EventArgs e)
        {
            if (Master == null) return;
            Literal lit = (Literal)Master.FindControl("LitTitle");
            if (lit != null)
            {
                lit.Text = "库对比 - " + eConfig.manageName();
            }
        }
    }
}