public JsonResult InvoiceGetInventoryItem(DatableOption option)
{
var jsonReturn = new JsonDataTable();
try
{
using (var db = new FTTxEntities())
{
var query = (from t1 in db.OITM
join t2 in db.OITB on t1.ItmsGrpCod equals t2.ItmsGrpCod
join t3 in db.ITM1.Where(i => i.PriceList == 1) on t1.ItemCode equals t3.ItemCode
where (string.IsNullOrEmpty(option.Query) ||
(t1.ItemCode.Contains(option.Query)) ||
(t1.ItemName.Contains(option.Query))
)
where t1.frozenFor == "N"
where t2.ItmsGrpNam == "Project"
select new
{
RowsNumber = 0,
ItemCode = t1.ItemCode,
ItemName = t1.ItemName ?? "",
UnitPrice = t1.PricingPrc,
});
if (option.order.Count > 0)
{
var sorting = int.Parse(option.order[0].FirstOrDefault(r => r.Key == "column").Value);
var dir = option.order[0].FirstOrDefault(r => r.Key == "dir").Value;
switch (sorting)
{
case 1: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.ItemCode) : query.OrderByDescending(r => r.ItemCode)); break;
case 2: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.ItemName) : query.OrderByDescending(r => r.ItemName)); break;
case 3: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.UnitPrice) : query.OrderByDescending(r => r.UnitPrice)); break;
default: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.ItemCode) : query.OrderByDescending(r => r.ItemCode)); break;
}
}
var data = query.Skip(option.start).Take(option.length).ToList();
var count = query.Count();
jsonReturn = new JsonDataTable {
status = true, message = "Ok", data = data, draw = option.draw, recordsTotal = count, recordsFiltered = count
};
}
}
catch (Exception ex)
{
jsonReturn = new JsonDataTable {
status = false, message = ex.Message, data = new string[0], draw = option.draw, recordsTotal = 0
};
Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message);
}
return(Json(jsonReturn));
}
void GenerateCreateTableScript(JsonDataTable dt, StringBuilder sbScript)
{
sbScript.AppendFormat("if not exists(select * from sysobjects where name = '{0}' and xtype = 'U')", dt.TableName);
sbScript.AppendLine();
sbScript.AppendFormat("\tcreate table [{0}](", dt.TableName);
sbScript.AppendLine();
foreach (var fieldData in dt.FieldMetaData)
{
sbScript.AppendFormat("\t\t[{0}] {1} NULL,", fieldData.FieldName, SqlDataType(fieldData.DataType));
sbScript.AppendLine();
}
sbScript.Length -= 3; //remove last comma and \r\n
sbScript.AppendLine();
sbScript.Append("\t)");
if (dt.Tables.Count > 0)
{
foreach (var table in dt.Tables)
{
sbScript.AppendLine();
sbScript.AppendFormat("------------ Table : \"{0}\" ------------", table.Key);
sbScript.AppendLine();
GenerateCreateTableScript(table.Value, sbScript);
}
}
sbScript.AppendLine();
}
void GenerateInsertScript(JsonDataTable dt, StringBuilder sbScript)
{
sbScript.AppendFormat("------------ Begin Table \"{0}\" Rows ({1}) ------------", dt.TableName, dt.Rows.Count);
sbScript.AppendLine();
foreach (var row in dt.Rows.DataRows)
{
sbScript.AppendFormat("insert into [{0}](", dt.TableName);
foreach (var fieldData in dt.FieldMetaData)
{
sbScript.AppendFormat("[{0}],", fieldData.FieldName);
}
sbScript.Length -= 1;
sbScript.Append(")").AppendLine();
sbScript.Append("\tvalues(");
sbScript.AppendFormat("{0},", row._Id);
sbScript.AppendFormat("{0},", row._ParentId);
//left join with matadata to make all fields available for insert
var flds = from m in dt.FieldMetaData
join r in row.Fields on m.FieldName equals r.FieldName into ps
from r in ps.DefaultIfEmpty()
where m.IsIdentityField == false
select new
{
FieldName = m.FieldName,
DataType = m.DataType,
DateCulture = m.DateCulture,
FieldValue = r?.FieldValue //use null propogation
};
foreach (var field in flds)
{
var val = DecideFieldValue(
new Field()
{
FieldName = field.FieldName, FieldValue = field.FieldValue
},
dt.FieldMetaData);
sbScript.AppendFormat("{1}{0}{1},", val, GetFieldDataTypeDelimit(
new Field()
{
FieldName = field.FieldName, FieldValue = field.FieldValue
},
dt.FieldMetaData));
}
sbScript.Length -= 1; //remove last comma
sbScript.Append(")").AppendLine();
}
sbScript.AppendFormat("------------ End Table \"{0}\" Rows ({1}) ------------", dt.TableName, dt.Rows.Count);
sbScript.AppendLine().AppendLine();
if (dt.Tables.Count > 0) //Iterate into child tables
{
foreach (var table in dt.Tables)
{
GenerateInsertScript(table.Value, sbScript);
}
}
}
public override StringBuilder ToSqlScript(JsonDataTable jsonDataTable)
{
this.jsonDataTable = jsonDataTable;
StringBuilder stringBuilder = new StringBuilder();
GenerateCreateTableScript(jsonDataTable, stringBuilder);
GenerateInsertScript(jsonDataTable, stringBuilder);
return(stringBuilder);
}
public ActionResult WvJRpzl5(int draw, int start, int length)
{
JsonDataTable dataTableData = new JsonDataTable();
string search = string.Empty;
int recordsFiltered = 0;
int recordsTotal = 0;
if (User != null)
{
int sortColumn = -1;
string sortDirection = "asc";
if (Request.QueryString["order[0][column]"] != null)
{
sortColumn = int.Parse(Request.QueryString["order[0][column]"]);
}
if (Request.QueryString["order[0][dir]"] != null)
{
sortDirection = Request.QueryString["order[0][dir]"];
}
var start_offset = start;
if (start_offset != 0)
{
start_offset = start / length;
}
if (Request.QueryString["search[value]"] != null)
{
search = Request.QueryString["search[value]"].Trim();
}
int flt = 0;
if (Request.QueryString["mose"] != null)
{
var s_mose = Peach.DecriptFromBase64(Request.QueryString["mose"].Trim());
if (s_mose != string.Empty)
{
flt = Convert.ToInt16((s_mose));
}
}
dataTableData.draw = draw;
dataTableData.data = new BLCuentaCorriente().Listar_toDataTables(start_offset, length, search, sortColumn, sortDirection, flt, ref recordsTotal);
recordsFiltered = dataTableData.data.Count();
dataTableData.recordsTotal = recordsTotal;
dataTableData.recordsFiltered = (search == string.Empty ? recordsTotal : recordsFiltered);
return(Json(dataTableData, JsonRequestBehavior.AllowGet));
}
else
{
return(RedirectToAction("Index", "Account", new { act = "timeout" }));
}
}
public ActionResult TXpDQ0V1(int draw, int start, int length)
{
JsonDataTable dataTableData = new JsonDataTable();
string search = string.Empty;
int recordsFiltered = 0;
int recordsTotal = 0;
if (User != null)
{
int sortColumn = -1;
string sortDirection = "asc";
if (Request.QueryString["order[0][column]"] != null)
{
sortColumn = int.Parse(Request.QueryString["order[0][column]"]);
}
if (Request.QueryString["order[0][dir]"] != null)
{
sortDirection = Request.QueryString["order[0][dir]"];
}
var start_offset = start;
if (start_offset != 0)
{
start_offset = start / length;
}
if (Request.QueryString["search[value]"] != null)
{
search = Request.QueryString["search[value]"].Trim();
}
dataTableData.draw = draw;
dataTableData.data = new BLMoneda().Listar_toDataTables(start_offset, length, search, sortColumn, sortDirection, ref recordsTotal);
recordsFiltered = dataTableData.data.Count();
dataTableData.recordsTotal = recordsTotal;
dataTableData.recordsFiltered = (search == string.Empty ? recordsTotal : recordsFiltered);
return(Json(dataTableData, JsonRequestBehavior.AllowGet));
}
else
{
return(RedirectToAction("Index", "Account", new { act = "timeout" }));
}
}
private void GenerateCreateTableScript(JsonDataTable dt, StringBuilder sbScript)
{
sbScript.AppendFormat("CREATE TABLE {0}(", dt.TableName.Trim().Replace(' ', '_'));
sbScript.AppendLine();
foreach (var fieldData in dt.FieldMetaData)
{
sbScript.AppendFormat("\t{0} {1} NULL,", DecideFieldName(fieldData), OracleDataType(fieldData.DataType));
sbScript.AppendLine();
}
sbScript.Length -= 3; //remove last comma and \r\n
sbScript.AppendLine();
sbScript.Append(")");
if (dt.Tables.Count > 0)
{
foreach (var table in dt.Tables)
{
sbScript.AppendLine();
sbScript.AppendFormat("------------ Table : \"{0}\" ------------", table.Key);
sbScript.AppendLine();
GenerateCreateTableScript(table.Value, sbScript);
}
}
sbScript.AppendLine();
}
void GenerateCreateTableScript(JsonDataTable dt, StringBuilder sbScript)
{
sbScript.AppendFormat("create table if not exists `{0}`(", dt.TableName);
sbScript.AppendLine();
foreach (var fieldData in dt.FieldMetaData)
{
sbScript.AppendFormat("\t`{0}` {1} NULL,", fieldData.FieldName, SqlDataType(fieldData.DataType));
sbScript.AppendLine();
}
sbScript.Length -= 3; //remove last comma and \r\n
sbScript.AppendLine();
sbScript.Append(");");
if (dt.Tables.Count > 0)
{
foreach (var table in dt.Tables)
{
sbScript.AppendLine();
sbScript.AppendFormat("-- ---------- Table : \"{0}\" ------------", table.Key);
sbScript.AppendLine();
GenerateCreateTableScript(table.Value, sbScript);
}
}
sbScript.AppendLine();
}
public abstract string ToSqlScript(JsonDataTable jsonDataTable, FilePath filePath);
public abstract StringBuilder ToSqlScript(JsonDataTable jsonDataTable);
/// <summary>
/// Send back a JsonDataTableObject as json with all the information that we need to populate datatable
/// </summary>
/// <param name="draw">Draw order. Client send a draw id in request to keep track of asyncron response</param>
/// <param name="start">Start from this item</param>
/// <param name="length">Take a list with "lenght" (if exists) objects inside.</param>
/// <returns>JsonDataTableObject</returns>
public JsonResult JsonTableFill(int draw, int start, int length)
{
try
{
const int TOTAL_ROWS = 999;
string category = category = Request.HttpContext.Request.Query["category"];
string search = search = Request.Query["search[value]"];
if (String.IsNullOrEmpty(category))
{
category = "";
}
if (String.IsNullOrEmpty(search))
{
search = "";
}
int sortColumn = -1;
string sortDirection = "asc";
if (length == -1)
{
length = TOTAL_ROWS;
}
// note: we only sort one column at a time
if (!String.IsNullOrEmpty(Request.Query["order[0][column]"]))
{
sortColumn = int.Parse(Request.Query["order[0][column]"]);
}
if (!String.IsNullOrEmpty(Request.Query["order[0][dir]"]))
{
sortDirection = Request.Query["order[0][dir]"];
}
IQueryable <Products> products;
//try to parse search to int
int searchInteger;
if (int.TryParse(search, out searchInteger))
{
//list of product that contain "search"
products = db.Products.Include(p => p.Category).Include(p => p.Supplier)
.Where(p => (p.ProductName.Contains(search) || p.UnitPrice == searchInteger ||
p.UnitsInStock == searchInteger || p.UnitsOnOrder == searchInteger ||
p.ReorderLevel == searchInteger || p.Discontinued.ToString().Contains(search)) &&
p.Category.CategoryName.Contains(category));
}
else
{
//if int cannot be taken
products = db.Products.Include(p => p.Category).Include(p => p.Supplier)
.Where(p => (p.ProductName.Contains(search) || p.Discontinued.ToString().Contains(search)) && p.Category.CategoryName.Contains(category));
}
//order list
switch (sortColumn)
{
case -1: //sort by first column
goto FirstColumn;
case 0: //first column
FirstColumn:
if (sortDirection == "asc")
{
products = products.OrderBy(x => x.ProductName);
}
else
{
products = products.OrderByDescending(x => x.ProductName);
}
break;
case 1: //second column
if (sortDirection == "asc")
{
products = products.OrderBy(x => x.UnitPrice);
}
else
{
products = products.OrderByDescending(x => x.UnitPrice);
}
break;
case 2: // and so on
if (sortDirection == "asc")
{
products = products.OrderBy(x => x.UnitsInStock);
}
else
{
products = products.OrderByDescending(x => x.UnitsInStock);
}
break;
case 3:
if (sortDirection == "asc")
{
products = products.OrderBy(x => x.UnitsOnOrder);
}
else
{
products = products.OrderByDescending(x => x.UnitsOnOrder);
}
break;
case 4:
if (sortDirection == "asc")
{
products = products.OrderBy(x => x.ReorderLevel);
}
else
{
products = products.OrderByDescending(x => x.ReorderLevel);
}
break;
case 5:
if (sortDirection == "asc")
{
products = products.OrderBy(x => x.Discontinued);
}
else
{
products = products.OrderByDescending(x => x.Discontinued);
}
break;
}
//objet that whill be sent to client
JsonDataTable dataTableData = new JsonDataTable()
{
draw = draw,
recordsTotal = db.Products.Count(),
data = products.Skip(start).Take(length).Select(x => new
{
ID = x.ProductID,
ProductName = x.ProductName,
Price = x.UnitPrice,
InStock = x.UnitsInStock,
OnOrders = x.UnitsOnOrder,
ReorderLevel = x.ReorderLevel,
Discontinued = x.Discontinued
}),
recordsFiltered = products.Count(), //need to be below data(ref recordsFiltered)
};
return(Json(dataTableData));
}
catch (Exception e)
{
//todo
//logger.Error(e.ToString());
return(Json(new JsonDataTable()
{
draw = draw,
recordsTotal = db.Products.Count(),
error = "Ceva nu a mers bine",
recordsFiltered = 0
}));
}
}
public JsonResult InvoiceListLoad(DatableOption option, FilterCustomer model)
{
var jsonReturn = new JsonDataTable();
try
{
using (var db = new FTTxEntities())
{
var endDate = DateTime.Now.Date;
if (model.EndDate != null)
{
endDate = model.EndDate.Value.AddDays(1);
}
var query = (from t1 in db.OINV
join t2 in db.OCRD on t1.CardCode equals t2.CardCode
where (model.ProvinceCode.Count == 0 || model.ProvinceCode.Contains(t1.CardCode.Substring(1, 2)))
where (model.StartDate == null || t1.CreateDate >= model.StartDate)
where (model.EndDate == null || t1.CreateDate < endDate)
where (model.Status.Count == 0 || model.Status.Contains(t1.DocStatus))
where t2.GroupCode == 103
where (string.IsNullOrEmpty(model.Query) ||
(t1.CardCode.Contains(model.Query)) ||
(t1.CardName.Contains(model.Query)) ||
(t1.DocNum.ToString().Contains(model.Query))
)
select new
{
DocNum = t1.DocNum,
CardCode = t1.CardCode ?? "",
CardName = t1.CardName ?? "",
DocTotal = t1.DocTotal,
CreateDate = t1.CreateDate,
PaidToDate = t1.PaidToDate,
CancelDate = t1.CancelDate,
DocDueDate = t1.DocDueDate,
VatSum = t1.VatSum,
BeforeVat = t1.DocTotal - t1.VatSum,
DocStatus = t1.DocStatus ?? ""
});
if (option.order.Count > 0)
{
var sorting = int.Parse(option.order[0].FirstOrDefault(r => r.Key == "column").Value);
var dir = option.order[0].FirstOrDefault(r => r.Key == "dir").Value;
switch (sorting)
{
case 1: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocNum) : query.OrderByDescending(r => r.DocNum)); break;
case 2: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardCode) : query.OrderByDescending(r => r.CardCode)); break;
case 3: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardName) : query.OrderByDescending(r => r.CardName)); break;
case 4: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocTotal) : query.OrderByDescending(r => r.DocTotal)); break;
case 5: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CreateDate) : query.OrderByDescending(r => r.CreateDate)); break;
case 6: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocDueDate) : query.OrderByDescending(r => r.DocDueDate)); break;
default: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocNum) : query.OrderByDescending(r => r.DocNum)); break;
}
}
var data = query.Skip(option.start).Take(option.length).ToList();
var count = query.Count();
jsonReturn = new JsonDataTable {
status = true, message = "Ok", data = data, draw = option.draw, recordsTotal = count, recordsFiltered = count
};
}
}
catch (Exception ex)
{
jsonReturn = new JsonDataTable {
status = false, message = ex.Message, data = new string[0], draw = option.draw, recordsTotal = 0
};
Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message);
}
return(Json(jsonReturn));
}
public override string ToSqlScript(JsonDataTable jsonDataTable, FilePath filePath)
{
throw new NotImplementedException();
}
public JsonResult CustomerListLoad(DatableOption option, FilterCustomer model)
{
var jsonReturn = new JsonDataTable();
try
{
using (var db = new FTTxEntities())
{
var endDate = DateTime.Now.Date;
if (model.EndDate != null)
{
endDate = model.EndDate.Value.AddDays(1);
}
var query = (from t1 in db.OCRD
where (model.ProvinceCode.Count == 0 || model.ProvinceCode.Contains(t1.CardCode.Substring(1, 2)))
where (model.StartDate == null || t1.CreateDate >= model.StartDate)
where (model.EndDate == null || t1.CreateDate < endDate)
where (model.Status.Count == 0 || model.Status.Contains(t1.frozenFor))
where t1.GroupCode == 103
where (string.IsNullOrEmpty(model.Query) ||
(t1.CardCode.Contains(model.Query)) ||
(t1.CardName.Contains(model.Query)) ||
(t1.Cellular.Contains(model.Query)) ||
(t1.E_Mail.Contains(model.Query))
)
select new
{
CardCode = t1.CardCode ?? "",
CardName = t1.CardName ?? "",
CardNo = t1.LicTradNum ?? "",
Cellular = t1.Cellular ?? "",
CreateDate = t1.CreateDate,
EMail = t1.E_Mail ?? "",
Address = t1.Address ?? "",
District = t1.Block ?? "",
Amphur = t1.County ?? "",
Province = t1.City ?? "",
ZipCode = t1.ZipCode ?? "",
Status = t1.frozenFor ?? ""
});
if (option.order.Count > 0)
{
var sorting = int.Parse(option.order[0].FirstOrDefault(r => r.Key == "column").Value);
var dir = option.order[0].FirstOrDefault(r => r.Key == "dir").Value;
switch (sorting)
{
case 1: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardName) : query.OrderByDescending(r => r.CardName)); break;
case 2: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.Cellular) : query.OrderByDescending(r => r.Cellular)); break;
case 3: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.EMail) : query.OrderByDescending(r => r.EMail)); break;
case 5: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CreateDate) : query.OrderByDescending(r => r.CreateDate)); break;
case 6: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.Status) : query.OrderByDescending(r => r.Status)); break;
default: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardCode) : query.OrderByDescending(r => r.CardCode)); break;
}
}
var data = query.Skip(option.start).Take(option.length).ToList();
var count = query.Count();
jsonReturn = new JsonDataTable {
status = true, message = "Ok", data = data, draw = option.draw, recordsTotal = count, recordsFiltered = count
};
}
}
catch (Exception ex)
{
jsonReturn = new JsonDataTable {
status = false, message = ex.Message, data = new string[0], draw = option.draw, recordsTotal = 0
};
Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message);
}
return(Json(jsonReturn));
}
/// <summary>
/// Function used to control the dashboard datatables from the server
/// </summary>
/// <param name="draw"></param>
/// <param name="start"></param>
/// <param name="length"></param>
/// <returns>A JSON filtered employees list.</returns>
// GET: Employees by Json
public JsonResult JsonTableFill(int draw, int start, int length)
{
const int TOTAL_ROWS = 999;
string search = Request.QueryString["search[value]"] ?? "";
int sortColumn = -1;
string sortDirection = "asc";
if (length == -1)
{
length = TOTAL_ROWS;
}
// note: we only sort one column at a time
if (Request.QueryString["order[0][column]"] != null)
{
sortColumn = int.Parse(Request.QueryString["order[0][column]"]);
}
if (Request.QueryString["order[0][dir]"] != null)
{
sortDirection = Request.QueryString["order[0][dir]"];
}
//list of product that contain "search"
var list = db.Employees.
Where
(p =>
p.LastName.Contains(search) ||
p.FirstName.Contains(search) ||
p.City.Contains(search) ||
p.Title.Contains(search) ||
p.Country.Contains(search) ||
p.HomePhone.Contains(search)
);
//order list
switch (sortColumn)
{
case -1: //sort by first column
goto FirstColumn;
case 0: //first column
FirstColumn:
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.LastName);
}
else
{
list = list.OrderByDescending(x => x.LastName);
}
break;
case 1: //second column
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.FirstName);
}
else
{
list = list.OrderByDescending(x => x.FirstName);
}
break;
case 3: // and so on
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.City);
}
else
{
list = list.OrderByDescending(x => x.City);
}
break;
case 2:
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.Title);
}
else
{
list = list.OrderByDescending(x => x.Title);
}
break;
case 5:
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.HomePhone);
}
else
{
list = list.OrderByDescending(x => x.HomePhone);
}
break;
case 4:
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.Country);
}
else
{
list = list.OrderByDescending(x => x.Country);
}
break;
}
//object that whill be sent to client
JsonDataTable dataTableData = new JsonDataTable()
{
draw = draw,
recordsTotal = db.Employees.Count(),
data = list.Skip(start).Take(length).Select(x => new
{
ID = x.EmployeeID,
LastName = x.LastName,
FirstName = x.FirstName,
Title = x.Title,
City = x.City,
Country = x.Country,
HomePhone = x.HomePhone
}),
recordsFiltered = list.Count(), //need to be below data(ref recordsFiltered)
};
return(Json(dataTableData, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// Send back a JsonDataTableObject as json with all the information that we need to populate datatable
/// </summary>
/// <param name="draw">Draw order. Client send a draw id in request to keep track of asyncron response</param>
/// <param name="start">Start from this item</param>
/// <param name="length">Take a list with "lenght" (if exists) objects inside.</param>
/// <returns>JsonDataTableObject</returns>
public JsonResult JsonTableFill(int draw, int start, int length, [FromQuery] string json = "")
{
const int TOTAL_ROWS = 999;
//init list of products in shopcart
IQueryable <ProductShopCartDetailed> list;
if (User.Identity.IsAuthenticated)
{
list = from s in db.ShopCart
join p in db.Products on s.ProductID equals p.ProductID
join c in db.Categories on p.CategoryID equals c.CategoryID
where s.UserName == User.Identity.Name
select new ProductShopCartDetailed
{
Category = c.CategoryName,
ID = s.ProductID,
ProductName = p.ProductName,
Quantity = s.Quantity,
UnitPrice = p.UnitPrice ?? 999999
};
}
else
{
if (json != "")
{
list = JsonConvert.DeserializeObject <List <ProductShopCartDetailed> >(json).AsQueryable();
}
else
{
list = new List <ProductShopCartDetailed>().AsQueryable();
}
}
int sortColumn = -1;
string sortDirection = "asc";
if (length == -1)
{
length = TOTAL_ROWS;
}
// note: we only sort one column at a time
#warning "This is unnecessarily complicated. Need to figure out datatable sorting."
//iSortCol_0
//if (Request.QueryString["order[0][column]"] != null)
//{
// sortColumn = int.Parse(Request.QueryString["order[0][column]"]);
//}
//if (Request.QueryString["order[0][dir]"] != null)
//{
// sortDirection = Request.QueryString["order[0][dir]"];
//}
//list of product that contain "search"
//order list
switch (sortColumn)
{
case -1: //sort by first column
goto FirstColumn;
case 0: //first column
FirstColumn:
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.ProductName);
}
else
{
list = list.OrderByDescending(x => x.ProductName);
}
break;
case 1: //second column
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.Quantity);
}
else
{
list = list.OrderByDescending(x => x.Quantity);
}
break;
case 2: // and so on
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.UnitPrice);
}
else
{
list = list.OrderByDescending(x => x.UnitPrice);
}
break;
case 3:
if (sortDirection == "asc")
{
list = list.OrderBy(x => (x.UnitPrice * x.Quantity));
}
else
{
list = list.OrderByDescending(x => (x.UnitPrice * x.Quantity));
}
break;
}
//object that whill be sent to client
JsonDataTable dataTableData = new JsonDataTable()
{
draw = draw,
recordsTotal = db.Products.Count(),
data = list.Skip(start).Take(length).Select(p => new
{
Category = p.Category,
ID = p.ID,
ProductName = p.ProductName,
Quantity = p.Quantity,
UnitPrice = (int)p.UnitPrice,
}).AsQueryable(),
recordsFiltered = list.Count(), //need to be below data(ref recordsFiltered)
};
//return Json(dataTableData, JsonRequestBehavior.AllowGet);
return(Json(dataTableData));
}
/// <summary>
/// Function used to control the dashboard datatables from the server
/// </summary>
/// <param name="draw"></param>
/// <param name="start"></param>
/// <param name="length"></param>
/// <returns>A JSON filtered customer list.</returns>
public JsonResult JsonTableFill(int draw, int start, int length)
{
try
{
const int totalRows = 999;
string search = "";
search = Request.QueryString["search[value]"] ?? "";
int sortColumn = -1;
string sortDirection = "asc";
if (length == -1)
{
length = totalRows;
}
// note: we only sort one column at a time
if (Request.QueryString["order[0][column]"] != null)
{
sortColumn = int.Parse(Request.QueryString["order[0][column]"]);
}
if (Request.QueryString["order[0][dir]"] != null)
{
sortDirection = Request.QueryString["order[0][dir]"];
}
//list of customers that contain "search"
var list = db.Customers.Where(x => x.CompanyName.Contains(search) ||
x.ContactName.Contains(search) ||
x.ContactTitle.Contains(search) ||
x.City.Contains(search) ||
x.Country.Contains(search) ||
x.Phone.Contains(search));
//order list
switch (sortColumn)
{
case -1: //sort by first column
goto FirstColumn;
case 0: //first column
FirstColumn:
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.CompanyName);
}
else
{
list = list.OrderByDescending(x => x.CompanyName);
}
break;
case 1: //second column
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.ContactName);
}
else
{
list = list.OrderByDescending(x => x.ContactName);
}
break;
case 2: // and so on
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.ContactTitle);
}
else
{
list = list.OrderByDescending(x => x.ContactTitle);
}
break;
case 3:
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.City);
}
else
{
list = list.OrderByDescending(x => x.City);
}
break;
case 4:
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.Country);
}
else
{
list = list.OrderByDescending(x => x.Country);
}
break;
case 5:
if (sortDirection == "asc")
{
list = list.OrderBy(x => x.Phone);
}
else
{
list = list.OrderByDescending(x => x.Phone);
}
break;
}
//objet that whill be sent to client
JsonDataTable dataTableData = new JsonDataTable()
{
draw = draw,
recordsTotal = db.Customers.Count(),
data = list.Skip(start).Take(length).Select(x => new
{
ID = x.CustomerID,
CompanyName = x.CompanyName,
ContactName = x.ContactName,
ContactTitle = x.ContactTitle,
City = x.City,
Country = x.Country,
Phone = x.Phone
}),
recordsFiltered = list.Count(), //need to be below data(ref recordsFiltered)
};
return(Json(dataTableData, JsonRequestBehavior.AllowGet));
}
catch (Exception e)
{
logger.Error(e.ToString());
return(Json(new JsonDataTable()
{
draw = draw,
recordsTotal = db.Customers.Count(),
error = "Ceva nu a mers bine",
recordsFiltered = 0
}, JsonRequestBehavior.AllowGet));
}
}
/// <summary>
/// Send back a JsonDataTableObject as json with all the information that we need to populate datatable
/// </summary>
/// <param name="draw">Draw order. Client send a draw id in request to keep track of asyncron response</param>
/// <param name="start">Start from this item</param>
/// <param name="length">Take a list with "lenght" (if exists) objects inside.</param>
/// <returns>JsonDataTableObject</returns>
public JsonResult JsonTableFill(int draw, int start, int length)
{
const int TOTAL_ROWS = 999;
string search = "";
try
{
search = Request.QueryString["search[value]"] ?? "";
}
catch (Exception exception)
{
logger.Error(exception.ToString());
}
int sortColumn = -1;
string sortDirection = "asc";
if (length == -1)
{
length = TOTAL_ROWS;
}
// note: we only sort one column at a time
try
{
if (Request.QueryString["order[0][column]"] != null)
{
sortColumn = int.Parse(Request.QueryString["order[0][column]"]);
}
}
catch (Exception exception)
{
logger.Error(exception.ToString());
}
try
{
if (Request.QueryString["order[0][dir]"] != null)
{
sortDirection = Request.QueryString["order[0][dir]"];
}
}
catch (Exception exception)
{
logger.Error(exception.ToString());
}
//list of product that contain "search"
var suppliersInfo = db.Suppliers.OrderBy(x => x.SupplierID).Where(s => s.CompanyName.Contains(search) || s.ContactName.Contains(search) ||
s.ContactTitle.Contains(search) || s.Address.Contains(search) || s.City.Contains(search) || s.Country.Contains(search) || s.Phone.Contains(search));
//order list
switch (sortColumn)
{
case -1: //sort by first column
goto FirstColumn;
case 0: //first column
FirstColumn:
if (sortDirection == "asc")
{
suppliersInfo = suppliersInfo.OrderBy(x => x.CompanyName);
}
else
{
suppliersInfo = suppliersInfo.OrderByDescending(x => x.CompanyName);
}
break;
case 1: //second column
if (sortDirection == "asc")
{
suppliersInfo = suppliersInfo.OrderBy(x => x.ContactName);
}
else
{
suppliersInfo = suppliersInfo.OrderByDescending(x => x.ContactName);
}
break;
case 2: // and so on
if (sortDirection == "asc")
{
suppliersInfo = suppliersInfo.OrderBy(x => x.ContactTitle);
}
else
{
suppliersInfo = suppliersInfo.OrderByDescending(x => x.ContactTitle);
}
break;
case 3:
if (sortDirection == "asc")
{
suppliersInfo = suppliersInfo.OrderBy(x => x.Address);
}
else
{
suppliersInfo = suppliersInfo.OrderByDescending(x => x.Address);
}
break;
case 4:
if (sortDirection == "asc")
{
suppliersInfo = suppliersInfo.OrderBy(x => x.City);
}
else
{
suppliersInfo = suppliersInfo.OrderByDescending(x => x.City);
}
break;
case 5:
if (sortDirection == "asc")
{
suppliersInfo = suppliersInfo.OrderBy(x => x.Country);
}
else
{
suppliersInfo = suppliersInfo.OrderByDescending(x => x.Country);
}
break;
case 6:
if (sortDirection == "asc")
{
suppliersInfo = suppliersInfo.OrderBy(x => x.Phone);
}
else
{
suppliersInfo = suppliersInfo.OrderByDescending(x => x.Phone);
}
break;
}
//objet that whill be sent to client
JsonDataTable dataTableData = new JsonDataTable()
{
draw = draw,
recordsTotal = db.Suppliers.Count(),
data = suppliersInfo.Skip(start).Take(length).Select(x => new
{
SupplierID = x.SupplierID,
CompanyName = x.CompanyName,
ContactName = x.ContactName,
ContactTitle = x.ContactTitle,
Address = x.Address,
City = x.City,
Country = x.Country,
Phone = x.Phone
}),
recordsFiltered = suppliersInfo.Count(), //need to be below data(ref recordsFiltered)
};
return(Json(dataTableData, JsonRequestBehavior.AllowGet));
}
