public bool CreateMyCustomer(Customer customer, out string errorMsg)
{
errorMsg = String.Empty;
using (OracleConnection conn = _connectionDao.GetConnection())
{
OracleTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "MY_TEST_PACKAGE.USR_CUSTOMER_INST";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("PCUSNAME", OracleDbType.Varchar2, customer.CusName,
ParameterDirection.Input);
cmd.Parameters.Add("PCUSFATHERNAME", OracleDbType.Varchar2, customer.CusFatherName,
ParameterDirection.Input);
cmd.Parameters.Add("PCUSMOTHERNAME", OracleDbType.Varchar2, customer.CusMotherName,
ParameterDirection.Input);
cmd.Parameters.Add("PCUSPHONE", OracleDbType.Varchar2, customer.CusPhone,
ParameterDirection.Input);
OracleParameter parError = cmd.Parameters.Add("PERROR", OracleDbType.Varchar2,
ParameterDirection.Output);
parError.Size = 500;
cmd.ExecuteNonQuery();
if (parError.Status == OracleParameterStatus.Success)
{
errorMsg = $"{parError.Value}";
}
}
tran.Commit();
return(errorMsg.ToUpper() == "NOERROR");
}
#if DEBUG
catch (OracleException oraEx)
{
tran?.Rollback();
throw new DatabaseException(oraEx.Message, ExceptionConstants.CommonUserExceptionMessage);
}
#endif
catch (Exception ex)
{
tran?.Rollback();
throw new DatabaseException(ex.Message, ExceptionConstants.CommonUserExceptionMessage);
}
}
}
public override void Save()
{
OracleConnection connection = GetConnection();
OracleTransaction transaction = null;
try
{
connection.Open();
transaction = connection.BeginTransaction();
foreach (var entity in eStates)
{
if (entity.EState == State.NEW)
{
InsertOne(entity.Entity, connection, transaction);
}
if (entity.EState == State.DELETED)
{
DeleteOne(entity.Entity, connection, transaction);
}
if (entity.EState == State.CHANGED)
{
UpdateOne(entity.Entity, connection, transaction);
}
}
transaction.Commit();
}
catch (Exception ex)
{
transaction?.Rollback();
Console.WriteLine(ex.Message);
}
finally
{
connection?.Close();
}
}
public override void Save(IEnumerable <Entity> entitiesForInsert, IEnumerable <Entity> entitiesForUpdate, IEnumerable <Entity> entitiesForDelete)
{
OracleConnection connection = GetConnection();
OracleTransaction transaction = null;
try
{
connection.Open();
transaction = connection.BeginTransaction();
if ((entitiesForInsert != null) && entitiesForInsert.Count() > 0)
{
InsertImpl(entitiesForInsert, connection, transaction);
}
if ((entitiesForUpdate != null) && entitiesForUpdate.Count() > 0)
{
UpdateImpl(entitiesForUpdate, connection, transaction);
}
if ((entitiesForDelete != null) && entitiesForDelete.Count() > 0)
{
DeleteImpl(entitiesForDelete, connection, transaction);
}
transaction.Commit();
}
catch (Exception ex)
{
transaction?.Rollback();
Console.WriteLine(ex.Message);
}
finally
{
connection?.Close();
}
}
public void Dispose()
{
_trans.Rollback();
_membershipTrans.Rollback();
}
/************************************************************
* Function name : SetNotice
* Purpose : 공지사항 저장
*
* Input : string[] rParams
* Output : String
*************************************************************/
public string SetNotice(params object[] rParams)
{
string xRtn = Boolean.FalseString;
string xDebug = string.Empty;
Database db = base.GetDataBase("LMS");
OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection();
xCnnLMS.Open();
OracleTransaction xTransLMS = null;
OracleCommand xCmdLMS = null;
try
{
xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
xCmdLMS = base.GetSqlCommand(db);
xCmdLMS.Connection = xCnnLMS;
xCmdLMS.Transaction = xTransLMS;
string xFilePath = string.Empty;
string[] rMasterParams = (string[])rParams[0];
object[,] rDetailParams = (object[, ])rParams[1];
string xSeq = rMasterParams[7];
string xSendFLG = rMasterParams[8];
//string xSeq = GetMaxIDOfCode(new string[] { "not_no" }, xCmdLMS);
//xDebug = "seq : " + xSeq;
try
{
string xSql = string.Empty;
xSql += " INSERT INTO t_notice ( ";
xSql += " not_no, "; // 공지사항 번호
xSql += " not_kind, "; // 공지사항 타입 m_cd = '0055' , 000001: 일반공지 , 000002: 과정공지
xSql += " open_course_id, "; // 과정 ID
xSql += " notice_begin_dt, "; // 공지사항 게시일자
xSql += " notice_end_dt, "; // 공지사항 종료일자
xSql += " not_sub, "; // 공지사항 제목
xSql += " not_content, "; // 공지사항 내용
xSql += " hit_cnt, "; // 읽은수
xSql += " del_yn, "; // 삭제유무
xSql += " send_flg, "; // 전송유무 1: 전송대기, 2: 전송
xSql += " company_kind, "; // 업체구분 m_cd = 0061 회사구분 (그룹사, 사업자 위수탁)
xSql += " upt_id, "; // 수정자 ID
xSql += " upt_dt, "; // 수정일시
xSql += " ins_id, "; // 생성자 ID
xSql += " ins_dt) "; // 생성일시
xSql += " VALUES( ";
xSql += string.Format(" {0}, ", Convert.ToInt32(xSeq)); // 공지사항 번호
xSql += string.Format(" '{0}', ", rMasterParams[0]); // 공지사항 타입 m_cd = '0055' , 000001: 일반공지 , 000002: 과정공지
xSql += string.Format(" '{0}', ", rMasterParams[1]); // 과정 ID (일반공지의 경우 Empty Value)
xSql += string.Format(" TO_DATE('{0}','YYYY.MM.DD.HH24MISS'), ", rMasterParams[2] + "000000"); // 공지 게시일자
xSql += string.Format(" TO_DATE('{0}','YYYY.MM.DD.HH24MISS'), ", rMasterParams[3] + "235959"); // 공지 종료일자
xSql += string.Format(" '{0}', ", rMasterParams[4]); // 공지사항 제목
xSql += " :NOTCONTENT, ";
//xSql += string.Format(" '{0}', ", rMasterParams[5]); // 공지사항 내용
xSql += " 0, "; // 읽은수
xSql += " 'N', "; // 삭제유무
xSql += string.Format(" '{0}', ", xSendFLG); // 전송유무 1: 전송대기, 2: 전송
xSql += string.Format(" '{0}', ", rMasterParams[9]); // 업체구분 m_cd = 0061 회사구분 (그룹사, 사업자 위수탁)
xSql += string.Format(" '{0}', ", rMasterParams[6]); // 수정자 ID
xSql += " SYSDATE, "; // 수정일시
xSql += string.Format(" '{0}', ", rMasterParams[6]); // 생성자 ID
xSql += " SYSDATE) "; // 생성일시
OracleParameter NOTCONTENT = new OracleParameter();
NOTCONTENT.OracleType = OracleType.Clob;
NOTCONTENT.ParameterName = "NOTCONTENT";
NOTCONTENT.Value = rMasterParams[5];
xCmdLMS.Parameters.Add(NOTCONTENT);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xTransLMS);
for (int i = 0; i < rDetailParams.GetLength(0); i++)
{
byte[] xFileData = (byte[])rDetailParams[i, 0];
string[] xTemp = (string[])rDetailParams[i, 1];
xSql = string.Empty;
xSql += " INSERT INTO t_notice_att ( ";
xSql += " NOT_NO, ";
xSql += " FILE_NM, ";
xSql += " att_file ";
xSql += " ) ";
xSql += " VALUES ( ";
xSql += string.Format(" {0}, ", Convert.ToInt32(xSeq));
xSql += string.Format(" '{0}', ", xTemp[0]);
xSql += " :ATTFILE ";
xSql += " ) ";
OracleParameter ATTFILE = new OracleParameter();
ATTFILE.OracleType = OracleType.Blob;
ATTFILE.ParameterName = "ATTFILE";
ATTFILE.Value = xFileData;
xCmdLMS.Parameters.Add(ATTFILE);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xTransLMS);
}
if (xSendFLG == "1") // 전송대상이면
{
OracleParameter[] oOraParams = new OracleParameter[2];
oOraParams[0] = base.AddParam("p_in_table", OracleType.VarChar, "T_NOTICE");
oOraParams[1] = base.AddParam("p_out_table", OracleType.VarChar, "T_BULL");
int i = base.Execute(db, CommandType.StoredProcedure, "pkg_lms_datasync.lms_export", oOraParams, xTransLMS);
}
xTransLMS.Commit(); // 트랜잭션 커밋
xRtn = Boolean.TrueString;
}
catch (Exception ex)
{
// 파일 제거
if (xFilePath.Trim() != "")
{
File.Delete(xFilePath);
}
xTransLMS.Rollback(); // Exception 발생시 롤백처리...
//throw new Exception(xDebug + " | " + ex.Message);
bool rethrow = ExceptionPolicy.HandleException(ex, "Propagate Policy");
if (rethrow)
{
throw;
}
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(xRtn);
}
/// <summary>
/// 回滚事务
/// </summary>
public void Rollback()
{
p_trans.Rollback();
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static int ExecuteSqlTranWithParaMeter(Dictionary <string, OracleParameter[]> dicParameter)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
OracleTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
foreach (string sql in dicParameter.Keys)
{
string strsql = sql;
string dd = "";
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.Parameters.Clear();
for (int i = 0; i < dicParameter[sql].Length; i++)
{
if (dicParameter[sql][i] == null)
{
continue;
}
cmd.Parameters.Add(dicParameter[sql][i]);
dd += dicParameter[sql][i].ParameterName + "-->" + dicParameter[sql][i].Value.ToString() + "\r\n";
}
try
{
count += cmd.ExecuteNonQuery();
}
catch (Exception es)
{
LogHelper.Log.Error("执行单条数据报错,原因：" + es.Message);
throw new Exception();
}
}
}
tx.Commit();
return(count);
}
catch (Exception ex)
{
//Trace.WriteLine("事务提交报错" + "原因：" + ex.Message);
LogHelper.Log.Error("事务提交报错,原因：" + ex.Message);
tx.Rollback();
return(0);
}
finally
{
cmd.Dispose();
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static bool ExecuteSqlTran(string conStr, List <CommandInfo> cmdList)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo c in cmdList)
{
if (!String.IsNullOrEmpty(c.CommandText))
{
PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters);
if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (c.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须符合select count(..的格式");
//return false;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须大于0");
//return false;
}
if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须等于0");
//eturn false;
}
continue;
}
int res = cmd.ExecuteNonQuery();
if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须有影像行");
// return false;
}
}
}
tx.Commit();
return(true);
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw E;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
/// <summary>
/// 事务回滚
/// </summary>
public void Rollback()
{
m_Transaction.Rollback();
}
/// <summary>
/// Apply the given changeset.
/// </summary>
/// <param name="change_set"></param>
public override void ApplyChange(ChangeSet change_set)
{
if (change_set != null && change_set.Changes != null)
{
foreach (Change change in change_set.Changes)
{
switch (change.Type)
{
case ChangeType.Create:
foreach (OsmGeo geo in change.OsmGeo)
{
// start applying the simplechange.
OracleTransaction trans = _connection.BeginTransaction();
try
{
if (geo is Node)
{
this.Create(geo as Node);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"+(n:{0})", geo.Id.Value);
}
else if (geo is Way)
{
this.Create(geo as Way);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"+(w:{0})", geo.Id.Value);
}
else if (geo is Relation)
{
this.Create(geo as Relation);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"+(r:{0})", geo.Id.Value);
}
trans.Commit();
}
catch (OracleException ex)
{
trans.Rollback();
if (!_pragmatic)
{
throw ex;
}
else
{
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"+(E:{0}-{1})", geo.Id.Value, geo.Type.ToString());
}
}
}
break;
case ChangeType.Delete:
foreach (OsmGeo geo in change.OsmGeo)
{
// start applying the simplechange.
OracleTransaction trans = _connection.BeginTransaction();
try
{
if (geo is Node)
{
this.Delete(geo as Node);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"-(n:{0})", geo.Id.Value);
}
else if (geo is Way)
{
this.Delete(geo as Way);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"-(w:{0})", geo.Id.Value);
}
else if (geo is Relation)
{
this.Delete(geo as Relation);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"-(r:{0})", geo.Id.Value);
}
trans.Commit();
}
catch (OracleException ex)
{
trans.Rollback();
if (!_pragmatic)
{
throw ex;
}
else
{
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"-(E:{0}-{1})", geo.Id.Value, geo.Type.ToString());
}
}
}
break;
case ChangeType.Modify:
foreach (OsmGeo geo in change.OsmGeo)
{
// start applying the simplechange.
OracleTransaction trans = _connection.BeginTransaction();
try
{
if (geo is Node)
{
this.Modify(geo as Node);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"/(n:{0})", geo.Id.Value);
}
else if (geo is Way)
{
this.Modify(geo as Way);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"/(w:{0})", geo.Id.Value);
}
else if (geo is Relation)
{
this.Modify(geo as Relation);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"/(r:{0})", geo.Id.Value);
}
trans.Commit();
}
catch (OracleException ex)
{
trans.Rollback();
if (!_pragmatic)
{
throw ex;
}
else
{
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information,
"/(E:{0}-{1})", geo.Id.Value, geo.Type.ToString());
}
}
}
break;
}
}
}
}
/// <summary>
///返回结果集的第一行第一列
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-OleDb command</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回结果集的第一行第一列</returns>
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
try
{
//execute the command & return the results
object obj = cmd.ExecuteScalar();
if (transaction != null)
{
transaction.Commit();
}
return obj;
}
catch
{
if (transaction != null)
{
// Rollback the transaction
transaction.Rollback();
}
return null;
}
}
private static void TryRollback(OracleTransaction transaction)
{
try
{
if (transaction != null)
transaction.Rollback();
}
catch
{
}
}
/// <summary>
/// 返回执行响应的行数
/// </summary>
/// <param name="trans">command中执行的事物（OracleTransaction）</param>
/// <param name="cmdType">执行类型（过程/语句）</param>
/// <param name="cmdText">执行的过程名称/查询语句</param>
/// <param name="commandParameters">command执行的OracleParameter[]数组</param>
/// <returns>int 返回command执行响应的行数</returns>
public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
int val = 0;
OracleCommand cmd = new OracleCommand();
try
{
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//Close(trans.Connection);
//Dispose(trans.Connection);
trans.Commit();
return val;
}
catch
{
trans.Rollback();
return val;
}
}
public void trans_execute_sql(string strSql, OracleConnection objConnection, OracleTransaction objTransaction)
{
//objConnection=open_connection();
objCommand = new OracleCommand(strSql, objConnection);
objCommand.CommandType = CommandType.Text;
try
{
//objCommand.Transaction=objTransaction;
objCommand.ExecuteNonQuery();
err_flag = false;
}
catch (OracleException objError)
{
if (objError.Message.Substring(0, 21) == "Table does not exist.")
{
ErrorStr = ErrorMsg(objConnection, "MSG1201");
err_flag = true;
}
else if (objError.Message.Substring(0, 28) == "ORA-00001: unique constraint")
{
ErrorStr = ErrorMsg(objConnection, "MSG1103");
err_flag = true;
}
else if (objError.Message.Substring(0, 31) == "ORA-02292: integrity constraint")
{
ErrorStr = ErrorMsg(objConnection, "MSG1107");
err_flag = true;
}
else if ((objError.Message.Length >= 87) && (objError.Message.Substring(59, 30) == "ORA-00904: invalid column name"))
{
ErrorStr = ErrorMsg(objConnection, "MSG1202");
err_flag = true;
}
else
{
ErrorStr = objError.Message;
err_flag = true;
}
objTransaction.Rollback();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
catch (Exception objError)
{
ErrorStr = objError.Message;
objTransaction.Rollback();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
err_flag = true;
}
/*finally
{
//objConnection.Close();
null;
}*/
}
public string execute_transaction(string[] strSql)
{
objConnection = open_connection();
try
{
if (open_con)
{
objTransaction = objConnection.BeginTransaction();
Int32 i = 0;
string strQuery = "";
for (i = 0; i <= strSql.Length - 1; i++)
{
strQuery = "";
strQuery = strSql[i];
if (strQuery.Length != 0)
{
objCommand = new OracleCommand(strQuery, objConnection);
objCommand.CommandType = CommandType.Text;
try
{
objCommand.ExecuteNonQuery();
err_flag = false;
}
catch (Exception objError)
{
ErrorStr = objError.Message;
err_flag = true;
objTransaction.Rollback();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
}
}
if (!err_flag)
{
objTransaction.Commit();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
else
{
objTransaction.Rollback();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
}
else
{
ErrorStr = "Error : Unable to connect to database.";
err_flag = true;
}
}
catch (Exception objError)
{
ErrorStr = objError.Message;
err_flag = true;
objTransaction.Rollback();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return ErrorStr;
}
/************************************************************
* Function name : SetExam
* Purpose :
* Input :
* Output : string
*************************************************************/
#region public string SetExam(string rParam)
public string SetExam(string rParam)
{
Database db = null;
string xRtn = string.Empty;
string xSql = string.Empty;
try
{
db = base.GetDataBase("LMS"); //Database 생성
using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS");
{
xCnnLMS.Open();
OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
OracleCommand xCmdLMS = null;
try
{
xCmdLMS = base.GetSqlCommand(db);
OracleParameter[] xPara = null;
ArrayList xarr = STR2MD2ArrayList(rParam);
string[] xParam;
for (int i = 0; i < xarr.Count; i++)
{
//xarr.Add(new string[] { dr["USER_ID"].ToString()
// , dr["OPEN_COURSE_ID"].ToString()
// , dr["COURSE_RESULT_SEQ"].ToString()
// , dr["COURSE_ID"].ToString()
// , dr["QUESTION_ID"].ToString()
// , xAnswer
// });
xParam = (string[])xarr[i];
//--USER_ID
//--OPEN_COURSE_ID
//--COURSE_RESULT_SEQ
//--COURSE_ID
//--QUESTION_ID
xSql = " SELECT OPEN_COURSE_ID FROM T_ASSESS_RESULT ";
xSql += " WHERE USER_ID = '" + xParam[0] + "' ";
xSql += " AND OPEN_COURSE_ID = '" + xParam[1] + "' ";
xSql += " AND COURSE_RESULT_SEQ = " + xParam[2] + " ";
xSql += " AND COURSE_ID = '" + xParam[3] + "' ";
xSql += " AND QUESTION_ID = '" + xParam[4] + "' ";
xCmdLMS.CommandText = xSql;
object xobj = base.ExecuteScalar(db, xCmdLMS, xTransLMS);
if (xobj != null && xobj.ToString() != string.Empty)
{
//update
xSql = @" UPDATE T_ASSESS_RESULT SET
USER_ANSWER = :USER_ANSWER
, UPT_ID = :USER_ID
, UPT_DT = SYSDATE
WHERE USER_ID = :USER_ID
AND OPEN_COURSE_ID = :OPEN_COURSE_ID
AND COURSE_RESULT_SEQ = :COURSE_RESULT_SEQ
AND COURSE_ID = :COURSE_ID
AND QUESTION_ID = :QUESTION_ID ";
xPara = new OracleParameter[6];
xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xParam[0]);
xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xParam[1]);
xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xParam[2]);
xPara[3] = base.AddParam("COURSE_ID", OracleType.VarChar, xParam[3]);
xPara[4] = base.AddParam("QUESTION_ID", OracleType.VarChar, xParam[4]);
xPara[5] = base.AddParam("USER_ANSWER", OracleType.VarChar, xParam[5]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
}
else
{
//insert
xSql = @" INSERT INTO T_ASSESS_RESULT
(USER_ID, OPEN_COURSE_ID, COURSE_RESULT_SEQ, COURSE_ID, QUESTION_ID
, USER_ANSWER
, QUESTION_SEQ
, INS_ID, INS_DT, UPT_ID, UPT_DT)
VALUES
(:USER_ID, :OPEN_COURSE_ID, :COURSE_RESULT_SEQ, :COURSE_ID, :QUESTION_ID
, :USER_ANSWER
, :QUESTION_SEQ
, :USER_ID, SYSDATE, :USER_ID, SYSDATE) ";
xPara = new OracleParameter[7];
xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xParam[0]);
xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xParam[1]);
xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xParam[2]);
xPara[3] = base.AddParam("COURSE_ID", OracleType.VarChar, xParam[3]);
xPara[4] = base.AddParam("QUESTION_ID", OracleType.VarChar, xParam[4]);
xPara[5] = base.AddParam("USER_ANSWER", OracleType.VarChar, xParam[5]);
xPara[6] = base.AddParam("QUESTION_SEQ", OracleType.Number, i + 1);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
}
}
xRtn = "Y";
xTransLMS.Commit(); //트렌잭션 커밋
}
catch (Exception ex)
{
// 트랜잭션 롤백
xTransLMS.Rollback();
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
if (xCnnLMS != null)
{
if (xCnnLMS.State == ConnectionState.Open)
{
xCnnLMS.Close();
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
db = null;
}
return(xRtn);//리터값
}
public Boolean ConfereProdutoBonus(ConferenciaBonus dados)
{
OracleConnection connection = DataBase.novaConexao();
OracleTransaction transacao = connection.BeginTransaction();
OracleCommand exec = connection.CreateCommand();
StringBuilder conferenteCab = new StringBuilder();
StringBuilder validaPrimeiroVol = new StringBuilder();
StringBuilder confereBonusi = new StringBuilder();
StringBuilder confereBonusiConf = new StringBuilder();
StringBuilder confereBonusiVolume = new StringBuilder();
exec.Transaction = transacao;
try
{
//Atribui conferente no cabeçalho do bônus
conferenteCab.Append($"UPDATE PCBONUSC SET DATARM = TRUNC(SYSDATE), CODFUNCRM = {dados.Codfuncconf} WHERE NUMBONUS = {dados.Numbonus}");
exec.CommandText = conferenteCab.ToString();
OracleDataReader cabBonus = exec.ExecuteReader();
//Confere item na PCBONUSI
confereBonusi.Append($"UPDATE PCBONUSI SET QTENTRADA = QTENTRADA + {dados.Qtconf}, QTAVARIA = QTAVARIA + {dados.Qtavaria}, DTVALIDADE = TO_DATE('{dados.Dtvalidade}', 'DD/MM/YYYY') ");
confereBonusi.Append($" WHERE NUMBONUS = {dados.Numbonus} AND CODPROD = {dados.Codprod}");
exec.CommandText = confereBonusi.ToString();
OracleDataReader bonusi = exec.ExecuteReader();
//Confere item na PCBONUSICONF
confereBonusiConf.Append("INSERT INTO PCBONUSICONF (NUMBONUS, CODPROD, DATACONF, DATAVALIDADE, CODFUNCCONF, NUMLOTE, QT, QTAVARIA, CODAUXILIAR)");
confereBonusiConf.Append($" VALUES ({dados.Numbonus}, {dados.Codprod}, SYSDATE, TO_DATE('{dados.Dtvalidade}', 'DD/MM/YYYY'), {dados.Codfuncconf}, 1, {dados.Qtconf}, {dados.Qtavaria}, NULL)");
exec.CommandText = confereBonusiConf.ToString();
OracleDataReader bonusiconf = exec.ExecuteReader();
//Verifica se é o primeiro registro
validaPrimeiroVol.Append($"SELECT COUNT(*) AS TOT_REG FROM PCBONUSIVOLUME WHERE NUMBONUS = {dados.Numbonus} AND CODPROD = {dados.Codprod} AND QTENTRADA = 0");
exec.CommandText = validaPrimeiroVol.ToString();
OracleDataReader valida = exec.ExecuteReader();
if (valida.Read())
{
int primeiraConferencia = valida.GetInt32(0);
if (primeiraConferencia > 0)
{
confereBonusiVolume.Append($"UPDATE PCBONUSIVOLUME SET NUMBONUS = {dados.Numbonus}, CODPROD = {dados.Codprod}, NUMLOTE = 1, CODAGREGACAO = NULL, DTVALIDADE = TO_DATE('{dados.Dtvalidade}', 'DD/MM/YYYY'), ");
confereBonusiVolume.Append($" QTPECAS = 0, QTENTRADA = {dados.Qtconf}, QTNF = {dados.Qtnf}, TIPO = 'N', CODIGOUMA = 0, ENDERECADO = 'N', CODMOTIVO = 0, DTLANCAMENTO = SYSDATE, ");
confereBonusiVolume.Append($" CODFILIALESTOQUE = NULL, CODFILIALGESTAO = NULL, CODFUNCCONFERENTE = {dados.Codfuncconf}");
confereBonusiVolume.Append($" WHERE NUMBONUS = { dados.Numbonus } AND CODPROD = { dados.Codprod } AND QTENTRADA = 0");
exec.CommandText = confereBonusiVolume.ToString();
OracleDataReader bonusivolume = exec.ExecuteReader();
}
else
{
//Confere (inserindo) item na PCBONUSIVOLUME
confereBonusiVolume.Append("INSERT INTO PCBONUSIVOLUME (NUMBONUS, CODPROD, NUMLOTE, CODAGREGACAO, DTVALIDADE, QTPECAS, QTENTRADA, QTNF, TIPO, ");
confereBonusiVolume.Append(" CODIGOUMA, ENDERECADO, CODMOTIVO, DTLANCAMENTO, CODFILIALESTOQUE, CODFILIALGESTAO, CODFUNCCONFERENTE)");
confereBonusiVolume.Append($" VALUES ({dados.Numbonus}, {dados.Codprod}, 1, NULL, TO_DATE('{dados.Dtvalidade}', 'DD/MM/YYYY'), 0, {dados.Qtconf}, {dados.Qtnf}, 'N', 0, 'N', 0, SYSDATE, NULL, NULL, {dados.Codfuncconf})");
exec.CommandText = confereBonusiVolume.ToString();
OracleDataReader bonusivolume = exec.ExecuteReader();
}
}
transacao.Commit();
return(true);
}
catch (Exception ex)
{
if (connection.State == ConnectionState.Open)
{
transacao.Rollback();
connection.Close();
throw new Exception(ex.ToString());
}
transacao.Rollback();
exec.Dispose();
connection.Dispose();
throw new Exception(ex.ToString());
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
exec.Dispose();
connection.Dispose();
}
}
/// <summary>
///执行一个命令，没有返回数据集，只返回影响记录数
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">OracleParameter[]</param>
/// <returns>影响记录数</returns>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
try
{
int returnInt = cmd.ExecuteNonQuery();
if (transaction != null)
{
transaction.Commit();
}
return returnInt;
}
catch
{
if (transaction != null)
{
transaction.Rollback();
}
return 0;
}
}
private bool load_file(object sender, DoWorkEventArgs e, out string msg)
{
msg = null;
OracleConnection connection = new OracleConnection(config.connectionstring);
connection.Open();
OracleTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
OracleCommand command = connection.CreateCommand();
// Start a local transaction
// transaction = connection.BeginTransaction();
// Assign transaction object for a pending local transaction
command.Transaction = transaction;
try
{
int pc = 0;
int i = 0;
int cnt = data.Count;
// command.CommandText = string.Format("DELETE FROM Rcd.CHECKWEBPAYDATA Where FILENAME='{0}'", FileName);
// command.ExecuteNonQuery();
foreach (STRowWebPayData item in data)
{
if (backgroundWorker1.CancellationPending)
{
transaction.Rollback();
e.Cancel = true;
backgroundWorker1.ReportProgress(0);
return(false);
}
command.CommandText = "INSERT INTO Rcd.VALID_WEBPAYDATA (IDSYS, RRN, IDSTATUS, LTIME, AMOUNT, FILENAME, TAGS) values (:1, :2, :3, :4, :5, :6, :7)";
command.Parameters.Clear();
command.Parameters.Add(crp(OracleType.Int32, item.idsys, "1", false));
command.Parameters.Add(crp(OracleType.Char, item.rrn, "2", false));
command.Parameters.Add(crp(OracleType.Int32, item.idstatus, "3", false));
command.Parameters.Add(crp(OracleType.DateTime, item.ltime, "4", false));
command.Parameters.Add(crp(OracleType.Number, Math.Round(item.amount, 2), "5", false));
command.Parameters.Add(crp(OracleType.Char, item.filename, "6", false));
command.Parameters.Add(crp(OracleType.Clob, item.tags, "7", false));
command.ExecuteNonQuery();
i += 1;
// pc = i / cnt * 100;
double d = ((i * 1.0) / cnt) * 100;
// double f = Math.Round((double)(i / cnt * 100.0));
backgroundWorker1.ReportProgress((int)d);
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
msg = ex.Message;
log.LogLine("Neither record was written to database.");
return(false);
}
return(true);
}
/// <summary>
/// Execute an OracleCommand (that returns a resultset) against the specified OracleTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回DataSet</returns>
public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
OracleDataAdapter da;
DataSet ds = new DataSet();
try
{
da = new OracleDataAdapter(cmd);
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
if (transaction != null)
{
transaction.Commit();
}
}
catch
{
if (transaction != null)
{
// Rollback the transaction
transaction.Rollback();
}
}
//return the dataset
return ds;
}
public string DeleteBomEntry(BOMModel objBOMModel)
{
string strMsg = "";
string[] TranIdArray = objBOMModel.GridTranId.Split(',');
int x = TranIdArray.Count();
for (int i = 0; i < x; i++)
{
var arrayTranId = TranIdArray[i];
OracleTransaction objOracleTransaction = null;
OracleCommand objOracleCommand = new OracleCommand("pro_bill_of_material_delete");
objOracleCommand.CommandType = CommandType.StoredProcedure;
if (objBOMModel.StyleNo != "")
{
objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.StyleNo;
}
else
{
objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
}
if (objBOMModel.BuyerId != "")
{
objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.BuyerId;
}
else
{
objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
}
if (objBOMModel.SeasonId != "")
{
objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonId;
}
else
{
objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
}
if (objBOMModel.SeasonYear != "")
{
objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonYear;
}
else
{
objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
}
if (arrayTranId != null)
{
objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayTranId;
}
else
{
objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
}
objOracleCommand.Parameters.Add("p_update_by", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.UpdateBy;
objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.HeadOfficeId;
objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.BranchOfficeId;
objOracleCommand.Parameters.Add("P_MESSAGE", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output;
using (OracleConnection strConn = GetConnection())
{
try
{
objOracleCommand.Connection = strConn;
strConn.Open();
trans = strConn.BeginTransaction();
objOracleCommand.ExecuteNonQuery();
trans.Commit();
strConn.Close();
strMsg = objOracleCommand.Parameters["P_MESSAGE"].Value.ToString();
}
catch (Exception ex)
{
throw new Exception("Error : " + ex.Message);
objOracleTransaction.Rollback();
}
finally
{
strConn.Close();
}
}
}
return(strMsg);
}
/// <summary>
/// Create and prepare an OracleCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">有效的 OracleConnection, on which to execute this command</param>
/// <param name="transaction">有效的 OracleTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by OracleProvider</param>
/// <returns>OracleDataReader containing the results of the command</returns>
private static OracleDataReader ExecuteReader(OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, OracleConnectionOwnership connectionOwnership)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
//create a reader
OracleDataReader dr = null;
try
{
// call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == OracleConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection));
}
if (transaction != null)
{
transaction.Commit();
}
return (OracleDataReader)dr;
}
catch
{
if (transaction != null)
{
// Rollback the transaction
transaction.Rollback();
}
throw;
}
}
public void RollbackTransaction(OracleTransaction trans, bool closeConnection)
{
trans.Rollback();
if (closeConnection && trans.Connection.State != ConnectionState.Closed)
{
trans.Connection.Close();
}
trans = null;
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant de mettre à jour un participant
/// </summary>
/// <param name="pIdParticipant">Id du participant à mettre à jour</param>
/// <param name="pCleWifi">La clé wifi (généré ou null)</param>
public void MajParticipant(Int32 pIdParticipant,String pCleWifi )
{
String MessageErreur = "";
try
{
UneOracleCommand = new OracleCommand("pckparticipant.enregistrearriveparticipant", CnOracle); //Package.procedure
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// Paramètres qui stocke l'ID du participant ainsi que la clé wifi de celui-ci
UneOracleCommand.Parameters.Add("pIdParticipant", OracleDbType.Int32, ParameterDirection.Input).Value = pIdParticipant;
UneOracleCommand.Parameters.Add("pCleWifi", OracleDbType.Char, ParameterDirection.Input).Value = pCleWifi;
// début de la transaction Oracle il vaut mieyx gérer les transactions dans l'applicatif que dans la bd dans les procédures stockées.
UneOracleTransaction = this.CnOracle.BeginTransaction();
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
MessageErreur = this.GetMessageOracle(Oex.Message);
}
catch (Exception)
{
MessageErreur = "Erreur de la mise à jour du participant";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
public static bool SavePratiuttar(ATTPratiuttar objPratiuttar)
{
GetConnection GetConn = new GetConnection();
OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction();
double pratiuttarID = 0;
List <OracleParameter> paramArray = new List <OracleParameter>();
try
{
paramArray.Add(Utilities.GetOraParam(":P_CASE_ID", objPratiuttar.CaseID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_PRATIUTTAR_ID", pratiuttarID, OracleDbType.Int32, ParameterDirection.InputOutput));
paramArray.Add(Utilities.GetOraParam(":P_PRATIUTTAR_DATE", objPratiuttar.PratiuttarDate, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ACCOUNT_FORWARD", objPratiuttar.AccountForward, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_SUBMITTED_BY", objPratiuttar.SubmittedBy, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_PRATIUTTAR_SUMMARY", objPratiuttar.PratiuttarSummary, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objPratiuttar.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
if (objPratiuttar.PratiuttarID == 0)
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_CASE_PRATIUTTAR", paramArray.ToArray());
}
else if (objPratiuttar.PratiuttarID > 0)
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_CASE_PRATIUTTAR", paramArray.ToArray());
}
objPratiuttar.PratiuttarID = int.Parse(paramArray[1].Value.ToString());
if (objPratiuttar.LstPratiuttarLitigants.Count > 0)
{
DLLPratiuttarLitigants.SavePratiuttarLitigants(objPratiuttar.LstPratiuttarLitigants, objPratiuttar.PratiuttarID, Tran);
}
if (objPratiuttar.LstPratiuttarEvidence.Count > 0)
{
DLLPratiuttarEvidence.SavePratiuttarEvidence(objPratiuttar.LstPratiuttarEvidence, objPratiuttar.PratiuttarID, Tran);
}
if (objPratiuttar.LstPratiuttarDocuments.Count > 0)
{
DLLPratiuttarDocuments.SavePratiuttarDocuments(objPratiuttar.LstPratiuttarDocuments, objPratiuttar.PratiuttarID, Tran);
}
Tran.Commit();
paramArray.Clear();
return(true);
}
catch (OracleException oex)
{
PCS.COREDL.OracleError oe = new PCS.COREDL.OracleError();
throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message));
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
finally
{
GetConn.CloseDbConn();
}
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant d'ajouter une nouvelle vacation
/// </summary>
/// <param name="pIdAtelier">Id de l'atelier que l'on va associé à la vacation</param>
/// <param name="pHeureDebut">Heure debut de la vacation</param>
/// <param name="pHeureFin">Heure fin de la vacation</param>
public void creerVacation(Int32 pIdAtelier, String pHeureDebut, String pHeureFin)
{
/// <remarks>
///
/// </remarks>
///
String MessageErreur = "";
try
{
UneOracleCommand = new OracleCommand("MDL.pckgestionajoutmodif.ajoutvacation", CnOracle); //Package.procedure
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// Paramètres qui stocke l'ID de l'atelier associé à la vacation ainsi que l'heure de debut et de fin de la vacation
UneOracleCommand.Parameters.Add("pIdAtelier", OracleDbType.Int32, ParameterDirection.Input).Value = pIdAtelier;
UneOracleCommand.Parameters.Add("pHeureDebut", OracleDbType.Varchar2, ParameterDirection.Input).Value = pHeureDebut;
UneOracleCommand.Parameters.Add("pHeureFin", OracleDbType.Varchar2, ParameterDirection.Input).Value = pHeureFin;
// début de la transaction Oracle il vaut mieyx gérer les transactions dans l'applicatif que dans la bd dans les procédures stockées.
UneOracleTransaction = this.CnOracle.BeginTransaction();
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
MessageErreur = this.GetMessageOracle(Oex.Message);
}
catch (Exception)
{
MessageErreur = "Autre Erreur, les informations n'ont pas été correctement saisies";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
/************************************************************
* Function name : SetNoticeHitCnt
* Purpose : 공지사항 읽은수 Update
* Input : string[] rParams
* Output : string
*************************************************************/
public string SetNoticeHitCnt(string rParams)
{
string xRtn = Boolean.FalseString;
Database db = base.GetDataBase("LMS");
OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection();
xCnnLMS.Open();
OracleTransaction xTransLMS = null;
OracleCommand xCmdLMS = null;
try
{
xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
xCmdLMS = base.GetSqlCommand(db);
xCmdLMS.Connection = xCnnLMS;
xCmdLMS.Transaction = xTransLMS;
try
{
OracleParameter[] oOraParams = new OracleParameter[2];
oOraParams[0] = base.AddParam("p_not_no", OracleType.Number, Convert.ToInt32(rParams));
oOraParams[1] = base.AddParam("p_in_table", OracleType.VarChar, "T_NOTICE");
int j = base.Execute(db, CommandType.StoredProcedure, "pkg_lms_hitcount.lms_hitcount", oOraParams, xTransLMS);
//string xSql = string.Empty;
//xSql += " UPDATE t_notice SET hit_cnt = hit_cnt + 1 ";
//xSql += string.Format(" WHERE not_no = {0} ", Convert.ToInt32(rParams));
//xCmdLMS.CommandText = xSql;
//base.Execute("LMS", xCmdLMS, xTransLMS);
xTransLMS.Commit(); // 트랜잭션 커밋
xRtn = Boolean.TrueString;
}
catch (Exception ex)
{
// 파일 제거
//if (xFilePath.Trim() != "")
// File.Delete(xFilePath);
xTransLMS.Rollback(); // Exception 발생시 롤백처리...
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(xRtn);
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant d'inscrire un nouvel intervenant sans nuité
/// </summary>
/// <param name="Cmd">nom de l'objet command concerné par les paramètres</param>
/// <param name="pNom">nom du participant</param>
/// <param name="pPrenom">prénom du participant</param>
/// <param name="pAdresse1">adresse1 du participant</param>
/// <param name="pAdresse2">adresse2 du participant</param>
/// <param name="pCp">cp du participant</param>
/// <param name="pVille">ville du participant</param>
/// <param name="pTel">téléphone du participant</param>
/// <param name="pMail">mail du participant</param>
/// <param name="pIdAtelier"> Id de l'atelier où interviendra l'intervenant</param>
/// <param name="pIdStatut">statut de l'intervenant pour l'atelier : animateur ou intervenant</param>
public void InscrireIntervenant(String pNom, String pPrenom, String pAdresse1, String pAdresse2, String pCp, String pVille, String pTel, String pMail, Int16 pIdAtelier, String pIdStatut)
{
/// <remarks>
/// procédure qui va créer :
/// 1- un enregistrement dans la table participant
/// 2- un enregistrement dans la table intervenant
/// en cas d'erreur Oracle, appel à la méthode GetMessageOracle dont le rôle est d'extraire uniquement le message renvoyé
/// par une procédure ou un trigger Oracle
/// </remarks>
///
String MessageErreur = "";
try
{
UneOracleCommand = new OracleCommand("pckparticipant.nouvelintervenant", CnOracle);
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// début de la transaction Oracle il vaut mieyx gérer les transactions dans l'applicatif que dans la bd dans les procédures stockées.
UneOracleTransaction = this.CnOracle.BeginTransaction();
// on appelle la procédure ParamCommunsNouveauxParticipants pour charger les paramètres communs aux intervenants
this.ParamCommunsNouveauxParticipants(UneOracleCommand, pNom, pPrenom, pAdresse1, pAdresse2, pCp, pVille, pTel, pMail);
// on appelle la procédure ParamsCommunsIntervenant pour charger les paramètres communs aux intervenants
this.ParamsSpecifiquesIntervenant(UneOracleCommand, pIdAtelier, pIdStatut);
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
MessageErreur = "Erreur Oracle \n" + this.GetMessageOracle(Oex.Message);
}
catch (Exception ex)
{
MessageErreur = "Autre Erreur, les informations n'ont pas été correctement saisies";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
/************************************************************
* Function name : SetNoticeUpdate
* Purpose : 공지사항 저장(기존 공지사항 저장 Update)
* Input : string[] rParams
* Output : String
*************************************************************/
public string SetNoticeUpdate(params object[] rParams)
{
string xRtn = Boolean.FalseString;
Database db = base.GetDataBase("LMS");
OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection();
xCnnLMS.Open();
OracleTransaction xTransLMS = null;
OracleCommand xCmdLMS = null;
try
{
string xFilePath = string.Empty;
xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
xCmdLMS = base.GetSqlCommand(db);
xCmdLMS.Connection = xCnnLMS;
xCmdLMS.Transaction = xTransLMS;
string[] rMasterParams = (string[])rParams[0];
object[,] rDetailParams = (object[, ])rParams[1];
string[] rDeleteFile = (string[])rParams[2];
// 1. 기존 첨부파일의 삭제
// 2. 기존공지사항 Update
// 3. 첨부파일 Insert
try
{
string xSql = string.Empty;
// 기존 첨부파일 삭제
for (int i = 0; i < rDeleteFile.Length; i++)
{
xSql = string.Empty;
xSql += string.Format(" DELETE FROM t_notice_att WHERE not_no = {0} AND file_nm = '{1}' ", Convert.ToInt32(rMasterParams[7]), rDeleteFile[i]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xTransLMS);
}
// 기존 공지사항 Update
xSql = string.Empty;
xSql += " UPDATE t_notice SET ";
xSql += string.Format(" not_kind = '{0}', ", rMasterParams[0]); // 공지사항 타입 m_cd = '0055' , 000001: 일반공지 , 000002: 과정공지
xSql += string.Format(" open_course_id = '{0}', ", rMasterParams[1]); // 과정 ID (일반공지의 경우 Empty Value)
xSql += string.Format(" notice_begin_dt = TO_DATE('{0}','YYYY.MM.DD'), ", rMasterParams[2]); // 공지 게시일자
xSql += string.Format(" notice_end_dt = TO_DATE('{0}','YYYY.MM.DD'), ", rMasterParams[3]); // 공지 종료일자
xSql += string.Format(" not_sub = '{0}', ", rMasterParams[4]); // 공지사항 제목
//xSql += string.Format(" not_content = '{0}', ", rMasterParams[5]); // 공지사항 내용
xSql += "not_content = :NOTCONTENT, ";
xSql += string.Format(" upt_id = '{0}', ", rMasterParams[6]); // 수정자 ID
xSql += string.Format(" send_flg = '{0}', ", rMasterParams[8]); // Sednf FLG 1 : 전송대상, 2 : 전송완료, 3 : 전송안함
xSql += " upt_dt = SYSDATE ";
xSql += string.Format(" WHERE not_no = {0} ", Convert.ToInt32(rMasterParams[7]));
OracleParameter NOTCONTENT = new OracleParameter();
NOTCONTENT.OracleType = OracleType.Clob;
NOTCONTENT.ParameterName = "NOTCONTENT";
NOTCONTENT.Value = rMasterParams[5];
xCmdLMS.Parameters.Add(NOTCONTENT);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xTransLMS);
for (int i = 0; i < rDetailParams.GetLength(0); i++)
{
byte[] xFileData = (byte[])rDetailParams[i, 0];
xSql = string.Empty;
xSql += " INSERT INTO t_notice_att ( ";
xSql += " NOT_NO, ";
xSql += " FILE_NM, ";
xSql += " att_file ";
xSql += " ) ";
xSql += " VALUES ( ";
xSql += string.Format(" {0}, ", Convert.ToInt32(rMasterParams[7]));
xSql += string.Format(" '{0}', ", rDetailParams[i, 1]);
xSql += " :ATTFILE ";
xSql += " ) ";
OracleParameter ATTFILE = new OracleParameter();
ATTFILE.OracleType = OracleType.Blob;
ATTFILE.ParameterName = "ATTFILE";
ATTFILE.Value = xFileData;
xCmdLMS.Parameters.Add(ATTFILE);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xTransLMS);
}
if (rMasterParams[8] == "1") // 전송대상이면
{
OracleParameter[] oOraParams = new OracleParameter[2];
oOraParams[0] = base.AddParam("p_in_table", OracleType.VarChar, "T_NOTICE");
oOraParams[1] = base.AddParam("p_out_table", OracleType.VarChar, "T_BULL");
int i = base.Execute(db, CommandType.StoredProcedure, "pkg_lms_datasync.lms_export", oOraParams, xTransLMS);
}
xTransLMS.Commit(); // 트랜잭션 커밋
xRtn = Boolean.TrueString;
}
catch (Exception ex)
{
// 파일 제거
if (xFilePath.Trim() != "")
{
File.Delete(xFilePath);
}
xTransLMS.Rollback(); // Exception 발생시 롤백처리...
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(xRtn);
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant d'inscrire un nouvel intervenant qui aura des nuités
/// </summary>
/// <param name="Cmd">nom de l'objet command concerné par les paramètres</param>
/// <param name="pNom">nom du participant</param>
/// <param name="pPrenom">prénom du participant</param>
/// <param name="pAdresse1">adresse1 du participant</param>
/// <param name="pAdresse2">adresse2 du participant</param>
/// <param name="pCp">cp du participant</param>
/// <param name="pVille">ville du participant</param>
/// <param name="pTel">téléphone du participant</param>
/// <param name="pMail">mail du participant</param>
/// <param name="pIdAtelier"> Id de l'atelier où interviendra l'intervenant</param>
/// <param name="pIdStatut">statut de l'intervenant pour l'atelier : animateur ou intervenant</param>
/// <param name="pLesCategories">tableau contenant la catégorie de chambre pour chaque nuité à réserver</param>
/// <param name="pLesHotels">tableau contenant l'hôtel pour chaque nuité à réserver</param>
/// <param name="pLesNuits">tableau contenant l'id de la date d'arrivée pour chaque nuité à réserver</param>
public void InscrireIntervenant(String pNom, String pPrenom, String pAdresse1, String pAdresse2, String pCp, String pVille, String pTel, String pMail, Int16 pIdAtelier, String pIdStatut, Collection<string> pLesCategories, Collection<string> pLesHotels, Collection<Int16> pLesNuits)
{
/// <remarks>
/// procédure qui va :
/// 1- faire appel à la procédure
/// un enregistrement dans la table participant
/// 2- un enregistrement dans la table intervenant
/// 3- un à 2 enregistrements dans la table CONTENUHEBERGEMENT
///
/// en cas d'erreur Oracle, appel à la méthode GetMessageOracle dont le rôle est d'extraire uniquement le message renvoyé
/// par une procédure ou un trigger Oracle
/// </remarks>
///
String MessageErreur = "";
try
{
// pckparticipant.nouvelintervenant est une procédure surchargée
UneOracleCommand = new OracleCommand("pckparticipant.nouvelintervenant", CnOracle);
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// début de la transaction Oracle : il vaut mieyx gérer les transactions dans l'applicatif que dans la bd.
UneOracleTransaction = this.CnOracle.BeginTransaction();
this.ParamCommunsNouveauxParticipants(UneOracleCommand, pNom, pPrenom, pAdresse1, pAdresse2, pCp, pVille, pTel, pMail);
this.ParamsSpecifiquesIntervenant(UneOracleCommand, pIdAtelier, pIdStatut);
//On va créer ici les paramètres spécifiques à l'inscription d'un intervenant qui réserve des nuits d'hôtel.
// Paramètre qui stocke les catégories sélectionnées
OracleParameter pOraLescategories = new OracleParameter();
pOraLescategories.ParameterName = "pLesCategories";
pOraLescategories.OracleDbType = OracleDbType.Char;
pOraLescategories.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLescategories.Value = pLesCategories.ToArray();
pOraLescategories.Size = pLesCategories.Count;
UneOracleCommand.Parameters.Add(pOraLescategories);
// Paramètre qui stocke les hotels sélectionnées
OracleParameter pOraLesHotels = new OracleParameter();
pOraLesHotels.ParameterName = "pLesHotels";
pOraLesHotels.OracleDbType = OracleDbType.Char;
pOraLesHotels.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesHotels.Value = pLesHotels.ToArray();
pOraLesHotels.Size = pLesHotels.Count;
UneOracleCommand.Parameters.Add(pOraLesHotels);
// Paramètres qui stocke les nuits sélectionnées
OracleParameter pOraLesNuits = new OracleParameter();
pOraLesNuits.ParameterName = "pLesNuits";
pOraLesNuits.OracleDbType = OracleDbType.Int16;
pOraLesNuits.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesNuits.Value = pLesNuits.ToArray();
pOraLesNuits.Size = pLesNuits.Count;
UneOracleCommand.Parameters.Add(pOraLesNuits);
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
//MessageErreur="Erreur Oracle \n" + this.GetMessageOracle(Oex.Message);
MessageBox.Show(Oex.Message);
}
catch (Exception ex)
{
MessageErreur = "Autre Erreur, les informations n'ont pas été correctement saisies";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
/************************************************************
* Function name : SetStudyUpdate
* Purpose :
* Input :
* Output : string
*************************************************************/
#region public string SetStudyUpdate(object[] rParams)
public string SetStudyUpdate(object[] rParams)
{
Database db = null;
string xRtn = string.Empty;
string xSql = string.Empty;
try
{
db = base.GetDataBase("LMS"); //Database 생성
using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS");
{
xCnnLMS.Open();
OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
OracleCommand xCmdLMS = null;
try
{
xCmdLMS = base.GetSqlCommand(db);
OracleParameter[] xPara = null;
//전체 contents 수 정보
xSql = @"
SELECT COUNT(*) AS TOTALCNT
FROM T_COURSE_RESULT CR, T_OPEN_COURSE OC
, T_COURSE_SUBJECT CS, T_SUBJECT_CONTENTS SC
, T_SUBJECT S, T_CONTENTS C
WHERE CR.OPEN_COURSE_ID = OC.OPEN_COURSE_ID
AND OC.COURSE_ID = CS.COURSE_ID
AND CS.SUBJECT_ID = SC.SUBJECT_ID
AND SC.SUBJECT_ID = S.SUBJECT_ID
AND SC.CONTENTS_ID = C.CONTENTS_ID
AND CR.USER_ID = '{0}'
AND OC.OPEN_COURSE_ID = '{1}' ";
xSql = string.Format(xSql, rParams[0], rParams[1]);
xCmdLMS.CommandText = xSql;
object xTotalCnt = base.ExecuteScalar(db, xCmdLMS, xTransLMS);
//최초
if (rParams[4].ToString() == "1")
{
xSql = @" UPDATE T_COURSE_RESULT SET
USER_COURSE_BEGIN_DT = SYSDATE
WHERE USER_ID = :USER_ID
AND OPEN_COURSE_ID = :OPEN_COURSE_ID ";
xPara = new OracleParameter[2];
xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, rParams[0]);
xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, rParams[1]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
}
//마지막 컨텐츠
if (rParams[4].ToString() == xTotalCnt.ToString())
{
xSql = @" UPDATE T_COURSE_RESULT SET
USER_COURSE_END_DT = SYSDATE
WHERE USER_ID = :USER_ID
AND OPEN_COURSE_ID = :OPEN_COURSE_ID ";
xPara = new OracleParameter[2];
xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, rParams[0]);
xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, rParams[1]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
}
//마지막 들은 contents 정보 seq 보다 현재 들으려고 하는 seq가 클 경우 last 정보 update
//사용자가 들은 마지막 컨텐츠 정보
xSql = @" SELECT LAST_SUBJECT_ID || '|' || LAST_CONTENTS_ID
FROM T_COURSE_RESULT
WHERE USER_ID = '{0}'
AND OPEN_COURSE_ID = '{1}' ";
xSql = string.Format(xSql, rParams[0], rParams[1]);
xCmdLMS.CommandText = xSql;
object xLast = base.ExecuteScalar(db, xCmdLMS, xTransLMS);
if (xLast != null && xLast.ToString().Replace("|", "") != string.Empty)
{
xSql = @"
SELECT OC.OPEN_COURSE_ID, S.SUBJECT_NM, S.SUBJECT_ID
, C.CONTENTS_NM, C.CONTENTS_ID, C.CONTENTS_FILE_NM
, CR.LAST_SUBJECT_ID, CR.LAST_CONTENTS_ID, SC.CONTENTS_SEQ
/*마지막으로 수강한 컨텐츠의 SEQ 정보*/
, (SELECT CONTENTS_SEQ FROM T_SUBJECT_CONTENTS WHERE SUBJECT_ID = CR.LAST_SUBJECT_ID AND CONTENTS_ID = CR.LAST_CONTENTS_ID) AS LAST_CONTENTS_SEQ
, S.SUBJECT_ID || '|' || C.CONTENTS_ID || '|' || C.CONTENTS_FILE_NM AS VINFO_T
, S.SUBJECT_ID || '|' || C.CONTENTS_ID || '|' || C.CONTENTS_FILE_NM || '|' || ROW_NUMBER() OVER(ORDER BY CS.SUBJECT_SEQ, SC.CONTENTS_SEQ) AS VINFO
, (SELECT COUNT(*) FROM T_SUBJECT_CONTENTS WHERE SUBJECT_ID = SC.SUBJECT_ID) AS TOTAL
, ROW_NUMBER() OVER(ORDER BY CS.SUBJECT_SEQ, SC.CONTENTS_SEQ) AS ROW_SEQ
FROM T_COURSE_RESULT CR, T_OPEN_COURSE OC
, T_COURSE_SUBJECT CS, T_SUBJECT_CONTENTS SC
, T_SUBJECT S, T_CONTENTS C
WHERE CR.OPEN_COURSE_ID = OC.OPEN_COURSE_ID
AND OC.COURSE_ID = CS.COURSE_ID
AND CS.SUBJECT_ID = SC.SUBJECT_ID
AND SC.SUBJECT_ID = S.SUBJECT_ID
AND SC.CONTENTS_ID = C.CONTENTS_ID
AND OC.OPEN_COURSE_ID = '{0}'
AND CR.USER_ID = '{1}'
ORDER BY CS.SUBJECT_SEQ, SC.CONTENTS_SEQ
";
xSql = string.Format(xSql, rParams[1], rParams[0]);
xCmdLMS.CommandText = xSql;
DataSet xds = base.ExecuteDataSet(db, xCmdLMS, xTransLMS);
string[] xLastKey = xLast.ToString().Split('|');
if (xds.Tables[0].Rows.Count > 0)
{
DataRow[] xdrarr = xds.Tables[0].Select("SUBJECT_ID = '" + xLastKey[0] + "' AND CONTENTS_ID = '" + xLastKey[1] + "' ");
DataRow xdr = null;
if (xdrarr.Length > 0)
{
xdr = xdrarr[0];
if (Convert.ToInt32(rParams[4]) > Convert.ToInt32(xdr["ROW_SEQ"].ToString()))
{
xTotalCnt = xTotalCnt == null || xTotalCnt.ToString() == string.Empty ? "1" : xTotalCnt.ToString();
double xProgress = (Convert.ToDouble(rParams[4]) / Convert.ToDouble(xTotalCnt)) * 100;
xSql = @" UPDATE T_COURSE_RESULT SET
PROGRESS_RATE = :PROGRESS_RATE
, LAST_SUBJECT_ID = :LAST_SUBJECT_ID
, LAST_CONTENTS_ID = :LAST_CONTENTS_ID
WHERE USER_ID = :USER_ID
AND OPEN_COURSE_ID = :OPEN_COURSE_ID ";
xPara = new OracleParameter[5];
xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, rParams[0]);
xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, rParams[1]);
xPara[2] = base.AddParam("PROGRESS_RATE", OracleType.VarChar, Convert.ToString(Math.Round(xProgress)));
xPara[3] = base.AddParam("LAST_SUBJECT_ID", OracleType.VarChar, rParams[2]);
xPara[4] = base.AddParam("LAST_CONTENTS_ID", OracleType.VarChar, rParams[3]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
}
}
}
}
else
{
//최초이면
xTotalCnt = xTotalCnt == null || xTotalCnt.ToString() == string.Empty ? "1" : xTotalCnt.ToString();
double xProgress = (Convert.ToDouble(rParams[4]) / Convert.ToDouble(xTotalCnt)) * 100;
xSql = @" UPDATE T_COURSE_RESULT SET
PROGRESS_RATE = :PROGRESS_RATE
, LAST_SUBJECT_ID = :LAST_SUBJECT_ID
, LAST_CONTENTS_ID = :LAST_CONTENTS_ID
WHERE USER_ID = :USER_ID
AND OPEN_COURSE_ID = :OPEN_COURSE_ID ";
xPara = new OracleParameter[5];
xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, rParams[0]);
xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, rParams[1]);
xPara[2] = base.AddParam("PROGRESS_RATE", OracleType.VarChar, Convert.ToString(Math.Round(xProgress)));
xPara[3] = base.AddParam("LAST_SUBJECT_ID", OracleType.VarChar, rParams[2]);
xPara[4] = base.AddParam("LAST_CONTENTS_ID", OracleType.VarChar, rParams[3]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
}
xRtn = "Y";
xTransLMS.Commit(); //트렌잭션 커밋
}
catch (Exception ex)
{
// 트랜잭션 롤백
xTransLMS.Rollback();
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
if (xCnnLMS != null)
{
if (xCnnLMS.State == ConnectionState.Open)
{
xCnnLMS.Close();
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
db = null;
}
return(xRtn);//리터값
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant de mettre à jour une vacation
/// </summary>
/// <param name="pHeureDebutVac">Heure de début de la vacation</param>
/// <param name="pHeureFinVac">Heure de fin de la vacation</param>
/// <param name="pNumeroVac">Numero de la vacation à mettre à jour</param>
public void MajVacation(DateTime pHeureDebutVac, DateTime pHeureFinVac, Int16 pNumeroVac)
{
String MessageErreur = "";
try
{
UneOracleCommand = new OracleCommand("MDL.pckgestionajoutmodif.modifvacation", CnOracle); //Package.procedure
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// Paramètres qui stocke l'ID de l'atelier associé à la vacation, le numero de la vacation ainsi que l'heure de debut et de fin de la vacation
UneOracleCommand.Parameters.Add("pHeureDebut", OracleDbType.Date, ParameterDirection.Input).Value = pHeureDebutVac;
UneOracleCommand.Parameters.Add("pHeureFin", OracleDbType.Date, ParameterDirection.Input).Value = pHeureFinVac;
UneOracleCommand.Parameters.Add("pNumero", OracleDbType.Int16, ParameterDirection.Input).Value = pNumeroVac;
// début de la transaction Oracle il vaut mieyx gérer les transactions dans l'applicatif que dans la bd dans les procédures stockées.
UneOracleTransaction = this.CnOracle.BeginTransaction();
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
MessageErreur = this.GetMessageOracle(Oex.Message);
}
catch (Exception)
{
MessageErreur ="Erreur de la mise à jour de la vacation";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
/************************************************************
* Function name : SetExamSubmit
* Purpose :
* Input :
* Output : string
*************************************************************/
#region public string SetExamSubmit(string rParam)
public string SetExamSubmit(string rParam)
{
Database db = null;
string xRtn = string.Empty;
string xSql = string.Empty;
try
{
db = base.GetDataBase("LMS"); //Database 생성
using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS");
{
xCnnLMS.Open();
OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
OracleCommand xCmdLMS = null;
try
{
xCmdLMS = base.GetSqlCommand(db);
OracleParameter[] xPara = null;
ArrayList xarr = STR2MD2ArrayList(rParam);
string[] xParam;
string xUser = string.Empty;
string xOpenId = string.Empty;
string xSeq = string.Empty;
for (int i = 0; i < xarr.Count; i++)
{
//xarr.Add(new string[] { dr["USER_ID"].ToString()
// , dr["OPEN_COURSE_ID"].ToString()
// , dr["COURSE_RESULT_SEQ"].ToString()
// , dr["COURSE_ID"].ToString()
// , dr["QUESTION_ID"].ToString()
// , xAnswer
// });
xParam = (string[])xarr[i];
if (xUser == string.Empty)
{
xUser = xParam[0];
xOpenId = xParam[1];
xSeq = xParam[2];
}
//--USER_ID
//--OPEN_COURSE_ID
//--COURSE_RESULT_SEQ
//--COURSE_ID
//--QUESTION_ID
xSql = " SELECT OPEN_COURSE_ID FROM T_ASSESS_RESULT ";
xSql += " WHERE USER_ID = '" + xParam[0] + "' ";
xSql += " AND OPEN_COURSE_ID = '" + xParam[1] + "' ";
xSql += " AND COURSE_RESULT_SEQ = " + xParam[2] + " ";
xSql += " AND COURSE_ID = '" + xParam[3] + "' ";
xSql += " AND QUESTION_ID = '" + xParam[4] + "' ";
xCmdLMS.CommandText = xSql;
object xobj = base.ExecuteScalar(db, xCmdLMS, xTransLMS);
if (xobj != null && xobj.ToString() != string.Empty)
{
//update
xSql = @" UPDATE T_ASSESS_RESULT SET
USER_ANSWER = :USER_ANSWER
, UPT_ID = :USER_ID
, UPT_DT = SYSDATE
, ISRIGHT_FLG = :ISRIGHT_FLG
WHERE USER_ID = :USER_ID
AND OPEN_COURSE_ID = :OPEN_COURSE_ID
AND COURSE_RESULT_SEQ = :COURSE_RESULT_SEQ
AND COURSE_ID = :COURSE_ID
AND QUESTION_ID = :QUESTION_ID ";
xPara = new OracleParameter[7];
xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xParam[0]);
xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xParam[1]);
xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xParam[2]);
xPara[3] = base.AddParam("COURSE_ID", OracleType.VarChar, xParam[3]);
xPara[4] = base.AddParam("QUESTION_ID", OracleType.VarChar, xParam[4]);
xPara[5] = base.AddParam("USER_ANSWER", OracleType.VarChar, xParam[5]);
xPara[6] = base.AddParam("ISRIGHT_FLG", OracleType.VarChar, xParam[6]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
}
else
{
//insert
xSql = @" INSERT INTO T_ASSESS_RESULT
(USER_ID, OPEN_COURSE_ID, COURSE_RESULT_SEQ, COURSE_ID, QUESTION_ID
, USER_ANSWER
, INS_ID, INS_DT, UPT_ID, UPT_DT,ISRIGHT_FLG )
VALUES
(:USER_ID, :OPEN_COURSE_ID, :COURSE_RESULT_SEQ, :COURSE_ID, :QUESTION_ID
, :USER_ANSWER
, :USER_ID, SYSDATE, :USER_ID, SYSDATE, :ISRIGHT_FLG) ";
xPara = new OracleParameter[7];
xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xParam[0]);
xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xParam[1]);
xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xParam[2]);
xPara[3] = base.AddParam("COURSE_ID", OracleType.VarChar, xParam[3]);
xPara[4] = base.AddParam("QUESTION_ID", OracleType.VarChar, xParam[4]);
xPara[5] = base.AddParam("USER_ANSWER", OracleType.VarChar, xParam[5]);
xPara[6] = base.AddParam("ISRIGHT_FLG", OracleType.VarChar, xParam[6]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
}
}
//T_COURSE_RESULT.ASSESS_SCORE에 점수 반영
xSql = @"
SELECT NVL(SUM(AQ.QUESTION_SCORE), 0) AS TOTSUM
FROM T_ASSESS_QUESTION AQ, T_ASSESS_RESULT AR
WHERE AQ.QUESTION_ID = AR.QUESTION_ID
AND AR.USER_ID = '{0}'
AND AR.OPEN_COURSE_ID = '{1}'
AND AR.COURSE_RESULT_SEQ = {2}
AND AR.ISRIGHT_FLG = 'Y'
";
xSql = string.Format(xSql, xUser, xOpenId, xSeq);
xCmdLMS.CommandText = xSql;
object xTotal = base.ExecuteScalar(db, xCmdLMS, xTransLMS);
xSql = @" UPDATE T_COURSE_RESULT SET
ASSESS_SCORE = :ASSESS_SCORE
WHERE USER_ID = :USER_ID
AND OPEN_COURSE_ID = :OPEN_COURSE_ID
AND COURSE_RESULT_SEQ = :COURSE_RESULT_SEQ
";
xPara = new OracleParameter[4];
xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xUser);
xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xOpenId);
xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xSeq);
xPara[3] = base.AddParam("ASSESS_SCORE", OracleType.VarChar, xTotal.ToString());
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
/*
* -. 총점(T_COURSE_RESULT.TOTAL_SCORE): 개설과정의 이수기준 PROGRESS RATE(T_OPEN_COURSE.STD_PROGRESS_RATE), FINAL TEST(T_OPEN_COURSE.STD_FINAL_EXAM) 항목의 각 %를 나의 강의실에 진도, 시험에 반영 하여
* 총점에 반영 하고, 개설과정의 수료점수(T_OPEN_COURSE.PASS_SCORE)에 총점이 만족되면 PASS_FLG를 수료로 UPDATE
* 만족되지 못하면 PASS_FLG를 미수료로 UPDATE
*
* Ex) 예를들어
* 개설과정: Progress Rate=80%, Final Test=20%, 수료점수60점
* 나의강의실: 진도=80%, 시험=70점 => 개설과정 이수기준에 맞추면 진도=64점, 시험=14점 => 총점 78점
* 개설과정의 수료점수 60점에 만족 하므로 Pass~
*/
xSql = @"
SELECT CR.PROGRESS_RATE, CR.ASSESS_SCORE, CR.TOTAL_SCORE, CR.PASS_FLG
, OC.STD_PROGRESS_RATE, OC.STD_FINAL_EXAM, OC.PASS_SCORE
, TO_NUMBER(CR.PROGRESS_RATE) * (OC.STD_PROGRESS_RATE/100) AS PRO
, TO_NUMBER(CR.ASSESS_SCORE) * (OC.STD_FINAL_EXAM/100) AS EXAM
, TO_NUMBER(CR.PROGRESS_RATE) * (OC.STD_PROGRESS_RATE/100) + TO_NUMBER(CR.ASSESS_SCORE) * (OC.STD_FINAL_EXAM/100) AS TOT
, CASE WHEN TO_NUMBER(CR.PROGRESS_RATE) * (OC.STD_PROGRESS_RATE/100) + TO_NUMBER(CR.ASSESS_SCORE) * (OC.STD_FINAL_EXAM/100) >= OC.PASS_SCORE
THEN '000001'
ELSE '000005' END PASS
FROM T_COURSE_RESULT CR, T_OPEN_COURSE OC
WHERE CR.OPEN_COURSE_ID = OC.OPEN_COURSE_ID
AND CR.USER_ID = '{0}'
AND CR.OPEN_COURSE_ID = '{1}'
AND CR.COURSE_RESULT_SEQ = {2}
";
xSql = string.Format(xSql, xUser, xOpenId, xSeq);
xCmdLMS.CommandText = xSql;
DataSet xdsResult = base.ExecuteDataSet(db, xCmdLMS, xTransLMS);
if (xdsResult.Tables[0].Rows.Count > 0)
{
DataRow drResult = xdsResult.Tables[0].Rows[0];
xSql = @" UPDATE T_COURSE_RESULT SET
TOTAL_SCORE = :TOTAL_SCORE
, PASS_FLG = :PASS_FLG
WHERE USER_ID = :USER_ID
AND OPEN_COURSE_ID = :OPEN_COURSE_ID
AND COURSE_RESULT_SEQ = :COURSE_RESULT_SEQ
";
xPara = new OracleParameter[5];
xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xUser);
xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xOpenId);
xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xSeq);
xPara[3] = base.AddParam("TOTAL_SCORE", OracleType.VarChar, drResult["TOT"].ToString());
xPara[4] = base.AddParam("PASS_FLG", OracleType.VarChar, drResult["PASS"].ToString());
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
}
xRtn = "Y";
xTransLMS.Commit(); //트렌잭션 커밋
}
catch (Exception ex)
{
// 트랜잭션 롤백
xTransLMS.Rollback();
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
if (xCnnLMS != null)
{
if (xCnnLMS.State == ConnectionState.Open)
{
xCnnLMS.Close();
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
db = null;
}
return(xRtn);//리터값
}
//******************************************* debut des modif**********************************************************************//
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant d'ajouter un nouvel Atelier
/// </summary>
/// <param name="pLibelleAtelier">Libelle de l'atelier</param>
/// <param name="pNbPlacesMax">Nombre de places Maximum pour participer à un atelier</param>
public void creerAtelier(String pLibelleAtelier, Int32 pNbPlacesMax, String pLibelleTheme, DateTime pHeureDebVac, DateTime pHeureFinVac)
{
String MessageErreur = "";
try
{
UneOracleCommand = new OracleCommand("MDL.pckgestionajoutmodif.ajoutatelier", CnOracle); //Package.procedure
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// Paramètres qui stocke le libelle et le nbre de place max d'un atelier
UneOracleCommand.Parameters.Add("pLibelleAtelier", OracleDbType.Varchar2, ParameterDirection.Input).Value = pLibelleAtelier;
UneOracleCommand.Parameters.Add("pNbPlacesMax", OracleDbType.Int32, ParameterDirection.Input).Value = pNbPlacesMax;
UneOracleCommand.Parameters.Add("pLibelleTheme", OracleDbType.Varchar2, ParameterDirection.Input).Value = pLibelleTheme;
UneOracleCommand.Parameters.Add("pHeureDebutVac", OracleDbType.Date, ParameterDirection.Input).Value = pHeureDebVac;
UneOracleCommand.Parameters.Add("pHeureFinVac", OracleDbType.Date, ParameterDirection.Input).Value = pHeureFinVac;
// début de la transaction Oracle il vaut mieyx gérer les transactions dans l'applicatif que dans la bd dans les procédures stockées.
UneOracleTransaction = this.CnOracle.BeginTransaction();
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
MessageErreur = this.GetMessageOracle(Oex.Message);
}
catch (Exception)
{
MessageErreur = "Autre Erreur, les informations n'ont pas été correctement saisies";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
private void RollBackTrans()
{
Trans.Rollback();
}
// 执行insert,update,delete 动作，也可以使用事务
public static bool UpdateData(out string sError, string sSQL, string sConnStr = "", OracleParameter[] cmdParams = null, bool bUseTransaction = false)
{
int iResult = 0;
sError = string.Empty;
if (string.IsNullOrEmpty(sConnStr))
{
sConnStr = _sConnString;
}
if (!bUseTransaction)
{
try
{
OracleConnection conn = new OracleConnection(sConnStr);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sSQL;
if (cmdParams != null)
{
foreach (OracleParameter parm in cmdParams)
{
cmd.Parameters.Add(parm);
}
}
iResult = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
sError = ex.Message;
iResult = -1;
}
}
else // 使用事务
{
OracleTransaction trans = null;
try
{
OracleConnection conn = new OracleConnection(sConnStr);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
trans = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sSQL;
if (cmdParams != null)
{
foreach (OracleParameter parm in cmdParams)
{
cmd.Parameters.Add(parm);
}
}
cmd.Transaction = trans;
iResult = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
sError = ex.Message;
iResult = -1;
trans.Rollback();
}
}
return(iResult > 0);
}
//Affiche FAMILLE JDE ajax (table)
public OracleDataReader GetJDE(string matr)
{
DBConnectJDE c = new DBConnectJDE();
OracleConnection conn = c.GetConnectionJDE();
conn.Open();
OracleTransaction trans = conn.BeginTransaction();
nb = CompterAgentTemp(matr);
if (nb == 0)
{
OracleCommand cmd = new OracleCommand
{
CommandText = "SELECT YAAN8, YAOEMP , YAALPH , YADSC1 , YASEX , YAALPH1, CASE YATRDJ WHEN 0 THEN '-' ELSE to_char(to_date(to_char(1900 + floor(YATRDJ/ 1000)),'YYYY') + mod(YATRDJ,1000) - 1, 'DD-MM-YYYY') END FROM F55EMPME WHERE YAAN8 = '" + matr + "' OR YAOEMP= '" + matr + "'",
Connection = conn,
CommandType = CommandType.Text
};
try
{
// Exécution de la requête
dr = cmd.ExecuteReader();
// On soumet la requête au serveur: tout s'est bien déroulé , la requête est exécutée
trans.Commit();
}
catch (Exception ex)
{
// Une erreur est survenue: on ne valide pas la requête
trans.Rollback();
Console.WriteLine("<body><script >alert('Requête non effectuée !!\nErreur: '" + ex.Message + "'');</script></body>");
}
finally
{ // Libération des ressources
cmd.Dispose();
}
}
else if (nb == 1)
{
OracleCommand cmd = new OracleCommand
{
CommandText = "SELECT YAAN8, YAOEMP , YAALPH , YADSC1 , YASEX , YAALPH1, CASE YATRDJ WHEN 0 THEN '-' ELSE to_char(to_date(to_char(1900 + floor(YATRDJ/ 1000)),'YYYY') + mod(YATRDJ,1000) - 1, 'DD-MM-YYYY') END FROM F55EMPME WHERE YADSC1 <> YAALPH AND YAAN8 = '" + matr + "' OR YAOEMP= '" + matr + "'",
Connection = conn,
CommandType = CommandType.Text
};
try
{
// Exécution de la requête
dr = cmd.ExecuteReader();
// On soumet la requête au serveur: tout s'est bien déroulé , la requête est exécutée
trans.Commit();
}
catch (Exception ex)
{
// Une erreur est survenue: on ne valide pas la requête
trans.Rollback();
Console.WriteLine("<body><script >alert('Requête non effectuée !!\nErreur: '" + ex.Message + "'');</script></body>");
}
finally
{ // Libération des ressources
cmd.Dispose();
}
}
return(dr);
}
/// <summary>
/// Atualiza a tabela de Custódia do sinacor dos clientes
/// </summary>
/// <param name="pListClientes">List<ClienteGradualPremiumInfo> pListClientes - Lista de Clientes a serem atualiados </param>
/// <param name="pTiraCustodia">Booleano para retirar ou inserir custódia nos cliente (351 - tirar a custódia, 151 - insere a custódia)</param>
public void AtualizaCustodiaSinacor(List <ClienteGradualPremiumInfo> pListClientes, bool pTiraCustodia)
{
OracleConnection lConn;
OracleCommand lCommand;
OracleTransaction lTrans = null;
lConn = new OracleConnection(StringConexaoSinacor);
try
{
lConn.Open();
lTrans = lConn.BeginTransaction();
lCommand = new OracleCommand();
lCommand.Connection = lConn;
lCommand.Transaction = lTrans;
lCommand.CommandType = CommandType.Text;
string lQueryCus = string.Empty;
foreach (ClienteGradualPremiumInfo lCliente in pListClientes)
{
if (pTiraCustodia)
{
lQueryCus = string.Format("UPDATE TSCCLICUS SET TP_CUSTODIA = 351 WHERE CD_CLIENTE = {0}", lCliente.CdCliente);
logger.InfoFormat("Inativando Custódia do cliente : {0}", lCliente.CdCliente);
}
else
{
lQueryCus = string.Format("UPDATE TSCCLICUS SET TP_CUSTODIA = 151 WHERE CD_CLIENTE = {0}", lCliente.CdCliente);
logger.InfoFormat("Ativando Custódia do cliente : {0}", lCliente.CdCliente);
}
lCommand.CommandText = string.Format(lQueryCus);
lCommand.ExecuteNonQuery();
}
lTrans.Commit();
logger.InfoFormat("! **************************************************************************************** !");
logger.InfoFormat("! ****************** Atualização de custodia efetuada com sucesso ************************ !");
logger.InfoFormat("! **************************************************************************************** !");
}
catch (Exception ex)
{
lTrans.Rollback();
logger.ErrorFormat("Erro em AtualizaCustodiaSinacor - {0} - Stacktrace - {1}", ex.Message, ex.StackTrace);
}
finally
{
lConn.Close();
logger.InfoFormat("Fechando a conexão no método AtualizaCustodiaSinacor");
}
}
public static void proceedToCheckout()
{
//Validasi Promo + Validasi Ada checked + kurir + payment
//Validasi Ada barang,
string message = validateProceedCheckout();
if (message == "Success")
{
App.openConnection(out _);
using (OracleTransaction Transaction = App.connection.BeginTransaction())
{
try
{
Dictionary <string, int> updateStok = new Dictionary <string, int>();
List <string> id_items = new List <string>();
H_Trans_ItemModel hti = new H_Trans_ItemModel();
DataRow rowHTI = hti.Table.NewRow();
ComboBoxItem cbiPembayaran = (ComboBoxItem)ViewComponent.cbPaymentMethod.SelectedItem;
Metode_PembayaranModel mpm = new Metode_PembayaranModel();
DataRow dr = mpm.Table.Select($"NAMA = '{cbiPembayaran.Content}'").First();
rowHTI["ID"] = 0;
rowHTI["ID_CUSTOMER"] = Session.User["ID"].ToString();
rowHTI["TANGGAL_TRANSAKSI"] = DateTime.Now;
if (checkPromotion(promo, false))
{
rowHTI["ID_PROMO"] = promo.ID;
if (promo.JENIS_POTONGAN == "P")
{
diskon = Convert.ToInt32((Convert.ToDouble(promo.POTONGAN) * Convert.ToDouble(hargaSebelumOngkir)) / 100.0);
diskon = Math.Min(promo.POTONGAN_MAX, diskon);
}
else
{
diskon = promo.POTONGAN;
}
}
else
{
diskon = 0;
}
//grandtotal = hargasebelumongkir + ongkir - diskon
rowHTI["GRANDTOTAL"] = grandTotal - diskon;
rowHTI["SUBTOTAL"] = hargaSebelumOngkir;
rowHTI["ONGKOS_KIRIM"] = ongkos_kirim;
rowHTI["DISKON"] = diskon;
rowHTI["ID_METODE_PEMBAYARAN"] = dr["ID"].ToString();
rowHTI["STATUS"] = "W";
hti.insert(rowHTI);
D_Trans_ItemModel dti = new D_Trans_ItemModel();
int id_hti = generateID_H_Trans();
foreach (ShopCartComponent scc in list_shopcart)
{
foreach (CartComponent cc in scc.getCarts())
{
DataRow rowDTI = dti.Table.NewRow();
rowDTI["ID"] = 0;
rowDTI["ID_H_TRANS_ITEM"] = id_hti;
rowDTI["ID_ITEM"] = cc.getItemID();
id_items.Add(cc.getItemID());
rowDTI["JUMLAH"] = cc.getQuantity();
rowDTI["ID_KURIR"] = scc.getIDKurir();
rowDTI["STATUS"] = "W";
if (updateStok.ContainsKey(cc.getItemID()))
{
updateStok[cc.getItemID()] += Convert.ToInt32(cc.getQuantity());
}
else
{
updateStok.Add(cc.getItemID(), Convert.ToInt32(cc.getQuantity()));
}
dti.insert(rowDTI);
}
}
Transaction.Commit();
//Update Stok
foreach (var val in updateStok)
{
string id_item = val.Key;
int qty = val.Value;
// MessageBox.Show(id_item + " - " + qty);
ItemModel im = new ItemModel();
DataRow item = im.Table.Select($"ID ='{id_item}'").FirstOrDefault();
if (item != null)
{
item["STOK"] = Convert.ToInt32(item["STOK"]) - qty;
}
im.update();
}
//TODO tambah ke history trans :)
foreach (string id_item in id_items)
{
deleteItemFromCart(Convert.ToInt32(id_item), false);
}
loadCartItem();
updateGrandTotal();
TransactionViewModel.initH_Trans();
CustomerViewModel.loadItems();
}
catch (OracleException e)
{
Transaction.Rollback();
MessageBox.Show(e.Message);
}
};
App.closeConnection(out _);
}
else
{
MessageBox.Show(message);
}
}
//删除
private void barBtnDelete_ItemClick(object sender, ItemClickEventArgs e)
{
//记录操作日志
LogUtils.ReviewLogManager.ReviewLog(Properties.Settings.Default.LocalUserName, this.Text + "-" + this.barBtnDelete.Caption);
if (this.gvDataInfo.DataSource == null)
{
XtraMessageBox.Show("没有可以操作的记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
this.gvDataInfo.PostEditor();
var dataSource = (DataView)this.gvDataInfo.DataSource;
var dtSelected = dataSource.Table.Copy();
dtSelected.Clear();
if (dataSource != null && dataSource.Table.Rows.Count > 0)
{
for (int i = 0; i < dataSource.Table.Rows.Count; i++)
{
bool result = false;
bool.TryParse(dataSource.Table.Rows[i]["check"].ToString(), out result);
if (result)
{
dtSelected.Rows.Add(dataSource.Table.Rows[i].ItemArray);
}
}
}
if (dtSelected.Rows.Count == 0)
{
XtraMessageBox.Show("请选择您要操作的记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (XtraMessageBox.Show("确定要删除吗？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.OK)
{
return;
}
using (OracleConnection conn = new OracleConnection(OracleHelper.conn))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
foreach (DataRow dr in dtSelected.Rows)
{
try
{
OracleHelper.ExecuteNonQuery(OracleHelper.conn, string.Format("DELETE FROM DB_CONTACTS WHERE ID = '{0}'", dr["ID"]), null);
}
catch (Exception ex)
{
trans.Rollback();
XtraMessageBox.Show(String.Format("数据库操作出现异常，删除失败：{0}！", ex.Message), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
if (trans.Connection != null)
{
trans.Commit();
}
}
}
this.refrashCurrentPage();
}
public string SaveBomEntry(BOMModel objBOMModel)
{
string strMsg = "";
int x = objBOMModel.ItemDescription.Count();
for (int i = 0; i < x; i++)
{
var arrayItemName = objBOMModel.ItemName[i];
var arrayItemDescription = objBOMModel.ItemDescription[i];
var arrayModelCode = objBOMModel.ModelCode[i];
var arrayComponentName = objBOMModel.ComponentName[i];
var arrayColorId = objBOMModel.ColorId[i];
var arrayColorName = objBOMModel.ColorName[i];
var arrayFabricQuantity = objBOMModel.FabricQuantity[i];
var arrayUnitId = objBOMModel.UnitId[i];
var arrayRemarks = objBOMModel.Remarks[i];
var arrayTranId = objBOMModel.TranId[i];
OracleTransaction objOracleTransaction = null;
OracleCommand objOracleCommand = new OracleCommand("pro_bill_of_material_save");
objOracleCommand.CommandType = CommandType.StoredProcedure;
if (objBOMModel.StyleNo != "")
{
objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.StyleNo;
}
else
{
objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
}
if (objBOMModel.BuyerId != "")
{
objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.BuyerId;
}
else
{
objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
}
if (objBOMModel.SeasonYear != "")
{
objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonYear;
}
else
{
objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
}
if (objBOMModel.SeasonId != "")
{
objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonId;
}
else
{
objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
}
if (objBOMModel.R3Code != "")
{
objOracleCommand.Parameters.Add("p_R3_CODE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.R3Code;
}
else
{
objOracleCommand.Parameters.Add("p_R3_CODE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
}
if (objBOMModel.LastUpdateDate != "")
{
objOracleCommand.Parameters.Add("p_LAST_UPDATE_DATE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.LastUpdateDate;
}
else
{
objOracleCommand.Parameters.Add("p_LAST_UPDATE_DATE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
}
if (arrayItemName != "")
{
objOracleCommand.Parameters.Add("p_ITEM_NAME", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = arrayItemName;
}
else
{
objOracleCommand.Parameters.Add("p_ITEM_NAME", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null;
}
if (arrayItemDescription != null)
{
objOracleCommand.Parameters.Add("p_ITEM_DESCRIPTION", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayItemDescription;
}
else
{
objOracleCommand.Parameters.Add("p_ITEM_DESCRIPTION", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
}
if (arrayModelCode != null)
{
objOracleCommand.Parameters.Add("p_MODEL_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayModelCode;
}
else
{
objOracleCommand.Parameters.Add("p_MODEL_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
}
if (arrayComponentName != null)
{
objOracleCommand.Parameters.Add("p_COMPONENT_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayComponentName;
}
else
{
objOracleCommand.Parameters.Add("p_COMPONENT_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
}
if (arrayColorId != null)
{
objOracleCommand.Parameters.Add("p_COLOR_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayColorId;
}
else
{
objOracleCommand.Parameters.Add("p_COLOR_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
}
if (arrayColorName != null)
{
objOracleCommand.Parameters.Add("p_COLOR_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayColorName;
}
else
{
objOracleCommand.Parameters.Add("p_COLOR_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
}
if (arrayFabricQuantity != null)
{
objOracleCommand.Parameters.Add("p_FABRIC_QUANTITY", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayFabricQuantity;
}
else
{
objOracleCommand.Parameters.Add("p_FABRIC_QUANTITY", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
}
if (arrayUnitId != null)
{
objOracleCommand.Parameters.Add("p_UNIT_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayUnitId;
}
else
{
objOracleCommand.Parameters.Add("p_UNIT_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
}
if (arrayRemarks != null)
{
objOracleCommand.Parameters.Add("p_REMARKS", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayRemarks;
}
else
{
objOracleCommand.Parameters.Add("p_REMARKS", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
}
if (arrayTranId != null)
{
objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayTranId;
}
else
{
objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null;
}
objOracleCommand.Parameters.Add("p_update_by", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.UpdateBy;
objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.HeadOfficeId;
objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.BranchOfficeId;
objOracleCommand.Parameters.Add("P_MESSAGE", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output;
using (OracleConnection strConn = GetConnection())
{
try
{
objOracleCommand.Connection = strConn;
strConn.Open();
trans = strConn.BeginTransaction();
objOracleCommand.ExecuteNonQuery();
trans.Commit();
strConn.Close();
strMsg = objOracleCommand.Parameters["P_MESSAGE"].Value.ToString();
}
catch (Exception ex)
{
throw new Exception("Error : " + ex.Message);
objOracleTransaction.Rollback();
}
finally
{
strConn.Close();
}
}
}
return(strMsg);
}
/// <summary>
/// 事务回滚
/// </summary>
public override void RollbackTrans()
{
_trans.Rollback();
_isInTransaction = false;
}
//************************************************************
//** 저장 버튼 Click (여러 건의 DATA 추가입력/수정 후 저장)
//************************************************************
public void BtnSave_Click()
{
if (MessageBox.Show("입력 및 수정중인 자료를 저장합니다.", "저장확인", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
return;
}
if (!InputCheck())
{
return;
}
OracleTransaction tran = null;
try
{
con = Utility.SetOracleConnection();
tran = con.BeginTransaction(IsolationLevel.ReadCommitted);
OracleCommand cmd = con.CreateCommand();
cmd.BindByName = true;
cmd.Transaction = tran;
for (int i = 0; i <= myViewModel.Count - 1; i++)
{
UcSubC05ViewModel vm = (UcSubC05ViewModel)myViewModel.ElementAt(i);
if (vm.DataStatus.Equals(""))
{
continue;
}
if (vm.DataStatus.Equals("A"))
{
cmd.CommandText = SQLStatement.InsertSQL;
}
if (vm.DataStatus.Equals("U"))
{
cmd.CommandText = SQLStatement.UpdateSQL;
cmd.Parameters.Add("key1", OracleDbType.Varchar2).Value = vm.Key1;
cmd.Parameters.Add("key2", OracleDbType.Varchar2).Value = vm.Key2;
}
cmd.Parameters.Add("award_empno", OracleDbType.Varchar2).Value = vm.Award_empno;
cmd.Parameters.Add("award_date", OracleDbType.Varchar2).Value = Utility.FormatDateR(vm.Award_date);
cmd.Parameters.Add("award_type", OracleDbType.Varchar2).Value = vm.Award_type_Cd;
cmd.Parameters.Add("award_no", OracleDbType.Varchar2).Value = vm.Award_no;
cmd.Parameters.Add("award_kind", OracleDbType.Varchar2).Value = vm.Award_kind;
cmd.Parameters.Add("award_organ", OracleDbType.Varchar2).Value = vm.Award_organ;
cmd.Parameters.Add("award_content", OracleDbType.Varchar2).Value = vm.Award_content;
cmd.Parameters.Add("award_inout", OracleDbType.Varchar2).Value = vm.Award_inout;
cmd.Parameters.Add("award_pos", OracleDbType.Varchar2).Value = vm.Award_pos;
cmd.Parameters.Add("award_dept", OracleDbType.Varchar2).Value = vm.Award_dept;
cmd.Parameters.Add("datasys3", OracleDbType.Varchar2).Value = string.Concat(UserId, ":", UserNm);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear(); //*----반드시 포함
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
MessageBox.Show(ex.Message);
return;
}
finally
{
if (con != null)
{
con.Close();
}
}
//**정상 저장 후 초기화*******************************************************
for (int i = 0; i <= myViewModel.Count - 1; i++)
{
UcSubC05ViewModel vm = (UcSubC05ViewModel)myViewModel.ElementAt(i);
if (vm.DataStatus.Equals(""))
{
continue;
}
vm.Key1 = vm.Award_empno;
vm.Key2 = vm.Award_date;
vm.DataStatus = "";
}
dataGrid.RefreshData();
UserMessage.Text = "자료가 정상적으로 저장 되었습니다.";
Utility.SetFuncBtn(MainBtn, "2");
}
public long Save(UserEntity entity)
{
string PREFERREDCURRENCY = ""; string PREFERREDCURRENCYCODE = "";
OracleConnection mFocisConnection;
OracleTransaction mfocisOracleTransaction = null;
using (mFocisConnection = new OracleConnection(GetConnectionString(configuration, DBConnectionMode.FOCiS)))
{
if (mFocisConnection.State == ConnectionState.Closed)
{
mFocisConnection.Open();
}
mfocisOracleTransaction = mFocisConnection.BeginTransaction();
using (OracleCommand mCommand = mFocisConnection.CreateCommand())
{
mCommand.BindByName = true;
mCommand.Transaction = mfocisOracleTransaction;
mCommand.CommandType = CommandType.Text;
try
{
mCommand.CommandText = "SELECT CURRENCYNAME,CURRENCYCODE FROM FOCIS.CURRENCIES WHERE CURRENCYID IN (SELECT BASECURRENCYID FROM FOCIS.SMDM_COUNTRY WHERE countryid=" + entity.COUNTRYID + ")";
DataTable dt = ReturnDatatable(GetConnectionString(configuration, DBConnectionMode.FOCiS), mCommand.CommandText, null);
if (dt.Rows.Count > 0)
{
PREFERREDCURRENCY = dt.Rows[0]["CURRENCYNAME"].ToString();
PREFERREDCURRENCYCODE = dt.Rows[0]["CURRENCYCODE"].ToString();
}
else
{
PREFERREDCURRENCY = "US Dollar";
PREFERREDCURRENCYCODE = "USD";
}
}
catch (Exception ex)
{
mfocisOracleTransaction.Rollback();
// throw new ShipaDbException(ex.ToString());
}
}
}
using (mConnection = new OracleConnection(GetConnectionString(configuration, DBConnectionMode.SSP)))
{
if (mConnection.State == ConnectionState.Closed)
{
mConnection.Open();
}
mOracleTransaction = mConnection.BeginTransaction();
using (OracleCommand mCommand = mConnection.CreateCommand())
{
mCommand.BindByName = true;
mCommand.Transaction = mOracleTransaction;
mCommand.CommandType = CommandType.Text;
try
{
if (string.IsNullOrEmpty(entity.COUNTRYCODE))
{
entity.COUNTRYCODE = "";
}
mCommand.CommandText = @"INSERT INTO USERS (USERID,USERTYPE,EMAILID,PASSWORD,DATECREATED,FIRSTNAME,LASTNAME,COMPANYNAME,
ISDID,ISDCODE,MOBILENUMBER,ISTERMSAGREED,COUNTRYID,ACCOUNTSTATUS,TIMEZONEID,USERCULTUREID,TOKEN,TOKENEXPIRY,COUNTRYCODE,COUNTRYNAME,
PREFERREDCURRENCY,PREFERREDCURRENCYCODE,NOTIFICATIONSUBSCRIPTION,SHIPMENTPROCESS,SHIPPINGEXPERIENCE) VALUES
(:USERID,:USERTYPE,:EMAILID,:PASSWORD,:DATECREATED,:FIRSTNAME,:LASTNAME,:COMPANYNAME,:ISDID,:ISDCODE,:MOBILENUMBER,:ISTERMSAGREED,
:COUNTRYID,:ACCOUNTSTATUS,:TIMEZONEID,:USERCULTUREID,:TOKEN,:TOKENEXPIRY,:COUNTRYCODE,:COUNTRYNAME,:PREFERREDCURRENCY,
:PREFERREDCURRENCYCODE,:NOTIFICATIONSUBSCRIPTION,:SHIPMENTPROCESS,:SHIPPINGEXPERIENCE)";
mCommand.Parameters.Add("USERID", entity.USERID.ToLower());
mCommand.Parameters.Add("USERTYPE", entity.USERTYPE);
mCommand.Parameters.Add("EMAILID", entity.EMAILID);
mCommand.Parameters.Add("PASSWORD", entity.PASSWORD);
mCommand.Parameters.Add("DATECREATED", DateTime.Now);
mCommand.Parameters.Add("FIRSTNAME", entity.FIRSTNAME);
mCommand.Parameters.Add("LASTNAME", entity.LASTNAME);
mCommand.Parameters.Add("COMPANYNAME", entity.COMPANYNAME);
mCommand.Parameters.Add("ISDID", entity.ISDID);
mCommand.Parameters.Add("ISDCODE", entity.ISDCODE);
mCommand.Parameters.Add("MOBILENUMBER", entity.MOBILENUMBER);
mCommand.Parameters.Add("ISTERMSAGREED", entity.ISTERMSAGREED);
mCommand.Parameters.Add("COUNTRYID", entity.COUNTRYID);
mCommand.Parameters.Add("ACCOUNTSTATUS", entity.ACCOUNTSTATUS);
mCommand.Parameters.Add("TIMEZONEID", entity.TIMEZONEID);
mCommand.Parameters.Add("USERCULTUREID", entity.USERCULTUREID);
mCommand.Parameters.Add("TOKEN", entity.TOKEN);
mCommand.Parameters.Add("TOKENEXPIRY", entity.TOKENEXPIRY);
mCommand.Parameters.Add("COUNTRYCODE", entity.COUNTRYCODE.ToUpper());
mCommand.Parameters.Add("COUNTRYNAME", entity.COUNTRYNAME);
mCommand.Parameters.Add("PREFERREDCURRENCY", PREFERREDCURRENCY);
mCommand.Parameters.Add("PREFERREDCURRENCYCODE", PREFERREDCURRENCYCODE);
mCommand.Parameters.Add("NOTIFICATIONSUBSCRIPTION", entity.NOTIFICATIONSUBSCRIPTION.Equals(true) ? 1 : 0);
mCommand.Parameters.Add("SHIPMENTPROCESS", entity.SHIPMENTPROCESS);
mCommand.Parameters.Add("SHIPPINGEXPERIENCE", entity.SHIPPINGEXPERIENCE);
mCommand.ExecuteNonQuery();
mOracleTransaction.Commit();
}
catch (Exception ex)
{
mOracleTransaction.Rollback();
// throw new ShipaDbException(ex.ToString());
}
}
//SSP NOTIFICATIONS ADDED BY SIVA
if (mConnection.State == ConnectionState.Closed)
{
mConnection.Open();
}
mOracleTransaction = mConnection.BeginTransaction();
using (OracleCommand mCommand = mConnection.CreateCommand())
{
mCommand.BindByName = true;
mCommand.Transaction = mOracleTransaction;
mCommand.CommandType = CommandType.Text;
try
{
mCommand.CommandText = "SELECT SSP_NOTIFICATIONS_SEQ.NEXTVAL FROM DUAL";
entity.NOTIFICATIONID = Convert.ToInt64(mCommand.ExecuteScalar());
mCommand.CommandText = "INSERT INTO SSPNOTIFICATIONS (NOTIFICATIONID,NOTIFICATIONTYPEID,USERID,DATECREATED,DATEREQUESTED,NOTIFICATIONCODE) VALUES (:NOTIFICATIONID,:NOTIFICATIONTYPEID,:USERID,:DATECREATED,:DATEREQUESTED,:NOTIFICATIONCODE)";
mCommand.Parameters.Add("NOTIFICATIONID", Convert.ToInt32(entity.NOTIFICATIONID));
mCommand.Parameters.Add("NOTIFICATIONTYPEID", 5111);
mCommand.Parameters.Add("USERID", entity.USERID.ToUpper());
mCommand.Parameters.Add("DATECREATED", DateTime.Now);
mCommand.Parameters.Add("DATEREQUESTED", DateTime.Now);
mCommand.Parameters.Add("NOTIFICATIONCODE", "Profile Incomplete");
mCommand.ExecuteNonQuery();
mOracleTransaction.Commit();
}
catch (Exception ex)
{
mOracleTransaction.Rollback();
// throw new ShipaDbException(ex.ToString());
}
}
}
//--------------------Sales Force---------------
using (mConnection = new OracleConnection(GetConnectionString(configuration, DBConnectionMode.FOCiS)))
{
if (mConnection.State == ConnectionState.Closed)
{
mConnection.Open();
}
mOracleTransaction = mConnection.BeginTransaction();
using (OracleCommand mCommand = mConnection.CreateCommand())
{
mCommand.Parameters.Clear();
mCommand.BindByName = true;
mCommand.Transaction = mOracleTransaction;
mCommand.CommandType = CommandType.StoredProcedure;
mCommand.CommandText = "USP_SSP_CEPREQUOTEPROFILE";
try
{
mCommand.Parameters.Add("PRM_GUESTNAME", Convert.ToString(entity.FIRSTNAME));
mCommand.Parameters.Add("PRM_GUESTCOUNTRYCODE", entity.COUNTRYCODE);
mCommand.Parameters.Add("PRM_GUESTCOUNTRYNAME", entity.COUNTRYNAME);
mCommand.Parameters.Add("PRM_GUESTCOMPANY", entity.COMPANYNAME);
mCommand.Parameters.Add("PRM_GUESTEMAIL", entity.USERID);
mCommand.Parameters.Add("PRM_TELEPHONENUMBER", Convert.ToInt64(entity.MOBILENUMBER));
mCommand.Parameters.Add("OP_RESULTSET", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
mCommand.ExecuteNonQuery();
mOracleTransaction.Commit();
}
catch (Exception ex)
{
mOracleTransaction.Rollback();
// throw new ShipaDbException(ex.ToString());
}
finally
{
mConnection.Dispose();
mCommand.Dispose();
mOracleTransaction.Dispose();
}
}
}
//-----------------------------------------------
return(1);
}
//保存变更申请单
private void simpleButton1_Click(object sender, EventArgs e)
{
string applyType = string.Empty;
if (INSERT_O.Checked)
{
applyType = INSERT_O.Text + "-";
}
if (UPDATE.Checked)
{
applyType += UPDATE.Text + "-";
}
if (DELETE.Checked)
{
applyType += DELETE.Text + "-";
}
applyType = applyType.TrimEnd('-');
if (gridControl1.DataSource == null)
{
MessageBox.Show("没有要保存的数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
System.Data.DataView dv = ((System.Data.DataTable)gridControl1.DataSource).AsDataView();
if (dv == null || dv.Table.Select("VIN not is null or VIN <> ''").Length < 1)
{
MessageBox.Show("没有要保存的数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
using (OracleConnection con = new OracleConnection(OracleHelper.conn))
{
con.Open();
OracleTransaction tra = con.BeginTransaction();
try
{
SplashScreenManager.ShowForm(typeof(DevWaitForm));
string delsql = string.Format("delete from DATA_CHANGE_BASE where ID='{0}'", guid);
int val = OracleHelper.ExecuteNonQuery(tra, delsql, null);
string sql = string.Format("insert into DATA_CHANGE_BASE values('{11}','{0}',to_date('{1}','yyyy-mm-dd hh24:mi:ss'),'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}' )", this.QCSCQYMC.Text, this.APPLYDATA.Text, applyType, INSERT_O_RANGE.Text, UPDATE_RANGE.Text, DELETE_RANGE.Text, INSERT_O_SL.Text, UPDATE_SL.Text, DELETE_SL.Text, REASON.Text, REMARK.Text, guid);
OracleHelper.ExecuteNonQuery(tra, sql, null);
string delsqldata = string.Format("delete from DATA_CHANGE where DID='{0}'", guid);
OracleHelper.ExecuteNonQuery(tra, delsqldata, null);
foreach (DataRow r in dv.Table.Rows)
{
string sqlData = "insert into DATA_CHANGE values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')";
string sqlDataStr = string.Format(sqlData, guid, r["VIN"], r["CLXH"], r["TYMC"], r["rllx"], r["CT_ZHGKRLXHL"], r["ZCZBZL"], r["CT_BSQXS"], r["ZWPS"], r["UPDATEFIELD"], r["FIELDOLD"], r["FIELDNEW"], r["APPLYTYPE"]);
OracleHelper.ExecuteNonQuery(tra, sqlDataStr, null);
}
tra.Commit();
MessageBox.Show("操作成功,可以生成WORD或发送管理员", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
tra.Rollback();
MessageBox.Show("操作失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
SplashScreenManager.CloseForm();
}
}
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant d'inscrire un nouvel intervenant sans nuité
/// </summary>
/// <param name="Cmd">nom de l'objet command concerné par les paramètres</param>
/// <param name="pNom">nom du participant</param>
/// <param name="pPrenom">prénom du participant</param>
/// <param name="pAdresse1">adresse1 du participant</param>
/// <param name="pAdresse2">adresse2 du participant</param>
/// <param name="pCp">cp du participant</param>
/// <param name="pVille">ville du participant</param>
/// <param name="pTel">téléphone du participant</param>
/// <param name="pMail">mail du participant</param>
/// <param name="pIdQualite">qualité du licencié</param>
/// <param name="pNumeroLicence">numéro de licence du licencié</param>
/// <param name="pLesAteliers">collection des ateliers du licencié</param>
/// <param name="pLesCategories">collection des catégories de l'hotel du licencié</param>
/// <param name="pLesHotels">collection des hotels du licencié</param>
/// <param name="pLesNuits">collection des nuits du licencié</param>
/// <param name="pNumCheque">numéro du cheque du licencié</param>
/// <param name="pMontantCheque">montant du cheque du licencié</param>
/// <param name="pLesAccompagnants">collection des accompagnants du licencié</param>
/// <param name="pInscription">type du paiement du licencié</param>
public void InscrireLicencie(String pNom, String pPrenom, String pAdresse1, String pAdresse2, String pCp, String pVille, String pTel, String pMail, Int16 pIdQualite, Int64 pNumeroLicence, Collection<Int16> pLesAteliers, Collection<string> pLesCategories, Collection<string> pLesHotels, Collection<Int16> pLesNuits, Int64 pNumCheque, Double pMontantCheque, Collection<Int16> pLesAccompagnants, String pInscription)
{
String MessageErreur = "";
try
{
// pckparticipant.nouvelintervenant est une procédure surchargée
UneOracleCommand = new OracleCommand("pckparticipant.nouveaulicencie", CnOracle);
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// début de la transaction Oracle : il vaut mieyx gérer les transactions dans l'applicatif que dans la bd.
UneOracleTransaction = this.CnOracle.BeginTransaction();
this.ParamCommunsNouveauxParticipants(UneOracleCommand, pNom, pPrenom, pAdresse1, pAdresse2, pCp, pVille, pTel, pMail);
this.ParamsSpecifiquesLicencie(UneOracleCommand, pIdQualite, pNumeroLicence, pNumCheque, pMontantCheque, pInscription);
if (pLesAteliers.Count == 0)
{
pLesAteliers.Add(0);
}
// Paramètre qui stocke les ateliers sélectionnées
OracleParameter pOraLesAteliers = new OracleParameter();
pOraLesAteliers.ParameterName = "pLesAteliers";
pOraLesAteliers.OracleDbType = OracleDbType.Int16;
pOraLesAteliers.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesAteliers.Value = pLesAteliers.ToArray();
pOraLesAteliers.Size = pLesAteliers.Count;
UneOracleCommand.Parameters.Add(pOraLesAteliers);
//On va créer ici les paramètres spécifiques à l'inscription d'un intervenant qui réserve des nuits d'hôtel.
// Paramètre qui stocke les catégories sélectionnées
OracleParameter pOraLescategories = new OracleParameter();
pOraLescategories.ParameterName = "pLesCategories";
pOraLescategories.OracleDbType = OracleDbType.Char;
pOraLescategories.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLescategories.Value = pLesCategories.ToArray();
pOraLescategories.Size = pLesCategories.Count;
UneOracleCommand.Parameters.Add(pOraLescategories);
// Paramètre qui stocke les hotels sélectionnées
OracleParameter pOraLesHotels = new OracleParameter();
pOraLesHotels.ParameterName = "pLesHotels";
pOraLesHotels.OracleDbType = OracleDbType.Char;
pOraLesHotels.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesHotels.Value = pLesHotels.ToArray();
pOraLesHotels.Size = pLesHotels.Count;
UneOracleCommand.Parameters.Add(pOraLesHotels);
// Paramètres qui stocke les nuits sélectionnées
OracleParameter pOraLesNuits = new OracleParameter();
pOraLesNuits.ParameterName = "pLesNuits";
pOraLesNuits.OracleDbType = OracleDbType.Int16;
pOraLesNuits.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesNuits.Value = pLesNuits.ToArray();
pOraLesNuits.Size = pLesNuits.Count;
UneOracleCommand.Parameters.Add(pOraLesNuits);
if (pLesAccompagnants.Count == 0)
{
pLesAccompagnants.Add(0);
}
// Paramètres qui stocke les accompagnants sélectionnées
OracleParameter pOraLesAccompagnants = new OracleParameter();
pOraLesAccompagnants.ParameterName = "plesaccompagnants";
pOraLesAccompagnants.OracleDbType = OracleDbType.Int16;
pOraLesAccompagnants.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesAccompagnants.Value = pLesAccompagnants.ToArray();
pOraLesAccompagnants.Size = pLesAccompagnants.Count;
UneOracleCommand.Parameters.Add(pOraLesAccompagnants);
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
//MessageErreur="Erreur Oracle \n" + this.GetMessageOracle(Oex.Message);
MessageBox.Show(Oex.Message);
}
catch (Exception ex)
{
MessageErreur = "Autre Erreur, les informations n'ont pas été correctement saisies";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
