using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Xylem.Common.Logic.ServiceCore;

namespace Xylem.Common.Logic.ProductionOrderCore.Vako
{
    /// <summary>
    /// VAKO extractor from data base
    /// </summary>
    public static class VakoDbAdapter
    {
        /// <summary>
        /// Collect the VAKO from data base
        /// </summary>
        /// <param name="dbConnectionString"></param>
        /// <param name="fertigungsAuftragsNr"></param>
        /// <returns></returns>
        public static RawVako GetRawVako(String dbConnectionString, Int32 fertigungsAuftragsNr)
        {
            using (var dataAccess = new SqlDataAccess(dbConnectionString)) 
            {
                var sb = new StringBuilder();

                sb.AppendLine($" select distinct ident.VakoCode, ident.Identnr,ident.Typ , ident.Nennweite,ident.Baulaenge   from  Auftrag.dbo.AuftragPosition_Gesamt auftraginfo     ");
                sb.AppendLine($"     left outer join Auftrag.dbo.Identnr ident on auftraginfo.IdentNr = ident.IdentNr   ");
                sb.AppendLine($"     where FertigungsauftragNr = {fertigungsAuftragsNr}                                 ");

                var mapData = dataAccess.ExecuteQuery(sb.ToString());
                if (mapData.Rows.Count != 1)
                {
                    return new RawVako();
                }

                return new RawVako
                {
                    VakoString = mapData.Rows[0]["VakoCode"].ToString(),
                    VakoIdent = (Int32)mapData.Rows[0]["Identnr"],
                    Type = mapData.Rows[0]["Typ"].ToString(),
                    Dn = (Int32)mapData.Rows[0]["Nennweite"],
                    Length = mapData.Rows[0]["Baulaenge"].GetType() == typeof(System.DBNull) ? 0: (Int32)mapData.Rows[0]["Baulaenge"],
                    
                };
            }
        }

        /// <summary>
        /// Collect the VAKO properties
        /// </summary>
        /// <param name="dbConnectionString"></param>
        /// <param name="basis"></param>
        /// <returns></returns>
        public static List<VakoMap> GetVakoMapping(String dbConnectionString, String basis)
        {
            using (var dataAccess = new SqlDataAccess(dbConnectionString))
            {

                var sb = new StringBuilder();

                sb.AppendLine($"  SELECT TOP(1000) [Id]              ");
                sb.AppendLine($" ,[Schluessel]                       ");
                sb.AppendLine($" ,[Basis]                            ");
                sb.AppendLine($" ,[Basislist]                        ");
                sb.AppendLine($" ,[Name]                             ");
                sb.AppendLine($" ,[Bezeichnung]                      ");
                sb.AppendLine($" ,[Stelle]                           ");
                sb.AppendLine($" ,[Laenge]                           ");
                sb.AppendLine($" ,[Charcode]                         ");
                sb.AppendLine($" ,[Wert]                             ");
                sb.AppendLine($" ,[Bemerkung]                        ");
                sb.AppendLine($" ,[NachSAP]                          ");
                sb.AppendLine($" ,[SAPText]                          ");
                sb.AppendLine($" ,[SAPReihenfolge]                   ");
                sb.AppendLine($" ,[AnlageDatum]                      ");
                sb.AppendLine($" FROM [Auftrag].[dbo].[VAKO_Merkmale] ");
                sb.AppendLine($" where [Basis] = '{basis}'            ");

                var mapData = dataAccess.ExecuteQuery(sb.ToString());
                var ret = new List<VakoMap>();
                //if (MapData.Rows.Count != 1)
                //{
                //    return ret;
                //}
                foreach (var rawRow in mapData.Rows)
                {
                    if (rawRow is DataRow row)
                    {
                        ret.Add(new VakoMap()
                        {
                            Basis = basis,
                            Id = (Int32)row["Id"],
                            Schluessel = row["Schluessel"].ToString(),
                            Basislist = row["Basislist"].ToString(),
                            Name = row["Name"].ToString(),
                            Bezeichnung = row["Bezeichnung"].ToString(),
                            Stelle = (Int32)row["Stelle"],
                            Laenge = (Int32)row["Laenge"],
                            Charcode = row["Charcode"].ToString(),
                            Wert = row["Wert"].ToString(),
                            Bemerkung = row["Bemerkung"].ToString(),
                            NachSap = (Boolean)row["NachSap"],
                            SapText = row["SAPText"].ToString(),
                            SapReihenfolge = row["SAPReihenfolge"] is DBNull ? 0 : (Int32)row["SAPReihenfolge"],
                            AnlageDatum = (DateTime)row["AnlageDatum"],
                        });
                    }
                }

                return ret;
            }
        }
    }
}