/// <summary>
/// Creates the report
/// </summary>
/// <param name="startdate">Starttime</param>
/// <param name="enddate">Endtime</param>
/// <param name="db">Database to collect data from</param>
/// <returns>0 - If all ok, else a database error</returns>
public int startreport(DateTime startdate, DateTime enddate, DataBase db)
{
reportlines_ = new SortedList();
SortedList lista;
int fel = db.getResourceBoxes(startdate, enddate, out lista);
if (fel != 0)
{
return(fel);
}
for (int i = 0; i < lista.Count; i++)
{
ResourceBox rb = (ResourceBox)lista[lista.GetKey(i)];
// Calculate time in hours
if (!rb.Free)
{
if (!reportlines_.ContainsKey(rb.Name))
{
reportlines_[rb.Name] = new ReportLine(rb.Name, rb.Duration, rb.Deviation);
}
else
{
((ReportLine)reportlines_[rb.Name]).Hours += rb.Duration;
((ReportLine)reportlines_[rb.Name]).Deviation += rb.Deviation;
}
}
}
return(0);
}
/// <summary>
/// Copy all resourceboxes in a given daterange, by adding hours and days
/// </summary>
/// <param name="starttime">Start of the copyregion</param>
/// <param name="endtime">End of the copyregion</param>
/// <param name="addhours">No of days to add to the copied resourcebox</param>
/// <param name="adddays">No of hours to add to the copied resourcebox</param>
/// <param name="newrboxes">A list of new resourceboxes added to system</param>
/// <returns>0 if all is ok</returns>
public int copyResourceBoxes(DateTime starttime, DateTime endtime, int addhours, int adddays, out SortedList newrboxes)
{
// TODO: Add hour support to this function, now it will probably crack out if oldhour+newhour>24
SortedList rboxes;
db_.getResourceBoxes(starttime, endtime, out rboxes);
newrboxes = new SortedList();
for (int i = 0; i < rboxes.Count; i++)
{
// Add hours and days on all resoureboxes times
ResourceBox rb = (ResourceBox)rboxes[rboxes.GetKey(i)];
if (rb.Locked)
{
try
{
rb.LockedEndTime = rb.LockedEndTime.AddDays(adddays).AddHours(addhours);
rb.LockedStartTime = rb.LockedStartTime.AddDays(adddays).AddHours(addhours);
}
catch
{
return(ErrorHandler.ERR_COPY_WEEK);
}
}
rb.StartTime = rb.StartTime.AddDays(adddays).AddHours(addhours);
rb.EndTime = rb.EndTime.AddDays(adddays).AddHours(addhours);
rb.Id = 0;
int fel = setResourceBox(rb, true);
if (fel != ErrorHandler.NO_ERROR)
{
return(fel);
}
}
newrboxes = rboxes;
return(0);
}
/// <summary>
/// Remove resourcebox from database
/// </summary>
/// <param name="resourcebox">Resourcebox to remove</param>
/// <returns>0 - If all is ok, else a Database errorcode</returns>
internal int removeResourceBox(ResourceBox resourcebox)
{
String sqlcmd;
// Remove the resourcebox
sqlcmd = "DELETE FROM ResourceBox WHERE Id=" + resourcebox.Id;
int fel = this.executeSqlNonQry(sqlcmd);
if (fel != 0)
{
return(fel);
}
return(ErrorHandler.NO_ERROR);
}
/// <summary>
/// Updates or adds resourcebox to system
/// </summary>
/// <param name="resourcebox">Resourcebox to be updated or added</param>
/// <param name="add">True if the resourcebox is to be added</param>
/// <returns>0 - If all Ok, else a database error</returns>
/// #1.04 - New index for week list (week number)
public int setResourceBox(ResourceBox resourcebox, bool add)
{
try
{
// Save the current id-list
SortedList oldids = new SortedList(resourcebox.IdsNextTo);
int fel = db_.setResourceBox(resourcebox, add);
if (fel != 0)
{
return(fel);
}
if (add == true)
{
// Add the resourcebox to the list
this.resourceBoxes_.Add(resourcebox.Id, resourcebox);
SelectedWeek selweek = new SelectedWeek(resourcebox.StartTime);
// #1.04 - start
if (!weeks_.ContainsKey(selweek.Week))
{
weeks_[selweek.Week] = new SortedList();
}
((SortedList)weeks_[selweek.Week]).Add(resourcebox.Id, resourcebox);
// #1.04 - stop
((ActivePersonResource)this.activePersonResources_[resourcebox.Name]).AddObserver(resourcebox);
}
// Update alla resourceboxes with the old id-list
fel = this.updateIdsNextTo(oldids);
// Update id-list of the resourcebox itself
resourcebox.updateIdsNextTo(db_);
// Update alla resourceboxes with the new id-list
fel += this.updateIdsNextTo(resourcebox.IdsNextTo);
resourcebox.Notify();
if (fel != 0)
{
return(fel);
}
return(0);
}
catch
{
// If there was en error update alla resourceboxes
this.updateResourceBoxes();
}
return(ErrorHandler.ERR_UNEXPECTED_RESOURCEBOX_ADD);
}
/// <summary>
/// Adds or updates a resourcebox in database
/// </summary>
/// <param name="resourcebox"></param>
/// <param name="add"></param>
/// <returns>0 - If all is ok, ERR_UNEXCPECTED_RESOURCEBOX_ADD if the resourcebox already exists, else database error</returns>
internal int setResourceBox(ResourceBox resourcebox, bool add)
{
String sqlcmd;
// Create SQL for add or update database
if (add == true)
{
sqlcmd = "INSERT INTO ResourceBox (starttime,endtime,name,free,locked,lockedstarttime,lockedendtime ) VALUES ('" + resourcebox.StartTime.ToShortDateString() + " " + resourcebox.StartTime.ToLongTimeString() + "','" + resourcebox.EndTime.ToShortDateString() + " " + resourcebox.EndTime.ToLongTimeString() + "', '" + resourcebox.Name + "', " + resourcebox.Free + ", " + resourcebox.Locked + ", '" + resourcebox.LockedStartTime.ToShortDateString() + " " + resourcebox.LockedStartTime.ToLongTimeString() + "','" + resourcebox.LockedEndTime.ToShortDateString() + " " + resourcebox.LockedEndTime.ToLongTimeString() + "')";
}
else
{
sqlcmd = "UPDATE ResourceBox SET starttime='" + resourcebox.StartTime.ToShortDateString() + " " + resourcebox.StartTime.ToLongTimeString() + "', endtime='" + resourcebox.EndTime.ToShortDateString() + " " + resourcebox.EndTime.ToLongTimeString() + "', name='" + resourcebox.Name + "', free=" + resourcebox.Free + ", locked=" + resourcebox.Locked + ", lockedstarttime='" + resourcebox.LockedStartTime.ToShortDateString() + " " + resourcebox.LockedStartTime.ToLongTimeString() + "', lockedendtime='" + resourcebox.LockedEndTime.ToShortDateString() + " " + resourcebox.LockedEndTime.ToLongTimeString() + "' WHERE Id=" + resourcebox.Id;
}
// Run SQL
int fel = this.executeSqlNonQry(sqlcmd);
if (fel != 0)
{
return(ErrorHandler.ERR_UNEXPECTED_RESOURCEBOX_ADD);
}
if (add == true)
{
sqlcmd = "SELECT MAX(id) FROM ResourceBox";
OleDbDataReader reader = executeSql(sqlcmd);
if (reader == null)
{
return(ErrorHandler.ERR_UNEXPECTED_RESOURCEBOX_ADD);
}
// Get id, because of the access counter it will always be max
if (reader.HasRows)
{
reader.Read();
resourcebox.Id = reader.GetInt32(0);
reader.Close();
}
else
{
reader.Close();
return(ErrorHandler.ERR_UNEXPECTED_RESOURCEBOX_ADD);
}
}
return(0);
}
/// <summary>
/// Gets all ids of ResourceBoxes that overlaps given resourcebox
/// </summary>
/// <param name="resourcebox">ResourceBox for wich all overlapping ids is to be calculated</param>
/// <param name="sl">SortedList with all ids as keys</param>
/// <returns>0 - If all is ok, </returns>
internal int getResouceBoxesNextTo(ResourceBox resourcebox, out SortedList sl)
{
String sqlcmd;
// Get all ids from resourceboxes that are overlapping this resourcebox
sqlcmd = "SELECT Id FROM ResourceBox rb WHERE Id<>" + resourcebox.Id + " AND ( starttime BETWEEN #" + resourcebox.StartTime.ToShortDateString() + " " + resourcebox.StartTime.ToLongTimeString() + "# AND #" + resourcebox.EndTime.ToShortDateString() + " " + resourcebox.EndTime.ToLongTimeString() + "# OR " +
" endtime BETWEEN #" + resourcebox.StartTime.ToShortDateString() + " " + resourcebox.StartTime.ToLongTimeString() + "# AND #" + resourcebox.EndTime.ToShortDateString() + " " + resourcebox.EndTime.ToLongTimeString() + "# ) AND starttime NOT IN (SELECT endtime FROM ResourceBox Where id=rb.Id)";
sl = new SortedList();
OleDbDataReader reader = executeSql(sqlcmd);
if (reader == null)
{
return(ErrorHandler.ERR_UNEXPECTED_READ_ERROR_OVERLAPPING);
}
// Add all ids to the sortedlist
while (reader.Read())
{
if (!sl.ContainsKey(reader.GetInt32(0)))
{
sl.Add(reader.GetInt32(0), reader.GetInt32(0));
}
}
reader.Close();
// Get all ids from resourceboxes that overlaps this resourcebox
sqlcmd = "SELECT r2.Id FROM ResourceBox r1, ResourceBox r2 WHERE r2.Id<>" + resourcebox.Id + " AND r1.Id=" + resourcebox.Id + " AND ( r1.starttime BETWEEN r2.starttime AND r2.endtime OR " +
" r1.endtime BETWEEN r2.starttime AND r2.endtime ) AND r1.starttime NOT IN (SELECT endtime FROM ResourceBox WHERE id=r1.id) AND r2.starttime NOT IN (SELECT endtime FROM ResourceBox WHERE id=r2.id)";
reader = executeSql(sqlcmd);
if (reader == null)
{
return(ErrorHandler.ERR_UNEXPECTED_READ_ERROR_OVERLAPPING);
}
// Add all ids to the sortedlist
while (reader.Read())
{
if (!sl.ContainsKey(reader.GetInt32(0)))
{
sl.Add(reader.GetInt32(0), reader.GetInt32(0));
}
}
reader.Close();
return(ErrorHandler.NO_ERROR);
}
/// <summary>
/// Get resourceboxes from the database
/// </summary>
/// <param name="startdate">Startdate of the resourceboxes to read, if DateTime(0) all resourceboxes are fetched</param>
/// <param name="enddate">Enddate of the resourceboxes to read</param>
/// <returns>0 - If all is ok</returns>
internal int getResourceBoxes(DateTime startdate, DateTime enddate, out SortedList retlist)
{
/* Create SQL code for fetching resourceboxes */
String sqlcmd = "SELECT id, starttime, endtime, name, free, locked, lockedstarttime, lockedendtime FROM ResourceBox";
// If startdate and enddate is given, use them in a WHERE part of SQL
if (startdate != new DateTime(0))
{
sqlcmd += " WHERE starttime BETWEEN #" + startdate.ToShortDateString() + " " + startdate.ToLongTimeString() + "# AND #" + enddate.ToShortDateString() + " " + enddate.ToLongTimeString() + "#";
}
// #1.01.003 - added line
sqlcmd += " ORDER BY starttime";
retlist = new SortedList();
// Get data from database
OleDbDataReader reader = executeSql(sqlcmd);
if (reader == null)
{
return(ErrorHandler.ERR_UNEXPECTED_RESOURCEBOX_FETCH);
}
int i = 0;
// #1.01.003 - added lines
ResourceBox[] rbs;
rbs = new ResourceBox[20000];
// Add all data in the sortedlist
while (reader.Read())
{
ResourceBox resursbox = new ResourceBox(reader.GetDateTime(1), reader.GetDateTime(2), reader.GetString(3), reader.GetBoolean(4), reader.GetBoolean(5), reader.GetDateTime(6), reader.GetDateTime(7));
resursbox.Id = reader.GetInt32(0);
// Get all overlaps of resourcebox
retlist.Add(resursbox.Id, resursbox);
rbs[i] = resursbox; // #1.01.003 - added line
i++;
}
// #1.01.003 - added lines
for (int o = 0; o < i - 1; o++)
{
for (int a = o + 1; rbs[a].StartTime <= rbs[o].EndTime && o < i; a++)
{
if (rbs[a].StartTime != rbs[a].EndTime &&
rbs[o].StartTime != rbs[o].EndTime)
{
rbs[a].IdsNextTo.Add(rbs[o].Id, rbs[o].Id);
rbs[o].IdsNextTo.Add(rbs[a].Id, rbs[a].Id);
}
// #1.03 - added lines
if (a + 1 >= i)
{
break;
}
}
}
// #1.01.003 - end
reader.Close();
return(ErrorHandler.NO_ERROR);
}
/// <summary>
/// Removes a resourcebox from system.
/// </summary>
/// <param name="resourcebox">Resourcebox to remove</param>
/// <returns>0 - If all Ok, else a database error</returns>
/// #1.04 - New index for week list (week number)
public int removeResourceBox(ResourceBox resourcebox)
{
// If the resourcebox just is locked, then just zero it, by setting end and startime to the same thing
if (resourcebox.Locked == true)
{
resourcebox.EndTime = resourcebox.LockedStartTime;
resourcebox.StartTime = resourcebox.LockedStartTime;
this.setResourceBox(resourcebox, false);
resourcebox.Notify();
return(0);
}
else
{
try
{
// Get old id-list
SortedList oldids = new SortedList(resourcebox.IdsNextTo);
int fel = db_.removeResourceBox(resourcebox);
if (fel != 0)
{
return(fel);
}
// Update with old id-list
fel = this.updateIdsNextTo(oldids);
// HACK: är denna rad nödvändig?
fel += this.updateIdsNextTo(resourcebox.IdsNextTo);
this.resourceBoxes_.Remove(resourcebox.Id);
// Remove for week-lists (this week and next)
SelectedWeek selweek = new SelectedWeek(resourcebox.StartTime);
// #1.04 - start
if (weeks_.ContainsKey(selweek.Week))
{
((SortedList)weeks_[selweek.Week]).Remove(resourcebox.Id);
}
selweek = new SelectedWeek(selweek.DateOfFirstDayInWeek.AddDays(7));
if (weeks_.ContainsKey(selweek.Week))
{
((SortedList)weeks_[selweek.Week]).Remove(resourcebox.Id);
}
// #1.04 - stop
// Hide graphics
resourcebox.StartTime = new DateTime(0);
resourcebox.EndTime = new DateTime(0);
resourcebox.Notify();
if (fel != 0)
{
return(fel);
}
return(0);
}
catch
{
this.updateResourceBoxes();
}
return(ErrorHandler.ERR_UNEXPECTED_RESOURCEBOX_REM);
}
}
