예제 #1
0
        public static List<OrderModel> OrderQuery(string city, string startDate, string endDate, string empAccount, string orderStatus, string serviceType, int pageIndex, int pageSize, out int recordCount)
        {
            #region
            recordCount = 0;
            pageIndex = pageIndex - 1;
            recordCount = 0;
            var startIndex = pageIndex * pageSize + 1;
            var endIndex = startIndex + pageSize - 1;

            var result = new List<OrderModel>();
            var sqlString = "select rownum as rn, o.order_number,o.order_time,o.order_status,o.release_status,o.money_status,c.category_value,o.pay_total,r.shifu_phone,o.c_phone from order_info o , order_rob_shifu r , dic_category c where  c.category_code=o.category_code and r.order_number=o.order_number and r.rob_status in ('80','90') ";
            var sqlPageString = "select * from ({table})m where rn >=" + startIndex + " and rn <=" + endIndex;
            var sqlCountString = "select count(*) rCount from order_info o , order_rob_shifu r , dic_category c where  c.category_code=o.category_code and r.order_number=o.order_number and r.rob_status in ('80','90') ";
            //
            //and r.shifu_phone='18611102971'
            //and o.category_code=''
            //and o.city_code=''
            //and o.order_time between and

            if (city != "-1")
            {
                sqlString += " and o.city_code='" + city + "' ";
                sqlCountString += " and o.city_code='" + city + "' ";
            }

            if (string.IsNullOrEmpty(startDate) == false)
            {
                sqlString += " and o.order_time>= to_date('" + startDate + "','yyyy-mm-dd')";
                sqlCountString += " and o.order_time>= to_date('" + startDate + "','yyyy-mm-dd')";
            }
            if (string.IsNullOrEmpty(endDate) == false)
            {
                sqlString += " and o.order_time<= to_date('" + endDate + "','yyyy-mm-dd')";
                sqlCountString += " and o.order_time<= to_date('" + endDate + "','yyyy-mm-dd')";
            }
            if (string.IsNullOrEmpty(empAccount) == false)
            {
                sqlString += " and r.shifu_phone='" + empAccount + "' ";
                sqlCountString += " and r.shifu_phone='" + empAccount + "' ";
            }
            if (orderStatus != "-1")
            {//order_status release_status money_status
                /*
                   order_status
                       15:等待支付
                       20,25:服务中
                       90:服务已完成
                   release_status
                       5:已取消
                   money_status
                       -10,-15:待退款
                */

                if (orderStatus == "1")
                {
                    sqlString += " and o.order_status in(20,25)";
                    sqlCountString += " and o.order_status in(20,25)";
                }
                else if (orderStatus == "2")
                {
                    sqlString += " and o.order_status='15'";
                    sqlCountString += " and o.order_status='15'";
                }
                else if (orderStatus == "3")
                {
                    sqlString += " and o.order_status='90'";
                    sqlCountString += " and o.order_status='90'";
                }
                else if (orderStatus == "4")
                {
                    sqlString += " and o.release_status='5'";
                    sqlCountString += " and o.release_status='5'";
                }
                else
                {//5
                    sqlString += " and o.money_status in('-10','-15')";
                    sqlCountString += " and o.money_status in('-10','-15')";
                }
            }
            if (serviceType != "-1")
            {
                sqlString += " and o.category_code='" + serviceType + "' ";
                sqlCountString += " and o.category_code='" + serviceType + "' ";
            }

            sqlPageString = sqlPageString.Replace("{table}", sqlString);
            //return result;

            recordCount = Convert.ToInt32(OracleHelper.ExecuteScalar(OracleHelper.OracleConnString, System.Data.CommandType.Text, sqlCountString));
            var reader = OracleHelper.ExecuteReader(OracleHelper.OracleConnString, System.Data.CommandType.Text, sqlPageString);
            while (reader.Read())
            {
                var emp = new OrderModel
                {
                    OrderId = reader["order_number"].ToString(),
                    TradeDate = reader["order_time"].ToString(),
                    Status = reader["order_status"].ToString(),
                    ReleaseStatus = reader["release_status"].ToString(),
                    MoneyStatus = reader["money_status"].ToString(),
                    ServiceType = reader["category_value"].ToString(),
                    Total = decimal.Parse(reader["pay_total"].ToString()),
                    CompanyEmpAccount = reader["shifu_phone"].ToString(),
                    Customer = reader["c_phone"].ToString()
                };
                result.Add(emp);
            }

            return result;
            #endregion
        }
