using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Log;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;

namespace LQKJ.K3.NDR.NewReportPlugIn
{
    [HotUpdate, Description("部门分摊")]
    public class DepartmentApportionDynamicPlugIn : SysReportBaseService
    {
        private string[] Type = null;
        public static string Month = "";
        public static string Year = "";
        public override void Initialize()//初始化
        {
            base.Initialize();
            this.ReportProperty.ReportName = new Kingdee.BOS.LocaleValue("部门分摊表", base.Context.LogLocale.LCID);
            ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;//简单账表
            this.ReportProperty.IsUIDesignerColumns = false;
            base.Initialize();
        }
        public override string GetTableName()
        {
            return base.GetTableName();
        }
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            string val = dyFilter["F_LQKJ_Combo"].ToString();
            DynamicObjectCollection val2 = dyFilter["F_LQKJ_KEMU1"] as DynamicObjectCollection;
            DynamicObjectCollection val5 = dyFilter["F_LQKJ_DEPS"] as DynamicObjectCollection;
            string ym = dyFilter["F_LQKJ_YM"].ToString();
            

            //List<string> s1 = new List<string>();
            string seqFld = string.Format(base.KSQL_SEQ, " T.部门编码 ");// 排序列不写不显示数据
            //string sql2 = $@"select a.*,b.FNUMBER as FNUMBER1,'00000' as FNUMBER2,c.FNAME as FNAME1 from  LQKJ_t_GBMFYHZMX a 
            //                    left join T_BD_DEPARTMENT b on a.F_LQKJ_BMNUMBER=b.FDEPTID
            //                    left join T_BD_DEPARTMENT_L c on c.FDEPTID=b.FDEPTID where 1=1 ";
            if (ym != null)
            {
                 Month = DateTime.ParseExact(ym, "yyyyMM", CultureInfo.InvariantCulture).Month.ToString();
                 Year = DateTime.ParseExact(ym, "yyyyMM", CultureInfo.InvariantCulture).Year.ToString();
                //sql2 += $@" and FPERIOD='{Month}' and FYEAR='{Year}'";
            }
            string sql = "";
            //if (DBUtils.ExecuteDataSet(this.Context, sql2).Tables[0].Rows.Count > 0)
            //{
            //     sql = $@"select *,{seqFld} into {tableName} from( select a.*,b.FNUMBER as 部门编码,'00000' as FNUMBER2,c.FNAME as 部门 from  LQKJ_t_GBMFYHZMX a 
            //                    left join T_BD_DEPARTMENT b on a.F_LQKJ_BMNUMBER=b.FDEPTID
            //                    left join T_BD_DEPARTMENT_L c on c.FDEPTID=b.FDEPTID where 1=1 )T";
            //}
            //else
            //{
                sql = $@" select *,{seqFld} into {tableName} from(   select 部门编码,FDEPTID,部门,FNUMBER as FNUMBER2,FPERIOD,FYEAR,isnull(sum(研发费用_租金_房屋租金),0)F_LQKJ_YF,isnull(sum(研发费用_租金_宿舍租金),0)F_LQKJ_SS
                            ,isnull(sum(研发费用_水电费),0)F_LQKJ_SDF,isnull(sum(研发费用_物业管理费),0)F_LQKJ_WY,isnull(sum(研发费用_办公费),0)F_LQKJ_BG,isnull(sum(研发费用_福利费),0)F_LQKJ_FL
                            ,isnull(sum(研发费用_折旧费),0)F_LQKJ_ZJ,isnull(sum(研发费用_服务费_装修服务费),0)F_LQKJ_ZX,
							isnull(sum(研发费用_人工成本_工资),0)F_LQKJ_GZ,isnull(sum(研发费用_人工成本_奖金),0)F_LQKJ_JJ,
							isnull(sum(研发费用_人工成本_社保费_工伤保险费),0)F_LQKJ_BX,isnull(sum(研发费用_人工成本_社保费_生育保险费),0)F_LQKJ_SY,
							isnull(sum(研发费用_人工成本_社保费_失业保险费),0)F_LQKJ_SYBX,isnull(sum(研发费用_人工成本_社保费_医疗保险费),0)F_LQKJ_YLBX,
							isnull(sum(研发费用_人工成本_社保费_养老保险费),0)F_LQKJ_YL,isnull(sum(研发费用_人工成本_住房公积金),0)F_LQKJ_ZF,
							isnull(sum(研发费用_差旅费),0)F_LQKJ_CL,isnull(sum(研发费用_差旅费_出差补助),0)F_LQKJ_CC,isnull(sum(研发费用_低值易耗品),0)F_LQKJ_DZ,
							isnull(sum(研发费用_电信费),0)F_LQKJ_DX,isnull(sum(研发费用_其他服务费_维修服务费),0)F_LQKJ_WX,isnull(sum(研发费用_招待费),0)F_LQKJ_ZD,
							isnull(sum(研发费用_邮递费),0)F_LQKJ_YD,isnull(sum(研发费用_研发材料费),0)F_LQKJ_YFCL,ROW_NUMBER()over(order by 部门) as num,isnull(sum(研发费用_人工成本_福利费_公司活动),0)F_LQKJ_GSHD,isnull(sum(研发费用_人工成本_福利费_部门活动),0)F_LQKJ_BMHD
							from (
                            Select f.FNUMBER,d.FNUMBER as 部门编码,d.FDEPTID,e.FNAME 部门,{Month} FPERIOD,{Year} FYEAR,case a.F_LQKJ_KM when 128849 then  F_LQKJ_AMOUNT end as 研发费用_租金_房屋租金
                            ,case a.F_LQKJ_KM when 128862 then  F_LQKJ_AMOUNT end as 研发费用_租金_宿舍租金,case a.F_LQKJ_KM when 128737 then  F_LQKJ_AMOUNT end as 研发费用_水电费
                            ,case a.F_LQKJ_KM when 128863 then  F_LQKJ_AMOUNT end as 研发费用_物业管理费,case a.F_LQKJ_KM when 128739 then  F_LQKJ_AMOUNT end as 研发费用_办公费
                            ,case a.F_LQKJ_KM when 128870 then  F_LQKJ_AMOUNT end as 研发费用_福利费,case a.F_LQKJ_KM when 128742 then  F_LQKJ_AMOUNT end as 研发费用_折旧费
                            ,case a.F_LQKJ_KM when 128768 then  F_LQKJ_AMOUNT end as 研发费用_服务费_装修服务费,case a.F_LQKJ_KM when 128766 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_工资
							,case a.F_LQKJ_KM when 128869 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_奖金,case a.F_LQKJ_KM when 128901 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_社保费_工伤保险费
							,case a.F_LQKJ_KM when 128902 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_社保费_生育保险费,case a.F_LQKJ_KM when 128900 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_社保费_失业保险费
							,case a.F_LQKJ_KM when 128898 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_社保费_养老保险费,case a.F_LQKJ_KM when 128899 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_社保费_医疗保险费
							,case a.F_LQKJ_KM when 128868 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_住房公积金,case a.F_LQKJ_KM when 128861 then  F_LQKJ_AMOUNT end as 研发费用_差旅费
							,case a.F_LQKJ_KM when 128861 then  F_LQKJ_AMOUNT end as 研发费用_差旅费_出差补助,case a.F_LQKJ_KM when 128741 then  F_LQKJ_AMOUNT end as 研发费用_低值易耗品
							,case a.F_LQKJ_KM when 128735 then  F_LQKJ_AMOUNT end as 研发费用_电信费,case a.F_LQKJ_KM when 128865 then  F_LQKJ_AMOUNT end as 研发费用_其他服务费_维修服务费
							,case a.F_LQKJ_KM when 128736 then  F_LQKJ_AMOUNT end as 研发费用_招待费,case a.F_LQKJ_KM when 128740 then  F_LQKJ_AMOUNT end as 研发费用_邮递费
							,case a.F_LQKJ_KM when 128738 then  F_LQKJ_AMOUNT end as 研发费用_研发材料费,case a.F_LQKJ_KM when 128870 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_福利费_公司活动,case a.F_LQKJ_KM when 272503 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_福利费_部门活动 
							From LQKJ_t_KMYESJ a
                                  left join T_BD_FLEXITEMDETAILV c on a.F_LQKJ_HSWD=c.FID
                            left join T_BD_DEPARTMENT d on c.FFLEX5=d.FDEPTID
                            left join T_BD_DEPARTMENT_L e on d.FDEPTID=e.FDEPTID
                            left join T_BAS_PREBDONE f on f.FID=c.FF100003
                            where 
                             a.F_LQKJ_HSWD!=0
                            and {Year} between F_LQKJ_STARTYEAR and F_LQKJ_ENDYEAR and {Month} between F_LQKJ_STARTMONTH and F_LQKJ_ENDMONTH
                            )T
                            group by 部门编码,部门,FPERIOD,FYEAR,FDEPTID,FNUMBER )T  ";
            //List<SqlObject> sqlObjects = new List<SqlObject>();
            //         string sql6 = $@"select 部门编码,FDEPTID,部门,'00000' as FNUMBER2,FPERIOD,FYEAR,isnull(sum(研发费用_租金_房屋租金),0)F_LQKJ_YF,isnull(sum(研发费用_租金_宿舍租金),0)F_LQKJ_SS
            //                     ,isnull(sum(研发费用_水电费),0)F_LQKJ_SDF,isnull(sum(研发费用_物业管理费),0)F_LQKJ_WY,isnull(sum(研发费用_办公费),0)F_LQKJ_BG,isnull(sum(研发费用_福利费),0)F_LQKJ_FL
            //                     ,isnull(sum(研发费用_折旧费),0)F_LQKJ_ZJ,isnull(sum(研发费用_服务费_装修服务费),0)F_LQKJ_ZX,
            //isnull(sum(研发费用_人工成本_工资),0)F_LQKJ_GZ,isnull(sum(研发费用_人工成本_奖金),0)F_LQKJ_JJ,
            //isnull(sum(研发费用_人工成本_社保费_工伤保险费),0)F_LQKJ_BX,isnull(sum(研发费用_人工成本_社保费_生育保险费),0)F_LQKJ_SY,
            //isnull(sum(研发费用_人工成本_社保费_失业保险费),0)F_LQKJ_SYBX,isnull(sum(研发费用_人工成本_社保费_医疗保险费),0)F_LQKJ_YLBX,
            //isnull(sum(研发费用_人工成本_社保费_养老保险费),0)F_LQKJ_YL,isnull(sum(研发费用_人工成本_住房公积金),0)F_LQKJ_ZF,
            //isnull(sum(研发费用_差旅费),0)F_LQKJ_CL,isnull(sum(研发费用_差旅费_出差补助),0)F_LQKJ_CC,isnull(sum(研发费用_低值易耗品),0)F_LQKJ_DZ,
            //isnull(sum(研发费用_电信费),0)F_LQKJ_DX,isnull(sum(研发费用_其他服务费_维修服务费),0)F_LQKJ_WX,isnull(sum(研发费用_招待费),0)F_LQKJ_ZD,
            //isnull(sum(研发费用_邮递费),0)F_LQKJ_YD,isnull(sum(研发费用_研发材料费),0)F_LQKJ_YFCL,ROW_NUMBER()over(order by 部门) as num
            //from (
            //                     Select d.FNUMBER as 部门编码,d.FDEPTID,e.FNAME 部门,FPERIOD,FYEAR,case a.FACCOUNTID when 128849 then  FDEBIT end as 研发费用_租金_房屋租金
            //                     ,case a.FACCOUNTID when 128862 then  FDEBIT end as 研发费用_租金_宿舍租金,case a.FACCOUNTID when 128737 then  FDEBIT end as 研发费用_水电费
            //                     ,case a.FACCOUNTID when 128863 then  FDEBIT end as 研发费用_物业管理费,case a.FACCOUNTID when 128739 then  FDEBIT end as 研发费用_办公费
            //                     ,case a.FACCOUNTID when 128870 then  FDEBIT end as 研发费用_福利费,case a.FACCOUNTID when 128742 then  FDEBIT end as 研发费用_折旧费
            //                     ,case a.FACCOUNTID when 128768 then  FDEBIT end as 研发费用_服务费_装修服务费,case a.FACCOUNTID when 128766 then  FDEBIT end as 研发费用_人工成本_工资
            //,case a.FACCOUNTID when 128869 then  FDEBIT end as 研发费用_人工成本_奖金,case a.FACCOUNTID when 128901 then  FDEBIT end as 研发费用_人工成本_社保费_工伤保险费
            //,case a.FACCOUNTID when 128902 then  FDEBIT end as 研发费用_人工成本_社保费_生育保险费,case a.FACCOUNTID when 128900 then  FDEBIT end as 研发费用_人工成本_社保费_失业保险费
            //,case a.FACCOUNTID when 128898 then  FDEBIT end as 研发费用_人工成本_社保费_养老保险费,case a.FACCOUNTID when 128899 then  FDEBIT end as 研发费用_人工成本_社保费_医疗保险费
            //,case a.FACCOUNTID when 128868 then  FDEBIT end as 研发费用_人工成本_住房公积金,case a.FACCOUNTID when 128861 then  FDEBIT end as 研发费用_差旅费
            //,case a.FACCOUNTID when 128861 then  FDEBIT end as 研发费用_差旅费_出差补助,case a.FACCOUNTID when 128741 then  FDEBIT end as 研发费用_低值易耗品
            //,case a.FACCOUNTID when 128735 then  FDEBIT end as 研发费用_电信费,case a.FACCOUNTID when 128865 then  FDEBIT end as 研发费用_其他服务费_维修服务费
            //,case a.FACCOUNTID when 128736 then  FDEBIT end as 研发费用_招待费,case a.FACCOUNTID when 128740 then  FDEBIT end as 研发费用_邮递费
            //,case a.FACCOUNTID when 128738 then  FDEBIT end as 研发费用_研发材料费
            //From T_GL_Balance a 
            //                     left join T_BD_FLEXITEMDETAILV c on a.FDETAILID=c.FID
            //                     left join T_BD_DEPARTMENT d on c.FFLEX5=d.FDEPTID
            //                     left join T_BD_DEPARTMENT_L e on d.FDEPTID=e.FDEPTID
            //                     where  FCURRENCYID=1 --人民币
            //                     and a.FDETAILID!=0
            //                     and FPERIOD like '%{Month}%' and FYEAR like '%{Year}%'--当期,借方本位币金额
            //                     )T
            //                     group by 部门编码,部门,FPERIOD,FYEAR,FDEPTID";
            //         DataTable dt = DBUtils.ExecuteDataSet(this.Context, sql6).Tables[0];
            //         for(int i = 0; i < dt.Rows.Count; i++)
            //         {
            //             string sql1 = $"insert into Z_LQKJ_t_GBMFYHZMX(Column1) values({i+1})";
            //             sqlObjects.Add(new SqlObject(sql1, new List<SqlParam>()));
            //         }
            //         DBUtils.ExecuteBatch(this.Context, sqlObjects);
            //         sqlObjects.Clear();
            //string sql3 = $@"select * from ("+sql + ") T left join Z_LQKJ_t_DepartmentApportion A on T.num = A.Column1";
            string sql7 = $@"select *  from(   select 部门编码,FDEPTID,部门,FNUMBER as FNUMBER2,FPERIOD,FYEAR,isnull(sum(研发费用_租金_房屋租金),0)F_LQKJ_YF,isnull(sum(研发费用_租金_宿舍租金),0)F_LQKJ_SS
                            ,isnull(sum(研发费用_水电费),0)F_LQKJ_SDF,isnull(sum(研发费用_物业管理费),0)F_LQKJ_WY,isnull(sum(研发费用_办公费),0)F_LQKJ_BG,isnull(sum(研发费用_福利费),0)F_LQKJ_FL
                            ,isnull(sum(研发费用_折旧费),0)F_LQKJ_ZJ,isnull(sum(研发费用_服务费_装修服务费),0)F_LQKJ_ZX,
							isnull(sum(研发费用_人工成本_工资),0)F_LQKJ_GZ,isnull(sum(研发费用_人工成本_奖金),0)F_LQKJ_JJ,
							isnull(sum(研发费用_人工成本_社保费_工伤保险费),0)F_LQKJ_BX,isnull(sum(研发费用_人工成本_社保费_生育保险费),0)F_LQKJ_SY,
							isnull(sum(研发费用_人工成本_社保费_失业保险费),0)F_LQKJ_SYBX,isnull(sum(研发费用_人工成本_社保费_医疗保险费),0)F_LQKJ_YLBX,
							isnull(sum(研发费用_人工成本_社保费_养老保险费),0)F_LQKJ_YL,isnull(sum(研发费用_人工成本_住房公积金),0)F_LQKJ_ZF,
							isnull(sum(研发费用_差旅费),0)F_LQKJ_CL,isnull(sum(研发费用_差旅费_出差补助),0)F_LQKJ_CC,isnull(sum(研发费用_低值易耗品),0)F_LQKJ_DZ,
							isnull(sum(研发费用_电信费),0)F_LQKJ_DX,isnull(sum(研发费用_其他服务费_维修服务费),0)F_LQKJ_WX,isnull(sum(研发费用_招待费),0)F_LQKJ_ZD,
							isnull(sum(研发费用_邮递费),0)F_LQKJ_YD,isnull(sum(研发费用_研发材料费),0)F_LQKJ_YFCL,ROW_NUMBER()over(order by 部门) as num
							from (
                            Select f.FNUMBER,d.FNUMBER as 部门编码,d.FDEPTID,e.FNAME 部门,{Month} FPERIOD,{Year} FYEAR,case a.F_LQKJ_KM when 128849 then  F_LQKJ_AMOUNT end as 研发费用_租金_房屋租金
                            ,case a.F_LQKJ_KM when 128862 then  F_LQKJ_AMOUNT end as 研发费用_租金_宿舍租金,case a.F_LQKJ_KM when 128737 then  F_LQKJ_AMOUNT end as 研发费用_水电费
                            ,case a.F_LQKJ_KM when 128863 then  F_LQKJ_AMOUNT end as 研发费用_物业管理费,case a.F_LQKJ_KM when 128739 then  F_LQKJ_AMOUNT end as 研发费用_办公费
                            ,case a.F_LQKJ_KM when 128870 then  F_LQKJ_AMOUNT end as 研发费用_福利费,case a.F_LQKJ_KM when 128742 then  F_LQKJ_AMOUNT end as 研发费用_折旧费
                            ,case a.F_LQKJ_KM when 128768 then  F_LQKJ_AMOUNT end as 研发费用_服务费_装修服务费,case a.F_LQKJ_KM when 128766 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_工资
							,case a.F_LQKJ_KM when 128869 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_奖金,case a.F_LQKJ_KM when 128901 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_社保费_工伤保险费
							,case a.F_LQKJ_KM when 128902 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_社保费_生育保险费,case a.F_LQKJ_KM when 128900 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_社保费_失业保险费
							,case a.F_LQKJ_KM when 128898 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_社保费_养老保险费,case a.F_LQKJ_KM when 128899 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_社保费_医疗保险费
							,case a.F_LQKJ_KM when 128868 then  F_LQKJ_AMOUNT end as 研发费用_人工成本_住房公积金,case a.F_LQKJ_KM when 128861 then  F_LQKJ_AMOUNT end as 研发费用_差旅费
							,case a.F_LQKJ_KM when 128861 then  F_LQKJ_AMOUNT end as 研发费用_差旅费_出差补助,case a.F_LQKJ_KM when 128741 then  F_LQKJ_AMOUNT end as 研发费用_低值易耗品
							,case a.F_LQKJ_KM when 128735 then  F_LQKJ_AMOUNT end as 研发费用_电信费,case a.F_LQKJ_KM when 128865 then  F_LQKJ_AMOUNT end as 研发费用_其他服务费_维修服务费
							,case a.F_LQKJ_KM when 128736 then  F_LQKJ_AMOUNT end as 研发费用_招待费,case a.F_LQKJ_KM when 128740 then  F_LQKJ_AMOUNT end as 研发费用_邮递费
							,case a.F_LQKJ_KM when 128738 then  F_LQKJ_AMOUNT end as 研发费用_研发材料费
							From LQKJ_t_KMYESJ a
                                  left join T_BD_FLEXITEMDETAILV c on a.F_LQKJ_HSWD=c.FID
                            left join T_BD_DEPARTMENT d on c.FFLEX5=d.FDEPTID
                            left join T_BD_DEPARTMENT_L e on d.FDEPTID=e.FDEPTID
                            left join T_BAS_PREBDONE f on f.FID=c.FF100003
                            where 
                             a.F_LQKJ_HSWD!=0
                            and {Year} between F_LQKJ_STARTYEAR and F_LQKJ_ENDYEAR and {Month} between F_LQKJ_STARTMONTH and F_LQKJ_ENDMONTH
                            )T
                            group by 部门编码,部门,FPERIOD,FYEAR,FDEPTID,FNUMBER )T";
            string DeleteSql = $"delete from LQKJ_t_GBMFYHZMX where FPERIOD='{Month}' and FYEAR='{Year}'";
            DBUtils.Execute(this.Context, DeleteSql);
            List<SqlObject> sqlObjects = new List<SqlObject>();
         DataTable   dt = DBUtils.ExecuteDataSet(this.Context, sql7).Tables[0];
            foreach (DataRow item in dt.Rows)
            {
                string Dep = item[1].ToString();//部门
                string YF = item[6].ToString();//
                string SS = item[7].ToString();//奖金
                string SDF = item[8].ToString();//工商保险
                string WY = item[9].ToString();//生育保险
                string BG = item[10].ToString();//失业
                string FL = item[11].ToString();//养老
                string ZJ = item[12].ToString();//医疗
                string ZX = item[13].ToString();//住房
                string F_LQKJ_GZ = item[14].ToString();//福利费
                string F_LQKJ_JJ = item[15].ToString();//差旅费
                string F_LQKJ_BX = item[16].ToString();//差旅费
                string F_LQKJ_SY = item[17].ToString();//出差补助
                string F_LQKJ_SYBX = item[18].ToString();//低值
                string F_LQKJ_YLBX = item[19].ToString();//电信
                string F_LQKJ_YL = item[20].ToString();//房屋
                string F_LQKJ_ZF = item[21].ToString();//水电
                string F_LQKJ_CL = item[22].ToString();//宿舍租金
                string F_LQKJ_CC = item[23].ToString();//维修服务
                string F_LQKJ_DZ = item[24].ToString();//物业管理
                string F_LQKJ_DX = item[26].ToString();//邮递
                string F_LQKJ_WX = item[27].ToString();//招待费
                string F_LQKJ_ZD = item[28].ToString();//折旧费
                string F_LQKJ_YD = item[29].ToString();//装修
                string F_LQKJ_YFCL = item[29].ToString();//部门名称
                string FPERIOD = item[4].ToString();//项目名称
                string FYEAR = item[5].ToString();//项目名称
                //string ID = item[31].ToString();//项目名称
                string sql5 = $@"insert into LQKJ_t_GBMFYHZMX(FID,F_LQKJ_BMNUMBER,F_LQKJ_BM,F_LQKJ_TEXT2,F_LQKJ_YF,F_LQKJ_SS,F_LQKJ_SDF,F_LQKJ_WY,F_LQKJ_BG,F_LQKJ_FL,F_LQKJ_ZJ,F_LQKJ_ZX,F_LQKJ_GZ,
                                         F_LQKJ_JJ,F_LQKJ_BX,F_LQKJ_SY,F_LQKJ_SYBX,F_LQKJ_YLBX,F_LQKJ_YL,F_LQKJ_ZF,F_LQKJ_CL,F_LQKJ_CC,F_LQKJ_DZ,F_LQKJ_DX,F_LQKJ_WX,F_LQKJ_ZD,F_LQKJ_YD,F_LQKJ_YFCL,FPERIOD,FYEAR) values(newid(),'{Dep}','{Dep}','00000','{YF}','{SS}','{SDF}','{WY}','{BG}',
                                        '{FL}', '{ZJ}','{ZX}','{F_LQKJ_GZ}','{F_LQKJ_JJ}','{F_LQKJ_BX}','{F_LQKJ_SY}','{F_LQKJ_SYBX}','{F_LQKJ_YLBX}','{F_LQKJ_YL}','{F_LQKJ_ZF}','{F_LQKJ_CL}','{F_LQKJ_CC}',
                                         '{F_LQKJ_DZ}','{F_LQKJ_DX}','{F_LQKJ_WX}','{F_LQKJ_ZD}','{F_LQKJ_YD}','{F_LQKJ_YFCL}','{FPERIOD}','{FYEAR}')";
                sqlObjects.Add(new SqlObject(sql5, new List<SqlParam>()));
            }
            DBUtils.ExecuteBatch(this.Context, sqlObjects);

            //}

            if (val5.Count() != 0)
            {
                if (val5.Count() == 1)
                {
                    foreach (DynamicObject item1 in val5)//部门多选取值
                    {
                        var i2 = item1[2] as DynamicObject;
                        var i3 = i2[3].ToString();//这个可以拿到文本值
                        sql += $@" where T.部门='{i3}'";
                    }
                }
                else
                {
                    sql += $@" where T.部门='1'";
                    foreach (DynamicObject item1 in val5)//部门多选取值
                    {
                        var i2 = item1[2] as DynamicObject;
                        var i3 = i2[3].ToString();//这个可以拿到文本值
                        sql += $@" or T.部门='{i3}'";
                    }
                }  
            }
            else
            {
                sql += $@" where 部门 in ('产品工程部', '手机产品线', '质量部', 'NPI', '可穿戴产品线', '可测量产品线', '产品开发部', '基础材料部', '战略Mrketing', '技术委员会') ";
            }
            if (ym != null)
            {
                 Month = DateTime.ParseExact(ym, "yyyyMM", CultureInfo.InvariantCulture).Month.ToString();
                 Year = DateTime.ParseExact(ym, "yyyyMM", CultureInfo.InvariantCulture).Year.ToString();
                sql += $@" and FPERIOD={Month} and FYEAR='{Year}'";
            }
            if (val2.Count != 0)
            {
                DynamicObject val4 = null;
                int i = 0;
                List<string> list = new List<string>();
                foreach (DynamicObject item in val2)
                {
                    //val3 = item as DynamicObject;
                    val4 = item[2] as DynamicObject;
                    list.Add(val4[3].ToString());
                    i++;
                }
                Type = list.ToArray();
            }
            else
            {
                sql += $@"and 
                       F_LQKJ_YF!=0 or F_LQKJ_SS!=0 or F_LQKJ_SDF!=0 or F_LQKJ_WY!=0 or F_LQKJ_BG!=0 or F_LQKJ_FL!=0 or F_LQKJ_ZJ!=0 or F_LQKJ_ZX!=0";
            }
            DBUtils.Execute(this.Context, sql);

            //DBUtils.Execute(this.Context, "delete from Z_LQKJ_t_GBMFYHZMX");
            base.BuilderReportSqlAndTempTable(filter, tableName);
        }
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            ReportHeader header = new ReportHeader();
            header.AddChild("部门编码", new Kingdee.BOS.LocaleValue("部门编码"));
            header.AddChild("部门", new Kingdee.BOS.LocaleValue("部门"));
            header.AddChild("FNUMBER2", new Kingdee.BOS.LocaleValue("项目号"));
            if (Type != null)
            {
                foreach (string item in Type)
                {
                    switch (item)
                    {
                        case "房屋租金":
                            header.AddChild("F_LQKJ_YF", new Kingdee.BOS.LocaleValue("研发费用_租金_房屋租金"), SqlStorageType.SqlDecimal);
                            break;
                        case "宿舍租金":
                            header.AddChild("F_LQKJ_SS", new Kingdee.BOS.LocaleValue("研发费用_租金_宿舍租金"), SqlStorageType.SqlDecimal);
                            break;
                        case "水电费":
                            header.AddChild("F_LQKJ_SDF", new Kingdee.BOS.LocaleValue("研发费用_水电费"), SqlStorageType.SqlDecimal);
                            break;
                        case "物业管理费":
                            header.AddChild("F_LQKJ_WY", new Kingdee.BOS.LocaleValue("研发费用_物业管理费"), SqlStorageType.SqlDecimal);
                            break;
                        case "办公费":
                            header.AddChild("F_LQKJ_BG", new Kingdee.BOS.LocaleValue("研发费用_办公费"), SqlStorageType.SqlDecimal);
                            break;
                        case "福利费":
                            header.AddChild("F_LQKJ_FL", new Kingdee.BOS.LocaleValue("研发费用_福利费"), SqlStorageType.SqlDecimal);
                            break;
                        case "折旧费":
                            header.AddChild("F_LQKJ_ZJ", new Kingdee.BOS.LocaleValue("研发费用_折旧费"), SqlStorageType.SqlDecimal);
                            break;
                        case "装修服务费":
                            header.AddChild("F_LQKJ_ZX", new Kingdee.BOS.LocaleValue("研发费用_服务费_装修服务费"), SqlStorageType.SqlDecimal);
                            break;
                        case "工资":
                            header.AddChild("F_LQKJ_GZ", new Kingdee.BOS.LocaleValue("研发费用_人工成本_工资"), SqlStorageType.SqlDecimal);
                            break;
                        case "奖金":
                            header.AddChild("F_LQKJ_JJ", new Kingdee.BOS.LocaleValue("研发费用_人工成本_奖金"), SqlStorageType.SqlDecimal);
                            break;
                        case "工伤保险费":
                            header.AddChild("F_LQKJ_BX", new Kingdee.BOS.LocaleValue("研发费用_人工成本_社保费_工伤保险费"), SqlStorageType.SqlDecimal);
                            break;
                        case "生育保险费":
                            header.AddChild("F_LQKJ_SY", new Kingdee.BOS.LocaleValue("研发费用_人工成本_社保费_生育保险费"), SqlStorageType.SqlDecimal);
                            break;
                        case "失业保险费":
                            header.AddChild("F_LQKJ_SYBX", new Kingdee.BOS.LocaleValue("研发费用_人工成本_社保费_失业保险费"), SqlStorageType.SqlDecimal);
                            break;
                        case "养老保险费":
                            header.AddChild("F_LQKJ_YL", new Kingdee.BOS.LocaleValue("研发费用_人工成本_社保费_养老保险费"), SqlStorageType.SqlDecimal);
                            break;
                        case "医疗保险费":
                            header.AddChild("F_LQKJ_YLBX", new Kingdee.BOS.LocaleValue("研发费用_人工成本_社保费_医疗保险费"), SqlStorageType.SqlDecimal);
                            break;
                        case "住房公积金":
                            header.AddChild("F_LQKJ_ZF", new Kingdee.BOS.LocaleValue("研发费用_人工成本_住房公积金"), SqlStorageType.SqlDecimal);
                            break;
                        case "差旅费":
                            header.AddChild("F_LQKJ_CL", new Kingdee.BOS.LocaleValue("研发费用_差旅费"), SqlStorageType.SqlDecimal);
                            break;
                        case "出差补助":
                            header.AddChild("F_LQKJ_CC", new Kingdee.BOS.LocaleValue("研发费用_差旅费_出差补助"), SqlStorageType.SqlDecimal);
                            break;
                        case "低值易耗品":
                            header.AddChild("F_LQKJ_DZ", new Kingdee.BOS.LocaleValue("研发费用_低值易耗品"), SqlStorageType.SqlDecimal);
                            break;
                        case "电信费":
                            header.AddChild("F_LQKJ_DX", new Kingdee.BOS.LocaleValue("研发费用_电信费"), SqlStorageType.SqlDecimal);
                            break;
                        case "维修服务费":
                            header.AddChild("F_LQKJ_WX", new Kingdee.BOS.LocaleValue("研发费用_其他服务费_维修服务费"), SqlStorageType.SqlDecimal);
                            break;
                        case "招待费":
                            header.AddChild("F_LQKJ_ZD", new Kingdee.BOS.LocaleValue("研发费用_招待费"), SqlStorageType.SqlDecimal);
                            break;
                        case "邮递费":
                            header.AddChild("F_LQKJ_YD", new Kingdee.BOS.LocaleValue("研发费用_邮递费"), SqlStorageType.SqlDecimal);
                            break;
                        case "研发材料费":
                            header.AddChild("F_LQKJ_YFCL", new Kingdee.BOS.LocaleValue("研发费用_研发材料费"), SqlStorageType.SqlDecimal);
                            break;
                        case "部门活动":
                            header.AddChild("F_LQKJ_BMHD", new Kingdee.BOS.LocaleValue("研发费用_人工成本_福利费_部门活动"), SqlStorageType.SqlDecimal);
                            break;
                        case "公司活动":
                            header.AddChild("F_LQKJ_GSHD", new Kingdee.BOS.LocaleValue("研发费用_人工成本_福利费_公司活动"), SqlStorageType.SqlDecimal);
                            break;
                    }
                }
                Type = null;
            }
            else
            {
                header.AddChild("F_LQKJ_YF", new Kingdee.BOS.LocaleValue("研发费用_租金_房屋租金"), SqlStorageType.SqlDecimal);
                header.AddChild("F_LQKJ_SS", new Kingdee.BOS.LocaleValue("研发费用_租金_宿舍租金"), SqlStorageType.SqlDecimal);
                header.AddChild("F_LQKJ_SDF", new Kingdee.BOS.LocaleValue("研发费用_水电费"), SqlStorageType.SqlDecimal);
                header.AddChild("F_LQKJ_WY", new Kingdee.BOS.LocaleValue("研发费用_物业管理费"), SqlStorageType.SqlDecimal);
                header.AddChild("F_LQKJ_BG", new Kingdee.BOS.LocaleValue("研发费用_办公费"), SqlStorageType.SqlDecimal);
                header.AddChild("F_LQKJ_FL", new Kingdee.BOS.LocaleValue("研发费用_福利费"), SqlStorageType.SqlDecimal);
                header.AddChild("F_LQKJ_ZJ", new Kingdee.BOS.LocaleValue("研发费用_折旧费"), SqlStorageType.SqlDecimal);
                header.AddChild("F_LQKJ_ZX", new Kingdee.BOS.LocaleValue("研发费用_服务费_装修服务费"), SqlStorageType.SqlDecimal);
            }
            return header;
        }
        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)//汇总列构造     
        {
            var result = base.GetSummaryColumnInfo(filter);
            result.Add(new SummaryField("F_LQKJ_GZ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_YF", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_SDF", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_SS", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_WY", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_ZJ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_ZX", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_FL", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_JJ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_SYBX", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_SY", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_YL", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_BG", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_BX", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_YLBX", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_YFCL", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));

            result.Add(new SummaryField("F_LQKJ_WX", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_CC", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_DZ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_DX", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_YD", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_ZD", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            return result;
        }
        protected override string GetIdentityFieldIndexSQL(string tableName)
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }
        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }
        protected override void AfterCreateTempTable(string tablename)
        {
            base.AfterCreateTempTable(tablename);
        }
    }
}
