common/Service/MeterProcessState/Controllers/PushControler.cs

using LaaPackages.Features.Cordonel.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using System.Web.Http;
using Xylem.Common.Logic.ServiceCore;


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



        [Route("SetOrderisFinish")]
        public async Task<HttpResponseMessage> SetOrderisFinish(int Auftrag, int Pos)
        {
            try
            {
                throw new NotImplementedException("TODO Roland");
      
            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.ToString() + "<br >" + ex.InnerException.ToString());
            }
        }

        public class retItem {

            public int OrderNr { get; internal set; }
            public int pos { get; internal set; }
            public int Pcb { get; internal set; }
            public DateTime kitronDate { get; internal set; }
    
            public int SerienNr { get; internal set; }
        }


        [Route("GetOrderProductionDates")]
        public async Task<HttpResponseMessage> GetOrderProductionDates(int? Order = null, int? Pos = null, int? PcbId = null)
        {
            try
            {

                return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() =>
                {
                    var ret = new List<retItem>(); 
                    
                    using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value))
                    {


                        var sbq = new StringBuilder();
                        sbq.AppendLine("select distinct AuftragNr, PositionNr, aps.SerienNr, map.MapPcbIdToSerialNumber_PcbId " +
                            "from Auftrag.dbo.AuftragPositionSerienNr aps " +
                            "inner join Auftrag.dbo.MapPcbIdToSerialNumber map " +
                            "on aps.SerienNr = map.MapPcbIdToSerialNumber_SerialNumber " +
                            "" +
                            "where  Wiederholungen = -1 ");
                        if (PcbId.HasValue)
                        {
                            sbq.AppendLine($" and map.MapPcbIdToSerialNumber_PcbId = {PcbId.Value}");
                        }
                        else if(Order.HasValue)
                        {
                            sbq.AppendLine($" and aps.AuftragNr  = {Order.Value}");
                            if (Pos.HasValue)
                            {
                                sbq.AppendLine($" and aps.PositionNr  = {Pos.Value}");
                            }
                      
                        }
                        else
                        {
                            return ret;
                        }
                        var OrderRow = dataacces.ExecuteQuery(sbq.ToString());

                        foreach (DataRow item in OrderRow.Rows)
                        {
                            try
                            {
                                var retC = new retItem();
                                retC.OrderNr = (Int32)item["AuftragNr"];
                                retC.pos = (short)item["PositionNr"];
                                //retC.SerienNr = (Int32)item["SerienNr"];
                                retC.Pcb = int.Parse(item["MapPcbIdToSerialNumber_PcbId"].ToString());

                                var dtPrdDate = dataacces.ExecuteQuery($"sELECT * FROM OPENQUERY(MyOraDB, ' SELECT JSON FROM DELTACHEF.GENESIS_PCBFUNCTIONALTEST_PD WHERE ID_PCB = ''{retC.Pcb}'' order by ID_TEST desc  ');");
                                if (dtPrdDate.Rows.Count >= 1)
                                {
                                    var St = dtPrdDate.Rows[0]["JSON"].ToString();
                                    var startInex = St.IndexOf("\"StartDT\":\"") + "\"StartDT\":\"".Length;
                                    var datestring = St.Substring(startInex, "29.07.2022 06:47:27".Length);
                                    var olastProdDate = new DateTime();
                                    if (DateTime.TryParse(datestring, out olastProdDate))
                                    {
                                        retC.kitronDate = olastProdDate;
                                    }
                                    else
                                    {
                                        retC.kitronDate = DateTime.ParseExact(datestring, "dd.MM.yyyy HH:mm:ss", CultureInfo.InvariantCulture);
                                    }
                                    
                                  
                                }
                                else
                                {
                                    retC.kitronDate = DateTime.MinValue;
                                }
                                ret.Add(retC);

                            }
                            catch (Exception ex)
                            {
                                ret.Add(new retItem() {  SerienNr = 1, pos = 1, OrderNr = 1, kitronDate = DateTime.MinValue, Pcb = 1});
                            }
                      
                           
                        }
                    }
                    return ret;
                }));

            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.ToString() + "<br >" + ex.InnerException.ToString());
            }
        }



        
             



    }
}