private void FillFromDatabase()
{
DataTable dt = new DataTable();
//DateFilters(ref ParamStartDate, ref ParamEndDate);
Common.StartEndDateFilters(ref ParamStartDate, ref ParamEndDate);
dateFrom.Text = DatePickerFormat(ParamStartDate);
dateTo.Text = DatePickerFormat(ParamEndDate);
SQLParameters.Add("@webStartDate", ApplyDateFormat(ParamStartDate));
SQLParameters.Add("@webEndDate", ApplyDateFormat(ParamEndDate));
//SQLParameters.Add("@webProgram", Session["YTDprogram"].ToString());
//SQLParameters.Add("@webStoreName", Session["YTDstoreName"].ToString());
//SQLParameters.Add("@webStoreNumber", Session["YTDstoreNumber"].ToString());
//SQLParameters.Add("@webOwner", Session["YTDowner"].ToString());
//SQLParameters.Add("@webLocation", Session["YTDlocation"].ToString());
SQLParameters.Add("@UserFullname", Session["YTDUserFullname"].ToString());
SQLParameters.Add("@UserType", userType);
dt = Queries.GetResultsFromStoreProcedure("spx_PAYOUTsummaryYTD", ref SQLParameters).Tables[0];
StoreNumberTXT.Text = string.Empty;
//ownerTXT.Text = string.Empty;
Session["ResultTable"] = dt;
//Session["ResultTable"] = sendingResultTableByValue(dt);
//Session["YTDdateFrom"] = ParamStartDate;
//Session["YTDdateTo"] = ParamEndDate;
}
private void FillFromDatabase()
{
DataTable dt = new DataTable();
DateFilters(ref ParamStartDate, ref ParamEndDate);
dateFrom.Text = DatePickerFormat(ParamStartDate);
dateTo.Text = DatePickerFormat(ParamEndDate);
SQLParameters.Add("@webStartDate", ParamStartDate);
SQLParameters.Add("@webEndDate", ParamEndDate);
SQLParameters.Add("@UserType", userType);
dt = Queries.GetResultsFromStoreProcedure("spx_PAYOUTsales", ref SQLParameters).Tables[0];
BindDDL(dt, "Program");
BindDDL(dt, "StoreName");
StoreNumberTXT.Text = string.Empty;
ownerTXT.Text = string.Empty;
Session["ResultTable"] = dt;
Session["dateFrom"] = ParamStartDate;
Session["dateTo"] = ParamEndDate;
}
private DataTable getResults()
{
Dictionary <string, string> Params = new Dictionary <string, string>();
if (DDLShowCount.SelectedValue == "")
{
Params.Add("WebStoreName", "NULL");
}
else
{
Params.Add("WebStoreName", DDLShowCount.SelectedValue);
}
DateTime OutdateResult = new DateTime();
DateTime.TryParse(txtSalesDate.Text.Trim(), out OutdateResult);
if (OutdateResult == DateTime.MinValue)
{
Params.Add("WebSalesDate", "NULL");
}
else
{
Params.Add("WebSalesDate", txtSalesDate.Text);
}
DataTable dt = Queries.GetResultsFromStoreProcedure("spx_PAYOUTShowsCount", ref Params).Tables[0];
return(dt);
}
private void GetDDL()
{
Dictionary <string, string> SelectedValues = new Dictionary <string, string>();
Dictionary <string, string> Params = fillParams(SelectedValues);
Params.Add("Action", "BULKSELECTDDL");
DataTable dt = Queries.GetResultsFromStoreProcedure("spx_PAYOUTsales", ref Params).Tables[0];
List <string> lstObjects = new List <string>();
lstObjects = dt.AsEnumerable()
.OrderBy(f => f.Field <string>("Program"))
.Select(f => f.Field <string>("Program")).Distinct().ToList();
lstObjects.Insert(0, "All");
if (ProgramDDL == null)
{
ProgramDDL = new DropDownList();
}
ProgramDDL.DataSource = lstObjects;
if (!(SelectedValues.ContainsKey("SelectedProgram") && lstObjects.Contains(SelectedValues["SelectedProgram"])))
{
SelectedValues.Remove("SelectedProgram");
}
lstObjects = dt.AsEnumerable()
.OrderBy(f => f.Field <string>("StoreName"))
.Select(f => f.Field <string>("StoreName")).Distinct().ToList();
if (lstObjects.Where(x => x.Contains("Kroger")).Count() > 0)
{
lstObjects.Add("Kroger All");
lstObjects.Sort();
}
lstObjects.Insert(0, "All");
if (RetailerDDL == null)
{
RetailerDDL = new DropDownList();
}
RetailerDDL.DataSource = lstObjects;
if (!(SelectedValues.ContainsKey("SelectedRetailer") && lstObjects.Contains(SelectedValues["SelectedRetailer"])))
{
SelectedValues.Remove("SelectedRetailer");
}
ProgramDDL.DataBind();
RetailerDDL.DataBind();
ProgramDDL.SelectedValue = SelectedValues.ContainsKey("SelectedProgram") ? SelectedValues["SelectedProgram"] : "All";
RetailerDDL.SelectedValue = SelectedValues.ContainsKey("SelectedRetailer") ? SelectedValues["SelectedRetailer"] : "All";
int RowCount = dt.AsEnumerable().Select(f => f.Field <int>("TotalRows")).Take(1).FirstOrDefault();
CountRows.Text = "Rows count: " + string.Format("{0:n0}", RowCount);
Session["RowCount"] = RowCount;
}
private void LoadGridData(int pageIndex)
{
if (Session["SP_Parameters"] != null && Session["SP_Name"] != null)
{
int recordCount;
Dictionary <string, string> Params =
new Dictionary <string, string>((Dictionary <string, string>)Session["SP_Parameters"]);
Params.Add("PageIndex", pageIndex.ToString());
Params.Add("PageSize", ddlPageSize.SelectedValue);
DataTable dt = Queries.GetResultsFromStoreProcedure((string)Session["SP_Name"], ref Params).Tables[0];
if (Session["AddDelOption"] != null && (bool)Session["AddDelOption"])
{
dt.Columns.Add("Action");
dt.Columns["Action"].SetOrdinal(1);
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["Action"] = "x";
}
}
if (dt.Rows.Count > 0)
{
recordCount = int.Parse(dt.Rows[0]["CountResults"].ToString());
}
else
{
recordCount = 0;
}
dt.Columns.Remove("CountResults");
//if(Session["HeaderFilterList"] != null)
//{
// AddHeaderTemplates(dt);
//}
GRV.DataSource = dt;
if (Session["ShowFixedHeader"] != null && !(bool)Session["ShowFixedHeader"])
{
GRV.ShowHeader = true;
}
else
{
GRV.ShowHeader = false;
}
GRV.DataBind();
setHeaders(dt.Clone()); //Lightweight datatable
PopulatePager(recordCount, pageIndex, 10);
}
}
public static DataTable SummaryBiweekly(string ParamStartDate, string ParamEndDate)
{
Dictionary <string, string> SQLParameters = new Dictionary <string, string>();
SQLParameters.Add("@webStartDate", ParamStartDate);
SQLParameters.Add("@webEndDate", ParamEndDate);
DataTable dt = Queries.GetResultsFromStoreProcedure("spx_PAYOUTsummaryCumulativePM", ref SQLParameters).Tables[0];
//Adding Row Column Date
dt.Columns.Add("CheckDate", typeof(string)).SetOrdinal(0);
dt.Columns.Add("Over Pay", typeof(decimal));
dt.Columns.Add("Under Pay", typeof(decimal));
dt.Columns.Add("OTPremium", typeof(decimal));
dt.Columns.Add("VacationPay", typeof(decimal));
dt.Columns.Add("TravelTime", typeof(decimal));
dt.Columns.Add("ClawBack", typeof(decimal));
dt.Columns.Add("Adjustment", typeof(decimal));
dt.Columns.Add("AdjustmentComment", typeof(string));
dt.Columns.Add("PayCheck", typeof(decimal));
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i][0] = Common.ApplyDateFormat(ParamEndDate);
decimal total =
(dt.Rows[i]["Override"].ToString() == "" ? 0 : decimal.Parse(dt.Rows[i]["Override"].ToString())) +
(dt.Rows[i]["Commission"].ToString() == "" ? 0 : decimal.Parse(dt.Rows[i]["Commission"].ToString())) -
(dt.Rows[i]["Salary"].ToString() == "" ? 0 : decimal.Parse(dt.Rows[i]["Salary"].ToString()));
if (total < 0)
{
dt.Rows[i]["Over Pay"] = total;//.ToString("#.##");
}
else
{
dt.Rows[i]["Under Pay"] = total;//.ToString("#.##");
}
}
dt.Rows.Add();
FormatColumnTypes(dt);
return(dt);
//Check how to fix header with multiple nested tables
//h_ttp://stackoverflow.com/questions/30079298/align-scrollable-table-columns-with-header-in-css
//h_ttp://jsfiddle.net/gfy4pwrr/6/
//Scrollable header
//h_ttp://jsfiddle.net/T9Bhm/7/
//h_ttp://jsfiddle.net/7UZA4/1385/
//h_ttps://codepen.io/ajkochanowicz/pen/KHdih
//h_ttp://jsfiddle.net/emn13/YMvk9/
}
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 static DataTable SalesReport(string StartDate, string EndDate, int reportNumber, string ParamYearDDL = null)
{
Dictionary <string, string> Parameters = new Dictionary <string, string>();
Parameters.Add("@WebStoreNameDDL", strStoreNameDDL);
Parameters.Add("@WebProgramDDL", strProgramDDL);
Parameters.Add("@WebOwnerDDL", strOwnerDDL);
Parameters.Add("@StartDate", StartDate);
Parameters.Add("@EndDate", EndDate);
Parameters.Add("@YearDDL", ParamYearDDL != null ? ParamYearDDL : "NULL");
Parameters.Add("@ReportNumber", reportNumber.ToString());
return(Queries.GetResultsFromStoreProcedure("spx_PAYOUTYtdWeeklyRpt", ref Parameters).Tables[0]);
}
private DataSet ImportDataToGrid(int pageIndex)
{
Dictionary <string, string> Params = GetFilterParameters();
Params["PageIndex"] = pageIndex.ToString();
Params["PageSize"] = ddlPageSize.SelectedValue;
Params["Action"] = StoredProcedureSelect;
foreach (var item in cbl_SubReconciliation.Items.OfType <ListItem>())
{
if (Params.ContainsKey(item.Text.Replace(" ", "") + "Reconciliation"))
{
Params[item.Text.Replace(" ", "") + "Reconciliation"] = item.Selected.ToString();
}
else
{
Params.Add(item.Text.Replace(" ", "") + "Reconciliation", item.Selected.ToString());
}
}
if (rbtn_LastLocked.SelectedItem.Text == "Latest")
{
if (Params.ContainsKey(rbtn_LastLocked.SelectedItem.Text))
{
Params["ShowLastLocked"] = rbtn_LastLocked.SelectedItem.Text;
}
else
{
Params.Add("ShowLastLocked", rbtn_LastLocked.SelectedItem.Text);
}
}
//Params[item.Text.Replace("$ ", "Dollar")] = item.Selected.ToString();
if (ChkUserChanges.Checked)
{
Params["HideOriginals1"] = "1";
}
else
{
Params["HideOriginals1"] = "0";
}
DataSet ds = Queries.GetResultsFromStoreProcedure("spx_PAYOUTAuditSales", ref Params);
return(ds);
}
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 BindGridData()
{
SQLParameters = new Dictionary <string, string>();
SQLParameters.Add("@webStartDate", dateFrom.Text);
SQLParameters.Add("@webEndDate", dateTo.Text);
SQLParameters.Add("@webProgram", programDDL.SelectedValue);
SQLParameters.Add("@webStoreName", sstoreDDL.SelectedValue);
SQLParameters.Add("@webStoreNumber", StoreNumberTXT.Text);
SQLParameters.Add("@webOwner", ownerTXT.Text);
SQLParameters.Add("@Option", "ScheduleTrainer");
SQLParameters.Add("@webTrainerId", DDTrainer.SelectedValue == "0" ? "" : DDTrainer.SelectedValue);
DataTable dt = Queries.GetResultsFromStoreProcedure("[spx_PAYOUTschedule]", ref SQLParameters).Tables[0];
ProfileScreenSettings(ref dt);
GridView1.DataSource = dt;
GridView1.DataBind();
GridView2.DataSource = dt;
GridView2.DataBind();
BindDDLs();
}
private void BindDDL(Dictionary <string, string> SQLParameters)
{
/* Add DropDownLists*/
string SQLString = @"SELECT StoreName FROM [PAYOUTcommissions] WHERE StoreName <> '' GROUP BY StoreName ORDER BY StoreName";
DataTable dtAdd = Queries.GetResultsFromQueryString(SQLString);
sstoreAddDDL.DataSource = dtAdd;
sstoreAddDDL.DataTextField = "StoreName";
sstoreAddDDL.DataBind();
SQLString = @"SELECT Program FROM [PAYOUTcommissions] WHERE Program <> '' GROUP BY Program ORDER BY Program";
dtAdd = Queries.GetResultsFromQueryString(SQLString);
programAddDDL.DataSource = dtAdd;
programAddDDL.DataTextField = "Program";
programAddDDL.DataBind();
if (userType != "Trainer")
{
SQLString = @"SELECT [ID] ,[RoleDescription] FROM [Payout].[dbo].[PAYOUTtrainerRoles]";
}
else
{
SQLString = string.Format(@" SELECT r.[ID] ,r.[RoleDescription]
FROM [Payout].[dbo].[PAYOUTtrainerRoles] r
INNER JOIN PAYOUTtrainer t ON r.RoleDescription = t.[Role]
WHERE EmailAddress = '{0}'", user);
}
dtAdd = Queries.GetResultsFromQueryString(SQLString);
roleAddDDL.DataSource = dtAdd;
roleAddDDL.DataTextField = "RoleDescription";
roleAddDDL.DataValueField = "ID";
roleAddDDL.DataBind();
/* Filtered DropDownLists*/
SQLParameters["Action"] = "SELECTDDL";
SQLParameters["PageIndex"] = "NULL";
SQLParameters["PageSize"] = "NULL";
DataTable dt = Queries.GetResultsFromStoreProcedure("spx_PAYOUTSetupCommission", ref SQLParameters).Tables[0];
Common.BindDDLFromTableResults(dt, "Program", ref programDDL, null);
Common.BindDDLFromTableResults(dt, "StoreName", ref storeDDL, null);
Common.BindDDLFromTableResults(dt, "Role", ref roleDDL, null);
/* ----------------------------------------------------------------------------- */
//DataRow row = dt.NewRow();
//row["RoleId"] = 0;
//dt.Rows.Add(row);
//var lstFilteredDT = dt.AsEnumerable()
// .Select(t =>
// new
// {
// Id = t.Field<string>("RoleId"),
// DisplayName = t.Field<string>("RoleDescription")
// }
// ).Distinct()
// .ToList();
//roleDDL.DataSource = lstFilteredDT;
//roleDDL.DataTextField = "RoleDescription";
//roleDDL.DataTextField = "RoleId";
//roleDDL.DataBind();
/* ----------------------------------------------------------------------------- */
//List<string> lstFilteredDT = new List<string>();
//lstFilteredDT = dt.AsEnumerable()
// .Select(t => t.Field<string>("Role")).ToList();
//lstFilteredDT = lstFilteredDT.Distinct().ToList();
//if (userType != "Trainer")
// SQLString = string.Format(@"SELECT [ID] ,[RoleDescription] FROM [Payout].[dbo].[PAYOUTtrainerRoles] WHERE [RoleDescription] IN ({0}) ORDER BY [RoleDescription]"
// , "'" + string.Join("','", lstFilteredDT) + "'");
//else
// SQLString = string.Format(@" SELECT r.[ID] ,r.[RoleDescription]
// FROM [Payout].[dbo].[PAYOUTtrainerRoles] r
// INNER JOIN PAYOUTtrainer t ON r.RoleDescription = t.[Role]
// WHERE EmailAddress = '{0}'", user);
//DataTable dt1 = Queries.GetResultsFromQueryString(SQLString);
//DataRow dr = dt1.NewRow();
//dr["RoleDescription"] = "All";
//dt1.Rows.InsertAt(dr, 0);
//roleDDL.DataSource = dt1;
//roleDDL.DataTextField = "RoleDescription";
//roleDDL.DataValueField = "ID";
//roleDDL.DataBind();
}
private DataTable GetTable(ref Dictionary <string, string> Params)
{
Params["Action"] = "BULKSELECTTABLE";
return(Queries.GetResultsFromStoreProcedure("spx_PAYOUTsales", ref Params).Tables[0]);
}