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