MES_Client/TGMES/Core.StlMes.Client.Sale.SaleFundMgt/FundDAL/FundPactAccountDAL/FundPactAccountDAL.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
{
  
    public class FundPactAccountDAL:BaseDAL.BaseDAL
    {
        public FundPactAccountDAL(OpeBase ob)
            : base(ob)
        { 
        }
        private string SQL_STR_QUERY = @" select balancesubject,
                                                            buyercode,
                                                            secondaccount,
                                                            pactno,
                                                            round(totalmoney,3) totalmoney,
                                                           round( leavemoney,3) leavemoney,
                                                            pact_begindate,
                                                            isinvoince,
                                                            importor,
                                                            importtime,
                                                            updatetime 
                                                            from sel_fund_pact_account  where 1 = 1  {0}";

        private string SQL_STR_INSERT = @" insert into  sel_fund_pact_account ( balancesubject,
                                                            buyercode,
                                                            secondaccount,
                                                            pactno,
                                                            totalmoney,
                                                            leavemoney,
                                                            pact_begindate,
                                                            isinvoince,
                                                            importor,
                                                            importtime,
                                                            updatetime ) 
                                                            values('{0}','{1}','{2}','{3}',{4},{5},'{6}','{7}','{8}',sysdate, 
                                                           sysdate)";

        private string SQL_STR_UPDATETOTALMONEY = @"  update  sel_fund_pact_account set totalmoney = totalmoney + {0} ,
                                                            leavemoney = leavemoney + {0} , updatetime = sysdate
                                                            where   balancesubject = '{1}'   and buyercode = '{2}' and secondaccount = '{3}' and  pactno = '{4}'     ";

        private string SQL_STR_UPDATELEAVEMONEY = @"  update  sel_fund_pact_account set 
                                                            leavemoney = leavemoney + {0} , updatetime =sysdate 
                                                            where   balancesubject = '{1}'   and buyercode = '{2}' and secondaccount = '{3}' and  pactno = '{4}'     ";
        private string SQL_STR_DELETE = @" delete from sel_fund_pact_account  where 1 = 1 and balancesubject = '{0}'   and buyercode = '{1}' and secondaccount = '{2}' and  pactno = '{3}'   ";

       

        /// <summary>
        /// 返回数据集
        /// </summary>
        /// <param name="sqlConditon"></param>
        /// <returns></returns>
        public DataSet GetSelFundPactAccount(string sqlConditon)
        {
            string sqlStr = string.Format(SQL_STR_QUERY, sqlConditon);
            return base.ExecuteReaderForSaleFund(sqlStr);
        }

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

        /// <summary>
        /// 通过结算单位，客户名称，secondaccount 查找所有合同账户信息
        /// </summary>
        /// <param name="pact"></param>
        /// <returns></returns>
        public DataSet GetSelFundPactAccountByBalAndBuyercodeAndSecAccount(Model.SEL_FUND_PACT_ACCOUNT pact)
        {
            return GetSelFundPactAccount(base.GetSqlConditon(pact.BALANCESUBJECT,pact.BUYERCODE,pact.SECONDACCOUNT));
        }

        /// <summary>
        /// 通过结算单位，客户名称，secondaccount 查找所有合同账户信息
        /// </summary>
        /// <param name="balancesubject"></param>
        /// <param name="buyercode"></param>
        /// <param name="secondaccount"></param>
        /// <returns></returns>
        public DataSet GetSelFundPactAccountByBalAndBuyercodeAndSecAccount(string balancesubject,string buyercode,string secondaccount)
        {
            return GetSelFundPactAccount(base.GetSqlConditon(balancesubject, buyercode, secondaccount));
        }

        /// <summary>
        /// 通过结算单位，客户名称，secondaccount 查找所有合同账户信息
        /// </summary>
        /// <param name="bal"></param>
        /// <param name="buyercode"></param>
        /// <param name="secondaccount"></param>
        /// <returns></returns>
        public List<Model.SEL_FUND_PACT_ACCOUNT> GetSelFundPactAccountByBalanceAndSecAccount(string bal, string buyercode, string secondaccount)
        {

            return Model.SEL_FUND_PACT_ACCOUNT.GetSelFundPactAccountList(GetSelFundPactAccountByBalAndBuyercodeAndSecAccount(bal, buyercode, secondaccount));

        }

        public DataSet GetSelFundPactAccountByBalAndBuyercodeAndSecAccount(Model.SEL_FUND_PACT_ACCOUNT pact,Boolean isSetCaption)
        {
            return GetSelFundPactAccount(base.GetSqlConditon(pact.BALANCESUBJECT, pact.BUYERCODE, pact.SECONDACCOUNT), isSetCaption);
        }

        /// <summary>
        /// 通过结算单位，客户名称，secondaccount 查找所有合同账户信息,以实体返回
        /// </summary>
        /// <param name="pact"></param>
        /// <returns></returns>
        public List<Model.SEL_FUND_PACT_ACCOUNT> GetSelFundPactAccountModelListByBalAndBuyercodeAndSecAccount(Model.SEL_FUND_PACT_ACCOUNT pact)
        {
            return Model.SEL_FUND_PACT_ACCOUNT.GetSelFundPactAccountList(GetSelFundPactAccountByBalAndBuyercodeAndSecAccount(pact));

        }


        /// <summary>
        /// 通过结算单位，客户名称，secondaccount,合同号 查找所有合同账户信息
        /// </summary>
        /// <param name="pact"></param>
        /// <returns></returns>
        public DataSet GetSelFundPactAccountByBalanceAndBuyerCodeAndSecAndPactno(Model.SEL_FUND_PACT_ACCOUNT pact)
        {
            return GetSelFundPactAccount(base.GetSqlConditon(pact.BALANCESUBJECT, pact.BUYERCODE, pact.SECONDACCOUNT, pact.PACTNO));
        }

        /// <summary>
        ///  通过结算单位，客户名称，secondaccount,合同号 查找所有合同账户信息
        /// </summary>
        /// <param name="pact"></param>
        /// <returns></returns>
        public List<Model.SEL_FUND_PACT_ACCOUNT> GetSelFundPactAccountModelList(Model.SEL_FUND_PACT_ACCOUNT pact)
        {
            DataSet ds = GetSelFundPactAccountByBalanceAndBuyerCodeAndSecAndPactno(pact);
            if (ds == null)
                return null;
            if (ds.Tables.Count == 0)
                return null;
            if (ds.Tables[0].Rows.Count == 0)
                return null;
            return Model.SEL_FUND_PACT_ACCOUNT.GetSelFundPactAccountList(ds);
        }

        /// <summary>
        /// 通过结算单位，客户名称，secondaccount,合同号 查找所有合同账户信息
        /// </summary>
        /// <param name="pact"></param>
        /// <returns></returns>
        public Model.SEL_FUND_PACT_ACCOUNT GetSelFundPactAccountModel(Model.SEL_FUND_PACT_ACCOUNT pact)
        {
            List<Model.SEL_FUND_PACT_ACCOUNT> list = GetSelFundPactAccountModelList(pact);
            if (list == null)
                return null;
            if (list.Count == 0)
                return null;
            return list[0];
 
        }

        /// <summary>
        /// 通过结算单位和客户名称查找一合同号是否存在
        /// </summary>
        /// <param name="bal"></param>
        /// <param name="pactno"></param>
        /// <returns></returns>
        public List<Model.SEL_FUND_PACT_ACCOUNT> GetSelFundPactAccountByBalanceAndPactno(string bal,string buyercode,string pactno)
        {
            return Model.SEL_FUND_PACT_ACCOUNT.GetSelFundPactAccountList(GetSelFundPactAccount(base.GetSqlConditon(bal, buyercode,pactno,"","","")));
 
        }

        public List<Model.SEL_FUND_PACT_ACCOUNT> GetSelFundPactAccountByBalanceAndPactno(string bal, string buyercode, string pactno,out string errMsg)
        {
            errMsg = "";
            return Model.SEL_FUND_PACT_ACCOUNT.GetSelFundPactAccountList(GetSelFundPactAccount(base.GetSqlConditon(bal, buyercode, pactno, "", "", "")));

        }

      

        /// <summary>
        /// 通过结算单位，客户名称，合同号返回合同账户信息
        /// // 原则上同客户单位下只有一个合同账户
        /// </summary>
        /// <param name="bal"></param>
        /// <param name="buyercode"></param>
        /// <param name="pactno"></param>
        /// <returns></returns>
        public Model.SEL_FUND_PACT_ACCOUNT GetSelFundPactAccountByBalanceAndBuyerCodeAndPactno(string bal, string buyercode, string pactno)
        {
            List<Model.SEL_FUND_PACT_ACCOUNT> list = GetSelFundPactAccountByBalanceAndPactno(bal,buyercode,pactno);
            if (list == null)
                return null;
            if (list.Count == 0)
                return null;
            return list[0];

        }

    

        /// <summary>
        /// 通过结算单位，客户名称，secondaccount,合同号 ,获取该合同账户剩余资金
        /// </summary>
        /// <param name="pact"></param>
        /// <returns></returns>
        public double GetSelFundPactAccountLeaveMoney(Model.SEL_FUND_PACT_ACCOUNT pact)
        {
            Model.SEL_FUND_PACT_ACCOUNT fund = GetSelFundPactAccountModel(pact);
            if (fund == null)
                return 0;
            return fund.LEAVEMONEY;
        }

        /// <summary>
        /// 通过结算单位和合同号获取其整个合同账户信息
        /// 同结算单位的情况时只有一个合同号的
        /// </summary>
        /// <param name="balanceSubject"></param>
        /// <param name="pactno"></param>
        /// <returns></returns>
        public DataSet GetSelFundPactAccountByBalanceSubAndPactno(string balanceSubject,string pactno)
        {
            return GetSelFundPactAccount(this.GetSqlConditon(balanceSubject,pactno));
        }

        /// <summary>
        /// 通过结算单位和合同返回其账户信息
        /// </summary>
        /// <param name="balanceSubject"></param>
        /// <param name="pactno"></param>
        /// <returns></returns>
        public  List<Model.SEL_FUND_PACT_ACCOUNT>  GetSelFundPactAccountByBalanceSubAndPactnoByModel(string balanceSubject, string pactno)
        {
            DataSet ds = GetSelFundPactAccountByBalanceSubAndPactno(balanceSubject,pactno);
            if (ds == null)
                return null;
            if (ds.Tables.Count == 0)
                return null;
            if (ds.Tables[0].Rows.Count == 0)
                return null;
            return Model.SEL_FUND_PACT_ACCOUNT.GetSelFundPactAccountList(ds);
        }

        /// <summary>
        /// 新增合同账户信息
        /// </summary>
        /// <param name="acct"></param>
        /// <param name="err"></param>
        public void CreateFundPactAccount(Model.SEL_FUND_PACT_ACCOUNT acct,out string err)
        {
            err = "";
            if (!Model.SEL_FUND_PACT_ACCOUNT.IsReasonable(acct, out err))
                return;
            string sqlStr = string.Format(SQL_STR_INSERT,
                                                     acct.BALANCESUBJECT,acct.BUYERCODE,acct.SECONDACCOUNT,
                                                     acct.PACTNO,acct.TOTALMONEY,acct.LEAVEMONEY,acct.PACT_BEGINDATE,
                                                     acct.ISINVOINCE,acct.IMPORTOR);
            base.ExecuteNoReaderForSaleFund(sqlStr,out err);
        }

        /// <summary>
        /// 新增合同账户信息，带事务
        /// </summary>
        /// <param name="acct"></param>
        /// <param name="transaction"></param>
        public void CreateFundPactAccount(Model.SEL_FUND_PACT_ACCOUNT acct, DbHelp.DbTransaction transaction)
        {
            string err = "";
            if (!Model.SEL_FUND_PACT_ACCOUNT.IsReasonable(acct, out err))
                return;
            string sqlStr = string.Format(SQL_STR_INSERT,
                                                     acct.BALANCESUBJECT, acct.BUYERCODE, acct.SECONDACCOUNT,
                                                     acct.PACTNO, acct.TOTALMONEY, acct.LEAVEMONEY, acct.PACT_BEGINDATE,
                                                     acct.ISINVOINCE, acct.IMPORTOR);
            base.ExecuteNoReaderForSaleFund(sqlStr, transaction);
        }

        public string Sql_CreateFundPactAccount(Model.SEL_FUND_PACT_ACCOUNT acct)
        {
            string err = "";
            if (!Model.SEL_FUND_PACT_ACCOUNT.IsReasonable(acct, out err))
                return "  1 1";
            string sqlStr = string.Format(SQL_STR_INSERT,
                                                     acct.BALANCESUBJECT, acct.BUYERCODE, acct.SECONDACCOUNT,
                                                     acct.PACTNO, acct.TOTALMONEY, acct.LEAVEMONEY, acct.PACT_BEGINDATE,
                                                     acct.ISINVOINCE, acct.IMPORTOR);
            return sqlStr;
        }

        /// <summary>
        /// 删除合同账户，带结算单位，客户单位，二级单位，合同单位
        /// </summary>
        /// <param name="acct"></param>
        /// <param name="errMsg"></param>
        public void DeleteFundPactAccount(Model.SEL_FUND_PACT_ACCOUNT acct, out string errMsg)
        {
            errMsg = "";
            string sqlstr = string.Format(this.SQL_STR_DELETE,acct.BALANCESUBJECT,acct.BUYERCODE,
                acct.SECONDACCOUNT,acct.PACTNO);
            base.ExecuteNoReaderForSaleFund(sqlstr,out errMsg);
        }

        /// <summary>
        /// 更新合同账户总资金和剩余资金
        /// 主要在从二级账户转资金到合同账户，资金增加
        /// or 从合同账户转钱到上级账户，资金减少
        /// </summary>
        /// <param name="acct"></param>
        public void AddFundPactAccountTotalMoneyAndLeavemoney(Model.SEL_FUND_TRANS_INFO acct,out string err )
        {
            err = "";
            // 参数检查
            if (!Model.SEL_FUND_TRANS_INFO.IsReasonable(acct, out err))
            {
                return;
            }
            string sqlStr = string.Format(SQL_STR_UPDATETOTALMONEY, acct.TRANS_MONEY, acct.BALANCESUBJECT,
                                  acct.BUYERCODE,acct.SECONDACCOUNT,acct.PACTNO);
            base.ExecuteNoReaderForSaleFund(sqlStr,out err);
        }

        public void AddFundPactAccountTotalMoneyAndLeavemoney(Model.SEL_FUND_TRANS_INFO acct, DbHelp.DbTransaction transaction)
        {
            string err = "";
            // 参数检查
            if (!Model.SEL_FUND_TRANS_INFO.IsReasonable(acct, out err))
            {
                transaction.ErrMsg = err;
                return;
            }
            string sqlStr = string.Format(SQL_STR_UPDATETOTALMONEY, acct.TRANS_MONEY, acct.BALANCESUBJECT,
                                  acct.BUYERCODE, acct.SECONDACCOUNT, acct.PACTNO);
            base.ExecuteNoReaderForSaleFund(sqlStr, transaction);
        }



        public void AddFundPactAccountTotalMoneyAndLeavemoney(Model.SEL_FUND_TRANS_PACT acct, DbHelp.DbTransaction transaction)
        {
            try
            {
                string err = "";
                if (acct == null)
                {
                    transaction.ErrMsg = "Error";
                    return;
                }
                // 参数检查
                if (acct.TRANS_MONEY == 0)
                    return;
             
                string sqlStr = string.Format(SQL_STR_UPDATETOTALMONEY, acct.TRANS_MONEY, acct.FUND_TRANS_SECONDACCOUNT.FUND_TRANS_CUST.BALANCESUBJECT,
                                      acct.FUND_TRANS_SECONDACCOUNT.FUND_TRANS_CUST.BUYERCODE, acct.FUND_TRANS_SECONDACCOUNT.SECONDACCOUNT, acct.PACTNO);
                base.ExecuteNoReaderForSaleFund(sqlStr, transaction);
            }
            catch(Exception ex)
            {
                transaction.ErrMsg = ex.Message;
            }
        }

        public string Sql_AddFundPactAccountTotalMoneyAndLeavemoney(Model.SEL_FUND_TRANS_PACT acct)
        {
           

                string sqlStr = string.Format(SQL_STR_UPDATETOTALMONEY, acct.TRANS_MONEY, acct.FUND_TRANS_SECONDACCOUNT.FUND_TRANS_CUST.BALANCESUBJECT,
                                      acct.FUND_TRANS_SECONDACCOUNT.FUND_TRANS_CUST.BUYERCODE, acct.FUND_TRANS_SECONDACCOUNT.SECONDACCOUNT, acct.PACTNO);
                return sqlStr;
          
        }

        public void AddFundPactAccountTotalMoneyAndLeavemoney(Model.SEL_FUND_TRANS_PACT acct, out string errMsg)
        {
            try
            {
                errMsg = "";
                if (acct == null)
                {
                    errMsg = "Error";
                    return;
                }
                // 参数检查
                if (acct.TRANS_MONEY == 0)
                    return;

                string sqlStr = string.Format(SQL_STR_UPDATETOTALMONEY, acct.TRANS_MONEY, acct.FUND_TRANS_SECONDACCOUNT.FUND_TRANS_CUST.BALANCESUBJECT,
                                      acct.FUND_TRANS_SECONDACCOUNT.FUND_TRANS_CUST.BUYERCODE, acct.FUND_TRANS_SECONDACCOUNT.SECONDACCOUNT, acct.PACTNO);
                base.ExecuteNoReaderForSaleFund(sqlStr, out errMsg);
            }
            catch (Exception ex)
            {
                errMsg  = ex.Message;
            }
        }


        /// <summary>
        /// 更新剩余资金，总资金部变，剩余资金变化，主要在下车使用等
        /// </summary>
        /// <param name="acct"></param>
        /// <param name="err"></param>
        public void AddFundPactAccountLeaveMoney(Model.SEL_FUND_TRANS_INFO acct, out string err)
        {
            err = "";
            // 参数检查
            if (!Model.SEL_FUND_TRANS_INFO.IsReasonable(acct, out err))
            {
                return;
            }
            string sqlStr = string.Format(SQL_STR_UPDATELEAVEMONEY, acct.TRANS_MONEY, acct.BALANCESUBJECT,
                                  acct.BUYERCODE, acct.SECONDACCOUNT, acct.PACTNO);
            base.ExecuteNoReaderForSaleFund(sqlStr, out err);
        }

        public void AddFundPactAccountLeaveMoney(Model.SEL_FUND_TRANS_PACT acct, out string err)
        {
            err = "";
            // 参数检查
         
            string sqlStr = string.Format(SQL_STR_UPDATELEAVEMONEY, acct.TRANS_MONEY, acct.FUND_TRANS_SECONDACCOUNT.FUND_TRANS_CUST.BALANCESUBJECT,
                                  acct.FUND_TRANS_SECONDACCOUNT.FUND_TRANS_CUST.BUYERCODE, acct.FUND_TRANS_SECONDACCOUNT.SECONDACCOUNT, acct.PACTNO);
            base.ExecuteNoReaderForSaleFund(sqlStr, out err);
        }

        public void AddFundPactAccountLeaveMoney(Model.SEL_FUND_TRANS_PACT acct, DbHelp.DbTransaction transaction)
        {
           

            string sqlStr = string.Format(SQL_STR_UPDATELEAVEMONEY, acct.TRANS_MONEY, acct.FUND_TRANS_SECONDACCOUNT.FUND_TRANS_CUST.BALANCESUBJECT,
                                  acct.FUND_TRANS_SECONDACCOUNT.FUND_TRANS_CUST.BUYERCODE, acct.FUND_TRANS_SECONDACCOUNT.SECONDACCOUNT, acct.PACTNO);
            base.ExecuteNoReaderForSaleFund(sqlStr, transaction);
        }

        /// <summary>
        /// 更新剩余资金，总资金部变，剩余资金变化，主要在下车使用等,带事务
        /// </summary>
        /// <param name="acct"></param>
        /// <param name="transaction"></param>
        public void AddFundPactAccountLeaveMoney(Model.SEL_FUND_TRANS_INFO acct, DbHelp.DbTransaction transaction)
        {
           string  err = "";
            // 参数检查
            if (!Model.SEL_FUND_TRANS_INFO.IsReasonable(acct, out err))
            {
                transaction.ErrMsg = err;
                return;
            }
            string sqlStr = string.Format(SQL_STR_UPDATELEAVEMONEY, acct.TRANS_MONEY, acct.BALANCESUBJECT,
                                  acct.BUYERCODE, acct.SECONDACCOUNT, acct.PACTNO);
            base.ExecuteNoReaderForSaleFund(sqlStr, transaction);
        }



    }
}