public static List <ChartDetails> Getaverageweekly() { DataTable dt = WebService.Getdataactive(); string[] selectedColumns = new[] { "cases", "province" }; DataTable dt1 = new DataView(dt).ToTable(false, selectedColumns); var data = dt1.AsEnumerable(); var query = from d in data group d by d.Field <string>("province") into g select new { Prov = g.Key, Avg = g.Average(e => (e.Field <long>("cases"))) }; DataTable dt2 = new DataTable(); dt2.Columns.Add("Province"); dt2.Columns.Add("Cases"); foreach (var q in query) { DataRow dr = dt2.NewRow(); dr["Province"] = q.Prov; dr["Cases"] = q.Avg; dt2.Rows.Add(dr); } dt2.Rows[0]["Province"] = "Alberta"; dt2.Rows[1]["Province"] = "British Columbia"; dt2.Rows[2]["Province"] = "Manitoba"; dt2.Rows[3]["Province"] = "New Brunswick"; dt2.Rows[4]["Province"] = "Newfoundland and Labrador"; dt2.Rows[5]["Province"] = "Nova Scotia"; dt2.Rows[6]["Province"] = "Nunavut"; dt2.Rows[7]["Province"] = "Northwest Territories"; dt2.Rows[8]["Province"] = "Ontario"; dt2.Rows[9]["Province"] = "Prince Edward Island"; dt2.Rows[10]["Province"] = "Quebec"; dt2.Rows[11]["Province"] = "Saskatchewan"; dt2.Rows[12]["Province"] = "Yukon"; List <ChartDetails> dataList = new List <ChartDetails>(); foreach (DataRow dtrow in dt2.Rows) { ChartDetails details = new ChartDetails(); details.province = dtrow[0].ToString(); details.average = Math.Ceiling(Convert.ToDecimal(dtrow[1].ToString())); dataList.Add(details); } return(dataList); //ScriptManager.RegisterStartupScript(this, this.GetType(), "average", "drawchart('" + dataList + "');", true); }
/// <summary> /// DataTable转换到List /// </summary> /// <param name="dt"></param> /// <returns></returns> private void DataTableToList(DataTable dt) { //获取最原始的List var orgList = (from dataTable in dt.AsEnumerable() select new PropertyNodeItem() { ParentName = dataTable["OWNER"].ToString(), ChildrenName = dataTable["TABLE_NAME"].ToString() }).ToList(); OrgList.AddRange(orgList); DataTable newdt = new DataView(dt.Columns["OWNER"].Table).ToTable(true, "OWNER");//去除重复,只留下OWNER //只获得Owner列表 var newList = (from dataTable in newdt.AsEnumerable() select dataTable["OWNER"].ToString()); OwnerList.AddRange(newList); }
/// <summary> /// Gets the full query document. /// </summary> /// <returns></returns> public string GetFullQueryDocument(string categoryName = "") { ClsObject queryManager = new ClsObject(); ClsUtility.Init_Hashtable(); ClsUtility.AddParameters("@Category", SqlDbType.VarChar, categoryName); string queryStatement = @"Select B.sbCategory SubCategory, Category, B.catID CategoryID, B.sbCatID SubCategoryID, Q.qryID Query_ID, Q.qryName Query_Name, Q.qryDescription Description From dbo.aa_Category A Inner Join dbo.aa_sbCategory B On A.CatID = B.CatID Inner Join dbo.aa_Queries Q On Q.qryID = B.QryID Where (B.DeleteFlag = 0 Or B.DeleteFlag Is Null) And (Q.DeleteFlag = 0 Or Q.DeleteFlag Is Null) And Case When @Category Is Null Or A.Category = @Category Then 1 Else 0 End = 1;" ; DataTable dt = (DataTable)queryManager.ReturnObject(ClsUtility.theParams, queryStatement, ClsUtility.ObjectEnum.DataTable, ConnectionMode.REPORT); this.InitialiseQueryDocument(); if (dt != null && dt.Rows.Count > 0) { string[] selectedColumns = new[] { "Category", "CategoryID" }; string[] selectedSubsColumns = new[] { "Category", "CategoryID", "SubCategory" }; DataTable cat = new DataView(dt).ToTable(true, selectedColumns); DataTable subcat = new DataView(dt).ToTable(true, selectedSubsColumns); foreach (DataRow dr in cat.Rows) { XElement R = new XElement("Category", new XAttribute("ID", dr["CategoryID"].ToString()), new XAttribute("Name", dr["Category"].ToString()), (from sub in subcat.AsEnumerable() where (sub["Category"].ToString() == dr["Category"].ToString()) select new XElement("SubCategory", new XAttribute("Name", sub["SubCategory"].ToString()), (from row in dt.AsEnumerable() where row["SubCategory"].ToString() == sub["SubCategory"].ToString() select new XElement("Query", new XElement("Query_ID", row["Query_ID"].ToString()), new XElement("Name", row["Query_Name"].ToString()), new XElement("Description", row["Description"].ToString()), new XElement("SubCategoryID", row["SubCategoryID"].ToString()) ) ) ) ) ); XmlElement rootElement = this.queryXML.DocumentElement; XmlDocumentFragment queryNode = queryXML.CreateDocumentFragment(); queryNode.InnerXml = R.ToString(); rootElement.AppendChild(queryNode); } //var s = (from crow in cat.AsEnumerable() // select new XElement("Category", // new XAttribute("ID", crow["CategoryID"].ToString()), // new XAttribute("Name", crow["Category"].ToString()), // (from sub in subcat.AsEnumerable() // select new XElement("SubCategory", // new XAttribute("Name", sub["SubCategory"].ToString()), // (from row in dt.AsEnumerable() // select new XElement("Query", // new XElement("Query_ID", row["Query_ID"].ToString()), // new XElement("Name", row["Query_Name"].ToString()), // new XElement("Description", row["Description"].ToString()), // new XElement("SubCategoryID", row["SubCategoryID"].ToString()) // ) // ) // ) // ) // ) // ); } return(this.queryXML.OuterXml.ToString()); }
public static string ExtractStmtData(AccountDetails details, string CBAConstr, string ClirecConstr, HttpContext context, string CBAType, string MsgType) { string uuid = Guid.NewGuid().ToString(); DataTable data = new DataTable(); try { var db = new DBConnector(CBAConstr, ClirecConstr); Utils.Log("Beginging Data Extraction for (" + details.AccountCode + ") using Interface definition ID (" + details.DefinitionID + ")"); InterfaceDefinition def = db.getDefinition(details.DefinitionID); string mappedAccts = ""; var map = new StringBuilder(); int ct = 1; foreach (string cba in details.StmtCBAccount) { map.Append("'").Append(cba).Append("'"); if (ct < details.StmtCBAccount.Count) { map.Append(","); } ct++; } mappedAccts = map.ToString(); //string sql = def.script.Replace("startdate", getDate(details.LastLedgerDate)). // Replace("enddate", details.Endate).Replace("acctid", mappedAccts). // Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode); Utils.Log("Connecting to transaction Database"); var sql = ""; string Startdate, Enddate; Startdate = getDate(details.LastStmtDate); Enddate = details.Endate.toDateTime().ToString("dd-MMM-yyyy"); switch (CBAType.CleanUp()) { case "mysql": sql = def.script.Replace("startdate", details.LastStmtDate.ToString("yyyy-MM-dd")). Replace("enddate", Convert.ToDateTime(details.Endate).ToString("yyyy-MM-dd")).Replace("acctid", mappedAccts). Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode).Replace("mtype", MsgType); Utils.Log(sql); data = db.fetchMySQLCBA(sql); break; case "sqlserver": sql = def.script.Replace("startdate", Startdate). Replace("enddate", Enddate).Replace("acctid", mappedAccts). Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode).Replace("mtype", MsgType); Utils.Log(sql); data = db.getSQLServerCBA(sql); break; default: sql = def.script.Replace("startdate", Startdate). Replace("enddate", Enddate).Replace("acctid", mappedAccts). Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode).Replace("mtype", MsgType); Utils.Log(sql); data = db.fetchCBA(sql); break; } int nofT = data.Rows.Count; bool isData = nofT > 0; Utils.Log(nofT + " transaction(s) fetched"); int sn = 1; data = Utils.AddExtractionColumns(data); foreach (DataRow row in data.Rows) { row["PostDate"] = row[def.postDateCol].toDateTime(); row["Valdate"] = row[def.valDateCol].toDateTime(); bool isDebit = row[def.directionCol].ToString().ToUpper().Equals("D"); row["CrDr"] = (isDebit ? "1" : "2"); row["Debits"] = (isDebit ? row[def.amountCol].toDecimal() : decimal.Zero); row["Credits"] = (isDebit ? decimal.Zero : row[def.amountCol].toDecimal()); row["Amount"] = row["Credits"].toDecimal() + row["Debits"].toDecimal(); getUDFs(row, def); row["Details"] = getNaration(def, row); row["SN"] = sn++; row["Id"] = uuid; row["username"] = "******"; } var balance = decimal.Zero; if (isData) { Utils.Log("Attempting to Fetch Account Balance"); SqlConnection conn = new SqlConnection(); foreach (string cba in details.StmtCBAccount) { DataTable baldata; switch (CBAType.CleanUp()) { case "mysql": sql = def.balScript.Replace("startdate", Convert.ToDateTime(Utils.getFirstDayofMonth(details.CurrentDate)).ToString("yyyy-MM-dd")) .Replace("enddate", Convert.ToDateTime(details.Endate).ToString("yyyy-MM-dd")).Replace("acctid", cba). Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode); baldata = db.fetchMySQLCBA(sql); break; case "sqlserver": conn.ConnectionString = CBAConstr; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 0; sql = def.balScript.Replace("startdate", Utils.getFirstDayofMonth(details.CurrentDate)) .Replace("enddate", Enddate).Replace("acctid", cba). Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode); Utils.Log(sql); cmd.CommandText = sql; baldata = db.fetchCBA(cmd); break; default: sql = def.balScript.Replace("startdate", Utils.getFirstDayofMonth(details.CurrentDate)) .Replace("enddate", Enddate).Replace("acctid", cba). Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode); baldata = db.fetchCBA(sql); break; } if (baldata.Rows.Count > 0) { balance += baldata.Rows[0][def.balCol].toDecimal(); } // Utils.Log(sql); Utils.Log("Balance downloaded successfully and = " + balance.ToString("0.00")); conn.Close(); } string[] selected = new[] { "SN", "PostDate", "Valdate", "Details", "Debits", "Credits", "Amount", "CrDr", "ud1", "ud2", "ud3", "ud4", "ud5", "Id", "username" }; data = new DataView(data).ToTable(false, selected); context.Session["TotalExempted"] = 0; context.Session["side"] = "S"; context.Session["TotalExtraction"] = data.Rows.Count; context.Session["ToatlCredits"] = Convert.ToDecimal(data.Compute("Sum(Credits)", "")); context.Session["ToatlDebits"] = Convert.ToDecimal(data.Compute("Sum(Debits)", "")); context.Session["latest"] = Convert.ToDateTime(data.Compute("Max(PostDate)", "")); context.Session["CreditCount"] = data.Compute("count(CrDr)", "CrDr ='2' ").toInt(); context.Session["DebitCount"] = data.Compute("count(CrDr)", "CrDr ='1' ").toInt(); var fp = from row in data.AsEnumerable() where Convert.ToInt32(row["SN"]) <= 1000 orderby row["SN"] select row; context.Session["FirstPage"] = fp.CopyToDataTable(); db.CopyDataTableToDB(data, "ExtractionTemp"); } var ID = uuid; return((isData) ? ID + "$" + balance.ToString("0.00") : "No Data Pulled!"); } catch (Exception e) { throw new Exception("CBA Download failed for " + details.AccountName + "(" + details.AccountCode + ") because: " + e.Message); } finally { data.Clear(); data.Dispose(); } }