public Mensalista getMensalistabyId_cliente(int id_cliente)
{
Mensalista returnMensalista = null;
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "select * from Mensalistas m inner join Clientes c on m.Id_cliente = c.Id_Cliente where m.Id_cliente = " + id_cliente + " ";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
if (reader.Read())
{
while (reader.Read())
{
returnMensalista.id_mensalista = Int32.Parse(string.Format("{0}", reader[0]));
returnMensalista.id_cliente = Int32.Parse(string.Format("{0}", reader[1]));
returnMensalista.nome_cliente = string.Format("{0}", reader[4]);
returnMensalista.data_termino = string.Format("{0}", reader[2]);
}
}
return returnMensalista;
}
public List<Cliente> buscar(String nome)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "SELECT * FROM Clientes where Nome LIKE '%" + nome + "%'";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
List<Cliente> listaClientes = new List<Cliente>();
while (reader.Read())
{
Cliente cliente = new Cliente();
cliente.id_cliente = Int32.Parse(string.Format("{0}", reader[0]));
cliente.nome = string.Format("{0}", reader[1]);
cliente.cpf = string.Format("{0}", reader[2]);
cliente.saldo = double.Parse(string.Format("{0}", reader[3]));
listaClientes.Add(cliente);
}
con.closeConnection();
return listaClientes;
}
public List<Mensalista> buscar(String nomeCliente)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "SELECT * FROM Mensalistas m INNER JOIN Clientes c ON m.Id_cliente = c.Id_Cliente WHERE c.Nome like '%"+ nomeCliente +"%'";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
List<Mensalista> listaMensalistas = new List<Mensalista>();
while (reader.Read())
{
Mensalista mensalista = new Mensalista();
mensalista.id_mensalista = Int32.Parse(string.Format("{0}", reader[0]));
mensalista.id_cliente = Int32.Parse(string.Format("{0}", reader[1]));
Cliente cliente = new Cliente();
cliente.nome = string.Format("{0}", reader[4]);
mensalista.nome_cliente = cliente.nome;
mensalista.data_termino = string.Format("{0}", reader[2]);
listaMensalistas.Add(mensalista);
}
con.closeConnection();
return listaMensalistas;
}
public List<Carro> buscar(String nome)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "SELECT * FROM Carro where modelo LIKE '%" + nome + "%' OR marca LIKE '%" + nome + "%'";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
List<Carro> listaCarros = new List<Carro>();
while (reader.Read())
{
Carro carro = new Carro();
carro.id_carro = Int32.Parse(string.Format("{0}", reader[0]));
carro.modelo = string.Format("{0}", reader[1]);
carro.marca = string.Format("{0}", reader[2]);
carro.cor = string.Format("{0}", reader[3]);
listaCarros.Add(carro);
}
con.closeConnection();
return listaCarros;
}
public Boolean editar(Ticket ticket, Carro carro, Cliente cliente, Servicos servico)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
String sql = "UPDATE Tickets SET Id_cliente = " + cliente.id_cliente + ", id_Carro = " + carro.id_carro + ", Id_Servico = " + servico.id_servico + ", DataEntrada = '" + ticket.data_entrada + "', DataSaida = '" + ticket.data_saida + "', ValorTotal = " + ticket.valorTotal + ", Placa = '" + ticket.placa + "', UF = '" + ticket.uf + "', Cidade = '" + ticket.cidade + "'";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
command.ExecuteNonQuery();
con.closeConnection();
return true;
}
public Boolean editar(Mensalista mensalista)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
String sql = "UPDATE Mensalistas set Data_termino ='" + mensalista.data_termino +"' where Id_Mensalista = "+mensalista.id_mensalista;
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
command.ExecuteNonQuery();
con.closeConnection();
return true;
}
public Boolean editar(Carro carro)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
String sql = "UPDATE Carro set marca='" + carro.marca + "', modelo ='" + carro.modelo + "', cor = '" + carro.cor + "' where ID_carro = " + carro.id_carro;
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
command.ExecuteNonQuery();
con.closeConnection();
return true;
}
public Boolean debitarSaldo( Cliente cliente, Double valor)
{
Double saldo = (cliente.saldo - valor);
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
String sql = "UPDATE Clientes SET Saldo = " + saldo + " where Id_cliente = " + cliente.id_cliente;
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
command.ExecuteNonQuery();
con.closeConnection();
return true;
}
public Boolean deletar(Ticket ticket)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "DELETE Tickets WHERE ID_ticket = " + ticket.id_ticket;
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
con.closeConnection();
return true;
}
public Boolean deletar(Mensalista mensalista)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "DELETE Mensalistas WHERE Id_Mensalista = " + mensalista.id_mensalista;
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
con.closeConnection();
return true;
}
public Boolean deletar(Carro carro)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "DELETE Carro WHERE ID_carro = " + carro.id_carro;
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
con.closeConnection();
return true;
}
public List<Ticket> buscar(String placa)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "SELECT * FROM Tickets WHERE placa LIKE '%" + placa + "%' ORDER BY DataEntrada";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
List<Ticket> listaTickets = new List<Ticket>();
while (reader.Read())
{
Ticket ticket = new Ticket();
//Ticket
ticket.id_ticket = int.Parse(string.Format("{0}", reader[0]));
//Cliente
ticket.id_cliente = int.Parse(string.Format("{0}", reader[1]));
//Carro
ticket.id_carro = Convert.ToInt32(string.Format("{0}", reader["id_Carro"]));
//Servico
ticket.id_servico = int.Parse(string.Format("{0}", reader["Id_Servico"]));
//Ticket
ticket.data_entrada = Convert.ToDateTime(reader["DataEntrada"]).ToString("dd/MM/yyyy HH:mm");
ticket.data_saida = Convert.ToDateTime(reader["DataSaida"]).ToString("dd/MM/yyyy HH:mm");
ticket.valorTotal = Convert.ToDouble(string.Format("{0}", reader[6]));
//Placa carro
ticket.placa = string.Format("{0}", reader[7]);
ticket.uf = string.Format("{0}", reader[8]);
ticket.cidade = string.Format("{0}", reader[9]);
listaTickets.Add(ticket);
}
con.closeConnection();
return listaTickets;
}
//delete
public void Delete(Entities.Thue dvt1)
{
dvt = new Constants.ThueH();
Entities.Thue loaihanghoa = new Entities.Thue();
Sql = new Constants.Sql();
string sql = Sql.DeleteThue;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(dvt.HanhDong, SqlDbType.NVarChar, 20).Value = dvt1.HanhDong;
cmd.Parameters.Add(dvt.ThueID, SqlDbType.Int).Value = dvt1.ThueID;
cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
}
//delete
public void Delete(Entities.LoaiHangHoa lhh1)
{
lhh = new Constants.LoaiHangHoa();
Entities.LoaiHangHoa loaihanghoa = new Entities.LoaiHangHoa();
Sql = new Constants.Sql();
string sql = Sql.DeleteLoaiHangHoa;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(lhh.HanhDong, SqlDbType.NVarChar, 20).Value = lhh1.HanhDong;
cmd.Parameters.Add(lhh.LoaiHangID, SqlDbType.Int).Value = lhh1.LoaiHangID;
cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
}
/// <summary>
/// Insert Update Bảng
/// </summary>
public bool Insert(Entities.SoDuCongNo pxh)
{
try
{
bool kt = false;
pt = new Constants.CongNo();
Sql = new Constants.Sql();
string sql = Sql.InsertSoDuCongNo;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(pt.MaSoDuCongNo, SqlDbType.VarChar, 20).Value = pxh.MaSoDuCongNo;
cmd.Parameters.Add(pt.MaDoiTuong, SqlDbType.VarChar, 20).Value = pxh.MaDoiTuong;
cmd.Parameters.Add(pt.TenDoiTuong, SqlDbType.NVarChar, 200).Value = pxh.TenDoiTuong;
cmd.Parameters.Add(pt.DiaChi, SqlDbType.NVarChar, 200).Value = pxh.DiaChi;
cmd.Parameters.Add(pt.SoDuDauKy, SqlDbType.Float).Value = pxh.SoDuDauKy;
cmd.Parameters.Add(pt.NgayKetChuyen, SqlDbType.DateTime).Value = pxh.NgayKetChuyen;
cmd.Parameters.Add(pt.SoDuCuoiKy, SqlDbType.Float).Value = pxh.SoDuCuoiKy;
cmd.Parameters.Add(pt.LoaiDoiTuong, SqlDbType.Bit).Value = pxh.LoaiDoiTuong;
cmd.Parameters.Add(pt.TrangThai, SqlDbType.Bit).Value = pxh.TrangThai;
int i = cmd.ExecuteNonQuery();
if (i == 1)
{
kt = true;
}
else
{
kt = false;
}
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
return(kt);
}
catch
{
return(false);
}
}
public int InsertUpdate(Entities.NhanVien nv1)
{
try
{
nv = new Constants.NhanVien();
Sql = new Constants.Sql();
string sql = Sql.InsertUpdateNhanVien;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(nv.HanhDong, SqlDbType.NVarChar, 20).Value = nv1.HanhDong;
cmd.Parameters.Add(nv.NhanVienID, SqlDbType.Int).Value = nv1.NhanVienID;
cmd.Parameters.Add(nv.MaNhanVien, SqlDbType.NVarChar, 20).Value = nv1.MaNhanVien;
cmd.Parameters.Add(nv.TenNhanVien, SqlDbType.NVarChar, 200).Value = nv1.TenNhanVien;
cmd.Parameters.Add(nv.MaPhongBan, SqlDbType.VarChar, 20).Value = nv1.MaPhongBan;
cmd.Parameters.Add(nv.DCThuongTru, SqlDbType.NVarChar, 200).Value = nv1.DCThuongTru;
cmd.Parameters.Add(nv.DCTamTru, SqlDbType.NVarChar, 200).Value = nv1.DCTamTru;
cmd.Parameters.Add(nv.DienThoaiCD, SqlDbType.NVarChar, 20).Value = nv1.DienThoaiCD;
cmd.Parameters.Add(nv.DienThoaiDD, SqlDbType.NVarChar, 20).Value = nv1.DienThoaiDD;
cmd.Parameters.Add(nv.Email, SqlDbType.NVarChar, 20).Value = nv1.Email;
cmd.Parameters.Add(nv.CMND, SqlDbType.NVarChar, 200).Value = nv1.CMND;
cmd.Parameters.Add(nv.NgayCap, SqlDbType.DateTime).Value = nv1.NgayCap;
cmd.Parameters.Add(nv.NoiCap, SqlDbType.NVarChar, 200).Value = nv1.NoiCap;
cmd.Parameters.Add(nv.NgaySinh, SqlDbType.DateTime).Value = nv1.NgaySinh;
cmd.Parameters.Add(nv.GhiChu, SqlDbType.NVarChar, 100).Value = nv1.GhiChu;
cmd.Parameters.Add(nv.Deleted, SqlDbType.Bit).Value = nv1.Deleted;
int i = cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
return(i);
}
catch (Exception ex)
{
string s = ex.Message;
return(0);
}
}
//insert update
public bool InsertUpdate(Entities.NhomHang nh)
{
bool kt = false;
try
{
nhh = new Constants.NhomHangHoa();
Sql = new Constants.Sql();
string sql = Sql.InsertUpdateNhomHangHoa;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(nhh.HanhDong, SqlDbType.NVarChar, 20).Value = nh.HanhDong;
cmd.Parameters.Add(nhh.NhomHangID, SqlDbType.Int).Value = nh.NhomHangID;
cmd.Parameters.Add(nhh.MaNhomHang, SqlDbType.VarChar, 20).Value = nh.MaNhomHang;
cmd.Parameters.Add(nhh.MaLoaiHang, SqlDbType.VarChar, 20).Value = nh.MaLoaiHang;
cmd.Parameters.Add(nhh.TenNhomHang, SqlDbType.NVarChar, 200).Value = nh.TenNhomHang;
cmd.Parameters.Add(nhh.GhiChu, SqlDbType.NVarChar, 100).Value = nh.GhiChu;
cmd.Parameters.Add(nhh.Deleted, SqlDbType.Bit).Value = nh.Deleted;
int i = cmd.ExecuteNonQuery();
if (i == 1)
{
kt = true;
}
else
{
kt = false;
}
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
return(kt);
}
catch
{
return(kt);
}
}
/// <summary>
/// vuong hung =================lay bang DonDatHang================
/// </summary>
/// <returns></returns>
public Entities.ThongTinNhanVien[] sp_LayBang_ThongTinNhanVien()
{
Entities.ThongTinNhanVien[] arrC = null;
try
{
dh = new Constants.ThongTinNhanVien();
Sql = new Constants.Sql();
string sql = Sql.sp_LayBang_NhanViens;
conn = new Connection();
cn = conn.openConnection();
cmd = new SqlCommand(sql, cn);
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
arr = new ArrayList();
while (dr.Read())
{
nhanvien = new Entities.ThongTinNhanVien();
nhanvien.Manhanvien = dr[dh.MaNhanVien].ToString();
nhanvien.Tennhanvien = dr[dh.TenNhanVien].ToString();
arr.Add(nhanvien);
}
int n = arr.Count;
if (n == 0)
{
return(null);
}
arrC = new Entities.ThongTinNhanVien[n];
for (int i = 0; i < n; i++)
{
arrC[i] = (Entities.ThongTinNhanVien)arr[i];
}
}
catch (Exception ex)
{ string s = ex.Message.ToString(); }
finally
{
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
}
return(arrC);
}
/// <summary>
/// lay ban ghi kiem tra khi update
/// </summary>
/// <param name="truyen"></param>
/// <returns></returns>
public Entities.TraLaiNCC CheckTraLaiNhaCungCap(Entities.TruyenGiaTri truyen)
{
Entities.TraLaiNCC giatri = new Entities.TraLaiNCC();
try
{
string sql = "exec sp_LayBang_TheoTenBang @table,@values";
conn = new Connection();
cn = conn.openConnection();
cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add("table", SqlDbType.NVarChar, 100).Value = truyen.Giatritruyen;
cmd.Parameters.Add("values", SqlDbType.NVarChar, 50).Value = truyen.Giatrithuhai;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
giatri.TraLaiNCCID = Convert.ToInt32(dr[0].ToString());
giatri.MaHDTraLaiNCC = dr[1].ToString();
giatri.Ngaytra = Convert.ToDateTime(dr[2].ToString());
giatri.MaNCC = dr[3].ToString();
giatri.NoHienThoi = dr[4].ToString();
giatri.NguoiNhanHang = dr[5].ToString();
giatri.HinhThucThanhToan = dr[6].ToString();
giatri.MaHoaDonNhap = dr[7].ToString();
giatri.MaKho = dr[8].ToString();
giatri.MaTienTe = dr[9].ToString();
giatri.TienBoiThuong = dr[10].ToString();
giatri.ThanhToanNgay = dr[11].ToString();
giatri.ThueGTGT = dr[12].ToString();
giatri.GhiChu = dr[13].ToString();
giatri.Deleted = Convert.ToBoolean(dr[14].ToString());
}
}
catch (Exception ex)
{ string s = ex.Message.ToString(); giatri = null; }
finally
{
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
}
return(giatri);
}
/// <summary>
/// Insert Update Bảng
/// </summary>
public bool InsertUpdate(Entities.ChiTietXuatHuy ctxh1)
{
try
{
bool kt = false;
ctxh = new Constants.ChiTietXuatHuy();
Sql = new Constants.Sql();
string sql = Sql.InsertUpdateChiTietXuatHuyMang;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(ctxh.HanhDong, SqlDbType.VarChar, 20).Value = ctxh1.HanhDong;
cmd.Parameters.Add(ctxh.MaPhieuXuatHuy, SqlDbType.VarChar, 20).Value = ctxh1.MaPhieuXuatHuy;
cmd.Parameters.Add(ctxh.MaHangHoa, SqlDbType.VarChar, 50).Value = ctxh1.MaHangHoa;
cmd.Parameters.Add(ctxh.SoLuong, SqlDbType.Int, 20).Value = ctxh1.SoLuong;
cmd.Parameters.Add(ctxh.GhiChu, SqlDbType.NVarChar, 20).Value = ctxh1.GhiChu;
cmd.Parameters.Add(ctxh.Deleted, SqlDbType.Bit).Value = ctxh1.Deleted;
int i = cmd.ExecuteNonQuery();
if (i == 1)
{
kt = true;
}
else
{
kt = false;
}
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
return(kt);
}
catch (Exception ex)
{
string s = ex.Message;
return(false);
}
}
/// <summary>
/// Insert Update Bảng
/// </summary>
public bool Insert(Entities.SoDuKho pxh)
{
try
{
bool kt = false;
sdk = new Constants.SoDuKho();
Sql = new Constants.Sql();
string sql = Sql.InsertUpdateSoDuKho;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(sdk.HanhDong, SqlDbType.VarChar, 20).Value = pxh.HanhDong;
cmd.Parameters.Add(sdk.SoDuKhoID, SqlDbType.Int).Value = pxh.SoDuKhoID;
cmd.Parameters.Add(sdk.MaSoDuKho, SqlDbType.VarChar, 20).Value = pxh.MaSoDuKho;
cmd.Parameters.Add(sdk.MaKho, SqlDbType.VarChar, 20).Value = pxh.MaKho;
cmd.Parameters.Add(sdk.MaHangHoa, SqlDbType.VarChar, 50).Value = pxh.MaHangHoa;
cmd.Parameters.Add(sdk.SoDuDauKy, SqlDbType.Int).Value = pxh.SoDuDauKy;
cmd.Parameters.Add(sdk.NgayKetChuyen, SqlDbType.DateTime).Value = pxh.NgayKetChuyen;
cmd.Parameters.Add(sdk.SoDuCuoiKy, SqlDbType.Int).Value = pxh.SoDuCuoiKy;
cmd.Parameters.Add(sdk.TrangThai, SqlDbType.Bit).Value = pxh.TrangThai;
int i = cmd.ExecuteNonQuery();
if (i == 1)
{
kt = true;
}
else
{
kt = false;
}
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
return(kt);
}
catch
{
return(false);
}
}
/// <summary>
/// vuong hung =================xu ly don dat hang================
/// </summary>
/// <returns></returns>
public int sp_XuLy_KhachHangTraLai(Entities.KhachHangTraLai kkt)
{
int Trave = 0;
try
{
khtl = new Constants.KhachHangTraLai();
Sql = new Constants.Sql();
string sql = Sql.sp_XuLy_KhachHangTraLai;
conn = new Connection();
cn = conn.openConnection();
cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(khtl.HanhDong, SqlDbType.NVarChar, 20).Value = kkt.Hanhdong;
cmd.Parameters.Add("KhachHangTraLaiID", SqlDbType.Int).Value = kkt.KhachHangTraLaiID;
cmd.Parameters.Add(khtl.MaKhachHangTraLai, SqlDbType.NVarChar, 20).Value = kkt.MaKhachHangTraLai;
cmd.Parameters.Add(khtl.NgayNhap, SqlDbType.DateTime).Value = kkt.NgayNhap;
cmd.Parameters.Add(khtl.MaKhachHang, SqlDbType.NVarChar, 20).Value = kkt.MaKhachHang;
cmd.Parameters.Add(khtl.NoHienThoi, SqlDbType.Float).Value = kkt.NoHienThoi;
cmd.Parameters.Add(khtl.NguoiTra, SqlDbType.NVarChar, 200).Value = kkt.NguoiTra;
cmd.Parameters.Add(khtl.HinhThucThanhToan, SqlDbType.NVarChar, 200).Value = kkt.HinhThucThanhToan;
cmd.Parameters.Add(khtl.HanThanhToan, SqlDbType.DateTime).Value = kkt.HanThanhToan;
cmd.Parameters.Add(khtl.MaHoaDonMuaHang, SqlDbType.NVarChar, 20).Value = kkt.MaHoaDonMuaHang;
cmd.Parameters.Add(khtl.MaKho, SqlDbType.NVarChar, 20).Value = kkt.MaKho;
cmd.Parameters.Add(khtl.MaTienTe, SqlDbType.NVarChar, 20).Value = kkt.MaTienTe;
cmd.Parameters.Add(khtl.TienBoiThuong, SqlDbType.Float).Value = kkt.TienBoiThuong;
cmd.Parameters.Add(khtl.ThanhToanNgay, SqlDbType.Float).Value = kkt.ThanhToanNgay;
cmd.Parameters.Add(khtl.ThueGTGT, SqlDbType.Float).Value = kkt.ThueGTGT;
cmd.Parameters.Add(khtl.GhiChu, SqlDbType.NVarChar, 100).Value = kkt.GhiChu;
cmd.Parameters.Add(khtl.Deleted, SqlDbType.Bit).Value = kkt.Deleted;
Trave = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ string s = ex.Message.ToString(); }
finally
{
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
}
return(Trave);
}
public bool updateTaiKhoan(Entities.TaiKhoan tk1)
{
try
{
bool kt = false;
tk = new Constants.TaiKhoan();
Sql = new Constants.Sql();
string sql = Sql.updateTaiKhoan;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(tk.TenDangNhap, SqlDbType.VarChar, 200).Value = tk1.TenDangNhap;
cmd.Parameters.Add(tk.MatKhauDangNhap, SqlDbType.VarChar, 200).Value = tk1.MatKhauDangNhap;
cmd.Parameters.Add(tk.KhoaTaiKhoan, SqlDbType.Bit).Value = tk1.KhoaTaiKhoan;
cmd.Parameters.Add(tk.NhanVienID, SqlDbType.VarChar, 20).Value = tk1.NhanVienID;
cmd.Parameters.Add(tk.Administrator, SqlDbType.VarChar, 20).Value = tk1.Administrator;
cmd.Parameters.Add(tk.TenNhomQuyen, SqlDbType.VarChar, 50).Value = tk1.TenNhomQuyen;
int i = cmd.ExecuteNonQuery();
if (i >= 1)
{
kt = true;
}
else
{
kt = false;
}
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
return(kt);
}
catch (Exception ex)
{
string s = ex.Message;
return(false);
}
}
/// <summary>
/// Select
/// </summary>
/// <returns></returns>
public Entities.KhuyenMaiSoLuong[] Select(Entities.KhuyenMaiSoLuong item)
{
Entities.KhuyenMaiSoLuong[] list = null;
try
{
string sql = "exec sp_SelectKMSLTheoMa @MaHangHoa";
conn = new Connection();
cn = conn.openConnection();
cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add("MaHangHoa", SqlDbType.VarChar, 50).Value = item.MaHangHoa;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
List <Entities.KhuyenMaiSoLuong> arr = new List <Entities.KhuyenMaiSoLuong>();
while (dr.Read())
{
Entities.KhuyenMaiSoLuong row = new Entities.KhuyenMaiSoLuong();
row.Id = Convert.ToInt32(dr["Id"].ToString());
row.MaHangHoa = dr["MaHangHoa"].ToString();
row.TenHangHoa = dr["TenHangHoa"].ToString();
row.SoLuong = double.Parse(dr["SoLuong"].ToString());
row.NgayBatDau = DateTime.Parse(dr["NgayBatDau"].ToString());
row.NgayKetThuc = DateTime.Parse(dr["NgayKetThuc"].ToString());
row.GiaBanBuon = double.Parse(dr["GiaBanBuon"].ToString());
row.GiaBanLe = double.Parse(dr["GiaBanLe"].ToString());
arr.Add(row);
}
list = arr.ToArray();
}
catch
{
list = null;
}
finally
{
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
}
return(list);
}
public void Save(string idpatient, string iddoctor, string diagnose)
{
sqlCon = con.openConnection();
using (sqlCon)
{
sqlCon.Open();
string sql = "exec pcdTreatment @idpatient,@iddoctor,@diagnose";
sqlCom = new SqlCommand(sql, sqlCon);
using (sqlCom)
{
sqlCom.Parameters.Add(new SqlParameter("@idpatient", idpatient));
sqlCom.Parameters.Add(new SqlParameter("@iddoctor", iddoctor));
sqlCom.Parameters.Add(new SqlParameter("@diagnose", diagnose));
sqlCom.ExecuteNonQuery();
}
sqlCom.Clone();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
c.openConnection();
String str;
str = ("insert into City(City_Name)values ('" + txtCityName.Text + "')");
int x = c.changeTable(str);
if (x > 0)
{
Label2.Visible = true;
Label2.Text = "City inserted successfully";
GridView1.DataBind();
Clear();
}
else
{
Label2.Visible = true;
Label2.Text = "City insert unsuccessful";
}
}
public bool specialistAvailable(string specialist)
{
SqlDataReader sqlDr;
SqlConnection sqlCon = con.openConnection();
using (sqlCon)
{
sqlCon.Open();
string query = "select count(Specialist) as Specialist from Doctor.Specialist where Specialist = '" + specialist + "'";
sqlCom = new SqlCommand(query, sqlCon);
sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
string specialistName = sqlDr["Specialist"].ToString();
if (specialistName == "0")
{
return(false);
}
}
}
sqlCon.Close();
return(true);
}
/// <summary>
/// lay so lupong mua theo hoa don ban hang
/// </summary>
/// <param name="giatri"></param>
/// <returns></returns>
public Entities.SoLuongTraLai[] sp_LaySoLuongDaMuaTheoHoaDonBanHang(Entities.TruyenGiaTri giatri)
{
Entities.SoLuongTraLai[] arrC = null;
try
{
string sql = "exec sp_LaySoLuongDaMuaTheoHoaDonBanHang @MaHDBanHang";
conn = new Connection();
cn = conn.openConnection();
cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add("MaHDBanHang", SqlDbType.NVarChar, 20).Value = giatri.Giatritruyen;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
arr = new ArrayList();
while (dr.Read())
{
Entities.SoLuongTraLai tao = new Entities.SoLuongTraLai();
tao.MaHangHoa = dr[0].ToString();
tao.SoLuong = int.Parse(dr[1].ToString());
arr.Add(tao);
}
int n = arr.Count;
if (n == 0)
{
arrC = null;
}
arrC = new Entities.SoLuongTraLai[n];
for (int i = 0; i < n; i++)
{
arrC[i] = (Entities.SoLuongTraLai)arr[i];
}
}
catch (Exception ex)
{ string s = ex.Message.ToString(); arrC = null; }
finally
{
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
}
return(arrC);
}
/// <summary>
/// vuong hung =================xu ly don dat hang================
/// </summary>
/// <returns></returns>
public int sp_XuLy_TraLaiNhaCungCap(Entities.TraLaiNCC ncc)
{
int Trave = 0;
try
{
tlncc = new Constants.TraLaiNhaCungCap();
Sql = new Constants.Sql();
string sql = Sql.sp_XuLy_TraLaiNhaCungCap;
conn = new Connection();
cn = conn.openConnection();
cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(tlncc.HanhDong, SqlDbType.NVarChar, 20).Value = ncc.Hanhdong;
cmd.Parameters.Add(tlncc.TraLaiNCCID, SqlDbType.Int).Value = ncc.TraLaiNCCID;
cmd.Parameters.Add(tlncc.MaHDTraLaiNCC, SqlDbType.NVarChar, 20).Value = ncc.MaHDTraLaiNCC;
cmd.Parameters.Add(tlncc.Ngaytra, SqlDbType.DateTime).Value = ncc.Ngaytra;
cmd.Parameters.Add(tlncc.MaNCC, SqlDbType.NVarChar, 20).Value = ncc.MaNCC;
cmd.Parameters.Add(tlncc.NoHienThoi, SqlDbType.Float).Value = ncc.NoHienThoi;
cmd.Parameters.Add(tlncc.NguoiNhanHang, SqlDbType.NVarChar, 200).Value = ncc.NguoiNhanHang;
cmd.Parameters.Add(tlncc.HinhThucThanhToan, SqlDbType.NVarChar, 200).Value = ncc.HinhThucThanhToan;
cmd.Parameters.Add(tlncc.MaHoaDonNhap, SqlDbType.NVarChar, 20).Value = ncc.MaHoaDonNhap;
cmd.Parameters.Add(tlncc.MaKho, SqlDbType.NVarChar, 20).Value = ncc.MaKho;
cmd.Parameters.Add(tlncc.MaTienTe, SqlDbType.NVarChar, 20).Value = ncc.MaTienTe;
cmd.Parameters.Add(tlncc.TienBoiThuong, SqlDbType.Float).Value = ncc.TienBoiThuong;
cmd.Parameters.Add(tlncc.ThanhToanNgay, SqlDbType.Float).Value = ncc.ThanhToanNgay;
cmd.Parameters.Add(tlncc.ThueGTGT, SqlDbType.Float).Value = ncc.ThueGTGT;
cmd.Parameters.Add(tlncc.GhiChu, SqlDbType.NVarChar, 100).Value = ncc.GhiChu;
cmd.Parameters.Add(tlncc.Deleted, SqlDbType.Bit).Value = ncc.Deleted;
Trave = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ string s = ex.Message.ToString(); }
finally
{
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
}
return(Trave);
}
public bool Insert(Entities.TheVip thevip)
{
bool trave = false;
try
{
string sql = new Common.Constants.Sql().insertTheVip;
conn = new Connection();
Constants.TheVip tv = new Constants.TheVip();
cn = conn.openConnection();
cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(tv.MaKhachHang, SqlDbType.VarChar, 50).Value = thevip.MaKhachHang.ToUpper();
cmd.Parameters.Add(tv.MaThe, SqlDbType.VarChar, 50).Value = thevip.MaThe.ToUpper();
cmd.Parameters.Add(tv.GiaTriThe, SqlDbType.Float).Value = thevip.GiaTriThe;
cmd.Parameters.Add(tv.GiaTriConLai, SqlDbType.Float).Value = thevip.GiaTriConLai;
cmd.Parameters.Add(tv.GhiChu, SqlDbType.VarChar, 100).Value = thevip.GhiChu;
cmd.Parameters.Add(tv.SoDiem, SqlDbType.VarChar, 100).Value = thevip.SoDiem;
int i = cmd.ExecuteNonQuery();
if (i == 1)
{
trave = true;
}
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
}
catch (Exception ex)
{
string s = ex.Message.ToString();
trave = false;
}
finally
{
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
}
return(trave);
}
public bool Restore(string duongdan)
{
try
{
try
{
SqlConnection.ClearAllPools();
string sql = "ALTER DATABASE SupermarketManagementDHT ";
sql += "SET SINGLE_USER WITH ROLLBACK IMMEDIATE ";
sql += " use master ";
sql += "restore DATABASE SupermarketManagementDHT from DISK='" + duongdan + "' with replace";
sql += " ALTER DATABASE SupermarketManagementDHT ";
sql += "SET MULTI_USER";
conn = new Connection();
cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = conn.openConnection();
cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
cmd.Connection.Close();
cmd.Dispose();
}
catch
{ return(false); }
finally
{
cmd.Connection.Dispose();
cmd.Connection.Close();
cmd.Dispose();
conn.closeConnection();
}
return(true);
}
catch
{
return(false);
}
}
private void doneBtn_Click(object sender, EventArgs e)
{
if (nameTxt.Text.Length < 1 || priceTxt.Text.Length < 1)
{
MessageBox.Show("All fields must be entered!", "Invalid Values", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Connection connection = new Connection();
connection.openConnection();
OleDbCommand serviceCommand = new OleDbCommand();
serviceCommand.Connection = connection.connection;
if (edit)
{
serviceCommand.CommandText = "UPDATE Services SET Service_Name = '" + nameTxt.Text + "', Price = " + priceTxt.Text + " WHERE ID = " + service.id;
serviceCommand.ExecuteNonQuery();
service.name = nameTxt.Text;
service.price = float.Parse(priceTxt.Text);
}
else
{
serviceCommand.CommandText = "INSERT INTO Services (Service_Name, Price) VALUES ('" + nameTxt.Text + "', " + priceTxt.Text + ")";
serviceCommand.ExecuteNonQuery();
serviceCommand.CommandText = "Select @@Identity";
string serviceID = serviceCommand.ExecuteScalar().ToString();
service = new CService(serviceID, nameTxt.Text, float.Parse(priceTxt.Text));
}
connection.closeConnection();
DialogResult = System.Windows.Forms.DialogResult.OK;
Close();
}
/// <summary>
/// Insert Update Bảng
/// </summary>
public bool UpdateTrangThai(Entities.SoDuSoQuy pxh)
{
try
{
if (pxh.SoDuSoQuyID == 0)
{
Entities.SoDuSoQuy sdsq = new Entities.SoDuSoQuy("", pxh.MaSoDuSoQuy, "0", pxh.NgayKetChuyen, pxh.SoDuCuoiKy, true);
return(Insert(sdsq));
}
bool kt = false;
pt = new Constants.SoQuy();
Sql = new Constants.Sql();
string sql = Sql.UpdateSoDuSoQuy;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(pt.SoDuSoQuyID, SqlDbType.Int).Value = pxh.SoDuSoQuyID;
cmd.Parameters.Add(pt.SoDuCuoiKy, SqlDbType.Float).Value = pxh.SoDuDauKy;
int i = cmd.ExecuteNonQuery();
if (i == 1)
{
kt = true;
}
else
{
kt = false;
}
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
Insert(pxh);
return(kt);
}
catch
{
return(false);
}
}
/// <summary>
/// Select tat ca hang hoa trong kho
/// </summary>
/// <returns></returns>
public Entities.ChiTietKhoHang[] SelectAll()
{
List <Entities.ChiTietKhoHang> arrC = new List <ChiTietKhoHang>();
try
{
string sql = "";
sql = "Select b.MaKho, b.MaHangHoa, a.TenHangHoa, b.NgayNhap,b.HanSuDung,b.SoLuong,b.GhiChu from HangHoa a join ChiTietKhoHang b on a.MaHangHoa=b.MaHangHoa where b.Deleted='false'";
conn = new Connection();
cn = conn.openConnection();
cmd = new SqlCommand(sql, cn);
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
Entities.ChiTietKhoHang ctkho = new Entities.ChiTietKhoHang();
ctkho.MaKho = dr["MaKho"].ToString();
ctkho.MaHangHoa = dr["MaHangHoa"].ToString();
ctkho.TenHangHoa = dr["TenHangHoa"].ToString();
ctkho.SoLuong = Convert.ToInt32(dr["SoLuong"].ToString());
ctkho.NgayNhap = DateTime.Parse(dr["NgayNhap"].ToString());
ctkho.HanSuDung = DateTime.Parse(dr["HanSuDung"].ToString());
ctkho.GhiChu = dr["GhiChu"].ToString();
arrC.Add(ctkho);
}
}
catch (Exception ex)
{
string s = ex.Message.ToString();
}
finally
{
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
}
return(arrC.ToArray());
}
//delete
public int Delete(Entities.HangHoa hh1)
{
hh = new Constants.HangHoa();
Entities.HangHoa hanghoa = new Entities.HangHoa();
Sql = new Constants.Sql();
string sql = Sql.DeleteHangHoa;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(hh.HanhDong, SqlDbType.NVarChar, 20).Value = hh1.HanhDong;
cmd.Parameters.Add(hh.HangHoaID, SqlDbType.Int).Value = hh1.HangHoaID;
int i = cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
return(i);
}
public void Delete(Entities.NhanVien nv1)
{
try
{
nv = new Constants.NhanVien();
Sql = new Constants.Sql();
string sql = Sql.DeleteNhanVien;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(nv.HanhDong, SqlDbType.NVarChar, 20).Value = nv1.HanhDong;
cmd.Parameters.Add(nv.NhanVienID, SqlDbType.Int).Value = nv1.NhanVienID;
cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
}
catch (Exception ex) { string s = ex.Message; }
}
/// <summary>
///
/// </summary>
/// <param name="gv"></param>
/// <returns></returns>
public Entities.GiaVonBanHang[] Select(Entities.GiaVonBanHang gv)
{
string sql = Common.GiaVonBanHang.Select;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(Common.GiaVonBanHang.HanhDong, SqlDbType.VarChar, 50).Value = gv.HanhDong;
cmd.Parameters.Add(Common.GiaVonBanHang.MaHoaDon, SqlDbType.VarChar, 50).Value = gv.MaHoaDon;
cmd.Parameters.Add(Common.GiaVonBanHang.MaHangHoa, SqlDbType.VarChar, 50).Value = gv.MaHangHoa;
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//Insert Category into ArrayList
List <Entities.GiaVonBanHang> arr = new List <Entities.GiaVonBanHang>();
while (dr.Read())
{
Entities.GiaVonBanHang item = new Entities.GiaVonBanHang();
//item.HanhDong = dr[Common.GiaVonBanHang.HanhDong].ToString();
item.MaHoaDon = dr[Common.GiaVonBanHang.MaHoaDon].ToString();
item.MaHangHoa = dr[Common.GiaVonBanHang.MaHangHoa].ToString();
item.GiaVon = double.Parse(dr[Common.GiaVonBanHang.GiaVon].ToString());
arr.Add(item);
}
int n = arr.Count;
if (n == 0)
{
return(null);
}
//Giai phong bo nho
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
return(arr.ToArray());
}
protected void btnsubmit_Click(object sender, EventArgs e)
{
c.openConnection();
String str;
str = ("insert into State(State_Name)values('" + txtStateName.Text + "')");
int x = c.changeTable(str);
if (x > 0)
{
Label1.Text = "State inserted successfully";
Label1.Visible = true;
GridView1.DataBind();
clear();
}
else
{
Label1.Text = "State insert unsuccessfully";
Label1.Visible = true;
}
c.closeConnection();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
if (txtUsername.Text == "Admin" && txtPassword.Text == "Admin")
{
Session["Admin"] = txtUsername.Text;
Response.Redirect("~/Admin/AdminHome.aspx");
}
else
{
c.openConnection();
SqlCommand cmd;
cmd = new SqlCommand("select * from Register where Username='******' and Password='******'", sqlcon);
//string str;
//str = "select * from Register where Username='******' and Password='******'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
try
{
da.Fill(dt);
if (dt.Rows.Count > 0)
{
Session["Cust"] = txtUsername.Text;
Session["Pass"] = txtUsername.Text;
Response.Redirect("~/Customer/Home.aspx");
}
else
{
Label1.Visible = true;
Label1.Text = "Invalid Username";
}
}
catch (Exception ex)
{
ex.ToString();
Label1.Visible = true;
Label1.Text = "unable to Connect Database";
}
}
}
public void Delete(Entities.PhieuDieuChuyenKhoNoiBo phieuDCK)
{
try
{
pdcknb = new Constants.PhieuDieuChuyenKhoNoiBo();
Sql = new Constants.Sql();
string sql = Sql.DeletePhieuDieuChuyenKhoNoiBo;
Connection conn = new Connection();
SqlConnection cn = conn.openConnection();
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(pdcknb.HanhDong, SqlDbType.NVarChar, 20).Value = phieuDCK.HanhDong;
cmd.Parameters.Add(pdcknb.PhieuDieuChuyenKhoID, SqlDbType.Int).Value = phieuDCK.PhieuDieuChuyenKhoID;
cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
cn.Close();
conn.closeConnection();
cn = null;
conn = null;
}
catch (Exception ex) { string s = ex.Message; }
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
c.openConnection();
String str;
str = ("UPDATE Register SET Password ='******' WHERE UserName='******' AND Password='******'");
int x = c.changeTable(str);
if (x != 0)
{
Label3.Text = "Password changed successuful";
Label3.Visible = true;
clear();
}
else
{
Label3.Text = "Password not change";
clear();
}
c.closeConnection();
}
//============Thêm Sửa Xóa===================================================
/// <summary>
/// ThaoTac_GiaVon:
/// - Select_GIAVON
/// - Insert_GIAVON
/// - Update_TheoMaKhoVaMaHangHoa_GIAVON
/// - Delete_TheoMaKhoVaMaHangHoa_GIAVON
/// </summary>
/// <param name="thaotac"></param>
/// <param name="set"></param>
/// <param name="filter"></param>
/// <returns></returns>
public int ThaoTac_GiaVon(string thaotac, Entities.GiaVon set, Entities.GiaVon filter)
{
int bg = 0;
try
{
con = new Connection();
cn = con.openConnection();
cmd = new SqlCommand("exec sp_GiaVon @ThaoTac,@MaKho,@MaHangHoa,@SoLuong,@Gia,@MaKhok,@MaHangHoak,@SoLuongk,@Giak", cn);
cmd.Parameters.Add("ThaoTac", SqlDbType.NVarChar).Value = thaotac;
//set
cmd.Parameters.Add("MaKho", SqlDbType.NVarChar).Value = set.MaKho;
cmd.Parameters.Add("MaHangHoa", SqlDbType.NVarChar).Value = set.MaHangHoa;
cmd.Parameters.Add("SoLuong", SqlDbType.NVarChar).Value = set.SoLuong;
cmd.Parameters.Add("Gia", SqlDbType.NVarChar).Value = set.Gia;
//filter
cmd.Parameters.Add("MaKhok", SqlDbType.NVarChar).Value = filter.MaKho;
cmd.Parameters.Add("MaHangHoak", SqlDbType.NVarChar).Value = filter.MaHangHoa;
cmd.Parameters.Add("SoLuongk", SqlDbType.NVarChar).Value = filter.SoLuong;
cmd.Parameters.Add("Giak", SqlDbType.NVarChar).Value = filter.Gia;
bg = cmd.ExecuteNonQuery();
}
catch (Exception)
{
bg = 0;
}
finally
{
cmd.Connection.Dispose();
cn.Close();
con.closeConnection();
}
if (bg == 0)
{
return(bg);
}
return(bg);
}
public Carro getCarrobyId(Carro carro)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "SELECT * FROM Carro where ID_carro =" + carro.id_carro;
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
Carro returnCarro = new Carro();
while (reader.Read())
{
returnCarro.id_carro = Int32.Parse(string.Format("{0}", reader[0]));
returnCarro.modelo = string.Format("{0}", reader[1]);
returnCarro.marca = string.Format("{0}", reader[2]);
returnCarro.cor = string.Format("{0}", reader[3]);
return returnCarro;
}
con.closeConnection();
return returnCarro;
}
public List<Cliente> buscarTicket(Boolean mensalista,String busca)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "";
if (mensalista == true)
{
sql = "SELECT C.Id_Cliente,C.Nome,C.Cpf,C.Saldo FROM Ticketcar.dbo.Clientes AS C LEFT JOIN Ticketcar.dbo.Mensalistas AS M ON C.Id_Cliente = M.Id_cliente WHERE M.Id_cliente IS NOT NULL AND DAY(M.Data_termino) = DAY(GETDATE()) AND C.Nome LIKE '%" + busca + "%'";
}
else
{
sql = "SELECT C.Id_Cliente,C.Nome,C.Cpf,C.Saldo FROM Ticketcar.dbo.Clientes AS C LEFT JOIN Ticketcar.dbo.Mensalistas AS M ON C.Id_Cliente = M.Id_cliente WHERE M.Id_cliente IS NULL AND C.Nome LIKE '%" + busca+"%'";
}
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
List<Cliente> listaClientes = new List<Cliente>();
while (reader.Read())
{
Cliente cliente = new Cliente();
cliente.id_cliente = Int32.Parse(string.Format("{0}", reader[0]));
cliente.nome = string.Format("{0}", reader[1]);
cliente.cpf = string.Format("{0}", reader[2]);
cliente.saldo = double.Parse(string.Format("{0}", reader[3]));
listaClientes.Add(cliente);
}
con.closeConnection();
return listaClientes;
}
public List<Ticket> listaarCaixa()
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "SELECT ID_ticket,Id_cliente,id_Carro,Id_Servico,DataEntrada,DataSaida,ValorTotal, Placa,UF,Cidade FROM Ticketcar.dbo.Tickets WHERE CAST(DataEntrada AS DATE) = CAST(GETDATE() AS DATE)";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
List<Ticket> listaTickets = new List<Ticket>();
while (reader.Read())
{
Ticket ticket = new Ticket();
//Ticket
ticket.id_ticket = int.Parse(string.Format("{0}", reader[0]));
//Cliente
ticket.id_cliente = int.Parse(string.Format("{0}", reader[1]));
//Carro
ticket.id_carro = Convert.ToInt32(string.Format("{0}", reader["id_Carro"]));
//Servico
ticket.id_servico = int.Parse(string.Format("{0}", reader["Id_Servico"]));
//Ticket
ticket.data_entrada = Convert.ToDateTime(reader["DataEntrada"]).ToString("dd/MM/yyyy HH:mm");
ticket.data_saida = Convert.ToDateTime(reader["DataSaida"]).ToString("dd/MM/yyyy HH:mm");
ticket.valorTotal = Convert.ToDouble(string.Format("{0}", reader[6]));
//Placa carro
ticket.placa = string.Format("{0}", reader[7]);
ticket.uf = string.Format("{0}", reader[8]);
ticket.cidade = string.Format("{0}", reader[9]);
listaTickets.Add(ticket);
}
con.closeConnection();
return listaTickets;
}
public Boolean inserir(Carro carro)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
string sql = "INSERT INTO Carro (marca,modelo,cor) VALUES('" + carro.marca + "','" + carro.modelo + "','" + carro.cor + "')";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
command.ExecuteNonQuery();
con.closeConnection();
return true;
}
public Ticket getTicketbyId(Ticket ticket)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "SELECT T.ID_ticket,Cl.Id_Cliente,Cl.Nome,Cl.Cpf,Ca.ID_carro,Ca.Marca,Ca.Modelo,T.Placa,T.UF,T.Cidade,S.ID_servico,S.Descricao,S.Preco,T.DataEntrada,T.DataSaida,T.ValorTotal FROM ticketcar.dbo.Tickets AS T INNER JOIN Clientes AS Cl ON T.Id_cliente = Cl.Id_Cliente INNER JOIN Carro AS Ca ON T.id_Carro = Ca.ID_carro INNER JOIN Servicos AS S ON T.Id_Servico = S.ID_servico WHERE T.ID_ticket =" + ticket.id_ticket;
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
Ticket returnTicket = new Ticket();
while (reader.Read())
{
//Ticket
ticket.id_ticket = int.Parse(string.Format("{0}", reader[0]));
//Cliente
ticket.id_cliente = int.Parse(string.Format("{0}", reader[1]));
//Carro
ticket.id_carro = Convert.ToInt32(string.Format("{0}", reader["id_Carro"]));
//Servico
ticket.id_servico = int.Parse(string.Format("{0}", reader["Id_Servico"]));
//Ticket
ticket.data_entrada = Convert.ToDateTime(reader["DataEntrada"]).ToString("dd/MM/yyyy HH:mm");
ticket.data_saida = Convert.ToDateTime(reader["DataSaida"]).ToString("dd/MM/yyyy HH:mm");
ticket.valorTotal = Convert.ToDouble(string.Format("{0}", reader[6]));
//Placa carro
ticket.placa = string.Format("{0}", reader[7]);
ticket.uf = string.Format("{0}", reader[8]);
ticket.cidade = string.Format("{0}", reader[9]);
return returnTicket;
}
con.closeConnection();
return returnTicket;
}
public Boolean inserir(Mensalista mensalista)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
string sql = "INSERT INTO Mensalistas (Id_cliente, Data_termino) VALUES(" + mensalista.id_cliente + ",'" + mensalista.data_termino + "')";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
command.ExecuteNonQuery();
con.closeConnection();
return true;
}
public double getTotalCaixa()
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "SELECT SUM(ValorTotal) AS TotalCaixa FROM Ticketcar.dbo.Tickets WHERE CAST(DataEntrada AS DATE) = CAST(GETDATE() AS DATE)";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
Double total = 0;
while (reader.Read())
{
total = double.Parse(string.Format("{0}", reader["TotalCaixa"]));
}
return total;
}
public Boolean editar(Cliente cliente)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
String sql = "UPDATE Clientes set Nome='" + cliente.nome + "', Cpf ='" + cliente.cpf + "', Saldo = " + cliente.saldo+ " where Id_cliente = " + cliente.id_cliente;
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
command.ExecuteNonQuery();
con.closeConnection();
return true;
}
public Boolean inserir(Cliente cliente)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
string sql = "INSERT INTO Clientes (Nome,Cpf,Saldo) VALUES('" + cliente.nome + "','" + cliente.cpf + "'," + cliente.saldo + ")";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
command.ExecuteNonQuery();
con.closeConnection();
return true;
}
public Cliente getClientebyId(Cliente cliente)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "SELECT * FROM Clientes where Id_cliente =" + cliente.id_cliente;
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
Cliente returnCliente = new Cliente();
while (reader.Read())
{
returnCliente.id_cliente = Int32.Parse(string.Format("{0}", reader[0]));
returnCliente.nome = string.Format("{0}", reader[1]);
returnCliente.cpf = string.Format("{0}", reader[2]);
returnCliente.saldo = double.Parse(string.Format("{0}", reader[3]));
return returnCliente;
}
con.closeConnection();
return returnCliente;
}
public Boolean inserir(Ticket ticket, Carro carro, Cliente cliente, Servicos servico)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
string sql = "INSERT INTO Tickets (Id_cliente,Id_carro,Id_Servico,DataEntrada,DataSaida,ValorTotal,Placa,UF,Cidade) VALUES(" + cliente.id_cliente + "," + carro.id_carro + "," + servico.id_servico + ",'" + ticket.data_entrada + "','" + ticket.data_saida + "'," + ticket.valorTotal + ",'" + ticket.placa + "','" + ticket.uf + "','" + ticket.cidade + "')";
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
command.ExecuteNonQuery();
con.closeConnection();
return true;
}
public Boolean deletar(Cliente cliente)
{
Connection con = new Connection();
con.openConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
string sql = "DELETE Clientes WHERE Id_cliente = " + cliente.id_cliente;
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = con.getConnection();
reader = command.ExecuteReader();
con.closeConnection();
return true;
}
