protected SPListItem GetOrderInfo(string orderNumber, string department)
{
SPListItem item = null;
var qOrderNumber = new QueryField("Order_x0020_Number", false);
var qDepartment = new QueryField("Department", false);
var qStatus = new QueryField("Status", false);
CamlExpression exp = null;
exp = WorkFlowUtil.LinkAnd(exp, qOrderNumber.Equal(orderNumber));
exp = WorkFlowUtil.LinkAnd(exp, qDepartment.Equal(department));
var status = CAWorkflowStatus.Completed;
exp = WorkFlowUtil.LinkAnd(exp, qStatus.Equal(status));
SPListItemCollection lc = ListQuery.Select()
.From(WorkFlowUtil.GetWorkflowList("CreationOrder"))
.Where(exp)
.GetItems();
if (lc.Count > 0)
{
item = lc[0];
}
return item;
}
public DataTable Query(string selectedMonth)
{
if (string.IsNullOrEmpty(selectedMonth))
{
return null;
}
var dt = new DataTable();
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (var site = new SPSite(SPContext.Current.Site.ID))
{
using (var web = site.OpenWeb("workflowcenter"))
{
var monthField = new QueryField("StatMon", false);
CamlExpression exp = monthField.Equal(selectedMonth + "-1");
dt = ListQuery.Select()
.From(web.Lists[LeaveDataListName])
.Where(exp)
.OrderBy(new QueryField("EmployeeName", false), false)
.OrderBy(new QueryField("TimeWageType", false), true)
.OrderBy(new QueryField("Date", false), true)
.GetDataTable();
}
}
});
return dt;
}
/*
*
*/
public static List<string> GetMailMembers(params string[] personTypes)
{
List<string> members = new List<string>();
var qType = new QueryField("PersonType", false);
CamlExpression exp = null;
foreach (var type in personTypes)
{
exp = WorkFlowUtil.LinkOr(exp, qType.Equal(type));
}
SPListItemCollection lc = null;
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (SPSite site = new SPSite(SPContext.Current.Site.ID))
{
using (SPWeb web = site.OpenWeb(SPContext.Current.Web.ID))
{
lc = ListQuery.Select()
.From(web.Lists["MailMember"])
.Where(exp)
.GetItems();
}
}
});
foreach (SPListItem item in lc)
{
members.Add(item["Account"].AsString());
}
return members;
}
/*
* Return list item according to from value and to value
*/
protected SPListItem GetFlightPriceItem(string from, string to)
{
var qFromCN = new QueryField("FromCN", false);
var qFromEN = new QueryField("FromEN", false);
var qToEN = new QueryField("ToEN", false);
var qToCN = new QueryField("ToCN", false);
//From->To
CamlExpression exp = null;
exp = WorkFlowUtil.LinkOr(exp, qFromEN.Equal(from));
exp = WorkFlowUtil.LinkOr(exp, qFromCN.Equal(from));
CamlExpression exp1 = null;
exp1 = WorkFlowUtil.LinkOr(exp1, qToEN.Equal(to));
exp1 = WorkFlowUtil.LinkOr(exp1, qToCN.Equal(to));
CamlExpression exp2 = WorkFlowUtil.LinkAnd(exp, exp1);
//To->From
exp = WorkFlowUtil.LinkOr(exp, qFromEN.Equal(to));
exp = WorkFlowUtil.LinkOr(exp, qFromCN.Equal(to));
exp1 = WorkFlowUtil.LinkOr(exp1, qToEN.Equal(from));
exp1 = WorkFlowUtil.LinkOr(exp1, qToCN.Equal(from));
CamlExpression exp3 = WorkFlowUtil.LinkAnd(exp, exp1);
//From<->To
CamlExpression exp4 = WorkFlowUtil.LinkOr(exp2, exp3);
SPListItemCollection lc = ListQuery.Select()
.From(WorkFlowUtil.GetWorkflowList("Flight Price"))
.Where(exp4)
.GetItems();
return lc.Count > 0 ? lc[0] : null;
}
internal static object GenerateID(string preFix, string listName)
{
int nNum = 1;
SPList list = SPContext.Current.Web.Lists[AnalogicConstant.TMSNumberListName];
QueryField field = new QueryField("Title");
CAMLExpression<object> exp = field.Equal(listName);
SPListItemCollection items = ListQuery.Select().From(list).Where(exp).GetItems();
if (items != null && items.Count > 0)
{
SPListItem item = list.GetItemById(items[0].ID);
nNum = Convert.ToInt32(items[0][AnalogicConstant.TMSNumber]) + 1;
item[AnalogicConstant.TMSNumber] = Convert.ToDouble(nNum);
item.Web.AllowUnsafeUpdates = true;
item.Update();
item.Web.AllowUnsafeUpdates = false;
}
else
{
SPListItem item = list.Items.Add();
item[SPBuiltInFieldId.Title] = listName;
item[AnalogicConstant.TMSNumber] = nNum;
item.Web.AllowUnsafeUpdates = true;
item.Update();
item.Web.AllowUnsafeUpdates = false;
}
return string.Format("{0}{1:000000}", preFix, nNum);
}
protected SPListItemCollection FilterVendor(string workflowNumber, string vendId, string enName, string cnName, string status, string applicantAccount, string department)
{
var qWorkflowNumber = new QueryField("Title", false);
var qENName = new QueryField("EN_x0020_Name_x0020_of_x0020_Ven", false);
var qCNName = new QueryField("CN_x0020_Name_x0020_of_x0020_Ven", false);
var qRecordType = new QueryField("Record_x0020_Type", false);
var qStatus = new QueryField("Status", false);
var qVendId = new QueryField("Vendor_x0020_ID", false);
var qApplicantAccount = new QueryField("Applicant", false);
var qDepartmentVal = new QueryField("DepartmentVal", false);
CamlExpression exp = null;
exp = WorkFlowUtil.LinkAnd(exp, qRecordType.Equal("New"));
if (!string.IsNullOrEmpty(status))
{
exp = WorkFlowUtil.LinkAnd(exp, qStatus.Equal(status));
}
if (!string.IsNullOrEmpty(workflowNumber))
{
exp = WorkFlowUtil.LinkAnd(exp, qWorkflowNumber.Equal(workflowNumber));
}
if (!string.IsNullOrEmpty(vendId))
{
exp = WorkFlowUtil.LinkAnd(exp, qVendId.Equal(vendId));
}
if (!string.IsNullOrEmpty(enName))
{
exp = WorkFlowUtil.LinkAnd(exp, qENName.Contains(enName));
}
if (!string.IsNullOrEmpty(cnName))
{
exp = WorkFlowUtil.LinkAnd(exp, qCNName.Contains(cnName));
}
if (!string.IsNullOrEmpty(applicantAccount))
{
//Applicant: Test1(CA\\test1)
//applicantAccount: CA\\test1
exp = WorkFlowUtil.LinkAnd(exp, qApplicantAccount.Contains(applicantAccount));
}
if (!string.IsNullOrEmpty(department))
{
exp = WorkFlowUtil.LinkAnd(exp, qDepartmentVal.Equal(department));
}
var result = ListQuery.Select().From(WorkFlowUtil.GetWorkflowList("Non Trade Supplier Setup Maintenance Workflow"))
.Where(exp)
.OrderBy(new QueryField("Title", false), true)
.GetItems();
return result;
}
/*
* Return data collection according to given REQUESTID and LISTNAME
*/
protected SPListItemCollection GetDataCollection(string requestId, string listName)
{
var qRequestId = new QueryField("Title", false);
CamlExpression exp = null;
exp = WorkFlowUtil.LinkAnd(exp, qRequestId.Equal(requestId));
SPListItemCollection lc = ListQuery.Select()
.From(WorkFlowUtil.GetWorkflowList(listName))
.Where(exp)
.GetItems();
return lc;
}
public SPListItemCollection GetApprovedItemCollection(string listName)
{
SPContext context = SPContext.Current;
SPListItemCollection items = null;
SPList list = context.Web.Lists[listName];
QueryField outcomeField = new QueryField("Outcome");
CAMLExpression<object> exp = outcomeField.Equal("Approved");
items = ListQuery.Select().From(list).Where(exp).GetItems();
return items;
}
protected bool isExistOrder(string orderNumber, string department)
{
var qOrderNumber = new QueryField("Order_x0020_Number", false);
var qDepartment = new QueryField("Department", false);
CamlExpression exp = null;
exp = WorkFlowUtil.LinkAnd(exp, qOrderNumber.Equal(orderNumber));
exp = WorkFlowUtil.LinkAnd(exp, qDepartment.Equal(department));
SPListItemCollection lc = ListQuery.Select()
.From(WorkFlowUtil.GetWorkflowList("CreationOrder"))
.Where(exp)
.GetItems();
return lc.Count > 0;
}
//Return the exchange rate item
protected SPListItem GetExchangeRate(string from, string to)
{
var qFrom = new QueryField("From", false);
var qTo = new QueryField("To", false);
CamlExpression exp = null;
exp = WorkFlowUtil.LinkAnd(exp, qFrom.Equal(from));
exp = WorkFlowUtil.LinkAnd(exp, qTo.Equal(to));
SPListItemCollection lc = ListQuery.Select()
.From(WorkFlowUtil.GetWorkflowList("ExchangeRates"))
.Where(exp)
.GetItems();
return lc.Count > 0 ? lc[0] : null;
}
private bool IsSubmiter()
{
QueryField field = new QueryField("Title");
CA.SharePoint.ISharePointService sps = CA.SharePoint.ServiceFactory.GetSharePointService(true);
SPList list = sps.GetList("NewsApproveConfig");
SPListItemCollection items = sps.Query(list, field.Equal(SPContext.Current.List.Title), 1);
if (items != null && items.Count > 0)
{
string users = items[0]["Submitted"] + "";
string strCurrentUser = string.Empty;
if (SPContext.Current.Web.CurrentUser.IsSiteAdmin)
strCurrentUser = HttpContext.Current.User.Identity.Name;
else
strCurrentUser = SPContext.Current.Web.CurrentUser.LoginName;
if (users.ToLower().Contains(strCurrentUser.ToLower()))
return true;
}
return false;
}
void actions_ActionExecuting(object sender, QuickFlow.UI.Controls.ActionEventArgs e)
{
if (e.Action.Equals("End", StringComparison.CurrentCultureIgnoreCase))
{
WorkflowContext.Current.DataFields["Status"] = "Cancelled";
SPListItem item = SPContext.Current.ListItem;
ISharePointService sps = ServiceFactory.GetSharePointService(true);
SPList listBalance = sps.GetList(CAWorkFlowConstants.ListName.LeaveBalance.ToString());
QueryField field = new QueryField("Employee");
QueryField field2 = new QueryField("Year");
int year = DateTime.Parse(item["DateFrom"] + "").Year;
//根据field来查询
SPListItemCollection items = sps.Query(listBalance, field.Equal(this.DataForm1.ApplicantName) && field2.Equal(year), 1);
//审批submit后 在balance表中扣除所请的天数
SPListItem itemBalance = items[0];
return;
}
bool IsSick = this.DataForm1.IsSickLeave;
WorkflowContext.Current.UpdateWorkflowVariable("IsSickLeave", IsSick);
string deptHead = WorkFlowUtil.GetEmployeeApprover(this.CurrentEmployee).UserAccount;
WorkflowContext.Current.UpdateWorkflowVariable(CA.WorkFlow.UI.Constants.WorkFlowStep.TimeOffDepartmentHeadAccount, GetDelemanNameCollection(new NameCollection(deptHead), Constants.CAModules.LeaveApplication));
string strNextTaskUrl = @"_Layouts/CA/WorkFlows/TimeOff2/EditForm.aspx";
string strNextTaskTitle = string.Format("{0}'s leave application needs approval", SPContext.Current.Web.CurrentUser.Name);
WorkflowContext.Current.UpdateWorkflowVariable("NextTaskUrl", strNextTaskUrl);
WorkflowContext.Current.UpdateWorkflowVariable("NextTaskTitle", strNextTaskTitle);
WorkflowContext.Current.DataFields["IsSave"] = 0;
WorkflowContext.Current.DataFields["EmployeeID"] = this.DataForm1.EmployeeNo;
WorkflowContext.Current.DataFields["Status"] = "In Progress";
WorkFlowUtil.UpdateWorkflowPath(WorkflowContext.Current);
}
public int CreateWorkFlowNumber2(string workflowName)
{
int nNum = 1;
//CA.SharePoint.ISharePointService sps = ServiceFactory.GetSharePointService(true);
//SPList list = sps.GetList(CAWorkFlowConstants.ListName.WorkFlowNumber.ToString());
var list = SPContext.Current.Web.Lists[CAWorkFlowConstants.ListName.WorkFlowNumber.ToString()];
QueryField field = new QueryField("Title");
//SPListItemCollection items = sps.Query(list, field.Equal(workflowName), 1, null);
SPQuery query = new SPQuery();
var querystr = @"<Where>
<Eq>
<FieldRef Name='Title' />
<Value Type='Text'>{0}</Value>
</Eq>
</Where>";
query.Query = string.Format(querystr, workflowName);
var items = list.GetItems(query);
if (items != null && items.Count > 0)
{
SPListItem item = items[0];//list.GetItemById(items[0].ID);
nNum = Convert.ToInt32(item["Number"]) + 1;//Convert.ToInt32(items[0]["Number"]) + 1;
item["Number"] = Convert.ToDouble(nNum);
item.Web.AllowUnsafeUpdates = true;
item.Update();
}
else
{
SPListItem item = list.Items.Add();
item["WorkFlowName"] = workflowName;
item["Number"] = nNum;
item.Web.AllowUnsafeUpdates = true;
item.Update();
}
return nNum;
}
/**
* 获取有效的Item Code
*/
private static DataTable GetActiveItemCode()
{
var qIsActive = new QueryField("IsActive", false);
CamlExpression exp = null;
exp = WorkFlowUtil.LinkAnd(exp, qIsActive.Equal(true));
SPListItemCollection lc = null;
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (SPSite site = new SPSite(SPContext.Current.Site.ID))
{
using (SPWeb web = site.OpenWeb(SPContext.Current.Web.ID))
{
lc = ListQuery.Select()
.From(web.Lists["Item Codes"])
.Where(exp)
.GetItems();
}
}
});
return lc.GetDataTable();
}
public static SPListItemCollection GetAllTasks(string listId, string itemId)
{
var qWorkflowListId = new QueryField("WorkflowListId", false);
var qWorkflowItemId = new QueryField("WorkflowItemId", false);
CamlExpression exp = null;
exp = WorkFlowUtil.LinkAnd(exp, qWorkflowListId.Equal(listId));
exp = WorkFlowUtil.LinkAnd(exp, qWorkflowItemId.Equal(itemId));
SPListItemCollection lc = null;
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (SPSite site = new SPSite(SPContext.Current.Site.ID))
{
using (SPWeb web = site.OpenWeb(SPContext.Current.Web.ID))
{
lc = ListQuery.Select()
.From(web.Lists["Tasks"])
.Where(exp)
.GetItems();
}
}
});
return lc;
}
public static string GetQuotaByType(string type)
{
var approvalType = new QueryField("ApprovalType", false);
CamlExpression exp = null;
exp = WorkFlowUtil.LinkAnd(exp, approvalType.Equal(type));
SPListItemCollection coll = null;
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (SPSite site = new SPSite(SPContext.Current.Site.ID))
{
using (SPWeb web = site.OpenWeb(SPContext.Current.Web.ID))
{
coll = ListQuery.Select()
.From(web.Lists["QuotaLevel"])
.Where(exp)
.GetItems();
}
}
});
return coll.Count > 0 ? coll[0]["Limits"].AsString() : null;
}
public static QuickFlow.NameCollection GetNewsApproveUsers(string listTitle)
{
QuickFlow.NameCollection names = new QuickFlow.NameCollection();
try
{
QueryField field = new QueryField("Title");
CA.SharePoint.ISharePointService sps = CA.SharePoint.ServiceFactory.GetSharePointService(true);
SPList list = sps.GetList("NewsApproveConfig");
SPListItemCollection items = sps.Query(list, field.Equal(listTitle), 1);
if (items != null && items.Count > 0)
{
SPFieldUserValueCollection users = items[0]["Approvers"] as SPFieldUserValueCollection;
foreach (SPFieldUserValue user in users)
{
string userLoginName = user.LookupValue;
names.Add(userLoginName);
}
}
}
catch { }
return names;
}
/*
* Get delegate users according to the given multi names.
* @Return array that contains delegate users
*/
public static List<string> GetDelemans(List<string> loginNames, string moduleId)
{
List<string> delemans = new List<string>();
var now = DateTime.Now.ToString("yyyyMMdd");
var qApprover = new QueryField("ApproverLoginName", false);
var qBeginOn = new QueryField("BeginOn", false);
var qEndOn = new QueryField("EndOn", false);
var qModules = new QueryField("Modules", false);
CamlExpression exp = null;
exp = WorkFlowUtil.LinkAnd(exp, qModules.Contains(moduleId));
exp = WorkFlowUtil.LinkAnd(exp, qBeginOn.LessEqual(now));
exp = WorkFlowUtil.LinkAnd(exp, qEndOn.MoreEqual(now));
CamlExpression exp2 = null;
foreach (string name in loginNames)
{
exp2 = WorkFlowUtil.LinkOr(exp2, qApprover.Equal(name));
}
exp = WorkFlowUtil.LinkAnd(exp, exp2);
SPListItemCollection coll = null;
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (SPSite site = new SPSite(SPContext.Current.Site.ID))
{
using (SPWeb web = site.OpenWeb(SPContext.Current.Web.ID))
{
coll = ListQuery.Select()
.From(web.Lists["Delegates"])
.Where(exp)
.GetItems();
}
}
});
foreach (SPListItem item in coll)
{
// Need to check whether the delegate account is valid.
if (UserProfileUtil.GetEmployeeEx(item["DelegateToLoginName"].ToString()) != null)
{
delemans.Add(item["DelegateToLoginName"].ToString());
}
}
return delemans;
}
private void SetupDataGridView(IPreferenceSet prefs)
{
// Add Column Selector
new DataGridViewColumnSelector(dataGridView1);
string[] names = QueryField.GetColumnNames();
dataGridView1.AutoGenerateColumns = false;
// ReSharper disable PossibleNullReferenceException
dataGridView1.Columns.Add(QueryFieldName.ProjectID.ToString(), names[(int)QueryFieldName.ProjectID]);
dataGridView1.Columns[QueryFieldName.ProjectID.ToString()].DataPropertyName = QueryFieldName.ProjectID.ToString();
dataGridView1.Columns.Add(QueryFieldName.WorkUnitName.ToString(), names[(int)QueryFieldName.WorkUnitName]);
dataGridView1.Columns[QueryFieldName.WorkUnitName.ToString()].DataPropertyName = QueryFieldName.WorkUnitName.ToString();
dataGridView1.Columns.Add(QueryFieldName.Name.ToString(), names[(int)QueryFieldName.Name]);
dataGridView1.Columns[QueryFieldName.Name.ToString()].DataPropertyName = QueryFieldName.Name.ToString();
dataGridView1.Columns.Add(QueryFieldName.Path.ToString(), names[(int)QueryFieldName.Path]);
dataGridView1.Columns[QueryFieldName.Path.ToString()].DataPropertyName = QueryFieldName.Path.ToString();
dataGridView1.Columns.Add(QueryFieldName.Username.ToString(), names[(int)QueryFieldName.Username]);
dataGridView1.Columns[QueryFieldName.Username.ToString()].DataPropertyName = QueryFieldName.Username.ToString();
dataGridView1.Columns.Add(QueryFieldName.Team.ToString(), names[(int)QueryFieldName.Team]);
dataGridView1.Columns[QueryFieldName.Team.ToString()].DataPropertyName = QueryFieldName.Team.ToString();
dataGridView1.Columns.Add(QueryFieldName.SlotType.ToString(), names[(int)QueryFieldName.SlotType]);
dataGridView1.Columns[QueryFieldName.SlotType.ToString()].DataPropertyName = QueryFieldName.SlotType.ToString();
dataGridView1.Columns.Add(QueryFieldName.Core.ToString(), names[(int)QueryFieldName.Core]);
dataGridView1.Columns[QueryFieldName.Core.ToString()].DataPropertyName = QueryFieldName.Core.ToString();
dataGridView1.Columns.Add(QueryFieldName.CoreVersion.ToString(), names[(int)QueryFieldName.CoreVersion]);
dataGridView1.Columns[QueryFieldName.CoreVersion.ToString()].DataPropertyName = QueryFieldName.CoreVersion.ToString();
dataGridView1.Columns.Add(QueryFieldName.FrameTime.ToString(), names[(int)QueryFieldName.FrameTime]);
dataGridView1.Columns[QueryFieldName.FrameTime.ToString()].DataPropertyName = QueryFieldName.FrameTime.ToString();
dataGridView1.Columns.Add(QueryFieldName.KFactor.ToString(), names[(int)QueryFieldName.KFactor]);
dataGridView1.Columns[QueryFieldName.KFactor.ToString()].DataPropertyName = QueryFieldName.KFactor.ToString();
dataGridView1.Columns.Add(QueryFieldName.PPD.ToString(), names[(int)QueryFieldName.PPD]);
dataGridView1.Columns[QueryFieldName.PPD.ToString()].DataPropertyName = QueryFieldName.PPD.ToString();
dataGridView1.Columns[QueryFieldName.PPD.ToString()].DefaultCellStyle = new DataGridViewCellStyle {
Format = prefs.GetPpdFormatString()
};
dataGridView1.Columns.Add(QueryFieldName.DownloadDateTime.ToString(), names[(int)QueryFieldName.DownloadDateTime]);
dataGridView1.Columns[QueryFieldName.DownloadDateTime.ToString()].DataPropertyName = QueryFieldName.DownloadDateTime.ToString();
dataGridView1.Columns.Add(QueryFieldName.CompletionDateTime.ToString(), names[(int)QueryFieldName.CompletionDateTime]);
dataGridView1.Columns[QueryFieldName.CompletionDateTime.ToString()].DataPropertyName = QueryFieldName.CompletionDateTime.ToString();
dataGridView1.Columns.Add(QueryFieldName.Credit.ToString(), names[(int)QueryFieldName.Credit]);
dataGridView1.Columns[QueryFieldName.Credit.ToString()].DataPropertyName = QueryFieldName.Credit.ToString();
dataGridView1.Columns[QueryFieldName.Credit.ToString()].DefaultCellStyle = new DataGridViewCellStyle {
Format = prefs.GetPpdFormatString()
};
dataGridView1.Columns.Add(QueryFieldName.Frames.ToString(), names[(int)QueryFieldName.Frames]);
dataGridView1.Columns[QueryFieldName.Frames.ToString()].DataPropertyName = QueryFieldName.Frames.ToString();
dataGridView1.Columns.Add(QueryFieldName.FramesCompleted.ToString(), names[(int)QueryFieldName.FramesCompleted]);
dataGridView1.Columns[QueryFieldName.FramesCompleted.ToString()].DataPropertyName = QueryFieldName.FramesCompleted.ToString();
dataGridView1.Columns.Add(QueryFieldName.Result.ToString(), names[(int)QueryFieldName.Result]);
dataGridView1.Columns[QueryFieldName.Result.ToString()].DataPropertyName = QueryFieldName.Result.ToString();
dataGridView1.Columns.Add(QueryFieldName.Atoms.ToString(), names[(int)QueryFieldName.Atoms]);
dataGridView1.Columns[QueryFieldName.Atoms.ToString()].DataPropertyName = QueryFieldName.Atoms.ToString();
dataGridView1.Columns.Add(QueryFieldName.ProjectRun.ToString(), names[(int)QueryFieldName.ProjectRun]);
dataGridView1.Columns[QueryFieldName.ProjectRun.ToString()].DataPropertyName = QueryFieldName.ProjectRun.ToString();
dataGridView1.Columns.Add(QueryFieldName.ProjectClone.ToString(), names[(int)QueryFieldName.ProjectClone]);
dataGridView1.Columns[QueryFieldName.ProjectClone.ToString()].DataPropertyName = QueryFieldName.ProjectClone.ToString();
dataGridView1.Columns.Add(QueryFieldName.ProjectGen.ToString(), names[(int)QueryFieldName.ProjectGen]);
dataGridView1.Columns[QueryFieldName.ProjectGen.ToString()].DataPropertyName = QueryFieldName.ProjectGen.ToString();
// ReSharper restore PossibleNullReferenceException
}
/// <summary>
///
/// </summary>
/// <param name="command"></param>
/// <param name="queryField"></param>
/// <param name="propertiesToSkip"></param>
/// <param name="entityType"></param>
/// <param name="dbFields"></param>
private static void CreateParameters(this IDbCommand command,
QueryField queryField,
IEnumerable <string> propertiesToSkip,
Type entityType,
IEnumerable <DbField> dbFields = null)
{
if (queryField == null)
{
return;
}
// Skip
if (propertiesToSkip?.Contains(queryField.Field.Name, StringComparer.OrdinalIgnoreCase) == true)
{
return;
}
// Variables
var dbField = GetDbField(queryField.Field.Name, dbFields);
var value = queryField.Parameter.Value;
var valueType = value?.GetType()?.GetUnderlyingType();
var isEnum = valueType?.IsEnum;
// PropertyHandler
var classProperty = PropertyCache.Get(entityType, queryField.Field);
var propertyHandler = GetPropertyHandler(classProperty, valueType);
var definition = InvokePropertyHandlerSetMethod(propertyHandler, value, classProperty);
if (definition != null)
{
valueType = definition.ReturnType.GetUnderlyingType();
value = definition.Value;
}
// Automatic
if (IsAutomaticConversion(dbField))
{
var underlyingType = dbField.Type.GetUnderlyingType();
value = AutomaticConvert(value, classProperty?.PropertyInfo?.PropertyType?.GetUnderlyingType(), underlyingType);
valueType = underlyingType;
}
// DbType
var dbType = (valueType != null ? clientTypeToDbTypeResolver.Resolve(valueType) : null) ??
classProperty?.GetDbType() ??
value?.GetType()?.GetDbType();
// Try get fallback dbType by classProperty to avoid being mistaken as string when value is null.
if (dbType == null && classProperty != null)
{
dbType = clientTypeToDbTypeResolver.Resolve(classProperty.PropertyInfo.PropertyType);
}
// Specialized enum
if (dbType == null && isEnum.HasValue && isEnum == true)
{
dbType = Converter.EnumDefaultDatabaseType;
}
// Add the parameter
command.Parameters.Add(command.CreateParameter(queryField.Parameter.Name, value, dbType));
}
public void QueryFieldValueTest3()
{
var field = new QueryField();
field.Value = 6900;
Assert.AreEqual("6900", field.Value);
field.Value = null;
Assert.IsNull(field.Value);
}
public void QueryFieldGetOperatorTest5()
{
var field = new QueryField();
field.Type = QueryFieldType.LessThanOrEqual;
Assert.AreEqual("<=", field.Operator);
}
/// <summary>
///
/// </summary>
/// <param name="command"></param>
/// <param name="queryField"></param>
/// <param name="dbField"></param>
private static void CreateParametersForBetweenOperation(this IDbCommand command,
QueryField queryField,
DbField dbField = null)
{
if (queryField == null)
{
return;
}
var values = (queryField.Parameter.Value as System.Collections.IEnumerable)?
.WithType <object>()
.AsList();
if (values?.Count == 2)
{
var leftValue = values[0];
var rightValue = values[1];
var leftValueType = leftValue?.GetType()?.GetUnderlyingType();
var rightValueType = rightValue?.GetType()?.GetUnderlyingType();
var isLeftEnum = leftValueType?.IsEnum;
var isRightEnum = rightValueType?.IsEnum;
// Propertyhandler (Left)
var leftPropertyHandler = GetProperyHandler(null, leftValueType);
var leftdefinition = InvokePropertyHandlerSetMethod(leftPropertyHandler, leftValue, null);
if (leftdefinition != null)
{
leftValueType = leftdefinition.ReturnType.GetUnderlyingType();
leftValue = leftdefinition.Value;
}
// Propertyhandler (Right)
var rightPropertyHandler = GetProperyHandler(null, rightValueType);
var rightDefinition = InvokePropertyHandlerSetMethod(rightPropertyHandler, rightValue, null);
if (rightDefinition != null)
{
rightValueType = rightDefinition.ReturnType.GetUnderlyingType();
rightValue = rightDefinition.Value;
}
// Automatic
if (IsAutomaticConversion(dbField))
{
leftValueType = dbField.Type.GetUnderlyingType();
rightValueType = leftValueType;
leftValue = AutomaticConvert(leftValue, leftValue?.GetType(), leftValueType);
rightValue = AutomaticConvert(rightValue, leftValue?.GetType(), rightValueType);
}
// DbType
var leftDbType = (leftValueType != null ? clientTypeToDbTypeResolver.Resolve(leftValueType) : null);
var rightDbType = (rightValueType != null ? clientTypeToDbTypeResolver.Resolve(rightValueType) : null);
// Specialized enum
if (leftDbType == null && isLeftEnum.HasValue && isLeftEnum.Value == true)
{
leftDbType = DbType.String;
}
if (rightDbType == null && isRightEnum.HasValue && isRightEnum.Value == true)
{
rightDbType = DbType.String;
}
// Add
command.Parameters.Add(
CreateParameter(command, string.Concat(queryField.Parameter.Name, "_Left"), leftValue, leftDbType));
command.Parameters.Add(
CreateParameter(command, string.Concat(queryField.Parameter.Name, "_Right"), rightValue, rightDbType));
}
else
{
throw new InvalidParameterException("The values for 'Between' and 'NotBetween' operations must be 2.");
}
}
// select a field on the grid
private void SelectField(QueryField field)
{
//var cm = BindingContext[_grid.DataSource] as CurrencyManager;
//cm.Position = cm.List.IndexOf(field);
}
// AsField
internal static string AsField(this QueryField queryField,
IDbSetting dbSetting)
{
return(queryField.Field.Name.AsField(dbSetting));
}
// AsDbParameter
internal static IDbDataParameter AsDbParameter(this QueryField queryField,
IDbCommand command)
{
return(AsDbParameter(queryField, command.CreateParameter()));
}
public void QueryField_Operator_Default_Test()
{
var field = new QueryField();
Assert.AreEqual("=", field.Operator);
}
// AsBetweenParameter
internal static string AsBetweenParameter(this QueryField queryField)
{
return(string.Concat("@", queryField.Parameter.Name, "_", StringConstant.BetweenLeft, " AND @", queryField.Parameter.Name, "_", StringConstant.BetweenRight));
}
public DatabaseQueryFilterModel(Banshee.Sources.DatabaseSource source, DatabaseTrackListModel trackModel,
HyenaSqliteConnection connection, string select_all_fmt, string uuid, QueryField field, string filter_column)
: base(field.Name, field.Label, source, trackModel, connection, QueryFilterInfo <T> .CreateProvider(filter_column, field), new QueryFilterInfo <T> (), String.Format("{0}-{1}", uuid, field.Name))
{
this.field = field;
this.select_all_fmt = select_all_fmt;
ReloadFragmentFormat = @"
FROM CoreTracks, CoreCache{0}
WHERE CoreCache.ModelID = {1} AND CoreCache.ItemID = {2} {3}
ORDER BY Value";
QueryFields = new QueryFieldSet(query_filter_field);
}
/// <summary>
///
/// </summary>
/// <param name="queryField"></param>
/// <param name="functionFormat"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static string AsField(this QueryField queryField,
string functionFormat,
IDbSetting dbSetting) =>
queryField.Field.Name.AsField(functionFormat, dbSetting);
public void QueryFieldGetOperatorTest1()
{
var field = new QueryField();
Assert.AreEqual("=", field.Operator);
}
/// <summary>
///
/// </summary>
/// <param name="queryField"></param>
/// <param name="index"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static string AsFieldAndParameterForBetween(this QueryField queryField,
int index,
IDbSetting dbSetting) =>
string.Concat(queryField.AsField(dbSetting), " ", queryField.GetOperationText(), " ", queryField.AsBetweenParameter(index, dbSetting));
public void QueryFieldValueTest1()
{
var field = new QueryField();
field.Value = "Value";
Assert.AreEqual("Value", field.Value);
field.Value = null;
Assert.IsNull(field.Value);
}
/// <summary>
/// Converts an instance of a query field into an enumerable list of query fields.
/// </summary>
/// <param name="queryField">The query field to be converted.</param>
/// <returns>An enumerable list of query fields.</returns>
public static IEnumerable <QueryField> AsEnumerable(this QueryField queryField)
{
yield return(queryField);
}
/// <summary>
/// 给页面赋值
/// </summary>
private void FillBalanceData()
{
//得到sharepointservice 用来进行sharepoint API操作
ISharePointService sps = ServiceFactory.GetSharePointService(true);
//获取列表
SPList listBalance = sps.GetList(CAWorkFlowConstants.ListName.LeaveBalance.ToString());
//根据field来查询
QueryField field=new QueryField("Employee");
QueryField field2 =new QueryField("Year");
SPListItemCollection items = sps.Query(listBalance, field.Equal(this.ApplicantName) && field2.Equal(DateTime.Now.Year), 1);
//如果有值
if (items.Count > 0)
{
this.labAnnulLeave.Text = items[0]["AnnualBalance"] + "";
this.labSickLeave.Text = items[0]["SickBalance"] + "";
this.labAnnualEntitlement.Text = items[0]["AnnualEntitlement"] + "";
this.labSickEntitlement.Text = items[0]["SickEntitlement"] + "";
this.Balance = Convert.ToDouble(items[0]["AnnualBalance"]);
this.SickBalance = Convert.ToDouble(items[0]["SickBalance"]);
}
else
{
this.labAnnulLeave.Text = "0";
this.labSickLeave.Text = "0";
this.labAnnualEntitlement.Text = "0";
this.labSickEntitlement.Text = "0";
}
}
/// <summary>
///
/// </summary>
/// <param name="queryField"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static string AsField(this QueryField queryField,
IDbSetting dbSetting) =>
queryField.Field.Name.AsField(dbSetting);
public static SqliteModelProvider <QueryFilterInfo <T> > CreateProvider(string filter_column, QueryField field)
{
SqliteModelProvider <QueryFilterInfo <T> > provider = new FilterModelProvider <QueryFilterInfo <T> > (
ServiceManager.DbConnection,
"CoreTracks",
"TrackID", typeof(QueryFilterInfo <T>).GetMember("DbId")[0] as PropertyInfo,
filter_column, typeof(QueryFilterInfo <T>).GetMember("Value")[0] as PropertyInfo
);
return(provider);
}
/// <summary>
///
/// </summary>
/// <param name="queryField"></param>
/// <param name="index"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static string AsParameter(this QueryField queryField,
int index,
IDbSetting dbSetting) =>
queryField.Parameter.Name.AsParameter(index, dbSetting);
/// <summary>
///
/// </summary>
/// <param name="queryField"></param>
/// <param name="index"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static string AsBetweenParameter(this QueryField queryField,
int index,
IDbSetting dbSetting) =>
AsBetweenParameter(queryField, index, null, dbSetting);
/// <summary>
///
/// </summary>
/// <param name="queryField"></param>
/// <param name="index"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static string AsParameterAsField(this QueryField queryField,
int index,
IDbSetting dbSetting) =>
string.Concat(queryField.AsParameter(index, dbSetting), " AS ", queryField.AsField(dbSetting));
//Get the list item that contains email subject and body
public static SPListItem GetEmailTemplateByTitle(string title)
{
var qTitle = new QueryField("Title", false);
CamlExpression exp = null;
exp = WorkFlowUtil.LinkAnd(exp, qTitle.Equal(title));
SPListItemCollection lc = ListQuery.Select()
.From(GetWorkflowList("EmailTemplate"))
.Where(exp)
.GetItems();
return lc.Count == 0 ? null : lc[0];
}
/// <summary>
///
/// </summary>
/// <param name="queryField"></param>
/// <param name="index"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static string AsBetweenParameter(this QueryField queryField,
int index,
IDbSetting dbSetting) =>
string.Concat(queryField.Parameter.Name.AsParameter(index, dbSetting), "_Left AND ", queryField.Parameter.Name.AsParameter(index, dbSetting), "_Right");
/// <summary>
/// 得到门店下的没有收货的PO
/// </summary>
/// <param name="sCostCenters"></param>
/// <param name="sPONO"></param>
/// <returns></returns>
public static DataTable GetNotRecievedPOForStore(string sCostCenters, string sPONO)
{
SPListItemCollection splic = null;
CamlExpression ce = null;
QueryField qfIsRecieved = new QueryField("IsReceived");
ce = WorkFlowUtil.LinkAnd(ce, qfIsRecieved.Equal(false));
QueryField PONO = new QueryField("Title");
ce = WorkFlowUtil.LinkAnd(ce, PONO.Equal(sPONO));
CamlExpression ceOR = null;
QueryField qfCostCenter = new QueryField("CostCenter");
foreach (string str in sCostCenters.Split(','))
{
ceOR = WorkFlowUtil.LinkOr(ceOR, qfCostCenter.Equal(str));
}
ce = WorkFlowUtil.LinkAnd(ce, ceOR);
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (SPSite site = new SPSite(SPContext.Current.Site.ID))
{
using (SPWeb web = site.OpenWeb(SPContext.Current.Web.ID))
{
splic = ListQuery.Select()
.From(WorkFlowUtil.GetWorkflowList("PurchaseOrderItems"))
.Where(ce)
.GetItems();
}
}
});
if (splic.Count > 0)
{
return splic.GetDataTable();
}
else
{
return null;
}
}
// AsParameterAsField
internal static string AsParameterAsField(this QueryField queryField)
{
return(string.Concat(queryField.Parameter.Name, " AS ", queryField.Field.Name.AsQuoted(true)));
}
private void GetPODetails()
{
ISharePointService sps = ServiceFactory.GetSharePointService(true);
//获取列表
SPList list = sps.GetList(CAWorkFlowConstants.ListName.PODetails + "");
QueryField field = new QueryField("RequestID", false);
SPListItemCollection items = sps.Query(list, field.Equal(string.IsNullOrEmpty(lblWorkflowNumber.Text) ? string.Empty : lblWorkflowNumber.Text), 0);
foreach (SPListItem item in items)
{
DataRow row = dtPODetails.Rows.Add();
row["RequestID"] = item["RequestID"];
row["PONumber"] = item["PONumber"];
row["Amount"] = item["Amount"];
row["ProcessDate"] = item["ProcessDate"];
row["InvoiceNumber"] = item["InvoiceNumber"];
row["DocumentNumber"] = item["DocumentNumber"];
}
//this.rptPODetails.DataSource = dtPODetails;
//this.rptPODetails.DataBind();
if (rptPODetails.Visible)
{
if (dtPODetails.Rows.Count == 0)
{
dtPODetails.Rows.Add();
}
this.rptPODetails.DataSource = dtPODetails;
this.rptPODetails.DataBind();
}
else if (rptPODetailsDisplay.Visible)
{
this.rptPODetailsDisplay.DataSource = dtPODetails;
this.rptPODetailsDisplay.DataBind();
}
else if (rptPODetailsUpdate.Visible)
{
this.rptPODetailsUpdate.DataSource = dtPODetails;
this.rptPODetailsUpdate.DataBind();
}
}
/// <summary>
///
/// </summary>
internal static void PrependAnUnderscoreAtParameter(this QueryField queryField)
{
queryField.Parameter?.PrependAnUnderscore();
}
public void QueryFieldCreateTest()
{
var field = new QueryField();
Assert.AreEqual(QueryFieldName.ProjectID, field.Name);
Assert.AreEqual(QueryFieldType.Equal, field.Type);
}
// AsParameter
internal static string AsParameter(this QueryField queryField)
{
return(string.Concat("@", queryField.Parameter.Name));
}
public void QueryFieldGetOperatorTest3()
{
var field = new QueryField();
field.Type = QueryFieldType.GreaterThanOrEqual;
Assert.AreEqual(">=", field.Operator);
}
// AsField
internal static string AsField(this QueryField queryField)
{
return(queryField.Field.Name.AsQuoted(true));
}
public void QueryFieldGetOperatorTest7()
{
var field = new QueryField();
field.Type = QueryFieldType.NotLike;
Assert.AreEqual("Not Like", field.Operator);
}
/// <summary>
/// Converts an instance of a query field into an enumerable list of query fields.
/// </summary>
/// <param name="queryField">The query field to be converted.</param>
/// <returns>An enumerable list of query fields.</returns>
public static IEnumerable <QueryField> AsEnumerable(this QueryField queryField)
{
return(new[] { queryField });
}
public void QueryFieldValueTest2()
{
var field = new QueryField();
field.Value = new DateTime(2000, 1, 1);
Assert.AreEqual(new DateTime(2000, 1, 1), field.Value);
field.Value = null;
Assert.IsNull(field.Value);
}
private SortableColumn CreateText (QueryField field, double width, bool visible)
{
return Create (field, width, visible, new ColumnCellText (field.PropertyName, true));
}
/// <summary>
///
/// </summary>
/// <param name="queryField"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static string AsField(this QueryField queryField,
IDbSetting dbSetting) =>
AsField(queryField, null, dbSetting);
private SortableColumn CreateText (QueryField field, double width)
{
return CreateText (field, width, false);
}
public SortableColumn(QueryField field)
{
this.field = field;
}
internal static IDbDataParameter AsDbParameter(this QueryField queryField, IDbDataParameter parameter)
{
parameter.ParameterName = queryField.Field.Name;
parameter.Value = queryField.Parameter.Value ?? DBNull.Value;
return(parameter);
}
/// <summary>
/// Creates a command parameter from the <see cref="QueryField"/> object.
/// </summary>
/// <param name="command">The command object to be used.</param>
/// <param name="queryField">The value of <see cref="QueryField"/> object.</param>
/// <param name="propertiesToSkip">The list of the properties to be skipped.</param>
/// <param name="entityType">The type of the data entity.</param>
internal static void CreateParameters(this IDbCommand command,
QueryField queryField,
IEnumerable <string> propertiesToSkip,
Type entityType)
{
// Exclude those to be skipped
if (propertiesToSkip?.Contains(queryField.Field.Name, StringComparer.OrdinalIgnoreCase) == true)
{
return;
}
//// Validate, make sure to only have the proper operation
//if (queryField.Operation != Operation.Equal)
//{
// throw new InvalidOperationException($"Operation must only be '{nameof(Operation.Equal)}' when calling the 'Execute' methods.");
//}
// Get the values
var value = queryField.Parameter.Value;
var valueType = value?.GetType()?.GetUnderlyingType();
var dbType = (DbType?)null;
#region PropertyHandler
// Check the property handler
var typeHandler = PropertyHandlerCache.Get <object>(valueType);
if (typeHandler != null)
{
var classProperty = (ClassProperty)null;
var setMethod = typeHandler.GetType().GetMethod("Set");
var returnType = setMethod.ReturnType;
if (entityType != null)
{
classProperty = PropertyCache.Get(entityType, queryField.Field);
}
dbType = clientTypeToDbTypeResolver.Resolve(returnType);
value = setMethod.Invoke(typeHandler, new[] { value, classProperty });
}
#endregion
#region DbType
else
{
dbType = TypeMapCache.Get(valueType);
// Check for the specialized types
if (dbType == null)
{
if (valueType?.IsEnum == true)
{
dbType = DbType.String;
}
else if (valueType == bytesType)
{
dbType = DbType.Binary;
}
}
}
#endregion
// Create the parameter
command.Parameters.Add(command.CreateParameter(queryField.Parameter.Name, value, dbType));
}
/// <summary>
///
/// </summary>
/// <param name="queryField"></param>
/// <param name="index"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
internal static string AsFieldAndParameterForIn(this QueryField queryField,
int index,
IDbSetting dbSetting) =>
AsFieldAndParameterForIn(queryField, index, null, dbSetting);
