using Kingdee.BOS;
using Kingdee.BOS.App;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.Bill;
using Kingdee.BOS.Core.CommonFilter;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.Core.DynamicForm.PlugIn;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.DynamicForm.PlugIn.ControlModel;
using Kingdee.BOS.Core.List.PlugIn;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Core.Metadata.FieldElement;
using Kingdee.BOS.Core.Metadata.FormElement;
using Kingdee.BOS.Core.Validation;
using Kingdee.BOS.JSON;
using Kingdee.BOS.Log;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.ServiceHelper.Excel;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;

namespace LF.K3.SCM.BusinessPlugin
{
    [Description("上传excel")]
    [HotUpdate]
    public class ImportDatePlugin : AbstractDynamicFormPlugIn
    {
        DataTable dtexcel;
        private string _filePath;
        int jg;
        DataTable sbtab;//失败数据
        public override void AfterBindData(EventArgs e)
        {
            this.EnableButton("FImportDate", false);
            base.AfterBindData(e);
        }
        public override void BeforeDoOperation(BeforeDoOperationEventArgs e)
        {

            if (e.Operation.FormOperation.Id == "FImportDate")
            {
                //数据的操作
                Impoort();
                if (this.IsNotNull())
                {
                    if (jg == 1)//成功
                    {
                        string fileName = string.Format("{0}_{1}.xls", this.View.BillBusinessInfo.GetForm().Name, DateTime.Now.ToString("yyyyMMddHHmmssff"));
                        ExcelOperation excelHelper = new ExcelOperation(this.View);
                        excelHelper.BeginExport();
                        string filePath = PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH, fileName);
                        string outServicePath = PathUtils.GetServerPath(KeyConst.TEMPFILEPATH, fileName);
                        List<DynamicObject> dObjList = new List<DynamicObject>();

                        var headerRow = sbtab.NewRow();
                        sbtab.Rows.InsertAt(headerRow, 0);
                        foreach (DataColumn column in sbtab.Columns)
                        {
                            headerRow[column] = column.ColumnName;
                        }

                        excelHelper.AddSheet("物料导入" + Kingdee.BOS.Resource.ResManager.LoadKDString("(错误提示)", "0022877030028396", Kingdee.BOS.Resource.SubSystemType.BOS));
                        excelHelper.ExportToFile(sbtab);
                        excelHelper.EndExport(filePath, SaveFileType.XLS);


                        if (sbtab.Rows.Count == 1)
                        {
                            this.View.ShowMessage(Kingdee.BOS.Resource.ResManager.LoadKDString("引入成功！", "0022877030028379", Kingdee.BOS.Resource.SubSystemType.BOS));

                        }
                        else
                        if (sbtab.Rows.Count > 1)
                        {
                            this.View.ShowWarnningMessage(Kingdee.BOS.Resource.ResManager.LoadKDString("部分数据引入成功，存在引入失败的数据！", "0022877030028394", Kingdee.BOS.Resource.SubSystemType.BOS), action: (result) =>
                            {
                                this.DownLoadFile(outServicePath);
                            });
                        }
                        return;
                    }
                    else//失败
                    {
                        this.View.ShowMessage("数据异常");
                    }




                }
            }
        }
        //下载文件弹窗
        protected new void DownLoadFile(string url)
        {
            DynamicFormShowParameter param = new DynamicFormShowParameter();
            param.FormId = "BOS_FileDownLoad";
            param.OpenStyle.ShowType = ShowType.Modal;
            param.CustomParams.Add("IsExportData", "true");
            param.CustomParams.Add("url", url);
            this.View.ShowForm(param);
        }


        //自定义方法,判断是否上传Excel表格
        private bool IsNotNull()
        {
            ExcelOperation excelOperation = new ExcelOperation();
            DataSet dataSet = null;
            dataSet = excelOperation.ReadFromFile(this._filePath, 0, 0);
            dtexcel = dataSet.Tables["Sheet1"];
            if (dtexcel == null)
            {
                this.View.ShowWarnningMessage("没有找到模板请重新选择");
                return false;
            }
            return true;
        }

