using LaaPackages.Features.Cordonel.Models;
using Newtonsoft.Json.Linq;
using Oracle.ManagedDataAccess.Client;
using Service.MeterProcessState;
using Service.MeterProcessState.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using System.Globalization;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
using System.Web.Helpers;
using System.Web.Http;
using System.Web.UI.WebControls;
using Xylem.Common.Cryptology.Security;
using Xylem.Common.Logic.ServiceCore;


namespace Xylem.Common.Service.MeterProcessState.Controllers
{
    [RoutePrefix("api/LuController")]
    public class LuController : ApiController
    {
        public static class GlobalConfig
        {
            public static Lazy<String> connectionString = new Lazy<String>(()
            =>
            System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString
            );




        }



        [Route("GetNZDaten")]
        public async Task<HttpResponseMessage> GetNZDaten(string Seriennumer = "")
        {
            try
            {

                using (var dataAccess = new SqlDataAccess(GlobalConfig.connectionString.Value))
                {

                    StringBuilder query = new StringBuilder();
                    query.AppendLine("SELECT TOP 1 * from Verbundzaehler where (Q1_SOLL is NULL) and (SerienNrNebenzaehler > 0 or KundeneigeneSerNrNbZ <> '') and (Herkunft is null or Herkunft <> 'M') and AnlageDatum > getdate() - 7 order by Datum desc ");

                    var retDic = new List<Tuple<string, string>>();
                    var dt = dataAccess.ExecuteQuery(query.ToString());
                    foreach (var item in dt.Select())
                    {
                        string strSQLOra;
                        int lngSerial;
                        string strSerial;

                        double Q1;
                        double Q2;
                        double F1;
                        double F2;

                        lngSerial = (int)item["SerienNrNebenzaehler"];
                        strSerial = item["KundeneigeneSerNrNbZ"].ToString().Trim();

                        if (strSerial == "" && lngSerial > 0)
                        {
                            strSQLOra = "SELECT * FROM PRIMA_RO.DT01_LAATZEN WHERE ZAEHLERNUMMER ='" + lngSerial.ToString("00000000") + "'";
                        }
                        else
                        {
                            if (strSerial != "")
                            {
                                //kundeneigenen NZ SerienNr ist bekannt
                                strSQLOra = "SELECT * FROM PRIMA_RO.DT01_LAATZEN WHERE ZAEHLERNUMMER='" + strSerial.Replace(" ", "") + "' or ZAEHLERNUMMER='" + strSerial + "'";
                            }
                            else
                            {
                                return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return "ERROR 1#"; }));
                                //     ' weder kundeneigenen SerienNr noch numerische NZ SerienNr   bekannt

                            }
                        }

                        // create connection
                        OracleConnection con = new OracleConnection();

                        // create connection string using builder
                        OracleConnectionStringBuilder ocsb = new OracleConnectionStringBuilder();
                        ocsb.Password = "senspi2012";
                        ocsb.UserID = "prima_ro";
                        ocsb.DataSource = "//SLUORADB:1521/DT01";

                        // connect
                        con.ConnectionString = ocsb.ConnectionString;
                        con.Open();


                        OracleCommand command = con.CreateCommand();

                        command.CommandText = strSQLOra;

                        OracleDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            Q1 = ((double)reader["Q1_SOLL"]) / 1000;
                            Q2 = ((double)reader["Q2_SOLL"]) / 1000;
                            F1 = (double)reader["FEHLER_Q1"];
                            F2 = (double)reader["FEHLER_Q2"];


                        }




                    }

