public void Delete(long id)
{
dynamic tran = DBHelper.GetInstance(mContext).BeginTransaction();
try
{
//已审核不能删除
var bExist = DBHelper.GetInstance(mContext).Exist(tran, "select * from _VoucherHeader where _status > 0 and _id = " + id);
if (bExist)
{
throw new FinanceException(FinanceResult.INCORRECT_STATE);
}
DBHelper.GetInstance(mContext).ExecuteSql(tran, "delete from _VoucherEntryUdef where _id = " + id);
DBHelper.GetInstance(mContext).ExecuteSql(tran, "delete from _VoucherEntry where _id= " + id);
DBHelper.GetInstance(mContext).ExecuteSql(tran, "delete from _VoucherHeader where _id= " + id);
DBHelper.GetInstance(mContext).CommitTransaction(tran);
}
catch (FinanceException ex)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
throw ex;
}
catch (Exception e)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
var traceId = SerialNoService.GetUUID();
logger.Error(e, traceId);
throw new FinanceException(FinanceResult.SYSTEM_ERROR, traceId);
}
}
public void Settle()
{
SystemProfileService system = new SystemProfileService(mContext);
var year = system.GetInt(SystemProfileCategory.Account, SystemProfileKey.CurrentYear);
var period = system.GetInt(SystemProfileCategory.Account, SystemProfileKey.CurrentPeriod);
dynamic tran = DBHelper.GetInstance(mContext).BeginTransaction();
try
{
//1、获取id
DataTable dt = DBHelper.GetInstance(mContext).ExecuteDt(string.Format("select _id from _VoucherHeader where _year = {0} and _period = {1}", year, period));
var lstIds = dt.AsEnumerable().Select(p => p.Field <long>("_id"));
if (lstIds.Count() > 0)
{
var ids = string.Join(",", lstIds);
//2、检查是否有未审核、未过账的，不能结账
var bExist = DBHelper.GetInstance(mContext).Exist(tran,
string.Format("select 1 from _VoucherHeader where _id in ({0}) and _status < {1}", ids, (int)VoucherStatus.Posted));
if (bExist)
{
throw new FinanceException(FinanceResult.INCORRECT_STATE);
}
//3、把结账到余额表
string.Format(@"
insert into _AccountBalance(_year,_period,_accountSubjectId,_debitsAmount,_creditAmount)
select {1},{2},_accountSubjectId,SUM(_debitsAmount) as _debitsAmount,SUM(_creditAmout) _creditAmout from (
select _accountSubjectId,_amount as _debitsAmount,0 as _creditAmout from _VoucherEntry where _id in ({0}) and _direction = 1
union
select _accountSubjectId,0 as _debitsAmount,_amount as _creditAmout from _VoucherEntry where _id in ({0}) and _direction = -1
) t group by _accountSubjectId
", ids, year, period);
//4、更新状态
DBHelper.GetInstance(mContext).ExecuteSql(tran,
string.Format("update _VoucherHeader set _status = {0} where _id in ({1})", (int)VoucherStatus.Settled, ids));
}
//5、更新Current Period 为新的
var next = CommonUtils.CalcNextPeriod(new PeridStrunct {
Year = year, Period = period
});
system.Update(tran, SystemProfileCategory.Account, SystemProfileKey.CurrentYear, next.Year.ToString());
system.Update(tran, SystemProfileCategory.Account, SystemProfileKey.CurrentPeriod, next.Period.ToString());
DBHelper.GetInstance(mContext).CommitTransaction(tran);
}
catch (FinanceException ex)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
throw ex;
}
catch (Exception e)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
var traceId = SerialNoService.GetUUID();
logger.Error(e, traceId);
throw new FinanceException(FinanceResult.SYSTEM_ERROR, traceId);
}
}
/// <summary>
/// 改变凭证状态
/// </summary>
/// <param name="srcStatus">原来的状态，如果不符合报错1007，如果为Invalid.就不检查</param>
/// <param name="destStatus"></param>
/// <param name="act">在更改状态 同事务执行</param>
void ChangeStatus(long id, VoucherStatus srcStatus, VoucherStatus destStatus, Action <dynamic> act)
{
dynamic tran = DBHelper.GetInstance(mContext).BeginTransaction();
try
{
if (srcStatus != VoucherStatus.Invalid)
{
var obj = DBHelper.GetInstance(mContext).ExecuteScalar(tran, string.Format("select _status from _VoucherHeader where _id = {0}", id));
if ((int)obj != (int)srcStatus)
{
throw new FinanceException(FinanceResult.INCORRECT_STATE);
}
}
DBHelper.GetInstance(mContext).ExecuteSql(tran, string.Format("update _VoucherHeader set _status={0} where _id={1} ", (int)destStatus, id));
act?.Invoke(tran);
DBHelper.GetInstance(mContext).CommitTransaction(tran);
}
catch (FinanceException ex)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
throw ex;
}
catch (Exception e)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
var traceId = SerialNoService.GetUUID();
logger.Error(e, traceId);
throw new FinanceException(FinanceResult.SYSTEM_ERROR, traceId);
}
}
public string ExecTask(ExecTaskType taskType, string procName, Dictionary <string, object> filter)
{
var taskId = SerialNoService.GetUUID();
try
{
switch (taskType)
{
case ExecTaskType.CreateVoucher:
ExecCreateVoucher(taskId, procName, filter);
break;
case ExecTaskType.CarriedForward:
ExecCarriedForward(taskId, procName, filter);
break;
}
}
catch (Exception ex)
{
logger.Error(ex.ToString());
RefreshTaskResult(taskId, ExecTaskType.CreateVoucher.ToString(), -1, "", ex.Message);
//throw new FinanceException(FinanceResult.SYSTEM_ERROR);
}
return(taskId);
}
public long AddUser(string userName, string password)
{
long userId = 0;
var tran = DBHelper.GetInstance(mContext).BeginTransaction();
try
{
var bExist = DBHelper.GetInstance(mContext).Exist(tran, string.Format("select 1 from _userInfo where _userName ='******' ", userName));
if (bExist)
{
throw new FinanceException(FinanceResult.RECORD_EXIST);
}
SerialNoService serialNoService = new SerialNoService(mContext, tran);
serialNoService.SerialKey = SerialNoKey.System;
userId = serialNoService.First();
DBHelper.GetInstance(mContext).ExecuteSql(
string.Format("insert into _userInfo (_userId,_userName,_passWord) values ({0},'{1}','{2}')", userId, userName, password));
serialNoService.Update();
UpdateTimeStampArticle(TimeStampArticleEnum.UserList, tran);
DBHelper.GetInstance(mContext).CommitTransaction(tran);
}
catch (Exception ex)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
throw ex;
}
return(userId);
}
public void Save(List <BeginBalance> balances)
{
var totalDebitsAmount = balances.Sum(b => b.debitsAmount);
var totalCreditAmount = balances.Sum(b => b.creditAmount);
if (totalDebitsAmount != totalCreditAmount)
{
throw new FinanceException(FinanceResult.AMMOUNT_IMBALANCE);
}
dynamic tran = DBHelper.GetInstance(mContext).BeginTransaction();
try
{
DataTable dt = EntityConvertor <BeginBalance> .ToDataTable(balances);
DBHelper.GetInstance(mContext).ExecuteSql(tran, "delete from _BeginBalance");
DBHelper.GetInstance(mContext).InsertTable(tran, dt, "_BeginBalance");
DBHelper.GetInstance(mContext).CommitTransaction(tran);
}
catch (FinanceException ex)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
throw ex;
}
catch (Exception e)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
var traceId = SerialNoService.GetUUID();
logger.Error(e, traceId);
throw new FinanceException(FinanceResult.SYSTEM_ERROR, traceId);
}
}
public long Add(Voucher item)
{
CheckData(item);
dynamic tran = DBHelper.GetInstance(mContext).BeginTransaction();
try
{
GeneratorIds(item.header, tran);
item.entries.ForEach(v => {
v.id = item.header.id;
if (v.accountSubjectId == 0 || !string.IsNullOrEmpty(v.accountSubjectNo))
{
var aso = AccountSubjectService.GetInstance(mContext).FindByNo(v.accountSubjectNo);
if (aso == null)
{
throw new FinanceException(FinanceResult.RECORD_NOT_EXIST, string.Format("科目[{0}]不存在", v.accountSubjectNo));
}
v.accountSubjectId = aso.id;
if (aso.flag != 0 && item.udefenties != null && !item.udefenties.ContainsKey(v.uniqueKey))
{
throw new FinanceException(FinanceResult.IMPERFECT_DATA, string.Format("科目[{0}]必须要有自定义扩展信息", v.accountSubjectNo));
}
}
});
DBHelper.GetInstance(mContext).InsertTable(tran,
EntityConvertor <VoucherHeader> .ToDataTable(new List <VoucherHeader> {
item.header
}), "_VoucherHeader");
DBHelper.GetInstance(mContext).InsertTable(tran,
EntityConvertor <VoucherEntry> .ToDataTable(item.entries), "_VoucherEntry");
saveUdefEntry(item, tran);
DBHelper.GetInstance(mContext).CommitTransaction(tran);
return(item.header.id);
}
catch (FinanceException ex)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
throw ex;
}
catch (Exception e)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
var traceId = SerialNoService.GetUUID();
logger.Error(e, traceId);
throw new FinanceException(FinanceResult.SYSTEM_ERROR, traceId);
}
}
public long Update(Voucher item)
{
var id = item.header.id;
var bExist = DBHelper.GetInstance(mContext).Exist("select * from _VoucherHeader where _id = " + id);
if (!bExist)
{
throw new FinanceException(FinanceResult.RECORD_NOT_EXIST);
}
item.entries.ForEach(entry => entry.id = id);
CheckData(item);
dynamic tran = DBHelper.GetInstance(mContext).BeginTransaction();
try
{
//已审核不能更新
bExist = DBHelper.GetInstance(mContext).Exist(tran, "select * from _VoucherHeader where _status > 0 and _id = " + id);
if (bExist)
{
throw new FinanceException(FinanceResult.INCORRECT_STATE);
}
DBHelper.GetInstance(mContext).ExecuteSql(tran, "delete from _VoucherEntry where _id= " + id);
DBHelper.GetInstance(mContext).ExecuteSql(tran, "delete from _VoucherEntryUdef where _id= " + id);
DBHelper.GetInstance(mContext).ExecuteSql(tran, DataManager.GetInstance(mContext).BuildUpdateSql(item.header));
DBHelper.GetInstance(mContext).InsertTable(tran,
EntityConvertor <VoucherEntry> .ToDataTable(item.entries), "_VoucherEntry");
saveUdefEntry(item, tran);
DBHelper.GetInstance(mContext).CommitTransaction(tran);
return(id);
}
catch (FinanceException ex)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
throw ex;
}
catch (Exception e)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
var traceId = SerialNoService.GetUUID();
logger.Error(e, traceId);
throw new FinanceException(FinanceResult.SYSTEM_ERROR, traceId);
}
}
/// <summary>
/// 对凭证的各个ID进行处理:内码、序号、凭证号
/// 序号要求连续，出现重复，直接取新的填充
/// 凭证号，在同一会计年度、期间、凭证字下唯一，出现重复，报错
/// </summary>
/// <param name="item">凭证主体</param>
/// <param name="tran">事务对象</param>
void GeneratorIds(VoucherHeader header, dynamic tran)
{
string exKey = string.Format("{0}_{1}_{2}", header.year, header.period, header.word);
SerialNoService noGenerator = new SerialNoService(mContext, tran)
{
SerialKey = SerialNoKey.VoucherNo, Ex = exKey
};
//var bExistNo = DBHelper.GetInstance(mContext).Exist(
// string.Format("select 1 from _voucherheader where _year = {0} and _period={1} and _word ='{2}' and _no={3}",
// header.year, header.period, header.word, header.no));
//if (bExistNo)
// throw new FinanceException(FinanceResult.RECORD_EXIST, string.Format("{0}年度第{1}期间{2}字序号{3}", header.year, header.period, header.word, header.no));
SerialNoService idGenerator = new SerialNoService(mContext, tran)
{
SerialKey = SerialNoKey.System
};
SerialNoService snGenerator = new SerialNoService(mContext, tran)
{
SerialKey = SerialNoKey.VoucherSn
};
header.id = idGenerator.GetIncrease();
header.serialNo = snGenerator.GetIncrease();
header.no = noGenerator.GetIncrease();
while (DBHelper.GetInstance(mContext).Exist(string.Format("select 1 from _voucherheader where _id = {0}", header.id)))
{
header.id = idGenerator.GetIncrease();
}
while (DBHelper.GetInstance(mContext).Exist(string.Format("select 1 from _voucherheader where _serialNo={0}", header.serialNo)))
{
header.serialNo = snGenerator.GetIncrease();
}
while (DBHelper.GetInstance(mContext).Exist(string.Format("select 1 from _voucherheader where _year = {0} and _period={1} and _word ='{2}' and _no={3}",
header.year, header.period, header.word, header.no)))
{
header.no = noGenerator.GetIncrease();
}
}
// [LinkNo, [uniqueKey, [k, v]]]
bool GenerateActItemGrp(string taskId, ref Dictionary <string, object> filedMap, ref List <Auxiliary> lst)
{
if (!filedMap.ContainsKey("item_type") || !filedMap.ContainsKey("item_no") || !filedMap.ContainsKey("item_name"))
{
return(false);
}
var itemType = filedMap["item_type"].ToString();
var typeItem = lst.FirstOrDefault(item => item.type == 0 && item.name == itemType);
if (typeItem == null)
{
logger.Error("invalid item type : {0}.", itemType);
return(false);
}
var itemNo = filedMap["item_no"].ToString();
var auxItem = lst.FirstOrDefault(item => item.type.ToString() == typeItem.no && item.no == itemNo);
if (auxItem == null)
{
auxItem = new Auxiliary
{
id = SerialNoService.GetInstance(mContext).Get(SerialNoKey.System),
type = long.Parse(typeItem.no),
no = itemNo,
name = filedMap["item_name"].ToString(),
groupId = (int)AuxiliaryGroup.AccountItems
};
DataManager.GetInstance(mContext).Insert(auxItem);
SerialNoService.GetInstance(mContext).Update(SerialNoKey.System);
}
if (filedMap.ContainsKey("actItemGrp"))
{
filedMap.Remove("actItemGrp");
}
filedMap["actItemGrp"] = auxItem.id;
return(true);
}
public void Save(AccountSubject aso)
{
if (string.IsNullOrEmpty(aso.no) || string.IsNullOrEmpty(aso.name))
{
throw new FinanceException(FinanceResult.IMPERFECT_DATA, "科目代码或名称不能为空");
}
if (aso.direction != 1 && aso.direction != -1)
{
throw new FinanceException(FinanceResult.IMPERFECT_DATA, "科目余额方向无效");
}
var bRet = DBHelper.GetInstance(mContext).Exist(string.Format("select 1 from _Auxiliary where _type ={0} and _id = {1}", (int)AuxiliaryType.AccountGroup, aso.groupId));
if (!bRet)
{
throw new FinanceException(FinanceResult.IMPERFECT_DATA, "科目类别无效");
}
dynamic tran = DBHelper.GetInstance(mContext).BeginTransaction();
try
{
bRet = DBHelper.GetInstance(mContext).Exist(tran, string.Format("select 1 from _accountsubject where _no ='{0}' and _id <> {1}", aso.no, aso.id));
if (bRet)
{
throw new FinanceException(FinanceResult.IMPERFECT_DATA, "代码已存在");
}
if (aso.id == 0)
{
var pos = aso.no.LastIndexOf('.');
if (pos != -1)
{
var pre = aso.no.Substring(0, pos);
var filter = new AccountSubject
{
no = pre
};
List <AccountSubject> lst = DataManager.GetInstance(mContext).Query(tran, filter);
if (lst.Count == 0)
{
throw new FinanceException(FinanceResult.IMPERFECT_DATA, "父级科目不存在：" + pre);
}
var parentItem = lst.FirstOrDefault();
aso.parentId = parentItem.id;
aso.rootId = parentItem.rootId;
aso.level = parentItem.level + 1;
parentItem.isHasChild = true;
DataManager.GetInstance(mContext).Update(tran, parentItem);
}
SerialNoService serial = new SerialNoService(mContext, tran)
{
SerialKey = SerialNoKey.System
};
aso.id = serial.GetIncrease();
aso.level = aso.level == 0 ? 1 : aso.level;
DataManager.GetInstance(mContext).Insert(tran, aso);
}
else
{
DataManager.GetInstance(mContext).Update(tran, aso);
}
UpdateFullName(aso, tran);
UserService.GetInstance(mContext).UpdateTimeStampArticle(TimeStampArticleEnum.AccountSubject, tran);
DBHelper.GetInstance(mContext).CommitTransaction(tran);
}
catch (Exception ex)
{
DBHelper.GetInstance(mContext).RollbackTransaction(tran);
throw ex;
}
}
