using Demo.BillView.PRD;
using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Core.SqlBuilder;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text;

namespace Demo.Report.PRD
{
    [Description("提料计划单简单账表(采购订单维度)")]
    [HotUpdate]
    /// <summary>
    /// 提料计划单简单账表
    /// </summary>
    public class prd_ExtractionPlanDetailPurOrder : SysReportBaseService
    {
        string date = "";
        string TableName = "";
        //初始化
        public override void Initialize()
        {
            base.Initialize();
            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.IsCreateTempTableByPlugin = true;
            //取代码中配置的列
            this.ReportProperty.IsUIDesignerColumns = false;
            //是否分组汇总
            this.ReportProperty.IsGroupSummary = true;
        }
        //组织名称
        private string GetMultiOrgnNameValues(string orgIdStrings)
        {
            List<string> list = new List<string>();
            string result = string.Empty;
            if (orgIdStrings.Trim().Length > 0)
            {
                IQueryService service = Kingdee.BOS.Contracts.ServiceFactory.GetService<IQueryService>(base.Context);
                QueryBuilderParemeter para = new QueryBuilderParemeter
                {
                    FormId = "ORG_Organizations",
                    SelectItems = SelectorItemInfo.CreateItems("FNAME"),
                    FilterClauseWihtKey = string.Format(" FORGID IN ({0}) AND FLOCALEID={1}", orgIdStrings, base.Context.UserLocale.LCID)
                };
                DynamicObjectCollection dynamicObjectCollection = service.GetDynamicObjectCollection(base.Context, para, null);
                foreach (DynamicObject current in dynamicObjectCollection)
                {
                    list.Add(current["FNAME"].ToString());
                }
                if (list.Count > 0)
                {
                    result = string.Join(",", list.ToArray());
                }
            }
            return result;
        }

        /*
        //获取过滤条件信息(构造单据信息)
        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            ReportTitles reportTitles = new ReportTitles();
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            if (customFilter != null)
            {
                string multiOrgnNameValues = this.GetMultiOrgnNameValues(customFilter["F_PAEZ_OrgId"].ToString());
                string startValue = (customFilter["FOrderStartDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FOrderStartDate"]).ToString("yyyy-MM-dd");
                string endValue = (customFilter["FOrderEndDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FOrderEndDate"]).ToString("yyyy-MM-dd");
                string BillNo = (Convert.ToString(customFilter["F_PoOrderBillNo"]) == "") ? string.Empty : customFilter["F_PoOrderBillNo"].ToString();
                string baseDataNameValue = this.GetBaseDataNameValue(customFilter["F_PAEZ_WLID"] as DynamicObjectCollection);
                string baseDataNameValue3 = this.GetBaseDataNameValue(customFilter["F_PAEZ_SuppId"] as DynamicObjectCollection);
                reportTitles.AddTitle("F_PAEZ_Org", multiOrgnNameValues);
                reportTitles.AddTitle("F_PAEZ_Date", startValue + "-" + endValue);
                reportTitles.AddTitle("F_PAEZ_BillNo", BillNo);
                reportTitles.AddTitle("F_PAEZ_WLName", baseDataNameValue);
                reportTitles.AddTitle("F_PAEZ_GYS", baseDataNameValue3);
            }
            return reportTitles;
        }
        
          */
        //基础资料名称

