DataManager/DataManager.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Automation.Peers;
using System.Windows.Forms;
using System.Windows.Forms.DataVisualization.Charting;
using static DataManager.DataAccess;
using static DataManager.UIConstants;


namespace DataManager
{
    public partial class frmDataManager : Form
    {

        private DateTime defaultDatetime = DateTime.Now.AddDays(-30);
        private DataTable collectionTaskTable = new DataTable();
        private DataTable contactTaskTable;
        private DataTable orphanFundTable;
        private DataTable companyCoverageTable;
        private DataTable authorizedCompanyTable;
        private DataTable fundCoverageTable;
        private DataTable authorizedFundTable;

        private DataTable companyDataTable;
        private DataTable fundDataTable;


        private static int userId;

        public static int UserId { get => userId; set => userId = value; }

        public frmDataManager(int userid)
        {
            UserId = userid;

            InitializeComponent();

            InitializeData();

        }

        private void InitializeData()
        {


            #region Contact Task Tab

            DataTable dt_dm_user_1 = DataAccess.Get_dm_user(null, 1);
            dt_dm_user_1.Rows.Add(DataAccess.DM_NULL, 1, "全部");
            cmbContactor.DataSource = dt_dm_user_1;
            cmbContactor.DisplayMember = "username";
            cmbContactor.ValueMember = "userid";
            cmbContactor.SelectedValue = UserId;

            dtpContactTaskDate.Value = defaultDatetime;

            BindingSource bs4 = new BindingSource();
            Dictionary<int, string> d4 = new Dictionary<int, string>(ContactTaskType);
            d4.Add(DM_NULL, "全部");
            bs4.DataSource = d4;
            cmbContactTaskType.DataSource = bs4;
            cmbContactTaskType.DisplayMember = "Value";
            cmbContactTaskType.ValueMember = "Key";
            cmbContactTaskType.SelectedValue = (int)DM_NULL;


            BindingSource bs = new BindingSource();
            Dictionary<int, string> d = new Dictionary<int, string>(ContactTaskStatus);
            d.Add(DM_NULL, "全部");
            bs.DataSource = d;
            cmbContactTaskStatus.DataSource = bs;
            cmbContactTaskStatus.DisplayMember = "Value";
            cmbContactTaskStatus.ValueMember = "Key";
            cmbContactTaskStatus.SelectedValue = (int)DM_NULL;

            BindingSource bs2 = new BindingSource();
            Dictionary<int, string> d2 = new Dictionary<int, string>(TaskPriority);
            d2.Add(DM_NULL, "全部");
            bs2.DataSource = d2;
            cmbContactPriority.DataSource = bs2;
            cmbContactPriority.DisplayMember = "Value";
            cmbContactPriority.ValueMember = "Key";
            cmbContactPriority.SelectedValue = (int)DM_NULL;

            BindingSource bs3 = new BindingSource();
            Dictionary<int, string> d3 = new Dictionary<int, string>(CompanyAssetSize);
            d3.Add(DM_NULL, "全部");
            bs3.DataSource = d3;
            cmbContactCompanySize.DataSource = bs3;
            cmbContactCompanySize.DisplayMember = "Value";
            cmbContactCompanySize.ValueMember = "Key";
            cmbContactCompanySize.SelectedValue = (int)DM_NULL;

            dtpContactFollowUpDate.ShowCheckBox = true;
            dtpContactFollowUpDate.Checked = false;


            #endregion

            #region Collection Task Tab

            DataTable dt_dm_user_2 = DataAccess.Get_dm_user(null, 2);
            dt_dm_user_2.Rows.Add(DataAccess.DM_NULL, 2, "全部");
            cmbCollector.DataSource = dt_dm_user_2;
            cmbCollector.DisplayMember = "username";
            cmbCollector.ValueMember = "userid";
            cmbCollector.SelectedValue = UserId;

            dtpCollectionTaskDate.Value = defaultDatetime;

            BindingSource bs5 = new BindingSource();
            Dictionary<int, string> d5 = new Dictionary<int, string>(CollectionTaskType);
            d5.Add(DM_NULL, "全部");
            bs5.DataSource = d5;
            cmbCollectionTaskType.DataSource = bs5;
            cmbCollectionTaskType.DisplayMember = "Value";
            cmbCollectionTaskType.ValueMember = "Key";
            cmbCollectionTaskType.SelectedValue = (int)DM_NULL;


            BindingSource bs6 = new BindingSource();
            Dictionary<int, string> d6 = new Dictionary<int, string>(CollectionTaskStatus);
            d6.Add(DM_NULL, "全部");
            bs6.DataSource = d6;
            cmbCollectionTaskStatus.DataSource = bs6;
            cmbCollectionTaskStatus.DisplayMember = "Value";
            cmbCollectionTaskStatus.ValueMember = "Key";
            cmbCollectionTaskStatus.SelectedValue = (int)DM_NULL;

            #endregion
        }

