MARKUS

using Markus.EntityModel;

using Markus.Message;

using Markus.Service.Extensions;

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using static Markus.Service.DataBase.ConvertDatabase;

using System.Windows;

using System.Linq.Expressions;

using Markus.Service.Interface;

namespace Markus.Service.DataBase

{

    public class ConvertDatabase : IDisposable

    {

        Markus.EntityModel.MarkusModel entities;

        public ConvertDatabase(string ConnectionString)

        {

            try

            {

                //해당 프로젝트의 데이터베이스 연결

                entities = new Markus.EntityModel.MarkusModel(ConnectionString);

            }

            catch (Exception ex)

            {

                System.Diagnostics.Debug.WriteLine(ex.ToString());

                throw ex;

            }

        }

        public void Dispose()

        {

            entities.Dispose();

        }

        /// <summary>

        /// 컨버터서비스를 이용하는 문서 정보 저장

        /// UniqueID

        /// using Markus.Service.Extensions;  new Guid().CreateUniqueGuid().ToString();

        /// </summary>

        /// <param name="ProjectNo">프로젝트</param>

        /// <param name="UniqueID">컨버터 서비스의 아이템 ID</param>

        /// <param name="originfilePath"></param>

        /// <param name="DocumentID"></param>

        /// <returns></returns>

        public bool SetConvertDoc(string ProjectNo, string UniqueID, string originfilePath, string DocumentID)

        {

            bool result = false;

            if (ProjectNo.IsNullOrEmpty())

            {

                throw new Exception("ProjectNo is Null");

            }

            if (UniqueID.IsNullOrEmpty())

            {

                throw new Exception("UniqueID is Null");

            }

            if (originfilePath.IsNullOrEmpty())

            {

                throw new Exception("originfilePath is Null");

            }

            if (DocumentID.IsNullOrEmpty())

            {

                throw new Exception("DocumentID is Null");

            }

            if (DocumentID.IsNullOrEmpty())

            {

                long _lastDocId = 0;

                if (entities.CONVERTER_DOC.Count() > 0)

                {

                    try

                    {

                        string qry = @"SELECT * FROM CONVERTER_DOC order by CAST(DOCUMENT_ID as int) desc";

                        var _item = entities.CONVERTER_DOC.SqlQuery(qry).FirstOrDefault();

                        if (_item != null)

                        {

                            _lastDocId = Convert.ToInt64(_item.DOCUMENT_ID) + 1;

                        }

                    }

                    catch (Exception)

                    {

                        throw;

                    }

                }

                DocumentID = _lastDocId.ToString();

            }

            CONVERTER_DOC convert_doc = new CONVERTER_DOC

            {

                ID = UniqueID,

                PROJECT_NO = ProjectNo,

                DOCUMENT_URL = originfilePath,

                DOCUMENT_ID = DocumentID,

                CREATE_DATETIME = DateTime.Now,

                STATUS = 0,

                TOTAL_PAGE = 0,

                CURRENT_PAGE = 0,

                RECONVERTER = 0

            };

            entities.CONVERTER_DOC.Add(convert_doc);

            if (entities.SaveChanges() == 1)

            {

                result = true;

            }

            return result;

        }

        //public bool SetDocument_Item(string Revision, string Document_No, string Document_Name, string Group_No)//세미

        //{

        //    bool result = false;

        //    if (Revision.IsNullOrEmpty())

        //    {

        //        throw new Exception("Revision is Null");

        //    }

        //    if (Document_No.IsNullOrEmpty())

        //    {

        //        throw new Exception("Document_No is Null");

        //    }

        //    if (Document_Name.IsNullOrEmpty())

        //    {

        //        throw new Exception("Document_Name is Null");

        //    }

        //    DOCUMENT_ITEM document_item = new DOCUMENT_ITEM

        //    {

        //        REVISION = Revision,

        //        DOCUMENT_NO = Document_No,

        //        DOCUMENT_NAME = Document_Name,

        //        GROUP_NO = Group_No

        //    };

        //    entities.DOCUMENT_ITEM.Add(document_item);

        //    if (entities.SaveChanges() == 1)

        //    {

        //        result = true;

        //    }

        //    return result;

        //}

        /// <summary>

        /// 컨버터서비스를 이용하는 문서 정보 저장

        /// UniqueID

        /// using Markus.Service.Extensions;  new Guid().CreateUniqueGuid().ToString();

        /// </summary>

        /// <param name="ProjectNo">프로젝트</param>

        /// <param name="UniqueID">컨버터 서비스의 아이템 ID</param>

        /// <param name="originfilePath"></param>

        /// <param name="DocumentID"></param>

        /// <returns></returns>

        public bool SetDocumentInfo(string ConvertID, int totalPage, List<DOCPAGE> pageinfo)

        {

            bool result = false;

            var convertDocItems = entities.CONVERTER_DOC.Where(f => f.ID == ConvertID);

            if (convertDocItems.Count() > 0)

            {

                var convertDoc = convertDocItems.First();

                string docId = new Guid().CreateUniqueGuid().ToString();

                var docinfo = entities.DOCINFO.FirstOrDefault(x => x.PROJECT_NO == convertDoc.PROJECT_NO && x.DOCUMENT_ID == convertDoc.DOCUMENT_ID);

                if (docinfo != null)

                {

                    // 기존 Docpage 삭제.

                    entities.DOCPAGE.RemoveRange(docinfo.DOCPAGE);

                    // 기존 코멘트 유지를 위한 Docinfo update                    

                    docinfo.PROJECT_NO = convertDoc.PROJECT_NO;

                    docinfo.ORIGINAL_FILE = convertDoc.DOCUMENT_URL;

                    docinfo.PAGE_COUNT = totalPage;                    

                    docinfo.DOCPAGE = pageinfo;                    

                    entities.SaveChanges();

                    result = true;

                }

                else

                {

                    DOCINFO instace = new DOCINFO

                    {

                        ID = docId,

                        PROJECT_NO = convertDoc.PROJECT_NO,

                        ORIGINAL_FILE = convertDoc.DOCUMENT_URL,

                        DOCUMENT_ID = convertDoc.DOCUMENT_ID,

                        PAGE_COUNT = totalPage,

                        DOCPAGE = pageinfo

                    };

                    entities.DOCINFO.Add(instace);

                    var save = entities.SaveChanges();

                    if (save == pageinfo.Count() + 1)

                    {

                        result = true;

                    }

                }

            }

            return result;

        }

