public IEnumerable SearchData()
{
using (ALSIEntities ctx = new ALSIEntities())
{
var result = from j in ctx.m_type_of_test
join s in ctx.m_specification on j.specification_id equals s.ID
select new
{
ID = j.ID,
specification_id = s.ID,
specification = s.name,
prefix = j.prefix,
name = j.name
};
if (this.ID > 0)
{
result = result.Where(x => x.ID == this.ID);
}
if (this.specification_id > 0)
{
result = result.Where(x => x.specification_id == this.specification_id);
}
if (!String.IsNullOrEmpty(this.name))
{
result = result.Where(x => x.name == this.name);
}
return(result.ToList());
}
}
public IEnumerable SearchData()
{
using (ALSIEntities ctx = new ALSIEntities())
{
var result = from u in ctx.users_login
//join p in ctx.users_login_profile on u.id equals p.users_login_id
join r in ctx.m_role on u.role_id equals r.ID
select new
{
id = u.id,
role = r.name,
username = u.username,
email = u.email,
latest_login = u.latest_login,
create_date = u.create_date,
status = u.status,
phone = u.mobile_phone
};
if (!String.IsNullOrEmpty(this.username))
{
result = result.Where(x => x.username.Contains(this.username));
}
if (!String.IsNullOrEmpty(this.email))
{
result = result.Where(x => x.email.Contains(this.email));
}
//if (!String.IsNullOrEmpty(this.users_loginProfile.phone))
//{
// result = result.Where(x => x.phone == this.users_loginProfile.phone);
//}
return(result.ToList());
}
}
public string getNavigator(string _currentPage)
{
StringBuilder sb = new StringBuilder();
sb.Append("<li>");
sb.Append("<i class=\"fa fa-home\"></i>");
sb.Append("<a href=\"" + Constants.LINK_SEARCH_JOB_REQUEST + "\">Home</a>");
sb.Append("</li>");
using (var ctx = new ALSIEntities())
{
_currentPage = Path.GetFileName(_currentPage);
menu child = repo.GetAll().Where(x => Path.GetFileName(x.URL_NAVIGATE) == _currentPage).FirstOrDefault();
if (child != null)
{
menu parent = repo.GetAll().Where(x => x.MENU_ID == child.PREVIOUS_MENU_ID).FirstOrDefault();
if (parent != null)
{
sb.Append("<li>");
sb.Append("<i class=\"fa fa-angle-right\"></i>");
sb.Append("<a href=\"#\">" + parent.MENU_NAME + "</a>");
sb.Append("</li>");
nav.Add(parent.MENU_NAME);
}
sb.Append("<li>");
sb.Append("<i class=\"fa fa-angle-right\"></i>");
sb.Append("<a href=\"#\">" + child.MENU_NAME + "</a>");
sb.Append("</li>");
nav.Add(child.MENU_NAME);
}
}
return(sb.ToString());
}
public List <menu> GetAll()
{
using (var ctx = new ALSIEntities())
{
IEnumerable <menu> menus = repo.GetAll();
return(menus.ToList());
}
}
public List <menu_role> getRoleListByRoleId(int _roleId)
{
using (var ctx = new ALSIEntities())
{
return((from mr in ctx.menu_role
where mr.ROLE_ID == _roleId
select mr).ToList());
}
}
public menu_role getRoleByUserId(int _userId)
{
using (var ctx = new ALSIEntities())
{
return((from mr in ctx.menu_role
join ur in ctx.users_role on mr.ROLE_ID equals ur.ROLE_ID
where ur.USER_ID == _userId
select mr).FirstOrDefault());
}
}
public static void IncrementRunning(int id)
{
using (ALSIEntities ctx = new ALSIEntities())
{
job_running running = (from c in ctx.job_running where c.ID == id select c).FirstOrDefault();
if (running != null)
{
running.running_number = running.running_number + 1;
ctx.SaveChanges();
}
}
}
public static int GetRunning(int id)
{
using (ALSIEntities ctx = new ALSIEntities())
{
job_running running = (from c in ctx.job_running where c.ID == id select c).FirstOrDefault();
if (running != null)
{
return((int)running.running_number + 1);
}
return(-1);
}
}
public IEnumerable SelectDistinct()
{
using (ALSIEntities ctx = new ALSIEntities())
{
var result = from c in ctx.m_type_of_test
//where c.parent == -1
group c by c.data_group into g
select new { Id = g.Key, Name = g.Key };
return(result.ToList());
}
}
//GET MENU_ROLE MAX ID
public int GetMax()
{
using (ALSIEntities ctx = new ALSIEntities())
{
m_role mRole = (from c in ctx.m_role select c).OrderByDescending(x => x.ID).FirstOrDefault();
if (mRole != null)
{
return((int)mRole.ID + 1);
}
return(-1);
}
}
public List <int> getMenuByRole(int _roleId)
{
List <int> result = new List <int>();
using (var ctx = new ALSIEntities())
{
IEnumerable <menu_role> menuRoles = repo.GetAll().Where(x => x.ROLE_ID == _roleId);
foreach (menu_role _menuRole in menuRoles)
{
result.Add(_menuRole.MENU_ID);
}
}
return(result);
}
public IEnumerable SearchData([Optional] string sortDirection, [Optional] string sortExpression)
{
using (ALSIEntities ctx = new ALSIEntities())
{
IEnumerable dataList = null;
var result = from j in ctx.m_customer
join a in ctx.m_customer_address on j.ID equals a.company_id
into dep from dept in dep.DefaultIfEmpty() orderby j.company_name
select new
{
j.ID,
j.customer_code,
j.company_name,
j.code,
dept.address
};
if (!string.IsNullOrEmpty(this.customer_code))
{
result = result.Where(x => x.customer_code.Contains(this.customer_code));
}
if (!string.IsNullOrEmpty(this.company_name))
{
result = result.Where(x => x.company_name.Contains(this.company_name));
}
if (sortDirection != null)
{
switch (sortDirection)
{
case "ASC":
dataList = result.ToList().OrderBy(x => x.GetType().GetProperty(sortExpression).GetValue(x, null)).ToList();
break;
case "DESC":
dataList = result.ToList().OrderByDescending(x => x.GetType().GetProperty(sortExpression).GetValue(x, null)).ToList();
break;
}
}
else
{
dataList = result.ToList();
}
return(dataList);
}
}
public IEnumerable SearchData()
{
//return _repository.GetAll().ToList();
using (ALSIEntities ctx = new ALSIEntities())
{
var result = from j in ctx.m_template select j;// j.status == "A" select j;
//if (this.ID > 0)
//{
// result = result.Where(x => x.ID == this.ID);
//}
if (!String.IsNullOrEmpty(this.name))
{
result = result.Where(x => x.name.Contains(this.name));
}
return(result.ToList());
}
}
public IEnumerable SearchData()
{
//return _repository.GetAll().ToList();
using (ALSIEntities ctx = new ALSIEntities())
{
var result = from r in ctx.m_role
select new
{
r.ID,
r.name
};
if (!String.IsNullOrEmpty(this.name))
{
result = result.Where(x => x.name == this.name);
}
return(result.ToList());
}
}
public string getMenuByRole(List <int> _menuByRole, string _currentPage)
{
StringBuilder result = new StringBuilder();
using (var ctx = new ALSIEntities())
{
_currentPage = Path.GetFileName(_currentPage);
IEnumerable <menu> menus = repo.GetAll().Where(x => _menuByRole.Contains(x.MENU_ID));
Console.WriteLine();
/*
* <li class="nav-item start ">
* <a href="javascript:;" class="nav-link nav-toggle">
* <i class="icon-home"></i>
* <span class="title">Dashboard</span>
* <span class="arrow"></span>
* </a>
* <ul class="sub-menu">
* <li class="nav-item start ">
* <a href="index.html" class="nav-link ">
* <i class="icon-bar-chart"></i>
* <span class="title">Dashboard 1</span>
* </a>
* </li>
* </ul>
* </li>
*/
IEnumerable <menu> parentMenus = menus.Where(x => x.PREVIOUS_MENU_ID == null).OrderBy(x => x.DISPLAY_ORDER);
foreach (menu _parent in parentMenus)
{
IEnumerable <menu> menuChilds = menus.Where(x => x.PREVIOUS_MENU_ID == _parent.MENU_ID).OrderBy(x => x.DISPLAY_ORDER);
if (menuChilds != null)
{
Boolean bActiveSelectedMenu = false;
foreach (menu _findCurrentMenu in menuChilds)
{
if (Path.GetFileName(_findCurrentMenu.URL_NAVIGATE).Equals(_currentPage))
{
if (_parent.MENU_ID == _findCurrentMenu.PREVIOUS_MENU_ID)
{
bActiveSelectedMenu = true;
}
break;
}
}
Console.WriteLine();
/* - BEGIN ADD MAIN MENU -*/
result.Append("<li class=\"nav-item " + (bActiveSelectedMenu ? "start active open" : "") + " \">");
result.Append("<a href=\"javascript:;\" class=\"nav-link nav-toggle\">");
result.Append("<i class=\"" + _parent.MENU_ICON + "\"></i>");
result.Append("<span class=\"title\">" + _parent.MENU_NAME + "</span>");
result.Append("<span class=\"arrow\"></span>");
result.Append("</a>");
/* - BEGIN ADD SUBMENU -*/
result.Append("<ul class=\"sub-menu\">");
foreach (menu _child in menuChilds)
{
result.Append("<li class=\"nav-item \">");
result.Append("<a href=\"" + _child.URL_NAVIGATE + "\" class=\"nav-link \">");
result.Append("<span class=\"title\">" + _child.MENU_NAME + "</span>");
result.Append("</a>");
result.Append("</li>");
}
result.Append("</ul>");
/* - END SUBMENU -*/
result.Append("</li>");
/* - END MAIN MENU -*/
}
}
}
return(result.ToString());
}
public void getmenuByTree(ref TreeView tv, List <menu_role> roles)
{
using (var ctx = new ALSIEntities())
{
IEnumerable <menu> menus = repo.GetAll();
foreach (menu _menu in menus.Where(x => x.PREVIOUS_MENU_ID == null).OrderBy(x => x.DISPLAY_ORDER))
{
TreeNode root = new TreeNode(_menu.MENU_NAME, _menu.MENU_ID.ToString());
IEnumerable <menu> menuChilds = menus.Where(x => x.PREVIOUS_MENU_ID == _menu.MENU_ID).OrderBy(x => x.DISPLAY_ORDER);
if (menuChilds != null)
{
foreach (menu _childmenu in menuChilds)
{
TreeNode child = new TreeNode(_childmenu.MENU_NAME, _childmenu.MENU_ID.ToString());
child.ShowCheckBox = true;
menu_role menuRole = roles.Where(x => x.MENU_ID == _childmenu.MENU_ID).FirstOrDefault();
if (menuRole != null)
{
child.Expanded = !((bool)menuRole.IS_CREATE && (bool)menuRole.IS_DELETE && (bool)menuRole.IS_EDIT);
child.Checked = true;
foreach (MenuRoleActionEnum val in Enum.GetValues(typeof(MenuRoleActionEnum)))
{
TreeNode childLevel1 = new TreeNode(val.ToString(), menuRole.MENU_ID.ToString());
childLevel1.ShowCheckBox = true;
switch (val)
{
case MenuRoleActionEnum.Add:
childLevel1.Checked = (bool)menuRole.IS_CREATE;
break;
case MenuRoleActionEnum.Delete:
childLevel1.Checked = (bool)menuRole.IS_DELETE;
break;
case MenuRoleActionEnum.Edit:
childLevel1.Checked = (bool)menuRole.IS_EDIT;
break;
}
child.ChildNodes.Add(childLevel1);
}
}
else
{
//
foreach (MenuRoleActionEnum val in Enum.GetValues(typeof(MenuRoleActionEnum)))
{
TreeNode childLevel1 = new TreeNode(val.ToString(), _childmenu.MENU_ID.ToString());
childLevel1.ShowCheckBox = true;
child.ChildNodes.Add(childLevel1);
}
}
root.ChildNodes.Add(child);
}
tv.Nodes.Add(root);
}
}
Console.WriteLine("");
}
}
public IEnumerable SearchData()
{
using (ALSIEntities ctx = new ALSIEntities())
{
//Status
// Received. Report x
// Sent to Customer x
// Receive Date.
// Due Date.
// ALS Ref
// No.Cus
// Ref
// No.S'Ref No.
// Company
// Invoice
// Po
// Contact
// Description
// Model
// Surface Area
// Specification
// Type of test
var result = from j in ctx.job_info
join s in ctx.job_sample on j.ID equals s.job_id
join ms in ctx.m_status on s.job_status equals ms.ID
join sp in ctx.m_specification on s.specification_id equals sp.ID
join tt in ctx.m_type_of_test on s.type_of_test_id equals tt.ID
join c in ctx.m_customer on j.customer_id equals c.ID
join cp in ctx.m_customer_contract_person on j.contract_person_id equals cp.ID
orderby s.ID descending
select new
{
ID = j.ID,
s.other_ref_no,
s.date_srchemist_complate,
s.date_admin_sent_to_cus,
receive_date = j.date_of_receive,
due_date = s.due_date,
s.due_date_customer,
s.due_date_lab,
job_number = s.job_number,
customer_ref_no = j.customer_ref_no,
s_pore_ref_no = j.s_pore_ref_no,
customer = c.company_name,
s.sample_invoice,
sample_po = s.sample_po,
contract_person = cp.name,
description = s.description,
model = s.model,
surface_area = s.surface_area,
specification = sp.name,
type_of_test = tt.name,
customer_id = c.ID,
job_status = s.job_status,
create_date = j.create_date,
sn = s.ID,
remarks = s.remarks,
contract_person_id = cp.ID,
job_role = s.job_role,
status_completion_scheduled = s.status_completion_scheduled,
s.step1owner,
s.step2owner,
s.step3owner,
s.step4owner,
s.step5owner,
s.step6owner,
j.job_prefix,
tt.data_group,
type_of_test_id = tt.ID,
type_of_test_name = tt.name,
spec_id = sp.ID,
s.date_login_received_sample,
s.date_chemist_alalyze,
s.date_labman_complete,
s.is_hold,
s.amend_count,
s.retest_count,
s.group_submit,
status_name = ms.name,
s.sample_prefix,
s.amend_or_retest
};
if (this.ID > 0)
{
result = result.Where(x => x.ID == this.ID);
}
if (this.sample_id > 0)
{
result = result.Where(x => x.sn == this.sample_id);
}
if (!String.IsNullOrEmpty(this.preFixText))
{
result = result.Where(x => x.sample_prefix.Trim().Contains(this.preFixText.Trim()));
}
//if (this.date_of_receive != null && this.date_of_receive !=DateTime.MinValue)
//{
// result = result.Where(x => x.receive_date == this.date_of_receive);
//}
//if (this.customer_id > 0)
//{
// result = result.Where(x => x.customer_id == this.customer_id);
//}
if (this.contract_person_id > 0)
{
result = result.Where(x => x.contract_person_id == this.contract_person_id);
}
//show type of test by user.
if (responsible_test != null && responsible_test.Length > 0)
{
result = result.Where(x => responsible_test.Contains(x.data_group));
}
if (this.status > 0)
{
result = result.Where(x => x.job_status == this.status);
}
if (!String.IsNullOrEmpty(this.jobRefNo))
{
result = result.Where(x => x.job_number.Contains(this.jobRefNo));
}
if (this.customer_id > 0)
{
if (!String.IsNullOrEmpty(this.customerText))
{
result = result.Where(x => x.customer.Contains(this.customerText));
}
}
//if (this.customer_id > 0)
//{
// result = result.Where(x => x.customer_id == this.customer_id);
//}
if (this.spec_id > 0)
{
result = result.Where(x => x.spec_id == this.spec_id);
}
if (!String.IsNullOrEmpty(this.dataGroup))
{
result = result.Where(x => x.data_group.Contains(this.dataGroup));
}
if (!String.IsNullOrEmpty(this.sample_po))
{
result = result.Where(x => x.sample_po.Contains(this.sample_po));
}
if (!String.IsNullOrEmpty(this.sample_invoice))
{
result = result.Where(x => x.sample_invoice.Contains(this.sample_invoice));
}
if (this.receive_report_from != DateTime.MinValue && this.receive_report_to != DateTime.MinValue)
{
result = result.Where(x => x.receive_date >= this.receive_report_from && x.receive_date <= this.receive_report_to);
}
if (this.duedate_from != DateTime.MinValue && this.duedate_to != DateTime.MinValue)
{
result = result.Where(x => x.due_date >= this.duedate_from && x.due_date <= this.duedate_to);
}
if (this.report_to_customer_from != DateTime.MinValue && this.report_to_customer_to != DateTime.MinValue)
{
result = result.Where(x => x.date_admin_sent_to_cus >= this.report_to_customer_from && x.date_admin_sent_to_cus <= this.report_to_customer_to);
}
return(result.ToList());
}
}
//public job_sample_logs SelectDate(int _sample_Id, int _status)
//{
// return _repository.Find(x => x.job_sample_id == _sample_Id && x.job_status == _status).OrderBy(x=>x.date).FirstOrDefault();
//}
public List <job_sample_logs> SelectNotification(users_login userLogin)
{
/*
* 1 Root
* 2 Login
* 3 Chemist
* 4 SrChemist
* 5 Admin
* 6 LabManager
* 7 Account
*/
List <job_sample_logs> logs = new List <job_sample_logs>();
using (var ctx = new ALSIEntities())
{
job_sample_logs jsl = new job_sample_logs();
var result = from l in ctx.job_sample_logs
join j in ctx.job_sample on l.job_sample_id equals j.ID
where l.is_active == "0"
orderby l.date descending
select new
{
l.ID,
j.step1owner,
j.step2owner,
j.step3owner,
j.step4owner,
j.step5owner,
j.step6owner,
j.step7owner,
l.log_title,
l.job_remark,
l.date,
j.job_status,
j.job_number
};
RoleEnum roleEnum = (RoleEnum)Enum.Parse(typeof(RoleEnum), userLogin.role_id.ToString());
switch (roleEnum)
{
case RoleEnum.LOGIN:
result = result.Where(x => x.step1owner == userLogin.id || x.step2owner == userLogin.id);
result = result.Where(x => x.job_status == 11); // Convert.ToInt16(StatusEnum.LOGIN_SELECT_SPEC));
break;
case RoleEnum.CHEMIST:
result = result.Where(x => x.step3owner == userLogin.id);
result = result.Where(x => x.job_status == 12); // Convert.ToInt16(StatusEnum.CHEMIST_TESTING));
break;
case RoleEnum.SR_CHEMIST:
result = result.Where(x => x.step4owner == userLogin.id);
result = result.Where(x => x.job_status == 4); // Convert.ToInt16(StatusEnum.SR_CHEMIST_CHECKING));
break;
case RoleEnum.ADMIN:
result = result.Where(x => x.step6owner == userLogin.id || x.step7owner == userLogin.id);
//result = result.Where(x => x.job_status == Convert.ToInt16(StatusEnum.ADMIN_CONVERT_PDF)||x.job_status == Convert.ToInt16(StatusEnum.ADMIN_CONVERT_WORD));
break;
case RoleEnum.LABMANAGER:
result = result.Where(x => x.step5owner == userLogin.id);
result = result.Where(x => x.job_status == 9); // Convert.ToInt16(StatusEnum.LABMANAGER_CHECKING));
break;
}
DataTable dt = result.ToDataTable();
foreach (DataRow dr in dt.Rows)
{
int jobStatus = Convert.ToInt16(dr["job_status"]);
string logTitle = dr["log_title"].ToString();
string jobRemark = dr["job_remark"].ToString();
string jobNumber = dr["job_number"].ToString();
DateTime date = Convert.ToDateTime(dr["date"].ToString());
job_sample_logs jobSampleLog = new job_sample_logs
{
log_title = logTitle,
job_number = jobNumber,
job_remark = jobRemark,
date = date
};
logs.Add(jobSampleLog);
}
//var result = from l in ctx.job_sample_logs
// join j in ctx.job_sample on l.job_sample_id equals j.ID
// where l.is_active == "0"
// select new
// {
// j.step1owner,
// j.step2owner,
// j.step3owner,
// j.step4owner,
// j.step5owner,
// j.step6owner,
// j.step7owner,
// l.log_title,
// l.job_remark,
// l.date,
// j.job_status,
// j.job_number
// };
//DataTable dt = result.ToDataTable();
//foreach (DataRow dr in dt.Rows)
//{
// bool con1 = (dr["step1owner"] == DBNull.Value) ? false : Convert.ToInt16(dr["step1owner"]) == userLogin.id;
// bool con2 = (dr["step2owner"] == DBNull.Value) ? false : Convert.ToInt16(dr["step2owner"]) == userLogin.id;
// bool con3 = (dr["step3owner"] == DBNull.Value) ? false : Convert.ToInt16(dr["step3owner"]) == userLogin.id;
// bool con4 = (dr["step4owner"] == DBNull.Value) ? false : Convert.ToInt16(dr["step4owner"]) == userLogin.id;
// bool con5 = (dr["step5owner"] == DBNull.Value) ? false : Convert.ToInt16(dr["step5owner"]) == userLogin.id;
// bool con6 = (dr["step6owner"] == DBNull.Value) ? false : Convert.ToInt16(dr["step6owner"]) == userLogin.id;
// bool con7 = (dr["step7owner"] == DBNull.Value) ? false : Convert.ToInt16(dr["step7owner"]) == userLogin.id;
// Boolean isOk = con1 || con2 || con3 || con4 || con5 || con6 || con7;
// int jobStatus = Convert.ToInt16(dr["job_status"]);
// string logTitle = dr["log_title"].ToString();
// string jobRemark = dr["job_remark"].ToString();
// string jobNumber = dr["job_number"].ToString();
// DateTime date = Convert.ToDateTime(dr["date"].ToString());
// StatusEnum status = (StatusEnum)Enum.Parse(typeof(StatusEnum), dr["job_status"].ToString(), true);
// switch (status)
// {
// case StatusEnum.LOGIN_CONVERT_TEMPLATE:
// break;
// case StatusEnum.LOGIN_SELECT_SPEC:
// if (isOk && userLogin.role_id == 2)
// {
// job_sample_logs jobSampleLog = new job_sample_logs
// {
// log_title = String.Format("{0}->{1}", job_number, logTitle),
// job_number = jobNumber,
// job_remark = jobRemark,
// date = date
// };
// logs.Add(jobSampleLog);
// }
// break;
// case StatusEnum.CHEMIST_TESTING:
// if (isOk && userLogin.role_id == 3)
// {
// job_sample_logs jobSampleLog = new job_sample_logs
// {
// log_title = logTitle,
// job_number = jobNumber,
// job_remark = jobRemark,
// date = date
// };
// logs.Add(jobSampleLog);
// }
// break;
// case StatusEnum.SR_CHEMIST_CHECKING:
// if (isOk && userLogin.role_id == 4)
// {
// job_sample_logs jobSampleLog = new job_sample_logs
// {
// log_title = String.Format("{0}->{1}", job_number, logTitle),
// job_number = jobNumber,
// job_remark = jobRemark,
// date = date
// };
// logs.Add(jobSampleLog);
// }
// break;
// case StatusEnum.ADMIN_CONVERT_WORD:
// if (isOk && userLogin.role_id == 5)
// {
// job_sample_logs jobSampleLog = new job_sample_logs
// {
// log_title = String.Format("{0}->{1}", job_number, logTitle),
// job_number = jobNumber,
// job_remark = jobRemark,
// date = date
// };
// logs.Add(jobSampleLog);
// }
// break;
// case StatusEnum.LABMANAGER_CHECKING:
// if (isOk && userLogin.role_id == 5)
// {
// job_sample_logs jobSampleLog = new job_sample_logs
// {
// log_title = String.Format("{0}->{1}", job_number, logTitle),
// job_number = jobNumber,
// job_remark = jobRemark,
// date = date
// };
// logs.Add(jobSampleLog);
// }
// break;
// case StatusEnum.ADMIN_CONVERT_PDF:
// if (isOk && userLogin.role_id == 5)
// {
// job_sample_logs jobSampleLog = new job_sample_logs
// {
// log_title = String.Format("{0}->{1}", job_number, logTitle),
// job_number = jobNumber,
// job_remark = jobRemark,
// date = date
// };
// logs.Add(jobSampleLog);
// }
// break;
// }
//}
return(logs);
}
}
