using Model;
using NPOI.POIFS.Properties;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace BLL.APIService
{
    public class SyncSESService
    {
        private const string GETAPIUrl = "https://reportserver.basf-ypc.net.cn:3033/api/query";
        public static void GetSyncContractor()
        {
            //string file = System.Web.HttpContext.Current.Server.MapPath("~/ContractorData.txt");
            //string result = System.IO.File.ReadAllText(file);

            string username = "EpermitContractor_EFC";
            string password = "nM3#yU5$pO8%aZ6";
            //string postData = $"Username={username}&Password={password}";
            string result = BLL.Common.HttpHelper.HttpGetRequest(GETAPIUrl, username, password);
            //BLL.ErrLogInfo.WriteLog($"请求API Result={result}");

            var data = JsonHelper.DeserializeJsonToObject<List<SES>>(result);
            ConcurrentBag<FC_SESReport> fcSesList = new ConcurrentBag<FC_SESReport>();
            string errorInfos = string.Empty;
            string message=string.Empty;
            string fo=string.Empty;

            foreach (var item in data)
            {
                string errorInfo = string.Empty;
                string sesNo = string.Empty;
                FC_SESReport fcSesModel = new FC_SESReport();
                if (!string.IsNullOrEmpty(item.sesNo))
                {
                    fcSesModel.SES_No = item.sesNo;
                    sesNo = item.sesNo;
                }
                else
                {
                    errorInfo += " [SES No.] cannot be empty!";
                }

                fcSesModel.Short_Descrption = item.shortDescrption;

                if (item.startDate != null)
                {
                    try
                    {
                        message = TimeValidation(item.startDate);
                        if (string.IsNullOrEmpty(message))
                        {
                            fcSesModel.Start_Date = TimeValue(item.startDate);
                        }
                        else
                        {
                            errorInfo += "[Start Date]" + message;
                        }
                    }
                    catch (Exception)
                    {
                        errorInfo += " [Start Date]" + message;
                    }
                }

                if (item.endDate != null)
                {
                    try
                    {
                        message = TimeValidation(item.endDate);
                        if (string.IsNullOrEmpty(message))
                        {
                            fcSesModel.End_Date = TimeValue(item.endDate);
                        }
                        else
                        {
                            errorInfo += "[End Date]" + message;
                        }
                    }
                    catch (Exception)
                    {
                        errorInfo += " [End Date]" + message;
                    }
                }

                fcSesModel.Created_by = item.createdBy;

                if (item.createdOn != null)
                {
                    try
                    {
                        message = TimeValidation(item.createdOn);
                        if (string.IsNullOrEmpty(message))
                        {
                            fcSesModel.Created_on = TimeValue(item.createdOn);
                        }
                        else
                        {
                            errorInfo += "[Created on]" + message;
                        }
                    }
                    catch (Exception)
                    {
                        errorInfo += " [Created on]" + message;
                    }
                }

                if (item.tecoDate != null)
                {
                    try
                    {
                        message = TimeValidation(item.tecoDate);
                        if (string.IsNullOrEmpty(message))
                        {
                            fcSesModel.TECO_Date = TimeValue(item.tecoDate);
                            fcSesModel.TECO_Format = TimeValue(item.tecoDate);
                        }
                        else
                        {
                            errorInfo += "[TECO Date]" + message;
                        }
                    }
                    catch (Exception)
                    {
                        errorInfo += " [TECO Date]" + message;
                    }
                }

                if (!string.IsNullOrEmpty(item.requisitioner))
                {
                    string requisitioner = item.requisitioner;
                    string[] reman = requisitioner.Split('/');
                    if (reman.Count() == 2)
                    {
                        fcSesModel.Requisitioner = reman[0];
                    }
                    else
                    {
                        fcSesModel.Requisitioner = requisitioner;
                    }
                }
                else
                {
                    fcSesModel.Requisitioner = "";
                }

                if (!string.IsNullOrEmpty(item.fo))
                {
                    fcSesModel.FO = item.fo;
                    fo = fcSesModel.FO;
                }
                else
                {
                    errorInfo += "[FO] cannot be empty!";
                }

                if (!string.IsNullOrEmpty(item.item))
                {
                    try
                    {
                        if (IsWholeNumber(item.item))
                        {
                            fcSesModel.Item = int.Parse(item.item);
                        }
                        else
                        {
                            errorInfo += " [Item]Must be in numeric format!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfo += "[Item]Must be in numeric format!";
                    }
                }

                fcSesModel.Vendor_Name = item.vendorName;

                if (item.ssrBudget!=null)
                {
                    try
                    {
                        if (IsNumber(item.ssrBudget))
                        {
                            fcSesModel.SSR_budget = DecibelConversion(item.ssrBudget.Value.ToString().Trim());
                        }
                        else
                        {
                            errorInfo += "[SSR budget]Must be in numeric format!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfo += "[SSR budget]Must be in numeric format!";
                    }
                }
                else
                {
                    fcSesModel.SSR_budget = 0;
                }

                fcSesModel.Currency = item.currency;

                if (item.contractorQuotation!=null)
                {
                    try
                    {
                        if (IsNumber(item.contractorQuotation))
                        {
                            fcSesModel.Contractor_quotation = DecibelConversion(item.contractorQuotation.Value.ToString().Trim());
                        }
                        else
                        {
                            errorInfo += " [Contractor quotation]Must be in numeric format!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfo += " [Contractor quotation]Must be in numeric format!";
                    }
                }
                else
                {
                    fcSesModel.Contractor_quotation = 0;
                }

                if (item.ssrActualCost!=null)
                {
                    try
                    {
                        if (IsNumber(item.ssrActualCost))
                        {
                            fcSesModel.SSR_Actual_cost = DecibelConversion(item.ssrActualCost.Value.ToString().Trim());
                        }
                        else
                        {
                            errorInfo += "[SSR Actual cost]Must be in numeric format!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfo += "[SSR Actual cost]Must be in numeric format!";
                    }
                }
                else
                {
                    fcSesModel.SSR_Actual_cost = 0;
                }

                if (!string.IsNullOrEmpty(item.taxRate))
                {
                    try
                    {
                        if (IsNumber(item.taxRate.Replace("%", "")))
                        {
                            fcSesModel.Tax_rate = DecibelConversion(item.taxRate.Replace("%", "").Trim());
                        }
                        else
                        {
                            errorInfo += "[Tax rate]Must be in numeric format!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfo += "[Tax rate]Must be in numeric format!";
                    }
                }

                if (item.deduction!=null)
                {
                    try
                    {
                        if (IsNumber(item.deduction))
                        {
                            fcSesModel.Deviation = DecibelConversion(item.deduction.Value.ToString().Trim());
                        }
                        else
                        {
                            errorInfos += "[Deviation]Must be in numeric format!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfos += "[Deviation]Must be in numeric format!";
                    }
                }

                //if (item.deductionPercentage!=null)
                //{
                //    try
                //    {
                //        if (IsNumber(item.deductionPercentage))
                //        {
                //            fcSesModel.Deviation_Percentage = DecibelConversion(item.deductionPercentage.ToString().Trim());
                //        }
                //        else
                //        {
                //            errorInfos += (i + 2) + "line[Deviation Percentage]Must be in numeric format</br>";
                //        }
                //    }
                //    catch (Exception)
                //    {
                //        errorInfos += (i + 2) + "line[Deviation Percentage]Must be in numeric format</br>";
                //    }
                //}

                fcSesModel.Long_text = item.longText;
                if (!string.IsNullOrEmpty(item.workOrder))
                {
                    try
                    {
                        if (IsWholeNumber(item.workOrder))
                        {
                            fcSesModel.Work_Order = int.Parse(item.workOrder.Trim());
                        }
                        else
                        {
                            errorInfos += "[Work Order]Must be in pure number format!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfos += "[Work Order]Must be in pure number format!";
                    }
                }
                fcSesModel.Function_location = item.functionLocation;
                fcSesModel.Main_work_center = item.mainWorkCenter;

                if (!string.IsNullOrEmpty(item.workCenter))
                {
                    fcSesModel.Work_Center = item.workCenter.ToString();

                    if (fcSesModel.Work_Center.Contains("CTM/L"))
                    {
                        fcSesModel.Dep = "CTM";
                        fcSesModel.Section = "CTM/P";
                    }
                    else if (fcSesModel.Work_Center.Length > 5 && (fcSesModel.Work_Center.ToString() == "CTA/LV" || fcSesModel.Work_Center.ToString() == "CTA/LW"))
                    {
                        fcSesModel.Dep = "CTA";
                        fcSesModel.Section = "CTA/L";
                    }
                    else if (fcSesModel.Work_Center.Length > 5 && fcSesModel.Work_Center.Substring(0, 5) == "CTA/L" && (fcSesModel.Work_Center.ToString() != "CTA/LV" || fcSesModel.Work_Center.ToString() != "CTA/LW"))
                    {
                        fcSesModel.Section = "CTA/P";
                    }
                    else if (fcSesModel.Work_Center.Length >= 5)
                    {
                        fcSesModel.Dep = fcSesModel.Work_Center.Substring(0, 3);
                        fcSesModel.Section = fcSesModel.Work_Center.Substring(0, 5);
                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(item.wbs))
                    {
                        fcSesModel.Section = "PS";
                    }
                }

                fcSesModel.Cost_center = item.costCenter;
                fcSesModel.WBS = item.wbs;
                fcSesModel.Network = item.network;

                if (item.claimSheetsReceive!=null)
                {
                    try
                    {
                        message = TimeValidation(item.claimSheetsReceive);
                        if (string.IsNullOrEmpty(message))
                        {
                            fcSesModel.Claim_sheets_receive = TimeValue(item.claimSheetsReceive);
                            fcSesModel.CS_REC_Format = TimeValue(item.claimSheetsReceive);
                        }
                        else
                        {
                            errorInfos += "[Claim sheets receive]" + message + "!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfos += "[Claim sheets receive]" + message + "!";
                    }
                }

                if (item.engineerConfirmedO!=null)
                {
                    try
                    {
                        message = TimeValidation(item.engineerConfirmedO);
                        if (string.IsNullOrEmpty(message))
                        {
                            fcSesModel.Engineer_confirmed_o = TimeValue(item.engineerConfirmedO);
                            fcSesModel.ENG_CONF_Format = TimeValue(item.engineerConfirmedO);
                        }
                        else
                        {
                            errorInfos += "[Engineer confirmed o]" + message + "!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfos += "[Engineer confirmed o]" + message + "!";
                    }
                }

                if (item.sesConfirmedOn!=null)
                {
                    try
                    {
                        message = TimeValidation(item.sesConfirmedOn);
                        if (string.IsNullOrEmpty(message))
                        {
                            fcSesModel.SES_Confirmed_on = TimeValue(item.sesConfirmedOn);
                            fcSesModel.SES_CONF_Format = TimeValue(item.sesConfirmedOn);
                        }
                        else
                        {
                            errorInfos += "[SES Confirmed on]" + message + "!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfos += "[SES Confirmed on]" + message + "!";
                    }
                }
                // 以下没有
                // Contractor_duration
                // BoQ_confirmation_dur
                // Settlement duration
                // Invoiced on
                //Invoice duration
                // Payment made on
                //Payment duration

                fcSesModel.Accepted = item.Accepted;
                fcSesModel.Deleted = item.deleted;
                fcSesModel.Blocked = item.blocked;
                fcSesModel.Changed_by = item.changedDy;
                fcSesModel.DateIn= DateTime.Now;

                // 以下和诚实度和及时性有关
                fcSesModel.CPT_No = item.cptNo;
                if (!string.IsNullOrEmpty(item.con))
                {
                    try
                    {
                        if (IsNumber(item.con))
                        {
                            fcSesModel.Con_Days = DecibelConversion(item.con.Trim());
                        }
                        else
                        {
                            errorInfo += " [con]Must be in numeric format!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfo += " [con]Must be in numeric format!";
                    }
                }

                if (item.submiteDate != null)
                {
                    try
                    {
                        message = TimeValidation(item.submiteDate);
                        if (string.IsNullOrEmpty(message))
                        {
                            fcSesModel.Submit_Date = TimeValue(item.submiteDate);
                        }
                        else
                        {
                            errorInfos += "[submiteDate]" + message + "!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfos += "[submiteDate]" + message + "!";
                    }
                }
                if (item.contractorQuotation!=null)
                {
                    try
                    {
                        if (IsNumber(item.contractorQuotation))
                        {
                            fcSesModel.Quotation = DecibelConversion(item.contractorQuotation.Value.ToString());
                        }
                        else
                        {
                            errorInfo += " [contractorQuotation]Must be in numeric format!";
                        }
                    }
                    catch (Exception)
                    {
                        errorInfo += " [contractorQuotation]Must be in numeric format!";
                    }
                }

                if (item.ssrActualCost != null && item.contractorQuotation != null)
                {
                    decimal punishment = 0;
                    var pun = from x in Funs.DB.EMC_Punishment where x.SES_No == item.sesNo select x;
                    if (pun.Count() > 0)
                    {
                        punishment = pun.Sum(x => x.Company != null ? x.Company.Value : 0) + pun.Sum(x => x.Individual != null ? x.Individual.Value : 0);
                    }

                    decimal tax_Value = decimal.Parse((item.ssrActualCost.Value * (1 + fcSesModel.Tax_rate)).Value.ToString("0.00"));
                    fcSesModel.Deduction = decimal.Parse((item.contractorQuotation.Value - tax_Value - punishment).ToString("0.00"));
                }


                if (errorInfo != string.Empty)
                {
                    errorInfos += errorInfo;
                }
                else
                {
                    fcSesList.Add(fcSesModel);
                }
            }
            AddSyncSES(fcSesList);
        }

        private static void AddSyncSES(ConcurrentBag<FC_SESReport> sesList)
        {
            SQLHelper.ExecutSql("truncate table FC_SESReport");
            var result = SqlBulkHelper.BulkInsert(SqlBulkHelper.GetCon(), "FC_SESReport", sesList.ToList());
           
        }

        #region 格式验证
        /// <summary>
        /// 时间格式验证
        /// </summary>
        /// <param name="time"></param>
        /// <returns></returns>
        private static string TimeValidation(object time)
        {
            string message = string.Empty;
            try
            {
                if (time.ToString().Contains("."))
                {
                    DateTime.ParseExact(time.ToString(), "dd.MM.yyyy", CultureInfo.InvariantCulture);
                }
                else
                {
                    Convert.ToDateTime(time);
                }
            }
            catch (Exception ex)
            {
                message = "Please enter a valid time format！";
            }
            return message;
        }

        /// <summary>
        /// 带小数的数字验证
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public static bool IsNumber(object input)
        {
            string pattern = "^-?\\d+$|^(-?\\d+)(\\.\\d+)?$";
            Regex regex = new Regex(pattern);
            string value = input != null ? DecibelConversion(input.ToString()).ToString() : "";
            return regex.IsMatch(value.Trim());
        }

        /// <summary>
        /// 整数
        /// </summary>
        /// <param name="strNumber"></param>
        /// <returns></returns>
        public static bool IsWholeNumber(object input)
        {
            Regex g = new Regex(@"^[-]?[0-9]\d*$");
            string value = input != null ? input.ToString() : "";
            return g.IsMatch(value);
        }

        /// <summary>
        /// 千分位转换
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public static decimal DecibelConversion(string input)
        {
            var inputValue = string.Empty;
            if (input.Contains(',') && input.Contains('.'))
            {
                var inputstr = input.Replace(".", "").Split(',');
                if (inputstr.Length > 0)
                {
                    for (int i = 0; i < inputstr.Length; i++)
                    {
                        inputValue += i < (inputstr.Length - 1) ? inputstr[i] : "." + inputstr[i];
                    }
                }
            }
            else if (input.Contains(','))
            {
                inputValue = input.Replace(",", ".");
            }
            else
            {
                inputValue = input;
            }
            inputValue = inputValue.Length > 18 ? inputValue.Substring(0, 17) : inputValue;
            return decimal.Parse(inputValue);
        }

        /// <summary>
        /// 时间格式转换
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DateTime TimeValue(object value)
        {
            string dateValue = string.Empty;
            if (value != null)
            {
                if (value.ToString().Contains("."))
                {
                    dateValue = DateTime.ParseExact(value.ToString(), "dd.MM.yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
                }
                else
                {
                    dateValue = DateTime.Parse(value.ToString()).ToString("yyyy-MM-dd");
                }
            }
            return DateTime.Parse(dateValue);
        }
        #endregion

    }

    #region 内部类
    [Serializable]
    internal class SES
    {
        public string Accepted { get; set; }
        public string blocked { get; set; }
        public DateTime? startDate { get; set; }
        public DateTime? endDate { get; set; }
        public string createdBy { get; set; }
        public DateTime? tecoDate { get; set; }
        public string sesNo { get; set; }
        public string deleted { get; set; }
        public string workOrder { get; set; }
        public string OpSopNo { get; set; }
        public string prItem { get; set; }
        public string prNumber { get; set; }
        public string shortDescrption { get; set; }
        public string fo { get; set; }
        public string item { get; set; }
        public string vendorName { get; set; }
        public decimal? ssrBudget { get; set; }
        public decimal? contractorQuotation { get; set; }
        public decimal? ssrActualCost { get; set; }
        public decimal? ssrActualCostNetAmount { get; set; }
        public string wbs { get; set; }
        public DateTime? createdOn { get; set; }
        public DateTime? claimSheetsReceive { get; set; }
        public decimal? sesConfirmedOn { get; set; }
        public string requisitioner { get; set; }
        public string currency { get; set; }
        public string reducedByQuantity { get; set; }
        public string userField { get; set; }
        public string taxRate { get; set; }

        public string changedDy { get; set; }
        public string longText { get; set; }
        public string functionLocation { get; set; }
        public string mainWorkCenter { get; set; }
        public string workCenter { get; set; }
        public string costCenter { get; set; }
        public string network { get; set; }
        public decimal? deduction { get; set; }
        public string deductionPercentage { get; set; }
        public string con { get; set; }
        public DateTime? submiteDate { get; set; }
        public DateTime? updateTime { get; set; }
        public string cptNo { get; set; }
        public string revision { get; set; }
        public string tarItem { get; set; }
        public string budgetBy { get; set; }
        public string sesStatus { get; set; }
        public string sesConfirm { get; set; }
        public string visaResetCount { get; set; }
        public string visaExceptionCount { get; set; }
        public string settleExceptionCount { get; set; }
        public string settleResetCount { get; set; }
        public string finishDate { get; set; }
        public string finishDateConfirm { get; set; }
        public string finishDateVerify { get; set; }
        public string isPack { get; set; }
        public string checkMoney { get; set; }

        public decimal? amountDeclared { get; set; }
        public decimal? priceFirstValue { get; set; }
        public decimal? approvedValue { get; set; }
        public decimal? entrustValue { get; set; }
        public decimal? overdueDeductions { get; set; }
        public decimal? safetyAssDeduc { get; set; }

        public string settleApprovedType { get; set; }
        public string externalNo { get; set; }
        public decimal? cptEndTime { get; set; }
        public string engineerConfirmedO { get; set; }
        public string settleClaimSheetsReceive { get; set; }
        public string section { get; set; }
        public string deviation { get; set; }
        public string deviationPercentage { get; set; }
        public string LastPayment { get; set; }

    }
    #endregion



}