using System;
using System.Collections.Generic;
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/EasyAccess")]
    public class EasyAccessController : ApiController
    {
        public static class GlobalConfig
        {
            public static Lazy<String> connectionString = new Lazy<String>(()
            =>
            System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString
            );
        }



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

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

                    StringBuilder query = new StringBuilder();
                    query.AppendLine("SELECT *                                                                                        ");
                    query.AppendLine("  FROM [Auftrag].[dbo].[Cordonel_PressureTest] pt                                                                   ");
                    query.AppendLine("  inner join Cordonel_PressureTest_He  he on pt.CordonelPressureTest_Id = he.CordonelPressureTestHe_TestID          ");
                    query.AppendLine("  left outer join  Druckpruefung c on pt.[CordonelPressureTest_PcbId] = c.FabNr                                          ");
                    query.AppendLine(" where 1 = 1                                                                                 ");

                    if (!string.IsNullOrEmpty(Auftrag))
                    {
                        query.AppendLine($"  and pt.[CordonelPressureTest_FertigungsAuftragsNr] = {Auftrag}                                               ");
                    }
                    query.AppendLine(" order by 1 desc                                                                                 ");

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

                        var sb = new StringBuilder();
                        try
                        {
                            sb.Append(item["CordonelPressureTest_Id"]).Append(";");
                            sb.Append(item["CordonelPressureTest_PcbId"]).Append(";");
                            sb.Append(item["CordonelPressureTest_FertigungsAuftragsNr"]).Append(";");
                            sb.Append(item["CordonelPressureTest_Date"]).Append(";");
                            sb.Append(item["CordonelPressureTest_Valid"]).Append(";");
                            sb.Append(item["CordonelPressureTest_IsDeleted"]).Append(";");
                            sb.Append(item["CordonelPressureTest_Text"]).Append(";");
                            sb.Append(item["CordonelPressureTestHe_LeakRate"]).Append(";");
                            sb.Append(item["CordonelPressureTestHe_TestPressure"]).Append(";");
                            sb.Append(item["CordonelPressureTestHe_TesterName"]).Append(";");

                            if (item["Pruefgang"] != DBNull.Value)
                            {
                                sb.Append(item["Pruefgang"]).Append(";");
                                sb.Append(item["Datum"]).Append(";");
                                sb.Append(item["StartDruck"]).Append(";");
                                sb.Append(item["EndDruck"]).Append(";");
                                sb.Append(item["Pruefzeit"]).Append(";");
                                sb.Append(item["Pruefer"]).Append(";");
                                sb.Append(item["Dicht"]).Append(";");
                            }
                            else
                            {
                                sb.Append("keine Kottmann Prüfung gefunden;;;;;;;");
                            }

                            retDic.Add(new Tuple<string, string>(item["CordonelPressureTest_Id"].ToString(), sb.ToString()));

                        }
                        catch (Exception ex)
                        {

                            throw;
                        }


                    }

                    var sbRet = new StringBuilder();


                    sbRet.Append("CordonelPressureTest_Id").Append(";");
                    sbRet.Append("CordonelPressureTest_PcbId").Append(";");
                    sbRet.Append("CordonelPressureTest_FertigungsAuftragsNr").Append(";");
                    sbRet.Append("CordonelPressureTest_Date").Append(";");
                    sbRet.Append("CordonelPressureTest_Valid").Append(";");
                    sbRet.Append("CordonelPressureTest_IsDeleted").Append(";");
                    sbRet.Append("CordonelPressureTest_Text").Append(";");
                    sbRet.Append("CordonelPressureTestHe_LeakRate").Append(";");
                    sbRet.Append("CordonelPressureTestHe_TestPressure").Append(";");
                    sbRet.Append("CordonelPressureTestHe_TesterName").Append(";");
                    sbRet.Append("Pruefgang").Append(";");
                    sbRet.Append("Datum").Append(";");
                    sbRet.Append("StartDruck").Append(";");
                    sbRet.Append("EndDruck").Append(";");
                    sbRet.Append("Pruefzeit").Append(";");
                    sbRet.Append("Pruefer").Append(";");
                    sbRet.Append("Dicht").Append(";");
                    sbRet.Append("HE_TestPoints").Append(";");
                    sbRet.AppendLine();
                    foreach (var item in retDic)
                    {
                        dt = dataAccess.ExecuteQuery($" select * from Cordonel_PressureTest_TestPoints where CordonelPressureTest_Id = {item.Item1} order by  CordonelPressureTest_TestPointNr ASC");
                        var sbTp = new StringBuilder();

                        foreach (var itemTP in dt.Select())
                        {
                            sbTp.Append("Nr ");
                            sbTp.Append(itemTP["CordonelPressureTest_TestPointNr"]);
                            sbTp.Append("= ");
                            sbTp.Append(itemTP["CordonelPressureTest_TestPointResult"]);
                            sbTp.Append(" and ");
                        }
                        sbRet.Append(item.Item2);
                        sbRet.Append(sbTp.ToString());
                        sbRet.AppendLine(";");
                    }



                    HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);


                    result.Content = new StringContent(sbRet.ToString());
                    result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
                    result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); //attachment will force download
                    result.Content.Headers.ContentDisposition.FileName = "RecordExport.csv";


                    return result;


                }


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


        [Route("GetCordonelCalibrationData")]
        public async Task<HttpResponseMessage> GetCordonelCalibrationData(String PcbId = "", string Auftrag = "", DateTime? Start = null, DateTime? End = null, int? Testbench = null)
        {
            try
            {
                var returnPw = "Not assigned";
                var resDic = new List<Tuple<string, string>>();

                if (!Start.HasValue)
                {
                    Start = new DateTime(2010, 01, 01);
                }
                if (!End.HasValue)
                {
                    End = new DateTime(2050, 01, 01);
                }
                using (var dataAccess = new SqlDataAccess(GlobalConfig.connectionString.Value))
                {
                    StringBuilder query = new StringBuilder();
                    query.AppendLine(" select                                                                                                                                       ");
                    query.AppendLine(" m.Seriennummer                                                                                                                                             ");
                    query.AppendLine(" , aps.AuftragNr                                                                                                                                            ");
                    query.AppendLine(" , aps.PositionNr                                                                                                                                           ");
                    query.AppendLine(" ,zDate.[PcbId]                                                                                                                                             ");
                    query.AppendLine(" ,[MeterSize]                                                                                                                                               ");
                    query.AppendLine(" ,zDate.Id as PreadjustmentID                                                                                                                               ");
                    query.AppendLine(" ,[ZeroOffset1]                                                                                                                                             ");
                    query.AppendLine(" ,[ZeroOffset2]                                                                                                                                             ");
                    query.AppendLine(" ,[ZeroOffset3]                                                                                                                                             ");
                    query.AppendLine(" ,[FirstHitUpdatePeriod]                                                                                                                                    ");
                    query.AppendLine(" ,[FirstHitShift]                                                                                                                                           ");
                    query.AppendLine(" ,[FirstHitPercent1]                                                                                                                                        ");
                    query.AppendLine(" ,[FirstHitPercent2]                                                                                                                                        ");
                    query.AppendLine(" ,[FirstHitPercent3]                                                                                                                                        ");
                    query.AppendLine(" ,[ToFTempOffset1]                                                                                                                                          ");
                    query.AppendLine(" ,[ToFTempOffset2]                                                                                                                                          ");
                    query.AppendLine(" ,[ToFTempOffset3]                                                                                                                                          ");
                    query.AppendLine(" ,zDate.[date] as ZeroflowDate                                                                                                                              ");
                    query.AppendLine(" ,[successful]                                                                                                                                              ");
                    query.AppendLine(" , qc.Date as Q3CalibrationDate                                                                                                                             ");
                    query.AppendLine(" , qc.P1Cal                                                                                                                                                 ");
                    query.AppendLine(" , qc.P1Offset                                                                                                                                              ");
                    query.AppendLine(" , qc.P2Cal                                                                                                                                                 ");
                    query.AppendLine(" , qc.P2Offset                                                                                                                                              ");
                    query.AppendLine(" , qc.P3Cal                                                                                                                                                 ");
                    query.AppendLine(" , qc.P3Offset                                                                                                                                              ");
                    query.AppendLine("          FROM Genesis_Meter m                                                                                                                              ");
                    query.AppendLine("          inner join MapPcbIdToSerialNumber map on m.Seriennummer = map.MapPcbIdToSerialNumber_SerialNumber                                                 ");
                    query.AppendLine("          inner join AuftragPositionSerienNr aps on aps.SerienNr = m.Seriennummer and aps.Wiederholungen = -1                                               ");
                    query.AppendLine("          inner join  (                                                                                                                                     ");
                    query.AppendLine("                       SELECT t1.* , t1.DATE AS STARTDATE, case when t2.DATE is null then CURRENT_TIMESTAMP else t2.DATE end AS ENDDATE                     ");
                    query.AppendLine("                     FROM [Auftrag].[dbo].[CordonelMeterCalibrationResults]  t1                                                                             ");
                    query.AppendLine("                 LEFT JOIN [Auftrag].[dbo].[CordonelMeterCalibrationResults]  t2 ON t2.PcbId = t1.PcbId and t2.DATE =                                       ");                                
                    query.AppendLine("                             (SELECT MIN(DATE) FROM [Auftrag].[dbo].[CordonelMeterCalibrationResults]  t3 WHERE t3.DATE > t1.DATE and  t3.PcbId = t1.PcbId) ");
                    query.AppendLine("                                 ) as zDate on zDate.PcbId = map.MapPcbIdToSerialNumber_PcbId                                                               ");
                    query.AppendLine("          left outer join Cordonel_Q3Calibration qc on qc.PcbId = zDate.PcbId and qc.Date between zDate.STARTDATE and zDate.ENDDATE                         ");
                    query.AppendLine(" order by zDate.[date] desc                                                                                                                                 ");
                    var dt = dataAccess.ExecuteQuery(query.ToString());

                    var sb = new StringBuilder();


                    sb.Append("Seriennummer;AuftragNr;PositionNr;PcbId;MeterSize;PreadjustmentID;ZeroOffset1;ZeroOffset2;ZeroOffset3;FirstHitUpdatePeriod;FirstHitShift;FirstHitPercent1;FirstHitPercent2;FirstHitPercent3;ToFTempOffset1;ToFTempOffset2;ToFTempOffset3;ZeroflowDate;successful;Q3CalibrationDate;P1Cal;P1Offset;P2Cal;P2Offset;P3Cal;P3Offset");
                    
                    sb.AppendLine();
                    foreach (var item in dt.Select())
                    {
                        try
                        {

                            foreach (var cell in item.ItemArray)
                            {
                                sb.Append(cell).Append(";");
                            }
                            sb.AppendLine();


                        }
                        catch (Exception ex)
                        {

                            throw;
                        }


                    }


                    HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);


                    result.Content = new StringContent(sb.ToString());
                    result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
                    result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); //attachment will force download
                    result.Content.Headers.ContentDisposition.FileName = "CordonelCalibrationData.csv";


                    return result;


                }


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



        [Route("GetTestDataFull")]
        public async Task<HttpResponseMessage> GetTestDataFull(String PcbId = "", string Auftrag = "", DateTime? Start = null, DateTime? End = null, int? Testbench = null)
        {
            try
            {
                var returnPw = "Not assigned";
                var resDic = new List<Tuple<string, string>>();

                if (!Start.HasValue)
                {
                    Start = new DateTime(2010, 01, 01);
                }
                if (!End.HasValue)
                {
                    End = new DateTime(2050, 01, 01);
                }
                using (var dataAccess = new SqlDataAccess(GlobalConfig.connectionString.Value))
                {
                    StringBuilder query = new StringBuilder();
                    query.AppendLine(" select                                                            ");
                    query.AppendLine(" apsn.AuftragNr,                                                    ");
                    query.AppendLine(" apsn.PositionNr,                                                    ");
                    query.AppendLine(" apsn.SerienNr,                                                    ");
                    query.AppendLine(" pg.PruefgangNr,                                                   ");
                    query.AppendLine(" pg.PruefstationNr,                                                   ");
                    query.AppendLine(" pg.Datum,                                                   ");
                    query.AppendLine(" pg.Pruefzeit,                                                   ");
                    query.AppendLine(" pg.Vorlauftemperatur,                                                 ");
                    query.AppendLine(" apsn.Wiederholungen,                                              ");
                    query.AppendLine(" apsn.Einbauplatz,                                                 ");
                    query.AppendLine(" pg.Nennweite,                                                     ");
                    query.AppendLine(" idt.Baulaenge,                                                     ");
                    query.AppendLine(" pg.Pruefzeit,                                                   ");

                    query.AppendLine(" PP1_Soll,PP1_Ist,PP1_Ist_V,                      ");
                    query.AppendLine(" pf.PP1_Fehler,                          ");
                    query.AppendLine(" PP2_Soll,PP2_Ist,PP2_Ist_V,                      ");
                    query.AppendLine(" pf.PP2_Fehler,                          ");
                    query.AppendLine(" PP3_Soll,PP3_Ist,PP3_Ist_V,                      ");
                    query.AppendLine(" pf.PP3_Fehler,                          ");
                    query.AppendLine(" PP4_Soll,PP4_Ist,PP4_Ist_V,                      ");
                    query.AppendLine(" pf.PP4_Fehler,                          ");
                    query.AppendLine(" PP5_Soll,PP5_Ist,PP5_Ist_V,                      ");
                    query.AppendLine(" pf.PP5_Fehler,                          ");
                    query.AppendLine(" PP6_Soll,PP6_Ist,PP6_Ist_V,                      ");
                    query.AppendLine(" pf.PP6_Fehler,                          ");
                    query.AppendLine(" PP7_Soll,PP7_Ist,PP7_Ist_V,                      ");
                    query.AppendLine(" pf.PP7_Fehler,                          ");
                    query.AppendLine(" PP8_Soll,PP8_Ist,PP8_Ist_V,                      ");
                    query.AppendLine(" pf.PP8_Fehler,                          ");
                    query.AppendLine(" PP9_Soll,PP9_Ist,PP9_Ist_V,                      ");
                    query.AppendLine(" pf.PP9_Fehler,                          ");
                    query.AppendLine(" PP10_Soll,PP10_Ist,PP10_Ist_V,                      ");
                    query.AppendLine(" pf.PP10_Fehler                                            ");


                    for (int p = 1; p <= 3; p++)
                    {
                        for (int f = 1; f <= 10; f++)
                        {
                            query.AppendLine($",pf{p}.PP{f}_Fehler");
                        }
                    }


                    query.AppendLine(" from [Auftrag].[dbo].[AuftragPositionSerienNr] apsn                ");
                    query.AppendLine(" inner join [Auftrag].[dbo].[AuftragPosition_Gesamt] apg on apg.AuftragNr = apsn.AuftragNr and apg.PositionNr = apsn.PositionNr  ");

                    query.AppendLine(" inner join [Auftrag].[dbo].[Pruefgang] pg on apsn.[Pruefgangnr] = pg.PruefgangNr ");

                    query.AppendLine(" inner join [Auftrag].[dbo].Prueffehler pf on pg.[Pruefgangnr] = pf.PruefgangNr and pf.SerienNr = apsn.SerienNr ");

                    query.AppendLine(" left join [Auftrag].[dbo].PrueffehlerGenesisPfade pf1 on pg.[Pruefgangnr] = pf1.PruefgangNr and pf1.SerienNr = apsn.SerienNr and pf1.[Pfad] = 1 ");

                    query.AppendLine(" left join [Auftrag].[dbo].PrueffehlerGenesisPfade pf2 on pg.[Pruefgangnr] = pf2.PruefgangNr and pf2.SerienNr = apsn.SerienNr and pf2.[Pfad] = 2 ");

                    query.AppendLine(" left join [Auftrag].[dbo].PrueffehlerGenesisPfade pf3 on pg.[Pruefgangnr] = pf3.PruefgangNr and pf3.SerienNr = apsn.SerienNr and pf3.[Pfad] = 3 ");

                    query.AppendLine("  left outer join Identnr idt on idt.IdentNr = apg.Identnr ");

                    //query.AppendLine("  left outer join  [MapPcbIdToSerialNumber] map on map.MapPcbIdToSerialNumber_SerialNumber = apsn.SerienNr  ");



                    query.AppendLine($" where pg.Datum >= '{Start}' and pg.Datum <= '{End}' ");




                    query.AppendLine("  and apsn.SerienNr in (                                                                                                                                                                                ");
                    query.AppendLine("              SELECT                                                                                                                                                                                     ");
                    query.AppendLine("              [MapPcbIdToSerialNumber_SerialNumber]                                                                                                                                                      ");
                    query.AppendLine("              FROM [Auftrag].[dbo].[MapPcbIdToSerialNumber]                                                                                                                                              ");
                    query.AppendLine("                          inner join [Auftrag].[dbo].[AuftragPositionSerienNr] apsn on apsn.SerienNr = [MapPcbIdToSerialNumber_SerialNumber] and apsn.SerienNr not in (20702497,20702499,20717438)       ");
                    query.AppendLine("                                                                                                                                                                                                         ");
                    query.AppendLine("                          inner join[Auftrag].[dbo].[AuftragPosition_Gesamt] apg on apg.AuftragNr = apsn.AuftragNr and apg.PositionNr = apsn.PositionNr                                ");
                    query.AppendLine("              			inner join[Auftrag].[dbo].[Pruefgang] pg on apsn.[Pruefgangnr] = pg.PruefgangNr  ");

                    if (Testbench.HasValue)
                    {
                        query.AppendLine($" and pg.PruefstationNr in ({Testbench.Value})                                                                   ");
                    }


                    query.AppendLine("              			inner join[Auftrag].[dbo].Prueffehler pf on pg.[Pruefgangnr] = pf.PruefgangNr and pf.SerienNr = [MapPcbIdToSerialNumber_SerialNumber]                                          ");
                    query.AppendLine("                                                                                                                                                                                                         ");
                    query.AppendLine($"                         where pg.Datum >= '{Start}' and pg.Datum <= '{End}' ");
                    query.AppendLine("                  group by [MapPcbIdToSerialNumber_SerialNumber]                                                                                                                                         ");
                    query.AppendLine("                          )                                                                                                                                                           ");
                    query.AppendLine("                                                                                                                                                                                                         ");
                    // query.AppendLine("     and pf.PP3_Fehler is not null                                                                                                                                                                          ");
                    query.AppendLine("                                                                                                                                                                                                         ");
                    query.AppendLine(" 	order by apsn.SerienNr desc, apsn.Wiederholungen asc                                                                                                                                                   ");



                    var dt = dataAccess.ExecuteQuery(query.ToString());

                    var sb = new StringBuilder();


                    sb.Append("AuftragNr;PositionNr;SerienNr;PruefgangNr;PruefstationNr;Datum;Pruefzeit;Temp;Wiederholungen;Einbauplatz;Nennweite;Baulaenge;");



                    for (int i = 1; i <= 10; i++)
                    {
                        sb.Append($"PP{i}_Soll [m3/h];PP{i}_Ist[m3/h];PP{i}_Ist_V[l];PP{i}_Fehler[%];");
                    }


                    for (int p = 1; p <= 3; p++)
                    {
                        for (int f = 1; f <= 10; f++)
                        {
                            sb.Append($"Pfad {p} PP{f}_Fehler;");
                        }
                    }


                    sb.AppendLine();
                    foreach (var item in dt.Select())
                    {
                        try
                        {

                            foreach (var cell in item.ItemArray)
                            {
                                sb.Append(cell).Append(";");
                            }
                            sb.AppendLine();


                        }
                        catch (Exception ex)
                        {

                            throw;
                        }


                    }


                    HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);


                    result.Content = new StringContent(sb.ToString());
                    result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
                    result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); //attachment will force download
                    result.Content.Headers.ContentDisposition.FileName = "RecordExport.csv";


                    return result;


                }


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






    }
}