        public Interface.ConvertItem GetConvertItem(string ProjectNo, string UniqueID)

        {

            Interface.ConvertItem result = null;

            //var items = entities.CONVERTER_DOC.Where(f => f.PROJECT_NO == gProjectNo && f.ID == UniqueID);

            var items = entities.CONVERTER_DOC.Where(f => f.PROJECT_NO == ProjectNo && f.ID == UniqueID);

            if (items.Count() > 0)

            {

                var convertItem = items.First();

                string convertPath = Path.Combine(BaseStorage(convertItem.PROJECT_NO), convertItem.PROJECT_NO + "_Tile", (System.Convert.ToInt64(convertItem.DOCUMENT_ID) / 100).ToString(), convertItem.DOCUMENT_ID);

                result = new Interface.ConvertItem(convertItem.DOCUMENT_ID, convertItem.PROJECT_NO, convertItem.ID, convertItem.DOCUMENT_URL, convertPath);

            }

            return result;

        }

        public Interface.ConvertItem GetConvertItembyDocumentID(string ProjectNo, string DocumentID)

        {

            Interface.ConvertItem result = null;

            //var items = entities.CONVERTER_DOC.Where(f => f.PROJECT_NO == gProjectNo && f.ID == UniqueID);

            var items = entities.CONVERTER_DOC.Where(f => f.PROJECT_NO == ProjectNo && f.DOCUMENT_ID == DocumentID);

            if (items.Count() > 0)

            {

                var convertItem = items.First();

                string convertPath = Path.Combine(BaseStorage(convertItem.PROJECT_NO), convertItem.PROJECT_NO + "_Tile", (System.Convert.ToInt64(convertItem.DOCUMENT_ID) / 100).ToString(), convertItem.DOCUMENT_ID);

                if (convertItem.STATUS == (int)StatusCodeType.Completed)

                {

                    result = new Interface.ConvertItem(convertItem.DOCUMENT_ID, convertItem.PROJECT_NO, convertItem.ID, convertItem.DOCUMENT_URL, convertPath,

                                                        (StatusCodeType)convertItem.STATUS, convertItem.CURRENT_PAGE, convertItem.TOTAL_PAGE, convertItem.EXCEPTION);

                }

                else

                {

                    result = new Interface.ConvertItem(convertItem.DOCUMENT_ID, convertItem.PROJECT_NO, convertItem.ID, convertItem.DOCUMENT_URL, convertPath);

                }

            }

            return result;

        }

        /// <summary>

        /// service에서 대기중이거나 오류인 아이템을 가져가서 컨버터 프로세스를 호출한다.

        /// 컨버터 상태가 에러이거나 2보다 작은 경우 

        /// UniqueKey는 각 데이터베이스의 문서 정보에서 유일한 키이며 컨버터저장에서 같은 폴더로의 저장을 방지 한다.

        /// 

        /// </summary>

        /// <param name="aliveItems"></param>

        /// <param name="TakeCount"></param>

        /// <returns></returns>

        public IEnumerable<CONVERTER_DOC> GetWaitConvertItems(List<string> projectList, int TakeCount)

        {

            var convertWaitingItems = entities.CONVERTER_DOC.OrderBy(x => x.CREATE_DATETIME).Where(

                                        x =>

                                        //projectList.Contains(x.PROJECT_NO) && x.RECONVERTER < 2 &&

                                         x.RECONVERTER < 2 &&

                                        (x.STATUS == (int)StatusCodeType.None || x.STATUS == (int)StatusCodeType.FileError || x.STATUS == (int)StatusCodeType.Error))

                                    .GroupBy(x=>x.DOCUMENT_ID).Select(x => x.FirstOrDefault())

                                    .Take(TakeCount);

            return convertWaitingItems;

        }

        public List<CONVERTER_DOC> GetConvertingItems(List<string> projectList)

        {

            var convertWaitingItems = entities.CONVERTER_DOC.OrderBy(x => x.CREATE_DATETIME).Where(

                                        x =>

                                        projectList.Contains(x.PROJECT_NO) &&

                                         (x.STATUS > (int)StatusCodeType.None && x.STATUS < (int)StatusCodeType.Completed) && x.RECONVERTER < 2);

            return convertWaitingItems.ToList();

        }

        private string GetConvertPath(string ProjectNo,string DocumentID)

        {

            try

            {

                return Path.Combine(BaseStorage(ProjectNo), ProjectNo + "_Tile", (System.Convert.ToInt64(DocumentID) / 100).ToString(), DocumentID);

            }

            catch (Exception)

            {

                throw new Exception("GetConvertPath Error");

            }

        }

        /// <summary>

        /// 오류나 wait으로 남아 있는 데이터베이스의 Item을 StatusCodeType.None으로 복구

        /// </summary>

        /// <param name="convertID">StatusCodeType을 변경하고자 하는 ConvertItem의 ID</param>

