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