public static ObservableCollection <Deposit> GetDeposits(NNM1_Series serie, DateTime?startDate, DateTime?endDate, string keyword, LocalStatus?status)
{
lock (Extensions.Locker)
{
var db = ContextFactory.GetDBContext();
IQueryable <Deposit> deposits = db.Deposits.Include("Cashier").Include("ShopManager");
// if (serie!= null) deposits.Where(d => d.IdSerie== serie.Series);
if (startDate.HasValue)
{
deposits = deposits.Where(d => d.Date >= startDate.Value);
}
if (endDate.HasValue)
{
deposits = deposits.Where(d => d.Date <= endDate.Value);
}
if (!string.IsNullOrEmpty(keyword))
{
deposits = deposits.Where(d => d.Comments.ToLower().Contains(keyword));
}
if (status.HasValue)
{
deposits = deposits.Where(d => d.StateL == status.Value);
}
return(new ObservableCollection <Deposit>(deposits.ToList()));
}
}
public static ObservableCollection <ODPI_DownPayment> GetDownPayments(
NNM1_Series serie, DateTime?startDate, DateTime?endDate, string keyWords, LocalStatus?localstatus,
bool IsAsNoTrackingForDownPayment)
{
lock (Extensions.Locker)
{
var db = ContextFactory.GetDBContext();
IQueryable <ODPI_DownPayment> downPayments = db.ODPI_DownPayment.Include("DPI1_DownPaymentDetail");
if (serie != null)
{
downPayments = downPayments.Where(dp => dp.Series == serie.Series);
}
if (!string.IsNullOrEmpty(keyWords))
{
downPayments = downPayments.Where(dp => dp.Comments.Contains(keyWords));
}
if (startDate.HasValue)
{
downPayments = downPayments.Where(dp => dp.DocDate >= startDate.Value);
}
if (endDate.HasValue)
{
downPayments = downPayments.Where(dp => dp.DocDate <= endDate.Value);
}
if (localstatus.HasValue)
{
downPayments = downPayments.Where(dp => dp.StateL == localstatus.Value);
}
var queryNames = (from dp in downPayments
join srie in db.NNM1_Series on dp.Series equals srie.Series
select new
{
DownPaymentId = dp.IdDownPayment,
SerieTitle = srie.SeriesName + " " + srie.Remark
}).ToList();
// con el as notracking resuelve el bug de ventas serie43
var colecction = IsAsNoTrackingForDownPayment ? downPayments.AsNoTracking().ToList(): downPayments.ToList();
colecction.ForEach(
trl =>
{
var filler = queryNames.FirstOrDefault(f => f.DownPaymentId == trl.IdDownPayment);
if (filler != null)
{
trl.SeriesTitle = filler.SerieTitle;
}
});
return(new ObservableCollection <ODPI_DownPayment>(colecction));
}
}
public static ObservableCollection <OPCH_Purchase> GetPurchase(NNM1_Series serie, DateTime?startDate, DateTime?endDate, string keywords, LocalStatus?status)
{
lock (Extensions.Locker)
{
var db = ContextFactory.GetDBContext();
IQueryable <OPCH_Purchase> purchases = db.OPCH_Purchase.Include("PCH1_PurchaseDetail").Include("PCH1_PurchaseDetail.OITM_Articles");
var p = new OPCH_Purchase();
// if (!string.IsNullOrEmpty(type.ToString())) purchases = purchases.Where(t => t.Series == type);
if (!string.IsNullOrEmpty(keywords))
{
purchases = purchases.Where(t => t.Comments.Contains(keywords));
}
if (startDate.HasValue)
{
purchases = purchases.Where(d => d.DocDueDate >= startDate.Value);
}
if (endDate.HasValue)
{
purchases = purchases.Where(t => t.DocDueDate <= endDate.Value);
}
if (serie != null)
{
purchases = purchases.Where(d => d.Series == serie.Series);
}
if (status.HasValue)
{
purchases = purchases.Where(t => t.StateL == status.Value);
}
var queryNames = (from purchase in purchases
join srie in db.NNM1_Series on purchase.Series equals srie.Series
select new
{
purchaseId = purchase.IdPurchase,
SerieTitle = srie.SeriesName + " " + srie.Remark
}).ToList();
var purchaseList = purchases.ToList();
purchaseList.ForEach(
trl =>
{
var filler = queryNames.FirstOrDefault(f => f.purchaseId == trl.IdPurchase);
//var filler = db.NNM1_Series.FirstOrDefault(b => b.Series == trl.Series);
if (filler != null)
{
trl.SeriesTitle = filler.SerieTitle;
}
});
return(new ObservableCollection <OPCH_Purchase>(purchaseList.OrderByDescending(t => t.ModifiedDateL).ThenBy(t => t.StateL)));
}
}
public static ObservableCollection <OINV_Sales> GetSales(NNM1_Series serie,
DateTime?startDate, DateTime?endDate, string keywords, LocalStatus?status)
{
lock (Extensions.Locker)
{
var db = ContextFactory.GetDBContext();
IQueryable <OINV_Sales> sales = db.OINV_Sales.Include(s => s.PaymentType).Include(s => s.RoyaltyPaymentType)
.Include(s => s.OWHS_Branch).Include(s => s.INV1_SalesDetail).Include("INV1_SalesDetail.OITM_Articles");
if (serie != null)
{
sales = sales.Where(t => t.Series == serie.Series);
}
if (!string.IsNullOrEmpty(keywords))
{
sales = sales.Where(t => t.Comments.Contains(keywords));
}
if (startDate.HasValue)
{
sales = sales.Where(d => d.DocDate >= startDate.Value);
}
if (endDate.HasValue)
{
sales = sales.Where(t => t.DocDate <= endDate.Value);
}
if (status.HasValue)
{
sales = sales.Where(t => t.StateL == status.Value);
}
var queryNames = (from sale in sales
join srie in db.NNM1_Series on sale.Series equals srie.Series
select new
{
saleId = sale.IdSaleL,
SerieTitle = srie.SeriesName + " " + srie.Remark
}).ToList();
sales.ToList().ForEach(
trl =>
{
var filler = queryNames.FirstOrDefault(f => f.saleId == trl.IdSaleL);
if (filler != null)
{
trl.SeriesTitle = filler.SerieTitle;
}
});
return(new ObservableCollection <OINV_Sales>(sales));
}
}
public static ObservableCollection <ORPC_SupplierCreditNotes> GetPurchase(NNM1_Series serie, DateTime?startDate, DateTime?endDate, string keywords, LocalStatus?status)
{
lock (Extensions.Locker)
{
var db = ContextFactory.GetDBContext();
IQueryable <ORPC_SupplierCreditNotes> creditnotes = db.ORPC_SupplierCreditNotes.Include(ccn => ccn.RPC1_SupplierCreditNoteDetail).Include("RPC1_SupplierCreditNoteDetail.OITM_Articles");
//var p = new OPCH_Purchase();
if (!string.IsNullOrEmpty(keywords))
{
creditnotes = creditnotes.Where(t => t.Comments.Contains(keywords));
}
if (startDate.HasValue)
{
creditnotes = creditnotes.Where(d => d.DocDate >= startDate.Value);
}
if (endDate.HasValue)
{
creditnotes = creditnotes.Where(t => t.DocDate <= endDate.Value);
}
if (serie != null)
{
creditnotes = creditnotes.Where(d => d.Series == serie.Series);
}
if (status.HasValue)
{
creditnotes = creditnotes.Where(t => t.StateL == status.Value);
}
var queryNames = (from note in creditnotes.ToList()
join srie in db.NNM1_Series on note.Series equals srie.Series
where serie != null
select new
{
NoteId = note.IdSupplierCreditNote,
Title = serie.SeriesName + " " + serie.Remark
}).ToList();
creditnotes.ToList().ForEach(
trl =>
{
//var filler = db.NNM1_Series.FirstOrDefault(b => b.Series == trl.Series);
//if (filler != null) trl.SeriesTitle = filler.SeriesName + " " + filler.Remark;
var name = queryNames.FirstOrDefault(q => q.NoteId == trl.IdSupplierCreditNote);
trl.SeriesTitle = name != null ? name.Title : string.Empty;
});
return(new ObservableCollection <ORPC_SupplierCreditNotes>(creditnotes.OrderByDescending(t => t.ModifiedDateL).ThenBy(t => t.StateL)));
}
}
public static ObservableCollection <ODPI_DownPayment> GetDownPaymentsProcessed(OCRD_BusinessPartner selectedPartner, NNM1_Series serie)
{
lock (Extensions.Locker)
{
var db = ContextFactory.GetDBContext();
IQueryable <ODPI_DownPayment> downPayments = null;
if (serie != null)
{
if (serie.Series == 43)
{
downPayments = db.ODPI_DownPayment.Include(dp => dp.DPI1_DownPaymentDetail)
.Where(dp => dp.StateL == LocalStatus.Procesado && dp.CardCode
== selectedPartner.CardCode && (dp.IdSaleL == null || dp.IdSaleL == 0) &&
dp.Series == 31);
}
if (serie.Series == 42)
{
downPayments = db.ODPI_DownPayment.Include(dp => dp.DPI1_DownPaymentDetail)
.Where(dp => dp.StateL == LocalStatus.Procesado && dp.CardCode
== selectedPartner.CardCode && (dp.IdSaleL == null || dp.IdSaleL == 0) &&
dp.Series == 118);
}
}
var existing = from sale in db.OINV_Sales.Where(d => d.dpEntry != 0)
join dp in downPayments on sale.dpEntry equals dp.DocEntry
select dp;
var existingByDetails = from detail in db.RIN1_ClientCreditNoteDetail
join dp in downPayments on detail.BaseEntry equals dp.DocEntry
select dp;
downPayments = downPayments.Where(d => !existing.Contains(d) && !existingByDetails.Contains(d));
//// TODO : implementar mejora de rendimiento hacer Tolist de todos los ids de la venta es lento , implementar un Left JOin , not Exists
//// para no mostrar anticipos cancelados ..
var downpaymentsLIst = downPayments.ToList();
var queryNames = (from dp in downpaymentsLIst
join srie in db.NNM1_Series on dp.Series equals srie.Series
select new
{
DownPaymentId = dp.IdDownPayment,
SerieTitle = srie.SeriesName + " " + srie.Remark
}).ToList();
downpaymentsLIst.ForEach(
trl =>
{
var filler = queryNames.FirstOrDefault(f => f.DownPaymentId == trl.IdDownPayment);
if (filler != null)
{
trl.SeriesTitle = filler.SerieTitle;
}
});
return(new ObservableCollection <ODPI_DownPayment>(downpaymentsLIst));
}
}
public static ObservableCollection <ORIN_ClientCreditNotes> GetCreditNotes(ClientCreditNoteType type, NNM1_Series serie, DateTime?startDate, DateTime?endDate, string keywords, LocalStatus?status)
{
lock (Extensions.Locker)
{
var db = ContextFactory.GetDBContext();
var creditnotes = db.ORIN_ClientCreditNotes.Include(ccn => ccn.RIN1_ClientCreditNoteDetail);
if (!string.IsNullOrEmpty(keywords))
{
creditnotes = creditnotes.Where(t => t.Comments.Contains(keywords));
}
if (startDate.HasValue)
{
creditnotes = creditnotes.Where(d => d.DocDate >= startDate.Value);
}
if (endDate.HasValue)
{
creditnotes = creditnotes.Where(t => t.DocDate <= endDate.Value);
}
if (serie != null)
{
creditnotes = creditnotes.Where(d => d.Series == serie.Series);
}
if (status.HasValue)
{
creditnotes = creditnotes.Where(t => t.StateL == status.Value);
}
var queryNames = (from note in creditnotes.ToList()
join srie in db.NNM1_Series on note.Series equals srie.Series
where serie != null
select new
{
NoteId = note.IdClientCreditNoteL,
Title = serie.SeriesName + " " + serie.Remark
}).ToList();
creditnotes.ToList().ForEach(
trl =>
{
var name = queryNames.FirstOrDefault(q => q.NoteId == trl.IdClientCreditNoteL);
trl.SeriesTitle = name != null ? name.Title : string.Empty;
});
return(new ObservableCollection <ORIN_ClientCreditNotes>(creditnotes.OrderByDescending(t => t.ModifiedDateL).ThenBy(t => t.StateL)));
}
}
