MES_Client/TGMES/Core.StlMes.Client.Sale.SaleFundMgt/FundDAL/FundInOutDAL/FundInOutDAL.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.FundInOutDAL
{
   
    public class FundInOutDAL:BaseDAL.BaseDAL
    {
        private const string SQL_SEL_FUND_CUST_FUNDINOUT = @" select busiseq,
                                                                                                balancesubject,
                                                                                                buyercode,
                                                                                                inoutmoney,
                                                                                                INOUTTIME,
                                                                                                fundtype,
                                                                                                billno,
                                                                                                isvalid,
                                                                                                remark,
                                                                                                importor,
                                                                                                importtime,
                                                                                                leavemoney ,secondaccount,affrimper,affrimdate ,
                                                                                                money_deliver,prebuyercode,orderno
                                                                                                from sel_fund_cust_fundinout  where   1 = 1  {0}";
        private const string SQL_SEL_FUND_CUST_FUNDINOUT_INSERT = @" insert into sel_fund_cust_fundinout
                                                                                                (BUSISEQ,
                                                                                                BALANCESUBJECT,
                                                                                                BUYERCODE,
                                                                                                INOUTMONEY,
                                                                                                FUNDTYPE,
                                                                                                BILLNO,
                                                                                                ISVALID,
                                                                                                REMARK,
                                                                                                ORDERNO,
                                                                                                IMPORTOR,
                                                                                                IMPORTTIME,
                                                                                                LEAVEMONEY,INOUTTIME,SECONDACCOUNT,MONEY_DELIVER
                                                                                                   ,PREBUYERCODE,INVOICE_MONTH) values(
                                                                                                 '{12}','{0}','{1}',{2},'{3}','{4}','{5}','{6}','{7}','{8}',sysdate
                                                                                                 ,{9},to_date('{10}','yyyymmddhh24miss'),'{11}','{13}','{14}',GET_FISCAL_MONTH()) ";

        private const string SQL_DELETEBYBUSISEQ = @"delete from sel_fund_cust_fundinout a where a.busiseq = '{0}' ";
        private const string SQL_UPDATEBYBUSISEQ = @" update sel_fund_cust_fundinout a  set isvalid = '1'  where a.busiseq = '{0}' ";


        public FundInOutDAL(OpeBase ob): base(ob)
        { 
        }
        private string GetSqlCondition(string balancesubject, string buyercode)
        {
            return string.Format(" and balancesubject = '{0}'   and buyercode = '{1}' ", balancesubject, buyercode);
        }


        /// <summary>
        /// 返回数据集
        /// </summary>
        /// <param name="sqlCondition"></param>
        /// <returns></returns>
        public DataSet GetSelFundCustFundInOut(string sqlCondition)
        {
            string sqlStr = string.Format(SQL_SEL_FUND_CUST_FUNDINOUT,sqlCondition);
            return base.ExecuteReader(sqlStr);
        }

        /// <summary>
        /// 返回数据集，带标题
        /// </summary>
        /// <param name="sqlCondition"></param>
        /// <param name="isSetCaption"></param>
        /// <returns></returns>
        public DataSet GetSelFundCustFundInOut(string sqlCondition,Boolean isSetCaption)
        {
            DataSet ds = GetSelFundCustFundInOut(sqlCondition);
            if (isSetCaption)
            {
                base.SetDataSetCaption(ref ds,base.GetColumnNameAndCaption());
            }
            return ds;
        }

        /// <summary>
        /// 通过结算主体和客户名称返回记录
        /// </summary>
        /// <param name="balanceSubject"></param>
        /// <param name="buyerCode"></param>
        /// <returns></returns>
        public DataSet GetSelFundCustFundInOutByBalanceSubjectAndBuyerCode(string balanceSubject,string buyerCode )
        {
            return GetSelFundCustFundInOut(GetSqlCondition(balanceSubject,buyerCode));
        }

        /// <summary>
        /// 通过结算主体和客户名称返回记录,初始化标题,记录开始时间，结束时间
        /// </summary>
        /// <param name="balanceSubject"></param>
        /// <param name="buyerCode"></param>
        /// <param name="begintime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public DataSet GetSelFundCustFundInOutByBalanceSubjectAndBuyerCodeAndInOutDate(string balanceSubject, string buyerCode,string begintime,string endtime)
        {
            string sqlcondition = GetSqlCondition(balanceSubject, buyerCode);
            sqlcondition = sqlcondition + ConvertStringDateValueToDbLanguage("inouttime", begintime, endtime);
           
            return GetSelFundCustFundInOut(sqlcondition, true);
        }

        public DataSet GetSelFundCustFundInOutByBalanceSubjectAndInOutDate(string balanceSubject, string begintime, string endtime)
        {
            string sqlcondition = string.Format("  and balancesubject  = '{0}' ",balanceSubject);
            sqlcondition = sqlcondition + ConvertStringDateValueToDbLanguage("inouttime", begintime, endtime);

            return GetSelFundCustFundInOut(sqlcondition, true);
        }

        /// <summary>
        /// 通过结算主体和客户名称返回记录,初始化标题
        /// </summary>
        /// <param name="balanceSubject">结算单位</param>
        /// <param name="buyerCode">客户单位</param>
        /// <param name="isSetCaption">是否初始化dataset 标题</param>
        /// <returns></returns>
        public DataSet GetSelFundCustFundInOutByBalanceSubjectAndBuyerCode(string balanceSubject, string buyerCode,Boolean isSetCaption)
        {
            return GetSelFundCustFundInOut(GetSqlCondition(balanceSubject, buyerCode), isSetCaption);
        }

        public List<Model.SEL_FUND_CUST_FUNDINOUT> GetGetSelFundCustFundInOutModelByBalanceSubjectAndBuyerCode(string balanceSubject, string buyerCode)
        {
            DataSet ds = GetSelFundCustFundInOutByBalanceSubjectAndBuyerCode( balanceSubject, buyerCode );
            return Model.SEL_FUND_CUST_FUNDINOUT.GetSelFundCustFundInOutList(ds);
        }

        private string GetInsertSqlStrByFundInandOutModel(Model.SEL_FUND_CUST_FUNDINOUT inout)
        {
            if (inout == null)
                return "";
            try
            {
                string sqlStr = string.Format(SQL_SEL_FUND_CUST_FUNDINOUT_INSERT,
                       inout.BALANCESUBJECT,
                       inout.BUYERCODE,
                       inout.INOUTMONEY,
                       inout.FUNDTYPE,
                       inout.BILLNO,
                       "0",
                       inout.REMARK,
                       inout.ORDERNO,
                       inout.IMPORTOR,
                       inout.LEAVEMONEY, inout.INOUTTIME,inout.SECONDACCOUNT,inout.BUSISEQ,inout.MONEY_DELIVER,
                       inout.PREBUYERCODE
                       );
                return sqlStr;
            }
            catch
            { }
            return "";
        }

        /// <summary>
        /// 写入一个进出账记录，带事务
        /// </summary>
        /// <param name="inout"></param>
        /// <param name="transaction"></param>
        public void Insert_SelFundCustFundinout(Model.SEL_FUND_CUST_FUNDINOUT inout,Core.StlMes.Client.Sale.DbHelp.DbTransaction transaction)
        {
            string InsertSqlStr = GetInsertSqlStrByFundInandOutModel(inout);
            if (InsertSqlStr.Length == 0)
                return;
            string err = "";
            if(! Model.SEL_FUND_CUST_FUNDINOUT.IsReasonable(inout,out err))
            {
                transaction.ErrMsg = err;
                return;
            }
            try
            {
                base.ExecuteNoQueryTransaction(InsertSqlStr, transaction);
            }
            catch
            { 
            }
        }

        public string  Sql_Insert_SelFundCustFundinout(Model.SEL_FUND_CUST_FUNDINOUT inout)
        {
            string InsertSqlStr = GetInsertSqlStrByFundInandOutModel(inout);
            return InsertSqlStr;
        }

        public string Sql_UpdateSelFundCustFundInoutStatusToValidt(string busiseq,string person)
        {
            string sql = " update sel_fund_cust_fundinout  set isvalid = '1' ,affrimper = '{0}',affrimdate = sysdate where busiseq = '{1}' ";
            sql = string.Format(sql,person,busiseq);
            return sql;
        }

        public string Sql_UpdateSelFundCustFundInoutStatusToUnValidt(string busiseq, string person)
        {
            string sql = " update sel_fund_cust_fundinout  set isvalid = '0' ,affrimper = '',affrimdate = null where busiseq = '{0}' ";
            sql = string.Format(sql, busiseq);
            return sql;
        }

        public string Sql_DeleteSelFundCustFundInoutRecord(string busiseq, string person)
        {
            string sql = " delete from  sel_fund_cust_fundinout  where busiseq = '{0}'  and isvalid = '0' ";
            sql = string.Format(sql, busiseq);
            return sql;
        }


        /// <summary>
        /// 写入一个进出账记录
        /// </summary>
        /// <param name="inout"></param>
        /// <param name="errMsg"></param>
        public void Insert_SelFundCustFundinout(Model.SEL_FUND_CUST_FUNDINOUT inout,out string errMsg)
        {
            string InsertSqlStr = GetInsertSqlStrByFundInandOutModel(inout);
            errMsg = "";
            if (InsertSqlStr.Length == 0)
                return;
            if (!Model.SEL_FUND_CUST_FUNDINOUT.IsReasonable(inout, out errMsg))
            {
                return;
            }

            try
            {
                base.ExecuteNoReader(InsertSqlStr, out errMsg);
            }
            catch
            {
            }
        }


        /// <summary>
        /// 删除一个记录
        /// </summary>
        /// <param name="buseSeq"></param>
        /// <param name="errMsg"></param>
        public void Delete_SelFundCustFundinout(string buseSeq, out string errMsg)
        {
            string sqlStr = "";
            sqlStr = string.Format(SQL_DELETEBYBUSISEQ, buseSeq);
            errMsg = "";
            try
            {
                base.ExecuteNoReader(sqlStr, out errMsg);
            }
            catch
            {
            }
        }


        public void Delete_SelFundCustFundinout(string buseSeq, DbHelp.DbTransaction transaction)
        {
            string sqlStr = "";
            sqlStr = string.Format(SQL_DELETEBYBUSISEQ, buseSeq);
            try
            {
                base.ExecuteNoQueryTransaction(sqlStr, transaction);
            }
            catch
            {
            }
        }

        /// <summary>
        /// 删除一条记录，指示将标志位改为无效
        /// </summary>
        /// <param name="buseSeq"></param>
        /// <param name="errMsg"></param>
        public void Update_SelFundCustFundinout(string buseSeq, out string errMsg)
        {
            string sqlStr = " ";
            sqlStr = string.Format(SQL_UPDATEBYBUSISEQ, buseSeq);
            errMsg = "";
            try
            {
                base.ExecuteNoReader(sqlStr, out errMsg);
            }
            catch
            {
            }
        }

        public void Update_SelFundCustFundinout(string buseSeq, DbHelp.DbTransaction transaction)
        {
            string sqlStr = " ";
            sqlStr = string.Format(SQL_UPDATEBYBUSISEQ, buseSeq);
            try
            {
                base.ExecuteNoQueryTransaction(sqlStr, transaction);
            }
            catch
            {
            }
        }

    }
}