// GET: DQManager
public ActionResult Index()
{
DatasetManager dm = new DatasetManager(); //dataset manager
ManageDQ manageModel = new ManageDQ();
//DatasetVersion dsv = new DatasetVersion(); //dataset version manager
List <long> datasetIds = dm.GetDatasetLatestIds(); //get latest
//List<List<long>> matrixId = new List<List<long>>();
List <dataset> datasets = new List <dataset>();
foreach (long Id in datasetIds) //for each dataset
{
dataset ds = new dataset();
ds.Id = Id;
ds.title = dm.GetDatasetLatestVersion(Id).Title;
//List<DatasetVersion> datasetVersions = dm.GetDatasetVersions(Id);
//List<long> versionIds = new List<long>();
//for (int i = 0; i < datasetVersions.Count; ++i)
//{
// long versionId = datasetVersions[i].Id;
// versionIds.Add(versionId);
//}
////matrixId.Add(versions);
//ds.versionIds = versionIds;
datasets.Add(ds);
}
//manageModel.matrixId = matrixId;
manageModel.datasets = datasets;
return(View(manageModel));
}
private void kasReport()
{
_report a = new _report();
string iQry = @"select
date,
code,
concat(surname, ', ', firstname, ' ', middlename) name,
brgy,
sex,
cp_no,
status,
remarks
from kasambahay2
where date between '{0}' and '{1}'
and brgy like '%%{2}%%'";
string qry = string.Format(iQry, dateTimePicker1.Text, dateTimePicker2.Text, comboBox5.Text);
dataset ds = new dataset();
using (MySqlConnection conn = new MySqlConnection(DBConn.connstring))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(qry, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, ds.Tables["kasReport"].TableName);
_cr_kasReport rep = new _cr_kasReport();
rep.SetDataSource(ds);
a.crystalReportViewer1.ReportSource = rep;
a.ShowDialog();
}
}
private void button6_Click(object sender, EventArgs e)
{
_report a = new _report();
string iQry = @"select
date,
code,
concat(surname, ', ', firstname, ' ', middlename) name,
address,
gender,
disability,
remarks
from pwd
where date between '{0}' and '{1}'";
string qry = string.Format(iQry, dateTimePicker1.Text, dateTimePicker2.Text, comboBox5.Text);
dataset ds = new dataset();
using (MySqlConnection conn = new MySqlConnection(DBConn.connstring))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(qry, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, ds.Tables["pwdReport"].TableName);
_cr_pwd rep = new _cr_pwd();
rep.SetDataSource(ds);
a.crystalReportViewer1.ReportSource = rep;
a.ShowDialog();
}
}
private void button6_Click(object sender, EventArgs e)
{
_report a = new _report();
string iQry = @"select
date `DATE`,
code `CODE`,
establishment_name `ESTABLISHMENT`,
acronym `ACRONYM`,
tin `TIN`,
contact_person `CONTACT_PERSON`,
tel `TEL`,
type `TYPE`
from rwa
where date between '{0}' and '{1}'";
string qry = string.Format(iQry, dateTimePicker1.Text, dateTimePicker2.Text);
dataset ds = new dataset();
using (MySqlConnection conn = new MySqlConnection(DBConn.connstring))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(qry, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, ds.Tables["rwaReport"].TableName);
_cr_rwa rep = new _cr_rwa();
rep.SetDataSource(ds);
a.crystalReportViewer1.ReportSource = rep;
a.ShowDialog();
}
}
private dataset[] builLineChartDataPoints(List <ReportVM> objData, string[] listOfMonths)
{
ColorsList = TempData.Peek("Colors") as List <MLookup>;
if (ColorsList == null || ColorsList.Count == 0)
{
ColorsList = new List <MLookup>();
ColorsList.Add(new MLookup()
{
ValueSet = "rgb(54, 162, 235)", Flag = true
});
ColorsList.Add(new MLookup()
{
ValueSet = "rgb(117, 27, 8)", Flag = true
});
}
var Linecolors = ColorsList.Select(s => s.ValueSet).ToArray();
List <dataset> objdatasetList = new List <dataset>();
{
dataset objMonthTotal = new dataset()
{
label = "NYSPFPRATE", backgroundColor = Linecolors[0], borderColor = Linecolors[0], fill = false, lineTension = 0
};
decimal[] arrMonthTotal = new decimal[listOfMonths.Length];
for (int j = 0; j < listOfMonths.Length; j++)
{
string monthandYear = listOfMonths[j];
var filteredData = objData.Where(w => w.MONTHTEXT == monthandYear.Split(' ')[0] && w.Year == Convert.ToInt32(monthandYear.Split(' ')[1]));
arrMonthTotal[j] = (filteredData != null && filteredData.Count() > 0) ? Convert.ToDecimal(filteredData.Sum(S => Convert.ToDecimal(S.PFPRATE))) : 0;
}
objMonthTotal.data = arrMonthTotal;
objdatasetList.Add(objMonthTotal);
dataset objHospMonthTotal = new dataset()
{
label = "HOSPRATE", backgroundColor = Linecolors[1], borderColor = Linecolors[1], fill = false, lineTension = 0
};
decimal[] arrHospMonthTotal = new decimal[listOfMonths.Length];
for (int j = 0; j < listOfMonths.Length; j++)
{
string monthandYear = listOfMonths[j];
var filteredData = objData.Where(w => w.MONTHTEXT == monthandYear.Split(' ')[0] && w.Year == Convert.ToInt32(monthandYear.Split(' ')[1]));
arrHospMonthTotal[j] = (filteredData != null && filteredData.Count() > 0) ? Convert.ToInt32(filteredData.Sum(S => Convert.ToDecimal(S.HOSPRATE))) : 0;
}
objHospMonthTotal.data = arrHospMonthTotal;
objdatasetList.Add(objHospMonthTotal);
return(objdatasetList.ToArray());
}
}
public TorrentDataHandler(ref dataset d, ref datasetTableAdapters.TorrentsTableTableAdapter musikadapter)
{
data = d;
WorkerReportsProgress = true;
WorkerSupportsCancellation = true;
musicadapter = musikadapter;
d.Tables.CollectionChanging += new CollectionChangeEventHandler(Tables_CollectionChanging);
d.Tables.CollectionChanged += new CollectionChangeEventHandler(Tables_CollectionChanged);
trackeradapter.Fill(data.TrackersTable);
}
public async Task <JsonResult> GetTodaysPower(string deviceId)
{
DateTime start = DateTime.UtcNow.AddHours(-12);
int year = start.Year;
int month = start.Month;
// Average all results over the minute
List <DateTime> timeFrame = new List <DateTime>();
var query = (from i in db.DataLogs
where i.timeStamp >= start && i.deviceId == deviceId
group i by new { i.timeStamp.Day, i.timeStamp.Hour, i.timeStamp.Minute } into grp
select new
{
day = grp.Key.Day,
hour = grp.Key.Hour,
minute = grp.Key.Minute,
compressorCurrent = grp.Average(x => x.compressorCurrent),
fanCurrent = grp.Average(x => x.fanCurrent)
}).ToListAsync();
for (int i = 1; i <= 720; i++)
{
timeFrame.Add(start.AddMinutes(i));
}
var results = await query;
var data = (from t in timeFrame
join i in results on new { x = t.Day, y = t.Hour, z = t.Minute } equals new { x = i.day, y = i.hour, z = i.minute } into joined
from j in joined.DefaultIfEmpty()
select new
{
timeStamp = new DateTime(year, month, t.Day, t.Hour, t.Minute, 0),
compressorCurrent = j == null? 0 : j.compressorCurrent,
fanCurrent = j == null? 0 : j.fanCurrent
}).ToList();
chartData powerChart = new chartData();
dataset set = new dataset();
powerChart.labels = data.Select(x => x.timeStamp.ToString()).ToList();
set = new dataset()
{
label = "Compressor Current", data = data.Select(x => x.compressorCurrent).ToList()
};
powerChart.datasets.Add(set);
set = new dataset()
{
label = "Fan current", data = data.Select(x => x.fanCurrent).ToList()
};
powerChart.datasets.Add(set);
return(Json(powerChart));
}
protected void btnSearch_Click(object sender, EventArgs e)
{
perlabEntities db = new perlabEntities();
dataset obj = new dataset();
var key = (txtSearch.Text);
var result = from da in db.datasets
where da.description.Contains(key)
select new { da.filename, da.description };
GridView1.DataSource = result.ToList();
GridView1.DataBind();
}
public dataset ToDataAngineModel()
{
dataset d = new dataset();
d.datasetname = this.DatasetName;
d.id = this.ID;
d.ip = this.IP;
d.password = this.Password;
d.port = this.Port;
d.type = this.Type;
d.user = this.User;
return(d);
}
private void frmreportes_Load(object sender, EventArgs e)
{
this.reportViewer1.LocalReport.DataSources.Clear();
dataset ds = new dataset();
DataTable dt = new DataTable();
dt = Utils.Utilities.fileToDataTable(Utils.Utilities.provincia_dir);
ReportDataSource rds = new ReportDataSource(this.report_dataset_name, this.dt);
//MessageBox.Show(dt.Rows.Count.ToString());
//this.reportViewer1.LocalReport.ReportEmbeddedResource = "FileSystem.reporte_provincia.rdlc";
this.reportViewer1.LocalReport.ReportEmbeddedResource = this.nombre_reporte;
this.reportViewer1.LocalReport.DataSources.Add(rds);
this.reportViewer1.RefreshReport();
}
protected void btnDelete_Click(object sender, EventArgs e)
{
perlabEntities db = new perlabEntities();
dataset obj = new dataset();
obj.data_id = int.Parse((txtDataId.Text));
var result = (from p in db.datasets
where p.data_id == obj.data_id
select p).Single();
db.datasets.Remove(result);
db.SaveChanges();
BindGridView();
}
public static Dataset CreateInstanceFromDataAngineModel(dataset dt)
{
if (null == dt)
{
return(null);
}
Dataset d = new Dataset();
d.DatasetName = dt.datasetname;
d.ID = dt.id;
d.IP = dt.ip;
d.Password = dt.password;
d.Port = dt.port;
d.Type = dt.type;
d.User = dt.user;
return(d);
}
private void button6_Click_1(object sender, EventArgs e)
{
_report a = new _report();
reportQuery st = new reportQuery();
string iQry = string.Format(st.graphAll, this.dateTimePicker1.Text);
dataset ds = new dataset();
using (MySqlConnection conn = new MySqlConnection(DBConn.connstring))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(iQry, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, ds.Tables["graph"].TableName);
_cr_GraphStat rep = new _cr_GraphStat();
rep.SetDataSource(ds);
a.crystalReportViewer1.ReportSource = rep;
a.ShowDialog();
}
}
private void collReporty()
{
_report a = new _report();
string iQry = @"SELECT
date `DATE`,
code `CODE`,
concat(surname, ', ', firstname, ' ', middlename) `NAME`,
sex `GENDER`,
dob `BOD`,
mother `MOTHERNAME`,
father `FATHERNAME`,
brgy `ADDRESS`,
cp_no `CONTACT`,
school `SCHOOL`,
yearlevel `YEAR`,
ave `AVE`,
status `STATUS`
FROM schoolar_coll
where date between '{0}' and '{1}'
and brgy like '%%{2}%%'
and school like '%%{3}%%'
and status like '%%{4}%%'
group by code";
string qry = string.Format(iQry, dateTimePicker1.Text, dateTimePicker2.Text, comboBox5.Text, textBox6.Text, comboBox1.Text);
string datasetTable = "colReport";
dataset ds = new dataset();
using (MySqlConnection conn = new MySqlConnection(DBConn.connstring))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(qry, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, ds.Tables[datasetTable].TableName);
_cr_colReport2 rep = new _cr_colReport2();
rep.SetDataSource(ds);
a.crystalReportViewer1.ReportSource = rep;
a.ShowDialog();
}
}
public void RunRoulettStats(int trials, double startingMoney, int maxRounds, double initialBet)
{
dataset max = new dataset(0, 0, 0, "", 0);
LinkedList <dataset> colection = new LinkedList <dataset>();
string filePath = System.AppDomain.CurrentDomain.BaseDirectory + "out.csv";
TextWriter tsw = new StreamWriter(filePath);
foreach (string bettype in p.roulett.BetTypes)
{
for (int r = 1; r < maxRounds; r++)
{
tsw.WriteLine("bettype," + bettype + ", rounds" + r + ", trials," + trials + ",starting money," + startingMoney);
double total = 0;
for (int i = 0; i < trials; i++)
{
p.Money = startingMoney;
double winnings = p.PlayRoulettInput(r, bettype, initialBet);
total += winnings;
tsw.Write(winnings + ",");
}
dataset cur = new dataset(r, initialBet, (total / trials) - startingMoney, bettype, startingMoney);
tsw.Write("\n avgmoney," + (total / trials));
if (cur.netgain > max.netgain)
{
max = cur;
}
colection.AddLast(cur);
}
}
tsw.Write("--------Final Results From " + trials + " Trials---------");
tsw.Write("With starting of $" + startingMoney + " and initial bet of " + max.initialbet);
tsw.Write("Optimal Rounds: " + max.rounds);
// Console.WriteLine("Optimal initial Bet Amount: " + max.initialbet);
tsw.Write("Optimal Bet On:" + max.beton);
tsw.Write("Nate money Made" + max.netgain);
}
private void button6_Click(object sender, EventArgs e)
{
_report a = new _report();
string iQry = @"SELECT
(select min(event_date) from sra where event_date between '{0}' and '{1}') `min`,
(select max(event_date) from sra where event_date between '{0}' and '{1}') `max`,
agency,
sra_no,
host,
veneu,
address_branch,
rep_contact,
concat(surname, ', ', firstname, ' ', middlename) `name`,
address,
age,
gender,
`position`,
jobsite,
remarks
FROM sra
where event_date between '{0}' and '{1}'
and agency like '%%{2}%%'
and sra_no like '%%{3}%%'
group by agency";
string qry = string.Format(iQry, dateTimePicker1.Text, dateTimePicker2.Text, textBox1.Text, textBox2.Text);
dataset ds = new dataset();
using (MySqlConnection conn = new MySqlConnection(DBConn.connstring))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(qry, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, ds.Tables["sraReport"].TableName);
_cr_sraReport rep = new _cr_sraReport();
rep.SetDataSource(ds);
a.crystalReportViewer1.ReportSource = rep;
a.ShowDialog();
}
}
private void button6_Click(object sender, EventArgs e)
{
_report a = new _report();
string iQry = @"SELECT
event_date,
event,
host,
veneu,
concat(surname, ', ', firstname, ' ', middlename) `name`,
gender,
purok,
address,
dob,
contact,
work_type
FROM child_labor
where event_date between '{0}' and '{1}'
and event like '%%{2}%%'
and host like '%%{3}%%'
and veneu like '%%{4}%%'
and address like '%%{6}%%'
and concat(surname, firstname, middlename) like '%%{5}%%'";
string qry = string.Format(iQry, dateTimePicker1.Text, dateTimePicker2.Text, textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text, comboBox5.Text);
string datasetTable = "childLaborReport";
dataset ds = new dataset();
using (MySqlConnection conn = new MySqlConnection(DBConn.connstring))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(qry, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, ds.Tables[datasetTable].TableName);
_cr_childLaborReport rep = new _cr_childLaborReport();
rep.SetDataSource(ds);
a.crystalReportViewer1.ReportSource = rep;
a.ShowDialog();
}
}
private void nsrpReport()
{
_report a = new _report();
string iQry = @"SELECT
date `DATE`,
concat(surname, ', ', firstname, ' ', middlename) `NAME`,
dob `BIRTHDAY`,
age `AGE`,
IF(sex = 'MALE','M','F') `GENDER`,
civil_status `CIVIL STATUS`,
religion `RELIGION`,
'BIRTHPLACE' `BIRTHPLACE`,
concat(brgy, ', ' , municipality, ', ', province) `ADDRESS`,
email `EMAIL`,
cp_no `CONTACT`,
`4ps` `4Ps`,
emp_status `EMP. STATUS`,
job_pre `JOB PREF.`,
educ_level `EDUC. LEVEL`,
skills `SKILLS`,
`from` `FROM`
FROM ofw2
where date between '{0}' and '{1}'
order by date";
dataset ds = new dataset();
string qry = string.Format(iQry, dateTimePicker1.Text, dateTimePicker2.Text);
using (MySqlConnection conn = new MySqlConnection(DBConn.connstring))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(qry, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, ds.Tables["nsrpReport"].TableName);
_cr_nsrp rep = new _cr_nsrp();
rep.SetDataSource(ds);
a.crystalReportViewer1.ReportSource = rep;
a.ShowDialog();
}
}
private void ofwReport()
{
_report a = new _report();
string iQry = @"select
date,
code,
concat(surname, ', ', firstname, ' ', middlename) name,
brgy `address`,
sex `gender`,
country,
passport,
type,
cp_no `contact_no`,
status,
remarks
from ofw2
where date between '{0}' and '{1}'
and brgy like '%%{2}%%'
and status like '%%{3}%%'
";
string qry = string.Format(iQry, dateTimePicker1.Text, dateTimePicker2.Text, comboBox5.Text, comboBox2.Text);
dataset ds = new dataset();
using (MySqlConnection conn = new MySqlConnection(DBConn.connstring))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(qry, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, ds.Tables["ofwReport"].TableName);
_cr_ofw rep = new _cr_ofw();
rep.SetDataSource(ds);
a.crystalReportViewer1.ReportSource = rep;
a.ShowDialog();
}
}
private void jfReport()
{
_report a = new _report();
string iQry = @"SELECT
(select min(event_date) from jobfair2 where event_date between '{0}' and '{1}') `min`,
(select max(event_date) from jobfair2 where event_date between '{0}' and '{1}') `max`,
event_date,
host,
concat(surname, ', ', firstname, ' ', middlename) `name`,
brgy `address`,
sex `gender`,
cp_no `tel_no`,
position `job_position`,
hiring_company,
jobsite `location`,
`status`,
remarks
FROM jobfair2
where event_date between '{0}' and '{1}'
and host like '%%{2}%%'";
string qry = string.Format(iQry, dateTimePicker1.Text, dateTimePicker2.Text, textBox1.Text);
dataset ds = new dataset();
using (MySqlConnection conn = new MySqlConnection(DBConn.connstring))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(qry, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, ds.Tables["jobFair"].TableName);
_cr_jobFair rep = new _cr_jobFair();
rep.SetDataSource(ds);
a.crystalReportViewer1.ReportSource = rep;
a.ShowDialog();
}
}
protected void ddlexcellst_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
if (ddlexcellst.SelectedIndex == 0)
{
Label2.Visible = true;
}
else
{
Label2.Visible = false;
OAuth2Parameters parameters_lst = oauthcredentials();
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "Fusion-SpreadSheet", parameters_lst);
SpreadsheetsService service = new SpreadsheetsService("Fusion-SpreadSheet");
service.RequestFactory = requestFactory;
service.RequestFactory = requestFactory;
// TODO: Authorize the service object for a specific user (see other sections)
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
SpreadsheetQuery query = new SpreadsheetQuery();
// Make a request to the API and get all spreadsheets.
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count == 0)
{
// TODO: There were no spreadsheets, act accordingly.
}
// TODO: Choose a spreadsheet more intelligently based on your
// app's needs.
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[Convert.ToInt32(ddlexcellst.SelectedIndex) - 1];
//Response.Write(spreadsheet.Title.Text + "\n");
// Get the first worksheet of the first spreadsheet.
// TODO: Choose a worksheet more intelligently based on your
// app's needs.
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
listQuery.StartIndex = 0;
ListFeed listFeed = service.Query(listQuery);
// Iterate through each row, printing its cell values.
List <category> chart_categories = new List <category>();
ChartModels result = new ChartModels();
categories final_categories = new categories();
List <categories> list_categories = new List <categories>();
List <dataset> collect_dataset = new List <dataset>();
foreach (ListEntry row in listFeed.Entries)
{
// Print the first column's cell value
TableRow tr = new TableRow();
// Response.Write(row.Title.Text + "\n");
// Iterate over the remaining columns, and print each cell value
dataset final_dataset = new dataset();
List <data> collect_data = new List <data>();
foreach (ListEntry.Custom element in row.Elements)
{
if (row.Title.Text == "Row: 2")
{
category chart_category = new category();
chart_category.label = element.Value.ToString();
chart_categories.Add(chart_category);
}
else
{
data data_value = new data();
final_dataset.seriesname = row.Title.Text;
int n;
bool isNumeric = int.TryParse(element.Value, out n);
if (isNumeric)
{
data_value.value = element.Value.ToString();
collect_data.Add(data_value);
}
}
// Response.Write(element.Value + "\n");
TableCell tc = new TableCell();
if (row.Title.Text == "Row: 2")
{
tc.Text = "";
}
else
{
tc.Text = element.Value;
}
tr.Cells.Add(tc);
}
Table1.Rows.Add(tr);
if (collect_data.Count != 0)
{
final_dataset.data = collect_data;
collect_dataset.Add(final_dataset);
}
}
final_categories.category = chart_categories;
result.dataset = collect_dataset;
list_categories.Add(final_categories);
result.categories = list_categories;
JavaScriptSerializer js = new JavaScriptSerializer();
string res = js.Serialize(result);
string chartjson = JsonConvert.SerializeObject(result.categories);
StringBuilder strJson = new StringBuilder();
strJson.Append("{" +
"'chart': {" +
"'caption': 'Quarterly revenue'," +
"'subCaption':'Last year'," +
"'xAxisName':'Quarter (Click to drill down)'," +
"'subcaptionFontColor':'#0075c2'," +
"'numberPrefix': '$'," +
"'formatNumberScale': '1'," +
"'placeValuesInside': '1'," +
"'decimals': '0'" +
"},");
strJson.Append("'categories':");
strJson.Append(chartjson);
strJson.Append(",");
strJson.Append("'dataset':");
string chartdatajson = JsonConvert.SerializeObject(result.dataset);
strJson.Append(chartdatajson);
strJson.Append("}");
// Initialize the chart.
Chart sales = new Chart("mscolumn3d", "myChart", "600", "350", "json", strJson.ToString());
// Render the chart.
Label1.Text = sales.Render();
}
}
catch (Exception)
{
Response.Redirect("Default.aspx");
}
}
public static global::System.Xml.Schema.XmlSchemaComplexType GetTypedTableSchema(global::System.Xml.Schema.XmlSchemaSet xs) {
global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType();
global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence();
dataset ds = new dataset();
global::System.Xml.Schema.XmlSchemaAny any1 = new global::System.Xml.Schema.XmlSchemaAny();
any1.Namespace = "http://www.w3.org/2001/XMLSchema";
any1.MinOccurs = new decimal(0);
any1.MaxOccurs = decimal.MaxValue;
any1.ProcessContents = global::System.Xml.Schema.XmlSchemaContentProcessing.Lax;
sequence.Items.Add(any1);
global::System.Xml.Schema.XmlSchemaAny any2 = new global::System.Xml.Schema.XmlSchemaAny();
any2.Namespace = "urn:schemas-microsoft-com:xml-diffgram-v1";
any2.MinOccurs = new decimal(1);
any2.ProcessContents = global::System.Xml.Schema.XmlSchemaContentProcessing.Lax;
sequence.Items.Add(any2);
global::System.Xml.Schema.XmlSchemaAttribute attribute1 = new global::System.Xml.Schema.XmlSchemaAttribute();
attribute1.Name = "namespace";
attribute1.FixedValue = ds.Namespace;
type.Attributes.Add(attribute1);
global::System.Xml.Schema.XmlSchemaAttribute attribute2 = new global::System.Xml.Schema.XmlSchemaAttribute();
attribute2.Name = "tableTypeName";
attribute2.FixedValue = "r2indexDataTable";
type.Attributes.Add(attribute2);
type.Particle = sequence;
global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable();
if (xs.Contains(dsSchema.TargetNamespace)) {
global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream();
global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream();
try {
global::System.Xml.Schema.XmlSchema schema = null;
dsSchema.Write(s1);
for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext(); ) {
schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current));
s2.SetLength(0);
schema.Write(s2);
if ((s1.Length == s2.Length)) {
s1.Position = 0;
s2.Position = 0;
for (; ((s1.Position != s1.Length)
&& (s1.ReadByte() == s2.ReadByte())); ) {
;
}
if ((s1.Position == s1.Length)) {
return type;
}
}
}
}
finally {
if ((s1 != null)) {
s1.Close();
}
if ((s2 != null)) {
s2.Close();
}
}
}
xs.Add(dsSchema);
return type;
}
public static global::System.Xml.Schema.XmlSchemaComplexType GetTypedDataSetSchema(global::System.Xml.Schema.XmlSchemaSet xs) {
dataset ds = new dataset();
global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType();
global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence();
global::System.Xml.Schema.XmlSchemaAny any = new global::System.Xml.Schema.XmlSchemaAny();
any.Namespace = ds.Namespace;
sequence.Items.Add(any);
type.Particle = sequence;
global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable();
if (xs.Contains(dsSchema.TargetNamespace)) {
global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream();
global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream();
try {
global::System.Xml.Schema.XmlSchema schema = null;
dsSchema.Write(s1);
for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext(); ) {
schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current));
s2.SetLength(0);
schema.Write(s2);
if ((s1.Length == s2.Length)) {
s1.Position = 0;
s2.Position = 0;
for (; ((s1.Position != s1.Length)
&& (s1.ReadByte() == s2.ReadByte())); ) {
;
}
if ((s1.Position == s1.Length)) {
return type;
}
}
}
}
finally {
if ((s1 != null)) {
s1.Close();
}
if ((s2 != null)) {
s2.Close();
}
}
}
xs.Add(dsSchema);
return type;
}
protected void ddlexcellst_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
if (ddlexcellst.SelectedIndex == 0) {
Label2.Visible = true;
}
else
{
Label2.Visible = false;
OAuth2Parameters parameters_lst = oauthcredentials();
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "Fusion-SpreadSheet", parameters_lst);
SpreadsheetsService service = new SpreadsheetsService("Fusion-SpreadSheet");
service.RequestFactory = requestFactory;
service.RequestFactory = requestFactory;
// TODO: Authorize the service object for a specific user (see other sections)
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
SpreadsheetQuery query = new SpreadsheetQuery();
// Make a request to the API and get all spreadsheets.
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count == 0)
{
// TODO: There were no spreadsheets, act accordingly.
}
// TODO: Choose a spreadsheet more intelligently based on your
// app's needs.
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[Convert.ToInt32(ddlexcellst.SelectedIndex) - 1];
//Response.Write(spreadsheet.Title.Text + "\n");
// Get the first worksheet of the first spreadsheet.
// TODO: Choose a worksheet more intelligently based on your
// app's needs.
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
listQuery.StartIndex = 0;
ListFeed listFeed = service.Query(listQuery);
// Iterate through each row, printing its cell values.
List<category> chart_categories = new List<category>();
ChartModels result = new ChartModels();
categories final_categories = new categories();
List<categories> list_categories = new List<categories>();
List<dataset> collect_dataset = new List<dataset>();
foreach (ListEntry row in listFeed.Entries)
{
// Print the first column's cell value
TableRow tr = new TableRow();
// Response.Write(row.Title.Text + "\n");
// Iterate over the remaining columns, and print each cell value
dataset final_dataset = new dataset();
List<data> collect_data = new List<data>();
foreach (ListEntry.Custom element in row.Elements)
{
if (row.Title.Text == "Row: 2")
{
category chart_category = new category();
chart_category.label = element.Value.ToString();
chart_categories.Add(chart_category);
}
else
{
data data_value = new data();
final_dataset.seriesname = row.Title.Text;
int n;
bool isNumeric = int.TryParse(element.Value, out n);
if (isNumeric)
{
data_value.value = element.Value.ToString();
collect_data.Add(data_value);
}
}
// Response.Write(element.Value + "\n");
TableCell tc = new TableCell();
if (row.Title.Text == "Row: 2")
{
tc.Text = "";
}
else
tc.Text = element.Value;
tr.Cells.Add(tc);
}
Table1.Rows.Add(tr);
if (collect_data.Count != 0)
{
final_dataset.data = collect_data;
collect_dataset.Add(final_dataset);
}
}
final_categories.category = chart_categories;
result.dataset = collect_dataset;
list_categories.Add(final_categories);
result.categories = list_categories;
JavaScriptSerializer js = new JavaScriptSerializer();
string res = js.Serialize(result);
string chartjson = JsonConvert.SerializeObject(result.categories);
StringBuilder strJson = new StringBuilder();
strJson.Append("{" +
"'chart': {" +
"'caption': 'Quarterly revenue'," +
"'subCaption':'Last year'," +
"'xAxisName':'Quarter (Click to drill down)'," +
"'subcaptionFontColor':'#0075c2'," +
"'numberPrefix': '$'," +
"'formatNumberScale': '1'," +
"'placeValuesInside': '1'," +
"'decimals': '0'" +
"},");
strJson.Append("'categories':");
strJson.Append(chartjson);
strJson.Append(",");
strJson.Append("'dataset':");
string chartdatajson = JsonConvert.SerializeObject(result.dataset);
strJson.Append(chartdatajson);
strJson.Append("}");
// Initialize the chart.
Chart sales = new Chart("mscolumn3d", "myChart", "600", "350", "json", strJson.ToString());
// Render the chart.
Label1.Text = sales.Render();
}
}
catch (Exception)
{
Response.Redirect("Default.aspx");
}
}
public ActionResult GetProjection(string[] checkedRes)
{
using (var q = new FinancialQueries())
{
var user = User.Identity.Name;
var funds = q.GetFunds(user);
var reserves = q.GetReserves(user).Where(r => checkedRes.Any(c => c == r.ID.ToString())).OrderByDescending(r => r.DateToWithdraw);
var today = DateTime.Today;
var labels = new List <string>();
DateTime end = reserves.Max(r => r.DateToWithdraw);
DateTime currDate1 = today;
int incr = (int)Math.Ceiling(end.Subtract(today).TotalDays / 1095);
while (currDate1.Year < end.Year || (currDate1.Year == end.Year && currDate1.Month <= end.Month))
{
labels.Add(GetMonth(currDate1.Month) + "-" + GetYear(currDate1.Year));
currDate1 = currDate1.AddMonths(incr);
}
var datasets = new List <dataset>();
int x = 0;
decimal totalMonthlyDep = 0;
foreach (var res in reserves)
{
x++;
int monthsToAchieve = 0;
var currDate = today;
while (currDate <= res.DateToWithdraw)
{
monthsToAchieve++;
currDate = currDate.AddMonths(1);
}
// escolhendo fundo
decimal?percIncome = null;
if (monthsToAchieve < 24)
{
percIncome = 1.0015m;
}
else if (monthsToAchieve < 84)
{
percIncome = 1.002875m;
}
else
{
percIncome = 1.0063125m;
}
var resBal = q.GetBalance(today, user, null, res.ID);
decimal monthlyDep = Math.Max(0, res.FinalExpectedValue - resBal);
decimal div = 0;
for (int i = 0; i < monthsToAchieve; i++)
{
div += (decimal)Math.Pow((double)percIncome, i);
}
monthlyDep /= Math.Max(1, div);
var data = new List <decimal>();
decimal currBal = q.GetBalance(today, user, reserveId: res.ID);
int currIt = 0;
for (int i = 0; i < monthsToAchieve; i++)
{
currIt++;
if (currIt == 1)
{
data.Add(currBal);
}
if (currIt == incr)
{
currIt = 0;
}
currBal *= percIncome.Value;
currBal += monthlyDep;
}
currIt++;
while (currIt != 1)
{
if (currIt == incr)
{
currIt = 0;
}
currBal *= percIncome.Value;
currBal += monthlyDep;
currIt++;
}
data.Add(currBal);
totalMonthlyDep += monthlyDep;
var dataset = new dataset()
{
reserveId = res.ID,
monthlyDep = monthlyDep.ToString("0.00"),
data = data,
fillColor = "rgba(151,187,205,0)",
strokeColor = GetColor(x),
pointColor = "rgba(151,187,205,1)",
pointStrokeColor = "#fff"
};
datasets.Add(dataset);
}
var simularRes = new simulatorResult()
{
totalMonthlyDep = totalMonthlyDep.ToString("0.00"),
labels = labels,
datasets = datasets
};
return(Json(simularRes));
}
}
private void button5_Click_1(object sender, EventArgs e)
{
_report a = new _report();
string iQry = @"select * from
(
SELECT
date `DATE`,
concat(surname, ', ', firstname, ' ', middlename) `NAME`,
dob `BIRTHDAY`,
age `AGE`,
IF(sex = 'MALE','M','F') `GENDER`,
civil_status `CIVIL STATUS`,
religion `RELIGION`,
birthplace `BIRTHPLACE`,
concat(brgy, ', ' , municipality, ', ', province) `ADDRESS`,
email `EMAIL`,
cp_no `CONTACT`,
`4ps` `4Ps`,
emp_status `EMP. STATUS`,
job_pre `JOB PREF.`,
educ_level `EDUC. LEVEL`,
skills `SKILLS`,
`from` `FROM`
FROM schoolar_coll
union all
SELECT
event_date `DATE`,
concat(surname, ', ', firstname, ' ', middlename) `NAME`,
dob `BIRTHDAY`,
age `AGE`,
IF(sex = 'MALE','M','F') `GENDER`,
civil_status `CIVIL STATUS`,
religion `RELIGION`,
'BIRTHPLACE' `BIRTHPLACE`,
concat(brgy, ', ' , municipality, ', ', province) `ADDRESS`,
email `EMAIL`,
cp_no `CONTACT`,
`4ps` `4Ps`,
emp_status `EMP. STATUS`,
job_pre `JOB PREF.`,
educ_level `EDUC. LEVEL`,
skills `SKILLS`,
`from` `FROM`
FROM sra2
union all
SELECT
event_date `DATE`,
concat(surname, ', ', firstname, ' ', middlename) `NAME`,
dob `BIRTHDAY`,
age `AGE`,
IF(sex = 'MALE','M','F') `GENDER`,
civil_status `CIVIL STATUS`,
religion `RELIGION`,
'BIRTHPLACE' `BIRTHPLACE`,
concat(brgy, ', ' , municipality, ', ', province) `ADDRESS`,
email `EMAIL`,
cp_no `CONTACT`,
`4ps` `4Ps`,
emp_status `EMP. STATUS`,
job_pre `JOB PREF.`,
educ_level `EDUC. LEVEL`,
skills `SKILLS`,
`from` `FROM`
FROM jobfair2
union all
SELECT
date `DATE`,
concat(surname, ', ', firstname, ' ', middlename) `NAME`,
dob `BIRTHDAY`,
age `AGE`,
IF(sex = 'MALE','M','F') `GENDER`,
civil_status `CIVIL STATUS`,
religion `RELIGION`,
'BIRTHPLACE' `BIRTHPLACE`,
concat(brgy, ', ' , municipality, ', ', province) `ADDRESS`,
email `EMAIL`,
cp_no `CONTACT`,
`4ps` `4Ps`,
emp_status `EMP. STATUS`,
job_pre `JOB PREF.`,
educ_level `EDUC. LEVEL`,
skills `SKILLS`,
`from` `FROM`
FROM kasambahay2
union all
SELECT
date `DATE`,
concat(surname, ', ', firstname, ' ', middlename) `NAME`,
dob `BIRTHDAY`,
age `AGE`,
IF(sex = 'MALE','M','F') `GENDER`,
civil_status `CIVIL STATUS`,
religion `RELIGION`,
'BIRTHPLACE' `BIRTHPLACE`,
concat(brgy, ', ' , municipality, ', ', province) `ADDRESS`,
email `EMAIL`,
cp_no `CONTACT`,
`4ps` `4Ps`,
emp_status `EMP. STATUS`,
job_pre `JOB PREF.`,
educ_level `EDUC. LEVEL`,
skills `SKILLS`,
`from` `FROM`
FROM ofw2
union all
SELECT
date `DATE`,
concat(surname, ', ', firstname, ' ', middlename) `NAME`,
dob `BIRTHDAY`,
age `AGE`,
IF(sex = 'MALE','M','F') `GENDER`,
civil_status `CIVIL STATUS`,
religion `RELIGION`,
'BIRTHPLACE' `BIRTHPLACE`,
concat(brgy, ', ' , municipality, ', ', province) `ADDRESS`,
email `EMAIL`,
cp_no `CONTACT`,
`4ps` `4Ps`,
emp_status `EMP. STATUS`,
job_pre `JOB PREF.`,
educ_level `EDUC. LEVEL`,
skills `SKILLS`,
`from` `FROM`
FROM contact2
) fin
order by date";
dataset ds = new dataset();
using (MySqlConnection conn = new MySqlConnection(DBConn.connstring))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(iQry, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds, ds.Tables["nsrpReport"].TableName);
_cr_nsrp rep = new _cr_nsrp();
rep.SetDataSource(ds);
a.crystalReportViewer1.ReportSource = rep;
a.ShowDialog();
}
}