        #region Contact Tab
        private void LoadContactTaskGrid()
        {
            ContactTask task = CreateContactTaskFromUI();

            int? userid = task.UserId;
            if (userid == DM_NULL) userid = null;

            short? taskType = task.TaskType;
            if(taskType == DM_NULL) taskType = null;

            sbyte? isvalid = task.Isvalid;
            if(isvalid == DM_NULL) isvalid = null;

            sbyte? priority = task.Priority;
            if (priority == DM_NULL) priority = null;

            sbyte? companyAssetSize = task.CompanyAssetSize;
            if(companyAssetSize == DM_NULL) companyAssetSize = null;

            contactTaskTable = DataAccess.Get_dm_contact_task(userid, null, task.CompanyId, task.CompanyShortName,
                                                              task.TaskDate, taskType, isvalid, priority,
                                                              task.FollowUpDate, companyAssetSize);
            
            grdContactTask.DataSource = contactTaskTable;

            grdContactTask.Columns["task_id"].HeaderText = "ID";
            grdContactTask.Columns["company_short_name"].HeaderText = "公司";
            grdContactTask.Columns["task_date"].HeaderText = "日期";
            grdContactTask.Columns["task_type_name"].HeaderText = "任务";
            grdContactTask.Columns["isvalid_name"].HeaderText = "状态";
            grdContactTask.Columns["priority_name"].HeaderText = "优先级";
            grdContactTask.Columns["follow_up_date"].HeaderText = "跟进日";
            grdContactTask.Columns["company_asset_size_name"].HeaderText = "规模";
            grdContactTask.Columns["creator_name"].HeaderText = "创建";
            grdContactTask.Columns["updater_name"].HeaderText = "更新";
            grdContactTask.Columns["updatetime"].HeaderText = "更新时间";
            
            grdContactTask.Columns["company_id"].Visible = false;
            grdContactTask.Columns["task_type"].Visible = false;
            grdContactTask.Columns["isvalid"].Visible = false;
            grdContactTask.Columns["priority"].Visible = false;
            grdContactTask.Columns["company_asset_size"].Visible = false;
            grdContactTask.Columns["creatorid"].Visible = false;
            grdContactTask.Columns["createtime"].Visible = false;
            grdContactTask.Columns["updaterid"].Visible = false;

            grdContactTask.Columns["company_short_name"].DefaultCellStyle.ForeColor = Color.DodgerBlue;
            grdContactTask.Columns["company_short_name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;

            lblContactTaskCount.Text = contactTaskTable.Rows.Count.ToString();
        }

        private ContactTask CreateContactTaskFromUI()
        {
            ContactTask task = new ContactTask();

            task.UserId = int.Parse(cmbContactor.SelectedValue.ToString());

            // 支持输 company_id或简称关键字的搜索
            string keywrod = txtContactCompany.Text.Trim();
            if (keywrod == String.Empty)
            {
                task.CompanyId = null;
                task.CompanyShortName = null;
            } else if (keywrod.Length == 10 && keywrod.ToUpper().Substring(0, 2) == "CO")
            {
                task.CompanyId = keywrod.ToUpper();
                task.CompanyShortName = null;
            } else
            {
                task.CompanyId = null;
                task.CompanyShortName = keywrod;
            }
            task.TaskDate = DateTime.Parse(dtpContactTaskDate.Text.ToString());
            task.TaskType = short.Parse(cmbContactTaskType.SelectedValue.ToString());
            task.Isvalid = sbyte.Parse(cmbContactTaskStatus.SelectedValue.ToString());
            task.Priority = sbyte.Parse(cmbContactPriority.SelectedValue.ToString());

            task.CompanyAssetSize = sbyte.Parse(cmbContactCompanySize.SelectedValue.ToString());
            
            if (dtpContactFollowUpDate.Checked == true) 
            {
                task.FollowUpDate = DateTime.Parse(dtpContactFollowUpDate.Text.ToString()); 
            } else { 
                task.FollowUpDate = null; 
            };

            return task;
        }
        private void btnContactSearch_Click(object sender, EventArgs e)
        {

            LoadContactTaskGrid();
        }


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

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

            DataRow row = contactTaskTable.Rows[rowIndex];

            if (columnIndex == grdContactTask.Columns["company_short_name"].Index)
            // 弹出公司页面
            {
                UICompany frmCompany = new UICompany(row.Field<string>("company_id"), UserId);
                frmCompany.Show();
            }
            else
            // 弹出联系任务编辑框
            {

                ContactTask task = new ContactTask(UserId, row.Field<int>("task_id"), row.Field<string>("company_id"), row.Field<string>("company_short_name"),
                                       row.Field<DateTime>("task_date"), row.Field<short>("task_type"), row.Field<sbyte>("isvalid"), row.Field<sbyte>("priority"),
                                       row.Field<DateTime?>("follow_up_date"), row.Field<sbyte?>("company_asset_size"),
                                       row.Field<int?>("creatorid"), row.Field<string>("creator_name"), row.Field<DateTime?>("createtime"),
                                       row.Field<int?>("updaterid"), row.Field<string>("updater_name"), row.Field<DateTime?>("updatetime"));

                if (task != null)
                {
                    frmContactTask ctk = new frmContactTask(task);

                    ctk.ShowDialog();

                    // 刷新 grid 数据
                    if (ctk.DialogResult == DialogResult.OK)
                    {
                        LoadContactTaskGrid();
                    }
                }
            }
        }


