public static int CapNhatQuyDinhTienNo(DTO_QuyDinhTienNo qd)
{
string query = "update QuyDinhTienNo set TienNoToiDa=" + qd.TienNoToiDa + " where IdLoaiDL=" + qd.IdLoaiDL;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
/*
* CapNhatLoaiDaiLy trả về
* 1:thành công
* 0:thất bại
*/
public static int CapNhatQuan(DTO_Quan quan)
{
string query = "update Quan set TenQuan =N'" + quan.TenQuan + "' where IdQuan=" + quan.IdQuan;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
/*
* XoaChiTietXuat trả về
* 1:thành công
* 0:thất bại
*/
public static int XoaChiTietXuat(int idPhieuXuat)
{
string query = "delete ChiTietXuatHang where IdPhieuXuat=" + idPhieuXuat;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
/*
* CapNhatLoaiDaiLy trả về
* 1:thành công
* 0:thất bại
*/
public static int CapNhatLoaiDaiLy(DTO_LoaiDL loaiDL)
{
string query = "update LoaiDaiLy set TenLoaiDL =N'" + loaiDL.TenLoaiDL + "' where IdLoaiDL=" + loaiDL.IdLoaiDL;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
public static ArrayList DSPhieuXuat()
{
ArrayList arrList = new ArrayList();
DataTable dt = new DataTable();
dt = ResultQuery.GetTableResult(conn, "SELECT * FROM ChiTietXuatHang ");
int idPhieuXuat;
int idMatHang;
int soLuong;
float donGia;
string donViTinh;
float thanhTien;
foreach (DataRow r in dt.Rows)
{
idPhieuXuat = (int)r["IdPhieuXuat"];
idMatHang = (int)r["IdMatHang"];
soLuong = (int)r["SoLuong"];
donGia = (float)Convert.ToDouble(r["DonGia"]);
donViTinh = r["DonViTinh"].ToString();
thanhTien = (float)Convert.ToDouble(r["ThanhTien"]);
DTO_ChiTietXuat ctx = new DTO_ChiTietXuat(idPhieuXuat, idMatHang, soLuong, donGia, donViTinh, thanhTien);
arrList.Add(ctx);
}
return(arrList);
}
public static ArrayList DSPhieuThu()
{
ArrayList arrList = new ArrayList();
DataTable dt = new DataTable();
dt = ResultQuery.GetTableResult(conn, "SELECT * FROM PhieuThuTien ");
int idPhieuThu;
DateTime ngayThu;
float soTienThu;
int idDaiLy;
string cmnd;
foreach (DataRow r in dt.Rows)
{
idPhieuThu = (int)r["IdPhieuXuat"];
ngayThu = Convert.ToDateTime(r["NgayThu"]);
soTienThu = (float)Convert.ToDouble(r["SoTienThu"]);
idDaiLy = (int)r["IdDaiLy"];
cmnd = r["CMND"].ToString();
DTO_PhieuThuTien phieuThu = new DTO_PhieuThuTien(idPhieuThu, ngayThu, soTienThu, idDaiLy, cmnd);
arrList.Add(phieuThu);
}
return(arrList);
}
public static int XoaByIdDL(int idDl)
{
string query = "delete PhieuThuTien where IdDaiLy=" + idDl;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
/*
* CapNhatMatHang trả về
* 1:thành công
* 0:thất bại
*/
public static int CapNhatMatHang(DTO_MatHang matHang)
{
string query = "update MatHang set TenMatHang=N'" + matHang.TenMatHang + "' where IdMatHang=" + matHang.IdMatHang;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
/*
* trả về 0:update thất bại
* trả về 1:update thành công
*/
public static int CapNhatQuyDinhMatHang(DTO_QuyDinhMatHang qd)
{
string query = "update QuyDinhMatHang set DonViTinh=N'" + qd.DonViTinh + "',DonGia=" + qd.DonGia + " where IdMatHang=" + qd.IdMatHang;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
/*
* CapNhatChiTietXuat trả về
* 1:thành công
* 0:thất bại
*/
public static int CapNhatChiTietXuat(DTO_ChiTietXuat ctx)
{
string query = "update ChiTietXuatHang set IdMatHang =" + ctx.IdMatHang + ",SoLuong=" + ctx.SoLuong + ",DonGia=" + ctx.DonGia + " ,DonViTinh=" + ctx.DonViTinh + ",ThanhTien=" + ctx.ThanhTien + " where IdPhieuXuat=" + ctx.IdPhieuXuat;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
/*
* XoaLoaiDaiLy trả về
* 1:thành công
* 0:thất bại
*/
public static int XoaQuan(int id)
{
string query = "delete Quan where IdQuan=" + id;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
public static ArrayList search(string name)
{
ArrayList al = new ArrayList();
string query = "select d.TenDaiLy,d.DiaChi,d.SDT,p.NgayThu,p.SoTienThu,p.CMND,p.IdPhieuThu from PhieuThuTien as p, DaiLy as d where p.IdDaiLy = d.IdDaiLy and d.TenDaiLy like '%" + name + "%'";
DataTable tb = ResultQuery.GetTableResult(conn, query);
string tendaily;
DateTime ngayThu;
float soTienThu;
string diachi;
string cmnd;
string sdt;
int idpt;
foreach (DataRow r in tb.Rows)
{
idpt = (int)r["IdPhieuThu"];
tendaily = (string)r["TenDaiLy"];
ngayThu = Convert.ToDateTime(r["NgayThu"]);
soTienThu = (float)Convert.ToDouble(r["SoTienThu"]);
diachi = (string)r["DiaChi"];
sdt = (string)r["SDT"];
cmnd = (string)r["CMND"];
DTO_TGPhieuThuTien phieuThu = new DTO_TGPhieuThuTien(idpt, tendaily, ngayThu, soTienThu, diachi, cmnd, sdt);
al.Add(phieuThu);
}
return(al);
}
public static ArrayList DSPhieuThu()
{
ArrayList arrList = new ArrayList();
DataTable dt = new DataTable();
dt = ResultQuery.GetTableResult(conn, "select d.TenDaiLy,d.DiaChi,d.SDT,p.NgayThu,p.SoTienThu,p.CMND,p.IdPhieuThu from PhieuThuTien as p, DaiLy as d where p.IdDaiLy = d.IdDaiLy ");
string tendaily;
DateTime ngayThu;
float soTienThu;
string diachi;
string cmnd;
string sdt;
int idpt;
foreach (DataRow r in dt.Rows)
{
idpt = (int)r["IdPhieuThu"];
tendaily = (string)r["TenDaiLy"];
ngayThu = Convert.ToDateTime(r["NgayThu"]);
soTienThu = (float)Convert.ToDouble(r["SoTienThu"]);
diachi = (string)r["DiaChi"];
sdt = (string)r["SDT"];
cmnd = (string)r["CMND"];
DTO_TGPhieuThuTien phieuThu = new DTO_TGPhieuThuTien(idpt, tendaily, ngayThu, soTienThu, diachi, cmnd, sdt);
arrList.Add(phieuThu);
}
return(arrList);
}
/*
* XoaLoaiDaiLy trả về
* 1:thành công
* 0:thất bại
*/
public static int XoaLoaiDaiLy(int id)
{
string query = "delete LoaiDaiLy where IdLoaiDL=" + id;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
public static string getDonGia(int idMatHang)
{
string query = "select DonGia from QuyDinhMatHang where IdMatHang =" + idMatHang;
DataTable dt = ResultQuery.GetTableResult(conn, query);
DataRow r = dt.Rows[0];
return(r["DonGia"].ToString());
}
public static string GetTenById(int id)
{
string query = "select TenQuan from Quan where IdQuan=" + id;
DataTable dt = ResultQuery.GetTableResult(conn, query);
DataRow r = dt.Rows[0];
return(r["TenQuan"].ToString());
}
//public void Xoapxbyid(int id)
//{
// string query = "delete PhieuXuatHang where IdPhieuXuat=" + id;
//}
/*
* CapNhatPhieuXuat trả về
* 1:thành công
* 0:thất bại
*/
public static int CapNhatPhieuXuat(DTO_PhieuXuatHang pxh)
{
string ngayXuatSql = pxh.NgayXuat.ToString("yyyy-MM-dd");
string query = "update PhieuXuatHang set NgayXuat ='" + ngayXuatSql + "',IdDaiLy=" + pxh.IdDaiLy + " ,CMND='" + pxh.Cmnd + "' where IdPhieuXuat=" + pxh.IdPhieuXuat;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
public static string GetTenNhanVien(string cmnd)
{
string query = "select TenNV from NhanVienQL where CMND=" + cmnd;
DataTable dt = ResultQuery.GetTableResult(conn, query);
DataRow r = dt.Rows[0];
return(r["TenNV"].ToString());
}
/*
* XoaMatHang trả về
* 1:thành công
* 0:thất bại
*/
public static int XoaMatHang(int id)
{
DAL_ChiTietXuat.XoaChiTietXuat(id);
string query = "delete MatHang where IdMatHang=" + id;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
/*
* trả về 0: update không thành không
* trả về #0 : thành công
*/
public static int SuaQuyChe(DTO_QuyCheToChuc qc)
{
string query = "update QuyCheToChuc set SoLoaiDaiLy=" + qc.SoLoaiDaiLy + ",SoDaiLyToiDa=" + qc.SoDaiLyToiDa +
",SoMatHang=" + qc.SoMatHang + ",SoQuan=" + qc.SoMatHang;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
/*
* ThemChiTietXuat trả về
* 1:thành công
* 0:thất bại
*/
public static int ThemChiTietXuat(DTO_ChiTietXuat ctx)
{
int kq = 0;
string query = "insert into ChiTietXuatHang(IdPhieuXuat, IdMatHang, SoLuong, DonGia, DonViTinh, ThanhTien) values(" + ctx.IdPhieuXuat + "," + ctx.IdMatHang + "," + ctx.SoLuong + "," + ctx.DonGia + ", N'" +
ctx.DonViTinh + "', " + ctx.ThanhTien + ")";
kq = ResultQuery.GetResultQuery(conn, query);
return(kq);
}
/*
* ThemPhieuXuat trả về
* 1:thành công
* 0:thất bại
*/
public static int ThemPhieuXuat(DTO_PhieuXuatHang pxh)
{
int kq = 0;
string ngayXuatSql = pxh.NgayXuat.ToString("yyyy-MM-dd");
string query = "insert into PhieuXuatHang(NgayXuat,IdDaiLy,CMND) values ('" + ngayXuatSql + "'," + pxh.IdDaiLy + ",'" + pxh.Cmnd + "')";
kq = ResultQuery.GetResultQuery(conn, query);
return(kq);
}
/*
* ThemPhieuThu trả về
* 1:thành công
* 0:thất bại
*/
public static int ThemPhieuThu(DTO_PhieuThuTien ptt)
{
int kq = 0;
string ngayThuSql = ptt.NgayThu.ToString("yyyy-MM-dd");
string query = "insert into PhieuThuTien(NgayThu, SoTienThu, IdDaiLy, CMND) values('" + ngayThuSql + "'," + ptt.SoTienThu + "," + ptt.IdDaiLy + ",'" + ptt.Cmnd + "')";
kq = ResultQuery.GetResultQuery(conn, query);
return(kq);
}
public static string GetTen(int id)
{
string ten = "";
DataTable tb = ResultQuery.GetTableResult(conn, "select TenMatHang from MatHang where IdMatHang=" + id);
foreach (DataRow r in tb.Rows)
{
ten = r["TenMatHang"].ToString();
}
return(ten);
}
public static float GetTienNo(int id)
{
float tien = 0;
DataTable tb = ResultQuery.GetTableResult(conn, "select TienNo from DaiLy where IdDaiLy=" + id);
foreach (DataRow r in tb.Rows)
{
tien = (float)Convert.ToDouble(r["TienNo"].ToString());
}
return(tien);
}
public static string GetTen(int id)
{
string ten = "";
DataTable tb = ResultQuery.GetTableResult(conn, "select TenDaiLy from DaiLy where IdDaiLy=" + id);
foreach (DataRow r in tb.Rows)
{
ten = r["TenDaiLy"].ToString();
}
return(ten);
}
public static DTO_ThongTinTaiKhoan GetTaiKhoan(string user, string pass)
{
string query = "select* from ThongTinTaiKhoan where UserName = '******' and Pass = '******'";
DataTable dt = ResultQuery.GetTableResult(conn, query);
DataRow r = dt.Rows[0];
string cmnd = r["CMND"].ToString();
int phanQuyen = (int)r["PhanQuyen"];
int trangThai = (int)r["PhanQuyen"];
DTO_ThongTinTaiKhoan tk = new DTO_ThongTinTaiKhoan(user, pass, cmnd, phanQuyen, trangThai);
return(tk);
}
/*
* XoaDaiLy trả về
* 0 : sửa thất bại
* 1 : sửa thành công
*/
public static int XoaDaiLy(int idDaiLy)
{
//DAL_ChiTietXuat.XoaChiTietXuat(idDaiLy);
//DAL_PhieuXuat.XoaByIdDl(idDaiLy);
//DAL_PhieuThuTien.XoaByIdDl(idDaiLy);
//DAL_CongNo.XoaCongNo(idDaiLy);
//DAL_DoanhSo.XoaDoanhSo(idDaiLy);
string query = "delete from DaiLy where IdDaiLy=" + idDaiLy;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
public static ArrayList QDTienNo()
{
ArrayList arr = new ArrayList();
DataTable dt = new DataTable();
dt = ResultQuery.GetTableResult(conn, "SELECT * FROM QuyDinhTienNo");
foreach (DataRow r in dt.Rows)
{
DTO_QuyDinhTienNo qd = new DTO_QuyDinhTienNo();
qd.IdLoaiDL = (int)r["IdLoaiDL"];
qd.TienNoToiDa = (float)Convert.ToDouble(r["TienNoToiDa"]);
arr.Add(qd);
}
return(arr);
}
public static string GetTenById(int id)
{
//DTO_LoaiDL loaidl = null;
string query = "select TenLoaiDL from LoaiDaiLy where IdLoaiDL=" + id;
DataTable dt = ResultQuery.GetTableResult(conn, query);
//foreach (DataRow item in dt.Rows)
//{
// loaidl = new DTO_LoaiDL(item);
// return loaidl;
//}
//return loaidl;
DataRow r = dt.Rows[0];
return(r["TenLoaiDL"].ToString());
}
