/// <summary>
/// Hàm lấy danh sách lỗ khoan của một bản vẽ
/// </summary>
/// <param name="_id">ID bản vẽ</param>
/// <returns>List<LoKhoan></returns>
public static List<LoKhoan> DanhSachLoKhoan(String _id)
{
List<LoKhoan> danhSachLoKhoan = new List<LoKhoan>();
Connection conn = new Connection();
string sql = "SELECT * FROM LoKhoan WHERE MaBanVe = " + _id;
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachLoKhoan.Add(new LoKhoan(dr["MaHoChieu"].ToString(), dr["MaLK"].ToString(), dr["MaBanVe"].ToString(), dr["MaMayKhoan"].ToString(), dr.GetDouble(4), dr.GetDouble(5), dr["HuongKhoan"].ToString(), dr.GetDouble(7), dr.GetDouble(8)));
}
return danhSachLoKhoan;
}
/// <summary>
/// Hàm lấy thông tin công trường
/// </summary>
/// <param name="id">mã công trường</param>
/// <returns>CongTruong</returns>
public static CongTruong CongTruong(String id)
{
CongTruong congTruong = new CongTruong();
Connection conn = new Connection();
string sql = "SELECT * FROM CongTruong WHERE MaCongTruong = '" + id + "'";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
congTruong = new CongTruong(dr["MaCongTruong"].ToString(), dr["TenCongTruong"].ToString(), dr["SoDienThoai"].ToString(), dr.GetDouble(3), dr["Tang"].ToString(), dr["KhuVuc"].ToString());
}
return congTruong;
}
/// <summary>
/// Hàm trả về danh sách hộ chiếu sử dụng gần đây nhất
/// </summary>
/// <returns>List<HoChieu></returns>
public static List<HoChieu> DanhSachHoChieuGanNhat()
{
List<HoChieu> danhSachHoChieu = new List<HoChieu>();
String sql = "SELECT * FROM HoChieu a ORDER BY (SELECT NgayChinhSua FROM BanVe WHERE ID = a.MaBanVe)";
Connection conn = new Connection();
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachHoChieu.Add(new HoChieu(dr["MaHoChieu"].ToString(), dr["MaBanVe"].ToString(), dr["TenHoChieu"].ToString(), ((dr["ChieuCaoTang"].ToString() == String.Empty) ? -1 : Convert.ToDouble(dr["ChieuCaoTang"])), ((dr["QuyMoDotNo"].ToString() == String.Empty) ? -1 : Convert.ToDouble(dr["QuyMoDotNo"])), dr["MaCongTruong"].ToString(), dr["MaDatDa"].ToString(), dr["MaMayKhoan"].ToString(), dr["MaKipNo"].ToString(), dr["MaThuocNo"].ToString(), dr["MaPPNo"].ToString(), dr["MaPhuKien"].ToString(), dr["MaCapDo"].ToString(), dr["NgayLap"].ToString(), ((dr["HeSoKC"].ToString() == String.Empty) ? -1 : Convert.ToDouble(dr["HeSoKC"])), ((dr["KichThuoc_X"].ToString() == String.Empty) ? -1 : Convert.ToDouble(dr["KichThuoc_X"])), ((dr["KichThuoc_Y"].ToString() == String.Empty) ? -1 : Convert.ToDouble(dr["KichThuoc_Y"]))));
}
return danhSachHoChieu;
}
/// <summary>
/// Hàm lấy thông tin máy khoan
/// </summary>
/// <param name="id">mã máy khoan</param>
/// <returns>MayKhoan</returns>
public static MayKhoan MayKhoan(String id)
{
MayKhoan mayKhoan = new MayKhoan();
Connection conn = new Connection();
string sql = "SELECT * FROM MayKhoan WHERE MaMayKhoan = '" + id + "'";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
mayKhoan = new MayKhoan(dr["MaMayKhoan"].ToString(), dr["TenMayKhoan"].ToString(), dr.GetDouble(2), dr["HuongKhoan"].ToString(), dr.GetDouble(4));
}
return mayKhoan;
}
/// <summary>
/// Hàm lấy thông tin hộ chiếu nổ mìn
/// </summary>
/// <param name="id">mã hộ chiếu</param>
/// <returns>HoChieu</returns>
public static HoChieu HoChieu(String id)
{
HoChieu hoChieu = new HoChieu();
Connection conn = new Connection();
string sql = "SELECT * FROM HoChieu WHERE MaHoChieu = '" + id + "'";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
hoChieu = new HoChieu(dr["MaHoChieu"].ToString(), dr["MaBanVe"].ToString(), dr["TenHoChieu"].ToString(), ((dr["ChieuCaoTang"].ToString() == String.Empty) ? -1 : Convert.ToDouble(dr["ChieuCaoTang"])), ((dr["QuyMoDotNo"].ToString() == String.Empty) ? -1 : Convert.ToDouble(dr["QuyMoDotNo"])), dr["MaCongTruong"].ToString(), dr["MaDatDa"].ToString(), dr["MaMayKhoan"].ToString(), dr["MaKipNo"].ToString(), dr["MaThuocNo"].ToString(), dr["MaPPNo"].ToString(), dr["MaPhuKien"].ToString(), dr["MaCapDo"].ToString(), dr["NgayLap"].ToString(), ((dr["HeSoKC"].ToString() == String.Empty) ? -1 : Convert.ToDouble(dr["HeSoKC"])), ((dr["KichThuoc_X"].ToString() == String.Empty) ? -1 : Convert.ToDouble(dr["KichThuoc_X"])), ((dr["KichThuoc_Y"].ToString() == String.Empty) ? -1 : Convert.ToDouble(dr["KichThuoc_Y"])));
}
return hoChieu;
}
/// <summary>
/// Hàm tìm kiếm bản vẽ tương ứng với tên bản vẽ
/// </summary>
/// <param name="tenBanVe">Tên bản vẽ</param>
/// <returns>List<BanVe></returns>
public static List<BanVe> TimKiemBanVe(String tenBanVe)
{
List<BanVe> danhSachBanVe = new List<BanVe>();
String sql = "SELECT * FROM BanVe WHERE TenBanVe LIKE '%" + tenBanVe + "%'";
Connection conn = new Connection();
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachBanVe.Add(new BanVe(dr["ID"].ToString(), dr["Template"].ToString(), dr["TenBanVe"].ToString(), dr.GetString(3), dr.GetDouble(4), dr.GetDouble(5), dr["NguoiDung"].ToString(), (dr.GetInt32(7) == 1) ? true : false));
}
return danhSachBanVe;
}
/// <summary>
/// Hàm trả về danh sách phương pháp nổ
/// </summary>
/// <returns>List<PhuongPhapNo></returns>
public static List<PhuongPhapNo> DanhSachPPNo()
{
List<PhuongPhapNo> danhSachPPNo = new List<PhuongPhapNo>();
Connection conn = new Connection();
string sql = "SELECT * FROM PhuongPhapNo";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachPPNo.Add(new PhuongPhapNo(dr["MaPPNo"].ToString(), dr["TenPPNo"].ToString(), dr["MaCongTruong"].ToString(), dr.GetDouble(3), dr.GetDouble(4)));
}
return danhSachPPNo;
}
/// <summary>
/// Hàm lấy danh sách máy khoan
/// </summary>
/// <returns>List<MayKhoan></returns>
static public List<MayKhoan> DanhSachMayKhoan()
{
List<MayKhoan> danhSachMayKhoan = new List<MayKhoan>();
Connection conn = new Connection();
string sql = "SELECT * FROM MayKhoan";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachMayKhoan.Add(new MayKhoan(dr["MaMayKhoan"].ToString(), dr["TenMayKhoan"].ToString(), dr.GetDouble(2), dr["HuongKhoan"].ToString(), dr.GetDouble(4)));
}
return danhSachMayKhoan;
}
/// <summary>
/// Hàm trả về danh sách thuốc nổ
/// </summary>
/// <returns>List<ThuocNo></returns>
public static List<ThuocNo> DanhSachThuocNo()
{
List<ThuocNo> danhSachThuocNo = new List<ThuocNo>();
Connection conn = new Connection();
string sql = "SELECT * FROM ThuocNo";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachThuocNo.Add(new ThuocNo(dr["MaThuocNo"].ToString(), dr["TenThuocNo"].ToString(), dr.GetDouble(2), dr.GetDouble(3), dr.GetDouble(4), dr["ChiuNuoc"].ToString(), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8)));
}
return danhSachThuocNo;
}
/// <summary>
/// Hàm trả về danh sách kíp nổ
/// </summary>
/// <returns>List<KipNo></returns>
public static List<KipNo> DanhSachKipNo()
{
List<KipNo> danhSachKipNo = new List<KipNo>();
Connection conn = new Connection();
string sql = "SELECT * FROM KipNo";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachKipNo.Add(new KipNo(dr["MaKipNo"].ToString(), dr["TenKipNo"].ToString(), dr["ThoiGianViSai"].ToString(), dr.GetDouble(3), dr.GetDouble(4)));
}
return danhSachKipNo;
}
/// <summary>
/// Hàm lấy thông tin kíp nổ
/// </summary>
/// <param name="id">mã kíp nổ</param>
/// <returns>KipNo</returns>
public static KipNo KipNo(String id)
{
KipNo kipNo = new KipNo();
Connection conn = new Connection();
string sql = "SELECT * FROM KipNo WHERE MaKipNo = '" + id + "'";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
kipNo = new KipNo(dr["MaKipNo"].ToString(), dr["TenKipNo"].ToString(), dr["ThoiGianViSai"].ToString(), dr.GetDouble(3), dr.GetDouble(4));
}
return kipNo;
}
/// <summary>
/// Hàm lấy danh sách Template
/// </summary>
/// <returns>List<Template></returns>
static public List<Template> DanhSachTemplate()
{
List<Template> danhSachTemplate = new List<Template>();
String sql = "SELECT * FROM Template";
Connection conn = new Connection();
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachTemplate.Add(new Template(dr["ID"].ToString(), dr["TenTemplate"].ToString(), dr["DuongDan"].ToString(), dr["MoTa"].ToString(), dr["AnhMau"].ToString()));
}
return danhSachTemplate;
}
/// <summary>
/// Hàm trả về danh sách đất đá
/// </summary>
/// <returns>List<DatDa></returns>
public static List<DatDa> DanhSachDatDa()
{
List<DatDa> danhSachDatDa = new List<DatDa>();
Connection conn = new Connection();
string sql = "SELECT * FROM DatDa";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachDatDa.Add(new DatDa(dr["MaDatDa"].ToString(), dr["TenDatDa"].ToString(), dr.GetDouble(2), dr.GetDouble(3), dr["TinhChat"].ToString(), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7)));
}
return danhSachDatDa;
}
/// <summary>
/// Hàm lấy thông tin phương pháp nổ
/// </summary>
/// <param name="id">mã phương pháp nổ</param>
/// <returns>PhuongPhapNo</returns>
public static PhuongPhapNo PhuongPhapNo(String id)
{
PhuongPhapNo phuongPhapNo = new PhuongPhapNo();
Connection conn = new Connection();
string sql = "SELECT * FROM PhuongPhapNo WHERE MaPPNo = '" + id + "'";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
phuongPhapNo = new PhuongPhapNo(dr["MaPPNo"].ToString(), dr["TenPPNo"].ToString(), dr["MaCongTruong"].ToString(), dr.GetDouble(3), dr.GetDouble(4));
}
return phuongPhapNo;
}
/// <summary>
/// Hàm lấy thông tin đất đá
/// </summary>
/// <param name="id">mã đất đá</param>
/// <returns>DatDa</returns>
public static DatDa DatDa(String id)
{
DatDa datDa = new DatDa();
Connection conn = new Connection();
string sql = "SELECT * FROM DatDa WHERE MaDatDa = '" + id + "'";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
datDa = new DatDa(dr["MaDatDa"].ToString(), dr["TenDatDa"].ToString(), dr.GetDouble(2), dr.GetDouble(3), dr["TinhChat"].ToString(), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7));
}
return datDa;
}
/// <summary>
/// Hàm trả về danh sách công trường
/// </summary>
/// <returns>List<CongTruong></returns>
public static List<CongTruong> DanhSachCongTruong()
{
List<CongTruong> danhSachCongTruong = new List<CongTruong>();
Connection conn = new Connection();
string sql = "SELECT * FROM CongTruong";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachCongTruong.Add(new CongTruong(dr["MaCongTruong"].ToString(), dr["TenCongTruong"].ToString(), dr["SoDienThoai"].ToString(), dr.GetDouble(3), dr["Tang"].ToString(), dr["KhuVuc"].ToString()));
}
return danhSachCongTruong;
}
/// <summary>
/// Hàm lấy bản vẽ từ ID tương ứng
/// </summary>
/// <param name="ID">ID bản vẽ</param>
/// <returns>BanVe</returns>
public static BanVe BanVe(String ID)
{
BanVe danhSachBanVe = new BanVe();
String sql = "SELECT * FROM BanVe WHERE ID = '" + ID + "'";
Connection conn = new Connection();
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachBanVe = new BanVe(dr["ID"].ToString(), dr["Template"].ToString(), dr["TenBanVe"].ToString(), dr.GetString(3), dr.GetDouble(4), dr.GetDouble(5), dr["NguoiDung"].ToString(), (dr.GetInt32(7) == 1)? true:false);
}
return danhSachBanVe;
}
/// <summary>
/// Hàm lấy thông tin một lỗ khoan của một bản vẽ
/// </summary>
/// <param name="_idHoChieu">ID hộ chiếu</param>
/// <param name="_idLoKhoan">ID lỗ khoan</param>
/// <returns>LoKhoan</returns>
public static LoKhoan ThongTinLoKhoan(String _idHoChieu, String _idLoKhoan)
{
LoKhoan loKhoan = new LoKhoan();
Connection conn = new Connection();
string sql = "SELECT * FROM LoKhoan WHERE MaHoChieu = '" + _idHoChieu + "' AND MaLK = '" + _idLoKhoan + "'";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
loKhoan = new LoKhoan(dr["ID"].ToString(), dr["MaHoChieu"].ToString(), dr["MaBanVe"].ToString(), dr["MaMayKhoan"].ToString(), dr["MaLK"].ToString(), dr.GetDouble(5), dr.GetDouble(6), dr["HuongKhoan"].ToString(), dr.GetDouble(8), dr.GetDouble(9), dr.GetDouble(10), dr.GetDouble(11), dr.GetDouble(12), dr.GetDouble(13));
}
return loKhoan;
}
/// <summary>
/// Hàm lấy danh sách lỗ khoan
/// </summary>
/// <returns>List<LoKhoan></returns>
public static List<LoKhoan> DanhSachLoKhoan()
{
List<LoKhoan> danhSachLoKhoan = new List<LoKhoan>();
Connection conn = new Connection();
string sql = "SELECT * FROM LoKhoan";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachLoKhoan.Add(new LoKhoan(dr["ID"].ToString(), dr["MaHoChieu"].ToString(), dr["MaBanVe"].ToString(), dr["MaMayKhoan"].ToString(), dr["MaLK"].ToString(), dr.GetDouble(5), dr.GetDouble(6), dr["HuongKhoan"].ToString(), dr.GetDouble(8), dr.GetDouble(9), dr.GetDouble(10), dr.GetDouble(11), dr.GetDouble(12), dr.GetDouble(13)));
}
return danhSachLoKhoan;
}
public static List<BanVe> DanhSachBanVeGanNhat()
{
List<BanVe> danhSachBanVe = new List<BanVe>();
String sql = "SELECT * FROM BanVe ORDER BY NgayChinhSua DESC LIMIT 5";
Connection conn = new Connection();
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachBanVe.Add(new BanVe(dr["ID"].ToString(), dr["TenBanVe"].ToString(), dr.GetString(2)));
}
return danhSachBanVe;
}
/// <summary>
/// Hàm lấy thông tin thuốc nổ
/// </summary>
/// <param name="id">mã thuốc nổ</param>
/// <returns>ThuocNo</returns>
public static ThuocNo ThuocNo(String id)
{
ThuocNo thuocNo = new ThuocNo();
Connection conn = new Connection();
string sql = "SELECT * FROM ThuocNo WHERE MaThuocNo = '" + id + "'";
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
thuocNo = new ThuocNo(dr["MaThuocNo"].ToString(), dr["TenThuocNo"].ToString(), dr.GetDouble(2), dr.GetDouble(3), dr.GetDouble(4), dr["ChiuNuoc"].ToString(), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8));
}
return thuocNo;
}
/// <summary>
/// Hàm xóa danh sách đất đá
/// </summary>
/// <param name="dsXoa">Danh sách đất đá cần xóa</param>
/// <returns>bool</returns>
public static bool XoaDatDa(List<DatDa> dsXoa)
{
if (dsXoa.Count == 0)
{
return true;
}
Connection conn = new Connection();
String sql = "";
foreach (DatDa value in dsXoa)
{
sql = "DELETE FROM DatDa WHERE MaDatDa = '" + value.MaDatDa + "'";
conn.SetData(sql);
}
return true;
}
/// <summary>
/// Hàm xóa danh sách máy khoan
/// </summary>
/// <param name="dsXoa">Danh sách máy khoan cần xóa</param>
/// <returns>bool</returns>
public static bool XoaMayKhoan(List<MayKhoan> dsXoa)
{
if (dsXoa.Count == 0)
{
return true;
}
Connection conn = new Connection();
String sql = "";
foreach (MayKhoan value in dsXoa)
{
sql = "DELETE FROM MayKhoan WHERE MaMayKhoan = '" + value.MaMayKhoan + "'";
conn.SetData(sql);
}
return true;
}
/// <summary>
/// Hàm xóa danh sách phương pháp nổ
/// </summary>
/// <param name="dsXoa">Danh sách phương pháp nổ cần xóa</param>
/// <returns>bool</returns>
public static bool XoaPhuongPhapNo(List<PhuongPhapNo> dsXoa)
{
if (dsXoa.Count == 0)
{
return true;
}
Connection conn = new Connection();
String sql = "";
foreach (PhuongPhapNo value in dsXoa)
{
sql = "DELETE FROM PhuongPhapNo WHERE MaPPNo = '" + value.MaPPNo + "'";
conn.SetData(sql);
}
return true;
}
/// <summary>
/// Hàm xóa danh sách công trường
/// </summary>
/// <param name="dsXoa">Danh sách công trường cần xóa</param>
/// <returns>bool</returns>
public static bool XoaCongTruong(List<CongTruong> dsXoa)
{
if (dsXoa.Count == 0)
{
return true;
}
Connection conn = new Connection();
String sql = "";
foreach (CongTruong value in dsXoa)
{
sql = "DELETE FROM CongTruong WHERE MaCongTruong = '" + value.MaCongTruong + "'";
conn.SetData(sql);
}
return true;
}
/// <summary>
/// Hàm lấy Template theo id
/// </summary>
/// <param name="ID">ID Template</param>
/// <returns>Template</returns>
static public Template ChonTemplate(String ID)
{
if (ID == null)
{
return null;
}
Template danhSachTemplate = new Template();
if (ID.Equals(String.Empty))
{
return danhSachTemplate;
}
String sql = "SELECT * FROM Template WHERE ID = " + ID;
Connection conn = new Connection();
SQLiteDataReader dr = conn.GetData(sql);
while (dr.Read())
{
danhSachTemplate = new Template(dr["ID"].ToString(), dr["TenTemplate"].ToString(), dr["DuongDan"].ToString(), dr["MoTa"].ToString(), dr["AnhMau"].ToString());
}
return danhSachTemplate;
}
/// <summary>
/// Hàm xóa danh sách bản vẽ
/// </summary>
/// <param name="dsXoa">Danh sách bản vẽ cần xóa</param>
/// <returns>bool</returns>
public static bool XoaBanVe(List<BanVe> dsXoa)
{
if (dsXoa.Count == 0)
{
return true;
}
Connection conn = new Connection();
String sql = "";
foreach (BanVe value in dsXoa)
{
////xóa lỗ khoan
//sql = "DELETE FROM LoKhoan WHERE MaBanVe = '" + value.ID + "'";
//conn.SetData(sql);
////xóa hộ chiếu
//sql = "DELETE FROM HoChieu WHERE MaHoChieu = '" + value.MaHoChieu + "'";
//conn.SetData(sql);
//xóa bản vẽ
sql = "DELETE FROM BanVe WHERE ID = '" + value.ID + "'";
conn.SetData(sql);
}
return true;
}
/// <summary>
/// Hàm cập nhật phương pháp nổ
/// </summary>
/// <param name="value">phương pháp nổ cần cập nhật</param>
/// <returns>bool</returns>
public static bool CapNhatPhuongPhapNo(PhuongPhapNo value)
{
String sql = "UPDATE PhuongPhapNo SET TenPPNo = '" + value.TenPPNo + "', MaCongTruong = '" + value.MaCongTruong + "', Hs_KienCo = '" + value.HeSoKienCo + "', HeSoPPNo = '" + value.HeSoPPNo + "' WHERE MaPPNo = '" + value.MaPPNo + "'";
Connection conn = new Connection();
return conn.SetData(sql);
}
/// <summary>
/// Hàm thêm phương pháp nổ
/// </summary>
/// <param name="value">phương pháp nổ cần thêm</param>
/// <returns>bool</returns>
public static bool ThemPhuongPhapNo(PhuongPhapNo value)
{
String sql = "INSERT INTO PhuongPhapNo(MaPPNo, TenPPNo, MaCongTruong, Hs_KienCo, HeSoPPNo) VALUES('" + value.MaPPNo + "', '" + value.TenPPNo + "', '" + value.MaCongTruong + "', '" + value.HeSoKienCo + "', '" + value.HeSoPPNo + "')";
Connection conn = new Connection();
return conn.SetData(sql);
}
/// <summary>
/// Hàm cập nhật phụ kiện nổ
/// </summary>
/// <param name="value">phụ kiện nổ cần cập nhật</param>
/// <returns>bool</returns>
public static bool CapNhatPhuKienNo(PhuKienNo value)
{
String sql = "UPDATE PhuKienNo SET TenPhuKien = '" + value.TenPhuKien + "', DonViTinh_PK = '" + value.DonViTinh_PK + "', GiaThanh_PK = '" + value.GiaThanh_PK + "' WHERE MaPhuKien = '" + value.MaPhuKien + "'";
Connection conn = new Connection();
return conn.SetData(sql);
}
