Example #1
0
        public MyResult GetList([FromQuery] OrderInfoParam param)
        {
            int total;
            List <OrderInfo> roleList = OrderBLL.GetOrderList(param, out total);

            var result = new
            {
                total,
                list = roleList
            };

            return(MyResult.OK(result));
        }
        /// <summary>
        /// 查询数据列表
        /// </summary>
        /// <param name="param"></param>
        /// <param name="pageInfo"></param>
        /// <returns></returns>
        public IActionResult SearchListByPage(OrderInfoParam param, PageInfo pageInfo)
        {
            param.UserId = CurrentUser.Id;
            if (param.BeginDate != null)
            {
                param.BeginDate = DateTime.Parse(string.Format("{0:yyyy-MM-dd}", param.BeginDate) + " 00:00:00");
            }
            if (param.EndDate != null)
            {
                param.EndDate = DateTime.Parse(string.Format("{0:yyyy-MM-dd}", param.EndDate) + " 23:59:59");
            }

            var result = _orderInfoRepository.SearchListByPage(param, pageInfo);

            return(Json(result));
        }
Example #3
0
        public PageModel <OrderInfoView> GetListByPages(PageInfo pageInfo, OrderInfoParam param)
        {
            string sqlWhere = " where o.isDel = 0 ";
            var    sqlParam = new List <DbParameter>();

            if (param.Status != 0)
            {
                sqlWhere += " and o.Status = @Status ";
                sqlParam.Add(new MySqlParameter("@Status", param.Status));
            }
            if (!string.IsNullOrEmpty(param.Phone))
            {
                sqlWhere += " and o.Phone like @Phone ";
                sqlParam.Add(new MySqlParameter("@Phone", "%" + param.Phone + "%"));
            }
            if (!string.IsNullOrEmpty(param.Consignee))
            {
                sqlWhere += " and o.Consignee like @Consignee ";
                sqlParam.Add(new MySqlParameter("@Consignee", "%" + param.Consignee + "%"));
            }
            if (param.BeginTime != null)
            {
                sqlWhere += " and o.CreateTime > @BeginTime ";
                sqlParam.Add(new MySqlParameter("@BeginTime", param.BeginTime));
            }
            if (param.EndTime != null)
            {
                sqlWhere += " and o.CreateTime < @EndTime ";
                sqlParam.Add(new MySqlParameter("@EndTime", param.EndTime));
            }

            string sql      = @"select o.id as OrderId, o.OrderNo, CONCAT(o.ProvinceName,o.CityName,o.CountyName, ' ',o.DetailedAddress) Address,
                            o.Consignee,o.Phone,o.BuyQuantity,o.Message,o.OrderAmount,o.Status,o.CreateTime,g.SpecificationDesc,
                            sg.TrackingNo,sg.CourierCompany,sg.Remarks
                            from orderinfo o
                            LEFT JOIN goodsinfo g on o.GoodsId = g.Id
                            LEFT JOIN SendGoodsInfo sg on o.id = sg.OrderId " + sqlWhere + " order by o.createTime ";
            string sqlCount = "select count(*) from OrderInfo o " + sqlWhere;

            var list  = _context.Database.SqlQuery <OrderInfoView>(sql.ToPaginationSql(pageInfo), sqlParam.ToArray());
            var count = _context.Database.ExcuteSclare(sqlCount, sqlParam.ToArray());

            return(new PageModel <OrderInfoView>(list, pageInfo.PageIndex, pageInfo.PageSize, Convert.ToInt32(count)));
        }
