using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Runtime.Remoting.Contexts;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Log;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.Util;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Globalization;
using System.Collections;
using System.Configuration;
using LQKJ.LicVerify;

namespace LQKJ.DANGAN.ServicePlugIN
{
    [Description("预约汇总"), HotUpdate]
    public class SummaryAppointments : SysReportBaseService
    {
        public override void Initialize()
        {
            //报表类型:简单报表
            ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.IsUIDesignerColumns = false;
            base.Initialize();

        }
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            if (!LicScheduler.LicVerify())
                throw new Exception("lic过期");
            ReportHeader header = base.GetReportHeaders(filter);
            header.AddChild("FDate", new LocaleValue("&时间", base.Context.UserLocale.LCID));
            header.AddChild("FirmNature", new LocaleValue("&企业性质", base.Context.UserLocale.LCID));
            header.AddChild("FCYCount", new LocaleValue("&总查阅次数", base.Context.UserLocale.LCID));
            header.AddChild("FCYFCount", new LocaleValue("&查阅份数", base.Context.UserLocale.LCID));
            header.AddChild("FXSCYCount", new LocaleValue("&线上预约查阅次数", base.Context.UserLocale.LCID));
            header.AddChild("FXXCYCount", new LocaleValue("&线下未预约查阅次数", base.Context.UserLocale.LCID));
            header.AddChild("FYYFCount", new LocaleValue("&移交份数", base.Context.UserLocale.LCID));
            //header.AddChild("DWName", new LocaleValue("&单位名称", base.Context.UserLocale.LCID));
            return header;
        }
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            IDBService dBService = ServiceFactory.GetDBService(this.Context);
             DynamicObject dynamicFile = filter.FilterParameter.CustomFilter;
             string type= dynamicFile["FType"].ToString();
            string Fdate=DateTime.Now.ToString("yyyy-MM-dd");
            string Fdate1=DateTime.Now.ToString("yyyy-MM-dd");
            string seqFid = string.Format(base.KSQL_SEQ, "FDate Desc");
            if (dynamicFile["FDate"] != null)
            {
                Fdate = DateTime.Parse(dynamicFile["FDate"].ToString()).ToString("yyyy-MM-dd");
            }
            if (dynamicFile["FDate1"] != null)
            {
                Fdate1 = DateTime.Parse(dynamicFile["FDate1"].ToString()).ToString("yyyy-MM-dd");
            }
            string sql;
            if (type == "1")
            {
                sql= string.Format(@"/*dialect*/SELECT FDate, FirmNature,
                                       SUM(CYCount) AS FCYCount, 
                                       SUM(CYFCount) AS FCYFCount, 
                                       SUM(XSCYCount)AS FXSCYCount,
                                       SUM(XXCYCount)AS FXXCYCount,
                                       SUM(YYFCount) AS FYYFCount,
                                                {0} into {1}
                                    FROM (
                                    SELECT
                                    YEAR(CAST(LEFT(FDATETIME1,10) AS DATE)) AS FDate,
                                    t2.FENTERPRISEQUALITY as FirmNature,--企业性质
                                    COUNT(*) AS CYCount,
                                    COUNT(*) AS CYFCount,
                                    SUM(CASE WHEN FNOAPPOINTMENT = '0' AND FHANDLESTATES = 'Y' AND FFORBIDSTATUS = 'A' THEN 1 ELSE 0 END) AS XSCYCount,
                                    SUM(CASE WHEN FNOAPPOINTMENT = '1' AND FHANDLESTATES = 'Y' AND FFORBIDSTATUS = 'A' THEN 1 ELSE 0 END) AS XXCYCount,
                                    0 AS YYFCount
                                    FROM
                                        T_DA_ArchiveAccess t1 left join T_LQKJ_FilesManage t2 on t1.FIDCARD=t2.FIDCARD 
                                    WHERE
                                        FHANDLESTATES = 'Y'
                                        AND FFORBIDSTATUS = 'A'
                                        AND FORMAT(CAST(LEFT(t1.FDATETIME1, 10) AS DATE), 'yyyy-MM-dd') >= '{2}'
                                        AND FORMAT(CAST(LEFT(t1.FDATETIME1, 10) AS DATE), 'yyyy-MM-dd') <= '{3}'
                                    GROUP BY
                                        YEAR(CAST(LEFT(FDATETIME1,10) AS DATE)),t2.FENTERPRISEQUALITY
                                    
                                    UNION ALL
                                    
                                    select
                                    YEAR(FRECEIPTDATE)  AS FDate, 
                                    FENTERPRISEQUALITY as FirmNature,--企业性质
                                               0 AS CYCount, 
                                               0 AS CYFCount,
                                               0 AS XSCYCount,
                                               0 AS XXCYCount,
                                               COUNT(*) AS YYFCount
                                    from  T_LQKJ_FilesManage 
                                    where FDOCUMENTSTATUS='C' and FINVALIDATEDSTATUS='A'
                                    and FORMAT(FRECEIPTDATE, 'yyyy-MM-dd')>='{2}'
                                    and FORMAT(FRECEIPTDATE, 'yyyy-MM-dd')<='{3}'
                                    GROUP BY YEAR(FRECEIPTDATE),FENTERPRISEQUALITY) AS CombinedResults
                                    GROUP BY FDate,FirmNature
                                    ", seqFid, tableName, Fdate,Fdate1);
            }
            else
            {
                sql = string.Format(@"/*dialect*/SELECT FDate, 
                                        		FirmNature,
                                               SUM(CYCount) AS FCYCount, 
                                                SUM(CYFCount) AS FCYFCount, 
                                                SUM(XSCYCount)AS FXSCYCount,
                                                SUM(XXCYCount)AS FXXCYCount,
                                                SUM(YYFCount) AS FYYFCount,
                                                {0} into {1} 
                                        	   FROM (
                                            SELECT
                                            FORMAT(CAST(LEFT(t1.FDATETIME1, 10) AS DATE), 'yyyy-MM-dd') AS FDate,
                                        	t2.FENTERPRISEQUALITY as FirmNature,--企业性质
                                            COUNT(*) AS CYCount,
                                            COUNT(*) AS CYFCount,
                                            SUM(CASE WHEN FNOAPPOINTMENT = '0' AND FHANDLESTATES = 'Y' AND FFORBIDSTATUS = 'A' THEN 1 ELSE 0 END) AS XSCYCount,
                                            SUM(CASE WHEN FNOAPPOINTMENT = '1' AND FHANDLESTATES = 'Y' AND FFORBIDSTATUS = 'A' THEN 1 ELSE 0 END) AS XXCYCount,
                                            0 AS YYFCount
                                        FROM
                                            T_DA_ArchiveAccess t1 left join T_LQKJ_FilesManage t2 on t1.FIDCARD=t2.FIDCARD 
                                        WHERE
                                            FHANDLESTATES = 'Y'
                                            AND FFORBIDSTATUS = 'A'
                                            AND FORMAT(CAST(LEFT(t1.FDATETIME1, 10) AS DATE), 'yyyy-MM-dd') >= '{2}'
                                            AND FORMAT(CAST(LEFT(t1.FDATETIME1, 10) AS DATE), 'yyyy-MM-dd') <= '{3}'
                                        GROUP BY
                                            FORMAT(CAST(LEFT(t1.FDATETIME1, 10) AS DATE), 'yyyy-MM-dd'),t2.FENTERPRISEQUALITY
                                        
                                            UNION ALL
                                        
                                            select
                                        FORMAT(FRECEIPTDATE, 'yyyy-MM-dd')  AS FDate, 
                                        FENTERPRISEQUALITY as FirmNature,--企业性质
                                                   0 AS CYCount, 
                                                   0 AS CYFCount,
                                                   0 AS XSCYCount,
                                                   0 AS XXCYCount,
                                                   COUNT(*) AS YYFCount
                                        from  T_LQKJ_FilesManage 
                                        where FDOCUMENTSTATUS='C' and FINVALIDATEDSTATUS='A'
                                        and FORMAT(FRECEIPTDATE, 'yyyy-MM-dd')>='{2}'
                                        and FORMAT(FRECEIPTDATE, 'yyyy-MM-dd')<='{3}'
                                        GROUP BY FORMAT(FRECEIPTDATE, 'yyyy-MM-dd'),FENTERPRISEQUALITY
                                        ) AS CombinedResults
                                        GROUP BY FDate,FirmNature", seqFid, tableName, Fdate,Fdate1);
            }

            /*//拼接sql语句
            //上级公司客户名
            //公司客户名
            if (!string.IsNullOrWhiteSpace(ClientName))
            {
                sql += "and kh.FNAME like ('%" + ClientName + "%')";
            }
            //订单日期
            if (!string.IsNullOrWhiteSpace(OrderDate))
            {
                sql += " and xs.FDATE in('" + OrderDate + "')";
            }*/
            dBService.Execute(this.Context, sql);
            base.BuilderReportSqlAndTempTable(filter, tableName);
        }

        /// <summary>
        /// 汇总方法
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
        {
            List<SummaryField> summarys = base.GetSummaryColumnInfo(filter);
            summarys.Add(new SummaryField("FCYCount", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            summarys.Add(new SummaryField("FCYFCount", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            summarys.Add(new SummaryField("FXSCYCount", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            summarys.Add(new SummaryField("FXXCYCount", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            summarys.Add(new SummaryField("FYYFCount", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            return summarys;
        }
    }
}
