public DataTable GetCourseCountList(CourseQuery query, out int totalCount)
{
try
{
DataTable course_dt = _tmDao.GetCourseCountList(query, out totalCount);
course_dt.Columns.Add("ticket_detail_id");
course_dt.Columns.Add("spec_name_1");
course_dt.Columns.Add("spec_name_2");
course_dt.Columns.Add("vendor_name_simple");
course_dt.Columns.Add("sales_number");
course_dt.Columns.Add("used_number");
if (course_dt != null)
{
VendorBrandDao _vbDao = new VendorBrandDao(connStr);
ProductSpecDao _psDao = new ProductSpecDao(connStr);
CourseTicketDao _ctDao = new CourseTicketDao(connStr);
foreach (DataRow item in course_dt.Rows)
{
//獲取供應商名稱
item["vendor_name_simple"] = _vbDao.GetBandList(string.Format(" and vb.brand_id='{0}'", item["brand_id"])).Rows[0]["vendor_name_simple"];
item["spec_name_1"] = _psDao.query(Convert.ToInt32(item["spec_id_1"])).spec_name;
item["spec_name_2"] = _psDao.query(Convert.ToInt32(item["spec_id_2"])).spec_name;
DataTable s_dt = _ctDao.GetCount(Convert.ToInt32(item["item_id"]));
if (s_dt.Rows.Count > 0)
{
item["sales_number"] = s_dt.Rows[0]["number"];
item["ticket_detail_id"] = s_dt.Rows[0]["ticket_detail_id"];
}
DataTable u_dt = _ctDao.GetCount(Convert.ToInt32(item["item_id"]), 1);
if (u_dt.Rows.Count > 0)
{
item["used_number"] = u_dt.Rows[0]["number"];
}
}
}
return course_dt;
}
catch (Exception ex)
{
throw new Exception("TicketMasterMgr-->GetCourseCountList-->" + ex.Message, ex);
}
}
public HttpResponseBase GetCourseCountList()
{
CourseQuery query = new CourseQuery();
string json = string.Empty;
int isTranInt = 0;
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["select_vendor"]))
{
query.Vendor_Id = int.Parse(Request.Params["select_vendor"]);
}
if (!string.IsNullOrEmpty(Request.Params["select_content"]))
{
if (int.TryParse(Request.Params["select_content"].Trim(), out isTranInt))
{
query.Course_Id = Convert.ToInt32(Request.Params["select_content"].Trim());
}
else
{
query.Course_Name = (Request.Params["select_content"]).Trim();
}
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
query.Start_Date = Convert.ToDateTime(Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00"));
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
query.End_Date = Convert.ToDateTime(Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59"));
}
int totalCount = 0;
_ITicketMaster = new TicketMasterMgr(mySqlConnectionString);
DataTable _dt = _ITicketMaster.GetCourseCountList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(_dt, Formatting.Indented, timeConverter) + "}";
}
catch (Exception ex)
{
Vendor.Log4NetCustom.LogMessage logMessage = new Vendor.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);
json = "{success:false,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json.ToString());
this.Response.End();
return this.Response;
}
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);
}
}