        //选择文件触发
        public override void CustomEvents(CustomEventsArgs e)
        {
            if (e.Key.EqualsIgnoreCase("FFileUpdate"))
            {
                this.View.GetControl("FFileUpdate").SetCustomPropertyValue("NeedCallback", true);
                this.View.GetControl("FFileUpdate").SetCustomPropertyValue("IsRequesting", false);
                if (e.EventName.EqualsIgnoreCase("FileChanged"))
                {
                    var postData = KDObjectConverter.DeserializeObject<JSONObject>(e.EventArgs);

                    if (postData == null)
                    {
                        this.View.ShowWarnningMessage("未选择文件！");
                    }
                    else
                    {
                        var uploadInfo = new JSONArray(postData["NewValue"].ToString());
                        if (uploadInfo.Count == 1)
                        {
                            // 取上传的文件名
                            var fileInfo = uploadInfo[0] as Dictionary<string, object>;
                            if (fileInfo != null)
                            {
                                var fileName = fileInfo["ServerFileName"].ToString();
                                if (this.CheckFile(fileName))
                                {
                                    //上传完成
                                    this._filePath = this.GetFilePath(fileName);
                                    this.EnableButton("FImportDate", true);
                                }
                                else
                                {
                                    //上传文件格式不正确
                                    this.EnableButton("FImportDate", false);
                                }
                            }
                        }
                        else
                         //只允许上传一个文件
                         if (uploadInfo.Count > 1)
                        {
                            this.EnableButton("FImportDate", false);
                            this.View.ShowWarnningMessage("只允许上传一个文件！");
                        }
                    }
                }

            }
        }
        //自定义方法,引入的数据
        private void Impoort()
        {
            ExcelOperation excelOperation = new ExcelOperation();
            DataSet dataSet = null;
            dataSet = excelOperation.ReadFromFile(this._filePath, 0, 0);
            DataTable dt = dataSet.Tables[0];
            dt.Rows.RemoveAt(0);
            string strTest = GetCreateTmpTableNameSql(dt.Columns);//获取Excel列
            string taball = DBServiceHelper.CreateTemporaryTableName(this.Context);
            string tabcg = DBServiceHelper.CreateTemporaryTableName(this.Context);
            string tabsb = DBServiceHelper.CreateTemporaryTableName(this.Context);
            string tabls = DBServiceHelper.CreateTemporaryTableName(this.Context);

            //截取sql表列
            strTest = strTest.Substring(1, strTest.Length - 1);
            strTest = strTest.Remove(strTest.Length - 1, 1);
            //判断是否存在此表，如果存在则删除
            string sql2 = $@"/*dialect*/ IF Object_id('{taball}') IS NOT NULL DROP TABLE {taball}";
            var a = DBUtils.Execute(this.Context, sql2);
            //创建表
            string sql1 = $@"/*dialect*/ create table {taball}(" + strTest;
            DBUtils.Execute(this.Context, sql1);
            //移除datatable中的第一行（第一行数据为Excel中的列字段信息）
            dt.Rows.RemoveAt(0);
            //表名匹配
            dt.TableName = taball;
            //Excel数据批量导入表中
            DBServiceHelper.BulkInserts(this.Context, string.Empty, string.Empty, dt);
            //定义输入输出参数
            string xznum = "";
            SqlParam sqlParam1 = new SqlParam("@TableName", KDDbType.String, taball);
            SqlParam sqlParam2 = new SqlParam("@TableNamecg", KDDbType.String, tabcg);//失败
            SqlParam sqlParam3 = new SqlParam("@TableNamesb", KDDbType.String, tabsb);//成功
            SqlParam sqlParam4 = new SqlParam("@TableNamels", KDDbType.String, tabls);//临时
            SqlParam sqlParam5 = new SqlParam("@xznum", KDDbType.Int32, xznum);
            sqlParam1.Direction = ParameterDirection.Input;
            sqlParam2.Direction = ParameterDirection.Input;
            sqlParam3.Direction = ParameterDirection.Input;
            sqlParam4.Direction = ParameterDirection.Input;
            sqlParam5.Direction = ParameterDirection.Output;
            List<SqlParam> sqlParams = new List<SqlParam>();
            sqlParams.Add(sqlParam1);
            sqlParams.Add(sqlParam2);
            sqlParams.Add(sqlParam3);
            sqlParams.Add(sqlParam4);
            sqlParams.Add(sqlParam5);
            //执行存储过程
            var result = DBUtils.ExecuteStoreProcedure(this.Context, "ImportData", sqlParams);
            if (result.Count > 0)
            {
                //Excelsum = dt.Rows.Count;
                jg = Convert.ToInt32(result[0].Value);//最后结果
                                                      // CFnum = Convert.ToInt32(result[1].Value);//重复物料
            }
            else
            {
                jg = -1;
            }
            //查出失败数据
            string sqldata = $@"/*dialect*/ select [失败原因],[创建人],[货品代号],[名称],[规格],[主单位],[大类],[中类],[预设仓库],[品牌],[12NC],[SKU],[CK编码], [接头数量],[接头型号]
                ,[线缆长度(米)],[材质],[长],[宽],[高],[拼板数],[开模图号],[每模穴数],[单模日产],[使用范围],[调价物料类型],[发料是否取整],[物料分组] from {tabsb}";
            sbtab = DBUtils.ExecuteDataSet(this.Context, sqldata).Tables[0];
            string sqlcg = $@"/*dialect*/select t2.FMATERIALID from {tabcg} t1 inner join  T_BD_MATERIAL t2 on t1.[货品代号]=t2.FNUMBER";
            Logger.Info("sql", sqlcg);
            List<object> ids = new List<object>();
            string id = "";
            using (IDataReader reader = DBUtils.ExecuteReader(this.Context, sqlcg))
            {
                while (reader.Read())
                {
                    var materialId = reader["FMATERIALID"];
                    ids.Add(materialId);
                    //id = materialId + ",";
                }
            }
            foreach (var item in ids)
            {
                id = item.ToString() + ",";
            }
            Logger.Info("导入成功ID", id);
            Logger.Info("导入成功ID长度", ids.Count().ToString());
            if (ids.Count > 0)
            {
                //List<DynamicObject> materails = new List<DynamicObject>();
                for (int i = 0; i < ids.Count; i++)
                {
                    Logger.Info("ID", ids[i].ToString());
                    var createView = CreateBillView("BD_MATERIAL", Convert.ToInt64(ids[i]));
                    var savebool = createView.InvokeFormOperation("Save");

                    Logger.Info(ids[i].ToString(), savebool.ToString());

                    createView.Close();
                    //materails.Add(createView.Model.DataObject);
                }

                //BusinessDataServiceHelper.Save(this.Context, this.View.ParentFormView.BillBusinessInfo, materails.ToArray());
            }
            //删除表
            string[] strarr = new string[4];
            strarr[0] = taball;
            strarr[1] = tabcg;
            strarr[2] = tabsb;
            strarr[3] = tabls;
            DBServiceHelper.DeleteTemporaryTableName(this.Context, strarr);
        }

