public List <PointLedgerModel> GetInvoiceData(string InvoiceNo, string CustomerId)
{
List <PointLedgerModel> bojList = new List <PointLedgerModel>();
using (var context = new ChitaleDBContext())
{
var checkTrans = context.TransactionMasters.Where(x => x.InvoiceNo == InvoiceNo && x.CustomerId == CustomerId && x.Type != null).FirstOrDefault();
if (checkTrans.Type == "Purchase" || checkTrans.Type == "Sale")
{
var ObjInvoiceLst = context.InvoiceOrderMappings.Where(x => x.InvoiceNo == InvoiceNo).ToList();
if (ObjInvoiceLst != null)
{
foreach (var item in ObjInvoiceLst)
{
PointLedgerModel objPointLedger = new PointLedgerModel();
var objTransaction = context.TransactionMasters.Where(x => x.OrderNo == item.OrderNo && ((x.TxnType == "Purchase" && x.SubType == "Order") ||
(x.TxnType == "Cancel" && x.SubType == "Cancel") || (x.TxnType == "Modified" && x.SubType == "Re-order") ||
(x.TxnType == "Bal-order" && x.SubType == "Bal-order") || (x.TxnType == "Sale" && x.SubType == "Invoice"))).FirstOrDefault();
if (objTransaction != null)
{
objPointLedger.TxnType = "";
objPointLedger.SubType = objTransaction.SubType;
objPointLedger.RefNo = objTransaction.OrderNo;
objPointLedger.Amount = objTransaction.InvoiceAmt;
objPointLedger.AmountStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(objTransaction.InvoiceAmt));
if (!string.IsNullOrEmpty(Convert.ToString(objTransaction.NormalPoints)))
{
objPointLedger.BasePoints = objTransaction.NormalPoints;
objPointLedger.BasePointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(objTransaction.NormalPoints));
}
else
{
objPointLedger.BasePoints = 0;
objPointLedger.BasePointsStr = "0.00";
}
if (!string.IsNullOrEmpty(Convert.ToString(objTransaction.AddOnPoints)))
{
objPointLedger.AddOnPoints = objTransaction.AddOnPoints;
objPointLedger.AddOnPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(objTransaction.AddOnPoints));
}
else
{
objPointLedger.AddOnPoints = 0;
objPointLedger.AddOnPointsStr = "0.00";
}
if (!string.IsNullOrEmpty(Convert.ToString(objTransaction.PenaltyPoints)))
{
objPointLedger.LostOppPoints = objTransaction.PenaltyPoints;
objPointLedger.LostOppPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(objTransaction.PenaltyPoints));
}
else
{
objPointLedger.LostOppPoints = 0;
objPointLedger.LostOppPointsStr = "0.00";
}
objPointLedger.OrderDate = objTransaction.OrderDatetime.Value.ToString("dd-MM-yyyy");
objPointLedger.RavanaDate = objTransaction.RavanaDate.Value.ToString("dd-MM-yyyy");
if (!string.IsNullOrEmpty(Convert.ToString(objTransaction.TotalPoints)))
{
objPointLedger.NetEarnPoints = objTransaction.TotalPoints;
objPointLedger.NetEarnPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(objTransaction.TotalPoints));
}
else
{
objPointLedger.NetEarnPoints = 0;
objPointLedger.NetEarnPointsStr = "0.00";
}
objPointLedger.DaysDiff = Convert.ToInt32((objTransaction.RavanaDate.Value.Date - objTransaction.OrderDatetime.Value.Date).Days);
bojList.Add(objPointLedger);
}
}
}
}
if (checkTrans.Type == "Tgt Vs Ach")
{
var objTransaction = context.TransactionMasters.Where(x => x.InvoiceNo == InvoiceNo && x.CustomerId == CustomerId && (x.SubType == "Volume" || x.SubType == "Value")).ToList();
foreach (var item in objTransaction)
{
PointLedgerModel objPointLedger = new PointLedgerModel();
objPointLedger.TxnType = "";
objPointLedger.SubType = item.SubType;
objPointLedger.RefNo = item.InvoiceNo;
objPointLedger.OrderDate = item.OrderDatetime.Value.ToString("dd-MM-yyyy");
objPointLedger.AmountStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.InvoiceAmt));
objPointLedger.AchievedAmt = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.AchievedAmt));
objPointLedger.Variance = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.Variance));
objPointLedger.AchPercentage = Convert.ToString(item.AchPercentage);// String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.Variance));
if (!string.IsNullOrEmpty(Convert.ToString(item.AddOnPoints)))
{
objPointLedger.AddOnPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.AddOnPoints));
}
else
{
objPointLedger.AddOnPointsStr = "0.00";
}
if (!string.IsNullOrEmpty(Convert.ToString(item.PenaltyPoints)))
{
objPointLedger.LostOppPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.PenaltyPoints));
}
else
{
objPointLedger.LostOppPointsStr = "0.00";
}
bojList.Add(objPointLedger);
}
}
}
return(bojList);
}
public List <PointLedgerModel> GetPointLedgerData(string CustomerId, string isBTD, string FrmDate, string ToDate)
{
List <PointLedgerModel> bojList = new List <PointLedgerModel>();
using (var context = new ChitaleDBContext())
{
List <string> ObjInvoiceLst = new List <string>();
//This is For Purchase
if (isBTD == "1")
{
ObjInvoiceLst = context.InvoiceOrderMappings.Where(x => x.CustomerId == CustomerId).Select(y => y.InvoiceNo).Distinct().ToList();
}
else
{
var fromDate = Convert.ToDateTime(FrmDate);
var toDate = Convert.ToDateTime(ToDate);
ObjInvoiceLst = context.InvoiceOrderMappings.Where(x => x.CustomerId == CustomerId && x.InvoiceDate >= fromDate && x.InvoiceDate <= toDate).Select(y => y.InvoiceNo).Distinct().ToList();
}
if (ObjInvoiceLst != null)
{
foreach (var item in ObjInvoiceLst)
{
PointLedgerModel objPointLedger = new PointLedgerModel();
var objTransaction = context.TransactionMasters.Where(x => x.InvoiceNo == item && (x.Type == "Purchase")).FirstOrDefault();
if (objTransaction != null)
{
objPointLedger.CustomerId = CustomerId;
objPointLedger.TxnType = objTransaction.Type;
objPointLedger.SubType = "Invoice";
objPointLedger.RefNo = objTransaction.InvoiceNo;
objPointLedger.Amount = objTransaction.InvoiceAmt;
objPointLedger.AmountStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(objTransaction.InvoiceAmt));
if (!string.IsNullOrEmpty(Convert.ToString(objTransaction.NormalPoints)))
{
objPointLedger.BasePoints = objTransaction.NormalPoints;
objPointLedger.BasePointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(objTransaction.NormalPoints));
}
else
{
objPointLedger.BasePoints = 0;
objPointLedger.BasePointsStr = "0.00";
}
if (!string.IsNullOrEmpty(Convert.ToString(objTransaction.AddOnPoints)))
{
objPointLedger.AddOnPoints = objTransaction.AddOnPoints;
objPointLedger.AddOnPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(objTransaction.AddOnPoints));
}
else
{
objPointLedger.AddOnPoints = 0;
objPointLedger.AddOnPointsStr = "0.00";
}
if (!string.IsNullOrEmpty(Convert.ToString(objTransaction.PenaltyPoints)))
{
objPointLedger.LostOppPoints = objTransaction.PenaltyPoints;
objPointLedger.LostOppPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(objTransaction.PenaltyPoints));
}
else
{
objPointLedger.LostOppPoints = 0;
objPointLedger.LostOppPointsStr = "0.00";
}
objPointLedger.OrderDate = objTransaction.OrderDatetime.Value.ToString("dd-MM-yyyy");
objPointLedger.RavanaDate = objTransaction.RavanaDate.Value.ToString("dd-MM-yyyy");
if (!string.IsNullOrEmpty(Convert.ToString(objTransaction.TotalPoints)))
{
objPointLedger.NetEarnPoints = objTransaction.TotalPoints;
objPointLedger.NetEarnPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(objTransaction.TotalPoints));
}
else
{
objPointLedger.NetEarnPoints = 0;
objPointLedger.NetEarnPointsStr = "0.00";
}
objPointLedger.DaysDiff = Convert.ToInt32((objTransaction.RavanaDate.Value.Date - objTransaction.OrderDatetime.Value.Date).Days);
bojList.Add(objPointLedger);
}
}
}
//This is For Tgt Vs Ach
List <TransactionMaster> tgtTransaction = new List <TransactionMaster>();
if (isBTD == "1")
{
tgtTransaction = context.TransactionMasters.Where(x => x.CustomerId == CustomerId && x.Type == "Tgt Vs Ach").ToList();
}
else
{
var fromDate = Convert.ToDateTime(FrmDate);
var toDate = Convert.ToDateTime(ToDate);
tgtTransaction = context.TransactionMasters.Where(x => x.CustomerId == CustomerId && x.OrderDatetime >= fromDate && x.OrderDatetime <= toDate && x.Type == "Tgt Vs Ach").ToList();
}
foreach (var item in tgtTransaction)
{
PointLedgerModel objPointLedger = new PointLedgerModel();
objPointLedger.CustomerId = CustomerId;
objPointLedger.TxnType = item.Type;
objPointLedger.SubType = "Invoice";
objPointLedger.RefNo = item.InvoiceNo;
objPointLedger.Amount = item.InvoiceAmt;
objPointLedger.AmountStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.InvoiceAmt));
if (!string.IsNullOrEmpty(Convert.ToString(item.NormalPoints)))
{
objPointLedger.BasePoints = item.NormalPoints;
objPointLedger.BasePointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.NormalPoints));
}
else
{
objPointLedger.BasePoints = 0;
objPointLedger.BasePointsStr = "0.00";
}
if (!string.IsNullOrEmpty(Convert.ToString(item.AddOnPoints)))
{
objPointLedger.AddOnPoints = item.AddOnPoints;
objPointLedger.AddOnPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.AddOnPoints));
}
else
{
objPointLedger.AddOnPoints = 0;
objPointLedger.AddOnPointsStr = "0.00";
}
if (!string.IsNullOrEmpty(Convert.ToString(item.PenaltyPoints)))
{
objPointLedger.LostOppPoints = item.PenaltyPoints;
objPointLedger.LostOppPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.PenaltyPoints));
}
else
{
objPointLedger.LostOppPoints = 0;
objPointLedger.LostOppPointsStr = "0.00";
}
objPointLedger.OrderDate = item.OrderDatetime.Value.ToString("dd-MM-yyyy");
objPointLedger.RavanaDate = item.RavanaDate.Value.ToString("dd-MM-yyyy");
if (!string.IsNullOrEmpty(Convert.ToString(item.TotalPoints)))
{
objPointLedger.NetEarnPoints = item.TotalPoints;
objPointLedger.NetEarnPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.TotalPoints));
}
else
{
objPointLedger.NetEarnPoints = 0;
objPointLedger.NetEarnPointsStr = "0.00";
}
objPointLedger.DaysDiff = Convert.ToInt32((item.RavanaDate.Value.Date - item.OrderDatetime.Value.Date).Days);
bojList.Add(objPointLedger);
}
//This is For Sale
List <TransactionMaster> tgtTransactionSale = new List <TransactionMaster>();
if (isBTD == "1")
{
tgtTransactionSale = context.TransactionMasters.Where(x => x.CustomerId == CustomerId && x.TxnType == "Sale" && x.SubType == "Invoice").ToList();
}
else
{
var fromDate = Convert.ToDateTime(FrmDate);
var toDate = Convert.ToDateTime(ToDate);
tgtTransactionSale = context.TransactionMasters.Where(x => x.CustomerId == CustomerId && x.OrderDatetime >= fromDate && x.OrderDatetime <= toDate && x.TxnType == "Sale" && x.SubType == "Invoice").ToList();
}
foreach (var item in tgtTransactionSale)
{
PointLedgerModel objPointLedger = new PointLedgerModel();
objPointLedger.CustomerId = CustomerId;
objPointLedger.TxnType = item.Type;
objPointLedger.SubType = "Invoice";
objPointLedger.RefNo = item.InvoiceNo;
objPointLedger.Amount = item.InvoiceAmt;
objPointLedger.AmountStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.InvoiceAmt));
if (!string.IsNullOrEmpty(Convert.ToString(item.NormalPoints)))
{
objPointLedger.BasePoints = item.NormalPoints;
objPointLedger.BasePointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.NormalPoints));
}
else
{
objPointLedger.BasePoints = 0;
objPointLedger.BasePointsStr = "0.00";
}
if (!string.IsNullOrEmpty(Convert.ToString(item.AddOnPoints)))
{
objPointLedger.AddOnPoints = item.AddOnPoints;
objPointLedger.AddOnPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.AddOnPoints));
}
else
{
objPointLedger.AddOnPoints = 0;
objPointLedger.AddOnPointsStr = "0.00";
}
if (!string.IsNullOrEmpty(Convert.ToString(item.PenaltyPoints)))
{
objPointLedger.LostOppPoints = item.PenaltyPoints;
objPointLedger.LostOppPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.PenaltyPoints));
}
else
{
objPointLedger.LostOppPoints = 0;
objPointLedger.LostOppPointsStr = "0.00";
}
objPointLedger.OrderDate = item.OrderDatetime.Value.ToString("dd-MM-yyyy");
objPointLedger.RavanaDate = item.RavanaDate.Value.ToString("dd-MM-yyyy");
if (!string.IsNullOrEmpty(Convert.ToString(item.TotalPoints)))
{
objPointLedger.NetEarnPoints = item.TotalPoints;
objPointLedger.NetEarnPointsStr = String.Format(new CultureInfo("en-IN", false), "{0:n}", Convert.ToDecimal(item.TotalPoints));
}
else
{
objPointLedger.NetEarnPoints = 0;
objPointLedger.NetEarnPointsStr = "0.00";
}
objPointLedger.DaysDiff = Convert.ToInt32((item.RavanaDate.Value.Date - item.OrderDatetime.Value.Date).Days);
bojList.Add(objPointLedger);
}
}
return(bojList);
}