/// <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); } }