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);
}
}
public List <UdefTemplateItem> FindUdefTemplate(string name)
{
DataTable dt = null;
if (!string.IsNullOrEmpty(name))
{
dt = mDBHelper.ExecuteDt(string.Format("select * from _UdefTemplate where _tableName ='{0}'", name));
}
else
{
dt = mDBHelper.ExecuteDt(string.Format("select * from _UdefTemplate order by _tableName ,_reserved,_tabIndex"));
}
var lst = EntityConvertor <UdefTemplateItem> .ToList(dt);
lst.ForEach(item => {
var val = item.defaultVal;
var str = val.ToString();
if (str.StartsWith("$") && str.IndexOf("(") != -1 && str.LastIndexOf(")") > 0)
{
str = str.Substring(str.IndexOf("(") + 1, str.LastIndexOf(")") - str.IndexOf("(") - 1);
switch (str)
{
case "currentYear":
item.defaultVal = SystemProfileService.GetInstance(mContext).GetString(SystemProfileCategory.Account, SystemProfileKey.CurrentYear);
break;
case "currentPeriod":
item.defaultVal = SystemProfileService.GetInstance(mContext).GetString(SystemProfileCategory.Account, SystemProfileKey.CurrentPeriod);
break;
}
}
});
return(lst);
}
public Dictionary <string, string> Calc(Dictionary <string, string> template)
{
SystemProfileService systemProfile = SystemProfileService.GetInstance(mContext);
var curYear = systemProfile.GetInt(SystemProfileCategory.Account, SystemProfileKey.CurrentYear);
var curPeriod = systemProfile.GetInt(SystemProfileCategory.Account, SystemProfileKey.CurrentPeriod);
AccountBalanceService abService = AccountBalanceService.GetInstance(mContext);
m_lstYear = abService.QueryOccurs(curYear, 1, curYear, 12);
m_lstOccurs = abService.QueryOccurs(curYear, curPeriod, curYear, curPeriod);
m_lstAso = DataManager.GetInstance(mContext).Query <AccountSubject>(null).OrderBy(a => a.no).ToList();
Dictionary <string, string> result = new Dictionary <string, string>();
List <string> sumKeys = new List <string>();
foreach (KeyValuePair <string, string> kv in template)
{
if (Regex.IsMatch(kv.Value, "(L)") && !Regex.IsMatch(kv.Value, "(SL)"))
{
sumKeys.Add(kv.Key);
continue;
}
result.Add(kv.Key, Calc(kv.Value));
}
sumKeys.Sort();
foreach (string key in sumKeys)
{
result.Add(key, SampleCalculator.sumLine(template[key], key, result));
}
return(result);
}
public void Finish()
{
SystemProfileService systemProfileService = SystemProfileService.GetInstance(mContext);
var startPeriod = new PeridStrunct
{
Year = systemProfileService.GetInt(SystemProfileCategory.Account, SystemProfileKey.StartYear),
Period = systemProfileService.GetInt(SystemProfileCategory.Account, SystemProfileKey.StartPeriod)
};
PeridStrunct prev = CommonUtils.CalcPrevPeriod(startPeriod);
DBHelper.GetInstance(mContext).ExecuteSql(string.Format(@"insert into _AccountBalance (_year,_period,_accountSubjectId,_debitsAmount,_creditAmount)
select {0},{1},_accountSubjectId,_debitsAmount,_creditAmount from _BeginBalance ", prev.Year, prev.Period));
systemProfileService.Update(SystemProfileCategory.Account, SystemProfileKey.IsInited, "1");
}
/// <summary>
/// 数据有效性检查
/// 头部：
/// 1、凭证字、凭证号不能为空
/// 2、业务日期、日期、年度、期间不能为空
/// 3、日期、年度、期间不能为已结账期间
/// 分录：
/// 1、科目、金额不能为0
/// 2、借贷方要平衡
/// </summary>
/// <param name="item"></param>
void CheckData(Voucher item)
{
var header = item.header;
DataCheckHelper.StringIsNullOrEmpty(header.word, "凭证字不能为空");
//DataCheckHelper.NumberIsZero(header.no, "凭证号不能为0");
DataCheckHelper.IsNull(header.businessDate, "业务日期不能为空");
DataCheckHelper.IsNull(header.date, "日期不能为空");
DataCheckHelper.NumberIsZero(header.year, "会计年度不能为0");
DataCheckHelper.NumberIsZero(header.period, "会计期间不能为0");
DataCheckHelper.DateInPeriod(header.date, header.year, header.period, "日期和会计年度期间不符");
SystemProfileService systemProfileService = SystemProfileService.GetInstance(mContext);
var currentYear = systemProfileService.GetInt(SystemProfileCategory.Account, SystemProfileKey.CurrentYear);
var currentPeriod = systemProfileService.GetInt(SystemProfileCategory.Account, SystemProfileKey.CurrentPeriod);
DateTime current = new DateTime(currentYear, currentPeriod, 1);
if (current > header.date)
{
throw new FinanceException(FinanceResult.IMPERFECT_DATA, "不能录入已过账期间的凭证");
}
if (item.entries == null || item.entries.Count == 0)
{
throw new FinanceException(FinanceResult.IMPERFECT_DATA, "凭证不能没有分录");
}
decimal totalAmount = 0M;
foreach (var entry in item.entries)
{
if (entry.accountSubjectId == 0 && string.IsNullOrEmpty(entry.accountSubjectNo))
{
throw new FinanceException(FinanceResult.IMPERFECT_DATA, string.Format("第{0}行分录，科目不能为空", entry.index));
}
DataCheckHelper.NumberIsZero(entry.amount, string.Format("第{0}行分录，金额不能为0", entry.index));
totalAmount += (entry.direction * entry.amount);
}
if (totalAmount != 0M)
{
throw new FinanceException(FinanceResult.AMMOUNT_IMBALANCE);
}
}
public List <CashflowSheetItem> ListSheet(Dictionary <string, string> filter)
{
List <ExcelTemplateItem> lstTemplate = TemplateSevice.GetInstance(mContext).FindTemplate("现金流量表");
var result = new List <CashflowSheetItem>();
var beginYear = int.Parse(filter["beginYear"]);
var beginPeriod = int.Parse(filter["beginPeriod"]);
var endYear = int.Parse(filter["endYear"]);
var endPeriod = int.Parse(filter["endPeriod"]);
var prev = CommonUtils.CalcPrevPeriod(new PeridStrunct {
Year = beginYear, Period = beginPeriod
});
var curYear = SystemProfileService.GetInstance(mContext).GetInt(SystemProfileCategory.Account, SystemProfileKey.CurrentYear);
m_lstAso = DataManager.GetInstance(mContext).Query <AccountSubject>(null).OrderBy(a => a.no).ToList();
m_lstBegin = AccountBalanceService.GetInstance(mContext).QuerySettled(prev.Year, prev.Period);
m_lstOccurs = AccountBalanceService.GetInstance(mContext).QueryOccurs(beginYear, beginPeriod, endYear, endPeriod);
m_lstYear = AccountBalanceService.GetInstance(mContext).QueryOccurs(curYear, 1, curYear, 12);
Dictionary <int, CalTempObj> dictTemplate = new Dictionary <int, CalTempObj>();
foreach (var template in lstTemplate)
{
var item = new CashflowSheetItem();
item.Name = template.a;
item.LineNo = template.b;
var lineNo = 0;
if (int.TryParse(item.LineNo, out lineNo))
{
item.Amount = CalcFormula(lineNo, template.c);
dictTemplate.Add(lineNo, new CalTempObj(template, item.Amount));
}
result.Add(item);
}
foreach (var item in result)
{
//计算扩展列
var lineNo = 0;
if (int.TryParse(item.LineNo, out lineNo))
{
decimal extendAmount = 0;
if (CalcExtend(lineNo, dictTemplate, out extendAmount))
{
item.Amount = extendAmount;
}
}
}
foreach (var item in result)
{
//计算合计列L
var lineNo = 0;
if (int.TryParse(item.LineNo, out lineNo))
{
decimal sumAmount = 0;
if (CalcSum(lineNo, dictTemplate, out sumAmount))
{
item.Flag = 1;
item.Amount = sumAmount;
dictTemplate[lineNo].originAmount = item.Amount;
}
}
}
return(result);
}
void ExecCarriedForward(string taskId, string procName, Dictionary <string, object> paramMap)
{
if (paramMap == null)
{
paramMap = new Dictionary <string, object>();
}
RefreshTaskResult(taskId, ExecTaskType.CarriedForward.ToString(), 0, "开始执行", "");
Auxiliary auxFilter = new Auxiliary()
{
type = (int)AuxiliaryType.CarriedForward, no = procName
};
var lst = DataManager.GetInstance(mContext).Query(auxFilter);
if (lst == null || lst.Count == 0)
{
throw new FinanceException(FinanceResult.RECORD_NOT_EXIST, "结转方式不存在");
}
var auxObj = lst.FirstOrDefault();
var templateList = TemplateSevice.GetInstance(mContext).ListCarriedForwardTemplate(auxObj.id);
if (templateList == null || templateList.Count == 0)
{
throw new FinanceException(FinanceResult.RECORD_NOT_EXIST, "结转模板不存在");
}
var year = SystemProfileService.GetInstance(mContext).GetInt(SystemProfileCategory.Account, SystemProfileKey.CurrentYear);
var period = SystemProfileService.GetInstance(mContext).GetInt(SystemProfileCategory.Account, SystemProfileKey.CurrentPeriod);
var dbHelper = DBHelper.GetInstance(mContext);
//1、检查是否还有未过账
if (dbHelper.Exist(string.Format("select 1 from _VoucherHeader where _year = {0} and _period = {1} and _status < {2} and _status <> {3}"
, year, period, (int)VoucherStatus.Posted, (int)VoucherStatus.Canceled)))
{
throw new FinanceException(FinanceResult.INCORRECT_STATE, "当前凭证没有全部过账");
}
//2、生成凭证
var direction = 1;
switch (auxObj.description)
{
case "income":
break;
case "cost":
direction = -1;
break;
case "investment":
break;
case "profits":
break;
}
var word = "转";
if (paramMap.ContainsKey("word"))
{
word = paramMap["word"].ToString();
}
var explanation = auxObj.name;
if (paramMap.ContainsKey("explanation"))
{
explanation = paramMap["explanation"].ToString();
}
var lstAccountObject = AccountSubjectService.GetInstance(mContext).List();
var lstEntries = new List <VoucherEntry>();
var index = 0;
foreach (var temp in templateList)
{
var srcActObj = lstAccountObject.FirstOrDefault(actObj => actObj.id == temp.src);
var dstActObj = lstAccountObject.FirstOrDefault(actObj => actObj.id == temp.dst);
if (srcActObj == null || dstActObj == null)
{
throw new FinanceException(FinanceResult.RECORD_NOT_EXIST, "结转模板数据错误");
}
var amountObj = dbHelper.ExecuteScalar(string.Format(@"select sum(_amount * _direction) as _amount
from _VoucherEntry where _accountSubjectId = {0}
and _id in (select _id from _VoucherHeader where _year = {1} and _period = {2})", temp.src, year, period));
if (amountObj == null || amountObj == DBNull.Value)
{
continue;
}
var amount = (decimal)amountObj;
if (amount < 0)
{
direction = -1 * direction;
amount = -1 * amount;
}
var voucherEntrySrc = new VoucherEntry();
voucherEntrySrc.index = index++;
voucherEntrySrc.accountSubjectId = temp.src;
voucherEntrySrc.accountSubjectNo = srcActObj.no;
voucherEntrySrc.amount = amount;
voucherEntrySrc.direction = direction;
voucherEntrySrc.explanation = explanation;
lstEntries.Add(voucherEntrySrc);
var voucherEntryDst = new VoucherEntry();
voucherEntryDst.index = index++;
voucherEntryDst.accountSubjectId = temp.dst;
voucherEntryDst.accountSubjectNo = dstActObj.no;
voucherEntryDst.amount = amount;
voucherEntryDst.direction = -1 * direction;
voucherEntryDst.explanation = explanation;
lstEntries.Add(voucherEntryDst);
}
if (lstEntries.Count == 0)
{
throw new FinanceException(FinanceResult.RECORD_NOT_EXIST, "没有需要结转的凭证");
}
var voucherHeader = new VoucherHeader();
voucherHeader.word = word;
voucherHeader.year = year;
voucherHeader.period = period;
voucherHeader.date = CommonUtils.CalcMaxPeriodDate(year, period);
var now = DateTime.Now;
voucherHeader.businessDate = now;
voucherHeader.creatTime = now;
voucherHeader.creater = 13594;
Voucher voucher = new Voucher();
voucher.header = voucherHeader;
voucher.entries = lstEntries;
var id = VoucherService.GetInstance(mContext).Add(voucher);
var h = VoucherService.GetInstance(mContext).FindHeader(id);
var msg = string.Format("结转成功，凭证字号：{0} - {1};", h.word, h.no);
RefreshTaskResult(taskId, ExecTaskType.CreateVoucher.ToString(), 100, "", msg, 1);
}
public InterfaceService(IDictionary <string, object> ctx)
{
mContext = ctx;
mBDBHelper = SystemProfileService.GetInstance(ctx).GetBDBHelper();
}
