public DataTable GetCourseCountList(CourseQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
totalCount = 0;
try
{
query.Replace4MySQL();
sql.Append(@"SELECT c.course_id,c.course_name ,pi.item_id,p.brand_id,pi.spec_id_1,pi.spec_id_2,cd.start_date,cd.end_date ");
sqlFrom.Append(@" FROM course c LEFT JOIN course_detail cd ON cd.course_id=c.course_id
LEFT JOIN course_product cp ON cp.course_id=c.course_id
LEFT JOIN product p ON p.product_id=cp.product_id
LEFT JOIN product_item pi ON pi.product_id=p.product_id ");
// sqlFrom.Append(@" FROM product_item pi
// LEFT JOIN product p ON p.product_id=pi.product_id
// LEFT JOIN course_product cp ON cp.product_id=p.product_id
// LEFT JOIN course c ON c.course_id=cp.course_id
// LEFT JOIN course_detail cd ON cd.course_id=c.course_id");
if (!string.IsNullOrEmpty(query.Vendor_Name_Simple))
{
sqlWhere.AppendFormat("and v.vendor_name_simple like '%{0}%' ", query.Vendor_Name_Simple);
}
if (query.Course_Id != 0)
{
sqlWhere.AppendFormat("and c.course_id='{0}' ", query.Course_Id);
}
if (!string.IsNullOrEmpty(query.Course_Name))
{
sqlWhere.AppendFormat("and c.course_name like '%{0}%' ", query.Course_Name);
}
if (query.Start_Date != DateTime.MinValue)
{
sqlWhere.AppendFormat("and cd.start_date >='{0}' ", query.Start_Date);
}
if (query.End_Date != DateTime.MinValue)
{
sqlWhere.AppendFormat("and cd.end_date <='{0}' ", query.End_Date);
}
if (sqlWhere.Length != 0)
{
sqlFrom.Append(" WHERE " + sqlWhere.ToString().TrimStart().Remove(0, 3));
}
if (query.IsPage)
{
DataTable _dtCount = _access.getDataTable("select count(c.course_id) as totalCount " + sqlFrom.ToString());
if (_dtCount.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dtCount.Rows[0]["totalCount"]);
}
sqlFrom.AppendFormat("LIMIT {0},{1} ;", query.Start, query.Limit);
}
sql.Append(sqlFrom.ToString());
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("TicketMasterDao-->GetCourseCountList-->" + sql.ToString() + ex.Message, ex);
}
}