HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using ID2.Manager.Data.Models;

namespace ID2.Manager.Dapper.Repository

{

    public class ProjectRepository : BaseRepository

    {

        public ProjectRepository(string connectionStr) : base(connectionStr) { }

        public IEnumerable<ProjectInfo> GetAllProjectList()

        {

            try

            {

                string query = $@"

;with Prj as

(

    select ProjectID

	      ,Code

		  ,[Name]

          ,isnull(Description,'') Description

	      ,isnull(ParentID,'') GroupID

          ,convert(varchar(255),'') GroupName

		  ,1 [Level]

	from   dbo.Projects

	where  ParentID is null

	UNION ALL

	select p.ProjectID

	      ,p.Code

		  ,p.[Name]

	      ,p.Description

          ,p.ParentID GroupID

		  ,pp.[Name] GroupName

          ,pp.[Level]+1 [Level]

    FROM   dbo.Projects p

               inner join Prj pp ON p.ParentID=pp.ProjectID

)

select *

from   prj

order  by [Level], [Name];";

                return Query<ProjectInfo>(query);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public ProjectInfo GetProjectInfo(string ProjectID)

        {

            try

            {

                string query = $@"

;with Prj as

(

    select ProjectID

	      ,Code

		  ,[Name]

          ,isnull(Description,'') Description

	      ,isnull(ParentID,'') GroupID

          ,convert(varchar(255),'') GroupName

		  ,1 [Level]

	from   dbo.Projects

	where  ParentID is null

	UNION ALL

	select p.ProjectID

	      ,p.Code

		  ,p.[Name]

	      ,p.Description

          ,p.ParentID GroupID

		  ,pp.[Name] GroupName

          ,pp.[Level]+1 [Level]

    FROM   dbo.Projects p

               inner join Prj pp ON p.ParentID=pp.ProjectID

)

select *

from   prj

where  ProjectID=@ProjectID";

                return Query<ProjectInfo>(query, new { ProjectID }).FirstOrDefault();

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public bool SetProjectData(ProjectInfo projectInfo, List<ID2ProjectInfo> id2ProjectList)

        {

            bool isSuccess = false;

            try

            {

                using (var transaction = base.BeginTransaction())

                {

                    string query = string.Empty;

                    if (projectInfo.ProjectID == null)

                    {

                        query = $@"

declare @newproject_uid table(projectId varchar(36))

insert into dbo.Projects(ProjectID, Code, [Name], [Description])

output inserted.ProjectID into @newproject_uid

values (lower(newid()), @Code, @Name, @Description)

if @@rowcount > 0

begin

    select projectId from @newproject_uid

end

else

begin

    select '' projectId

end;";

                        projectInfo.ProjectID = base.ExecuteScalar<string>(query, projectInfo, transaction);

                    }

                    else

                    {

                        query = $@"

update dbo.Projects

set    Code=@Code

      ,[Name]=@Name

      ,[Description]=@Description

where  ProjectID=@ProjectID;";

                        base.Execute(query, projectInfo, transaction);

                    }

                    if (id2ProjectList.Count > 0)

                    {

                        var parameters = new Dictionary<string, object>

                        {

                            { "ParentID", projectInfo.ProjectID }

                        };

                        string id2NameList = string.Join("','", id2ProjectList.Select(x => x.Name).ToList());

                        if (id2NameList.Length > 0)

                        {

                            query = $@"delete dbo.Projects where ParentID=@ParentID and [Code] not in ('{id2NameList}');";

                            base.Execute(query, parameters, transaction);

                        }

                    }

                    foreach (ID2ProjectInfo prj in id2ProjectList)

                    {

                        if (!string.IsNullOrEmpty(prj.Name))

                        {

                            var parameters = new Dictionary<string, object>

                            {

                                { "ParentID", projectInfo.ProjectID },

                                { "Name", prj.Name },

                                { "Desc", prj.Desc }

                            };

                            query = $@"

if exists(select * from dbo.Projects where ParentID=@ParentID and Code=@Name)

begin

    update dbo.Projects

    set    [Description]=@Desc

    where  ParentID=@ParentID and Code=@Name

end

else

begin

    insert into dbo.Projects

    (

         ProjectID

        ,ParentID

        ,Code

        ,[Name]

        ,[Description]

    )

    values 

    (

         lower(newid())

        ,@ParentID

        ,@Name

        ,@Name

        ,@Desc

    )

end;";

                            base.Execute(query, parameters, transaction);

                        }

                    }

                    transaction.Commit();

                    isSuccess = true;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return isSuccess;

        }

        public bool SetProjectGroupData(ProjectInfo projectInfo)

        {

            bool isSuccess = false;

            try

            {

                using (var transaction = base.BeginTransaction())

                {

                    string query = string.Empty;

                    query = $@"

delete dbo.Projects where ParentID=@ProjectID;

delete dbo.Projects where ProjectID=@ProjectID;";

                    base.Execute(query, projectInfo, transaction);

                    transaction.Commit();

                    isSuccess = true;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return isSuccess;

        }

    }

}