//----------------------------------------------------------------------------------------------------
public static void Do_ExcelUpload(string guidKey, int tableId)
{
ImportExportLogic.CleanExpiredMemory();
var importExcelInfo = new ImportExcelInfo()
{
GuidKey = guidKey, UploadTime = DateTime.Now, TableId = tableId
};
var dict = GetMemoryDict();
if (!dict.ContainsKey(guidKey))
{
dict.Add(guidKey, importExcelInfo);
}
else
{
throw new Exception("GuidKey '" + guidKey + "' already in memory.");
}
string s = "";
try
{
if (String.IsNullOrEmpty(guidKey))
{
throw new Exception("GuidKey is null or empty");
}
if (HttpContext.Current.Request.Files.Count < 1)
{
throw new Exception("No file attached");
}
var fileData = HttpContext.Current.Request.Files[0];
if (!fileData.FileName.ToLower().EndsWith(".xlsx"))
{
throw new Exception("Must be .xlsx Excel file.");
}
importExcelInfo.FileName = fileData.FileName;
importExcelInfo.ContentType = fileData.ContentType;
importExcelInfo.ContentLength = fileData.ContentLength;
importExcelInfo.ExcelPackage = new ExcelPackage(fileData.InputStream);
s += String.Format(@"
FileName: {0}<br />
ContentType: {1}<br />
ContentLength: {2}<br />
", fileData.FileName, fileData.ContentType, fileData.ContentLength);
}
catch (Exception exc)
{
ASPdb.Framework.Debug.RecordException(exc);
importExcelInfo.ExceptionMessage = exc.Message;
importExcelInfo.ExceptionStacktrace = exc.StackTrace;
}
HttpContext.Current.Response.Write(s);
}
public ImportProcess(ImportExcelInfo importInfo)
{
ImportInfo = importInfo;
dtContacts = CreateContact();
dtPhones = CreatePhoneTable();
dtContactLevels = CreateContactLevel();
dtContactTmps = CreateContactTmpTable();
dtObjectChanges = CreateObjectChangeTable();
}
public ActionResult Upload(FormCollection forms, HttpPostedFileBase excelfile)
{
#region "Start Checkpoint"
CheckPointApi checkPointApi = new CheckPointApi();
var watch = new Stopwatch();
watch.Start();
checkPointApi.CheckPointNew(UserContext.GetCurrentUser().UserName, "ImportExcel", "Start", 0);
#endregion
//ViewBag.Filename = fileUpload.FileName;
if (excelfile.FileName.EndsWith(".xls") || excelfile.FileName.EndsWith(".xlsx"))
{
var importInfo = new ImportExcelInfo
{
UserId = UserContext.GetCurrentUser().UserID,
Status = ConvertHelper.ToInt32(forms["Status"]),
LevelId = ConvertHelper.ToInt32(forms["LevelId"]),
BranchId = ConvertHelper.ToInt32(forms["BranchId"]),
ChannelId = ConvertHelper.ToInt32(forms["ChannelId"]),
TypeId = ConvertHelper.ToInt32(forms["SourceTypeId"]),
CollectorId = ConvertHelper.ToInt32(forms["CollectorId"]),
};
var now = DateTime.Now;
var fullFileDir = Server.MapPath("/Uploads");
var fullFilePath = Server.MapPath("/Uploads/") + string.Format("{0}_{1}_{2}_{3}_{4}_{5}_", now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second) + excelfile.FileName;
if (!Directory.Exists(fullFileDir))
{
Directory.CreateDirectory(fullFileDir);
}
excelfile.SaveAs(fullFilePath);
importInfo.TotalRow = 0;
importInfo.CheckCount = 0;
importInfo.ErrorCount = 0;
importInfo.DuplicateCount = 0;
importInfo.FilePath = fullFilePath;
importInfo.ImportedDate = DateTime.Now;
var id = ImportExcelRepository.Create(importInfo);
var socketClient = new SocketClient("localhost", Constant.PortImportExcel);
socketClient.SendMessage(id.ToString());
ViewBag.Message = id;
}
else
{
ViewBag.Message = "InvalidFileFormat";
}
#region "End CheckPoint"
watch.Stop();
checkPointApi.CheckPointNew(UserContext.GetCurrentUser().UserName, "ImportExcel", "End", watch.ElapsedMilliseconds);
#endregion
return(View());
}
public ImportProcess(ImportExcelInfo importInfo)
{
ImportInfo = importInfo;
dtContacts = CreateContact();
dtPhones = CreatePhoneTable();
dtContactLevels = CreateContactLevel();
dtContactTmps = CreateContactTmpTable();
dtObjectChanges = CreateObjectChangeTable();
dtContactDuplicate = CreateContactDuplicateTable();
dtLogContainerMOL = CreateLogContainerMOL();
}
public static void UpdateImportProgress(ImportExcelInfo import, ContactInfo contact, string mobile1, string mobile2, string tel)
{
if (contact == null)
{
Update(import);
}
else
{
DataProvider.Instance().ImportExcels_UpdateImportProgress(import.ImportId, import.RowIndex, import.TotalRow, import.CheckCount, import.ErrorCount, import.InternalDuplicateCount, import.DuplicateCount, contact.Code, contact.Fullname, contact.Birthday, contact.Email, mobile1, mobile2, tel, contact.Address, contact.Gender, contact.TypeId, contact.ChannelId, contact.LevelId, contact.CollectorId, contact.BranchId, contact.StatusId, contact.StatusCareConsultantId, contact.RegisteredDate, contact.Notes);
}
}
public static void UpdateImportProgress(ImportExcelInfo import, ContactInfo contact, string mobile1, string mobile2, string tel)
{
if (contact == null)
{
Update(import);
}
else
{
DataProvider.Instance().ImportExcels_UpdateImportProgress(import.ImportId, import.RowIndex, import.TotalRow, import.CheckCount, import.ErrorCount, import.InternalDuplicateCount, import.DuplicateCount, contact.Code, contact.Fullname, contact.Birthday, contact.Email, mobile1, mobile2, tel, contact.Address, contact.Gender, contact.TypeId, contact.ChannelId, contact.LevelId, contact.CollectorId, contact.BranchId, contact.StatusId, contact.StatusCareConsultantId, contact.RegisteredDate, contact.Notes);
}
}
private static List <ImportExcelInfo> FillImportExcelCollection(IDataReader reader, out int totalRecords)
{
var retVal = new List <ImportExcelInfo>();
totalRecords = 0;
try
{
while (reader.Read())
{
//fill business object
var info = new ImportExcelInfo();
info.ImportId = ConvertHelper.ToInt32(reader["ImportId"]);
info.UserId = ConvertHelper.ToInt32(reader["UserId"]);
info.TypeId = ConvertHelper.ToInt32(reader["TypeId"]);
info.ChannelId = ConvertHelper.ToInt32(reader["ChannelId"]);
info.CollectorId = ConvertHelper.ToInt32(reader["CollectorId"]);
info.BranchId = ConvertHelper.ToInt32(reader["BranchId"]);
info.Status = ConvertHelper.ToInt32(reader["Status"]);
info.FilePath = ConvertHelper.ToString(reader["FilePath"]);
info.TotalRow = ConvertHelper.ToInt32(reader["TotalRow"]);
info.CheckCount = ConvertHelper.ToInt32(reader["CheckCount"]);
info.ErrorCount = ConvertHelper.ToInt32(reader["ErrorCount"]);
info.DuplicateCount = ConvertHelper.ToInt32(reader["DuplicateCount"]);
retVal.Add(info);
}
//Get the next result (containing the total)
reader.NextResult();
//Get the total no of records from the second result
if (reader.Read())
{
totalRecords = Convert.ToInt32(reader[0]);
}
}
catch (Exception exc)
{
//DotNetNuke.Services.Exceptions.Exceptions.LogException(exc);
}
finally
{
//close datareader
ObjectHelper.CloseDataReader(reader, true);
}
return(retVal);
}
public static ImportExcelModel FromInfo(ImportExcelInfo info)
{
ImportExcelModel retVal = new ImportExcelModel();
retVal.Filename = Path.GetFileName(info.FilePath);
retVal.IsCorrectFormat = retVal.Filename.ToLower().EndsWith(".xls") || retVal.Filename.ToLower().EndsWith(".xlsx");
retVal.RowIndex = info.RowIndex;
retVal.TotalRow = info.TotalRow;
retVal.ErrorCount = info.ErrorCount;
retVal.CheckCount = info.CheckCount;
retVal.DuplicateCount = info.DuplicateCount + info.InternalDuplicateCount;
retVal.NoDuplicateCount = retVal.TotalRow - retVal.ErrorCount - retVal.DuplicateCount;
retVal.ImportedDate = info.ImportedDate;
retVal.Status = info.Status;
retVal.ImportStatus = info.ImportStatus;
var sourceType = SourceTypeRepository.GetInfo(info.TypeId);
if (sourceType != null)
{
retVal.SourceTypeName = sourceType.Name;
}
var channel = ChannelRepository.GetInfo(info.ChannelId);
if (channel != null)
{
retVal.ChannelName = channel.Name;
}
var branch = BranchRepository.GetInfo(info.BranchId);
if (branch != null)
{
retVal.BranchName = branch.Name;
}
var collector = CollectorRepository.GetInfo(info.CollectorId);
if (collector != null)
{
retVal.CollectorName = collector.Name;
}
return(retVal);
}
public ActionResult Upload(FormCollection forms, HttpPostedFileBase excelfile)
{
//ViewBag.Filename = fileUpload.FileName;
if (excelfile.FileName.EndsWith(".xls") || excelfile.FileName.EndsWith(".xlsx"))
{
var importInfo = new ImportExcelInfo
{
UserId = UserContext.GetCurrentUser().UserID,
Status = ConvertHelper.ToInt32(forms["Status"]),
LevelId = ConvertHelper.ToInt32(forms["LevelId"]),
BranchId = ConvertHelper.ToInt32(forms["BranchId"]),
ChannelId = ConvertHelper.ToInt32(forms["ChannelId"]),
TypeId = ConvertHelper.ToInt32(forms["SourceTypeId"]),
CollectorId = ConvertHelper.ToInt32(forms["CollectorId"]),
};
var now = DateTime.Now;
var fullFileDir = Server.MapPath("/Uploads");
var fullFilePath = Server.MapPath("/Uploads/") + string.Format("{0}_{1}_{2}_{3}_{4}_{5}_", now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second) + excelfile.FileName;
if (!Directory.Exists(fullFileDir)) Directory.CreateDirectory(fullFileDir);
excelfile.SaveAs(fullFilePath);
importInfo.TotalRow = 0;
importInfo.CheckCount = 0;
importInfo.ErrorCount = 0;
importInfo.DuplicateCount = 0;
importInfo.FilePath = fullFilePath;
importInfo.ImportedDate = DateTime.Now;
var id = ImportExcelRepository.Create(importInfo);
var socketClient = new SocketClient("localhost", 9999);
socketClient.SendMessage(id.ToString());
ViewBag.Message = id;
}
else
{
ViewBag.Message = "InvalidFileFormat";
}
return View();
}
public void Dispose()
{
ImportInfo = null;
}
public static void UpdateDuplicateContact(ImportExcelInfo import, int duplicateContactId)
{
DataProvider.Instance().ImportExcels_UpdateDuplicateContact(import.ImportId, import.RowIndex, import.TotalRow, import.CheckCount, import.ErrorCount, import.DuplicateCount, import.TypeId, duplicateContactId);
}
public string Edit(FormDataCollection form)
{
var retVal = string.Empty;
var operation = form.Get("oper");
var id = ConvertHelper.ToInt32(form.Get("ImportExcelId"));
if (!string.IsNullOrEmpty(operation))
{
ImportExcelInfo info;
switch (operation)
{
case "edit":
info = ImportExcelRepository.GetInfo(id);
if (info != null)
{
/*
*
* info.ImportId = form.Get("ImportId");
*
* info.UserId = form.Get("UserId");
*
* info.TypeId = form.Get("TypeId");
*
* info.ChannelId = form.Get("ChannelId");
*
* info.CollectorId = form.Get("CollectorId");
*
* info.BranchId = form.Get("BranchId");
*
* info.Status = form.Get("Status");
*
* info.FilePath = form.Get("FilePath");
*
* info.TotalRow = form.Get("TotalRow");
*
* info.CheckCount = form.Get("CheckCount");
*
* info.ErrorCount = form.Get("ErrorCount");
*
* info.DuplicateCount = form.Get("DuplicateCount");
*
* info.ImportedDate = form.Get("ImportedDate");
*
* ImportExcelRepository.Update(info);
*/
}
break;
case "add":
info = new ImportExcelInfo();
/*
*
* info.ImportId = form.Get("ImportId");
*
* info.UserId = form.Get("UserId");
*
* info.TypeId = form.Get("TypeId");
*
* info.ChannelId = form.Get("ChannelId");
*
* info.CollectorId = form.Get("CollectorId");
*
* info.BranchId = form.Get("BranchId");
*
* info.Status = form.Get("Status");
*
* info.FilePath = form.Get("FilePath");
*
* info.TotalRow = form.Get("TotalRow");
*
* info.CheckCount = form.Get("CheckCount");
*
* info.ErrorCount = form.Get("ErrorCount");
*
* info.DuplicateCount = form.Get("DuplicateCount");
*
* info.ImportedDate = form.Get("ImportedDate");
*
*/
ImportExcelRepository.Create(info);
break;
case "del":
ImportExcelRepository.Delete(id);
break;
}
}
return(retVal);
}
public void Dispose()
{
ImportInfo = null;
}
private static List<ImportExcelInfo> FillImportExcelCollection(IDataReader reader, out int totalRecords)
{
var retVal = new List<ImportExcelInfo>();
totalRecords = 0;
try
{
while (reader.Read())
{
//fill business object
var info = new ImportExcelInfo();
info.ImportId = ConvertHelper.ToInt32(reader["ImportId"]);
info.UserId = ConvertHelper.ToInt32(reader["UserId"]);
info.TypeId = ConvertHelper.ToInt32(reader["TypeId"]);
info.ChannelId = ConvertHelper.ToInt32(reader["ChannelId"]);
info.CollectorId = ConvertHelper.ToInt32(reader["CollectorId"]);
info.BranchId = ConvertHelper.ToInt32(reader["BranchId"]);
info.Status = ConvertHelper.ToInt32(reader["Status"]);
info.FilePath = ConvertHelper.ToString(reader["FilePath"]);
info.TotalRow = ConvertHelper.ToInt32(reader["TotalRow"]);
info.CheckCount = ConvertHelper.ToInt32(reader["CheckCount"]);
info.ErrorCount = ConvertHelper.ToInt32(reader["ErrorCount"]);
info.DuplicateCount = ConvertHelper.ToInt32(reader["DuplicateCount"]);
retVal.Add(info);
}
//Get the next result (containing the total)
reader.NextResult();
//Get the total no of records from the second result
if (reader.Read())
{
totalRecords = Convert.ToInt32(reader[0]);
}
}
catch (Exception exc)
{
//DotNetNuke.Services.Exceptions.Exceptions.LogException(exc);
}
finally
{
//close datareader
ObjectHelper.CloseDataReader(reader, true);
}
return retVal;
}
public static ImportExcelModel FromInfo(ImportExcelInfo info)
{
ImportExcelModel retVal = new ImportExcelModel();
retVal.Filename = Path.GetFileName(info.FilePath);
retVal.IsCorrectFormat = retVal.Filename.ToLower().EndsWith(".xls") || retVal.Filename.ToLower().EndsWith(".xlsx");
retVal.RowIndex = info.RowIndex;
retVal.TotalRow = info.TotalRow;
retVal.ErrorCount = info.ErrorCount;
retVal.CheckCount = info.CheckCount;
retVal.DuplicateCount = info.DuplicateCount + info.InternalDuplicateCount;
retVal.NoDuplicateCount = retVal.TotalRow - retVal.ErrorCount - retVal.DuplicateCount;
retVal.ImportedDate = info.ImportedDate;
retVal.Status = info.Status;
retVal.ImportStatus = info.ImportStatus;
var sourceType = SourceTypeRepository.GetInfo(info.TypeId);
if (sourceType != null) retVal.SourceTypeName = sourceType.Name;
var channel = ChannelRepository.GetInfo(info.ChannelId);
if (channel != null) retVal.ChannelName = channel.Name;
var branch = BranchRepository.GetInfo(info.BranchId);
if (branch != null) retVal.BranchName = branch.Name;
var collector = CollectorRepository.GetInfo(info.CollectorId);
if (collector != null) retVal.CollectorName = collector.Name;
return retVal;
}
public string Edit(FormDataCollection form)
{
var retVal = string.Empty;
var operation = form.Get("oper");
var id = ConvertHelper.ToInt32(form.Get("ImportExcelId"));
if (!string.IsNullOrEmpty(operation))
{
ImportExcelInfo info;
switch (operation)
{
case "edit":
info = ImportExcelRepository.GetInfo(id);
if (info != null)
{
/*
info.ImportId = form.Get("ImportId");
info.UserId = form.Get("UserId");
info.TypeId = form.Get("TypeId");
info.ChannelId = form.Get("ChannelId");
info.CollectorId = form.Get("CollectorId");
info.BranchId = form.Get("BranchId");
info.Status = form.Get("Status");
info.FilePath = form.Get("FilePath");
info.TotalRow = form.Get("TotalRow");
info.CheckCount = form.Get("CheckCount");
info.ErrorCount = form.Get("ErrorCount");
info.DuplicateCount = form.Get("DuplicateCount");
info.ImportedDate = form.Get("ImportedDate");
ImportExcelRepository.Update(info);
*/
}
break;
case "add":
info = new ImportExcelInfo();
/*
info.ImportId = form.Get("ImportId");
info.UserId = form.Get("UserId");
info.TypeId = form.Get("TypeId");
info.ChannelId = form.Get("ChannelId");
info.CollectorId = form.Get("CollectorId");
info.BranchId = form.Get("BranchId");
info.Status = form.Get("Status");
info.FilePath = form.Get("FilePath");
info.TotalRow = form.Get("TotalRow");
info.CheckCount = form.Get("CheckCount");
info.ErrorCount = form.Get("ErrorCount");
info.DuplicateCount = form.Get("DuplicateCount");
info.ImportedDate = form.Get("ImportedDate");
*/
ImportExcelRepository.Create(info);
break;
case "del":
ImportExcelRepository.Delete(id);
break;
}
}
return retVal;
}
public static void Update(ImportExcelInfo info)
{
DataProvider.Instance().ImportExcels_Update(info.ImportId, info.UserId, info.TypeId, info.ChannelId, info.CollectorId, info.BranchId, info.LevelId, info.Status, info.FilePath, info.RowIndex, info.TotalRow, info.CheckCount, info.ErrorCount, info.InternalDuplicateCount, info.DuplicateCount,info.ImportedDate, info.ImportStatus);
}
public static void UpdateDuplicateContact(ImportExcelInfo import, int duplicateContactId)
{
DataProvider.Instance().ImportExcels_UpdateDuplicateContact(import.ImportId, import.RowIndex, import.TotalRow, import.CheckCount, import.ErrorCount, import.DuplicateCount, import.TypeId, duplicateContactId);
}
public static void Update(ImportExcelInfo info)
{
DataProvider.Instance().ImportExcels_Update(info.ImportId, info.UserId, info.TypeId, info.ChannelId, info.CollectorId, info.BranchId, info.LevelId, info.Status, info.FilePath, info.RowIndex, info.TotalRow, info.CheckCount, info.ErrorCount, info.InternalDuplicateCount, info.DuplicateCount, info.ImportedDate, info.ImportStatus);
}
public static int Create(ImportExcelInfo info)
{
return(DataProvider.Instance().ImportExcels_Insert(info.UserId, info.TypeId, info.ChannelId, info.CollectorId, info.BranchId, info.LevelId, info.Status, info.FilePath, info.TotalRow, info.CheckCount, info.ErrorCount, info.InternalDuplicateCount, info.DuplicateCount, info.ImportedDate, info.ImportStatus));
}
//----------------------------------------------------------------------------------------------------
public static ImportExcelInfo Get_ImportExcelInfo_ForClient(string guidKey)
{
var obj = GetFileFromMemory(guidKey);
if (obj == null)
{
return(null);
}
var rtn = new ImportExcelInfo()
{
GuidKey = obj.GuidKey,
TableId = obj.TableId,
UploadTime = obj.UploadTime,
FileName = obj.FileName,
ContentLength = obj.ContentLength,
ContentType = obj.ContentType,
ExceptionMessage = obj.ExceptionMessage,
ExceptionStacktrace = obj.ExceptionStacktrace,
ExcelPackage = null
};
var tableStructure = DbInterfaces.SQLServerInterface.Tables__Get(rtn.TableId, false);
var tableColumns = new List <ImportExcelInfo_TableColumn>();
foreach (var column in tableStructure.Columns)
{
tableColumns.Add(new ImportExcelInfo_TableColumn()
{
ColumnName = column.ColumnName,
IsSelectedAsUniqueKey = column.IsPrimaryKey,
IsPrimaryKey = column.IsPrimaryKey,
IsIdentity = column.IsIdentity,
DataType = column.DataType,
AllowNulls = column.AllowNulls,
DefaultValue = column.DefaultValue,
DefaultValue_Orig = column.DefaultValue
});
}
rtn.TableColumns = tableColumns.ToArray();
var worksheets = new List <ImportExcelInfo_Worksheet>();
foreach (var item in obj.ExcelPackage.Workbook.Worksheets)
{
var wColumns = new List <ImportExcelInfo_WorksheetColumn>();
int emptyColCount = 0;
for (int i = 1; i <= item.Cells.Count(); i++)
{
bool columnAdded = false;
try
{
string value = item.Cells[1, i].Value.ToString();
if (value != null && value.Trim() != "")
{
wColumns.Add(new ImportExcelInfo_WorksheetColumn()
{
Index = i,
ColumnName = value.Trim()
});
emptyColCount = 0;
columnAdded = true;
}
}
catch { }
if (!columnAdded)
{
emptyColCount++;
}
if (emptyColCount > 10)
{
break;
}
}
worksheets.Add(new ImportExcelInfo_Worksheet()
{
WorksheetName = item.Name,
Columns = wColumns.ToArray()
});
}
rtn.Worksheets = worksheets.ToArray();
foreach (var worksheet in rtn.Worksheets)
{
FindMatches(worksheet.Columns, rtn.TableColumns);
}
return(rtn);
}
public static int Create(ImportExcelInfo info)
{
return DataProvider.Instance().ImportExcels_Insert(info.UserId, info.TypeId, info.ChannelId, info.CollectorId, info.BranchId, info.LevelId, info.Status, info.FilePath, info.TotalRow, info.CheckCount, info.ErrorCount, info.InternalDuplicateCount, info.DuplicateCount, info.ImportedDate, info.ImportStatus);
}