using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.UI.WebControls;

namespace dbexam
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        DataClasses1DataContext dbContext;

        protected void Page_Load(object sender, EventArgs e)
        {
            dbContext = new DataClasses1DataContext();
            getFixedPriceProjects();
        }

        private void getFixedPriceProjects()
        {
            var fixedprojects =
            from p in dbContext.projects
            where p.fixedprice != null && p.projectstatus == "active"
            select new { p.estimatedhours, p.id, p.projectname };

            foreach (var p in fixedprojects)
            {
                var timeregistrationtables =
                from t in dbContext.timeregistrations
                where t.projectid == p.id
                select new { t.starttime, t.endtime };

                TimeSpan totalTimeSpentOnProject = new TimeSpan(0);

                foreach (var t in timeregistrationtables)
                {
                    var start = ((DateTime)t.starttime).ToString("hh:mm");
                    var end = ((DateTime)t.endtime).ToString("hh:mm");
                    if (t.starttime != null && t.endtime != null)
                    {
                        totalTimeSpentOnProject += DateTime.Parse(end).Subtract(DateTime.Parse(start));
                    }
                }
                var estimatedHours = "Estimated hours: " + p.estimatedhours;
                var totalTime = "Total man hours spent: " + totalTimeSpentOnProject;
                var fixedprojectoutcome = new List<Object> { estimatedHours, totalTime };

                GridView fixedProjectGrid = new GridView();
                GridViewRow HeaderGridRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
                TableCell HeaderCell = new TableCell();
                HeaderCell.Text = p.projectname;
                HeaderCell.HorizontalAlign = HorizontalAlign.Center;
                HeaderCell.ColumnSpan = 2;
                HeaderGridRow.Cells.Add(HeaderCell);

                fixedProjectGrid.DataSource = fixedprojectoutcome;
                fixedProjectGrid.DataBind();
                fixedProjectGrid.Controls[0].Controls.AddAt(0, HeaderGridRow);
                form1.Controls.Add(fixedProjectGrid);
            }
        }

        protected void BossBotton_Click(object sender, EventArgs e)
        {
            if (BossNameText.Text.Length > 0)
            {
                var bossData =
                from b in dbContext.bosses
                where b.name == BossNameText.Text
                select new { b.id };

                foreach (var b in bossData)
                {
                    var employeeData =
                        from em in dbContext.employees
                        from empro in dbContext.employeeprojects
                        from p in dbContext.projects
                        from c in dbContext.customers
                        where em.bossid == b.id
                        where empro.employeeid == em.id
                        where p.id == empro.projectid
                        where c.id == p.customerid
                        select new { p.projectname, c.customername };
                    GridView bossGrid = new GridView();
                    bossGrid.DataSource = employeeData;
                    bossGrid.DataBind();
                    form1.Controls.Add(bossGrid);

                }
            }
        }

        protected void NewProjectButton_Click(object sender, EventArgs e)
        {
            project of = new project
            {
                projectname = NewProjectName.Text,
                projectstatus = "active",
                customerid = 3,
                fixedprice = null,
                estimatedhours = null,
                hourlyrate = null
            };
            dbContext.projects.InsertOnSubmit(of);
            try
            {
                dbContext.SubmitChanges();
            }
            catch (Exception ex)
            {
                Console.WriteLine(e);
            }
            var newProjectData =
              from ep in dbContext.employeeprojects
              where ep.projectid == Int32.Parse(OldProjectID.Text)
              select new { ep.employeeid };
            foreach (var np in newProjectData)
            {
                employeeproject emp = new employeeproject
                {
                    employeeid = np.employeeid,
                    projectid = of.id
                };
                dbContext.employeeprojects.InsertOnSubmit(emp);
                try
                {
                    dbContext.SubmitChanges();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(e);
                }
            }
        }
    }
}