private void btnImportExcel_Click(object sender, RoutedEventArgs e) { Excel.Application xlDropOrder; Excel.Workbook xlDropBook; Excel.Worksheet xlDropSheet; Excel.Range range; int intColumnRange = 0; int intCounter; int intNumberOfRecords; int intRecordsReturned; int intAssetID; string strAssetDescription; string strLocation; string strSerialNumber; string strAssetType; string strBJCNumber; bool blnFatalError = false; string strErrorMessage = ""; bool blnItemFound; int intTransactionID; string strModel; string strManufacturer; try { TheImportWaspToolAssetsDataSet.importassets.Rows.Clear(); Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog(); dlg.FileName = "Document"; // Default file name dlg.DefaultExt = ".xlsx"; // Default file extension dlg.Filter = "Excel (.xlsx)|*.xlsx"; // Filter files by extension // Show open file dialog box Nullable <bool> result = dlg.ShowDialog(); // Process open file dialog box results if (result == true) { // Open document string filename = dlg.FileName; } PleaseWait PleaseWait = new PleaseWait(); PleaseWait.Show(); strLocation = txtEnterLocation.Text; if (strLocation.Length < 2) { blnFatalError = true; strErrorMessage += "The Location is not long Enough\n"; } else { TheFindWaspAssetLocationByLocationDataSet = TheAssetClass.FindWaspAssetLocationByLocation(strLocation); intRecordsReturned = TheFindWaspAssetLocationByLocationDataSet.FindWaspAssetLocationByLocation.Rows.Count; if (intRecordsReturned < 1) { blnFatalError = true; strErrorMessage += "There is no Such Location\n"; } } if (cboSelectLocation.SelectedIndex < 1) { blnFatalError = true; strErrorMessage += "The Site Has Not Been Selected\n"; } if (blnFatalError == true) { TheMessagesClass.ErrorMessage(strErrorMessage); return; } xlDropOrder = new Excel.Application(); xlDropBook = xlDropOrder.Workbooks.Open(dlg.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlDropSheet = (Excel.Worksheet)xlDropOrder.Worksheets.get_Item(1); range = xlDropSheet.UsedRange; intNumberOfRecords = range.Rows.Count; intColumnRange = range.Columns.Count; for (intCounter = 1; intCounter <= intNumberOfRecords; intCounter++) { TheWaspAssetIDDataSet = TheAssetClass.GetWaspAssetIDInfo(); intAssetID = TheWaspAssetIDDataSet.waspassetid[0].CreatedAssetID; intTransactionID = TheWaspAssetIDDataSet.waspassetid[0].TransactionID; blnFatalError = TheAssetClass.UpdateWaspAssetID(intTransactionID, intAssetID + 1); blnItemFound = false; strAssetType = "UNKNOWN"; if (((range.Cells[intCounter, 2] as Excel.Range).Value2) == null) { intCounter = intNumberOfRecords; break; } else { strAssetDescription = Convert.ToString((range.Cells[intCounter, 2] as Excel.Range).Value2).ToUpper(); if (((range.Cells[intCounter, 3] as Excel.Range).Value2) == null) { strSerialNumber = " "; } else { strSerialNumber = Convert.ToString((range.Cells[intCounter, 3] as Excel.Range).Value2).ToUpper(); } if (((range.Cells[intCounter, 4] as Excel.Range).Value2) == null) { strBJCNumber = " "; } else { strBJCNumber = Convert.ToString((range.Cells[intCounter, 4] as Excel.Range).Value2).ToUpper(); } if (strSerialNumber.Length > 2) { TheFindWaspAssetBySerialNumberDataSet = TheAssetClass.FindWaspAssetsBySerialNumber(strSerialNumber); intRecordsReturned = TheFindWaspAssetBySerialNumberDataSet.FindWaspAssetsBySerialNumber.Rows.Count; if (intRecordsReturned > 0) { blnItemFound = true; } } if (strBJCNumber.Length > 2) { TheFindWaspAssetByBJCAssetIDDataSet = TheAssetClass.FindWaspAssetByBJCAssetID(strBJCNumber); intRecordsReturned = TheFindWaspAssetByBJCAssetIDDataSet.FindWaspAssetByBJCAssetID.Rows.Count; if (intRecordsReturned > 0) { blnItemFound = true; } } } if (blnItemFound == false) { if (strBJCNumber.Length > 2) { TheFindActiveToolByToolIDDataSet = TheToolClass.FindActiveToolByToolID(strBJCNumber); intRecordsReturned = TheFindActiveToolByToolIDDataSet.FindActiveToolByToolID.Rows.Count; if (intRecordsReturned > 0) { strAssetType = TheFindActiveToolByToolIDDataSet.FindActiveToolByToolID[0].ToolCategory; strAssetDescription = TheFindActiveToolByToolIDDataSet.FindActiveToolByToolID[0].ToolDescription; } } ImportWaspToolAssetsDataSet.importassetsRow NewAssetRow = TheImportWaspToolAssetsDataSet.importassets.NewimportassetsRow(); NewAssetRow.AssetDescription = strAssetDescription; NewAssetRow.AssetID = intAssetID; NewAssetRow.AssetType = strAssetType; NewAssetRow.Location = strLocation; NewAssetRow.Manufacturer = "UNKNOWN"; NewAssetRow.Model = "UNKNOWN"; NewAssetRow.SerialNumber = strSerialNumber; NewAssetRow.Site = gstrSite; NewAssetRow.WarehouseID = MainWindow.gintWarehouseID; NewAssetRow.BJCAssetID = strBJCNumber; TheImportWaspToolAssetsDataSet.importassets.Rows.Add(NewAssetRow); } } intNumberOfRecords = TheImportWaspToolAssetsDataSet.importassets.Rows.Count; if (intNumberOfRecords > 0) { for (intCounter = 0; intCounter < intNumberOfRecords; intCounter++) { strAssetDescription = TheImportWaspToolAssetsDataSet.importassets[intCounter].AssetDescription; strAssetType = TheImportWaspToolAssetsDataSet.importassets[intCounter].AssetType; intAssetID = TheImportWaspToolAssetsDataSet.importassets[intCounter].AssetID; strBJCNumber = TheImportWaspToolAssetsDataSet.importassets[intCounter].BJCAssetID; strLocation = TheImportWaspToolAssetsDataSet.importassets[intCounter].Location; strManufacturer = TheImportWaspToolAssetsDataSet.importassets[intCounter].Manufacturer; strModel = TheImportWaspToolAssetsDataSet.importassets[intCounter].Model; strSerialNumber = TheImportWaspToolAssetsDataSet.importassets[intCounter].SerialNumber; blnFatalError = TheAssetClass.InsertWaspAssets(intAssetID, strAssetDescription, strBJCNumber, strAssetType, gstrSite, strLocation, MainWindow.gintWarehouseID, DateTime.Now, strSerialNumber, strManufacturer, strModel); if (blnFatalError == true) { throw new Exception(); } } } PleaseWait.Close(); dgrAssets.ItemsSource = TheImportWaspToolAssetsDataSet.importassets; } catch (Exception Ex) { TheEventLogClass.InsertEventLogEntry(DateTime.Now, "New Blue Jay ERP // Import Vehicle Assets // Import Excel " + Ex.Message); TheMessagesClass.ErrorMessage(Ex.ToString()); } }
private void cboSelectLocation_SelectionChanged(object sender, SelectionChangedEventArgs e) { Excel.Application xlDropOrder; Excel.Workbook xlDropBook; Excel.Worksheet xlDropSheet; Excel.Range range; int intColumnRange = 0; int intCounter; int intNumberOfRecords; int intRecordsReturned; int intAssetID; string strAssetDescription; string strSerialNumber; string strAssetType; string strBJCNumber; bool blnFatalError = false; string strErrorMessage = ""; bool blnItemFound; int intTransactionID; string strModel; string strManufacturer; int intSelectedIndex; try { PleaseWait PleaseWait = new PleaseWait(); PleaseWait.Show(); intSelectedIndex = cboSelectLocation.SelectedIndex - 1; if (intSelectedIndex > -1) { TheMonitorsDataSet.monitors.Rows.Clear(); Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog(); dlg.FileName = "Document"; // Default file name dlg.DefaultExt = ".xlsx"; // Default file extension dlg.Filter = "Excel (.xlsx)|*.xlsx"; // Filter files by extension // Show open file dialog box Nullable <bool> result = dlg.ShowDialog(); // Process open file dialog box results if (result == true) { // Open document string filename = dlg.FileName; } MainWindow.gstrAssetLocation = TheFindSortedWaspAssetLocationsBySiteDataSet.FindSortedWaspAssetLoctionsBySite[intSelectedIndex].AssetLocation; if (cboSelectLocation.SelectedIndex < 1) { blnFatalError = true; strErrorMessage += "The Site Has Not Been Selected\n"; } if (blnFatalError == true) { TheMessagesClass.ErrorMessage(strErrorMessage); return; } xlDropOrder = new Excel.Application(); xlDropBook = xlDropOrder.Workbooks.Open(dlg.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlDropSheet = (Excel.Worksheet)xlDropOrder.Worksheets.get_Item(1); range = xlDropSheet.UsedRange; intNumberOfRecords = range.Rows.Count; intColumnRange = range.Columns.Count; for (intCounter = 2; intCounter <= intNumberOfRecords; intCounter++) { TheWaspAssetIDDataSet = TheAssetClass.GetWaspAssetIDInfo(); intAssetID = TheWaspAssetIDDataSet.waspassetid[0].CreatedAssetID; intTransactionID = TheWaspAssetIDDataSet.waspassetid[0].TransactionID; blnFatalError = TheAssetClass.UpdateWaspAssetID(intTransactionID, intAssetID + 1); blnItemFound = false; strAssetType = "UNKNOWN"; if (((range.Cells[intCounter, 1] as Excel.Range).Value2) == null) { intCounter = intNumberOfRecords; break; } else { strAssetDescription = Convert.ToString((range.Cells[intCounter, 2] as Excel.Range).Value2).ToUpper(); strManufacturer = Convert.ToString((range.Cells[intCounter, 3] as Excel.Range).Value2).ToUpper(); strModel = Convert.ToString((range.Cells[intCounter, 4] as Excel.Range).Value2).ToUpper(); strSerialNumber = Convert.ToString((range.Cells[intCounter, 5] as Excel.Range).Value2).ToUpper(); strBJCNumber = Convert.ToString((range.Cells[intCounter, 6] as Excel.Range).Value2).ToUpper(); strAssetType = Convert.ToString((range.Cells[intCounter, 7] as Excel.Range).Value2).ToUpper(); TheFindWaspAssetByBJCAssetIDDataSet = TheAssetClass.FindWaspAssetByBJCAssetID(strBJCNumber); intRecordsReturned = TheFindWaspAssetByBJCAssetIDDataSet.FindWaspAssetByBJCAssetID.Rows.Count; if (intRecordsReturned > 0) { blnItemFound = true; } } if (blnItemFound == false) { MonitorsDataSet.monitorsRow NewMonitorRow = TheMonitorsDataSet.monitors.NewmonitorsRow(); NewMonitorRow.AssetID = intAssetID; NewMonitorRow.AssetType = strAssetType; NewMonitorRow.BJCAssetID = strBJCNumber; NewMonitorRow.Item = strAssetDescription; NewMonitorRow.Location = MainWindow.gstrAssetLocation; NewMonitorRow.Manufacturer = strManufacturer; NewMonitorRow.Model = strModel; NewMonitorRow.SerialNumber = strSerialNumber; NewMonitorRow.Site = gstrSite; NewMonitorRow.WarehouseID = MainWindow.gintWarehouseID; TheMonitorsDataSet.monitors.Rows.Add(NewMonitorRow); } } } PleaseWait.Close(); dgrAssets.ItemsSource = TheMonitorsDataSet.monitors; } catch (Exception Ex) { TheEventLogClass.InsertEventLogEntry(DateTime.Now, "New Blue Jay ERP // Import Vehicle Assets // Import Excel " + Ex.Message); TheMessagesClass.ErrorMessage(Ex.ToString()); } }
private void Window_Loaded(object sender, RoutedEventArgs e) { //setting up local variables DateTime datTransactionDate = DateTime.Now; int intNumberOfRecords; int intCounter; int intTransactionID; int intAssetID; bool blnFatalError = false; string strSite; try { TheWaspAssetIDDataSet = TheAssetClass.GetWaspAssetIDInfo(); chkIsTool.IsChecked = false; intAssetID = TheWaspAssetIDDataSet.waspassetid[0].CreatedAssetID; intTransactionID = TheWaspAssetIDDataSet.waspassetid[0].TransactionID; MainWindow.gintAssetID = intAssetID; intAssetID++; txtAssetID.Text = Convert.ToString(MainWindow.gintAssetID); txtDate.Text = Convert.ToString(datTransactionDate); blnFatalError = TheAssetClass.UpdateWaspAssetID(intTransactionID, intAssetID); txtLocation.Text = MainWindow.gstrAssetLocation; TheFindWaspAssetLocationByLoctionDataSet = TheAssetClass.FindWaspAssetLocationByLocation(MainWindow.gstrAssetLocation); strSite = TheFindWaspAssetLocationByLoctionDataSet.FindWaspAssetLocationByLocation[0].AssetSite; txtSite.Text = strSite; if (strSite == "GROVEPORT") { strSite = "CBUS-GROVEPORT"; } TheFindWarehouseByWarehouseNameDataSet = TheEmployeeClass.FindWarehouseByWarehouseName(strSite); MainWindow.gintWarehouseID = TheFindWarehouseByWarehouseNameDataSet.FindWarehouseByWarehouseName[0].EmployeeID; txtWarehouseID.Text = Convert.ToString(MainWindow.gintWarehouseID); TheFindSortedToolCategoryDataSet = TheToolCategoryClass.FindSortedToolCategory(); cboAssetCategory.Items.Add("Select Category"); intNumberOfRecords = TheFindSortedToolCategoryDataSet.FindSortedToolCategory.Rows.Count; for (intCounter = 0; intCounter < intNumberOfRecords; intCounter++) { cboAssetCategory.Items.Add(TheFindSortedToolCategoryDataSet.FindSortedToolCategory[intCounter].ToolCategory); } cboAssetCategory.SelectedIndex = 0; txtSerialNo.Text = "UNKNOWN"; txtModel.Text = "UNKNOWN"; txtManufacturer.Text = "UNKNOWN"; } catch (Exception Ex) { TheSendEmailClass.SendEventLog("New Blue Jay ERP // Add Wasp Asset // Window Loaded Method " + Ex.Message); TheEventLogClass.InsertEventLogEntry(DateTime.Now, "New Blue Jay ERP // Add Wasp Asset // Window Loaded Method " + Ex.Message); TheMessagesClass.ErrorMessage(Ex.ToString()); } }
private void cboSelectLocation_SelectionChanged(object sender, SelectionChangedEventArgs e) { Excel.Application xlDropOrder; Excel.Workbook xlDropBook; Excel.Worksheet xlDropSheet; Excel.Range range; int intColumnRange = 0; int intCounter; int intNumberOfRecords; int intRecordsReturned; int intAssetID; string strAssetDescription; string strSerialNumber; string strAssetType; string strBJCNumber; string strFirstName; string strLastName; bool blnFatalError = false; bool blnItemFound; int intTransactionID; int intSelectedIndex; int intEmployeeID; try { intSelectedIndex = cboSelectLocation.SelectedIndex - 1; if (intSelectedIndex > -1) { TheEmployeeToolAssetDataSet.employeetoolassets.Rows.Clear(); Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog(); dlg.FileName = "Document"; // Default file name dlg.DefaultExt = ".xlsx"; // Default file extension dlg.Filter = "Excel (.xlsx)|*.xlsx"; // Filter files by extension // Show open file dialog box Nullable <bool> result = dlg.ShowDialog(); // Process open file dialog box results if (result == true) { // Open document string filename = dlg.FileName; } PleaseWait PleaseWait = new PleaseWait(); PleaseWait.Show(); gstrLocation = TheFindSortedWaspAssetLocationsBySiteDataSet.FindSortedWaspAssetLoctionsBySite[intSelectedIndex].AssetLocation; xlDropOrder = new Excel.Application(); xlDropBook = xlDropOrder.Workbooks.Open(dlg.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlDropSheet = (Excel.Worksheet)xlDropOrder.Worksheets.get_Item(1); range = xlDropSheet.UsedRange; intNumberOfRecords = range.Rows.Count; intColumnRange = range.Columns.Count; for (intCounter = 2; intCounter <= intNumberOfRecords; intCounter++) { TheWaspAssetIDDataSet = TheAssetClass.GetWaspAssetIDInfo(); intAssetID = TheWaspAssetIDDataSet.waspassetid[0].CreatedAssetID; intTransactionID = TheWaspAssetIDDataSet.waspassetid[0].TransactionID; blnFatalError = TheAssetClass.UpdateWaspAssetID(intTransactionID, intAssetID + 1); blnItemFound = false; strAssetType = "UNKNOWN"; if (((range.Cells[intCounter, 1] as Excel.Range).Value2) == null) { intCounter = intNumberOfRecords; break; } else { strAssetDescription = Convert.ToString((range.Cells[intCounter, 3] as Excel.Range).Value2).ToUpper(); if (((range.Cells[intCounter, 2] as Excel.Range).Value2) == null) { strBJCNumber = " "; } else { strBJCNumber = Convert.ToString((range.Cells[intCounter, 2] as Excel.Range).Value2).ToUpper(); } if (((range.Cells[intCounter, 5] as Excel.Range).Value2) == null) { strSerialNumber = " "; } else { strSerialNumber = Convert.ToString((range.Cells[intCounter, 5] as Excel.Range).Value2).ToUpper(); } if (((range.Cells[intCounter, 4] as Excel.Range).Value2) == null) { strAssetType = " "; } else { strAssetType = Convert.ToString((range.Cells[intCounter, 4] as Excel.Range).Value2).ToUpper(); } strFirstName = Convert.ToString((range.Cells[intCounter, 8] as Excel.Range).Value2).ToUpper(); strLastName = Convert.ToString((range.Cells[intCounter, 9] as Excel.Range).Value2).ToUpper(); if (strSerialNumber.Length > 2) { TheFindWaspAssetBySerialNumberDataSet = TheAssetClass.FindWaspAssetsBySerialNumber(strSerialNumber); intRecordsReturned = TheFindWaspAssetBySerialNumberDataSet.FindWaspAssetsBySerialNumber.Rows.Count; if (intRecordsReturned > 0) { blnItemFound = true; } } if (strBJCNumber.Length > 2) { TheFindWaspAssetByBJCAssetIDDataSet = TheAssetClass.FindWaspAssetByBJCAssetID(strBJCNumber); intRecordsReturned = TheFindWaspAssetByBJCAssetIDDataSet.FindWaspAssetByBJCAssetID.Rows.Count; if (intRecordsReturned > 0) { blnItemFound = true; } } } if (blnItemFound == false) { if (strBJCNumber.Length > 2) { TheFindActiveToolByToolIDDataSet = TheToolClass.FindActiveToolByToolID(strBJCNumber); intRecordsReturned = TheFindActiveToolByToolIDDataSet.FindActiveToolByToolID.Rows.Count; if (intRecordsReturned > 0) { strAssetType = TheFindActiveToolByToolIDDataSet.FindActiveToolByToolID[0].ToolCategory; strAssetDescription = TheFindActiveToolByToolIDDataSet.FindActiveToolByToolID[0].ToolDescription; } } } TheComboEmployeeDataSet = TheEmployeeClass.FillEmployeeComboBox(strLastName); EmployeeToolAssetDataSet.employeetoolassetsRow NewToolRow = TheEmployeeToolAssetDataSet.employeetoolassets.NewemployeetoolassetsRow(); NewToolRow.AssetID = intAssetID; NewToolRow.BJCAssetID = strBJCNumber; NewToolRow.EmployeeID = TheComboEmployeeDataSet.employees[0].EmployeeID; NewToolRow.FirstName = strFirstName; NewToolRow.LastName = strLastName; NewToolRow.Office = gstrSite; NewToolRow.ToolLocation = gstrLocation; NewToolRow.ToolCategory = strAssetType; NewToolRow.ToolDescription = strAssetDescription; TheEmployeeToolAssetDataSet.employeetoolassets.Rows.Add(NewToolRow); } xlDropOrder.Quit(); PleaseWait.Close(); } dgrAssets.ItemsSource = TheEmployeeToolAssetDataSet.employeetoolassets; } catch (Exception Ex) { TheEventLogClass.InsertEventLogEntry(DateTime.Now, "New Blue Jay ERP // Import Vehicle Assets // Import Excel " + Ex.Message); TheMessagesClass.ErrorMessage(Ex.ToString()); } }