        private void btnAddContactTask_Click(object sender, EventArgs e)
        {
            frmContactTask ctk = new frmContactTask(UserId);
            ctk.ShowDialog();

            // 刷新 grid 数据
            if (ctk.DialogResult == DialogResult.OK)
            {
                LoadContactTaskGrid();
            }
        }
        #endregion



        #region Collection Tab

        private void LoadCollectionTaskGrid()
        {
            CollectionTask task = CreatecCollectionTaskFromUI();

            int? userId = task.UserId;
            if (userId == DM_NULL) userId = null;

            short? taskType = task.TaskType;
            if (taskType == DM_NULL) taskType = null;

            sbyte? isvalid = task.Isvalid;
            if (isvalid == DM_NULL) isvalid = null;

            collectionTaskTable = DataAccess.Get_dm_collection_task(userId, null, task.ProviderId, task.ProviderName,
                                                              task.TaskDate, taskType, task.EntityId, task.EntityName,
                                                              isvalid);

            grdCollectionTask.DataSource = collectionTaskTable;

            grdCollectionTask.Columns["task_id"].HeaderText = "ID";
            grdCollectionTask.Columns["provider_name"].HeaderText = "来源";

            grdCollectionTask.Columns["task_date"].HeaderText = "日期";
            grdCollectionTask.Columns["task_type_name"].HeaderText = "任务";
            grdCollectionTask.Columns["entity_name"].HeaderText = "主题";
            grdCollectionTask.Columns["err_message"].HeaderText = "详情";
            grdCollectionTask.Columns["isvalid_name"].HeaderText = "状态";
            grdCollectionTask.Columns["creator_name"].HeaderText = "创建";
            grdCollectionTask.Columns["updater_name"].HeaderText = "更新";
            grdCollectionTask.Columns["updatetime"].HeaderText = "更新时间";

            grdCollectionTask.Columns["provider_id"].Visible = false;
            grdCollectionTask.Columns["task_type"].Visible = false;
            grdCollectionTask.Columns["entity_id"].Visible = false;
            grdCollectionTask.Columns["isvalid"].Visible = false;
            grdCollectionTask.Columns["creatorid"].Visible = false;
            grdCollectionTask.Columns["createtime"].Visible = false;
            grdCollectionTask.Columns["updaterid"].Visible = false;

            //grdCollectionTask.Columns["provider_name"].DefaultCellStyle.ForeColor = Color.DodgerBlue;
            grdCollectionTask.Columns["entity_name"].DefaultCellStyle.ForeColor = Color.DodgerBlue;
            grdCollectionTask.Columns["entity_name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;

            lblCollectionTaskCount.Text = collectionTaskTable.Rows.Count.ToString();
        }


        private CollectionTask CreatecCollectionTaskFromUI()
        {
            CollectionTask task = new CollectionTask();

            task.UserId = int.Parse(cmbCollector.SelectedValue.ToString());

            string providerKeyword = txtCollectionProvider.Text.Trim();
            if (providerKeyword == string.Empty)
            {
                task.ProviderId = null;
                task.ProviderName = null;
            }
            else if (providerKeyword.Length == 10 && providerKeyword.Substring(0, 2).ToUpper() == "CO")
            {
                task.ProviderId = providerKeyword.ToUpper();
                task.ProviderName = null;
            }
            else
            {
                task.ProviderId = null;
                task.ProviderName = providerKeyword;
            }

            task.TaskDate = DateTime.Parse(dtpCollectionTaskDate.Text.ToString());
            task.TaskType = short.Parse(cmbCollectionTaskType.SelectedValue.ToString());


            // 支持输入fund_id或简称关键字的搜索
            string entityKeyword = txtCollectionEntity.Text.Trim();
            if (entityKeyword == string.Empty)
            {
                task.EntityId = null;
                task.EntityName = null;
            }
            else if (entityKeyword.Length == 10 && Regex.Match(entityKeyword.ToUpper(), "^[M|H]F").Success)
            {
                task.EntityId = entityKeyword.ToUpper();
                task.EntityName = null;
            }
            else
            {
                task.EntityId = null;
                task.EntityName = entityKeyword;
            }

            task.Isvalid = sbyte.Parse(cmbCollectionTaskStatus.SelectedValue.ToString());

            return task;
        }

        private void btnCollectionSearch_Click(object sender, EventArgs e)
        {
            LoadCollectionTaskGrid();

        }

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

            if (columnIndex < 0 || rowIndex < 0) return;
            
            DataRow row = collectionTaskTable.Rows[rowIndex];

            if (columnIndex == grdCollectionTask.Columns["entity_name"].Index )
            // 弹出基金页面
            {
                string entityId = row["entity_id"].ToString();
                
                if (entityId.Length == 10 && Regex.Match(entityId, "^[HM]F").Success)
                {
                    UIFund frmFund = new UIFund(entityId, UserId);
                    frmFund.Show();
                }
            }
            else
            // 弹出采集任务编辑框
            {

                CollectionTask task = new CollectionTask(UserId, row.Field<int>("task_id"), row.Field<string>("provider_id"), row.Field<string>("provider_name"),
                                       row.Field<DateTime>("task_date"), row.Field<short>("task_type"), 
                                       row.Field<string>("entity_id"), row.Field<string>("entity_name"),
                                       row.Field<string>("err_message"), row.Field<sbyte>("isvalid"), 
                                       row.Field<int?>("creatorid"), row.Field<string>("creator_name"), row.Field<DateTime?>("createtime"),
                                       row.Field<int?>("updaterid"), row.Field<string>("updater_name"), row.Field<DateTime?>("updatetime"));

                if (task != null)
                {
                    UICollectionTask ctk = new  UICollectionTask(task);

                    ctk.ShowDialog();

                    // 刷新 grid 数据
                    if (ctk.DialogResult == DialogResult.OK)
                    {
                        LoadCollectionTaskGrid();
                    }
                }
            }

        }
        private void btnAddCollectionTask_Click(object sender, EventArgs e)
        {
            UICollectionTask ctk = new UICollectionTask(UserId);
            ctk.ShowDialog();

            // 刷新 grid 数据
            if (ctk.DialogResult == DialogResult.OK)
            {
                LoadCollectionTaskGrid();
            }
        }


