public bool deleteAllMemberInformation(string memberId)
{
if (ValidChecker.checkStringValid(memberId) == false)
{
return(false);
}
try
{
string bookCartITemDeleteQuery = "delete from " + bookCartItemTable + " where memberid='" + memberId + "';";
NpgsqlCommand deleteBookCartItemSqlCommand = new NpgsqlCommand(bookCartITemDeleteQuery, this.connection);
deleteBookCartItemSqlCommand.ExecuteNonQuery();
string OrderDeleteQuery = "delete from " + bookOrderTable + " where memberid='" + memberId + "';";
NpgsqlCommand DeleteOrderSqlCommand = new NpgsqlCommand(OrderDeleteQuery, this.connection);
DeleteOrderSqlCommand.ExecuteNonQuery();
}
catch
{
return(false);
}
return(true);
}
public bool checkMemberAuthentication(string memberId)
{
if (ValidChecker.checkStringValid(memberId) == false)
{
return(false);
}
string query = "select isauthentication from " + memberTable + " where id='" + memberId + "';";
NpgsqlCommand cmd = new NpgsqlCommand(query, this.getConnection());
NpgsqlDataReader dr = cmd.ExecuteReader();
string resultString = "";
while (dr.Read())
{
resultString += dr[0];
}
resultString = resultString.Trim();
if (resultString.Equals("True"))
{
return(true);
}
return(false);
}
public bool deleteMember(string memberId)
{
if (ValidChecker.checkStringValid(memberId) == false)
{
return(false);
}
if (this.deleteAllMemberInformation(memberId) == false)
{
return(false);
}
string query = "delete from " + this.memberTable + " where id='" + memberId + "';";
NpgsqlCommand sqlCommand = new NpgsqlCommand(query, this.connection);
try
{
sqlCommand.ExecuteNonQuery();
}
catch
{
return(false);
}
return(true);
}
public bool sendFindPasswordEmail(string mailRecipient, string memberPassword)
{
if (ValidChecker.checkStringValid(mailRecipient) == false)
{
return(false);
}
MailMessage mailMessage = new MailMessage();
mailMessage.From = new MailAddress(this.senderAddress, this.senderName, System.Text.Encoding.Default);
mailMessage.Subject = "회원님의 BookStore 임시 비밀번호 입니다.";
mailMessage.IsBodyHtml = false;
mailMessage.Body = "BookStore 임시 비밀번호는 " + memberPassword + " 입니다.\n" +
"서비스 이용전에 반드시 비밀번호를 변경해 주세요.";
mailMessage.SubjectEncoding = System.Text.Encoding.Default;
mailMessage.BodyEncoding = System.Text.Encoding.Default;
mailMessage.To.Add(mailRecipient);
SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
smtpClient.UseDefaultCredentials = false;
smtpClient.EnableSsl = true;
smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
smtpClient.Credentials = new System.Net.NetworkCredential("mia22rmrjs9", "gldpflrmrjs9");
smtpClient.Send(mailMessage);
return(true);
}
public bool setPassword(string password)
{
if (ValidChecker.checkStringValid(password) == false)
{
return(false);
}
this.password = password;
return(true);
}
public bool setAuthenticationAnswer(string answer)
{
if (ValidChecker.checkStringValid(answer) == false)
{
return(false);
}
this.authenticationAnswer = answer;
return(true);
}
public bool setAuthenticationQustion(string question)
{
if (ValidChecker.checkStringValid(question) == false)
{
return(false);
}
this.authenticationQustion = question;
return(true);
}
public bool setAddress(string address)
{
if (ValidChecker.checkStringValid(address) == false)
{
return(false);
}
this.address = address;
return(true);
}
public bool setMailNumber(string mailNumber)
{
if (ValidChecker.checkStringValid(mailNumber) == false)
{
return(false);
}
this.mailNumber = mailNumber;
return(true);
}
public bool setPhone(string phone)
{
if (ValidChecker.checkStringValid(phone) == false)
{
return(false);
}
this.phone = phone;
return(true);
}
public bool setSubEmail(string subEmail)
{
if (ValidChecker.checkStringValid(subEmail) == false)
{
return(false);
}
this.subEmail = subEmail;
return(true);
}
public Member inquiryMemberSubEmail(string subEmail)
{
if (ValidChecker.checkStringValid(subEmail) == false)
{
return(null);
}
string query = "select * from member where sub_email='" + subEmail + "';";
NpgsqlCommand sqlCommand = new NpgsqlCommand(query, this.connection);
NpgsqlDataReader dataReader = null;
try
{
dataReader = sqlCommand.ExecuteReader();
}
catch
{
return(null);
}
Debug.Assert(dataReader != null);
if (!dataReader.HasRows)
{
return(null);
}
string id = null;
string password = null;
string sub_email = null;
string phone = null;
string mail_number = null;
string address = null;
string authentication_question = null;
string authentication_answer = null;
while (dataReader.Read())
{
id = dataReader.GetString(1);
password = dataReader.GetString(2);
sub_email = dataReader.GetString(3);
phone = dataReader.GetString(4);
mail_number = dataReader.GetString(5);
address = dataReader.GetString(6);
authentication_question = dataReader.GetString(7);
authentication_answer = dataReader.GetString(8);
}
dataReader.Close();
Member member = new Member(id, password, sub_email, phone, mail_number, address, authentication_question, authentication_answer);
Debug.Assert(member != null);
return(member);
}
protected void btn_search_Click(object sender, EventArgs e)
{
if (ValidChecker.checkStringValid(inputKeyword.Text) == false)
{
return;
}
Response.Redirect("search.aspx?keyword=" + inputKeyword.Text);
}
public Book inquaryRandomBook(string inputCategory)
{
if (ValidChecker.checkStringValid(inputCategory) == false)
{
return(null);
}
string query = "select * from " + bookTable + " where category='" + inputCategory + "' order by random() limit 1;";
NpgsqlCommand sqlCommand = new NpgsqlCommand(query, this.connection);
NpgsqlDataReader dataReader = null;
try
{
dataReader = sqlCommand.ExecuteReader();
}
catch
{
return(null);
}
Debug.Assert(dataReader != null);
if (!dataReader.HasRows)
{
return(null);
}
int book_number = -1;
string name = null;
string author = null;
string publisher = null;
int cost = -1;
string introduce = null;
string authorIntroduce = null;
string category = null;
while (dataReader.Read())
{
book_number = dataReader.GetInt32(0);
name = dataReader.GetString(1);
author = dataReader.GetString(2);
publisher = dataReader.GetString(3);
cost = dataReader.GetInt32(4);
introduce = dataReader.GetString(5);
authorIntroduce = dataReader.GetString(6);
category = dataReader.GetString(7);
}
dataReader.Close();
Book book = new Book(book_number, name, author, publisher, cost, introduce, authorIntroduce, category);
Debug.Assert(book != null);
return(book);
}
public Member(string id, string password, string subEmail, string phone, string mailNumber, string address, string authenticationQustion, string authenticationAnswer)
{
if (ValidChecker.checkStringValid(id) == false)
{
throw new System.ArgumentException("Member형 객체를 생성하기 위한 id가 유효하지 않습니다. 입니다.", "Member.id");
}
if (ValidChecker.checkStringValid(password) == false)
{
throw new System.ArgumentException("Member형 객체를 생성하기 위한 password가 유효하지 않습니다.", "Member.password");
}
if (ValidChecker.checkStringValid(subEmail) == false)
{
throw new System.ArgumentException("Member형 객체를 생성하기 위한 subEmail이 유효하지 않습니다.", "Member.subEmail");
}
if (ValidChecker.checkStringValid(phone) == false)
{
throw new System.ArgumentException("Member형 객체를 생성하기 위한 phone이 유효하지 않습니다.", "Member.phone");
}
if (ValidChecker.checkStringValid(address) == false)
{
throw new System.ArgumentException("Member형 객체를 생성하기 위한 address가 유효하지 않습니다.", "Member.address");
}
if (ValidChecker.checkStringValid(mailNumber) == false)
{
throw new System.ArgumentException("Member형 객체를 생성하기 위한 mailNumber가 유효하지 않습니다.", "Member.mailNumber");
}
if (ValidChecker.checkStringValid(authenticationQustion) == false)
{
throw new System.ArgumentException(
"Member형 객체를 생성하기 위한 authenticationQustion이 유효하지 않습니다.", "Member.authenticationQustion");
}
if (ValidChecker.checkStringValid(authenticationAnswer) == false)
{
throw new System.ArgumentException(
"Member형 객체를 생성하기 위한 authenticationAnswer이 유효하지 않습니다.", "Member.authenticationAnswer");
}
this.id = id;
this.password = password;
this.subEmail = subEmail;
this.phone = phone;
this.mailNumber = mailNumber;
this.address = address;
this.authenticationQustion = authenticationQustion;
this.authenticationAnswer = authenticationAnswer;
}
public Book(int bookNumber, string name, string author, string publisher, int cost, string introduce, string authorintroduce, string category)
{
if (bookNumber < 0)
{
throw new System.ArgumentException("Book형 객체를 생성하기 위한 bookNumber가 유효하지 않습니다. 입니다.", "Book.bookNumber");
}
if (ValidChecker.checkStringValid(name) == false)
{
throw new System.ArgumentException("Book형 객체를 생성하기 위한 name이 유효하지 않습니다. 입니다.", "Book.name");
}
if (ValidChecker.checkStringValid(author) == false)
{
throw new System.ArgumentException("Book형 객체를 생성하기 위한 author가 유효하지 않습니다. 입니다.", "Book.author");
}
if (ValidChecker.checkStringValid(publisher) == false)
{
throw new System.ArgumentException("Book형 객체를 생성하기 위한 publisher가 유효하지 않습니다. 입니다.", "Book.publisher");
}
if (cost < 0)
{
throw new System.ArgumentException("Book형 객체를 생성하기 위한 cost가 유효하지 않습니다. 입니다.", "Book.cost");
}
if (ValidChecker.checkStringValid(introduce) == false)
{
throw new System.ArgumentException("Book형 객체를 생성하기 위한 nameintroduce가 유효하지 않습니다. 입니다.", "Book.introduce");
}
if (ValidChecker.checkStringValid(authorintroduce) == false)
{
throw new System.ArgumentException("Book형 객체를 생성하기 위한 authorintroduce가 유효하지 않습니다. 입니다.", "Book.authorintroduce");
}
if (ValidChecker.checkStringValid(category) == false)
{
throw new System.ArgumentException("Book형 객체를 생성하기 위한 category가 유효하지 않습니다. 입니다.", "Book.category");
}
this.bookNumber = bookNumber;
this.name = name;
this.author = author;
this.publisher = publisher;
this.cost = cost;
this.introduce = introduce;
this.authorIntroduce = authorintroduce;
this.category = category;
}
public DataSet inquiryOrder(string memberId)
{
if (ValidChecker.checkStringValid(memberId) == false)
{
return(null);
}
string query = "select * from " + bookOrderTable + " where memberid='" + memberId + "';";
NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter();
dataAdapter.SelectCommand = new NpgsqlCommand(query, this.connection);
DataSet dataSet = new DataSet("ds1");
dataAdapter.Fill(dataSet, "ds1");
return(dataSet);
}
public bool insertTemporaryOrderItem(string memberId, string bookName, int bookCost, int bookQuantity)
{
if (ValidChecker.checkStringValid(memberId) == false)
{
return(false);
}
if (ValidChecker.checkStringValid(bookName) == false)
{
return(false);
}
if (bookCost < 0)
{
return(false);
}
if (bookQuantity <= 0)
{
return(false);
}
string cost = bookCost.ToString();
string quantity = bookQuantity.ToString();
string query = "insert into " + temporaryOrderItemTable + " (memberid, book_name, cost, quantity)" +
"values(@MEMBER_ID, @BOOK_NAME, @COST, @QUANTITY);";
NpgsqlCommand sqlCommand = new NpgsqlCommand(query, this.connection);
sqlCommand.Parameters.AddWithValue("@MEMBER_ID", memberId);
sqlCommand.Parameters.AddWithValue("@BOOK_NAME", bookName);
sqlCommand.Parameters.AddWithValue("@COST", cost);
sqlCommand.Parameters.AddWithValue("@QUANTITY", quantity);
try
{
sqlCommand.ExecuteNonQuery();
}
catch
{
return(false);
}
return(true);
}
public DataSet searchBook(string bookName)
{
if (ValidChecker.checkStringValid(bookName) == false)
{
return(null);
}
string query = "select * from book where name like'%" + bookName + "%';";
NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter();
dataAdapter.SelectCommand = new NpgsqlCommand(query, this.connection);
DataSet dataSet = new DataSet("ds1");
dataAdapter.Fill(dataSet, "ds1");
return(dataSet);
}
//도서관리
public DataSet inquiryBooks(string category)
{
if (ValidChecker.checkStringValid(category) == false)
{
return(null);
}
string query = "select distinct on(name) * from book where category='" + category + "';";
NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter();
dataAdapter.SelectCommand = new NpgsqlCommand(query, this.connection);
DataSet dataSet = new DataSet("ds1");
dataAdapter.Fill(dataSet, "ds1");
return(dataSet);
}
public bool deleteTemporaryOrderItems(string memberId)
{
if (ValidChecker.checkStringValid(memberId) == false)
{
return(false);
}
string query = "delete from " + temporaryOrderItemTable + " where memberid='" + memberId + "';";
NpgsqlCommand sqlCommand = new NpgsqlCommand(query, this.connection);
try
{
sqlCommand.ExecuteNonQuery();
}
catch
{
return(false);
}
return(true);
}
public Order(string memberId, string orderName, string date, int cost, string state, string receiver, string phone, string email, string mailNumber, string address)
{
Debug.Assert(ValidChecker.checkStringValid(memberId));
Debug.Assert(ValidChecker.checkStringValid(orderName));
Debug.Assert(ValidChecker.checkStringValid(date));
Debug.Assert(cost > 0);
Debug.Assert(ValidChecker.checkStringValid(state));
Debug.Assert(ValidChecker.checkStringValid(receiver));
Debug.Assert(ValidChecker.checkStringValid(phone));
Debug.Assert(ValidChecker.checkStringValid(email));
Debug.Assert(ValidChecker.checkStringValid(mailNumber));
Debug.Assert(ValidChecker.checkStringValid(address));
this.memberId = memberId;
this.orderName = orderName;
this.date = date;
this.cost = cost;
this.state = state;
this.receiver = receiver;
this.phone = phone;
this.email = email;
this.mailNumber = mailNumber;
this.address = address;
}
public bool sendRegisterAuthenticationEmail(string mailRecipient)
{
/* 자체 SMTP서버 사용
* if (ValidChecker.checkStringValid(mailRecipient) == false) return false;
*
* MailMessage mailMessage = new MailMessage();
* mailMessage.From = new MailAddress(this.senderAddress, this.senderName, System.Text.Encoding.Default);
* mailMessage.Subject = "BookStore 회원인증메일";
* mailMessage.IsBodyHtml = false;
* //해쉬함수로 계정활성화코드(ActivationCode) 생성
* byte[] tmpSource = ASCIIEncoding.ASCII.GetBytes(mailRecipient);
* byte[] tmpHash = new MD5CryptoServiceProvider().ComputeHash(tmpSource);
* //생성된 해쉬값을 string타입으로 변환
* StringBuilder sOutput = new StringBuilder(tmpHash.Length);
* for (int i = 0; i < tmpHash.Length - 1; i++)
* {
* sOutput.Append(tmpHash[i].ToString("X2"));
* }
* string activationCode = sOutput.ToString();
* mailMessage.Body = ""+
* "<!DOCTYPE html>"+
* "<html><body><p>BookStore의 계정을 인증하려면 계정인증버튼을 눌러주세요.</p>"+
* "<form method=\"post\" action=\"http://218.156.146.189/view/idAuthentication.aspx\">"+
* "<input type=\"hidden\" name=\"memberId\" value=\""+mailRecipient+"\">"+
* "<input type=\"hidden\" name=\"activationCode\" value=\""+activationCode+"\">"+
* "<input type=\"submit\" value=\"계정인증\"/></form></body></html>";
* mailMessage.SubjectEncoding = System.Text.Encoding.Default;
* mailMessage.BodyEncoding = System.Text.Encoding.Default;
* mailMessage.To.Add(mailRecipient);
*
* SmtpClient smtpClient = new SmtpClient(this.smtpClientAddress);
* smtpClient.Send(mailMessage);
*/
if (ValidChecker.checkStringValid(mailRecipient) == false)
{
return(false);
}
MailMessage mailMessage = new MailMessage();
mailMessage.From = new MailAddress(this.senderAddress, this.senderName, System.Text.Encoding.Default);
mailMessage.Subject = "BookStore 회원인증메일";
mailMessage.IsBodyHtml = true;
//해쉬함수로 계정활성화코드(ActivationCode) 생성
byte[] tmpSource = ASCIIEncoding.ASCII.GetBytes(mailRecipient);
byte[] tmpHash = new MD5CryptoServiceProvider().ComputeHash(tmpSource);
//생성된 해쉬값을 string타입으로 변환
StringBuilder sOutput = new StringBuilder(tmpHash.Length);
for (int i = 0; i < tmpHash.Length - 1; i++)
{
sOutput.Append(tmpHash[i].ToString("X2"));
}
string activationCode = sOutput.ToString();
mailMessage.Body = "" +
"<!DOCTYPE html>" +
"<html><body><p>BookStore의 계정을 인증하려면 계정인증버튼을 눌러주세요.</p>" +
"<form method=\"post\" action=\"http://218.156.146.189/view/idAuthentication.aspx\">" +
"<input type=\"hidden\" name=\"memberId\" value=\"" + mailRecipient + "\">" +
"<input type=\"hidden\" name=\"activationCode\" value=\"" + activationCode + "\">" +
"<input type=\"submit\" value=\"계정인증\"/></form></body></html>";
mailMessage.SubjectEncoding = System.Text.Encoding.Default;
mailMessage.BodyEncoding = System.Text.Encoding.Default;
mailMessage.To.Add(mailRecipient);
SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
smtpClient.UseDefaultCredentials = false;
smtpClient.EnableSsl = true;
smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
smtpClient.Credentials = new System.Net.NetworkCredential("mia22rmrjs9", "gldpflrmrjs9");
smtpClient.Send(mailMessage);
return(true);
}
