public GetReplenishmentDetailByConditionResponse GetReplenishmentByCondition(ReplenishmentSearchCondition SearchCondition, int pageIndex, int pageSize, out int rowCount)
{
rowCount = 0;
GetReplenishmentDetailByConditionResponse response = new GetReplenishmentDetailByConditionResponse();
string sqlWhere = this.GenGetReplenishmentWhere(SearchCondition);
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)
};
DataSet ds = this.ExecuteDataSet("Proc_WMS_ReplenishmentByCondition", dbParams);
rowCount = (int)dbParams[3].Value;
response.ReplenishmentCollection = ds.Tables[0].ConvertToEntityCollection <Replenishment>();
response.ReplenishmentDetailCollection = ds.Tables[1].ConvertToEntityCollection <ReplenishmentDetail>();
return(response);
}
private string GenGetReplenishmentWhere(ReplenishmentSearchCondition SearchCondition)
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(SearchCondition.ReplenishmentNumber))
{
IEnumerable <string> numbers = Enumerable.Empty <string>();
if (SearchCondition.ReplenishmentNumber.IndexOf("\n") > 0)
{
numbers = SearchCondition.ReplenishmentNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (SearchCondition.ReplenishmentNumber.IndexOf(',') > 0)
{
numbers = SearchCondition.ReplenishmentNumber.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.ReplenishmentNumber in ( ");
foreach (string s in numbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and a.ReplenishmentNumber like '%" + SearchCondition.ReplenishmentNumber.Trim() + "%' ");
}
}
if (SearchCondition.CustomerID != null)
{
sb.Append(" AND a.CustomerID=").Append(SearchCondition.CustomerID).Append(" ");
}
if (!string.IsNullOrEmpty(SearchCondition.WarehouseID.ToString()))
{
sb.Append(" AND a.WarehouseID=" + SearchCondition.WarehouseID);
}
//if (!string.IsNullOrEmpty(SearchCondition.AdjustmentType))
//{
// sb.Append(" AND a.AdjustmentType='").Append(SearchCondition.AdjustmentType).Append("' ");
//}
if (SearchCondition.Status != 0)
{
sb.Append(" AND a.Status='").Append(SearchCondition.Status).Append("' ");
}
if (SearchCondition.StartCreateTime != null)
{
sb.Append(" AND a.CreateTime >='").Append(SearchCondition.StartCreateTime.DateTimeToString("yyyy-MM-dd 00:00:00.00")).Append("' ");
}
if (SearchCondition.EndCreateTime != null)
{
sb.Append(" AND a.CreateTime <='").Append(SearchCondition.EndCreateTime.DateTimeToString("yyyy-MM-dd 23:59:59.99")).Append("' ");
}
//if (SearchCondition.IsHold != 0)
//{
// sb.Append(" AND a.IsHold ='").Append(SearchCondition.IsHold).Append("' ");
//}
if (SearchCondition.Remark != null)
{
sb.Append(" and a.Remark like '%" + SearchCondition.Remark.Trim() + "%' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str1))
{
sb.Append(" AND a.str1='").Append(SearchCondition.str1).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str2))
{
sb.Append(" AND a.str2='").Append(SearchCondition.str2).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str3))
{
sb.Append(" AND a.str3='").Append(SearchCondition.str3).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str4))
{
sb.Append(" AND a.str4='").Append(SearchCondition.str4).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str5))
{
sb.Append(" AND a.str5='").Append(SearchCondition.str5).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str6))
{
sb.Append(" AND a.str6='").Append(SearchCondition.str6).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str7))
{
sb.Append(" AND a.str7='").Append(SearchCondition.str7).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str8))
{
sb.Append(" AND a.str8=").Append(SearchCondition.str8).Append(" ");
}
if (!string.IsNullOrEmpty(SearchCondition.str9))
{
sb.Append(" AND a.str9='").Append(SearchCondition.str9).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str10))
{
sb.Append(" AND a.str10='").Append(SearchCondition.str10).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str11))
{
sb.Append(" AND a.str11='").Append(SearchCondition.str11).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str12))
{
sb.Append(" AND a.str12='").Append(SearchCondition.str12).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str13))
{
sb.Append(" AND a.str13='").Append(SearchCondition.str13).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str14))
{
sb.Append(" AND a.str14='").Append(SearchCondition.str14).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str15))
{
sb.Append(" AND a.str15='").Append(SearchCondition.str15).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str16))
{
sb.Append(" AND a.str16='").Append(SearchCondition.str16).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str17))
{
sb.Append(" AND a.str17='").Append(SearchCondition.str17).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str18))
{
sb.Append(" AND b.18='").Append(SearchCondition.str18).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str19))
{
sb.Append(" AND b.19='").Append(SearchCondition.str19).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str20))
{
sb.Append(" AND b.20='").Append(SearchCondition.str20).Append("' ");
}
if (SearchCondition.Int1 != 0 && !(string.IsNullOrEmpty(SearchCondition.Int1.ToString())))
{
sb.Append(" AND a.Int1=").Append(SearchCondition.Int1).Append(" ");
}
if (SearchCondition.Int2 != 0 && !(string.IsNullOrEmpty(SearchCondition.Int2.ToString())))
{
sb.Append(" AND a.Int2=").Append(SearchCondition.Int2).Append(" ");
}
if (SearchCondition.Int3 != 0 && !(string.IsNullOrEmpty(SearchCondition.Int3.ToString())))
{
sb.Append(" AND a.Int3=").Append(SearchCondition.Int3).Append(" ");
}
if (SearchCondition.Int4 != 0 && !(string.IsNullOrEmpty(SearchCondition.Int4.ToString())))
{
sb.Append(" AND a.Int4=").Append(SearchCondition.Int4).Append(" ");
}
if (SearchCondition.Int5 != 0 && !(string.IsNullOrEmpty(SearchCondition.Int5.ToString())))
{
sb.Append(" AND a.Int5=").Append(SearchCondition.Int5).Append(" ");
}
return(sb.ToString());
}
