/// <summary>
/// Helper method to set the Sales order filter criteria.
/// </summary>
/// <returns></returns>
public FC.SalesOrder.FC_SalesOrder GetQueryRange()
{
//initiate the onboarging helper.
BM.Helpers.BM_OnboardingHelper OnboardingHelper = new BM.Helpers.BM_OnboardingHelper(Config);
//get the save point of the SalesOrder Index
var SavePoint = OnboardingHelper.GetFactQuerySavePoint(Config.IndexGroup, BM.Helpers.BM_SalesOrderDataTableHelper.SalesOrderIndexerConfig.IndexName);
//set the initial start and end data of the SalesOrder query
DateTime StartDate = Config.StartDateUTC;
DateTime EndDate = StartDate.AddMinutes(Config.QueryInterval);
if (SavePoint.Count > 0)
{
//if save point was returned we set the start and end date based on the save point value.
StartDate = (DateTime)SavePoint[0].Value;
EndDate = StartDate.AddMinutes(Config.QueryInterval);
}
//Create the Sales Order Filter
FC.SalesOrder.FC_SalesOrder SalesOrderFilter = new FC.SalesOrder.FC_SalesOrder()
{
StartDate = StartDate, EndDate = EndDate
};
//return the sales order filter object.
return(SalesOrderFilter);
}
/// <summary>
/// Gets sales orders using the filter criteria supplied.
/// </summary>
/// <param name="filterCriteria"></param>
/// <returns></returns>
public override DTO_Result Get(FC_Base filterCriteria)
{
bcl.DTO.DTO_Result Results = new DTO_Result();
//defines SalesOrderList object to hold all sales orders returned by the poll.
DTO.SalesOrder.DTO_SalesOrderList SalesOrders = new DTO.SalesOrder.DTO_SalesOrderList();
//filter criteria object should be of type FC_SalesOrder
if (filterCriteria.GetType() == typeof(FC.SalesOrder.FC_SalesOrder))
{
//cast the filter criteria to the FC_SalesOrder type.
FC.SalesOrder.FC_SalesOrder cSalesOrder = (FC.SalesOrder.FC_SalesOrder)filterCriteria;
#region "Sql"
string query = @"
SELECT h.[SalesOrderID]
,h.[RevisionNumber]
,h.[OrderDate]
,h.[DueDate]
,h.[ShipDate]
,h.[Status]
,h.[OnlineOrderFlag]
,h.[SalesOrderNumber]
,h.[PurchaseOrderNumber]
,h.[AccountNumber]
,h.[CustomerID]
,h.[SalesPersonID]
,h.[TerritoryID]
,h.[BillToAddressID]
,h.[ShipToAddressID]
,h.[ShipMethodID]
,h.[CreditCardID]
,h.[CreditCardApprovalCode]
,h.[CurrencyRateID]
,h.[SubTotal]
,h.[TaxAmt]
,h.[Freight]
,h.[TotalDue]
,h.[Comment]
,CONVERT(varchar(50), h.[rowguid],120) HeaderRowGuid
,h.[ModifiedDate]
,ISNULL(e.FirstName,'') + ' ' + ISNULL(e.MiddleName,'') + ' ' + ISNULL(e.LastName,'') SalesPerson
,ISNULL(p.FirstName,'') + ' ' + ISNULL(p.MiddleName,'') + ' ' + ISNULL(p.LastName,'') CustomerName
,t.CountryRegionCode + '-' + t.Name Territory
,sh.Name ShipMethod
FROM Sales.SalesOrderHeader h --INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
LEFT OUTER JOIN Sales.Customer c ON h.CustomerID = c.CustomerID
LEFT OUTER JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
LEFT OUTER JOIN Person.Person e ON h.SalesPersonID = e.BusinessEntityID
LEFT OUTER JOIN Purchasing.ShipMethod sh ON h.ShipMethodID = sh.ShipMethodID
LEFT OUTER JOIN Sales.SalesTerritory t ON h.TerritoryID = t.TerritoryID
WHERE h.ModifiedDate > @StartDate AND h.ModifiedDate <= @EndDate
";
#endregion
IEnumerable <dynamic> results = null;
//run the query (via dapper) on the Adventure Works Database and fetch the results.
using (var conn = new System.Data.SqlClient.SqlConnection(Config.AdventureWorksConnection))
{
results = conn.Query <dynamic>(query, new { StartDate = cSalesOrder.StartDate, EndDate = cSalesOrder.EndDate });
}
//we'll use the Slapper.AutoMapper to map results into the SalesOrder Object. The automapper can map data into a complex DTO hierarchy
Slapper.AutoMapper.Cache.ClearInstanceCache();
//specify the primary key columns
Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(DTO.SalesOrder.DTO_SalesOrder), new List <string> {
"SalesOrderID"
});
//map the results into the SalesOrders List object.
SalesOrders = new DTO.SalesOrder.DTO_SalesOrderList(Slapper.AutoMapper.MapDynamic <DTO.SalesOrder.DTO_SalesOrder>(results));
//fill in the results object for returning
Results = new DTO_Result(true, SalesOrders);
}
else
{
//Filter criteria supplied was not of type FC_SalesOrder
Results = new DTO_Result(false, "Filter criteria not of type: FC_SalesOrder");
Log.Error("Filter criteria not of type: FC_SalesOrder");
}
return(Results);
}