/// <summary>
/// 只給MailSend呼叫使用
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
public List <TbUserData> qryUserDataByList(TbUserData condition = null)
{
List <TbUserData> userList = new List <TbUserData>();
using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
dbConn.Open();
dao.dbConn = dbConn;
try
{
if (condition == null)
{
userList = dao.qryUserDataByList();
}
else //有帶查詢資料
{
if (object.ReferenceEquals(condition.COUNTY_ID, null))
{
condition.COUNTY_ID = "0";
}
if (object.ReferenceEquals(condition.SCHOOL_SYSTEM_SNO, null))
{
condition.SCHOOL_SYSTEM_SNO = 0;
}
userList = dao.qryUserDataByList(condition);
}
}
catch (Exception e)
{
logger.Error(e, e.Message);
}
}
return(userList);
}
/// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
public int insertUserData(TbUserData model)
{
string sql = this.getInsertSql("TbUserDataSqlProvider", "insertTableUser");
int result = ExecuteTableBySql(sql, modelDbParametersMapping(model));
return(result);
}
public TbUserData qryUserData(string userId)
{
string sql = this.getSelectSql("TbUserDataSqlProvider", "selectTableUser", " where USER_ID = @USER_ID");
List <TbUserData> userList = new List <TbUserData>();
var resultList = QueryTableListBySql(sql, new { USER_ID = userId });
foreach (var row in resultList)
{
TbUserData model = new TbUserData();
model.COUNTY_ID = row.COUNTY_ID;
model.SCHOOL = row.SCHOOL;
model.ACCOUNT_STATUS = row.STATUS;
model.NAME = row.NAME;
model.JOB = row.JOB;
model.PHONE = row.PHONE;
model.EMAIL = row.EMAIL;
model.USER_ID = row.USER_ID;
model.ACCOUNT = row.ACCOUNT;
model.PASSWORD = row.PASSWORD; //Ting I 先加回來
model.SCHOOL_SYSTEM_SNO = row.SCHOOL_SYSTEM_SNO;
model.CITY = row.CITY;
model.SCHOOL_ADDRESS = row.SCHOOL_ADDRESS;
model.SCHOOL_PRESIDENT = row.SCHOOL_PRESIDENT;
model.ROAD = row.ROAD;
model.COUNTY_ID_CHT = row.COUNTY_ID_CHT;
userList.Add(model);
}
return(userList[0]);
}
public List <TbUserData> qryParentUser(string userId)
{
List <TbUserData> userList = new List <TbUserData>();
string sql = this.getSelectSql("TbUserDataSqlProvider", "qryParentUser");
var resultList = QueryTableListBySql(sql, new { USER_ID = userId });
foreach (var row in resultList)
{
TbUserData model = new TbUserData();
model.COUNTY_ID = row.COUNTY_ID;
model.SCHOOL = row.SCHOOL;
model.ACCOUNT_STATUS = row.STATUS;
model.NAME = row.NAME;
model.JOB = row.JOB;
model.PHONE = row.TEL;
model.EMAIL = row.EMAIL;
model.USER_ID = row.USER_ID;
model.CH_OWNER = row.CH_OWNER;
model.TITLE = row.TITLE;
model.SCHOOL_SYSTEM_SNO = row.SCHOOL_SYSTEM_SNO;
model.CITY = row.CITY;
model.SCHOOL_ADDRESS = row.SCHOOL_ADDRESS;
model.SCHOOL_PRESIDENT = row.SCHOOL_PRESIDENT;
model.ROAD = row.ROAD;
userList.Add(model);
}
return(userList);
}
public string CheckUserId(string userId)
{
string message = "";
TbUserData model = qryUserData(userId);
if (model != null)
{
message = userId + "帳號重複，請重新輸入";
}
return(message);
}
public Dictionary <string, dynamic> insertUserData(TbUserData model)
{
Dictionary <string, dynamic> result = new Dictionary <string, dynamic>();
string status = "success", msg = "";
using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
dbConn.Open();
SqlTransaction dbConnTxn = dbConn.BeginTransaction();
try{
dao.dbConn = dbConn;
dao.dbConnTxn = dbConnTxn;
loginAccountDataDao.dbConn = dbConn;
loginAccountDataDao.dbConnTxn = dbConnTxn;
if (dao.UserIdExists(model.USER_ID))
{
status = "error";
msg = "學校代號重複，請重新輸入";
}
if (loginAccountDataDao.loginUserExists(model.ACCOUNT))
{
status = "error";
msg = "帳號重複，請重新輸入";
}
//前端已檢查，後端還要再確定一次，避免寫入重複account
if (status == "success")
{
model.PASSWORD = new SecurityUtils().getCsrcEnCrypt(model.PASSWORD);
if (dao.insertUserData(model) == 0)
{
status = "error";
msg = "權限帳號資料新增失敗!!";
}
}
}catch (Exception e) {
status = "exception";
msg = "處理您的要求時發生錯誤!!";
logger.Error(e, e.Message);
}finally{
if (status == "success")
{
dbConnTxn.Commit();
}
else
{
dbConnTxn.Rollback();
}
}
}
result.Add("status", status);
result.Add("msg", msg);
return(result);
}
/// <summary>
/// [0]- 職稱代號 ; [1] - 職稱中文
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public string getUserTitle(string userId)
{
string title = "";
string sql = this.getSelectSql("TbUserDataSqlProvider", "selectUserTitle");
var resultList = QueryTableListBySql(sql, new { ACCOUNT = userId });
TbUserData model = new TbUserData();
foreach (var row in resultList)
{
title = row.TITLE;
}
//title[1] 等回到Service層再撈
return(title);
}
public List <TbUserData> qryUserEmail(string userIds)
{
string userId = "'" + userIds.Replace(",", "','") + "'";
string sql = this.getSelectSql("TbUserDataSqlProvider", "selectUserEmail", " where USER_ID in (" + userId + ")");
List <TbUserData> userList = new List <TbUserData>();
var resultList = QueryTableListBySql(sql, new { USER_ID = userId });
foreach (var row in resultList)
{
TbUserData model = new TbUserData();
model.EMAIL = row.EMAIL;
userList.Add(model);
}
return(userList);
}
public TbUserData qryUserData(string userId)
{
TbUserData result = new TbUserData();
using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
try{
dbConn.Open();
dao.dbConn = dbConn;
result = dao.qryUserData(userId);
result.PASSWORD = new SecurityUtils().getCsrcDeCrypt(result.PASSWORD); //解密
}
catch (Exception e) {
logger.Error(e, e.Message);
}
}
return(result);
}
public List <TbCommonData> qryNoUserRelativeData(TbUserData model)
{
List <TbCommonData> list = new List <TbCommonData>();
using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
try
{
dbConn.Open();
dao.dbConn = dbConn;
list = dao.qryNoUserRelativeData(model);
}
catch (Exception e)
{
logger.Error(e, e.Message);
}
}
return(list);
}
/// <summary>
/// 透過userid 取得 學校名稱
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public string getUserName(string userId)
{
TbUserData result = new TbUserData();
using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
try
{
dbConn.Open();
dao.dbConn = dbConn;
result = dao.qryUserData(userId);
}
catch (Exception e)
{
logger.Error(e, e.Message);
}
}
return(result.SCHOOL);
}
/// <summary>
/// 查詢 UserData 列表。
/// </summary>
/// <returns>The user list.</returns>
public GridModel qryUserDataForGrid(int page, int pageSize, TbUserData condition = null)
{
CommonService commonService = new CommonService();
List <TbUserData> userList = new List <TbUserData>();
GridModel gridModel = new GridModel();
int totalCount = 0;
using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
dbConn.Open();
dao.dbConn = dbConn;
try
{
int[] rowIndex = commonService.getRowRange(page, pageSize);
if (condition == null)
{
userList = dao.qryUserDataForGrid(rowIndex[0], rowIndex[1]);
}
else //有帶查詢資料
{
if (object.ReferenceEquals(condition.COUNTY_ID, null))
{
condition.COUNTY_ID = "0";
}
if (object.ReferenceEquals(condition.SCHOOL_SYSTEM_SNO, null))
{
condition.SCHOOL_SYSTEM_SNO = 0;
}
userList = dao.qryUserDataForGrid(rowIndex[0], rowIndex[1], condition);
}
totalCount = dao.qryUserDataForGridCount(condition);
gridModel = commonService.setGridModel(page, pageSize, totalCount, userList);
}
catch (Exception e)
{
logger.Error(e, e.Message);
}
}
return(gridModel);
}
/// <summary>
/// 未選學校列表
/// </summary>
/// <returns></returns>
public List <TbCommonData> qryNoUserRelativeData(TbUserData condition)
{
string sql = "";
string conditionStr = "";
if (condition.COUNTY_ID.ToString() != "0")
{
conditionStr += " and COUNTY_ID = @COUNTY_ID ";
}
if (!string.IsNullOrEmpty(condition.SCHOOL))
{
conditionStr += " and SCHOOL like @SCHOOL ";
}
if (condition.SCHOOL_SYSTEM_SNO.ToString() != "0")
{
conditionStr += " and SCHOOL_SYSTEM_SNO = @SCHOOL_SYSTEM_SNO ";
}
sql = getSelectSql("TbUserAuthRelativeDataSqlProvider", "selectTableUserAuth1", conditionStr);
List <TbCommonData> userList = new List <TbCommonData>();
var resultList = QueryTableListBySql(sql, new {
USER_ID = condition.USER_ID,
COUNTY_ID = condition.COUNTY_ID,
SCHOOL = "%" + condition.SCHOOL + "%",
SCHOOL_SYSTEM_SNO = condition.SCHOOL_SYSTEM_SNO
});
foreach (var row in resultList)
{
TbCommonData model = new TbCommonData();
model.COMM_VALUE = row.SCHOOL;
model.COMM_CODE = row.USER_ID;
userList.Add(model);
}
return(userList);
}
public void updUserData(TbUserData model)
{
using (SqlConnection dbConn = new SqlConnection(DbConnection.connString)){
dbConn.Open();
SqlTransaction dbConnTxn = dbConn.BeginTransaction();
try
{
dao.dbConn = dbConn;
dao.dbConnTxn = dbConnTxn;
model.PASSWORD = new SecurityUtils().getCsrcEnCrypt(model.PASSWORD); //加密
if (dao.updUserData(model) == 0)
{
throw new Exception("權限帳號資料修改失敗!!");
}
dbConnTxn.Commit();
}
catch (Exception e)
{
dbConnTxn.Rollback();
logger.Error(e, e.Message);
}
}
}
/// <summary>
/// 給update與insert呼叫使用
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
private object modelDbParametersMapping(TbUserData model)
{
return(new
{
USER_ID = model.USER_ID,
PASSWORD = model.PASSWORD,
SCHOOL = model.SCHOOL,
SCHOOL_SYSTEM_SNO = model.SCHOOL_SYSTEM_SNO,
COUNTY_ID = model.COUNTY_ID,
CITY = model.CITY,
ROAD = model.ROAD,
SCHOOL_ADDRESS = model.SCHOOL_ADDRESS,
SCHOOL_PRESIDENT = model.SCHOOL_PRESIDENT,
ACCOUNT_NAME = model.NAME,
JOB = model.JOB,
PHONE = model.PHONE,
EMAIL = model.EMAIL,
STATUS = model.ACCOUNT_STATUS,
ACCOUNT = model.ACCOUNT,
IS_HAVE_SPCF = model.IS_HAVE_SPCF,
UP_USER = model.UP_USER,
UP_IP = model.UP_IP
});
}
public int updOnlyUserData(TbUserData model)
{
int count = 0;
string sql = this.getUpdateSql("TbUserDataSqlProvider", "updUserDataOnly");
count = ExecuteTableBySql(sql, new{
USER_ID = model.USER_ID,
COUNTY_ID = model.COUNTY_ID,
CITY = model.CITY,
ROAD = model.ROAD,
SCHOOL = model.SCHOOL,
CH_OWNER = model.CH_OWNER,
TEL = model.TEL,
SCHOOL_PRESIDENT = model.SCHOOL_PRESIDENT,
SCHOOL_ADDRESS = model.SCHOOL_ADDRESS,
SCHOOL_SYSTEM_SNO = model.SCHOOL_SYSTEM_SNO,
SCHOOL_CODE = model.SCHOOL_CODE,
SCHOOL_TYPE = model.SCHOOL_TYPE,
UP_ID = model.UP_IP,
UP_USER = model.UP_USER,
IS_HAVE_SPCF = model.IS_HAVE_SPCF
});
return(count);
}
public List <TbUserData> qryUserDataForGrid(int beginRow, int endRow, TbUserData condition = null)
{
string sql;
string conditionStr = ""; //有帶查詢才會用到此變數
if (object.ReferenceEquals(condition, null))
{
sql = this.getSelectSql("TbUserDataSqlProvider", "selectTableUser", " where [LOGIN_TYPE] not in (1) ");
}
else //有帶查詢資料
{
conditionStr = " where [LOGIN_TYPE] not in (1) ";
if (!string.IsNullOrEmpty(condition.USER_ID))
{
conditionStr += " and USER_ID like @USER_ID ";
}
if (condition.COUNTY_ID.ToString() != "0")
{
conditionStr += " and COUNTY_ID = @COUNTY_ID ";
}
if (condition.SCHOOL_SYSTEM_SNO.ToString() != "0")
{
conditionStr += " and SCHOOL_SYSTEM_SNO = @SCHOOL_SYSTEM_SNO ";
}
if (!string.IsNullOrEmpty(condition.SCHOOL))
{
conditionStr += " and SCHOOL like @SCHOOL ";
}
if (condition.SCHOOL_TYPE_ARR != null && condition.SCHOOL_TYPE_ARR.Length > 0)
{
string schoolTypes = "";
int i = 0;
foreach (int val in condition.SCHOOL_TYPE_ARR)
{
schoolTypes += "'" + val + "'";
if (i < condition.SCHOOL_TYPE_ARR.Length - 1)
{
schoolTypes += ",";
}
i++;
}
conditionStr += " and SCHOOL_TYPE in (" + schoolTypes + ") ";
}
sql = this.getSelectSql("TbUserDataSqlProvider", "selectTableUser", conditionStr);
}
sql = "select * from (" + sql + ") new_table where rowId >= " + beginRow + " and rowId <= " + endRow;
List <TbUserData> userList = new List <TbUserData>();
IEnumerable <dynamic> resultList = null;
if (string.IsNullOrEmpty(conditionStr))
{
resultList = QueryTableListBySql(sql);
}
else
{
resultList = QueryTableListBySql(sql, new
{
USER_ID = "%" + condition.USER_ID + "%",
COUNTY_ID = condition.COUNTY_ID,
SCHOOL_SYSTEM_SNO = condition.SCHOOL_SYSTEM_SNO,
SCHOOL = "%" + condition.SCHOOL + "%"
});
}
foreach (var row in resultList)
{
TbUserData model = new TbUserData();
model.COUNTY_ID = row.COUNTY_ID;
model.PASSWORD = row.PASSWORD; //Ting I 先加回來
model.SCHOOL = row.SCHOOL;
model.ACCOUNT_STATUS = row.STATUS;
model.NAME = row.NAME;
model.PHONE = row.PHONE;
model.EMAIL = row.EMAIL;
model.USER_ID = row.USER_ID;
model.ACCOUNT = row.ACCOUNT;
model.SCHOOL_SYSTEM_SNO = row.SCHOOL_SYSTEM_SNO;
model.CITY = row.CITY;
model.SCHOOL_ADDRESS = row.SCHOOL_ADDRESS;
model.SCHOOL_PRESIDENT = row.SCHOOL_PRESIDENT;
model.ROAD = row.ROAD;
model.COUNTY_ID_CHT = row.COUNTY_ID_CHT;
userList.Add(model);
}
return(userList);
}
/// <summary>
/// 回傳筆數給Grid用
/// </summary>
/// <returns></returns>
public int qryUserDataForGridCount(TbUserData condition = null)
{
CommonDataDao dao = new CommonDataDao();
string conditionStr = "where [LOGIN_TYPE] not in (1, 4)";
if (!string.IsNullOrEmpty(condition.USER_ID))
{
conditionStr += " and USER_ID like @USER_ID ";
}
if (condition.COUNTY_ID.ToString() != "0")
{
conditionStr += " and COUNTY_ID = @COUNTY_ID ";
}
if (condition.SCHOOL_SYSTEM_SNO.ToString() != "0")
{
conditionStr += " and SCHOOL_SYSTEM_SNO = @SCHOOL_SYSTEM_SNO ";
}
if (!string.IsNullOrEmpty(condition.SCHOOL))
{
conditionStr += " and SCHOOL like @SCHOOL ";
}
if (condition.SCHOOL_TYPE_ARR != null && condition.SCHOOL_TYPE_ARR.Length > 0)
{
string schoolTypes = "";
int i = 0;
foreach (int val in condition.SCHOOL_TYPE_ARR)
{
schoolTypes += "'" + val + "'";
if (i < condition.SCHOOL_TYPE_ARR.Length - 1)
{
schoolTypes += ",";
}
i++;
}
conditionStr += " and SCHOOL_TYPE in (" + schoolTypes + ") ";
}
string sql = dao.getCountSql("USER_ID", "VW_LOGIN_INFO", conditionStr);
int count = 0;
IEnumerable <dynamic> result;
if (string.IsNullOrEmpty(conditionStr))
{
result = dbConn.Query(sql);
}
else
{
result = dbConn.Query(sql, new
{
USER_ID = "%" + condition.USER_ID + "%",
COUNTY_ID = condition.COUNTY_ID,
SCHOOL_SYSTEM_SNO = condition.SCHOOL_SYSTEM_SNO,
SCHOOL = "%" + condition.SCHOOL + "%"
});
}
foreach (var item in result)
{
count = item.COUNT;
}
return(count);
}
