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();
}
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 static IList <MO> ReadDb() //从数据库得到上行短信信息
{
IList <MO> moList = new List <MO>();
string str = "select SMS_MOID,SMS_MOBILE,SMS_CONTENT from SMS_MO where SMS_MODATE = to_date('" + today + "','yyyy-mm-dd')";
using (Oracle.DataAccess.Client.OracleDataReader dr = DataBase.getSysSqlSdr(str))
{
while (dr.Read())
{
MO result = new MO();
result.Id = dr[0].ToString();
result.Mobile = dr[1].ToString();
result.Content = dr[2].ToString();
moList.Add(result);
}
return(moList);
}
}
// 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;
}
}
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
}
public OracleDataReaderWrapper(OracleDataAccess.Client.OracleDataReader reader)
{
this.innerReader = reader;
}
/// <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 CustomerPersonalInfo GetCustomerDetailsByCustomerID(int CustomerID, AspxCommonInfo aspxCommonObj)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSPUC(aspxCommonObj);
parameter.Add(new KeyValuePair <string, object>("customerID", CustomerID));
OracleHandler sqlH = new OracleHandler();
Oracle.DataAccess.Client.OracleDataReader drCustDetail = sqlH.ExecuteAsDataReader("usp_Aspx_GetCustomerDetailsByCustomerID", parameter);
CustomerPersonalInfo customerPersonalInfo = new CustomerPersonalInfo();
int count = 0;
while (drCustDetail.Read())
{
if (count == 0)
{
DateTime tempDateTime;
int tempInt;
//customerPersonalInfo.AccountCreatedIn = DateTime.TryParse(drCustDetail["CreateDate"].ToString(), out tempDateTime) ? tempDateTime : DateTime.MinValue;
customerPersonalInfo.AccountCreatedOn = DateTime.TryParse(drCustDetail["CreateDate"].ToString(), out tempDateTime) ? tempDateTime.ToString("HH:mm dd/MM/yyyy") : "Error retrieving data";
//customerPersonalInfo.ConfirmedEmail = drCustDetail["Email"].ToString();
customerPersonalInfo.CustomerID = Int32.TryParse(drCustDetail["CustomerID"].ToString(), out tempInt) ? tempInt : 0;
customerPersonalInfo.LastLoggedIn = DateTime.TryParse(drCustDetail["LastLoginDate"].ToString(), out tempDateTime) ? tempDateTime.ToString("HH:mm dd/MM/yyyy") : "Never";
customerPersonalInfo.PortalID = Int32.TryParse(drCustDetail["PortalID"].ToString(), out tempInt) ? tempInt : 0;
customerPersonalInfo.UserName = drCustDetail["UserName"].ToString();
string strLifetimeSales = FixDouble(drCustDetail["LifetimeSales"].ToString());
customerPersonalInfo.LifetimeSales = strLifetimeSales;
string strAverageSales = FixDouble(drCustDetail["AverageSales"].ToString());
customerPersonalInfo.AverageSales = strAverageSales;
if (drCustDetail["DefaultBilling"].ToString() == "True" && drCustDetail["DefaultShipping"].ToString() == "True")
{
//Billing address
customerPersonalInfo.BillingAddress = new AddressInfo();
customerPersonalInfo.BillingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.BillingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.BillingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.BillingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.BillingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.BillingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.BillingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.BillingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.BillingAddress.State = drCustDetail["saState"].ToString();
//Shipping address
customerPersonalInfo.ShippingAddress = new AddressInfo();
customerPersonalInfo.ShippingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.ShippingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.ShippingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.ShippingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.ShippingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.ShippingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.ShippingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.ShippingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.ShippingAddress.State = drCustDetail["saState"].ToString();
}
else if (drCustDetail["DefaultBilling"].ToString() == "True")
{
//Billing address
customerPersonalInfo.BillingAddress = new AddressInfo();
customerPersonalInfo.BillingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.BillingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.BillingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.BillingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.BillingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.BillingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.BillingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.BillingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.BillingAddress.State = drCustDetail["saState"].ToString();
}
else if (drCustDetail["DefaultShipping"].ToString() == "True")
{
//Shipping address
customerPersonalInfo.ShippingAddress = new AddressInfo();
customerPersonalInfo.ShippingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.ShippingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.ShippingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.ShippingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.ShippingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.ShippingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.ShippingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.ShippingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.ShippingAddress.State = drCustDetail["saState"].ToString();
}
}
else
{
if (drCustDetail["DefaultBilling"].ToString() == "True")
{
//Billing address
customerPersonalInfo.BillingAddress = new AddressInfo();
customerPersonalInfo.BillingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.BillingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.BillingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.BillingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.BillingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.BillingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.BillingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.BillingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.BillingAddress.State = drCustDetail["saState"].ToString();
}
else if (drCustDetail["DefaultShipping"].ToString() == "True")
{
//Shipping address
customerPersonalInfo.ShippingAddress = new AddressInfo();
customerPersonalInfo.ShippingAddress.Address1 = drCustDetail["saAddress1"].ToString();
customerPersonalInfo.ShippingAddress.Address2 = drCustDetail["saAddress2"].ToString();
customerPersonalInfo.ShippingAddress.City = drCustDetail["saCity"].ToString();
customerPersonalInfo.ShippingAddress.Company = drCustDetail["saCompany"].ToString();
customerPersonalInfo.ShippingAddress.Country = drCustDetail["saCountry"].ToString();
customerPersonalInfo.ShippingAddress.FirstName = drCustDetail["saFirstName"].ToString();
customerPersonalInfo.ShippingAddress.LastName = drCustDetail["saLastName"].ToString();
customerPersonalInfo.ShippingAddress.Zip = drCustDetail["saZip"].ToString();
customerPersonalInfo.ShippingAddress.State = drCustDetail["saState"].ToString();
}
}
count++;
}
return(customerPersonalInfo);
}
catch (Exception e)
{
throw e;
}
}