        /// <param name="ReConvert">ReConvert가 2이면 다시 Convert를 시도 안하기 때문에 기존 값으로 유지 하고 계속 시도 할수 있도록 0을 주는 경우가 있다.</param>

        /// <returns></returns>

        /// 

        public int SetCleanUpItem(ConvertItem _ConvertItem)

        {

            int result = 0;

            //var items = entities.CONVERTER_DOC.Where(x => x.ID == _ConvertItem.ConvertID && x.DOCUMENT_ID == _ConvertItem.DocumentID);

            var items = entities.CONVERTER_DOC.Where(x => x.ID == _ConvertItem.ConvertID);

            result = items.Count();

            if (items.Count() > 0)

            {

                var item = items.First();

                item.RECONVERTER = item.RECONVERTER - _ConvertItem.ReConverter;

                item.CREATE_DATETIME = DateTime.Now;//여기서도 변경 되고 디비에서도 변경됨

                //item.START_DATETIME = null;

                //item.END_DATETIME = null;

                item.STATUS = (int)StatusCodeType.None; //여기서는 값 변경 되는데 디비에서 변경 안됨 => 0 되었다가 파일수에 따라 점차 늘어나는것 

                result = entities.SaveChanges();

            }

            return result;

        }

        //public int SetCleanUpItem(string convertID,int ReConvert)

        //{

        //    int result = 0;

        //    var items = entities.CONVERTER_DOC.Where(x=> x.ID == convertID);

        //    result = items.Count();

        //    if (items.Count() > 0)

        //    {

        //        var item = items.First();

        //        item.RECONVERTER = item.RECONVERTER - ReConvert;

        //        item.CREATE_DATETIME = DateTime.Now;//여기서도 변경 되고 디비에서도 변경됨

        //        //item.START_DATETIME = null;

        //        //item.END_DATETIME = null;

        //        item.STATUS = (int)StatusCodeType.None; //여기서는 값 변경 되는데 디비에서 변경 안됨 => 0 되었다가 파일수에 따라 점차 늘어나는것 

        //        result = entities.SaveChanges();

        //    }

        //    return result;

        //}

        /// <summary>

        /// Item 삭제

        /// </summary>

        /// <returns></returns>

        public int RemoveItem(string convertID)

        {

            int result = 0;

            var items = entities.CONVERTER_DOC.Where(x => x.ID == convertID);

            if (items.Count() > 0)

            {

                CONVERTER_DOC item = items.First();

                entities.CONVERTER_DOC.Remove(item);

                result = entities.SaveChanges();

            }

            return result;

        }

        /// <summary>

        /// 전체 컨버터를 가져온다. 비동기로 변경하여야 한다.

        /// </summary>

        /// <returns></returns>

        public List<Interface.ConvertItem> GetConvertItems( StatusCodeType statusCodeType)

        {

            List<Interface.ConvertItem> convertItems = new List<Interface.ConvertItem>();

            var items = entities.CONVERTER_DOC.Where(x=>x.STATUS == (int)statusCodeType).ToList(); //.Where(x => x.PROJECT_NO == gProjectNo);

            if (items.Count() > 0)

            {

                foreach (var item in items)

                {

                    var convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

                    {

                        ReConverter = item.RECONVERTER,

                        ServiceID = item.SERVICE_ID,

                        ConvertState = ((StatusCodeType)item.STATUS),

                        CreateTime = item.CREATE_DATETIME,

                        TotalPage = item.TOTAL_PAGE,

                        CurrentPageNo = item.CURRENT_PAGE,

                        ConvertPath = GetConvertPath(item.PROJECT_NO, item.DOCUMENT_ID)

                    };

                    convertItems.Add(convertitem);

                }

            }

            return convertItems;

        }

        /// <summary>

        /// 전체 컨버터를 가져온다. 비동기로 변경하여야 한다.

        /// </summary>

        /// <returns></returns>

        public List<Interface.ConvertItem> GetConvertItems(StatusCodeType statusCodeType,Func<CONVERTER_DOC, bool> @where,int takeCount = 100)

        {

            List<Interface.ConvertItem> convertItems = new List<Interface.ConvertItem>();

            var items = entities.CONVERTER_DOC.Where(x => x.STATUS == (int)statusCodeType).Take(takeCount).ToList(); //.Where(x => x.PROJECT_NO == gProjectNo);

            if (items.Count() > 0)

            {

                foreach (var item in items)

                {

                    var convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

                    {

                        ReConverter = item.RECONVERTER,

                        ServiceID = item.SERVICE_ID,

                        //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

                        ConvertState = ((StatusCodeType)item.STATUS),

                        CreateTime = item.CREATE_DATETIME,

                        TotalPage = item.TOTAL_PAGE,

                        CurrentPageNo = item.CURRENT_PAGE,

                        ConvertPath = GetConvertPath(item.PROJECT_NO, item.DOCUMENT_ID)

                    };

                    convertItems.Add(convertitem);

                }

            }

            return convertItems;

        }

        //List<Interface.ConvertItem> StatusKeep = new List<Interface.ConvertItem>();

        public IEnumerable<Interface.ConvertItem> GetConvertProjects(Markus.Service.Interface.ConvertItem selectedConvert)

