Beispiel #1
0
        /// <summary>
        /// 新站訂單詳情管理:列表頁
        /// </summary>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
         public DataTable GetTicketDetailTable(TicketDetailQuery query, out int totalCount)
         { 
             StringBuilder sql = new StringBuilder();
             StringBuilder sqlCondi = new StringBuilder();
             try
             {
                query.Replace4MySQL();
                sql.AppendFormat(@" select td.ticket_detail_id,td.ticket_master_id,td.single_cost,td.single_money,td.single_price,td.vendor_id,cd.course_id,td.product_name,
 cd.start_date,cd.end_date ");
                sql.AppendFormat(" ,(SELECT spec_name from product_spec ps where pi.product_id=ps.product_id and pi.spec_id_1=ps.spec_id )as spec_id_1 ");
                sql.AppendFormat(" ,(SELECT spec_name from product_spec ps where pi.product_id=ps.product_id and pi.spec_id_2=ps.spec_id )as spec_id_2 ");
                sql.AppendFormat(" ,(SELECT vendor_name_simple FROM vendor v WHERE v.vendor_id=td.vendor_id)AS vendor_name_simple,ct.flag,ct.ticket_code ");
                sqlCondi.AppendFormat("  from ticket_detail td ");
                sqlCondi.AppendFormat("  LEFT JOIN course_detail_item cdi ON cdi.course_detail_item_id=td.cd_item_id ");
                sqlCondi.AppendFormat("  LEFT JOIN course_detail cd ON cd.course_detail_id=cdi.course_detail_id ");
                sqlCondi.AppendFormat("  LEFT JOIN product_item pi ON pi.item_id=cdi.item_id ");
                sqlCondi.AppendFormat("  LEFT JOIN course_ticket ct ON ct.ticket_detail_id=td.ticket_detail_id ");
                sqlCondi.AppendFormat(" where 1=1 "); 
                 if (query.flag != -1)
                 {
                     sqlCondi.AppendFormat(" and ct.flag='{0}' ", query.flag);
                 }
                 if (!string.IsNullOrEmpty(query.TimeStart))
                 {
                     sqlCondi.AppendFormat(" and cd.start_date>='{0}' ", query.TimeStart);
                 }
                 if (!string.IsNullOrEmpty(query.TimeEnd))
                 {
                     sqlCondi.AppendFormat(" and cd.end_date<='{0}' ", query.TimeEnd);
                 }
                 if (query.MDID != 0)
                 {
                     sqlCondi.AppendFormat(" and ( td.ticket_detail_id='{0}' or td.ticket_master_id='{0}' or cd.course_id='{0}') ", query.MDID);
                 }
                sqlCondi.AppendFormat("  ORDER BY td.ticket_detail_id DESC ");
                totalCount = 0;
                if (query.IsPage)
                {
                    DataTable _dt = _access.getDataTable("select count(td.ticket_detail_id) as totalCount " + sqlCondi.ToString());
                    if (_dt.Rows.Count > 0)
                    {
                        totalCount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
                    }

                    sqlCondi.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
                }

                sql.Append(sqlCondi.ToString());

                return _access.getDataTable(sql.ToString());
            }
            catch (Exception ex)
            {

                throw new Exception("TicketDetailDao.GetTicketDetailList-->" + ex.Message + sql.ToString(), ex);
            }
        }
Beispiel #2
0
 public DataTable GetTicketDetailTable(TicketDetailQuery query, out int totalCount)
 {
     try
     {
         return _ITicketDetailDao.GetTicketDetailTable(query, out totalCount);
     }
     catch (Exception ex)
     {
         throw new Exception("TicketDetailMgr-->GetTicketDetailTable" + ex.Message, ex);
     }
 }
Beispiel #3
0
        //#region 課程訂單列表頁
        //public HttpResponseBase GetTicketMasterList()
        //{
        //    string json = string.Empty;
        //    int tranInt = 0;
        //    try
        //    {
        //        TicketMasterQuery query = new TicketMasterQuery();
        //        query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
        //        query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
        //        int totalCount = 0;
        //        _ITicketMaster = new TicketMasterMgr(mySqlConnectionString);
        //        if (!string.IsNullOrEmpty(Request.Params["order_status"]))
        //        {
        //            query.order_status = int.Parse(Request.Params["order_status"]);
        //        }
        //        else
        //        {
        //            query.order_status = -1;
        //        }
        //        if (!string.IsNullOrEmpty(Request.Params["order_payment"]))
        //        {
        //            query.order_payment = int.Parse(Request.Params["order_payment"]);
        //        }
        //        else
        //        {
        //            query.order_payment = -1;
        //        }
        //        if (!string.IsNullOrEmpty(Request.Params["order_id"]))
        //        {
        //            query.ticket_master_id = int.Parse(Request.Params["order_id"]);
        //        }
        //        query.order_name = Request.Params["order_name"];
        //        if (!string.IsNullOrEmpty(Request.Params["ticket_start"]))
        //        {
        //            query.order_start = (int)CommonFunction.GetPHPTime(Request.Params["ticket_start"]);
        //        }
        //        if (!string.IsNullOrEmpty(Request.Params["ticket_end"]))
        //        {
        //            query.order_end = (int)CommonFunction.GetPHPTime(Request.Params["ticket_end"].Substring(0, 10) + " 23:59:59");
        //        }
        //        if (!string.IsNullOrEmpty(Request.Params["bill_check"]))
        //        {
        //            query.billing_checked = int.Parse(Request.Params["bill_check"]);
        //        }
        //        if (!string.IsNullOrEmpty(Request.Params["course_search"]))
        //        {
        //            if (int.TryParse(Request.Params["course_search"], out tranInt))
        //            {
        //                query.course_id = tranInt;
        //            }
        //            else
        //            {
        //                query.course_name = Request.Params["course_search"];
        //            }
        //        }
        //        if (!string.IsNullOrEmpty(Request.Params["course_start"]))
        //        {
        //            query.start_date = DateTime.Parse(Request.Params["course_start"]);
        //        }
        //        if (!string.IsNullOrEmpty(Request.Params["course_end"]))
        //        {
        //            query.end_date = DateTime.Parse(Request.Params["course_end"].Substring(0, 10) + " 23:59:59");
        //        }
        //        DataTable dtTicketMaster = _ITicketMaster.GetTicketMasterList(query, out totalCount);

        //        IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
        //        //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式     
        //        timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
        //        json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(dtTicketMaster, Formatting.Indented, timeConverter) + "}";//返回json數據
        //    }
        //    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);
        //        json = "{success:false,msg:0}";
        //    }
        //    this.Response.Clear();
        //    this.Response.Write(json);
        //    this.Response.End();
        //    return this.Response;
        //}
        //public HttpResponseBase UpdateTicketMaster()
        //{
        //    string json = string.Empty;
        //    try
        //    {
        //        TicketMaster tm = new TicketMaster();
        //        _ITicketMaster = new TicketMasterMgr(mySqlConnectionString);
        //        if (Request.Params["ticket_master_id"] != "0")
        //        {
        //            tm.ticket_master_id = int.Parse(Request.Params["ticket_master_id"]);
        //        }
        //        tm.delivery_name = Request.Params["delivery_name"];
        //        tm.delivery_mobile = Request.Params["delivery_mobile"];
        //        tm.delivery_phone = Request.Params["delivery_phone"];
        //        tm.delivery_address = Request.Params["delivery_address"];
        //        if (!string.IsNullOrEmpty(Request.Params["delivery_zip"]))
        //        {
        //            tm.delivery_zip = int.Parse(Request.Params["delivery_zip"]);
        //        }
        //        tm.order_name = Request.Params["order_name"];
        //        tm.order_mobile = Request.Params["order_mobile"];
        //        tm.order_phone = Request.Params["order_phone"];
        //        tm.order_address = Request.Params["order_address"];
        //        if (!string.IsNullOrEmpty(Request.Params["order_zip"]))
        //        {
        //            tm.order_zip = int.Parse(Request.Params["order_zip"]);
        //        }
        //        int i = _ITicketMaster.Update(tm);
        //        if (i > 0)
        //        {
        //            json = "{success:true}";//返回json數據
        //        }
        //        else
        //        {
        //            json = "{success:false}";//返回json數據
        //        }

        //    }
        //    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);
        //        json = "{success:false,msg:0}";
        //    }
        //    this.Response.Clear();
        //    this.Response.Write(json);
        //    this.Response.End();
        //    return this.Response;
        //}
        //#endregion

        #region 課程訂購單明細表
        /// <summary>
        /// 訂單詳情頁面列表
        /// </summary>
        /// <returns></returns>

        public HttpResponseBase GetTicketDetailList()
        {
            string jsonStr = string.Empty;
            try
            {
                TicketDetailQuery query = new TicketDetailQuery();
                query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
                if (!string.IsNullOrEmpty(Request.Params["limit"]))
                {
                    query.Limit = Convert.ToInt32(Request.Params["limit"]);
                }
                query.flag = -1;
                if (!string.IsNullOrEmpty(Request.Params["Search"]))
                {
                    query.flag = int.Parse(Request.Params["Search"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["MasterID"]))
                {
                    query.MDID = int.Parse(Request.Params["MasterID"]);
                }

                if (!string.IsNullOrEmpty(Request.Params["TimeStart"]))
                {
                    query.TimeStart = DateTime.Parse(Request.Params["TimeStart"]).ToString("yyyy/MM/dd 00:00:00");
                }
                if (!string.IsNullOrEmpty(Request.Params["TimeEnd"]))
                {
                    query.TimeEnd = DateTime.Parse(Request.Params["TimeEnd"]).ToString("yyyy/MM/dd 23:59:59");
                }

                long a = CommonFunction.GetPHPTime("2014-02-01 00:00:00");
                long s = CommonFunction.GetPHPTime("2014-02-28 23:29:59");
                int totalCount = 0;
                _ITicketDetail = new TicketDetailMgr(mySqlConnectionString);

                DataTable _dt = _ITicketDetail.GetTicketDetailTable(query, out totalCount);

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式     
                timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
                jsonStr = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(_dt, Formatting.Indented, timeConverter) + "}";//返回json數據
            }
            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);
                jsonStr = "{success:false,msg:0}";
            }
            this.Response.Clear();
            this.Response.Write(jsonStr.ToString());
            this.Response.End();
            return this.Response;
        }
