public ActionResult ClientsServiceDate(ClientsIndexModel model)
{
ModelState.Clear();
try
{
db.CommandTimeout = 600;
var m = new ClientsServiceDateExportModel();
var data = m.GetClientServiceDateReport(db, Permissions);
return(this.Csv(data));
}
catch (Exception ex)
{
string error = ex.Message;
if (ex.InnerException != null)
{
var inner = ex.InnerException;
while (inner.InnerException != null)
{
inner = inner.InnerException;
}
error = inner.Message;
}
model.Exception = ex;
model.Messages.Add(error);
return(View("Index", model));
}
throw new NotImplementedException();
}
public ActionResult UploadMasterIds(HttpPostedFileWrapper file)
{
if (file == null)
{
var model = new ClientsIndexModel();
ViewBag.ImportMasterIdsError = "Please select a file.";
return(View("Index", model));
}
var import = MasterIdsImportModel.Upload(file, this.Permissions.User.Id);
return(this.RedirectToAction(f => f.PreviewMasterIds(import.Id)));
}
public ActionResult Index(ClientsIndexModel model)
{
model = model ?? new ClientsIndexModel();
using (var db = new ccEntities())
{
var lastRun = db.Globals.Where(f => f.Message == "CFS auto import/upload finished").OrderByDescending(f => f.Date).FirstOrDefault();
if (lastRun != null)
{
ViewBag.LastRun = lastRun.Date.Value.ToString();
}
}
return(View("Index", model));
}
public ActionResult ExportNew(ClientsIndexModel model)
{
ModelState.Clear();
try
{
db.CommandTimeout = 600;
var data = db.NewClientsExport((int)ApprovalStatusEnum.New);
return(this.Csv(data));
}
catch (Exception)
{
return(View("Index", model));
}
throw new NotImplementedException();
}
public ActionResult ClientsRejectedPending(ClientsIndexModel model)
{
ModelState.Clear();
try
{
db.CommandTimeout = 600;
var m = new ClientsRejectedPendingReportModel();
var data = m.GetRejectedPendingData(db, Permissions);
return(this.Excel("output", "Sheet1", data));
}
catch
{
return(View("Index", model));
}
throw new NotImplementedException();
}
public ActionResult UpdateNewToPending(ClientsIndexModel model)
{
using (var db = new ccEntities())
{
try
{
var clients = db.UpdateClientApptovalStatus((int)ApprovalStatusEnum.New, (int)ApprovalStatusEnum.Pending, this.Permissions.User.Id).ToList();
model.Messages.Add(string.Format("{0} records updated", clients.Count()));
model.UpdateClientsCount(db);
}
catch (System.Data.UpdateException ex)
{
model.Exception = ex;
}
return(Index(model));
}
}
public ActionResult CsvExport(ClientsIndexModel model)
{
ModelState.Clear();
TryValidateModel(model.HistoryExportModel);
if (ModelState.IsValid)
{
var m = model.HistoryExportModel;
var records = m.GetExportData(db, Permissions);
var propperties = m.GetExportProperties();
return(this.Csv(records, propperties));
}
else
{
return(View("Index", model));
}
}
public ActionResult Upload(HttpPostedFileWrapper file)
{
if (file == null)
{
var model = new ClientsIndexModel();
ViewBag.ImportApprovalStatusError = "Please select a file.";
return(View("Index", model));
}
var id = Guid.NewGuid();
string fileName = System.IO.Path.Combine(System.IO.Path.GetTempPath(), id.ToString());
var csvConf = new CsvHelper.Configuration.CsvConfiguration()
{
IsStrictMode = false,
IsCaseSensitive = false,
SkipEmptyRecords = true
};
csvConf.ClassMapping <ApprovalStatusCsvMap>();
using (var csvReader = new CsvHelper.CsvReader(new System.IO.StreamReader(file.InputStream), csvConf))
{
var updatedAt = DateTime.Now;
var updatedBy = this.Permissions.User.Id;
var csvChunkSize = 10000;
var recordIndex = 1;
Dictionary <int, int> fsas = new Dictionary <int, int>();
using (var db = new ccEntities())
{
db.Imports.AddObject(new CC.Data.Import()
{
Id = id,
StartedAt = DateTime.Now,
UserId = this.Permissions.User.Id
});
db.SaveChanges();
var q = (from fs in db.FundStatuses
join a in db.ApprovalStatuses on fs.ApprovalStatusName equals a.Name
select new
{
fsid = fs.Id,
asid = a.Id
}
);
foreach (var intem in q)
{
fsas.Add(intem.fsid, intem.asid);
}
}
foreach (var csvChunk in csvReader.GetRecords <ImportClient>().Split(csvChunkSize))
{
string connectionString = System.Data.SqlClient.ConnectionStringHelper.GetProviderConnectionString();
using (var sqlBulk = new System.Data.SqlClient.SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepNulls))
{
foreach (var record in csvChunk)
{
record.RowIndex = recordIndex++;
record.ImportId = id;
if (record.FundStatusId.HasValue && fsas.ContainsKey(record.FundStatusId.Value))
{
record.ApprovalStatusId = fsas[record.FundStatusId.Value];
}
record.UpdatedAt = updatedAt;
record.UpdatedById = updatedBy;
}
var dataTable = csvChunk.ToDataTable();
var q = dataTable.Columns.OfType <System.Data.DataColumn>().Where(f => f.DataType == typeof(Int32)).Select(f => new
{
c = f.ColumnName,
values = dataTable.Rows.OfType <System.Data.DataRow>().Select((r, i) => r[f.ColumnName])
});
sqlBulk.DestinationTableName = "ImportClients";
sqlBulk.NotifyAfter = 1000;
sqlBulk.ColumnMappings.Add("ClientId", "ClientId");
sqlBulk.ColumnMappings.Add("FundStatusId", "FundStatusId");
sqlBulk.ColumnMappings.Add("RowIndex", "RowIndex");
sqlBulk.ColumnMappings.Add("ImportId", "ImportId");
sqlBulk.ColumnMappings.Add("UpdatedAt", "UpdatedAt");
sqlBulk.ColumnMappings.Add("UpdatedById", "UpdatedById");
sqlBulk.SqlRowsCopied += (s, e) =>
{
System.Diagnostics.Debug.Write(e.RowsCopied);
};
sqlBulk.WriteToServer(dataTable);
}
}
}
return(RedirectToAction("Preview", new { id = id }));
}
