public PaginationQueryResult<Complaint> GetComplaint(PaginationQueryCondition condition)
{
PaginationQueryResult<Complaint> result = new PaginationQueryResult<Complaint>();
string sql = "SELECT TOP " + condition.PageSize + " id, client_id, client_name, content, create_time, company_id FROM complaints ";
if (condition.CurrentPage > 1)
{
sql += " WHERE id<(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize*(condition.CurrentPage-1) + " id FROM complaints ORDER BY id DESC) AS D) ";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM complaints ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Complaint comp = new Complaint();
comp.Id = dr.GetInt32(0);
comp.ClientId = dr.GetInt32(1);
comp.ClientName = dr.GetString(2);
comp.Content = dr.GetString(3);
comp.CreateTime = dr.GetDateTime(4);
comp.CompanyId = dr.GetInt32(5);
result.Results.Add(comp);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string title = Request.Form[txtTitle.ID].Trim();
if (string.IsNullOrEmpty(title) || Validator.IsMatchLessThanChineseCharacter(title, TITLE_LENGTH))
{
lblMsg.Text = "标题不能为空， 且长度不能超过"+ TITLE_LENGTH +"个字符！";
return;
}
string content = Request.Form[txtContent.ID].Trim();
if(string.IsNullOrEmpty(content))
{
lblMsg.Text = "投诉内容不能为空！";
return;
}
Complaint comp = new Complaint();
comp.Title = title;
comp.ClientId = clientSession.Id;
comp.ClientName = clientSession.RealName;
comp.Content = content;
comp.CreateTime = DateTime.Now;
Client client = ClientOperation.GetClientById(clientSession.Id);
comp.CompanyId = client.CompanyId;
ComplaintOperation.CreateComplaint(comp);
lblMsg.Text = "提交成功！";
}
public void CreateComplaint(Complaint comp)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@client_id",comp.ClientId),
SqlUtilities.GenerateInputIntParameter("@company_id",comp.CompanyId),
SqlUtilities.GenerateInputNVarcharParameter("@client_name",50,comp.ClientName),
SqlUtilities.GenerateInputNVarcharParameter("@title",50,comp.Title),
SqlUtilities.GenerateInputNVarcharParameter("@content", 2000,comp.Content),
SqlUtilities.GenerateInputDateTimeParameter("@create_time",comp.CreateTime)
};
string sql = "INSERT INTO complaints(client_id, company_id, client_name, title, content, create_time) VALUES(@client_id, @company_id, @client_name, @title, @content, @create_time) ";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
protected void Page_Load(object sender, EventArgs e)
{
seo.Title = "我的投诉";
int id = 0;
if(int.TryParse(Request.QueryString["id"],out id))
{
comp = ComplaintOperation.GetComplaintById(id);
}
lblTitle.Text = comp.Title;
lblCreateTime.Text = comp.CreateTime.ToString();
lblContent.Text = comp.Content;
divComplaintReplyDataBind();
}
public static void UpdateComplaintIsReply(Complaint complaint)
{
dal.UpdateComplaintIsReply(complaint);
}
public static void CreateComplaint(Complaint comp)
{
dal.CreateComplaint(comp);
}
public PaginationQueryResult<Complaint> GetComplaintByClientId(PaginationQueryCondition condition, int clientId)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@client_id", clientId)
};
PaginationQueryResult<Complaint> result = new PaginationQueryResult<Complaint>();
string sql = "SELECT TOP " + condition.PageSize + " id, client_id, client_name, content, create_time, company_id, is_reply, title FROM complaints WHERE client_id = @client_id AND is_delete = 0";
if (condition.CurrentPage > 1)
{
sql += " AND id<(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM complaints WHERE client_id = @client_id AND is_delete = 0 ORDER BY id DESC) AS D) ";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM complaints WHERE client_id = @client_id AND is_delete = 0 ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
Complaint comp = new Complaint();
comp.Id = dr.GetInt32(0);
comp.ClientId = dr.GetInt32(1);
comp.ClientName = dr.GetString(2);
comp.Content = dr.GetString(3);
comp.CreateTime = dr.GetDateTime(4);
comp.CompanyId = dr.GetInt32(5);
comp.IsReply = dr.GetBoolean(6);
comp.Title = dr.GetString(7);
result.Results.Add(comp);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public void UpdateComplaintIsReply(Complaint complaint)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", complaint.Id),
SqlUtilities.GenerateInputParameter("@is_reply", SqlDbType.Bit, complaint.IsReply)
};
string sql = "UPDATE complaints SET is_reply = @is_reply WHERE id = @id";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public Complaint GetComplaintById(int id)
{
Complaint comp = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", id)
};
string sql = "SELECT id, client_id, client_name, content, create_time,company_id, is_reply, title FROM complaints WHERE id = @id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
comp = new Complaint();
comp.Id = dr.GetInt32(0);
comp.ClientId = dr.GetInt32(1);
comp.ClientName = dr.GetString(2);
comp.Content = dr.GetString(3);
comp.CreateTime = dr.GetDateTime(4);
comp.CompanyId = dr.GetInt32(5);
comp.IsReply = dr.GetBoolean(6);
comp.Title = dr.GetString(7);
}
}
return comp;
}