Beispiel #4
0
        /// <summary>
        /// 供應商後臺檢視使用序號功能 列表
        /// </summary>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable GetTicketDetailAllCodeTable(TicketDetailQuery query, out int totalCount)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder sqlCondi = new StringBuilder();
            try
            {
                sql.AppendFormat(@"select tm.ticket_master_id,c.course_name,cd.start_date,cd.end_date,ct.ticket_code,ct.flag,");
                sql.AppendFormat("(SELECT spec_name from product_spec ps where pi.product_id=ps.product_id and pi.spec_id_1=ps.spec_id )as spec_id_1, ");
                sql.AppendFormat("(SELECT spec_name from product_spec ps where pi.product_id=ps.product_id and pi.spec_id_2=ps.spec_id )as spec_id_2 ");

                sqlCondi.AppendFormat("  from ticket_detail td ");
                sqlCondi.AppendFormat("  LEFT JOIN ticket_master tm ON td.ticket_master_id=tm.ticket_master_id ");
                sqlCondi.AppendFormat("  LEFT JOIN course_ticket ct ON ct.ticket_detail_id=td.ticket_detail_id ");
                sqlCondi.AppendFormat("  LEFT JOIN course_detail_item cdi ON cdi.course_detail_item_id=td.cd_item_id ");
                sqlCondi.AppendFormat("  LEFT JOIN course_detail cd ON cd.course_detail_id=cdi.course_detail_id ");
                sqlCondi.AppendFormat("  LEFT JOIN product_item pi ON pi.item_id=cdi.item_id ");
                sqlCondi.AppendFormat("LEFT JOIN course c ON c.course_id=cd.course_id ");
                sqlCondi.Append(" where 1=1 ");
                if (query.flag != -1)
                {
                    sqlCondi.AppendFormat(" and ct.flag='{0}' ", query.flag);
                }
                //if (!string.IsNullOrEmpty(query.TimeStart))
                //{
                //    sqlCondi.AppendFormat(" and cd.start_date>='{0}' ", query.TimeStart);
                //}
                //if (!string.IsNullOrEmpty(query.TimeEnd))
                //{
                //    sqlCondi.AppendFormat(" and cd.end_date<='{0}' ", query.TimeEnd);
                //}
                if (!string.IsNullOrEmpty(query.ticket_code))
                {
                    sqlCondi.AppendFormat(" and ( ct.ticket_code='{0}' or td.ticket_master_id='{0}' ) ", query.ticket_code);
                }
                 sqlCondi.Append("  ORDER BY td.ticket_detail_id DESC ");
                totalCount = 0;
                if (query.IsPage)
                {
                    DataTable _dt = _access.getDataTable("select count(td.ticket_detail_id) as totalCount " + sqlCondi.ToString());
                    if (_dt.Rows.Count > 0)
                    {
                        totalCount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
                    }

                    sqlCondi.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
                }

                sql.Append(sqlCondi.ToString());

                return _access.getDataTable(sql.ToString());
             }
            catch (Exception ex)
             {

                throw new Exception("TicketDetailDao.GetTicketDetailList-->" + ex.Message + sql.ToString(), ex);
             }
         }