Example #4
0
        /// <summary>
        /// 后台订单列表查询
        /// </summary>
        /// <param name="param"></param>
        /// <param name="pageInfo"></param>
        /// <returns></returns>
        public PageModel <OrderInfoView2> SearchListByPage(OrderInfoParam param, PageInfo pageInfo)
        {
            var    sqlParam = new List <DbParameter>();
            string sqlWhere = " where oi.isDel=0 and oi.userId=@UserId ";

            sqlParam.Add(new MySqlParameter("@UserId", param.UserId));

            if (param.BeginDate != null)
            {
                sqlWhere += " and oi.CreateTime > @BeginDate ";
                sqlParam.Add(new MySqlParameter("@BeginDate", param.BeginDate));
            }

            if (param.EndDate != null)
            {
                sqlWhere += " and oi.CreateTime < @EndDate ";
                sqlParam.Add(new MySqlParameter("@EndDate", param.EndDate));
            }

            if (!string.IsNullOrEmpty(param.ClientName))
            {
                sqlWhere += " and (ci.ClientName like @ClientName or ci.ClientPhone like @ClientName) ";
                sqlParam.Add(new MySqlParameter("@ClientName", "%" + param.ClientName + "%"));
            }

            string sql = $@"SELECT oi.*,ci.ClientName,ci.ClientPhone 
                            from orderinfo oi
                            LEFT JOIN clientinfo ci on oi.clientId = ci.id {sqlWhere} order by oi.id desc ";

            string sqlCount = $@"SELECT count(*)  
                            from orderinfo oi
                            LEFT JOIN clientinfo ci on oi.clientId = ci.id {sqlWhere} ";

            var list  = _context.Database.SqlQuery <OrderInfoView2>(sql.ToPaginationSql(pageInfo), sqlParam.ToArray());
            var count = _context.Database.ExcuteSclare(sqlCount, sqlParam.ToArray());

            return(new PageModel <OrderInfoView2>(list, pageInfo.PageIndex, pageInfo.PageSize, Convert.ToInt32(count)));
        }
 /// <summary>
 /// 根据查询条件获取分页数据
 /// </summary>
 /// <param name="pageInfo"></param>
 /// <param name="param"></param>
 /// <returns></returns>
 public IActionResult GetListByPages(PageInfo pageInfo, OrderInfoParam param)
 {
     var result = _orderInfoRepository.GetListByPages(pageInfo,param);
     return JsonOk(result);
 }
Example #6
0
        public MyResult GetDetail([FromQuery] OrderInfoParam param)
        {
            OrderInfo orderInfo = OrderBLL.GetOrderDetail(param);

            return(MyResult.OK(orderInfo));
        }
Example #7
0
        internal static List <OrderInfo> GetOrderList(OrderInfoParam searchParam, out int total)
        {
            int offset = (searchParam.PageIndex - 1) * searchParam.PageSize;
            int rows   = searchParam.PageSize;

            List <OrderInfo> recordList = new List <OrderInfo>();
            string           sql        = @" SELECT o.ID, ORDER_NUM, BATCH_NUMBER,JBBW_PHONE, JBBW_NAME,FLIGHT_NUM, LANDING_TIME, `STATUS`, o.CreateTime, e.ChineseName
                            FROM ex_orderinfo o
                            LEFT JOIN mt_employee e ON o.CreatedBy = e.UserName 
                            {0}
                            ORDER by o.CreateTime DESC
                            LIMIT {1},{2} ";

            string where = " WHERE 1=1 ";
            List <MySqlParameter> param = new List <MySqlParameter>();

            if (!string.IsNullOrEmpty(searchParam.OrderNumber))
            {
                where += " AND ORDER_NUM = @OrderNumber";
                param.Add(new MySqlParameter("@OrderNumber", searchParam.OrderNumber));
            }
            if (!string.IsNullOrEmpty(searchParam.Status))
            {
                where += " AND `STATUS` = @Status";
                param.Add(new MySqlParameter("@Status", searchParam.Status));
            }
            if (!string.IsNullOrEmpty(searchParam.CreateTimeStartStr))
            {
                where += " AND o.CreateTime > @CreateTimeStart";
                param.Add(new MySqlParameter("@CreateTimeStart", Convert.ToDateTime(searchParam.CreateTimeStartStr)));
            }
            if (!string.IsNullOrEmpty(searchParam.CreateTimeEndStr))
            {
                where += " AND o.CreateTime < @CreateTimeEnd";
                param.Add(new MySqlParameter("@CreateTimeEnd", Convert.ToDateTime(searchParam.CreateTimeEndStr)));
            }
            if (!string.IsNullOrEmpty(searchParam.BatchNo))
            {
                where += " AND BATCH_NUMBER = @BatchNo";
                param.Add(new MySqlParameter("@BatchNo", searchParam.BatchNo));
            }
            if (!string.IsNullOrEmpty(searchParam.FlightNumber))
            {
                where += " AND FLIGHT_NUM = @FlightNumber";
                param.Add(new MySqlParameter("@FlightNumber", searchParam.FlightNumber));
            }
            if (!string.IsNullOrEmpty(searchParam.KeyWord))
            {
                where += " AND JBBW_PHONE like CONCAT('%',@KeyWord,'%') OR JBBW_NAME like CONCAT('%',@KeyWord,'%')";
                param.Add(new MySqlParameter("@KeyWord", searchParam.KeyWord));
            }

            DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection, string.Format(sql, where, offset, rows), param.ToArray());

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    recordList.Add(new OrderInfo()
                    {
                        ID           = Converter.TryToInt64(row["ID"]),
                        OrderNumber  = Converter.TryToString(row["ORDER_NUM"]),
                        BatchNo      = Converter.TryToString(row["BATCH_NUMBER"]),
                        JBBWPhone    = Converter.TryToString(row["JBBW_PHONE"]),
                        JBBWName     = Converter.TryToString(row["JBBW_NAME"]),
                        FlightNumber = Converter.TryToString(row["FLIGHT_NUM"]),
                        LandingTime  = string.IsNullOrEmpty(Converter.TryToString(row["LANDING_TIME"])) ? "" : Converter.TryToDateTime(row["LANDING_TIME"]).ToString("yyyy-MM-dd HH:mm:ss"),
                        Status       = Converter.TryToString(row["STATUS"]),
                        StatusStr    = OrderStatus.GetStatus(Converter.TryToString(row["STATUS"])),
                        CreateTime   = Converter.TryToDateTime(row["CreateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
                        CreatedBy    = Converter.TryToString(row["ChineseName"])
                    });
                }
            }

            // 查询总数
            string sqlCount = @"SELECT count(*) FROM ex_orderinfo o {0}";
            object re       = JabMySqlHelper.ExecuteScalar(Config.DBConnection, string.Format(sqlCount, where), param.ToArray());

            total = Convert.ToInt32(re);

            return(recordList);
        }
