private async Task <string> GenerateExcelFile()
{
DateTime Now = AppManager.Now.Date;
var _query = from u in db.Users
where u.IsDataActive
select new
{
u.ID,
u.FirstName,
u.LastName,
u.Title,
u.Position,
u.Organization,
u.Email,
u.Phone,
u.PostalCode,
u.LastUpdatedOn,
};
var _data = await _query.OrderByDescending(o => o.ID).ToListAsync();
List <DataTableRow> _rows = new List <DataTableRow>();
foreach (var _rowData in _data)
{
DataTableRow _row = new DataTableRow();
string _statusHtml = string.Empty;
_row.Add(_rowData.FirstName + " " + _rowData.LastName);
_row.Add(_rowData.Email);
_row.Add(_rowData.Phone);
_row.Add(_rowData.PostalCode);
_row.Add(_rowData.Position);
_row.Add(_rowData.Organization);
_row.Add(_rowData.LastUpdatedOn.ToString());
_rows.Add(_row);
}
var _workbook = new XLWorkbook();
var _worksheet = _workbook.Worksheets.Add("Users");
_worksheet.Cell("A1").Value = "Name";
_worksheet.Cell("B1").Value = "Email";
_worksheet.Cell("C1").Value = "Phone";
_worksheet.Cell("D1").Value = "Postal Code";
_worksheet.Cell("E1").Value = "Position";
_worksheet.Cell("F1").Value = "Organization";
_worksheet.Cell("G1").Value = "Last Updated On";
int _rowIndex = 2;
foreach (DataTableRow _row in _rows)
{
_worksheet.Cell("A" + _rowIndex).Value = _row.ElementAt(0);
_worksheet.Cell("B" + _rowIndex).Value = _row.ElementAt(1);
_worksheet.Cell("C" + _rowIndex).Value = "'" + _row.ElementAt(2);
_worksheet.Cell("D" + _rowIndex).Value = "'" + _row.ElementAt(3);
_worksheet.Cell("E" + _rowIndex).Value = "'" + _row.ElementAt(4);
_worksheet.Cell("F" + _rowIndex).Value = _row.ElementAt(5);
_worksheet.Cell("G" + _rowIndex).Value = _row.ElementAt(6);
_rowIndex++;
}
_worksheet.Row(1).Style.Font.Bold = true;
_workbook.SaveAs(HostingEnvironment.MapPath("/Exports/users.xlsx"));
return("/Exports/users.xlsx");
}
private async Task <string> GenerateExcelFile(string type)
{
if (type == "seratio")
{
type = "Seratio Coin";
}
else if (type == "womenscoin")
{
type = "Womens Coin";
}
else if (type == "studentcoin")
{
type = "Student Coin";
}
else if (type == "10xgrowth")
{
type = "Growth Token";
}
else if (type == "ttc")
{
type = "TimeTraveler Coin";
}
var _query = from i in db.Investments
join u in db.Users on i.UserID equals u.ID
where i.Type == type
select new
{
i.ID,
i.Mode,
i.Amount,
i.Status,
i.InitializedOn,
i.LastUpdatedOn,
i.TxRefNoUpdatedOn,
i.TxReferenceNumber,
i.UserID,
u.Title,
u.FirstName,
i.ReceivedAmount,
u.LastName,
i.Token,
u.Email,
i.ExchangeRate,
i.ConfirmedOn,
i.RejectedOn,
i.OriginalExchangeRate,
i.UserSessionID,
i.UserSession,
i.CampaignCode,
i.InvestmentType,
i.TokenAmount,
i.Type,
u.Phone,
u.ReferralSource,
i.HasTransferred,
u.PostalCode
};
var _data = await _query.OrderByDescending(o => o.ID).ToListAsync();
List <DataTableRow> _rows = new List <DataTableRow>();
foreach (var _rowData in _data)
{
DataTableRow _row = new DataTableRow();
_row.Add(_rowData.Title);
_row.Add($"{_rowData.FirstName} {_rowData.LastName}");
_row.Add(_rowData.Email);
_row.Add(_rowData.Phone);
_row.Add(_rowData.Mode);
_row.Add(_rowData.Type);
_row.Add(_rowData.Amount);
_row.Add(_rowData.Token);
if (_rowData.UserSessionID != null)
{
_row.Add(_rowData.UserSession.IPAddress);
}
else
{
_row.Add("N/A");
}
_row.Add(_rowData.CampaignCode);
if (_rowData.InvestmentType != null)
{
_row.Add(_rowData.InvestmentType);
}
else
{
_row.Add("N/A");
}
_row.Add(_rowData.InitializedOn.ToString());
if (_rowData.OriginalExchangeRate != null)
{
_row.Add(String.Format("'{0:0.0000000}", _rowData.OriginalExchangeRate.ToString()));
}
else
{
_row.Add("N/A");
}
if (_rowData.TxReferenceNumber != null)
{
_row.Add(_rowData.TxReferenceNumber);
}
else
{
_row.Add("Pending");
}
if (_rowData.ConfirmedOn != null)
{
_row.Add(_rowData.ConfirmedOn);
}
else
{
_row.Add("Pending");
}
if (_rowData.ConfirmedOn != null)
{
_row.Add(_rowData.ReceivedAmount.ToString());
}
else
{
_row.Add("Pending");
}
if (_rowData.ExchangeRate != null)
{
_row.Add(String.Format("{0:0.00}", _rowData.ExchangeRate.ToString()));
}
else
{
_row.Add("N/A");
}
string _statusHtml = "";
if (_rowData.ConfirmedOn != null)
{
_statusHtml = "Confirmed";
}
else
{
if (_rowData.RejectedOn != null)
{
_statusHtml = "Rejected";
}
else
{
_statusHtml = "Pending";
}
}
if (_rowData.Type == "Growth Token" && _rowData.Status == "Confirmed" && _rowData.HasTransferred == true)
{
_statusHtml = $"YOUR {_rowData.ReceivedAmount} SER TOKENS HAVE NOW BEEN TRANSFERRED TO THE GROWTH TOKEN CRYPTOFUND";
}
else if (_rowData.Type == "Growth Token" && _rowData.Status == "Confirmed")
{
_statusHtml = "INVESTMENT CONFIRMED, PENDING TRANSFER TO THE GROWTH TOKEN CRYPTOFUND";
}
_row.Add(_statusHtml);
if (!string.IsNullOrEmpty(_rowData.TokenAmount))
{
_row.Add(_rowData.TokenAmount);
}
else
{
_row.Add("N/A");
}
_row.Add(_rowData.ReferralSource);
_row.Add(_rowData.PostalCode);
_rows.Add(_row);
}
var _workbook = new XLWorkbook();
var _worksheet = _workbook.Worksheets.Add("Investments");
_worksheet.Cell("A1").Value = "Contributor (Title)";
_worksheet.Cell("B1").Value = "Contributor (Name)";
_worksheet.Cell("C1").Value = "Contributor (Email)";
_worksheet.Cell("D1").Value = "Contributor (Phone)";
_worksheet.Cell("E1").Value = "Contribution Type";
_worksheet.Cell("F1").Value = "ICO Type";
_worksheet.Cell("G1").Value = "Amount";
_worksheet.Cell("H1").Value = "Token ID ";
_worksheet.Cell("I1").Value = "IP Address";
_worksheet.Cell("J1").Value = "Campaign code";
_worksheet.Cell("K1").Value = "Token Type";
_worksheet.Cell("L1").Value = "Contribution Requested On";
_worksheet.Cell("M1").Value = "Original Exchange rate";
_worksheet.Cell("N1").Value = "Transaction ID";
_worksheet.Cell("O1").Value = "Contribution Received On";
_worksheet.Cell("P1").Value = "Contribution Received Amount";
_worksheet.Cell("Q1").Value = "Exchange Rate";
_worksheet.Cell("R1").Value = "Confirmed";
_worksheet.Cell("S1").Value = "Token Amount";
_worksheet.Cell("T1").Value = "Referral Source";
_worksheet.Cell("U1").Value = "Postal Address";
int _rowIndex = 2;
foreach (DataTableRow _row in _rows)
{
_worksheet.Cell("A" + _rowIndex).Value = _row.ElementAt(0);
_worksheet.Cell("B" + _rowIndex).Value = _row.ElementAt(1);
_worksheet.Cell("C" + _rowIndex).Value = _row.ElementAt(2);
_worksheet.Cell("D" + _rowIndex).Value = "'" + _row.ElementAt(3);
_worksheet.Cell("E" + _rowIndex).Value = "'" + _row.ElementAt(4);
_worksheet.Cell("F" + _rowIndex).Value = _row.ElementAt(5);
_worksheet.Cell("G" + _rowIndex).Value = _row.ElementAt(6);
_worksheet.Cell("H" + _rowIndex).Value = _row.ElementAt(7);
_worksheet.Cell("I" + _rowIndex).Value = _row.ElementAt(8);
_worksheet.Cell("J" + _rowIndex).Value = _row.ElementAt(9);
_worksheet.Cell("K" + _rowIndex).Value = _row.ElementAt(10);
_worksheet.Cell("L" + _rowIndex).Value = _row.ElementAt(11);
_worksheet.Cell("M" + _rowIndex).Value = _row.ElementAt(12);
_worksheet.Cell("N" + _rowIndex).Value = "'" + _row.ElementAt(13);
_worksheet.Cell("O" + _rowIndex).Value = "'" + _row.ElementAt(14);
_worksheet.Cell("P" + _rowIndex).Value = _row.ElementAt(15);
_worksheet.Cell("Q" + _rowIndex).Value = _row.ElementAt(16);
_worksheet.Cell("R" + _rowIndex).Value = _row.ElementAt(17);
_worksheet.Cell("S" + _rowIndex).Value = _row.ElementAt(18);
_worksheet.Cell("T" + _rowIndex).Value = _row.ElementAt(19);
_worksheet.Cell("U" + _rowIndex).Value = _row.ElementAt(20);
_rowIndex++;
}
_worksheet.Row(1).Style.Font.Bold = true;
_workbook.SaveAs(HostingEnvironment.MapPath($"/Exports/Investments_{type}.xlsx"));
return($"/Exports/Investments_{type}.xlsx");
}