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

namespace LQKJ.K3.NDR.NewReportPlugIn
{
    [HotUpdate, Description("部门汇总工时分摊简单张表插件")]
    public class DepartmentSummaryReportPlugIn : SysReportBaseService
    {
        private string[] Deps = null;
        private string[] Names = null;
        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;
            //this.ReportProperty.PrimaryKeyFieldName = "FCUSTOMERID";主键字段
            //List<DecimalControlField> list = new List<DecimalControlField>();
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "NPI",
            //    DecimalControlFieldName = "JinDu"
            //});
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName= "KCL",
            //    DecimalControlFieldName = "JinDu"
            //});
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "KCD",
            //    DecimalControlFieldName = "JinDu"
            //});
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "PHONE",
            //    DecimalControlFieldName = "JinDu"
            //});
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "ZL",
            //    DecimalControlFieldName = "JinDu"
            //});
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "JS",
            //    DecimalControlFieldName = "JinDu"
            //});
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "CP",
            //    DecimalControlFieldName = "JinDu"
            //});
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "MRK",
            //    DecimalControlFieldName = "JinDu"
            //});
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "JC",
            //    DecimalControlFieldName = "JinDu"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
            base.Initialize();
        }
        public override string GetTableName()
        {
            return base.GetTableName();
        }
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)//构造表体
        {
            string seqFld = string.Format(base.KSQL_SEQ, " T.FNUMBER ");// 排序列不写不显示数据
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            var keySet = dyFilter.DynamicObjectType.Properties;//过滤框的所有属性
            DynamicObjectCollection ProductsName = dyFilter["F_LQKJ_DEP"] as DynamicObjectCollection;
            DynamicObjectCollection Items = dyFilter["F_LQKJ_NUMBER"] as DynamicObjectCollection;
            string YM = dyFilter["F_LQKJ_YM"].ToString();//年月
            List<string> s1 = new List<string>();
             List<string> s2 = new List<string>();
            if (ProductsName.Count() != 0)
            {
                foreach (DynamicObject item1 in ProductsName)//部门多选取值
                {
                    var i = item1[1].ToString();//这个可以拿到ID
                    s1.Add(i);
                    var i2 = item1[2] as DynamicObject;
                    var i3 = i2[3].ToString();//这个可以拿到文本值
                    s2.Add(i3);
                }
                Deps = s1.ToArray();
                Names = s2.ToArray();
                s1.Clear();
                s2.Clear();
            }
            else
            {
                Deps = null;
                Names = null;
            }
            //DynamicObject State = dyFilter["F_LQKJ_Combo"] as DynamicObject;
            //string val = dyFilter["F_LQKJ_Combo"].ToString();
            string sql = $@"/*dialect*/select *,{seqFld} into {tableName} from ( select FNUMBER,case F_LQKJ_COMBO when '0' then '分摊' else '不分摊' end as STATE";
            if (Deps == null)
            {
                sql += $@",case F_LQKJ_COMBO when '0' then isnull(F_LQKJ_WORKTIME2,0) else 0 END as NPI,
                                                        case F_LQKJ_COMBO when '0' then isnull(F_LQKJ_WORKTIME3,0) else 0 END as KCL,case F_LQKJ_COMBO when '0' then isnull(F_LQKJ_WORKTIME4,0) else 0 END as KCD
                                                        ,case F_LQKJ_COMBO when '0' then isnull(F_LQKJ_WORKTIME5,0) else 0 END as PHONE,case F_LQKJ_COMBO when '0' then isnull(F_LQKJ_WORKTIME6,0) else 0 END as ZL
                                                        ,case F_LQKJ_COMBO when '0' then isnull(F_LQKJ_WORKTIME7,0) else 0 END as JS,case F_LQKJ_COMBO when '0' then isnull(F_LQKJ_WORKTIME8,0) else 0 END as CP
                                                        ,case F_LQKJ_COMBO when '0' then isnull(F_LQKJ_WORKTIME9,0) else 0 END as MRK,case F_LQKJ_COMBO when '0' then isnull(F_LQKJ_WORKTIME10,0) else 0 END as JC
                                                        ,isnull(F_LQKJ_WORKTIME2,0)+isnull(F_LQKJ_WORKTIME3,0)+isnull(F_LQKJ_WORKTIME4,0)+isnull(F_LQKJ_WORKTIME5,0)+isnull(F_LQKJ_WORKTIME6,0)+isnull(F_LQKJ_WORKTIME7,0)+isnull(F_LQKJ_WORKTIME8,0)+isnull(F_LQKJ_WORKTIME9,0)+isnull(F_LQKJ_WORKTIME10,0) as 项目总工时
                                                        from (
                                                            T_BAS_PREBDONE k left join
                                                        (
														select t1.F_LQKJ_WORKTIME1+isnull(t.F_LQKJ_WORKTIME1,0) F_LQKJ_WORKTIME1,t1.F_LQKJ_FITEM from (
														(select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME1
														from LQKJ_T_PROJECTHOURS where F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}' group by F_LQKJ_FITEM ) t1--根据项目来汇总工时 
														left join
														(select sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME1,F_LQKJ_FITEM from LQKJ_t_Cust100010 a left join LQKJ_WORKTIMESUMMARY b 
														on a.FID=b.FID where FDOCUMENTSTATUS='C' and FYEARANDMONTH='{YM}' group by F_LQKJ_FITEM) t on t.F_LQKJ_FITEM=t1.F_LQKJ_FITEM ) )
														 a on a.F_LQKJ_FITEM=k.FID
                                                        left join 
                                                        (select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME2 from LQKJ_T_PROJECTHOURS
                                                        where F_LQKJ_FDEPARTMENT='100137' and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}'
                                                        group by F_LQKJ_FITEM ) b--NPI部门每个项目的总工时
                                                        on a.F_LQKJ_FITEM=b.F_LQKJ_FITEM
                                                        left join 
                                                        (select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME3 from LQKJ_T_PROJECTHOURS
                                                        where F_LQKJ_FDEPARTMENT='100130' and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}'
                                                        group by F_LQKJ_FITEM ) c--可测量产品线部门每个项目的总工时
                                                        on a.F_LQKJ_FITEM=c.F_LQKJ_FITEM
                                                        left join 
                                                        (select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME4 from LQKJ_T_PROJECTHOURS
                                                        where F_LQKJ_FDEPARTMENT='100129' and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}'
                                                        group by F_LQKJ_FITEM ) d--可穿戴产品线部门每个项目的总工时
                                                        on a.F_LQKJ_FITEM=d.F_LQKJ_FITEM
                                                        left join 
                                                        (select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME5 from LQKJ_T_PROJECTHOURS
                                                        where F_LQKJ_FDEPARTMENT='100128' and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}'
                                                        group by F_LQKJ_FITEM ) e--手机产品线部门每个项目的总工时
                                                        on a.F_LQKJ_FITEM=e.F_LQKJ_FITEM
                                                        left join 
                                                        (
														select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME6 from LQKJ_T_PROJECTHOURS
                                                        where F_LQKJ_FDEPARTMENT='100140' and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}'
                                                        group by F_LQKJ_FITEM
														) f --质量部每个项目的总工时
                                                        on a.F_LQKJ_FITEM=f.F_LQKJ_FITEM
                                                        left join 
                                                        (select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME7 from LQKJ_T_PROJECTHOURS
                                                        where F_LQKJ_FDEPARTMENT='100135' and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}'
                                                        group by F_LQKJ_FITEM ) g--技术委员会每个项目的总工时
                                                        on a.F_LQKJ_FITEM=g.F_LQKJ_FITEM
                                                        left join 
                                                        (select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME8 from LQKJ_T_PROJECTHOURS
                                                        where F_LQKJ_FDEPARTMENT='100131' and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}'
                                                        group by F_LQKJ_FITEM ) h--产品工程部每个项目的总工时
                                                        on a.F_LQKJ_FITEM=h.F_LQKJ_FITEM
                                                        left join 
                                                        (select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME9 from LQKJ_T_PROJECTHOURS
                                                        where F_LQKJ_FDEPARTMENT='100134' and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}'
                                                        group by F_LQKJ_FITEM ) I--战略Mrketing每个项目的总工时
                                                        on a.F_LQKJ_FITEM=I.F_LQKJ_FITEM
                                                        left join 
                                                        (select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME10 from LQKJ_T_PROJECTHOURS
                                                        where F_LQKJ_FDEPARTMENT='100133' and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}'
                                                        group by F_LQKJ_FITEM ) j--基础材料部每个项目的总工时
                                                        on a.F_LQKJ_FITEM=j.F_LQKJ_FITEM
                                                        ) 
                                                        ) T where 项目总工时 is not null and 项目总工时>0";
            }
            else
            {
                string sql3 = "";
                for(int i = 0; i < Names.Length; i++)
                {
                    int i2 = i + 2;
                    sql += $@",case F_LQKJ_COMBO when '0' then isnull(F_LQKJ_WORKTIME{i2},0) else 0 END as {Names[i].ToString()}";
                    sql3 += $@"isnull(F_LQKJ_WORKTIME{i2},0)+";
                }
                sql3 = ","+sql3.Remove(sql3.LastIndexOf('+'), 1)+" as 项目总工时 ";
                sql += sql3;
                sql += $@"                                from (
                                                                    T_BAS_PREBDONE k left join
                                                        (
														select t1.F_LQKJ_WORKTIME1+isnull(t.F_LQKJ_WORKTIME1,0) F_LQKJ_WORKTIME1,t1.F_LQKJ_FITEM from (
														(select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME1
														from LQKJ_T_PROJECTHOURS where F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}' group by F_LQKJ_FITEM ) t1
														left join
														(select sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME1,F_LQKJ_FITEM from LQKJ_t_Cust100010 a left join LQKJ_WORKTIMESUMMARY b 
														on a.FID=b.FID where FDOCUMENTSTATUS='C' and FYEARANDMONTH='{YM}' group by F_LQKJ_FITEM) t on t.F_LQKJ_FITEM=t1.F_LQKJ_FITEM ) )
														 a on a.F_LQKJ_FITEM=k.FID";
                for(int i = 0; i < Deps.Length; i++)
                {
                    sql += $@"                          left join 
                                                        (select t1.F_LQKJ_FITEM,t1.F_LQKJ_WORKTIME  --+isnull(t.F_LQKJ_WORKTIME1,0) 
                                                            F_LQKJ_WORKTIME{i + 2} from
                                                        (
                                                        select F_LQKJ_FITEM,sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME from LQKJ_T_PROJECTHOURS
                                                        where F_LQKJ_FDEPARTMENT='{Deps[i].ToString()}' and F_LQKJ_BILLSTATUS='C' and F_LQKJ_CHECKBOX=0 and convert(varchar(6),F_LQKJ_DATE2,112)='{YM}'
                                                        group by F_LQKJ_FITEM ) t1
                                                        --left join (select sum(F_LQKJ_WORKTIME) F_LQKJ_WORKTIME1,F_LQKJ_FITEM from LQKJ_t_Cust100010 a left join LQKJ_WORKTIMESUMMARY b 
														--on a.FID=b.FID where FDOCUMENTSTATUS='C' and F_LQKJ_DEPARTMENT='{Deps[i].ToString()}' and FYEARANDMONTH='{YM}' group by F_LQKJ_FITEM) t on t.F_LQKJ_FITEM=t1.F_LQKJ_FITEM
														) {'a'+i.ToString()}
                                                        on a.F_LQKJ_FITEM={'a' + i.ToString()}.F_LQKJ_FITEM";
                }
                sql += $@"                              
                                                        ) 
                                                        ) T where 项目总工时 is not null and 项目总工时>0  ";
            }
            if (Items.Count() != 0  )//项目编号
            {
                if(Items.Count() ==1)//1行的时候
                {
                    foreach (DynamicObject item in Items)
                    {
                        var i2 = item[2] as DynamicObject;
                        var i3 = i2[4].ToString();//这个可以拿到文本值
                        sql += $@"  and T.FNUMBER='{i3}' ";
                    }
                }
                else
                {
                    sql += $@" and (T.FNUMBER='1'";
                    foreach (DynamicObject item in Items)
                    {
                        var i2 = item[2] as DynamicObject;
                        var i3 = i2[4].ToString();//这个可以拿到文本值
                        sql += $@" or T.FNUMBER='{i3}' ";
                    }
                    sql += $@" )";
                }
            }
            DBUtils.ExecuteDynamicObject(this.Context, sql);
            base.BuilderReportSqlAndTempTable(filter, tableName);
        }
        public override ReportHeader GetReportHeaders(IRptParams filter)//构造表头
        {
            ReportHeader header = new ReportHeader();
            header.AddChild("FNUMBER", new Kingdee.BOS.LocaleValue("项目编号")).ColIndex = 0;
            header.AddChild("STATE", new Kingdee.BOS.LocaleValue("是否分摊")).ColIndex = 1;
            if (Deps == null)
            {
                header.AddChild("NPI", new Kingdee.BOS.LocaleValue("各部门工时(小时)&NPI"), SqlStorageType.SqlDecimal).ColIndex = 2;
                header.AddChild("KCL", new Kingdee.BOS.LocaleValue("各部门工时(小时)&可测量产品线"), SqlStorageType.SqlDecimal).ColIndex = 3;
                header.AddChild("KCD", new Kingdee.BOS.LocaleValue("各部门工时(小时)&可穿戴产品线"), SqlStorageType.SqlDecimal).ColIndex = 4;
                header.AddChild("PHONE", new Kingdee.BOS.LocaleValue("各部门工时(小时)&手机产品线"), SqlStorageType.SqlDecimal).ColIndex = 5;
                header.AddChild("ZL", new Kingdee.BOS.LocaleValue("各部门工时(小时)&质量部"), SqlStorageType.SqlDecimal).ColIndex = 6;
                header.AddChild("JS", new Kingdee.BOS.LocaleValue("各部门工时(小时)&技术委员会"), SqlStorageType.SqlDecimal).ColIndex = 7;
                header.AddChild("CP", new Kingdee.BOS.LocaleValue("各部门工时(小时)&产品工程部"), SqlStorageType.SqlDecimal).ColIndex = 8;
                header.AddChild("MRK", new Kingdee.BOS.LocaleValue("各部门工时(小时)&战略Mrketing"), SqlStorageType.SqlDecimal).ColIndex = 9;
                header.AddChild("JC", new Kingdee.BOS.LocaleValue("各部门工时(小时)&基础材料部"), SqlStorageType.SqlDecimal).ColIndex = 10;
            }
            else
            {
                for(int i = 0; i < Names.Length; i++)
                {
                    header.AddChild(Names[i].ToString(), new Kingdee.BOS.LocaleValue("各部门工时(小时)&"+Names[i].ToString()), SqlStorageType.SqlDecimal);
                }
            }
            header.AddChild("项目总工时", new Kingdee.BOS.LocaleValue("各部门工时(小时)&项目总工时"), SqlStorageType.SqlDecimal);
            return header;
        }
        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)//汇总列构造
        {
            var result = base.GetSummaryColumnInfo(filter);
            if (Deps == null)
            {
                result.Add(new SummaryField("NPI", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
                result.Add(new SummaryField("KCL", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
                result.Add(new SummaryField("KCD", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
                result.Add(new SummaryField("PHONE", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
                result.Add(new SummaryField("ZL", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
                result.Add(new SummaryField("JS", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
                result.Add(new SummaryField("CP", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
                result.Add(new SummaryField("MRK", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
                result.Add(new SummaryField("JC", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            }
            else
            {
                for(int i = 0; i < Names.Length; i++)
                {
                    result.Add(new SummaryField(Names[i].ToString(), 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);
        }
    }
}
