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 System.IO;
using EKETEAM.Data;
using EKETEAM.FrameWork;
using System.Xml;


namespace eFrameWork.Manage
{
    public partial class CompareOnline : System.Web.UI.Page
    {
        public string pattern = "";
        string serverUrl = "http://frame.eketeam.com/Services/databaseSchema.aspx";
        //string filepath = "columns_cache.xml";
        string filepath = "../upload/temp/columns_cache.xml";
        protected void Page_Load(object sender, EventArgs e)
        {
            pattern = eParameters.Request("pattern");
            if (pattern.Length == 0) pattern = "1";
            string act = eParameters.QueryString("act");
            switch (act)
            {
                case "": 
                    list();
                    break;
                case "add":
                    add();
                    break;
                case "create":
                    create();
                    break;
                case "upcreate":
                    upcreate();
                    break;
            }            
        }
        private void checkFile()
        {
            string path = Server.MapPath(filepath);
            if (!Directory.Exists(new eFileInfo(path).Path)) Directory.CreateDirectory(new eFileInfo(path).Path);
            if (System.IO.File.Exists(path))
            {
                FileInfo fi = new FileInfo(path);
                TimeSpan timeSpan = DateTime.Now - fi.CreationTime;
                int Minutes = Convert.ToInt32(timeSpan.TotalMinutes);
                if (Minutes < 31) return;
            }
            string xmlbody = eBase.getRequest(serverUrl);
            if (xmlbody == null)
            {
                eBase.Writeln("请求服务器失败,请确保服务器能访问外网。");
                eBase.End();
            }
            else
            {
                if (xmlbody.StartsWith("<") && xmlbody.EndsWith(">"))
                {
                    eBase.WriteFile(path, xmlbody);
                }
            }
        }
        private void add()
        {
            string table = eParameters.QueryString("table");
            string code = eParameters.QueryString("code");
            string path = Server.MapPath(filepath);
            if (!Directory.Exists(new eFileInfo(path).Path)) Directory.CreateDirectory(new eFileInfo(path).Path);
            if (System.IO.File.Exists(path))
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(path);
                XmlNode node = doc.SelectSingleNode("/root/model");
                if (node != null)
                {
                    XmlNode onode = node.SelectSingleNode("table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + table.ToLower() + "']");//不区分大小写
                    if (onode != null)
                    {
                      
                        DataTable dt = onode.ChildNodes.toDataTable();
                        DataRow[] rows = dt.Select("COLUMN_NAME='" + code + "'");
                        if (rows.Length > 0)
                        {
                            //eBase.Writeln(table + "::" + code);
                            //eBase.PrintDataRow(rows);
                            eBase.DataBase.addSchemaColumn(table, rows[0]);
                        }
                    }
                }
            }
            string ajax = eParameters.QueryString("ajax");
            if (ajax == "true")
            {
                Response.End();
               
            }
            else
            {
                Response.Redirect(eBase.getAspxFileName(), true);
            }
        }
        private void upcreate()
        {
            string table = eParameters.QueryString("table");
            string path = Server.MapPath(filepath);
            if (!Directory.Exists(new eFileInfo(path).Path)) Directory.CreateDirectory(new eFileInfo(path).Path);
            DataTable dt= new DataTable();
            if (System.IO.File.Exists(path))
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(path);
                XmlNode node = doc.SelectSingleNode("/root/model");
                if (node != null)
                {
                    XmlNode onode = node.SelectSingleNode("table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + table.ToLower() + "']");//不区分大小写
                    if (onode != null)
                    {
                        dt = onode.ChildNodes.toDataTable();
                    }
                }
            }
            if (dt.Rows.Count > 0)
            {
                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();
                }
                string temp = eBase.DataBase.getValue("select count(1) from " + eBase.DataBase.StartNameSplitChar + table + eBase.DataBase.EndNameSplitChar);
                int count = Convert.ToInt32(temp);
                DataTable dt2 = eBase.DataBase.getSchemaColumns(table , false);//本库结构


                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) //重命名成功
                {
                   

                    #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.PrintDataTable(dt);
                   // eBase.End();
                    //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 create()
        {
            string table = eParameters.QueryString("table");
            string path = Server.MapPath(filepath);
            if (!Directory.Exists(new eFileInfo(path).Path)) Directory.CreateDirectory(new eFileInfo(path).Path);
            if (System.IO.File.Exists(path))
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(path);
                XmlNode node = doc.SelectSingleNode("/root/model");
                if (node != null)
                {
                    XmlNode onode = node.SelectSingleNode("table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + table.ToLower() + "']");//不区分大小写
                    if (onode != null)
                    {
                        DataTable dt = onode.ChildNodes.toDataTable();
                        //eBase.PrintDataTable(dt);
                        eBase.DataBase.SchemaCreate(dt);
                    }
                }
            }
            string ajax = eParameters.QueryString("ajax");
            if (ajax == "true")
            {
                Response.End();
            }
            else
            {
                Response.Redirect(eBase.getAspxFileName(), true);
            } 
        }
        private void list()
        {
            checkFile();
            string path = Server.MapPath(filepath);
            if (!Directory.Exists(new eFileInfo(path).Path)) Directory.CreateDirectory(new eFileInfo(path).Path);
            if (System.IO.File.Exists(path))
            {
                StringBuilder sb = new StringBuilder();
                StringBuilder sbl = new StringBuilder();
                StringBuilder sbr = new StringBuilder();

                DataTable tb = eBase.DataBase.getSchemaTables();
                XmlDocument doc = new XmlDocument();
                doc.Load(path);
                XmlNode node = doc.SelectSingleNode("/root/model");
                foreach (XmlNode _node in node.ChildNodes)
                {
                    string name = _node.Attributes["name"].Value;
                    DataTable dt1 = _node.ChildNodes.toDataTable();//新结构
                    sbl.Append("<div class=\"close\" onclick=\"show(this);\">" + name + "</div>");
                    sbl.Append("<ul style=\"display:none;margin-left:40px;\">\r\n");
                    foreach (DataRow dr in dt1.Rows)
                    {
                        sbl.Append("<li>" + dr["COLUMN_NAME"].ToString() + "</li>");
                    }
                    sbl.Append("</ul>\r\n");

                    DataRow[] rows = tb.Select("value='" + name + "'");
                    bool has = rows.Length == 0 ? false : true;
                    DataTable dt2 = has ? eBase.DataBase.getSchemaColumns(name) : dt1.Clone();//当前结构

                    if (name == "a_eke_sysWebSites")
                    {
                        //litBody2.Text = eBase.getTableHTML(dt1);
                        //litBody3.Text = eBase.getTableHTML(dt2);
                    }
                    bool changed = false;
                    if (dt1.Rows.Count == dt2.Rows.Count || 1>0)
                    {
                        foreach (DataRow _dr in dt1.Rows)
                        {
                            string dataType = "'" + _dr["DATA_TYPE"].ToString() + "'";
                            if (eBase.DataBase.DataBaseType == eDataBaseType.MySQL)
                            {
                                if (dataType.Contains("uniqueidentifier")) dataType = "'varchar'";
                                if (dataType.Contains("nvarchar")) dataType = "'text','varchar'";
                                if (dataType.Contains("datetime")) dataType = "'timestamp','datetime'";
                                if (dataType.Contains("text")) dataType = "'text','varchar'";
                                if (dataType.Contains("varchar") && _dr["CHARACTER_MAXIMUM_LENGTH"].ToString() == "-1") dataType = "'text','ntext','varchar','nvarchar'";
                            }
                            if (eBase.DataBase.DataBaseType == eDataBaseType.SQLite)
                            {
                                if (dataType.Contains("uniqueidentifier")) dataType = "'varchar','nvarchar'";
                                if (dataType.Contains("int")) dataType = "'int','integer'";
                                if (dataType.Contains("varchar")) dataType = "'text','ntext','varchar','nvarchar'";
                            }
                            DataRow[] _rs = new DataTable().Select();
                            if (pattern == "2")//详细
                            {
                                string filter = "COLUMN_NAME='" + _dr["COLUMN_NAME"].ToString() + "' and DATA_TYPE in (" + dataType + ")";
                                if (_dr["DATA_TYPE"].ToString().ToLower().IndexOf("char") > -1 && _dr["CHARACTER_MAXIMUM_LENGTH"].ToString() != "-1") filter += " and CHARACTER_MAXIMUM_LENGTH='" + _dr["CHARACTER_MAXIMUM_LENGTH"].ToString() + "'";
                                _rs = dt2.Select(filter);
                            }
                            else
                            {
                                _rs = dt2.Select("COLUMN_NAME='" + _dr["COLUMN_NAME"].ToString() + "' and DATA_TYPE in (" + dataType + ")");
                            }
                            if (_rs.Length == 0)
                            {
                              
                                changed = true;
                                break;
                            }
                            else
                            {
                                string default1 = _dr["COLUMN_DEFAULT"].ToString().ToLower().Replace("current_timestamp", "getdate()").Replace("datetime()", "getdate()").Replace("datetime ()", "getdate()").Replace("now()", "getdate()").Replace("newid()", "").Replace("(", "").Replace(")", "").Replace("b'", "'").Replace("'", "");
                                string default2 = _rs[0]["COLUMN_DEFAULT"].ToString().ToLower().Replace("current_timestamp", "getdate()").Replace("datetime()", "getdate()").Replace("datetime ()", "getdate()").Replace("now()", "getdate()").Replace("newid()", "").Replace("(", "").Replace(")", "").Replace("b'", "'").Replace("'", "");
                                if (default1 != default2 && pattern == "2")
                                {
                                 
                                    changed = true;
                                    break;
                                }
                            }
                        }
                    }
                    else
                    {
                        changed = true;
                    }

                    //sbr.Append("<div class=\"close\"" + (rows.Length == 0 || changed ? " style=\"color:#ff0000;\"" : "") + " onclick=\"show(this);\">" + name + (has ? "" : "<a href=\"javascript:;\" onclick=\"createTable(this,'" + name + "');\" _href=\"?act=create&table=" + name + "\" style=\"display:inline-block;margin-left:20px;\">创建</a>") + "</div>");
                    sbr.Append("<div class=\"close\"" + (rows.Length == 0 || changed ? " style=\"color:#ff0000;\"" : "") + " onclick=\"show(this);\">" + name);
                    if (has)
                    {
                        if (changed) sbr.Append("<a href=\"javascript:;\" onclick=\"upcreateTable(this,'" + name + "');\" _href=\"?act=upcreate&table=" + name + "\" style=\"display:inline-block;margin-left:20px;\">同步</a>");
                    }
                    else
                    {
                        sbr.Append("<a href=\"javascript:;\" onclick=\"createTable(this,'" + name + "');\" _href=\"?act=create&table=" + name + "\" style=\"display:inline-block;margin-left:20px;\">创建</a>");
                    }
                    sbr.Append("</div>");
                    sbr.Append("<ul style=\"display:none;margin-left:40px;\">\r\n");
                    foreach (DataRow _dr in dt1.Rows)
                    {
                        changed = false;
                        bool _has = dt2.Select("COLUMN_NAME='" + _dr["COLUMN_NAME"].ToString() + "'").Length == 0 ? false : true;
                        DataRow[] _rs = new DataTable().Select();
                        string dataType = "'" +  _dr["DATA_TYPE"].ToString() + "'";
                        if (eBase.DataBase.DataBaseType == eDataBaseType.MySQL)
                        {
                            if (dataType.Contains("uniqueidentifier")) dataType = "'varchar'";
                            if (dataType.Contains("nvarchar")) dataType = "'text','varchar'";
                            if (dataType.Contains("datetime")) dataType = "'timestamp','datetime'";
                            if (dataType.Contains("text")) dataType = "'text','varchar'";
                            if (dataType.Contains("varchar") && _dr["CHARACTER_MAXIMUM_LENGTH"].ToString() == "-1") dataType = "'text','ntext','varchar','nvarchar'";
                            
                        }
                        if (eBase.DataBase.DataBaseType == eDataBaseType.SQLite)
                        {
                            if (dataType.Contains("uniqueidentifier")) dataType = "'varchar','nvarchar'";
                            if (dataType.Contains("int")) dataType = "'int','integer'";
                            if (dataType.Contains("varchar")) dataType = "'text','ntext','varchar','nvarchar'";
                        }
                        if (pattern == "2")//详细
                        {
                            string filter = "COLUMN_NAME='" + _dr["COLUMN_NAME"].ToString() + "' and DATA_TYPE in (" + dataType + ")";
                            if (_dr["DATA_TYPE"].ToString().ToLower().IndexOf("char") > -1  && _dr["CHARACTER_MAXIMUM_LENGTH"].ToString() != "-1") filter += " and CHARACTER_MAXIMUM_LENGTH='" + _dr["CHARACTER_MAXIMUM_LENGTH"].ToString() + "'";
                            _rs = dt2.Select(filter);
                        }
                        else
                        {
                            _rs = dt2.Select("COLUMN_NAME='" + _dr["COLUMN_NAME"].ToString() + "' and DATA_TYPE in (" + dataType + ")");
                        }
                      
                        if (_rs.Length == 0)
                        {
                            changed = true;
                        }
                        else
                        {
                            string default1 = _dr["COLUMN_DEFAULT"].ToString().ToLower().Replace("current_timestamp", "getdate()").Replace("datetime()", "getdate()").Replace("datetime ()", "getdate()").Replace("now()", "getdate()").Replace("newid()", "").Replace("(", "").Replace(")", "").Replace("b'", "'").Replace("'", "");
                            string default2 = _rs[0]["COLUMN_DEFAULT"].ToString().ToLower().Replace("current_timestamp", "getdate()").Replace("datetime()", "getdate()").Replace("datetime ()", "getdate()").Replace("now()", "getdate()").Replace("newid()", "").Replace("(", "").Replace(")", "").Replace("b'", "'").Replace("'", "");

                            //if ("a_eke_sysWebSites".ToLower() == name.ToLower() && _dr["COLUMN_NAME"].ToString().ToLower() == "programepath") eBase.WriteDiv(default1 + "::" + default2 + "::" + _rs[0]["COLUMN_DEFAULT"].ToString());
                            //if ("a_eke_sysWebSites".ToLower() == name.ToLower() && default1.Length > 0) eBase.WriteDiv(default1 + "::" + default2 + "::" + "::" + _dr["COLUMN_NAME"].ToString());


                            if (default1 != default2 && pattern == "2")
                            {
                                changed = true;
                            }
                        }

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

               
                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>最新结构</td>\r\n");
                sb.Append("<td>当前结构</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");
                sb.Append(sbl.ToString());
                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();
            }
        }
    }
}