public object GetInventoryReport([FromBody] InnovaDto dto)
{
var context = new innova01Entities();
return(from p in context.proc_packs.Where(x => x.inventory == 10)
join m in context.proc_materials.Where(x => x.code != "0000000" && x.code != "0000001")
on p.material equals m.material
join pc in context.proc_collections
on p.pallet equals pc.id
join inv in context.proc_invlocations
on p.invlocation equals inv.id
select new
{
Product = m.code,
//ItemCode = m.code.Substring(0,2) + "/" + m.code.Substring(2, 3) + "-" + m.code.Substring(m.code.Length - 2, 2),
ItemCode = m.code,
ProdDescription = m.name,
Regtime = p.regtime,
InvLocation = inv.code,
Pallet = pc.number,
PackSSCC = p.sscc,
PackNumber = p.number
}
);
}
public object GetDailyProductionTotal([FromBody] InnovaDto dto)
{
var context = new innova01Entities();
var startDate = dto.StartDate;
var endDate = dto.EndDate;
//List<int> stations = new List<int>();
//stations.Add(8);
//stations.Add(10);
//stations.Add(12);
//stations.Add(13);
//stations.Add(14);
//stations.Add(15);
//stations.Add(16);
return(from p in context.proc_packs.Where(x => //stations.Contains(x.station.Value) &&
x.prday >= startDate &&
x.prday <= endDate &&
x.conum != null)
group p by p.device into g
select new
{
Station = g.Key,
Nominal = g.Sum(x => x.nominal),
Weight = g.Sum(x => x.weight)
});
}
public object GetBOLData([FromBody] InnovaDto dto)
{
var context = new DB323Entities();
var startDate = dto.StartDate;
var endDate = dto.EndDate;
//var mypredicate = "OrderDate == DateTime.Parse(\"" + startDate.ToShortDateString() + "\")";// +" && OrderDate <= " + startDate.ToShortDateString();
//if (dto.CustomerNumber != "")
//{
// mypredicate += " && CustNumber == " + dto.CustomerNumber + "";
//}
//if (dto.OrderNumber != null || dto.OrderNumber != "")
//{
// mypredicate += " && OrderCode == \"" + dto.OrderNumber + "\"";
//}
return(from p in context.vwBOLProds.Where(x => x.OrderCode == dto.OrderCode)
select new
{
OrderCode = p.OrderCode,
CustNumber = p.CustNumber,
CustShort = p.CustShort,
CustLong = p.CustLong,
OrderDate = p.OrderDate,
DispDate = p.DispDate,
PO1 = p.PO1,
PO2 = p.PO2,
PO3 = p.PO3,
Comments = p.Comments,
OrderTerms = p.OrderTerms,
ShipToName = p.ShipToName,
ShipToAddress = p.ShipToAddress,
ShipToPhone = p.ShipToPhone,
MaterialID = p.MaterialID,
ProdCode = p.ProdCode,
ProdName = p.ProdName,
OrderedAmt = p.OrderedAmt,
ShippedQty = p.ShippedQty,
ShippedWeight = p.ShippedWeight,
ApproxUnitWeight = p.ApproxUnitWeight,
HowPacked = p.HowPacked,
WeightLabel = p.WeightLabel,
CustomerAddress = p.CustomerAddress,
CustomerAddress2 = p.CustomerAddress2,
CustomerCity = p.CustomerCity,
CustomerState = p.CustomerState,
CustomerZip = p.CustomerZip,
CustomerPhone = p.CustomerPhone,
Terms = p.Terms,
CWItem = p.CWItem
});
}
public object GetTodaysProductionTotal([FromBody] InnovaDto dto)
{
var context = new innova01Entities();
var startDate = DateTime.Now.Date;
return(from p in context.proc_packs.Where(x => x.prday >= startDate && x.conum != null)
group p by p.device into g
select new
{
Station = g.Key,
Nominal = g.Sum(x => x.nominal),
Weight = g.Sum(x => x.weight)
});
}
public object GetKeithsData([FromBody] InnovaDto dto)
{
// validate the key
//if(ValidateKey(dto.Key)) {
var context = new innova01Entities();
//try {
// var data = context.proc_sizes.Where(x => x.size == 1).ToList();
// return returnPackage(Request, data);
//}
//catch(Exception e) {
// var s = "";
// throw;
//}
//var startDate = new DateTime(2015,7,1);
//var endDate = new DateTime(2015,7,2);
var startDate = dto.StartDate;
var endDate = dto.StartDate.AddDays(1);
return((from m in context.proc_materials
.Where(x => x.shname == "Sample")
join p in context.proc_packs.Where(x => x.rtype != 4 &&
x.regtime >= startDate &&
x.regtime <= endDate)
on m.material equals p.material
join l in context.proc_lots
on p.lot equals l.lot
join bc in context.base_companies
on l.customer equals bc.company
select new {
m.code,
Farm = bc.name,
Pond = l.shname,
FarmPond = l.name,
Date = p.regtime,
RangeName = m.code,
RangeValue = m.name,
Weight = p.weight
}
).OrderBy(x => x.code));
//}
return(null);
}
public object GetCurrentShipping([FromBody] InnovaDto dto)
{
var context = new innova01Entities();
var startDate = DateTime.Now.Date;
var pl = from r in context.proc_packs.Where(x => x.regtime >= startDate)
orderby r.material
group r by r.material into grp
select new { key = grp.Key, cnt = grp.Count() };
//var endDate = startDate.AddDays(5);
return(from a in context.proc_orders.Where(x => x.dispatchtime >= startDate)
join b in context.proc_orderl
on a.order equals b.order
join p in context.proc_invstatus.Where(x => x.regtime >= startDate)
on b.material equals p.material into ps
from p in ps.DefaultIfEmpty()
join l in context.proc_materials
on b.material equals l.material
from tp in pl.Where(x => x.key == b.material).DefaultIfEmpty()
//on b.material equals tp.key
join bc in context.base_companies
on a.customer equals bc.company
select new
{
CustomerName = bc.name,
ItemDescription = l.name,
ItemCode = l.code,
OrderAmount = b.maxamount,
QuantityOnHand = p.units,
OrderDate = a.dispatchtime,
ShippedAmount = b.curamount,
TodayUnits = tp != null ? tp.cnt : 0
}
);
}
