public ListHopDongModelGridView DanhSachHopDong(string mdv, string nsd, string pas, string maDonVi, long idGoiThau, int pageSize, int pageIndex = 1) { try { var listHopDong = new ListHopDongModelGridView(); var giamSatDataTier = new GiamSatRepository(); var objData = giamSatDataTier.DanhSachHopDong(mdv, nsd, pas, maDonVi, idGoiThau, pageSize, pageIndex); if (objData != null) { var list = new List <HopDongShortModel>(); var pageSetting = new PaginationSetting { PageSize = pageSize, TotalRecords = Convert.ToInt64(objData[1]) }; listHopDong.TotalPage = pageSetting.TotalPage; listHopDong.TotalRecords = pageSetting.TotalRecords; var table = objData[0] as DataTable; if (table != null && table.Rows.Count > 0) { foreach (DataRow dr in table.Rows) { var hopdong = new HopDongShortModel(); hopdong.IdHopDong = Convert.ToInt64(dr["id_hopdong"]); hopdong.MaDonVi = maDonVi; hopdong.IdGoiThau = idGoiThau; hopdong.TenHopDong = dr["ten_hd"].ToString(); hopdong.BenA = dr["ben_a"].ToString(); hopdong.BenB = dr["ben_b"].ToString(); hopdong.TienNoiTe = Convert.ToInt64(dr["tien_nt"]); hopdong.TienNgoaiTe = Convert.ToInt64(dr["tien_ngt"]); hopdong.TinhTrangHopDong = dr["tinhtrang_hdo"].ToString(); hopdong.TinhTrangXoa = (TinhTrangXoa)Convert.ToInt32(dr["hdo_xoa"]); // check giai doan von da duoc giam sat chua if (!dr.IsNull("id_giamsat")) { hopdong.IdGiamSat = Convert.ToInt64(dr["id_giamsat"]); hopdong.KetQuaGiamSat = (KetQuaGiamSat)Convert.ToInt32(dr["ma_kq_gs"]); hopdong.GhiChuGiamSat = dr["ghi_chu"].ToString(); } list.Add(hopdong); } listHopDong.HopDongModelsGridView = list; } } return(listHopDong); } catch (Exception) { return(null); } }
public ListDuAnModelGridView TimKiemDuAn(string mdv, string nsd, string pas, SearchProjectSetting searchProjectSetting, int pageSize, int pageIndex = 1) { try { var listDuAn = new ListDuAnModelGridView(); var giamSatDataTier = new GiamSatRepository(); var objData = giamSatDataTier.TimKiemDuAn(mdv, nsd, pas, searchProjectSetting, pageSize, pageIndex); if (objData != null) { var list = new List <DuAnShortModel>(); var pageSetting = new PaginationSetting { PageSize = pageSize, TotalRecords = Convert.ToInt64(objData[1]) }; listDuAn.TotalPage = pageSetting.TotalPage; listDuAn.TotalRecords = pageSetting.TotalRecords; var table = objData[0] as DataTable; if (table != null && table.Rows.Count > 0) { foreach (DataRow dr in table.Rows) { var duan = new DuAnShortModel(); duan.IdDuAn = Convert.ToInt64(dr["so_id"]); duan.LoaiNguonVon = (LoaiNguonVon)Convert.ToInt32(dr["loai_nguon_von"]); duan.LoaiPhanCap = (LoaiPhanCap)Enum.Parse(typeof(LoaiPhanCap), dr["phancap"].ToString()); duan.MaDonVi = dr["ma_dvi"].ToString(); duan.MaDuAn = dr["ma"].ToString(); duan.NamBatDau = Convert.ToInt32(dr["nambd"]); duan.NamKetThuc = Convert.ToInt32(dr["namkt"]); duan.NhomDuAn = (NhomDuAn)Enum.Parse(typeof(NhomDuAn), dr["nhom_da"].ToString()); duan.TenDuAn = dr["ten"].ToString(); duan.TongVonDauTu = Convert.ToInt64(dr["tienqd"]); duan.TenLoaiNguonVon = EnumHelper.GetDescription(duan.LoaiNguonVon); duan.TenLoaiPhanCap = EnumHelper.GetDescription(duan.LoaiPhanCap); duan.TenNhomDuAn = EnumHelper.GetDescription(duan.NhomDuAn); list.Add(duan); } listDuAn.DuAnModelsGridView = list; } } return(listDuAn); } catch (Exception) { return(null); } }
public ChangeResultSettings DongBoDBDauTu() { var result = new ChangeResultSettings(); // dung de xac dinh bang thuc hien sql cuoi cung. Khi phan bat bug hoan chinh se bo var lastTableName = ""; try { // tao ket noi dau tu, giam sat ConnectDB.CloseConnection(_connectDt); _connectDt = new OracleConnection(); _connectDt = ConnectDB.GetOracleConnection(_connectDt, NameDatabase.DauTu); ConnectDB.CloseConnection(_connectGs); _connectGs = new OracleConnection(); _connectGs = ConnectDB.GetOracleConnection(_connectGs); //todo: tao transaction //_oracleTransaction = _connectGs.BeginTransaction(IsolationLevel.ReadCommitted); #region bang co lich su #region comment for test BangDauTuCoLichSu List <string> listColumnId; DataTable tableDt; DataTable tableGs; foreach (var item in Enum.GetNames(typeof(BangDauTuCoLichSu))) { lastTableName = "<br /> begin " + item; // list cac cot la khoa chinh cua bang listColumnId = new List <string>(); listColumnId.Add("MA_DVI"); switch (item) { case "BDT_QLDT_KHDT_TR": listColumnId.Add("NAM"); listColumnId.Add("SO_ID"); listColumnId.Add("DOT"); listColumnId.Add("SO_QD"); break; case "BDT_QLDT_QDDT_TINH": listColumnId.Add("SO_ID"); listColumnId.Add("BT"); break; default: listColumnId.Add("SO_ID"); break; } // sql get list unique cua 1 ban ghi var sqlDt = "select "; foreach (var dk in listColumnId) { sqlDt += " a." + dk + ", "; } sqlDt += " nvl(max(b.lan),0) lan from " + item + @" a left join " + item + @"_ls b on "; var first = true; foreach (var dk in listColumnId) { if (first) { sqlDt += " a." + dk + " = b." + dk; first = false; } else { sqlDt += " and a." + dk + " = b." + dk; } } //var sqlGs = sqlDt + " and xoa = " + (int)TinhTrangXoa.ChuaXoa; var sqlGroup = " group by "; first = true; foreach (var dk in listColumnId) { if (first) { sqlGroup += " a." + dk; first = false; } else { sqlGroup += ", a." + dk; } } sqlDt += sqlGroup; //sqlGs += sqlGroup; // get data dau tu tableDt = new DataTable(); _oracleAdapter = new OracleDataAdapter(sqlDt, _connectDt); _oracleAdapter.Fill(tableDt); // get data giam sat tableGs = new DataTable(); _oracleAdapter = new OracleDataAdapter(sqlDt, _connectGs); _oracleAdapter.Fill(tableGs); // gen list dong bo object tu bang dau tu tra ve var listDt = new List <DongBoObjectLS>(); if (tableDt.Rows.Count > 0) { foreach (DataRow dr in tableDt.Rows) { var ls = new DongBoObjectLS(); var ob = GenObjectDongBo(listColumnId, dr); ls.DongBoObject = ob; ls.LAN = Convert.ToInt64(dr[listColumnId.Count]); listDt.Add(ls); } } // gen list dong bo object tu bang giam sat tra ve var listGs = new List <DongBoObjectLS>(); if (tableGs.Rows.Count > 0) { foreach (DataRow dr in tableGs.Rows) { var ls = new DongBoObjectLS(); var ob = GenObjectDongBo(listColumnId, dr); ls.DongBoObject = ob; ls.LAN = Convert.ToInt64(dr[listColumnId.Count]); listGs.Add(ls); } } // list id var listDtId = listDt.Select(x => x.DongBoObject).ToList(); var listGsId = listGs.Select(x => x.DongBoObject).ToList(); // 1. Delete // 1a. select Ids ko ton tai trong DT ma ton tai trong GS //var list1 = listGsId.Except(listDtId).ToList(); var list1 = new List <DongBoObject>(); // todo: can toi uu doan loc nay foreach (var dongBoObject in listGsId) { var t = 0; if (listDtId.Any(boObject => dongBoObject.SO_ID == boObject.SO_ID && dongBoObject.MA_DVI == boObject.MA_DVI && dongBoObject.MA == boObject.MA && dongBoObject.SO_QD == boObject.SO_QD && dongBoObject.BT == boObject.BT && dongBoObject.HANG == boObject.HANG && dongBoObject.MA_HT == boObject.MA_HT && dongBoObject.NAM == boObject.NAM && dongBoObject.DOT == boObject.DOT && dongBoObject.NHA == boObject.NHA)) { t = 1; } if (t == 0) { list1.Add(dongBoObject); } } if (list1.Any()) { // 1b. danh dau xoa trong GS foreach (var o in list1) { var sDieuKien = GenStringDieuKien(listColumnId, o); // cap nhat thanh da xoa vao database giam sat var tempSql = "update " + item + " set xoa = " + (int)TinhTrangXoa.DaXoa + " where "; tempSql += sDieuKien; _lastSql = tempSql; _oracleCommand = new OracleCommand(tempSql, _connectGs); _oracleCommand.CommandType = CommandType.Text; var returnUpdate = _oracleCommand.ExecuteNonQuery(); if (returnUpdate == 0) { throw new Exception("cap nhat tinh trang xoa that bai"); } } } // 2. Update // 2a. select Ids co "lan" trong lich su DT > "lan" trong lich su GS //var list2 = (from oDt in listDt from oGs in listGs where oDt.DongBoObject == oGs.DongBoObject && oDt.LAN > oGs.LAN select oDt).ToList(); var list2 = new List <DongBoObjectLS>(); // todo: can toi uu doan loc nay foreach (var dongBoObject in listDt) { if (listGs.Any(boObject => dongBoObject.DongBoObject.SO_ID == boObject.DongBoObject.SO_ID && dongBoObject.DongBoObject.MA_DVI == boObject.DongBoObject.MA_DVI && dongBoObject.DongBoObject.MA == boObject.DongBoObject.MA && dongBoObject.DongBoObject.SO_QD == boObject.DongBoObject.SO_QD && dongBoObject.DongBoObject.BT == boObject.DongBoObject.BT && dongBoObject.DongBoObject.HANG == boObject.DongBoObject.HANG && dongBoObject.DongBoObject.MA_HT == boObject.DongBoObject.MA_HT && dongBoObject.DongBoObject.NAM == boObject.DongBoObject.NAM && dongBoObject.DongBoObject.DOT == boObject.DongBoObject.DOT && dongBoObject.DongBoObject.NHA == boObject.DongBoObject.NHA && dongBoObject.LAN > boObject.LAN)) { list2.Add(dongBoObject); } } if (list2.Any()) { // 2b. update GS var ps = new PaginationSetting { TotalRecords = list2.Count }; for (int i = 0; i < ps.TotalPage; i++) { // update bang chinh foreach (var o in list2) { var tempData = new DataTable(); var tempSql = "select * from " + item + " where "; tempSql += GenStringDieuKien(listColumnId, o.DongBoObject); _lastSql = tempSql; _oracleAdapter = new OracleDataAdapter(tempSql, _connectDt); _oracleAdapter.Fill(tempData); if (tempData.Rows.Count > 0) { UpdateDatatable(listColumnId, item, tempData, _connectGs); } // insert bang lich su tempData = new DataTable(); tempSql = "select * from " + item + "_ls where "; tempSql += GenStringDieuKien(listColumnId, o.DongBoObject) + " and lan = " + o.LAN; _lastSql = tempSql; _oracleAdapter = new OracleDataAdapter(tempSql, _connectDt); _oracleAdapter.Fill(tempData); if (tempData.Rows.Count > 0) { InsertDatatable(item + "_LS", tempData, _connectGs); } } } } // 3. Insert // 3a. select Ids ton tai trong DT ma ko ton tai trong GS //var list3 = listDtId.Except(listGsId).ToList(); var list3 = new List <DongBoObject>(); // todo: can toi uu doan loc nay foreach (var dongBoObject in listDtId) { var t = 0; if (listGsId.Any(boObject => dongBoObject.SO_ID == boObject.SO_ID && dongBoObject.MA_DVI == boObject.MA_DVI && dongBoObject.MA == boObject.MA && dongBoObject.SO_QD == boObject.SO_QD && dongBoObject.BT == boObject.BT && dongBoObject.HANG == boObject.HANG && dongBoObject.MA_HT == boObject.MA_HT && dongBoObject.NAM == boObject.NAM && dongBoObject.DOT == boObject.DOT && dongBoObject.NHA == boObject.NHA)) { t = 1; } if (t == 0) { list3.Add(dongBoObject); } } if (list3.Any()) { // 3b. insert GS foreach (var o in list3) { var tempData = new DataTable(); var tempSql = "select * from " + item + " where "; tempSql += GenStringDieuKien(listColumnId, o); _lastSql = tempSql; _oracleAdapter = new OracleDataAdapter(tempSql, _connectDt); _oracleAdapter.Fill(tempData); if (tempData.Rows.Count > 0) { InsertDatatable(item, tempData, _connectGs); } } } lastTableName += "<br /> end: " + item; } #endregion comment #endregion bang co lich su #region bang ko co lich su simple key #region comment for test BangDauTuKoLichSu foreach (var item in Enum.GetNames(typeof(BangDauTuKoLichSu))) { lastTableName += "<br />begin " + item; // list cac cot la khoa chinh cua bang listColumnId = new List <string>(); listColumnId.Add("MA_DVI"); // cac bang ma dung chung if (item.IndexOf("MA", System.StringComparison.Ordinal) != -1) { listColumnId.Add("MA"); } // cac bang thong tin else { switch (item) { // cac bang primary key complex case "BDT_QLDT_KHDT_TD": listColumnId.Add("SO_QD"); listColumnId.Add("NAM"); break; case "BDT_QLDT_GTH_MOI_NHA": case "BDT_QLDT_GTH_MO_NHA": case "BDT_QLDT_GTH_XET_NHA": listColumnId.Add("SO_ID"); listColumnId.Add("NHA"); break; case "BDT_QLDT_GTH_XET_TBI": case "BDT_QLDT_GTH_TO_CGIA": case "BDT_QLDT_GTH_TBI": case "BDT_QLDT_QTOAN": listColumnId.Add("SO_ID"); listColumnId.Add("BT"); break; case "BDT_QLDT_HTHANH": listColumnId.Add("SO_ID"); listColumnId.Add("HANG"); listColumnId.Add("MA_HT"); break; // bang simple primary key default: listColumnId.Add("SO_ID"); break; } } // sql get list unique cua 1 ban ghi var sqlDt = "select "; var first = true; foreach (var dk in listColumnId) { if (first) { sqlDt += dk; first = false; } else { sqlDt += "," + dk; } } sqlDt += " from " + item; //var sqlGs = sqlDt + " where xoa = " + (int)TinhTrangXoa.ChuaXoa; // get data dau tu tableDt = new DataTable(); _oracleAdapter = new OracleDataAdapter(sqlDt, _connectDt); _oracleAdapter.Fill(tableDt); // get data giam sat tableGs = new DataTable(); _oracleAdapter = new OracleDataAdapter(sqlDt, _connectGs); _oracleAdapter.Fill(tableGs); // list object key var listDtId = new List <DongBoObject>(); if (tableDt.Rows.Count > 0) { // get list dong bo object tu datatable dau tu tra ve listDtId.AddRange(from DataRow dr in tableDt.Rows select GenObjectDongBo(listColumnId, dr)); } var listGsId = new List <DongBoObject>(); if (tableGs.Rows.Count > 0) { // get list dong bo object tu datatable giam sat tra ve listGsId.AddRange(from DataRow dr in tableGs.Rows select GenObjectDongBo(listColumnId, dr)); } // 1. Delete // 1a. select Ids ko ton tai trong DT ma ton tai trong GS //var list1 = listGsId.Except(listDtId).ToList(); var list1 = new List <DongBoObject>(); // list ton tai trong ca dau tu va giam sat var list2 = new List <DongBoObject>(); // todo: can toi uu doan loc nay foreach (var dongBoObject in listGsId) { var t = 0; if (listDtId.Any(boObject => dongBoObject.SO_ID == boObject.SO_ID && dongBoObject.MA_DVI == boObject.MA_DVI && dongBoObject.MA == boObject.MA && dongBoObject.SO_QD == boObject.SO_QD && dongBoObject.BT == boObject.BT && dongBoObject.HANG == boObject.HANG && dongBoObject.MA_HT == boObject.MA_HT && dongBoObject.NAM == boObject.NAM && dongBoObject.DOT == boObject.DOT && dongBoObject.NHA == boObject.NHA)) { t = 1; list2.Add(dongBoObject); } if (t == 0) { list1.Add(dongBoObject); } } if (list1.Any()) { // 1b. danh dau xoa trong csdl GS foreach (var o in list1) { var sDieuKien = GenStringDieuKien(listColumnId, o); // cap nhat thanh da xoa vao database giam sat var tempSql = "update " + item + " set xoa = " + (int)TinhTrangXoa.DaXoa + " where "; tempSql += sDieuKien; _lastSql = tempSql; _oracleCommand = new OracleCommand(tempSql, _connectGs); _oracleCommand.CommandType = CommandType.Text; var returnUpdate = _oracleCommand.ExecuteNonQuery(); if (returnUpdate == 0) { throw new Exception("cap nhat tinh trang xoa that bai"); } } } // 2. Update // 2a. select Ids ton tai trong ca DT va GS //var list2 = listDtId.Where(listGsId.Contains).ToList(); if (list2.Any()) { // 2b. update GS foreach (var o in list2) { var tempData = new DataTable(); var tempSql = "select * from " + item + " where "; tempSql += GenStringDieuKien(listColumnId, o); _lastSql = tempSql; _oracleAdapter = new OracleDataAdapter(tempSql, _connectDt); _oracleAdapter.Fill(tempData); if (tempData.Rows.Count > 0) { UpdateDatatable(listColumnId, item, tempData, _connectGs); } } } // 3. Insert // 3a. select Ids ton tai trong DT ma ko ton tai trong GS //var list3 = listDtId.Except(listGsId).ToList(); var list3 = new List <DongBoObject>(); // todo: can toi uu doan loc nay foreach (var dongBoObject in listDtId) { var t = 0; if (listGsId.Any(boObject => dongBoObject.SO_ID == boObject.SO_ID && dongBoObject.MA_DVI == boObject.MA_DVI && dongBoObject.MA == boObject.MA && dongBoObject.SO_QD == boObject.SO_QD && dongBoObject.BT == boObject.BT && dongBoObject.HANG == boObject.HANG && dongBoObject.MA_HT == boObject.MA_HT && dongBoObject.NAM == boObject.NAM && dongBoObject.DOT == boObject.DOT && dongBoObject.NHA == boObject.NHA)) { t = 1; } if (t == 0) { list3.Add(dongBoObject); } } if (list3.Any()) { // 3b. insert GS foreach (var o in list3) { var tempData = new DataTable(); var tempSql = "select * from " + item + " where "; tempSql += GenStringDieuKien(listColumnId, o); _lastSql = tempSql; _oracleAdapter = new OracleDataAdapter(tempSql, _connectDt); _oracleAdapter.Fill(tempData); if (tempData.Rows.Count > 0) { InsertDatatable(item, tempData, _connectGs); } } } lastTableName += "<br /> end: " + item; } #endregion end comment for test #endregion bang ko co lich su //todo: commit transaction //_oracleTransaction.Commit(); } catch (Exception ex) { // todo: roll back //_oracleTransaction.Rollback(); result.ChangeResult = ChangeResult.ThatBai; result.Message = _lastSql + "<br />" + lastTableName + "<br />" + ex.Message; } finally { // todo: dong tat ca ket noi ConnectDB.CloseConnection(_connectDt); ConnectDB.CloseConnection(_connectGs); } return(result); }
public ListGoiThauModelGridView DanhSachGoiThau(string mdv, string nsd, string pas, string maDonVi, long idDuAn, int pageSize, int pageIndex = 1) { try { var listGoiThau = new ListGoiThauModelGridView(); var giamSatDataTier = new GiamSatRepository(); var listTrunggian = new List <TrungGian>(); var objData = giamSatDataTier.DanhSachGoiThau(mdv, nsd, pas, maDonVi, idDuAn, pageSize, pageIndex); if (objData != null) { var list = new List <GoiThauShortModel>(); var pageSetting = new PaginationSetting { // 1 ban ghi phan ra moi thau, mo thau, xet thau nen total reocord phai nhan voi 3 PageSize = pageSize * 3, TotalRecords = Convert.ToInt64(objData[1]) * 3 }; listGoiThau.TotalPage = pageSetting.TotalPage; listGoiThau.TotalRecords = pageSetting.TotalRecords; var table = objData[0] as DataTable; if (table != null && table.Rows.Count > 0) { foreach (DataRow dr in table.Rows) { var isGiamSat = false; var id = Convert.ToInt64(dr["id_goithau"]); var hinhthuc = dr["hinhthuc_dauthau"].ToString(); var xoa = (TinhTrangXoa)Convert.ToInt32(dr["goithau_xoa"]); var ten = dr["ten_goithau"].ToString(); if (!dr.IsNull("ma_gd_gthau")) { var giaidoan = Convert.ToInt32(dr["ma_gd_gthau"]); if (listTrunggian.Exists(element => element.Id == id && element.GiaiDoan == giaidoan)) { break; } listTrunggian.Add(new TrungGian { Id = id, GiaiDoan = giaidoan }); isGiamSat = true; } else { listTrunggian.Add(new TrungGian { Id = id, GiaiDoan = -1 }); } if (!isGiamSat) { var goithau = new GoiThauShortModel(); goithau.IdDuAn = idDuAn; goithau.MaDonVi = maDonVi; goithau.IdGoiThau = id; goithau.HinhThucDauThau = hinhthuc; goithau.TinhTrangXoa = xoa; goithau.TenGoiThau = ten; goithau.GiaiDoanDauThau = (int)GiaiDoanChonNhaThau.MoiThau; //todo: xac dinh trang thai lai goithau.TrangThaiThucHien = "Hoàn thành"; list.Add(goithau); goithau = new GoiThauShortModel(); goithau.IdDuAn = idDuAn; goithau.MaDonVi = maDonVi; goithau.IdGoiThau = id; goithau.HinhThucDauThau = hinhthuc; goithau.TinhTrangXoa = xoa; goithau.TenGoiThau = ten; goithau.GiaiDoanDauThau = GiaiDoanChonNhaThau.MoThau; //todo: xac dinh trang thai lai goithau.TrangThaiThucHien = "Hoàn thành"; list.Add(goithau); goithau = new GoiThauShortModel(); goithau.IdDuAn = idDuAn; goithau.MaDonVi = maDonVi; goithau.IdGoiThau = id; goithau.HinhThucDauThau = hinhthuc; goithau.TinhTrangXoa = xoa; goithau.TenGoiThau = ten; goithau.GiaiDoanDauThau = GiaiDoanChonNhaThau.XetThau; //todo: xac dinh trang thai lai goithau.TrangThaiThucHien = "Hoàn thành"; list.Add(goithau); } else { var goithau = new GoiThauShortModel(); goithau.IdDuAn = idDuAn; goithau.MaDonVi = maDonVi; goithau.IdGoiThau = id; goithau.HinhThucDauThau = hinhthuc; goithau.TinhTrangXoa = xoa; goithau.TenGoiThau = ten; goithau.GiaiDoanDauThau = (GiaiDoanChonNhaThau)Convert.ToInt32(dr["ma_gd_gthau"]); goithau.TrangThaiThucHien = "Hoàn thành"; list.Add(goithau); } } foreach (var l in list) { foreach (DataRow dr in table.Rows) { if (l.IdGoiThau == Convert.ToInt64(dr["id_goithau"])) { if (!dr.IsNull("id_giamsat")) { if (l.GiaiDoanDauThau == (GiaiDoanChonNhaThau)Convert.ToInt32(dr["ma_gd_gthau"])) { l.IdGiamSat = Convert.ToInt64(dr["id_giamsat"]); l.KetQuaGiamSat = (KetQuaGiamSat)Convert.ToInt32(dr["ma_kq_gs"]); l.GhiChuGiamSat = dr["ghi_chu"].ToString(); break; } } } } } } listGoiThau.GoiThauModelsGridView = list; } return(listGoiThau); } catch (Exception) { return(null); } }