/// <summary>
/// Checks which invite link has been used by newly joined user and
/// updates corresponding record in database for it
/// </summary>
/// <param name="user">newly joined user</param>
private async Task OnUserJoinedAsync(SocketGuildUser user)
{
await using var dbContext = DatabaseFactory.Create();
var inviteLinks = await user.Guild.GetInvitesAsync();
var inviteeDb = await dbContext.GetOrCreateUserEntityAsync(user);
try
{
await user.SendMessageAsync(Config.GetValue <string>("Welcoming:DirectMessage"));
}
catch (HttpException ex) when(ex.DiscordCode == 50007)
{
// User has disabled DMs
}
foreach (var link in inviteLinks)
{
if (link.Inviter.Id != DiscordSocketClient.CurrentUser.Id)
{
continue;
}
if (link.Uses == 0)
{
continue;
}
var invite = await dbContext.Invites.AsQueryable()
.Where(i => i.InviteLink == link.Url)
.FirstOrDefaultAsync();
if (invite == null)
{
continue;
}
invite.UsedByUserId = inviteeDb.Id;
await dbContext.SaveChangesAsync();
await link.DeleteAsync();
var welcomeMessageTemplate = Config.GetValue <string>("Welcoming:Message");
if (welcomeMessageTemplate == null)
{
return;
}
var welcomeMessage = string.Format(
welcomeMessageTemplate,
await UsersService.GetDisplayNameAsync(user)
);
await user.Guild.DefaultChannel.SendMessageAsync(welcomeMessage);
return;
}
await user.Guild.DefaultChannel.SendMessageAsync(
$"Nový uživatel **{Format.Sanitize(user.Username)}** se připojil na server pomocí odkazu, který nebyl vytvořen `$invite` příkazem!");
}
public void Test_Initialization()
{
var dbConfig = new DbConfig()
{
ConnectionString = "Server=127.0.0.1;Port=3306; Uid=root; Pwd=1197344; Database=test;",
DatabaseType = DatabaseType.MySql,
SchemaName = "test",
AuditRecordType = typeof(AuditRecord)
};
DatabaseFactory.Initialize(dbConfig);
database = DatabaseFactory.Create();
Mapping.SetNextId <Employee>(NextIdOption.AutoIncrement);
Mapping.SetNextId <Program>(NextIdOption.AutoIncrement);
Mapping.SetNextId <EmployeeProgram>(NextIdOption.AutoIncrement);
Mapping.SetNextId <Address>(NextIdOption.AutoIncrement);
Mapping.SetNextId <FinancialInfo>(NextIdOption.AutoIncrement);
Mapping.SetNextId <AdditionalInfo>(NextIdOption.AutoIncrement);
Mapping.SetNextId <Lookup>(NextIdOption.AutoIncrement);
Mapping.SetNextId <AuditRecord>(NextIdOption.AutoIncrement);
}
public void GetAllBots_ExistingBots_ShouldReturnListOfBots()
{
var databaseContext = DatabaseFactory.Create();
var timeProvider = TimeProviderFactory.Create();
var botService = new BotsService(databaseContext, timeProvider);
databaseContext.Bots.Add(new BotModel
{
Id = "1000",
Name = "Bot1",
CreateTime = timeProvider.Get()
});
databaseContext.Bots.Add(new BotModel
{
Id = "1001",
Name = "Bot2",
CreateTime = timeProvider.Get()
});
databaseContext.SaveChanges();
var result = botService.GetAllBots();
Assert.Equal(2, result.Count);
Assert.Equal("1000", result[0].Id);
Assert.Equal("1001", result[1].Id);
}
public SchemaPropertyExtension Load(string targetSchemaType, string sourceID)
{
string sql = "SELECT TOP 1 * FROM SC.SchemaPropertyExtensions WHERE ";
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendCondition("TargetSchemaType", targetSchemaType);
builder.AppendCondition("SourceID", sourceID);
sql += builder.ToSqlString(TSqlBuilder.Instance);
SchemaPropertyExtension obj = null;
using (DbContext context = DbContext.GetContext(GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
using (var dr = db.ExecuteReader(System.Data.CommandType.Text, sql))
{
if (dr.Read())
{
obj = new SchemaPropertyExtension(dr["TargetSchemaType"].ToString(), dr["SourceID"].ToString(), dr["Description"].ToString());
obj.InternalDefinitionXml = dr["Definition"].ToString();
}
}
}
return(obj);
}
/// <summary>获取费用单位开放权限列表
/// </summary>
/// <param name="filialeId">公司ID</param>
/// <param name="branchId">部门ID</param>
/// <param name="companyId">往来单位ID</param>
/// <returns></returns>
public IEnumerable <CostPermissionInfo> GetCostPermissionList(Guid filialeId, Guid branchId, Guid companyId)
{
string sql = @"
SELECT CP.FilialeId,CP.BranchId,CC.CompanyId,CC.CompanyName
FROM CostCompany CC
INNER JOIN CostPermission CP ON CC.CompanyId=CP.CompanyId
WHERE 1=1
";
var parms = new[]
{
new Parameter("@CompanyId", companyId),
new Parameter(PARM_FILIALE_ID, filialeId),
new Parameter(PARM_BRANCH_ID, branchId)
};
if (filialeId != Guid.Empty)
{
sql += " AND CP.FilialeId=@FilialeId";
}
if (branchId != Guid.Empty)
{
sql += " AND CP.BranchId=@BranchId";
}
if (companyId != Guid.Empty)
{
sql += " and CC.CompanyId=@CompanyId";
}
using (var db = DatabaseFactory.Create())
{
return(db.Select <CostPermissionInfo>(true, sql, parms));
}
}
/// <summary>
/// 读取没有安排到任务中的JobID的集合
/// </summary>
/// <param name="batchCount"></param>
/// <returns></returns>
public IList <string> LoadUnTaskedJobs(int batchCount, TimeSpan timeOffset)
{
string sqlTemplate = "SELECT {0} J.JOB_ID FROM WF.JOBS J LEFT JOIN WF.SYS_NOT_RUNNING_TASK T WITH(UPDLOCK READPAST) ON J.JOB_ID = T.RESOURCE_ID WHERE (T.STATUS IS NULL) AND {1} ORDER BY J.CREATE_TIME";
string top = batchCount >= 0 ? "TOP " + batchCount : string.Empty;
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("ENABLED", "1");
WhereSqlClauseBuilder builder2 = new WhereSqlClauseBuilder(LogicOperatorDefine.Or);
builder2.AppendItem("LAST_START_EXE_TIME", string.Format("DATEADD(SECOND, -{0}, GETDATE())", (int)timeOffset.TotalSeconds), "<", true);
builder2.AppendItem("LAST_START_EXE_TIME", "NULL", "IS", true);
string sql = string.Format(sqlTemplate, top, new ConnectiveSqlClauseCollection(builder, builder2).ToSqlString(TSqlBuilder.Instance));
List <string> result = new List <string>(800);
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
DataTable table = db.ExecuteDataSet(CommandType.Text, sql).Tables[0];
foreach (DataRow row in table.Rows)
{
result.Add(row["JOB_ID"].ToString());
}
}
return(result);
}
public override void DeleteOperation()
{
DataView users = new DataView(this.Context.OriginalData.Tables["USERS"], string.Empty, "GUID", DataViewRowState.Deleted);
Database db = DatabaseFactory.Create(this.Context.InitialParams.AccreditAdminConnectionName);
foreach (DataRowView drv in users)
{
try
{
string user_delete = string.Format("DELETE USERS WHERE GUID = '{0}'", drv["GUID"].ToString());
int count = db.ExecuteNonQuery(CommandType.Text, user_delete);
if (count == 1)
{
this.DeleteCount++;
this.Context.InitialParams.Log.Write(string.Format("USERS表GUID是{0},LogonName是{1}的记录删除成功",
drv["GUID"].ToString(), drv["LOGON_NAME"].ToString()));
}
}
catch (Exception ex)
{
this.Context.InitialParams.Log.Write(string.Format("用户表GUID 是{0},LogonName是{1}的记录执行删除时出错,错误是{2}",
drv["GUID"].ToString(), drv["LOGON_NAME"].ToString(), ex.Message));
}
}
}
public void Log_ExistingBot_ShouldReturnTrueAndSaveInDatabase()
{
var databaseContext = DatabaseFactory.Create();
var timeProvider = TimeProviderFactory.Create();
var statsService = new StatsService(databaseContext, timeProvider);
databaseContext.Bots.Add(new BotModel
{
Id = "1",
Name = "Bot1"
});
databaseContext.SaveChanges();
var stats = new LogStatsDto
{
BotId = "1",
ExecutedCommandsCount = 2,
GuildsCount = 3,
MembersCount = 4
};
var result = statsService.Log(stats);
var loggedStats = databaseContext.Stats.First();
Assert.True(result);
Assert.Equal(1, databaseContext.Stats.Count());
Assert.Equal("1", loggedStats.BotId);
Assert.Equal(timeProvider.Get(), loggedStats.CreateTime);
Assert.Equal(2, loggedStats.ExecutedCommandsCount);
Assert.Equal(3, loggedStats.GuildsCount);
Assert.Equal(4, loggedStats.MembersCount);
}
public void GetStatsForBot_ExistingBot_ShouldReturnListOfStats()
{
var databaseContext = DatabaseFactory.Create();
var timeProvider = TimeProviderFactory.Create();
var statsService = new StatsService(databaseContext, timeProvider);
databaseContext.Bots.Add(new BotModel
{
Id = "1",
Name = "Bot1",
Stats = new List <StatsModel>
{
new StatsModel {
BotId = "1", CreateTime = timeProvider.Get(), ExecutedCommandsCount = 1001
},
new StatsModel {
BotId = "1", CreateTime = timeProvider.Get().AddDays(1), ExecutedCommandsCount = 1002
},
new StatsModel {
BotId = "1", CreateTime = timeProvider.Get().AddDays(1), ExecutedCommandsCount = 1003
}
}
});
databaseContext.SaveChanges();
var result = statsService.GetStatsForBot("1");
Assert.Equal(2, result.Count);
Assert.Equal(1001, result[0].ExecutedCommandsCount);
Assert.Equal(1003, result[1].ExecutedCommandsCount);
}
private DataSet DoSplitPageQuery(QueryCondition qc, bool retrieveTotalCount)
{
string sql = GetQuerySql(qc, retrieveTotalCount);
using (DbContext context = DbContext.GetContext(string.IsNullOrEmpty(this.ConnectionName) ? ConnectionDefine.DBConnectionName : this.ConnectionName))
{
DataSet ds = null;
int serverVersion = Convert.ToInt32(context.Connection.ServerVersion.Split('.')[0]);
Database db = DatabaseFactory.Create(context);
//根据SQL Server版本选择分页语句的写法
if (serverVersion > 8)
{
ds = db.ExecuteDataSet(CommandType.Text, sql, "RESULT", "RESULT_COUNT");
}
else
{
ds = db.ExecuteDataSet("CommonSplitPageQuery",
qc.SelectFields,
qc.FromClause,
qc.WhereClause,
qc.OrderByClause,
qc.PrimaryKey,
qc.RowIndex / qc.PageSize + 1,
qc.PageSize,
1);
}
return(ds);
}
}
//public SearchOrgChildrenCondition(string strRootGuids, string strAttrs, DataAccess da)
//{
// InitSearchOrgChildrenCondition(strRootGuids, strAttrs, da);
//}
/// <summary>
/// 构造函数（构造数据对象查询条件）
/// </summary>
/// <param name="strRootValues">指定的机构标识（多个之间采用“,”分隔）</param>
/// <param name="soc">查询要求的查询列名称
/// （ＧＵＩＤ、ＵＳＥＲ＿ＧＵＩＤ、ＬＯＧＯＮ＿ＮＡＭＥ、ＯＲＩＧＩＮＡＬ＿ＳＯＲＴ、ＧＬＯＢＡＬ＿ＳＯＲＴ、ＡＬＬ＿ＰＡＴＨ＿ＮＡＭＥ）</param>
/// <param name="strAttrs">要求获取的属性</param>
public SearchOrgChildrenCondition(string strRootValues, SearchObjectColumn soc, string strAttrs)
{
using (DbContext context = DbContext.GetContext(CommonResource.AccreditConnAlias))
{
string strRootGuids = string.Empty;
if (strRootValues.Length > 0 && soc != SearchObjectColumn.SEARCH_GUID)
{
Database database = DatabaseFactory.Create(context);
DataSet ds = OGUReader.GetObjectsDetail("ORGANIZATIONS",
strRootValues,
soc,
string.Empty,
SearchObjectColumn.SEARCH_NULL,
string.Empty);
foreach (DataRow row in ds.Tables[0].Rows)
{
if (strRootGuids.Length > 0)
{
strRootGuids += ",";
}
strRootGuids += row["GUID"].ToString();
}
}
else
{
strRootGuids = strRootValues;
}
InitSearchOrgChildrenCondition(strRootGuids, strAttrs, string.IsNullOrEmpty(strRootValues) ? true : false);
}
}
public IEnumerable <ReferenceHistoryEntry> GetReferenceHistoryEntries(string objId)
{
ReferenceHistoryEntryCollection list = new ReferenceHistoryEntryCollection();
WhereSqlClauseBuilder where1 = new WhereSqlClauseBuilder();
where1.AppendItem("R.ObjectID", objId);
WhereSqlClauseBuilder where2 = new WhereSqlClauseBuilder();
where2.AppendItem("R.ParentID", objId);
var sql = string.Format(@"SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName
FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ParentID = O.ID
WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {0}
UNION
SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName
FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ObjectID = O.ID
WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {1} ORDER BY VersionStartTime ASC
", where1.ToSqlString(TSqlBuilder.Instance), where2.ToSqlString(TSqlBuilder.Instance));
using (var context = DbHelper.GetDBContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
var cmd = db.GetSqlStringCommand(sql);
using (var dr = db.ExecuteReader(cmd))
{
ORMapping.DataReaderToCollection(list, dr);
}
}
return(list);
}
/// <summary>
/// 获取与指定对象关联的条件历史
/// </summary>
/// <param name="id">对象ID</param>
/// <param name="type">类型，如果为<see langword="null"/>则不限。</param>
/// <returns></returns>
public IEnumerable <SCCondition> GetConditionHistoryEntries(string id, string type)
{
SCConditionCollection entries = new SCConditionCollection();
using (var context = DbHelper.GetDBContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("OwnerID", id);
if (type != null)
{
builder.AppendItem("Type", type);
}
var sql = @"SELECT * FROM [SC].[Conditions] WHERE " + builder.ToSqlString(TSqlBuilder.Instance) + " ORDER BY [VersionEndTime] DESC, SortID ASC";
var cmd = db.GetSqlStringCommand(sql);
using (var dr = db.ExecuteReader(cmd))
{
ORMapping.DataReaderToCollection(entries, dr);
}
return(entries);
}
}
public void Test()
{
Database db = DatabaseFactory.Create("LocalSQL");
DataSet dataset = db.ExecuteDataSet("SELECT * FROM Shippers");
DbTraceHelper.TraceData(dataset);
}
/// <summary>
/// 保持SignInInfo信息
/// </summary>
/// <param name="signInInfo">SignInInfo信息</param>
/// <remarks>
/// <code source="..\Framework\TestProjects\DeluxeWorks.Library.Passport.Test\DataObjectsTest.cs" region="SignInInfoPersistTest" lang="cs" title="保存SignInInfo对象" />
/// </remarks>
public void SaveSignInInfo(ISignInInfo signInInfo)
{
ORMappingItemCollection mapping = LoadMappingFromResource("MCS.Library.Passport.DataObjects.SignInInfoMapping.xml", typeof(ISignInInfo));
string sql = string.Format("UPDATE PASSPORT_SIGNIN_INFO SET {0} WHERE {1}",
ORMapping.GetUpdateSqlClauseBuilder(signInInfo, mapping).ToSqlString(TSqlBuilder.Instance),
ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(signInInfo, mapping).ToSqlString(TSqlBuilder.Instance));
using (TransactionScope scope = TransactionScopeFactory.Create())
{
using (DbContext context = DbContext.GetContext(DataAdapter.DBConnectionName))
{
Database db = DatabaseFactory.Create(DataAdapter.DBConnectionName);
if (db.ExecuteNonQuery(CommandType.Text, sql) == 0)
{
sql = string.Format("INSERT INTO PASSPORT_SIGNIN_INFO {0}",
ORMapping.GetInsertSqlClauseBuilder(signInInfo, mapping).ToSqlString(TSqlBuilder.Instance));
db.ExecuteNonQuery(CommandType.Text, sql);
}
}
scope.Complete();
}
}
/// <summary>
/// 多条采购单存在同主商品且有一条待调价的记录
/// 调价审核通过时同步更新未提交的采购单中的商品采购价
/// </summary>
/// <param name="realGoodsIds">子商品列表</param>
/// <param name="companyId">主商品绑定公司</param>
/// <param name="price"> </param>
/// <param name="warehouseId"></param>
/// <param name="hostingFilialeId"></param>
/// <returns></returns>
public bool UpdatePurchasingDetailPrice(IEnumerable <Guid> realGoodsIds, Guid companyId, decimal price, Guid warehouseId, Guid hostingFilialeId)
{
var builder = new StringBuilder(@"update lmShop_PurchasingDetail set Price=@Price,CPrice=@Price
where PurchasingID in(select PurchasingID from lmShop_Purchasing where PurchasingState=0 and CompanyID=@CompanyID and WarehouseID=@WarehouseId and PurchasingFilialeId=@PurchasingFilialeId) and GoodsID in( ");
var realGoodsIdStr = new StringBuilder("");
var parms = new[]
{
new Parameter("@Price", price),
new Parameter("@CompanyID", companyId),
new Parameter("@WarehouseId", warehouseId),
new Parameter("@PurchasingFilialeId", hostingFilialeId),
};
foreach (var id in realGoodsIds)
{
if (id != Guid.Empty)
{
if (realGoodsIdStr.Length == 0)
{
realGoodsIdStr.Append(id);
}
else
{
realGoodsIdStr.Append(",").Append(id);
}
}
}
builder.Append("select id as RealGoodsId from splitToTable('" + realGoodsIdStr + "',','))");
using (var db = DatabaseFactory.Create())
{
return(db.Execute(false, builder.ToString(), parms));
}
}
public JobCollection UPDLOCKLoadJobs(int batchCount, IConnectiveSqlClause whereClause)
{
JobCollection result = new JobCollection();
if (whereClause.IsEmpty == false)
{
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
string top = batchCount >= 0 ? "TOP " + batchCount : string.Empty;
Database db = DatabaseFactory.Create(context);
using (IDataReader dr = db.ExecuteReader(CommandType.Text,
string.Format(UPDLOCK_LOAD_JOBS, top,
whereClause.AppendTenantCodeSqlClause(typeof(JobBase)).ToSqlString(TSqlBuilder.Instance))))
{
ORMapping.DataReaderToCollection(result, dr);
}
}
AfterLoad(result);
}
return(result);
}
/// <summary>
/// 修改采购单商品价格，排除赠品
/// </summary>
/// <param name="purchasingId"></param>
/// <param name="realGoodsIds"></param>
/// <param name="price"></param>
/// <param name="cprice"></param>
/// <returns></returns>
public bool UpdateDetailsPrice(Guid purchasingId, List <Guid> realGoodsIds, decimal price, decimal cprice)
{
if (realGoodsIds.Count > 0)
{
var strbSql = new StringBuilder();
strbSql.Append("UPDATE lmShop_PurchasingDetail SET Price=").Append(price).Append(",CPrice=").Append(cprice);
strbSql.Append(" WHERE Price!=0 AND PurchasingID='").Append(purchasingId).Append("'");
var strbRealGoodsIds = new StringBuilder();
foreach (var id in realGoodsIds)
{
if (id != Guid.Empty)
{
if (strbRealGoodsIds.Length == 0)
{
strbRealGoodsIds.Append(id);
}
else
{
strbRealGoodsIds.Append(",").Append(id);
}
}
}
strbSql.Append(" AND GoodsId IN (select id as RealGoodsId from splitToTable('" + strbRealGoodsIds + "',','))");
using (var db = DatabaseFactory.Create())
{
return(db.Execute(false, strbSql.ToString()));
}
}
return(false);
}
public JobBase LoadOneUnTaskedJob(string jobID)
{
string sqlTemplate = "SELECT J.* FROM WF.JOBS J WITH(UPDLOCK READPAST) LEFT JOIN WF.SYS_NOT_RUNNING_TASK T WITH(UPDLOCK READPAST) ON J.JOB_ID = T.RESOURCE_ID WHERE (T.STATUS IS NULL) AND J.JOB_ID = {0}";
string sql = string.Format(sqlTemplate, TSqlBuilder.Instance.CheckUnicodeQuotationMark(jobID));
JobBase result = null;
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
using (IDataReader dr = db.ExecuteReader(CommandType.Text, sql))
{
while (dr.Read())
{
JobBase job = new JobBase();
ORMapping.DataReaderToObject(dr, job);
result = job;
}
}
if (result != null)
{
JobScheduleAdapter.Instance.FillJobSchdules(result);
}
}
return(result);
}
/// <summary>获取公司对应具体供应商采购索票额度集合
/// </summary>
/// <param name="filialeId">公司ID</param>
/// <param name="dateYear">年份</param>
/// <param name="dateMonth">月份</param>
/// <param name="pageIndex">当前页码 </param>
/// <param name="pageSize"> 每页显示条数</param>
/// <param name="totalCount">总记录数 </param>
/// <returns>Return:供应商采购索票额度集合</returns>
public IList <ProcurementTicketLimitInfo> GetProcurementTicketLimitDetailList(Guid filialeId, int dateYear, int dateMonth, int pageIndex, int pageSize, out int totalCount)
{
const string SQL = @"
SELECT
CC.CompanyId,CC.CompanyName,
ISNULL(PTL.TakerTicketLimit,0) AS TakerTicketLimit,ISNULL(FilialeId,'00000000-0000-0000-0000-000000000000') AS FilialeId
FROM lmShop_CompanyCussent AS CC
LEFT JOIN
(
SELECT CompanyId,TakerTicketLimit,FilialeId FROM [ProcurementTicketLimit]
WHERE DateYear=@DateYear AND DateMonth=@DateMonth
{0}{1}
)
AS PTL ON CC.CompanyId=PTL.CompanyId
WHERE CC.CompanyId
IN(SELECT CompanyId FROM [CompanyBindingFiliale] {2} {1} GROUP BY CompanyId)";
var parms = new[] {
new Parameter("@DateYear", dateYear),
new Parameter("@DateMonth", dateMonth),
new Parameter("@FilialeId", filialeId)
};
using (var db = DatabaseFactory.Create())
{
//var pageQuery = new Keede.DAL.Helper.Sql.PageQuery(pageIndex, pageSize, string.Format(SQL,filialeId!=Guid.Empty?" AND ":"",
// filialeId != Guid.Empty ? " FilialeId=@FilialeId " : "", filialeId != Guid.Empty ? " WHERE " : ""), " CompanyName ASC");
var pageItem = db.Select <ProcurementTicketLimitInfo>(true, string.Format(SQL, filialeId != Guid.Empty ? " AND " : "",
filialeId != Guid.Empty ? " FilialeId=@FilialeId " : "", filialeId != Guid.Empty ? " WHERE " : ""), parms);
var procurementTicketLimitInfos = pageItem as ProcurementTicketLimitInfo[] ?? pageItem.ToArray();
totalCount = pageItem != null?procurementTicketLimitInfos.Count() : 0;
return(procurementTicketLimitInfos.ToList());
}
}
/// <summary>
/// 根据TaskGuid得到UserAccomplishedTask
/// </summary>
/// <param name="strTaskGuid"></param>
/// <returns></returns>
public UserTaskCollection GetUserAccomplishedTasks(params string[] strTaskGuid)
{
UserTaskCollection utc = new UserTaskCollection();
if (strTaskGuid.Length > 0)
{
InSqlClauseBuilder builder = new InSqlClauseBuilder();
builder.AppendItem(strTaskGuid);
string strSql = "SELECT * FROM WF.USER_ACCOMPLISHED_TASK WHERE (TASK_GUID " + builder.ToSqlStringWithInOperator(TSqlBuilder.Instance) + ")";
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
Database db = DatabaseFactory.Create(dbi);
using (IDataReader dr = db.ExecuteReader(CommandType.Text, strSql))
{
while (dr.Read())
{
UserTask ut = new UserTask();
ORMapping.DataReaderToObject(dr, ut);
utc.Add(ut);
}
}
}
}
return(utc);
}
/// <summary>
/// 根据条件返回GoodsPriceChange表的所有数据
/// </summary>
/// <param name="saleFilialeId">公司ID</param>
/// <param name="salePlatformId">销售平台ID</param>
/// <param name="goodsName">商品名称</param>
/// <param name="goodsId">商品编号</param>
/// <param name="type">0:销售价；1:加盟价；2:批发价</param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total">数据总数</param>
/// <returns></returns>
public List <GoodsPriceChange> GetAllGoodsPriceChange(Guid?saleFilialeId, Guid?salePlatformId, string goodsName, Guid goodsId, int type, int pageIndex, int pageSize, out int total)
{
var sqlsb = new StringBuilder(SQL_SELECT + " where 1=1");
if (!string.IsNullOrEmpty(saleFilialeId.ToString()) && saleFilialeId != Guid.Empty)
{
sqlsb.AppendFormat(@" AND [SaleFilialeId]='{0}'", saleFilialeId);
}
if (!string.IsNullOrEmpty(salePlatformId.ToString()) && salePlatformId != Guid.Empty)
{
sqlsb.AppendFormat(@" AND [SalePlatformId]='{0}'", salePlatformId);
}
if (!string.IsNullOrEmpty(goodsName))
{
sqlsb.AppendFormat(@" AND [GoodsName]='{0}'", goodsName);
}
if (goodsId != Guid.Empty)
{
sqlsb.AppendFormat(@" AND [GoodsId]='{0}'", goodsId);
}
if (type != -1)
{
sqlsb.AppendFormat(@" AND [Type]='{0}'", type);
}
using (var db = DatabaseFactory.Create())
{
var list = new Keede.DAL.Helper.Sql.PageQuery(pageIndex, pageSize, sqlsb.ToString(), " Datetime DESC");
var pageItem = db.SelectByPage <GoodsPriceChange>(true, list);
total = (int)pageItem.RecordCount;
return(pageItem.Items.ToList());
}
}
public void Test_Initialization()
{
var dbConfig = new DbConfig()
{
ConnectionString =
string.Format("Data Source= " +
"(DESCRIPTION =" +
"(ADDRESS = (PROTOCOL = TCP)(HOST = {0} )(PORT = {1}))" +
"(CONNECT_DATA = (SID = {2})));User Id={3};Password={4};",
"localhost", "1521", "xe", "easylink", "1197344"),
DatabaseType = DatabaseType.Oracle,
SchemaName = "EASYLINK",
AuditRecordType = typeof(AuditRecord)
};
DatabaseFactory.Initialize(dbConfig);
database = DatabaseFactory.Create();
Mapping.SetNextId <Employee>(NextIdOption.Sequence, "EMPLOYEE_ID_SEQ");
Mapping.SetNextId <Program>(NextIdOption.Sequence, "PROGRAM_ID_SEQ");
Mapping.SetNextId <EmployeeProgram>(NextIdOption.Sequence, "EMPLOYEE_PROGRAM_ID_SEQ");
Mapping.SetNextId <Address>(NextIdOption.Sequence, "ADDRESS_ID_SEQ");
Mapping.SetNextId <FinancialInfo>(NextIdOption.Sequence, "FINANCIAL_INFO_ID_SEQ");
Mapping.SetNextId <AdditionalInfo>(NextIdOption.Sequence, "ADDITIONAL_INFO_ID_SEQ");
Mapping.SetNextId <Lookup>(NextIdOption.Sequence, "LOOKUP_ID_SEQ");
Mapping.SetNextId <AuditRecord>(NextIdOption.Sequence, "AUDIT_ID_SEQ");
}
/// <summary>
/// 删除sessionID相关的SignInInfo信息
/// </summary>
/// <param name="sessionID">sessionID</param>
/// <remarks>
/// <code source="..\Framework\TestProjects\DeluxeWorks.Library.Passport.Test\DataObjectsTest.cs" region="SignInInfoPersistTest" lang="cs" title="删除SignInInfo对象" />
/// </remarks>
public void DeleteRelativeSignInInfo(string sessionID)
{
ExceptionHelper.CheckStringIsNullOrEmpty(sessionID, "sessionID");
StringBuilder strB = new StringBuilder(256);
strB.Append(TSqlBuilder.Instance.DBStatementBegin);
strB.AppendFormat("DELETE PASSPORT_SIGNIN_INFO WHERE SIGNIN_ID = {0}" +
TSqlBuilder.Instance.DBStatementSeperator +
"DELETE PASSPORT_TICKET WHERE SIGNIN_ID = {0}",
TSqlBuilder.Instance.CheckQuotationMark(sessionID, true));
strB.Append(TSqlBuilder.Instance.DBStatementEnd);
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
{
using (DbContext context = DbContext.GetContext(DataAdapter.DBConnectionName))
{
Database db = DatabaseFactory.Create(DataAdapter.DBConnectionName);
db.ExecuteNonQuery(CommandType.Text, strB.ToString());
}
scope.Complete();
}
}
public override void DeleteOperation()
{
//1删除组织表
DataView organizations = new DataView(this.Context.OriginalData.Tables["ORGANIZATIONS"], string.Empty, "GUID", DataViewRowState.Deleted);
Database db = DatabaseFactory.Create(this.Context.InitialParams.AccreditAdminConnectionName);
foreach (DataRowView drv in organizations)
{
try
{
string organization_delete = string.Format("DELETE ORGANIZATIONS WHERE GUID = '{0}'", drv["GUID"].ToString());
int count = db.ExecuteNonQuery(CommandType.Text, organization_delete);
if (count > 0)
{
this.DeleteCount++;
this.Context.InitialParams.Log.Write(string.Format("ORGANIZATIONS表GUID 是{0}的记录删除成功，ALL_PATH_NAME是'{1}'",
drv["GUID"].ToString(), drv["ALL_PATH_NAME"].ToString()));
}
}
catch (Exception ex)
{
this.Context.InitialParams.Log.Write(string.Format("ORGANIZATIONS表GUID 是{0}的记录执行删除时出错,错误是{1}",
drv["GUID"].ToString(), ex.Message));
}
}
}
/// <summary>
/// 保存OpenIDBinding
/// </summary>
/// <param name="binding"></param>
public void SaveOpenIDBinding(OpenIDBinding binding)
{
binding.NullCheck("binding");
string sql = ORMapping.GetInsertSql(binding, TSqlBuilder.Instance);
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
{
using (DbContext context = DbContext.GetContext(DataAdapter.DBConnectionName))
{
Database db = DatabaseFactory.Create(DataAdapter.DBConnectionName);
try
{
db.ExecuteNonQuery(CommandType.Text, sql);
}
catch (SqlException ex)
{
if (ex.Number == 2627)
{
throw new OpenIDBindingException(Translator.Translate(Define.DefaultCategory, "OpenID已经绑定到某用户了，不能重复绑定"));
}
throw;
}
}
scope.Complete();
}
}
public void RegisterBot_ExistingBot_ShouldReturnDuplicatedIdError()
{
var databaseContext = DatabaseFactory.Create();
var timeProvider = TimeProviderFactory.Create();
var botService = new BotsService(databaseContext, timeProvider);
databaseContext.Bots.Add(new BotModel
{
Id = "1000",
Name = "Some other bot",
CreateTime = DateTime.Now
});
databaseContext.SaveChanges();
var bot = new RegisterBotDto
{
Id = "1000",
Name = "Bot1"
};
var result = botService.RegisterBot(bot);
Assert.Equal(RegisterResult.DuplicatedIdError, result);
Assert.Equal(1, databaseContext.Bots.Count());
}
/// <summary>
///
/// </summary>
/// <param name="openID"></param>
/// <returns></returns>
public OpenIDBinding GetBindingByOpenID(string openID)
{
ExceptionHelper.CheckStringIsNullOrEmpty(openID, "openID");
ORMappingItemCollection mappings = ORMapping.GetMappingInfo(typeof(OpenIDBinding));
string sql = string.Format("SELECT * FROM {0} WHERE OPEN_ID = {1}",
mappings.TableName,
TSqlBuilder.Instance.CheckUnicodeQuotationMark(openID));
DataTable table;
using (DbContext context = DbContext.GetContext(DataAdapter.DBConnectionName))
{
Database db = DatabaseFactory.Create(DataAdapter.DBConnectionName);
table = db.ExecuteDataSet(CommandType.Text, sql).Tables[0];
}
OpenIDBinding binding = null;
if (table.Rows.Count > 0)
{
binding = new OpenIDBinding();
ORMapping.DataRowToObject(table.Rows[0], binding);
}
return(binding);
}
public static string[] QueryGuidsByCondition(IConnectiveSqlClause condition)
{
QueryCondition qc = new QueryCondition();
qc.FromClause = "WF.PROCESS_INSTANCES (NOLOCK)";
qc.SelectFields = "INSTANCE_ID";
qc.OrderByClause = "CREATE_TIME DESC";
qc.WhereClause = condition.ToSqlString(TSqlBuilder.Instance);
string sql = string.Format("SELECT {0} FROM {1} WHERE 1 = 1 {2} {3} ORDER BY {4}",
qc.SelectFields,
qc.FromClause,
qc.WhereClause.IsNotEmpty() ? " AND " + qc.WhereClause : string.Empty,
qc.GroupBy.IsNotEmpty() ? "GROUP BY " + qc.GroupBy : string.Empty,
qc.OrderByClause);
using (DbContext context = DbContext.GetContext(WorkflowSettings.GetConfig().ConnectionName))
{
Database db = DatabaseFactory.Create(context);
using (var dr = db.ExecuteReader(CommandType.Text, sql))
{
List <string> guids = new List <string>();
while (dr.Read())
{
guids.Add(dr.GetString(0));
}
return(guids.ToArray());
}
}
}
static void Main(string[] args)
{
DatabaseFactory.AddProviderFactory(System.Data.SqlClient.SqlClientFactory.Instance, DatabaseType.SqlServer);
Dictionary <string, GetHandler> cached = new Dictionary <string, GetHandler>();
//using (var db = DatabaseFactory.Create("ZDATA"))
//{
// for (int i = 0; i < 1000000; i++)
// {
// db.CreateCommand("insert into ")
// }
//}
var item = new Item
{
Id = 1,
Name = "abc",
Age = 10
};
using (var db = DatabaseFactory.Create("test"))
{
var commd = db.CreateCommand("select * from Item where id=@id", new { id = 1 });
}
//create insert sql
}