public TKS_FAS_MonthPeriodInfo CetNextPeriod(TKS_FAS_MonthPeriodInfo curPeriod, IDbTransaction ts) { int month = 1; int year = 1997; if (curPeriod.Month == 12) { month = 1; year = curPeriod.Year + 1; } else { month = curPeriod.Month + 1; year = curPeriod.Year; } string sql = @"select * from TKS_FAS_MonthPeriodInfo where accountId=@AccountId and year=@Year and month=@Month"; var data = cnn.QueryFirstOrDefault <TKS_FAS_MonthPeriodInfo> (sql, new { AccountId = curPeriod.AccountId, Year = year, Month = month }, ts); if (data != null) { return(data); } return(null); }
/// <summary> /// 获取计提折旧的固定资产 /// </summary> /// <param name="accountId"></param> /// <param name="period"></param> /// <param name="ts"></param> /// <returns></returns> public List <TKS_FAS_FixedAssets> FixedAssetsGetByAcccount(string accountId, TKS_FAS_MonthPeriodInfo period, IDbTransaction ts) { string sql = @"select parentId from TKS_FAS_FixedAssetsChange where changeType=8 and accountId=@AccountId and periodId=@PeriodId"; //已经处置,但是处置期间是当期的固定资产,还需要计提折旧 var lsChange = cnn.Query <TKS_FAS_FixedAssetsChange>(sql, new { AccountId = accountId, PeriodId = period.Id }, ts).ToList(); string where = string.Empty; if (lsChange.Count > 0) { var con = lsChange.Select(p => p.ParentId).ToList(); where = "or ( id in ('" + string.Join("','", con.ToArray()) + "') )"; } sql = @"select * from tks_fas_fixedAssets where (DepreciationMethod=1 and status=0 and accountId=@AccountId) " + where; var data = cnn.Query <TKS_FAS_FixedAssets>(sql, new { AccountId = accountId }, ts).ToList(); return(data); }
/// <summary> /// 本月客户收款金额 /// </summary> /// <param name="period"></param> /// <param name="ts"></param> /// <returns></returns> List <SubjectMoney> CurMonthKHSK(TKS_FAS_MonthPeriodInfo period, IDbTransaction ts) { List <SubjectMoney> res = new List <SubjectMoney>(); //本月的凭证 string sql = @"select * from tks_fas_doc where periodId=@PeriodId"; var lsDoc = cnn.Query <TKS_FAS_Doc>(sql, new { PeriodId = period.Id }, ts).ToList(); sql = @"SELECT A.ParentId,A.AccountId,A.SubjectCode,sum(A.Money_Credit) as Money_Credit , sum(A.Money_Debit) as Money_Debit,A.CalItem1,A.CalValue1 FROM TKS_FAS_DocDetail A where periodId=@PeriodId group by A.ParentId,A.AccountId,A.SubjectCode,A.CalItem1,A.CalValue1 " ; var lsDocDetail = cnn.Query <TKS_FAS_DocDetailExt>(sql, new { PeriodId = period.Id }, ts).ToList(); sql = "select * from TKS_FAS_CaculateHelperDetail where accountId=@AccountId"; var calHelper = cnn.Query <TKS_FAS_CaculateHelperDetail>(sql, new { AccountId = period.AccountId }, ts).ToList(); Dictionary <string, SubjectMoney> dicRes = new Dictionary <string, SubjectMoney>(); foreach (var doc in lsDoc) { var detail = lsDocDetail.Where(p => p.ParentId == doc.Id).ToList(); var flag1 = detail.Any(p => { return(p.SubjectCode == "1002" && p.Credit_Debit == 0); }); var flag2 = detail.Any(p => { return(p.SubjectCode == "1131" && p.Credit_Debit == 1 && p.CalItem1 != null); }); if (flag1 && flag2) { var d = detail.Where(p => { return(p.SubjectCode == "1131" && p.Credit_Debit == 1); }).ToList(); if (d.Count() == 0) { continue; } foreach (var item in d) { if (dicRes.ContainsKey(item.CalValue1)) { dicRes[item.CalValue1].Money += item.Money_Credit;//累计贷方金额 } else { var h = calHelper.Where(p => p.Code == item.CalValue1).SingleOrDefault(); item.Name = h == null ?"": h.Name; dicRes.Add(item.CalValue1, new SubjectMoney { Subject = "客户收款-" + item.Name, Item = "本月客户收款金额", Credit_Debit = "贷", Money = item.Money_Credit }); } } } } foreach (var item in dicRes) { res.Add(dicRes[item.Key]); } res.Sort(); return(res); }
public ResponsePZZTotalGet PZZTotalGet(RequestPZZTotalGet request) { ResponsePZZTotalGet response = new ResponsePZZTotalGet(); using (cnn = GetConnection()) { var ts = cnn.BeginTransaction(); try { var user = this.UserInfoGet(request.Token, ts); string sql = "select * from TKS_FAS_CertificateWord where accountId=@AccountId order by isdefault desc"; List <TKS_FAS_CertificateWord> data = cnn.Query <TKS_FAS_CertificateWord>(sql, new { AccountId = user.AccountId }, ts).ToList(); AccountSetBLL account = new AccountSetBLL(cnn); PeriodBLL periodBLL = new PeriodBLL(cnn); var activePeriod = account.GetActivePeriod(user.AccountId, ts); sql = @"select top 1 * from TKS_FAS_Doc where accountid=@AccountId and PZDate>=@StartDate order by CreateDate desc "; var doc = cnn.QueryFirstOrDefault <TKS_FAS_Doc>(sql, new { AccountId = user.AccountId, StartDate = activePeriod.StartDate, }, ts); var period = new TKS_FAS_MonthPeriodInfo(); if (request.Type == "GD") { //add by Hero.Zhang 计提折旧日期默认为当前活动期间 period = activePeriod; response.DefaultDate = DateTime.Parse(period.EndDate.ToString()).ToString("yyyy-MM-dd"); } else { if (doc != null) { response.DefaultDate = (doc.PZDate ?? DateTime.Now).ToString("yyyy-MM-dd"); period = periodBLL.GetPeriod(doc.PeriodId, ts); } else { period = activePeriod; if (period.Month == DateTime.Now.Month && period.Year == DateTime.Now.Year) { //当天日期属于本期 response.DefaultDate = DateTime.Parse(DateTime.Now.ToString()).ToString("yyyy-MM-dd"); } else { response.DefaultDate = DateTime.Parse(period.EndDate.ToString()).ToString("yyyy-MM-dd"); } } } sql = @"select (case when max(pzzno) is null then 0 else max(pzzno) end) from TKS_FAS_Doc where accountid=@AccountId and periodId=@PeriodId"; var max = cnn.ExecuteScalar(sql, new { AccountId = user.AccountId, PeriodId = period.Id }, ts); response.No = (int.Parse(max.ToString()) + 1).ToString(); var currentAccount = account.GetAccountSetByAccountId(user.AccountId, ts); ts.Commit(); response.AccountName = currentAccount.QY_Name; response.IsSuccess = true; response.Message = "加载完毕"; response.Data = data; return(response); } catch (Exception ex) { ts.Rollback(); return(this.DealException(response, ex) as ResponsePZZTotalGet); } } }
public TKS_FAS_MonthPeriodInfo CreateNextPeriod(TKS_FAS_MonthPeriodInfo curPeriod, IDbTransaction ts) { BalanceBLL balanceBLL = new BalanceBLL(cnn); int month = 1; int year = 1997; if (curPeriod.Month == 12) { month = 1; year = curPeriod.Year + 1; } else { month = curPeriod.Month + 1; year = curPeriod.Year; } string sql = @"select * from TKS_FAS_MonthPeriodInfo where accountId=@AccountId and year=@Year and month=@Month"; var data = cnn.QueryFirstOrDefault <TKS_FAS_MonthPeriodInfo> (sql, new { AccountId = curPeriod.AccountId, Year = year, Month = month }, ts); if (data != null) { //balanceBLL.BalanceAddup(data, curPeriod, ts); return(data); } else { #region 插入新的期间 sql = @"select * from tks_fas_periodTrack where fromId=@FromId"; var trackData = cnn.QueryFirstOrDefault <TKS_FAS_PeriodTrack>(sql, new { FromId = curPeriod.Id }, ts); TKS_FAS_MonthPeriodInfo newPeriod = new TKS_FAS_MonthPeriodInfo(); if (trackData == null) { newPeriod.Id = Guid.NewGuid().ToString("N"); } else { newPeriod.Id = trackData.ToId; } newPeriod.AccountId = curPeriod.AccountId; newPeriod.IsActive = 0; newPeriod.IsPay = 0; if (curPeriod.Month == 12) { newPeriod.Month = 1; newPeriod.Year = curPeriod.Year + 1; //如果是12月份,则把年度期间也更新为激活,插入新的年度期间 sql = @"update TKS_FAS_YearPeriodInfo set isClose=1,isActive=0 where id=@Id and isClose=0 and isActive=1"; cnn.Execute(sql, new { Id = curPeriod.ParentId }, ts); TKS_FAS_YearPeriodInfo newYearPeriod = new TKS_FAS_YearPeriodInfo(); newYearPeriod.Id = Guid.NewGuid().ToString("N"); newYearPeriod.IsActive = 0; newYearPeriod.IsClose = 0; newYearPeriod.AccountId = curPeriod.AccountId; newYearPeriod.StartDate = DateTime.Parse(newPeriod.Year + "-01-01"); newYearPeriod.EndDate = DateTime.Parse(newPeriod.Year + "-12-31 23:59:59"); newYearPeriod.PeriodNUM = 12; newYearPeriod.Year = newPeriod.Year; cnn.Insert <TKS_FAS_YearPeriodInfo>(newYearPeriod, ts); newPeriod.ParentId = newYearPeriod.Id; } else { newPeriod.ParentId = curPeriod.ParentId; newPeriod.Month = curPeriod.Month + 1; newPeriod.Year = curPeriod.Year; } newPeriod.StartDate = DateTime.Parse(newPeriod.Year + "-" + newPeriod.Month + "-01"); newPeriod.EndDate = newPeriod.StartDate.Value.AddMonths(1).AddSeconds(-1); cnn.Insert <TKS_FAS_MonthPeriodInfo>(newPeriod, ts); if (trackData == null) { //期间历史 TKS_FAS_PeriodTrack track = new TKS_FAS_PeriodTrack(); track.Id = Guid.NewGuid().ToString("N"); track.FromId = curPeriod.Id; track.ToId = newPeriod.Id; track.FromYear = curPeriod.Year.ToString(); track.ToYear = newPeriod.Year.ToString(); track.FromMonth = curPeriod.Month.ToString(); track.ToMonth = newPeriod.Month.ToString(); track.AccountId = curPeriod.AccountId; cnn.Insert <TKS_FAS_PeriodTrack>(track, ts); } #endregion balanceBLL.BalanceAddup(newPeriod, curPeriod, ts); balanceBLL.FBalanceAddup(newPeriod, curPeriod, ts); return(newPeriod); } }
/// <summary> /// 折旧 /// </summary> /// <param name="accountId"></param> /// <param name="ts"></param> public void Depreciation(string accountId, IDbTransaction ts, TKS_FAS_MonthPeriodInfo period) { //update by Hero.Zhang 只计提折旧当前期间和当期之前的固定资产 var lstPeriod = cnn.Query <TKS_FAS_MonthPeriodInfo>(@"select * from TKS_FAS_MonthPeriodInfo where AccountId=@AccountId and StartDate<=@StartDate", new { AccountId = accountId, StartDate = period.StartDate }, ts).ToList(); string sql_where = "("; foreach (var item in lstPeriod) { if (sql_where == "(") { sql_where += "'" + item.Id + "'"; } else { sql_where += ",'" + item.Id + "'"; } } sql_where += ")"; string sql = string.Format(@"select * from tks_fas_fixedAssets where accountId=@AccountId and StartPeriod in {0}", sql_where); var data = cnn.Query <TKS_FAS_FixedAssets>(sql, new { AccountId = accountId }, ts).ToList(); foreach (var item in data) { if (item.DepreciationMethod == "2") { continue; } if (item.IsStartPeriodDepreciation == 0 && item.StartPeriod == period.Id) { continue; } if (item.DpreMonth < item.PreUseMonth) { item.DpreMonth += 1; item.RemainderUseMonth = item.PreUseMonth - item.DpreMonth; item.AccumulativeDpre_Y += item.DprePerMonth; item.AccumulativeDpre += item.DprePerMonth; item.PreviousAccumulativeDpre += item.AccumulativeDpre - item.AccumulativeDpre_Y; sql = @"update TKS_FAS_FixedAssets set DpreMonth=@DpreMonth, RemainderUseMonth=@RemainderUseMonth, AccumulativeDpre_Y=@AccumulativeDpre_Y, AccumulativeDpre=@AccumulativeDpre, PreviousAccumulativeDpre=@PreviousAccumulativeDpre where id=@Id"; cnn.Execute(sql, item, ts); #region log TKS_FAS_FixedAssetsLog log = new TKS_FAS_FixedAssetsLog(); log.Id = Guid.NewGuid().ToString(); log.AccountId = accountId; log.PeriodId = period.Id; log.FixedId = item.Id; log.Amount = item.DprePerMonth; log.CreateDate = DateTime.Now; cnn.Insert <TKS_FAS_FixedAssetsLog>(log, ts); #endregion } } }