MES_Client/TGMES/Core.StlMes.Client.Sale.SaleFundMgt/FundDAL/FundPactAccountDAL/FundPactAccountRelationDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CoreFS.CA06;
using DbHelp = Core.StlMes.Client.Sale.DbHelp;

namespace Core.StlMes.Client.Sale.SaleFundMgt.FundDAL.FundPactAccountDAL
{
    /// <summary>
    /// 合同账户和二级账户关系管理类
    /// </summary>
    public class FundPactAccountRelationDAL:BaseDAL.BaseDAL
    {
        public FundPactAccountRelationDAL(OpeBase ob)
            : base(ob)
        { 
        }

        private const string SQL_QUERY = @" select relation,
                                                            pact_rea_name,
                                                            pact_rea_name_show,
                                                            secondaccount,
                                                            priority,
                                                            importtime,
                                                            importor,
                                                            isvalid,
                                                            deleteor,
                                                            deletetime,
                                                            lastupdatetime from sel_fund_acct_relation where 1 = 1 {0}";

        private const string SQL_INSERT = @"insert into sel_fund_acct_relation(
                                                                relation,
                                                            pact_rea_name,
                                                            secondaccount,
                                                            priority,
                                                            importtime,
                                                            importor,
                                                            isvalid,
                                                            deleteor,
                                                            deletetime,
                                                            lastupdatetime,pact_rea_name_show)
                                                            values('{0}','{1}','{2}','{3}',sysdate,'{4}','{5}','{6}',sysdate,sysdate,'{7}')
                                                            ";


    

        private string GetSqlCondition(string pact_rea_name)
        {
            string sqlCondition = string.Format("  and pact_rea_name = '{0}' ",pact_rea_name);
            return sqlCondition;
        }

        /// <summary>
        /// 返回数据集
        /// </summary>
        /// <param name="sqlCondition"></param>
        /// <returns></returns>
        public DataSet GetSelFundPactAccountRelation(string sqlCondition)
        {
            string sqlStr = string.Format(SQL_QUERY, sqlCondition);
            DataSet ds = base.ExecuteReaderForSaleFund(sqlStr);
            // 设置标题
            base.SetDataSetCaption(ref ds, base.GetColumnNameAndCaption());
            return ds;
        }

        /// <summary>
        /// 根据规则返回数据集，如产线名称4001HB1等
        /// </summary>
        /// <param name="pact_rea_name"></param>
        /// <returns></returns>
        public DataSet GetSelFundPactAccountRelationByRelName(string pact_rea_name)
        {
            return GetSelFundPactAccountRelation(GetSqlCondition(pact_rea_name));
        }

        public List<Model.SEL_FUND_ACCT_RELATION> GetSelFundPactAccountRelationModel(string pact_rea_name)
        {
            DataSet ds = GetSelFundPactAccountRelationByRelName(pact_rea_name);
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                return null;
            List<Model.SEL_FUND_ACCT_RELATION> list = Model.SEL_FUND_ACCT_RELATION.GetSelFundAcctRelationList(ds);
            return list;
        }

        /// <summary>
        /// 通过规则返回优先级最高的记录
        /// </summary>
        /// <param name="pact_rea_name"></param>
        /// <returns></returns>
        public Model.SEL_FUND_ACCT_RELATION GetSelFundPactAccountRelationModelByRelName(string pact_rea_name)
        {
            DataSet ds = GetSelFundPactAccountRelationByRelName(pact_rea_name);
            if (Util.DataSetUtil.Count(ds) == 0)
                return null;
            ds = Util.DataSetUtil.ReturnDsByDescOrderFromSourceDataSet(ds, _priority);
            return Model.SEL_FUND_ACCT_RELATION.GetSelFundAcctRelation(ds.Tables[0].Rows[0]);
             
        }

        // 映射优先级
        private string _priority = "PRIORITY";

