public ResponseDocNOGet GetNextDocNO(RequestDocNOGet request)
{
using (cnn = GetConnection())
{
ResponseDocNOGet res = new ResponseDocNOGet();
DateTime docDate = DateTime.Parse(request.DocDate);
PeriodBLL periodBLL = new PeriodBLL(cnn);
var user = this.UserInfoGet(request.Token, null);
AccountSetBLL accountSetBLL = new AccountSetBLL(cnn);
var activePeriod = accountSetBLL.GetActivePeriod(user.AccountId, null);
if ((activePeriod.Year == docDate.Year && activePeriod.Month > docDate.Month) ||
activePeriod.Year > docDate.Year)
{
res.IsSuccess = false;
res.Message = "该期别,不可新增凭证";
return(res);
}
var period = cnn.QueryFirstOrDefault <TKS_FAS_MonthPeriodInfo>("select * from TKS_FAS_MonthPeriodInfo where Year=@Year and AccountId=@AccountId and Month=@Month", new { Year = docDate.Year, Month = docDate.Month, AccountId = user.AccountId }, null);
string no = string.Empty;
if (period == null)
{
no = "1";
}
else
{
string 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 }, null);
no = (int.Parse(max.ToString()) + 1).ToString();
}
res.NO = no;
res.IsSuccess = true;
return(res);
}
}
public ResponseFixedAssetsGet FixedAssetsGet(RequestFixedAssetsGet request)
{
ResponseFixedAssetsGet response = new ResponseFixedAssetsGet();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = UserInfoGet(request.Token, ts);
AccountSetBLL account = new AccountSetBLL(cnn);
var period = account.GetActivePeriod(user.AccountId, ts);
var data = cnn.QuerySingle <TKS_FAS_FixedAssets>("select * from TKS_FAS_FixedAssets where id=@Id", new
{
Id = request.Id
}, ts);
ts.Commit();
response.IsSuccess = true;
response.Message = "加载完毕";
response.Data = data;
response.NowPeriod = period.Id;
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseFixedAssetsGet);
}
}
}
public ResponseFixedAssetsChangeListSearch FixedAssetsChangeListSearch(RequestFixedAssetsChangeListSearch request)
{
ResponseFixedAssetsChangeListSearch response = new ResponseFixedAssetsChangeListSearch();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = this.UserInfoGet(request.Token, ts);
AccountSetBLL account = new AccountSetBLL(cnn);
var period = account.GetActivePeriod(user.AccountId, ts);
var condition = new
{
AccountId = user.AccountId,
Name = "%" + request.Name + "%"
};
string sql = this.GetPageSql(@" A.*, convert(varchar(20),B.Year)+'年'+ convert(varchar(20),B.Month)+'期' as period
,C.tplId,ISCHUZHI=(SELECT 'Y' from TKS_FAS_FixedAssetsChange where ChangeType=8 and A.ParentId=ParentId)",
@" TKS_FAS_FixedAssetsChange A left join tks_fas_monthPeriodInfo B on
A.periodId=B.id left join tks_fas_changeType2TPL C on A.changeType=C.changeType
where A.accountId=@AccountId
and A.assetsname like @Name ",
" createDate desc ",
request.PageSize,
request.PageIndex);
List <TKS_FAS_FixedAssetsChangeExt> data = cnn.Query <TKS_FAS_FixedAssetsChangeExt>(sql,
condition, ts).ToList();
string countSql = @"select count(1) from TKS_FAS_FixedAssetsChange where
accountId=@AccountId and assetsname like @Name";
int total = int.Parse(cnn.ExecuteScalar(countSql, condition, ts).ToString());
ts.Commit();
response.IsSuccess = true;
response.Message = "加载完毕";
response.PageIndex = request.PageIndex;
response.Data = data;
response.Total = total;
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseFixedAssetsChangeListSearch);
}
}
}
/// <summary>
/// 只能查询当前期间下的信息
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public ResponseFixedAssetsListSearch FixedAssetsListSearch(RequestFixedAssetsListSearch request)
{
ResponseFixedAssetsListSearch response = new ResponseFixedAssetsListSearch();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
AccountSetBLL account = new AccountSetBLL(cnn);
var user = this.UserInfoGet(request.Token, ts);
var period = account.GetActivePeriod(user.AccountId, ts);
var PeriodDate = request.PeriodDate.Split('-');
var condition = new
{
AccountId = user.AccountId,
PeriodId = request.PeriodId,
PeriodDate = PeriodDate[0] + PeriodDate[1].PadLeft(2, '0'),
Year = PeriodDate[0],
Month = PeriodDate[1],
Name = "%" + request.Name + "%"
};
string sql = this.GetPageSql("a.*,a.InitialAssetValue-a.AccumulativeDpre as ZCJZ,log.Amount",
@" TKS_FAS_FixedAssets a left join(select distinct FixedId,Amount from TKS_FAS_FixedAssetsLog where Amount>0 and accountId=@AccountId and PeriodId=@PeriodId) log on a.id=log.FixedId where a.accountId=@AccountId and
a.name like @Name and convert(nvarchar(6),a.StartUseDate,112)<=@PeriodDate",
" a.DocNo",
request.PageSize,
request.PageIndex);
List <TKS_FAS_FixedAssetsExt> data = cnn.Query <TKS_FAS_FixedAssetsExt>(sql,
condition, ts).ToList();
string countSql = @"select count(1) from TKS_FAS_FixedAssets where
accountId=@AccountId and name like @Name and convert(nvarchar(6),StartUseDate,112)<=@PeriodDate";
int total = int.Parse(cnn.ExecuteScalar(countSql, condition, ts).ToString());
ts.Commit();
response.IsSuccess = true;
response.Message = "加载完毕";
response.PageIndex = request.PageIndex;
response.Data = data;
response.Total = total;
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseFixedAssetsListSearch);
}
}
}
public void CustomDataImport(DataTable dt, string token, string itemId)
{
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = this.UserInfoGet(token, ts);
AccountSetBLL account = new AccountSetBLL(cnn);
var period = account.GetActivePeriod(user.AccountId, ts);
BalanceBLL balanceBLL = new BalanceBLL(cnn);
PeriodBLL periodBLL = new PeriodBLL(cnn);
string sql = string.Empty;
sql = @"delete from TKS_FAS_CaculateHelperDetail where AccountId=@AccountId and ParentId=@ParentId";
cnn.Execute(sql, new { AccountId = user.AccountId, ParentId = itemId }, ts);
if (dt.DefaultView.ToTable(true, "Name").Rows.Count < dt.Rows.Count)
{
throw new NormalException("名称不能重复");
}
for (var i = 0; i < dt.Rows.Count; i++)
{
var rowItem = dt.Rows[i];
TKS_FAS_CaculateHelperDetail fix = new TKS_FAS_CaculateHelperDetail();
fix.Id = Guid.NewGuid().ToString("N");
fix.AccountId = user.AccountId;
fix.ParentId = itemId;//当前期间为录入期间
fix.IsValid = 1;
fix.Code = (i + 1).ToString().PadLeft(3, '0');
fix.Name = rowItem["Name"].ToString();
fix.Memo = rowItem["Memo"].ToString();
fix.Custom1 = "#nodata#";
fix.Custom2 = "#nodata#";
fix.Custom3 = "#nodata#";
fix.Custom4 = "#nodata#";
fix.Custom5 = "#nodata#";
fix.Custom6 = "#nodata#";
fix.Custom7 = "#nodata#";
fix.Custom8 = "#nodata#";
cnn.Insert <TKS_FAS_CaculateHelperDetail>(fix, ts);
}
ts.Commit();
}
catch (Exception ex)
{
ts.Rollback();
throw ex;
}
}
}
/// <summary>
/// 处置资产
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public ResponseFixedAssetsDeal FixedAssetsDeal(RequestFixedAssetsDeal request)
{
ResponseFixedAssetsDeal response = new ResponseFixedAssetsDeal();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = UserInfoGet(request.Token, ts);
AccountSetBLL bll = new AccountSetBLL(cnn);
var period = bll.GetActivePeriod(user.AccountId, ts);
string sql = "select * from TKS_FAS_FixedAssets where isGenPz=1 and id=@Id";
var data = cnn.Query <TKS_FAS_FixedAssets>(sql, new { Id = request.Data.Id }, ts).FirstOrDefault();
if (data == null)
{
throw new NormalException("未生成凭证，无法报废");
}
TKS_FAS_FixedAssetsChange change = new TKS_FAS_FixedAssetsChange();
change.Id = Guid.NewGuid().ToString("N");
change.ParentId = data.Id;
change.DocNo = data.DocNo;
change.PeriodId = period.Id;
change.AccountId = data.AccountId;
change.AssetsName = data.Name;
change.PreContent = (data.InitialAssetValue - data.AccumulativeDpre).ToString();
change.CurContent = (data.InitialAssetValue - data.AccumulativeDpre).ToString();
change.ChangeType = 8;
change.CreateUser = data.CreateUser;
change.CreateDate = data.CreateDate;
cnn.Insert <TKS_FAS_FixedAssetsChange>(change, ts);
sql = "update TKS_FAS_FixedAssets set status=1 where Id=@Id";
cnn.Execute(sql, new { Id = data.Id }, ts);
ts.Commit();
response.IsSuccess = true;
response.Message = "处置成功";
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseFixedAssetsDeal);
}
}
}
/// <summary>
/// 根据保存的来源类型处理
/// 例如：如果是从模板生成触发，期末检查模板要关联生成的凭证
/// </summary>
/// <param name="request"></param>
/// <param name="ts"></param>
/// <param name="user"></param>
private void DealByPZType(RequestDocAdd request, IDbTransaction ts, MM_UserInfo user)
{
if (request.Type == PZType.QM.ToString())//期末
{
AccountSetBLL account = new AccountSetBLL(cnn);
var period = account.GetActivePeriod(user.AccountId, ts);
TKS_FAS_TPL2PZ newData = new TKS_FAS_TPL2PZ();
newData.Id = Guid.NewGuid().ToString();
newData.TPLId = request.TPLId;
newData.PZId = request.Head.Id;
newData.AccountId = user.AccountId;
newData.CreateUser = user.User.UserName;
newData.CreateDate = DateTime.Now;
newData.PeriodId = period.Id;
cnn.Insert <TKS_FAS_TPL2PZ>(newData, ts);
}
else if (request.Type == PZType.FP.ToString())
{
InvoiceBLL bll = new FAS.InvoiceBLL(cnn);
bll.LZ(request.Key, request.Head.Id, user, ts);
}
else if (request.Type == PZType.CHANGE.ToString())
{
PZZBLL pzz = new PZZBLL(cnn);
var pzzData = pzz.PZZGetById(request.Head.PZZ, ts);
FixedAssetsBLL fixedAssetsBLL = new FixedAssetsBLL(cnn);
fixedAssetsBLL.ChangeSetPZ(request.Head.Id, pzzData.PZZ + request.Head.PZZNO, request.Key, ts);
fixedAssetsBLL.SetIsGenPZ(request.Key, user, ts);
}
else if (request.Type == PZType.GD.ToString())
{
AccountSetBLL account = new AccountSetBLL(cnn);
var period = account.GetActivePeriod(user.AccountId, ts);
TKS_FAS_TPL2PZ newData = new TKS_FAS_TPL2PZ();
newData.Id = Guid.NewGuid().ToString();
newData.TPLId = request.TPLId;
newData.PZId = request.Head.Id;
newData.AccountId = user.AccountId;
newData.CreateUser = user.User.UserName;
newData.CreateDate = DateTime.Now;
newData.PeriodId = period.Id;
cnn.Insert <TKS_FAS_TPL2PZ>(newData, ts);
//更新固定资产
FixedAssetsBLL fixedAssetsBLL = new FixedAssetsBLL(cnn);
fixedAssetsBLL.Depreciation(user.AccountId, ts, period);
}
}
/// <summary>
/// 获取快速凭证模板
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public ResponseKSPZTPLsGet KSPZTPLsGet(RequestKSPZTPLsGet request)
{
var res = new ResponseKSPZTPLsGet();
using (cnn = this.GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = UserInfoGet(request.Token, ts);
AccountSetBLL accountBll = new AccountSetBLL(cnn);
var period = accountBll.GetActivePeriod(user.AccountId, ts);
//获取期末检查模板 是否已经生成凭证
//update by Hero.Zhang 按照原始模板查询
string sql = @"select A.*,0 as IsGenPZ
from TKS_FAS_DocTPL A
where A.tplTarget=1 and A.isCustom=0 and a.AccountId='-' ";
var data = cnn.Query <TKS_FAS_DocTPLExt>(sql, new
{
AccountId = user.AccountId, //账套
PeriodId = period.Id //当前 期间
}, ts).ToList();
res.IsSuccess = true;
res.Data = data;
res.Message = "";
return(res);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(res, ex) as ResponseKSPZTPLsGet);
}
}
}
/// <summary>
/// 新增固定资产，都是在当前的期间下操作
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public ResponseFixedAssetsAdd FixedAssetsAdd(RequestFixedAssetsAdd request)
{
ResponseFixedAssetsAdd response = new ResponseFixedAssetsAdd();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = this.UserInfoGet(request.Token, ts);
AccountSetBLL account = new AccountSetBLL(cnn);
var period = account.GetActivePeriod(user.AccountId, ts);
request.Data.Id = Guid.NewGuid().ToString("N");
request.Data.AccountId = user.AccountId;
request.Data.StartPeriod = period.Id;//当前期间为录入期间
request.Data.CreateUser = user.User.UserName;
request.Data.CreateDate = DateTime.Now;
cnn.Insert <TKS_FAS_FixedAssets>(request.Data, ts);
if (request.Data.AddType != "期初")
{
AddChange(request.Data, ts);
}
ts.Commit();
response.Id = request.Data.Id;
response.IsSuccess = true;
response.Message = "新增成功";
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseFixedAssetsAdd);
}
}
}
private string Add(RequestTPLAdd request, System.Data.IDbTransaction ts, MM_UserInfo user)
{
AccountSetBLL ac = new AccountSetBLL(cnn);
var period = ac.GetActivePeriod(user.AccountId, ts);
decimal total = 0;
var flag = 0;
//凭证上辅助核算项直接写死对应列
foreach (var item in request.Detail)
{
if (string.IsNullOrEmpty(item.SubjectCode))
{
continue;
}
item.Id = Guid.NewGuid().ToString("N");
#region 辅助核算赋值
string[] cals = item.CalValue1.Split(new char[] { '#' });
for (int i = 0; i < cals.Length; i++)
{
if (string.IsNullOrEmpty(cals[i]))
{
continue;
}
string[] ss = cals[i].Split(new char[] { ',' });
if (i == 0)
{
item.CalItem1 = ss[0];
item.CalValue1 = ss[1];
}
else if (i == 1)
{
item.CalItem2 = ss[0];
item.CalValue2 = ss[1];
}
else if (i == 2)
{
item.CalItem3 = ss[0];
item.CalValue3 = ss[1];
}
else if (i == 3)
{
item.CalItem4 = ss[0];
item.CalValue4 = ss[1];
}
else if (i == 4)
{
item.CalItem5 = ss[0];
item.CalValue5 = ss[1];
}
}
#endregion
total += item.Money_Debit;
item.Year = period.Year;
item.ParentId = request.Head.Id;
item.AccountId = user.AccountId;
cnn.Insert <TKS_FAS_DocDetailTPL>(item, ts);
flag++;
}
if (flag == 0)
{
throw new NormalException("亲，您还没有填写明细");
}
request.Head.AccountId = user.AccountId;
request.Head.AMT_DBT = total;
request.Head.IsCarry = 0;
request.Head.TPLTarget = 0; //用户选择
request.Head.IsCustom = 1; //自定义
request.Head.CreateUser = user.User.TrueName;
request.Head.CreateDate = DateTime.Now;
cnn.Insert <TKS_FAS_DocTPL>(request.Head, ts);
return(request.Head.Id);
}
/// <summary>
/// 模板加载
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public ResponseTPLMGet TPLGet(RequestTPLMGet request)
{
ResponseTPLMGet response = new ResponseTPLMGet();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = UserInfoGet(request.Token, ts);
var head = cnn.QuerySingle <TKS_FAS_DocTPL>("select * from TKS_FAS_DocTPL where id=@Id", new
{
Id = request.Data.Id
}, ts);
AccountSetBLL account = new AccountSetBLL(cnn);
var period = account.GetActivePeriod(user.AccountId, ts);
//模板体明细
var detail = cnn.Query <TKS_FAS_DocDetailTPL>(
@"select * from tks_fas_docDetailTPL where parentId=@ParentId",
new { ParentId = head.Id }, ts).ToList();
//如果是固定资产，则需要捞取当前账套的所有固定资产的每月折旧额，生成计提折旧凭证
//需要对detail进行再包装
if (head.TPLTarget == 3)
{
#region 固定资产，计提折旧
FixedAssetsBLL fbll = new FixedAssetsBLL(cnn);
//获取固定资产
List <TKS_FAS_FixedAssets> fixedAssets = fbll.FixedAssetsGetByAcccount(user.AccountId, period, ts);
//凭证明细
List <TKS_FAS_DocDetailTPL> newDetail = new List <TKS_FAS_DocDetailTPL>();
head.AMT_DBT = 0;
foreach (var item in fixedAssets)
{
if (item.DepreciationMethod == "2")
{
continue;
}
if (item.IsStartPeriodDepreciation == 0 && item.StartPeriod == period.Id)
{
continue;
}
if (item.DpreMonth < item.PreUseMonth)
{
//固定资产累计折旧科目，产生到凭证
//用固定资产表单上的科目覆盖模板的科目
for (var i = 0; i < detail.Count; i++)
{
//update by Hero.Zhang 每次循环前必须将借贷金额初始化
detail[i].Money_Credit = 0;
detail[i].Money_Debit = 0;
if (detail[i].Credit_Debit == 1)
{
detail[i].SubjectCode = item.ADSubjectCode;
//detail[i].SubjectDescription = item.ADSubjectCode + " " + item.ADSubjectName;
detail[i].SubjectDescription = item.ADSubjectName;
}
else if (detail[i].Credit_Debit == 0)
{
detail[i].SubjectCode = item.DCostSubjectCode;
detail[i].SubjectDescription = item.DCostSubjectName;
}
}
//填充金额
var d = FillDetail(item.DprePerMonth, detail);
newDetail.AddRange(d);
head.AMT_DBT += item.DprePerMonth;
//if (item.ADSubjectCode == "1502001")
//{
// test.Add(item);
//}
}
}
//分组合并
var lst = newDetail.GroupBy(x => new { x.Id, x.ParentId, x.LineNo, x.AccountId, x.Year, x.SubjectCode, x.SubjectDescription, x.Summary, x.Credit_Debit, x.PartnerCode, x.Unit, x.CurrencyCode, x.CalItem1, x.CalValue1, x.CalItem2, x.CalValue2, x.CalItem3, x.CalValue3, x.CalItem4, x.CalValue4, x.CalItem5, x.CalValue5, x.Rate, x.Quantity, x.Price, x.IsCurrency, x.IsQuantity, x.IsCalHelper, x.Balance, x.YB, x.IPercent, x.SourceType }).Select(x => new TKS_FAS_DocDetailTPL
{
Id = x.First().Id,
ParentId = x.First().ParentId,
LineNo = x.First().LineNo,
AccountId = x.First().AccountId,
Year = x.First().Year,
SubjectCode = x.First().SubjectCode,
SubjectDescription = x.First().SubjectDescription,
Summary = x.First().Summary,
Credit_Debit = x.First().Credit_Debit,
PartnerCode = x.First().PartnerCode,
Unit = x.First().Unit,
CurrencyCode = x.First().CurrencyCode,
CalItem1 = x.First().CalItem1,
CalValue1 = x.First().CalValue1,
CalItem2 = x.First().CalItem2,
CalValue2 = x.First().CalValue2,
CalItem3 = x.First().CalItem3,
CalValue3 = x.First().CalValue3,
CalItem4 = x.First().CalItem4,
CalValue4 = x.First().CalValue4,
CalItem5 = x.First().CalItem5,
CalValue5 = x.First().CalValue5,
Rate = x.First().Rate,
Quantity = x.First().Quantity,
Price = x.First().Price,
IsCurrency = x.First().IsCurrency,
IsQuantity = x.First().IsQuantity,
IsCalHelper = x.First().IsCalHelper,
Balance = x.First().Balance,
YB = x.First().YB,
IPercent = x.First().IPercent,
SourceType = x.First().SourceType,
Money_Credit = x.Sum(s => s.Money_Credit),
Money_Debit = x.Sum(s => s.Money_Debit)
}).ToList();
//detail = newDetail;
detail = lst;
#endregion
}
else if (head.TPLTarget == 7)
{
#region 固定资产变动单类型
var changeTypeToTpl = cnn.QuerySingleOrDefault <TKS_FAS_FixedAssetsChange>(
"select * from TKS_FAS_FixedAssetsChange where Id=@Id ",
new { Id = request.Key }, ts);
if (changeTypeToTpl != null)
{
//固定资产变动类型新增的情况
string fixsql = @"select B.* from TKS_FAS_FixedAssetsChange A
left join TKS_FAS_FixedAssets B on A.parentId=B.id where A.id=@ID";
TKS_FAS_FixedAssets fixedAssets = cnn.QuerySingleOrDefault <TKS_FAS_FixedAssets>(fixsql, new { ID = request.Key }, ts);
if (fixedAssets == null)
{
throw new Exception("固定资产不存在");
}
FillDetail(request.Money, detail);
if (changeTypeToTpl.ChangeType == 1)
{
#region 新增
for (var i = 0; i < detail.Count; i++)
{
if (detail[i].SubjectCode == "1501")
{
detail[i].SubjectCode = fixedAssets.GDCode;
detail[i].SubjectDescription = fixedAssets.GDName;
}
else if (detail[i].SubjectCode == "217100101")
{
if (detail[i].Credit_Debit == 0)
{
detail[i].Money_Debit = fixedAssets.InputVAT;
}
else
{
detail[i].Money_Credit = fixedAssets.InputVAT;
}
}
else if (detail[i].SubjectCode == "1002")
{
if (detail[i].Credit_Debit == 0)
{
detail[i].Money_Debit = request.Money + fixedAssets.InputVAT;
}
else
{
detail[i].Money_Credit = request.Money + fixedAssets.InputVAT;
}
}
}
request.Money += fixedAssets.InputVAT;
#endregion
}
else if (changeTypeToTpl.ChangeType == 8)
{
#region 报废
for (var i = 0; i < detail.Count; i++)
{
if (detail[i].SubjectCode == "1501")
{
if (detail[i].Credit_Debit == 0)
{
detail[i].Money_Debit = fixedAssets.InitialAssetValue;
}
else
{
detail[i].Money_Credit = fixedAssets.InitialAssetValue;
}
}
else if (detail[i].SubjectCode == "1502")
{
if (detail[i].Credit_Debit == 0)
{
detail[i].Money_Debit = fixedAssets.AccumulativeDpre + fixedAssets.DprePerMonth;//累计折旧+每月折旧额
}
else
{
detail[i].Money_Credit = fixedAssets.AccumulativeDpre + fixedAssets.DprePerMonth;
}
}
else if (detail[i].SubjectCode == "1701")
{
if (detail[i].Credit_Debit == 0)
{
detail[i].Money_Debit = fixedAssets.InitialAssetValue - fixedAssets.AccumulativeDpre - fixedAssets.DprePerMonth;//减去累计折旧和每月折旧额
}
else
{
detail[i].Money_Credit = fixedAssets.InitialAssetValue - fixedAssets.AccumulativeDpre - fixedAssets.DprePerMonth;
}
}
}
request.Money = fixedAssets.InitialAssetValue;
#endregion
}
}
else
{
FillDetail(request.Money, detail);
}
#endregion
}
else if (head.TPLTarget == 2)
{
#region 发票上传
//var fp = cnn.Query<TKS_FAS_Invoice>(
// "select * from TKS_FAS_Invoice where id=@Id", new { Id =request.Data.Id}, ts);
//if (fp == null)
//{
// throw new NormalException("未找到对应发票");
//}
FillDetail(request.Money, detail);
decimal total = 0;
for (var i = 0; i < detail.Count; i++)
{
if (detail[i].SourceType == 0)
{
//发生额比例，不做处理
}
else if (detail[i].SourceType == 1)
{
//含税金额
if (detail[i].Credit_Debit == 0)
{
detail[i].Money_Debit = request.Money;
}
else
{
detail[i].Money_Credit = request.Money;
}
}
else if (detail[i].SourceType == 2)
{
//税金
if (detail[i].Credit_Debit == 0)
{
detail[i].Money_Debit = request.Tax;
}
else
{
detail[i].Money_Credit = request.Tax;
}
}
else if (detail[i].SourceType == 3)
{
//未税金额
if (detail[i].Credit_Debit == 0)
{
detail[i].Money_Debit = request.Money - request.Tax;
}
else
{
detail[i].Money_Credit = request.Money - request.Tax;
}
}
total += detail[i].Money_Debit;
}
request.Money = total;
#endregion
}
else
{
FillDetail(request.Money, detail);
}
if (request.Money > 0)
{
head.AMT_DBT = request.Money;
}
ts.Commit();
response.IsSuccess = true;
response.Message = "加载完毕";
response.Head = head;
response.Detail = detail;
#region add by Hero.Zhang 判断科目行是否有子科目
for (int i = 0; i < detail.Count(); i++)
{
var item = detail[i];
List <TKS_FAS_AccountSubject> sub = cnn.Query <TKS_FAS_AccountSubject>(@"select * from TKS_FAS_AccountSubject where AccountId=@AccountId and RootCode=@RootCode",
new { AccountId = user.AccountId, RootCode = item.SubjectCode }, ts).ToList();
if (sub.Count > 1)
{
response.CheckParent = "1";
response.Message += "</br>第" + (i + 1).ToString() + "行有子科目，请重新选择";
}
}
#endregion
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseTPLMGet);
}
}
}
private string Add(RequestDocAdd request, System.Data.IDbTransaction ts, MM_UserInfo user)
{
AccountSetBLL ac = new AccountSetBLL(cnn);
#region modify by andy 期间根据凭证日期计算
//var period = ac.GetActivePeriod(user.AccountId, ts);
var pzDate = request.Head.PZDate;
PeriodBLL balanceBLL = new PeriodBLL(cnn);
var period = balanceBLL.GetPeriod(user.AccountId, pzDate?.Year ?? 0, pzDate?.Month ?? 0, ts);
#endregion
var d = cnn.Query(@"select * from TKS_FAS_Doc where accountId=@AccountId
and periodId=@PeriodId and pzz=@PZZ and pzzno=@PZZNO",
new { AccountId = user.AccountId, PeriodId = period.Id, PZZ = request.Head.PZZ, PZZNO = request.Head.PZZNO }, ts);
if (d.Count() > 0)
{
throw new NormalException("当前账套，会计期间下，凭证字号已经存在");
}
if (request.Head.PZDate.HasValue)
{
if (period.StartDate > request.Head.PZDate || period.EndDate < request.Head.PZDate)
{
//可以录入当前期间之后期间的凭证 update by Hero.Zhang 20180507
//throw new NormalException("凭证日期必须在当前会计期间");
}
}
else
{
throw new NormalException("凭证日期必须选择");
}
var accountSet = ac.GetAccountSetByAccountId(user.AccountId, ts);
if (accountSet.IsNeedReviewed == 1)
{
request.Head.CheckStatus = 1;
}
else
{
request.Head.CheckStatus = 3;
}
decimal debit = 0;
decimal credit = 0;
decimal total = 0;
var flag = 0;
string strMessage = "";
List <TKS_FAS_AccountSubject> sub = cnn.Query <TKS_FAS_AccountSubject>(@"select * from TKS_FAS_AccountSubject where AccountId=@AccountId",
new { AccountId = user.AccountId }, ts).ToList();
//凭证上辅助核算项直接写死对应列
foreach (var item in request.Detail)
{
if (string.IsNullOrEmpty(item.SubjectCode))
{
continue;
}
item.Id = Guid.NewGuid().ToString("N");
#region 辅助核算赋值
string[] cals = item.CalValue1.Split(new char[] { '#' });
for (int i = 0; i < cals.Length; i++)
{
if (string.IsNullOrEmpty(cals[i]))
{
continue;
}
string[] ss = cals[i].Split(new char[] { ',' });
if (i == 0)
{
item.CalItem1 = ss[0];
item.CalValue1 = ss[1];
}
else if (i == 1)
{
item.CalItem2 = ss[0];
item.CalValue2 = ss[1];
}
else if (i == 2)
{
item.CalItem3 = ss[0];
item.CalValue3 = ss[1];
}
else if (i == 3)
{
item.CalItem4 = ss[0];
item.CalValue4 = ss[1];
}
else if (i == 4)
{
item.CalItem5 = ss[0];
item.CalValue5 = ss[1];
}
}
#endregion
total += item.Money_Debit;
//录入在借方，则凭证借贷方向是借，否则是贷
if (item.Money_Debit != 0)
{
item.Credit_Debit = 0;
}
else
{
item.Credit_Debit = 1;
}
if (string.IsNullOrEmpty(item.CurrencyCode))
{
item.CurrencyCode = "RMB";//默认RMB
}
item.SubjectDescription = item.SubjectDescription.Trim();
item.PeriodId = period.Id;
item.Year = period.Year;
item.Source = request.Head.Source;
item.ParentId = request.Head.Id;
item.AccountId = user.AccountId;
//add by Hero.Zhang 添加期初，期末值 2018-12-18
// #region add by Hero.Zhang 添加期初，期末值 2018-12-18
// item.FirstMoney = 0;
// item.EndBAL = 0;
// var det = cnn.Query<TKS_FAS_DocDetail>(@"
// select top 1 det.* from TKS_FAS_DocDetail det
// left join TKS_FAS_Doc doc on det.ParentId=doc.Id
// where det.AccountId=@AccountId
// and det.PeriodId=@PeriodId
// and det.SubjectCode=@SubjectCode
// order by doc.CreateDate desc",
// new { AccountId = user.AccountId, PeriodId = period.Id, SubjectCode = item.SubjectCode}, ts).ToList();
// if (det.Count>0)
// {
// //此科目在该期间上一次的期末值为这一次的期初值
// item.FirstMoney = det[0].EndBAL;
// if (item.Credit_Debit==det[0].Credit_Debit)
// {
// item.EndBAL = item.FirstMoney + item.Money_Debit + item.Money_Credit;
// }
// else
// {
// item.EndBAL = Math.Abs(item.FirstMoney - (item.Money_Debit + item.Money_Credit));
// }
// }
// else
// {
// //第一次存入，从期初余额中取值
// var bal = cnn.Query<TKS_FAS_GLBalance>(@"
// select top 1 det.* from TKS_FAS_GLBalance det
// where det.AccountId=@AccountId
// and det.PeriodId=''
// and det.SubjectCode=@SubjectCode
// ",
//new { AccountId = user.AccountId, SubjectCode = item.SubjectCode }, ts).ToList();
// if (bal.Count>0)
// {
// item.FirstMoney = bal[0].BWBStartBAL;
// if (item.Credit_Debit == bal[0].SCredit_Debit)
// {
// item.EndBAL = item.FirstMoney + item.Money_Debit + item.Money_Credit;
// }
// else
// {
// item.EndBAL = Math.Abs(item.FirstMoney - (item.Money_Debit + item.Money_Credit));
// }
// }
// }
// #endregion
var info = sub.Where(p => p.Code == item.SubjectCode).ToList();
if (info.Count() == 0)
{
throw new NormalException(item.SubjectCode + "科目不存在");
}
if (info[0].IsCalHelperValid == 1 && string.IsNullOrEmpty(item.CalValue1))
{
throw new NormalException(item.SubjectCode + "科目中有辅助核算，请选择辅助核算");
}
List <TKS_FAS_AccountSubject> checkSub = sub.FindAll(p => p.ParentId == info[0].Id).ToList();
if (checkSub.Count() > 0)
{
strMessage += "</br>第" + (flag + 1).ToString() + "行有子科目，请重新选择";
}
cnn.Insert <TKS_FAS_DocDetail>(item, ts);
flag++;
}
if (flag == 0)
{
throw new NormalException("亲，您还没有填写明细");
}
if (flag == 1)
{
throw new NormalException("亲，明细不能只存在一条");
}
if (strMessage != "")
{
throw new NormalException(strMessage);
}
request.Head.PeriodId = period.Id;
request.Head.Year = period.Year;
request.Head.AccountId = user.AccountId;
request.Head.AMT_DBT = total;
request.Head.CreateUser = user.User.TrueName;
request.Head.CreateDate = DateTime.Now;
//add by Hero.Zhang 存入来源 20180903
request.Head.Source = request.Type;
if (string.IsNullOrEmpty(request.Type))
{
request.Head.Source = "LR";
}
cnn.Insert <TKS_FAS_Doc>(request.Head, ts);
return(request.Head.Id);
}
public ResponseDocListSearch DocListSearch(RequestDocListSearch request)
{
ResponseDocListSearch response = new ResponseDocListSearch();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = this.UserInfoGet(request.Token, ts);
string where = string.Empty;
object predicate;
object accountList;
if (request.Type == "normal")
{
#region 一般查询
if (!string.IsNullOrEmpty(request.PZZ))
{
where = " and A.pzz=@PZZ";
}
if (!string.IsNullOrEmpty(request.PZZ_S))
{
where += " and pzzNo>=" + int.Parse(request.PZZ_S);
}
if (!string.IsNullOrEmpty(request.PZZ_E))
{
where += " and pzzNo<=" + int.Parse(request.PZZ_E);
}
if (!string.IsNullOrEmpty(request.Period) && request.Period != "##")
{
where += " and A.periodId='" + request.Period + "'";
}
predicate = new
{
AccountId = user.AccountId,
PZZ = request.PZZ
};
#endregion
}
else
{
#region 更多查询
if (!string.IsNullOrEmpty(request.More.PZZ))
{
where = " and A.pzz=@PZZ";
}
if (!string.IsNullOrEmpty(request.More.PZZ_S))
{
where += " and pzzNo>=" + int.Parse(request.More.PZZ_S);
}
if (!string.IsNullOrEmpty(request.More.PZZ_E))
{
where += " and pzzNo<=" + int.Parse(request.More.PZZ_E);
}
if (!string.IsNullOrEmpty(request.More.Period_S))
{
var periodS = cnn.QueryFirstOrDefault <TKS_FAS_MonthPeriodInfo>(
@"select * from TKS_FAS_MonthPeriodInfo where id=@Id",
new { Id = request.More.Period_S }, ts);
where += " and A.pzdate>='" + DateTime.Parse(periodS.StartDate.ToString()).ToString("yyyy-MM-dd") + "'";
}
if (!string.IsNullOrEmpty(request.More.Period_E))
{
var periodE = cnn.QueryFirstOrDefault <TKS_FAS_MonthPeriodInfo>(
@"select * from TKS_FAS_MonthPeriodInfo where id=@Id",
new { Id = request.More.Period_E }, ts);
where += " and A.pzdate<='" + DateTime.Parse(periodE.EndDate.ToString()).ToString("yyyy-MM-dd") + "'";
}
if (!string.IsNullOrEmpty(request.AccountList))
{
where += " and A.accountId in" + request.AccountList;
}
predicate = new
{
AccountId = user.AccountId,
PZZ = request.More.PZZ
};
#endregion
}
string sql = this.GetPageSql("A.*,B.pzz as PZZName," +
"AttachmentCount=(select count(att.Id) from TKS_FAS_Attachment att where att.DocId=A.Id ) ",
@" TKS_FAS_Doc A left join TKS_FAS_CertificateWord B on A.pzz=B.ID
where A.accountId=@AccountId " + where,
" A.PZDate,A.PeriodId,B.PZZ,A.PZZNO",
request.PageSize,
request.PageIndex);
List <TKS_FAS_DocExt> data = cnn.Query <TKS_FAS_DocExt>(sql, predicate, ts).ToList();
if (!string.IsNullOrEmpty(request.AccountList))
{
accountList = new
{
AccountList = request.AccountList,
PZZ = request.More.PZZ
};
//打印工具专用
sql = this.GetPageSql(" row_number() OVER ( ORDER BY A.PZZNO ASC ) AS RowNum,A.*,B.pzz as PZZName," +
"AttachmentCount=(select count(att.Id) from TKS_FAS_Attachment att where att.DocId=A.Id ) ",
@" TKS_FAS_Doc A left join TKS_FAS_CertificateWord B on A.pzz=B.ID
where 1=1 " + where,
" A.pzDate desc,A.pzzNo ",
request.PageSize,
request.PageIndex);
data = cnn.Query <TKS_FAS_DocExt>(sql, accountList, ts).ToList();
}
string countSql = @"select count(1) from TKS_FAS_Doc A
where A.accountId=@AccountId " + where;
int total = int.Parse(cnn.ExecuteScalar(countSql, predicate, ts).ToString());
List <string> docIds = data.Select(p => p.Id).ToList();
string ids = "('" + string.Join("','", docIds) + "')";
sql = @"select * from TKS_FAS_DocDetail where parentId in " + ids;
var detail = cnn.Query <TKS_FAS_DocDetail>(sql, null, ts).ToList();
var result = (from item in data
select new DocList
{
Head = item,
Detail = detail.Where(p => p.ParentId == item.Id).OrderBy(p => p.Seq).ToList()
}).ToList();
AccountSetBLL account = new AccountSetBLL(cnn);
var currentAccount = account.GetAccountSetByAccountId(user.AccountId, ts);
ts.Commit();
response.IsSuccess = true;
response.Message = "加载完毕";
response.PageIndex = request.PageIndex;
response.Data = result;
response.Total = total;
response.AccountName = currentAccount.QY_Name;
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseDocListSearch);
}
}
}
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);
}
}
}
/// <summary>
/// 折旧数据回滚
/// </summary>
/// <param name="accountId"></param>
/// <param name="ts"></param>
public void RollbackFixedAssets(string accountId, string PeriodId, IDbTransaction ts)
{
AccountSetBLL account = new AccountSetBLL(cnn);
var period = cnn.QueryFirstOrDefault <TKS_FAS_MonthPeriodInfo>(
@"select * from TKS_FAS_MonthPeriodInfo where Id=@Id",
new { Id = PeriodId }, ts);
//var period = account.GetActivePeriod(accountId, ts);
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;
item.DpreMonth--;
item.PreviousAccumulativeDpre -= item.AccumulativeDpre - item.AccumulativeDpre_Y;
item.RemainderUseMonth = item.PreUseMonth - item.DpreMonth;
item.AccumulativeDpre_Y -= item.DprePerMonth;
item.AccumulativeDpre -= item.DprePerMonth;
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
sql = @"update TKS_FAS_FixedAssetsLog set Amount=0
where
AccountId=@AccountId and FixedId=@FixedId and PeriodId=@PeriodId";
cnn.Execute(sql, new { AccountId = accountId, FixedId = item.Id, PeriodId = period.Id }, ts);
#endregion
}
}
}
public ResponseFixedAssetsUpdate FixedAssetsUpdate(RequestFixedAssetsUpdate request)
{
ResponseFixedAssetsUpdate response = new ResponseFixedAssetsUpdate();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = this.UserInfoGet(request.Token, ts);
//更新固定资产前，先检查是否存在变更记录，若存在，先更新变更记录
var check = "select * from TKS_FAS_FixedAssetsChange where ParentId=@id";
var dataUpdate = cnn.Query(check, new { id = request.Data.Id }, ts);
if (dataUpdate.Count() > 0)
{
//add by Hero.Zhang如果购入类型是期初，则删除变更记录
if (request.Data.AddType == "期初")
{
cnn.Execute(@"delete from TKS_FAS_FixedAssetsChange where ParentId=@id", new { id = request.Data.Id }, ts);
}
else
{
//获取更新前固定资产
var data = cnn.QueryFirst <TKS_FAS_FixedAssets>("select * from TKS_FAS_FixedAssets where id=@Id", new { Id = request.Data.Id }, ts);
if (data != null)
{
string updateSql = @"update TKS_FAS_FixedAssetsChange
set DocNo = @DocNo,
AssetsName = @AssetsName,
PreContent = @PreContent,
CurContent = @CurContent where ParentId=@Id";
cnn.Execute(updateSql, new { DocNo = data.DocNo, AssetsName = data.Name, PreContent = request.Data.InitialAssetValue, CurContent = request.Data.InitialAssetValue, Id = request.Data.Id }, ts);
}
}
}
else
{
//add by Hero.Zhang
//如果不存在变更记录，判断是否是期初购入类型，不是期初则新增变更记录
if (request.Data.AddType != "期初")
{
AccountSetBLL account = new AccountSetBLL(cnn);
var period = account.GetActivePeriod(user.AccountId, ts);
request.Data.AccountId = user.AccountId;
request.Data.StartPeriod = period.Id;//当前期间为录入期间
request.Data.CreateUser = user.User.UserName;
request.Data.CreateDate = DateTime.Now;
AddChange(request.Data, ts);
}
}
string sql = @"UPDATE TKS_FAS_FixedAssets
SET
DocNo = @DocNo
,Name = @Name
,AddType = @AddType
,AssetsClass = @AssetsClass
,SpecificationType = @SpecificationType
,StartUseDate = @StartUseDate
,UseDeptId = @UseDeptId
,UseDeptName = @UseDeptName
,Supplier = @Supplier
,DepreciationMethod = @DepreciationMethod
,IsStartPeriodDepreciation = @IsStartPeriodDepreciation
,ADSubjectCode = @ADSubjectCode
,ADSubjectName = @ADSubjectName
,DCostSubjectCode = @DCostSubjectCode
,DCostSubjectName = @DCostSubjectName
,AssetImpairmentSubjectCode = @AssetImpairmentSubjectCode
,AssetImpairmentSubjectName = @AssetImpairmentSubjectName
,InitialAssetValue = @InitialAssetValue
,ScrapValueRate = @ScrapValueRate
,ScrapValue = @ScrapValue
,PreUseMonth = @PreUseMonth
,DpreMonth = @DpreMonth
,RemainderUseMonth = @RemainderUseMonth
,AccumulativeDpre = @AccumulativeDpre
,AccumulativeDpre_Y = @AccumulativeDpre_Y
,PreviousAccumulativeDpre = @PreviousAccumulativeDpre
,DprePerMonth = @DprePerMonth
,GDCode=@GDCode
,GDName=@GDName
,InputVAT=@InputVAT
,Memo = @Memo
WHERE
Id=@Id";
cnn.Execute(sql, request.Data, ts);
ts.Commit();
response.IsSuccess = true;
response.Message = "更新成功";
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseFixedAssetsUpdate);
}
}
}