using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MyApp.Models;
using DevExtreme.AspNet.Data;
using DevExtreme.AspNet.Mvc;
using MyApp.Models.DBContext;
using MvcContrib.UI.DataList;
using System.Data;
using Newtonsoft.Json;
using Microsoft.EntityFrameworkCore;
namespace MyApp.Controllers.Api
{
[Route("api/[controller]")]
[ApiController]
public class apiDemoController : ControllerBase
{
#region Controller Init
private readonly ApplicationDbContext _dbContext;
public apiDemoController(ApplicationDbContext
dbContext)
{
//----< Init: Controller >----
_dbContext = dbContext;
//----</ Init: Controller >----
}
#endregion
[HttpGet("getGridData")]
public object getGridData(DataSourceLoadOptions
loadOptions, string filterGridParameters)
{
//-------------< getDataGrid()
>-------------
var dsProducts = from tblProducts in _dbContext.tbl_Products
orderby tblProducts.Product ascending
select tblProducts;
var dsProductDates = from tblProductDates in _dbContext.tbl_ProductDates
orderby tblProductDates.IDProduct,
tblProductDates.Date_Product ascending
select tblProductDates;
//--< Get Linq.Query >--
DataTable tblMonth = new DataTable();
//< define Columns >
tblMonth.Columns.Add("IDProduct", typeof(int));
tblMonth.Columns.Add("Product", typeof(String));
DateTime dtFrom = DateTime.Today.AddDays(-
DateTime.Today.Day +1 );
DateTime dtTo=
DateTime.Today.AddMonths(6);
DateTime dtLoop = dtFrom;
while (dtLoop <= dtTo)
{
tblMonth.Columns.Add(dtLoop.ToString("yyyy-MM-01"), typeof(String));
dtLoop = dtLoop.AddMonths(1);
}
//</ define Columns >
//----< @Loop:Products >----
foreach (var Product in dsProducts)
{
//----<
[ Product ]----
DataRow row = tblMonth.Rows.Add();
row["IDProduct"] = Product.IDProduct;
row["Product"] = Product.Product;
//*SubQuery:
Current Dates of Row Product
var qDatesByProduct =
dsProductDates.Where(p => p.IDProduct == Product.IDProduct &&
p.Date_Product>= dtFrom && p.Date_Product<=dtTo)
.Select(d=>new {
dtMonth=d.Date_Product}) ;
//----<
@Loop:Products >----
foreach (var ProductDate in qDatesByProduct)
{
//----<
[ Product ]----
string sMonth01 =
ProductDate.dtMonth.ToString("yyyy-MM-01");
if (row[sMonth01] is System.DBNull) {
row[sMonth01] =
ProductDate.dtMonth.ToString("yyyy-MM-dd");
}
else
{
row[sMonth01] = row[sMonth01] + ", " +
ProductDate.dtMonth.ToString("yyyy-MM-dd");
}
//----</
[ Product ]----
}
//----</
@Loop:Products >----
//----</
[ Product ]----
}
//----</
@Loop:Products >----
List<DataRow> dataList =
tblMonth.Select().ToList();
//----< fill Data_to_View >----
//---< @Loop: Rows >---
//----</ fill Data_to_View >----
return
JsonConvert.SerializeObject(tblMonth) ;
//-------------</ getDataGrid()
>-------------
}
[HttpPut("UpdateGridCell")]
public ActionResult
UpdateGridCell([FromForm] Dictionary<string,string> cell)
{
//-------------< UpdateGridCell()
>-------------
var sKey = cell["key"]; //ID=A
var sCell_Column_Value = cell["values"]; //{"..":".."}
sCell_Column_Value =
sCell_Column_Value.Substring(1,sCell_Column_Value.Length-2); //"..":".."
string[] arrCell_Column_Value =
sCell_Column_Value.Split(':');
string sColumn_Name =
arrCell_Column_Value[0]; //"col: 2021-10-01"
string sCell_Value =
arrCell_Column_Value[1]; //"val:
2021-10-10;2021-10-20;"
sCell_Value = sCell_Value.Replace("\"","");
DateTime dtCell =
DateTime.Parse(sCell_Value);
int IDProduct = Convert.ToInt32(sKey);
ProductDateModel productDate =
_dbContext.tbl_ProductDates.SingleOrDefault(p => p.IDProduct == IDProduct
&& p.Date_Product==dtCell);
if(productDate == null)
{
try
{
//< new
ProductDate >
ProductDateModel newProductDate = new ProductDateModel();
newProductDate.Date_Product = dtCell;
newProductDate.IDProduct = IDProduct;
_dbContext.tbl_ProductDates.Add(newProductDate); //*add 1 record
//</
new ProductDate >
_dbContext.SaveChanges(); //*update all in table
//----<
Save Data >----
try
{
//_dbContext.Update(newProductDate);
_dbContext.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
return Content("Update Error");
}
//----</
Save Data >----
//else
//{
// //< Add on Server >
// _dbContext.tbl_Notes.Add(note);
// _dbContext.SaveChanges();
// IDNote = note.IDNote;
// //</ Add on Server >
//}
//await
_dbContext.SaveChangesAsync(true);
}
catch (DbUpdateConcurrencyException
ex)
{
return Content("Error in saving
ID:" +
IDProduct + "
Date:" + productDate.Date_Product + ".. " + ex.Message);
}
}
return Ok();
//-------------</ UpdateGridCell()
>-------------
}
//*Upload
Data
public class Upload_Item
{
public string setids { get; set; }
public string setmonths { get; set; }
public string setday { get; set; }
}
[HttpPost("Add_Date_To_Products")]
public ActionResult
Add_Date_To_Products([FromBody] Upload_Item param_SetItems)
{
//-------------< UpdateGridCell()
>-------------
string sSQL_Result = "";
//< get items >
String[] arrParameter_SetIDs =
param_SetItems.setids.Split(";");
String[] arrParameter_SetMonths =
param_SetItems.setmonths.Split(";");
var SetDay = param_SetItems.setday;
//</ get items >
//< convert >
List<int> setProductIDs = new List<int>();
foreach (string sID in arrParameter_SetIDs)
{
setProductIDs.Add(Convert.ToInt32(sID));
}
List<DateTime> setMonths = new List<DateTime>();
foreach (string sMonth in arrParameter_SetMonths)
{
setMonths.Add(Convert.ToDateTime(sMonth));
}
//</ convert >
//--< connect_db >--
var query_ProductDates = (from tblProductDates in _dbContext.tbl_ProductDates
select tblProductDates);
//--</ connect_db >--
//-< SubQuery.SelectIDs >-
foreach (int ProductID in setProductIDs)
{
query_ProductDates=query_ProductDates.Where(q => q.IDProduct ==
ProductID);
}
sSQL_Result =
query_ProductDates.ToQueryString(); //SQL string
//-</ SubQuery.SelectIDs >-
foreach(var row in query_ProductDates)
{
sSQL_Result = sSQL_Result + "\n" + row.IDProduct + " " + row.Date_Product;
}
return Ok(sSQL_Result);
//-------------</ UpdateGridCell()
>-------------
}
}
}
|