        /// <summary>
        /// 通过名称寻找优先级最高的一个账户名称,最高优先级产线名称
        /// </summary>
        /// <param name="pact_rea_name"></param>
        /// <returns></returns>
        public string  GetHighestPriorRealationByRelName(string pact_rea_name)
        {
            List<Model.SEL_FUND_ACCT_RELATION> list = GetSelFundPactAccountRelationModel(pact_rea_name);
            if (list == null)
                return "";
            if (list.Count == 0)
                return "";
            Model.SEL_FUND_ACCT_RELATION real = null;
            foreach (Model.SEL_FUND_ACCT_RELATION re in list)
            {
                // 无效的，继续
                if (re.ISVALID == "1")
                    continue;
                if (re.ISVALID == "0")
                    real = re;
                if (Convert.ToInt16(re.PRIORITY) < Convert.ToInt16(real.PRIORITY))
                {
                    real = re;
                }
            }
            try
            {
                return real.SECONDACCOUNT;
            }
            catch
            {
                return "";
            }
           
        }

        /// <summary>
        /// 通过映射名，实际映射名，二级账户名，优先级查找是否存在同样的
        /// </summary>
        /// <param name="relation"></param>
        /// <param name="pactrealname"></param>
        /// <param name="secondaccount"></param>
        /// <param name="priority"></param>
        /// <returns></returns>
        public DataSet GetSelFundAcctRelationByRelAndRelNameAndSecondActAndPriority(string relation, string pactrealname, string secondaccount, string priority)
        {
            string sqlCondition = string.Format(" and relation = '{0}'   and  pact_rea_name = '{1}'  and secondaccount = '{2}'  and  priority = '{3}' ",
                                                                 relation, pactrealname, secondaccount, priority);

            DataSet ds = GetSelFundAcctRealtionValid(sqlCondition, false);
            if (Util.DataSetUtil.Count(ds) == 0)
                return null;
            return ds;

        }

        public DataSet GetSelFundAcctRealtion(string sqlCondition)
        {
            return base.ExecuteReaderForSaleFund(string.Format(SQL_QUERY, sqlCondition));
        }

        public DataSet GetSelFundAcctRealtion(string sqlCondition, Boolean isSetCaption)
        {
            DataSet ds = GetSelFundAcctRealtion(sqlCondition);
            if (isSetCaption)
            {
                base.SetDataSetCaption(ref ds, base.GetColumnNameAndCaption());
            }
            return ds;
        }

        public DataSet GetSelFundAcctRealtionValid(string sqlCondition, Boolean isSetCaption)
        {
            DataSet ds = GetSelFundAcctRealtion(sqlCondition + " and isvalid = '0' ");
            if (isSetCaption)
            {
                base.SetDataSetCaption(ref ds, base.GetColumnNameAndCaption());
            }
            return ds;
        }

        public void Insert(Model.SEL_FUND_ACCT_RELATION realtion, out string errMsg)
        {
            errMsg = "";
            string sqlStr = string.Format(SQL_INSERT,
                                                     realtion.RELATION,
                                                     realtion.PACT_REA_NAME,
                                                     realtion.SECONDACCOUNT,
                                                     realtion.PRIORITY,
                                                     realtion.IMPORTOR,
                                                     realtion.ISVALID,
                                                     realtion.DELETEOR,
                                                     realtion.PACT_REA_NAME_SHOW);
            base.ExecuteNoReaderForSaleFund(sqlStr, out errMsg);
        }

        /// <summary>
        /// 删除一个映射关系
        /// </summary>
        /// <param name="relation">名称</param>
        /// <param name="pactrealname">合同属性，如产线等</param>
        /// <param name="secondaccount">二级账户名称</param>
        /// <param name="priority">优先级</param>
        /// <param name="deletor">删除人</param>
        /// <param name="errMsg"></param>
        public void Delete(string relation, string pactrealname, string secondaccount, string priority, string deletor, out string errMsg)
        {
            string SQL_DELETE = string.Format(@"  update sel_fund_acct_relation a  set ISVALID = '1' ,DELETEOR = '{4}',DELETETIME =sysdate where a.relation = '{0}'   and a.pact_rea_name = '{1}'
                                             and secondaccount = '{2}'  and  priority = '{3}'  ", relation, pactrealname, secondaccount, priority, deletor);
            base.ExecuteNoReaderForSaleFund(SQL_DELETE, out errMsg);

        }


    }
}