private void BindDDLs() { trainerDDL_Ins.DataSource = dtTrainerResults.Copy(); trainerDDL_Ins.DataTextField = "trainer"; trainerDDL_Ins.DataValueField = "ID"; trainerDDL_Ins.DataBind(); DDTrainer.DataSource = dtTrainerResults.Copy(); DDTrainer.DataTextField = "trainer"; DDTrainer.DataValueField = "ID"; DDTrainer.DataBind(); string SQLstring = @"SELECT 'All' AS Program UNION ALL SELECT * FROM ( SELECT TOP 1000 Program FROM PAYOUTschedule WHERE Program != '' AND Program IS NOT NULL GROUP BY Program ORDER BY Program )x"; programDDL.DataSource = Queries.GetResultsFromQueryString(SQLstring); programDDL.DataTextField = "Program"; programDDL.DataValueField = "Program"; programDDL.DataBind(); }
private void SubmitCommissionOverrideSummaries(DateTime CheckDate) { Dictionary <string, string> parameters = new Dictionary <string, string>(); string SQLstring = string.Format(@";WITH StartDateTable AS ( SELECT MAX(DATEADD(DAY, 1, [WeekEnding])) [StartDate] FROM [PAYOUTPMCheckDates] WHERE [WeekEnding] < '{0}' ) SELECT CASE WHEN (SELECT [StartDate] FROM StartDateTable) IS NULL THEN DATEADD(DAY, -14, (SELECT MIN([WeekEnding]) FROM [PAYOUTPMCheckDates])) ELSE (SELECT [StartDate] FROM StartDateTable) END", CheckDate.ToString()); string startDate = Common.ApplyDateFormat(Queries.GetResultsFromQueryString(SQLstring).Rows[0][0].ToString()); string endDate = Common.ApplyDateFormat(CheckDate); parameters.Add("@webStartDate", startDate); parameters.Add("@webEndDate", endDate); parameters.Add("@webTrainer", ""); parameters.Add("@Action", "INSERT"); Queries.ExecuteFromStoreProcedure("spx_PAYOUTsummaryOverrides", parameters); Queries.ExecuteFromStoreProcedure("spx_PAYOUTsummaryCommissionDaily", parameters); }
private bool ValidateSameDaySchedule(DateTime TrainStartDate, DateTime TrainEndDate, string trainerId, string scheduleTrainerId) { if (scheduleTrainerId == " ") { return(false); } string TrainerInScheduleStatus = string.Empty; string SQLstring = string.Format(@" SELECT COUNT(*) FROM [PAYOUTschedule] s INNER JOIN [PAYOUTScheduleTrainer] st ON s.Id = st.ScheduleId WHERE s.[TrainerID] = '{0}' AND ( ('{1}' BETWEEN s.[TrainStartDate] AND s.[TrainEndDate] AND '{2}' BETWEEN s.[TrainStartDate] AND s.[TrainEndDate]) OR (s.[TrainStartDate] BETWEEN '{1}' AND '{2}') OR (s.[TrainEndDate] BETWEEN '{1}' AND '{2}') ) AND st.Id <> '{3}'", trainerId, Common.ApplyDateFormat(TrainStartDate), Common.ApplyDateFormat(TrainEndDate), scheduleTrainerId); DataTable dt = Queries.GetResultsFromQueryString(SQLstring); if (dt.Rows[0][0].ToString() != "0") { return(true); } return(false); }
protected void addBtn_Click(object sender, EventArgs e) { string Firstname = Request.Form["TxtFName"].ToString(); string lastname = Request.Form["TxtLname"].ToString(); bool Active = true; if (CHKActive.Checked) { Active = true; } else { Active = false; } DataTable dt = Queries.GetResultsFromQueryString("select MAX(ID) + 1 from [PAYOUTtrainManager]"); string SQLstring = string.Format(@"INSERT INTO [PAYOUTtrainManager] (Firstname, Lastname, Active) VALUES ('{0}', '{1}', {2})", Firstname, lastname, Convert.ToInt32(Active)); Queries.ExecuteFromQueryString(SQLstring); Response.Redirect("TrainerManager.aspx", true); }
private void FillOwner() { List <string> lstOwner = new List <string>(); if (WebProgramDDL.SelectedValue != "--Select--") { string tempStoreName = strStoreNameDDL; if (strStoreNameDDL == "Kroger All") { tempStoreName = "Kroger"; } SQLstring = string.Format(@"SELECT DISTINCT OwnerFirstname + ' ' + OwnerLastname [Owner] FROM PAYOUTsales WHERE StoreName LIKE '{0}%' AND Program = '{1}' {2}", tempStoreName, WebProgramDDL.SelectedValue, WebRotationDDL.SelectedValue != "All" ? string.Format(" AND SalesDate BETWEEN (SELECT StartDate FROM PAYOUTwe WHERE WeekEnding = '{0}') AND '{0}'", WebRotationDDL.SelectedValue) : ""); DataTable dt = Queries.GetResultsFromQueryString(SQLstring); lstOwner = dt.AsEnumerable().Select(f => f.Field <string>("Owner")).ToList(); } lstOwner.Insert(0, "All"); WebOwnerDDL.DataSource = lstOwner; WebOwnerDDL.DataBind(); }
private void BindGridData() { //updateGrid.Update(); //UpdatePanelTopWrapper.Update(); bool isFirstFilter = true; string SqlString; if (userType != "Trainer") { SqlString = @"SELECT o.[Id],t.[Firstname] + ' ' + t.[Lastname] [Employee],o.[StoreName],o.[Program],o.[WeeklyCompensation],o.[Override],convert(varchar(10), o.[EffectiveDate], 23) [EffectiveDate] FROM [PAYOUTOverridesPM] o INNER JOIN PAYOUTtrainer t ON o.TrainerId = t.ID"; } else { SqlString = string.Format(@"SELECT o.[Id],t.[Firstname] + ' ' + t.[Lastname] [Employee],o.[StoreName],o.[Program],o.[WeeklyCompensation],o.[Override],convert(varchar(10), o.[EffectiveDate], 23) [EffectiveDate] FROM [PAYOUTOverridesPM] o INNER JOIN PAYOUTtrainer t ON o.TrainerId = t.ID WHERE t.EmailAddress = '{0}'", user); } SqlString += AddFilterQuery("o.StoreName", sstoreDDL, ref isFirstFilter); SqlString += AddFilterQuery("o.Program", programDDL, ref isFirstFilter); SqlString += AddFilterQuery("t.Id", trainerDDL, ref isFirstFilter); DataTable dt = Queries.GetResultsFromQueryString(SqlString); Session["ResultTable"] = dt; GridView1.DataSource = dt; GridView1.DataBind(); }
private void ProfileScreenSettings(ref DataTable dt) { if (userType == "Trainer") { //SetupCommissionBtn.Visible = false; //SetupOverrideBtn.Visible = false; TrainerBtn.Visible = false; try { string SQLstring = string.Format(@" SELECT [ID] FROM[Payout].[dbo].[PAYOUTtrainer] WHERE[EmailAddress] = '{0}'", user); string trainerId = Queries.GetResultsFromQueryString(SQLstring).Rows[0][0].ToString(); if (dt.Rows.Count > 0) { dt = dt.AsEnumerable().Where(t => t.Field <int?>("TrainerId") == int.Parse(trainerId) || t.Field <int?>("TrainerId") == null).CopyToDataTable(); } } catch { } //foreach (DataRow row in dt.Rows) //Clean Ids from datatable where is not existing //{ // if (row["TrainerId"].ToString() != trainerId) // row["Id"] = null; //} } }
private int InsertRecord(string ScheduleId, string TrainerId, string TrainStartDate, string TrainEndDate) { try { if (TrainerId != "0") { string SQLString = string.Format(@" SELECT COUNT(*) FROM [PAYOUTScheduleTrainer] WHERE ([TrainStartDate] BETWEEN '{0}' AND '{1}' OR [TrainEndDate] BETWEEN '{0}' AND '{1}' OR ([TrainStartDate] <= '{0}' AND [TrainEndDate] >= '{1}')) AND [TrainerId] = {2}", TrainStartDate, TrainEndDate, TrainerId); if (Queries.GetResultsFromQueryString(SQLString).Rows[0][0].ToString() == "0") { SQLString = string.Format(@"INSERT INTO PAYOUTScheduleTrainer (ScheduleId, TrainerId, TrainStartDate, TrainEndDate) VALUES ({0},{1},'{2}','{3}')", ScheduleId, TrainerId, TrainStartDate, TrainEndDate); Queries.ExecuteFromQueryString(SQLString); return(1); } } } catch { return(-1); } return(0); }
private void FillDDls() { string sqlString = "SELECT DISTINCT CASE WHEN StoreName LIKE 'Kroger%' THEN 'Kroger' ELSE StoreName END StoreName FROM PAYOUTsales"; DDLShowCount.DataSource = Queries.GetResultsFromQueryString(sqlString); DDLShowCount.DataValueField = "StoreName"; DDLShowCount.DataBind(); }
private static List <DataTable> BuildReport() { SQLstring = string.Format(@"SELECT StartDate, CONVERT(NVARCHAR(20), [WeekEnding]) WeekEnding FROM PAYOUTweList_fnc('{0}', '{1}') ORDER BY WeekEnding ASC", strRotationDDL, strYearDDL); List <DataTable> lstdtRev = new List <DataTable>(); List <DataTable> lstdtQty = new List <DataTable>(); List <DataTable> lstdtResults = new List <DataTable>(); DataTable dt = Queries.GetResultsFromQueryString(SQLstring); DataTable dtResults, tempTable; if (strRotationDDL == "All") { foreach (DataRow drRotation in dt.Rows) { dtResults = SalesReport(drRotation["StartDate"].ToString(), drRotation["WeekEnding"].ToString(), 2, strYearDDL); tempTable = dtResults.Copy(); tempTable.Columns.Remove("TotalQty"); tempTable.Columns["TotalRev"].ColumnName = drRotation["WeekEnding"].ToString(); lstdtRev.Add(tempTable); tempTable = dtResults.Copy(); tempTable.Columns.Remove("TotalRev"); tempTable.Columns["TotalQty"].ColumnName = drRotation["WeekEnding"].ToString(); lstdtQty.Add(tempTable); } } else { DateTime WeekEnding = Convert.ToDateTime(dt.Rows[0]["WeekEnding"].ToString()); int dayDiff = Convert.ToInt32( (WeekEnding - Convert.ToDateTime(dt.Rows[0]["StartDate"].ToString()) ).TotalDays); for (int i = dayDiff; i >= 0; i--) { dtResults = SalesReport(WeekEnding.AddDays(-i).ToString(), WeekEnding.AddDays(-i).ToString(), 2); tempTable = dtResults.Copy(); tempTable.Columns.Remove("TotalQty"); tempTable.Columns["TotalRev"].ColumnName = Common.ApplyDateFormat(WeekEnding.AddDays(-i).ToString()); lstdtRev.Add(tempTable); tempTable = dtResults.Copy(); tempTable.Columns.Remove("TotalRev"); tempTable.Columns["TotalQty"].ColumnName = Common.ApplyDateFormat(WeekEnding.AddDays(-i).ToString()); lstdtQty.Add(tempTable); } } lstdtResults.Add(MergeTables(lstdtQty)); lstdtResults.Add(MergeTables(lstdtRev)); return(lstdtResults); }
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e) { GridView grv = (GridView)sender; if (StoreNumberTXT.Text == "") { Session["StoreNumber"] = "All"; } else { Session["StoreNumber"] = StoreNumberTXT.Text; } string SQLstring = string.Format("SELECT TOP 1 StartDate FROM PAYOUTwe WHERE WeekEnding = '{0}'", grv.SelectedRow.Cells[0].Text); DataTable dt = Queries.GetResultsFromQueryString(SQLstring); Session["g2StartDate"] = dt.Rows[0][0].ToString(); Session["g2Duration"] = "14"; for (int i = 0; i < grv.SelectedRow.Cells.Count; i++) { string columnName = ((BoundField)((DataControlFieldCell)(grv.SelectedRow.Cells[i])).ContainingField).DataField; switch (columnName.ToLower().Trim()) { case "owner": Session["g2Owner"] = grv.SelectedRow.Cells[i].Text; break; case "hub": Session["g2Hub"] = grv.SelectedRow.Cells[i].Text; break; case "store name": Session["g2StoreName"] = grv.SelectedRow.Cells[i].Text.Replace("amp;", ""); break; case "program": Session["g2Program"] = grv.SelectedRow.Cells[i].Text; break; case "club #": Session["g2StoreNumber"] = grv.SelectedRow.Cells[i].Text; break; case "location": Session["g2Location"] = grv.SelectedRow.Cells[i].Text; break; case "ps id": Session["g2PSID"] = grv.SelectedRow.Cells[i].Text; break; } } Response.Redirect("Grids2.aspx", true); }
private void Fillcontrols() { string SQLstring = string.Format(@" SELECT StartDate, WeekEnding FROM PAYOUTwe WHERE WeekEnding BETWEEN CONVERT(DATE, '{0}') AND CONVERT(DATE, '{1}')", (string)Session["ParamStartDate"], (string)Session["ParamEndDate"]); DataTable dtWE = Queries.GetResultsFromQueryString(SQLstring); ParamStartDate = Convert.ToDateTime(dtWE.Rows[0][0].ToString()).AddDays(-2).ToString("yyyy-MM-dd"); ParamEndDate = Convert.ToDateTime(dtWE.Rows[0][1].ToString()).AddDays(-2).ToString("yyyy-MM-dd"); //ParamStartDate = (string)Session["ParamStartDate"]; //ParamEndDate = (string)Session["ParamEndDate"]; Trainer = (string)Session["Trainer"]; Session["ResultTable"] = new List <DataTable>(); Session["ExportTable"] = new List <DataTable>(); NumberOfWeeks = ((((decimal)(Convert.ToDateTime(ParamEndDate) - Convert.ToDateTime(ParamStartDate)).TotalDays + 1) / 7) % 1) != 0 ? (((int)(Convert.ToDateTime(ParamEndDate) - Convert.ToDateTime(ParamStartDate)).TotalDays + 1) / 7) + 1 : ((int)(Convert.ToDateTime(ParamEndDate) - Convert.ToDateTime(ParamStartDate)).TotalDays + 1) / 7; FillFromDatabase(); List <DataTable> lstResultTables = new List <DataTable>(); lstResultTables = (List <DataTable>)Session["lstResultTables"]; CommissionPanel.Controls.Add(BindGridData(topGrid(), 1)); int incremental = 1; foreach (DataTable dt in lstResultTables) { Panel pnl = new Panel() { CssClass = "TitleBox" }; pnl.Controls.Add(new Label() { Text = "Week #" + incremental.ToString() }); incremental++; CommissionPanel.Controls.Add(pnl); CommissionPanel.Controls.Add(BindGridData(dt, incremental, true)); } }
private void FillFromDatabase() { DataTable dt = new DataTable(); //DateFilters(ref ParamStartDate, ref ParamEndDate); string SQLString = string.Format("SELECT [Id] FROM [PAYOUTPMPayroll] WHERE [CheckDate] = '{0}'", (string)Session["ParamEndDate"]); List <int> dtPayrollIds = Queries.GetResultsFromQueryString(SQLString).AsEnumerable().Select(i => i.Field <int>("Id")).ToList(); if (dtPayrollIds.Count > 0) { SQLString = string.Format(@"SELECT TrainerName [Trainer], Retailer [StoreName], [Program], [WeeklyCompensation], OverridePercentage [Override], TotalSales [Total Sales], [NCR], [Overrides], OverridesDue [Overrides Due] FROM PAYOUTPMOverrideSummary WHERE PMPayroll_Id IN ({0}) AND [TrainerName] = '{1}'", string.Join(", ", dtPayrollIds.ToArray()), (string)Session["Trainer"]); dt = Queries.GetResultsFromQueryString(SQLString); } else { ParamStartDate = (string)Session["ParamStartDate"]; ParamEndDate = (string)Session["ParamEndDate"]; dateFrom.Text = DatePickerFormat(ParamStartDate); dateTo.Text = DatePickerFormat(ParamEndDate); SQLParameters.Add("@webStartDate", ParamStartDate); SQLParameters.Add("@webEndDate", ParamEndDate); SQLParameters.Add("@webTrainer", (string)Session["Trainer"]); SQLParameters.Add("@Action", "SELECT"); dt = Queries.GetResultsFromStoreProcedure("spx_PAYOUTsummaryOverrides", ref SQLParameters).Tables[0]; } DataView dv = dt.DefaultView; //dt = dv.Table.Rows.OfType<DataRow>().Where(f => f.Field<string>("Trainer") == ParamTrainer).CopyToDataTable(); if (dt != null && dt.Rows.Count > 0) { BindDDL(dt, "Program", programDDL); BindDDL(dt, "StoreName", StoreNameDDL); BindDDL(dt, "Trainer", TrainerDDL); } Session["ResultTable"] = dt; Session["dateFrom"] = ParamStartDate; Session["dateTo"] = ParamEndDate; }
private void TrainerTableResults() { string SQLstring = @"SELECT 0 [ID], 'All' [trainer] UNION ALL SELECT [ID],[Firstname] + ' ' + [Lastname] [trainer] FROM [Payout].[dbo].[PAYOUTtrainer] WHERE [Active] = 1"; if (userType == "Trainer") { SQLstring = string.Format("{0} AND EmailAddress = '{1}'", SQLstring, user); } dtTrainerResults = Queries.GetResultsFromQueryString(SQLstring); }
private List <List <PMPaycheck> > GetResultsFromDB() { List <List <PMPaycheck> > lstByBiweek = new List <List <PMPaycheck> >(); List <PMPaycheck> lstPaycheck = new List <PMPaycheck>(); string SQLstring = @"SELECT CheckDate, TrainerId, TrainerName, Manager, [Role], Salary, [Override], Commission, OverPay, UnderPay, OTPremium, VacationPay, TravelTime, ClawBack, Adjustment, AdjustmentComment, PayCheck FROM PAYOUTPMPayroll ORDER BY CheckDate DESC"; DataTable dtPayroll = Queries.GetResultsFromQueryString(SQLstring); SQLstring = @" SELECT WeekEnding FROM [PAYOUTPMCheckDates] WHERE WeekEnding IN (SELECT DISTINCT CheckDate FROM PAYOUTPMPayroll) ORDER BY WeekEnding DESC"; DataTable dtWeekEndings = Queries.GetResultsFromQueryString(SQLstring); foreach (DataRow dr in dtWeekEndings.Rows) { DataView dv = dtPayroll.AsEnumerable().Where(f => f.Field <DateTime>("CheckDate") == Convert.ToDateTime(dr[0].ToString())).CopyToDataTable().DefaultView; var dv1 = dv[0]; foreach (DataRowView item in dv) { lstPaycheck.Add(new PMPaycheck() { CheckDate = Common.ApplyDateFormat(item["CheckDate"].ToString(), 1), TrainerId = int.Parse(item["TrainerId"].ToString()), TrainerName = item["TrainerName"].ToString(), Manager = item["Manager"].ToString() == "" ? null : item[2].ToString(), Role = item["Role"].ToString() == "" ? null : item[3].ToString(), Salary = item["Salary"].ToString() == "" ? 0 : decimal.Parse(item[4].ToString()), Override = item["Override"].ToString() == "" ? 0 : decimal.Parse(item[5].ToString()), Commission = item["Commission"].ToString() == "" ? 0 : decimal.Parse(item[6].ToString()), OverPay = item["OverPay"].ToString() == "" ? 0 : decimal.Parse(item[7].ToString()), UnderPay = item["UnderPay"].ToString() == "" ? 0 : decimal.Parse(item[8].ToString()), OTPremium = item["OTPremium"].ToString() == "" ? 0 : decimal.Parse(item[9].ToString()), VacationPay = item["VacationPay"].ToString() == "" ? 0 : decimal.Parse(item[10].ToString()), TravelTime = item["TravelTime"].ToString() == "" ? 0 : decimal.Parse(item[11].ToString()), ClawBack = item["ClawBack"].ToString() == "" ? 0 : decimal.Parse(item[12].ToString()), Adjustment = item["Adjustment"].ToString() == "" ? 0 : decimal.Parse(item[13].ToString()), AdjustmentComment = item["AdjustmentComment"].ToString() == "" ? null : item[14].ToString(), Paycheck = item["Paycheck"].ToString() == "" ? 0 : decimal.Parse(item[15].ToString()) }); } lstPaycheck.Add(new PMPaycheck()); lstByBiweek.Add(lstPaycheck); } return(lstByBiweek); }
private void LoadDDL() { string DateFormatFixed; string SQLString = @"SELECT Id, CONVERT(NVARCHAR, CONVERT(DATE, WeekEnding), 101) [Week Ending] FROM PAYOUTPMCheckDates ORDER BY WeekEnding DESC"; DataTable dt = Queries.GetResultsFromQueryString(SQLString); webStartDate.DataSource = dt; webStartDate.DataTextField = "Week Ending"; webStartDate.DataValueField = "Id"; webStartDate.DataBind(); DateFormatFixed = Common.ApplyDateFormat(Session["StartDate"].ToString(), 2); webStartDate.SelectedValue = webStartDate.Items.FindByText(DateFormatFixed).Value; webEndDate.DataSource = dt; webEndDate.DataTextField = "Week Ending"; webEndDate.DataValueField = "Id"; webEndDate.DataBind(); DateFormatFixed = Common.ApplyDateFormat(Session["EndDate"].ToString(), 2); webEndDate.SelectedValue = webEndDate.Items.FindByText(DateFormatFixed).Value; SQLString = string.Format( @"SELECT 0 ID, 'All' Trainer UNION ALL SELECT Id, [Firstname] + ' ' + [Lastname] [Trainer] FROM [PAYOUTtrainer] WHERE [Active] = 1 {0} ORDER BY [Trainer]", userType == "Trainer" ? string.Format("AND EmailAddress = '{0}'", SQLString, user) : ""); dt = Queries.GetResultsFromQueryString(SQLString); webTrainer.DataSource = dt; webTrainer.DataTextField = "Trainer"; webTrainer.DataValueField = "Id"; webTrainer.DataBind(); webTrainer.SelectedValue = "0"; SQLString = "SELECT 'All' [YearBalance] UNION ALL SELECT CONVERT(NVARCHAR(4), [YearBalance]) [YearBalance] FROM [PAYOUTPMCheckDates] GROUP BY [YearBalance] ORDER BY [YearBalance] DESC"; dt = Queries.GetResultsFromQueryString(SQLString); ddlYear.DataSource = dt; ddlYear.DataTextField = "YearBalance"; ddlYear.DataValueField = "YearBalance"; ddlYear.DataBind(); ddlYear.SelectedValue = dt.Rows[0][0].ToString(); }
private List <string> DateColumns() { string SQLString = @"SELECT DISTINCT CONVERT(NVARCHAR, WeekEnding, 20) WeekEnding FROM PAYOUTSchedule s INNER JOIN PAYOUTwe w ON (s.StartDate BETWEEN w.StartDate AND w.WeekEnding) OR (s.EndDate BETWEEN w.StartDate AND w.WeekEnding) WHERE[Archive] = 0 AND {0} {1} {2} {3} ORDER BY WeekEnding"; SQLString = SetFilters(SQLString); return(Queries.GetResultsFromQueryString(SQLString).AsEnumerable().Select(f => f.Field <string>("WeekEnding")).ToList()); }
/// <summary> /// Get full list PMPayroll from range in table CheckDates /// </summary> /// <param name="paramStartDate">Get the lower value from range of existing check dates</param> /// <param name="paramEndDate">Get the Higher value from range of existing check dates</param> /// <returns></returns> public static List <DataTable> GetPMPayrollresults(ref string paramStartDate, ref string paramEndDate) { List <weekending> lstWE = GetWeekendingList(ref paramStartDate, ref paramEndDate); List <DataTable> LstDT = new List <DataTable>(); string SQLString = "SELECT [CheckDate] FROM [PAYOUTPMPayroll] GROUP BY [CheckDate] ORDER BY [CheckDate] DESC"; List <DateTime> lstCheckDates = Queries.GetResultsFromQueryString(SQLString).AsEnumerable().Select(f => f.Field <DateTime>("CheckDate")).ToList(); if (lstCheckDates.Count > 0) { lstWE = lstWE.Where(w => Convert.ToDateTime(w.WeekEnding) > lstCheckDates.Max()).ToList(); } //Calculate results for (int i = 0; i < lstWE.Count; i++) { if (i < lstWE.Count - 1) { LstDT.Add(SummaryBiweekly( Convert.ToDateTime(lstWE[i + 1].WeekEnding).AddDays(1).ToShortDateString(), lstWE[i].WeekEnding )); } else { LstDT.Add(SummaryBiweekly( Convert.ToDateTime(lstWE[i].WeekEnding).AddDays(-14).ToShortDateString(), lstWE[i].WeekEnding )); } } //Get PM Payroll results SQLString = @" SELECT CONVERT(NVARCHAR(20), CheckDate) CheckDate, TrainerId, TrainerName, Manager, Role, Salary, Override, Commission, OverPay [Over Pay], UnderPay [Under Pay], [OTPremium],[VacationPay],[TravelTime],[ClawBack],[Adjustment],[AdjustmentComment],[PayCheck] FROM PAYOUTPMPayroll"; DataTable dt = Queries.GetResultsFromQueryString(SQLString); foreach (DateTime date in lstCheckDates) { DataTable dtPayrollByDate = dt.AsEnumerable().Where(f => Convert.ToDateTime(f.Field <string>("CheckDate")) == date).CopyToDataTable(); dtPayrollByDate.Rows.Add(); LstDT.Add(dtPayrollByDate); } return(Common.CloneList <DataTable>(LstDT)); }
private void LoadDDL() { string SQLString = @"SELECT Id, CONVERT(NVARCHAR, CONVERT(DATE, WeekEnding), 101) [Week Ending] FROM PAYOUTPMCheckDates ORDER BY WeekEnding DESC"; DataTable dt = Queries.GetResultsFromQueryString(SQLString); DataRow dr = dt.NewRow(); dr[0] = 0; dr[1] = "All"; dt.Rows.InsertAt(dr, 0); ddlCheckPeriod.DataSource = dt; ddlCheckPeriod.DataTextField = "Week Ending"; ddlCheckPeriod.DataValueField = "Id"; ddlCheckPeriod.DataBind(); ddlCheckPeriod.SelectedIndex = 1; }
protected void addBtn_Click(object sender, EventArgs e) { string Firstname = Request.Form["TxtFName"].ToString(); string lastname = Request.Form["TxtLname"].ToString(); string Email = Request.Form["Txtemail"].ToString(); string Role = Request.Form["DDLRole"].ToString(); string Salary = Request.Form["TxtSalary"].ToString(); string Manager = Request.Form["DDLManager"].ToString(); bool Active = true; if (CHKActive.Checked) { Active = true; } else { Active = false; } DataTable dt = Queries.GetResultsFromQueryString("select MAX(ID) + 1 from [PAYOUTtrainer]"); decimal OUTresult = 0; if (!decimal.TryParse(Salary, out OUTresult)) { Salary = OUTresult.ToString("F2"); } string SQLstring = string.Format(@"INSERT INTO [PAYOUTtrainer] (ID, Firstname, Lastname, EmailAddress, Active, Role, Salary) VALUES ({0}, '{1}', '{2}', '{3}', {4}, '{5}', {6})", dt.Rows[0][0].ToString(), Firstname, lastname, Email, Convert.ToInt32(Active), Role, Salary); Queries.ExecuteFromQueryString(SQLstring); SQLstring = string.Format(@"INSERT INTO [PAYOUTtrainerManager] ([TrainerId], [TrainManagerId]) VALUES ({0}, (SELECT TOP 1 Id FROM [PAYOUTtrainManager] WHERE FirstName + ' ' + LastName = '{1}'))", dt.Rows[0][0].ToString(), Manager); Queries.ExecuteFromQueryString(SQLstring); Response.Redirect("Trainer.aspx", true); }
private void FillFromDatabase() { DataTable dt = new DataTable(); string SQLString = string.Format("SELECT [Id] FROM [PAYOUTPMPayroll] WHERE [CheckDate] = '{0}'", (string)Session["ParamEndDate"]); List <int> dtPayrollIds = Queries.GetResultsFromQueryString(SQLString).AsEnumerable().Select(i => i.Field <int>("Id")).ToList(); if (dtPayrollIds.Count > 0) { SQLString = string.Format(@"SELECT CONVERT(NVARCHAR(20), [Date]) [Date], TrainerName, Program, Retailer, StoreNumber [Club #], [City] + ' ' + [State] [City/State], NCR, RoleDescription, Rate FROM PAYOUTPMCommissionSummary WHERE PMPayroll_Id IN ({0}) AND [TrainerName] = '{1}'", string.Join(", ", dtPayrollIds.ToArray()), Trainer); dt = Queries.GetResultsFromQueryString(SQLString); } else { Dictionary <string, string> SQLParameters = new Dictionary <string, string>(); SQLParameters.Add("@webStartDate", (string)Session["ParamStartDate"]); SQLParameters.Add("@webEndDate", (string)Session["ParamEndDate"]); SQLParameters.Add("@webTrainer", Trainer); SQLParameters.Add("@Action", "SELECT"); dt = Queries.GetResultsFromStoreProcedure("spx_PAYOUTsummaryCommissionDaily", ref SQLParameters).Tables[0]; } //for (int i = 0; i < dt.Rows.Count; i++) // dt.Rows[i]["Date"] = dt.Rows[i]["Date"].ToString() == "" ? "" : Common.ApplyDateFormat(dt.Rows[i]["Date"].ToString()); List <DataTable> lstResultTable = new List <DataTable>(); if (dt != null && dt.Rows.Count > 0) { lstResultTable = ReportDatatable(dt); } Session["lstResultTables"] = lstResultTable; }
private void SetOwners() { SQLstring = string.Format(@";WITH CTE AS ( SELECT SalesDate FROM PAYOUTsales WHERE StoreName LIKE '{0}%' AND program = '{1}' AND DATEPART(YEAR, SalesDate) = {2} UNION ALL SELECT SalesDate FROM PAYOUTsalesArchive WHERE StoreName LIKE '{0}%' AND program = '{1}' AND DATEPART(YEAR, SalesDate) = {2} ) SELECT 'All' WeekEnding UNION ALL SELECT DISTINCT CONVERT(NVARCHAR(20), [WeekEnding]) WeekEnding FROM PAYOUTwe w INNER JOIN (SELECT SalesDate FROM CTE) SalesFiltered ON SalesFiltered.SalesDate BETWEEN w.StartDate AND w.WeekEnding ORDER BY WeekEnding DESC ", WebStoreNameDDL.SelectedValue.Replace("'", "''").Replace("Kroger All", "Kroger"), WebProgramDDL.SelectedValue.Replace("'", "''"), YearDDL.SelectedValue ); string selectedValue = WebRotationDDL.SelectedValue; DataTable RotationsResult = Queries.GetResultsFromQueryString(SQLstring); WebRotationDDL.DataSource = RotationsResult; WebRotationDDL.DataValueField = "WeekEnding"; WebRotationDDL.DataBind(); if (RotationsResult.AsEnumerable().Where(f => f.Field <string>("WeekEnding").Contains(selectedValue)).Count() > 0) { WebRotationDDL.SelectedValue = selectedValue; } strRotationDDL = WebRotationDDL.SelectedValue; }
public static void PopulateWeekendingDDL(ref DropDownList weDDL) { string SQLString = @" SELECT CONVERT(NVARCHAR(20), [WeekEnding]) [WeekEnding] FROM [PAYOUTwe] GROUP BY [WeekEnding] ORDER BY [WeekEnding] DESC"; DataTable dt = Queries.GetResultsFromQueryString(SQLString); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i][0] = ApplyDateFormat(dt.Rows[i][0].ToString()); } weDDL.DataSource = dt; weDDL.DataValueField = "WeekEnding"; weDDL.DataTextField = "WeekEnding"; weDDL.DataBind(); weDDL.SelectedIndex = 0; }
private int UpdateScheduleRecord(DateTime?TrainStartDate, DateTime?TrainEndDate, DateTime?ScheduleStartDate, DateTime?ScheduleEndDate, string trainerId, string scheduleTrainerId) { if (trainerId != "0)") { string SQLstring = string.Empty; SQLstring = string.Format(@" SELECT COUNT(*) FROM [PAYOUTScheduleTrainer] WHERE ([TrainStartDate] BETWEEN '{0}' AND '{1}' OR [TrainEndDate] BETWEEN '{0}' AND '{1}' OR ([TrainStartDate] <= '{0}' AND [TrainEndDate] >= '{1}')) AND [TrainerId] = {2}", TrainStartDate, TrainEndDate, trainerId); try { if (Queries.GetResultsFromQueryString(SQLstring).Rows[0][0].ToString() != "0") { SQLstring = "UPDATE [PAYOUTScheduleTrainer] SET"; if (TrainStartDate != null && TrainEndDate != null && trainerId != null && trainerId != "0") { SQLstring += string.Format(" {0} = {1},", "TrainStartDate", "'" + TrainStartDate + "'"); SQLstring += string.Format(" {0} = {1},", "TrainEndDate", "'" + TrainEndDate + "'"); SQLstring += string.Format(" {0} = {1}", "TrainerID", "'" + trainerId + "'"); } else { SQLstring += string.Format(" {0} = {1},", "TrainStartDate", "NULL"); SQLstring += string.Format(" {0} = {1},", "TrainEndDate", "NULL"); SQLstring += string.Format(" {0} = {1}", "TrainerID", "NULL"); } SQLstring += " WHERE [Id] = '" + scheduleTrainerId + "' "; Queries.ExecuteFromQueryString(SQLstring); return(1); } } catch { return(-1); } } return(0); }
private void FillRetailer() { Session["ReportsToPrint"] = null; SQLstring = string.Format(@"SELECT DISTINCT StoreName, Program, [Owner] FROM ( SELECT 'Kroger All' StoreName, NULL Program, NULL [Owner] UNION ALL SELECT StoreName, Program, OwnerFirstname + ' ' + OwnerLastname [Owner] FROM PAYOUTsales WHERE DATEPART(YEAR, SalesDate) = {0} AND Qty > 0 GROUP BY StoreName, Program, OwnerFirstname, OwnerLastname UNION ALL SELECT StoreName, Program, OwnerFirstname + ' ' + OwnerLastname [Owner] FROM PAYOUTsalesArchive WHERE DATEPART(YEAR, SalesDate) = {0} AND Qty > 0 GROUP BY StoreName, Program, OwnerFirstname, OwnerLastname ) tab", YearDDL.SelectedValue); dtStoreProgOwnerInSales = Queries.GetResultsFromQueryString(SQLstring); List <string> lstStoreName = dtStoreProgOwnerInSales.AsEnumerable() .OrderBy(f => f.Field <string>("StoreName")) .Select(f => f.Field <string>("StoreName")) .Distinct().ToList(); lstStoreName.Sort(); lstStoreName.Insert(0, "--Select--"); string selectedValue = WebStoreNameDDL.SelectedValue; WebStoreNameDDL.DataSource = lstStoreName; WebStoreNameDDL.DataBind(); if (lstStoreName.Contains(selectedValue)) { WebStoreNameDDL.SelectedValue = selectedValue; } }
private void YearDDLPopulate() { SQLstring = @"SELECT DISTINCT SalesDate FROM ( SELECT DISTINCT DATEPART(YEAR, SalesDate) SalesDate FROM PAYOUTsales UNION ALL SELECT DISTINCT DATEPART(YEAR, SalesDate) SalesDate FROM PAYOUTsalesArchive ) tab WHERE SalesDate <> 2014 ORDER BY SalesDate DESC"; YearDDL.DataSource = Queries.GetResultsFromQueryString(SQLstring); YearDDL.DataValueField = "SalesDate"; YearDDL.DataBind(); strYearDDL = YearDDL.SelectedValue; }
private void FindOpenCheckDates() { string SQLstring = @"SELECT COUNT(*) OpenCheckDates, (SELECT MAX(WeekEnding) FROM [Payout].[dbo].[PAYOUTPMCheckDates]) LastClosed FROM [Payout].[dbo].[PAYOUTPMCheckDates] WHERE [ClosedPeriodDate] IS NULL"; DataTable dt = Queries.GetResultsFromQueryString(SQLstring); if (dt.Rows[0][0].ToString() == "0") { ExistOpenCheckDate.Value = "True"; NextCheckDate.Value = dt.Rows[0][1].ToString(); } else { ExistOpenCheckDate.Value = "False"; NextCheckDate.Value = ""; } }
private void SetSession_PeriodIsOpenOrClosed() { string SQLstring = string.Format(@"SELECT ClosedPeriodDate FROM [PAYOUTPMCheckDates] WHERE WeekEnding = '{0}'", ddlCheckPeriod.SelectedItem.Text); string result = Queries.GetResultsFromQueryString(SQLstring).Rows[0][0].ToString(); if (result != "") { Session["DDLShow"] = "Close"; SubmitPayroll.Visible = false; } else { Session["DDLShow"] = "Open"; SubmitPayroll.Visible = true; //if (Convert.ToDateTime(result) >= DateTime.Now) // //if (Convert.ToDateTime(result) >= Convert.ToDateTime("2017-04-07")) // Session["DDLShow"] = "Open"; //else // Session["DDLShow"] = "Close"; } //string result = Queries.GetResultsFromQueryString(SQLstring).Rows[0][0].ToString(); //if (result == "") //{ // Session["DDLShow"] = "Close"; //} //else //{ // if (Convert.ToDateTime(result) >= DateTime.Now) // //if (Convert.ToDateTime(result) >= Convert.ToDateTime("2017-04-07")) // Session["DDLShow"] = "Open"; // else // Session["DDLShow"] = "Close"; //} }
private void YearlyGridResults(string selectedYear = "All") { //GridView grv; DataTable dtUnion = new DataTable(); List <DataTable> LstDT = new List <DataTable>(); string SQLstring = @"SELECT YearBalance FROM PAYOUTPMCheckDates GROUP BY YearBalance"; DataTable SQL_DTResult = Queries.GetResultsFromQueryString(SQLstring); if (Session["YearTable"] == null) { for (int i = SQL_DTResult.Rows.Count - 1; i >= 0; i--) { LstDT.Add(SummaryYearly((int)SQL_DTResult.Rows[i][0])); } Session["YearTable"] = Common.UnionAllDatatables(LstDT); } dtUnion = ((DataTable)Session["YearTable"]).Copy(); if (dtUnion.Columns.Contains("TrainerName")) { dtUnion.Columns["TrainerName"].ColumnName = "Trainer"; } if (selectedYear != "All") { dtUnion = dtUnion.AsEnumerable().Where(f => f.Field <string>("CheckDate") == selectedYear).CopyToDataTable(); } GRVYearly.DataSource = dtUnion; GRVYearly.DataBind(); GRVYearlyTotals.DataSource = totalizeYearlyResults(dtUnion); GRVYearlyTotals.DataBind(); Common.RightAlignCurrencyFormat(ref GRVYearly); }
private void GetWEYearDates(ref string ParamStartDate, ref string ParamEndDate, int yearDate) { string SQLstring = string.Format(@" SELECT COALESCE( ( SELECT DATEADD(DAY, 1, MAX(WeekEnding)) FROM PAYOUTPMCheckDates WHERE YearBalance = {0} - 1 ), ( SELECT DATEADD(DAY, -14, MIN(WeekEnding)) FROM PAYOUTPMCheckDates WHERE YearBalance = {0} ))", yearDate); ParamStartDate = Queries.GetResultsFromQueryString(SQLstring).Rows[0][0].ToString(); SQLstring = string.Format(@"SELECT MAX(WeekEnding) FROM PAYOUTPMCheckDates WHERE YearBalance = {0}", yearDate); ParamEndDate = Queries.GetResultsFromQueryString(SQLstring).Rows[0][0].ToString(); }