public string UpdateWorkorderRouting(string workorderRoutingNo, string statusId, string stateId)
{
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
try
{
var wor = (from w in ctx.WorkOrderRouting where w.WorkOrderRoutingNo == workorderRoutingNo select w).SingleOrDefault();
if (wor != null)
{
wor.StateID = System.Convert.ToInt16(stateId);
wor.WorkOrderRoutingStatusId = System.Convert.ToInt16(statusId);
ctx.SubmitChanges();
return "OK";
}
return "No record";
}
catch (Exception e)
{
return (e.Message);
}
}
//public string NewSRMARequest()
public string NewSRMARequest(string phoneNumber, string locationId)
{
if (phoneNumber == "") phoneNumber = "0000000000";
//1- on regarde s'il existe un contact avec le numéro de tél
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
var contact = (from c in ctx.Contact where c.Phone == phoneNumber select c).SingleOrDefault();
if (contact != null)
//on recherche le customer correspondant
{
var customer = (from c in ctx.Customer where c.Contact == contact select c).SingleOrDefault();
//normalement on passe par là!
if (customer != null)
{
//Création d'un nouvel orderHeader
OrderHeader o = new OrderHeader();
o.Customer = customer;
var loc = (from l in ctx.Location where l.LocationID == System.Convert.ToInt32(locationId) select l).SingleOrDefault();
o.Location = loc;
//o.DeliveryLocationID = System.Convert.ToInt32(locationId);
o.OrderType = 'C';
o.Comment = "Demande effectuee par tél";
o.OrderStatusID = 1;
o.OrderDate = System.DateTime.Now;
ctx.OrderHeader.InsertOnSubmit(o);
ctx.SubmitChanges();
WorkOrder wo = new WorkOrder();
wo.OrderHeader = o;
wo.Type = "CAL";
wo.WorkOrderStatusID = 1;
wo.OrderHeader = o;
ctx.WorkOrder.InsertOnSubmit(wo);
//ctx.SubmitChanges();
//Wor Transport vers demandeur
WorkOrderRouting worT = new WorkOrderRouting();
worT.Location = loc;
worT.Type = 'T';
worT.OperationSequence = 10;
worT.StateID = 1;
worT.WorkOrderRoutingStatusId = 1;
worT.WorkOrder = wo;
ctx.WorkOrderRouting.InsertOnSubmit(worT);
//wor chargement/dialogue avec demandeur
WorkOrderRouting worA = new WorkOrderRouting();
worA.Location = loc;
worA.Type = 'A';
worA.OperationSequence = 20;
worA.StateID = 1;
worA.WorkOrderRoutingStatusId = 1;
worA.WorkOrder = wo;
ctx.WorkOrderRouting.InsertOnSubmit(worA);
ctx.SubmitChanges();
return "ok";
}
}
/* return (from w in ctx.V_Order
where w.OrderID == System.Convert.ToInt32(orderHeaderId)
select new OrderHeaderRecord
{
OrderId = w.OrderID,
OrderNo = w.OrderNo,
ObjetDemandeExpress = w.ObjetDemandeExpress,
CustomerFirstName = w.CustomerFirstName,
CustomerLastName = w.CustomerLastName,
OrderDate = w.OrderDate.ToString()
}).SingleOrDefault();
*/
/*
SqlCommand cmd = new SqlCommand("insert into Workorder(Type) values(@Type)", conn);
cmd.Parameters.AddWithValue("@Type", 'T');
int result = cmd.ExecuteNonQuery();
string Message;
if (result == 1)
{
Message = " Details inserted successfully";
}
else
{
Message = " Details not inserted successfully";
}
//conn.Close();
return Message;
//}*/
return "Pas OK";
}
public ProductInventoryRecord SetProductInventory(string productId, string locationId, string quantity)
{
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
var productInventory = (from pi in ctx.ProductInventory
where pi.LocationID == System.Convert.ToInt32(locationId)
&& pi.ProductID == System.Convert.ToInt32(productId)
select pi).SingleOrDefault();
if (productInventory != null)
{
productInventory.Quantity = System.Convert.ToInt16(quantity);
ctx.SubmitChanges();
}
return (from pi in ctx.ProductInventory
where (pi.LocationID == System.Convert.ToInt32(locationId)
&& pi.ProductID == System.Convert.ToInt32(productId))
select new ProductInventoryRecord
{
Location = new LocationRecord
{
LocationID = pi.Location.LocationID,
LocationName = pi.Location.Name.TrimEnd()
},
ProductID = System.Convert.ToInt32(productId),
Shelf = pi.Shelf,
Quantity = pi.Quantity,
Capacity = pi.Capacity,
DeliverThreshold = System.Convert.ToInt32(pi.DeliverThreshold),
SupplyThreshold = System.Convert.ToInt32(pi.SupplyThreshold)
}).SingleOrDefault();
}
public List<WorkOrderRecord> GetWorkorders(string status)
{
List<WorkOrderRecord> lWorkOrders = new List<WorkOrderRecord>();
using (var ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux"))
{
var listOfWorkOrders = from w in ctx.WorkOrder
where w.WorkOrderStatusID == System.Convert.ToInt16(status)
select new WorkOrderRecord
{
WorkOrderID = w.WorkOrderID,
//StartDate=(DateTime)w.StartDate,
//ModifiedDate=(DateTime)w.ModifiedDate,
type = w.Type,
WorkorderNo = w.WorkOrderNo,
//WorkorderRoutings=w.WorkOrderRouting.ToList(),
OrderHeader = new OrderHeaderRecord
{
OrderId = w.OrderHeader.OrderID,
OrderNo = w.OrderHeader.OrderNo.TrimEnd(),
CustomerFirstName = w.OrderHeader.Customer.Contact.FirstName.TrimEnd(),
CustomerLastName = w.OrderHeader.Customer.Contact.LastName.TrimEnd(),
ObjetDemandeExpress = w.OrderHeader.ObjetDemandeExpress.TrimEnd()
},
WorkorderRoutings = (from wor in w.WorkOrderRouting
select new WorkOrderRoutingRecord
{
WorkOrderRoutingNo = wor.WorkOrderRoutingNo.TrimEnd(),
Type = System.Convert.ToChar(wor.Type).ToString(),
Location = new LocationRecord
{
LocationName = wor.Location.Name.TrimEnd()
}
}).ToList()
};
return listOfWorkOrders.ToList();
}
//// List<WorkOrderRecord> l = new List<WorkOrderRecord>();
//DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
//var listOfWorkOrders = from w in ctx.WorkOrder
// where w.WorkOrderStatusID == 3
// select w;
//return listOfWorkOrders.ToList();
//foreach (WorkOrder wo in listOfWorkOrders.ToList())
//{
// WorkOrderRecord wor = new WorkOrderRecord(wo.Type);
// l.Add(wor);
//}
//return l;
}
public ResourceRecord LogSRMA()
{
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
var srma = (from c in ctx.Resource where c.ResourceID == 9 select c).SingleOrDefault();
if(srma!=null)
{
srma.DateModified = System.DateTime.Now;
ctx.SubmitChanges();
}
return (from c in ctx.Resource where c.ResourceID == 9 select new ResourceRecord
{
Name=c.Name.TrimEnd(),
//DateModified=c.DateModified
}).SingleOrDefault();
//return srma as ResourceRecord;
}
public ResourceRecord GetResourceById(string resourceId)
{
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
return (from w in ctx.Resource
where w.ResourceID == System.Convert.ToInt32(resourceId)
select new ResourceRecord
{
Name = w.Name.TrimEnd(),
// DateModified=string.Format
DateModified = String.Format("{0:u}", w.DateModified),
//LocationId = w.Location.LocationID,
ResourceId = w.ResourceID
/*OrderId = w.OrderID,
OrderNo = w.OrderNo,
ObjetDemandeExpress = w.ObjetDemandeExpress,
CustomerFirstName = w.CustomerFirstName,
CustomerLastName = w.CustomerLastName,
OrderDate = w.OrderDate.ToString()*/
}).SingleOrDefault();
}
/* public LocationRecord GetLocationById(string locationId)
{
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
return (from w in ctx.Location
where w.LocationID == System.Convert.ToInt32(locationId)
select new LocationRecord
{
LocationName = w.Name.TrimEnd(),
// DateModified=string.Format
// DateModified = String.Format("{0:u}", w.DateModified),
//LocationId = w.Location.LocationID,
// ResourceId = w.ResourceID
/*OrderId = w.OrderID,
OrderNo = w.OrderNo,
ObjetDemandeExpress = w.ObjetDemandeExpress,
CustomerFirstName = w.CustomerFirstName,
CustomerLastName = w.CustomerLastName,
OrderDate = w.OrderDate.ToString()
}).SingleOrDefault();
}*/
public SRMARecord GetSRMA()
{
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
return (from w in ctx.Resource
where w.ResourceID == 9
select new SRMARecord
{
Name = (w.Name).TrimEnd(),
State = System.Convert.ToInt32(w.ResourceStateID),
Mode = System.Convert.ToInt32(w.ModeID)
//Task = System.Convert.ToInt32(w.TaskID)
}).SingleOrDefault();
}
public ProductLevelRecord GetProductLevelByLocation(string productId, string locationId)
{
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
/* return (from x in ctx.ProductInventory
where*/
System.Nullable<long> total =
(from x in ctx.ProductInventory
where (x.ProductID == System.Convert.ToInt32(productId)
&& x.LocationID == System.Convert.ToInt32(locationId))
select (long)x.Quantity
).Sum();
return (new ProductLevelRecord(System.Convert.ToInt32(productId), System.Convert.ToInt32(locationId), total.Value));
}
public OrderHeaderRecord GetOrderHeaderById(string orderHeaderId)
{
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
return (from w in ctx.V_Order
where w.OrderID == System.Convert.ToInt32(orderHeaderId)
select new OrderHeaderRecord
{
OrderId = w.OrderID,
OrderNo = w.OrderNo,
ObjetDemandeExpress = w.ObjetDemandeExpress,
CustomerFirstName = w.CustomerFirstName,
CustomerLastName = w.CustomerLastName,
OrderDate = w.OrderDate.ToString()
}).SingleOrDefault();
}
public LocationRecord GetLocationById(string locationId)
{
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
return (from l in ctx.Location
where l.LocationID == System.Convert.ToInt32(locationId)
select new LocationRecord
{
LocationName = l.Name.TrimEnd(),
LocationID = System.Convert.ToInt32(locationId)
}).SingleOrDefault();
}
public EmployeeRecord GetEmployeeByCardId(string cardId)
{
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
return (from w in ctx.vEmployee
where w.CardID == cardId
select new EmployeeRecord
{
firstname = w.FirstName.TrimEnd(),
lastname = w.LastName.TrimEnd(),
cardID = w.CardID.TrimEnd(),
emailAddress = w.EmailAddress.TrimEnd()
}).SingleOrDefault();
}
//test
public EmployeeRecord GetEmployee()
{
EmployeeRecord emp = new EmployeeRecord();
DataClassesDataContext ctx = new DataClassesDataContext("Data Source=AIP-SQLAIPL;Initial Catalog=Aipl;User ID=ambiflux;Password=ambiflux");
return (from e in ctx.vEmployee
where e.ResourceID == 3
select new EmployeeRecord
{
firstname = e.FirstName,
lastname = e.LastName
}).SingleOrDefault();
}
