public GeneralResponse AddTaxRecord(MunicipalityTax tbl)
{
GeneralResponse res = new GeneralResponse();
if (!ValidateTaxDates(tbl.TaxType, tbl.ValidFrom.Date, tbl.ValidTo.Date))
{
res.message = "From/To dates do not match tax type";
res.status = false;
return(res);
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.CommandText = @"IF NOT EXISTS (SELECT 1 FROM municipalities
WHERE municipality = @parMunicip and tax_type = @parTaxType and valid_from = @parFrom)
INSERT INTO municipalities (municipality, tax_type, valid_from, valid_to, tax)
VALUES (@parMunicip, @parTaxType, @parFrom, @parTo, @parTax)";
sqlCommand.Parameters.Add(new SqlParameter("parMunicip", tbl.Municipality));
sqlCommand.Parameters.Add(new SqlParameter("parTaxType", tbl.TaxType));
sqlCommand.Parameters.Add(new SqlParameter("parFrom", tbl.ValidFrom.Date));
sqlCommand.Parameters.Add(new SqlParameter("parTo", tbl.ValidTo.Date));
sqlCommand.Parameters.Add(new SqlParameter("parTax", tbl.Tax));
sqlCommand.Connection = connection;
connection.Open();
try
{
rows_affected = sqlCommand.ExecuteNonQuery();
if (rows_affected < 1)
{
res.message = "Duplicate tax record was not added";
res.status = false;
return(res);
}
}
catch
{
res.status = false;
res.message = "Unable to add record";
return(res);
}
}
}
res.message = "Tax record added";
res.status = true;
return(res);
}
public MunicipalityTaxResponse GetTaxInfo(MunicipalityTax req)
{
MunicipalityTaxResponse tbl = new MunicipalityTaxResponse();
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.CommandText = @"SELECT TOP 1 tax
FROM municipalities
LEFT JOIN TaxPriorities ON municipalities.tax_type = TaxPriorities.TaxType
where municipality = @parMunicip and valid_from <= @parDate and valid_to >= @parDate
ORDER BY Priority";
sqlCommand.Parameters.Add(new SqlParameter("parMunicip", req.Municipality));
sqlCommand.Parameters.Add(new SqlParameter("parDate", req.ValidFrom));
sqlCommand.Connection = connection;
connection.Open();
try
{
sqlCommand.ExecuteNonQuery();
using (SqlDataReader reader = sqlCommand.ExecuteReader())
{
if (reader.Read())
{
tbl.Status = true;
tbl.Tax = reader.GetDecimal(0);
tbl.Message = "";
}
else
{
tbl.Status = false;
tbl.Tax = 0;
tbl.Message = "No tax records found";
}
}
}
catch
{
tbl.Status = false;
tbl.Tax = 0;
tbl.Message = "Database error occured";
}
}
}
return(tbl);
}
public GeneralResponse UpdateTaxRecord(MunicipalityTax tbl)
{
GeneralResponse res = new GeneralResponse();
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.CommandText = @"IF EXISTS (SELECT 1 FROM municipalities
WHERE municipality = @parMunicip and tax_type = @parTaxType and valid_from = @parFrom)
UPDATE municipalities
SET tax = @parTax
WHERE municipality = @parMunicip and tax_type = @parTaxType and valid_from = @parFrom;";
sqlCommand.Parameters.Add(new SqlParameter("parMunicip", tbl.Municipality));
sqlCommand.Parameters.Add(new SqlParameter("parTaxType", tbl.TaxType));
sqlCommand.Parameters.Add(new SqlParameter("parFrom", tbl.ValidFrom.Date));
sqlCommand.Parameters.Add(new SqlParameter("parTax", tbl.Tax));
sqlCommand.Connection = connection;
connection.Open();
try
{
rows_affected = sqlCommand.ExecuteNonQuery();
if (rows_affected < 1)
{
res.message = "Tax record not found";
res.status = false;
return(res);
}
}
catch (Exception e)
{
res.status = false;
res.message = e.Message;
}
}
}
res.message = "Record updated";
res.status = true;
return(res);
}
