public List <DebitNoteListEntity> GetAllPurDebitJson(string JsonData, bool?ExcincTax) { List <DebitNoteListEntity> DebitList = new List <DebitNoteListEntity>(); DebitNoteListEntity DebitList1 = new DebitNoteListEntity(); List <DebitNoteListEntity> DebitListReturn = new List <DebitNoteListEntity>(); string ignored = string.Empty; if (JsonData != null) { var objResponse1 = JsonConvert.DeserializeObject <List <SearchEntity> >(JsonData); DebitList = entities.Database.SqlQuery <DebitNoteListEntity>("USP_DebitNoteList @year,@Quarter,@Month,@ShowAll,@SDate,@EDate", new SqlParameter("Year", Convert.ToInt32(objResponse1[0].FieldValue)), new SqlParameter("Quarter", Convert.ToInt32(objResponse1[1].FieldValue)), new SqlParameter("Month", Convert.ToInt32(objResponse1[2].FieldValue)), new SqlParameter("ShowAll", Convert.ToBoolean(objResponse1[3].FieldValue)), new SqlParameter("SDate", objResponse1[4].FieldValue), new SqlParameter("EDate", objResponse1[5].FieldValue) ).ToList(); } else { ignored = JsonConvert.SerializeObject(JsonData, Formatting.Indented, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }); DebitList = entities.Database.SqlQuery <DebitNoteListEntity>("USP_DebitNoteList @year,@Quarter,@Month,@ShowAll,@SDate,@EDate", new SqlParameter("Year", Convert.ToInt32(0)), new SqlParameter("Quarter", Convert.ToInt32(0)), new SqlParameter("Month", Convert.ToInt32(0)), new SqlParameter("ShowAll", 1), new SqlParameter("SDate", ""), new SqlParameter("EDate", "") ).ToList(); } return(DebitList); }
public List <DebitNoteListEntity> GetAllPurDebitJson(string JsonData, bool?ExcincTax) { List <DebitNoteListEntity> DebitList = new List <DebitNoteListEntity>(); DebitNoteListEntity DebitList1 = new DebitNoteListEntity(); List <DebitNoteListEntity> DebitListReturn = new List <DebitNoteListEntity>(); ////DebitList = entities.DebitNotes.Join(entities.Suppliers, y => y.Sup_Id, x => x.ID, (x, y) => new DebitNoteListEntity ////{ //// ID = x.ID, //// SupplierName = y.Sup_Name, //// Sup_ID = x.Sup_Id, //// DebitNo = x.PI_No, //// DebitDate = x.PI_Date.ToString(), //// DebitAmount = x.PI_Tot_aft_Tax.ToString(), //// DebitAmountIncGST = x.PI_Tot_aft_Tax.ToString(), //// DebitAmountExcGST = 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, //// DebitDateDateTime = x.PI_Date ////}).Where(w => w.IsDeleted != true && w.ExcIncGST == ExcincTax).ToList(); //DebitList = (from i in entities.DebitNotes // 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 DebitNoteListEntity // { // 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, // DebitNo = i.PI_No, // DebitDate = i.PI_Date.ToString(), // DebitAmount = i.PI_Tot_aft_Tax.ToString(), // IsDeleted = i.IsDeleted, // CashChequeAmount = cbt.Amount, // CashChequeDate = cbt.Cash_Cheque_Date.ToString(), // CashChequeNo = cbt.Cash_Cheque_No // }).ToList(); DebitList = entities.Database.SqlQuery <DebitNoteListEntity>("DebitNoteList").ToList(); if (JsonData != null && JsonData != "[]") { DateTime startDate = 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); DebitList = DebitList.Where(x => x.DebitDateDateTime.Value.Year == year).ToList(); break; case "Quarter": switch (item.FieldValue) { case "1": DebitList = DebitList.Where(x => x.DebitDateDateTime.Value.Month == 1 || x.DebitDateDateTime.Value.Month == 2 || x.DebitDateDateTime.Value.Month == 3).ToList(); break; case "2": DebitList = DebitList.Where(x => x.DebitDateDateTime.Value.Month == 4 || x.DebitDateDateTime.Value.Month == 5 || x.DebitDateDateTime.Value.Month == 6).ToList(); break; case "3": DebitList = DebitList.Where(x => x.DebitDateDateTime.Value.Month == 7 || x.DebitDateDateTime.Value.Month == 8 || x.DebitDateDateTime.Value.Month == 9).ToList(); break; case "4": DebitList = DebitList.Where(x => x.DebitDateDateTime.Value.Month == 10 || x.DebitDateDateTime.Value.Month == 11 || x.DebitDateDateTime.Value.Month == 12).ToList(); break; } break; case "Month": var month = Convert.ToInt32(item.FieldValue); DebitList = DebitList.Where(x => x.DebitDateDateTime.Value.Month == month).ToList(); break; case "StartDate": //startDate = Convert.ToDateTime(item.FieldValue); startDate = DateTime.ParseExact(item.FieldValue, "MMM/dd/yyyy", CultureInfo.InvariantCulture); //DebitList = DebitList.Where(x => x.DebitDateDateTime ).ToList(); break; case "EndDate": // DateTime endDate = Convert.ToDateTime(item.FieldValue); DateTime endDate = DateTime.ParseExact(item.FieldValue, "MMM/dd/yyyy", CultureInfo.InvariantCulture); DebitList = DebitList.Where(x => x.DebitDateDateTime > startDate && x.DebitDateDateTime < endDate).ToList(); break; } } DebitListReturn = DebitList; } else { DebitListReturn = DebitList; } return(DebitListReturn); }