public void SetWeekDateArrays(WeekYear wyFirst, WeekYear wyLast)
{
SqlConnection conn = new SqlConnection(strConn);
SqlDataReader dr = default(SqlDataReader);
string strSQL = null;
int intI = 0;
int intWeekFirst = wyFirst.Week;
int intYearFirst = wyFirst.Year;
string strDateFirst = wyFirst.MondayDate;
int intWeekLast = wyLast.Week;
int intYearLast = wyLast.Year;
string strDateLast = wyLast.MondayDate;
//
strSQL = "SELECT * FROM tblWeeks ";
strSQL += "WHERE WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "' ";
strSQL = strSQL + "ORDER BY WeekNoStartDate";
SqlCommand cmd = new SqlCommand(strSQL, conn);
conn.Open();
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
intWeekIDs[intI] = dr.GetValueOrDefault<int>("ID");
intWeeks[intI] = dr.GetValueOrDefault<int>("WeekNo");
intYears[intI] = dr.GetValueOrDefault<int>("WeekNoYear");
strMondayDates[intI] = dr.GetValueOrDefault<string>("WeekNoStartDate");
strWeekLabel[intI] = dr.GetValueOrDefault<int>("WeekNoMonth").ToString() + "/" + dr.GetValueOrDefault<int>("WeekNoDay").ToString("00");
intI += 1;
}
dr.Close();
dr = null;
conn = null;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
txtDepartmentHeadsRemarks.Visible = true;
txtDivisionHeadsRemarks.Visible = true;
txtDepartmentHeadsRemarks.Enabled = false;
txtDivisionHeadsRemarks.Enabled = false;
txtDepartmentHeadsRemarks.Text = EmployeeJournalApproval.GetRemarks(Convert.ToInt16(Request.QueryString["JournalCode"]), "1");
txtDivisionHeadsRemarks.Text = EmployeeJournalApproval.GetRemarks(Convert.ToInt16(Request.QueryString["JournalCode"]), "2");
lblWeekDates.Text = WeekYear.GetWeekName(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString() + " (" + WeekYear.GetDateStart(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString("MMM dd, yyyy") + " - " + WeekYear.GetDateEnd(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString("MMM dd, yyyy") + ")";
this.MakeCart();
if (EmployeeJournalApproval.GetApprover(Convert.ToInt16(Request.QueryString["JournalCode"]), "1").ToString() == Request.Cookies["Speedo"]["UserName"].ToString())
{
txtDepartmentHeadsRemarks.Enabled = true;
btnApprove.Text = "Approved";
}
if (EmployeeJournalApproval.GetApprover(Convert.ToInt16(Request.QueryString["JournalCode"]), "2").ToString() == Request.Cookies["Speedo"]["UserName"].ToString())
{
txtDivisionHeadsRemarks.Enabled = true;
btnApprove.Text = "Read and Commented";
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
lblWeekDates.Text = WeekYear.GetWeekName(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString() + " (" + WeekYear.GetDateStart(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString("MMM dd, yyyy") + " - " + WeekYear.GetDateEnd(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString("MMM dd, yyyy") + ")";
this.MakeCart();
lblDeptApprover.Text = "(" + EmployeeJournalApproval.GetApprover(Convert.ToInt16(Request.QueryString["JournalCode"]), "1") + ")";
lblDepartmentHeadsRemarks.Text = EmployeeJournalApproval.GetRemarks(Convert.ToInt16(Request.QueryString["JournalCode"]), "1");
lblDivApprover.Text = "(" + EmployeeJournalApproval.GetApprover(Convert.ToInt16(Request.QueryString["JournalCode"]), "2") + ")";
lblDivisionHeadsRemarks.Text = EmployeeJournalApproval.GetRemarks(Convert.ToInt16(Request.QueryString["JournalCode"]), "2");
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
lblWeekDates.Text = WeekYear.GetWeekName(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString() + " (" + WeekYear.GetDateStart(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString("MMM dd, yyyy") + " - " + WeekYear.GetDateEnd(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString("MMM dd, yyyy") + ")";
this.MakeCart();
this.LoadDSLs();
}
}
protected void Page_Load(object sender, EventArgs e)
{
clsSpeedo.Authenticate();
if (!Page.IsPostBack)
{
string strDeptDetails = "", strDivDetails = "";
string strDeptStatus = "", strDivStatus = "";
lblWeekDates.Text = WeekYear.GetWeekName(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString() + " (" + WeekYear.GetDateStart(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString("MMM dd, yyyy") + " - " + WeekYear.GetDateEnd(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString("MMM dd, yyyy") + ")";
this.MakeCart();
this.LoadContent();
//lblDeptApprover.Text = "(" + EmployeeJournalApproval.GetApprover(Convert.ToInt16(Request.QueryString["JournalCode"]), "1") + ")";
try
{
lblDepartmentHeadsRemarks.Text = EmployeeJournalApproval.GetRemarks(Convert.ToInt16(Request.QueryString["JournalCode"]), "1");
if (EmployeeJournalApproval.GetApproverAStatus(Convert.ToInt16(Request.QueryString["JournalCode"]), "1") == "F")
{
strDeptStatus = "For Review";
}
else if (EmployeeJournalApproval.GetApproverAStatus(Convert.ToInt16(Request.QueryString["JournalCode"]), "1") == "A")
{
strDeptStatus = "Reviewed";
}
else if (EmployeeJournalApproval.GetApproverAStatus(Convert.ToInt16(Request.QueryString["JournalCode"]), "1") == "F")
{
strDeptStatus = "Disapproved";
}
strDeptDetails = "Reviewer: " + EmployeeJournalApproval.GetApprover(Convert.ToInt16(Request.QueryString["JournalCode"]), "1") + "<br />" +
"Status: " + strDeptStatus + "<br />" +
"Remarks: <br />" + EmployeeJournalApproval.GetRemarks(Convert.ToInt16(Request.QueryString["JournalCode"]), "1");
lblDepartmentHeadsRemarks.Text = strDeptDetails;
}
catch { }
//if (EmployeeJournalApproval.GetApproverAStatus(Convert.ToInt16(Request.QueryString["JournalCode"]), "2") == "F")
//{
// strDivStatus = "For Approval";
//}
//else if (EmployeeJournalApproval.GetApproverAStatus(Convert.ToInt16(Request.QueryString["JournalCode"]), "2") == "A")
//{
// strDivStatus = "Approved";
//}
//else if (EmployeeJournalApproval.GetApproverAStatus(Convert.ToInt16(Request.QueryString["JournalCode"]), "2") == "F")
//{
// strDivStatus = "Disapproved";
//}
//strDivDetails = "Approver: " + EmployeeJournalApproval.GetApprover(Convert.ToInt16(Request.QueryString["JournalCode"]), "2") + "<br />" +
// "Status: " + strDivStatus + "<br />" +
// "Remarks: <br />" + EmployeeJournalApproval.GetRemarks(Convert.ToInt16(Request.QueryString["JournalCode"]), "2");
//lblDivisionHeadsRemarks.Text = strDivDetails;
}
}
protected void ddlJournalYear_SelectedIndexChanged(object sender, EventArgs e)
{
ddlJournalDates.DataSource = EmployeeJournal.GetDSLJournalDatesALL(ddlJournalYear.SelectedValue.ToString());
ddlJournalDates.DataValueField = "pvalue";
ddlJournalDates.DataTextField = "ptext";
ddlJournalDates.DataBind();
try
{
ddlJournalDates.SelectedValue = WeekYear.GetActiveWeekCode();
}
catch { }
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
lblWeekDates.Text = WeekYear.GetWeekName(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString() + " (" + WeekYear.GetDateStart(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString("MMM dd, yyyy") + " - " + WeekYear.GetDateEnd(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString("MMM dd, yyyy") + ")";
this.MakeCart();
lblDeptApprover.Text = "(" + EmployeeJournalApproval.GetApprover(Convert.ToInt16(Request.QueryString["JournalCode"]), "1") + ")";
lblDeptRemarks.Text = EmployeeJournalApproval.GetRemarks(Convert.ToInt16(Request.QueryString["JournalCode"]), "1");
lblDivApprover.Text = "(" + EmployeeJournalApproval.GetApprover(Convert.ToInt16(Request.QueryString["JournalCode"]), "2") + ")";
lblDivRemarks.Text = EmployeeJournalApproval.GetRemarks(Convert.ToInt16(Request.QueryString["JournalCode"]), "2");
ViewState["JournalCode"] = Request.QueryString["JournalCode"].ToString();
}
}
protected void MakeCart()
{
DataTable tblCart = new DataTable("Cart");
tblCart.Columns.Add("WeekCode", System.Type.GetType("System.String"));
tblCart.Columns.Add("WeekName", System.Type.GetType("System.String"));
tblCart.Columns.Add("DateStart", System.Type.GetType("System.DateTime"));
tblCart.Columns.Add("DateEnd", System.Type.GetType("System.DateTime"));
//tblCart = WeekYear.GetDSGCart(Convert.ToInt16(FiscalYear.GetActiveYearCode()));
tblCart = WeekYear.GetDSGCart(Convert.ToInt16(ddlFiscalYear.SelectedValue));
ViewState["Cart"] = tblCart;
//LoadGrid();
}
public IHttpActionResult GetWeekHours(int week, int year, bool next)
{
var dto = new WeekYear
{
Week = week,
Year = year
};
WeekYear weekDto = next ? Core.Models.CourseManager.GetNextWeekYear(dto) : Core.Models.CourseManager.GetPrevWeekYear(dto);
var manager = new Core.Models.CourseManager(_unitOfWork, weekDto);
var view = _mapper.Map <Core.Models.CourseManager, CourseManagerWeekView>(manager);
return(Ok(view));
}
protected void btnSave_Click(object sender, EventArgs e)
{
using (WeekYear objWeekYear = new WeekYear())
{
objWeekYear.WeekName = txtWeekName.Text;
objWeekYear.WeekNumber = Convert.ToInt16(ddlWeekNunmber.SelectedValue.ToString());
objWeekYear.DateStart = Convert.ToDateTime(dtpFrom.SelectedDate.ToString());
objWeekYear.DateTo = Convert.ToDateTime(dtpTo.SelectedDate.ToString());
objWeekYear.FiscalYearCode = Convert.ToInt16(ddlFiscalYear.SelectedValue);
objWeekYear.CreatedBy = Request.Cookies["Speedo"]["UserName"].ToString();
objWeekYear.ModifiedBy = Request.Cookies["Speedo"]["UserName"].ToString();
if (objWeekYear.Insert() > 0)
{
Response.Redirect("JournalWeeks.aspx");
}
}
}
public void LoadDetails()
{
ddlJournalYear.DataSource = EmployeeJournal.GetDSLJournalYearsALL();
ddlJournalYear.DataValueField = "pvalue";
ddlJournalYear.DataTextField = "ptext";
ddlJournalYear.DataBind();
ddlJournalDates.DataSource = EmployeeJournal.GetDSLJournalDatesALL(ddlJournalYear.SelectedValue.ToString());
ddlJournalDates.DataValueField = "pvalue";
ddlJournalDates.DataTextField = "ptext";
ddlJournalDates.DataBind();
try
{
ddlJournalDates.SelectedValue = WeekYear.GetActiveWeekCode();
}
catch { }
}
protected void Page_Load(object sender, EventArgs e)
{
//DateTime firstDayOfWeek = clsDateTime.FirstDateOfWeek(DateTime.Now.Year, _intThisWeekCode, CultureInfo.CurrentCulture);
//lblWeekDates.Text = firstDayOfWeek.ToString("MM/dd/yyyy") + " - " + firstDayOfWeek.AddDays(6).ToString("MM/dd/yyyy");
lblWeekDates.Text = WeekYear.GetWeekName(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString() + " (" + WeekYear.GetDateStart(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString("MMM dd, yyyy") + " - " + WeekYear.GetDateEnd(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString("MMM dd, yyyy") + ")";
//if (EmployeeJournal.HasExistingJournal(Request.Cookies["Speedo"]["UserName"].ToString(), _intThisWeekCode))
//{
// using (EmployeeJournal objEmployeeJouurnal = new EmployeeJournal())
// {
// objEmployeeJouurnal.WeekCode = _intThisWeekCode;
// objEmployeeJouurnal.WeekYear = DateTime.Now.Year.ToString();
// objEmployeeJouurnal.CreatedBy = Request.Cookies["Speedo"]["UserName"].ToString();
// objEmployeeJouurnal.Fill();
// ckeContents.Text = objEmployeeJouurnal.Contents;
// }
//}
}
protected void btnGoToJournal_Click(object sender, EventArgs e)
{
string strLink = "";
using (EmployeeJournal objEmployeeJournal = new EmployeeJournal())
{
if (EmployeeJournal.HasExistingJournal(Request.Cookies["Speedo"]["UserName"].ToString(), Convert.ToInt16(WeekYear.GetActiveWeekCode())))
{
if (EmployeeJournal.GetJournalStatus(Convert.ToInt16(EmployeeJournal.GetLastJournalCode(Convert.ToInt16(WeekYear.GetActiveWeekCode()), Request.Cookies["Speedo"]["UserName"].ToString()))).ToString() == "S" && EmployeeJournal.GetLockStatus(Convert.ToInt16(EmployeeJournal.GetLastJournalCode(Convert.ToInt16(WeekYear.GetActiveWeekCode()), Request.Cookies["Speedo"]["UserName"].ToString()))).ToString() == "0")
{
strLink = "JournalEncoding";
Response.Redirect("JournalEncoding.aspx?JournalCode=" + EmployeeJournal.GetLastJournalCode(Convert.ToInt16(WeekYear.GetActiveWeekCode()), Request.Cookies["Speedo"]["UserName"].ToString()).ToString());
}
else if (EmployeeJournal.GetJournalStatus(Convert.ToInt16(EmployeeJournal.GetLastJournalCode(Convert.ToInt16(WeekYear.GetActiveWeekCode()), Request.Cookies["Speedo"]["UserName"].ToString()))).ToString() == "F" && EmployeeJournal.GetLockStatus(Convert.ToInt16(EmployeeJournal.GetLastJournalCode(Convert.ToInt16(WeekYear.GetActiveWeekCode()), Request.Cookies["Speedo"]["UserName"].ToString()))).ToString() == "0")
{
strLink = "JournalEncodingM";
Response.Redirect("JournalEncodingM.aspx?JournalCode=" + EmployeeJournal.GetLastJournalCode(Convert.ToInt16(WeekYear.GetActiveWeekCode()), Request.Cookies["Speedo"]["UserName"].ToString()).ToString());
}
else
{
strLink = "JournalViewer";
Response.Redirect("JournalViewer.aspx?JournalCode=" + EmployeeJournal.GetLastJournalCode(Convert.ToInt16(WeekYear.GetActiveWeekCode()), Request.Cookies["Speedo"]["UserName"].ToString()).ToString());
}
}
else
{
objEmployeeJournal.WeekCode = Convert.ToInt16(WeekYear.GetActiveWeekCode());
objEmployeeJournal.Enabled = "1";
objEmployeeJournal.CreatedBy = Request.Cookies["Speedo"]["UserName"].ToString();
objEmployeeJournal.CreatedOn = DateTime.Now;
objEmployeeJournal.ModifiedBy = Request.Cookies["Speedo"]["UserName"].ToString();
objEmployeeJournal.ModifiedOn = DateTime.Now;
if (objEmployeeJournal.Insert() > 0)
{
Response.Redirect("JournalEncoding.aspx");
}
}
}
}
public void LoadDueValidation()
{
//Response.Write(WeekYear.GetDateDue(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"]))).AddHours(9).ToString());
//Response.Write("<br> " + DateTime.Now.ToString());
//Response.Write(Convert.ToDateTime("11/12/2015 4:50:17 PM").ToString());
if (WeekYear.GetDateDue(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"]))).AddHours(9) < DateTime.Now)
//if (Convert.ToDateTime("11/12/2015 4:57:00 PM") < DateTime.Now)
{
if (JournalExceptions.CountIfExist(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])), Request.Cookies["Speedo"]["UserName"].ToString()) > 0)
{
btnDraft.Enabled = true;
btnFinalize.Enabled = true;
btnPreview.Enabled = true;
}
else
{
btnDraft.Enabled = false;
btnFinalize.Enabled = false;
btnPreview.Enabled = false;
}
}
}
public void LoadDetails()
{
ddlJournalYear.DataSource = EmployeeJournal.GetDSLJournalYearsALL();
ddlJournalYear.DataValueField = "pvalue";
ddlJournalYear.DataTextField = "ptext";
ddlJournalYear.DataBind();
ddlJournalDates.DataSource = EmployeeJournal.GetDSLJournalDates(ddlJournalYear.SelectedValue.ToString());
ddlJournalDates.DataValueField = "pvalue";
ddlJournalDates.DataTextField = "ptext";
ddlJournalDates.DataBind();
ddlJournalDates.SelectedValue = WeekYear.GetActiveWeekCode();
ddlDivision.DataSource = clsDivision.GetDdlDsBasedOnApproverModule(Request.Cookies["Speedo"]["UserName"].ToString());
ddlDivision.DataValueField = "pvalue";
ddlDivision.DataTextField = "ptext";
ddlDivision.DataBind();
ddlDepartment.DataSource = clsDepartment.GetDdlDsBasedOnModuleApprover(ddlDivision.SelectedValue.ToString(), Request.Cookies["Speedo"]["UserName"].ToString());
ddlDepartment.DataValueField = "pvalue";
ddlDepartment.DataTextField = "ptext";
ddlDepartment.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
clsSpeedo.Authenticate();
this.LoadDueValidation();
if (!Page.IsPostBack)
{
//lblWeekDates.Text = WeekYear.GetWeekName(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString() + " (" + WeekYear.GetDateStart(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString("MMM dd, yyyy") + " - " + WeekYear.GetDateEnd(Convert.ToInt16(WeekYear.GetActiveWeekCode())).ToString("MMM dd, yyyy") + ")";
lblWeekDates.Text = WeekYear.GetWeekName(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString() + " (" + WeekYear.GetDateStart(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString("MMM dd, yyyy") + " - " + WeekYear.GetDateEnd(Convert.ToInt16(EmployeeJournal.GetWeekCode(Convert.ToInt16(Request.QueryString["JournalCode"])))).ToString("MMM dd, yyyy") + ")";
this.MakeCart();
this.LoadDSLs();
this.LoadContents();
if (EmployeeReviewer.GetReviewer(Request.Cookies["Speedo"]["UserName"].ToString()).Length > 0)
{
lblReviewer.ForeColor = Color.Black;
lblReviewer.Text = EmployeeReviewer.GetReviewer(Request.Cookies["Speedo"]["UserName"].ToString());
}
else
{
lblReviewer.ForeColor = Color.Red;
lblReviewer.Text = "No assigned reviewer.";
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
clsSpeedo.Authenticate();
//int weeks = 0;
//DateTime firstdayback = Convert.ToDateTime("2015-04-01");
////weeks = DateTime.Now.Subtract(firstdayback).Days / 7;
//weeks = (Convert.ToDateTime("2015-04-03").Subtract(firstdayback).Days / 7)+1;
//Response.Write(weeks);
if (!Page.IsPostBack)
{
ddlFiscalYear.DataSource = WeekYear.GetDSLFiscalYearsALL();
ddlFiscalYear.DataValueField = "pvalue";
ddlFiscalYear.DataTextField = "ptext";
ddlFiscalYear.DataBind();
try
{
MakeCart();
}
catch { }
}
LoadGrid();
}
private DataSet GetOverallWorkLoadByHours_Needed(WeekYear wyFirst, WeekYear wyLast)
{
string strSQL = null;
SqlDataAdapter da = default(SqlDataAdapter);
DataSet ds = default(DataSet);
//
int intWeekFirst = wyFirst.Week;
int intYearFirst = wyFirst.Year;
string strDateFirst = wyFirst.MondayDate;
int intWeekLast = wyLast.Week;
int intYearLast = wyLast.Year;
string strDateLast = wyLast.MondayDate;
int intK = 0;
//
strSQL = "SELECT ID";
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
strSQL += ", (SELECT SUM([Hours]) ";
strSQL += "FROM v_EmployeeScheduleByWeek_ActiveProjects s ";
strSQL += "WHERE (s.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "AND (s.WeekNo = " + WeekDate.Weeks[intK] + ")) AS [" +WeekDate.WeekLabels[intK] + "] ";
}
strSQL += "FROM tblTemp";
//
da = new SqlDataAdapter(strSQL, strConn);
ds = new DataSet();
da.Fill(ds);
//
return ds;
}
private DataSet GetOverallWorkLoadByHours_Available_OLD(WeekYear wyFirst, WeekYear wyLast)
{
string strSQL = null;
SqlDataAdapter da = default(SqlDataAdapter);
DataSet ds = default(DataSet);
//
int intWeekFirst = wyFirst.Week;
int intYearFirst = wyFirst.Year;
string strDateFirst = wyFirst.MondayDate;
int intWeekLast = wyLast.Week;
int intYearLast = wyLast.Year;
string strDateLast = wyLast.MondayDate;
int intK = 0;
// Set week labels
//
strSQL = "SELECT ID";
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
strSQL += ", (SELECT COUNT(DISTINCT EmployeeID) ";
strSQL += "FROM v_EmployeeScheduleByWeek s ";
strSQL += "WHERE (EmployeeName NOT LIKE '-%') ";
strSQL += "AND (s.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "AND (s.WeekNo = " + WeekDate.Weeks[intK] + ")) AS [" + WeekDate.WeekLabels[intK] + "] ";
}
strSQL += "FROM tblTemp";
//
da = new SqlDataAdapter(strSQL, strConn);
ds = new DataSet();
da.Fill(ds);
//
return ds;
}
private void PopulateDataset(WeekYear wyFirst, WeekYear wyLast, string strFilterField, string strFilterText)
{
string strSQL = null;
string strFilter = "";
//
int intWeekFirst = wyFirst.Week;
int intYearFirst = wyFirst.Year;
string strDateFirst = wyFirst.MondayDate;
int intWeekLast = wyLast.Week;
int intYearLast = wyLast.Year;
string strDateLast = wyLast.MondayDate;
int intI = 0;
int intID = 0;
//
strSQL = "SELECT ([ID]) AS ProjectID, ProjectNo, ";
strSQL += "UPPER(CASE WHEN (LEN(ProjectName) > 30) THEN LEFT(ProjectName, 30) + '...' ELSE ProjectName END) AS ProjectName, PICCode, PM1Code ";
for (intI = 0; intI <= clsSchedule.cWeekSpan; intI++)
{
strSQL += ", (SELECT SUM ([Hours]) ";
strSQL += "FROM v_EmployeeScheduleByWeek s ";
strSQL += "WHERE s.ProjectID = tblProjects.ID ";
strSQL += "AND (s.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "AND (s.WeekNo = " + WeekDate.WeekIDs[intI] + ")) AS [Week" + (intI + 1) + "] ";
}
strSQL += "FROM tblProjects ";
if (!string.IsNullOrEmpty(strFilterText))
{
strFilter = GetFilter(strFilterField, strFilterText);
strSQL += "WHERE Active = 1 AND ";
strSQL += strFilter + " ";
}
else
{
strSQL += "WHERE Active = 1 ";
}
strSQL += "ORDER BY ProjectNo";
//
daProjects = new SqlDataAdapter(strSQL, strConn);
//
daProjects.Fill(dsProjects, "Projects");
//
}
private void ConstructTable(WeekYear wyFirst, WeekYear wyLast)
{
string strSQL = null;
System.Data.SqlClient.SqlConnection connSQL = default(System.Data.SqlClient.SqlConnection);
SqlDataAdapter da = default(SqlDataAdapter);
DataSet dsEngineers = default(DataSet);
DataTable dtEngineers = default(DataTable);
DataSet dsProjects = default(DataSet);
DataTable dtProjects = default(DataTable);
DataRow dtRow = default(DataRow);
int intEmployeeID = 0;
int intProjID = 0;
//
int intWeekFirst = wyFirst.Week;
int intYearFirst = wyFirst.Year;
string strDateFirst = wyFirst.MondayDate;
int intWeekLast = wyLast.Week;
int intYearLast = wyLast.Year;
string strDateLast = wyLast.MondayDate;
int intI = 0;
int intJ = 0;
int intK = 0;
int intID = 0;
int intCol = 0;
int intColChild = 0;
//
TableRow rEng = default(TableRow);
TableCell cEng = default(TableCell);
//
Table t = default(Table);
TableRow r = default(TableRow);
TableCell c = default(TableCell);
const int cWidth = 975;
//
string strHeaderStyle = null;
string strStyle = null;
//
decimal decHours = default(decimal);
var hours = new List<decimal>();
string strHours = null;
object val = null;
//
string strCurWeekLabel = schedule.GetCurrentWeekLabel();
bool blnCurWeek = false;
//
// Reset ArrayList
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
hours.Add(0);
}
// Set week labels
SetWeekDateArrays(wyFirst, wyLast);
// Set table width
this.tblEngineers.Width = Unit.Pixel(cWidth);
// Add row to container table
rEng = new TableRow();
cEng = new TableCell();
//
//strSQL = "SELECT TOP 20 ([ID]) AS ProjectID, ProjectNo, "
strSQL = "SELECT ([ID]) AS ProjectID, ProjectNo, ";
strSQL += "UPPER(CASE WHEN (LEN(ProjectName) > 20) THEN LEFT(ProjectName, 20) + '...' ELSE ProjectName END) AS ProjectName, PICCode, PM1Code";
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
strSQL += ", (SELECT SUM([Hours]) ";
strSQL += "FROM v_EmployeeScheduleByWeek s ";
strSQL += "WHERE s.ProjectID = tblProjects.ID ";
strSQL += "AND (s.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "AND (s.WeekNo = " + intWeeks[intK] + ")) AS [" + strWeekLabel[intK] + "] ";
}
strSQL += "FROM tblProjects ";
strSQL += "WHERE Active=1 ";
strSQL += "ORDER BY ProjectNo";
//
da = new SqlDataAdapter(strSQL, strConn);
dsProjects = new DataSet();
da.Fill(dsProjects);
dtProjects = dsProjects.Tables[0];
if (dtProjects.Rows.Count > 0)
{
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
t.Width = Unit.Pixel(cWidth);
// Column Headers
r = new TableRow();
r.Height = Unit.Pixel(13);
//
// Job #
c = new TableCell();
c.Width = Unit.Pixel(90);
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "HeaderStyle";
c.Text = "Job #";
r.Cells.Add(c);
// Project
c = new TableCell();
c.Width = Unit.Pixel(210);
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "HeaderStyle";
c.Text = "Project";
r.Cells.Add(c);
// PIC
c = new TableCell();
c.Width = Unit.Pixel(100);
c.CssClass = "HeaderStyle";
c.Text = "PIC";
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
// PM1
c = new TableCell();
c.Width = Unit.Pixel(100);
c.CssClass = "HeaderStyle";
c.Text = "PM";
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
// Week Hours Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.Width = Unit.Pixel(40);
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "WeekHeaderStyle";
if (dtProjects.Columns[intK + 5].Caption == strCurWeekLabel)
{
c.CssClass = "ChildCurWeekHeaderStyle";
}
c.Text = strWeekLabel[intK];
r.Cells.Add(c);
}
//
t.Rows.Add(r);
cEng.Controls.Add(t);
// Now grab data and populate table
for (intI = 0; intI <= dtProjects.Rows.Count - 1; intI++)
{
// Get ProjectID
intProjID = dtProjects.Rows[intI]["ProjectID"].GetValueOrDefault<int>();
//
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
t.Width = Unit.Pixel(cWidth);
//
//If intI Mod 2 = 0 Then
// strStyle = "RowStyle"
//Else
// strStyle = "AlternateRowStyle"
//End If
//
strStyle = "GrayRowStyle";
//
r = new TableRow();
r.Height = Unit.Pixel(13);
r.CssClass = strStyle;
// Job #
c = new TableCell();
c.Width = Unit.Pixel(90);
c.CssClass = strStyle;
c.Text = dtProjects.Rows[intI]["ProjectNo"].GetValueOrDefault<string>();
r.Cells.Add(c);
// Project
c = new TableCell();
c.Width = Unit.Pixel(210);
c.CssClass = strStyle;
c.Text = dtProjects.Rows[intI]["ProjectName"].GetValueOrDefault<string>();
r.Cells.Add(c);
// PIC
c = new TableCell();
c.Width = Unit.Pixel(100);
c.CssClass = strStyle;
c.Text = dtProjects.Rows[intI]["PICCode"].GetValueOrDefault<string>();
r.Cells.Add(c);
// PM1
c = new TableCell();
c.Width = Unit.Pixel(100);
c.CssClass = strStyle;
c.Text = dtProjects.Rows[intI]["PM1Code"].GetValueOrDefault<string>();
r.Cells.Add(c);
// Week Hours Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.Width = Unit.Pixel(40);
decHours = dtProjects.Rows[intI][intK + 5].GetValueOrDefault<decimal>();
hours[intK] += decHours; // alHours[intK] += decHours;
strHours = decHours.ToString(); // basToolbox.Nz(dtProjects.Rows[intI][intK + 5], " ");
c.Text = strHours;
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = strStyle;
r.Cells.Add(c);
}
//
t.Rows.Add(r);
cEng.Controls.Add(t);
//
// Now add Child Records
strSQL = "SELECT TOP 100 PERCENT (0) AS ID, e.ProjectID, e.EmployeeID, ";
strSQL += "e.EmployeeName, ";
strSQL += "('Engineer') AS Role";
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
strSQL += ", SUM(CASE WeekNo WHEN " + intWeeks[intK] + " THEN Hours ELSE 0 END) AS [" + strWeekLabel[intK] + "] ";
}
strSQL += ", ([EmployeeID]) AS IDField ";
strSQL += "FROM v_EmployeeScheduleByWeek e ";
strSQL += "WHERE e.ProjectID = " + intProjID + " ";
strSQL += "AND (e.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "GROUP BY e.ProjectID, ";
strSQL += "e.EmployeeID, e.EmployeeName ";
strSQL += "ORDER BY e.EmployeeName";
da = new SqlDataAdapter(strSQL, strConn);
dsEngineers = new DataSet();
da.Fill(dsEngineers);
dtEngineers = dsEngineers.Tables[0];
if (dtEngineers.Rows.Count > 0)
{
//
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
t.Width = Unit.Pixel(cWidth);
// Now grab data and populate table
for (intJ = 0; intJ <= dtEngineers.Rows.Count - 1; intJ++)
{
intEmployeeID = dtEngineers.Rows[intJ]["EmployeeID"].GetValueOrDefault<int>();
//
if (intJ % 2 == 0)
{
strStyle = "RowStyle";
}
else
{
strStyle = "AlternateRowStyle";
}
//
r = new TableRow();
r.Height = Unit.Pixel(13);
// Blank Column
c = new TableCell();
c.Width = Unit.Pixel(25);
c.CssClass = "ChildHeaderStyle";
c.Text = " ";
r.Cells.Add(c);
// Employee Name
c = new TableCell();
c.Width = Unit.Pixel(269);
c.CssClass = strStyle;
c.Text = dtEngineers.Rows[intJ]["EmployeeName"].GetValueOrDefault<string>();
r.Cells.Add(c);
// Type
c = new TableCell();
c.Width = Unit.Pixel(205);
c.CssClass = strStyle;
c.Text = dtEngineers.Rows[intJ]["Role"].GetValueOrDefault<string>();
r.Cells.Add(c);
// Week Hours Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.Width = Unit.Pixel(40);
decHours = dtEngineers.Rows[intJ][intK + 5].GetValueOrDefault<decimal>();
strHours = decHours.ToString();
val = schedule.GetEmployeeWeekTotal(intEmployeeID, intWeekIDs[intK]);
strStyle = this.schedule.GetWeekHoursStyle(val.GetValueOrDefault<int>()); // schedule.GetWeekHoursStyle(val);
c.Text = strHours;
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = strStyle;
r.Cells.Add(c);
}
//
t.Rows.Add(r);
}
cEng.Controls.Add(t);
//
}
// Add Blank Row
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
t.Width = Unit.Pixel(cWidth);
//
r = new TableRow();
r.Height = Unit.Pixel(10);
// Blank Column
c = new TableCell();
c.Text = "<img src='images/spacer.gif' height='10' />";
c.Width = Unit.Pixel(cWidth);
r.Cells.Add(c);
//
t.Rows.Add(r);
//
cEng.Controls.Add(t);
}
cEng.Controls.Add(t);
// Add Footer
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
t.Width = Unit.Pixel(cWidth);
// Footer Row
r = new TableRow();
r.Height = Unit.Pixel(13);
//
// Blank Column
c = new TableCell();
c.CssClass = "HeaderStyle";
c.Width = Unit.Pixel(575);
c.Text = " ";
r.Cells.Add(c);
// Week Hours Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.Width = Unit.Pixel(40);
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "HeaderStyle";
c.Text = hours[intK].ToString(); // alHours[intK];
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
//
t.Rows.Add(r);
cEng.Controls.Add(t);
}
else
{
// No Data Display Text
}
rEng.Cells.Add(cEng);
this.tblEngineers.Rows.Add(rEng);
}
private void PopulateDataset(int intProjID, WeekYear wyFirst, WeekYear wyLast, string strSortColumn = "", string strSortType = "")
{
//
string strSQL = null;
int intWeekFirst = wyFirst.Week;
int intYearFirst = wyFirst.Year;
string strDateFirst = wyFirst.MondayDate;
int intWeekLast = wyLast.Week;
int intYearLast = wyLast.Year;
string strDateLast = wyLast.MondayDate;
int intI = 0;
int intID = 0;
// Set week labels
SetWeekDateArrays(wyFirst, wyLast);
//
conn = new SqlConnection(strConn);
//
// Add schedule for PM1
strSQL = "SELECT TOP 100 PERCENT (0) AS ID, e.ProjectID, e.EmployeeID, ";
strSQL += "e.EmployeeName, ";
strSQL += "('Project Manager') AS Role";
for (intI = 0; intI <= clsSchedule.cWeekSpan; intI++)
{
//
strSQL += ", (SELECT SUM ([Hours])";
strSQL += "FROM v_EmployeeScheduleByWeek s ";
strSQL += "WHERE s.ProjectID = e.ProjectID ";
strSQL += "AND s.EmployeeID = e.EmployeeID ";
strSQL += "AND (s.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "AND (s.WeekNo = " + intWeeks[intI] + ")) AS [Week" + (intI + 1) + "] ";
}
strSQL += ", ([EmployeeID]) AS IDField ";
strSQL += "FROM v_EmployeeScheduleByWeek e ";
strSQL += "WHERE e.ProjectID = " + intProjID + " ";
strSQL += "AND e.PM1 = e.EmployeeID ";
strSQL += "GROUP BY e.ProjectID, ";
strSQL += "e.EmployeeID, e.EmployeeName ";
strSQL += "ORDER BY e.EmployeeName";
//
daSchedule = new SqlDataAdapter(strSQL, conn);
daSchedule.Fill(dsProjects, "Schedule");
// Add for rest of employees
strSQL = "SELECT TOP 100 PERCENT (0) AS ID, e.ProjectID, e.EmployeeID, ";
strSQL += "e.EmployeeName, ";
strSQL += "('Engineer') AS Role";
for (intI = 0; intI <= clsSchedule.cWeekSpan; intI++)
{
//
strSQL += ", (SELECT SUM ([Hours])";
strSQL += "FROM v_EmployeeScheduleByWeek s ";
strSQL += "WHERE s.ProjectID = e.ProjectID ";
strSQL += "AND s.EmployeeID = e.EmployeeID ";
strSQL += "AND (s.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "AND (s.WeekNo = " + intWeeks[intI] + ")) AS [Week" + (intI + 1) + "] ";
}
strSQL += ", ([EmployeeID]) AS IDField ";
strSQL += "FROM v_EmployeeScheduleByWeek e ";
strSQL += "WHERE e.ProjectID = " + intProjID + " ";
//strSQL &= "AND e.PIC <> e.EmployeeID "
strSQL += "AND e.PM1 <> e.EmployeeID ";
strSQL += "GROUP BY e.ProjectID, ";
strSQL += "e.EmployeeID, e.EmployeeName ";
strSQL += "ORDER BY e.EmployeeName";
//
daSchedule = new SqlDataAdapter(strSQL, conn);
daSchedule.Fill(dsProjects, "Schedule");
// Auto increment ID field
for (intI = 0; intI <= dsProjects.Tables["Schedule"].Rows.Count - 1; intI++)
{
intID += 1;
dsProjects.Tables["Schedule"].Rows[intI]["ID"] = intID;
}
//
if (!string.IsNullOrEmpty(strSortColumn))
{
dtSchedule.DefaultView.Sort = strSortColumn + strSortType;
}
//
this.uwgProjects.DataSource = dsProjects;
this.uwgProjects.DataMember = dsProjects.Tables["Schedule"].TableName;
}
//
public WeekYear GetWeekYear_Old(DateTime dtmDate)
{
WeekYear wyCurrent = new WeekYear();
int intCurrWeek = DateAndTime.DatePart(DateInterval.WeekOfYear, dtmDate);
int intCurrYear = DateAndTime.DatePart(DateInterval.Year, dtmDate);
string strWeekMondayDate = GetWeekMondayDate(intCurrWeek, intCurrYear);
//
wyCurrent.Week = intCurrWeek;
wyCurrent.Year = intCurrYear;
wyCurrent.MondayDate = DateTime.Parse(strWeekMondayDate);
//
return wyCurrent;
}
public WeekYear GetWeekYearLast(WeekYear wyFirst)
{
int intWeek = wyFirst.Week;
int intYear = wyFirst.Year;
int intWeekLast;
int intYearLast;
WeekYear wyLast = new WeekYear();
int intNoOfWeeks = GetNoOfWeeks(intYear);
//
intWeekLast = (intWeek + cWeekSpan);
intYearLast = intYear;
// If intWeekLast > 52 Then
// intWeekLast = intWeekLast - 52
// intYearLast = intYear + 1
// End If
if ((intWeekLast > intNoOfWeeks))
{
intWeekLast = (intWeekLast - intNoOfWeeks);
intYearLast = (intYear + 1);
}
//
wyLast.Week = intWeekLast;
wyLast.Year = intYearLast;
wyLast.MondayDate = DateTime.Parse(GetWeekMondayDate(intWeekLast, intYearLast));
//
return wyLast;
}
private List<decimal> GetEmployeeWorkLoadByPeople_Under(WeekYear wyFirst, WeekYear wyLast)
{
string strSQL = null;
SqlDataAdapter da = default(SqlDataAdapter);
DataSet ds = default(DataSet);
var al = new List<Decimal>();
//
int intWeekFirst = wyFirst.Week;
int intYearFirst = wyFirst.Year;
string strDateFirst = wyFirst.MondayDate;
int intWeekLast = wyLast.Week;
int intYearLast = wyLast.Year;
string strDateLast = wyLast.MondayDate;
int intK = 0;
// Set week labels
//
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
//
strSQL = "SELECT DISTINCT EmployeeID ";
strSQL += "FROM v_EmployeeScheduleByWeek_ActiveProjects s ";
strSQL += "WHERE (EmployeeName NOT LIKE '-%') ";
strSQL += "AND (s.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "AND (s.WeekNo = " + WeekDate.Weeks[intK] + ") ";
strSQL += "GROUP BY EmployeeID ";
strSQL += "HAVING (SUM(Hours) < 40)";
//
da = new SqlDataAdapter(strSQL, strConn);
ds = new DataSet();
da.Fill(ds);
//
al.Add(ds.Tables[0].Rows.Count);
//
da.Dispose();
ds.Dispose();
}
//
return al;
}
private void ConstructTableByPeople(WeekYear wyFirst, WeekYear wyLast)
{
DataSet ds = default(DataSet);
DataTable dt = default(DataTable);
//
TableRow rEng = default(TableRow);
TableCell cEng = default(TableCell);
//
Table t = default(Table);
TableRow r = default(TableRow);
TableCell c = default(TableCell);
//
var alNeeded = new List<Decimal>();
var alAvailable = new List<Decimal>();
var alSurplusShortage = new List<Decimal>();
var alIndex = new List<Decimal>();
var alUnder = new List<Decimal>();
var alScheduled = new List<Decimal>();
var al10To25 = new List<Decimal>();
var alOver25 = new List<Decimal>();
var alUnAssignedEng = new List<Decimal>();
int intK = 0;
decimal decAvailable = 0;
System.DateTime dtmStart = DateTime.Parse(wyFirst.MondayDate);
System.DateTime dtmEnd = default(System.DateTime);
//
string strHeaderStyle = null;
string strStyle = null;
//
decimal decHours = default(decimal);
string strHours = null;
object val = null;
//
string strCurWeekLabel = schedule.GetCurrentWeekLabel();
bool blnCurWeek = false;
// Reset all array lists
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
alNeeded.Add(0);
alAvailable.Add(0);
alSurplusShortage.Add(0);
alUnAssignedEng.Add(0);
}
// Overall WorkLoad Needed
ds = GetOverallWorkLoadByHours_Needed(wyFirst, wyLast);
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
alNeeded[intK] = dt.Rows[0][intK + 1].GetValueOrDefault<decimal>() / 40; // (basToolbox.Nz(dt.Rows[0][intK + 1], 0)) / 40;
}
}
// Overall WorkLoad Available
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
dtmEnd = dtmStart.AddDays(6); // DateAdd(DateInterval.Day, 6, dtmStart);
alAvailable[intK] = GetOverallWorkLoadByHours_Available(dtmStart, dtmEnd) / 40;
dtmStart = dtmStart.AddDays(7); // DateAdd(DateInterval.Day, 7, dtmStart);
}
// Surplus/Shortage Hours
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
alSurplusShortage[intK] = (alNeeded[intK].GetValueOrDefault<decimal>() - alAvailable[intK].GetValueOrDefault<decimal>());
}
// Surplus/Shortage Index
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
alIndex.Add(Math.Round(alNeeded[intK].GetValueOrDefault<decimal>() / alAvailable[intK].GetValueOrDefault<decimal>(),2));
}
// Employee WorkLoad Under
alUnder = GetEmployeeWorkLoadByPeople_Under(wyFirst, wyLast);
// Employee WorkLoad Scheduled
alScheduled = GetEmployeeWorkLoadByPeople_Scheduled(wyFirst, wyLast);
// Employee WorkLoad 10 To 25 %
al10To25 = GetEmployeeWorkLoadByPeople_10To25(wyFirst, wyLast);
// Employee WorkLoad Over 25 %
alOver25 = GetEmployeeWorkLoadByPeople_Over25(wyFirst, wyLast);
//
// Unassigned Engineer By Hours
ds = GetUnAssignedEngineer_ByHours(wyFirst, wyLast);
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
alUnAssignedEng[intK] = dt.Rows[0][intK + 1].GetValueOrDefault<decimal>() / 40; // (basToolbox.Nz(dt.Rows[0][intK + 1], 0)) / 40;
}
}
// Set table width
this.tblEngineers.Width = Unit.Pixel(855);
// Add row to container table
rEng = new TableRow();
cEng = new TableCell();
//
// New Table Inside
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
//t.Width = Unit.Pixel(705)
// Header Row
r = new TableRow();
r.Height = Unit.Pixel(13);
//
// Blank Column
c = new TableCell();
c.Width = Unit.Pixel(200);
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "ChildHeaderStyle";
c.Text = " ";
r.Cells.Add(c);
// Blank Column
c = new TableCell();
c.Width = Unit.Pixel(155);
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "ChildHeaderStyle";
c.Text = " ";
r.Cells.Add(c);
// Week Hours Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.Width = Unit.Pixel(50);
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "ChildHeaderStyle";
if (WeekDate.WeekLabels[intK] == strCurWeekLabel)
{
c.CssClass = "ChildCurWeekHeaderStyle";
}
c.Text = WeekDate.WeekLabels[intK];
r.Cells.Add(c);
}
t.Rows.Add(r);
// OVERALL WORKLOAD Row
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "GrayRowStyle";
c.Text = "OVERALL WORKLOAD";
r.Cells.Add(c);
//
c = new TableCell();
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "GrayRowStyle";
c.Text = "PEOPLE";
r.Cells.Add(c);
// Blank Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "GrayRowStyle";
c.Text = " ";
r.Cells.Add(c);
}
t.Rows.Add(r);
// Needed Row
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = " ";
r.Cells.Add(c);
//
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = "NEEDED";
r.Cells.Add(c);
// Week Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
if (alIndex[intK].GetValueOrDefault<decimal>() < 1)
{
strStyle = "CellUnderSyle";
}
else if (alIndex[intK].GetValueOrDefault<decimal>() >= 1 & alIndex[intK].GetValueOrDefault<decimal>() < 1.1m)
{
strStyle = "CellScheduledStyle";
}
else if (alIndex[intK].GetValueOrDefault<decimal>() >= 1.1m & alIndex[intK].GetValueOrDefault<decimal>() < 1.25m)
{
strStyle = "Cell1025Style";
}
else if (alIndex[intK].GetValueOrDefault<decimal>() >= 1.25m)
{
strStyle = "CellOver25Style";
}
c.CssClass = strStyle;
c.Text = alNeeded[intK].GetValueOrDefault<decimal>().ToString("###,##0.0");
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
t.Rows.Add(r);
// Available Row
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "AlternateRowStyle";
c.Text = " ";
r.Cells.Add(c);
//
c = new TableCell();
c.CssClass = "AlternateRowStyle";
c.Text = "AVAILABLE";
r.Cells.Add(c);
// Week Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = alAvailable[intK].GetValueOrDefault<decimal>().ToString("###,##0.0");
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
t.Rows.Add(r);
// Surplus/Shortage Row
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = " ";
r.Cells.Add(c);
//
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = "SURPLUS/SHORTAGE";
r.Cells.Add(c);
// Week Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
//
if (alIndex[intK].GetValueOrDefault<decimal>() < 1)
{
strStyle = "CellUnderSyle";
}
else if (alIndex[intK].GetValueOrDefault<decimal>() >= 1 & alIndex[intK].GetValueOrDefault<decimal>() < 1.1m)
{
strStyle = "CellScheduledStyle";
}
else if (alIndex[intK].GetValueOrDefault<decimal>() >= 1.1m & alIndex[intK].GetValueOrDefault<decimal>() < 1.25m)
{
strStyle = "Cell1025Style";
}
else if (alIndex[intK].GetValueOrDefault<decimal>() >= 1.25m)
{
strStyle = "CellOver25Style";
}
c.CssClass = strStyle;
c.Text = Math.Abs(alSurplusShortage[intK].GetValueOrDefault<decimal>()).ToString("###,###.##");
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
t.Rows.Add(r);
// Index Row
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "AlternateRowStyle";
c.Text = " ";
r.Cells.Add(c);
//
c = new TableCell();
c.CssClass = "AlternateRowStyle";
c.Text = "INDEX";
r.Cells.Add(c);
// Week Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
//
if (alIndex[intK].GetValueOrDefault<decimal>() < 1)
{
strStyle = "CellUnderSyle";
}
else if (alIndex[intK].GetValueOrDefault<decimal>() >= 1 & alIndex[intK].GetValueOrDefault<decimal>() < 1.1m)
{
strStyle = "CellScheduledStyle";
}
else if (alIndex[intK].GetValueOrDefault<decimal>() >= 1.1m & alIndex[intK].GetValueOrDefault<decimal>() < 1.25m)
{
strStyle = "Cell1025Style";
}
else if (alIndex[intK].GetValueOrDefault<decimal>() >= 1.25m)
{
strStyle = "CellOver25Style";
}
c.CssClass = strStyle;
c.Text = alIndex[intK].GetValueOrDefault<decimal>().ToString(); //, 2);
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
t.Rows.Add(r);
// Blank Row
r = new TableRow();
// Spacer Column
c = new TableCell();
c.Text = "<img src='images/spacer.gif' height='10' />";
c.ColumnSpan = 12;
r.Cells.Add(c);
t.Rows.Add(r);
//
// EMPLOYEE WORKLOAD Row
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "GrayRowStyle";
c.Text = "EMPLOYEE WORKLOAD";
r.Cells.Add(c);
//
c = new TableCell();
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "GrayRowStyle";
c.Text = "PEOPLE";
r.Cells.Add(c);
// Blank Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "GrayRowStyle";
c.Text = " ";
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
t.Rows.Add(r);
// Under Row
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = " ";
r.Cells.Add(c);
//
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = "UNDER";
r.Cells.Add(c);
// Week Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
//
strStyle = "CellUnderSyle";
c.CssClass = strStyle;
c.Text = alUnder[intK].GetValueOrDefault<decimal>().ToString("###,###");
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
t.Rows.Add(r);
// Scheduled Row
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "AlternateRowStyle";
c.Text = " ";
r.Cells.Add(c);
//
c = new TableCell();
c.CssClass = "AlternateRowStyle";
c.Text = "EVEN";
r.Cells.Add(c);
// Week Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
//
strStyle = "CellScheduledStyle";
c.CssClass = strStyle;
c.Text = alScheduled[intK].ToString(); //.ToString("###,###");
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
t.Rows.Add(r);
// 10 To 25 % Row
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = " ";
r.Cells.Add(c);
//
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = "10 - 25%";
r.Cells.Add(c);
// Week Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
//
strStyle = "Cell1025Style";
c.CssClass = strStyle;
c.Text = al10To25[intK].ToString(); //, "###,###");
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
t.Rows.Add(r);
// Over 25% Row
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "AlternateRowStyle";
c.Text = " ";
r.Cells.Add(c);
//
c = new TableCell();
c.CssClass = "AlternateRowStyle";
c.Text = "25% +";
r.Cells.Add(c);
// Week Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
//
strStyle = "CellOver25Style";
c.CssClass = strStyle;
c.Text = alOver25[intK].ToString(); //, "###,###");
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
t.Rows.Add(r);
// Blank Row
r = new TableRow();
// Spacer Column
c = new TableCell();
c.Text = "<img src='images/spacer.gif' height='10' />";
c.ColumnSpan = 12;
r.Cells.Add(c);
t.Rows.Add(r);
//
// Un Assigned By Hours
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "GrayRowStyle";
c.Text = "UNASSIGNED";
r.Cells.Add(c);
//
c = new TableCell();
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "GrayRowStyle";
c.Text = "PEOPLE";
r.Cells.Add(c);
// Blank Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "GrayRowStyle";
c.Text = " ";
r.Cells.Add(c);
}
t.Rows.Add(r);
// UnASssigned Engineer Row
r = new TableRow();
//
// Title
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = " ";
r.Cells.Add(c);
//
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = "ENGINEERS";
r.Cells.Add(c);
// Week Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.CssClass = "RowStyle";
c.Text = alUnAssignedEng[intK].ToString(); //, "###,###.##");
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
t.Rows.Add(r);
// Blank Row
r = new TableRow();
// Spacer Column
c = new TableCell();
c.Text = "<img src='images/spacer.gif' height='10' />";
c.ColumnSpan = 12;
r.Cells.Add(c);
t.Rows.Add(r);
// Add table to Cell
cEng.Controls.Add(t);
//
// Add all to container table
rEng.Cells.Add(cEng);
this.tblEngineers.Rows.Add(rEng);
}
protected void btnFinalize_Click(object sender, EventArgs e)
{
using (EmployeeJournal objEmployeeJournal = new EmployeeJournal())
{
objEmployeeJournal.EmployeeJournalCode = EmployeeJournal.GetLastJournalCode(Convert.ToInt16(WeekYear.GetActiveWeekCode()), Request.Cookies["Speedo"]["UserName"].ToString());
objEmployeeJournal.JournalStatus = "F";
objEmployeeJournal.LockStatus = "1";
objEmployeeJournal.Enabled = "1";
objEmployeeJournal.ModifiedBy = Request.Cookies["Speedo"]["UserName"].ToString();
if (objEmployeeJournal.UpdateStatus() > 0)
{
using (EmployeeJournalApproval objEmployeeJournalApproval = new EmployeeJournalApproval())
{
objEmployeeJournalApproval.EmployeeJournalCode = objEmployeeJournal.EmployeeJournalCode;
objEmployeeJournalApproval.ResetStatus();
}
Response.Redirect("EmployeeJournalList.aspx");
}
}
}
private DataSet GetUnAssignedEngineer_ByHours(WeekYear wyFirst, WeekYear wyLast)
{
string strSQL = null;
SqlDataAdapter da = default(SqlDataAdapter);
DataSet ds = default(DataSet);
//
int intWeekFirst = wyFirst.Week;
int intYearFirst = wyFirst.Year;
string strDateFirst = wyFirst.MondayDate;
int intWeekLast = wyLast.Week;
int intYearLast = wyLast.Year;
string strDateLast = wyLast.MondayDate;
int intK = 0;
//
strSQL = "SELECT ID";
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
strSQL += ", (SELECT SUM(Hours) ";
strSQL += "FROM v_EmployeeScheduleByWeek s ";
strSQL += "WHERE (EmployeeName LIKE '- Unassigned%') ";
strSQL += "AND (s.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "AND (s.WeekNo = " + WeekDate.Weeks[intK] + ")) AS [" + WeekDate.WeekLabels[intK] + "] ";
}
strSQL += "FROM tblTemp";
//
da = new SqlDataAdapter(strSQL, strConn);
ds = new DataSet();
da.Fill(ds);
//
return ds;
}
protected void MakeCart()
{
DataTable tblCart = new DataTable("Cart");
tblCart.Columns.Add("JournalDCode", System.Type.GetType("System.String"));
tblCart.Columns.Add("JournalCode", System.Type.GetType("System.String"));
tblCart.Columns.Add("ItemNumber", System.Type.GetType("System.String"));
tblCart.Columns.Add("Contents", System.Type.GetType("System.String"));
tblCart = EmployeeJournalDetails.GetDSGCart(EmployeeJournal.GetLastJournalCode(Convert.ToInt16(WeekYear.GetActiveWeekCode()), Request.Cookies["Speedo"]["UserName"].ToString()));
ViewState["Cart"] = tblCart;
LoadGrid();
}
private void PopulateDataset(int intProjID, WeekYear wyFirst, WeekYear wyLast, string strSortColumn = "", string strSortType = "")
{
int intI = 0;
int intID = 0;
DataSet dsProjects = Project.GetProjectAssignments(intProjID);
// Auto increment ID field
for (intI = 0; intI <= dsProjects.Tables["Schedule"].Rows.Count - 1; intI++)
{
intID += 1;
dsProjects.Tables["Schedule"].Rows[intI]["ID"] = intID;
}
DataView scheduleView = dsProjects.Tables["Schedule"].DefaultView;
scheduleView.Sort = "order asc, week1 desc";
this.uwgProjects.DataSource = scheduleView.Table;
this.uwgProjects.DataMember = scheduleView.Table.TableName;
}
private void PopulateDataset(WeekYear wyFirst, WeekYear wyLast, string strFilterField, string strFilterText)
{
string strSQL = null;
string strFilter = "";
//
int intWeekFirst = wyFirst.Week;
int intYearFirst = wyFirst.Year;
string strDateFirst = wyFirst.MondayDate;
int intWeekLast = wyLast.Week;
int intYearLast = wyLast.Year;
string strDateLast = wyLast.MondayDate;
int intI = 0;
int intID = 0;
// Set week labels
SetWeekDateArrays(wyFirst, wyLast);
//
strSQL = "SELECT ([ID]) AS ProjectID, ProjectNo, ";
strSQL += "UPPER(ProjectName) AS ProjectName, PICCode, PM1Code ";
for (intI = 0; intI <= clsSchedule.cWeekSpan; intI++)
{
strSQL += ", (SELECT SUM ([Hours]) ";
strSQL += "FROM v_EmployeeScheduleByWeek s ";
strSQL += "WHERE s.ProjectID = tblProjects.ID ";
strSQL += "AND (s.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "AND (s.WeekNo = " + intWeeks[intI] + ")) AS [Week" + (intI + 1) + "] ";
}
strSQL += "FROM tblProjects ";
if (!string.IsNullOrEmpty(strFilterText))
{
strFilter = GetFilter(strFilterField, strFilterText);
strSQL += "WHERE Active = 0 AND ";
strSQL += strFilter + " ";
}
else
{
strSQL += "WHERE Active = 0 ";
}
strSQL += "ORDER BY ProjectNo";
//
daProjects = new SqlDataAdapter(strSQL, strConn);
//
daProjects.Fill(dsProjects, "Projects");
//
this.uwgProjects.DataSource = dsProjects;
this.uwgProjects.DataMember = "Projects";
}
protected void btnSave_Click(object sender, EventArgs e)
{
using (EmployeeJournalDetails objEmployeeJournalDetails = new EmployeeJournalDetails())
{
objEmployeeJournalDetails.EmployeeJournalCode = EmployeeJournal.GetLastJournalCode(Convert.ToInt16(WeekYear.GetActiveWeekCode()), Request.Cookies["Speedo"]["UserName"].ToString());
objEmployeeJournalDetails.Contents = txtContents.Text;
objEmployeeJournalDetails.ItemNumber = EmployeeJournalDetails.GetTotalRecords(EmployeeJournal.GetLastJournalCode(Convert.ToInt16(WeekYear.GetActiveWeekCode()), Request.Cookies["Speedo"]["UserName"].ToString())) + 1;
objEmployeeJournalDetails.JournalDate = DateTime.Now;
objEmployeeJournalDetails.CreatedOn = DateTime.Now;
objEmployeeJournalDetails.EndorsedBy = "NA";
objEmployeeJournalDetails.EndorsedOn = DateTime.Now;
objEmployeeJournalDetails.EndoredRemarks = "NA";
objEmployeeJournalDetails.ApprovedBy = "NA";
objEmployeeJournalDetails.ApprovedOn = DateTime.Now;
objEmployeeJournalDetails.ApprovedRemarks = "NA";
objEmployeeJournalDetails.JournalStatus = "F";
objEmployeeJournalDetails.IsEnabled = "1";
if (objEmployeeJournalDetails.Insert() > 0)
{
Response.Redirect("JournalEncodingM.aspx?JournalCode=" + ViewState["JournalCode"].ToString());
}
}
}
public WeekYear GetWeekYear(DateTime dtmDate)
{
WeekYear wyCurrent = new WeekYear();
string strWeekMondayDate = GetWeekMondayDate(dtmDate);
// Get Week No and Year from table
int intCurrWeek = 0;
int intCurrYear = 0;
string strSQL;
strSQL = "SELECT WeekNo, WeekNoYear FROM tblWeeks WHERE WeekNoStartDate = \'" + (DateTime.Parse(strWeekMondayDate) + "\'");
DataSet ds = general.FillDataset(strSQL);
DataTable dt = ds.Tables[0];
if ((dt.Rows.Count > 0))
{
intCurrWeek = Convert.ToInt32(Utilities.Nz(dt.Rows[0]["WeekNo"], 0));
intCurrYear = Convert.ToInt32(Utilities.Nz(dt.Rows[0]["WeekNoYear"], 0));
}
dt.Dispose();
ds.Dispose();
dt = null;
ds = null;
wyCurrent.Week = intCurrWeek;
wyCurrent.Year = intCurrYear;
wyCurrent.MondayDate = DateTime.Parse(strWeekMondayDate);
//
return wyCurrent;
}
private void UpdateData()
{
WyLast = Schedule.GetWeekYearLast(WyFirst);
//this.wdtWeek.Value = WeekDate.WYFirst.MondayDate;
SetWeekCalDate(WeekDate.WYFirst.MondayDate);
BindData();
}
public WeekYear GetWeekYearFirst(WeekYear wyCurrent, bool blnNext)
{
int intWeek = wyCurrent.Week;
int intYear = wyCurrent.Year;
int intWeekFirst;
int intYearFirst;
WeekYear wyFirst = new WeekYear();
int intNoOfWeeks = GetNoOfWeeks(intYear);
//
// Check how many weeks in the year
if (blnNext)
{
// Forward
intWeekFirst = (intWeek + cMoveWeeks);
intYearFirst = intYear;
// If intWeekFirst > 52 Then
// intWeekFirst = intWeekFirst - 52
// intYearFirst = intYear + 1
// End If
if ((intWeekFirst > intNoOfWeeks))
{
intWeekFirst = (intWeekFirst - intNoOfWeeks);
intYearFirst = (intYear + 1);
}
}
else
{
// Back
intWeekFirst = (intWeek - cMoveWeeks);
intYearFirst = intYear;
// If intWeekFirst <= 0 Then
// intWeekFirst = 52 + intWeekFirst
// intYearFirst = intYear - 1
// End If
if ((intWeekFirst <= 0))
{
intWeekFirst = (intNoOfWeeks + intWeekFirst);
intYearFirst = (intYear - 1);
}
}
//
wyFirst.Week = intWeekFirst;
wyFirst.Year = intYearFirst;
wyFirst.MondayDate = DateTime.Parse(GetWeekMondayDate(intWeekFirst, intYearFirst));
//
return wyFirst;
}
private void ConstructTable(WeekYear wyFirst, WeekYear wyLast)
{
string strSQL = null;
System.Data.SqlClient.SqlConnection connSQL = default(System.Data.SqlClient.SqlConnection);
SqlDataAdapter da = default(SqlDataAdapter);
DataSet dsEngineers = default(DataSet);
DataTable dtEngineers = default(DataTable);
DataSet dsProjects = default(DataSet);
DataTable dtProjects = default(DataTable);
DataRow dtRow = default(DataRow);
int intEmployeeID = 0;
//
int intWeekFirst = wyFirst.Week;
int intYearFirst = wyFirst.Year;
string strDateFirst = wyFirst.MondayDate;
int intWeekLast = wyLast.Week;
int intYearLast = wyLast.Year;
string strDateLast = wyLast.MondayDate;
int intI = 0;
int intJ = 0;
int intK = 0;
int intID = 0;
int intCol = 0;
int intColChild = 0;
//
TableRow rEng = default(TableRow);
TableCell cEng = default(TableCell);
//
Table t = default(Table);
TableRow r = default(TableRow);
TableCell c = default(TableCell);
//
string strHeaderStyle = null;
string strStyle = null;
//
decimal decHours = default(decimal);
var alHours = new List<decimal>(); // ArrayList alHours = new ArrayList();
string strHours = null;
object val = null;
//
string strCurWeekLabel = schedule.GetCurrentWeekLabel();
bool blnCurWeek = false;
// Reset ArrayList
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
alHours.Add(0);
}
// Set week labels
SetWeekDateArrays(wyFirst, wyLast);
// Set table width
this.tblEngineers.Width = Unit.Pixel(850);
// Add row to container table
rEng = new TableRow();
cEng = new TableCell();
//
strSQL = "SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName, ";
strSQL += "tblEmployeeTypes.EmployeeType ";
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
strSQL += ", (SELECT SUM ([Hours]) ";
strSQL += "FROM v_EmployeeScheduleByWeek_ActiveProjects s ";
strSQL += "WHERE s.EmployeeID = tblEmployees.EmployeeID ";
strSQL += "AND (s.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "AND (s.WeekNo = " + intWeeks[intK] + ")) AS [" + strWeekLabel[intK] + "] ";
}
strSQL += "FROM tblEmployees LEFT OUTER JOIN tblEmployeeTypes ";
strSQL += "ON tblEmployees.EmployeeTypeID = tblEmployeeTypes.ID ";
strSQL += "WHERE tblEmployees.Active = 1 ";
strSQL += "ORDER BY tblEmployees.EmployeeName";
//
da = new SqlDataAdapter(strSQL, strConn);
dsEngineers = new DataSet();
da.Fill(dsEngineers);
dtEngineers = dsEngineers.Tables[0];
if (dtEngineers.Rows.Count > 0)
{
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
t.Width = Unit.Pixel(850);
// Column Headers
r = new TableRow();
r.Height = Unit.Pixel(13);
//
// EmployeeName
c = new TableCell();
c.Width = Unit.Pixel(230);
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "ChildHeaderStyle";
c.Text = "Engineer";
r.Cells.Add(c);
// Employee Type
c = new TableCell();
c.Width = Unit.Pixel(125);
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "ChildHeaderStyle";
c.Text = "Type";
r.Cells.Add(c);
// Week Hours Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.Width = Unit.Pixel(35);
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "ChildHeaderStyle";
if (dtEngineers.Columns[intK + 3].Caption == strCurWeekLabel)
{
c.CssClass = "ChildCurWeekHeaderStyle";
}
c.Text = strWeekLabel[intK];
r.Cells.Add(c);
}
//
t.Rows.Add(r);
cEng.Controls.Add(t);
// Now grab data and populate table
for (intI = 0; intI <= dtEngineers.Rows.Count - 1; intI++)
{
// Get EmployeeID
intEmployeeID = dtEngineers.Rows[intI]["EmployeeID"].GetValueOrDefault<int>();
//
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
t.Width = Unit.Pixel(850);
//
//If intI Mod 2 = 0 Then
// strStyle = "RowStyle"
//Else
// strStyle = "AlternateRowStyle"
//End If
//
strStyle = "GrayRowStyle";
//
r = new TableRow();
r.Height = Unit.Pixel(13);
r.CssClass = strStyle;
// EmployeeName
c = new TableCell();
c.Width = Unit.Pixel(230);
c.CssClass = strStyle;
c.Text = dtEngineers.Rows[intI]["EmployeeName"].GetValueOrDefault<string>();
r.Cells.Add(c);
// Employee Type
c = new TableCell();
c.Width = Unit.Pixel(125);
c.CssClass = strStyle;
c.Text = dtEngineers.Rows[intI]["EmployeeType"].GetValueOrDefault<string>();
r.Cells.Add(c);
// Week Hours Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.Width = Unit.Pixel(35);
decHours = dtEngineers.Rows[intI][intK + 3].GetValueOrDefault<decimal>();
alHours[intK] += decHours;
strHours = dtEngineers.Rows[intI][intK + 3].GetValueOrDefault<string>();
strStyle = schedule.GetWeekHoursStyle((int)decHours);
c.Text = strHours;
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = strStyle;
r.Cells.Add(c);
}
//
t.Rows.Add(r);
cEng.Controls.Add(t);
//
// Now add Child Records
strSQL = "SELECT TOP 100 PERCENT e.ProjectID, e.ProjectNo, UPPER(CASE WHEN (LEN(e.ProjectName) > 20) THEN LEFT(e.ProjectName, 20) + '...' ELSE e.ProjectName END) AS ProjectName, EmployeeID ";
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
strSQL += ", (SELECT SUM([Hours]) ";
strSQL += "FROM v_EmployeeScheduleByWeek s ";
strSQL += "WHERE s.ProjectID = e.ProjectID ";
strSQL += "AND s.EmployeeID = e.EmployeeID ";
strSQL += "AND (s.WeekNoStartDate ";
strSQL += "BETWEEN '" + strDateFirst + "' ";
strSQL += "AND '" + strDateLast + "') ";
strSQL += "AND (s.WeekNo = " + intWeeks[intK] + ")) AS [" + strWeekLabel[intK] + "] ";
}
strSQL += "FROM v_EmployeeScheduleByWeek e ";
strSQL += "WHERE e.EmployeeID = " + intEmployeeID + " ";
strSQL += "GROUP BY e.ProjectID, e.ProjectNo, e.ProjectName, e.EmployeeID ";
strSQL += "ORDER BY e.ProjectNo, e.ProjectName";
da = new SqlDataAdapter(strSQL, strConn);
dsEngineers = new DataSet();
da.Fill(dsEngineers);
dtProjects = dsEngineers.Tables[0];
if (dtProjects.Rows.Count > 0)
{
//
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
t.Width = Unit.Pixel(850);
// '' '' '' '' '' Add Header row
//' '' '' '' ''r = New TableRow
//' '' '' '' ''r.Height = Unit.Pixel(13)
// '' '' '' '' ''
// '' '' '' '' '' Blank Column
//' '' '' '' ''c = New TableCell
//' '' '' '' ''c.Width = Unit.Pixel(25)
//' '' '' '' ''c.CssClass = "ChildHeaderStyle"
//' '' '' '' ''c.Text = ""
//' '' '' '' ''r.Cells.Add(c)
// '' '' '' '' '' Project No
//' '' '' '' ''c = New TableCell
//' '' '' '' ''c.Width = Unit.Pixel(100)
//' '' '' '' ''c.CssClass = "ChildHeaderStyle"
//' '' '' '' ''c.Text = "Job #"
//' '' '' '' ''r.Cells.Add(c)
// '' '' '' '' '' Project Name
//' '' '' '' ''c = New TableCell
//' '' '' '' ''c.Width = Unit.Pixel(230)
//' '' '' '' ''c.CssClass = "ChildHeaderStyle"
//' '' '' '' ''c.Text = "Project"
//' '' '' '' ''r.Cells.Add(c)
// '' '' '' '' ''
// '' '' '' '' '' Week Hours Columns
//' '' '' '' ''For intK = 0 To clsSchedule.cWeekSpan
//' '' '' '' '' c = New TableCell
//' '' '' '' '' c.Width = Unit.Pixel(35)
//' '' '' '' '' c.CssClass = "ChildHeaderStyle"
//' '' '' '' '' If dtProjects.Columns(intK + 4).Caption = strCurWeekLabel Then
//' '' '' '' '' c.CssClass = "ChildCurWeekHeaderStyle"
//' '' '' '' '' End If
//' '' '' '' '' c.Text = strWeekLabel[intK]
//' '' '' '' '' r.Cells.Add(c)
//' '' '' '' ''Next
// '' '' '' '' ''
//' '' '' '' ''t.Rows.Add(r)
//' '' '' '' ''cEng.Controls.Add(t)
//
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
t.Width = Unit.Pixel(850);
// Now grab data and populate table
for (intJ = 0; intJ <= dtProjects.Rows.Count - 1; intJ++)
{
//
if (intJ % 2 == 0)
{
strStyle = "RowStyle";
}
else
{
strStyle = "AlternateRowStyle";
}
//
r = new TableRow();
r.Height = Unit.Pixel(13);
// Blank Column
c = new TableCell();
c.Width = Unit.Pixel(25);
c.CssClass = "ChildHeaderStyle";
c.Text = "";
r.Cells.Add(c);
// Project No
c = new TableCell();
c.Width = Unit.Pixel(100);
c.CssClass = strStyle;
c.Text = dtProjects.Rows[intJ]["ProjectNo"].GetValueOrDefault<string>();
r.Cells.Add(c);
// Project Name
c = new TableCell();
c.Width = Unit.Pixel(230);
c.CssClass = strStyle;
c.Text = dtProjects.Rows[intJ]["ProjectName"].GetValueOrDefault<string>();
r.Cells.Add(c);
// Week Hours Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.Width = Unit.Pixel(35);
decHours = dtProjects.Rows[intJ][intK + 4].GetValueOrDefault<decimal>();
strHours = decHours.ToString();
val = dtEngineers.Rows[intI][intK + 3].GetValueOrDefault<int>();
strStyle = schedule.GetWeekHoursStyle((int)val);
c.Text = strHours;
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = strStyle;
r.Cells.Add(c);
}
//
t.Rows.Add(r);
}
cEng.Controls.Add(t);
//
}
// Add Blank Row
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
t.Width = Unit.Pixel(850);
//
r = new TableRow();
r.Height = Unit.Pixel(10);
// Blank Column
c = new TableCell();
c.Text = "<img src='images/spacer.gif' height='10' />";
c.ColumnSpan = 12;
r.Cells.Add(c);
//
t.Rows.Add(r);
//
cEng.Controls.Add(t);
}
cEng.Controls.Add(t);
// Add Footer
t = new Table();
t.CellSpacing = 0;
t.CellPadding = 0;
t.BorderWidth = Unit.Pixel(0);
t.Width = Unit.Pixel(850);
// Footer Row
r = new TableRow();
r.Height = Unit.Pixel(13);
//
// Blank Column
c = new TableCell();
c.Width = Unit.Pixel(25);
c.CssClass = "ChildHeaderStyle";
c.Text = " ";
r.Cells.Add(c);
// Legend
c = new TableCell();
c.CssClass = "ChildHeaderStyle";
c.Width = Unit.Pixel(330);
c.Text = "<img src='../images/GridLegend.jpg' />";
r.Cells.Add(c);
// Week Hours Columns
for (intK = 0; intK <= clsSchedule.cWeekSpan; intK++)
{
c = new TableCell();
c.Width = Unit.Pixel(35);
c.HorizontalAlign = HorizontalAlign.Center;
c.CssClass = "ChildHeaderStyle";
c.Text = alHours[intK].GetValueOrDefault<decimal>().ToString("###,###.##");
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
}
//
t.Rows.Add(r);
cEng.Controls.Add(t);
}
else
{
// No Data Display Text
}
rEng.Cells.Add(cEng);
this.tblEngineers.Rows.Add(rEng);
}
private void PopulateDataset(int intEmpID, WeekYear wyFirst, WeekYear wyLast, string strSortColumn = "", string strSortType = "")
{
int intI = 0;
int intID = 0;
DataSet dsProjects = Project.GetActiveScheduleByEmployeeID(intEmpID);
for (intI = 0; intI <= dsProjects.Tables["Schedule"].Rows.Count - 1; intI++)
{
intID += 1;
dsProjects.Tables["Schedule"].Rows[intI]["ID"] = intID;
}
//
DataView scheduleView = dsProjects.Tables["Schedule"].DefaultView;
if (!string.IsNullOrEmpty(strSortColumn))
{
scheduleView.Sort = strSortColumn + strSortType;
}
else
{
scheduleView.Sort = "week1 desc, ProjectName asc";
}
this.uwgProjects.DataSource = scheduleView;
this.uwgProjects.DataMember = scheduleView.Table.TableName;
}
