public List <Voucher> SearchVoucher(SearchConditionViewModel condition)
{
_abid = _cache.GetUserCache().AccountBookID;
//生成可查询的凭证集合
var vouchers = _ledger.Vouchers.Where(v => v.AbId == _abid);
if (!string.IsNullOrEmpty(condition.StartPeriod))
{
vouchers = vouchers.Where(v => v.PaymentTerms.CompareTo(condition.StartPeriod) >= 0);
}
if (!string.IsNullOrEmpty(condition.EndPeriod))
{
vouchers = vouchers.Where(v => v.PaymentTerms.CompareTo(condition.EndPeriod) <= 0);
}
if (!string.IsNullOrEmpty(condition.CertWord))
{
vouchers = vouchers.Where(v => v.CertificateWord.CertWord == condition.CertWord);
}
vouchers = vouchers.OrderByDescending(v => v.VId)
.Include(v => v.VoucherDetails)
.Include(v => v.CertificateWord)
.Include("VoucherDetails.Account");
return(vouchers.ToList());
}
public IHttpActionResult ViewVoucher(SearchConditionViewModel condition)
{
var voucher = _voucher.VoucherToSearchVoucherViewModel(_voucher.SearchVoucher(condition));
DatagridViewModel <SearchVoucherViewModel> dgsv = new DatagridViewModel <SearchVoucherViewModel>();
dgsv.rows = voucher;
return(Ok(dgsv));
}
public IHttpActionResult GetDetailSheet(SearchConditionViewModel condition)
{
List <DetailSheetViewModels> sheet = _sheet.GetDetailSheet(condition);
DatagridViewModel <DetailSheetViewModels> dgSheet = new DatagridViewModel <DetailSheetViewModels>();
dgSheet.rows = sheet;
return(Ok(dgSheet));
}
public IHttpActionResult GetAccountBalance(SearchConditionViewModel condition)
{
if (string.IsNullOrEmpty(condition.StartPeriod) || string.IsNullOrEmpty(condition.EndPeriod))
{
ResMessage.Fail("会计期间不能为空");
}
List <AccountBalanceViewModels> sheet = _sheet.GetAccountBalance(condition);
DatagridViewModel <AccountBalanceViewModels> dgSheet = new DatagridViewModel <AccountBalanceViewModels>();
dgSheet.rows = sheet;
return(Ok(dgSheet));
}
/// <summary>
/// 生成明细账列表数据。
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
public List <DetailSheetViewModels> GetDetailSheet(SearchConditionViewModel condition)
{
Guid abid = _cache.GetUserCache().AccountBookID;
object[] parames = new object[] {
Utility.NewParameter("abid", abid),
Utility.NewParameter("startterm", condition.StartPeriod),
Utility.NewParameter("endterm", condition.EndPeriod),
Utility.NewParameter("accid", condition.AccId)
};
List <DetailSheetViewModels> detailSheet = new List <DetailSheetViewModels>();
//获取查询的所有会计期间
List <string> paymentTerms = _ledger.Database.SqlQuery <string>(string.Format("select PaymentTerms from T_Voucher where AbId = {0} and {1} <= PaymentTerms and PaymentTerms <= {2} group by PaymentTerms order by PaymentTerms",
Utility.ParameterNameString("abid"), Utility.ParameterNameString("startterm"), Utility.ParameterNameString("endterm")),
parames).ToList();
#region 期初余额
//计算期初发生余额
string initBalance = "select " +
" GETDATE() as VoucherDate," +
" '' as CertWord," +
" '期初余额' as Abstract," +
" sum(vd.Debit) as Debit," +
" sum(vd.Credit) as Credit," +
" '平' as Direction," +
"min(v.PaymentTerms) as PaymentTerms," +
" case a.Direction when '借' then sum(vd.Debit)-sum(vd.Credit) when '贷' then sum(vd.Credit)-sum(vd.Debit) end as Balance " +
" from T_Voucher v inner join T_Voucher_Detail vd on v.VId=vd.VId " +
" left join T_Account a on a.AccId=vd.AccId " +
string.Format(" where v.AbId={0} and vd.AccId={1} ", Utility.ParameterNameString("abid"), Utility.ParameterNameString("accid")) +
string.Format(" and v.PaymentTerms < {0} ", Utility.ParameterNameString("startterm")) +
" group by vd.AccId,a.Direction";
Account account = _ledger.Accounts.Where(a => a.AccId == condition.AccId).FirstOrDefault();
DetailSheetViewModels initDetail = _ledger.Database.SqlQuery <DetailSheetViewModels>(initBalance, parames.Select(p => ((ICloneable)p).Clone()).ToArray()).FirstOrDefault();
if (initDetail == null)
{
initDetail = new DetailSheetViewModels();
initDetail.Abstract = "期初余额";
initDetail.Direction = "平";
}
initDetail.VoucherDate = new DateTime(Convert.ToInt32(condition.StartPeriod.Substring(0, 4)), Convert.ToInt32(condition.StartPeriod.Substring(4)), 1);
initDetail.PaymentTerms = condition.StartPeriod;
if (account != null && account.InitialBalance > 0)
{
//加上科目余额
if (account.Direction == "借")
{
initDetail.Debit += account.InitialBalance;
}
if (account.Direction == "贷")
{
initDetail.Credit += account.InitialBalance;
}
}
//期初余额方向，默认显示“平”
if (initDetail.Debit > 0 || initDetail.Credit > 0)
{
initDetail.Direction = account.Direction;
}
//加入集合
detailSheet.Add(initDetail);
#endregion
#region 明细
//各期的明细
string detail = "select " +
" v.VoucherDate," +
" cw.CertWord+'-'+CONVERT(nvarchar(10),v.certwordsn) as CertWord," +
" vd.Abstract," +
" vd.Debit," +
" vd.Credit," +
" a.Direction," +
" v.PaymentTerms," +
" 0.00 as Balance " +
" from T_Voucher v inner join T_Voucher_Detail vd on v.VId=vd.VId " +
" left join T_Account a on a.AccId=vd.AccId " +
" left join T_Certificate_Word cw on v.CertificateWord_CwId=cw.CwId " +
string.Format(" where v.AbId={0} and vd.AccId={1} ", Utility.ParameterNameString("abid"), Utility.ParameterNameString("accid")) +
string.Format(" and {0} <= v.PaymentTerms and v.PaymentTerms <= {1} ", Utility.ParameterNameString("startterm"), Utility.ParameterNameString("endterm")) +
" order by v.VoucherYear,v.VoucherMonth";
//计算明细余额
var detailRecord = _ledger.Database.SqlQuery <DetailSheetViewModels>(detail, parames.Select(p => ((ICloneable)p).Clone()).ToArray()).ToList();
decimal balance = initDetail.Balance;
foreach (DetailSheetViewModels dsvm in detailRecord)
{
if (dsvm.Direction == "借")
{
balance += (dsvm.Debit - dsvm.Credit);
}
if (dsvm.Direction == "贷")
{
balance += (dsvm.Credit - dsvm.Debit);
}
dsvm.Balance = balance;
}
detailSheet.AddRange(detailRecord);
//本期合计
string detailMonth = "select " +
" max(v.VoucherDate) as VoucherDate," +
" '' as CertWord," +
" '本期合计' as Abstract," +
" sum(vd.Debit) as Debit," +
" sum(vd.Credit) as Credit," +
" min(a.Direction) as Direction," +
" max(v.PaymentTerms) as PaymentTerms," +
" 0.00 as Balance " +
" from T_Voucher v inner join T_Voucher_Detail vd on v.VId=vd.VId " +
" left join T_Account a on a.AccId=vd.AccId " +
string.Format(" where v.AbId={0} and vd.AccId={1} ", Utility.ParameterNameString("abid"), Utility.ParameterNameString("accid")) +
string.Format(" and {0} <= v.PaymentTerms and v.PaymentTerms <= {1} ", Utility.ParameterNameString("startterm"), Utility.ParameterNameString("endterm")) +
" group by v.VoucherYear,v.VoucherMonth" +
" order by v.VoucherYear,v.VoucherMonth";
var detailRecordM = _ledger.Database.SqlQuery <DetailSheetViewModels>(detailMonth, parames.Select(p => ((ICloneable)p).Clone()).ToArray()).ToList();
balance = initDetail.Balance;
foreach (DetailSheetViewModels dsvm in detailRecordM)
{
if (dsvm.Direction == "借")
{
balance += (dsvm.Debit - dsvm.Credit);
}
if (dsvm.Direction == "贷")
{
balance += (dsvm.Credit - dsvm.Debit);
}
dsvm.Balance = balance;
}
detailSheet.AddRange(detailRecordM);
#endregion
#region 本年累计
string detailYear = "select " +
" max(v.VoucherDate) as VoucherDate," +
" '' as CertWord," +
" '本年累计' as Abstract," +
" sum(vd.Debit) as Debit," +
" sum(vd.Credit) as Credit," +
" min(a.Direction) as Direction," +
" max(v.PaymentTerms) as PaymentTerms," +
" 0.00 as Balance " +
" from T_Voucher v inner join T_Voucher_Detail vd on v.VId=vd.VId " +
" left join T_Account a on a.AccId=vd.AccId " +
string.Format(" where v.AbId={0} and vd.AccId={1} ", Utility.ParameterNameString("abid"), Utility.ParameterNameString("accid")) +
string.Format(" and v.PaymentTerms <= {0} ", Utility.ParameterNameString("endterm")) +
" group by v.VoucherYear" +
" order by v.VoucherYear";
List <DetailSheetViewModels> orderDetail = new List <DetailSheetViewModels>();
foreach (string p in paymentTerms)
{
var detailRecordY = _ledger.Database.SqlQuery <DetailSheetViewModels>(detailYear,
Utility.NewParameter("abid", abid),
Utility.NewParameter("accid", condition.AccId),
Utility.NewParameter("endterm", p)).ToList();
foreach (DetailSheetViewModels dsvm in detailRecordY)
{
if (dsvm.Direction == "借")
{
dsvm.Balance = initDetail.Balance + (dsvm.Debit - dsvm.Credit);
}
if (dsvm.Direction == "贷")
{
dsvm.Balance = initDetail.Balance + (dsvm.Credit - dsvm.Debit);
}
}
detailSheet.AddRange(detailRecordY);
//按会计期间排序(单个期间的顺序已排好只需按期间排列)
orderDetail.AddRange(detailSheet.Where(d => d.PaymentTerms == p).ToList());
}
#endregion
return(orderDetail);
}
public List <VoucherSummaryViewModels> GetVoucherSummary(SearchConditionViewModel condition)
{
return(new List <VoucherSummaryViewModels>());
}
public List <AccountBalanceViewModels> GetAccountBalance(SearchConditionViewModel condition)
{
Guid abid = _cache.GetUserCache().AccountBookID;
string initBalance = "select vd.AccId,vd.AccountCode,vd.AccountName,'init' as Period,a.Direction," +
"(case a.Direction when '借' then SUM(vd.Debit - vd.Credit) when '贷' then SUM(vd.Credit - vd.Debit) end) as Balance " +
"from T_Voucher v, T_Voucher_Detail vd, T_Account a " +
"where v.VId = vd.VId and vd.AccId = a.AccId " +
string.Format("and v.AbId = {0} ", Utility.ParameterNameString("abid")) +
string.Format("and v.VoucherYear = {0} and v.VoucherMonth < {1} ", Utility.ParameterNameString("year"), Utility.ParameterNameString("minmonth")) +
"group by vd.AccId, vd.AccountCode, vd.AccountName, a.Direction ";
string curBalance = "select vd.AccId,vd.AccountCode,vd.AccountName,'cur' as Period,a.Direction," +
"(case a.Direction when '借' then SUM(vd.Debit - vd.Credit) when '贷' then SUM(vd.Credit - vd.Debit) end) as Balance " +
"from T_Voucher v, T_Voucher_Detail vd, T_Account a " +
"where v.VId = vd.VId and vd.AccId = a.AccId " +
string.Format("and v.AbId = {0} ", Utility.ParameterNameString("abid")) +
string.Format("and v.[VoucherYear] = {0} and v.VoucherMonth >= {1} and v.VoucherMonth <= {2} ", Utility.ParameterNameString("year"), Utility.ParameterNameString("minmonth"), Utility.ParameterNameString("maxmonth")) +
"group by vd.AccId, vd.AccountCode, vd.AccountName, a.Direction";
string ytdBalance = "select vd.AccId,vd.AccountCode,vd.AccountName,'yearly' as Period,a.Direction," +
"(case a.Direction when '借' then SUM(vd.Debit - vd.Credit) when '贷' then SUM(vd.Credit - vd.Debit) end) as Balance " +
"from T_Voucher v, T_Voucher_Detail vd, T_Account a " +
"where v.VId = vd.VId and vd.AccId = a.AccId " +
string.Format("and v.AbId = {0} ", Utility.ParameterNameString("abid")) +
string.Format("and v.[VoucherYear] = {0} and v.VoucherMonth <= {1} ", Utility.ParameterNameString("year"), Utility.ParameterNameString("maxmonth")) +
"group by vd.AccId, vd.AccountCode, vd.AccountName, a.Direction";
object[] parames = new object[] {
Utility.NewParameter("abid", abid),
Utility.NewParameter("year", condition.StartPeriod.Substring(0, 4)),
Utility.NewParameter("minmonth", condition.StartPeriod.Substring(4)),
Utility.NewParameter("maxmonth", condition.EndPeriod.Substring(4))
};
List <AccountBalanceModel> accBalances = _ledger.Database.SqlQuery <AccountBalanceModel>(initBalance, parames).ToList();
accBalances.AddRange(_ledger.Database.SqlQuery <AccountBalanceModel>(curBalance, parames.Select(p => ((ICloneable)p).Clone()).ToArray()).ToList());
accBalances.AddRange(_ledger.Database.SqlQuery <AccountBalanceModel>(ytdBalance, parames.Select(p => ((ICloneable)p).Clone()).ToArray()).ToList());
var accounts = _ledger.Accounts.Where(a => a.AbId == abid).ToList();
List <AccountBalanceViewModels> abViewModels = new List <AccountBalanceViewModels>();
foreach (AccountBalanceModel ab in accBalances)
{
AccountBalanceViewModels abRow = abViewModels.Where(abvm => abvm.AccountCode == ab.AccountCode).FirstOrDefault();
if (abRow == null)
{
abRow = new AccountBalanceViewModels();
abRow.AccountCode = ab.AccountCode;
abRow.AccountName = ab.AccountName;
abRow.Direction = ab.Direction;
//科目期初余额
decimal initBal = accounts.Where(ai => ai.AccId == ab.AccId).Select(ai => ai.InitialBalance).FirstOrDefault();
if (ab.Direction == "借")
{
abRow.InitDebit = initBal;
}
if (ab.Direction == "贷")
{
abRow.InitCredit = initBal;
}
abViewModels.Add(abRow);
}
switch (ab.Period)
{
//期初余额（加上科目期初）
case "init":
if (ab.Direction == "借")
{
abRow.InitDebit += ab.Balance;
}
if (ab.Direction == "贷")
{
abRow.InitCredit += ab.Balance;
}
break;
//本期发生额
case "cur":
if (ab.Direction == "借")
{
abRow.CurOccurrenceDebit = ab.Balance;
}
if (ab.Direction == "贷")
{
abRow.CurOccurrenceCredit = ab.Balance;
}
break;
//本年累计
case "yearly":
if (ab.Direction == "借")
{
abRow.YtdDebit = ab.Balance;
}
if (ab.Direction == "贷")
{
abRow.YtdCredit = ab.Balance;
}
break;
}
}
//期末余额
foreach (AccountBalanceViewModels abvm in abViewModels)
{
if (abvm.Direction == "借")
{
abvm.DebitBalance = abvm.InitDebit + abvm.CurOccurrenceDebit;
}
if (abvm.Direction == "贷")
{
abvm.CreditBalance = abvm.CreditBalance + abvm.CurOccurrenceCredit;
}
}
return(abViewModels);
}
public List <GeneralLedgerViewModels> GetGeneralLedger(SearchConditionViewModel condition)
{
string initBalance = "select " +
"vd.AccId," +
"min(vd.AccountCode) as AccountCode," +
"min(vd.AccountName) as AccountName," +
string.Format("'{0}' as Period,", condition.StartPeriod) +
"'期初余额' as Abstract," +
"SUM(vd.Debit) as Debit," +
"SUM(vd.Credit) as Credit," +
"min(a.Direction) as Direction," +
"0.00 as Balance,0 as MergeIndex,0 as RowSpan,1 as Sort " +
"from T_Voucher v, T_Voucher_Detail vd, T_Account a " +
"where v.VId = vd.VId and vd.AccId = a.AccId " +
string.Format("and v.AbId = {0} ", Utility.ParameterNameString("abid")) +
string.Format("and v.PaymentTerms < {0} ", Utility.ParameterNameString("startterm")) +
"group by vd.AccId, v.VoucherYear";
string curBalance = "select " +
"vd.AccId," +
"min(vd.AccountCode) as AccountCode," +
"min(vd.AccountName) as AccountName," +
"min(v.PaymentTerms) as Period," +
"'本期合计' as Abstract," +
"SUM(vd.Debit) as Debit," +
"SUM(vd.Credit) as Credit," +
"min(a.Direction) as Direction," +
"0.00 as Balance,0 as MergeIndex,0 as RowSpan,2 as Sort " +
"from T_Voucher v, T_Voucher_Detail vd, T_Account a " +
"where v.VId = vd.VId and vd.AccId = a.AccId " +
string.Format("and v.AbId = {0} ", Utility.ParameterNameString("abid")) +
string.Format("and {0} <= v.PaymentTerms and v.PaymentTerms <= {1} ", Utility.ParameterNameString("startterm"), Utility.ParameterNameString("endterm")) +
"group by vd.AccId, v.VoucherYear, v.VoucherMonth";
string ytdBalance = "select " +
"vd.AccId," +
"min(vd.AccountCode) as AccountCode," +
"min(vd.AccountName) as AccountName," +
"max(v.PaymentTerms) as Period," +
"'本年累计' as Abstract," +
"SUM(vd.Debit) as Debit," +
"SUM(vd.Credit) as Credit," +
"min(a.Direction) as Direction," +
"0.00 as Balance,0 as MergeIndex,0 as RowSpan,3 as Sort " +
"from T_Voucher v, T_Voucher_Detail vd, T_Account a " +
"where v.VId = vd.VId and vd.AccId = a.AccId " +
string.Format("and v.AbId = {0} ", Utility.ParameterNameString("abid")) +
string.Format("and v.PaymentTerms <= {0} ", Utility.ParameterNameString("endterm")) +
"group by vd.AccId, v.VoucherYear";
Guid abid = _cache.GetUserCache().AccountBookID;
object[] parames = new object[] {
Utility.NewParameter("abid", abid),
Utility.NewParameter("startterm", condition.StartPeriod),
Utility.NewParameter("endterm", condition.EndPeriod)
};
List <string> paymentTerms = _ledger.Database.SqlQuery <string>(string.Format("select PaymentTerms from T_Voucher where AbId = {0} and {1} <= PaymentTerms and PaymentTerms <= {2} group by PaymentTerms order by PaymentTerms",
Utility.ParameterNameString("abid"), Utility.ParameterNameString("startterm"), Utility.ParameterNameString("endterm")),
Utility.NewParameter("abid", abid), Utility.NewParameter("startterm", condition.StartPeriod), Utility.NewParameter("endterm", condition.EndPeriod)).ToList();
List <GeneralLedgerViewModels> genLedger = _ledger.Database.SqlQuery <GeneralLedgerViewModels>(initBalance, parames).ToList();
genLedger.AddRange(_ledger.Database.SqlQuery <GeneralLedgerViewModels>(curBalance, Utility.NewParameter("abid", abid), Utility.NewParameter("startterm", condition.StartPeriod), Utility.NewParameter("endterm", condition.EndPeriod)).ToList());
foreach (string pt in paymentTerms)
{
genLedger.AddRange(_ledger.Database.SqlQuery <GeneralLedgerViewModels>(ytdBalance, Utility.NewParameter("abid", abid), Utility.NewParameter("endterm", pt)).ToList());
}
//记录排序
genLedger = genLedger.OrderBy(gl => gl.AccId).ThenBy(gl => gl.Period).ThenBy(gl => gl.Sort).ToList();
//设置合并列
long accid = -1;
for (int i = 0; i < genLedger.Count; i++)
{
if (accid != genLedger[i].AccId)
{
genLedger[i].MergeIndex = i;
genLedger[i].RowSpan = genLedger.Where(gl => gl.AccId == genLedger[i].AccId).Count();
accid = genLedger[i].AccId;
}
}
return(genLedger);
}