public List <ExcelTemplateItem> FindTemplate(string name)
{
DataTable dt = mDBHelper.ExecuteDt(string.Format("select * from _ExcelTemplate where _name ='{0}'", name));
var lst = EntityConvertor <ExcelTemplateItem> .ToList(dt);
return(lst);
}
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 List <BeginBalance> List()
{
DataTable dt = DBHelper.GetInstance(mContext).ExecuteDt("select * from _beginBalance");
var lst = EntityConvertor <BeginBalance> .ToList(dt);
return(lst);
}
public List <CarriedForwardTemplate> ListCarriedForwardTemplate(long id)
{
DataTable dt = mDBHelper.ExecuteDt(string.Format("select * from _CarriedForwardTemplate where _id ={0}", id));
var lst = EntityConvertor <CarriedForwardTemplate> .ToList(dt);
return(lst);
}
public static void AccoutPrint()
{
var lst = DataManager.GetInstance(null).Query <AccountCtl>(null);
var str = EntityConvertor <AccountCtl> .PrintString(lst);
logger().Info(str);
}
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 DSharpPlusDiscord(IBotConfiguration botConfiguration, EntityConvertor entityConvertor, IServiceProvider services, ILanguageProvider lang)
{
_botConfiguration = botConfiguration;
_services = services;
_entityConvertor = entityConvertor;
_lang = lang;
}
public ProfileCommand(
EntityConvertor entityConvertor,
ProfileService profileService)
{
_entityConvertor = entityConvertor;
_profileService = profileService;
}
public List <MenuTableMap> AllMenuTables()
{
DataTable dt = DBHelper.GetInstance(mContext).ExecuteDt("select * from _MenuTableMap order by _group,_index");
var lst = EntityConvertor <MenuTableMap> .ToList(dt);
return(lst);
}
public CommandHandler(IDiscord discord, IServiceProvider services, ILogWriter logger, EntityConvertor convertor)
{
_discord = discord;
_commandService = new CommandService();
_services = services;
_logger = logger;
_convertor = convertor;
}
void UpdateFullName(AccountSubject aso, dynamic tran)
{
var dt = DBHelper.GetInstance(mContext).ExecuteDt(tran, "select * from _accountsubject order by _no");
var lst = EntityConvertor <AccountSubject> .ToList(dt);
aso.fullName = CalcFullName(lst, aso);
DBHelper.GetInstance(mContext).ExecuteSql(tran, string.Format("update _accountsubject set _fullName = '{0}' where _id = {1}", aso.fullName, aso.id));
}
void ExecCreateVoucher(string taskId, string procName, Dictionary <string, object> filter)
{
DataSet ds = null;
if (filter == null)
{
ds = (DataSet)mBDBHelper.RunDataSetProc(procName);
}
else
{
var prams = new SqlParameter[filter.Keys.Count];
var i = 0;
foreach (var kv in filter)
{
prams[i++] = new SqlParameter(kv.Key, kv.Value);
}
ds = (DataSet)mBDBHelper.RunDataSetProc(procName, prams);
}
var lstHeader = EntityConvertor <VoucherHeader> .ToList(ds.Tables[0]);
var lstEntries = EntityConvertor <VoucherEntry> .ToList(ds.Tables[1]);
var dtUdefenties = ds.Tables[1];
var lstUdefenties = new Dictionary <string, Dictionary <string, Dictionary <string, object> > >();
Auxiliary auxfilter = new Auxiliary();
var lstAuxiliary = DataManager.GetInstance(mContext).Query(auxfilter);
foreach (DataRow dr in dtUdefenties.Rows)
{
var map = EntityConvertor <Object> .ToMap(dr);
GenerateActItemGrp(taskId, ref map, ref lstAuxiliary);
var linkNo = map["linkNo"].ToString();
if (lstUdefenties.ContainsKey(linkNo))
{
lstUdefenties[linkNo].Add(map["uniqueKey"].ToString(), map);
}
else
{
var val = new Dictionary <string, Dictionary <string, object> >();
val.Add(map["uniqueKey"].ToString(), map);
lstUdefenties.Add(linkNo, val);
}
}
RefreshTaskResult(taskId, ExecTaskType.CreateVoucher.ToString(), 0, "开始执行", "");
//这里需要异步
var lstAccountSubjectNo = lstEntries.Select(e => e.accountSubjectNo).Distinct().ToList();
var bRnt = GenerateAccoutSubject(taskId, lstAccountSubjectNo);
if (bRnt)
{
GenerateVoucher(taskId, lstHeader, lstEntries, lstUdefenties);
}
}
public DSharpPlusDiscord(
IBotConfiguration botConfiguration,
EntityConvertor entityConvertor,
IServiceProvider services)
{
_botConfiguration = botConfiguration;
_services = services;
_entityConvertor = entityConvertor;
}
public CommandHandler(IDiscord discord, CommandService commandService, IServiceProvider services, ILogWriter logger, EntityConvertor convertor, IBotConfiguration botConfig)
{
_discord = discord;
_commandService = commandService;
_services = services;
_logger = logger;
_convertor = convertor;
_botConfig = botConfig;
}
public FinanceResponse List()
{
var dt = DBHelper.DefaultInstance.ExecuteDt("select _id, _no, _name from _AccountCtl order by _id");
var lst = EntityConvertor <SampleItem> .ToList(dt);
return(new SampleItemListResponse {
Content = lst
});
}
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 CommandHandler(IDiscord discord, IServiceProvider services, ILogWriter logger, EntityConvertor convertor, IBotConfiguration botConfig, ICommandProcessor commandProcessor)
{
_discord = discord;
_commandService = new CommandService();
_services = services;
_logger = logger;
_convertor = convertor;
_botConfig = botConfig;
_commandProcessor = commandProcessor;
}
/// <summary>
/// dr转换为Dto实体对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dr"></param>
/// <returns></returns>
public static T ToEntity <T>(this DataRow dr) where T : class, new()
{
DtoMapping dtoMapping = DtoMappingHelper.GetDtoMapping <T>();
if (dtoMapping.EntityConvertor == null)
{
dtoMapping.EntityConvertor = EntityConvertor.CreateEntityConvertor <T>(); //存入dtoMapping中,缓存起来
}
return((T)((EntityConvertorDelegate)dtoMapping.EntityConvertor)(dr));
}
public List <AccountSubject> List(int status = 0)
{
string where = "";
if (status != 0)
{
where = " where _isDeleted = " + status;
}
var dt = DBHelper.GetInstance(mContext).ExecuteDt("select * from _accountsubject " + where + " order by _no");
return(EntityConvertor <AccountSubject> .ToList(dt));
}
/// <summary>
/// 获取期间已结账期初余额
/// </summary>
/// <param name="year"></param>
/// <param name="perid"></param>
/// <returns></returns>
public List <AccountAmountItem> QuerySettled(long year, long perid)
{
var dt = DBHelper.GetInstance(mContext).ExecuteDt(string.Format("select * from _AccountBalance where _year={0} and _period= {1}", year, perid));
if (dt == null || dt.Rows.Count == 0)
{
return(new List <AccountAmountItem>());
}
var lst = EntityConvertor <AccountAmountItem> .ToList(dt);
return(lst);
}
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 List <Voucher> List(IDictionary <string, object> filter)
{
var result = new List <Voucher>();
string where = string.Empty;
if (filter != null)
{
where = appendFilter(where, buildDateFilter(filter));
where = appendFilter(where, buildStatusFilter(filter));
where = appendFilter(where, buildUserFilter(filter));
where = appendFilter(where, buildTextFilter(filter));
if (where != "")
{
where = " where " + where;
}
DataTable dt = DBHelper.GetInstance(mContext).ExecuteDt("select _id from _VoucherHeader " + where);
if (dt == null || dt.Rows.Count == 0)
{
return(result);
}
List <long> Ids1 = GetIdsFromDataTable(dt);
var idFilter = string.Join(",", Ids1);
where = "where _id in (" + idFilter + ")";
where = appendFilter(where, buildEntryFilter(filter));
DataTable dtIds = DBHelper.GetInstance(mContext).ExecuteDt("select _id from _VoucherEntry " + where);
if (dtIds == null || dtIds.Rows.Count == 0)
{
return(result);
}
List <long> ids2 = GetIdsFromDataTable(dtIds);
idFilter = string.Join(",", ids2);
where = "where _id in (" + idFilter + ")";
}
DataTable dtHeader = DBHelper.GetInstance(mContext).ExecuteDt("select * from _VoucherHeader " + where);
DataTable dtEntrys = DBHelper.GetInstance(mContext).ExecuteDt("select * from _VoucherEntry " + where);
List <VoucherHeader> lstHeader = EntityConvertor <VoucherHeader> .ToList(dtHeader);
List <VoucherEntry> lstEntreis = EntityConvertor <VoucherEntry> .ToList(dtEntrys);
foreach (var header in lstHeader)
{
Voucher voucher = new Voucher();
voucher.header = header;
voucher.entries = lstEntreis.FindAll(entry => entry.id == header.id);
result.Add(voucher);
}
return(result);
}
public SystemProfile Find(SystemProfileCategory category, SystemProfileKey key)
{
var dt = DBHelper.GetInstance(mContext).ExecuteDt(string.Format("select * from _systemprofile where _category='{0}' and _key='{1}'", category, key));
if (dt == null || dt.Rows.Count == 0)
{
throw new FinanceException(FinanceResult.RECORD_NOT_EXIST);
}
var rsp = EntityConvertor <SystemProfile> .ToEntity(dt.Rows[0]);
return(rsp);
}
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 Dictionary <string, Dictionary <string, object> > FindUdefEntrys(long id)
{
var result = new Dictionary <string, Dictionary <string, object> >();
DataTable dt = DBHelper.GetInstance(mContext).ExecuteDt("select * from _VoucherEntryUdef where _id =" + id);
if (dt == null)
{
return(result);
}
foreach (DataRow dr in dt.Rows)
{
var map = EntityConvertor <int> .ToMap(dr);
var key = dr["_uniqueKey"].ToString();
result.Add(key, map);
}
return(result);
}
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);
}
public List <MenuTableMap> MenuTables()
{
var userId = mContext["UserId"].ToString();
var userName = mContext["UserName"].ToString();
if (userName == "admin")
{
return(AllMenuTables());
}
else
{
var sql = string.Format(@"select menu.* from _MenuTableMap menu right join _AccessRight access on menu._group = access._group and menu._name = access._name
where access._id = {0} and access._mask > 0 order by _group,_index", userId);
var dt = DBHelper.GetInstance(mContext).ExecuteDt(sql);
var lst = EntityConvertor <MenuTableMap> .ToList(dt);
return(lst);
}
}
private void btnDBCreateIndex_Click(object sender, EventArgs e)
{
StringBuffer sql = "SELECT TOP 1000 Id,FirstName,LastName FROM Person(NOLOCK)";
try
{
IList<Person> listPersons = EntityConvertor.QueryForList<Person>(sql.ToString(), strSqlConn, null);
SetOutput("======================DB索引创建开始===============================");
IndexModifier modifier = new IndexModifier(INDEX_STORE_PATH, new StandardAnalyzer(), true);
#region 同步创建索引
Stopwatch watch = new Stopwatch();
watch.Start();
IndexDB(modifier, listPersons);
modifier.Optimize();//优化索引
modifier.Close();//关闭索引读写器
watch.Stop();
StringBuffer sb = "索引完成,共用时：" + watch.Elapsed.Hours + "时 " + watch.Elapsed.Minutes + "分 " + watch.Elapsed.Seconds + "秒 " + watch.Elapsed.Milliseconds + "毫秒";
SetOutput(sb);
#endregion
//#region 异步创建索引
//dtStart = DateTime.Now;
//AsyncIndexDBCaller caller = new AsyncIndexDBCaller(IndexDB);
//IAsyncResult ar = caller.BeginInvoke(modifier, listPersons, new AsyncCallback(IndexCallback), modifier);
//#endregion
}
catch (Exception ex)
{
SetOutput(ex.Message);
}
}
public RemoteRepositoryCommand(RemoteRepositoryService remoteRepoService, EntityConvertor entityConvertor)
{
_remoteRepoService = remoteRepoService;
_entityConvertor = entityConvertor;
}
public ProfileCommand(EntityConvertor entityConvertor, ProfileService profileService, IDiscordMessages discordMessages, ILogger logger)
{
_entityConvertor = entityConvertor;
_profileService = profileService;
}
