protected void btnLogin_Click(object sender, EventArgs e)
{
string userLoginQuery = "select Type from tblUsers where ID = '" + txtID.Text + "' and Password = '******'";
DBCon dbConnection = new DBCon();
dbConnection.ConOpen();
SqlDataReader typeReader = dbConnection.ExecuteReader(userLoginQuery);
if (typeReader.Read())
{
Session["UserId"] = txtID.Text;
string userType = typeReader.GetString(0);
dbConnection.ConClose();
switch (userType)
{
case "TEACHER":
Response.Redirect("~/Teacher/TeacherHome.aspx");
break;
case "STUDENT":
Response.Redirect("~/Student/StudentHome.aspx");
break;
case "ADMIN":
Response.Redirect("~/Admin/AdminHome.aspx");
break;
}
}
else
{
lblLoginStatus.Text = "Invalid Username/ Password";
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Session["Username"] == null)
{
Response.Redirect("redirectionpage.aspx"); // Help From http://stackoverflow.com/questions/12219246/submit-show-results-delay-3-seconds-and-redirect
};
mainPageUsername.Text = Session["Username"].ToString();
int id = Convert.ToInt32(Request.QueryString["id"]);
var db = new DBCon();
var reader = db.Execute(string.Format("SELECT * FROM Notes WHERE ID={0}", id));
while (reader.Read())
{
noteName.Text = reader["noteTitle"].ToString();
nn = reader["noteTitle"].ToString();
ID = id;
noteBody.Text = reader["note2"].ToString();
DateTime.Text = reader["noteTime"].ToString();
}
db.Close();
}
}
protected void submit_Click(object sender, EventArgs e)
{
DateTime t = System.DateTime.Now;
string time = t.ToString();
int i = 0;
if(reminderName.Text != "")
{
int id = Convert.ToInt32(Request.QueryString["id"]);
string id1 = id.ToString();
var con = new DBCon();
string sql = string.Format("UPDATE Reminders SET name = '{0}' WHERE ID = {1}", reminderName.Text, id);
var reader = con.Execute(sql);
con.Close();
var con2 = new DBCon();
string sql2 = string.Format("UPDATE Reminders SET reminder = '{0}' WHERE ID = {1}", reminderBody.Text, id);
var reader2 = con2.Execute(sql2);
con2.Close();
Response.Redirect("success.aspx");
}
else
errorLabelreminder.Text = "*Enter Title!";
}
protected void Button1_Click(object sender, EventArgs e)
{
var con = new DBCon();
string sql = string.Format("SELECT ID, Username, Password FROM users WHERE Username = '******' AND Password = '******'", loginId.Text, loginPass.Text);
var reader = con.Execute(sql);
while (reader.Read())
{
Session["ID"] = reader["ID"];
Session["Password"] = reader["Password"];
}
if (reader.HasRows)
{
checking.Text = "Success!";
Session["Username"] = loginId.Text;
con.Close();
con.Close();
Response.Redirect("mainpage.aspx");
}
else
{
checking.Text = "Invalid Username or Password!";
con.Close();
con.Close();
}
}
protected void btnInsert_Click(object sender, EventArgs e)
{
bool isValidOperation = true;
string insertQuery = "";
SqlDataReader listDaySession;
if (txtComCode.Text != "" && ddlTutor.SelectedValue != "")
{
string DaySession;
DBCon myCon2 = new DBCon();
string query = "select DaySession from tblCourseTeacherMap where ComCod = '" + ddlCourseTitle.SelectedValue + "' and TeacherID = '" + ddlTutor.SelectedValue + "'";
if (ddlOperation.SelectedValue == "Insert")
{
myCon.ConOpen();
listDaySession = myCon.ExecuteReader(query);
while (listDaySession.Read() && isValidOperation)
{
DaySession = listDaySession.GetString(0);
string checkQuery = "select count(*) from tblStudentCourseMap where DaySession = '" + DaySession + "'";
int Occurence = myCon2.ExecuteScalarInt(checkQuery);
if (Occurence > 0)
{
Response.Write("<script LANGUAGE='JavaScript' >alert('" + DaySession + " has another entry, aborting operation. Please remove entry and try again.');</script>");
isValidOperation = false;
break;
}
}
myCon.ConClose();
}
myCon.ConOpen();
query = "select DaySession from tblCourseTeacherMap where ComCod = '" + ddlCourseTitle.SelectedValue + "' and TeacherID = '" + ddlTutor.SelectedValue + "'";
listDaySession = myCon.ExecuteReader(query);
while (listDaySession.Read() && isValidOperation)
{
DaySession = listDaySession.GetString(0);
if (ddlOperation.SelectedValue == "Insert")
{
insertQuery = "insert into tblStudentCourseMap(StudentID,ComCod,DaySession,TeacherID) values ('" + Current_User_ID + "','" + txtComCode.Text + "','" + DaySession + "','" + ddlTutor.SelectedValue + "')";
}
else
{
if (ddlOperation.SelectedValue == "Remove")
{
insertQuery = "Delete from tblStudentCourseMap where StudentID = '" + Current_User_ID + "' and ComCod = '" + txtComCode.Text + "' and DaySession = '" + DaySession + "' and TeacherID = '" + ddlTutor.SelectedValue + "'";
}
}
myCon2.ExecuteNonQuery(insertQuery);
}
myCon.ConClose();
if (isValidOperation)
{
Make_Dirty_Approval_Status();
Response.Redirect(Request.RawUrl);
}
}
}
protected void newUsernameSubmit_Click(object sender, EventArgs e)
{
string sql2 = string.Format("UPDATE users SET Username = '******' WHERE ID = {1}", newUserName.Text, Session["ID"]);
var db2 = new DBCon();
var reader2 = db2.Execute(sql2);
db2.Close();
Session.RemoveAll();
Response.Redirect("Default.aspx");
}
protected void noteSubmitButton_Click(object sender, EventArgs e)
{
DateTime t = DateTime.Now;
string time = t.ToString();
if (newNoteName.Text != "")
{
var con = new DBCon();
string sql = string.Format("INSERT INTO Notes ([noteTitle], [note2], [noteTime], [userID]) VALUES ('{0}', '{1}', '{2}',{3})", newNoteName.Text, newNote.Text,time, Session["ID"]);
var reader = con.Execute(sql);
con.Close();
Response.Redirect("noteSuccess.aspx");
}
else
errorLabelNotes.Text = "*Enter Title!";
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["Username"] == null)
{
Response.Redirect("redirectionpage.aspx"); // Help From http://stackoverflow.com/questions/12219246/submit-show-results-delay-3-seconds-and-redirect
};
mainPageUsername.Text = Session["Username"].ToString();
string sql = string.Format("SELECT * FROM Reminders WHERE userID={0}", Session["ID"]);
var db = new DBCon();
var reader = db.Execute(sql);
var sb = new StringBuilder();
while (reader.Read())
{
sb.AppendLine("<tr>");
sb.AppendLine(string.Format("<td><a href='viewreminder.aspx?id={0}'>{1}</a></td>", reader["ID"], reader["name"]));
sb.AppendLine("</tr>");
}
lbl_table.Text = sb.ToString();
db.Close();
}
public static List <FeedbkResponse> GetAll(int Orgid, DateTime Fdate, DateTime Tdate)
{
DBCon dBCon = new DBCon();
SqlCommand cmd = null;
SqlDataReader SDR = null;
List <FeedbkResponse> listfeedbk = new List <FeedbkResponse>();
try
{
string Quary = "Select * From FeedbkResponse where OrgId=" + Orgid;
if (Fdate != null && Tdate != null)
{
Quary += "and CreateDate between '" + Fdate.ToString("MM/dd/yyyy") + "' and '" + Tdate.ToString("MM/dd/yyyy HH:mm:ss") + "";
}
cmd = new SqlCommand(Quary, dBCon.Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
FeedbkResponse OBJfeedbk = new FeedbkResponse();
OBJfeedbk.QID = SDR.GetInt32(0);
OBJfeedbk.ResponseType = SDR.GetInt32(1);
OBJfeedbk.FeedbkFormId = SDR.GetInt32(2);
OBJfeedbk.StarCnt = SDR.GetInt32(3);
OBJfeedbk.Subject = SDR.GetString(4);
OBJfeedbk.LikeCnt = SDR.GetInt32(5);
OBJfeedbk.DislikeCnt = SDR.GetInt32(6);
OBJfeedbk.NormalOkCnt = SDR.GetInt32(7);
OBJfeedbk.FeedbkId = SDR.GetInt32(8);
OBJfeedbk.ObjectiveOptions = SDR.GetString(9);
OBJfeedbk.CreateDate = SDR.GetDateTime(10);
OBJfeedbk.CID = SDR.GetInt32(11);
OBJfeedbk.OrgId = SDR.GetInt32(12);
listfeedbk.Add(OBJfeedbk);
}
}
catch (System.Exception e) { e.ToString(); }
finally { cmd.Dispose();; dBCon.Con.Close(); }
return(listfeedbk);
}
public int JDSave()
{
int JRow = 0;
DBCon con = new DBCon();
SqlCommand cmd = null;
try
{
string Quary = "";
if (this.JDID == 0)
{
Quary = "Insert Into ACJournalEntryDetails Values (@JornalEntryId,@GroupId,@DRLedgerId,@CRLedgerId,@Amount,@Date);SELECT SCOPE_IDENTITY();";
}
else
{
Quary = "Update ACJournalEntryDetails Set JournalEntryId=@JornalEntryId,GroupId=@GroupId,DRLedgerId=@DRLedgerId,CRLedgerId=@CRLedgerId,Amount=@Amount,Date=@Date where ID=@ID";
}
cmd = new SqlCommand(Quary, con.Con);
cmd.Parameters.AddWithValue("@ID", this.AID);
cmd.Parameters.AddWithValue("@JornalEntryId", this.JournalEntryId);
cmd.Parameters.AddWithValue("@GroupId", this.GroupId);
cmd.Parameters.AddWithValue("@DRLedgerId", this.DRLedgerId);
cmd.Parameters.AddWithValue("@CRLedgerId", this.CRLedgerId);
cmd.Parameters.AddWithValue("@Amount", this.JEDAmount);
cmd.Parameters.AddWithValue("@Date", this.Date);
if (this.JDID == 0)
{
JRow = Convert.ToInt32(cmd.ExecuteScalar());
this.JDID = JRow;
}
else
{
JRow = cmd.ExecuteNonQuery();
//this.CategoryID = Row;
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
return(JRow);
}
public static HandleLog GetHandleLogById(string LogID)
{
HandleLog HL = new HandleLog();
OracleConnection oraFileConn;
OracleCommand oraCMD;
OracleDataReader oraDataReader;
oraFileConn = DBCon.OraConOpen();;
string sql;
sql = "SELECT * FROM HANDLELOG WHERE LOGID = :p1";
oraCMD = new OracleCommand(sql, oraFileConn);
oraCMD.Parameters.Add(":p1", LogID);
try { oraDataReader = oraCMD.ExecuteReader(); }
catch { return(null); }
if (!oraDataReader.HasRows)
{
return(null);
}
while (oraDataReader.Read())
{
int fieldCount = oraDataReader.FieldCount;
for (int i = 0; i < fieldCount; i++)
{
string fieldName = oraDataReader.GetName(i).ToString();
HL = InitHandleLogData(HL, oraDataReader[fieldName], fieldName);
}
}
oraFileConn.Close();
oraCMD.Dispose();
return(HL);
}
public int Save()
{
int Row = 0;
DBCon con = new DBCon();
SqlCommand cmd = null;
try
{
string Quary = "";
if (this.ID == 0)
{
Quary = "Insert into FeedBackQuestion values (@Title,@Status,@QuestionType,@FeedBKFormID,@OrderNo); SELECT SCOPE_IDENTITY();";
}
else
{
Quary = "Update FeedBackQuestion Set Title=@Title,Status=@Status,QuestionType=@QuestionType,FeedBKFormID=@FeedBKFormID,OrderNo=@OrderNo where ID=@ID";
}
cmd = new SqlCommand(Quary, con.Con);
cmd.Parameters.AddWithValue("@ID", this.ID);
cmd.Parameters.AddWithValue("@Title", this.Title);
cmd.Parameters.AddWithValue("@Status", this.Status);
cmd.Parameters.AddWithValue("@QuestionType", this.QuestionType);
cmd.Parameters.AddWithValue("@FeedBkFormID", this.FeedBkFormID);
cmd.Parameters.AddWithValue("@OrderNo", this.OrderNo);
if (this.ID == 0)
{
Row = Convert.ToInt32(cmd.ExecuteScalar());
this.ID = Row;
}
else
{
Row = cmd.ExecuteNonQuery();
//this.CategoryID = Row;
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
return(Row);
}
protected void signUp_Click(object sender, EventArgs e)
{
if ((newPass.Text.Length != 0) && (newId.Text.Length != 0))
{
string sql2 = string.Format("SELECT Username FROM users WHERE Username = '******'", newId.Text);
var con = new DBCon();
var reader = con.Execute(sql2);
RegexUtilities util = new RegexUtilities();
bool check= util.IsValidEmail(newEmailid.Text);
if (!reader.HasRows && check)
{
con.Close();
string sql = string.Format("INSERT INTO users ([Username], [Password], [email]) VALUES ('{0}', '{1}', '{2}')", newId.Text, newPass.Text,newEmailid.Text);
con.Execute(sql);
checking.Text = "Account Created Successfully!";
con.Close();
}
else if(reader.HasRows)
{
checking.Text = "Username Already Exists!";
con.Close();
}
else if (!check)
{
checking.Text = "Invalid Email ID!";
con.Close();
}
con.Close();
}
else
{
checking.Text = "Invalid Username or Password!";
}
}
public static Role GetRoleById(string roleId)
{
Role role = new Role();
OracleConnection oraRasterConn;
OracleCommand oraCMD;
OracleDataReader oraDataReader;
oraRasterConn = DBCon.OraConOpen();;
string sql;
sql = "SELECT * FROM ROLE WHERE ROLEID = :p1";
oraCMD = new OracleCommand(sql, oraRasterConn);
oraCMD.Parameters.Add(":p1", roleId);
try { oraDataReader = oraCMD.ExecuteReader(); }
catch { return(null); }
if (!oraDataReader.HasRows)
{
return(null);
}
while (oraDataReader.Read())
{
int fieldCount = oraDataReader.FieldCount;
for (int i = 0; i < fieldCount; i++)
{
string fieldName = oraDataReader.GetName(i).ToString();
role = InitRoleData(role, oraDataReader[fieldName], fieldName);
}
}
oraRasterConn.Close();
oraCMD.Dispose();
return(role);
}
public int Save()
{
int Row = 0;
DBCon con = new DBCon();
SqlCommand cmd = null;
try
{
string Quary = "";
if (this.id == 0)
{
Quary = "Insert into FeedbackObjective values (@Name,@ObjectiveType,@QuestionId); SELECT SCOPE_IDENTITY();";
}
else
{
Quary = "Update FeedbackObjective Set Name=@Name,ObjectiveType=@ObjectiveType,QuestionId=@QuestionId where id=@id";
}
cmd = new SqlCommand(Quary, con.Con);
cmd.Parameters.AddWithValue("@id", this.id);
cmd.Parameters.AddWithValue("@Name", this.Name);
cmd.Parameters.AddWithValue("@ObjectiveType", this.ObjectiveType);
cmd.Parameters.AddWithValue("@QuestionId", this.QuestionId);
cmd.Parameters.AddWithValue("@CreateDate", DateTime.Now);
if (this.id == 0)
{
Row = Convert.ToInt32(cmd.ExecuteScalar());
this.id = Row;
}
else
{
Row = cmd.ExecuteNonQuery();
//this.Categoryid = Row;
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
return(Row);
}
public bool ExisteDespesasNaoClassificadas(int codProjeto, DateTime?dataFim)
{
string filtroPer = string.Empty;
if (dataFim != null)
{
filtroPer = "AND DATAPAG <=@2";
}
string sql = @"SELECT 1 FROM DRDESPESA WHERE
( (CODETAPA IS NULL AND CODATIVIDADE IS NULL) OR (STATUS <> 'A') )
AND CODIGOPRJ =@1 " + filtroPer;
if (string.IsNullOrEmpty(filtroPer))
{
return(DBCon.QueryFind(sql, codProjeto));
}
else
{
return(DBCon.QueryFind(sql, codProjeto, dataFim));
}
}
public static int DeleteRole(Role role)
{
OracleConnection oraConn;
OracleCommand oraCMD;
oraConn = DBCon.OraConOpen();;
string sql;
sql = "DELETE FROM ROLE WHERE ROLEID=:p1";
oraCMD = new OracleCommand(sql, oraConn);
// oraCMD.Parameters.Add();
oraCMD.Parameters.Add(":p1", role.ROLEID);
int result = oraCMD.ExecuteNonQuery();
oraConn.Close();
oraCMD.Dispose();
return(result);
}
public bool SavePaymentOther()
{
if (DBCon.State == ConnectionState.Open)
{
dbClose();
}
dbOpen();
MySqlTransaction trans = DBCon.BeginTransaction();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = DBCon;
cmd.Transaction = trans;
//insert payment to database
cmd.CommandText = "INSERT INTO payment(idcashier, idassessment, ornumber, amount, paymenttype, payer, payment.division) VALUES ('" + CashierId + "'," + AssessmentId + ", '" + ORNumber + "', " + Amount + ", " + PaymentType + ", '" + Payer + "', " + Division + ")";
cmd.ExecuteNonQuery();
long lastpaymentId = cmd.LastInsertedId; //get last payment id inserted
//insert payment details
foreach (PaymentDetail fee in PaymentDetail)
{
cmd.CommandText = "INSERT INTO payment_detail(feecode,idpayment,amount) VALUES('" + fee.Code + "'," + lastpaymentId + "," + fee.Amount + ")";
cmd.ExecuteNonQuery();
}
trans.Commit();
dbClose();
return(true);
}
catch (Exception)
{
trans.Rollback();
dbClose();
return(false);
}
}
public ActionResult Login(string Email, string Password)
{
User Model = new User();
if (IsValid(Email, Password))
{
using (var db = new DBCon())
{
Model = db.Users.FirstOrDefault(u => u.Email == Email);
FormsAuthentication.SetAuthCookie(Email, false);
}
return(RedirectToAction("Index", "Home", new { id = Model.IDUser }));
}
else
{
ViewData["Msg"] = "Login data is incorrect.";
Email = null;
Password = null;
}
return(View());
}
/// <summary>
/// 用户授权
/// </summary>
/// <param name="userId"></param>
/// <param name="roleId"></param>
/// <returns></returns>
public static int AuthorizationUserInfo(string userId, string roleId)
{
OracleConnection oraConn;
OracleCommand oraCMD;
oraConn = DBCon.OraConOpen();;
string sql;
sql = "UPDATE USERINFO SET ROLEID=:p1 WHERE USERID =:p2)";
oraCMD = new OracleCommand(sql, oraConn);
oraCMD.Parameters.Add(":p1", roleId);
oraCMD.Parameters.Add(":p2", userId);
int result = oraCMD.ExecuteNonQuery();
oraConn.Close();
oraCMD.Dispose();
return(result);
}
public int Save()
{
int Row = 0;
DBCon con = new DBCon();
SqlCommand cmd = null;
try
{
string Quary = "";
if (this.Id == 0)
{
Quary = "Insert into FeedbackForm values (@Name,@OrgId,@Status,@CreateDate); SELECT SCOPE_IDENTITY();";
}
else
{
Quary = "Update FeedbackForm Set Name=@Name,OrgId=@OrgId,Status=@Status where Id=@Id";
}
cmd = new SqlCommand(Quary, con.Con);
cmd.Parameters.AddWithValue("@Id", this.Id);
cmd.Parameters.AddWithValue("@Name", this.Name);
cmd.Parameters.AddWithValue("@OrgId", this.OrgId);
cmd.Parameters.AddWithValue("@Status", this.Status);
if (this.Id == 0)
{
cmd.Parameters.AddWithValue("@CreateDate", DateTime.Now);
Row = Convert.ToInt32(cmd.ExecuteScalar());
this.Id = Row;
}
else
{
Row = cmd.ExecuteNonQuery();
//this.CategoryId = Row;
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
return(Row);
}
public static List <Accounts> GetAllACDetails(int OrgId)
{
DBCon con = new DBCon();
SqlCommand cmd = null;
SqlDataReader SDR = null;
List <Accounts> ACList = new List <Accounts>();
try
{
string Quary = "Select * from ACAccount where OrgId=" + OrgId;
cmd = new SqlCommand(Quary, con.Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
Accounts OBJAC = new Accounts();
OBJAC.AID = SDR.GetInt32(0);
OBJAC.Date = SDR.GetDateTime(1);
OBJAC.DRAmount = SDR.GetDouble(2);
OBJAC.CRAmount = SDR.GetDouble(3);
OBJAC.Narration = SDR.GetString(4);
OBJAC.Balance = SDR.GetDouble(5);
OBJAC.CRGroupId = SDR.GetInt32(6);
OBJAC.AOrgId = SDR.GetInt32(7);
OBJAC.DRGroupId = SDR.GetInt32(8);
OBJAC.CRLedgerId = SDR.GetInt32(9);
OBJAC.DRLedgerId = SDR.GetInt32(10);
OBJAC.EntryNo = SDR.GetInt32(11);
OBJAC.EntryType = SDR.GetString(12);
OBJAC.ReceiptID = SDR.GetInt32(13);
ACList.Add(OBJAC);
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
return(ACList);
}
public static List <FeedbkItem> GetAll(int Orgid, DateTime Fdate, DateTime Tdate)
{
DBCon dBCon = new DBCon();
SqlCommand cmd = null;
SqlDataReader SDR = null;
List <FeedbkItem> listfeedbk = new List <FeedbkItem>();
try
{
string Quary = "Select * From FeedBkItem where OrgId=" + Orgid;
if (Fdate != null && Tdate != null)
{
Quary += "and CreateOn between '" + Fdate.ToString("MM/dd/yyyy") + "' and '" + Tdate.ToString("MM/dd/yyyy HH:mm:ss") + "";
}
cmd = new SqlCommand(Quary, dBCon.Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
FeedbkItem OBJfeedbk = new FeedbkItem();
OBJfeedbk.ItemID = SDR.GetInt32(0);
OBJfeedbk.Rating = SDR.GetInt32(1);
//OBJfeedbk.Comment = SDR.GetString(2);
OBJfeedbk.FeedbkFormID = SDR.GetInt32(3);
OBJfeedbk.FeedBkID = SDR.GetInt32(4);
OBJfeedbk.CreateOn = SDR.GetDateTime(5);
OBJfeedbk.CID = SDR.GetInt32(6);
OBJfeedbk.OrgId = SDR.GetInt32(7);
OBJfeedbk.LikeCnt = SDR.GetInt32(8);
OBJfeedbk.DislikeCnt = SDR.GetInt32(9);
OBJfeedbk.OkCnt = SDR.GetInt32(10);
listfeedbk.Add(OBJfeedbk);
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose();; dBCon.Con.Close(); }
return(listfeedbk);
}
public int save()
{
int R = 0;
DBCon con = new DBCon();
SqlCommand cmd = null;
try
{
string Quary = "";
if (FeedBkId == 0)
{
Quary = "Insert into FeedBk values(@OrgId,@OrderId,@FeedbkFormId,@CreateOn);select SCOPE_IDENTITY();";
cmd = new SqlCommand(Quary, con.Con);
}
else
{
Quary = "Update FeedBk Set OrgId=@OrgId,OrderId=@OrderId,FeedbkFormId=@FeedbkFormId where FeedBkId=@FeedBkId";
cmd = new SqlCommand(Quary, con.Con);
cmd.Parameters.AddWithValue("@FeedBkId", this.FeedBkId);
}
cmd.Parameters.AddWithValue("@OrgId", this.OrgId);
cmd.Parameters.AddWithValue("@OrderId", this.OrderId);
cmd.Parameters.AddWithValue("@FeedbkFormId", this.FeedbkFormId);
if (this.FeedBkId == 0)
{
cmd.Parameters.AddWithValue("@CreateOn", DateTime.Now);
R = Convert.ToInt32(cmd.ExecuteScalar());
this.FeedBkId = R;
}
else
{
R = cmd.ExecuteNonQuery();
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
return(R);
}
public System.Collections.Generic.List <OrderItem> GetAll()
{
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlDataReader SDR = null;
System.Collections.Generic.List <OrderItem> ListTmp = new System.Collections.Generic.List <OrderItem>();
DBCon Obj = new DBCon();
try
{
string Query = "SELECT * FROM ORDERITEM WHERE Deleted=0 ORDER BY OIID DESC";
cmd = new System.Data.SqlClient.SqlCommand(Query, Obj.Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
OrderItem ObjTmp = new OrderItem();
ObjTmp.OIID = SDR.GetInt64(0);
ObjTmp.FID = SDR.GetInt64(1);
ObjTmp.Price = SDR.GetDouble(2);
ObjTmp.Count = SDR.GetInt32(3);
ObjTmp.Qty = SDR.GetString(4);
ObjTmp.OID = SDR.GetInt64(5);
ObjTmp.MessID = SDR.IsDBNull(7) ? 0 : SDR.GetInt64(7);
ObjTmp.Status = SDR.IsDBNull(8) ? 0 : SDR.GetInt32(8);
ObjTmp.OrderDate = SDR.IsDBNull(9) ? System.DateTime.Now : SDR.GetDateTime(9);
ObjTmp.TifinRackIds = SDR.IsDBNull(10) ? "" : SDR.GetString(10);
ObjTmp.UpdatedBy = SDR.IsDBNull(11) ? 0 : SDR.GetInt32(11);
ObjTmp.UpdationDate = SDR.IsDBNull(12) ? System.DateTime.Now : SDR.GetDateTime(12);
ObjTmp.TifinID = SDR.IsDBNull(13) ? 0 : SDR.GetInt64(13);
ObjTmp.ItemCollectBy = SDR.IsDBNull(14) ? 0 : SDR.GetInt32(14);
ObjTmp.ItemAssembleBy = SDR.IsDBNull(15) ? 0 : SDR.GetInt32(15);
ListTmp.Add(ObjTmp);
}
}
catch (System.Exception e) { e.ToString(); }
finally { cmd.Dispose(); SDR.Close(); Obj.Con.Close(); Obj.Con.Dispose(); Obj.Con = null; }
return(ListTmp);
}
public static List <INTGSTBL> GetAll()
{
DBCon con = new DBCon();
SqlCommand cmd = null;
SqlDataReader SDR = null;
List <INTGSTBL> listintcat = new List <INTGSTBL>();
try
{
string Quary = "Select * from INTGSTBL";
cmd = new SqlCommand(Quary, con.Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
INTGSTBL OBJINT = new INTGSTBL();
OBJINT.GSID = SDR.GetInt32(0);
OBJINT.CatID = SDR.GetInt32(1);
OBJINT.Name = SDR.GetString(2);
OBJINT.Typeid = SDR.GetInt32(3);
OBJINT.UnitID = SDR.GetInt32(4);
OBJINT.Qty = SDR.GetDouble(5);
OBJINT.PurchasedPrize = SDR.GetDouble(6);
OBJINT.PurchasedTax = SDR.GetDouble(7);
OBJINT.SalePrize = SDR.GetDouble(8);
OBJINT.SaleTax = SDR.GetDouble(9);
OBJINT.ISSaleable = SDR.GetBoolean(10);
OBJINT.ISdirectlyPurchased = SDR.GetBoolean(11);
OBJINT.ISProcessed = SDR.GetBoolean(12);
OBJINT.ServicePrize = SDR.GetDouble(13);
OBJINT.ServiceTax = SDR.GetDouble(14);
listintcat.Add(OBJINT);
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
return(listintcat);
}
public int Save()
{
int Row = 0;
DBCon con = new DBCon();
SqlCommand cmd = null;
try
{
string Quary = "";
if (this.SubItemID == 0)
{
Quary = "Insert Into INTItems Values (@ItemID,@Qty,@UnitID,@GSID);SELECT SCOPE_IDENTITY();";
}
else
{
Quary = "Update INTItems Set ItemID=@ItemID,Qty=@Qty,UnitID=@UnitID,GSID=@GSID where SubItemID=@SubItemID";
}
cmd = new SqlCommand(Quary, con.Con);
cmd.Parameters.AddWithValue("@SubItemID", this.SubItemID);
cmd.Parameters.AddWithValue("@ItemID", this.ItemID);
cmd.Parameters.AddWithValue("@Qty", this.IQty);
cmd.Parameters.AddWithValue("@UnitID", this.IUnitID);
cmd.Parameters.AddWithValue("@GSID", this.IParentId);
if (this.SubItemID == 0)
{
Row = Convert.ToInt32(cmd.ExecuteScalar());
this.SubItemID = Row;
}
else
{
Row = cmd.ExecuteNonQuery();
//this.CategoryID = Row;
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
return(Row);
}
public string GetNextCodigoAtividade(int codigoPrj, string codEtapa)
{
string result = string.Empty;
object lastCod = DBCon.QueryValue(@"SELECT MAX(CODATIVIDADE) FROM DRATIVIDADE WHERE CODIGOPRJ=@1 AND
CODETAPA =@2", codigoPrj, codEtapa);
if (lastCod != null)
{
string[] quebra = lastCod.ToString().Split('.');
if (quebra.Length == 0)
{
try
{
lastCod = Convert.ToInt32(quebra) + 1;
}
catch
{
result = "1";
}
}
else
{
try
{
result = Convert.ToString(Convert.ToInt32(quebra[quebra.Length - 1]) + 1);
}
catch
{
result = "1";
}
}
return(string.Concat(codEtapa, ".", result));
}
else
{
return(string.Concat(codEtapa, ".1"));
}
}
public int Save()
{
int Row = 0;
DBCon con = new DBCon();
SqlCommand cmd = null;
try
{
string Quary = "";
if (this.ID == 0)
{
Quary = "Insert Into ACGroup Values (@Name,@PGID,@Type);SELECT SCOPE_IDENTITY();";
}
else
{
Quary = "Update ACGroup Set Name=@Name,PGID=@PGID,Type=@Type where ID=@ID";
}
cmd = new SqlCommand(Quary, con.Con);
cmd.Parameters.AddWithValue("@ID", this.ID);
cmd.Parameters.AddWithValue("@Name", this.Name);
cmd.Parameters.AddWithValue("@PGID", this.PGID);
cmd.Parameters.AddWithValue("@Type", this.Type);
if (this.ID == 0)
{
Row = Convert.ToInt32(cmd.ExecuteScalar());
this.ID = Row;
}
else
{
Row = cmd.ExecuteNonQuery();
//this.CategoryID = Row;
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
GetAll();
return(Row);
}
public static bool Validate(string emailAddress)
{
SqlDataReader rd;
//getting the database connectivity
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
//set command type as stored procedure
SqlCommand cmd = new SqlCommand("sp_validate_VolunteerApplication", con);
cmd.CommandType = CommandType.StoredProcedure;
// Passing parameters
cmd.Parameters.AddWithValue("EmailAddress", emailAddress);
con.Open();
//get row count (if no records count is 0)
int rowCount = (int)cmd.ExecuteScalar();
con.Close();
if (rowCount > 0)
{
return(false);
}
return(true);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["Username"] == null)
{
Response.Redirect("redirectionpage.aspx"); // Help From http://stackoverflow.com/questions/12219246/submit-show-results-delay-3-seconds-and-redirect
}
;
mainPageUsername.Text = Session["Username"].ToString();
string sql = string.Format("SELECT * FROM Reminders WHERE userID={0}", Session["ID"]);
var db = new DBCon();
var reader = db.Execute(sql);
var sb = new StringBuilder();
while (reader.Read())
{
sb.AppendLine("<tr>");
sb.AppendLine(string.Format("<td><a href='viewreminder.aspx?id={0}'>{1}</a></td>", reader["ID"], reader["name"]));
sb.AppendLine("</tr>");
}
lbl_table.Text = sb.ToString();
db.Close();
}
public ActionResult Index(int?id)
{
if (id != null && this.User.Identity.IsAuthenticated)
{
User user = new User();
using (var db = new DBCon())
{
user = db.Users.Find(id);
}
if (user == null)
{
return(HttpNotFound());
}
else
{
return(View(user));
}
}
else
{
return(View());
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["Username"] == null)
{
Response.Redirect("redirectionpage.aspx"); // Help From http://stackoverflow.com/questions/12219246/submit-show-results-delay-3-seconds-and-redirect
};
mainPageUsername.Text = Session["Username"].ToString();
string sql = string.Format("SELECT * FROM Alarms WHERE userID={0}", Session["ID"]);
var db = new DBCon();
var reader = db.Execute(sql);
var sb = new StringBuilder();
while (reader.Read())
{
sb.AppendLine("<tr>");
var time = Convert.ToDateTime(reader["time"].ToString());
sb.AppendLine(string.Format("<td class=\"time\">{0}</td>", time.ToLongTimeString()));
sb.AppendLine("</tr>");
}
lbl_table.Text = sb.ToString();
db.Close();
}
//Funkcija za vraćanje svih Poslovnica iz baze za combobox
static public List <Poslovnica> DohvatiPoslovniceZaComboBox()
{
List <Poslovnica> poslovnice = new List <Poslovnica>();
DBCon baza = new DBCon();
SqlCommand command = new SqlCommand("SELECT ID_Poslovnica,Naziv FROM Poslovnica");
DataTable dt = baza.DohvatiDT(command);
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
foreach (DataRow row in dt.Rows)
{
Poslovnica p = new Poslovnica();
p.PoslovnicaId = (int)row["ID_Poslovnica"];
p.Naziv = (string)row["Naziv"];
poslovnice.Add(p);
}
return(poslovnice);
}
}
private bool InitDatabase()
{
CloseDatabase();
bool result = false;
try
{
_dbcon = new DBConComSqlite(DatabaseFilename);
object o = _dbcon.ExecuteScalar("SELECT name FROM sqlite_master WHERE type='table' AND name='images'");
if (o == null || o.GetType() == typeof(DBNull))
{
_dbcon.ExecuteNonQuery("create table 'images' (org_url text, gccode text, local_file text)");
_dbcon.ExecuteNonQuery("create index idx_images on images (org_url)");
_dbcon.ExecuteNonQuery("create index idx_gccodes on images (gcode)");
}
result = true;
}
catch (Exception e)
{
Core.ApplicationData.Instance.Logger.AddLog(this, e);
}
return(result);
}
public object[] GetProjetoDefault()
{
object prj =
DBCon.QueryValue("SELECT LogaCodigoPrj FROM DRUsuario WHERE IDUsuario =@1", DRContex.Sistema.User.IdUsuario);
if (prj == DBNull.Value || prj == null)
{
return(null);
}
else
{
DataTable tbl =
DBCon.QuerySelect("Tbl", "SELECT CodigoPrj,Nome,DATALASTBCK FROM DRProjeto WHERE CodigoPrj=@1", prj);
object[] result = new object[3] {
tbl.Rows[0]["CodigoPrj"],
tbl.Rows[0]["Nome"],
tbl.Rows[0]["DATALASTBCK"]
};
return(result);
}
}
public static List <TypeObject> GetAnimalsByType(bool isRehomed)
{
List <TypeObject> typeList = new List <TypeObject>();;
SqlDataReader rd;
using (SqlConnection con = new SqlConnection(DBCon.GetDBCon()))
{
SqlCommand cmd = new SqlCommand("sp_get_AnimalsbyType", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@rehomed", isRehomed);
con.Open();
rd = cmd.ExecuteReader();
while (rd.Read())
{
TypeObject type = new TypeObject();
type.Type = rd["Type"].ToString();
type.Count = Convert.ToInt32(rd["Count"]);
typeList.Add(type);
}
rd.Close();
}
return(typeList);
}
protected override void DoRecuperaRegistroDialog(System.Data.DataSet dataSet, object[] pk)
{
string sql = @"SELECT * FROM DRInvestidores (NOLOCK) WHERE CodInvestidor =@1";
DBCon.Fill(dataSet.Tables["DRInvestidores"], sql, pk);
string sql_ = @"SELECT DROBRAINVESTIDOR.*,DRCOTAS.DESCRICAO AS COTA FROM DROBRAINVESTIDOR
LEFT JOIN DRCOTAS ON
DRCOTAS.CODIGOPRJ = DROBRAINVESTIDOR.CODIGOPRJ AND
DRCOTAS.IDCOTA = DROBRAINVESTIDOR.IDCOTA
WHERE CODINVESTIDOR=@1";
DBCon.Fill(dataSet.Tables["DROBRAINVESTIDOR"], sql_, pk);
//somente para update
dataSet.Tables["DROBRAINVESTIDOR"].ExtendedProperties["@SQL"] =
@"SELECT * FROM DROBRAINVESTIDOR WHERE CODINVESTIDOR=@1";
DRProps.SetInfoColuna(dataSet.Tables["DROBRAINVESTIDOR"], "IDCOTA", ColProps.Visible, false);
DRProps.SetInfoColuna(dataSet.Tables["DROBRAINVESTIDOR"], "COTA", ColProps.Caption, "Cota");
}
public int Save()
{
int Row = 0;
DBCon Con = new DBCon();
SqlCommand cmd = null;
try
{
string Quary = "";
if (this.ID == 0)
{
Quary = "Insert Into VideoMark values(@VideoID,@CID,@CreateDate); SELECT SCOPE_IDENTITY();";
}
else
{
Quary = "Update VideoMark Set VideoID=@VideoID,CID=@CID,CreateDate=@CreateDate Where ID=@ID";
}
cmd = new SqlCommand(Quary, Con.Con);
cmd.Parameters.AddWithValue("@ID", this.ID);
cmd.Parameters.AddWithValue("@VideoID", this.VideoID);
cmd.Parameters.AddWithValue("@CID", this.CID);
cmd.Parameters.AddWithValue("@CreateDate", DateTime.Now);
if (this.ID == 0)
{
Row = Convert.ToInt32(cmd.ExecuteScalar());
this.ID = Row;
}
else
{
Row = cmd.ExecuteNonQuery();
//this.CategoryID = Row;
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); Con.Con.Close(); }
return(Row);
}
public static OfferObj GetAll(int CBID)
{
DBCon dBCon = new DBCon();
SqlCommand cmd = null;
SqlDataReader SDR = null;
OfferObj offerObj = new OfferObj();
offerObj.CBID = CBID;
List <ItemOffer> ListTmp = new List <ItemOffer>();
string Query = "SELECT ItemOfferId,ItemId,Min,Max,CashBkId,dbo.GetItemName(ItemId),dbo.GetItemPrice(ItemId) FROM ItemOffer where CashBkId=" + CBID.ToString() + " and IsDeleted=0";
try
{
cmd = new SqlCommand(Query, dBCon.Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
int index = 0;
ItemOffer ObjTmp = new ItemOffer();
ObjTmp.ItemOfferId = SDR.GetInt32(index++);
ObjTmp.ItemId = SDR.GetInt32(index++);
ObjTmp.Min = SDR.GetInt32(index++);
ObjTmp.Max = SDR.GetInt32(index++);
ObjTmp.CashBkId = SDR.GetInt32(index++);
ObjTmp.ItemName = SDR.GetString(index++);
ObjTmp.TotalItemPrice = SDR.GetDouble(index++);
offerObj.Min = ObjTmp.Min;
offerObj.Max = ObjTmp.Max;
ListTmp.Add(ObjTmp);
}
offerObj.itemOffers = ListTmp;
}
catch (Exception e) { e.ToString(); }
finally { dBCon.Close(); }
return(offerObj);
}
protected void alarmSubmitButton_Click(object sender, EventArgs e)
{
if (newAlarmTime.Text != "")
{
var con = new DBCon();
string sql = string.Format("INSERT INTO Alarms ( [time],[userID] ) VALUES ('{0}', {1})", newAlarmTime.Text, Session["ID"]);
var reader = con.Execute(sql);
con.Close();
Response.Redirect("alarmSuccess.aspx");
}
}
protected void reminderSubmitButton_Click(object sender, EventArgs e)
{
int i = 0;
if (newReminderTime.Text != "")
{
DateTime t1 = DateTime.Now; //http://forums.asp.net/t/1025431.aspx?How+to+compare+Time+in+C+
DateTime t2 = Convert.ToDateTime(string.Format("{0} {1}", newReminderDate.Text.ToString(), newReminderTime.Text.ToString())); //("{ 0:0000}", value)
i = DateTime.Compare(t1, t2);
}
if (newReminderName.Text != ""&& i < 0 && newReminderTime.Text != "")
{
var con = new DBCon();
string sql = string.Format("INSERT INTO Reminders ( [name], [reminder],[time],[userID] ) VALUES ( '{0}', '{1}', '{2} {3}',{4})", newReminderName.Text, newReminder.Text, newReminderDate.Text, newReminderTime.Text, Session["ID"]);
var reader = con.Execute(sql);
con.Close();
Response.Redirect("reminderSuccess.aspx");
}
else if (newReminderName.Text == "")
{
errorLabelReminder.Text = "*Enter Name!";
}
else if (i >= 0)
{
errorLabelReminder.Text = "*Enter correct Time!";
}
else
errorLabelReminder.Text = "*Enter correct Time!";
}
private void Bind_grdCourses()
{
////populate courses not added to profile
//myCon.ConOpen();
//queryString = "Select ComCod, CourseNo, CourseTitle from tblcourses where ComCod not in ( select ComCod from tblcourseteachermap where TeacherID = '" + Current_User_ID + "')";
//queryCommand = myCon.MakeSqlCommand(queryString);
//sqlDA = new SqlDataAdapter(queryCommand);
//System.Data.DataSet myDS = new System.Data.DataSet();
//sqlDA.Fill(myDS);
////myReader = myCon.ExecuteReader(queryString);
////grdCourses.DataSource = myReader;
//grdCourses.DataSource = myDS;
//grdCourses.DataBind();
//btnAddCourses.Visible = (grdCourses.Rows.Count != 0);
//myDS.Dispose();
//myCon.ConClose();
String queryString;
queryString = "Select ComCod, CourseNo, CourseTitle from tblcourses ";
DBCon myCon = new DBCon();
myCon.ConOpen();
if (ddlDepartments.SelectedItem.Text != "All Departments" || ddlSem.SelectedItem.Text != "All Semesters" || txtSearchString.Text != "")
{
queryString += "where ";
if (ddlDepartments.SelectedItem.Text != "All Departments")
{
queryString = queryString + " ComCod in (Select ComCod from tblDepartment where Department = '" + ddlDepartments.SelectedItem.Text + "'";
if (ddlSem.SelectedItem.Text != "All Semesters")
{
queryString += " and Semester = '" + ddlSem.SelectedItem.Text + "'";
}
queryString += ") ";
}
else
{
if (ddlSem.SelectedItem.Text != "All Semesters")
{
queryString = queryString + " ComCod in (Select ComCod from tblDepartment where Semester = '" + ddlSem.SelectedItem.Text + "') ";
}
}
if (txtSearchString.Text != "")
{
if (ddlDepartments.SelectedItem.Text != "All Departments" || ddlSem.SelectedItem.Text != "All Semesters")
{
queryString += " and ";
}
queryString += ddlSearchItem.SelectedItem.Value + " like '%" + txtSearchString.Text + "%'";
}
}
queryCommand = myCon.MakeSqlCommand(queryString);
sqlDA = new SqlDataAdapter(queryCommand);
System.Data.DataSet myDS = new System.Data.DataSet();
sqlDA.Fill(myDS);
grdCourses.DataSource = myDS;
grdCourses.DataBind();
btnAddCourses.Visible = (grdCourses.Rows.Count != 0);
myCon.ConClose();
}
protected void deleteAccButton_Click(object sender, EventArgs e)
{
if (password.Text == Session["Password"].ToString())
{
var con = new DBCon();
string sql = string.Format("DELETE FROM Notes WHERE userID={0}", Session["ID"]);
var reader = con.Execute(sql);
con.Close();
string sql2 = string.Format("DELETE FROM Reminders WHERE userID={0}", Session["ID"]);
reader = con.Execute(sql2);
con.Close();
string sql3 = string.Format("DELETE FROM Alarms WHERE userID={0}", Session["ID"]);
reader = con.Execute(sql3);
con.Close();
string sql4 = string.Format("DELETE FROM users WHERE ID={0}", Session["ID"]);
reader = con.Execute(sql4);
con.Close();
Response.Redirect("Default.aspx");
}
else
errorDeleteAcc.Text = "Invalid Password!";
}
protected void submit_Click(object sender, EventArgs e)
{
DateTime t = System.DateTime.Now;
string time = t.ToString();
if (noteName.Text != "")
{
int id = Convert.ToInt32(Request.QueryString["id"]);
var con = new DBCon();
string sql = string.Format("UPDATE Notes SET noteTitle = '{0}' WHERE ID = {1}", noteName.Text, id);
var reader = con.Execute(sql);
con.Close();
var con2 = new DBCon();
string sql2 = string.Format("UPDATE Notes SET note2 = '{0}' WHERE ID = {1}", noteBody.Text, id);
var reader2= con2.Execute(sql2);
con2.Close();
var con3 = new DBCon();
string sql3 = string.Format("UPDATE Notes SET noteTime = '{0}' WHERE ID = {1}", time, id);
var reader3 = con3.Execute(sql3);
con3.Close();
errorLabelNotes.Text = sql2;
Response.Redirect("Success.aspx");
}
else
errorLabelNotes.Text = "*Enter Title!";
}
protected void delete_Click(object sender, EventArgs e)
{
int id = Convert.ToInt32(Request.QueryString["id"]);
var con = new DBCon();
string sql = string.Format("DELETE FROM Notes WHERE ID={0}", id );
var reader = con.Execute(sql);
con.Close();
Response.Redirect("success.aspx");
}
