using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using FarPoint.Web.Spread;
using FarPoint.Web.Spread.Model;
using FarPoint.CalcEngine;
using System.Data.OleDb;

namespace ControlExplorer
{
    public partial class Template_New : System.Web.UI.Page
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                fpDataFields.UseClipboard = false;
                rdoTemplate.Checked = true;
                pnlTemplate.Width = new Unit("96%");
            }
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            Boolean fileOK = false;

            if (fileTemplate.HasFile)
            {
                String fileExtension = System.IO.Path.GetExtension(fileTemplate.FileName).ToLower();
                String[] allowedExtensions = { ".xls", ".xlsx" };

                for (int i = 0; i < allowedExtensions.Length; i++)
                {
                    if (fileExtension == allowedExtensions[i])
                    {
                        fileOK = true;
                    }
                }
            }

            if (fileOK)
            {
                try
                {
                    string tid = Guid.NewGuid().ToString();

                    FpSpread spread = null;
                    spread = UpdataTemplate(tid);
                    UpdateDataFields(spread, tid);

                    Response.Redirect(string.Format("~/Template_Edit.aspx?tid={0}", tid));
                }
                catch (Exception ex)
                {
                    Response.Write("<script>alert('数据源配置错误');</script>");
                }
            }
        }

        // 保存上传的模板至数据库
        private FpSpread UpdataTemplate(string tid)
        {
            // 插入模板
            string commandstring = "SELECT * FROM 模板列表";
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionstring))
            {
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(commandstring, connection);
                System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                DataRow dr = dt.NewRow();
                DateTime now = DateTime.Now;

                // 字段赋值
                dr["模板编号"] = tid;
                dr["所属部门"] = lstDepartment.SelectedItem.Value;
                dr["模板名称"] = txtName.Text;
                dr["模板类型"] = lstCategory.SelectedItem.Value;
                dr["说明"] = txtDescription.Text;
                dr["创建时间"] = now;
                dr["修改时间"] = now;
                dr["数据源"] = string.Format("T{0}{1}{2}", now.ToString("yyyMMddHHmmss"), now.Millisecond, new Random().Next(10, 99));

                // 保存Spread对象
                System.IO.Stream stream = fileTemplate.PostedFile.InputStream;
                FpSpread tmpspread = new FpSpread();
                tmpspread.OpenExcel(stream);

                tmpspread.Sheets[0].RowCount += 1;
                tmpspread.Sheets[0].ColumnCount += 1;

                System.IO.MemoryStream streamspread = new System.IO.MemoryStream();
                tmpspread.Save(streamspread, false);
                byte[] templatebytes = new byte[streamspread.Length];
                templatebytes = streamspread.ToArray();
                stream.Close();
                streamspread.Close();

                dr["模板"] = templatebytes;
                dt.Rows.Add(dr);
                adapter.Update(dt);

                // 返回 Spread 对象
                MemoryStream ms = new MemoryStream(templatebytes);
                FpSpread spread = new FpSpread();
                spread.Open(ms);

                return spread;
            }
        }

        // 更新模板对应的数据源字段
        private void UpdateDataFields(FarPoint.Web.Spread.FpSpread spread, string tid)
        {
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

            if (rdoSelect.Checked == true)
            {
                string tablename = lstDataSources.SelectedItem.Value;
                string commandstring = @"update 模板列表 set 数据源 = '" + tablename + "' WHERE 模板编号 = '" + tid + "'";
                using (OleDbConnection connection = new OleDbConnection(connectionstring))
                {
                    OleDbCommand cmd = new OleDbCommand(commandstring, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
            }
            else
            {
                string tablename = DataTools.GetTemplateDataSource(tid);
                string commandstring = @"create table " + tablename + " ([记录编号] varchar(255) NOT NULL UNIQUE {0} , primary key(记录编号))";

                System.Text.StringBuilder sb = new System.Text.StringBuilder();

                if (rdoNew.Checked == true)
                {
                    fpDataFields.SaveChanges();

                    for (int i = 0; i < fpDataFields.Sheets[0].NonEmptyRowCount; i++)
                    {
                        if (!string.IsNullOrWhiteSpace(fpDataFields.Sheets[0].Cells[i, 0].Text))
                        {
                            sb.AppendFormat(",{0} varchar(255) NULL", fpDataFields.Sheets[0].Cells[i, 0].Text);
                        }
                    }
                }
                else if (rdoTemplate.Checked == true)
                {
                    var customnames = (spread.ActiveSheetView.DataModel as DefaultSheetDataModel).GetCustomNameEnumerator();
                    while (customnames.MoveNext())
                    {
                        ExternalCellExpression expr = spread.Sheets[0].GetCustomName(customnames.Current.ToString()) as ExternalCellExpression;
                        sb.AppendFormat(",{0} varchar(255) NULL", customnames.Current.ToString());
                    }
                }

                // 新建数据或者模板内置数据需要创建数据表
                commandstring = string.Format(commandstring, sb.ToString());
                using (OleDbConnection connection = new OleDbConnection(connectionstring))
                {
                    OleDbCommand cmd = new OleDbCommand(commandstring, connection);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
            }
        }

        protected void rdoTemplate_CheckedChanged(object sender, EventArgs e)
        {
            lTemplate.Visible = true;
            lNew.Visible = false;
            lstDataSources.Visible = false;
            lstDataSources.Enabled = false;
            pnlDataFields.Visible = false;
            pnlTemplate.Width = new Unit("96%");
        }

        protected void rdoSelect_CheckedChanged(object sender, EventArgs e)
        {
            lTemplate.Visible = false;
            lNew.Visible = false;
            lstDataSources.Visible = true;
            lstDataSources.Enabled = true;
            pnlDataFields.Visible = false;
            pnlTemplate.Width = new Unit("96%");
        }

        protected void rdoNew_CheckedChanged(object sender, EventArgs e)
        {
            lTemplate.Visible = false;
            lNew.Visible = true;
            lstDataSources.Visible = false;
            lstDataSources.Enabled = false;
            pnlDataFields.Visible = true;
            pnlTemplate.Width = new Unit("650px");
        }

    }
}