//private string SKUSearchConditionRRTime(GetReceiptbyCondition SearchCondition)
//{
// StringBuilder sb = new StringBuilder();
// if (SearchCondition.StartShelvesTime != null)
// {
// sb.Append("and RR.CreateTime>='" + SearchCondition.StartShelvesTime + "'");
// }
// if (SearchCondition.EndShelvesTime != null)
// {
// sb.Append("and RR.CreateTime<'" + SearchCondition.EndShelvesTime.DateTimeToString() + "23:59'");
// }
// return sb.ToString();
//}
/// <summary>
/// 导出上架单
/// </summary>
/// <param name="Request"></param>
/// <returns></returns>
public IEnumerable <StoresByGetReceipt> GetReceiptExecl(GetReceiptbyCondition Request)
{
string WhereSql = "";
if (Request != null)
{
WhereSql = SKUSearchCondition(Request);
}
DbParam[] dbParams = new DbParam[] {
new DbParam("@WhereSql", DbType.String, WhereSql, ParameterDirection.Input),
};
DataTable dt = this.ExecuteDataTable("[Proc_WMS_GetReceiptExecl]", dbParams);
return(dt.ConvertToEntityCollection <StoresByGetReceipt>());
}
public IEnumerable <StoresByGetReceipt> GetReceiptExport(GetReceiptbyCondition Request)
{
string WhereSql = "";
// string WhereSqlRRTime = "";
if (Request != null)
{
WhereSql = ExportSearchCondition(Request);
//WhereSqlRRTime = SKUSearchConditionRRTime(Request);
}
int tempRowCount = 0;
DbParam[] dbParams = new DbParam[] {
new DbParam("@WhereSql", DbType.String, WhereSql, ParameterDirection.Input)
};
DataTable dt = this.ExecuteDataTable("[Proc_WMS_CarryOutShelves]", dbParams);
return(dt.ConvertToEntityCollection <StoresByGetReceipt>());
}
public IEnumerable <StoresByGetReceipt> GetReceipt(GetReceiptbyCondition Request, int pageIndex, int pageSize, out int rowCount)
{
string WhereSql = "";
// string WhereSqlRRTime = "";
if (Request != null)
{
WhereSql = SKUSearchCondition(Request);
//WhereSqlRRTime = SKUSearchConditionRRTime(Request);
}
int tempRowCount = 0;
DbParam[] dbParams = new DbParam[] {
new DbParam("@WhereSql", DbType.String, WhereSql, ParameterDirection.Input),
// new DbParam("@WhereSqlRRTime", DbType.String, WhereSql, 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_WMS_GetReceipt]", dbParams);
rowCount = (int)dbParams[3].Value;
return(dt.ConvertToEntityCollection <StoresByGetReceipt>());
}
private string ExportSearchCondition(GetReceiptbyCondition SearchCondition)
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(SearchCondition.CustomerIDs))
{
sb.Append(" and R.CustomerID in (" + SearchCondition.CustomerIDs + ")");
}
if (!string.IsNullOrEmpty(SearchCondition.ExternReceiptNumber))
{
IEnumerable <string> numbers = Enumerable.Empty <string>();
if (SearchCondition.ExternReceiptNumber.IndexOf("\n") > 0)
{
numbers = SearchCondition.ExternReceiptNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (SearchCondition.ExternReceiptNumber.IndexOf(',') > 0)
{
numbers = SearchCondition.ExternReceiptNumber.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 R.ExternReceiptNumber in ( ");
foreach (string s in numbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and R.ExternReceiptNumber like '%" + SearchCondition.ExternReceiptNumber.Trim() + "%' ");
}
// sb.Append("and R.ExternReceiptNumber='" + SearchCondition.ExternReceiptNumber+"'");
}
if (!string.IsNullOrEmpty(SearchCondition.ReceiptNumber))
{
IEnumerable <string> numbers = Enumerable.Empty <string>();
if (SearchCondition.ReceiptNumber.IndexOf("\n") > 0)
{
numbers = SearchCondition.ReceiptNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (SearchCondition.ReceiptNumber.IndexOf(',') > 0)
{
numbers = SearchCondition.ReceiptNumber.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 R.ReceiptNumber in ( ");
foreach (string s in numbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and R.ReceiptNumber like '%" + SearchCondition.ReceiptNumber.Trim() + "%' ");
}
}
if (SearchCondition.ShelvesState != null)
{
sb.Append("and R.[Status]='" + SearchCondition.ShelvesState + "'");
}
if (SearchCondition.StartCreateTime != null)
{
sb.Append("and R.CreateTime>='" + SearchCondition.StartCreateTime.DateTimeToString("yyyy-MM-dd 00:00:00.00")).Append("' ");
}
if (SearchCondition.EndCreateTime != null)
{
sb.Append("and R.CreateTime<'" + SearchCondition.EndCreateTime.DateTimeToString("yyyy-MM-dd 23:59:59.99")).Append("' ");
}
if (SearchCondition.StartStorageTime != null)
{
sb.Append("and R.ReceiptDate>='" + SearchCondition.StartStorageTime.DateTimeToString("yyyy-MM-dd 00:00:00.00")).Append("' ");
}
if (SearchCondition.EndStorageTime != null)
{
sb.Append("and R.ReceiptDate<'" + SearchCondition.EndStorageTime.DateTimeToString("yyyy-MM-dd 23:59:59.99")).Append("' ");
}
if (SearchCondition.WarehouseID != 0)
{
sb.Append("and R.WarehouseID=" + SearchCondition.WarehouseID + " ");
}
if (!string.IsNullOrEmpty(SearchCondition.WarehouseIDs))
{
sb.Append("and R.WarehouseID in (" + SearchCondition.WarehouseIDs + ") ");
}
if ((SearchCondition.CustomerID == null ? 0 : SearchCondition.CustomerID) != 0)
{
sb.Append("and R.CustomerID=" + SearchCondition.CustomerID);
}
return(sb.ToString());
}
