using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DBUtility;//Please add references
namespace OT.DAL
{
    /// <summary>
    /// 資料訪問類:OTB_SYS_Document
    /// </summary>
    public partial class OTB_SYS_Document
    {
        public OTB_SYS_Document()
        { }
        #region  Method

        /// <summary>
        /// 新增
        /// </summary>
        public bool Add(OT.Model.OTB_SYS_Document model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" declare @parentID char(36) ");
            strSql.Append(" select @parentID = [GUID] from OTB_SYS_Document ");
            strSql.Append(" where [FilePath] = @FileRoot and [filetype] = 'Directory' ");
            strSql.Append(" insert into OTB_SYS_Document([GUID],[PUID],[FileName], SubFileName, FilePath, FileSize, PixelW, PixelH, FileContent, IsProtected, IsPublic, Memo, FileCreateDate, FileType, CreateUser, CreateDate, ModifyUser, ModifyDate, [status], FileRoot,SourceRelationID) ");
            strSql.Append(" values(@GUID,@parentID,@FileName,@SubFileName,@FilePath,@FileSize,@PixelW,@PixelH,@FileContent,@IsProtected,@IsPublic,@Memo,@FileCreateDate,'File',@CreateUser,GETDATE(),@ModifyUser,GETDATE(),3,@FileRoot,@SourceRelationID) ");
            strSql.Append(" insert into OTB_SYS_DocumentRule(OrganizationID,RUID,FUID,[User],[Group],FileType,[Rule],CreateUser,CreateDate) ");
            strSql.Append(" values( @Organization,NEWID(),@GUID,@CreateUser,'admin','File','-v-w-x-d-a',@CreateUser,GETDATE() ) ");

