DataManager/DataAccess.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;

using MySql.Data.MySqlClient;
using System.Runtime.InteropServices.ComTypes;
using static DataManager.UIConstants;
using System.ComponentModel.Design;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.ListView;
using System.Xml.Linq;

namespace DataManager
{
    public class DataAccess
    {
        private static String connectionString = ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString;

        public const sbyte DM_NULL = -99;

        public static DataTable Get_cm_user_by_cellphone(string cellphone)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_cm_user_by_cellphone", connection);

            MySqlParameter i_cellphone = new MySqlParameter("i_cellphone", cellphone);

            ad.SelectCommand.Parameters.Add(i_cellphone);
            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;
        }


        public static DataTable Get_dm_user(int? userId, sbyte? role)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_dm_user", connection);

            MySqlParameter i_userid = new MySqlParameter("i_userid", userId);
            MySqlParameter i_role = new MySqlParameter("i_role", role);

            ad.SelectCommand.Parameters.Add(i_userid);
            ad.SelectCommand.Parameters.Add(i_role);
            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;
        }


        /// <summary>
        /// 取数据采集任务列表
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="taskId"></param>
        /// <param name="companyId"></param>
        /// <param name="taskDate"></param>
        /// <param name="taskType"></param>
        /// <param name="isValid"></param>
        /// <returns></returns>
        public static DataTable Get_dm_collection_task(int? userId, int? taskId, string providerId, string providerKeyword,
                                                       DateTime? taskDate, short? taskType, string entityId, string entityKeyword, sbyte? isValid)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_dm_collection_task", connection);

            MySqlParameter i_userid = new MySqlParameter("i_userid", userId);
            MySqlParameter i_task_id = new MySqlParameter("i_task_id", taskId);
            MySqlParameter i_provider_id = new MySqlParameter("i_provider_id", providerId);
            MySqlParameter i_provider_keyword = new MySqlParameter("i_provider_keyword", providerKeyword);
            MySqlParameter i_task_date = new MySqlParameter("i_task_date", taskDate);
            MySqlParameter i_task_type = new MySqlParameter("i_task_type", taskType);
            MySqlParameter i_entity_id = new MySqlParameter("i_entity_id", entityId);
            MySqlParameter i_entity_keywordword = new MySqlParameter("i_entity_keyword", entityKeyword);
            MySqlParameter i_isvalid = new MySqlParameter("i_isvalid", isValid);

            ad.SelectCommand.Parameters.Add(i_userid);
            ad.SelectCommand.Parameters.Add(i_task_id);
            ad.SelectCommand.Parameters.Add(i_provider_id);
            ad.SelectCommand.Parameters.Add(i_provider_keyword);
            ad.SelectCommand.Parameters.Add(i_task_date);
            ad.SelectCommand.Parameters.Add(i_task_type);
            ad.SelectCommand.Parameters.Add(i_entity_id);
            ad.SelectCommand.Parameters.Add(i_entity_keywordword);
            ad.SelectCommand.Parameters.Add(i_isvalid);

            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;
        }
        public static void Set_dm_collection_task(int? taskId, string providerId, DateTime taskDate, Int16 taskType, 
                                                  string entityId, string errMessage,
                                                  sbyte isvalid, int userId, out int task_id)
        {

            task_id = 0;

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {

                connection.Open();

                MySqlCommand cmd = new MySqlCommand("sp_set_dm_collection_task", connection);

                MySqlParameter i_task_id = new MySqlParameter("i_task_id", taskId);
                MySqlParameter i_provider_id = new MySqlParameter("i_provider_id", providerId);
                MySqlParameter i_task_date = new MySqlParameter("i_task_date", taskDate);
                MySqlParameter i_task_type = new MySqlParameter("i_task_type", taskType);
                MySqlParameter i_entity_id = new MySqlParameter("i_entity_id", entityId);
                MySqlParameter i_err_message = new MySqlParameter("i_err_message", errMessage);
                MySqlParameter i_isvalid = new MySqlParameter("i_isvalid", isvalid);
                MySqlParameter i_user_id = new MySqlParameter("i_user_id", userId);

                MySqlParameter o_task_id = new MySqlParameter("o_task_id", DbType.Int32);
                o_task_id.Direction = ParameterDirection.Output;

                cmd.Parameters.Add(i_task_id);
                cmd.Parameters.Add(i_provider_id);
                cmd.Parameters.Add(i_task_date);
                cmd.Parameters.Add(i_task_type);
                cmd.Parameters.Add(i_isvalid);
                cmd.Parameters.Add(i_entity_id);
                cmd.Parameters.Add(i_err_message);
                cmd.Parameters.Add(i_user_id);
                cmd.Parameters.Add(o_task_id);

                cmd.CommandType = CommandType.StoredProcedure;


                cmd.Connection = connection;
                cmd.ExecuteNonQuery();

                task_id = int.Parse(cmd.Parameters["o_task_id"].Value.ToString());
            }

        }

        /// <summary>
        /// 取公司联系任务列表
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="taskId"></param>
        /// <param name="companyId"></param>
        /// <param name="taskDate"></param>
        /// <param name="taskType"></param>
        /// <param name="isValid"></param>
        /// <param name="priority"></param>
        /// <param name="followUpDate"></param>
        /// <param name="companyAsset"></param>
        /// <returns></returns>
        public static DataTable Get_dm_contact_task(int? userId, int? taskId, string companyId, string companyKeyword,
                                                    DateTime? taskDate, short? taskType, sbyte? isValid, sbyte? priority, 
                                                    DateTime? followUpDate, sbyte? companyAsset)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_dm_contact_task", connection);

            MySqlParameter i_userid = new MySqlParameter("i_userid", userId);
            MySqlParameter i_task_id = new MySqlParameter("i_task_id", taskId);
            MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);
            MySqlParameter i_company_keyword = new MySqlParameter("i_company_keyword", companyKeyword);
            MySqlParameter i_task_date = new MySqlParameter("i_task_date", taskDate);
            MySqlParameter i_task_type = new MySqlParameter("i_task_type", taskType);
            MySqlParameter i_isvalid = new MySqlParameter("i_isvalid", isValid);
            MySqlParameter i_priority = new MySqlParameter("i_priority", priority);
            MySqlParameter i_follow_up_date = new MySqlParameter("i_follow_up_date", followUpDate);
            MySqlParameter i_company_asset_size = new MySqlParameter("i_company_asset_size", companyAsset);

            ad.SelectCommand.Parameters.Add(i_userid);
            ad.SelectCommand.Parameters.Add(i_task_id);
            ad.SelectCommand.Parameters.Add(i_company_id);
            ad.SelectCommand.Parameters.Add(i_company_keyword);
            ad.SelectCommand.Parameters.Add(i_task_date);
            ad.SelectCommand.Parameters.Add(i_task_type);
            ad.SelectCommand.Parameters.Add(i_isvalid);
            ad.SelectCommand.Parameters.Add(i_priority);
            ad.SelectCommand.Parameters.Add(i_follow_up_date);
            ad.SelectCommand.Parameters.Add(i_company_asset_size);

            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;
        }
        /// <summary>
        /// 更新联系任务表
        /// </summary>
        /// <param name="taskId"></param>
        /// <param name="companyId"></param>
        /// <param name="taskDate"></param>
        /// <param name="taskType"></param>
        /// <param name="isvalid"></param>
        /// <param name="priority"></param>
        /// <param name="followUpDate"></param>
        /// <param name="userId"></param>
        public static void Set_dm_contact_task(int? taskId, string companyId, DateTime taskDate, Int16 taskType, sbyte isvalid,
                                               sbyte priority, DateTime? followUpDate, int userId, out int task_id)
        {

            task_id = 0;

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {

                connection.Open();

                MySqlCommand cmd = new MySqlCommand("sp_set_dm_contact_task", connection);

                MySqlParameter i_task_id = new MySqlParameter("i_task_id", taskId);
                MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);
                MySqlParameter i_task_date = new MySqlParameter("i_task_date", taskDate);
                MySqlParameter i_task_type = new MySqlParameter("i_task_type", taskType);
                MySqlParameter i_isvalid = new MySqlParameter("i_isvalid", isvalid);
                MySqlParameter i_priority = new MySqlParameter("i_priority", priority);
                MySqlParameter i_follow_up_date = new MySqlParameter("i_follow_up_date", followUpDate);
                MySqlParameter i_user_id = new MySqlParameter("i_user_id", userId);

                MySqlParameter o_task_id = new MySqlParameter("o_task_id", DbType.Int32);
                o_task_id.Direction = ParameterDirection.Output;

                cmd.Parameters.Add(i_task_id);
                cmd.Parameters.Add(i_company_id);
                cmd.Parameters.Add(i_task_date);
                cmd.Parameters.Add(i_task_type);
                cmd.Parameters.Add(i_isvalid);
                cmd.Parameters.Add(i_priority);
                cmd.Parameters.Add(i_follow_up_date);
                cmd.Parameters.Add(i_user_id);
                cmd.Parameters.Add(o_task_id);

                cmd.CommandType = CommandType.StoredProcedure;


                cmd.Connection = connection;
                cmd.ExecuteNonQuery();

                task_id = int.Parse(cmd.Parameters["o_task_id"].Value.ToString());
            }

        }
        /// <summary>
        /// 取基金基本信息
        /// </summary>
        /// <param name="fundId"></param>
        /// <returns></returns>
        public static DataTable Get_dm_fund_information(string fundId, string companyId)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_dm_fund_information", connection);

            MySqlParameter i_fundId = new MySqlParameter("i_fund_id", fundId);
            MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);

            ad.SelectCommand.Parameters.Add(i_fundId);
            ad.SelectCommand.Parameters.Add(i_company_id);
            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;

        }

        public static DataTable Get_dm_company_information(string companyId)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_dm_company_information", connection);

            MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);

            ad.SelectCommand.Parameters.Add(i_company_id);
            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;

        }


        /// <summary>
        /// 搜索公司（目前仅支持对 company_short_name 的搜索)
        /// </summary>
        /// <param name="companyType"></param> 私募证券：1
        /// <param name="keyWord"></param>
        /// <returns></returns>
        public static DataTable Search_dm_company(int? companyType, string keyWord)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_dm_search_company", connection);

            MySqlParameter i_companyType = new MySqlParameter("i_company_type", companyType);
            MySqlParameter i_keyWord = new MySqlParameter("i_key_word", keyWord);

            ad.SelectCommand.Parameters.Add(i_companyType);
            ad.SelectCommand.Parameters.Add(i_keyWord);
            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;

        }

        /// <summary>
        /// 搜索基金（目前仅支持对 fund_short_name 的搜索)
        /// </summary>
        /// <param name="raiseType"></param> 私募：1、公募：2
        /// <param name="keyWord"></param>
        /// <returns></returns>
        public static DataTable Search_dm_fund(int? raiseType, string keyWord)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_dm_search_fund", connection);

            MySqlParameter i_raise_type = new MySqlParameter("i_raise_type", raiseType);
            MySqlParameter i_keyWord = new MySqlParameter("i_key_word", keyWord);

            ad.SelectCommand.Parameters.Add(i_raise_type);
            ad.SelectCommand.Parameters.Add(i_keyWord);
            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;

        }

        public static DataTable Get_Nav(string fundId, DateTime? startDate, DateTime? endDate)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_nav", connection);

            MySqlParameter i_fund_id = new MySqlParameter("i_fund_id", fundId);
            MySqlParameter i_start_date = new MySqlParameter("i_start_date", startDate);
            MySqlParameter i_end_date = new MySqlParameter("i_end_date", endDate);

            ad.SelectCommand.Parameters.Add(i_fund_id);
            ad.SelectCommand.Parameters.Add(i_start_date);
            ad.SelectCommand.Parameters.Add(i_end_date);

            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;
        }

        public static DataTable Get_dm_memo(sbyte jobType, int taskId)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_dm_task_memo", connection);

            MySqlParameter i_job_type = new MySqlParameter("i_job_type", jobType);
            MySqlParameter i_task_id = new MySqlParameter("i_task_id", taskId);

            ad.SelectCommand.Parameters.Add(i_job_type);
            ad.SelectCommand.Parameters.Add(i_task_id);

            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;
        }

        public static void Set_dm_memo(sbyte jobType, int taskId, string memo, int userId)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {

                connection.Open();

                MySqlCommand cmd = new MySqlCommand("sp_set_dm_task_memo", connection);

                MySqlParameter i_job_type = new MySqlParameter("i_job_type", jobType);
                MySqlParameter i_task_id = new MySqlParameter("i_task_id", taskId);
                MySqlParameter i_memo = new MySqlParameter("i_memo", memo);
                MySqlParameter i_userid = new MySqlParameter("i_userid", userId);

                cmd.Parameters.Add(i_job_type);
                cmd.Parameters.Add(i_task_id);
                cmd.Parameters.Add(i_memo);
                cmd.Parameters.Add(i_userid);

                cmd.CommandType = CommandType.StoredProcedure;


                cmd.Connection = connection;
                cmd.ExecuteNonQuery();
            }
        }

        public static DataTable Get_dm_contacts(string companyId)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_dm_get_contacts", connection);

            MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);

            ad.SelectCommand.Parameters.Add(i_company_id);

            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;
        }


        public static int Set_dm_contacts(DataTable dt, string companyId, int userId)
        {
            int ret = 0;

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

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dataRow = dt.Rows[i];

                int o_id;

                if (dataRow.RowState == DataRowState.Deleted)
                {
                    Set_dm_contacts(int.Parse(dataRow["id", DataRowVersion.Original].ToString()), dataRow["company_id", DataRowVersion.Original].ToString(),
                                    dataRow["name", DataRowVersion.Original].ToString(), dataRow["role", DataRowVersion.Original].ToString(),
                                    dataRow["phone", DataRowVersion.Original].ToString(), dataRow["wechat", DataRowVersion.Original].ToString(),
                                    dataRow["email", DataRowVersion.Original].ToString(),
                                    0, userId, out o_id);

                    ret++;
                }
                else if (dataRow.RowState == DataRowState.Added || dataRow.RowState == DataRowState.Modified)
                {

                    int? id = null;
                    if (string.IsNullOrEmpty(dataRow["id"].ToString()) == false)
                        id = int.Parse(dataRow["id"].ToString());


                    Set_dm_contacts(id, companyId, dataRow["name"].ToString(), dataRow["role"].ToString(), dataRow["phone"].ToString(),
                                                   dataRow["wechat"].ToString(), dataRow["email"].ToString(),
                                                   1, userId, out o_id);

                    if (dataRow.RowState == DataRowState.Added)
                    {
                        dataRow["id"] = o_id;
                        dataRow["company_id"] = companyId;
                    }

                    ret++;
                }
            }

            return ret;
        }

        public static void Set_dm_contacts(int? id, string companyId, string name, string role, string phone, 
                                           string wechat, string email, sbyte isvalid, int userId, out int out_id)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {

                connection.Open();

                MySqlCommand cmd = new MySqlCommand("sp_dm_set_contacts", connection);

                MySqlParameter i_id = new MySqlParameter("i_id", id);
                MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);
                MySqlParameter i_name = new MySqlParameter("i_name", name);
                MySqlParameter i_role = new MySqlParameter("i_role", role);
                MySqlParameter i_phone = new MySqlParameter("i_phone", phone);
                MySqlParameter i_wechat = new MySqlParameter("i_wechat", wechat);
                MySqlParameter i_email = new MySqlParameter("i_email", email);
                MySqlParameter i_isvalid = new MySqlParameter("i_isvalid", isvalid);
                MySqlParameter i_userid = new MySqlParameter("i_userid", userId);
                
                MySqlParameter o_id = new MySqlParameter("o_id", DbType.Int32);
                o_id.Direction = ParameterDirection.Output;

                cmd.Parameters.Add(i_id);
                cmd.Parameters.Add(i_company_id);
                cmd.Parameters.Add(i_name);
                cmd.Parameters.Add(i_role);
                cmd.Parameters.Add(i_phone);
                cmd.Parameters.Add(i_wechat);
                cmd.Parameters.Add(i_email);
                cmd.Parameters.Add(i_isvalid);
                cmd.Parameters.Add(i_userid);
                cmd.Parameters.Add(o_id);

                cmd.CommandType = CommandType.StoredProcedure;


                cmd.Connection = connection;
                cmd.ExecuteNonQuery();

                out_id = int.Parse(cmd.Parameters["o_id"].Value.ToString());
            }
        }

        public static DataTable Get_dm_company_cover(string companyId, sbyte? coverType, int? userId)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_dm_company_cover", connection);

            MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);
            MySqlParameter i_cover_type = new MySqlParameter("i_cover_type", coverType);
            MySqlParameter i_userid = new MySqlParameter("i_userid", userId);

            ad.SelectCommand.Parameters.Add(i_company_id);
            ad.SelectCommand.Parameters.Add(i_cover_type);
            ad.SelectCommand.Parameters.Add(i_userid);

            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;
        }

        /// <summary>
        /// 批量更新公司-对接人员关系
        /// </summary>
        /// <param name="updateQuery">Json字符串</param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static int Set_dm_company_cover(string updateQuery, int userId)
        {
            int ret = -1;

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {

                connection.Open();

                MySqlCommand cmd = new MySqlCommand("sp_set_dm_company_cover", connection);

                MySqlParameter i_update_query = new MySqlParameter("i_update_query", updateQuery);
                MySqlParameter i_userid = new MySqlParameter("i_userid", userId);

                cmd.Parameters.Add(i_update_query);
                cmd.Parameters.Add(i_userid);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Connection = connection;
                cmd.ExecuteNonQuery();

                ret = 1;
            }

            return ret;
        }

        /// <summary>
        /// 取基金指数，benchmark_type: 0-主基准、1-分类平均
        /// </summary>
        /// <param name="fundId"></param>
        /// <returns></returns>
        public static DataTable Get_dm_fund_benchmark(string fundId)
        {

            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_dm_fund_benchmark", connection);

            MySqlParameter i_fund_id = new MySqlParameter("i_fund_id", fundId);

            ad.SelectCommand.Parameters.Add(i_fund_id);

            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;
        }
    }
}