public JsonResult InvoiceGetInventoryItem(DatableOption option)
{
var jsonReturn = new JsonDataTable();
try
{
using (var db = new FTTxEntities())
{
var query = (from t1 in db.OITM
join t2 in db.OITB on t1.ItmsGrpCod equals t2.ItmsGrpCod
join t3 in db.ITM1.Where(i => i.PriceList == 1) on t1.ItemCode equals t3.ItemCode
where (string.IsNullOrEmpty(option.Query) ||
(t1.ItemCode.Contains(option.Query)) ||
(t1.ItemName.Contains(option.Query))
)
where t1.frozenFor == "N"
where t2.ItmsGrpNam == "Project"
select new
{
RowsNumber = 0,
ItemCode = t1.ItemCode,
ItemName = t1.ItemName ?? "",
UnitPrice = t1.PricingPrc,
});
if (option.order.Count > 0)
{
var sorting = int.Parse(option.order[0].FirstOrDefault(r => r.Key == "column").Value);
var dir = option.order[0].FirstOrDefault(r => r.Key == "dir").Value;
switch (sorting)
{
case 1: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.ItemCode) : query.OrderByDescending(r => r.ItemCode)); break;
case 2: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.ItemName) : query.OrderByDescending(r => r.ItemName)); break;
case 3: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.UnitPrice) : query.OrderByDescending(r => r.UnitPrice)); break;
default: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.ItemCode) : query.OrderByDescending(r => r.ItemCode)); break;
}
}
var data = query.Skip(option.start).Take(option.length).ToList();
var count = query.Count();
jsonReturn = new JsonDataTable {
status = true, message = "Ok", data = data, draw = option.draw, recordsTotal = count, recordsFiltered = count
};
}
}
catch (Exception ex)
{
jsonReturn = new JsonDataTable {
status = false, message = ex.Message, data = new string[0], draw = option.draw, recordsTotal = 0
};
Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message);
}
return(Json(jsonReturn));
}
public DataTableResponseModel InquiryMasterDatatable(DatableOption option, ConfigurationMaster master)
{
var result = new DataTableResponseModel();
try
{
_logger.LogInformation($"Start => {MethodBase.GetCurrentMethod().Name}");
result = _dataAccess.ConfigurationDataAccess.InquiryMasterDatatable(option, master);
_logger.LogInformation($"Finish => {MethodBase.GetCurrentMethod().Name}", JsonSerializer.Serialize(result));
}
catch (Exception ex)
{
_logger.LogError(ex, $"Faile => {MethodBase.GetCurrentMethod().Name}");
}
return(result);
}
public DataTableResponseModel InquiryMasterDatatable(DatableOption option, ConfigurationMaster master)
{
var methodName = MethodBase.GetCurrentMethod().Name;
var result = new DataTableResponseModel();
try
{
_logger.LogInformation($"Start Function => {methodName}, Parameters => {JsonSerializer.Serialize(master)}");
result = _dataAccess.ConfigurationDataAccess.InquiryMasterDatatable(option, master);
_logger.LogInformation($"Finish Function => {methodName}, Result => {JsonSerializer.Serialize(result)}");
}
catch (Exception ex)
{
_logger.LogError(ex, $"Error Function => {methodName}");
}
return(result);
}
public JsonResult InquiryDataTableUser(DatableOption option, UserModel model)
{
var users = new List <UserModel>();
for (int i = 0; i < 30; i++)
{
users.Add(new UserModel {
ID = Guid.NewGuid(), Code = $"000{ i.ToString() }", Name = $"Name{i.ToString()}", Last = $"Last{ i.ToString()}", CreateBy = $"CreateBy {i.ToString()}", CreateDate = DateTime.Now
});
}
var query = (from user in users
where string.IsNullOrEmpty(model.Name) || model.Name.Contains(user.Name)
where string.IsNullOrEmpty(model.Last) || model.Last.Contains(user.Last)
select user);
switch (option.sortingby)
{
case 1: query = (option.orderby == "asc" ? query.OrderBy(r => r.Code) : query.OrderByDescending(r => r.Code)); break;
case 2: query = (option.orderby == "asc" ? query.OrderBy(r => r.Name) : query.OrderByDescending(r => r.Name)); break;
case 3: query = (option.orderby == "asc" ? query.OrderBy(r => r.Last) : query.OrderByDescending(r => r.Last)); break;
case 4: query = (option.orderby == "asc" ? query.OrderBy(r => r.CreateDate) : query.OrderByDescending(r => r.CreateDate)); break;
case 5: query = (option.orderby == "asc" ? query.OrderBy(r => r.CreateBy) : query.OrderByDescending(r => r.CreateBy)); break;
default: query = (option.orderby == "asc" ? query.OrderBy(r => r.Code) : query.OrderByDescending(r => r.Code)); break;
}
var datas = query.Skip(option.start).Take(option.length).ToList();
var recordsTotal = query.Count();
var result = new DataTableResponseModel {
status = true, message = "success", data = datas, draw = option.draw, recordsTotal = recordsTotal, recordsFiltered = recordsTotal
};
return(Json(result));
}
public JsonResult InquiryMasterDatatable(DatableOption option, ConfigurationMaster master)
{
var result = _configurationDataService.InquiryMasterDatatable(option, master);
return(Json(result));
}
public JsonResult InvoiceListLoad(DatableOption option, FilterCustomer model)
{
var jsonReturn = new JsonDataTable();
try
{
using (var db = new FTTxEntities())
{
var endDate = DateTime.Now.Date;
if (model.EndDate != null)
{
endDate = model.EndDate.Value.AddDays(1);
}
var query = (from t1 in db.OINV
join t2 in db.OCRD on t1.CardCode equals t2.CardCode
where (model.ProvinceCode.Count == 0 || model.ProvinceCode.Contains(t1.CardCode.Substring(1, 2)))
where (model.StartDate == null || t1.CreateDate >= model.StartDate)
where (model.EndDate == null || t1.CreateDate < endDate)
where (model.Status.Count == 0 || model.Status.Contains(t1.DocStatus))
where t2.GroupCode == 103
where (string.IsNullOrEmpty(model.Query) ||
(t1.CardCode.Contains(model.Query)) ||
(t1.CardName.Contains(model.Query)) ||
(t1.DocNum.ToString().Contains(model.Query))
)
select new
{
DocNum = t1.DocNum,
CardCode = t1.CardCode ?? "",
CardName = t1.CardName ?? "",
DocTotal = t1.DocTotal,
CreateDate = t1.CreateDate,
PaidToDate = t1.PaidToDate,
CancelDate = t1.CancelDate,
DocDueDate = t1.DocDueDate,
VatSum = t1.VatSum,
BeforeVat = t1.DocTotal - t1.VatSum,
DocStatus = t1.DocStatus ?? ""
});
if (option.order.Count > 0)
{
var sorting = int.Parse(option.order[0].FirstOrDefault(r => r.Key == "column").Value);
var dir = option.order[0].FirstOrDefault(r => r.Key == "dir").Value;
switch (sorting)
{
case 1: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocNum) : query.OrderByDescending(r => r.DocNum)); break;
case 2: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardCode) : query.OrderByDescending(r => r.CardCode)); break;
case 3: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardName) : query.OrderByDescending(r => r.CardName)); break;
case 4: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocTotal) : query.OrderByDescending(r => r.DocTotal)); break;
case 5: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CreateDate) : query.OrderByDescending(r => r.CreateDate)); break;
case 6: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocDueDate) : query.OrderByDescending(r => r.DocDueDate)); break;
default: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocNum) : query.OrderByDescending(r => r.DocNum)); break;
}
}
var data = query.Skip(option.start).Take(option.length).ToList();
var count = query.Count();
jsonReturn = new JsonDataTable {
status = true, message = "Ok", data = data, draw = option.draw, recordsTotal = count, recordsFiltered = count
};
}
}
catch (Exception ex)
{
jsonReturn = new JsonDataTable {
status = false, message = ex.Message, data = new string[0], draw = option.draw, recordsTotal = 0
};
Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message);
}
return(Json(jsonReturn));
}
public JsonResult CustomerListLoad(DatableOption option, FilterCustomer model)
{
var jsonReturn = new JsonDataTable();
try
{
using (var db = new FTTxEntities())
{
var endDate = DateTime.Now.Date;
if (model.EndDate != null)
{
endDate = model.EndDate.Value.AddDays(1);
}
var query = (from t1 in db.OCRD
where (model.ProvinceCode.Count == 0 || model.ProvinceCode.Contains(t1.CardCode.Substring(1, 2)))
where (model.StartDate == null || t1.CreateDate >= model.StartDate)
where (model.EndDate == null || t1.CreateDate < endDate)
where (model.Status.Count == 0 || model.Status.Contains(t1.frozenFor))
where t1.GroupCode == 103
where (string.IsNullOrEmpty(model.Query) ||
(t1.CardCode.Contains(model.Query)) ||
(t1.CardName.Contains(model.Query)) ||
(t1.Cellular.Contains(model.Query)) ||
(t1.E_Mail.Contains(model.Query))
)
select new
{
CardCode = t1.CardCode ?? "",
CardName = t1.CardName ?? "",
CardNo = t1.LicTradNum ?? "",
Cellular = t1.Cellular ?? "",
CreateDate = t1.CreateDate,
EMail = t1.E_Mail ?? "",
Address = t1.Address ?? "",
District = t1.Block ?? "",
Amphur = t1.County ?? "",
Province = t1.City ?? "",
ZipCode = t1.ZipCode ?? "",
Status = t1.frozenFor ?? ""
});
if (option.order.Count > 0)
{
var sorting = int.Parse(option.order[0].FirstOrDefault(r => r.Key == "column").Value);
var dir = option.order[0].FirstOrDefault(r => r.Key == "dir").Value;
switch (sorting)
{
case 1: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardName) : query.OrderByDescending(r => r.CardName)); break;
case 2: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.Cellular) : query.OrderByDescending(r => r.Cellular)); break;
case 3: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.EMail) : query.OrderByDescending(r => r.EMail)); break;
case 5: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CreateDate) : query.OrderByDescending(r => r.CreateDate)); break;
case 6: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.Status) : query.OrderByDescending(r => r.Status)); break;
default: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardCode) : query.OrderByDescending(r => r.CardCode)); break;
}
}
var data = query.Skip(option.start).Take(option.length).ToList();
var count = query.Count();
jsonReturn = new JsonDataTable {
status = true, message = "Ok", data = data, draw = option.draw, recordsTotal = count, recordsFiltered = count
};
}
}
catch (Exception ex)
{
jsonReturn = new JsonDataTable {
status = false, message = ex.Message, data = new string[0], draw = option.draw, recordsTotal = 0
};
Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message);
}
return(Json(jsonReturn));
}
public DataTableResponseModel InquiryMasterDatatable(DatableOption option, ConfigurationMaster master)
{
var methodName = MethodBase.GetCurrentMethod().Name;
var result = new DataTableResponseModel();
try
{
_logger.LogInformation($"Start Function => {methodName}, Parameters => {JsonSerializer.Serialize(master)}");
var query = _context.ConfigurationMaster
.Where(r => (master.ID == Guid.Empty || r.ID == master.ID))
.Where(r => master.IsActive == null || r.IsActive == master.IsActive)
.OrderByDescending(r => r.CreateDate)
.ThenByDescending(r => r.UpdateDate)
.Select(item => new ConfigurationMaster
{
ID = item.ID,
TemplateName = item.TemplateName,
InputFile = item.InputFile,
OutputFile = item.OutputFile,
CreateDate = item.CreateDate,
CreateBy = item.CreateBy,
UpdateDate = item.UpdateDate,
IsActive = item.IsActive
});
switch (option.sortingby)
{
case 1: query = (option.orderby == "asc" ? query.OrderBy(r => r.TemplateName) : query.OrderByDescending(r => r.TemplateName)); break;
case 2: query = (option.orderby == "asc" ? query.OrderBy(r => r.InputFile) : query.OrderByDescending(r => r.InputFile)); break;
case 3: query = (option.orderby == "asc" ? query.OrderBy(r => r.OutputFile) : query.OrderByDescending(r => r.OutputFile)); break;
case 4: query = (option.orderby == "asc" ? query.OrderBy(r => r.CreateDate) : query.OrderByDescending(r => r.CreateDate)); break;
case 5: query = (option.orderby == "asc" ? query.OrderBy(r => r.CreateBy) : query.OrderByDescending(r => r.CreateBy)); break;
case 6: query = (option.orderby == "asc" ? query.OrderBy(r => r.UpdateDate) : query.OrderByDescending(r => r.UpdateDate)); break;
case 7: query = (option.orderby == "asc" ? query.OrderBy(r => r.UpdateBy) : query.OrderByDescending(r => r.UpdateBy)); break;
default: query = (option.orderby == "asc" ? query.OrderBy(r => r.CreateDate) : query.OrderByDescending(r => r.CreateDate)); break;
}
var datas = query.Skip(option.start).Take(option.length).ToList();
var recordsTotal = query.Count();
result = new DataTableResponseModel
{
status = true,
message = "success",
data = datas,
draw = option.draw,
recordsTotal = recordsTotal,
recordsFiltered = recordsTotal
};
_logger.LogInformation($"Finish Function => {methodName}, Result => {JsonSerializer.Serialize(result)}");
}
catch (Exception ex)
{
var messageError = $"Error Function => {methodName}";
_logger.LogError(ex, messageError);
throw new ArgumentException(messageError, ex);
}
return(result);
}
