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;
}
/*
* 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 SPListItemCollection FilterColl()
{
lblTmpXlsUrl.Text = "";
//string fEmployee = txtfEmployee.Text;
string fYear = txtfYear.Text;
string fAnnualEntitlement = txtfAnnualEntitlement.Text;
string fSickEntitlement = txtfSickEntitlement.Text;
string fDepartments = ddlDepartments.SelectedValue;//txtfDepartments.Text;
QueryField qEmployee = new QueryField("Employee", false);
QueryField qYear = new QueryField("Year", false);
QueryField qAnnualEntitlement = new QueryField("AnnualEntitlement", false);
QueryField qSickEntitlement = new QueryField("SickEntitlement", false);
//QueryField qDepartment = new QueryField("Department", false);
CamlExpression exp = null;
if (!string.IsNullOrEmpty(fYear))
{
exp = LinkAnd(exp, qYear.Contains(fYear));
}
if (!string.IsNullOrEmpty(fAnnualEntitlement))
{
exp = LinkAnd(exp, qAnnualEntitlement.Equal(fAnnualEntitlement));
}
if (!string.IsNullOrEmpty(fSickEntitlement))
{
exp = LinkAnd(exp, qSickEntitlement.Equal(fSickEntitlement));
}
//if (!string.IsNullOrEmpty(hidAssoDepts.Value))
//{
// CamlExpression exp2 = null;
// if (!string.IsNullOrEmpty(fDepartments))
// {
// exp2 = qDepartment.Equal(fDepartments);
// }
// else
// {
// string[] depts = hidAssoDepts.Value.Split(',');
// foreach (string dept in depts)
// {
// exp2 = LinkOr(exp2, qDepartment.Equal(dept));
// }
// }
// exp = LinkAnd(exp, exp2);
//}
if (chkIsReportToMe.Checked && !SPContext.Current.Web.CurrentUser.IsSiteAdmin)
{
List<string> directReports = GetDirectReportsInternal(SPContext.Current.Web.CurrentUser.Name, fDepartments);
CamlExpression exp3 = null;
if (directReports.Count > 0)
{
foreach (var report in directReports)
{
exp3 = LinkOr(exp3, qEmployee.Equal(report));
}
}
else
{
exp3 = qEmployee.Equal("");
}
exp = LinkAnd(exp, exp3);
}
return ListQuery.Select()
.From(SPContext.Current.Site.OpenWeb("workflowcenter").Lists["LeaveBalance"])
.Where(exp)
.OrderBy(new QueryField("Year", false), false)
.OrderBy(new QueryField("Employee", false), true)
.GetItems();
}
private SPListItemCollection FilterColl()
{
lblTmpXlsUrl.Text = "";
string fApplicant = txtfApplicant.Text;
string fLeaveType = ddlLeaveType.SelectedValue;//txtfLeaveType.Text;
string fLeaveDays = txtfLeaveDays.Text;
string fDateFrom = txtfDateFrom.Text;
string fDateTo = txtfDateTo.Text;
string fDepartments = ddlDepartments.SelectedValue;//txtfDepartments.Text;
QueryField qApplicant = new QueryField("Applicant", false);
QueryField qLeaveType = new QueryField("LeaveType", false);
QueryField qLeaveDays = new QueryField("LeaveDays", false);
QueryField qDateFrom = new QueryField("DateFrom", false);
QueryField qDateTo = new QueryField("DateTo", false);
QueryField qDepartment = new QueryField("Department", false);
QueryField qStatus = new QueryField("Status", false);
CamlExpression exp = null;
CamlExpression exp2 = null;
var alldepts = new List<string>();
foreach (SPListItem dept in SPContext.Current.Site.RootWeb.Lists["Department"].Items)
{
alldepts.Add(dept["Name"] + "");
}
if (!string.IsNullOrEmpty(fApplicant))
{
exp = LinkAnd(exp, qApplicant.Contains(fApplicant));
}
if (!string.IsNullOrEmpty(fLeaveType))
{
exp = LinkAnd(exp, qLeaveType.Contains(fLeaveType));
}
if (!string.IsNullOrEmpty(fLeaveDays))
{
exp = LinkAnd(exp, qLeaveDays.Equal(fLeaveDays));
}
if (!string.IsNullOrEmpty(fDateFrom))
{
exp = LinkAnd(exp, qDateTo >= fDateFrom);
}
if (!string.IsNullOrEmpty(fDateTo))
{
exp = LinkAnd(exp, qDateFrom <= fDateTo);
}
if (!string.IsNullOrEmpty(hidAssoDepts.Value))
{
if (!string.IsNullOrEmpty(fDepartments))
{
//exp2 = qDepartment.Equal(fDepartments);
if (ddlDepartments.SelectedValue.ToLower().Contains("store"))
{
//选中的是store，查 不包含在departments list里的员工
foreach (string dept in alldepts)
{
exp2 = LinkAnd(exp2, qDepartment.NotEqual(dept));
}
}
else
{
//选中的是别的部门，查 匹配该部门的员工
exp2 = qDepartment.Equal(fDepartments);
}
}
else
{
string[] depts = hidAssoDepts.Value.Split(',');
//foreach (string dept in depts)
//{
// exp2 = LinkOr(exp2, qDepartment.Equal(dept));
//}
if (hidAssoDepts.Value.ToLower().Contains("store"))
{
//如果权限里含有store，查满足 匹配权限 或者 不在alldepts中的
foreach (string dept in depts)
{
exp2 = LinkOr(exp2, qDepartment.Equal(dept));
}
CamlExpression exp3 = null;
foreach (string dept in alldepts)
{
exp3 = LinkAnd(exp3, qDepartment.NotEqual(dept));
}
exp2 = LinkOr(exp2, exp3);
}
else
{
//如果权限里不含store，查满足 匹配权限 的
foreach (string dept in depts)
{
exp2 = LinkOr(exp2, qDepartment.Equal(dept));
}
}
}
exp = LinkAnd(exp, exp2);
}
if (chkIsReportToMe.Checked && !SPContext.Current.Web.CurrentUser.IsSiteAdmin)
{
List<string> directReports = GetDirectReportsInternal(SPContext.Current.Web.CurrentUser.Name, fDepartments);
exp2 = null;
if (directReports.Count > 0)
{
foreach (var report in directReports)
{
exp2 = LinkOr(exp2, qApplicant.Equal(report));
}
}
else
{
exp2 = qApplicant.Equal("");
}
exp = LinkAnd(exp, exp2);
}
exp = LinkAnd(exp, qStatus.NotEqual("Cancelled"));
exp = LinkAnd(exp, qStatus.NotEqual("Canceled"));
return ListQuery.Select()
.From(SPContext.Current.Site.OpenWeb("workflowcenter").Lists["LeaveRecord"])
.Where(exp)
.OrderBy(new QueryField("Applicant", false), true)
.OrderBy(new QueryField("DateFrom", false), true)
.GetItems();
}
/**
* 查询当前帐号对应的已产生PO的且为未收货状态的Item
*/
private SPListItemCollection GetPOItemCollection(string sPONumber,string sPRNumber)
{
var qIsReceived = new QueryField("IsReceived", false);
QueryField qCostCenter = new QueryField("CostCenter", false);
QueryField qCreated = new QueryField("Created", false);
CamlExpression exp = null;
exp = WorkFlowUtil.LinkAnd(exp, qIsReceived.NotEqual(true));
string sUserName = SPContext.Current.Web.CurrentUser.LoginName;
if (PurchaseRequestCommon.IsInGroups(sUserName, new string[] { "wf_Store" }))
{
List<string> costCenters = GetOwnCostCenters(SPContext.Current.Web.CurrentUser.LoginName);
if (costCenters.Count == 0)
{
return null;
}
CamlExpression ceOR = null;
foreach (var costCenter in costCenters)
{
ceOR = WorkFlowUtil.LinkOr(ceOR, qCostCenter.Equal(costCenter));
}
exp = WorkFlowUtil.LinkAnd(exp, ceOR);
}
else if (PurchaseRequestCommon.IsInGroups(sUserName, new string[] { "wf_HO" }))//如果是wf_HO
{
QueryField qAuthor = new QueryField("Author", false);
exp = WorkFlowUtil.LinkAnd(exp, qAuthor.Equal(SPContext.Current.Web.CurrentUser.Name));
HFIsHO.Value = "True";
}
else if (PurchaseRequestCommon.IsInGroups(sUserName, new string[] { "w_PMGR" }))
{
QueryField qfTitle = new QueryField("Title", false);
exp = WorkFlowUtil.LinkAnd(exp, qfTitle.BeginsWith("H"));
}
int iPO = sPONumber.Length;
int iPR = sPRNumber.Length;
if (iPO > 0)//按PONumber查询
{
QueryField qTitle = new QueryField("Title", false);
exp = WorkFlowUtil.LinkAnd(exp, qTitle.Equal(sPONumber));
}
if (iPR > 0)
{
QueryField qPRNO = new QueryField("PRNumber", false);
exp = WorkFlowUtil.LinkAnd(exp, qPRNO.Equal(sPRNumber));
}
if (iPO > 0 || iPR > 0)
{
QueryField qTitle = new QueryField("Title", false);
CamlExpression ce = null;
ce = WorkFlowUtil.LinkAnd(ce,qIsReceived.NotEqual(true));
ce = WorkFlowUtil.LinkAnd(ce, qTitle.Contains("R"));
exp = WorkFlowUtil.LinkOr(ce, exp);
}
SPListItemCollection lc = ListQuery.Select()
.From(SPContext.Current.Web.Lists["PurchaseOrderItems"])
.Where(exp)
.GetItems();
return lc;
}