        #endregion

        #region Data - Fund
        
        private void LoadFundGrid()
        {
            string keyword = txtFundKeyword.Text.Trim();
            if (keyword == string.Empty) 
            {
                MessageBox.Show("搜索至少得输入一个字吧", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                txtFundKeyword.Focus();

                return;
            }
            
            this.Cursor = Cursors.WaitCursor;

            // 只搜私募
            fundDataTable = DataAccess.Search_dm_fund(RaiseType.FirstOrDefault(x=>x.Value== "私募").Key, keyword);

            grdFundList.DataSource = fundDataTable;

            grdFundList.DefaultCellStyle.ForeColor = Color.FromArgb(255, 64, 64, 64);
            
            grdFundList.Columns["fund_id"].Visible = false;
            grdFundList.Columns["fund_name"].HeaderText = "基金全名";
            grdFundList.Columns["fund_short_name"].HeaderText = "基金简称";

            grdFundList.Columns["company_id"].Visible = false;
            grdFundList.Columns["company_short_name"].HeaderText = "公司简称";
            grdFundList.Columns["strategy"].HeaderText = "策略分类";
            grdFundList.Columns["substrategy"].HeaderText = "二级分类";
            grdFundList.Columns["max_price_date"].HeaderText = "最新净值";
            grdFundList.Columns["company_short_name"].DefaultCellStyle.ForeColor = Color.DodgerBlue;

            grdFundList.Columns["max_price_date"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
            grdFundList.Columns["fund_short_name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;

            ToolStripStatusLabel label = new ToolStripStatusLabel();
            label.Text =  "记录条数：" + fundDataTable.Rows.Count.ToString();
            sstData.Items.Clear();
            sstData.Items.Add(label);

            this.Cursor = Cursors.Default;
        }
        
        private void btnSearchFund_Click(object sender, EventArgs e)
        {
            LoadFundGrid();
        }
        private void txtFundKeyword_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                btnSearchFund_Click(sender, e);
            }
        }


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

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

            DataGridViewRow row = grdFundList.Rows[rowIndex];

            if (columnIndex == grdFundList.Columns["company_short_name"].Index)
            // 弹出公司页面
            {
                string entityId = row.Cells["company_id"].Value.ToString().ToUpper();

                if (entityId.Length == 10 && Regex.Match(entityId, "^CO").Success)
                {
                    UICompany frm = new UICompany(entityId, UserId);

                    frm.Show();
                }
            }
            else
            // 弹出基金页面
            {
                string entityId = row.Cells["fund_id"].Value.ToString().ToUpper();

                if (entityId.Length == 10 && Regex.Match(entityId, "^[M|H]F").Success)
                {
                    UIFund frm = new UIFund(entityId, UserId);

                    frm.Show();
                }

            }
        }

