public IQueryable <TimeSheetMasterView> ShowTimeSheetStatus(string sortColumn, string sortColumnDir, string Search, int UserID, int TimeSheetStatus)
{
var _context = new DatabaseContext();
var IQueryabletimesheet = (from timesheetmaster in _context.TimeSheetMaster
where timesheetmaster.UserID == UserID && timesheetmaster.TimeSheetStatus == TimeSheetStatus
select new TimeSheetMasterView
{
TimeSheetStatus = timesheetmaster.TimeSheetStatus == 1 ? "Submitted" : timesheetmaster.TimeSheetStatus == 2 ? "Approved" : "Rejected",
Comment = timesheetmaster.Comment,
TimeSheetMasterID = timesheetmaster.TimeSheetMasterID,
FromDate = SqlFunctions.DateName("day", timesheetmaster.FromDate).Trim() + "/" +
SqlFunctions.StringConvert((double)timesheetmaster.FromDate.Value.Month).TrimStart() + "/" +
SqlFunctions.DateName("year", timesheetmaster.FromDate),
ToDate = SqlFunctions.DateName("day", timesheetmaster.ToDate).Trim() + "/" +
SqlFunctions.StringConvert((double)timesheetmaster.ToDate.Value.Month).TrimStart() + "/" +
SqlFunctions.DateName("year", timesheetmaster.ToDate),
CreatedOn = SqlFunctions.DateName("day", timesheetmaster.CreatedOn).Trim() + "/" +
SqlFunctions.StringConvert((double)timesheetmaster.CreatedOn.Value.Month).TrimStart() + "/" +
SqlFunctions.DateName("year", timesheetmaster.CreatedOn),
TotalHours = timesheetmaster.TotalHours
});
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
IQueryabletimesheet = IQueryabletimesheet.OrderBy(sortColumn + " " + sortColumnDir);
}
if (!string.IsNullOrEmpty(Search))
{
IQueryabletimesheet = IQueryabletimesheet.Where(m => m.FromDate == Search);
}
return(IQueryabletimesheet);
}
public List <ApplicationViewModel> GetList()
{
List <ApplicationViewModel> result = context.Applications.Select(rec => new
ApplicationViewModel
{
ApplicationId = rec.ApplicationId,
ResourseId = rec.ResourseId,
Count = rec.Count,
Summa = rec.Sum,
Status = rec.Status.ToString(),
DateCreate = SqlFunctions.DateName("dd", rec.DateCreate) + " " +
SqlFunctions.DateName("mm", rec.DateCreate) + " " +
SqlFunctions.DateName("yyyy", rec.DateCreate),
DateImplement = rec.DateImplement == null ? "" :
SqlFunctions.DateName("dd",
rec.DateImplement.Value) + " " +
SqlFunctions.DateName("mm",
rec.DateImplement.Value) + " " +
SqlFunctions.DateName("yyyy",
rec.DateImplement.Value),
ResourseName = rec.Resourse.ResourseName
})
.ToList();
return(result);
}
public ActionResult reporteDUp(int Id)
{
List <ReporteDUpItem> lst = null;
using (AppDBContext db = new AppDBContext())
{
lst = (from d in db.Documentos
from u in db.Usuarios
where d.idFlujoDocumento == null &&
d.idUsuario == u.idUsuario &&
d.idUsuario == Id
select new ReporteDUpItem
{
Documento = d.nombreDocumentos,
Fecha = SqlFunctions.DateName("day", d.created_at) + "/" + SqlFunctions.DateName("month", d.created_at) + "/" + SqlFunctions.DateName("year", d.created_at),
Usuario = u.nombre + " " + u.apellido
}).ToList();
}
//Creamos el PDF de la vista html
var reporte = new ViewAsPdf("reporteDUp", lst)
{
PageSize = Rotativa.Options.Size.Letter,
PageMargins = new Rotativa.Options.Margins(10, 20, 20, 20)
};
return(reporte);
}
public List <OrderViewModel> GetList()
{
List <OrderViewModel> result = context.Orders.Select(rec => new OrderViewModel
{
Id = rec.Id,
AdminId = rec.AdminId,
OrderListId = rec.OrderListId,
DateCreate = SqlFunctions.DateName("dd", rec.DateCreate) + " " +
SqlFunctions.DateName("mm", rec.DateCreate) + " " +
SqlFunctions.DateName("yyyy", rec.DateCreate),
DateImplement = rec.DateImplement == null ? "" :
SqlFunctions.DateName("dd",
rec.DateImplement.Value) + " " +
SqlFunctions.DateName("mm",
rec.DateImplement.Value) + " " +
SqlFunctions.DateName("yyyy",
rec.DateImplement.Value),
Status = rec.Status.ToString(),
Count = rec.Count,
Sum = rec.Sum,
AdminFIO = rec.Admin.AdminFIO,
OrderListName = rec.OrderList.OrderListName
})
.ToList();
return(result);
}
public List <OrderViewModel> GetList()
{
List <OrderViewModel> result = context.Orders
.Select(rec => new OrderViewModel
{
id = rec.id,
clientId = rec.clientId,
DateCreate = SqlFunctions.DateName("dd", rec.DateCreate) + " " +
SqlFunctions.DateName("mm", rec.DateCreate) + " " +
SqlFunctions.DateName("yyyy", rec.DateCreate),
status = rec.status.ToString(),
clientName = rec.client.clientFirstName,
services = rec.orderServices.Select(os => new ServiceViewModel
{
id = os.serviceId,
serviceName = os.service.serviceName,
price = os.service.price,
ServiceResources = os.service.serviceResources.Select(r => new ServiceResourceViewModel
{
id = r.id,
resourceId = r.resourceId,
resourceName = r.resource.resourceName,
}).ToList()
}).ToList(),
number = rec.number
})
.ToList();
return(result);
}
public string GetDATASO(string BASE) //ข้อมูล P1 || P2
{
var dt = DateTime.UtcNow.Date.AddDays(-2);
var Get = (from M_SO in DbFile.OESO
join M_CUSs in DbFile.ARMAS on M_SO.CUSCOD equals M_CUSs.cuscod into M_CUS1
from M_CUS in M_CUS1.DefaultIfEmpty()
where M_SO.SODAT >= dt
orderby M_SO.SONUM ascending
select new
{
M_SO.SONUM,
//M_SO.SODAT,
SODAT = SqlFunctions.Replicate("0", 2 - SqlFunctions.DateName("dd", M_SO.SODAT).Trim().Length) +
SqlFunctions.DateName("dd", M_SO.SODAT) + "/" +
// SqlFunctions.Replicate("/", 2 - SqlFunctions.StringConvert((double)M_SO.SODAT.Value.Month).TrimStart().Length) +
SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double)M_SO.SODAT.Value.Month).TrimStart().Length) +
SqlFunctions.StringConvert((double)M_SO.SODAT.Value.Month).TrimStart() + "/" +
// SqlFunctions.Replicate("/", 2 - SqlFunctions.StringConvert((double)M_SO.SODAT.Value.Month).TrimStart().Length) +
SqlFunctions.DateName("year", M_SO.SODAT).Trim(),
M_CUS.cusnam
}).ToList();
string jsonlog = new JavaScriptSerializer().Serialize(Get);
return(jsonlog);
// return View();
}
public List <FridgeViewModel> GetList()
{
List <FridgeViewModel> result = context.Fridges.Select(rec => new FridgeViewModel
{
Id = rec.Id,
FridgeName = rec.FridgeName,
FridgeProducts = context.FridgeProducts
.Where(recPC => recPC.FridgeId == rec.Id)
.Select(recPC => new FridgeProductViewModel
{
Id = recPC.Id,
FridgeId = recPC.FridgeId,
ProductId = recPC.ProductId,
ProductName = recPC.Product.ProductName,
Count = recPC.Count,
ReceiptDate = SqlFunctions.DateName("dd", recPC.ReceiptDate) + " " +
SqlFunctions.DateName("mm", recPC.ReceiptDate) + " " +
SqlFunctions.DateName("yyyy", recPC.ReceiptDate),
FreshDate = recPC.FreshDate,
DateNotFresh = recPC.DateNotFresh == null ? "" :
SqlFunctions.DateName("dd",
recPC.DateNotFresh.Value) + " " +
SqlFunctions.DateName("mm",
recPC.DateNotFresh.Value) + " " +
SqlFunctions.DateName("yyyy",
recPC.DateNotFresh.Value),
FreshStatus = recPC.FreshStatus.ToString()
})
.ToList()
})
.ToList();
return(result);
}
private IQueryable <ProjectView> SelectProjectView(Expression <Func <ProjectView, bool> > Expression, string Sorting = null, string Filtering = null)
{
var Query = from tbl in DbSet
.Include(c => c.Customer)
.Include(c => c.Department)
.Include(c => c.ProjectType)
.Include(c => c.FocalPoint)
.Include(c => c.CompanyFocalPoint)
let ptCount = (from pt in Context.Set <ProjectTechnology>() where tbl.ID == pt.ProjectID select pt).Count()
select new ProjectView()
{
ID = tbl.ID,
Name = tbl.Name,
CustomerID = tbl.CustomerID,
CustomerName = tbl.Customer.Name,
DepartmentID = tbl.DepartmentID,
DepartmentName = tbl.Department.Name,
ProjectTypeID = tbl.ProjectTypeID,
ProjectTypeName = tbl.ProjectType.Name,
FocalPointID = tbl.FocalPointID,
FocalPointName = tbl.FocalPoint.FirstName + " " + tbl.FocalPoint.LastName,
CompanyFocalPointID = tbl.CompanyFocalPointID,
CompanyFocalPointName = tbl.CompanyFocalPoint.FirstName + " " + tbl.CompanyFocalPoint.LastName,
EndDate = tbl.EndDate,
IsActive = tbl.IsActive,
Risk = tbl.Risk,
IsAllowDelete = ptCount == 0
};
if ((Filtering ?? "") != "")
{
Expression = Expression.And(tbl =>
tbl.ID.ToString().Contains(Filtering) ||
tbl.Name.ToString().Contains(Filtering) ||
tbl.CustomerName.ToString().Contains(Filtering) ||
tbl.DepartmentName.ToString().Contains(Filtering) ||
tbl.ProjectTypeName.ToString().Contains(Filtering) ||
tbl.FocalPointName.ToString().Contains(Filtering) ||
((SqlFunctions.DateName("mm", tbl.EndDate).Substring(0, 3).ToString() + " " + SqlFunctions.DateName("dd", tbl.EndDate) + " ," + SqlFunctions.DateName("yyyy", tbl.EndDate)).ToString()).Contains(Filtering) ||
(tbl.IsActive == true ? "true" : "false").Contains(Filtering) ||
tbl.Risk.ToString().Contains(Filtering)
);
}
if (Expression != null)
{
Query = Query.Where(Expression);
}
if (Sorting != null)
{
if (!Sorting.StartsWith("ID"))
{
Sorting = Sorting.Replace("ID", "Name");
}
Query = Query.GetOrderByQuery(Sorting);
}
return(Query);
}
public List <ModelBookingView> getList()
{
List <ModelBookingView> result = context.Bookings
.Select(rec => new ModelBookingView
{
ID = rec.ID,
CustomerID = rec.CustomerID,
OutputID = rec.OutputID,
ExecutorID = rec.ExecutorID,
DateOfCreate = SqlFunctions.DateName("dd", rec.DateOfCreate) + " " +
SqlFunctions.DateName("mm", rec.DateOfCreate) + " " +
SqlFunctions.DateName("yyyy", rec.DateOfCreate),
DateOfImplement = rec.DateOfImplement == null ? "" :
SqlFunctions.DateName("dd", rec.DateOfImplement.Value) + " " +
SqlFunctions.DateName("mm", rec.DateOfImplement.Value) + " " +
SqlFunctions.DateName("yyyy", rec.DateOfImplement.Value),
Status = rec.Status.ToString(),
Count = rec.Count,
Summa = rec.Summa,
CustomerFullName = rec.Customer.CustomerFullName,
OutputName = rec.Output.OutputName,
ExecutorName = rec.Executor.ExecutorFullName
})
.ToList();
return(result);
}
/// <summary>
/// 独立新访客
/// </summary>
public ChartItemEntity GetNewUV(DateTime start, DateTime end)
{
var ed = end.AddDays(1);
var onlyOne = from x in this.DbSet
where (x.VisitTime < ed)
group x by x.VisitId into g
where g.Count() == 1
select new
{
VisitId = g.Key
};
var query = GetDataSource(start, end);
var newuv = (from x in query
where (from y in onlyOne
where y.VisitId == x.VisitId
select y).Any()
group x by new
{
Year = SqlFunctions.DateName("yyyy", x.VisitTime),
Month = SqlFunctions.DateName("MM", x.VisitTime),
Day = SqlFunctions.DateName("dd", x.VisitTime),
x.VisitId
} into g
select new
{
Name = g.Key.Year + "-" + g.Key.Month + "-" + g.Key.Day + "-" + g.Key.VisitId,
Data = g.Count()
}).Count();
return(new ChartItemEntity {
Name = "NEWUV", Data = newuv
});
}
public List <TimeSheetDetailsView> TimesheetDetailsbyTimeSheetMasterID(int TimeSheetMasterID)
{
using (var _context = new DatabaseContext())
{
var data = (from timesheet in _context.TimeSheetDetails
join project in _context.ProjectMaster on timesheet.ProjectID equals project.ProjectID
where timesheet.TimeSheetMasterID == TimeSheetMasterID
select new TimeSheetDetailsView
{
TimeSheetID = timesheet.TimeSheetID,
CreatedOn = SqlFunctions.DateName("day", timesheet.CreatedOn).Trim() + "/" +
SqlFunctions.StringConvert((double)timesheet.CreatedOn.Value.Month).TrimStart() + "/" +
SqlFunctions.DateName("year", timesheet.CreatedOn),
Period = SqlFunctions.DateName("day", timesheet.Period).Trim() + "/" +
SqlFunctions.StringConvert((double)timesheet.Period.Value.Month).TrimStart() + "/" +
SqlFunctions.DateName("year", timesheet.Period),
DaysofWeek = timesheet.DaysofWeek,
Hours = timesheet.Hours,
ProjectName = project.ProjectName,
TimeSheetMasterID = timesheet.TimeSheetMasterID
}).ToList();
return(data);
}
}
public List <ClientOrdersViewModel> GetClientOrders(ReportBindingModel model, int clientId)
{
return(context.Orders
.Where(rec => rec.DateCreate >= model.DateFrom &&
rec.DateCreate <= model.DateTo &&
rec.ClientId == clientId)
.Select(rec => new ClientOrdersViewModel
{
ClientName = rec.Client.ClientFIO,
DateCreateOrder =
SqlFunctions.DateName("dd", rec.DateCreate) + " " +
SqlFunctions.DateName("mm", rec.DateCreate) + " " +
SqlFunctions.DateName("yyyy", rec.DateCreate),
TotalSum = rec.TotalSum,
StatusOrder = rec.OrderStatus.ToString(),
OrderCars = context.OrderCars.Where(recPC => recPC.OrderId == rec.Id)
.Select(recPC => new OrderCarViewModel
{
Id = recPC.Id,
CarId = recPC.CarId,
OrderId = recPC.OrderId,
CarName = recPC.Car.CarName,
Amount = recPC.Amount
})
.ToList()
})
.ToList());
}
/// <summary>
/// The ShowNotifications
/// </summary>
/// <param name="sortColumn">The sortColumn<see cref="string"/></param>
/// <param name="sortColumnDir">The sortColumnDir<see cref="string"/></param>
/// <param name="Search">The Search<see cref="string"/></param>
/// <returns>The <see cref="IQueryable{NotificationsTB_ViewModel}"/></returns>
public IQueryable <NotificationsTB_ViewModel> ShowNotifications(string sortColumn, string sortColumnDir, string Search)
{
var _context = new DatabaseContext();
var IQueryableNotifications = (from notification in _context.NotificationsTBs
select new NotificationsTB_ViewModel
{
CreatedOn = SqlFunctions.DateName("day", notification.CreatedOn).Trim() + "/" +
SqlFunctions.StringConvert((double)notification.CreatedOn.Value.Month).TrimStart() + "/" +
SqlFunctions.DateName("year", notification.CreatedOn),
Message = notification.Message,
NotificationsID = notification.NotificationsID,
Status = notification.Status,
FromDate = SqlFunctions.DateName("day", notification.FromDate).Trim() + "/" +
SqlFunctions.StringConvert((double)notification.FromDate.Value.Month).TrimStart() + "/" +
SqlFunctions.DateName("year", notification.FromDate) + " " + SqlFunctions.DateName("hh", notification.FromDate).Trim() + ":" + SqlFunctions.DateName("n", notification.FromDate).Trim() + ":" + SqlFunctions.DateName("s", notification.FromDate).Trim(),
ToDate = SqlFunctions.DateName("day", notification.ToDate).Trim() + "/" +
SqlFunctions.StringConvert((double)notification.ToDate.Value.Month).TrimStart() + "/" +
SqlFunctions.DateName("year", notification.ToDate) + " " + SqlFunctions.DateName("hh", notification.ToDate).Trim() + ":" + SqlFunctions.DateName("n", notification.ToDate).Trim() + ":" + SqlFunctions.DateName("s", notification.ToDate).Trim(),
Min = SqlFunctions.DateDiff("n", notification.FromDate.ToString(), notification.ToDate.ToString())
});
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
IQueryableNotifications = IQueryableNotifications.OrderBy(sortColumn + " " + sortColumnDir);
}
if (!string.IsNullOrEmpty(Search))
{
IQueryableNotifications = IQueryableNotifications.Where(m => m.Message == Search || m.Message == Search);
}
return(IQueryableNotifications);
}
public GetScheduleByUserResponse GetScheduleByUser(GetScheduleByUserRequest request)
{
GetScheduleByUserResponse response = new GetScheduleByUserResponse();
var scheduleList = new List <ScheduleDto>();
if (request.RoleName.Equals("student"))
{
scheduleList = _context.Schedules.Where(s => s.Student.StudentId == request.UserId && s.TermId == request.TermId && s.CourseId == request.CourseId)
.Select(s => new ScheduleDto()
{
ScheduleId = s.ScheduleId,
Date = SqlFunctions.DateName("day", s.Date) + "/" + SqlFunctions.DateName("month", s.Date) + "/" + SqlFunctions.DateName("year", s.Date),
SlotNo = s.SlotId,
TeacherId = s.TeacherId,
ClassId = s.ClassId,
AttendanceStatus = s.AttendanceStatus,
ReportStatus = s.ReportStatus
}).ToList();
}
else if (request.RoleName.Equals("teacher"))
{
scheduleList = _context.Schedules.Where(s => s.TeacherId == request.UserId && s.TermId == request.TermId && s.CourseId == request.CourseId)
.GroupBy(g => new { g.Date, g.SlotId, g.TeacherId, g.ClassId })
.Select(s => new ScheduleDto()
{
ScheduleId = s.Select(c => c.ScheduleId).FirstOrDefault(),
Date = SqlFunctions.DateName("day", s.Key.Date) + "/" + SqlFunctions.DatePart("mm", s.Key.Date) + "/" + SqlFunctions.DateName("year", s.Key.Date),
SlotNo = s.Key.SlotId,
TeacherId = s.Key.TeacherId,
ClassId = s.Select(c => c.ClassId).FirstOrDefault()
}).ToList();
}
response.Schedules = scheduleList;
return(response);
}
public List <OrderViewModel> GetList()
{
var result = context.Orders.Select(rec => new OrderViewModel
{
Id = rec.Id,
ClientId = rec.ClientId,
DateCreate =
SqlFunctions.DateName("dd", rec.DateCreate) + " " + SqlFunctions.DateName("mm", rec.DateCreate) +
" " + SqlFunctions.DateName("yyyy", rec.DateCreate),
DateImplement =
rec.DateImplement == null
? ""
: SqlFunctions.DateName("dd", rec.DateImplement.Value) + " " +
SqlFunctions.DateName("mm", rec.DateImplement.Value) + " " +
SqlFunctions.DateName("yyyy", rec.DateImplement.Value),
StatusOrder = rec.OrderStatus.ToString(),
TotalSum = rec.TotalSum,
ClientFIO = rec.Client.ClientFIO,
OrderCars = context.OrderCars.Where(recPC => recPC.OrderId == rec.Id)
.Select(recPC => new OrderCarViewModel
{
Id = recPC.Id,
CarId = recPC.CarId,
OrderId = recPC.OrderId,
CarName = recPC.Car.CarName,
Amount = recPC.Amount
})
.ToList()
})
.ToList();
return(result);
}
/// <summary>
/// get Material for datatable flow pagging
/// </summary>
/// <param name="search"></param>
/// <param name="start"></param>
/// <param name="length"></param>
/// <param name="sortColumn"></param>
/// <param name="sortColumnDir"></param>
/// <returns></returns>
public List <object> getPartyForDatatablePagging(string search, int start, int length, string sortColumn, string sortColumnDir, bool isSearchDate, DateTime valueDate)
{
try
{
using (var context = (ConnectionEFDataFirst)Activator.CreateInstance(typeof(ConnectionEFDataFirst), _connectionStr))
{
List <object> getData = new List <object>();
//check data
search = String.IsNullOrWhiteSpace(search) ? "" : search;
sortColumn = String.IsNullOrWhiteSpace(sortColumn) ? "" : sortColumn;
sortColumnDir = String.IsNullOrWhiteSpace(sortColumnDir) ? "" : sortColumnDir;
DateTime startDate = new DateTime(valueDate.Year, valueDate.Month, valueDate.Day, 0, 0, 0, 0);
DateTime endDate = new DateTime(valueDate.Year, valueDate.Month, valueDate.Day, 23, 59, 59, 999);
//excute query
getData = (from u in context.TBL_PARTY
where (!isSearchDate && (search == "" || u.CustomerName.Contains(search)) ||
(isSearchDate && u.PartyDate >= startDate && u.PartyDate <= endDate))
select new { u.PartyID,
PartyDate = SqlFunctions.DateName("day", u.PartyDate)
+ "/" + SqlFunctions.StringConvert((double)u.PartyDate.Month).TrimStart()
+ "/" + SqlFunctions.DateName("year", u.PartyDate)
+ " " + SqlFunctions.DateName("hour", u.PartyDate)
+ ":" + SqlFunctions.DateName("minute", u.PartyDate), u.CustomerName, u.NumberTablePlan, u.NumberTableException, u.NumberTableVegetarian })
.OrderBy((sortColumn == "" && sortColumnDir == "") ? "CustomerName asc" : sortColumn
+ " " + sortColumnDir).Skip(start).Take(length).ToList <object>();
return(getData);
}
}
catch (Exception ex)
{
return(new List <object>());
}
}
public ContentResult HoursWorkedsData(HoursWorked filter)
{
var hoursWorkeds = db.HoursWorkeds.Include(h => h.Employee).Include(h => h.Job);
var hoursWorkedsData = (from hrs in hoursWorkeds
where (hrs.Employee.EmployeeID == filter.EmployeeID || filter.EmployeeID == 0) &&
(hrs.Job.JobID == filter.JobID || filter.JobID == 0) &&
(hrs.ItemNumber == filter.ItemNumber || filter.ItemNumber == null) &&
(hrs.HoursWorkedRT == filter.HoursWorkedRT || filter.HoursWorkedRT == null) &&
(hrs.HoursWorkedOT == filter.HoursWorkedOT || filter.HoursWorkedOT == null) &&
(hrs.JobDescription == filter.JobDescription || filter.JobDescription == null)
select new
{
hrs.HoursWorkedID,
EmployeeID = hrs.Employee.EmployeeID,
JobID = hrs.Job.JobID,
hrs.ItemNumber,
Date = SqlFunctions.DateName("mm", hrs.Date) + " " + SqlFunctions.DateName("day", hrs.Date) + ", " + SqlFunctions.DateName("year", hrs.Date),
hrs.HoursWorkedRT,
hrs.HoursWorkedOT,
hrs.JobDescription
}).ToList();
JavaScriptSerializer jsonSerializer = new JavaScriptSerializer();
string output = jsonSerializer.Serialize(hoursWorkedsData);
return(Content(output, "application/json"));
}
public TaskEntity GetTaskById(int taskId)
{
var taskFromDb = (from t in _db.T_TASK
join p in _db.T_PARENT_TASK on t.PARENT_TASK_ID equals p.PARENT_TASK_ID into parents
from parent in parents.DefaultIfEmpty()
join pr in _db.T_PROJECT on t.PROJ_ID equals pr.PROJ_ID
join u in _db.T_USER on t.USR_ID equals u.EMP_ID
where t.TASK_ID == taskId
select new TaskEntity
{
TaskId = t.TASK_ID,
TaskName = t.TASK_NM,
ParentId = t.PARENT_TASK_ID != null ? t.PARENT_TASK_ID.Value : 0,
ParentName = parent.PARENT_TASK_NM,
Priority = t.PRIORITY,
StartDate = (t.STRT_DT != null ?
SqlFunctions.DateName("day", t.STRT_DT) + "/" + SqlFunctions.DateName("month", t.STRT_DT) + "/" + SqlFunctions.DateName("year", t.STRT_DT) : ""),
EndDate = (t.END_DT != null ?
SqlFunctions.DateName("day", t.END_DT) + "/" + SqlFunctions.DateName("month", t.END_DT) + "/" + SqlFunctions.DateName("year", t.END_DT) : ""),
ProjectId = t.PROJ_ID,
ProjectName = pr.PROJ_NM,
UserId = t.USR_ID,
UserName = u.EMP_FRST_NM + " " + u.EMP_LST_NM,
TaskStatus = t.STATUS
}).SingleOrDefault();
return(taskFromDb);
}
public IEnumerable <Object> GetByFiltering(int StartIndex, int Count, string Sorting, string Filtering, out int CountRecords)
{
IEnumerable <Object> ListCP = null;
CountRecords = 0;
Expression <Func <CP, bool> > ExpressionFilter = null;
try
{
if ((Filtering ?? "") != "")
{
ExpressionFilter = (TableCp => TableCp.Milestone.Name.Contains(Filtering) ||
TableCp.CPID.ToString().Contains(Filtering) ||
((SqlFunctions.DateName("mm", TableCp.ExpirationDate).Substring(0, 3).ToString() + " " + SqlFunctions.DateName("dd", TableCp.ExpirationDate) + " ," + SqlFunctions.DateName("yyyy", TableCp.ExpirationDate)).ToString()).Contains(Filtering) ||
((SqlFunctions.DateName("mm", TableCp.EffectiveDate).Substring(0, 3).ToString() + " " + SqlFunctions.DateName("dd", TableCp.EffectiveDate) + " ," + SqlFunctions.DateName("yyyy", TableCp.EffectiveDate)).ToString()).Contains(Filtering) ||
TableCp.Order.ToString().Contains(Filtering) ||
(TableCp.IsDeleted == true ? "true" : "false").Contains(Filtering) ||
TableCp.Name.Contains(Filtering));
}
var query = SelectCPView(ExpressionFilter, Sorting);
string sqlstring = query.ToString();
query = query.Get(StartIndex, Count, out CountRecords);
ListCP = query.ToList();
}
catch (Exception ex)
{
Errors.Write(ex);
}
return(ListCP);
}
public List <OrderViewModel> GetList()
{
List <OrderViewModel> result = context.Orders.Select(rec => new OrderViewModel
{
Id = rec.Id,
ClientId = rec.ClientId,
DateCreate = SqlFunctions.DateName("dd", rec.DateCreate) + " " +
SqlFunctions.DateName("mm", rec.DateCreate) + " " +
SqlFunctions.DateName("yyyy", rec.DateCreate),
DateImplement = rec.DateImplement == null ? "" :
SqlFunctions.DateName("dd", rec.DateImplement.Value) + " " +
SqlFunctions.DateName("mm", rec.DateImplement.Value) + " " +
SqlFunctions.DateName("yyyy", rec.DateImplement.Value),
Status = rec.Status.ToString(),
Sum = rec.Sum,
ClientName = rec.Client.Name,
Reserved = rec.Reserved
})
.ToList();
foreach (var o in result)
{
o.OrderProducts = context.OrderProducts.Where(op => op.OrderId == o.Id).Select(op => new OrderProductViewModel
{
Id = op.Id,
OrderId = op.Order.Id,
ProductId = op.Product.Id,
ProductName = op.Product.Name,
Count = op.Count
}
).ToList();
}
return(result);
}
public OrderViewModel GetElement(int id)
{
var o = context.Orders.Where(e => e.Id == id).Select(rec => new OrderViewModel
{
Id = rec.Id,
ClientId = rec.ClientId,
DateCreate = SqlFunctions.DateName("dd", rec.DateCreate) + " " +
SqlFunctions.DateName("mm", rec.DateCreate) + " " +
SqlFunctions.DateName("yyyy", rec.DateCreate),
DateImplement = rec.DateImplement == null ? "" :
SqlFunctions.DateName("dd", rec.DateImplement.Value) + " " +
SqlFunctions.DateName("mm", rec.DateImplement.Value) + " " +
SqlFunctions.DateName("yyyy", rec.DateImplement.Value),
Status = rec.Status.ToString(),
Sum = rec.Sum,
ClientName = rec.Client.Name,
Reserved = rec.Reserved,
OrderProducts = context.OrderProducts
.Where(e => e.OrderId == id)
.Select(op => new OrderProductViewModel
{
Id = op.Id,
OrderId = op.Order.Id,
ProductId = op.Product.Id,
ProductName = op.Product.Name,
Count = op.Count
}).ToList()
});
return(o.First());
}
//找所有USER
public object AllUser()
{
var User = practiceEntities.Members.Select(x => new {
Account = x.Account,
Password = x.Password,
Name = x.Name,
Phone = x.Phone,
Tel = x.Tel,
Gender = x.Gender,
Birthday = SqlFunctions.DateName("year", x.Birthday) + "/" + x.Birthday.Value.Month + "/" + SqlFunctions.DateName("day", x.Birthday)
// ,Idx = index + 1
}).ToList();
var result = User.AsEnumerable()
.Select((x, index) => new
{
Account = x.Account,
Password = x.Password,
Name = x.Name,
Phone = x.Phone,
Tel = x.Tel,
Gender = x.Gender,
Birthday = x.Birthday,
Idx = index + 1
}).ToList();
return(User);
}
public IEnumerable <ResponseComandaDto> GetComandaList()
{
var result = DbContext.Comanda.Select(x => new CustomerOrder.Service.DTO.ResponseComandaDto()
{
IdComanda = x.idComanda,
ClientEmail = (from client in DbContext.client
where client.idClient == x.idClient
select client.email).FirstOrDefault(),
EmployeeName = (from employee in DbContext.employee
where employee.idEmployee == x.idEmployee
select employee.Name).FirstOrDefault(),
Telefon = new AddTelefonDto()
{
Brand = x.telefon.brand,
Marca = x.telefon.marca
},
IdUnicTelefon = x.idUnicTelefon,
Stare = x.stare,
DataDeschidere = x.DataDeschidere != null ? SqlFunctions.DateName("dd", x.DataDeschidere) + " " + SqlFunctions.DateName("MM", x.DataDeschidere) + " " + SqlFunctions.DateName("yyyy", x.DataDeschidere) : String.Empty,
DataInchidere = x.DataInchidere != null ? SqlFunctions.DateName("dd", x.DataInchidere) + " " + SqlFunctions.DateName("MM", x.DataInchidere) + " " + SqlFunctions.DateName("yyyy", x.DataInchidere) : String.Empty,
DefectiuneDtos = x.defectiune,
}).OrderBy(x => x.DataInchidere).ToList();
return(result);
}
public static string QueryToDateString(DateTime date)
{
return(SqlFunctions.DateName("year", date) + "-" +
SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double)date.Month).TrimStart().Length) +
SqlFunctions.StringConvert((double)date.Month).TrimStart() + "-" +
SqlFunctions.Replicate("0", 2 - SqlFunctions.DateName("dd", date).Trim().Length) +
SqlFunctions.DateName("dd", date).Trim());
}
public List <RoleMgrModel> roleMgrQry(string codeRole, string roleAuthType, string isDIsabled, string vMemo, string cUpdUserID)
{
bool bCodeRole = StringUtil.isEmpty(codeRole);
bool bRoleAuthType = StringUtil.isEmpty(roleAuthType);
bool bisDIsabled = StringUtil.isEmpty(isDIsabled);
bool bcUpdUserID = StringUtil.isEmpty(cUpdUserID);
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
List <RoleMgrModel> roleList = (from role in db.CODE_ROLE
join codeFlag in db.SYS_CODE.Where(x => x.CODE_TYPE == "IS_DISABLED") on role.IS_DISABLED equals codeFlag.CODE into psFlag
from xFlag in psFlag.DefaultIfEmpty()
join codeReview in db.SYS_CODE.Where(x => x.CODE_TYPE == "DATA_STATUS") on role.DATA_STATUS equals codeReview.CODE into psReview
from xReview in psReview.DefaultIfEmpty()
join codeAuthType in db.SYS_CODE.Where(x => x.CODE_TYPE == "ROLE_AUTH_TYPE") on role.ROLE_AUTH_TYPE equals codeAuthType.CODE into psAuthType
from xAuthType in psAuthType.DefaultIfEmpty()
where 1 == 1
& (bCodeRole || (role.ROLE_ID == codeRole.Trim()))
& (bRoleAuthType || (role.ROLE_AUTH_TYPE == roleAuthType.Trim()))
& (bisDIsabled || (role.IS_DISABLED == isDIsabled.Trim()))
& (bcUpdUserID || (role.LAST_UPDATE_UID == cUpdUserID.Trim()))
orderby xAuthType.ISORTBY, role.ROLE_NAME
select new RoleMgrModel
{
cRoleID = role.ROLE_ID,
cRoleName = role.ROLE_NAME.Trim(),
roleAuthType = role.ROLE_AUTH_TYPE,
roleAuthTypeDesc = (xAuthType == null ? String.Empty : xAuthType.CODE_VALUE),
isDisabled = (xFlag == null ? String.Empty : xFlag.CODE_VALUE),
vMemo = role.MEMO,
freezeUid = role.FREEZE_UID == null ? "" : role.FREEZE_UID,
cUpdUserID = role.LAST_UPDATE_UID == null ? "" : role.LAST_UPDATE_UID,
cUpdDateTime = role.LAST_UPDATE_DT == null ? "" : SqlFunctions.DateName("year", role.LAST_UPDATE_DT) + "/" +
SqlFunctions.DatePart("m", role.LAST_UPDATE_DT) + "/" +
SqlFunctions.DateName("day", role.LAST_UPDATE_DT).Trim() + " " +
SqlFunctions.DateName("hh", role.LAST_UPDATE_DT).Trim() + ":" +
SqlFunctions.DateName("n", role.LAST_UPDATE_DT).Trim() + ":" +
SqlFunctions.DateName("s", role.LAST_UPDATE_DT).Trim(),
dataStatus = (xReview == null ? String.Empty : xReview.CODE_VALUE)
}).ToList();
return(roleList);
}
}
}
public JsonResult BuscarInventarioAsignado(string id = "", string filtro = "", int pagina = 1, int registrosPagina = 15, string tipo = "", string fechaInicio = "1990-01-01", string fechaFin = "3500-12-30")
{
DateTime FIn = Convert.ToDateTime(fechaInicio);
DateTime FFin = Convert.ToDateTime(fechaFin);
if (filtro != "")
{
if (registrosPagina == 0)
{
registrosPagina = (from ci in db.CasaInventario where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin && (ci.Corretaje.Crt_Direccion.Contains(filtro) || ci.Usuario.usu_username.Contains(filtro) || (ci.Usuario.usu_nombre + " " + ci.Usuario.usu_apellidoPa + " " + ci.Usuario.usu_apellidoMa).Contains(filtro)) select ci).Count();
}
int totalPaginas = (int)Math.Ceiling((double)(from ci in db.CasaInventario
where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin && (ci.Corretaje.Crt_Direccion.Contains(filtro) || ci.Usuario.usu_username.Contains(filtro))
select ci).Count() / registrosPagina);
var respuesta = (from ci in db.CasaInventario
where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin && (ci.Corretaje.Crt_Direccion.Contains(filtro) || ci.Usuario.usu_username.Contains(filtro) || (ci.Usuario.usu_nombre + " " + ci.Usuario.usu_apellidoPa + " " + ci.Usuario.usu_apellidoMa).Contains(filtro))
select new
{
descripcion = ci.Corretaje.Crt_Direccion,
tipo = "Salida",
cantidad = ci.ci_cantidadAsignada,
fecha = SqlFunctions.DateName("year", ci.ci_fecha).Trim() + "/" + SqlFunctions.StringConvert((double)ci.ci_fecha.Month).TrimStart() + "/" + SqlFunctions.DateName("day", ci.ci_fecha).Trim(),
nombre = (ci.Usuario.usu_nombre + " " + ci.Usuario.usu_apellidoPa + " " + ci.Usuario.usu_apellidoMa),
fechaOrden = ci.ci_fecha,
total = totalPaginas
}).OrderBy(c => c.fechaOrden).Skip((pagina - 1) * registrosPagina).Take(registrosPagina);
return(Json(respuesta.ToList(), JsonRequestBehavior.AllowGet));
}
else
{
if (registrosPagina == 0)
{
registrosPagina = (from ci in db.CasaInventario where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin select ci).Count();
}
int totalPaginas = (int)Math.Ceiling((double)(from ci in db.CasaInventario where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin select ci).Count() / registrosPagina);
var respuesta = (from ci in db.CasaInventario
where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin
select new
{
descripcion = ci.Corretaje.Crt_Direccion,
tipo = "Salida",
cantidad = ci.ci_cantidadAsignada,
fecha = SqlFunctions.DateName("year", ci.ci_fecha).Trim() + "/" + SqlFunctions.StringConvert((double)ci.ci_fecha.Month).TrimStart() + "/" + SqlFunctions.DateName("day", ci.ci_fecha).Trim(),
nombre = (ci.Usuario.usu_nombre + " " + ci.Usuario.usu_apellidoPa),
fechaOrden = ci.ci_fecha,
total = totalPaginas
}).OrderBy(c => c.fechaOrden).Skip((pagina - 1) * registrosPagina).Take(registrosPagina);
//.Union(from e in db.Existencias where e.ext_art_id == id select new{ descripcion = ("Inventario"), tipo = "Entrada", cantidad = }))
return(Json(respuesta.ToList(), JsonRequestBehavior.AllowGet));
}
}
/// <summary>
/// 查詢修改前後的資料
/// </summary>
/// <param name="db"></param>
/// <param name="cRoleID"></param>
/// <param name="apprStatus"></param>
/// <param name="updDateB"></param>
/// <param name="updDateE"></param>
/// <returns></returns>
public List <CodeRoleItemModel> qryForRoleMgrHis(dbTreasuryEntities db, string cRoleID, string apprStatus, string updDateB, string updDateE)
{
bool bapprStatus = StringUtil.isEmpty(apprStatus);
bool bDateB = StringUtil.isEmpty(updDateB);
bool bDateE = StringUtil.isEmpty(updDateE);
DateTime sB = DateTime.Now.AddDays(1);
if (!bDateB)
{
sB = Convert.ToDateTime(updDateB);
}
DateTime sE = DateTime.Now.AddDays(1);
if (!bDateE)
{
sE = Convert.ToDateTime(updDateE);
}
sE = sE.AddDays(1);
var roleItemHis = (from m in db.CODE_ROLE_ITEM_HIS
join item in db.TREA_ITEM on m.ITEM_ID equals item.ITEM_ID
join appr in db.AUTH_APPR on m.APLY_NO equals appr.APLY_NO into psAppr
from xAppr in psAppr.DefaultIfEmpty()
join cType in db.SYS_CODE.Where(x => x.CODE_TYPE == "ITEM_OP_TYPE") on item.ITEM_OP_TYPE equals cType.CODE into psCType
from xType in psCType.DefaultIfEmpty()
where m.ROLE_ID == cRoleID
& (bapprStatus || xAppr.APPR_STATUS == apprStatus.Trim())
& (bDateB || xAppr.CREATE_DT >= sB)
& (bDateE || xAppr.CREATE_DT <= sE)
select new CodeRoleItemModel
{
authType = m.AUTH_TYPE,
aplyNo = m.APLY_NO.Trim(),
apprUid = xAppr.APPR_UID.Trim(),
apprStatus = xAppr.APPR_STATUS.Trim(),
updateDT = SqlFunctions.DateName("year", xAppr.CREATE_DT) + "/" +
SqlFunctions.DatePart("m", xAppr.CREATE_DT) + "/" +
SqlFunctions.DateName("day", xAppr.CREATE_DT).Trim(),
updateUid = xAppr.CREATE_UID.Trim(),
execAction = m.EXEC_ACTION.Trim(),
itemOpType = xType == null ? item.ITEM_OP_TYPE : xType.CODE_VALUE.Trim(),
itemId = m.ITEM_ID.Trim(),
itemDesc = item.ITEM_DESC.Trim()
}).ToList <CodeRoleItemModel>();
return(roleItemHis);
}
public IQueryable <TimeSheetMasterView> ShowAllSubmittedTimeSheet(string sortColumn, string sortColumnDir, string Search, int UserID)
{
var _context = new DatabaseContext();
var IQueryabletimesheet = (from timesheetmaster in _context.TimeSheetMaster
join timeSheetAuditTB in _context.TimeSheetAuditTB on timesheetmaster.TimeSheetMasterID equals timeSheetAuditTB.TimeSheetID
join registration in _context.Registration on timesheetmaster.UserID equals registration.RegistrationID
join AssignedRolesAdmin in _context.AssignedRoles on registration.RegistrationID equals AssignedRolesAdmin.RegistrationID
where AssignedRolesAdmin.AssignToAdmin == UserID && timeSheetAuditTB.Status == 1
select new TimeSheetMasterView
{
TimeSheetStatus = timesheetmaster.TimeSheetStatus == 1 ? "Submitted" : timesheetmaster.TimeSheetStatus == 2 ? "Approved" : "Rejected",
Comment = timesheetmaster.Comment,
TimeSheetMasterID = timesheetmaster.TimeSheetMasterID,
FromDate =
(
EntityFunctions.Right(SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("yyyy", timesheetmaster.FromDate)), 4)
+ "-"
+ EntityFunctions.Right(String.Concat(" ", SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("mm", timesheetmaster.FromDate))), 2)
+ "-"
+ EntityFunctions.Right(String.Concat(" ", SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("dd", timesheetmaster.FromDate))), 2)
).Replace(" ", "0"),
ToDate =
(
EntityFunctions.Right(SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("yyyy", timesheetmaster.ToDate)), 4)
+ "-"
+ EntityFunctions.Right(String.Concat(" ", SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("mm", timesheetmaster.ToDate))), 2)
+ "-"
+ EntityFunctions.Right(String.Concat(" ", SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("dd", timesheetmaster.ToDate))), 2)
).Replace(" ", "0"),
CreatedOn = SqlFunctions.DateName("day", timesheetmaster.CreatedOn).Trim() + "/" +
SqlFunctions.StringConvert((double)timesheetmaster.CreatedOn.Value.Month).TrimStart() + "/" +
SqlFunctions.DateName("year", timesheetmaster.CreatedOn),
TotalHours = timesheetmaster.TotalHours,
Username = registration.Username,
SubmittedMonth = SqlFunctions.DateName("MONTH", timesheetmaster.ToDate).ToString()
});
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
IQueryabletimesheet = IQueryabletimesheet.OrderBy(sortColumn + " " + sortColumnDir);
}
if (!string.IsNullOrEmpty(Search))
{
IQueryabletimesheet = IQueryabletimesheet.Where(m => m.FromDate == Search || m.Username == Search);
}
return(IQueryabletimesheet);
}
public ContentResult UpdateHoursWorkedsData(HoursWorked hoursWorked)
{
var wageHistoryID = db.WageHistories.Where(x => x.EmployeeID == hoursWorked.EmployeeID && x.IsCurrent == true).Select(x => x.WageHistoryID).FirstOrDefault();
hoursWorked.WageHistoryID = wageHistoryID;
if (ModelState.IsValid)
{
try
{
db.Entry(hoursWorked).State = EntityState.Modified;
db.SaveChanges();
}
catch (Exception e)
{
return(Content("", "application/json"));
}
}
else
{
string error = "";
foreach (ModelState modelState in ViewData.ModelState.Values)
{
foreach (ModelError err in modelState.Errors)
{
error = error + " \n" + err.ErrorMessage;
}
}
ViewBag.error = true;
ViewBag.errorMessage = error;
return(Content("", "application/json"));
}
//Form the hours worked data again for a single row and include the data conversions
var hoursWorkeds = db.HoursWorkeds.Include(h => h.Employee).Include(h => h.ItemNumber).Include(h => h.Job);
var hoursWorkedsData = (from hrs in hoursWorkeds
where hrs.HoursWorkedID == hoursWorked.HoursWorkedID
select new
{
hrs.HoursWorkedID,
EmployeeID = hrs.Employee.EmployeeID,
JobID = hrs.Job.JobID,
hrs.ItemNumber,
Date = SqlFunctions.DateName("mm", hrs.Date) + " " + SqlFunctions.DateName("day", hrs.Date) + ", " + SqlFunctions.DateName("year", hrs.Date),
hrs.HoursWorkedRT,
hrs.HoursWorkedOT,
hrs.JobDescription
}).SingleOrDefault();
JavaScriptSerializer jsonSerializer = new JavaScriptSerializer();
string output = jsonSerializer.Serialize(hoursWorkedsData);
return(Content(output, "application/json"));
}
public Expression <Func <PVLog, PvLogGroup> > GroupLogByUserAndMonthYear()
{
Expression <Func <PVLog, PvLogGroup> > expression =
x => new PvLogGroup
{
MonthYear = DbFunctions.Left(SqlFunctions.DateName("month", x.LogDate), 3) + "-" + SqlFunctions.DatePart("year", x.LogDate),
UserId = x.UserId,
UserName = x.UserName
};
return(expression);
}
