You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
874 lines
50 KiB
874 lines
50 KiB
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
|
|
|
|
}
|
|
}
|