public ProductCommentQuery GetUsetInfo(ProductCommentQuery store)
{
try
{
return _proCommentDao.GetUsetInfo(store);
}
catch (Exception ex)
{
throw new Exception("ProductCommentMgr-->GetUsetInfo-->" + ex.Message, ex);
}
}
/// <summary>
/// 修改評價滿意度
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int ProductCommentSatisfySave(ProductCommentQuery query)
{
try
{
return _proCommentDao.ProductCommentSatisfySave(query);
}
catch (Exception ex)
{
throw new Exception("ProductCommentMgr-->ProductCommentSatisfySave-->" + ex.Message, ex);
}
}
public int UpdateActive(ProductCommentQuery model)
{
try
{
return _proCommentDao.UpdateActive(model);
}
catch (Exception ex)
{
throw new Exception("ProductCommentMgr-->UpdateActive-->" + ex.Message, ex);
}
}
/// <summary>
/// 商品滿意度修改
/// </summary>
/// <returns></returns>
public HttpResponseBase ProductCommentSatisfySave()
{
string json = string.Empty;
ProductCommentQuery query = new ProductCommentQuery();
try
{
ProductCommentQuery store = new ProductCommentQuery();
_proCommentImpl = new ProductCommentMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Params["comment_id"]))
{
query.comment_id = Convert.ToInt32(Request.Params["comment_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["logistics_deliver"]))
{
query.logistics_deliver = Convert.ToInt32(Request.Params["logistics_deliver"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["web_server"]))
{
query.web_server = Convert.ToInt32(Request.Params["web_server"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["seller_server"]))
{
query.seller_server = Convert.ToInt32(Request.Params["seller_server"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["product_desc"]))
{
query.product_desc = Convert.ToInt32(Request.Params["product_desc"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["old_logistics_deliver"]))
{
query.old_logistics_deliver = Convert.ToInt32(Request.Params["old_logistics_deliver"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["old_web_server"]))
{
query.old_web_server = Convert.ToInt32(Request.Params["old_web_server"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["old_seller_server"]))
{
query.old_seller_server = Convert.ToInt32(Request.Params["old_seller_server"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["old_product_desc"]))
{
query.old_product_desc = Convert.ToInt32(Request.Params["old_product_desc"].ToString());
}
query.reply_user = (Session["caller"] as Caller).user_id;
if (_proCommentImpl.ProductCommentSatisfySave(query) > 0)
{
json = "{success:'true'}";//保存成功
}
else
{
json = "{success:'false'}";//保存失敗
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase ProductCommentSave()
{
string json = string.Empty;
int send_mail = 1;
int isSave = 0;//保存未成功
ProductCommentQuery query = new ProductCommentQuery();
try
{
string path = Server.MapPath(xmlPath);
SiteConfigMgr _siteConfigMgr = new SiteConfigMgr(path);
SiteConfig Mail_From = _siteConfigMgr.GetConfigByName("Mail_From");
SiteConfig Mail_Host = _siteConfigMgr.GetConfigByName("Mail_Host");
SiteConfig Mail_Port = _siteConfigMgr.GetConfigByName("Mail_Port");
SiteConfig Mail_UserName = _siteConfigMgr.GetConfigByName("Mail_UserName");
SiteConfig Mail_UserPasswd = _siteConfigMgr.GetConfigByName("Mail_UserPasswd");
string EmailFrom = Mail_From.Value;//發件人郵箱
string SmtpHost = Mail_Host.Value;//smtp服务器
string SmtpPort = Mail_Port.Value;//smtp服务器端口
string EmailUserName = Mail_UserName.Value;//郵箱登陸名
string EmailPassWord = Mail_UserPasswd.Value;//郵箱登陸密碼
ProductCommentQuery store = new ProductCommentQuery();
_proCommentImpl = new ProductCommentMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Params["comment_id"]))
{
query.comment_id = Convert.ToInt32(Request.Params["comment_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["comment_answer"]))
{
query.comment_answer = Request.Params["comment_answer"];
query.comment_answer = query.comment_answer.Replace("\\", "\\\\");
}
if (!string.IsNullOrEmpty(Request.Params["comment_answer"]))
{
query.old_comment_answer = Request.Params["old_comment_answer"];
query.old_comment_answer = query.old_comment_answer.Replace("\\", "\\\\");
}
if (!string.IsNullOrEmpty(Request.Params["answer_is_show"]))
{
query.answer_is_show = Convert.ToInt32(Request.Params["answer_is_show"]);
}
if (!string.IsNullOrEmpty(Request.Params["old_answer_is_show"]))
{
query.old_answer_is_show = Request.Params["old_answer_is_show"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["isReplay"]))
{
if (Convert.ToInt32(Request.Params["isReplay"]) == 1)
{
query.old_answer_is_show = string.Empty;
}
}
if (!string.IsNullOrEmpty(Request.Params["comment_detail_id"]))
{
query.comment_detail_id = Convert.ToInt32(Request.Params["comment_detail_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["send_mail"]))
{
send_mail = Convert.ToInt32(Request.Params["send_mail"]);//0 發送 1 不發送
query.send_mail = Convert.ToInt32(Request.Params["send_mail"]);//0 發送 1 不發送 2 發送失敗
}
query.reply_user = (Session["caller"] as Caller).user_id;
store = _proCommentImpl.GetUsetInfo(query);
string user_email = store.user_email;
string user_name = store.user_name;
string product_name = store.product_name;
string comment_info = store.comment_info;
if (_proCommentImpl.ProductCommentSave(query) > 0)
{
json = "{success:'true',msg:'0'}";//保存成功，但不發送郵件
isSave = 1;//指示保存成功
if (send_mail == 0)
{
FileStream fs = new FileStream(Server.MapPath("../ImportUserIOExcel/CommentReplay.html"), FileMode.OpenOrCreate, FileAccess.Read);
StreamReader sr = new StreamReader(fs, Encoding.UTF8);
string strTemp = sr.ReadToEnd();
sr.Close();
fs.Close();
//[email protected]
strTemp = strTemp.Replace("{{$username$}}", user_name);
strTemp = strTemp.Replace("{{$productName$}}", product_name);
strTemp = strTemp.Replace("{{$commentAnwser$}}", Request.Params["comment_answer"]);
strTemp = strTemp.Replace("{{$commentInfo$}}", comment_info);
if (sendmail(EmailFrom, FromName, user_email, user_name, EmailTile, strTemp, "", SmtpHost, Convert.ToInt32(SmtpPort), EmailUserName, EmailPassWord))
{
json = "{success:'true',msg:'1'}";//保存成功,郵件發送成功！
}
else
{
json = "{success:'true',msg:'2'}";//保存成功,郵件發送失敗！
query.send_mail = 2;
_proCommentImpl.ProductCommentSave(query);
}
}
}
else
{
json = "{success:'false'}";//保存失敗
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
if (isSave == 1)//保存成功,可能郵件發送失敗！
{
json = "{success:'true',msg:'2'}";//保存成功,郵件發送失敗！
query.send_mail = 2;
_proCommentImpl.ProductCommentSave(query);
}
else//保存失敗
{
json = "{success:'false'}";
}
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase ProductCommentLogExport()
{
string newCsvName = string.Empty;
string json = string.Empty;
_proCommentImpl = new ProductCommentMgr(mySqlConnectionString);
ProductCommentQuery query = new ProductCommentQuery();
query.change_table = Request.Params["table_name"].ToString();
if (!string.IsNullOrEmpty(Request.Params["comment_id"]))
{
query.comment_id = Convert.ToInt32(Request.Params["comment_id"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
//query.start_time = Convert.ToDateTime(Request.Params["start_time"]);
query.start_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
//query.end_time = Convert.ToDateTime(Request.Params["end_time"]);
query.end_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
try
{
DataTable _dt = _proCommentImpl.ProductCommentLogExport(query);
string filename = "評價管理異動記錄_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";
if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))
{
System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
}
newCsvName = Server.MapPath(excelPath) + filename;
if (System.IO.File.Exists(newCsvName))
{
//設置文件的屬性，以防刪除文件的時候因為文件的屬性造成無法刪除
System.IO.File.SetAttributes(newCsvName, FileAttributes.Normal);
System.IO.File.Delete(newCsvName);
}
string[] colname = { "評價編號", "評價內容", "修改人", "修改時間", "變動欄位", "欄位中文名", "修改前值", "修改后值" };
CsvHelper.ExportDataTableToCsv(_dt, newCsvName, colname, true);
json = "true," + filename + "," + excelPath;
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "false, ";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetProductCommentList()
{
DataTable store = new DataTable();
string json = string.Empty;
string str = string.Empty;
try
{
_proCommentImpl = new ProductCommentMgr(mySqlConnectionString);
ProductCommentQuery query = new ProductCommentQuery();
query.Start = Convert.ToInt32(Request["Start"] ?? "0");
if (!string.IsNullOrEmpty(Request["Limit"]))
{
query.Limit = Convert.ToInt32(Request["Limit"]);
}
if (!string.IsNullOrEmpty(Request.Params["ddlSel"]))
{
query.comment_type = Convert.ToInt32(Request.Params["ddlSel"]);
}
if (!string.IsNullOrEmpty(Request.Params["commentsel"]))
{
query.commentsel = Request.Params["commentsel"];
}
if (!string.IsNullOrEmpty(Request.Params["productName"]))
{
query.product_name = Request.Params["productName"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["userName"]))
{
query.user_name = Request.Params["userName"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["userEmail"]))
{
query.user_email = Request.Params["userEmail"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["brand_name"]))
{
query.brand_name = Request.Params["brand_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["timestart"]))
{
query.beginTime = Convert.ToInt32(CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["timestart"].ToString()).ToString("yyyy-MM-dd HH:mm:ss")));
}
if (!string.IsNullOrEmpty(Request.Params["timeend"]))
{
query.endTime = Convert.ToInt32(CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["timeend"].ToString()).ToString("yyyy-MM-dd HH:mm:ss")));
}
if (!string.IsNullOrEmpty(Request.Params["productId"]))
{
query.product_id = Convert.ToUInt32(Request.Params["productId"]);
}
if (!string.IsNullOrEmpty(Request.Params["shopClass"]))
{
query.productIds = Request.Params["shopClass"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["isReplay"]))
{
query.isReplay = Convert.ToInt32(Request.Params["isReplay"].ToString());
}
int totalCount = 0;
store = _proCommentImpl.Query(query, out totalCount);
for (int i = 0; i < store.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(store.Rows[i]["user_name"].ToString()))
{
store.Rows[i]["user_name"] = store.Rows[i]["user_name"].ToString().Substring(0, 1) + "**";
}
store.Rows[i]["user_email"] = store.Rows[i]["user_email"].ToString().Split('@')[0] + "@***";
store.Rows[i]["answer_is_show"] = store.Rows[i]["answer_is_show"].ToString() == "" ? "-1" : store.Rows[i]["answer_is_show"].ToString();
store.Rows[i]["is_show_name"] = store.Rows[i]["is_show_name"].ToString() == "" ? "-1" : store.Rows[i]["is_show_name"].ToString();
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:true,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetChangeLogList()
{
string json = "{success:false,data:0}";
try
{
_proCommentImpl = new ProductCommentMgr(mySqlConnectionString);
ProductCommentQuery query = new ProductCommentQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
query.change_table = Request.Params["table_name"].ToString();
if (!string.IsNullOrEmpty(Request.Params["comment_id"]))
{
query.comment_id = Convert.ToInt32(Request.Params["comment_id"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
query.start_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
query.end_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
int totalCount = 0;
DataTable store = _proCommentImpl.GetChangeLogList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 更改活動使用狀態
/// </summary>
/// <returns>數據庫操作結果</returns>
public JsonResult UpdateActive()
{
string jsonStr = string.Empty;
try
{
ProductCommentQuery model = new ProductCommentQuery();
if (!string.IsNullOrEmpty(Request.Params["active"]))
{
model.status = Convert.ToInt32(Request.Params["active"]);
}
if (!string.IsNullOrEmpty(Request.Params["id"]))
{
model.comment_id = Convert.ToInt32(Request.Params["id"]);
}
_proCommentImpl = new ProductCommentMgr(mySqlConnectionString);
if (_proCommentImpl.UpdateActive(model) > 0)
{
return Json(new { success = "true" });
}
else
{
return Json(new { success = "false" });
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
return Json(new { success = "false" });
}
}
/// <summary>
/// 匯出評價列表csv表格
/// </summary>
public void IndexExport()
{
string json = string.Empty;
StringBuilder sb = new StringBuilder();
try
{
DataTable store = new DataTable();
#region 前提查詢條件
ProductCommentQuery query = new ProductCommentQuery();
query.Start = Convert.ToInt32(Request["Start"] ?? "0");
if (!string.IsNullOrEmpty(Request["Limit"]))
{
query.Limit = Convert.ToInt32(Request["Limit"]);
}
if (!string.IsNullOrEmpty(Request.Params["ddlSel"]) && Request.Params["ddlSel"] != "null")
{
query.comment_type = Convert.ToInt32(Request.Params["ddlSel"]);
}
if (!string.IsNullOrEmpty(Request.Params["commentsel"]) && Request.Params["commentsel"] != "null")
{
query.commentsel = Request.Params["commentsel"];
}
if (!string.IsNullOrEmpty(Request.Params["productName"]))
{
query.product_name = Request.Params["productName"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["userName"]))
{
query.user_name = Request.Params["userName"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["userEmail"]))
{
query.user_email = Request.Params["userEmail"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["isReplay"]))
{
query.isReplay = Convert.ToInt32(Request.Params["isReplay"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["brand_name"]))
{
query.brand_name = Request.Params["brand_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["timestart"]) && Request.Params["timestart"] != "null")
{
query.beginTime = Convert.ToInt32(CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["timestart"].ToString()).ToString("yyyy-MM-dd HH:mm:ss")));
}
if (!string.IsNullOrEmpty(Request.Params["timeend"]) && Request.Params["timeend"] != "null")
{
query.endTime = Convert.ToInt32(CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["timeend"].ToString()).ToString("yyyy-MM-dd HH:mm:ss")));
}
if (!string.IsNullOrEmpty(Request.Params["productId"]))
{
query.product_id = Convert.ToUInt32(Request.Params["productId"]);
}
if (!string.IsNullOrEmpty(Request.Params["shopClass"]) && Request.Params["shopClass"] != "null")
{
query.productIds = Request.Params["shopClass"].ToString();
}
query.IsPage = false;
#endregion
_proCommentImpl = new ProductCommentMgr(mySqlConnectionString);
string fileName = "用戶評價信息" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";
int totalCount = 0;
store = _proCommentImpl.Query(query, out totalCount);
DataTable dtHZ = new DataTable();
string newExcelName = string.Empty;
dtHZ.Columns.Add("編號", typeof(String));
dtHZ.Columns.Add("訂單編號", typeof(String));
dtHZ.Columns.Add("商品編號", typeof(String));
dtHZ.Columns.Add("商品名稱", typeof(String));
dtHZ.Columns.Add("品牌名稱", typeof(String));
dtHZ.Columns.Add("用戶名稱", typeof(String));
//dtHZ.Columns.Add("用戶郵箱", typeof(String));
dtHZ.Columns.Add("留言狀態", typeof(String));
dtHZ.Columns.Add("留言內容", typeof(String));//--處理
dtHZ.Columns.Add("留言回覆", typeof(String));
dtHZ.Columns.Add("回覆是否顯示", typeof(String));
dtHZ.Columns.Add("商品描述相符度", typeof(String));
dtHZ.Columns.Add("商品質量滿意度", typeof(String));
dtHZ.Columns.Add("配送速度滿意度", typeof(String));
dtHZ.Columns.Add("網站整體滿意度", typeof(String));
dtHZ.Columns.Add("回覆人", typeof(String));
dtHZ.Columns.Add("回覆時間", typeof(String));
dtHZ.Columns.Add("狀態", typeof(String));
for (int i = 0; i < store.Rows.Count; i++)
{
DataRow dr = dtHZ.NewRow();
dr[0] = store.Rows[i]["comment_id"];
string comment_info = store.Rows[i]["product_name"].ToString();
comment_info = comment_info.Replace(',', '，');
comment_info = comment_info.Replace("\n", "");
dr[1] = store.Rows[i]["order_id"].ToString() == "" ? "0" : store.Rows[i]["order_id"];
dr[2] = store.Rows[i]["product_id"];
dr[3] = comment_info;
comment_info = store.Rows[i]["brand_name"].ToString();
comment_info = comment_info.Replace(',', '，');
comment_info = comment_info.Replace("\n", "");
dr[4] = comment_info;
dr[5] = store.Rows[i]["user_name"];
//dr[4] = store[i].user_email;
comment_info = store.Rows[i]["is_show_name"].ToString();
if (comment_info != "")
{
dr[6] = Convert.ToInt32(comment_info) == 0 ? "匿名" : "公開";//0-匿名，1-公開
}
else {
dr[6] = "";
}
comment_info = store.Rows[i]["comment_info"].ToString();
comment_info = comment_info.Replace(',', '，');
comment_info = comment_info.Replace("\n", "");
dr[7] = comment_info;
//dr[6] = store[i].comment_answer;
dr[8] = store.Rows[i]["comment_answer"].ToString().Replace(',', '，').Replace("\r", "").Replace("\n", "");
// dtHZ.Columns.Add("留言回覆", typeof(String));
comment_info = store.Rows[i]["answer_is_show"].ToString();
if (comment_info != "")
{
dr[9] = Convert.ToInt32(comment_info) == 0 ? "隱藏" : "顯示";
}
else {
dr[9] = "";
}
dr[10] = Satisfaction(Convert.ToInt32(store.Rows[i]["product_desc"].ToString() == "" ? "0" : store.Rows[i]["product_desc"].ToString()));//滿意度
dr[11] = Satisfaction(Convert.ToInt32(store.Rows[i]["seller_server"].ToString() == "" ? "0" : store.Rows[i]["seller_server"].ToString()));
dr[12] = Satisfaction(Convert.ToInt32(store.Rows[i]["web_server"].ToString() == "" ? "0" : store.Rows[i]["web_server"].ToString()));
dr[13] = Satisfaction(Convert.ToInt32(store.Rows[i]["logistics_deliver"].ToString() == "" ? "0" : store.Rows[i]["logistics_deliver"].ToString()));
dr[14] = store.Rows[i]["s_reply_user"];
if (!string.IsNullOrEmpty(store.Rows[i]["reply_time"].ToString()))
{
if (CommonFunction.DateTimeToString(Convert.ToDateTime(store.Rows[i]["reply_time"].ToString())) == "1970-01-01 08:00:00")
{
dr[15] = "";
}
else
{
dr[15] = CommonFunction.DateTimeToString(Convert.ToDateTime(store.Rows[i]["reply_time"].ToString()));
}
}
else {
dr[15] = "";
}
comment_info = store.Rows[i]["status"].ToString();
if (comment_info != "")
{
dr[16] = Convert.ToInt32(comment_info) == 1 ? "開啟" : "關閉";//1,開啟。2.關閉
}
else {
dr[16] = "";
}
dtHZ.Rows.Add(dr);
}
if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))
{
System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
}
string newName = string.Empty;
newName = Server.MapPath(excelPath) + fileName;
if (System.IO.File.Exists(newName))
{
//設置文件的屬性，以防刪除文件的時候因為文件的屬性造成無法刪除
System.IO.File.SetAttributes(newName, FileAttributes.Normal);
System.IO.File.Delete(newName);
}
StringWriter sw = ExcelHelperXhf.SetCsvFromData(dtHZ, fileName);
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.Default;
Response.Write(sw);
Response.End();
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,data:[]}";
}
}
public DataTable GetChangeLogList(ProductCommentQuery query, out int totalCount)
{
query.Replace4MySQL();
StringBuilder strSql = new StringBuilder();
StringBuilder SqlWhere = new StringBuilder();
StringBuilder SqlCount = new StringBuilder();
try
{
//SqlCount.AppendFormat("select count(tcl.row_id) as totalCount from table_change_log tcl ");
strSql.AppendFormat(@"select DISTINCT tcl.create_time,tcl.pk_id,tcl.create_user,comment_detail.comment_id,comment_detail.comment_info from table_change_log tcl");
if (!string.IsNullOrEmpty(query.change_table))
{
if (query.change_table == "comment_detail")
{
SqlWhere.Append(" LEFT JOIN comment_detail on tcl.pk_id = comment_detail.comment_detail_id WHERE 1=1 ");
}
else if (query.change_table == "comment_num")
{
SqlWhere.Append(" LEFT JOIN comment_num on tcl.pk_id = comment_num.comment_numid left join comment_detail on comment_num.comment_id=comment_detail.comment_id WHERE 1=1 ");
}
SqlWhere.AppendFormat(" and tcl.change_table='{0}'", query.change_table);
}
if (query.comment_id>0)
{
SqlWhere.AppendFormat(" and comment_detail.comment_id ='{0}'", query.comment_id);
}
if (query.start_time != DateTime.MinValue && query.end_time != DateTime.MinValue)
{
SqlWhere.AppendFormat(" and tcl.create_time between '{0}' and '{1}'",
CommonFunction.DateTimeToString(query.start_time), CommonFunction.DateTimeToString(query.end_time));
}
SqlWhere.Append(" order by tcl.create_time desc");
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable(strSql.ToString() + SqlWhere.ToString());
if (_dt.Rows.Count > 0)
{
//totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
totalCount = _dt.Rows.Count;
SqlWhere.AppendFormat(" limit {0},{1};", query.Start, query.Limit);
}
}
return _access.getDataTable(strSql.ToString() + SqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductCommentDao-->GetChangeLogList-->" + ex.Message + strSql.ToString(), ex);
}
}
/// <summary>
/// 修改評價滿意度
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int ProductCommentSatisfySave(ProductCommentQuery query)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlSearch = new StringBuilder();
query.Replace4MySQL();
int result = 0;
try
{
///獲取主鍵值
sqlSearch.AppendFormat("select comment_numid from comment_num where comment_id='{0}';", query.comment_id);
DataTable _dt = _access.getDataTable(sqlSearch.ToString());
if(_dt.Rows.Count>0)
{
int comment_numid = Convert.ToInt32(_dt.Rows[0]["comment_numid"]);
sql.Append("set sql_safe_updates = 0;");
sql.AppendFormat("update comment_num set logistics_deliver='{0}', web_server='{1}',seller_server='{2}',product_desc='{3}' where comment_id='{4}';", query.logistics_deliver, query.web_server, query.seller_server, query.product_desc, query.comment_id);
if (query.old_logistics_deliver != query.logistics_deliver)
{
sql.AppendFormat("INSERT into table_change_log(user_type,pk_id,change_table,change_field,field_ch_name,old_value,new_value,create_user,create_time)values('1','{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", comment_numid, "comment_num", "logistics_deliver", "配送速度滿意度", query.old_logistics_deliver, query.logistics_deliver, query.reply_user, Common.CommonFunction.DateTimeToString(DateTime.Now));
}
if (query.old_web_server != query.web_server)
{
sql.AppendFormat("INSERT into table_change_log(user_type,pk_id,change_table,change_field,field_ch_name,old_value,new_value,create_user,create_time)values('1','{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", comment_numid, "comment_num", "web_server", "網站整體滿意度", query.old_web_server, query.web_server, query.reply_user, Common.CommonFunction.DateTimeToString(DateTime.Now));
}
if (query.old_seller_server != query.seller_server)
{
sql.AppendFormat("INSERT into table_change_log(user_type,pk_id,change_table,change_field,field_ch_name,old_value,new_value,create_user,create_time)values('1','{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", comment_numid, "comment_num", "seller_server", "商品質量滿意度", query.old_seller_server, query.seller_server, query.reply_user, Common.CommonFunction.DateTimeToString(DateTime.Now));
}
if (query.old_product_desc != query.product_desc)
{
sql.AppendFormat("INSERT into table_change_log(user_type,pk_id,change_table,change_field,field_ch_name,old_value,new_value,create_user,create_time)values('1','{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", comment_numid, "comment_num", "product_desc", "商品描述相符度", query.old_product_desc, query.product_desc, query.reply_user, Common.CommonFunction.DateTimeToString(DateTime.Now));
}
sql.AppendFormat("set sql_safe_updates = 1;");
result = _access.execCommand(sql.ToString());
}
return result;
}
catch (Exception ex)
{
throw new Exception("ProductCommentDao-->ProductCommentSatisfySave-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 評價回覆
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int ProductCommentSave(ProductCommentQuery query)
{
StringBuilder sql = new StringBuilder();
query.Replace4MySQL();
try
{
sql.Append("set sql_safe_updates = 0;");
if (query.send_mail == 2)
{
sql.AppendFormat(@"UPDATE table_change_log,
(SELECT max(row_id) as row_id from table_change_log where pk_id= '{0}' and change_field = 'send_mail') row
set new_value='2' WHERE table_change_log.row_id = row.row_id;", query.comment_detail_id);
}
else
{
sql.AppendFormat("update comment_detail set comment_answer='{0}', answer_is_show='{1}',reply_time='{2}',reply_user='******' where comment_id='{4}';", query.comment_answer, query.answer_is_show, CommonFunction.GetPHPTime(), query.reply_user, query.comment_id);
if(query.old_comment_answer!=query.comment_answer)
{
sql.AppendFormat("INSERT into table_change_log(user_type,pk_id,change_table,change_field,field_ch_name,old_value,new_value,create_user,create_time)values('1','{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", query.comment_detail_id, "comment_detail", "comment_answer", "回覆內容", query.old_comment_answer, query.comment_answer, query.reply_user, Common.CommonFunction.DateTimeToString(DateTime.Now));
}
if (query.old_answer_is_show!=query.answer_is_show.ToString()) //如果修改了是否顯示
{
sql.AppendFormat("INSERT into table_change_log(user_type,pk_id,change_table,change_field,field_ch_name,old_value,new_value,create_user,create_time)values('1','{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", query.comment_detail_id, "comment_detail", "answer_is_show", "回覆內容是否顯示", query.old_answer_is_show, query.answer_is_show.ToString(), query.reply_user, Common.CommonFunction.DateTimeToString(DateTime.Now));
}
if (query.send_mail==0) //如果發送郵件
{
sql.AppendFormat("INSERT into table_change_log(user_type,pk_id,change_table,change_field,field_ch_name,old_value,new_value,create_user,create_time)values('1','{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", query.comment_detail_id, "comment_detail", "send_mail", "是否發送郵件", "",query.send_mail.ToString(), query.reply_user, Common.CommonFunction.DateTimeToString(DateTime.Now));
}
}
sql.AppendFormat("set sql_safe_updates = 1;");
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductCommentDao-->ProductCommentSave-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 更改狀態
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateActive(ProductCommentQuery model)
{
string strSql = string.Empty;
try
{
strSql = string.Format("set sql_safe_updates = 0; update comment_detail set status='{0}' where comment_id='{1}';update comment_num set status='{0}' where comment_id='{1}'; set sql_safe_updates = 1;", model.status, model.comment_id);
return _access.execCommand(strSql);
}
catch (Exception ex)
{
throw new Exception("ProductCommentDao-->UpdateActive-->" + ex.Message + strSql, ex);
}
}
public DataTable ProductCommentLogExport(ProductCommentQuery query)
{
DataTable result = new DataTable();
result.Columns.Add("評價編號", typeof(String));
result.Columns.Add("評價內容", typeof(String));
result.Columns.Add("修改人", typeof(String));
result.Columns.Add("修改時間", typeof(String));
result.Columns.Add("變動欄位", typeof(String));
result.Columns.Add("欄位中文名", typeof(String));
result.Columns.Add("修改前值", typeof(String));
result.Columns.Add("修改后值", typeof(String));
// string[] colname = { "", "建立人", "建立時間", "變動欄位", "欄位中文名", "修改前值", "修改后值" };
int totalCount = 0;
query.IsPage = false;
DataTable _dt = GetChangeLogList(query, out totalCount);
if (_dt.Rows.Count > 0)
{
foreach (DataRow item in _dt.Rows)
{
DataRow dr = result.NewRow();
dr[0] = item["comment_id"].ToString();
dr[1] = item["comment_info"].ToString();
dr[1] = dr[1].ToString().Replace(',', '，').Replace("\r", "").Replace("\n", "");
dr[2] = item["user_name"].ToString();
dr[3] = item["create_time"].ToString();
int pk_id = Convert.ToInt32(item["pk_id"]);
string create_time = Common.CommonFunction.DateTimeToString(Convert.ToDateTime(item["create_time"]));
Model.Custom.TableChangeLogCustom tclc = GetChangeLogDetailList(pk_id,Convert.ToInt32(dr[0]), create_time);
dr[4] = tclc.tclModel[0].change_field.ToString();
dr[5] = tclc.tclModel[0].field_ch_name.ToString();
dr[6] = tclc.tclModel[0].old_value.ToString();
dr[7] = tclc.tclModel[0].new_value.ToString();
dr[6] = dr[6].ToString().Replace(',', '，').Replace("\r", "").Replace("\n", "");
dr[7] = dr[7].ToString().Replace(',', '，').Replace("\r", "").Replace("\n", "");
result.Rows.Add(dr);
for (int i = 1; i < tclc.tclModel.Count; i++)
{
DataRow dr2 = result.NewRow();
dr2[4] = tclc.tclModel[i].change_field.ToString();
dr2[5] = tclc.tclModel[i].field_ch_name.ToString();
dr2[6] = tclc.tclModel[i].old_value.ToString();
dr2[7] = tclc.tclModel[i].new_value.ToString();
dr2[6] = dr2[6].ToString().Replace(',', '，').Replace("\r", "").Replace("\n", "");
dr2[7] = dr2[7].ToString().Replace(',', '，').Replace("\r", "").Replace("\n", "");
result.Rows.Add(dr2);
}
}
}
return result;
}
public DataTable GetChangeLogList(ProductCommentQuery query, out int totalCount)
{
try
{
DataTable _dt = _proCommentDao.GetChangeLogList(query, out totalCount);
_dt.Columns.Add("user_name");
if (_dt.Rows.Count > 0)
{
foreach (DataRow item in _dt.Rows)
{
Dao.ManageUserDao _muDao = new Dao.ManageUserDao(connStr);
if (!string.IsNullOrEmpty(item["create_user"].ToString()) && item["create_user"].ToString() != "0")
{
item["user_name"] = _muDao.GetManageUser(new Model.ManageUser { user_id = Convert.ToUInt32(item["create_user"].ToString()) }).FirstOrDefault().user_username;
}
}
}
return _dt;
//return _proCommentDao.GetChangeLogList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("ProductCommentMgr.GetChangeLogList-->" + ex.Message, ex);
}
}
