MES_Client/TGMES/Core.StlMes.Client.Sale.SaleFundMgt/FundDAL/FundPactAccountDAL/FundPactUsedDetailDAL.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 FundPactUsedDetailDAL:BaseDAL.BaseDAL
    {
       public FundPactUsedDetailDAL(OpeBase ob)
            : base(ob)
        { 
        }
       private const string SQL_STR_QUERY = @"select  trans_seq,
                                                                        trans_type,
                                                                        balancesubject,
                                                                        buyercode,
                                                                        secondaccount,
                                                                        pactno,
                                                                        askplan_id,
                                                                        weight,
                                                                        round(money,3) money,
                                                                        round(askplan_money,3) askplan_money,
                                                                        round(trans_money,3) trans_money,
                                                                        trans_time,
                                                                        ismoneyvalid,
                                                                        trans_person,
                                                                        remark,
                                                                        updatetime
                                                                     from 
                                                                    sel_fund_pact_fundusedetail a where 1 = 1 {0}";
       private const string SQL_STR_INSERT = @" insert into sel_fund_pact_fundusedetail(
                                                                      trans_seq,
                                                                        trans_type,
                                                                        balancesubject,
                                                                        buyercode,
                                                                        secondaccount,
                                                                        pactno,
                                                                        askplan_id,
                                                                        weight,
                                                                        money,
                                                                        askplan_money,
                                                                        trans_money,
                                                                        trans_time,
                                                                        ismoneyvalid,
                                                                        trans_person,
                                                                        remark,
                                                                        updatetime)
                                                                     values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},{8},{9},{10},sysdate,'{12}','{13}','{14}',
                                                                     sysdate)";

       private const string SQL_STR_DELETE = @"delete from sel_fund_pact_fundusedetail where 1 = 1 and trans_seq = '{0}' ";

       private const string SQL_STR_UPDATEPACTMONEYTOUNVALID = @" update sel_fund_pact_fundusedetail set ismoneyvalid = '1'   where BALANCESUBJECT = '{0}'
            and BUYERCODE = '{1}'  and SECONDACCOUNT = '{2}' and pactno = '{3}' and TRANS_TYPE = '{4}' ";

       private const string SQL_STR_UPDATEDLIVTOUNVALID = @" update sel_fund_pact_fundusedetail set ismoneyvalid = '1'   where BALANCESUBJECT = '{0}'
            and BUYERCODE = '{1}'  and SECONDACCOUNT = '{2}' and pactno = '{3}'  and askplan_id = '{4}' and TRANS_TYPE = '{5}' ";

     


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

       public DataSet GetSelFundPactFundUsedDetail(string sqlConditon)
       {
           string sqlStr = string.Format(SQL_STR_QUERY, sqlConditon);
           DataSet ds = base.ExecuteReaderForSaleFund(sqlStr);
           base.SetDataSetCaption(ref ds, base.GetColumnNameAndCaption());
           return ds;
       }

      

       /// <summary>
       /// 通过结算单位，客户单位，二级单位，合同单位获取其信息
       /// </summary>
       /// <param name="acct"></param>
       /// <returns></returns>
       public DataSet GetSelFundPactFundUsedDetailByBalAndBuyercodeAndSecAccountAndPactno(Model.SEL_FUND_PACT_FUNDUSEDETAIL acct)
       {
           string err = "";
           if (!Model.SEL_FUND_PACT_FUNDUSEDETAIL.IsReasonable(acct, out err))
           {
               return null;
           }
           return
               GetSelFundPactFundUsedDetail
               (base.GetSqlConditon(acct.BALANCESUBJECT, acct.BUYERCODE, acct.SECONDACCOUNT, acct.PACTNO));

       }

       /// <summary>
       /// 通过结算单位和合同号返回信息,原则上一个结算单位的合同号是唯一的
       /// </summary>
       /// <param name="balancesubject"></param>
       /// <param name="pactno"></param>
       /// <returns></returns>
       public DataSet GetSelFundPactFundUsedDetailByBalAndPactno(string balancesubject, string pactno)
       {
           return GetSelFundPactFundUsedDetail(base.GetSqlConditon(balancesubject,pactno));
       }

       /// <summary>
       /// 通过结算单位，客户编码，出库指示号获取其现有的有效出库信息
       /// </summary>
       /// <param name="bal"></param>
       /// <param name="buyercode"></param>
       /// <param name="askplanId"></param>
       /// <returns></returns>
       private List<Model.SEL_FUND_PACT_FUNDUSEDETAIL> GetSelFundPctUsedByBalanceAndBuyercodeAndAskplanId(string bal,string buyercode,string askplanId,string delivtype)
       {
           try
           {
               string sqlCondtion = string.Format("  and balancesubject = '{0}'   and buyercode = '{1}' and askplan_id = '{2}'  and ismoneyvalid = '0'  and trans_type = '{3}'", bal, buyercode, askplanId, delivtype);
               return Model.SEL_FUND_PACT_FUNDUSEDETAIL.GetSelFundPactFundUsedDetailList(GetSelFundPactFundUsedDetail(sqlCondtion));
           }
           catch
           {
               return null;
           }

       }
       /// <summary>
       /// 通过结算单位，客户编码，出库指示号获取其现有的有效出库信息
       /// </summary>
       /// <param name="bal"></param>
       /// <param name="buyercode"></param>
       /// <param name="askplanId"></param>
       /// <returns></returns>
       public List<Model.SEL_FUND_PACT_FUNDUSEDETAIL> GetSelFundPctUsedDelivByBalanceAndBuyercodeAndAskplanId(string bal, string buyercode, string askplanId)
       {
           return GetSelFundPctUsedByBalanceAndBuyercodeAndAskplanId(bal,buyercode,askplanId,FundVariable.FundConstVariable._transTypePactDeliver);

       }

       /// <summary>
       ///  通过结算单位，客户编码，出库指示号获取其现有的有效装车信息
       /// </summary>
       /// <param name="bal"></param>
       /// <param name="buyercode"></param>
       /// <param name="askplanId"></param>
       /// <returns></returns>
       public List<Model.SEL_FUND_PACT_FUNDUSEDETAIL> GetSelFundPctUsedLoadByBalanceAndBuyercodeAndAskplanId(string bal, string buyercode, string askplanId)
       {
           return GetSelFundPctUsedByBalanceAndBuyercodeAndAskplanId(bal, buyercode, askplanId, FundVariable.FundConstVariable._transTypePactLoad);

       }

       /// <summary>
       /// 检查一个计划单号是否存在
       /// </summary>
       /// <param name="balancesubject"></param>
       /// <param name="askPlanid"></param>
       /// <returns></returns>
       public Boolean IsAskplanIdExists(string balancesubject, string askPlanid)
       {
           string sqlCondition = string.Format("  and balancesubject = '{0}'  and  askplan_id = '{1}'   and rownum = 1 ",balancesubject,askPlanid);
           DataSet ds = GetSelFundPactFundUsedDetail(sqlCondition);
           return (Util.DataSetUtil.Count(ds) > 0) ? true : false;
       }

       /// <summary>
       /// 通过类型，loadType ，装车，下车，结算等，检查一个车号状态
       /// 保证是有效类型
       /// </summary>
       /// <param name="balancesubject"></param>
       /// <param name="askPlanid"></param>
       /// <param name="loadType"></param>
       /// <returns></returns>
       public Boolean IsAskplanIdExists(string balancesubject, string askPlanid,string loadType)
       {
           string sqlCondition = string.Format("  and ismoneyvalid = '0' and balancesubject = '{0}'  and  askplan_id = '{1}'   and TRANS_TYPE = '{2}' and rownum = 1 ", balancesubject, askPlanid, loadType);
           DataSet ds = GetSelFundPactFundUsedDetail(sqlCondition);
           return (Util.DataSetUtil.Count(ds) > 0) ? true : false;
       }

       /// <summary>
       /// 检查一个车是否有装车冲减
       /// </summary>
       /// <param name="balancesubject"></param>
       /// <param name="askPlanid"></param>
       /// <param name="loadType"></param>
       /// <returns></returns>
       public Boolean IsAskplanIdExistsLoad(string balancesubject, string askPlanid)
       {
           return IsAskplanIdExists(balancesubject, askPlanid, FundVariable.FundConstVariable._transTypePactLoad);
       }

       /// <summary>
       /// 检查一个车是否有结算冲减
       /// </summary>
       /// <param name="balancesubject"></param>
       /// <param name="askPlanid"></param>
       /// <returns></returns>
       public Boolean IsAskplanIdExistsInvoince(string balancesubject, string askPlanid)
       {
           return IsAskplanIdExists(balancesubject, askPlanid, FundVariable.FundConstVariable._transTypePactInvoice);
       }

       

       /// <summary>
       /// 通过结算单位，客户编码，出库指示号获取其现有的有效结算信息
       /// </summary>
       /// <param name="bal"></param>
       /// <param name="buyercode"></param>
       /// <param name="askplanId"></param>
       /// <returns></returns>
       public List<Model.SEL_FUND_PACT_FUNDUSEDETAIL> GetSelFundPctUsedInvoiceByBalanceAndBuyercodeAndAskplanId(string bal, string buyercode, string askplanId)
       {
           return GetSelFundPctUsedByBalanceAndBuyercodeAndAskplanId(bal, buyercode, askplanId, FundVariable.FundConstVariable._transTypePactInvoice);

       }

       private string GetInsertSqlStr(Model.SEL_FUND_PACT_FUNDUSEDETAIL acct, out string err)
       {
           err = "";
           if (!Model.SEL_FUND_PACT_FUNDUSEDETAIL.IsReasonable(acct, out err))
           {
               return "";
           }
           try
           {
               string SQL_INSERT = string.Format(SQL_STR_INSERT,
                                              acct.TRANS_SEQ,
                                              acct.TRANS_TYPE,
                                              acct.BALANCESUBJECT,
                                              acct.BUYERCODE,
                                              acct.SECONDACCOUNT,
                                              acct.PACTNO,
                                              acct.ASKPLAN_ID,
                                              acct.WEIGHT,
                                              acct.MONEY,
                                              acct.ASKPLAN_MONEY,
                                              acct.TRANS_MONEY,
                                           //   acct.TRANS_TIME,
                                              acct.ISMONEYVALID,
                                              acct.TRANS_PERSON,
                                              acct.REMARK);
               return SQL_INSERT;
           }
           catch
           {
               return "";
           }
       }

       /// <summary>
       /// 写入记录，不带事务处理
       /// </summary>
       /// <param name="acct"></param>
       /// <param name="err"></param>
       public void Insert(Model.SEL_FUND_PACT_FUNDUSEDETAIL acct, out string err)
       {
           err = "";
           string SQL_INSERT = GetInsertSqlStr(acct,out err);
           
           if (err.Length > 0)
               return;
           base.ExecuteNoReaderForSaleFund(SQL_INSERT,out err);
       }

       /// <summary>
       /// 写记录，带事务
       /// </summary>
       /// <param name="acct"></param>
       /// <param name="transaction"></param>
       public void Insert(Model.SEL_FUND_PACT_FUNDUSEDETAIL acct, DbHelp.DbTransaction transaction)
       {
          string err = "";
           string SQL_INSERT = GetInsertSqlStr(acct, out err);
           if (err.Length > 0)
           {
               transaction.ErrMsg = err;
               return;
           }
           base.ExecuteNoReaderForSaleFund(SQL_INSERT, transaction);
       }

       /// <summary>
       /// 删除一个明细
       /// </summary>
       /// <param name="busiseq"></param>
       public void Delete(string busiseq)
       {
           string sqlStr = string.Format(SQL_STR_DELETE,busiseq);
           base.ExecuteNoReaderForSaleFund(sqlStr);
       }

       /// <summary>
       /// 针对多次有合同生效扣款的情况，将原来的先设置未无效
       /// </summary>
       /// <param name="balancesubject"></param>
       /// <param name="secondlevelact"></param>
       /// <param name="pactno"></param>
       public void UpdatePactEffectMoneyToUnValid(string balancesubject,string buyercode,string secondlevelact,string pactno,out string errMsg)
       {
           errMsg = "";
           string sqlstr = string.Format(SQL_STR_UPDATEPACTMONEYTOUNVALID, balancesubject, buyercode, secondlevelact, pactno, FundVariable.FundConstVariable._transTypePactEffect);
           base.ExecuteNoReaderForSaleFund(sqlstr,out errMsg);


       }

       public void UpdatePactEffectMoneyToUnValid(string balancesubject, string buyercode, string secondlevelact, string pactno, DbHelp.DbTransaction transaction)
       {
           string sqlstr = string.Format(SQL_STR_UPDATEPACTMONEYTOUNVALID, balancesubject, buyercode, secondlevelact, pactno, FundVariable.FundConstVariable._transTypePactEffect);
           base.ExecuteNoReaderForSaleFund(sqlstr, transaction);

       }

      /// <summary>
       /// 对同计划单号，同合同号有多次冲减的情况，将前面的设置未无效
      /// </summary>
      /// <param name="balancesubject"></param>
      /// <param name="buyercode"></param>
      /// <param name="secondlevelact"></param>
      /// <param name="pactno"></param>
      /// <param name="askplan_id"></param>
      /// <param name="transaction"></param>
       public void UpdateDlivMoneyToUnValid(string balancesubject, string buyercode, string secondlevelact, string pactno, string askplan_id, out string errMsg)
       {
           errMsg = "";
           string sqlstr = string.Format(SQL_STR_UPDATEDLIVTOUNVALID, balancesubject, buyercode, secondlevelact, pactno, askplan_id, FundVariable.FundConstVariable._transTypePactDeliver);
           base.ExecuteNoReaderForSaleFund(sqlstr, out errMsg);

       }
       public void UpdateDlivMoneyToUnValid(string balancesubject, string buyercode, string secondlevelact, string pactno, string askplan_id, DbHelp.DbTransaction transaction)
       {

           string sqlstr = string.Format(SQL_STR_UPDATEDLIVTOUNVALID, balancesubject, buyercode, secondlevelact, pactno, askplan_id,FundVariable.FundConstVariable._transTypePactDeliver);
           base.ExecuteNoReaderForSaleFund(sqlstr, transaction);

       }

       /// <summary>
       /// 将装车数据设置未无效
       /// </summary>
       /// <param name="balancesubject"></param>
       /// <param name="buyercode"></param>
       /// <param name="secondlevelact"></param>
       /// <param name="pactno"></param>
       /// <param name="askplan_id"></param>
       /// <param name="errMsg"></param>
       public void UpdateLoadMoneyToUnValid(string balancesubject, string buyercode, string secondlevelact, string pactno, string askplan_id, out string errMsg)
       {
           errMsg = "";
           string sqlstr = string.Format(SQL_STR_UPDATEDLIVTOUNVALID, balancesubject, buyercode, secondlevelact, pactno, askplan_id, FundVariable.FundConstVariable._transTypePactLoad);
           base.ExecuteNoReaderForSaleFund(sqlstr, out errMsg);

       }
       public void UpdateLoadMoneyToUnValid(string balancesubject, string buyercode, string secondlevelact, string pactno, string askplan_id, DbHelp.DbTransaction transaction)
       {

           string sqlstr = string.Format(SQL_STR_UPDATEDLIVTOUNVALID, balancesubject, buyercode, secondlevelact, pactno, askplan_id, FundVariable.FundConstVariable._transTypePactLoad);
           base.ExecuteNoReaderForSaleFund(sqlstr, transaction);

       }

       /// <summary>
       /// 将结算的车的数据设置为无效
       /// </summary>
       /// <param name="balancesubject"></param>
       /// <param name="buyercode"></param>
       /// <param name="secondlevelact"></param>
       /// <param name="pactno"></param>
       /// <param name="askplan_id"></param>
       /// <param name="errMsg"></param>
       public void UpdateInvoiceMoneyToUnValid(string balancesubject, string buyercode, string secondlevelact, string pactno, string askplan_id, out string errMsg)
       {
           errMsg = "";
           string sqlstr = string.Format(SQL_STR_UPDATEDLIVTOUNVALID, balancesubject, buyercode, secondlevelact, pactno, askplan_id, FundVariable.FundConstVariable._transTypePactInvoice);
           base.ExecuteNoReaderForSaleFund(sqlstr, out errMsg);

       }
       public void UpdateInvoiceMoneyToUnValid(string balancesubject, string buyercode, string secondlevelact, string pactno, string askplan_id, DbHelp.DbTransaction transaction)
       {
           string sqlstr = string.Format(SQL_STR_UPDATEDLIVTOUNVALID, balancesubject, buyercode, secondlevelact, pactno, askplan_id, FundVariable.FundConstVariable._transTypePactInvoice);
           base.ExecuteNoReaderForSaleFund(sqlstr, transaction);

       }

       /// <summary>
       /// 对下车无效的记录，只保留最近的一个
       /// </summary>
       /// <param name="balancesubject"></param>
       /// <param name="buyercode"></param>
       /// <param name="askplan_id"></param>
       /// <param name="errMsg"></param>
       public void DeleteUnValidDelivAskPlanId(string balancesubject, string buyercode, string askplan_id, out string errMsg)
       {
           errMsg = "";
           string lastUPdateTime = GetMaxUpdateTimeByAksplanIdAndTransType(balancesubject, buyercode, askplan_id, FundVariable.FundConstVariable._transTypePactDeliver);
           if (lastUPdateTime.Length == 0)
               return;
           string sqlStr = GetDeleteUnvalidAskplanIdSql(balancesubject, buyercode, askplan_id, FundVariable.FundConstVariable._transTypePactDeliver, lastUPdateTime);
           base.ExecuteNoReaderForSaleFund(sqlStr);
       }

       /// <summary>
       /// 对装车无效的记录，只保留最近的一个
       /// </summary>
       /// <param name="balancesubject"></param>
       /// <param name="buyercode"></param>
       /// <param name="askplan_id"></param>
       /// <param name="errMsg"></param>
       public void DeleteUnValidLoadAskPlanId(string balancesubject, string buyercode, string askplan_id, out string errMsg)
       {
           errMsg = "";
           string lastUPdateTime = GetMaxUpdateTimeByAksplanIdAndTransType(balancesubject, buyercode, askplan_id, FundVariable.FundConstVariable._transTypePactLoad);
           if (lastUPdateTime.Length == 0)
               return;
           string sqlStr = GetDeleteUnvalidAskplanIdSql(balancesubject, buyercode, askplan_id, FundVariable.FundConstVariable._transTypePactLoad, lastUPdateTime);
           base.ExecuteNoReaderForSaleFund(sqlStr);
       }

       /// <summary>
       /// 对结算无效的记录，只保留最近的一个
       /// </summary>
       /// <param name="balancesubject"></param>
       /// <param name="buyercode"></param>
       /// <param name="askplan_id"></param>
       /// <param name="errMsg"></param>
       public void DeleteUnValidInvoinceAskPlanId(string balancesubject, string buyercode, string askplan_id, out string errMsg)
       {
           errMsg = "";
           string lastUPdateTime = GetMaxUpdateTimeByAksplanIdAndTransType(balancesubject, buyercode, askplan_id, FundVariable.FundConstVariable._transTypePactInvoice);
           if (lastUPdateTime.Length == 0)
               return;
           string sqlStr = GetDeleteUnvalidAskplanIdSql(balancesubject, buyercode, askplan_id, FundVariable.FundConstVariable._transTypePactInvoice, lastUPdateTime);
           base.ExecuteNoReaderForSaleFund(sqlStr);
       }

       private string GetDeleteUnvalidAskplanIdSql(string balancesubject, string buyercode, string askplan_id, string transType,string lastUPdatetime)
       {
           string sqlStr = @" delete  from  sel_fund_pact_fundusedetail a 
                                 where balancesubject = '{0}' and buyercode = '{1}' and askplan_id = '{2}' 
                                  and trans_type = '{3}'  and ismoneyvalid = '1'  and  updatetime <> '{4}' ";
           return string.Format(sqlStr,balancesubject,buyercode,askplan_id,transType,lastUPdatetime);
       }

       public string GetMaxUpdateTimeByAksplanIdAndTransType(string balancesubject, string buyercode, string askplan_id,string transType)
       {
           string sqlStr = @" select max(updatetime) updatetime  from  sel_fund_pact_fundusedetail a 
                                 where balancesubject = '{0}' and buyercode = '{1}' and askplan_id = '{2}' 
                                  and trans_type = '{3}'  ";
           DataSet ds = base.ExecuteReaderForSaleFund(string.Format(sqlStr,balancesubject,buyercode,askplan_id,transType));
           try
           {
               return ds.Tables[0].Rows[0][0].ToString();
           }
           catch
           {
               return "";
           }

          

       }






    }
}