public Combo GetComboByID(string ID)
{
ConnectDB.DbConnection();
string query = "Select * from combo where ID=@ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("@ID", ID);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
if (dr.Read())
{
//string[] array = dr["Product_List"].ToString().Split(',');
return(new Combo()
{
combo_ID = Convert.ToInt32(dr["ID"].ToString()),
combo_name = dr["Combo_Name"].ToString(),
product_list = dr["Product_List"].ToString(),
total_money = dr["totalMoney"].ToString(),
discount_money = dr["discountMoney"].ToString(),
start_date = Convert.ToDateTime(dr["startDate"].ToString()),
end_date = Convert.ToDateTime(dr["endDate"].ToString()),
image_combo = dr["Image_Combo"].ToString(),
});
}
}
return(null);
}
}
public InvoiceDetail GetInvoiceDetailByID(string ID)
{
ConnectDB.DbConnection();
string query = "Select * from InvoiceDetail where Invoice_ID=@Invoice_ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("@ID", ID);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
if (dr.Read())
{
return(new InvoiceDetail()
{
Invoice_ID = Convert.ToInt32(dr["Invoice_ID"].ToString()),
Product_ID = Convert.ToInt32(dr["Product_ID"].ToString()),
Combo_ID = Convert.ToInt32(dr["Combo_ID"].ToString()),
Amount = Convert.ToInt32(dr["Amount"].ToString()),
Price = dr["Price"].ToString()
});
}
}
return(null);
}
}
public Employee GetEmpByID(string ID)
{
ConnectDB.DbConnection();
string query = "Select * from employee where ID=@ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("@ID", ID);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
if (dr.Read())
{
return(new Employee()
{
employee_ID = Convert.ToInt32(dr["ID"].ToString()),
username = dr["username"].ToString(),
password = dr["password"].ToString(),
firstName = dr["firstName"].ToString(),
lastName = dr["lastName"].ToString(),
gender = dr["gender"].ToString(),
address = dr["address"].ToString(),
role_ID = Convert.ToInt32(dr["Role_ID"].ToString())
});
}
}
return(null);
}
}
public Product GetProByID(string ID)
{
ConnectDB.DbConnection();
string query = "select p.ID, p.Product_Name, c.Catalog_Name, p.Catalog_Id, p.Amount, p.Price, p.Image_Name, p.Detail " +
"from Product p, Catalog c " +
"where p.ID=@ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("@ID", ID);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
if (dr.Read())
{
return(new Product()
{
product_ID = Convert.ToInt32(dr["ID"].ToString()),
product_name = dr["Product_Name"].ToString(),
catalog_ID = Convert.ToInt32(dr["Catalog_ID"].ToString()),
price = dr["Price"].ToString(),
amount = Convert.ToInt32(dr["Amount"].ToString()),
product_detail = dr["Detail"].ToString(),
img = dr["Image_Name"].ToString(),
});
}
}
return(null);
}
}
public Customer GetCusByID(string ID)
{
ConnectDB.DbConnection();
string query = "Select * from customer where ID=@ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("@ID", ID);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
if (dr.Read())
{
return(new Customer()
{
ID = Convert.ToInt32(dr["ID"].ToString()),
username = dr["username"].ToString(),
password = dr["password"].ToString(),
firstName = dr["firstName"].ToString(),
lastName = dr["lastName"].ToString(),
gender = dr["gender"].ToString(),
//birthDate = Convert.ToDateTime(dr["birthDate"].ToString()),
//joinDate = Convert.ToDateTime(dr["joinDate"].ToString()),
address = dr["address"].ToString(),
phoneNumber = dr["phoneNumber"].ToString()
});
}
}
return(null);
}
}
public Invoice GetInvoiceByID(string ID)
{
ConnectDB.DbConnection();
string query = "Select * from Invoice where ID=@ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("@ID", ID);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
if (dr.Read())
{
return(new Invoice()
{
ID = Convert.ToInt32(dr["ID"].ToString()),
Invoice_Name = dr["Invoice_Name"].ToString(),
totalMoney = dr["totalMoney"].ToString(),
createdDate = Convert.ToDateTime(dr["createdDate"].ToString()),
shipDate = Convert.ToDateTime(dr["shipDate"].ToString()),
customerAddress = dr["customerAddress"].ToString()
});
}
}
return(null);
}
}
public void AddCatalog(Catalog catalog)
{
ConnectDB.DbConnection();
string query = "insert into catalog(Catalog_Name) values (@Catalog_Name)";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.Add("@Catalog_Name", SqlDbType.NVarChar).Value = catalog.catalog_name;
cmd.ExecuteNonQuery();
}
public void DeletePro(string ID)
{
ConnectDB.DbConnection();
string query = "delete from product where ID = @ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("@ID", ID);
cmd.ExecuteNonQuery();
}
//public void DeleteCatalog(string ID)
//{
// ConnectDB.DbConnection();
// string query = "delete from catalog where ID = @ID";
// SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
// cmd.Parameters.AddWithValue("@ID", ID);
// cmd.ExecuteNonQuery();
//}
public void UpdateCatalog(Catalog catalog)
{
ConnectDB.DbConnection();
string query = "update catalog set Catalog_Name =@Catalog_Name where ID = @ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("Catalog_Name", catalog.catalog_name);
cmd.Parameters.AddWithValue("ID", catalog.catalog_ID);
cmd.ExecuteNonQuery();
}
public void AddDetailInvoice(InvoiceDetail invoiceDetail)
{
ConnectDB.DbConnection();
string query = "insert into InvoiceDetail (Invoice_ID, Product_ID, Combo_ID, Amount, Price ) values (@Invoice_ID, @Product_ID, @Combo_ID, @Amount, @Price )";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("Product_ID", invoiceDetail.Product_ID);
cmd.Parameters.AddWithValue("Combo_ID", invoiceDetail.Combo_ID);
cmd.Parameters.AddWithValue("Amount", invoiceDetail.Amount);
cmd.Parameters.AddWithValue("Price", invoiceDetail.Price);
cmd.Parameters.AddWithValue("Invoice_ID", invoiceDetail.Invoice_ID);
cmd.ExecuteNonQuery();
}
public void UpdateCus(Customer customer)
{
ConnectDB.DbConnection();
string query = "update customer set firstName =@firstName, lastName =@lastName, gender =@gender, address = @address where ID = @ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("firstName", customer.firstName);
cmd.Parameters.AddWithValue("lastName", customer.lastName);
cmd.Parameters.AddWithValue("gender", customer.gender);
cmd.Parameters.AddWithValue("address", customer.address);
cmd.Parameters.AddWithValue("ID", customer.ID);
cmd.ExecuteNonQuery();
}
public DataTable GetAll()
{
string query = "select ID, Combo_Name, totalMoney, discountMoney, startDate, endDate from combo";
ConnectDB.DbConnection();
SqlCommand command = new SqlCommand(query, ConnectDB.db);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public DataTable GetAll()
{
string query = "select * from catalog";
ConnectDB.DbConnection();
SqlCommand command = new SqlCommand(query, ConnectDB.db);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public DataTable GetAll()
{
string query = "select id, username, password, phoneNumber, birthDate, address, joinDate,gender, firstName + ' ' + lastName as name from customer";
ConnectDB.DbConnection();
SqlCommand command = new SqlCommand(query, ConnectDB.db);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public DataTable LoadCatalog()
{
string data = "select ID, Catalog_Name from Catalog";
ConnectDB.DbConnection();
SqlCommand command = new SqlCommand(data, ConnectDB.db);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
DataSet ds = new DataSet();
da.Fill(ds);
return(ds.Tables[0]);
}
public void AddPro(Product product)
{
ConnectDB.DbConnection();
string query = "insert into product(Product_Name,catalog_ID,Amount,Price,Image_Name,Detail) values (@Product_Name,@Catalog_ID,@Amount,@Price,@Image_Name,@Detail)";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.Add("@Product_Name", SqlDbType.NVarChar).Value = product.product_name;
cmd.Parameters.Add("@Catalog_ID", SqlDbType.Int).Value = product.catalog_ID;
cmd.Parameters.Add("@Amount", SqlDbType.Int).Value = product.amount;
cmd.Parameters.Add("@Price", SqlDbType.NVarChar).Value = product.price;
cmd.Parameters.Add("@Image_Name", SqlDbType.NVarChar).Value = product.img;
cmd.Parameters.Add("@Detail", SqlDbType.NVarChar).Value = product.product_detail;
//cmd.Parameters.Add("@ID", SqlDbType.Int).Value = product.product_ID;
cmd.ExecuteNonQuery();
}
public void UpdatePro(Product product)
{
ConnectDB.DbConnection();
string query = "update product set Product_Name =@Product_Name,Catalog_ID =@Catalog_ID, Amount =@Amount, Price =@Price, Image_Name = @Image_Name, Detail = @Detail where ID = @ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("Product_Name", product.product_name);
cmd.Parameters.AddWithValue("Catalog_ID", product.catalog_ID);
cmd.Parameters.AddWithValue("Amount", product.amount);
cmd.Parameters.AddWithValue("Price", product.price);
cmd.Parameters.AddWithValue("Image_Name", product.img);
cmd.Parameters.AddWithValue("Detail", product.product_detail);
cmd.Parameters.AddWithValue("ID", product.product_ID);
cmd.ExecuteNonQuery();
}
public DataTable GetAll()
{
string query = "select e.id, e.username, e.password, e.address,e.gender, r.role_name as role, e.firstName + ' ' + e.lastName as name " +
"from employee e, role r where e.role_ID=r.ID";
ConnectDB.DbConnection();
SqlCommand command = new SqlCommand(query, ConnectDB.db);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public void UpdateInvoice(Invoice invoice)
{
ConnectDB.DbConnection();
string query = "update Invoice set Invoice_Name =@Invoice_Name, totalMoney =@totalMoney, createdDate =@createdDate, customerAddress =@customerAddress, shipDate = @shipDate, Shipper_ID = @Shipper_ID, ID_Customer = @ID_Customer where ID = @ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("Invoice_Name", invoice.Invoice_Name);
cmd.Parameters.AddWithValue("totalMoney", invoice.totalMoney);
cmd.Parameters.AddWithValue("createdDate", invoice.createdDate);
cmd.Parameters.AddWithValue("customerAddress", invoice.customerAddress);
cmd.Parameters.AddWithValue("shipDate", invoice.shipDate);
cmd.Parameters.AddWithValue("Shipper_ID", invoice.Shipper_ID);
cmd.Parameters.AddWithValue("ID", invoice.ID);
cmd.ExecuteNonQuery();
}
public DataTable GetAll()
{
string query = "select p.ID, p.Product_Name, c.Catalog_Name as catalog, p.Amount, p.Price, p.Image_Name, p.Detail " +
"from Product as p, Catalog as c " +
"where p.Catalog_ID=c.ID";
ConnectDB.DbConnection();
SqlCommand command = new SqlCommand(query, ConnectDB.db);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public void AddEmp(Employee employee)
{
ConnectDB.DbConnection();
string query = "insert into employee(username, password,firstName,lastName,gender,address, role_ID) " +
"values (@username,@password,@firstName,@lastName,@gender,@address, @role_ID)";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = employee.username;
cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = employee.password;
cmd.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = employee.firstName;
cmd.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = employee.lastName;
cmd.Parameters.Add("@gender", SqlDbType.NVarChar).Value = employee.gender;
cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = employee.address;
cmd.Parameters.Add("@role_ID", SqlDbType.NVarChar).Value = employee.role_ID;
cmd.ExecuteNonQuery();
}
public void UpdateEmp(Employee employee)
{
ConnectDB.DbConnection();
string query = "update employee set firstName =@firstName, lastName =@lastName, @username = username, @password = password, gender =@gender, role_ID = @role_ID, address = @address where ID = @ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("firstName", employee.firstName);
cmd.Parameters.AddWithValue("lastName", employee.lastName);
cmd.Parameters.AddWithValue("gender", employee.gender);
cmd.Parameters.AddWithValue("address", employee.address);
cmd.Parameters.AddWithValue("role_ID", employee.role_ID);
cmd.Parameters.AddWithValue("username", employee.username);
cmd.Parameters.AddWithValue("password", employee.password);
cmd.Parameters.AddWithValue("ID", employee.employee_ID);
cmd.ExecuteNonQuery();
}
public void AddInvoice(Invoice invoice)
{
ConnectDB.DbConnection();
string query = "insert into Invoice (Invoice_Name, ID_Customer, totalMoney, createdDate, customerAddress, Shipper_ID, shipDate) values ( @Invoice_Name, @ID_Customer, @totalMoney, @createdDate, @customerAddress, @shipDate, @Shipper_ID)";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("Invoice_Name", invoice.Invoice_Name);
cmd.Parameters.AddWithValue("totalMoney", invoice.totalMoney);
cmd.Parameters.AddWithValue("createdDate", invoice.createdDate);
cmd.Parameters.AddWithValue("customerAddress", invoice.customerAddress);
cmd.Parameters.AddWithValue("shipDate", invoice.shipDate);
cmd.Parameters.AddWithValue("Shipper_ID", invoice.Shipper_ID);
cmd.Parameters.AddWithValue("ID_Customer", invoice.ID_Customer);
cmd.Parameters.AddWithValue("ID", invoice.ID);
cmd.ExecuteNonQuery();
}
public DataTable GetAll()
{
string query = "select Invoice.ID , Invoice.Invoice_Name," +
" Invoice.totalMoney , Invoice.createdDate ," +
" Invoice.customerAddress , Invoice.shipDate , " +
"Employee.lastName as shiper " +
" from Invoice join Employee on Invoice.Shipper_ID = Employee.ID";
ConnectDB.DbConnection();
SqlCommand command = new SqlCommand(query, ConnectDB.db);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public void AddCus(Customer customer)
{
ConnectDB.DbConnection();
string query = "insert into customer(username, password,phoneNumber,firstName,lastName,gender,address) " +
"values (@username,@password,@phoneNumber,@firstName,@lastName,@gender,@address)";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = customer.username;
cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = customer.password;
cmd.Parameters.Add("@phoneNumber", SqlDbType.NVarChar).Value = customer.phoneNumber;
cmd.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = customer.firstName;
cmd.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = customer.lastName;
cmd.Parameters.Add("@gender", SqlDbType.NVarChar).Value = customer.gender;
//cmd.Parameters.Add("@birthDate", SqlDbType.DateTime).Value = customer.birthDate;
cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = customer.address;
//cmd.Parameters.Add("@joinDate", SqlDbType.DateTime).Value = customer.joinDate;
//cmd.Parameters.Add("@ID", SqlDbType.Int).Value = product.product_ID;
cmd.ExecuteNonQuery();
}
public Catalog GetCatalogByID(string ID)
{
ConnectDB.DbConnection();
string query = "select ID, Catalog_Name from catalog where ID=@ID";
SqlCommand cmd = new SqlCommand(query, ConnectDB.db);
cmd.Parameters.AddWithValue("@ID", ID);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
if (dr.Read())
{
return(new Catalog()
{
catalog_ID = Convert.ToInt32(dr["ID"].ToString()),
catalog_name = dr["Catalog_Name"].ToString(),
});
}
}
return(null);
}
}