public void ReadExcelData(XLWorkbook workBook)
{
JuryList jurylsit = JuryList.GetJuryList();
if (workBook.Worksheets.Count > 0)
{
int sheetNo = 1;
foreach (IXLWorksheet sh in workBook.Worksheets)
{
var dataRange = sh.RangeUsed();
int skiprows = 1;
if (dataRange != null)
{
foreach (var row in dataRange.Rows())
{
if (skiprows >= 2 && sheetNo == 1)
{
if (ValidateRow(row))
{
Jury jury = null;
string serialNo = RemoveControlChars(row.Cell(3).GetString());
try
{
jury = jurylsit.Where(m => m.SerialNo.Equals(serialNo)).Single();
}
catch { }
if (jury == null)
{
jury = Jury.NewJury();
}
if (jury.IsNew)
{
jury.Type = RemoveControlChars(row.Cell(2).GetString());
jury.Salutation = RemoveControlChars(row.Cell(4).GetString());
jury.FirstName = RemoveControlChars(row.Cell(5).GetString());
jury.LastName = RemoveControlChars(row.Cell(6).GetString());
jury.Designation = RemoveControlChars(row.Cell(7).GetString());
jury.Company = RemoveControlChars(row.Cell(8).GetString());
jury.Network = RemoveControlChars(row.Cell(9).GetString());
jury.HoldingCompany = RemoveControlChars(row.Cell(10).GetString());
jury.Email = RemoveControlChars(row.Cell(11).GetString());
jury.Contact = RemoveControlChars(row.Cell(12).GetString());
jury.Mobile = RemoveControlChars(row.Cell(13).GetString());
jury.PAName = RemoveControlChars(row.Cell(14).GetString());
jury.PAEmail = RemoveControlChars(row.Cell(16).GetString());
jury.PATel = RemoveControlChars(row.Cell(15).GetString());
jury.Country = RemoveControlChars(row.Cell(17).GetString());
jury.EffieExpProgram = RemoveControlChars(row.Cell(18).GetString());
jury.Source = RemoveControlChars(row.Cell(19).GetString());
string effieExpereinceYears = string.Empty;
effieExpereinceYears += ((RemoveControlChars(row.Cell(25).GetString()).Equals("1") || RemoveControlChars(row.Cell(26).GetString()).Equals("1")) ? "2014" : "") + "#" + RemoveControlChars(row.Cell(27).GetString()) + "|";
effieExpereinceYears += "|";
effieExpereinceYears += "|";
jury.EffieExpYear = effieExpereinceYears;
jury.Remarks = RemoveControlChars(row.Cell(28).GetString());
jury.SerialNo = GeneralFunction.GetNewJuryId();
jury.DateCreatedString = DateTime.Now.ToString();
jury.Password = Guid.NewGuid().ToString().Substring(0, 6);
}
else
{
bool is2014 = jury.EffieExpYear.IndexOf("2014") != -1;
bool is2015 = jury.EffieExpYear.IndexOf("2015") != -1;
bool is2016 = jury.EffieExpYear.IndexOf("2016") != -1;
string remarks2014 = string.Empty;
string remarks2015 = string.Empty;
string remarks2016 = string.Empty;
try
{
remarks2014 = jury.EffieExpYear.Split('|')[0].Split('#')[1];
}
catch { }
try
{
remarks2015 = jury.EffieExpYear.Split('|')[1].Split('#')[1];
}
catch { }
try
{
remarks2016 = jury.EffieExpYear.Split('|')[2].Split('#')[1];
}
catch { }
string effieExpereinceYears = string.Empty;
effieExpereinceYears += ((RemoveControlChars(row.Cell(25).GetString()).Equals("1") || RemoveControlChars(row.Cell(26).GetString()).Equals("1")) ? "2014" : "") + "#" + RemoveControlChars(row.Cell(27).GetString()) + "|";
effieExpereinceYears += (is2015 ? "2015" : "") + "#" + remarks2015 + "|";
effieExpereinceYears += (is2016 ? "2016" : "") + "#" + remarks2016 + "|";
jury.EffieExpYear = effieExpereinceYears;
}
if (jury.IsValid)
{
jury = jury.Save();
}
Invitation juryInv = Invitation.NewInvitation();
juryInv.JuryId = jury.Id;
juryInv.IsRound1Invited = RemoveControlChars(row.Cell(20).GetString()).Equals("1");
juryInv.IsRound2Invited = RemoveControlChars(row.Cell(21).GetString()).Equals("1");
juryInv.IsDeclined = RemoveControlChars(row.Cell(22).GetString()).Equals("1");
juryInv.IsRound1Accepted = RemoveControlChars(row.Cell(23).GetString()).Equals("1");
juryInv.IsRound2Accepted = RemoveControlChars(row.Cell(24).GetString()).Equals("1");
juryInv.IsRound1Assigned = RemoveControlChars(row.Cell(25).GetString()).Equals("1");
juryInv.IsRound2Assigned = RemoveControlChars(row.Cell(26).GetString()).Equals("1");
juryInv.EventCode = "2014";
if (juryInv.IsValid)
{
juryInv.Save();
}
if (!IsSameCompanyDetails(jury, row))
{
SaveCompanyHistory(jury, row);
}
}
}
skiprows++;
}
}
sheetNo++;
}
}
else
{
lbError.Text += "The Sheet has invalid/not enough data.<br/>Please check your file";
}
}
private void BindGrid(bool needRebind)
{
JuryList list = JuryList.GetJuryList();
string status = (string)ViewState["TabFilterValue"];
string advanceSearch = (string)ViewState["AdvanceSearch"];
List <Jury> flist = new List <Jury>();
if (advanceSearch == "1")
{
foreach (Jury item in list)
{
if (
(ddlDelete.SelectedValue == "" || (ddlDelete.SelectedValue != "" && item.IsToDelete.ToString() == ddlDelete.SelectedValue)) &&
(ddlType.SelectedValue == "" || (ddlType.SelectedValue != "" && item.Type == ddlType.SelectedValue)) &&
(ddlNetwork.SelectedValue == "" || (ddlNetwork.SelectedValue != "" && item.Network == ddlNetwork.SelectedValue)) &&
(ddlHoldingCompany.SelectedValue == "" || (ddlHoldingCompany.SelectedValue != "" && item.HoldingCompany == ddlHoldingCompany.SelectedValue)) &&
(ddlCountry.SelectedValue == "" || (ddlCountry.SelectedValue != "" && item.Country == ddlCountry.SelectedValue)) &&
(
(txtSearch.Text.Trim() == "" || (txtSearch.Text.Trim() != "" && (ddlSearch.SelectedValue == "" || ddlSearch.SelectedValue == "judgeId") && item.SerialNo.ToUpper().IndexOf(txtSearch.Text.Trim().ToUpper()) != -1)) ||
(txtSearch.Text.Trim() == "" || (txtSearch.Text.Trim() != "" && (ddlSearch.SelectedValue == "" || ddlSearch.SelectedValue == "name") && (item.FirstName.ToUpper().IndexOf(txtSearch.Text.Trim().ToUpper()) != -1 || item.LastName.ToUpper().IndexOf(txtSearch.Text.Trim().ToUpper()) != -1))) ||
(txtSearch.Text.Trim() == "" || (txtSearch.Text.Trim() != "" && (ddlSearch.SelectedValue == "" || ddlSearch.SelectedValue == "title") && item.Designation.ToUpper().IndexOf(txtSearch.Text.Trim().ToUpper()) != -1)) ||
(txtSearch.Text.Trim() == "" || (txtSearch.Text.Trim() != "" && (ddlSearch.SelectedValue == "" || ddlSearch.SelectedValue == "company") && item.Company.ToUpper().IndexOf(txtSearch.Text.Trim().ToUpper()) != -1))
)
)
{
flist.Add(item);
}
}
}
else
{
if (status != null)
{
if (status.Equals("Current"))
{
flist = list.Where(m => !m.IsToDelete).ToList();
}
else if (status.Equals("Deleted"))
{
flist = list.Where(m => m.IsToDelete).ToList();
}
else
{
flist = list.ToList();
}
}
}
// Sort
flist = flist.OrderBy(p => p.SerialNo).ToList();
radGridJury.DataSource = flist;
if (needRebind)
{
radGridJury.Rebind();
}
GeneralFunction.SetReportDataCache(flist);
}