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 下拉選單
///
/// 得到一個模組實體
///
/// 使用地方: 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");
}
///
/// 得到一個程式清單實體
///
///
///
///
/// 使用地方: 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);
}
///
/// 取得所有使用者清單
/// modify by Dean 2013/08/07
///
///
///
/// 使用地方: 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);
}
///
///
/// 取得目前系統內所有沒有被館別使用的大分類
///
///
///
/// 使用地方: 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(''+replace(Classifications, '|', '')+'' 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 取流水號
///
/// 獲取大類別編號的最大數字
///
/// 大類別代號
/// 該類別的最大編號
/// 使用地方: 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);
}
///
/// 獲取小類別編號的最大數字
///
/// 大類別代號
/// 小類別代號
///
/// 使用地方: 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
///
/// 依據角色ID取得程式列表信息
///
/// 角色ID
/// 程式列表信息
/// 使用地方: 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
///
/// 依據參數類別ID取得參數值
///
/// 參數類別ID
/// 參數值列表信息
/// 使用地方: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 內部系統
///
/// 獲取系統參數
///
/// 參數名稱
/// 參數取向值
///
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
///
///
///
///
///
///
///
/// 使用地方: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);
}
///
/// 依據文件來源獲取文件列表
///
///
///
/// 使用地方: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);
}
///
/// 獲得資料列表
///
/// 使用地方: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");
}
///
/// 更新排序欄位
///
/// 舊值
/// 新值
/// 欄位名稱
/// 修改的欄位名稱
/// 修改的欄位名稱的值
/// 修改的欄位名稱
/// 表名稱
/// 條件1
/// 條件2
///
public bool UpdateOrderByValue(string OldOrderByValue, string NewOrderByValue, string FeildName, string MDFUserFeild, string ModifyUser, string MDFDateFeild, string TableName, string Where1, string Where2)
{
List lstCommandInfo = new List(); //定義事物執行的所有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 lstCommandInfo = new List(); //定義事物執行的所有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;
//}
}
///
/// 獲得資料列表
///
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
///
/// 共用型更改MemberID
/// Create by Gary 2014/08/13
///
/// 資料表名稱
/// MemberField欄位名稱
/// 新的MemberID
/// 更改人欄位
/// 更改人ID
/// 更改時間欄位
/// Where條件，請務必給正確的條件，以免更新錯誤
/// 是否更新成功
/// 使用地方: 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 可刪除的資料
///
/// 通過專案編號獲取該專案中所有合約的附件資料列表
///
/// 使用地方: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
}
}