public void GetSingleQueryUpdate(TrArticle item, ref List <SqlCommand> ArraySQLCmd)
{
TrArticle itm = item;
//DALInfo.AssignedInfo(ref itm);
UpdateQuery(itm, ArraySQLCmd);
}
public long Insert(ref TrArticle item)
{
SqlCommand cmd = null;
cmd = new SqlCommand("up_InsertTrArticle");
cmd.Connection = new SqlConnection(DALInfo.ConnectionString);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Category", SqlDbType.VarChar, 10).Value = item.Category ?? (object)DBNull.Value;
cmd.Parameters.Add("@Title", SqlDbType.VarChar, 20).Value = item.Title ?? (object)DBNull.Value;
cmd.Parameters.Add("@Story", SqlDbType.VarChar, 255).Value = item.Story ?? (object)DBNull.Value;
cmd.Parameters.Add("@GPSLocation", SqlDbType.VarChar, 100).Value = item.GPSLocation ?? (object)DBNull.Value;
cmd.Parameters.Add("@GPSLong", SqlDbType.Float).Value = item.GPSLong == null ? (object)DBNull.Value : item.GPSLong;
cmd.Parameters.Add("@GPSLat", SqlDbType.Float).Value = item.GPSLat == null ? (object)DBNull.Value : item.GPSLat;
cmd.Parameters.Add("@cStatus", SqlDbType.VarChar, 1).Value = item.cStatus == null ? (object)DBNull.Value : item.cStatus;
cmd.Parameters.Add("@cCreated", SqlDbType.VarChar, 20).Value = item.cCreated ?? (object)DBNull.Value;
cmd.Parameters.Add("@cLastUpdated", SqlDbType.VarChar, 20).Value = item.cLastUpdated == null ? (object)DBNull.Value : item.cLastUpdated;
cmd.Connection.Open();
object generatedId = cmd.ExecuteScalar();
cmd.Connection.Close();
long articleId = long.Parse(generatedId == null ? "0" : generatedId.ToString());
item.Id = articleId;
return(articleId);
}
public List <TrArticle> MoveDataToCollectionDomain(DataTable dataTable)
{
List <TrArticle> trArticleList = new List <TrArticle>();
for (int i = 0; i <= dataTable.Rows.Count - 1; i++)
{
TrArticle objTrArticle = new TrArticle();
objTrArticle.Id = long.Parse(dataTable.Rows[i]["Id"].ToString().Trim());
objTrArticle.Category = dataTable.Rows[i]["Category"].ToString().Trim();
objTrArticle.Title = dataTable.Rows[i]["Title"].ToString().Trim();
objTrArticle.Story = dataTable.Rows[i]["Story"].ToString();
objTrArticle.GPSLocation = dataTable.Rows[i]["GPSLocation"].ToString().Trim();
objTrArticle.GPSLong = double.Parse(string.IsNullOrEmpty(dataTable.Rows[i]["GPSLong"].ToString()) ? "0" : dataTable.Rows[i]["GPSLong"].ToString());
objTrArticle.GPSLat = double.Parse(string.IsNullOrEmpty(dataTable.Rows[i]["GPSLat"].ToString()) ? "0" : dataTable.Rows[i]["GPSLat"].ToString());
objTrArticle.cStatus = dataTable.Rows[i]["cStatus"].ToString().Trim();
objTrArticle.cCreated = dataTable.Rows[i]["cCreated"].ToString().Trim();
objTrArticle.dCreated = Mapper <DateTime>(dataTable.Rows[i]["dCreated"]);
objTrArticle.cLastUpdated = dataTable.Rows[i]["cLastUpdated"].ToString().Trim();
objTrArticle.dLastUpdated = Mapper <DateTime>(dataTable.Rows[i]["dLastUpdated"]);
objTrArticle.RowState = DataRowState.Unchanged;
trArticleList.Add(objTrArticle);
}
return(trArticleList);
}
public void UpdateQuery(TrArticle item, List <SqlCommand> ArraySQLCmd)
{
SqlCommand cmd = null;
if (item.RowState == DataRowState.Added)
{
//using (SqlConnection sqlCon = new SqlConnection(DALInfo.ConnectionString))
//{
//sqlCon.Open();
cmd = new SqlCommand("up_InsertTrArticle");
//cmd.Connection = sqlCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Category", SqlDbType.VarChar, 10).Value = item.Category ?? (object)DBNull.Value;
cmd.Parameters.Add("@Title", SqlDbType.VarChar, 20).Value = item.Title ?? (object)DBNull.Value;
cmd.Parameters.Add("@Story", SqlDbType.VarChar, 255).Value = item.Story ?? (object)DBNull.Value;
cmd.Parameters.Add("@GPSLocation", SqlDbType.VarChar, 100).Value = item.GPSLocation ?? (object)DBNull.Value;
cmd.Parameters.Add("@GPSLong", SqlDbType.Float).Value = item.GPSLong == null ? (object)DBNull.Value : item.GPSLong;
cmd.Parameters.Add("@GPSLat", SqlDbType.Float).Value = item.GPSLat == null ? (object)DBNull.Value : item.GPSLat;
cmd.Parameters.Add("@cStatus", SqlDbType.VarChar, 1).Value = item.cStatus == null ? (object)DBNull.Value : item.cStatus;
cmd.Parameters.Add("@cCreated", SqlDbType.VarChar, 20).Value = item.cCreated ?? (object)DBNull.Value;
cmd.Parameters.Add("@cLastUpdated", SqlDbType.VarChar, 20).Value = item.cLastUpdated == null ? (object)DBNull.Value : item.cLastUpdated;
//cmd.ExecuteNonQuery();
//}
}
else if (item.RowState == DataRowState.Modified)
{
cmd = new SqlCommand("up_UpdateTrArticle");
//cmd.Connection = new SqlConnection(DALInfo.ConnectionString);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("@moduleid", SqlDbType.VarChar, 5).Value = item.ModuleID;
cmd.Parameters.Add("@Id", SqlDbType.BigInt).Value = item.Id;
cmd.Parameters.Add("@Category", SqlDbType.VarChar, 10).Value = item.Category ?? (object)DBNull.Value;
cmd.Parameters.Add("@Title", SqlDbType.VarChar, 20).Value = item.Title ?? (object)DBNull.Value;
cmd.Parameters.Add("@Story", SqlDbType.VarChar, 255).Value = item.Story ?? (object)DBNull.Value;
cmd.Parameters.Add("@GPSLocation", SqlDbType.VarChar, 100).Value = item.GPSLocation ?? (object)DBNull.Value;
cmd.Parameters.Add("@GPSLong", SqlDbType.Float).Value = item.GPSLong == null ? (object)DBNull.Value : item.GPSLong;
cmd.Parameters.Add("@GPSLat", SqlDbType.Float).Value = item.GPSLat == null ? (object)DBNull.Value : item.GPSLat;
cmd.Parameters.Add("@cStatus", SqlDbType.VarChar, 1).Value = item.cStatus == null ? (object)DBNull.Value : item.cStatus;
cmd.Parameters.Add("@cLastUpdated", SqlDbType.VarChar, 20).Value = item.cLastUpdated == null ? (object)DBNull.Value : item.cLastUpdated;
//cmd.ExecuteNonQuery();
}
else if (item.RowState == DataRowState.Deleted)
{
cmd = new SqlCommand("up_DeleteTrArticle");
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("@moduleid", SqlDbType.VarChar, 5).Value = item.ModuleID;
cmd.Parameters.Add("@Id", SqlDbType.BigInt).Value = item.Id;
//cmd.Parameters.Add("@userroleid", SqlDbType.VarChar, 20).Value = item.UserRoleID;
//cmd.Parameters.Add("@modusrid", SqlDbType.VarChar, 20).Value = item.ModUsrID == null ? (object)DBNull.Value : item.ModUsrID;
//cmd.ExecuteNonQuery();
}
if (cmd != null)
{
ArraySQLCmd.Add(cmd);
}
}
public void GetBatchQueryUpdate(List <TrArticle> objDomain, ref List <SqlCommand> ArraySQLCmd)
{
foreach (TrArticle item in objDomain)
{
TrArticle itm = item;
DALInfo.AssignedInfo(ref itm);
UpdateQuery(itm, ArraySQLCmd);
}
}
public ActionResult CreateOrUpdate(TrArticleViewModel trArticleViewModel, IEnumerable <HttpPostedFileBase> imageFiles)
{
BootstrapBreadcrumbHelper bcrumb = new BootstrapBreadcrumbHelper();
string a = Request["a"];
bcrumb.AddNode(Url.Action("Index", "Home"), "Home");
bcrumb.AddNode(Url.Action("Index", "TrNews"), "Berita");
bcrumb.AddNode(Url.Action("CreateOrUpdate", "TrNews"), "Add/Edit Berita");
TrArticle trArticle = trArticleDataAccess.GetArticleById(trArticleViewModel.Id);
try
{
if (trArticle == null)
{
trArticle = new TrArticle();
trArticle.Category = "NEWS";
trArticle.Title = trArticleViewModel.Title;
trArticle.Story = trArticleViewModel.Story;
trArticle.dCreated = DateTime.Now;
trArticle.cCreated = HttpContext.User.Identity.Name;
trArticle.RowState = System.Data.DataRowState.Added;
trArticleDataAccess.Insert(ref trArticle);
}
else
{
trArticle.Title = trArticleViewModel.Title;
trArticle.Story = trArticleViewModel.Story;
trArticle.dLastUpdated = DateTime.Now;
trArticle.cLastUpdated = HttpContext.User.Identity.Name;
trArticle.RowState = System.Data.DataRowState.Modified;
trArticleDataAccess.Update(ref trArticle);
}
this.deleteListImages(trArticle.Id, trArticleViewModel.listImage == null ? null : trArticleViewModel.listImage.Select(x => x.Id).ToArray());
this.saveListImages(trArticle.Id, imageFiles);
}
catch (Exception e)
{
Error(e.Message);
return(View(trArticleViewModel));
}
Success("Article Successfully saved");
return(this.RedirectToAction("Index"));
}
public ActionResult Delete(long id)
{
BootstrapBreadcrumbHelper bcrumb = new BootstrapBreadcrumbHelper();
bcrumb.AddNode(Url.Action("Index", "Home"), "Home");
bcrumb.AddNode(Url.Action("Index", "TrNews"), "Berita");
TrArticle trArticle = trArticleDataAccess.GetArticleById(id);
trArticle.RowState = System.Data.DataRowState.Deleted;
trArticleDataAccess.Update(ref trArticle);
Success("Article Successfully deleted");
return(this.RedirectToAction("Index"));
}
public TransactionResult Update(ref TrArticle item)
{
List <SqlCommand> ArraySQLCmd = new List <SqlCommand>();
TransactionDB TransDB = new TransactionDB(DALInfo);
TransactionResult ObjTransResult = default(TransactionResult);
GetSingleQueryUpdate(item, ref ArraySQLCmd);
ObjTransResult = TransDB.BatchUpdate(ArraySQLCmd);
if (ObjTransResult.Result == 1 && !item.RowState.Equals(DataRowState.Deleted))
{
item.RowState = DataRowState.Unchanged;
}
return(ObjTransResult);
}
public ActionResult SetStatus(int id, string status)
{
string msg = "Artikel berhasil diupdate";
try
{
TrArticle trArticle = trArticleDataAccess.GetArticleById(id);
trArticle.cStatus = status; //trArticle.cStatus == "Y" ? "N" : "Y";
trArticleDataAccess.Update(ref trArticle);
}
catch (Exception e)
{
msg = e.Message;
}
return(this.Json(new { message = msg }, JsonRequestBehavior.AllowGet));
}
private List <TrArticle> MoveDataToCollection(SqlDataReader MyReader, Boolean isCustom = false)
{
List <TrArticle> msArticleList = new List <TrArticle>();
while (MyReader.Read())
{
var columns = new List <string>();
for (int i = 0; i < MyReader.FieldCount; i++)
{
columns.Add(MyReader.GetName(i));
}
TrArticle objTrArticle = new TrArticle();
objTrArticle.Id = long.Parse(MyReader["Id"].ToString().Trim());
objTrArticle.Category = MyReader["Category"].ToString().Trim();
objTrArticle.Title = MyReader["Title"].ToString().Trim();
objTrArticle.Story = MyReader["Story"].ToString();
objTrArticle.GPSLocation = MyReader["GPSLocation"].ToString().Trim();
objTrArticle.GPSLong = double.Parse(string.IsNullOrEmpty(MyReader["GPSLong"].ToString()) ? "0" : MyReader["GPSLong"].ToString());
objTrArticle.GPSLat = double.Parse(string.IsNullOrEmpty(MyReader["GPSLat"].ToString()) ? "0" : MyReader["GPSLat"].ToString());
objTrArticle.cStatus = MyReader["cStatus"].ToString().Trim();
objTrArticle.cCreated = MyReader["cCreated"].ToString().Trim();
objTrArticle.dCreated = Mapper <DateTime>(MyReader["dCreated"]);
objTrArticle.cLastUpdated = MyReader["cLastUpdated"].ToString().Trim();
objTrArticle.dLastUpdated = Mapper <DateTime>(MyReader["dLastUpdated"]);
objTrArticle.RowState = DataRowState.Unchanged;
int fields = MyReader.FieldCount;
int visi = MyReader.VisibleFieldCount;
if (isCustom == true)
{
objTrArticle.RowNumber = Convert.ToInt64(MyReader["RowNumber"]);
objTrArticle.TotalRecord = Convert.ToInt64(MyReader["TotalRecord"]);
}
msArticleList.Add(objTrArticle);
}
return(msArticleList);
}
public TrArticle GetArticleById(long Id)
{
SqlConnection conn = new SqlConnection(DALInfo.ConnectionString);
SqlCommand cmd = new SqlCommand("up_RetrieveTrArticle", conn);
TrArticle objTbl = new TrArticle();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", Id);
SqlDataReader da = default(SqlDataReader);
try
{
cmd.Connection.Open();
da = cmd.ExecuteReader();
if (da.HasRows)
{
objTbl = MoveDataToCollection(da)[0];
}
else
{
return(null);//objTbl;
}
}
catch (Exception)
{
throw;
}
finally
{
da.Close();
conn.Close();
cmd.Dispose();
}
return(objTbl);
}
public ActionResult CreateOrUpdate(int id)
{
BootstrapBreadcrumbHelper bcrumb = new BootstrapBreadcrumbHelper();
bcrumb.AddNode(Url.Action("Index", "Home"), "Home");
bcrumb.AddNode(Url.Action("Index", "TrNews"), "Berita");
bcrumb.AddNode(Url.Action("CreateOrUpdate", "TrNews"), "Add/Edit Berita");
ViewBag.Breadcrumb = bcrumb.Output();
TrArticle trArticle = trArticleDataAccess.GetArticleById(id);
TrArticleViewModel trArticleViewModel = new TrArticleViewModel();
if (trArticle != null)
{
trArticleViewModel.Title = trArticle.Title;
trArticleViewModel.Story = trArticle.Story;
trArticleViewModel.listImage = getListImages(id);
}
return(View(trArticleViewModel));
}
// POST api/values
public object Post()
{
JsonResultViewModel result = new JsonResultViewModel();
result.status = true;
result.message = "News successfully updated";
result.messageCode = "S";
var httpRequest = HttpContext.Current.Request;
long id = string.IsNullOrEmpty(httpRequest["Id"]) ? 0 : long.Parse(httpRequest["Id"]);
string title = httpRequest["title"];
string story = httpRequest["story"];
string location = httpRequest["location"];
double latitude = string.IsNullOrEmpty(httpRequest["latitude"]) ? 0 : double.Parse(httpRequest["latitude"]);
double longitude = string.IsNullOrEmpty(httpRequest["longitude"]) ? 0 : double.Parse(httpRequest["longitude"]);
string userId = httpRequest["userId"];
try
{
using (PatuhEntities db = new PatuhEntities())
{
TrArticle article = db.TrArticles.Where(x => x.Id == id).FirstOrDefault();
if (article == null)
{
article = new TrArticle();
article.cCreated = userId;
article.dCreated = DateTime.Now;
db.TrArticles.AddObject(article);
}
article.Category = "ARTICLE";
article.Title = title;
article.Story = story;
article.GPSLocation = location;
article.GPSLong = longitude;
article.GPSLat = latitude;
article.cLastUpdated = userId;
article.dLastUpdated = DateTime.Now;
db.SaveChanges();
try
{
if (httpRequest.Files.Count > 0)
{
IList <TrImageAttachment> currentImages = db.TrImageAttachments.Where(x => x.HeaderId == article.Id).ToList();
if (currentImages != null && currentImages.Count > 0)
{
foreach (TrImageAttachment dbImg in currentImages)
{
db.TrImageAttachments.DeleteObject(dbImg);
}
}
string extention = "";
string guid = "";
string[] supportedTypes = new string[] { "jpg", "jpeg", "bmp", "png" };
int fileSequence = 0;
foreach (string file in httpRequest.Files)
{
var postedFile = httpRequest.Files[file];
Type fileType = postedFile.GetType();
if (postedFile != null)
{
if (postedFile.FileName != "")
{
byte[] theImage = new byte[postedFile.ContentLength];
extention = (Path.GetExtension(postedFile.FileName).TrimStart('.')).ToLower();
if (supportedTypes.Contains(extention))
{
postedFile.InputStream.Read(theImage, 0, postedFile.ContentLength);
TrImageAttachment imageAtt = new TrImageAttachment();// db.TrImageAttachments.Where(x => x.Id == id).FirstOrDefault();
imageAtt = new TrImageAttachment();
imageAtt.HeaderId = article.Id;
imageAtt.cCreated = userId;
imageAtt.dCreated = DateTime.Now;
imageAtt.Sequence = ++fileSequence;
imageAtt.Image = theImage;
imageAtt.cLastUpdated = userId;
imageAtt.dLastUpdated = DateTime.Now;
db.TrImageAttachments.AddObject(imageAtt);
db.SaveChanges();
}
}
}
}
}
}
catch (Exception e)
{
result.status = false;
result.message = e.Message;
result.messageCode = "Error in saving article";
return(result);
}
}
}
catch (Exception e)
{
result.status = false;
result.message = e.Message;
result.messageCode = "Error in saving Article";
}
return(result);
}
// POST api/values
public object Post()
{
JsonResultViewModel result = new JsonResultViewModel();
result.status = true;
result.message = "Comment successfully updated";
result.messageCode = "S";
var httpRequest = HttpContext.Current.Request;
long id = string.IsNullOrEmpty(httpRequest["Id"]) ? 0 : long.Parse(httpRequest["Id"]);
long articleId = string.IsNullOrEmpty(httpRequest["ArticleId"]) ? 0 : long.Parse(httpRequest["ArticleId"]);
string postedComment = httpRequest["Comment"];
string userId = httpRequest["userId"];
try
{
using (PatuhEntities db = new PatuhEntities())
{
TrComment comment = db.TrComments.Where(x => x.Id == id).FirstOrDefault();
if (comment == null)
{
comment = new TrComment();
comment.cCreated = userId;
comment.dCreated = DateTime.Now;
db.TrComments.AddObject(comment);
MsPoint msPoint = db.MsPoints.Where(x => x.ActionCode == "COMMENTART").FirstOrDefault();
if (msPoint != null)
{
TrArticle trArticle = db.TrArticles.Where(x => x.Id == articleId).FirstOrDefault();
if (trArticle != null)
{
TrPoint trPoint = new TrPoint();
trPoint.ArticleId = trArticle.Id;
trPoint.UserID = trArticle.cCreated;
trPoint.ActionCode = "COMMENTART";
trPoint.PointValue = msPoint.RewardPoint;
trPoint.cCreated = userId;
trPoint.dCreated = DateTime.Now;
trPoint.cLastUpdated = userId;
trPoint.dLastUpdated = DateTime.Now;
db.TrPoints.AddObject(trPoint);
//db.SaveChanges();
}
}
}
comment.ArticleId = articleId;
comment.Comment = postedComment;
comment.cLastUpdated = userId;
comment.dLastUpdated = DateTime.Now;
db.SaveChanges();
}
}
catch (Exception e)
{
result.status = false;
result.message = e.Message;
result.messageCode = "Error in saving Comment";
}
return(result);
}
