public static ReportingTo GetReportingtoByEmpId(int employee_id)
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from employee in datacontext.Employees
join employee2 in datacontext.Employees
on employee.reporting_to equals employee2.id
where employee.id == employee_id
select new ReportingTo
{
emp_name = employee2.first_name,
emp_id = employee2.id,
mailid = employee2.email
};
return(query.FirstOrDefault());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Debug.WriteLine(exception.GetBaseException());
throw exception;
}
finally
{
datacontext.Dispose();
}
}
public static List <ClientModel> GetActiveClientList()
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from x in datacontext.Clients
join y in datacontext.Client_type
on x.type_id equals y.id
where x.is_active == 1
select new ClientModel
{
client_id = x.id,
client_name = x.client_name,
client_type_id = x.type_id,
client_type_name = y.type_name,
client_type_description = y.type_description,
is_active = x.is_active
};
return(query.ToList());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Debug.WriteLine(exception.GetBaseException());
throw exception;
}
finally
{
datacontext.Dispose();
}
}
public List <MEnterpriseCases> GetBgMEnterpriseCases()
{
EMSEntities entity = new EMSEntities();
List <MEnterpriseCases> _list = entity.MEnterpriseCases.ToList();
return(_list);
}
public static List <TaskModel> GetTaskList(int project_id)
{
var predicate = LinqKit.PredicateBuilder.True <Task>();
if (project_id != 0)
{
predicate = predicate.And(i => i.project_id == project_id);
}
EMSEntities datacontext = new EMSEntities();
try
{
var query = from task in datacontext.Tasks.AsExpandable().Where(predicate)
select new TaskModel
{
id = task.id,
task_name = task.task_name,
task_description = task.task_description,
project_id = task.project_id
};
return(query.ToList());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Debug.WriteLine(exception.GetBaseException());
throw exception;
}
finally
{
datacontext.Dispose();
}
}
public List <MEnterpriseCases> GetMEnterpriseCases()
{
EMSEntities entity = new EMSEntities();
List <MEnterpriseCases> _list = entity.MEnterpriseCases.Where(m => m.MEnterpriseCaseShow).ToList();
return(_list);
}
public IHttpActionResult PostStudentsByClIdandGrpId(PromotionViewModel st)
{
if (!ModelState.IsValid)
{
return(BadRequest("Invalid data."));
}
IList <StudentViewModel> lss = null;
using (var ctx = new EMSEntities())
{
lss = ctx.spStudentCurrentClass((double)st.CLASS_TRNNO, st.GRPMST_TRNNO)
.Select(s => new StudentViewModel()
{
TRNNO = s.TRNNO,
EMP_ID = s.EMP_ID,
EMP_F_NAME = s.EMP_F_NAME,
EMP_NAME = s.EMP_NAME
}).ToList <StudentViewModel>();
}
if (lss == null)
{
return(NotFound());
}
return(Ok(lss));
}
public static List <LeaveTypeListModel> GetLeaveTypeList()
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from lt in datacontext.Leave_type
//join lt in datacontext.Leave_type on l.leavetype_id equals lt.id
select new LeaveTypeListModel
{
leavetype_id = lt.id,
type_name = lt.type_name
};
return(query.ToList());
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine(e.GetBaseException());
throw e;
}
finally
{
datacontext.Dispose();
}
}
public IHttpActionResult GetAllMarkTotal()
{
//IEnumerable<MarkTotalViewModel> markTotal = null;
IList <MarkTotalViewModel> mks = null;
using (var ctx = new EMSEntities())
{
// mks = ctx.MARKTOTALs.ToList<MARKTOTAL>();
//var st= ctx.MARKTOTALs.SqlQuery("select * from ems.MARKTOTAL where trnno=19").FirstOrDefault<MARKTOTAL>();
// var nestedquery = from s in ctx.MARKTOTALs
// from c in s.MARKTOTALDTLs
// where s.TRNNO == 19
// select new { s.TRNNO, c };
// var result = nestedquery.ToList();
//var mktotal=ctx.MARKTOTALs
mks = ctx.MARKTOTALs
.Select(s => new MarkTotalViewModel()
{
TRNNO = s.TRNNO,
CLASS_TRNNO = s.CLASS_TRNNO,
EXAM_TRNNO = s.EXAM_TRNNO,
GRPMST_TRNNO = s.GRPMST_TRNNO,
MDT = s.MDT,
}).ToList <MarkTotalViewModel>();
}
if (mks.Count == 0)
{
return(NotFound());
}
return(Ok(mks));
}
public MEnterpriseAdmin GetMEnterpriseAdmin(string adminName)
{
EMSEntities entity = new EMSEntities();
MEnterpriseAdmin admin = entity.MEnterpriseAdmin.SingleOrDefault(m => (m.AdminName == adminName));
return(admin);
}
//public static List<Holiday_List> GetHolidayList()
//{
// EMSEntities datacontext = new EMSEntities();
// try
// {
// var query = from hl in datacontext.Holiday_List
// select hl;
// return query.ToList();
// }
// catch (Exception e)
// {
// Debug.WriteLine(e.Message);
// Debug.WriteLine(e.GetBaseException());
// throw e;
// }
// finally
// {
// datacontext.Dispose();
// }
//}
public static List <LeaveBalanceModel> GetLeaveBalanceById(int employee_id)
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from lbs in datacontext.Leavebalance_sheet
//join e in datacontext.Employees on lbs.employee_id equals e.id
join lt in datacontext.Leave_type on lbs.leavetype_id equals lt.id
//join u in datacontext.Users on e.
where lbs.employee_id == employee_id
select new LeaveBalanceModel
{
leavetype_id = lbs.leavetype_id,
type_name = lt.type_name,
no_of_days = (decimal)lbs.no_of_days
};
return(query.ToList());
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine(e.GetBaseException());
throw e;
}
finally
{
datacontext.Dispose();
}
}
public IHttpActionResult GetFeeCollectById(int id)
{
FeeCollectViewModel fcs = null;
using (var ctx = new EMSEntities())
{
fcs = ctx.FEECOLLECTMSTs
.Where(s => s.TRNNO == id)
.Select(s => new FeeCollectViewModel()
{
TRNNO = s.TRNNO,
RDATE = s.RDATE,
EM_TRNNO = s.EM_TRNNO,
FMONTH = s.FMONTH,
DDATE = s.DDATE,
LDATE = s.LDATE,
FSTATUS = s.FSTATUS,
DISCOUNT = s.DISCOUNT,
DISCOUNTTYPE = s.DISCOUNTTYPE,
CLASS_TRNNO = s.CLASS_TRNNO,
SECDTL_TRNNO = s.SECDTL_TRNNO,
SECDTL_SR = s.SECDTL_SR,
PDATE = s.PDATE,
ARRFLG = s.ARRFLG,
}).FirstOrDefault <FeeCollectViewModel>();
}
if (fcs == null)
{
return(NotFound());
}
return(Ok(fcs));
}
public EnterpriseDynamic EnterpriseDynamicByEnterpriseDynamicID(int DynamicID)
{
EMSEntities entity = new EMSEntities();
EnterpriseDynamic enterpriseDynamic = entity.EnterpriseDynamic.SingleOrDefault(m => m.EnterpriseDynamicID == DynamicID);
return(enterpriseDynamic);
}
public EnterpriseCases EnterpriseCaseByEnterpriseCaseID(int EnterpriseCaseID)
{
EMSEntities entity = new EMSEntities();
EnterpriseCases enterpriseCases = entity.EnterpriseCases.SingleOrDefault(m => m.EnterpriseCasesID == EnterpriseCaseID);
return(enterpriseCases);
}
public static bool Login(User user)
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from u in datacontext.Users
where u.user_name == user.user_name && u.password == user.password && u.is_active == 1
select u;
if (query.ToList().Count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine(e.GetBaseException());
return(false);
}
finally
{
datacontext.Dispose();
}
}
public List <EnterpriseCases> EnterpriseCases(int EnterpriseID)
{
EMSEntities entity = new EMSEntities();
List <EnterpriseCases> list = entity.EnterpriseCases.Where(m => m.EnterpriseID == EnterpriseID).ToList();
return(list);
}
public IHttpActionResult GetAllLeaveTypes(string name)
{
IList <LeaveTypeViewModel> ltps = null;
using (var ctx = new EMSEntities())
{
ltps = ctx.LEAVETYPEs
.Where(s => s.LDESC.ToLower() == name.ToLower())
.Select(s => new LeaveTypeViewModel()
{
TRNNO = s.TRNNO,
LDESC = s.LDESC,
LABRV = s.LABRV,
LWEIGHT = s.LWEIGHT,
STATUS = s.STATUS
}).ToList <LeaveTypeViewModel>();
}
if (ltps.Count == 0)
{
return(NotFound());
}
return(Ok(ltps));
}
public IHttpActionResult GetLeaveTypeById(int id)
{
LeaveTypeViewModel ltps = null;
using (var ctx = new EMSEntities())
{
ltps = ctx.LEAVETYPEs
.Where(s => s.TRNNO == id)
.Select(s => new LeaveTypeViewModel()
{
TRNNO = s.TRNNO,
LDESC = s.LDESC,
LABRV = s.LABRV,
LWEIGHT = s.LWEIGHT,
STATUS = s.STATUS
}).FirstOrDefault <LeaveTypeViewModel>();
}
if (ltps == null)
{
return(NotFound());
}
return(Ok(ltps));
}
public static TaskModel GetTaskDetailsById(int task_id)//t_id task_id
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from task in datacontext.Tasks
where task.id == task_id
select new TaskModel
{
id = task.id,
task_name = task.task_name,
task_description = task.task_description,
project_id = task.project_id
};
return(query.FirstOrDefault());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Debug.WriteLine(exception.GetBaseException());
throw exception;
}
finally
{
datacontext.Dispose();
}
}
public static List <TimeSheetModel> GetSheetByEmpId(int employee_id)//e_id employee_id
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from sheet in datacontext.Timesheets
where sheet.employee_id == employee_id
select new TimeSheetModel
{
id = sheet.id,
employee_id = sheet.employee_id,
task_id = sheet.task_id,
project_id = sheet.project_id,
work_date = sheet.work_date,
work_hour = sheet.work_hour
};
return(query.ToList());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Debug.WriteLine(exception.GetBaseException());
throw exception;
}
finally
{
datacontext.Dispose();
}
}
public List <Models.EnterpriseDynamic> EnterpriseDynamics(int EnterpriseID)
{
EMSEntities entity = new EMSEntities();
List <EnterpriseDynamic> list = entity.EnterpriseDynamic.Where(m => m.EnterpriseID == EnterpriseID).ToList();
return(list);
}
public static void InsertLeaveBalance(Employee employee, int[] arr)//arr constant array - contains manager , HR and TeamLead ID
{
EMSEntities datacontext = new EMSEntities();
try
{
for (int i = 0; i < arr.Length; i++)
{
Leavebalance_sheet leave_balance = new Leavebalance_sheet();
leave_balance.employee_id = employee.id;
leave_balance.leavetype_id = arr[i];
leave_balance.no_of_days = (decimal)Utils.LeaveCalculationBasedDOJ(employee.date_of_joining, arr[i]);
leave_balance.actual_balance = leave_balance.no_of_days;
datacontext.Leavebalance_sheet.Add(leave_balance);
}
datacontext.SaveChanges();
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Debug.WriteLine(exception.GetBaseException());
throw exception;
}
finally
{
datacontext.Dispose();
}
}
public MEnterpriseAdmin GetMEnterpriseAdmin(int adminID)
{
EMSEntities entity = new EMSEntities();
MEnterpriseAdmin admin = entity.MEnterpriseAdmin.SingleOrDefault(m => (m.MEnterpriseAdminID == adminID));
return(admin);
}
public static void InactiveEmployee(Employee existinginstance)
{
EMSEntities datacontext = new EMSEntities();
try
{
//var query = from employee in datacontext.Employees
// join user in datacontext.Users
// on employee.user_id equals user.id
// where employee.id == e_id && user.is_active ==1
// select user;
var query = from user in datacontext.Users
where existinginstance.user_id == user.id && user.is_active == 1
select user;
User user_details = query.FirstOrDefault();
user_details.is_active = 0;
datacontext.Entry(user_details).State = EntityState.Modified;
datacontext.SaveChanges();
//datacontext.Entry(query.FirstOrDefault()).State = EntityState.Deleted;
//datacontext.SaveChanges();
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Debug.WriteLine(exception.GetBaseException());
throw exception;
}
finally
{
datacontext.Dispose();
}
}
public MEnterpriseAdmin GetMEnterpriseAdmin(string adminName, string password)
{
EMSEntities entity = new EMSEntities();
MEnterpriseAdmin admin = entity.MEnterpriseAdmin.SingleOrDefault(m => m.AdminName == adminName && m.MEnterpriseAdminPassword == password);
return(admin);
}
public static List <DesignationModel> GetDesignationList()
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from d_list in datacontext.Designations
select new DesignationModel
{
id = d_list.id,
name = d_list.name,
description = d_list.description
};
return(query.ToList());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Debug.WriteLine(exception.GetBaseException());
throw exception;
}
finally
{
datacontext.Dispose();
}
}
public static List <EmployeeListByRoleModel> GetEmployeeListByRole(int id)
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from pr in datacontext.Project_role
join e in datacontext.Employees on pr.employee_id equals e.id
join p in datacontext.Projects on pr.project_id equals p.id
join r in datacontext.Roles on pr.role_id equals r.id
where e.reporting_to == id && r.role_type == "Project Role"
select new EmployeeListByRoleModel
{
id = e.id,
first_name = e.first_name,
last_name = e.last_name,
email = e.email,
project_name = p.project_name,
project_role = r.role_name
};
return(query.ToList());
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine(e.GetBaseException());
throw e;
}
finally
{
datacontext.Dispose();
}
}
public static List <ReportingTo> GetReportingtoList()
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from employee in datacontext.Employees
join userrole in datacontext.User_role on employee.user_id equals userrole.user_id
join user in datacontext.Users on employee.user_id equals user.id
where (user.is_active == 1) && (userrole.role_id == Constants.ROLE_HR || userrole.role_id == Constants.SYSTEMROLE_MANAGER || userrole.role_id == Constants.SYSTEMROLE_TEAMLEAD)
select new ReportingTo
{
emp_name = employee.first_name + " " + employee.last_name,
emp_id = employee.id
};
return(query.ToList());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Debug.WriteLine(exception.GetBaseException());
throw exception;
}
finally
{
datacontext.Dispose();
}
}
public static LeavehistoryModel GetLeaveDetailsByLeaveId(int leave_id)
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from l in datacontext.Leaves
join ls in datacontext.Status_leave on l.leave_statusid equals ls.id
join e in datacontext.Employees on l.employee_id equals e.id
where l.id == leave_id
select new LeavehistoryModel
{
leave_id = l.id,
from_date = l.from_date,
to_date = l.to_date,
no_of_days = l.no_of_days,
leave_status = ls.leave_status,
first_name = e.first_name,
employee_id = e.id
};
return(query.FirstOrDefault());
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine(e.GetBaseException());
throw e;
}
finally
{
datacontext.Dispose();
}
}
public static List <IncometaxModel> GetIncometaxListByEmpId(int employee_id)//e_id employee_id
{
EMSEntities datacontext = new EMSEntities();
try
{
var query = from tax in datacontext.Incometaxes
where tax.emp_id == employee_id
select new IncometaxModel
{
id = tax.id,
emp_id = tax.emp_id,
from_date = tax.from_date,
to_date = tax.to_date,
income_tax = tax.income_tax,
is_active = tax.is_active,
notes = tax.notes
};
return(query.ToList());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
Debug.WriteLine(exception.GetBaseException());
throw exception;
}
finally
{
datacontext.Dispose();
}
}
public IHttpActionResult GetMarkTotalById(int id)
{
MarkTotalViewModel mks = null;
using (var ctx = new EMSEntities())
{
mks = ctx.MARKTOTALs
.Where(s => s.TRNNO == id)
.Select(s => new MarkTotalViewModel()
{
TRNNO = s.TRNNO,
CLASS_TRNNO = s.CLASS_TRNNO,
EXAM_TRNNO = s.EXAM_TRNNO,
GRPMST_TRNNO = s.GRPMST_TRNNO,
MDT = s.MDT
}).FirstOrDefault <MarkTotalViewModel>();
}
if (mks == null)
{
return(NotFound());
}
return(Ok(mks));
}
protected void Button2_Click(object sender, EventArgs e)
{
EMSEntities Context=new EMSEntities();
EntittyFramewordWebApplication4.StudentDetail stud1=new StudentDetail();
stud1.id = "67";
stud1.name = "ziauddin";
stud1.address = "chittagong";
stud1.marks = 88;
stud1.section = "C";
Context.StudentDetails.Add(stud1);
Context.SaveChanges();
Label1.Text = "added";
}
protected void Button3_Click(object sender, EventArgs e)
{
EMSEntities dbcontext = new EMSEntities();
EntittyFramewordWebApplication4.StudentDetail stud1 = new StudentDetail();
try
{
string idd = TextBox2.Text;
stud1 = dbcontext.StudentDetails.FirstOrDefault(xx => xx.id == idd);
stud1.marks = int.Parse(TextBox6.Text);
stud1.section = TextBox5.Text;
dbcontext.SaveChanges();
Label1.Text = "updated successfull";
}
catch(Exception e1)
{
Label1.Text = "not found";
}
}
protected void Button1_Click(object sender, EventArgs e)
{
int m =int.Parse(TextBox1.Text);
EMSEntities dbcontext=new EMSEntities();
var stud = from dd in dbcontext.StudentDetails
where
dd.marks > m && dd.section == "A"
select dd;
//string s = "Output";
DataTable dt=new DataTable();
dt.Columns.Add("id");
dt.Columns.Add("name");
dt.Columns.Add("address");
dt.Columns.Add("section");
dt.Columns.Add("marks");
foreach (var student in stud)
{
DataRow r = dt.NewRow();
r[0] = student.id;
r[1] = student.name;
r[2] = student.address;
r[3] = student.section;
r[4] = student.marks;
//r[0] = "id";
//r[1] = "name";
//r[2] = "address";
//r[3] = "section";
//r[4] = 4;
dt.Rows.Add(r);
//s = s + "ID:" + student.id;
//s = s + "name:" + student.name;
//s = s + "Section::" + student.section;
}
GridView1.DataSource = dt;
GridView1.DataBind();
//Label1.Text = s;
}
protected void Button5_Click(object sender, EventArgs e)
{
string id = TextBox2.Text;
EMSEntities dbcontext = new EMSEntities();
var stud = from dd in dbcontext.StudentDetails
where dd.id==id select dd;
bool flag = false;
foreach (var student in stud)
{
TextBox3.Text = student.name;
TextBox4.Text = student.address;
TextBox5.Text = student.section;
TextBox6.Text = student.marks.ToString();
flag = true;
}
if (flag == false)
Label1.Text = "not found";
else
{
Label1.Text = "found";
}
}
protected void Button7_Click(object sender, EventArgs e)
{
//EMSEntities dbcontext = new EMSEntities();
//emp e1 = new emp();
//e1.empId = int.Parse(TextBox2.Text);
//e1.name = TextBox4.Text;
//e1.deptno = int.Parse(TextBox5.Text);
//e1.job = TextBox5.Text;
//e1.salary = int.Parse(TextBox6.Text);
//dbcontext.emps.Add(e1);
//dbcontext.SaveChanges();
//Label1.Text = "added from emp table";
EMSEntities dbcontext = new EMSEntities();
emp e1 = new emp();
e1.empId = 786;
e1.name = "pabel";
e1.deptno = 56;
e1.job = "hr";
e1.salary = 6700;
dbcontext.emps.Add(e1);
dbcontext.SaveChanges();
Label1.Text = "added from emp table";
}