HYTOS

using Dapper;

using DapperParameters;

using ID2.Manager.Dapper.Entities;

using ID2.Manager.Data.Models;

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ID2.Manager.Dapper.Repository

{

    public class MarkusRepository : BaseRepository

    {

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

        public bool Insert(string ProjectNo,string Name)

        {

            bool result = false;

            var convertDocID = CreateConvertDoc(ProjectNo, Name + ".pdf", Name);

            if (convertDocID != null)

            {

                var documentItemID = CreateOrUPdateDocItem(

                    new DOCUMENTITEM

                    {

                        REVISION = "0",

                        PROJECT_NO = ProjectNo,

                        DOCUMENT_NO = Name,

                        GROUP_NO = Name,

                        DOCUMENT_NAME = Name,

                        ORIGINAL_FILE = Name,

                        DOCUMENT_ID = Name

                    });

                if (documentItemID != null)

                {

                    result = true;

                }

            }

            return result;

        }

        public bool InsertOrUpdateUsers(MEMBER member)

        {

            bool result = false;

            try

            {

                var oMember = GetMEMBER(member.ID);

                if(oMember?.Count() > 0)

                {

                    var tran = BeginTransaction();

                    string SQL = "UPDATE MEMBER" +

                        " SET " +

                        "DEPARTMENT = @DEPARTMENT" +

                        ",NAME = @NAME" +

                        ",POSITION = @POSITION" +

                        ",EMAIL_ADDRESS = @EMAIL_ADDRESS" +

                        ",PASSWORD = @PASSWORD" +

                        ",COMPANY = @COMPANY" +

                        ",CREATE_DATETIME = @CREATE_DATETIME" +

                        ",MODIFIED_DATETIME = @MODIFIED_DATETIME" +

                        ",ISLICENSE = @ISLICENSE" +

                        " WHERE ID = @ID";

                    var parameters = new DynamicParameters();

                    parameters.Add("@ID", member.ID);

                    parameters.Add("@DEPARTMENT", member.DEPARTMENT);

                    parameters.Add("@NAME", member.NAME);

                    parameters.Add("@POSITION", member.POSITION);

                    parameters.Add("@EMAIL_ADDRESS", member.EMAIL_ADDRESS);

                    parameters.Add("@PASSWORD", member.PASSWORD);

                    parameters.Add("@COMPANY", member.COMPANY);

                    parameters.Add("@CREATE_DATETIME", member.CREATE_DATETIME);

                    parameters.Add("@MODIFIED_DATETIME", member.MODIFIED_DATETIME);

                    parameters.Add("@ISLICENSE", member.ISLICENSE);

                    var rowsAffected = base.Execute(SQL, parameters, tran);

                    if (rowsAffected < 0)

                    {

                        result = false;

                    }

                    else

                    {

                        tran.Commit();

                    }

                }

                else

                {

                    var tran = BeginTransaction();

                    string SQL = "INSERT INTO MEMBER (ID,DEPARTMENT,NAME,POSITION,EMAIL_ADDRESS,PASSWORD,COMPANY,CREATE_DATETIME,MODIFIED_DATETIME,ISLICENSE) " +

                        "VALUES (@ID,@DEPARTMENT,@NAME,@POSITION,@EMAIL_ADDRESS,@PASSWORD,@COMPANY,@CREATE_DATETIME,@MODIFIED_DATETIME,@ISLICENSE)";

                    var parameters = new DynamicParameters();

                    parameters.Add("@ID", member.ID);

                    parameters.Add("@DEPARTMENT", member.DEPARTMENT);

                    parameters.Add("@NAME", member.NAME);

                    parameters.Add("@POSITION", member.POSITION);

                    parameters.Add("@EMAIL_ADDRESS", member.EMAIL_ADDRESS);

                    parameters.Add("@PASSWORD", member.PASSWORD);

                    parameters.Add("@COMPANY", member.COMPANY);

                    parameters.Add("@CREATE_DATETIME", member.CREATE_DATETIME);

                    parameters.Add("@MODIFIED_DATETIME", member.MODIFIED_DATETIME);

                    parameters.Add("@ISLICENSE", member.ISLICENSE);

                    var rowsAffected = base.Execute(SQL, parameters, tran);

                    if(rowsAffected < 0)

                    {

                        result = false;

                    }

                    else

                    {

                        tran.Commit();

                    }

                }

            }

            catch (Exception ex)

            {

                throw new Exception($"userId : {member.ID}",ex);

            }

            return result;

        }

        public bool InsertProperties(string ProjectNo)

        {

            bool result = false;

            try

            {

                var properties = new[] { "TileSorceStorage", "TileSorcePath" };

                foreach (var property in properties)

                {

                    var tran = BeginTransaction();

                    string SQL = "INSERT INTO [dbo].[PROPERTIES] ([TYPE], [PROPERTY], [VALUE])" +

                                " SELECT @TYPE,@ProjectNo, [VALUE]" +

                                " FROM [dbo].[PROPERTIES]" +

                                " WHERE [TYPE] = @TYPE AND [PROPERTY] = 'APAO'" +

                                " AND NOT EXISTS (" +

                                " SELECT 1" +

                                " FROM [dbo].[PROPERTIES]" +

                                "  WHERE [TYPE] =  @TYPE AND [PROPERTY] = @ProjectNo)";

                    var parameters = new DynamicParameters();

                    parameters.Add("@TYPE", property);

                    parameters.Add("@ProjectNo", ProjectNo);

                    var rowsAffected = base.Execute(SQL, parameters, tran);

                    tran.Commit();

                }

            }

            catch (Exception ex)

            {

                throw new Exception($"InsertProperties : {ProjectNo}", ex);

            }

            return result;

        }

        public IEnumerable<MEMBER> GetMEMBER(string id)

        {

            string SQL = "SELECT * FROM MEMBER WHERE ID = @ID";

            var parameters = new DynamicParameters();

            parameters.Add("@ID", id);

            return base.Query<MEMBER>(SQL, parameters);

        }

        public IEnumerable<ConvertDoc> GetConvertDoc(int status,string ProjectNo)

        {

            string SQL = "SELECT * FROM CONVERTER_DOC WHERE STATUS = @STATUS and PROJECT_NO = @PROJECT_NO";

            var parameters = new DynamicParameters();

            parameters.Add("@STATUS", status);

            parameters.Add("@PROJECT_NO", ProjectNo);

            var convertItems = base.Query<ConvertDoc>(SQL, parameters);

            return convertItems;

        }

        public int GetConvertDoc(string ProjectNo,string DOCUMENT_ID)

        {

            string SQL = "SELECT STATUS FROM CONVERTER_DOC WHERE PROJECT_NO = @PROJECT_NO and DOCUMENT_ID = @DOCUMENT_ID";

            var parameters = new DynamicParameters();

            parameters.Add("@PROJECT_NO", ProjectNo);

            parameters.Add("@DOCUMENT_ID", DOCUMENT_ID);

            return base.ExecuteScalar<int>(SQL, parameters);

        }

        //public IEnumerable<MarkupText> GetMarkupText(IEnumerable<string> ProjectNoList, IEnumerable<string> DocumentNoList)

        //{

        //    string SQL = "GET_MARKUPDATA";

        //    var parameters = new DynamicParameters();

        //    parameters.AddTable("@PROJECT_NOs", "VARCHAR_TABLE", ProjectNoList.Select(x=>new VARCHAR_TABLE { ITEM = x }));

        //    parameters.AddTable("@DOCUMENT_IDs", "VARCHAR_TABLE", DocumentNoList.Select(x => new VARCHAR_TABLE { ITEM = x }));

        //    var Items = base.Query<MarkupText>(SQL, parameters,commandType:CommandType.StoredProcedure);

        //    return Items;

        //}

        /// <summary>

        /// 완료

        /// </summary>

        /// <param name="PROJECT_NO"></param>

        /// <param name="DOCUMENT_URL"></param>

        /// <param name="DOCUMENT_ID"></param>

        /// <returns></returns>

        public string CreateConvertDoc(string PROJECT_NO, string DOCUMENT_URL, string DOCUMENT_ID)

        {

            string result = null;

            try

            {

                var tran = BeginTransaction();

                var parameters = new DynamicParameters();

                parameters.Add("@project_no", value: PROJECT_NO);

                parameters.Add("@document_url", value: DOCUMENT_URL);

                parameters.Add("@document_id", value: DOCUMENT_ID);

                parameters.Add("@newid", direction: ParameterDirection.Output,size:int.MaxValue);

                base.Execute("convert_insert_convertdoc", parameters, transaction: tran, commandType: CommandType.StoredProcedure);

                tran.Commit();

                result = parameters.Get<string>("newid");

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return result;

        }

        public int UpdateStatusAsync(string ServiceID, string ConvertDocID, int status, int totalPage, int currentPage, string exception)

        {

            int result = 0;

            var tran = BeginTransaction();

            var parameters = new DynamicParameters();

            parameters.Add("@service_id", value: ServiceID);

            parameters.Add("@id", value: ConvertDocID);

            parameters.Add("@status", value: status);

            parameters.Add("@total_page", value: totalPage);

            parameters.Add("@current_page", value: currentPage);

            parameters.Add("@exception", value: exception);

            result = base.Execute("convert_update_status", parameters, transaction: tran, commandType: CommandType.StoredProcedure);

            tran.Commit();

            return result;

        }

        /// <summary>

        /// 

        /// </summary>

        /// <param name="documentItem"></param>

        /// <returns>Create or Update id</returns>

        public string CreateOrUPdateDocItem(DOCUMENTITEM documentItem)

        {

            string result = null;

            var tran = BeginTransaction();

            var parameter = new DynamicParameters();

            parameter.Add("@revision", documentItem.REVISION);

            parameter.Add("@document_no", documentItem.DOCUMENT_NO);

            parameter.Add("@document_name", documentItem.DOCUMENT_NAME);

            parameter.Add("@original_file", documentItem.ORIGINAL_FILE);

            parameter.Add("@document_id", documentItem.DOCUMENT_ID);

            parameter.Add("@project_no", documentItem.PROJECT_NO);

            parameter.Add("@link", documentItem.Link);

            parameter.Add("@result_file", documentItem.RESULT_FILE);

            parameter.Add("@result", documentItem.RESULT);

            parameter.Add("@group_no", documentItem.GROUP_NO);

            parameter.Add("@newid", direction: ParameterDirection.Output, size: int.MaxValue);

            base.Execute("convert_insert_documentitem", parameter, tran, commandType: CommandType.StoredProcedure);

            tran.Commit();

            result = parameter.Get<string>("newid");

            return result;

        }

        /// <summary>

        /// test 필요 transaction Commit 이후 parameter.get을 해야 commit이 된다.

        /// </summary>

        /// <param name="convertDocID"></param>

        /// <param name="PageCount"></param>

        /// <returns></returns>

        public string CreateDocInfo(string convertDocID, int PageCount)

        {

            string result = "";

            var tran = BeginTransaction();

            var parameters = new DynamicParameters();

            parameters.Add("@convert_id", convertDocID, dbType: DbType.String);

            parameters.Add("@page_count", PageCount, dbType: DbType.Int32);

            parameters.Add("@newid", dbType: DbType.String, direction: ParameterDirection.Output, size: 50);

            parameters.Add("@errorcode", dbType: DbType.Int32, direction: ParameterDirection.Output);

            parameters.Add("@error", dbType: DbType.String, direction: ParameterDirection.Output, size: 500);

            base.Execute("convert_insert_docinfo", parameters, tran, commandType: CommandType.StoredProcedure);

            var errorCode = parameters.Get<int>("errorcode");

            var error = parameters.Get<string>("error");

            if (errorCode > 0)

            {

                tran.Rollback();

                throw new Exception(error);

            }

            else

            {

                tran.Commit();

                result = parameters.Get<string>("newid");

            }

            return result;

        }

        public bool CreateDocPage(IEnumerable<DocPage> docPages)

        {

            bool result = false;

            try

            {

                var tran = BeginTransaction();

                var parameter = new DynamicParameters();

                parameter.AddTable< DocPage>("DOCPAGES", "TYPE_INSERT_DOCPAGE", docPages.ToList());

                var id = base.Execute("CONVERT_INSERT_DOCPAGE", parameter, tran, commandType: CommandType.StoredProcedure);

                tran.Commit();

                result = true;

            }

            catch (Exception ex)

            {

                throw new Exception("DOCPAGERepository CreateAsync error.", ex);

            }

            return result;

        }

    }

    internal class VARCHAR_TABLE

    {

        public string ITEM { get; set; }

    }

}