public DataSet ViewDates(int id)
{
//if (conn.State.ToString() == "Closed")
//{
// conn.Open();
//}
//SqlCommand cmd = conn.CreateCommand();
//cmd.Connection = conn;
//cmd.CommandType = CommandType.Text;
//cmd.CommandText = "SELECT Date AS 'Datum pružene usluge' FROM Service WHERE CustomerId=@id AND Active='1'";
//cmd.Parameters.AddWithValue("@id", id);
//cmd.ExecuteNonQuery();
//popunjavanje dataseta podacima (za datagridview)
object[,] array = new object[1, 2];
array[0, 0] = "@id";
array[0, 1] = id;
DB_Sql sql = new DB_Sql();
var cmd = sql.GetSql("SELECT Date AS 'Datum pružene usluge' FROM Service WHERE CustomerId=@id AND Active='1'", 1, array, true);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "Service");
conn.Close();
return(ds);
}
public void AddNewCustomer(string name, string surname, string phone)
{
//if (conn.State.ToString() == "Closed")
//{
// conn.Open();
//}
//SqlCommand cmd = conn.CreateCommand();
//cmd.Connection = conn;
//cmd.CommandType = CommandType.Text;
//cmd.CommandText = "INSERT INTO Customer (Name, Surname, Phone, Points, Status) VALUES(@name, @surname, @phone, 0, 0)";
//cmd.Parameters.AddWithValue("@name", name);
//cmd.Parameters.AddWithValue("@surname", surname);
//cmd.Parameters.AddWithValue("@phone", phone);
//cmd.ExecuteNonQuery();
//conn.Close();
object[,] array = new object[3, 2];
array[0, 0] = "@name";
array[0, 1] = name;
array[1, 0] = "@surname";
array[1, 1] = surname;
array[2, 0] = "@phone";
array[2, 1] = phone;
DB_Sql sql = new DB_Sql();
var cmd = sql.GetSql("INSERT INTO Customer (Name, Surname, Phone, Points, Status) VALUES(@name, @surname, @phone, 0, 0)", 1, array, true);
}
public void SetNumPoints(int numPoints, int sound)
{
//if (conn.State.ToString() == "Closed")
//{
// conn.Open();
//}
//SqlCommand cmd = conn.CreateCommand();
//cmd.Connection = conn;
//cmd.CommandType = CommandType.Text;
//cmd.CommandText = "UPDATE Settings SET NumPoints=@numPoints, SoundCheck=@sound WHERE Id=1";
//cmd.Parameters.AddWithValue("@numPoints", numPoints);
//cmd.Parameters.AddWithValue("@sound", sound);
//cmd.ExecuteNonQuery();
object[,] array = new object[2, 2];
array[0, 0] = "@numPoints";
array[0, 1] = numPoints;
array[1, 0] = "@sound";
array[1, 1] = sound;
DB_Sql sql = new DB_Sql();
var cmd = sql.GetSql("UPDATE Settings SET NumPoints=@numPoints, SoundCheck=@sound WHERE Id=1", 1, array, true);
conn.Close();
}
public DataSet ViewCustomers()
{
//if (conn.State.ToString() == "Closed")
//{
// conn.Open();
//}
//SqlCommand cmd = conn.CreateCommand();
//cmd.Connection = conn;
//cmd.CommandType = CommandType.Text;
//cmd.CommandText = "SELECT Id, Name AS Ime, Surname AS Prezime, Phone AS Kontakt, Points AS Bodovi, Status AS 'Gratis šišanje' FROM Customer";
//cmd.ExecuteNonQuery();
DB_Sql h = new DB_Sql();
var cmd = h.GetSql("SELECT Id, Name AS Ime, Surname AS Prezime, Phone AS Kontakt, Points AS Bodovi, Status AS 'Gratis šišanje' FROM Customer", 1, null, false);
//popunjavanje dataseta podacima (za datagridview)
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "Customer");
//conn.Close();
return(ds);
}
public void UpdateOwnerData(string company,
string ownerName,
string ownerSurname,
string city,
string address,
string postCode,
string homeNum)
{
//if (conn.State.ToString() == "Closed")
//{
// conn.Open();
//}
//SqlCommand cmd = conn.CreateCommand();
//cmd.Connection = conn;
//cmd.CommandType = CommandType.Text;
//cmd.CommandText = "UPDATE Owner SET Company=@company, OwnerName=@ownerName, OwnerSurname=@ownerSurname, City=@city, Adress=@address, PostCode=@postCode, HomeNum=@homeNum WHERE Id=1";
//cmd.Parameters.AddWithValue("@company", company);
//cmd.Parameters.AddWithValue("@ownerName", ownerName);
//cmd.Parameters.AddWithValue("@ownerSurname", ownerSurname);
//cmd.Parameters.AddWithValue("@city", city);
//cmd.Parameters.AddWithValue("@address", address);
//cmd.Parameters.AddWithValue("@postCode", postCode);
//cmd.Parameters.AddWithValue("@homeNum", homeNum);
//cmd.ExecuteNonQuery();
object[,] array = new object[7, 2];
array[0, 0] = "@company";
array[0, 1] = company;
array[1, 0] = "@ownerName";
array[1, 1] = ownerName;
array[2, 0] = "@ownerSurname";
array[2, 1] = ownerSurname;
array[3, 0] = "@city";
array[3, 1] = city;
array[4, 0] = "@address";
array[4, 1] = address;
array[5, 0] = "@postCode";
array[5, 1] = postCode;
array[6, 0] = "@homeNum";
array[6, 1] = homeNum;
DB_Sql sql = new DB_Sql();
var cmd = sql.GetSql("UPDATE Owner SET Company=@company, OwnerName=@ownerName, OwnerSurname=@ownerSurname, City=@city, Adress=@address, PostCode=@postCode, HomeNum=@homeNum WHERE Id=1", 1, array, true);
conn.Close();
}
public DataSet SearchCustomer(string name, string surname)
{
//if (conn.State.ToString() == "Closed")
//{
// conn.Open();
//}
//SqlCommand cmd = conn.CreateCommand();
//cmd.Connection = conn;
//cmd.CommandType = CommandType.Text;
string sqlString;
if (String.IsNullOrEmpty(name))
{
sqlString = "SELECT * FROM Customer WHERE (Surame=@surname)";
}
else if (String.IsNullOrEmpty(surname))
{
sqlString = "SELECT * FROM Customer WHERE (Name=@name)";
}
else
{
sqlString = "SELECT * FROM Customer WHERE (Name=@name) AND (Surname=@surname)";
}
//cmd.Parameters.AddWithValue("@name", name);
//cmd.Parameters.AddWithValue("@surname", surname);
//cmd.ExecuteNonQuery();
object[,] array = new object[2, 2] {
{ "@name", name }, { "@surname", surname }
};
DB_Sql sql = new DB_Sql();
var cmd = sql.GetSql(sqlString, 1, array, true);
//popunjavanje dataseta podacima (za datagridview)
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "Customer");
conn.Close();
return(ds);
}
public void UpdateCustomer(string name, string surname, string phone, int id)
{
//if (conn.State.ToString() == "Closed")
//{
// conn.Open();
//}
//SqlCommand cmd = conn.CreateCommand();
//cmd.Connection = conn;
//cmd.CommandType = CommandType.Text;
//cmd.CommandText = "UPDATE Customer SET Name=@name, Surname=@surname, Phone=@phone WHERE Id=@id";
//cmd.Parameters.AddWithValue("@name", name);
//cmd.Parameters.AddWithValue("@surname", surname);
//cmd.Parameters.AddWithValue("@phone", phone);
////cmd.Parameters.AddWithValue("@points", 0);
////cmd.Parameters.AddWithValue("@status", 0);
//cmd.Parameters.AddWithValue("@id", id);
//cmd.ExecuteNonQuery();
object[,] array = new object[4, 2];
array[0, 0] = "@name";
array[0, 1] = name;
array[1, 0] = "@surname";
array[1, 1] = surname;
array[2, 0] = "@phone";
array[2, 1] = phone;
array[3, 0] = "@id";
array[3, 1] = id;
//array[3, 0] = "@points";
//array[3, 1] = 0;
//array[4, 0] = "@status";
//array[4, 1] = 0;
DB_Sql sql = new DB_Sql();
var cmd = sql.GetSql("UPDATE Customer SET Name=@name, Surname=@surname, Phone=@phone WHERE Id=@id", 1, array, true);
conn.Close();
}
public void DeleteCustomer(int id)
{
//if (conn.State.ToString() == "Closed")
//{
// conn.Open();
//}
//SqlCommand cmd = conn.CreateCommand();
//cmd.Connection = conn;
//cmd.CommandType = CommandType.Text;
//cmd.CommandText = "DELETE FROM Service WHERE CustomerId=@id; DELETE FROM Customer WHERE Id=@id;";
//cmd.Parameters.AddWithValue("@id", id);
//cmd.ExecuteNonQuery();
object[,] array = new object[1, 2];
array[0, 0] = "@id";
array[0, 1] = id;
DB_Sql sql = new DB_Sql();
var cmd = sql.GetSql("DELETE FROM Service WHERE CustomerId=@id; DELETE FROM Customer WHERE Id=@id;", 1, array, true);
conn.Close();
}