public List<EdmContentNew> GetECNList(EdmContentNew query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
query.Replace4MySQL();
totalCount = 0;
try
{
sqlCount.AppendFormat("select count(edn.content_id) as countTotal ");
sql.AppendFormat("select edn.content_id,edn.group_id,`subject`,esl.count,esl.date,edn.sender_id,ms.sender_email,ms.sender_name,edn.importance,edn.template_id,edn.template_data,'' as 'template_data_send', et.edit_url,et.content_url , edn.pm, para.parameterName 'edm_pm',et.static_template,mu.user_username 'user_username_create',mu2.user_username 'user_username_update' ");
sqlFrom.AppendFormat("from edm_content_new edn LEFT JOIN (SELECT content_id,COUNT(content_id) as count,MAX(schedule_date) as date from edm_send_log WHERE test_send=0 GROUP BY content_id) esl ON edn.content_id=esl.content_id LEFT JOIN mail_sender ms on edn.sender_id=ms.sender_id LEFT JOIN edm_template et on et.template_id=edn.template_id ");
sqlFrom.Append(" left join (select parameterCode,parameterName from t_parametersrc where parameterType='edm_pm_name' and used=1) para on edn.pm=para.parameterCode ");
sqlFrom.Append(" left join manage_user mu on mu.user_id=edn.content_create_userid ");
sqlFrom.Append(" left join manage_user mu2 on mu2.user_id=edn.content_update_userid ");
sqlWhere.AppendFormat(" where 1=1 ");
sqlWhere.AppendFormat(" and edn.content_createdate between '{0}' and '{1}' ", CommonFunction.DateTimeToString(DateTime.Now.AddDays(-5)), CommonFunction.DateTimeToString(DateTime.Now));
if (query.group_id != 0)
{
sqlWhere.AppendFormat(" and edn.group_id='{0}' ", query.group_id);
}
if (query.content_id != 0)
{
sqlWhere.AppendFormat(" and edn.content_id='{0}' ", query.content_id);
}
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0][0]);
}
sqlWhere.AppendFormat(" order by edn.content_id desc limit {0},{1}; ", query.Start, query.Limit);
return _access.getDataTableForObj<EdmContentNew>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentNewDao-->GetECNList-->" + sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString() + ex.Message, ex);
}
}
public int UpdateEdmContentNew(EdmContentNew query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
query.Replace4MySQL();
try
{
sql.AppendFormat("update edm_content_new set group_id='{0}',subject='{1}',template_id='{2}',template_data='{3}',importance='{4}',pm='{5}', ", query.group_id, query.subject, query.template_id, query.template_data, query.importance,query.pm);
sql.AppendFormat(" sender_id='{0}',content_updatedate='{1}',content_update_userid='{2}' where content_id='{3}';", query.sender_id, CommonFunction.DateTimeToString(DateTime.Now), query.content_update_userid, query.content_id);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentNewDao-->InsertEdmContentNew-->" + ex.Message + ";sql:" + sql.ToString(), ex);
}
}
public int InsertEdmContentNew(EdmContentNew query)
{
query.Replace4MySQL(); StringBuilder sql = new StringBuilder();
try
{
sql.Append("insert into edm_content_new(group_id,`subject`,template_id,template_data, ");
sql.Append("importance,sender_id,content_createdate,content_updatedate,content_create_userid,content_update_userid,pm) ");
sql.AppendFormat("values('{0}','{1}','{2}','{3}',", query.group_id, query.subject, query.template_id, query.template_data);
sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}');", query.importance, query.sender_id, CommonFunction.DateTimeToString(DateTime.Now), CommonFunction.DateTimeToString(DateTime.Now), query.content_create_userid, query.content_update_userid,query.pm);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentNewDao-->InsertEdmContentNew-->" + ex.Message + ";sql:" + sql.ToString(), ex);
}
}
