예제 #1
0
        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);
            }

        }
예제 #2
0
 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;
 }
예제 #3
0
        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);
            }

        }