public bool SelectPurchaseOrderStatus(int POId)
{
bool flag = false;
try
{
tblPurchaseInvoice obj = (from tbl in objData.tblPurchaseInvoices
where tbl.IsDelete == false &&
tbl.PONo == POId
select tbl).FirstOrDefault();
if (obj != null)
{
flag = false;
}
else
{
flag = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(flag);
}
public void AddTractorPurchaseDetails(TractorPurchaseDetail t)
{
// First Add the Invoice in it to Invoice Table.
tblPurchaseInvoice invoice = new tblPurchaseInvoice();
getTableInvoiceEquivalentFromSparePurchaseObj(ref invoice, t);
dc.tblPurchaseInvoices.InsertOnSubmit(invoice);
dc.SubmitChanges();
// Add the Tractors Details inside to TractorPurchase Table.
List <tblTractorPurchaseDetail> traPurchaseDetail = null;
getTableTractorPurchaseDetailEquivalentFromTractorPurchaseObj(ref traPurchaseDetail, t);
dc.tblTractorPurchaseDetails.InsertAllOnSubmit(traPurchaseDetail);
dc.SubmitChanges();
// Add the Tractor to Stock Table.
}
private void FrmPurchase_Load(object sender, EventArgs e)
{
LoadCategory();
txtInvoiceDate.Text = DateTime.Today.ToShortDateString();
tblPurchaseInvoice pur = db.tblPurchaseInvoices.OrderByDescending(i => i.PurchaseInvoiceId).Take(1).FirstOrDefault();
if (pur != null)
{
string[] invno = pur.InvoiceNo.Split('-');
int maxno = Convert.ToInt32(invno[1]) + 1;
txtInvoiceNo.Text = "PUR-" + maxno;
}
else
{
txtInvoiceNo.Text = "PUR-1";
}
}
public void AddTractorPurchaseDetails(TractorPurchaseDetail t)
{
// First Add the Invoice in it to Invoice Table.
tblPurchaseInvoice invoice = new tblPurchaseInvoice();
getTableInvoiceEquivalentFromSparePurchaseObj(ref invoice, t);
dc.tblPurchaseInvoices.InsertOnSubmit(invoice);
dc.SubmitChanges();
// Add the Tractors Details inside to TractorPurchase Table.
List<tblTractorPurchaseDetail> traPurchaseDetail = null;
getTableTractorPurchaseDetailEquivalentFromTractorPurchaseObj(ref traPurchaseDetail, t);
dc.tblTractorPurchaseDetails.InsertAllOnSubmit(traPurchaseDetail);
dc.SubmitChanges();
// Add the Tractor to Stock Table.
}
public int Update(EntityPurchaseInvoice invoice, List<EntityPurchaseInvoiceDetails> lst)
{
int i = 0;
try
{
tblPurchaseInvoice objExistInvoice = (from tbl in objData.tblPurchaseInvoices
where tbl.IsDelete == false
&& tbl.PINo == invoice.PINo
select tbl).FirstOrDefault();
if (objExistInvoice != null)
{
bool flagISPOComplete = false;
if (invoice.PONo > 0)
{
if (objExistInvoice.PONo > 0)
{
List<tblPurchaseInvoiceDetail> lstExistedItems = (from tbl in objData.tblPurchaseInvoiceDetails
where tbl.PINo == invoice.PINo
&& tbl.IsDelete == false
select tbl).ToList();
if (lstExistedItems.Count > lst.Count)
{
foreach (tblPurchaseInvoiceDetail item in lstExistedItems)
{
int cnt = (from tbl in lst
where tbl.ProductCode == item.ProductCode
&& tbl.PurchaseInvoiceNo == item.PINo
select tbl).Count();
if (cnt == 0)
{
item.IsDelete = true;
}
tblPurchaseOrderDetail objOrder = (from tbl in objData.tblPurchaseOrderDetails
join tblPurchaseInv in objData.tblPurchaseInvoices
on tbl.PO_Id equals tblPurchaseInv.PONo
where tblPurchaseInv.PINo == item.PINo
&& tbl.IsDelete == false
&& tbl.Product_Id == item.ProductCode
select tbl).FirstOrDefault();
if (objOrder != null)
{
objOrder.InvoiceQuantity = 0;
}
}
objData.SubmitChanges();
}
#region Update Invoice Details
foreach (EntityPurchaseInvoiceDetails item in lst)
{
tblPurchaseInvoiceDetail invoiceItem = (from tbl in objData.tblPurchaseInvoiceDetails
where tbl.PINo == invoice.PINo
&& tbl.IsDelete == false
&& tbl.ProductCode == item.ProductCode
select tbl).FirstOrDefault();
if (invoiceItem != null)
{
invoiceItem.Amount = item.Amount;
invoiceItem.BatchNo = item.BatchNo;
invoiceItem.ExpiryDate = item.ExpiryDate;
invoiceItem.InvoicePrice = item.InvoicePrice;
invoiceItem.InvoiceQty = item.InvoiceQty;
invoiceItem.PINo = item.PurchaseInvoiceNo;
}
else
{
invoiceItem = new tblPurchaseInvoiceDetail()
{
PINo = invoice.PINo,
ProductCode = item.ProductCode,
InvoiceQty = item.InvoiceQty,
InvoicePrice = item.InvoicePrice,
Amount = item.Amount,
BatchNo = item.BatchNo,
ExpiryDate = item.ExpiryDate,
IsDelete = false
};
objData.tblPurchaseInvoiceDetails.InsertOnSubmit(invoiceItem);
}
tblPurchaseOrderDetail orderItem = (from tbl in objData.tblPurchaseOrderDetails
where tbl.IsDelete == false
&& tbl.PO_Id == invoice.PONo
&& tbl.Product_Id == item.ProductCode
select tbl).FirstOrDefault();
if (orderItem != null)
{
if (orderItem.Quantity == item.InvoiceQty)
{
flagISPOComplete = true;
}
else
{
flagISPOComplete = false;
}
orderItem.InvoiceQuantity = item.InvoiceQty;
}
tblStockDetail stockItem = (from tbl in objData.tblStockDetails
where tbl.IsDelete == false
&& tbl.TransactionType.Equals("PInvoice")
&& Convert.ToInt32(tbl.DocumentNo) == invoice.PINo
&& tbl.ProductId == Convert.ToInt32(item.ProductCode)
select tbl).FirstOrDefault();
if (stockItem != null)
{
stockItem.ProductId = Convert.ToInt32(item.ProductCode);
stockItem.InwardQty = item.InvoiceQty;
stockItem.InwardPrice = item.InvoicePrice;
stockItem.BatchNo = item.BatchNo;
stockItem.ExpiryDate = item.ExpiryDate;
stockItem.InwardAmount = item.Amount;
}
else
{
stockItem = new tblStockDetail()
{
BatchNo = item.BatchNo,
ExpiryDate = item.ExpiryDate,
InwardPrice = item.InvoicePrice,
InwardQty = item.InvoiceQty,
DocumentNo = invoice.PINo,
IsDelete = false,
ProductId = Convert.ToInt32(item.ProductCode),
TransactionType = "PInvoice",
InwardAmount = item.Amount
};
}
}
#endregion
#region Update Status of Order and Transaction
tblCustomerTransaction transact = (from tbl in objData.tblCustomerTransactions
where tbl.SupplierId == invoice.SupplierId
&& tbl.IsDelete == false
&& tbl.TransactionDocNo == invoice.PINo
&& tbl.TransactionType.Equals("PInvoice")
select tbl).FirstOrDefault();
if (transact != null)
{
transact.BillAmount = invoice.Amount;
}
tblPurchaseOrder orderMain = (from tbl in objData.tblPurchaseOrders
where tbl.IsDelete == false
&& tbl.PO_Id == invoice.PONo
select tbl).FirstOrDefault();
if (orderMain != null)
{
if (GetPOProductForEdit(Convert.ToInt32(invoice.PONo)).Count == lst.Count)
{
flagISPOComplete = true;
}
else
{
flagISPOComplete = false;
}
orderMain.IsPOComplete = flagISPOComplete;
}
objExistInvoice.Amount = invoice.Amount;
objExistInvoice.Discount = invoice.Discount;
objExistInvoice.PIDate = invoice.PIDate;
objExistInvoice.Tax1 = invoice.Tax1;
objExistInvoice.Tax2 = invoice.Tax2;
objData.SubmitChanges();
#endregion
}
else
{
///Specifies that Current Invoice with PO
foreach (EntityPurchaseInvoiceDetails item in lst)
{
}
}
}
else if (objExistInvoice.PONo > 0)
{
List<tblPurchaseInvoiceDetail> lstExistedItems = (from tbl in objData.tblPurchaseInvoiceDetails
where tbl.PINo == invoice.PINo
&& tbl.IsDelete == false
select tbl).ToList();
if (lstExistedItems.Count > lst.Count)
{
foreach (tblPurchaseInvoiceDetail item in lstExistedItems)
{
int cnt = (from tbl in lst
where tbl.ProductCode == item.ProductCode
&& tbl.PurchaseInvoiceNo == item.PINo
select tbl).Count();
if (cnt == 0)
{
item.IsDelete = true;
}
tblPurchaseOrderDetail objOrder = (from tbl in objData.tblPurchaseOrderDetails
join tblPurchaseInv in objData.tblPurchaseInvoices
on tbl.PO_Id equals tblPurchaseInv.PONo
where tblPurchaseInv.PINo == item.PINo
&& tbl.IsDelete == false
&& tbl.Product_Id == item.ProductCode
select tbl).FirstOrDefault();
if (objOrder != null)
{
objOrder.InvoiceQuantity = 0;
}
}
objData.SubmitChanges();
}
if (invoice.PONo > 0)
{
#region Update Invoice Details
foreach (EntityPurchaseInvoiceDetails item in lst)
{
tblPurchaseInvoiceDetail invoiceItem = (from tbl in objData.tblPurchaseInvoiceDetails
where tbl.PINo == invoice.PINo
&& tbl.IsDelete == false
&& tbl.ProductCode == item.ProductCode
select tbl).FirstOrDefault();
if (invoiceItem != null)
{
invoiceItem.Amount = item.Amount;
invoiceItem.BatchNo = item.BatchNo;
invoiceItem.ExpiryDate = item.ExpiryDate;
invoiceItem.InvoicePrice = item.InvoicePrice;
invoiceItem.InvoiceQty = item.InvoiceQty;
invoiceItem.PINo = item.PurchaseInvoiceNo;
}
else
{
invoiceItem = new tblPurchaseInvoiceDetail()
{
PINo = invoice.PINo,
ProductCode = item.ProductCode,
InvoiceQty = item.InvoiceQty,
InvoicePrice = item.InvoicePrice,
Amount = item.Amount,
BatchNo = item.BatchNo,
ExpiryDate = item.ExpiryDate,
IsDelete = false
};
objData.tblPurchaseInvoiceDetails.InsertOnSubmit(invoiceItem);
}
tblPurchaseOrderDetail orderItem = (from tbl in objData.tblPurchaseOrderDetails
where tbl.IsDelete == false
&& tbl.PO_Id == invoice.PONo
&& tbl.Product_Id == item.ProductCode
select tbl).FirstOrDefault();
if (orderItem != null)
{
if (orderItem.Quantity == item.InvoiceQty)
{
flagISPOComplete = true;
}
else
{
flagISPOComplete = false;
}
orderItem.InvoiceQuantity = item.InvoiceQty;
}
tblStockDetail stockItem = (from tbl in objData.tblStockDetails
where tbl.IsDelete == false
&& tbl.TransactionType.Equals("PInvoice")
&& Convert.ToInt32(tbl.DocumentNo) == invoice.PINo
&& tbl.ProductId == Convert.ToInt32(item.ProductCode)
select tbl).FirstOrDefault();
if (stockItem != null)
{
stockItem.ProductId = Convert.ToInt32(item.ProductCode);
stockItem.InwardQty = item.InvoiceQty;
stockItem.InwardPrice = item.InvoicePrice;
stockItem.BatchNo = item.BatchNo;
stockItem.ExpiryDate = item.ExpiryDate;
stockItem.InwardAmount = item.Amount;
}
else
{
stockItem = new tblStockDetail()
{
BatchNo = item.BatchNo,
ExpiryDate = item.ExpiryDate,
InwardPrice = item.InvoicePrice,
InwardQty = item.InvoiceQty,
DocumentNo = invoice.PINo,
IsDelete = false,
ProductId = Convert.ToInt32(item.ProductCode),
TransactionType = "PInvoice",
InwardAmount = item.Amount
};
}
}
#endregion
#region Update Status of PO and Transaction Update
tblCustomerTransaction transact = (from tbl in objData.tblCustomerTransactions
where tbl.SupplierId == invoice.SupplierId
&& tbl.IsDelete == false
&& tbl.TransactionDocNo == invoice.PINo
&& tbl.TransactionType.Equals("PInvoice")
select tbl).FirstOrDefault();
if (transact != null)
{
transact.BillAmount = invoice.Amount;
}
tblPurchaseOrder orderMain = (from tbl in objData.tblPurchaseOrders
where tbl.IsDelete == false
&& tbl.PO_Id == invoice.PONo
select tbl).FirstOrDefault();
if (orderMain != null)
{
if (GetPOProduct(Convert.ToInt32(invoice.PONo)).Count == lst.Count)
{
flagISPOComplete = true;
}
else
{
flagISPOComplete = false;
}
orderMain.IsPOComplete = flagISPOComplete;
}
objExistInvoice.Amount = invoice.Amount;
objExistInvoice.Discount = invoice.Discount;
objExistInvoice.PIDate = invoice.PIDate;
objExistInvoice.Tax1 = invoice.Tax1;
objExistInvoice.Tax2 = invoice.Tax2;
objData.SubmitChanges();
#endregion
}
else
{
///Specifies that Existing Invoice With PO and Current Not With PO
#region Update Invoice Details
foreach (EntityPurchaseInvoiceDetails item in lst)
{
tblPurchaseInvoiceDetail invoiceItem = (from tbl in objData.tblPurchaseInvoiceDetails
where tbl.PINo == invoice.PINo
&& tbl.IsDelete == false
&& tbl.ProductCode == item.ProductCode
select tbl).FirstOrDefault();
if (invoiceItem != null)
{
invoiceItem.Amount = item.Amount;
invoiceItem.BatchNo = item.BatchNo;
invoiceItem.ExpiryDate = item.ExpiryDate;
invoiceItem.InvoicePrice = item.InvoicePrice;
invoiceItem.InvoiceQty = item.InvoiceQty;
invoiceItem.PINo = item.PurchaseInvoiceNo;
}
else
{
invoiceItem = new tblPurchaseInvoiceDetail()
{
PINo = invoice.PINo,
ProductCode = item.ProductCode,
InvoiceQty = item.InvoiceQty,
InvoicePrice = item.InvoicePrice,
Amount = item.Amount,
BatchNo = item.BatchNo,
ExpiryDate = item.ExpiryDate,
IsDelete = false
};
objData.tblPurchaseInvoiceDetails.InsertOnSubmit(invoiceItem);
}
tblPurchaseOrderDetail orderItem = (from tbl in objData.tblPurchaseOrderDetails
where tbl.IsDelete == false
&& tbl.PO_Id == objExistInvoice.PONo
&& tbl.Product_Id == item.ProductCode
select tbl).FirstOrDefault();
if (orderItem != null)
{
orderItem.InvoiceQuantity = 0;
}
tblStockDetail stockItem = (from tbl in objData.tblStockDetails
where tbl.IsDelete == false
&& tbl.TransactionType.Equals("PInvoice")
&& Convert.ToInt32(tbl.DocumentNo) == invoice.PINo
&& tbl.ProductId == Convert.ToInt32(item.ProductCode)
select tbl).FirstOrDefault();
if (stockItem != null)
{
stockItem.ProductId = Convert.ToInt32(item.ProductCode);
stockItem.InwardQty = item.InvoiceQty;
stockItem.InwardPrice = item.InvoicePrice;
stockItem.BatchNo = item.BatchNo;
stockItem.ExpiryDate = item.ExpiryDate;
stockItem.InwardAmount = item.Amount;
}
else
{
stockItem = new tblStockDetail()
{
BatchNo = item.BatchNo,
ExpiryDate = item.ExpiryDate,
InwardPrice = item.InvoicePrice,
InwardQty = item.InvoiceQty,
DocumentNo = invoice.PINo,
IsDelete = false,
ProductId = Convert.ToInt32(item.ProductCode),
TransactionType = "PInvoice",
InwardAmount = item.Amount
};
}
}
#endregion
#region Update Status of PO and Transaction Update
tblCustomerTransaction transact = (from tbl in objData.tblCustomerTransactions
where tbl.SupplierId == invoice.SupplierId
&& tbl.IsDelete == false
&& tbl.TransactionDocNo == invoice.PINo
&& tbl.TransactionType.Equals("PInvoice")
select tbl).FirstOrDefault();
if (transact != null)
{
transact.BillAmount = invoice.Amount;
}
tblPurchaseOrder orderMain = (from tbl in objData.tblPurchaseOrders
where tbl.IsDelete == false
&& tbl.PO_Id == objExistInvoice.PONo
select tbl).FirstOrDefault();
if (orderMain != null)
{
orderMain.IsPOComplete = false;
}
objExistInvoice.Amount = invoice.Amount;
objExistInvoice.Discount = invoice.Discount;
objExistInvoice.PIDate = invoice.PIDate;
objExistInvoice.Tax1 = invoice.Tax1;
objExistInvoice.Tax2 = invoice.Tax2;
objExistInvoice.PONo = invoice.PONo;
objData.SubmitChanges();
#endregion
}
}
}
i++;
}
catch (Exception ex)
{
i = 0;
throw ex;
}
return i;
}
private void BtnSave_Click(object sender, EventArgs e)
{
tblPurchaseInvoice tb = new tblPurchaseInvoice();
tb.InvoiceNo = txtInvoiceNo.Text;
tb.InvoiceDate = Convert.ToDateTime(txtInvoiceDate.Text);
tb.GrandTotal = Convert.ToDecimal(txtGrandTotal.Text);
tb.VendorId = vendorid;
db.tblPurchaseInvoices.Add(tb);
db.SaveChanges();
var tbv = db.tblVendorPayments.OrderByDescending(a => a.VendorPaymentId).Select(x => new { VendorId = x.VendorId, VendorName = x.tblVendor.Name, PaymentDate = x.PaymentDate, TotalAmount = x.TotalAmount, PaymentAmount = x.PaymentAmount, RemainingAmount = x.RemainingAmount }).Where(b => b.VendorId == vendorid).Take(1).FirstOrDefault();
tblVendorPayment tbvenpay = new tblVendorPayment();
if (tbv != null)
{
tbvenpay.VendorId = vendorid;
tbvenpay.PaymentDate = Convert.ToDateTime(txtInvoiceDate.Text);
tbvenpay.TotalAmount = Convert.ToDecimal(txtGrandTotal.Text) + Convert.ToDecimal(tbv.RemainingAmount);
tbvenpay.PaymentAmount = Convert.ToDecimal(txtPaidAmount.Text);
tbvenpay.RemainingAmount = (Convert.ToDecimal(txtGrandTotal.Text) + Convert.ToDecimal(tbv.RemainingAmount)) - Convert.ToDecimal(txtPaidAmount.Text);
}
else
{
tbvenpay.VendorId = vendorid;
tbvenpay.PaymentDate = Convert.ToDateTime(txtInvoiceDate.Text);
tbvenpay.TotalAmount = Convert.ToDecimal(txtGrandTotal.Text);
tbvenpay.PaymentAmount = Convert.ToDecimal(txtPaidAmount.Text);
tbvenpay.RemainingAmount = Convert.ToDecimal(txtRemainingAmount.Text);
tbvenpay.PaymentMode = rbdCash.Checked ? "Cash" : "Cheque";
}
db.tblVendorPayments.Add(tbvenpay);
if (db.SaveChanges() > 0)
{
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
tblPurchase pur = new tblPurchase();
pur.PurchaseInvoiceId = tb.PurchaseInvoiceId;
pur.ProductId = Convert.ToInt32(dr.Cells["colProductId"].Value);
pur.Quantity = Convert.ToInt32(dr.Cells["colQuantity"].Value);
pur.UnitPrice = Convert.ToDecimal(dr.Cells["colUnitPrice"].Value);
pur.Total = Convert.ToDecimal(dr.Cells["colTotal"].Value);
pur.PurchaseDate = Convert.ToDateTime(txtInvoiceDate.Text);
pur.Tax = Convert.ToDecimal(dr.Cells["colTax"].Value);
db.tblPurchases.Add(pur);
if (db.SaveChanges() > 0)
{
tblStock tbst = db.tblStocks.Where(p => p.ProductId == pur.ProductId).FirstOrDefault();
if (tbst != null)
{
int puranoquantity = Convert.ToInt32(tbst.Quantity);
tbst.Quantity = puranoquantity + Convert.ToInt32(dr.Cells["colQuantity"].Value);
db.SaveChanges();
}
else
{
tblStock tbstock = new tblStock();
tbstock.ProductId = Convert.ToInt32(dr.Cells["colProductId"].Value);
tbstock.Quantity = Convert.ToInt32(dr.Cells["colQuantity"].Value);
db.tblStocks.Add(tbstock);
db.SaveChanges();
}
}
}
}
MessageBox.Show("Purchase Done");
}