public List <PurchaseInvoiceListEntity> GetAllPurInvoiceJson(string JsonData, bool?ExcincTax) { List <PurchaseInvoiceListEntity> InvoiceList = new List <PurchaseInvoiceListEntity>(); PurchaseInvoiceListEntity InvoiceList1 = new PurchaseInvoiceListEntity(); List <PurchaseInvoiceListEntity> InvoiceListReturn = new List <PurchaseInvoiceListEntity>(); ////InvoiceList = entities.PurchaseInvoices.Join(entities.Suppliers, y => y.Sup_Id, x => x.ID, (x, y) => new PurchaseInvoiceListEntity ////{ //// ID = x.ID, //// SupplierName = y.Sup_Name, //// Sup_ID = x.Sup_Id, //// InvoiceNo = x.PI_No, //// InvoiceDate = x.PI_Date.ToString(), //// InvoiceAmount = x.PI_Tot_aft_Tax.ToString(), //// InvoiceAmountIncGST = x.PI_Tot_aft_Tax.ToString(), //// InvoiceAmountExcGST = x.PI_Tot_bef_Tax.ToString(), //// //ConvertedToPI = x.PO_Conv_to_PI, //// //ConvertedToPO = x.PO_Conv_to_PO, //// IsDeleted = x.IsDeleted, //// SelectedSearchPQList = x.ID, //// CreatedDate = x.PI_Date, //// CreatedDateList = x.CreatedDate, //// ExcIncGST = x.Exc_Inc_GST, //// InvoiceDateDateTime = x.PI_Date ////}).Where(w => w.IsDeleted != true && w.ExcIncGST == ExcincTax).ToList(); //InvoiceList = (from i in entities.PurchaseInvoices // join s in entities.Suppliers // on i.Sup_Id equals s.ID // join cbt in entities.CashAndBankTransactions // on s.ID equals cbt.Cus_Sup_Id into cbtej // from cbt in cbtej.DefaultIfEmpty() // join d in entities.DebitNotes on i.ID equals d.PI_Id into ej // from d in ej.DefaultIfEmpty() // where i.IsDeleted != true && d.IsDeleted != true // select new PurchaseInvoiceListEntity // { // ID = i.ID, // Sup_ID = i.Sup_Id, // ExcIncGST = i.Exc_Inc_GST, // DebitNoteAmount = d.Sup_CN_Amount, // DebitNoteDate = d.DN_Date.ToString(), // DebitNoteNo = d.DN_No, // SupplierName = s.Sup_Name, // Status = i.PI_Status, // InvoiceNo = i.PI_No, // InvoiceDate = i.PI_Date.ToString(), // InvoiceAmount = i.PI_Tot_aft_Tax.ToString(), // IsDeleted = i.IsDeleted, // CashChequeAmount = cbt.Amount, // CashChequeDate = cbt.Cash_Cheque_Date.ToString(), // CashChequeNo = cbt.Cash_Cheque_No // }).ToList(); InvoiceList = entities.Database.SqlQuery <PurchaseInvoiceListEntity>("SP_PurchaseInvoiceList").ToList(); if (JsonData != null && JsonData != "[]") { DateTime startDate = new DateTime(); //DateTime endDate = new DateTime(); var objResponse1 = JsonConvert.DeserializeObject <List <SearchEntity> >(JsonData); foreach (var item in objResponse1) { switch (item.FieldName) { case "Year": var year = Convert.ToInt32(item.FieldValue); InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Year == year).ToList(); break; case "Quarter": switch (item.FieldValue) { case "1": InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Month == 1 || x.InvoiceDateDateTime.Value.Month == 2 || x.InvoiceDateDateTime.Value.Month == 3).ToList(); break; case "2": InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Month == 4 || x.InvoiceDateDateTime.Value.Month == 5 || x.InvoiceDateDateTime.Value.Month == 6).ToList(); break; case "3": InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Month == 7 || x.InvoiceDateDateTime.Value.Month == 8 || x.InvoiceDateDateTime.Value.Month == 9).ToList(); break; case "4": InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Month == 10 || x.InvoiceDateDateTime.Value.Month == 11 || x.InvoiceDateDateTime.Value.Month == 12).ToList(); break; } break; case "Month": var month = Convert.ToInt32(item.FieldValue); InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Month == month).ToList(); break; case "StartDate": //bool dateValid = DateTime.TryParse(item.FieldValue, out startDate); startDate = DateTime.ParseExact(item.FieldValue, "MMM/dd/yyyy", CultureInfo.InvariantCulture); //startDate = Convert.ToDateTime(item.FieldValue); //InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime ).ToList(); break; case "EndDate": //bool dateValid1 = DateTime.TryParse(item.FieldValue, out endDate); DateTime endDate = DateTime.ParseExact(item.FieldValue, "MMM/dd/yyyy", CultureInfo.InvariantCulture); // DateTime endDate = Convert.ToDateTime(item.FieldValue); InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime > startDate && x.InvoiceDateDateTime < endDate).ToList(); break; } } InvoiceListReturn = InvoiceList; } else { InvoiceListReturn = InvoiceList; } return(InvoiceListReturn); }
public List <PurchaseInvoiceListEntity> GetAllPurInvoiceJson(string JsonData, bool?ExcincTax) { List <PurchaseInvoiceListEntity> InvoiceList = new List <PurchaseInvoiceListEntity>(); PurchaseInvoiceListEntity InvoiceList1 = new PurchaseInvoiceListEntity(); List <PurchaseInvoiceListEntity> InvoiceListReturn = new List <PurchaseInvoiceListEntity>(); InvoiceList = entities.Database.SqlQuery <PurchaseInvoiceListEntity>("SP_PurchaseInvoiceList").ToList(); if (JsonData != null && JsonData != "[]") { DateTime startDate = new DateTime(); //DateTime endDate = new DateTime(); var objResponse1 = JsonConvert.DeserializeObject <List <SearchEntity> >(JsonData); foreach (var item in objResponse1) { switch (item.FieldName) { case "Year": var year = Convert.ToInt32(item.FieldValue); InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Year == year).ToList(); break; case "Quarter": switch (item.FieldValue) { case "1": InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Month == 1 || x.InvoiceDateDateTime.Value.Month == 2 || x.InvoiceDateDateTime.Value.Month == 3).ToList(); break; case "2": InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Month == 4 || x.InvoiceDateDateTime.Value.Month == 5 || x.InvoiceDateDateTime.Value.Month == 6).ToList(); break; case "3": InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Month == 7 || x.InvoiceDateDateTime.Value.Month == 8 || x.InvoiceDateDateTime.Value.Month == 9).ToList(); break; case "4": InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Month == 10 || x.InvoiceDateDateTime.Value.Month == 11 || x.InvoiceDateDateTime.Value.Month == 12).ToList(); break; } break; case "Month": var month = Convert.ToInt32(item.FieldValue); if (month == 0) { break; } else { InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime.Value.Month == month).ToList(); break; } case "StartDate": //bool dateValid = DateTime.TryParse(item.FieldValue, out startDate); startDate = DateTime.ParseExact(item.FieldValue, "MMM/dd/yyyy", CultureInfo.InvariantCulture); //startDate = Convert.ToDateTime(item.FieldValue); //InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime ).ToList(); break; case "EndDate": //bool dateValid1 = DateTime.TryParse(item.FieldValue, out endDate); DateTime endDate = DateTime.ParseExact(item.FieldValue, "MMM/dd/yyyy", CultureInfo.InvariantCulture); // DateTime endDate = Convert.ToDateTime(item.FieldValue); InvoiceList = InvoiceList.Where(x => x.InvoiceDateDateTime > startDate && x.InvoiceDateDateTime < endDate).ToList(); break; } } InvoiceListReturn = InvoiceList; } else { InvoiceListReturn = InvoiceList; } return(InvoiceListReturn); }