/** * <summary> * Method that reads a Tripletex export file and write it's content to a table in the database. * </summary> */ public static void ReadFileAndWriteToDB(Stream stream) { StreamReader reader = new StreamReader(stream); DatabaseClassesDataContext db = new DatabaseClassesDataContext(); string line; while((line = reader.ReadLine()) != null) { try { line = line.Substring(1, line.Length-2); //Removes the surrounding "" string[] columns = line.Split(';'); TripletexImport row = new TripletexImport { ProjectNo = int.Parse(columns[0]), ProjectName = columns[1], ProjectLeader = columns[2], DepName = columns[3], EmployeeName = columns[4], Date = DateTime.Parse(columns[5]), Hours = double.Parse(columns[6]), Comment = columns[7] }; db.TripletexImports.InsertOnSubmit(row); } catch (Exception e) { TripletexImportException tiEx = new TripletexImportException(e.Message); log.Error(tiEx.Message); throw tiEx; } } try { //Deletes the current content db.ExecuteCommand("DELETE FROM TripletexImport"); //Submits the new content db.SubmitChanges(); log.Info("A new CSV file was read and TripletexImport table was updated."); } catch (Exception e) { log.Error("Something went wrong while trying to update the TripletexImport table: " + e.Message); } }
/** * <summary> * Method that performs the search for the specified input. * </summary> */ private void performSearch(string input) { SearchInputHidden.Value = input; //Resets the page MessagePanel.Visible = false; Filter.Visible = false; Results.DataSource = null; Results.DataBind(); dataContext = new DatabaseClassesDataContext(); //Gets the HourPrice that's configured in the Web.config's AppSettings section. double hourPrice = Convert.ToDouble(ConfigurationManager.AppSettings["HourPrice"]); //Query that gets the project number, project name, customer name, project manager, project start time, //project stop time, hours spent, sum of total sales amount and the latest balance for each SLA project that //matches the input parameter. var query = (from project in dataContext.Projects join slaProjects in dataContext.SLAProjects on project.ProjectNo equals slaProjects.ProjectNo join customer in dataContext.Customers on project.CustomerNo equals customer.CustomerNo join employee in dataContext.Employees on project.PMEmployeeNo equals employee.EmployeeNo join tripletexImport in ( from ti in dataContext.TripletexImports group ti by ti.ProjectNo into g select new { ProjectNo = g.Key, HoursSpent = g.Sum(p => p.Hours) } ) on project.ProjectNo equals tripletexImport.ProjectNo into tripletexImportGroup from hoursSpent in tripletexImportGroup.DefaultIfEmpty() join salesFigures in ( from sf in dataContext.SalesFigures group sf by sf.ProjectNo into g select new { ProjectNo = g.Key, TotalSalesAmount = g.Sum(p => p.TotalSalesAmount) } ) on project.ProjectNo equals salesFigures.ProjectNo into salesFiguresGroup from salesFigures in salesFiguresGroup.DefaultIfEmpty() join balance in ( from b in dataContext.Balances orderby b.LastUpdate descending, b.Year descending, b.Period descending select new { ProjectNo = b.ProjectNo, BalanceAmount = b.Amount } ) on project.ProjectNo equals balance.ProjectNo into balanceGroup from balance in balanceGroup.DefaultIfEmpty().Take(1) where SqlMethods.Like(project.ProjectNo.ToString(), string.Format("%{0}%", input)) || SqlMethods.Like(project.Name.ToLower(), string.Format("%{0}%", input.ToLower())) || SqlMethods.Like(employee.Name.ToLower(), string.Format("%{0}%", input.ToLower())) select new { ProjectNo = project.ProjectNo, ProjectName = project.Name, CustomerName = customer.Name, ProjectManager = employee.Name, ProjectStartTime = project.StartTime, ProjectStopTime = project.StopTime, HoursSpent = hoursSpent.HoursSpent != null ? hoursSpent.HoursSpent * hourPrice : 0.0, TotalSalesAmount = salesFigures.TotalSalesAmount != null ? salesFigures.TotalSalesAmount : 0.0, BalanceAmount = balance.BalanceAmount != null ? balance.BalanceAmount : 0.0 }).Distinct(); var list = query.ToList(); if (list.Count != 0) { Filter.Visible = true; //Binds the data to the GridView Results.DataSource = list; Results.DataBind(); } else { MessagePanel.Visible = true; } }
/** * <summary> * Loads the SLATab, with the specified sorting expression and sorting order. * </summary> */ private void LoadSLATab(string sortExpression, string sortOrder) { dataContext = new DatabaseClassesDataContext(); //Query that gets the project number, project name, customer name, project manager //and the latest balance for each SLA project. var mainQuery = (from project in dataContext.Projects join customer in dataContext.Customers on project.CustomerNo equals customer.CustomerNo join slaProjects in dataContext.SLAProjects on project.ProjectNo equals slaProjects.ProjectNo join employee in dataContext.Employees on project.PMEmployeeNo equals employee.EmployeeNo join balance in ( from b in dataContext.Balances orderby b.LastUpdate descending, b.Year descending, b.Period descending select new { ProjectNo = b.ProjectNo, BalanceAmount = b.Amount } ) on project.ProjectNo equals balance.ProjectNo into balanceGroup from balance in balanceGroup.DefaultIfEmpty().Take(1) orderby project.ProjectNo ascending select new { ProjectNo = project.ProjectNo, ProjectName = project.Name, CustomerName = customer.Name, ProjectManager = employee.Name, BalanceAmount = balance.BalanceAmount != null ? balance.BalanceAmount : 0 }).Distinct(); //Creates a DataTable to fill with the results from the query. DataTable dt = new DataTable(); dt.Columns.Add(PROJECT_NO, Type.GetType("System.Int32")); dt.Columns.Add(PROJECT_NAME); dt.Columns.Add(CUSTOMER_NAME); dt.Columns.Add(PROJECT_MANAGER); dt.Columns.Add(BALANCE_AMOUNT, Type.GetType("System.Double")); //Fills the DataTable with data foreach (var row in mainQuery) { DataRow newRow = dt.NewRow(); newRow[PROJECT_NO] = row.ProjectNo; newRow[PROJECT_NAME] = row.ProjectName; newRow[CUSTOMER_NAME] = row.CustomerName; newRow[PROJECT_MANAGER] = row.ProjectManager; newRow[BALANCE_AMOUNT] = row.BalanceAmount; dt.Rows.Add(newRow); } //Binds the data to the GridView SLATable.DataSource = dt; SLATable.DataBind(); //Sorts the GridView if (sortExpression != string.Empty) { Sort(SLATable, sortExpression, sortOrder); } else { Sort(SLATable, "ProjectNo", ASC); } }
/** * <summary> * Loads the OverviewTab, with the specified sorting expression and sorting order. * </summary> */ private void LoadOverviewTab(string sortExpression, string sortOrder) { dataContext = new DatabaseClassesDataContext(); //Gets the HourPrice configured in the Web.Config's AppSettings section hourPrice = Convert.ToDouble(ConfigurationManager.AppSettings["HourPrice"]); //Query that gets the project number, project name, customer name, project manager, project start time, //project stop time, hours spent, sum of total sales amount and the latest balance for each SLA project. var query = (from project in dataContext.Projects join slaProjects in dataContext.SLAProjects on project.ProjectNo equals slaProjects.ProjectNo join customer in dataContext.Customers on project.CustomerNo equals customer.CustomerNo join employee in dataContext.Employees on project.PMEmployeeNo equals employee.EmployeeNo join tripletexImport in ( from ti in dataContext.TripletexImports group ti by ti.ProjectNo into g select new { ProjectNo = g.Key, HoursSpent = g.Sum(p => p.Hours) } ) on project.ProjectNo equals tripletexImport.ProjectNo into tripletexImportGroup from hoursSpent in tripletexImportGroup.DefaultIfEmpty() join salesFigures in ( from sf in dataContext.SalesFigures group sf by sf.ProjectNo into g select new { ProjectNo = g.Key, TotalSalesAmount = g.Sum(p => p.TotalSalesAmount) } ) on project.ProjectNo equals salesFigures.ProjectNo into salesFiguresGroup from salesFigures in salesFiguresGroup.DefaultIfEmpty() join balance in ( from b in dataContext.Balances orderby b.LastUpdate descending, b.Year descending, b.Period descending select new { ProjectNo = b.ProjectNo, BalanceAmount = b.Amount } ) on project.ProjectNo equals balance.ProjectNo into balanceGroup from balance in balanceGroup.DefaultIfEmpty().Take(1) select new { ProjectNo = project.ProjectNo, ProjectName = project.Name, CustomerName = customer.Name, ProjectManager = employee.Name, ProjectStartTime = project.StartTime, ProjectStopTime = project.StopTime, HoursSpent = hoursSpent.HoursSpent != null ? hoursSpent.HoursSpent * hourPrice : 0.0, TotalSalesAmount = salesFigures.TotalSalesAmount != null ? salesFigures.TotalSalesAmount : 0.0, BalanceAmount = balance.BalanceAmount != null ? balance.BalanceAmount : 0.0 }).Distinct(); //Creates a DataTable to fill with the results from the query. DataTable dt = new DataTable(); dt.Columns.Add(PROJECT_NO, Type.GetType("System.Int32")); dt.Columns.Add(PROJECT_NAME); dt.Columns.Add(CUSTOMER_NAME); dt.Columns.Add(PROJECT_MANAGER); dt.Columns.Add(PROJECT_START_TIME, Type.GetType("System.DateTime")); dt.Columns.Add(PROJECT_STOP_TIME, Type.GetType("System.DateTime")); dt.Columns.Add(HOURS_SPENT, Type.GetType("System.Double")); dt.Columns.Add(TOTAL_SALES_AMOUNT, Type.GetType("System.Double")); dt.Columns.Add(BALANCE_AMOUNT, Type.GetType("System.Double")); //Fills the DataTable with data foreach (var row in query) { DataRow newRow = dt.NewRow(); newRow[PROJECT_NO] = row.ProjectNo; newRow[PROJECT_NAME] = row.ProjectName; newRow[CUSTOMER_NAME] = row.CustomerName; newRow[PROJECT_MANAGER] = row.ProjectManager; newRow[PROJECT_START_TIME] = row.ProjectStartTime; newRow[PROJECT_STOP_TIME] = row.ProjectStopTime; newRow[HOURS_SPENT] = row.HoursSpent; newRow[TOTAL_SALES_AMOUNT] = row.TotalSalesAmount; newRow[BALANCE_AMOUNT] = row.BalanceAmount; dt.Rows.Add(newRow); } //Binds the data to the GridView OverviewTable.DataSource = dt; OverviewTable.DataBind(); //Sorts the GridView if (sortExpression != string.Empty) { Sort(OverviewTable, sortExpression, sortOrder); } else { Sort(OverviewTable, "ProjectNo", ASC); } }
/** * <summary> * Loads the AddlServicesTab, with the specified sorting expression and sorting order. * </summary> */ private void LoadAddlServicesTab(string sortExpression, string sortOrder) { dataContext = new DatabaseClassesDataContext(); //Query that gets the project number, project name, customer name and the sum of total sales amount for a sla project, //and also gets the article numbers and article names of the articles sold to the customer. var query = (from project in dataContext.Projects join customer in dataContext.Customers on project.CustomerNo equals customer.CustomerNo join slaProjects in dataContext.SLAProjects on project.ProjectNo equals slaProjects.ProjectNo join article in ( from a in dataContext.Articles join sf in dataContext.SalesFigures on a.ArticleNo equals sf.ArticleNo select new { ArticleNo = a.ArticleNo, ArticleName = a.Name, ProjectNo = sf.ProjectNo } ) on project.ProjectNo equals article.ProjectNo into articelSelection from article in articelSelection.Distinct().DefaultIfEmpty() join salesFigures in ( from sf in dataContext.SalesFigures group sf by new { sf.ProjectNo, sf.ArticleNo } into g select new { ProjectNo = g.Key.ProjectNo, ArticleNo = g.Key.ArticleNo, TotalSalesAmount = g.Sum(p => p.TotalSalesAmount) } ) on new { project.ProjectNo, article.ArticleNo } equals new { salesFigures.ProjectNo, salesFigures.ArticleNo } select new { ProjectNo = project.ProjectNo, ProjectName = project.Name, CustomerName = customer.Name, ArticleNo = article.ArticleNo, ArticleName = article.ArticleName, TotalSalesAmount = salesFigures.TotalSalesAmount }).Distinct(); //Creates a DataTable to fill with the results from the query. DataTable dt = new DataTable(); dt.Columns.Add(PROJECT_NO, Type.GetType("System.Int32")); dt.Columns.Add(PROJECT_NAME); dt.Columns.Add(CUSTOMER_NAME); dt.Columns.Add(ARTICLE_NO); dt.Columns.Add(ARTICLE_NAME); dt.Columns.Add(TOTAL_SALES_AMOUNT, Type.GetType("System.Double")); //Fills the DataTable with data. foreach (var row in query) { DataRow newRow = dt.NewRow(); newRow[PROJECT_NO] = row.ProjectNo; newRow[PROJECT_NAME] = row.ProjectName; newRow[CUSTOMER_NAME] = row.CustomerName; newRow[ARTICLE_NO] = row.ArticleNo; newRow[ARTICLE_NAME] = row.ArticleName; newRow[TOTAL_SALES_AMOUNT] = row.TotalSalesAmount; dt.Rows.Add(newRow); } //Binds the data to the GridView AddlServicesTable.DataSource = dt; AddlServicesTable.DataBind(); //Sorts the GridView if (sortExpression != string.Empty) { Sort(AddlServicesTable, sortExpression, sortOrder); } else { Sort(AddlServicesTable, "ProjectNo", ASC); } }
/** * <summary> * Method that creates a graph with the specified data from the dataSelection parameter, as the type specified * by the chartType parameter, with the specified number of projects from the count paramater and in the direction * specified by the direction parameter. * </summary> */ private void CreateGraph(string dataSelection, SeriesChartType chartType, int count, string direction) { dataContext = new DatabaseClassesDataContext(); //Gets the HourPrice configured in the Web.Config's AppSettings section hourPrice = Convert.ToDouble(ConfigurationManager.AppSettings["HourPrice"]); //Query that gets the Project name, customer name and the latest balance for each SLA project var balanceAmountQuery = (from project in dataContext.Projects join slaProjects in dataContext.SLAProjects on project.ProjectNo equals slaProjects.ProjectNo join customer in dataContext.Customers on project.CustomerNo equals customer.CustomerNo join balance in ( from b in dataContext.Balances orderby b.LastUpdate descending, b.Year descending, b.Period descending select new { ProjectNo = b.ProjectNo, BalanceAmount = b.Amount } ) on project.ProjectNo equals balance.ProjectNo into balanceGroup from balance in balanceGroup.DefaultIfEmpty().Take(1) select new { ProjectName = project.Name, CustomerName = customer.Name, BalanceAmount = balance.BalanceAmount != null ? balance.BalanceAmount : 0.0 }).Distinct(); //Query that gets the project name, customer name and the product of the sum of the hours spent //multiplied with the hour price var hoursSpentQuery = (from project in dataContext.Projects join slaProjects in dataContext.SLAProjects on project.ProjectNo equals slaProjects.ProjectNo join customer in dataContext.Customers on project.CustomerNo equals customer.CustomerNo join tripletexImport in ( from ti in dataContext.TripletexImports group ti by ti.ProjectNo into g select new { ProjectNo = g.Key, HoursSpent = g.Sum(p => p.Hours) } ) on project.ProjectNo equals tripletexImport.ProjectNo into tripletexImportGroup from hoursSpent in tripletexImportGroup.DefaultIfEmpty() select new { ProjectName = project.Name, CustomerName = customer.Name, HoursSpent = hoursSpent.HoursSpent != null ? hoursSpent.HoursSpent * hourPrice : 0.0, }).Distinct(); //Query that gets the project name, customer name and the sum of the total sales amount for a SLA project. var totalSalesAmountQuery = (from project in dataContext.Projects join slaProjects in dataContext.SLAProjects on project.ProjectNo equals slaProjects.ProjectNo join customer in dataContext.Customers on project.CustomerNo equals customer.CustomerNo join salesFigures in ( from sf in dataContext.SalesFigures group sf by sf.ProjectNo into g select new { ProjectNo = g.Key, TotalSalesAmount = g.Sum(p => p.TotalSalesAmount) } ) on project.ProjectNo equals salesFigures.ProjectNo into salesFiguresGroup from salesFigures in salesFiguresGroup.DefaultIfEmpty() select new { ProjectName = project.Name, CustomerName = customer.Name, TotalSalesAmount = salesFigures.TotalSalesAmount != null ? salesFigures.TotalSalesAmount : 0.0 }).Distinct(); if(dataSelection.Equals(BALANCE_AMOUNT)) { if (direction.Equals(DESC)) { //Takes the <count> projects from the result in descending order, ordered by BalanceAmount Graph.DataSource = balanceAmountQuery.OrderByDescending(p => p.BalanceAmount).Take(count); } else { //Takes the <count> projects from the result in ascending order, ordered by BalanceAmount Graph.DataSource = balanceAmountQuery.OrderBy(p => p.BalanceAmount).Take(count); } } else if (dataSelection.Equals(HOURS_SPENT)) { if (direction.Equals(DESC)) { //Takes the <count> projects from the result in descending order, ordered by Hours spent Graph.DataSource = hoursSpentQuery.OrderByDescending(p => p.HoursSpent).Take(count); } else { //Takes the <count> projects from the result in ascending order, ordered by Hours spent Graph.DataSource = hoursSpentQuery.OrderBy(p => p.HoursSpent).Take(count); } } else if (dataSelection.Equals(TOTAL_SALES_AMOUNT)) { if (direction.Equals(DESC)) { //Takes the <count> projects from the result in descending order, ordered by Total Sales Amount Graph.DataSource = totalSalesAmountQuery.OrderByDescending(p => p.TotalSalesAmount).Take(count); } else { //Takes the <count> projects from the result in ascending order, ordered by Total Sales Amount Graph.DataSource = totalSalesAmountQuery.OrderBy(p => p.TotalSalesAmount).Take(count); } } //Creates the graph Series series = new Series("Series1"); series.ChartArea = "ChartArea1"; series.ChartType = chartType; series.XValueMember = "ProjectName"; series.YValueMembers = dataSelection; series["PointWidth"] = "0.5"; Graph.Series.Add(series); //Binds the data to the Graph Graph.DataBind(); }