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);
}
}
DataTable decondeAuxiliary(DataTable dtDetail, long idIndex)
{
List <Auxiliary> lst = new List <Auxiliary>();
foreach (DataRow dr in dtDetail.Rows)
{
Auxiliary at = new Auxiliary();
at.id = idIndex;
AuxiliaryType atype = AuxiliaryType.Invalid;
foreach (DataColumn col in dtDetail.Columns)
{
switch (col.ColumnName)
{
case "代码":
at.no = dr[col.ColumnName].ToString();
break;
case "名称":
at.name = dr[col.ColumnName].ToString();
break;
case "描述":
at.description = dr[col.ColumnName].ToString();
break;
case "类型":
string strType = dr[col.ColumnName].ToString();
atype = AuxiliaryTypeMap[strType];
at.type = (long)atype;
break;
case "上级代码":
string strParent = dr[col.ColumnName].ToString();
long pid = 0L;
if (atype == AuxiliaryType.AccountGroup)
{
at.parentId = (long)AccountClassMap[strParent];
}
else if (long.TryParse(strParent, out pid))
{
at.parentId = pid;
}
break;
}
}
if (!string.IsNullOrEmpty(at.no))
{
lst.Add(at);
idIndex++;
}
}
//logger.Info(JsonConverter.JsonSerialize(lst));
DataTable dtRsp = EntityConvertor <Auxiliary> .ToDataTable(lst);
dtRsp.TableName = "Auxiliary";
return(dtRsp);
}
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);
}
}
public HttpResponseMessage Export(CashflowSheetExportRequest request)
{
ExcelExportor exportor = new ExcelExportor(new CashflowExportHandler());
Dictionary <string, string> filter = request.filter;
var lst = service.ListSheet(filter);
var dt = EntityConvertor <CashflowSheetItem> .ToDataTable(lst);
MemoryStream ms = new MemoryStream();
exportor.Export(ms, dt, ".xls");
string relativePath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
string sPath = Path.Combine(Path.GetFullPath(relativePath), "Cache");
if (!Directory.Exists(sPath))
{
Directory.CreateDirectory(sPath);
}
string fileName = SerialNoService.GetUUID() + ".xls";
string filePath = Path.Combine(sPath, fileName);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
var stream = new FileStream(filePath, FileMode.Open);
HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
response.Content = new StreamContent(stream);
response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = fileName
};
//System.IO.File.Delete(filePath);
return(response);
}
private void btn_Click(object sender, RoutedEventArgs e)
{
try
{
var txt = (sender as Button).Name;
switch (txt)
{
case "query":
var frmFilter = new FormListFilterPopup();
frmFilter.Filter = m_filter;
frmFilter.FilterPopupEvent += (args) =>
{
m_filter = args.Filter;
SheetModel = SheetModel;
};
frmFilter.Show();
break;
case "refresh":
SheetModel = SheetModel;
break;
case "formula":
if (SheetModel == SheetModel.FORMULA)
{
SheetModel = SheetModel.DATA;
}
else
{
SheetModel = SheetModel.FORMULA;
}
break;
case "exportformula":
SaveFileDialog sflg = new SaveFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
sflg.FileName = "资产负债表";
var bRnt = sflg.ShowDialog();
if (bRnt == null || bRnt == false)
{
return;
}
ExcelExportor exportor = new ExcelExportor(new BalanceSheetExportHandler());
var dt = EntityConvertor <ExcelTemplateItem> .ToDataTable(m_lstTemplate);
MemoryStream ms = new MemoryStream();
string flg = FileHelper.FileSuffix(sflg.FileName);
exportor.Export(ms, dt, flg);
using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
FileHelper.ExplorePath(sflg.FileName.Substring(0, sflg.FileName.LastIndexOf("\\")));
break;
case "importformula":
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
ofd.Title = "选择文件";
ofd.RestoreDirectory = true;
if (ofd.ShowDialog() == true)
{
DataFactory.Instance.GetTemplateExecuter().UploadTemplate("BalanceSheet", ofd.FileName);
FinanceMessageBox.Info("导入成功");
}
break;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
FinanceMessageBox.Error(ex.Message);
}
}
void IImportHandler.Deconde(ref DataSet ds)
{
DataTable dt = ds.Tables[0];
DataRow[] dataRows = dt.Select("1=1", "代码 asc");
long idIndex = SerialNoService.GetInstance(new Dictionary <string, object> {
{ "Tid", mTid }
}).GetIncrease(dt.Rows.Count, SerialNoKey.System);
Auxiliary auxiliary = new Auxiliary()
{
type = (long)AuxiliaryType.AccountGroup
};
var lstAuxiliary = DataManager.GetInstance(new Dictionary <string, object> {
{ "Tid", mTid }
}).Query <Auxiliary>(auxiliary);
///TODO:
/// 1、列名映射
/// 2、值转换
/// 3、计算父子关系
List <AccountSubject> lst = new List <AccountSubject>();
foreach (DataRow dr in dataRows)
{
AccountSubject at = new AccountSubject();
at.id = idIndex;
foreach (DataColumn col in dt.Columns)
{
switch (col.ColumnName)
{
case "代码":
var no = dr[col.ColumnName].ToString();
at.no = no;
var pointIndex = at.no.LastIndexOf(".");
if (pointIndex > -1)
{
var parentNo = no.Substring(0, pointIndex);
AccountSubject parentAccount = lst.FirstOrDefault(a => a.no == parentNo);
if (parentAccount == null)
{
throw new FinanceException(FinanceResult.RECORD_NOT_EXIST, "找不到父节点科目【" + parentNo + "】.");
}
at.parentId = parentAccount.id;
at.level = parentAccount.level + 1;
at.rootId = parentAccount.rootId == 0 ? parentAccount.id : parentAccount.rootId;
parentAccount.isHasChild = true;
}
else
{
at.level = 1;
}
break;
case "名称":
at.name = dr[col.ColumnName].ToString();
at.fullName = CalcFullName(lst, at);
break;
case "类别":
string strAux = dr[col.ColumnName].ToString();
Auxiliary aux = lstAuxiliary.Find(t => t.name == strAux);
if (aux == null)
{
//如果需要，去数据库检索一遍
throw new FinanceException(FinanceResult.RECORD_NOT_EXIST, "类别【" + strAux + "】.");
}
at.groupId = aux.id;
break;
case "余额方向":
at.direction = dr[col.ColumnName].ToString() == "借" ? 1 : -1;
break;
//case "现金科目":
// at.isCashSubject = dr[col.ColumnName].ToString() == "是";
// break;
//case "银行科目":
// at.isBankSubject = dr[col.ColumnName].ToString() == "是";
// break;
//case "现金等价物":
// at.isCashEqulvalent = dr[col.ColumnName].ToString() == "是";
// break;
//case "主表项目":
// var val = dr[col.ColumnName].ToString();
// if(!string.IsNullOrEmpty(val))
// at.mainProjectId = 0L;
// break;
}
}
if (!string.IsNullOrEmpty(at.no))
{
lst.Add(at);
idIndex++;
}
}
DataTable dtRsp = EntityConvertor <AccountSubject> .ToDataTable(lst);
dtRsp.TableName = "AccountSubject";
ds = new DataSet();
ds.Tables.Add(dtRsp);
}