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); }
/** * 获取有效的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; }
/* * Return data collection according to given REQUESTID and LISTNAME * 这个方法将是GetCollection(string requestId, string listName)的替代方法,以后尽量不要用上面那个,而换成用这个 */ public static SPListItemCollection GetCollectionByColumn(string column, string value, string listName) { var qColumn = new QueryField(column, false); CamlExpression exp = null; exp = WorkFlowUtil.LinkAnd(exp, qColumn.Equal(value)); 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(WorkFlowUtil.GetWorkflowList(listName)) .Where(exp) .GetItems(); } } }); return lc; }
/// <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"; } }
/* * Return travel policy item according to "TO" value */ protected SPListItem GetTravelPolicyByCity(string to) { var qToEN = new QueryField("Title", false); var qToCN = new QueryField("City", false); CamlExpression exp = null; exp = WorkFlowUtil.LinkOr(exp, qToEN.Equal(to)); exp = WorkFlowUtil.LinkOr(exp, qToCN.Equal(to)); SPListItemCollection lc = ListQuery.Select() .From(WorkFlowUtil.GetWorkflowList("Travel Policy")) .Where(exp) .GetItems(); return lc.Count > 0 ? lc[0] : null; }
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(); } }
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; }
/// <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; } }
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 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]; }
protected bool isExistRunningVendor(string vendId, string departmentVal) { var qStatus = new QueryField("Status", false); var qVendId = new QueryField("Vendor_x0020_ID", false); var qDepartmentVal = new QueryField("DepartmentVal", false); CamlExpression exp = null; exp = WorkFlowUtil.LinkAnd(exp, qVendId.Equal(vendId)); exp = WorkFlowUtil.LinkAnd(exp, qDepartmentVal.Equal(departmentVal)); //the status should not be "pending", "notstart", "financemanagerreject", "cforeject". var status = CAWorkflowStatus.Pending; exp = WorkFlowUtil.LinkAnd(exp, qStatus.NotEqual(status)); status = CAWorkflowStatus.Completed; exp = WorkFlowUtil.LinkAnd(exp, qStatus.NotEqual(status)); status = CAWorkflowStatus.NTVDepartmentHeadReject; exp = WorkFlowUtil.LinkAnd(exp, qStatus.NotEqual(status)); status = CAWorkflowStatus.NTVCFOReject; exp = WorkFlowUtil.LinkAnd(exp, qStatus.NotEqual(status)); status = CAWorkflowStatus.NTVFinanceReject; exp = WorkFlowUtil.LinkAnd(exp, qStatus.NotEqual(status)); var result = ListQuery.Select().From(WorkFlowUtil.GetWorkflowList("Non Trade Supplier Setup Maintenance Workflow")) .Where(exp) .OrderBy(new QueryField("Title", false), true) .GetItems(); return result.Count > 0; }
protected SPListItem GetVendorById(string vendId, string departmentVal) { var qVendId = new QueryField("Vendor_x0020_ID", false); var qDepartmentVal = new QueryField("DepartmentVal", false); var qStatus = new QueryField("Status", false); var status = CAWorkflowStatus.Completed; CamlExpression exp = null; exp = WorkFlowUtil.LinkAnd(exp, qVendId.Equal(vendId)); exp = WorkFlowUtil.LinkAnd(exp, qDepartmentVal.Equal(departmentVal)); exp = WorkFlowUtil.LinkAnd(exp, qStatus.Equal(status)); SPListItemCollection lc = ListQuery.Select() .From(WorkFlowUtil.GetWorkflowList("Non Trade Supplier Setup Maintenance Workflow")) .Where(exp) .OrderBy(new QueryField("Created", false), false) .GetItems(); return lc.Count > 0 ? lc[0] : null; }
public static int CreateWorkFlowNumber(string workflowName) { int nNum = 1; CA.SharePoint.ISharePointService sps = ServiceFactory.GetSharePointService(true); SPList list = sps.GetList(CAWorkFlowConstants.ListName.WorkFlowNumber.ToString()); QueryField field = new QueryField("Title"); SPListItemCollection items = sps.Query(list, field.Equal(workflowName), 1, null); if (items != null && items.Count > 0) { SPListItem item = list.GetItemById(items[0].ID); nNum = 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; }
public static void RemoveExistingRecord(SPList list, string key, string val) { CA.SharePoint.ISharePointService sps = CA.SharePoint.ServiceFactory.GetSharePointService(true); QueryField field = new QueryField(key, false); SPListItemCollection items = sps.Query(list, field.Equal(val), 0); for (int nIndex = items.Count - 1; nIndex >= 0; nIndex--) { items[nIndex].Web.AllowUnsafeUpdates = true; items[nIndex].Delete(); } }
private SPListItemCollection FilterChangeHistory(string orderNumber, string department, bool isCompleted) { string status = null; var qOrderNumber = new QueryField("Order_x0020_Number", false); var qStatus = new QueryField("Status", false); var qDepartment = new QueryField("Department", false); CamlExpression exp = null; exp = WorkFlowUtil.LinkAnd(exp, qOrderNumber.Equal(orderNumber)); if (department.IsNotNullOrWhitespace()) { exp = WorkFlowUtil.LinkAnd(exp, qDepartment.Equal(department)); } if (isCompleted) { status = CAWorkflowStatus.Completed; exp = WorkFlowUtil.LinkAnd(exp, qStatus.Equal(status)); } else { //the status should not be "pending", "notstart", "financemanagerreject", "cforeject". status = CAWorkflowStatus.Pending; exp = WorkFlowUtil.LinkAnd(exp, qStatus.NotEqual(status)); status = CAWorkflowStatus.Completed; exp = WorkFlowUtil.LinkAnd(exp, qStatus.NotEqual(status)); status = CAWorkflowStatus.IODepartmentManagerReject; exp = WorkFlowUtil.LinkAnd(exp, qStatus.NotEqual(status)); status = CAWorkflowStatus.IOCFOReject; exp = WorkFlowUtil.LinkAnd(exp, qStatus.NotEqual(status)); status = CAWorkflowStatus.IOFinanceReject; exp = WorkFlowUtil.LinkAnd(exp, qStatus.NotEqual(status)); } SPListItemCollection lc = ListQuery.Select() .From(WorkFlowUtil.GetWorkflowList("Internal Order Maintenance Workflow")) .Where(exp) .OrderBy(new QueryField("Title", false), false) .GetItems(); return lc; }
void Export() { SPListItem item = SPContext.Current.ListItem; ISharePointService sps = ServiceFactory.GetSharePointService(true); SPList items = sps.GetList("Store Maintenance Items2"); QueryField field = new QueryField("WorkflowNumber", false); SPListItemCollection coll = sps.Query(items, field.Equal(item["WorkflowNumber"] + ""),0); int len = coll.Count; if (len > 0) { string strFilePath = Server.MapPath("standard PO.xls"); string strCostCenter = item["CostCenter"] + ""; string strDate = item["Created"] + ""; //string strTotalPrice decimal coltotal = 0; string strInstallation = "0.00"; string strFreigh = "0.00"; string strPackaging = "0.00"; string strFileName = this.DataForm1.WorkflowNumber + ".xls"; string strPath = Server.MapPath("/tmpfiles/excel"); DirectoryInfo dinfo = new DirectoryInfo(strPath); if (!dinfo.Exists) { Directory.CreateDirectory(strPath); } string strSavePath = strPath + "/" + item["WorkflowNumber"] + ".xls"; GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8"); GemBox.Spreadsheet.ExcelFile objExcelFile = new ExcelFile(); objExcelFile.LoadXls(strFilePath); GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets[0]; GemBox.Spreadsheet.ExcelWorksheet worksheet2 = objExcelFile.Worksheets[1]; //objExcelFile = new ExcelFile(); //GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets.AddCopy(worksheet1.Name, worksheet1); //GemBox.Spreadsheet.ExcelWorksheet worksheet2 = objExcelFile.Worksheets.AddCopy(worksheet2.Name, worksheet2); int j = 0; int k = 0; for (int i = 10; i < 10 + len; i++) { decimal price = decimal.Parse(coll[k]["Price"] + ""); decimal total = decimal.Parse(coll[k]["Total"] + ""); int quantity = int.Parse(coll[k]["Quantity"] + ""); decimal rowtotal = price * quantity; coltotal += rowtotal; worksheet1.Rows[i].InsertEmpty(1); worksheet1.Cells[i, 0].Value = ++j; worksheet1.Cells[i, 1].Value = coll[k]["Seq"].ToString(); worksheet1.Cells[i, 2].Value = strCostCenter; worksheet1.Cells.GetSubrangeAbsolute(i, 3, i, 6).Merged = true; worksheet1.Cells[i, 3].Value = coll[k]["Reason"].ToString(); worksheet1.Cells[i, 7].Value = strDate; worksheet1.Cells.GetSubrangeAbsolute(i, 8, i, 9).Merged = true; worksheet1.Cells[i, 8].Value = coll[k]["Quantity"].ToString(); //worksheet1.Cells[i, 10].Value = coll[k]["Unit"].ToString(); worksheet1.Cells.GetSubrangeAbsolute(i, 11, i, 14).Merged = true; worksheet1.Cells[i, 14].Value = price.ToString("N2"); worksheet1.Cells.GetSubrangeAbsolute(i, 15, i, 19).Merged = true; worksheet1.Cells[i, 19].Value = total.ToString("N2");//rowtotal.ToString("N2"); worksheet1.Cells.GetSubrangeAbsolute(i, 20, i, 24).Merged = true; worksheet1.Cells[i, 24].Value = ""; k++; } worksheet1.Cells[11 + len, 0].Value = coltotal.ToString("N2"); worksheet1.Cells[11 + len, 2].Value = strInstallation; worksheet1.Cells[11 + len, 4].Value = strPackaging; worksheet1.Cells[11 + len, 6].Value = strFreigh; worksheet1.Cells[11 + len, 9].Value = coltotal.ToString("N2"); j = 0; k = 0; for (int i = 10; i < 10 + len; i++) { decimal price = decimal.Parse(coll[k]["Price"] + ""); decimal total = decimal.Parse(coll[k]["Total"] + ""); int quantity = int.Parse(coll[k]["Quantity"] + ""); decimal rowtotal = price * quantity; worksheet2.Rows[i].InsertEmpty(1); worksheet2.Cells[i, 0].Value = ++j; worksheet2.Cells[i, 1].Value = coll[k]["Seq"].ToString(); worksheet2.Cells[i, 2].Value = strCostCenter; worksheet2.Cells.GetSubrangeAbsolute(i, 3, i, 6).Merged = true; worksheet2.Cells[i, 3].Value = coll[k]["Reason"].ToString(); worksheet2.Cells[i, 7].Value = ""; worksheet2.Cells.GetSubrangeAbsolute(i, 8, i, 9).Merged = true; worksheet2.Cells[i, 8].Value = coll[k]["Quantity"].ToString(); //worksheet2.Cells[i, 10].Value = coll[k]["Unit"].ToString(); worksheet2.Cells.GetSubrangeAbsolute(i, 11, i, 14).Merged = true; worksheet2.Cells[i, 14].Value = price.ToString("N2"); worksheet2.Cells.GetSubrangeAbsolute(i, 15, i, 19).Merged = true; worksheet2.Cells[i, 19].Value = total.ToString("N2");//rowtotal.ToString("N2"); worksheet2.Cells.GetSubrangeAbsolute(i, 20, i, 24).Merged = true; worksheet2.Cells[i, 24].Value = ""; k++; } worksheet2.Cells[11 + len, 0].Value = coltotal.ToString("N2"); worksheet2.Cells[11 + len, 2].Value = strInstallation; worksheet2.Cells[11 + len, 4].Value = strPackaging; worksheet2.Cells[11 + len, 6].Value = strFreigh; worksheet2.Cells[11 + len, 9].Value = coltotal.ToString("N2"); if (File.Exists(strSavePath)) { File.Delete(strSavePath); } objExcelFile.SaveXls(strSavePath); } }
internal static SPListItem GetTravelPolicyByArea(string area) { var qLocation = new QueryField("Location", false); CamlExpression exp = null; exp = WorkFlowUtil.LinkAnd(exp, qLocation.Equal(area)); SPListItemCollection lc = ListQuery.Select() .From(WorkFlowUtil.GetWorkflowList("Travel Policy")) .Where(exp) .GetItems(); return lc.Count > 0 ? lc[0] : null; }
/* * 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; }