public List<EdmGroup> GetEdmGroupList(EdmGroup query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
totalCount = 0;
try
{
query.Replace4MySQL();
sqlCount.Append("select count(eg.group_id) as totalCount ");
sql.Append(" select eg.group_id,eg.group_name,eg.group_total_email,eg.group_createdate,eg.group_updatedate,count(ec.content_id) as total_content ");
sqlFrom.Append("from edm_group eg ");
sqlFrom.Append(" left join edm_content ec on eg.group_id = ec.group_id ");
sqlWhere.Append(" where 1=1 ");
if (query.selectType == "0"&&!string.IsNullOrEmpty(query.search_con))
{
sqlWhere.AppendFormat(" and eg.group_id like '%{0}%'",query.search_con.ToString());
}
if (query.selectType == "1" && !string.IsNullOrEmpty(query.search_con))
{
sqlWhere.AppendFormat(" and eg.group_name like '%{0}%'", query.search_con.ToString());
}
if (query.dateCondition != -1)
{
if (query.dateCondition == 0)
{
sqlWhere.AppendFormat("AND group_createdate BETWEEN {0} AND {1}", query.start, query.end);
}
if (query.dateCondition == 1)
{
sqlWhere.AppendFormat("AND group_updatedate BETWEEN {0} AND {1}", query.start, query.end);
}
}
sqlWhere.AppendFormat(" group by eg.group_id, eg.group_name, eg.group_total_email, eg.group_createdate, eg.group_updatedate order by eg.group_id asc ");
if (query.IsPage)
{
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = _dt.Rows.Count;
}
sqlWhere.AppendFormat(" limit {0},{1};", query.Start, query.Limit);
}
return _access.getDataTableForObj<EdmGroup>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmGroupDao-->GetEdmGroupList-->" + ex.Message + sql.ToString() + sqlWhere.ToString(), ex);
}
}
public int UpEdmGroup(EdmGroup query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.AppendFormat("set sql_safe_updates=0;update edm_group set group_name ='{0}',group_updatedate='{1}' where group_id='{2}';set sql_safe_updates=1;", query.group_name, CommonFunction.GetPHPTime(), query.group_id);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmGroupDao-->UpEdmGroup-->" + ex.Message + sql.ToString(), ex);
}
}
