public JsonResult Get(int draw, int start)
{
var employeeNo = "";
var countEmpNo = "";
int count = 0;
int index = 0;
int empCount = 0;
string empNo = "";
List <Employee> rows = new List <Employee>();
List <string> rowsCount = new List <string>();
List <Employee> tbrows = new List <Employee>();
string parameter = Request.Query["search[value]"].FirstOrDefault();
EmployeeCondition condition = null;
var counttmp = _db.tbEmployee.Select(m => m.EmployeeNo).Distinct().AsQueryable();
var tmp = (from a in _db.tbEmployee
from b in _db.tbEmpGroup
.Where(f => f.EmployeeNo == a.EmployeeNo)
select new
{
a.EmployeeNo,
a.EmployeeName,
b.GroupID
} into x
join c in _db.tbGroup on x.GroupID equals c.GroupID into gj
from y in gj.DefaultIfEmpty()
select new Employee
{
EmployeeNo = x.EmployeeNo,
EmployeeName = x.EmployeeName,
GroupName = (y == null ? String.Empty : y.GroupName)
}).AsQueryable();
if (parameter != "")
{
condition = (EmployeeCondition)JsonConvert.DeserializeObject(parameter, typeof(EmployeeCondition));
}
if (condition != null)
{
if (condition.EmployeeNo != "")
{
tmp = tmp.Where(e => e.EmployeeNo.Contains(condition.EmployeeNo));
}
if (condition.EmployeeName != "")
{
tmp = tmp.Where(e => e.EmployeeName.Contains(condition.EmployeeName));
}
}
rows = tmp.OrderBy(e => e.EmployeeNo).ToList();
//計算搜尋員工總數
foreach (Employee emp in rows)
{
if (!empNo.Equals(emp.EmployeeNo))
{
empNo = emp.EmployeeNo;
empCount++;
}
}
//for (int i = rows.Count - 1; i >= 0; i--)
//{
// if (!rows[i].EmployeeNo.Equals(employeeNo))
// {
// index = i;
// employeeNo = rows[i].EmployeeNo;
// }
// else
// {
// rows[index].GroupName = rows[index].GroupName + "," + rows[i].GroupName;
// rows.RemoveAt(i);
// }
//}
Employee Employee = new Employee();
for (int i = 0; i < rows.Count; i++)
{
if (!rows[i].EmployeeNo.Equals(countEmpNo))
{
countEmpNo = rows[i].EmployeeNo;
count++;
}
if (count > (start + 10))
{
break;
}
if (count > start)
{
if (!rows[i].EmployeeNo.Equals(employeeNo))
{
if (!employeeNo.Equals(""))
{
tbrows.Add(Employee);
}
Employee = new Employee();
employeeNo = rows[i].EmployeeNo;
Employee.EmployeeNo = employeeNo;
Employee.EmployeeName = rows[i].EmployeeName;
Employee.GroupName = rows[i].GroupName;
}
else
{
Employee.GroupName = Employee.GroupName + "," + rows[i].GroupName;
}
}
}
if (!countEmpNo.Equals(""))
{
tbrows.Add(Employee);
}
int recordsTotal = counttmp.Count();
//rows = filteredData.OrderBy(e => e.GroupID).Skip(start).Take(10).ToList();
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = tbrows,
length = tbrows.Count(),
recordsFiltered = empCount,
recordsTotal = empCount
};
return(Json(renderModel));
}
public JsonResult Get(int draw, int start)
{
List <PalletsDetail> rows = new List <PalletsDetail>();
InventoryPalletsCondition condition = null;
string parameter = Request.Query["search[value]"].FirstOrDefault();
if (parameter != "")
{
condition = (InventoryPalletsCondition)JsonConvert.DeserializeObject(parameter, typeof(InventoryPalletsCondition));
}
var Should = (from a in _db.vRecords
join b in (from w in _db.InventoryBody.Where(e => e.InventoryNo == condition.InventoryNo)
group w by new { w.StockNo } into Stock
select new
{
Stock.Key.StockNo
})
on a.WarehouseNo equals b.StockNo
join c in _db.PackingLists.Where(e => e.PalletsNo == condition.PalletsNo)
on a.BagNo equals c.BagNo
group a by new { c.CaseNo, c.PalletsNo, a.WarehouseNo } into S
select new
{
S.Key.CaseNo,
QTY = S.Count().ToString()
}).ToList();
var Actual = (from a in _db.InventoryBody.Where(e => e.InventoryNo == condition.InventoryNo && e.PalletsNo == condition.PalletsNo)
group a by new { a.PalletsNo, a.CaseNo, a.StockNo } into ActBag
select new
{
ActBag.Key.CaseNo,
ActualQty = ActBag.Count().ToString()
}).ToList();
//Full Join 應盤實盤 Linq需兩次Join後再union，不可先裝進Model
var leftJoin = (from a in Should
join b in Actual
on a.CaseNo equals b.CaseNo into ps
from g in ps.DefaultIfEmpty()
select new
{
CaseNo = a.CaseNo,
Qty = a.QTY,
ActualQty = g == null ? "0" : g.ActualQty
});
var rightJoin = (from a in Actual
join b in Should
on a.CaseNo equals b.CaseNo into ps
from g in ps.DefaultIfEmpty()
select new
{
CaseNo = a.CaseNo,
Qty = g == null ? "0" : g.QTY,
ActualQty = a.ActualQty
});
var full = leftJoin.Union(rightJoin);
IEnumerable <PalletsDetail> tmp = (from a in full
select new PalletsDetail
{
CaseNo = a.CaseNo,
Qty = a.Qty,
ActualQty = a.ActualQty
});
//資料總數
int recordsTotal = tmp.Count();
//取得當頁資料
rows = tmp.OrderBy(e => e.CaseNo).OrderBy(e => e.ActualQty == e.Qty).Skip(start).Take(10).ToList();
foreach (PalletsDetail rowResult in rows)
{
if (rowResult.ActualQty != rowResult.Qty)
{
rowResult.ActualQty = "<span style='color:red'>" + rowResult.ActualQty + "</span>";
}
}
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = rows,
length = rows.Count(),
recordsFiltered = recordsTotal,
recordsTotal = recordsTotal
};
return(Json(renderModel));
}
public JsonResult Get(int draw, int start)
{
//List<Records> tmp = (from a in _db.ProductInfoes
// join b in _db.Records
// on a.ProductNo equals b.ProductNo
// where a.ProductNo == "dddd"
// select new Records
// {
// UserId = b.UserId,
// Unit = b.Unit,
// ProductNo = a.ProductNo
// }).ToList();
var employeeNo = "";
var countEmpNo = "";
int count = 0;
int index = 0;
List <EmpGroup> rows = new List <EmpGroup>();
List <string> rowsCount = new List <string>();
List <EmpGroup> tbrows = new List <EmpGroup>();
string parameter = Request.Query["search[value]"].FirstOrDefault();
EmpGroupCondition condition = null;
var counttmp = _db.tbEmployee.Select(m => m.EmployeeNo).Distinct().AsQueryable();
var tmp = (from a in _db.tbEmployee
from b in _db.tbEmpGroup
.Where(f => f.EmployeeNo == a.EmployeeNo)
select new
{
a.EmployeeNo,
a.EmployeeName,
b.GroupID
} into x
join c in _db.tbGroup on x.GroupID equals c.GroupID into gj
from y in gj.DefaultIfEmpty()
select new EmpGroup
{
EmployeeNo = x.EmployeeNo,
EmployeeName = x.EmployeeName,
GroupName = (y == null ? String.Empty : y.GroupName)
}).AsQueryable();
//var tmp = (from a in _db.tbEmployee
// join b in _db.tbEmpGroup
// on a.EmployeeNo equals b.EmployeeNo into list1
// from l1 in list1.DefaultIfEmpty()
// join c in _db.tbGroup
// on l1.GroupID equals c.GroupID into list12
// from l2 in list12.DefaultIfEmpty()
// select new EmpGroup
// {
// EmployeeNo = a.EmployeeNo,
// EmployeeName = a.EmployeeName,
// EmployeeEName = a.EmployeeEName,
// GroupName = l2.GroupName
// }).AsQueryable();
// var tmp = (from a in _db.tbEmployee
// from OD in _db.tbEmpGroup
//.Where(OD => OD.EmployeeNo == a.EmployeeNo).DefaultIfEmpty()
// join c in _db.tbGroup
// on OD.GroupID equals c.GroupID
// select new EmpGroup
// {
// EmployeeNo = a.EmployeeNo,
// EmployeeName = a.EmployeeName,
// EmployeeEName = a.EmployeeEName,
// GroupName = (OD == null ? String.Empty : c.GroupName)
// }).AsQueryable();
if (parameter != "")
{
condition = (EmpGroupCondition)JsonConvert.DeserializeObject(parameter, typeof(EmpGroupCondition));
}
if (condition != null)
{
if (condition.EmployeeNo != "")
{
tmp = tmp.Where(e => e.EmployeeNo.Contains(condition.EmployeeNo));
}
if (condition.EmployeeName != "")
{
tmp = tmp.Where(e => e.EmployeeName.Contains(condition.EmployeeName));
}
}
rows = tmp.OrderBy(e => e.EmployeeNo).ToList();
//for (int i = rows.Count - 1; i >= 0; i--)
//{
// if (!rows[i].EmployeeNo.Equals(employeeNo))
// {
// index = i;
// employeeNo = rows[i].EmployeeNo;
// }
// else
// {
// rows[index].GroupName = rows[index].GroupName + "," + rows[i].GroupName;
// rows.RemoveAt(i);
// }
//}
EmpGroup EmpGroup = new EmpGroup();
for (int i = 0; i < rows.Count; i++)
{
if (!rows[i].EmployeeNo.Equals(countEmpNo))
{
countEmpNo = rows[i].EmployeeNo;
count++;
}
if (count > (start + 10))
{
break;
}
if (count > start)
{
if (!rows[i].EmployeeNo.Equals(employeeNo))
{
if (!employeeNo.Equals(""))
{
tbrows.Add(EmpGroup);
}
EmpGroup = new EmpGroup();
employeeNo = rows[i].EmployeeNo;
EmpGroup.EmployeeNo = employeeNo;
EmpGroup.EmployeeName = rows[i].EmployeeName;
EmpGroup.GroupName = rows[i].GroupName;
}
else
{
EmpGroup.GroupName = EmpGroup.GroupName + "," + rows[i].GroupName;
}
}
}
if (rows.Count != 0)
{
tbrows.Add(EmpGroup);
}
int recordsTotal = counttmp.Count();
//rows = filteredData.OrderBy(e => e.GroupID).Skip(start).Take(10).ToList();
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = tbrows,
length = tbrows.Count(),
recordsFiltered = recordsTotal,
recordsTotal = recordsTotal
};
return(Json(renderModel));
}
public JsonResult Get(int draw, int start)
{
int boxIndex = 0;
UserModel _UserModel = null;
List <PackingCase> rows = new List <PackingCase>();
string parameter = Request.Query["search[value]"].FirstOrDefault();
PackingCaseCondition condition = null;
string palletsNo = "";
string packingTime = "";
if (parameter != "")
{
condition = (PackingCaseCondition)JsonConvert.DeserializeObject(parameter, typeof(PackingCaseCondition));
palletsNo = condition.PalletsNo;
packingTime = condition.PackingTime;
}
if (ViewData["UserModel"] != null)
{
_UserModel = (UserModel)ViewData["UserModel"];
//where a.CompanyCode == _UserModel.EmployeeNo
var tmp = from a in _db.PackingLists
where a.PalletsNo == palletsNo
where a.PackingTime.ToString("yyyyMMdd") == packingTime
group a by a.CaseNo into g
select new PackingCase
{
Item = 0,
CaseNo = g.Key,
Quantity = g.Count()
};
//資料總數
int recordsTotal = tmp.Count();
//取得當頁資料
rows = tmp.Skip(start).Take(10).ToList();;
//計算Item
boxIndex = start;
foreach (PackingCase box in rows)
{
boxIndex++;
box.Item = boxIndex;
}
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = rows,
length = rows.Count(),
recordsFiltered = recordsTotal,
recordsTotal = recordsTotal
};
return(Json(renderModel));
}
else
{
return(null);
}
}
public JsonResult Get(int draw, int start)
{
List <PackingPallets> rows = new List <PackingPallets>();
string[] packingTimeRangeList;
string packingTimeS = "";
string packingTimeE = "";
string parameter = Request.Query["search[value]"].FirstOrDefault();
PackingPalletsListsCondition condition = null;
var tmp = (from a in _db.PackingLists
orderby a.ProductNo
orderby a.PackingTime
group a by new { a.CompanyCode, a.ProductNo, a.PalletsNo, a.Lot, a.CaseNo, PackingTime = a.PackingTime.ToString("yyyyMMdd") } into g
group g by new { g.Key.CompanyCode, g.Key.ProductNo, g.Key.PalletsNo, g.Key.Lot, g.Key.PackingTime } into k
from b in _db.ProductInfoes.Where(e => e.ProductNo == k.Key.ProductNo)
select new PackingPallets
{
CompanyCode = k.Key.CompanyCode,
PackingTime = k.Key.PackingTime,
ProductName = b.ProductName + "(" + b.Spec + ")",
ProductNo = k.Key.ProductNo,
PalletsNo = k.Key.PalletsNo,
Lot = k.Key.Lot,
Quantity = k.Count()
});
if (parameter != "")
{
condition = (PackingPalletsListsCondition)JsonConvert.DeserializeObject(parameter, typeof(PackingPalletsListsCondition));
}
//如果有搜尋條件
if (condition != null)
{
if (condition.CompanyCode != "")
{
tmp = tmp.Where(e => e.CompanyCode == condition.CompanyCode);
}
if (condition.PackingTime != "")
{
tmp = tmp.Where(e => e.PackingTime == condition.PackingTime);
}
if (condition.ProductName != "")
{
tmp = tmp.Where(e => e.ProductNo == condition.ProductName);
}
if (condition.Lot != "")
{
tmp = tmp.Where(e => e.Lot == condition.Lot);
}
}
//資料總數
int recordsTotal = rows.Count();
//取得當頁資料
rows = tmp.OrderBy(x => x.PalletsNo).OrderBy(x => x.Quantity).Skip(start).Take(10).ToList();
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = rows,
length = rows.Count(),
recordsFiltered = recordsTotal,
recordsTotal = recordsTotal
};
return(Json(renderModel));
}
public JsonResult Get(int draw, int start)
{
string logDateS;
string logDateE;
var filteredData = (from a in _db.OrderLog
select new OrderLogTable
{
LogDate = a.LogDate,
LogTime = a.LogTime,
OrderType = a.OrderType,
OrderNo = a.OrderNo,
WorkType = a.WorkType.ToString(),
ProductNo = a.ProductNo,
OriQty = a.OriQty,
RealQty = a.RealQty
}).AsQueryable();
string parameter = Request.Query["search[value]"].FirstOrDefault();
OrderLogCondition condition = null;
if (parameter != "")
{
condition = (OrderLogCondition)JsonConvert.DeserializeObject(parameter, typeof(OrderLogCondition));
}
List <OrderLogTable> rows = null;
if (condition != null)
{
if (condition.LogDate != "")
{
logDateS = condition.LogDate.Substring(0, 10).Replace("/", "").Trim();
logDateE = condition.LogDate.Substring(10).Replace("/", "").Trim();
filteredData = filteredData.Where(e => Convert.ToInt32(e.LogDate) >= Convert.ToInt32(logDateS) && Convert.ToInt32(e.LogDate) <= Convert.ToInt32(logDateE));
}
if (condition.OrderType != "")
{
filteredData = filteredData.Where(e => e.OrderType.Contains(condition.OrderType));
}
if (condition.OrderNo != "")
{
filteredData = filteredData.Where(e => e.OrderNo.Contains(condition.OrderNo));
}
if (condition.ProductNo != "")
{
filteredData = filteredData.Where(e => e.ProductNo.Contains(condition.ProductNo));
}
}
int recordsTotal = filteredData.Count();
rows = filteredData.OrderBy(e => e.LogDate).Skip(start).Take(10).ToList();
foreach (OrderLogTable orderlog in rows)
{
if (orderlog.WorkType.Equals("2"))
{
orderlog.WorkType = "包裝";
}
if (orderlog.WorkType.Equals("3"))
{
orderlog.WorkType = "出產入庫";
}
if (orderlog.WorkType.Equals("4"))
{
orderlog.WorkType = "轉撥";
}
if (orderlog.WorkType.Equals("5"))
{
orderlog.WorkType = "進貨";
}
if (orderlog.WorkType.Equals("6"))
{
orderlog.WorkType = "銷貨";
}
if (orderlog.WorkType.Equals("7"))
{
orderlog.WorkType = "上車";
}
if (orderlog.WorkType.Equals("8"))
{
orderlog.WorkType = "下車";
}
if (orderlog.WorkType.Equals("9"))
{
orderlog.WorkType = "退貨";
}
}
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = rows,
length = rows.Count(),
recordsFiltered = recordsTotal,
recordsTotal = recordsTotal
};
return(Json(renderModel));
}
public JsonResult Get(int draw, int start)
{
UserModel _UserModel = null;
List <PackingPallets> rows = new List <PackingPallets>();
List <PackingPallets> rowsResult = new List <PackingPallets>();
List <PackingPallets> rowsPageResult = new List <PackingPallets>();
string[] packingTimeRangeList;
string packingTimeS = "";
string packingTimeE = "";
string parameter = Request.Query["search[value]"].FirstOrDefault();
PackingListsCondition condition = null;
if (ViewData["UserModel"] != null)
{
_UserModel = (UserModel)ViewData["UserModel"];
tbEmpGroup GroupId = _db.tbEmpGroup.Where(e => e.EmployeeNo == _UserModel.EmployeeNo).FirstOrDefault();
var tmp = (from a in _db.PackingLists
orderby a.ProductNo
orderby a.PackingTime
group a by new { a.CompanyCode, a.ProductNo, a.PalletsNo, a.Lot, a.CaseNo, PackingTime = a.PackingTime.ToString("yyyyMMdd") } into g
group g by new { g.Key.CompanyCode, g.Key.ProductNo, g.Key.PalletsNo, g.Key.Lot, g.Key.PackingTime } into k
from b in _db.ProductInfoes.Where(e => e.ProductNo == k.Key.ProductNo)
select new PackingPallets
{
CompanyCode = k.Key.CompanyCode,
PackingTime = k.Key.PackingTime,
ProductName = b.ProductName + "(" + b.Spec + ")",
ProductNo = k.Key.ProductNo,
PalletsNo = k.Key.PalletsNo,
Lot = k.Key.Lot,
Quantity = k.Count()
}).Where(c => c.CompanyCode == GroupId.GroupID);
if (parameter != "")
{
condition = (PackingListsCondition)JsonConvert.DeserializeObject(parameter, typeof(PackingListsCondition));
}
//如果有搜尋條件
if (condition != null)
{
if (condition.PackingTime.ToString() != "")
{
packingTimeS = condition.PackingTime.Substring(0, 10).Replace("/", "").Trim();
packingTimeE = condition.PackingTime.Substring(10).Replace("/", "").Trim();
tmp = tmp.Where(e => Convert.ToInt32(e.PackingTime) >= Convert.ToInt32(packingTimeS) && Convert.ToInt32(e.PackingTime) <= Convert.ToInt32(packingTimeE));
}
if (condition.ProductName != "")
{
tmp = tmp.Where(e => e.ProductName.Contains(condition.ProductName));
}
}
rows = tmp.OrderBy(e => e.CompanyCode).OrderBy(e => e.PackingTime).OrderBy(e => e.ProductName).OrderBy(e => e.Lot).ToList();
int count = 0;
PackingPallets newPackingPallets = new PackingPallets();
foreach (PackingPallets packingPallets in rows)
{
if (count == 0)
{
newPackingPallets = new PackingPallets();
newPackingPallets.CompanyCode = packingPallets.CompanyCode;
newPackingPallets.PackingTime = packingPallets.PackingTime;
newPackingPallets.ProductName = packingPallets.ProductName;
newPackingPallets.ProductNo = packingPallets.ProductNo;
newPackingPallets.Lot = packingPallets.Lot;
newPackingPallets.PalletsNo = "1";
newPackingPallets.Quantity = packingPallets.Quantity;
count++;
continue;
}
if (newPackingPallets.CompanyCode.Equals(packingPallets.CompanyCode) &&
newPackingPallets.PackingTime.Equals(packingPallets.PackingTime) &&
newPackingPallets.ProductName.Equals(packingPallets.ProductName) &&
newPackingPallets.Lot.Equals(packingPallets.Lot))
{
newPackingPallets.PalletsNo = (Convert.ToInt32(newPackingPallets.PalletsNo) + 1).ToString();
newPackingPallets.Quantity = newPackingPallets.Quantity + packingPallets.Quantity;
}
else
{
rowsResult.Add(newPackingPallets);
newPackingPallets = new PackingPallets();
newPackingPallets.CompanyCode = packingPallets.CompanyCode;
newPackingPallets.PackingTime = packingPallets.PackingTime;
newPackingPallets.ProductName = packingPallets.ProductName;
newPackingPallets.ProductNo = packingPallets.ProductNo;
newPackingPallets.Lot = packingPallets.Lot;
newPackingPallets.PalletsNo = "1";
newPackingPallets.Quantity = packingPallets.Quantity;
}
}
if (count != 0)
{
rowsResult.Add(newPackingPallets);
}
//資料總數
int recordsTotal = rowsResult.Count();
//取得當頁資料
rowsPageResult = rowsResult.Skip(start).Take(10).ToList();;
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = rowsPageResult,
length = rowsPageResult.Count(),
recordsFiltered = recordsTotal,
recordsTotal = recordsTotal
};
return(Json(renderModel));
}
else
{
return(null);
}
}
public JsonResult Get(int draw, int start)
{
var filteredData = _db.VendorProducts.AsQueryable();
string parameter = Request.Query["search[value]"].FirstOrDefault();
VendorProductCondition condition = null;
if (parameter != "")
{
condition = (VendorProductCondition)JsonConvert.DeserializeObject(parameter, typeof(VendorProductCondition));
}
List <VendorProducts> rows = null;
if (condition != null)
{
filteredData = filteredData.Where(e => e.VendorNo == condition.VendorNo);
if (condition.ProductNo != "")
{
filteredData = filteredData.Where(e => e.ProductNo.Contains(condition.ProductNo));
}
if (condition.ProductName != "")
{
filteredData = filteredData.Where(e => e.ProductName.Contains(condition.ProductName));
}
}
int recordsTotal = filteredData.Count();
rows = filteredData.OrderBy(e => e.VendorNo).Skip(start).Take(10).ToList();
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = rows,
length = rows.Count(),
recordsFiltered = recordsTotal,
recordsTotal = recordsTotal
};
return(Json(renderModel));
//var filteredData = (from p in _db.VendorProducts
// select new
// {
// p.VendorNo,
// p.ProductNo,
// p.ProductName,
// p.spec
// }).AsQueryable();
//int recordsTotal = _db.VendorProducts.Count();
////var rows = filteredData.OrderBy(e => e.ProductNo).ToList();
//var rows = filteredData.OrderBy(e => e.VendorNo).OrderBy(s => s.ProductNo).Skip(start).Take(10).ToList();
//var renderModel = new DataTablesRenderModel
//{
// draw = draw,
// data = rows,
// length = rows.Count(),
// recordsFiltered = recordsTotal,
// recordsTotal = recordsTotal
//};
//return Json(renderModel);
}
public JsonResult Get(int draw, int start)
{
List <InventoryLists> rows = new List <InventoryLists>();
string InventoryNo = Request.Query["search[value]"].FirstOrDefault();
var Should = (from a in _db.vRecords
join b in (from w in _db.InventoryBody.Where(e => e.InventoryNo == InventoryNo)
group w by new { w.StockNo } into Stock
select new
{
Stock.Key.StockNo
})
on a.WarehouseNo equals b.StockNo
join c in _db.PackingLists
on a.BagNo equals c.BagNo
group a by new { a.ProductNo, c.CaseNo, c.PalletsNo, a.WarehouseNo } into Bag
group Bag by new { Bag.Key.ProductNo, Bag.Key.PalletsNo, Bag.Key.WarehouseNo } into Case
select new
{
Case.Key.ProductNo,
PalletsNo = Case.Key.PalletsNo,
Qty = Case.Count().ToString(),
}).ToList();
var Actual = (from a in _db.InventoryBody.Where(e => e.InventoryNo == InventoryNo)
group a by new { a.PalletsNo, a.CaseNo, a.StockNo } into ActBag
group ActBag by new { ActBag.Key.PalletsNo, ActBag.Key.StockNo } into ActCase
select new
{
ActCase.Key.PalletsNo,
ActualQty = ActCase.Count().ToString()
}).ToList();
//Full Join 應盤實盤 Linq需兩次Join後再union，不可先裝進Model
var leftJoin = (from a in Should
join b in Actual
on a.PalletsNo equals b.PalletsNo into ps
from k in ps.DefaultIfEmpty()
select new
{
ProductNo = a.ProductNo,
PalletsNo = a.PalletsNo,
Qty = a.Qty,
ActualQty = k == null ? "0" : k.ActualQty
});
var rightJoin = (from a in Actual
join b in Should
on a.PalletsNo equals b.PalletsNo into ps
from k in ps.DefaultIfEmpty()
select new
{
ProductNo = k == null ? "" : k.ProductNo,
PalletsNo = a.PalletsNo,
Qty = k == null ? "0" : k.Qty,
ActualQty = a.ActualQty
});
var fullJoin = leftJoin.Union(rightJoin);
IEnumerable <InventoryLists> tmp = (from a in fullJoin
from b in _db.ProductInfoes.Where(e => e.ProductNo == a.ProductNo)
select new InventoryLists
{
ProductName = b.ProductName + "(" + b.Spec + ")",
PalletsNo = a.PalletsNo,
Qty = a.Qty,
ActualQty = a.ActualQty
});
rows = tmp.OrderBy(e => e.Qty == e.ActualQty).ToList();
//資料總數
int recordsTotal = rows.Count();
//取得當頁資料
rows = tmp.OrderBy(e => e.Qty == e.ActualQty).Skip(start).Take(10).ToList();
foreach (InventoryLists rowResult in rows)
{
if (rowResult.ActualQty != rowResult.Qty)
{
rowResult.ActualQty = "<span style='color:red'>" + rowResult.ActualQty + "</span>";
}
}
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = rows,
length = rows.Count(),
recordsFiltered = recordsTotal,
recordsTotal = recordsTotal
};
return(Json(renderModel));
}
public JsonResult Get(int draw, int start)
{
int bagIndex = 0;
UserModel _UserModel = null;
List <PackingBag> rows = new List <PackingBag>();
string parameter = Request.Query["search[value]"].FirstOrDefault();
PackingBagCondition condition = null;
string palletsNo = "";
string packingTime = "";
string caseNo = "";
if (parameter != "")
{
condition = (PackingBagCondition)JsonConvert.DeserializeObject(parameter, typeof(PackingBagCondition));
palletsNo = condition.PalletsNo;
packingTime = condition.PackingTime;
caseNo = condition.CaseNo;
}
if (ViewData["UserModel"] != null)
{
_UserModel = (UserModel)ViewData["UserModel"];
//where a.CompanyCode == _UserModel.EmployeeNo
var tmp = from a in _db.PackingLists
where a.PalletsNo == palletsNo
where a.PackingTime.ToString("yyyyMMdd") == packingTime
where a.CaseNo == caseNo
select new PackingBag
{
Item = 0,
BagNo = a.BagNo
};
//資料總數
int recordsTotal = tmp.Count();
//取得當頁資料
rows = tmp.OrderBy(b => b.BagNo).ToList();;
//計算Item
foreach (PackingBag bag in rows)
{
bagIndex++;
bag.Item = bagIndex;
}
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = rows,
length = rows.Count(),
recordsFiltered = recordsTotal,
recordsTotal = recordsTotal
};
return(Json(renderModel));
}
else
{
return(null);
}
}
public JsonResult Get(int draw, int start)
{
var filteredData = _db.tbGroup.AsQueryable();
string parameter = Request.Query["search[value]"].FirstOrDefault();
GroupCondition condition = null;
if (parameter != "")
{
condition = (GroupCondition)JsonConvert.DeserializeObject(parameter, typeof(GroupCondition));
}
List <tbGroup> rows = null;
if (condition != null)
{
if (condition.GroupID != "")
{
filteredData = filteredData.Where(e => e.GroupID.Contains(condition.GroupID));
}
if (condition.GroupName != "")
{
filteredData = filteredData.Where(e => e.GroupName.Contains(condition.GroupName));
}
}
int recordsTotal = filteredData.Count();
rows = filteredData.OrderBy(e => e.GroupID).Skip(start).Take(10).ToList();
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = rows,
length = rows.Count(),
recordsFiltered = recordsTotal,
recordsTotal = recordsTotal
};
return(Json(renderModel));
////////////////
//var filteredData = (from p in _db.ProductInfoes
// select new
// {
// p.ProductNo,
// p.ProductName,
// p.Spec,
// p.Unit,
// p.Capcity
// }).AsQueryable();
//int recordsTotal = _db.ProductInfoes.Count();
//var rows = filteredData.OrderBy(e => e.ProductNo).ToList();
//var rows = filteredData.OrderBy(e => e.ProductNo).Skip(start).Take(10).ToList();
//rows = filteredData.OrderBy(e => e.ProductNo).Where(s => s.ProductNo == searchProductNo).Skip(start).Take(10).ToList();
//var renderModel = new DataTablesRenderModel
//{
// draw = draw,
// data = rows,
// length = rows.Count(),
// recordsFiltered = recordsTotal,
// recordsTotal = recordsTotal
//};
//return Json(renderModel);
}
public JsonResult Get(int draw, int start)
{
List <ProductVendor> rows = null;
List <VendorProducts> vrows = null;
var VendorProductsfilteredData = _db.VendorProducts.AsQueryable();
var filteredData = (from p in _db.ProductInfoes
select new ProductVendor
{
ProductNo = p.ProductNo,
ProductName = p.ProductName,
Spec = p.Spec,
Unit = p.Unit,
Capcity = p.Capcity,
EffectiveMonth = p.EffectiveMonth,
EffectiveDay = p.EffectiveDay,
Barcode = p.Barcode,
Manufacture = false
}
).AsQueryable();
string parameter = Request.Query["search[value]"].FirstOrDefault();
VendorCondition condition = null;
if (parameter != "")
{
condition = (VendorCondition)JsonConvert.DeserializeObject(parameter, typeof(VendorCondition));
}
int recordsTotal = filteredData.Count();
rows = filteredData.OrderBy(e => e.ProductNo).ToList();
if (condition != null)
{
//根據代工廠Table(VendorProducts)，給予ProductVendor.Manufacture是否為true，true代表該代工廠有製造此商品
VendorProductsfilteredData = VendorProductsfilteredData.Where(e => e.VendorNo == condition.VendorNo);
vrows = VendorProductsfilteredData.OrderBy(e => e.ProductNo).ToList();
foreach (VendorProducts vendorProducts in vrows)
{
foreach (ProductVendor productVendor in rows)
{
if (productVendor.ProductNo.Equals(vendorProducts.ProductNo))
{
productVendor.Manufacture = true;
}
}
}
}
rows = rows.AsQueryable().OrderByDescending(x => x.Manufacture).ToList();
var renderModel = new DataTablesRenderModel
{
draw = draw,
data = rows,
length = rows.Count(),
recordsFiltered = recordsTotal,
recordsTotal = recordsTotal
};
return(Json(renderModel));
}
