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); } }