/// <summary>
/// 导出库存变更数据
/// </summary>
/// <param name="SearchCondition"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowCount"></param>
/// <returns></returns>
public GetAdjustmentDetailByConditionResponse ExportadjustByCondition(AdjustmentSearchCondition SearchCondition)
{
GetAdjustmentDetailByConditionResponse response = new GetAdjustmentDetailByConditionResponse();
string sqlWhere = this.GenGetAdjustWhere(SearchCondition);
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sqlWhere, ParameterDirection.Input),
};
DataSet ds = this.ExecuteDataSet("Proc_WMS_ExportAdjustmentByCondition", dbParams);
response.AdjustmentCollection = ds.Tables[0].ConvertToEntityCollection <Adjustment>();
response.AdjustmentDetailCollection = ds.Tables[1].ConvertToEntityCollection <AdjustmentDetail>();
return(response);
}
/// <summary>
/// 库存变更数据查询
/// </summary>
/// <param name="SearchCondition"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowCount"></param>
/// <returns></returns>
public GetAdjustmentDetailByConditionResponse GetadjustByCondition(AdjustmentSearchCondition SearchCondition, int pageIndex, int pageSize, out int rowCount)
{
GetAdjustmentDetailByConditionResponse response = new GetAdjustmentDetailByConditionResponse();
string sqlWhere = this.GenGetAdjustWhere(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_GetAdjustmentByCondition", dbParams);
rowCount = (int)dbParams[3].Value;
response.AdjustmentCollection = ds.Tables[0].ConvertToEntityCollection <Adjustment>();
//response.AdjustmentDetailCollection = ds.Tables[1].ConvertToEntityCollection<AdjustmentDetail>();
return(response);
}
private string GenGetAdjustWhere(AdjustmentSearchCondition SearchCondition)
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(SearchCondition.AdjustmentNumber))
{
IEnumerable <string> numbers = Enumerable.Empty <string>();
if (SearchCondition.AdjustmentNumber.IndexOf("\n") > 0)
{
numbers = SearchCondition.AdjustmentNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (SearchCondition.AdjustmentNumber.IndexOf(',') > 0)
{
numbers = SearchCondition.AdjustmentNumber.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.AdjustmentNumber in ( ");
foreach (string s in numbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and a.AdjustmentNumber like '%" + SearchCondition.AdjustmentNumber.Trim() + "%' ");
}
}
if (SearchCondition.CustomerID != 0)
{
sb.Append(" AND a.CustomerID = ").Append(SearchCondition.CustomerID);
}
if (SearchCondition.CustomerID == 0 && SearchCondition.CustomerIDs != null)
{
IEnumerable <string> numbers = Enumerable.Empty <string>();
numbers = SearchCondition.CustomerIDs.Split(',').Select(s => { return(s.Trim()); });
if (numbers != null && numbers.Any())
{
numbers = numbers.Where(c => !string.IsNullOrEmpty(c));
}
sb.Append(" and a.CustomerID in ( ");
foreach (string s in numbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
if (!string.IsNullOrEmpty(SearchCondition.Warehouse))
{
sb.Append(" AND a.Warehouse in (select WarehouseName from wms_warehouse where id in ( ").Append(SearchCondition.Warehouse.Trim()).Append(")) ");
}
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.StartAdjustmentDate != null)
{
sb.Append(" AND a.AdjustmentTime >='").Append(SearchCondition.StartAdjustmentDate.DateTimeToString("yyyy-MM-dd 00:00:00.00")).Append("' ");
}
if (SearchCondition.EndAdjustmentDate != null)
{
sb.Append(" AND a.AdjustmentTime <='").Append(SearchCondition.EndAdjustmentDate.DateTimeToString("yyyy-MM-dd 23:59:59.99")).Append("' ");
}
if (SearchCondition.IsHold != 0)
{
sb.Append(" AND a.IsHold ='").Append(SearchCondition.IsHold).Append("' ");
}
//if (SearchCondition.AdjustmentReason != null)
//{
// sb.Append(" and a.AdjustmentReason like '%" + SearchCondition.AdjustmentReason.Trim() + "%' ");
//}
if (!string.IsNullOrEmpty(SearchCondition.AdjustmentReason))
{
sb.Append(" AND a.AdjustmentReason='").Append(SearchCondition.AdjustmentReason).Append("' ");
}
//备注
if (!string.IsNullOrEmpty(SearchCondition.Remark))
{
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.SKU='").Append(SearchCondition.str18).Append("' ");
}
if (!string.IsNullOrEmpty(SearchCondition.str19))
{
sb.Append(" AND b.FromArea=(SELECT TOP 1 AreaName FROM dbo.WMS_Warehouse_Area WHERE ID=" + SearchCondition.str19 + ")").Append(" ");
}
if (!string.IsNullOrEmpty(SearchCondition.str20))
{
sb.Append(" AND b.ToLocation=(SELECT TOP 1 Location FROM dbo.WMS_Warehouse_Location WHERE Location='" + 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());
}
