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 }