using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using AMESCoreStudio.WebApi;
using AMESCoreStudio.WebApi.Models.BAS;
using AMESCoreStudio.WebApi.Models.AMES;
using AMESCoreStudio.CommonTools.Result;
using Microsoft.Extensions.Configuration;
using System.Net;
using System.Net.Mail;
using System.Data.Common;
using System.Data;
using System.Dynamic;

namespace AMESCoreStudio.WebApi.Controllers.AMES
{
    /// <summary>
    /// 報工資料檔
    /// </summary>
    [Route("api/[controller]")]
    [ApiController]
    public class ActualTimeController : ControllerBase
    {
        private readonly AMESContext _context;
        private readonly IConfiguration _config;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        public ActualTimeController(AMESContext context)
        {
            _config = new ConfigurationBuilder().SetBasePath(Environment.CurrentDirectory).AddJsonFile("appsettings.json").Build();
            _context = context;
        }
     

    /// <summary>
    /// 
    /// </summary>
    /// <param name="ActualDate"></param>
    /// <param name="page"></param>
    /// <param name="limit"></param>
    /// <returns></returns>
        [Route("[action]")]
        [HttpGet]
        public async Task<ResultModel<dynamic>> GetActualTimeDetail(string ActualDate,int page = 0, int limit = 10)
        {
            DateTime date = System.DateTime.Now;
           if (ActualDate != null) 
                 date = DateTime.Parse(ActualDate); // 將字符串轉換為 DateTime 對象

            // 獲取該月份的第一天
            DateTime firstDayOfMonth = new DateTime(date.Year, date.Month, 1);

            // 獲取該月份的最後一天
            DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
            try
            {
                ResultModel<dynamic> result = new ResultModel<dynamic>();
                string sql = string.Format(@"select to_char(a.Actual_Date,'yyyy-MM-dd')  actualdate ,a.wip_no wiP_NO,a.actual_id,a.unit_no,a.Production_Time,a.production_cnt_sap,
                        b.op_CNT,b.total_CT,b.PRODUCTION_QTY,b.Production_Time_Detail, a.Production_Time - b.Production_Time_Detail Production_TimeExcept ,a.route
                        from jhames.actual_time a left outer join 
                        (
                        select 　actual_ID ,Unit_NO,sum( op_CNT) op_CNT,sum(total_CT) total_CT,Sum(PRODUCTION_QTY) PRODUCTION_QTY,sum(Production_Time_Detail)  Production_Time_Detail
                        from (
                        select Actual_ID,unit_no, A_CNT OP_CNT, A_CT total_CT,S_CT  PRODUCTION_QTY , 
                        case when  unit_no ='S' then  S_CT * A_CT  
                        else   S_CT  *A_CNT * A_CT end  Production_Time_detail
                        from  jhames.actual_time_detail )
                        group by 　actual_ID ,Unit_NO) b on a.actual_id = b.actual_ID 
                        where to_char(a.Actual_Date,'yyyy-MM-dd') >='{0}' and  to_char(a.Actual_Date,'yyyy-MM-dd') <='{1}' ", firstDayOfMonth.ToString("yyyy-MM-dd"), lastDayOfMonth.ToString("yyyy-MM-dd"));

                DbConnection conn = _context.Database.GetDbConnection();
                if (conn.State != System.Data.ConnectionState.Open)
                {
                    await conn.OpenAsync();
                }

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        if (reader.HasRows)
                        {
                            List<dynamic> list = new List<dynamic>();
                            DataTable table = new DataTable();

                            table = DataReaderToDataTable(reader);

                            foreach (DataRow row in table.Rows)
                            {
                                dynamic dyn = new ExpandoObject();
                                list.Add(dyn);
                                foreach (DataColumn column in table.Columns)
                                {
                                    var dic = (IDictionary<string, object>)dyn;
                                    dic[column.ColumnName] = row[column];
                                }
                            }

                            result.DataTotal = list.Count();

                            //Table 頁數
                            if (page > 0)
                            {
                                list = list.Skip((page - 1) * limit).Take(limit).ToList();
                            }
                            result.Data = list;
                        }

                    }
                }


            

         

            if (result == null)
            {
                result.Msg = "查無資料";
                result.Success = false;
                return result;
            }

            result.Success = true;
            result.Msg = "OK";
            return result;
            }
            catch (Exception ex)
            {
                ResultModel<dynamic> result = new ResultModel<dynamic>();
                result.Success = false;
                result.Msg = ex.ToString();
                return result;
            }
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static DataTable DataReaderToDataTable(DbDataReader reader)
        {
            try
            {
                DataTable dt = new DataTable();
                int fieldCount = reader.FieldCount;
                for (int fieldIndex = 0; fieldIndex < fieldCount; ++fieldIndex)
                {
                    dt.Columns.Add(reader.GetName(fieldIndex), reader.GetFieldType(fieldIndex));
                }

                dt.BeginLoadData();

                object[] rowValues = new object[fieldCount];
                while (reader.Read())
                {
                    reader.GetValues(rowValues);
                    dt.LoadDataRow(rowValues, true);
                }
                reader.Close();
                dt.EndLoadData();

                return dt;

            }
            catch (Exception ex)
            {
                throw new Exception("DataReader Convert DataTable Error!", ex);
            }
        }

    }
}