MES_Client/TGMES/Core.StlMes.Client.Sale.SaleFundMgt/FundDAL/FundCustAccountDAL/FundCustAccountDAL.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.FundCustAccountDAL
{
    /// <summary>
    /// SEL_FUND_CUST_ACCOUNT 管理
    /// 主要包括资金入账，总账增加，出账总账
    /// 剩余金额减少
    /// 同时还有创建客户账户功能
    /// </summary>
    public class FundCustAccountDAL:BaseDAL.BaseDAL
    {
        public FundCustAccountDAL(OpeBase ob)
            : base(ob)
        { 
        }
        private string SQL_SEL_FUND_CUST_ACCOUNT = @"select balancesubject,
                                                                                    buyercode,
                                                                                   round(totalmoney,3) totalmoney,
                                                                        round(leavemoney,3) leavemoney,
                                                                                    importor,
                                                                                    importtime,
                                                                                    updatetime from sel_fund_cust_account where 1 = 1 {0} ";
        private string SQL_SEL_FUND_CUST_ACCOUNT_INSERT = @"insert into sel_fund_cust_account  (balancesubject,
                                                                                    buyercode,
                                                                                    totalmoney,
                                                                                    leavemoney,
                                                                                    importor,
                                                                                    importtime,
                                                                                    updatetime)  values ('{0}','{1}',{2},{3},'{4}',sysdate,sysdate) ";

        private string SQL_DELETE = @"delete from sel_fund_cust_account where 1 = 1 and balancesubject = '{0}'  and buyercode = '{1}'  ";

        /// <summary>
        /// 获取客户账户表数据集
        /// </summary>
        /// <param name="sqlCondition"></param>
        /// <returns></returns>
        public DataSet GetSelFundCustAccount(string sqlCondition)
        {
            string sqlStr = string.Format(SQL_SEL_FUND_CUST_ACCOUNT,sqlCondition);
            DataSet ds = base.ExecuteReaderForSaleFund(sqlStr);
            return ds;
        }

        /// <summary>
        /// 获取账户表数据集
        /// </summary>
        /// <param name="sqlCondition"></param>
        /// <param name="isSetDataSetCaption">是否设置数据集的caption</param>
        /// <returns></returns>
        public DataSet GetSelFundCustAccount(string sqlCondition,Boolean isSetDataSetCaption)
        {
            string sqlStr = string.Format(SQL_SEL_FUND_CUST_ACCOUNT, sqlCondition);
            DataSet ds = base.ExecuteReaderForSaleFund(sqlStr);
            if (isSetDataSetCaption)
            base.SetDataSetCaption(ref ds, base.GetColumnNameAndCaption());
            return ds;
        }

        /// <summary>
        /// 通过结算单位和客户编码获取其账户数据
        /// </summary>
        /// <param name="balancesubject"></param>
        /// <param name="buyerCode"></param>
        /// <returns></returns>
        public DataSet GetSelFundCustAccountInfo(string balancesubject, string buyerCode)
        {
            string sqlConditon = string.Format(" and  balancesubject = '{0}'  and  buyercode = '{1}' ", balancesubject,buyerCode);
            return GetSelFundCustAccount(sqlConditon);
        }

        public DataSet GetSelFundCustAccountInfo(string balancesubject, string buyerCode, Boolean isSetDataSetCaption)
        {
            string sqlConditon = string.Format(" and  balancesubject = '{0}'  and  buyercode = '{1}' ", balancesubject, buyerCode, isSetDataSetCaption);
            return GetSelFundCustAccount(sqlConditon, isSetDataSetCaption);
        }

        /// <summary>
        /// 获取一个结算单位下的所有客户单位
        /// </summary>
        /// <param name="balancesubject"></param>
        /// <returns></returns>
        public DataSet GetSelFundCustAccountInfo(string balancesubject)
        {
            string sqlConditon = string.Format(" and  balancesubject = '{0}'  ", balancesubject);
            return GetSelFundCustAccount(sqlConditon,true);
        }

        /// <summary>
        /// 通过结算编码和客户编码返回其实体类，
        /// </summary>
        /// <param name="balancesubject"></param>
        /// <param name="buyerCode"></param>
        /// <returns></returns>
        public Model.SEL_FUND_CUST_ACCOUNT GetSelFundCustAccountModelByBalanceAndBuyercode(string balancesubject, string buyerCode)
        {
            DataSet ds = GetSelFundCustAccountInfo(balancesubject, buyerCode);
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            return Model.SEL_FUND_CUST_ACCOUNT.GetSelFundCustAccount(ds.Tables[0].Rows[0]);
        }

        public List<Model.SEL_FUND_CUST_ACCOUNT> GetSelFundCustAccountModelByBalanceList(string balancesubject)
        {
            DataSet ds = GetSelFundCustAccountInfo(balancesubject);
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            return Model.SEL_FUND_CUST_ACCOUNT.GetSelFundCustAccountList(ds);
        }

        private string GetInsertSqlByModel(Model.SEL_FUND_CUST_ACCOUNT account)
        {

            string sqlStr = string.Format(SQL_SEL_FUND_CUST_ACCOUNT_INSERT, account.BALANCESUBJECT, 
                account.BUYERCODE,
                account.TOTALMONEY,
                account.LEAVEMONEY,
                account.IMPORTOR
            );
            return sqlStr;
           
        }

        /// <summary>
        /// 写入记录
        /// </summary>
        /// <param name="account"></param>
        /// <param name="errMsg"></param>
        public void Insert_SelFundCustAccount(Model.SEL_FUND_CUST_ACCOUNT account,out string errMsg)
        {
            errMsg = "";
            string sqlStr = GetInsertSqlByModel(account);
            base.ExecuteNoReaderForSaleFund(sqlStr,out errMsg);
 
        }

        public void Insert_SelFundCustAccount(Model.SEL_FUND_CUST_ACCOUNT account, DbHelp.DbTransaction transaction)
        {
            string sqlStr = GetInsertSqlByModel(account);
            base.ExecuteNoReaderForSaleFund(sqlStr, transaction);

        }

        public string  Sql_Insert_SelFundCustAccount(Model.SEL_FUND_CUST_ACCOUNT account)
        {
            string sqlStr = GetInsertSqlByModel(account);
            return sqlStr;

        }

        /// <summary>
        /// 增加账户资金,总资金和剩余资金均增加,为负数则均减少
        /// 为0的均不处理
        /// </summary>
        /// <param name="account"></param>
        /// <param name="transaction"></param>
        public void AddMoneyToCustAccount(Model.SEL_FUND_CUST_ACCOUNT account, double money, DbHelp.DbTransaction transaction)
        {
            if (money == 0)
                return;
            string sqlStr = " update  sel_fund_cust_account set totalmoney = totalmoney + {0},  leavemoney = leavemoney + {1} ,"
                             + "updatetime = sysdate  where 1 = 1 and  balancesubject = '{2}'   and buyercode = '{3}' ";
            sqlStr = string.Format(sqlStr,money,money,account.BALANCESUBJECT,account.BUYERCODE);
            base.ExecuteNoReaderForSaleFund(sqlStr, transaction);
        }

        public string Sql_AddMoneyToCustAccount(Model.SEL_FUND_CUST_ACCOUNT account, double money)
        {
            if (money == 0)
                return " 111";
            string sqlStr = " update  sel_fund_cust_account set totalmoney = totalmoney + {0},  leavemoney = leavemoney + {1} ,"
                             + "updatetime = sysdate  where 1 = 1 and  balancesubject = '{2}'   and buyercode = '{3}' ";
            sqlStr = string.Format(sqlStr, money, money, account.BALANCESUBJECT, account.BUYERCODE);
            return sqlStr;
        }

        /// <summary>
        /// 剩余资金变化，为正则剩余资金增加，为负则剩余资金减少
        /// </summary>
        /// <param name="balancesubject"></param>
        /// <param name="buyerCode"></param>
        /// <param name="money"></param>
        public void AddLeaveMoneyCustAccount(string balancesubject, string buyerCode, double money, DbHelp.DbTransaction transaction)
        {
            if (money == 0)
                return;
            string sqlStr = " update  sel_fund_cust_account set  leavemoney = leavemoney + {0} ,"
                          + "updatetime = sysdate   where 1 = 1 and  balancesubject = '{1}'   and buyercode = '{2}' ";
            sqlStr = string.Format(sqlStr, money, balancesubject, buyerCode);
            base.ExecuteNoReaderForSaleFund(sqlStr, transaction);

        }

        public void AddLeaveMoneyCustAccount(Model.SEL_FUND_TRANS_CUST cust, DbHelp.DbTransaction transaction)
        {
            try
            {
                if (cust == null)
                {
                    transaction.ErrMsg = "Error";
                    return;
                }
                if (cust.TRANS_MONEY == 0)
                    return;

                string sqlStr = " update  sel_fund_cust_account set  leavemoney = leavemoney + {0} ,"
                              + "updatetime =sysdate  where 1 = 1 and  balancesubject = '{1}'   and buyercode = '{2}' ";
                sqlStr = string.Format(sqlStr, cust.TRANS_MONEY, cust.BALANCESUBJECT, cust.BUYERCODE);
                base.ExecuteNoReaderForSaleFund(sqlStr, transaction);
            }
            catch
            { 
            }

        }

        public string Sql_AddLeaveMoneyCustAccount(Model.SEL_FUND_TRANS_CUST cust)
        {
            string sqlStr = " update  sel_fund_cust_account set  leavemoney = leavemoney + {0} ,"
                            + "updatetime =sysdate  where 1 = 1 and  balancesubject = '{1}'   and buyercode = '{2}' ";
            sqlStr = string.Format(sqlStr, cust.TRANS_MONEY, cust.BALANCESUBJECT, cust.BUYERCODE);
            return sqlStr;

        }

        public void AddLeaveMoneyCustAccount(Model.SEL_FUND_TRANS_CUST cust, out string errMsg)
        {
            errMsg = "";
            try
            {
                if (cust == null)
                {
                    errMsg = "Error";
                    return;
                }
                if (cust.TRANS_MONEY == 0)
                    return;

                string sqlStr = " update  sel_fund_cust_account set  leavemoney = leavemoney + {0} ,"
                              + "updatetime =sysdate   where 1 = 1 and  balancesubject = '{1}'   and buyercode = '{2}' ";
                sqlStr = string.Format(sqlStr, cust.TRANS_MONEY, cust.BALANCESUBJECT, cust.BUYERCODE);
                base.ExecuteNoReaderForSaleFund(sqlStr, out errMsg);
            }
            catch(Exception ex)
            {
                errMsg = ex.Message;
            }

        }

        public void AddLeaveMoneyCustAccount(string balancesubject, string buyerCode, double money)
        {
            if (money == 0)
                return;
            string sqlStr = " update  sel_fund_cust_account set  leavemoney = leavemoney + {0} ,"
                          + "updatetime =sysdate  where 1 = 1 and  balancesubject = '{1}'   and buyercode = '{2}' ";
            sqlStr = string.Format(sqlStr, money, balancesubject, buyerCode);
            base.ExecuteNoReaderForSaleFund(sqlStr);

        }

        /// <summary>
        /// 创建客户账户信息
        /// 总资金和剩余资金为0
        /// </summary>
        /// <param name="balancesubject"></param>
        /// <param name="buyerCode"></param>
        public void CreateCustAccount(Model.SEL_FUND_CUST_ACCOUNT account, out string errMsg)
        {
            errMsg = "";
            this.Insert_SelFundCustAccount(account, out errMsg);
        }

        public void CreateCustAccount(Model.SEL_FUND_CUST_ACCOUNT account, DbHelp.DbTransaction transaction)
        {
            this.Insert_SelFundCustAccount(account, transaction);
        }



        /// <summary>
        /// 删除客户账户信息
        /// </summary>
        /// <param name="balancesubject"></param>
        /// <param name="buyerCode"></param>
        public void DeleteCustAccount(string balancesubject, string buyerCode, out string errMsg)
        {
            errMsg = "";
            string sqlStr = string.Format(SQL_DELETE,balancesubject,buyerCode);
            base.ExecuteNoReaderForSaleFund(sqlStr, out errMsg);
        }

        /// <summary>
        /// 删除客户账户，带事务控制
        /// </summary>
        /// <param name="balancesubject"></param>
        /// <param name="buyerCode"></param>
        /// <param name="transaction"></param>
        public void DeleteCustAccount(string balancesubject, string buyerCode, DbHelp.DbTransaction transaction)
        {
            string sqlStr = string.Format(SQL_DELETE, balancesubject, buyerCode);
            base.ExecuteNoReaderForSaleFund(sqlStr, transaction);


        }



        /// <summary>
        /// 通过结算单位和客户编码获取总的剩余金额
        /// </summary>
        /// <param name="balancesubject">结算单位</param>
        /// <param name="buyerCode">客户编码</param>
        /// <returns></returns>
        public double GetLeaveMoneyByBalanceSubjectAndBuyerCode(string balancesubject, string buyerCode)
        {
            Model.SEL_FUND_CUST_ACCOUNT account = GetSelFundCustAccountModelByBalanceAndBuyercode(balancesubject, buyerCode);
            if (account != null)
                return account.LEAVEMONEY;
            else
                return 0;
        }

        /// <summary>
        /// 通过结算单位和客户编码获取总金额
        /// </summary>
        /// <param name="balancesubject">结算单位</param>
        /// <param name="buyerCode">客户编码</param>
        /// <returns></returns>
        public double GetTotalMoneyByBalanceSubjectAndBuyerCode(string balancesubject, string buyerCode)
        {
            Model.SEL_FUND_CUST_ACCOUNT account = GetSelFundCustAccountModelByBalanceAndBuyercode(balancesubject, buyerCode);
            if (account != null)
                return account.TOTALMONEY;
            else
                return 0;
        }

      

    }
}