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