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

namespace eFrameWork.Customs.Base
{
    public partial class ExcelImport : System.Web.UI.Page
    {
        public string ModelID = eParameters.QueryString("ModelID");
        public string AppItem = eParameters.QueryString("AppItem");
        public string UserArea = eParameters.QueryString("area");
        public string sheet = eParameters.QueryString("sheet");
        eUser user;
        public eModel model;
        public string file = eParameters.QueryString("file");
        /// <summary>
        /// 主库或扩展库
        /// </summary>
        private eDataBase _exdatabase;
        private eDataBase ExDataBase
        {
            get
            {
                if (_exdatabase == null)
                {
                    if (model.ModelInfo["DataSourceID"].ToString().Length > 0)
                    {
                        _exdatabase = new eDataBase(model.ModelInfo);
                    }
                    else
                    {
                        _exdatabase = eOleDB.DataBase;
                    }
                }
                return _exdatabase;
            }
        }


        protected void Page_Load(object sender, EventArgs e)
        {
            user = new eUser(UserArea);
            user.Check();
            
            eModelInfo customModel = new eModelInfo(user);
            model = customModel.Model;

            //eBase.PrintDataRow(model.Power);
            if (!model.Power.Table.Columns.Contains("import") && model.Power["import"].ToString().ToLower() == "false")
            {
                eBase.Writeln("没有导入权限!");
                eBase.End();
            }

            if (file.Length == 0)
            {
                #region 保存文件
                if (Request.Form["act"] != null)
                {
                    HttpPostedFile f = imgFile.PostedFile;
                    if (f.ContentLength > 0)
                    {
                        string dirpath = Server.MapPath("~/") + "upload\\temp\\";

                        int pos = f.FileName.LastIndexOf(".");
                        string ext = f.FileName.Substring(pos, f.FileName.Length - pos).ToLower();
                        if (ext.ToLower().IndexOf("xls") == -1)
                        {
                            Response.Write("<script>alert('文件格式不正确!');</script>"); //document.location='Models.aspx';
                            Response.End();
                        }
                        string filename = eBase.GetFileName() + ext;
                        string pathname = dirpath + filename;
                        if (!Directory.Exists(dirpath)) Directory.CreateDirectory(dirpath);
                        f.SaveAs(pathname);
                        Response.Redirect(eBase.getAspxFileName() +  "?area=" + UserArea + (AppItem.Length > 0 ? "&AppItem=" + AppItem : "") + (ModelID.Length > 0 ? "&ModelID=" + ModelID : "") + "&file=" + filename, true);

                    }
                }
                #endregion

            }
            else
            {
                if (sheet.Length == 0)
                {
                    ReadFile();
                }
                else
                {
                    Import();
                }
            }           

        }
        private void Import()
        {
            string pathname = Server.MapPath("~/") + "upload\\temp\\" + file;
            if (!File.Exists(pathname)) return;
            eFileInfo fi = new eFileInfo(pathname);            
            #region 尝试打开上传文件
            OleDbConnection Oleconn;
            if (fi.Extension.ToLower() == "xlsx")
            {
                Oleconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathname + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\";");
            }
            else
            {
                Oleconn = new OleDbConnection("provider=microsoft.jet.oledb.4.0;Excel 8.0;ReadOnly=True;HDR=YES;IMEX=1;database=" + pathname + ";");
            }
            try
            {
                Oleconn.Open();
            }
            catch
            {
                System.IO.File.Delete(pathname);
                Response.Write("文件格式有误!");
                Response.End();
            }
            #endregion
            DataTable tb = new DataTable();
            //eBase.Writeln(sheet);
            OleDbDataAdapter sda = new OleDbDataAdapter("Select * From [" + sheet + "]", Oleconn);
            sda.Fill(tb);
            //eBase.Writeln(tb.Rows.Count.ToString());
            //eBase.Writeln(model.ModelInfo["code"].ToString());
            if (tb.Rows.Count > 0)
            {
                //eBase.Writeln(ExDataBase.DataBaseType.ToString());
                foreach (DataRow dr in tb.Rows)
                {
                    //eBase.PrintDataRow(dr);
                    eTable etb = new eTable(model.ModelInfo["code"].ToString(), user);
                    etb.DataBase = ExDataBase;
                    for (int i = 0; i < tb.Columns.Count; i++)
                    {
                        string code = eParameters.Form("fa" + i.ToString());
                        string code1 = eParameters.Form("fb" + i.ToString());
                        if (code.Length > 0 && code1.Length > 0)
                        {
                            
                            DataRow[] rs = model.Items.Select("code='" + code1 + "'");
                            if (rs.Length > 0)
                            {
                                string value = dr[code].ToString();
                                string options = rs[0]["options"].ToString();
                                if (options.Length > 2 && options.StartsWith("["))
                                {
                                    eJson json = new eJson(options);
                                    foreach (eJson m in json.GetCollection())
                                    {
                                        value = value.Replace(m.GetValue("text"), m.GetValue("value"));
                                    }
                                    if (rs[0]["type"].ToString().ToLower() == "bit")
                                    {
                                        value = value.ToLower().Replace("true", "1").Replace("false", "0");
                                    }
                                }
                                etb.Fields.Add(code1, value);
                                //eBase.Writeln(code1 + "::" + value);
                            }
                        }
                    }
                    etb.Add();

                }
            }
            Oleconn.Close();

