public static List <ISINClass> GetCommitments(DataTable CommittmentDT)
{
List <ISINClass> ISINList = new List <ISINClass>();
try
{
var TransList = from Trans in CommittmentDT.AsEnumerable()
select new
{
ISIN_CODE = Trans.Field <string>("ISIN_CODE"),
ISIN_CCY = Trans.Field <string>("ISIN_CCY"),
ISIN_FULL_NAME = Trans.Field <string>("ISIN_FULL_NAME"),
ISIN_SHORT_NAME = Trans.Field <string>("ISIN_SHORT_NAME"),
ISIN_ID = Trans.Field <int?>("ISIN_ID")
};
foreach (var isins in TransList.ToList())
{
ISINClass isinclass = new ISINClass();
isinclass.ISIN_CCY = isins.ISIN_CCY;
isinclass.ISIN_CODE = isins.ISIN_CODE;
isinclass.ISIN_FULL_NAME = isins.ISIN_FULL_NAME;
isinclass.ISIN_SHORT_NAME = isins.ISIN_SHORT_NAME;
isinclass.ISIN_ID = isins.ISIN_ID.ToString();
ISINList.Add(isinclass);
}
}
catch (Exception ex)
{
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(DateTime.Now.ToString() + "\t" + ex.Message);
}
return(ISINList);
}
private ClientPortfolio GetTransactions(string AccountNumber, string StartDate, string EndDate)
{
byte[] bs;
string schema;
string TimeZone = Properties.Settings.Default.TimeInfo.ToString();
string strtDate = StartDate + TimeZone;
string edDate = EndDate + TimeZone;
string fileName = Properties.Settings.Default.LIST_TRANSACTION_QUERY.ToString();
int RowCount = 10;
RowCount = Properties.Settings.Default.DEFAULT_ROW_COUNT;
string TemplateFileName = HttpContext.Current.Server.MapPath("~/" + fileName);
string filter = FileToString(TemplateFileName);
filter = filter.Replace("@ACCOUNT_REFERENCE", AccountNumber);
filter = filter.Replace("@StartDate", strtDate);
filter = filter.Replace("@EndDate", edDate);
ClientPortfolio clientPortfolio = new ClientPortfolio();
List <InstrumentInfo> instrumentInfoList = new List <InstrumentInfo>();
BrokerInfo brokerInfo = new BrokerInfo();
AccountDetails accountDetails = new AccountDetails();
try
{
rt = mid.DataSetListZIP(SessionID, "LIST_TRANSACTION.1", RowCount, filter, out schema, out bs);
if (rt.HasError) // lets see what server thinks about that
{
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(DateTime.Now.ToString() + "\t" + rt.ErrorInfo.ErrorReference + "\t" + rt.ErrorInfo.ErrorText);
}
else
{
/* unzip output from server */
DataSet opListDS = Utils.unZipDS(bs, schema);
DataTable Transacitons = opListDS.Tables["LIST_TRANSACTION"];
var TransactionList = from Trans in Transacitons.AsEnumerable()
select new
{
TransactionID = Trans.Field <int>("TRANS_ID"),
TransactionQty = Trans.Field <decimal>("BALCHANGE_QTY"),
TransactionISIN = Trans.Field <int?>("BALCHANGE_ISIN"),
TransactionAccount = Trans.Field <int?>("BALCHANGE_ACCOUNT"),
TransactionDate = Trans.Field <DateTime?>("BALCHANGE_DATE"),
TransactionTypeDesc = Trans.Field <string>("TRANTYPE_DESCRIPTION"),
ISINCode = Trans.Field <string>("ISIN_CODE"),
TransactionPrice = Trans.Field <decimal?>("TRANS_PRICE"),
TransactionValue = Trans.Field <decimal?>("TRANS_VALUE"),
// Name = Trans.Field<string>("NAME_BLOCK_1"),
BrokerName = Trans.Field <string>("TRANS_BROKER"),
BrokerCode = Trans.Field <string>("TRANS_BROKER"),
TransactionMatch = Trans.Field <int?>("TRANS_MATCH")
};
}
}
catch (Exception ex)
{
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(DateTime.Now.ToString() + "\t" + ex.Message);
}
finally
{
mid.Close();
}
return(clientPortfolio);
}
private IEnumerable <Transaction> GetTransactions(string AccountNumber, string StartDate, string EndDate)
{
byte[] bs;
string schema;
string StartdateTemplate = Properties.Settings.Default.StartDate_Template.ToString();
string EnddateTemplate = Properties.Settings.Default.EndDate_Template.ToString();
string TimeZone = Properties.Settings.Default.TimeInfo.ToString();
string strtDate = StartDate + TimeZone;
string edDate = EndDate + TimeZone;
string TransQuery = Properties.Settings.Default.LIST_TRANSACTION_QUERY.ToString();
string ISINQuery = Properties.Settings.Default.LIST_ISIN_QUERY.ToString();
string TransactionOperationName = Properties.Settings.Default.LIST_TRANSACTION_OPNAME.ToString();
string TransactionTableName = Properties.Settings.Default.LIST_TRANSACTION_TABLE.ToString();
int RowCount = 10;
RowCount = Properties.Settings.Default.DEFAULT_ROW_COUNT;
StartDate = StartDate.Insert(6, "-");
StartDate = StartDate.Insert(4, "-");
StartDate = string.Format(StartdateTemplate, StartDate);
EndDate = EndDate.Insert(6, "-");
EndDate = EndDate.Insert(4, "-");
EndDate = string.Format(EnddateTemplate, EndDate);
DateTime Start = DateTime.Parse(StartDate);
DateTime End = DateTime.Parse(EndDate);
string TemplateFileName = HttpContext.Current.Server.MapPath("~/" + TransQuery);
string Transfilter = FileToString(TemplateFileName);
Transfilter = Transfilter.Replace("@ACCOUNT_REFERENCE", AccountNumber);
TemplateFileName = HttpContext.Current.Server.MapPath("~/" + ISINQuery);
string ISINfilter = FileToString(TemplateFileName);
List <ISINClass> ISINList = null;
List <Transaction> TransactionList = new List <Transaction>();
List <Commitments> CommitmentList = new List <Commitments>();
try
{
rt = mid.DataSetListZIP(SessionID, TransactionOperationName, RowCount, Transfilter, out schema, out bs);
if (rt.HasError) // lets see what server thinks about that
{
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(DateTime.Now.ToString() + "\t" + rt.ErrorInfo.ErrorReference + "\t" + rt.ErrorInfo.ErrorText);
}
else
{
DataSet opListDS = Utils.unZipDS(bs, schema);
DataTable Transactions = opListDS.Tables[TransactionTableName];
string expression = null;
//Get Symbols
schema = null;
bs = null;
rt = null;
rt = mid.DataSetListZIP(SessionID, "LIST_ISIN.1", RowCount, ISINfilter, out schema, out bs);
if (rt.HasError)
{
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(DateTime.Now.ToString() + "\t" + rt.ErrorInfo.ErrorReference + "\t" + rt.ErrorInfo.ErrorText);
}
else
{
opListDS = Utils.unZipDS(bs, schema);
DataTable ISINS = opListDS.Tables["LIST_ISIN"];
try
{
ISINList = DependAPIOperation.GetInstruments(ISINS);
}
catch (Exception ex)
{
ISINList = null;
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(DateTime.Now.ToString() + "\t" + ex.Message);
}
}
string Commitmentfilter = null;
try
{
Commitmentfilter = FileToString(HttpContext.Current.Server.MapPath("~/" + Properties.Settings.Default.LIST_COMMIT_QUERY.ToString()));
string AccountID = AccountNumber.Substring(0, AccountNumber.Length - 1);
Commitmentfilter = Commitmentfilter.Replace("@ACCOUNT_ID", AccountID);
}
catch (Exception exp)
{
Commitmentfilter = "";
}
string CommittmentOpName = Properties.Settings.Default.LIST_COMMIT_OPNAME.ToString();
//List<Commitments> commitmentslist
CommitmentList = GetCommitments(SessionID, CommittmentOpName, RowCount, Commitmentfilter, out schema, out bs, Start, End);
var TransList = from Trans in Transactions.AsEnumerable()
.Where(t => t.Field <DateTime>("BALCHANGE_DATE") >= Start &&
t.Field <DateTime>("BALCHANGE_DATE") <= End)
select new
{
TransactionID = Trans.Field <int>("TRANS_ID"),
TransactionQty = Trans.Field <decimal>("BALCHANGE_QTY"),
TransactionISIN = Trans.Field <int?>("BALCHANGE_ISIN"),
TransactionAccount = Trans.Field <int?>("BALCHANGE_ACCOUNT"),
TransactionDate = Trans.Field <DateTime?>("BALCHANGE_DATE"),
TransactionType = Trans.Field <string>("TRANS_TYPE"),
TransactionTypeDesc = Trans.Field <string>("TRANTYPE_DESCRIPTION"),
ISINCode = Trans.Field <string>("ISIN_CODE"),
ISINSHORTName = Trans.Field <string>("ISIN_SHORT_NAME"),
TransactionPrice = Trans.Field <decimal?>("TRANS_PRICE"),
TransactionValue = Trans.Field <decimal?>("TRANS_VALUE"),
BalTimeBal = Trans.Field <decimal?>("BALTIME_BALANCE"),
BrokerCode = Trans.Field <string>("TRANS_BROKER"),
TransactionMatch = Trans.Field <int?>("TRANS_MATCH")
};
foreach (var Trans in TransList.ToList())
{
Transaction transaction = new Transaction();
transaction.AccountReference = AccountNumber.Trim();
transaction.TransactionID = Trans.TransactionID;
transaction.TransactionPrice = Trans.TransactionPrice == null ? null : Trans.TransactionPrice;
transaction.TransactionValue = Trans.TransactionValue == null ? null : Trans.TransactionValue;
transaction.TransactionVolume = Trans.TransactionQty;
transaction.BrokerCode = Trans.BrokerCode;
transaction.TransactionTypeID = Trans.TransactionType;
transaction.InstrumentCode = Trans.ISINSHORTName;
transaction.InstrumentISINCode = Trans.ISINCode;
transaction.Available = Trans.BalTimeBal;
transaction.Owned = Trans.BalTimeBal;
transaction.TradeDate = Convert.ToInt32(Convert.ToDateTime(Trans.TransactionDate).ToString("yyyyMMdd"));
/* Should be Removed. */
transaction.BrokerID = "";
transaction.BrokerCode = "";
transaction.BrokerName = "";
if (ISINList == null || ISINList.Count == 0)
{
transaction.InstrumentName = "";
}
else
{
try
{
string IsINName = ISINList.First(a => a.ISIN_CODE == transaction.InstrumentISINCode).ISIN_FULL_NAME;
transaction.InstrumentName = IsINName;
}
catch (Exception ex)
{
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(DateTime.Now.ToString() + "\t" + ex.Message);
transaction.InstrumentName = "";
}
}
transaction.TransactionDate = Convert.ToInt32(Convert.ToDateTime(Trans.TransactionDate).ToString("yyyyMMdd"));
transaction.TransactionDescription = Trans.TransactionTypeDesc.ToString();
TransactionList.Add(transaction);
}
}
}
catch (Exception ex)
{
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(DateTime.Now.ToString() + "\t" + ex.Message);
}
finally
{
mid.Close();
}
//Merging List
return(MergeTransactionAndCommitment(TransactionList, CommitmentList));
// return TransactionList;
}
private void btnPivot_Click(object sender, EventArgs e)
{
DataTable dt = (DataTable)dgvDocs.DataSource;
// DataRow[] dr = dt.Select("Select Symbol, Sum(ProfitnLoss)");
var results = from Trans in dt.AsEnumerable()
where Trans.Field <decimal>("SellProfitPercent") == 0.5m
select Trans;
// DataTable view = results.CopyToDataTable();
var dataRow = dt.AsEnumerable().Where(x => x.Field <decimal>("SellProfitPercent") == 0.5m)
.Select(x => new { Name = x.Field <string>("Symbol"), profit = x.Field <decimal>("ProfitnLoss") })
.GroupBy(x => x.Name)
.Select(x => new
{
Name = Name,
Profit = x.Sum(y => y.profit)
});
var rst = (from row in dt.AsEnumerable()
group row by row.Field <string>("Symbol") into grp
select new
{
Name = grp.Key,
Sum = (from r in grp where r.Field <decimal>("SellProfitPercent") == 0.5m select r.Field <decimal>("ProfitnLoss")).Sum()
}).ToList();
var totalPnlList = (from row in dt.AsEnumerable()
group row by row.Field <decimal>("SellProfitPercent") into grp
select new
{
Name = grp.Key,
Sum = (from r in grp select r.Field <decimal>("ProfitnLoss")).Sum()
}).ToList();
var pnlDayList = (from row in dt.AsEnumerable()
group row by new
{
Date = row.Field <DateTime>("ModifiedOn"),
SPP = row.Field <decimal>("SellProfitPercent"),
MPL = row.Field <int>("MinPositionLevel")
} into grp
select new
{
Name = grp.Key,
Sum = (from r in grp select r.Field <decimal>("ProfitnLoss")).Sum()
}).ToList();
DataTable newItems = new DataTable();
newItems.Columns.Add("Name");
newItems.Columns.Add("Sum");
foreach (var item in pnlDayList)
{
DataRow newRow = newItems.NewRow();
newRow["Name"] = item.Name;
newRow["Sum"] = item.Sum;
newItems.Rows.Add(newRow);
}
frmStrategyPerformance frmPro = new frmStrategyPerformance();
frmPro.pnlDayDT = newItems;
frmPro.Show();
}