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);
}