        private void pctFundExport_Click(object sender, EventArgs e)
        {
            ExportDataTable(fundDataTable);
        }

        #endregion

        #region Data - Company

        private void LoadCompanyGrid()
        {
            string keyword = txtCompanyKeyword.Text.Trim();
            if (keyword == string.Empty)
            {
                MessageBox.Show("搜索至少得输入一个字吧", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                txtFundKeyword.Focus();

                return;
            }

            this.Cursor = Cursors.WaitCursor;

            // 只搜私募
            companyDataTable = DataAccess.Search_dm_company(1, keyword);

            grdCompanyList.DataSource = companyDataTable;

            grdCompanyList.DefaultCellStyle.ForeColor = Color.FromArgb(255, 64, 64, 64);
            grdCompanyList.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            //grdCompanyList.Columns["company_name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;

            grdCompanyList.Columns["company_id"].Visible = false;
            grdCompanyList.Columns["company_name"].HeaderText = "公司全名";
            grdCompanyList.Columns["company_short_name"].HeaderText = "公司简称";
            grdCompanyList.Columns["company_asset_size"].Visible = false;
            grdCompanyList.Columns["company_asset_size_name"].HeaderText = "管理规模";
            grdCompanyList.Columns["register_number"].HeaderText = "协会编号";
            grdCompanyList.Columns["establish_date"].HeaderText = "成立日";

            grdCompanyList.Columns["company_name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;

            ToolStripStatusLabel label = new ToolStripStatusLabel();
            label.Text = "记录条数：" + companyDataTable.Rows.Count.ToString();
            sstData.Items.Clear();
            sstData.Items.Add(label);

            this.Cursor = Cursors.Default;
        }
        private void btnSearchCompany_Click(object sender, EventArgs e)
        {
            LoadCompanyGrid();
        }
        private void txtCompanyKeyword_KeyPress(object sender, KeyPressEventArgs e)
        {
            if(e.KeyChar == 13)
            {
                btnSearchCompany_Click(sender, e);
            }
        }

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

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

            DataGridViewRow row = grdCompanyList.Rows[rowIndex];

            string entityId = row.Cells["company_id"].Value.ToString().ToUpper();

            if (entityId.Length == 10 && Regex.Match(entityId, "^CO").Success)
            {
                UICompany frm = new UICompany(entityId, UserId);

                frm.Show();
            }
        }

        private void pctCompanyExport_Click(object sender, EventArgs e)
        {
            ExportDataTable(companyDataTable);
        }

        #endregion



        #region Reports


        #region Orphan Fund

        private void LoadOrphanFund()
        {
            DataTable dt = null;
            string providerName = txtOrphanProvider.Text.Trim();
            sbyte isOrphan = 1;
            if (!chkIsOrphan.Checked) isOrphan = 0;

            orphanFundTable = DataAccess.Get_dm_external_fund_mapping(isOrphan, UserId);

            if (providerName != string.Empty)
            {
                DataRow[] rows = orphanFundTable.Select("provider_name LIKE '%" + providerName + "%'", "id DESC");
                if(rows != null && rows.Length > 0) dt = rows.CopyToDataTable();
            }
            else
                dt = orphanFundTable;

            if (dt != null)
            {
                grdOrphanFund.DataSource = dt;

                grdOrphanFund.Columns["id"].Visible = false;
                grdOrphanFund.Columns["fund_id"].HeaderText = "基金ID";
                grdOrphanFund.Columns["provider_id"].Visible = false;
                grdOrphanFund.Columns["provider_name"].HeaderText = "数据源";
                grdOrphanFund.Columns["external_id"].HeaderText = "外部ID";
                grdOrphanFund.Columns["isvalid"].HeaderText = "状态";
                grdOrphanFund.Columns["creatorid"].Visible = false;
                grdOrphanFund.Columns["creator_name"].HeaderText = "创建";
                grdOrphanFund.Columns["createtime"].HeaderText = "创建时间";
                grdOrphanFund.Columns["updaterid"].Visible = false;
                grdOrphanFund.Columns["updater_name"].HeaderText = "更新";
                grdOrphanFund.Columns["updatetime"].HeaderText = "更新时间";

                grdOrphanFund.Columns["provider_name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
                grdOrphanFund.Columns["external_id"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
            }
        }

        private void btnOrphanFundSearch_Click(object sender, EventArgs e)
        {
            LoadOrphanFund();
        }

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

            if (rowIndex < 0) return;

            DataGridViewRow row = grdOrphanFund.Rows[rowIndex];

            // 弹出基金页面
            string provider_id = row.Cells["provider_id"].Value.ToString();
            string external_id = row.Cells["external_id"].Value.ToString();
            string fund_id = row.Cells["fund_id"].Value.ToString();

            UIFund frmFund = new UIFund(provider_id, external_id, fund_id, UserId);
            frmFund.ShowDialog();

            if (frmFund.DialogResult == DialogResult.OK) 
            {
                LoadOrphanFund();
            }

        }

        private void txtOrphanProvider_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13) 
            {
                btnOrphanFundSearch_Click(sender, e);
            }
        }


