using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.Bill.PlugIn;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.List.PlugIn;
using Kingdee.BOS.Core.Report.PlugIn;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.WebApi.Client;
using Newtonsoft.Json.Linq;
using Kingdee.BOS.Core.Bill;
using Kingdee.BOS.Log;

namespace LQKJ.K3.NDR.NewReportPlugIn
{
    [HotUpdate, Description("部门具体项目保存点击按钮")]
    public class DepartmentSave : AbstractSysReportPlugIn
    {
        public string url = Kingdee.BOS.KDConfiguration.Current.CommonConfiguration.AppSettings.Settings["Url"].Value;
        public string dbid = Kingdee.BOS.KDConfiguration.Current.CommonConfiguration.AppSettings.Settings["Dbid"].Value;
        public string pwd = Kingdee.BOS.KDConfiguration.Current.CommonConfiguration.AppSettings.Settings["passWord"].Value;
        public string login = Kingdee.BOS.KDConfiguration.Current.CommonConfiguration.AppSettings.Settings["login"].Value;
        public override void BarItemClick(BarItemClickEventArgs e)
        {
            if(e.BarItemKey== "LQKJ_tbButton")//保存按钮
            {
                string sql = DepartmentProjectReportPlugIn.sqld;
                DataTable dt = DBUtils.ExecuteDataSet(this.Context, sql).Tables[0];//每一条数据生成一条凭证
                if (dt.Rows.Count < 1)
                {
                    this.View.ShowMessage("没有任何数据可以进行保存", MessageBoxOptions.OK);
                    return;
                }
                string sql1 = $@"select F_LQKJ_YM,F_LQKJ_TEXT from LQKJ_DEPARTMENTPROJECT where F_LQKJ_YM between {DepartmentProjectReportPlugIn.YEAR1} and {DepartmentProjectReportPlugIn.YEAR2} and F_LQKJ_TEXT between {DepartmentProjectReportPlugIn.QIJIAN1} and {DepartmentProjectReportPlugIn.QIJIAN2}";
                if (DBUtils.ExecuteDataSet(this.Context, sql1).Tables[0].Rows.Count > 0)
                {
                    this.View.ShowMessage("已存在部分数据,是否继续保存？", MessageBoxOptions.YesNo, new Action<MessageBoxResult>((result) =>
                    {
                        if (result == MessageBoxResult.Yes)
                        {
                            List<SqlObject> sqlObjects = new List<SqlObject>();
                            foreach (DataRow item in dt.Rows)
                            {
                                string Dep = item[1].ToString();//部门
                                string Item = item[3].ToString();//项目编号
                                string sql2 = $@"delete from LQKJ_DEPARTMENTPROJECT where F_LQKJ_DEP='{Dep}' and F_LQKJ_ITEM='{Item}' and F_LQKJ_YM between {DepartmentProjectReportPlugIn.YEAR1} and {DepartmentProjectReportPlugIn.YEAR2} and F_LQKJ_TEXT between {DepartmentProjectReportPlugIn.QIJIAN1} and {DepartmentProjectReportPlugIn.QIJIAN2}";
                                sqlObjects.Add(new SqlObject(sql2, new List<SqlParam>()));
                            }
                            DBUtils.ExecuteBatch(this.Context, sqlObjects);
                            Batch(dt);  
                            return;
                        }
                        else
                        {
                            return;
                        }
                    }));
                }
                else
                {
                    Batch(dt);
                    return;
                }

            }
            else if (e.BarItemKey == "LQKJ_tbButton11")//更新最新数据
            {
                string sql = "delete from LQKJ_t_GBMFYHZMX";
                
                DBUtils.Execute(this.Context, sql);
                
                this.View.Refresh();
            }
            else if(e.BarItemKey== "LQKJ_tbButton_1")//引入按钮
            {
                DynamicFormShowParameter form = new DynamicFormShowParameter();
                form.FormId = "LQKJ_YRMB";
                this.View.ShowForm(form);
            }else if (e.BarItemKey == "LQKJ_PZ")
            {
                string sql = "";
                if (DepartmentProjectReportPlugIn.lsSql2 != "")
                {
                    this.View.ShowErrMessage("请使用最新数据生成凭证!");
                    return;
                }
                    sql =  DepartmentProjectReportPlugIn.sqld + " and 是否分摊='分摊'";
                DataTable dt = DBUtils.ExecuteDataSet(this.Context, "select 部门,项目编号,F_LQKJ_ZJ,F_LQKJ_ZX,F_LQKJ_FW,F_LQKJ_SS,F_LQKJ_SD,F_LQKJ_WY,F_LQKJ_BG,F_LQKJ_FL from( " + sql+" ) A").Tables[0];
                string SqlSum= "select * from (select 部门,sum(round(F_LQKJ_ZJ,2)) F_LQKJ_ZJ,sum(round(F_LQKJ_ZX,2)) F_LQKJ_ZX,sum(round(F_LQKJ_FW,2)) F_LQKJ_FW,sum(round(F_LQKJ_SS,2)) F_LQKJ_SS,sum(round(F_LQKJ_SD,2)) F_LQKJ_SD,sum(round(F_LQKJ_WY,2)) F_LQKJ_WY,sum(round(F_LQKJ_BG,2)) F_LQKJ_BG,sum(round(F_LQKJ_FL,2))F_LQKJ_FL from( " + sql + " ) A group by 部门 )B";//每个部门每个科目汇总后的数据
                Logger.Info("生成凭证查询的sql", sql);
                if (dt.Rows.Count != 0)
                {
                    K3CloudApiClient client = new K3CloudApiClient(url);
                    var loginResult = client.ValidateLogin(dbid, login, pwd, 2052);
                    //K3CloudApiClient client = new K3CloudApiClient("http://192.168.1.37/K3Cloud/");
                    //var loginResult = client.ValidateLogin("646c66d0102ca7", "administrator", "kingdee@123", 2052);
                    var resultType = JObject.Parse(loginResult)["LoginResultType"].Value<int>();
                    if (resultType != 1)
                    {
                        this.View.ShowErrMessage("当前数据中心不正确,请前往Common.config文件配置,详细信息请看日志:凭证Dbid");
                        Logger.Info($"凭证Dbid:{dbid};Url:{url};Login:{login};Pwd:{pwd}", "错误");
                        return;
                    }
                    string model = "{\"Model\":[";
                    string json5 = "";
                    string json2 = "";
                    var name2 = "";
                    var Num = "";
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        
                        if (i > 0 && dt.Rows[i]["部门"].ToString() == dt.Rows[i - 1]["部门"].ToString())//后面的一行的部门等于前面一行的部门
                        {
                            string json7 = "";
                            for (int j = 2; j < dt.Columns.Count; j++)//从第6个开始算,前面的是
                            {
                                string Dep = dt.Rows[i]["部门"].ToString();
                                string Item = dt.Rows[i]["项目编号"].ToString();
                                string name = dt.Columns[j].ColumnName.ToString();
                                double price = Math.Round(Convert.ToDouble(dt.Rows[i][name].ToString()),9);
                                if (price == 0)
                                {
                                    continue;
                                }
                                string sql5 = $"select  {name},F_LQKJ_NUMBER from LQKJ_T_BMKMDYB where F_LQKJ_DEP='{Dep}'";
                                DataTable dt2 = DBUtils.ExecuteDataSet(this.Context, sql5).Tables[0];
                                 name2 = dt2.Rows[0][name].ToString();
                                 Num = dt2.Rows[0]["F_LQKJ_NUMBER"].ToString();
                                if (name2 == null)
                                {
                                    continue;
                                }
                                json2 = "{\"FEXPLANATION\": \"" + Type(dt.Columns[j].ColumnName.ToString()) + "\",\"FACCOUNTID\": {\"FNumber\": \"" + name2.ToString() + "\"},\"FDetailID\": {\"FDETAILID__FFLEX5\": {\"FNumber\": \"" + Num.ToString() + "\"},\"FDETAILID__FF100003\": {\"FNumber\": \"" + Item + "\"}},\"FCURRENCYID\": {\"FNumber\": \"PRE001\"},\"FEXCHANGERATETYPE\": {\"FNumber\": \"HLTX01_SYS\"},\"FDEBIT\": " + price + ",},";//借方
                                json7 += json2 ;
                            }
                            json5 = json5 + json7;
                            //string sqlss = "select 部门 from (select 部门,项目编号,F_LQKJ_ZJ,F_LQKJ_ZX,F_LQKJ_FW,F_LQKJ_SS,F_LQKJ_SD,F_LQKJ_WY,F_LQKJ_BG,F_LQKJ_FL from( " + sql + " ) A)B group by 部门";
                            //if (DBUtils.ExecuteDataSet(this.Context, "select 部门 from (select 部门,项目编号,F_LQKJ_ZJ,F_LQKJ_ZX,F_LQKJ_FW,F_LQKJ_SS,F_LQKJ_SD,F_LQKJ_WY,F_LQKJ_BG,F_LQKJ_FL from( " + sql + " ) A)B group by 部门").Tables[0].Rows.Count == 1 && i == dt.Rows.Count - 1)
                            //{
                            //    json5 += "]}";
                            //}
                            if (i == dt.Rows.Count-1)
                            {
                                string Dep6 = "";
                                Dep6 = dt.Rows[i ]["部门"].ToString();
                                string SumSql = SqlSum + $" where 部门='{Dep6}'";
                                DataTable dt6 = DBUtils.ExecuteDataSet(this.Context, SumSql).Tables[0];
                                string sql9 = $"select  * from LQKJ_T_BMKMDYB where F_LQKJ_DEP='{Dep6}'";
                                DataTable dt9 = DBUtils.ExecuteDataSet(this.Context, sql9).Tables[0];
                                string json8 = "";
                                for (int b = 0; b < dt6.Rows.Count; b++)
                                {
                                    for (int c = 1; c < dt6.Columns.Count; c++)
                                    {
                                        string name3 = dt6.Columns[c].ColumnName.ToString();
                                        string name4 = dt9.Rows[0][name3].ToString();
                                        double money = Math.Round(Convert.ToDouble(dt6.Rows[b][name3].ToString()), 9);
                                        if (money == 0)
                                        {
                                            continue;//如果为零继续循环
                                        }
                                        json8 = "{\"FEXPLANATION\": \"" + Type(dt6.Columns[c].ColumnName.ToString().ToUpper()) + "\",\"FACCOUNTID\": {\"FNumber\": \"" + name4 + "\"},\"FDetailID\": {\"FDETAILID__FFLEX5\": {\"FNumber\": \"" + Num.ToString() + "\"},\"FDETAILID__FF100003\": {\"FNumber\": \"" + "00000" + "\"}},\"FCURRENCYID\": {\"FNumber\": \"PRE001\"},\"FEXCHANGERATETYPE\": {\"FNumber\": \"HLTX01_SYS\"},\"FDEBIT\": " + -money + ",},";
                                        json5 += json8;
                                    }
                                }
                            }
                        }
                        else//不是同一个部门的话
                        {
                            if (i > 0 )
                            {
                                string Dep6 = "";
                                Dep6 = dt.Rows[i - 1]["部门"].ToString();
                                string SumSql = SqlSum + $" where 部门='{Dep6}'";
                                DataTable dt6 = DBUtils.ExecuteDataSet(this.Context, SumSql).Tables[0];
                                string sql9 = $"select  * from LQKJ_T_BMKMDYB where F_LQKJ_DEP='{Dep6}'";
                                DataTable dt9 = DBUtils.ExecuteDataSet(this.Context, sql9).Tables[0];
                                string json8 = "";
                                for (int b = 0; b < dt6.Rows.Count; b++)
                                {
                                    for(int c = 1; c < dt6.Columns.Count; c++)
                                    {
                                        string name3 = dt6.Columns[c].ColumnName.ToString();//字段名
                                        string name4 = dt9.Rows[0][name3].ToString();
                                        double money = Math.Round(Convert.ToDouble(dt6.Rows[b][name3].ToString()), 9);
                                        if (money == 0)
                                        {
                                            continue;//如果为零继续循环
                                        }
                                        json8 = "{\"FEXPLANATION\": \"" + Type(dt6.Columns[c].ColumnName.ToString().ToUpper()) + "\",\"FACCOUNTID\": {\"FNumber\": \"" + name4 + "\"},\"FDetailID\": {\"FDETAILID__FFLEX5\": {\"FNumber\": \"" + Num.ToString() + "\"},\"FDETAILID__FF100003\": {\"FNumber\": \"" + "00000" + "\"}},\"FCURRENCYID\": {\"FNumber\": \"PRE001\"},\"FEXCHANGERATETYPE\": {\"FNumber\": \"HLTX01_SYS\"},\"FDEBIT\": " + -money + ",},";
                                        json5 += json8;
                                    }
                                }
                                json5 = json5 + "]},";
                            }
                            string json1 = "{\"FAccountBookID\":{\"FNumber\": \"001\"},\"FVOUCHERGROUPID\": {\"FNumber\": \"PRE001\"},\"FDocumentStatus\": \"Z\",\"FEntity\": [";//每一条开头都要
                            string Dep = dt.Rows[i]["部门"].ToString();
                            string Item = dt.Rows[i]["项目编号"].ToString();
                            for (int j = 2; j < dt.Columns.Count; j++)//从第6个开始算,前面的是
                            {
                                string name = dt.Columns[j].ColumnName.ToString();
                                double price = Math.Round(Convert.ToDouble(dt.Rows[i][name].ToString()), 9);
                                if (price == 0)
                                {
                                    continue;
                                }
                                string sql5 = $"select  {name},F_LQKJ_NUMBER from LQKJ_T_BMKMDYB where F_LQKJ_DEP='{Dep}'";
                                DataTable dt2 = DBUtils.ExecuteDataSet(this.Context, sql5).Tables[0];
                                if (dt2.Rows.Count==0)
                                {
                                    this.View.ShowErrMessage(Dep+"在部门对照科目表中不存在"+Type(name)+"科目!请维护");
                                    Logger.Info("部门对照科目表查询:",sql5);
                                    return;
                                }
                                name2 = dt2.Rows[0][name].ToString();
                                Num = dt2.Rows[0]["F_LQKJ_NUMBER"].ToString();
                                if (name2 == null)
                                {
                                    continue;
                                }
                                json2 = "{\"FEXPLANATION\": \"" + Type(dt.Columns[j].ColumnName.ToString()) + "\",\"FACCOUNTID\": {\"FNumber\": \"" + name2.ToString() + "\"},\"FDetailID\": {\"FDETAILID__FFLEX5\": {\"FNumber\": \"" + Num.ToString() + "\"},\"FDETAILID__FF100003\": {\"FNumber\": \"" + Item + "\"}},\"FCURRENCYID\": {\"FNumber\": \"PRE001\"},\"FEXCHANGERATETYPE\": {\"FNumber\": \"HLTX01_SYS\"},\"FDEBIT\": " + price + ",},";//借方
                                json1 += json2 ;
                            }
                            json5 = json5 + json1 ;
                        }
                    }
                    string SaveJson = model + json5 + "]}]}";
                    string result = client.BatchSave("GL_VOUCHER", SaveJson).ToString();
                    Logger.Info("调用保存操作",result);
                    JObject Keys = JObject.Parse(result);
                    if (Keys["Result"]["ResponseStatus"]["IsSuccess"].ToString() == "True")
                    {
                        this.View.ShowMessage("生成成功");
                    }
                    else
                    {
                        this.View.ShowMessage("生成失败");
                    }
                }
                else
                {
                    this.View.ShowWarnningMessage("没有数据！");
                }

            }
            base.BarItemClick(e);
        }
        private string Type(string item)
        {
            switch (item)
            {
                case "F_LQKJ_GZ":
                    return "研发工时分摊:工资";
                case "F_LQKJ_JJ":
                    return "研发工时分摊:奖金";
                case "F_LQKJ_GS":
                    return "研发工时分摊:工伤保险费";
                case "F_LQKJ_SYBX":
                    return "研发工时分摊:生育保险费";
                case "F_LQKJ_SY":
                    return "研发工时分摊:失业保险费";
                case "F_LQKJ_YL":
                    return "研发工时分摊:养老保险费";
                case "F_LQKJ_YLBX":
                    return "研发工时分摊:医疗保险费";
                case "F_LQKJ_ZF":
                    return "研发工时分摊:住房公积金";
                case "F_LQKJ_FL":
                    return "研发工时分摊:福利费";
                case "F_LQKJ_BG":
                    return "研发工时分摊:办公费";
                case "F_LQKJ_CL":
                    return "研发工时分摊:差旅费";
                case "F_LQKJ_CCBZ":
                    return "研发工时分摊:出差补助";
                case "F_LQKJ_DZ":
                    return "研发工时分摊:低值易耗品";
                case "F_LQKJ_DX":
                    return "研发工时分摊:电信费";
                case "F_LQKJ_FW":
                    return "研发工时分摊:房屋租金";
                case "F_LQKJ_SD":
                    return "研发工时分摊:水电费";
                case "F_LQKJ_SS":
                    return "研发工时分摊:宿舍租金";
                case "F_LQKJ_WX":
                    return "研发工时分摊:维修服务费";
                case "F_LQKJ_WY":
                    return "研发工时分摊:物业管理费";
                case "F_LQKJ_YF":
                    return "研发工时分摊:研发材料费";
                case "F_LQKJ_YD":
                    return "研发工时分摊:邮递费";
                case "F_LQKJ_ZD":
                    return "研发工时分摊:招待费";
                case "F_LQKJ_ZJ":
                    return "研发工时分摊:折旧费";
                case "F_LQKJ_ZX":
                    return "研发工时分摊:装修服务费";
            }
            return "";
        }
        private void Batch(DataTable dt)
        {
            List<SqlObject> sqlObjects = new List<SqlObject>();
            string sql1 = "";
            int FID = int.Parse(DBUtils.ExecuteDataSet(this.Context, "select isnull(max(FID),99999) FID from LQKJ_DEPARTMENTPROJECT").Tables[0].Rows[0]["FID"].ToString());
            foreach (DataRow item in dt.Rows)
            {
                string Dep = item[1].ToString();//部门
                string Item = item[3].ToString();//项目编号
                string GS = item[4].ToString();//工时
                string BL = item[5].ToString();//分摊比例
                string GZ = item[6].ToString();//工资
                string JJ = item[7].ToString();//奖金
                string GSBX = item[8].ToString();//工商保险
                string SYBX = item[9].ToString();//生育保险
                string SY = item[10].ToString();//失业
                string YL = item[11].ToString();//养老
                string YLBX = item[12].ToString();//医疗
                string ZF = item[13].ToString();//住房
                string FL = item[14].ToString();//福利费
                string BG = item[15].ToString();//办公费
                string CL = item[16].ToString();//差旅费
                string CCBZ = item[17].ToString();//出差补助
                string DZ = item[18].ToString();//低值
                string DX = item[19].ToString();//电信
                string FW = item[20].ToString();//房屋
                string SD = item[21].ToString();//水电
                string SS = item[22].ToString();//宿舍租金
                string WX = item[23].ToString();//维修服务
                string WY = item[24].ToString();//物业管理
                string YF = item[25].ToString();//研发
                string YD = item[26].ToString();//邮递
                string ZD = item[27].ToString();//招待费
                string ZJ = item[28].ToString();//折旧费
                string ZX = item[29].ToString();//装修
                string DNAME = item[0].ToString();//部门名称
                string INAME = item[2].ToString();//项目名称
                string YEAR = item[30].ToString();//年
                string QIJIAN = item[31].ToString();//期间
                sql1 = $@"insert into LQKJ_DEPARTMENTPROJECT(FID,F_LQKJ_DEP,F_LQKJ_ITEM,F_LQKJ_GS,F_LQKJ_BL,F_LQKJ_GZ,F_LQKJ_JJ,
                            F_LQKJ_GSBX,F_LQKJ_SYBX,F_LQKJ_SY,F_LQKJ_YLBX,F_LQKJ_YL,F_LQKJ_ZF,F_LQKJ_FL,F_LQKJ_BG,F_LQKJ_CL,F_LQKJ_CCBZ,F_LQKJ_DZ,
                            F_LQKJ_DX,F_LQKJ_FW,F_LQKJ_SD,F_LQKJ_SS,F_LQKJ_WX,F_LQKJ_WY,F_LQKJ_YF,F_LQKJ_YD,F_LQKJ_ZD,F_LQKJ_ZJ,F_LQKJ_ZX,F_LQKJ_DNAME,F_LQKJ_INAME,F_LQKJ_YM,F_LQKJ_Text,F_LQKJ_TEXT2) values({++FID},'{Dep}','{Item}','{GS}','{BL}','{GZ}','{JJ}','{GSBX}','{SYBX}',
                    '{SY}','{YL}','{YLBX}','{ZF}','{FL}','{BG}','{CL}','{CCBZ}','{DZ}','{DX}','{FW}','{SD}','{SS}','{WX}','{WY}','{YF}','{YD}','{ZD}','{ZJ}','{ZX}','{DNAME}','{INAME}','{YEAR}','{QIJIAN}','{DateTime.Now.ToString("yyyyMM")}')";
                sqlObjects.Add(new SqlObject(sql1, new List<SqlParam>()));
            }
            DBUtils.ExecuteBatch(this.Context, sqlObjects);
            this.View.ShowMessage("保存成功");
        }
    }
}
