public List <Quotation> Quotations(int id, int problemid)
{
SqlConnection con = new SqlConnection(Resource.CadenaConexion);
con.Open();
List <Quotation> lista = new List <Quotation>();
try
{
SpecialistManager specialistManager = new SpecialistManager();
ProblemManager problemmanager = new ProblemManager();
string sql = "Select qu.Id,ProblemId,qu.specialistid,qu.Description,qu.Price," +
"qu.EstimatedTime,qu.IncludesMaterials,qu.State,qu.StartDate," +
"qu.FinishDate,qu.FinalPrice,qu.SpecialistRate,qu.SpecialistComment," +
"qu.CustomerRate,qu.CustomerComment from Quotations qu " +
"join Problems pro on pro.Id = qu.ProblemId " +
"where pro.customerid = @id and ProblemId = @problemid";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@problemid", System.Data.SqlDbType.NVarChar).Value = problemid;
cmd.Parameters.Add("@id", System.Data.SqlDbType.NVarChar).Value = id;
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
Quotation spe = new Quotation();
spe.id = reader.GetInt32(0);
spe.problem = problemmanager.Obtener(reader.GetInt32(1));
spe.specialist = specialistManager.Obtener(reader.GetInt32(2));
spe.description = reader.GetString(3);
spe.price = reader.GetDecimal(4);
spe.estimatedTime = reader.GetByte(5);
spe.includesMaterial = reader.GetBoolean(6);
spe.state = reader.GetByte(7);
spe.startDate = reader.GetDateTime(8);
spe.finishDate = reader.GetDateTime(9);
spe.finalPrice = reader.GetDecimal(10);
spe.specialistRate = reader.GetDecimal(11);
spe.specialistComment = reader.GetString(12);
spe.customerRate = reader.GetDecimal(13);
spe.customerComment = reader.GetString(14);
lista.Add(spe);
}
reader.Close();
}
catch (Exception e)
{
System.Console.WriteLine(e);
}
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
return(lista);
}
public Quotation Insertar(Quotation spe)
{
Quotation result = null;
SqlConnection con = new SqlConnection(Resource.CadenaConexion);
con.Open();
try
{
string sql = "Insert into Quotations(ProblemId,SpecialistId,Description,Price,EstimatedTime" +
",IncludesMaterials,State,StartDate,FinishDate,FinalPrice,SpecialistRate,SpecialistComment" +
",CustomerRate,CustomerComment) output INSERTED.Id values(@problemid,@specialistid,@description" +
",@price,@estimatedtime,@includesmaterials,@state,@startdate,@finishdate,@finalprice" +
",@specialistrate,@specialistcomment,@customerrate,@customercomment)";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@problemid", System.Data.SqlDbType.Int).Value = spe.problem.id;
cmd.Parameters.Add("@specialistid", System.Data.SqlDbType.Int).Value = spe.specialist.id;
cmd.Parameters.Add("@description", System.Data.SqlDbType.VarChar).Value = spe.description;
cmd.Parameters.Add("@price", System.Data.SqlDbType.Decimal).Value = spe.price;
cmd.Parameters.Add("@estimatedtime", System.Data.SqlDbType.Bit).Value = spe.estimatedTime;
cmd.Parameters.Add("@includesmaterials", System.Data.SqlDbType.Bit).Value = spe.includesMaterial;
cmd.Parameters.Add("@state", System.Data.SqlDbType.Bit).Value = spe.state;
cmd.Parameters.Add("@startdate", System.Data.SqlDbType.DateTime).Value = spe.startDate;
cmd.Parameters.Add("@finishdate", System.Data.SqlDbType.DateTime).Value = spe.finishDate;
cmd.Parameters.Add("@finalprice", System.Data.SqlDbType.Decimal).Value = spe.finalPrice;
cmd.Parameters.Add("@specialistrate", System.Data.SqlDbType.Decimal).Value = spe.specialistRate;
cmd.Parameters.Add("@specialistcomment", System.Data.SqlDbType.VarChar).Value = spe.specialistComment;
cmd.Parameters.Add("@customerrate", System.Data.SqlDbType.Decimal).Value = spe.customerRate;
cmd.Parameters.Add("@customercomment", System.Data.SqlDbType.VarChar).Value = spe.customerComment;
int modified = (int)cmd.ExecuteScalar();
if (modified != 0)
{
ProblemManager problemManager = new ProblemManager();
SpecialistManager specialistManager = new SpecialistManager();
result = Obtener(modified);
result.specialist = specialistManager.Obtener(result.specialist.id);
result.problem = problemManager.Obtener(result.problem.id);
}
}
catch (Exception e)
{
Console.WriteLine(e);
result = null;
}
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
return(result);
}
public Quotation Obtener(int id)
{
Quotation spe = null;
SqlConnection con = new SqlConnection(Resource.CadenaConexion);
con.Open();
string sql = "select Id,ProblemId,SpecialistId,Description," +
"Price,EstimatedTime,IncludesMaterials,State," +
"StartDate,FinishDate,FinalPrice,SpecialistRate," +
"SpecialistComment,CustomerRate,CustomerComment from Quotations where Id = @quotation";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@quotation", System.Data.SqlDbType.NVarChar).Value = id;
SqlDataReader reader =
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (reader.Read())
{
ProblemManager problemManager = new ProblemManager();
SpecialistManager specialistManager = new SpecialistManager();
spe = new Quotation();
spe.id = reader.GetInt32(0);
spe.problem = problemManager.Obtener(reader.GetInt32(1));
spe.specialist = specialistManager.Obtener(reader.GetInt32(2));
spe.description = reader.GetString(3);
spe.price = reader.GetDecimal(4);
spe.estimatedTime = reader.GetByte(5);
spe.includesMaterial = reader.GetBoolean(6);
spe.state = reader.GetByte(7);
spe.startDate = reader.GetDateTime(8);
spe.finishDate = reader.GetDateTime(9);
spe.finalPrice = reader.GetDecimal(10);
spe.specialistRate = reader.GetDecimal(11);
spe.specialistComment = reader.GetString(12);
spe.customerRate = reader.GetDecimal(13);
spe.customerComment = reader.GetString(14);
}
reader.Close();
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
return(spe);
}