using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace SYNC_SAP_MES
{
    public class Sql_Biz_SAP
    {

        /// <summary>
        /// 
        /// </summary>
        public static void LoadConfig()
        {
            var sql =
                $" SELECT TOP 1 [AID],[AppServerHost],[SystemNumber],[User],[Password],[Client]" +
                $",[Language],[PoolSize],[IdleTimeout],[Name],[SystemType],[Enable]" +
                $",[IsDemo],[ReachSyncCount],[LineCode]" +
                $" FROM [大柴_接口_SAP登录信息]" +
                $" WHERE [Enable] = 'True'";
            DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out DataTable dt, out string err);
            if (dt.Rows.Count > 0)
            {
                var row = dt.Rows[0];
                PP.AppServerHost = row["AppServerHost"].ToString();
                PP.SystemNumber = row["SystemNumber"].ToString();
                PP.User = row["User"].ToString();
                PP.Password = row["Password"].ToString();
                PP.Client = row["Client"].ToString();
                PP.Language = row["Language"].ToString();
                PP.PoolSize = row["PoolSize"].ToString();
                PP.IdleTimeout = row["IdleTimeout"].ToString();
                PP.Name = row["Name"].ToString();

                PP.IsDemo = row["IsDemo"].ToString();
                PP.ReachSyncCount = Convert.ToInt32(row["ReachSyncCount"]);
                PP.LineCode = row["LineCode"].ToString();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="date"></param>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public static bool Select_SyncParam_SAP(out string date, out string dateTime)
        {
            bool success = false;
            date = "";
            dateTime = "";

            //SELECT top 1 [AID],[日期],[更新时间] FROM [大柴_接口_SAP生产计划_标记]

            var sql = "SELECT top 1 [AID],[日期],[更新时间] FROM [大柴_接口_SAP生产计划_标记]";
            success = DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out DataTable dt, out string err);
            success = err.Length > 0 ? false : true;
            if (dt.Rows.Count > 0)
            {
                date = DateTime.Parse(dt.Rows[0]["日期"].ToString()).ToString("yyyy-MM-dd");
                dateTime = DateTime.Parse(dt.Rows[0]["更新时间"].ToString()).ToString("yyyy-MM-dd HH:mm:ss.fff");
            }
            else
            {
                var sql_Insert =
                    $" INSERT INTO [大柴_接口_SAP生产计划_标记]([日期],[修改时间])" +
                    $" VALUES(getdate(),getdate())";
                DataLinkMesWork.SQLCommon.ExecuteSql(sql_Insert, PP.ConnectionString, out string err_Index);
                Select_SyncParam_SAP(out date, out dateTime);
            }

            return success;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        public static bool Update_SyncParam_SAP(string date)
        {
            //SELECT top 1 [AID],[日期],[更新时间] FROM [大柴_接口_SAP生产计划_标记]
            var sql =
                $" UPDATE [大柴_接口_SAP生产计划_标记]" +
                $" SET [日期] = '{date}',[更新时间] = getdate()";
            return DataLinkMesWork.SQLCommon.ExecuteSql(sql, PP.ConnectionString, out string err);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="startFlowNo"></param>
        /// <param name="flowNoTime"></param>
        /// <returns></returns>
        public static bool Select_SyncParam_FlowNo(out int startFlowNo,out string flowNoTime)
        {
            bool success = false;
            startFlowNo = 0;
            flowNoTime = "";

            //SELECT [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号]

            var sql = $"SELECT top 1 [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号] ORDER BY 更新时间 DESC";
            success = DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out DataTable dt, out string err);
            success = err.Length > 0 ? false : true;
            if (dt.Rows.Count > 0)
            {
                startFlowNo = Convert.ToInt32(dt.Rows[0]["流水号"]);
                flowNoTime = DateTime.Parse(dt.Rows[0]["更新时间"].ToString()).ToString("yyyy-MM-dd HH:mm:ss.fff");
            }

            return success;
        }

        /// <summary>
        /// 根据上线日期获取到 这天的流水号，若这天没有则用【历史最大】流水号
        /// </summary>
        /// <param name="上线日期"></param>
        /// <param name="startFlowNo"></param>
        /// <param name="flowNoTime"></param>
        /// <returns></returns>
        public static bool Select_SyncParam_FlowNo(string useDate,out int startFlowNo, out string flowNoTime)
        {
            bool success = false;
            startFlowNo = 0;
            flowNoTime = "";

            //SELECT [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号]

            var sql = $"SELECT top 1 [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号] WHERE 使用日期 = '{useDate}'";
            //var sql = $"SELECT top 1 [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号] ORDER BY 使用时间 ASC";
            success = DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out DataTable dt, out string err);
            success = err.Length > 0 ? false : true;
            if (dt.Rows.Count > 0)
            {
                startFlowNo = Convert.ToInt32(dt.Rows[0]["流水号"]);
                flowNoTime = DateTime.Parse(dt.Rows[0]["更新时间"].ToString()).ToString("yyyy-MM-dd HH:mm:ss.fff");
            }
            else
            {
                // 获取前一天的流水号 用这个流水号往下排
                DateTime date = DateTime.ParseExact(useDate, "yyyy-MM-dd", CultureInfo.InvariantCulture);
                DateTime previousDay = date.AddDays(-1); // 获取前一天的日期
                string formattedDate = previousDay.ToString("yyyy-MM-dd"); // 格式化为字符串形式
                var sql2 = $"SELECT top 1 [AID],[流水号],[更新时间] FROM [大柴_接口_SAP生产计划_流水号] WHERE 使用日期 <= '{formattedDate}' ORDER BY [流水号] DESC";
                success = DataLinkMesWork.SQLCommon.ExecuteDataTable(sql2, PP.ConnectionString, out DataTable dt2, out string err2);
                success = err2.Length > 0 ? false : true;
                if (dt2.Rows.Count > 0)
                {
                    startFlowNo = Convert.ToInt32(dt2.Rows[0]["流水号"]);
                    flowNoTime = DateTime.Parse(dt2.Rows[0]["更新时间"].ToString()).ToString("yyyy-MM-dd HH:mm:ss.fff");
                }
            }

            return success;
        }










        /// <summary>
        /// 
        /// </summary>
        /// <param name="FlowNo"></param>
        /// <returns></returns>
        public static bool GetFlowNo_CurrMax(out string FlowNo)
        {
            var success = false;
            FlowNo = "-1";
            var sql =
                 $" SELECT MAX([发动机流水号]) FROM [大柴_接口_SAP生产计划_执行计划]";
            success = SqlOperation.ExecuteSql(sql, out DataTable dt, out string error);
            if (dt.Rows.Count > 0)
            {
                FlowNo = dt.Rows[0][0].ToString();
            }
            return success;
        }

        /// <summary>
        /// 获取今天明天两天的订单信息
        /// </summary>
        /// <param name="dateDay"></param>
        /// <param name="table_Plan"></param>
        /// <returns></returns>
        public static bool Get_MesPlan(DateTime dateDay,out DataTable table_Plan)
        {

            TimeSpan oneDay = new TimeSpan(1, 0, 0, 0); // 创建一个表示一天的 TimeSpan 对象
            //var sql = $"SELECT [AID],[订单号],[订单类型],[工厂],[机型号],[数量],[单位],[上线日期],[上线序号],[用户],[修改时间]" +
            //    $" FROM [大柴_接口_SAP生产计划]" +
            //    $" WHERE [上线日期] >= '{dateDay.ToString("yyyy-MM-dd")}'" + //OR [上线日期] = '{tomorrow.ToString("yyyy-MM-dd")}'
            //    $" ORDER BY [上线日期] ,[上线序号]";
            var sql = $"SELECT a.[订单号],[订单类型],[工厂],[机型号],[数量],[单位],[上线日期],[上线序号],[用户],[修改时间],isnull(b.Exist_InMes,0) as Exist_InMes" +
                $" FROM [大柴_接口_SAP生产计划] a left join " +
                $" (select 订单号,count(*) as Exist_InMes from 大柴_接口_SAP生产计划_执行计划 where 生产日期>='{dateDay.ToString("yyyy-MM-dd")}' group by 订单号) b on a.订单号=b.订单号 " +
                $" WHERE [上线日期] >= '{dateDay.ToString("yyyy-MM-dd")}'" + //OR [上线日期] = '{tomorrow.ToString("yyyy-MM-dd")}'
                $" ORDER BY [上线日期] ,[上线序号]";
            return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_Plan, out string err);
        }
        public static bool Get_Mes_ExcutePlan(DateTime dateDay,string key_OrderNum,out DataTable table_Plan)
        {
            var sql = $@"
SELECT [订单类型],[订单号],[机型号],[订单数量],[生产日期],[上线序号],[上线总排序],[发动机流水号],[起始流水号],[终止流水号],[执行状态] 
FROM[大柴_接口_SAP生产计划_执行计划] WHERE 生产日期 = '{dateDay.ToString("yyyy-MM-dd")}' AND 订单号 LIKE '%{key_OrderNum}%'";
            return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_Plan, out string err);
        }

        /// <summary>
        /// 只获取今天的订单信息
        /// </summary>
        /// <param name="dateDay"></param>
        /// <param name="table_Plan"></param>
        /// <returns></returns>
        public static bool Get_MesTodayPlan(DateTime dateDay, out DataTable table_Plan)
        {
            //SELECT [AID],[订单号],[订单类型],[工厂],[机型号],[数量],[单位],[上线日期],[上线序号],[用户],[修改时间] FROM [大柴_接口_SAP生产计划]
            var sql = $"SELECT [AID],[订单号],[订单类型],[工厂],[机型号],[数量],[单位],[上线日期],[上线序号],[用户],[修改时间],isnull([流水号拆分已上报],0) as 流水号拆分已上报" +
                $" FROM [大柴_接口_SAP生产计划]" +
                $" WHERE [上线日期] = '{dateDay.ToString("yyyy-MM-dd")}'" +
                $" ORDER BY [上线序号]";
            return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_Plan, out string err);
        }
        /// <summary>
        /// 更新订单的流水号拆分上报状态：流水号拆分已上报=0，未上报，流水号拆分已上报=1 已上报
        /// </summary>
        /// <param name="OrderNum"></param>
        /// <returns></returns>
        public static bool Update_FlowNoSplit_UpLoadState(string OrderNum)
        {
            //SELECT [AID],[订单号],[订单类型],[工厂],[机型号],[数量],[单位],[上线日期],[上线序号],[用户],[修改时间] FROM [大柴_接口_SAP生产计划]
            var sql = $"Update [大柴_接口_SAP生产计划] set 流水号拆分已上报=1 WHERE [订单号] = '{OrderNum}'";
            return SqlOperation.ExecuteSql(sql, out string error);
        }

        /// <summary>
        /// 获取拆分后数据 展示到网页
        /// </summary>
        /// <param name="dateDay">数据日期</param>
        /// <param name="table_Plan"></param>
        /// <returns></returns>
        public static bool Get_MesSpiltPlan(DateTime dateDay, out DataTable table_Plan)
        {
            var sql = $"SELECT [AID],[订单类型],[生产日期],[班次],[上线序号],[上线总排序],[发动机流水号],[机型号],[托盘号],[订单号],[订单序号],[订单数量],[起始流水号],[终止流水号],[创建时间],[执行状态],[状态变更时间],[操作标记]" +
                $" FROM [大柴_接口_SAP生产计划_执行计划]" +
                $" WHERE [生产日期] = '{dateDay.ToString("yyyy-MM-dd")}'" +
                $" ORDER BY 起始流水号";
            return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_Plan, out string err);
        }

        public static bool Get_MesPlan_ExecPlan(string orderNo, out DataTable table_ExecPlan)
        {
            var sql = $"SELECT [订单号],[订单类型],[机型号],[订单数量],[发动机流水号],[上线序号],[上线总排序],[执行状态],[生产日期]" +
                $" FROM [大柴_接口_SAP生产计划_执行计划]" +
                $" WHERE [订单号] = '{orderNo}'" +
                $" ORDER BY [上线总排序]";
            return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_ExecPlan, out string err);
        }        
        public static DateTime Get_BOMChanged_DateFlag(string LineCode)
        {
            var sql = $"SELECT [编辑时间标识] FROM [大柴_接口_SAP订单BOM_编辑时间标识] WHERE [LineCode]='{LineCode}'";
            DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out DataTable dt, out string err);
            if(dt.Rows.Count>0)
            {
                return DateTime.Parse(dt.Rows[0]["编辑时间标识"].ToString());
            }
            else
            {
                return DateTime.Now.AddDays(-10);
            }
        }
        public static void Update_TimeFlag_BomChanged(DateTime NewTimeFlag,string LineCode)
        {
            var name = "Event_接口_SAP订单BOM_时间标识";

            var sqlParameters = new SqlParameter[] {
                 new SqlParameter("@NewTimeFlag", NewTimeFlag),
                 new SqlParameter("@LineCode", LineCode)
                };
            SqlOperation.ExecuteStoredProcedure(name, sqlParameters, out DataSet ds, out string error);

        }

        public static bool Get_MesBOM_ByOrderNo(string orderNo, out DataTable table_BOM)
        {
            var sql = $"SELECT [订单号],[工序号],[物料号],[物料描述],[总数量],[指定厂家代码],[指定厂家简称],[指定厂家名称],[工艺合件]," +
                $"[虚拟件],[试装标识],[单台数量],[创建时间] FROM [大柴_接口_SAP订单BOM] where 订单号 = {orderNo}";
            return DataLinkMesWork.SQLCommon.ExecuteDataTable(sql, PP.ConnectionString, out table_BOM, out string err);
        }

    }
}