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 void GetNote()
{
string strSQL = "select t.note,t.expense_type from trn_ee_rqst_expns t where t.record_id=" + Request.Params["rid"];
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();
reader.Read();
txtDescribtion.Text = reader.GetValue(0).ToString();
lblExpenseType.Text = reader.GetValue(1).ToString();
}
finally
{
if (reader != null)
{
reader.Dispose();
}
cmd.Dispose();
conn.Dispose();
}
txtDescribtion.Text = SQLStatic.SQL.ExecScaler(strSQL, Training_Source.TrainingClass.ConnectioString).ToString();
txtRemaining.Text = (4000 - txtDescribtion.Text.Length).ToString();
}
public static Oracle.DataAccess.Client.OracleDataReader ExecuteReaderMSS(
Oracle.DataAccess.Client.OracleCommand target)
{
object result;
if (TestSpecificStubsUtil.RunTestSpecificStub(System.Reflection.MethodBase.GetCurrentMethod(), new object[] { target }, out result))
{
return((Oracle.DataAccess.Client.OracleDataReader)result);
}
else
{
return(target.ExecuteReader());
}
}
private static void QueryEmployee(Oracle.DataAccess.Client.OracleConnection conn)
{
string sql = "Select Emp_Id, Emp_No, Emp_Name, Mng_Id from Employee";
// Tạo một đối tượng Command.
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
// Liên hợp Command với Connection.
cmd.Connection = conn;
cmd.CommandText = sql;
using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
// Chỉ số (index) của cột Emp_ID trong câu lệnh SQL.
int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0
long empId = Convert.ToInt64(reader.GetValue(0));
// Chỉ số của cột Emp_No là 1.
string empNo = reader.GetString(1);
int empNameIndex = reader.GetOrdinal("Emp_Name");// 2
string empName = reader.GetString(empNameIndex);
int mngIdIndex = reader.GetOrdinal("Mng_Id");
long?mngId = null;
if (!reader.IsDBNull(mngIdIndex))
{
mngId = Convert.ToInt64(reader.GetValue(mngIdIndex));
}
Console.WriteLine("--------------------");
Console.WriteLine("empIdIndex:" + empIdIndex);
Console.WriteLine("EmpId:" + empId);
Console.WriteLine("EmpNo:" + empNo);
Console.WriteLine("EmpName:" + empName);
Console.WriteLine("MngId:" + mngId);
}
}
}
}
private void SetEmployeeInfo()
{
if (ViewState["Employee_Number"].ToString() != "0")
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_employee_3.employee_profile", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "emplouee_number", "number", "in", ViewState["Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "result_list_", "cursor", "out", null);
Oracle.DataAccess.Client.OracleDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
lblEEName.Text = reader.GetValue(0).ToString();
lblWorkPh.Text = reader.GetValue(1).ToString();
lblHomePh.Text = reader.GetValue(2).ToString();
lblMobilePh.Text = reader.GetValue(3).ToString();
lblHomeEmail.Text = reader.GetValue(4).ToString();
lblWorkEmail.Text = reader.GetValue(5).ToString();
lblEmployer.Text = reader.GetValue(6).ToString();
lblEmployeNo.Text = reader.GetValue(7).ToString();
lblEENumber.Text = reader.GetValue(8).ToString();
lblBenefitClass.Text = reader.GetValue(9).ToString();
lblPaySchedule.Text = reader.GetValue(10).ToString();
lblProcessingYear.Text = reader.GetValue(11).ToString();
}
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null)
{
conn.Dispose();
}
}
}
}
public void InsertCLOB(string SQLStatement, string str)
{
if (SQLStatement.Length > 0)
{
if (connOracle.State.ToString().Equals("Open"))
{
byte[] newvalue = System.Text.Encoding.Unicode.GetBytes(str);
sqlCommandOracle =
new Oracle.DataAccess.Client.OracleCommand(SQLStatement, connOracle);
rstOracle = sqlCommandOracle.ExecuteReader();
rstOracle.Read();
txn = connOracle.BeginTransaction();
clob = rstOracle.GetOracleClob(0);
clob.Write(newvalue, 0, newvalue.Length);
txn.Commit();
}
}
}
// private void SetupRollingPanel()
// {
// upnl.PanelSourceXml = GetRollXML();
// upnl.DataBind();
// }
// private void SetupRightPanel()
// {
// string strXML = GetRightXML();
// if (GetRightXML() != "")
// {
// upRTab.PanelSourceXml = strXML;
// upRTab.DataBind();
// }
// }
private void SetupMessages()
{
return;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_web.get_home_page_bas_msg", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "ps_session_id", Request.Cookies["Session_ID"].Value.ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "ps_page_url", Request.Path);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "pn_detail_id", "number", "in", 1);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "cr", "cursor", "out", null);
Oracle.DataAccess.Client.OracleDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
reader.Read();
lblMessageFromBas.Text = reader.GetValue(0).ToString();
}
catch {}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null)
{
conn.Dispose();
}
}
// lblMessageFromBas.Text = SQLStatic.SQL.ExecScaler(
// "select ('"+Request.Cookies["Session_ID"].Value.ToString()+"','"+Request.Path+"',1) from dual",ConnectionString).ToString();
}
private void LoadDataGrid()
{
DateTime FrmDt = Convert.ToDateTime(TxtFrm.Text);
DateTime ToDt = Convert.ToDateTime(TxtTo.Text);
string sql = "SELECT LEAVE_DATE \"LEAVE DATE\",TO_CHAR(LEAVE_DATE, 'DAY') \"DAY\", DESCRIPTION, LEAVE_TYPE \"LEAVE TYPE\", DECODE(STATUS,'C','CANCELLED','APPROVED') STATUS FROM VEW_HRM_LEAVE_HISTORY WHERE EMP_ID=" + EmpID() + " AND LEAVE_DATE>='" + FrmDt.ToString("dd-MMM-yyyy") + "' AND LEAVE_DATE<='" + ToDt.ToString("dd-MMM-yyyy") + "' ORDER BY LEAVE_DATE";
if (db.Con.State == ConnectionState.Closed)
{
db.Con.Open();
}
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(sql, db.Con);
Oracle.DataAccess.Client.OracleDataReader dr = cmd.ExecuteReader();
if (!dr.HasRows)
{
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('No Leaves in this period');", true);
GridView1.DataSource = null;
GridView1.DataBind();
dr.Close();
db.Con.Close();
return;
}
dr.Close();
db.Con.Close();
DataSet ds = db.GetData(sql);
GridView1.DataSource = ds;
GridView1.DataBind();
if (ds.Tables[0].Rows.Count <= 0)
{
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('No Leaves in this period');", true);
return;
}
}
//有返回值的SQL执行
public System.Data.Common.DbDataReader ExecuteReader(string cmdText)
{
cmdText = SqlTransfer(cmdText);
Oracle.DataAccess.Client.OracleDataReader reader;
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();
}
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(reader);
}
catch (Exception exp)
{
LogWriter.Error(exp, cmdText);
throw exp;
}
}
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;
}
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 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;
}
protected void btnTest_Click(object sender, EventArgs e)
{
log4net.ILog Log = log4net.LogManager.GetLogger(typeof(PublicController));
string connstring = AppEnv.LuckyFoneOracleGpcViettel;
#region Process
using (var conn = new OracleConnection(connstring))
{
conn.Open();
string year = DateTime.Now.Year.ToString();
string month = DateTime.Now.Month.ToString();
string day = (DateTime.Now.Day).ToString();
string hour = DateTime.Now.Hour.ToString();
string minute = (DateTime.Now.AddMinutes(-10).Minute).ToString();
string second = DateTime.Now.Second.ToString();
string oldParameterTime = month + "-" + day + "-" + year + " " + hour + ":" + minute + ":" + second;
if (month.Length == 1)
{
month = "0" + month;
}
if (day.Length == 1)
{
day = "0" + day;
}
string oldParameter = year + month + day;
string tableMoLog = " SMSDT.SMS_RECEIVE_LOG PARTITION (P_" + oldParameter.Substring(0, 4) + "_" +
oldParameter.Substring(4, 2);
int intCurrentDate = ConvertUtility.ToInt32(oldParameter.Substring(6, 2));
if (intCurrentDate > 0 && intCurrentDate < 6)
{
tableMoLog = tableMoLog + "_1)";
}
else if (intCurrentDate > 5 && intCurrentDate < 11)
{
tableMoLog = tableMoLog + "_2)";
}
else if (intCurrentDate > 10 && intCurrentDate < 16)
{
tableMoLog = tableMoLog + "_3)";
}
else if (intCurrentDate > 15 && intCurrentDate < 21)
{
tableMoLog = tableMoLog + "_4)";
}
else if (intCurrentDate > 20 && intCurrentDate < 26)
{
tableMoLog = tableMoLog + "_5)";
}
else if (intCurrentDate > 25 && intCurrentDate < 32)
{
tableMoLog = tableMoLog + "_6)";
}
Log.Debug("SQL : " + oldParameterTime);
Log.Debug(" ");
string sql = "Select USER_ID, " +
"SERVICE_ID, " +
"MOBILE_OPERATOR, " +
"COMMAND_CODE, " +
"INFO, " +
"TIMESTAMP, " +
"RESPONDED, " +
"REQUEST_ID " +
" From " + tableMoLog + " Where To_Char(TIMESTAMP,'YYYYMMDD')='" + oldParameter + "' " +
"and TIMESTAMP > to_date('" + oldParameterTime + "', 'mm-dd-yyyy hh24:mi:ss' ) AND MOBILE_OPERATOR = 'GPC' "
;
Log.Debug("********** LUCKFONE LOG SQL VINA-VIETTEL **********");
Log.Debug("SQL : " + sql);
Log.Debug(" ");
Log.Debug(" ");
using (var comm = new OracleCommand(sql, conn))
{
using (OracleDataReader rdr = comm.ExecuteReader())
{
int count = 0;
//SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
//dbConn.Open();
while (rdr.Read())
{
count = count + 1;
Log.Debug("VALUE : " + rdr["User_ID"] + "|" + rdr["MOBILE_OPERATOR"]);
//var item = new MoEntity997();
//item.CommandCode = rdr["COMMAND_CODE"].ToString();
////item.Info = rdr["INFO"].ToString();
//item.Info = string.Empty;
//item.MobileOperator = rdr["MOBILE_OPERATOR"].ToString();
//item.RequestID = rdr["REQUEST_ID"].ToString();
//item.Responded = ConvertUtility.ToInt32(rdr["RESPONDED"].ToString());
//item.ServiceID = rdr["SERVICE_ID"].ToString();
//item.Timestamp = ConvertUtility.ToDateTime(rdr["TIMESTAMP"].ToString());
//item.UserID = rdr["USER_ID"].ToString();
//PublicController.LuckyfoneMoInsert(item);
}
Log.Debug("Count : " + count);
}
}
}
#endregion
}
/// <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 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;
}