public ChangeResultSettings Login(string maDonVi, string userName, string pass) { var result = new ChangeResultSettings(); try { ConnectDB.CloseConnection(_connectGs); _connectGs = new OracleConnection(); _connectGs = ConnectDB.GetOracleConnection(_connectGs); var cm = _connectGs.CreateCommand(); cm.CommandText = "usp_check_login"; cm.CommandType = CommandType.StoredProcedure; cm.Parameters.Add(new OracleParameter("mdv", OracleDbType.Varchar2)).Value = maDonVi; cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = userName; cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = pass; // 0 chinh xac, 1 tai khoan da lock cm.Parameters.Add(new OracleParameter("cs_lke", OracleDbType.RefCursor)).Direction = ParameterDirection.Output; var tableGs = new DataTable(); _oracleAdapter = new OracleDataAdapter(cm); _oracleAdapter.Fill(tableGs); var resultLogin = 2; if (tableGs.Rows.Count > 0) { resultLogin = Convert.ToInt32(tableGs.Rows[0]["khoa"]); } switch (resultLogin) { case 2: result.ChangeResult = ChangeResult.ThatBai; result.Message = "Tên hoặc mật khẩu không chính xác!"; break; case 0: result.ChangeResult = ChangeResult.ThanhCong; break; case 1: result.ChangeResult = ChangeResult.ThatBai; result.Message = "Tài khoản đã bị khóa. Vui lòng liên hệ với admin!"; break; default: throw new Exception(); } } catch (Exception ex) { result.Message = "Có lỗi trong quá trình đăng nhập. Vui lòng thử lại!"; result.ChangeResult = ChangeResult.ThatBai; } return(result); }
public string CapNhatLoaiNguonVon(string ma_don_vi, string so_id_don_vi, string trang_thai) { ChangeResultSettings result = giamsatService.CapNhatLoaiNguonVon("", "", "", ma_don_vi, Int64.Parse(so_id_don_vi), Int32.Parse(trang_thai)); if (result.ChangeResult == ChangeResult.ThanhCong) { return("Cập nhật thành công"); } else { return(result.Message); } }
public string CapNhatHopDong(string stringGridHopDong) { string madonvi = Request.QueryString["madonvi"]; long idduan = Int64.Parse(Request.QueryString["idduan"]); string excelData = stringGridHopDong; string[] rowSeparator = new string[] { "|*row*|" }; string[] cellSeparator = new string[] { "|*cell*|" }; string[] dataRows = excelData.Split(rowSeparator, StringSplitOptions.None); string a = ""; GiamSatSetting[] subjects = new GiamSatSetting[dataRows.Length]; for (int i = 0; i < dataRows.Length; i++) { string[] dataCells = dataRows[i].Split(cellSeparator, StringSplitOptions.None); string TenHopDong = dataCells[0]; string BenA = dataCells[1]; string BenB = dataCells[2]; string TienNo = dataCells[3]; string TienNgoai = dataCells[4]; string Ttthuchien = dataCells[5]; string Ttgiamsat = dataCells[6]; string Ghichu = dataCells[7]; string GiamSatId = dataCells[8]; string HopDongId = dataCells[9]; string GoiThauId = dataCells[10]; GiamSatSetting gss = new GiamSatSetting(); gss.MaDonVi = madonvi; gss.DuAnID = idduan; gss.HopDongID = Int64.Parse(HopDongId); gss.GiamSatID = Int64.Parse(GiamSatId); gss.GoiThauID = Int64.Parse(GoiThauId); gss.KetQuaGiamSat = EnumHelper.GetEnumValueFromDescription <KetQuaGiamSat>(Ttgiamsat); gss.GhiChu = Ghichu; subjects[i] = gss; } ChangeResultSettings result = giamsatService.GiamSat("", "", "", (int)LoaiGiamSat.GiamSatHopDong, subjects); if (result.ChangeResult == ChangeResult.ThanhCong) { a += "Cập nhật thành công"; } else { a += result.Message; } return(a); }
public ChangeResultSettings CapNhatNguoiDung(string mdv, string nsd, string pas, NguoiDungModel nguoiDung) { var result = new ChangeResultSettings(); try { result.ChangeResult = ChangeResult.ThanhCong; } catch (Exception) { result.ChangeResult = ChangeResult.ThatBai; } return(result); }
public ChangeResultSettings XoaNhomNguoiDung(string mdv, string nsd, string pas, string maDonVi, long nhomId) { var result = new ChangeResultSettings(); try { result.ChangeResult = ChangeResult.ThanhCong; } catch (Exception) { result.ChangeResult = ChangeResult.ThatBai; } return(result); }
public string CapNhatKeHoachVon(string stringGridKHV, string Nam) { string madonvi = Request.QueryString["madonvi"]; long idduan = Int64.Parse(Request.QueryString["idduan"]); string excelData = stringGridKHV; string[] rowSeparator = new string[] { "|*row*|" }; string[] cellSeparator = new string[] { "|*cell*|" }; string[] dataRows = excelData.Split(rowSeparator, StringSplitOptions.None); string a = ""; GiamSatSetting[] subjects = new GiamSatSetting[dataRows.Length]; for (int i = 0; i < dataRows.Length; i++) { string[] dataCells = dataRows[i].Split(cellSeparator, StringSplitOptions.None); string TenGiaiDoan = dataCells[0]; string TrangThaiThucHien = dataCells[1]; string TenKetQuaGiamSat = dataCells[2]; string GhiChu = dataCells[3]; string GiamSatId = dataCells[4]; string Dot = dataCells[5]; string SoQuyetDinh = dataCells[6]; string GiaiDoanKHV = dataCells[7]; GiamSatSetting gss = new GiamSatSetting(); gss.MaDonVi = madonvi; gss.DuAnID = idduan; gss.NamKHV = Int32.Parse(Nam); gss.GiaiDoanKHV = (GiaiDoanKHV)Convert.ToInt32(GiaiDoanKHV); gss.KetQuaGiamSat = EnumHelper.GetEnumValueFromDescription <KetQuaGiamSat>(TenKetQuaGiamSat); gss.GhiChu = GhiChu; gss.DotKHV = Convert.ToInt32(Dot); gss.SoQD = SoQuyetDinh == "" ? " " : SoQuyetDinh; gss.GiamSatID = Int64.Parse(GiamSatId); subjects[i] = gss; } ChangeResultSettings result = giamsatService.GiamSat("", "", "", (int)LoaiGiamSat.GiamSatKHV, subjects); if (result.ChangeResult == ChangeResult.ThanhCong) { a += "Cập nhật thành công"; } else { a += result.Message; } LoadKeHoachVon(); return(a); }
public string CapNhatNhaThau(string stringGridNhaThau) { string madonvi = Request.QueryString["madonvi"]; long idduan = Int64.Parse(Request.QueryString["idduan"]); string excelData = stringGridNhaThau; string[] rowSeparator = new string[] { "|*row*|" }; string[] cellSeparator = new string[] { "|*cell*|" }; string[] dataRows = excelData.Split(rowSeparator, StringSplitOptions.None); string a = ""; GiamSatSetting[] subjects = new GiamSatSetting[dataRows.Length]; for (int i = 0; i < dataRows.Length; i++) { string[] dataCells = dataRows[i].Split(cellSeparator, StringSplitOptions.None); string MaDonVi = dataCells[0]; string IdGoiThau = dataCells[1]; string HinhThucDauThau = dataCells[2]; string TenGiaiDoan = dataCells[3]; string TrangThaiThucHien = dataCells[4]; string TenKetQuaGiamSat = dataCells[5]; string GhiChuGiamSat = dataCells[6]; string GiamSatId = dataCells[7]; GiamSatSetting gss = new GiamSatSetting(); gss.MaDonVi = madonvi; gss.DuAnID = idduan; gss.GoiThauID = Int64.Parse(IdGoiThau); gss.GiaiDoanChonNhaThau = EnumHelper.GetEnumValueFromDescription <GiaiDoanChonNhaThau>(TenGiaiDoan); gss.KetQuaGiamSat = EnumHelper.GetEnumValueFromDescription <KetQuaGiamSat>(TenKetQuaGiamSat); gss.GiamSatID = Int64.Parse(GiamSatId); gss.GhiChu = GhiChuGiamSat; subjects[i] = gss; } ChangeResultSettings result = giamsatService.GiamSat("", "", "", (int)LoaiGiamSat.GiamSatChonNhaThau, subjects); if (result.ChangeResult == ChangeResult.ThanhCong) { a += "Cập nhật thành công"; } else { a += result.Message; } LoadGridNhaThau(); return(a); }
public ChangeResultSettings IsExistsUserGroupName(string moduleId, string maDonVi, string maNhom) { var result = new ChangeResultSettings(); try { ConnectDB.CloseConnection(_connectGs); _connectGs = new OracleConnection(); _connectGs = ConnectDB.GetOracleConnection(_connectGs); var cm = _connectGs.CreateCommand(); cm.CommandText = "usp_check_exists_usergroup"; cm.CommandType = CommandType.StoredProcedure; cm.Parameters.Add(new OracleParameter("moduleId", OracleDbType.Varchar2)).Value = moduleId; cm.Parameters.Add(new OracleParameter("ma_dvi_check", OracleDbType.Varchar2)).Value = maDonVi; cm.Parameters.Add(new OracleParameter("ma_nhom_check", OracleDbType.Varchar2)).Value = maNhom; var op = new OracleParameter("total_result", OracleDbType.Long, 15) { Direction = ParameterDirection.Output }; cm.Parameters.Add(op); cm.ExecuteNonQuery(); var total = Convert.ToInt32(cm.Parameters["total_result"].Value.ToString()); switch (total) { case 0: result.ChangeResult = ChangeResult.ThanhCong; break; default: result.ChangeResult = ChangeResult.ThatBai; break; } } catch (Exception ex) { result.Message = "Có lỗi trong quá trình kiểm tra tên nhóm. Vui lòng thử lại!"; result.ChangeResult = ChangeResult.ThatBai; } return(result); }
public ChangeResultSettings CapNhatLoaiNguonVon(string mdv, string nsd, string pas, string maDonVi, long idDuAn, int loaiNguonVon) { var result = new ChangeResultSettings(); try { ConnectDB.CloseConnection(_connectGs); _connectGs = new OracleConnection(); _connectGs = ConnectDB.GetOracleConnection(_connectGs); var cm = _connectGs.CreateCommand(); cm.CommandText = "usp_CapNhat_DuAn_LoaiNguonVon"; cm.CommandType = CommandType.StoredProcedure; cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("id_duan", OracleDbType.Long)).Value = idDuAn; cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = maDonVi; cm.Parameters.Add(new OracleParameter("loai_nguonvon", OracleDbType.Int32)).Value = loaiNguonVon; var op = new OracleParameter("row_updated", OracleDbType.Long, 15) { Direction = ParameterDirection.Output }; cm.Parameters.Add(op); cm.ExecuteNonQuery(); var rowUpdated = cm.Parameters["row_updated"].Value.ToString(); if (rowUpdated == "0") { result.ChangeResult = ChangeResult.ThatBai; } } catch (Exception ex) { result.ChangeResult = ChangeResult.ThatBai; result.Message = ex.Message; throw; } return(result); }
public ChangeResultSettings XoaNguoiDung(string mdv, string nsd, string pas, string maDonVi, long nguoiDungId) { var result = new ChangeResultSettings(); try { ConnectDB.CloseConnection(_connectGs); _connectGs = new OracleConnection(); _connectGs = ConnectDB.GetOracleConnection(_connectGs); var cm = _connectGs.CreateCommand(); cm.CommandText = "usp_user_delete"; cm.CommandType = CommandType.StoredProcedure; cm.Parameters.Add(new OracleParameter("mdv", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("ma_dvi", OracleDbType.Varchar2)).Value = maDonVi; cm.Parameters.Add(new OracleParameter("so_id_xoa", OracleDbType.Long)).Value = nguoiDungId; var op = new OracleParameter("row_updated", OracleDbType.Long, 15) { Direction = ParameterDirection.Output }; cm.Parameters.Add(op); cm.ExecuteNonQuery(); var rowUpdated = cm.Parameters["row_updated"].Value.ToString(); if (rowUpdated == "" || rowUpdated == "0") { result.ChangeResult = ChangeResult.ThatBai; } } catch (Exception ex) { result.Message = "Có lỗi trong quá trình xóa người dùng. Vui lòng thử lại!"; result.ChangeResult = ChangeResult.ThatBai; } return(result); }
/// <summary> /// update datatable to database /// </summary> /// <param name="columnIds">list column id primary key</param> /// <param name="tableName">ten bang</param> /// <param name="data">du lieu</param> /// <param name="connect">connection</param> /// <returns>ChangeResultSettings</returns> private ChangeResultSettings UpdateDatatable(List <string> columnIds, string tableName, DataTable data, OracleConnection connect) { var returnObject = new ChangeResultSettings(); var aColumnNames = data.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray(); var totalColumn = data.Columns.Count; foreach (DataRow dr in data.Rows) { var listDieuKien = new List <string>(); var sqlUpdate = "update " + tableName + " set "; for (int i = 0; i < totalColumn; i++) { var a = data.Columns[i].DataType; switch (a.Name) { case "String": // column la khoa chinh if (columnIds.Contains(aColumnNames[i])) { if (dr.IsNull(i)) { throw new Exception("bang " + tableName + ", khoa chinh " + aColumnNames[i] + " null"); } listDieuKien.Add(aColumnNames[i] + " = '" + dr[i] + "'"); } // column khac else { if (dr.IsNull(i)) { sqlUpdate += aColumnNames[i] + " = null,"; } else { sqlUpdate += aColumnNames[i] + " = '" + dr[i].ToString().Replace("'", "''") + "',"; } } break; case "DateTime": if (columnIds.Contains(aColumnNames[i])) { if (dr.IsNull(i)) { throw new Exception("bang " + tableName + ", khoa chinh " + aColumnNames[i] + " null"); } // todo: xem lai chuoi dieu kien trong truong hop nay listDieuKien.Add(aColumnNames[i] + " = to_date('" + dr[i] + "', 'mm/dd/yyyy hh:mi:ss AM')"); } else { if (dr.IsNull(i)) { sqlUpdate += aColumnNames[i] + " = null,"; } else { sqlUpdate += aColumnNames[i] + " = to_date('" + dr[i] + "', 'mm/dd/yyyy hh:mi:ss AM'),"; } } break; default: if (columnIds.Contains(aColumnNames[i])) { if (dr.IsNull(i)) { throw new Exception("bang " + tableName + ", khoa chinh " + aColumnNames[i] + " null"); } listDieuKien.Add(aColumnNames[i] + " = " + dr[i]); } else { if (dr.IsNull(i)) { sqlUpdate += aColumnNames[i] + " = null,"; } else { sqlUpdate += aColumnNames[i] + " = " + dr[i] + ","; } } break; } } sqlUpdate += " xoa = " + (int)TinhTrangXoa.ChuaXoa + " where "; var first = true; foreach (var dk in listDieuKien) { if (first) { sqlUpdate += dk; first = false; } else { sqlUpdate += " and " + dk; } } _lastSql = sqlUpdate; _oracleCommand = new OracleCommand(sqlUpdate, connect); _oracleCommand.CommandType = CommandType.Text; var result = _oracleCommand.ExecuteNonQuery(); if (result == 0) { returnObject.ChangeResult = ChangeResult.ThatBai; } } return(returnObject); }
/// <summary> /// insert du lieu vao csdl /// </summary> /// <param name="tableName">ten bang</param> /// <param name="data">du lieu</param> /// <param name="connect">connection</param> /// <returns>ChangeResultSettings</returns> private ChangeResultSettings InsertDatatable(string tableName, DataTable data, OracleConnection connect) { var returnObject = new ChangeResultSettings(); var sColumnNames = string.Join(",", data.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray()); var totalColumn = data.Columns.Count; foreach (DataRow dr in data.Rows) { var sqlInsert = "insert into " + tableName + "(" + sColumnNames + ") values ("; for (int i = 0; i < totalColumn; i++) { var a = data.Columns[i].DataType; switch (a.Name) { case "String": if (dr.IsNull(i)) { sqlInsert += "null,"; } else { sqlInsert += "'" + dr[i].ToString().Replace("'", "''") + "',"; } break; case "DateTime": if (dr.IsNull(i)) { sqlInsert += "null,"; } else { sqlInsert += "to_date('" + dr[i] + "', 'mm/dd/yyyy hh:mi:ss AM'),"; } break; default: if (dr.IsNull(i)) { sqlInsert += "null,"; } else { sqlInsert += dr[i] + ","; } break; } } if (sqlInsert.Substring(sqlInsert.Length - 1, 1) == ",") { sqlInsert = sqlInsert.Substring(0, sqlInsert.Length - 1); } sqlInsert += ")"; _lastSql = sqlInsert; _oracleCommand = new OracleCommand(sqlInsert, connect); _oracleCommand.CommandType = CommandType.Text; var result = _oracleCommand.ExecuteNonQuery(); // todo: check result } return(returnObject); }
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 ChangeResultSettings GiamSat(string mdv, string nsd, string pas, int loaiGiamSat, List <GiamSatSetting> listGiamSat = null) { if (listGiamSat == null || listGiamSat.Count == 0) { return(null); } var result = new ChangeResultSettings(); try { ConnectDB.CloseConnection(_connectGs); _connectGs = new OracleConnection(); _connectGs = ConnectDB.GetOracleConnection(_connectGs); var cm = _connectGs.CreateCommand(); cm.CommandType = CommandType.StoredProcedure; switch (loaiGiamSat) { case (int)LoaiGiamSat.GiamSatKHV: foreach (var gs in listGiamSat) { if (gs.GiamSatID == 0) { cm = _connectGs.CreateCommand(); cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "usp_GS_KHV_Insert"; cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "hoa"; cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = gs.MaDonVi; cm.Parameters.Add(new OracleParameter("id_duan", OracleDbType.Long)).Value = gs.DuAnID; cm.Parameters.Add(new OracleParameter("nam_khv", OracleDbType.Int32)).Value = gs.NamKHV; cm.Parameters.Add(new OracleParameter("giaidoan_khv", OracleDbType.Int32)).Value = (int)gs.GiaiDoanKHV; cm.Parameters.Add(new OracleParameter("kq_giamsat", OracleDbType.Int32)).Value = (int)gs.KetQuaGiamSat; cm.Parameters.Add(new OracleParameter("ghichu_giamsat", OracleDbType.NVarchar2)).Value = gs.GhiChu; cm.Parameters.Add(new OracleParameter("dot_khv", OracleDbType.Int32)).Value = gs.DotKHV; cm.Parameters.Add(new OracleParameter("so_quyetdinh", OracleDbType.Varchar2)).Value = gs.SoQD; var op = new OracleParameter("row_inserted", OracleDbType.Long, 15) { Direction = ParameterDirection.Output }; cm.Parameters.Add(op); cm.ExecuteNonQuery(); var rowInserted = cm.Parameters["row_inserted"].Value.ToString(); if (rowInserted == "" || rowInserted == "0") { throw new Exception("Thêm mới bản ghi giám sát kế hoạch vốn thất bại"); } } else { cm = _connectGs.CreateCommand(); cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "usp_GS_KHV_Update"; cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "hoa"; cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("id_giamsat", OracleDbType.Long)).Value = gs.GiamSatID; cm.Parameters.Add(new OracleParameter("kq_giamsat", OracleDbType.Int32)).Value = (int)gs.KetQuaGiamSat; cm.Parameters.Add(new OracleParameter("ghichu_giamsat", OracleDbType.NVarchar2)).Value = gs.GhiChu; var op = new OracleParameter("row_updated", OracleDbType.Long, 15) { Direction = ParameterDirection.Output }; cm.Parameters.Add(op); cm.ExecuteNonQuery(); var rowUpdated = cm.Parameters["row_updated"].Value.ToString(); if (rowUpdated == "" || rowUpdated == "0") { result.ChangeResult = ChangeResult.ThatBai; } } } break; case (int)LoaiGiamSat.GiamSatHopDong: foreach (var gs in listGiamSat) { if (gs.GiamSatID == 0) { cm = _connectGs.CreateCommand(); cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "usp_GS_HDO_Insert"; cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "hoa"; cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = gs.MaDonVi; cm.Parameters.Add(new OracleParameter("id_hopdong", OracleDbType.Long)).Value = gs.HopDongID; cm.Parameters.Add(new OracleParameter("id_duan", OracleDbType.Long)).Value = gs.DuAnID; cm.Parameters.Add(new OracleParameter("id_goithau", OracleDbType.Long)).Value = gs.GoiThauID; cm.Parameters.Add(new OracleParameter("kq_giamsat", OracleDbType.Int32)).Value = (int)gs.KetQuaGiamSat; cm.Parameters.Add(new OracleParameter("ghichu_giamsat", OracleDbType.NVarchar2)).Value = gs.GhiChu; var op = new OracleParameter("row_inserted", OracleDbType.Long, 15) { Direction = ParameterDirection.Output }; cm.Parameters.Add(op); cm.ExecuteNonQuery(); var rowInserted = cm.Parameters["row_inserted"].Value.ToString(); if (rowInserted == "" || rowInserted == "0") { throw new Exception("Thêm mới bản ghi giám sát hợp đồng thất bại"); } } else { cm = _connectGs.CreateCommand(); cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "usp_GS_HDO_Update"; cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "hoa"; cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("id_giamsat", OracleDbType.Long)).Value = gs.GiamSatID; cm.Parameters.Add(new OracleParameter("kq_giamsat", OracleDbType.Int32)).Value = (int)gs.KetQuaGiamSat; cm.Parameters.Add(new OracleParameter("ghichu_giamsat", OracleDbType.NVarchar2)).Value = gs.GhiChu; var op = new OracleParameter("row_updated", OracleDbType.Long, 15) { Direction = ParameterDirection.Output }; cm.Parameters.Add(op); cm.ExecuteNonQuery(); var rowUpdated = cm.Parameters["row_updated"].Value.ToString(); if (rowUpdated == "" || rowUpdated == "0") { result.ChangeResult = ChangeResult.ThatBai; } } } break; case (int)LoaiGiamSat.GiamSatChonNhaThau: foreach (var gs in listGiamSat) { if (gs.GiamSatID == 0) { cm = _connectGs.CreateCommand(); cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "usp_GS_GThau_Insert"; cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "hoa"; cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = gs.MaDonVi; cm.Parameters.Add(new OracleParameter("id_goithau", OracleDbType.Long)).Value = gs.GoiThauID; cm.Parameters.Add(new OracleParameter("giaidoan_goithau", OracleDbType.Int32)).Value = (int)gs.GiaiDoanChonNhaThau; cm.Parameters.Add(new OracleParameter("id_duan", OracleDbType.Long)).Value = gs.DuAnID; cm.Parameters.Add(new OracleParameter("kq_giamsat", OracleDbType.Int32)).Value = (int)gs.KetQuaGiamSat; cm.Parameters.Add(new OracleParameter("ghichu_giamsat", OracleDbType.NVarchar2)).Value = gs.GhiChu; var op = new OracleParameter("row_inserted", OracleDbType.Long, 15) { Direction = ParameterDirection.Output }; cm.Parameters.Add(op); cm.ExecuteNonQuery(); var rowInserted = cm.Parameters["row_inserted"].Value.ToString(); if (rowInserted == "" || rowInserted == "0") { throw new Exception("Thêm mới bản ghi giám sát tiến trình lựa chọn nhà thầu thất bại"); } } else { cm = _connectGs.CreateCommand(); cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "usp_GS_GThau_Update"; cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "hoa"; cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = ""; cm.Parameters.Add(new OracleParameter("id_giamsat", OracleDbType.Long)).Value = gs.GiamSatID; cm.Parameters.Add(new OracleParameter("kq_giamsat", OracleDbType.Int32)).Value = (int)gs.KetQuaGiamSat; cm.Parameters.Add(new OracleParameter("ghichu_giamsat", OracleDbType.NVarchar2)).Value = gs.GhiChu; var op = new OracleParameter("row_updated", OracleDbType.Long, 15) { Direction = ParameterDirection.Output }; cm.Parameters.Add(op); cm.ExecuteNonQuery(); var rowUpdated = cm.Parameters["row_updated"].Value.ToString(); if (rowUpdated == "" || rowUpdated == "0") { result.ChangeResult = ChangeResult.ThatBai; } } } break; } } catch (OracleException ex) { if (ex.Number == 20104) { result.ChangeResult = ChangeResult.ThatBai; result.Message = "Bạn không có quyền thực hiện thao tác này!"; } } catch (Exception ex) { result.ChangeResult = ChangeResult.ThatBai; result.Message = ex.Message; } return(result); }