//public Int32 DeterminePollTime()
//{
// Int32 UpperSecondBound = 10;
// Int32 LowerSecondBound = 1;
// GlobalFunctions.DecayingQueue<Int32> newQueue = new GlobalFunctions.DecayingQueue<Int32>(updateCount.DecayMilliseconds);
// Int32 ucCount = updateCount.queue.Count;
// Int32 UpperPointBound = Math.Max(1, ucCount * 60);
// Int32 AllPoints = 0;
// Tuple<Int32, DateTime> queueObj = updateCount.Dequeue();
// DateTime referenceTime = DateTime.Now.AddMilliseconds(-1 * updateCount.DecayMilliseconds);
// while (queueObj != null)
// {
// DateTime updateTime = queueObj.Item2;
// Int32 pointVal = (Int32)Math.Round((updateTime - referenceTime).TotalSeconds);
// AllPoints += Math.Max(pointVal, 0);
// Tuple<Int32, DateTime> newQueueObj = new Tuple<Int32, DateTime>(1, updateTime);
// newQueue.Enqueue(newQueueObj);
// queueObj = updateCount.Dequeue();
// }
// Double thisPointVal = (Double)(UpperPointBound - AllPoints) / (Double)UpperPointBound;
// Int32 AddToLowerBound = (Int32)Math.Round(thisPointVal * (UpperSecondBound - LowerSecondBound), 0, MidpointRounding.AwayFromZero);
// updateCount = newQueue;
// return LowerSecondBound + AddToLowerBound;
//}
public Boolean TryConnectSheet()
{
try
{
GlobalFunctions.SetActiveWorkBookName();
String channelName;
if (CheckConnection(out channelName))
{
Globals.Ribbons.Ribbon1.buttonToggleConnect.Checked = true;
Globals.Ribbons.Ribbon1.SetRibbonConnected();
Globals.Ribbons.Ribbon1.editBoxChannel.Text = channelName;
EnableSync();
return(true);
}
else
{
DisableSync();
Globals.Ribbons.Ribbon1.SetRibbonDisconnected();
return(false);
}
}
catch (Exception ex)
{
try
{
GlobalFunctions.ErrorLog(ex);
DisableSync();
Globals.Ribbons.Ribbon1.SetRibbonDisconnected();
}
catch (Exception)
{
}
return(false);
}
}
private Boolean CheckConnection(out String channelName)
{
channelName = "";
try
{
SqlConnection dConn = GlobalFunctions.GetDBConnection();
String qry = "Select * from Channels";
GlobalFunctions.AppendEqualityQueryClause(ref qry, "User_Server");
GlobalFunctions.AppendEqualityQueryClause(ref qry, "Workbook_Name");
SqlCommand cmd = new SqlCommand(qry, dConn);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@User_Server", System.Environment.MachineName);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@Workbook_Name", GlobalFunctions.ActiveWBName);
//GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@Workbook_Path", ActiveWB.Path);
DataTable ShareDT = GlobalFunctions.GetShareDBData(cmd, "Channels");
foreach (DataRow dr in ShareDT.Rows)
{
channelName = dr["Channel"].ToString();
channelID = dr["Channel_ID"].ToString();
Vars.UserID = "WORKBOOK_NAME:" + dr["Workbook_Name"].ToString() + ";USER_SERVER=" + dr["User_Server"].ToString() + ";ID:" + dr["ID"].ToString();
return(true);
}
return(false);
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
return(false);
}
}
public void DisableSync()
{
try
{
Application.SheetChange -= Application_SheetChange;
pollTimer.Enabled = false;
pollTimer.Elapsed -= pollTimer_Elapsed;
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
}
}
private List <CellUpdate> GetDBCells()
{
try
{
SqlCommand cmd = GetOthersChangesSelect();
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.DateTime2, "@Change_Time", Vars.LatestUpdateTime.ToShortDateString() + " " + Vars.LatestUpdateTime.TimeOfDay.ToString());
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@channel_id", channelID);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@Change_Author", Vars.SessionID);
DataTable ShareDT = GlobalFunctions.GetShareDBData(cmd, "Changes");
return(ConvertToList(ShareDT));
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
throw;
}
}
//public void ClearNotifications()
//{
// Excel.Worksheet ws = null;
// try
// {
// foreach (KeyValuePair<String, WorksheetExt> kvp in GlobalFunctions.worksheetBounds)
// {
// WorksheetExt wext = kvp.Value;
// ws = GlobalFunctions.findWorksheetByName(kvp.Key);
// for (int i = 1; i <= wext.LastRow; i++)
// {
// for (int j = 1; j <= wext.LastCol; j++)
// {
// if (wext.CellStyles[i, j] != null)
// {
// CellStyleExt cext = wext.CellStyles[i, j];
// CellUpdate uc = new CellUpdate(i, j, kvp.Key, cext);
// GlobalFunctions.ClearCell(uc, ws);
// wext.CellStyles[i, j] = null;
// }
// }
// }
// }
// }
// catch (Exception ex)
// {
// GlobalFunctions.ErrorLog(ex);
// }
// finally
// {
// if (ws != null) Marshal.ReleaseComObject(ws);
// }
//}
private void pollTimer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
try
{
pollTimer.Stop();
Globals.Ribbons.Ribbon1.LabelStatus.Label = "Syncing. . .";
GlobalFunctions.WaitForApplicationReady();
DoWork();
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
}
finally
{
pollTimer.Start();
Globals.Ribbons.Ribbon1.LabelStatus.Label = "Sync Complete. . .";
pollTimer.Interval = MyRefreshTime;
}
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
try
{
GlobalFunctions.ConnectToDB();
// Workbook open event does not run when debugging from visual studop
if (System.Diagnostics.Debugger.IsAttached)
{
TryConnectSheet();
}
GlobalFunctions.InfoLog("Session Started ID: " + Vars.SessionID);
Vars.LatestUpdateTime = Convert.ToDateTime(getDbTime()).AddSeconds(-5);
Application.WorkbookOpen += Application_WorkbookOpen;
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
}
}
private List <CellUpdate> ConvertToList(DataTable DT)
{
Int32 Count = DT.Rows.Count;
List <CellUpdate> otherCells = new List <CellUpdate>(Count);
try
{
for (int i = 0; i < Count; i++)
{
DataRow dr = DT.Rows[i];
CellUpdate newCell = CellUpdate.Create(dr);
otherCells.Add(newCell);
}
return(otherCells);
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
throw;
}
}
public static CellUpdate Create(DataRow dr)
{
try
{
Int32 iRow = Int32.Parse(dr["Excel_Row"].ToString());
Int32 iCol = Int32.Parse(dr["Excel_Column"].ToString());
String iWorksheet = dr["Worksheet"].ToString();
String ival = dr["Cell_Value"].ToString();
String iType = dr["Change_Type"].ToString();
String iChangeAuthor = dr["Change_Author"].ToString();
DateTime iChangeTime = (DateTime)dr["Change_Time"];
Enums.CellChangeType type = (iType == "Value" ? Enums.CellChangeType.Value : iType == "Comment" ? Enums.CellChangeType.Comment : Enums.CellChangeType.Value);
return(new CellUpdate(iRow, iCol, iWorksheet, ival, type, iChangeAuthor, iChangeTime));
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
throw;
}
}
//public CellStyleExt[,] CellStyles;
public WorksheetExt(String sheetName)
{
SheetName = sheetName;
Excel.Worksheet ws = null;
try
{
ws = GlobalFunctions.findWorksheetByName(SheetName);
LastCol = ws.UsedRange.Columns.Count < 20 ? 40 : GetLastColumn(ws) + 20;
LastRow = ws.UsedRange.Rows.Count < 20 ? 40 : GetLastRow(ws) + 20;
//CellStyles = new CellStyleExt[LastRow + 1, LastCol + 1];
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
if (ws != null)
{
Marshal.ReleaseComObject(ws);
}
throw;
}
}
private String getChannelID(String ChannelName)
{
try
{
SqlConnection dConn = GlobalFunctions.GetDBConnection();
String qry = "Select * from Channels Order By Channel_ID asc";
SqlCommand cmd = new SqlCommand(qry, dConn);
DataTable ShareDT = GlobalFunctions.GetShareDBData(cmd, "Channels");
Int32 MaxChan = 0;
if (ShareDT.Rows.Count > 0)
{
foreach (DataRow dr in ShareDT.Rows)
{
String ChannelID = dr["Channel_ID"].ToString();
String rChannelName = dr["Channel"].ToString();
MaxChan = Math.Max(1, Int32.Parse(ChannelID));
if (rChannelName == ChannelName)
{
return(ChannelID);
}
}
return((MaxChan + 1).ToString());
}
else
{
return("");
}
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
throw;
}
}
public CellUpdate(Excel.Range cell, Enums.CellChangeType type)
{
Excel.Comment comment = null;
try
{
Row = cell.Row;
Col = cell.Column;
Worksheet = cell.Worksheet.Name.ToUpper();
this.TypeEnum = type;
switch (TypeEnum)
{
case Enums.CellChangeType.Value:
val = cell.Formula == null ? "" : cell.Formula.ToString();
break;
case Enums.CellChangeType.Comment:
comment = cell.Comment;
val = comment == null ? "" : comment.Text();
break;
}
UpdateWSBounds();
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
throw;
}
finally
{
if (comment != null)
{
Marshal.ReleaseComObject(comment);
}
}
}
private Boolean RefreshSheet(CellUpdate uc)
{
Excel.Worksheet ws = null;
Excel.Range thisRange = null;
Excel.Comment comment = null;
try
{
ws = GlobalFunctions.findWorksheetByName(uc.Worksheet);
if (ws == null)
{
throw new Exception("Worksheet not found: " + uc.Worksheet);
}
GlobalFunctions.WaitForApplicationReady();
thisRange = GlobalFunctions.createRange(ws, uc.Row, uc.Col);
if (thisRange != null)
{
CellUpdate oldCell = new CellUpdate(thisRange, uc.TypeEnum);
if (!uc.Equals(oldCell))
{
switch (uc.TypeEnum)
{
case Enums.CellChangeType.Value:
thisRange.Formula = uc.val;
break;
case Enums.CellChangeType.Comment:
comment = thisRange.Comment;
if (comment == null)
{
thisRange.AddComment(uc.val);
}
else
{
if (String.IsNullOrEmpty(uc.val))
{
thisRange.ClearComments();
}
else
{
comment.Text(uc.val);
}
}
break;
}
}
GlobalFunctions.InfoLog("Received", uc);
//RefreshedCell rc = new RefreshedCell(thisRange, uc, oldCell.val);
//RefreshedCell rc = new RefreshedCell(thisRange, uc, "");
Vars.LatestUpdateTime = GlobalFunctions.MaxDate(Vars.LatestUpdateTime, uc.changeTime.AddSeconds(-1));
}
else
{
Marshal.ReleaseComObject(thisRange);
}
return(true);
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex, uc);
throw ex;
}
finally
{
if (thisRange != null)
{
Marshal.ReleaseComObject(thisRange);
}
if (ws != null)
{
Marshal.ReleaseComObject(ws);
}
}
}