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