using DevExtreme.AspNet.Data; using DevExtreme.AspNet.Mvc; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.ModelBinding; using Microsoft.EntityFrameworkCore; using Newtonsoft.Json; using System; using System.Collections; using System.Collections.Generic; using System.Globalization; using System.Linq; using System.Threading.Tasks; using CertAuth.Data; using CertAuth.Domain; using Microsoft.Data.SqlClient; namespace CertAuthAdmin.Controllers { [Route("api/[controller]/[action]")] public class CollectorsController : Controller { private CertAuthContext _context; public IEnumerable theList; public CollectorsController(CertAuthContext context) { _context = context; } [HttpGet] public async Task Get(DataSourceLoadOptions loadOptions) { var collectors = _context.Collectors.Select(i => new { i.Id, i.CollectorFirstName, i.CollectorLastName, i.CollectorAddress1, i.CollectorAddress2, i.CollectorCity, i.CollectorPostalCode, i.CollectorProvince, i.CollectorCountry, i.EmailAddress, i.CollectorPhone, i.CollectorCellPhone, i.DisplayName, i.DisplayEmail, i.Status, i.TimeStamp, i.collectorDisplayName }); // If you work with a large amount of data, consider specifying the PaginateViaPrimaryKey and PrimaryKey properties. // In this case, keys and data are loaded in separate queries. This can make the SQL execution plan more efficient. // Refer to the topic https://github.com/DevExpress/DevExtreme.AspNet.Data/issues/336. // loadOptions.PrimaryKey = new[] { "Id" }; // loadOptions.PaginateViaPrimaryKey = true; return Json(await DataSourceLoader.LoadAsync(collectors, loadOptions)); } [HttpGet] public IEnumerable GetAll() { string connStr = Globals.connStr; List Datalst; IEnumerable Datalist; Datalst = new List(); using (SqlConnection con = new SqlConnection(connStr)) { SqlCommand cmd = new SqlCommand("Collectors_GetAll", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { CollectorsLst rec = new CollectorsLst(); rec.Id = (int)rdr["Id"]; rec.UserKey = rdr["UserKey"].ToString(); rec.CollectorFirstName = rdr["CollectorFirstName"].ToString(); rec.CollectorLastName = rdr["CollectorLastName"].ToString(); rec.CollectorAddress1 = rdr["CollectorAddress1"].ToString(); rec.CollectorAddress2 = rdr["CollectorAddress2"].ToString(); rec.CollectorCity = rdr["CollectorCity"].ToString(); rec.CollectorProvince = rdr["CollectorProvince"].ToString(); rec.CollectorCountry = rdr["CollectorCountry"].ToString(); rec.CollectorPostalCode = rdr["CollectorPostalCode"].ToString(); rec.EmailAddress = rdr["EmailAddress"].ToString(); rec.CollectorPhone = rdr["CollectorPhone"].ToString(); rec.CollectorCellPhone = rdr["CollectorCellPhone"].ToString(); rec.DisplayName = (bool)rdr["DisplayName"]; rec.DisplayEmail = (bool)rdr["DisplayEmail"]; rec.Status = rdr["Status"].ToString(); rec.TimeStamp = (DateTime)rdr["TimeStamp"]; rec.collectorDisplayName = rdr["collectorDisplayName"].ToString(); Datalst.Add(rec); } con.Close(); } Datalist = Datalst; return Datalist; } [HttpGet] public IEnumerable GetItem(int id) { string connStr = Globals.connStr; List lst; lst = new List(); //IEnumerable theList; using (SqlConnection con = new SqlConnection(connStr)) { SqlCommand cmd = new SqlCommand("Collectors_ItemList_GetByCollectorID", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CollectorID", id); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { CollectorItemsList rec = new CollectorItemsList(); rec.CollectorID = (int)rdr["CollectorID"]; rec.CollectorFirstName = rdr["CollectorFirstName"].ToString(); rec.CollectorLastName = rdr["CollectorLastName"].ToString(); rec.collectorDisplayName = rdr["collectorDisplayName"].ToString(); rec.ItemName = rdr["ItemName"].ToString(); rec.ItemType = rdr["ItemType"].ToString(); rec.ItemTypeDesc = rdr["ItemTypeDesc"].ToString(); rec.ItemSubType = rdr["ItemSubType"].ToString(); rec.ItemSubTypeDesc = rdr["ItemSubTypeDesc"].ToString(); rec.Category = rdr["Category"].ToString(); rec.CategoryDesc = rdr["CategoryDesc"].ToString(); rec.SubCategory = rdr["SubCategory"].ToString(); rec.SubCategoryDesc = rdr["SubCategoryDesc"].ToString(); lst.Add(rec); } con.Close(); } theList = lst; return theList; } [HttpPost] public async Task Post(string values) { var model = new Collectors(); var valuesDict = JsonConvert.DeserializeObject(values); PopulateModel(model, valuesDict); if(!TryValidateModel(model)) return BadRequest(GetFullErrorMessage(ModelState)); var result = _context.Collectors.Add(model); await _context.SaveChangesAsync(); return Json(result.Entity.Id); } [HttpPut] public async Task Put(int key, string values) { var model = await _context.Collectors.FirstOrDefaultAsync(item => item.Id == key); if(model == null) return StatusCode(409, "Collectors not found"); var valuesDict = JsonConvert.DeserializeObject(values); PopulateModel(model, valuesDict); if(!TryValidateModel(model)) return BadRequest(GetFullErrorMessage(ModelState)); await _context.SaveChangesAsync(); return Ok(); } [HttpDelete] public async Task Delete(int key) { var model = await _context.Collectors.FirstOrDefaultAsync(item => item.Id == key); _context.Collectors.Remove(model); await _context.SaveChangesAsync(); } private void PopulateModel(Collectors model, IDictionary values) { string ID = nameof(Collectors.Id); string COLLECTOR_FIRST_NAME = nameof(Collectors.CollectorFirstName); string COLLECTOR_LAST_NAME = nameof(Collectors.CollectorLastName); string COLLECTOR_ADDRESS1 = nameof(Collectors.CollectorAddress1); string COLLECTOR_ADDRESS2 = nameof(Collectors.CollectorAddress2); string COLLECTOR_CITY = nameof(Collectors.CollectorCity); string COLLECTOR_POSTAL_CODE = nameof(Collectors.CollectorPostalCode); string COLLECTOR_PROVINCE = nameof(Collectors.CollectorProvince); string COLLECTOR_COUNTRY = nameof(Collectors.CollectorCountry); string EMAIL_ADDRESS = nameof(Collectors.EmailAddress); string COLLECTOR_PHONE = nameof(Collectors.CollectorPhone); string COLLECTOR_CELL_PHONE = nameof(Collectors.CollectorCellPhone); string DISPLAY_NAME = nameof(Collectors.DisplayName); string DISPLAY_EMAIL = nameof(Collectors.DisplayEmail); string STATUS = nameof(Collectors.Status); string TIME_STAMP = nameof(Collectors.TimeStamp); string COLLECTORDISPLAYNAME = nameof(Collectors.collectorDisplayName); if(values.Contains(ID)) { model.Id = Convert.ToInt32(values[ID]); } if(values.Contains(COLLECTOR_FIRST_NAME)) { model.CollectorFirstName = Convert.ToString(values[COLLECTOR_FIRST_NAME]); } if(values.Contains(COLLECTOR_LAST_NAME)) { model.CollectorLastName = Convert.ToString(values[COLLECTOR_LAST_NAME]); } if(values.Contains(COLLECTOR_ADDRESS1)) { model.CollectorAddress1 = Convert.ToString(values[COLLECTOR_ADDRESS1]); } if(values.Contains(COLLECTOR_ADDRESS2)) { model.CollectorAddress2 = Convert.ToString(values[COLLECTOR_ADDRESS2]); } if(values.Contains(COLLECTOR_CITY)) { model.CollectorCity = Convert.ToString(values[COLLECTOR_CITY]); } if(values.Contains(COLLECTOR_POSTAL_CODE)) { model.CollectorPostalCode = Convert.ToString(values[COLLECTOR_POSTAL_CODE]); } if(values.Contains(COLLECTOR_PROVINCE)) { model.CollectorProvince = Convert.ToString(values[COLLECTOR_PROVINCE]); } if(values.Contains(COLLECTOR_COUNTRY)) { model.CollectorCountry = Convert.ToString(values[COLLECTOR_COUNTRY]); } if(values.Contains(EMAIL_ADDRESS)) { model.EmailAddress = Convert.ToString(values[EMAIL_ADDRESS]); } if(values.Contains(COLLECTOR_PHONE)) { model.CollectorPhone = Convert.ToString(values[COLLECTOR_PHONE]); } if(values.Contains(COLLECTOR_CELL_PHONE)) { model.CollectorCellPhone = Convert.ToString(values[COLLECTOR_CELL_PHONE]); } if(values.Contains(DISPLAY_NAME)) { model.DisplayName = Convert.ToBoolean(values[DISPLAY_NAME]); } if(values.Contains(DISPLAY_EMAIL)) { model.DisplayEmail = Convert.ToBoolean(values[DISPLAY_EMAIL]); } if(values.Contains(STATUS)) { model.Status = Convert.ToString(values[STATUS]); } if(values.Contains(TIME_STAMP)) { model.TimeStamp = Convert.ToDateTime(values[TIME_STAMP]); } if (values.Contains(COLLECTORDISPLAYNAME)) { model.collectorDisplayName = Convert.ToString(values[COLLECTORDISPLAYNAME]); } } private string GetFullErrorMessage(ModelStateDictionary modelState) { var messages = new List(); foreach(var entry in modelState) { foreach(var error in entry.Value.Errors) messages.Add(error.ErrorMessage); } return String.Join(" ", messages); } } }