        private IBillView CreateBillView(string formId, long pkId)
        {
            //读取物料的元数据
            FormMetadata meta = MetaDataServiceHelper.Load(this.Context, formId) as FormMetadata;
            Form form = meta.BusinessInfo.GetForm();
            //创建用于引入数据的单据view
            Type type = Type.GetType("Kingdee.BOS.Web.Bill.BillView,Kingdee.BOS.Web");
            var billView = (IDynamicFormViewService)Activator.CreateInstance(type);
            //开始初始化billView：
            //创建视图加载参数对象，指定各种参数，如FormId, 视图(LayoutId)等
            BillOpenParameter openParam = CreateOpenParameter(meta, pkId);
            //动态领域模型服务提供类，通过此类，构建MVC实例
            var provider = form.GetFormServiceProvider();
            billView.Initialize(openParam, provider);
            billView.LoadData();
            return billView as IBillView;
        }

        private BillOpenParameter CreateOpenParameter(FormMetadata meta, long pkId)
        {
            Form form = meta.BusinessInfo.GetForm();
            //指定FormId, LayoutId
            BillOpenParameter openParam = new BillOpenParameter(form.Id, meta.GetLayoutInfo().Id);
            //数据库上下文
            openParam.Context = this.Context;
            //本单据模型使用的MVC框架
            openParam.ServiceName = form.FormServiceName;
            //随机产生一个不重复的PageId，作为视图的标识
            openParam.PageId = Guid.NewGuid().ToString();
            //元数据
            openParam.FormMetaData = meta;
            //界面状态：新增 (修改、查看)
            if (Convert.ToInt64(pkId) == 0)
            {
                openParam.Status = OperationStatus.ADDNEW;
            }
            else
            {
                openParam.Status = OperationStatus.EDIT;
                //单据主键：本案例演示新建物料，不需要设置主键
                openParam.PkValue = pkId;
            }
            //界面创建目的：普通无特殊目的 （为工作流、为下推、为复制等）
            openParam.CreateFrom = CreateFrom.Default;
            var plugs = form.CreateFormPlugIns();
            openParam.SetCustomParameter(FormConst.PlugIns, plugs);
            PreOpenFormEventArgs args = new PreOpenFormEventArgs(this.Context, openParam);
            foreach (var plug in plugs)
            { //触发插件PreOpenForm事件，供插件确认是否允许打开界面
                plug.PreOpenForm(args);
            }
            return openParam;
        }
        //自定义方法,拼接临时表字段
        private string GetCreateTmpTableNameSql(DataColumnCollection fieldColumn)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine("(");
            List<string> listStr = new List<string>();
            foreach (var field in fieldColumn)
            {
                listStr.Add('"' + Convert.ToString(field).Trim().ToString() + '"' + " nvarchar(1000)");
            }
            stringBuilder.AppendLine(String.Join(",", listStr.Distinct()));
            stringBuilder.AppendLine(")");
            return stringBuilder.ToString();
        }
        //自定义方法,判断是否是上传的是Excel文件
        private bool CheckFile(string fileName)
        {
            bool flag = false;
            string[] array = fileName.Split(new char[] { '.' });
            //通过后缀名,判断是否是Excel
            if (array.Length == 2 && (array[1].EqualsIgnoreCase("xls") || array[1].EqualsIgnoreCase("xlsx")))
            {
                flag = true;
            }
            if (!flag)
            {
                this.View.ShowWarnningMessage("请选择正确的文件格式进行引入");
            }
            return flag;
        }

        //自定义方法,没有上传完成Excel,那么上传按钮是灰色的
        private void EnableButton(string key, bool bEnable)
        {
            this.View.GetControl<Button>(key).Enabled = bEnable;
        }
        //获取上传路径
        private string GetFilePath(string serverFileName)
        {
            string directory = "FileUpLoadServices\\UploadFiles";
            return PathUtils.GetPhysicalPath(directory, serverFileName);
        }




    }
}
