MES_Client/TGMES/Core.StlMes.Client.Sale.SaleFundMgt/FundDAL/FundSecondLevelAccountDAL/FundSecondlevelAccountDAL.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.FundSecondLevelAccountDAL
{
    /// <summary>
    /// 二级账户管理，包括查询，创建，删除等
    /// </summary>
    public class FundSecondlevelAccountDAL:BaseDAL.BaseDAL
    {
        public FundSecondlevelAccountDAL(OpeBase ob)
            : base(ob)
        { 
        }
        private const string SQL_STR_QUERY = @"select balancesubject,
                                                                        buyercode,
                                                                        secondaccount,
                                                                        round(totalmoney,3) totalmoney,
                                                                        round(leavemoney,3) leavemoney,
                                                                        importor,
                                                                        importtime,
                                                                        updatetime from sel_fund_secondlevel_account where 1 = 1 {0}";
        private const string SQL_STR_DELETE = @" delete  from sel_fund_secondlevel_account where 1 = 1 {0}";

        private const string SQL_STR_ADDMONEY = @" update   sel_fund_secondlevel_account 
                                                                  set   totalmoney = totalmoney + {0} , leavemoney = leavemoney + {0},
                                                                  updatetime = sysdate where 1 = 1
                                                                  and  balancesubject = '{1}'  and buyercode = '{2}'  and secondaccount = '{3}'   ";

        private const string SQL_STR_ADDLEAVEMONEY = @" update   sel_fund_secondlevel_account 
                                                                  set   leavemoney = leavemoney + {0},
                                                                  updatetime = sysdate  where 1 = 1
                                                                  and  balancesubject = '{1}'  and buyercode = '{2}'  and secondaccount = '{3}'   ";


        private const string SQL_STR_INSERT = " insert into sel_fund_secondlevel_account ( balancesubject,buyercode,"
                               + " secondaccount,totalmoney,leavemoney,importor,importtime,"
                               + " updatetime) "
                               + "  values('{0}', '{1}','{2}',{3},{4},'{5}',sysdate,sysdate) ";

        private string GetSqlCondition(string balanceSubject, string buyerCode)
        {
            string sqlConditon = string.Format("  and balancesubject = '{0}'   and buyercode = '{1}' ", balanceSubject, buyerCode);
            return sqlConditon;
        }

        private string GetSqlCondition(string balanceSubject, string buyerCode,string secondAccount)
        {
            string sqlConditon = string.Format("  and balancesubject = '{0}'   and buyercode = '{1}'  and  secondaccount = '{2}' ", balanceSubject, buyerCode, secondAccount);
            return sqlConditon;
        }

        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="sqlCondition"></param>
        /// <returns></returns>
        public DataSet GetSelFundSecondlevelAccountInfo(string sqlCondition)
        {
            string sqlStr = string.Format(SQL_STR_QUERY,sqlCondition);
            return base.ExecuteReaderForSaleFund(sqlStr);
        }

        public DataSet GetSelFundSecondlevelAccountInfo(string sqlCondition,Boolean isSetCaption)
        {
            string sqlStr = string.Format(SQL_STR_QUERY, sqlCondition);
            DataSet ds = base.ExecuteReaderForSaleFund(sqlStr);
            if (isSetCaption)
            {
                base.SetDataSetCaption(ref ds, base.GetColumnNameAndCaption());
            }
            return ds;
        }

        /// <summary>
        /// 通过结算单位和客户编码获取所有二级账户信息
        /// </summary>
        /// <param name="balanceSubject"></param>
        /// <param name="buyerCode"></param>
        /// <returns></returns>
        public DataSet GetSelFundSecondLevelAccountInfoByBalanceSubjectAndBuyerCode(string balanceSubject, string buyerCode)
        {
            return GetSelFundSecondlevelAccountInfo(GetSqlCondition(balanceSubject, buyerCode));
        }

        public DataSet GetSelFundSecondLevelAccountInfoByBalanceSubjectAndBuyerCode(string balanceSubject, string buyerCode,Boolean isSetCaption)
        {
            return GetSelFundSecondlevelAccountInfo(GetSqlCondition(balanceSubject, buyerCode),true);
        }

        /// <summary>
        /// 通过结算单位和客户名称返回所有二级账户列表
        /// </summary>
        /// <param name="balanceSubject"></param>
        /// <param name="buyerCode"></param>
        /// <returns></returns>
        public List<Model.SEL_FUND_SECONDLEVEL_ACCOUNT> GetSelFundSecondAccountByBalanceSubjectAndBuyercode(string balanceSubject, string buyerCode)
        {
            DataSet ds = GetSelFundSecondLevelAccountInfoByBalanceSubjectAndBuyerCode(balanceSubject, buyerCode);
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                return null;
            return Model.SEL_FUND_SECONDLEVEL_ACCOUNT.GetSelFundSecondLevelAccountList(ds);
        }


        /// <summary>
        /// 通过通过结算单位和客户名称以及二级账户名称返回二级账户信息，如果没有，则返回空
        /// </summary>
        /// <param name="balanceSubject"></param>
        /// <param name="buyerCode"></param>
        /// <param name="secondeAcctName"></param>
        /// <returns></returns>
        public Model.SEL_FUND_SECONDLEVEL_ACCOUNT GetSelFundSecondAccountBySecondAccount(string balanceSubject, string buyerCode, string secondeAcctName)
        {
            List<Model.SEL_FUND_SECONDLEVEL_ACCOUNT> list =
                GetSelFundSecondAccountByBalanceSubjectAndBuyercode(balanceSubject, buyerCode);
            if (list == null)
                return null;
            if (list.Count == 0)
                return null;
            // 二级名称相同则返回
            foreach (Model.SEL_FUND_SECONDLEVEL_ACCOUNT acct in list)
            {
                if (acct.SECONDACCOUNT == secondeAcctName)
                    return acct;
            }
            return null;
        }

        /// <summary>
        /// 新增记录
        /// </summary>
        /// <param name="account"></param>
        /// <param name="errMsg"></param>
        public void AdSecondLevelAccount(Model.SEL_FUND_SECONDLEVEL_ACCOUNT account, out string errMsg)
        {
            errMsg = "";
           string sqlStr = string.Format(SQL_STR_INSERT, account.BALANCESUBJECT, account.BUYERCODE, account.SECONDACCOUNT,
                                           account.TOTALMONEY, account.LEAVEMONEY,
                                           account.IMPORTOR);
            base.ExecuteNoReaderForSaleFund(sqlStr, out errMsg);
        }

        /// <summary>
        /// 创建二级账户,
        /// </summary>
        /// <param name="account"></param>
        /// <param name="errMsg"></param>
        public void CreateSecondLevelAccount(Model.SEL_FUND_SECONDLEVEL_ACCOUNT account, out string errMsg)
        {
            errMsg = "";
            account.TOTALMONEY = 0;
            account.LEAVEMONEY = 0;
            AdSecondLevelAccount(account,out errMsg);
        }

        /// <summary>
        /// 创建二级账户，带事务
        /// </summary>
        /// <param name="account"></param>
        /// <param name="transaction"></param>
        public void CreateSecondLevelAccount(Model.SEL_FUND_SECONDLEVEL_ACCOUNT account, DbHelp.DbTransaction transaction)
        {
            account.TOTALMONEY = 0;
            account.LEAVEMONEY = 0;
           string sqlStr = string.Format(SQL_STR_INSERT, account.BALANCESUBJECT, account.BUYERCODE, account.SECONDACCOUNT,
                                           account.TOTALMONEY, account.LEAVEMONEY,
                                           account.IMPORTOR);
            base.ExecuteNoReaderForSaleFund(sqlStr,transaction);
        }

        public string SqlCreateSecondLevelAccount(Model.SEL_FUND_SECONDLEVEL_ACCOUNT account)
        {
            account.TOTALMONEY = 0;
            account.LEAVEMONEY = 0;
            string sqlStr = string.Format(SQL_STR_INSERT, account.BALANCESUBJECT, account.BUYERCODE, account.SECONDACCOUNT,
                                            account.TOTALMONEY, account.LEAVEMONEY,
                                            account.IMPORTOR);
            return sqlStr;
        }

       

        /// <summary>
        /// 删除二级账户信息,根据结算单位，客户编码，二级账户名删除
        /// </summary>
        /// <param name="account"></param>
        /// <param name="errMsg"></param>
        public void DeleteSecondLevelAccount(Model.SEL_FUND_SECONDLEVEL_ACCOUNT account,out string errMsg)
        {
            errMsg = "";
            string sqlStr = string.Format(SQL_STR_DELETE, GetSqlCondition(account.BALANCESUBJECT, account.BUYERCODE, account.SECONDACCOUNT));
            base.ExecuteNoReaderForSaleFund(sqlStr,out errMsg);
        }

        /// <summary>
        /// 删除二级账户信息，带事务
        /// </summary>
        /// <param name="account"></param>
        /// <param name="transaction"></param>
        public void DeleteSecondLevelAccount(Model.SEL_FUND_SECONDLEVEL_ACCOUNT account, DbHelp.DbTransaction transaction)
        {
            string sqlStr = string.Format(SQL_STR_DELETE, GetSqlCondition(account.BALANCESUBJECT, account.BUYERCODE, account.SECONDACCOUNT));
            base.ExecuteNoReaderForSaleFund(sqlStr, transaction);
        }

        /// <summary>
        /// 向二级账户增加资金，总资金和剩余资金均增加
        /// 资金为负数，则总资金和剩余资金均减少
        /// </summary>
        public void AddMoneyToSecondLevelAccount(Model.SEL_FUND_TRANS_INFO fund,out string errmsg)
        {
            errmsg = "";
            if (fund == null)
                return;
            if (fund.TRANS_MONEY == 0)
                return;
            string sqlStr = string.Format(SQL_STR_ADDMONEY, fund.TRANS_MONEY, fund.BALANCESUBJECT,fund.BUYERCODE,fund.SECONDACCOUNT);
            base.ExecuteNoReaderForSaleFund(sqlStr,out errmsg);
        }


        /// <summary>
        /// 向二级账户增加资金，总资金和剩余资金均增加，带事务
        /// </summary>
        /// <param name="fund"></param>
        /// <param name="transaction"></param>
        public void AddMoneyToSecondLevelAccount(Model.SEL_FUND_TRANS_INFO fund, DbHelp.DbTransaction transaction)
        {
            if (fund == null)
                return;
            if (fund.TRANS_MONEY == 0)
                return;
            string sqlStr = string.Format(SQL_STR_ADDMONEY, fund.TRANS_MONEY, fund.BALANCESUBJECT,fund.BUYERCODE,fund.SECONDACCOUNT);
            base.ExecuteNoReaderForSaleFund(sqlStr,transaction);
        }

        public void AddMoneyToSecondLevelAccount(Model.SEL_FUND_TRANS_SECONDACCOUNT fund, DbHelp.DbTransaction transaction)
        {
            try
            {
                if (fund == null)
                    return;
                if (fund.TRANS_MONEY == 0)
                    return;
                string sqlStr = string.Format(SQL_STR_ADDMONEY, fund.TRANS_MONEY, fund.FUND_TRANS_CUST.BALANCESUBJECT, fund.FUND_TRANS_CUST.BUYERCODE, fund.SECONDACCOUNT);
                base.ExecuteNoReaderForSaleFund(sqlStr, transaction);
            }
            catch(Exception ex)
            {
                transaction.ErrMsg = ex.Message;
            }
        }

        public string Sql_AddMoneyToSecondLevelAccount(Model.SEL_FUND_TRANS_SECONDACCOUNT fund)
        {
           
                string sqlStr = string.Format(SQL_STR_ADDMONEY, fund.TRANS_MONEY, fund.FUND_TRANS_CUST.BALANCESUBJECT, fund.FUND_TRANS_CUST.BUYERCODE, fund.SECONDACCOUNT);
                return sqlStr;
        }

        public void AddMoneyToSecondLevelAccount(Model.SEL_FUND_TRANS_SECONDACCOUNT fund, out string errMsg)
        {
            errMsg = "";
            try
            {
                if (fund == null)
                    return;
                if (fund.TRANS_MONEY == 0)
                    return;
                string sqlStr = string.Format(SQL_STR_ADDMONEY, fund.TRANS_MONEY, fund.FUND_TRANS_CUST.BALANCESUBJECT, fund.FUND_TRANS_CUST.BUYERCODE, fund.SECONDACCOUNT);
                base.ExecuteNoReaderForSaleFund(sqlStr, out errMsg);
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
            }
        }

        /// <summary>
        /// 二级账户剩余资金变化，总资金不变，主要在二级账户转三级账户，三级账户转二级账户
        /// 资金为负则是剩余资金减少
        /// </summary>
        /// <param name="fund"></param>
        /// <param name="errmsg"></param>
        public void AddSecondLevelAccountLeavemoney(Model.SEL_FUND_TRANS_INFO fund,out string errmsg)
        {
            errmsg = "";
            if (fund == null)
                return;
            if (fund.TRANS_MONEY == 0)
                return;
            if (!Model.SEL_FUND_TRANS_INFO.IsReasonable(fund, out errmsg))
            {
                return;
            }
            string sqlStr = string.Format(SQL_STR_ADDLEAVEMONEY, fund.TRANS_MONEY, fund.BALANCESUBJECT, fund.BUYERCODE, fund.SECONDACCOUNT);
            base.ExecuteNoReaderForSaleFund(sqlStr,out errmsg);
        }

        public void AddSecondLevelAccountLeavemoney(Model.SEL_FUND_TRANS_SECONDACCOUNT fund, out string errmsg)
        {
            errmsg = "";
            if (fund == null)
            {
                errmsg = "Error";
                return;
            }
            if (fund.TRANS_MONEY == 0)
            {
                errmsg = "Error";
                return;
            }
            if (fund.FUND_TRANS_CUST == null)
            {
                errmsg = "Error";
                return;
            }
            string sqlStr = string.Format(SQL_STR_ADDLEAVEMONEY, fund.TRANS_MONEY, fund.FUND_TRANS_CUST.BALANCESUBJECT, fund.FUND_TRANS_CUST.BUYERCODE, fund.SECONDACCOUNT);
            base.ExecuteNoReaderForSaleFund(sqlStr, out errmsg);
        }


        /// <summary>
        /// 二级账户剩余资金变化，总资金不变，主要在二级账户转三级账户，三级账户转二级账户
        /// 资金为负则是剩余资金减少
        /// 带事务处理
        /// </summary>
        /// <param name="fund"></param>
        /// <param name="errmsg"></param>
        public void AddSecondLevelAccountLeavemoney(Model.SEL_FUND_TRANS_INFO fund, DbHelp.DbTransaction transaction)
        {
            string errmsg = "";
            if (fund == null)
                return;
            if (fund.TRANS_MONEY == 0)
                return;
            // 参数检查
            if (!Model.SEL_FUND_TRANS_INFO.IsReasonable(fund, out errmsg))
            {
                transaction.ErrMsg = errmsg;
                return;
            }
            string sqlStr = string.Format(SQL_STR_ADDLEAVEMONEY, fund.TRANS_MONEY, fund.BALANCESUBJECT, fund.BUYERCODE, fund.SECONDACCOUNT);
            base.ExecuteNoReaderForSaleFund(sqlStr, transaction);
        }

        public void AddSecondLevelAccountLeavemoney(Model.SEL_FUND_TRANS_SECONDACCOUNT fund, DbHelp.DbTransaction transaction)
        {
            if (fund == null)
            {
                transaction.ErrMsg = "Error";
                return;
            }
            if (fund.TRANS_MONEY == 0)
            {
                return;
            }
            if (fund.FUND_TRANS_CUST == null)
            {
                transaction.ErrMsg = "Error";
                return;
            }
            // 参数检查
          
            string sqlStr = string.Format(SQL_STR_ADDLEAVEMONEY, fund.TRANS_MONEY, fund.FUND_TRANS_CUST.BALANCESUBJECT, fund.FUND_TRANS_CUST.BUYERCODE, fund.SECONDACCOUNT);
            base.ExecuteNoReaderForSaleFund(sqlStr, transaction);
        }

        public string Sql_AddSecondLevelAccountLeavemoney(Model.SEL_FUND_TRANS_SECONDACCOUNT fund)
        {
           
            string sqlStr = string.Format(SQL_STR_ADDLEAVEMONEY, fund.TRANS_MONEY, fund.FUND_TRANS_CUST.BALANCESUBJECT, fund.FUND_TRANS_CUST.BUYERCODE, fund.SECONDACCOUNT);
            return sqlStr;
        }
    
    }
}