// Update in transaction manner TOPDOWN(insert/update), BOTTOMUP(delete)
public dynamic Postupdate(JArray pPostedData)
{
bool mCommit = true;
var serializer = new JsonSerializer();
int mfdocnumber = 0;
string mErrMsg = "";
var salesordersinsert = new List<salesorder>();
var salesordersupdate = new List<salesorder>();
var salesdetailsinsert = new List<salesdetail>();
var salesdetailsupdate = new List<salesdetail>();
var salesdetailsdelete = new List<salesdetail>();
var inventorytrxinsert = new List<inventorytrx>();
// Get Data From JSON
ofPopulateModel(pPostedData, salesordersinsert, "salesordersinsert", "ServerData.salesorder, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, salesordersupdate, "salesordersupdate", "ServerData.salesorder, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, salesdetailsinsert, "salesdetailsinsert", "ServerData.salesdetail, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, salesdetailsupdate, "salesdetailsupdate", "ServerData.salesdetail, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, salesdetailsdelete, "salesdetailsdelete", "ServerData.salesdetail, ServerData", serializer); // Fill Deletes
// Update using transaction
using (TransactionScope transaction = new TransactionScope())
{
try
{
// Header Inserting Assign Next document #
if (salesordersinsert.Count() > 0)
{
var mCmpny = new pcdr.Controllers.CompanyController();
mfdocnumber = mCmpny.ofGetnextsequence("soticket"); // Get Next seq
salesordersinsert.First().fdocnumber = mfdocnumber;
_db.salesorders.Add(salesordersinsert.First());
_db.SaveChanges();
}
ofDBSave(salesordersupdate, _db.salesorders, "U"); // Update
// Delete
foreach (salesdetail sc in salesdetailsdelete) _db.salesdetails.Remove(_db.salesdetails.Find(sc.fsoid, sc.fsodid));
// salesdetail
ofDBSave(salesdetailsinsert, _db.salesdetails, "I"); // Insert
ofDBSave(salesdetailsupdate, _db.salesdetails, "U"); // Update
_db.SaveChanges();
}
catch (InvalidCastException e)
{
mCommit = false;
mErrMsg = e.Message;
}
if (mCommit) transaction.Complete();
}
return new
{
success = mCommit,
fdocnumber = mfdocnumber,
errmsg = mErrMsg
};
}
// Update in transaction manner TOPDOWN(insert/update), BOTTOMUP(delete)
public dynamic Postupdate(JArray pPostedData)
{
bool mCommit = true;
var serializer = new JsonSerializer();
int mfdocnumber = 0;
string mErrMsg = "";
var sales_order_headersinsert = new List<sales_order_header>();
var sales_order_headersupdate = new List<sales_order_header>();
var sales_order_detailsinsert = new List<sales_order_detail>();
var sales_order_detailsupdate = new List<sales_order_detail>();
var sales_order_detailsdelete = new List<sales_order_detail>();
var salespaymentsinsert = new List<salespayment>();
var salespaymentsupdate = new List<salespayment>();
var salespaymentsdelete = new List<salespayment>();
var sales_order_logsinsert = new List<sales_order_log>();
var addressesinsert = new List<address>();
var addressesupdate = new List<address>();
var contactsinsert = new List<contact>();
var contactsupdate = new List<contact>();
var contactsdelete = new List<contact>();
// Get Data From JSON
ofPopulateModel(pPostedData, sales_order_headersinsert, "sales_order_headersinsert", "ServerData.sales_order_header, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, sales_order_headersupdate, "sales_order_headersupdate", "ServerData.sales_order_header, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, sales_order_detailsinsert, "sales_order_detailsinsert", "ServerData.sales_order_detail, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, sales_order_detailsupdate, "sales_order_detailsupdate", "ServerData.sales_order_detail, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, sales_order_detailsdelete, "sales_order_detailsdelete", "ServerData.sales_order_detail, ServerData", serializer); // Fill Deletes
ofPopulateModel(pPostedData, salespaymentsinsert, "salespaymentsinsert", "ServerData.salespayment, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, salespaymentsupdate, "salespaymentsupdate", "ServerData.salespayment, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, salespaymentsdelete, "salespaymentsdelete", "ServerData.salespayment, ServerData", serializer); // Fill Deletes
ofPopulateModel(pPostedData, sales_order_logsinsert, "sales_order_logsinsert", "ServerData.sales_order_log, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, addressesinsert, "addressesinsert", "ServerData.address, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, addressesupdate, "addressesupdate", "ServerData.address, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, contactsinsert, "contactsinsert", "ServerData.contact, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, contactsupdate, "contactsupdate", "ServerData.contact, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, contactsdelete, "contactsdelete", "ServerData.contact, ServerData", serializer); // Fill Deletes
// Update using transaction
using (TransactionScope transaction = new TransactionScope())
{
try
{
// Address Insert, Update First
ofDBSave(addressesinsert, _db.addresses, "I"); // Insert
ofDBSave(addressesupdate, _db.addresses, "U"); // Update
// contact: Delete, Insert, Update
foreach (contact sc in contactsdelete) _db.contacts.Remove(_db.contacts.Find(sc.fcid, sc.faid));
ofDBSave(contactsinsert, _db.contacts, "I"); // Insert
ofDBSave(contactsupdate, _db.contacts, "U"); // Update
// Sales Header Inserting Assign Next document #
if (sales_order_headersinsert.Count() > 0)
{
var mCmpny = new pcdr.Controllers.CompanyController();
mfdocnumber = mCmpny.ofGetnextsequence("ticket"); // Get Next seq
sales_order_headersinsert.First().fticket = mfdocnumber;
_db.sales_order_header.Add(sales_order_headersinsert.First());
_db.SaveChanges();
}
if (sales_order_headersupdate.Count() > 0) sales_order_headersupdate.First().flastupdate = DateTime.Now; // Always save server datetime in case machine is bad
ofDBSave(sales_order_headersupdate, _db.sales_order_header, "U"); // Update
// Delete
foreach (sales_order_detail sc in sales_order_detailsdelete) _db.sales_order_detail.Remove(_db.sales_order_detail.Find(sc.fsdid, sc.fsoid));
foreach (salespayment sc in salespaymentsdelete) _db.salespayments.Remove(_db.salespayments.Find(sc.fsoid, sc.fsopid));
// sales_order_detail
ofDBSave(sales_order_detailsinsert, _db.sales_order_detail, "I"); // Insert
ofDBSave(sales_order_detailsupdate, _db.sales_order_detail, "U"); // Update
// salespayment
ofDBSave(salespaymentsinsert, _db.salespayments, "I"); // Insert
ofDBSave(salespaymentsupdate, _db.salespayments, "U"); // Update
// sales_order_logs
ofDBSave(sales_order_logsinsert, _db.sales_order_log, "I"); // Insert
_db.SaveChanges();
}
catch (InvalidCastException e)
{
mCommit = false;
mErrMsg = e.Message;
}
if (mCommit) transaction.Complete();
}
return new
{
success = mCommit,
fdocnumber = mfdocnumber,
errmsg = mErrMsg
};
}
//// Save Vendor favorites
//public dynamic PostFavorites(JArray pPostedData)
//{
// bool mCommit = true;
// var itemvendorsinsert = new List<itemvendor>();
// itemvendor ctrx;
// // Update using transaction
// using (TransactionScope transaction = new TransactionScope())
// {
// try
// {
// foreach (JObject mrow in pPostedData)
// {
// string fitem = (string)mrow["fitem"];
// int fvid = (int)mrow["fvid"];
// // Find if inventory item
// var mim = (from iu in _db.itemunits.Where(t => t.fitem == fitem)
// from im in _db.itemmasters.Where(t => t.fimid == iu.fimid)
// select new
// {
// im.ftype,
// im.fimid
// }).ToList().FirstOrDefault();
// // Only for 'I'nvnetory Type
// if (mim.ftype == "I")
// {
// // Only new items
// if (_db.itemvendors.Where(t => t.fvid == fvid && t.fitem == fitem).Count() == 0) // See if exists
// {
// ctrx = new itemvendor();
// ctrx.fimid = mim.fimid;
// ctrx.fitem = fitem;
// ctrx.fivid = (int)mrow["fvid"];
// ctrx.fvid = fvid;
// ctrx.fdescription = (string)mrow["fdescription"];
// itemvendorsinsert.Add(ctrx);
// }
// }
// }
// ofDBSave(itemvendorsinsert, _db.itemvendors, "I"); // Insert
// _db.SaveChanges();
// }
// catch (InvalidCastException e)
// {
// mCommit = false;
// }
// if (mCommit) transaction.Complete();
// }
// return new
// {
// success = mCommit
// };
//}
// Update in transaction manner TOPDOWN(insert/update), BOTTOMUP(delete)
public dynamic Postupdate(JArray pPostedData)
{
bool mCommit = true;
var serializer = new JsonSerializer();
int mfdocnumber = 0;
var purchaseordersinsert = new List<purchaseorder>();
var purchaseordersupdate = new List<purchaseorder>();
var purchasedetailsinsert = new List<purchasedetail>();
var purchasedetailsupdate = new List<purchasedetail>();
var purchasedetailsdelete = new List<purchasedetail>();
var inventorytrxinsert = new List<inventorytrx>();
// Get Data From JSON
ofPopulateModel(pPostedData, purchaseordersinsert, "purchaseordersinsert", "ServerData.purchaseorder, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, purchaseordersupdate, "purchaseordersupdate", "ServerData.purchaseorder, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, purchasedetailsinsert, "purchasedetailsinsert", "ServerData.purchasedetail, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, purchasedetailsupdate, "purchasedetailsupdate", "ServerData.purchasedetail, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, purchasedetailsdelete, "purchasedetailsdelete", "ServerData.purchasedetail, ServerData", serializer); // Fill Deletes
// Update using transaction
using (TransactionScope transaction = new TransactionScope())
{
try
{
// Header Inserting Assign Next document #
if (purchaseordersinsert.Count() > 0)
{
var mCmpny = new pcdr.Controllers.CompanyController();
mfdocnumber = mCmpny.ofGetnextsequence("poticket"); // Get Next seq
purchaseordersinsert.First().fponumber = mfdocnumber;
_db.purchaseorders.Add(purchaseordersinsert.First());
_db.SaveChanges();
}
ofDBSave(purchaseordersupdate, _db.purchaseorders, "U"); // Update
// Get ponumber
var row = (purchaseordersinsert.Count() > 0) ? purchaseordersinsert.First() : purchaseordersupdate.First();
string mfdoctype = row.fstatus;
if (mfdoctype == "V") mfdoctype = "VP"; // Void
if (mfdoctype == "O") mfdoctype = "P"; // Open
// Create inventorytrx for each detail
inventorytrx mtrx;
purchasedetail morgtrx;
itemunit mitemunit;
itemmaster mitemmaster;
// If VOID reverse original Qty's
if (mfdoctype == "VP")
{
var mNotChanged = _db.purchasedetails.AsNoTracking().Where(t => t.fpoid == row.fpoid).ToList(); // Get Details not modified us ToList() to prevent creating nesting DataReader
foreach (purchasedetail sc in mNotChanged)
{
if (sc.fqty != 0) // Valid Qty
{
mitemunit = _db.itemunits.AsNoTracking().Where(t => t.fitem == sc.fitem).FirstOrDefault();
mitemmaster = _db.itemmasters.AsNoTracking().Where(t => t.fimid == mitemunit.fimid).FirstOrDefault();
// Only 'I'nventory
if (mitemmaster.ftype == "I")
{
// Reversal Trx with fqty * -1
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = sc.fpoid;
mtrx.fdoctype = "P";
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.fshipto;
mtrx.fqty = (sc.fqty * mitemunit.funits) * -1;
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
}
}
}
}
else {
// Insert
foreach (purchasedetail sc in purchasedetailsinsert)
{
mitemunit = _db.itemunits.AsNoTracking().Where(t => t.fitem == sc.fitem).FirstOrDefault(); // Itemunits funits
mitemmaster = _db.itemmasters.AsNoTracking().Where(t => t.fimid == mitemunit.fimid).FirstOrDefault();
// Only 'I'nventory
if (mitemmaster.ftype == "I")
{
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = sc.fpoid;
mtrx.fdoctype = mfdoctype;
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.fshipto;
mtrx.fqty = (mfdoctype == "R") ? sc.freceivedqty * mitemunit.funits : sc.fqty * mitemunit.funits; // If Receiving use proper Qty
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
// Update ItemVendors price only at receiving
if (mfdoctype == "R")
{
var rec = _db.itemvendors.Where(t => t.fitem == sc.fitem && t.fvid == row.fvid).First();
if (rec != null && mtrx.famount > 0) rec.fcost = mtrx.famount;
}
}
}
// Delete
foreach (purchasedetail sc in purchasedetailsdelete)
{
//morgtrx = _db.purchasedetails.Find(sc.fpoid, sc.fpodid); // Get original rec
morgtrx = _db.purchasedetails.AsNoTracking().Where(t => t.fpoid == sc.fpoid && t.fpodid == sc.fpodid).FirstOrDefault();
// Only if valid qty
if (morgtrx.fqty != 0)
{
mitemunit = _db.itemunits.AsNoTracking().Where(t => t.fitem == sc.fitem).FirstOrDefault(); // Itemunits funits
mitemmaster = _db.itemmasters.AsNoTracking().Where(t => t.fimid == mitemunit.fimid).FirstOrDefault();
// Only 'I'nventory
if (mitemmaster.ftype == "I")
{
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = morgtrx.fpoid;
mtrx.fdoctype = "P"; // Only 'P'urchase trx
mtrx.fitem = morgtrx.fitem;
mtrx.flocation = (int)row.fshipto;
mtrx.fqty = (morgtrx.fqty * mitemunit.funits) * -1; // Reverse Qty
mtrx.famount = morgtrx.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
}
}
}
// Update
foreach (purchasedetail sc in purchasedetailsupdate)
{
// Get original rec 'AsNoTracking' but no plan to update it
morgtrx = _db.purchasedetails.AsNoTracking().Where(t => t.fpoid == sc.fpoid && t.fpodid == sc.fpodid).FirstOrDefault();
mitemunit = _db.itemunits.AsNoTracking().Where(t => t.fitem == sc.fitem).FirstOrDefault();
mitemmaster = _db.itemmasters.AsNoTracking().Where(t => t.fimid == mitemunit.fimid).FirstOrDefault();
// Only 'I'nventory
if (mitemmaster.ftype == "I")
{
// Open Orders and Qty was modified
if (mfdoctype == "P" && sc.fqty != morgtrx.fqty)
{
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = morgtrx.fpoid;
mtrx.fdoctype = mfdoctype;
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.fshipto;
mtrx.fqty = (sc.fqty - morgtrx.fqty) * mitemunit.funits; // Difference
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
}
else if (mfdoctype == "R")
{
if (morgtrx.fqty != 0) // Reverse only if valid Qty
{
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = morgtrx.fpoid;
mtrx.fdoctype = "P";
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.fshipto;
mtrx.fqty = (morgtrx.fqty * mitemunit.funits) * -1; // Reverse
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
}
if (sc.freceivedqty != 0) // Valid Qty
{
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = morgtrx.fpoid;
mtrx.fdoctype = mfdoctype;
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.fshipto;
mtrx.fqty = sc.freceivedqty * mitemunit.funits;
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
// Update ItemVendors price only at receiving
var rec = _db.itemvendors.Where(t => t.fitem == sc.fitem && t.fvid == row.fvid).First(); // Get original rec
if (rec != null && mtrx.famount > 0) rec.fcost = mtrx.famount;
}
}
}
}
// Records not modified must also create transactions if 'R'eceiving
if (mfdoctype == "R")
{
var mNotChanged = _db.purchasedetails.AsNoTracking().Where(t => t.fpoid == row.fpoid).ToList(); // Get Details not modified us ToList() to prevent creating nesting DataReader
foreach (purchasedetail sc in mNotChanged)
{
if (purchasedetailsupdate.Where(t => t.fpodid == sc.fpodid).Count() == 0) // Not found under modified
{
if (sc.freceivedqty != 0) // Valid Qty
{
mitemunit = _db.itemunits.AsNoTracking().Where(t => t.fitem == sc.fitem).FirstOrDefault();
mitemmaster = _db.itemmasters.AsNoTracking().Where(t => t.fimid == mitemunit.fimid).FirstOrDefault();
// Only 'I'nventory
if (mitemmaster.ftype == "I")
{
// Receiving Trx with freceivedqty
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = sc.fpoid;
mtrx.fdoctype = mfdoctype;
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.fshipto;
mtrx.fqty = sc.freceivedqty * mitemunit.funits;
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
// Reversal Trx with fqty * -1
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = sc.fpoid;
mtrx.fdoctype = "P";
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.fshipto;
mtrx.fqty = (sc.fqty * mitemunit.funits) * -1;
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
// Update ItemVendors price only at receiving
var rec = _db.itemvendors.Where(t => t.fitem == sc.fitem && t.fvid == row.fvid).First(); // Get original rec
if (rec != null && sc.fprice > 0) rec.fcost = sc.fprice;
}
}
}
}
}
// Delete
foreach (purchasedetail sc in purchasedetailsdelete) _db.purchasedetails.Remove(_db.purchasedetails.Find(sc.fpoid, sc.fpodid));
// purchasedetail
ofDBSave(purchasedetailsinsert, _db.purchasedetails, "I"); // Insert
ofDBSave(purchasedetailsupdate, _db.purchasedetails, "U"); // Update
}
// Save inventorytrx
ofDBSave(inventorytrxinsert, _db.inventorytrxes, "I"); // Always Insert
_db.SaveChanges();
}
catch (InvalidCastException e)
{
mCommit = false;
}
if (mCommit) transaction.Complete();
}
return new
{
success = mCommit,
fponumber = mfdocnumber
};
}
// public dynamic GetPriceList()
// {
// return _db.priceclasses;
// }
// public dynamic GetItemUnits(int pfimid)
// {
// return _db.itemunits.Where(t => t.fimid == pfimid).OrderBy(t => t.fuomdescription);
// }
// Return next available item number
public dynamic GetNextItem()
{
int mNextSeq = new pcdr.Controllers.CompanyController().ofGetnextsequence("itemnumber"); // Get next sequence
return new
{
fitem = "730742" + mNextSeq.ToString("D6")
};
}
// Update in transaction manner TOPDOWN(insert/update), BOTTOMUP(delete)
public dynamic Postupdate(JArray pPostedData)
{
bool mCommit = true;
var serializer = new JsonSerializer();
int mfdocnumber = 0;
string mErrMsg = "";
var salesordersinsert = new List<salesorder>();
var salesordersupdate = new List<salesorder>();
var salesdetailsinsert = new List<salesdetail>();
var salesdetailsupdate = new List<salesdetail>();
var salesdetailsdelete = new List<salesdetail>();
var salespaymentsinsert = new List<salespayment>();
var salespaymentsupdate = new List<salespayment>();
var salespaymentsdelete = new List<salespayment>();
var inventorytrxinsert = new List<inventorytrx>();
// Get Data From JSON
ofPopulateModel(pPostedData, salesordersinsert, "salesordersinsert", "ServerData.salesorder, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, salesordersupdate, "salesordersupdate", "ServerData.salesorder, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, salesdetailsinsert, "salesdetailsinsert", "ServerData.salesdetail, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, salesdetailsupdate, "salesdetailsupdate", "ServerData.salesdetail, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, salesdetailsdelete, "salesdetailsdelete", "ServerData.salesdetail, ServerData", serializer); // Fill Deletes
ofPopulateModel(pPostedData, salespaymentsinsert, "salespaymentsinsert", "ServerData.salespayment, ServerData", serializer); // Fill Inserts
ofPopulateModel(pPostedData, salespaymentsupdate, "salespaymentsupdate", "ServerData.salespayment, ServerData", serializer); // Fill Updates
ofPopulateModel(pPostedData, salespaymentsdelete, "salespaymentsdelete", "ServerData.salespayment, ServerData", serializer); // Fill Deletes
// Update using transaction
using (TransactionScope transaction = new TransactionScope())
{
try
{
// Header Inserting Assign Next document #
if (salesordersinsert.Count() > 0)
{
var mCmpny = new pcdr.Controllers.CompanyController();
mfdocnumber = mCmpny.ofGetnextsequence("soticket"); // Get Next seq
salesordersinsert.First().fdocnumber = mfdocnumber;
_db.salesorders.Add(salesordersinsert.First());
_db.SaveChanges();
}
ofDBSave(salesordersupdate, _db.salesorders, "U"); // Update
// Get ponumber
var row = (salesordersinsert.Count() > 0) ? salesordersinsert.First() : salesordersupdate.First();
string mfdoctype = row.fstatus;
// Create inventorytrx for each detail
inventorytrx mtrx;
salesdetail morgtrx;
itemunit mitemunit;
itemmaster mitemmaster;
// If VOID reverse original Qty's
if (mfdoctype == "V")
{
var mNotChanged = _db.salesdetails.AsNoTracking().Where(t => t.fsoid == row.fsoid).ToList(); // Get Details not modified ToList() to prevent creating nesting DataReader
foreach (salesdetail sc in mNotChanged)
{
if (sc.fqty != 0) // Valid Qty
{
mitemunit = _db.itemunits.AsNoTracking().Where(t => t.fitem == sc.fitem).FirstOrDefault();
mitemmaster = _db.itemmasters.AsNoTracking().Where(t => t.fimid == mitemunit.fimid).FirstOrDefault();
// Only 'I'nventory
if (mitemmaster.ftype == "I")
{
// Reversal Trx with fqty * -1
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = sc.fsoid;
mtrx.fdoctype = "S";
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.flocation;
mtrx.fqty = (sc.fqty * mitemunit.funits) * -1;
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
}
}
}
}
else
{
// Insert
foreach (salesdetail sc in salesdetailsinsert)
{
mitemunit = _db.itemunits.AsNoTracking().Where(t => t.fitem == sc.fitem).FirstOrDefault(); // Itemunits funits
mitemmaster = _db.itemmasters.AsNoTracking().Where(t => t.fimid == mitemunit.fimid).FirstOrDefault();
// Only 'I'nventory
if (mitemmaster.ftype == "I")
{
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = sc.fsoid;
mtrx.fdoctype = mfdoctype;
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.flocation;
mtrx.fqty = sc.fqty * mitemunit.funits;
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
}
}
// Delete
foreach (salesdetail sc in salesdetailsdelete)
{
// Retrieve original and reverse
morgtrx = _db.salesdetails.AsNoTracking().Where(t => t.fsoid == sc.fsoid && t.fsodid == sc.fsodid).FirstOrDefault();
// Only if valid qty
if (morgtrx.fqty != 0)
{
mitemunit = _db.itemunits.AsNoTracking().Where(t => t.fitem == sc.fitem).FirstOrDefault(); // Itemunits funits
mitemmaster = _db.itemmasters.AsNoTracking().Where(t => t.fimid == mitemunit.fimid).FirstOrDefault();
// Only 'I'nventory
if (mitemmaster.ftype == "I")
{
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = morgtrx.fsoid;
mtrx.fdoctype = "S"; // Only 'S'ales trx
mtrx.fitem = morgtrx.fitem;
mtrx.flocation = (int)row.flocation;
mtrx.fqty = (morgtrx.fqty * mitemunit.funits) * -1; // Reverse Qty
mtrx.famount = morgtrx.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
}
}
}
// Update
foreach (salesdetail sc in salesdetailsupdate)
{
// Get original rec 'AsNoTracking' but no plan to update it
morgtrx = _db.salesdetails.AsNoTracking().Where(t => t.fsoid == sc.fsoid && t.fsodid == sc.fsodid).FirstOrDefault();
mitemunit = _db.itemunits.AsNoTracking().Where(t => t.fitem == sc.fitem).FirstOrDefault();
mitemmaster = _db.itemmasters.AsNoTracking().Where(t => t.fimid == mitemunit.fimid).FirstOrDefault();
// Only 'I'nventory
if (mitemmaster.ftype == "I")
{
// Open Orders and Qty was modified
if (mfdoctype == "S" && sc.fqty != morgtrx.fqty)
{
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = morgtrx.fsoid;
mtrx.fdoctype = mfdoctype;
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.flocation;
mtrx.fqty = (sc.fqty - morgtrx.fqty) * mitemunit.funits; // Difference
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
}
else if (mfdoctype == "I")
{
if (morgtrx.fqty != 0) // Reverse only if valid Qty
{
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = morgtrx.fsoid;
mtrx.fdoctype = "S";
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.flocation;
mtrx.fqty = (morgtrx.fqty * mitemunit.funits) * -1; // Reverse
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
}
if (sc.fqty != 0) // New Valid Qty
{
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = morgtrx.fsoid;
mtrx.fdoctype = mfdoctype;
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.flocation;
mtrx.fqty = sc.fqty * mitemunit.funits;
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
}
}
}
}
// Records not modified must also create transactions if 'I'nvocing
if (mfdoctype == "I")
{
var mNotChanged = _db.salesdetails.AsNoTracking().Where(t => t.fsoid == row.fsoid).ToList(); // Get Details not modified ToList() to prevent creating nesting DataReader
foreach (salesdetail sc in mNotChanged)
{
if (salesdetailsupdate.Where(t => t.fsodid == sc.fsodid).Count() == 0) // Not found under modified
{
if (sc.fqty != 0) // Valid Qty
{
mitemunit = _db.itemunits.AsNoTracking().Where(t => t.fitem == sc.fitem).FirstOrDefault();
mitemmaster = _db.itemmasters.AsNoTracking().Where(t => t.fimid == mitemunit.fimid).FirstOrDefault();
// Only 'I'nventory
if (mitemmaster.ftype == "I")
{
// Invoice Trx
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = sc.fsoid;
mtrx.fdoctype = mfdoctype;
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.flocation;
mtrx.fqty = sc.fqty * mitemunit.funits;
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
// Reversal Trx with fqty * -1
mtrx = new inventorytrx();
mtrx.fuser = row.fusername;
mtrx.fdate = row.fdate;
mtrx.fdocid = sc.fsoid;
mtrx.fdoctype = "S";
mtrx.fitem = sc.fitem;
mtrx.flocation = (int)row.flocation;
mtrx.fqty = (sc.fqty * mitemunit.funits) * -1;
mtrx.famount = sc.fprice / mitemunit.funits;
inventorytrxinsert.Add(mtrx);
}
}
}
}
}
// Delete
foreach (salesdetail sc in salesdetailsdelete) _db.salesdetails.Remove(_db.salesdetails.Find(sc.fsoid, sc.fsodid));
foreach (salespayment sc in salespaymentsdelete) _db.salespayments.Remove(_db.salespayments.Find(sc.fsoid, sc.fsopid));
// salesdetail
ofDBSave(salesdetailsinsert, _db.salesdetails, "I"); // Insert
ofDBSave(salesdetailsupdate, _db.salesdetails, "U"); // Update
// salespayment
ofDBSave(salespaymentsinsert, _db.salespayments, "I"); // Insert
ofDBSave(salespaymentsupdate, _db.salespayments, "U"); // Update
}
// Save inventorytrx
ofDBSave(inventorytrxinsert, _db.inventorytrxes, "I"); // Always Insert
_db.SaveChanges();
}
catch (InvalidCastException e)
{
mCommit = false;
mErrMsg = e.Message;
}
if (mCommit) transaction.Complete();
}
return new
{
success = mCommit,
fdocnumber = mfdocnumber,
errmsg = mErrMsg
};
}