private static void BuildJoinCondition(SqlQuery query, SqlQueryJoin join, QueryConditionDef condition, DocDef docDef, SqlQueryCondition parentCondition, IDataContext dataContext)
{
if (condition.Condition != ConditionOperation.Include && condition.Condition != ConditionOperation.Exp)
{
var leftPart = BuildConditionPart(query, condition.Left, dataContext);
var rightPart = BuildConditionPart(query, condition.Right, dataContext);
join.Conditions.Add(new SqlQueryCondition(condition.Operation, leftPart, condition.Condition, rightPart));
}
else if (condition.Condition == ConditionOperation.Include && condition.Conditions != null && condition.Conditions.Count > 0)
{
var attrDef = String.IsNullOrEmpty(condition.AttributeName)
? docDef.Attributes.First(a => a.Id == condition.AttributeId)
: docDef.Attributes.First(
a => String.Equals(a.Name, condition.AttributeName, StringComparison.OrdinalIgnoreCase));
query.JoinSource(query.FindSourceByDocDef(docDef), attrDef.DocDefType.Id, SqlSourceJoinType.Inner, attrDef.Id);
foreach (var child in condition.Conditions)
{
BuildJoinCondition(query, join, child, attrDef.DocDefType.Id, parentCondition, dataContext);
}
}
else if (condition.Condition == ConditionOperation.Exp && condition.Conditions != null)
{
var exp = new SqlQueryCondition(null, condition.Operation, Guid.Empty, ConditionOperation.Exp, null);
foreach (var child in condition.Conditions)
{
BuildJoinCondition(query, join, child, docDef, exp, dataContext);
}
}
}
public static string UpdatePassword(string oldpwd, string newpwd, int userid)
{
SHA256CryptoServiceProvider sha256crypto = new SHA256CryptoServiceProvider();
oldpwd = CommonMethods.Decrypt(oldpwd);
byte[] b = sha256crypto.ComputeHash(Encoding.UTF8.GetBytes(oldpwd));
oldpwd = CommonMethods.BytesToHexString(b);
SqlQueryCondition sqlcondition1 = new SqlQueryCondition(UserField.Id, SqlQueryConditionOperator.Equal, userid);
SqlQueryCondition sqlcondition2 = new SqlQueryCondition(UserField.Password, SqlQueryConditionOperator.Equal, oldpwd);
if (UserDAO.Select(new UserField[] { UserField.Id },
new SqlQueryCondition(new SqlQueryCondition[] { sqlcondition1, sqlcondition2 }, SqlQueryLogicalOperator.And)).Length == 0) //学号不存在或者状态码或密码不正确
{
return("原密码不正确!");
}
else
{
newpwd = CommonMethods.Decrypt(newpwd);
if (newpwd.Length < 8 || newpwd.Length > 16)
{
return("新密码长度不符合要求！");
}
b = sha256crypto.ComputeHash(Encoding.UTF8.GetBytes(newpwd));
newpwd = CommonMethods.BytesToHexString(b);
UserDAO.Update(new UserField[] { UserField.Password }, new object[] { newpwd }, sqlcondition1);
UserLogDAO.Insert(userid, "updpwd{}");
return("true");
}
}
public static string SignOut(string sno, string sequence, string remark)
{
if (!ScheduleField.Sequence.TestValue(sequence) || !ScheduleField.SignOutRemark.TestValue(remark))
{
return("数据格式或长度错误 请联系管理员！");
}
DateTime nowtime = DateTime.Now;
SqlQueryCondition[] sqlconditions1 = new SqlQueryCondition[] {
new SqlQueryCondition(UserField.StudentNmber, SqlQueryConditionOperator.Equal, sno),
new SqlQueryCondition(UserField.Status, SqlQueryConditionOperator.Equal, 1)
};
object[,] user = UserDAO.Select(new UserField[] { UserField.Id, UserField.Name }, new SqlQueryCondition(sqlconditions1, SqlQueryLogicalOperator.And));
if (user.Length == 0)
{
return("未找到该用户的信息，请检查学号是否填写正确！");
}
SqlQueryCondition[] sqlconditions2 = new SqlQueryCondition[] {
new SqlQueryCondition(ScheduleField.Date, SqlQueryConditionOperator.Equal, nowtime),
new SqlQueryCondition(ScheduleField.Sequence, SqlQueryConditionOperator.Equal, sequence),
new SqlQueryCondition(ScheduleField.Attendant, SqlQueryConditionOperator.Equal, user[0, 0])
};
object[,] schedule = ScheduleDAO.Select(new ScheduleField[] { ScheduleField.Id, ScheduleField.SignInTime, ScheduleField.SignOutTime },
new SqlQueryCondition(sqlconditions2, SqlQueryLogicalOperator.And));
if (schedule.Length == 0)
{
return("未找到你的值班信息，请检查班次是否错误！");
}
if (schedule[0, 1] == DBNull.Value)
{
return("你还未进行入站签到，请先入站签到！");
}
if (schedule[0, 2] != DBNull.Value)
{
return("你已对此班签出！");
}
if (nowtime.TimeOfDay < dutytimes[sequence].EndTime - aheadtime)
{
return("此时签出太早了，请晚点再来！");
}
if (nowtime.TimeOfDay > dutytimes[sequence].EndTime + gracetime)
{
return("你来晚了，签出时间已过！");
}
ScheduleDAO.Update(new ScheduleField[] { ScheduleField.SignOutTime, ScheduleField.SignOutRemark }, new object[] { nowtime.TimeOfDay, remark },
new SqlQueryCondition(ScheduleField.Id, SqlQueryConditionOperator.Equal, schedule[0, 0]));
UserLogDAO.Insert((int)user[0, 0], "signout{" + sequence + '\r' + remark + "}");
return("true\n" + user[0, 1].ToString() + " 第" + sequence + "班 " + nowtime.ToLongTimeString() + "签出");
}
public static string DeleteTimetable(string timetableid, int userid)
{
if (!TimetableField.Id.TestValue(timetableid))
{
return("");
}
SqlQueryCondition[] sqlconditions = new SqlQueryCondition[] {
new SqlQueryCondition(TimetableField.Id, SqlQueryConditionOperator.Equal, timetableid),
new SqlQueryCondition(TimetableField.Status, SqlQueryConditionOperator.NotEqual, 1),
new SqlQueryCondition(TimetableField.Owner, SqlQueryConditionOperator.Equal, userid) //加入此条件防止非法更改他人课表
};
TimetableDAO.Delete(new SqlQueryCondition(sqlconditions, SqlQueryLogicalOperator.And));
UserLogDAO.Insert(userid, "deltb{" + timetableid + "}");
return("true");
}
public SqlQueryCondition AddCondition(ExpressionOperation operation, SqlQuerySource source1, string attrDefName1, ConditionOperation condition, SqlQuerySource source2, string attrDefName2, SqlQueryCondition parentCondition = null)
{
var item = new SqlQueryCondition(operation, source1, attrDefName1, condition, source2, attrDefName2);
if (parentCondition == null)
{
Conditions.Add(item);
}
else
{
parentCondition.Conditions.Add(item);
}
return(item);
}
public SqlQueryCondition AddCondition(ExpressionOperation operation, SqlQuerySource source, string attrDefName, ConditionOperation condition, object value, SqlQueryCondition parentCondition = null)
{
var item = new SqlQueryCondition(source, operation, attrDefName, condition, value);
if (parentCondition == null)
{
Conditions.Add(item);
}
else
{
parentCondition.Conditions.Add(item);
}
return(item);
}
public static string UpdateTimetableData(string timetableid, string newdata, int userid)
{
if (!TimetableField.Id.TestValue(timetableid) || !TimetableField.Data.TestValue(newdata))
{
return("数据错误 请联系管理员！");
}
SqlQueryCondition[] sqlconditions = new SqlQueryCondition[] {
new SqlQueryCondition(TimetableField.Owner, SqlQueryConditionOperator.Equal, userid), //加入此条件防止非法更改他人课表
new SqlQueryCondition(TimetableField.Id, SqlQueryConditionOperator.Equal, timetableid)
};
TimetableDAO.Update(new TimetableField[] { TimetableField.Data }, new object[] { newdata },
new SqlQueryCondition(sqlconditions, SqlQueryLogicalOperator.And));
UserLogDAO.Insert(userid, "updtb{" + timetableid + "}");
return("true");
}
public static object[,] GetTimetableData(string timetableid, int userid)
{
if (!TimetableField.Id.TestValue(timetableid))
{
return new object[, ] {
{ "" }
}
}
;
SqlQueryCondition[] sqlconditions = new SqlQueryCondition[] {
new SqlQueryCondition(TimetableField.Id, SqlQueryConditionOperator.Equal, timetableid),
new SqlQueryCondition(TimetableField.Owner, SqlQueryConditionOperator.Equal, userid) //加入此条件防止非法查看他人课表
};
UserLogDAO.Insert(userid, "gettbdat{" + timetableid + "}");
return(TimetableDAO.Select(new TimetableField[] { TimetableField.Data }, new SqlQueryCondition(sqlconditions, SqlQueryLogicalOperator.And)));
}
public static string AddTimetable(string timetablename, string data, int userid)
{
if (!TimetableField.Name.TestValue(timetablename) || !TimetableField.Data.TestValue(data))
{
return("数据错误 请联系管理员！");
}
SqlQueryCondition[] sqlconditions = new SqlQueryCondition[] {
new SqlQueryCondition(TimetableField.Owner, SqlQueryConditionOperator.Equal, userid),
new SqlQueryCondition(TimetableField.Name, SqlQueryConditionOperator.Equal, timetablename)
};
if (TimetableDAO.Select(new TimetableField[] { TimetableField.Id }, new SqlQueryCondition(sqlconditions, SqlQueryLogicalOperator.And)).Length != 0)
{
return("课表名重复！");
}
else
{
UserLogDAO.Insert(userid, "addtb{" + timetablename + "}");
return("true:" + TimetableDAO.Insert(timetablename, userid, data)); //加上id一起返回
}
}
public static string ActiveTimetable(string timetableid, int userid)
{
if (!TimetableField.Id.TestValue(timetableid))
{
return("");
}
SqlQueryCondition sqlcondition1 = new SqlQueryCondition(TimetableField.Id, SqlQueryConditionOperator.Equal, timetableid);
SqlQueryCondition sqlcondition2 = new SqlQueryCondition(TimetableField.Status, SqlQueryConditionOperator.Equal, 1);
SqlQueryCondition sqlcondition3 = new SqlQueryCondition(TimetableField.Owner, SqlQueryConditionOperator.Equal, userid); //加入此条件防止非法更改他人课表
if (TimetableDAO.Select(new TimetableField[] { TimetableField.Id },
new SqlQueryCondition(new SqlQueryCondition[] { sqlcondition1, sqlcondition3 }, SqlQueryLogicalOperator.And)).Length != 0)
{
TimetableDAO.Update(new TimetableField[] { TimetableField.Status }, new object[] { 0 },
new SqlQueryCondition(new SqlQueryCondition[] { sqlcondition2, sqlcondition3 }, SqlQueryLogicalOperator.And));
TimetableDAO.Update(new TimetableField[] { TimetableField.Status }, new object[] { 1 },
new SqlQueryCondition(new SqlQueryCondition[] { sqlcondition1 }, SqlQueryLogicalOperator.And));
}
UserLogDAO.Insert(userid, "acttb{" + timetableid + "}");
return("true");
}
public static string RenameTimetable(string timetableid, string newname, int userid)
{
if (!TimetableField.Id.TestValue(timetableid) || !TimetableField.Name.TestValue(newname))
{
return("数据错误 请联系管理员！");
}
SqlQueryCondition sqlcondition1 = new SqlQueryCondition(TimetableField.Owner, SqlQueryConditionOperator.Equal, userid); //加入此条件防止非法更改他人课表
SqlQueryCondition sqlcondition2 = new SqlQueryCondition(TimetableField.Id, SqlQueryConditionOperator.Equal, timetableid);
SqlQueryCondition sqlcondition3 = new SqlQueryCondition(TimetableField.Name, SqlQueryConditionOperator.Equal, newname);
if (TimetableDAO.Select(new TimetableField[] { TimetableField.Id },
new SqlQueryCondition(new SqlQueryCondition[] { sqlcondition1, sqlcondition3 }, SqlQueryLogicalOperator.And)).Length != 0)
{
return("课表名重复！");
}
else
{
TimetableDAO.Update(new TimetableField[] { TimetableField.Name }, new object[] { newname },
new SqlQueryCondition(new SqlQueryCondition[] { sqlcondition1, sqlcondition2 }, SqlQueryLogicalOperator.And));
UserLogDAO.Insert(userid, "rntb{" + timetableid + '\r' + newname + "}");
return("true");
}
}
private static void BuildJoinCondition(SqlQuery query, SqlQueryJoin join, QueryConditionDef condition, Guid docDefId, SqlQueryCondition parentCondition, IDataContext dataContext)
{
// using (var defRepo = new DocDefRepository(dataContext))
var defRepo = query.Provider.Get <IDocDefRepository>();
{
var docDef = defRepo.DocDefById(docDefId);
BuildJoinCondition(query, join, condition, docDef, parentCondition, dataContext);
}
}
private static void BuildCondition(SqlQuery query, ReportDef report, ReportConditionItemDef item, SqlQueryCondition parentCondition, IDictionary <Guid, SqlQuerySource> mapper, bool isHaving)
{
var condition = item as ReportConditionDef;
if (condition != null)
{
var source = mapper[condition.LeftAttribute.SourceId];
var reportSource = report.GetSourceDef(condition.LeftAttribute.SourceId);
var reportSourceAttr = reportSource.Attributes != null
? reportSource.Attributes.FirstOrDefault(a => a.Id == condition.LeftAttribute.AttributeId)
: null;
var attr = reportSourceAttr == null?source.GetAttribute(condition.LeftAttribute.AttributeId) : source.GetAttribute(reportSourceAttr.Ident);
var attrRef = new SqlQuerySourceAttributeRef(source, attr);
var leftPart = new SqlQueryConditionPart();
leftPart.Attributes.Add(attrRef);
var rightPart = BuildConditionPart(query, report, condition, mapper);
if (parentCondition != null)
{
parentCondition.Conditions.Add(new SqlQueryCondition(item.Operation, leftPart,
CompareOperationConverter.CompareToCondition(condition.Condition), rightPart));
}
else if (isHaving)
{
query.HavingConditions.Add(new SqlQueryCondition(item.Operation, leftPart,
CompareOperationConverter.CompareToCondition(condition.Condition),
rightPart));
}
else
{
query.Conditions.Add(new SqlQueryCondition(item.Operation, leftPart,
CompareOperationConverter.CompareToCondition(condition.Condition),
rightPart));
}
}
else
{
var expCondition = item as ReportExpConditionDef;
if (expCondition != null && expCondition.Conditions != null)
{
var exp = query.AddExpCondition(item.Operation, parentCondition);
foreach (var child in expCondition.Conditions)
{
BuildCondition(query, report, child, exp, mapper, isHaving);
}
}
}
}
public override SqlBuilder BuildSql(ICollection <SqlQuerySourceAttribute> attrs,
ICollection <SqlQueryCondition> conditions, bool isMain = false)
{
var builder = new SqlBuilder();
var tableMap = Mapper != null?Mapper.Find(Source.Id) : null;
var needToJoinDocument = isMain || (tableMap != null && NeedToJoinDocument(tableMap));
var withOption = WithNoLock ? "WITH(NOLOCK)" : "";
var fromAlias = tableMap != null ? "t" : "d";
var orgAlias = (tableMap != null && !needToJoinDocument) ? "t" : "d";
var createdAlias = (tableMap != null && tableMap.FindIdentField("Created") != null) ? "t" : "d";
var modifiedAlias = (tableMap != null && tableMap.FindIdentField("Last_Modified") != null) ? "t" : "d";
var userAlias = (tableMap != null && tableMap.FindIdentField("User_Id") != null) ? "t" : "d";
var defAlias = (tableMap != null && tableMap.FindIdentField("Def_Id") != null) ? "t" : "d";
var tableMapToDocJoin = tableMap != null && tableMap.IsVirtual ? "LEFT OUTER" : "INNER";
var from = tableMap == null
? "Documents d " + withOption
: needToJoinDocument
? String.Format("{0} {1} JOIN Documents d {2} ON t.Id = d.Id", GetTableMapSql(tableMap, "t", withOption), tableMapToDocJoin, withOption)
: String.Format("{0}", GetTableMapSql(tableMap, "t", withOption));
// ? String.Format("[{0}] t {1} INNER JOIN Documents d {1} ON t.Id = d.Id", tableMap.TableName, withOption)
// : String.Format("[{0}] t {1}", tableMap.TableName, withOption);
// var from = "Documents d";
var orgRestriction = "";
// if (WithNoLock) from += " " + withOption;
if (UserId != Guid.Empty && !Source.IsPublic)
{
/*using (*/
//var userRepo = new UserRepository(DataContext);/*)*/
{
/* var userInfo = userRepo.GetUserInfo(UserId);
*
* from += " inner join Organizations o WITH(NOLOCK) on o.Id = d.Organization_Id";
* orgRestriction = String.Format(SourceOrgRestrictCondition, userInfo.OrgUnitTypeId,
* userInfo.OrganizationId, withOption);*/
var userOrgs = new List <Guid>(_userRepo.GetUserAccessOrgs(UserId));
if (userOrgs.Count > 0)
{
foreach (var orgId in userOrgs)
{
if (orgRestriction.Length > 0)
{
orgRestriction += ", ";
}
orgRestriction += String.Format("'{0}'", orgId);
}
orgRestriction =
String.Format(
userOrgs.Count == 1 ? "{0}.Organization_Id = {1}" : "{0}.Organization_Id IN ({1})",
orgAlias, orgRestriction);
}
}
}
builder.SetFrom(from);
builder.AddSelect(fromAlias + ".Id");
var usedConditions = new List <SqlQueryCondition>();
var attrIndexDictionary = new Dictionary <SqlQuerySourceAttribute, int>();
var no = 1;
if (attrs != null && Attributes != null)
{
foreach (var attr in attrs)
{
var i = no;
if (!Attributes.Contains(attr))
{
continue;
}
if (attrIndexDictionary.ContainsKey(attr))
{
continue;
}
attrIndexDictionary.Add(attr, i);
var attributeConditions =
new List <SqlQueryCondition>(GetQuerySourceAttributeConditions(attr, conditions));
var attrConditionStr = SqlQueryCondition.BuildConditions(attributeConditions, "[{0}{1}].[{2}]");
var hasCondition = HasQuerySourceAttributeConditions(attr, conditions);
var sb = new ScriptStringBuilder();
var attribute = attr;
if (attr.Def != null)
{
var attrTableField = tableMap != null
? tableMap.FindField(attribute.Def.Id) /*tableMap.Fields.FirstOrDefault(
* f => f.AttrDefId == attribute.Def.Id)*/
: null;
var attrDef = attr.Def;
if (attrDef.Type.Id == (short)CissaDataType.DocList)
{
if (String.IsNullOrEmpty(attrConditionStr))
{
builder.AddJoin(
SqlSourceJoinType.LeftOuter,
String.Format(SourceInnerJoinDocListAttr2, attrDef.Id, i, withOption, String.Empty));
}
else
{
builder.AddJoin(
SqlSourceJoinType.Inner,
String.Format(SourceInnerJoinDocListAttr2, attr.Def.Id, i, withOption,
String.Format(" and " + attrConditionStr, "dla", i, "Value")));
}
sb.AppendFormat("[dla{0}].[Value] as [{1}]", i, attr.AliasName);
}
else if (attrDef.Type.Id == (short)CissaDataType.Blob)
{
var tableName = attr.GetAttrDefTableName();
sb.AppendFormat("[a{0}].[File_Name] as [{1}]", i, attr.AliasName);
if (String.IsNullOrEmpty(attrConditionStr))
{
builder.AddJoin(
SqlSourceJoinType.LeftOuter,
String.Format(
"{0} a{1} {2} on (a{1}.Document_Id = {4}.Id and a{1}.Def_Id = '{3}' and a{1}.Expired = '99991231')",
tableName, i, withOption, attr.Def.Id, fromAlias));
}
else
{
var valueFieldName = attr.Def.Type.Id == (short)CissaDataType.Text ? "Upper_" : "";
builder.AddJoin(
SqlSourceJoinType.Inner,
String.Format(
"{0} a{1} {2} on (a{1}.Document_Id = {5}.Id and a{1}.Def_Id = '{3}' and a{1}.Expired = '99991231' and {4})",
tableName, i, withOption, attr.Def.Id,
String.Format(attrConditionStr, "a", i, valueFieldName + "Value"), fromAlias));
}
}
else if (attrDef.Type.Id == (short)CissaDataType.OrganizationOfDocument)
{
if (String.IsNullOrEmpty(attrConditionStr))
{
sb.AppendFormat("(select od{0}.Full_Name", i);
sb.AppendFormat(
" from Organizations o{0} {1} inner join Object_Defs od{0} {1} on od{0}.Id = o{0}.Id\n",
i,
withOption);
sb.AppendFormat(" where o{0}.Id = {2}.Organization_Id) as [{1}]", i,
attr.AliasName, orgAlias);
}
else
{
sb.AppendFormat("[od{0}].[Full_Name] as [{1}]", i, attr.AliasName);
builder.AddJoin(
SqlSourceJoinType.Inner,
String.Format("Organizations o{0} {1} on o{0}.[Id] = {2}.Organization_Id", i,
withOption, orgAlias));
builder.AddJoin(
SqlSourceJoinType.Inner,
String.Format("Object_Defs od{0} {1} on (od{0}.[Id] = o{0}.[Id] and {2})", i,
withOption,
String.Format(attrConditionStr, "od", i, "Full_Name")));
}
}
/*
* else if (attrDef.Type.Id == (short)CissaDataType.AuthorOfDocument)
* {
* sb.AppendFormat("(select top 1 od{0}.Full_Name from Workers w{0} ", i);
* sb.AppendFormat("inner join Object_Defs od{0} on od{0}.Id = w{0}.Id ", i);
* sb.AppendFormat(
* " where o{0}.Id = d.Organization_Id) as [{1}]",
* i, attr.AliasName);
* }
* else if (attrDef.Type.Id == (short)CissaDataType.Organization)
* {
* var tableName = attr.GetAttrDefTableName();
* sb.AppendFormat("(select top 1 od{0}.Value from {1} oa{0} \n", i, tableName);
* // sb.AppendFormat(" inner join Organizations o{0} on o{0}.Id = oa{0}.Value\n", i);
* // sb.AppendFormat(" inner join Object_Defs od{0} on od{0}.Id = o{0}.Id\n", i);
* sb.AppendFormat(
* " where oa{0}.Document_Id = d.Id and oa{0}.Def_Id = '{1}' and oa{0}.Expired = convert(date, '31/12/9999', 103)) as [{2}]",
* i, attrDef.Id, attr.AliasName);
* }
* else if (attrDef.Type.Id == (short)CissaDataType.DocumentState)
* {
* var tableName = attr.GetAttrDefTableName();
* sb.AppendFormat("(select top 1 od{0}.Full_Name from {1} da{0} \n", i, tableName);
* sb.AppendFormat(" inner join Document_State_Types dst{0} on dst{0}.Id = da{0}.Value\n", i);
* sb.AppendFormat(" inner join Object_Defs od{0} on od{0}.Id = dst{0}.Id\n", i);
* sb.AppendFormat(
* " where da{0}.Document_Id = d.Id and da{0}.Def_Id = '{1}' and da{0}.Expired = convert(date, '31/12/9999', 103)) as [{2}]",
* i, attrDef.Id, attr.AliasName);
* }
*/
else
{
var tableName = attr.GetAttrDefTableName();
if (attrTableField != null)
{
if (attrTableField.Type.HasFlag(AttributeFieldType.View))
{
sb.AppendFormat("[{0}].[{1}] as [{2}]", fromAlias, attrTableField.FieldName,
attr.AliasName);
}
else
{
sb.AppendFormat(
"(select [a{0}].[Value] from [{1}] [a{0}] {2}\n" +
"where [a{0}].Document_Id = {3}.Id and [a{0}].Def_Id = '{4}' and [a{0}].Expired = '99991231') as [{5}]",
i, tableName, withOption, fromAlias, attr.Def.Id, attr.AliasName);
}
if (!String.IsNullOrEmpty(attrConditionStr))
{
if (attrTableField.Type.HasFlag(AttributeFieldType.Search))
{
builder.AddCondition(ExpressionOperation.And,
String.Format(attrConditionStr, fromAlias, String.Empty,
attrTableField.FieldName));
}
else
{
var valueFieldName = attr.Def.Type.Id == (short)CissaDataType.Text ? "Upper_" : "";
builder.AddJoin(
SqlSourceJoinType.Inner,
String.Format(
"{0} a{1} {2} on (a{1}.Document_Id = {5}.Id and a{1}.Def_Id = '{3}' and a{1}.Expired = '99991231' and {4})",
tableName, i, withOption, attr.Def.Id,
String.Format(attrConditionStr, "a", i, valueFieldName + "Value"), fromAlias));
}
}
}
else
{
sb.AppendFormat("[a{0}].[Value] as [{1}]", i, attr.AliasName);
if (String.IsNullOrEmpty(attrConditionStr))
{
builder.AddJoin(
SqlSourceJoinType.LeftOuter,
String.Format(
"{0} a{1} {2} on (a{1}.Document_Id = {4}.Id and a{1}.Def_Id = '{3}' and a{1}.Expired = '99991231')",
tableName, i, withOption, attr.Def.Id, fromAlias));
}
else
{
var valueFieldName = attr.Def.Type.Id == (short)CissaDataType.Text ? "Upper_" : "";
builder.AddJoin(
SqlSourceJoinType.Inner,
String.Format(
"{0} a{1} {2} on (a{1}.Document_Id = {5}.Id and a{1}.Def_Id = '{3}' and a{1}.Expired = '99991231' and {4})",
tableName, i, withOption, attr.Def.Id,
String.Format(attrConditionStr, "a", i, valueFieldName + "Value"), fromAlias));
}
}
}
}
else if (attr.Ident == SystemIdent.State || attr.Ident == SystemIdent.InState)
{
var stateTableField = tableMap != null?tableMap.FindIdentField("State") : null;
if (stateTableField != null)
{
sb.AppendFormat("[{0}].[State] as [{1}]", fromAlias, attr.AliasName);
if (!String.IsNullOrEmpty(attrConditionStr))
{
builder.AddCondition(ExpressionOperation.And,
String.Format(attrConditionStr, fromAlias, String.Empty,
stateTableField.FieldName));
}
}
else
{
sb.AppendFormat("[ds{0}].[State_Type_Id] as [{1}]", i, attr.AliasName);
if (String.IsNullOrEmpty(attrConditionStr))
{
//sb.AppendFormat("(select ds{0}.State_Type_Id from Document_States ds{0} {1}\n", i,
// withOption);
//sb.AppendFormat(
// " where ds{0}.Document_Id = {2}.Id and ds{0}.Expired = '99991231') as [{1}]",
// i, attr.AliasName, fromAlias);
builder.AddJoin(
SqlSourceJoinType.LeftOuter,
String.Format(
"[Document_States] ds{0} {1} on (ds{0}.Document_Id = {2}.Id and ds{0}.Expired = '99991231')",
i, withOption, fromAlias));
}
else
{
builder.AddJoin(
SqlSourceJoinType.Inner,
String.Format(
"[Document_States] ds{0} {1} on (ds{0}.Document_Id = {3}.Id and ds{0}.Expired = '99991231' and {2})",
i, withOption,
String.Format(attrConditionStr, "ds", i, "State_Type_Id"), fromAlias));
}
}
}
else if (attr.Ident == SystemIdent.StateDate)
{
if (String.IsNullOrEmpty(attrConditionStr))
{
sb.AppendFormat("(select ds{0}.Created from Document_States ds{0} {1}\n", i, withOption);
sb.AppendFormat(
" where ds{0}.Document_Id = {2}.Id and ds{0}.Expired = '99991231') as [{1}]",
i, attr.AliasName, fromAlias);
}
else
{
sb.AppendFormat("[ds{0}].[Created] as [{1}]", i, attr.AliasName);
builder.AddJoin(
SqlSourceJoinType.Inner,
String.Format(
"[Document_States] ds{0} {1} on (ds{0}.Document_Id = {3}.Id and ds{0}.Expired = '99991231' and {2})",
i, withOption,
String.Format(attrConditionStr, "ds", i, "Created"), fromAlias));
}
}
else if (attr.Ident == SystemIdent.Id)
{
sb.AppendFormat("{0}.Id as [{1}]", fromAlias, attr.AliasName);
if (!String.IsNullOrEmpty(attrConditionStr))
{
builder.AddCondition(ExpressionOperation.And,
String.Format(attrConditionStr, fromAlias, String.Empty, "Id"));
}
}
else if (attr.Ident == SystemIdent.OrgId)
{
sb.AppendFormat("{0}.Organization_Id as [{1}]", orgAlias, attr.AliasName);
if (!String.IsNullOrEmpty(attrConditionStr))
{
builder.AddCondition(ExpressionOperation.And,
String.Format(attrConditionStr, orgAlias, "", "Organization_Id"));
}
}
else if (attr.Ident == SystemIdent.Created)
{
sb.AppendFormat("{0}.Created as [{1}]", createdAlias, attr.AliasName);
if (!String.IsNullOrEmpty(attrConditionStr))
{
builder.AddCondition(ExpressionOperation.And,
String.Format(attrConditionStr, createdAlias, "", "Created"));
}
}
else if (attr.Ident == SystemIdent.Modified)
{
sb.AppendFormat("{0}.Last_Modified as [{1}]", modifiedAlias, attr.AliasName);
if (!String.IsNullOrEmpty(attrConditionStr))
{
builder.AddCondition(ExpressionOperation.And,
String.Format(attrConditionStr, modifiedAlias, "", "Last_Modified"));
}
}
else if (attr.Ident == SystemIdent.UserId || attr.Ident == SystemIdent.UserName)
{
sb.AppendFormat("{0}.UserId as [{1}]", userAlias, attr.AliasName);
if (!String.IsNullOrEmpty(attrConditionStr))
{
builder.AddCondition(ExpressionOperation.And,
String.Format(attrConditionStr, userAlias, "", "UserId"));
}
}
else if (attr.Ident == SystemIdent.DefId)
{
sb.AppendFormat("{0}.Def_Id as [{1}]", defAlias, attr.AliasName);
if (!String.IsNullOrEmpty(attrConditionStr))
{
builder.AddCondition(ExpressionOperation.And,
String.Format(attrConditionStr, defAlias, "", "Def_Id"));
}
}
/*else if (attr.Ident == SystemIdent.UserName)
* {
* if (String.IsNullOrEmpty(attrConditionStr))
* {
* sb.AppendFormat("(select w{0}.User_Name from Workers w{0} {1}\n", i, withOption);
* sb.AppendFormat(" where w{0}.Id = {2}.UserId) as [{1}]", i, attr.AliasName, userAlias);
* }
* else
* {
* sb.AppendFormat("[w{0}].[User_Name] as [{1}]", i, attr.AliasName);
* builder.AddJoin(
* SqlSourceJoinType.Inner,
* String.Format("Workers w{0} {1} on (w{0}.Id = {3}.UserId and {2})", i, withOption,
* String.Format(attrConditionStr, "w", i, "User_Name"), userAlias));
* }
* }*/
else if (attr.Ident == SystemIdent.OrgCode)
{
if (String.IsNullOrEmpty(attrConditionStr))
{
sb.AppendFormat("(select o{0}.Code from Organizations o{0} {1}\n", i, withOption);
sb.AppendFormat(" where o{0}.Id = {2}.Organization_Id) as [{1}]", i,
attr.AliasName, orgAlias);
}
else
{
sb.AppendFormat("[o{0}].[Code] as [{1}]", i, attr.AliasName);
builder.AddJoin(
SqlSourceJoinType.Inner,
String.Format("Organizations o{0} {1} on (o{0}.Id = {3}.Organization_Id and {2})", i, withOption,
String.Format(attrConditionStr, "o", i, "Code"), orgAlias));
}
}
else if (attr.Ident == SystemIdent.OrgName)
{
if (String.IsNullOrEmpty(attrConditionStr))
{
sb.AppendFormat(
"(select od{0}.Full_Name\n" +
" from Organizations o{0} {1} inner join Object_Defs od{0} {1} on od{0}.Id = o{0}.Id\n",
i, withOption);
sb.AppendFormat(" where o{0}.Id = {2}.Organization_Id) as [{1}]", i,
attr.AliasName, orgAlias);
}
else
{
sb.AppendFormat("[od{0}].[Full_Name] as [{1}]", i, attr.AliasName);
builder.AddJoin(
SqlSourceJoinType.Inner,
String.Format("Organizations o{0} {1} on o{0}.[Id] = {2}.Organization_Id", i,
withOption, orgAlias));
builder.AddJoin(
SqlSourceJoinType.Inner,
String.Format("Object_Defs od{0} {1} on od{0}.[Id] = o{0}.[Id] and {2}", i,
withOption,
String.Format(attrConditionStr, "od", i, "Full_Name")));
}
}
else
{
throw new ApplicationException("Не известный атрибут в условии запроса!");
}
no++;
builder.AddSelect(sb);
usedConditions.AddRange(attributeConditions);
}
}
//if (builder.Select == null || builder.Select.Count == 0) builder.AddSelect("d.Id");
if (tableMap == null)
{
var descendants = new List <Guid>(_docDefRepo.GetDocDefDescendant(Source.Id));
if (descendants.Count < 2)
{
builder.AddCondition(ExpressionOperation.And,
String.Format("{0}.Def_Id = '{1}'", fromAlias, Source.Id));
}
else
{
var s = String.Empty;
foreach (var defId in descendants)
{
if (!String.IsNullOrEmpty(s))
{
s += ",";
}
s += String.Format("'{0}'", defId);
}
builder.AddCondition(ExpressionOperation.And, String.Format("{0}.Def_Id IN ({1})", fromAlias, s));
}
}
if (!String.IsNullOrEmpty(orgRestriction))
{
builder.AddCondition(ExpressionOperation.And, orgRestriction);
}
if (isMain || (needToJoinDocument && (tableMap == null || !tableMap.IsVirtual)))
{
builder.AddCondition(ExpressionOperation.And, "([d].[Deleted] is null OR [d].[Deleted] = 0)");
}
BuildConditions(builder, conditions, attrIndexDictionary, tableMap, needToJoinDocument, usedConditions);
return(builder);
}
private static void BuildCondition(SqlQuery query, QueryConditionDef condition, DocDef docDef, SqlQueryCondition parentCondition)
{
if (condition.Condition != ConditionOperation.Include && condition.Condition != ConditionOperation.Exp)
{
if (condition.SubQueryDef != null)
{
var subQuery = Build(query.DataContext, condition.SubQueryDef);
if (String.IsNullOrEmpty(condition.AttributeName))
{
query.AddCondition(condition.Operation, docDef, condition.AttributeId, condition.Condition,
subQuery, condition.SubQueryAttribute, parentCondition);
}
else
{
query.AddCondition(condition.Operation, docDef, condition.AttributeName, condition.Condition,
subQuery, condition.SubQueryAttribute, parentCondition);
}
}
else
if (String.IsNullOrEmpty(condition.AttributeName))
{
query.AddCondition(condition.Operation, docDef, condition.AttributeId, condition.Condition, condition.Values, parentCondition);
}
else
{
query.AddCondition(condition.Operation, docDef, condition.AttributeName, condition.Condition,
condition.Values, parentCondition);
}
}
else if (condition.Condition == ConditionOperation.Include && condition.Conditions != null && condition.Conditions.Count > 0)
{
var attrDef = String.IsNullOrEmpty(condition.AttributeName)
? docDef.Attributes.First(a => a.Id == condition.AttributeId)
: docDef.Attributes.First(
a => String.Equals(a.Name, condition.AttributeName, StringComparison.OrdinalIgnoreCase));
query.JoinSource(query.FindSourceByDocDef(docDef), attrDef.DocDefType.Id, SqlSourceJoinType.Inner, attrDef.Id);
foreach (var child in condition.Conditions)
{
BuildCondition(query, child, attrDef.DocDefType.Id, parentCondition);
}
}
else if (condition.Condition == ConditionOperation.Exp && condition.Conditions != null)
{
var exp = query.AddExpCondition(condition.Operation, parentCondition);
foreach (var child in condition.Conditions)
{
BuildCondition(query, child, docDef, exp);
}
}
}
public static string UpdateUserInfo(Dictionary <string, string> info, out object[,] newinfo, int userid)
{
List <UserField> fieldlist = new List <UserField>(7);
List <object> valuelist = new List <object>(7);
List <string> changes = new List <string>(7);
string s = "", msg;
if (info.ContainsKey("birthday"))
{
if (info["birthday"].Length != 0)
{
DateTime date;
if (DateTime.TryParse(info["birthday"], out date))
{
fieldlist.Add(UserField.Birthday);
valuelist.Add(date.ToString("yyyy-MM-dd"));
changes.Add("生日：" + date.ToString("yyyy-MM-dd"));
}
else
{
s += "生日信息错误 不是一个日期\n";
}
}
else
{
fieldlist.Add(UserField.Birthday);
valuelist.Add(null);
changes.Add("生日：(空)");
}
}
if (info.ContainsKey("qq"))
{
if (UserField.QQ.TestValue(info["qq"], out msg))
{
fieldlist.Add(UserField.QQ);
valuelist.Add(info["qq"]);
changes.Add("QQ：" + info["qq"]);
}
else
{
s += "QQ信息错误 " + msg + '\n';
}
}
if (info.ContainsKey("wechat"))
{
if (UserField.WeChat.TestValue(info["wechat"], out msg))
{
fieldlist.Add(UserField.WeChat);
valuelist.Add(info["wechat"]);
changes.Add("微信：" + info["wechat"]);
}
else
{
s += "微信信息错误 " + msg + '\n';
}
}
if (info.ContainsKey("phone"))
{
if (UserField.PhoneNumber.TestValue(info["phone"], out msg))
{
fieldlist.Add(UserField.PhoneNumber);
valuelist.Add(info["phone"]);
changes.Add("电话号码：" + info["phone"]);
}
else
{
s += "电话号码信息错误 " + msg + '\n';
}
}
if (info.ContainsKey("dormitory"))
{
if (UserField.Dormitory.TestValue(info["dormitory"], out msg))
{
fieldlist.Add(UserField.Dormitory);
valuelist.Add(info["dormitory"]);
changes.Add("宿舍：" + info["dormitory"]);
}
else
{
s += "宿舍信息错误 " + msg + '\n';
}
}
if (info.ContainsKey("address"))
{
if (UserField.Address.TestValue(info["address"], out msg))
{
fieldlist.Add(UserField.Address);
valuelist.Add(info["address"]);
changes.Add("家庭住址：" + info["address"]);
}
else
{
s += "家庭住址信息错误 " + msg + '\n';
}
}
if (info.ContainsKey("idcard"))
{
if (UserField.IdCardNumber.TestValue(info["idcard"], out msg))
{
fieldlist.Add(UserField.IdCardNumber);
valuelist.Add(info["idcard"]);
changes.Add("身份证号：" + info["idcard"]);
}
else
{
s += "身份证号信息错误 " + msg + '\n';
}
}
SqlQueryCondition sqlcondition = new SqlQueryCondition(UserField.Id, SqlQueryConditionOperator.Equal, userid);
if (fieldlist.Count == 0)
{
s = "没有符合要求的数据！";
}
else
{
UserDAO.Update(fieldlist.ToArray(), valuelist.ToArray(), sqlcondition);
string loginfo = changes[0];
bool overflow = false;
for (int i = 1; i < changes.Count; i++)
{
if (loginfo.Length + changes[i].Length <= UserLogField.Event.SqlDataLength - 14)
{
loginfo += '\t' + changes[i];
}
else
{
overflow = true;
}
}
if (overflow)
{
loginfo += "\t...";
}
UserLogDAO.Insert(userid, "updinfo{" + loginfo + "}");
if (s.Length != 0)
{
s = "true:" + s;
}
else
{
s = "true";
}
}
newinfo = UserDAO.Select(UserField.OtherFields, sqlcondition);
return(s);
}
protected static void FillConditionSources(ICollection <SqlQuerySource> sources, SqlQueryCondition condition)
{
if (condition.Condition == ConditionOperation.Include || condition.Condition == ConditionOperation.Exp)
{
if (condition.Conditions == null || condition.Conditions.Count == 0)
{
return;
}
foreach (var subCondition in condition.Conditions)
{
FillConditionSources(sources, subCondition);
}
return;
}
// if (condition.SubQuery == null)
foreach (var attrRef in condition.Left.Attributes)
{
if (!sources.Contains(attrRef.Source))
{
sources.Add(attrRef.Source);
}
}
if (condition.Right.IsAttribute)
{
foreach (var attrRef in condition.Right.Attributes)
{
if (!sources.Contains(attrRef.Source))
{
sources.Add(attrRef.Source);
}
}
}
}
internal string BuildConditions()
{
return(SqlQueryCondition.BuildConditions(Conditions));
}
