/// <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 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 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 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;
 }
 /// <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;
 }
 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 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 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 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 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 đấ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 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 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 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 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 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 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 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 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 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 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 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 trả về danh sách bản vẽ sử dụng gần đây nhất (có giới hạn)
 /// </summary>
 /// <param name="number">số bản vẽ trả về</param>
 /// <returns>List<BanVe></returns>
 public static List<BanVe> DanhSachBanVeGanNhat(int number)
 {
     List<BanVe> danhSachBanVe = new List<BanVe>();
     String sql = "SELECT * FROM BanVe ORDER BY NgayChinhSua DESC LIMIT " + number;
     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 tạo hộ chiếu
 /// </summary>
 /// <param name="tenHoChieu">Tên hộ chiếu</param>
 /// <returns>ID hộ chiếu vừa tạo</returns>
 public static String TaoHoChieu(String idTemplate, String tenHoChieu)
 {
     Connection conn = new Connection();
     SQLiteDataReader dr;
     HoChieu hoChieuVuaTao = new HoChieu();
     bool flag = true;
     //kiểm tra tên hộ chiếu có trùng ko
     List<HoChieu> danhSachHoChieu = BS_HoChieu.DanhSachHoChieu();
     foreach (HoChieu value in danhSachHoChieu)
     {
         if (value.TenHoChieu == tenHoChieu)
         {
             flag = false; break;
         }
     }
     //nếu ko trùng => tạo hộ chiếu mới, trả về id hộ chiếu vừa tạo
     if (flag)
     {
         //tạo bản vẽ mới cho hộ chiếu
         String idBanVe = BS_BanVe.TaoBanVe(idTemplate, tenHoChieu);
         if (idBanVe == null)
         {
             return null;
         }
         else
         {
             String sql = "INSERT INTO HoChieu (MaBanVe, TenHoChieu, NgayLap) VALUES ('" + idBanVe + "', '" + tenHoChieu + "', '" + DateTime.Now.ToString() + "')";
             conn.SetData(sql);
             sql = "SELECT * FROM HoChieu WHERE TenHoChieu = '" + tenHoChieu + "'";
             dr = conn.GetData(sql);
             while (dr.Read())
             {
                 hoChieuVuaTao = 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 hoChieuVuaTao.MaHoChieu;
         }               
     }
     //nếu trùng => trả về null
     else
     {
         return null;
     }
 }
        public ExportData(String _maHoChieu)
        {
            Connection conn = new Connection();
            string sql = "SELECT HoChieu.*, LoKhoan.ID, LoKhoan.MaLK, LoKhoan.BanKinhLK, LoKhoan.ChieuSauLK FROM LoKhoan, BanVe, HoChieu WHERE HoChieu.MaHoChieu = LoKhoan.MaHoChieu And BanVe.ID= HoChieu.MaBanVe and HoChieu.MaHoChieu = \'" + _maHoChieu + "\';";
            SQLiteDataReader dr = conn.GetData(sql);
            while (dr.Read())
            {
                hoChieu.TenHoChieu = dr["TenHoChieu"].ToString();
                hoChieu.ChieuCaoTang = (dr["ChieuCaoTang"].ToString() == String.Empty ? 0 : Convert.ToDouble(dr["ChieuCaoTang"].ToString()));
                hoChieu.HeSoKC = (dr["HeSoKC"].ToString() == String.Empty ? 0 : Convert.ToDouble(dr["HeSoKC"].ToString()));
                hoChieu.KichThuoc_X = (dr["KichThuoc_X"].ToString() == String.Empty ? 0 : Convert.ToDouble(dr["KichThuoc_X"].ToString()));
                hoChieu.KichThuoc_Y = (dr["KichThuoc_Y"].ToString() == String.Empty ? 0 : Convert.ToDouble(dr["KichThuoc_Y"].ToString()));

                //thoiDiemNo = Convert.ToDateTime(dr["ThoiDiemNo"].ToString());

                hoChieu.MaDatDa = dr["MaDatDa"].ToString();
                hoChieu.MaCapDo = dr["MaCapDo"].ToString();
                hoChieu.MaMayKhoan = dr["MaMayKhoan"].ToString();
                hoChieu.MaThuocNo = dr["MaThuocNo"].ToString();
                hoChieu.MaCongTruong = dr["MaCongTruong"].ToString();
                danhSachLoKhoan.Add(new LoKhoan(dr["MaLK"].ToString(), Convert.ToDouble(dr["BanKinhLK"].ToString()), Convert.ToDouble(dr["ChieuSauLK"].ToString())));
            }
            dr.Close();

            //Lấy hết giá trị của bảng Đất Đá 
            datDa = BS_DatDa.DatDa(hoChieu.MaDatDa);

            //Lấy hết giá trị từ bảng Cấp độ nổ
            capDoNo = BS_CapDoNo.CapDoNo(hoChieu.MaCapDo);

            //Lấy hết giá trị của bảng Máy khoan 
            mayKhoan = BS_MayKhoan.MayKhoan(hoChieu.MaMayKhoan);

            //Lấy hết giá trị của bảng Thuốc nổ
            thuocNo = BS_ThuocNo.ThuocNo(hoChieu.MaThuocNo);

            //Lấy hết giá trị của bảng Công trường
            congTruong = BS_CongTruong.CongTruong(hoChieu.MaCongTruong);
           
            //tính toán
            congTB = Math.Round((thuocNo.KhaNangSinhCong / 360), 2);   // Cong trung binh (e)
                                                                       //double congtb = thuocNo.KhaNangSinhCong / 360; // Cong trung binh (e)
                                                                       //congtb = Math.Round(congtb * 100) / 100;
                                                                       //duong Khang chan tang
                                                                       // duongKhang = Math.Round((53 * datDa.KCTB_KhoiNut * (mayKhoan.DuongKinhLoKhoan / 1000) * Math.Sqrt(thuocNo.MatDoNapThuoc * congTB / datDa.DungTrong) * (1.6 - 0.5 * hoChieu.HeSoKC)) * 100) / 100;
            duongKhang = Math.Round((53 * datDa.HeSoDatDa * (mayKhoan.DuongKinhLoKhoan / 1000) * Math.Sqrt((thuocNo.MatDoNapThuoc * (congTB / datDa.DungTrong))) * (1.6 - 0.5 * hoChieu.HeSoKC)), 2);
            //Khoang cach giua cac lo min  (Kí Hiệu {A})
            // KC_Cot = Math.Round((hoChieu.HeSoKC * duongKhang) * 100) / 100;
            KC_Cot = Math.Round((hoChieu.HeSoKC * duongKhang), 2);
            // Khoảng cách giữa các hàng  (Kí Hiệu {b})
            // KC_Hang = Math.Round((0.85 * KC_Cot) * 100) / 100;
            KC_Hang = Math.Round((0.85 * KC_Cot), 2);
            //Chieu sau khoan them
            chieuSauThem = Math.Round((0.3 * KC_Hang), 2);
            // Chieu dai lo khoan (Kí Hiệu {Lk})
            chieuSauToanBoLK = Math.Round((mayKhoan.ChieuSau + chieuSauThem), 2);
            // Chieu cao cột bua thiết kế ( Kí Hiệu {Lbua})
            chieuDaiBua = Math.Round((0.75 * duongKhang), 2);
            //Hang min
            //hangMin = Convert.ToInt32(hoChieu.KichThuoc_Y / KC_Hang);
            //Hangmin = Math.Round(Hangmin * 100) / 100;
            //Cot min
            //soCotMin = Convert.ToInt32((hoChieu.KichThuoc_X - duongKhang) / KC_Cot);
            // Tổng số lỗ mìn    (Kí Hiệu {Tổng số lỗ})
            //tongLoMin = soCotMin * hangMin;
        }
 /// <summary>
 /// Hàm tạo bản vẽ (không có template)
 /// </summary>
 /// <param name="tenBanVe">Tên bản vẽ</param>
 /// <returns>ID bản vẽ vừa tạo</returns>
 public static String TaoBanVe(String tenBanVe)
 {
     Connection conn = new Connection();
     SQLiteDataReader dr;
     BanVe banVeVuaTao = new BanVe();
     bool flag = true;
     //kiểm tra tên bản vẽ có trùng ko
     List<BanVe> danhSachBanVe = BS_BanVe.DanhSachBanVe();
     foreach (BanVe value in danhSachBanVe)
     {
         if (value.TenBanVe == tenBanVe)
         {
             flag = false; break;
         }
     }
     //nếu ko trùng => tạo bản vẽ mới, trả về id bản vẽ vừa tạo
     if (flag)
     {
         String sql = "INSERT INTO BanVe (TenBanVe, NgayChinhSua) VALUES ('" + tenBanVe + "','" + DateTime.Now + "')";
         conn.SetData(sql);
         sql = "SELECT * FROM BanVe WHERE TenBanVe = '" + tenBanVe + "'";
         dr = conn.GetData(sql);
         while (dr.Read())
         {
             banVeVuaTao = 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 banVeVuaTao.ID;
     }
     //nếu trùng => trả về null
     else
     {
         return null;
     }
 }