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)
{
//-------------<
Add_Date_To_Products() >-------------
//< get items >
String[] arrParameter_SetIDs =
param_SetItems.setids.Split(";");
String[] arrParameter_SetMonths =
param_SetItems.setmonths.Split(";");
int intSetDay = Convert.ToInt32(
param_SetItems.setday) ;
//</ get items >
//< convert >
List<int> setProductIDs = new List<int>();
foreach (string sID in arrParameter_SetIDs)
{
setProductIDs.Add(Convert.ToInt32(sID));
}
//*add month target-days
List<DateTime> setMonths = new List<DateTime>();
foreach (string sMonth in arrParameter_SetMonths)
{
DateTime dtMonth =
Convert.ToDateTime(sMonth);
DateTime dtSet=
dtMonth.AddDays(intSetDay-1);
//#test
//if (dtMonth >
DateTime.Parse("2022-02-01"))
{
//*must be at the same month
ex.:2021-02-30
if (dtSet.Month == dtMonth.Month) {
setMonths.Add(dtSet);
}
}
}
//</ convert >
//----< @Loop: Products >----
foreach ( int IDProduct in setProductIDs) {
foreach (DateTime dtMonthDay in setMonths)
{
//< add >
ProductDateModel
productDate = _dbContext.tbl_ProductDates.FirstOrDefault(x => x.IDProduct
== IDProduct && x.Date_Product == dtMonthDay);
if (productDate == null) {
productDate = new ProductDateModel();
productDate.IDProduct
= IDProduct;
productDate.Date_Product = dtMonthDay;
_dbContext.tbl_ProductDates.Add(productDate);
//</ add >
//----< Save Data
>----
try
{
//_dbContext.Update(productDate);
_dbContext.SaveChanges(true);
}
catch (Exception ex)
{
//return Content("Insert
Date Error");
Console.WriteLine(ex.Message);
}
//----</ Save Data >----
}
}
}
//----</ @Loop: Products >----
//----</ INSERT NEW DAYS >-----
return Ok();
//-------------</
Add_Date_To_Products() >-------------
}
}
}
|