        //设置单据列
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {

            //更新总拖期数量 和 总提料数量
            List<string> sqlList = new List<string>();
            string sql = string.Format(@"/*dialect*/ update a set a.TQSum = b.FHQTY from {0} a
            join (
            select SUM(b.FHQTY)FHQTY, FIDENTITYID from {0} c
            join Cg_PODemandPlanBillMain a  on c.FSUPPLIERID = a.FSUPPLIERID and c.FHPURCHASEORGID = a.FHPURCHASEORGID
            join(select SUM(FHQTY)FHQTY, FHMATERID, FID from Cg_PODemandPlanBillSub GROUP BY FHMATERID, FID)  b on a.FID = B.FID and c.FHMATERID = b.FHMATERID
            where {1} and datediff(d, FHDATE, getdate())> 0
            group by FIDENTITYID,FSalOrderNo
            ) b on a.FIDENTITYID = b.FIDENTITYID",
            TableName, date);
            //DBServiceHelper.Execute(Context, sql);
            sqlList.Add(sql);
            sql = string.Format(@"/*dialect*/ update a set a.PlanSum = b.FHQTY from {0} a 
            join (
            select SUM(b.FHQTY)FHQTY,FIDENTITYID from {0} c
            join   Cg_PODemandPlanBillMain a  on c.FSUPPLIERID = a.FSUPPLIERID and c.FHPURCHASEORGID = a.FHPURCHASEORGID
            join   (select SUM(FHQTY)FHQTY, FHMATERID, FID from Cg_PODemandPlanBillSub GROUP BY FHMATERID,FID)  b on a.FID = B.FID and c.FHMATERID = b.FHMATERID
            where {1} and  datediff(d,FHDATE,getdate())<=0  
            group by FIDENTITYID,FSalOrderNo
            ) b on a.FIDENTITYID=b.FIDENTITYID",
            TableName, date);
            //DBServiceHelper.Execute(Context, sql);
            sqlList.Add(sql);
            sql = string.Format(@"/*dialect*/ update {0} set 
FSumQty = b.FSumQty
from {0} a
join (select FSUPPLIERID,FHPURCHASEORGID,sum(isnull(TQSum,0)+isnull(PlanSum,0))FSumQty from {0} group by FSUPPLIERID,FHPURCHASEORGID) b
on a.FSUPPLIERID = b.FSUPPLIERID and a.FHPURCHASEORGID = b.FHPURCHASEORGID
where a.FHMATERID=0", TableName);
            sqlList.Add(sql);
            DBServiceHelper.Execute(Context, string.Join(";", sqlList));

            ReportHeader header = new ReportHeader();
            header.AddChild("FRogNUMBER", new LocaleValue("采购组织", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar).ColIndex = 0;
            header.AddChild("FNAME", new LocaleValue("供应商", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar).ColIndex = 1;
            header.AddChild("FSumQty", new LocaleValue("合计", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar).ColIndex = 2;

            //采购订单号、需求单号（销售订单号），采购订单表头摘要，采购订单表体备注，采购员
            header.AddChild("FHPOOrderBillNo", new LocaleValue("采购订单号", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar).ColIndex = 3;
            header.AddChild("FSalOrderNo", new LocaleValue("需求单号", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar).ColIndex = 4;
            header.AddChild("F_QIMB_NOTE", new LocaleValue("摘要", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar).ColIndex = 5;
            header.AddChild("FNOTE", new LocaleValue("备注", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar).ColIndex = 6;
            header.AddChild("FCgddCreaterNmae", new LocaleValue("采购员", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar).ColIndex = 7;

            header.AddChild("FNUMBER", new LocaleValue("物料编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar).ColIndex = 8;
            header.AddChild("FMATERIALNAME", new LocaleValue("物料名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar).ColIndex = 9;
            header.AddChild("FSPECIFICATION", new LocaleValue("物料规格", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar).ColIndex = 10;
            //header.AddChild("FBASEUNIT", new LocaleValue("单位", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FPROUNIT", new LocaleValue("计量单位", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FQTY", new LocaleValue("库存数量", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FTRANSITQTY", new LocaleValue("在途数量", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);

            header.AddChild("TQSum", new LocaleValue("提料拖期数量", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            DataSet ds = DBServiceHelper.ExecuteDataSet(Context, "/*dialect*/select distinct convert(nvarchar(50),FHDATE,23) FHDATE from Cg_PODemandPlanBillMain where " + date + " and datediff(d,FHDATE,getdate())>0   ");
            DataTable dt = ds.Tables[0];
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    header.AddChild(dr[0].ToString(), new LocaleValue(dr[0].ToString() + "", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
                }
            }
            header.AddChild("PlanSum", new LocaleValue("合计", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            DataSet _ds = DBServiceHelper.ExecuteDataSet(Context, "/*dialect*/select distinct convert(nvarchar(50),FHDATE,23) FHDATE from Cg_PODemandPlanBillMain  where " + date + " and  datediff(d,FHDATE,getdate())<=0");
            dt = _ds.Tables[0];
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    header.AddChild(dr[0].ToString(), new LocaleValue(dr[0].ToString() + "", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
                }
            }
            return header;
        }
        //创建临时报表
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            TableName = tableName;
            string Filter = GetFilterWhere(filter);
            string seqFld = string.Format(base.KSQL_SEQ, OrderColumn(filter));

            // 取数SQL
            // FID, FEntryId, 编号、状态、物料、数量、单位、单位精度、单价、价税合计 ,t1M_L.FNAME as FMaterialName
            string sql = string.Format(@"/*dialect*/exec 提料计划报表2 '{0}','{1}'", tableName, Filter.Trim());
            LogService.Write(sql);
            DBServiceHelper.Execute(Context, sql);

        }
        ////获取过滤条件
        private string GetFilterWhere(IRptParams filter)
        {
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            StringBuilder strwhere = new StringBuilder();
            strwhere.AppendLine("Where 1=1 ");
            //日期
            string startValue = (customFilter["FBeginConveyDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FBeginConveyDate"]).ToString("yyyy/MM/d");
            string endValue = (customFilter["FEndConveyDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FEndConveyDate"]).ToString("yyyy/MM/d");
            strwhere.AppendLine(string.Format(" AND FHDate>=''{0}'' and FHDate<=''{1}'' ", startValue, endValue));
            date = string.Format("  FHDate>='{0}' and FHDate<='{1}' ", startValue, endValue);
            //物料
            var baseDataNameValue = customFilter["FMATERIALID"];
            string MaterailName = baseDataNameValue == null
                ? " " : string.Format(" AND B.FHMATERID IN ({0}) ", (customFilter["FMATERIALID"] as DynamicObject)["Id"].ToString());
            strwhere.AppendLine(MaterailName);
            //组织
            var org = customFilter["FOrgId"] == null
                ? " " : string.Format(" AND FHPURCHASEORGID IN ({0}) ", (customFilter["FOrgId"] as DynamicObject)["Id"].ToString());
            strwhere.AppendLine(org);
            //供应商
            var baseSuppName = customFilter["FSUPPLIERID"];
            string SuppName = baseSuppName == null
                ? " " : string.Format(" AND a.FSUPPLIERID IN ({0}) ", (customFilter["FSUPPLIERID"] as DynamicObject)["Id"].ToString());
            strwhere.AppendLine(SuppName);
            string text2 = string.IsNullOrWhiteSpace(filter.FilterParameter.FilterString) ? " " : " AND " + filter.FilterParameter.FilterString.Replace("{ts", "").Replace("}", "");
            strwhere.AppendLine(text2);
            return strwhere.ToString();
        }
        /// 设置汇总列信息
        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
        {
            var result = base.GetSummaryColumnInfo(filter);
            //result.Add(new SummaryField(dr[0].ToString(), Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            return result;
        }
        ///排序语句
        private string OrderColumn(IRptParams filter)
        {
            string OrderBy = "";
            string datasort = Convert.ToString(filter.FilterParameter.SortString);//排序
            if (datasort != "")
            {
                OrderBy = " " + datasort + " ";
            }
            else
            {
                OrderBy = " a.FID ";
            }
            return OrderBy;
        }
    }
}