예제 #2
0
        public static List<OrderModel> Query(string companyCode, string startDate, string endDate, string empAccount, string serviceType, string status, int pageIndex, int pageSize, out int recordCount)
        {
            #region
            var result = new List<OrderModel>();

            pageIndex = pageIndex - 1;
            recordCount = 0;
            var startIndex = pageIndex * pageSize + 1;
            var endIndex = startIndex + pageSize - 1;

            //OracleParameterCollection paramList = new OracleParameterCollection();
            //OracleParameterCollection countParamList = new OracleParameterCollection();
            List<OracleParameter> paramList = new List<OracleParameter>();

            var countSqlString = "  select count(*) as rowCount from v_company_order v where 1=1 ";
            var sqlString = "select * from ( select rownum as rn,v.order_number,v.order_time,v.pay_total,v.c_phone,v.shifu_phone,v.category_value,v.order_status,v.release_status,v.money_status,v.category_code from v_company_order v where 1=1 ";//

            sqlString += " and shifu_phone in (select shifu_phone from shifu_reg where company_code=:companyCode" + (string.IsNullOrEmpty(empAccount) ? "" : " and shifu_phone =:empAccount") + ")";
            countSqlString += " and shifu_phone in (select shifu_phone from shifu_reg where company_code=:companyCode" + (string.IsNullOrEmpty(empAccount) ? "" : " and shifu_phone =:empAccount") + ")";
            //paramList.Add(new OracleParameter { ParameterName = "companyCode", Value = companyCode });
            //countParamList.Add(new OracleParameter { ParameterName = "companyCode", Value = companyCode });
            paramList.Add(OracleHelper.MakeParam("companyCode", companyCode));

            if (!string.IsNullOrEmpty(empAccount))
            {
                //paramList.Add(new OracleParameter { ParameterName = "", Value =  });
                paramList.Add(new OracleParameter { ParameterName = "empAccount", Value = empAccount });
            }

            if (serviceType != "-1")
            {
                #region
                sqlString += " and v.category_code=:serviceType";
                countSqlString += " and v.category_code=:serviceType";
                paramList.Add(new OracleParameter { ParameterName = "serviceType", Value = serviceType });
                #endregion
            }

            if (status != "-1")
            {
                #region
                /*
                    order_status
                        15:等待支付
                        20,25:服务中
                        90:服务已完成
                    release_status
                        5:已取消
                    money_status
                        -10,-15:待退款
                 */
                if (status == "1")
                {
                    sqlString += " and v.order_status in(20,25)";
                    countSqlString += " and v.order_status in(20,25)";
                }
                else if (status == "2") {
                    sqlString += " and v.order_status='15'";
                    countSqlString += " and v.order_status='15'";
                }
                else if (status == "3")
                {
                    sqlString += " and v.order_status='90'";
                    countSqlString += " and v.order_status='90'";
                }
                else if (status == "4")
                {
                    sqlString += " and v.release_status='5'";
                    countSqlString += " and v.release_status='5'";
                }
                else
                {//5
                    sqlString += " and v.money_status in('-10','-15')";
                    countSqlString += " and v.money_status in('-10','-15')";
                }
                //paramList.Add(new OracleParameter { ParameterName = "status", Value = status });
                #endregion
            }

            if (string.IsNullOrEmpty(startDate) == false)
            {
                sqlString += " and v.order_time>=:startDate";
                countSqlString += " and v.order_time>=:startDate";
                paramList.Add(new OracleParameter { ParameterName = "startDate", Value = startDate, OracleType = OracleType.DateTime });
            }

            if (string.IsNullOrEmpty(endDate) == false)
            {
                sqlString += " and v.order_time<=:endDate";
                countSqlString += " and v.order_time<=:endDate";
                paramList.Add(new OracleParameter { ParameterName = "endDate", Value = endDate, OracleType = OracleType.DateTime });
            }
            sqlString += ") m where rn >=" + startIndex + " and rn <=" + endIndex;
            //return result;

            var paramsArr = new OracleParameter[paramList.Count];
            paramList.CopyTo(paramsArr, 0);

            recordCount = Convert.ToInt32(OracleHelper.ExecuteScalar(OracleHelper.OracleConnString, System.Data.CommandType.Text, countSqlString, paramsArr));
            var reader = OracleHelper.ExecuteReader(OracleHelper.OracleConnString, System.Data.CommandType.Text, sqlString, paramsArr);
            while (reader.Read())
            {
                var emp = new OrderModel
                {
                    OrderId = reader["order_number"].ToString(),
                    TradeDate = reader["order_time"].ToString(),
                    Status = reader["order_status"].ToString(),
                    ReleaseStatus = reader["release_status"].ToString(),
                    MoneyStatus = reader["money_status"].ToString(),
                    ServiceType = reader["category_value"].ToString(),
                    Total = decimal.Parse(reader["pay_total"].ToString()),
                    CompanyEmpAccount = reader["shifu_phone"].ToString(),
                    Customer = reader["c_phone"].ToString()
                };
                result.Add(emp);
            }
            #endregion
            return result;
        }