public QueryResult QueryProductCostInAndCostOutReport(CostInAndCostOutReportQueryFilter queryFilter)
{
int totalCount;
DataTable table = ObjectFactory <IInventoryQueryDA> .Instance.QueryProductCostInAndCostOutReport(queryFilter, out totalCount);
QueryResult result = new QueryResult()
{
Data = table,
TotalCount = totalCount
};
return(result);
}
/// <summary>
/// 商品入库出库报表导出
/// </summary>
/// <param name="model"></param>
/// <param name="textInfoList"></param>
/// <param name="columnSet"></param>
public void ExportExcelForCostInAndCostOutReport(CostInAndCostOutReportQueryVM model, ColumnSet[] columnSet)
{
CostInAndCostOutReportQueryFilter filter = model.ConvertVM <CostInAndCostOutReportQueryVM, CostInAndCostOutReportQueryFilter>();
filter.PagingInfo = new QueryFilter.Common.PagingInfo()
{
PageIndex = 0,
PageSize = int.MaxValue,
};
string relativeUrl = "/InventoryService/Inventory/QueryCostInAndCostOutReport";
restClient.ExportFile(relativeUrl, filter, columnSet);
}
/// <summary>
/// 商品入库出库报表
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryProductCostInAndCostOutReport(CostInAndCostOutReportQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PagingInfo.SortBy,
StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize,
MaximumRows = filter.PagingInfo.PageSize
};
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductCostInAndCostOutReport");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, pagingInfo, "p.[SysNo] DESC"))
{
builder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, @"(
EXISTS(SELECT TOP 1 1 FROM @tCostIn c WHERE p.SysNo = c.ProductSysNo)OR
EXISTS(SELECT TOP 1 1 FROM @tCostOut c WHERE p.SysNo = c.ProductSysNo))");
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c1.SysNo", DbType.Int32,
"@C1SysNo", QueryConditionOperatorType.Equal, filter.Category1SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c2.SysNo", DbType.Int32,
"@C2SysNo", QueryConditionOperatorType.Equal, filter.Category2SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c3.SysNo", DbType.Int32,
"@C3SysNo", QueryConditionOperatorType.Equal, filter.Category3SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "p.ProductID", DbType.String,
"@ProductID", QueryConditionOperatorType.Equal, filter.ProductID);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "p.ProductName", DbType.String,
"@ProductName", QueryConditionOperatorType.Like, filter.ProductName);
builder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "brand.SysNo", DbType.Int32, filter.BrandSysNoList);
builder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "vendor.SysNo", DbType.Int32, filter.VendorSysNoList);
builder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "stk.SysNo", DbType.Int32, filter.WarehouseSysNoList);
command.CommandText = builder.BuildQuerySql();
command.CommandText = command.CommandText.Replace("#strWhereDate#", string.Format(" (c.InDate BETWEEN '{0}' AND '{1}') "
, filter.DateTimeFrom.HasValue ? filter.DateTimeFrom.Value.ToString("yyyy-MM-dd HH:mm:ss") : DateTime.Parse("1900-01-01 00:00:00").ToString("yyyy-MM-dd HH:mm:ss")
, filter.DateTimeTo.HasValue ? filter.DateTimeTo.Value.ToString("yyyy-MM-dd HH:mm:ss") : DateTime.Parse("2100-12-31 23:59:59").ToString("yyyy-MM-dd HH:mm:ss")));
DataTable dt = command.ExecuteDataTable();
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 商品入库出库报表查询
/// </summary>
/// <param name="model"></param>
/// <param name="callback"></param>
public void QueryCostInAndCostOutReport(CostInAndCostOutReportQueryVM model, int pageIndex, int pageSize, string sortField, EventHandler <RestClientEventArgs <dynamic> > callback)
{
CostInAndCostOutReportQueryFilter filter = model.ConvertVM <CostInAndCostOutReportQueryVM, CostInAndCostOutReportQueryFilter>();
filter.PagingInfo = new QueryFilter.Common.PagingInfo()
{
PageIndex = pageIndex,
PageSize = pageSize,
SortBy = sortField
};
string relativeUrl = "/InventoryService/Inventory/QueryCostInAndCostOutReport";
restClient.QueryDynamicData(relativeUrl, filter, (_, args) =>
{
if (args.FaultsHandle())
{
return;
}
callback(this, new RestClientEventArgs <dynamic>(args.Result, this.page));
});
}