        {

            List<Interface.ConvertItem> StatusKeep = new List<Interface.ConvertItem>();

            System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();///////////////디비 테스트 후 해보기 

            stopwatch.Start();

            List<Interface.ConvertItem> convertItems = new List<Interface.ConvertItem>();//디비 다 조인 후 조건 비교 후 그리드 출력

            var query = from items in entities.CONVERTER_DOC //세미

                        join documents in entities.DOCUMENT_ITEM on items.DOCUMENT_ID equals documents.DOCUMENT_ID into GroupItems

                        from subpet in GroupItems.DefaultIfEmpty()

                        where items.PROJECT_NO == selectedConvert.ProjectNumber && items.DOCUMENT_ID == selectedConvert.DocumentID

                        orderby items.CREATE_DATETIME descending 

                        select new

                        {

                            ID = items.ID,

                            DOCUMENT_URL = items.DOCUMENT_URL,

                            RECONVERTER = items.RECONVERTER,

                            SERVICE_ID = items.SERVICE_ID,

                            STATUS = items.STATUS,

                            CREATE_DATETIME = items.CREATE_DATETIME,

                            TOTAL_PAGE = items.TOTAL_PAGE,

                            CURRENT_PAGE = items.CURRENT_PAGE,

                            EXCEPTION = items.EXCEPTION,

                            PROJECT_NO = items.PROJECT_NO,

                            DOCUMENT_ID = items.DOCUMENT_ID,

                            REVISION = subpet.REVISION,

                            DOCUMENT_NO = subpet.DOCUMENT_NO,

                            DOCUMENT_NAME = subpet.DOCUMENT_NAME,

                            GROUP_NO = subpet.GROUP_NO

                        };

            if (query.Count() > 0)//세미

            {

                foreach (var item in query)

                {

                    //var convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

                    Interface.ConvertItem convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

                    {

                        ReConverter = item.RECONVERTER,

                        ServiceID = item.SERVICE_ID,

                        //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

                        //ConvertState = (item.STATUS).ToString(),

                        ConvertState = (StatusCodeType)item.STATUS,

                        CreateTime = item.CREATE_DATETIME,

                        TotalPage = item.TOTAL_PAGE,

                        CurrentPageNo = item.CURRENT_PAGE,

                        Exception = item.EXCEPTION,

                        ConvertPath = GetConvertPath(item.PROJECT_NO, item.DOCUMENT_ID),

                        Revision = item.REVISION,

                        DocumnetNo = item.DOCUMENT_NO,

                        DocumnetName = item.DOCUMENT_NAME,

                        GroupNo = item.GROUP_NO

                    };

                    //if (@whereq(convertitem))

                    //{

                        ////convertItems.Add(convertitem);

                        StatusKeep.Add(convertitem);

                   // }

                }

            }

            ///System.Diagnostics.Debug.WriteLine(new TimeSpan(stopwatch.ElapsedTicks).ToString());

            //return convertItems;//디비 다 조인 후 조건 비교 후 그리드 출력

            return StatusKeep;

        }

        public IEnumerable<Interface.ConvertItem> GetConvertProjects(System.Collections.ObjectModel.ObservableCollection<Interface.ConvertItem> selectedConvert)

        {

            List<Interface.ConvertItem> StatusKeep = new List<Interface.ConvertItem>();

            //System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();///////////////디비 테스트 후 해보기 

            //stopwatch.Start();

            List<Interface.ConvertItem> convertItems = new List<Interface.ConvertItem>();//디비 다 조인 후 조건 비교 후 그리드 출력

            foreach (var selectedConvertItem in selectedConvert)

            {

                var query = from items in entities.CONVERTER_DOC //세미

                            join documents in entities.DOCUMENT_ITEM on items.DOCUMENT_ID equals documents.DOCUMENT_ID into GroupItems

                            from subpet in GroupItems.DefaultIfEmpty()

                            where items.PROJECT_NO == selectedConvertItem.ProjectNumber && items.ID == selectedConvertItem.ConvertID 

                            select new

                            {

                                ID = items.ID,

                                DOCUMENT_URL = items.DOCUMENT_URL,

                                RECONVERTER = items.RECONVERTER,

                                SERVICE_ID = items.SERVICE_ID,

                                STATUS = items.STATUS,

                                CREATE_DATETIME = items.CREATE_DATETIME,

                                TOTAL_PAGE = items.TOTAL_PAGE,

                                CURRENT_PAGE = items.CURRENT_PAGE,

                                EXCEPTION = items.EXCEPTION,

                                PROJECT_NO = items.PROJECT_NO,

                                DOCUMENT_ID = items.DOCUMENT_ID,

                                REVISION = subpet.REVISION,

                                DOCUMENT_NO = subpet.DOCUMENT_NO,

                                DOCUMENT_NAME = subpet.DOCUMENT_NAME,

                                GROUP_NO = subpet.GROUP_NO

                            };

                if (query.Count() > 0)//세미

                {

                    foreach (var item in query)

                    {

                        //var convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

                        Interface.ConvertItem convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

                        {

                            ReConverter = item.RECONVERTER,

                            ServiceID = item.SERVICE_ID,

                            //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

                            //ConvertState = (item.STATUS).ToString(),

                            ConvertState = (StatusCodeType)item.STATUS,

                            CreateTime = item.CREATE_DATETIME,

                            TotalPage = item.TOTAL_PAGE,

                            CurrentPageNo = item.CURRENT_PAGE,

                            Exception = item.EXCEPTION,

                            ConvertPath = GetConvertPath(item.PROJECT_NO, item.DOCUMENT_ID),

                            Revision = item.REVISION,

                            DocumnetNo = item.DOCUMENT_NO,

                            DocumnetName = item.DOCUMENT_NAME,

                            GroupNo = item.GROUP_NO

                        };

                        //if (@whereq(convertitem))

                        //{

                        ////convertItems.Add(convertitem);

                        StatusKeep.Add(convertitem);

                        // }

                    }

                }

            }

                //System.Diagnostics.Debug.WriteLine(new TimeSpan(stopwatch.ElapsedTicks).ToString());

                //return convertItems;//디비 다 조인 후 조건 비교 후 그리드 출력

                return StatusKeep;

        }

