public static void EditProduct(int amount, int id)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string sql = "";
sql = "select amount from Product where Id = " + id;
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
int sl = int.Parse(dt.Rows[0][0].ToString()) - amount;
sql = @"UPDATE Product SET Amount = " + sl
+ " WHERE Id =" + id;
command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
conn.Close();
}
}
/* == Lấy id sản phẩm trong bảng ComboProduct */
public static ArrayList ListIDProduct(int id)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string sql = "select Product_Id from ComboProduct where Combo_Id=" + id;
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
adapter.Fill(dt);
conn.Close();
ArrayList temp = new ArrayList();
foreach (DataRow row in dt.Rows)
{
temp.Add(row.Field <int>("Product_Id"));
}
return(temp);
}
}
public static bool Trung(string username)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string sql = "select count(*) from Emplopyee where Username='******'";
SqlCommand com = new SqlCommand(sql, conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(com);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
conn.Close();
int countrow = int.Parse(dt.Rows[0][0].ToString());
if (countrow > 0)
{
return(true);
}
return(false);
}
}
public static int GetTotal(int month, int year)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string sql = "select TotalMoney from Invoice where YEAR(CreateDay) = " + year + " and MONTH(CreateDay) = " + month;
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
adapter.Fill(dt);
conn.Close();
int total = 0;
foreach (DataRow row in dt.Rows)
{
total += int.Parse(row.Field <string>("TotalMoney"));
}
return(total);
}
}
public static ArrayList GetIdHd(int month, int year)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string sql = "select Id from Invoice where YEAR(CreateDay) = " + year + " and MONTH(CreateDay) = " + month;
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
adapter.Fill(dt);
conn.Close();
ArrayList list = new ArrayList();
foreach (DataRow row in dt.Rows)
{
list.Add(row.Field <int>("Id"));
}
return(list);
}
}
public static ArrayList GetYear()
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string sql = "select Distinct year(CreateDay) year from Invoice";
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
adapter.Fill(dt);
conn.Close();
ArrayList list = new ArrayList();
foreach (DataRow row in dt.Rows)
{
list.Add(row.Field <int>("year"));
}
return(list);
}
}
public static void AddCombo(Combo combo)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string comboname = combo.comboName;
string image = combo.image;
DateTime daystart = combo.dayStart;
DateTime dayend = combo.dayEnd;
int total = combo.total;
string discountmoney = combo.discountMoney;
string sql = @"INSERT INTO Combo ( [comboName], [dayStart], [dayEnd], [total], [discountMoney], [image]) VALUES" +
"(N'" + comboname + "','" + daystart + "','" + dayend + "'," + total +
", N'" + discountmoney + "', N'" + image + "')";
SqlCommand com = new SqlCommand(sql, conn);
com.ExecuteNonQuery();
conn.Close();
}
}
public static void AddProduct(Product product)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string productname = product.productname;
string image = product.image;
int amount = product.amount;
int price = product.price;
string detail = product.detail;
int company_id = product.companyid;
string sql = @"INSERT INTO Product VALUES" +
"(N'" + productname + "'," + company_id + "," + amount + "," + price +
",N'" + detail + "',N'" + image + "')";
SqlCommand com = new SqlCommand(sql, conn);
com.ExecuteNonQuery();
conn.Close();
}
}
public static void AddKhach(string firstname, string lastname, string phone, string address)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string username = RandomUsername();
string password = DBConnection.HashPassword(phone);
string email = username + "@gmail.com";
string gender = "Male";
string day = DBConnection.FormatDate(DateTime.Now);
int isNew = 2;
string status = "Active";
string sql = "insert into Customer VALUES ('" + username + "','" + password + "','" + email + "',N'" + firstname
+ "',N'" + lastname + "','" + gender + "','" + day + "',N'" + address + "','" + phone
+ "','" + day + "'," + isNew + ",N'" + status + "')";
SqlCommand command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
conn.Close();
}
}
public static void AddInvoice(Invoice invoice)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
int cusid = invoice.customer_id;
string totalmoney = invoice.totalmoney;
string amount = invoice.amount;
string createday = DBConnection.FormatDate(invoice.creatday);
string note = invoice.ordernote;
string postcode = invoice.postcode;
string address = invoice.customeraddress;
string status = invoice.status;
string sql = "insert into Invoice VALUES (" + cusid + ",'" + totalmoney + "','" + amount + "','" + createday
+ "','" + note + "','" + postcode + "',N'" + address + "','" + status + "')";
SqlCommand command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
conn.Close();
}
}
public static void ChangeStatus(string username)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string sql = "select status from Emplopyee where Username='******'";
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
string status = "Active";
if (dt.Rows[0][0].ToString().Equals("Active"))
{
status = "Block";
}
sql = @"UPDATE Emplopyee SET Status = '" + status + "' WHERE Username = '******'";
command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
conn.Close();
}
}
public static void AddComboProduct(ComboProduct comboProduct)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string sql = "select MAX(Id) from Combo";
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
int combo_id = int.Parse(dt.Rows[0][0].ToString());
int product_id = comboProduct.product_id;
sql = @"INSERT INTO ComboProduct VALUES(" + combo_id + ", " + product_id + ")";
SqlCommand com = new SqlCommand(sql, conn);
com.ExecuteNonQuery();
conn.Close();
}
}
public static Combo GetCombo(int id)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
Combo combo = new Combo();
conn.Open();
string sql = "select * from Combo where Id=" + id;
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
conn.Close();
combo.Id = int.Parse(dt.Rows[0][0].ToString());
combo.comboName = dt.Rows[0][1].ToString();
combo.dayStart = DateTime.Parse(dt.Rows[0][2].ToString());
combo.dayEnd = DateTime.Parse(dt.Rows[0][3].ToString());
combo.total = int.Parse(dt.Rows[0][4].ToString());
combo.discountMoney = dt.Rows[0][6].ToString();
combo.image = dt.Rows[0][7].ToString();
return(combo);
}
}
public static List <Company> GetCompany(int id)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string sql = "select * from Company WHERE Id=" + id;
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
adapter.Fill(dt);
List <Company> list = new List <Company>();
foreach (DataRow item in dt.Rows)
{
Company company = new Company();
company.Id = item.Field <int>("Id");
company.CompanyName = item.Field <string>("CompanyName");
list.Add(company);
}
conn.Close();
return(list);
}
}
public static Product GetProduct(int id)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
Product product = new Product();
conn.Open();
string sql = "select * from Product where Id=" + id;
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
conn.Close();
product.id = int.Parse(dt.Rows[0][0].ToString());
product.productname = dt.Rows[0][1].ToString();
product.categoryid = int.Parse(dt.Rows[0][2].ToString());
product.amount = int.Parse(dt.Rows[0][3].ToString());
product.price = int.Parse(dt.Rows[0][4].ToString());
product.detail = dt.Rows[0][5].ToString();
product.image = dt.Rows[0][6].ToString();
return(product);
}
}
public static List <Types> GetTypes(int id)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string sql = "select * from Type WHERE id=" + id;
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
adapter.Fill(dt);
List <Types> types = new List <Types>();
foreach (DataRow item in dt.Rows)
{
Types type = new Types();
type.Id = item.Field <int>("id");
type.TypeName = item.Field <string>("TypeName");
types.Add(type);
}
conn.Close();
return(types);
}
}
public static void addEmployee(Employee employee)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string username = employee.username;
string firstname = employee.firstname;
string lastname = employee.lastname;
string gender = employee.gender;
string address = employee.address;
int role = employee.role;
DateTime dob = employee.birthday;
string password = DBConnection.HashPassword(dob.Day.ToString("dd") + dob.Month.ToString("MM") + dob.Year.ToString());
string joindate = DBConnection.FormatDate(employee.joindate);
string sql = @"INSERT INTO Emplopyee VALUES" +
"('" + username + "','" + password + "',N'" + firstname + "',N'" + lastname +
"','" + gender + "','" + DBConnection.FormatDate(dob) + "',N'" + address + "','" + joindate + "'," + role + ", 'Active')";
SqlCommand com = new SqlCommand(sql, conn);
com.ExecuteNonQuery();
conn.Close();
}
}
public static void EditProduct(Product product, int id)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string productname = product.productname;
string image = product.image;
int amount = product.amount;
int price = product.price;
string detail = product.detail.Replace("'", "\"");
int company_id = product.companyid;
string sql = @"UPDATE Product SET Productname = N'" + productname
+ "', Company_ID = " + company_id
+ ", Amount = " + amount
+ ", Price = " + price
+ ", Detail = N'" + detail
+ "', Image = N'" + image
+ "' WHERE Id =" + id;
SqlCommand command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
conn.Close();
}
}
public static void ChangeStatus(int id)
{
using (SqlConnection conn = DBConnection.GetConnection())
{
conn.Open();
string sql = "select status from Customer where Id = " + id;
SqlCommand command = new SqlCommand(sql, conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
if (dt.Rows[0][0].ToString().Equals("Active"))
{
sql = "Update Customer set status = 'Block' where Id=" + id;
}
else
{
sql = "Update Customer set status = 'Active' where Id=" + id;
}
command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
conn.Close();
}
}