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_Jobtitle
///
public partial class OTB_SYS_Jobtitle : IOTB_SYS_Jobtitle
{
public OTB_SYS_Jobtitle()
{ }
#region Method
///
/// 是否存在該記錄
///
public bool Exists(string JobtitleID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from OTB_SYS_Jobtitle");
strSql.Append(" where JobtitleID=@JobtitleID ");
SqlParameter[] parameters = {
new SqlParameter("@JobtitleID", SqlDbType.VarChar,10) };
parameters[0].Value = JobtitleID;
return DbHelperSQL.Exists(strSql.ToString(), parameters);
}
///
/// 是否存在該記錄在人員表中
///
public bool ExistsByMember(string JobtitleID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from OTB_SYS_Members");
strSql.Append(" where JobtitleID=@JobtitleID ");
SqlParameter[] parameters = {
new SqlParameter("@JobtitleID", SqlDbType.VarChar,10) };
parameters[0].Value = JobtitleID;
return DbHelperSQL.Exists(strSql.ToString(), parameters);
}
///
/// 增加一條資料
///
public bool Add(OT.Model.OTB_SYS_Jobtitle model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into OTB_SYS_Jobtitle(");
strSql.Append("JobtitleID,JobtitleName,DepartID,Isleader,Effective,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate)");
strSql.Append(" values (");
strSql.Append("@JobtitleID,@JobtitleName,@DepartID,@Isleader,@Effective,@Memo,@CreateUser,getdate(),@CreateUser,getdate())");
SqlParameter[] parameters = {
new SqlParameter("@JobtitleID", SqlDbType.VarChar,10),
new SqlParameter("@JobtitleName", SqlDbType.NVarChar,10),
new SqlParameter("@DepartID", SqlDbType.VarChar,10),
new SqlParameter("@Isleader", SqlDbType.Char,1),
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.JobtitleID;
parameters[1].Value = model.JobtitleName;
parameters[2].Value = model.DepartID;
parameters[3].Value = model.Isleader;
parameters[4].Value = model.Effective;
parameters[5].Value = model.Memo;
parameters[6].Value = model.CreateUser;
parameters[7].Value = model.CreateDate;
parameters[8].Value = model.ModifyUser;
parameters[9].Value = model.ModifyDate;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
///
/// 更新一條資料
///
public bool Update(OT.Model.OTB_SYS_Jobtitle model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update OTB_SYS_Jobtitle set ");
strSql.Append("JobtitleName=@JobtitleName,");
strSql.Append("DepartID=@DepartID,");
strSql.Append("Isleader=@Isleader,");
strSql.Append("Effective=@Effective,");
strSql.Append("Memo=@Memo,");
strSql.Append("ModifyUser=@ModifyUser,");
strSql.Append("ModifyDate=getdate()");
strSql.Append(" where JobtitleID=@JobtitleID ");
SqlParameter[] parameters = {
new SqlParameter("@JobtitleName", SqlDbType.NVarChar,10),
new SqlParameter("@DepartID", SqlDbType.VarChar,10),
new SqlParameter("@Isleader", SqlDbType.Char,1),
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("@JobtitleID", SqlDbType.VarChar,10)};
parameters[0].Value = model.JobtitleName;
parameters[1].Value = model.DepartID;
parameters[2].Value = model.Isleader;
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 = model.JobtitleID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
///
/// 刪除一條資料
///
public bool Delete(string JobtitleID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from OTB_SYS_Jobtitle ");
strSql.Append(" where JobtitleID=@JobtitleID ");
SqlParameter[] parameters = {
new SqlParameter("@JobtitleID", SqlDbType.VarChar,10) };
parameters[0].Value = JobtitleID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
///
/// 批量刪除資料
///
public bool DeleteList(string JobtitleIDlist)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from OTB_SYS_Jobtitle ");
strSql.Append(" where JobtitleID in (" + JobtitleIDlist + ") ");
int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
///
/// 得到一個對象實體
///
public OT.Model.OTB_SYS_Jobtitle GetModel(string JobtitleID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 JobtitleID,JobtitleName,DepartID,Isleader,Effective,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate from OTB_SYS_Jobtitle ");
strSql.Append(" where JobtitleID=@JobtitleID ");
SqlParameter[] parameters = {
new SqlParameter("@JobtitleID", SqlDbType.VarChar,10) };
parameters[0].Value = JobtitleID;
OT.Model.OTB_SYS_Jobtitle model = new OT.Model.OTB_SYS_Jobtitle();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["JobtitleID"] != null && ds.Tables[0].Rows[0]["JobtitleID"].ToString() != "")
{
model.JobtitleID = ds.Tables[0].Rows[0]["JobtitleID"].ToString();
}
if (ds.Tables[0].Rows[0]["JobtitleName"] != null && ds.Tables[0].Rows[0]["JobtitleName"].ToString() != "")
{
model.JobtitleName = ds.Tables[0].Rows[0]["JobtitleName"].ToString();
}
if (ds.Tables[0].Rows[0]["DepartID"] != null && ds.Tables[0].Rows[0]["DepartID"].ToString() != "")
{
model.DepartID = ds.Tables[0].Rows[0]["DepartID"].ToString();
}
if (ds.Tables[0].Rows[0]["Isleader"] != null && ds.Tables[0].Rows[0]["Isleader"].ToString() != "")
{
model.Isleader = ds.Tables[0].Rows[0]["Isleader"].ToString();
}
if (ds.Tables[0].Rows[0]["Effective"] != null && ds.Tables[0].Rows[0]["Effective"].ToString() != "")
{
model.Effective = ds.Tables[0].Rows[0]["Effective"].ToString();
}
if (ds.Tables[0].Rows[0]["Memo"] != null && ds.Tables[0].Rows[0]["Memo"].ToString() != "")
{
model.Memo = ds.Tables[0].Rows[0]["Memo"].ToString();
}
if (ds.Tables[0].Rows[0]["CreateUser"] != null && ds.Tables[0].Rows[0]["CreateUser"].ToString() != "")
{
model.CreateUser = ds.Tables[0].Rows[0]["CreateUser"].ToString();
}
if (ds.Tables[0].Rows[0]["CreateDate"] != null && ds.Tables[0].Rows[0]["CreateDate"].ToString() != "")
{
model.CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
}
if (ds.Tables[0].Rows[0]["ModifyUser"] != null && ds.Tables[0].Rows[0]["ModifyUser"].ToString() != "")
{
model.ModifyUser = ds.Tables[0].Rows[0]["ModifyUser"].ToString();
}
if (ds.Tables[0].Rows[0]["ModifyDate"] != null && ds.Tables[0].Rows[0]["ModifyDate"].ToString() != "")
{
model.ModifyDate = DateTime.Parse(ds.Tables[0].Rows[0]["ModifyDate"].ToString());
}
return model;
}
else
{
return null;
}
}
///
/// 獲得資料列表
///
public DataSet GetList(int StartRecordIndex, int EndRecordIndex, string DepartID, string JobtitleID, string JobtitleName, string Effective, string SortExpression)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT ");
strSql.Append(" RowId ");
strSql.Append(" ,JobtitleID ");
strSql.Append(" ,JobtitleName ");
strSql.Append(" ,Effective ");
strSql.Append(" ,DepartID ");
strSql.Append(" FROM ");
strSql.Append(" ( ");
strSql.Append(" SELECT ");
strSql.Append(" CASE @SortExpression ");
strSql.Append(" WHEN N'JobtitleID' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY JobtitleID)) ");
strSql.Append(" WHEN N'JobtitleID DESC' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY JobtitleID DESC)) ");
strSql.Append(" WHEN N'DepartID' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY DepartID)) ");
strSql.Append(" WHEN N'DepartID DESC' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY DepartID DESC)) ");
strSql.Append(" WHEN N'JobtitleName' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY JobtitleName)) ");
strSql.Append(" WHEN N'JobtitleName DESC' THEN ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY JobtitleName 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(" ELSE ");
strSql.Append(" (ROW_NUMBER() OVER(Order BY ModifyDate DESC)) ");
strSql.Append(" END AS RowId ");
strSql.Append(" ,JobtitleID ");
strSql.Append(" ,JobtitleName ");
strSql.Append(" ,Effective ");
strSql.Append(" ,DepartID ");
strSql.Append(" FROM [OTB_SYS_Jobtitle] ");
strSql.Append(" WHERE(JobtitleID LIKE @JobtitleID OR @JobtitleID IS NULL OR @JobtitleID='%%') ");
strSql.Append(" AND (JobtitleName LIKE @JobtitleName OR @JobtitleName IS NULL OR JobtitleName IS NULL OR @JobtitleName='%%') ");
strSql.Append(" AND (Effective = @Effective OR @Effective IS NULL OR @Effective='') ");
strSql.Append(" ");
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("@DepartID", SqlDbType.VarChar,10)
,new SqlParameter("@JobtitleID", SqlDbType.VarChar,10)
,new SqlParameter("@JobtitleName", SqlDbType.NVarChar,50)
,new SqlParameter("@Effective", SqlDbType.Char,1)
,new SqlParameter("@SortExpression", SqlDbType.NVarChar,500)
};
parameters[0].Value = StartRecordIndex;
parameters[1].Value = EndRecordIndex;
parameters[2].Value = DepartID;
parameters[3].Value = "%" + JobtitleID + "%";
parameters[4].Value = "%" + JobtitleName + "%";
parameters[5].Value = Effective;
parameters[6].Value = SortExpression;
return DbHelperSQL.Query(strSql.ToString(), parameters);
}
///
/// 獲得資料總筆數
///
public int GetListCount(string DepartID, string JobtitleID, string JobtitleName, string Effective)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT ");
strSql.Append(" COUNT(0) ");
strSql.Append(" FROM [OTB_SYS_Jobtitle] ");
strSql.Append(" WHERE ");
strSql.Append(" (JobtitleID LIKE @JobtitleID OR @JobtitleID IS NULL OR @JobtitleID='%%') ");
strSql.Append(" AND (JobtitleName LIKE @JobtitleName OR @JobtitleName IS NULL OR JobtitleName IS NULL OR @JobtitleName='%%') ");
strSql.Append(" AND (Effective = @Effective OR @Effective IS NULL OR @Effective='') ");
SqlParameter[] parameters = {
new SqlParameter("@DepartID", SqlDbType.VarChar,10)
,new SqlParameter("@JobtitleID", SqlDbType.VarChar,10)
,new SqlParameter("@JobtitleName", SqlDbType.NVarChar,50)
,new SqlParameter("@Effective", SqlDbType.Char,1)
};
parameters[0].Value = DepartID;
parameters[1].Value = "%" + JobtitleID + "%";
parameters[2].Value = "%" + JobtitleName + "%";
parameters[3].Value = Effective;
return (int)DbHelperSQL.GetSingle(strSql.ToString(), parameters);
}
#endregion Method
}
}