Example #8
0
        internal static OrderInfo GetOrderDetail(OrderInfoParam searchParam)
        {
            OrderInfo             orderInfo = new OrderInfo();
            string                sql       = @"SELECT `ID`,
                            `ORDER_NUM`,
                            `USER_ID`,
                            `SENDER_PHONE`,
                            `SENDER_NAME`,
                            `SENDER_ADDRESS`,
                            `JBBW_PHONE`,
                            `JBBW_NAME`,
                            `JBBW_ADDRESS`,
                            `REMARKS`,
                            `WEIGHT`,
                            `FLIGHT_NUM`,
                            `LANDING_TIME`,
                            `STATUS`,
                            `BATCH_NUMBER`,
                            `CreateTime`,
                            `CreatedBy`,
                            `UpdateTime`,
                            `UpdateBy`
                        FROM `ex_orderinfo`
                        {0};";
            List <MySqlParameter> param     = new List <MySqlParameter>();

            string where;
            if (searchParam.ID != null)
            {
                where = " WHERE `ID` = @ID";
                param.Add(new MySqlParameter("@ID", searchParam.ID));
            }
            else if (!string.IsNullOrEmpty(searchParam.OrderNumber))
            {
                where = " WHERE ORDER_NUM = @OrderNumber";
                param.Add(new MySqlParameter("@OrderNumber", searchParam.OrderNumber));
            }
            else
            {
                throw new MsgException("参数错误!");
            }
            DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection, string.Format(sql, where), param.ToArray());

            if (dt != null && dt.Rows.Count > 0)
            {
                DataRow   row       = dt.Rows[0];
                OrderInfo orderinfo = new OrderInfo()
                {
                    ID            = Converter.TryToInt64(row["ID"]),
                    OrderNumber   = Converter.TryToString(row["ORDER_NUM"]),
                    SenderPhone   = Converter.TryToString(row["SENDER_PHONE"]),
                    SenderName    = Converter.TryToString(row["SENDER_NAME"]),
                    SenderAddress = Converter.TryToString(row["SENDER_ADDRESS"]),
                    JBBWPhone     = Converter.TryToString(row["JBBW_PHONE"]),
                    JBBWName      = Converter.TryToString(row["JBBW_NAME"]),
                    JBBWAddress   = Converter.TryToString(row["JBBW_ADDRESS"]),
                    Remarks       = Converter.TryToString(row["REMARKS"]),
                    Weight        = Converter.TryToString(row["WEIGHT"]),
                    FlightNumber  = Converter.TryToString(row["FLIGHT_NUM"]),
                    LandingTime   = string.IsNullOrEmpty(Converter.TryToString(row["LANDING_TIME"])) ? "" : Converter.TryToDateTime(row["LANDING_TIME"]).ToString("yyyy-MM-dd HH:mm:ss"),
                    Status        = Converter.TryToString(row["STATUS"]),
                    StatusStr     = OrderStatus.GetStatus(Converter.TryToString(row["STATUS"])),
                    BatchNo       = Converter.TryToString(row["BATCH_NUMBER"]),
                    CreateTime    = Converter.TryToDateTime(row["CreateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
                    CreatedBy     = Converter.TryToString(row["CreatedBy"]),
                    UpdateTime    = Converter.TryToDateTime(row["UpdateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
                    UpdatedBy     = Converter.TryToString(row["UpdateBy"]),
                };
                orderinfo.BatchNo = string.IsNullOrEmpty(orderinfo.BatchNo) ? null : orderinfo.BatchNo;
                return(orderinfo);
            }
            else
            {
                return(null);
            }
        }