void BindShippingCalculationTable()
{
//We'll need shipping method shipping charge amounts to work with in building the data source
using (DataTable gridData = new DataTable())
{
//Populate shipping methods data
using (SqlConnection sqlConnection = new SqlConnection(DB.GetDBConn()))
{
sqlConnection.Open();
string getShippingMethodMapping = "exec aspdnsf_GetStoreShippingMethodMapping @StoreID = @StoreId, @IsRTShipping = 0, @OnlyMapped = @FilterByStore";
var getShippingMethodMappingParams = new[]
{
new SqlParameter("@StoreId", SelectedStoreId),
new SqlParameter("@FilterByStore", FilterShipping),
};
using (IDataReader rs = DB.GetRS(getShippingMethodMapping, getShippingMethodMappingParams, sqlConnection))
gridData.Load(rs);
}
if (gridData.Rows.Count == 0)
{
AlertMessage.PushAlertMessage(String.Format("You do not have any shipping methods setup for the selected store. Please <a href=\"{0}\">click here</a> to set them up.", AppLogic.AdminLinkUrl("shippingmethods.aspx")), AspDotNetStorefrontControls.AlertMessage.AlertType.Error);
ShippingRatePanel.Visible = false;
return;
}
ShippingGrid.DataSource = gridData;
ShippingGrid.DataBind();
}
}
void BindShippingCalculationTable(bool addInsertRow)
{
//clear the columns to prevent duplicates
ShippingGrid.Columns.Clear();
//We're going to assemble the datasource that we need by putting it together manually here.
using (DataTable gridData = new DataTable())
{
//We'll need shipping method shipping charge amounts to work with in building the data source
using (DataTable methodAmountsData = new DataTable())
{
//Populate shipping methods data
using (SqlConnection sqlConnection = new SqlConnection(DB.GetDBConn()))
{
sqlConnection.Open();
string getShippingMethodMapping = "exec aspdnsf_GetStoreShippingMethodMapping @StoreID = @StoreId, @IsRTShipping = 0, @OnlyMapped = @FilterByStore";
var getShippingMethodMappingParams = new[]
{
new SqlParameter("@StoreId", SelectedStoreId),
new SqlParameter("@FilterByStore", FilterShipping),
};
using (IDataReader rs = DB.GetRS(getShippingMethodMapping, getShippingMethodMappingParams, sqlConnection))
methodAmountsData.Load(rs);
}
if (methodAmountsData.Rows.Count == 0)
{
AlertMessage.PushAlertMessage(String.Format("You do not have any shipping methods setup for the selected store. Please <a href=\"{0}\">click here</a> to set them up.", AppLogic.AdminLinkUrl("shippingmethods.aspx")), AspDotNetStorefrontControls.AlertMessage.AlertType.Error);
ShippingRatePanel.Visible = false;
return;
}
using (DataTable shippingRangeData = new DataTable())
{
using (SqlConnection sqlConnection = new SqlConnection(DB.GetDBConn()))
{
//populate the shipping range data
var sqlShipping = @"SELECT DISTINCT stp.RowGuid,stp.LowValue,stp.HighValue,stp.MinimumCharge,stp.SurCharge
FROM ShippingByTotalByPercent stp with (NOLOCK)
INNER JOIN ShippingMethod sm WITH (NOLOCK) ON sm.ShippingMethodid = stp.ShippingMethodId AND (@FilterByStore = 0 or @StoreId = stp.StoreID)
order by LowValue" ;
var shippingRangeParams = new[]
{
new SqlParameter("@StoreId", SelectedStoreId),
new SqlParameter("@FilterByStore", FilterShipping),
};
sqlConnection.Open();
using (IDataReader rs = DB.GetRS(sqlShipping, shippingRangeParams, sqlConnection))
shippingRangeData.Load(rs);
}
//Add the data columns we'll need on our table and add grid columns to match
gridData.Columns.Add(new DataColumn("RowGuid", typeof(string)));
gridData.Columns.Add(new DataColumn("LowValue", typeof(string)));
BoundField boundField = new BoundField();
boundField.DataField = "LowValue";
boundField.HeaderText = "Low";
boundField.ControlStyle.CssClass = "text-4";
ShippingGrid.Columns.Add(boundField);
gridData.Columns.Add(new DataColumn("HighValue", typeof(string)));
boundField = new BoundField();
boundField.DataField = "HighValue";
boundField.HeaderText = "High";
boundField.ControlStyle.CssClass = "text-4";
ShippingGrid.Columns.Add(boundField);
gridData.Columns.Add(new DataColumn("MinimumCharge", typeof(string)));
boundField = new BoundField();
boundField.DataField = "MinimumCharge";
boundField.HeaderText = "Minimum Dollar Amount";
boundField.ControlStyle.CssClass = "text-4";
ShippingGrid.Columns.Add(boundField);
gridData.Columns.Add(new DataColumn("SurCharge", typeof(string)));
boundField = new BoundField();
boundField.DataField = "SurCharge";
boundField.HeaderText = "Base Dollar Amount";
boundField.ControlStyle.CssClass = "text-4";
ShippingGrid.Columns.Add(boundField);
//Add shipping method columns to our grid data
foreach (DataRow methodAmountsRow in methodAmountsData.Rows)
{
var columnName = String.Format("MethodAmount_{0}", DB.RowField(methodAmountsRow, "ShippingMethodID"));
gridData.Columns.Add(new DataColumn(columnName, typeof(string)));
//add a column to the gridview to hold the data
boundField = new BoundField();
boundField.DataField = columnName;
boundField.HeaderText = DB.RowFieldByLocale(methodAmountsRow, "Name", LocaleSetting);
boundField.ControlStyle.CssClass = "text-4";
ShippingGrid.Columns.Add(boundField);
}
//now that our columns are setup add rows to our table
foreach (DataRow rangeRow in shippingRangeData.Rows)
{
var newRow = gridData.NewRow();
//add the range data
newRow["RowGuid"] = rangeRow["RowGuid"];
newRow["LowValue"] = rangeRow["LowValue"];
newRow["HighValue"] = rangeRow["HighValue"];
newRow["MinimumCharge"] = rangeRow["MinimumCharge"];
newRow["SurCharge"] = rangeRow["SurCharge"];
//add shipping method amounts to our grid data
foreach (DataRow methodAmountsRow in methodAmountsData.Rows)
{
var shippingMethodId = DB.RowFieldInt(methodAmountsRow, "ShippingMethodID");
var shippingRangeGuid = DB.RowFieldGUID(rangeRow, "RowGUID");
Decimal surCharge; // not used here
Decimal minimumCharge; // not used here
var amount = Shipping.GetShipByTotalByPercentCharge(shippingMethodId, shippingRangeGuid, out minimumCharge, out surCharge);
var localizedAmount = Localization.CurrencyStringForDBWithoutExchangeRate(amount);
var colName = String.Format("MethodAmount_{0}", shippingMethodId);
newRow[colName] = localizedAmount;
}
gridData.Rows.Add(newRow);
}
//if we're inserting, add an empty row to the end of the table
if (addInsertRow)
{
var newRow = gridData.NewRow();
//add the range data
newRow["RowGuid"] = 0;
newRow["LowValue"] = 0;
newRow["HighValue"] = 0;
newRow["MinimumCharge"] = 0;
newRow["SurCharge"] = 0;
//add shipping method columns to our insert row
foreach (DataRow methodAmountsRow in methodAmountsData.Rows)
{
var shippingMethodId = DB.RowFieldInt(methodAmountsRow, "ShippingMethodID");
var amount = 0;
var localizedAmount = Localization.CurrencyStringForDBWithoutExchangeRate(amount);
var colName = String.Format("MethodAmount_{0}", shippingMethodId);
newRow[colName] = localizedAmount;
}
gridData.Rows.Add(newRow);
//if we're inserting than we'll want to make the insert row editable
ShippingGrid.EditIndex = gridData.Rows.Count - 1;
}
//add the delete button column
ButtonField deleteField = new ButtonField();
deleteField.ButtonType = ButtonType.Link;
deleteField.Text = "<i class=\"fa fa-times\"></i> Delete";
deleteField.CommandName = "Delete";
deleteField.ControlStyle.CssClass = "delete-link";
deleteField.ItemStyle.Width = 94;
ShippingGrid.Columns.Add(deleteField);
//add the edit button column
CommandField commandField = new CommandField();
commandField.ButtonType = ButtonType.Link;
commandField.ShowEditButton = true;
commandField.ShowDeleteButton = false;
commandField.ShowCancelButton = true;
commandField.ControlStyle.CssClass = "edit-link";
commandField.EditText = "<i class=\"fa fa-share\"></i> Edit";
commandField.CancelText = "<i class=\"fa fa-reply\"></i> Cancel";
commandField.UpdateText = "<i class=\"fa fa-floppy-o\"></i> Save";
commandField.ItemStyle.Width = 84;
ShippingGrid.Columns.Add(commandField);
ShippingGrid.DataSource = gridData;
ShippingGrid.DataBind();
}
}
}
btnInsert.Visible = !addInsertRow; //Hide the 'add new row' button while editing/inserting to avoid confusion and lost data
}
