public DataTable ChiTietHopDong(string mdv, string nsd, string pas, string maDonVi, long idHopDong)
{
try
{
ConnectDB.CloseConnection(_connectGs);
_connectGs = new OracleConnection();
_connectGs = ConnectDB.GetOracleConnection(_connectGs);
var cm = _connectGs.CreateCommand();
cm.CommandText = "usp_ChiTiet_HopDong";
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_hopdong", OracleDbType.Long)).Value = idHopDong;
cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = maDonVi;
cm.Parameters.Add(new OracleParameter("cs_lke", OracleDbType.RefCursor)).Direction =
ParameterDirection.Output;
var tableGs = new DataTable();
_oracleAdapter = new OracleDataAdapter(cm);
_oracleAdapter.Fill(tableGs);
return(tableGs);
}
catch (Exception)
{
return(null);
}
finally
{
ConnectDB.CloseConnection(_connectGs);
}
}
public static List <T> ReadDB(string nameColumn)
{
using (var command = new SQLiteCommand("SELECT * FROM PeopleToday", ConnectDB.connect))
{
if (column.Count > 0)
{
column.Clear();
}
ConnectDB.OpenConnection();
var result = command.ExecuteReader();
if (result.HasRows)
{
for (int i = 0; result.Read(); i++)
{
try
{
column.Add((T)result[nameColumn]);
}
catch (System.IndexOutOfRangeException)
{
i--;
var commandALT = new SQLiteCommand($"ALTER TABLE PeopleToday\n ADD COLUMN {nameColumn} text;", ConnectDB.connect);
var resultALT = commandALT.ExecuteReader();
}
}
}
ConnectDB.CloseConnection();
}
return(column);
}
public DataTable DanhSachLoaiDuAn(string mdv, string nsd, string pas)
{
try
{
ConnectDB.CloseConnection(_connectGs);
_connectGs = new OracleConnection();
_connectGs = ConnectDB.GetOracleConnection(_connectGs);
var cm = _connectGs.CreateCommand();
cm.CommandText = "usp_danhsach_loai_duan";
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("cs_lke", OracleDbType.RefCursor)).Direction =
ParameterDirection.Output;
var tableGs = new DataTable();
_oracleAdapter = new OracleDataAdapter(cm);
_oracleAdapter.Fill(tableGs);
return(tableGs);
}
catch (Exception)
{
return(null);
}
finally
{
ConnectDB.CloseConnection(_connectGs);
}
}
public DataTable DanhSachGiaiDoanKHV(string mdv, string nsd, string pas, string maDonVi, long idDuAn, int nam)
{
try
{
ConnectDB.CloseConnection(_connectGs);
_connectGs = new OracleConnection();
_connectGs = ConnectDB.GetOracleConnection(_connectGs);
var cm = _connectGs.CreateCommand();
cm.CommandText = "usp_DanhSach_KHV";
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("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = maDonVi;
cm.Parameters.Add(new OracleParameter("nam_khv", OracleDbType.Int32)).Value = nam;
cm.Parameters.Add(new OracleParameter("id_duan", OracleDbType.Long)).Value = idDuAn;
cm.Parameters.Add(new OracleParameter("cs_lke", OracleDbType.RefCursor)).Direction = ParameterDirection.Output;
var tableGs = new DataTable();
_oracleAdapter = new OracleDataAdapter(cm);
_oracleAdapter.Fill(tableGs);
return(tableGs);
}
catch (Exception)
{
return(null);
}
finally
{
ConnectDB.CloseConnection(_connectGs);
}
}
public user FindUserByUsername(string username)
{
connection = connectionDB.OpenConnection();
query = "SELECT * FROM user WHERE username = @username";
MySqlCommand command = new MySqlCommand(query, connection);
command.Parameters.Add(new MySqlParameter("username", username));
user user = new user();
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
user.id = (int)reader[0];
user.name = (string)reader[1];
user.password = (string)reader[2];
user.role = (string)reader[3];
user.username = (string)reader[4];
}
}
connectionDB.CloseConnection();
return(user);
}
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 static void Delete(long id)
{
using (var command = new SQLiteCommand($"DELETE FROM PeopleToday WHERE Id = {id};", ConnectDB.connect))
{
ConnectDB.OpenConnection();
var result = command.ExecuteReader();
ConnectDB.CloseConnection();
}
}
public static void Insert(string[] column, PeopleToday today)
{
using (var command = new SQLiteCommand($"INSERT INTO PeopleToday ({column[0]}, {column[1]}, {column[2]}, {column[3]}, {column[4]})" +
$" VALUES ('{today.id}', '{today.name}', '{today.app}', '{today.date}', '{today.coordinate}'); ", ConnectDB.connect))
{
ConnectDB.OpenConnection();
var result = command.ExecuteReader();
ConnectDB.CloseConnection();
}
}
public List <package> SearchPackage(string name)
{
connection = connectionDB.OpenConnection();
query = "SELECT * FROM package WHERE name = @name";
MySqlCommand command = new MySqlCommand(query, connection);
command.Parameters.Add(new MySqlParameter("name", name));
package pack = new package();
List <package> packages = new List <package>();
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
pack.id = (int)reader[0];
pack.description = (string)reader[1];
pack.name = (string)reader[2];
pack.destinationCity_id = (int)reader[4];
pack.receiver_id = (int)reader[5];
pack.sender_id = (int)reader[6];
pack.senderCity_id = (int)reader[7];
String track = reader[3].ToString();
if (track.Equals("1"))
{
pack.tracking = true;
}
else
{
pack.tracking = false;
}
packages.Add(pack);
}
}
connectionDB.CloseConnection();
return(packages);
}
public static void Update(long id, PeopleToday today, string[] column)
{
using (var command = new SQLiteCommand($"UPDATE PeopleToday SET " +
$"{column[0]} = '{today.id}'," +
$"{column[1]} = '{today.name}', " +
$"{column[2]} = '{today.app}'," +
$"{column[3]} = '{today.date}'," +
$"{column[4]} = '{today.coordinate}' " +
$"WHERE Id = {id}; ", ConnectDB.connect))
{
ConnectDB.OpenConnection();
var result = command.ExecuteReader();
ConnectDB.CloseConnection();
}
}
public object[] DanhSachHopDong(string mdv, string nsd, string pas, string maDonVi, long idGoiThau, int pageSize, int pageIndex = 1)
{
try
{
ConnectDB.CloseConnection(_connectGs);
_connectGs = new OracleConnection();
_connectGs = ConnectDB.GetOracleConnection(_connectGs);
var cm = _connectGs.CreateCommand();
cm.CommandText = "usp_DanhSach_HDO";
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("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = maDonVi;
cm.Parameters.Add(new OracleParameter("id_goithau", OracleDbType.Long)).Value = idGoiThau;
cm.Parameters.Add(new OracleParameter("page_index", OracleDbType.Long)).Value = pageIndex;
cm.Parameters.Add(new OracleParameter("page_size", OracleDbType.Long)).Value = pageSize;
var op = new OracleParameter("total_record", OracleDbType.Long, 15)
{
Direction = ParameterDirection.Output
};
cm.Parameters.Add(op);
cm.Parameters.Add(new OracleParameter("cs_lke", OracleDbType.RefCursor)).Direction = ParameterDirection.Output;
var tableGs = new DataTable();
_oracleAdapter = new OracleDataAdapter(cm);
_oracleAdapter.Fill(tableGs);
var totalRecord = cm.Parameters["total_record"].Value.ToString();
var arrObject = new object[2];
arrObject[0] = tableGs;
arrObject[1] = totalRecord;
return(arrObject);
}
catch (Exception)
{
return(null);
}
finally
{
ConnectDB.CloseConnection(_connectGs);
}
}
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 static List <PeopleToday> AnotherReadDB(PeopleToday today)
{
using (LibraryContext context = new LibraryContext())
{
context.PeopleToday.Add(today);
try
{
context.SaveChanges();
}
catch (System.Data.Entity.Infrastructure.DbUpdateConcurrencyException)
{
}
catch (System.Data.Entity.Infrastructure.DbUpdateException)
{
ConnectDB.OpenConnection();
var commandALT = new SQLiteCommand($"ALTER TABLE PeopleToday\n ADD COLUMN coordinate text;", ConnectDB.connect);
var resultALT = commandALT.ExecuteReader();
ConnectDB.CloseConnection();
}
return(context.PeopleToday.ToList());
}
}
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);
}
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);
}
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 object[] TimKiemDuAn(string mdv, string nsd, string pas, SearchProjectSetting searchProjectSetting,
int pageSize, int pageIndex = 1)
{
try
{
ConnectDB.CloseConnection(_connectGs);
_connectGs = new OracleConnection();
_connectGs = ConnectDB.GetOracleConnection(_connectGs);
var cm = _connectGs.CreateCommand();
cm.CommandText = "usp_TimKiem_DuAn";
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("ma_duan", OracleDbType.Varchar2)).Value =
searchProjectSetting.MaDuAn;
cm.Parameters.Add(new OracleParameter("loai_duan", OracleDbType.Varchar2)).Value =
searchProjectSetting.LoaiDuAn;
cm.Parameters.Add(new OracleParameter("nhom_duan", OracleDbType.Varchar2)).Value =
searchProjectSetting.NhomDuAn == NhomDuAn.KhongXacDinh?"":searchProjectSetting.NhomDuAn.ToString();
cm.Parameters.Add(new OracleParameter("loai_nguonvon", OracleDbType.Int32)).Value =
searchProjectSetting.LoaiNguonVon == LoaiNguonVon.KhongXacDinh ? -1 : (int)searchProjectSetting.LoaiNguonVon;
cm.Parameters.Add(new OracleParameter("phancap", OracleDbType.Varchar2)).Value =
searchProjectSetting.PhanCap == LoaiPhanCap.KhongXacDinh ? "" : searchProjectSetting.PhanCap.ToString();
cm.Parameters.Add(new OracleParameter("ma_donvi_quanly", OracleDbType.Varchar2)).Value =
searchProjectSetting.MaDonViQuanLy;
cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value =
searchProjectSetting.MaDonViThucHien;
cm.Parameters.Add(new OracleParameter("toantu_tongvon", OracleDbType.Varchar2)).Value =
searchProjectSetting.TongVonDauTuToanTu;
cm.Parameters.Add(new OracleParameter("tongvon", OracleDbType.Long)).Value =
searchProjectSetting.TongVonDauTu;
cm.Parameters.Add(new OracleParameter("toantu_nam_bd", OracleDbType.Varchar2)).Value =
searchProjectSetting.NamBatDauToanTu;
cm.Parameters.Add(new OracleParameter("nam_bd", OracleDbType.Int32)).Value =
searchProjectSetting.NamBatDau;
cm.Parameters.Add(new OracleParameter("toantu_nam_kt", OracleDbType.Varchar2)).Value =
searchProjectSetting.NamKetThucToanTu;
cm.Parameters.Add(new OracleParameter("nam_kt", OracleDbType.Int32)).Value =
searchProjectSetting.NamKetThuc;
cm.Parameters.Add(new OracleParameter("page_index", OracleDbType.Int32)).Value = pageIndex;
cm.Parameters.Add(new OracleParameter("page_size", OracleDbType.Int32)).Value = pageSize;
var op = new OracleParameter("total_record", OracleDbType.Long, 15)
{
Direction = ParameterDirection.Output
};
cm.Parameters.Add(op);
cm.Parameters.Add(new OracleParameter("cs_lke", OracleDbType.RefCursor)).Direction =
ParameterDirection.Output;
var tableGs = new DataTable();
_oracleAdapter = new OracleDataAdapter(cm);
_oracleAdapter.Fill(tableGs);
var totalRecord = cm.Parameters["total_record"].Value.ToString();
var arrObject = new object[2];
arrObject[0] = tableGs;
arrObject[1] = totalRecord;
return(arrObject);
}
catch (Exception ex)
{
return(null);
}
finally
{
ConnectDB.CloseConnection(_connectGs);
}
}