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

namespace LQKJ.K3.NDR.NewReportPlugIn
{
    [HotUpdate,Description("费用汇总表")]
   public class CostSummaryReportPlugIn: SysReportBaseService
    {
       
        private string[] Type = null; public static DataTable KMLB = null;
        public override void Initialize()
        {
            this.ReportProperty.ReportName = new Kingdee.BOS.LocaleValue("费用汇总表", base.Context.LogLocale.LCID);
            ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;//简单账表
            this.ReportProperty.IsUIDesignerColumns = false;
            string getKemu = @"select FDATAVALUE from T_BAS_ASSISTANTDATA  a
                                left join T_BAS_ASSISTANTDATAENTRY b on a.FID=b.FID
                                left join T_BAS_ASSISTANTDATAENTRY_L c on c.FENTRYID=b.FENTRYID
                                where a.FID = '645da94cc40111'";
            KMLB = DBUtils.ExecuteDataSet(this.Context, getKemu).Tables[0];
            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_MulCombo"].ToString();
            string year = dyFilter["F_LQKJ_Text"].ToString();
            var Name = string.Empty;
            DynamicObject Product = dyFilter["F_LQKJ_Item"] as DynamicObject;
            DynamicObject CPX = dyFilter["F_LQKJ_CPX"] as DynamicObject;
            DynamicObjectCollection val2 = dyFilter["F_LQKJ_KEMU"] as DynamicObjectCollection;
            string YEAR1 = dyFilter["F_LQKJ_Combo11"].ToString();//会计年度
            string YEAR2 = dyFilter["F_LQKJ_Combo1"].ToString();//会计年度2
            string QIJIAN1 = dyFilter["F_LQKJ_Combo21"].ToString();//期间
            string QIJIAN2 = dyFilter["F_LQKJ_Combo2"].ToString();//期间2
            if (val2.Count != 0)
            {
                DynamicObject val3 = null, 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 year = dyFilter["F_LQKJ_Combo"].ToString();
            //if (!val.IsNullOrEmptyOrWhiteSpace())
            //{
            //    Type = dyFilter["F_LQKJ_MulCombo"].ToString().Split(',');
            //}
            StringBuilder stringBuilder = new StringBuilder();
            string seqFld = string.Format(base.KSQL_SEQ, " T.项目编号 ");// 排序列不写不显示数据
            string firstSql = $"/*dialect*/ select T.*,T1.FDEBIT,{seqFld} into {tableName} from ( select FPERIOD,月份, 产品线, 项目编号,sum(主营业务成本) 主营业务成本,sum(主营业务收入) 主营业务收入,sum(其他业务支出)其他业务支出,sum(其他业务收入)其他业务收入 ";
            stringBuilder.Append(firstSql);
            string SumSql = "";
            for (int i = 0; i < KMLB.Rows.Count; i++)
            {
                string ConSql = $",sum({KMLB.Rows[i]["FDATAVALUE"].ToString()}) {KMLB.Rows[i]["FDATAVALUE"].ToString()}";
                 SumSql += $"sum(isnull({KMLB.Rows[i]["FDATAVALUE"].ToString()},0))+";
                stringBuilder.Append(ConSql);
            }
            SumSql = ","+SumSql.Substring(0,SumSql.Length-1) +"as 总金额";
            stringBuilder.Append(SumSql);
            stringBuilder.Append(" from (select convert(varchar(6), FDATE, 112) 月份, h.F_PJXX_TEXT 产品线, h.FNUMBER 项目编号, FPERIOD ");
            for (int i = 0; i < KMLB.Rows.Count; i++)
            {
                string ConSql = $",case  when e.FFULLNAME  like  '%{KMLB.Rows[i]["FDATAVALUE"].ToString()}%' then FDEBIT end as {KMLB.Rows[i]["FDATAVALUE"].ToString()}";
                stringBuilder.Append(ConSql);
            }
            string EndSql = @",case  when e.FFULLNAME  like  '%主营业务成本%' then FDEBIT end as  主营业务成本,case  when e.FFULLNAME like '%其他业务支出%' then FDEBIT end as  其他业务支出
                              ,case  when e.FFULLNAME  like  '%主营业务收入%' then FDEBIT end as  主营业务收入,case  when e.FFULLNAME like '%其他业务收入%' then FDEBIT end as  其他业务收入
                                        from T_GL_VOUCHER a left join T_GL_VOUCHERENTRY b 
										on a.FVOUCHERID=b.FVOUCHERID left join T_BD_ACCOUNT c on b.FACCOUNTID=c.FACCTID --凭证关联科目 
										left join T_BD_ACCOUNT_L e on c.FACCTID=e.FACCTID left join T_BD_FLEXITEMDETAILV f on b.FDETAILID=f.FID 
										left join T_BAS_PREBDONE h on h.FID=f.FF100003
										left join T_BD_FLEXITEMGROUP_L I on c.FITEMDETAILID=I.FID
										where I.FNAME like '%项目%'  and (e.FFULLNAME like '研发费用_%' or e.FFULLNAME like '制造费用_%' or e.FFULLNAME like '管理费用_%' or e.FFULLNAME like '销售费用_%' or e.FFULLNAME like '%主营业务收入%' or e.FFULLNAME like '%其他业务收入%' or e.FFULLNAME like '%主营业务成本%' or e.FFULLNAME like '%其他业务支出%')
										)T group by 项目编号,产品线,月份,FPERIOD
										) T left join (
										select sum(FDEBIT)FDEBIT,FNUMBER from(
									select  FDEBIT,isnull(h.FNUMBER,'00000')FNUMBER   from T_GL_VOUCHER a 
										left join T_GL_VOUCHERENTRY b on a.FVOUCHERID=b.FVOUCHERID
										left join T_BD_ACCOUNT c on b.FACCOUNTID=c.FACCTID 
										left join T_BD_FLEXITEMDETAILV f on b.FDETAILID=f.FID
										left join T_BAS_PREBDONE h on h.FID=f.FF100003 
										)T group by FNUMBER
                                        ) T1 on T.项目编号=T1.FNUMBER
                                    where 总金额+isnull(其他业务支出,0)+isnull( 主营业务成本,0)+isnull(主营业务收入,0)+isnull(其他业务收入,0)!=0";
            stringBuilder.Append(EndSql);
            string sql = stringBuilder.ToString();

