public static DataTable getDataTable(string sql, int FirstRow, int pageSize)
{
GanMoiDataContext db = new GanMoiDataContext();
try
{
if (db.Connection.State == ConnectionState.Open)
{
db.Connection.Close();
}
db.Connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, db.Connection.ConnectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset, FirstRow, pageSize, "TABLE");
db.Connection.Close();
return(dataset.Tables[0]);
}
catch (Exception ex)
{
}
finally
{
db.Connection.Close();
}
return(null);
}
public static int TotalTimDonKH(string shs, string hoten, string diachi, string dienthoai)
{
string sql = "SELECT COUNT(*) ";
sql += " FROM QUAN q,PHUONG p,DON_KHACHHANG biennhan, LOAI_HOSO lhs ";
sql += " WHERE biennhan.QUAN = q.MAQUAN AND q.MAQUAN=p.MAQUAN AND biennhan.PHUONG=p.MAPHUONG AND lhs.MALOAI=biennhan.LOAIHOSO";
if (!"".Equals(shs))
{
sql += " AND biennhan.SHS = '" + shs + "'";
}
if (!"".Equals(hoten))
{
sql += " AND HOTEN LIKE N'%" + hoten + "%'";
}
if (!"".Equals(dienthoai))
{
sql += " AND DIENTHOAI LIKE N'%" + dienthoai + "%'";
}
if (!"".Equals(diachi))
{
sql += " AND replace(( SONHA +' '+DUONG+', P.'+ p.TENPHUONG+', Q.'+q.TENQUAN),' ','') LIKE N'%" + diachi.Replace(" ", "") + "%'";
}
GanMoiDataContext db = new GanMoiDataContext();
SqlConnection conn = new SqlConnection(db.Connection.ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
int result = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
return(result);
}
public static DataTable getListDuong(string tenduong, string maphuong, string maquan, int FirstRow, int pageSize)
{
GanMoiDataContext db = new GanMoiDataContext();
db.Connection.Open();
string sql = " SELECT STT, DUONG, TENPHUONG, TENQUAN ";
sql += " FROM QUAN q, PHUONG p, TENDUONG d ";
sql += " WHERE d.MAPHUONG=p.MAPHUONG AND p.MAQUAN=q.MAQUAN AND d.MAQUAN=q.MAQUAN";
if ("".Equals(tenduong) == false)
{
sql += " AND DUONG LIKE N'%" + tenduong + "%'";
}
if ("".Equals(maphuong) == false)
{
sql += " AND p.TENPHUONG LIKE N'%" + maphuong + "%'";
}
if ("".Equals(maquan) == false)
{
sql += " AND q.MAQUAN = '" + maquan.Trim() + "'";
}
sql += " ORDER BY TENPHUONG ASC ";
SqlDataAdapter adapter = new SqlDataAdapter(sql, db.Connection.ConnectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset, FirstRow, pageSize, "TABLE");
db.Connection.Close();
return(dataset.Tables[0]);
}
public static DataTable TimBienNhan(string shs, string hoten, string diachi, int FirstRow, int pageSize, string dienthoai)
{
string sql = "SELECT biennhan.SHS, biennhan.HOTEN,( SONHA +' '+DUONG+', P.'+ p.TENPHUONG+', Q.'+q.TENQUAN) as 'DIACHI', DIENTHOAI ,CONVERT(VARCHAR(20),biennhan.NGAYNHAN,103) AS 'NGAYNHAN',lhs.TENLOAI as 'LOAIHS' ";
sql += " FROM QUAN q,PHUONG p,BIENNHANDON biennhan, LOAI_HOSO lhs ";
sql += " WHERE biennhan.QUAN = q.MAQUAN AND q.MAQUAN=p.MAQUAN AND biennhan.PHUONG=p.MAPHUONG AND lhs.MALOAI=biennhan.LOAIDON";
if (!"".Equals(shs))
{
sql += " AND biennhan.SHS = '" + shs + "'";
}
if (!"".Equals(hoten))
{
sql += " AND HOTEN LIKE N'%" + hoten + "%'";
}
if (!"".Equals(dienthoai))
{
sql += " AND DIENTHOAI LIKE N'%" + dienthoai + "%'";
}
if (!"".Equals(diachi))
{
sql += " AND replace(( SONHA +' '+DUONG+', P.'+ p.TENPHUONG+', Q.'+q.TENQUAN),' ','') LIKE N'%" + diachi.Replace(" ", "") + "%'";
}
GanMoiDataContext db = new GanMoiDataContext();
db.Connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, db.Connection.ConnectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset, FirstRow, pageSize, "TABLE");
db.Connection.Close();
return(dataset.Tables[0]);
}
public static TENDUONG findbyDuong(string tenduong, string maphuong, int maquan)
{
GanMoiDataContext db = new GanMoiDataContext();
var query = from duong in db.TENDUONGs where duong.DUONG == tenduong && duong.MAPHUONG == maphuong && duong.MAQUAN == maquan select duong;
return(query.SingleOrDefault());
}
public static List <PHUONG> getListPhuongAdmin()
{
GanMoiDataContext data = new GanMoiDataContext();
var lisPhuong = from phuong in data.PHUONGs select phuong;
return(lisPhuong.ToList());
}
public static QUAN finbyTenQuan(string tenquan)
{
GanMoiDataContext data = new GanMoiDataContext();
var quan = from q in data.QUANs where q.TENQUAN == tenquan select q;
return(quan.SingleOrDefault());
}
public static List <QUAN> getListQuan()
{
GanMoiDataContext data = new GanMoiDataContext();
var quan = from p in data.QUANs select p;
return(quan.ToList());
}
public static QUAN finByMaQuan(int maquan)
{
GanMoiDataContext data = new GanMoiDataContext();
var quan = from q in data.QUANs where q.MAQUAN == maquan select q;
return(quan.SingleOrDefault());
}
public static PHUONG finbyTenPhuong(int maquan, string tenPhuong)
{
GanMoiDataContext data = new GanMoiDataContext();
var phuong = from p in data.PHUONGs where p.MAQUAN == maquan && p.TENPHUONG == tenPhuong select p;
return(phuong.SingleOrDefault());
}
public static List <PHUONG> ListPhuongByTenPhuong(string tenPhuong)
{
GanMoiDataContext data = new GanMoiDataContext();
var lisPhuong = from phuong in data.PHUONGs where phuong.TENPHUONG == tenPhuong select phuong;
return(lisPhuong.ToList());
}
public static int TotalListDuong(string tenduong, string maphuong, string maquan)
{
GanMoiDataContext db = new GanMoiDataContext();
SqlConnection conn = new SqlConnection(db.Connection.ConnectionString);
conn.Open();
string sql = " SELECT COUNT(*) ";
sql += " FROM QUAN q, PHUONG p, TENDUONG d ";
sql += " WHERE d.MAPHUONG=p.MAPHUONG AND p.MAQUAN=q.MAQUAN AND d.MAQUAN=q.MAQUAN";
if ("".Equals(tenduong) == false)
{
sql += " AND DUONG LIKE N'%" + tenduong + "%'";
}
if ("".Equals(maphuong) == false)
{
sql += " AND p.TENPHUONG LIKE N'%" + maphuong + "%'";
}
if ("".Equals(maquan) == false)
{
sql += " AND q.MAQUAN ='" + maquan.Trim() + "'";
}
SqlCommand cmd = new SqlCommand(sql, conn);
int result = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
return(result);
}
public static List <QUAN> ListQuan()
{
GanMoiDataContext db = new GanMoiDataContext();
var data = from phuong in db.QUANs select phuong;
return(data.ToList());;
}
public static List <PHUONG> ListPhuong(int quan)
{
GanMoiDataContext db = new GanMoiDataContext();
var data = from phuong in db.PHUONGs where phuong.MAQUAN == quan select phuong;
return(data.ToList());;
}
public static List <PHUONG> getListByQuan(int maquan)
{
GanMoiDataContext data = new GanMoiDataContext();
var lisPhuong = from phuong in data.PHUONGs where phuong.MAQUAN == maquan select phuong;
return(lisPhuong.ToList());
}
public static List <TENDUONG> getList()
{
GanMoiDataContext db = new GanMoiDataContext();
var query = from duong in db.TENDUONGs select duong;
return(query.ToList());
}
public static ArrayList getListPhuong(int quan)
{
ArrayList list = new ArrayList();
GanMoiDataContext data = new GanMoiDataContext();
var lisPhuong = from phuong in data.PHUONGs where phuong.MAQUAN == quan select phuong;
foreach (var a in lisPhuong)
{
list.Add(new AddValueCombox(a.TENPHUONG, a.MAPHUONG));
}
return(list);
}
public static ArrayList getListQUAN()
{
ArrayList list = new ArrayList();
GanMoiDataContext data = new GanMoiDataContext();
var lisPhuong = from phuong in data.QUANs select phuong;
foreach (var a in lisPhuong)
{
list.Add(new AddValueCombox(a.TENQUAN, a.MAQUAN + ""));
}
return(list);
}
public static ArrayList getPhuong()
{
GanMoiDataContext db = new GanMoiDataContext();
var data = from phuong in db.PHUONGs select phuong;
ArrayList list = new ArrayList();
list.Add(new AddValueCombox(" Chọn Phường ", ""));
foreach (var a in data)
{
list.Add(new AddValueCombox(a.TENPHUONG, a.MAPHUONG));
}
return(list);
}
public static ArrayList getQuan()
{
GanMoiDataContext db = new GanMoiDataContext();
var data = from quan in db.QUANs select quan;
ArrayList list = new ArrayList();
list.Add(new AddValueCombox(" Chọn Quận ", ""));
foreach (var a in data)
{
list.Add(new AddValueCombox(a.TENQUAN, a.MAQUAN + ""));
}
return(list);
}
public static bool InsertDuong(TENDUONG duong)
{
try
{
GanMoiDataContext db = new GanMoiDataContext();
db.TENDUONGs.InsertOnSubmit(duong);
db.SubmitChanges();
return(true);
}
catch (Exception)
{
}
return(false);
}
public static DataTable getQuanPhuong(string tenduong)
{
GanMoiDataContext db = new GanMoiDataContext();
db.Connection.Open();
string sql = " SELECT DISTINCT p.TENPHUONG, q.TENQUAN ";
sql += " FROM TENDUONG d, PHUONG p, QUAN q ";
sql += " WHERE d.MAPHUONG = p.MAPHUONG AND d.MAQUAN=q.MAQUAN AND q.MAQUAN =p.MAQUAN";
sql += " AND replace(d.DUONG,' ','')=N'" + tenduong.Replace(" ", "") + "'";
SqlDataAdapter adapter = new SqlDataAdapter(sql, db.Connection.ConnectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset, "TABLE");
db.Connection.Close();
return(dataset.Tables[0]);
}
public static BIENNHANDON searchBienNhan(string sohoso)
{
try
{
GanMoiDataContext db = new GanMoiDataContext();
var data = from don in db.BIENNHANDONs where don.SHS == sohoso select don;
BIENNHANDON donkh = data.SingleOrDefault();
//if (donkh.HOSOCHA != null)
//{
// var hosocha = from don in db.DON_KHACHHANGs where don.SHS == donkh.HOSOCHA select don;
// return hosocha.SingleOrDefault();
//}
return(donkh);
}
catch (Exception ex)
{
}
return(null);
}
public static bool Delete(int id)
{
GanMoiDataContext db = new GanMoiDataContext();
var query = from duong in db.TENDUONGs where duong.STT == id select duong;
TENDUONG tDuong = query.SingleOrDefault();
if (tDuong != null)
{
try
{
db.TENDUONGs.DeleteOnSubmit(tDuong);
db.SubmitChanges();
return(true);
}
catch (Exception ex)
{
}
}
return(false);
}
public static bool UpdateDuong(int id, string tenduong, string maphuong, int maquan)
{
GanMoiDataContext db = new GanMoiDataContext();
var query = from duong in db.TENDUONGs where duong.STT == id select duong;
TENDUONG tDuong = query.SingleOrDefault();
if (tDuong != null)
{
try
{
tDuong.DUONG = tenduong;
tDuong.MAPHUONG = maphuong;
tDuong.MAQUAN = maquan;
db.SubmitChanges();
return(true);
}
catch (Exception)
{
}
}
return(false);
}
public static DataTable getDataTable(string sql)
{
DataTable table = new DataTable();
GanMoiDataContext db = new GanMoiDataContext();
try
{
if (db.Connection.State == ConnectionState.Open)
{
db.Connection.Close();
}
db.Connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, db.Connection.ConnectionString);
adapter.Fill(table);
}
catch (Exception ex)
{
}
finally
{
db.Connection.Close();
}
return(table);
}
