/// <summary>
/// Возвращает данные для Chart Графика в отчете - только один актив
/// </summary>
/// <param name="order"></param>
/// <param name="min"></param>
/// <param name="max"></param>
/// <returns></returns>
internal static Dictionary <DateTime, double> ChartInfoOneActive(string order, DateTime min, DateTime max, string activname)
{
string fromDate = SQLDateConverter.DateConverer(min);
string toDate = SQLDateConverter.DateConverer(max);
var ChartPortfell = new Dictionary <DateTime, double>();
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
string query = $@"SELECT paper.{activname} as `value`, paper.date
FROM paper
WHERE
(paper.date >= '{fromDate}' and paper.date <= '{toDate}') and paper.order = '{order}' ORDER BY date";
MySqlCommand comm = new MySqlCommand(query, conn);
try
{
conn.Open();
MySqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
ChartPortfell.Add(reader.GetDateTime(1), Convert.ToDouble(reader.GetDecimal(0)));
}
}
catch (System.ArgumentException) { }
catch (Exception ex)
{
Logger.WriteLog(ex.Message, ex.Source);
return(null);
}
return(ChartPortfell);
}
}
/// <summary>
/// Возвращает начальные Inflow Клиента - т.е. сумму всех активов которые изначально введены в фонд
/// </summary>
/// <returns></returns>
public static double StartInflo()
{
double nullInflo = 0;
var client = Storage.datasetKlient.Tables["ClientInfo"].Rows[0][6].ToString();
var registrationdate = Convert.ToDateTime(Storage.datasetKlient.Tables["ClientInfo"].Rows[0][19]);
var sqlDate = SQLDateConverter.DateConverer(registrationdate);
var sqlDateend = SQLDateConverter.DateConverer(registrationdate.AddDays(1));
string query = $@"SELECT Sum(actives_inflow.summ) FROM actives_inflow WHERE actives_inflow.order_client =
'{client}' AND actives_inflow.date >= '{sqlDate}' AND actives_inflow.date <= '{sqlDateend}'";
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
MySqlCommand comm = new MySqlCommand(query, conn);
try
{
conn.Open();
MySqlDataReader reader = comm.ExecuteReader();
if (!reader.IsClosed)
{
while (reader.Read())
{
nullInflo = reader.GetDouble(0);
}
}
}
catch (Exception ex)
{
Logger.WriteLog(ex.Message, ex.Source);
}
return(nullInflo);
}
}
/// <summary>
/// Возвращает исторические данные о состоянии счета на сегодня или вчера
/// </summary>
/// <param name="order"></param>
/// <param name="NowDate"></param>
/// <returns></returns>
internal static DataTable GetToolsCurrentPrice(string order, DateTime NowDate)
{
// string ConnectStr = "User Id=root;Password=root;Host=localhost;Database=custodian; port=3306;Charset=utf8;connection timeout = 15";
string _date = SQLDateConverter.DateConverer(NowDate);
string query = $@"SELECT
`investment_bonds`,
`speculation_bonds`,
`Credit_Linked_Notes`,
`SNGC`,
`SNCC`,
`SNRS`,
`Hi_cap_share`,
`emerging_markets`,
`mid_cap_share`,
`lo_cap`,
`options`,
`futures`,
`leveraged_ETF`,
`mutual_funds`,
`residential_properties`,
`commercial_property`,
`classics_pictures`,
`modern_pictures`,
`marks`,
`wine`
FROM
paper
WHERE
`order` = '{order}'
AND date = '{_date}'; ";
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
MySqlCommand comm = new MySqlCommand(query, conn);
try
{
conn.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(comm);
DataTable RowValues = new DataTable();
adapter.Fill(RowValues);
return(RowValues);
}
catch (Exception ex)
{
Logger.WriteLog(ex.Message, ex.Source);
return(null);
}
}
}
//..........................Графики и таблицы в отчете.....................
/// <summary>
/// Возвращает данные для Chart Графика в отчете - Полный портфель
/// </summary>
/// <param name="order"></param>
/// <param name="min"></param>
/// <param name="max"></param>
/// <returns></returns>
internal static Dictionary <DateTime, double> ChartInfoPortfell(string order, DateTime min, DateTime max)
{
string fromDate = SQLDateConverter.DateConverer(min);
string toDate = SQLDateConverter.DateConverer(max);
var ChartPortfell = new Dictionary <DateTime, double>();
ChartPortfell.Add(Convert.ToDateTime("1111-11-11"), StartInflo());
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
string query =
$@"SELECT paper.date, paper.investment_bonds + paper.speculation_bonds + paper.Credit_Linked_Notes + paper.SNGC + paper.SNCC + paper.SNRS
+ paper.Hi_cap_share + paper.emerging_markets + paper.mid_cap_share + paper.lo_cap + paper.options + paper.futures +
paper.leveraged_ETF + paper.mutual_funds + paper.residential_properties + paper.commercial_property + paper.classics_pictures +
paper.modern_pictures + paper.marks + paper.wine
+
cash_accounts.CA_USD+ cash_accounts.CA_EUR+ cash_accounts.CA_GBP + cash_accounts.CA_SGD+cash_accounts.CA_AUD+cash_accounts.cash_allocation
FROM paper
JOIN cash_accounts on
paper.date = cash_accounts.date
WHERE
(cash_accounts.date >= '{fromDate}' and cash_accounts.date <= '{toDate}') and cash_accounts.order_client = '{order}' and paper.order = '{order}' ORDER BY date
";
MySqlCommand comm = new MySqlCommand(query, conn);
try
{
conn.Open();
MySqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
ChartPortfell.Add(reader.GetDateTime(0), reader.GetDouble(1));
}
}
catch (System.ArgumentException) { }
catch (Exception ex)
{
Logger.WriteLog(ex.Message, ex.Source);
}
}
return(ChartPortfell);
}
internal static double PortfelActiveInTime(DateTime date, string order)
{
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
var _SQldate = SQLDateConverter.DateConverer(date);
string query = $@"SELECT paper.investment_bonds + paper.speculation_bonds +
paper.Credit_Linked_Notes + paper.SNGC + paper.SNCC + paper.SNRS
+ paper.Hi_cap_share + paper.emerging_markets + paper.mid_cap_share + paper.lo_cap +
paper.options + paper.futures +
paper.leveraged_ETF + paper.mutual_funds + paper.residential_properties +
paper.commercial_property + paper.classics_pictures +
paper.modern_pictures + paper.marks + paper.wine
+
cash_accounts.CA_USD + cash_accounts.CA_EUR + cash_accounts.CA_GBP + cash_accounts.CA_SGD +
cash_accounts.CA_AUD + cash_accounts.cash_allocation
FROM paper
JOIN cash_accounts on
paper.date = cash_accounts.date
WHERE
(cash_accounts.date = '{_SQldate}') and cash_accounts.order_client = '{order}' and paper.order = '{order}' ";
double TotalPort = 0;
MySqlCommand comm = new MySqlCommand(query, conn);
try
{
conn.Open();
MySqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
TotalPort = Convert.ToDouble(reader.GetString(0));
}
return(TotalPort);
}
catch (Exception ex)
{
Logger.WriteLog(ex.Message, ex.Source);
}
return(0.00000000001);
}
}
/// <summary>
/// Возвращает таблицу всех операций по клиенту - выписку
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
internal static DataTable StatementOperation(string order, DateTime from, DateTime to)
{
string datefrom = SQLDateConverter.DateConverer(from);
string dateto = SQLDateConverter.DateConverer(to.AddDays(1));
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
string query = $@"SELECT * FROM (SELECT 'Buy Operation', `Isin`,`value`,`money`,`get_inst_date`, actives_buy.aq_price, actives_buy.count_paper
FROM `actives_buy` WHERE `order_client`='{order}'
and `actives_buy`.`regist_inst_date` >= '{datefrom} ' and `actives_buy`.`regist_inst_date` <= '{dateto}'
UNION ALL
SELECT
'Divident ',
dividends_history.ISIN,
dividends_history.currency,
dividends.cash,
dividends_history.date,
dividends_history.price_for_one_asset,
dividends_history.ticket
from dividends_history
JOIN dividends on
dividends.id_dividends_history = dividends_history.id WHERE dividends.order_client = '{order}'
and dividends_history.date >= '{datefrom}' and dividends_history.date <= '{dateto}'
UNION ALL
SELECT 'Sale Operation', `ISIN`,`value`,`summ`,`regist_inst_date`, actives_sale.aq_value, actives_sale.count_paper FROM `actives_sale` WHERE `order_client`='{order}'
and actives_sale.get_inst_date >= '{datefrom}' and actives_sale.get_inst_date <= '{dateto} '
UNION ALL
SELECT 'Inflow Operation', `Isin`,`value`,`summ`,`regist_inst_date`, '','' FROM `actives_inflow` WHERE `order_client`='{order}'
and `actives_inflow`.`date` >= '{datefrom} ' and `actives_inflow`.`date` <= '{dateto} '
UNION ALL
SELECT 'Transfer Operation', `platform_type`,`Value`,`transfer_summ`,`get_inst_date`,'','' FROM `actives_transfer` WHERE `order_client`='{order}'
and actives_transfer.get_inst_date >= '{datefrom} ' and actives_transfer.get_inst_date <= '{dateto} '
UNION ALL
SELECT 'Outflow Operation', `ISIN`,`value`, `outflow_summ`,`date_operation` ,'','' FROM `actives_outflow` WHERE `order_client`='{order}'
and `actives_outflow`.`date_registr` >= '{datefrom} ' and `actives_outflow`.`date_registr` <= '{dateto} '
UNION ALL
SELECT 'Dealing Comission ', operation_commission.ISIN, operation_commission.cash_account_type, operation_commission.commission, operation_commission.date, '','' FROM
`operation_commission` WHERE operation_commission.client_order = '{order}' and operation_commission.date >= '{datefrom} ' and operation_commission.date <= '{dateto} '
Union ALL
SELECT 'Brokerage Commission of', `operation`, `cash_account_type`,`commission`,`date`, '','' FROM `broker_commission` WHERE `order_client`='{order}'
and broker_commission.date >= '{datefrom} ' AND broker_commission.date <= '{dateto} '
UNION ALL
SELECT 'Convert Operations', CONCAT('FROM ', colfrom, ' TO ', colto), CONCAT('From ',colfrvalue), CONCAT('To ',colltovalue),`date_get_instruction` , '',''
from (SELECT `value_from` as colfrom ,`value_to` as colto ,`date_create_operation`, `count_value_from` as colfrvalue,`count_value_to` as colltovalue, `date_get_instruction`
FROM `convert_operation` WHERE `order_client` ='{order}'
and convert_operation.date_get_instruction >= '{datefrom} ' and convert_operation.date_get_instruction <= '{dateto} ' )y)x order by `get_inst_date`
";
MySqlCommand comm = new MySqlCommand(query, conn);
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(comm);
try
{
conn.Open();
adapter.Fill(table);
return(table);
}
catch (Exception ex)
{
Logger.WriteLog(ex.Message, ex.Source);
return(null);
}
}
}
