On the problem of sorting and fetching data in H3 BPM Database

Question:

If you get the contents of the sub-table in the print template, you can get the data from the database, but the data in the current project database is entered annually, but you only need to display the data for nearly 3 years. How can the plug-in sort the data?

Solution:

1. Write a SQL statement directly to read out the data sorted in the past 3 years.

2. Load data when calling the sub-table interface

3. Example:

Var monthly = DateTime.Now.Month

Var selectsql = string.Format (@ "SELECT

T1.Yearly

T1.Monthly

T1.BudgetUnitName

T1.BudgetUnitCode

T1.BudgetItemName

T1.BudgetItemCode

T1.Budget

FROM XL_YS_BudgetPay t1

WHERE t1.Yearly = {0}

-- AND Monthly > {1}

AND t1.BudgetUnitCode ='{2}'

AND BudgetItemCode ='{3}'

ORDER BY Monthly ", yearly, monthly, budgetunitcode, budgetitemcode)

Var dt = OThinker.H3.WorkSheet.AppUtility.Engine.Query.QueryTable (selectsql)

Var list = new List ()

Var monthlist = new List {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "December"}

If (dt.Rows.Count > 0)

{

/ / DeptCostBudgetInfo is the child table name

BizObjectSchema childSchema = this.ActionContext.Schema.GetProperty ("DeptCostBudgetInfo") .ChildSchema

Var bizObject = new BizObject (this.ActionContext.Engine, childSchema, this.ActionContext.User.UserID)

BizObject ["DataType"] = "original"

BizObject ["BudgetItemName"] = dt.Rows [0] ["BudgetItemName"] .ToString ()

BizObject ["BudgetItemCode"] = dt.Rows [0] ["BudgetItemCode"] .ToString ()

Var bizObject2 = new BizObject (this.ActionContext.Engine, childSchema, this.ActionContext.User.UserID)

BizObject2 ["DataType"] = "modify"

BizObject2 ["BudgetItemName"] = dt.Rows [0] ["BudgetItemName"] .ToString ()

BizObject2 ["BudgetItemCode"] = dt.Rows [0] ["BudgetItemCode"] .ToString ()

For (int I = 0; I

< monthlist.Count; i++) { bizObject[monthlist] = dt.Rows["Budget"].ToString(); bizObject2[monthlist] = dt.Rows["Budget"].ToString(); } list.Add(bizObject); list.Add(bizObject2); } if (bos != null) { if (bos.Length >

1)

{

List.AddRange (bos)

}

}

This.ActionContext.InstanceData ["DeptCostBudgetInfo"] .Value = list.ToArray ()

ActionContext.InstanceData.Submit ()