/// <summary>
/// 百姓网订单报表查询
/// </summary>
/// <param name="condition"></param>
/// <param name="projectID"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowCount"></param>
/// <returns></returns>
public IEnumerable <PodWithAttachment> QueryPod(PodSearchCondition condition, long projectID, int pageIndex, int pageSize, out int rowCount)
{
if (condition.UserType == 2 && !condition.CustomerIDs.Any())
{
rowCount = 0;
return(Enumerable.Empty <PodWithAttachment>());
}
string endCities = string.Empty;
string endCitiesRuleArea = string.Empty;
//if (condition.EndCities != null && condition.EndCities.IndexOf(',') > 0)
//{
// endCities = endCitiesId(condition.EndCities);
//}
//if (!string.IsNullOrEmpty(condition.RuleArea) && condition.RuleArea.IndexOf(',') > 0)
//{
// endCitiesRuleArea = endCitiesId(condition.RuleArea);
//}
string sqlWhere = this.GenQueryPodWhere(condition, projectID, endCities, endCitiesRuleArea);
int tempRowCount = 0;
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sqlWhere, ParameterDirection.Input),
new DbParam("@PageIndex", DbType.Int32, pageIndex, ParameterDirection.Input),
new DbParam("@PageSize", DbType.Int32, pageSize, ParameterDirection.Input),
new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output)
};
DataTable dt = this.ExecuteDataTable("Proc_QueryPods", dbParams);
rowCount = (int)dbParams[3].Value;
return(dt.ConvertToEntityCollection <PodWithAttachment>());
}
public ActionResult PrintDemo(string SystemNumber)
{
QueryPodViewModel vm = new QueryPodViewModel();
PodSearchCondition SearchCondition = new PodSearchCondition();
SearchCondition.SystemNumber = SystemNumber;
vm.SearchCondition = SearchCondition;
var results = new PodService().QueryBSPod(new QueryPodRequest()
{
PageSize = UtilConstants.PAGESIZE, PageIndex = 0, SearchCondition = vm.SearchCondition, ProjectID = base.UserInfo.ProjectID
}).Result;
vm.PodCollection = results.PodCollections;
vm.PageIndex = results.PageIndex;
vm.PageCount = results.PageCount;
//IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//JavaScriptSerializer jsonSerializer = new JavaScriptSerializer();
////这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
//timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";
//string jsonStr = JsonConvert.SerializeObject(results.PodCollections, Formatting.Indented, timeConverter);
return(View(vm));
//return jsonStr;
}
public ActionResult QueryPod(QueryTianjinPodViewModel vm)
{
PodSearchCondition condition = new PodSearchCondition();
condition.CustomerOrderNumber = vm.CustomerOrderNumbers;
condition.ActualDeliveryDate = vm.ActualDeliverlyDate;
condition.EndActualDeliveryDate = vm.EndActualDeliverlyDate;
condition.CustomerID = vm.CustomerID;
condition.ShipperID = vm.ShipperID;
condition.DateTime14 = vm.ExpertArrivalDate;
condition.EndDateTime14 = vm.EndExpertArrivalDate;
if (vm.InOrOut == "0")
{
condition.EndCityID = 3;
}
else
{
condition.StartCityID = 3;
}
var response = new PodService().QueryPodWithNoPaging(new QueryPodRequest() { SearchCondition = condition, ProjectID = base.UserInfo.ProjectID });
if (response.IsSuccess)
{
vm.PodCollection = response.Result.OrderByDescending(p => p.ActualDeliveryDate);
}
vm.Customers = ApplicationConfigHelper.GetProjectUserCustomers(base.UserInfo.ProjectID, base.UserInfo.ID).Select(c => new SelectListItem() { Value = c.CustomerID.ToString(), Text = c.CustomerName });
return View(vm);
}
/// <summary>
/// 百姓网订单报表导出
/// </summary>
/// <param name="condition"></param>
/// <param name="projectID"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowCount"></param>
/// <returns></returns>
public DataTable ExportAll(PodSearchCondition condition, long projectID, int pageIndex, int pageSize)
{
string endCities = string.Empty;
string endCitiesRuleArea = string.Empty;
string sqlWhere = this.ExportAllWhere(condition, projectID, endCities, endCitiesRuleArea);
DbParam[] dbParams = new DbParam[] {
new DbParam("@where", DbType.String, sqlWhere, ParameterDirection.Input)
};
return(this.ExecuteDataTable("Proc_GetBaiXingreportNew", dbParams));
//rowCount = (int)dbParams[3].Value;
//return dt.ConvertToEntityCollection<PodWithAttachment>();
}
private string ExportAllWhere(PodSearchCondition condition, long projectID, string endCities, string endCitiesRuleArea)
{
StringBuilder sb = new StringBuilder();
//百姓网订单排除快递单号
//if (condition.CustomerID == 40)
//{
// sb.Append(" and [Type]='2' ");
//}
#region
if (condition.StartCreateTime.HasValue)
{
sb.Append(" and CreateTime >= '" + condition.StartCreateTime.Value.DateTimeToString() + "' ");
}
if (condition.EndCreateTime.HasValue)
{
sb.Append(" and CreateTime <= '" + condition.EndCreateTime.Value.DateTimeToString() + " 23:59' ");
}
if (!string.IsNullOrEmpty(condition.SystemNumber))
{
IEnumerable <string> systemNumbers = Enumerable.Empty <string>();
if (condition.SystemNumber.IndexOf("\n") > 0)
{
systemNumbers = condition.SystemNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (condition.SystemNumber.IndexOf(',') > 0)
{
systemNumbers = condition.SystemNumber.Split(',').Select(s => { return(s.Trim()); });
}
if (systemNumbers != null && systemNumbers.Any())
{
systemNumbers = systemNumbers.Where(c => !string.IsNullOrEmpty(c));
}
if (systemNumbers != null && systemNumbers.Any())
{
sb.Append(" and SystemNumber in ( ");
foreach (string s in systemNumbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and SystemNumber like '%" + condition.SystemNumber.Trim() + "%' ");
}
}
if (!string.IsNullOrEmpty(condition.CustomerOrderNumber))
{
IEnumerable <string> customerOrderNumbers = Enumerable.Empty <string>();
if (condition.CustomerOrderNumber.IndexOf("\n") > 0)
{
customerOrderNumbers = condition.CustomerOrderNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (condition.CustomerOrderNumber.IndexOf(',') > 0)
{
customerOrderNumbers = condition.CustomerOrderNumber.Split(',').Select(s => { return(s.Trim()); });
}
if (customerOrderNumbers != null && customerOrderNumbers.Any())
{
customerOrderNumbers = customerOrderNumbers.Where(c => !string.IsNullOrEmpty(c));
}
if (customerOrderNumbers != null && customerOrderNumbers.Any())
{
sb.Append(" and CustomerOrderNumber in ( ");
foreach (string s in customerOrderNumbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and CustomerOrderNumber like '%" + condition.CustomerOrderNumber.Trim() + "%' ");
}
}
if (!string.IsNullOrEmpty(condition.Str1))
{
IEnumerable <string> Str1 = Enumerable.Empty <string>();
if (condition.Str1.IndexOf("\n") > 0)
{
Str1 = condition.Str1.Split('\n').Select(s => { return(s.Trim()); });
}
if (condition.Str1.IndexOf(',') > 0)
{
Str1 = condition.Str1.Split(',').Select(s => { return(s.Trim()); });
}
if (Str1 != null && Str1.Any())
{
Str1 = Str1.Where(c => !string.IsNullOrEmpty(c));
}
if (Str1 != null && Str1.Any())
{
sb.Append(" and CustomerOrderNumber in (SELECT DISTINCT CustomerOrderNumber FROM dbo.POD WHERE Str1 in(");
foreach (string s in Str1)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ))");
}
else
{
sb.Append(" and CustomerOrderNumber IN(SELECT DISTINCT CustomerOrderNumber FROM dbo.POD WHERE Str1 LIKE '%" + condition.Str1 + "%')");
}
}
//if (!string.IsNullOrEmpty(condition.Str1))
//{
// //sb.Append(" and Str1 like '%" + condition.Str1 + "%' ");
// sb.Append(" and CustomerOrderNumber IN(SELECT DISTINCT CustomerOrderNumber FROM dbo.POD WHERE Str1 LIKE '%" + condition.Str1 + "%')");
//}
if (!string.IsNullOrEmpty(condition.Str2))
{
sb.Append(" and Str2 like '%" + condition.Str2 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str7))
{
sb.Append(" and Str7 like '%" + condition.Str7 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str13))
{
sb.Append(" and Str13 like '%" + condition.Str13 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str17))
{
sb.Append(" and Str17 like '%" + condition.Str17 + "%' ");
}
#endregion
return(sb.ToString());
}
private string GenQueryPodWhere(PodSearchCondition condition, long projectID, string endCities, string endCitiesRuleArea)
{
StringBuilder sb = new StringBuilder();
sb.Append(" where ProjectID=" + projectID + " ");
//百姓网订单排除快递单号
if (condition.CustomerID == 40)
{
sb.Append(" and [Type]='2' ");
}
#region
if (condition.StartCityID != null && condition.StartCityID != 0)
{
sb.Append(" and StartCityID=" + condition.StartCityID + " ");
}
if (condition.EndCityID != null && condition.EndCityID != 0)
{
sb.Append(" and EndCityID in (SELECT ID FROM Func_GetReginAndSunRegions(" + condition.EndCityID + ")) ");
}
else
{
if (!string.IsNullOrEmpty(endCities))
{
sb.Append(" and EndCityID in (").Append(endCities).Append(") ");
}
}
if (condition.UserType == 1)
{
sb.Append(" and PODStateID>2 ");
}
sb.Append(" and CustomerID=40 ");
//if (condition.UserType == 2)
//{
// if (condition.CustomerID == null || condition.CustomerID == 0)
// {
// sb.Append(" and CustomerID in (");
// foreach (long i in condition.CustomerIDs)
// {
// sb.Append(i.ToString());
// sb.Append(",");
// }
// sb.Remove(sb.Length - 1, 1);
// sb.Append(") ");
// }
// else
// {
// sb.Append(" and CustomerID=" + condition.CustomerID + " ");
// }
//}
//else
//{
// if (condition.CustomerID != null && condition.CustomerID != 0)
// {
// sb.Append(" and CustomerID=" + condition.CustomerID + " ");
// }
//}
if (condition.Types != null && condition.Types.Any())
{
sb.Append(" and Type in (");
foreach (int type in condition.Types)
{
sb.Append(type.ToString()).Append(",");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(") ");
}
if (condition.PODStateID != null && condition.PODStateID != 0)
{
sb.Append(" and PODStateID=" + condition.PODStateID + " ");
}
if (condition.TtlOrTplID != null && condition.TtlOrTplID != 0)
{
sb.Append(" and TtlOrTplID=" + condition.TtlOrTplID + " ");
}
if (condition.PODTypeID != null && condition.PODTypeID != 0)
{
sb.Append(" and PODTypeID=" + condition.PODTypeID + " ");
}
if (condition.ShipperTypeID != null && condition.ShipperTypeID != 0)
{
sb.Append(" and ShipperTypeID=" + condition.ShipperTypeID + " ");
}
if (condition.ShipperIDIsNull)
{
sb.Append(" and (ShipperID is null or ShipperID=0) ");
}
else
{
if (condition.ShipperID != null && condition.ShipperID != 0)
{
sb.Append(" and ShipperID=" + condition.ShipperID + " ");
}
}
if (condition.BoxNumber != null && condition.BoxNumber != 0)
{
sb.Append(" and BoxNumber=" + condition.BoxNumber + " ");
}
if (condition.Weight != null && condition.Weight != 0)
{
sb.Append(" and Weight=" + condition.Weight + " ");
}
if (condition.GoodsNumber != null && condition.GoodsNumber != 0)
{
sb.Append(" and GoodsNumber=" + condition.GoodsNumber + " ");
}
if (condition.Volume != null && condition.Volume != 0)
{
sb.Append(" and BoxNumber=" + condition.Volume + " ");
}
if (!string.IsNullOrEmpty(condition.Creator))
{
sb.Append(" and Creator='" + condition.Creator + "' ");
}
if (condition.ActualDeliveryDate.HasValue)
{
sb.Append(" and ActualDeliveryDate >= '" + condition.ActualDeliveryDate.Value.DateTimeToString() + "' ");
}
if (condition.EndActualDeliveryDate.HasValue)
{
sb.Append(" and ActualDeliveryDate <= '" + condition.EndActualDeliveryDate.Value.DateTimeToString() + " 23:59' ");
}
if (condition.CreateTime.HasValue)
{
sb.Append(" and CreateTime >= '" + condition.CreateTime.Value.DateTimeToString() + "' ");
}
if (condition.StartCreateTime.HasValue)
{
sb.Append(" and CreateTime >= '" + condition.StartCreateTime.Value.DateTimeToString() + "' ");
}
if (condition.EndCreateTime.HasValue)
{
sb.Append(" and CreateTime <= '" + condition.EndCreateTime.Value.DateTimeToString() + " 23:59' ");
}
if (!string.IsNullOrEmpty(condition.SystemNumber))
{
IEnumerable <string> systemNumbers = Enumerable.Empty <string>();
if (condition.SystemNumber.IndexOf("\n") > 0)
{
systemNumbers = condition.SystemNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (condition.SystemNumber.IndexOf(',') > 0)
{
systemNumbers = condition.SystemNumber.Split(',').Select(s => { return(s.Trim()); });
}
if (systemNumbers != null && systemNumbers.Any())
{
systemNumbers = systemNumbers.Where(c => !string.IsNullOrEmpty(c));
}
if (systemNumbers != null && systemNumbers.Any())
{
sb.Append(" and SystemNumber in ( ");
foreach (string s in systemNumbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and SystemNumber like '%" + condition.SystemNumber.Trim() + "%' ");
}
}
if (!string.IsNullOrEmpty(condition.CustomerOrderNumber))
{
IEnumerable <string> customerOrderNumbers = Enumerable.Empty <string>();
if (condition.CustomerOrderNumber.IndexOf("\n") > 0)
{
customerOrderNumbers = condition.CustomerOrderNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (condition.CustomerOrderNumber.IndexOf(',') > 0)
{
customerOrderNumbers = condition.CustomerOrderNumber.Split(',').Select(s => { return(s.Trim()); });
}
if (customerOrderNumbers != null && customerOrderNumbers.Any())
{
customerOrderNumbers = customerOrderNumbers.Where(c => !string.IsNullOrEmpty(c));
}
if (customerOrderNumbers != null && customerOrderNumbers.Any())
{
sb.Append(" and CustomerOrderNumber in ( ");
foreach (string s in customerOrderNumbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and CustomerOrderNumber like '%" + condition.CustomerOrderNumber.Trim() + "%' ");
}
}
if (!string.IsNullOrEmpty(condition.Str1))
{
IEnumerable <string> Str1 = Enumerable.Empty <string>();
if (condition.Str1.IndexOf("\n") > 0)
{
Str1 = condition.Str1.Split('\n').Select(s => { return(s.Trim()); });
}
if (condition.Str1.IndexOf(',') > 0)
{
Str1 = condition.Str1.Split(',').Select(s => { return(s.Trim()); });
}
if (Str1 != null && Str1.Any())
{
Str1 = Str1.Where(c => !string.IsNullOrEmpty(c));
}
if (Str1 != null && Str1.Any())
{
sb.Append(" and CustomerOrderNumber in (SELECT DISTINCT CustomerOrderNumber FROM dbo.POD WHERE Str1 in(");
foreach (string s in Str1)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ))");
}
else
{
sb.Append(" and CustomerOrderNumber IN(SELECT DISTINCT CustomerOrderNumber FROM dbo.POD WHERE Str1 LIKE '%" + condition.Str1 + "%')");
}
}
//if (!string.IsNullOrEmpty(condition.Str1))
//{
// //sb.Append(" and Str1 like '%" + condition.Str1 + "%' ");
// sb.Append(" and CustomerOrderNumber IN(SELECT DISTINCT CustomerOrderNumber FROM dbo.POD WHERE Str1 LIKE '%" + condition.Str1 + "%')");
//}
if (!string.IsNullOrEmpty(condition.Str2))
{
sb.Append(" and Str2 like '%" + condition.Str2 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str3))
{
sb.Append(" and Str3 like '%" + condition.Str3 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str4))
{
sb.Append(" and Str4 like '%" + condition.Str4 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str5))
{
sb.Append(" and Str5 like '%" + condition.Str5 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str6))
{
sb.Append(" and Str6 like '%" + condition.Str6 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str7))
{
sb.Append(" and Str7 like '%" + condition.Str7 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str8))
{
sb.Append(" and Str8 like '%" + condition.Str8 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str9))
{
sb.Append(" and Str9 like '%" + condition.Str9 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str10))
{
sb.Append(" and Str10 like '%" + condition.Str10 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str11))
{
sb.Append(" and Str11 like '%" + condition.Str11 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str12))
{
sb.Append(" and Str12 like '%" + condition.Str12 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str13))
{
sb.Append(" and Str13 like '%" + condition.Str13 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str14))
{
sb.Append(" and Str14 like '%" + condition.Str14 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str15))
{
sb.Append(" and Str15 like '%" + condition.Str15 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str16))
{
sb.Append(" and Str16 like '%" + condition.Str16 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str17))
{
sb.Append(" and Str17 like '%" + condition.Str17 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str18))
{
sb.Append(" and Str18 like '%" + condition.Str18 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str19))
{
sb.Append(" and Str19 like '%" + condition.Str19 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str20))
{
sb.Append(" and Str20 like '%" + condition.Str20 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str21))
{
sb.Append(" and Str21 like '%" + condition.Str21 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str22))
{
sb.Append(" and Str22 like '%" + condition.Str22 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str23))
{
sb.Append(" and Str23 like '%" + condition.Str23 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str24))
{
sb.Append(" and Str24 like '%" + condition.Str24 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str25))
{
sb.Append(" and Str25 like '%" + condition.Str25 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str26))
{
sb.Append(" and Str26 like '%" + condition.Str26 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str27))
{
sb.Append(" and Str27 like '%" + condition.Str27 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str28))
{
sb.Append(" and Str28 like '%" + condition.Str28 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str29))
{
sb.Append(" and Str29 like '%" + condition.Str29 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str30))
{
sb.Append(" and Str30 like '%" + condition.Str30 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str31))
{
sb.Append(" and Str31 like '%" + condition.Str31 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str32))
{
sb.Append(" and Str32 like '%" + condition.Str32 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str33))
{
sb.Append(" and Str33 like '%" + condition.Str33 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str34))
{
sb.Append(" and Str34 like '%" + condition.Str34 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str35))
{
sb.Append(" and Str35 like '%" + condition.Str35 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str36))
{
sb.Append(" and Str36 like '%" + condition.Str36 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str37))
{
sb.Append(" and Str37 like '%" + condition.Str37 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str38))
{
sb.Append(" and Str38 like '%" + condition.Str38 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str39))
{
sb.Append(" and Str39 like '%" + condition.Str39 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str40))
{
sb.Append(" and Str40 like '%" + condition.Str40 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str41))
{
sb.Append(" and Str41 like '%" + condition.Str41 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str42))
{
sb.Append(" and Str42 like '%" + condition.Str42 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str43))
{
sb.Append(" and Str43 like '%" + condition.Str43 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str44))
{
sb.Append(" and Str44 like '%" + condition.Str44 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str45))
{
sb.Append(" and Str45 like '%" + condition.Str45 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str46))
{
sb.Append(" and Str46 like '%" + condition.Str46 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str47))
{
sb.Append(" and Str47 like '%" + condition.Str47 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str48))
{
sb.Append(" and Str48 like '%" + condition.Str48 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str49))
{
sb.Append(" and Str49 like '%" + condition.Str49 + "%' ");
}
if (!string.IsNullOrEmpty(condition.Str50))
{
sb.Append(" and Str50 like '%" + condition.Str50 + "%' ");
}
if (condition.DateTime1.HasValue)
{
sb.Append(" and DateTime1 >= '" + condition.DateTime1.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime1.HasValue)
{
sb.Append(" and DateTime1 <= '" + condition.EndDateTime1.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime2.HasValue)
{
sb.Append(" and DateTime2 >= '" + condition.DateTime2.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime2.HasValue)
{
sb.Append(" and DateTime2 <= '" + condition.EndDateTime2.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime3.HasValue)
{
sb.Append(" and DateTime3 >= '" + condition.DateTime3.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime3.HasValue)
{
sb.Append(" and DateTime3 <= '" + condition.EndDateTime3.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime4.HasValue)
{
sb.Append(" and DateTime4 >= '" + condition.DateTime4.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime4.HasValue)
{
sb.Append(" and DateTime4 <= '" + condition.EndDateTime4.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime5.HasValue)
{
sb.Append(" and DateTime5 >= '" + condition.DateTime5.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime5.HasValue)
{
sb.Append(" and DateTime5 <= '" + condition.EndDateTime5.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime6.HasValue)
{
sb.Append(" and DateTime6 >= '" + condition.DateTime6.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime6.HasValue)
{
sb.Append(" and DateTime6 <= '" + condition.EndDateTime6.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime7.HasValue)
{
sb.Append(" and DateTime7 >= '" + condition.DateTime7.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime7.HasValue)
{
sb.Append(" and DateTime7 <= '" + condition.EndDateTime7.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime8.HasValue)
{
sb.Append(" and DateTime8 >= '" + condition.DateTime8.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime8.HasValue)
{
sb.Append(" and DateTime8 <= '" + condition.EndDateTime8.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime9.HasValue)
{
sb.Append(" and DateTime9 >= '" + condition.DateTime9.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime9.HasValue)
{
sb.Append(" and DateTime9 <= '" + condition.EndDateTime9.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime10.HasValue)
{
sb.Append(" and DateTime10 >= '" + condition.DateTime10.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime10.HasValue)
{
sb.Append(" and DateTime10 <= '" + condition.EndDateTime10.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime11.HasValue)
{
sb.Append(" and DateTime11 >= '" + condition.DateTime11.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime11.HasValue)
{
sb.Append(" and DateTime11 <= '" + condition.EndDateTime11.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime12.HasValue)
{
sb.Append(" and DateTime12 >= '" + condition.DateTime12.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime12.HasValue)
{
sb.Append(" and DateTime12 <= '" + condition.EndDateTime12.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime13.HasValue)
{
sb.Append(" and DateTime13 >= '" + condition.DateTime13.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime13.HasValue)
{
sb.Append(" and DateTime13 <= '" + condition.EndDateTime13.Value.DateTimeToString() + " 23:59' ");
}
if (condition.DateTime15.HasValue)
{
sb.Append(" and DateTime15 >= '" + condition.DateTime14.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime15.HasValue)
{
sb.Append(" and DateTime15 <= '" + condition.EndDateTime14.Value.DateTimeToString() + " 23:59' ");
}
//for Tianjin Hub 查询
if (condition.DateTime14.HasValue)
{
sb.Append(" AND CASE CustomerID WHEN 1 THEN DateTime4 WHEN 8 THEN DateTime6 WHEN 2 THEN DateTime2 WHEN 7 THEN DateTime1 END >= '" + condition.DateTime14.Value.DateTimeToString() + "' ");
}
if (condition.EndDateTime14.HasValue)
{
sb.Append(" AND CASE CustomerID WHEN 1 THEN DateTime4 WHEN 8 THEN DateTime6 WHEN 2 THEN DateTime2 WHEN 7 THEN DateTime1 END <= '" + condition.EndDateTime14.Value.DateTimeToString() + " 23:59' ");
}
if (condition.IsSettledForCustomer.HasValue)
{
sb.Append(" and IsSettledForCustomer=" + (condition.IsSettledForCustomer.Value ? "1" : "0") + " ");
}
if (condition.IsSettledForShipper.HasValue)
{
sb.Append(" and IsSettledForShipper=" + (condition.IsSettledForShipper.Value ? "1" : "0") + " ");
}
if (condition.HasShortDial.HasValue)
{
sb.Append(" and HasShortDial=" + (condition.HasShortDial.Value ? "1" : "0") + " ");
}
if (condition.HasDistribution.HasValue)
{
sb.Append(" and HasDistribution=" + (condition.HasDistribution.Value ? "1" : "0") + " ");
}
if (condition.HasExpress.HasValue)
{
sb.Append(" and HasExpress=" + (condition.HasExpress.Value ? "1" : "0") + " ");
}
if (condition.PodMinStateID.HasValue)
{
sb.Append(" and PodStateID >= " + condition.PodMinStateID.Value + " ");
}
if (condition.HasAllocateShipper.HasValue)
{
if (condition.HasAllocateShipper.Value)
{
sb.Append(" and (ShipperID != 0 or ShipperID IS NOT NULL) ");
}
else
{
sb.Append(" and (ShipperID = 0 OR ShipperID IS NULL) ");
}
}
if (condition.ID != 0)
{
sb.Append(" and ID = " + condition.ID + " ");
}
if (!string.IsNullOrEmpty(condition.RuleArea) && condition.RuleArea.Split(',').Count() == 1)
{
sb.Append(" and EndCityID in (SELECT ID FROM Func_GetReginAndSunRegions(" + condition.RuleArea + ")) ");
}
else
{
if (!string.IsNullOrEmpty(endCitiesRuleArea))
{
sb.Append(" and EndCityID in (").Append(endCitiesRuleArea).Append(") ");
}
}
#endregion
return(sb.ToString());
}
