protected void Page_Load(object sender, EventArgs e)
{
try
{
string connString = ConfigurationManager.AppSettings["strConn"];
connString += "User Id=Ctl;Password=Ctl2";
OracleConnection connection = new OracleConnection(connString);
connection.Open();
OracleCommand command = connection.CreateCommand();
string login_user = Session["User_ID"].ToString();
command.CommandText = "call Ctl.sp_upd_user_logout('" + login_user + "')";
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
CtlLib.WriteLogError(ex.Message);
}
Session.Abandon();
Response.Redirect("../Login.aspx");
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
string sqlText = "";
try
{
string img_pk = Request.QueryString["img_pk"];
string table_name = Request.QueryString["table_name"];
if (img_pk == null || img_pk == "" || img_pk == "0")
{
img_pk = "-1";//show default image
}
if (table_name.IndexOf(".") > 1)
{
dbuser = table_name.Substring(0, table_name.IndexOf("."));
//get database pass by database user name
conString = ConfigurationManager.AppSettings["strConn"];
conString += "User Id=GASP;password=gasp2";
connection = new OracleConnection(conString);
command = new OracleCommand();
command.CommandText = "Call GASP.sp_sel_db_pass2('" + dbuser + "', :p_rtn_value)";
command.Parameters.Add(":p_rtn_value", OracleType.Cursor).Direction = ParameterDirection.Output;
connection.Open();
command.Connection = connection;
OracleDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
dbpass = reader["dbuser_pwd"].ToString();
}
conString = ConfigurationManager.AppSettings["strConn"];
conString += "User Id=" + dbuser + ";password="******"select * from " + table_name + " where pk =" + img_pk;
}
}
else
{
dbuser = Session["APP_DBUSER"].ToString();
dbpass = Session["APP_DBPASS"].ToString();
conString = ConfigurationManager.AppSettings["strConn"];
conString += "User Id=" + dbuser + ";password="******"select * from " + dbuser + "." + table_name + " where pk =" + img_pk;
}
connection = new OracleConnection(conString);
connection.Open();
command = connection.CreateCommand();
command.CommandText = sqlText;
OracleDataReader dr = command.ExecuteReader();
byte[] fileData = null;
string contenttype = "";
string filename = "";
if (dr.Read())
{
filename = dr["filename"].ToString();
contenttype = dr["CONTENTTYPE"].ToString();
fileData = (byte[])dr["DATA"];
}
connection.Close();
Response.ClearContent();
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);
BinaryWriter bw = new BinaryWriter(Response.OutputStream);
bw.Write(fileData);
bw.Close();
Response.ContentType = contenttype;
Response.End();
}
catch (Exception ex)
{
CtlLib.WriteLogError(ex.Message + ". " + sqlText);
throw (ex);
}
}
}
private void SaveToDB()
{
string conString = ConfigurationManager.AppSettings["strConn"];
conString += "User Id=" + _dbuser + ";Password="******"declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;";
command.Parameters.Add("tempblob", OracleType.Blob).Direction = ParameterDirection.Output;
command.Parameters["tempblob"].Size = docLen;
command.ExecuteNonQuery();
tempLob = (OracleLob)command.Parameters[0].Value;
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(buff, 0, buff.Length);
tempLob.EndBatch();
command.Parameters.Clear();
if (_procedure != "" && _procedure != null)
{
command.CommandText = _procedure;
}
else
{
command.CommandText = "ST_HR_PRO_CO_INSERT_IMAGE";
}
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("p_table_name", OracleType.VarChar, 100);
command.Parameters["p_table_name"].Value = _table_name;
command.Parameters.Add("p_master_pk", OracleType.VarChar, 20);
command.Parameters["p_master_pk"].Value = _Master_pk;
command.Parameters.Add("p_tc_fsbinary_pk", OracleType.VarChar, 100);
command.Parameters["p_tc_fsbinary_pk"].Value = _img_pk;
command.Parameters.Add("p_data", OracleType.Blob);
command.Parameters["p_data"].Value = tempLob;
command.Parameters["p_data"].Size = FileInput.PostedFile.ContentLength;
command.Parameters.Add("p_filename", OracleType.VarChar, 100);
command.Parameters["p_filename"].Value = FileInput.PostedFile.FileName;
command.Parameters.Add("p_filesize", OracleType.Number, 20);
command.Parameters["p_filesize"].Value = Math.Round((double)FileInput.PostedFile.ContentLength / 1024, 2);
command.Parameters.Add("p_contenttype", OracleType.VarChar, 100);
command.Parameters["p_contenttype"].Value = FileInput.PostedFile.ContentType;
command.Parameters.Add("p_crt_by", OracleType.VarChar, 10);
command.Parameters["p_crt_by"].Value = _login_user;
command.Parameters.Add("p_rtn_pk", OracleType.VarChar, 200);
command.Parameters["p_rtn_pk"].Direction = ParameterDirection.Output;
//CtlLib.WriteLogError("test");
command.ExecuteNonQuery();
tx.Commit();
this.hiddenImgPK.Value = command.Parameters["p_rtn_pk"].Value.ToString();
}
catch (Exception e)
{
this.hiddenImgPK.Value = e.Message;
CtlLib.WriteLogError("Upload file error: " + e.Message);
Response.Write(e.Message);
}
}
private void SaveToDBFile()
{
string conString = ConfigurationManager.AppSettings["strConn"];
conString += "User Id=" + _dbuser + ";Password="******"{0:yyyyMMdd-HHmmss}", now);
string fn = time + System.IO.Path.GetFileName(FileInput.PostedFile.FileName);
string SaveLocation = @"D:\upload\" + fn;
FileInput.PostedFile.SaveAs(SaveLocation);
if (_procedure != "" && _procedure != null)
{
command.CommandText = _procedure;
}
else
{
command.CommandText = "ST_HR_PRO_CO_INSERT_IMAGE";
}
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("p_table_name", OracleType.VarChar, 100);
command.Parameters["p_table_name"].Value = _table_name;
command.Parameters.Add("p_master_pk", OracleType.VarChar, 20);
command.Parameters["p_master_pk"].Value = _Master_pk;
command.Parameters.Add("p_tc_fsbinary_pk", OracleType.VarChar, 100);
command.Parameters["p_tc_fsbinary_pk"].Value = _img_pk;
command.Parameters.Add("p_data", OracleType.Blob);
command.Parameters["p_data"].Value = null;
command.Parameters["p_data"].Size = FileInput.PostedFile.ContentLength;
command.Parameters.Add("p_filename", OracleType.VarChar, 500);
command.Parameters["p_filename"].Value = SaveLocation;
command.Parameters.Add("p_filesize", OracleType.Number, 20);
command.Parameters["p_filesize"].Value = Math.Round((double)FileInput.PostedFile.ContentLength / 1024, 2);
command.Parameters.Add("p_contenttype", OracleType.VarChar, 100);
command.Parameters["p_contenttype"].Value = FileInput.PostedFile.ContentType;
command.Parameters.Add("p_crt_by", OracleType.VarChar, 10);
command.Parameters["p_crt_by"].Value = _login_user;
command.Parameters.Add("p_rtn_pk", OracleType.VarChar, 200);
command.Parameters["p_rtn_pk"].Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
this.hiddenImgPK.Value = command.Parameters["p_rtn_pk"].Value.ToString();
}
catch (Exception e)
{
this.hiddenImgPK.Value = e.Message;
CtlLib.WriteLogError("Upload file error: " + e.Message);
Response.Write(e.Message);
}
}
private void ImportDataNewHR()
{
DataTable myDT = new DataTable();
string SQL = "";
string _file = "";
string temp = "";
string user_id = "";
string _file_name = "";
try
{
if (!string.IsNullOrEmpty(_img_pk))
{
CtlLib.SetUser(_dbuser);
SQL = "SELECT DATA, FILENAME, FILESIZE, CRT_BY FROM " + _dbuser + ".TC_FSBINARY WHERE PK = " + _img_pk;
myDT = CtlLib.TableReadOpen(SQL);
_file_name = myDT.Rows[0]["FILENAME"].ToString();
_file = myDT.Rows[0]["FILENAME"].ToString();
_file = _file.Substring(_file.LastIndexOf("\\") + 1, _file.Length - _file.LastIndexOf("\\") - 1);
_file = "../temp/" + _file;
_file = Server.MapPath(_file);
if (File.Exists(_file))
{
File.Delete(_file);
}
byte[] MyData = new byte[0];
MyData = (byte[])myDT.Rows[0]["DATA"];
BinaryWriter bw = new BinaryWriter(File.Open(_file, FileMode.OpenOrCreate));
bw.Write(MyData);
bw.Close();
user_id = myDT.Rows[0]["CRT_BY"].ToString();
IWorkbook exBook = NativeExcel.Factory.OpenWorkbook(_file);
IWorksheet exSheet = exBook.Worksheets[1];
exSheet.UsedRange.UnMerge();
int sRow = 0;
if (string.IsNullOrEmpty(_p_start))
{
sRow = 2;
}
else
{
sRow = int.Parse(_p_start);
}
// chỗ này hơi chuối, vì dữ liệu trên file execel đã mer
// nên phải set lại để truyền đủ tham số vào
if (_procedure_file.Substring(_procedure_file.IndexOf(".") + 1, _procedure_file.Length - _procedure_file.IndexOf(".") - 1) == "HR_PRO_10020018_IMP_WT_OT")
{
myDT = exSheet.UsedRange.GetDataTable(false, false);
for (int i = sRow; i <= myDT.Rows.Count; i++)
{
exSheet.Cells["A" + i].Value = i - sRow;
if (exSheet.Cells["B" + i].Value == null)
{
exSheet.Cells["B" + i].Value = exSheet.Cells["B" + (i - 1)].Value;
}
if (exSheet.Cells["C" + i].Value == null)
{
exSheet.Cells["C" + i].Value = exSheet.Cells["C" + (i - 1)].Value;
}
if (exSheet.Cells["D" + i].Value == null)
{
exSheet.Cells["D" + i].Value = exSheet.Cells["D" + (i - 1)].Value;
}
if (exSheet.Cells["E" + i].Value == null)
{
exSheet.Cells["E" + i].Value = exSheet.Cells["E" + (i - 1)].Value;
}
}
}
int sCols = 0;
if (string.IsNullOrEmpty(_p_cols))
{
sCols = myDT.Columns.Count;
}
else
{
sCols = int.Parse(_p_cols);
}
myDT = exSheet.UsedRange.GetDataTable(false, false);
for (int row = sRow; row < myDT.Rows.Count; row++)
{
temp = "";
for (int col = 0; col < sCols; col++)
{
temp += myDT.Rows[row][col].ToString().Replace("'", "''").ToString() + "!";
}
//bien thu 5 dung de luu ten file MR TRUONG ADD
_p_5 = _file_name;
temp += _p_1 + "!" + _p_2 + "!" + _p_3 + "!" + _p_4 + "!" + _p_5 + "!" + _import_seq;
//temp += "!" + user_id;
string[] paraIn = temp.Split('!');
CtlLib.WriteLogError(_procedure_file + "('" + temp + "')");
if (CtlLib.TableReadOpenString(_procedure_file, paraIn) == "1")
{
_count++;
}
else
{
CtlLib.WriteLogError(_procedure_file + "('" + temp + "')");
}
}
this.hiddenRecord.Value = _count.ToString();
}
}
catch (Exception e)
{
CtlLib.WriteLogError("ImportData:" + e.Message + "/" + myDT.Rows.Count.ToString());
Response.Write("ImportData:" + e.Message);
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
if (Session["User_ID"].ToString() == "")
{
Response.Redirect(Application["rooturl"] + "/system/Login.aspx");
}
_login_user = Session["User_ID"].ToString();
// Only accept image types.ok
// FileInput.Accept = "image/*";
if (Page.IsPostBack)
{
if (FileInput.PostedFile == null)
{
return;
}
if (FileInput.PostedFile.FileName == "")
{
this.hiddenImgPK.Value = "No file specified.";
}
else
{
_table_name = Request.Form["hiddenTableName"];
_img_pk = Request.Form["hiddenImgPK"];
_record = Request.Form["hiddenRecord"];
_import_seq = Request.Form["hiddenImportSeq"];
_p_1 = Request.Form["hiddenType1"];
_p_2 = Request.Form["hiddenType2"];
_p_3 = Request.Form["hiddenType3"];
_p_4 = Request.Form["hiddenType4"];
_p_5 = Request.Form["hiddenType5"];
_p_start = Request.Form["hiddenStart"];
_p_cols = Request.Form["hiddenCols"];
_error_continue = Request.Form["hiddenErrorCont"];
_procedure_file = this.hiddenProcedureFile.Value;
_table_name = this.hiddenTableName.Value;
//_Master_pk = Request.Form["hiddenMaster_pk"];
_Master_pk = this.hiddenMaster_pk.Value;
_procedure = this.hiddenProcedure.Value;
_dbuser = Session["APP_DBUSER"].ToString();
_dbpass = Session["APP_DBPASS"].ToString();
SaveToDB();
if (_error_continue == "Y")
{
ImportDataNewHR();
}
else
{
ImportDataNew();//commit rows which has no error.
}
}
}
}
catch (Exception ex)
{
CtlLib.WriteLogError("Page_Load:" + ex.StackTrace);
}
}
private void CheckLogin(string p_user_id, string p_user_pass)
{
string user_info = "";
string[] parameter;
string[] CDelimeter = new string[] { "|!" };
try
{
string login_status = "";
string client_id = ConfigurationManager.AppSettings["dbUser"];
string conString = ConfigurationManager.AppSettings["strConn"];
conString += "User id=ctl;Password=ctl2";
string ip_address = Request.UserHostAddress;
string exeStatement = "Call ctl.ST_SY_LOGIN('" + p_user_id + "','" + p_user_pass + "','" + _notMD5Pass + "','" + ip_address + "','" + client_id + "',:p_rtn_value)";
//string exeStatement = "Call GASP.sp_sel_check_login('" + p_user_id + "','" + p_user_pass + "','" + _notMD5Pass + "','" + ip_address + "',:p_rtn_value)";
OracleConnection connection = new OracleConnection(conString);
connection.Open();
OracleCommand command = new OracleCommand();
command.CommandText = exeStatement;
command.Parameters.Add(":p_rtn_value", OracleType.Cursor).Direction = ParameterDirection.Output;
command.Connection = connection;
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
login_status = reader["xxx"].ToString();
user_info = reader["info"].ToString();
}
reader.Close();
if (login_status == "OK" && p_user_id != "" && p_user_id != null)
{
parameter = user_info.Split(CDelimeter, System.StringSplitOptions.None);
Session["User_ID"] = p_user_id.ToLower();
Session["LOGIN_ID"] = p_user_id.ToLower();
Session["ORG_NAME"] = parameter[2];
Session["DEPT_NAME"] = parameter[2];
Session["ROLE_NAME"] = parameter[3];
Session["CODEADMIN_YN"] = parameter[4];
Session["Debug_YN"] = parameter[4];
Session["Debug"] = (parameter[4].ToString() == "Y")?"TRUE":"FALSE";
Session["DebugLog"] = (parameter[4].ToString() == "Y")?"TRUE":"FALSE";
Session["USER_NAME"] = parameter[0];
Session["EMPLOYEE_PK"] = parameter[1];
Session["COMPANY_PK"] = parameter[5];
Session["HR_LEVEL"] = parameter[7];
Session["SESSION_LANG"] = parameter[8];
Session["EMP_ID"] = parameter[9];
Session["POSITION_NAME"] = parameter[10];
Session["ANNOUNCE_YN"] = parameter[11];
Session["Lang"] = "1";
Session["DEPT_PK"] = parameter[6];
Session["DEPT_CODE"] = parameter[12];
Session["ORG_PK"] = parameter[6];
Session["ORG_ID"] = parameter[12];
Session["CLIENT_PK"] = parameter[14];
Session["CLIENT_ID"] = parameter[15];
Session["USER_PK"] = parameter[13];
//store db pass in the session
command = new OracleCommand();
command.CommandText = "Call ctl.ST_SY_SEL_PASS('" + parameter[13] + "', :p_rtn_value)";
command.Parameters.Add(":p_rtn_value", OracleType.Cursor).Direction = ParameterDirection.Output;
command.Connection = connection;
reader = command.ExecuteReader();
//CtlLib.WriteLogError(parameter[13].ToString());
if (reader.HasRows)
{
while (reader.Read())
{
Session["APP_DBUSER"] = reader["dbuser_name"].ToString().ToUpper();
Session["APP_DBPASS"] = reader["dbuser_pwd"].ToString();
}
}
else
{
connection.Close();
connection.Dispose();
CtlLib.WriteLogError("System cannot get database password information.");
Session.Clear();
Response.Redirect("login.aspx?errmsg=System meet an error. Please ask genuwin supporter for helping.");
}
connection.Close();
connection.Dispose();
Response.Redirect("system/Main.aspx");
}
else
{
connection.Close();
connection.Dispose();
Session.Clear();
Response.Redirect("login.aspx?errmsg=" + login_status);
}
}
catch (Exception ex)
{
if (ex.Message.IndexOf("Thread was being aborted", 0, ex.Message.Length - 1) == -1)
{
CtlLib.WriteLogError(ex.Message + "\n" + ex.StackTrace);
Session.Clear();
Response.Redirect("login.aspx?errmsg=System meet an error. Please ask genuwin supporter for helping.");
}
}
}