//get elw drop event list: FM for (dd-mm-yy) (Morning).xls
private List <AddDropChangeInfo> GetELWDropEventList(Worksheet worksheet, int lastUsedRow)
{
List <AddDropChangeInfo> elwDropEventList = new List <AddDropChangeInfo>();
int startPos = 1;
for (int i = startPos; i <= lastUsedRow; i++)
{
if (ExcelUtil.GetRange(i, 1, worksheet).Text != null && ExcelUtil.GetRange(i, 1, worksheet).Text.ToString().ToUpper() == "DROP")
{
startPos = i;
break;
}
}
for (int i = startPos + 2; i <= lastUsedRow; i++)
{
if (ExcelUtil.GetRange(i, 1, worksheet).Text != null && ExcelUtil.GetRange(i, 1, worksheet).Text.ToString() != string.Empty)
{
AddDropChangeInfo changeInfo = new AddDropChangeInfo();
changeInfo.ChangeType = "Delete";
changeInfo.Date = DateTime.ParseExact(ExcelUtil.GetRange(i, 2, worksheet).Text.ToString().Trim(), "dd-MMM-yy", null);
changeInfo.DescriptionWas = ExcelUtil.GetRange(i, 5, worksheet).Text.ToString().Trim();
//changeInfo.DescriptionNow = "";
changeInfo.RicWas = ExcelUtil.GetRange(i, 3, worksheet).Text.ToString().Trim();
//changeInfo.RicNow = "";
changeInfo.ISINWas = ExcelUtil.GetRange(i, 6, worksheet).Text.ToString().Trim();
//changeInfo.ISINNow = "";
changeInfo.SecondWas = ExcelUtil.GetRange(i, 7, worksheet).Text.ToString().Trim();
//changeInfo.SecondNow = "";
changeInfo.Exchange = "KSC";
changeInfo.Asset = "WNT";
elwDropEventList.Add(changeInfo);
}
}
return(elwDropEventList);
}
//get PEO, PRF, REIT, ETF, CEF, ADD event list: Sample File: KR FM (PEO ADD) _ 079980.KS(wef 2012-Feb-23).xlsx and so on
private List <AddDropChangeInfo> GetPeoPrfReitCefAddEventList(Worksheet worksheet, int lastUsedRow)
{
List <AddDropChangeInfo> PEOPRFREITCefAddEventList = new List <AddDropChangeInfo>();
int startRow = 1;
for (int i = 1; i <= lastUsedRow; i++)
{
if (ExcelUtil.GetRange(i, 1, worksheet).Text != null && ExcelUtil.GetRange(i, 1, worksheet).Text.ToString() == "Updated Date")
{
startRow = i + 1;
break;
}
}
for (int i = startRow; i <= lastUsedRow; i++)
{
if (ExcelUtil.GetRange(i, 2, worksheet).Text != null && ExcelUtil.GetRange(i, 2, worksheet).Text.ToString() != string.Empty)
{
AddDropChangeInfo changeInfo = new AddDropChangeInfo();
changeInfo.ChangeType = "Add";
changeInfo.Date = DateTime.ParseExact(ExcelUtil.GetRange(i, 2, worksheet).Text.ToString().Trim(), "dd-MMM-yy", null);
//changeInfo.DescriptionWas = "";
changeInfo.DescriptionNow = ExcelUtil.GetRange(i, 7, worksheet).Text.ToString().Trim();
//changeInfo.RicWas = "";
changeInfo.RicNow = ExcelUtil.GetRange(i, 3, worksheet).Text.ToString().Trim();
//changeInfo.ISINWas = "";
changeInfo.ISINNow = ExcelUtil.GetRange(i, 8, worksheet).Text.ToString().Trim();
//changeInfo.SecondWas = "";
changeInfo.SecondNow = ExcelUtil.GetRange(i, 9, worksheet).Text.ToString().Trim();
if (changeInfo.RicNow.EndsWith(".KS"))
{
changeInfo.Exchange = "KSC";
}
else if (changeInfo.RicNow.EndsWith(".KQ"))
{
changeInfo.Exchange = "KOE";
}
else if (changeInfo.RicNow.EndsWith(".KN"))
{
changeInfo.Exchange = "KNX";
}
if (ExcelUtil.GetRange(i, 4, worksheet).Text.ToString().Trim() == "REIT")
{
changeInfo.Asset = "REI";
}
else if (ExcelUtil.GetRange(i, 4, worksheet).Text.ToString().Trim() == "KDR")
{
changeInfo.Asset = "DRC";
}
else
{
changeInfo.Asset = ExcelUtil.GetRange(i, 4, worksheet).Text.ToString().Trim();
}
PEOPRFREITCefAddEventList.Add(changeInfo);
}
}
return(PEOPRFREITCefAddEventList);
}
//get rights and company warrant drop event list
//Sample files: KR FM (Right DROP) Request_ 027970_r.KS(wef 2012-MAR-07).xls
//KR FM (Company Warrant Drop) Request_068630W.KQ (wef 2011-Sep-28).xls
private List <AddDropChangeInfo> GetRightsAndCompanyWarrantDropEventList(Worksheet worksheet, int lastUsedRow, string type)
{
List <AddDropChangeInfo> rightsAndCompanyWarrantsDropEventList = new List <AddDropChangeInfo>();
int currentRow = 3;
while (currentRow <= lastUsedRow)
{
if (ExcelUtil.GetRange(currentRow, 3, worksheet).Text != null && ExcelUtil.GetRange(currentRow, 3, worksheet).Text.ToString() != string.Empty)
{
AddDropChangeInfo changeInfo = new AddDropChangeInfo();
changeInfo.ChangeType = "Delete";
changeInfo.Date = DateTime.ParseExact(ExcelUtil.GetRange(currentRow, 3, worksheet).Text.ToString().Trim(), "yyyy-MMM-dd", null);
changeInfo.DescriptionWas = ExcelUtil.GetRange(currentRow + 3, 3, worksheet).Text.ToString().Trim();
//changeInfo.DescriptionNow = "";
changeInfo.RicWas = ExcelUtil.GetRange(currentRow + 1, 3, worksheet).Text.ToString().Trim();
//changeInfo.RicNow = "";
changeInfo.ISINWas = ExcelUtil.GetRange(currentRow + 2, 3, worksheet).Text.ToString().Trim();
//changeInfo.ISINNow = "";
Regex r = new Regex("\\d{8}");
Match m = r.Match(changeInfo.ISINWas);
changeInfo.SecondWas = m.Value;
//changeInfo.SecondNow = "";
if (changeInfo.RicWas.EndsWith(".KS"))
{
changeInfo.Exchange = "KSC";
}
else if (changeInfo.RicWas.EndsWith(".KQ"))
{
changeInfo.Exchange = "KOE";
}
else if (changeInfo.RicNow.EndsWith(".KN"))
{
changeInfo.Exchange = "KNX";
}
changeInfo.Asset = type;
rightsAndCompanyWarrantsDropEventList.Add(changeInfo);
currentRow += 11;
}
else
{
break;
}
}
return(rightsAndCompanyWarrantsDropEventList);
}
//get name change event list:
//Sample file: KR FM(Name Change)Korea FM_123410.KQ (wef 02-MAR-12).xlsx
private List <AddDropChangeInfo> GetNameChangeEventList(Worksheet worksheet, int lastUsedRow)
{
List <AddDropChangeInfo> nameChangeEventList = new List <AddDropChangeInfo>();
for (int i = 2; i <= lastUsedRow; i++)
{
AddDropChangeInfo changeInfo = new AddDropChangeInfo();
changeInfo.ChangeType = "Change";
changeInfo.Date = DateTime.ParseExact(ExcelUtil.GetRange(i, 2, worksheet).Text.ToString().Trim(), "dd-MMM-yy", null);
changeInfo.DescriptionWas = ExcelUtil.GetRange(i, 11, worksheet).Text.ToString().Trim();
changeInfo.DescriptionNow = ExcelUtil.GetRange(i, 12, worksheet).Text.ToString().Trim();
changeInfo.RicWas = ExcelUtil.GetRange(i, 3, worksheet).Text.ToString().Trim();
changeInfo.RicNow = ExcelUtil.GetRange(i, 4, worksheet).Text.ToString().Trim();
changeInfo.ISINWas = ExcelUtil.GetRange(i, 5, worksheet).Text.ToString().Trim();
changeInfo.ISINNow = ExcelUtil.GetRange(i, 6, worksheet).Text.ToString().Trim();
changeInfo.SecondWas = ExcelUtil.GetRange(i, 7, worksheet).Text.ToString().Trim();
changeInfo.SecondNow = ExcelUtil.GetRange(i, 8, worksheet).Text.ToString().Trim();
if (changeInfo.RicWas.EndsWith(".KS"))
{
changeInfo.Exchange = "KSC";
}
else if (changeInfo.RicWas.EndsWith(".KQ"))
{
changeInfo.Exchange = "KOE";
}
else if (changeInfo.RicNow.EndsWith(".KN"))
{
changeInfo.Exchange = "KNX";
}
string ric = changeInfo.RicNow.Remove(changeInfo.RicNow.IndexOf('.'));
if (ric.EndsWith("5") || ric.EndsWith("7") || ric.EndsWith("9"))
{
changeInfo.Asset = "PRF";
}
else
{
changeInfo.Asset = "ORD";
}
nameChangeEventList.Add(changeInfo);
}
return(nameChangeEventList);
}
//get PEO, PRF, REIT, ETF, CEF, drop
//Sample files: KR FM (Drop) Request_134000.KS (wef 2012-Mar-02).xls
//KR FM (Right DROP) Request_ 027970_r.KS(wef 2012-MAR-07).xls
//KR_FM (ETF Drop) Request_097730.KS (wef 2009-AUG-11).xls
private List <AddDropChangeInfo> GetPeoPrfReitCefDropEventList(Worksheet worksheet, int lastUsedRow, string type)
{
List <AddDropChangeInfo> peoPrfReitCefDropEventList = new List <AddDropChangeInfo>();
int currentRow = 3;
while (currentRow <= lastUsedRow)
{
AddDropChangeInfo changeInfo = new AddDropChangeInfo();
changeInfo.ChangeType = "Delete";
changeInfo.Date = DateTime.ParseExact(ExcelUtil.GetRange(currentRow, 3, worksheet).Text.ToString().Trim(), "yyyy-MMM-dd", null);
changeInfo.DescriptionWas = ExcelUtil.GetRange(currentRow + 3, 3, worksheet).Text.ToString().Trim();
//changeInfo.DescriptionNow = "";
changeInfo.RicWas = ExcelUtil.GetRange(currentRow + 1, 3, worksheet).Text.ToString().Trim();
//changeInfo.RicNow = "";
changeInfo.ISINWas = ExcelUtil.GetRange(currentRow + 2, 3, worksheet).Text.ToString().Trim();
//changeInfo.ISINNow = "";
changeInfo.SecondWas = changeInfo.RicWas.Remove(changeInfo.RicWas.IndexOf('.'));
//changeInfo.SecondNow = "";
if (changeInfo.RicWas.EndsWith(".KS"))
{
changeInfo.Exchange = "KSC";
}
else if (changeInfo.RicWas.EndsWith(".KQ"))
{
changeInfo.Exchange = "KOE";
}
else if (changeInfo.RicNow.EndsWith(".KN"))
{
changeInfo.Exchange = "KNX";
}
//TO DO: Depends on the file name
changeInfo.Asset = type;
peoPrfReitCefDropEventList.Add(changeInfo);
currentRow += 7;
while ((ExcelUtil.GetRange(currentRow, 1, worksheet).Text == null || ExcelUtil.GetRange(currentRow, 1, worksheet).Text.ToString().Trim() == string.Empty) && currentRow < lastUsedRow)
{
currentRow++;
}
}
return(peoPrfReitCefDropEventList);
}
//get company warrant add event list
//Sample files: KR FM(Company Warrant ADD)Request_102940W.KQ (wef 2011-SEP-09) .xls
//FM(Right Add)_027970_r.KS (wef 2012-FEB-28).xls
private List <AddDropChangeInfo> GetRightsAndCompanyWarrantAddEventList(Worksheet worksheet, int lastUsedRow, string type)
{
List <AddDropChangeInfo> RightsAndcompamyWarrantddEventList = new List <AddDropChangeInfo>();
int currentRow = 3;
while (currentRow <= lastUsedRow)
{
if (ExcelUtil.GetRange(currentRow, 1, worksheet).Text != null && ExcelUtil.GetRange(currentRow, 1, worksheet).Text.ToString() != string.Empty)
{
AddDropChangeInfo changeInfo = new AddDropChangeInfo();
changeInfo.ChangeType = "Add";
changeInfo.Date = DateTime.ParseExact(ExcelUtil.GetRange(currentRow, 3, worksheet).Text.ToString().Trim(), "yyyy-MMM-dd", null);
//changeInfo.DescriptionWas = "";
changeInfo.DescriptionNow = ExcelUtil.GetRange(currentRow + 4, 3, worksheet).Text.ToString().Trim();
//changeInfo.RicWas = "";
changeInfo.RicNow = ExcelUtil.GetRange(currentRow + 1, 3, worksheet).Text.ToString().Trim();
//changeInfo.ISINWas = "";
changeInfo.ISINNow = ExcelUtil.GetRange(currentRow + 7, 3, worksheet).Text.ToString().Trim();
//changeInfo.SecondWas = "";
changeInfo.SecondNow = ExcelUtil.GetRange(currentRow + 6, 3, worksheet).Text.ToString().Trim();
if (changeInfo.RicNow.EndsWith(".KS"))
{
changeInfo.Exchange = "KSC";
}
else if (changeInfo.RicNow.EndsWith(".KQ"))
{
changeInfo.Exchange = "KOE";
}
else if (changeInfo.RicNow.EndsWith(".KN"))
{
changeInfo.Exchange = "KNX";
}
changeInfo.Asset = type;
RightsAndcompamyWarrantddEventList.Add(changeInfo);
currentRow += 24;
}
else
{
break;
}
}
return(RightsAndcompamyWarrantddEventList);
}