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;
}
