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