/// <summary>
/// Create a list of ItemBuilder objects and export them in a Excel file
/// </summary>
/// <param name="itemName">Item name</param>
/// <returns>Result of action</returns>
public ActionResult ExportHuman(string itemName, string instanceName, string connectionString)
{
string source = string.Format(CultureInfo.InvariantCulture, @"Excel:List({0})", itemName);
this.itemBuilder = new ItemBuilder(itemName, instanceName);
var res = ActionResult.NoAction;
try
{
this.workBook = new XSSFWorkbook();
this.sheet = (XSSFSheet)this.workBook.CreateSheet(this.itemBuilder.Definition.Layout.LabelPlural);
// HEADER
int countCells = 0;
this.CreateHeader();
ReadOnlyCollection <ItemBuilder> list = Read.Active(this.itemBuilder.Definition, instanceName);
int countRows = 1;
foreach (ItemBuilder item in list)
{
if (item == null)
{
continue;
}
ToolsXlsx.CreateRow(this.sheet, countRows);
countCells = 0;
foreach (ItemField field in this.itemBuilder.HumanFields)
{
this.sheet.GetRow(countRows).CreateCell(countCells);
if (item.ContainsKey(field.Name) && item[field.Name] != null)
{
if (item[field.Name].GetType().Name.ToUpperInvariant() == "JOBJECT")
{
string data = item[field.Name].ToString();
JObject x = item[field.Name] as JObject;
if (x["Description"] != null)
{
data = x["Description"].ToString();
}
this.sheet.GetRow(countRows).GetCell(countCells).SetCellValue(data);
}
else
{
string fieldName = string.Empty;
if (item.Definition.Fields.Any(f => f.Name == field.Name))
{
fieldName = field.Name;
}
else
{
if (item.Definition.Fields.Any(f => f.Name == field.Name + "Id"))
{
fieldName = string.Format(CultureInfo.InvariantCulture, "{0}Id", field.Name);
}
fieldName = field.Name;
}
if (!string.IsNullOrEmpty(fieldName))
{
ItemField fieldDefinition = item.Definition.Fields.Where(f => f.Name == fieldName).First();
this.SetCellValue(countRows, countCells, item[fieldName], fieldDefinition.DataType);
}
}
}
countCells++;
}
countRows++;
}
string path = HttpContext.Current.Request.PhysicalApplicationPath;
if (!path.EndsWith("\\", StringComparison.OrdinalIgnoreCase))
{
path = string.Format(CultureInfo.GetCultureInfo("en-us"), @"{0}", path);
}
path = string.Format(CultureInfo.GetCultureInfo("en-us"), @"{0}\Temp\{1}.{2}", path, this.itemBuilder.Definition.Layout.LabelPlural, ConstantValue.Excel2007Extension);
string link = string.Format(CultureInfo.GetCultureInfo("en-us"), @"/Temp/{0}.{1}", this.itemBuilder.Definition.Layout.LabelPlural, ConstantValue.Excel2007Extension);
using (FileStream fs = new FileStream(path, FileMode.Create))
{
this.workBook.Write(fs);
}
res.SetSuccess(link);
}
catch (IOException ex)
{
res.SetFail(ex);
ExceptionManager.Trace(ex, source);
}
catch (NullReferenceException ex)
{
res.SetFail(ex);
ExceptionManager.Trace(ex, source);
}
catch (NotSupportedException ex)
{
res.SetFail(ex);
ExceptionManager.Trace(ex, source);
}
return(res);
}
public static ReadOnlyCollection <ItemBuilder> GetActive(string itemName, string instanceName)
{
return(Read.Active(ItemDefinition.Load(itemName, instanceName), instanceName));
}
/// <summary>Continues PageLoad execution if session is alive</summary>
private void Go()
{
var d0 = DateTime.Now;
string itemName = this.Request.QueryString["ItemName"];
string instanceName = this.Request.QueryString["InstanceName"];
bool external = false;
var instance = CustomerFramework.Load(instanceName);
var definition = ItemDefinition.Load(itemName, instanceName);
var data = Read.Active(definition, instance.Name);
if (this.Request.QueryString.AllKeys.Count() > 3)
{
var temporalData = data.ToList();
if (this.Request.QueryString.AllKeys.Any(k => k == "InstanceName"))
{
external = true;
foreach (string key in this.Request.QueryString.AllKeys.Where(k => k != "ItemName"))
{
if (!key.Equals("ItemName", StringComparison.OrdinalIgnoreCase))
{
continue;
}
var localKey = key.Remove('_');
object query = this.Request.QueryString[localKey];
if (query.ToString().Contains("EXCLUDE"))
{
long val = Convert.ToInt64(query.ToString().Split('/')[1]);
temporalData = temporalData.Where(d => Convert.ToInt64(d[localKey]) != val).ToList();
}
else if (query.GetType().ToString().ToUpperInvariant() == "SYSTEM.STRING")
{
if (query.ToString().Trim().ToUpperInvariant().Equals("NULL", StringComparison.OrdinalIgnoreCase) || query.ToString().Trim().ToUpperInvariant().Equals("0", StringComparison.OrdinalIgnoreCase))
{
temporalData = temporalData.Where(d => d[localKey] == null || d[localKey].ToString().Trim().Equals("0", StringComparison.OrdinalIgnoreCase)).ToList();
}
else
{
temporalData = temporalData.Where(d => d[localKey] != null && d[localKey].ToString().Equals(query.ToString())).ToList();
}
}
else
{
temporalData = temporalData.Where(d => d[localKey] != null && Convert.ToInt64(d[localKey]) == Convert.ToInt64(query)).ToList();
}
}
data = new ReadOnlyCollection <ItemBuilder>(temporalData);
}
else
{
foreach (string key in this.Request.QueryString.AllKeys.Where(k => k != "ItemName"))
{
var localKey = key;
if (localKey == "callback")
{
external = true;
}
if (localKey == "format" || localKey == "InstanceName" || localKey == "callback" || localKey == "_" || string.IsNullOrEmpty(localKey))
{
continue;
}
if (key.Equals(itemName + "Id"))
{
localKey = "Id";
}
localKey = localKey.Replace("_", string.Empty);
object query = this.Request.QueryString[key];
if (query.ToString().Contains("EXCLUDE"))
{
long val = Convert.ToInt64(query.ToString().Split('/')[1]);
temporalData = temporalData.Where(d => Convert.ToInt64(d[localKey]) != val).ToList();
}
else if (query.GetType().ToString().ToUpperInvariant() == "SYSTEM.STRING")
{
if (query.ToString().Trim().ToUpperInvariant().Equals("NULL", StringComparison.OrdinalIgnoreCase) || query.ToString().Trim().ToUpperInvariant().Equals("0", StringComparison.OrdinalIgnoreCase))
{
temporalData = temporalData.Where(d => d[localKey] == null || d[localKey].ToString().Trim().Equals("0", StringComparison.OrdinalIgnoreCase)).ToList();
}
else
{
temporalData = temporalData.Where(d => d[localKey] != null && d[localKey].ToString().Equals(query.ToString())).ToList();
}
}
else
{
temporalData = temporalData.Where(d => d[localKey] != null && Convert.ToInt64(d[localKey]) == Convert.ToInt64(query)).ToList();
}
}
data = new ReadOnlyCollection <ItemBuilder>(temporalData);
}
}
var d1 = DateTime.Now;
this.Response.Clear();
this.Response.ContentType = "application/json";
var d2 = DateTime.Now;
if (external)
{
string res = string.Format(
CultureInfo.InvariantCulture,
@"{0}",
ItemBuilder.ListItemJson(data).Replace("\n", string.Empty).Replace("\r", string.Empty).Replace("\\", string.Empty).Replace(@"""""""""", @""""""));
this.Response.Write(res);
}
else
{
this.Response.Write(@"{""data"":");
this.Response.Write(ItemBuilder.ListItemJson(data));
this.Response.Write(string.Format(CultureInfo.GetCultureInfo("en-us"), @",""Time"":""{0:#,##0} - {1:#,##0}""}}", (d1 - d0).TotalMilliseconds, (d2 - d1).TotalMilliseconds));
}
this.Response.Flush();
this.Response.SuppressContent = true;
this.ApplicationInstance.CompleteRequest();
}
