public List<EdmContentQuery> GetEdmContentList(EdmContentQuery store, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlLimit = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
totalCount = 0;
try
{
store.Replace4MySQL();
//sqlCount.AppendFormat("select count(*) as totalCount FROM edm_content edm left join epaper_content ec on edm.info_epaper_id=ec.epaper_id ");
//sql.AppendFormat("SELECT edm.content_id,edm.group_id,edm.content_status,edm.content_start, edm.content_click,edm.content_person,edm.content_send_success,edm.content_send_failed,edm.content_from_name,edm.content_from_email,edm.content_reply_email,edm.content_body,edm.content_priority,edm.content_title,edm.content_createdate,edm.content_updatedate,ec.epaper_title,edm.info_epaper_id FROM edm_content edm left join epaper_content ec on edm.info_epaper_id=ec.epaper_id ");
//sqlWhere.AppendFormat(" where 1=1 ");
sqlCount.AppendFormat("select count(edm.content_id) as totalCount ");
sql.AppendFormat("SELECT edm.content_id,edm.group_id,edm.content_status,edm.content_start, edm.content_click,edm.content_person,edm.content_send_success,edm.content_send_failed,edm.content_from_name,edm.content_from_email,edm.content_reply_email,edm.content_body,edm.content_priority,edm.content_title,edm.content_createdate,edm.content_updatedate,ec.epaper_title,edm.info_epaper_id ");
sqlWhere.Append(" FROM edm_content edm ");
sqlWhere.Append(" left join epaper_content ec on edm.info_epaper_id=ec.epaper_id ");
sqlWhere.AppendFormat(" where 1=1 ");
if (store.search_text != "")
{
sqlWhere.AppendFormat(" and edm.content_title like N'%{0}%'", store.search_text);
}
if (store.searchStatus != "0")
{
sqlWhere.AppendFormat(" and edm.content_status= '{0}'", store.searchStatus);
}
if (store.s_content_start != DateTime.MinValue && store.s_content_end != DateTime.MinValue)
{
sqlWhere.AppendFormat(" and edm.content_start between '{0}' and '{1}'", CommonFunction.GetPHPTime(store.s_content_start.ToString()), CommonFunction.GetPHPTime(store.s_content_end.ToString()));
}
if (store.IsPage)
{
sqlCount.Append(sqlWhere.ToString());
DataTable _dt = _accessMySql.getDataTable(sqlCount.ToString() );
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
}
sqlWhere.AppendFormat(" ORDER BY content_id DESC ");
sqlLimit.AppendFormat(" limit {0},{1};", store.Start, store.Limit);
sql.AppendFormat(sqlWhere.ToString());
sql.AppendFormat(sqlLimit.ToString());
return _accessMySql.getDataTableForObj<EdmContentQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentDao-->GetEdmContentList-->" + ex.Message + sql.ToString()+sqlCount.ToString(), ex);
}
}
public string InsertEdmContent(EdmContentQuery store)
{
store.Replace4MySQL();
StringBuilder sql = new StringBuilder();
sql.AppendFormat("insert into edm_content (content_id,group_id,content_status,content_email_id,content_start, ");
sql.AppendFormat("content_end,content_range,content_single_count,content_click,content_person,");
sql.AppendFormat("content_from_name,content_from_email,content_reply_email,content_priority,content_title,");
sql.AppendFormat("content_body,content_createdate,content_updatedate,info_epaper_id)");
sql.AppendFormat(" values({0},{1},{2},{3},{4},", store.serialvalue, store.group_id, store.content_status, store.content_email_id, store.content_start);
sql.AppendFormat("{0},{1},{2},{3},{4},", store.content_end, store.content_range, store.content_single_count, store.content_click, store.content_person);
sql.AppendFormat("'{0}','{1}','{2}',{3},'{4}',", store.content_from_name, store.content_from_email, store.content_reply_email, store.content_priority, store.content_title);
sql.AppendFormat("'{0}',{1},{2},'{3}');", store.content_body, CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")), CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")), store.info_epaper_id);
return sql.ToString();
}
public int EdmContentSave(EdmContentQuery store)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sql = new StringBuilder();
int re = 0;
if (store.content_id == 0)//新增
{
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
store.serialvalue = GetSerialValue();
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateSerialVal(store.serialvalue);
mySqlCmd.CommandText += InsertEdmContent(store);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("EdmContentDao-->EdmContentSave-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}
else //編輯
{
try
{
store.Replace4MySQL();
sql.AppendFormat("update edm_content set group_id={0},content_status={1},content_start={2},", store.group_id, store.content_status, store.content_start);
sql.AppendFormat("content_from_name='{0}',content_from_email='{1}',content_reply_email='{2}',content_priority={3},info_epaper_id='{4}',", store.content_from_name, store.content_from_email, store.content_reply_email, store.content_priority, store.info_epaper_id);
sql.AppendFormat("content_title='{0}',content_body='{1}',content_updatedate={2} where content_id={3};", store.content_title, store.content_body, CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")), store.content_id);
return _accessMySql.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentDao-->EdmContentSave-->" + sql.ToString() + ex.Message, ex);
}
}
}