public static bool AddRegionRateToTax(int taxId, RegionalRate rate) { SQLDataAccess.ExecuteNonQuery("INSERT INTO [Catalog].[TaxRegionRate] ([TaxId], [RegionID], [RegionRate]) VALUES (@taxId, @regionId, @rate)", CommandType.Text, new SqlParameter("@taxId", taxId), new SqlParameter("@regionId", rate.RegionId), new SqlParameter("@rate", rate.Rate)); return(true); }
public static ICollection <TaxElement> GetTaxesForProduct(int productId, CustomerContact sellerContact, CustomerContact billingContact, CustomerContact shippingContact) { using (var da = new SQLDataAccess()) { da.cmd.CommandText = @" SELECT [Catalog].[Tax].*, [Catalog].[TaxRegionRate].[RegionID], [Catalog].[TaxRegionRate].[RegionRate] FROM [Catalog].[Tax] JOIN [Catalog].[TaxMappingOnProduct] ON [Tax].[TaxId] = [TaxMappingOnProduct].[TaxId] LEFT JOIN [Catalog].[TaxRegionRate] ON [Tax].[TaxId] = [TaxRegionRate].[TaxId] AND ( ([DependsOnAddress] = @default AND [CountryID] = @sellerCountry AND [RegionID] = @sellerRegion) OR ([DependsOnAddress] = @shipping AND [CountryID] = @shippingCountry AND [RegionID] = @shippingRegion) OR ([DependsOnAddress] = @billing AND [CountryID] = @billingCountry AND [RegionID] = @billingRegion) ) WHERE [ProductID] = @ProductID AND [Enabled] = 'True' "; da.cmd.CommandType = CommandType.Text; da.cmd.Parameters.AddWithValue("@default", (int)TypeRateDepends.Default); da.cmd.Parameters.AddWithValue("@shipping", (int)TypeRateDepends.ByShippingAddress); da.cmd.Parameters.AddWithValue("@billing", (int)TypeRateDepends.ByBillingAddress); da.cmd.Parameters.AddWithValue("@sellerCountry", sellerContact.CountryId); da.cmd.Parameters.AddWithValue("@sellerRegion", sellerContact.RegionId.HasValue ? sellerContact.RegionId : (object)DBNull.Value); da.cmd.Parameters.AddWithValue("@shippingCountry", shippingContact.CountryId); da.cmd.Parameters.AddWithValue("@shippingRegion", shippingContact.RegionId.HasValue ? shippingContact.RegionId : (object)DBNull.Value); da.cmd.Parameters.AddWithValue("@billingCountry", billingContact.CountryId); da.cmd.Parameters.AddWithValue("@billingRegion", billingContact.RegionId.HasValue ? billingContact.RegionId : (object)DBNull.Value); da.cmd.Parameters.AddWithValue("@ProductID", productId); da.cnOpen(); var result = new List <TaxElement>(); using (SqlDataReader reader = da.cmd.ExecuteReader()) while (reader.Read()) { var t = ReadTax(reader); var regionalRateCollection = new List <RegionalRate>(); t.RegionalRates = regionalRateCollection; if (!(reader["RegionID"] is DBNull)) { var regionalRate = new RegionalRate { RegionId = SQLDataHelper.GetInt(reader, "RegionID"), Rate = SQLDataHelper.GetDecimal(reader, "RegionRate") }; regionalRateCollection.Add(regionalRate); } result.Add(t); } return(result); } }