/// <summary>
/// 태그아이디로 노트리스트 불러옴
/// </summary>
/// <param name="tagId"></param>
/// <returns></returns>
public static List <NoteVO> GetNoteListByTagId(int tagId)
{
List <NoteVO> noteList = new List <NoteVO>();
using (OracleConnection conn = DbHelper.NewConnection())
{
conn.Open();
//중복검사
String sql = $"select note_id from note_tag_map where tag_id = :TagId";
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = sql
};
OracleParameter paramTagId = cmd.Parameters.Add("TagId", OracleDbType.Varchar2);
paramTagId.Value = tagId;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
NoteVO note = new NoteVO();
note = NoteDAO.GetNotebyId(int.Parse(reader["note_id"].ToString()));
noteList.Add(note);
}
reader.Close();
return(noteList);
}
}
public static Dictionary <int, object> GetShortcuts()
{
Dictionary <int, object> shortcutDic = new Dictionary <int, object>();
//키값으론 order를 넣는다. 리스트에는 Note or Notebook을 넣고 각각 id, title을 넣는다.
//shortcut에서 orderby order로 모든 데이터를 긁어온 후 order를 키값으로 넣고 type과 id를 해당 value 넣는다.
using (OracleConnection conn = DbHelper.NewConnection())
{
conn.Open();
String sql = "SELECT * FROM shortcut ORDER BY ORDERS ASC";
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = sql
};
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int order = int.Parse(reader["orders"].ToString());
int noteid = 0;
int notebookid = 0;
if (reader["noteid"] != DBNull.Value)
{
noteid = int.Parse(reader["noteid"].ToString());
}
else if (reader["notebookid"] != DBNull.Value)
{
notebookid = int.Parse(reader["notebookid"].ToString());
}
if (noteid != 0) //노트일경우
{
NoteVO newNote = new NoteVO()
{
NoteId = noteid,
Title = NoteDAO.GetNotebyId(noteid).Title
};
shortcutDic.Add(order, newNote);
}
else if (notebookid != 0) //노트북일경우
{
NoteBookVO newNoteBook = new NoteBookVO()
{
NoteBookId = notebookid,
Name = NoteBookDAO.GetNoteBookbyId(notebookid).Name
};
shortcutDic.Add(order, newNoteBook);
}
}
reader.Close();
return(shortcutDic);
}
}
//노트북 이나 노트가 0이 아닌 것을 찾아서 title을 따온다.
public static List <object> GetShorcutList()
{
List <object> shortcuts = new List <object>();
using (OracleConnection conn = DbHelper.NewConnection())
{
conn.Open();
String noteSql = "SELECT NOTE.NOTEID, NOTE.TITLE, SHORTCUT.ORDERS, isdeleted FROM NOTE, SHORTCUT WHERE NOTE.NOTEID = SHORTCUT.NOTEID AND ISDELETED = 0";
String noteBookSql = "SELECT NOTEBOOK.NOTEBOOKID, NOTEBOOK.NAME, SHORTCUT.ORDERS FROM NOTEBOOK, SHORTCUT WHERE NOTEBOOK.NOTEBOOKID = SHORTCUT.NOTEBOOKID";
string all = "";
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = noteSql
};
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
NoteVO note = new NoteVO();
note.Title = reader["title"].ToString();
note.NoteId = int.Parse(reader["noteid"].ToString());
int index = int.Parse(reader["orders"].ToString()) - 1;
shortcuts.Add(note);
}
reader.Close();
OracleCommand noteBookCmd = new OracleCommand
{
Connection = conn,
CommandText = noteBookSql
};
OracleDataReader noteBookReader = noteBookCmd.ExecuteReader();
while (noteBookReader.Read())
{
NoteBookVO notebook = new NoteBookVO();
notebook.Name = noteBookReader["name"].ToString();
notebook.NoteBookId = int.Parse(noteBookReader["notebookid"].ToString());
int index = int.Parse(noteBookReader["orders"].ToString()) - 1;
shortcuts.Add(notebook);
}
noteBookReader.Close();
return(shortcuts);
}
}
// 노트 아이디로 노트 불러오기 : /detail
/*
* 목적 : 노트 아이디로 노트의 정보를 불러옴
* 준비물 : 노트, db커넥션
* (1) 빈 노트와 db커넥션을 생성.
* (2) 노트의 정보를 불러오는 쿼리 작성
* (3) 불러온 정보를 빈 노트에 넣어준다.
*/
public static NoteVO GetNotebyId(int noteId)
{
NoteVO note = new NoteVO();
String sql = "select * from Note where noteId = " + noteId.ToString();
OracleConnection conn = DbHelper.NewConnection();
conn.Open();
//using (conn = DbHelper.NewConnection())
//{
// note = conn.QuerySingle<NoteVO>(sql);
//}
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = sql
};
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
NoteVO newNote = new NoteVO
{
NoteId = int.Parse(reader["NOTEID"].ToString()),
Title = reader["TITLE"].ToString(),
IsDeleted = int.Parse(reader["ISDELETED"].ToString()),
IsShortcut = Convert.ToBoolean(int.Parse(reader["ISSHORTCUT"].ToString())),
Contents = reader["CONTENTS"] as String,
NoteDate = reader["NOTEDATE"].ToString(),
UpdatedDate = reader["UpdatedDate"].ToString(),
NoteBookId = int.Parse(reader["NOTEBOOKID"].ToString()),
TagList = TagDAO.GetTagListByNote(noteId)
};
note = newNote;
}
return(note);
}
/// <summary>
/// 목적 : 인덱스 페이지에서 노트리스트를 최근순으로 불러옴
/// 준비물 : 노트리스트, db커넥션
/// (1) 빈 노트리스트와 db커넥션을 생성.
/// (2) isdeleted의 값이 0인(삭제되지 않은) 노트를 불러오는 쿼리 작성.
/// (3) 불러온 노트를 노트리스트에 넣고 커넥션과 reader를 닫아준다.
/// </summary>
/// <param name="orderColumnName">"notedate" | "title"</param>
/// <param name="orderType">"ASC" | "DESC"</param>
/// <param name="noteBookId"></param>
/// <returns></returns>
public static List <NoteVO> GetNoteList(OrderColumn orderColumnName, OrderType orderType, int noteBookId)
{
List <NoteVO> noteList = new List <NoteVO>();
OracleConnection conn = DbHelper.NewConnection();
conn.Open();
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("\n SELECT ");
sbQuery.Append("\n noteid, ");
sbQuery.Append("\n title, ");
sbQuery.Append("\n contents, ");
sbQuery.Append("\n notedate, ");
sbQuery.Append("\n TO_CHAR(notedate,'YYMMDD')AS yymmdd ");
sbQuery.Append("\n FROM ");
sbQuery.Append("\n note ");
if (noteBookId == 0) //삭제 되지 않은 노트 전체
{
sbQuery.Append("\n WHERE isdeleted = 0 ");
}
else if (noteBookId == -1) //휴지통에 있는 노트 전체
{
sbQuery.Append("\n WHERE isdeleted = 1 ");
}
else
{
sbQuery.Append("\n WHERE isdeleted = 0 ");
sbQuery.Append("\n AND notebookid = " + noteBookId);
}
sbQuery.Append($"\n order by {orderColumnName} {orderType}");
String sql = sbQuery.ToString();
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = sql
};
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
NoteVO note = new NoteVO
{
NoteId = int.Parse(reader["NOTEID"].ToString()),
Title = reader["TITLE"].ToString(),
Contents = reader["CONTENTS"] as String,
NoteDate = reader["YYMMDD"].ToString(),
FullDate = Convert.ToDateTime(reader["NOTEDATE"])
};
noteList.Add(note);
DateTime today = DateTime.Now;
DateTime theDay = note.FullDate;
TimeSpan diff = today.Subtract(theDay);
if (diff.Days < 21)
{
note.NoteDate = "2주 전";
if (diff.Days < 14)
{
note.NoteDate = "1주 전";
if (diff.Days < 7)
{
note.NoteDate = diff.Days.ToString() + "일 전";
if (diff.Days < 1)
{
note.NoteDate = diff.Hours.ToString() + "시간 전";
if (diff.Hours < 1)
{
note.NoteDate = diff.Minutes.ToString() + "분 전";
if (diff.Minutes < 1)
{
note.NoteDate = diff.Seconds.ToString() + "초 전";
}
}
}
}
}
}
else
{
note.NoteDate = note.FullDate.ToString();
//note.NoteDate = note.FullDate.ToString("yy. M. d.");
}
}
reader.Close();
conn.Close();
return(noteList);
}