| | |
| | | " declare @BaseCurrencyIsoCode varchar(20) \n" + |
| | | " select @BaseCurrencyIsoCode = CurrencyIsoCode from t110703 where isnull(domestic,0) = 1 \n" + |
| | | " select a.periodid as TIME, --时间,会计期间\n" + |
| | | " a.companyid as ENTITY, --公司名称,这里传公司编号\n" + |
| | | " a.docnum as ENTRYFLAG, --凭证标识,凭证号+分录行项目号\n" + |
| | | " a.acctcode as D_ACCOUNT, --科目名称,实际上是科目编号\n" + |
| | | " a.TradePartnerCode as INTERCO, --贸易伙伴,内部单位编号(用于集团内部关联交易)\n" + |
| | | " a.CltCode as KUNNR, --客户编号\n" + |
| | | " a.VndCode as LIFNR, --供应商编号\n" + |
| | | " a.FundTypeCode as XREF2, --SAP款项性质(见SAP款项性质参考)\n" + |
| | | " a.FAlterId as ANBWA, --资产变动类型(与固定资产、无形资产、坏账准备、跌价准备的变动相关,见SAP资产交易类型)\n" + |
| | | " case isnull(a.DcFlag,'') when '借' then 'S' else 'H' end as DRCRK, --借贷标识(S-借方、H贷方)\n" + |
| | | " a.MatCode as MATNR , --物料编号\n" + |
| | | " d.CurrencyIsoCode as RWCUR, --交易币币种(WSL)\n" + |
| | | " case a.dcflag when '借' then a.AmountDebit else a.AmountCredit end as WSL, --交易原(WSL)\n" + |
| | | " @BaseCurrencyIsoCode as RHCUR, --记账本币币种\n" + |
| | | " case a.dcflag when '借' then a.NatAmountDebit else a.NatAmountCredit end as HSL --记账本币金额\n" + |
| | | " --a.DocWord, a.dcflag,b.acctname,c.companyname,\n" + |
| | | " --a.cv1,a.cv1name,a.cv2,a.cv2name,a.cv3,a.cv3name,a.cv4,a.cv4name,a.cv5,a.cv5name,a.Currency \n" + |
| | | " a.companyid as ENTITY, --公司名称,这里传公司编号\n" + |
| | | " a.docnum as ENTRYFLAG, --凭证标识,凭证号+分录行项目号\n" + |
| | | " case when a.acctcode like '6699%' then 'A'+ a.acctcode else a.acctcode end as D_ACCOUNT, --科目名称,实际上是科目编号\n" + |
| | | " a.TradePartnerCode as INTERCO, --贸易伙伴,内部单位编号(用于集团内部关联交易)\n" + |
| | | " a.CltCode as KUNNR, --客户编号\n" + |
| | | " a.VndCode as LIFNR, --供应商编号\n" + |
| | | " a.FundTypeCode as XREF2, --SAP款项性质(见SAP款项性质参考)\n" + |
| | | " a.FAlterId as ANBWA, --资产变动类型(与固定资产、无形资产、坏账准备、跌价准备的变动相关,见SAP资产交易类型)\n" + |
| | | " case isnull(a.DcFlag,'') when '借' then 'S' else 'H' end as DRCRK, --借贷标识(S-借方、H贷方)\n" + |
| | | " a.MatCode as MATNR , --物料编号\n" + |
| | | " d.CurrencyIsoCode as RWCUR, --交易币币种(WSL)\n" + |
| | | " case a.dcflag when '借' then a.AmountDebit else 0.0 - isnull(a.AmountCredit,0) end as WSL, --交易原(WSL)\n" + |
| | | " @BaseCurrencyIsoCode as RHCUR, --记账本币币种\n" + |
| | | " case a.dcflag when '借' then a.NatAmountDebit else 0.0 - isnull(a.NatAmountCredit,0) end as HSL , --记账本币金额\n" + |
| | | " '800' as MANDT , --系统标识\n" + |
| | | " case a.dcflag when '借' then a.NatAmountDebit else 0.0 - isnull(a.NatAmountCredit,0) end as AMOUNT --凭证金额,凭证金额,等于HSL\n" + |
| | | " from t150102 a join v110709 b on a.companyid = b.companyid and a.AcctCode = b.Acctcode\n" + |
| | | " join oCompany c on a.CompanyID = b.companyid \n" + |
| | | " join t110703 d on a.Currency = d.currency\n" + |
| | | " where (isnull(@periodid,'') = '' or a.periodid = @periodid)\n" + |
| | | " where a.periodid = @periodid \n" + |
| | | " and (isnull(@companyid,'') = '' or a.CompanyID = @companyid)\n" + |
| | | " and (isnull(@companyname,'') = '' or c.Companyname like '%'+ @companyname + '%')\n" + |
| | | " and (isnull(@acctcode,'') = '' or a.acctcode = @acctcode)\n" + |
| | |
| | | " and (isnull(@cv2,'') = '' or a.cv2 = @cv2)\n" + |
| | | " and (isnull(@cv3,'') = '' or a.cv3 = @cv3)\n" + |
| | | " and (isnull(@cv4,'') = '' or a.cv4 = @cv4)\n" + |
| | | " and (isnull(@cv5,'') = '' or a.cv5 = @cv5)", new BeanPropertyRowMapper<>(T150102Entry.class)); |
| | | " and (isnull(@cv5,'') = '' or a.cv5 = @cv5)\n" + |
| | | " and a.DocType <> '月末结转' --排除结转损益", new BeanPropertyRowMapper<>(T150102Entry.class)); |
| | | } |
| | | |
| | | @Override |
| | |
| | | " a.Amount5 as AMT_5 , --25-36个月(金额)\n" + |
| | | " a.Amount6 as AMT_6 , --37-48个月(金额)\n" + |
| | | " a.Amount7 as AMT_7 --48个月以上(金额)\n" + |
| | | " from f140933(@companyid,@periodid,'','','',30,180,360,720,1080,1440) a", new BeanPropertyRowMapper<>(InventoryAgeDataEntry.class)); |
| | | " from f140933(@companyid,@periodid,'','','',30,150,180,360,360,360) a", new BeanPropertyRowMapper<>(InventoryAgeDataEntry.class)); |
| | | } |
| | | @Override |
| | | public List<CustomerDataEntry> getCustomerData() { |
| | |
| | | @Override |
| | | public List<CashFlowDetailsEntry> getCashFlowDetails(Post150102Entry entry) { |
| | | return this.jdbcTemplate.query(" declare @periodid varchar(20) =" + GridUtils.prossSqlParm(entry.getPeriodid()) + ",@companyid varchar(20) =" + GridUtils.prossSqlParm(entry.getCompanyid()) +"\n"+ |
| | | " select a.PeriodId as TIME, --时间,会计期间\n" + |
| | | " a.CompanyId as ENTITY , --公司名称,实际是公司编号\n" + |
| | | " a.FluxId as ACCOUNT , --现金流量编码,需要跟SAP的现金流编码保持一致\n" + |
| | | " a.BusinessPartnerCode as BP , --客商编码\n" + |
| | | " a.FluxAmount as AMOUNT , --金额\n" + |
| | | " isnull(a.DocCode,'')+'-'+isnull(a.DocRowId,'') as ENTRYFLAG , --分录标识,凭证号+分录行号\n" + |
| | | " a.Resume as TXT --分录文本(摘要) \n" + |
| | | " select a.PeriodId as TIME, --时间,会计期间\n" + |
| | | " a.CompanyId as ENTITY , --公司名称,实际是公司编号\n" + |
| | | " a.FluxId as ACCOUNT , --现金流量编码,需要跟SAP的现金流编码保持一致\n" + |
| | | " a.BusinessPartnerCode as BP , --客商编码\n" + |
| | | " --AMOUNT 金额,把 B、D、F开头的现金流项目变成负数,这些是属于支出,因为在SAP中用负数显示的\n" + |
| | | " case when a.FluxId in ('B1','B2','B3','B4','B5','D1','D2','D3','D4','F1','F2','F3') \n" + |
| | | " then 0.0 - isnull(a.FluxAmount,0) else a.FluxAmount end as AMOUNT , \n" + |
| | | " isnull(a.DocCode,'')+'-'+isnull(a.DocRowId,'') as ENTRYFLAG , --分录标识,凭证号+分录行号\n" + |
| | | " a.Resume as TXT, --分录文本(摘要) \n" + |
| | | " a.TradePartnerCode as INTERCO --贸易伙伴,内部单位编号(用于集团内部关联交易)\n" + |
| | | " from t150107 a\n" + |
| | | " where (isnull(@periodid,'') = '' or a.periodid = @periodid)\n" + |
| | | " and (isnull(@companyid,'') = '' or a.CompanyID = @companyid)", new BeanPropertyRowMapper<>(CashFlowDetailsEntry.class)); |
| | |
| | | @Override |
| | | public List<VoucherOutstandingEntry> getVoucherOutstanding(Post150102Entry entry) { |
| | | return this.jdbcTemplate.query(" declare @periodid varchar(20) =" + GridUtils.prossSqlParm(entry.getPeriodid()) + ",@companyid varchar(20) =" + GridUtils.prossSqlParm(entry.getCompanyid()) + "\n"+ |
| | | " declare @AccountsReceivableAcctCode varchar(20) --应收帐款科目\n" + |
| | | " select @AccountsReceivableAcctCode = AccountsReceivableAcctCode from t111601 \n" + |
| | | " \n" + |
| | | " declare @BaseCurrencyIsoCode varchar(20) \n" + |
| | | " declare @BaseCurrencyIsoCode varchar(20) \n" + |
| | | " select @BaseCurrencyIsoCode = CurrencyIsoCode from t110703 where isnull(domestic,0) = 1 \n" + |
| | | " select a.periodid as TIME, --时间,会计期间\n" + |
| | | " a.companyid as ENTITY, --公司名称,这里传公司编号\n" + |
| | | " a.acctcode as RACCT, --科目编码\n" + |
| | | " a.CltCode as KUNNR , --客户编码\n" + |
| | | " a.VndCode as LIFNR , --供应商编码\n" + |
| | | " a.FundTypeCode as XREF2 , --SAP款项性质描述\n" + |
| | | " a.docnum as BELNR, --SAP凭证号\n" + |
| | | " a.DocRowID as BUZEI , --SAP凭证行\n" + |
| | | " d.CurrencyIsoCode as RTCUR, --原币币种 \n" + |
| | | " @BaseCurrencyIsoCode as RHCUR , --本币币种\n" + |
| | | " case isnull(a.DcFlag,'') when '借' then a.AmountDebit else a.AmountCredit end as TSL , --原币金额\n" + |
| | | " case isnull(a.DcFlag,'') when '借' then a.NatAmountDebit else a.NatAmountCredit end as HSL, --本币金额\n" + |
| | | " a.BillingNetDate as NETDT, --账龄到期日,账龄到期日(加上信用期之后的账龄起算日)\n" + |
| | | " a.DocDate as BLDAT , --凭证日期\n" + |
| | | " a.BillRealDate as REALDATE ,-- 账龄计算开始日(等于账龄到期日)\n" + |
| | | " a.CurrencyRate as KURSK --汇率,折算汇率\n" + |
| | | " a.companyid as ENTITY, --公司名称,这里传公司编号\n" + |
| | | " a.acctcode as RACCT, --科目编码\n" + |
| | | " a.CltCode as KUNNR , --客户编码\n" + |
| | | " a.VndCode as LIFNR , --供应商编码\n" + |
| | | " a.FundTypeCode as XREF2 , --SAP款项性质描述\n" + |
| | | " a.docnum as BELNR, --SAP凭证号\n" + |
| | | " a.DocRowID as BUZEI , --SAP凭证行\n" + |
| | | " d.CurrencyIsoCode as RTCUR, --原币币种 \n" + |
| | | " @BaseCurrencyIsoCode as RHCUR , --本币币种\n" + |
| | | " case isnull(a.DcFlag,'') when '借' then a.AmountDebit else a.AmountCredit end as TSL , --原币金额\n" + |
| | | " case isnull(a.DcFlag,'') when '借' then a.NatAmountDebit else a.NatAmountCredit end as HSL, --本币金额\n" + |
| | | " case when a.BillRealDate is not null then a.BillingNetDate else a.DocDate end as NETDT, --账龄到期日,账龄到期日(加上信用期之后的账龄起算日)\n" + |
| | | " a.DocDate as BLDAT , --凭证日期\n" + |
| | | " case when a.BillRealDate is not null then a.BillRealDate else case when a.BillRealDate is not null then a.BillingNetDate else a.DocDate end end as REALDATE ,-- 账龄计算开始日(等于账龄到期日)\n" + |
| | | " a.CurrencyRate as KURSK --汇率,折算汇率\n" + |
| | | " from t150102 a join v110709 b on a.companyid = b.companyid and a.AcctCode = b.Acctcode\n" + |
| | | " join oCompany c on a.CompanyID = b.companyid \n" + |
| | | " join t110703 d on a.Currency = d.currency\n" + |
| | | " where a.periodid = @periodid \n" + |
| | | " and a.AcctCode = isnull(@AccountsReceivableAcctCode,'')\n" + |
| | | " and (isnull(@companyid,'') = '' or a.CompanyID = @companyid)\n" + |
| | | " and isnull(FaPiaoMatchedAmount,0) <> (case isnull(a.DcFlag,'') when '借' then isnull(a.AmountDebit,0) else isnull(a.AmountCredit,0) end) --已经匹配的金额\n", new BeanPropertyRowMapper<>(VoucherOutstandingEntry.class)); |
| | | " and isnull(FaPiaoMatchedAmount,0) <> (case isnull(a.DcFlag,'') when '借' then isnull(a.AmountDebit,0) else isnull(a.AmountCredit,0) end) --已经匹配的金额\n" + |
| | | " and isnull(b.isOpenItemMngment,0) = 1", new BeanPropertyRowMapper<>(VoucherOutstandingEntry.class)); |
| | | } |
| | | } |