HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.SQLite;

using ID2.Manager.Data.Models;

namespace ID2.Manager.Dapper.Repository

{

    public class ID2Repository : BaseRepository

    {

        public ID2Repository(string connectionStr, DatabaseType databaseType) : base(connectionStr, databaseType) { }

        public IEnumerable<ID2ProjectInfo> GetID2ProjectList()

        {

            IEnumerable<ID2ProjectInfo> results = null;

            try

            {

                string query = $@"

select    pj.Id, pj.ProjectID, pj.ProjectNo, pj.Name, pj.Desc, pj.Unit, pj.NPSUnit, pj.Path, pj.AreaName, pj.PlantName, pj.CreatedDate, pj.UpdatedDate

         ,ty.UID as DBTypeID ,ty.Name as DBTypeName

         ,db.Host, db.User, db.Password, db.FilePath as DBPath

from      (

              select p.*

              from   Projects p

                     inner join

                     (

                         select Min(Id) as Id, Name

                         from   Projects

                         where  ifnull(Name, '') <> ''

                         group by Name

                     ) g on p.Id=g.Id

          ) pj

              inner join DBSettings db on pj.Id=db.Projects_UID

              inner join DBTypes ty on db.DBTypes_UID=ty.UID

order  by pj.Id;";

                results = Query<ID2ProjectInfo>(query);

            }

            catch

            {

                results = new List<ID2ProjectInfo>();

            }

            return results;

        }

    }

}