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.Linq;
using System.Text;

namespace LQKJ.K3.NDR.NewReportPlugIn
{
    [HotUpdate, Description("部门具体项目分摊表")]
    public class DepartmentProjectReportPlugIn : SysReportBaseService
    {
        private string[] Type = null;
        public static string YEAR1 = "";
        public static string YEAR2 = "";
        public static string QIJIAN1 = "";
        public static string QIJIAN2 = "";
        public static string sqld = "";
        public static string lsSql2 = "";
        public override void 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;
            DynamicObject Product = dyFilter["F_LQKJ_Item"] as DynamicObject;
            DynamicObjectCollection Dep = dyFilter["F_LQKJ_DEP"] as DynamicObjectCollection;

            DynamicObjectCollection val2 = dyFilter["F_LQKJ_KEMU"] as DynamicObjectCollection;
            string val3 = dyFilter["F_LQKJ_CheckBox"].ToString();
             YEAR1 = dyFilter["F_LQKJ_Combo11"].ToString();
             YEAR2 = dyFilter["F_LQKJ_Combo1"].ToString();
             QIJIAN1 = dyFilter["F_LQKJ_Combo2"].ToString();
             QIJIAN2 = dyFilter["F_LQKJ_Combo3"].ToString();
            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();
            }
            string seqFld = string.Format(base.KSQL_SEQ, " T.项目编号 ");// 排序列不写不显示数据
            string lsSql = $@"select *,F_LQKJ_GS as 工时,convert(varchar(20),convert(decimal(18,2),F_LQKJ_BL))+'%' 分摊比例2,F_LQKJ_INAME 项目编号,F_LQKJ_DNAME 部门 from LQKJ_DEPARTMENTPROJECT) T where 1=1 ";
            string Item = string.Empty, Dep2 = string.Empty;
            string sql1 = $@"/*dialect*/select *,convert(varchar(20),convert(decimal(18,2),isnull(工时/nullif(各部门总工时,0)*100,0)))+'%' 分摊比例2,{seqFld} into {tableName} from  (";
            string sql2 = $@"select A.FNAME 部门,部门 as FDEPTID,B.FNUMBER 项目编号,项目编号 F_LQKJ_FITEM,工时,分摊比例*100 分摊比例,工资*分摊比例  F_LQKJ_GZ,奖金*分摊比例 F_LQKJ_JJ,工伤保险*分摊比例 F_LQKJ_GS,失业*分摊比例
							F_LQKJ_SYBX,生育*分摊比例 F_LQKJ_SY,养老*分摊比例 F_LQKJ_YL,医疗*分摊比例 F_LQKJ_YLBX,住房*分摊比例 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,年份,期间 FPERIOD,总工时 各部门总工时,'分摊' 是否分摊 from (
							select isnull(工时/nullif(总工时,0),0) 分摊比例,总工时,工时,项目编号,T.* from (
							select F_LQKJ_BMNUMBER 部门,FPERIOD 期间,FYEAR 年份,sum(F_LQKJ_YF) 房屋,sum(F_LQKJ_SS) 宿舍,sum(F_LQKJ_WY) 物业,sum(F_LQKJ_SDF) 水电费,sum(F_LQKJ_FL) 福利费,sum(F_LQKJ_ZX) 装修,sum(F_LQKJ_ZJ) 折旧,
							sum(F_LQKJ_BG) 办公,sum(F_LQKJ_GZ) 工资,sum(F_LQKJ_JJ) 奖金,sum(F_LQKJ_BX) 工伤保险,sum(F_LQKJ_SY) 生育,sum(F_LQKJ_SYBX) 失业,sum(F_LQKJ_YL) 养老,sum(F_LQKJ_YLBX) 医疗,
							sum(F_LQKJ_ZF) 住房,sum(F_LQKJ_CL) 差旅,sum(F_LQKJ_CC) 出差补助,sum(F_LQKJ_DX) 电信,sum(F_LQKJ_DZ) 低值,sum(F_LQKJ_ZD) 招待,sum(F_LQKJ_WX) 维修,sum(F_LQKJ_YD) 邮递,sum(F_LQKJ_YFCL) 研发
							from LQKJ_t_GBMFYHZMX group by F_LQKJ_BMNUMBER,FPERIOD,FYEAR
							) T left join
							(
							select F_LQKJ_FDEPARTMENT,sum(工时) 工时,年份,期间,项目编号 from (
							select F_LQKJ_FDEPARTMENT,F_LQKJ_WORKTIME 工时,year(F_LQKJ_DATE2) 年份,month(F_LQKJ_DATE2) 期间,F_LQKJ_FITEM 项目编号 from LQKJ_T_PROJECTHOURS a left join T_BAS_PREBDONE b on a.F_LQKJ_FITEM=b.FID
							where F_LQKJ_COMBO=0 and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 --没请假的，并且项目是已经分摊的
							)T1 group by F_LQKJ_FDEPARTMENT,年份,期间,项目编号
							)T2 on T2.F_LQKJ_FDEPARTMENT=T.部门 and T.年份=T2.年份 and T.期间=T2.期间 --这个算的是每个部门各个项目的工时
							left join (
							select F_LQKJ_FDEPARTMENT,sum(工时) 总工时,年份,期间 from (
							select F_LQKJ_FDEPARTMENT,F_LQKJ_WORKTIME 工时,year(F_LQKJ_DATE2) 年份,month(F_LQKJ_DATE2) 期间 from LQKJ_T_PROJECTHOURS a left join T_BAS_PREBDONE b on a.F_LQKJ_FITEM=b.FID
							where F_LQKJ_COMBO=0 and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 --没请假的，并且项目是已经分摊的
							)T3 group by F_LQKJ_FDEPARTMENT,年份,期间
							) T4 on T.部门=T4.F_LQKJ_FDEPARTMENT and T.年份=T4.年份 and T.期间=T4.期间 --这个算的是总工时
							) T
							left join T_BD_DEPARTMENT_L A on A.FDEPTID=T.部门
							left join T_BAS_PREBDONE B on B.FID=T.项目编号
";
            string sql3 = $@" )T  where  部门 is not null and 项目编号 is not null and 工时 is not null  ";
            //
            if (Product != null)
            {
                Item = Product["Number"].ToString();
                sql3 += $" and 项目编号='{Item}'";
                lsSql += $" and 项目编号='{Item}'";
            }
            if (Dep.Count!=0)
            {
                sql3 += $" and 部门 in ( ";
                lsSql += $" and 部门 in ( ";
                foreach (DynamicObject item in Dep)
                {
                    DynamicObject o = item[2] as DynamicObject;
                    Dep2 = o[3].ToString();
                    sql3 += $"'{Dep2}',";
                    lsSql += $"'{Dep2}',";
                }
                sql3 = sql3.Substring(0,sql3.Length - 1)+ "  )";
                lsSql = lsSql.Substring(0, lsSql.Length - 1) + "  )";
            }
            else
            {
                sql3 += $@" and 部门 in ('产品工程部', '手机产品线', '质量部', 'NPI', '可穿戴产品线', '可测量产品线', '产品开发部', '基础材料部', '战略Mrketing', '技术委员会')";
                lsSql += $@" and 部门 in ('产品工程部', '手机产品线', '质量部', 'NPI', '可穿戴产品线', '可测量产品线', '产品开发部', '基础材料部', '战略Mrketing', '技术委员会')";
            }
            if (YEAR1 != "" && YEAR2 != "")
            {
                sql3 += $@" and 年份 between {YEAR1} and {YEAR2}";
                lsSql += $@" and F_LQKJ_YM between {YEAR1} and {YEAR2}";
            }
            if (QIJIAN1 != "" && QIJIAN2 != "")
            {
                sql3 += $@" and FPERIOD between {QIJIAN1} and {QIJIAN2}";
                lsSql += $@" and F_LQKJ_TEXT between {QIJIAN1} and {QIJIAN2}";
            }
            if (val3 == "True")
            {
                lsSql2 = $@"/*dialect*/select * from (" + lsSql;
                DBUtils.Execute(this.Context, $"/*dialect*/select *,{seqFld} into {tableName} from ( " +lsSql);
                Logger.Info("查询历史记录SQL", "@SQL:"+lsSql2);
            }
            else
            {
                string sql = sql1 + sql2 + sql3;
                lsSql2 = "";
                sqld = $@"/*dialect*/select * from (" + sql2 + sql3;
                DBUtils.Execute(this.Context, sql);
            }
            
            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("工时", new Kingdee.BOS.LocaleValue("工时(小时)"), SqlStorageType.SqlDecimal);
            header.AddChild("分摊比例2", new Kingdee.BOS.LocaleValue("分摊比例"));
            header.AddChild("是否分摊", new Kingdee.BOS.LocaleValue("是否分摊"));
            if (Type == null)
            {

                header.AddChild("F_LQKJ_FW", new Kingdee.BOS.LocaleValue("房屋租金"), SqlStorageType.SqlDecimal);
                header.AddChild("F_LQKJ_SD", new Kingdee.BOS.LocaleValue("水电费"), SqlStorageType.SqlDecimal);
                header.AddChild("F_LQKJ_SS", new Kingdee.BOS.LocaleValue("宿舍租金"), SqlStorageType.SqlDecimal);
                header.AddChild("F_LQKJ_WY", 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);
            }
            else
            {
                foreach (string item in Type)
                {
                    switch (item)
                    {
                        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_GS", 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_SY", 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_FL", 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_CL", new Kingdee.BOS.LocaleValue("差旅费"), SqlStorageType.SqlDecimal);
                            break;
                        case "出差补助":
                            header.AddChild("F_LQKJ_CCBZ", 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_FW", new Kingdee.BOS.LocaleValue("房屋租金"), SqlStorageType.SqlDecimal);
                            break;
                        case "水电费":
                            header.AddChild("F_LQKJ_SD", 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_WX", 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_YF", 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_ZD", 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;
                    }
                }
                Type = null;
            }

            return header;
        }
        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)//汇总列构造
        {
            var result = base.GetSummaryColumnInfo(filter);
            result.Add(new SummaryField("工时", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_GZ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_FW", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_SD", 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_JJ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_GS", 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_YLBX", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_ZF", 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_BG", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("F_LQKJ_CL", 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_CCBZ", 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_YF", 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);
        }
    }
}
