/// <summary>
/// Write the People or Publications report
/// </summary>
private void PeopleOrPublicationsReport(WhichReport whichReport, bool ContinuePreviousReport, Reports reports)
{
ArrayList SetnbsToSkip = null;
string Filename = Folder.Text + "\\";
if (whichReport == WhichReport.People)
{
Filename += People.Text;
}
else
{
Filename += Publications.Text;
}
// If we're continuing the previous report, rename it to a backup filename,
// then copy every person in it to the file to append -- except for the
// last person, which we'll restart.
if (ContinuePreviousReport && File.Exists(Filename))
{
try
{
SetnbsToSkip = Reports.BackupReportAndGetSetnbs(Filename);
}
catch (Exception ex)
{
AddLogEntry("An error occurred while creating the report '" + Filename + "': " + ex.Message);
return;
}
}
// Make an anonymous callback function that keeps track of the callback data
Reports.ReportStatus StatusCallback = delegate(int number, int total, Person person, bool StatusBarOnly)
{
toolStripProgressBar1.Minimum = 0;
toolStripProgressBar1.Maximum = total;
toolStripProgressBar1.Value = number;
if (!StatusBarOnly)
{
AddLogEntry("Writing " + person.Last + " " + person.Setnb + " (" + number.ToString() + " of " + total.ToString() + ")");
}
Application.DoEvents();
};
// Make an anonymous callback function to receive message
Reports.ReportMessage MessageCallback = delegate(string Message)
{
AddLogEntry(Message);
Application.DoEvents();
};
// Write the Stars report
StreamWriter writer;
try
{
// Open the writer to append the file only if continuing from a previous report
writer = new StreamWriter(Filename, ContinuePreviousReport);
}
catch (Exception ex)
{
AddLogEntry("An error occurred while creating the report '" + Filename + "': " + ex.Message);
return;
}
try
{
if (whichReport == WhichReport.People)
{
reports.PeopleReport(SetnbsToSkip, writer, StatusCallback, MessageCallback);
}
else
{
reports.PubsReport(SetnbsToSkip, writer, StatusCallback, MessageCallback);
}
}
catch (Exception ex)
{
AddLogEntry("An error occurred while creating the report '" + Filename + "': " + ex.Message);
}
finally
{
writer.Close();
}
}
public System.Collections.IEnumerable getReportItems(WhichReport whichReport, string siteLocationId, string equipment)
{
var query = string.Empty;
switch (whichReport)
{
case WhichReport.InventorySummary:
query = "SELECT DISTINCT ItemType.name as description, (SELECT SUM(count) FROM Item AS I2 Where Item.id=I2.id) AS quantity, VehicleLocation.name AS location, IfNULL((SELECT IT2.name FROM Item AS I2 INNER JOIN ItemType AS IT2 ON I2.itemTypeId=IT2.id WHERE I2.id=Item.parentId), '') as binOrModule, expirationDate FROM (Item INNER JOIN ItemType ON Item.itemTypeId=ItemType.id) LEFT JOIN VehicleLocation ON Item.vehicleLocationId=VehicleLocation.id ORDER BY binOrModule, description;";
break;
case WhichReport.ItemStatus:
query = "SELECT DISTINCT ItemStatus.name as status, ItemType.name as description, " +
"(SELECT SUM(count) FROM Item AS I2 Where Item.id = I2.id) AS quantity, " +
"IfNULL((SELECT IT2.name FROM Item AS I2 INNER JOIN ItemType AS IT2 ON I2.itemTypeId = IT2.id WHERE I2.id = Item.parentId), '') as binOrModule, " +
"ItemInstance.itemNumber " +
"FROM (ItemStatus INNER JOIN(ItemInstance INNER JOIN(Item INNER JOIN ItemType ON Item.itemTypeId = ItemType.id) ON ItemInstance.itemId = Item.id) ON ItemStatus.id = ItemInstance.statusId) " +
$"WHERE ItemInstance.siteLocationId = '{siteLocationId}' AND " +
$"Item.unitTypeName = '{equipment}' ORDER BY binOrModule, status, description;";
break;
case WhichReport.VendorCost:
break;
case WhichReport.Weight:
query =
"SELECT DISTINCT VehicleLocation.name AS location, ItemType.name as col1, NULL as col2, NULL as description, ItemType.weight " +
"FROM (VehicleLocation INNER JOIN(ItemInstance INNER JOIN(Item INNER JOIN ItemType ON Item.itemTypeId = ItemType.id) ON ItemInstance.itemId = Item.id) ON VehicleLocation.id = Item.vehicleLocationId) " +
$"WHERE ItemInstance.siteLocationId = '{siteLocationId}' AND Item.unitTypeName = '{equipment}' AND Item.parentId IS null AND (isBin=1) UNION " +
"SELECT DISTINCT VehicleLocation.name AS location, NULL as col1, ItemType.name as col2, NULL as description, ItemType.weight " +
"FROM (VehicleLocation INNER JOIN(ItemInstance INNER JOIN(Item INNER JOIN ItemType ON Item.itemTypeId = ItemType.id) ON ItemInstance.itemId = Item.id) ON VehicleLocation.id = Item.vehicleLocationId) " +
$"WHERE ItemInstance.siteLocationId = '{siteLocationId}' AND Item.unitTypeName = '{equipment}' AND Item.parentId IS null AND (isModule=1) UNION " +
"SELECT DISTINCT VehicleLocation.name AS location, NULL as col1, NULL as col2, ItemType.name as description, ItemType.weight " +
"FROM (VehicleLocation INNER JOIN(ItemInstance INNER JOIN(Item INNER JOIN ItemType ON Item.itemTypeId = ItemType.id) ON ItemInstance.itemId = Item.id) ON VehicleLocation.id = Item.vehicleLocationId) " +
$"WHERE ItemInstance.siteLocationId = '{siteLocationId}' AND Item.unitTypeName = '{equipment}' AND Item.parentId IS null AND (isBin<>1 AND isModule<>1) " +
"ORDER BY location, description, col2, col1;";
break;
case WhichReport.Expiration:
query = "SELECT DISTINCT ItemType.name as description, (SELECT SUM(count) FROM Item AS I2 Where Item.id=I2.id) AS quantity, VehicleLocation.name AS location, expirationDate FROM (Item INNER JOIN ItemType ON Item.itemTypeId=ItemType.id) LEFT JOIN VehicleLocation ON Item.vehicleLocationId=VehicleLocation.id WHERE expirationDate IS NOT NULL ORDER BY expirationDate, description;";
break;
case WhichReport.Service:
break;
case WhichReport.Deployment:
break;
case WhichReport.DamagedOrMissing:
break;
case WhichReport.MainBoxContents:
default:
//MessageBox.Show("Unsupported report selected, please report.", "Error - unknown report:", MessageBoxButton.OK, MessageBoxImage.Error);
break;
}
if (query == string.Empty)
{
return(Enumerable.Empty <ReportItemsView>());
}
else
{
return(db.QueryAsync <ReportItemSummaryView>(query).Result);
}
}
