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
}
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;
}