public ReturnClass.ReturnBool Insert_empmap(bl_emp ur)
{
db_maria_connection db = new db_maria_connection();
ReturnClass.ReturnDataTable dt = new ReturnClass.ReturnDataTable();
ReturnClass.ReturnBool rb = new ReturnClass.ReturnBool();
string strQuery = @"INSERT INTO emp_office_mapping
(office_mapping_id,emp_code,office_id,designation_id,base_department_id,office_level_id,district_id_ofc,office_category,user_id,charge_type,Active,client_ip)
VALUES(@office_mapping_id,@emp_code,@office_id,@designation_id,@base_department_id,@office_level_id,@district_id_ofc,@office_category,@user_id,@charge_type,@Active,@client_ip)";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("office_mapping_id", ur.Office_mapping_id),
new MySqlParameter("emp_code", ur.Emp_code),
new MySqlParameter("office_id", ur.Office_id),
new MySqlParameter("designation_id", ur.Designation_id),
new MySqlParameter("base_department_id", ur.Base_department_id),
new MySqlParameter("office_level_id", ur.Office_level_id),
new MySqlParameter("district_id_ofc", ur.District_id_ofc),
new MySqlParameter("office_category", ur.Office_category),
new MySqlParameter("user_id", ur.User_id),
new MySqlParameter("charge_type", ur.Charge_type),
new MySqlParameter("Active", ur.Active),
new MySqlParameter("client_ip", ur.Client_ip)
};
rb = db.executeInsertQuery(strQuery, pm);
return(rb);
}
public ReturnClass.ReturnBool update(bl_emp bl)
{
ReturnClass.ReturnBool rb = new ReturnClass.ReturnBool();
db_maria_connection db = new db_maria_connection();
string str = @"update employee_table set Name_en= @Name_en,Active=@Active,
state_id=@state_id,base_department_id=@base_department_id,district_id=@district_id,
NewOfficeCode=@NewOfficeCode,mobile_no=@mobile_no,email_id=@email_id,user_id=@user_id
where emp_id=@emp_id ";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("Name_en", bl.Name_en),
new MySqlParameter("Active", bl.Active),
new MySqlParameter("state_id", bl.State_id),
new MySqlParameter("base_department_id", bl.Department),
new MySqlParameter("district_id", bl.District_id),
new MySqlParameter("NewOfficeCode", bl.NewOfficeCode),
new MySqlParameter("mobile_no", bl.Mobile_no),
new MySqlParameter("email_id", bl.Email_id),
new MySqlParameter("emp_id", bl.Emp_id),
new MySqlParameter("user_id", bl.User_id)
};
rb = db.executeUpdateQuery(str, pm);
return(rb);
}
public ReturnClass.ReturnDataTable designation(bl_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string st = " select Designation_ID, Designation_Name from designation ";
rt = db.executeSelectQuery(st);
return(rt);
}
public ReturnClass.ReturnDataTable employee_permission(bl_emp bl)
{
ReturnClass.ReturnDataTable rd = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string str = @"select eom.office_mapping_id as emp_map_id,concat( et.Name_en, ' / ',ofc.OfficeName ) as name from permission ps
inner join emp_office_mapping eom on eom.office_mapping_id = ps.emp_map_id
inner join employee_table et on et.emp_id = eom.emp_code
inner join basedepartment bd on bd.dept_id = eom.base_department_id
inner join office ofc on ofc.NewOfficeCode = eom.office_id and ofc.BaseDeptCode = eom.base_department_id
where eom.active = 'Y' ";
string where = " ";
try
{
List <MySqlParameter> pm = new List <MySqlParameter>();
if (bl.State != "0" && bl.State != null && bl.State != "")
{
MySqlParameter ca = new MySqlParameter("State", bl.State);
pm.Add(ca);
where += " and eom.state_id=@State ";
}
if (bl.District != "0" && bl.District != null && bl.District != "" && bl.District != "Select")
{
MySqlParameter ba = new MySqlParameter("District", bl.District);
pm.Add(ba);
where += " and eom.district_id_ofc =@District ";
}
if (bl.Base_department != "" && bl.Base_department != null && bl.Base_department != "0" && bl.Base_department != "Select")
{
MySqlParameter ca = new MySqlParameter("Department", bl.Base_department);
pm.Add(ca);
where += " and eom.base_department_id=@Department ";
}
if (bl.Office != "" && bl.Office != null && bl.Office != "0" && bl.Office != "Select")
{
MySqlParameter ca = new MySqlParameter("Office", bl.Office);
pm.Add(ca);
where += " and emp.NewOfficeCode=@Office ";
}
if (bl.Designation_ID != "" && bl.Designation_ID != null && bl.Designation_ID != "0" && bl.Designation_ID != "Select")
{
MySqlParameter ca = new MySqlParameter("Designation_ID", bl.Designation_ID);
pm.Add(ca);
where += " and emp.Designation_ID=@Designation_ID ";
}
str = str + where + " ORDER by name ASC";
rd = db.executeSelectQuery(str, pm.ToArray());
}
catch (Exception Ex)
{
rd.status = false;
rd.message = Ex.Message;
}
return(rd);
}
public ReturnClass.ReturnDataTable department_id(bl_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string st = "select dept_id,dept_name from basedepartment order by dept_name ";
rt = db.executeSelectQuery(st);
return(rt);
}
public ReturnClass.ReturnDataTable office_level(bl_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string st = "SELECT OfficeLevelName FROM officelevel where OfficeLevelCode=@OfficeLevelCode ";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter(" DistrictCodeNew", bl.DistrictCodeNew)
};
rt = db.executeSelectQuery(st);
return(rt);
}
public ReturnClass.ReturnDataTable role_id(bl_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string st = "select RollID from login where UserId=@UserId";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("UserId", bl.User_id)
};
rt = db.executeSelectQuery(st, pm);
return(rt);
}
public ReturnClass.ReturnDataTable district_code(bl_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string st = "select District_ID,District_Name from districts where StateCode=@StateCode order by District_Name ";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("StateCode", bl.StateCode)
};
rt = db.executeSelectQuery(st, pm);
return(rt);
}
public ReturnClass.ReturnDataTable permission_employee(bl_emp bl)
{
ReturnClass.ReturnDataTable rd = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string str = @"select pm.approve as approve , pm.review as review, pm.dispose as dispose, pm.forward as forward , pm.reject as reject from permission pm
where pm.emp_map_id = @eom";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("eom", bl.Office_mapping_id)
};
rd = db.executeSelectQuery(str, pm);
return(rd);
}
public ReturnClass.ReturnDataTable select_admin_info(bl_emp bl)
{
ReturnClass.ReturnDataTable rd = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string str = @"select lo.UserID, emp.Name_en, emp.state_id,emp.base_department_id, emp.NewOfficeCode, emp.district_id,lo.RollID from login lo
inner join employee_table emp on emp.emp_id=lo.LoginID
where lo.UserID=@user";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("user", bl.User_id)
};
rd = db.executeSelectQuery(str, pm);
return(rd);
}
public ReturnClass.ReturnDataTable office1(bl_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string st = "select os.NewOfficeCode, os.OfficeName from office os where os.DistrictCodeNew=@DistrictCodeNew and os.StateCode=@StateCode and os.BaseDeptCode=@BaseDeptCode and os.OfficeLevel!='01'";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("DistrictCodeNew", bl.DistrictCodeNew),
new MySqlParameter("StateCode", bl.StateCode),
new MySqlParameter("BaseDeptCode", bl.Department)
};
rt = db.executeSelectQuery(st, pm);
return(rt);
}
public ReturnClass.ReturnDataTable showEmployee(bl_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string st = @"SELECT et.emp_id,et.Name_en,et.mobile_no,et.email_id,(select s.state_name_e from state s where s.state_id = et.state_id)state_name_e,
(select d.district_nm_e from district d where d.district_id = et.district_id)district_nm_e,
(select bd.dept_name from basedepartment bd where bd.dept_id = et.base_department_id)dept_name,
(select o.OfficeName from office o where o.NewOfficeCode=et.NewOfficecode)OfficeName,
et.state_id,et.district_id,et.base_department_id,et.NewOfficeCode
FROM employee_table et";
rt = db.executeSelectQuery(st);
return(rt);
}
public ReturnClass.ReturnDataTable office1(bl_emp bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string st = @"select et.state_id as state_id, em.base_department_id as base_department_id,em.district_id_ofc as district_id, em.office_id, em.office_id as newofficecode, of.OfficeName from emp_office_mapping em
inner join employee_table et on et.emp_id = em.emp_code
inner join office of on of.NewOfficeCode=em.office_id
where em.office_mapping_id = @eom";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("DistrictCodeNew", bl.DistrictCodeNew),
new MySqlParameter("StateCode", bl.StateCode),
new MySqlParameter("BaseDeptCode", bl.Department),
new MySqlParameter("eom", bl.Emp_code)
};
rt = db.executeSelectQuery(st, pm);
return(rt);
}
public ReturnClass.ReturnDataTable select_admin_info(bl_emp bl)
{
ReturnClass.ReturnDataTable rd = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string str = @"select lo.UserID, emp.Name_en, emp.state_id,emp.base_department_id, emp.NewOfficeCode, emp.district_id,lo.RollID from login lo
inner join employee_table emp on emp.emp_id=lo.LoginID
where lo.UserID=@user";
string pp = @"select em.district_id_ofc as district_id,e.state_id as state_id, dis.District_Name_En, em.base_department_id ,em.office_id from emp_office_mapping em
inner join employee_table e on e.emp_id=em.emp_code
inner join districts dis on em.district_id_ofc= dis.District_ID
where em.office_mapping_id=@officemap and em.role_id=@role";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("officemap", bl.Emp_code),
new MySqlParameter("role", bl.Role_id),
new MySqlParameter("user", bl.Userid)
};
rd = db.executeSelectQuery(pp, pm);
return(rd);
}
public ReturnClass.ReturnBool checkuserid(bl_emp bl)
{
ReturnClass.ReturnBool rb = new ReturnClass.ReturnBool();
ReturnClass.ReturnDataTable dt = new ReturnClass.ReturnDataTable();
db_maria_connection objData = new db_maria_connection();
string query = " SELECt * FROM empl_office_mapping WHERE UserID= @user_id";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("user_id", bl.User_id)
};
dt = objData.executeSelectQuery(query, pm);
if (dt.table.Rows.Count == 0)
{
rb.status = false;
}
else
{
rb.status = true;
}
return(rb);
}
public ReturnClass.ReturnBool Update_permission(bl_emp bl)
{
ReturnClass.ReturnBool rb = new ReturnClass.ReturnBool();
db_maria_connection db = new db_maria_connection();
using (TransactionScope ts = new TransactionScope())
{
string str = @"insert into permission_log select * from permission pr where pr.emp_map_id=@eom";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("eom", bl.Office_mapping_id)
};
rb = db.executeInsertQuery(str, pm);
if (rb.status == true)
{
string str1 = @"update permission pm set pm.approve=@approve , pm.review=@review, pm.dispose=@dispose, pm.forward=@forward, pm.reject=@reject,
pm.userid=@userid, pm.ipaddress=@ipad, pm.useragent=@userage,pm.useros=@useros,pm.clientbrowser=@clientbr where pm.emp_map_id=@eom";
MySqlParameter[] pm1 = new MySqlParameter[]
{
new MySqlParameter("approve", bl.Approve),
new MySqlParameter("review", bl.Review),
new MySqlParameter("dispose", bl.Dispose),
new MySqlParameter("forward", bl.Forward),
new MySqlParameter("reject", bl.Reject),
new MySqlParameter("userid", bl.User_id),
new MySqlParameter("ipad", bl.Client_ip),
new MySqlParameter("userage", bl.Useragent),
new MySqlParameter("useros", bl.ClientOS),
new MySqlParameter("clientbr", bl.ClientBrowser),
new MySqlParameter("eom", bl.Office_mapping_id)
};
rb = db.executeUpdateQuery(str1, pm1);
if (rb.status == true)
{
ts.Complete();
}
}
return(rb);
}
}
public ReturnClass.ReturnBool Insert_emp(bl_emp ur)
{
db_maria_connection db = new db_maria_connection();
ReturnClass.ReturnDataTable dt = new ReturnClass.ReturnDataTable();
ReturnClass.ReturnBool rb = new ReturnClass.ReturnBool();
string strQuery = @"INSERT INTO employee_table
(emp_id,user_id,Name_hi,Name_en,Active,state_id,base_department_id,district_id,NewOfficeCode,mobile_no,email_id,emp_date_time,ip_address)
VALUES(@emp_id,@user_id,@Name_hi,@Name_en,@Active,@state_id,@base_department_id,@district_id,@NewOfficeCode,@mobile_no,@email_id,@emp_date_time,@ip_address)";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("emp_id", ur.Emp_id),
new MySqlParameter("user_id", ur.User_id),
new MySqlParameter("Name_hi", ur.Name_hi),
new MySqlParameter("Name_en", ur.Name_en),
new MySqlParameter("email_id", ur.Emailid),
new MySqlParameter("Active", ur.Active),
new MySqlParameter("mobile_no", ur.Mobileno),
new MySqlParameter("district_id", ur.District_id),
new MySqlParameter("state_id", ur.State_id),
new MySqlParameter("base_department_id", ur.Department),
new MySqlParameter("NewOfficeCode", ur.NewOfficeCode),
new MySqlParameter("emp_date_time", ur.Emp_date_time),
new MySqlParameter("ip_address", ur.Client_ip)
};
rb = db.executeInsertQuery(strQuery, pm);
return(rb);
}
public ReturnClass.ReturnDataTable Bind_grid(bl_emp bl)
{
// ReturnClass.ReturnDataTable dt = new ReturnClass.ReturnDataTable();
string qr = "", where_outer = "", where_inner = " and officer_maping_id in ( select emp_map.office_mapping_id as emp_map_id from emp_office_mapping as emp_map where 1=1 ";
try
{
List <MySqlParameter> pm = new List <MySqlParameter>();
if (bl.District_id != "0" && bl.District_id != null && bl.District_id != "" && bl.District_id != "Select")
{
MySqlParameter ba = new MySqlParameter("District", bl.District_id);
pm.Add(ba);
where_inner += " and emp_map.district_id_ofc =@District";
}
if (bl.Department != "" && bl.Department != null && bl.Department != "0" && bl.Department != "Select")
{
MySqlParameter ca = new MySqlParameter("Department", bl.Department);
pm.Add(ca);
where_inner += " and emp_map.base_department_id=@Department";
}
if (bl.Office_id != "" && bl.Office_id != null && bl.Office_id != "0" && bl.Office_id != "Select")
{
MySqlParameter ca = new MySqlParameter("Office", bl.Office_id);
pm.Add(ca);
where_inner += " and emp_map.office_id=@Office ";
}
if (bl.Office_mapping_id != "" && bl.Office_mapping_id != null && bl.Office_mapping_id != "0" && bl.Office_mapping_id != "Select")
{
MySqlParameter ca = new MySqlParameter("emp_map_code", bl.Office_mapping_id);
pm.Add(ca);
where_inner += " and emp_map.office_mapping_id=@emp_map_code ";
}
if (bl.RTI_dept_status != "" && bl.RTI_dept_status != null && bl.RTI_dept_status != "0" && bl.RTI_dept_status != "Select")
{
MySqlParameter ca = new MySqlParameter("dept_status", bl.RTI_dept_status);
pm.Add(ca);
where_outer += " and rst.DeptStatus=@dept_status ";
}
where_inner += " ) ";
qr = @"select rst.rti_id , rtd.Applicant_Name_en as Ap_Name, rtd.rti_Subject as subject , dst.District_Name_En as dist,
bd.dept_name as dept_name, ofc.OfficeName as ofc_name, emp.Name_en as emp_name, ddl.DisplayName_en as dept_status, dst.District_ID as dist_id,
bd.dept_id as dept_id, e_map.office_id as ofc_id, rst.officer_maping_id as ofc_map_id
from rti_status as rst
inner join rti_detail as rtd on rst.rti_id=rtd.rti_id
inner join emp_office_mapping as e_map on e_map.office_mapping_id = rst.officer_maping_id
inner join districts as dst on dst.District_ID= e_map.district_id_ofc
inner join basedepartment as bd on bd.dept_id = e_map.base_department_id
inner join office as ofc on ofc.DistrictCodeNew=e_map.district_id_ofc and ofc.BaseDeptCode= e_map.base_department_id and ofc.NewOfficeCode=e_map.office_id
inner join employee_table as emp on emp.emp_id = e_map.emp_code
left join ddl_list as ddl on ddl.DDL_Name_Value=rst.DeptStatus and ddl.Category='Permission'
where rst.IsValid='Y' and rst.Payment_status='Y'";
// qr = @"select rst.rti_id , rtd.Applicant_Name_en as Ap_Name, rtd.rti_Subject as subject , dst.District_Name_En as dist, bd.dept_name as dept_name, ofc.OfficeName as ofc_name, emp.Name_en as emp_name, ddl.DisplayName_en as dept_status from rti_status as rst
// inner join rti_detail as rtd on rst.rti_id=rtd.rti_id
// inner join emp_office_mapping as e_map on e_map.office_mapping_id = rst.officer_maping_id
// inner join districts as dst on dst.District_ID= e_map.district_id_ofc
// inner join basedepartment as bd on bd.dept_id = e_map.base_department_id
// inner join office as ofc on ofc.DistrictCodeNew=e_map.district_id_ofc and ofc.BaseDeptCode= e_map.base_department_id and ofc.NewOfficeCode=e_map.office_id
// inner join employee_table as emp on emp.emp_id = e_map.emp_code
// left join ddl_list as ddl on ddl.DDL_Name_Value=rst.DeptStatus and ddl.Category='Permission'
// where rst.IsValid='Y' and rst.Payment_status='Y'
// -- and rst.DeptStatus='NREV'
// and officer_maping_id in (
// select emp_map.office_mapping_id as emp_map_id from emp_office_mapping as emp_map
// where 1=1 and emp_map.base_department_id='B001' and emp_map.district_id_ofc='11'
// -- and office_id='2211030318'
// ) ";
string where = where_outer + where_inner + " order by rst.rti_id";
qr = qr + where;
rd = db.executeSelectQuery(qr, pm.ToArray());
}
catch (Exception Ex)
{
rd.status = false;
rd.message = Ex.Message;
}
return(rd);
}
public ReturnClass.ReturnDataTable Bind_grid(bl_emp bl)
{
ReturnClass.ReturnDataTable dt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string qr = "", where = "";
try
{
List <MySqlParameter> pm = new List <MySqlParameter>();
if (bl.State_id != "0" && bl.State_id != null && bl.State_id != "")
{
MySqlParameter ca = new MySqlParameter("State", bl.State_id);
pm.Add(ca);
where += " and emp.state_id=@State";
}
if (bl.District_id != "0" && bl.District_id != null && bl.District_id != "" && bl.District_id != "Select")
{
MySqlParameter ba = new MySqlParameter("District", bl.District_id);
pm.Add(ba);
where += " and emp.district_id =@District";
}
if (bl.Department != "" && bl.Department != null && bl.Department != "0" && bl.Department != "Select")
{
MySqlParameter ca = new MySqlParameter("Department", bl.Department);
pm.Add(ca);
where += " and emp.base_department_id=@Department";
}
if (bl.Office_id != "" && bl.Office_id != null && bl.Office_id != "0" && bl.Office_id != "Select")
{
MySqlParameter ca = new MySqlParameter("Office", bl.Office_id);
pm.Add(ca);
where += " and emp.NewOfficeCode=@Office ";
}
qr = @"select emp.emp_id as id, emp.Name_en as empname,emp.mobile_no as mblname, dic.District_Name as dist, bd.dept_name as depnm,
ofc.OfficeName as office,emp.email_id as email, st.state_name_e as state, st.state_id as state_id,
ofc.DistrictCodeNew as dist_id, emp.NewOfficeCode as ofc_id, emp.base_department_id as dept_id
from employee_table emp
INNER join office ofc on ofc.DistrictCodeNew=emp.district_id and ofc.BaseDeptCode= emp.base_department_id and
ofc.StateCode = emp.state_id and ofc.NewOfficeCode=emp.NewOfficeCode
inner join districts dic on dic.StateCode = emp.state_id and dic.District_ID = emp.district_id
inner join basedepartment bd on bd.dept_id = emp.base_department_id
inner join state st on st.state_id=emp.state_id where 1=1";
where = where + " order by Name_en";
qr = qr + where;
dt = db.executeSelectQuery(qr, pm.ToArray());
}
catch (Exception Ex)
{
dt.status = false;
dt.message = Ex.Message;
}
return(dt);
}
