public Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.MonthItem> ReadFromDayTable(string connectionString)
{
DERMSCommon.SCADACommon.MonthItem itemMonth = null;
Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.MonthItem> monthItems = new Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.MonthItem>();
Tuple <long, DateTime> key = null;
Tuple <long, DateTime> keyM = null;
Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.DayItem> dayItemsData = new Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.DayItem>();
using (System.Data.SqlClient.SqlConnection _con = new System.Data.SqlClient.SqlConnection(connectionString))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT Timestamp, Gid, E, PMax, PMin, PAvg FROM dbo.Day", _con))
{
_con.Open();
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
{
// Check is the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
DERMSCommon.SCADACommon.DayItem c = new DERMSCommon.SCADACommon.DayItem();
// To avoid unexpected bugs access columns by name.
try
{
c.E = reader.GetDouble(reader.GetOrdinal("E"));
c.PAvg = reader.GetDouble(reader.GetOrdinal("PAvg"));
c.PMax = reader.GetDouble(reader.GetOrdinal("PMax"));
c.PMin = reader.GetDouble(reader.GetOrdinal("PMin"));
c.Timestamp = reader.GetDateTime(reader.GetOrdinal("Timestamp"));
c.Gid = reader.GetInt64(reader.GetOrdinal("Gid"));
//c.P = reader.GetDouble(reader.GetOrdinal("P"));
key = new Tuple <long, DateTime>(c.Gid, c.Timestamp);
dayItemsData.Add(key, c);
}
catch (Exception e)
{ }
}
}
}
_con.Close();
}
}
foreach (var d in dayItemsData)
{
//itemDay = new DERMSCommon.SCADACommon.DayItem(d.Key.Item1, d.Key.Item2.Date.AddHours(d.Key.Item2.Hour), MinProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), MaxProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), AvgProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), 0, 0);
itemMonth = new DERMSCommon.SCADACommon.MonthItem(d.Key.Item1, d.Key.Item2.Date, MinProductionPerDay(d.Key.Item2.DayOfYear, d.Key.Item2.Month, dayItemsData, d.Key.Item1), MaxProductionPerDay(d.Key.Item2.DayOfYear, d.Key.Item2.Month, dayItemsData, d.Key.Item1), AvgProductionPerDay(d.Key.Item2.DayOfYear, d.Key.Item2.Month, dayItemsData, d.Key.Item1), 0, d.Value.P);
keyM = new Tuple <long, DateTime>(itemMonth.Gid, itemMonth.Timestamp);
if (!monthItems.ContainsKey(keyM))
{
monthItems.Add(keyM, itemMonth);
}
}
return(monthItems);
}
public static double ValidateDataReader_F(SqlDataReader reader,string colname)
{
if(reader.GetValue(reader.GetOrdinal(colname))!=DBNull.Value)
return reader.GetDouble(reader.GetOrdinal(colname));
else
return System.Double.MinValue;
}
public string[] getDBEletric()
{
//SELECT TOP 1 [PV_VOLTAGE] FROM [SunCoreDB].[dbo].[PERF_ARRAY] 单元
//SELECT TOP 1 [CAPACITY_CUMULATIVE_EM] FROM [SunCoreDB].[dbo].[PERF_STATION] 阵列
//
String[] eletric = new String[3];
comm.Connection = conn;
comm.Connection.Open();
comm.CommandText = "SELECT TOP 1 [PV_VOLTAGE] FROM [SunCoreDB].[dbo].[PERF_ARRAY]";
dr = comm.ExecuteReader();
dr.Read();
eletric[0] = dr.GetString(0);
dr.Close();
comm.CommandText = "SELECT TOP 1 [CAPACITY_CUMULATIVE_EM] FROM [SunCoreDB].[dbo].[PERF_STATION]";
dr = comm.ExecuteReader();
dr.Read();
eletric[1] = dr.GetDouble(0).ToString();
dr.Close();
eletric[2] = "0";
comm.Connection.Close();
return eletric;
}
public static InstitutionBaseInfo fromSqlDataReader(SqlDataReader reader)
{
return new InstitutionBaseInfo
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Address = reader.GetString(2),
Lat = reader.GetDouble(3),
Lng = reader.GetDouble(4),
Email = reader.GetString(5),
Phone = reader.GetString(6),
Type = reader.GetInt32(7),
Site = reader.GetString(8),
WorkingHours = reader.GetString(9),
Icon = GetIconUrl(reader.GetInt32(7))
};
}
protected void moreInfoStudentLinkBtn_Click(object sender, CommandEventArgs e)
{
String connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
System.Data.SqlClient.SqlConnection sql = new System.Data.SqlClient.SqlConnection(connectionString);
int rowIndex = Convert.ToInt32(((sender as LinkButton).NamingContainer as GridViewRow).RowIndex);
GridViewRow row = GridView1.Rows[rowIndex];
int applicationID = Convert.ToInt32(e.CommandArgument);
sql.Open();
System.Data.SqlClient.SqlCommand moreJobInfo = new System.Data.SqlClient.SqlCommand();
moreJobInfo.Connection = sql;
moreJobInfo.CommandText = "SELECT Student.FirstName + ' ' + Student.LastName, Student.StudentGPA, Student.StudentGraduationTrack, JobListing.JobTitle, JobListing.JobDescription, JobListing.JobType, JobListing.Location, JobListing.Deadline, JobListing.NumOfApplicants, Organization.OrganizationName, Organization.OrganizationDescription FROM Organization INNER JOIN JobListing ON Organization.OrganizationEntityID = JobListing.OrganizationID INNER JOIN ApplicationRequest ON JobListing.JobListingID = ApplicationRequest.JobListingID INNER JOIN Student ON ApplicationRequest.StudentEntityID = Student.StudentEntityID where ApplicationRequest.ApplicationID = " + applicationID;
System.Data.SqlClient.SqlDataReader reader = moreJobInfo.ExecuteReader();
while (reader.Read())
{
//set labels to db values
lblStudentName.Text = reader.GetString(0);
lblSudentGPA.Text = "GPA: " + reader.GetDouble(1).ToString();
// On track if statement
String status = reader.GetString(2);
if (status == "Y")
{
lblStudentStatus.Text = "Student on Track";
}
else
{
lblStudentStatus.Text = "Student not on Track";
}
lblOrgName.Text = "Organization Name: " + reader.GetString(9);
lblOrgDesc.Text = "Organization Description: " + reader.GetString(10);
lblJobTitle.Text = "Job Title: " + reader.GetString(3);
lblJobDesc.Text = "Job Description: " + reader.GetString(4);
lblJobLocation.Text = "Location: " + reader.GetString(6);
lblJobDeadline.Text = "Deadline: " + reader.GetDateTime(7).ToString();
lblNumberOfApplicants.Text = "Number Of Applicants: " + reader.GetInt32(8).ToString();
}
sql.Close();
Session["selectedapplicationID"] = applicationID.ToString();
ClientScript.RegisterStartupScript(this.GetType(), "Pop", "openEditSModal();", true);
}
private string GetObjectValue(System.Data.SqlClient.SqlDataReader reader, int i)
{
string fieldType = reader.GetFieldType(i).ToString();
object FieldValue = null;;
if (fieldType.Contains("Int32"))
{
FieldValue = reader.GetInt32(i);
}
else if (fieldType.Contains("Int64"))
{
FieldValue = reader.GetInt64(i);
}
else if (fieldType.Contains("Decimal"))
{
FieldValue = reader.GetDecimal(i);
}
else if (fieldType.Contains("Double"))
{
FieldValue = reader.GetDouble(i);
}
else if (fieldType.Contains("DateTime"))
{
FieldValue = reader.GetDateTime(i);
}
else if (fieldType.Contains("String"))
{
FieldValue = reader.GetString(i);
}
else if (fieldType.Contains("Boolean"))
{
FieldValue = reader.GetBoolean(i);
}
else if (fieldType.Contains("Byte"))
{
FieldValue = reader.GetByte(i);
}
else if (fieldType.Contains("Guid"))
{
FieldValue = reader.GetGuid(i);
}
else
{
FieldValue = reader.GetString(i);
}
if (FieldValue != null)
{
return(FieldValue.ToString());
}
else
{
return("");
}
}
//protected void SearchButton_Click(object sender, EventArgs e)
//{
// String term = SearchBox.Text;
// StudentOpportunity.SelectParameters.Add("term", term);
// StudentOpportunity.SelectCommand = "SELECT ApplicationRequest.ApplicationID, Student.StudentImage, CONCAT(Student.FirstName, ' ', Student.LastName) AS FullName, Student.StudentGradeLevel, Student.StudentGPA, Student.HoursOfWorkPlaceExp, JobListing.JobTitle, JobListing.JobDescription, JobListing.JobType, Organization.OrganizationName FROM ApplicationRequest INNER JOIN JobListing ON ApplicationRequest.JobListingID = JobListing.JobListingID INNER JOIN Organization ON JobListing.OrganizationID = Organization.OrganizationEntityID INNER JOIN Student ON ApplicationRequest.StudentEntityID = Student.StudentEntityID WHERE(ApplicationRequest.ApprovedFlag = 'P') and((Student.FirstName like '%" + @term + "%' or Student.LastName like '%" + @term + "%') or (JobListing.JobTitle like '%" + @term + "%') or (Organization.OrganizationName like '%" + @term + "%') or (Student.StudentGradeLevel like '%" + @term + "%') or (Student.StudentGPA like '%" + @term + "%') or (Student.HoursOfWorkPlaceExp like '%" + @term + "%') or (JobListing.JobDescription like '%" + @term + "%') or (JobListing.JobType like '%" + @term + "%'))";
// StudentOpportunity.DataBind();
// GridView1.DataBind();
// StudentOpportunity.SelectParameters.Clear();
//}
protected void btnStudentView_Click(object sender, CommandEventArgs e)
{
String connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
System.Data.SqlClient.SqlConnection sql = new System.Data.SqlClient.SqlConnection(connectionString);
int rowIndex = Convert.ToInt32(((sender as LinkButton).NamingContainer as GridViewRow).RowIndex);
int applicationID = Convert.ToInt32(e.CommandArgument);
Session["applicationID"] = applicationID.ToString();
//find student ID from logID
sql.Open();
System.Data.SqlClient.SqlCommand findStudentID = new System.Data.SqlClient.SqlCommand();
findStudentID.Connection = sql;
findStudentID.CommandText = "SELECT StudentEntityID FROM ApplicationRequest WHERE applicationID = " + Session["applicationID"];
System.Data.SqlClient.SqlDataReader IDreader = findStudentID.ExecuteReader();
//declare studentID session variable
Session["studentID"] = 0;
while (IDreader.Read())
{
Session["studentID"] = IDreader.GetInt32(0);
}
sql.Close();
//get student info for selected student
sql.Open();
System.Data.SqlClient.SqlCommand getStudentInfo = new System.Data.SqlClient.SqlCommand();
getStudentInfo.Connection = sql;
getStudentInfo.CommandText = "SELECT CONCAT(FirstName,' ',LastName), StudentGradeLevel, StudentGPA, StudentSATScore, HoursOfWorkPlaceExp, StudentEntityID, StudentImage FROM Student WHERE StudentEntityID = " + Session["studentID"];
System.Data.SqlClient.SqlDataReader studentReader = getStudentInfo.ExecuteReader();
while (studentReader.Read())
{
//fill labels in modal
lblStudentName.Text = studentReader.GetString(0);
lblGradeLevel.Text = "Grade Level: " + studentReader.GetString(1);
lblGPA.Text = "GPA: " + studentReader.GetDouble(2);
lblSATScore.Text = "SAT Score: " + studentReader.GetInt32(3);
lblHoursWorked.Text = "WBL Hours Earned: " + studentReader.GetInt32(4);
imgStudent.ImageUrl = studentReader.GetString(6);
}
ClientScript.RegisterStartupScript(this.GetType(), "Pop", "openviewStudentModal();", true);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false)
{
int id;
if (int.TryParse(Request.QueryString["id"], out id) == false)
{
lblMensagem.Text = "Id inválido !";
return;
}
//SqlConnection conn = new SqlConnection("Server=tcp:eot9dccau4.database.windows.net,1433;Database=testedb;User ID=alunos@eot9dccau4;Password=web2015$;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;");
conn.Open();
cmd = new SqlCommand("SELECT Nome, Endereco, Email, Nascimento, Peso FROM tbPessoa WHERE Id = @a", conn);
cmd.Parameters.AddWithValue("@a", id);
reader = cmd.ExecuteReader();
if (reader.Read() == true)
{
TxtNome.Text = reader.GetString(0);
TxtEndereco.Text = reader.GetString(1);
TxtEmail.Text = reader.GetString(2);
TxtNascimento.Text = reader.GetDateTime(3).ToString("dd/MM/yyyy");
TxtPeso.Text = reader.GetDouble(4).ToString("00.0");
}
else
{
lblMensagem.Text = "Id inválido seu burro, usuário anta, aprende a usar isso seu animal!";
}
reader.Close();
reader.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
internal object GetValue(SqlDataReader reader, UnitTags unitTypeTag, int i)
{
switch (unitTypeTag)
{
case UnitTags.AllorsString:
return reader.GetString(i);
case UnitTags.AllorsInteger:
return reader.GetInt32(i);
case UnitTags.AllorsFloat:
return reader.GetDouble(i);
case UnitTags.AllorsDecimal:
return reader.GetDecimal(i);
case UnitTags.AllorsBoolean:
return reader.GetBoolean(i);
case UnitTags.AllorsDateTime:
return reader.GetDateTime(i);
case UnitTags.AllorsUnique:
return reader.GetGuid(i);
case UnitTags.AllorsBinary:
return reader.GetValue(i);
default:
throw new ArgumentException("Unknown Unit ObjectType: " + unitTypeTag);
}
}
internal static void FillCompatibleITypedSettersFromReader(SmiEventSink_Default sink, ITypedSettersV3 setters, SmiMetaData[] metaData, SqlDataReader reader)
{
for (int i = 0; i < metaData.Length; i++)
{
if (reader.IsDBNull(i))
{
SetDBNull_Unchecked(sink, setters, i);
continue;
}
switch (metaData[i].SqlDbType)
{
case SqlDbType.BigInt:
{
SetInt64_Unchecked(sink, setters, i, reader.GetInt64(i));
continue;
}
case SqlDbType.Binary:
{
SetSqlBytes_LengthChecked(sink, setters, i, metaData[i], reader.GetSqlBytes(i), 0);
continue;
}
case SqlDbType.Bit:
{
SetBoolean_Unchecked(sink, setters, i, reader.GetBoolean(i));
continue;
}
case SqlDbType.Char:
{
SetSqlChars_LengthChecked(sink, setters, i, metaData[i], reader.GetSqlChars(i), 0);
continue;
}
case SqlDbType.DateTime:
{
SetDateTime_Checked(sink, setters, i, metaData[i], reader.GetDateTime(i));
continue;
}
case SqlDbType.Decimal:
{
SetSqlDecimal_Unchecked(sink, setters, i, reader.GetSqlDecimal(i));
continue;
}
case SqlDbType.Float:
{
SetDouble_Unchecked(sink, setters, i, reader.GetDouble(i));
continue;
}
case SqlDbType.Image:
{
SetSqlBytes_LengthChecked(sink, setters, i, metaData[i], reader.GetSqlBytes(i), 0);
continue;
}
case SqlDbType.Int:
{
SetInt32_Unchecked(sink, setters, i, reader.GetInt32(i));
continue;
}
case SqlDbType.Money:
{
SetSqlMoney_Unchecked(sink, setters, i, metaData[i], reader.GetSqlMoney(i));
continue;
}
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
{
SetSqlChars_LengthChecked(sink, setters, i, metaData[i], reader.GetSqlChars(i), 0);
continue;
}
case SqlDbType.Real:
{
SetSingle_Unchecked(sink, setters, i, reader.GetFloat(i));
continue;
}
case SqlDbType.UniqueIdentifier:
{
SetGuid_Unchecked(sink, setters, i, reader.GetGuid(i));
continue;
}
case SqlDbType.SmallDateTime:
{
SetDateTime_Checked(sink, setters, i, metaData[i], reader.GetDateTime(i));
continue;
}
case SqlDbType.SmallInt:
{
SetInt16_Unchecked(sink, setters, i, reader.GetInt16(i));
continue;
}
case SqlDbType.SmallMoney:
{
SetSqlMoney_Checked(sink, setters, i, metaData[i], reader.GetSqlMoney(i));
continue;
}
case SqlDbType.Text:
{
SetSqlChars_LengthChecked(sink, setters, i, metaData[i], reader.GetSqlChars(i), 0);
continue;
}
case SqlDbType.Timestamp:
{
SetSqlBytes_LengthChecked(sink, setters, i, metaData[i], reader.GetSqlBytes(i), 0);
continue;
}
case SqlDbType.TinyInt:
{
SetByte_Unchecked(sink, setters, i, reader.GetByte(i));
continue;
}
case SqlDbType.VarBinary:
{
SetSqlBytes_LengthChecked(sink, setters, i, metaData[i], reader.GetSqlBytes(i), 0);
continue;
}
case SqlDbType.VarChar:
{
SetSqlChars_LengthChecked(sink, setters, i, metaData[i], reader.GetSqlChars(i), 0);
continue;
}
case SqlDbType.Variant:
{
object sqlValue = reader.GetSqlValue(i);
ExtendedClrTypeCode typeCode = MetaDataUtilsSmi.DetermineExtendedTypeCode(sqlValue);
SetCompatibleValue(sink, setters, i, metaData[i], sqlValue, typeCode, 0);
continue;
}
case SqlDbType.Xml:
{
SetSqlXml_Unchecked(sink, setters, i, reader.GetSqlXml(i));
continue;
}
case SqlDbType.Udt:
{
SetSqlBytes_LengthChecked(sink, setters, i, metaData[i], reader.GetSqlBytes(i), 0);
continue;
}
}
throw ADP.NotSupported();
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Card Arrays
JobListing[] JobCardsArray = new JobListing[5];
Student[] StudentCardsArray = new Student[5];
JobListing[] StudentCardJobInfoArray = new JobListing[5];
((Label)Master.FindControl("lblMaster")).Text = "Landing Page";
EmptyPostinglbl.Visible = false;
EmptyStudentslbl.Visible = false;
String connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
System.Data.SqlClient.SqlConnection sql = new System.Data.SqlClient.SqlConnection(connectionString);
sql.Open();
System.Data.SqlClient.SqlCommand RecentJobs = new System.Data.SqlClient.SqlCommand();
RecentJobs.Connection = sql;
RecentJobs.CommandText = "SELECT TOP (5) JobListing.JobListingID, JobListing.JobTitle, Organization.Image, Organization.OrganizationName, JobListing.JobType, JobListing.JobDescription, JobListing.Location, JobListing.NumOfApplicants, JobListing.Deadline, Organization.OrganizationDescription, Organization.ExternalLink FROM SchoolApproval INNER JOIN OpportunityEntity ON SchoolApproval.OpportunityEntityID = OpportunityEntity.OpportunityEntityID INNER JOIN JobListing" +
" ON OpportunityEntity.OpportunityEntityID = JobListing.JobListingID INNER JOIN Organization ON JobListing.OrganizationID = Organization.OrganizationEntityID " +
"where SchoolApproval.ApprovedFlag = 'P' and SchoolApproval.SchoolEntityID = " + Session["schoolID"] + " ORDER BY JobListing.JobListingID DESC";
System.Data.SqlClient.SqlDataReader reader = RecentJobs.ExecuteReader();
// Attempting OOP Stuff
int x = 0;
while (reader.Read())
{
String JobTitle = reader.GetString(1);
String JobDescription = reader.GetString(5);
String JobType = reader.GetString(4);
String JobLocation = reader.GetString(6);
DateTime JobDeadline = reader.GetDateTime(8);
int numOfApplicants = reader.GetInt32(7);
String OrgName = reader.GetString(3);
String OrgDescription = reader.GetString(9);
String OrgImage = reader.GetString(2);
String OrgWebsite = reader.GetString(10);
JobListing tempObject = new JobListing(JobTitle, JobDescription, JobType, JobLocation, JobDeadline, numOfApplicants, OrgName, OrgDescription, OrgImage, OrgWebsite);
JobCardsArray[x] = tempObject;
x++;
}
// OOP First Card
if (JobCardsArray[0] != null)
{
Image1.ImageUrl = JobCardsArray[0].getOrgImage();
CompanyNamelbl.Text = JobCardsArray[0].getOrgName();
JobTitlelbl.Text = JobCardsArray[0].getJobTitle();
CompanyNamelbl2.Text = JobCardsArray[0].getOrgName();
lblJOrganizationDescription.Text = JobCardsArray[0].getJobDescription();
lblJobType.Text = JobCardsArray[0].getJobType();
lblOrgDescription.Text = JobCardsArray[0].getOrgDescription();
JobLink1.NavigateUrl = JobCardsArray[0].getOrgWebsite();
}
// OOP Second Card
if (JobCardsArray[1] != null)
{
Image2.ImageUrl = JobCardsArray[1].getOrgImage();
CompanyNamelbl3.Text = JobCardsArray[1].getOrgName();
JobTitlelbl2.Text = JobCardsArray[1].getJobTitle();
CompanyNamelbl4.Text = JobCardsArray[1].getOrgName();
lblJOrganizationDescription2.Text = JobCardsArray[1].getJobDescription();
lblJobType2.Text = JobCardsArray[1].getJobType();
lblOrgDescription2.Text = JobCardsArray[1].getOrgDescription();
JobLink2.NavigateUrl = JobCardsArray[1].getOrgWebsite();
}
// OOP Third Card
if (JobCardsArray[2] != null)
{
Image3.ImageUrl = JobCardsArray[2].getOrgImage();
CompanyNamelbl5.Text = JobCardsArray[2].getOrgName();
JobTitlelbl3.Text = JobCardsArray[2].getJobTitle();
CompanyNamelbl6.Text = JobCardsArray[2].getOrgName();
lblJOrganizationDescription3.Text = JobCardsArray[2].getJobDescription();
lblJobType3.Text = JobCardsArray[2].getJobType();
lblOrgDescription3.Text = JobCardsArray[2].getOrgDescription();
JobLink3.NavigateUrl = JobCardsArray[2].getOrgWebsite();
}
// OOP Fourth Card
if (JobCardsArray[3] != null)
{
Image4.ImageUrl = JobCardsArray[3].getOrgImage();
CompanyNamelbl7.Text = JobCardsArray[3].getOrgName();
JobTitlelbl4.Text = JobCardsArray[3].getJobTitle();
CompanyNamelbl8.Text = JobCardsArray[3].getOrgName();
lblJOrganizationDescription4.Text = JobCardsArray[3].getJobDescription();
lblJobType4.Text = JobCardsArray[3].getJobType();
lblOrgDescription4.Text = JobCardsArray[3].getOrgDescription();
JobLink4.NavigateUrl = JobCardsArray[3].getOrgWebsite();
}
sql.Close();
if (JobCardsArray[0] == null)
{
card1.Visible = false;
card2.Visible = false;
card3.Visible = false;
card4.Visible = false;
EmptyPostinglbl.Visible = true;
}
else if (JobCardsArray[3] == null && JobCardsArray[2] == null && JobCardsArray[1] == null)
{
card4.Visible = false;
card3.Visible = false;
card2.Visible = false;
}
else if (JobCardsArray[3] == null && JobCardsArray[2] == null)
{
card4.Visible = false;
card3.Visible = false;
}
else if (JobCardsArray[3] == null)
{
card4.Visible = false;
}
else
{
}
// Team member queries and setting
sql.Open();
System.Data.SqlClient.SqlCommand RecentRequests = new System.Data.SqlClient.SqlCommand();
RecentRequests.Connection = sql;
RecentRequests.CommandText = "SELECT TOP (5) ApplicationRequest.ApplicationID, JobListing.JobTitle, Organization.OrganizationName, Student.StudentGPA, Student.StudentImage, Organization.ExternalLink, Student.FirstName, Student.LastName FROM ApplicationRequest INNER JOIN JobListing ON ApplicationRequest.JobListingID = JobListing.JobListingID INNER JOIN Organization ON JobListing.OrganizationID = Organization.OrganizationEntityID INNER JOIN Student ON ApplicationRequest.StudentEntityID = Student.StudentEntityID WHERE (ApplicationRequest.ApprovedFlag = 'P') AND (Student.SchoolEntityID = '12') ORDER BY ApplicationRequest.ApplicationID DESC";
System.Data.SqlClient.SqlDataReader result = RecentRequests.ExecuteReader();
int y = 0;
while (result.Read())
{
// Stopped here, query in google drive, need to change query so results are stored in a student and joblisting object and add them to arrays, then set lbls based on arrays, need to make first and last name separate labels on cards
int applicationID = result.GetInt32(0);
String firstName = result.GetString(6);
String lastName = result.GetString(7);
double studentGPA = result.GetDouble(3);
String studentImage = result.GetString(4);
String jobTitle = result.GetString(1);
String orgName = result.GetString(2);
String orgWebsite = result.GetString(5);
Student tempStudent = new Student(applicationID, firstName, lastName, studentGPA, studentImage);
JobListing tempJob = new JobListing(jobTitle, orgName, orgWebsite);
StudentCardsArray[y] = tempStudent;
StudentCardJobInfoArray[y] = tempJob;
y++;
}
// OOP First Student Request Card
if (StudentCardsArray[0] != null)
{
StudentImage.ImageUrl = StudentCardsArray[0].getStudentImage();
FrontStudentName.Text = StudentCardsArray[0].getFirstName() + " " + StudentCardsArray[0].getLastName();
BackStudentName.Text = StudentCardsArray[0].getFirstName() + " " + StudentCardsArray[0].getLastName();
StudentJobTitlelbl.Text = StudentCardJobInfoArray[0].getJobTitle();
OrgTitlelbl.Text = StudentCardJobInfoArray[0].getOrgName();
StudentGPAlbl.Text = StudentCardsArray[0].getStudentGPA().ToString();
StudentLink1.NavigateUrl = StudentCardJobInfoArray[0].getOrgWebsite();
}
// OOP Second Student Request Card
if (StudentCardsArray[1] != null)
{
StudentImage2.ImageUrl = StudentCardsArray[1].getStudentImage();
FrontStudentName2.Text = StudentCardsArray[1].getFirstName() + " " + StudentCardsArray[1].getLastName();
BackStudentName2.Text = StudentCardsArray[1].getFirstName() + " " + StudentCardsArray[1].getLastName();
StudentJobTitlelbl2.Text = StudentCardJobInfoArray[1].getJobTitle();
OrgTitlelbl2.Text = StudentCardJobInfoArray[1].getOrgName();
StudentGPAlbl2.Text = StudentCardsArray[1].getStudentGPA().ToString();
StudentLink2.NavigateUrl = StudentCardJobInfoArray[1].getOrgWebsite();
}
// OOP Third Student Request Card
if (StudentCardsArray[2] != null)
{
StudentImage3.ImageUrl = StudentCardsArray[2].getStudentImage();
FrontStudentName3.Text = StudentCardsArray[2].getFirstName() + " " + StudentCardsArray[2].getLastName();
BackStudentName3.Text = StudentCardsArray[2].getFirstName() + " " + StudentCardsArray[2].getLastName();
StudentJobTitlelbl3.Text = StudentCardJobInfoArray[2].getJobTitle();
OrgTitlelbl3.Text = StudentCardJobInfoArray[2].getOrgName();
StudentGPAlbl3.Text = StudentCardsArray[2].getStudentGPA().ToString();
StudentLink3.NavigateUrl = StudentCardJobInfoArray[2].getOrgWebsite();
}
// OOP Fourth Student Request Card
if (StudentCardsArray[3] != null)
{
StudentImage4.ImageUrl = StudentCardsArray[3].getStudentImage();
FrontStudentName4.Text = StudentCardsArray[3].getFirstName() + " " + StudentCardsArray[3].getLastName();
BackStudentName4.Text = StudentCardsArray[3].getFirstName() + " " + StudentCardsArray[3].getLastName();
StudentJobTitlelbl4.Text = StudentCardJobInfoArray[3].getJobTitle();
OrgTitle4.Text = StudentCardJobInfoArray[3].getOrgName();
StudentGPAlbl4.Text = StudentCardsArray[3].getStudentGPA().ToString();
StudentLink4.NavigateUrl = StudentCardJobInfoArray[3].getOrgWebsite();
}
sql.Close();
if (StudentCardsArray[0] == null)
{
StudentCard1.Visible = false;
StudentCard2.Visible = false;
StudentCard3.Visible = false;
StudentCard4.Visible = false;
EmptyStudentslbl.Visible = true;
}
else if (StudentCardsArray[3] == null && StudentCardsArray[2] == null && StudentCardsArray[1] == null)
{
StudentCard4.Visible = false;
StudentCard3.Visible = false;
StudentCard2.Visible = false;
}
else if (StudentCardsArray[3] == null && StudentCardsArray[2] == null)
{
StudentCard4.Visible = false;
StudentCard3.Visible = false;
}
else if (StudentCardsArray[3] == null)
{
StudentCard4.Visible = false;
}
else
{
}
// Start of Tableau Charts
if (Session["schoolID"].Equals(12))
{
LouisaDesktop.Visible = true;
LouisaTablet.Visible = true;
LousiaPhone.Visible = true;
}
else if (Session["schoolID"].Equals(15))
{
TurnerDesktop.Visible = true;
TurnerTablet.Visible = true;
TurnerPhone.Visible = true;
}
}
private Element GetCellValue(SqlDataReader reader, int col, ElementType type)
{
switch (type) {
case ElementType.LONG:
return new Element(reader.GetInt32(col));
case ElementType.DOUBLE:
return new Element(reader.GetDouble(col));
case ElementType.STRING:
return new Element(reader.GetString(col));
case ElementType.DATETIME:
return new Element(reader.GetDateTime(col));
}
throw new Exception("invalidate data type");
}
public void PopulateProduct(SqlDataReader _dtr, Product obj)
{
int columnIndex = 0;
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_ID);
if (!_dtr.IsDBNull(columnIndex))
{
obj.ID = _dtr.GetInt32((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_NAME);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Name = _dtr.GetString((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_SERIAL);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Serial = _dtr.GetString((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_PUBLICPRICE);
if (!_dtr.IsDBNull(columnIndex))
{
obj.PublicPrice = _dtr.GetDouble((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_PRIVATEPRICE);
if (!_dtr.IsDBNull(columnIndex))
{
obj.PrivatePrice = _dtr.GetDouble((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_CATEGORYID);
if (!_dtr.IsDBNull(columnIndex))
{
obj.CategoryID = _dtr.GetInt32((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_PRODUCINGDATE);
if (!_dtr.IsDBNull(columnIndex))
{
obj.ProducingDate = _dtr.GetString((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_EXPIRYDATE);
if (!_dtr.IsDBNull(columnIndex))
{
obj.ExpiryDate = _dtr.GetString((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_VALUE);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Value = _dtr.GetString((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_TAX);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Tax = _dtr.GetDouble((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_PICTURE1);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Picture1 = _dtr.GetString((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_PICTURE2);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Picture2 = _dtr.GetString((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_WEIGHT);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Weight = _dtr.GetDouble((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_HEIGHT);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Height = _dtr.GetDouble((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_TAX);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Tax = _dtr.GetDouble((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_DISCOUNT);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Discount = _dtr.GetDouble((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_ISDELETED);
if (!_dtr.IsDBNull(columnIndex))
{
obj.IsDeleted = _dtr.GetBoolean((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_QUANTITY);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Quantity = _dtr.GetInt32((columnIndex));
}
columnIndex = _dtr.GetOrdinal(ProductDataMapper.CN_PRODUCT_PROVIDER);
if (!_dtr.IsDBNull(columnIndex))
{
obj.Provider = _dtr.GetInt32((columnIndex));
}
}
public Satellite(SqlDataReader dr)
{
Id = dr.GetInt32(SatelliteIdField);
Name = dr.GetString(SatelliteNameField);
Altitude = dr.GetDouble(OrbitalAltitudeField);
}
public async Task MessageReceivedAsync(IDialogContext context, IAwaitable <IMessageActivity> argument)
{
/*User Dialogs
* -Give me profit on Jumper Leads (Jumper leads will be flexible where it can be any entity)
* -Show me products that have made less then 50,000
*/
//Connect to DataWarehouse and put content of query into reader
using (var connection = new QC.SqlConnection(connectionString)) //Call out
{
connection.Open();
//Extract needed data from user text
var message = await argument;
string userText = message.Text;
if (userText.Contains("profit") && userText.Contains(":"))
{
string title = userText.Split(':')[1];
title = title.Trim(' ');
using (var Command = new QC.SqlCommand())
{
Command.Connection = connection;
Command.CommandType = System.Data.CommandType.Text; //Call out
Command.CommandText = @"select a.Profit, b.title from adw.ProfitableProducts a INNER JOIN adw.DimProductCatalog b ON a.productId=b.productId WHERE b.title='" + title + "' ";
QC.SqlDataReader reader = Command.ExecuteReader();
//await context.PostAsync("Output: ");
while (reader.Read())
{
await context.PostAsync(String.Format("Profit for {0}: ${1}", title, reader.GetDouble(0).ToString())); //Call out
// await context.PostAsync(reader.GetDouble(0).ToString());
}
if (reader.HasRows == false)
{
await context.PostAsync(String.Format("{0} Not in the database", title));
}
}
}
else if (userText.Contains("<") || userText.Contains("less than"))
{
//Grab integer value
//String res = userText.Split('<')[1];
//int lessThen = Int32.Parse(res.Trim(' '));
int lessThen = Int32.Parse(userText.Split('<')[1].Trim(' ').Replace(",", ""));
using (var Command = new QC.SqlCommand())
{
Command.Connection = connection;
Command.CommandType = System.Data.CommandType.Text; //Call out
Command.CommandText = @"select a.*, b.title from adw.ProfitableProducts a INNER JOIN adw.DimProductCatalog b ON a.productId=b.productID WHERE a.Profit<'" + lessThen + "' ";
QC.SqlDataReader reader = Command.ExecuteReader();
String result = String.Format("Products that made less then {0}: \n", lessThen);
while (reader.Read())
{
result = result + reader.GetString(3) + ", Profit: " + reader.GetDouble(2) + "; ";
}
result = result.Substring(0, result.Length - 2);
await context.PostAsync(result);
}
}
else if (userText.Contains("most profitable"))
{
//Grab integer value
//String res = userText.Split('<')[1];
//int lessThen = Int32.Parse(res.Trim(' '));
using (var Command = new QC.SqlCommand())
{
Command.Connection = connection;
Command.CommandType = System.Data.CommandType.Text; //Call out
Command.CommandText = @"select top 1 a.*, b.title from adw.ProfitableProducts a INNER JOIN adw.DimProductCatalog b ON a.productId=b.productID ORDER BY a.Profit DESC";
QC.SqlDataReader reader = Command.ExecuteReader();
String result = String.Format("Most Profitable Product : ");
int loopCount = 0;
while (reader.Read())
{
result = result + reader.GetString(3) + "; Profit: " + reader.GetDouble(2);
loopCount++;
}
if (loopCount == 1)
{
await context.PostAsync(result);
}
else
{
await context.PostAsync("You entered a command that I cannot process please enter pruduct or profit request.");
}
}
}
else if (userText.Contains("hi") || userText.Contains("hello") || userText.Contains("Hello") || userText.Contains("Hi"))
{
await context.PostAsync("Hello! Please ask me about the profitability of your products");
}
else
{
await context.PostAsync("You entered a command that I cannot process please enter pruduct or profit request.");
}
context.Wait(MessageReceivedAsync); //Call out
}
}
protected List<InclineRecord> ToInclineRecords(SqlDataReader sqlDataReader)
{
List<InclineRecord> lstRecords = null;
InclineRecord record = new InclineRecord();
while (sqlDataReader.Read())
{
record.MeasureId = sqlDataReader.GetInt32(0);
record.PipeId = sqlDataReader.GetInt32(1);
record.ProjectPointId = sqlDataReader.GetInt32(2);
record.MeasureTime = sqlDataReader.GetDateTime(3);
record.Alpha = sqlDataReader.GetDouble(4);
record.Beta = sqlDataReader.GetDouble(5);
lstRecords.Add(record);
}
return lstRecords;
}
public void GetDoubleTest ()
{
cmd.CommandText = "select type_double,type_float,convert(float,null)";
cmd.CommandText += " from numeric_family where id=1";
reader = cmd.ExecuteReader ();
reader.Read ();
// Test for standard exceptions
GetMethodTests("Double");
// Test if data is returned correctly
Assert.AreEqual (numericRow["type_double"], reader.GetDouble(0),
"#2 DataValidation Failed");
// Test for standard exceptions
GetMethodTests("SqlDouble");
// Test if data is returned correctly
Assert.AreEqual (numericRow["type_double"], reader.GetSqlDouble(0).Value,
"#4 DataValidation Failed");
reader.Close ();
}
static string SqlReadyValue(string sqlBuiltString, Column column, int columnIndex, SqlDataReader reader)
{
if (columnIndex > 0)
sqlBuiltString += ", ";
if (reader[columnIndex] == null || String.IsNullOrEmpty(reader[columnIndex].ToString()))
return sqlBuiltString += "NULL";
switch (column.DataType.SqlDataType)
{
case SqlDataType.Int:
sqlBuiltString += reader.GetInt32(columnIndex);
break;
case SqlDataType.SmallInt:
sqlBuiltString += reader.GetInt16(columnIndex);
break;
case SqlDataType.BigInt:
sqlBuiltString += reader.GetInt64(columnIndex);
break;
case SqlDataType.Float:
try
{
float floatNumber = reader.GetFloat(columnIndex);
sqlBuiltString += floatNumber.ToString(CultureInfo.InvariantCulture);
}
catch
{
double doubleNumber = reader.GetDouble(columnIndex);
sqlBuiltString += doubleNumber.ToString(CultureInfo.InvariantCulture);
}
break;
case SqlDataType.Decimal:
decimal decimalNumber = reader.GetDecimal(columnIndex);
sqlBuiltString += decimalNumber.ToString(CultureInfo.InvariantCulture);
break;
case SqlDataType.DateTime2:
case SqlDataType.DateTime:
var date = reader.GetDateTime(columnIndex);
sqlBuiltString += String.Format("TO_DATE('{0}', 'yyyy-mm-dd hh24:mi:ss')", date.ToString("yyyy-MM-dd HH:mm:ss"));
break;
case SqlDataType.Bit:
var bitData = reader.GetBoolean(columnIndex);
sqlBuiltString += bitData == true ? "1" : "0";
break;
case SqlDataType.NVarChar:
case SqlDataType.NVarCharMax:
case SqlDataType.VarChar:
sqlBuiltString += "'" + reader.GetString(columnIndex) + "'";
break;
case SqlDataType.UniqueIdentifier:
sqlBuiltString += "'" + reader.GetGuid(columnIndex).ToString() + "'";
break;
case SqlDataType.VarBinary:
case SqlDataType.VarBinaryMax:
sqlBuiltString += "NULL";
break;
}
return sqlBuiltString;
}
protected void Page_Load(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=ROMANNB-ПК;Initial Catalog=C:\CANDLEBASE\DATABASE1.MDF;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd1 = new System.Data.SqlClient.SqlCommand();
cmd1.CommandType = System.Data.CommandType.Text;
cmd1.Connection = sqlConnection1;
cmd1.CommandText = "SELECT * FROM portfolio_funds ;";
sqlConnection1.Open();
System.Data.SqlClient.SqlDataReader reader2 = cmd1.ExecuteReader();
reader2.Close();
reader2 = cmd1.ExecuteReader();
TableHeaderCell header = new TableHeaderCell();
header.RowSpan = 1;
header.ColumnSpan = 3;
header.Text = "Показатели счета";
header.Font.Bold = true;
header.HorizontalAlign = HorizontalAlign.Center;
header.VerticalAlign = VerticalAlign.Middle;
// Add the header to a new row.
TableRow headerRow = new TableRow();
headerRow.Cells.Add(header);
// Add the header row to the table.
Table1.Rows.AddAt(0, headerRow);
Table1.BorderWidth = 1;
Table1.CellPadding = 5;
if (reader2.HasRows)
{
while (reader2.Read())
{
TableRow r = new TableRow();
for (int i = 0; i <= 7; i++)
{
TableCell c = new TableCell();
if (reader2.GetDataTypeName(i) == "nvarchar" || reader2.GetDataTypeName(i) == "nchar")
{
c.Text = reader2.GetString(i);
}
else
{
c.Text = reader2.GetDouble(i).ToString();
}
r.HorizontalAlign = HorizontalAlign.Center;
r.BorderWidth = 1;
c.HorizontalAlign = HorizontalAlign.Center;
c.BorderWidth = 1;
r.Cells.Add(c);
}
Table1.Rows.Add(r);
}
}
cmd1.CommandText = "SELECT * FROM dbo.[Current] where Ticker = 'SiZ6' or Ticker = 'SNGSP' or Ticker = 'BRX6' ;";
reader2.Close();
System.Data.SqlClient.SqlDataReader reader = cmd1.ExecuteReader();
header = new TableHeaderCell();
header.RowSpan = 1;
header.ColumnSpan = 4;
header.Text = "Текущие цены";
header.Font.Bold = true;
header.HorizontalAlign = HorizontalAlign.Center;
header.VerticalAlign = VerticalAlign.Middle;
// Add the header to a new row.
headerRow = new TableRow();
headerRow.Cells.Add(header);
// Add the header row to the table.
Table2.Rows.AddAt(0, headerRow);
Table2.BorderWidth = 1;
Table2.CellPadding = 5;
if (reader.HasRows)
{
while (reader.Read())
{
TableRow r = new TableRow();
for (int i = 0; i <= 3; i++)
{
TableCell c = new TableCell();
if (reader.GetDataTypeName(i) == "nvarchar" || reader.GetDataTypeName(i) == "nchar")
{
c.Text = reader.GetString(i);
}
else
{
c.Text = reader.GetDouble(i).ToString();
}
r.HorizontalAlign = HorizontalAlign.Center;
r.BorderWidth = 1;
c.HorizontalAlign = HorizontalAlign.Center;
c.BorderWidth = 1;
r.Cells.Add(c);
}
TableCell b = new TableCell();
// Создать новый объект кнопки.
LinkButton LinkBtn = new LinkButton();
LinkBtn.CommandArgument = reader.GetString(0);
LinkBtn.CommandName = reader.GetString(0);
LinkBtn.Command += new CommandEventHandler(LinkBtn_Command);
LinkBtn.Text = "Удалить";
//Page.Form.Controls.Add(LinkBtn);
// b.Text = reader.GetString(0);
b.Controls.Add(LinkBtn);
///r.Cells.Add(b);
r.Cells.Add(b);
Table2.Rows.Add(r);
}
}
reader.Close();
sqlConnection1.Close();
Button1.Click += new EventHandler(this.GreetingBtn_Click);
/* if (IsPostBack)
* {
* GuestResponse rsvp = new GuestResponse();
*
* if (TryUpdateModel(rsvp, new FormValueProvider(ModelBindingExecutionContext)))
* {
* ResponseRepository.GetRepository().AddResponse(rsvp);
*
*
*
* if (rsvp.WillAttend.HasValue && rsvp.WillAttend.Value)
* {
*
* string path = @"d:\\MyTest.txt";
*
* try
* {
*
*
* // Create the file.
* using (FileStream fs = File.Create(path))
* {
* Byte[] info = new UTF8Encoding(true).GetBytes(rsvp.Name.ToString());
* // Add some information to the file.
*
* fs.Write(info, 0, info.Length);
* }
*
*
* }
*
* catch (Exception ex)
* {
* ;
* }
* Response.Redirect("myweb.aspx");
* }
* else
* {
* Response.Redirect("myweb.aspx");
* }
* }
* }*/
}
static public string SelectRowsByDates(int User_id)
{
using (var command = new QC.SqlCommand())
{
using (var connection = new QC.SqlConnection("Data Source=LENOVO-PC;Initial Catalog=DB_CourceProject;Integrated Security=True;"))
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
connection.Open();
command.Connection = connection;
command.CommandType = DT.CommandType.Text;
command.CommandText =
$@"SELECT dt, amount FROM costs WHERE user_id = {User_id}
ORDER BY dt DESC;";
QC.SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
sb.Append("").Append(String.Format("\n{0}\t{1} Р", reader.GetDateTime(0), reader.GetDouble(1)));
}
command.CommandText = null;
string str = sb.ToString();
sb.Clear();
if (String.IsNullOrEmpty(str))
{
return(String.Format("Пока что список твоих трат пуст\nотправь мне фотографию чека."));
}
else
{
return(str);
}
}
}
}
static public string SelectRowsByCategory(int User_id)
{
using (var command = new QC.SqlCommand())
{
using (var connection = new QC.SqlConnection("Data Source=LENOVO-PC;Initial Catalog=DB_CourceProject;Integrated Security=True;"
))
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
connection.Open();
command.Connection = connection;
command.CommandType = DT.CommandType.Text;
command.CommandText = $@"select name_category, sum(amount)
from costs
where user_id = {User_id}
group by name_category";
QC.SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
sb.Append("").Append(String.Format("\n{0}\t{1} ", reader.GetValue(0), reader.GetDouble(1)));
}
string str = sb.ToString();
if (String.IsNullOrEmpty(str))
{
return(String.Format("Пока что список твоих трат по этой категории пуст\nотправь мне фотографию чека."));
}
else
{
return(str);
}
}
}
}
private static Mention GetMentionFromDataReader(SqlDataReader dr)
{
var id = dr.GetInt32(0);
var occurred = dr.GetDateTime(1);
var updated = dr.GetDateTime(2);
var name = dr.IsDBNull(3) ? "" : dr.GetString(3);
var sent = dr.GetDouble(4);
var guid = dr.IsDBNull(5) ? "" : dr.GetString(5);
var desc = dr.IsDBNull(6) ? "" : dr.GetString(6);
var created = dr.GetDateTime(7);
var type = dr.IsDBNull(8) ? "" : dr.GetString(8);
var author = dr.IsDBNull(9) ? "" : dr.GetString(9);
var influence = dr.IsDBNull(10) ? 0L : (long)dr.GetInt32(10);
var followers = dr.IsDBNull(11) ? 0L : (long)dr.GetInt32(11);
var klout = dr.IsDBNull(12) ? 0L : (long)dr.GetInt32(12);
var comments = dr.IsDBNull(13) ? 0L : (long)dr.GetInt32(13);
var mention = new Mention
{
Type = type,
Name = name,
Sentiment = sent,
Guid = guid,
Description = desc,
Id = id,
Author = author,
Influence = influence,
Followers = followers,
Klout = klout,
Comments = comments,
//dates
OccurredOn = occurred,
UpdatedOn = updated,
CreatedOn = created,
//ticks
OccurredOnTicks = occurred.Ticks,
UpdatedOnTicks = updated.Ticks,
CreatedOnTicks = created.Ticks,
//OccurredOnMinuteTicks = new DateTime(occurred.Year, occurred.Month, occurred.Day, occurred.Hour, occurred.Minute, 0).Ticks,
//OccurredOnQuarterHourTicks = new DateTime(occurred.Year, occurred.Month, occurred.Day, occurred.Hour, occurred.Minute - occurred.Minute % 15, 0).Ticks,
//OccurredOnDayTicks = new DateTime(occurred.Year, occurred.Month, occurred.Day).Ticks,
//OccurredOnWeekTicks = new DateTime(occurred.Year, occurred.Month, occurred.Day).Ticks,
//OccurredOnMonthTicks = new DateTime(occurred.Year, occurred.Month, 1).Ticks,
//OccurredOnQuarterTicks = new DateTime(occurred.Year, (((occurred.Month - 1) / 3) * 3 + 1), 1).Ticks,
//OccurredOnYearTicks = new DateTime(occurred.Year, 1, 1).Ticks
};
return mention;
}
protected object safeGetDouble(SqlDataReader reader, string columnName)
{
int ordinal = reader.GetOrdinal(columnName);
return reader.IsDBNull(ordinal) ? null : (object)reader.GetDouble(ordinal);
}
public static string getFieldValue(SqlDataReader dr, int ind, string def)
{
string val = def;
if (!dr.IsDBNull(ind))
{
switch (System.Type.GetTypeCode(dr.GetFieldType(ind)))
{
case System.TypeCode.Boolean:
val = (dr.GetBoolean(ind) ? "1" : "0");
break;
case System.TypeCode.Int16:
val = dr.GetInt16(ind).ToString();
break;
case System.TypeCode.Int32:
val = dr.GetInt32(ind).ToString();
break;
case System.TypeCode.Int64:
val = dr.GetInt64(ind).ToString();
break;
case System.TypeCode.Single:
val = dr.GetFloat(ind).ToString();
break;
case System.TypeCode.Double:
val = dr.GetDouble(ind).ToString();
break;
case System.TypeCode.Decimal:
val = dr.GetDecimal(ind).ToString();
break;
case System.TypeCode.DateTime:
val = DBManagerSQL.DBDateToFormatedDate(dr.GetDateTime(ind));
break;
case System.TypeCode.String:
val = dr.GetString(ind);
break;
}
}
return val;
}
private double GetDBDouble(SqlDataReader reader, string columnName)
{
try
{
int ordinal = reader.GetOrdinal(columnName);
return reader.IsDBNull(ordinal) ? -1 : reader.GetDouble(ordinal);
}
catch (IndexOutOfRangeException)
{
return -1;
}
}
// Copy multiple fields from reader to ITypedSettersV3
// Assumes caller enforces that reader and setter metadata are compatible
internal static void FillCompatibleITypedSettersFromReader( SmiEventSink_Default sink, ITypedSettersV3 setters, SmiMetaData[] metaData, SqlDataReader reader ) {
for ( int i = 0; i < metaData.Length; i++ ) {
if ( reader.IsDBNull(i) ) {
ValueUtilsSmi.SetDBNull_Unchecked( sink, setters, i );
}
else {
switch ( metaData[i].SqlDbType ) {
case SqlDbType.BigInt:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.Int64 ) );
ValueUtilsSmi.SetInt64_Unchecked( sink, setters, i, reader.GetInt64(i) );
break;
case SqlDbType.Binary:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlBytes ) );
ValueUtilsSmi.SetSqlBytes_LengthChecked( sink, setters, i, metaData[i], reader.GetSqlBytes(i), 0 );
break;
case SqlDbType.Bit:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.Boolean ) );
SetBoolean_Unchecked( sink, setters, i, reader.GetBoolean(i) );
break;
case SqlDbType.Char:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlChars ) );
SetSqlChars_LengthChecked( sink, setters, i, metaData[i], reader.GetSqlChars(i), 0 );
break;
case SqlDbType.DateTime:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.DateTime ) );
SetDateTime_Checked( sink, setters, i, metaData[i], reader.GetDateTime(i) );
break;
case SqlDbType.Decimal:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlDecimal ) );
SetSqlDecimal_Unchecked( sink, setters, i, reader.GetSqlDecimal(i) );
break;
case SqlDbType.Float:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.Double ) );
SetDouble_Unchecked( sink, setters, i, reader.GetDouble(i) );
break;
case SqlDbType.Image:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlBytes ) );
SetSqlBytes_LengthChecked( sink, setters, i, metaData[i], reader.GetSqlBytes(i), 0 );
break;
case SqlDbType.Int:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.Int32 ) );
SetInt32_Unchecked( sink, setters, i, reader.GetInt32(i) );
break;
case SqlDbType.Money:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlMoney ) );
SetSqlMoney_Unchecked( sink, setters, i, metaData[i], reader.GetSqlMoney(i) );
break;
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlChars ) );
SetSqlChars_LengthChecked( sink, setters, i, metaData[i], reader.GetSqlChars(i), 0 );
break;
case SqlDbType.Real:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.Single ) );
SetSingle_Unchecked( sink, setters, i, reader.GetFloat(i) );
break;
case SqlDbType.UniqueIdentifier:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.Guid ) );
SetGuid_Unchecked( sink, setters, i, reader.GetGuid(i) );
break;
case SqlDbType.SmallDateTime:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.DateTime ) );
SetDateTime_Checked( sink, setters, i, metaData[i], reader.GetDateTime(i) );
break;
case SqlDbType.SmallInt:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.Int16 ) );
SetInt16_Unchecked( sink, setters, i, reader.GetInt16(i) );
break;
case SqlDbType.SmallMoney:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlMoney ) );
SetSqlMoney_Checked( sink, setters, i, metaData[i], reader.GetSqlMoney(i) );
break;
case SqlDbType.Text:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlChars ) );
SetSqlChars_LengthChecked( sink, setters, i, metaData[i], reader.GetSqlChars(i), 0 );
break;
case SqlDbType.Timestamp:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlBytes ) );
SetSqlBytes_LengthChecked( sink, setters, i, metaData[i], reader.GetSqlBytes(i), 0 );
break;
case SqlDbType.TinyInt:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.Byte ) );
SetByte_Unchecked( sink, setters, i, reader.GetByte(i) );
break;
case SqlDbType.VarBinary:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlBytes ) );
SetSqlBytes_LengthChecked( sink, setters, i, metaData[i], reader.GetSqlBytes(i), 0 );
break;
case SqlDbType.VarChar:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.String ) );
SetSqlChars_LengthChecked( sink, setters, i, metaData[i], reader.GetSqlChars(i), 0 );
break;
case SqlDbType.Xml:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlXml ) );
SetSqlXml_Unchecked( sink, setters, i, reader.GetSqlXml(i) );
break;
case SqlDbType.Variant:
object o = reader.GetSqlValue(i);
ExtendedClrTypeCode typeCode = MetaDataUtilsSmi.DetermineExtendedTypeCode( o );
SetCompatibleValue( sink, setters, i, metaData[i], o, typeCode, 0 );
break;
case SqlDbType.Udt:
Debug.Assert( CanAccessSetterDirectly( metaData[i], ExtendedClrTypeCode.SqlBytes ) );
SetSqlBytes_LengthChecked( sink, setters, i, metaData[i], reader.GetSqlBytes(i), 0 );
break;
default:
// In order for us to get here we would have to have an
// invalid instance of SqlDbType, or one would have to add
// new member to SqlDbType without adding a case in this
// switch, hence the assert - it must be
Debug.Assert( false, "unsupported DbType:" + metaData[i].SqlDbType.ToString() );
throw ADP.NotSupported();
}
}
}
}
public static double GetDouble(string pColName,SqlDataReader pReader)
{
return pReader.GetDouble(pReader.GetOrdinal(pColName));
}
protected List<GPSRecord> ToGPSRecords(SqlDataReader sqlDataReader)
{
List<GPSRecord> lstRecords = null;
GPSRecord record = new GPSRecord();
while (sqlDataReader.Read())
{
record.MeasureId = sqlDataReader.GetInt32(0);
record.PipeId = sqlDataReader.GetInt32(1);
record.ProjectPointId = sqlDataReader.GetInt32(2);
record.MeasureTime = sqlDataReader.GetDateTime(3);
record.Location = new Point3D(sqlDataReader.GetDouble(4),
sqlDataReader.GetDouble(5),
sqlDataReader.GetDouble(6));
lstRecords.Add(record);
}
return lstRecords;
}
public static double getFieldValue(SqlDataReader dr, int ind, double def)
{
double val = def;
if (!dr.IsDBNull(ind))
{
switch (System.Type.GetTypeCode(dr.GetFieldType(ind)))
{
case System.TypeCode.Boolean:
val = (dr.GetBoolean(ind) ? 1.0 : 0.0);
break;
case System.TypeCode.Int16:
val = System.Convert.ToDouble(dr.GetInt16(ind));
break;
case System.TypeCode.Int32:
val = System.Convert.ToDouble(dr.GetInt32(ind));
break;
case System.TypeCode.Int64:
val = System.Convert.ToDouble(dr.GetInt64(ind));
break;
case System.TypeCode.Single:
val = System.Convert.ToDouble(dr.GetFloat(ind));
break;
case System.TypeCode.Double:
val = dr.GetDouble(ind);
break;
case System.TypeCode.Decimal:
val = System.Convert.ToDouble(dr.GetDecimal(ind));
break;
}
}
return val;
}
public String[] getDBWeather()
{
comm.Connection = conn;
comm.Connection.Open();
comm.CommandText = "SELECT TOP 1 [TEMPRETURE],[HUMIDITY],[WINDSPEED],[WINDDIRECTION],[AIRPRESSURE] FROM [SunCoreDB].[dbo].[PERF_WEATHER2]";
dr = comm.ExecuteReader();
dr.Read();
String []weather=new String[5];
//double tmp = dr.GetDouble(0);
//int a;
weather[0] = dr.GetDouble(0).ToString();
weather[1] = dr.GetDouble(1).ToString();
weather[2] = dr.GetDouble(2).ToString();
weather[3] = dr.GetDouble(3).ToString();
weather[4] = dr.GetDouble(4).ToString();
comm.Connection.Close();
return weather;
}
public LaunchSite(SqlDataReader dr)
{
Id = dr.GetInt32(LaunchSiteIdField);
Name = dr.GetString(LaunchSiteNameField);
MaxAltitude = dr.GetDouble(MaximumAltitudeField);
}
public Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.YearItem> ReadFromMonthTable(string connectionString)
{
DERMSCommon.SCADACommon.YearItem itemYear = null;
Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.YearItem> yearItems = new Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.YearItem>();
Tuple <long, DateTime> key = null;
Tuple <long, DateTime> keyY = null;
Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.MonthItem> monthItemsData = new Dictionary <Tuple <long, DateTime>, DERMSCommon.SCADACommon.MonthItem>();
using (System.Data.SqlClient.SqlConnection _con = new System.Data.SqlClient.SqlConnection(connectionString))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT Timestamp, Gid, E, PMax, PMin, PAvg FROM dbo.Month", _con))
{
_con.Open();
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
{
// Check is the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
DERMSCommon.SCADACommon.MonthItem c = new DERMSCommon.SCADACommon.MonthItem();
// To avoid unexpected bugs access columns by name.
try
{
c.E = reader.GetDouble(reader.GetOrdinal("E"));
c.PAvg = reader.GetDouble(reader.GetOrdinal("PAvg"));
c.PMax = reader.GetDouble(reader.GetOrdinal("PMax"));
c.PMin = reader.GetDouble(reader.GetOrdinal("PMin"));
c.Timestamp = reader.GetDateTime(reader.GetOrdinal("Timestamp"));
c.Gid = reader.GetInt64(reader.GetOrdinal("Gid"));
//c.P = reader.GetDouble(reader.GetOrdinal("P"));
key = new Tuple <long, DateTime>(c.Gid, c.Timestamp);
monthItemsData.Add(key, c);
}
catch (Exception e)
{ }
}
}
}
_con.Close();
}
}
bool ok = false;
foreach (var d in monthItemsData)
{
ok = false;
//itemDay = new DERMSCommon.SCADACommon.DayItem(d.Key.Item1, d.Key.Item2.Date.AddHours(d.Key.Item2.Hour), MinProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), MaxProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), AvgProductionPerHour(d.Key.Item2.Hour, d.Key.Item2.DayOfYear, collectItemsData), 0, 0);
itemYear = new DERMSCommon.SCADACommon.YearItem(d.Key.Item1, d.Key.Item2.Date, MinProductionPerMonth(d.Key.Item2.Month, d.Key.Item2.Year, monthItemsData, d.Key.Item1), MaxProductionPerMonth(d.Key.Item2.Month, d.Key.Item2.Year, monthItemsData, d.Key.Item1), AvgProductionPerMonth(d.Key.Item2.Month, d.Key.Item2.Year, monthItemsData, d.Key.Item1), 0, d.Value.P);
keyY = new Tuple <long, DateTime>(itemYear.Gid, itemYear.Timestamp);
if (yearItems.Count > 0)
{
foreach (var y in yearItems)
{
if (!(y.Key.Item2.Month == keyY.Item2.Month && y.Key.Item1 == keyY.Item1))
{
ok = true;
}
else
{
ok = false;
break;
}
}
if (ok)
{
yearItems.Add(keyY, itemYear);
}
}
else
{
yearItems.Add(keyY, itemYear);
}
//if (!yearItems.ContainsKey(keyY))
// yearItems.Add(keyY, itemYear);
}
return(yearItems);
}
protected static void PopulateEntity(SqlDataReader reader, ServerEntity entity, Dictionary<string, PropertyInfo> propMap)
{
for (int i = 0; i < reader.FieldCount; i++)
{
String columnName = reader.GetName(i);
// Special case for when we select a range of values with an EntityBroker, just ignore
if (columnName.Equals("RowNum"))
continue;
if (columnName.Equals("GUID"))
{
Guid uid = reader.GetGuid(i);
entity.SetKey(new ServerEntityKey(entity.Name, uid));
continue;
}
if (columnName.Equals(entity.Name) && columnName.Contains("Enum"))
columnName = "Enum";
PropertyInfo prop;
if (!propMap.TryGetValue(columnName, out prop))
throw new EntityNotFoundException("Unable to match column to property: " + columnName, null);
if (columnName.Contains("GUID"))
columnName = columnName.Replace("GUID", "Key");
if (reader.IsDBNull(i))
{
prop.SetValue(entity, null, null);
continue;
}
Type propType = prop.PropertyType;
if (propType == typeof(String))
prop.SetValue(entity, reader.GetString(i), null);
else if (propType == typeof(ServerEntityKey))
{
Guid uid = reader.GetGuid(i);
prop.SetValue(entity, new ServerEntityKey(columnName.Replace("Key", String.Empty), uid), null);
}
else if (propType == typeof(DateTime))
prop.SetValue(entity, reader.GetDateTime(i), null);
else if (propType == typeof(DateTime?))
{
if (reader.IsDBNull(i))
{
prop.SetValue(entity, null, null);
}
else
{
prop.SetValue(entity, reader.GetDateTime(i), null);
}
}
else if (propType == typeof(bool))
prop.SetValue(entity, reader.GetBoolean(i), null);
else if (propType == typeof(Int32))
prop.SetValue(entity, reader.GetInt32(i), null);
else if (propType == typeof(Int16))
prop.SetValue(entity, reader.GetInt16(i), null);
else if (propType == typeof(double))
prop.SetValue(entity, reader.GetDouble(i), null);
else if (propType == typeof(Decimal))
prop.SetValue(entity, reader.GetDecimal(i), null);
else if (propType == typeof(float))
prop.SetValue(entity, reader.GetFloat(i), null);
else if (propType == typeof(XmlDocument))
{
SqlXml xml = reader.GetSqlXml(i);
if (xml!=null && !xml.IsNull)
{
XmlReader xmlReader = xml.CreateReader();
if (xmlReader != null)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xmlReader);
prop.SetValue(entity, xmlDoc, null);
}
else
prop.SetValue(entity, null, null);
}
else
{
prop.SetValue(entity, null, null);
}
}
else if (typeof(ServerEnum).IsAssignableFrom(propType))
{
short enumVal = reader.GetInt16(i);
ConstructorInfo construct = prop.PropertyType.GetConstructor(new Type[0]);
ServerEnum val = (ServerEnum)construct.Invoke(null);
val.SetEnum(enumVal);
prop.SetValue(entity, val, null);
}
else
throw new EntityNotFoundException("Unsupported property type: " + propType, null);
}
}
/// <summary>
/// Creates a new instance of the HistoricoAnalitico class and populates it with data from the specified SqlDataReader.
/// </summary>
private Analitico MapDataReader(SqlDataReader dataReader)
{
Analitico analitico = new Analitico();
analitico.CatedraComision = dataReader.GetString("CatedraComision", null);
analitico.DNI = dataReader.GetInt32("DNI", 0);
analitico.Fecha = dataReader.GetDateTime("Fecha", new DateTime(0));
analitico.Folio = dataReader.GetString("Folio", null);
analitico.Libro = dataReader.GetString("Libro", null);
analitico.IdMateria = dataReader.GetInt32("IdMateria", 0);
analitico.Materia = dataReader.GetString("Materia", null);
if (!dataReader.IsDBNull(8))
analitico.Nota = dataReader.GetDouble(8);
analitico.Plan = dataReader.GetInt32("Plan", 0);
analitico.Resolucion = dataReader.GetString("Resolucion", null);
analitico.SubFolio = dataReader.GetString("SubFolio", null);
analitico.TipoInscripcion = dataReader.GetString("TipoInscripcion", null);
analitico.Tomo = dataReader.GetString("Tomo", null);
analitico.TurnoInscripcion = dataReader.GetDateTime("TurnoInscripcion", new DateTime(0));
analitico.UltimoIngreso = dataReader.GetDateTime("UltimoIngreso", new DateTime(0));
return analitico;
}
