private void GetData_Load(object sender, EventArgs e)
{
db myDb = new db();
Cursor.Current = Cursors.WaitCursor;
object[] sites = myDb.getSites();
Cursor.Current = Cursors.Default;
if (sites != null)
{
lbxSites.Items.AddRange(sites);
}
// Test mode
dtpFrom.Text = "1 July 2006 10:00";
dtpTo.Text = "31 December 2006 11:00";
//if (lbxSites.Items.Count >= 8)
//{
// lbxSites.SelectedIndex = 8;
//}
lbxSites.SelectedItem = "BPMT.US.OR.00007";
}
private void btnOK_Click(object sender, EventArgs e)
{
db myDb = new db();
string site = lbxSites.SelectedItem.ToString();
DateTime dtFrom = dtpFrom.Value;
DateTime dtTo = dtpTo.Value;
this.Hide();
Cursor.Current = Cursors.WaitCursor;
Error frmError = new Error();
frmError.Title = "Progress";
frmError.ErrorMsg = "Downloading data from PI Sever";
frmError.btnOkVisible = false;
frmError.Show();
Application.DoEvents();
// ADODB Test Idea
//ADODB.Connection adoCn = new ADODB.Connection();
//adoCn.Provider = "PIOLEDB";
//myDb.OLEConnect();
//DataSet objDataSet = new DataSet();
//OleDbDataAdapter objAdapter = new OleDbDataAdapter(@"SELECT tag, [time], [value] FROM piarchive..picomp2 where tag = 'BPMT.US.OR.00007.DCL.29.WD.341.Avg' and time = '2006-07-01 10:00'", myDb.OLEConnection);
//objAdapter.Fill(objDataSet, "PIData");
//DataView objDataView = new DataView(objDataSet.Tables["PIData"]);
// End Test Idea
List <PITagData> data = myDb.GetDataForSite(site, dtFrom, dtTo, false, true);
DateTime startTime = DateTime.Now;
//int row;
int col = 1;
int i = 1;
string lastTag = String.Empty;
List <String> cellRange = new List <String>();
List <DateTime> dateTimeRange = new List <DateTime>();
Excel.Range dataRange = (Excel.Range)_ws.Cells[1, 1];
frmError.Close();
frmError = new Error();
frmError.Title = "Progress";
frmError.ErrorMsg = "Populating spreadsheet";
frmError.btnOkVisible = false;
frmError.progressBarVisible = true;
frmError.progressBarMaximum = data.Count;
frmError.Show();
Application.DoEvents();
foreach (PITagData d in data)
{
// update progress bar every 100 items
// this is to prevent too much slowdown
i++;
if (i % 100 == 0)
{
frmError.progressBarValue = i;
Application.DoEvents();
}
if (d.Tag != lastTag)
{
// If this is the first tag, no need to flush cos
if (lastTag != String.Empty)
{
// We are on a new tag - flush the buffer to the spreadsheet
Object[,] excelArrayData = new Object[cellRange.Count, 1];
if (col == 2)
{
// Better put the date/times in
int dateLoop = 0;
foreach (DateTime item in dateTimeRange)
{
excelArrayData[dateLoop++, 0] = item;
}
dataRange = (Excel.Range)_ws.Cells[2, 1]; // First position of datetime data
dataRange = dataRange.get_Resize(cellRange.Count, 1);
dataRange.Value2 = excelArrayData;
dataRange.NumberFormat = "yyyy-MM-dd hh:mm";
}
int loop = 0;
foreach (Object item in cellRange)
{
excelArrayData[loop++, 0] = item;
}
dataRange = (Excel.Range)_ws.Cells[1, col];
dataRange = dataRange.get_Resize(cellRange.Count, 1);
dataRange.Value2 = excelArrayData;
// Make sure it doesn't try to auto format the cells
dataRange.NumberFormat = "@";
cellRange.Clear();
excelArrayData = null; // Hopefully save some memory by forcing garbage collection
}
// reset
//row = 1;
col++;
cellRange.Add(d.Tag);
}
// Only store time in when we are in col 2, i.e. the first column of data
if (col == 2)
{
dateTimeRange.Add(d.DateTime);
}
cellRange.Add(d.Value);
lastTag = d.Tag;
}
// Put marker in cell [1, 1] to indicate it is a grid of data
_ws.Cells[1, 1] = "PI Data Grid";
// Name the worksheet, so we can reload the data
try
{
_ws.Name = site;
}
catch
{
MessageBox.Show(String.Format("Could not name the spreadsheet {0}. This will need to be done manually if the data is to be uploaded", site));
}
// set column A to a set width so we can see the datatime stamps properly, rather than #####
Excel.Range range = (Excel.Range)_ws.Cells[1, 1];
range.ColumnWidth = 20;
Cursor.Current = Cursors.Default;
frmError.Close();
TimeSpan duration = DateTime.Now - startTime;
//MessageBox.Show(string.Format("Populate spreadsheet: {0} minutes {1} seconds", duration.Minutes, duration.Seconds));
this.Close();
}
private void updatePIAndIncrCount(string piDateTime, string piTag, string piVal, ref int updateCount, ref int failCount, db myDb)
{
if (piTag != string.Empty && piDateTime != string.Empty && piVal != string.Empty)
{
if (myDb.updateData(piTag, piDateTime, piVal, true))
{
updateCount++;
}
else
{
failCount++;
}
}
}
private void updatePIData()
{
Excel.Range range = null;
string piRef = string.Empty;
string piDateTime = string.Empty;
string piTag = string.Empty;
string piInstrument = string.Empty;
int piTagStart = 0;
int piTagEnd = 0;
string piVal = string.Empty;
int row = 1;
int updateCount = 0;
int failCount = 0;
bool dataUnchanged = false;
long appCalc = (long)Application.Calculation;
Application.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual;
Application.ScreenUpdating = false;
Application.EnableEvents = false;
Application.DisplayAlerts = false;
DateTime startTime = DateTime.Now;
db myDb = new db();
ws = (Excel.Worksheet) this.Application.ActiveSheet;
// Get initial cell
range = (Excel.Range)ws.Cells[row, 1];
piRef = (string)range.Formula;
myDb.OLEConnect();
myDb.SetFlushOff();
// Is it a PI Data Grid?
if (range.Text.ToString() == "PI Data Grid")
{
StringBuilder megaSQL = new StringBuilder();
//bool blockUpdateSuccess = false;
// get ws name, this is the PI towername
string piTower = ws.Name;
// check it looks like a PI tower
if ((((Array)piTower.Split('.')).Length <= 3) || (piTower.Contains(" ")))
{
MessageBox.Show(String.Format("Worksheet name '{0}' does not look like a valid PI tower name", piTower));
return;
}
// grid data starts at [2, 2]
row = 2;
int col = 2;
// Get PI tag for this column
//piTag = String.Format("{0}.{1}", piTower, cellToString(1, col));
// Count the number of rows in the datetime column
int numberOfRows = 2;
while (cellToString(numberOfRows++, 1) != String.Empty)
{
}
// Back 1 from last increment
// Back 1 as the current cell is the empty one
// Back another 1 since we start on row 2
numberOfRows -= 3;
// Count the number of cols
int numberOfCols = 2;
while (cellToString(1, numberOfCols++) != String.Empty)
{
}
// Back 1 from last increment
// Back 1 as the current cell is the empty one
// Back another 1 since we start on col 2
numberOfCols -= 3;
Object[,] excelArrayData = new Object[numberOfRows, 1];
Object[,] excelArrayDateData = new Object[numberOfRows, 1];
Excel.Range dataRange = (Excel.Range)ws.Cells[2, 1];
dataRange = dataRange.get_Resize(numberOfRows, 1);
dataRange.NumberFormat = "yyyy-MM-dd hh:mm";
//excelArrayDateData = (Object[,])dataRange.Cells.Text;
excelArrayDateData = (Object[, ])dataRange.Value2;
// Get original data - WORKING ON THIS BIT
//List<PITagData> originalData = myDb.GetDataForSite(piTower, new DateTime(2007, 7, 1, 10, 0, 0), new DateTime(2007, 8, 1, 10, 0, 0));
List <PITagData> originalData = myDb.GetDataForSite(piTower, DateTime.FromOADate((double)excelArrayDateData[1, 1]), DateTime.FromOADate((double)excelArrayDateData[excelArrayDateData.Length, 1]), true, false);
//PITagData PIres = originalData.Find(new PITagData("DCL.29.WD.341.Avg", new DateTime(2007, 7, 1, 10, 0, 0), "347"));
// End get original data
//DateTime test = DateTime.FromOADate((double)excelArrayDateData[1,1]);
//
//stop
//
//return;
Error frmError = new Error();
frmError.Title = "Progress";
frmError.ErrorMsg = "Sending data to PI";
frmError.btnOkVisible = false;
frmError.progressBarVisible = true;
frmError.progressBarMaximum = numberOfCols + 1;
frmError.progressBarValue = 1;
frmError.Show();
frmError.Refresh();
bool finished = false;
while (!finished)
{
// get time
//piDateTime = cellToString(row, 1);
// get column of data - include header
dataRange = (Excel.Range)ws.Cells[1, col];
dataRange = dataRange.get_Resize(numberOfRows + 1, 1);
excelArrayData = (Object[, ])dataRange.Value2;
//piVal = cellToString(row++, col);
// Get PI tag for this column
piTag = String.Format("{0}.{1}", piTower, excelArrayData[1, 1].ToString());
piInstrument = excelArrayData[1, 1].ToString();
for (int i = 1; i <= numberOfRows; i++)
{
piDateTime = DateTime.FromOADate((double)excelArrayDateData[i, 1]).ToString("yyyy-MM-dd hh:mm");
piVal = excelArrayData[i + 1, 1].ToString();
//piDateTime = cellToString(row, 1);
//old way
dataUnchanged = originalData.Exists(delegate(PITagData tag) { return(tag.Tag == piInstrument && tag.DateTime == DateTime.FromOADate((double)excelArrayDateData[i, 1]) && tag.Value == piVal); });
if (!dataUnchanged)
{
updatePIAndIncrCount(piDateTime, piTag, piVal, ref updateCount, ref failCount, myDb);
}
// mega megaSQL.Append(myDb.CreateSQL(piTag, piDateTime, piVal));
// mega updateCount++;
}
// mega blockUpdateSuccess = myDb.updateDataInBlock(megaSQL);
// mega megaSQL = new StringBuilder();
//updatePIAndIncrCount(piDateTime, piTag, piVal, ref updateCount, ref failCount, myDb);
//if (cellToString(row, 1) == "")
//{
//// give the app time to breathe
//Application.EnableEvents = true;
////Application.DoEvents();
// End of the time row, restart at next column?
col++;
row = 2;
//Application.ScreenUpdating = true;
//Application.EnableEvents = true;
frmError.progressBarValue = col - 1;
frmError.Refresh();
//Application.ScreenUpdating = false;
//Application.EnableEvents = false;
//Application.DoEvents();
// reset PI tag name
//piTag = String.Format("{0}.{1}", piTower, cellToString(1, col));
if (cellToString(row, col) == "")
{
finished = true;
}
//}
}
frmError.Close();
}
else // it is a list - one of two formats are acceptable
{
while (piRef != "")
{
// Does our piRef contain an array formula?
if (piRef.Contains("=PINCompDat"))
{
// This is a array of data from PI
// PITag+DateTime, Old Value, New Value
piTagStart = piRef.IndexOf("\"") + 1;
piTagEnd = piRef.IndexOf("\"", piTagStart + 1);
piTag = piRef.Substring(piTagStart, piTagEnd - piTagStart);
// Text of the current cell is the datetime
piDateTime = (string)range.Text;
}
else
{
// ...guess it is a user created pi tag
// PITag, DateTime, New Value
piTag = piRef;
// so datetime is in col 2
range = (Excel.Range)ws.Cells[row, 2];
piDateTime = (string)range.Text;
}
range = (Excel.Range)ws.Cells[row, 3];
piVal = (string)range.Text;
updatePIAndIncrCount(piDateTime, piTag, piVal, ref updateCount, ref failCount, myDb);
// get next
row++;
range = (Excel.Range)ws.Cells[row, 1];
piRef = (string)range.Formula;
}
}
Application.Calculation = (Microsoft.Office.Interop.Excel.XlCalculation)appCalc;
Application.ScreenUpdating = true;
Application.EnableEvents = true;
Application.DisplayAlerts = true;
myDb.SetFlushOn();
myDb.OLEConnection.Close();
if (row == 1)
{
MessageBox.Show(String.Format("No data found in cell A1 - please ensure the data is in columns A - C"));
}
else
{
TimeSpan duration = DateTime.Now - startTime;
MessageBox.Show(String.Format("{0} record{1} updated\n\n{2} failure{3}", updateCount, updateCount != 1 ? "s" : "", failCount, failCount != 1 ? "s" : ""));
MessageBox.Show(string.Format("Time taken: {0} minutes {1} seconds", duration.Minutes, duration.Seconds));
}
}