            System.IO.File.Delete(pathname);
            eBase.Writeln("导入成功!");
        }
        private void ReadFile()
        {
            string pathname = Server.MapPath("~/") + "upload\\temp\\" + file;
            if (!File.Exists(pathname)) return;
            eFileInfo fi = new eFileInfo(pathname);
            #region 尝试打开上传文件
            OleDbConnection Oleconn;
            if (fi.Extension.ToLower() == "xlsx")
            {
                Oleconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathname + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\";");
            }
            else
            {
                Oleconn = new OleDbConnection("provider=microsoft.jet.oledb.4.0;Excel 8.0;ReadOnly=True;HDR=YES;IMEX=1;database=" + pathname + ";");
            }
            try
            {
                Oleconn.Open();
            }
            catch
            {
                System.IO.File.Delete(pathname);
                Response.Write("文件格式有误!");
                Response.End();
            }
            #endregion
            DataTable dt = Oleconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            //eBase.PrintDataTable(dt);
            foreach (DataRow dr in dt.Rows)
            {

                DataTable tb = new DataTable();
                OleDbDataAdapter sda = new OleDbDataAdapter("Select top 3 * From [" + dr["TABLE_NAME"].ToString() + "]", Oleconn);
                sda.Fill(tb);
                if (tb.Rows.Count > 0)
                {
                    if (tb.Rows.Count == 1 &&  tb.Columns.Count == 1 && tb.Columns[0].ColumnName.ToLower() == "f1") continue;

                    eBase.Write("<div>数据预览</div>"); 
                    eBase.PrintDataTable(tb);

                    eBase.Write("<div>对应关系</div>");


                    DataRow[] addItems;
                    addItems = model.Items.Select("showAdd=1 and hasUI=1 and (len(code)>0 or len(ProgrameFile)>0) and len(isnull(addControlType,''))>0 and controlType<>'hidden'", "AddOrder, PX, addTime");
                    //eBase.PrintDataRow(addItems);


                    StringBuilder sb = new StringBuilder();
                    sb.Append("<form id=\"form1\" name=\"form1\" method=\"post\" action=\"" + eBase.getAspxFileName() + "?area=" + UserArea + "&sheet=" + dr["TABLE_NAME"].ToString() + "&act=import" + (AppItem.Length > 0 ? "&AppItem=" + AppItem : "") + (ModelID.Length > 0 ? "&ModelID=" + ModelID : "") + "&file=" + file + "\">\r\n");
                    sb.Append("<table width=\"600\" border=\"0\" cellpadding=\"0\" cellspacing=\"1\" bgcolor=\"#DDDDDD\" class=\"eDataTable\" style=\"margin-bottom:10px;\">\r\n");
                    sb.Append("<thead>\r\n");
                    sb.Append("<tr bgcolor=\"#F9F9F9\">\r\n");
                    sb.Append("<td height=\"30\">Excel</td>\r\n");
                    sb.Append("<td>" + model.ModelInfo["mc"].ToString() + "</td>\r\n");
                    sb.Append("</tr>\r\n");
                    sb.Append("</thead>\r\n");
                    sb.Append("<tbody>\r\n");
                    for (int i = 0; i < tb.Columns.Count; i++)
                    {
                        string style = " style=\"color:#ff0000;\"";
                        for (int j = 0; j < addItems.Length; j++)
                        {
                            if (tb.Columns[i].ColumnName == addItems[j]["code"].ToString() || tb.Columns[i].ColumnName == addItems[j]["mc"].ToString())
                            {
                                style = "";
                            }
                        }
                        sb.Append("<tr bgcolor=\"#FFFFFF\">\r\n");
                        sb.Append("<td height=\"30\">" );
                        sb.Append("<select name=\"fa" + i.ToString() + "\"" + style + ">\r\n");
                        sb.Append("<option value=\"\">忽略</option>\r\n");
                        for (int j = 0; j < tb.Columns.Count; j++)
                        {
                            sb.Append("<option value=\"" + tb.Columns[j].ColumnName + "\"" + (tb.Columns[i].ColumnName == tb.Columns[j].ColumnName ? " selected=\"true\"" : "") + ">" + tb.Columns[j].ColumnName + "</option>\r\n");
                        }
                        sb.Append("</select>");
                        sb.Append("</td>\r\n");
                        sb.Append("<td>");
                        sb.Append("<select name=\"fb" + i.ToString() + "\"" + style + ">\r\n");
                        sb.Append("<option value=\"\">忽略</option>\r\n");
                        for (int j = 0; j < addItems.Length; j++)
                        {
                            sb.Append("<option value=\"" + addItems[j]["code"].ToString() + "\"" + (tb.Columns[i].ColumnName == addItems[j]["code"].ToString() || tb.Columns[i].ColumnName == addItems[j]["mc"].ToString() ? " selected=\"true\"" : "") + ">" + addItems[j]["mc"].ToString() + "</option>\r\n");
                        }
                        sb.Append("</select>");
                        sb.Append("</td>\r\n");
                        sb.Append("</tr>\r\n");
                    }
                    sb.Append("</tbody>\r\n");
                    sb.Append("</table>\r\n");
                    sb.Append("<input type=\"submit\" name=\"Submit\" value=\"确定导入\" />\r\n");
                    sb.Append("</form>\r\n");
                    litBody.Text = sb.ToString();


                    continue;
                }
            }
            Oleconn.Close();

            
            
        }
    }
}