|
|
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
}}
|
