public bool Update(string idSuCo, string maVatTu, int soLuong)
{
try
{
using (var context = new GISEntities())
{
var baseModel = context.HOSOVATTUSUCOes.FirstOrDefault(f => f.IDSuCo.Equals(idSuCo) && f.MaVatTu.Equals(maVatTu));
if (baseModel == null)
{
throw new NullReferenceException("Không tìm thấy đối tượng trong cơ sở dữ liệu");
}
else
{
var sql = String.Format("UPDATE sde.HOSOVATTUSUCO SET SoLuong = @soLuong WHERE IDSuCo = @idSuCo AND MaVatTu = @maVatTu");
return(context.Database.ExecuteSqlCommand(sql,
new SqlParameter("@soLuong", soLuong),
new SqlParameter("@idSuCo", idSuCo),
new SqlParameter("@maVatTu", maVatTu)
) > 0);
}
}
}
catch (Exception e)
{
throw e;
}
}
public THONGTINDOAN TimKiemThongTinDoAn(int objectID)
{
using (var context = new GISEntities())
{
var shape = context.QHCT_SUDUNGDAT.Where(f => f.OBJECTID == objectID).Select(s => s.SHAPE).First();
var thongTinDoAn = context.QHCT_THONGTINDOAN.FirstOrDefault(f => f.SHAPE.Contains(shape));
return(new THONGTINDOAN
{
OBJECTID = thongTinDoAn.OBJECTID,
MaDoAn = thongTinDoAn.MaDoAn,
TenDoAn = thongTinDoAn.TenDoAn,
DiaDiem = thongTinDoAn.DiaDiem,
DienTich = thongTinDoAn.DienTich,
NgayCapNhat = thongTinDoAn.NgayCapNhat,
NguoiCapNhat = thongTinDoAn.NguoiCapNhat,
DonViCapNhat = thongTinDoAn.DonViCapNhat,
DonViQuanLy = thongTinDoAn.DonViQuanLy,
GhiChu = thongTinDoAn.GhiChu,
KiHieuKhuVuc = thongTinDoAn.KiHieuKhuVuc,
ChuDauTu = thongTinDoAn.ChuDauTu,
SoQuyetDinhPheDuyet = thongTinDoAn.SoQuyetDinhPheDuyet,
NgayPheDuyet = thongTinDoAn.NgayPheDuyet,
CoQuanPheDuyet = thongTinDoAn.CoQuanPheDuyet,
MaQuanHuyen = thongTinDoAn.MaQuanHuyen,
MaPhuongXa = thongTinDoAn.MaPhuongXa,
LoaiQuyHoach = thongTinDoAn.LoaiQuyHoach,
TrangThaiDoAn = thongTinDoAn.TrangThaiDoAn
});
}
}
public SUCO Get(string id)
{
using (var context = new GISEntities())
{
return(context.SUCOes.FirstOrDefault(f => f.IDSuCo.Equals(id)));
}
}
public List <QHC_THONGTINDOANDTO> TimKiem_QHC(string maQuanHuyen, string maPhuongXa, string tenDoAn)
{
using (var context = new GISEntities())
{
var query = context.THONGTINDOANs.Where(w =>
(String.IsNullOrEmpty(maQuanHuyen) || w.MaQuanHuyen.Equals(maQuanHuyen)) &&
(String.IsNullOrEmpty(maPhuongXa) || w.MaPhuongXa.Equals(maPhuongXa)) &&
(String.IsNullOrEmpty(tenDoAn) || w.TenDoAn.Equals(tenDoAn))
)
.Select(s => new QHC_THONGTINDOANDTO
{
OBJECTID = s.OBJECTID,
MaDoAn = s.MaDoAn,
TenDoAn = s.TenDoAn,
DiaDiem = s.DiaDiem,
DienTich = s.DienTich,
NgayCapNhat = s.NgayCapNhat,
NguoiCapNhat = s.NguoiCapNhat,
DonViCapNhat = s.DonViCapNhat,
DonViQuanLy = s.DonViQuanLy,
GhiChu = s.GhiChu,
KiHieuKhuVuc = s.KiHieuKhuVuc,
ChuDauTu = s.ChuDauTu,
SoQuyetDinhPheDuyet = s.SoQuyetDinhPheDuyet,
NgayPheDuyet = s.NgayPheDuyet,
CoQuanPheDuyet = s.CoQuanPheDuyet,
MaQuanHuyen = s.MaQuanHuyen,
MaPhuongXa = s.MaPhuongXa,
LoaiQuyHoach = s.LoaiQuyHoach,
TrangThaiDoAn = s.TrangThaiDoAn
});
return(query.ToList());
}
}
/// <summary>
/// The GetAll
/// </summary>
/// <returns>The <see cref="List{QHPK_SUDUNGDAT}"/></returns>
public List <QHPK_SUDUNGDAT> GetAll()
{
using (var context = new GISEntities())
{
return(context.QHPK_SUDUNGDAT.ToList());
}
}
public List <HoSoDoAn> GetAll()
{
using (var context = new GISEntities())
{
return(context.HoSoDoAns.ToList());
}
}
/// <summary>
/// The GetAll
/// </summary>
/// <returns>The <see cref="List{THONGTINDOAN}"/></returns>
public List <THONGTINDOAN> GetAll()
{
using (var context = new GISEntities())
{
return(context.THONGTINDOANs.ToList());
}
}
/// <summary>
/// Thực hiện cập nhật tiếp nhận sự cố
/// </summary>
/// <param name="objectId">Objectid của đối tượng sự cố</param>
/// <returns>IDSuCo</returns>
public String HintNhomKhacPhuc(string pNhomKhacPhuc)
{
using (var context = new GISEntities())
{
var result = context.SUCOes.FirstOrDefault(f => !String.IsNullOrEmpty(f.NhomKhacPhuc) && f.NhomKhacPhuc.StartsWith(pNhomKhacPhuc));
return(result != null ? result.NhomKhacPhuc : "");
}
}
public List <String> GetDistinctNhomKhacPhuc()
{
using (var context = new GISEntities())
{
var result = context.SUCOes.Where(w => !String.IsNullOrEmpty(w.NhomKhacPhuc)).Select(s => s.NhomKhacPhuc).Distinct().ToList();
return(result);
}
}
/// <summary>
/// The TimKiem
/// </summary>
/// <param name="maQuanHuyen">The maQuanHuyen<see cref="string"/></param>
/// <param name="maPhuongXa">The maPhuongXa<see cref="string"/></param>
/// <param name="loaiQuyHoach">The loaiQuyHoach<see cref="string"/></param>
/// <param name="tenDoAn">The tenDoAn<see cref="string"/></param>
/// <returns>The <see cref="List{ThongTinDoAnDTO}"/></returns>
public List <ThongTinDoAnDTO> TimKiem(string maQuanHuyen, string maPhuongXa, string loaiQuyHoach, string tenDoAn)
{
using (var context = new GISEntities())
{
if (loaiQuyHoach == "QHCT")
{
List <ThongTinDoAnDTO> result = this.TimKiem_QHCT(maQuanHuyen, maPhuongXa, tenDoAn);
return(result);
}
else if (loaiQuyHoach == "QHPK")
{
List <ThongTinDoAnDTO> result = this.TimKiem_QHPK(maQuanHuyen, maPhuongXa, tenDoAn);
return(result);
}
else if (loaiQuyHoach == "QHC")
{
List <ThongTinDoAnDTO> result = this.TimKiem_QHC(maQuanHuyen, maPhuongXa, tenDoAn);
return(result);
}
else if (loaiQuyHoach == "QHNT")
{
List <ThongTinDoAnDTO> result = this.TimKiem_QHNT(maQuanHuyen, maPhuongXa, tenDoAn);
return(result);
}
else if (loaiQuyHoach == "KHAC")
{
List <ThongTinDoAnDTO> result = this.TimKiem_Khac(maQuanHuyen, maPhuongXa, tenDoAn);
return(result);
}
else if (loaiQuyHoach == "QHV")
{
List <ThongTinDoAnDTO> result = this.TimKiem_QHV(maQuanHuyen, maPhuongXa, tenDoAn);
return(result);
}
else
{
List <ThongTinDoAnDTO> chung_ThongTinDoAn = new List <ThongTinDoAnDTO>();
List <ThongTinDoAnDTO> khac_ThongTinDoAn = TimKiem_Khac(maQuanHuyen, maPhuongXa, tenDoAn);
List <ThongTinDoAnDTO> qhc_ThongTinDoAn = TimKiem_QHC(maQuanHuyen, maPhuongXa, tenDoAn);
List <ThongTinDoAnDTO> hqct_ThongTinDoAn = TimKiem_QHCT(maQuanHuyen, maPhuongXa, tenDoAn);
List <ThongTinDoAnDTO> qhpk_ThongTinDoAn = TimKiem_QHPK(maQuanHuyen, maPhuongXa, tenDoAn);
List <ThongTinDoAnDTO> qhnt_ThongTinDoAn = TimKiem_QHNT(maQuanHuyen, maPhuongXa, tenDoAn);
List <ThongTinDoAnDTO> qhv_ThongTinDoAn = TimKiem_QHV(maQuanHuyen, maPhuongXa, tenDoAn);
chung_ThongTinDoAn.AddRange(khac_ThongTinDoAn);
chung_ThongTinDoAn.AddRange(qhc_ThongTinDoAn);
chung_ThongTinDoAn.AddRange(hqct_ThongTinDoAn);
chung_ThongTinDoAn.AddRange(qhpk_ThongTinDoAn);
chung_ThongTinDoAn.AddRange(qhnt_ThongTinDoAn);
chung_ThongTinDoAn.AddRange(qhv_ThongTinDoAn);
return(chung_ThongTinDoAn);
}
}
}
/// <summary>
/// The TimKiem
/// </summary>
/// <param name="maQuanHuyen">The maQuanHuyen<see cref="string"/></param>
/// <param name="maPhuongXa">The maPhuongXa<see cref="string"/></param>
/// <param name="loaiDat">The loaiDat<see cref="string"/></param>
/// <param name="kiHieuLoDat">The kiHieuLoDat<see cref="string"/></param>
/// <param name="dienTichTu">The dienTichTu<see cref="int"/></param>
/// <param name="dientichDen">The dientichDen<see cref="int"/></param>
/// <param name="kcTu">The kcTu<see cref="int"/></param>
/// <param name="kcDen">The kcDen<see cref="int"/></param>
/// <param name="soVoi">The soVoi<see cref="string"/></param>
/// <returns>The <see cref="List{QHPK_SUDUNGDAT}"/></returns>
public List <ThongTinQHPKDTO> TimKiem(string maQuanHuyen, string maPhuongXa, string loaiDat, string kiHieuLoDat, decimal?dienTichTu, decimal?dientichDen,
decimal?kcTu, decimal?kcDen, string soVoi)
{
using (var context = new GISEntities())
{
var query = context.QHPK_SUDUNGDAT.Where(w =>
(String.IsNullOrEmpty(maQuanHuyen) || w.MaQuanHuyen.Equals(maQuanHuyen)) &&
(String.IsNullOrEmpty(maPhuongXa) || w.MaPhuongXa.Equals(maPhuongXa)) &&
(String.IsNullOrEmpty(loaiDat) || w.LoaiDat.Equals(loaiDat)) &&
(String.IsNullOrEmpty(kiHieuLoDat) || w.MaQuanHuyen.Equals(kiHieuLoDat)) &&
(!dienTichTu.HasValue || (w.DienTich.HasValue && w.DienTich >= dienTichTu.Value)) &&
(!dientichDen.HasValue || (w.DienTich.HasValue && w.DienTich <= dientichDen.Value)) &&
(!kcTu.HasValue || ((soVoi.Equals("sbDaNang") && w.KhoangCach_sanbaydanang >= kcTu.Value)) ||
(soVoi.Equals("sbChuLai") && (w.KhoangCach_sanbaychulai.HasValue && w.KhoangCach_sanbaychulai >= kcTu.Value)) ||
(soVoi.Equals("cangKyHa") && (w.KhoangCach_cangkyha.HasValue && w.KhoangCach_cangkyha >= kcTu.Value)) ||
(soVoi.Equals("tpHoiAn") && (w.KhoangCach_tphoian.HasValue && w.KhoangCach_tphoian >= kcTu.Value)) ||
(soVoi.Equals("ckNamGiang") && (w.KhoangCach_cuakhaunamgiang.HasValue && w.KhoangCach_cuakhaunamgiang >= kcTu.Value)))
&&
(!kcDen.HasValue || ((soVoi.Equals("sbDaNang") && w.KhoangCach_sanbaydanang <= kcDen.Value)) ||
(soVoi.Equals("sbChuLai") && (w.KhoangCach_sanbaychulai.HasValue && w.KhoangCach_sanbaychulai <= kcDen.Value)) ||
(soVoi.Equals("cangKyHa") && (w.KhoangCach_cangkyha.HasValue && w.KhoangCach_cangkyha <= kcDen.Value)) ||
(soVoi.Equals("tpHoiAn") && (w.KhoangCach_tphoian.HasValue && w.KhoangCach_tphoian <= kcDen.Value)) ||
(soVoi.Equals("ckNamGiang") && (w.KhoangCach_cuakhaunamgiang.HasValue && w.KhoangCach_cuakhaunamgiang <= kcDen.Value)))
)
.Select(s => new ThongTinQHPKDTO
{
OBJECTID = s.OBJECTID,
MaSDD = s.MaSDD,
KiHieuKhuDat = s.KiHieuKhuDat,
LoaiDat = s.LoaiDat,
DienTich = s.DienTich,
MatDoXayDung = s.MatDoXayDung,
TangCao = s.TangCao,
ChiGioiXayDung = s.ChiGioiXayDung,
KhoangLuiBien = s.KhoangLuiBien,
GhiChu = s.GhiChu,
ChucNang = s.ChucNang,
GiaiDoanQuyHoach = s.GiaiDoanQuyHoach,
HeSoSuDungDat = s.HeSoSuDungDat,
KhoangLuiChinh = s.KhoangLuiChinh,
MaPhuongXa = s.MaPhuongXa,
MaQuanHuyen = s.MaQuanHuyen,
KhoangCach_sanbaydanang = s.KhoangCach_sanbaydanang,
KhoangCach_sanbaychulai = s.KhoangCach_sanbaychulai,
KhoangCach_cangkyha = s.KhoangCach_cangkyha,
KhoangCach_TpTamky = s.KhoangCach_TpTamky,
KhoangCach_tphoian = s.KhoangCach_tphoian,
KhoangCach_cuakhaunamgiang = s.KhoangCach_cuakhaunamgiang,
TenDoAn = s.TenDoAn
});
return(query.ToList());
}
}
public HANHCHINH Get(string id)
{
try
{
using (var context = new GISEntities())
{
return(context.HANHCHINHs.FirstOrDefault(f => f.IDHanhChinh.Equals(id)));
}
}
catch (Exception e)
{
throw e;
}
}
public List <WebAPI.DataProvider.Models.DMA> SelectLoggerDMA()
{
try
{
using (var context = new GISEntities())
{
return(context.Database.SqlQuery <WebAPI.DataProvider.Models.DMA>("select distinct MaDMA,TenDMA from sde.DMA where MADMA is not null").ToList());
}
}
catch (Exception e)
{
throw e;
}
}
public HOSOVATTUSUCO Get(int id)
{
try
{
using (var context = new GISEntities())
{
context.Configuration.LazyLoadingEnabled = false;
return(context.HOSOVATTUSUCOes.FirstOrDefault(f => f.RID.Equals(id)));
}
}
catch (Exception e)
{
throw e;
}
}
public IEnumerable <HOSOVATTUSUCO> GetAll()
{
try
{
using (var context = new GISEntities())
{
context.Configuration.LazyLoadingEnabled = false;
return(context.HOSOVATTUSUCOes.ToList());
}
}
catch (Exception e)
{
throw e;
}
}
public HOSOVATTUSUCO Get(string idSuCo, string maVatTu)
{
try
{
using (var context = new GISEntities())
{
context.Configuration.LazyLoadingEnabled = false;
return(context.HOSOVATTUSUCOes.FirstOrDefault(f => f.IDSuCo.Equals(idSuCo) && f.MaVatTu.Equals(maVatTu)));
}
}
catch (Exception e)
{
throw e;
}
}
public IEnumerable <VATTU> GetAll()
{
try
{
using (var context = new GISEntities())
{
return(context.VATTUs.Where(w => !String.IsNullOrEmpty(w.MaVatTu)).OrderBy(o => o.TenVatTu)
.Distinct().ToList());
}
}
catch (Exception e)
{
throw e;
}
}
public List <HoSoDoAnDTO> TimKiem(string maDoAn)
{
using (var context = new GISEntities())
{
var query = context.HoSoDoAns.Where(w => w.MaDoAn.Equals(maDoAn))
.Select(s => new HoSoDoAnDTO
{
id = s.id,
MaDoAn = s.MaDoAn,
TenHoSo = s.TenHoSo,
LoaiHoSo = s.LoaiHoSo,
DinhDangHoSo = s.DinhDangHoSo,
DuongDan = s.DuongDan
});
return(query.ToList());
}
}
public HANHCHINH GetByPoint(Point point)
{
using (var context = new GISEntities())
{
var query = Helper.Query(@"
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT ({0} {1})', 0);
select * FROM sde.HANHCHINH a where a.SHAPE.STContains(@g) = 1
");
var model = context.Database.SqlQuery <HANHCHINH>(String.Format(query, point.x, point.y)).FirstOrDefault();
if (model != null)
{
model.SHAPE = null;
}
return(model);
}
}
public bool Delete(string idSuCo, string maVatTu)
{
try
{
using (var context = new GISEntities())
{
var sql = String.Format("DELETE FROM SDE.HOSOVATTUSUCO WHERE IDSuCo = @idSuCo AND MaVatTu = @maVatTu");
return(context.Database.ExecuteSqlCommand(sql,
new SqlParameter("@idSuCo", idSuCo),
new SqlParameter("@maVatTu", maVatTu)
) > 0);
}
}
catch (Exception e)
{
throw e;
}
}
public HOSOVATTUSUCO Create(HOSOVATTUSUCO model)
{
try
{
using (var context = new GISEntities())
{
if (model != null)
{
var query = String.Format(@"
INSERT INTO SDE.HOSOVATTUSUCO
SELECT
,IDSuCo = @idSuCo
,SoLuong = @soLuong
,MaVatTu = @maVatTu
FROM SDE.HOSOVATTUSUCO
");
query = Helper.Query(query);
var result = context.Database.ExecuteSqlCommand(query,
new SqlParameter("@idSuCo", model.IDSuCo),
new SqlParameter("@soLuong", model.SoLuong),
new SqlParameter("@maVatTu", model.MaVatTu)
);
if (result > 0)
{
return(model);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
}
catch (Exception e)
{
throw e;
}
}
public ILIModel ILI(float P)
{
try
{
using (var context = new GISEntities())
{
var sanLuongDAO = new SanLuongKhachHangDAO();
var tongSanLuongDMA = this.TongSanLuongDMA();
var tongTieuThu = sanLuongDAO.TongSanLuong();
var query = context.Database.SqlQuery <ILIModel>(String.Format("exec ILI {0}, {1}, {2}", P, tongSanLuongDMA, tongTieuThu));
var resultQuery = query.DefaultIfEmpty(new ILIModel
{
CAPL = 0,
MAAPL = 0,
LM = 0,
NC = 0,
LP = 0,
P = 0,
Group = string.Empty,
Summary = string.Empty
}).SingleOrDefault();
resultQuery.Group = (resultQuery.ILI >= 1 && resultQuery.ILI <= 4) ? "A" :
(resultQuery.ILI > 4 && resultQuery.ILI <= 8) ? "B" :
(resultQuery.ILI > 8 && resultQuery.ILI <= 16) ? "C" :
resultQuery.ILI > 16 ? "D" : "Không xác định";
resultQuery.Summary =
resultQuery.Group.Equals("A") == true ? @"Giảm thất thoát nước hơn nữa có thể không hiệu quả về mặt kinh tế. Cần phân tích kỹ để xác định những cải thiện hiệu quả về mặt chi phí" :
resultQuery.Group == "B" ? @"Có tiềm năng cải thiện. Xem xét: quản lý áp lực, các biện pháp kiểm soát rò rỉ chủ động tốt hơn và bảo dưỡng tốt hơn." :
resultQuery.Group == "C" ?
@"Chỉ cho phép nếu lực lượng nước nhiều và giá rẻ. Cần củng cố các nỗ lực giảm NRW" :
resultQuery.Group == "D" ? @"Sử dụng các nguồn lực thiếu hiệu quả. Các chương trình giảm NRW là bắt buộc và ưu tiên." :
"Không có đánh giá";
return(resultQuery);
}
}
catch (Exception e)
{
throw e;
}
}
public List <SuCoTheoDMA> LayDanhSachSuCoTheoDMA(DateTime tuNgay, DateTime denNgay)
{
try
{
using (var context = new GISEntities())
{
var tuNgaySQL = tuNgay.ToString("yyyy-MM-dd");
var denNgaySQL = denNgay.ToString("yyyy-MM-dd");
var query = String.Format(@"select sc.IDSuCo,SuaBe = LuongNuocThatThoatChuQuan,DiemBe = LuongNuocThatThoatKhachQuan,dma.TenDMA
from sde.SUCO sc inner join sde.DMA dma on dma.SHAPE.STContains(sc.SHAPE) = 1
where CAST(sc.NgayXayRa AS DATE) between '{0}' AND '{1}' OR CAST(sc.NgayKhacPhuc AS DATE) between '{0}' AND '{1}'", tuNgaySQL, denNgaySQL);
query = query.Replace("\r\n", "");
query = query.Replace("\t", "");
return(context.Database.SqlQuery <SuCoTheoDMA>(query).ToList());
}
}
catch (Exception e)
{
throw e;
}
}
public List <ThongTinQHCTDTO> TimKiem(string maQuanHuyen, string maPhuongXa, string loaiDat, string kiHieuKhuDat, string kiHieuLoDat, string tenDoAn)
{
using (var context = new GISEntities())
{
var query = context.QHCT_SUDUNGDAT.Where(w =>
(String.IsNullOrEmpty(maQuanHuyen) || w.MaQuanHuyen.Equals(maQuanHuyen)) &&
(String.IsNullOrEmpty(maPhuongXa) || w.MaPhuongXa.Equals(maPhuongXa)) &&
(String.IsNullOrEmpty(loaiDat) || w.LoaiDat.Equals(loaiDat)) &&
(String.IsNullOrEmpty(kiHieuKhuDat) || w.KiHieuKhuDat.Equals(kiHieuKhuDat)) &&
(String.IsNullOrEmpty(kiHieuLoDat) || w.KiHieuLoDat.Equals(kiHieuLoDat)) &&
(String.IsNullOrEmpty(tenDoAn) || w.TenDoAn.Equals(tenDoAn))
)
.Select(s => new ThongTinQHCTDTO
{
OBJECTID = s.OBJECTID,
MaSDD = s.MaSDD,
KiHieuKhuDat = s.KiHieuKhuDat,
KiHieuLoDat = s.KiHieuLoDat,
LoaiDat = s.LoaiDat,
DienTichKhuDat = s.DienTichKhuDat,
MatDoXayDung = s.MatDoXayDung,
TangCao = s.TangCao,
ChiGioiXayDung = s.ChiGioiXayDung,
KhoangLuiBien = s.KhoangLuiBien,
GhiChu = s.GhiChu,
ChucNang = s.ChucNang,
GiaiDoanQuyHoach = s.GiaiDoanQuyHoach,
KhoangLuiChinh = s.KhoangLuiChinh,
DienTichLoDat = s.DienTichLoDat,
MaQuanHuyen = s.MaQuanHuyen,
MaPhuongXa = s.MaPhuongXa,
TenDoAn = s.TenDoAn,
HeSoSuDungDat = s.HeSoSuDungDat
});
return(query.ToList());
}
}
public bool Delete(int id)
{
try
{
using (var context = new GISEntities())
{
var model = context.HOSOVATTUSUCOes.FirstOrDefault(f => f.RID.Equals(id));
if (model != null)
{
context.HOSOVATTUSUCOes.Remove(model);
return(context.SaveChanges() > 0);
}
else
{
return(false);
}
}
}
catch (Exception e)
{
throw e;
}
}
public List <VatTuSuCo> SelectMaterialsBySuCo(string idSuCo)
{
try
{
using (var context = new GISEntities())
{
var query = from hsvt in context.HOSOVATTUSUCOes
join vt in context.VATTUs on hsvt.MaVatTu equals vt.MaVatTu
where hsvt.IDSuCo == idSuCo
select new VatTuSuCo
{
MaVatTu = vt.MaVatTu,
TenVatTu = vt.TenVatTu,
SoLuong = hsvt.SoLuong,
DonViTinh = vt.DonViTinh,
};
return(query.ToList());
}
}
catch (Exception e)
{
throw e;
}
}
public string LayMaDoiTuong(string layerID, string maNhaMay)
{
using (var context = new GISEntities())
{
var shortName = String.Empty;
var tableName = String.Empty;
switch (layerID)
{
case "baixiLYR":
shortName = "BXI";
tableName = "BAIXI";
break;
case "bangtaiLYR":
shortName = "BTI";
tableName = "BANGTAI";
break;
case "bechuaLYR":
shortName = "BCA";
tableName = "BECHUA";
break;
case "cameraLYR":
shortName = "CAA";
tableName = "CAMERA";
break;
case "cangLYR":
shortName = "CAG";
tableName = "CANG";
break;
case "congtrinhLYR":
shortName = "CTH";
tableName = "CONGTRINH";
break;
case "cotdienLYR":
shortName = "CDN";
tableName = "COTDIEN";
break;
case "cualaynuocLYR":
shortName = "CLN";
tableName = "CUALAYNUOC";
break;
case "dapdangLYR":
shortName = "DDG";
tableName = "DAPDANG";
break;
case "daptranxaluLYR":
shortName = "DTX";
tableName = "DAPTRANXALU";
break;
case "duongdaydienLYR":
shortName = "DDD";
tableName = "DUONGDAYDIEN";
break;
case "duongongLYR":
shortName = "DOG";
tableName = "DUONGONG";
break;
case "giaothongLYR":
shortName = "GTG";
tableName = "GIAOTHONG";
break;
case "hochuanuocngotLYR":
shortName = "HCN";
tableName = "HOCHUANUOCNGOT";
break;
case "hogaLYR":
shortName = "HGA";
tableName = "HOGA";
break;
case "kenhdanLYR":
shortName = "KDN";
tableName = "KENHDAN";
break;
case "kenhnuoclammatLYR":
shortName = "KNL";
tableName = "KENHNUOCLAMMAT";
break;
case "luuvucLYR":
shortName = "LVC"; tableName = "LUUVUC";
break;
case "nhamaydienLYR":
shortName = "NMD"; tableName = "NHAMAYDIEN";
break;
case "songhoLYR":
shortName = "SHO"; tableName = "SONGHO";
break;
case "thapdieuapLYR":
shortName = "TDA"; tableName = "THAPDIEUAP";
break;
case "tomaynhietdienLYR":
shortName = "TMN"; tableName = "TOMAYNHIETDIEN";
break;
case "trambienapLYR":
shortName = "TBA"; tableName = "TRAMBIENAP";
break;
case "trambomLYR":
shortName = "TBM"; tableName = "TRAMBOM";
break;
case "trusocongtyLYR":
shortName = "TSC";
break;
case "tuyenduongthaixiLYR":
shortName = "TDT"; tableName = "TUYENDUONGTHAIXI";
break;
case "tuyennangluongLYR":
shortName = "TNL"; tableName = "TUYENNANGLUONG";
break;
}
if (!String.IsNullOrEmpty(shortName))
{
var sql = String.Format("select top 1 MaNhaMay+'/'+'{0}'+REPLACE(STR(count(MaNhaMay) + 1,4), SPACE(1), '0') from {1} where MaNhaMay = @maNhaMay group by MaNhaMay order by MaNhaMay desc",
shortName, tableName, maNhaMay
);
var maDoiTuong = context.Database.SqlQuery <string>(sql, new SqlParameter("maNhaMay", maNhaMay))
.DefaultIfEmpty(String.Format("{0}/{1}0001", maNhaMay, shortName))
.FirstOrDefault();
return(maDoiTuong);
}
else
{
return(null);
}
}
return(null);
}
public List <TBKhachHangMatNuoc> CustomersByDMA(string dma, DateTime from, DateTime to)
{
try
{
using (var context = new GISEntities())
{
//var result = from dt in context.TBKHACHHANGMATNUOCs
// join hc in context.HANHCHINHs on dt.MAPHUONG equals hc.IDHanhChinh
// //join dhkh in context.DONGHOKHACHHANGs on dt.DBDONGHONUOC equals dhkh.DBDONGHONUOC
// where
// !String.IsNullOrEmpty(dt.DBDONGHONUOC)
// && dt.DBDONGHONUOC != " "
// && dt.TENDMA == dma
// && dt.TuNgay.HasValue && dt.DenNgay.HasValue
// && dt.TuNgay.Value >= @from
// && dt.DenNgay.Value <= to
// select new TBKhachHangMatNuoc
// {
// MAPHUONG = hc.TenHanhChinh,
// MAQUAN = hc.TenQuan,
// CODONGHO = dt.CODONGHO,
// DBDONGHONUOC = dt.DBDONGHONUOC,
// MADUONG = dt.MADUONG,
// NGAYCAPNHAT = dt.NGAYCAPNHAT,
// OBJECTID = dt.OBJECTID,
// SODIENTHOAI = dt.SODIENTHOAI,
// SONHA = dt.SONHA,
// TENDMA = dt.TENDMA,
// TENDUONG = dt.TENDUONG,
// TENTHUEBAO = dt.TENTHUEBAO,
// TuNgay = dt.TuNgay,
// DenNgay = dt.DenNgay
// //Geometry = new Point
// //{
// // x = dhkh.SHAPE.XCoordinate.Value,
// // y = dhkh.SHAPE.YCoordinate.Value,
// //}
// };
var query = String.Format(@"SELECT
MAPHUONG = HC.TenHanhChinh,
MAQUAN = HC.TenQuan,
CODONGHO = KH.CODONGHO,
DBDONGHONUOC = KH.DBDONGHONUOC,
MADUONG = KH.MADUONG,
NGAYCAPNHAT = KH.NGAYCAPNHAT,
OBJECTID = KH.OBJECTID,
SODIENTHOAI = KH.SODIENTHOAI,
SONHA = KH.SONHA,
TENDMA = KH.TENDMA,
TENDUONG = KH.TENDUONG,
TENTHUEBAO = KH.TENTHUEBAO,
TuNgay = KH.TuNgay,DenNgay = KH.DenNgay
FROM sde.TBKHACHHANGMATNUOC KH
INNER JOIN sde.HANHCHINH HC ON KH.MAPHUONG = HC.IDHanhChinh
WHERE KH.TENDMA= '{0}' AND CAST(KH.NGAYCAPNHAT AS DATE) BETWEEN '{1}' AND '{2}'
AND KH.DBDONGHONUOC is not null and KH.DBDONGHONUOC <> ' '",
dma, from.ToString("yyyy-MM-dd"), to.ToString("yyyy-MM-dd"));
return(context.Database.SqlQuery <TBKhachHangMatNuoc>(Helper.Query(query)).ToList());
//return result.Take(100).ToList();
//return result.Take(100).ToList();
}
}
catch (Exception e)
{
throw e;
}
}