            SqlParameter[] parameters = {
                    new SqlParameter("@GUID", SqlDbType.Char,36),
                    new SqlParameter("@PUID", SqlDbType.Char,36),
                    new SqlParameter("@FileName", SqlDbType.NVarChar,200),
                    new SqlParameter("@SubFileName", SqlDbType.VarChar,10),
                    new SqlParameter("@FilePath", SqlDbType.NVarChar,500),
                    new SqlParameter("@FileSize", SqlDbType.Int),
                    new SqlParameter("@PixelW", SqlDbType.NVarChar,20),
                    new SqlParameter("@PixelH", SqlDbType.NVarChar,20),
                    new SqlParameter("@FileContent", SqlDbType.VarChar,-1),
                    new SqlParameter("@IsProtected", SqlDbType.NVarChar,1),
                    new SqlParameter("@IsPublic", SqlDbType.NVarChar,1),
                    new SqlParameter("@Memo", SqlDbType.NVarChar,-1),
                    new SqlParameter("@FileCreateDate", SqlDbType.DateTime),
                    new SqlParameter("@CreateUser", SqlDbType.VarChar,50),
                    new SqlParameter("@ModifyUser", SqlDbType.VarChar,50),
                    new SqlParameter("@FileRoot", SqlDbType.NVarChar,200),
                    new SqlParameter("@Organization", SqlDbType.VarChar,50),
                    new SqlParameter("@SourceRelationID", SqlDbType.Char,36)
                                        };
            parameters[0].Value = model.GUID;
            parameters[1].Value = model.PUID;
            parameters[2].Value = model.FileName;
            parameters[3].Value = model.SubFileName;
            parameters[4].Value = model.FilePath;
            parameters[5].Value = model.FileSize;
            parameters[6].Value = model.PixelW;
            parameters[7].Value = model.PixelH;
            parameters[8].Value = model.FileContent;
            parameters[9].Value = model.IsProtected;
            parameters[10].Value = model.IsPublic;
            parameters[11].Value = model.Memo;
            parameters[12].Value = model.FileCreateDate;
            parameters[13].Value = model.CreateUser;
            parameters[14].Value = model.ModifyUser;
            parameters[15].Value = model.FileRoot;
            parameters[16].Value = model.Organization;
            parameters[17].Value = model.SourceRelationID;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        public bool Update(OT.Model.OTB_SYS_Document model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update OTB_SYS_Document set [FileName] = @FileName, SubFileName = @SubFileName, FilePath = @FilePath, FileSize = @FileSize, PixelW = @PixelW, PixelH = @PixelH, FileContent = @FileContent, IsProtected = @IsProtected, IsPublic = @IsPublic, Memo = @Memo, FileCreateDate = @FileCreateDate, ModifyUser = @ModifyUser, ModifyDate = GETDATE(), [status] = 2, FileRoot = @FileRoot ");
            strSql.Append(" where [GUID] = @GUID and Isnull([status],0) <> 99");
            SqlParameter[] parameters = {
                    new SqlParameter("@GUID", SqlDbType.Char,36),
                    new SqlParameter("@FileName", SqlDbType.NVarChar,200),
                    new SqlParameter("@SubFileName", SqlDbType.VarChar,10),
                    new SqlParameter("@FilePath", SqlDbType.NVarChar,500),
                    new SqlParameter("@FileSize", SqlDbType.Int),
                    new SqlParameter("@PixelW", SqlDbType.NVarChar,20),
                    new SqlParameter("@PixelH", SqlDbType.NVarChar,20),
                    new SqlParameter("@FileContent", SqlDbType.Text),
                    new SqlParameter("@IsProtected", SqlDbType.NVarChar,1),
                    new SqlParameter("@IsPublic", SqlDbType.NVarChar,1),
                    new SqlParameter("@Memo", SqlDbType.NVarChar),
                    new SqlParameter("@FileCreateDate", SqlDbType.DateTime),
                    new SqlParameter("@ModifyUser", SqlDbType.VarChar,50),
                    new SqlParameter("@FileRoot", SqlDbType.NVarChar,200)};
            parameters[0].Value = model.GUID;
            parameters[1].Value = model.FileName;
            parameters[2].Value = model.SubFileName;
            parameters[3].Value = model.FilePath;
            parameters[4].Value = model.FileSize;
            parameters[5].Value = model.PixelW;
            parameters[6].Value = model.PixelH;
            parameters[7].Value = model.FileContent;
            parameters[8].Value = model.IsProtected;
            parameters[9].Value = model.IsPublic;
            parameters[10].Value = model.Memo;
            parameters[11].Value = model.FileCreateDate;
            parameters[12].Value = model.ModifyUser;
            parameters[13].Value = model.FileRoot;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        public bool AddDirectory(OT.Model.OTB_SYS_Document model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" declare @parentID char(36) ");
            strSql.Append(" select @parentID = [GUID] from OTB_SYS_Document ");
            strSql.Append(" where [FilePath] = @FileRoot and [filetype] = 'Directory' ");
            strSql.Append(" INSERT INTO OTB_SYS_Document([GUID],[PUID],[FileName],[FileRoot],[FilePath],[FileType],[CreateUser],[CreateDate]) ");
            strSql.Append(" VALUES(@GUID,@parentID,@FileName,@FileRoot,@FilePath,'Directory',@CreateUser,GETDATE()) ");
            strSql.Append(" insert into OTB_SYS_DocumentRule(OrganizationID,RUID,FUID,[User],[Group],FileType,[Rule],CreateUser,CreateDate) ");
            strSql.Append(" values( @Organization,NEWID(),@GUID,@CreateUser,'admin','Directory','-v-w-x-d-a',@CreateUser,GETDATE() ) ");

            SqlParameter[] parameters = {
                    new SqlParameter("@GUID", SqlDbType.Char,36),
                    new SqlParameter("@PUID", SqlDbType.Char,36),
                    new SqlParameter("@FileName", SqlDbType.NVarChar,200),
                    new SqlParameter("@FileRoot", SqlDbType.NVarChar,500),
                    new SqlParameter("@FilePath", SqlDbType.NVarChar,500),
                    new SqlParameter("@CreateUser", SqlDbType.VarChar,50),
                    new SqlParameter("@Organization", SqlDbType.VarChar,50)};
            parameters[0].Value = model.GUID;
            parameters[1].Value = model.PUID;
            parameters[2].Value = model.FileName;
            parameters[3].Value = model.FileRoot;
            parameters[4].Value = model.FilePath;
            parameters[5].Value = model.CreateUser;
            parameters[6].Value = model.Organization;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        public bool UpdateDirectory(OT.Model.OTB_SYS_Document model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" INSERT INTO OTB_SYS_Document([GUID],[PUID],[FileName],[FileRoot],[FilePath],[FileType],[CreateUser],[CreateDate]) ");
            strSql.Append(" VALUES(@GUID,@PUID,@FileName,@FileRoot,@FilePath,'Directory',@CreateUser,GETDATE()) ");
            strSql.Append(" insert into OTB_SYS_DocumentRule(OrganizationID,RUID,FUID,[User],FileType,[Rule],CreateUser,CreateDate) ");
            strSql.Append(" values( @Organization,NEWID(),@GUID,@CreateUser,'File','-v-w-x-d-a',@CreateUser,GETDATE() ) ");

            strSql.Append(" update OTB_SYS_Document ");
            strSql.Append(" set [PUID] = @PUID ");
            strSql.Append(" ,[FileName] = @FileName ");
            strSql.Append(" ,ModifyUser = @ModifyUser ");
            strSql.Append(" ,ModifyDate = GETDATE() ");
            strSql.Append(" where [GUID] = @GUID and FileType = 'Directory' ");

            SqlParameter[] parameters = {
                    new SqlParameter("@GUID", SqlDbType.Char,36),
                    new SqlParameter("@PUID", SqlDbType.Char,36),
                    new SqlParameter("@FileName", SqlDbType.NVarChar,200),
                    new SqlParameter("@ModifyUser", SqlDbType.VarChar,50)};
            parameters[0].Value = model.GUID;
            parameters[1].Value = model.PUID;
            parameters[2].Value = model.FileName;
            parameters[3].Value = model.ModifyUser;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 重設更新狀態
        /// </summary>
        public bool resetStatus(string GUID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update OTB_SYS_Document set [status] = '0' where Isnull([status],0) <> 99");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 刪除一條資料
        /// </summary>
        public bool Delete(string GUID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from OTB_SYS_Document ");
            strSql.Append(" where [Status]='0' ");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 刪除一條資料
        /// </summary>
        public bool DeleteByGuid(string GUID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from OTB_SYS_Document ");
            strSql.Append(" where GUID='" + GUID + "'  ");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新圖庫資料
        /// </summary>
        public bool UpdateImgDB(string strPath)
        {
            StringBuilder strSql = new StringBuilder();
            #region Marked
            //strSql.Append(" declare @id char(36) ");

            //strSql.Append(" select top 1 @id = [GUID] from OTB_SYS_Document ");
            ////strSql.Append(" where [FileName] = @FileName AND SubFileName = @SubFileName AND [status] = '0' ");
            //strSql.Append(" where [FilePath] = @FilePath  AND [status] = '0' ");

            //strSql.Append(" if @id <> '' ");
            //strSql.Append(" begin");
            //strSql.Append(" update OTB_SYS_Document ");
            //strSql.Append(" set [FileName] = @FileName, SubFileName = @SubFileName, FilePath = @FilePath, FileSize = @FileSize, PixelW = @PixelW, PixelH = @PixelH, FileContent = @FileContent, IsProtected = @IsProtected, IsPublic = @IsPublic, Memo = @Memo, ModifyDate = GETDATE(), [Status] = '1' ");
            //strSql.Append(" where [GUID] = @id ");
            //strSql.Append(" end ");
            //strSql.Append(" else ");
            //strSql.Append(" begin ");
            //strSql.Append(" insert into OTB_SYS_Document([GUID], [FileName], SubFileName, FilePath, FileSize, PixelW, PixelH, FileContent, IsProtected, IsPublic, Memo, CreateUser, CreateDate, ModifyUser, ModifyDate, [Status]) ");
            //strSql.Append(" values(@GUID,@FileName,@SubFileName,@FilePath,@FileSize,@PixelW,@PixelH,@FileContent,@IsProtected,@IsPublic,@Memo,@CreateUser,GETDATE(),@ModifyUser,GETDATE(),'1') ");
            //strSql.Append(" end ");

            ////strSql.Append(" delete from OTB_SYS_Document ");
            ////strSql.Append(" where [Status] = '0' ");

            //SqlParameter[] parameters = {
            //        new SqlParameter("@GUID", SqlDbType.Char,36),
            //        new SqlParameter("@FileName", SqlDbType.NVarChar,50),
            //        new SqlParameter("@SubFileName", SqlDbType.VarChar,10),
            //        new SqlParameter("@FilePath", SqlDbType.NVarChar,200),
            //        new SqlParameter("@FileSize", SqlDbType.Int),
            //        new SqlParameter("@PixelW", SqlDbType.NVarChar,20),
            //        new SqlParameter("@PixelH", SqlDbType.NVarChar,20),
            //        new SqlParameter("@FileContent", SqlDbType.Text),
            //        new SqlParameter("@IsProtected", SqlDbType.NVarChar,1),
            //        new SqlParameter("@IsPublic", SqlDbType.NVarChar,1),
            //        new SqlParameter("@Memo", SqlDbType.Text),
            //        new SqlParameter("@CreateUser", SqlDbType.VarChar,50),
            //        new SqlParameter("@ModifyUser", SqlDbType.VarChar,50)};
            //parameters[0].Value = model.GUID;
            //parameters[1].Value = model.FileName;
            //parameters[2].Value = model.SubFileName;
            //parameters[3].Value = model.FilePath;
            //parameters[4].Value = model.FileSize;
            //parameters[5].Value = model.PixelW;
            //parameters[6].Value = model.PixelH;
            //parameters[7].Value = model.FileContent;
            //parameters[8].Value = model.IsProtected;
            //parameters[9].Value = model.IsPublic;
            //parameters[10].Value = model.Memo;
            //parameters[11].Value = model.CreateUser;
            //parameters[12].Value = model.ModifyUser;
            #endregion


            strPath = strPath.Replace("'", "''");   //資料庫存取單引號字串時將單引號取代成雙引號
            strSql.Append(" update OTB_SYS_Document_Temp ");
            strSql.Append(" set [status] = null ");

            //strSql.Append(" update OTB_SYS_Document ");
            //strSql.Append(" set [status] = null ");



            strSql.Append(" update OTB_SYS_Document_Temp ");
            strSql.Append(" set [status] = 1 ");
            strSql.Append(" from OTB_SYS_Document_Temp A inner join OTB_SYS_Document B  ");
            strSql.Append(" on A.FilePath = B.FilePath AND A.FileCreateDate = B.FileCreateDate AND A.FileRoot = B.FileRoot AND A.FileSize = B.FileSize AND A.PixelW = B.PixelW AND A.PixelH = B.PixelH ;");

            strSql.Append(" update OTB_SYS_Document_Temp ");
            strSql.Append(" set [status] = 2 ");
            strSql.Append(" from OTB_SYS_Document_Temp A inner join OTB_SYS_Document B  ");
            strSql.Append(" on A.FilePath = B.FilePath AND A.[status] is null;");

            strSql.Append(" update OTB_SYS_Document_Temp ");
            strSql.Append(" set [status] = 3 ");
            strSql.Append(" where [status] is null; ");

            //判斷資料夾存在設定狀態4  modify by ted 2014/11/4
            strSql.Append(" update OTB_SYS_Document ");
            strSql.Append(" set [status] = 4 ");
            strSql.Append(" where FileRoot like '" + strPath + "%' ;");


            //檔案名稱存在就給狀態1  modify by ted 2014/11/4
            strSql.Append(" update OTB_SYS_Document ");
            strSql.Append(" set [status] = 1 ");
            strSql.Append(" where FilePath in (select FilePath From OTB_SYS_Document_Temp where [status] = 1 ); ");

            strSql.Append(" update OTB_SYS_Document ");
            strSql.Append(" set [FileName] = A.[FileName], SubFileName = A.SubFileName, FilePath = A.FilePath, FileSize = A.FileSize, PixelW = A.PixelW, PixelH = A.PixelH, FileContent = A.FileContent, Memo = A.Memo, FileCreateDate = A.FileCreateDate, CreateDate = GETDATE(), ModifyDate = GETDATE(), [status] = 2, FileRoot = A.FileRoot ");
            strSql.Append(" from OTB_SYS_Document_Temp A inner join OTB_SYS_Document B  ");
            strSql.Append(" on A.FilePath = B.FilePath AND A.[status] = 2 ;");

            strSql.Append(" insert into OTB_SYS_Document([GUID], [FileName], SubFileName, FilePath, FileSize, PixelW, PixelH, FileContent, IsProtected, IsPublic, Memo, FileCreateDate, CreateUser, CreateDate, ModifyUser, ModifyDate, [status], FileRoot) ");
            strSql.Append(" select [GUID], [FileName], SubFileName, FilePath, FileSize, PixelW, PixelH, FileContent, IsProtected, IsPublic, Memo, FileCreateDate, CreateUser, GETDATE(), ModifyUser, GETDATE(), 3, FileRoot from OTB_SYS_Document_Temp  ");
            strSql.Append(" where [status] = 3; ");

            //刪除資料表當中狀態4，狀態4為更新後不存在的檔案 modify by ted 2014/11/4
            strSql.Append(" delete from OTB_SYS_Document ");
            strSql.Append(" where [status] = 4; ");

            strSql.Append(" update OTB_SYS_Document ");
            strSql.Append(" set [status] = null ");
            strSql.Append(" where FileRoot like '" + strPath + "%'; ");


            //strSql.Append(" update OTB_SYS_Document ");
            //strSql.Append(" set [status] = 1 ");
            //strSql.Append(" where [status] is null ");

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool InsertImgTempDB(OT.Model.OTB_SYS_Document model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" insert into OTB_SYS_Document_Temp([GUID], [FileName], SubFileName, FilePath, FileSize, PixelW, PixelH, FileContent, IsProtected, IsPublic, Memo, FileCreateDate, CreateUser, CreateDate, ModifyUser, ModifyDate, [status], FileRoot) ");
            strSql.Append(" values(@GUID,@FileName,@SubFileName,@FilePath,@FileSize,@PixelW,@PixelH,@FileContent,@IsProtected,@IsPublic,@Memo,@FileCreateDate,@CreateUser,GETDATE(),@ModifyUser,GETDATE(),null,@FileRoot) ");

            SqlParameter[] parameters = {
                    new SqlParameter("@GUID", SqlDbType.Char,36),
                    new SqlParameter("@FileName", SqlDbType.NVarChar,200),
                    new SqlParameter("@SubFileName", SqlDbType.VarChar,10),
                    new SqlParameter("@FilePath", SqlDbType.NVarChar,500),
                    new SqlParameter("@FileSize", SqlDbType.Int),
                    new SqlParameter("@PixelW", SqlDbType.NVarChar,20),
                    new SqlParameter("@PixelH", SqlDbType.NVarChar,20),
                    new SqlParameter("@FileContent", SqlDbType.Text),
                    new SqlParameter("@IsProtected", SqlDbType.NVarChar,1),
                    new SqlParameter("@IsPublic", SqlDbType.NVarChar,1),
                    new SqlParameter("@Memo", SqlDbType.Text),
                    new SqlParameter("@FileCreateDate", SqlDbType.DateTime),
                    new SqlParameter("@CreateUser", SqlDbType.VarChar,50),
                    new SqlParameter("@ModifyUser", SqlDbType.VarChar,50),
                    new SqlParameter("@FileRoot", SqlDbType.NVarChar,200)};
            parameters[0].Value = model.GUID;
            parameters[1].Value = model.FileName;
            parameters[2].Value = model.SubFileName;
            parameters[3].Value = model.FilePath;
            parameters[4].Value = model.FileSize;
            parameters[5].Value = model.PixelW;
            parameters[6].Value = model.PixelH;
            parameters[7].Value = model.FileContent;
            parameters[8].Value = model.IsProtected;
            parameters[9].Value = model.IsPublic;
            parameters[10].Value = model.Memo;
            parameters[11].Value = model.FileCreateDate;
            parameters[12].Value = model.CreateUser;
            parameters[13].Value = model.ModifyUser;
            parameters[14].Value = model.FileRoot;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 清空暫存資料表
        /// </summary>
        /// <returns></returns>
        public bool ClearTempDB()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("truncate table OTB_SYS_Document_Temp ");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            return true;

        }

        /// <summary>
        /// 根據GetImg讀取Src路徑
        /// </summary>
        /// <returns></returns>
        public OT.Model.OTB_SYS_Document GetImgSrc(string GUID)
        {
            StringBuilder strSql = new StringBuilder();
            OT.Model.OTB_SYS_Document otb_document_model = new Model.OTB_SYS_Document();
            strSql.Append("select FilePath,SubFileName from OTB_SYS_Document ");
            strSql.Append(" where GUID ='" + GUID + "' ");
            DataSet ds = DbHelperSQL.Query(strSql.ToString());

            if (ds.Tables[0].Rows.Count > 0)
            {
                otb_document_model.FilePath = ds.Tables[0].Rows[0]["FilePath"].ToString();
                otb_document_model.SubFileName = ds.Tables[0].Rows[0]["SubFileName"].ToString();
            }

            return otb_document_model;
        }


        /// <summary>
        /// 取得指定目錄下的圖片資料
        /// </summary>
        /// <returns></returns>
        public DataSet GetFilePath(string strOrganization, string strFileRoot, string CreateUser, string Group, string Dep, string SourceID)
        {
            StringBuilder strSql = new StringBuilder();


            //strSql.Append(" select [GUID],[PUID],[Filename],[FileRoot],[FilePath],D.[FileType],isnull([User],'') as [User] ,isnull([Group],'') as [Group],isnull([Department],'') as [Department],isnull([Rule],'') as [Rule],isnull([UnRule],'') as [UnRule] from OTB_SYS_Document D  ");
            //strSql.Append(" left join (select [FUID],[User],[Group],[Department],[FileType],[Rule],[UnRule] FROM OTB_SYS_DocumentRule where [OrganizationID] = @Organization and [USER] = @CreateUser) R ");
            //strSql.Append(" on D.[GUID] = R.FUID ");
            //strSql.Append(" where D.FileType = 'Directory' AND D.FileRoot = @FileRoot");

            //資料夾權限
            strSql.Append(" SELECT * FROM ");
            strSql.Append(" ( ");
            strSql.Append(" select ROW_NUMBER() OVER(PARTITION BY [GUID] ORDER BY [USER] desc ,[Group] desc,[Department] desc) AS RowNo, [GUID],[PUID],[Filename],[FileRoot],[FilePath],D.[FileType],isnull([User],'') as [User] ,isnull([Group],'') as [Group],isnull([Department],'') as [Department],isnull([Rule],'-') as [Rule],isnull([UnRule],'-') as [UnRule] from OTB_SYS_Document D  ");
            strSql.Append(" left join (select [FUID],[User],[Group],[Department],[FileType],[Rule],[UnRule] FROM OTB_SYS_DocumentRule  ");
            strSql.Append(" where [OrganizationID] = @Organization  ");
            strSql.Append(" and (([USER] = @CreateUser AND @CreateUser <> '')  ");
            strSql.Append(" or ([Group] in (select RuleID from OTB_SYS_MembersToRule where MemberID = @CreateUser) AND @CreateUser <> '') ");
            strSql.Append(" or ([Department] = @Dep AND @Dep <> '') ) ");
            strSql.Append(" and FileType = 'Directory' ) R  ");
            strSql.Append(" on D.[GUID] = R.FUID   ");
            strSql.Append(" where D.FileType = 'Directory' AND D.[FileRoot] = @FileRoot AND D.[IsDelete] = 'N' ");
            strSql.Append(" ) AS T ");
            strSql.Append(" WHERE T.RowNo = 1 ");

            //strSql.Append(" select D.*,isnull([User],'') as [User] ,isnull([Group],'') as [Group],isnull([Department],'') as [Department],isnull(r.[Rule],'') as [Rule],isnull([UnRule],'') as [UnRule] ");
            //strSql.Append(" ,ISNULL((select top 1 [Rule] from OTB_SYS_DocumentRule where [OrganizationID] = @Organization and [FUID] = D.[PUID] ");
            //strSql.Append(" and ((ISNULL([USER],'') = @CreateUser AND @CreateUser <> '') ");
            //strSql.Append(" or (ISNULL([Group],'') = @Group AND @Group <> '') ");
            //strSql.Append(" or (ISNULL([Department],'') = @Dep AND @Dep <> ''))),'') as [dir_Rule] from OTB_SYS_Document D  ");
            //strSql.Append(" left join (select [FUID],[User],[Group],[Department],[FileType],[Rule],[UnRule] FROM OTB_SYS_DocumentRule where [OrganizationID] = @Organization and [USER] = @CreateUser) R ");
            //strSql.Append(" on D.[GUID] = R.FUID ");
            //strSql.Append(" where D.FileType = 'File' AND D.FileRoot = @FileRoot and IsNull(D.[status],0) <> 99 ");

            strSql.Append(" SELECT * FROM ");
            strSql.Append(" ( ");
            strSql.Append(" select ROW_NUMBER() OVER(PARTITION BY [GUID] ORDER BY [USER] desc ,[Group] desc,[Department] desc) AS RowNo, [GUID],[PUID],[Filename],[CFName],[EFName],[SubFileName],[FileRoot],[FilePath],[FileSize],[PixelW],[PixelH],[FileContent],D.[Memo],D.[FileType],isnull([User],'') as [User] ,isnull([Group],'') as [Group],isnull([Department],'') as [Department],isnull(R.[Rule],'-') as [Rule],isnull(R.[UnRule],'-') as [UnRule],isnull(dir.[Rule],'-v') as [dir_Rule],isnull(dir.[UnRule],'-') as [dir_UnRule] from OTB_SYS_Document D   ");
            strSql.Append(" left join (select [FUID],[User],[Group],[Department],[FileType],[Rule],[UnRule] FROM OTB_SYS_DocumentRule  ");
            strSql.Append(" where [OrganizationID] = @Organization  ");
            strSql.Append(" and (([USER] = @CreateUser AND @CreateUser <> '')  ");
            strSql.Append(" or ([Group] in (select RuleID from OTB_SYS_MembersToRule where MemberID = @CreateUser) AND @Group <> '') ");
            strSql.Append(" or ([Department] = @Dep AND @Dep <> '') ) ");
            strSql.Append(" and FileType = 'File'  ");
            strSql.Append(" ) R  ");
            strSql.Append(" on D.[GUID] = R.FUID   ");
            strSql.Append(" left join (select [FUID],[Rule],[UnRule] FROM OTB_SYS_DocumentRule  ");
            strSql.Append(" where [OrganizationID] = @Organization  ");
            strSql.Append(" and (([USER] = @CreateUser AND @CreateUser <> '')  ");
            strSql.Append(" or ([Group] in (select RuleID from OTB_SYS_MembersToRule where MemberID = @CreateUser) AND @Group <> '') ");
            strSql.Append(" or ([Department] = @Group AND @Dep <> '') ) ");
            strSql.Append(" and FileType = 'Directory'  ");
            strSql.Append(" ) dir ");
            strSql.Append(" on D.[PUID] = dir.[FUID] ");
            if (string.IsNullOrEmpty(SourceID))
            {
                strSql.Append(" where D.FileType = 'File' AND D.FileRoot = @FileRoot AND IsNull(D.[status],0) <> 99 AND D.[IsDelete] = 'N'    ");
            }
            else
            {
                strSql.Append(" where D.FileType = 'File' AND D.FileRoot = @FileRoot AND IsNull(D.[status],0) <> 99 AND D.[IsDelete] = 'N'   AND (SourceRelationID = @SourceID OR @SourceID  IS NULL OR @SourceID='')  ");
            }
            strSql.Append(" ) AS T ");
            strSql.Append(" WHERE T.RowNo = 1 ");


            SqlParameter[] parameters = {
                    new SqlParameter("@Organization", SqlDbType.NVarChar,50),
					new SqlParameter("@FileRoot", SqlDbType.NVarChar,200),
                    new SqlParameter("@CreateUser", SqlDbType.NVarChar,50),
                    new SqlParameter("@Group", SqlDbType.NVarChar,50),
                    new SqlParameter("@Dep", SqlDbType.NVarChar,50),
                    new SqlParameter("@SourceID", SqlDbType.NVarChar,50)
                                        };
            parameters[0].Value = strOrganization;
            parameters[1].Value = strFileRoot;
            parameters[2].Value = CreateUser;
            parameters[3].Value = Group;
            parameters[4].Value = Dep;
            parameters[5].Value = SourceID;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 根據檔案路徑取得GUID
        /// </summary>
        /// <returns></returns>
        public string GetGuIdByFilePath(string strFilePath)
        {
            strFilePath = strFilePath.Replace("'", "''");
            OT.Model.OTB_SYS_Document otb_document_model = new Model.OTB_SYS_Document();
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select top 1 [GUID] from OTB_SYS_Document ");
            strSql.Append(" where FilePath = N'" + strFilePath + "' and Isnull([status],0) <> 99");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());

            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0]["GUID"].ToString();
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 即時更新儲存圖檔資訊
        /// </summary>
        /// <returns></returns>
        public bool SaveMemo(string strFileId, string strFileName, string strCFName, string strEFName, string strNumber, string strFilePath, string strUpdateMemo, string strModifyUser)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" update OTB_SYS_Document ");
            strSql.Append(" set ");
            if (strCFName != "" || strEFName != "")
            {
                strSql.Append(" FileName = @FileName, ");
                strSql.Append(" CFName = @CFName, ");
                strSql.Append(" EFName = @EFName, ");
                strSql.Append(" FileNumber = @strNumber, ");
                strSql.Append(" FilePath = @FilePath, ");
            }
            strSql.Append(" Memo = @Memo, ");
            strSql.Append(" ModifyUser = @ModifyUser, ");
            strSql.Append(" ModifyDate = GETDATE() ");
            strSql.Append(" where [GUID] = @GUID");

            SqlParameter[] parameters = {
                    new SqlParameter("@GUID", SqlDbType.Char,36),
                    new SqlParameter("@FileName", SqlDbType.NVarChar,200),
                    new SqlParameter("@CFName", SqlDbType.NVarChar,100),
                    new SqlParameter("@EFName", SqlDbType.NVarChar,100),
                    new SqlParameter("@strNumber", SqlDbType.NVarChar,20),
                    new SqlParameter("@FilePath", SqlDbType.NVarChar,500),
                    new SqlParameter("@Memo", SqlDbType.NVarChar),
                    new SqlParameter("@ModifyUser", SqlDbType.NVarChar,50)};
            parameters[0].Value = strFileId;
            parameters[1].Value = strFileName;
            parameters[2].Value = strCFName;
            parameters[3].Value = strEFName;
            parameters[4].Value = strNumber;
            parameters[5].Value = strFilePath;
            parameters[6].Value = strUpdateMemo;
            parameters[7].Value = strModifyUser;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// DirMove資料夾移動
        /// </summary>
        /// <returns></returns>
        public bool DirMove(string strorifilename, string strnewfilename, string strnewfileroot, string strnewfilepath, string strModifyUser)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" update OTB_SYS_Document ");
            strSql.Append(" set FileName = @newFileName, ");
            strSql.Append(" FileRoot = @newFileRoot, ");
            strSql.Append(" FilePath = @newFilePath, ");
            strSql.Append(" ModifyUser = @ModifyUser, ");
            strSql.Append(" ModifyDate = GETDATE() ");
            strSql.Append(" where [FilePath] = @oriFilePath and Isnull([status],0) <> 99");

            SqlParameter[] parameters = {
                    new SqlParameter("@oriFilePath", SqlDbType.NVarChar,500),
                    new SqlParameter("@newFileName", SqlDbType.NVarChar,200),
                    new SqlParameter("@newFileRoot", SqlDbType.NVarChar,200),
                    new SqlParameter("@newFilePath", SqlDbType.NVarChar,500),
                    new SqlParameter("@ModifyUser", SqlDbType.NVarChar,50)};
            parameters[0].Value = strorifilename;
            parameters[1].Value = strnewfilename;
            parameters[2].Value = strnewfileroot;
            parameters[3].Value = strnewfilepath;
            parameters[4].Value = strModifyUser;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// DirectoryRename資料夾重新命名
        /// </summary>
        /// <returns></returns>
        public bool DirectoryRename(string strGUID, string strDirName, string strOldPathName, string strNewPathName, string strModifyUser)
        {
            strOldPathName.Replace("'", "''");
            strNewPathName.Replace("'", "''");
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" update OTB_SYS_Document ");
            strSql.Append(" set FileRoot = Replace(FileRoot,@strOldPathName,@strNewPathName), ");
            strSql.Append(" FilePath = Replace(FilePath,@strOldPathName,@strNewPathName), ");
            strSql.Append(" ModifyUser = @ModifyUser, ");
            strSql.Append(" ModifyDate = GETDATE() ");
            strSql.Append(" where [FileRoot] LIKE @strOldPathName + '%' and Isnull([status],0) <> 99 ");
            //更新資料夾名稱 add by Ted 2015/03/09
            strSql.Append(" update OTB_SYS_Document ");
            strSql.Append(" set [FileName] = @DirName ");
            strSql.Append("   , [FilePath] = Replace(FilePath,@strOldPathName,@strNewPathName) ");
            strSql.Append("   , ModifyUser = @ModifyUser ");
            strSql.Append("   , ModifyDate = GETDATE() ");
            strSql.Append(" where [GUID] = @GUID ");


            SqlParameter[] parameters = {
                    new SqlParameter("@strOldPathName", SqlDbType.NVarChar,500),
                    new SqlParameter("@strNewPathName", SqlDbType.NVarChar,500),
                    new SqlParameter("@ModifyUser", SqlDbType.NVarChar,50),
                    new SqlParameter("@GUID", SqlDbType.Char,36),
                    new SqlParameter("@DirName", SqlDbType.NVarChar,200)};
            parameters[0].Value = strOldPathName;
            parameters[1].Value = strNewPathName;
            parameters[2].Value = strModifyUser;
            parameters[3].Value = strGUID;
            parameters[4].Value = strDirName;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// SingleFileMove 單圖檔移動
        /// </summary>
        /// <returns></returns>
        public bool SingleFileMove(string guid, string filename, string fileroot, string filepath, string strModifyUser)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" update OTB_SYS_Document ");
            strSql.Append(" set FileRoot = @fileroot, ");
            strSql.Append(" FilePath = @filepath, ");
            strSql.Append(" FileName = @filename, ");
            strSql.Append(" ModifyUser = @ModifyUser, ");
            strSql.Append(" ModifyDate = GETDATE() ");
            strSql.Append(" where [GUID] = @guid");

            SqlParameter[] parameters = {
                    new SqlParameter("@guid", SqlDbType.Char,36),
                    new SqlParameter("@filename", SqlDbType.NVarChar,200),
                    new SqlParameter("@fileroot", SqlDbType.NVarChar,500),
                    new SqlParameter("@filepath", SqlDbType.NVarChar,500),
                    new SqlParameter("@ModifyUser", SqlDbType.NVarChar,50)};
            parameters[0].Value = guid;
            parameters[1].Value = filename;
            parameters[2].Value = fileroot;
            parameters[3].Value = filepath;
            parameters[4].Value = strModifyUser;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 初始化資料夾狀態
        /// </summary>
        /// <returns></returns>
        public bool InitDirectory()
        {
            StringBuilder strSql = new StringBuilder();
            //--初始化資料夾狀態
            strSql.Append(" UPDATE OTB_SYS_Document SET [status] = '-1' WHERE FileType = 'Directory' ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), "");
            if (rows >= 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新資料夾
        /// </summary>
        /// <returns></returns>
        public bool UpdateDirectoryToDB(OT.Model.OTB_SYS_Document model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" DECLARE @COUNT INT, ");
            strSql.Append(" @PUID CHAR(36) ");
            strSql.Append(" SELECT @COUNT = COUNT(0) FROM OTB_SYS_Document ");
            strSql.Append(" WHERE FilePath = @FilePath AND FileType = 'Directory' ");

            //--判斷是否有上層
            strSql.Append(" SELECT @PUID = [GUID] FROM OTB_SYS_Document WHERE FilePath = @FileRoot AND FileType = 'Directory' ");

            strSql.Append(" IF @COUNT = 0 ");
            strSql.Append(" BEGIN ");
            //strSql.Append(" IF (@PUID IS NULL OR @PUID = '') SET @PUID = NEWID(); ");
            //--新增資料夾
            strSql.Append(" declare @New_GUID char(36) ");
            strSql.Append(" set @New_GUID = NEWID() ");
            strSql.Append(" INSERT INTO OTB_SYS_Document([GUID],[PUID],[FileName],[FileRoot],[FilePath],[FileType],[CreateUser],[CreateDate]) ");
            strSql.Append(" VALUES(@New_GUID,@PUID,@FileName,@FileRoot,@FilePath,'Directory',@CreateUser,GETDATE()) ");
            //--更新新增資料夾狀態
            strSql.Append(" UPDATE OTB_SYS_Document SET [status] = '3' WHERE [GUID] = @New_GUID AND FileType = 'Directory' ");
            strSql.Append(" END ");
            strSql.Append(" ELSE ");
            strSql.Append(" BEGIN ");
            //--更新PUID
            strSql.Append(" UPDATE OTB_SYS_Document ");
            strSql.Append(" SET [PUID] = @PUID ");
            strSql.Append(" ,[FileName] = @FileName ");
            strSql.Append(" ,[FileRoot] = @FileRoot ");
            strSql.Append(" ,[FilePath] = @FilePath ");
            strSql.Append(" ,[FileType] = 'Directory' ");
            strSql.Append(" ,[CreateUser] = @CreateUser ");
            strSql.Append(" ,[CreateDate] = GETDATE() ");
            strSql.Append(" WHERE FilePath = @FilePath AND FileType = 'Directory' ");
            //--更新新增資料夾狀態
            strSql.Append(" UPDATE OTB_SYS_Document SET [status] = '1' WHERE [FileName] = @FileName AND [FilePath] = @FilePath AND FileType = 'Directory' ");
            strSql.Append(" END ");


            //--更新檔案所屬資料夾
            strSql.Append(" SELECT @PUID = [GUID] FROM OTB_SYS_Document WHERE FilePath = @FilePath AND FileType = 'Directory' ");
            strSql.Append(" UPDATE OTB_SYS_Document ");
            strSql.Append(" SET PUID = @PUID ");
            strSql.Append(" WHERE FileRoot = @FilePath AND FileType = 'File' ");

            //更新資料夾權限
            strSql.Append(" insert into OTB_SYS_DocumentRule(OrganizationID,RUID,FUID,[User],[Group],FileType,[Rule],CreateUser,CreateDate) ");
            strSql.Append(" select @Organization,NEWID(),[GUID],CreateUser,'admin',FileType,'-v-w-x-d-a',CreateUser,GETDATE() from OTB_SYS_Document ");
            strSql.Append(" where not exists (select [FUID] from OTB_SYS_DocumentRule where [FUID] = [GUID] and [Group] = 'admin') ");


            SqlParameter[] parameters = {
                    new SqlParameter("@FileName", SqlDbType.NVarChar,200),
                    new SqlParameter("@FileRoot", SqlDbType.NVarChar,500),
                    new SqlParameter("@FilePath", SqlDbType.NVarChar,500),
                    new SqlParameter("@CreateUser", SqlDbType.NVarChar,50),
                    new SqlParameter("@Organization", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.FileName;
            parameters[1].Value = model.FileRoot;
            parameters[2].Value = model.FilePath;
            parameters[3].Value = model.CreateUser;
            parameters[4].Value = model.Organization;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);

            if (rows >= 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 刪除資料夾
        /// </summary>
        /// <returns></returns>
        public bool DeleteDirectory()
        {
            StringBuilder strSql = new StringBuilder();

            //--刪除狀態為-1的資料夾(代表不存在實體)
            strSql.Append(" DELETE FROM OTB_SYS_Document WHERE [status] = '-1' ");

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), "");
            if (rows >= 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion  Method
    }
}