public TransactionModel GetVistorTracsaction()
{
TransactionModel obj = new TransactionModel();
DateTime startDate = DateTime.Now.Date;
DateTime endDate = DateTime.Now.AddDays(1).Date;
try
{
using (var ctx = new BIG_VMSEntities())
{
obj.ALL_VISITOR_IN = ctx.TRN_VISITOR.Where(o => o.TYPE == "In" || o.TYPE == "Appointment").Count();
obj.TODAY_VISITOR_IN = ctx.TRN_VISITOR.Where(o => ((o.TYPE == "In" || o.TYPE == "Appointment") &&
(o.CREATED_DATE >= startDate && o.CREATED_DATE <= endDate))).Count();
obj.TODAY_VISITOR_OUT = ctx.TRN_VISITOR.Where(o => ((o.TYPE == "Out" || o.TYPE == "AppointmentOut") &&
(o.CREATED_DATE >= startDate && o.CREATED_DATE <= endDate))).Count();
}
}
catch (Exception ex)
{
}
return(obj);
}
public ContainerVisitor UpdateVisitorOutByID(ContainerVisitor obj)
{
var result = new ContainerVisitor();
try
{
using (var ctx = new BIG_VMSEntities())
{
var reTrnVisitor = ctx.TRN_VISITOR.Where(o => o.AUTO_ID == obj.TRN_VISITOR.AUTO_ID).FirstOrDefault();
if (reTrnVisitor != null)
{
reTrnVisitor.STATUS = 2;
ctx.SaveChanges();
result.Status = true;
}
else
{
result.Status = false;
}
}
}
catch (Exception ex)
{
result.Status = false;
result.ExceptionMessage = ex.Message;
}
return(result);
}
public ContainerVisitor GetLastUserNo()
{
var result = new ContainerVisitor();
using (var ctx = new BIG_VMSEntities())
{
var reTrnVisitor = ctx.TRN_VISITOR.Where(o => o.MONTH == DateTime.Today.Month && o.YEAR == DateTime.Today.Year).OrderByDescending(x => x.NO).FirstOrDefault();
if (reTrnVisitor != null)
{
result.TRN_VISITOR = reTrnVisitor;
result.Status = true;
}
else
{
TRN_VISITOR visit = new TRN_VISITOR()
{
AUTO_ID = 0,
NO = 0,
};
result.TRN_VISITOR = visit;
result.Status = true;
}
}
return(result);
}
public List <ReportParameter> GetReportParameter()
{
List <ReportParameter> listData = new List <ReportParameter>();
try
{
using (var ctx = new BIG_VMSEntities())
{
var reParameter = ctx.SYS_CONFIGURATION.Where(x => x.MODULE == "SLIP").ToList();
if (reParameter.Count > 0)
{
listData = (from item in reParameter
select new ReportParameter
{
MODULE = item.MODULE,
NAME = item.NAME,
VALUE = item.VALUE,
DESCRIPTION = item.DESCRIPTION
}).ToList();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(listData);
}
public ContainerVisitor Update(ContainerVisitor obj)
{
var result = new ContainerVisitor();
using (var ctx = new BIG_VMSEntities())
{
try
{
var visitorObj = obj.TRN_VISITOR;
var updateData = ctx.TRN_VISITOR.Where(o => o.AUTO_ID == obj.TRN_VISITOR.AUTO_ID).FirstOrDefault();
if (updateData != null)
{
if (visitorObj.TRN_ATTACHEDMENT.Count > 0)
{
var attach = ctx.TRN_ATTACHEDMENT.Where(o => o.VISITOR_ID == visitorObj.AUTO_ID).FirstOrDefault();
if (attach != null)
{
attach.CONTACT_PHOTO = visitorObj.TRN_ATTACHEDMENT.FirstOrDefault().CONTACT_PHOTO;
attach.ID_CARD_PHOTO = visitorObj.TRN_ATTACHEDMENT.FirstOrDefault().ID_CARD_PHOTO;
}
else
{
var att = new TRN_ATTACHEDMENT();
att.CONTACT_PHOTO = visitorObj.TRN_ATTACHEDMENT.FirstOrDefault().CONTACT_PHOTO;
att.ID_CARD_PHOTO = visitorObj.TRN_ATTACHEDMENT.FirstOrDefault().ID_CARD_PHOTO;
att.VISITOR_ID = visitorObj.AUTO_ID;
}
}
updateData.ID_CARD = visitorObj.ID_CARD;
updateData.FIRST_NAME = visitorObj.FIRST_NAME;
updateData.LAST_NAME = visitorObj.LAST_NAME;
updateData.CAR_TYPE_ID = visitorObj.CAR_TYPE_ID;
updateData.LICENSE_PLATE = visitorObj.LICENSE_PLATE;
updateData.LICENSE_PLATE_PROVINCE_ID = visitorObj.LICENSE_PLATE_PROVINCE_ID;
updateData.REASON_ID = visitorObj.REASON_ID;
updateData.CONTACT_EMPLOYEE_ID = visitorObj.CONTACT_EMPLOYEE_ID;
updateData.UPDATED_DATE = DateTime.Now;
updateData.UPDATED_BY = visitorObj.UPDATED_BY;
ctx.SaveChanges();
result.Status = true;
result.Message = "บันทึกข้อมูลเรียบร้อย";
}
else
{
result.Status = false;
result.Message = "แก้ไขไม่สำเร็จ";
}
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message.ToString();
}
}
return(result);
}
public ContainerVisitor GetVisitorByAutoID(int auto_id)
{
var result = new ContainerVisitor();
try
{
using (var ctx = new BIG_VMSEntities())
{
var reTrnVisitor = ctx.TRN_VISITOR
.Include("MAS_EMPLOYEE")
.Include("MAS_REASON")
.Include("MAS_PROVINCE")
.Include("MAS_CAR_TYPE")
.Include("TRN_ATTACHEDMENT")
.Where(x => x.AUTO_ID == auto_id).FirstOrDefault();
if (reTrnVisitor != null)
{
//var x = string.Join(",",reTrnVisitor.ID_CARD_PHOTO);
result.TRN_VISITOR = reTrnVisitor;
result.Status = true;
}
}
}
catch (Exception ex)
{
result.Status = false;
result.ExceptionMessage = ex.Message;
}
return(result);
}
public ContainerVisitor GetItem(ContainerVisitor obj)
{
var result = new ContainerVisitor();
try
{
using (var ctx = new BIG_VMSEntities())
{
var reTrnVisitor = ctx.TRN_VISITOR.OrderByDescending(x => x.NO).FirstOrDefault();
if (reTrnVisitor != null)
{
result.TRN_VISITOR = reTrnVisitor;
result.Status = true;
}
else
{
TRN_VISITOR visit = new TRN_VISITOR()
{
AUTO_ID = 0,
NO = 0,
};
result.TRN_VISITOR = visit;
result.Status = true;
}
}
}
catch (Exception ex)
{
result.Status = false;
result.ExceptionMessage = ex.Message;
}
return(result);
}
public ContainerVisitor Delete(ContainerVisitor obj)
{
var result = new ContainerVisitor();
using (var ctx = new BIG_VMSEntities())
{
try
{
var deleteAttach = ctx.TRN_ATTACHEDMENT.Where(o => o.VISITOR_ID == obj.TRN_VISITOR.AUTO_ID).FirstOrDefault();
var deleteData = ctx.TRN_VISITOR.Where(o => o.AUTO_ID == obj.TRN_VISITOR.AUTO_ID).FirstOrDefault();
if (deleteAttach != null)
{
ctx.TRN_ATTACHEDMENT.Remove(deleteAttach);
}
ctx.TRN_VISITOR.Remove(deleteData);
ctx.SaveChanges();
result.Status = true;
result.Message = "Delete Successful";
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message.ToString();
}
}
return(result);
}
public ContainerBlackList Retrieve(ContainerBlackList obj)
{
var result = new ContainerBlackList();
using (var ctx = new BIG_VMSEntities())
{
try
{
var blackList = ctx.TRN_BLACKLIST.ToList();
if (obj.Filter != null)
{
if (!string.IsNullOrEmpty(obj.Filter.ID_CARD))
{
blackList = blackList.Where(o => o.ID_CARD == obj.Filter.ID_CARD).ToList();
}
}
result.ResultObj = blackList;
result.Status = true;
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message.ToString();
}
}
return(result);
}
public ContainerBlackList GetBlackListByIdCard(string IdCard)
{
var result = new ContainerBlackList();
bool isBlackList = false;
using (var ctx = new BIG_VMSEntities())
{
try
{
var blackList = ctx.TRN_BLACKLIST.Where(o => o.ID_CARD == IdCard).OrderByDescending(x => x.AUTO_ID).FirstOrDefault();
if (blackList != null)
{
isBlackList = true;
result.ResultObj = isBlackList;
result.TRN_BLACKLIST = blackList;
}
else
{
result.ResultObj = isBlackList;
}
result.Status = true;
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message.ToString();
}
}
return(result);
}
public IQueryable <TRN_VISITOR> GetListVisitorQuery(ContainerVisitor obj)
{
try
{
var ctx = new BIG_VMSEntities();
var filter = obj.Filter;
IQueryable <TRN_VISITOR> query = ctx.TRN_VISITOR;
if (obj.Filter != null)
{
if (!string.IsNullOrEmpty(filter.ID_CARD))
{
query = query.Where(o => o.ID_CARD.Contains(filter.ID_CARD));
}
if (!string.IsNullOrEmpty(filter.TYPE))
{
query = query.Where(o => o.TYPE == filter.TYPE);
}
if (!string.IsNullOrEmpty(filter.LICENSE_PLATE))
{
query = query.Where(o => o.LICENSE_PLATE.Contains(filter.LICENSE_PLATE));
}
if (filter.NO > 0)
{
query = query.Where(o => o.NO == filter.NO);
}
if (!string.IsNullOrEmpty(filter.FIRST_NAME))
{
query = query.Where(o => o.FIRST_NAME.Contains(filter.FIRST_NAME));
}
if (!string.IsNullOrEmpty(filter.LAST_NAME))
{
query = query.Where(o => o.LAST_NAME.Contains(filter.LAST_NAME));
}
if (filter.DATE_TO != null && filter.DATE_TO != DateTime.MinValue)
{
var endDate = filter.DATE_TO.AddDays(1);
query = query.Where(x => x.CREATED_DATE >= filter.DATE_TO && x.CREATED_DATE <= endDate);
}
if (string.IsNullOrEmpty(filter.FIRST_NAME) && string.IsNullOrEmpty(filter.LAST_NAME) &&
string.IsNullOrEmpty(filter.LICENSE_PLATE) && filter.NO == 0)
{
var date = DateTime.Now.AddDays(-5);
query = query.Where(x => x.CREATED_DATE >= date);
}
query.OrderByDescending(o => o.UPDATED_DATE);
return(query);
}
else
{
query.OrderByDescending(o => o.UPDATED_DATE);
return(query);
}
}
catch
{
throw;
}
}
public IQueryable <TRN_APPOINTMENT> GetListAppointmentQuery(ContainerAppointment obj)
{
try
{
var ctx = new BIG_VMSEntities();
var filter = obj.Filter;
IQueryable <TRN_APPOINTMENT> query = ctx.TRN_APPOINTMENT;
if (filter != null)
{
if (filter.AUTO_ID > 0)
{
query = query.Where(o => o.AUTO_ID == filter.AUTO_ID);
}
if (!string.IsNullOrEmpty(filter.ID_CARD))
{
query = query.Where(o => o.REQUEST_ID_CARD.Contains(filter.ID_CARD));
}
if (!string.IsNullOrEmpty(filter.LICENSE_PLATE))
{
query = query.Where(o => o.REQUEST_LICENSE_PLATE.Contains(filter.LICENSE_PLATE));
}
if (!string.IsNullOrEmpty(filter.FIRST_NAME))
{
query = query.Where(o => o.REQUEST_FIRST_NAME.Contains(filter.FIRST_NAME));
}
if (!string.IsNullOrEmpty(filter.LAST_NAME))
{
query = query.Where(o => o.REQUEST_LAST_NAME.Contains(filter.LAST_NAME));
}
if (filter.CONTACT_DATE != null && filter.CONTACT_DATE != DateTime.MinValue)
{
var dateFrom = filter.CONTACT_DATE.AddDays(1);
var dateTo = filter.CONTACT_DATE.AddDays(-1);
query = query.Where(o => o.CONTACT_DATE < dateFrom && o.CONTACT_DATE >= dateTo);
}
return(query);
}
else
{
return(query);
}
}
catch
{
throw;
}
}
public ContainerAppointment GetItem(ContainerAppointment obj)
{
var result = new ContainerAppointment();
using (var ctx = new BIG_VMSEntities())
{
try
{
result.TRN_APPOINTMENT = GetListAppointmentQuery(obj).FirstOrDefault();
}
catch (Exception ex)
{
}
}
return(result);
}
public ContainerCarBrand GetAutoIDFromCarModel(string model)
{
ContainerCarBrand result = new ContainerCarBrand();
try
{
using (BIG_VMSEntities ctx = new BIG_VMSEntities())
{
var data = ctx.MAS_CAR_MODEL.Where(o => o.NAME == model).FirstOrDefault();
result.ResultObj = data.AUTO_ID;
result.Status = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public ContainerVisitor GetRegularyVisitor()
{
var result = new ContainerVisitor();
try
{
using (var ctx = new BIG_VMSEntities())
{
var listVisitorGroup = new List <VisitorGroupModel>();
var group = ctx.TRN_VISITOR.Where(x => (x.TYPE == "In" || x.TYPE == "Regulary")).GroupBy(o => o.ID_CARD)
.Select(o => o.FirstOrDefault()).ToList();
var allIdCard = group.Select(o => o.ID_CARD).ToList();
foreach (var id in allIdCard)
{
VisitorGroupModel visitor = new VisitorGroupModel();
visitor.Key = id;
visitor.Count = ctx.TRN_VISITOR.Where(x => (x.TYPE == "In" || x.TYPE == "Regulary")).Count();
listVisitorGroup.Add(visitor);
}
listVisitorGroup = listVisitorGroup.OrderByDescending(o => o.Count).Take(10).ToList();
var listTopIdCard = listVisitorGroup.Select(x => x.Key).ToList();
var listData = ctx.TRN_VISITOR.Where(x => listTopIdCard.Contains(x.ID_CARD) && ((x.TYPE == "In" || x.TYPE == "Regulary"))).GroupBy(o => o.ID_CARD).Select(o => o.FirstOrDefault()).ToList();
result.ResultObj = listData;
result.Status = true;
result.Message = "Retrive Data Successful";
}
}
catch (Exception ex)
{
result.Status = false;
result.ExceptionMessage = ex.Message;
}
return(result);
}
public ContainerAppointment Create(ContainerAppointment obj)
{
var result = new ContainerAppointment();
using (var ctx = new BIG_VMSEntities())
{
try
{
ctx.TRN_APPOINTMENT.Add(obj.TRN_APPOINTMENT);
ctx.SaveChanges();
result.Status = true;
result.Message = "Save Successful";
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message.ToString();
}
}
return(result);
}
public ContainerCarModel Create(ContainerCarModel obj)
{
var result = new ContainerCarModel();
using (var ctx = new BIG_VMSEntities())
{
try
{
ctx.MAS_CAR_MODEL.Add(obj.MAS_CAR_MODEL);
ctx.SaveChanges();
result.Status = true;
result.Message = "Save Successful";
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message.ToString();
}
}
return(result);
}
public ContainerBlackList Create(ContainerBlackList obj)
{
var result = new ContainerBlackList();
using (var ctx = new BIG_VMSEntities())
{
try
{
ctx.TRN_BLACKLIST.Add(obj.TRN_BLACKLIST);
ctx.SaveChanges();
result.Status = true;
result.Message = "Save Successful";
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message.ToString();
}
}
return(result);
}
public ContainerAuthentication Create(ContainerAuthentication obj)
{
var result = new ContainerAuthentication();
using (var ctx = new BIG_VMSEntities())
{
try
{
ctx.MEMBER_LOGON.Add(obj.MEMBER_LOGON);
ctx.SaveChanges();
result.Status = true;
result.Message = "Save Successful";
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message.ToString();
}
}
return(result);
}
public ContainerAppointment UpdateStatus(int autoID)
{
var result = new ContainerAppointment();
using (var ctx = new BIG_VMSEntities())
{
try
{
var data = ctx.TRN_APPOINTMENT.Where(o => o.AUTO_ID == autoID).FirstOrDefault();
data.STATUS = "เข้าพบแล้ว";
ctx.SaveChanges();
result.Status = true;
result.Message = "Update Successful";
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message.ToString();
}
}
return(result);
}
public ContainerBlackList Delete(ContainerBlackList obj)
{
var result = new ContainerBlackList();
using (var ctx = new BIG_VMSEntities())
{
try
{
var deleteData = ctx.TRN_BLACKLIST.Where(o => o.AUTO_ID == obj.TRN_BLACKLIST.AUTO_ID).FirstOrDefault();
ctx.TRN_BLACKLIST.Remove(deleteData);
ctx.SaveChanges();
result.Status = true;
result.Message = "Delete Successful";
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message.ToString();
}
}
return(result);
}
public ContainerAuthentication Retrieve(ContainerAuthentication authen)
{
var result = new ContainerAuthentication();
try
{
using (BIG_VMSEntities ctx = new BIG_VMSEntities())
{
var filter = (AuthenticationFilter)authen.Filter;
var obj = ctx.MEMBER_LOGON.FirstOrDefault(x => x.USERNAME == filter.UserName && x.PASSWORD == filter.Password);
if (obj != null)
{
result.ResultObj = obj;
result.Status = true;
}
else
{
var chkUsername = ctx.MEMBER_LOGON.FirstOrDefault(x => x.USERNAME == filter.UserName);
if (chkUsername != null)
{
result.Message = " USERNAME ไม่ถูกต้อง";
}
else
{
result.Message = " PASSWORD ไม่ถูกต้อง";
}
}
}
}
catch (Exception ex)
{
result.Status = false;
result.ExceptionMessage = ex.Message;
}
return(result);
}
public List <ComboBoxItem> GetComboCarBrandByTypeID(int carTypeID, string filter)
{
List <ComboBoxItem> listData = new List <ComboBoxItem>();
try
{
using (BIG_VMSEntities ctx = new BIG_VMSEntities())
{
var list = ctx.MAS_CAR_BRAND.Where(o => o.TYPE_ID == carTypeID && o.NAME.Contains(filter)).OrderBy(o => o.SHOW_SEQ).ToList();
foreach (var item in list)
{
ComboBoxItem data = new ComboBoxItem();
data.Text = item.NAME;
data.Value = item.AUTO_ID;
listData.Add(data);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(listData);
}
public List <ComboBoxItem> GetComboReason()
{
List <ComboBoxItem> listData = new List <ComboBoxItem>();
try
{
using (BIG_VMSEntities ctx = new BIG_VMSEntities())
{
var list = ctx.MAS_REASON.OrderBy(o => o.REASON).ToList();
foreach (var item in list)
{
ComboBoxItem data = new ComboBoxItem();
data.Text = item.REASON;
data.Value = item.AUTO_ID;
listData.Add(data);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(listData);
}
public List <ComboBoxItem> GetComboProvince(string filter)
{
List <ComboBoxItem> listData = new List <ComboBoxItem>();
try
{
using (BIG_VMSEntities ctx = new BIG_VMSEntities())
{
var list = ctx.MAS_PROVINCE.Where(o => o.NAME.Contains(filter)).OrderBy(o => o.NAME).ToList();
foreach (var item in list)
{
ComboBoxItem data = new ComboBoxItem();
data.Text = item.NAME;
data.Value = item.AUTO_ID;
listData.Add(data);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(listData);
}
public List <ComboBoxItem> GetComboProvincePriority()
{
List <ComboBoxItem> listData = new List <ComboBoxItem>();
try
{
using (BIG_VMSEntities ctx = new BIG_VMSEntities())
{
var list = ctx.MAS_PROVINCE.OrderByDescending(o => o.PRIORITY).ThenBy(o => o.NAME).Take(10).ToList();
foreach (var item in list)
{
ComboBoxItem data = new ComboBoxItem();
data.Text = item.NAME;
data.Value = item.AUTO_ID;
listData.Add(data);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(listData);
}
public List <ComboBoxItem> GetComboEmployeeByDepartmentID(int deptID, string filter)
{
List <ComboBoxItem> listData = new List <ComboBoxItem>();
try
{
using (BIG_VMSEntities ctx = new BIG_VMSEntities())
{
var list = ctx.MAS_EMPLOYEE.Where(o => o.DEPARTMENT_ID == deptID && (o.FIRST_NAME.Contains(filter) || o.LAST_NAME.Contains(filter))).ToList();
foreach (var item in list)
{
ComboBoxItem data = new ComboBoxItem();
data.Text = item.FIRST_NAME + " " + item.LAST_NAME;
data.Value = item.AUTO_ID;
listData.Add(data);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(listData);
}
public List <ComboBoxItem> GetComboDepartment(string filter)
{
List <ComboBoxItem> listData = new List <ComboBoxItem>();
try
{
using (BIG_VMSEntities ctx = new BIG_VMSEntities())
{
var list = ctx.MAS_DEPARTMENT.Where(o => o.NAME.Contains(filter));
foreach (var item in list)
{
ComboBoxItem data = new ComboBoxItem();
data.Text = item.NAME;
data.Value = item.AUTO_ID;
listData.Add(data);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(listData.OrderByDescending(x => x.Value).ToList());
}
public List <ComboBoxItem> GetComboReasonByDepartmentID(int deptID)
{
List <ComboBoxItem> listData = new List <ComboBoxItem>();
try
{
using (BIG_VMSEntities ctx = new BIG_VMSEntities())
{
var list = ctx.MAS_REASON.Where(o => o.DEPT_ID == deptID).ToList();
foreach (var item in list)
{
ComboBoxItem data = new ComboBoxItem();
data.Text = item.REASON;
data.Value = item.AUTO_ID;
listData.Add(data);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(listData);
}
public List <ComboBoxItem> GetComboEmployee()
{
List <ComboBoxItem> listData = new List <ComboBoxItem>();
try
{
using (BIG_VMSEntities ctx = new BIG_VMSEntities())
{
var list = ctx.MAS_EMPLOYEE.ToList();
foreach (var item in list)
{
ComboBoxItem data = new ComboBoxItem();
data.Text = "คุณ" + item.FIRST_NAME + " " + item.LAST_NAME;
data.Value = item.AUTO_ID;
listData.Add(data);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(listData);
}