public static DataTable GetDataSoLuongKiemTraBanVe(DateTime?startDate, DateTime?endDate)
{
DateTime dateNow = ManageBase.GetDateNow();
#region Create datatable
DataTable dt = new DataTable("Data");
DataColumn dc = new DataColumn("STT", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("Phuong", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("TongSoBienNhan", typeof(int));
dt.Columns.Add(dc);
dc = new DataColumn("BienNhanHopLy", typeof(int));
dt.Columns.Add(dc);
dc = new DataColumn("BienNhanHuy", typeof(int));
dt.Columns.Add(dc);
dc = new DataColumn("TongBanVeDaKiemTra", typeof(int));
dt.Columns.Add(dc);
dc = new DataColumn("SoBanVeDat", typeof(int));
dt.Columns.Add(dc);
dc = new DataColumn("TyLeBanVeDat", typeof(double));
dt.Columns.Add(dc);
dc = new DataColumn("SoBanVeChuaDat", typeof(int));
dt.Columns.Add(dc);
dc = new DataColumn("TyLeBanVeChuaDat", typeof(double));
dt.Columns.Add(dc);
dc = new DataColumn("SoBanVeDungHan_DKT", typeof(int));
dt.Columns.Add(dc);
dc = new DataColumn("SoBanVeQuaHan_DKT", typeof(int));
dt.Columns.Add(dc);
dc = new DataColumn("TongBanVeChuaKiemTra", typeof(int));
dt.Columns.Add(dc);
dc = new DataColumn("SoBanVeDungHan_CKT", typeof(int));
dt.Columns.Add(dc);
dc = new DataColumn("SoBanVeQuaHan_CKT", typeof(int));
dt.Columns.Add(dc);
#endregion
#region Set data
int stt = 1;
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(XaFields.HuyenId == 769);
EntityCollection ecXa = GenericEntityColManagerBase <XaEntityFactory> .SelectAll(0, null, filter);
Dictionary <int, string> dictXa = new Dictionary <int, string>();
int tongSoBienNhan, bienNhanHopLy, bienNhanHuy, tongBanVeDKT, soBanVeDat, soBanVeChuaDat, soBanVeDungHan_DKT, soBanVeQuaHan_DKT, tongBanVeChuaKiemTra, soBanVeDungHan_CKT, soBanVeQuaHan_CKT;
foreach (XaEntity item in ecXa)
{
DataRow dr = dt.NewRow();
dr["STT"] = stt.ToString();
dr["Phuong"] = item.TenXa;
#region Hồ sơ đăng ký kiểm tra
filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(ThongTinHoSoFields.XaId == item.XaId);
if (startDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop >= startDate);
}
if (endDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop <= endDate);
}
tongSoBienNhan = GenericEntityColManagerBase <ThongTinHoSoEntityFactory> .GetDbCount(filter);
filter.PredicateExpression.Add(ThongTinHoSoFields.HoSoKhongHopLy == true);
bienNhanHuy = GenericEntityColManagerBase <ThongTinHoSoEntityFactory> .GetDbCount(filter);
bienNhanHopLy = tongSoBienNhan - bienNhanHuy;
#endregion
#region Hồ sơ đã kiểm tra
filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(ThongTinHoSoFields.XaId == item.XaId);
if (startDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop >= startDate);
}
if (endDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop <= endDate);
}
filter.PredicateExpression.Add(ThongTinHoSoFields.HoSoDaKiemTra == true);
tongBanVeDKT = GenericEntityColManagerBase <ThongTinHoSoEntityFactory> .GetDbCount(filter);
filter.PredicateExpression.Add(ThongTinHoSoFields.HoSoChuaDat == true);
soBanVeChuaDat = GenericEntityColManagerBase <ThongTinHoSoEntityFactory> .GetDbCount(filter);
soBanVeDat = tongBanVeDKT - soBanVeChuaDat;
filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(ThongTinHoSoFields.XaId == item.XaId);
if (startDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop >= startDate);
}
if (endDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop <= endDate);
}
filter.PredicateExpression.Add(ThongTinHoSoFields.HoSoDaKiemTra == true);
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayTraHoSo <= ThongTinHoSoFields.NgayHenTra | ThongTinHoSoFields.NgayHenTra >= dateNow);
soBanVeDungHan_DKT = GenericEntityColManagerBase <ThongTinHoSoEntityFactory> .GetDbCount(filter);
soBanVeQuaHan_DKT = tongBanVeDKT - soBanVeDungHan_DKT;
#endregion
#region Hồ sơ chưa kiểm tra
filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(ThongTinHoSoFields.XaId == item.XaId);
if (startDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop >= startDate);
}
if (endDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop <= endDate);
}
filter.PredicateExpression.Add(ThongTinHoSoFields.HoSoDaKiemTra == false);
tongBanVeChuaKiemTra = GenericEntityColManagerBase <ThongTinHoSoEntityFactory> .GetDbCount(filter);
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayTraHoSo <= ThongTinHoSoFields.NgayHenTra | ThongTinHoSoFields.NgayHenTra >= dateNow);
soBanVeDungHan_CKT = GenericEntityColManagerBase <ThongTinHoSoEntityFactory> .GetDbCount(filter);
soBanVeQuaHan_CKT = tongBanVeChuaKiemTra - soBanVeDungHan_CKT;
#endregion
dr["TongSoBienNhan"] = tongSoBienNhan;
dr["BienNhanHopLy"] = bienNhanHopLy;
dr["BienNhanHuy"] = bienNhanHuy;
dr["TongBanVeDaKiemTra"] = tongBanVeDKT;
dr["SoBanVeDat"] = soBanVeDat;
dr["SoBanVeChuaDat"] = soBanVeChuaDat;
if (tongBanVeDKT > 0)
{
dr["TyLeBanVeDat"] = Math.Round(soBanVeDat / (double)tongBanVeDKT * 100, 2);
dr["TyLeBanVeChuaDat"] = 100 - (double)dr["TyLeBanVeDat"];
}
dr["SoBanVeDungHan_DKT"] = soBanVeDungHan_DKT;
dr["SoBanVeQuaHan_DKT"] = soBanVeQuaHan_DKT;
dr["TongBanVeChuaKiemTra"] = tongBanVeChuaKiemTra;
dr["SoBanVeDungHan_CKT"] = soBanVeDungHan_CKT;
dr["SoBanVeQuaHan_CKT"] = soBanVeQuaHan_CKT;
stt++;
dt.Rows.Add(dr);
}
#endregion
return(dt);
}
public static DataTable GetDataByLoaiBienDong(DateTime?startDate, DateTime?endDate, List <int> lstLoaibienDongId)
{
#region Get data
RelationPredicateBucket filter = new RelationPredicateBucket();
if (startDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop >= startDate);
}
if (endDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop <= endDate);
}
if (lstLoaibienDongId.Count > 0)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.LoaiBienDongId == lstLoaibienDongId);
}
IncludeFieldsList includeFields = new IncludeFieldsList();
includeFields.AddRange(new List <EntityField2> {
ThongTinHoSoFields.SoBienNhan, ThongTinHoSoFields.LoaiBienDongId, ThongTinHoSoFields.NgayNop, ThongTinHoSoFields.PhiDoVe, ThongTinHoSoFields.PhiThamDinh, ThongTinHoSoFields.XaId
});
EntityCollection ec = GenericEntityColManagerBase <ThongTinHoSoEntityFactory> .SelectAll(0, null, filter, includeFields);
EntityCollection ecXa = GenericEntityColManagerBase <XaEntityFactory> .SelectAll(0, null, new RelationPredicateBucket());
Dictionary <int, string> dictXa = new Dictionary <int, string>();
foreach (XaEntity item in ecXa)
{
dictXa.Add(item.XaId, item.TenXa);
}
EntityCollection ecLoaiBD = GenericEntityColManagerBase <LoaiBienDongEntityFactory> .SelectAll(0, null, new RelationPredicateBucket());
Dictionary <int, string> dictLoaiBD = new Dictionary <int, string>();
foreach (LoaiBienDongEntity item in ecLoaiBD)
{
dictLoaiBD.Add(item.LoaiBienDongId, item.TenLoaiBienDong);
}
#endregion
#region create datatable
DataTable dt = new DataTable("Data");
DataColumn dc = new DataColumn("STT", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("LoaiBienDong", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("SoBienNhan", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("NgayNhan", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("PhiDoVe", typeof(double));
dt.Columns.Add(dc);
dc = new DataColumn("PhiThamDinh", typeof(double));
dt.Columns.Add(dc);
dc = new DataColumn("Phuong", typeof(string));
dt.Columns.Add(dc);
#endregion
#region Set data
int stt = 1;
foreach (ThongTinHoSoEntity item in ec)
{
DataRow dr = dt.NewRow();
dr["STT"] = stt.ToString();
if (item.LoaiBienDongId != null && dictLoaiBD.ContainsKey((int)item.LoaiBienDongId))
{
dr["LoaiBienDong"] = dictLoaiBD[(int)item.LoaiBienDongId];
}
dr["SoBienNhan"] = item.SoBienNhan;
dr["NgayNhan"] = item.NgayNop?.ToString("dd/MM/yyyy");
if (item.PhiDoVe.HasValue)
{
dr["PhiDoVe"] = item.PhiDoVe;
}
if (item.PhiThamDinh.HasValue)
{
dr["PhiThamDinh"] = item.PhiThamDinh;
}
if (dictXa.ContainsKey(item.XaId))
{
dr["Phuong"] = dictXa[item.XaId];
}
stt++;
dt.Rows.Add(dr);
}
#endregion
return(dt);
}
public static DataTable GetDataTongPhiDoVe(DateTime?startDate, DateTime?endDate)
{
#region Get data
RelationPredicateBucket filter = new RelationPredicateBucket();
if (startDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop >= startDate);
}
if (endDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayNop <= endDate);
}
filter.PredicateExpression.Add(ThongTinHoSoFields.HoSoMoiNhat == true);
IncludeFieldsList includeFields = new IncludeFieldsList();
includeFields.AddRange(new List <EntityField2> {
ThongTinHoSoFields.SoThuTuThua, ThongTinHoSoFields.SoHieuToBanDo, ThongTinHoSoFields.LoaiBienDongId, ThongTinHoSoFields.PhiDoVe, ThongTinHoSoFields.PhiThamDinh, ThongTinHoSoFields.XaId, ThongTinHoSoFields.RootId, ThongTinHoSoFields.KhoaChaId
});
EntityCollection ec = GenericEntityColManagerBase <ThongTinHoSoEntityFactory> .SelectAll(0, null, filter, includeFields);
EntityCollection ecXa = GenericEntityColManagerBase <XaEntityFactory> .SelectAll(0, null, new RelationPredicateBucket());
Dictionary <int, string> dictXa = new Dictionary <int, string>();
foreach (XaEntity item in ecXa)
{
dictXa.Add(item.XaId, item.TenXa);
}
#endregion
#region create datatable
DataTable dt = new DataTable("Data");
DataColumn dc = new DataColumn("STT", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("SoThua", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("SoTo", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("Phuong", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("SoLanBienDong", typeof(int));
dt.Columns.Add(dc);
dc = new DataColumn("PhiDoVe", typeof(double));
dt.Columns.Add(dc);
dc = new DataColumn("PhiThamDinh", typeof(double));
dt.Columns.Add(dc);
#endregion
#region Set data
int stt = 1;
foreach (ThongTinHoSoEntity item in ec)
{
DataRow dr = dt.NewRow();
dr["STT"] = stt.ToString();
dr["SoThua"] = item.SoThuTuThua;
dr["SoTo"] = item.SoHieuToBanDo;
if (dictXa.ContainsKey(item.XaId))
{
dr["Phuong"] = dictXa[item.XaId];
}
int soLanBienDong = 0;
double tongPhiDoVe = 0, tongPhiThamDinh = 0;
if (item.KhoaChaId.HasValue)
{
filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(ThongTinHoSoFields.RootId == item.RootId);
includeFields = new IncludeFieldsList();
includeFields.AddRange(new List <EntityField2> {
ThongTinHoSoFields.LoaiBienDongId, ThongTinHoSoFields.PhiDoVe, ThongTinHoSoFields.PhiThamDinh
});
EntityCollection ecTemp = GenericEntityColManagerBase <ThongTinHoSoEntityFactory> .SelectAll(0, null, filter, includeFields);
foreach (ThongTinHoSoEntity itemTTHS in ecTemp)
{
if (itemTTHS.LoaiBienDongId.HasValue)
{
soLanBienDong++;
}
if (itemTTHS.PhiDoVe.HasValue)
{
tongPhiDoVe += itemTTHS.PhiDoVe.Value;
}
if (itemTTHS.PhiThamDinh.HasValue)
{
tongPhiThamDinh += itemTTHS.PhiThamDinh.Value;
}
}
}
else
{
if (item.LoaiBienDongId.HasValue)
{
soLanBienDong = 1;
}
if (item.PhiDoVe.HasValue)
{
tongPhiDoVe = item.PhiDoVe.Value;
}
if (item.PhiThamDinh.HasValue)
{
tongPhiThamDinh = item.PhiThamDinh.Value;
}
}
dr["SoLanBienDong"] = soLanBienDong;
dr["PhiDoVe"] = tongPhiDoVe;
dr["PhiThamDinh"] = tongPhiThamDinh;
stt++;
dt.Rows.Add(dr);
}
#endregion
return(dt);
}
public static DataTable GetPhiDoVeByNgayKiemTraNoiNghiep(DateTime?startDate, DateTime?endDate)
{
#region Get data
RelationPredicateBucket filter = new RelationPredicateBucket();
if (startDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayKiemTraNoiNghiep >= startDate);
}
if (endDate != null)
{
filter.PredicateExpression.Add(ThongTinHoSoFields.NgayKiemTraNoiNghiep <= endDate);
}
IncludeFieldsList includeFields = new IncludeFieldsList();
includeFields.AddRange(new List <EntityField2> {
ThongTinHoSoFields.SoBienNhan, ThongTinHoSoFields.SoBanVe, ThongTinHoSoFields.NgayNop, ThongTinHoSoFields.NgayTraHoSo, ThongTinHoSoFields.NguoiNopHoSo, ThongTinHoSoFields.NgayKiemTraNoiNghiep, ThongTinHoSoFields.SoThuTuThua, ThongTinHoSoFields.SoHieuToBanDo, ThongTinHoSoFields.PhiDoVe, ThongTinHoSoFields.PhiThamDinh, ThongTinHoSoFields.XaId
});
EntityCollection ec = GenericEntityColManagerBase <ThongTinHoSoEntityFactory> .SelectAll(0, null, filter, includeFields);
EntityCollection ecXa = GenericEntityColManagerBase <XaEntityFactory> .SelectAll(0, null, new RelationPredicateBucket());
Dictionary <int, string> dictXa = new Dictionary <int, string>();
foreach (XaEntity item in ecXa)
{
dictXa.Add(item.XaId, item.TenXa);
}
#endregion
#region create datatable
DataTable dt = new DataTable("PhiDoVe");
DataColumn dc = new DataColumn("STT", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("SoBienNhan", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("SoBanVe", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("NgayNhan", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("NgayTraHoSo", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("TenNguoiNop", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("NgayKiemTraNoiNghiep", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("SoThua", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("SoTo", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("Phuong", typeof(string));
dt.Columns.Add(dc);
dc = new DataColumn("PhiDoVe", typeof(double));
dt.Columns.Add(dc);
dc = new DataColumn("PhiThamDinh", typeof(double));
dt.Columns.Add(dc);
#endregion
#region Set data
int stt = 1;
foreach (ThongTinHoSoEntity item in ec)
{
DataRow dr = dt.NewRow();
dr["STT"] = stt.ToString();
dr["SoBienNhan"] = item.SoBienNhan;
dr["SoBanVe"] = item.SoBanVe;
dr["NgayNhan"] = item.NgayNop?.ToString("dd/MM/yyyy");
dr["NgayTraHoSo"] = item.NgayTraHoSo?.ToString("dd/MM/yyyy");
dr["TenNguoiNop"] = item.NguoiNopHoSo;
dr["NgayKiemTraNoiNghiep"] = item.NgayKiemTraNoiNghiep?.ToString("dd/MM/yyyy");
dr["SoThua"] = item.SoThuTuThua;
dr["SoTo"] = item.SoHieuToBanDo;
if (dictXa.ContainsKey(item.XaId))
{
dr["Phuong"] = dictXa[item.XaId];
}
if (item.PhiDoVe.HasValue)
{
dr["PhiDoVe"] = item.PhiDoVe;
}
if (item.PhiThamDinh.HasValue)
{
dr["PhiThamDinh"] = item.PhiThamDinh;
}
stt++;
dt.Rows.Add(dr);
}
#endregion
return(dt);
}