private void importDataClicked() { // To enable if I can figure out how to force it into the CSV import wizard //showFileOpen(); //showFileImportText(); db myDb = new db(); if (!myDb.verifyCanConnectToDb()) { return; } if (alreadyInsertedUFLHeader()) { insertStuff(); addWorksheet(); insertDataEntryHeader(); LoadDetailsDone = true; } }
private void createPiTagsNamedRangeForSite(string site) { db myDb = new db(); List <String> piTags = new List <string>(); myDb.getSitePITags(site, piTags); // Insert constants piTags.Insert(0, "Height"); piTags.Insert(0, "JulianDay"); piTags.Insert(0, "Year"); piTags.Insert(0, "Time"); piTags.Insert(0, "Date"); piTags.Insert(0, "DateTime"); piTags.Insert(0, "N/A"); // Insert our named range in Tags worksheet Excel.Worksheet tagWs = (Excel.Worksheet)wb.Sheets["Tags"]; tagWs.Select(missing); insertNamedRange(tagWs, piTags.ToArray(), "PITags", 1); // Switch back to original ws.Select(missing); }
private void saveDetails() { Error frmError = new Error(); UFLDetails ufl = new UFLDetails(); string errMsg = string.Empty; db myDb = new db(); ufl.GetHeaderValues(ws); // should we verify on save? /* * if(!verifyHeaderValues(ufl, ref errMsg)) * { * frmError.errorMsg = errMsg; * frmError.ShowDialog(); * return; * } */ ufl.HeaderMarkerRow = getHeaderMarkerRow(); ufl.HeaderAdditionalDetailRows = getHeaderAdditionalDetailsRows(); ufl.GetPiTags(ws); // Save, save, save ufl.SaveUFL(); // Update Row Id ws.Cells[8, 3] = ufl.UFLDetailId; frmError.ErrorMsg = "Successfully saved details."; frmError.Title = "Saved"; frmError.ShowDialog(); }
private void getLocations(List <string> locations) { db myDb = new db(); myDb.getLocations(locations); }
private void getMetTowers(List <string> metTowers) { db myDb = new db(); myDb.getSites(metTowers); }
//private void getHeaderValues(UFLDetails ufl) //{ // ufl.Delimitor = (String)((Excel.Range)ws.Cells[2, 3]).Text; // ufl.DateTimeFormat = (String)((Excel.Range)ws.Cells[3, 3]).Text; // ufl.DateFormat = (String)((Excel.Range)ws.Cells[4, 3]).Text; // ufl.TimeFormat = (String)((Excel.Range)ws.Cells[5, 3]).Text; // ufl.PiTowerName = (String)((Excel.Range)ws.Cells[7, 3]).Text; // if(((Excel.Range)ws.Cells[8, 3]).Text.ToString()!="") // { // ufl.UFLDetailId = int.Parse(((Excel.Range)ws.Cells[8, 3]).Text.ToString()); // } //} //private void setHeaderValues(UFLDetails ufl) //{ // ws.Cells[2, 3] = ufl.Delimitor; // ws.Cells[3, 3] = ufl.DateTimeFormat; // ws.Cells[4, 3] = ufl.DateFormat; // ws.Cells[5, 3] = ufl.TimeFormat; // ws.Cells[7, 3] = ufl.PiTowerName; // ws.Cells[8, 3] = ufl.UFLDetailId; //} private void generateUFLClicked() { UFLDetails ufl = new UFLDetails(); List <String> iniTemplate = new List <string>(); Utils u = new Utils(); Error frmError = new Error(); string outFilename = string.Empty; db myDb = new db(); // get all the UFL details ufl.GetHeaderValues(ws); if (ufl.UFLDetailId <= 0) { frmError.ErrorMsg = "Please use 'Save Details to Database' before generating the UFL."; frmError.ShowDialog(); return; } if (ufl.DateTimeFormat.IndexOf("mm") != ufl.DateTimeFormat.LastIndexOf("mm")) { frmError.ErrorMsg = String.Format("Warning: It looks like the date format contains 'mm' twice.\n\nMonth should use 'MMM', minutes should us 'mm'."); frmError.Title = "Warning"; frmError.ShowDialog(); } ufl.HeaderMarkerRow = getHeaderMarkerRow(); ufl.HeaderAdditionalDetailRows = getHeaderAdditionalDetailsRows(); ufl.GetPiTags(ws); ufl.GetHeaderRowContents(ws); ufl.GetHeaderAdditionalDetailsRowsContents(ws); ufl.UpdateFileFormatDetails(); DateTime dateval = default(DateTime); int row = ufl.HeaderMarkerRow + 2; if (!ufl.HeaderMarkerRowContents.Contains("JulianDay")) { Excel.Range rng = ws.Cells[row, 2] as Excel.Range; string datevalstring = rng.Value.ToString(); dateval = DateTime.Parse(datevalstring); } else { //assemble date from julian date Excel.Range rng = ws.Cells[row, 4] as Excel.Range; string year = rng.Value.ToString(); rng = ws.Cells[row, 5] as Excel.Range; string doy = rng.Value.ToString(); rng = ws.Cells[row, 6] as Excel.Range; string hourmin = rng.Value.ToString(); dateval = u.JulianToDateTime(year, doy, hourmin); } string sensorError = ufl.CheckMappedSensors(ufl.UFLDetailId, dateval); if (sensorError.Length > 0) { frmError.ErrorMsg = sensorError; frmError.Title = "Save Sensor Mappings"; frmError.Height = 500; frmError.Width = 700; frmError.ShowDialog(); return; } frmError.ErrorMsg = "Successfully saved sensor mappings "; frmError.Title = "Save Sensor Mappings"; frmError.Width = 500; frmError.ShowDialog(); }
private void loadDetails() { Error frmError = new Error(); UFLDetails ufl = new UFLDetails(); UFLDetails oldUfl = new UFLDetails(); string errMsg = string.Empty; db myDb = new db(); Load frmLoad = new Load(); Excel.Range range = null; if (LoadDetailsDone) { frmLoad.ShowDialog(); ufl.UFLDetailId = frmLoad.UFLDetailId; if (ufl.UFLDetailId <= 0) { //frmError.ErrorMsg = "No UFL details could be found.\n\nHave you created a UFL Area and selected a unique id?"; //frmError.ShowDialog(); return; } oldUfl.GetPiTags(ws); // Clear PI Tags row if (oldUfl.PiTagRow > 0) { range = (Excel.Range)ws.get_Range("A" + oldUfl.PiTagRow, "A" + oldUfl.PiTagRow); range.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); } // Clear contents of col 1 range = (Excel.Range)ws.get_Range("A1", "A1"); range.EntireColumn.ClearContents(); //.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); //insertCol(1); //myDb.loadUFL(ufl); ufl.LoadUFL(); ufl.SetHeaderValues(ref ws); setHeaderMarkerRow(ufl); setHeaderAdditionalDetailsRows(ufl); createPiTagsNamedRangeForSite(ufl.PiTowerName); insertPiTagsIntoHeaderRow(ufl.PiTagRow); ufl.SetPiTags(ref ws); frmError.ErrorMsg = String.Format("Successfully loaded details for {0} ({1}).", ufl.PiTowerName, ufl.UFLDetailId); frmError.Title = "Loaded"; frmError.ShowDialog(); } else { // Please select 'Insert PI Tag Header' first frmError.ErrorMsg = "Please use the menu option 'Add UFL Areas' first."; frmError.ShowDialog(); } }
public void UpdateFileFormatDetails() { int affected = 0; db myDb = new db(); SqlParameter fileFormatIdParam = new SqlParameter("@FileFormatId", SqlDbType.Int); fileFormatIdParam.Value = UFLDetailId; // Use the same id for FileFormatId as the UFLDetailId SqlParameter delimiterParam = new SqlParameter("@Delimitor", SqlDbType.VarChar, 5); delimiterParam.Value = Delimiter; SqlParameter headerRowNumberParam = new SqlParameter("@HeaderRowNumber", SqlDbType.Int); headerRowNumberParam.Value = HeaderMarkerRow - UFL_DETAIL_HEADER_SIZE; // We use 15 extra lines for our UFL Details header SqlParameter headerRowContentsParam = new SqlParameter("@HeaderRowContents", SqlDbType.VarChar, 1000); headerRowContentsParam.Value = String.Join(DelimiterCode, HeaderMarkerRowContents.ToArray()); SqlParameter piTowerNameParam = new SqlParameter("@PiTowerName", SqlDbType.VarChar, 50); piTowerNameParam.Value = PiTowerName; SqlParameter folderParam = new SqlParameter("@Folder", SqlDbType.VarChar, 1000); folderParam.Value = Folder; SqlParameter fileMaskParam = new SqlParameter("@FileMask", SqlDbType.VarChar, 100); fileMaskParam.Value = FileMask; SqlParameter recurseParam = new SqlParameter("@Recurse", SqlDbType.Bit); recurseParam.Value = Recursive; SqlParameter encryptedParam = new SqlParameter("@Encrypted", SqlDbType.Bit); encryptedParam.Value = Encrypted; myDb.Connect(); SqlCommand command = new SqlCommand("SpExcelUpdateFileFormatDetails", myDb.Connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(fileFormatIdParam); command.Parameters.Add(delimiterParam); command.Parameters.Add(headerRowNumberParam); command.Parameters.Add(headerRowContentsParam); command.Parameters.Add(piTowerNameParam); command.Parameters.Add(folderParam); command.Parameters.Add(fileMaskParam); command.Parameters.Add(recurseParam); command.Parameters.Add(encryptedParam); try { affected = command.ExecuteNonQuery(); } catch (Exception e) { string dummy = e.Message; Error frmError = new Error(); frmError.ErrorMsg = String.Format("An error occoured: {0}", e.Message); frmError.ShowDialog(); } // now store additional details content SqlParameter fileFormatIdMLParam = new SqlParameter("@FileFormatId", SqlDbType.Int); SqlParameter headerAdditioanlDetailRowNumberParam = new SqlParameter("@HeaderRowNumber", SqlDbType.Int); SqlParameter headerAdditioanlDetailRowContentsParam = new SqlParameter("@HeaderRowContents", SqlDbType.VarChar, 1000); SqlParameter deleteExistingParam = new SqlParameter("@DeleteExisting", SqlDbType.Bit); SqlCommand commandML = new SqlCommand("SpExcelUpdateMultiLineHeaderRow", myDb.Connection); commandML.CommandType = CommandType.StoredProcedure; fileFormatIdMLParam.Value = UFLDetailId; commandML.Parameters.Add(fileFormatIdMLParam); commandML.Parameters.Add(headerAdditioanlDetailRowNumberParam); commandML.Parameters.Add(headerAdditioanlDetailRowContentsParam); commandML.Parameters.Add(deleteExistingParam); for (int i = 0; i < HeaderAdditionalDetailRows.Count; i++) { // insert headerAdditioanlDetailRowNumberParam.Value = int.Parse(HeaderAdditionalDetailRows[i]) - UFL_DETAIL_HEADER_SIZE; // We use 15 extra lines for our UFL Details header headerAdditioanlDetailRowContentsParam.Value = HeaderAdditionalDetailRowsContents[i]; deleteExistingParam.Value = (i == 0); // Only delete existing records when we insert the first line try { affected = commandML.ExecuteNonQuery(); } catch (Exception e) { string dummy = e.Message; Error frmError = new Error(); frmError.ErrorMsg = String.Format("An error occoured: {0}", e.Message); frmError.ShowDialog(); } } }
public void LoadUFL() { db myDb = new db(); SqlDataReader reader = null; SqlParameter uflDetailIdParam = new SqlParameter("@UFLDetailId", SqlDbType.Int); uflDetailIdParam.Value = UFLDetailId; myDb.Connect(); SqlCommand command = new SqlCommand("SpExcelLoadUFLDetail", myDb.Connection); command.CommandType = CommandType.StoredProcedure; try { command.Parameters.Add(uflDetailIdParam); reader = command.ExecuteReader(); } catch (Exception e) { string dummy = e.Message; } while (reader.Read()) { if (reader["UFLDetailId"].ToString() != "") { UFLDetailId = int.Parse(reader["UFLDetailId"].ToString()); } PiTowerName = reader["PiTowerName"].ToString(); FileMask = reader["FileMask"].ToString(); Folder = reader["Folder"].ToString(); if (reader["Recursive"].ToString() != String.Empty) { Recursive = (bool)reader["Recursive"]; } else { Recursive = false; } if (reader["Encrypted"].ToString() != String.Empty) { Encrypted = (bool)reader["Encrypted"]; } else { Encrypted = false; } DataType = reader["DataType"].ToString(); Delimiter = reader["Delimitor"].ToString(); DateTimeFormat = reader["DateTimeFormat"].ToString(); DateFormat = reader["DateFormat"].ToString(); TimeFormat = reader["TimeFormat"].ToString(); if (reader["HeaderMarkerRow"].ToString() != "") { HeaderMarkerRow = int.Parse(reader["HeaderMarkerRow"].ToString()); } HeaderAdditionalDetailRows.Clear(); HeaderAdditionalDetailRows.AddRange((reader["HeaderAdditionalDetail"].ToString()).Split('|')); if (reader["PiTagRow"].ToString() != "") { PiTagRow = int.Parse(reader["PiTagRow"].ToString()); } PiTagList.Clear(); PiTagList.AddRange((reader["PiTagList"].ToString()).Split('|')); } reader.Close(); myDb.Connection.Close(); }
public void SaveUFL() { SqlDataReader reader = null; SqlParameter uflDetailIdParam = new SqlParameter("@UFLDetailId", SqlDbType.Int); uflDetailIdParam.Value = UFLDetailId; SqlParameter piTowerNameParam = new SqlParameter("@PiTowerName", SqlDbType.VarChar, 50); piTowerNameParam.Value = PiTowerName; SqlParameter fileMaskParam = new SqlParameter("@FileMask", SqlDbType.VarChar, 50); fileMaskParam.Value = FileMask; SqlParameter folderParam = new SqlParameter("@Folder", SqlDbType.VarChar, 255); folderParam.Value = Folder; SqlParameter recursiveParam = new SqlParameter("@Recursive", SqlDbType.Bit); recursiveParam.Value = Recursive; SqlParameter encryptedParam = new SqlParameter("@Encrypted", SqlDbType.Bit); encryptedParam.Value = Encrypted; SqlParameter dataTypeParam = new SqlParameter("@DataType", SqlDbType.Char, 3); dataTypeParam.Value = DataType; SqlParameter delimiterParam = new SqlParameter("@Delimitor", SqlDbType.VarChar, 10); delimiterParam.Value = Delimiter; SqlParameter dateTimeFormatParam = new SqlParameter("@DateTimeFormat", SqlDbType.VarChar, 30); dateTimeFormatParam.Value = DateTimeFormat; SqlParameter dateFormatParam = new SqlParameter("@DateFormat", SqlDbType.VarChar, 20); dateFormatParam.Value = DateFormat; SqlParameter timeFormatParam = new SqlParameter("@TimeFormat", SqlDbType.VarChar, 20); timeFormatParam.Value = TimeFormat; SqlParameter headerMarkerRowParam = new SqlParameter("@HeaderMarkerRow", SqlDbType.Int); headerMarkerRowParam.Value = HeaderMarkerRow; SqlParameter headerAdditionalDetailParam = new SqlParameter("@HeaderAdditionalDetail", SqlDbType.VarChar, 2000); headerAdditionalDetailParam.Value = String.Join("|", HeaderAdditionalDetailRows.ToArray()); SqlParameter piTagRowParam = new SqlParameter("@PiTagRow", SqlDbType.Int); piTagRowParam.Value = PiTagRow; SqlParameter piTagListParam = new SqlParameter("@PiTagList", SqlDbType.VarChar, 2000); piTagListParam.Value = String.Join("|", PiTagList.ToArray()); db myDb = new db(); myDb.Connect(); SqlCommand command = new SqlCommand("SpExcelSaveUFLDetail", myDb.Connection); command.CommandType = CommandType.StoredProcedure; try { command.Parameters.Add(uflDetailIdParam); command.Parameters.Add(piTowerNameParam); command.Parameters.Add(fileMaskParam); command.Parameters.Add(folderParam); command.Parameters.Add(recursiveParam); command.Parameters.Add(encryptedParam); command.Parameters.Add(dataTypeParam); command.Parameters.Add(delimiterParam); command.Parameters.Add(dateTimeFormatParam); command.Parameters.Add(dateFormatParam); command.Parameters.Add(timeFormatParam); command.Parameters.Add(headerMarkerRowParam); command.Parameters.Add(headerAdditionalDetailParam); command.Parameters.Add(piTagRowParam); command.Parameters.Add(piTagListParam); reader = command.ExecuteReader(); } catch (Exception e) { string dummy = e.Message; } while (reader.Read()) { if (reader["UFLDetailId"].ToString() != "") { UFLDetailId = int.Parse(reader["UFLDetailId"].ToString()); } } reader.Close(); myDb.Connection.Close(); }