/// <summary>
/// 商品库龄报表
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryProductStockAgeReport(StockAgeReportQueryFilter 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("QueryProductStockAgeReport");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, pagingInfo, "p.[SysNo] DESC"))
{
command.AddInputParameter("@StatisticDate", DbType.DateTime, filter.StatisticDate.AddDays(1).AddSeconds(-1));
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c1.SysNo", DbType.Int32,
"@C1SysNo", QueryConditionOperatorType.Equal, filter.C1SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c2.sysno", DbType.Int32,
"@C2SysNo", QueryConditionOperatorType.Equal, filter.C2SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c3.sysno", DbType.Int32,
"@C3SysNo", QueryConditionOperatorType.Equal, filter.C3SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductSysNo", DbType.Int32,
"@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNo", DbType.Int32,
"@StockSysNo", QueryConditionOperatorType.Equal, filter.StockSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vendor.sysno", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo);
#region 根据选择的库龄类型构造时间条件
StringBuilder strUpdateProductStockAgeInventory = new StringBuilder();
foreach (string stockAgeTypeID in filter.StockAgeTypeList)
{
Tuple <DateTime, DateTime, string> dateRange = GetStockAgeDateRange(filter.StatisticDate.Date, stockAgeTypeID);
strUpdateProductStockAgeInventory.AppendFormat(@"UPDATE t
SET t.Amt{2} = i.Amount,t.Qty{2} = i.Quantity
FROM @ProductStockAgeInventory t
LEFT JOIN ipp3.dbo.FN_EC_GetProductPOInventory('{0}','{1}') i
ON t.ProductSysNo = i.ProductSysNo AND t.StockSysNo = i.StockSysNo AND t.VendorSysNo = i.VendorSysNo;"
, dateRange.Item1.ToString("yyyy-MM-dd HH:mm:ss"), dateRange.Item2.ToString("yyyy-MM-dd HH:mm:ss"), dateRange.Item3);
strUpdateProductStockAgeInventory.AppendLine();
}
#endregion
command.CommandText = builder.BuildQuerySql();
command.CommandText = command.CommandText.Replace("#strUpdateProductStockAgeInventory#", strUpdateProductStockAgeInventory.ToString());
DataTable dt = command.ExecuteDataTable();
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 商品库龄报表查询
/// </summary>
/// <param name="model"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="sortField"></param>
/// <param name="callback"></param>
public void QueryStockAgeReport(StockAgeReportQueryVM model, int pageIndex, int pageSize, string sortField, EventHandler <RestClientEventArgs <dynamic> > callback)
{
StockAgeReportQueryFilter filter = EntityConverter <StockAgeReportQueryVM, StockAgeReportQueryFilter> .Convert(model, (s, t) =>
{
t.StockAgeTypeList = s.StockAgeTypeList.Where(x => x.Value != null && x.Selected).Select(x => x.Value).ToList();
}, "StockAgeTypeList");
filter.PagingInfo = new QueryFilter.Common.PagingInfo()
{
PageIndex = pageIndex,
PageSize = pageSize,
SortBy = sortField
};
string relativeUrl = "/InventoryService/Inventory/QueryStockAgeReport";
restClient.QueryDynamicData(relativeUrl, filter, (_, args) =>
{
if (args.FaultsHandle())
{
return;
}
callback(this, new RestClientEventArgs <dynamic>(args.Result, this.page));
});
}
public QueryResult QueryProductStockAgeReport(StockAgeReportQueryFilter queryFilter)
{
int totalCount;
DataTable table = ObjectFactory <IInventoryQueryDA> .Instance.QueryProductStockAgeReport(queryFilter, out totalCount);
QueryResult result = new QueryResult()
{
Data = table,
TotalCount = totalCount
};
return(result);
}
/// <summary>
/// 商品库龄报表导出
/// </summary>
/// <param name="model"></param>
/// <param name="columnSet"></param>
public void ExportExcelForStockAgeReport(StockAgeReportQueryVM model, ColumnSet[] columnSet)
{
StockAgeReportQueryFilter filter = EntityConverter <StockAgeReportQueryVM, StockAgeReportQueryFilter> .Convert(model, (s, t) =>
{
t.StockAgeTypeList = s.StockAgeTypeList.Where(x => x.Value != null && x.Selected).Select(x => x.Value).ToList();
}, "StockAgeTypeList");
filter.PagingInfo = new QueryFilter.Common.PagingInfo()
{
PageIndex = 0,
PageSize = int.MaxValue,
};
//List<TextInfo> textInfoList = new List<TextInfo>();
//textInfoList.Add(new TextInfo() { Title = "Neticom (Hong Kong) Limited", Memo = string.Empty });
//textInfoList.Add(new TextInfo() { Title = "As at", Memo = filter.StatisticDate.ToString("dd/MM/yyyy") });
//textInfoList.Add(new TextInfo() { Title = "Reporting currency:", Memo = "RMB / HKD" });
string relativeUrl = "/InventoryService/Inventory/QueryStockAgeReport";
restClient.ExportFile(relativeUrl, filter, columnSet);
}
