// 닉네임 리스트
public List <NickKeyowrd> SelectNickKeywordList(string nickNm, string workYmd)
{
List <NickKeyowrd> list = new List <NickKeyowrd>();
SQLiteConnection dbConnection = ConnectionToDB();
String sql = ""
+ "SELECT "
+ "* "
+ "FROM NICK N "
+ "WHERE 1 = 1 "
+ "AND N.USE_YN = 'Y' "
+ "AND N.WORK_YMD > strftime('%Y%m%d' ,'now', 'localtime', '-7 day') ";
if (nickNm != null && nickNm.Trim().Length != 0)
{
sql += "and N.NICK_NM = '" + nickNm + "' ";
}
if (workYmd != null && workYmd.Trim().Length != 0)
{
sql += "and N.WORK_YMD = '" + workYmd + "' ";
}
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
NickKeyowrd n = new NickKeyowrd();
n.nickNm = (string)reader["NICK_NM"];
n.keyword = (string)reader["KEYWORD"];
n.workYmd = (string)reader["WORK_YMD"];
n.totCnt = (long)reader["TOT_CNT"];
n.workCnt = (long)reader["WORK_CNT"];
list.Add(n);
}
DisconnectionToDB(dbConnection);
return(list);
}
// 남은 닉네임 리스트
public List <NickKeyowrd> SelectRemainNickKeywordList()
{
List <NickKeyowrd> list = new List <NickKeyowrd>();
SQLiteConnection dbConnection = ConnectionToDB();
String sql = ""
+ "SELECT "
+ "* "
+ "FROM NICK N "
+ "WHERE 1 = 1 "
+ "AND N.USE_YN = 'Y' "
+ "AND N.WORK_CNT < N.TOT_CNT "
+ "AND N.WORK_YMD = strftime('%Y%m%d' ,'now', 'localtime') "
+ "ORDER BY N.WORK_CNT ASC";
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
NickKeyowrd n = new NickKeyowrd();
n.nickNm = (string)reader["NICK_NM"];
n.keyword = (string)reader["KEYWORD"];
n.workYmd = (string)reader["WORK_YMD"];
n.option1 = DBNull.Value.Equals(reader["OPTION1"]) ? "" : (string)reader["OPTION1"];
n.option2 = DBNull.Value.Equals(reader["OPTION2"]) ? "" : (string)reader["OPTION2"];
n.option3 = DBNull.Value.Equals(reader["OPTION3"]) ? "" : (string)reader["OPTION3"];
list.Add(n);
}
DisconnectionToDB(dbConnection);
return(list);
}
