using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using OT.IDAL;
using DBUtility;
using System.Collections.Generic;//Please add references
namespace OT.SQLServerDAL
{
///
/// 資料訪問類:OTB_SYS_ArgumentClass
///
public partial class OTB_SYS_ArgumentClass : IOTB_SYS_ArgumentClass
{
public OTB_SYS_ArgumentClass()
{ }
#region Method
///
/// 是否存在該記錄
///
public bool Exists(string ArgumentClassID)
{
int rowsAffected;
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from OTB_SYS_ArgumentClass");
strSql.Append(" where ArgumentClassID=@ArgumentClassID ");
SqlParameter[] parameters = {
new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10) };
parameters[0].Value = ArgumentClassID;
return DbHelperSQL.Exists(strSql.ToString(), parameters);
}
///
/// 增加一條資料
///
public bool Add(OT.Model.OTB_SYS_ArgumentClass model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" INSERT INTO [OTB_SYS_ArgumentClass]( ");
strSql.Append(" [ArgumentClassID],[ArgumentClassName],[MaxNumberClass],[MaxNumberCatData],[OrderByValue],[Effective],[DelStatus],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate] ");
strSql.Append(" )VALUES( ");
strSql.Append(" @ArgumentClassID,@ArgumentClassName,@MaxNumberClass,@MaxNumberCatData,@OrderByValue,@Effective,@DelStatus,@Memo,@CreateUser,getdate(),@CreateUser,getdate() ) ");
SqlParameter[] parameters = {
new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10),
new SqlParameter("@ArgumentClassName", SqlDbType.NVarChar,200),
new SqlParameter("@OrderByValue", SqlDbType.Int,4),
new SqlParameter("@Effective", SqlDbType.Char,1),
new SqlParameter("@Memo", SqlDbType.NVarChar),
new SqlParameter("@CreateUser", SqlDbType.VarChar,50),
new SqlParameter("@CreateDate", SqlDbType.DateTime),
new SqlParameter("@ModifyUser", SqlDbType.VarChar,50),
new SqlParameter("@ModifyDate", SqlDbType.DateTime),
new SqlParameter("@MaxNumberClass", SqlDbType.VarChar,50),
new SqlParameter("@MaxNumberCatData", SqlDbType.VarChar,50),
new SqlParameter("@DelStatus", SqlDbType.Char,1)};
parameters[0].Value = model.ArgumentClassID;
parameters[1].Value = model.ArgumentClassName;
parameters[2].Value = model.OrderByValue;
parameters[3].Value = model.Effective;
parameters[4].Value = model.Memo;
parameters[5].Value = model.CreateUser;
parameters[6].Value = model.CreateDate;
parameters[7].Value = model.ModifyUser;
parameters[8].Value = model.ModifyDate;
parameters[9].Value = "";
parameters[10].Value = "";
parameters[11].Value = "N";
List lstCommandInfo = new List(); //定義事物執行的所有SQL
StringBuilder strSql1 = new StringBuilder();
strSql1.Append(" DECLARE @strSQL NVARCHAR(1000) ");
strSql1.Append(" BEGIN ");
strSql1.Append(" PRINT @NewOrderByValue ");
strSql1.Append(" PRINT @OldOrderByValue ");
strSql1.Append(" IF CONVERT(INT, @NewOrderByValue) > CONVERT(INT,@OldOrderByValue) ");
strSql1.Append(" BEGIN ");
strSql1.Append(" SET @strSQL=N'UPDATE '+@TableName+' ");
strSql1.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) - 1 ");
strSql1.Append(" WHERE '+@FeildName+' >= '+@OldOrderByValue + ' AND '+@FeildName+'<='+@NewOrderByValue+' ' ");
strSql1.Append(" IF @Where <>'' ");
strSql1.Append(" SET @strSQL =@strSQL + ' AND ' + @Where ");
strSql1.Append(" print @strSQL ");
strSql1.Append(" exec sp_executesql @strSQL ");
strSql1.Append(" END ");
strSql1.Append(" ELSE ");
strSql1.Append(" BEGIN ");
strSql1.Append(" SET @strSQL=N'UPDATE '+@TableName+' ");
strSql1.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) + 1 ");
strSql1.Append(" WHERE '+@FeildName+' <= '+@OldOrderByValue + ' AND '+@FeildName+'>='+@NewOrderByValue+' ' ");
strSql1.Append(" IF @Where <>'' ");
strSql1.Append(" SET @strSQL =@strSQL + ' AND ' + @Where ");
strSql1.Append(" print @strSQL ");
strSql1.Append(" exec sp_executesql @strSQL ");
strSql1.Append(" END ");
strSql1.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 = model.OldOrderByValue;
parametersChangeOrder[1].Value = model.OrderByValue;
parametersChangeOrder[2].Value = "OrderByValue";
parametersChangeOrder[3].Value = "OTB_SYS_ArgumentClass";
parametersChangeOrder[4].Value = "DelStatus='N' ";
CommandInfo o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表修改
//o_CommandInfo.CommandText = "OSP_Common_UpdateOrderByValue";
o_CommandInfo.CommandText = strSql1.ToString();
o_CommandInfo.Parameters = parametersChangeOrder;
lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表新增，雖然參數名稱相同，但是經過new以後就是新的對象
//o_CommandInfo.CommandText = "OSP_OTB_SYS_ArgumentClass_ADD";
o_CommandInfo.CommandText = strSql.ToString();
o_CommandInfo.Parameters = parameters;
lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0)
{
return true;
}
else
{
return false;
}
}
///
/// 更新一條資料
///
public bool Update(OT.Model.OTB_SYS_ArgumentClass model)
{
int rowsAffected = 0;
StringBuilder strSql = new StringBuilder();
strSql.Append(" UPDATE [OTB_SYS_ArgumentClass] SET ");
strSql.Append(" [ArgumentClassName] = @ArgumentClassName,[OrderByValue] = @OrderByValue,[Effective] = @Effective,[Memo] = @Memo,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE() ");
strSql.Append(" WHERE ArgumentClassID=@ArgumentClassID ");
SqlParameter[] parameters = {
new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10),
new SqlParameter("@ArgumentClassName", SqlDbType.NVarChar,200),
new SqlParameter("@OrderByValue", SqlDbType.Int,4),
new SqlParameter("@Effective", SqlDbType.Char,1),
new SqlParameter("@Memo", SqlDbType.NVarChar),
new SqlParameter("@CreateUser", SqlDbType.VarChar,50),
new SqlParameter("@CreateDate", SqlDbType.DateTime),
new SqlParameter("@ModifyUser", SqlDbType.VarChar,50),
new SqlParameter("@ModifyDate", SqlDbType.DateTime),};
parameters[0].Value = model.ArgumentClassID;
parameters[1].Value = model.ArgumentClassName;
parameters[2].Value = model.OrderByValue;
parameters[3].Value = model.Effective;
parameters[4].Value = model.Memo;
parameters[5].Value = model.CreateUser;
parameters[6].Value = model.CreateDate;
parameters[7].Value = model.ModifyUser;
parameters[8].Value = model.ModifyDate;
List lstCommandInfo = new List(); //定義事物執行的所有SQL
StringBuilder strSql2 = new StringBuilder();
strSql2.Append(" DECLARE @strSQL NVARCHAR(1000) ");
strSql2.Append(" IF CONVERT(INT, @NewOrderByValue) > CONVERT(INT,@OldOrderByValue) ");
strSql2.Append(" BEGIN "); //由小變大，OLD<-1<=NEW
strSql2.Append(" SET @strSQL=N'UPDATE '+@TableName+' ");
strSql2.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) - 1 ");
strSql2.Append(" WHERE '+@FeildName+' >= '+@OldOrderByValue + ' AND '+@FeildName+'<='+@NewOrderByValue+' ' ");
strSql2.Append(" IF @Where <>'' ");
strSql2.Append(" SET @strSQL =@strSQL + ' AND ' + @Where ");
strSql2.Append(" print @strSQL ");
strSql2.Append(" exec sp_executesql @strSQL ");
strSql2.Append(" END ");
strSql2.Append(" ELSE ");
strSql2.Append(" BEGIN "); //由大變小，@NewOrderByValue<=+1<@OldOrderByValue
strSql2.Append(" SET @strSQL=N'UPDATE '+@TableName+' ");
strSql2.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) + 1 ");
strSql2.Append(" WHERE '+@FeildName+' <= '+@OldOrderByValue + ' AND '+@FeildName+'>='+@NewOrderByValue+' ' ");
strSql2.Append(" IF @Where <>'' ");
strSql2.Append(" SET @strSQL =@strSQL + ' AND ' + @Where ");
strSql2.Append(" print @strSQL ");
strSql2.Append(" exec sp_executesql @strSQL ");
strSql2.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 = model.OldOrderByValue;
parametersChangeOrder[1].Value = model.OrderByValue;
parametersChangeOrder[2].Value = "OrderByValue";
parametersChangeOrder[3].Value = "OTB_SYS_ArgumentClass";
parametersChangeOrder[4].Value = "DelStatus='N' ";
CommandInfo o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表修改
//o_CommandInfo.CommandText = "OSP_Common_UpdateOrderByValue";
o_CommandInfo.CommandText = strSql2.ToString();
o_CommandInfo.Parameters = parametersChangeOrder;
lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表新增，雖然參數名稱相同，但是經過new以後就是新的對象
//o_CommandInfo.CommandText = "OSP_OTB_SYS_ArgumentClass_Update";
o_CommandInfo.CommandText = strSql.ToString();
o_CommandInfo.Parameters = parameters;
lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0)
{
return true;
}
else
{
return false;
}
}
///
/// 刪除一條資料
///
public int Delete(string strArgumentClassID, string OrderByValue, string OrderByValueCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" DECLARE @rowsAff INT ");
strSql.Append(" DECLARE @TempID INT ");
strSql.Append(" DECLARE @OrderByValue INT ");
strSql.Append(" SET @rowsAff=0 ");
strSql.Append(" SELECT @OrderByValue = OrderByValue ");
strSql.Append(" FROM [OTB_SYS_ArgumentClass] ");
strSql.Append(" WHERE ArgumentClassID = '0001' ");
strSql.Append(" SELECT @TempID = COUNT(1) ");
strSql.Append(" FROM [OTB_SYS_Arguments] ");
strSql.Append(" WHERE ArgumentClassID = @ArgumentClassID ");
strSql.Append(" PRINT @TempID ");
strSql.Append(" IF @TempID > 0 ");
strSql.Append(" BEGIN ");
strSql.Append(" SELECT -1 ");
strSql.Append(" END ");
strSql.Append(" ELSE ");
strSql.Append(" BEGIN ");
strSql.Append(" UPDATE [OTB_SYS_ArgumentClass] ");
strSql.Append(" SET [DelStatus] = 'Y' ");
strSql.Append(" WHERE ArgumentClassID = @ArgumentClassID ");
strSql.Append(" SET @rowsAff+=@@rowcount ");
strSql.Append(" PRINT @rowsAff ");
strSql.Append(" UPDATE [OTB_SYS_Arguments] ");
strSql.Append(" SET [DelStatus] = 'Y' ");
strSql.Append(" WHERE ArgumentClassID = @ArgumentClassID ");
strSql.Append(" SET @rowsAff+=@@rowcount ");
strSql.Append(" PRINT @rowsAff ");
strSql.Append(" UPDATE [OTB_SYS_ArgumentClass] ");
strSql.Append(" SET OrderByValue = ISNULL(OrderByValue, 1) - 1 ");
strSql.Append(" WHERE OrderByValue > @OrderByValue ");
strSql.Append(" SET @rowsAff+=@@rowcount ");
strSql.Append(" PRINT @rowsAff ");
strSql.Append(" SELECT @rowsAff ");
strSql.Append(" END ");
SqlParameter[] parameters = {
new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10)};
parameters[0].Value = strArgumentClassID;
int rowsAffected = 0;
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
rowsAffected = int.Parse(ds.Tables[0].Rows[0][0].ToString());
}
else
{
rowsAffected = 0;
}
return rowsAffected;
}
///
/// 批量刪除資料
///
public bool DeleteList(string ArgumentClassIDlist)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from SYS_ArgumentClass ");
strSql.Append(" where ArgumentClassID in (" + ArgumentClassIDlist + ") ");
int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
///
/// 得到一個對象實體
///
public OT.Model.OTB_SYS_ArgumentClass GetModel(string ArgumentClassID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ArgumentClassID,ArgumentClassName,MaxNumberClass,MaxNumberCatData,OrderByValue,Effective,DelStatus,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate from OTB_SYS_ArgumentClass ");
strSql.Append(" where ArgumentClassID=@ArgumentClassID ");
SqlParameter[] parameters = {
new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10) };
parameters[0].Value = ArgumentClassID;
OT.Model.OTB_SYS_ArgumentClass model = new OT.Model.OTB_SYS_ArgumentClass();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return DataRowToModel(ds.Tables[0].Rows[0]);
}
else
{
return null;
}
}
///
/// 得到一個對象實體
///
public OT.Model.OTB_SYS_ArgumentClass DataRowToModel(DataRow row)
{
OT.Model.OTB_SYS_ArgumentClass model = new OT.Model.OTB_SYS_ArgumentClass();
if (row != null)
{
if (row["ArgumentClassID"] != null)
{
model.ArgumentClassID = row["ArgumentClassID"].ToString();
}
if (row["ArgumentClassName"] != null)
{
model.ArgumentClassName = row["ArgumentClassName"].ToString();
}
if (row["OrderByValue"] != null && row["OrderByValue"].ToString() != "")
{
model.OrderByValue = int.Parse(row["OrderByValue"].ToString());
}
if (row["Effective"] != null)
{
model.Effective = row["Effective"].ToString();
}
if (row["Memo"] != null)
{
model.Memo = row["Memo"].ToString();
}
if (row["CreateUser"] != null)
{
model.CreateUser = row["CreateUser"].ToString();
}
if (row["CreateDate"] != null && row["CreateDate"].ToString() != "")
{
model.CreateDate = DateTime.Parse(row["CreateDate"].ToString());
}
if (row["ModifyUser"] != null)
{
model.ModifyUser = row["ModifyUser"].ToString();
}
if (row["ModifyDate"] != null && row["ModifyDate"].ToString() != "")
{
model.ModifyDate = DateTime.Parse(row["ModifyDate"].ToString());
}
}
return model;
}
///
/// 獲得資料列表
///
public DataSet GetList(int StartRecordIndex, int EndRecordIndex, string ArgumentClassID, string ArgumentClassName, string Effective, string SortExpression)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT RowId,ArgumentClassID,ArgumentClassName,Effective,OrderByValue ");
strSql.Append(" ,(SELECT COUNT(0) FROM [OTB_SYS_ArgumentClass] WHERE [DelStatus] = 'N') AS ClassCount ");
strSql.Append(" FROM ");
strSql.Append(" ( ");
strSql.Append(" SELECT ");
strSql.Append(" CASE @SortExpression WHEN N'ArgumentClassID' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY ArgumentClassID)) ");
strSql.Append(" WHEN N'ArgumentClassID DESC' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY ArgumentClassID DESC)) ");
strSql.Append(" WHEN N'ArgumentClassName' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY ArgumentClassName)) ");
strSql.Append(" WHEN N'ArgumentClassName DESC' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY ArgumentClassName DESC)) ");
strSql.Append(" WHEN N'Effective' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY Effective)) ");
strSql.Append(" WHEN N'Effective DESC' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY Effective DESC)) ");
strSql.Append(" WHEN N'OrderByValue' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY OrderByValue)) ");
strSql.Append(" WHEN N'OrderByValue DESC' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY OrderByValue DESC)) ");
strSql.Append(" ELSE ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY OrderByValue)) ");
strSql.Append(" END AS RowId,ArgumentClassID,ArgumentClassName,Effective,OrderByValue ");
strSql.Append(" FROM [OTB_SYS_ArgumentClass] ");
strSql.Append(" WHERE (ArgumentClassID LIKE @ArgumentClassID OR @ArgumentClassID IS NULL OR @ArgumentClassID='' OR @ArgumentClassID = '%%') ");
strSql.Append(" AND (ArgumentClassName LIKE @ArgumentClassName OR @ArgumentClassName IS NULL OR @ArgumentClassName='' OR @ArgumentClassName='%%') ");
strSql.Append(" AND (Effective = @Effective OR @Effective IS NULL OR @Effective='') ");
strSql.Append(" AND [DelStatus] = 'N' ");
// strSql.Append(" AND ArgumentClassID <> '99999'"); //modfiy by Alina 20150728 可維護Text值，新增按鈕的值
strSql.Append(" ) AS AA ");
strSql.Append(" WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex ");
strSql.Append(" ORDER BY RowId ");
SqlParameter[] parameters = {
new SqlParameter("@StartRecordIndex", SqlDbType.Int)
,new SqlParameter("@EndRecordIndex", SqlDbType.Int)
,new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10)
,new SqlParameter("@ArgumentClassName", SqlDbType.NVarChar,200)
,new SqlParameter("@Effective", SqlDbType.Char,1)
,new SqlParameter("@SortExpression", SqlDbType.NVarChar,500)
};
parameters[0].Value = StartRecordIndex;
parameters[1].Value = EndRecordIndex;
parameters[2].Value = "%" + ArgumentClassID + "%";
parameters[3].Value = "%" + ArgumentClassName + "%";
parameters[4].Value = Effective;
parameters[5].Value = SortExpression;
return DbHelperSQL.Query(strSql.ToString(), parameters);
}
///
/// 獲得資料總筆數
///
public int GetListCount(string ArgumentClassID, string ArgumentClassName, string Effective)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT ");
strSql.Append(" COUNT(0) ");
strSql.Append(" FROM [OTB_SYS_ArgumentClass] ");
strSql.Append(" WHERE (ArgumentClassID LIKE @ArgumentClassID OR @ArgumentClassID IS NULL OR @ArgumentClassID='' OR @ArgumentClassID = '%%') ");
strSql.Append(" AND (ArgumentClassName LIKE @ArgumentClassName OR @ArgumentClassName IS NULL OR @ArgumentClassName='' OR @ArgumentClassName='%%' ) ");
strSql.Append(" AND (Effective = @Effective OR @Effective IS NULL OR @Effective='') ");
strSql.Append(" AND [DelStatus] = 'N' ");
SqlParameter[] parameters = {
new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10)
,new SqlParameter("@ArgumentClassName", SqlDbType.NVarChar,200)
,new SqlParameter("@Effective", SqlDbType.Char,1)
};
parameters[0].Value = "%" + ArgumentClassID + "%";
parameters[1].Value = "%" + ArgumentClassName + "%";
parameters[2].Value = Effective;
return (int)DbHelperSQL.GetSingle(strSql.ToString(), parameters);
}
#endregion Method
}
}