public bool ExecuteAll(Course c, List<CourseDetail> cdList,List<CoursePicture> plist)
{
ArrayList list = new ArrayList();
try
{
int course_id = GetCourseId(c);
//獲得操作Course表的sql語句
List<string> listStr = GetSqlByCourseDetail(cdList, course_id);
//獲取操作圖片的sql語句
List<string> plistStr = GetSqlByPicture(plist, course_id);
if (listStr.Count != 0)
{
list.AddRange(listStr);
}
if (plistStr.Count != 0)
{
list.AddRange(plistStr);
}
if (list.Count != 0)
{
return _courseImpl.SaveAll(list);
}
else
{
return true;
}
}
catch (Exception ex)
{
throw new Exception("CourseMgr-->SaveAll" + ex.Message,ex);
}
}
public int GetCourseId(Course c)
{
if (c==null)
return 0;
if (c.Course_Id == 0)
{
return Save(c);
}
else
{
Update(c);
return c.Course_Id; //得到course_id的值,用於後面課程細項的新增和更新
}
}
/// <summary>
/// 保存課程
/// </summary>
/// <param name="c">一個Course對象</param>
/// <returns>受影響的行數</returns>
public int Save(Course c)
{
StringBuilder sb = new StringBuilder();
try
{
sb.Append("INSERT INTO course(`course_name`,`tel`,`send_msg`,`msg`,`send_mail`,`mail_content`,`start_date`,`end_date`,`create_time`,`source`,`ticket_type`)");
sb.AppendFormat("VALUES('{0}','{1}',{2},'{3}',{4},'{5}',",c.Course_Name,c.Tel,c.Send_Msg,c.Msg,c.Send_Mail,c.Mail_Content);
sb.AppendFormat("'{0}','{1}','{2}',{3},{4});SELECT @@identity", c.Start_Date.ToString("yyyy-MM-dd HH:mm:ss"), c.End_Date.ToString("yyyy-MM-dd HH:mm:ss"), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), c.Source, c.Ticket_Type);
return Convert.ToInt32(_dbAccess.getDataTable(sb.ToString()).Rows[0][0]);
}
catch (Exception ex)
{
throw new Exception("CourseDao-->Save" + ex.Message,ex);
}
}
public List<Course> Query(Course c)
{
StringBuilder sb = new StringBuilder();
try
{
sb.Append("SELECT course_id,course_name,tel,send_msg,msg,send_mail,mail_content,start_date,end_date,create_time,source,ticket_type FROM course WHERE 1=1");
if (c.Course_Id != 0)
{
sb.AppendFormat(" AND course_id = {0}", c.Course_Id);
}
if (!string.IsNullOrEmpty(c.Course_Name))
{
sb.AppendFormat(" AND course_name like '%{0}%'", c.Course_Name);
}
return _dbAccess.getDataTableForObj<Course>(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("CourseDao-->Query" + ex.Message, ex);
}
}
/// <summary>
/// 獲取課程data
/// </summary>
/// <returns></returns>
public ActionResult GetCurricul(Course c)
{
try
{
int count = 0;
c.Start = int.Parse(Request.Form["start"] ?? "0");
if (Request["limit"] != null)
{
c.Limit = Convert.ToInt32(Request["limit"]);
}
ICourseImplMgr _courseMgr = new CourseMgr(connectionString);
bool isPage = Convert.ToBoolean(Request["IsPage"]);
if (isPage)
{
var course = _courseMgr.Query(c, out count);
IsoDateTimeConverter iso = new IsoDateTimeConverter() { DateTimeFormat = "yyyy-MM-dd HH:mm:ss" };
return Content("{succes:true,totalCount:" + count + ",item:" + JsonConvert.SerializeObject(course, Formatting.None, iso) + "}");
}
else
{
var course = _courseMgr.Query(c);
return Json(course);
}
}
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 new EmptyResult();
}
}
public List<Course> Query(Course c,out int totalCount)
{
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();//add by wwei0216w 2015/4/7 添加查詢總數的sql語句
try
{
sb.Append("SELECT course_id,course_name,tel,send_msg,msg,send_mail,mail_content,start_date,end_date,create_time,source,ticket_type FROM course WHERE 1=1 ");
sb2 = sb2.Append("SELECT COUNT(course_id) AS totalCount FROM course WHERE 1=1 ");
if(c.Course_Id !=0)
{
sb.AppendFormat(" AND course_id = {0}", c.Course_Id);
sb2.AppendFormat(" AND course_id = {0}", c.Course_Id);
}
if(!string.IsNullOrEmpty(c.Course_Name))
{
sb.AppendFormat(" AND course_name like '%{0}%'",c.Course_Name);
sb2.AppendFormat(" AND course_name like '%{0}%'", c.Course_Name);
}
sb.AppendFormat(" ORDER BY create_time DESC limit {0},{1}", c.Start, c.Limit);
sb2.Append(" ORDER BY create_time DESC "); ///edit by wwei0216w 2015/7/31 條換order by 的位置 修改mysql語法報錯的問題
System.Data.DataTable _dt = _dbAccess.getDataTable(sb2.ToString());
totalCount = 0;
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
return _dbAccess.getDataTableForObj<Course>(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("CourseDao-->Query" + ex.Message, ex);
}
}
public int Update(Course c)
{
return _courseImpl.Update(c);
}
public List<Course> Query(Course c)//add by wwei0216w 2015/04/10 添加一個無需分頁的查詢
{
return _courseImpl.Query(c);
}
public List<Course> Query(Course c, out int totalCount)
{
return _courseImpl.Query(c,out totalCount);
}
public int Save(Course c)
{
return _courseImpl.Save(c);
}
public int Update(Course c)
{
StringBuilder sb = new StringBuilder();
try
{
sb.Append(@"SET sql_safe_updates = 0;UPDATE course c SET ");
sb.AppendFormat(" c.msg = '{0}',", c.Msg);
sb.AppendFormat(" c.mail_content = '{0}',", c.Mail_Content);
if (!string.IsNullOrEmpty(c.Course_Name))
{
sb.AppendFormat(" c.course_name = '{0}',", c.Course_Name);
}
if (!string.IsNullOrEmpty(c.Tel))
{
sb.AppendFormat(" c.tel = '{0}',", c.Tel);
}
if(c.Start_Date != DateTime.MinValue)
{
sb.AppendFormat(" c.start_date = '{0}',", c.Start_Date.ToString("yyyy-MM-dd HH:mm:ss"));
}
if(c.End_Date != DateTime.MinValue)
{
sb.AppendFormat(" c.end_date = '{0}',", c.End_Date.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (c.Send_Msg != -1)
{
sb.AppendFormat(" c.send_msg = {0},", c.Send_Msg);
}
if(c.Send_Mail!=-1)
{
sb.AppendFormat(" c.send_mail = {0},", c.Send_Mail);
}
if(c.Source!=-1)
{
sb.AppendFormat(" c.source = {0},", c.Source);
}
if(c.Ticket_Type != -1)
{
sb.AppendFormat(" c.ticket_type = {0},", c.Ticket_Type);
}
sb.Append(" c.course_id = c.course_id,");
string strSql = sb.ToString().Remove(sb.ToString().Length-1,1);
string strWhere = string.Format(" WHERE c.course_id = {0}", c.Course_Id);
strSql += strWhere;
strSql += "; SET sql_safe_updates = 1;";
return _dbAccess.execCommand(strSql);
}
catch (Exception ex)
{
throw new Exception("CourseDao-->Update" + ex.Message, ex);
}
}