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;

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
                    //
                    bool isDate = DateTime.TryParse(Utility.Json2Text(root, "foundingDate"), out DateTime newDate);
                    if (isDate) this.dtpIncepionDate.Value = newDate.Date;

                    this.txtDetailOfAcquired.Text = Utility.Json2Text(root, "detailOfAcquired");
                    this.txtParentCompanyName.Text = Utility.Json2Text(root, "parentCompanyName");

                    LoadAcqusitionGrid(root);

                    LoadSupportFromParentCompany(root);

                    LoadFinancialData(root);

                    LoadCreditRating(root);

                    LoadCompanyAUM(root);

                    LoadClientBreakdown(root);
                }
            }
        }

        private void LoadAcqusitionGrid(JsonElement element)
        {

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

            DataTable dt = new DataTable();

            if (hasData == true)
            {
                dt = Utility.Json2Table(element);
            } 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;
        }


        private void LoadSupportFromParentCompany(JsonElement element)
        {

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

            if (hasData == false) return;

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

        }

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

            DataTable dt = new DataTable();

            if (hasData == true)
            {
                dt = Utility.Json2Table(element);
            }
            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;

        }

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

            DataTable dt = new DataTable();

            if (hasData == true)
            {
                dt = Utility.Json2Table(element);
            }
            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;

        }

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

            DataTable dt = new DataTable();

            if (hasData == true)
            {
                dt = Utility.Json2Table(element);
            }
            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;
            }

        }

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

            DataTable dt = new DataTable();

            if (hasData == true)
            {
                dt = Utility.Json2Table(element);
            }
            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;
            }

        }

        private void btnSaveCompany_Click(object sender, EventArgs e)
        {
            try
            {
                // 数据对象
                var textData = new
                {
                    foundingDate = this.dtpIncepionDate.Value.Date.ToString("yyyy-MM-dd"),
                    detailOfAcquired = this.txtDetailOfAcquired.Text.Trim(),
                    parentCompanyName = this.txtParentCompanyName.Text.Trim(),
                    acqusition = Utility.DataTable2List((DataTable)this.grdAcquisiton.DataSource),

                    supportFromParent = new
                    {
                        financial = this.chkSupportFinancial.Checked,
                        sales = this.chkSupportSales.Checked,
                        marketing = this.chkSupportMarketing.Checked,
                        technical = this.chkSupportTechnical.Checked,
                        personnel = this.chkSupportPersonnel.Checked
                    },

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

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

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

                    clientBreakdown = Utility.DataTable2List((DataTable) this.grdClientBreakdown.DataSource),

                    updateTime = DateTime.Now
                };

                // 序列化选项
                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");
            }
        }
    }
}