//
// GET: /AttProcessors/Create
public ActionResult Create()
{
TAS2013Entities db = new TAS2013Entities();
User LoggedInUser = Session["LoggedUser"] as User;
QueryBuilder qb = new QueryBuilder();
String query = qb.QueryForCompanyViewLinq(LoggedInUser);
ViewBag.PeriodTag = new SelectList(new List<SelectListItem>
{
new SelectListItem { Selected = true, Text = "Daily", Value = "D"},
new SelectListItem { Selected = false, Text = "Monthly", Value = "M"},
new SelectListItem { Selected = false, Text = "Summary", Value = "S"},
}, "Value" , "Text",1);
ViewBag.CriteriaID = new SelectList(new List<SelectListItem>
{
new SelectListItem { Selected = true, Text = "Company", Value = "C"},
new SelectListItem { Selected = false, Text = "Location", Value = "L"},
}, "Value", "Text", 1);
ViewBag.ProcessCats = new SelectList(new List<SelectListItem>
{
new SelectListItem { Selected = true, Text = "Yes", Value = "1"},
new SelectListItem { Selected = false, Text = "No", Value = "0"},
}, "Value", "Text", 1);
ViewBag.CompanyID = new SelectList(db.Companies.Where(query).OrderBy(s=>s.CompName), "CompID", "CompName");
query = qb.QueryForLocationTableSegerationForLinq(LoggedInUser);
ViewBag.LocationID = new SelectList(db.Locations.Where(query).OrderBy(s=>s.LocName), "LocID", "LocName");
ViewBag.CatID = new SelectList(db.Categories.OrderBy(s=>s.CatName), "CatID", "CatName");
return View();
}
public string CheckForUserRole(User user)
{
string val = "";
using (var ctx = new TAS2013Entities())
{
string criteria = ctx.UserAccesses.Where(aa => aa.UserID == user.UserID).FirstOrDefault().Criteria;
switch (criteria.Trim())
{
case "Z":
val = "Zone";
break;
case "R":
val="Region";
break;
case "C":
val = "City";
break;
case "L":
val = "Location";
break;
case "S":
val = "SuperUser";
break;
}
ctx.Dispose();
}
return val;
}
public ActionResult Create()
{
var _wings = new List<Division>();
using (TAS2013Entities context = new TAS2013Entities())
{
_wings = context.Divisions.ToList();
}
ViewBag.CompanyID = new SelectList(db.Companies.OrderBy(aa=>aa.CompName), "CompID", "CompName");
ViewBag.CrewID = new SelectList(db.Crews.OrderBy(aa => aa.CrewName), "CrewID", "CrewName");
ViewBag.DesigID = new SelectList(db.Designations.OrderBy(aa => aa.DesignationName), "DesignationID", "DesignationName");
ViewBag.GradeID = new SelectList(db.Grades.OrderBy(aa => aa.GradeName), "GradeID", "GradeName");
ViewBag.JobID = new SelectList(db.JobTitles.OrderBy(aa => aa.JobTitle1), "JobID", "JobTitle1");
//ViewBag.LocID = new SelectList(db.Locations, "LocID", "LocName");
ViewBag.SecID = new SelectList(db.Sections.OrderBy(aa => aa.SectionName), "SectionID", "SectionName");
ViewBag.ShiftID = new SelectList(db.Shifts.OrderBy(aa => aa.ShiftName), "ShiftID", "ShiftName");
ViewBag.TypeID = new SelectList(db.EmpTypes.OrderBy(aa => aa.TypeName), "TypeID", "TypeName");
ViewBag.EmpID = new SelectList(db.EmpFaces, "EmpID", "Face1");
ViewBag.EmpID = new SelectList(db.EmpFps.OrderBy(aa => aa.Fp1), "EmpID", "Fp1");
ViewBag.CatID = new SelectList(db.Categories.OrderBy(aa => aa.CatName), "CatID", "CatName");
ViewBag.DeptID = new SelectList(db.Departments.OrderBy(aa => aa.DeptName), "DeptID", "DeptName");
ViewBag.CityID = new SelectList(db.Cities.OrderBy(aa => aa.CityName), "CityID", "CityName");
ViewBag.ZoneID = new SelectList(db.Zones.OrderBy(aa => aa.ZoneName), "ZoneID", "ZoneName");
ViewBag.RegionID = new SelectList(db.Regions.OrderBy(aa => aa.RegionName), "RegionID", "RegionName");
ViewBag.PostedAsID = new SelectList(db.Designations.OrderBy(aa => aa.DesignationName), "DesignationID", "DesignationName");
return View();
}
public void AddHalfLeaveToLeaveData(LvApplication lvappl)
{
DateTime datetime = new DateTime();
datetime = lvappl.FromDate;
string _EmpDate = lvappl.EmpID + datetime.Date.ToString("yyMMdd");
LvData _LVData = new LvData();
_LVData.EmpID = lvappl.EmpID;
_LVData.EmpDate = _EmpDate;
_LVData.Remarks = lvappl.LvReason;
_LVData.HalfLeave = true;
_LVData.LvID = lvappl.LvID;
_LVData.AttDate = datetime.Date;
_LVData.LvCode = lvappl.LvType;
_LVData.FirstHalf = lvappl.FirstHalf;
try
{
using (var db = new TAS2013Entities())
{
db.LvDatas.Add(_LVData);
db.SaveChanges();
}
}
catch (Exception ex)
{
}
// Balance Leaves from Emp Table
BalanceLeaves(lvappl);
}
public int UploadImageInDataBase(HttpPostedFileBase file, string _empNo)
{
using (var context = new TAS2013Entities())
{
List<EmpPhoto> _empPhotoList = new List<EmpPhoto>();
EmpPhoto _EmpPhoto = new EmpPhoto();
Emp _Emp = new Emp();
int empPhotoID = 0;
_Emp = context.Emps.First(aa => aa.EmpNo == _empNo);
_empPhotoList = context.EmpPhotoes.Where(aa => aa.EmpID == _Emp.EmpID).ToList();
_EmpPhoto.EmpPic = ConvertToBytes(file);
if (_empPhotoList.Count > 0)
{
//Update Existing Image
_EmpPhoto.EmpID = _empPhotoList.FirstOrDefault().EmpID;
_EmpPhoto.PhotoID = _empPhotoList.FirstOrDefault().PhotoID;
}
else
{
//Add New Image
_EmpPhoto.EmpID = _Emp.EmpID;
context.EmpPhotoes.Add(_EmpPhoto);
}
try
{
empPhotoID = _EmpPhoto.PhotoID;
context.SaveChanges();
return empPhotoID;
}
catch (Exception ex)
{
return empPhotoID;
}
}
}
public ActionResult GetBestCriteria(string CriteriaValue)
{
List<DailySummary> BestCriteria = new List<DailySummary>();
using (TAS2013Entities dc = new TAS2013Entities())
{
DateTime To = (DateTime)dc.DailySummaries.Max(x => x.Date);
DateTime From = To.AddDays(-20);
var GetSummaries = dc.DailySummaries.Where(aa => aa.Criteria == CriteriaValue && (aa.Date >= From && aa.Date <= To)).ToList();
var DistinctCriterias = GetSummaries.Select(aa => new { aa.CriteriaValue }).Distinct().ToList();
foreach (var distinctcriteria in DistinctCriterias)
{
DailySummary gatherinfo = new DailySummary();
List<DailySummary> intermediate = GetSummaries.Where(aa => aa.CriteriaValue == distinctcriteria.CriteriaValue).ToList();
gatherinfo = InitializeDailySummaryValues(intermediate);
foreach (DailySummary inter in intermediate)
{
gatherinfo = AddValues(gatherinfo, inter);
}
BestCriteria.Add(gatherinfo);
}
}
Console.Write(CriteriaValue);
return Json(BestCriteria, JsonRequestBehavior.AllowGet);
}
private void LoadSession()
{
using (TAS2013Entities dc = new TAS2013Entities())
{
var v = dc.Users.Where(aa => aa.UserName == "wms.ffl").FirstOrDefault();
if (v != null)
{
Session["MDevice"] = "0";
Session["MHR"] = "0";
Session["MDevice"] = "0";
Session["MLeave"] = "0";
Session["MEditAtt"] = "0";
Session["MUser"] = "******";
Session["LogedUserFullname"] = "";
Session["UserCompany"] = "";
Session["MRDailyAtt"] = "0";
Session["MRLeave"] = "0";
Session["MRMonthly"] = "0";
Session["MRAudit"] = "0";
Session["MRManualEditAtt"] = "0";
Session["MREmployee"] = "0";
Session["MRDetail"] = "0";
Session["MRSummary"] = "0";
Session["LogedUserID"] = v.UserID.ToString();
Session["LogedUserFullname"] = v.UserName;
Session["LoggedUser"] = v;
Session["UserCompany"] = v.CompanyID.ToString();
if (v.MHR == true)
Session["MHR"] = "1";
if (v.MDevice == true)
Session["MDevice"] = "1";
if (v.MLeave == true)
Session["MLeave"] = "1";
if (v.MEditAtt == true)
Session["MEditAtt"] = "1";
if (v.MUser == true)
Session["MUser"] = "******";
if (v.MRDailyAtt == true)
Session["MRDailyAtt"] = "1";
if (v.MRLeave == true)
Session["MRLeave"] = "1";
if (v.MRMonthly == true)
Session["MRMonthly"] = "1";
if (v.MRAudit == true)
Session["MRAudit"] = "1";
if (v.MRManualEditAtt == true)
Session["MRManualEditAtt"] = "1";
if (v.MREmployee == true)
Session["MREmployee"] = "1";
if (v.MRDetail == true)
Session["MRDetail"] = "1";
if (v.MRSummary == true)
Session["MRSummary"] = "1";
if (v.MRoster == true)
Session["MRoster"] = "1";
}
}
}
public string MakeCustomizeQuery(User _user)
{
string query = " where ";
string subQuery = "";
string subQueryLoc = "";
List<string> _Criteria = new List<string>();
List<string> _CriteriaForOr = new List<string>();
List<string> _CriteriaForOrLoc = new List<string>();
TAS2013Entities db = new TAS2013Entities();
List<UserAccess> ulocs = new List<UserAccess>();
ulocs = db.UserAccesses.Where(aa => aa.UserID == _user.UserID).ToList();
foreach (var uloc in ulocs)
{
if(uloc.Criteria.Trim() == "Z")
_CriteriaForOrLoc.Add(" ZoneID = " + uloc.CriteriaData + " ");
if (uloc.Criteria.Trim() == "R")
_CriteriaForOrLoc.Add(" RegionID = " + uloc.CriteriaData + " ");
if (uloc.Criteria.Trim() == "C")
_CriteriaForOrLoc.Add(" CityID = " + uloc.CriteriaData + " ");
if (uloc.Criteria.Trim() == "L")
_CriteriaForOrLoc.Add(" LocID = " + uloc.CriteriaData + " ");
if (uloc.Criteria.Trim() == "S")
query = "";
if (uloc.Criteria.Trim() == "")
{
query = "";
return query;
}
}
for (int i = 0; i < _CriteriaForOrLoc.Count - 1; i++)
{
subQueryLoc = subQueryLoc + _CriteriaForOrLoc[i] + " or ";
}
if(_CriteriaForOrLoc.Count>0)
subQueryLoc = " ( " + subQueryLoc + _CriteriaForOrLoc[_CriteriaForOrLoc.Count - 1] + " ) ";
//subQuery = " ( ";
//for (int i = 0; i < _CriteriaForOr.Count - 1; i++)
//{
// subQuery = subQuery + _CriteriaForOr[i] + " or ";
//} if (_CriteriaForOr.Count > 0)
//subQuery = subQuery + _CriteriaForOr[_CriteriaForOr.Count - 1];
//subQuery = subQuery + " ) ";
query = query + subQueryLoc;
return query;
}
protected void btnlogin_Click(object sender, EventArgs e)
{
TAS2013Entities context = new TAS2013Entities();
//lbError.Text = "";
List<User> _User = context.Users.ToList();
//Request.Cookies[user]
if (tbUserName.Text != "" && tbPassword.Text != "")
{
foreach (var item in _User)
{
if (item.UserName.ToUpper() == tbUserName.Text.ToUpper())
{
if (item.Password.ToUpper() == tbPassword.Text.ToUpper())
{
if (item.RoleID == 2 || item.RoleID == 3)
{
Response.Redirect("/WMS/Reports/ReportHome.aspx");
Session["RoleID"] = "Rpt";
//SaveAuditLogEntry(item.UserID, item.UserName);
}
else
{
Response.Redirect("~/Home");
if (item.RoleID == 4)
{
Session["RoleID"] = "Entry";
}
//SaveAuditLogEntry(item.UserID, item.UserName);
}
if (item.RoleID == 5)
{
Session["RoleID"] = "Admin";
}
}
else
{
//lbError.Text = "*Password doesnot match";
}
}
else
{
//lbError.Text = "*Username is not valid";
}
}
}
}
public void AddHalfLeaveToAttData(LvApplication lvappl)
{
DateTime datetime = new DateTime();
datetime = lvappl.FromDate;
string _EmpDate = lvappl.EmpID + datetime.Date.ToString("yyMMdd");
using (var db = new TAS2013Entities())
{
if (db.AttProcesses.Where(aa => aa.ProcessDate == datetime).Count() > 0)
{
AttData _EmpAttData = new AttData();
_EmpAttData = db.AttDatas.First(aa => aa.EmpDate == _EmpDate);
if (lvappl.LvType == "A")//Casual Leave
_EmpAttData.Remarks = _EmpAttData.Remarks+"[H-CL]";
if (lvappl.LvType == "B")//Anual Leave
_EmpAttData.Remarks = _EmpAttData.Remarks+"[H-AL]";
if (lvappl.LvType == "C")//Sick Leave
_EmpAttData.Remarks = _EmpAttData.Remarks+"[H-SL]";
if (_EmpAttData.Remarks.Contains("[Absent]"))
_EmpAttData.Remarks.Replace("[Abesnt]", "");
if (_EmpAttData.Remarks.Contains("[EO]"))
_EmpAttData.Remarks.Replace("[EO]", "-");
if (_EmpAttData.Remarks.Contains("[LI]"))
_EmpAttData.Remarks.Replace("[LI]", "");
_EmpAttData.StatusLeave = true;
if (lvappl.FirstHalf == true)
{
_EmpAttData.LateIn = 0;
_EmpAttData.StatusLI = false;
}
else
{
_EmpAttData.StatusEO = false;
_EmpAttData.EarlyOut = 0;
}
//_EmpAttData.statushl
db.SaveChanges();
}
}
}
public ActionResult Create()
{
var _wings = new List<Division>();
using (TAS2013Entities context = new TAS2013Entities())
{
_wings = context.Divisions.ToList();
}
ViewBag.CrewID = new SelectList(db.Crews.OrderBy(s => s.CrewName), "CrewID", "CrewName");
ViewBag.DesigID = new SelectList(db.Designations.OrderBy(s => s.DesignationName), "DesignationID", "DesignationName");
ViewBag.GradeID = new SelectList(db.Grades.OrderBy(s=>s.GradeName), "GradeID", "GradeName");
ViewBag.JobID = new SelectList(db.JobTitles.OrderBy(s=>s.JobTitle1), "JobID", "JobTitle1");
ViewBag.LocID = new SelectList(db.Locations.OrderBy(s=>s.LocName), "LocID", "LocName");
ViewBag.SecID = new SelectList(db.Sections.OrderBy(s=>s.SectionName), "SectionID", "SectionName");
ViewBag.ShiftID = new SelectList(db.Shifts.OrderBy(s=>s.ShiftName), "ShiftID", "ShiftName");
ViewBag.TypeID = new SelectList(db.EmpTypes.OrderBy(s=>s.TypeName), "TypeID", "TypeName");
ViewBag.EmpID = new SelectList(db.EmpFaces.OrderBy(s=>s.Face1), "EmpID", "Face1");
ViewBag.EmpID = new SelectList(db.EmpFps.OrderBy(s=>s.Fp1), "EmpID", "Fp1");
ViewBag.CatID = new SelectList(db.Categories.OrderBy(s=>s.CatName), "CatID", "CatName");
ViewBag.DeptID = new SelectList(db.Departments.OrderBy(s=>s.DeptName), "DeptID", "DeptName");
return View();
}
public ActionResult GetCriteriaNames(string Criteria)
{
List<CriteriaFoAngular> GetValueNames = new List<CriteriaFoAngular>();
using (TAS2013Entities dc = new TAS2013Entities())
{
FiltersModel fm = Session["FiltersModel"] as FiltersModel;
if (Criteria == "C")
{
foreach (var company in fm.CompanyFilter)
GetValueNames.Add(new CriteriaFoAngular("" + company.ID, company.FilterName));
}
if (Criteria == "L")
{
foreach (var location in fm.LocationFilter)
GetValueNames.Add(new CriteriaFoAngular("" + location.ID, location.FilterName));
}
if (Criteria == "S")
{
foreach (var shift in fm.ShiftFilter)
GetValueNames.Add(new CriteriaFoAngular("" + shift.ID, shift.FilterName));
}
if (Criteria == "E")
{
foreach (var section in fm.SectionFilter)
GetValueNames.Add(new CriteriaFoAngular("" + section.ID, section.FilterName));
}
if (Criteria == "D")
{
foreach (var department in fm.DepartmentFilter)
GetValueNames.Add(new CriteriaFoAngular("" + department.ID, department.FilterName));
}
//var getSummaries = dc.DailySummaries.Where(aa => aa.Criteria == Criteria).Select(aa => new { aa.CriteriaValue, aa.CriteriaName }).Distinct().ToList();
return Json(
GetValueNames.ToArray()
, JsonRequestBehavior.AllowGet);
}
}
public int UploadImageInDataBase(HttpPostedFileBase file, Emp _Emp)
{
using (var context = new TAS2013Entities())
{
List<EmpPhoto> _empPhotoList = new List<EmpPhoto>();
EmpPhoto _EmpPhoto = new EmpPhoto();
int empPhotoID = 0;
_empPhotoList = context.EmpPhotoes.Where(aa => aa.EmpID == _Emp.EmpID).ToList();
_EmpPhoto.EmpPic = ConvertToBytes(file);
if (_empPhotoList.Count > 0)
{
_EmpPhoto.PhotoID = _empPhotoList.FirstOrDefault().PhotoID;
_empPhotoList.FirstOrDefault().EmpPic = _EmpPhoto.EmpPic;
context.EmpPhotoes.Attach(_empPhotoList.FirstOrDefault());
context.Entry(_empPhotoList.FirstOrDefault()).State = EntityState.Modified;
context.SaveChanges();
}
else
{
//Add New Image
_EmpPhoto.EmpID = _Emp.EmpID;
context.EmpPhotoes.Add(_EmpPhoto);
}
try
{
empPhotoID = _EmpPhoto.PhotoID;
context.SaveChanges();
return empPhotoID;
}
catch (Exception ex)
{
return empPhotoID;
}
}
}
internal string QueryForShiftForLinq(User LoggedInUser)
{
TAS2013Entities db = new TAS2013Entities();
List<UserLocation> ulocs = new List<UserLocation>();
List<string> _CriteriaForOrLoc = new List<string>();
ulocs = db.UserLocations.Where(aa => aa.UserID == LoggedInUser.UserID).ToList();
string query = "";
foreach (var uloc in ulocs)
{
_CriteriaForOrLoc.Add(" LocationID = " + uloc.LocationID + " ");
}
for (int i = 0; i < _CriteriaForOrLoc.Count - 1; i++)
{
query = query + _CriteriaForOrLoc[i] + " or ";
}
query = query + _CriteriaForOrLoc[_CriteriaForOrLoc.Count - 1];
return query;
}
/// <summary>
/// This function takes FiltersModel and an empty list of AttDeptSummary as input and
/// gives a populated AttDeptSummary as output. If there is any dept selected in the filter
/// it will iterate through all the departments if there is no dept selected it will try to
/// find all the depts of the company.
/// </summary>
/// <param name="fm"> FiltersModel </param>
/// <param name="attDeptList">Empty List of AttDeptSummary</param>
/// <returns>Populated List of AttDeptSummary</returns>
public List<AttDeptSummary> GetListForAttDepartmentsSummary(FiltersModel fm, string _dateFrom,string _dateTo)
{
List<AttDeptSummary> attDeptList = new List<AttDeptSummary>();
TAS2013Entities db = new TAS2013Entities();
//To get the query from the db
QueryBuilder qb = new QueryBuilder();
//Get the Department filters
List<FiltersAttributes> departments = new List<FiltersAttributes>();
departments = fm.DepartmentFilter;
//if more then 0 it means some departments are selected now we iterate over them and see
//how many employee does one dept has. The table empView is the most suitable db for this case.
if (departments.Count > 0)
{
//do nothing still cater for it in future there might be a need
}
//if there is no department list in the filter then first get the company's department and then do the same as above.
else
{
foreach (var comp in fm.CompanyFilter)
{
int compID = Convert.ToInt16(comp.ID);
List<Department> depts = db.Departments.Where(aa => aa.CompanyID == compID).ToList();
foreach (var dept in depts)
departments.Add(new FiltersAttributes() { ID=dept.DeptID + "", FilterName=dept.DeptName });
}
}
//NOw run the departments if they are from the department filter or from the company itself
foreach (var dept in departments)
{
DataTable dt = qb.GetValuesfromDB("select * from EmpView where DeptID=" + dept.ID);
List<EmpView> EmView = dt.ToList<EmpView>();
//2015-01-24
foreach (DateTime day in EachDay(Convert.ToDateTime(_dateFrom), Convert.ToDateTime(_dateTo)))
{
AttDeptSummary singleInstance = new AttDeptSummary();
singleInstance.Department = dept.FilterName;
singleInstance.TotalStrength = EmView.Count();
singleInstance.Total = singleInstance.TotalStrength;
singleInstance.Location = EmView.FirstOrDefault().LocName;
singleInstance.Section = EmView.FirstOrDefault().SectionName;
singleInstance.Company = EmView.FirstOrDefault().CompName;
singleInstance.CardSwapped = 0;
singleInstance.Absent = 0;
singleInstance.OnLeave = 0;
string dynamicQueryForEmps = GetDynamicQueryForEmps(EmView);
string queryForCardSwapped = "select * from AttData where (TimeIn is not null AND AttDate = '" + day + "') AND ";
string queryForAbsent = "select * from AttData where (StatusAB = 1 AND AttDate = '" + day + "') AND ";
string queryOnLeave = "select * from AttData where (StatusHL = 1 OR StatusLeave =1 AND StatusSL=1 AND AttDate = '" + day + "') AND ";
queryForCardSwapped = queryForCardSwapped + dynamicQueryForEmps;
singleInstance.CardSwapped = qb.GetValuesfromDB(queryForCardSwapped).ToList<AttData>().Count();
queryForAbsent = queryForAbsent + dynamicQueryForEmps;
singleInstance.Absent = qb.GetValuesfromDB(queryForAbsent).ToList<AttData>().Count(); queryOnLeave = queryOnLeave + dynamicQueryForEmps;
singleInstance.OnLeave = qb.GetValuesfromDB(queryOnLeave).ToList<AttData>().Count();
//foreach (var emp in EmView)
//{
//s= singleInstance.CardSwapped + db.AttDatas.Where(aa => aa.TimeIn != null && aa.AttDate == day && aa.EmpID == emp.EmpID).Count();
//singleInstance.Absent = singleInstance.Absent+ db.AttDatas.Where(aa => aa.StatusAB == true && aa.AttDate == day && aa.EmpID == emp.EmpID).Count();
//singleInstance.OnLeave = singleInstance.OnLeave + db.AttDatas.Where(aa => (aa.StatusHL == true || aa.StatusLeave == true || aa.StatusSL == true) && aa.AttDate == day && aa.EmpID == emp.EmpID).Count();
//}
singleInstance.date = day;
attDeptList.Add(singleInstance);
}
}
return attDeptList;
}
internal string QueryForEmployeeReports(User LoggedInUser)
{
string query = " where ";
TAS2013Entities db = new TAS2013Entities();
List<UserAccess> uAcc = new List<UserAccess>();
uAcc = db.UserAccesses.Where(aa => aa.UserID == LoggedInUser.UserID).ToList();
List<Region> regions = db.Regions.ToList();
List<City> cities = db.Cities.ToList();
List<Location> locs = db.Locations.ToList();
List<string> queryList = new List<string>();
foreach (var access in uAcc)
{
switch (LoggedInUser.RoleID)
{
case 1://Super ADmin
query = "";
break;
case 4://Zone
queryList.Add(" ZoneID =" + access.CriteriaData.ToString());
break;
case 5://REgion
queryList.Add(" RegionID =" + access.CriteriaData.ToString());
break;
case 6://City
queryList.Add(" CityID =" + access.CriteriaData.ToString());
break;
case 7://Location
queryList.Add(" LocID =" + access.CriteriaData.ToString());
break;
}
}
if (queryList.Count == 1)
{
query = query + queryList[0];
}
else if (queryList.Count > 1)
{
for (int i = 0; i < queryList.Count - 1; i++)
{
query = query + queryList[i] + " or ";
}
query = query + queryList[queryList.Count - 1];
}
return query;
}
internal string QueryForReportsCity(User LoggedInUser, string p)
{
string query = " where ";
TAS2013Entities db = new TAS2013Entities();
List<UserAccess> uAcc = new List<UserAccess>();
uAcc = db.UserAccesses.Where(aa => aa.UserID == LoggedInUser.UserID).ToList();
List<Region> regions = db.Regions.ToList();
List<City> cities = db.Cities.ToList();
List<Location> locs = db.Locations.ToList();
List<string> queryList = new List<string>();
foreach (var access in uAcc)
{
switch (LoggedInUser.RoleID)
{
case 1://Super ADmin
query = "";
break;
case 4://Zone
List<City> city = db.Cities.Where(aa => aa.Region.ZoneID== access.CriteriaData).ToList();
foreach (var c in city)
{
queryList.Add(" CityID =" + c.CityID);
}
break;
case 5://REgion
city = db.Cities.Where(aa => aa.RegionID == access.CriteriaData).ToList();
foreach (var c in city)
{
queryList.Add(" CityID =" + c.CityID);
}
break;
case 6://City
string cityID = cities.Where(aa => aa.CityID == access.CriteriaData).FirstOrDefault().CityID.ToString();
queryList.Add(" CityID =" + cityID);
break;
case 7://Location
string cityIDForLoc = locs.Where(aa => aa.LocID == access.CriteriaData).FirstOrDefault().CityID.ToString();
queryList.Add(" CityID =" + cityIDForLoc);
break;
}
}
if (queryList.Count == 1)
{
query = query + queryList[0];
}
else if (queryList.Count > 1)
{
for (int i = 0; i < queryList.Count - 1; i++)
{
query = query + queryList[i] + " or ";
}
query = query + queryList[queryList.Count - 1];
}
return query;
}
public string QueryForLocationTableSegeration(User _user)
{
TAS2013Entities db = new TAS2013Entities();
List<UserLocation> ulocs = new List<UserLocation>();
List<string> _CriteriaForOrLoc = new List<string>();
ulocs = db.UserLocations.Where(aa => aa.UserID == _user.UserID).ToList();
string query = " where ";
foreach (var uloc in ulocs)
{
_CriteriaForOrLoc.Add(" LocID = " + uloc.LocationID + " ");
}
for (int i = 0; i < _CriteriaForOrLoc.Count - 1; i++)
{
query = query + _CriteriaForOrLoc[i] + " or ";
}
query = query + _CriteriaForOrLoc[_CriteriaForOrLoc.Count - 1];
return query;
}
public string QueryForUserAccess(User _User, string view)
{
string role="";
string query = "";
using (var context = new TAS2013Entities())
{
role = context.UserRoles.Where(aa => aa.RoleID == _User.RoleID).FirstOrDefault().RoleName;
}
switch (view)
{
case "Region": query= UserAcccessGetRegion(_User,role);
return query;
case "Zone": query = UserAcccessGetZone(_User, role);
return query;
case "Location": query = UserAcccessGetLocation(_User, role);
return query;
case "City": query = UserAccessGetCity(_User, role);
return query;
case "SuperUser": query = "CompanyID >0";
return query;
}
return query;
}
public string MakeCustomizeQueryForUserAccess(User _user)
{
TAS2013Entities db = new TAS2013Entities();
string query = "where";
List<UserAccess> uAcc = new List<UserAccess>();
uAcc = db.UserAccesses.Where(aa => aa.UserID == _user.UserID).ToList();
foreach (var access in uAcc)
{
if (access.Criteria.Contains("L"))
query = query + " LocID = " + access.CriteriaData + " ";
if (access.Criteria.Contains("S"))
query = query + " LocID>0";
}
return query;
}
public string MakeCustomizeQuery(User _user)
{
string RoleQuery = "";
string CatQuery = "";
TAS2013Entities db = new TAS2013Entities();
List<UserRoleData> userRoleData = new List<UserRoleData>();
List<string> UserRoleString = new List<string>();
List<string> CategoryUser = new List<string>();
CategoryUser.Add(" where (CatID=1 ");
if (_user.ViewContractual == true)
{
CategoryUser.Add(" CatID = 4 ");
}
if (_user.ViewPermanentMgm == true)
{
CategoryUser.Add(" CatID = 2 ");
}
if (_user.ViewPermanentStaff == true)
{
CategoryUser.Add(" CatID = 3 ");
}
userRoleData = db.UserRoleDatas.Where(aa => aa.RoleUserID == _user.UserID).ToList();
switch (_user.UserRoleD)
{
case "A"://Admin
break;
case "C"://City
foreach (var urd in userRoleData)
{
UserRoleString.Add(" CityID = " + urd.RoleDataValue + " ");
}
break;
case "D"://Department
foreach (var urd in userRoleData)
{
UserRoleString.Add(" DeptID = " + urd.RoleDataValue + " ");
}
break;
case "E"://Single Employee
foreach (var urd in userRoleData)
{
UserRoleString.Add(" EmpID = " + urd.RoleDataValue + " ");
}
break;
case "L"://Location
foreach (var urd in userRoleData)
{
UserRoleString.Add(" LocID = " + urd.RoleDataValue + " ");
}
break;
case "R"://Region
foreach (var urd in userRoleData)
{
UserRoleString.Add(" RegionID = " + urd.RoleDataValue + " ");
}
break;
case "S"://Section
foreach (var urd in userRoleData)
{
UserRoleString.Add(" SecID = " + urd.RoleDataValue + " ");
}
break;
case "V"://Division
foreach (var urd in userRoleData)
{
UserRoleString.Add(" DivID = " + urd.RoleDataValue + " ");
}
break;
case "W"://Crew
foreach (var urd in userRoleData)
{
UserRoleString.Add(" CrewID = " + urd.RoleDataValue + " ");
}
break;
}
if (UserRoleString.Count == 1)
{
RoleQuery = " and (" + RoleQuery + UserRoleString[0] + " ) ";
}
else if(UserRoleString.Count>1)
{
RoleQuery = RoleQuery + " and ( ";
for (int i = 0; i < UserRoleString.Count - 1; i++)
{
RoleQuery = RoleQuery + UserRoleString[i] + " or ";
}
RoleQuery = RoleQuery + UserRoleString[UserRoleString.Count - 1] + " ) ";
}
if (CategoryUser.Count == 1)
CatQuery = CatQuery + CategoryUser[0]+" ) ";
else if(CategoryUser.Count>1)
{
for (int i = 0; i < CategoryUser.Count-1; i++)
{
CatQuery = CatQuery + CategoryUser[i] + " or ";
}
CatQuery = CatQuery + CategoryUser[CategoryUser.Count - 1] + " ) ";
}
return CatQuery + RoleQuery;
}
public ActionResult Login(User u)
{
try
{
if (ModelState.IsValid) // this is check validity
{
using (TAS2013Entities dc = new TAS2013Entities())
{
var v = dc.Users.Where(a => a.UserName.Equals(u.UserName) && a.Password==u.Password && a.Status == true).FirstOrDefault();
if (v != null)
{
Session["MDevice"] = "0";
Session["MHR"] = "0";
Session["MDevice"] = "0";
Session["MLeave"] = "0";
Session["MEditAtt"] = "0";
Session["MUser"] = "******";
Session["LogedUserFullname"] = "";
Session["UserCompany"] = "";
Session["MRDailyAtt"] = "0";
Session["MRLeave"] = "0";
Session["MRMonthly"] = "0";
Session["MRAudit"] = "0";
Session["MRManualEditAtt"] = "0";
Session["MREmployee"] = "0";
Session["MRDetail"] = "0";
Session["MRSummary"] = "0";
Session["LogedUserID"] = v.UserID.ToString();
Session["LogedUserFullname"] = v.UserName;
Session["LoggedUser"] = v;
Session["UserCompany"] = v.CompanyID.ToString();
if (v.MHR == true)
Session["MHR"] = "1";
if (v.MDevice == true)
Session["MDevice"] = "1";
if (v.MLeave == true)
Session["MLeave"] = "1";
if (v.MEditAtt == true)
Session["MEditAtt"] = "1";
if (v.MUser == true)
Session["MUser"] = "******";
if (v.MRDailyAtt == true)
Session["MRDailyAtt"] = "1";
if (v.MRLeave == true)
Session["MRLeave"] = "1";
if (v.MRMonthly == true)
Session["MRMonthly"] = "1";
if (v.MRAudit == true)
Session["MRAudit"] = "1";
if (v.MRManualEditAtt == true)
Session["MRManualEditAtt"] = "1";
if (v.MProcess == true)
Session["MProcess"] = "1";
if (v.MREmployee == true)
Session["MREmployee"] = "1";
if (v.MRDetail == true)
Session["MRDetail"] = "1";
if (v.MRSummary == true)
Session["MRSummary"] = "1";
else
Session["MRSummary"] = "0";
if (v.MRoster == true)
Session["MRoster"] = "1";
if(v.MRGraph==true)
Session["MGraph"] = "1";
else
Session["MGraph"] = "0";
HelperClass.MyHelper.SaveAuditLog(v.UserID, (byte)MyEnums.FormName.LogIn, (byte)MyEnums.Operation.LogIn, DateTime.Now);
return RedirectToAction("AfterLogin");
}
else
{
int LoginCount = 0;
bool successOnConversion = int.TryParse(Session["LoginCount"] as string, out LoginCount);
if (successOnConversion == true)
{
LoginCount++;
Session["LoginCount"] = LoginCount + "";
}
else
{
Session["LoginCount"] = "1";
}
}
}
}
return RedirectToAction("index");
}
catch (Exception ex)
{
ViewBag.Message = "There seems to be a problem with the network. Please contact your network administrator";
return RedirectToAction("Index");
}
}
public ActionResult Create([Bind(Include="EmpID,EmpNo,EmpName,DesigID,JobID,Gender,ShiftID,LocID,TypeID,GradeID,SecID,CardNo,FpID,PinCode,NicNo,FatherName,BloodGroup,BirthDate,MarStatus,JoinDate,ValidDate,IssueDate,ResignDate,HomeAdd,ProcessAtt,ProcessIn,Status,PhoneNo,Remarks,Email,CellNo,CrewID,FlagFP,FlagFace,FlagCard,EmpImageID,HasOT")] Emp emp)
{
string empNo = "";
int cardno = Convert.ToInt32(emp.CardNo);
emp.CardNo = cardno.ToString("0000000000");
if (string.IsNullOrEmpty(emp.EmpNo))
ModelState.AddModelError("EmpNo", "Emp No is required!");
if (string.IsNullOrEmpty(emp.EmpName))
ModelState.AddModelError("EmpName", "Name is required!");
if (emp.EmpNo != null)
{
if (emp.EmpNo.Length > 15)
ModelState.AddModelError("EmpNo", "String length exceeds!");
if (db.Emps.Where(aa => aa.EmpNo.ToUpper() == emp.EmpNo.ToUpper()).Count() > 0 )
ModelState.AddModelError("EmpNo", "Emp No should be unique!");
}
if (emp.CardNo != null)
{
if (db.Emps.Where(aa => aa.CardNo == emp.CardNo).Count() > 0)
if(emp.CardNo!="0000000000")
ModelState.AddModelError("CardNo", "Card No should be unique!");
if (emp.CardNo.Length > 10)
ModelState.AddModelError("CardNo", "String length exceeds!");
}
if (emp.EmpName != null)
{
if (emp.EmpName.Length > 40)
ModelState.AddModelError("EmpName", "String length exceeds!");
}
if(emp.SecID== null)
ModelState.AddModelError("SecID", "Please Specify section!");
if (emp.TypeID == null)
ModelState.AddModelError("TypeID", "Please Specify Type!");
if (emp.GradeID == null)
ModelState.AddModelError("GradeID", "Please Specify Grade!");
if (ModelState.IsValid)
{
emp.ProcessAtt = true;
emp.ProcessIn = true;
emp.EmpNo = emp.EmpNo.ToUpper();
empNo = emp.EmpNo;
emp.FpID = emp.EmpID;
db.Emps.Add(emp);
db.SaveChanges();
int _userID = Convert.ToInt32(Session["LogedUserID"].ToString());
HelperClass.MyHelper.SaveAuditLog(_userID, (byte)MyEnums.FormName.Employee, (byte)MyEnums.Operation.Add, DateTime.Now);
HttpPostedFileBase file = Request.Files["ImageData"];
if (file != null)
{
ImageConversion _Image = new ImageConversion();
int imageID = _Image.UploadImageInDataBase(file, emp.EmpNo);
if (imageID!=0)
{
using (var ctx = new TAS2013Entities())
{
var _emp = ctx.Emps.Where(aa => aa.EmpNo == empNo).ToList();
if (_emp.Count > 0)
{
_emp.FirstOrDefault().EmpImageID = imageID;
ctx.SaveChanges();
ctx.Dispose();
}
}
}
else
{
}
}
return RedirectToAction("Index");
}
var _wings = new List<Division>();
using (TAS2013Entities context = new TAS2013Entities())
{
_wings = context.Divisions.ToList();
ViewBag.Wing = new SelectList(_wings.OrderBy(s=>s.DivisionName), "WingID", "WingName");
User LoggedInUser = Session["LoggedUser"] as User;
ViewBag.CrewID = new SelectList(db.Crews.OrderBy(s=>s.CrewName), "CrewID", "CrewName");
ViewBag.DesigID = new SelectList(db.Designations.OrderBy(s=>s.DesignationName), "DesignationID", "DesignationName");
ViewBag.GradeID = new SelectList(db.Grades.OrderBy(s=>s.GradeName), "GradeID", "GradeName");
ViewBag.JobID = new SelectList(db.JobTitles.OrderBy(s=>s.JobTitle1), "JobID", "JobTitle1");
ViewBag.LocID = new SelectList(db.Locations.OrderBy(s=>s.LocName), "LocID", "LocName");
ViewBag.SecID = new SelectList(db.Sections.OrderBy(s=>s.SectionName), "SectionID", "SectionName");
ViewBag.ShiftID = new SelectList(db.Shifts.OrderBy(s=>s.ShiftName), "ShiftID", "ShiftName");
ViewBag.TypeID = new SelectList(db.EmpTypes.OrderBy(s=>s.TypeName), "TypeID", "TypeName");
ViewBag.CatID = new SelectList(db.Categories.OrderBy(s=>s.CatName), "CatID", "CatName");
ViewBag.DeptID = new SelectList(db.Departments.OrderBy(s=>s.DeptName), "DeptID", "DeptName");
}
return View(emp);
}
public ActionResult Login(User u)
{
try
{
//using (PrincipalContext pc = new PrincipalContext(ContextType.Domain, "fatima-group.com"))
//{
// //validate the credentials
// //bool isValid = pc.ValidateCredentials("ffl.ithelpdesk", "fatima@0202");
// bool isValid = pc.ValidateCredentials(u.UserName, u.Password);
// if (isValid)
// {
if (ModelState.IsValid) // this is check validity
{
using (TAS2013Entities dc = new TAS2013Entities())
{
var v = dc.Users.Where(a => a.UserName.Equals(u.UserName) && a.Status == true).FirstOrDefault();
if (v != null)
{
Session["MDevice"] = "0";
Session["MHR"] = "0";
Session["MDevice"] = "0";
Session["MLeave"] = "0";
Session["MEditAtt"] = "0";
Session["MUser"] = "******";
Session["LogedUserFullname"] = "";
Session["UserCompany"] = "";
Session["MRDailyAtt"] = "0";
Session["MRLeave"] = "0";
Session["MRMonthly"] = "0";
Session["MRAudit"] = "0";
Session["MRManualEditAtt"] = "0";
Session["MREmployee"] = "0";
Session["MRDetail"] = "0";
Session["MRSummary"] = "0";
Session["LogedUserID"] = v.UserID.ToString();
Session["LogedUserFullname"] = v.UserName;
Session["LoggedUser"] = v;
Session["UserCompany"] = v.CompanyID.ToString();
if (v.MHR == true)
Session["MHR"] = "1";
if (v.MDevice == true)
Session["MDevice"] = "1";
if (v.MLeave == true)
Session["MLeave"] = "1";
if (v.MEditAtt == true)
Session["MEditAtt"] = "1";
if (v.MUser == true)
Session["MUser"] = "******";
if (v.MRDailyAtt == true)
Session["MRDailyAtt"] = "1";
if (v.MRLeave == true)
Session["MRLeave"] = "1";
if (v.MRMonthly == true)
Session["MRMonthly"] = "1";
if (v.MRAudit == true)
Session["MRAudit"] = "1";
if (v.MRManualEditAtt == true)
Session["MRManualEditAtt"] = "1";
if (v.MProcess == true)
Session["MProcess"] = "1";
if (v.MREmployee == true)
Session["MREmployee"] = "1";
if (v.MRDetail == true)
Session["MRDetail"] = "1";
if (v.MRSummary == true)
Session["MRSummary"] = "1";
else
Session["MRSummary"] = "0";
if (v.MRoster == true)
Session["MRoster"] = "1";
if(v.MRGraph==true)
Session["MGraph"] = "1";
else
Session["MGraph"] = "0";
HelperClass.MyHelper.SaveAuditLog(v.UserID, (byte)MyEnums.FormName.LogIn, (byte)MyEnums.Operation.LogIn, DateTime.Now);
return RedirectToAction("AfterLogin");
}
else
{
int LoginCount = 0;
bool successOnConversion = int.TryParse(Session["LoginCount"] as string, out LoginCount);
if (successOnConversion == true)
{
LoginCount++;
Session["LoginCount"] = LoginCount + "";
}
else
{
Session["LoginCount"] = "1";
}
}
}
}
//}
else
{
int LoginCount = 0;
bool successOnConversion = int.TryParse(Session["LoginCount"] as string, out LoginCount);
if (successOnConversion == true)
{
LoginCount++;
Session["LoginCount"] = LoginCount + "";
}
else
{
Session["LoginCount"] = "1";
}
}
return RedirectToAction("index");
}
//using (var context = new PrincipalContext(ContextType.Domain, "fatima-group.com", "*****@*****.**", "fatima@0202"))
//{
// using (var searcher = new PrincipalSearcher(new UserPrincipal(context)))
// {
// foreach (var result in searcher.FindAll())
// {
// DirectoryEntry de = result.GetUnderlyingObject() as DirectoryEntry;
// string name = result.Name;
// //label1.Text += "Name: " + result.Name;
// //label1.Text += " account name : " + result.UserPrincipalName;
// //label1.Text += " Server: " + result.Context.ConnectedServer + "\r";
// }
// }
//}
// this action is for handle post (login)
//}
catch (Exception ex)
{
ViewBag.Message = "There seems to be a problem with the network. Please contact your network administrator";
return RedirectToAction("Index");
}
}
private DataTable GYL(List<EmpView> _Emp)
{
TAS2013Entities context = new TAS2013Entities();
List<LvConsumed> leaveQuota = new List<LvConsumed>();
List<LvConsumed> tempLeaveQuota = new List<LvConsumed>();
leaveQuota = context.LvConsumeds.ToList();
foreach (var emp in _Emp)
{
int EmpID;
string EmpNo = ""; string EmpName = "";
float TotalAL = 0; float BalAL = 0; float TotalCL = 0; float BalCL = 0; float TotalSL = 0; float BalSL = 0;
float JanAL = 0; float JanCL = 0; float JanSL = 0; float FebAL = 0; float FebCL = 0; float FebSL = 0;
float MarchAL = 0; float MarchCL = 0; float MarchSL = 0;
float AprilAL = 0; float AprilCL = 0; float AprilSL = 0;
float MayAL = 0; float MayCL = 0; float MaySL = 0;
float JunAL = 0; float JunCL = 0; float JunSL = 0;
float JullyAL = 0; float JullyCL = 0; float JullySL = 0;
float AugAL = 0; float AugCL = 0; float AugSL = 0;
float SepAL = 0; float SepCL = 0; float SepSL = 0;
float OctAL = 0; float OctCL = 0; float OctSL = 0;
float NovAL = 0; float NovCL = 0; float NovSL = 0;
float DecAL = 0; float DecCL = 0; float DecSL = 0;
string Remarks = ""; string DeptName; short DeptID; string LocationName; short LocationID; string SecName; short SecID; string DesgName; short DesigID; string CrewName; short CrewID; string CompanyName; short CompanyID;
tempLeaveQuota = leaveQuota.Where(aa => aa.EmpID == emp.EmpID).ToList();
foreach (var leave in tempLeaveQuota)
{
EmpID = emp.EmpID;
EmpNo = emp.EmpNo;
EmpName = emp.EmpName;
DeptID = (short)emp.DeptID;
DeptName = emp.DeptName;
LocationName = emp.LocName;
LocationID = (short)emp.LocID;
SecName = emp.SectionName;
SecID = (short)emp.SecID;
DesgName = emp.DesignationName;
DesigID = (short)emp.DesigID;
CrewName = emp.CrewName;
CrewID = (short)emp.CrewID;
CompanyName = emp.CompName;
CompanyID = (short)emp.CompanyID;
switch (leave.LeaveType)
{
case "A"://Casual
JanCL = (float)leave.JanConsumed;
FebCL = (float)leave.FebConsumed;
MarchCL = (float)leave.MarchConsumed;
AprilCL = (float)leave.AprConsumed;
MayCL = (float)leave.MayConsumed;
JunCL = (float)leave.JuneConsumed;
JullyCL = (float)leave.JulyConsumed;
AugCL = (float)leave.AugustConsumed;
SepCL = (float)leave.SepConsumed;
OctCL = (float)leave.OctConsumed;
NovCL = (float)leave.NovConsumed;
DecCL = (float)leave.DecConsumed;
TotalCL = (float)leave.TotalForYear;
BalCL = (float)leave.YearRemaining;
break;
case "B"://Anual
JanAL = (float)leave.JanConsumed;
FebAL = (float)leave.FebConsumed;
MarchAL = (float)leave.MarchConsumed;
AprilAL = (float)leave.AprConsumed;
MayAL = (float)leave.MayConsumed;
JunAL = (float)leave.JuneConsumed;
JullyAL = (float)leave.JulyConsumed;
AugAL = (float)leave.AugustConsumed;
SepAL = (float)leave.SepConsumed;
OctAL = (float)leave.OctConsumed;
NovAL = (float)leave.NovConsumed;
DecAL = (float)leave.DecConsumed;
TotalAL = (float)leave.TotalForYear;
BalAL = (float)leave.YearRemaining;
break;
case "C"://Sick
JanSL = (float)leave.JanConsumed;
FebSL = (float)leave.FebConsumed;
MarchSL = (float)leave.MarchConsumed;
AprilSL = (float)leave.AprConsumed;
MaySL = (float)leave.MayConsumed;
JunSL = (float)leave.JuneConsumed;
JullySL = (float)leave.JulyConsumed;
AugSL = (float)leave.AugustConsumed;
SepSL = (float)leave.SepConsumed;
OctSL = (float)leave.OctConsumed;
NovSL = (float)leave.NovConsumed;
DecSL = (float)leave.DecConsumed;
TotalSL = (float)leave.TotalForYear;
BalSL = (float)leave.YearRemaining;
break;
}
AddDataToDT(EmpID, EmpNo, EmpName, TotalAL, BalAL, TotalCL, BalCL, TotalSL, BalSL, JanAL, JanCL, JanSL, FebAL, FebCL, FebSL, MarchAL, MarchCL, MarchSL, AprilAL, AprilCL, AprilSL, MayAL, MayCL, MaySL, JunAL, JunCL, JunSL, JullyAL, JullyCL, JullySL, AugAL, AugCL, AugSL, SepAL, SepCL, SepSL, OctAL, OctCL, OctSL, NovAL, NovCL, NovSL, DecAL, DecCL, DecSL, Remarks, DeptName, (short)DeptID, LocationName, (short)LocationID, SecName, (short)SecID, DesgName, DesigID, CrewName, CrewID, CompanyName, (short)CompanyID);
}
}
return MYLeaveSummaryDT;
}
public string MakeCustomizeQuery(User _user)
{
string query = " where ";
string subQuery = "";
string subQueryLoc = "";
List<string> _Criteria = new List<string>();
List<string> _CriteriaForOr = new List<string>();
List<string> _CriteriaForOrLoc = new List<string>();
//if (_user.ViewLocation == true)
// {
// _Criteria.Add(" LocID = " + _user.LocationID.ToString());
// }
TAS2013Entities db= new TAS2013Entities();
List<UserLocation> ulocs = new List<UserLocation>();
ulocs = db.UserLocations.Where(aa => aa.UserID == _user.UserID).ToList();
foreach (var uloc in ulocs)
{
_CriteriaForOrLoc.Add(" LocID = " + uloc.LocationID + " ");
}
if (_user.ViewContractual == true)
{
_CriteriaForOr.Add(" CatID = 3 ");
}
if (_user.ViewPermanentMgm == true)
{
_CriteriaForOr.Add(" CatID = 2 ");
}
if (_user.ViewPermanentStaff == true)
{
_CriteriaForOr.Add(" CatID = 4 ");
}
_CriteriaForOr.Add(" CatID=1 ");
switch (_user.RoleID)
{
case 1:
break;
case 2:
_Criteria.Add(" CompanyID= 1 or CompanyID = 2 ");
break;
case 3:
_Criteria.Add(" CompanyID>= 3");
break;
case 4:
_Criteria.Add(" CompanyID = "+_user.CompanyID.ToString());
break;
case 5:
break;
}
for (int i = 0; i < _Criteria.Count; i++ )
{
query = query + _Criteria[i] + " and ";
}
for (int i = 0; i < _CriteriaForOrLoc.Count-1; i++)
{
subQueryLoc = subQueryLoc + _CriteriaForOrLoc[i] + " or ";
}
if(_CriteriaForOrLoc.Count !=0)
subQueryLoc = " and ( " + subQueryLoc + _CriteriaForOrLoc[_CriteriaForOrLoc.Count-1] + " ) ";
//query = query + " ) and (";
//query = query + _Criteria[_Criteria.Count-1];
subQuery = " ( ";
for (int i = 0; i < _CriteriaForOr.Count - 1; i++)
{
subQuery = subQuery + _CriteriaForOr[i] + " or ";
}
subQuery = subQuery + _CriteriaForOr[_CriteriaForOr.Count - 1];
subQuery = subQuery + " ) ";
query = query + subQuery + subQueryLoc;
return query;
}
private string UserAccessGetCity(User _User, string role)
{
string query = "";
TAS2013Entities ctx = new TAS2013Entities();
List<UserAccess> uAcc = new List<UserAccess>();
uAcc = ctx.UserAccesses.Where(aa => aa.UserID == _User.UserID).ToList();
switch (role)
{
case "Region":
foreach (var uaccess in uAcc)
{
Region region = ctx.Regions.Where(aa => aa.RegionID == uaccess.CriteriaData).FirstOrDefault();
List<City> cities = ctx.Cities.Where(aa => aa.CityID == region.RegionID).ToList();
foreach (var city in cities)
{
query = query + "CityID=" + city.CityID + " or ";
}
}
query = query.Substring(0, query.Length - 4);
return query;
case "City": foreach (var uaccess in uAcc)
{
query = query + "CityID=" + uaccess.CriteriaData + " or ";
}
query = query.Substring(0, query.Length - 4);
return query;
case "Zone": foreach (var uaccess in uAcc)
{
Zone zone = ctx.Zones.Where(aa => aa.ZoneID == uaccess.CriteriaData).FirstOrDefault();
List<Region> region = ctx.Regions.Where(aa => aa.ZoneID == zone.ZoneID).ToList();
foreach (var re in region)
{
List<City> cities = ctx.Cities.Where(aa => aa.RegionID == re.RegionID).ToList();
foreach (var city in cities)
{
query = query + "CityID=" + city.CityID + " or ";
}
}
}
query = query.Substring(0, query.Length - 4);
return query;
case "Location": foreach (var uaccess in uAcc)
{
Location loc = ctx.Locations.Where(aa => aa.LocID == uaccess.CriteriaData).FirstOrDefault();
City city = ctx.Cities.Where(aa => aa.CityID == loc.CityID).FirstOrDefault();
query = query + "CityID=" + city.CityID + " or ";
} query = query.Substring(0, query.Length - 4);
return query;
case "SuperUser": query = "CityID>0";
return query;
} return query;
}
public List<EmpPhoto> GetCompanyImages(FiltersModel fm)
{
TAS2013Entities ctx = new TAS2013Entities();
companyimage = new List<EmpPhoto>();
companyimage.Add(ctx.EmpPhotoes.Where(aa => aa.PhotoID == 4785).First());
return companyimage;
}
private void BindGridViewLocation(string search)
{
FiltersModel fm = Session["FiltersModel"] as FiltersModel;
List<Location> _View = new List<Location>();
List<Location> _TempView = new List<Location>();
TAS2013Entities db = new TAS2013Entities();
User LoggedInUser = HttpContext.Current.Session["LoggedUser"] as User;
QueryBuilder qb = new QueryBuilder();
string query = qb.QueryForLocReport(LoggedInUser);
List<City> city = db.Cities.ToList();
DataTable dt = qb.GetValuesfromDB("select * from Location " + query+" order by LocName asc");
_View = dt.ToList<Location>();
if (fm.RegionFilter.Count > 0)
{
_TempView.Clear();
foreach (var region in fm.RegionFilter)
{
short regionID = Convert.ToInt16(region.ID);
foreach (var c in city.Where(aa => aa.RegionID == regionID))
{
_TempView.AddRange(_View.Where(aa => aa.CityID == c.CityID).ToList());
}
}
_View = _TempView.ToList();
}
if (fm.CityFilter.Count > 0)
{
_TempView.Clear();
foreach (var citi in fm.CityFilter)
{
short _compID = Convert.ToInt16(citi.ID);
_TempView.AddRange(_View.Where(aa => aa.CityID == _compID).ToList());
}
_View = _TempView.ToList();
}
GridViewLocation.DataSource = _View.Where(aa => aa.LocName.ToUpper().Contains(search.ToUpper())).ToList();
GridViewLocation.DataBind();
}
private DataTable GetLV(List<EmpView> _Emp, int month)
{
using (var ctx = new TAS2013Entities())
{
List<LvConsumed> _lvConsumed = new List<LvConsumed>();
LvConsumed _lvTemp = new LvConsumed();
_lvConsumed = ctx.LvConsumeds.ToList();
List<LvType> _lvTypes = ctx.LvTypes.ToList();
foreach (var emp in _Emp)
{
float BeforeCL = 0, UsedCL = 0, BalCL = 0;
float BeforeSL = 0, UsedSL = 0, BalSL = 0;
float BeforeAL = 0, UsedAL = 0, BalAL = 0;
string _month = "";
List<LvConsumed> entries = ctx.LvConsumeds.Where(aa => aa.EmpID == emp.EmpID).ToList();
LvConsumed eCL = entries.FirstOrDefault(lv => lv.LeaveType == "A");
LvConsumed eSL = entries.FirstOrDefault(lv => lv.LeaveType == "C");
LvConsumed eAL = entries.FirstOrDefault(lv => lv.LeaveType == "B");
if (entries.Count > 0)
{
switch (month)
{
case 1:
// casual
BeforeCL = (float)eCL.TotalForYear;
UsedCL = (float)eCL.JanConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear;
UsedSL = (float)eSL.JanConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear;
UsedAL = (float)eAL.JanConsumed;
_month = "January";
break;
case 2:
// casual
BeforeCL = (float)eCL.TotalForYear - (float)eCL.JanConsumed;
UsedCL = (float)eCL.FebConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear - (float)eSL.JanConsumed;
UsedSL = (float)eSL.FebConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear - (float)eAL.JanConsumed;
UsedAL = (float)eAL.FebConsumed;
break;
_month = "Febu";
case 3:
// casual
BeforeCL = (float)eCL.TotalForYear - ((float)eCL.JanConsumed + (float)eCL.FebConsumed);
UsedCL = (float)eCL.MarchConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear - ((float)eSL.JanConsumed + (float)eSL.FebConsumed);
UsedSL = (float)eSL.MarchConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear - ((float)eAL.JanConsumed + (float)eAL.FebConsumed);
UsedAL = (float)eAL.MarchConsumed;
break;
case 4:
// casual
BeforeCL = (float)eCL.TotalForYear - ((float)eCL.JanConsumed + (float)eCL.FebConsumed + (float)eCL.MarchConsumed);
UsedCL = (float)eCL.AprConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear - ((float)eSL.JanConsumed + (float)eSL.FebConsumed + (float)eSL.MarchConsumed);
UsedSL = (float)eSL.AprConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear - ((float)eAL.JanConsumed + (float)eAL.FebConsumed + (float)eAL.MarchConsumed);
UsedAL = (float)eAL.AprConsumed;
break;
case 5:
// casual
BeforeCL = (float)eCL.TotalForYear - ((float)eCL.JanConsumed + (float)eCL.FebConsumed + (float)eCL.MarchConsumed + (float)eCL.AprConsumed);
UsedCL = (float)eCL.MayConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear - ((float)eSL.JanConsumed + (float)eSL.FebConsumed + (float)eSL.MarchConsumed + (float)eSL.AprConsumed);
UsedSL = (float)eSL.MayConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear - ((float)eAL.JanConsumed + (float)eAL.FebConsumed + (float)eAL.MarchConsumed + (float)eAL.AprConsumed);
UsedAL = (float)eAL.MayConsumed;
break;
case 6:
// casual
BeforeCL = (float)eCL.TotalForYear - ((float)eCL.JanConsumed + (float)eCL.FebConsumed + (float)eCL.MarchConsumed + (float)eCL.AprConsumed + (float)eCL.MayConsumed);
UsedCL = (float)eCL.JuneConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear - ((float)eSL.JanConsumed + (float)eSL.FebConsumed + (float)eSL.MarchConsumed + (float)eSL.AprConsumed + (float)eSL.MayConsumed);
UsedSL = (float)eSL.JuneConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear - ((float)eAL.JanConsumed + (float)eAL.FebConsumed + (float)eAL.MarchConsumed + (float)eAL.AprConsumed + (float)eAL.MayConsumed);
UsedAL = (float)eAL.JuneConsumed;
break;
case 7:
// casual
BeforeCL = (float)eCL.TotalForYear - ((float)eCL.JanConsumed + (float)eCL.FebConsumed + (float)eCL.MarchConsumed + (float)eCL.AprConsumed + (float)eCL.MayConsumed + (float)eCL.JuneConsumed);
UsedCL = (float)eCL.JulyConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear - ((float)eSL.JanConsumed + (float)eSL.FebConsumed + (float)eSL.MarchConsumed + (float)eSL.AprConsumed + (float)eSL.MayConsumed + (float)eSL.JuneConsumed);
UsedSL = (float)eSL.JulyConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear - ((float)eAL.JanConsumed + (float)eAL.FebConsumed + (float)eAL.MarchConsumed + (float)eAL.AprConsumed + (float)eAL.MayConsumed + (float)eAL.JuneConsumed);
UsedAL = (float)eAL.JulyConsumed;
break;
case 8:
// casual
BeforeCL = (float)eCL.TotalForYear - ((float)eCL.JanConsumed + (float)eCL.FebConsumed + (float)eCL.MarchConsumed + (float)eCL.AprConsumed + (float)eCL.MayConsumed + (float)eCL.JuneConsumed + (float)eCL.JulyConsumed);
UsedCL = (float)eCL.AugustConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear - ((float)eSL.JanConsumed + (float)eSL.FebConsumed + (float)eSL.MarchConsumed + (float)eSL.AprConsumed + (float)eSL.MayConsumed + (float)eSL.JuneConsumed + (float)eSL.JulyConsumed);
UsedSL = (float)eSL.AugustConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear - ((float)eAL.JanConsumed + (float)eAL.FebConsumed + (float)eAL.MarchConsumed + (float)eAL.AprConsumed + (float)eAL.MayConsumed + (float)eAL.JuneConsumed + (float)eAL.JulyConsumed);
UsedAL = (float)eAL.AugustConsumed;
break;
case 9:
// casual
BeforeCL = (float)eCL.TotalForYear - ((float)eCL.JanConsumed + (float)eCL.FebConsumed + (float)eCL.MarchConsumed + (float)eCL.AprConsumed + (float)eCL.MayConsumed + (float)eCL.JuneConsumed + (float)eCL.JulyConsumed + (float)eCL.AugustConsumed);
UsedCL = (float)eCL.SepConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear - ((float)eSL.JanConsumed + (float)eSL.FebConsumed + (float)eSL.MarchConsumed + (float)eSL.AprConsumed + (float)eSL.MayConsumed + (float)eSL.JuneConsumed + (float)eSL.JulyConsumed + (float)eSL.AugustConsumed);
UsedSL = (float)eSL.SepConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear - ((float)eAL.JanConsumed + (float)eAL.FebConsumed + (float)eAL.MarchConsumed + (float)eAL.AprConsumed + (float)eAL.MayConsumed + (float)eAL.JuneConsumed + (float)eAL.JulyConsumed + (float)eAL.AugustConsumed);
UsedAL = (float)eAL.SepConsumed;
break;
case 10:
// casual
BeforeCL = (float)eCL.TotalForYear - ((float)eCL.JanConsumed + (float)eCL.FebConsumed + (float)eCL.MarchConsumed + (float)eCL.AprConsumed + (float)eCL.MayConsumed + (float)eCL.JuneConsumed + (float)eCL.JulyConsumed + (float)eCL.AugustConsumed + (float)eCL.SepConsumed);
UsedCL = (float)eCL.OctConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear - ((float)eSL.JanConsumed + (float)eSL.FebConsumed + (float)eSL.MarchConsumed + (float)eSL.AprConsumed + (float)eSL.MayConsumed + (float)eSL.JuneConsumed + (float)eSL.JulyConsumed + (float)eSL.AugustConsumed + (float)eSL.SepConsumed);
UsedSL = (float)eSL.OctConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear - ((float)eAL.JanConsumed + (float)eAL.FebConsumed + (float)eAL.MarchConsumed + (float)eAL.AprConsumed + (float)eAL.MayConsumed + (float)eAL.JuneConsumed + (float)eAL.JulyConsumed + (float)eAL.AugustConsumed + (float)eAL.AugustConsumed);
UsedAL = (float)eAL.SepConsumed;
break;
case 11:
// casual
BeforeCL = (float)eCL.TotalForYear - ((float)eCL.JanConsumed + (float)eCL.FebConsumed + (float)eCL.MarchConsumed + (float)eCL.AprConsumed + (float)eCL.MayConsumed + (float)eCL.JuneConsumed + (float)eCL.JulyConsumed + (float)eCL.AugustConsumed + (float)eCL.SepConsumed + (float)eCL.OctConsumed);
UsedCL = (float)eCL.NovConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear - ((float)eSL.JanConsumed + (float)eSL.FebConsumed + (float)eSL.MarchConsumed + (float)eSL.AprConsumed + (float)eSL.MayConsumed + (float)eSL.JuneConsumed + (float)eSL.JulyConsumed + (float)eSL.AugustConsumed + (float)eSL.SepConsumed + (float)eSL.OctConsumed);
UsedSL = (float)eSL.NovConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear - ((float)eAL.JanConsumed + (float)eAL.FebConsumed + (float)eAL.MarchConsumed + (float)eAL.AprConsumed + (float)eAL.MayConsumed + (float)eAL.JuneConsumed + (float)eAL.JulyConsumed + (float)eAL.AugustConsumed + (float)eAL.AugustConsumed + (float)eAL.OctConsumed);
UsedAL = (float)eAL.NovConsumed;
break;
case 12:
// casual
BeforeCL = (float)eCL.TotalForYear - ((float)eCL.JanConsumed + (float)eCL.FebConsumed + (float)eCL.MarchConsumed + (float)eCL.AprConsumed + (float)eCL.MayConsumed + (float)eCL.JuneConsumed + (float)eCL.JulyConsumed + (float)eCL.AugustConsumed + (float)eCL.SepConsumed + (float)eCL.OctConsumed + (float)eCL.NovConsumed);
UsedCL = (float)eCL.DecConsumed;
//Sick
BeforeSL = (float)eSL.TotalForYear - ((float)eSL.JanConsumed + (float)eSL.FebConsumed + (float)eSL.MarchConsumed + (float)eSL.AprConsumed + (float)eSL.MayConsumed + (float)eSL.JuneConsumed + (float)eSL.JulyConsumed + (float)eSL.AugustConsumed + (float)eSL.SepConsumed + (float)eSL.OctConsumed + (float)eSL.NovConsumed);
UsedSL = (float)eSL.DecConsumed;
//Anual
BeforeAL = (float)eAL.TotalForYear - ((float)eAL.JanConsumed + (float)eAL.FebConsumed + (float)eAL.MarchConsumed + (float)eAL.AprConsumed + (float)eAL.MayConsumed + (float)eAL.JuneConsumed + (float)eAL.JulyConsumed + (float)eAL.AugustConsumed + (float)eAL.AugustConsumed + (float)eAL.OctConsumed + (float)eAL.NovConsumed);
UsedAL = (float)eAL.DecConsumed;
break;
}
BalCL = (float)(BeforeCL - UsedCL);
BalSL = (float)(BeforeSL - UsedSL);
BalAL = (float)(BeforeAL - UsedAL);
AddDataToDT(emp.EmpNo, emp.EmpName, emp.DesignationName, emp.SectionName, emp.DeptName, emp.TypeName, emp.CatName, emp.LocName, BeforeCL, BeforeSL, BeforeAL, UsedCL, UsedSL, UsedAL, BalCL, BalSL, BalAL, _month);
}
}
}
return LvSummaryMonth;
}