                    return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return "store"; }));

                }


            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.ToString() + "<br >" + ex.InnerException.ToString());
            }
        }
        [Route("SyncKitronData")]
        public async Task<HttpResponseMessage> SyncKitronData()
        {




            StringBuilder query = new StringBuilder();
            query.AppendLine("SELECT TOP (1) ID, Jobname, ActionDate, DoneDate, LastID, DataCount FROM KitronSync where DoneDate is null order by ID ");
            using (var dataAccess = new SqlDataAccess(GlobalConfig.connectionString.Value))
            {
                var dt = dataAccess.ExecuteQuery(query.ToString());
                foreach (DataRow item in dt.Rows)
                {
                    var LastID = (int)item["LastID"];

                    var DataCount = (int)item["DataCount"];
                    var UpdateTillLasID = LastID + DataCount;
                    var kitronFinals = dataAccess.ExecuteQuery($" sELECT *  FROM OPENQUERY (MyOraDB, ' SELECT DS_ID,ID_PCB,ID_TEST,JSON FROM DELTACHEF.GENESIS_PCBFUNCTIONALTEST_PD WHERE DS_ID >= {LastID + 1} and DS_ID <= {LastID + 1 + DataCount}  order by DS_ID asc  ');");
                    foreach (DataRow kitronItem in kitronFinals.Rows)
                    {
                        try
                        {
                            var DS_ID = (double)kitronItem["DS_ID"];
                            var ID_PCB = kitronItem["ID_PCB"].ToString();
                            var ID_TEST = (double)kitronItem["ID_TEST"];
                            var JSON = kitronItem["JSON"].ToString();
                            var oo = JObject.Parse(JSON);
                            UpdateTillLasID = (int)DS_ID;
                            var TestResultPass = false;
                            var IrDALogonSkeleton = "";
                            var FWVersion = "";
                            var MaterialNumber = "";
                            var StartDT = DateTime.MinValue;
                            foreach (var oItem in oo.Children().Values())
                            {
                                foreach (JProperty ChildValues in oItem.Children())
                                {
                                    switch (ChildValues.Name)
                                    {
                                        case "TestResult":
                                            TestResultPass = (ChildValues.Value.ToString().ToUpper() == "PASS");
                                            break;
                                        case "IrDA Logon Skeleton":
                                            IrDALogonSkeleton = ChildValues.Value.ToString();
                                            break;
                                        case "FW Version":
                                            FWVersion = ChildValues.Value.ToString();
                                            break;
                                        case "MaterialNumber":
                                            MaterialNumber = ChildValues.Value.ToString();
                                            break;
                                        case "StartDT":
                                            DateTime.TryParseExact(ChildValues.Value.ToString(), "dd.MM.yyyy HH:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.AssumeLocal, out StartDT);
                                            break;
                                        default:
                                            break;
                                    }

                                }
                            }
                            if (TestResultPass)
                            {
                                int matr = -1;
                                string remark = "";
                                if (!int.TryParse(MaterialNumber, out matr))
                                {
                                    matr = -1;
                                    remark = MaterialNumber;
                                }




                                dataAccess.ExecuteQuery($"" +
                                    $" IF  not EXISTS (SELECT 1 FROM dbo.[Cordonel_KitronData]  WHERE KitronDSId = {DS_ID}) " +
                                    $" Begin" +
                                    $" INSERT INTO [dbo].[Cordonel_KitronData]   ([PcbId] ,[KitronDSId] ,[Skeleton]           ,[FW]           ,[MaterialNumber]  ,[ProdDate],                            [Remark])  " +
                                    $" VALUES  (                                  {ID_PCB},{DS_ID},    '{IrDALogonSkeleton}','{FWVersion}', {matr} ,             '{StartDT.ToString("yyyyMMdd HH:mm:ss")}', '{remark}')" +
                                    $" end");



                            }


                        }
                        catch (Exception ex)
                        {

                            throw;
                        }
                    }
                    dataAccess.ExecuteQuery($"Update [dbo].KitronSync set  LastID = {UpdateTillLasID} ");
                }

            }




            return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return true; }));
        }

        [Route("SyncKitronDataByPbc")]
        public async Task<HttpResponseMessage> SyncKitronDataByPbc(int PcbId)
        {

            using (var dataAccess = new SqlDataAccess(GlobalConfig.connectionString.Value))
            {

                var kitronFinals = dataAccess.ExecuteQuery($" sELECT *  FROM OPENQUERY (MyOraDB, ' SELECT DS_ID,ID_PCB,ID_TEST,JSON FROM DELTACHEF.GENESIS_PCBFUNCTIONALTEST_PD WHERE ID_PCB = ''{PcbId}''  order by DS_ID asc  ');");
                foreach (DataRow kitronItem in kitronFinals.Rows)
                {
                    try
                    {
                        var DS_ID = (double)kitronItem["DS_ID"];
                        var ID_PCB = kitronItem["ID_PCB"].ToString();
                        var ID_TEST = (double)kitronItem["ID_TEST"];
                        var JSON = kitronItem["JSON"].ToString();
                        var oo = JObject.Parse(JSON);
                        var TestResultPass = false;
                        var IrDALogonSkeleton = "";
                        var FWVersion = "";
                        var MaterialNumber = "";
                        var StartDT = DateTime.MinValue;
                        foreach (var oItem in oo.Children().Values())
                        {
                            foreach (JProperty ChildValues in oItem.Children())
                            {
                                switch (ChildValues.Name)
                                {
                                    case "TestResult":
                                        TestResultPass = (ChildValues.Value.ToString().ToUpper() == "PASS");
                                        break;
                                    case "IrDA Logon Skeleton":
                                        IrDALogonSkeleton = ChildValues.Value.ToString();
                                        break;
                                    case "FW Version":
                                        FWVersion = ChildValues.Value.ToString();
                                        break;
                                    case "MaterialNumber":
                                        MaterialNumber = ChildValues.Value.ToString();
                                        break;
                                    case "StartDT":
                                        DateTime.TryParseExact(ChildValues.Value.ToString(), "dd.MM.yyyy HH:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.AssumeLocal, out StartDT);
                                        break;
                                    default:
                                        break;
                                }

                            }
                        }
                        if (TestResultPass)
                        {
                            int matr = -1;
                            string remark = "";
                            if (!int.TryParse(MaterialNumber, out matr))
                            {
                                matr = -1;
                                remark = MaterialNumber;
                            }




                            dataAccess.ExecuteQuery($"" +
                                $" IF  not EXISTS (SELECT 1 FROM dbo.[Cordonel_KitronData]  WHERE KitronDSId = {DS_ID}) " +
                                $" Begin" +
                                $" INSERT INTO [dbo].[Cordonel_KitronData]   ([PcbId] ,[KitronDSId] ,[Skeleton]           ,[FW]           ,[MaterialNumber]  ,[ProdDate],                            [Remark])  " +
                                $" VALUES  (                                  {ID_PCB},{DS_ID},    '{IrDALogonSkeleton}','{FWVersion}', {matr} ,             '{StartDT.ToString("yyyyMMdd HH:mm:ss")}', '{remark}')" +
                                $" end");



                        }


                    }
                    catch (Exception ex)
                    {

                        throw;
                    }
                }
            }
            return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return true; }));
        }



        [Route("GetProddate")]
        public async Task<HttpResponseMessage> GetProddate(string Seriennumer = "")
        {
            return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return getLastProductionDateByPcb(Seriennumer); }));
        }


        public class PickingInfo
        {
            public string Error { get; set; }

            public string Warning { get; set; }

            public string Info { get; set; }
        }
        [Route("GetPcbInfoPicking")]
        public async Task<HttpResponseMessage> GetPcbInfoPicking(int PcbId)
        {

            return await Task<PickingInfo>.Run(() =>
            {
                try
                {

                    PickingInfo ret = null;
                    using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value))
                    {
                        var sb = new StringBuilder();
                      sb.AppendLine($"      SELECT        prim.PcbId, prim.ProdDate, prim.BlockedDate, prim.BlockedRemark, kd.MaterialNumber, count (kd.ID) as CountOlderRegister , min(kd.ProdDate) as CountOlderFrom, max(kd.ProdDate)   as CountOlderTo                            ");
                      sb.AppendLine($"      FROM           dbo.Cordonel_KitronData prim                                                                                                                                                                                                 ");
					  sb.AppendLine($"          inner join dbo.Cordonel_KitronData AS kd  on prim.MaterialNumber = kd.MaterialNumber  left OUTER JOIN                                                                                                                                   ");
                      sb.AppendLine($"       dbo.Cordonel_AssignedPicking AS ap ON kd.PcbId = ap.CordonelAssignedPicking_PcbId AND ap.CordonelAssignedPicking_IsDeleted = 0 LEFT OUTER JOIN                                                                                             ");
                      sb.AppendLine($"       dbo.MapPcbIdToSerialNumber AS map ON kd.PcbId = map.MapPcbIdToSerialNumber_PcbId AND map.MapPcbIdToSerialNumber_Deleted = 0 LEFT OUTER JOIN                                                                                                ");
                      sb.AppendLine($"       dbo.Cordonel_EOL_Progress AS eol ON kd.PcbId = eol.PcbId LEFT OUTER JOIN                                                                                                                                                                   ");
                      sb.AppendLine($"       dbo.AuftragPositionSerienNr AS aps ON aps.SerienNr = map.MapPcbIdToSerialNumber_SerialNumber AND aps.Wiederholungen = - 1                                                                                                                  ");
                      sb.AppendLine($"          WHERE prim.PcbId =  {PcbId} and ap.CordonelAssignedPicking_FertigungsAuftragsNr is null                                                                                                                                               ");
                      sb.AppendLine($"          and prim.ProdDate >= kd.ProdDate and kd.BlockedDate is null and  kd.SignedOutDate is null                                                                                                                                               ");
                      sb.AppendLine($"                                                                                                                                                                                                                                                  ");
                      sb.AppendLine($"          group by    prim.PcbId, prim.ProdDate, prim.BlockedDate, prim.BlockedRemark ,  kd.MaterialNumber                                                                                                                                        ");




                        var dbResult = dataacces.ExecuteQuery(sb.ToString());
                        foreach (DataRow row in dbResult.Rows)
                        {

                            DateTime? CheckBlockedDate =( row["BlockedDate"].GetType() == typeof(System.DBNull) ? null :  (DateTime?)row["BlockedDate"]);
                            var CheckProdDate = (DateTime)row["ProdDate"];
                            var blockText = (row["BlockedRemark"].GetType() == typeof(System.DBNull) ? "" : (String)row["BlockedRemark"]);
                            var matNr = (int)row["MaterialNumber"];


                            DateTime? CountOlderFrom =  (row["CountOlderFrom"].GetType() == typeof(System.DBNull) ? null : (DateTime?)row["CountOlderFrom"]); 
                            DateTime? CountOlderTo = (row["CountOlderTo"].GetType() == typeof(System.DBNull) ? null : (DateTime?)row["CountOlderTo"]); 
                            var CountOlder = (int)row["CountOlderRegister"];


                            ret = new PickingInfo() { Info = $"{PcbId} ist von Materialnummer: {matNr} und dem Produktiondatum: {CheckProdDate} " };

                            if (CheckBlockedDate.HasValue)
                            {
                                ret.Error = $"Gesperrt am {CheckBlockedDate.Value} wegen. {blockText}";
                            }
                            if (CountOlder >= 2)
                            {
                                ret.Warning = $"Es gibt {CountOlder} ältere Register mit der Materialnummer: {matNr}. Produktionsdatum von {CountOlderFrom.Value} bis {CountOlderTo.Value} ";
                            }
                            
                        }

                    }
                    return Request.CreateResponse(HttpStatusCode.OK, ret);



                    //return Request.CreateResponse(HttpStatusCode.OK, getUserList(LogInName, Id));
                }
                catch (Exception ex)
                {
                    return Request.CreateResponse(HttpStatusCode.InternalServerError, ex);
                }
            });
        }


            public async Task<DateTime> getLastProductionDateByPcb(string Seriennumer = "")
            {
                DateTime lastProdDate = DateTime.MinValue;

                try
                {

                    var sb = new StringBuilder();


                    sb.AppendLine(" SELECT top 1 [DS_ID] ,[ID_PCB]  ,[ID_TEST]  ,[JSON] FROM [DELTACHEF].[GENESIS_PCBFUNCTIONALTEST_PD]   ");
                    sb.AppendLine($" where [ID_PCB] = '{Seriennumer}' ");
                    sb.AppendLine(" order by ID_TEST desc ");






                    // create connection
                    using (OracleConnection con = new OracleConnection())
                    {

                        // create connection string using builder
                        OracleConnectionStringBuilder ocsb = new OracleConnectionStringBuilder();
                        ocsb.Password = "senspi2012";
                        ocsb.UserID = "prima_ro";
                        ocsb.DataSource = "//SLUORADB:1521/DT01";

                        // connect
                        con.ConnectionString = ocsb.ConnectionString;
                        con.Open();


                        OracleCommand command = con.CreateCommand();

                        command.CommandText = sb.ToString();

                        OracleDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            var St = ((string)reader["JSON"]);
                            var startInex = St.IndexOf("\"StartDT\":\"") + "\"StartDT\":\"".Length;
                            var datestring = St.Substring(startInex, "29.07.2022 06:47:27".Length);
                            DateTime.ParseExact(datestring, "dd.MM.yyyy HH:mm:ss", CultureInfo.InvariantCulture);



                        }
                    }
                }
                catch (Exception ex)
                {

                    throw ex;
                }
                return lastProdDate;

            }

            //connect





            //If Not rs.EOF Then




            //    If lngSerial = 0 Then
            //      StatusBar1.SimpleText = "LU: " & lngCount & "/" & ProgressBar1.Max & " " & strSerial & " " & Q1 & "=" & F1 & ", " & Q2 & " =" & F2 & " " & rsSQL("AnlageDatum")
            //    Else
            //      StatusBar1.SimpleText = "LU: " & lngCount & "/" & ProgressBar1.Max & " " & Format(lngSerial, "00000000") & " " & Q1 & "=" & F1 & ", " & Q2 & " =" & F2 & " " & rsSQL("AnlageDatum")
            //    End If


            //    'Debug.Print Format(lngSerial, "00000000") & " " & Q1 & "=" & F1 & ", " & Q2 & " =" & F2
            //    DoEvents

            //    If Not rsSQL Is Nothing Then


            //      ' Reihenfolge wieder zurückgesetzt Q1 < Q2   2018-04-19
            //      If Q1<Q2 Then
            //         ' richtig herum
            //         rsSQL("Q1_SOLL") = Q1
            //         rsSQL("FEHLER_Q1") = F1


            //         rsSQL("Q2_SOLL") = Q2
            //         rsSQL("FEHLER_Q2") = F2
            //      Else
            //         ' Q2 < Q1 => Tauschen
            //         rsSQL("Q1_SOLL") = Q2
            //         rsSQL("FEHLER_Q1") = F2


            //         rsSQL("Q2_SOLL") = Q1
            //         rsSQL("FEHLER_Q2") = F1
            //      End If


            //        Debug.Print rs("ZAEHLERNUMMER")

            //        If Val(rsSQL("SerienNrNebenzaehlerLU") &"") = 0 And lngSerial > 0 Then
            //            rsSQL("SerienNrNebenzaehlerLU") = lngSerial
            //        End If


            //        rsSQL("Herkunft") = "L"
            //        rsSQL("ErgaenzungDatum") = Now


            //        rsSQL.Update
            //        Debug.Print lngCount &"/" & ProgressBar1.Max & " OK " & lngSerial & " / " & strSerial
            //    End If
            //Else



            //       StatusBar1.SimpleText = lngCount & "/" & ProgressBar1.Max & " unbekannte SNr: " & lngSerial & " / " & strSerial
            //        Debug.Print StatusBar1.SimpleText & " " & Now
            //    'Debug.Print lngCount & "/" & ProgressBar1.Max & " unbekannt " & Format(lngSerial, "00000000") & " / " & strSerial
            //    DoEvents


            //    'rsSQL("Q1_SOLL") = 0
            //    'rsSQL("Herkunft") = "u"
            //    'rsSQL("Info") = "unbekannt in LU"
            //    'rsSQL.Update


            //End If
            //Exit Sub
            //Errorhandler:
            //                        Dim errnum As Long
            //   Dim errdesc As String
            //   errnum = Err.Number
            //   errdesc = Err.Description
            //   PrintStatus "Fehler " & errnum & " in Ergaenze():  " & errdesc & " SQL=" & strSQL
            //End Sub


        }
    }