public ReturnClass.ReturnDataTable Get_Action_Detail(bl_rti_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string where = "";
if (bl.Roll_id == "2")
{
where += " and rd.securitycode=@securitycode ";
}
string st = @"select ra.action_id, ra.rti_id, ra.officer_mapping_id, ra.action_detail,
IF(ra.IsMeeting = 'Y', 'YES', 'NO') as IsMeeting, DATE_FORMAT(ra.meeting_date,'%d-%m-%Y') as meeting_date,
IF(ra.IsUpload = 'Y', 'YES', 'NO') as IsUpload, dl.DisplayName_en as rti_status, DATE_FORMAT(rd.rti_DateTime,'%d-%m-%Y: %H:%i:%s') as rti_date,
emp_map.emp_code, emp.Name_en as Emp_Name, emp.NewOfficeCode, ofc.OfficeName, DATE_FORMAT(ra.action_date,'%d-%m-%Y') as action_date ,
emp_map.base_department_id, bd.dept_name, emp_map.district_id_ofc, dis.District_Name_En, af.action_fileID,
(case when ra.IsUpload ='Y' then af.action_fileID else 'No File' end) as file_ID
from rti_action as ra
inner join emp_office_mapping as emp_map on ra.officer_mapping_id = emp_map.office_mapping_id
inner join employee_table as emp on emp_map.emp_code = emp.emp_id
inner join office as ofc on ofc.NewOfficeCode = emp.NewOfficeCode
inner join basedepartment as bd on emp_map.base_department_id = bd.dept_id
inner join districts as dis on dis.District_ID = emp_map.district_id_ofc
inner join ddl_list as dl on dl.DDL_Name_Value = ra.`dept_status` and dl.Category='Permission'
inner join rti_detail as rd on ra.rti_id = rd.rti_id
left join action_files as af on af.action_id = ra.action_id and af.rti_id = ra.rti_id
where ra.rti_id=@rti_id " + where + " order by entry_date_time ASC";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("rti_id", bl.Rti_id),
new MySqlParameter("securitycode", bl.Security_code)
};
rt = db.executeSelectQuery(st, pm);
return(rt);
} // End
protected void Page_Load(object sender, EventArgs e)
{
if (Session["username"] == null)
{
Response.Redirect("../logout.aspx");
}
else
{
bl_rti_emp bl = new bl_rti_emp();
dl_rti_emp dl = new dl_rti_emp();
ReturnClass.ReturnDataTable rt2 = new ReturnClass.ReturnDataTable();
bl.User_id = Session["username"].ToString();
bl.Office_map_id = Session["EmpMapID"].ToString();
rt2 = dl.Get_EmpDesName(bl);
if (rt2.table.Rows.Count > 0)
{
lbl_UserName.Text = rt2.table.Rows[0]["Name_en"].ToString().ToUpper();
}
if (!Page.IsPostBack)
{
Session["CheckRefresh"] = Server.UrlDecode(System.DateTime.Now.ToString());
bind_grd_Action();
string key = System.Configuration.ConfigurationManager.AppSettings["EncKey"].ToString();
Utilities ut = new Utilities();
string sas = Server.UrlDecode(Request.QueryString["rtiid"].ToString());
sas = sas.Replace(" ", "+");
string decrypt_query_string = ut.Decrypt_AES(sas, key);
bl1.Rti_id = decrypt_query_string;
sas = Server.UrlDecode(Request.QueryString["empid"].ToString());
sas = sas.Replace(" ", "+");
decrypt_query_string = ut.Decrypt_AES(sas, key);
bl1.Office_mapping_id = Session["EmpMapID"].ToString();
//bl1.Rti_id = Request.QueryString["rtiid"];
//bl1.Office_mapping_id = Request.QueryString["emp_ofc_map_id"];
rd = dl1.applicantDetail(bl1);
if (rd.table.Rows.Count > 0)
{
txt_applicationNo.Text = rd.table.Rows[0]["rti_id"].ToString();
txt_mobileNo.Text = rd.table.Rows[0]["Mobile_No"].ToString();
txt_applicantName.Text = rd.table.Rows[0]["Applicant_Name_en"].ToString();
txt_applicantAddress.Text = rd.table.Rows[0]["Address"].ToString();
txt_subject.Text = rd.table.Rows[0]["rti_Subject"].ToString();
txt_date.Text = rd.table.Rows[0]["rti_DateTime"].ToString();
txt_application_status.Text = rd.table.Rows[0]["DisplayName_en"].ToString();
//txt_dept.Text = rd.table.Rows[0]["dept_name"].ToString();
//txt_ofc.Text = rd.table.Rows[0]["OfficeName"].ToString();
string t = rd.table.Rows[0]["degid"].ToString();
}
}
}
}
} // End
public ReturnClass.ReturnDataTable GetRoll_ID(bl_rti_emp bl)
{
ReturnClass.ReturnDataTable dt = null;// = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string str = @"select lo.RollID from login lo
where lo.UserID=@user";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("user", bl.User_id)
};
dt = db.executeSelectQuery(str, pm);
return(dt);
}
} // End
public ReturnClass.ReturnBool Set_IsNew(bl_rti_emp bl)
{
ReturnClass.ReturnBool rt = new ReturnClass.ReturnBool();
db_maria_connection db = new db_maria_connection();
string st = @" update rti_status set IsNew='N' where rti_id=@Rti_id ";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("Rti_id", bl.Rti_id)
};
rt = db.executeUpdateQuery(st, pm);
//rt = db.executeSelectQuery(st, pm);
return(rt);
} // End of Get_EmpDesName
} // End of Get_EmpDesName
public ReturnClass.ReturnDataTable Get_Office(bl_rti_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string st = @" select lo.LoginID, emp_map.office_mapping_id, emp_map.designation_id, des.Designation_Name, emp_map.office_id,
ofc.OfficeName from login as lo
inner join emp_office_mapping as emp_map on lo.LoginID = emp_map.emp_code
inner join designation as des on emp_map.designation_id = des.Designation_ID
inner join office as ofc on ofc.NewOfficeCode = emp_map.office_id
where lo.UserID=@UserId ";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("UserId", bl.User_id)
};
rt = db.executeSelectQuery(st, pm);
return(rt);
} // End of Get_EmpDesName
protected void Page_PreInit(object sender, EventArgs e)
{
bl_rti_emp bl = new bl_rti_emp();
dl_rti_emp dl = new dl_rti_emp();
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
if (Session["username"] != null)
{
bl.User_id = Session["username"].ToString();
string rollid = null;
rt = dl.GetRoll_ID(bl);
if (rt.table.Rows.Count > 0)
{
if (Session["role"] == null)
{
rollid = rt.table.Rows[0]["RollID"].ToString();
}
else
{
rollid = Session["role"].ToString(); // This has been added on 26/sep/2017
}
if (rollid == "2") // User ROll ID
{
this.MasterPageFile = "~/UserMaster.master";
}
else if (rollid == "3")
{
this.MasterPageFile = "~/Master_employee.master";
}
//else if (rollid == "1")
//{
// this.MasterPageFile = "~/admin_master.master";
//}
//else if (rollid == "4" || rollid == "5")
//{
// this.MasterPageFile = "~/master_dio.master";
//}
}
}
}
public ReturnClass.ReturnDataTable GetRequestCount(bl_rti_emp bl)
{
ReturnClass.ReturnDataTable dt = null;//= new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string qr = "";
try
{
string query = @" select distinct(select count(status) from rti_status where officer_maping_id=@ofc_map_id ) as total,
(select count(status) from rti_status where status=@status and officer_maping_id=@ofc_map_id )as complete ,
(select count(status) from rti_status where status<>@status and officer_maping_id=@ofc_map_id )as pending
from rti_status rd";
if (bl.RequestStatus == "REG")
{
qr = " select count(status) as RequestCount from rti_status where officer_maping_id=@ofc_map_id AND IsValid='Y' ";
}
else if (bl.RequestStatus == "PEN")
{
qr = " select count(status) as RequestCount from rti_status where officer_maping_id=@ofc_map_id AND IsValid='Y' " +
" and (status='PEN' OR status='REG') ";
}
else
{
qr = " select count(status) as RequestCount from rti_status where officer_maping_id=@ofc_map_id AND IsValid='Y' " +
" and status='CLT' ";
}
MySqlParameter[] pr = new MySqlParameter[] {
new MySqlParameter("ofc_map_id", bl.Office_map_id),
new MySqlParameter("status", bl.RequestStatus)
};
dt = db.executeSelectQuery(query, pr);
dt.status = true;
}
catch (Exception Ex)
{
dt.status = false;
dt.message = Ex.Message;
}
return(dt);
} // End of GetRequestCount
} // End of Get_EmpDesName
public ReturnClass.ReturnDataTable Get_RTI_Data(bl_rti_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string st = @" select rd.rti_id, rd.Applicant_Name_en, rd.rti_Subject,
IF(rd.Is_BPL = 'Y', 'YES', 'NO') as Is_BPL, DATE_FORMAT(rd.rti_DateTime,'%m-%d-%Y :%H:%i:%s') as rti_DateTime,
rd.Mobile_No, ddl.DisplayName_en as Gender, st.action_id, ddl2.DisplayName_en as rti_status, st.IsNew, ddl3.DisplayName_en as DeptStatus from rti_detail rd
inner join rti_status st on rd.rti_id=st.rti_id
inner join ddl_list ddl on rd.Gender = ddl.DDL_Name_Value and ddl.Category='Gender'
inner join ddl_list ddl2 on st.status = ddl2.DDL_Name_Value and ddl2.Category='RTIStatus'
left join ddl_list ddl3 on st.DeptStatus = ddl3.DDL_Name_Value and ddl3.Category='Permission'
where st.officer_maping_id = @office_map_id order by rd.rti_id DESC";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("office_map_id", bl.Office_map_id)
};
rt = db.executeSelectQuery(st, pm);
return(rt);
} // End
} // End of GetRequestCount
public ReturnClass.ReturnDataTable Get_EmpDesName(bl_rti_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string st = @"select et.Name_en, emp.office_mapping_id, dg.Designation_Name, of.OfficeName, bd.dept_name from emp_office_mapping emp
inner join employee_table et on et.emp_id=emp.emp_code
inner join designation dg on dg.Designation_ID= emp.designation_id
inner join office of on of.NewOfficeCode=emp.office_id
inner JOIN basedepartment bd on bd.dept_id=emp.base_department_id
where emp.office_mapping_id=@empmapid";
//@" select lo.LoginID, emp.Name_en from login as lo
// inner join employee_table as emp on lo.LoginID = emp.emp_id
// where lo.UserID=@UserId ";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("UserId", bl.User_id),
new MySqlParameter("empmapid", bl.Office_map_id)
};
rt = db.executeSelectQuery(st, pm);
return(rt);
} // End of Get_EmpDesName
protected void Page_Load(object sender, EventArgs e)
{
comval1.ValueToCompare = DateTime.Now.ToString("dd/MM/yyyy");
comp_date.ValueToCompare = DateTime.Now.ToString("dd/MM/yyyy");
calex_from.EndDate = DateTime.Now;
if (Session["username"] == null)
{
Response.Redirect("../Login.aspx");
}
else
{
bl_rti_emp bl = new bl_rti_emp();
dl_rti_emp dl = new dl_rti_emp();
ReturnClass.ReturnDataTable rt2 = new ReturnClass.ReturnDataTable();
bl.User_id = Session["username"].ToString();
bl.Office_map_id = Session["EmpMapID"].ToString();
rt2 = dl.Get_EmpDesName(bl);
if (rt2.table.Rows.Count > 0)
{
lbl_UserName.Text = rt2.table.Rows[0]["Name_en"].ToString().ToUpper();
}
if (!Page.IsPostBack)
{
Session["CheckRefresh"] = Server.UrlDecode(System.DateTime.Now.ToString());
bind_grd_Action();
CalendarExtender2.StartDate = DateTime.Now;
string key = System.Configuration.ConfigurationManager.AppSettings["EncKey"].ToString();
Utilities ut = new Utilities();
string sas = Server.UrlDecode(Request.QueryString["rtiid"].ToString());
sas = sas.Replace(" ", "+");
string decrypt_query_string = ut.Decrypt_AES(sas, key);
bl1.Rti_id = decrypt_query_string;
sas = Server.UrlDecode(Request.QueryString["empid"].ToString());
sas = sas.Replace(" ", "+");
decrypt_query_string = ut.Decrypt_AES(sas, key);
bl1.Office_mapping_id = Session["EmpMapID"].ToString();
//bl1.Rti_id = Request.QueryString["rtiid"];
//bl1.Office_mapping_id = Request.QueryString["emp_ofc_map_id"];
rd = dl1.applicantDetail(bl1);
if (rd.table.Rows.Count > 0)
{
txt_applicationNo.Text = rd.table.Rows[0]["rti_id"].ToString();
txt_mobileNo.Text = rd.table.Rows[0]["Mobile_No"].ToString();
txt_applicantName.Text = rd.table.Rows[0]["Applicant_Name_en"].ToString();
txt_applicantAddress.Text = rd.table.Rows[0]["Address"].ToString();
txt_subject.Text = rd.table.Rows[0]["rti_Subject"].ToString();
txt_date.Text = rd.table.Rows[0]["rti_DateTime"].ToString();
txt_application_status.Text = rd.table.Rows[0]["DisplayName_en"].ToString();
//txt_dept.Text = rd.table.Rows[0]["dept_name"].ToString();
//txt_ofc.Text = rd.table.Rows[0]["OfficeName"].ToString();
string t = rd.table.Rows[0]["degid"].ToString();
if (t == "1" || t == "2")
{
div_meeting.Visible = true;
}
else
{
div_meeting.Visible = false;
}
}
bind_Status();
div_forward.Visible = false;
bind_district_code();
bind_department_id();
ddl_status.SelectedValue = "0";
// If there is an rti Data then show it
rd = dl1.GetRTIDetails(bl1);
if (rd.table.Rows.Count > 0)
{
h_IsRtiData.Value = "Y";
//ddl_status.SelectedValue = "DIS";
div_dispose.Visible = true;
rfv_txt_amount.Enabled = true;
txt_result.Text = rd.table.Rows[0]["result_description"].ToString();
string filename = rd.table.Rows[0]["FileName"].ToString();
if (filename != null && filename != "")
{
ddl_rti_file.SelectedValue = "Y";
div_file_rti.Visible = true;
lnk_file_rti.Visible = true;
txt_desc_rti.Text = rd.table.Rows[0]["FileDescription"].ToString();
}
else
{
lnk_file_rti.Visible = false;
}
string ismeeting = rd.table.Rows[0]["IsMeetingFix"].ToString();
if (ismeeting == "Y")
{
div_meeting.Visible = true;
RadioMeeting.SelectedValue = "Y";
div_meet_date.Visible = true;
txt_meeting.Text = DateTime.Now.ToString("dd/MM/yyyy");
}
else
{
div_meeting.Visible = true;
RadioMeeting.SelectedValue = "N";
}
string isadditionalfees = rd.table.Rows[0]["IsAdditionalFees"].ToString();
if (isadditionalfees == "Y")
{
div_additional_fees.Visible = true;
rbl_additional_fees_rti.SelectedValue = "Y";
txt_amount.Text = rd.table.Rows[0]["FeesAmount"].ToString();
}
else
{
rbl_additional_fees_rti.SelectedValue = "N";
}
} // End of if count
}
}
}
