DDQ/DDQ.cs

using Org.BouncyCastle.Asn1.Pkcs;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Security.Policy;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;

using static ddq.DataAccess;
using static ddq.Utility;

namespace ddq
{
    public partial class frmMain : Form
    {

        // 先hard-code公司ID，以后要按照登录用户来判断它的公司归属
        private string companyId = "CO000201PR";
        private int userId = 123;

        private DataTable fundDataTable;
        private DataTable peopleDataTable;

        public frmMain()
        {
            InitializeComponent();

            InitializeData();
        }

        private void InitializeData()
        {

        }

        private void tbcMain_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Request
            if (tbcMain.SelectedIndex == 0) { }
            // Data
            else if (tbcMain.SelectedIndex == 1)
            {

                LoadFundList(companyId);
            }
            // Setting
            else { };
        }

        private void tbcData_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (tbcData.SelectedIndex == 0)
            {
                LoadFundList(companyId);
            }
            else if (tbcData.SelectedIndex == 1)
            {
                LoadPeopleList(companyId);
            }
            else if (tbcData.SelectedIndex == 2)
            {
                InitCompanyData(companyId);
            }
        }

        //
        // Fund
        //
        private void LoadFundList(string companyId)
        {
            if (fundDataTable == null)
            {
                fundDataTable = DataAccess.Get_fund_list_by_company(companyId, 1, 1000);

                InitFundListGrid(fundDataTable);
            }
        }

        private void InitFundListGrid(DataTable dt)
        {
            this.grdFundList.DataSource = dt;

            this.grdFundList.Columns["fund_id"].Visible = true;
            this.grdFundList.Columns["company_id"].Visible = false;

            this.grdFundList.Columns["fund_short_name"].HeaderText = "Fund Name";
            this.grdFundList.Columns["fund_status"].HeaderText = "Operating Status";
            this.grdFundList.Columns["management_start_date"].HeaderText = "Inception Date";
            this.grdFundList.Columns["management_start_date"].DefaultCellStyle.Format = "yyyy-MM-dd";
            this.grdFundList.Columns["management_end_date"].HeaderText = "Liquidate Date";
            this.grdFundList.Columns["management_end_date"].DefaultCellStyle.Format = "yyyy-MM-dd";
            this.grdFundList.Columns["ret_tenure"].HeaderText = "Return Since Inception";
            this.grdFundList.Columns["ret_tenure"].DefaultCellStyle.Format = "P1";

        }

        private void grdFundList_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            int rowIndex = e.RowIndex;
            int columnIndex = e.ColumnIndex;

            if (columnIndex < 0 || rowIndex < 0) return;

            DataRow row = fundDataTable.Rows[rowIndex];

            // 弹出基金页面
            string entityId = row["fund_id"].ToString();

            if (entityId.Length == 10 && Regex.Match(entityId, "^[HM]F").Success)
            {
                FundQ fundQ = new FundQ(entityId, companyId, userId);
                fundQ.Show();
            }

        }


        //
        //  People
        //

        private void LoadPeopleList(string companyId)
        {
            if (peopleDataTable == null)
            {
                peopleDataTable = DataAccess.Get_dd_personnel_info(null, companyId);

                InitPeopleListGrid(peopleDataTable);
            }
        }

        private void InitPeopleListGrid(DataTable dt)
        {
            this.grdPeopleList.DataSource = dt;

            this.grdPeopleList.Columns["personnel_id"].Visible = false;
            this.grdPeopleList.Columns["company_id"].Visible = false;
            this.grdPeopleList.Columns["role"].Visible = false;

            this.grdPeopleList.Columns["name"].HeaderText = "Name";
            this.grdPeopleList.Columns["role_desc"].HeaderText = "Role";
            this.grdPeopleList.Columns["title"].HeaderText = "Title";
            this.grdPeopleList.Columns["industry_start_year"].HeaderText = "Industry Start Year";
            this.grdPeopleList.Columns["company_start_year"].HeaderText = "Company Start Year";
            this.grdPeopleList.Columns["bio"].HeaderText = "Bio";

        }

        private void btnAddPersonn_Click(object sender, EventArgs e)
        {
            LoadPersonnelQ(null);
        }

        private void grdPeopleList_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            int rowIndex = e.RowIndex;

            if (rowIndex < 0) return;

            string personnelId = peopleDataTable.Rows[rowIndex]["personnel_id"].ToString();

            LoadPersonnelQ(personnelId);
        }

        private void LoadPersonnelQ(string personnelId)
        {
            using (PersonnelQ dialog = new PersonnelQ(personnelId, this.companyId, this.userId))
            {
                dialog.StartPosition = FormStartPosition.CenterParent;
                DialogResult result = dialog.ShowDialog(this);

                if (result == DialogResult.OK)
                {
                    peopleDataTable = DataAccess.Get_dd_personnel_info(null, this.companyId);

                    this.grdPeopleList.DataSource = peopleDataTable;
                }

            }
        }

        //
        // Company
        //
        private void InitCompanyData(string companyId)
        {

            DataTable dt = DataAccess.Get_dd_company_info(companyId, null, 1);

            if (dt == null || dt.Rows.Count <= 0) return;


            if (dt != null && dt.Rows.Count > 0)
            {

                string jsonString = dt.Rows[0].Field<string>("info").Trim();

                JsonDocument document = JsonDocument.Parse(jsonString);
                JsonElement root = document.RootElement;

                if (root.ValueKind != JsonValueKind.Undefined)
                {
                    //
                    // General Info
                    //
                    Utility.DDData dddata = Utility.Json2Text2(root, "foundingDate");
                    if (dddata != null)
                    {
                        bool isDate = DateTime.TryParse(dddata.value.ToString(), out DateTime newDate);
                        if (isDate)
                        {
                            this.dtpIncepionDate.Value = newDate.Date;
                            this.tspInceptionDate.Text = dddata.updateTime;
                            this.tspInceptionDate.ForeColor = Utility.IsChangedRecently(dddata.updateTime) ? Color.Red : Color.DarkSlateGray;
                        }
                    }

                    this.txtDetailOfAcquired.Text = Utility.Json2Text2(root, "detailOfAcquired").value.ToString();
                    this.tspDetailOfAcquired.Text = Utility.Json2Text2(root, "detailOfAcquired").updateTime;
                    this.tspDetailOfAcquired.ForeColor = Utility.IsChangedRecently(this.tspDetailOfAcquired.Text) ? Color.Red : Color.DarkSlateGray;

                    this.txtParentCompanyName.Text = Utility.Json2Text2(root, "parentCompanyName").value.ToString();
                    this.tspParentCompanyName.Text = Utility.Json2Text2(root, "parentCompanyName").updateTime;
                    this.tspParentCompanyName.ForeColor = Utility.IsChangedRecently(this.tspParentCompanyName.Text) ? Color.Red : Color.DarkSlateGray;


                    LoadAcqusitionGrid(root);

                    LoadSupportFromParentCompany(root);

                    LoadFinancialData(root);

                    LoadCreditRating(root);

                    LoadCompanyAUM(root);

                    LoadClientBreakdown(root);

                }
            }
        }

        private void LoadAcqusitionGrid(JsonElement jsonElement)
        {

            bool hasData = jsonElement.TryGetProperty("acqusition", out JsonElement element);

            string strUpdateTime = "";

            if (hasData == true)
                hasData &= element.TryGetProperty("v", out JsonElement elm);

            DataTable dt = new DataTable();

            if (hasData == true)
            {
                Utility.DDData dddata = Utility.Json2Table2(element);

                if (dddata != null)
                {
                    dt = (DataTable)dddata.value;

                    strUpdateTime = dddata.updateTime;
                }

            } else
            {
                dt.Columns.Add("date", typeof(string));
                dt.Columns.Add("action", typeof(string));
            }

            this.grdAcquisiton.DataSource = dt;

            this.grdAcquisiton.Columns["date"].HeaderText = "Date";
            this.grdAcquisiton.Columns["date"].DefaultCellStyle.Format = "yyyy-MM-dd";
            this.grdAcquisiton.Columns["date"].DisplayIndex = 0;

            this.grdAcquisiton.Columns["action"].HeaderText = "Event";
            this.grdAcquisiton.Columns["action"].DisplayIndex = 1;

            this.tspAcqusition.Text = strUpdateTime;
            this.tspAcqusition.ForeColor = Utility.IsChangedRecently(strUpdateTime) ? Color.Red : Color.DarkSlateGray;

        }


        private void LoadSupportFromParentCompany(JsonElement jsonElement)
        {

            bool hasData = jsonElement.TryGetProperty("supportFromParent", out JsonElement element);

            string strUpdateTime = "";

            JsonElement elm = new JsonElement();

            if (hasData == true)
                hasData &= element.TryGetProperty("v", out elm);

            if (hasData == true)
            {

                this.chkSupportFinancial.Checked = Utility.Json2Boolean(elm, "financial");
                this.chkSupportSales.Checked = Utility.Json2Boolean(elm, "sales");
                this.chkSupportMarketing.Checked = Utility.Json2Boolean(elm, "marketing"); ;
                this.chkSupportTechnical.Checked = Utility.Json2Boolean(elm, "technical"); ;
                this.chkSupportPersonnel.Checked = Utility.Json2Boolean(elm, "personnel");

                hasData = element.TryGetProperty("t", out elm);
                if (hasData == true)
                    strUpdateTime = elm.ToString();
            }

            this.tspSupportFromParent.Text = strUpdateTime;
            this.tspSupportFromParent.ForeColor = Utility.IsChangedRecently(strUpdateTime) ? Color.Red : Color.DarkSlateGray;

        }

        private void LoadFinancialData(JsonElement jsonElement)
        {
            bool hasData = jsonElement.TryGetProperty("financialData", out JsonElement element);

            string strUpdateTime = "";

            if (hasData == true)
                hasData &= element.TryGetProperty("v", out JsonElement elm);

            DataTable dt = new DataTable();

            if (hasData == true)
            {

                Utility.DDData dddata = Utility.Json2Table2(element);

                if (dddata != null)
                {
                    dt = (DataTable)dddata.value;

                    strUpdateTime = dddata.updateTime;
                }
            }
            else
            {
                dt.Columns.Add("year", typeof(int));
                dt.Columns.Add("netIncome", typeof(int));
                dt.Columns.Add("operatingCashFlow", typeof(int));
                dt.Columns.Add("interestCoverageRatio", typeof(decimal));

                
                for (int i = 1; i <= 5; i++)
                {
                    DataRow row = dt.NewRow();

                    row.SetField("year", DateTime.Today.Year - i);

                    dt.Rows.Add(row);
                }
            }

            this.grdFinancialData.DataSource = dt;

            this.grdFinancialData.Columns["year"].HeaderText = "Year";
            this.grdFinancialData.Columns["year"].DisplayIndex = 0;

            this.grdFinancialData.Columns["netIncome"].HeaderText = "Net Income (US$ Mil)";
            this.grdFinancialData.Columns["netIncome"].DisplayIndex = 1;

            this.grdFinancialData.Columns["operatingCashFlow"].HeaderText = "Operating Cash Flow (US$ Mil)";
            this.grdFinancialData.Columns["operatingCashFlow"].DisplayIndex = 2;

            this.grdFinancialData.Columns["interestCoverageRatio"].HeaderText = "Interest Coverage Ratio (%)";
            this.grdFinancialData.Columns["interestCoverageRatio"].DefaultCellStyle.Format = "N2";
            this.grdFinancialData.Columns["interestCoverageRatio"].DisplayIndex = 3;

            this.tspFinancialData.Text = strUpdateTime;
            this.tspFinancialData.ForeColor = Utility.IsChangedRecently(strUpdateTime) ? Color.Red : Color.DarkSlateGray;

        }

        private void LoadCreditRating(JsonElement jsonElement)
        {
            bool hasData = jsonElement.TryGetProperty("creditRating", out JsonElement element);

            string strUpdateTime = "";

            if (hasData == true)
                hasData &= element.TryGetProperty("v", out JsonElement elm);

            DataTable dt = new DataTable();

            if (hasData == true)
            {
                Utility.DDData dddata = Utility.Json2Table2(element);

                if (dddata != null)
                {
                    dt = (DataTable)dddata.value;

                    strUpdateTime = dddata.updateTime;
                }
            }
            else
            {
                dt.Columns.Add("agent", typeof(string));
                dt.Columns.Add("outlook", typeof(string));
                dt.Columns.Add("longTerm", typeof(string));
                dt.Columns.Add("shortTerm", typeof(string));
                dt.Columns.Add("seniorUnsecured", typeof(string));
                dt.Columns.Add("subordinated", typeof(string));
                dt.Columns.Add("trustPreferred", typeof(string));
                dt.Columns.Add("preferred", typeof(string));

            }

            this.grdCreditRating.DataSource = dt;

            this.grdCreditRating.Columns["agent"].DisplayIndex = 0;

            this.grdCreditRating.Columns["outlook"].HeaderText = "Outlook";
            this.grdCreditRating.Columns["outlook"].DisplayIndex = 1;

            this.grdCreditRating.Columns["longTerm"].HeaderText = "Long-term Issuer Rating";
            this.grdCreditRating.Columns["longTerm"].DisplayIndex = 2;

            this.grdCreditRating.Columns["shortTerm"].HeaderText = "Short-term Issuer Rating";
            this.grdCreditRating.Columns["shortTerm"].DisplayIndex = 3;

            this.grdCreditRating.Columns["seniorUnsecured"].HeaderText = "Senior Unsecured";
            this.grdCreditRating.Columns["seniorUnsecured"].DisplayIndex = 4;

            this.grdCreditRating.Columns["subordinated"].HeaderText = "Sub-ordinated";
            this.grdCreditRating.Columns["subordinated"].DisplayIndex = 5;

            this.grdCreditRating.Columns["trustPreferred"].HeaderText = "Trust Preferred";
            this.grdCreditRating.Columns["trustPreferred"].DisplayIndex = 6;

            this.grdCreditRating.Columns["preferred"].HeaderText = "Preferred Stock";
            this.grdCreditRating.Columns["preferred"].DisplayIndex = 7;

            this.tspCreditRating.Text = strUpdateTime;
            this.tspCreditRating.ForeColor = Utility.IsChangedRecently(strUpdateTime) ? Color.Red : Color.DarkSlateGray;
        }

        private void LoadCompanyAUM(JsonElement jsonElement)
        {
            bool hasData = jsonElement.TryGetProperty("aum", out JsonElement element);

            string strUpdateTime = "";

            if (hasData == true)
                hasData &= element.TryGetProperty("v", out JsonElement elm);

            DataTable dt = new DataTable();

            if (hasData == true)
            {
                Utility.DDData dddata = Utility.Json2Table2(element);

                if (dddata != null)
                {
                    dt = (DataTable)dddata.value;

                    strUpdateTime = dddata.updateTime;
                }
            }
            else
            {
                for (int i = 1; i <= 5; i++)
                {
                    dt.Columns.Add((DateTime.Today.Year - i).ToString(), typeof(int));
                }

                dt.Rows.Add(dt.NewRow());

            }

            this.grdAUM.DataSource = dt;

            for (int i = 1; i <= 5; i++)
            {
                this.grdAUM.Columns[(DateTime.Today.Year - i).ToString()].DisplayIndex = i - 1;
            }

            this.tspAUM.Text = strUpdateTime;
            this.tspAUM.ForeColor = Utility.IsChangedRecently(strUpdateTime) ? Color.Red : Color.DarkSlateGray;
        }

        private void LoadClientBreakdown(JsonElement jsonElement)
        {
            bool hasData = jsonElement.TryGetProperty("clientBreakdown", out JsonElement element);

            string strUpdateTime = "";

            if (hasData == true)
                hasData &= element.TryGetProperty("v", out JsonElement elm);

            DataTable dt = new DataTable();

            if (hasData == true)
            {
                Utility.DDData dddata = Utility.Json2Table2(element);

                if (dddata != null)
                {
                    dt = (DataTable)dddata.value;

                    strUpdateTime = dddata.updateTime;
                }
            }
            else
            {

                for (int i = 1; i <= 5; i++)
                {
                    dt.Columns.Add((DateTime.Today.Year - i).ToString(), typeof(int));
                }

                dt.Columns.Add("type", typeof(string));

                DataRow row = dt.NewRow();
                row.SetField("type", "Instituional");
                dt.Rows.Add(row);
                
                row = dt.NewRow();
                row.SetField("type", "Retail");
                dt.Rows.Add(row);

            }

            this.grdClientBreakdown.DataSource = dt;

            this.grdClientBreakdown.Columns["type"].HeaderText = "Client Type";
            this.grdClientBreakdown.Columns["type"].DisplayIndex = 0;

            for (int i = 1; i <= 5; i++)
            {
                this.grdClientBreakdown.Columns[(DateTime.Today.Year - i).ToString()].DisplayIndex = i;
            }

            this.tspClientBreakdown.Text = strUpdateTime;
            this.tspClientBreakdown.ForeColor = Utility.IsChangedRecently(strUpdateTime) ? Color.Red : Color.DarkSlateGray;

        }

        private void btnSaveCompany_Click(object sender, EventArgs e)
        {


            try
            {
                // 数据对象
                var textData = new
                {
                    foundingDate = Utility.AddInfo(this.dtpIncepionDate.Value.Date.ToString("yyyy-MM-dd"), userId),
                    detailOfAcquired = Utility.AddInfo(this.txtDetailOfAcquired.Text.Trim(), userId),
                    parentCompanyName = Utility.AddInfo(this.txtParentCompanyName.Text.Trim(), userId),

                    acqusition = Utility.AddInfo(Utility.DataTable2List((DataTable)this.grdAcquisiton.DataSource), userId),
                    
                    supportFromParent = Utility.AddInfo(new 
                        {
                            financial = this.chkSupportFinancial.Checked,
                            sales = this.chkSupportSales.Checked,
                            marketing = this.chkSupportMarketing.Checked,
                            technical = this.chkSupportTechnical.Checked,
                            personnel = this.chkSupportPersonnel.Checked
                        }, userId),

                    financialData = Utility.AddInfo(Utility.DataTable2List((DataTable)this.grdFinancialData.DataSource), userId),

                    creditRating = Utility.AddInfo(Utility.DataTable2List((DataTable)this.grdCreditRating.DataSource), userId),

                    aum = Utility.AddInfo(Utility.DataTable2List((DataTable)this.grdAUM.DataSource), userId),

                    clientBreakdown = Utility.AddInfo(Utility.DataTable2List((DataTable) this.grdClientBreakdown.DataSource), userId)
  
                };

                // 序列化选项
                var options = new JsonSerializerOptions
                {
                    WriteIndented = true,
                    PropertyNamingPolicy = JsonNamingPolicy.CamelCase
                };

                string jsonString = JsonSerializer.Serialize(textData, options);

                int ret = DataAccess.Set_dd_company_info(this.companyId, DateTime.Today, jsonString, 1, 1, this.userId);

                if (ret == 1)
                {
                    MessageBox.Show("Data is saved successfully", "Information");
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error: {ex.Message}", "Error");
            }
        }
    }
}