/// <summary>
/// 已選學校列表
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public List <TbCommonData> qryUserRelativeData(string userId)
{
string sql = getSelectSql("TbUserAuthRelativeDataSqlProvider", "selectTableUserAuth2");
List <TbCommonData> list = new List <TbCommonData>();
var resultList = QueryTableListBySql(sql, new { USER_ID = userId });
foreach (var row in resultList)
{
TbCommonData model = new TbCommonData();
model.COMM_CODE = row.RELATIVE_USER_ID;
model.COMM_VALUE = row.SCHOOL;
list.Add(model);
}
return(list);
}
/// <summary>
/// 透過輸入commCode及Type 撈取對應中文
/// </summary>
/// <param name="commonCode"></param>
/// <param name="type"></param>
/// <returns></returns>
public string qryCommonName(string commonCode, string type)
{
string sql = this.getSelectSql("TbCommonDataSqlProvider", "selectTableCommon", " where COMM_TYPE = @COMM_TYPE and COMM_CODE = @COMM_CODE");
TbCommonData model = new TbCommonData();
var resultList = QueryTableListBySql(sql, new { COMM_TYPE = type, COMM_CODE = commonCode });
foreach (var row in resultList)
{
model.COMM_VALUE = row.COMM_VALUE;
model.COMM_CODE = row.COMM_CODE;
}
return(model.COMM_VALUE);
}
public List <TbCommonData> qryCommonListByPerCode(string commType, string commPerCode)
{
string sql = this.getSelectSql("TbCommonDataSqlProvider", "selectTableCommon", " where COMM_TYPE = @COMM_TYPE and COMM_PER_CODE = @COMM_PER_CODE ");
List <TbCommonData> tbSnoList = new List <TbCommonData>();
var resultList = QueryTableListBySql(sql, new { COMM_TYPE = commType, COMM_PER_CODE = commPerCode });
foreach (var row in resultList)
{
TbCommonData model = new TbCommonData();
model.COMM_VALUE = row.COMM_VALUE;
model.COMM_CODE = row.COMM_CODE;
model.COMM_VALUE2 = row.COMM_CODE2;
tbSnoList.Add(model);
}
return(tbSnoList);
}
public string qryCommonName(TbCommonData condition)
{
TbCommonData model = new TbCommonData();
string commonName = "";
using (SqlConnection dbConn = new SqlConnection(DbConnection.connString))
{
try{
dbConn.Open();
dao.dbConn = dbConn;
commonName = dao.qryCommonName(condition.COMM_CODE, condition.COMM_TYPE);
}catch (Exception e) {
logger.Error(e, e.Message);
}
}
return(commonName);
}
/// <summary>
/// 撈出縣市(county)下的行政區(City) 或 撈出行政區(City)下的 路名(Road)
/// </summary>
/// <param name="commonCode">city或road的</param>
/// <param name="type">要撈city或road</param>
/// <returns></returns>
public List <TbCommonData> qryCityRoadDataByList(string commonCode, string type)
{
string xmlSelectId = (type == "city") ? "selectTableCommonCity" : "selectTableCommonRoad";
string sql = this.getSelectSql("TbCommonDataSqlProvider", xmlSelectId, " and COMM_PER_CODE = @COMM_PER_CODE ");
List <TbCommonData> list = new List <TbCommonData>();
var resultList = QueryTableListBySql(sql, new { COMM_PER_CODE = commonCode });
foreach (var row in resultList)
{
TbCommonData model = new TbCommonData();
model.COMM_VALUE = row.COMM_VALUE;
model.COMM_CODE = row.COMM_CODE;
list.Add(model);
}
return(list);
}
/// <summary>
/// 撈出台灣所有縣市(county)
/// </summary>
/// <returns></returns>
public List <TbCommonData> qryCountyDataByList()
{
string sql = this.getSelectSql("TbCommonDataSqlProvider", "selectTableCommonCounty");
List <TbCommonData> tbCountyList = new List <TbCommonData>();
var resultList = QueryTableListBySql(sql);
foreach (var row in resultList)
{
TbCommonData model = new TbCommonData();
model.COMM_VALUE = row.COMM_VALUE;
model.COMM_CODE = row.COMM_CODE;
tbCountyList.Add(model);
}
return(tbCountyList);
}
/// <summary>
/// 撈出參數
/// </summary>
/// <returns></returns>
public List <TbCommonData> qryCommonByList(string type)
{
string sql = this.getSelectSql("TbCommonDataSqlProvider", "selectTableCommon", " where COMM_TYPE = @COMM_TYPE ");
List <TbCommonData> tbSnoList = new List <TbCommonData>();
var resultList = QueryTableListBySql(sql, new { COMM_TYPE = type });
foreach (var row in resultList)
{
TbCommonData model = new TbCommonData();
model.COMM_VALUE = row.COMM_VALUE;
model.COMM_CODE = row.COMM_CODE;
model.COMM_VALUE2 = row.COMM_VALUE2;
model.ROW_ID = row.ROW_ID;
tbSnoList.Add(model);
}
return(tbSnoList);
}
/// <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);
}