public Response <TransOrderResponse> QueryTransOrderRanges(TransOrderRequest request)
{
Response <TransOrderResponse> response = new Response <TransOrderResponse>()
{
Result = new TransOrderResponse()
};
try
{
PodReportAccessor accessor = new PodReportAccessor();
int RowCount;
response.Result.transOrder = accessor.QueryTransOrderRanges(request, out RowCount);
response.Result.PageCount = RowCount % request.PageSize == 0 ? RowCount / request.PageSize : RowCount / request.PageSize + 1;
response.Result.PageIndex = request.PageIndex;
response.IsSuccess = true;
//response.Result.PageIndex = 1;
//response.Result.transOrder = accessor.QueryTransOrderRange(request, out RowCount);
//response.Result.PageIndex
//response.IsSuccess = true;
}
catch (Exception ex)
{
LogError(ex);
response.Exception = ex;
response.IsSuccess = false;
response.ErrorCode = ErrorCode.Technical;
}
return(response);
}
public IEnumerable <TransOrder> QueryTransOrderRange(TransOrderRequest Request)//, out int RowCount
{
string SqlWhere = GenQueryAttachmentSql(Request);
int tempRowCount = 0;
DbParam[] dbParams = new DbParam[] {
new DbParam("@SqlWhere", DbType.String, SqlWhere, ParameterDirection.Input),
// new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output)
//new DbParam("@EndTime", DbType.DateTime, EndTime, ParameterDirection.Input),
//new DbParam("@ShipperID", DbType.Int64, ShipperID, ParameterDirection.Input)
};
//RowCount = (int)dbParams[3].Value;
return(base.ExecuteDataTable("Proc_OrderToNull", dbParams).ConvertToEntityCollection <TransOrder>());
}
public IEnumerable <TransOrder> QueryTransOrderRanges(TransOrderRequest Request, out int RowCount)//,
{
string SqlWhere = GenQueryAttachmentSql(Request);
int tempRowCount = 0;
DbParam[] dbParams = new DbParam[] {
new DbParam("@SqlWhere", DbType.String, SqlWhere, ParameterDirection.Input),
new DbParam("@PageIndex", DbType.Int32, Request.PageIndex, ParameterDirection.Input),
new DbParam("@PageSize", DbType.Int32, Request.PageSize, ParameterDirection.Input),
new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output)
};
DataTable dt = this.ExecuteDataTable("Proc_OrderToNullFen", dbParams);
RowCount = (int)dbParams[3].Value;
return(dt.ConvertToEntityCollection <TransOrder>());
//base.ExecuteDataTable("Proc_OrderToNullFen", dbParams).ConvertToEntityCollection<TransOrder>();
}
public ActionResult TransOrderStatus(TransOrderModel vm)
{
//vm.Customers = ApplicationConfigHelper.GetProjectUserCustomers(base.UserInfo.ProjectID, base.UserInfo.ID)
// .Select(c => new SelectListItem() { Value = c.CustomerID.ToString(), Text = c.CustomerName });
//TransOrderModel vm = new TransOrderModel();
//vm.SearchCondition = new PodSearchCondition();
//vm.PageIndex = 0;
//vm.PageCount = 0;
TransOrderRequest tor = new TransOrderRequest();
tor.StartTime = DateTime.Now.AddDays(-10).ToString("yyyy-MM-dd");
tor.EndTime = DateTime.Now.AddDays(+1).ToString("yyyy-MM-dd");
vm.transOrderRequest = tor;
//vm.transOrderRequest.StartTime = DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd");
//vm.transOrderRequest.EndTime = DateTime.Now.AddDays(0).ToString("yyyy-MM-dd");
//this.GenQueryPodViewModel(vm);
return(View(vm));
}
/// <summary>
/// 查询装箱单号拼接sql
/// </summary>
/// <param name="condition"></param>
/// <param name="Customers"></param>
/// <returns></returns>
private string GenQueryAttachmentSql(TransOrderRequest Request)
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(Request.ID))
{
IEnumerable <string> numbers = Enumerable.Empty <string>();
if (Request.ID.IndexOf("\n") > 0)
{
numbers = Request.ID.Split('\n').Select(s => { return(s.Trim()); });
}
if (Request.ID.IndexOf(',') > 0)
{
numbers = Request.ID.Split(',').Select(s => { return(s.Trim()); });
}
if (numbers != null && numbers.Any())
{
numbers = numbers.Where(c => !string.IsNullOrEmpty(c));
}
if (numbers != null && numbers.Any())
{
sb.Append(" and a.CustomerOrderNumber in ( ");
foreach (string s in numbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and a.CustomerOrderNumber like '%" + Request.ID.Trim() + "%' ");
}
}
if (Request.Customers != "0" && !string.IsNullOrEmpty(Request.Customers))//
{
string CustomerName = "";
switch (Request.Customers)
{
case "1":
CustomerName = "Adidas";
break;
case "2":
CustomerName = "Nike";
break;
case "3":
CustomerName = "Hilti";
break;
case "4":
CustomerName = "AKZO";
break;
case "5":
CustomerName = "Dowcorning";
break;
default:
CustomerName = Request.Customers + "%' or a.CustomerOrderNumber like'%" + Request.Customers + "%' or EndCityName like '%" + Request.Customers + "%' or ShipperName like '%" + Request.Customers;
break;
}
sb.Append("and (CustomerName like '%" + CustomerName + "%')");
}
if (!string.IsNullOrEmpty(Request.ShipperName))
{
sb.Append(" and ShipperName='" + Request.ShipperName + "'");
}
if (!string.IsNullOrEmpty(Request.startCityTreeName))
{
sb.Append("and a.StartCityName='" + Request.startCityTreeName + "'");
}
if (!string.IsNullOrEmpty(Request.endCityTreeName))
{
sb.Append("and a.EndCityName='" + Request.endCityTreeName + "'");
}
if (!string.IsNullOrEmpty(Request.StartTime))
{
sb.Append("and ActualDeliveryDate>='" + Request.StartTime + "'");
}
if (!string.IsNullOrEmpty(Request.StartTime))
{
sb.Append("and ActualDeliveryDate<'" + Request.EndTime + " 23:59:59'");
}
if (!string.IsNullOrEmpty(Request.Time))
{
sb.Append("and CONVERT(varchar(10), ActualDeliveryDate, 120) ='" + Request.Time + "'");
}
return(sb.ToString());
}
