protected void Button1_Click(object sender, EventArgs e) { dateConverter d = new dateConverter(); Response.Clear(); Response.Buffer = true; Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "WORKING HOURS FOR " + cbo_farm.SelectedItem.Text + ".csv")); Response.ContentType = "application/ms-excel"; StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); Response.Charset = ""; dgd_workers.AllowPaging = false; StringBuilder columnbind = new StringBuilder(); for (int k = 0; k < dgd_workers.Columns.Count; k++) { columnbind.Append(dgd_workers.Columns[k].HeaderText + ','); } columnbind.Append("\r\n"); for (int i = 0; i < dgd_workers.Rows.Count; i++) { for (int k = 0; k < dgd_workers.Columns.Count; k++) { columnbind.Append(dgd_workers.Rows[i].Cells[k].Text + ','); } columnbind.Append("\r\n"); } Response.Output.Write(columnbind.ToString()); Response.Flush(); Response.End(); }
private void saveButton_Click(object sender, RoutedEventArgs e) { if (checkCanSave()) { if (isEditMode) // in edit mode, replace existing details with modified. { int idInt = 0; int.TryParse(id, out idInt); if (idInt != 0) { Performance p = thisTheatre.getPerformances().Find(x => x.getID() == idInt); DateTime date = new dateConverter().stringToDate(dateBox.Text); Screen s = p.getScreen(); s.setDressPrice(float.Parse(priceDress.Text)); s.setStallsPrice(float.Parse(priceStalls.Text)); s.setUpperPrice(float.Parse(priceUpper.Text)); Performance temp = new Performance(idInt, titleBox.Text, descriptionBox.Text, date, s); thisTheatre.deletePerformanceEntry(id); thisTheatre.getPerformances().Remove(p); thisTheatre.addPerformance(temp); MessageBox.Show("Updated Performance."); this.Close(); } } else // not in edit mode, add new performance. { string[] dates = dateBox.Text.Split(',').ToArray(); List <DateTime> dateList = new List <DateTime>(); for (int i = 0; i < dates.Length; ++i) { DateTime date = new dateConverter().stringToDate(dates[i]); if (dateList.Contains(date)) { break; } dateList.Add(date); Performance newPerf = new Performance(titleBox.Text, descriptionBox.Text, date, float.Parse(priceUpper.Text), float.Parse(priceDress.Text), float.Parse(priceStalls.Text)); thisTheatre.addPerformance(newPerf); } MessageBox.Show("New performance/s added succesfully."); this.Close(); } } else { MessageBox.Show("Please ensure all details have been entered."); } }
protected void btn_excel_Click(object sender, EventArgs e) { if (cbo_farm.SelectedIndex != 0 && (cbo_week.SelectedIndex != 0 && Convert.ToBoolean(Session["day"]) == false) || (cbo_day.SelectedIndex != 0 && Convert.ToBoolean(Session["day"]) == true)) { Response.ClearContent(); Response.Buffer = true; Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "WORKING HOURS FOR " + cbo_farm.SelectedItem.Text + ".xls")); Response.ContentType = "application/ms-excel"; StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); dgd_workers.AllowPaging = false; dateConverter d = new dateConverter(); SqlDataSource1.SelectParameters.Clear(); // SqlDataSource1.SelectCommand = "SELECT [dbo].[tbl_worker].[FirstName] as Fname ,[dbo].[tbl_worker].[LastName] as lname ,[dbo].[tbl_Attendance].[Start_time],[dbo].[tbl_Attendance].[End_time],[dbo].[tbl_Attendance].[breaktime] as [Break Time],[dbo].[tbl_Attendance].[paid_break] as [Paid Break],[dbo].[tbl_Attendance].[Total_hours],[dbo].[tbl_Attendance].[note] As [] FROM [dbo].[tbl_Attendance] INNER JOIN tbl_duty on tbl_attendance.rosterid = tbl_duty.rosterid INNER JOIN tbl_worker on tbl_worker.workersid = tbl_duty.workerid INNER JOIN tbl_shift on tbl_duty.shiftid = tbl_shift.shiftid where tbl_duty.day = @day and tbl_duty.growerid = @grower and farmid = @farm and tbl_attendance.end_time is not null"; SqlDataSource1.SelectCommand = "SELECT ([dbo].[tbl_worker].[FirstName] + [dbo].[tbl_worker].[LastName]) as Name ,[dbo].[tbl_Attendance].[Start_time] As [Start Time],[dbo].[tbl_Attendance].[End_time] as [End Time],[dbo].[tbl_Attendance].[breaktime] as [Break Time],[dbo].[tbl_Attendance].[paid_break] as [Paid Break],cast(round([dbo].[tbl_Attendance].[Total_hours]/60,2) as numeric(36,2)) As [Total Hours] ,[dbo].[tbl_Attendance].[note] as [Note] FROM [dbo].[tbl_Attendance] INNER JOIN tbl_duty on tbl_attendance.rosterid = tbl_duty.rosterid INNER JOIN tbl_worker on tbl_worker.workersid = tbl_duty.workerid INNER JOIN tbl_shift on tbl_duty.shiftid = tbl_shift.shiftid where tbl_duty.day = @day and tbl_duty.growerid = @grower and farmid = @farm "; //SqlDataSource1.SelectParameters.Add("supervisor", cbo_supervisor.ToString()); if (Convert.ToBoolean(Session["day"])) { //SqlDataSource1.SelectParameters.Add("day", Convert.ToDateTime(cbo_day.SelectedValue).Date.ToString()); //SqlDataSource1.SelectParameters.Add("day", "12/05/2016"); SqlDataSource1.SelectParameters.Add("day", d.convertUTCtoNZT(Convert.ToDateTime(cbo_day.SelectedValue)).Month.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_day.SelectedValue)).Day.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_day.SelectedValue)).Year.ToString()); } else { SqlDataSource1.SelectParameters.Clear(); // SqlDataSource1.SelectCommand = "SELECT tbl_worker.firstname AS 'First Name', tbl_worker.lastname AS 'Last Name', sum(tbl_attendance.total_hours)/60 AS 'Total Hours' FROM tbl_attendance INNER JOIN tbl_duty on tbl_attendance.rosterid = tbl_duty.rosterid INNER JOIN tbl_worker on tbl_worker.workersid = tbl_duty.workerid INNER JOIN tbl_shift on tbl_duty.shiftid = tbl_shift.shiftid where tbl_duty.day BETWEEN @0 AND @1 and tbl_duty.growerid = @grower and farmid = @farm and tbl_attendance.end_time is not null group by tbl_worker.firstname,tbl_worker.lastname"; SqlDataSource1.SelectCommand = "SELECT tbl_worker.firstname AS 'First Name', tbl_worker.lastname AS 'Last Name',cast(round(sum(tbl_attendance.total_hours) / 60 , 2) as numeric(36,2)) AS 'Total Hours' FROM tbl_attendance INNER JOIN tbl_duty on tbl_attendance.rosterid = tbl_duty.rosterid INNER JOIN tbl_worker on tbl_worker.workersid = tbl_duty.workerid INNER JOIN tbl_shift on tbl_duty.shiftid = tbl_shift.shiftid where tbl_duty.day BETWEEN @0 AND @1 and tbl_duty.growerid = @grower and farmid = @farm and tbl_attendance.end_time is not null group by tbl_worker.firstname,tbl_worker.lastname"; SqlDataSource1.SelectParameters.Add("0", d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).Month.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).Day.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).Year.ToString()); SqlDataSource1.SelectParameters.Add("1", d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).AddDays(7).Month.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).AddDays(7).Day.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).AddDays(7).Year.ToString()); } SqlDataSource1.SelectParameters.Add("farm", cbo_farm.SelectedValue.ToString()); SqlDataSource1.SelectParameters.Add("grower", Session["Id"].ToString()); SqlDataSource1.DataBind(); //Change the Header Row back to white color dgd_workers.HeaderRow.Style.Add("background-color", "#FFFFFF"); //Applying stlye to gridview header cells for (int i = 0; i < dgd_workers.HeaderRow.Cells.Count; i++) { dgd_workers.HeaderRow.Cells[i].Style.Add("background-color", "#df5015"); } dgd_workers.RenderControl(htw); Response.Write(sw.ToString()); Response.End(); } }
protected void btn_excel_Click(object sender, EventArgs e) { dateConverter d = new dateConverter(); if (cbo_farm.SelectedIndex != 0 && cbo_day.SelectedIndex != 0) { SqlDataSource2.SelectParameters.Clear(); SqlDataSource2.SelectParameters.Add("1", d.convertUTCtoNZT(Convert.ToDateTime(cbo_day.SelectedValue)).Month.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_day.SelectedValue)).Day.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_day.SelectedValue)).Year.ToString()); SqlDataSource2.SelectParameters.Add("2", Session["Id"].ToString()); SqlDataSource2.SelectParameters.Add("3", cbo_farm.SelectedValue.ToString()); SqlDataSource2.DataBind(); btn_excel.Visible = true; } //download(); }
protected void btn_submit_Click(object sender, EventArgs e) { dateConverter d = new dateConverter(); if (cbo_farm.SelectedIndex != 0 && (cbo_week.SelectedIndex != 0 && Convert.ToBoolean(Session["day"]) == false) || (cbo_day.SelectedIndex != 0 && Convert.ToBoolean(Session["day"]) == true)) { SqlDataSource1.SelectParameters.Clear(); // SqlDataSource1.SelectCommand = "SELECT tbl_worker.firstname AS 'Fisrt Name', tbl_worker.lastname AS 'Last Name', CONVERT(DECIMAL(10,2), tbl_attendance.total_hours/60) AS 'Total Hours' FROM tbl_attendance INNER JOIN tbl_duty on tbl_attendance.rosterid = tbl_duty.rosterid INNER JOIN tbl_worker on tbl_worker.workersid = tbl_duty.workerid INNER JOIN tbl_shift on tbl_duty.shiftid = tbl_shift.shiftid where tbl_duty.day = @day and tbl_duty.growerid = @grower and farmid = @farm and tbl_attendance.end_time is not null"; if (chkphase.Checked) { SqlDataSource1.SelectCommand = "SELECT ([dbo].[tbl_worker].[FirstName] + [dbo].[tbl_worker].[LastName]) as Name ,[dbo].[temp_attendance].[Start_time] As [Start Time],[dbo].[temp_attendance].[End_time] as [End Time], Phase,[dbo].[temp_attendance].[pay] FROM [dbo].[temp_attendance] inner join [dbo].[tbl_Attendance] on [dbo].[tbl_Attendance].[RosterID]=[dbo].[temp_attendance].[RosterID] INNER JOIN tbl_duty on tbl_attendance.rosterid = tbl_duty.rosterid INNER JOIN tbl_worker on tbl_worker.workersid = tbl_duty.workerid INNER JOIN tbl_shift on tbl_duty.shiftid = tbl_shift.shiftid where tbl_duty.day = @day and tbl_duty.growerid = @grower and farmid = @farm and [dbo].[temp_attendance].[End_time] is not null and [dbo].[temp_attendance].[pay] is not null and [dbo].[temp_attendance].[pay] <> 0"; } else { SqlDataSource1.SelectCommand = "SELECT ([dbo].[tbl_worker].[FirstName] + [dbo].[tbl_worker].[LastName]) as Name ,[dbo].[tbl_Attendance].[Start_time] As [Start Time],[dbo].[tbl_Attendance].[End_time] as [End Time],[dbo].[tbl_Attendance].[breaktime] as [Break Time],[dbo].[tbl_Attendance].[paid_break] as [Paid Break],cast(round([dbo].[tbl_Attendance].[Total_hours]/60,2) as numeric(36,2)) As [Total Hours] ,[dbo].[tbl_Attendance].[note] as [Note] FROM [dbo].[tbl_Attendance] INNER JOIN tbl_duty on tbl_attendance.rosterid = tbl_duty.rosterid INNER JOIN tbl_worker on tbl_worker.workersid = tbl_duty.workerid INNER JOIN tbl_shift on tbl_duty.shiftid = tbl_shift.shiftid where tbl_duty.day = @day and tbl_duty.growerid = @grower and farmid = @farm "; } //SqlDataSource1.SelectParameters.Add("supervisor", cbo_supervisor.ToString()); if (Convert.ToBoolean(Session["day"])) { //SqlDataSource1.SelectParameters.Add("day", Convert.ToDateTime(cbo_day.SelectedValue).Date.ToString()); //SqlDataSource1.SelectParameters.Add("day", "12/05/2016"); SqlDataSource1.SelectParameters.Add("day", d.convertUTCtoNZT(Convert.ToDateTime(cbo_day.SelectedValue)).Month.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_day.SelectedValue)).Day.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_day.SelectedValue)).Year.ToString()); } else { SqlDataSource1.SelectParameters.Clear(); SqlDataSource1.SelectCommand = "SELECT tbl_worker.firstname AS 'First Name', tbl_worker.lastname AS 'Last Name',cast(round(sum(tbl_attendance.total_hours) / 60 , 2) as numeric(36,2)) AS 'Total Hours' FROM tbl_attendance INNER JOIN tbl_duty on tbl_attendance.rosterid = tbl_duty.rosterid INNER JOIN tbl_worker on tbl_worker.workersid = tbl_duty.workerid INNER JOIN tbl_shift on tbl_duty.shiftid = tbl_shift.shiftid where tbl_duty.day BETWEEN @0 AND @1 and tbl_duty.growerid = @grower and farmid = @farm and tbl_attendance.end_time is not null group by tbl_worker.firstname,tbl_worker.lastname"; SqlDataSource1.SelectParameters.Add("0", d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).Month.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).Day.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).Year.ToString()); SqlDataSource1.SelectParameters.Add("1", d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).AddDays(7).Month.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).AddDays(7).Day.ToString() + "/" + d.convertUTCtoNZT(Convert.ToDateTime(cbo_week.SelectedValue)).AddDays(7).Year.ToString()); } SqlDataSource1.SelectParameters.Add("farm", cbo_farm.SelectedValue.ToString()); SqlDataSource1.SelectParameters.Add("grower", Session["Id"].ToString()); SqlDataSource1.DataBind(); btn_excel.Visible = true; } }
protected void download() { dateConverter d = new dateConverter(); Response.ClearContent(); Response.Buffer = true; Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "WORKING HOURS FOR " + cbo_farm.SelectedItem.Text + ".csv")); Response.ContentType = "application/ms-excel"; StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); dgd_workers.AllowPaging = false; //int j = dgd_workers.HeaderRow.Cells.Count; //for (int i = 0; i < j; i++) //{ // dgd_workers.HeaderRow.Cells[i].Style.Add("background-color", "#df5015"); //} dgd_workers.RenderControl(htw); Response.Write(sw.ToString()); Response.End(); }
protected void Page_Load(object sender, EventArgs e) { if (Session["Id"] == null) { Response.Redirect("~/login.aspx"); } Session["redirect"] = "load"; ((Label)Master.FindControl("lbl_title")).Text = "Dashboard"; Session["flag"] = true; dateConverter dc = new dateConverter(); DateTime dt = dc.convertUTCtoNZT(DateTime.UtcNow); //SqlDataSource2.SelectCommand = "DECLARE @count INT = 1 DECLARE @qry VARCHAR(MAX) = '' WHILE(@count <= (SELECT COUNT(*) FROM tbl_budget)) BEGIN IF (SELECT MONTH((SELECT TimeStamp FROM (SELECT ROW_NUMBER() OVER (ORDER BY TimeStamp ASC) AS rownumber, * FROM tbl_budget) AS looper WHERE rownumber = @count))) = @month BEGIN SET @qry = @qry + 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY TimeStamp ASC) AS rownumber, * FROM tbl_budget) AS looper WHERE rownumber = ' + CAST(@count AS VARCHAR(MAX)) + ' UNION ALL ' END SET @count = @count + 1 END IF LEN(@qry) > 10 SET @qry = LEFT(@qry, LEN(@qry) - 10) EXEC(@qry)"; SqlDataSource2.SelectCommand = "SELECT SUM((DATEPART(HOUR, tbl_Shift.totaltime) * tbl_worker.payrate))AS 'S', 'Assigned Worker Pay' AS 'grouper' FROM tbl_shift INNER JOIN tbl_Duty ON tbl_shift.ShiftID = tbl_Duty.ShiftID INNER JOIN tbl_worker ON tbl_worker.workersid = tbl_duty.workerid WHERE tbl_Duty.Day >= @monthStart AND tbl_Duty.Day < @monthEnd AND tbl_Duty.GrowerID = @growerId UNION SELECT( SELECT SUM(Amount) AS 'S' FROM tbl_budget WHERE GrowerID = @growerId AND TimeStamp >= @monthStart AND TimeStamp < @monthEnd) - (SELECT SUM((DATEPART(HOUR, tbl_Shift.totaltime) * tbl_worker.payrate)) AS 'S' FROM tbl_shift INNER JOIN tbl_Duty ON tbl_shift.ShiftID = tbl_Duty.ShiftID INNER JOIN tbl_worker ON tbl_worker.workersid = tbl_duty.workerid WHERE tbl_Duty.Day >= @monthStart AND tbl_Duty.Day < @monthEnd AND tbl_Duty.GrowerID = @growerId) AS 'S', 'Remaining Budget' AS 'grouper' ORDER BY 'grouper'"; //SqlDataSource2.SelectParameters.Add("month", Convert.ToInt32(DateTime.UtcNow.Month).ToString()); //SqlDataSource2.SelectParameters.Add("monthStart", DateTime.UtcNow.Month.ToString() + "/" + DateTime.UtcNow.Day.ToString() + "/" + DateTime.UtcNow.Year.ToString()); //SqlDataSource2.SelectParameters.Add("monthEnd", (DateTime.UtcNow.Month + 1).ToString() + "/" + DateTime.UtcNow.Day.ToString() + "/" + DateTime.UtcNow.Year.ToString()); if (!IsPostBack) { SqlDataSource2.SelectParameters.Add("monthStart", dt.Month.ToString() + "/1/" + dt.Year.ToString()); SqlDataSource2.SelectParameters.Add("monthEnd", (dt.Month + 1).ToString() + "/1/" + dt.Year.ToString()); SqlDataSource2.SelectParameters.Add("growerId", Session["Id"].ToString()); } SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["KiwihortData"].ConnectionString); con.Open(); try { //SqlCommand comChart = new SqlCommand(); //comChart.Connection = con; //comChart.CommandType = System.Data.CommandType.StoredProcedure; //comChart.CommandText = "ViewBudgetAndWorkerPay"; ////comChart.Parameters.AddWithValue("@loops", 20); ////comChart.Parameters.AddWithValue("@growerId", Session["Id"].ToString()); ////comChart.Parameters.AddWithValue("@startDate", DateTime.UtcNow.Month.ToString() + "/1/" + DateTime.UtcNow.Year.ToString()); ////comChart.Parameters.AddWithValue("@endDate", (DateTime.UtcNow.Month + 1).ToString() + "/1/" + DateTime.UtcNow.Year.ToString()); //comChart.Parameters.AddWithValue("@loops", 20); //comChart.Parameters.AddWithValue("@growerId", "grower4"); //comChart.Parameters.AddWithValue("@startDate", "1-1-2017"); //comChart.Parameters.AddWithValue("@endDate", "2-1-2017"); //Chart1.DataSourceID = ""; //Chart1.DataSource = comChart.ExecuteReader(); //Chart1.Series[0].XValueMember = "month"; //Chart1.Series[0].YValueMembers = "Workers Paid"; //Chart1.DataBind(); SqlDataSource1.SelectParameters.Clear(); SqlDataSource1.SelectParameters.Add("loops", Convert.ToString(6)); SqlDataSource1.SelectParameters.Add("growerId", Session["Id"].ToString()); SqlDataSource1.SelectParameters.Add("startDate", dt.Month.ToString() + "/1/" + dt.Year.ToString()); SqlDataSource1.SelectParameters.Add("endDate", (dt.Month + 1).ToString() + "/1/" + dt.Year.ToString()); } catch (Exception err6) { Session["error"] = err6.ToString(); Response.Redirect("~/Debug.aspx"); } try { SqlCommand comWorkerCheck = new SqlCommand("SELECT COUNT(DISTINCT WorkerID) FROM tbl_Duty WHERE Day >= @start AND Day < @end ", con); //comWorkerCheck.Parameters.AddWithValue("@0", Session["Id"].ToString()); //AND GrowerId = @0" comWorkerCheck.Parameters.AddWithValue("@start", dt.AddMonths(-3).Month.ToString() + "/1/" + dt.AddMonths(-3).Year.ToString()); comWorkerCheck.Parameters.AddWithValue("@end", dt.AddMonths(-2).Month.ToString() + "/1/" + dt.AddMonths(-2).Year.ToString()); lbl_monthVal1.Text = comWorkerCheck.ExecuteScalar().ToString(); comWorkerCheck.Parameters.Clear(); lbl_month1.Text = dt.AddMonths(-3).ToString("MMM"); comWorkerCheck.Parameters.AddWithValue("@start", dt.AddMonths(-2).Month.ToString() + "/1/" + dt.AddMonths(-2).Year.ToString()); comWorkerCheck.Parameters.AddWithValue("@end", dt.AddMonths(-1).Month.ToString() + "/1/" + dt.AddMonths(-1).Year.ToString()); lbl_monthVal2.Text = comWorkerCheck.ExecuteScalar().ToString(); comWorkerCheck.Parameters.Clear(); lbl_month2.Text = dt.AddMonths(-2).ToString("MMM"); comWorkerCheck.Parameters.AddWithValue("@start", dt.AddMonths(-1).Month.ToString() + "/1/" + dt.AddMonths(-1).Year.ToString()); comWorkerCheck.Parameters.AddWithValue("@end", dt.Month.ToString() + "/1/" + dt.Year.ToString()); lbl_monthVal3.Text = comWorkerCheck.ExecuteScalar().ToString(); comWorkerCheck.Parameters.Clear(); lbl_month3.Text = dt.AddMonths(-1).ToString("MMM"); comWorkerCheck.Parameters.AddWithValue("@start", dt.Month.ToString() + "/1/" + dt.Year.ToString()); comWorkerCheck.Parameters.AddWithValue("@end", dt.AddMonths(1).Month.ToString() + "/1/" + dt.AddMonths(1).Year.ToString()); lbl_monthVal4.Text = comWorkerCheck.ExecuteScalar().ToString(); comWorkerCheck.Dispose(); } catch (Exception err5) { Session["error"] = err5.ToString(); Response.Redirect("~/Debug.aspx"); } try { //SqlCommand comTotalAmount = new SqlCommand("SELECT SUM(Amount) FROM tbl_budget WHERE GrowerID = @0 AND TimeStamp >= @monthStart AND TimeStamp < @monthEnd", con); SqlCommand comTotalAmount = new SqlCommand("SELECT SUM(Amount) FROM tbl_budget WHERE GrowerID = @0 AND TimeStamp >= @monthStart AND TimeStamp < @monthEnd", con); comTotalAmount.Parameters.AddWithValue("@0", Session["Id"].ToString()); comTotalAmount.Parameters.AddWithValue("@monthStart", dt.Month.ToString() + "/1/" + dt.Year.ToString()); comTotalAmount.Parameters.AddWithValue("@monthEnd", dt.AddMonths(1).Month.ToString() + "/1/" + dt.AddMonths(1).Year.ToString()); SqlCommand comRemainingAmount = new SqlCommand("SELECT( SELECT SUM(Amount) AS 'S' FROM tbl_budget WHERE GrowerID = @growerId AND TimeStamp >= @monthStart AND TimeStamp < @monthEnd) - ( SELECT SUM((DATEPART(HOUR, tbl_Shift.totaltime) * tbl_worker.payrate)) AS 'S' FROM tbl_shift INNER JOIN tbl_Duty ON tbl_shift.ShiftID = tbl_Duty.ShiftID INNER JOIN tbl_worker ON tbl_worker.workersid = tbl_duty.workerid WHERE tbl_Duty.Day >= @monthStart AND tbl_Duty.Day < @monthEnd AND tbl_Duty.GrowerID = @growerId) AS 'S'", con); comRemainingAmount.Parameters.AddWithValue("@growerId", Session["Id"].ToString()); comRemainingAmount.Parameters.AddWithValue("@monthStart", dt.Month.ToString() + "/1/" + dt.Year.ToString()); comRemainingAmount.Parameters.AddWithValue("@monthEnd", dt.AddMonths(1).Month.ToString() + "/1/" + dt.AddMonths(1).Year.ToString()); SqlDataReader readerTotalAmount = comTotalAmount.ExecuteReader(); SqlDataReader readerRemainingAmount = comRemainingAmount.ExecuteReader(); if (readerTotalAmount.HasRows && readerRemainingAmount.HasRows) { if (readerTotalAmount.Read()) { if (readerRemainingAmount.Read()) { try { lbl_percentage2.Text = (Convert.ToDecimal(readerRemainingAmount[0]) * (100 / Convert.ToDecimal(readerTotalAmount[0]))).ToString("#,##0.00") + "%"; } catch { //Should this display something to tell the user some data was missing? } //string amountVar = ; //if (!amountVar.Contains('.')) //{ // amountVar = amountVar + ".00"; //} //else if (amountVar.Length - amountVar.IndexOf(".") == 2) //{ // amountVar = amountVar + "0"; //} //lbl_amount2.Text = ; //if (!lbl_amount2.Text.Contains('.')) //{ // lbl_amount2.Text = lbl_amount2.Text + ".00"; //} //else if (lbl_amount2.Text.Length - lbl_amount2.Text.IndexOf(".") == 2) //{ // lbl_amount2.Text = lbl_amount2.Text + "0"; //} try { lbl_amount2.Text = "$" + Convert.ToDecimal(readerRemainingAmount[0].ToString()).ToString("#,##0.00") + "/$" + Convert.ToDecimal(readerTotalAmount[0].ToString()).ToString("#,##0.00"); } catch { //Should this display something to tell the user some data was missing? } } } } readerTotalAmount.Close(); readerRemainingAmount.Close(); comRemainingAmount.Dispose(); comTotalAmount.Dispose(); } catch (Exception err4) { Session["error"] = err4.ToString(); Response.Redirect("~/Debug.aspx"); } try { SqlCommand com = new SqlCommand("SELECT count(*) FROM tbl_address WHERE AddressId = @0", con); com.Parameters.AddWithValue("@0", Session["Id"]); if (Convert.ToInt32(com.ExecuteScalar()) == 0) { ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('You need to fill in your address details'); window.location='" + Request.ApplicationPath + "Contractor/ContractorProfile.aspx';", true); } com.Dispose(); } catch (Exception err1) { Session["error"] = err1.ToString(); Response.Redirect("~/Debug.aspx"); } try { SqlCommand comAddress = new SqlCommand("SELECT Region FROM tbl_address WHERE AddressId = @0", con); comAddress.Parameters.AddWithValue("@0", Session["Id"].ToString()); if (comAddress.ExecuteScalar() != DBNull.Value) { SqlCommand comAddress2 = new SqlCommand("SELECT AddressId FROM tbl_address WHERE Region = @0", con); comAddress2.Parameters.AddWithValue("@0", Convert.ToString(comAddress.ExecuteScalar())); SqlDataReader readerAddress2 = comAddress2.ExecuteReader(); var count = 0; if (readerAddress2.HasRows) { while (readerAddress2.Read()) { SqlCommand comWorker = new SqlCommand("SELECT * FROM tbl_worker WHERE WorkersID = @0", con); comWorker.Parameters.AddWithValue("@0", readerAddress2["AddressId"].ToString()); SqlDataReader readerWorker = comWorker.ExecuteReader(); if (readerWorker.HasRows) { count++; } comWorker.Dispose(); readerWorker.Close(); } } //lbl_workerRegion.Text = Convert.ToString(count); comAddress2.Dispose(); readerAddress2.Close(); } else { //lbl_workerRegion.Text = "?"; } comAddress.Dispose(); } catch (Exception err2) { Session["error"] = err2.ToString(); Response.Redirect("~/Debug.aspx"); } try { SqlCommand comDuty = new SqlCommand("SELECT * FROM tbl_Duty WHERE GrowerID = @0", con); comDuty.Parameters.AddWithValue("@0", Session["Id"]); SqlDataReader readerDuty = comDuty.ExecuteReader(); comDuty.Dispose(); lbl_workerTodayContent.Text = ""; lbl_workerTomorrowContent.Text = ""; if (readerDuty.HasRows) { while (readerDuty.Read()) { SqlCommand comWorker2 = new SqlCommand("SELECT * FROM tbl_worker WHERE WorkersID = @0", con); comWorker2.Parameters.AddWithValue("@0", readerDuty["WorkerID"]); SqlDataReader readerWorker = comWorker2.ExecuteReader(); if (readerWorker.Read()) { dc = new dateConverter(); DateTime date = Convert.ToDateTime(readerDuty["Day"]); DateTime dtc = dc.convertUTCtoNZT(DateTime.UtcNow); string img = "../img/14456900_1036563233107787_1965655255_o.jpg"; if (readerWorker["Picture"] != DBNull.Value) { img = readerWorker["Picture"].ToString().TrimStart('~'); } if (DateTime.Compare(date.Date, dtc.Date) == 0) { lbl_workerTodayContent.Text += "<div class=\"workingSingleItem\">" + "<img src = \"" + img + "\" class=\"workingTodayBoxImage\" />" + "<h3>" + readerWorker["FirstName"] + " " + readerWorker["LastName"] + "</h3>" + "</div>"; } DateTime dtt = dtc.AddDays(1); if (DateTime.Compare(date.Date, dtt.Date) == 0) { lbl_workerTomorrowContent.Text += "<div class=\"workingSingleItem\">" + "<img src = \"" + img + "\" class=\"workingTodayBoxImage\" />" + "<h3>" + readerWorker["FirstName"] + " " + readerWorker["LastName"] + "</h3>" + "</div>"; } } readerWorker.Close(); } } readerDuty.Close(); } catch (Exception err3) { Session["error"] = err3.ToString(); Response.Redirect("~/Debug.aspx"); } con.Close(); con.Dispose(); if (lbl_percentage2.Text.Contains("X%")) { lbl_percentage2.Text = "0%"; lbl_amount2.Text = "$0.00"; } if (Convert.ToDecimal(lbl_percentage2.Text.Replace('%', ' ')) <= 0) { System.Drawing.Color[] gg = new System.Drawing.Color[2]; gg[0] = System.Drawing.Color.FromArgb(255, 203, 203, 203); gg[1] = System.Drawing.Color.FromArgb(255, 255, 0, 0); lbl_percentage2.Attributes.Add("style", "color:red"); Chart1.PaletteCustomColors = gg; } }
protected void cbo_weekStart_SelectedIndexChanged(object sender, EventArgs e) { var wdgy = cbo_weekStart.SelectedIndex; SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["KiwihortData"].ConnectionString); try { con.Open(); SqlCommand com = new SqlCommand("SELECT * FROM tbl_Duty WHERE Day > @0 AND Day < @1 AND WorkerID like @2 AND GrowerID like @3", con); com.Parameters.AddWithValue("@0", DateTime.ParseExact(cbo_weekStart.SelectedValue, "M/d/yyyy", CultureInfo.InvariantCulture).AddDays(-1)); com.Parameters.AddWithValue("@1", DateTime.ParseExact(cbo_weekStart.SelectedValue, "M/d/yyyy", CultureInfo.InvariantCulture).AddDays(7)); com.Parameters.AddWithValue("@2", Session["Id"]); com.Parameters.AddWithValue("@3", cbo_contractors.SelectedValue); SqlDataReader reader = com.ExecuteReader(); com.Dispose(); txt_mondayStart.Text = "--:-- --"; txt_mondayEnd.Text = "--:-- --"; txt_tuesdayStart.Text = "--:-- --"; txt_tuesdayEnd.Text = "--:-- --"; txt_wednesdayStart.Text = "--:-- --"; txt_wednesdayEnd.Text = "--:-- --"; txt_thursdayStart.Text = "--:-- --"; txt_thursdayEnd.Text = "--:-- --"; txt_fridayStart.Text = "--:-- --"; txt_fridayEnd.Text = "--:-- --"; txt_saturdayStart.Text = "--:-- --"; txt_saturdayEnd.Text = "--:-- --"; txt_sundayStart.Text = "--:-- --"; txt_sundayEnd.Text = "--:-- --"; dateConverter d = new dateConverter(); if (reader.HasRows) { while (reader.Read()) { for (var count = 0; count < 7; count++) { //var test2 = reader.GetString(reader.GetOrdinal("Day")); //var test3 = Convert.ToDateTime(reader.GetString(reader.GetOrdinal("Day"))); //var test4 = d.convertUTCtoNZT(Convert.ToDateTime(reader["Day"])); //var test5 = DateTime.ParseExact(cbo_weekStart.SelectedValue, "MM/dd/yyyy", CultureInfo.InvariantCulture).AddDays(count); if (Convert.ToDateTime(reader["Day"]) == DateTime.ParseExact(cbo_weekStart.SelectedValue, "M/d/yyyy", CultureInfo.InvariantCulture).AddDays(count)) { SqlCommand com2 = new SqlCommand("SELECT Shiftstarttime FROM tbl_Shift WHERE ShiftID = @0", con); com2.Parameters.AddWithValue("@0", Convert.ToString(reader["ShiftID"])); SqlCommand com3 = new SqlCommand("SELECT ShiftendTime FROM tbl_Shift WHERE ShiftID = @0", con); com3.Parameters.AddWithValue("@0", Convert.ToString(reader["ShiftID"])); switch (count) { case 0: txt_mondayStart.Text = Convert.ToString(com2.ExecuteScalar()); txt_mondayEnd.Text = Convert.ToString(com3.ExecuteScalar()); break; case 1: txt_tuesdayStart.Text = Convert.ToString(com2.ExecuteScalar()); txt_tuesdayEnd.Text = Convert.ToString(com3.ExecuteScalar()); break; case 2: txt_wednesdayStart.Text = Convert.ToString(com2.ExecuteScalar()); txt_wednesdayEnd.Text = Convert.ToString(com3.ExecuteScalar()); break; case 3: txt_thursdayStart.Text = Convert.ToString(com2.ExecuteScalar()); txt_thursdayEnd.Text = Convert.ToString(com3.ExecuteScalar()); break; case 4: txt_fridayStart.Text = Convert.ToString(com2.ExecuteScalar()); txt_fridayEnd.Text = Convert.ToString(com3.ExecuteScalar()); break; case 5: txt_saturdayStart.Text = Convert.ToString(com2.ExecuteScalar()); txt_saturdayEnd.Text = Convert.ToString(com3.ExecuteScalar()); break; case 6: txt_sundayStart.Text = Convert.ToString(com2.ExecuteScalar()); txt_sundayEnd.Text = Convert.ToString(com3.ExecuteScalar()); break; } com2.Dispose(); com3.Dispose(); } } } //ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('The work for the selected week has been listed.');", true); } else { //ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('You have no work for the selected week.');", true); } //reader.Close(); } catch (Exception err3) { Session["error"] = err3.ToString(); Response.Redirect("~/Debug.aspx"); } con.Close(); con.Dispose(); }
protected void Page_Load(object sender, EventArgs e) { if (Session["Id"] == null) { Response.Redirect("~/login.aspx"); } ((Label)Master.FindControl("lbl_title")).Text = "Dashboard"; SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["KiwihortData"].ConnectionString); try { dateConverter dc = new dateConverter(); DateTime dt = dc.convertUTCtoNZT(DateTime.UtcNow); con.Open(); SqlCommand comDuty = new SqlCommand("SELECT * FROM tbl_Duty WHERE Day = @0 AND WorkerID = @1", con); comDuty.Parameters.AddWithValue("@0", dt.Month.ToString() + "/" + dt.Day.ToString() + "/" + dt.Year.ToString()); comDuty.Parameters.AddWithValue("@1", Session["Id"]); SqlDataReader readerDuty = comDuty.ExecuteReader(); comDuty.Dispose(); if (readerDuty.HasRows) { if (readerDuty.Read()) { SqlCommand comGrower = new SqlCommand("SELECT * FROM tbl_grower WHERE GrowersId = @0", con); comGrower.Parameters.AddWithValue("@0", readerDuty["GrowerID"].ToString()); SqlDataReader readerGrower = comGrower.ExecuteReader(); comGrower.Dispose(); if (readerGrower.Read()) { lbl_contractorValueToday.Text = readerGrower["FirstName"].ToString() + " " + readerGrower["LastName"].ToString(); } readerGrower.Close(); SqlCommand comShift = new SqlCommand("SELECT Shiftstarttime, ShiftendTime, [dbo].[tbl_pay].[pay] FROM tbl_Shift INNER JOIN tbl_duty ON tbl_shift.shiftid = tbl_duty.shiftid INNER JOIN tbl_worker ON tbl_duty.workerid=tbl_worker.workersid INNER JOIN [dbo].[tbl_pay] ON [dbo].[tbl_pay].[payID]=[dbo].[tbl_worker].[payrate] WHERE tbl_Shift.ShiftID = @0 AND tbl_duty.workerid = @1", con); comShift.Parameters.AddWithValue("@0", readerDuty["ShiftID"]); comShift.Parameters.AddWithValue("@1", Session["Id"]); SqlDataReader readerShift = comShift.ExecuteReader(); comShift.Dispose(); if (readerShift.Read()) { lbl_startTimeToday.Text = Convert.ToDateTime(readerShift["Shiftstarttime"].ToString().Substring(0, 5)).ToString("h:mm tt"); lbl_endTimeToday.Text = Convert.ToDateTime(readerShift["ShiftendTime"].ToString().Substring(0, 5)).ToString("h:mm tt"); if (!readerShift["pay"].ToString().Contains('.')) { lbl_hourlyRateValueToday.Text = "$" + readerShift["pay"].ToString() + ".00"; } else if (readerShift["pay"].ToString().Length - readerShift["pay"].ToString().IndexOf(".") == 2) { lbl_hourlyRateValueToday.Text = "$" + readerShift["pay"].ToString() + "0"; } else { lbl_hourlyRateValueToday.Text = "$" + readerShift["pay"].ToString(); } } readerShift.Close(); } } SqlCommand comPay = new SqlCommand("SELECT pay FROM [dbo].[tbl_pay] inner join tbl_worker on [dbo].[tbl_pay].payID=tbl_worker.payrate WHERE workersid = @0", con); comPay.Parameters.AddWithValue("@0", Session["Id"]); SqlDataReader readerPay = comPay.ExecuteReader(); if (readerPay.Read()) { decimal d = (decimal)readerPay["pay"]; lbl_hourlyRateValueToday.Text = "$" + decimal.Round(d, 2, MidpointRounding.AwayFromZero).ToString(); lbl_hourlyRateValueTomorrow.Text = "$" + decimal.Round(d, 2, MidpointRounding.AwayFromZero).ToString(); } readerPay.Close(); comDuty = new SqlCommand("SELECT * FROM tbl_Duty WHERE Day = @0 AND WorkerID = @1", con); comDuty.Parameters.AddWithValue("@0", dt.AddDays(1).Month.ToString() + "/" + dt.AddDays(1).Day.ToString() + "/" + dt.AddDays(1).Year.ToString()); comDuty.Parameters.AddWithValue("@1", Session["Id"]); readerDuty = comDuty.ExecuteReader(); comDuty.Dispose(); if (readerDuty.HasRows) { if (readerDuty.Read()) { SqlCommand comGrower = new SqlCommand("SELECT * FROM tbl_grower WHERE GrowersId = @0", con); comGrower.Parameters.AddWithValue("@0", readerDuty["GrowerID"].ToString()); SqlDataReader readerGrower = comGrower.ExecuteReader(); comGrower.Dispose(); if (readerGrower.Read()) { lbl_contractorValueTomorrow.Text = readerGrower["FirstName"].ToString() + " " + readerGrower["LastName"].ToString(); } readerGrower.Close(); SqlCommand comShift = new SqlCommand("SELECT Shiftstarttime, ShiftendTime FROM tbl_Shift INNER JOIN tbl_duty ON tbl_shift.shiftid = tbl_duty.shiftid INNER JOIN tbl_worker ON tbl_duty.workerid=tbl_worker.workersid WHERE tbl_Shift.ShiftID = @0 AND tbl_duty.workerid = @1", con); comShift.Parameters.AddWithValue("@0", readerDuty["ShiftID"]); comShift.Parameters.AddWithValue("@1", Session["Id"]); SqlDataReader readerShift = comShift.ExecuteReader(); comShift.Dispose(); if (readerShift.Read()) { lbl_startTimeTomorrow.Text = Convert.ToDateTime(readerShift["Shiftstarttime"].ToString().Substring(0, 5)).ToString("h:mm tt"); lbl_endTimeTomorrow.Text = Convert.ToDateTime(readerShift["ShiftendTime"].ToString().Substring(0, 5)).ToString("h:mm tt"); } readerShift.Close(); } } readerDuty.Close(); } catch (Exception err1) { Session["error"] = err1.ToString(); Response.Redirect("~/Debug.aspx"); } try { SqlCommand comDuty2 = new SqlCommand("SELECT * FROM tbl_grower WHERE GrowersId = @0", con); comDuty2.Parameters.AddWithValue("@0", DateTime.Today.AddDays(1).Month.ToString() + "/" + DateTime.Today.AddDays(1).Day.ToString() + "/" + DateTime.Today.AddDays(1).Year.ToString()); comDuty2.Parameters.AddWithValue("@1", Session["Id"]); SqlDataReader readerDuty2 = comDuty2.ExecuteReader(); comDuty2.Dispose(); if (readerDuty2.HasRows) { if (readerDuty2.Read()) { SqlCommand comGrower = new SqlCommand("SELECT * FROM tbl_grower WHERE GrowersId = @0", con); comGrower.Parameters.AddWithValue("@0", readerDuty2["GrowerID"].ToString()); SqlDataReader readerGrower = comGrower.ExecuteReader(); comGrower.Dispose(); if (readerGrower.Read()) { lbl_contractorValueTomorrow.Text = readerGrower["FirstName"].ToString() + " " + readerGrower["LastName"].ToString(); } readerGrower.Close(); SqlCommand comShift = new SqlCommand("SELECT * FROM tbl_Shift WHERE ShiftID = @0", con); comShift.Parameters.AddWithValue("@0", readerDuty2["ShiftID"]); SqlDataReader readerShift = comShift.ExecuteReader(); comShift.Dispose(); if (readerShift.Read()) { lbl_startTimeTomorrow.Text = Convert.ToDateTime(readerShift["Shiftstarttime"].ToString().Substring(0, 5)).ToString("h:mm tt"); lbl_endTimeTomorrow.Text = Convert.ToDateTime(readerShift["ShiftendTime"].ToString().Substring(0, 5)).ToString("h:mm tt"); } readerShift.Close(); } } readerDuty2.Close(); } catch (Exception err2) { Session["error"] = err2.ToString(); Response.Redirect("~/Debug.aspx"); } con.Close(); con.Dispose(); if (!IsPostBack) { sds_growers.SelectParameters.Add("0", Session["Id"].ToString()); DataSourceSelectArguments args = new DataSourceSelectArguments(); DataView view = (DataView)sds_growers.Select(args); DataTable dt = view.ToTable(); dt.Columns.Add("FullName", typeof(string), "FirstName + ' ' + LastName"); cbo_contractors.DataSource = dt; cbo_contractors.DataTextField = "FullName"; cbo_contractors.DataValueField = "GrowersId"; cbo_contractors.DataBind(); var weekStart = DateTime.Today.Date; var firstRun = true; for (var count = 0; count < 8; count++, weekStart = weekStart.AddDays(1)) { while (weekStart.DayOfWeek.ToString() != "Monday") { if (firstRun) { weekStart = weekStart.AddDays(-1); } else { weekStart = weekStart.AddDays(1); } } firstRun = false; cbo_weekStart.Items.Add(new ListItem(weekStart.Day.ToString() + "/" + weekStart.Month.ToString() + "/" + weekStart.Year.ToString(), weekStart.Month.ToString() + "/" + weekStart.Day.ToString() + "/" + weekStart.Year.ToString())); //Problems. Big problems? - probably fixed } cbo_weekStart_SelectedIndexChanged(null, null); } //cbo_weekStart_SelectedIndexChanged(null, null); }
protected void Page_Load(object moveIcon, EventArgs e) { btn_excel.Visible = false; int cbo_day_index = cbo_day.SelectedIndex; int cbo_week_index = cbo_week.SelectedIndex; int cbo_farm_index = cbo_farm.SelectedIndex; ((Label)Master.FindControl("lbl_title")).Text = "Report"; SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["KiwihortData"].ConnectionString); con.Open(); dateConverter d = new dateConverter(); SqlCommand comMin = new SqlCommand("SELECT min(Day) FROM tbl_Duty", con); SqlCommand comMax = new SqlCommand("SELECT max(Day) FROM tbl_Duty", con); SqlDataReader readerMin = comMin.ExecuteReader(); readerMin.Read(); if (readerMin.HasRows == true && String.Compare(readerMin.GetValue(0).ToString(), null) != 0) { btn_submit.Enabled = true; DateTime dayMin = d.convertUTCtoNZT(readerMin.GetDateTime(0)); DateTime dayMax = d.convertUTCtoNZT((DateTime)comMax.ExecuteScalar()); cbo_day.Items.Clear(); cbo_farm.Items.Clear(); cbo_day.Items.Add("Select a day"); for (var count = dayMax; count >= dayMin; count = count.AddDays(-1)) { cbo_day.Items.Add(new ListItem(count.Date.ToString("d/M/yyyy") + " - " + count.DayOfWeek.ToString(), d.convertNZTtoUTC(count.Date).ToString())); } var firstRun = true; cbo_week.Items.Clear(); cbo_week.Items.Add("Select a week"); //for (var count = dayMin; count <= dayMax; dayMin = dayMin.AddDays(1), count = count.AddDays(1)) //{ // while (dayMin.DayOfWeek.ToString() != "Monday") // { // if (firstRun) // { // dayMin = dayMin.AddDays(-1); // } // else // { // dayMin = dayMin.AddDays(1); // count = count.AddDays(1); // } // } // firstRun = false; // cbo_week.Items.Add(new ListItem(dayMin.Day.ToString() + "/" + dayMin.Month.ToString() + "/" + dayMin.Year.ToString(), d.convertNZTtoUTC(dayMin).ToString())); //} for (var count = dayMax; count >= dayMin; dayMax = dayMax.AddDays(-1), count = count.AddDays(-1)) { while (dayMax.DayOfWeek.ToString() != "Monday") { if (firstRun) { dayMax = dayMax.AddDays(-1); } else { dayMax = dayMax.AddDays(-1); count = count.AddDays(-1); } } firstRun = false; cbo_week.Items.Add(new ListItem(dayMax.Day.ToString() + "/" + dayMax.Month.ToString() + "/" + dayMax.Year.ToString(), d.convertNZTtoUTC(dayMax).ToString())); } } else { btn_submit.Enabled = false; } SqlCommand comFarm = new SqlCommand("SELECT Farm_Name, farmid FROM tbl_farms WHERE GrowerID = @0", con); comFarm.Parameters.AddWithValue("@0", Session["Id"].ToString()); SqlDataReader reader = comFarm.ExecuteReader(); if (reader.HasRows) { cbo_farm.Items.Add("Select a farm"); while (reader.Read()) { cbo_farm.Items.Add(new ListItem(reader.GetString(0), reader.GetString(1))); } } cbo_day.SelectedIndex = cbo_day_index; cbo_week.SelectedIndex = cbo_week_index; cbo_farm.SelectedIndex = cbo_farm_index; }
protected void Page_Load(object sender, EventArgs e) { int cbo_day_index = cbo_day.SelectedIndex; int cbo_farm_index = cbo_farm.SelectedIndex; GridView1.Visible = false; SqlConnection con2 = new SqlConnection(WebConfigurationManager.ConnectionStrings["KiwihortData"].ConnectionString); con2.Open(); dateConverter d = new dateConverter(); SqlCommand comMin = new SqlCommand("SELECT min(Day) FROM tbl_Duty", con2); SqlCommand comMax = new SqlCommand("SELECT max(Day) FROM tbl_Duty", con2); SqlDataReader readerMin = comMin.ExecuteReader(); readerMin.Read(); if (readerMin.HasRows == true && String.Compare(readerMin.GetValue(0).ToString(), null) != 0) { btn_submit.Enabled = true; DateTime dayMin = d.convertUTCtoNZT(readerMin.GetDateTime(0)); DateTime dayMax = d.convertUTCtoNZT((DateTime)comMax.ExecuteScalar()); cbo_day.Items.Clear(); cbo_farm.Items.Clear(); cbo_day.Items.Add("Select a day"); for (var count = dayMax; count >= dayMin; count = count.AddDays(-1)) { cbo_day.Items.Add(new ListItem(count.Date.ToString("d/M/yyyy") + " - " + count.DayOfWeek.ToString(), d.convertNZTtoUTC(count.Date).ToString())); } } else { } SqlCommand comFarm = new SqlCommand("SELECT Farm_Name, farmid FROM tbl_farms WHERE GrowerID = @0", con2); comFarm.Parameters.AddWithValue("@0", Session["Id"].ToString()); SqlDataReader reader1 = comFarm.ExecuteReader(); if (reader1.HasRows) { cbo_farm.Items.Add("Select a farm"); while (reader1.Read()) { cbo_farm.Items.Add(new ListItem(reader1.GetString(0), reader1.GetString(1))); } } cbo_day.SelectedIndex = cbo_day_index; cbo_farm.SelectedIndex = cbo_farm_index; SqlDataSource1.SelectCommand = "SELECT RTRIM(firstname) + ' ' + RTRIM(lastname) AS name, '$' + CAST(CAST(tbl_pay.pay AS MONEY) AS VARCHAR(MAX)) AS payrate FROM tbl_worker INNER JOIN tbl_pay ON tbl_worker.payrate = tbl_pay.payID INNER JOIN[dbo].[tbl_employees] on[dbo].[tbl_employees].[workersid]= [tbl_worker].[WorkersId] where[dbo].[tbl_employees].[growersid]=@0"; SqlDataSource1.SelectParameters.Clear(); SqlDataSource1.SelectParameters.Add("0", Session["Id"].ToString()); SqlDataSource1.DataBind(); if (Session["Id"] == null) { Response.Redirect("~/login.aspx"); } ((Label)Master.FindControl("lbl_title")).Text = "Worker Pay"; // RequiredFieldValidator2.ControlToValidate = txtsubject; con2.Close(); if (!IsPostBack) { SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["KiwihortData"].ConnectionString); con.Open(); SqlCommand com = new SqlCommand("SELECT [tbl_worker].[FirstName], [tbl_worker].[LastName], [tbl_worker].[WorkersId] FROM [tbl_worker] INNER JOIN [tbl_login] ON [tbl_login].[Id]=[tbl_worker].[WorkersId] INNER JOIN [dbo].[tbl_employees] on [dbo].[tbl_employees].[workersid]= [tbl_worker].[WorkersId] where [dbo].[tbl_employees].[growersid]=@0 ORDER BY [tbl_worker].[FirstName]", con); com.Parameters.AddWithValue("@0", Session["Id"]); SqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { if (reader.HasRows == true && String.Compare(reader.GetValue(0).ToString(), null) != 0) { cbo_worker.Items.Add(new ListItem(Convert.ToString(reader["FirstName"]) + " " + Convert.ToString(reader["LastName"]), Convert.ToString(reader["workersId"]))); } } cbo_worker_SelectedIndexChanged(null, null); } }