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));
}