private void Save2ndApproval(Oracle.DataAccess.Client.OracleConnection conn)
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training.req_apprvd_by_2nd_sprvsor", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
Oracle.DataAccess.Client.OracleParameter parm = null;
try
{
int intFileLen = txtMemo.Text.Length;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", "");
ViewState["Saved"] = "";
cmd.ExecuteNonQuery();
if ((cmd.Parameters["error_msg_"].Value != null) && (cmd.Parameters["error_msg_"].Value.ToString() != ""))
{
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
ViewState["Saved"] = "F";
}
}
finally
{
if (parm != null)
{
parm = null;
}
cmd.Dispose();
}
}
public static void SavePDF(string session_id_, string strName, byte[] bValue)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Enrollment_Wizard_LE.save_LE_Doc_upload", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "session_id_", session_id_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_name_", strName);
Oracle.DataAccess.Client.OracleParameter parm;
parm = new Oracle.DataAccess.Client.OracleParameter(
"value_", Oracle.DataAccess.Client.OracleDbType.Blob, bValue.Length, System.Data.ParameterDirection.Input, true,
((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, bValue);
cmd.Parameters.Add(parm);
try
{
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
conn.Clone();
conn.Dispose();
}
}
public void insertMessengerRecordIntoDB(string filePath, string startTime, string endTime, int portNumber)
{
try
{
// 오라클 연결
conn = new Oracle.DataAccess.Client.OracleConnection(connectionInfo);
conn.Open();
//명령 실행을 위한 커맨드 객체 생성
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
cmd.Connection = conn;
// SQL문 지정 및 INSERT 실행
cmd.CommandText = "insert into KBJ_MESSENGER(start_time, end_time, file_path, port_number) " +
"values(TO_DATE('" + startTime + "', 'YYYY.MM.DD-HH24:MI:SS')," +
" TO_DATE('" + endTime + "', 'YYYY.MM.DD-HH24:MI:SS'), '" +
filePath + "', " + portNumber + " )";
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception)
{
MessageBox.Show(LanguageResource.language_res.strExceptionMessageDBInsertFailed);
}
finally
{
conn.Close();
}
}
private DataTable GetGridTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_training.getlearningcategories", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", ViewState["Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", ViewState["Processing_Year"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "learningcategorylist_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private bool DoSave_cdp()
{
bool blnSaved = true;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training_cdp.req_apprvd_by_sprvsor", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
try
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", "");
ViewState["Saved"] = "";
conn.Open();
cmd.ExecuteNonQuery();
if (cmd.Parameters["error_msg_"].Value != null)
{
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
ViewState["Saved"] = "F";
}
}
catch
{
blnSaved = false;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
return(blnSaved);
}
private DataTable GetGridTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_training.paid_request_list", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", ViewState["Adjust_Employee"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "paid_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private DataTable GetExpenseTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_training.GetEmployeeExpenses", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Expenses_List_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
public static void alter(string sqlAlter)
{
Oracle.DataAccess.Client.OracleCommand cmdAlter = new Oracle.DataAccess.Client.OracleCommand(sqlAlter, conn);
cmdAlter.ExecuteNonQuery();
cmdAlter.Dispose();
}
public static void Request_info_doc(string employee_number_, string account_number_, string log_user_name_, string r_log_id_,
string reason_, string doc_name_, byte[] bValue)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Dependent_Audit_Wizard.Request_info_doc", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", employee_number_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "account_number_", account_number_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "log_user_name_", log_user_name_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "r_log_id_", r_log_id_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", reason_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_name_", doc_name_);
Oracle.DataAccess.Client.OracleParameter parm;
parm = new Oracle.DataAccess.Client.OracleParameter(
"value_", Oracle.DataAccess.Client.OracleDbType.Blob, bValue.Length, System.Data.ParameterDirection.Input, true,
((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, bValue);
cmd.Parameters.Add(parm);
try
{
cmd.ExecuteNonQuery();
}
finally
{
SQLStatic.SQL.CloseConnection(conn);
cmd.Dispose();
}
}
private void ExcecuteNonQuery(Oracle.DataAccess.Client.OracleCommand command, string storedProcName, Oracle.DataAccess.Client.OracleConnection dbConnection)
{
command.Connection = dbConnection;
command.CommandText = storedProcName;
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
}
public static void insert(string sqlInsert)
{
Oracle.DataAccess.Client.OracleCommand cmdInsert = new Oracle.DataAccess.Client.OracleCommand(sqlInsert, conn);
cmdInsert.ExecuteNonQuery();
cmdInsert.Dispose();
}
public static void delete(string sqlDelete)
{
Oracle.DataAccess.Client.OracleCommand cmdDelete = new Oracle.DataAccess.Client.OracleCommand(sqlDelete, conn);
cmdDelete.ExecuteNonQuery();
cmdDelete.Dispose();
}
private void FillOneDropDown(DropDownList ddl, string strTableName)
{
ddl.Items.Clear();
ddl.Items.Add(new ListItem("---- Select ----", "xx"));
string strSQL = "select record_id,description from " + strTableName;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strSQL, conn);
conn.Open();
Oracle.DataAccess.Client.OracleDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
ListItem li = new ListItem(reader.GetValue(1).ToString(), reader.GetValue(0).ToString());
ddl.Items.Add(li);
}
}
finally
{
reader.Dispose();
cmd.Dispose();
conn.Dispose();
}
}
private bool DoSave()
{
string strProcedureName = "basdba.PKG_Training.SupervisorCancelRequest";
if (Request.Params["who"] == "payor")
{
strProcedureName = "basdba.PKG_Training.AdministratorCancelRequest";
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
bool blnOk = true;
try
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", "varchar2", "in", txtOther.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", "");
cmd.ExecuteNonQuery();
if ((cmd.Parameters["error_msg_"].Value != null) && (cmd.Parameters["error_msg_"].Value.ToString() != ""))
{
blnOk = false;
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
}
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
return(blnOk);
}
private DataTable GetGridTable()
{
DataTable tbl = null;
string strProcedureName = "BASDBA.pkg_training.History_List";
if (ViewState["Product_Code"].ToString() == "11001")
{
strProcedureName = "BASDBA.pkg_training_cdp.History_List";
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "hstList_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
public static void SavePDF(string account_number_, string employee_number_, string dependent_sequence_no_, string user_name_,
string doc_name_, byte[] bValue)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_dependents_audit.save_Verify_Doc_upload", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "account_number_", account_number_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", employee_number_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "dependent_sequence_no_", dependent_sequence_no_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", user_name_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_name_", doc_name_);
Oracle.DataAccess.Client.OracleParameter parm;
parm = new Oracle.DataAccess.Client.OracleParameter(
"value_", Oracle.DataAccess.Client.OracleDbType.Blob, bValue.Length, System.Data.ParameterDirection.Input, true,
((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, bValue);
cmd.Parameters.Add(parm);
try
{
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
conn.Clone();
conn.Dispose();
}
}
private DataTable GetDataTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_training.ExpenseAdjustmentList", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "expense_record_id_", Request.Params["recid"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "adjustmentList_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private void ResetCDPStatus()
{
string strEmployee_Number = ViewState["Employee_Number"].ToString();
string strSQL = "select pkg_training_cdp.headerRecordID('" + strEmployee_Number + "') from dual";
string cdp_Header_id = SQLStatic.SQL.ExecScaler(strSQL, Training_Source.TrainingClass.ConnectioString).ToString();
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_training_cdp.set_application_status", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", cdp_Header_id);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", ViewState["Processing_Year"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", ViewState["User_Name"].ToString());
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch {}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
/// <summary>
/// 更新大文本字符串保存到数据库
/// 王方圆添加 2017-5-16
/// </summary>
/// <param name="tableName">表明</param>
/// <param name="id">唯一标识ID</param>
/// <param name="longTextFieldName">字段名称</param>
/// <param name="longTextContent">更新文本内容</param>
public static void UpdateLongText(string tableName, long id, string longTextFieldName, string longTextContent)
{
string connectStr = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultDB"].ConnectionString;
using (Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connectStr))
{
conn.Open();
//OracleTransaction trans = conn.BeginTransaction();
Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand();
//cmd.Transaction = trans;
cmd.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :templob := xx; end;";
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter(":templob", Oracle.DataAccess.Client.OracleDbType.Clob)).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
OracleClob tmplob = (OracleClob)cmd.Parameters[0].Value;
byte[] buffer = System.Text.Encoding.Unicode.GetBytes(longTextContent);
tmplob.Write(buffer, 0, buffer.Length);
tmplob.Position = 0;
cmd.Parameters.Clear();
string cmdText = "update {0} set {1} = :lob where EMAIL_ID= :id";
cmdText = string.Format(cmdText, tableName, longTextFieldName);
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter(":lob", Oracle.DataAccess.Client.OracleDbType.Clob)).Value = tmplob;
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter(":id", id));
cmd.ExecuteNonQuery();
}
}
private bool DoSave_cdp()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training_cdp.req_dclnd_by_sprvsor", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
bool blnOk = true;
try
{
int intFileLen = txtMemo.Text.Length;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", "varchar2", "in", txtMemo.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", "");
cmd.ExecuteNonQuery();
if (cmd.Parameters["error_msg_"].Value != null)
{
blnOk = false;
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
}
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
return(blnOk);
}
//有返回值的SQL执行
public object ExecuteScalar(string cmdText)
{
cmdText = SqlTransfer(cmdText);
object retVal = null;
using (Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connStr))
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdText, conn);
cmd.CommandType = CommandType.Text;
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
retVal = cmd.ExecuteScalar();
}
catch (Exception exp)
{
LogWriter.Error(exp, cmdText);
//retVal = (object)exp.Message;
throw exp;
}
finally
{
conn.Close();
}
}
return(retVal);
}
public string First_step_in_wizard()
{
string FirststepinWiz = null;
string sessID = Request.Cookies["Session_ID"].Value.ToString();
// create the command for the function
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
Oracle.DataAccess.Client.OracleConnection conn =
new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
cmd.Connection = conn;
cmd.CommandText = "pkg_wizard.First_step_in_wizard";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("session_id_", Oracle.DataAccess.Client.OracleDbType.Varchar2, 100, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, sessID));
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("first_step_id_", Oracle.DataAccess.Client.OracleDbType.Double, 20, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("first_step_url_", Oracle.DataAccess.Client.OracleDbType.Varchar2, 255, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
// execute the function
conn.Open();
cmd.ExecuteNonQuery();
FirststepinWiz = Convert.ToString(cmd.Parameters[2].Value);
cmd.Dispose();
conn.Close();
conn.Dispose();
return(FirststepinWiz);
}
private bool DoSave(Oracle.DataAccess.Client.OracleConnection conn)
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training.req_Partial_Payment", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
bool blnOk = true;
Oracle.DataAccess.Client.OracleParameter parm = null;
try
{
int intFileLen = txtMemo.Text.Length;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", "varchar2", "in", txtMemo.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", "");
cmd.ExecuteNonQuery();
if ((cmd.Parameters["error_msg_"].Value != null) && (cmd.Parameters["error_msg_"].Value.ToString() != ""))
{
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
blnOk = false;
}
}
catch
{
}
finally
{
if (parm != null)
{
parm = null;
}
cmd.Dispose();
}
return(blnOk);
}
private void FillReasonCheckBoxList()
{
System.Data.DataTable dTable;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd =
new Oracle.DataAccess.Client.OracleCommand("pkg_training.reason_for_admin_partial_list", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_list_", "cursor", "out", "");
Oracle.DataAccess.Client.OracleDataAdapter da =
new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataSet mds = new DataSet();
conn.Open();
try
{
da.Fill(mds, "Purpose");
dTable = mds.Tables["Purpose"];
chklstReasons.Items.Clear();
foreach (DataRow dr in dTable.Rows)
{
ListItem li = new ListItem(dr["description"].ToString(), dr["record_id"].ToString());
chklstReasons.Items.Add(li);
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
da.Dispose();
mds.Dispose();
dTable = null;
}
}
private DataTable GetTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_training_2.employeeslist_sup", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "admin_ee_number", "number", "in", ViewState["Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "retresult_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
public static void CheckBudgetForApproval(string strheader_record_id, string strCurseExpense,
ref string returned_condintion, ref string returned_test)
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_trn_budget.checbudgetbeforeapproval_ee", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "pla_header_record_id_", strheader_record_id);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "course_expense_", strCurseExpense);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "return_condition_", "varchar2", "out", null, 10);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "return_condition_text_", "varchar2", "out", null, 4000);
conn.Open();
try
{
cmd.ExecuteNonQuery();
returned_condintion = cmd.Parameters["return_condition_"].Value.ToString();
if (returned_condintion != "1")
{
returned_test = cmd.Parameters["return_condition_text_"].Value.ToString();
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
private void DoSave()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training.CancelPaidRequest", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
try
{
string strUserID = "";
if (ViewState["User_Name"] != null)
{
strUserID = ViewState["User_Name"].ToString();
}
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", "varchar2", "in", txtReason.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", strUserID);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", null);
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
private bool SetEmployeeNumber()
{
bool eeFound = true;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_training.set_employee_number", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "User_id_", ViewState["User_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Session_id_", Request.Cookies["Session_ID"].Value.ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", "varchar2", "out", null, 20);
try
{
conn.Open();
cmd.ExecuteNonQuery();
if (cmd.Parameters["employee_number_"].Value == null)
{
eeFound = false;
}
else
{
ViewState["Employee_Number"] = cmd.Parameters["employee_number_"].Value.ToString();
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
return(eeFound);
}
private void FillExpenseType()
{
ddlExpenseType.Items.Clear();
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_training.GetExpenseTypes", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Expenses_List_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
foreach (DataRow dr in tbl.Rows)
{
ListItem li = new ListItem(dr["description"].ToString(), dr["record_id"].ToString());
ddlExpenseType.Items.Add(li);
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
tbl.Dispose();
}
}
private DataTable GetGridTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_trn_budget.budget_detail_list", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", Request.Params["ee"]);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", ddlBudgetYear.SelectedItem.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "budget_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private void SaveOne(Oracle.DataAccess.Client.OracleConnection conn, string strEmployeeNumber)
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training.add_Communication_memo", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
Oracle.DataAccess.Client.OracleParameter parm = null;
try
{
int intFileLen = txtMemo.Text.Length;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "rqst_header_record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "from_employee_number_", "number", "in", ViewState["Loged_Employee_Number"]);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "to_employee_number_", "number", "in", strEmployeeNumber);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "subject_", "varchar2", "in", txtSubject.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "memo_body_", "varchar2", "in", txtMemo.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "add_user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "send_email", "number", "in", 1);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "new_record_id_", "number", "out", "");
cmd.ExecuteNonQuery();
ViewState["New_Comm_ID"] = cmd.Parameters["new_record_id_"].Value.ToString();
}
finally
{
if (parm != null)
{
parm = null;
}
cmd.Dispose();
}
}
private void GetInvolvedEmployeesData()
{
txtMemo.Text = "";
lblReplayFrom.Text = Training_Source.TrainingClass.Employee_Name(ViewState["Employee_Number"].ToString());
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training.Get_Possible_CommEEs", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
try
{
int intFileLen = txtMemo.Text.Length;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Communication_record_id_", "number", "in", ViewState["comm_record_id"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_employee_number_", "number", "in", ViewState["Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "sprvsr_employee_number_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "sprvsr_name_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "payor_employee_number_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "payor_name_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "applicant_employee_number_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "applicant_name_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "from_employee_number_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "from_name_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "from_position_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "to_employee_number_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "to_name_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "to_position_", "varchar2", "out", "", 100);
cmd.ExecuteNonQuery();
lblFromPosition.Text = cmd.Parameters["from_position_"].Value.ToString();
lblFromName.Text = cmd.Parameters["from_name_"].Value.ToString();
lblToPosition.Text = cmd.Parameters["to_position_"].Value.ToString();
lblToName.Text = cmd.Parameters["to_name_"].Value.ToString();
chklstEmailTo.Items.Clear();
if (cmd.Parameters["sprvsr_employee_number_"].Value != null)
{
AddItemToSelection("Supervisor", cmd.Parameters["sprvsr_employee_number_"].Value.ToString(), cmd.Parameters["sprvsr_name_"].Value.ToString());
}
if (cmd.Parameters["payor_employee_number_"].Value != null)
{
AddItemToSelection("Administrator", cmd.Parameters["payor_employee_number_"].Value.ToString(), cmd.Parameters["payor_name_"].Value.ToString());
}
if (cmd.Parameters["applicant_employee_number_"].Value != null)
{
AddItemToSelection("Applicant", cmd.Parameters["applicant_employee_number_"].Value.ToString(), cmd.Parameters["applicant_name_"].Value.ToString());
}
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
//带SQL执行类型，无返回值的SQL执行
public string ExecuteNonQuery(string cmdText, CommandType cmdtype)
{
cmdText = SqlTransfer(cmdText);
string retVal = "0";
using (Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connStr))
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdText, conn);
cmd.CommandType = cmdtype;
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
retVal = cmd.ExecuteNonQuery().ToString();
}
catch (Exception exp)
{
LogWriter.Error(exp, cmdText);
throw exp;
retVal = exp.Message;
}
finally
{
conn.Close();
}
}
return(retVal);
}
public static bool isExist(string table, string where)
{
string cmdQuery = "select * from " + table + " where " + where;
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdQuery, conn);
Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
reader.Close();
cmd.Dispose();
return true;
}
reader.Close();
cmd.Dispose();
return false;
}
/// <summary>
/// execute a sql statement
/// </summary>
public void ExecuteNonQuery(string sql)
{
using (XSqlCommand cmd = new XSqlCommand(sql, Conn))
{
int iResult = cmd.ExecuteNonQuery();
}
}
/// <summary>
/// execute a sql statement, return an Int64.
/// </summary>
public long ExecuteScalar(string sql)
{
using (XSqlCommand cmd = new XSqlCommand(sql, Conn))
{
object oResult = cmd.ExecuteScalar();
Assert.IsNotNull("Expecting result, instead got null. (sql=" + sql + ")");
Assert.IsInstanceOfType(typeof(long), oResult, "Expecting 'long' result from query " + sql + ", instead got type " + oResult.GetType());
return (long)oResult;
}
}
public static string selectStr(string cmdQuery, string nullValue)
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdQuery, conn);
Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader();
string value = nullValue;
while (reader.Read())
value = reader.GetString(0);
reader.Close();
cmd.Dispose();
return value;
}
public static System.Collections.Generic.List<string> selectListStr(string cmdQuery)
{
System.Collections.Generic.List<string> list = new System.Collections.Generic.List<string>();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdQuery, conn);
Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
list.Add(reader.GetString(0));
reader.Close();
cmd.Dispose();
return list;
}
public static long selectLong(string cmdQuery, long nullValue)
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdQuery, conn);
Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader();
long value = nullValue;
while (reader.Read())
value = System.Int32.Parse(reader.GetValue(0).ToString());
reader.Close();
cmd.Dispose();
return value;
}
/// <summary>手持登录</summary>
public AscmUserInfo MobileLogin(string userId, string userPwd, string connString, ref string errorMsg)
{
AscmUserInfo ascmUserInfo = null;
errorMsg = string.Empty;
using (Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connString))
{
if (conn.State != System.Data.ConnectionState.Open)
conn.Open();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT userId,userName,password,employeeId,extExpandType,extExpandId FROM ynUser WHERE extExpandId = :extExpandId";
cmd.CommandType = System.Data.CommandType.Text;
Oracle.DataAccess.Client.OracleParameter parm = new Oracle.DataAccess.Client.OracleParameter();
parm.ParameterName = ":extExpandId";
parm.OracleDbType = Oracle.DataAccess.Client.OracleDbType.NVarchar2;
parm.Size = 20;
parm.Value = userId;
parm.Direction = System.Data.ParameterDirection.Input;
cmd.Parameters.Add(parm);
using (Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
cmd.Parameters.Clear();
if (reader.Read())
{
ascmUserInfo = new AscmUserInfo();
ascmUserInfo.userId = reader["userId"].ToString();
ascmUserInfo.userName = reader["userName"].ToString();
ascmUserInfo.password = reader["password"].ToString();
int employeeId = 0;
int.TryParse(reader["employeeId"].ToString(), out employeeId);
ascmUserInfo.employeeId = employeeId;
ascmUserInfo.extExpandType = reader["extExpandType"].ToString();
ascmUserInfo.extExpandId = reader["extExpandId"].ToString();
if (ascmUserInfo.extExpandType == "erp")
{
byte[] result = Encoding.Default.GetBytes(userPwd);
System.Security.Cryptography.MD5 md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
userPwd = BitConverter.ToString(md5.ComputeHash(result)).Replace("-", "");
if (ascmUserInfo.password != userPwd)
{
errorMsg = "密码不正确";
}
else if (!string.IsNullOrEmpty(ascmUserInfo.userName))
{
Oracle.DataAccess.Client.OracleCommand cmd2 = new Oracle.DataAccess.Client.OracleCommand();
cmd2.Connection = conn;
cmd2.CommandText = "SELECT id,name FROM ascm_supplier WHERE docNumber = :docNumber";
cmd2.CommandType = System.Data.CommandType.Text;
cmd2.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter {
ParameterName = ":docNumber",
OracleDbType = Oracle.DataAccess.Client.OracleDbType.NVarchar2,
Size = 20,
Value = ascmUserInfo.userName,
Direction = System.Data.ParameterDirection.Input
});
using (Oracle.DataAccess.Client.OracleDataReader reader2 = cmd2.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
cmd2.Parameters.Clear();
if (reader2.Read())
{
int id = 0;
if (int.TryParse(reader2["id"].ToString(), out id))
{
AscmSupplier ascmSupplier = new AscmSupplier();
ascmSupplier.id = id;
ascmSupplier.name = reader2["name"].ToString();
ascmUserInfo.ascmSupplier = ascmSupplier;
}
}
}
}
}
}
}
}
return ascmUserInfo;
}
public static System.Collections.Generic.List<int> selectListInt(string cmdQuery)
{
System.Collections.Generic.List<int> list = new System.Collections.Generic.List<int>();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdQuery, conn);
Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
list.Add(System.Int32.Parse(reader.GetValue(0).ToString()));
reader.Close();
cmd.Dispose();
return list;
}