            if (YEAR1 != "" && YEAR2 !="")
            {
                sql += $@" and substring(月份,1,4) between {YEAR1} and {YEAR2}";
            }
            if (QIJIAN1 != "" && QIJIAN2 != "")
            {
                sql += $@" and FPERIOD between {QIJIAN1} and {QIJIAN2}";
            }
            if (CPX != null)
            {
                Name = ((LocaleValue)CPX["FDataValue"]).ToString();
                sql += $" and 产品线='{Name}'";
            }
            string Item = string.Empty;
            if (Product != null)
            {
                Item = Product["Number"].ToString();
                sql += $" and 项目编号='{Item}'";
            }
            Logger.Info("费用汇总表", sql);
            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("月份"),Kingdee.BOS.SqlStorageType.SqlnText).Index=0;
            header.AddChild("产品线", new Kingdee.BOS.LocaleValue("产品线"));
            header.AddChild("项目编号", new Kingdee.BOS.LocaleValue("项目编号"));
            header.AddChild("主营业务成本", new Kingdee.BOS.LocaleValue("主营业务成本"), SqlStorageType.SqlDecimal);
            header.AddChild("主营业务收入", new Kingdee.BOS.LocaleValue("主营业务收入"), SqlStorageType.SqlDecimal);
            header.AddChild("其他业务支出", new Kingdee.BOS.LocaleValue("其他业务支出"), SqlStorageType.SqlDecimal);
            header.AddChild("其他业务收入", new Kingdee.BOS.LocaleValue("其他业务收入"), SqlStorageType.SqlDecimal);
            if (Type != null)
            {
                foreach (string item in Type)
                {
                    header.AddChild(item, new Kingdee.BOS.LocaleValue(item), SqlStorageType.SqlDecimal);
                    //switch (item)
                    //{
                    //    case "工资":
                    //        header.AddChild("工资", new Kingdee.BOS.LocaleValue("工资"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "奖金":
                    //        header.AddChild("奖金", new Kingdee.BOS.LocaleValue("奖金"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "工伤保险费":
                    //        header.AddChild("工伤保险费", new Kingdee.BOS.LocaleValue("工伤保险费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "生育保险费":
                    //        header.AddChild("生育保险费", new Kingdee.BOS.LocaleValue("生育保险费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "失业保险费":
                    //        header.AddChild("失业保险费", new Kingdee.BOS.LocaleValue("失业保险费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "养老保险费":
                    //        header.AddChild("养老保险费", new Kingdee.BOS.LocaleValue("养老保险费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "医疗保险费":
                    //        header.AddChild("医疗保险费", new Kingdee.BOS.LocaleValue("医疗保险费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "住房公积金":
                    //        header.AddChild("住房公积金", new Kingdee.BOS.LocaleValue("住房公积金"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "福利费":
                    //        header.AddChild("福利费", new Kingdee.BOS.LocaleValue("福利费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "办公费":
                    //        header.AddChild("办公费", new Kingdee.BOS.LocaleValue("办公费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "差旅费":
                    //        header.AddChild("差旅费", new Kingdee.BOS.LocaleValue("差旅费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "出差补助":
                    //        header.AddChild("出差补助", new Kingdee.BOS.LocaleValue("出差补助"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "低值易耗品":
                    //        header.AddChild("低值易耗品", new Kingdee.BOS.LocaleValue("低值易耗品"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "电信费":
                    //        header.AddChild("电信费", new Kingdee.BOS.LocaleValue("电信费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "房屋租金":
                    //        header.AddChild("房屋租金", new Kingdee.BOS.LocaleValue("房屋租金"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "水电费":
                    //        header.AddChild("水电费", new Kingdee.BOS.LocaleValue("水电费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "宿舍租金":
                    //        header.AddChild("宿舍租金", new Kingdee.BOS.LocaleValue("宿舍租金"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "维修服务费":
                    //        header.AddChild("维修服务费", new Kingdee.BOS.LocaleValue("维修服务费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "物业管理费":
                    //        header.AddChild("物业管理费", new Kingdee.BOS.LocaleValue("物业管理费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "研发材料费":
                    //        header.AddChild("研发材料费", new Kingdee.BOS.LocaleValue("研发材料费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "邮递费":
                    //        header.AddChild("邮递费", new Kingdee.BOS.LocaleValue("邮递费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "招待费":
                    //        header.AddChild("招待费", new Kingdee.BOS.LocaleValue("招待费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "折旧费":
                    //        header.AddChild("折旧费", new Kingdee.BOS.LocaleValue("折旧费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //    case "装修服务费":
                    //        header.AddChild("装修服务费", new Kingdee.BOS.LocaleValue("装修服务费"), SqlStorageType.SqlDecimal);
                    //        break;
                    //}
                }
                Type = null;
            }
            else
            {
                for (int i = 0; i < KMLB.Rows.Count; i++)
                {
                    
                    header.AddChild(KMLB.Rows[i]["FDATAVALUE"].ToString(), new Kingdee.BOS.LocaleValue(KMLB.Rows[i]["FDATAVALUE"].ToString()), SqlStorageType.SqlDecimal);
                }
                //header.AddChild("工资", new Kingdee.BOS.LocaleValue("工资"), SqlStorageType.SqlDecimal);
                //header.AddChild("奖金", new Kingdee.BOS.LocaleValue("奖金"), SqlStorageType.SqlDecimal);
                //header.AddChild("工伤保险费", new Kingdee.BOS.LocaleValue("工伤保险费"), SqlStorageType.SqlDecimal);
                //header.AddChild("生育保险费", new Kingdee.BOS.LocaleValue("生育保险费"), SqlStorageType.SqlDecimal);
                //header.AddChild("失业保险费", new Kingdee.BOS.LocaleValue("失业保险费"), SqlStorageType.SqlDecimal);
                //header.AddChild("养老保险费", new Kingdee.BOS.LocaleValue("养老保险费"), SqlStorageType.SqlDecimal);
                //header.AddChild("医疗保险费", new Kingdee.BOS.LocaleValue("医疗保险费"), SqlStorageType.SqlDecimal);
                //header.AddChild("住房公积金", new Kingdee.BOS.LocaleValue("住房公积金"), SqlStorageType.SqlDecimal);
                //header.AddChild("福利费", new Kingdee.BOS.LocaleValue("福利费"), SqlStorageType.SqlDecimal);
                //header.AddChild("办公费", new Kingdee.BOS.LocaleValue("办公费"), SqlStorageType.SqlDecimal);
                //header.AddChild("差旅费", new Kingdee.BOS.LocaleValue("差旅费"), SqlStorageType.SqlDecimal);
                //header.AddChild("出差补助", new Kingdee.BOS.LocaleValue("出差补助"), SqlStorageType.SqlDecimal);
                //header.AddChild("低值易耗品", new Kingdee.BOS.LocaleValue("低值易耗品"), SqlStorageType.SqlDecimal);
                //header.AddChild("电信费", new Kingdee.BOS.LocaleValue("电信费"), SqlStorageType.SqlDecimal);
                //header.AddChild("房屋租金", new Kingdee.BOS.LocaleValue("房屋租金"), SqlStorageType.SqlDecimal);
                //header.AddChild("水电费", new Kingdee.BOS.LocaleValue("水电费"), SqlStorageType.SqlDecimal);
                //header.AddChild("宿舍租金", new Kingdee.BOS.LocaleValue("宿舍租金"), SqlStorageType.SqlDecimal);
                //header.AddChild("维修服务费", new Kingdee.BOS.LocaleValue("维修服务费"), SqlStorageType.SqlDecimal);
                //header.AddChild("物业管理费", new Kingdee.BOS.LocaleValue("物业管理费"), SqlStorageType.SqlDecimal);
                //header.AddChild("研发材料费", new Kingdee.BOS.LocaleValue("研发材料费"), SqlStorageType.SqlDecimal);
                //header.AddChild("邮递费", new Kingdee.BOS.LocaleValue("邮递费"), SqlStorageType.SqlDecimal);
                //header.AddChild("招待费", new Kingdee.BOS.LocaleValue("招待费"), SqlStorageType.SqlDecimal);
                //header.AddChild("折旧费", new Kingdee.BOS.LocaleValue("折旧费"), SqlStorageType.SqlDecimal);
                //header.AddChild("装修服务费", new Kingdee.BOS.LocaleValue("装修服务费"), SqlStorageType.SqlDecimal);
            }
            return header;
        }
        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);
        }
    }
}