        //public IEnumerable<Interface.ConvertItem> GetConvertProjects(Func<Interface.ConvertItem, bool> @whereq, int status)//조인 후 조건 & 조건and조인

        ////public List<Interface.ConvertItem> GetConvertProjects(Expression<Func<CONVERTER_DOC, bool>> @where) //linq and entity and sql

        ////public IEnumerable<Interface.ConvertItem> GetConvertProjects(Func<CONVERTER_DOC, bool> @where) //원본

        //{

        //    System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();///////////////디비 테스트 후 해보기 

        //    stopwatch.Start();

        //    List<Interface.ConvertItem> convertItems = new List<Interface.ConvertItem>();//디비 다 조인 후 조건 비교 후 그리드 출력

        //    var query = from items in entities.CONVERTER_DOC //세미

        //                join documents in entities.DOCUMENT_ITEM on items.DOCUMENT_ID equals documents.DOCUMENT_ID

        //                where items.PROJECT_NO == documents.PROJECT_NO && items.STATUS == status

        //                orderby items.CREATE_DATETIME descending

        //                select new

        //                {

        //                    ID = items.ID,

        //                    DOCUMENT_URL = items.DOCUMENT_URL,

        //                    RECONVERTER = items.RECONVERTER,

        //                    SERVICE_ID = items.SERVICE_ID,

        //                    STATUS = items.STATUS,

        //                    CREATE_DATETIME = items.CREATE_DATETIME,

        //                    TOTAL_PAGE = items.TOTAL_PAGE,

        //                    CURRENT_PAGE = items.CURRENT_PAGE,

        //                    EXCEPTION = items.EXCEPTION,

        //                    PROJECT_NO = items.PROJECT_NO,

        //                    DOCUMENT_ID = items.DOCUMENT_ID,

        //                    REVISION = documents.REVISION,

        //                    DOCUMENT_NO = documents.DOCUMENT_NO,

        //                    DOCUMENT_NAME = documents.DOCUMENT_NAME,

        //                    GROUP_NO = documents.GROUP_NO

        //                };

        //    if (query.Count() > 0)//세미

        //    {

        //        foreach (var item in query)

        //        {

        //            //var convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

        //            Interface.ConvertItem convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

        //            {

        //                ReConverter = item.RECONVERTER,

        //                ServiceID = item.SERVICE_ID,

        //                //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

        //                ConvertState = (item.STATUS).ToString(),

        //                CreateTime = item.CREATE_DATETIME,

        //                TotalPage = item.TOTAL_PAGE,

        //                CurrentPageNo = item.CURRENT_PAGE,

        //                Exception = item.EXCEPTION,

        //                //ConvertPath = GetConvertPath(item.PROJECT_NO, item.DOCUMENT_ID),

        //                Revision = item.REVISION,

        //                DocumnetNo = item.DOCUMENT_NO,

        //                DocumnetName = item.DOCUMENT_NAME,

        //                GroupNo = item.GROUP_NO

        //            };

        //            if (@whereq(convertitem))

        //            {

        //                //convertItems.Add(convertitem);

        //                StatusKeep.Add(convertitem);

        //            }

        //        }

        //    }

        //    System.Diagnostics.Debug.WriteLine(new TimeSpan(stopwatch.ElapsedTicks).ToString());

        //    //return convertItems;//디비 다 조인 후 조건 비교 후 그리드 출력

        //    return StatusKeep;

        //}

        //public IEnumerable<Interface.ConvertItem> GetConvertProjects(Func<CONVERTER_DOC, bool> @where) //@where는 입력값이 CONVERTER_DOC이고 출력값이 bool //부장님

        //public List<Interface.ConvertItem> GetConvertProjects(Func<CONVERTER_DOC, bool> @where) //@where는 입력값이 CONVERTER_DOC이고 출력값이 bool

        //System.Linq.IQueryable

        public IEnumerable<Interface.ConvertItem> GetConvertProjects(int status)//조인 후 조건 & 조건and조인

        //public List<Interface.ConvertItem> GetConvertProjects(Expression<Func<CONVERTER_DOC, bool>> @where) //linq and entity and sql

        //public IEnumerable<Interface.ConvertItem> GetConvertProjects(Func<CONVERTER_DOC, bool> @where) //원본

