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