public ReturnClass.ReturnDataTable GetTotalDepartmentInGrid()
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string str = "";
try
{
str = @"select dept_id, dept_name from basedepartment order by dept_id";
rt = db.executeSelectQuery(str);
}
catch (Exception ex)
{
Gen_Error_Rpt.Write_Error("../dio/dl_Dio/Bind_Grid log.txt");
}
return(rt);
}
public ReturnClass.ReturnDataTable BindOfficerDL(bl_RTI_Request bl)
{
string str = "select office_mapping_id, Name_en from employee_table A, emp_office_mapping B where A.emp_id=B.emp_code and " +
" B.base_department_id= @base_department_id and B.office_level_id= @office_level_id " +
" and B.district_id_ofc=@district_id_ofc and B.office_category= @office_category and B.office_id=@office_id and B.Active='Y' order by Name_en asc";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("base_department_id", bl.Base_department_id),
new MySqlParameter("office_level_id", bl.Office_level_id),
new MySqlParameter("district_id_ofc", bl.District_id_ofc),
new MySqlParameter("office_category", bl.Office_category),
new MySqlParameter("office_id", bl.Office_id)
};
ReturnClass.ReturnDataTable rd = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
rd = db.executeSelectQuery(str, pm);
return(rd);
}
//------------------------- district dmf home page-------24 april-2017----------------------------------------------------//
// public ReturnClass.ReturnDataTable Call_index(bl_home ur)
// {
// db_maria_connection db = new db_maria_connection();
// ReturnClass.ReturnDataTable dt = new ReturnClass.ReturnDataTable();
// MySqlParameter[] pm = new MySqlParameter[]
// {
// new MySqlParameter("District_id",ur.District_id)
// };
//// string qr = @"select notice_id,file_id,Header,Hyperlink,File_Type,active,status,bold,blink,priority,image,permanent,NOW(),
////
////(CASE WHEN Validity_From IS NOT NULL OR Validity_From !='' THEN DATE_FORMAT(Validity_From,'%d/%m/%Y') ELSE 'Not Available' END)Validity_From,
////(CASE WHEN Validity_To IS NOT NULL OR Validity_To!='' THEN DATE_FORMAT(Validity_To,'%d/%m/%Y') ELSE 'Not Available' END)Validity_To,
////(CASE WHEN DATE_FORMAT(Publish_Date,'%d/%m/%Y') IS NULL OR DATE_FORMAT(Publish_Date,'%d/%m/%Y')='' THEN DATE_FORMAT(validity_from,'%d/%m/%Y') ELSE DATE_FORMAT(Publish_Date,'%d/%m/%Y') END)Publish_Date,
////(case when Permanent='Y' then 'Yes' when Permanent='N' then 'No' end)permanent,
//// (case when color is null or color='' then '#000000' else color end)color,
//// (case when url is null or url='' then '../user/notice_download.aspx' else url end)url
////
//// from notice_board where active='A' and status='A' and district_id=@District_id
//// and case when
//// permanent = 'N' then NOW() between validity_from AND Validity_To else NOW() >= Publish_Date end";
// string qr = @"select nb.notice_id,nb.file_id,nb.Header,nb.Hyperlink,nb.File_Type,nb.active,nb.status,nb.bold,nb.blink,nb.priority,nb.image,nb.permanent,NOW(),
//
//(CASE WHEN Validity_From IS NOT NULL OR Validity_From !='' THEN DATE_FORMAT(Validity_From,'%d/%m/%Y') ELSE 'Not Available' END)Validity_From,
//(CASE WHEN Validity_To IS NOT NULL OR Validity_To!='' THEN DATE_FORMAT(Validity_To,'%d/%m/%Y') ELSE 'Not Available' END)Validity_To,
//(CASE WHEN DATE_FORMAT(Publish_Date,'%d/%m/%Y') IS NULL OR DATE_FORMAT(Publish_Date,'%d/%m/%Y')='' THEN DATE_FORMAT(validity_from,'%d/%m/%Y') ELSE DATE_FORMAT(Publish_Date,'%d/%m/%Y') END)Publish_Date,
//(case when Permanent='Y' then 'Yes' when Permanent='N' then 'No' end)permanent,
// (case when color is null or color='' then '#000000' else color end)color,
// (case when url is null or url='' then '../user/notice_download.aspx' else url end)url
//
// from notice_board nb
// left outer join notice_file_details fd on nb.file_id=fd.file_id
// where active='A' and status='A' and district_id=@District_id
// and case when
// permanent = 'N' then NOW() between validity_from AND Validity_To else NOW() >= Publish_Date end limit 10";
// dt = db.executeSelectQuery(qr,pm);
// return dt;
// }
//-------------------------image entry form-------------------------------------------------------------------------------//
public ReturnClass.ReturnBool Insert_notice_image(Upload_doc bl)
{
ReturnClass.ReturnBool rb = new ReturnClass.ReturnBool();
db_maria_connection db = new db_maria_connection();
MySqlParameter[] param = new MySqlParameter[]
{
new MySqlParameter("image", bl.filename),
new MySqlParameter("user_id", bl.user_id),
new MySqlParameter("district_id", bl.district_id)
};
string qur = "insert into notice_image(image,user_id,district_id) "
+ " values(@image,@user_id,@district_id);";
rb = db.executeInsertQuery(qur, param);
return(rb);
}
} // End // it is not required
public string GetMaxValueRtiFiles()
{
string value = "";
string query = " SELECT IFNULL(MAX(rti_fileID),0) as NO FROM rti_files";
ReturnClass.ReturnDataTable dt;//= new ReturnClass.ReturnDataTable();
db_maria_connection objData = new db_maria_connection();
try
{
dt = objData.executeSelectQuery(query);
int count = Convert.ToInt32(dt.table.Rows[0]["NO"]) + 1;
value = count.ToString();
}
catch (Exception ex)
{
}
return(value);
} // End
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
public ReturnClass.ReturnDataTable Get_Office_Data(bl_empMap bl)
{
string strquery = @"SELECT NewOfficeCode, OfficeName FROM office WHERE
office.BaseDeptCode=@dept AND office.DistrictCodeNew=@district
AND office.OfficeCategory=@officeCategory AND office.OfficeLevel=@officeLevel order by OfficeName ";
//string str = @"SELECT OfficeLevelCode, OfficeLevelName FROM officelevel WHERE officelevel.BaseDeptCode=@dept order by OfficeLevelName ";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("dept", bl.Base_department_id),
new MySqlParameter("district", bl.District_id_ofc),
new MySqlParameter("officeCategory", bl.Office_category),
new MySqlParameter("officeLevel", bl.Office_level_id)
};
ReturnClass.ReturnDataTable rd = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
rd = db.executeSelectQuery(strquery, pm);
return(rd);
}
}// end of get GetLoginPassword
public ReturnClass.ReturnDataTable GetDDL_Data(string qr)
{
ReturnClass.ReturnDataTable dt = null;
db_maria_connection db = new db_maria_connection();
try
{
dt = db.executeSelectQuery(qr);
dt.status = true;
}
catch (Exception Ex)
{
dt.status = false;
dt.message = Ex.Message;
}
return dt;
}
} // End of Get_EmpDesName
public ReturnClass.ReturnDataTable Get_OfficeDesignation(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.office_id, emp_map.designation_id,
concat (ofc.OfficeName,'-', des.Designation_Name) as ofc_des_Text
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
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);
}
} // 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
//notice entry methods... Naveen
public ReturnClass.ReturnDataTable Get_Count()
{
ReturnClass.ReturnDataTable rd = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string str = @" select count(*) as count_rti_dept_ofc_emp from rti_detail A inner join rti_status B on A.rti_id=B.rti_id where B.IsValid='Y' and B.Payment_status='Y'
union all
select count(dept_name) as count_dept from basedepartment
union all
select count(*) as office_count from office ofc
inner join districts dic on dic.StateCode=ofc.StateCode and dic.district_id = ofc.DistrictCodeNew
inner join basedepartment bd on bd.dept_id = ofc.BaseDeptCode
inner join officelevel ol on ol.OfficeLevelCode = ofc.OfficeLevel and ol.StateCode= ofc.StateCode and ol.BaseDeptCode=ofc.BaseDeptCode
union all
select count(*) as officer_count from employee_table emp inner join emp_office_mapping eom on eom.emp_code=emp.emp_id where eom.designation_id='2'";
rd = db.executeSelectQuery(str);
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";
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.ReturnDataTable GetTotalPIOInGrid(bl_report bl)
{
ReturnClass.ReturnDataTable rt = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string str = "";
try
{
str = @"select emp.Name_en from employee_table emp inner join emp_office_mapping eom on eom.emp_code=emp.emp_id where eom.designation_id='2' ";
rt = db.executeSelectQuery(str);
}
catch (Exception ex)
{
Gen_Error_Rpt.Write_Error("../dio/dl_Dio/Bind_Grid log.txt");
}
return(rt);
}
/// <summary>
/// Aim of method: The Method is used to delete e-challan from e-challan
/// Database Table :society_aims
/// bl layer use:bl_society_fee- (MEMORANDUM_OF_SOCIETY_From_1_Gridview3())
/// </summary>
/// <returns></returns>
public ReturnClass.ReturnBool delete_echallan(bl_RTI_Fees reg)
{
ReturnClass.ReturnBool rb = new ReturnClass.ReturnBool();
db_maria_connection db = new db_maria_connection();
try
{
using (TransactionScope ts = new TransactionScope())
{
MySqlParameter[] pr = new MySqlParameter[]{
new MySqlParameter("RTI_provisional_no",reg.RTI_provisional_no),
new MySqlParameter("serial_no",reg.Serial_no)
};
MySqlParameter[] pr1 = new MySqlParameter[]{
new MySqlParameter("RTI_provisional_no",reg.RTI_provisional_no),
new MySqlParameter("serial_no",reg.Serial_no)
};
string qr = "insert into echallan_detail_log select * from echallan_detail where RTI_provisional_no=@RTI_provisional_no and serial_no=@serial_no ";
rb = db.executeInsertQuery(qr, pr);
if (rb.status == true)
{
string qrr = "delete from echallan_detail where RTI_provisional_no=@RTI_provisional_no and serial_no=@serial_no";
rb = db.executeDeleteQuery(qrr, pr1);
}
if (rb.status == true)
{
ts.Complete();
rb.status = true;
}
}
}
catch (Exception Ex)
{
rb.status = false;
rb.message = Ex.Message;
}
return rb;
}
}// end of get GetGender
public ReturnClass.ReturnDataTable GetUserData(bl_RTI_Request ur)
{
ReturnClass.ReturnDataTable dt = null;// = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
try
{
string qr = "SELECT Name_hi, Name_en, EmailID, Gender, Address, PinCode, Country, StateCode, UserType, " +
" MobileNo, DistrictCode, Country_name " +
" FROM user_registration WHERE UserID=@userid AND IsValid='Y' ";
MySqlParameter[] pr = new MySqlParameter[] {
new MySqlParameter("userid", ur.RTI_login_userName)
};
dt = db.executeSelectQuery(qr, pr);
//dt = db.executeSelectQuery(qr);
if (dt.table != null)
{
ur.NameEnglish = dt.table.Rows[0]["Name_en"].ToString();
ur.NameHindi = dt.table.Rows[0]["Name_hi"].ToString();
ur.Email = dt.table.Rows[0]["EmailID"].ToString();
ur.Gender = dt.table.Rows[0]["Gender"].ToString();
ur.Address = dt.table.Rows[0]["Address"].ToString();
ur.Pincode = dt.table.Rows[0]["PinCode"].ToString();
ur.Country = dt.table.Rows[0]["Country"].ToString();
ur.State = dt.table.Rows[0]["StateCode"].ToString();
ur.UserType = dt.table.Rows[0]["UserType"].ToString();
ur.Mobile = dt.table.Rows[0]["MobileNo"].ToString();
ur.District = dt.table.Rows[0]["DistrictCode"].ToString();
ur.CountryName = dt.table.Rows[0]["Country_name"].ToString();
}
dt.status = true;
}
catch (Exception Ex)
{
dt.status = false;
dt.message = Ex.Message;
}
return(dt);
}// end of get GetUserData
public ReturnClass.ReturnDataTable GetRole()
{
ReturnClass.ReturnDataTable dt = null;
db_maria_connection db = new db_maria_connection();
try
{
string qr = "SELECT Role_id,RoleName FROM role_table ";
dt = db.executeSelectQuery(qr);
dt.status = true;
}
catch (Exception Ex)
{
dt.status = false;
dt.message = Ex.Message;
}
return(dt);
}// end of GetRole
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);
}
/// <summary>
/// Aim of method: The Method is used to select_tr_ref_no from e-challan
/// Database Table :society_aims
/// bl layer use:bl_society_fee- (MEMORANDUM_OF_SOCIETY_From_1_Gridview3())
/// </summary>
/// <returns></returns>
public ReturnClass.ReturnDataTable select_tr_ref_no(echallan ec)
{
ReturnClass.ReturnDataTable rb = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
try
{
string qr = "select tr_ref_no from echallan_detail where tr_ref_no=@tr_ref_no";
MySqlParameter[] pr = new MySqlParameter[]{
new MySqlParameter("tr_ref_no",ec.Tr_ref_no)
};
rb = db.executeSelectQuery(qr, pr);
}
catch (Exception Ex)
{
rb.status = false;
rb.message = Ex.Message;
}
return rb;
}
public ReturnClass.ReturnBool Insert_FileDetails(Upload_doc ne)
{
ReturnClass.ReturnBool rb = new ReturnClass.ReturnBool();
db_maria_connection db = new db_maria_connection();
MySqlParameter[] param = new MySqlParameter[]
{
new MySqlParameter("Header", ne.Filename),
new MySqlParameter("file_ext", ne.File_Extn),
new MySqlParameter("file_data", ne.File_Data),
new MySqlParameter("Client_Ip", ne.Client_ip),
new MySqlParameter("User_Id", ne.User_id),
new MySqlParameter("Date_Time", ne.Upload_Date)
};
string qur = "insert into notice_file_details(File_Header,mime_type,file_data,client_ip,user_code,upload_date) "
+ " values(@Header,@file_ext,@file_data,@Client_Ip,@User_Id,@Date_Time);";
rb = db.executeInsertQuery(qur, param);
return(rb);
}
public ReturnClass.ReturnBool Insert_Notice(Notice_Entry ne)
{
ReturnClass.ReturnBool rb = new ReturnClass.ReturnBool();
db_maria_connection db = new db_maria_connection();
MySqlParameter[] param = new MySqlParameter[]
{
new MySqlParameter("Header", ne.Header),
new MySqlParameter("Pernament", ne.Permanent),
new MySqlParameter("Publish_Date", ne.Publish_date),
new MySqlParameter("Validity_From", ne.Datefrom),
new MySqlParameter("Validity_To", ne.Dateto),
new MySqlParameter("Active", ne.Active),
new MySqlParameter("Hyperlink", ne.Hyperlink),
new MySqlParameter("File_Type", ne.Filetype),
new MySqlParameter("File_Id", ne.file_ID),
new MySqlParameter("Url", ne.Url),
new MySqlParameter("Client_Ip", ne.Client_ip),
new MySqlParameter("User_Id", ne.User_id),
new MySqlParameter("Date_Time", ne.Datetime),
new MySqlParameter("Bold", ne.Bold),
new MySqlParameter("Blink", ne.Blink),
new MySqlParameter("Priority", ne.Priority),
new MySqlParameter("Color", ne.Color),
new MySqlParameter("Image", ne.Image),
new MySqlParameter("status", ne.status),
new MySqlParameter("district_id", ne.District_id),
new MySqlParameter("office_id", ne.Office_id),
new MySqlParameter("base_dept_id", ne.Base_dept_id)
};
string qur = @"insert into Notice_Board(Header,Permanent,Publish_Date,Validity_From,Validity_To,Active,Hyperlink,File_Type,File_Id,Url,Client_Ip,User_Id,Date_Time,
bold,blink,priority,color,image,status,district_id, base_dept_id, office_id)
values(@Header,@Pernament,@Publish_Date,@Validity_From,@Validity_To,@Active,@Hyperlink,@File_Type,@File_Id,@Url,@Client_Ip,@User_Id,@Date_Time,
@Bold,@Blink,@Priority,@Color,@Image,@status,@district_id,@base_dept_id,@office_id)";
rb = db.executeInsertQuery(qur, param);
return(rb);
}
} // 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
public ReturnClass.ReturnBool Update_permission(bl_report 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);
}
}
//#region
///// <summary>
///// Aim of method: The Method is used to get fee from society_fees_master
///// Database Table :society_aims
///// bl layer use:bl_society_fee- (MEMORANDUM_OF_SOCIETY_From_1_Gridview3())
///// </summary>
///// <returns></returns>
//public ReturnClass.ReturnDataTable getfee(bl_RTI_Fees reg)
//{
// ReturnClass.ReturnDataTable rb = new ReturnClass.ReturnDataTable();
// db_maria_connection db = new db_maria_connection();
// try
// {
// string qr = "select fees_code,fees_details,Amount from society_fees_master where society_type_code=(select society_type_code from society where RTI_provisional_no=@RTI_provisional_no) ";
// MySqlParameter[] pr = new MySqlParameter[]{
// new MySqlParameter("RTI_provisional_no",reg.RTI_provisional_no)
// };
// rb = db.executeSelectQuery(qr, pr);
// }
// catch (Exception Ex)
// {
// rb.status = false;
// rb.message = Ex.Message;
// }
// return rb;
//}
//#endregion
#region
/// <summary>
/// Aim of method: The Method is used to get record from e-challan
/// Database Table :society_aims
/// bl layer use:bl_society_fee- (MEMORANDUM_OF_SOCIETY_From_1_Gridview3())
/// </summary>
/// <returns></returns>
public ReturnClass.ReturnDataTable getrecord(bl_RTI_Fees reg)
{
ReturnClass.ReturnDataTable rb = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
try
{
string qr = "select * from echallan_detail where RTI_provisional_no=@RTI_provisional_no order by serial_no";
MySqlParameter[] pr = new MySqlParameter[]{
new MySqlParameter("RTI_provisional_no",reg.RTI_provisional_no)
};
rb = db.executeSelectQuery(qr, pr);
}
catch (Exception Ex)
{
rb.status = false;
rb.message = Ex.Message;
}
return rb;
}
}//end of Insert_rti_filed_user
public ReturnClass.ReturnBool Insert_RTI_files(bl_RTI_RequestFiles ur)
{
string strQuery = "INSERT INTO rti_files " +
"( rti_fileID, fileName, fileType, fileData, FileDescription, rti_id, BPL_RTI_FileType ) " +
" VALUES (@rti_fileID, @fileName, @fileType, @fileData,@FileDescription, @rti_requestID,@bpl_rti_FileType )";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("rti_fileID", ur.RTI_fileID),
new MySqlParameter("fileName", ur.RTI_fileName),
new MySqlParameter("fileType", ur.RTI_fileType),
new MySqlParameter("fileData", ur.RTI_fileData),
new MySqlParameter("FileDescription", ur.FileDescription),
new MySqlParameter("rti_requestID", ur.RTI_Request_id),
new MySqlParameter("bpl_rti_FileType", ur.BPL_RTI_FileType)
};
ReturnClass.ReturnBool dt = null;
db_maria_connection objDB = new db_maria_connection();
dt = objDB.executeInsertQuery(strQuery, pm);
return(dt);
}//end of Insert_RTI_files
//end of Insert_Login
public ReturnClass.ReturnBool Insert_user_Registration(bl_RTI_Registration ur)
{
string strQuery = @"INSERT INTO user_registration (RegistrationID, UserID,Password, Name_hi, Name_en, Gender,MobileNo, EmailID, Address,DistrictCode,District_name,StateCode,
Country,Country_name ,PinCode, IsValid, UserType, Registration_Year, client_ip,useragent,client_os,client_browser )
VALUES (@registrationID, @userID,@Password, @nameHindi, @nameEnglish,@gender, @mobileNo, @emailID, @address, @districtCode,@distname, @statecode ,@country,
@Country_name, @pinCode,@isValid, @userType, @registration_Year,@client_ip, @useragent, @client_os, @client_browser)";
MySqlParameter[] pm = new MySqlParameter[]
{
new MySqlParameter("registrationID", ur.RegistrationID),
new MySqlParameter("userID", ur.UserID),
new MySqlParameter("Password", ur.Password),
new MySqlParameter("nameHindi", ur.NameHindi),
new MySqlParameter("nameEnglish", ur.NameEnglish),
new MySqlParameter("gender", ur.Gender),
new MySqlParameter("mobileNo", ur.MobileNo),
new MySqlParameter("emailID", ur.EmailID ),
new MySqlParameter("address", ur.Address),
new MySqlParameter("districtCode", ur.DistrictCode),
new MySqlParameter("distname",ur.DistrictName),
new MySqlParameter("statecode",ur.Statecode),
new MySqlParameter("country", ur.Country),
new MySqlParameter("Country_name",ur.Country_name),
new MySqlParameter("pinCode", ur.PinCode),
new MySqlParameter("isValid", ur.IsValid),
new MySqlParameter("userType", ur.UserType),
new MySqlParameter("registration_Year", ur.Registration_Year),
new MySqlParameter("client_ip",ur.UserIP),
new MySqlParameter("useragent",ur.UserAgent),
new MySqlParameter("client_os",ur.UserOS),
new MySqlParameter("client_browser",ur.User_browser)
};
ReturnClass.ReturnBool dt = new ReturnClass.ReturnBool();
db_maria_connection objDB = new db_maria_connection();
dt = objDB.executeInsertQuery(strQuery, pm);
return dt;
}
public ReturnClass.ReturnDataTable BindOfficerDL(bl_RTI_Request bl)
{
string str = @"select office_mapping_id,concat( A.Name_en,'/', dg.Designation_Name ) as Name_en from employee_table as A
inner join emp_office_mapping as B on A.emp_id=B.emp_code
inner join designation as dg on dg.Designation_ID = B.designation_id
inner join office as ofc on ofc.NewOfficeCode = B.office_id
where B.base_department_id= @base_department_id and B.district_id_ofc=@district_id_ofc
and B.office_id=@office_id and B.Active='Y' order by Name_en asc";
//string str = "select office_mapping_id, Name_en from employee_table A, emp_office_mapping B where A.emp_id=B.emp_code and " +
// " B.base_department_id= @base_department_id and B.office_level_id= @office_level_id " +
// " and B.district_id_ofc=@district_id_ofc and B.office_category= @office_category and B.office_id=@office_id and B.Active='Y' order by Name_en asc";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("base_department_id", bl.Base_department_id),
new MySqlParameter("district_id_ofc", bl.District_id_ofc),
new MySqlParameter("office_id", bl.Office_id)
};
ReturnClass.ReturnDataTable rd = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
rd = db.executeSelectQuery(str, pm);
return(rd);
}
public ReturnClass.ReturnBool Insert_DeptUserManual(blAdmin 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 dept_user_manual_file
(User_ID,base_department_id,office_id,office_level_id,district_id_ofc,office_category,Client_OS,ClientBrowser,UserAgent,Client_IP,file_name,content_type,
file_data,file_description, Year_Issue)
VALUES(@User_ID,@base_department_id,@office_id,@office_level_id,@district_id_ofc,@office_category,@Client_OS,@ClientBrowser,
@UserAgent,@Client_IP,@file_name,@content_type, @file_data, @file_description, @Year_Issue ) ";
MySqlParameter[] pm = new MySqlParameter[]{
new MySqlParameter("User_ID",ur.UserID),
new MySqlParameter("base_department_id",ur.Base_department_id),
new MySqlParameter("office_id",ur.Office_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("Client_OS",ur.ClientOS),
new MySqlParameter("ClientBrowser",ur.ClientBrowser),
new MySqlParameter("UserAgent",ur.Useragent),
new MySqlParameter("Client_IP",ur.Client_ip),
new MySqlParameter("file_name",ur.FileName),
new MySqlParameter("content_type",ur.ContentType),
new MySqlParameter("file_data",ur.FileData) ,
new MySqlParameter("file_description",ur.FileDescription),
new MySqlParameter("Year_Issue",ur.Year_issue)
};
rb = db.executeInsertQuery(strQuery, pm);
return rb;
}
public ReturnClass.ReturnBool Insert_emp(bl_report bl)
{
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", bl.Emp_id),
new MySqlParameter("user_id", bl.User_id),
new MySqlParameter("Name_hi", bl.Name_hi),
new MySqlParameter("Name_en", bl.Name_en),
new MySqlParameter("email_id", bl.Emailid),
new MySqlParameter("Active", bl.Active),
new MySqlParameter("mobile_no", bl.Mobileno),
new MySqlParameter("district_id", bl.District_id),
new MySqlParameter("state_id", bl.State_id),
new MySqlParameter("base_department_id", bl.Department),
new MySqlParameter("NewOfficeCode", bl.NewOfficeCode),
new MySqlParameter("emp_date_time", bl.Emp_date_time),
new MySqlParameter("ip_address", bl.Client_ip)
};
rb = db.executeInsertQuery(strQuery, pm);
return(rb);
}
public ReturnClass.ReturnDataTable Get_RTI_DDL_Data(string qr)
{
ReturnClass.ReturnDataTable dt = null;// = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
try
{
// string qr = "SELECT District_ID, District_Name_En FROM districts ";
//MySqlParameter[] pr = new MySqlParameter[]{
// new MySqlParameter("gender", category)
// };
//dt = db.executeSelectQuery(qr, pr);
dt = db.executeSelectQuery(qr);
dt.status = true;
}
catch (Exception Ex)
{
dt.status = false;
dt.message = Ex.Message;
}
return(dt);
}// end of get GetDistrict
public string Max_office_code1(bl_report bl)
{
ReturnClass.ReturnDataTable rd = new ReturnClass.ReturnDataTable();
db_maria_connection db = new db_maria_connection();
string oficecode;
string str = "select max(substring(ofc.NewOfficeCode,7,10) )as max from office ofc where ofc.DistrictCodeNew=@district";
MySqlParameter[] pm = new MySqlParameter[] {
new MySqlParameter("district", bl.District)
};
rd = db.executeSelectQuery(str, pm);
if (rd.table.Rows.Count > 0)
{
int code = Convert.ToInt32(rd.table.Rows[0]["max"]);
code = code + 1;
string code1 = code.ToString("0000");
oficecode = bl.State_id + bl.District + bl.Office_level_id + code1;
}
else
{
oficecode = bl.State_id + bl.District + bl.Office_level_id + "0001";
}
return(oficecode);
}