        {

            // System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();///////////////디비 테스트 후 해보기 

            // stopwatch.Start();

            // //@ 심벌 ex) string filename = "C:\\Temp\\1.txt"; -> filename = @"C:\Temp\1.txt"; and 여러 문자열 사용 가능

            ////var items = entities.CONVERTER_DOC.Where(@where);//부장님

            // List<Interface.ConvertItem> convertItems = new List<Interface.ConvertItem>();

            // var query = from items in entities.CONVERTER_DOC //세미

            //              join documents in entities.DOCUMENT_ITEM on items.DOCUMENT_ID equals documents.DOCUMENT_ID

            //              //where @where(items)

            //             select new//new 다음에 클래스 이름 넣으면 안됨 

            //             {

            //                 ID = items.ID,

            //                 DOCUMENT_URL = items.DOCUMENT_URL,

            //                 RECONVERTER = items.RECONVERTER,

            //                 SERVICE_ID = items.SERVICE_ID,

            //                 STATUS = items.STATUS,

            //                 CREATE_DATETIME = items.CREATE_DATETIME,

            //                 TOTAL_PAGE = items.TOTAL_PAGE,

            //                 CURRENT_PAGE = items.CURRENT_PAGE,

            //                 EXCEPTION = items.EXCEPTION,

            //                 PROJECT_NO = items.PROJECT_NO,

            //                 DOCUMENT_ID = items.DOCUMENT_ID,

            //                 REVISION = documents.REVISION,

            //                 DOCUMENT_NO = documents.DOCUMENT_NO,

            //                 DOCUMENT_NAME = documents.DOCUMENT_NAME,

            //                 GROUP_NO = documents.GROUP_NO

            //             };

            // if (query.Count() > 0)//ConvertItem에 값들 넣어야 함 그래야 값이 바뀌면 propertychanged로 그리드 실시간 없데이트 가능

            // {

            //     foreach (var item in query)

            //     {

            //         var convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

            //         {

            //             ReConverter = item.RECONVERTER,

            //             ServiceID = item.SERVICE_ID,

            //             //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

            //             ConvertState = (item.STATUS).ToString(),

            //             CreateTime = item.CREATE_DATETIME,

            //             TotalPage = item.TOTAL_PAGE,

            //             CurrentPageNo = item.CURRENT_PAGE,

            //             Exception = item.EXCEPTION,

            //             ConvertPath = GetConvertPath(item.PROJECT_NO, item.DOCUMENT_ID),

            //             Revision = item.REVISION,

            //             DocumnetNo = item.DOCUMENT_NO,

            //             DocumnetName = item.DOCUMENT_NAME,

            //             GroupNo = item.GROUP_NO

            //         };

            //         convertItems.Add(convertitem);

            //     }

            // }

            //System.Diagnostics.Debug.WriteLine(new TimeSpan(stopwatch.ElapsedTicks).ToString());

            //return convertItems;///////////////디비 테스트 후 해보기 

            //select new Interface.ConvertItem(ConvertDoc.DOCUMENT_ID, ConvertDoc.PROJECT_NO, ConvertDoc.ID, ConvertDoc.DOCUMENT_URL)

            //{

            //    ReConverter = ConvertDoc.RECONVERTER,

            //    ServiceID = ConvertDoc.SERVICE_ID,

            //    //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

            //    ConvertState = (ConvertDoc.STATUS).ToString(),

            //    CreateTime = ConvertDoc.CREATE_DATETIME,

            //    TotalPage = ConvertDoc.TOTAL_PAGE,

            //    CurrentPageNo = ConvertDoc.CURRENT_PAGE,

            //    Exception = ConvertDoc.EXCEPTION,

            //    ConvertPath = GetConvertPath(ConvertDoc.PROJECT_NO, ConvertDoc.DOCUMENT_ID),

            //    Revision = documents.REVISION,

            //    DocumnetNo = documents.DOCUMENT_NO,

            //    DocumnetName = documents.DOCUMENT_NAME,

            //    GroupNo = documents.GROUP_NO

            //};

            //System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();///////////////디비 테스트 후 해보기 

            //stopwatch.Start();

            List<Interface.ConvertItem> convertItems = new List<Interface.ConvertItem>();//디비 다 조인 후 조건 비교 후 그리드 출력

            var query = from items in entities.CONVERTER_DOC //세미

                        join documents in entities.DOCUMENT_ITEM on items.DOCUMENT_ID equals documents.DOCUMENT_ID into GroupItems

                        from subpet in GroupItems.DefaultIfEmpty()

                        where items.STATUS == status

                        orderby items.CREATE_DATETIME descending

                        select new

                        {

                            ID = items.ID,

                            DOCUMENT_URL = items.DOCUMENT_URL,

                            RECONVERTER = items.RECONVERTER,

                            SERVICE_ID = items.SERVICE_ID,

                            STATUS = items.STATUS,

                            CREATE_DATETIME = items.CREATE_DATETIME,

                            TOTAL_PAGE = items.TOTAL_PAGE,

                            CURRENT_PAGE = items.CURRENT_PAGE,

                            EXCEPTION = items.EXCEPTION,

                            PROJECT_NO = items.PROJECT_NO,

                            DOCUMENT_ID = items.DOCUMENT_ID,

                            REVISION = subpet.REVISION,

                            DOCUMENT_NO = subpet.DOCUMENT_NO,

                            DOCUMENT_NAME = subpet.DOCUMENT_NAME,

                            GROUP_NO = subpet.GROUP_NO

                            //REVISION = documents.REVISION,

                            //DOCUMENT_NO = documents.DOCUMENT_NO,

                            //DOCUMENT_NAME = documents.DOCUMENT_NAME,

                            //GROUP_NO = documents.GROUP_NO

                        };

            if (query.Count() > 0)//세미

            {

                foreach (var item in query)

                {

                    //var convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

                    Interface.ConvertItem convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

                    {

                        ReConverter = item.RECONVERTER,

                        ServiceID = item.SERVICE_ID,

                        //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

                        //ConvertState = (item.STATUS).ToString(),

                        ConvertState = (StatusCodeType)item.STATUS,

                        CreateTime = item.CREATE_DATETIME,

                        TotalPage = item.TOTAL_PAGE,

                        CurrentPageNo = item.CURRENT_PAGE,

                        Exception = item.EXCEPTION,

                        //ConvertPath = GetConvertPath(item.PROJECT_NO, item.DOCUMENT_ID),

                        Revision = item.REVISION,

                        DocumnetNo = item.DOCUMENT_NO,

                        DocumnetName = item.DOCUMENT_NAME,

                        GroupNo = item.GROUP_NO,

                        DocumentID = item.DOCUMENT_ID

                    };

                        convertItems.Add(convertitem);

                }

            }

            //System.Diagnostics.Debug.WriteLine(new TimeSpan(stopwatch.ElapsedTicks).ToString());

            return convertItems;//디비 다 조인 후 조건 비교 후 그리드 출력

                                //if (Wheredel(convertitem) == false)

                    //if (@where(convertitem) == false)

                    //{

                    //    break;

                    //}

                    //if (@where(convertitem))

                    //{

                    //    convertItems.Add(convertitem);

                    //}

                    //yield return new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

                    //{

                    //    ReConverter = item.RECONVERTER,

                    //    ServiceID = item.SERVICE_ID,

                    //    //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

                    //    ConvertState = (item.STATUS).ToString(),

                    //    CreateTime = item.CREATE_DATETIME,

                    //    TotalPage = item.TOTAL_PAGE,

                    //    CurrentPageNo = item.CURRENT_PAGE,

                    //    Exception = item.EXCEPTION,

                    //    ConvertPath = GetConvertPath(item.PROJECT_NO, item.DOCUMENT_ID),

                    //    Revision = item.REVISION,

                    //    DocumnetNo = item.DOCUMENT_NO,

                    //    DocumnetName = item.DOCUMENT_NAME,

                    //    GroupNo = item.GROUP_NO

                    //};

            //var query = entities.CONVERTER_DOC.Where(@where);//이렇게 해서 두 테이블 조인하는  프로시저 만들어라

            //if (query.Count() > 0)

            //{

            //    foreach (var item in query)

            //    {

            //        //var docitem = entities.DOCUMENT_ITEM.Where(x => x.DOCUMENT_ID == item.DOCUMENT_ID);

            //        if (@where(item))

            //        {

            //            //string rev = "";

            //            //string docNo = "";

            //            //string docName = "";

            //            //string grpNo = "";

            //            //if (item.Count() > 0)

            //            //{

            //            //    rev = docitem.First().REVISION;

            //            //    docNo = docitem.First().DOCUMENT_NO;

            //            //    docName = docitem.First().DOCUMENT_NAME;

            //            //    grpNo = docitem.First().GROUP_NO;

            //            //}

            //            yield return new Interface.ConvertItem(item.DOCUMENT_ID, item.projectNo, item.ID, item.DOCUMENT_URL)

            //            {

            //                ReConverter = item.RECONVERTER,

            //                ServiceID = item.SERVICE_ID,

            //                //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

            //                CreateTime = item.CREATE_DATETIME,

            //                TotalPage = item.TOTAL_PAGE,

            //                CurrentPageNo = item.CURRENT_PAGE,

            //                Exception = item.EXCEPTION,

            //                ConvertPath = GetConvertPath(item.projectNo, item.DOCUMENT_ID),

            //                Revision = item.REVISION,

            //                DocumnetNo = item.DOCUMENT_NO,

            //                DocumnetName = item.DOCUMENT_NAME,

            //                GroupNo = item.GROUP_NO

            //            };

            //        }

            //    }

            //}//프로시저

            //var items = entities.CONVERTER_DOC.Where(@where);//부장님원본

            //if (items.Count() > 0)//원본

            //{

            //    foreach (var item in items)

            //    {

            //        yield return new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

            //        {

            //            ReConverter = item.RECONVERTER,

            //            ServiceID = item.SERVICE_ID,

            //            //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

            //            ConvertState = (item.STATUS).ToString(),

            //            CreateTime = item.CREATE_DATETIME,

            //            TotalPage = item.TOTAL_PAGE,

            //            CurrentPageNo = item.CURRENT_PAGE,

            //            Exception = item.EXCEPTION,

            //            ConvertPath = GetConvertPath(item.PROJECT_NO, item.DOCUMENT_ID)

            //        };

            //    }

            //}//원본

            //LINQKit 하다 말음

            //List<Interface.ConvertItem> convertItems = new List<Interface.ConvertItem>();

            //var query = 

            //    from items in entities.CONVERTER_DOC //세미

            //    join documents in entities.DOCUMENT_ITEM on items.DOCUMENT_ID equals documents.DOCUMENT_ID

            //    select new//new 다음에 클래스 이름 넣으면 안됨 

            //            {

            //                ID = items.ID,

            //                DOCUMENT_URL = items.DOCUMENT_URL,

            //                RECONVERTER = items.RECONVERTER,

            //                SERVICE_ID = items.SERVICE_ID,

            //                STATUS = items.STATUS,

            //                CREATE_DATETIME = items.CREATE_DATETIME,

            //                TOTAL_PAGE = items.TOTAL_PAGE,

            //                CURRENT_PAGE = items.CURRENT_PAGE,

            //                EXCEPTION = items.EXCEPTION,

            //                PROJECT_NO = items.PROJECT_NO,

            //                DOCUMENT_ID = items.DOCUMENT_ID,

            //                REVISION = documents.REVISION,

            //                DOCUMENT_NO = documents.DOCUMENT_NO,

            //                DOCUMENT_NAME = documents.DOCUMENT_NAME,

            //                GROUP_NO = documents.GROUP_NO

            //            };

            //if (query.Count() > 0)//ConvertItem에 값들 넣어야 함 그래야 값이 바뀌면 propertychanged로 그리드 실시간 없데이트 가능

            //{

            //    foreach (var item in query)

            //    {

            //        var convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

            //        {

            //            ReConverter = item.RECONVERTER,

            //            ServiceID = item.SERVICE_ID,

            //            //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

            //            ConvertState = (item.STATUS).ToString(),

            //            CreateTime = item.CREATE_DATETIME,

            //            TotalPage = item.TOTAL_PAGE,

            //            CurrentPageNo = item.CURRENT_PAGE,

            //            Exception = item.EXCEPTION,

            //            ConvertPath = GetConvertPath(item.PROJECT_NO, item.DOCUMENT_ID),

            //            Revision = item.REVISION,

            //            DocumnetNo = item.DOCUMENT_NO,

            //            DocumnetName = item.DOCUMENT_NAME,

            //            GroupNo = item.GROUP_NO

            //        };

            //        convertItems.Add(convertitem);

            //    }

            //}

            //return convertItems;

            //LINQKit

        }

