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