private static int AddNewQuote(_Quote quote)
{
SqlCommand cmdQuote = new SqlCommand("INSERT INTO Quote (TotalServiceTime, CustomerID, ServiceDescription, AdditionalLabourPrice) " +
"VALUES (@totalServiceTime, @customerID, @serviceDescription, @additionalLabourPrice)", connection);
cmdQuote.Parameters.Add("@totalServiceTime", SqlDbType.Int).Value = quote.TotalServiceTime;
cmdQuote.Parameters.Add("@customerID", SqlDbType.Int).Value = quote.CustomerID;
cmdQuote.Parameters.Add("@serviceDescription", SqlDbType.Text).Value = quote.ServiceDescription;
cmdQuote.Parameters.Add("@additionalLabourPrice", SqlDbType.Int).Value = quote.AdditionalLabourPrice;
try
{
connection.Open();
quote.ID = Convert.ToInt32(cmdQuote.ExecuteScalar());
/*
* foreach (Item i in quote.Items)
* {
* SqlCommand cmdQP = new SqlCommand("INSERT INTO QuotePart VALUES(" + quote.ID + "," + i.ID + "," + i.Quantity + ")", connection);
* cmdQP.ExecuteNonQuery();
* }*/
}
finally
{
connection.Close();
}
return(quote.ID);
}
private static void sqlGetQuotes()
{
//SqlConnection connection = new SqlConnection("Data Source=fastapps04.qut.edu.au;Initial Catalog=n5499879;User ID=n5499879;Password=ohmedix");
List <_Quote> quotes = new List <_Quote>();
SqlCommand cmd = new SqlCommand("SELECT * FROM Quote", connection);
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
_Quote q = new _Quote();
q.TotalServiceTime = reader.GetInt32(1);
q.PartsETA = reader.GetDateTime(3);
q.IsApproved = reader.GetBoolean(4);
q.PurchaseOrderID = reader.GetInt32(5);
q.ServiceDescription = reader.GetString(7);
q.AdditionalLabourPrice = reader.GetInt32(8);
quotes.Add(q);
}
reader.Close();
foreach (_Quote q in quotes)
{
cmd.CommandText = "SELECT * FROM QuotePart qp INNER JOIN WarehouseStock whs ON qp.PartID = whs.PartID WHERE qp.QuoteID=" + q.ID;
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
_Item i = new _Item();
i.ID = r.GetInt32(1);
i.Quantity = r.GetInt32(2);
i.Description = r.GetString(4);
i.Price = r.GetInt32(5);
i.ServiceTime = r.GetInt32(7);
q.AddItem(i);
}
r.Close();
}
}
finally
{
connection.Close();
}
}
