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 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;
}
}
}
public ResponseDocAdd DocAdd(RequestDocAdd request)
{
ResponseDocAdd response = new ResponseDocAdd();
using (cnn = GetConnection())
{
BalanceBLL balBLL = new BalanceBLL(cnn);
var ts = cnn.BeginTransaction();
try
{
var user = this.UserInfoGet(request.Token, ts);
#region modify by andy 期间根据凭证日期计算
//AccountSetBLL account = new FAS.AccountSetBLL(cnn);
//var period = account.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
request.Head.Id = Guid.NewGuid().ToString("N");
request.Head.Source = "LR";
Add(request, ts, user);
DealByPZType(request, ts, user);
#region 新增凭证时，对余额表进行操作 Add by Hero.Zhang
balBLL.UpdateBalance(request, ts, user);
#endregion
ts.Commit();
response.Id = request.Head.Id;
response.IsSuccess = true;
response.Message = "新增成功";
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseDocAdd);
}
}
}
public ResponseDocDelete DocDelete(RequestDocDelete request)
{
ResponseDocDelete response = new ResponseDocDelete();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
BalanceBLL balanceBLL = new BalanceBLL(cnn);
var user = this.UserInfoGet(request.Token, ts);
FixedAssetsBLL fixBLL = new FixedAssetsBLL(cnn);
var flag = fixBLL.IsDocHasChangeRecord(request.Data.Id, ts);
if (flag)
{
//取消此判断 update by Hero.Zhang 20180404
//throw new NormalException("该凭证通过固定资产生成，无法删除");
}
string sql = @"select * from tks_fas_doc where id=@Id";
var DocDate = cnn.Query(sql, new { Id = request.Data.Id }, ts);
if (DocDate.Count() <= 0)
{
throw new NormalException("该凭证已经删除");
}
var data = cnn.QueryFirst <TKS_FAS_Doc>(sql, new { Id = request.Data.Id }, ts);
PeriodBLL period = new PeriodBLL(cnn);
flag = period.IsPeriodPaid(data.PeriodId, ts);
if (flag)
{
throw new NormalException("该凭证已经期末结转，无法删除");
}
#region 先回滚余额表 Hero.Zhang
List <TKS_FAS_DocDetail> Detail = cnn.Query <TKS_FAS_DocDetail>("select * from TKS_FAS_DocDetail where parentId=@ParentId", new { ParentId = request.Data.Id }, ts).ToList();
var doc = cnn.QueryFirst <TKS_FAS_Doc>("select * from TKS_FAS_Doc where id=@Id",
new { Id = request.Data.Id }, ts);
RequestDocAdd oldDoc = new RequestDocAdd();
oldDoc.Head = doc;
oldDoc.Detail = Detail;
foreach (var det in oldDoc.Detail)
{
det.Money_Debit = -1 * det.Money_Debit;
det.Money_Credit = -1 * det.Money_Credit;
det.Quantity = -1 * det.Quantity;
}
balanceBLL.UpdateBalance(oldDoc, ts, user);
#endregion
sql = @" delete from TKS_FAS_Doc where id=@Id";
cnn.Execute(sql, request.Data, ts);
sql = "delete from TKS_FAS_DocDetail where parentId=@ParentId";
cnn.Execute(sql, new { ParentId = request.Data.Id }, ts);
//发票状态置为 递交财务
sql = "update tks_fas_invoice set status=1 where pzId=@PZId and AccountId=@AccountId";
cnn.Execute(sql, new { PZId = data.Id, AccountId = data.AccountId }, ts);
sql = "delete from TKS_FAS_TPL2PZ where pzId=@PZID and AccountId=@AccountId ";
cnn.Execute(sql, new { PZID = data.Id, AccountId = data.AccountId }, ts);
// add by Hero.Zhang 20180404
/*判断次凭证号是否在变更记录及生成凭证表TKS_FAS_FixedAssetsChange中，如果存在，则
* 将这笔数据还原成新增并且没有生成凭证的状态；同时将固定资产表TKS_FAS_FixedAssets中这笔固定资产变更为‘未生成凭证状态’,同时将折旧相关数据回冲一个月*/
var check = "select * from TKS_FAS_FixedAssetsChange where docid=@docid and AccountId=@AccountId";
var datacheck = cnn.Query(check, new { docid = data.Id, AccountId = data.AccountId }, ts);
if (datacheck.Count() > 0)
{
var dataUpdate = cnn.QueryFirst <TKS_FAS_FixedAssetsChange>(check, new { docid = data.Id, AccountId = data.AccountId }, ts);
if (dataUpdate != null)
{
sql = "update TKS_FAS_FixedAssetsChange set ChangeType=1,DocId='',DocPZZ='' where id=@id and AccountId=@AccountId";
//cnn.Execute(sql, new { id = request.TKS_FAS_FixedAssetsChange_Id, AccountId = data.AccountId }, ts);
cnn.Execute(sql, new { id = dataUpdate.Id, AccountId = data.AccountId }, ts);
sql = "update TKS_FAS_FixedAssets set IsGenPZ=0 where id=@id and AccountId=@AccountId";
//cnn.Execute(sql, new { id = request.TKS_FAS_FixedAssets_Id, AccountId = data.AccountId }, ts);
cnn.Execute(sql, new { id = dataUpdate.ParentId, AccountId = data.AccountId }, ts);
}
}
//add by Hero.Zhang 20180903 删除计提折旧凭证，回滚固定资产计提折旧数据
if (data.Source == PZType.GD.ToString())
{
//add by Hero.Zhang 删除折旧凭证判断，如果改凭证的日期之后有折旧凭证，则不允许删除
var checkPZ = @"select * from tks_fas_doc where source='GD' and PZDate>@PZDate AND AccountId=@AccountId";
var PZcheck = cnn.Query(checkPZ, new { PZDate = data.PZDate, AccountId = data.AccountId }, ts);
if (PZcheck.Count() > 0)
{
throw new NormalException("该计提折旧凭证无法删除，请先删除下一个期间的计提折旧凭证");
}
//回滚固定资产
fixBLL.RollbackFixedAssets(user.AccountId, data.PeriodId, ts);
}
ts.Commit();
response.IsSuccess = true;
response.Message = "删除成功";
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseDocDelete);
}
}
}
public ResponseDocUpdate DocUpdate(RequestDocUpdate request)
{
ResponseDocUpdate response = new ResponseDocUpdate();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
BalanceBLL balBLL = new BalanceBLL(cnn);
try
{
var user = this.UserInfoGet(request.Token, ts);
var doc = cnn.QueryFirst <TKS_FAS_Doc>("select * from TKS_FAS_Doc where id=@Id",
new { Id = request.Head.Id }, ts);
PeriodBLL period = new PeriodBLL(cnn);
bool flag = period.IsPeriodPaid(doc.PeriodId, ts);
if (flag)
{
throw new NormalException("该凭证已经期末结转，无法修改");
}
flag = period.IsHasPeriod(doc.PeriodId, ts);
if (!flag)
{
throw new NormalException("该凭证期间非激活状态，无法修改");
}
List <TKS_FAS_DocDetail> Detail = cnn.Query <TKS_FAS_DocDetail>("select * from TKS_FAS_DocDetail where parentId=@ParentId", new { ParentId = request.Head.Id }, ts).ToList();
#region 先回滚余额表 Hero.Zhang
RequestDocAdd oldDoc = new RequestDocAdd();
oldDoc.Head = doc;
oldDoc.Detail = Detail;
foreach (var det in oldDoc.Detail)
{
det.Money_Debit = -1 * det.Money_Debit;
det.Money_Credit = -1 * det.Money_Credit;
det.Quantity = -1 * det.Quantity;
}
balBLL.UpdateBalance(oldDoc, ts, user);
#endregion
string sql = @" delete from TKS_FAS_Doc where id=@Id";
cnn.Execute(sql, request.Head, ts);
sql = "delete from TKS_FAS_DocDetail where parentId=@ParentId";
cnn.Execute(sql, new { ParentId = request.Head.Id }, ts);
request.Head.Id = request.Head.Id;
request.Head.Source = "LR";
var id = Add(new RequestDocAdd
{
Token = request.Token,
Head = request.Head,
Detail = request.Detail
}, ts, user);
#region 更新余额表 Hero.Zhang
RequestDocAdd docRequest = new RequestDocAdd();
docRequest.Head = request.Head;
docRequest.Detail = request.Detail;
docRequest.Type = request.Type;
docRequest.TPLId = request.TPLId;
docRequest.Key = request.Key;
balBLL.UpdateBalance(docRequest, ts, user);
#endregion
ts.Commit();
response.IsSuccess = true;
response.Id = id;
response.Message = "更新成功";
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseDocUpdate);
}
}
}
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 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 ResponseFixedAssetsDelete FixedAssetsDelete(RequestFixedAssetsDelete request)
{
ResponseFixedAssetsDelete response = new ResponseFixedAssetsDelete();
using (cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
string sql = "select * from TKS_FAS_FixedAssetsChange where parentId=@ParentId";
var data = cnn.Query <TKS_FAS_FixedAssetsChange>(sql, new { ParentId = request.Data.Id }, ts).ToList();
bool flag = false;
if (data.Count() > 0)
{
PeriodBLL bll = new PeriodBLL(cnn);
foreach (var item in data)
{
flag = bll.IsPeriodPaid(item.PeriodId, ts);
if (flag == true)
{
break;
}
}
}
if (flag)//已经结转 不允许删除凭证
{
throw new NormalException("固定资产生成的凭证已经期末结转，不允许删除");
}
else
{
sql = @" delete from TKS_FAS_FixedAssets where id=@Id";
cnn.Execute(sql, request.Data, ts);
sql = "delete from TKS_FAS_FixedAssetsChange where parentId=@ParentId";
cnn.Execute(sql, new { ParentId = request.Data.Id }, ts);
foreach (var item in data)
{
sql = "delete from TKS_FAS_Doc where id=@Id";
cnn.Execute(sql, new { Id = item.DocId }, ts);
sql = "delete from TKS_FAS_DocDetail where parentId=@ParentId";
cnn.Execute(sql, new { ParentId = item.DocId }, ts);
}
//add by Hero.Zhang删除对应的log
sql = @" delete from TKS_FAS_FixedAssetsLog where FixedId=@FixedId";
cnn.Execute(sql, new { FixedId = request.Data.Id }, ts);
}
ts.Commit();
response.IsSuccess = true;
response.Message = "删除成功";
return(response);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(response, ex) as ResponseFixedAssetsDelete);
}
}
}