DataManager/DataManager.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
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 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) { keyword = null; }
            
            // 只搜私募
            DataTable dt = DataAccess.Search_dm_fund(RaiseType.FirstOrDefault(x=>x.Value== "私募").Key, keyword);

            grdFundList.DataSource = dt;
            
            grdFundList.DefaultCellStyle.ForeColor = Color.DimGray;
            
            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 =  "记录条数：" + dt.Rows.Count.ToString();
            sstData.Items.Clear();
            sstData.Items.Add(label);
        }
        
        private void btnSearchFund_Click(object sender, EventArgs e)
        {
            LoadFundGrid();
        }



        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();
                }

            }
        }
        #endregion

        #region Data - Company

        private void LoadCompanyGrid()
        {
            string keyword = txtCompanyKeyword.Text.Trim();
            if (keyword == string.Empty) { keyword = null; }

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

            grdCompanyList.DataSource = dt;

            grdCompanyList.DefaultCellStyle.ForeColor = Color.DimGray;
            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["company_name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;

            ToolStripStatusLabel label = new ToolStripStatusLabel();
            label.Text = "记录条数：" + dt.Rows.Count.ToString();
            sstData.Items.Clear();
            sstData.Items.Add(label);
        }
        private void btnSearchCompany_Click(object sender, EventArgs e)
        {
            LoadCompanyGrid();
        }


        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();
            }
        }
        #endregion



        #region Reports


        #region Orphan Fund

        private void LoadOrphanFund(DataTable dt)
        {
            if(dt == null)
                dt = DataAccess.Get_dm_report_orphan_fund(UserId);

            grdOrphanFund.DataSource = dt;

            grdOrphanFund.Columns["id"].Visible = false;
            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)
        {
            DataTable dt;
            string providerName = txtOrphanProvider.Text.Trim();

            if(orphanFundTable == null)
            {
                LoadOrphanFund(orphanFundTable);
            }
            else
            {
                if (providerName != string.Empty)
                    dt = orphanFundTable.Select("provider_name LIKE '%" + providerName + "%'", "id DESC").CopyToDataTable();
                else
                    dt = orphanFundTable;

                LoadOrphanFund(dt);

            }
        }

        #endregion

        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();

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

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

        }

        #endregion

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