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();
}
