//post api/values
//动态加组图（动态ID）or 个人信息修改加头图（用户ID）
public static bool Post(string id, int type)
{
bool flag = true;
//打开数据库
OracleConnection conn = new OracleConnection(DBAccess.connStr);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
//浏览器上传文件的body
var SrcRequest = HttpContext.Current.Request;
string filepath = "";
//逐一存下body中给出的文件集合
foreach (string f in SrcRequest.Files.AllKeys)
{
HttpPostedFile file = SrcRequest.Files[f];
//分割后缀
string fileExt = Path.GetExtension(file.FileName);
if (type == 1)
{
//插入PICTURE一条记录
GeneralAPI g = new GeneralAPI();
string pid = g.NewIDOf("PICTURE");
//文件路径不重名
filepath = @"C:\mmps\" + pid + fileExt;
cmd.CommandText = "insert into PICTURE(ID,URL,MOMENT_ID) values('" + pid + "','" + filepath + "','" + id + "')";
if (cmd.ExecuteNonQuery() != 1)
{
flag = false;
}
}
else if (type == 2)
{
//更新用户个人头像
filepath = @"C:\heads\" + id + fileExt;
cmd.CommandText = "update USERS set PHOTO='" + filepath + "' where ID='" + id + "'";
if (cmd.ExecuteNonQuery() != 1)
{
flag = false;
}
}
//存本地
if (File.Exists(filepath))
{
File.Delete(filepath);
}
file.SaveAs(filepath);
}
return(flag);
}
public IHttpActionResult GetRankingMoments(string email)
{
//todo:连接数据库
DBAccess dBAccess = new DBAccess();
//执行数据库select操作
OracleDataReader rd = dBAccess.GetDataReader("select* from (select m.ID, m.content, m.like_num, m.forward_num, m.collect_num, m.comment_num, m.time, u.id sender_id, u.username, u.email, u.bio, u.photo, ROWNUM rn from MOMENT m,USERS u where m.sender_id = u.ID order by m.like_num desc) where rn<=20");
//创建User_Moment对象List，并向其中添加读出的数据库信息
List <User_Moment> resultList = new List <User_Moment>();
//创建api对象
GeneralAPI api = new GeneralAPI();
while (rd.Read())//当数据库能读出一条符合条件的元组，执行循环
{
User_Moment um = new User_Moment();
um.SenderID = rd["SENDER_ID"].ToString();
um.MomentID = rd["ID"].ToString();
um.Username = rd["USERNAME"].ToString();
um.Email = rd["EMAIL"].ToString();
um.Bio = rd["BIO"].ToString();
um.Photo = rd["PHOTO"].ToString();
um.Content = rd["CONTENT"].ToString();
um.LikeNum = int.Parse(rd["LIKE_NUM"].ToString());
um.ForwardNum = int.Parse(rd["FORWARD_NUM"].ToString());
um.CollectNum = int.Parse(rd["COLLECT_NUM"].ToString());
um.CommentNum = int.Parse(rd["COMMENT_NUM"].ToString());
um.Time = rd["TIME"].ToString();
//获取该用户的点赞状态
int likeState = api.CheckLikeState(email, um.MomentID);
if (likeState == 1)//未点赞过
{
um.LikeState = "false";
}
else if (likeState == 0)//点赞过
{
um.LikeState = "true";
}
else
{
um.LikeState = "error";
}
resultList.Add(um);
}
//以json格式返回数组
return(Json <List <User_Moment> >(resultList));
}
public IHttpActionResult ModifyMoment([FromBody] Modify_Moment modifyMoment)
{
int status = 0;
//todo:连接数据库
DBAccess dBAccess = new DBAccess();
//创建api对象
GeneralAPI api = new GeneralAPI();
//获取用户ID
string user_id = api.EmailToUserID(modifyMoment.email);
if (user_id == null)
{
status = 1;//不存在该用户
}
//执行数据库操作
OracleDataReader rd = dBAccess.GetDataReader("select * from MOMENT where ID='" + modifyMoment.moment_id + "'and SENDER_ID = '" + user_id + "'");
if (rd.Read())
{
string currentTime = DateTime.Now.ToString("yyyyMMddhhmmss");
if (dBAccess.ExecuteSql("update MOMENT set content = '" + modifyMoment.content + "',time = TO_TIMESTAMP ('" + currentTime + "','yyyy-mm-dd hh24:mi:ss.ff') where sender_id = '" + user_id + "'and id = '" + modifyMoment.moment_id + "'"))
{
status = 0;//修改成功
}
else
{
status = 3;//修改失败
}
}
else
{
status = 2;//该用户没有发表过该动态
}
return(Ok(status));
}
public IHttpActionResult GetLikeList(string email, string moment_id)
{
//todo:连接数据库
DBAccess dBAccess = new DBAccess();
//执行数据库select操作
OracleDataReader rd = dBAccess.GetDataReader(" select u.* from FAVORITE f, USERS u where f.moment_id = '" + moment_id + "' and f.user_id = u.id ");
//创建Users对象List，并向其中添加读出的数据库信息
List <User_Follow> resultList = new List <User_Follow>();
//创建api对象
GeneralAPI api = new GeneralAPI();
while (rd.Read()) //当数据库能读出一条符合条件的元组，执行循环
{
User_Follow uf = new User_Follow();
uf.ID = rd["ID"].ToString();
uf.Username = rd["USERNAME"].ToString();
uf.Photo = rd["PHOTO"].ToString();
uf.Email = rd["EMAIL"].ToString();
uf.Bio = rd["BIO"].ToString();
string user_id = api.EmailToUserID(email);
int follow_state = api.CheckFollowState(user_id, rd["ID"].ToString());
if (follow_state == 0)//关注
{
uf.FollowState = "true";
}
else//未关注
{
uf.FollowState = "false";
}
resultList.Add(uf);
}
return(Json <List <User_Follow> >(resultList));//返回符合条件的Users列表
}
public HttpResponseMessage SvCmt()
{
bool success = true;
HttpResponseMessage response = new HttpResponseMessage();
var coment = HttpContext.Current.Request.Params;
Comment comment = new Comment();
//永久保存数据
comment.Mid = coment["Mid"];
comment.Sender_id = coment["Sender_id"];
comment.Content = coment["Content"];
//comment.Send_time = coment["Send_time"];
comment.Send_time = DateTime.Now.ToString();
comment.Quote_id = coment["Quote_id"];
//打开数据库连接
OracleConnection conn = new OracleConnection(DBAccess.connStr);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
//保存评论体
GeneralAPI g = new GeneralAPI();
comment.Cid = g.NewIDOf("COMENT");
if (comment.Quote_id == null || comment.Quote_id.Trim().Equals("")) //搞定评论类型
{
comment.Type = "0";
}
else
{
comment.Type = "1";
}
cmd.CommandText = "insert into COMENT(ID,CONTENT,SEND_TIME,QUOTE_ID,TYPE) values('" + comment.Cid + "','" + comment.Content + "',TO_DATE('" + comment.Send_time + "', 'yyyy-mm-dd hh24:mi:ss'),'" + comment.Quote_id + "','" + comment.Type + "')";
int executeResult = cmd.ExecuteNonQuery();
if (executeResult != 1)
{
success = false;
}
//评论动态关系表插入记录
cmd.CommandText = "insert into PUBLISH_COMMENT(USER_ID,COMMENT_ID,MOMENT_ID) values('" + comment.Sender_id + "','" + comment.Cid + "','" + comment.Mid + "')";
executeResult = cmd.ExecuteNonQuery();
if (executeResult != 1)
{
success = false;
}
/* *
* 评论数触发器级联修改动态Comment_Num
* hh +2 DEBUG
* */
if (success != true)
{
response.StatusCode = HttpStatusCode.InternalServerError; //404
}
return(response);
}
public Tuple <List <Moment>, int, bool, List <string>, List <int> > Followers(int Page, int PageSize, string TagContent, string Email)
{
bool FollowState = false;
string sql = Access.Select("ID", "USERS", "EMAIL='" + Email + "'").ToString();
OracleDataReader r = Access.GetDataReader(sql);
r.Read();
string UserId = r["ID"].ToString();
if (Access.GetRecordCount(Access.Select("*", "FOLLOW_TAG", "USER_ID='" + UserId + "' and TAG='" + TagContent + "'")) == 0)
{
FollowState = false; //用户未关注
}
else
{
FollowState = true; //用户已关注
}
//动态ID的数据集
DataSet MIDSet = new DataSet();
//动态的数据集
//DataSet MSet = new DataSet();
string select = Access.Select("MOMENT_ID", "MOMENT_TAG", "TAG = '" + TagContent + "'");
MIDSet = Access.GetDataSet(select, "MOMENT_TAG", PageSize, Page);
//动态的数组
List <Moment> moments = new List <Moment>();
//user的数组
List <string> users = new List <string>();
//点赞状态的数组
List <int> states = new List <int>();
foreach (DataRow row in MIDSet.Tables[0].Rows)
{
select = Access.Select("*", "MOMENT", "ID = '" + row[0] + "'");
OracleDataReader rd = Access.GetDataReader(select);
while (rd.Read())
{
string id = rd["ID"].ToString();
string sender_id = rd["SENDER_ID"].ToString();
string content = rd["CONTENT"].ToString();
int likes = int.Parse(rd["LIKE_NUM"].ToString());
int forwards = int.Parse(rd["FORWARD_NUM"].ToString());
int collects = int.Parse(rd["COLLECT_NUM"].ToString());
int comments = int.Parse(rd["COMMENT_NUM"].ToString());
string time = rd["TIME"].ToString().Replace('T', ' ');
DBAccess db = new DBAccess();
string SQL = db.Select("USERNAME", "USERS", "ID='" + sender_id + "'");
OracleDataReader R = db.GetDataReader(SQL);
while (R.Read())
{
users.Add(R["USERNAME"].ToString());
}
GeneralAPI A = new GeneralAPI();
states.Add(A.CheckLikeState(Email, id));
moments.Add(new Moment(id, sender_id, content, likes, forwards, collects, comments, time));
}
}
select = Access.Select("*", "FOLLOW_TAG", "TAG = '" + TagContent + "'");
int Flowers = Access.GetRecordCount(select);
Tuple <List <Moment>, int, bool, List <string>, List <int> > result = new Tuple <List <Moment>, int, bool, List <string>, List <int> >(null, 0, false, null, null);
if (moments.Count != 0)
{
result = new Tuple <List <Moment>, int, bool, List <string>, List <int> >(moments, Flowers, FollowState, users, states);
}
else if (moments.Count == 0)
{
result = new Tuple <List <Moment>, int, bool, List <string>, List <int> >(null, 0, FollowState, null, null);
}
return(result);
}
public IHttpActionResult DeleteMoment(string email, string moment_id)
{
//删除所有评论
CmtApi.DelCmt(moment_id);
int status = 0;
//todo:连接数据库
DBAccess dBAccess = new DBAccess();
//创建api对象
GeneralAPI api = new GeneralAPI();
//获取用户ID
string user_id = api.EmailToUserID(email);
if (user_id == null)
{
status = 1;//不存在该用户
}
//执行数据库操作
OracleDataReader rd = dBAccess.GetDataReader("select * from MOMENT where ID='" + moment_id + "'and SENDER_ID = '" + user_id + "'");
if (rd.Read())
{
//获取该动态的quote_mid
string quote_mid = rd["QUOTE_MID"].ToString();
if (dBAccess.ExecuteSql("delete from MOMENT where sender_id = '" + user_id + "'and id = '" + moment_id + "'"))
{
status = 0;//成功删除
}
else
{
status = 3; //动态删除失败
}
if (quote_mid != "") //该条动态来自转发，同时修改forward表
{
rd = dBAccess.GetDataReader("select * from MOMENT where ID='" + quote_mid + "'");
if (rd.Read())
{
//设置新的转发数
int new_forward_num = int.Parse(rd["FORWARD_NUM"].ToString()) - 1;
if (dBAccess.ExecuteSql("delete from FORWARD where user_id = '" + user_id + "'and moment_id = '" + quote_mid + "'"))
{
if (dBAccess.ExecuteSql("update MOMENT set forward_num= ' " + new_forward_num + " 'where ID='" + quote_mid + "' "))
{
status = 0;//成功删除该动态和转发表项，并且源动态转发数减一
}
else
{
status = 6;//成功删除该动态和转发表项，但源动态转发数没有更改
}
}
else
{
status = 5;//转发表项删除失败
}
}
else
{
status = 4;//该动态删除成功，找不到源动态
}
}
}
else
{
status = 2;//该用户没有发表过该动态
}
return(Ok(status));
}
public IHttpActionResult FollowState(string from_id, string to_id)
{
GeneralAPI a = new GeneralAPI();
return(Ok(a.CheckFollowState(from_id, to_id)));
}
public IHttpActionResult UpdateLiking(string email, string moment_id)
{
int status = 0;
int likeState = -1;
//todo:连接数据库
DBAccess dBAccess = new DBAccess();
//创建api对象
GeneralAPI api = new GeneralAPI();
//记录该用户是否点赞该动态
likeState = api.CheckLikeState(email, moment_id);
//执行数据库操作
OracleDataReader rd = dBAccess.GetDataReader("select * from MOMENT where ID='" + moment_id + "'");
if (rd.Read())
{
//获取该动态的quote_mid
string quote_mid = rd["QUOTE_MID"].ToString();
//设置新的点赞数
int new_like_num;
if (likeState == 0)//取消点赞，赞数减一
{
new_like_num = int.Parse(rd["LIKE_NUM"].ToString()) - 1;
}
else if (likeState == 1)//点赞，赞数加一
{
new_like_num = int.Parse(rd["LIKE_NUM"].ToString()) + 1;
}
else//用户不存在
{
status = 4;
return(Ok(status));
}
//获取用户id
string user_id = api.EmailToUserID(email);
//更新数据库中的点赞数
if (dBAccess.ExecuteSql("update MOMENT set like_num= ' " + new_like_num + " 'where ID='" + moment_id + "' "))
{
if (likeState == 1)//点赞
{
if (dBAccess.ExecuteSql("insert into FAVORITE(USER_ID,MOMENT_ID) values('" + user_id + "','" + moment_id + "')"))
{
status = 0;//该动态moment表和favorite表都修改成功
}
else
{
status = 1;//该动态moment表修改成功，favorite表修改失败
}
}
else if (likeState == 0)//取消点赞
{
if (dBAccess.ExecuteSql("delete from FAVORITE where user_id = '" + user_id + "' and moment_id = '" + moment_id + "'"))
{
status = 0;//该动态moment表和favorite表都修改成功
}
else
{
status = 1;//该动态moment表修改成功，favorite表修改失败
}
}
}
else//该动态moment表修改失败，未修改favorite表
{
status = 2;
}
if (quote_mid != "")//该条动态来自转发，同时修改源动态点赞数
{
rd = dBAccess.GetDataReader("select * from MOMENT where ID='" + quote_mid + "'");
int likeState2 = api.CheckLikeState(email, quote_mid);
if (rd.Read())
{
if (likeState == 0 && likeState2 == 0)//取消点赞，赞数减一
{
new_like_num = int.Parse(rd["LIKE_NUM"].ToString()) - 1;
}
else if ((likeState == 0 && likeState2 == 1) || (likeState == 1 && likeState2 == 0))//当前动态点赞，源动态已点赞过，或当前动态取消点赞，源动态未点赞，源动态赞数不变
{
new_like_num = int.Parse(rd["LIKE_NUM"].ToString());
status = 0;
return(Ok(status));
}
else if (likeState == 1 && likeState2 == 1)//点赞，赞数加一
{
new_like_num = int.Parse(rd["LIKE_NUM"].ToString()) + 1;
}
//更新数据库中源动态的点赞数
if (dBAccess.ExecuteSql("update MOMENT set like_num= ' " + new_like_num + " 'where ID='" + quote_mid + "' "))
{
if (likeState2 == 1)//点赞
{
if (dBAccess.ExecuteSql("insert into FAVORITE(USER_ID,MOMENT_ID) values('" + user_id + "','" + quote_mid + "')"))
{
status = 0;//源动态moment表和favorite表都修改成功，返回成功状态码0
}
else
{
status = 6;//源动态moment表修改成功，favorite表修改失败
}
}
else if (likeState2 == 0)//取消点赞
{
if (dBAccess.ExecuteSql("delete from FAVORITE where user_id = '" + user_id + "' and moment_id = '" + quote_mid + "'"))
{
status = 0;//源动态moment表和favorite表都修改成功，返回成功状态码0
}
else
{
status = 6;//源动态moment表修改成功，favorite表修改失败
}
}
}
else//源动态moment表修改失败，未修改favorite表
{
status = 5;
}
}
else
{
status = 7;//找不到源动态
}
}
}
else//找不到该动态
{
status = 3;
}
//返回状态码
return(Ok(status));
}
private void dclock_Tick(object sender, EventArgs e)
{
GeneralAPI.appendUsername(playerName);
this.ltime.Text = DateTime.Now.ToString("hh:mm tt");
}
public IHttpActionResult DeleteMoment(string email, string moment_id)
{
//设置初始状态码
int status = 0;
DBAccess dBAccess = new DBAccess();
GeneralAPI api = new GeneralAPI();
//获取当前用户ID
string user_id = api.EmailToUserID(email);
if (user_id == null)
{
status = 1;//不存在该用户
}
//获取本条动态信息
OracleDataReader rd = dBAccess.GetDataReader("select * from MOMENT where ID='" + moment_id + "'and SENDER_ID = '" + user_id + "'");
if (rd.Read())
{
//获取该动态的quote_mid
string quote_mid = rd["QUOTE_MID"].ToString();
//
//判断本条动态是否来自转发
//
if (quote_mid != "")
{
rd = dBAccess.GetDataReader("select * from MOMENT where ID='" + quote_mid + "'");
if (rd.Read())
{
//设置新的转发数
int new_forward_num = int.Parse(rd["FORWARD_NUM"].ToString()) - 1;
//删除本条动态的所有评论
CmtApi.DelCmt(moment_id);
//删除本条动态
if (dBAccess.ExecuteSql("delete from MOMENT where sender_id = '" + user_id + "'and id = '" + moment_id + "'"))
{
status = 0;//成功删除
}
else
{
status = 3;//动态删除失败
}
if (dBAccess.ExecuteSql("delete from FORWARD where user_id = '" + user_id + "'and moment_id = '" + quote_mid + "'"))
{
if (dBAccess.ExecuteSql("update MOMENT set forward_num= ' " + new_forward_num + " 'where ID='" + quote_mid + "' "))
{
status = 0;//成功删除该动态和转发表项，并且源动态转发数减一
}
else
{
status = 6;//成功删除该动态和转发表项，但源动态转发数没有更改
}
}
else
{
status = 5;//转发表项删除失败
}
}
else
{
status = 4;//该动态删除成功，找不到源动态
}
}
//
//本条动态为源动态
//
else
{
//获取转发了本条动态的动态id
DataSet ds = dBAccess.GetDataSet("select ID from MOMENT where QUOTE_MID='" + moment_id + "'", "unknown");
List <string> list = new List <string>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
list.Add(dr[0].ToString());
}
bool forward = true;
//判断是否有人转发本动态
if (list.Count.Equals(0))
{
forward = false;
}
if (forward) //有人转发本条动态，需要删除所有转发动态
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
string mid = dr[0].ToString();
CmtApi.DelCmt(mid); //删除对应动态的全部评论
dBAccess.ExecuteSql("delete from MOMENT where id = '" + mid + "'"); //从Moment表删除全部转发自本动态的动态
if (dBAccess.ExecuteSql("delete from FORWARD where moment_id = '" + moment_id + "'")) //从forward表删除全部转发表项
{
status = 0;
}
else
{
status = 7;
}
//删除本条动态的所有评论
CmtApi.DelCmt(moment_id);
}
}
//删除本条动态
if (dBAccess.ExecuteSql("delete from MOMENT where id = '" + moment_id + "'"))
{
status = 0;//成功删除
}
else
{
status = 3;//动态删除失败
}
}
}
else
{
status = 2;//该用户没有发表过该动态
}
return(Ok(status));
}
public IHttpActionResult ForwardMoment([FromBody] Forward forward)
{
//创建返回信息，先假设转发成功
int status = 0;
OracleConnection conn = new OracleConnection(DBAccess.connStr);
try
{
conn.Open();
}
catch (Exception ex)
{
throw (ex);
}
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from moment " +
"where sender_id='" + forward.User_ID + "' and id='" + forward.Moment_ID + "'";
OracleDataReader rd = cmd.ExecuteReader();
if (rd.HasRows)
{
status = 1;//转发了自己的
return(Ok(status));
}
cmd.CommandText = "select * from forward where forward.user_id='" + forward.User_ID + "' and moment_id='" + forward.Moment_ID + "'";
rd = cmd.ExecuteReader();
if (rd.HasRows)
{
status = 3;//已经转发过
return(Ok(status));
}
cmd.CommandText = "insert into FORWARD(USER_ID,MOMENT_ID) " +
"values('" + forward.User_ID + "','" + forward.Moment_ID + "')";
int result = cmd.ExecuteNonQuery();
if (result != 1)//插入出现错误
{
status = 2;
return(Ok(status));
}
//插入新动态
cmd.CommandText = "select * from moment " +
"where id='" + forward.Moment_ID + "'";
rd = cmd.ExecuteReader();
if (!rd.Read())
{
cmd.CommandText = "delete from forward where forward.user_id='" + forward.User_ID + "' and moment_id='" + forward.Moment_ID + "'";
rd = cmd.ExecuteReader();
status = 2;
return(Ok(status));
}
Moment moment = new Moment();
GeneralAPI api = new GeneralAPI();
moment.ID = api.NewIDOf("moment");
moment.SenderID = forward.User_ID;
moment.Content = rd["CONTENT"].ToString();
moment.LikeNum = 0;
int forwardNum = Convert.ToInt32(rd["FORWARD_NUM"]);
moment.ForwardNum = 0;
moment.CollectNum = 0;
moment.CommentNum = 0;
moment.Time = DateTime.Now.ToString();
cmd.CommandText = "select quote_mid" +
"from moment" +
"where id='" + forward.MOMENT_ID + "'";
rd = cmd.ExecuteReader();
rd.Read();
string quote = rd["QUOTE_MID"].ToString();
if (quote == null)
{
moment.QuoteMID = forward.MOMENT_ID;
}
else
{
moment.QuoteMID = quote;
}
cmd.CommandText = "insert into MOMENT(ID,SENDER_ID,CONTENT,LIKE_NUM,FORWARD_NUM,COLLECT_NUM,COMMENT_NUM,TIME,QUOTE_MID) " +
"values('" + moment.ID + "','" + moment.SenderID + "','" + moment.Content + "','" + moment.LikeNum + "'," +
"'" + moment.ForwardNum + "','" + moment.CollectNum + "','" + moment.CommentNum + "',TO_DATE('" + moment.Time + "', 'yyyy-mm-dd hh24:mi:ss'),'" + moment.QuoteMID + "')";
int result1 = cmd.ExecuteNonQuery();
if (result1 != 1)//插入出现错误
{
cmd.CommandText = "delete from forward where forward.user_id='" + forward.User_ID + "' and moment_id='" + forward.Moment_ID + "'";
rd = cmd.ExecuteReader();
status = 2;
return(Ok(status));
}
//插入成功，更改转发数
forwardNum++;
cmd.CommandText = "update moment " +
"set forward_num= '" + forwardNum +
"' where id='" + forward.Moment_ID + "'";
rd = cmd.ExecuteReader();
//建立图片的联系
cmd.CommandText = "select * from picture " +
"where moment_id='" + forward.Moment_ID + "'";
rd = cmd.ExecuteReader();
while (rd.Read())
{
string p_url = rd["URL"].ToString();
string p_id;
p_id = api.NewIDOf("picture");
cmd.CommandText = "insert into PICTURE(ID,URL,MOMENT_ID) " +
"values('" + p_id + "','" + p_url + "','" + moment.ID + "')";
int result2 = cmd.ExecuteNonQuery();
if (result2 != 1)//插入出现错误
{
status = 2;
return(Ok(status));
}
}
//关闭数据库连接
conn.Close();
//返回信息
return(Ok(status));
}
