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 IsExistsEmail(string email)
{
var result = new ChangeResultSettings();
try
{
ConnectDB.CloseConnection(_connectGs);
_connectGs = new OracleConnection();
_connectGs = ConnectDB.GetOracleConnection(_connectGs);
var cm = _connectGs.CreateCommand();
cm.CommandText = "usp_check_exists_email";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add(new OracleParameter("email_check", OracleDbType.Varchar2)).Value = email;
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 email. Vui lòng thử lại!";
result.ChangeResult = ChangeResult.ThatBai;
}
return result;
}
public ChangeResultSettings CapNhatNhomNguoiDung(string mdv, string nsd, string pas, NhomNguoiDungModel nhomNguoiDung)
{
var result = new ChangeResultSettings();
try
{
result.ChangeResult=ChangeResult.ThanhCong;
}
catch (Exception)
{
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 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 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 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 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;
}