public IHttpActionResult GetMultiColumn(SearchMultiColumnViewModel condition) { if (string.IsNullOrEmpty(condition.StartPeriod) || string.IsNullOrEmpty(condition.EndPeriod)) { ResMessage.Fail("会计期间不能为空"); } List <MultiColumnViewModels> sheet = _sheet.GetMultiColumn(condition); DatagridViewModel <MultiColumnViewModels> dgSheet = new DatagridViewModel <MultiColumnViewModels>(); dgSheet.rows = sheet; return(Ok(dgSheet)); }
public List <MultiColumnViewModels> GetMultiColumn(SearchMultiColumnViewModel condition) { Guid abid = _cache.GetUserCache().AccountBookID; Account pAccount = _ledger.Accounts.Where(a => a.AbId == abid && a.AccCode == condition.ParentAccCode).FirstOrDefault(); //固定列的明细记录 string frozenFields = "select v.VoucherYear,v.VoucherMonth,(select CertWord from T_Certificate_Word where CwId=v.CertificateWord_CwId) Certword,v.CertWordSN,vd.Abstract,vd.Debit,vd.Credit,a.Direction,0.00 as balance,vd.VdId,v.PaymentTerms,vd.AccId " + "from T_Voucher v ,T_Voucher_Detail vd,T_Account a where v.VId=vd.VId and vd.AccId=a.AccId " + string.Format("and (Debit<>0 or Credit<>0) and v.AbId = {0} ", Utility.ParameterNameString("abid")) + string.Format("and v.PaymentTerms>={0} and v.PaymentTerms <= {1} and a.ParentAccCode={2} ", Utility.ParameterNameString("pts"), Utility.ParameterNameString("pte"), Utility.ParameterNameString("parAccCode")) + "order by v.VoucherYear,v.VoucherMonth "; object[] parames = new object[] { Utility.NewParameter("abid", abid), Utility.NewParameter("pts", condition.StartPeriod), Utility.NewParameter("pte", condition.EndPeriod), Utility.NewParameter("parAccCode", condition.ParentAccCode) }; List <MultiColumnViewModels> multiColumn = _ledger.Database.SqlQuery <MultiColumnViewModels>(frozenFields, parames.Select(p => ((ICloneable)p).Clone()).ToArray()).ToList(); //科目选项 string accountOptions = "select vd.AccId,vd.AccountCode,vd.AccountName,a.Direction " + "from T_Voucher v ,T_Voucher_Detail vd,T_Account a where v.VId=vd.VId and vd.AccId=a.AccId " + string.Format("and (Debit<>0 or Credit<>0) and v.AbId = {0} ", Utility.ParameterNameString("abid")) + string.Format("and v.PaymentTerms>={0} and v.PaymentTerms <= {1} and a.ParentAccCode={2} ", Utility.ParameterNameString("pts"), Utility.ParameterNameString("pte"), Utility.ParameterNameString("parAccCode")) + "group by vd.AccId,vd.AccountCode,vd.AccountName,a.Direction " + "order by a.Direction"; List <BalanceOfSubAccount> accounts = _ledger.Database.SqlQuery <BalanceOfSubAccount>(accountOptions, parames.Select(p => ((ICloneable)p).Clone()).ToArray()).ToList(); //科目余额 string accountBalance = "select VdId,vd.AccId,vd.Debit+vd.Credit as Balance,a.Direction " + "from T_Voucher v ,T_Voucher_Detail vd,T_Account a where v.VId=vd.VId and vd.AccId=a.AccId " + string.Format("and (Debit<>0 or Credit<>0) and v.AbId = {0} ", Utility.ParameterNameString("abid")) + string.Format("and v.PaymentTerms>={0} and v.PaymentTerms <= {1} and a.ParentAccCode={2} ", Utility.ParameterNameString("pts"), Utility.ParameterNameString("pte"), Utility.ParameterNameString("parAccCode")); List <BalanceOfSubAccount> accBalance = _ledger.Database.SqlQuery <BalanceOfSubAccount>(accountBalance, parames.Select(p => ((ICloneable)p).Clone()).ToArray()).ToList(); //期初余额 string InitialBalance = "select vd.AccId,vd.AccountName,sum(vd.Debit+vd.Credit) as Balance,a.Direction " + "from T_Voucher v ,T_Voucher_Detail vd,T_Account a where v.VId=vd.VId and vd.AccId=a.AccId " + string.Format("and (Debit<>0 or Credit<>0) and v.AbId = {0} ", Utility.ParameterNameString("abid")) + string.Format("and v.PaymentTerms < {0} and a.ParentAccCode={1} ", Utility.ParameterNameString("pts"), Utility.ParameterNameString("parAccCode")) + "GROUP BY vd.AccId,vd.AccountName,a.Direction "; List <BalanceOfSubAccount> initBalance = _ledger.Database.SqlQuery <BalanceOfSubAccount>(InitialBalance, parames.Select(p => ((ICloneable)p).Clone()).ToArray()).ToList(); //本期合计 string TotalOfTheCurrentPeriod = "select '本期合计' as Abstract,v.PaymentTerms,sum(vd.Debit) as Debit,sum(vd.Credit) as Credit " + "from T_Voucher v ,T_Voucher_Detail vd,T_Account a where v.VId=vd.VId and vd.AccId=a.AccId " + string.Format("and (Debit<>0 or Credit<>0) and v.AbId = {0} ", Utility.ParameterNameString("abid")) + string.Format("and v.PaymentTerms>={0} and v.PaymentTerms <= {1} and a.ParentAccCode={2} ", Utility.ParameterNameString("pts"), Utility.ParameterNameString("pte"), Utility.ParameterNameString("parAccCode")) + "group by v.PaymentTerms "; List <MultiColumnViewModels> TotalOfTheCurrentPeriods = _ledger.Database.SqlQuery <MultiColumnViewModels>(TotalOfTheCurrentPeriod, parames.Select(p => ((ICloneable)p).Clone()).ToArray()).ToList(); List <string> fpt = this.GetFullPaymentTerms(condition.StartPeriod, condition.EndPeriod); return(this.DataIntegrate(multiColumn, accounts, accBalance, initBalance, fpt, pAccount, TotalOfTheCurrentPeriods)); }