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 } } }