public int insertCar(Car car, string user)
{
int car_id = 0;
try
{
string query = "dbo.insertCar";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CarDiaryDB"].ToString());
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = query;
cmd.Connection = conn;
//set up the parameters
cmd.Parameters.Add("@bnd", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@mdl", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@year", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@eng", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@fuel", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@pwr", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@img", System.Data.SqlDbType.NVarChar, 500);
cmd.Parameters.Add("@usr", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;
//set parameter values
cmd.Parameters["@bnd"].Value = car.getCarBrand();
cmd.Parameters["@mdl"].Value = car.getCarModel();
cmd.Parameters["@year"].Value = car.getCarYear();
cmd.Parameters["@eng"].Value = car.getCarEngine();
cmd.Parameters["@fuel"].Value = car.getCarFuel();
cmd.Parameters["@pwr"].Value = car.getCarHPowers();
cmd.Parameters["@img"].Value = car.getCarImage();
cmd.Parameters["@usr"].Value = user;
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
//read output value
car_id = Convert.ToInt32(cmd.Parameters["@id"].Value);
//car_id = (int)cmd.ExecuteScalar();
conn.Close();
}
catch (Exception ex)
{
// Log the exception.
ExceptionUtility.LogException(ex, "DB.cs");
}
return car_id;
}
private void addNewTable(Car car)
{
Table tbl = new Table();
tbl.Width = new Unit("100%");
tbl.Height = new Unit("100%");
//------------------row-----------------------
TableRow rw = new TableRow();
//---------------------cell--------------
TableCell cell = new TableCell();
cell.Width = 300;
LinkButton carTitle = new LinkButton();
carTitle.Font.Size = 20;
carTitle.Text = car.getCarBrand() + " " + car.getCarModel();
carTitle.Click += new EventHandler(LinkButton1_Click);
carTitle.ID = "Title" + car.getId().ToString();
carTitle.CssClass = "linkbtn";
cell.Controls.Add(carTitle);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
Panel div = new Panel();
div.Style.Add("float", "right");
Button btnEdit = new Button();
btnEdit.Click += new EventHandler(btnAddFuel_Click);
btnEdit.Text = "Редактирай";
btnEdit.Style.Add("margin-left", "15px");
btnEdit.Style.Add("margin-right", "15px");
btnEdit.ID = "Edit" + car.getId().ToString();
btnEdit.CssClass = "btn";
div.Controls.Add(btnEdit);
cell.Controls.Add(div);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
Panel div2 = new Panel();
div.Style.Add("float", "left");
Button btnDel = new Button();
btnDel.Click += new EventHandler(btnDel_Click);
btnDel.Text = "Изтрий";
btnDel.Style.Add("margin-left", "15px");
btnDel.Style.Add("margin-right", "15px");
btnDel.ID = "Delete" + car.getId().ToString();
btnDel.CssClass = "btn";
div2.Controls.Add(btnDel);
cell.Controls.Add(div2);
rw.Cells.Add(cell);
tbl.Controls.Add(rw);
//------------------row-----------------------
rw = new TableRow();
//---------------------cell--------------
cell = new TableCell();
cell.RowSpan = 5;
cell.Width = 300;
ImageButton img = new ImageButton();
img.ImageUrl = car.getCarImage();
img.Width = 300;
img.Height = 150;
img.Style.Add("min-height", "150px");
img.Style.Add("min-width", "100px");
img.ID = "Img" + car.getId().ToString();
img.ToolTip = "Виж история на автомобила";
img.Click += new ImageClickEventHandler(img_Click);
cell.Controls.Add(img);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
cell.ColumnSpan = 2;
cell.Style.Add("height", "10px");
Label carInfo = new Label();
carInfo.Font.Size = 10;
carInfo.Text = car.getCarFuel() + ", " + car.getCarEngine() + ", " + car.getCarHPowers() + " к.с., " + car.getCarYear();
cell.Controls.Add(carInfo);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
cell.ColumnSpan = 4;
cell.RowSpan = 3;
List<Tuple<Double, DateTime>> list = db.getTotalCostPerMonth(car.getId());
List<Double> total_cost = new List<double>();
List<DateTime> period = new List<DateTime>();
foreach (Tuple<Double, DateTime> item in list)
{
total_cost.Add(item.Item1);
period.Add(item.Item2);
}
List<String> sPeriod = new List<string>();
foreach (DateTime item in period)
{
sPeriod.Add(item.ToString("MM/yyyy"));
}
if (total_cost.Count > 0)
{
Chart chart = new Chart();
Series series = new Series("Default");
series.ChartType = SeriesChartType.Spline;
series["PieLabelStyle"] = "Disabled";
chart.Series.Add(series);
ChartArea chartArea = new ChartArea();
ChartArea3DStyle areaStyle = new ChartArea3DStyle(chartArea);
areaStyle.Rotation = 0;
Axis yAxis = new Axis(chartArea, AxisName.Y);
Axis xAxis = new Axis(chartArea, AxisName.X);
chart.ChartAreas.Add(chartArea);
// Bind the data to the chart
chart.Series["Default"].Points.DataBindXY(sPeriod, total_cost);
chart.Width = new System.Web.UI.WebControls.Unit(600, System.Web.UI.WebControls.UnitType.Pixel);
chart.Height = new System.Web.UI.WebControls.Unit(200, System.Web.UI.WebControls.UnitType.Pixel);
//string filename = Server.MapPath("~") + "/Chart.png";
//chart.SaveImage(filename, ChartImageFormat.Png);
chart.Legends.Add(new Legend("Legend1"));
chart.Series["Default"].LegendText = "Обща сума в лв.";
cell.Controls.Add(chart);
}
rw.Cells.Add(cell);
tbl.Controls.Add(rw);
//------------------row-----------------------
rw = new TableRow();
//---------------------cell--------------
cell = new TableCell();
cell.ColumnSpan = 2;
Label averageCons = new Label();
averageCons.Font.Bold = true;
averageCons.Font.Size = 35;
averageCons.Style.Add("margin-left", "10px");
double average = db.getAvgCons(car.getId());
if (average == 0)
{
averageCons.Text = "---";
}
else
{
averageCons.Text = average.ToString("0.##");
}
cell.Controls.Add(averageCons);
rw.Cells.Add(cell);
tbl.Controls.Add(rw);
//------------------row-----------------------
rw = new TableRow();
//---------------------cell--------------
cell = new TableCell();
cell.ColumnSpan = 2;
Label averageConsInfo = new Label();
averageConsInfo.Font.Size = 10;
averageConsInfo.Style.Add("margin-left", "10px");
if (car.getCarFuel().Equals("Електричество"))
{
averageConsInfo.Text = "кВч/100км " + car.getCarFuel();
}
else averageConsInfo.Text = "л./100км " + car.getCarFuel();
cell.Controls.Add(averageConsInfo);
rw.Cells.Add(cell);
tbl.Controls.Add(rw);
//------------------row-----------------------
rw = new TableRow();
//---------------------cell--------------
cell = new TableCell();
Label fillUps = new Label();
fillUps.Font.Size = 11;
fillUps.Font.Bold = true;
fillUps.Style.Add("margin-left", "10px");
fillUps.Style.Add("text-align", "center");
fillUps.Width = new Unit("100%");
int numFillUps = db.getFillUps(car.getId());
if (numFillUps == 0)
fillUps.Text = "-";
else
fillUps.Text = numFillUps.ToString();
cell.Controls.Add(fillUps);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
Label minCons = new Label();
minCons.Font.Size = 11;
minCons.Font.Bold = true;
minCons.Style.Add("margin-left", "10px");
minCons.Style.Add("text-align", "center");
minCons.Width = new Unit("100%");
double minConsDB = db.getMinCons(car.getId());
if (minConsDB == 0)
minCons.Text = "-";
else
minCons.Text = minConsDB.ToString("0.##");
cell.Controls.Add(minCons);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
Label km = new Label();
km.Font.Size = 11;
km.Font.Bold = true;
km.Style.Add("margin-left", "10px");
km.Style.Add("text-align", "center");
km.Width = new Unit("100%");
int kmDB = db.getKM(car.getId());
if (kmDB == 0)
km.Text = "-";
else
km.Text = kmDB.ToString("0.##");
cell.Controls.Add(km);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
Label pricePerKm = new Label();
pricePerKm.Font.Size = 11;
pricePerKm.Font.Bold = true;
pricePerKm.Style.Add("margin-left", "10px");
pricePerKm.Style.Add("text-align", "center");
pricePerKm.Width = new Unit("100%");
double price = db.getTotalPrice(car.getId());
if (price != 0 && kmDB != 0)
pricePerKm.Text = (price / kmDB).ToString("0.##");
else
pricePerKm.Text = "-";
cell.Controls.Add(pricePerKm);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
rw.Cells.Add(cell);
tbl.Controls.Add(rw);
//------------------row-----------------------
rw = new TableRow();
//---------------------cell--------------
cell = new TableCell();
cell.Width = new Unit("15%");
Label fillUpsInfo = new Label();
fillUpsInfo.Font.Size = 9;
fillUpsInfo.Style.Add("margin-left", "10px");
fillUpsInfo.Style.Add("text-align", "center");
fillUpsInfo.Width = new Unit("100%");
fillUpsInfo.Text = "зареждания";
cell.Controls.Add(fillUpsInfo);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
cell.Width = new Unit("15%");
Label minConsInfo = new Label();
minConsInfo.Font.Size = 9;
minConsInfo.Style.Add("margin-left", "10px");
minConsInfo.Style.Add("text-align", "center");
minConsInfo.Width = new Unit("100%");
if (car.getCarFuel().Equals("Електричество"))
minConsInfo.Text = "мин кВч/100 км";
else
minConsInfo.Text = "мин л./100 км";
cell.Controls.Add(minConsInfo);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
cell.Width = new Unit("15%");
Label kmInfo = new Label();
kmInfo.Font.Size = 9;
kmInfo.Style.Add("margin-left", "10px");
kmInfo.Style.Add("text-align", "center");
kmInfo.Width = new Unit("100%");
kmInfo.Text = "изминати км";
cell.Controls.Add(kmInfo);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
cell.Width = new Unit("15%");
Label pricePerKmInfo = new Label();
pricePerKmInfo.Font.Size = 9;
pricePerKmInfo.Style.Add("margin-left", "10px");
pricePerKmInfo.Style.Add("text-align", "center");
pricePerKmInfo.Width = new Unit("100%");
pricePerKmInfo.Text = "цена на км";
cell.Controls.Add(pricePerKmInfo);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
cell.Width = new Unit("20%");
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
cell.Width = new Unit("20%");
rw.Cells.Add(cell);
tbl.Controls.Add(rw);
//------------------row-----------------------
rw = new TableRow();
//---------------------cell--------------
cell = new TableCell();
rw.Cells.Add(cell);
tbl.Controls.Add(rw);
//------------------row-----------------------
rw = new TableRow();
//---------------------cell--------------
cell = new TableCell();
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
cell.ColumnSpan = 2;
Button buttonAddFuel = new Button();
buttonAddFuel.Click += new EventHandler(btnAddFuel_Click);
buttonAddFuel.Width = new Unit("75%");
buttonAddFuel.Text = "Добави гориво";
buttonAddFuel.Style.Add("margin-left", "15px");
buttonAddFuel.Style.Add("margin-right", "15px");
buttonAddFuel.ID = "Fuel" + car.getId().ToString();
buttonAddFuel.CssClass = "btn";
//buttonAddFuel
cell.Controls.Add(buttonAddFuel);
rw.Cells.Add(cell);
//---------------------cell--------------
cell = new TableCell();
cell.ColumnSpan = 2;
Button buttonAddOtherCost = new Button();
buttonAddOtherCost.Click += new EventHandler(btnAddFuel_Click);
buttonAddOtherCost.Width = new Unit("75%");
buttonAddOtherCost.Text = "Добави друг разход";
buttonAddOtherCost.Style.Add("margin-left", "15px");
buttonAddOtherCost.Style.Add("margin-right", "15px");
buttonAddOtherCost.ID = "Other" + car.getId().ToString();
buttonAddOtherCost.CssClass = "btn";
cell.Controls.Add(buttonAddOtherCost);
rw.Cells.Add(cell);
tbl.Controls.Add(rw);
Panel1.Controls.Add(tbl);
Panel1.Controls.Add(new LiteralControl("<br />"));
Panel1.Controls.Add(new LiteralControl("<hr />"));
}
public bool updateCar(int carId, Car car)
{
bool update = false;
try
{
string query = "dbo.updateCar";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CarDiaryDB"].ToString());
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = query;
cmd.Connection = conn;
cmd.Parameters.Add("@bnd", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@mdl", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@year", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@eng", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@fuel", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@pwr", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@img", System.Data.SqlDbType.NVarChar, 500);
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int);
//set parameter values
cmd.Parameters["@bnd"].Value = car.getCarBrand();
cmd.Parameters["@mdl"].Value = car.getCarModel();
cmd.Parameters["@year"].Value = car.getCarYear();
cmd.Parameters["@eng"].Value = car.getCarEngine();
cmd.Parameters["@fuel"].Value = car.getCarFuel();
cmd.Parameters["@pwr"].Value = car.getCarHPowers();
cmd.Parameters["@img"].Value = car.getCarImage();
cmd.Parameters["@id"].Value = carId;
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
update = true;
conn.Close();
}
catch (Exception ex)
{
update = false;
// Log the exception.
ExceptionUtility.LogException(ex, "DB.cs");
}
return update;
}