public ComplaintEntity Get(int entityId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT c.*, s.SystemName, c.Comments AS UpdatedByName from Complaints c, Systems s ");
strSql.Append("WHERE c.ComplaintID=@ComplaintID AND c.SystemID=s.SystemID ");
ComplaintEntity comEntity = null;
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "ComplaintID", DbType.Int32, entityId);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
comEntity = ComplaintEntity.ReaderBind(dataReader);
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(comEntity);
}
public IActionResult AddComplaint([FromBody] ComplaintDataModel complaintModel)
{
var complaintEntity = ComplaintEntity.MapToEntity(complaintModel);
var complaint = _complaintService.AddComplaint(complaintEntity);
return(Ok(complaint));
}
public ComplaintEntity AddComplaint(ComplaintEntity complaintEntity)
{
var complaint = _context.Add(complaintEntity);
_context.SaveChanges();
return(complaint.Entity);
}
public int Insert(ComplaintEntity entity)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO Complaints(");
strSql.Append("Type,TargetID,Reason,AdditionalInfo,SystemID,AppSrc,ReporterID,ReporterEmail,CreatedOn,Status) ");
strSql.Append("VALUES(");
strSql.Append("@Type,@TargetID,@Reason,@AdditionalInfo,@SystemID,@AppSrc,@ReporterID,@ReporterEmail,@CreatedOn,@Status)");
strSql.Append(";select ISNULL( SCOPE_IDENTITY(),0);");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "Type", DbType.Int32, entity.Type);
db.AddInParameter(dbCommand, "TargetID", DbType.Int32, entity.TargetID);
db.AddInParameter(dbCommand, "Reason", DbType.Int32, entity.Reason);
db.AddInParameter(dbCommand, "AdditionalInfo", DbType.String, entity.AdditionalInfo);
db.AddInParameter(dbCommand, "SystemID", DbType.Int32, entity.SystemID);
db.AddInParameter(dbCommand, "AppSrc", DbType.Int32, entity.AppSrc);
db.AddInParameter(dbCommand, "ReporterID", DbType.Int32, entity.ReporterID);
db.AddInParameter(dbCommand, "ReporterEmail", DbType.String, entity.ReporterEmail);
db.AddInParameter(dbCommand, "CreatedOn", DbType.DateTime, entity.CreatedOn);
db.AddInParameter(dbCommand, "Status", DbType.Int32, entity.Status);
int result;
object obj = db.ExecuteScalar(dbCommand);
if (!int.TryParse(obj.ToString(), out result))
{
return(0);
}
return(result);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
return(0);
}
}
}
public void ShouldFetchCorrectComplaintFromDb(string title, string description)
{
// Arrange
using var context = new ComplaintsContext(DbFixtureProvider.CreateNewContextOptions());
var complaintService = new ComplaintService(context);
var complaintEntity = new ComplaintEntity
{
Title = title,
Description = description
};
var createdComplaint = context.Complaints.Add(complaintEntity);
context.SaveChanges();
// Act
var complaintInDb = complaintService.GetComplaintById(createdComplaint.Entity.Id);
// Assert
Assert.Equal(complaintInDb, createdComplaint.Entity);
}
/// <summary>
/// Save the complaint, for later processing.
/// </summary>
/// <param name="fullName">Complaint person's full name.</param>
/// <param name="complaint">The complaint.</param>
/// <param name="level">Level of complaint.</param>
public void ProcessComplaint(
string fullName,
string complaint,
string level)
{
var complaintEntity = new ComplaintEntity
{
FullName = fullName,
Complaint = complaint,
Level = level
};
var processComplaintCommand = new ProcessComplaintCommand
{
ComplaintEntity = complaintEntity
};
var queue = new Queue();
queue.AddMessage(processComplaintCommand);
}
public bool Update(ComplaintEntity entity)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE Complaints SET ");
strSql.Append("Status=@Status, ");
strSql.Append("Comments=@Comments ");
strSql.Append("WHERE ComplaintID=@ComplaintID");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "Status", DbType.Int32, entity.Status);
db.AddInParameter(dbCommand, "Comments", DbType.String, entity.Comments);
db.AddInParameter(dbCommand, "ComplaintID", DbType.Int32, entity.ComplaintID);
int rows = db.ExecuteNonQuery(dbCommand);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(false);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
complaintApp = new ComplaintApplication();
var master = (Pop)this.Master;
if (master != null)
{
master.Width = 580;
}
if (!IsPostBack)
{
int complaintID = QS("ComplaintID", 0);
ltlComplaintID.Text = complaintID.ToString();
IComplaintRepository comRepository = ObjectFactory.GetInstance <IComplaintRepository>();
cmplEntity = comRepository.Get(complaintID);
GetComplaintItem();
IComplaintHistoryRepository comHisRepository = ObjectFactory.GetInstance <IComplaintHistoryRepository>();
List <ComplaintHistoryEntity> list = comHisRepository.GetHistorysByComID(complaintID);
if (null != list && list.Count > 0)
{
this.rptComplaintHistoryList.DataSource = list;
}
else
{
this.trNoComments.Visible = true;
this.rptComplaintHistoryList.DataSource = new List <ComplaintHistoryEntity>();
}
this.rptComplaintHistoryList.DataBind();
}
}
public bool UpdateComplaint(ComplaintEntity complaintEntity)
{
return(complaintRepository.Update(complaintEntity));
}
public int AddComplaint(ComplaintEntity complaintEntity)
{
return(complaintRepository.Insert(complaintEntity));
}
protected void btnOK_Click(object sender, EventArgs e)
{
IComplaintRepository comRepository = ObjectFactory.GetInstance <IComplaintRepository>();
cmplEntity = comRepository.Get(QS("ComplaintID", 0));
// Update Complaint History
ComplaintHistoryEntity comHisEntity = new ComplaintHistoryEntity();
comHisEntity.ComplaintID = QS("ComplaintID", 0);
comHisEntity.ModifiedOn = DateTime.Now;
comHisEntity.ModifiedByID = UserInfo.UserID;
comHisEntity.Comments = txtComments.Text;
string actionStr = ((ComplaintStatusEnum)this.cmplEntity.Status).ToString() + " To " + ddlAction.SelectedValue;
comHisEntity.Action = actionStr;
IComplaintHistoryRepository comHisRepository = ObjectFactory.GetInstance <IComplaintHistoryRepository>();
comHisRepository.Insert(comHisEntity);
// Update Complaint
ComplaintEntity newCmplEntity = new ComplaintEntity();
newCmplEntity.Comments = txtComments.Text;
newCmplEntity.UpdatedOn = DateTime.Now;
newCmplEntity.UpdatedByID = UserInfo.UserID;
newCmplEntity.ComplaintID = QS("ComplaintID", 0);
switch (ddlAction.SelectedValue)
{
case "DELETE":
//Response.Redirect("http://localhost:2777/Complaint/Complaint/Delete?id=" + newCmplEntity.ComplaintID + "&type=" + (SunNet.PMNew.Entity.ComplaintModel.Enums.ComplaintTypeEnum)cmplEntity.Type + "&returnUrl=http://localhost:27273/OA/Complaints/Complaints.aspx");
//Get System properties from System table
ISystemRepository systemRepository = ObjectFactory.GetInstance <ISystemRepository>();
SystemEntity sysEntity = systemRepository.GetBySysName(cmplEntity.SystemName);
//Delete Item
string serverName = sysEntity.IP + (sysEntity.Port.Length > 0 ? ":" + sysEntity.Port : "");
string connStr = String.Format("server={0};database={1};uid={2};pwd={3};max pool size =1024000",
serverName, sysEntity.DBLocation, sysEntity.UserName, sysEntity.UserPwd);
string type = ((ComplaintTypeEnum)cmplEntity.Type).ToString();
if (complaintApp.DeleteComItem(connStr, sysEntity.Procedure, type, cmplEntity.TargetID))
{
Response.Write("Deletion Succeeded.");
}
else
{
Response.Write("Deletion Failed.");
}
newCmplEntity.Status = 1;
break;
case "APPROVEBUTNOTDEL":
newCmplEntity.Status = 2;
break;
case "DENY":
newCmplEntity.Status = 3;
break;
}
complaintApp.UpdateComplaint(newCmplEntity);
Redirect(EmptyPopPageUrl, false, true);
}
public List <ComplaintEntity> SearchComplaints(ComplaintSearchEntity request, out int recordCount)
{
recordCount = 0;
int startID = request.CurrentPage * request.PageCount + 1 - request.PageCount;
int endID = request.CurrentPage * request.PageCount;
StringBuilder strSql = new StringBuilder();
StringBuilder strSqlBody = new StringBuilder();
StringBuilder strSelAll = new StringBuilder();
StringBuilder strSelCnt = new StringBuilder();
StringBuilder strOrderBy = new StringBuilder();
StringBuilder strFitPage = new StringBuilder();
strSelCnt.Append("SELECT Count(*) FROM ");
strSelAll.Append(String.Format("SELECT * from(SELECT *, Row_Number() Over(Order By {0} {1}) RowNum FROM ", request.OrderExpression, request.OrderDirection));
strSqlBody.Append("(SELECT c.*, s.SystemName, '' AS UpdatedByName ");
strSqlBody.Append("FROM Complaints c, Systems s ");
strSqlBody.Append("WHERE c.SystemID=s.SystemID AND c.UpdatedByID is NULL ");
strSqlBody.Append("UNION ");
strSqlBody.Append("SELECT c.*, s.SystemName, u.FirstName+' '+u.LastName AS UpdatedByName ");
strSqlBody.Append("FROM Complaints c, Systems s, Users u ");
strSqlBody.Append("WHERE c.SystemID=s.SystemID AND c.UpdatedByID=u.UserID) combine ");
strSqlBody.Append("WHERE combine.ComplaintID>0 ");
strFitPage.Append(") bigCombine where RowNum BETWEEN @StartID AND @EndID ");
if (request.Type >= 0)
{
strSqlBody.Append("AND combine.Type=@Type ");
}
if (request.Reason >= 0)
{
strSqlBody.Append("AND combine.Reason=@Reason ");
}
if (request.SystemID >= 0)
{
strSqlBody.Append("AND combine.SystemID=@SystemID ");
}
if (request.AppSrc >= 0)
{
strSqlBody.Append("AND combine.AppSrc=@AppSrc ");
}
if (request.Status >= 0)
{
strSqlBody.Append("AND combine.Status=@Status ");
}
if (request.Keyword.Length > 0)
{
strSqlBody.Append("AND (combine.AdditionalInfo like @Keywords OR combine.SystemName like @Keywords) ");
}
if (request.UpdatedByName.Length > 0)
{
strSqlBody.Append("AND combine.UpdatedByName like @UpdatedByName ");
}
strSql.Append(strSelCnt);
strSql.Append(strSqlBody);
strSql.Append(";");
strSql.Append(strSelAll);
strSql.Append(strSqlBody);
strSql.Append(strFitPage);
// Execute
List <ComplaintEntity> list = new List <ComplaintEntity>();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "Type", DbType.Int32, request.Type);
db.AddInParameter(dbCommand, "Reason", DbType.Int32, request.Reason);
db.AddInParameter(dbCommand, "SystemID", DbType.Int32, request.SystemID);
db.AddInParameter(dbCommand, "AppSrc", DbType.Int32, request.AppSrc);
db.AddInParameter(dbCommand, "Status", DbType.Int32, request.Status);
db.AddInParameter(dbCommand, "Keywords", DbType.String, request.Keyword);
db.AddInParameter(dbCommand, "UpdatedByName", DbType.String, request.UpdatedByName);
db.AddInParameter(dbCommand, "StartID", DbType.Int32, startID);
db.AddInParameter(dbCommand, "EndID", DbType.Int32, endID);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
recordCount = dataReader.GetInt32(0);
dataReader.NextResult();
}
while (dataReader.Read())
{
list.Add(ComplaintEntity.ReaderBind(dataReader));
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSqlBody.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
return(list);
}
}
public bool UpdateComplaint(ComplaintEntity complaintEntity)
{
return(complaintMgr.UpdateComplaint(complaintEntity));
}
public int AddComplaint(ComplaintEntity complaintEntity)
{
return(complaintMgr.AddComplaint(complaintEntity));
}
public int Report(int type, int targetID, int reason, string additionalInfo, int systemID,
int appSource, int reporterID, string reporterEmail, long timeStamp, string sign)
{
try
{
ISystemRepository systemRepository = ObjectFactory.GetInstance <ISystemRepository>();
SystemEntity systemEntity = systemRepository.Get(systemID);
string md5Key = systemEntity.MD5Key; // "MFBUY#!982015"
if (additionalInfo == null)
{
additionalInfo = "";
}
if (reporterEmail == null)
{
reporterEmail = "";
}
string seed = "" + type + targetID + reason + additionalInfo + systemID + appSource + reporterID + reporterEmail + timeStamp;
string localSign = UtilFactory.GetEncryptProvider(EncryptType.MD5).Encrypt(seed + md5Key);
localSign = localSign.Replace("-", "");
//Log seed and Local Sign
WebLogAgent.Write(string.Format("[Complaint Seed: {0},\r\nLocalSign: {1}\r\nSign:{2}]",
seed,
localSign,
sign));
if (localSign == sign.ToUpper())
{
//Insert to dababas
ComplaintEntity complaintEntity = new ComplaintEntity();
complaintEntity.Type = type;
complaintEntity.TargetID = targetID;
complaintEntity.Reason = reason;
complaintEntity.AdditionalInfo = additionalInfo;
complaintEntity.SystemID = systemID;
complaintEntity.AppSrc = appSource;
complaintEntity.ReporterID = reporterID;
complaintEntity.ReporterEmail = reporterEmail;
RealSystemDateTime time = new RealSystemDateTime();
complaintEntity.CreatedOn = time.Now;
complaintEntity.Status = 1;
ComplaintApplication complaintApp = new ComplaintApplication();
int newComID = complaintApp.AddComplaint(complaintEntity);
try
{
//Send email
IEmailSender sender = ObjectFactory.GetInstance <IEmailSender>();
string emailTitle = string.Format("Complaint Received from {0} ", systemEntity.SystemName);
string emailContent = "Please check this URL: \r\n " + Config.AppDomain + "/OA/Complaints/ComplaintReview.aspx?ComplaintID=" + newComID + "\r\n\r\n";
sender.SendMail(Config.ComplainNotifyList, Config.DefaultSendEmail, emailTitle, emailContent);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[Email Sending Exception]: {0}", ex.Message));
}
return(1); //Accepted, Successed
}
return(2); //Invalid
}
catch (Exception ex)
{
//log this excption
WebLogAgent.Write(string.Format("[Exception]: {0}", ex.Message));
return(3);//System Error
}
}