public DataTable GetDataProject(string PRO_ID)
{
DataTable dt = new DataTable();
OracleConnection.ClearAllPools();
using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
con.Open();
string query = "SELECT * FROM TB_PROJECT";
if (!string.IsNullOrEmpty(PRO_ID))
{
query += "where 1=1";
if (!string.IsNullOrEmpty(PRO_ID))
{
query += " and PRO_ID :PRO_ID";
}
}
using (OracleCommand com = new OracleCommand(query, con))
{
if (!string.IsNullOrEmpty(PRO_ID))
{
com.Parameters.Add(new OracleParameter("PRO_ID", PRO_ID));
}
using (OracleDataAdapter da = new OracleDataAdapter(com))
{
da.Fill(dt);
}
}
}
return(dt);
}
public int INSERT_PROJECT()
{
int id = 0;
OracleConnection.ClearAllPools();
using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
con.Open();
using (OracleCommand com = new OracleCommand("INSERT INTO TB_PROJECT (CITIZEN_ID,CATEGORY_ID,COUNTRY_ID,SUB_COUNTRY_ID,PROJECT_NAME,ADDRESS_PROJECT,START_DATE,END_DATE,EXPENSES,FUNDING,CERTIFICATE,SUMMARIZE_PROJECT,RESULT_TEACHING,RESULT_ACADEMIC,DIFFICULTY_PROJECT,RESULT_PROJECT,RESULT_RESEARCHING,RESULT_OTHER,COUNSEL,PDF_FILE) VALUES (:CITIZEN_ID,:CATEGORY_ID,:COUNTRY_ID,:SUB_COUNTRY_ID,:PROJECT_NAME,:ADDRESS_PROJECT,:START_DATE,:END_DATE,:EXPENSES,:FUNDING,:CERTIFICATE,:SUMMARIZE_PROJECT,:RESULT_TEACHING,:RESULT_ACADEMIC,:DIFFICULTY_PROJECT,:RESULT_PROJECT,:RESULT_RESEARCHING,:RESULT_OTHER,:COUNSEL,:PDF_FILE)", con))
{
com.Parameters.Add(new OracleParameter("CITIZEN_ID", CITIZEN_ID));
com.Parameters.Add(new OracleParameter("CATEGORY_ID", CATEGORY_ID));
com.Parameters.Add(new OracleParameter("COUNTRY_ID", COUNTRY_ID));
com.Parameters.Add(new OracleParameter("SUB_COUNTRY_ID", SUB_COUNTRY_ID));
com.Parameters.Add(new OracleParameter("PROJECT_NAME", PROJECT_NAME));
com.Parameters.Add(new OracleParameter("ADDRESS_PROJECT", ADDRESS_PROJECT));
com.Parameters.Add(new OracleParameter("START_DATE", START_DATE));
com.Parameters.Add(new OracleParameter("END_DATE", END_DATE));
com.Parameters.Add(new OracleParameter("EXPENSES", EXPENSES));
com.Parameters.Add(new OracleParameter("FUNDING", FUNDING));
com.Parameters.Add(new OracleParameter("CERTIFICATE", CERTIFICATE));
com.Parameters.Add(new OracleParameter("SUMMARIZE_PROJECT", SUMMARIZE_PROJECT));
com.Parameters.Add(new OracleParameter("RESULT_TEACHING", RESULT_TEACHING));
com.Parameters.Add(new OracleParameter("RESULT_ACADEMIC", RESULT_ACADEMIC));
com.Parameters.Add(new OracleParameter("DIFFICULTY_PROJECT", DIFFICULTY_PROJECT));
com.Parameters.Add(new OracleParameter("RESULT_PROJECT", RESULT_PROJECT));
com.Parameters.Add(new OracleParameter("RESULT_RESEARCHING", RESULT_RESEARCHING));
com.Parameters.Add(new OracleParameter("RESULT_OTHER", RESULT_OTHER));
com.Parameters.Add(new OracleParameter("COUNSEL", COUNSEL));
com.Parameters.Add(new OracleParameter("PDF_FILE", PDF_FILE));
id = com.ExecuteNonQuery();
}
}
return(id);
}
public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
try
{
OracleConnection.ClearAllPools();
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
adapter.Fill(ds);
return(ds);
}
catch
{
conn.Close();
throw;
}
finally
{
conn.Close();
}
}
protected void Search_Click(object sender, EventArgs e)
{
string oracleConnectionString = ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString;
OracleConnection con = new OracleConnection(oracleConnectionString);
int ab = con.ConnectionTimeout;
try
{
string query = QueryBuilderClass.BowlerQueryBuilder(DropTeam, EconomyDrop, Wickets_Taken, Name);
OracleCommand cmd = new OracleCommand(query, con);
con.Open();
OracleConnection.ClearAllPools();
OracleDataReader rdr = cmd.ExecuteReader();
BowlerSearchRes.DataSource = rdr;
BowlerSearchRes.DataBind();
}
catch (OracleException ex)
{
Response.Write("<br>/" + "<br>/" + "<br>/" + "<br>/" + "<br>/" + ex);
}
finally
{
con.Close();
}
}
public static List <DateTime> GetLeaveDateTimeFromToDate(string citizenID)
{
OracleConnection.ClearAllPools();
List <DateTime> list = new List <DateTime>();
using (OracleConnection con = new OracleConnection(CONNECTION_STRING)) {
con.Open();
using (OracleCommand com = new OracleCommand("SELECT FROM_DATE, TO_DATE FROM LEV_DATA WHERE PS_ID = '" + citizenID + "'", con)) {
using (OracleDataReader reader = com.ExecuteReader()) {
while (reader.Read())
{
DateTime start = reader.GetDateTime(0);
DateTime to = reader.GetDateTime(1);
while (true)
{
if (!list.Contains(start))
{
list.Add(start);
}
start = start.AddDays(1);
if ((to - start).TotalDays < 0)
{
break;
}
}
}
}
}
}
return(list);
}
protected void btnUpdateInsig_Click(object sender, EventArgs e)
{
string ValueDateInsig = tbInsertDateInsig.Text;
if (Session["DefaultIdInsig"] == null)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('กรุณาเลือกรายการที่จะแก้ไขก่อน')", true);
return;
}
if (ValueDateInsig != "")
{
OracleConnection.ClearAllPools();
using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
con.Open();
using (OracleCommand com = new OracleCommand("UPDATE TB_INSIG_PERSON SET GET_DATE = :GET_DATE, IP_STATUS_ID = :IP_STATUS_ID WHERE IP_ID = :IP_ID", con))
{
com.Parameters.Add(new OracleParameter("GET_DATE", Util.ToDateTimeOracle(tbInsertDateInsig.Text)));
com.Parameters.Add(new OracleParameter("IP_STATUS_ID", ddlStatusID.SelectedValue));
com.Parameters.Add(new OracleParameter("IP_ID", Session["DefaultIdInsig"].ToString()));
com.ExecuteNonQuery();
}
}
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('อัพเดทข้อมูลเรียบร้อย')", true);
BindInsig();
ClearInsig();
Session.Remove("DefaultIdInsig");
}
}
public override async Task DeleteAsync(CancellationToken cancellationToken = default)
{
using (var masterConnection = _connection.CreateMasterConnection())
{
retry:
try
{
OracleConnection.ClearAllPools();
await Dependencies.MigrationCommandExecutor
.ExecuteNonQueryAsync(CreateDropCommands(), masterConnection, cancellationToken);
}
catch (OracleException e)
{
if (e.Number == 1940 ||
e.Number == 31 ||
e.Number == 30 ||
e.Number == 26)
{
// ORA-01940: cannot drop a user that is currently connected
// ORA-00031: session marked for kill
// ORA-00030: User session ID does not exist
// ORA-00026: missing or invalid session ID
goto retry;
}
throw;
}
}
}
public void SetUp()
{
if (this.connectionType == ConnectionType.Oracle)
{
OracleConnection.ClearAllPools();
}
}
protected void lbuAllow_Click(object sender, EventArgs e)
{
if (rbAllow.Checked)
{
UPDATE_PERSON();
DataShow.Visible = false;
Accept.Visible = true;
//ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('บันทึกข้อมูลเรียบร้อย')", true);
}
else if (rbNotAllow.Checked)
{
OracleConnection.ClearAllPools();
using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
con.Open();
using (OracleCommand com = new OracleCommand("UPDATE TB_REQUEST SET DATE_END=:DATE_END, R_STATUS_ID=:R_STATUS_ID, COMMENT_INFO=:COMMENT_INFO, R_ALLOW=:R_ALLOW WHERE R_ID = '" + MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString()) + "'", con))
{
com.Parameters.Add(new OracleParameter("DATE_END", DateTime.Today));
com.Parameters.Add(new OracleParameter("R_STATUS_ID", "4"));
com.Parameters.Add(new OracleParameter("COMMENT_INFO", tbComment.Text));
com.Parameters.Add(new OracleParameter("R_ALLOW", "2"));
com.ExecuteNonQuery();
}
}
DataShow.Visible = false;
NoAccept.Visible = true;
}
}
/// <summary>
/// 针对性处理数据库异常
/// </summary>
/// <param name="oraEx"></param>
/// <param name="conn"></param>
public static void HandleOraException(OracleException oraEx, OracleConnection conn)
{
if (oraEx == null)
{
return;
}
switch (oraEx.Number)
{
case 3113: //ORA-03113: 通信通道的文件结尾
//可能发生在重启数据库
OracleConnection.ClearAllPools();
throw new Exception("发生ORA-03113错误，已清空数据库连接池。", oraEx);
case 28: //ORA-00028: 会话己被终止(session kill) - 发生在会话被Kill之后
case 1012: //ORA-01012: 没有登录(not logon) - 发生在ORA-00028后再访问数据库
case 2396: //ORA-02396: 超出最大空闲时间(exceeded maximum idle time)
case 12535: //ORA-12535: TNS操作超时(TNS:operation timed out)
if (conn != null)
{
OracleConnection.ClearPool(conn);
throw new Exception("发生ORA-" + oraEx.Number.ToString() + "错误，已从数据库连接池清理掉当前连接。", oraEx);
}
else
{
OracleConnection.ClearAllPools();
throw new Exception("发生ORA-" + oraEx.Number.ToString() + "错误，已清空数据库连接池。", oraEx);
}
default:
break;
}
//ORA-01089: 正在执行立即关闭 - 发生在数据库正在关闭
}
private void PrepareCommand(IDbCommand command)
{
try
{
if (Transaction == null && Connection.State == ConnectionState.Closed)
{
if (Connection.ConnectionString != ConnectionString)
{
Connection.ConnectionString = ConnectionString;
}
Connection.Open();
}
}
catch (Exception ex)
{
TraceSource.TraceEvent(TraceEventType.Error, 5110, "{0}", ex);
try { Connection.Close(); }
catch { }
try { OracleConnection.ClearAllPools(); }
catch (Exception cpex)
{
TraceSource.TraceEvent(TraceEventType.Error, 5111, "{0}", cpex);
}
if (Connection.State == ConnectionState.Closed)
{
if (Connection.ConnectionString != ConnectionString)
{
Connection.ConnectionString = ConnectionString;
}
Connection.Open();
}
}
command.Connection = Connection;
command.Transaction = Transaction;
}
private void fBack()
{
PersonnelSystem ps = PersonnelSystem.GetPersonnelSystem(this);
Person loginPerson = ps.LoginPerson;
int count = 0;
OracleConnection.ClearAllPools();
using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) {
con.Open();
using (OracleCommand com = new OracleCommand("SELECT COUNT(LEAVE_ID) FROM LEV_LEAVE WHERE CMD_LOW_ID = '" + loginPerson.PS_CITIZEN_ID + "' AND LEV_LEAVE.STATE_ID = 1", con)) {
using (OracleDataReader reader = com.ExecuteReader()) {
while (reader.Read())
{
count = int.Parse(reader.GetValue(0).ToString());
}
}
}
}
if (count == 0)
{
error_area.InnerHtml = "ไม่มีรายการที่ท่านต้องลงความเห็น";
}
else
{
error_area.InnerHtml = "กรุณาเลือกรายการที่ต้องการลงความเห็น";
}
//error_area.Attributes["class"] = "alert alert_info";
error_area.Attributes["class"] = null;
error_area.InnerHtml = "";
MultiView1.ActiveViewIndex = 0;
}
/// <summary>
/// 数据连接对象
/// </summary>
/// <returns>OracleConnection数据连接对象</returns>
private OracleConnection GetConnection()
{
int num = 0;
for (int i = 0; i <= num; i++)
{
try
{
if (this._conn == null)
{
this._conn = new OracleConnection(this._connectionString);
}
if (this._conn.State == ConnectionState.Closed)
{
this._conn.Open();
}
}
catch (Exception ex)
{
OracleConnection.ClearAllPools();
num = 1;
throw ex;
}
}
return(this._conn);
}
private void FuncGVCH()
{
OracleConnection.ClearAllPools();
SqlDataSource sds = DatabaseManager.CreateSQLDataSource("SELECT LEV_DATA.LEAVE_ID รหัสการลา, (SELECT LEAVE_TYPE_NAME FROM LEV_TYPE WHERE LEV_TYPE.LEAVE_TYPE_ID = LEV_DATA.LEAVE_TYPE_ID) ประเภทการลา, REQ_DATE วันที่ข้อมูล, (SELECT PS_FIRSTNAME || ' ' || PS_LASTNAME FROM PS_PERSON WHERE PS_CITIZEN_ID = LEV_DATA.PS_ID) ชื่อผู้ลา, (SELECT TB_POSITION_WORK.POSITION_WORK_NAME FROM TB_POSITION_WORK, PS_PERSON WHERE TB_POSITION_WORK.POSITION_WORK_ID = PS_PERSON.PS_WORK_POS_ID AND PS_PERSON.PS_CITIZEN_ID = LEV_DATA.PS_ID) ตำแหน่ง, (SELECT ADMIN_POSITION_NAME FROM TB_ADMIN_POSITION, PS_PERSON WHERE ADMIN_POSITION_ID = PS_PERSON.PS_ADMIN_POS_ID AND PS_PERSON.PS_CITIZEN_ID = LEV_DATA.PS_ID) ระดับ, (SELECT LEAVE_STATUS_NAME FROM LEV_STATUS WHERE LEV_STATUS.LEAVE_STATUS_ID = LEV_DATA.LEAVE_STATUS_ID) สถานะ, NVL(LEV_DATA.V_ALLOW,0) ผลการอนุมัติ FROM LEV_DATA, LEV_BOSS_DATA WHERE LEV_DATA.LEAVE_ID = LEV_BOSS_DATA.LEAVE_ID AND LEV_BOSS_DATA.CITIZEN_ID = '" + loginPerson.PS_CITIZEN_ID + "' ORDER BY LEV_DATA.LEAVE_ID DESC");
gvCH.DataSource = sds;
gvCH.DataBind();
if (gvCH.Rows.Count > 0)
{
lbCH.Visible = false;
TableHeaderCell headerCell = new TableHeaderCell();
headerCell.Text = "ดูข้อมูล";
gvCH.HeaderRow.Cells.Add(headerCell);
gvCH.HeaderRow.Cells[0].Text = "<img src='Image/Small/ID.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[0].Text;
gvCH.HeaderRow.Cells[1].Text = "<img src='Image/Small/list.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[1].Text;
gvCH.HeaderRow.Cells[2].Text = "<img src='Image/Small/calendar.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[2].Text;
gvCH.HeaderRow.Cells[3].Text = "<img src='Image/Small/person2.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[3].Text;
gvCH.HeaderRow.Cells[6].Text = "<img src='Image/Small/question.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[6].Text;
gvCH.HeaderRow.Cells[7].Text = "<img src='Image/Small/correct.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[7].Text;
for (int i = 0; i < gvCH.Rows.Count; ++i)
{
string ID = gvCH.Rows[i].Cells[0].Text;
TableCell cell = new TableCell();
LinkButton btn = new LinkButton();
btn.CssClass = "ps-button-img";
btn.Text = "<img src='Image/Small/search.png'></img>";
btn.Click += (e2, e3) => {
Response.Redirect("ViewLeaveForm.aspx?LeaveID=" + ID);
};
cell.Controls.Add(btn);
gvCH.Rows[i].Cells.Add(cell);
if (Util.StringEqual(gvCH.Rows[i].Cells[7].Text, new string[] { "0" }))
{
gvCH.Rows[i].Cells[7].Text = "-";
gvCH.Rows[i].Cells[7].ForeColor = System.Drawing.Color.Black;
}
if (Util.StringEqual(gvCH.Rows[i].Cells[7].Text, new string[] { "2" }))
{
gvCH.Rows[i].Cells[7].Text = "ไม่อนุญาต";
gvCH.Rows[i].Cells[7].ForeColor = System.Drawing.Color.Red;
}
if (Util.StringEqual(gvCH.Rows[i].Cells[7].Text, new string[] { "1" }))
{
gvCH.Rows[i].Cells[7].Text = "อนุญาต";
gvCH.Rows[i].Cells[7].ForeColor = System.Drawing.Color.Green;
}
}
Util.NormalizeGridViewDate(gvCH, 2);
}
else
{
lbCH.Visible = true;
}
}
public static void BindDropDown(DropDownList ddl, string sql, string text, string value)
{
OracleConnection.ClearAllPools();
ddl.DataSource = CreateSQLDataSource(sql);
ddl.DataTextField = text;
ddl.DataValueField = value;
ddl.DataBind();
}
public static void BindGridView(GridView gv, string sql)
{
OracleConnection.ClearAllPools();
SqlDataSource sds = CreateSQLDataSource(sql);
gv.DataSource = sds;
gv.DataBind();
}
public static OracleConnection OC()
{
OracleConnection.ClearAllPools();
OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING);
con.Open();
return(con);
}
public void Load(int ID)
{
HasData = false;
OracleConnection.ClearAllPools();
using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
con.Open();
using (OracleCommand com = new OracleCommand("SELECT TB_PROJECT.*, (SELECT CATEGORY_NAME FROM TB_PROJECT_CATEGORY WHERE TB_PROJECT_CATEGORY.CATEGORY_ID = TB_PROJECT.CATEGORY_ID) CATEGORY_NAME FROM TB_PROJECT WHERE PRO_ID = " + ID, con))
{
using (OracleDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
HasData = true;
int i = 1;
PRO_ID = ID;
CITIZEN_ID = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
COUNTRY_ID = reader.GetInt32(i++);
SUB_COUNTRY_ID = reader.GetInt32(i++);
CATEGORY_ID = reader.GetInt32(i++);
PROJECT_NAME = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
ADDRESS_PROJECT = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
if (reader.IsDBNull(i))
{
START_DATE = null;
}
else
{
START_DATE = reader.GetDateTime(i);
} ++i;
if (reader.IsDBNull(i))
{
END_DATE = null;
}
else
{
END_DATE = reader.GetDateTime(i);
} ++i;
EXPENSES = reader.GetInt32(i++);
FUNDING = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
CERTIFICATE = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
SUMMARIZE_PROJECT = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
RESULT_TEACHING = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
RESULT_ACADEMIC = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
DIFFICULTY_PROJECT = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
RESULT_PROJECT = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
RESULT_RESEARCHING = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
RESULT_OTHER = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
COUNSEL = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
PDF_FILE = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
category_name = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i;
}
}
}
person = DatabaseManager.GetPerson(citizen_id);
}
}
public override bool HandleFatalException(IDbConnection connection, Exception e)
{
#if !MONO
#pragma warning disable 618
OracleConnection.ClearAllPools();
#pragma warning restore 618
#endif
return(false);
}
public static void BindDropDown(DropDownList ddl, string sql, string text, string value, string first)
{
OracleConnection.ClearAllPools();
ddl.DataSource = CreateSQLDataSource(sql);
ddl.DataTextField = text;
ddl.DataValueField = value;
ddl.DataBind();
ddl.Items.Insert(0, new ListItem(first, ""));
}
//public static readonly string CONNECTION_STRING_FIXED = @"Provider=OraOLEDB.Oracle; Data Source = 203.158.140.67:1521/orcl;USER ID=rmutto;PASSWORD=Zxcvbnm";
public static void ExecuteNonQuery(string sql)
{
OracleConnection.ClearAllPools();
using (OracleConnection con = new OracleConnection(CONNECTION_STRING)) {
con.Open();
using (OracleCommand com = new OracleCommand(sql, con)) {
com.ExecuteNonQuery();
}
}
}
private void FuncGVFinish()
{
OracleConnection.ClearAllPools();
SqlDataSource sds = DatabaseManager.CreateSQLDataSource("SELECT R_ID รหัสคำร้องแก้ไขข้อมูล, DATE_START วันที่ข้อมูล, (SELECT R_STATUS_NAME FROM TB_REQUEST_STATUS WHERE TB_REQUEST_STATUS.R_STATUS_ID = TB_REQUEST.R_STATUS_ID) สถานะ, NVL(R_ALLOW,0) ผลการอนุมัติ, DATE_END วันที่อนุมัติ FROM TB_REQUEST WHERE R_STATUS_ID IN(2,4) AND CITIZEN_ID = '" + loginPerson.PS_CITIZEN_ID + "' ORDER BY R_ID DESC");
gvFinish.DataSource = sds;
gvFinish.DataBind();
if (gvFinish.Rows.Count > 0)
{
lbFinish.Visible = false;
TableHeaderCell headerCell = new TableHeaderCell();
headerCell.Text = "ตกลง";
gvFinish.HeaderRow.Cells.Add(headerCell);
for (int i = 0; i < gvFinish.Rows.Count; ++i)
{
string ID = gvFinish.Rows[i].Cells[0].Text;
TableCell cell = new TableCell();
LinkButton btn = new LinkButton();
btn.CssClass = "ps-button-img";
btn.Text = "ตกลง";
btn.Click += (e2, e3) =>
{
DatabaseManager.ExecuteNonQuery("UPDATE TB_REQUEST SET R_STATUS_ID = R_STATUS_ID+1 WHERE R_ID = " + ID);
Response.Redirect("RequestHistory.aspx");
};
cell.Controls.Add(btn);
gvFinish.Rows[i].Cells.Add(cell);
if (Util.StringEqual(gvFinish.Rows[i].Cells[3].Text, new string[] { "0" }))
{
gvFinish.Rows[i].Cells[3].Text = "-";
gvFinish.Rows[i].Cells[3].ForeColor = System.Drawing.Color.Black;
}
if (Util.StringEqual(gvFinish.Rows[i].Cells[3].Text, new string[] { "2" }))
{
gvFinish.Rows[i].Cells[3].Text = "ไม่อนุมัติ";
gvFinish.Rows[i].Cells[3].ForeColor = System.Drawing.Color.Red;
}
if (Util.StringEqual(gvFinish.Rows[i].Cells[3].Text, new string[] { "1" }))
{
gvFinish.Rows[i].Cells[3].Text = "อนุมัติ";
gvFinish.Rows[i].Cells[3].ForeColor = System.Drawing.Color.Green;
}
}
Util.NormalizeGridViewDate(gvFinish, 1);
Util.NormalizeGridViewDate(gvFinish, 4);
}
else
{
lbFinish.Visible = true;
}
}
private void FuncGVFinish()
{
OracleConnection.ClearAllPools();
SqlDataSource sds = DatabaseManager.CreateSQLDataSource("SELECT IP_ID รหัสการขอเครื่องราช, REQ_DATE วันที่ขอ, (SELECT INSIG_GRADE_NAME_L FROM TB_INSIG_GRADE WHERE INSIG_GRADE_ID = INSIG_ID) ระดับชั้นเครื่องราชที่ขอ, (SELECT IP_STATUS_NAME FROM TB_INSIG_PERSON_STATUS WHERE TB_INSIG_PERSON_STATUS.IP_STATUS_ID = TB_INSIG_PERSON.IP_STATUS_ID) สถานะ, NVL(I_ALLOW,0) ผลการอนุมัติ, GET_DATE วันที่อนุมัติ FROM TB_INSIG_PERSON WHERE IP_STATUS_ID IN(2,4) AND CITIZEN_ID = '" + loginPerson.PS_CITIZEN_ID + "' ORDER BY IP_ID DESC");
gvFinish.DataSource = sds;
gvFinish.DataBind();
if (gvFinish.Rows.Count > 0)
{
lbFinish.Visible = false;
TableHeaderCell headerCell = new TableHeaderCell();
headerCell.Text = "ตกลง";
gvFinish.HeaderRow.Cells.Add(headerCell);
for (int i = 0; i < gvFinish.Rows.Count; ++i)
{
string ID = gvFinish.Rows[i].Cells[0].Text;
TableCell cell = new TableCell();
LinkButton btn = new LinkButton();
btn.CssClass = "ps-button-img";
btn.Text = "ตกลง";
btn.Click += (e2, e3) =>
{
DatabaseManager.ExecuteNonQuery("UPDATE TB_INSIG_PERSON SET IP_STATUS_ID = IP_STATUS_ID+1 WHERE IP_ID = " + ID);
Response.Redirect("INS_History.aspx");
};
cell.Controls.Add(btn);
gvFinish.Rows[i].Cells.Add(cell);
if (Util.StringEqual(gvFinish.Rows[i].Cells[4].Text, new string[] { "0" }))
{
gvFinish.Rows[i].Cells[4].Text = "-";
gvFinish.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Black;
}
if (Util.StringEqual(gvFinish.Rows[i].Cells[4].Text, new string[] { "2" }))
{
gvFinish.Rows[i].Cells[4].Text = "ไม่ได้รับ";
gvFinish.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Red;
}
if (Util.StringEqual(gvFinish.Rows[i].Cells[4].Text, new string[] { "1" }))
{
gvFinish.Rows[i].Cells[4].Text = "ได้รับ";
gvFinish.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Green;
}
}
Util.NormalizeGridViewDate(gvFinish, 1);
Util.NormalizeGridViewDate(gvFinish, 5);
}
else
{
lbFinish.Visible = true;
}
}
protected void tbUsername_TextChanged(object sender, EventArgs e)
{
OracleConnection.ClearAllPools();
using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
con.Open();
using (OracleCommand com = new OracleCommand("SELECT ST_LOGIN_ID FROM UOC_STAFF WHERE CITIZEN_ID ='" + tbUsername.Text + "'", con))
{
using (OracleDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
int Login = reader.GetInt32(0);
if (tbUsername.Text.Length == 13)
{
if (Login == 0)
{
LabelTop.Text = "รหัสบัตรประชาชนดังกล่าวเป็นการล็อคอินครั้งแรก โปรดยืนยันตัวตน ด้วยการใส่รหัสผ่านเป็นวันเกิด รูปแบบ(01/01/2500)";
ScriptManager.GetCurrent(this.Page).SetFocus(this.tbPassword);
}
if (Login == 1)
{
LabelTop.Text = "";
ScriptManager.GetCurrent(this.Page).SetFocus(this.tbPassword);
}
}
}
}
}
}
using (OracleCommand com2 = new OracleCommand("SELECT COUNT(*) FROM UOC_STAFF WHERE CITIZEN_ID ='" + tbUsername.Text + "'", con))
{
using (OracleDataReader reader2 = com2.ExecuteReader())
{
while (reader2.Read())
{
if (reader2.GetInt32(0) == 0)
{
LabelBottom.Text = "ไม่พบผู้ใช้งาน!";
ScriptManager.GetCurrent(this.Page).SetFocus(this.tbUsername);
return;
}
else
{
LabelBottom.Text = "";
ScriptManager.GetCurrent(this.Page).SetFocus(this.tbPassword);
}
}
}
}
}
}
protected void txtAbbr_TextChanged(object sender, EventArgs e)
{
OracleConnection oraConn = new OracleConnection(Session["constring"].ToString());
if (oraConn.State != ConnectionState.Open)
{
oraConn.Open();
}
try
{
DataTable dtLoanAbbr = new DataTable();
sSql = " SELECT LSCH_DES,LSCH_ABBREV ";
sSql += " FROM LOANSCHEMESETUP";
sSql += " WHERE LSCH_BRANCH = " + ddlBranch.SelectedValue;
sSql += " AND LSCH_ABBREV = '" + txtAbbr.Text.Trim().ToUpper() + "'";
dtLoanAbbr = objDataFetch.DatatablePassSQL(sSql);
if (dtLoanAbbr.Rows.Count <= 0)
{
ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Invalid Loan Abbr')</script>");
txtAbbr.Text = "";
txtAbbr.Focus();
return;
}
//else
//{
// if (!DBNull.Value.Equals(dtLoanAbbr.Rows[0]["LSCH_ABBREV"]) && (dtLoanAbbr.Rows[0]["LSCH_ABBREV"].ToString() != string.Empty))
// {
// }
//}
if (dtLoanAbbr.Rows[0]["LSCH_DES"].ToString() != "")
{
strLoanDes = dtLoanAbbr.Rows[0]["LSCH_DES"].ToString();
}
Session["LoanName"] = strLoanDes;
txtAcno.Focus();
}
catch (Exception ex)
{
objLog.WriteLog(ex.Message);
ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('" + ex.Message + "')</script>");
}
finally
{
if (oraConn.State == ConnectionState.Open)
{
oraConn.Close();
}
oraConn.Dispose();
OracleConnection.ClearAllPools();
}
}
private void FuncGVHistory()
{
OracleConnection.ClearAllPools();
SqlDataSource sds = DatabaseManager.CreateSQLDataSource("SELECT R_ID รหัสคำร้องแก้ไขข้อมูล, (SELECT PS_FIRSTNAME || ' ' || PS_LASTNAME FROM PS_PERSON WHERE PS_PERSON.PS_CITIZEN_ID = TB_REQUEST.CITIZEN_ID) ชื่อ, (SELECT (SELECT CAMPUS_NAME FROM TB_CAMPUS WHERE PS_PERSON.PS_CAMPUS_ID = TB_CAMPUS.CAMPUS_ID) FROM PS_PERSON WHERE PS_PERSON.PS_CITIZEN_ID = TB_REQUEST.CITIZEN_ID) วิทยาเขต, (SELECT (SELECT STAFFTYPE_NAME FROM TB_STAFFTYPE WHERE PS_PERSON.PS_STAFFTYPE_ID = TB_STAFFTYPE.STAFFTYPE_ID) FROM PS_PERSON WHERE PS_PERSON.PS_CITIZEN_ID = TB_REQUEST.CITIZEN_ID) ประเภทบุคลากร, DATE_START วันที่ข้อมูล, (SELECT R_STATUS_NAME FROM TB_REQUEST_STATUS WHERE TB_REQUEST_STATUS.R_STATUS_ID = TB_REQUEST.R_STATUS_ID) สถานะ, NVL(R_ALLOW,0) ผลการอนุมัติ, DATE_END วันที่อนุมัติ FROM TB_REQUEST WHERE R_STATUS_ID IN(2,3,4,5) ORDER BY R_ID DESC");
gvHistory.DataSource = sds;
gvHistory.DataBind();
if (gvHistory.Rows.Count > 0)
{
lbHistory.Visible = false;
TableHeaderCell headerCell = new TableHeaderCell();
headerCell.Text = "ดูข้อมูล";
gvHistory.HeaderRow.Cells.Add(headerCell);
for (int i = 0; i < gvHistory.Rows.Count; ++i)
{
string ID = gvHistory.Rows[i].Cells[0].Text;
TableCell cell = new TableCell();
LinkButton btn = new LinkButton();
btn.CssClass = "ps-button-img";
btn.Text = "<img src='Image/Small/search.png'></img>";
btn.Click += (e2, e3) => {
Response.Redirect("ViewRequestForm.aspx?id=" + MyCrypto.GetEncryptedQueryString(ID).ToString());
};
cell.Controls.Add(btn);
gvHistory.Rows[i].Cells.Add(cell);
if (Util.StringEqual(gvHistory.Rows[i].Cells[6].Text, new string[] { "0" }))
{
gvHistory.Rows[i].Cells[6].Text = "-";
gvHistory.Rows[i].Cells[6].ForeColor = System.Drawing.Color.Black;
}
if (Util.StringEqual(gvHistory.Rows[i].Cells[6].Text, new string[] { "2" }))
{
gvHistory.Rows[i].Cells[6].Text = "ไม่อนุมัติ";
gvHistory.Rows[i].Cells[6].ForeColor = System.Drawing.Color.Red;
}
if (Util.StringEqual(gvHistory.Rows[i].Cells[6].Text, new string[] { "1" }))
{
gvHistory.Rows[i].Cells[6].Text = "อนุมัติ";
gvHistory.Rows[i].Cells[6].ForeColor = System.Drawing.Color.Green;
}
}
Util.NormalizeGridViewDate(gvHistory, 4);
Util.NormalizeGridViewDate(gvHistory, 7);
}
else
{
lbHistory.Visible = true;
}
}
protected void OnDeletePosition(object sender, EventArgs e)
{
RepeaterItem item = (sender as LinkButton).Parent as RepeaterItem;
string ValuePHID = (item.FindControl("HFPH_ID") as HiddenField).Value;
if (ValuePHID != "")
{
string CheckNull = DatabaseManager.ExecuteString("SELECT COUNT(*) FROM PS_POSITION_HISTORY WHERE CITIZEN_ID = '" + MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString()) + "'");
OracleConnection.ClearAllPools();
using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
con.Open();
if (CheckNull == "1")
{
using (OracleCommand com = new OracleCommand("UPDATE PS_PERSON SET PS_POSITION_ID = :PS_POSITION_ID WHERE PS_CITIZEN_ID = :PS_CITIZEN_ID", con))
{
com.Parameters.Add(new OracleParameter("PS_POSITION_ID", DBNull.Value));
com.Parameters.Add(new OracleParameter("PS_CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString())));
com.ExecuteNonQuery();
}
using (OracleCommand com = new OracleCommand("UPDATE PS_PERSON SET PS_FIRST_POSITION_ID = :PS_FIRST_POSITION_ID WHERE PS_CITIZEN_ID = :PS_CITIZEN_ID", con))
{
com.Parameters.Add(new OracleParameter("PS_FIRST_POSITION_ID", DBNull.Value));
com.Parameters.Add(new OracleParameter("PS_CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString())));
com.ExecuteNonQuery();
}
}
else
{
using (OracleCommand com = new OracleCommand("UPDATE PS_PERSON SET PS_POSITION_ID = (select P_ID from ( select P_ID, rownum as rn from ( select P_ID from PS_POSITION_HISTORY where CITIZEN_ID = :CITIZEN_ID order by GET_DATE desc )) where rn=2) WHERE PS_CITIZEN_ID = :PS_CITIZEN_ID", con))
{
com.Parameters.Add(new OracleParameter("CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString())));
com.Parameters.Add(new OracleParameter("PS_CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString())));
com.ExecuteNonQuery();
}
//using (OracleCommand com = new OracleCommand("UPDATE PS_PERSON SET PS_FIRST_POSITION_ID = (select P_ID from ( select P_ID, rownum as rn from ( select P_ID from PS_POSITION_HISTORY where CITIZEN_ID = :CITIZEN_ID order by GET_DATE desc )) where rn=2) WHERE PS_CITIZEN_ID = :PS_CITIZEN_ID", con))
using (OracleCommand com = new OracleCommand("UPDATE PS_PERSON SET PS_FIRST_POSITION_ID = (SELECT P_ID FROM PS_POSITION_HISTORY WHERE CITIZEN_ID = :CITIZEN_ID AND GET_DATE = (SELECT MIN(GET_DATE) FROM PS_POSITION_HISTORY WHERE CITIZEN_ID = :CITIZEN_ID)) WHERE PS_CITIZEN_ID = :PS_CITIZEN_ID", con))
{
com.Parameters.Add(new OracleParameter("CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString())));
com.Parameters.Add(new OracleParameter("PS_CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString())));
com.ExecuteNonQuery();
}
}
DatabaseManager.ExecuteNonQuery("DELETE PS_POSITION_HISTORY WHERE PH_ID = '" + ValuePHID + "'");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('ลบข้อมูลเรียบร้อย')", true);
BindPosition();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
objLog.WriteLog("HousingLoan.aspx: Entered Page_Load");
if (Session.Contents.Count == 0)
{
Response.Redirect("ShowMessage.aspx?msg=" + GlobalConstants.msg);
}
else
{
try
{
if (!IsPostBack)
{
OracleConnection oraConn = new OracleConnection(Session["constring"].ToString());
if (oraConn.State != ConnectionState.Open)
{
oraConn.Open();
}
sSQL = objFill.BranchFill();
objDataFetch.FillDdl(sSQL, ddlBranch, "BRAN_SNO", "BRAN_NAME");
ddlBranch.SelectedValue = Session["BranchLogin"].ToString();
ddlBranch.Enabled = false;
txtAbbr.Focus();
Bal.Gen objfilldate = new Bal.Gen();
objfilldate.Call_Date(ddfrmday, ddfrmMonth, ddfrmYear);
Bal.Gen objfilldate1 = new Bal.Gen();
objfilldate1.Call_Date(ddtoday, ddtoMonth, ddtoYear);
}
strdtfrm = ddfrmday.SelectedItem.Value + "-" + ddfrmMonth.SelectedItem.Text + "-" + ddfrmYear.SelectedItem.Value;
strdtTo = ddtoday.SelectedItem.Value + "-" + ddtoMonth.SelectedItem.Text + "-" + ddtoYear.SelectedItem.Value;
objLog.WriteLog("HousingLoan.aspx: strdtfrm=" + strdtfrm);
objLog.WriteLog("HousingLoan.aspx: strdtTo=" + strdtTo);
}
catch (Exception ex)
{
objLog.WriteLog("HousingLoan Exception in PageLoad : " + ex.Message);
ClientScript.RegisterStartupScript(Page.GetType(), "VALIDATION", "<script language='javascript'>alert('" + ex.Message + "')</script>");
}
finally
{
if (oraConn.State == ConnectionState.Open)
{
oraConn.Close();
}
oraConn.Dispose();
OracleConnection.ClearAllPools();
}
}
}
private void FuncGVHistory()
{
OracleConnection.ClearAllPools();
SqlDataSource sds = DatabaseManager.CreateSQLDataSource("SELECT IP_ID รหัสการขอเครื่องราช, REQ_DATE วันที่ขอ, (SELECT INSIG_GRADE_NAME_L FROM TB_INSIG_GRADE WHERE INSIG_GRADE_ID = INSIG_ID) ระดับชั้นเครื่องราชที่ขอ, (SELECT IP_STATUS_NAME FROM TB_INSIG_PERSON_STATUS WHERE TB_INSIG_PERSON_STATUS.IP_STATUS_ID = TB_INSIG_PERSON.IP_STATUS_ID) สถานะ, NVL(I_ALLOW,0) ผลการอนุมัติ, GET_DATE วันที่อนุมัติ FROM TB_INSIG_PERSON WHERE IP_STATUS_ID IN(3,5) AND CITIZEN_ID = '" + loginPerson.PS_CITIZEN_ID + "' ORDER BY IP_ID DESC");
gvHistory.DataSource = sds;
gvHistory.DataBind();
if (gvHistory.Rows.Count > 0)
{
lbHistory.Visible = false;
//TableHeaderCell headerCell = new TableHeaderCell();
//headerCell.Text = "ดูข้อมูล";
//gvHistory.HeaderRow.Cells.Add(headerCell);
for (int i = 0; i < gvHistory.Rows.Count; ++i)
{
/*string ID = gvHistory.Rows[i].Cells[0].Text;
* TableCell cell = new TableCell();
* LinkButton btn = new LinkButton();
* btn.CssClass = "ps-button-img";
* btn.Text = "<img src='Image/Small/search.png'></img>";
* btn.Click += (e2, e3) =>
* {
* Response.Redirect("ViewRequestForm.aspx?RequestID=" + ID);
* };
* cell.Controls.Add(btn);
* gvHistory.Rows[i].Cells.Add(cell);*/
if (Util.StringEqual(gvHistory.Rows[i].Cells[4].Text, new string[] { "0" }))
{
gvHistory.Rows[i].Cells[4].Text = "-";
gvHistory.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Black;
}
if (Util.StringEqual(gvHistory.Rows[i].Cells[4].Text, new string[] { "2" }))
{
gvHistory.Rows[i].Cells[4].Text = "ไม่ได้รับ";
gvHistory.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Red;
}
if (Util.StringEqual(gvHistory.Rows[i].Cells[4].Text, new string[] { "1" }))
{
gvHistory.Rows[i].Cells[4].Text = "ได้รับ";
gvHistory.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Green;
}
}
Util.NormalizeGridViewDate(gvHistory, 1);
Util.NormalizeGridViewDate(gvHistory, 5);
}
}
public int INSERT_REQUEST()
{
int id = 0;
OracleConnection.ClearAllPools();
using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
con.Open();
using (OracleCommand com = new OracleCommand("INSERT INTO TB_REQUEST (UOC_ID,STATUS_ID,DATE_START,UNIV_ID,PREFIX_NAME,STF_FNAME,STF_LNAME) VALUES (:UOC_ID,:STATUS_ID,:DATE_START,:UNIV_ID,:PREFIX_NAME,:STF_FNAME,:STF_LNAME)", con))
{
com.Parameters.Add(new OracleParameter("UOC_ID", loginPerson.UOC_ID));
com.Parameters.Add(new OracleParameter("STATUS_ID", "0"));
com.Parameters.Add(new OracleParameter("DATE_START", DateTime.Today));
if (cbUniv.Checked)
{
com.Parameters.Add(new OracleParameter("UNIV_ID", ddlUniv.SelectedValue));
}
else
{
com.Parameters.Add(new OracleParameter("UNIV_ID", DBNull.Value));
}
if (cbPrefixName.Checked)
{
com.Parameters.Add(new OracleParameter("PREFIX_NAME", ddlPrefixName.SelectedValue));
}
else
{
com.Parameters.Add(new OracleParameter("PREFIX_NAME", DBNull.Value));
}
if (cbName.Checked)
{
com.Parameters.Add(new OracleParameter("STF_FNAME", tbName.Text));
}
else
{
com.Parameters.Add(new OracleParameter("STF_FNAME", DBNull.Value));
}
if (cbLastName.Checked)
{
com.Parameters.Add(new OracleParameter("STF_LNAME", tbLastName.Text));
}
else
{
com.Parameters.Add(new OracleParameter("STF_LNAME", DBNull.Value));
}
id = com.ExecuteNonQuery();
}
}
return(id);
}
