// GET: api/Items/5
public Item Get(int id)
{
using (WarehouseConnection db = new WarehouseConnection())
{
return(Item.Map(db.GetItemById(id)));
}
}
public Item GetItemById(int itemId)
{
using (WarehouseConnection db = new WarehouseConnection())
{
log.Info(String.Concat("Item with id: ", itemId, " requested").WithDate());
return(db.GetItemById(itemId));
}
}
private async Task <IList <string> > GetProjectTypes()
{
using (var connection = await WarehouseConnection.ConnectTo())
{
// Execute the query and return the results
return((await connection.QueryAsync <string>("SELECT ProjectTypes FROM Dimension_Project")).ToList());
}
}
public IEnumerable <Item> GetItemsWithNoQty()
{
using (WarehouseConnection db = new WarehouseConnection())
{
log.Info(String.Concat("Items with 0 quantity requested.").WithDate());
return(db.GetItemsWithNoQty());
}
}
public IEnumerable <Item> GetItemsInOrder(Order order)
{
using (WarehouseConnection db = new WarehouseConnection())
{
log.Info(String.Concat("Items by Order id: ", order.OrderId, " requested. ").WithDate());
return(db.GetItemsInOrder(order));
}
}
// GET: api/Items
public IEnumerable <Item> Get()
{
using (WarehouseConnection db = new WarehouseConnection())
{
var items = Item.Map(db.GetAllItems());
return(items);
}
}
public bool InsertNewItem(Item item)
{
log.Info("Item insertion requested".WithDate());
using (WarehouseConnection db = new WarehouseConnection())
{
return(db.InsertNewItem(item));
}
}
public bool InsertNewItems(IEnumerable <Item> items)
{
log.Info("Items insertion requested".WithDate());
using (WarehouseConnection db = new WarehouseConnection())
{
return(db.InsertNewItems(items));
}
}
public bool RemoveItem(Item item)
{
using (WarehouseConnection db = new WarehouseConnection())
{
log.Info(String.Concat("Remove Item with id: ", item.ItemId, " requested").WithDate());
return(db.RemoveItem(item));
}
}
public IEnumerable <Item> GetAllItems()
{
using (WarehouseConnection db = new WarehouseConnection())
{
log.Info("All items requested".WithDate());
return(db.GetAllItems());
}
}
public IEnumerable <Item> GetItemsByType(ItemType itemType)
{
using (WarehouseConnection db = new WarehouseConnection())
{
log.Info(String.Concat("Items by type: ", itemType.ToString(), " requested. ").WithDate());
return(db.GetItemByType(itemType));
}
}
// POST: api/Items
public void Post(Item item)
{
if (item != null)
{
using (WarehouseConnection db = new WarehouseConnection())
{
db.InsertNewItem(item);
}
}
}
public App()
{
var connection = new WarehouseConnection();
var mainViewModel = new MainViewModel(BuyerName, connection, new BuyerRepository(ConnectionString));
var window = new MainWindow
{
DataContext = mainViewModel
};
window.ShowDialog();
}
private async void button1_ClickAsync(object sender, EventArgs e)
{
DataGridViewSelectedRowCollection r = pending_grid.SelectedRows;
for (int i = 0; i < r.Count; ++i)
{
string orderID = r[i].Cells[0].Value.ToString();
await WarehouseConnection.UpdateOrderAsync(orderID);
UpdateOrders();
}
}
private async Task ConfirmPackageExport(WarehousePackageReference package)
{
Trace.TraceInformation(string.Format("{0}: Marked Package Exported", package.PackageId));
using (var connection = await WarehouseConnection.ConnectTo())
{
await connection.QueryAsync <int>(
"ConfirmPackageExported",
param : new { PackageId = package.PackageId, DirtyCount = package.DirtyCount },
commandType : CommandType.StoredProcedure);
}
Trace.TraceInformation(string.Format("{0}: Marking Package Exported", package.PackageId));
}
private async Task <JArray> GatherProjectTypeRanking(string projectType)
{
using (var connection = await WarehouseConnection.ConnectTo())
{
// Get the script
var script = await ResourceHelpers.ReadResourceFile("Scripts.SearchRanking_ByProjectType.sql");
// Execute it and return the results
return(new JArray(
(await connection.QueryWithRetryAsync <SearchRankingEntry>(script, new { RankingCount, ProjectGuid = projectType }, commandTimeout: 120))
.Select(e => e.PackageId)));
}
}
private async Task <JArray> GatherOverallRankings()
{
using (var connection = await WarehouseConnection.ConnectTo())
{
// Get the script
var script = await ResourceHelpers.ReadResourceFile("NuGet.Services.Work.Jobs.Scripts.SearchRanking_Overall.sql");
// Execute it and return the results
return(new JArray(
(await connection.QueryWithRetryAsync <SearchRankingEntry>(script, new { RankingCount }, commandTimeout: 120))
.Select(e => e.PackageId)));
}
}
// DELETE: api/Items/5
public void Delete(int id)
{
using (WarehouseConnection db = new WarehouseConnection())
{
if (db.GetItemById(id) != null)
{
db.DeleteItem(id);
}
else
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
}
}
// PUT: api/Items/5
public void Put(int id, Item item)
{
using (WarehouseConnection db = new WarehouseConnection())
{
if (db.GetItemById(id) != null)
{
db.RemoveItem(item);
db.InsertNewItem(item.ReverseMap());
}
else
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
}
}
private async Task ConfirmPackageExport(WarehousePackageReference package)
{
Log.MarkingPackageExported(package.PackageId);
if (!WhatIf)
{
using (var connection = await WarehouseConnection.ConnectTo())
{
await connection.QueryAsync <int>(
"ConfirmPackageExported",
param : new { PackageId = package.PackageId, DirtyCount = package.DirtyCount },
commandType : CommandType.StoredProcedure);
}
}
Log.MarkedPackageExported(package.PackageId);
}
// POST: api/Items
public HttpResponseMessage Post([FromBody] Item item)
{
if (item != null)
{
using (WarehouseConnection db = new WarehouseConnection())
{
db.InsertNewItem(item.ReverseMap());
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, item);
return(response);
}
}
else
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
}
private async Task <DataTable> CollectReportData(string reportName, string scriptName, params Tuple <string, int, string>[] parameters)
{
Log.CollectingReportData(reportName);
DataTable table = null;
using (var connection = await WarehouseConnection.ConnectTo())
{
// Get the data
await WithRetry(async() =>
{
table = await ExecuteSql(scriptName, parameters);
});
}
Debug.Assert(table != null);
Log.CollectedReportData(reportName, table.Rows.Count);
return(table);
}
private async Task <DataTable> CollectReportData(string reportName, string scriptName, params Tuple <string, int, string>[] parameters)
{
Trace.TraceInformation(string.Format("{0}: Collecting data", reportName));
DataTable table = null;
using (var connection = await WarehouseConnection.ConnectTo())
{
// Get the data
await WithRetry(async() =>
{
table = await ExecuteSql(scriptName, parameters);
});
}
Debug.Assert(table != null);
Trace.TraceInformation(string.Format("{0}: Collected {1} rows", reportName, table.Rows.Count));
return(table);
}
public App()
{
var connection = new WarehouseConnection();
var warehouse = new Warehouse(connection, BuyerName);
var portfolio = new BuyerPortfolio();
var launcher = new BuyerLauncher(warehouse, portfolio);
var mainViewModel = new MainViewModel(portfolio);
mainViewModel.AddUserRequestListener(launcher);
var window = new MainWindow
{
DataContext = mainViewModel
};
window.ShowDialog();
}
public override async Task <bool> Run()
{
using (var connection = await WarehouseConnection.ConnectTo())
{
Trace.TraceInformation(string.Format("Rebuilding Indexes in {0}/{1}", WarehouseConnection.DataSource, WarehouseConnection.InitialCatalog));
SqlCommand rebuild = connection.CreateCommand();
rebuild.CommandText = "RebuildIndexes";
rebuild.CommandTimeout = CommandTimeOut > 0 ? CommandTimeOut :
60 * // seconds
60 * // minutes
8; // hours
await rebuild.ExecuteNonQueryAsync();
Trace.TraceInformation(string.Format("Rebuilt Indexes in {0}/{1}", WarehouseConnection.DataSource, WarehouseConnection.InitialCatalog));
}
return(true);
}
private async Task RebuildPackageReports(bool all)
{
IList <WarehousePackageReference> packages;
using (var connection = await WarehouseConnection.ConnectTo())
{
if (all)
{
Log.GettingAllPackages();
packages = (await connection.QueryAsync <WarehousePackageReference>("SELECT DISTINCT packageId AS PackageId, NULL as DirtyCount FROM Dimension_Package")).ToList();
}
else
{
Log.GettingPackagesInNeedOfUpdate();
packages = (await connection.QueryAsync <WarehousePackageReference>("GetPackagesForExport", commandType: CommandType.StoredProcedure)).ToList();
}
Log.GotPackages(packages.Count);
}
Parallel.ForEach(packages, new ParallelOptions()
{
MaxDegreeOfParallelism = 4
}, package =>
{
CreateReport(
PackageReportDetailBaseName + package.PackageId.ToLowerInvariant(),
"NuGet.Services.Work.Jobs.Scripts.DownloadReport_RecentPopularityDetailByPackage.sql",
t =>
{
var jobj = MakeReportJson(t);
TotalDownloads(jobj);
SortItems(jobj);
return(jobj.ToString(JsonFormat.SerializerSettings.Formatting));
},
Tuple.Create("@PackageId", 128, package.PackageId)).Wait();
if (!all)
{
ConfirmPackageExport(package).Wait();
}
});
}
private async Task RebuildPackageReports(bool all)
{
IList <WarehousePackageReference> packages;
using (var connection = await WarehouseConnection.ConnectTo())
{
if (all)
{
Trace.TraceInformation("Getting list of all packages.");
packages = (await connection.QueryAsync <WarehousePackageReference>("SELECT DISTINCT packageId AS PackageId, NULL as DirtyCount FROM Dimension_Package")).ToList();
}
else
{
Trace.TraceInformation("Getting list of packages in need of update.");
packages = (await connection.QueryAsync <WarehousePackageReference>("GetPackagesForExport", commandType: CommandType.StoredProcedure)).ToList();
}
Trace.TraceInformation(string.Format("Found {0} packages to update.", packages.Count));
}
Parallel.ForEach(packages, new ParallelOptions()
{
MaxDegreeOfParallelism = 4
}, package =>
{
CreateReport(
PackageReportDetailBaseName + package.PackageId.ToLowerInvariant(),
"Scripts.DownloadReport_RecentPopularityDetailByPackage.sql",
t =>
{
var jobj = MakeReportJson(t);
TotalDownloads(jobj);
SortItems(jobj);
return(jobj.ToString());
},
Tuple.Create("@PackageId", 128, package.PackageId)).Wait();
if (!all)
{
ConfirmPackageExport(package).Wait();
}
});
}
private void UpdateOrders()
{
pending_grid.Rows.Clear();
completed_grid.Rows.Clear();
List <BookOrder> orders = WarehouseConnection.GetOrders();
foreach (BookOrder order in orders)
{
switch (order.OrderState)
{
case State.AWAITING_EXPEDITION:
pending_grid.Rows.Add(order.OrderID, order.BookID, order.Quantity, ((DateTime)order.DispatchDate).ToString("dd/MM/yyyy"));
break;
case State.DISPATCHED:
completed_grid.Rows.Add(order.OrderID, order.BookID, order.Quantity, ((DateTime)order.DispatchDate).ToString("dd/MM/yyyy"));
break;
}
}
}
// We don't use Dapper because we need a general purpose method to load any resultset
// This method loads a tuple where the first item is a
private async Task <DataTable> ExecuteSql(string scriptName, params Tuple <string, int, string>[] parameters)
{
string sql = await ResourceHelpers.ReadResourceFile(scriptName);
using (SqlConnection connection = await WarehouseConnection.ConnectTo())
{
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.Text;
command.CommandTimeout = 60 * 5;
foreach (Tuple <string, int, string> parameter in parameters)
{
command.Parameters.Add(parameter.Item1, SqlDbType.NVarChar, parameter.Item2).Value = parameter.Item3;
}
var table = new DataTable();
var reader = await command.ExecuteReaderAsync();
table.Load(reader);
return(table);
}
}
protected internal override async Task Execute()
{
// Load default data if not provided
WarehouseConnection = WarehouseConnection ?? Config.Sql.GetConnectionString(KnownSqlConnection.Warehouse);
using (var connection = await WarehouseConnection.ConnectTo())
{
Log.RebuildingIndexes(WarehouseConnection.DataSource, WarehouseConnection.InitialCatalog);
if (!WhatIf)
{
SqlCommand rebuild = connection.CreateCommand();
rebuild.CommandText = "RebuildIndexes";
rebuild.CommandTimeout = CommandTimeout > 0 ? CommandTimeout :
60 * // seconds
60 * // minutes
8; // hours
await Extend(TimeSpan.FromSeconds(rebuild.CommandTimeout));
await rebuild.ExecuteNonQueryAsync();
}
Log.RebuiltIndexes(WarehouseConnection.DataSource, WarehouseConnection.InitialCatalog);
}
}