        #endregion

        #region Authorization Coverage

        private void LoadCompanyCoverageChart()
        {
            int coveredCnt = 0;
            int totalCnt = 0;

            DataRow[] rows = companyCoverageTable.Select(CompanySizeFilter());
            for (int i = 0; i < rows.Length; i ++) 
            {
                coveredCnt += int.Parse(rows[i]["covered_cnt"].ToString());
                totalCnt += int.Parse(rows[i]["total_cnt"].ToString());
            }

            string[] pieX = { "合作#", "未合作#" };
            int[] pieY = { coveredCnt, totalCnt - coveredCnt };

            chtEntityCoverage.Series.Clear();
            Series series = new Series();
            series.ChartType = SeriesChartType.Pie;

            series.Points.DataBindXY(pieX, pieY);
            series.Points[0].Label = $"{series.Points[0].AxisLabel}: {coveredCnt:N0}";
            series.Points[0].ToolTip = $"{series.Points[0].AxisLabel}: {(100.0 * coveredCnt / totalCnt):F2}%";
            series.Points[1].Label = $"{series.Points[1].AxisLabel}: {(totalCnt - coveredCnt):N0}";
            series.Points[1].ToolTip = $"{series.Points[1].AxisLabel}: {(100.0 * ( 1.0 - coveredCnt / totalCnt)):F2}%";
            series.Points[1].Color = Color.LightGray;

            chtEntityCoverage.Series.Add(series);

        }

        private void LoadFundCoverageChart()
        {

            chtEntityCoverage.Series.Clear();

            Series series = new Series();
            series.Name = "合作%";
            series.ChartType = SeriesChartType.StackedColumn100;

            Series series2 = new Series();
            series2.Name = "未合作%";
            series2.ChartType = SeriesChartType.StackedColumn100;
            series2.Color = Color.LightGray;

            int coveredCnt = 0;
            int totalCnt = 0;

            DataRow[] rows = fundCoverageTable.Select(CompanySizeFilter());
            for (int i = 0; i < rows.Length; i++)
            {
                coveredCnt += int.Parse(rows[i]["covered_cnt"].ToString());
                totalCnt += int.Parse(rows[i]["total_cnt"].ToString());

                series.Points.AddXY(rows[i]["company_asset_size_name"].ToString(), coveredCnt);
                series2.Points.AddXY(rows[i]["company_asset_size_name"].ToString(), totalCnt - coveredCnt);
                
            }

            chtEntityCoverage.Series.Add(series);
            chtEntityCoverage.Series.Add(series2);
            chtEntityCoverage.ChartAreas[0].AxisX.MajorGrid.LineColor = Color.LightGray;
            chtEntityCoverage.ChartAreas[0].AxisY.MajorGrid.LineColor = Color.LightGray;

        }