        public List<Interface.ConvertItem> GetConvertProjectsStatus(Func<CONVERTER_DOC, bool> @where)

        {

            List<Interface.ConvertItem> convertItems = new List<Interface.ConvertItem>();

            //var items = entities.CONVERTER_DOC.Where(x => x.STATUS == (int)statusCodeType).Take(takeCount).ToList(); //.Where(x => x.PROJECT_NO == gProjectNo);

            var items = entities.CONVERTER_DOC.Where(x => x.STATUS<= 4);

            if (items.Count() > 0)

            {

                foreach (var item in items)

                {

                    var convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL)

                    {

                        ReConverter = item.RECONVERTER,

                        ServiceID = item.SERVICE_ID,

                        //ConvertState = ((StatusCodeType)item.STATUS).ToString(),

                        //ConvertState = (item.STATUS).ToString(),

                        ConvertState = (StatusCodeType)item.STATUS,

                        CreateTime = item.CREATE_DATETIME,

                        TotalPage = item.TOTAL_PAGE,

                        CurrentPageNo = item.CURRENT_PAGE,

                        Exception = item.EXCEPTION,

                        ConvertPath = GetConvertPath(item.PROJECT_NO, item.DOCUMENT_ID)

                    };

                    convertItems.Add(convertitem);

                }

            }

