///// <summary>
///// This method is used to get latest quitation no
///// </summary>
///// <returns></returns>
//public int GetLastOrderNo()
//{
// int qno = 0;
// try
// {
// using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
// {
// var pq = (from pqs in entities.PurchaseOrders
// orderby pqs.ID descending
// select new
// {
// pqs.ID,
// pqs.CreatedDate
// }
// );
// if (pq != null)
// {
// qno = pq.Take(1).SingleOrDefault().ID;
// }
// else
// {
// qno = 0;
// }
// }
// return qno;
// }
// catch (Exception ex)
// {
// throw ex;
// }
//}
/// <summary>
/// This method is used to get all purchase orders
/// </summary>
/// <returns></returns>
public List <PurchaseOrderEntity> GetAllPurchaseOrders()
{
// List<PurchaseOrderModel> lstPOF = new List<PurchaseOrderForm>();
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var lstPOs = (from pq in entities.PurchaseOrders
where (pq.IsDeleted == false || pq.IsDeleted == null)
select new PurchaseOrderEntity
{
SupplierID = pq.Sup_Id,
OrderNo = pq.PO_No,
OrderDate = pq.PO_Date,
//ValidForDays = pq.PO_Valid_for,,
DeliveryDate = pq.PO_Del_Date,
TotalBeforeTax = pq.PO_Tot_bef_Tax,
TotalTax = pq.PO_GST_Amt,
TotalAfterTax = pq.PO_Tot_aft_Tax,
TermsAndConditions = pq.PO_TandC,
CreatedBy = pq.CreatedBy,
CreatedDate = pq.CreatedDate
}).ToList <PurchaseOrderEntity>();
return(lstPOs);
}
}
catch (Exception ex)
{
throw ex;
}
}
public string GetCountOfPOSuppliers()
{
string POCount = string.Empty;
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var po = (from e in entities.PurchaseOrders
join p in entities.Suppliers
on e.Sup_Id equals p.ID
where e.IsDeleted == false
select new
{
e.Sup_Id,
p.Sup_Name
}
).Distinct().ToList();
if (po != null)
{
POCount = Convert.ToString(po.Count);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(POCount);
}
/// <summary>
/// This method is used to get category content
/// </summary>
/// <returns></returns>
public string GetCategoryContent(string catType)
{
string tandCContent = string.Empty;
using (SDNPurchasingDBEntities objProdEntities = new SDNPurchasingDBEntities())
{
try
{
var tandC = (from content in objProdEntities.TermsAndConditions
where content.Cat_Code == catType
select new ContentModel
{
ContentName = content.Cat_Content,
ContentID = content.ID
});
if (tandC != null)
{
tandCContent = tandC.SingleOrDefault().ContentName;
}
return(tandCContent);
}
catch (Exception ex)
{
throw ex;
}
}
}
public int GetLastOrderNo()
{
int qno = 0;
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var pq = (from pqs in entities.PurchaseOrders
orderby pqs.ID descending
select new
{
pqs.ID,
pqs.CreatedDate
}
);
if (pq != null)
{
qno = pq.Take(1).SingleOrDefault().ID;
}
else
{
qno = 0;
}
}
return(qno);
}
catch (Exception ex)
{
throw ex;
}
}
public List <AdjustDebitNoteEntity> GetDebitNotes(string SupplierId)
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
int supid = 0;
//List<SupplierDetailEntity> suppliersource = entities.Suppliers.Where(x => x.IsDeleted != true).Select(x => new SupplierDetailEntity
//{
// ID = x.ID,
// SupplierName = x.Sup_Name,
// Createddate = x.CreatedDate,
//}).ToList();
if (SupplierId != null || SupplierId != "" || SupplierId != " ")
{
supid = Convert.ToInt32(SupplierId);
}
List <AdjustDebitNoteEntity> debitnotelist = (from p in entities.PurchaseInvoices
join d in entities.DebitNotes on p.ID equals d.PI_Id
where d.IsDeleted != true && d.Sup_Id == supid
select new AdjustDebitNoteEntity
{
Date = d.DN_Date,
Amount = p.PI_Tot_aft_Tax,
DebitNoteNo = d.DN_No
}).ToList();
return(debitnotelist);
}
}
public string GetCountOfPISuppliers()
{
string PICount = string.Empty;
byte status = Convert.ToByte(PI_Status.UnPaid);
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var po = (from e in entities.PurchaseInvoices
join p in entities.Suppliers
on e.Sup_Id equals p.ID
where e.IsDeleted == false &&
e.PI_Status == status
select new
{
e.Sup_Id,
p.Sup_Name
}
).Distinct().ToList();
if (po != null)
{
PICount = Convert.ToString(po.Count);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(PICount);
}
public string GetCountOfDNSuppliers(out List <SupplierDetailEntity> lstSuppliers)
{
string DNCount = string.Empty;
byte status = Convert.ToByte(DN_Status.UnAdjusted);
byte unrefunded = Convert.ToByte(DN_Status.Refunded);
lstSuppliers = new List <SupplierDetailEntity>();
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var po = (from e in entities.DebitNotes
join p in entities.Suppliers
on e.Sup_Id equals p.ID
where e.IsDeleted == false &&
(e.DN_Status == status || e.DN_Status != unrefunded)
select new SupplierDetailEntity
{
ID = e.Sup_Id,
SupplierName = p.Sup_Name
}
).Distinct().ToList();
if (po != null)
{
lstSuppliers = new List <SupplierDetailEntity>(po.ToList());
DNCount = Convert.ToString(po.Count);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(DNCount);
}
/// <summary>
/// This method is used to get all purchase invoices
/// </summary>
/// <returns></returns>
public List <PurchaseInvoiceEntity> GetAllPurchaseInvoices()
{
// List<PurchaseInvoiceModel> lstPIF = new List<PurchaseInvoiceForm>();
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var lstPIs = (from pq in entities.PurchaseInvoices
where (pq.IsDeleted == false || pq.IsDeleted == null)
select new PurchaseInvoiceEntity
{
SupplierID = pq.Sup_Id,
InvoiceNo = pq.PI_No,
InvoiceDate = pq.PI_Date,
PaymentDueDate = pq.PI_Pmt_Due_Date,
OurPONo = pq.Our_PO_No,
TotalBeforeTax = pq.PI_Tot_bef_Tax,
TotalTax = pq.PI_GST_Amt,
TotalAfterTax = pq.PI_Tot_aft_Tax,
TermsAndConditions = pq.PI_TandC,
CreatedBy = pq.CreatedBy,
CreatedDate = pq.CreatedDate
}).ToList <PurchaseInvoiceEntity>();
return(lstPIs);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// This method is used to get all purchase quotations
/// </summary>
/// <returns></returns>
public List <PurchaseQuotationEntity> GetAllPurchaseQuotations()
{
// List<PurchaseQuotationModel> lstPQF = new List<PurchaseQuotationForm>();
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var lstPQs = (from pq in entities.PurchaseQuotations
where (pq.IsDeleted == false || pq.IsDeleted == null)
select new PurchaseQuotationEntity
{
SupplierID = pq.Sup_Id,
QuotationNo = pq.PQ_No,
QuotationDate = pq.PQ_Date,
ValidForDays = pq.PQ_Valid_for,
TotalBeforeTax = pq.PQ_Tot_bef_Tax,
TotalTax = pq.PQ_GST_Amt,
TotalAfterTax = pq.PQ_Tot_aft_Tax,
TermsAndConditions = pq.PQ_TandC,
CreatedBy = pq.CreatedBy,
CreatedDate = pq.CreatedDate
}).ToList <PurchaseQuotationEntity>();
return(lstPQs);
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool DeleteQuotatoin(int pqID)
{
bool result = false;
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
//var detailList = entities.PurchaseQuotationDetails.Where(x => x.PQ_ID == pqID).ToList();
//if (detailList != null)
//{
// //foreach (PurchaseQuotationDetail pqdetail in detailList)
// //{
// // entities.PurchaseQuotationDetails.Remove(pqdetail);
// //}
//}
var obj = entities.PurchaseQuotations.Where(x => x.ID == pqID).FirstOrDefault();
//entities.PurchaseQuotations.Remove(obj);
if (obj != null)
{
obj.IsDeleted = true;
obj.ModifiedDate = DateTime.Now;
entities.SaveChanges();
}
}
result = true;
}
catch
{
result = false;
}
return(result);
}
public bool IsChequeNoPresent(string cashChequeNo)
{
string str = Convert.ToString(Convert.ToByte(CashBankTransactionType.RefundFromSupplier));
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var po = entities.CashAndBankTransactions.Where(e => e.Cash_Cheque_No == cashChequeNo &&
e.Type == str).ToList();
if (po.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateDebitNote(DebitNoteForm debitNote)
{
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
DebitNote obj = entities.DebitNotes.Where(e => e.DN_No == debitNote.DebitNote.DebitNo
).SingleOrDefault();
if (obj != null)
{
if (!string.IsNullOrEmpty(debitNote.DebitNote.SupplierCreditNoteAmount))
{
debitNote.DebitNote.SCNAmount = Convert.ToDecimal(debitNote.DebitNote.SupplierCreditNoteAmount);
}
obj.Sup_CN_No = debitNote.DebitNote.SupplierCreditNoteNo;
obj.Sup_CN_Date = debitNote.DebitNote.SupplierCreditNoteDate;
obj.Sup_CN_Amount = debitNote.DebitNote.SCNAmount;
obj.ModifiedDate = DateTime.Now;
entities.SaveChanges();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public int SaveAdjustDebitNote(AdjustDebitNoteForm psForm)
{
int autoId = 0;
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
foreach (var item in psForm.AdjustDebitNoteDetails)
{
CashAndBankTransaction obj = new CashAndBankTransaction();
obj.Cus_Sup_Id = psForm.AdjustDebitNote.SupplierID;
obj.Acc_Id = psForm.AdjustDebitNote.AccountId;
obj.Amount = psForm.AdjustDebitNote.Amount;
//obj.Cash_Cheque_No = psForm.AdjustDebitNote.CashChequeNo;
obj.Cash_Cheque_Date = psForm.AdjustDebitNote.Date;
obj.Remarks = psForm.AdjustDebitNote.Remarks;
obj.Type = "S";
//obj.Is_Cheque = psForm.AdjustDebitNote.IsCheque;
//saving details entity
obj.SO_CN_PO_DN_No = item.PurchaseNo;
obj.SO_CN_PO_DN_Date = item.PurchaseDate;
obj.SO_CN_PO_DN_Amt = item.PurchaseAmount;
obj.Amt_Due = item.AmountDue;
obj.Amt_Refunded = item.AmountAdjusted;
obj.Discount = item.Discount;
obj.UpdatedBy = 0;
obj.UpdatedDate = DateTime.Now.Date;
entities.CashAndBankTransactions.Add(obj);
entities.SaveChanges();
if (item.AmountDue == 0 && item.AmountAdjusted == item.PurchaseAmount)
{
PurchaseInvoice invoice = entities.PurchaseInvoices.Where(e => e.PI_No == item.PurchaseNo).SingleOrDefault();
PurchaseOrder order = entities.PurchaseOrders.Where(e => e.PO_No == item.PurchaseNo).SingleOrDefault();
if (invoice != null)
{
invoice.PI_Status = Convert.ToByte(PI_Status.Paid);
entities.SaveChanges();
}
else if (order != null)
{
}
}
//}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(autoId);
}
public int UpdateRefundFromSupplier(RefundFromSupplierForm psForm)
{
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
foreach (var item in psForm.RefundFromSupplierDetails)
{
CashAndBankTransaction obj = entities.CashAndBankTransactions.Where(e => e.Cash_Cheque_No == psForm.RefundFromSupplier.CashChequeNo &&
e.SO_CN_PO_DN_No == item.PurchaseNo).SingleOrDefault();
if (obj != null)
{
obj.Acc_Id = psForm.RefundFromSupplier.AccountId;
obj.Amount = psForm.RefundFromSupplier.Amount;
obj.Cash_Cheque_No = psForm.RefundFromSupplier.CashChequeNo;
obj.Cash_Cheque_Date = psForm.RefundFromSupplier.Date;
obj.Remarks = psForm.RefundFromSupplier.Remarks;
obj.Is_Cheque = psForm.RefundFromSupplier.IsCheque;
//saving details entity
obj.SO_CN_PO_DN_No = item.PurchaseNo;
obj.SO_CN_PO_DN_Date = item.PurchaseDate;
obj.SO_CN_PO_DN_Amt = item.PurchaseAmount;
obj.Amt_Due = obj.Amt_Due = item.AmountDue - item.AmountAdjusted;
obj.Amt_Refunded = item.AmountAdjusted;
obj.Discount = item.Discount;
obj.UpdatedBy = 0;
obj.UpdatedDate = DateTime.Now;
entities.SaveChanges();
if (item.AmountDue == 0 && item.AmountAdjusted == item.PurchaseAmount)
{
DebitNote dn = entities.DebitNotes.Where(e => e.DN_No == item.PurchaseNo).SingleOrDefault();
PurchaseOrder order = entities.PurchaseOrders.Where(e => e.PO_No == item.PurchaseNo).SingleOrDefault();
if (dn != null)
{
dn.DN_Status = Convert.ToByte(DN_Status.Refunded);
entities.SaveChanges();
}
else if (order != null)
{
order.PO_Status = Convert.ToByte(PO_Status.Refunded);
entities.SaveChanges();
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(psForm.RefundFromSupplier.ID);
}
public RefundFromSupplierForm GetRefundFromSupplierDetails(string cashChequeNo)
{
string str = Convert.ToString(Convert.ToByte(CashBankTransactionType.RefundFromSupplier));
RefundFromSupplierForm pqf = new RefundFromSupplierForm();
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var pq = (from pqs in entities.CashAndBankTransactions
where pqs.Cash_Cheque_No == cashChequeNo &&
pqs.Type == str
select new RefundFromSupplierEntity
{
ID = pqs.ID,
SupplierID = pqs.Cus_Sup_Id,
Amount = pqs.Amount,
AccountId = pqs.Acc_Id,
Remarks = pqs.Remarks,
Date = pqs.Cash_Cheque_Date,
IsCheque = pqs.Is_Cheque,
CashChequeNo = pqs.Cash_Cheque_No,
}).FirstOrDefault();
if (pq != null)
{
pq.AmountStr = Convert.ToString(pq.Amount);
pqf.RefundFromSupplier = pq;
}
var pqd = (from pqs in entities.CashAndBankTransactions
where pqs.Cash_Cheque_No == cashChequeNo &&
pqs.Type == str
select new RefundFromSupplierDetailsEntity
{
PurchaseNo = pqs.SO_CN_PO_DN_No,
PurchaseDate = pqs.SO_CN_PO_DN_Date,
PurchaseAmount = pqs.SO_CN_PO_DN_Amt,
AmountDue = pqs.Amt_Due,
Discount = pqs.Discount,
AmountAdjusted = pqs.Amt_Refunded
}).ToList <RefundFromSupplierDetailsEntity>();
if (pqd != null)
{
pqf.RefundFromSupplierDetails = pqd;
}
return(pqf);
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool DeleteQuotatoin(int pqID)
{
bool result = false;
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var detailList = entities.PurchaseOrderDetails.Where(x => x.PO_ID == pqID).ToList();
if (detailList != null)
{
foreach (PurchaseOrderDetail pqdetail in detailList)
{
int PSId = Convert.ToInt32(pqdetail.PO_No);
if (PSId != 0)
{
ProductsAndService ps = entities.ProductsAndServices.SingleOrDefault(e => e.ID == PSId);
if (ps != null)
{
ps.PandS_Qty_on_PO = ps.PandS_Qty_on_PO - pqdetail.PO_Qty;
entities.SaveChanges();
}
}
pqdetail.PO_Qty = 0;
pqdetail.PO_Price = 0;
pqdetail.PO_Discount = 0;
pqdetail.PO_Amount = 0;
pqdetail.GST_Rate = 0;
entities.SaveChanges();
}
}
var obj = entities.PurchaseOrders.Where(x => x.ID == pqID).FirstOrDefault();
//entities.PurchaseOrders.Remove(obj);
if (obj != null)
{
obj.PO_Status = Convert.ToByte(PO_Status.Cancelled);
obj.PO_Tot_bef_Tax = 0;
obj.PO_Tot_aft_Tax = 0;
obj.PO_GST_Amt = 0;
//obj.IsDeleted = true;
obj.ModifiedDate = DateTime.Now;
entities.SaveChanges();
}
}
result = true;
}
catch
{
result = false;
}
return(result);
}
public bool CanDeleteQuotation(int pqID)
{
bool allowDelete = true;
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var PO_PI = entities.PurchaseQuotations.Where(x => (x.PQ_Conv_to_PO == true || x.PQ_Conv_to_PI == true) && x.ID == pqID).FirstOrDefault();
if (PO_PI != null)
{
allowDelete = false;
}
}
return(allowDelete);
}
private void CreateDebitNote(PurchaseInvoiceForm invoiceData)
{
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
DebitNote deb = entities.DebitNotes.Where(e => e.PI_Id == invoiceData.Invoice.ID).SingleOrDefault();
if (Convert.ToDecimal(invoiceData.Invoice.TotalBeforeTax) < 0)
{
if (deb != null)
{
deb.DN_Date = DateTime.Now.Date;
// deb.DN_No = "DN-" + (GetLastDebitNoteNo() + 1);
//deb.PI_Id = obj.ID;
deb.Sup_Id = invoiceData.Invoice.SupplierID;
// deb.Sup_CN_Amount = Convert.ToDecimal(invoiceData.Invoice.TotalBeforeTax);
//deb.ModifiedDate = DateTime.Now;
entities.SaveChanges();
}
else
{
deb = new DebitNote();
deb.DN_Date = DateTime.Now.Date;
deb.DN_No = "DN-" + (GetLastDebitNoteNo() + 1);
deb.PI_Id = invoiceData.Invoice.ID;
deb.Sup_Id = invoiceData.Invoice.SupplierID;
deb.DN_Status = Convert.ToByte(DN_Status.UnAdjusted);
// deb.Sup_CN_Amount = Convert.ToDecimal(invoiceData.Invoice.TotalBeforeTax);
deb.CreatedDate = DateTime.Now;
entities.DebitNotes.Add(deb);
entities.SaveChanges();
}
}
else
{
if (deb != null)
{
entities.DebitNotes.Remove(deb);
entities.SaveChanges();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public string GetLastCashNo()
{
string st = Convert.ToString(Convert.ToByte(CashBankTransactionType.RefundFromSupplier));
string cNo = string.Empty;
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var pq = (from pqs in entities.CashAndBankTransactions
where pqs.Is_Cheque == false && pqs.Type == st
orderby pqs.UpdatedDate descending
select new
{
pqs.UpdatedDate,
pqs.Cash_Cheque_No,
pqs.ID,
pqs.Is_Cheque,
pqs.Type
}
).ToList();
if (pq.Count > 0)
{
cNo = pq.Take(1).SingleOrDefault().Cash_Cheque_No;
if (cNo != null)
{
string[] str = cNo.Split('-');
if (str != null)
{
cNo = Convert.ToString(Convert.ToInt64(str[1]) + 1);
}
}
}
else
{
cNo = Convert.ToString(1);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(cNo);
}
public bool CanDeleteOrder(int pqID)
{
byte deposited = Convert.ToByte(PO_Status.Collected);
byte refunded = Convert.ToByte(PO_Status.Refunded);
bool allowDelete = true;
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var PO_PI = entities.PurchaseOrders.Where(x => (x.PO_Conv_to_PI == true) && x.ID == pqID).FirstOrDefault();
var PO = entities.PurchaseOrders.Where(x => (x.PO_Status == deposited || x.PO_Status == refunded) && x.ID == pqID).FirstOrDefault();
if (PO_PI != null || PO != null)
{
allowDelete = false;
}
}
return(allowDelete);
}
public List <SupplierDetailEntity> GetAllSupplier()
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
//List<SupplierDetailEntity> suppliersource = entities.Suppliers.Where(x => x.IsDeleted != true).Select(x => new SupplierDetailEntity
//{
// ID = x.ID,
// SupplierName = x.Sup_Name,
// Createddate = x.CreatedDate,
//}).ToList();
List <SupplierDetailEntity> suppliersource = (from s in entities.Suppliers
join d in entities.DebitNotes on s.ID equals d.Sup_Id
where d.IsDeleted != true
select new SupplierDetailEntity
{
ID = s.ID,
SupplierName = s.Sup_Name
}).Distinct().ToList();
return(suppliersource);
}
}
public bool IsChequeNoPresent(string cashChequeNo)
{
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var po = entities.CashAndBankTransactions.Where(e => e.Cash_Cheque_No == cashChequeNo).ToList();
if (po.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <AccountsEntity> GetAccountDetails()
{
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
List <AccountsEntity> accountsource = entities.Accounts.Where(x => x.IsDeleted != true).Select(x => new AccountsEntity
{
AccountName = x.Acc_Name,
AccountID = x.ID,
AccountType = x.Acc_Type,
AccuntTypeCode = x.Acc_Type.ToString(),
IsInactive = x.Acc_Inactive
}).ToList();
return(accountsource);
}
}
catch (Exception ex)
{
throw ex;
}
}
public RefundFromSupplierForm GetNewPS(int?SupplierID)
{
RefundFromSupplierForm pqf = new RefundFromSupplierForm();
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var pqd = entities.Database.SqlQuery <RefundFromSupplierDetailsEntity>("PRC_GetPurchaseDataForRefundFromSuppliers @SupplierID={0}", SupplierID).ToList();
if (pqd != null)
{
pqf.RefundFromSupplierDetails = pqd;
}
return(pqf);
}
}
catch (Exception ex)
{
throw ex;
}
}
public AdjustDebitNoteForm GetNewPS(int?SupplierID)
{
AdjustDebitNoteForm pqf = new AdjustDebitNoteForm();
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var pqd = entities.Database.SqlQuery <AdjustDebitNoteDetailsEntity>("PRC_GetPurchaseDataForAdjustDebitNotes @SupplierID={0}", SupplierID).ToList();
if (pqd != null)
{
pqf.AdjustDebitNoteDetails = pqd;
}
return(pqf);
}
}
catch (Exception ex)
{
throw ex;
}
}
public string GetCountOfPOSuppliers(out List <SupplierDetailEntity> lstSuppliers)
{
string POCount = string.Empty;
lstSuppliers = new List <SupplierDetailEntity>();
byte collected = Convert.ToByte(PO_Status.Collected);
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var po = (from e in entities.PurchaseOrders
join p in entities.Suppliers
on e.Sup_Id equals p.ID
where e.IsDeleted == false &&
e.PO_Status == collected
select new SupplierDetailEntity
{
ID = e.Sup_Id,
SupplierName = p.Sup_Name
}
).Distinct().ToList();
if (po != null)
{
lstSuppliers = new List <SupplierDetailEntity>(po.ToList());
POCount = Convert.ToString(po.Count);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(POCount);
}
public AdjustDebitNoteForm GetAdjustDebitNoteDetails(string DebitNoteNo)
{
AdjustDebitNoteForm pqf = new AdjustDebitNoteForm();
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
decimal value;
var pq = (from pqs in entities.DebitNotes
join pi in entities.PurchaseInvoices on pqs.PI_Id equals pi.ID
where pqs.DN_No == DebitNoteNo
select new AdjustDebitNoteEntity
{
ID = pqs.ID,
SupplierID = pqs.Sup_Id,
//Amount = pi.PI_Tot_aft_Tax,
Amount = pi.PI_Tot_aft_Tax,
Date = pqs.DN_Date,
DebitNoteNo = pqs.DN_No
//CashChequeNo = pqs.Cash_Cheque_No,
}).FirstOrDefault();
if (pq != null)
{
pq.AmountStr = Convert.ToString(pq.Amount);
pqf.AdjustDebitNote = pq;
}
if (pq.SupplierID != 0 || pq.SupplierID != null)
{
//var pqd = (from pqs in entities.PurchaseInvoices
// where pqs.Sup_Id == pq.SupplierID && pqs.PI_Status == 2
// select new AdjustDebitNoteDetailsEntity
// {
// PurchaseNo = pqs.PI_No,
// PurchaseDate = pqs.PI_Date,
// PaymentDueDate = pqs.PI_Pmt_Due_Date,
// PurchaseAmount = pqs.PI_Tot_aft_Tax,
// //AmountDue = pqs.Amt_Due,
// //Discount = pqs.Discount,
// //AmountAdjusted = pqs
// }).ToList<AdjustDebitNoteDetailsEntity>();
var pqd = entities.Database.SqlQuery <AdjustDebitNoteDetailsEntity>("PRC_GetPurchaseDataForAdjustDebitNotes @SupplierID={0}", pq.SupplierID).ToList();
if (pqd != null)
{
pqf.AdjustDebitNoteDetails = pqd;
}
}
return(pqf);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// This method is used to convert the Purchase order to order
/// </summary>
/// <param name="orderData"></param>
/// <returns></returns>
public int ConvertToPurchaseOrder(PurchaseOrderForm orderData)
{
int autoId = 0;
//Add purchase order
PurchaseOrder obj = new PurchaseOrder();
//obj.ID = orderData.Order.ID;
obj.Sup_Id = orderData.Order.SupplierID;
obj.PO_Date = orderData.Order.OrderDate;
obj.PO_Del_Date = DateTime.Now;
obj.PO_GST_Amt = Convert.ToDecimal(orderData.Order.TotalTax);
obj.PO_No = "PO-" + GetLastOrderNo();
obj.PO_TandC = orderData.Order.TermsAndConditions;
obj.PO_Tot_aft_Tax = Convert.ToDecimal(orderData.Order.TotalAfterTax);
obj.PO_Tot_bef_Tax = Convert.ToDecimal(orderData.Order.TotalBeforeTax);
obj.Exc_Inc_GST = orderData.Order.ExcIncGST;
obj.IsDeleted = false;
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
if (entities.PurchaseOrders.AsNoTracking().FirstOrDefault(x => x.ID == orderData.Order.ID) == null)
{
//obj.CreatedBy = orderData.POModel.CreatedBy;
obj.CreatedDate = DateTime.Now;
entities.PurchaseOrders.Add(obj);
entities.SaveChanges();
autoId = obj.ID;
}
else
{
// obj.ModifiedBy = orderData.POModel.ModifiedBy;
obj.ModifiedDate = DateTime.Now;
entities.Entry(obj).State = EntityState.Modified;
autoId = entities.SaveChanges();
}
if (autoId > 0)
{
PurchaseOrderDetail PODetails;
if (orderData.OrderDetails != null)
{
foreach (PurchaseOrderDetailEntity PODetailEntity in orderData.OrderDetails)
{
PODetails = new PurchaseOrderDetail();
PODetails.PO_ID = autoId;
PODetails.PO_No = PODetailEntity.PONo;
PODetails.PandS_Code = PODetailEntity.PandSCode;
PODetails.PandS_Name = PODetailEntity.PandSName;
PODetails.PO_Amount = PODetailEntity.POAmount;
PODetails.PO_Discount = PODetailEntity.PODiscount;
PODetails.PO_No = PODetailEntity.PONo;
PODetails.PO_Price = Convert.ToDecimal(PODetailEntity.POPrice);
PODetails.PO_Qty = PODetailEntity.POQty;
PODetails.GST_Code = PODetailEntity.GSTCode;
PODetails.GST_Rate = PODetailEntity.GSTRate;
if (entities.PurchaseOrderDetails.AsNoTracking().FirstOrDefault(x => x.ID == PODetailEntity.ID) == null)
{
entities.PurchaseOrderDetails.Add(PODetails);
entities.SaveChanges();
}
else
{
entities.Entry(PODetails).State = EntityState.Modified;
entities.SaveChanges();
}
}
}
PurchaseOrder objQ = entities.PurchaseOrders.Where(e => e.PO_No == orderData.Order.OrderNo
).SingleOrDefault();
if (objQ != null)
{
//objQ.PO_Conv_to_PO = true;
objQ.ModifiedDate = DateTime.Now;
entities.SaveChanges();
}
}
}
return(autoId);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// This method is used to get Purchase Order details
/// </summary>
/// <param name="pqId"></param>
/// <returns></returns>
public PurchaseOrderForm GetPurchaseOrder(string pqno)
{
PurchaseOrderForm pqf = new PurchaseOrderForm();
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
var pq = (from pqs in entities.PurchaseOrders
where pqs.PO_No == pqno && (pqs.IsDeleted == false || pqs.IsDeleted == null)
select new PurchaseOrderEntity
{
ID = pqs.ID,
SupplierID = pqs.Sup_Id,
OrderNo = pqs.PO_No,
OrderDate = pqs.PO_Date,
//ValidForDays = pqs.PO_Valid_for,
TermsAndConditions = pqs.PO_TandC,
TotalBeforeTax = pqs.PO_Tot_bef_Tax,
TotalTax = pqs.PO_GST_Amt,
TotalAfterTax = pqs.PO_Tot_aft_Tax,
ExcIncGST = pqs.Exc_Inc_GST,
PO_Conv_to_PI = pqs.PO_Conv_to_PI,
DeliveryDate = pqs.PO_Del_Date,
Status = pqs.PO_Status
//PO_Conv_to_PO = pqs.PO_Conv_to_PO
}).SingleOrDefault();
if (pq != null)
{
pqf.Order = pq;
}
var pqd = (from pqds in entities.PurchaseOrderDetails
where pqds.PO_ID == pq.ID
select new PurchaseOrderDetailEntity
{
ID = pqds.ID,
POID = pqds.PO_ID,
PONo = pqds.PO_No,
PandSCode = pqds.PandS_Code,
PandSName = pqds.PandS_Name,
POQty = pqds.PO_Qty,
Price = pqds.PO_Price,
PODiscount = pqds.PO_Discount,
POAmount = pqds.PO_Amount,
GSTRate = pqds.GST_Rate
}).ToList <PurchaseOrderDetailEntity>();
if (pqd != null)
{
pqf.OrderDetails = pqd;
}
return(pqf);
}
}
catch (Exception ex)
{
throw ex;
}
}
public int UpdateAdjustDebitNote(AdjustDebitNoteForm psForm)
{
try
{
using (SDNPurchasingDBEntities entities = new SDNPurchasingDBEntities())
{
foreach (var item in psForm.AdjustDebitNoteDetails)
{
if (item.CheckAmountAdjusted == true)
{
CashAndBankTransaction obj = entities.CashAndBankTransactions.Where(e => e.SO_CN_PO_DN_No == item.PurchaseNo).SingleOrDefault();
if (obj != null)
{
obj.Acc_Id = psForm.AdjustDebitNote.AccountId;
obj.Amount = psForm.AdjustDebitNote.Amount;
//obj.Cash_Cheque_No = psForm.AdjustDebitNote.CashChequeNo;
obj.Cash_Cheque_Date = psForm.AdjustDebitNote.Date;
obj.Remarks = psForm.AdjustDebitNote.Remarks;
obj.Is_Cheque = psForm.AdjustDebitNote.IsCheque;
//saving details entity
obj.SO_CN_PO_DN_No = item.PurchaseNo;
obj.SO_CN_PO_DN_Date = item.PurchaseDate;
obj.SO_CN_PO_DN_Amt = item.PurchaseAmount;
obj.Amt_Due = item.AmountDue;
obj.Amt_Refunded = item.AmountAdjusted;
obj.Discount = item.Discount;
obj.DN_CN_No = psForm.AdjustDebitNote.DebitNoteNo;
obj.UpdatedBy = 0;
obj.UpdatedDate = DateTime.Now.Date;
entities.SaveChanges();
if (item.AmountDue == 0 && item.AmountAdjusted == item.PurchaseAmount)
{
PurchaseInvoice invoice = entities.PurchaseInvoices.Where(e => e.PI_No == item.PurchaseNo).SingleOrDefault();
//PurchaseOrder order = entities.PurchaseOrders.Where(e => e.PO_No == item.PurchaseNo).SingleOrDefault();
if (invoice != null)
{
invoice.PI_Status = Convert.ToByte(PI_Status.Paid);
entities.SaveChanges();
}
//else if (order != null)
//{
//}
DebitNote debit = entities.DebitNotes.Where(e => e.DN_No == psForm.AdjustDebitNote.DebitNoteNo).SingleOrDefault();
if (debit != null)
{
debit.DN_Status = Convert.ToByte(DN_Status.Adjusted);
entities.SaveChanges();
}
}
}
else
{
CashAndBankTransaction cashbankobj = new CashAndBankTransaction()
{
Acc_Id = psForm.AdjustDebitNote.AccountId,
Amount = psForm.AdjustDebitNote.Amount,
Cash_Cheque_Date = psForm.AdjustDebitNote.Date,
Remarks = psForm.AdjustDebitNote.Remarks,
Is_Cheque = psForm.AdjustDebitNote.IsCheque,
SO_CN_PO_DN_No = item.PurchaseNo,
SO_CN_PO_DN_Date = item.PurchaseDate,
SO_CN_PO_DN_Amt = item.PurchaseAmount,
Amt_Due = item.AmountDue,
Amt_Refunded = item.AmountAdjusted,
Discount = item.Discount,
UpdatedBy = 0,
UpdatedDate = DateTime.Now.Date,
Cus_Sup_Id = psForm.AdjustDebitNote.SupplierID,
DN_CN_No = psForm.AdjustDebitNote.DebitNoteNo,
Type = "S"
};
entities.CashAndBankTransactions.Add(cashbankobj);
entities.SaveChanges();
if (item.AmountDue == 0 && item.AmountAdjusted == item.PurchaseAmount)
{
PurchaseInvoice invoice = entities.PurchaseInvoices.Where(e => e.PI_No == item.PurchaseNo).SingleOrDefault();
//PurchaseOrder order = entities.PurchaseOrders.Where(e => e.PO_No == item.PurchaseNo).SingleOrDefault();
if (invoice != null)
{
invoice.PI_Status = Convert.ToByte(PI_Status.Paid);
entities.SaveChanges();
}
//else if (order != null)
//{
//}
}
DebitNote debit = entities.DebitNotes.Where(e => e.DN_No == psForm.AdjustDebitNote.DebitNoteNo).SingleOrDefault();
if (debit != null)
{
debit.DN_Status = Convert.ToByte(DN_Status.Adjusted);
entities.SaveChanges();
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(psForm.AdjustDebitNote.ID);
}
