public JsonResult AddInvoiceData(InvoiceVM Inv) { var w = (from yt in db.UserLogins where yt.UserID.ToString() == User.Identity.Name select new { yt.WorkStationID }).FirstOrDefault(); var wn = db.Warehouses.Where(t => t.WarehouseID == w.WorkStationID).FirstOrDefault(); bool status = false; string mes = ""; string s1 = w.WorkStationID.ToString(); string s2 = string.Concat(s1 + "6000000"); string s3 = string.Concat(s1 + "5000000"); int MaxChallanNo = Convert.ToInt32(s2); int MaxCashID = Convert.ToInt32(s3); var maxchno = (from n in db.Orders where n.WarehouseID == w.WorkStationID select n.ChallanNumber).DefaultIfEmpty(MaxChallanNo).Max(); var maxCasettleId = (from n in db.CashSettlements where n.WarehouseID == w.WorkStationID select n.CashSettlementID).DefaultIfEmpty(MaxCashID).Max(); var maxprodtr = (from j in db.ProductTransactions where j.WarehouseID == w.WorkStationID select j.TransactionNo).Max() + 1; chNumber = Convert.ToInt32(maxchno + 1); csID = Convert.ToInt32(maxCasettleId + 1); int v = chNumber; if (ModelState.IsValid) { try { using (PEPSIEntities dc = new PEPSIEntities()) { // var V = db.Orders.Max(p => p.ChallanNumber); CommonChk cm = new CommonChk(); { if (cm.chkDuplicateChallan(Inv.OrderID) == true) { //status = false; var vr = db.Orders.SingleOrDefault(x => x.OrderID == Inv.OrderID); CashSettlement cs = new CashSettlement(); { // var vr = db.Orders.SingleOrDefault(x => x.OrderID == Inv.OrderID); cs.CashSettlementID = csID; cs.SalesPersonID = vr.SalesPersonID; cs.DeliveryChallanDate = DateTime.Today; //vr.DeliveryChallanDate; cs.CustomerID = vr.CustomerID; cs.DeliveryChallanNumbers = chNumber; // Convert.ToInt32(vr.ChallanNumber)+1; cs.WarehouseID = vr.WarehouseID; cs.VehicleID = vr.VehicleID; cs.CashSettlementDate = DateTime.Today; cs.RebateWillBeGivenWithOrder = vr.RebateWillBeGivenWithOrder; cs.SpecialNoteFromOrder = vr.SpecialNote; cs.SpecialInstruction = Inv.SpecialNote; cs.OrderID = vr.OrderID; cs.ProductRateID = vr.ProductRateID; cs.DayendFinished = "N"; cs.DayendDay = DateTime.Now; cs.OrderTypeID = vr.OrderTypeID; cs.CustomerExecutiveID = vr.CustomerExecutiveID; cs.DriverID = vr.DriverID; cs.Promotional = vr.Promotional; cs.CheckedByShipping = "Y"; cs.AgencyCommission = Inv.AgencyCommission; // vr.AgencyCommission; cs.TotalSpecialCharges = vr.SpecialCharge1Amount; cs.SecurityAmount = Inv.SecurityAmount; //vr cs.DrinksAmount = (Inv.SalesAmount - Inv.AgencyCommission); //(vr.SalesAmount - vr.AgencyCommission); cs.FinishGoodsUnloadCharge = Inv.FinishGoodsUnloadCharge; //vr cs.CreateBy = User.Identity.Name; cs.CraeteDate = DateTime.Now; // dc.CashSettlements.Add(cs); } dc.CashSettlements.Add(cs); // Product Transaction ProductTransaction pt = new ProductTransaction(); { pt.WarehouseID = Inv.WarehouseID; pt.TransactionNo = maxprodtr; pt.TransactionDate = DateTime.Today; pt.TransactionTypeID = 7; pt.FromWarehouse = Inv.WarehouseID; pt.ReferenceNo = csID.ToString(); pt.Remarks = Inv.SpecialNote; pt.Status = "I"; pt.CreatedBy = User.Identity.Name; pt.CreatedDate = DateTime.Now; } dc.ProductTransactions.Add(pt); // Helper InvoiceHelper pm = new InvoiceHelper(); { pm.OrderID = Inv.OrderID; pm.HelperID1 = Inv.Helper1; pm.HelperID2 = Inv.Helper2; pm.HelperID3 = Inv.Helper2; dc.InvoiceHelpers.Add(pm); } // Customer Ledger CustomerLedgerDetail cld = new CustomerLedgerDetail(); { cld.WarehouseID = vr.WarehouseID; cld.CustomerID = vr.CustomerID; cld.TransactionNo = "INV" + csID.ToString(); cld.CrAmount = 0; cld.TransactionTypeID = 15; cld.TransactionDate = DateTime.Today; cld.RefNumber1 = csID.ToString(); cld.DrAmount = (Inv.SalesAmount + Inv.SecurityAmount) - (Inv.AgencyCommission + (decimal)Inv.FinishGoodsUnloadCharge);// (Inv.SalesAmount - Inv.AgencyCommission); //(vr.SalesAmount - vr.AgencyCommission); cld.DebitCredit = "D"; cld.Narration = "Invoice Value#" + cld.DrAmount + " for Customer" + cld.CustomerID; cld.CreateBy = User.Identity.Name; cld.CreateDate = System.DateTime.Now; } dc.CustomerLedgerDetails.Add(cld); // update Customer Ledgermaster // Farid Correction 03dec2018 //decimal amountofdr = (Inv.SalesAmount - Inv.AgencyCommission); //decimal bokamt = amountofdr + Inv.SecurityAmount; //var clmbf = db.spCustomerBalanceUpdate(vr.CustomerID, "F", bokamt); //var clm = db.spCustomerBalanceUpdate(vr.CustomerID, "I", amountofdr); //var security = db.spCustomerBalanceUpdate(vr.CustomerID, "S", Inv.SecurityAmount); //decimal vInvAmt = (Inv.SalesAmount - Inv.AgencyCommission)- (decimal)Inv.FinishGoodsUnloadCharge; decimal vInvAmt = Inv.SalesAmount - (Inv.AgencyCommission); //(decimal)Inv.FinishGoodsUnloadCharge decimal vBookAmt = Inv.SalesAmount + Inv.SecurityAmount - Inv.AgencyCommission; decimal vCurBal = (Inv.SalesAmount + Inv.SecurityAmount) - (Inv.AgencyCommission + (decimal)Inv.FinishGoodsUnloadCharge); decimal vChargeAmount = (decimal)Inv.FinishGoodsUnloadCharge; decimal vSecurityAmt = Inv.SecurityAmount; decimal vRebateAmt = 0; if (Inv.RebateAmount > 0) { decimal vRebate = (Inv.RebateAmount); vRebateAmt = vRebate; } else { decimal vRebate = Inv.RebateAmount; vRebateAmt = vRebate; } decimal vFare = Inv.FareAmnt; db.spUpdateDBsInvoiceValue( vr.CustomerID, "I", vCurBal, vCurBal, vInvAmt, vBookAmt, vSecurityAmt, vChargeAmount, vFare, vRebateAmt, chNumber ); // Farid Correction // var clm = db.spCustomerBalanceUpdate(vr.CustomerID, "I", bokamt);// with sequirity amount // Add Fare Amount if (Inv.VehicleID == 1) { //need to adjust without var cus = cm.GetCustomerName(vr.CustomerID, "C"); var cusAdd = db.Customers.Where(x => x.CustomerID == vr.CustomerID).FirstOrDefault(); var c = db.spInsertHireTruckFare(Inv.WarehouseID, chNumber, Inv.TAID, DateTime.Today, cus, cusAdd.CustomerAddress1, Inv.VechileNo, Inv.FareAmnt, 0, Inv.TotalCases, "No", User.Identity.Name, DateTime.Now, "Hired Truck"); } var ODetails = db.OrderDetails.Where(x => x.WarehouseID == Inv.WarehouseID && x.OrderID == Inv.OrderID); foreach (var z in ODetails.ToList()) { CashSettlementDetail csd = new CashSettlementDetail() { WarehouseID = Inv.WarehouseID, CashSettlementID = csID, ProductID = z.ProductID, SchemeID = (int)z.SchemeID, UnitPrice = (decimal)z.UnitPrice, AlternateUnitPrice = (decimal)z.AlternateUnitPrice, Quantity = (int)z.Quantity, AlternateQuantity = (int)z.AlternateQuantity, PlasticBoxQuantity = (int)z.PlasticBoxQuantity, RebateQuantity = (int)z.RebateQuantity, AlternateRebateQuantity = (int)z.AlternateRebateQuantity, AgencyCommission = (decimal)z.AgencyCommission, AlternateAgencyCommission = (decimal)z.AlternateAgencyCommission, SecurityDeposit = (decimal)z.SecurityDeposit, AlternateSecurityDeposit = (decimal)z.AlternateSecurityDeposit, PlasticBoxSecurity = (decimal)z.PlasticBoxSecurity, MRPRate = z.MRPRate }; dc.CashSettlementDetails.Add(csd); var totpc = (from x in db.Products where x.ProductID == z.ProductID select x).FirstOrDefault(); int TotQty = (int)(z.Quantity + ((z.AlternateQuantity + z.AlternateRebateQuantity) * totpc.ConversionFactor) + z.RebateQuantity); StockBatchDetail sbd = new StockBatchDetail() { WarehouseID = Inv.WarehouseID, TransactionNo = csID, TransactionDate = System.DateTime.Today, ProductID = z.ProductID, Quantity = TotQty,//(int)z.AlternateQuantity for rebate and case to pcs, PlasticBoxQuantity = z.PlasticBoxQuantity, TransactionType = "Invoice Creation", BatchNo = (int)z.BatchNo, CreateBy = User.Identity.Name, CreateDate = System.DateTime.Now, }; dc.StockBatchDetails.Add(sbd); //var cc = (int)z.Quantity; //var xxx = (int)z.PlasticBoxQuantity; //var cc = CommonChk // CommonChk cm = new CommonChk(); //int qty = cm.CasetoPcs(z.ProductID, (int)z.AlternateQuantity); // off for rebate int qty = TotQty; //int qty = cm.CasetoPcs(z.ProductID, TotQty); Farid Correction ProductTransactionDetail ptd = new ProductTransactionDetail(); { ptd.TransactionNo = maxprodtr; ptd.WarehouseID = Inv.WarehouseID; ptd.ProductID = z.ProductID; ptd.Quantity = qty;// (int)z.AlternateQuantity; ptd.PlasticBoxQuantity = (int)z.PlasticBoxQuantity; //BatchNo = z.BatchNo, //ExpiryDate = z.ExpDate, //ManufactureDate = z.MfgDate, ptd.EmptyBottleQuantity = 0; ptd.BurstBottleQuantity = 0; ptd.BreakageBottleQuantity = 0; dc.ProductTransactionDetails.Add(ptd); }; // Product Stock Maintaince // int allProductsQty; //var Pr = (from x in db.Products where x.ProductID == z.ProductID select x).FirstOrDefault(); // allProductsQty = (int)(z.Quantity + ((z.AlternateQuantity + z.AlternateRebateQuantity) * Pr.ConversionFactor) + z.RebateQuantity); // SqlCommand com = new SqlCommand("spProductBalanceDecrease", con); // off for holding stock against loadsheet //var decreaseprodbal = db.spProductBalanceDecrease(z.ProductID, // vr.WarehouseID, // qty, // 0, // 0, // 0, // 0); var decreaseprodbal = db.spStockBookedandBookedFree(z.ProductID, vr.WarehouseID, qty, "D"); } // Update Order var upby = User.Identity.Name; var orderdata = db.spOrderUpdateAfterInvoice( Inv.WarehouseID, Inv.OrderID, "Y", DateTime.Today, Inv.SalesAmount, Inv.SecurityAmount, Inv.RebateAmount, Inv.AgencyCommission, chNumber, upby, csID, "Y", Inv.FinishGoodsUnloadCharge, upby, DateTime.Now, Inv.AdvanceOrder ); // con.Close(); //Transport QR Data Tauhid //salesOrderNo will be OrderDate try { var getSONo = (from x in db.Orders where x.OrderID == Inv.OrderID select x).FirstOrDefault(); SalesOrdNo = Convert.ToInt32(getSONo.OrderID); TransportQRData qd = db.TransportQRDatas.SingleOrDefault(x => x.SalesOrderNo == SalesOrdNo); qd.InvoiceDateTime = System.DateTime.Now; qd.DeliveryChallanNumber = chNumber; dc.Entry(qd).State = EntityState.Modified; } catch (Exception ex) { } dc.SaveChanges(); //Invoice Print var getSNo = (from x in db.Orders where x.ChallanNumber == chNumber select x).FirstOrDefault(); SalesOrdNo = Convert.ToInt32(getSNo.SalesOrderNo); // SalesOrdNo = (int)Inv.SalesOrderNo; //if (SalesOrdNo!=null) //{ // return RedirectToAction("Invoce", "OrderReport"); //} status = true; //int v = chNumber; Dispose(); // status = true; //return new JsonResult { Data = new { status = status, mes = mes } }; } else { status = false; v = 0; } } } } catch (Exception ex) { throw ex; } } else { status = false; } return(new JsonResult { Data = new { status = status, mes = mes, v = v, SNo = SalesOrdNo } }); //return RedirectToAction("Invoice", "ReprintController", new { @InvNo = v }); }
public ActionResult SaveData(StockVM S) { var w = (from y in db.UserLogins where y.UserID.ToString() == User.Identity.Name select new { y.WorkStationID }).FirstOrDefault(); var wn = db.Warehouses.Where(x => x.WarehouseID == w.WorkStationID).FirstOrDefault(); // var PTID = (from x in db.OrderBankSummaries select x.SalesOrderNO).DefaultIfEmpty(610000000).Max(); // PIDMax = (PTID + 1); string s1 = w.WorkStationID.ToString(); string s2 = string.Concat(s1 + "0000000"); string s3 = string.Concat(s1 + Convert.ToString(DateTime.Now.Year) + "000"); string plantfl = wn.PlantFlag; int batchno = Convert.ToInt32(s3); int Trno = Convert.ToInt32(s2); bool status = true; string mes = ""; int PIDMax; var PTID = (from x in db.ProductTransactions where x.WarehouseID == wn.WarehouseID select x.TransactionNo).DefaultIfEmpty(Trno).Max(); PIDMax = (PTID + 1); int v = PIDMax; if (ModelState.IsValid) { try { using (PEPSIEntities dc = new PEPSIEntities()) { CommonChk ck = new CommonChk(); ProductTransaction dm = new ProductTransaction(); { // Here Id Primary Key of DB table auto increase //var PTID = db.ProductTransactions.DefaultIfEmpty(100).Max(p => p.TransactionNo); dm.TransactionTypeID = S.TransactionTypeID; dm.TransactionNo = PIDMax;// dm.WarehouseID = S.WarehouseID; dm.FromWarehouse = S.WarehouseID; dm.ToWarehouse = S.ToWarehouse; dm.TransactionDate = DateTime.Today; dm.ReferenceNo = S.ReferenceNo; dm.Remarks = S.Remarks; if (S.TransactionTypeID != 1) { if (S.TransactionTypeID == 1) { dm.Status = "T"; } if (S.TransactionTypeID == 6) { dm.Status = "D"; } if (S.TransactionTypeID == 10) { dm.Status = "B"; } if (S.TransactionTypeID == 11) { dm.Status = "W"; } } else { dm.Status = "A"; } //cuser = User.Identity.Name dm.CreatedBy = User.Identity.Name; dm.CreatedDate = DateTime.Now; dm.StoreLocation = S.StoreLocation; } dc.ProductTransactions.Add(dm); // Transfer Note if (S.TransactionTypeID == 2) { ProductTransferNote ptn = new ProductTransferNote(); { var PTNoteID = (from x in db.ProductTransferNotes where x.WarehouseID == w.WorkStationID select x.SLNo).DefaultIfEmpty(1).Max(); int TrNoteId = Convert.ToInt32((PTNoteID)) + 1; ptn.WarehouseID = S.WarehouseID; ptn.TransactionNo = PIDMax; ptn.VehicleID = S.VehicleID; if (S.VehicleID == 1) { ptn.DriverID = 0; } else { ptn.DriverID = S.DriverID; } ptn.Remarks = S.Remarks; ptn.SLNo = TrNoteId; ptn.CreateBy = User.Identity.Name; ptn.CreateDate = DateTime.Now; } dc.ProductTransferNotes.Add(ptn); } // Agency Fare Amount if (S.VehicleID == 1 && S.TransactionTypeID == 2) { TransportAgencyandFareSetup tf = new TransportAgencyandFareSetup(); { tf.WarehouseID = S.WarehouseID; tf.TAID = (int)S.TAID; tf.ChallanNumber = PIDMax; tf.ChallanDate = DateTime.Today; tf.CustomerName = ck.GetCustomerName((int)S.ToWarehouse, "N"); tf.Address = ck.GetCustomerName((int)S.ToWarehouse, "N"); tf.VechileNo = S.VehicleID.ToString(); tf.FareAmnt = (decimal)S.FareAmnt; tf.Remarks = "Hired Truck"; tf.TotalCases = S.TotCase; tf.Status = "No"; tf.EnteredBy = User.Identity.Name; tf.EnteredDate = System.DateTime.Now; } dc.TransportAgencyandFareSetups.Add(tf); } if (S.TransactionTypeID == 2) { foreach (var j in S.ProdTrDtl) { //checking stock balace if (ck.stockchkcs(j.ProductID, S.WarehouseID, (int)j.Quantity) == true) { //status = false; } else { status = false; }; } ; } if (status == true) { foreach (var i in S.ProdTrDtl) { var CF = (from x in dc.Products where x.ProductID == i.ProductID select x).FirstOrDefault(); var batch = (from x in dc.StockBatches where x.ProductID == i.ProductID && x.WarehouseID == S.WarehouseID select x.BatchNo).DefaultIfEmpty(batchno).Max() + 1; ProductTransactionDetail dmd = new ProductTransactionDetail(); dmd.ProductID = i.ProductID; // Not Null dmd.WarehouseID = S.WarehouseID; dmd.TransactionNo = PIDMax; dmd.LineNoPlant = i.LineNoPlant; if (S.TransactionTypeID != 1) { dmd.BatchNo = i.BatchNo; } else { dmd.BatchNo = batch; } if (plantfl == "Y") { int plant = (int)wn.PlantNo; dmd.PlantNo = plant; } dmd.ManufactureDate = i.ManufactureDate; //DateTime d = Convert.ToDateTime(i.ManufactureDate); if (CF.Expirydays == null) { dmd.ExpiryDate = i.ManufactureDate; } else { dmd.ExpiryDate = Convert.ToDateTime(i.ManufactureDate).AddDays((int)CF.Expirydays);// + 10;// (int)CF.Expirydays; //i.ExpiryDate; } dmd.Quantity = (i.Quantity); dmd.EmptyBottleQuantity = (i.EmptyBottleQuantity); dmd.PlasticBoxQuantity = i.PlasticBoxQuantity; dmd.BurstBottleQuantity = (i.BurstBottleQuantity); dmd.BreakageBottleQuantity = (i.BreakageBottleQuantity); //TCases = TCases + i.Quantity; dc.ProductTransactionDetails.Add(dmd); // For Batch StockBatch sb = new StockBatch(); sb.ProductID = i.ProductID; sb.WarehouseID = S.WarehouseID; //sb.BatchNo = batch; if (S.TransactionTypeID != 1) { var t = db.StockBatches.Where(x => x.WarehouseID == S.WarehouseID && x.ProductID == i.ProductID && x.BatchNo == i.BatchNo).FirstOrDefault(); sb.BatchRefNo = t.BatchRefNo; sb.ManufacturDate = t.ManufacturDate; sb.ExpiryDate = t.ExpiryDate; sb.PlantLineNo = t.PlantLineNo; sb.BatchNo = (int)i.BatchNo; // Add sb.PlantNo = t.PlantNo; if (S.TransactionTypeID == 2) { sb.Status = "T"; } if (S.TransactionTypeID == 6) { sb.Status = "D"; } if (S.TransactionTypeID == 10) { sb.Status = "B"; } if (S.TransactionTypeID == 11) { sb.Status = "W"; } } else { sb.ManufacturDate = (DateTime)i.ManufactureDate; sb.ExpiryDate = Convert.ToDateTime(i.ManufactureDate).AddDays((int)CF.Expirydays); //(DateTime)i.ExpiryDate; sb.PlantLineNo = (int)i.LineNoPlant; sb.PlantNo = i.PlantNo; sb.BatchRefNo = i.BatchNo; sb.BatchNo = batch; // Add sb.WarehouseLocationCode = S.StoreLocation; sb.Status = "A"; } sb.IssueQuantity = (decimal)i.Quantity; sb.ReceivedQty = i.Quantity; dc.StockBatches.Add(sb); // Add stock batch detail for transaction history StockBatchDetail sbd = new StockBatchDetail(); sbd.WarehouseID = S.WarehouseID; sbd.ProductID = i.ProductID; sbd.TransactionNo = PIDMax; sbd.Quantity = i.Quantity; sbd.BurstBottleQuantity = i.BurstBottleQuantity; sbd.BreakageBottleQuantity = i.BreakageBottleQuantity; sbd.PlasticBoxQuantity = (int)i.PlasticBoxQuantity; sbd.TransactionDate = DateTime.Today; sbd.CreateBy = User.Identity.Name; sbd.CreateDate = DateTime.Now; if (S.TransactionTypeID == 2) { sbd.BatchNo = i.BatchNo; sbd.TransactionType = "Product Transfer"; } else if (S.TransactionTypeID == 10) { sbd.BatchNo = i.BatchNo; sbd.TransactionType = "Warehouse Burst"; } else if (S.TransactionTypeID == 11) { sbd.BatchNo = i.BatchNo; sbd.TransactionType = "Write Off"; } else if (S.TransactionTypeID == 6) { sbd.BatchNo = i.BatchNo; sbd.TransactionType = "Adjustment"; } else { sbd.TransactionType = "Product Received"; sbd.BatchNo = batch; } dc.StockBatchDetails.Add(sbd); } } //db.SaveChanges(); // dc.SaveChanges(); foreach (var i in S.ProdTrDtl) { var CF = (from x in dc.Products where x.ProductID == i.ProductID select x).FirstOrDefault(); try { if (S.TransactionTypeID == 1) { var productupdate = dc.spProductBalanceUpdate(i.ProductID, S.WarehouseID, (int)i.Quantity, // change i.BurstBottleQuantity, i.BreakageBottleQuantity, (int)i.PlasticBoxQuantity, //change i.EmptyBottleQuantity); } if (S.TransactionTypeID != 1 && status == true) { //Deduct Stock var productupdate = dc.spProductBalanceDecrease(i.ProductID, S.WarehouseID, (int)i.Quantity, //change i.BurstBottleQuantity, i.BreakageBottleQuantity, (int)i.PlasticBoxQuantity, i.EmptyBottleQuantity); } } catch (Exception ex) { throw ex; } } if (status == true) { dc.SaveChanges(); status = true; dc.Dispose(); } } // return new JsonResult { Data = new { status = status, mes = mes } }; } catch (Exception ex) { throw ex; } } else { status = false; } return(new JsonResult { Data = new { status = status, mes = mes, v = v } }); //, v = v } }; }