            return convertItems;

        }

        /// <summary>

        /// 현재 서비스에서 컨버터중인 아이템.

        /// </summary>

        /// <returns></returns>

        public List<Interface.ConvertItem> GetServiceAliveItems(string ServiceID)

        {

            List<Interface.ConvertItem> convertItems = new List<Interface.ConvertItem>();

            var items = entities.CONVERTER_DOC.Where(x => x.SERVICE_ID == ServiceID && x.STATUS > (int)StatusCodeType.None && x.STATUS < (int)StatusCodeType.Completed).ToList(); //.Where(x => x.PROJECT_NO == gProjectNo);

            if (items.Count() > 0)

            {

                foreach (var item in items)

                {

                    var convertitem = new Interface.ConvertItem(item.DOCUMENT_ID, item.PROJECT_NO, item.ID, item.DOCUMENT_URL);

                    convertItems.Add(convertitem);

                }

            }

            return convertItems;

        }

        public SERVICE_PROPERTIES GetServiceProperties(string ServiceID)

        {

            SERVICE_PROPERTIES result = null;

            var items =  entities.SERVICE_PROPERTIES.Where(x => x.ID == ServiceID);

            if(items.Count() > 0)

            {

                result = items.First();

            }

            return result;

        }

        public bool SetConvertState(string ServiceID, string UniqueID,int State,int CurrentPage,int TotalPage,string Error)

        {

            bool result = false;

            try

            {

                var items = entities.CONVERTER_DOC.SingleOrDefault(f => f.ID == UniqueID);

                //var items = entities.CONVERTER_DOC.SingleOrDefault(f => f.PROJECT_NO == gProjectNo && f.ID == UniqueID);

                if (items != null)

                {

                    items.SERVICE_ID = ServiceID;

                    items.STATUS = State;

                    items.CURRENT_PAGE = CurrentPage;

                    items.TOTAL_PAGE = TotalPage;

                    StatusCodeType statusCode = (StatusCodeType)State;

                    if (items.START_DATETIME == null)

                    {

                        items.START_DATETIME = DateTime.Now;

                    }

                    switch (statusCode)

                    {

                        case StatusCodeType.None:

                            break;

                        case StatusCodeType.Wait:

                            items.START_DATETIME = DateTime.Now;

                            break;

                        case StatusCodeType.PageLoading:

                            break;

                        case StatusCodeType.Saving:

                            break;

                        case StatusCodeType.Completed:

                            items.CURRENT_PAGE = items.TOTAL_PAGE;

                            items.END_DATETIME= DateTime.Now;

                            break;

                        case StatusCodeType.FileError:

                            Error = $" file Error :" + Error;

                            items.END_DATETIME = DateTime.Now;

                            break;

                        case StatusCodeType.PageError:

                            Error = $" Page No {CurrentPage} Error :" + Error;

                            break;

                        case StatusCodeType.NeedsPassword:

                            break;

                        case StatusCodeType.Error:

                            Error = $" Error :" + Error;

                            items.END_DATETIME = DateTime.Now;

                            break;

                        default:

                            break;

                    }

                    if(items.EXCEPTION == null)

                    {

                        items.EXCEPTION = "";

                    }

                    if (items.EXCEPTION.Length < 255)

                    {

                        items.EXCEPTION = items.EXCEPTION + Error;

                    }

                    entities.SaveChanges();

                    result = true;

                }

            }

            catch (Exception ex)

            {

                throw new Exception("SetConvertState",ex);

            }

            return result;

        }

        private string BaseStorage(string ProjectNo)

        {

            string result = "";

            var properties = entities.PROPERTIES.Where(f => f.PROPERTY == ProjectNo && f.TYPE == PROPERTIES_DEFINE.TILE_SOURCE_STORAGE);

            if(properties.Count() > 0)

            {

                result = properties.First().VALUE;

            }

            else

            {

                throw new Exception($"Project {ProjectNo} BaseStorage Not Setting.");

            }

            return result;

        }

    }

}