private List <WRKCTRTABLE> GetAxRoutListe(string vendor)
{
using (var context = new ax2009_ccEntities())
{
return(context.WRKCTRTABLEs.Where(x => x.VENDID == vendor && x.DATAAREAID == "Ccm" && x.ISGROUP == 0).ToList());
}
}
private List <WRKCTRTABLE> GetAxRouteGroup()
{
using (var context = new ax2009_ccEntities())
{
return(context.WRKCTRTABLEs.Where(x => x.DATAAREAID == "Ccm" && x.ISGROUP == 1).ToList());
}
}
private List <VENDPAYMMODETABLE> GetVendPayMode(string dataArea)
{
using (var context = new ax2009_ccEntities())
{
return(context.VENDPAYMMODETABLEs.Where(x => x.DATAAREAID == dataArea).ToList());
}
}
private List <PAYMTERM> GetAxPaymentTerm(string dataArea)
{
using (var context = new ax2009_ccEntities())
{
return(context.PAYMTERMs.Where(x => x.DATAAREAID == dataArea).ToList());
}
}
public List <Tbl_ReservationMainDetails> GetReservationMainDetailByFabric(string filter,
Dictionary <string, object> valuesObjects, bool GetOnhand,
int tblSalesOrder, int type, string Fabric, List <string> FabricColor, out List <GetItemOnhand_Result> OnHandList, out List <Fabric_UnitID> mainFabricList)
{
using (var entities = new WorkFlowManagerDBEntities())
{
var salesordercode = entities.TblSalesOrders.FirstOrDefault(w => w.Iserial == tblSalesOrder).SalesOrderCode;
if (valuesObjects.Any())
{
filter = filter + " and it.Fabric LIKE(@Code0)";
}
else
{
filter = filter + "it.Fabric LIKE(@Code0)";
}
valuesObjects.Add("Code0", Fabric);
var parameterCollection = ConvertToParamters(valuesObjects);
var reservationMainDetail = entities.Tbl_ReservationMainDetails.Include("Tbl_ReservationDetails.Tbl_ReservationRec").Where(filter, parameterCollection.ToArray());
//(from i in entities.Tbl_ReservationMainDetails.Include("Tbl_ReservationDetails.Tbl_ReservationRec")
var reservationMainDetails = reservationMainDetail.AsExpandable().Where(i => FabricColor.Contains(i.FabricColor)).ToList();
foreach (var item in reservationMainDetails.Select(w => w.FabricColor).Distinct().ToList())
{
if (entities.Tbl_ReservationDetails.Any(w => w.SalesOrder == salesordercode && w.Tbl_ReservationMainDetails1.Fabric == Fabric && w.Tbl_ReservationMainDetails1.FabricColor == item))
{
var rowsToRemove = reservationMainDetails.Where(w => w.FabricColor == item).ToList();
foreach (var rowToRemove in rowsToRemove)
{
reservationMainDetails.Remove(rowToRemove);
}
}
}
var fabriclist = reservationMainDetails.Select(x => x.Fabric).Distinct();
var listOnHand = new List <GetItemOnhand_Result>();
if (GetOnhand)
{
using (var model = new ax2009_ccEntities())
{
foreach (var item in reservationMainDetails.GroupBy(x => new { x.Fabric, x.Batchno, x.FabricColor, x.Location }))
{
listOnHand.AddRange(model.GetItemOnhand(item.Key.Fabric, item.Key.Batchno, item.Key.FabricColor, DateTime.Now, item.Key.Location));
}
}
OnHandList = listOnHand;
}
else
{
OnHandList = null;
}
mainFabricList = entities.Fabric_UnitID.Where(x => fabriclist.Any(l => x.Fabric_Code == l)).ToList();
return(reservationMainDetails.ToList());
}
}
public List <VENDGROUP> GetVendorGroups()
{
using (var context = new ax2009_ccEntities())
{
return(context.VENDGROUPs.Where(x => x.DATAAREAID == "Ccm").ToList());
}
}
private decimal GetExRateByCurrency(string currency, string dataArea)
{
using (var context = new ax2009_ccEntities())
{
var result = context.EXCHRATES.Where(e => e.DATAAREAID == dataArea && e.CURRENCYCODE == currency).OrderByDescending(x => x.TODATE).FirstOrDefault();
return(result.EXCHRATE1);
}
}
public List <CONFIGTABLE> GetAxConfigTable(string Item, string DataArea)
{
using (var context = new ax2009_ccEntities())
{
var Config = (from c in context.CONFIGTABLEs.Where(x => x.ITEMID == Item && x.DATAAREAID == DataArea)
select c).ToList();
return(Config);
}
}
public List <INVENTTABLE> GetAxItems(string ItemGroup, string DataArea)
{
using (var context = new ax2009_ccEntities())
{
var item = (from i in context.INVENTTABLEs.Where(x => x.ITEMGROUPID != "Fp" && x.DATAAREAID == DataArea &&
(x.ITEMGROUPID == ItemGroup || ItemGroup == null))
select i).ToList();
return(item);
}
}
private List <CURRENCY> GetAxCurrency(string dataarea, out List <EXCHRATE> exrates)
{
using (var context = new ax2009_ccEntities())
{
var oldRow = (from e in context.CURRENCies
where e.DATAAREAID == dataarea
select e).ToList();
exrates = (from e in context.EXCHRATES
where e.DATAAREAID == dataarea
select e).ToList();
return(oldRow);
}
}
public WMSLOCATION GetWmsLocation(string warehouse, string location, string vendor)
{
using (var context = new ax2009_ccEntities())
{
try
{
return
(context.WMSLOCATIONs.SingleOrDefault(x => x.INVENTLOCATIONID == warehouse && (x.WMSLOCATIONID == location && location != null) &&
(x.VENDID == vendor && vendor != null)));
}
catch (System.Exception ex)
{
throw ex;
}
}
}
public List <INVENTDIM> SearchedAxItemsDetails(string dataArea, string itemId)
{
using (var context = new ax2009_ccEntities())
{
context.INVENTSUMs.MergeOption = System.Data.Objects.MergeOption.NoTracking;
context.INVENTDIMs.MergeOption = System.Data.Objects.MergeOption.NoTracking;
var searchedItems = context.INVENTSUMs.Where(x => x.DATAAREAID == dataArea &&
x.ITEMID == itemId).Select(x => x.INVENTDIMID);
var details = context.INVENTDIMs.Where(x => x.DATAAREAID == dataArea &&
searchedItems.Any(s => s == x.INVENTDIMID)).ToList();
return(details);
}
}
public List <WareHouseDto> GetWarehouseswithOnHand()
{
using (var entities = new ax2009_ccEntities())
{
var query = from invents in entities.INVENTSUMs
join dim in entities.INVENTDIMs on new { invents.INVENTDIMID, invents.DATAAREAID }
equals new { dim.INVENTDIMID, dim.DATAAREAID }
join loc in entities.INVENTLOCATIONs on new { dim.INVENTLOCATIONID, dim.DATAAREAID }
equals new { loc.INVENTLOCATIONID, loc.DATAAREAID }
where invents.DATAAREAID == "CCm" && invents.AVAILPHYSICAL > 0
select new WareHouseDto
{
WareHouseCode = loc.INVENTLOCATIONID,
WareHouseName = loc.NAME,
};
return(query.Distinct().ToList());
}
}
public List <AxItem> SearchedAxItems(string dataArea, string itemGroup, string itemId, string itemDesc)
{
using (var context = new ax2009_ccEntities())
{
context.INVENTTABLEs.MergeOption = System.Data.Objects.MergeOption.NoTracking;
var searchedItems = (from i in context.INVENTTABLEMODULEs
join x in context.INVENTTABLEs
on new { i.ITEMID, i.DATAAREAID } equals new { x.ITEMID, x.DATAAREAID }
where x.DATAAREAID == dataArea && x.ITEMGROUPID.Contains(itemGroup) &&
x.ITEMID.Contains(itemId) && i.MODULETYPE == 0 &&
x.ITEMNAME.Contains(itemDesc)
select new AxItem()
{
ItemId = x.ITEMID,
ItemGroup = x.ITEMGROUPID,
Description = x.ITEMNAME,
InventoryUnit = i.UNITID,
}).ToList();
return(searchedItems);
}
}
public void PurchaseDyeingServicesToAxAcc(TblDyeingOrdersHeaderAcc objToPost, TblDyeingOrdersMainDetailsACC headerObjToPost, int postPostOrNo, int userIserial, string transactionGuid)
{
using (var context = new WorkFlowManagerDBEntities())
{
using (var axapta = new Axapta())
{
var credential = new NetworkCredential("bcproxy", "around1");
TblAuthUser userToLogin;
userToLogin = context.TblAuthUsers.SingleOrDefault(x => x.Iserial == userIserial);
axapta.LogonAs(userToLogin.User_Win_Login, userToLogin.User_Domain, credential, "Ccm", null, null, null);
var inventTable = axapta.CreateAxaptaRecord("InventDim");
try
{
var vendorWmsLocation = context.GetWmsLocations.SingleOrDefault(x => x.VENDID == objToPost.Vendor);
var vendorLoc =
context.GetLocations.SingleOrDefault(x => x.INVENTLOCATIONID == vendorWmsLocation.INVENTLOCATIONID);
var purchId = "Rc_ " + headerObjToPost.DyeingProductionOrder.ToString() + headerObjToPost.TransId.ToString() + headerObjToPost.TransactionType;
var tableName = "PurchTable";
var purchTableRecord = axapta.CreateAxaptaRecord(tableName);
purchTableRecord.Clear();
purchTableRecord.InitValue();
purchTableRecord.set_Field("PurchId", purchId);
purchTableRecord.set_Field("DeliveryDate", headerObjToPost.TblDyeingOrdersDetailsAccs.OrderByDescending(x => x.EstimatedDeliveryDate).FirstOrDefault().EstimatedDeliveryDate ?? DateTime.Now);
var headerax = axapta.CallStaticRecordMethod("VendTable", "find", objToPost.Vendor) as AxaptaRecord;
purchTableRecord.Call("initFromVendTable", headerax);
purchTableRecord.Insert();
foreach (var item in headerObjToPost.TblDyeingOrdersDetailsAccs)
{
tableName = "PurchLine";
foreach (var servicerow in item.DyeingOrderDetailsServicesAccs)
{
var axaptaRecord = axapta.CreateAxaptaRecord(tableName);
axaptaRecord.Clear();
axaptaRecord.InitValue();
inventTable.Clear();
inventTable.set_Field("InventLocationId", vendorLoc.INVENTLOCATIONID);
if (item.Color != null)
{
inventTable.set_Field("InventColorId", item.Color);
}
var importNew = axapta.CreateAxaptaObject("CreateProductionJournals");
var producationOrder = "Free";
using (var model = new ax2009_ccEntities())
{
// var batch = item.BatchNo.ToString();
var firstOrDefault = model.PRODCONNECTIONs.FirstOrDefault(
x =>
x.DYEDITEM == item.DyedFabric && x.TRANSID == item.DyeingProductionOrder &&
x.FROMCONFIG == item.Color &&
// x.FROMBATCH == batch &&
x.JOURNALLINKID == item.TransId);
if (firstOrDefault != null)
{
producationOrder =
firstOrDefault.PRODID;
}
}
if (producationOrder == null || producationOrder == "")
{
producationOrder = "Free";
}
importNew.Call("CreateConfig", servicerow.ServiceCode, item.Color);
importNew.Call("CreateBatch", servicerow.ServiceCode, producationOrder);
inventTable.set_Field("configId", item.Color);
inventTable.set_Field("inventBatchId", producationOrder);
inventTable = axapta.CallStaticRecordMethod("InventDim", "findOrCreate", inventTable) as AxaptaRecord;
if (inventTable != null)
{
var tempx = inventTable.get_Field("inventDimId").ToString();
axaptaRecord.set_Field("InventDimId", tempx);
}
axaptaRecord.set_Field("ItemId", servicerow.ServiceCode);
axaptaRecord.set_Field("purchId", purchId);
axaptaRecord.set_Field("QtyOrdered", Convert.ToDecimal(item.CalculatedTotalQty.ToString()));
decimal price = 1;
//context.TblTradeAgreementDetails.Where(x => x.ItemCode == servicerow.ServiceCode && x.TblTradeAgreementHeader1.Vendor == objToPost.Vendor);
axaptaRecord.set_Field("PurchPrice", Convert.ToDecimal(price));
axaptaRecord.set_Field("PurchQty", Convert.ToDecimal(item.CalculatedTotalQty.ToString()));
axaptaRecord.set_Field("LineAmount", Convert.ToDecimal(item.CalculatedTotalQty.ToString()) * price);
axaptaRecord.Call("createLine", true, true, true, true, true, false);
}
//No errors occured, Commit!
//Axapta.TTSCommit();
if (postPostOrNo == 1)
{
var importNew = axapta.CreateAxaptaObject("CreateProductionJournals");
importNew.Call("PostPurchaseOrder", purchId, objToPost.TransactionDate ?? DateTime.Now);
PickingListForAxServicesAcc(objToPost, headerObjToPost, postPostOrNo, userIserial,
transactionGuid);
}
}
}
catch (Exception ex)
{
//There was some errors, Abort transaction and Raise error!
//Axapta.TTSAbort();
throw new Exception(ex.Message);
}
finally
{
//Finally logoff the Axapta Session
axapta.Logoff();
}
}
}
}
public List <ItemsDto> SearchForRfqItems(string searchTerm, string descSearchTerm, string source)
{
WorkFlowManagerDBEntities context;
using (context = new WorkFlowManagerDBEntities())
{
context.CommandTimeout = 120;
ax2009_ccEntities axContext;
using (axContext = new ax2009_ccEntities())
{
axContext.CommandTimeout = 120;
if (source.ToLower().Contains("ac"))
{
#region accessories
var temp =
(from i in axContext.INVENTTABLEMODULEs
join x in axContext.INVENTTABLEs
on new { i.ITEMID, i.DATAAREAID } equals new { x.ITEMID, x.DATAAREAID }
where i.MODULETYPE == 0 &&
x.ITEMGROUPID.ToUpper() == "ACCESSORIES" &&
i.DATAAREAID == "Ccm" &&
(string.IsNullOrEmpty(searchTerm) || x.ITEMID.ToUpper()
.StartsWith(searchTerm.Trim().ToUpper())) &&
(string.IsNullOrEmpty(descSearchTerm) || x.ITEMNAME.ToUpper()
.Contains(descSearchTerm.Trim().ToUpper()))
join y in axContext.INVENTDIMCOMBINATIONs
on new { i.ITEMID, i.DATAAREAID } equals new { y.ITEMID, y.DATAAREAID }
join z in axContext.INVENTDIMs
on y.DATAAREAID equals z.DATAAREAID
where z.INVENTDIMID == y.INVENTDIMID
select new ItemsDto
{
Code = x.ITEMID,
Size = z.INVENTSIZEID,
Config = z.CONFIGID,
Batch = z.INVENTBATCHID,
ItemGroup = "ACCESSORIES",
Name = x.ITEMNAME,
Desc = x.ITEMNAME
}).Take(20).ToList();
temp.ForEach(
x =>
x.Images =
context.tbl_AccessoryAttributesDetails.Where(f => f.Code == x.Code && f.Configuration == x.Config && f.Size == x.Size)
.Select(f => new ImageDto {
Image = f.AccImage
}).DefaultIfEmpty().ToList());
temp.ForEach(
x =>
x.Image =
context.tbl_AccessoryAttributesDetails.Where(
f => f.Code == x.Code && f.Configuration == x.Config && f.Size == x.Size)
.Select(f => f.AccImage).FirstOrDefault());
var ret = temp;
return(ret);
#endregion accessories
}
if (source.ToLower().Contains("fab"))
{
#region Fabric
var temp =
(from i in axContext.INVENTTABLEMODULEs
join x in axContext.INVENTTABLEs
on new { i.ITEMID, i.DATAAREAID } equals new { x.ITEMID, x.DATAAREAID }
where i.MODULETYPE == 0 &&
x.ITEMGROUPID.ToUpper() != "ACCESSORIES" &&
i.DATAAREAID == "Ccm" &&
(string.IsNullOrEmpty(searchTerm) || x.ITEMID.ToUpper()
.StartsWith(searchTerm.Trim().ToUpper())) &&
(string.IsNullOrEmpty(descSearchTerm) || x.ITEMNAME.ToUpper()
.Contains(descSearchTerm.Trim().ToUpper()))
join y in axContext.INVENTDIMCOMBINATIONs
on new { i.ITEMID, i.DATAAREAID } equals new { y.ITEMID, y.DATAAREAID }
join z in axContext.INVENTDIMs
on y.DATAAREAID equals z.DATAAREAID
where z.INVENTDIMID == y.INVENTDIMID
select new ItemsDto
{
Code = x.ITEMID,
Size = z.INVENTSIZEID,
Config = z.CONFIGID,
Batch = z.INVENTBATCHID,
ItemGroup = "Fabric",
Name = x.ITEMNAME,
Desc = x.ITEMNAME
}).Take(20).ToList();
temp.ForEach(
x =>
x.Images =
context.tbl_FabricImage.Where(f => f.FabricCode == x.Code)
.Select(f => new ImageDto {
Image = f.FabImage
}).DefaultIfEmpty().ToList());
temp.ForEach(x => x.Image = context.tbl_FabricImage.Where(f => f.FabricCode == x.Code)
.Select(f => f.FabImage).FirstOrDefault());
var ret = temp;
return(ret);
#endregion Fabric
}
if (source.ToLower().Contains("gen"))
{
#region Generic
var temp = context.Fabric_new
.Where(x => (string.IsNullOrEmpty(searchTerm) || x.Fabric_Code.ToUpper()
.StartsWith(searchTerm.Trim().ToUpper())) &&
(string.IsNullOrEmpty(descSearchTerm) || x.Fabric_Ename.ToUpper()
.Contains(descSearchTerm.Trim().ToUpper())) &&
!x.Fabric_FabricType.ToLower().Contains("oper"))
.Select(x => new ItemsDto
{
Code = x.Fabric_Code,
Name = x.Fabric_Ename,
ItemGroup = "Generic"
});
return(temp.ToList());
#endregion Generic
}
}
return(new List <ItemsDto>());
}
}
public void PurchaseDyeingServicesToAx(TblDyeingOrdersHeader objToPost, TblDyeingOrdersMainDetail headerObjToPost, int postPostOrNo, int userIserial, string transactionGuid)
{
using (var context = new WorkFlowManagerDBEntities())
{
using (var axapta = new Axapta())
{
var credential = new NetworkCredential("bcproxy", "around1");
TblAuthUser userToLogin = context.TblAuthUsers.SingleOrDefault(x => x.Iserial == userIserial);
axapta.LogonAs(userToLogin.User_Win_Login, userToLogin.User_Domain, credential, "Ccm", null, null, null);
var inventTable = axapta.CreateAxaptaRecord("InventDim");
try
{
var vendorWmsLocation = context.GetWmsLocations.FirstOrDefault(x => x.VENDID == objToPost.Vendor);
var vendorLoc =
context.GetLocations.FirstOrDefault(x => x.INVENTLOCATIONID == vendorWmsLocation.INVENTLOCATIONID);
var purchId = "Rc_ " + objToPost.DocPlan.ToString() + objToPost.DyeingProductionOrder + headerObjToPost.TransId.ToString() + headerObjToPost.TransactionType;
var tableName = "PurchTable";
purchId = GenerateNewPurchase(purchId);
var purchTableRecord = axapta.CreateAxaptaRecord(tableName);
purchTableRecord.Clear();
purchTableRecord.InitValue();
purchTableRecord.set_Field("PurchId", purchId);
purchTableRecord.set_Field("DeliveryDate", headerObjToPost.TblDyeingOrdersDetails.OrderByDescending(x => x.EstimatedDeliveryDate).FirstOrDefault().EstimatedDeliveryDate ?? DateTime.Now);
var headerax = axapta.CallStaticRecordMethod("VendTable", "find", objToPost.Vendor) as AxaptaRecord;
purchTableRecord.Call("initFromVendTable", headerax);
purchTableRecord.Insert();
foreach (var item in headerObjToPost.TblDyeingOrdersDetails)
{
tableName = "PurchLine";
foreach (var servicerow in item.DyeingOrderDetailsServices)
{
var axaptaRecord = axapta.CreateAxaptaRecord(tableName);
axaptaRecord.Clear();
axaptaRecord.InitValue();
inventTable.Clear();
inventTable.set_Field("InventLocationId", vendorLoc.INVENTLOCATIONID);
inventTable.set_Field("wMSLocationId", vendorWmsLocation.WMSLOCATIONID);
if (item.Color != null)
{
inventTable.set_Field("InventColorId", item.Color);
}
var importNew = axapta.CreateAxaptaObject("CreateProductionJournals");
var producationOrder = "Free";
using (var model = new ax2009_ccEntities())
{
var firstOrDefault = model.PRODCONNECTIONs.FirstOrDefault(
x =>
x.DYEDITEM == item.DyedFabric && x.TRANSID == item.DyeingProductionOrder &&
x.TOCONFIG == item.Color && x.PRODID != "");
if (firstOrDefault != null)
{
producationOrder =
firstOrDefault.PRODID;
}
}
if (producationOrder == null || (string)producationOrder == "")
{
producationOrder = "Free";
}
importNew.Call("CreateConfig", servicerow.ServiceCode, item.Color);
importNew.Call("CreateBatch", servicerow.ServiceCode, producationOrder);
inventTable.set_Field("configId", item.Color);
inventTable.set_Field("inventBatchId", producationOrder);
inventTable.set_Field("INVENTSITEID", vendorLoc.INVENTSITEID);
inventTable = axapta.CallStaticRecordMethod("InventDim", "findOrCreate", inventTable) as AxaptaRecord;
if (inventTable != null)
{
var tempx = inventTable.get_Field("inventDimId").ToString();
axaptaRecord.set_Field("InventDimId", tempx);
}
axaptaRecord.set_Field("PurchUnit", "Kg");
axaptaRecord.set_Field("ItemId", servicerow.ServiceCode);
axaptaRecord.set_Field("purchId", purchId);
axaptaRecord.set_Field("QtyOrdered", Convert.ToDecimal(item.CalculatedTotalQty.ToString()));
decimal price = 1;
price = (decimal)servicerow.Qty;
axaptaRecord.set_Field("PurchPrice", Convert.ToDecimal(price));
axaptaRecord.set_Field("PurchQty", Convert.ToDecimal(item.CalculatedTotalQty.ToString()));
axaptaRecord.set_Field("LineAmount", Convert.ToDecimal(price * (decimal)item.CalculatedTotalQty));
axaptaRecord.Call("createLine", true, true, false, true, true, false);
}
}
if (postPostOrNo == 1)
{
var importNew = axapta.CreateAxaptaObject("CreateProductionJournals");
importNew.Call("PostPurchaseOrder", purchId, objToPost.TransactionDate ?? DateTime.Now);
PickingListForAxServices(objToPost, headerObjToPost, postPostOrNo, userIserial,
transactionGuid);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
axapta.Logoff();
}
}
}
}