private void ProjectSummary()
{
// get a summary of all data relating to the projects
try
{
// get a list of all running projects
try
{
DB1 = new DBManager();
this.RunningProjectsListView.ItemsSource = DB1.GetProjects("SELECT * FROM projects WHERE start_date < '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "' AND end_date > '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "'");
Debug.WriteLine("SELECT * FROM projects WHERE start_date < '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "' AND end_date > '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "'");
ProjectListviewItem_Click(null, null);
this.ProjectsDatagrid.ItemsSource = DB1.GetRawData("SELECT project_name AS 'ALL PROJECTS' FROM projects ").DefaultView;
}
catch { }
}
catch { }
}
private void TasksSummary()
{
try
{
DB1 = new DBManager();
this.RunningTasksListView.ItemsSource = DB1.GetTasks("SELECT * FROM tasks WHERE start_date < '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "' AND end_date > '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "'");
Debug.WriteLine("SELECT * FROM tasks WHERE start_date < '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "' AND end_date > '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "'");
this.AllTasksDatagrid.ItemsSource = DB1.GetRawData("SELECT a.task_name AS 'ALL TASKS' , b.project_name FROM tasks a , projects b WHERE a.project_id = b.project_id").DefaultView;
}
catch { }
}
private void ReportsProjectsCombobox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
try
{
DB1 = new DBManager();
ComboBoxItem c = (ComboBoxItem) ((ComboBox)sender).SelectedItem;
switch (c.Content.ToString().ToLower())
{
case "all projects":
// get a list of all projects
this.ReportsDataGrid.ItemsSource = DB1.GetRawData("SELECT * FROM projects ").DefaultView;
break;
case "running projects":
this.ReportsDataGrid.ItemsSource = DB1.GetRawData("SELECT * FROM projects WHERE start_date < '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "' AND end_date > '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "'").DefaultView;
break;
case "job allocation":
this.ReportsDataGrid.ItemsSource = DB1.GetRawData("SELECT e.project_name , a.job_name , count(b.job_type_id) AS count FROM job_type a , job_description b , employees c , project_employee_allocation d , projects e WHERE a.job_type_id = b.job_type_id AND b.employee_id = c.employee_id AND c.employee_id = d.employee_id AND d.project_id = e.project_id GROUP BY e.project_name , a.job_name ").DefaultView;
break;
case "resource expenses":
this.ReportsDataGrid.ItemsSource = DB1.GetRawData("SELECT d.project_name , a.material_name , SUM(b.quantity) AS quantity, SUM(b.quantity * b.unit_buying_price) AS Cost FROM materials a , task_materials b , tasks c , projects d WHERE a.material_id = b.material_id AND b.task_id = c.task_id AND c.project_id = d.project_id Group BY d.project_name , a.material_name").DefaultView;
break;
case "tasks in project":
this.ReportsDataGrid.ItemsSource = DB1.GetRawData("SELECT b.project_name , a.* FROM tasks a , projects b WHERE a.project_id = b.project_id" ).DefaultView;
break;
}
}
catch { }
}
private void ReportsTasksCombobox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
try
{
DB1 = new DBManager();
ComboBoxItem c = (ComboBoxItem) ((ComboBox)sender).SelectedItem;
switch (c.Content.ToString().ToLower())
{
case "all tasks":
this.ReportsDataGrid.ItemsSource = DB1.GetRawData("SELECT * FROM tasks").DefaultView;
break;
case "running tasks":
this.ReportsDataGrid.ItemsSource = DB1.GetRawData("SELECT * FROM tasks WHERE start_date < '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "' AND end_date > '" + DateTime.Now.ToString("yyyy'-'MM'-'dd") + "'").DefaultView;
break;
case "resource allocation":
this.ReportsDataGrid.ItemsSource = DB1.GetRawData("SELECT a.task_name , b.material_name , c.quantity , c.unit_buying_price , (c.quantity * c.unit_Buying_price) AS 'cost' , c.date_allocated AS 'Date' FROM tasks a , materials b , task_materials c WHERE c.material_id = b.material_id AND c.task_id = a.task_id ").DefaultView;
break;
case "resource expenses":
this.ReportsDataGrid.ItemsSource = DB1.GetRawData("SELECT c.task_name, a.material_name, SUM(b.quantity) AS quantity, SUM(b.quantity * b.unit_buying_price) AS Cost FROM materials a , task_materials b , tasks c , projects d WHERE a.material_id = b.material_id AND b.task_id = c.task_id AND c.project_id = d.project_id Group BY c.task_name, a.material_name").DefaultView;
break;
}
}
catch { }
}
private void ReportsEmployeesCombobox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
try
{
DB1 = new DBManager();
ComboBoxItem c = (ComboBoxItem) ((ComboBox)sender).SelectedItem;
switch (c.Content.ToString().ToLower())
{
case "all employees":
this.ReportsDataGrid.ItemsSource = DB1.GetRawData("SELECT employee_name , employee_contact_number FROM employees").DefaultView;
break;
case "job descriptions":
this.ReportsDataGrid.ItemsSource = DB1.GetRawData("SELECT a.employee_name , a.employee_contact_number , c.job_name FROM employees a , job_description b , job_type c WHERE c.job_type_id = b.job_type_id AND b.employee_id = a.employee_id ").DefaultView;
break;
}
}
catch { }
}
