public object JTableContact([FromBody] JtableModelContact jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
int? customerId = jTablePara.CustomerId;
string contactName = jTablePara.ContactName.ToLower();
string contactEmail = jTablePara.ContactEmail.ToLower();
string contactTelephone = jTablePara.ContactTelephone;
string contactMobilePhone = jTablePara.ContactMobilePhone;
var query = from a in _context.Contacts
where (a.ContactType == customerId && a.ContactType != null &&
a.IsDeleted == false &&
a.Email.ToLower().Contains(contactEmail) &&
a.ContactName.ToLower().Contains(contactName) &&
a.Telephone.Contains(contactTelephone) &&
a.MobilePhone.Contains(contactMobilePhone))
select new
{
id = a.Id,
contactName = a.ContactName,
contactEmail = a.Email,
contactAddress = a.Address,
contactTelephone = a.Telephone,
contactMobilePhone = a.MobilePhone,
};
var count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "id", "contactName", "contactEmail", "contactAddress", "contactTelephone", "contactMobilePhone");
return(Json(jdata));
}
public object JTable([FromBody] JtableAddonAppServerModel jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = (from a in _context.AddonAppServers
where (string.IsNullOrEmpty(jTablePara.ServerCode) || a.ServerCode.ToLower().Contains(jTablePara.ServerCode.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.ServerAddress) || a.ServerAddress.ToLower().Contains(jTablePara.ServerAddress.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.Note) || a.Note.ToLower().Contains(jTablePara.Note.ToLower()))
select new
{
a.Id,
a.ServerCode,
a.AppCode,
a.AppVendorCode,
a.ServerAddress,
a.Status,
a.Note,
}).OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var count = (from a in _context.AddonAppServers
select a).AsNoTracking().Count();
var data = query.Select(x => new
{
x.Id,
x.ServerCode,
AppName = _context.AddonApps.FirstOrDefault(y => y.AppCode == x.AppCode)?.AppTitle,
VendorName = _context.AppVendors.FirstOrDefault(y => y.VendorCode == x.AppVendorCode)?.Name,
x.ServerAddress,
x.Status,
x.Note
}).ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "Id", "ServerCode", "AppName", "VendorName", "ServerAddress", "Status", "Note");
return(Json(jdata));
}
public object JTable([FromBody] JTableModelTicket jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var listCommon = _context.CommonSettings.Select(x => new { x.CodeSet, x.ValueSet });
DateTime?fromDate = !string.IsNullOrEmpty(jTablePara.FromDate) ? DateTime.ParseExact(jTablePara.FromDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
DateTime?toDate = !string.IsNullOrEmpty(jTablePara.ToDate) ? DateTime.ParseExact(jTablePara.ToDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var query = from a in _context.MaterialPaymentTickets
join b in _context.ContractHeaders on a.PayObjId equals b.ContractCode into b1
from b in b1.DefaultIfEmpty()
join c in listCommon on a.Currency equals c.CodeSet into c1
from c in c1.DefaultIfEmpty()
where (string.IsNullOrEmpty(jTablePara.ContractName) || b.Title.ToLower().Contains(jTablePara.ContractName.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.PayTitle) || a.PayTitle.ToLower().Contains(jTablePara.PayTitle.ToLower())) &&
((fromDate == null || (a.CreatedTime >= fromDate)) && (toDate == null || (a.CreatedTime <= toDate))) &&
(jTablePara.PayType == null || a.PayType == jTablePara.PayType)
select new
{
Id = a.PaymentTickitId,
Title = a.PayTitle,
ContractName = b != null ? b.Title : "Không xác định",
Type = a.PayType == true ? "Phiếu thu" : "Phiếu chi",
TotalPay = a.MoneyTotal,
Unit = c != null ? c.CodeSet : "Không xác định",
a.CreatedTime,
};
int count = query.Count();
//var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBegin).Take(jTablePara.Length).AsNoTracking().ToList();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).AsNoTracking().ToList();
var data1 = data.Skip(intBeginFor).Take(jTablePara.Length).ToList();
var jdata = JTableHelper.JObjectTable(data1, jTablePara.Draw, count, "Id", "Title", "ContractName", "TotalPay", "Unit", "Type", "CreatedTime");
return(Json(jdata));
}
public object JTable([FromBody] WeekWorkingScheduleJtable jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var fromDate = !string.IsNullOrEmpty(jTablePara.FromDate) ? DateTime.ParseExact(jTablePara.FromDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var toDate = !string.IsNullOrEmpty(jTablePara.ToDate) ? DateTime.ParseExact(jTablePara.ToDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var query = from a in _context.DispatchesWeekWorkingSchedulerss
where (string.IsNullOrEmpty(jTablePara.FromDate) || (a.CreatedTime.Date >= fromDate.Value.Date)) &&
(string.IsNullOrEmpty(jTablePara.ToDate) || (a.CreatedTime.Date <= toDate.Value.Date))
select new
{
a.Id,
a.Chair,
a.CreatedTime,
a.TimeStart,
a.TimeEnd,
a.Composition,
a.Content,
a.Room,
};
var count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "Id", "Chair", "CreatedTime", "TimeStart", "TimeEnd", "Composition", "Content", "Room");
return(Json(jdata));
}
public String JtableRoomModel(JTableModelCustom jTablePara)
{
int intBegin = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = from room in _context.Rooms.Include(x => x.Role)
where room.IsDelete == false
select new
{
room.Id,
room.Name,
room.Row,
room.Col,
FullName = room.Role.Staffs.First().FullName.Length != 0 ? room.Role.Staffs.First().FullName : ""
};
var query1 = from a in query
where (a.FullName.Contains(jTablePara.FullName) || String.IsNullOrEmpty(jTablePara.FullName)) && (String.IsNullOrEmpty(jTablePara.NameRoom) || a.Name.Contains(jTablePara.NameRoom))
select a;
int count = query1.Count();
var data = query1.AsQueryable()
.Skip(intBegin)
.Take(jTablePara.Length);
var jdata = JTableHelper.JObjectTable(data.ToList(), jTablePara.Draw, count, "Id", "Name", "Row", "Col", "FullName");
return(JsonConvert.SerializeObject(jdata));
}
public object JTable([FromBody] JTableModelParamForWarning jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var fromTime = !string.IsNullOrEmpty(jTablePara.FromTime) ? DateTime.ParseExact(jTablePara.FromTime, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var toTime = !string.IsNullOrEmpty(jTablePara.ToTime) ? DateTime.ParseExact(jTablePara.ToTime, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var query = from a in _context.ParamForWarnings
join b in _context.FundCatReptExpss on a.catCode equals b.CatCode
where (string.IsNullOrEmpty(jTablePara.Curency) || a.currency.ToLower().Equals(jTablePara.Curency.ToLower())) &&
((jTablePara.Total == null) || (jTablePara.Total <= a.total)) &&
((fromTime == null || (fromTime <= a.fromTime))) && (toTime == null || toTime <= a.toTime) &&
a.isDeleted == false
select new
{
id = a.id,
aettype = a.aetType,
catCode = a.catCode,
total = a.total,
currency = a.currency,
fromTime = a.fromTime,
toTime = a.toTime,
catName = b.CatName
};
int count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).AsNoTracking().ToList();
var data1 = data.Skip(intBeginFor).Take(jTablePara.Length).ToList();
var jdata = JTableHelper.JObjectTable(data1, jTablePara.Draw, count, "id", "aettype", "catCode", "total", "currency", "fromTime", "toTime", "catName");
return(Json(jdata));
}
public object JTable([FromBody] JtableAddonAppModel jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var date = !string.IsNullOrEmpty(jTablePara.Date) ? DateTime.ParseExact(jTablePara.Date, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var query = from a in _context.AddonApps
where ((date == null) || (a.AppDate.HasValue && a.AppDate.Value.Date == date.Value.Date)) &&
(string.IsNullOrEmpty(jTablePara.Code) || a.AppCode.ToLower().Contains(jTablePara.Code.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.Title) || a.AppTitle.ToLower().Contains(jTablePara.Title.ToLower()))
select new
{
a.Id,
a.AppCode,
a.AppTitle,
a.AppDate,
a.Note,
a.Icon,
a.LinkChplay,
a.Status
};
var count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "Id", "AppCode", "AppTitle", "AppDate", "Note", "Icon", "LinkChplay", "Status");
return(Json(jdata));
}
//view dữ liệu ra bảng
public object jtable([FromBody] JTableModelCustom jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = from a in _context.edu_student
join b in _context.edu_student_contact
on a.id equals b.studentID
where a.flag == 1 && b.flag == 1
select new
{
id_st = a.id,
code = a.code,
firstname = a.firstname,
sex = a.sex,
birthday = a.birthday,
id_con = b.id,
name = b.name,
relationship = b.relationship
};
var count = query.Count();
var data = query
.OrderByDescending("id_con").Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "id_st", "code", "firstname", "sex", "birthday", "id_con", "name", "relationship");
return(Json(jdata));
}
public object Jtable([FromBody] JTableModelStore jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
string storeCode = jTablePara.StoreCode.ToLower();
string storeName = jTablePara.StoreName.ToLower();
DateTime?fromDate = string.IsNullOrEmpty(jTablePara.FromDate) ? (DateTime?)null : DateTime.ParseExact(jTablePara.FromDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime?toDate = string.IsNullOrEmpty(jTablePara.ToDate) ? (DateTime?)null : DateTime.ParseExact(jTablePara.ToDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
var query = from a in _context.MaterialStores
where (a.IsDeleted == false &&
a.StoreCode.ToLower().Contains(storeCode) &&
a.StoreName.ToLower().Contains(storeName) &&
((fromDate == null || (a.CreatedTime >= fromDate)) &&
(toDate == null || (a.CreatedTime <= toDate))))
select new
{
id = a.StoreId,
code = a.StoreCode,
name = a.StoreName,
location = a.Location,
description = a.Description,
manager = a.UserId,
createdTime = a.CreatedTime
};
int count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).AsNoTracking().ToList();
var data1 = data.Skip(intBeginFor).Take(jTablePara.Length).ToList();
var jdata = JTableHelper.JObjectTable(data1, jTablePara.Draw, count, "id", "code", "name", "location", "description", "manager", "createdTime");
return(Json(jdata));
}
public JsonResult JTableCardJob([FromBody] JTableModelCardJob jtablePara)
{
int intBegin = (jtablePara.CurrentPage - 1) * jtablePara.Length;
var query = from a in _context.CardForWObj
join b in _context.WORKOSCards on a.CardCode equals b.CardCode into b1
from b in b1.DefaultIfEmpty()
where a.IsDeleted == false &&
a.CatObjCode.Equals("CUSTOMER") &&
a.ObjCode.Equals(_context.Customers.FirstOrDefault(x => x.CusID == jtablePara.CustomerId).CusCode) &&
(string.IsNullOrEmpty(jtablePara.CardCode) || a.CardCode.ToLower().Contains(jtablePara.CardCode.ToLower())) &&
(string.IsNullOrEmpty(jtablePara.CardName) || b.CardName.ToLower().Contains(jtablePara.CardName.ToLower()))
select new
{
Id = a.Id,
CardCode = a.CardCode,
Customer = a.ObjCode,
CardName = b.CardName
};
int count = query.Count();
var data = query.OrderUsingSortExpression(jtablePara.QueryOrderBy).Skip(intBegin).Take(jtablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jtablePara.Draw, count, "Id", "CardCode", "Customer", "CardName");
return(Json(jdata));
}
public object JTable([FromBody] DocumentModel jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = from a in _context.DispatchesCategorys
join b in _context.DispatchesCategorys on a.TypeM equals b.Code into b2
from b1 in b2.DefaultIfEmpty()
where a.IsDeleted == false &&
(string.IsNullOrEmpty(jTablePara.Code) || a.Code.ToLower().Contains(jTablePara.Code.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.Name) || a.Name.ToLower().Contains(jTablePara.Name.ToLower())) &&
((jTablePara.Year == null) || (a.Year.Equals(jTablePara.Year))) &&
(a.Type == EnumHelper <DocumentTypeEnum> .GetDisplayValue(DocumentTypeEnum.SVB))
select new DocumentRes
{
Id = a.Id,
Code = a.Code,
Name = a.Name,
DocumentType = a.DocumentType,
NumberCreator = a.NumberCreator,
DocumentSymbol = a.DocumentSymbol,
CreatedTime = a.CreatedTime,
Type = b1 != null ? b1.Name : ""
};
var count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "Id", "Code", "Name", "DocumentType", "NumberCreator", "DocumentSymbol", "CreatedTime", "Type");
return(Json(jdata));
}
public object JTableContract([FromBody] JTableModelContract jTablePara)
{
int intBegin = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = from a in _context.ContractHeaders
where a.IsDeleted == false &&
a.CusCode.Equals(jTablePara.CustomerCode) &&
(string.IsNullOrEmpty(jTablePara.ContractCode) || (a.ContractCode.ToLower().Contains(jTablePara.ContractCode.ToLower()))) &&
(string.IsNullOrEmpty(jTablePara.Title) || (a.Title.ToLower().Contains(jTablePara.Title.ToLower())))
select new
{
id = a.ContractHeaderID,
code = a.ContractCode,
title = a.Title,
mainService = a.MainService,
budget = a.Budget,
currency = a.Currency,
};
int count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBegin).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "id", "code", "title", "mainService", "budget", "currency");
return(Json(jdata));
}
public object JTable([FromBody] JTableModelCustomer jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var listCommon = _context.CommonSettings.Select(x => new { x.CodeSet, x.ValueSet });
var query = from a in _context.Customers
join b in listCommon on a.CusGroup equals b.CodeSet into b1
from b in b1.DefaultIfEmpty()
join c in listCommon on a.ActivityStatus equals c.CodeSet into c1
from c in c1.DefaultIfEmpty()
where (a.IsDeleted == false) &&
(string.IsNullOrEmpty(jTablePara.CustomerCode) || a.CusCode.ToLower().Contains(jTablePara.CustomerCode.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.CustomerName) || a.CusName.ToLower().Contains(jTablePara.CustomerName.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.CustomerEmail) || a.Email.ToLower().Contains(jTablePara.CustomerEmail.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.CustomerPhone) || a.MobilePhone.ToLower().Contains(jTablePara.CustomerPhone.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.CustomerActivityStatus) || a.ActivityStatus == jTablePara.CustomerActivityStatus) &&
(string.IsNullOrEmpty(jTablePara.CustomerGroup) || a.CusGroup.ToLower() == jTablePara.CustomerGroup.ToLower()) &&
(string.IsNullOrEmpty(jTablePara.Address) || a.Address.ToLower().Contains(jTablePara.Address.ToLower()))
select new
{
id = a.CusID,
cusCode = a.CusCode,
cusName = a.CusName,
cusEmail = a.Email,
cusAddress = a.Address,
cusMobilePhone = a.MobilePhone,
cusGroup = b != null ? b.ValueSet : "",
cusActivity = c != null ? c.CodeSet == "ACTIVE" ? c.CodeSet : c.CodeSet == "DEACTIVE" ? c.CodeSet : c.ValueSet : "Không xác định",
};
var count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).AsNoTracking().ToList();
var data1 = data.Skip(intBeginFor).Take(jTablePara.Length).ToList();
var jdata = JTableHelper.JObjectTable(data1, jTablePara.Draw, count, "id", "cusCode", "cusName", "cusEmail", "cusAddress", "cusMobilePhone", "cusGroup", "cusActivity");
return(Json(jdata));
}
public object JTableExtend([FromBody] JTableModelExtend jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
int? customerId = jTablePara.CustomerId;
DateTime?fromdate = !string.IsNullOrEmpty(jTablePara.FromDate) ? DateTime.ParseExact(jTablePara.FromDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
DateTime?todate = !string.IsNullOrEmpty(jTablePara.ToDate) ? DateTime.ParseExact(jTablePara.ToDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var query = from a in _context.CustomerExtends
where (a.isdeleted == false &&
a.customer_code == customerId) &&
a.isdeleted == false &&
((fromdate == null || (a.created_time >= fromdate)) && (todate == null || (a.created_time <= todate))) &&
(string.IsNullOrEmpty(jTablePara.Extvalue) || a.ext_value.ToLower().Contains(jTablePara.Extvalue.ToLower()))
select new
{
id = a.id,
code = a.ext_code,
value = a.ext_value,
created_time = a.created_time.Value.ToString("dd/MM/yyyy HH:mm:ss")
};
int count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "id", "code", "value", "created_time");
return(Json(jdata));
}
public object JTable([FromBody] JTableModelAct jTablePara)
{
int intBegin = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var listCommon = _context.CommonSettings.Select(x => new { x.CodeSet, x.ValueSet });
var query = (from a in _context.AssetTransferDetails
select new
{
a.AssetID,
a.AssetName,
a.AssetCode,
a.Quantity,
a.CostValue,
a.CreatedBy,
a.CreatedTime,
//a.StatusÁ,
a.Note,
a.ListImage,
a.UpdatedBy,
a.UpdatedTime,
}).AsParallel();
int count = query.Count();
var data = query.AsQueryable().OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBegin).Take(jTablePara.Length);
var jdata = JTableHelper.JObjectTable(data.ToList(), jTablePara.Draw, count, "AssetID", "AssetName", "AssetCode", "Quantity", "CostValue", "CreatedBy", "CreatedTime", "Status", "Note", "ListImage", "UpdatedBy", "UpdatedTime");
return(Json(jdata));
}
public object JTable([FromBody] ContactJtableModel jTablePara)
{
var dateFrom = !string.IsNullOrEmpty(jTablePara.DateFrom) ? DateTime.ParseExact(jTablePara.DateFrom, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var dateTo = !string.IsNullOrEmpty(jTablePara.DateTo) ? DateTime.ParseExact(jTablePara.DateTo, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = from a in _context.Contacts
where (string.IsNullOrEmpty(jTablePara.Phone) || a.MobilePhone.ToLower().Contains(jTablePara.Phone.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.Email) || a.Email.ToLower().Contains(jTablePara.Email.ToLower())) &&
(dateFrom == null || (a.CreateTime.HasValue && a.CreateTime.Value.Date >= dateFrom)) &&
(dateTo == null || (a.CreateTime.HasValue && a.CreateTime.Value.Date <= dateTo))
select new
{
a.Id,
a.ContactName,
a.MobilePhone,
a.Email,
a.FilePath,
a.CreateTime
};
var count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "Id", "ContactName", "MobilePhone", "Email", "FilePath", "CreateTime");
return(Json(jdata));
}
public object JTableADD([FromBody] JTableModelAct jTablePara)
{
int intBegin = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = (from a in _context.AssetTransferDetails
join b in _context.AssetTransferHeaders on a.Ticketcode equals b.Ticketcode into b1
from b2 in b1.DefaultIfEmpty()
//join c in _context.AdGroupUsers on b.Departtransf equals c.GroupUserId.ToString() into c1
//from c2 in c1.DefaultIfEmpty()
select new
{
a.AssetID,
a.Quantity,
//a.Status,
a.Note,
a.AssetName,
b2.DepartTransf,
b2.DepartReceived,
a.Ticketcode
}).AsParallel();
int count = query.Count();
var data = query.AsQueryable().OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBegin).Take(jTablePara.Length);
var jdata = JTableHelper.JObjectTable(data.ToList(), jTablePara.Draw, count, "AssetName", "AssetID", "Quantity", "Status", "DepartTransf", "Ticketcode", "DepartReceived");
return(Json(jdata));
}
public object JTable([FromBody] JTableModelMaterial jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
string code = jTablePara.MaterialCode.ToLower();
string name = jTablePara.MaterialName.ToLower();
int? parent = jTablePara.MaterialParent;
var query = from a in _context.MaterialTypes
where (a.IsDeleted == false &&
a.Code.ToLower().Contains(code) &&
a.Name.ToLower().Contains(name) &&
(parent == null || parent == a.ParentId))
select new
{
id = a.Id,
code = a.Code,
name = a.Name,
description = a.Description
};
int count = query.Count();
//var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBegin).Take(jTablePara.Length).AsNoTracking().ToList();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).AsNoTracking().ToList();
var data1 = data.Skip(intBeginFor).Take(jTablePara.Length).ToList();
var jdata = JTableHelper.JObjectTable(data1, jTablePara.Draw, count, "id", "code", "name", "description");
return(Json(jdata));
}
//view dữ liệu ra bảng
public object jtable([FromBody] JTableModelCustom jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = from a in _context.edu_student
where a.flag == 1
select new
{
id = a.id,
facebook = a.facebook,
sky = a.sky,
google = a.google,
twitter = a.twitter,
wordpress = a.wordpress,
other = a.other,
};
var count = query.Count();
var data = query
.OrderByDescending("id").Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "id", "facebook", "sky", "google", "twitter", "wordpress", "other");
return(Json(jdata));
}
public object JTableDetail([FromBody] JTableModelMaterialImpStoreDetail jTablePara)
{
if (string.IsNullOrEmpty(jTablePara.ImpCode))
{
var data = new List <object>();
var jquery = JTableHelper.JObjectTable(data, jTablePara.Draw, 0, "Id", "ProductName", "Quantity", "Unit", "Cose", "Saleoff", "BarCode", "ExpireDate");
return(Json(jquery));
}
else
{
int intBegin = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = from a in _context.MaterialStoreImpGoodsDetails
join b in _context.MaterialProducts on a.GoodsCode equals b.ProductCode into b1
from b in b1.DefaultIfEmpty()
join c in _context.CommonSettings on a.Unit equals c.CodeSet into c1
from c in c1.DefaultIfEmpty()
where a.ImpCode == jTablePara.ImpCode
select new
{
a.Id,
ProductName = b != null ? b.ProductName : null,
a.Quantity,
Unit = c != null ? c.ValueSet : null,
a.Cose,
a.Saleoff,
a.Barcode,
a.ExpireDate
};
int count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBegin).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "Id", "ProductName", "Quantity", "Unit", "Cose", "Saleoff", "Barcode", "ExpireDate");
return(Json(jdata));
}
}
public object JTable([FromBody] JTableModelCustom jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = from a in _context.Jnana_news_cat
where a.cat_status == 1 && (jTablePara.Key == null || jTablePara.Key == "" || a.cat_title.ToLower().Contains(jTablePara.Key.ToLower())) &&
(jTablePara.Key1 == null || jTablePara.Key1 == "" || (a.created_time > DateTime.Parse(jTablePara.Key1))) &&
(jTablePara.Key2 == null || jTablePara.Key2 == "" || (a.created_time < DateTime.Parse(jTablePara.Key2)))
select new
{
id = a.id,
cat_code = a.cat_code,
cat_title = a.cat_title,
cat_description = a.cat_description,
cat_parent_code = a.cat_parent_code,
created_time = a.created_time,
cat_avarta = a.cat_avarta
};
var count = query.Count();
var data = query
.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "id", "cat_code", "cat_title", "cat_description", "created_time", "cat_parent_code", "cat_avarta");
return(Json(jdata));
}
public object JTable([FromBody] JTableModelMaterialImpStore jTablePara)
{
int intBegin = (jTablePara.CurrentPage - 1) * jTablePara.Length;
DateTime?fromDate = !string.IsNullOrEmpty(jTablePara.FromTo) ? DateTime.ParseExact(jTablePara.FromTo, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
DateTime?toDate = !string.IsNullOrEmpty(jTablePara.DateTo) ? DateTime.ParseExact(jTablePara.DateTo, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var query = from a in _context.MaterialStoreImpGoodsHeaders
join c in _context.MaterialStores on a.StoreId equals c.StoreId into c1
from c in c1.DefaultIfEmpty()
where !a.IsDeleted &&
((fromDate == null || (a.CreatedTime >= fromDate)) && (toDate == null || (a.CreatedTime <= toDate))) &&
(string.IsNullOrEmpty(jTablePara.ImpCode) || a.ImpCode.ToLower().Contains(jTablePara.ImpCode.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.StoreName) || c.StoreName.ToLower().Contains(jTablePara.StoreName.ToLower()))
select new
{
a.Id,
a.ImpCode,
StoreName = c != null ? c.StoreName : null,
a.Total,
a.TotalPayment,
a.Vat,
a.TotalPayed,
a.Debt,
a.CreatedTime
};
int count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBegin).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "Id", "ImpCode", "StoreName", "Total", "TotalPayment", "Vat", "TotalPayed", "Debt", "CreatedTime");
return(Json(jdata));
}
//view dữ liệu ra bảng
public object jtable([FromBody] JTableModelCustom jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = from a in _context.edu_course
join b in _context.common_properties
on a.coursetype equals b.id
where a.flag == 1 && b.code.Contains("edu_type_") && (jTablePara.Key == null || jTablePara.Key == "" || a.course_name.ToLower().Contains(jTablePara.Key.ToLower())) &&
(jTablePara.Key4 == null || jTablePara.Key4 == "" || a.coursetype.ToString() == jTablePara.Key4.ToLower())
select new
{
id = a.id,
course_code = a.course_code,
course_name = a.course_name,
coursetype = b.value,
price = a.price,
note = a.note,
userid = a.userid
};
var count = query.Count();
var data = query
.OrderByDescending("id").Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "id", "course_code", "course_name", "coursetype", "price", "note", "userid");
return(Json(jdata));
}
public object JTable([FromBody] SStaffTimeKeepingJtableModel jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var today = DateTime.Today;
var query = (from a in _context.StaffTimetableWorkings
join b in _context.Users on a.UserId equals b.Id
where (a.Action != "CHECKIN" && a.Action != "CHECKOUT") &&
(((a.Action == "NOTWORK" && a.ActionTime.Date <= today) && (a.ActionTo.HasValue && a.ActionTo.Value.Date >= today)) || (a.ActionTime.Date == today)) &&
(string.IsNullOrEmpty(jTablePara.Name) || b.GivenName.ToLower().Contains(jTablePara.Name.ToLower()))
select new
{
a.Id,
FullName = b.GivenName,
b.Gender,
a.Action,
a.ActionTime,
a.ActionTo,
a.Note,
}).AsNoTracking().Skip(intBeginFor).Take(jTablePara.Length).ToList();
var count = (from a in _context.StaffTimetableWorkings
join b in _context.HREmployees on a.UserId equals b.Id.ToString()
where ((a.Action == "NOTWORK" && a.ActionTime.Date <= today && (a.ActionTo.HasValue && a.ActionTo.Value.Date >= today)) || (a.ActionTime.Date == today))
select new
{
a
}).AsNoTracking().Count();
var jdata = JTableHelper.JObjectTable(query, jTablePara.Draw, count, "Id", "FullName", "Gender", "Action", "ActionTime", "ActionTo", "Note");
return(Json(jdata));
}
public object JTable([FromBody] JTableModelCustom jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var listCommon = _context.CommonSettings.Select(x => new { x.CodeSet, x.ValueSet });
var query = from a in _context.ProductCats
join b in listCommon on a.Unit equals b.CodeSet into b1
from b in b1.DefaultIfEmpty()
join c in listCommon on a.ProductGroup equals c.CodeSet into c1
from c in c1.DefaultIfEmpty()
//join b in _context.cms_extra_fields_groups on a.Group equals b.Id
//orderby b.Name
where (jTablePara.productcode == null || jTablePara.productcode == "" || a.ProductCode.ToLower().Contains(jTablePara.productcode.ToLower())) &&
(jTablePara.productname == null || jTablePara.productname == "" || a.ProductName.ToString().ToLower().Contains(jTablePara.productname.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.unit) || a.Unit == jTablePara.unit) &&
(jTablePara.describe == null || jTablePara.describe == "" || a.Note.ToLower().Contains(jTablePara.describe.ToLower()))
orderby a.ProductID
select new
{
//idd=test(),
id = a.ProductID,
productcode = a.ProductCode,
productname = a.ProductName,
unit = b != null ? b.ValueSet : "Không xác định",
productgroup = c != null ? c.ValueSet : "Không xác định",
pathimg = a.PathImg,
note = a.Note,
};
var count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).AsNoTracking().ToList();
var data1 = data.Skip(intBeginFor).Take(jTablePara.Length).ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "id", "productcode", "productname", "unit", "pathimg", "note", "productgroup");
return(Json(jdata));
}
public object JTableFile([FromBody] MaterialFileJtableModel jTablePara)
{
if (string.IsNullOrEmpty(jTablePara.ProductCode))
{
var data = new List <object>();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, 0, "Id", "Name", "Fomart", "CreatedBy", "CreatedTime");
return(Json(jdata));
}
else
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var query = from a in _context.MaterialFiles
where a.ProductCode == jTablePara.ProductCode &&
(string.IsNullOrEmpty(jTablePara.Name) || a.Name.ToLower().Contains(jTablePara.Name.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.Fomart) || a.Fomart.ToLower().Contains(jTablePara.Fomart.ToLower()))
select new
{
a.Id,
a.Name,
a.Fomart,
a.Soure,
a.CreatedBy,
a.CreatedTime
};
var count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "Id", "Name", "Fomart", "Soure", "CreatedBy", "CreatedTime");
return(Json(jdata));
}
}
public object JTable([FromBody] JTableModelAct jTablePara)
{
int intBegin = (jTablePara.CurrentPage - 1) * jTablePara.Length;
var listCommon = _context.CommonSettings.Select(x => new { x.CodeSet, x.ValueSet });
var query = (from a in _context.AssetAtivitys
join b in listCommon on a.ActType equals b.CodeSet into b1
where (!a.IsDeleted &&
(string.IsNullOrEmpty(jTablePara.ActCode) || (a.ActCode.ToLower().Contains(jTablePara.ActCode.ToLower()))) &&
(string.IsNullOrEmpty(jTablePara.ActTitle) || (a.ActTitle.ToLower().Contains(jTablePara.ActTitle.ToLower()))) &&
(string.IsNullOrEmpty(jTablePara.ActType) || (a.ActType.Equals(jTablePara.ActType))) &&
(string.IsNullOrEmpty(jTablePara.ActNote) || (a.ActNote.ToLower().Contains(jTablePara.ActNote.ToLower()))))
select new AssetAtivitysJtableModel
{
ActivityId = a.ActivityId,
ActCode = a.ActCode,
ActTitle = a.ActTitle,
ActType = a.ActType,
ActNote = a.ActNote,
ActMember = a.ActMember
}).AsParallel();
int count = query.Count();
var data = query.AsQueryable().OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBegin).Take(jTablePara.Length);
var jdata = JTableHelper.JObjectTable(data.ToList(), jTablePara.Draw, count, "ActivityId", "ActCode", "ActTitle", "ActType", "ActNote", "ActMember");
return(Json(jdata));
}
public object JTable([FromBody] MaterialJtableModel jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
DateTime?fromDate = !string.IsNullOrEmpty(jTablePara.FromTo) ? DateTime.ParseExact(jTablePara.FromTo, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
DateTime?toDate = !string.IsNullOrEmpty(jTablePara.DateTo) ? DateTime.ParseExact(jTablePara.DateTo, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var query = from a in _context.MaterialProducts.AsNoTracking()
join b in _context.MaterialProductGroups on a.GroupCode equals b.Code into b1
from b in b1.DefaultIfEmpty()
join c in _context.MaterialTypes on a.TypeCode equals c.Code into c1
from c in c1.DefaultIfEmpty()
where !a.IsDeleted &&
(string.IsNullOrEmpty(jTablePara.Code) || a.ProductCode.ToLower().Contains(jTablePara.Code.ToLower())) &&
(string.IsNullOrEmpty(jTablePara.Name) || a.ProductName.ToLower().Contains(jTablePara.Name.ToLower())) &&
((fromDate == null) || (a.CreatedTime.HasValue && a.CreatedTime.Value.Date >= fromDate)) &&
((toDate == null) || (a.CreatedTime.HasValue && a.CreatedTime.Value.Date <= toDate))
select new
{
a.Id,
a.ProductCode,
a.ProductName,
GroupName = b != null ? b.Name : null,
TypeName = c != null ? c.Name : null,
Accessory = _context.MaterialAttributes.FirstOrDefault(x => x.ProductId == a.Id) != null ? "1" : "",
a.CreatedTime
};
var count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).AsNoTracking().ToList();
var data1 = data.Skip(intBeginFor).Take(jTablePara.Length).ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "Id", "ProductCode", "ProductName", "GroupName", "TypeName", "Accessory", "CreatedTime");
return(Json(jdata));
}
public object GetEventCatGrid([FromBody] EventCatJtableModel searchData)
{
int intBegin = (searchData.CurrentPage - 1) * searchData.Length;
var fromDate = string.IsNullOrEmpty(searchData.FromDate) ? (DateTime?)null : DateTime.ParseExact(searchData.FromDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
var toDate = string.IsNullOrEmpty(searchData.ToDate) ? (DateTime?)null : DateTime.ParseExact(searchData.ToDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
if (!string.IsNullOrEmpty(searchData.MemberId))
{
var query = from a in _context.HREmployees
join b in _context.StaffScheduleWorks on a.Id.ToString() equals b.MemberId
where b.MemberId == searchData.MemberId
select new
{
a.fullname,
b.Id,
b.DatetimeEvent,
//Morning = b.FrameTime.Split(';')[0],//Chua tuong thich
//Afternoon = b.FrameTime.Split(';')[1],
//Evening = b.FrameTime.Split(';')[2]
};
int count = query.Count();
var data = query.OrderUsingSortExpression(searchData.QueryOrderBy).Skip(intBegin).Take(searchData.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, searchData.Draw, count, "fullname", "Id", "DatetimeEvent", "Morning", "Afternoon", "Evening");
return(Json(jdata));
}
else
{
var list = new List <object>();
var jdata = JTableHelper.JObjectTable(list, searchData.Draw, 0, "GivenName", "Id", "DatetimeEvent", "Morning", "Afternoon", "Evening");
return(Json(jdata));
}
}
public object JTableFile([FromBody] JTableModelFile jTablePara)
{
int intBeginFor = (jTablePara.CurrentPage - 1) * jTablePara.Length;
int? searchCustomerId = jTablePara.CustomerId;
string searchTitle = jTablePara.Title.ToLower();
string searchType = jTablePara.Type.ToLower();
DateTime?searchCreatedTime = !string.IsNullOrEmpty(jTablePara.CreatedTime) ? DateTime.ParseExact(jTablePara.CreatedTime, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
DateTime?searchUpdatedTime = !string.IsNullOrEmpty(jTablePara.UpdatedTime) ? DateTime.ParseExact(jTablePara.UpdatedTime, "dd/MM/yyyy", CultureInfo.InvariantCulture) : (DateTime?)null;
var query = from a in _context.CustomerFiles
where ((a.CustomerID == searchCustomerId) &&
a.IsDeleted == false &&
a.Title.ToLower().Contains(searchTitle) &&
a.FileType.ToLower().Contains(searchType) &&
(searchCreatedTime == null || a.CreatedTime == searchCreatedTime) &&
(searchUpdatedTime == null || a.UpdatedTime == searchUpdatedTime))
select new
{
id = a.Id,
type = a.FileType,
title = a.Title,
description = a.Description,
createdTime = a.CreatedTime,
updatedTime = a.UpdatedTime
};
var count = query.Count();
var data = query.OrderUsingSortExpression(jTablePara.QueryOrderBy).Skip(intBeginFor).Take(jTablePara.Length).AsNoTracking().ToList();
var jdata = JTableHelper.JObjectTable(data, jTablePara.Draw, count, "id", "type", "title", "description", "createdTime", "updatedTime");
return(Json(jdata));
}
