protected void cmbCharacters_SelectedIndexChanged(object sender, EventArgs e)
{
int charId = int.Parse(cmbCharacters.SelectedValue);
if (charId != -1)
{
btnUpload.Enabled = true;
FileUploadCharacterImage.Enabled = true;
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
var character = context.Characters.Single(i => i.CharacterID == charId);
if (character.Image != null)
{
CharacterImage.ImageUrl = "data:image/jpg;base64," + Convert.ToBase64String(character.Image);
CharacterImage.Visible = true;
btnRemoveImage.Visible = true;
}
else
{
CharacterImage.ImageUrl = "#";
CharacterImage.Visible = false;
btnRemoveImage.Visible = false;
}
}
}
else
{
btnUpload.Enabled = false;
FileUploadCharacterImage.Enabled = false;
CharacterImage.ImageUrl = "#";
CharacterImage.Visible = false;
btnRemoveImage.Visible = false;
}
}
private void PopulateComboBoxes()
{
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
cmbSpecies.Items.Add(new ListItem("--Select--", "-1"));
cmbSpecies.Items.AddRange(context.Species.OrderBy(i => i.SpeciesName).Select(i => new ListItem()
{
Text = i.SpeciesName, Value = SqlFunctions.StringConvert((decimal)i.SpeciesID)
}).ToArray());
cmbSpecies_SelectedIndexChanged(null, null);
}
}
protected void btnRemoveCurrentFile_Click(object sender, EventArgs e)
{
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
int spId = int.Parse(cmbSpecies.SelectedValue);
var sp = context.Species.Single(i => i.SpeciesID == spId);
sp.DescriptionFile = null;
sp.DescriptionFileName = null;
context.SaveChanges();
lblInfo.Text = "File cleared.";
}
}
private void RemoveDependencyForCharacterState(int characterStateID, int disabledCharacterID)
{
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
var state = context.CharacterStates.Find(characterStateID);
var disabledCharacter = context.Characters.Find(disabledCharacterID);
state.DisabledCharacters.Remove(disabledCharacter);
context.SaveChanges();
gvExistingDependencies.DataBind();
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
lblInfo.Text = "";
if (FileUploadDescriptionFile.HasFile)
{
//if (FileUploadDescriptionFile.PostedFile.ContentLength > 1048576)
//{
// lblInfo.Text = "File size must be less than 1Mb.";
// return;
//}
string[] acceptedFileTypes = new string[] { ".pdf", ".doc", ".docx", ".xls", ".xlsx" };
string Extension = Path.GetExtension(FileUploadDescriptionFile.PostedFile.FileName);
string fileName = Path.GetFileName(FileUploadDescriptionFile.PostedFile.FileName);
if (!acceptedFileTypes.Contains(Extension.ToLower()))
{
lblInfo.Text = "Please select .PDF, .DOCX, .DOC, .XLSX or XLS file.";
}
else
{
MemoryStream ms = new MemoryStream(FileUploadDescriptionFile.FileBytes);
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
var file = context.UploadedFiles.SingleOrDefault(i => i.FileType == UploadFileTypes.GlossaryFileType);
if (file == null)
{
file = context.UploadedFiles.Create();
file.FileType = UploadFileTypes.GlossaryFileType;
context.UploadedFiles.Add(file);
}
file.FileName = fileName;
file.FileData = ms.ToArray();
ms.Dispose();
context.SaveChanges();
lblInfo.Text = "File uploaded successfully.";
}
}
}
else
{
lblInfo.Text = "Please Select a file first.";
}
}
public static int AssignNewSpeciesId(this Species species, InteractiveKeyEntities context)
{
int id = 0;
try
{
id = context.Species.Max(s => s.SpeciesID);
}
catch (Exception)
{
}
species.SpeciesID = id + 1;
return(id + 1);
}
public static int AssignNewdCharacterId(this Character character, InteractiveKeyEntities context)
{
int id = 0;
try
{
id = context.Characters.Max(s => s.CharacterID);
}
catch (Exception)
{
}
character.CharacterID = id + 1;
return(id + 1);
}
protected void btnRemoveImage_Click(object sender, EventArgs e)
{
int charId = int.Parse(cmbCharacters.SelectedValue);
if (charId != -1)
{
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
var character = context.Characters.Single(i => i.CharacterID == charId);
character.Image = null;
context.SaveChanges();
lblInfo.Text = "Successfully removed the image";
cmbCharacters_SelectedIndexChanged(sender, e);
}
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
lblInfo.Text = "";
if (FileUploadDescriptionFile.HasFile)
{
if (FileUploadDescriptionFile.PostedFile.ContentLength > 5000000)
{
lblInfo.Text = "File size must be less than 5Mb.";
return;
}
string[] acceptedFileTypes = new string[] { ".pdf", ".doc", ".docx", ".xls", ".xlsx" };
string Extension = Path.GetExtension(FileUploadDescriptionFile.PostedFile.FileName);
string fileName = Path.GetFileName(FileUploadDescriptionFile.PostedFile.FileName);
if (!acceptedFileTypes.Contains(Extension.ToLower()))
{
lblInfo.Text = "Please select .PDF, .DOCX, .DOC, .XLSX or XLS file.";
}
else
{
MemoryStream ms = new MemoryStream(FileUploadDescriptionFile.FileBytes);
int spId = int.Parse(cmbSpecies.SelectedValue);
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
var sp = context.Species.Single(i => i.SpeciesID == spId);
sp.DescriptionFile = ms.ToArray();
sp.DescriptionFileName = fileName;
context.SaveChanges();
lblInfo.Text = "File uploaded successfully.";
}
}
}
else
{
lblInfo.Text = "Please Select a file first.";
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
int stateId = int.Parse(cmbState.SelectedValue);
int disCharId = int.Parse(cmbDisabledCharacter.SelectedValue);
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
var state = context.CharacterStates.Single(i => i.CharacterStateID == stateId);
var character = context.Characters.Find(disCharId);
if (state == null || character == null)
{
throw new Exception("Invalid Character State or Disabling character is selected!");
}
state.DisabledCharacters.Add(character);
context.SaveChanges();
gvExistingDependencies.DataBind();
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
lblInfo.Text = "";
if (FileUploadCharacterImage.HasFile)
{
if (FileUploadCharacterImage.PostedFile.ContentLength > 2097152)
{
lblInfo.Text = "File size must be less than 2 Mb.";
return;
}
string[] acceptedImageTypes = new string[] { ".jpg", ".png", ".gif" };
string Extension = Path.GetExtension(FileUploadCharacterImage.PostedFile.FileName);
if (!acceptedImageTypes.Contains(Extension.ToLower()))
{
lblInfo.Text = "Please select .JPG, .PNG or .GIF file.";
}
else
{
MemoryStream ms = new MemoryStream(FileUploadCharacterImage.FileBytes);
int charId = int.Parse(cmbCharacters.SelectedValue);
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
var cha = context.Characters.Single(i => i.CharacterID == charId);
cha.Image = ms.ToArray();
context.SaveChanges();
cmbCharacters_SelectedIndexChanged(sender, e);
lblInfo.Text = "Image uploaded successfully";
}
}
}
else
{
lblInfo.Text = "Please Select a file first.";
}
}
protected void cmbCharacter_SelectedIndexChanged(object sender, EventArgs e)
{
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
int charId = int.Parse(cmbCharacter.SelectedValue);
cmbState.Items.Clear();
if (charId != -1)
{
cmbState.Items.Add(new ListItem("--Select--", "-1"));
cmbState.Items.AddRange(context.CharacterStates.Where(i => i.CharacterID == charId).Select(i => new ListItem()
{
Text = i.CharacterStateCode, Value = SqlFunctions.StringConvert((decimal)i.CharacterStateID)
}).ToArray());
}
else
{
cmbState.Items.Add(new ListItem("--Select Character--", "-1"));
cmbState_SelectedIndexChanged(sender, e);
}
cmbState_SelectedIndexChanged(null, null);
}
}
protected void cmbState_SelectedIndexChanged(object sender, EventArgs e)
{
int stateId = int.Parse(cmbState.SelectedValue);
cmbDisabledCharacter.Items.Clear();
if (stateId != -1)
{
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
cmbDisabledCharacter.Items.Add(new ListItem("--Select--", "-1"));
cmbDisabledCharacter.Items.AddRange(context.Characters.OrderBy(i => i.CharacterCode).Select(i => new ListItem()
{
Text = i.CharacterCode, Value = SqlFunctions.StringConvert((decimal)i.CharacterID)
}).ToArray());
}
}
else
{
cmbDisabledCharacter.Items.Add(new ListItem("--Select State--", "-1"));
}
cmbDisabledCharacter_SelectedIndexChanged(sender, e);
}
protected void ExcelImportWizard_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
if (!CustomValidatorValidateMapping.IsValid)
{
return;
}
string appendMode = ViewState["AppendMode"].ToString();
Dictionary <string, string> columnMapping = new Dictionary <string, string>();
foreach (GridViewRow row in dgvColumns.Rows)
{
string excelColumn = row.Cells[0].Text;
string dbColumn = (row.Cells[1].Controls[1] as DropDownList).SelectedValue;
if (dbColumn.Equals(ExcelUtils.IGNORE_COLUMN_VALUE, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
columnMapping.Add(dbColumn, excelColumn);
}
MemoryStream stream = new MemoryStream(ViewState["Stream"] as byte[]);
DataTable dt;
using (ExcelPackage spreadSheetDocument = new ExcelPackage(stream))
{
dt = ExcelUtils.GetDataTableFromExcel(spreadSheetDocument, true);
}
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
if (appendMode == "Overwrite")
{
foreach (var sp in context.Species)
{
sp.CharacterStates.Clear();
context.Species.Remove(sp);
}
foreach (var cs in context.CharacterStates)
{
cs.DisabledCharacters.Clear();
}
foreach (var st in context.CharacterStates)
{
context.CharacterStates.Remove(st);
}
foreach (var ch in context.Characters)
{
context.Characters.Remove(ch);
}
foreach (var cat in context.CharacterCategories)
{
context.CharacterCategories.Remove(cat);
}
context.SaveChanges();
}
foreach (DataRow dataRow in dt.Rows)
{
string categoryName = ExcelUtils.GetExcelRowValueForColumn(dataRow, FriendlyColumnNames.CategoryName, columnMapping).ToString();
string characterCode = ExcelUtils.GetExcelRowValueForColumn(dataRow, FriendlyColumnNames.CharacterCode, columnMapping).ToString();
string characterDesc = ExcelUtils.GetExcelRowValueForColumn(dataRow, FriendlyColumnNames.CharacterDescription, columnMapping).ToString();
string stateCode = ExcelUtils.GetExcelRowValueForColumn(dataRow, FriendlyColumnNames.CharacterStateCode, columnMapping).ToString();
string stateDesc = ExcelUtils.GetExcelRowValueForColumn(dataRow, FriendlyColumnNames.CharacterStateDescription, columnMapping).ToString();
CharacterCategory cat = context.CharacterCategories.SingleOrDefault(i => i.CategoryName == categoryName);
if (cat == null)
{
cat = context.CharacterCategories.Create();
cat.AssignNewdCharacterCategoryId(context);
cat.CategoryName = categoryName;
context.CharacterCategories.Add(cat);
}
Character cha = context.Characters.SingleOrDefault(i => i.CharacterCode == characterCode);
if (cha == null)
{
cha = context.Characters.Create();
cha.AssignNewdCharacterId(context);
cha.CharacterCode = characterCode;
cha.CharacterDescription = characterDesc;
cat.Characters.Add(cha);
CharacterState st = context.CharacterStates.Create();
st.AssignNewCharacterStateId(context);
st.CharacterStateCode = stateCode;
st.CharacterStateDescription = stateDesc;
cha.CharacterStates.Add(st);
}
else
{
CharacterState st = context.CharacterStates.SingleOrDefault(i => i.CharacterStateCode == characterCode);
if (st == null)
{
st = context.CharacterStates.Create();
st.AssignNewCharacterStateId(context);
st.CharacterStateCode = stateCode;
st.CharacterStateDescription = stateDesc;
cha.CharacterStates.Add(st);
}
}
context.SaveChanges();
}
context.SaveChanges();
}
}
protected void CustomValidatorValidateMapping_ServerValidate(object source, ServerValidateEventArgs args)
{
string currentCol = string.Empty;
Dictionary <string, string> columnMapping = new Dictionary <string, string>();
try
{
foreach (GridViewRow row in dgvColumns.Rows)
{
string excelColumn = row.Cells[0].Text;
string dbColumn = (row.Cells[1].Controls[1] as DropDownList).SelectedValue;
if (dbColumn.Equals(ExcelUtils.IGNORE_COLUMN_VALUE, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
currentCol = dbColumn;
columnMapping.Add(dbColumn, excelColumn);
lblNoError.Text = "No errors found. Click Finish to Import.";
}
}
catch (Exception ex)
{
CustomValidatorValidateMapping.ErrorMessage = string.Format("{0} mapped twice!", currentCol);
lblNoError.Text = "";
args.IsValid = false;
}
MemoryStream stream = new MemoryStream(ViewState["Stream"] as byte[]);
DataTable dt = new DataTable();
using (ExcelPackage spreadSheetDocument = new ExcelPackage(stream))
{
try
{
dt = ExcelUtils.GetDataTableFromExcel(spreadSheetDocument, true);
}
catch (Exception ex)
{
CustomValidatorValidateMapping.ErrorMessage = ex.Message;
}
}
string speciesColName = ExcelUtils.GetExcelColumnName(FriendlyColumnNames.SpeciesName, columnMapping);
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
foreach (DataColumn dtColumn in dt.Columns)
{
if (!dtColumn.ColumnName.Equals(speciesColName))
{
var colName = dtColumn.ColumnName;
Character cha = context.Characters.SingleOrDefault(i => i.CharacterCode == colName);
if (cha == null)
{
CustomValidatorValidateMapping.ErrorMessage = string.Format("Cannot find Character Code: {0} in the database!", colName);
lblNoError.Text = "";
args.IsValid = false;
break;
}
foreach (DataRow dtRow in dt.Rows)
{
string stateCode = Convert.ToString(ExcelUtils.GetExcelRowValueForColumn(dtRow, colName));
if (!string.IsNullOrEmpty(stateCode))
{
bool validStateCode = cha.CharacterStates.Any(i => i.CharacterStateCode == stateCode);
if (!validStateCode)
{
CustomValidatorValidateMapping.ErrorMessage = string.Format("Cannot find Character State Code: {0} under Character: {1} in the database!", stateCode, colName);
lblNoError.Text = "";
args.IsValid = false;
break;
}
}
}
}
}
}
}
public static int AssignNewdCharacterCategoryId(this CharacterCategory category, InteractiveKeyEntities context)
{
int id = 0;
try
{
id = context.CharacterCategories.Max(c => c.CategoryID);
}
catch (Exception)
{
}
category.CategoryID = id + 1;
return(id + 1);
}
public static int AssignNewCharacterStateId(this Application.CharacterState characterState, InteractiveKeyEntities context)
{
int id = 0;
try
{
id = context.CharacterStates.Max(s => s.CharacterStateID);
}
catch (Exception)
{
}
characterState.CharacterStateID = id + 1;
return(id + 1);
}
protected void ExcelImportWizard_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
if (!CustomValidatorValidateMapping.IsValid)
{
return;
}
string appendMode = ViewState["AppendMode"].ToString();
Dictionary <string, string> columnMapping = new Dictionary <string, string>();
foreach (GridViewRow row in dgvColumns.Rows)
{
string excelColumn = row.Cells[0].Text;
string dbColumn = (row.Cells[1].Controls[1] as DropDownList).SelectedValue;
if (dbColumn.Equals(ExcelUtils.IGNORE_COLUMN_VALUE, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
columnMapping.Add(dbColumn, excelColumn);
}
MemoryStream stream = new MemoryStream(ViewState["Stream"] as byte[]);
DataTable dt;
using (ExcelPackage spreadSheetDocument = new ExcelPackage(stream))
{
dt = ExcelUtils.GetDataTableFromExcel(spreadSheetDocument, true);
}
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
if (appendMode == "Overwrite")
{
// remove existing species and their character states
foreach (var sp in context.Species)
{
var states = sp.CharacterStates.ToArray();
foreach (var state in states)
{
sp.CharacterStates.Remove(state);
}
context.Species.Remove(sp);
}
context.SaveChanges();
}
string excelSpeciesColName = ExcelUtils.GetExcelColumnName(FriendlyColumnNames.SpeciesName, columnMapping);
foreach (DataRow dataRow in dt.Rows)
{
string speciesName = ExcelUtils.GetExcelRowValueForColumn(dataRow, excelSpeciesColName).ToString().Trim();
Species sp = context.Species.SingleOrDefault(i => i.SpeciesName == speciesName);
if (sp == null)
{
sp = context.Species.Create();
sp.AssignNewSpeciesId(context);
sp.SpeciesName = speciesName;
context.Species.Add(sp);
}
foreach (DataColumn col in dataRow.Table.Columns)
{
string colName = col.ColumnName;
if (colName != columnMapping[FriendlyColumnNames.SpeciesName.ToUpper()]) //not species column
{
Character cha = context.Characters.SingleOrDefault(i => i.CharacterCode == colName);
if (cha == null)
{
//excel contains non character code columns -- Error
throw new Exception(colName + " is not a valid Character Code. Please correct this and re-import.");
}
else
{
string stateCode = dataRow[colName].ToString().Trim();
if (!string.IsNullOrEmpty(stateCode) && stateCode != "-")
{
CharacterState st = context.CharacterStates.SingleOrDefault(i => i.CharacterID == cha.CharacterID && i.CharacterStateCode == stateCode);
if (st == null)
{
string errMsg = string.Format("{0} is not a valid Character State for the character: {1} for {2}. Please correct this and re-import.", stateCode, cha.CharacterCode, speciesName);
throw new Exception(errMsg);
}
else
{
sp.CharacterStates.Add(st);
}
}
}
}
}
context.SaveChanges();
}
context.SaveChanges();
}
}
