|
|
using System;using System.Collections.Generic;using System.Linq;using System.Text;using OT.IDAL;using System.Data;using System.Data.SqlClient;using DBUtility;
namespace OT.SQLServerDAL{ public class CommonClass : ICommonClass { #region 下拉選單
/// <summary>
/// 得到一個模組實體
/// </summary>
/// 使用地方: Menu.aspx.cs by Gary 2013/12/23
public DataSet GetDDLModuleList(string UserRoleId) { StringBuilder strSql = new StringBuilder(); strSql.Append(" WITH tr (ModuleID,ModuleName,ParentID) "); strSql.Append(" as( "); strSql.Append(" SELECT ModuleID,ModuleName,ParentID "); strSql.Append(" FROM OTB_SYS_ModuleList WHERE ModuleID in ( SELECT DISTINCT ModuleID "); strSql.Append(" FROM OTB_SYS_Authorize AS SA "); strSql.Append(" INNER JOIN dbo.OTB_SYS_ProgramList AS PL ON SA.ProgramID = PL.ProgramID "); strSql.Append(" WHERE SA.RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RULEID) and SA.AllowRight <>'' "); strSql.Append(" ) "); strSql.Append(" union all "); strSql.Append(" SELECT b.ModuleID,b.ModuleName,b.ParentID "); strSql.Append(" FROM OTB_SYS_ModuleList as b "); strSql.Append(" inner join tr on b.ModuleID = tr.ParentID "); strSql.Append(" ) "); strSql.Append(" SELECT distinct "); strSql.Append(" SM .ModuleID "); strSql.Append(" ,SM .ModuleName "); strSql.Append(" ,SM .ParentID "); strSql.Append(" FROM [OTB_SYS_ModuleList] AS SM "); strSql.Append(" inner join tr on sm.ModuleID in(tr.ModuleID) "); strSql.Append(" WHERE ISNULL(SM .ParentID,'')='' "); SqlParameter[] parameters = { new SqlParameter("@RuleID", SqlDbType.NVarChar,50) }; parameters[0].Value = UserRoleId; return DbHelperSQL.Query(strSql.ToString(), parameters); //return DbHelperSQL.RunProcedure("OSP_Common_DDL_GetModuleListByUserGroupId", parameters, "ModuleList");
}
/// <summary>
/// 得到一個程式清單實體
/// </summary>
/// <param name="ModuleID"></param>
/// <param name="UserRoleId"></param>
/// <returns></returns>
/// 使用地方: ModuleDefault.aspx.cs by Gary 2013/12/23
public DataSet GetProgramListByModuleID(string ModuleID, string UserRoleId) { StringBuilder strSql = new StringBuilder(); //--Table1
strSql.Append(" WITH ViewList (ModuleID,ParentID) "); strSql.Append(" as( "); strSql.Append(" SELECT ModuleID,ParentID "); strSql.Append(" FROM OTB_SYS_ModuleList WHERE ModuleID in ( SELECT DISTINCT ModuleID "); strSql.Append(" FROM OTB_SYS_Authorize AS SA "); strSql.Append(" INNER JOIN dbo.OTB_SYS_ProgramList AS PL ON SA.ProgramID = PL.ProgramID "); strSql.Append(" WHERE SA.RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RuleID) and SA.AllowRight <> '' "); strSql.Append(" ) "); strSql.Append(" union all "); strSql.Append(" SELECT b.ModuleID,b.ParentID "); strSql.Append(" FROM OTB_SYS_ModuleList as b "); strSql.Append(" inner join ViewList on b.ModuleID = ViewList.ParentID "); strSql.Append(" WHERE ViewList.ModuleID<>b.ModuleID "); strSql.Append(" ), ViewList1(ModuleID,ParentID,Level,AccountNameSort,ModuleIDSort,MainModule, ModuleName,OrderByValue,BgCss) "); strSql.Append(" as( "); strSql.Append(" SELECT ModuleID,ParentID,0 as Level,CONVERT(nvarchar(128),ModuleName),CONVERT(nvarchar(128),ModuleID),ModuleID,ModuleName,OrderByValue,BgCss "); strSql.Append(" FROM OTB_SYS_ModuleList WHERE ParentID='' "); strSql.Append(" union all "); strSql.Append(" SELECT a.ModuleID,a.ParentID,b.Level+1,CONVERT(nvarchar(128),b.AccountNameSort+'/'+CONVERT(nvarchar(128),a.ModuleName)),CONVERT(nvarchar(128),b.ModuleIDSort+'/'+CONVERT(nvarchar(128),a.ModuleID)),b.MainModule,a.ModuleName,a.OrderByValue,a.BgCss "); strSql.Append(" FROM OTB_SYS_ModuleList as a "); strSql.Append(" inner join ViewList1 as b on b.ModuleID=a.ParentID "); strSql.Append(" ),ViewList2 (ModuleID,ParentID,Level,AccountNameSort, ModuleIDSort,MainModule,ModuleName,OrderByValue,BgCss) "); strSql.Append(" as "); strSql.Append(" ( "); strSql.Append(" select distinct * FROM ViewList1 WHERE ModuleID in (select ModuleID FROM ViewList) "); strSql.Append(" ) "); strSql.Append(" SELECT * FROM (SELECT DISTINCT ModuleID "); strSql.Append(" , ModuleName "); strSql.Append(" ,ParentID "); strSql.Append(" ,'#' AS FILEPATH "); strSql.Append(" ,'folder' AS ImgPath "); strSql.Append(" ,AccountNameSort "); strSql.Append(" ,ModuleIDSort "); strSql.Append(" ,OrderByValue "); strSql.Append(" ,ISNULL(BgCss,'red-box')as BgCss "); strSql.Append(" FROM ViewList2 "); strSql.Append(" WHERE ModuleID <> REPLACE(@ModuleID,'%','') and ParentID<>'' and CHARINDEX(REPLACE(@ModuleID,'%','') ,ModuleIDSort) > 0 "); strSql.Append(" UNION ALL "); strSql.Append(" SELECT DISTINCT "); strSql.Append(" PL.ProgramID AS ModuleID "); strSql.Append(" ,ProgramName AS ModuleName "); strSql.Append(" ,ModuleID AS ParentID "); strSql.Append(" ,FilePath "); strSql.Append(" ,ISNULL(ImgPath,'') AS ImgPath "); strSql.Append(" ,NULL AS AccountNameSort "); strSql.Append(" ,NULL AS ModuleIDSort "); strSql.Append(" ,OrderByValue "); strSql.Append(" ,BgCss "); strSql.Append(" FROM OTB_SYS_Authorize AS SA "); strSql.Append(" INNER JOIN dbo.OTB_SYS_ProgramList AS PL ON SA.ProgramID = PL.ProgramID "); strSql.Append(" WHERE ModuleID = REPLACE(@ModuleID,'%','') "); strSql.Append(" AND ShowInList = 'Y' "); strSql.Append(" AND SA.RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RuleID) "); strSql.Append(" AND CHARINDEX('View',SA.AllowRight)>0)as abc "); strSql.Append(" ORDER BY case when FILEPATH ='#' then 0 else 1 end,OrderByValue,ModuleID ; ");
// --Table2
strSql.Append(" SELECT DISTINCT "); strSql.Append(" PL.ProgramID "); strSql.Append(" ,ProgramName "); strSql.Append(" ,ModuleID "); strSql.Append(" ,pl.FilePath "); strSql.Append(" ,ISNULL(att.FilePath,'') AS ImgPath "); strSql.Append(" ,OrderByValue "); strSql.Append(" FROM [OTB_SYS_ProgramList] AS PL "); strSql.Append(" left join OTB_SYS_Attachments as att on pl.ImgPath =att.TargetRelationID "); strSql.Append(" INNER JOIN OTB_SYS_Authorize AS SA ON SA.ProgramID = PL.ProgramID "); strSql.Append(" WHERE SA.RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RuleID) AND CHARINDEX('View',SA.AllowRight)>0 "); strSql.Append(" AND ShowInList = 'Y' "); strSql.Append(" ORDER BY ModuleID,OrderByValue; ");
strSql.Append(" with ViewList1(ModuleID,ParentID,Level,AccountNameSort,ModuleIDSort, ModuleName,OrderByValue,BgCss) "); strSql.Append(" as( "); strSql.Append(" SELECT ModuleID,ParentID,0 as Level,CONVERT(nvarchar(128),ModuleName),CONVERT(nvarchar(128),ModuleID),ModuleName,OrderByValue,BgCss "); strSql.Append(" FROM OTB_SYS_ModuleList WHERE ParentID='' "); strSql.Append(" union all "); strSql.Append(" SELECT a.ModuleID,a.ParentID,b.Level+1,CONVERT(nvarchar(128),b.AccountNameSort+'/'+CONVERT(nvarchar(128),a.ModuleName)),CONVERT(nvarchar(128),b.ModuleIDSort+'/'+CONVERT(nvarchar(128),a.ModuleID)),a.ModuleName,a.OrderByValue,a.BgCss "); strSql.Append(" FROM OTB_SYS_ModuleList as a "); strSql.Append(" inner join ViewList1 as b on b.ModuleID=a.ParentID "); strSql.Append(" ) "); strSql.Append(" SELECT a.ModuleID,a.ParentID,b.AccountNameSort,b.ModuleIDSort,a.ModuleName FROM dbo.OTB_SYS_ModuleList as a "); strSql.Append(" inner join ViewList1 as b on a.ModuleID =b.ModuleID "); strSql.Append(" WHERE a.ModuleID = REPLACE(@ModuleID,'%','') ; ");
//--Table3
strSql.Append(" WITH ViewList (ModuleID,ParentID) "); strSql.Append(" as "); strSql.Append(" ( "); strSql.Append(" SELECT ModuleID,ParentID "); strSql.Append(" FROM OTB_SYS_ModuleList WHERE ModuleID in ( SELECT DISTINCT ModuleID "); strSql.Append(" FROM OTB_SYS_Authorize AS SA "); strSql.Append(" INNER JOIN dbo.OTB_SYS_ProgramList AS PL ON SA.ProgramID = PL.ProgramID "); strSql.Append(" WHERE SA.RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RuleID)and SA.AllowRight <>'' "); strSql.Append(" ) "); strSql.Append(" union all ");
strSql.Append(" SELECT b.ModuleID,b.ParentID "); strSql.Append(" FROM OTB_SYS_ModuleList as b "); strSql.Append(" inner join ViewList on b.ModuleID = ViewList.ParentID "); strSql.Append(" WHERE ViewList.ModuleID<>b.ModuleID "); strSql.Append(" ) "); strSql.Append(" ,ViewList1(ModuleID,ParentID,Level,AccountNameSort,ModuleIDSort,MainModule, ModuleName,OrderByValue,BgCss) "); strSql.Append(" as "); strSql.Append(" ( "); strSql.Append(" SELECT ModuleID,ParentID,0 as Level,CONVERT(nvarchar(128),ModuleName),CONVERT(nvarchar(128),ModuleID),ModuleID,ModuleName,OrderByValue,BgCss "); strSql.Append(" FROM OTB_SYS_ModuleList WHERE ParentID='' "); strSql.Append(" union all "); strSql.Append(" SELECT a.ModuleID,a.ParentID,b.Level+1,CONVERT(nvarchar(128),b.AccountNameSort+'/'+CONVERT(nvarchar(128),a.ModuleName)),CONVERT(nvarchar(128),b.ModuleIDSort+'/'+CONVERT(nvarchar(128),a.ModuleID)),b.MainModule,a.ModuleName,a.OrderByValue,a.BgCss "); strSql.Append(" FROM OTB_SYS_ModuleList as a "); strSql.Append(" inner join ViewList1 as b on b.ModuleID=a.ParentID "); strSql.Append(" ) "); strSql.Append(" ,ViewList2 (ModuleID,ParentID,Level,AccountNameSort, ModuleIDSort,MainModule,ModuleName,OrderByValue,BgCss) "); strSql.Append(" as "); strSql.Append(" ( "); strSql.Append(" select * FROM ViewList1 WHERE ModuleID in (select ModuleID FROM ViewList) "); strSql.Append(" ) "); strSql.Append(" SELECT ModuleID,ParentID,ModuleName,BgCss,'#'as FilePath,'folder' AS ImgPath,'ModuleDefault' as grouptag ,AccountNameSort,ModuleIDSort,'Y' as ShowInList,OrderByValue FROM ViewList2 "); strSql.Append(" union all "); strSql.Append(" SELECT DISTINCT "); strSql.Append(" PL.ProgramID AS ModuleID "); strSql.Append(" ,ModuleID AS ParentID "); strSql.Append(" ,ProgramName AS ModuleName,PL.BgCss,PL.FilePath,PL.ImgPath,PL.grouptag,'','',ShowInList ,PL.OrderByValue as OrderByValue "); strSql.Append(" FROM OTB_SYS_Authorize AS SA "); strSql.Append(" INNER JOIN dbo.OTB_SYS_ProgramList AS PL ON SA.ProgramID = PL.ProgramID "); strSql.Append(" WHERE ModuleID in(select ModuleID FROM ViewList2 ) "); strSql.Append(" AND SA.RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RULEID) "); strSql.Append(" AND CHARINDEX('View',SA.AllowRight)>0 "); strSql.Append(" Order By ParentID,imgpath,OrderByValue "); SqlParameter[] parameters = { new SqlParameter("@ModuleID", SqlDbType.VarChar,10), new SqlParameter("@RuleID", SqlDbType.NVarChar,50) }; parameters[0].Value = ModuleID + "%"; parameters[1].Value = UserRoleId; //return DbHelperSQL.RunProcedure("OSP_Common_DDL_GetProgramListByModuleIDAndRoleID", parameters, "ds");
return DbHelperSQL.Query(strSql.ToString(), parameters);
}
/// <summary>
/// 取得所有使用者清單
/// modify by Dean 2013/08/07
/// </summary>
/// <param name="NULL"></param>
/// <returns></returns>
/// 使用地方: RoleMaintain_Upd.aspx.cs by Gary 2013/12/23
public DataSet GetUSERACCOUNTList(string NULL) { StringBuilder strSql = new StringBuilder(); strSql.Append(" select memberid as ACCOUNT, MemberName as NAME "); strSql.Append(" from [OTB_SYS_Members] "); strSql.Append(" where Effective ='Y' "); strSql.Append(" order by MemberName "); SqlParameter[] parameters = { new SqlParameter("@NULL", SqlDbType.VarChar,50), }; parameters[0].Value = NULL; //return DbHelperSQL.RunProcedure("OSP_Common_GetUSERACCOUNTList", parameters, "ds");
return DbHelperSQL.Query(strSql.ToString(), parameters); }
/// <summary>
/// <summary>
/// 取得目前系統內所有沒有被館別使用的大分類
/// </summary>
/// <param name="NULL"></param>
/// <returns></returns>
/// 使用地方: TvlShopMaintain_Upd.aspx.cs by Alina 2014/9/24
public DataSet GetChinWDCCOUNTList(string NULL) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT Data_Value as LargeID ,Chin_WD as LargeName "); strSql.Append(" FROM TRDATA.DBO.TRWORD "); strSql.Append(" WHERE CLS_CD='TVL_AREA' "); strSql.Append(" AND Data_Value NOT IN ( "); strSql.Append(" SELECT col "); strSql.Append("FROM dbo.[OTB_ADV_TvlShop] "); strSql.Append(" OUTER apply "); strSql.Append("( select n.r.value('.', 'varchar(50)') as col "); strSql.Append(" from (select cast('<r>'+replace(Classifications, '|', '</r><r>')+'</r>' as xml )) "); strSql.Append(" as s(XMLCol) cross apply s.XMLCol.nodes('r') as n(r) "); strSql.Append(") xm "); strSql.Append(" WHERE col<>'' AND col IS NOT NULL "); strSql.Append(") "); strSql.Append(" order by Chin_WD "); SqlParameter[] parameters = { new SqlParameter("@NULL", SqlDbType.VarChar,50), }; parameters[0].Value = NULL; return DbHelperSQL.Query(strSql.ToString(), parameters); }
#endregion
#region 取流水號
/// <summary>
/// 獲取大類別編號的最大數字
/// </summary>
/// <param name="ArgumentClassID">大類別代號</param>
/// <returns>該類別的最大編號</returns>
/// 使用地方: BasePage.cs by Gary 2013/12/23
public int GetArgumentClassMaxNumber(string ArgumentClassID, string ModifyUser) { int rowsAffected; SqlParameter[] parameters = { new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10), new SqlParameter("@ModifyUser", SqlDbType.VarChar,50) }; parameters[0].Value = ArgumentClassID; parameters[1].Value = ModifyUser;
return DbHelperSQL.RunProcedure("OSP_Common_GetArgumentClassMaxNumber", parameters, out rowsAffected); }
/// <summary>
/// 獲取小類別編號的最大數字
/// </summary>
/// <param name="ArgumentClassID">大類別代號</param>
/// <param name="ArgumentID">小類別代號</param>
/// <returns></returns>
/// 使用地方: BasePage.cs by Gary 2013/12/23
public int GetArgumentMaxNumber(string ArgumentClassID, string ArgumentID, string ModifyUser) { int rowsAffected; SqlParameter[] parameters = { new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10), new SqlParameter("@ArgumentID", SqlDbType.NVarChar,20), new SqlParameter("@ModifyUser", SqlDbType.VarChar,50), }; parameters[0].Value = ArgumentClassID; parameters[1].Value = ArgumentID; parameters[2].Value = ModifyUser;
return DbHelperSQL.RunProcedure("OSP_Common_GetArgumentMaxNumber", parameters, out rowsAffected); }
#endregion
#region 取程式列表
#region GetProgramListByRuleId
/// <summary>
/// 依據角色ID取得程式列表信息
/// </summary>
/// <param name="strRuleId">角色ID</param>
/// <returns>程式列表信息</returns>
/// 使用地方: AuthantedPrograms.aspx.cs by Gary 2013/12/23
public DataSet GetProgramListByRuleId(string strRuleId) { StringBuilder strSql = new StringBuilder(); strSql.Append(" WITH ModuleLayer (ParentID,ModuleID,ModuleName,OrderByValue,Module_PATH,Module_LEVEL) AS "); strSql.Append(" ("); strSql.Append(" select ParentID,ModuleID,ModuleName,OrderByValue,CAST(ModuleName as varchar(MAX)),0 AS Module_LEVEL"); strSql.Append(" from OTB_SYS_ModuleList"); strSql.Append(" where ParentID = '' AND ModuleID <> '001'"); strSql.Append(" union all "); strSql.Append(" select M.ParentID,M.ModuleID,M.ModuleName,M.OrderByValue,CAST(ML.Module_PATH+' > '+M.ModuleName as varchar(MAX)),ML.Module_LEVEL+1"); strSql.Append(" from OTB_SYS_ModuleList M INNER JOIN ModuleLayer ML on M.ParentID=ML.ModuleID"); strSql.Append(" ),"); strSql.Append(" kk as ("); strSql.Append(" select MM.ModuleID as ParentID,MM.ModuleID,'' as ModuleName, PL.ProgramID,PL.ProgramName,FilePath,AllowRight,AllowRight as CanAllowRight,ProgramType,PL.OrderByValue,PL.Effective,PL.Memo,Module_PATH,Module_LEVEL from ModuleLayer MM"); strSql.Append(" LEFT join OTB_SYS_ProgramList PL"); strSql.Append(" on MM.ModuleID = PL.ModuleID"); strSql.Append(" where PL.ProgramType <> 'S'"); strSql.Append(" union all"); strSql.Append(" select ParentID, ModuleID, ModuleName, '' as ProgramID, ModuleName as ProgramName, '#' as FilePath, '' as AllowRight, '' as CanAllowRight, 'M' ProgramType, OrderByValue, '' as Effective, '' as Memo, Module_PATH, Module_LEVEL from ModuleLayer"); strSql.Append(" )"); strSql.Append(" select ROW_NUMBER() OVER(order by Module_PATH, Module_LEVEL, ProgramType, OrderByValue) as RowId, @RuleID as RuleID, ParentID,ModuleID,ModuleName,MP.ProgramID,ProgramName,FilePath,AUT.AllowRight,MP.AllowRight as CanAllowRight,ProgramType as PageType,OrderByValue,Effective,ISNULL(MP.Memo,'') as Memo,Module_PATH,Module_LEVEL from kk as MP"); strSql.Append(" left join OTB_SYS_Authorize AUT ON AUT.ProgramID = MP.ProgramID AND AUT.RuleID = @RuleID where ParentID <> ''"); #region is Marked
//strSql.Append(" SELECT ");
//strSql.Append(" (ROW_NUMBER() OVER(ORDER BY OrderByValue)) as RowId ");
//strSql.Append(" ,NULL AS ProgramID ");
//strSql.Append(" ,ModuleName AS ProgramName ");
//strSql.Append(" ,ModuleID ");
//strSql.Append(" ,ModuleName ");
//strSql.Append(" ,NULL AS FilePath ");
//strSql.Append(" ,OrderByValue ");
//strSql.Append(" ,NULL AS ProgramType ");
//strSql.Append(" ,NULL AS Effective ");
//strSql.Append(" ,Memo ");
//strSql.Append(" ,NULL AS AllowRight ");
//strSql.Append(" ,NULL AS CanAllowRight ");
//strSql.Append(" ,@RuleID AS RuleID ");
//strSql.Append(" ,'M' AS PageType ");
//strSql.Append(" FROM [OTB_SYS_ModuleList] ");
//strSql.Append(" UNION ALL ");
//strSql.Append(" SELECT ");
//strSql.Append(" (ROW_NUMBER() OVER(ORDER BY SML.ModuleID,SPL.OrderByValue)) as RowId ");
//strSql.Append(" ,SPL.ProgramID ");
//strSql.Append(" ,SPL.ProgramName ");
//strSql.Append(" ,SML.ModuleID ");
//strSql.Append(" ,SML.ModuleName ");
//strSql.Append(" ,SPL.FilePath ");
//strSql.Append(" ,SPL.OrderByValue ");
//strSql.Append(" ,SPL.ProgramType ");
//strSql.Append(" ,SPL.Effective ");
//strSql.Append(" ,SPL.Memo ");
//strSql.Append(" ,AUT.AllowRight ");
//strSql.Append(" ,SPL.AllowRight AS CanAllowRight ");
//strSql.Append(" ,@RuleID AS RuleID ");
//strSql.Append(" ,'P' AS PageType ");
//strSql.Append(" FROM OTB_SYS_ProgramList SPL ");
//strSql.Append(" INNER JOIN OTB_SYS_ModuleList SML ON SPL.ModuleID = SML.ModuleID ");
//strSql.Append(" LEFT JOIN OTB_SYS_Authorize AUT ON AUT.ProgramID = SPL.ProgramID AND AUT.RuleID = @RuleID ");
//strSql.Append(" WHERE SPL.Effective = 'Y' AND SPL.ProgramType <> 'S' ");
//strSql.Append(" ORDER BY ModuleID,Pagetype,ProgramID ");
#endregion
SqlParameter[] parameters = { new SqlParameter("@RuleID", SqlDbType.VarChar,20) }; parameters[0].Value = strRuleId; return DbHelperSQL.Query(strSql.ToString(), parameters); //return DbHelperSQL.RunProcedure("OSP_Common_GetProgramListByRuleId", parameters, "ds");
} #endregion
#endregion
#region 取Text值
/// 詢問過Dean 主要工作取日期用 by Gary 2013/12/23
public String GetDate() { return DbHelperSQL.GetSingle("OSP_Common_DDL_GetDATA").ToString(); }
#endregion
#region 取參數值列表
#region GetArgumentListByArgumentClassID
/// <summary>
/// 依據參數類別ID取得參數值
/// </summary>
/// <param name="strArgumentClassID">參數類別ID</param>
/// <returns>參數值列表信息</returns>
/// 使用地方:HRMMaintain_Upd.aspx.cs 、 MemberMaintain_Upd.aspx.cs by Gary 2013/12/23
public DataSet GetArgumentListByArgumentClassID(string strArgumentClassID) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" ArgumentValue,ArgumentID,OrderByValue "); strSql.Append(" FROM [OTB_SYS_Arguments] "); strSql.Append(" WHERE (ArgumentClassID = @ArgumentClassID OR @ArgumentClassID IS NULL OR @ArgumentClassID='') order by OrderByValue "); SqlParameter[] parameters = { new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10) }; parameters[0].Value = strArgumentClassID; return DbHelperSQL.Query(strSql.ToString(), parameters); //return DbHelperSQL.RunProcedure("OSP_Common_GetArgumentListByArgumentClassID", parameters, "ArgumentList");
} #endregion
#endregion
#region 內部系統
/// <summary>
/// 獲取系統參數
/// </summary>
/// <param name="strItem">參數名稱</param>
/// <param name="SettingValueOrMemo">參數取向值</param>
/// <returns></returns>
public string GetSystemSetting(string strItem, string SettingValueOrMemo) { StringBuilder sbSql = new StringBuilder(); if (SettingValueOrMemo == "SettingValue") { sbSql.AppendLine(" SELECT ISNULL(SettingValue,'') AS SettingValue FROM dbo.OTB_SYS_SystemSetting WHERE SettingItem=@SettingItem "); } else { sbSql.AppendLine(" SELECT ISNULL(Memo,'') AS Memo FROM dbo.OTB_SYS_SystemSetting WHERE SettingItem=@SettingItem "); }
SqlParameter[] parameters = { new SqlParameter("@SettingItem", SqlDbType.NVarChar,50)}; parameters[0].Value = strItem; object obj = DbHelperSQL.GetSingle(sbSql.ToString(), parameters); return obj == null ? "" : obj.ToString(); }
#endregion
/// <summary>
///
/// </summary>
/// <param name="Type"></param>
/// <param name="Flag"></param>
/// <param name="ModifyUser"></param>
/// <returns></returns>
/// 使用地方:BasePage.cs 、 QuotationBorneMaintain_Upd.aspx.cs 、 QuotationWorkMaintain_Upd.aspx.cs 、
/// ArgumentClassMaintain_Upd.aspx.cs 、 ModuleMaintain_Upd.aspx.cs 、 RoleMaintain_Upd.aspx.cs by Gary 2013/12/23
public int GetMaxNumberByType(string Type, string Flag, string ModifyUser) { StringBuilder sbBuilder = new StringBuilder(); //sbBuilder.AppendLine("DECLARE @Type NVARCHAR(3)");
//sbBuilder.AppendLine("DECLARE @Flag NVARCHAR(1)");
//sbBuilder.AppendLine("DECLARE @ModifyUser NVARCHAR(50)");
//sbBuilder.AppendLine("SET @Type = ''");
//sbBuilder.AppendLine("SET @Flag = 'D'");
//sbBuilder.AppendLine("SET @ModifyUser = ''");
sbBuilder.AppendLine("DECLARE @MaxNumberCatData INT"); sbBuilder.AppendLine("SET @MaxNumberCatData = 0");
sbBuilder.AppendLine("IF @Flag = 'Y' OR @Flag = 'Year'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" SELECT @MaxNumberCatData = ISNULL(CountMax,0) "); sbBuilder.AppendLine(" FROM OTB_SYS_MaxNumber"); sbBuilder.AppendLine(" WHERE [Type] = @Type AND [CountYear] = YEAR(GETDATE()) AND ([CountMonth] IS NULL OR [CountMonth]='') AND ([CountDay] IS NULL OR [CountDay]='')"); sbBuilder.AppendLine("PRINT @Type"); sbBuilder.AppendLine("PRINT @Flag"); sbBuilder.AppendLine("PRINT @MaxNumberCatData"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine("ELSE IF @Flag = 'M' OR @Flag = 'Month'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" SELECT @MaxNumberCatData = ISNULL(CountMax,0) "); sbBuilder.AppendLine(" FROM OTB_SYS_MaxNumber"); sbBuilder.AppendLine(" WHERE [Type] = @Type AND [CountYear] = YEAR(GETDATE()) AND [CountMonth] = MONTH(GETDATE()) AND ([CountDay] IS NULL OR [CountDay]='')"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine("ELSE IF @Flag = 'D' OR @Flag = 'Day'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" SELECT @MaxNumberCatData = ISNULL(CountMax,0) "); sbBuilder.AppendLine(" FROM OTB_SYS_MaxNumber"); sbBuilder.AppendLine(" WHERE [Type] = @Type AND [CountYear] = YEAR(GETDATE()) AND [CountMonth] = MONTH(GETDATE()) AND [CountDay] = DAY(GETDATE())"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine("ELSE IF @Flag = 'O' OR @Flag = 'Other'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" SELECT @MaxNumberCatData = ISNULL(CountMax,0) "); sbBuilder.AppendLine(" FROM OTB_SYS_MaxNumber"); sbBuilder.AppendLine(" WHERE [Type] = @Type AND ([CountYear] IS NULL OR [CountYear]='') AND ([CountMonth] IS NULL OR [CountMonth]='') AND ([CountDay] IS NULL OR [CountDay]='')"); sbBuilder.AppendLine(" END");
sbBuilder.AppendLine("IF @MaxNumberCatData > 0"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" IF @Flag = 'Y' OR @Flag = 'Year'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" UPDATE OTB_SYS_MaxNumber SET "); sbBuilder.AppendLine(" CountMax = ISNULL(CountMax,0) + 1,@MaxNumberCatData =@MaxNumberCatData+1,"); sbBuilder.AppendLine(" --Add By Vivian, 修改更新資料時更新最新修改人和最新修改時間的問題, 2013/05/27, Start."); sbBuilder.AppendLine(" ModifyUser = @ModifyUser,"); sbBuilder.AppendLine(" ModifyDate = GETDATE()"); sbBuilder.AppendLine(" --Add By Vivian, 修改更新資料時更新最新修改人和最新修改時間的問題, 2013/05/27, End."); sbBuilder.AppendLine(" WHERE [Type] = @Type AND [CountYear] = YEAR(GETDATE()) AND ([CountMonth] IS NULL OR [CountMonth]='') AND ([CountDay] IS NULL OR [CountDay]='')"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine(" ELSE IF @Flag = 'M' OR @Flag = 'Month'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" UPDATE OTB_SYS_MaxNumber SET "); sbBuilder.AppendLine(" CountMax = ISNULL(CountMax,0) + 1,@MaxNumberCatData =@MaxNumberCatData+1,"); sbBuilder.AppendLine(" --Add By Vivian, 修改更新資料時更新最新修改人和最新修改時間的問題, 2013/05/27, Start."); sbBuilder.AppendLine(" ModifyUser = @ModifyUser,"); sbBuilder.AppendLine(" ModifyDate = GETDATE()"); sbBuilder.AppendLine(" --Add By Vivian, 修改更新資料時更新最新修改人和最新修改時間的問題, 2013/05/27, End."); sbBuilder.AppendLine(" WHERE [Type] = @Type AND [CountYear] = YEAR(GETDATE()) AND [CountMonth] = MONTH(GETDATE()) AND ([CountDay] IS NULL OR [CountDay]='')"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine(" ELSE IF @Flag = 'D' OR @Flag = 'Day'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" UPDATE OTB_SYS_MaxNumber SET "); sbBuilder.AppendLine(" CountMax = ISNULL(CountMax,0) + 1,@MaxNumberCatData =@MaxNumberCatData+1,"); sbBuilder.AppendLine(" --Add By Vivian, 修改更新資料時更新最新修改人和最新修改時間的問題, 2013/05/27, Start."); sbBuilder.AppendLine(" ModifyUser = @ModifyUser,"); sbBuilder.AppendLine(" ModifyDate = GETDATE()"); sbBuilder.AppendLine(" --Add By Vivian, 修改更新資料時更新最新修改人和最新修改時間的問題, 2013/05/27, End."); sbBuilder.AppendLine(" WHERE [Type] = @Type AND [CountYear] = YEAR(GETDATE()) AND [CountMonth] = MONTH(GETDATE()) AND [CountDay] = DAY(GETDATE())"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine(" ELSE IF @Flag = 'O' OR @Flag = 'Other'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" UPDATE OTB_SYS_MaxNumber SET "); sbBuilder.AppendLine(" CountMax = ISNULL(CountMax,0) + 1,@MaxNumberCatData =@MaxNumberCatData+1,"); sbBuilder.AppendLine(" --Add By Vivian, 修改更新資料時更新最新修改人和最新修改時間的問題, 2013/05/27, Start."); sbBuilder.AppendLine(" ModifyUser = @ModifyUser,"); sbBuilder.AppendLine(" ModifyDate = GETDATE()"); sbBuilder.AppendLine(" --Add By Vivian, 修改更新資料時更新最新修改人和最新修改時間的問題, 2013/05/27, End."); sbBuilder.AppendLine(" WHERE [Type] = @Type AND ([CountYear] IS NULL OR [CountYear]='') AND ([CountMonth] IS NULL OR [CountMonth]='') AND ([CountDay] IS NULL OR [CountDay]='')"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine("ELSE"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" SET @MaxNumberCatData = 1"); sbBuilder.AppendLine(" IF @Flag = 'Y' OR @Flag = 'Year'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" INSERT INTO OTB_SYS_MaxNumber([Type],CountYear,CountMonth,CountDay,CountMax,CreateUser,CreateDate,ModifyUser,ModifyDate)VALUES(@Type,YEAR(GETDATE()),'','',1,@ModifyUser, GETDATE(), @ModifyUser, GETDATE())"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine(" ELSE IF @Flag = 'M' OR @Flag = 'Month'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" INSERT INTO OTB_SYS_MaxNumber([Type],CountYear,CountMonth,CountDay,CountMax,CreateUser,CreateDate,ModifyUser,ModifyDate)VALUES(@Type,YEAR(GETDATE()),MONTH(GETDATE()),'',1,@ModifyUser, GETDATE(), @ModifyUser, GETDATE())"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine(" ELSE IF @Flag = 'D' OR @Flag = 'Day'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" INSERT INTO OTB_SYS_MaxNumber([Type],CountYear,CountMonth,CountDay,CountMax,CreateUser,CreateDate,ModifyUser,ModifyDate)VALUES(@Type,YEAR(GETDATE()),MONTH(GETDATE()),DAY(GETDATE()),1,@ModifyUser, GETDATE(), @ModifyUser, GETDATE())"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine(" ELSE IF @Flag = 'O' OR @Flag = 'Other'"); sbBuilder.AppendLine(" BEGIN"); sbBuilder.AppendLine(" INSERT INTO OTB_SYS_MaxNumber([Type],CountYear,CountMonth,CountDay,CountMax,CreateUser,CreateDate,ModifyUser,ModifyDate)VALUES(@Type,'','','',1,@ModifyUser, GETDATE(), @ModifyUser, GETDATE())"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine(" END"); sbBuilder.AppendLine(" SELECT @MaxNumberCatData");
SqlParameter[] parameters = { new SqlParameter("@Type", SqlDbType.VarChar,50) ,new SqlParameter("@Flag", SqlDbType.Char,1) ,new SqlParameter("@ModifyUser", SqlDbType.VarChar,50) }; parameters[0].Value = Type; parameters[1].Value = Flag; parameters[2].Value = ModifyUser; //return DbHelperSQL.RunProcedure("proc_Common_GetMaxNumberByType", parameters, out rowsAffected);
object obj = DbHelperSQL.GetSingle(sbBuilder.ToString(), parameters); return Convert.ToInt32(obj); }
/// <summary>
/// 依據文件來源獲取文件列表
/// </summary>
/// <param name="SourceRelationID"></param>
/// <returns></returns>
/// 使用地方:AsyncFileUpload.ascx.cs 、 AsyncFileUploadClass.aspx.cs 、 AsyncFileUploadPrtOrPub.aspx.cs 、
/// AsyncFileUpView.aspx.cs 、 UploadFileView.aspx.cs 、 UploadHandler.aspx.cs by Gary 2013/12/23
public DataSet GetFileListBySourceRelationID(string SourceRelationID) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" GUID,SourceRelationID,FileName,FilePath,FileSize "); strSql.Append(" FROM dbo.OTB_SYS_Document "); strSql.Append(" WHERE SourceRelationID= @SourceRelationID OR GUID= @SourceRelationID "); SqlParameter[] parameters = { new SqlParameter("@SourceRelationID", SqlDbType.VarChar,36)}; parameters[0].Value = SourceRelationID; return DbHelperSQL.Query(strSql.ToString(), parameters);
}
/// <summary>
/// 獲得資料列表
/// </summary>
/// 使用地方:ucExportEquList.ascx.cs by Gary 2013/12/23
public DataSet GetList(int StartRecordIndex, int EndRecordIndex, string CustomerClass, string CustomerID, string CustomerName, string ContectName, string TEL, string TaxNumber, string Status, string SortExpression) { SqlParameter[] parameters = { new SqlParameter("@StartRecordIndex", SqlDbType.Int) ,new SqlParameter("@EndRecordIndex", SqlDbType.Int) ,new SqlParameter("@CustomerClass", SqlDbType.NVarChar,20) ,new SqlParameter("@CustomerID", SqlDbType.VarChar,36) ,new SqlParameter("@CustomerName", SqlDbType.NVarChar,100) ,new SqlParameter("@ContectName", SqlDbType.NVarChar,100) ,new SqlParameter("@TEL", SqlDbType.NVarChar,50) ,new SqlParameter("@TaxNumber", SqlDbType.VarChar,20) ,new SqlParameter("@Status", SqlDbType.Char,1) ,new SqlParameter("@SortExpression", SqlDbType.NVarChar,500) }; parameters[0].Value = StartRecordIndex; parameters[1].Value = EndRecordIndex; parameters[2].Value = CustomerClass; parameters[3].Value = CustomerID; parameters[4].Value = "%" + CustomerName + "%"; parameters[5].Value = "%" + ContectName + "%"; parameters[6].Value = "%" + TEL + "%"; parameters[7].Value = TaxNumber; parameters[8].Value = Status; parameters[9].Value = SortExpression; return DbHelperSQL.RunProcedure("OSP_Common_GetCustomerListByCustomerID", parameters, "ds"); }
/// <summary>
/// 更新排序欄位
/// </summary>
/// <param name="OldOrderByValue">舊值</param>
/// <param name="NewOrderByValue">新值</param>
/// <param name="FeildName">欄位名稱</param>
/// <param name="MDFUserFeild">修改的欄位名稱</param>
/// <param name="ModifyUser">修改的欄位名稱的值</param>
/// <param name="MDFDateFeild">修改的欄位名稱</param>
/// <param name="TableName">表名稱</param>
/// <param name="Where1">條件1</param>
/// <param name="Where2">條件2</param>
/// <returns></returns>
public bool UpdateOrderByValue(string OldOrderByValue, string NewOrderByValue, string FeildName, string MDFUserFeild, string ModifyUser, string MDFDateFeild, string TableName, string Where1, string Where2) { List<CommandInfo> lstCommandInfo = new List<CommandInfo>(); //定義事物執行的所有SQL
StringBuilder strSql = new StringBuilder(); strSql.Append(" DECLARE @strSQL NVARCHAR(1000)"); strSql.Append(" BEGIN"); strSql.Append(" PRINT @NewOrderByValue"); strSql.Append(" PRINT @OldOrderByValue"); strSql.Append(" set @strSQL=N'UPDATE '+ @TableName+'"); strSql.Append(" SET '+@FeildName+' = 88888 "); strSql.Append(" WHERE '+@FeildName+' = '+@OldOrderByValue "); strSql.Append(" IF @Where <>''"); strSql.Append(" SET @strSQL =@strSQL + ' AND ' + @Where"); strSql.Append(" print @strSQL"); strSql.Append(" exec sp_executesql @strSQL"); strSql.Append(" "); strSql.Append(" IF CONVERT(INT, @NewOrderByValue) > CONVERT(INT,@OldOrderByValue)"); strSql.Append(" BEGIN");//由小變大，OLD<-1<=NEW
strSql.Append(" SET @strSQL=N'UPDATE '+@TableName+'"); strSql.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) - 1 "); strSql.Append(" WHERE '+@FeildName+' >= '+@OldOrderByValue + ' AND '+@FeildName+'<='+@NewOrderByValue+' '"); strSql.Append(" IF @Where <>''"); strSql.Append(" SET @strSQL =@strSQL + ' AND ' + @Where"); strSql.Append(" print @strSQL"); strSql.Append(" exec sp_executesql @strSQL"); strSql.Append(" END"); strSql.Append(" ELSE"); strSql.Append(" BEGIN");//由大變小，@NewOrderByValue<=+1<@OldOrderByValue
strSql.Append(" SET @strSQL=N'UPDATE '+@TableName+'"); strSql.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) + 1 "); strSql.Append(" WHERE '+@FeildName+' <= '+@OldOrderByValue + ' AND '+@FeildName+'>='+@NewOrderByValue+' '"); strSql.Append(" IF @Where <>''"); strSql.Append(" SET @strSQL =@strSQL + ' AND ' + @Where"); strSql.Append(" print @strSQL"); strSql.Append(" exec sp_executesql @strSQL"); strSql.Append(" END"); strSql.Append(" set @strSQL=N'UPDATE '+ @TableName+'"); strSql.Append(" SET '+@FeildName+' = '+@NewOrderByValue+"); strSql.Append(" ' WHERE '+@FeildName+' = 88888' "); strSql.Append(" IF @Where <>''"); strSql.Append(" SET @strSQL =@strSQL + ' AND ' + @Where"); strSql.Append(" print @strSQL"); strSql.Append(" exec sp_executesql @strSQL"); strSql.Append(" END");
SqlParameter[] parametersChangeOrder = { new SqlParameter("@OldOrderByValue", SqlDbType.NVarChar,10) ,new SqlParameter("@NewOrderByValue", SqlDbType.NVarChar,10) ,new SqlParameter("@FeildName", SqlDbType.NVarChar,50) ,new SqlParameter("@TableName", SqlDbType.NVarChar,50) ,new SqlParameter("@Where", SqlDbType.NVarChar,500) }; parametersChangeOrder[0].Value = OldOrderByValue; parametersChangeOrder[1].Value = NewOrderByValue; parametersChangeOrder[2].Value = FeildName; parametersChangeOrder[3].Value = TableName; parametersChangeOrder[4].Value = Where1;
CommandInfo o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表修改
o_CommandInfo.CommandText = strSql.ToString(); o_CommandInfo.Parameters = parametersChangeOrder; lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
strSql.Clear(); strSql.Append(" DECLARE @strSQL NVARCHAR(1000)"); strSql.Append(" BEGIN"); strSql.Append(" PRINT @NewOrderByValue"); strSql.Append(" SET @strSQL=N'UPDATE '+@TableName+' "); strSql.Append(" SET ' +@MDFUserFeild+' ='''+@ModifyUser+''','+@MDFDateFeild+' =GETDATE() WHERE '+@Where"); strSql.Append(" PRINT @strSQL "); strSql.Append(" exec sp_executesql @strSQL"); strSql.Append(" END"); SqlParameter[] parameters = { new SqlParameter("@NewOrderByValue", SqlDbType.NVarChar,10) ,new SqlParameter("@FeildName", SqlDbType.NVarChar,50) ,new SqlParameter("@MDFUserFeild", SqlDbType.NVarChar,50) ,new SqlParameter("@ModifyUser", SqlDbType.VarChar,50) ,new SqlParameter("@MDFDateFeild", SqlDbType.NVarChar,50) ,new SqlParameter("@TableName", SqlDbType.NVarChar,50) ,new SqlParameter("@Where", SqlDbType.NVarChar,500) }; parameters[0].Value = NewOrderByValue; parameters[1].Value = FeildName; parameters[2].Value = MDFUserFeild; parameters[3].Value = ModifyUser; parameters[4].Value = MDFDateFeild; parameters[5].Value = TableName; parameters[6].Value = Where2;
o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表修改
o_CommandInfo.CommandText = strSql.ToString(); o_CommandInfo.Parameters = parameters; lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0) { return true; } else { return false; }
//List<CommandInfo> lstCommandInfo = new List<CommandInfo>(); //定義事物執行的所有SQL
//SqlParameter[] parametersChangeOrder = {
// new SqlParameter("@OldOrderByValue", SqlDbType.NVarChar,10)
// ,new SqlParameter("@NewOrderByValue", SqlDbType.NVarChar,10)
// ,new SqlParameter("@FeildName", SqlDbType.NVarChar,50)
// ,new SqlParameter("@TableName", SqlDbType.NVarChar,50)
// ,new SqlParameter("@Where", SqlDbType.NVarChar,500)
// };
//parametersChangeOrder[0].Value = OldOrderByValue;
//parametersChangeOrder[1].Value = NewOrderByValue;
//parametersChangeOrder[2].Value = FeildName;
//parametersChangeOrder[3].Value = TableName;
//parametersChangeOrder[4].Value = Where1;
//CommandInfo o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表修改
//o_CommandInfo.CommandText = "OSP_Common_UpdateOrderByValue";
//o_CommandInfo.Parameters = parametersChangeOrder;
//lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
//SqlParameter[] parameters = {
// new SqlParameter("@NewOrderByValue", SqlDbType.NVarChar,10)
// ,new SqlParameter("@FeildName", SqlDbType.NVarChar,50)
// ,new SqlParameter("@MDFUserFeild", SqlDbType.NVarChar,50)
// ,new SqlParameter("@ModifyUser", SqlDbType.VarChar,50)
// ,new SqlParameter("@MDFDateFeild", SqlDbType.NVarChar,50)
// ,new SqlParameter("@TableName", SqlDbType.NVarChar,50)
// ,new SqlParameter("@Where", SqlDbType.NVarChar,500)
// };
//parameters[0].Value = NewOrderByValue;
//parameters[1].Value = FeildName;
//parameters[2].Value = MDFUserFeild;
//parameters[3].Value = ModifyUser;
//parameters[4].Value = MDFDateFeild;
//parameters[5].Value = TableName;
//parameters[6].Value = Where2;
//o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表修改
//o_CommandInfo.CommandText = "OSP_Common_UpdateCurrentRowOrderByValue";
//o_CommandInfo.Parameters = parameters;
//lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
//if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 獲得資料列表
/// </summary>
public DataSet GetTrWord(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT * FROM TRWORD "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return DbHelperSQL.Query(strSql.ToString()); }
#region 共用型更改MemberID
/// <summary>
/// 共用型更改MemberID
/// Create by Gary 2014/08/13
/// </summary>
/// <param name="strTableName">資料表名稱</param>
/// <param name="strMemberField">MemberField欄位名稱</param>
/// <param name="strMemberID">新的MemberID</param>
/// <param name="strModifyField">更改人欄位</param>
/// <param name="strUserID">更改人ID</param>
/// <param name="strModifyDateField">更改時間欄位</param>
/// <param name="strWhere">Where條件，請務必給正確的條件，以免更新錯誤</param>
/// <returns>是否更新成功</returns>
/// 使用地方: ChangeMember_Pop.aspx.cs by Gary 2014/08/13
public bool UpdateMemberID(string strTableName, string strMemberField, string strMemberID, string strModifyField, string strUserID, string strModifyDateField, string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append(" update " + strTableName); strSql.Append(" set " + strMemberField + " =@MemberID, "); strSql.Append(strModifyField + " =@UserID, "); strSql.Append(strModifyDateField + " =GetDate()"); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } else { strSql.Append(" where 1=0");//避免參數沒送正確時，導致全部資料都被引響
} SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.VarChar,18), new SqlParameter("@UserID", SqlDbType.VarChar,18) }; parameters[0].Value = strMemberID; parameters[1].Value = strUserID; int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } } #endregion
#region 可刪除的資料
/// <summary>
/// 通過專案編號獲取該專案中所有合約的附件資料列表
/// </summary>
/// 使用地方:UpLoadCTListFileView.ascx.cs by Gary 2013/12/23
public DataSet GetContractListFileListByProjectID(string ProjectID) { SqlParameter[] parameters = { new SqlParameter("@ProjectID", SqlDbType.VarChar,36)}; parameters[0].Value = ProjectID; return DbHelperSQL.RunProcedure("OSP_TB_Common_GetContractListFileListByProjectID", parameters, "FileList"); }
#endregion
}}
|
