public List<ElementDetailQuery> QueryAll(ElementDetailQuery query, out int totalCount)
{
try
{
query.Replace4MySQL();
StringBuilder TempCol = new StringBuilder("SELECT p.product_id,ap.element_type,ap.packet_status,p.product_name,p.product_status,ap.packet_name, bd.element_id,ap.element_type,");//c.parametername as element_type_name,
TempCol.Append(" bd.element_content,bd.packet_id,bd.element_name,bd.element_link_url,bd.element_link_mode,bd.element_remark,bd.element_sort,bd.element_status, bd.element_link_mode,");//d.parametername as element_linkmode,
TempCol.Append(" bd.element_start,bd.element_end,bd.element_createdate,bd.element_updatedate,bd.category_id,bd.category_name,bd.element_img_big ");
StringBuilder tempCount = new StringBuilder("select count(bd.element_id) as totalCount ");
StringBuilder mainSql = new StringBuilder(" FROM element_detail bd ");
mainSql.Append(" join area_packet ap on ap.packet_id=bd.packet_id");
mainSql.Append(" LEFT JOIN product p ON p.product_id = bd.element_content ");
// mainSql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_type') c on ap.element_type=c.parametercode");
// mainSql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_link_mode') d on bd.element_link_mode=d.parametercode");
StringBuilder condi = new StringBuilder();
condi.Append(" and element_status!=2 ");
if (query.packet_id != 0)
{
condi.AppendFormat(" and ap.packet_id={0} ", query.packet_id);
}
if (query.element_type != 0)
{
condi.AppendFormat(" and ap.element_type={0} ", query.element_type);
}
if (!string.IsNullOrEmpty(query.key))
{
condi.AppendFormat(" and ( bd.element_name like N'%{0}%' ", query.key);
condi.AppendFormat(" or bd.element_remark like N'%{0}%' ", query.key);
condi.AppendFormat(" or ap.packet_name like N'%{0}%' ", query.key);
condi.AppendFormat(" or bd.element_content like N'%{0}%') ", query.key);
}
if (!string.IsNullOrEmpty(query.searchcate))
{
condi.AppendFormat(" and bd.category_name like N'%{0}%' ", query.searchcate);
}
if (query.product_status != 0 && query.element_type == 3)
{
if (query.product_status == 5)
{
condi.Append(" and p.product_status=5 ");
}
else
{
condi.AppendFormat(" and p.product_status <> 5 ");
}
}
if (condi.Length > 0)
{
mainSql.Append(" where ");
mainSql.Append(condi.ToString().TrimStart().Remove(0, 3));
}
mainSql.AppendFormat(" order by bd.element_id desc ");
totalCount = 0;
if (query.IsPage)
{
sql.Append(tempCount.ToString() + mainSql.ToString());
System.Data.DataTable _dt = _access.getDataTable(sql.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
mainSql.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
IParametersrcImplDao _paradao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _paradao.QueryParametersrcByTypes("element_type", "element_link_mode");
List<ElementDetailQuery> list = _access.getDataTableForObj<ElementDetailQuery>(TempCol.ToString() + mainSql.ToString());
foreach (ElementDetailQuery q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "element_type" && m.ParameterCode == q.element_type.ToString());
var blist = parameterList.Find(m => m.ParameterType == "element_link_mode" && m.ParameterCode == q.element_link_mode.ToString());
if (alist != null)
{
q.element_type_name = alist.parameterName;
}
if (blist != null)
{
q.element_linkmode = blist.parameterName;
}
}
return list;
}
catch (Exception ex)
{
throw new Exception("ElementDetailDao-->QueryAll-->" + ex.Message + sql.ToString(), ex);
}
}
public List<ElementDetailQuery> QueryAllWares(ElementDetailQuery query, out int totalCount)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
StringBuilder sqlContent = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
try
{
sqlCount.AppendFormat(" select count(ed.element_id) as totalCount ");
sqlContent.AppendFormat("select p.product_id, c.parametername as element_type,ed.element_id,ed.element_name,ed.category_id,ed.category_name,ed.element_content,ed.element_link_url,d.parametername as element_link_mode,ed.element_sort,ed.element_status,ed.element_start,ed.element_end,ed.element_remark ");
sql.Append(" from element_detail ed ");
sql.Append(" join area_packet ap on ap.packet_id=ed.packet_id ");
sql.Append(" LEFT JOIN product p ON p.product_id = ed.element_content ");
sql.Append(" left join (select parametername,parametercode from t_parametersrc where parametertype='element_type') c on ap.element_type=c.parametercode");
sql.Append(" left join ( select parametername,parametercode from t_parametersrc where parametertype='element_link_mode') d on ed.element_link_mode=d.parametercode");
sqlWhere.Append(" where ap.element_type=3 ");
if (query.packet_id != 0)
{
sqlWhere.AppendFormat(" and packet_id={0}", query.packet_id);
}
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sql.ToString() + sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
sqlWhere.AppendFormat(" limit {0},{1} ;", query.Start, query.Limit);
}
return _access.getDataTableForObj<ElementDetailQuery>(sqlContent.ToString() + sql.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("ElementDetailDao-->QueryAllWares-->" + sqlContent.ToString() + sql.ToString() + sqlWhere.ToString(), ex);
}
}
public int UpdateStatus(ElementDetailQuery model)
{
model.Replace4MySQL();
try
{
sql.AppendFormat(@"UPDATE element_detail set element_status='{1}',element_updatedate='{2}' ,update_userid='{3}' where element_id='{0}' ",
model.element_id, model.element_status, CommonFunction.DateTimeToString(model.element_updatedate), model.update_userid);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("ElementDetailDao-->UpdateStatus-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 更改活動使用狀態
/// </summary>
/// <returns>數據庫操作結果</returns>
public JsonResult UpdateDetailStatus()
{
string jsonStr = string.Empty;
try
{
_detailMgr = new ElementDetailMgr(mySqlConnectionString);
int id = Convert.ToInt32(Request.Params["id"]);
int statusValue = Convert.ToInt32(Request.Params["status"]);
ElementDetailQuery model = new ElementDetailQuery();
model.element_id = id;
model.element_status = statusValue;
model.update_userid = int.Parse((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString());
model.element_updatedate = DateTime.Now;
if (_detailMgr.UpdateStatus(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" });
}
}
public HttpResponseBase GetDetailList()
{
string json = string.Empty;
try
{
List<ElementDetailQuery> detailStore = new List<ElementDetailQuery>();
ElementDetailQuery query = new ElementDetailQuery();
#region 獲取query對象數據
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["product_status"]))
{
query.product_status = uint.Parse(Request.Params["product_status"]);
query.element_type = 3;
}
if (!string.IsNullOrEmpty(Request.Params["packet_id"]))
{
query.packet_id = int.Parse(Request.Params["packet_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["serchcontent"]))
{
query.key = Request.Params["serchcontent"].Trim();
}
if (!string.IsNullOrEmpty(Request.Params["searchCate"]))
{
query.searchcate = Request.Params["searchCate"].Trim();
}
if (!string.IsNullOrEmpty(Request.Params["search_type"]))
{
query.element_type = int.Parse(Request.Params["search_type"]);
}
#endregion
_detailMgr = new ElementDetailMgr(mySqlConnectionString);
query.create_userid = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
int totalCount = 0;
detailStore = _detailMgr.QueryAll(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
foreach (var item in detailStore)
{
if (item.element_type == 1)
{
if (item.element_content != "")
{
item.element_content = imgServerPath + ElementPath + item.element_content;
}
if (item.element_img_big != "")
{
item.element_img_big = imgServerPath + ElementPath + item.element_img_big;
}
else
{
item.element_img_big = defaultImg;
}
}
if (item.element_type == 2)
{
if (item.element_content != "")
{
item.kendo_editor = Server.HtmlDecode(Server.HtmlDecode(item.element_content));
}
}
if (item.element_type == 3)
{
if (item.element_img_big != "")
{
item.element_img_big = imgServerPath + ElementPath + item.element_img_big;
}
else
{
item.element_img_big = defaultImg;
}
}
if (item.category_name != "")
{
item.category_name = Server.HtmlDecode(Server.HtmlDecode(item.category_name));
}
}
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(detailStore, 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:false,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public List<ElementDetailQuery> QueryAllWares(ElementDetailQuery query, out int totalCount)
{
try
{
return _detaildao.QueryAllWares(query,out totalCount);
}
catch (Exception ex)
{
throw new Exception("ElementDetailMgr-->QueryAllWares-->" + ex.Message, ex);
}
}
