public IEnumerable <NikeforBSPOD> GetNikePOD(NikePodForBSCondition Condition, int PageIndex, int PageSize, out int RowCount)
{
string SqlWhere = "";
if (Condition != null)
{
SqlWhere = SqlWhereCondition(Condition);
}
DbParam[] dbParams =
{
new DbParam("@SqlWhere", 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, 0, ParameterDirection.Output)
};
DataTable dt = base.ExecuteDataTable("Proc_GetNikePod", dbParams);
var NikePOD = dt.ConvertToEntityCollection <NikeforBSPOD>();
RowCount = (int)dbParams[3].Value;
return(NikePOD);
}
private string SqlWhereCondition(NikePodForBSCondition condition)
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(condition.SystemNumber))
{
IEnumerable <string> SystemNumber = Enumerable.Empty <string>();
if (condition.SystemNumber.IndexOf("\n") > 0)
{
SystemNumber = condition.SystemNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (condition.SystemNumber.IndexOf(',') > 0)
{
SystemNumber = condition.SystemNumber.Split(',').Select(s => { return(s.Trim()); });
}
if (SystemNumber != null && SystemNumber.Any())
{
SystemNumber = SystemNumber.Where(c => !string.IsNullOrEmpty(c));
}
if (SystemNumber != null && SystemNumber.Any())
{
sb.Append(" and p.SystemNumber in ( ");
foreach (string s in SystemNumber)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and p.SystemNumber like '%" + condition.SystemNumber.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 p.Str1 in ( ");
foreach (string s in Str1)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and p.Str1 like '%" + condition.Str1.Trim() + "%' ");
}
}
if (!string.IsNullOrEmpty(condition.CustomerOrderNumber))
{
IEnumerable <string> CustomerOrderNumber = Enumerable.Empty <string>();
if (condition.CustomerOrderNumber.IndexOf("\n") > 0)
{
CustomerOrderNumber = condition.CustomerOrderNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (condition.CustomerOrderNumber.IndexOf(',') > 0)
{
CustomerOrderNumber = condition.CustomerOrderNumber.Split(',').Select(s => { return(s.Trim()); });
}
if (CustomerOrderNumber != null && CustomerOrderNumber.Any())
{
CustomerOrderNumber = CustomerOrderNumber.Where(c => !string.IsNullOrEmpty(c));
}
if (CustomerOrderNumber != null && CustomerOrderNumber.Any())
{
sb.Append(" and p.CustomerOrderNumber in ( ");
foreach (string s in CustomerOrderNumber)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and p.CustomerOrderNumber like '%" + condition.CustomerOrderNumber.Trim() + "%' ");
}
}
if (!string.IsNullOrEmpty(condition.PodStateName))
{
sb.Append(" and p.PodStateName = '" + condition.PodStateName.Trim() + "' ");
}
if (!string.IsNullOrEmpty(condition.ShipperName))
{
sb.Append(" and p.ShipperName = '" + condition.ShipperName.Trim() + "' ");
}
if (!string.IsNullOrEmpty(condition.TtlOrTplName))
{
sb.Append(" and p.TtlOrTplName = '" + condition.TtlOrTplName.Trim() + "' ");
}
if (!string.IsNullOrEmpty(condition.StartCityName))
{
sb.Append(" and p.StartCityName = '" + condition.StartCityName.Trim() + "' ");
}
if (!string.IsNullOrEmpty(condition.EndCityName))
{
sb.Append(" and p.EndCityName = '" + condition.EndCityName.Trim() + "' ");
}
if (condition.StartDeliveryTime != null)
{
sb.Append(" and p.ActualDeliveryDate >= '" + condition.StartDeliveryTime + "' ");
}
if (condition.EndDeliveryTime != null)
{
sb.Append(" and p.ActualDeliveryDate < '" + condition.EndDeliveryTime + " 23:59:59'");
}
if (condition.IsConversion != null)
{
if (condition.IsConversion == 1)
{
sb.Append(" and p.Str50 =1 ");
}
else
{
sb.Append(" and p.Str50 is NULL ");
}
}
return(sb.ToString());
}
