public static IEnumerable<Model.Post> Selecionar()
{
using (var c = new OracleConnection(Helper.Oracle.CS))
{
const String cmd = "SELECT P.*, C.DESCRICAO_CATEGORIA FROM BLOG.POST P INNER JOIN BLOG.POST_CATEGORIA PC ON P.CODIGO_POST = PC.CODIGO_POST INNER JOIN BLOG.CATEGORIA C ON C.CODIGO_CATEGORIA = PC.CODIGO_CATEGORIA WHERE C.DATA_EXCLUSAO_CATEGORIA IS NULL AND P.DATA_LIBERACAO_POST <= SYSDATE";
using (var k = new OracleCommand(cmd, c))
{
c.Open();
var dr = k.ExecuteReader();
Model.Post post = null;
while (dr.Read())
{
if (post != null && post.Codigo != Convert.ToInt32(dr[0]))
yield return post;
if (post == null || post.Codigo != Convert.ToInt32(dr[0]))
post = new Model.Post(Convert.ToInt32(dr[0]), dr[1].ToString(), dr[2].ToString(), Convert.ToDateTime(dr[3]), Convert.ToDateTime(dr[4]), Convert.ToInt32(dr[5]), Convert.ToChar(dr[6]), new List<Model.Categoria>());
post.Categorias.Add(new Model.Categoria(descricao: dr[7].ToString()));
}
if (post != null)
yield return post;
c.Close();
}
}
}
public static void Inserir(Model.Post obj)
{
using (var c = new OracleConnection(Helper.Oracle.CS))
{
const String cmd = "INSERT INTO BLOG.POST (TITULO_POST, CONTEUDO_POST, DATA_INCLUSAO_POST, DATA_LIBERACAO_POST, CODIGO_USUARIO, PRIVADO_POST) VALUES (:TITULO_POST, :CONTEUDO_POST, :DATA_INCLUSAO_POST, :DATA_LIBERACAO_POST, :CODIGO_USUARIO, :PRIVADO_POST)";
using (var k = new OracleCommand(cmd, c))
{
k.Parameters.Add(":TITULO_POST", obj.Titulo);
k.Parameters.Add(":CONTEUDO_POST", obj.Conteudo);
k.Parameters.Add(":DATA_INCLUSAO_POST", DateTime.Now);
k.Parameters.Add(":DATA_LIBERACAO_POST", obj.DataLiberacao);
k.Parameters.Add(":CODIGO_USUARIO", obj.CodigoUsuario);
k.Parameters.Add(":PRIVADO_POST", obj.Privado);
c.Open();
k.ExecuteNonQuery();
var codigo = ObterCodigoPost(c);
foreach (var item in obj.Categorias)
{
InserirPostCategoria(codigo, item.Codigo, c);
}
c.Close();
}
}
}
/// <summary>
/// 更新岗位
/// </summary>
/// <param name="model">岗位实体类</param>
/// <returns></returns>
public bool UpdateDALPOST(Model.Post model)
{
bool bl = true;
StringBuilder strSql = new StringBuilder();
strSql.Append("update POST set ");
strSql.Append("POSTNAME=:POSTNAME,");
strSql.Append("SANXIANG=:SANXIANG, ");
strSql.Append("PTYPE=:PTYPE ");
strSql.Append(" where WORKTASKID=:WORKTASKID ");
OracleParameter[] parameters =
{
new OracleParameter(":POSTNAME", OracleType.NVarChar, 40),
new OracleParameter(":SANXIANG", OracleType.NVarChar, 4),
new OracleParameter(":PTYPE", OracleType.NVarChar, 20),
new OracleParameter(":POSTID", OracleType.Number, 9),
};
parameters[0].Value = model.POSTNAME;
parameters[1].Value = model.SANXIANG;
parameters[2].Value = model.PTYPE;
parameters[3].Value = model.POSTID;
if (OracleHelper.ExecuteSql(strSql.ToString(), parameters) >= 1)
{
bl = true;
}
else
{
bl = false;
}
return(bl);
}
/// <summary>
/// 增加一个岗位
/// </summary>
/// <param name="model">岗位实体类</param>
/// <returns></returns>
public bool CreateDALPOST(Model.Post model)
{
int zy = int.Parse(System.Web.HttpContext.Current.Session["zyID"].ToString());
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into POST(");
strSql.Append("POSTNAME,ZYID,SANXIANG,PTYPE,STATUS)");
strSql.Append(" values(");
strSql.Append(":POSTNAME,:ZYID,:SANXIANG,:PTYPE,:STATUS)");
OracleParameter[] parameters =
{
new OracleParameter(":POSTNAME", OracleType.NVarChar, 40),
new OracleParameter(":ZYID", OracleType.Number, 9),
new OracleParameter(":SANXIANG", OracleType.NVarChar, 4),
new OracleParameter(":PTYPE", OracleType.NVarChar, 20),
new OracleParameter(":STATUS", OracleType.Char, 10)
};
parameters[0].Value = model.POSTNAME;
parameters[1].Value = zy;
parameters[2].Value = model.SANXIANG;
parameters[3].Value = model.PTYPE;
parameters[4].Value = "有效";
if (OracleHelper.ExecuteSql(strSql.ToString(), parameters) >= 1)
{
return(true);
}
else
{
return(false);
}
}
public PostVM(Model.Post post, int rank)
{
Title = post.Title;
Uri = post.Url;
Author = post.By;
Points = post.Score;
Comments = post.Kids.Count();
Rank = rank;
}
public async Task <string> ToeicQues(string content, string idmessenger)
{
ChatfuelJson result = new ChatfuelJson();
MessJson messPron = new MessJson();
MessJson messPron2 = new MessJson();
var userMakeQues = _service.listUserID().Where(x => x.Id_Messenger == idmessenger).ToList();
if (userMakeQues.Count != 0)
{
Post newPost = new Model.Post();
newPost.Content = content;
newPost.DatePost = DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalMilliseconds.ToString();
newPost.Id_User = userMakeQues.FirstOrDefault().Id;
newPost.Id_Type = 8; // TOIEC
//Send lên nhóm fb
{
//post to fb toiec
//Gửi cho admin trước khi dc duyệt
//
//var IdPost = await _fbService.PostingToGroupFB(newPost.Content);
// newPost.Id_PostFB = IdPost.id;
}
_postService.Add(newPost);
_postService.Save();
await NotifyForAdminToApprove(newPost.Id);
//Send notify
List <ApplicationUser> userSubQues = getNormalUserBasedOnType(newPost);
foreach (var itemUser in userSubQues)
{
await sendNofityToMessenger(newPost, itemUser);
HaveSendQuestion haveSendQues = new HaveSendQuestion();
haveSendQues.QuesID = newPost.Id;
haveSendQues.UserID = itemUser.Id;
haveSendQues.Status = false;
haveSendQues.Protected = false;
_haveSendQuesService.Add(haveSendQues);
_haveSendQuesService.Save();
}
messPron.text = "Câu hỏi của bạn đã được gửi đi cho mọi người !";
result.messages.Add(messPron);
// messPron2.text = "https://www.facebook.com/" + newPost.Id_PostFB;
//result.messages.Add(messPron2);
return(JsonConvert.SerializeObject(result));
}
else
{
messPron.text = "Hình như bạn chưa có tài khoản trên Olympus English";
result.messages.Add(messPron);
return(JsonConvert.SerializeObject(result));
}
}
public static WcfServiceLib.Model.Post ToServiceModel(this ClassLib.ModelLayer.Post post)
{
WcfServiceLib.Model.Post servicePost = new Model.Post();
servicePost.Id = post.id;
servicePost.Type = post.type;
servicePost.Details = post.details;
servicePost.Price = post.price;
servicePost.PhoneNr = post.phoneNr;
servicePost.Address = post.address;
servicePost.PostalCode = post.postalCode;
servicePost.Expiration = post.expiration;
servicePost.IsReserved = post.isReserved;
return servicePost;
}
public async Task <string> createNewPostViaFB(string messID, string containQues, int typeQues)
{
//Determine user
var userMakeQues = _service.listUserID().Where(x => x.Id_Messenger == messID).ToList();
if (userMakeQues.Count != 0)
{
Post newPost = new Model.Post();
newPost.Content = containQues;
newPost.DatePost = DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalMilliseconds.ToString();
newPost.Id_User = userMakeQues.FirstOrDefault().Id;
newPost.Id_Type = typeQues; // TOIEC
//Post to fb group
if (newPost.Id_Type == 8)
{
try
{
//post to fb toiec
var IdPost = await _fbService.PostingToGroupFB(newPost.Content);
newPost.Id_PostFB = IdPost.id;
}
catch (Exception e)
{
}
}
_postService.Add(newPost);
_postService.Save();
//Send noti for all user register
var userIDList = getUserIDListBasedOnType(typeQues);
foreach (var userID in userIDList)
{
var sendToUser = _service.GetUserById(userID);
if (sendToUser.Id_Messenger != messID && sendToUser.Id_Messenger != null && _eventService.IsFreeTime(sendToUser.Email))
{
//Create json send............
FBPostNoti newNoti = new FBPostNoti();
newNoti.recipient.id = sendToUser.Id_Messenger;
newNoti.message.attachment.payload.text = "```\r\n" + "💬 Bạn có một câu hỏi: " + "\r\n" + '"' + newPost.Content + '"' + "\r\n```";
NotiButton button = new NotiButton();
button.payload = "REPLAY_" + newPost.Id;
newNoti.message.attachment.payload.buttons.Add(button);
//
ChatBotMessenger.sendRequest(JsonConvert.SerializeObject(newNoti));
}
}
}
return("");
}
public Model.Post CreatePost(Model.User requestor, Model.Post post)
{
if (requestor == null)
{
throw new ArgumentNullException("requestor");
}
if (post == null)
{
throw new ArgumentNullException("post");
}
post.AuthorId = requestor.UserId;
post.DateAdded = DateTimeOffset.Now;
this._postRepository.Add(post);
return(post);
}
public void UpdatePost(Model.User requestor, Model.Post post)
{
if (requestor == null)
{
throw new ArgumentNullException("requestor");
}
if (post == null)
{
throw new ArgumentNullException("post");
}
if (requestor.UserId != post.AuthorId)
{
throw new AuthorizationException("Only the author of a post can modify it.");
}
post.LastModifierId = requestor.UserId;
post.DateModified = DateTimeOffset.Now;
this._postRepository.Update(post);
}
//make question directly from Messenger
public async Task <string> IeltsQues(string content, string messengerid)
{
ChatfuelJson result = new ChatfuelJson();
MessJson messPron = new MessJson();
var userMakeQues = _service.listUserID().Where(x => x.Id_Messenger == messengerid).ToList();
if (userMakeQues.Count != 0)
{
Post newPost = new Model.Post();
newPost.Content = content;
newPost.DatePost = DateTime.Now.Ticks.ToString();
newPost.Id_User = userMakeQues.FirstOrDefault().Id;
newPost.Id_Type = 9; // TOIEC
_postService.Add(newPost);
_postService.Save();
//Send notify
List <ApplicationUser> userSubQues = getNormalUserBasedOnType(newPost);
foreach (var itemUser in userSubQues)
{
await sendNofityToMessenger(newPost, itemUser);
HaveSendQuestion haveSendQues = new HaveSendQuestion();
haveSendQues.QuesID = newPost.Id;
haveSendQues.UserID = itemUser.Id;
haveSendQues.Status = false;
haveSendQues.Protected = false;
_haveSendQuesService.Add(haveSendQues);
_haveSendQuesService.Save();
}
messPron.text = "Câu hỏi của bạn đã được gửi đi cho mọi người";
result.messages.Add(messPron);
return(JsonConvert.SerializeObject(result));
}
else
{
messPron.text = "Hình như bạn chưa có tài khoản trên Olympus English";
result.messages.Add(messPron);
return(JsonConvert.SerializeObject(result));
}
}
public static IEnumerable <Model.Post> Selecionar()
{
using (var c = new OracleConnection(Helper.Oracle.CS))
{
const String cmd = "SELECT P.*, C.DESCRICAO_CATEGORIA FROM BLOG.POST P INNER JOIN BLOG.POST_CATEGORIA PC ON P.CODIGO_POST = PC.CODIGO_POST INNER JOIN BLOG.CATEGORIA C ON C.CODIGO_CATEGORIA = PC.CODIGO_CATEGORIA WHERE C.DATA_EXCLUSAO_CATEGORIA IS NULL AND P.DATA_LIBERACAO_POST <= SYSDATE";
using (var k = new OracleCommand(cmd, c))
{
c.Open();
var dr = k.ExecuteReader();
Model.Post post = null;
while (dr.Read())
{
if (post != null && post.Codigo != Convert.ToInt32(dr[0]))
{
yield return(post);
}
if (post == null || post.Codigo != Convert.ToInt32(dr[0]))
{
post = new Model.Post(Convert.ToInt32(dr[0]), dr[1].ToString(), dr[2].ToString(), Convert.ToDateTime(dr[3]), Convert.ToDateTime(dr[4]), Convert.ToInt32(dr[5]), Convert.ToChar(dr[6]), new List <Model.Categoria>());
}
post.Categorias.Add(new Model.Categoria(descricao: dr[7].ToString()));
}
if (post != null)
{
yield return(post);
}
c.Close();
}
}
}
/// <summary>
/// 删除/禁用一个岗位
/// </summary>
/// <param name="OperatorID">岗位ID</param>
/// <returns></returns>
public bool DeleteDALPOST(Model.Post model)
{
bool bl = true;
StringBuilder strSql = new StringBuilder();
strSql.Append("delete POST");
strSql.Append(" where POSTID=:POSTID ");
OracleParameter[] parameters =
{
new OracleParameter(":POSTID", OracleType.Number, 9)
};
parameters[0].Value = model.POSTID;
if (OracleHelper.ExecuteSql(strSql.ToString(), parameters) >= 1)
{
bl = true;
}
else
{
bl = false;
}
return(bl);
}
public void DeletePost(Model.User requestor, Model.Post post)
{
post.DateDeleted = DateTimeOffset.Now;
this.UpdatePost(requestor, post);
}
public static void Excluir(Model.Post obj)
{
}
public static void Alterar(Model.Post obj)
{
}