public static ratecolumns oldRatesValues(int _rateid)
{
ratecolumns oRec1 = new ratecolumns();
string _cmdstr = "SELECT rate_rateamt, rate_companyRtamt FROM [Rates] "
+ " WHERE rate_id = @rid";
if (connect == null || connect.State == ConnectionState.Closed)
{
connect.Open();
}
try
{
command = new SqlCommand(_cmdstr, connect);
command.Parameters.AddWithValue("@rid", _rateid);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
oRec1.EErate = Convert.ToDecimal(reader[0]);
oRec1.COrate = Convert.ToDecimal(reader[1]);
}
reader.Close();
}
catch (Exception ex)
{
}
finally
{
connect.Close();
command.Dispose();
}
return(oRec1);
}
public static void insertRates(ratecolumns inValues, string _pgcode)
{
string _cmdstr = "SELECT ph_id FROM [Planhier] "
+ " WHERE ph_plancd = @pcode AND ph_plandesc = @dsc AND ph_tiercd = @tcode AND ph_progmcd = @prog";
string _cmdstr1 = "SELECT COUNT(*) FROM [Rates] "
+ " WHERE rate_plhr_id = @pid AND rate_rateamt = @r1 AND rate_companyRtamt = @r2 AND rate_effyrmo = @yrmo AND rate_py = @py";
string _cmdstr2 = "INSERT INTO [Rates] (rate_plhr_id, rate_rateamt,rate_companyRtamt,rate_effyrmo,rate_py) "
+ " VALUES (@pid,@r1,@r2,@yrmo,@py) ";
string _cmdstr3 = "SELECT rate_id FROM [Rates] "
+ " WHERE rate_plhr_id = @pid AND rate_rateamt = @r1 AND rate_companyRtamt = @r2 AND rate_effyrmo = @yrmo AND rate_py = @py";
int _plhrid = 0;
int _foundid = 0;
SqlTransaction ts;
if (connect == null || connect.State == ConnectionState.Closed)
{
connect.Open();
}
ts = connect.BeginTransaction();
try
{
command = new SqlCommand(_cmdstr, connect, ts);
command.Parameters.AddWithValue("@pcode", inValues.PlanCode);
command.Parameters.AddWithValue("@dsc", inValues.PlanDesc.Trim());
command.Parameters.AddWithValue("@tcode", inValues.TierCode);
command.Parameters.AddWithValue("@prog", _pgcode);
if (!command.ExecuteScalar().Equals(DBNull.Value))
{
_plhrid = Convert.ToInt32(command.ExecuteScalar());
}
if (_plhrid != 0)
{
command = new SqlCommand(_cmdstr1, connect, ts);
command.Parameters.AddWithValue("@pid", _plhrid);
command.Parameters.AddWithValue("@r1", inValues.EErate);
command.Parameters.AddWithValue("@r2", inValues.COrate);
command.Parameters.AddWithValue("@yrmo", inValues.EffYrmo);
command.Parameters.AddWithValue("@py", inValues.EffYrmo.Substring(0, 4));
_foundid = Convert.ToInt32(command.ExecuteScalar());
if (_foundid > 0)
{
throw (new Exception(" Rates for selected plan are already defined!"));
}
else
{
command = new SqlCommand(_cmdstr2, connect, ts);
command.Parameters.AddWithValue("@pid", _plhrid);
command.Parameters.AddWithValue("@r1", inValues.EErate);
command.Parameters.AddWithValue("@r2", inValues.COrate);
command.Parameters.AddWithValue("@yrmo", inValues.EffYrmo);
command.Parameters.AddWithValue("@py", inValues.EffYrmo.Substring(0, 4));
command.ExecuteNonQuery();
command = new SqlCommand(_cmdstr3, connect, ts);
command.Parameters.AddWithValue("@pid", _plhrid);
command.Parameters.AddWithValue("@r1", inValues.EErate);
command.Parameters.AddWithValue("@r2", inValues.COrate);
command.Parameters.AddWithValue("@yrmo", inValues.EffYrmo);
command.Parameters.AddWithValue("@py", inValues.EffYrmo.Substring(0, 4));
HttpContext.Current.Session["rateid"] = command.ExecuteScalar();
HttpContext.Current.Session["ratephid"] = _plhrid;
}
}
ts.Commit();
}
catch (Exception ex)
{
ts.Rollback();
if (ex.Message.Contains("Rates for selected plan are already defined!"))
{
throw (new Exception("Error Inserting record - Rates for selected plan are already defined!"));
}
else
{
throw (new Exception("Error Inserting record!"));
}
}
finally
{
connect.Close();
command.Dispose();
}
}