        private string CompanySizeFilter()
        {
            string selectExp = "company_asset_size in (";
            if (chk100.Checked) selectExp += "6,";
            if (chk50.Checked) selectExp += "5,";
            if (chk20.Checked) selectExp += "4,";
            if (chk10.Checked) selectExp += "3,";
            if (chk5.Checked) selectExp += "2,";
            if (chk0.Checked) selectExp += "1,";
            // 没有checkbox背选中时表示规模未知
            if (selectExp.Substring(selectExp.Length - 1) == "(") selectExp = selectExp + " -1)";
            // 去掉最后一个逗号
            if (selectExp.Substring(selectExp.Length - 1) == ",") selectExp = selectExp.Substring(0, selectExp.Length - 1) + ")";

            return selectExp;
        }


        private void LoadEntityCoverageGrid()
        {
            // 这个sp有点慢
            this.Cursor = Cursors.WaitCursor;

            if (rdbCompany.Checked)
            {
                companyCoverageTable = DataAccess.Report_company_coverage(null);
                grdEntityCoverage.DataSource = companyCoverageTable;
            }
            else
            {
                fundCoverageTable = DataAccess.Report_fund_coverage(null);
                grdEntityCoverage.DataSource = fundCoverageTable;
            }

            this.Cursor = Cursors.Default;

            grdEntityCoverage.Columns["company_asset_size"].Visible = false;
            grdEntityCoverage.Columns["company_asset_size_name"].HeaderText = "规模";
            grdEntityCoverage.Columns["covered_cnt"].HeaderText = "覆盖数";
            grdEntityCoverage.Columns["covered_cnt"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdEntityCoverage.Columns["covered_cnt"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdEntityCoverage.Columns["total_cnt"].HeaderText = "总数";
            grdEntityCoverage.Columns["total_cnt"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdEntityCoverage.Columns["total_cnt"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdEntityCoverage.Columns["cover_pct"].HeaderText = "覆盖率%";
            grdEntityCoverage.Columns["cover_pct"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdEntityCoverage.Columns["cover_pct"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

            grdEntityCoverage.Columns["company_asset_size_name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
            grdEntityCoverage.Columns["cover_pct"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;

        }

        private void LoadAuthrizedCompanyList()
        {
            authorizedCompanyTable = DataAccess.Get_dm_company_authorization(null, null);
        }

        private void LoadAuthrizedFundList()
        {
            authorizedFundTable = DataAccess.Get_dm_fund_authorization(null);
        }

        private void LoadAuthorizedCompanyGrid()
        {
            if (authorizedCompanyTable != null)
            {
                DataTable dt = null;

                string selectExp = CompanySizeFilter();
                string nameExp = txtLocateEntity.Text.Trim();
                if (nameExp.Length > 0) selectExp += " and company_short_name LIKE '%" + nameExp + "%'";

                DataRow[] dr = authorizedCompanyTable.Select(selectExp, "company_asset_size DESC");

                if (dr != null && dr.Length > 0) dt = dr.CopyToDataTable();

                grdAuthorizedEntity.DataSource = dt;

                if (dt != null)
                {
                    grdAuthorizedEntity.Columns["company_id"].Visible = false;
                    grdAuthorizedEntity.Columns["company_name"].Visible = false;
                    grdAuthorizedEntity.Columns["company_short_name"].HeaderText = "公司简称";
                    grdAuthorizedEntity.Columns["company_asset_size"].Visible = false;
                    grdAuthorizedEntity.Columns["company_asset_size_name"].HeaderText = "规模";
                    grdAuthorizedEntity.Columns["register_number"].HeaderText = "协会编号";
                    grdAuthorizedEntity.Columns["establish_date"].HeaderText = "成立日";

                    grdAuthorizedEntity.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                    grdAuthorizedEntity.Columns["company_short_name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
                }
            }
        }

        private void LoadAuthorizedFundGrid()
        {
            if (authorizedFundTable != null)
            {
                DataTable dt = null;

                string selectExp = CompanySizeFilter();
                string nameExp = txtLocateEntity.Text.Trim();
                if (nameExp.Length > 0) selectExp += " and fund_short_name LIKE '%" + nameExp + "%'";

                DataRow[] dr = authorizedFundTable.Select(selectExp, "company_asset_size DESC");

                if (dr != null && dr.Length > 0) dt = dr.CopyToDataTable();

                grdAuthorizedEntity.DataSource = dt;

                if (dt != null)
                {
                    grdAuthorizedEntity.Columns["fund_id"].Visible = false;
                    grdAuthorizedEntity.Columns["fund_name"].Visible = false;
                    grdAuthorizedEntity.Columns["fund_short_name"].HeaderText = "基金简称";
                    grdAuthorizedEntity.Columns["company_asset_size"].Visible = false;
                    grdAuthorizedEntity.Columns["strategy"].Visible = false;
                    grdAuthorizedEntity.Columns["substrategy"].HeaderText = "二级分类";
                    grdAuthorizedEntity.Columns["inception_date"].HeaderText = "成立日";
                    grdAuthorizedEntity.Columns["register_number"].HeaderText = "备案编码";

                    grdAuthorizedEntity.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                    grdAuthorizedEntity.Columns["fund_short_name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
                }
            }
        }

        private void btnEntityCoverage_Click(object sender, EventArgs e)
        {
            // 左下角的固定格式覆盖率表
            LoadEntityCoverageGrid();

            if (rdbCompany.Checked)
            {
                // 授权公司数据
                LoadAuthrizedCompanyList();
                // 对公司覆盖率表进行可视化 (PIE）
                LoadCompanyCoverageChart();
                // 授权公司列表
                LoadAuthorizedCompanyGrid();
            }
            else
            {
                // 授权基金数据
                LoadAuthrizedFundList();
                // 对基金覆盖率表进行可视化（BAR）
                LoadFundCoverageChart();
                // 授权基金列表
                LoadAuthorizedFundGrid();

            }
        }


        private void rdbCompany_CheckedChanged(object sender, EventArgs e)
        {
            btnEntityCoverage_Click(sender, e);
        }
        

        private void chk100_CheckedChanged(object sender, EventArgs e)
        {
            if (((Control)sender).Focus())
            {
                if (rdbCompany.Checked)
                {
                    // 重新画图
                    LoadCompanyCoverageChart();

                    // 刷新公司列表
                    LoadAuthorizedCompanyGrid();
                }
                else
                {
                    // 重新画图
                    LoadFundCoverageChart();

                    // 刷新基金列表
                    LoadAuthorizedFundGrid();

                }
            }
        }

        private void chk50_CheckedChanged(object sender, EventArgs e)
        {
            chk100_CheckedChanged(sender, e);
        }

        private void chk20_CheckedChanged(object sender, EventArgs e)
        {
            chk100_CheckedChanged(sender, e);
        }

        private void chk10_CheckedChanged(object sender, EventArgs e)
        {
            chk100_CheckedChanged(sender, e);
        }

        private void chk5_CheckedChanged(object sender, EventArgs e)
        {
            chk100_CheckedChanged(sender, e);
        }

        private void chk0_CheckedChanged(object sender, EventArgs e)
        {
            chk100_CheckedChanged(sender, e);
        }

        private void txtLocateEntity_TextChanged(object sender, EventArgs e)
        {
            if (rdbCompany.Checked) LoadAuthorizedCompanyGrid();
            else LoadAuthorizedFundGrid();
        }

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

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

            DataGridViewRow row = grdAuthorizedEntity.Rows[rowIndex];

            if (rdbCompany.Checked)
            {
                string entityId = row.Cells["company_id"].Value.ToString().ToUpper();

                if (entityId.Length == 10 && Regex.Match(entityId, "^CO").Success)
                {
                    UICompany frm = new UICompany(entityId, UserId);

                    frm.Show();
                }
            }
            else
            {
                string entityId = row.Cells["fund_id"].Value.ToString().ToUpper();

                if (entityId.Length == 10 && Regex.Match(entityId, "^[H|M]F").Success)
                {
                    UIFund frm = new UIFund(entityId, UserId);

                    frm.Show();
                }

            }
        }

        private void rdbFund_CheckedChanged(object sender, EventArgs e)
        {
            // DO NOTHING, because same actions were triggered by rdbCompany_CheckedChanged anyway
        }




        #endregion

        #endregion

        public static void ExportDataTable(DataTable dt)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "CSV 文件 (*.csv)|*.csv|TXT 文件|*.txt";

            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                string filePath = saveFileDialog.FileName;

                int ret = DataTableToCSV(dt, filePath);

                MessageBox.Show(ret < 0 ? "数据导出有问题，得问问程序员师傅" : "数据导出后，可以用Excel直接打开", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

        }


        private static int DataTableToCSV(DataTable dt, string fileName)
        {
            int ret = -1;

            if (dt == null || dt.Columns == null) return ret;

            StringBuilder sb = new StringBuilder();
            foreach (DataColumn col in dt.Columns)
            {
                sb.Append(col.ColumnName + ",");
            }
            sb.AppendLine();

            if (dt.Rows != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    foreach(DataColumn col in dt.Columns)
                    {
                        sb.Append(row[col].ToString() + ",");
                    }
                    sb.AppendLine();
                }
            }

            using (StreamWriter writer = new StreamWriter(fileName))
            {
                writer.Write(sb.ToString());
                ret = 1;
            }

            return ret;
        }
    }
}