//Tìm xe theo nhiều điều kiện
public static DataTable SearchCar(string _BienSo)
{
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("FindCars", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_BienSo", MySqlDbType.VarChar, 10);
cmd.Parameters["@_BienSo"].Value = _BienSo;
Conn.Open();
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
Conn.Close();
return(dt);
}
public static void insertChiTietPhieuChuaChua(ChiTietPhieuSuaChuaDTO ChiTiet)
{
MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand command = new MySqlCommand("InsertCHITIETPHIEUSUACHUA", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@_MaPhieuSuaChua", MySqlDbType.VarChar, 10);
command.Parameters.Add("@_NoiDung", MySqlDbType.VarChar, 100);
command.Parameters.Add("@_MaVatTuPhuTung", MySqlDbType.VarChar, 10);
command.Parameters.Add("@_SoLuongSuaChua", MySqlDbType.Int16);
command.Parameters.Add("@_TienCong", MySqlDbType.Decimal);
command.Parameters.Add("@_ThanhTien", MySqlDbType.Decimal);
command.Parameters["@_MaPhieuSuaChua"].Value = ChiTiet.MaPhieuSuaChua;
command.Parameters["@_NoiDung"].Value = ChiTiet.NoiDung;
command.Parameters["@_MaVatTuPhuTung"].Value = ChiTiet.MaVatTuPhuTung;
command.Parameters["@_SoLuongSuaChua"].Value = ChiTiet.SoLuongSuaChua;
command.Parameters["@_TienCong"].Value = ChiTiet.TienCong;
command.Parameters["@_ThanhTien"].Value = ChiTiet.ThanhTien;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
//public static void fillCBO(string column, string table, ComboBox receiver)
//{
// MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
// MySqlCommand cmd = new MySqlCommand("select * from " + table, connection);
// MySqlDataReader reader;
// try
// {
// connection.Open();
// reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// string getter = reader.GetString(column);
// receiver.Items.Add(getter);
// }
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message);
// }
//}
#endregion
#region Insert, Update, Remove
public static void InsertCar(string BienSo, int MaKhachSuaXe, string MaHieuXe, int TienNo)
{
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("InsertCar", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_BienSo", MySqlDbType.VarChar, 10);
cmd.Parameters.Add("@_MaKhachSuaXe", MySqlDbType.Decimal);
cmd.Parameters.Add("@_MaHieuXe", MySqlDbType.VarChar, 20);
cmd.Parameters.Add("@_TienNo", MySqlDbType.Decimal);
cmd.Parameters["@_BienSo"].Value = BienSo;
cmd.Parameters["@_MaKhachSuaXe"].Value = MaKhachSuaXe;
cmd.Parameters["@_MaHieuXe"].Value = MaHieuXe;
cmd.Parameters["@_TienNo"].Value = TienNo;
Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close();
}
public static DataTable SearchSpareParts(VatTuPhuTungDTO vatTu)
{
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("SearchSpareParts", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_MaVatTuPhuTung", MySqlDbType.VarChar, 10);
cmd.Parameters.Add("@_TenVatTuPhuTung", MySqlDbType.VarChar, 100);
cmd.Parameters.Add("@_DonGia", MySqlDbType.Decimal);
cmd.Parameters.Add("@_SoLuongVatTu", MySqlDbType.Decimal);
cmd.Parameters["@_MaVatTuPhuTung"].Value = vatTu.MaVatTuPhuTung;
cmd.Parameters["@_TenVatTuPhuTung"].Value = vatTu.TenVatTuPhuTung;
cmd.Parameters["@_DonGia"].Value = vatTu.DonGia;
cmd.Parameters["@_SoLuongVatTu"].Value = vatTu.SoLuongVatTu;
Conn.Open();
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
Conn.Close();
return(dt);
}
//Tìm khách hàng theo nhiều điều kiện
public static DataTable SearchCustomer(KhachSuaXeDTO kh, string SoTienNoCompareType)
{
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("FindCustomers", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_MaKhachSuaXe", MySqlDbType.VarChar, 10);
cmd.Parameters.Add("@_TenChuXe", MySqlDbType.VarChar, 100);
cmd.Parameters.Add("@_DienThoai", MySqlDbType.VarChar, 20);
cmd.Parameters.Add("@_DiaChi", MySqlDbType.VarChar, 100);
cmd.Parameters.Add("@_Email", MySqlDbType.VarChar, 40);
cmd.Parameters.Add("@_TienNo", MySqlDbType.Decimal);
cmd.Parameters.Add("@_SoTienNoCompareType", MySqlDbType.VarChar, 2);
cmd.Parameters["@_MaKhachSuaXe"].Value = kh.MaKhachSuaXe;
cmd.Parameters["@_TenChuXe"].Value = kh.TenChuXe;
cmd.Parameters["@_DienThoai"].Value = kh.DienThoai;
cmd.Parameters["@_DiaChi"].Value = kh.DiaChi;
cmd.Parameters["@_Email"].Value = kh.Email;
cmd.Parameters["@_TienNo"].Value = kh.TienNo;
cmd.Parameters["@_SoTienNoCompareType"].Value = SoTienNoCompareType;
Conn.Open();
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
Conn.Close();
return(dt);
}
public static Boolean GetParameters(string exception = "")
{
try
{
//query
string query = string.Format("Select * from THAMSO");
//excute reader
MySqlDataReader reader = DatabaseConnectionDAO.ExcuteQuery(query, exception);
//if there is no exception
if (exception == "")
{
//get Parameters
while (reader.Read())
{
ThamSoDTO.SoTienNoToiDa = decimal.Parse(reader.GetString("SoTienNoToiDa"));
ThamSoDTO.SoXeSuaChuaToiDa = decimal.Parse(reader.GetString("SoXeSuaChuaToiDa"));
ThamSoDTO.SuDungQuyDinh4 = bool.Parse(reader.GetString("SuDungQuyDinh4"));
}
return(true);
}
}
catch (Exception ex)
{
exception = ex.Message;
}
return(false);
}
public static MySqlDataReader ExcuteProcedure(string procedureName, ref string exception, MySqlParameter[] values)
{
//we have to close previous reader first to be able to excute new reader
if (!(_reader == null))
{
_reader.Close();
}
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
Conn.Open();
//Excute reader
MySqlCommand command = new MySqlCommand(procedureName, Conn);
try
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(values);
_reader = command.ExecuteReader();
}
catch (MySqlException ex)
{
exception = ex.Message;
}
command.Dispose();
return(_reader);
}
public static bool RemoveCustomer(int _MaKhachSuaXe, string exception = "")
{
try
{
//ExcuteQuery
DatabaseConnectionDAO.ExcuteProcedure("RemoveCustomer", ref exception, new MySqlParameter[] { new MySqlParameter("@_MaKhachSuaXe", _MaKhachSuaXe) });
//if there is no exception
if (exception == "")
{
return(true);
}
}
catch (Exception ex)
{
exception = ex.Message;
}
return(false);
//MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
//MySqlCommand cmd = new MySqlCommand("RemoveCustomer", Conn);
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("@_MaKhachSuaXe", MySqlDbType.VarChar, 10);
//cmd.Parameters["@_MaKhachSuaXe"].Value = _MaKhachSuaXe;
//Conn.Open();
//cmd.ExecuteNonQuery();
//Conn.Close();
}
//public static void fillCBO(string column, string table, ComboBox receiver)
//{
// MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
// MySqlCommand cmd = new MySqlCommand("select * from " + table, connection);
// MySqlDataReader reader;
// try
// {
// connection.Open();
// reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// string getter = reader.GetString(column);
// receiver.Items.Add(getter);
// }
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message);
// }
//}
public static void updateSoLuongTon()
{
MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("updateSoLuongTonVT", connection);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
public static void RemoveSalary(string _MaTienCong)
{
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("RemoveSalary", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_MaTienCong", MySqlDbType.VarChar, 10);
cmd.Parameters["@_MaTienCong"].Value = _MaTienCong;
Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close();
}
public static void XoaHieuXe(string _MaHieuXe)
{
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("RemoveHIEUXE", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_MaHieuXe", MySqlDbType.VarChar, 10);
cmd.Parameters["@_MaHieuXe"].Value = _MaHieuXe;
Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close();
}
public static void deleteQuanLyNhapVatTu(string _MaVatTu)
{
MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand command = new MySqlCommand("deleteQUANLYNHAPVATTU", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@_MaVatTu", MySqlDbType.VarChar, 10);
command.Parameters["@_MaVatTu"].Value = _MaVatTu;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
public static void fillCBO(string column, string table, ComboBox receiver)
{
MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("select " + column + " from " + table, connection);
connection.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet dataset = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(dataset, column);
receiver.DataSource = dataset.Tables[0];
receiver.DisplayMember = column;
receiver.ValueMember = column;
}
/// <summary>
/// Dung de lay du lieu tu DataBase
/// </summary>
/// <returns>Tra lai gia tri la mot Table</returns>
public static DataTable getData()
{
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("LoadAllCar", Conn);
Conn.Open();
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
Conn.Close();
return(dt);
}
//Tìm khách hàng theo mã khách hàng
public static void FindCustomerByID(KhachSuaXeDTO kh)
{
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("FindCustomer", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_MaKhachSuaXe", MySqlDbType.VarChar, 10);
cmd.Parameters["@_MaKhachSuaXe"].Value = kh.MaKhachSuaXe;
Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close();
}
//Tìm xe theo bien so
public static void FindCarByID(XeDTO xe)
{
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("FindCar", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_BienSo", MySqlDbType.VarChar, 10);
cmd.Parameters["@_BienSo"].Value = xe.BienSo;
Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close();
}
public static void decreaseSoLuongVatTu(string MaVatTuPhuTung, int SoLuong)
{
MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("decreaseSpareParts", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_MaVatTuPhuTung", MySqlDbType.VarChar, 10);
cmd.Parameters.Add("@_SoLuong", MySqlDbType.Int16);
cmd.Parameters["@_MaVatTuPhuTung"].Value = MaVatTuPhuTung;
cmd.Parameters["@_SoLuong"].Value = SoLuong;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
public static DataTable selectTongDoanhThu(DateTime _Thang0)
{
DataTable data = new DataTable();
using (MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase())
{
MySqlCommand cmd = new MySqlCommand("selectTongDoanhThu", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_Thang0", MySqlDbType.Date);
cmd.Parameters["@_Thang0"].Value = _Thang0;
connection.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(data);
}
return(data);
}
public static void initiateGrid(DataGridView dgv)
{
MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand command = new MySqlCommand("SelectAllCHITIETPHIEUSUACHUA", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
DataTable dt = new DataTable();
MySqlDataReader dr = command.ExecuteReader();
dt.Load(dr);
dgv.DataSource = dt;
command.ExecuteNonQuery();
connection.Close();
}
public static void payTienNo(string BienSo, decimal SoTien)
{
MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("payTienNo", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_BienSo", MySqlDbType.VarChar, 10);
cmd.Parameters.Add("@_SoTien", MySqlDbType.Decimal);
cmd.Parameters["@_BienSo"].Value = BienSo;
cmd.Parameters["@_SoTien"].Value = SoTien;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
//Insert
public static void insertTiepNhanXeSua(TiepNhanXeSuaDTO TiepNhan)
{
MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand command = new MySqlCommand("InsertTIEPNHANXESUA", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@_BienSo", MySqlDbType.VarChar, 10);
command.Parameters.Add("@_NgayTiepNhan", MySqlDbType.Date);
command.Parameters["@_BienSo"].Value = TiepNhan.BienSo;
command.Parameters["@_NgayTiepNhan"].Value = TiepNhan.NgayTiepNhan;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
public static DataTable selectAllTiepNhanXeSua()
{
DataTable data = new DataTable();
using (MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase())
{
MySqlCommand command = new MySqlCommand("SelectAllTIEPNHANXESUA", connection);
connection.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(data);
connection.Close();
}
return(data);
}
public static DataTable selectAllPhieuSuaChua()
{
DataTable data = new DataTable();
using (MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase())
{
MySqlCommand command = new MySqlCommand("SelectAllPHIEUSUACHUA", connection);
connection.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(data);
connection.Close();
}
return(data);
}
public static bool CheckPrimary(string _MaVatTuPhuTung)
{
bool check = false;
string cPrimaryKey = "Select *From VATTUPHUTUNG Where MaVatTuPhuTung = '" + _MaVatTuPhuTung + "' ";
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand(cPrimaryKey, Conn);
cmd.CommandType = CommandType.Text;
Conn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
check = true;
}
return(check);
}
public static bool KiemTraBienSo(string _textBox)
{
bool check = false;
string cPrimaryKey = "Select *From XE Where BIENSO = '" + _textBox + "' ";
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand(cPrimaryKey, Conn);
cmd.CommandType = CommandType.Text;
Conn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
check = true;
}
return(check);
}
public static void ThemHieuXe(HieuXeDTO hx)
{
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("InsertHIEUXE", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_MaHieuXe", MySqlDbType.VarChar, 10);
cmd.Parameters.Add("@_TenHieuXe", MySqlDbType.VarChar, 100);
cmd.Parameters["@_MaHieuXe"].Value = hx.MaHieuXe;
cmd.Parameters["@_TenHieuXe"].Value = hx.TenHieuXe;
Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close();
}
public static void insertBaoCaoTon(BaoCaoTonDTO BaoCao)
{
MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand command = new MySqlCommand("insertBaoCaoTon", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@_MaBaoCao", MySqlDbType.VarChar, 10);
command.Parameters.Add("@_Thang", MySqlDbType.Int32);
command.Parameters.Add("@_Nam", MySqlDbType.Int32);
command.Parameters["@_MaBaoCao"].Value = BaoCao.MaBaoCaoTon;
command.Parameters["@_Thang"].Value = BaoCao.ThangBaoCaoTon;
command.Parameters["@_Nam"].Value = BaoCao.NamBaoCaoTon;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
public static void insertChiTietTon(string MaBaoCao, int _Month, int _Year)
{
MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand command = new MySqlCommand("insertCTBaoCaoTon", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@_MaBaoCao", MySqlDbType.VarChar, 10);
command.Parameters.Add("@_Month", MySqlDbType.Int16);
command.Parameters.Add("@_Year", MySqlDbType.Int16);
command.Parameters["@_MaBaoCao"].Value = MaBaoCao;
command.Parameters["@_Month"].Value = _Month;
command.Parameters["@_Year"].Value = _Year;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
static void Main(string[] args)
{
Console.WriteLine("Getting Connection ...");
MySqlConnection conn = DatabaseConnectionDAO.connectionDatabase();
try
{
Console.WriteLine("Openning Connection ...");
conn.Open();
Console.WriteLine("Connection successful!");
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message);
}
Console.Read();
}
public static void insertQuanLyNhapVatTu(QuanLyNhapVatTuDTO QuanLy)
{
MySqlConnection connection = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand command = new MySqlCommand("InsertQUANLYNHAPVATTU", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@_MaVatTu", MySqlDbType.VarChar, 10);
command.Parameters.Add("@_SoLuongNhap", MySqlDbType.Int32);
command.Parameters.Add("@_NgayNhap", MySqlDbType.Date);
command.Parameters["@_MaVatTu"].Value = QuanLy.MaVatTuPhuTung;
command.Parameters["@_SoLuongNhap"].Value = QuanLy.SoLuongNhap;
command.Parameters["@_NgayNhap"].Value = QuanLy.NgayNhap;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}