public void createProjectTable(string tablename)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
try
{
String quary = "CREATE TABLE " + tablename +
"("
+ "username varchar(255),"
+ "name varchar(255),"
+ "assaignedwork varchar(255),"
+ "duedate varchar(255))";
SqlCommand cmd = new SqlCommand(quary, con);
cmd.ExecuteNonQuery();
MessageBox.Show("Table Created");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
public string[,] showResult(string tablename)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
String[,] info = new String[100,5];
try
{
String quary = "select * from "+tablename+"";
SqlCommand cmd = new SqlCommand(quary, con);
using (SqlDataReader reader = cmd.ExecuteReader())
{
for (int i = 0; reader.Read();i++ )
{
info[i,0] = reader["username"].ToString();
info[i,1] = reader["name"].ToString();
info[i,2] = reader["assaignedwork"].ToString();
info[i,3] = reader["duedate"].ToString();
info[i,4] = reader["status"].ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
return info;
}
public string[] showingData(string username,int flag)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
string query = "Select * from tbl_profile where name like '%" + username + "%'";
SqlCommand com = new SqlCommand(query, con);
SqlDataReader reader = com.ExecuteReader();
for (int i = 1; reader.Read(); i++)
{
name[i] = reader[1].ToString();
uname[i] = reader[0].ToString();
}
reader.Close();
con.Close();
if (flag == 1)
{
return name;
}
else
{
return uname;
}
}
public string[] projectInfo(string projectName)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
String[] info=new String[8];
try
{
String quary = "select * from tbl_project where project='" + projectName + "'";
SqlCommand cmd = new SqlCommand(quary, con);
using (SqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
for (int i = 0; i < 8; i++)
{
info[i] = reader[i].ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
return info;
}
//==============Insert========================
public int InsertTyLeTinh(Entities.TyLeTinh input)
{
int bg = 0;
try
{
con = new Connection();
cn = con.openConnection();
cmd = new SqlCommand("exec sp_TyLeTinh @ThaoTac,@MaTyLeTinh,@SoTien,@NgayNhap,@GhiChu,@Deleted", cn);
cmd.Parameters.Add("ThaoTac", SqlDbType.NVarChar).Value = "insert";
//set
cmd.Parameters.Add("MaTyLeTinh", SqlDbType.VarChar).Value = input.MaTyLeTinh;
cmd.Parameters.Add("SoTien", SqlDbType.Float).Value = input.SoTien;
cmd.Parameters.Add("NgayNhap", SqlDbType.DateTime).Value = input.NgayNhap;
cmd.Parameters.Add("GhiChu", SqlDbType.NVarChar).Value = input.GhiChu;
cmd.Parameters.Add("Deleted", SqlDbType.Bit).Value = input.Deleted;
bg = cmd.ExecuteNonQuery();
}
catch (Exception)
{
bg = 0;
}
finally
{
cmd.Connection.Dispose();
cn.Close();
con.closeConnection();
}
if (bg == 0)
return bg;
return bg;
}
/// <summary>
/// Insert
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public int Insert(Entities.KhuyenMaiSoLuong item)
{
int rt = 0;
try
{
conn = new Connection();
SqlConnection cn = conn.openConnection();
string sql = "exec sp_InsertKMSL @Id,@MaHangHoa,@TenHangHoa,@NgayBatDau,@NgayKetThuc,@SoLuong,@GiaBanBuon,@GiaBanLe";
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add("Id", SqlDbType.Int).Value = 0;
cmd.Parameters.Add("MaHangHoa", SqlDbType.VarChar, 50).Value = item.MaHangHoa;
cmd.Parameters.Add("TenHangHoa", SqlDbType.NVarChar, 200).Value = item.TenHangHoa;
cmd.Parameters.Add("NgayBatDau", SqlDbType.DateTime).Value = item.NgayBatDau;
cmd.Parameters.Add("NgayKetThuc", SqlDbType.DateTime).Value = item.NgayKetThuc;
cmd.Parameters.Add("SoLuong", SqlDbType.Float).Value = item.SoLuong;
cmd.Parameters.Add("GiaBanBuon", SqlDbType.Float).Value = item.GiaBanBuon;
cmd.Parameters.Add("GiaBanLe", SqlDbType.Float).Value = item.GiaBanLe;
rt = cmd.ExecuteNonQuery();
}
catch
{
rt = 0;
}
return rt;
}
public KhuyenMaiSoLuong()
{
conn = null;
cmd = null;
dr = null;
kmsl = null;
cn = null;
}
public TyLeTinh()
{
con = null;
cmd = null;
arr = null;
dr = null;
cn = null;
}
public KhachHang_k()
{
con = null;
cmd = null;
arr = null;
dr = null;
cn = null;
}
public BCNhapHangTheoNhomHang()
{
con = null;
cmd = null;
arr = null;
dr = null;
cn = null;
}
public DiemThuongKhachHang()
{
con = null;
cmd = null;
arr = null;
dr = null;
cn = null;
}
public ChiTietTheGiamGia()
{
con = null;
cmd = null;
arr = null;
dr = null;
cn = null;
}
public GiaVon()
{
con = null;
cmd = null;
arr = null;
dr = null;
cn = null;
}
public string[] projectDisplay(string username)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
int counter=0;
String[] info=new String[10];
try
{
//1st Person
String quary = "select * from tbl_project where people1username='******'";
SqlCommand cmd = new SqlCommand(quary, con);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
info[counter++] = reader["project"].ToString();
}
}
//2nd person
quary = "select * from tbl_project where people2username='******'";
cmd = new SqlCommand(quary, con);
using (SqlDataReader reader1 = cmd.ExecuteReader())
{
while (reader1.Read())
{
info[counter++] = reader1["project"].ToString();
}
}
//3rd person
quary = "select * from tbl_project where people3username='******'";
cmd = new SqlCommand(quary, con);
using (SqlDataReader reader2 = cmd.ExecuteReader())
{
while (reader2.Read())
{
info[counter++] = reader2["project"].ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
return info;
}
//==============Select========================
/// <summary>
/// SelectTheoDieuKien_GiaVon:
/// - Select_GIAVON
/// - Insert_GIAVON
/// - Update_TheoMaKhoVaMaHangHoa_GIAVON
/// - Delete_TheoMaKhoVaMaHangHoa_GIAVON
/// </summary>
/// <param name="thaotac"></param>
/// <param name="set"></param>
/// <param name="filter"></param>
/// <returns></returns>
public Entities.GiaVon[] SelectTheoDieuKien_GiaVon(string thaotac, Entities.GiaVon set, Entities.GiaVon filter)
{
Entities.GiaVon[] list = null;
try
{
con = new Connection();
cn = con.openConnection();
cmd = new SqlCommand("exec sp_GiaVon @ThaoTac,@MaKho,@MaHangHoa,@SoLuong,@Gia,@MaKhok,@MaHangHoak,@SoLuongk,@Giak", cn);
cmd.Parameters.Add("ThaoTac", SqlDbType.NVarChar).Value = thaotac;
//set
cmd.Parameters.Add("MaKho", SqlDbType.NVarChar).Value = set.MaKho;
cmd.Parameters.Add("MaHangHoa", SqlDbType.NVarChar).Value = set.MaHangHoa;
cmd.Parameters.Add("SoLuong", SqlDbType.NVarChar).Value = set.SoLuong;
cmd.Parameters.Add("Gia", SqlDbType.NVarChar).Value = set.Gia;
//filter
cmd.Parameters.Add("MaKhok", SqlDbType.NVarChar).Value = filter.MaKho;
cmd.Parameters.Add("MaHangHoak", SqlDbType.NVarChar).Value = filter.MaHangHoa;
cmd.Parameters.Add("SoLuongk", SqlDbType.NVarChar).Value = filter.SoLuong;
cmd.Parameters.Add("Giak", SqlDbType.NVarChar).Value = filter.Gia;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
arr = new ArrayList();
while (dr.Read())
{
int sl;
float g;
Entities.GiaVon row = new Entities.GiaVon
{
MaKho = dr["MaKho"].ToString(),
MaHangHoa = dr["MaHangHoa"].ToString(),
SoLuong = int.TryParse(dr["SoLuong"].ToString(), out sl) ? sl : 0,
Gia = float.TryParse(dr["Gia"].ToString(), out g) ? g : 0
};
arr.Add(row);
}
int n = arr.Count;
if (n == 0) list = null;
list = new Entities.GiaVon[n];
for (int i = 0; i < n; i++)
{
list[i] = (Entities.GiaVon)arr[i];
}
}
catch (Exception ex)
{ string s = ex.Message.ToString(); list = null; }
finally
{
cmd.Connection.Dispose();
cn.Close();
con.closeConnection();
}
return list;
}
//==============Select========================
public Entities.DiemThuongKhachHang[] SelectDiemThuongKhachHang(Entities.DiemThuongKhachHang input)
{
Entities.DiemThuongKhachHang[] list = null;
try
{
con = new Connection();
cn = con.openConnection();
cmd = new SqlCommand("exec sp_DiemThuongKhachHang @ThaoTac,@MaDiemThuongKhachHang,@MaKhachHang,@TenKhachHang,@TongDiem,@DiemDaDung,@DiemConLai,@GhiChu,@Deleted", cn);
cmd.Parameters.Add("ThaoTac", SqlDbType.NVarChar).Value = input.ThaoTac;
//set
cmd.Parameters.Add("MaDiemThuongKhachHang", SqlDbType.VarChar).Value = input.MaDiemThuongKhachHang;
cmd.Parameters.Add("MaKhachHang", SqlDbType.VarChar).Value = input.MaKhachHang;
cmd.Parameters.Add("TenKhachHang", SqlDbType.NVarChar).Value = input.TenKhachHang;
cmd.Parameters.Add("TongDiem", SqlDbType.Int).Value = input.TongDiem;
cmd.Parameters.Add("DiemDaDung", SqlDbType.Int).Value = input.DiemDaDung;
cmd.Parameters.Add("DiemConLai", SqlDbType.Int).Value = input.DiemConLai;
cmd.Parameters.Add("GhiChu", SqlDbType.NVarChar).Value = input.GhiChu;
cmd.Parameters.Add("Deleted", SqlDbType.Bit).Value = input.Deleted;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
arr = new ArrayList();
while (dr.Read())
{
Entities.DiemThuongKhachHang row = new Entities.DiemThuongKhachHang();
row.MaDiemThuongKhachHang = dr["MaDiemThuongKhachHang"].ToString();
row.MaKhachHang = dr["MaKhachHang"].ToString();
row.TenKhachHang = dr["TenKhachHang"].ToString();
row.TongDiem = int.Parse(dr["TongDiem"].ToString());
row.DiemDaDung = int.Parse(dr["DiemDaDung"].ToString());
row.DiemConLai = int.Parse(dr["DiemConLai"].ToString());
row.GhiChu = dr["GhiChu"].ToString();
row.Deleted = bool.Parse(dr["Deleted"].ToString());
arr.Add(row);
}
int n = arr.Count;
if (n == 0) list = null;
list = new Entities.DiemThuongKhachHang[n];
for (int i = 0; i < n; i++)
{
list[i] = (Entities.DiemThuongKhachHang)arr[i];
}
}
catch (Exception ex)
{ string s = ex.Message.ToString(); list = null; }
finally
{
cmd.Connection.Dispose();
cn.Close();
con.closeConnection();
}
return list;
}
//==============Select========================
public Entities.ChiTietTheGiamGia[] SelectChiTietTheGiamGia(Entities.ChiTietTheGiamGia input)
{
Entities.ChiTietTheGiamGia[] list = null;
try
{
con = new Connection();
cn = con.openConnection();
cmd = new SqlCommand("exec sp_ChiTietTheGiamGia @ThaoTac,@MaTheGiamGia,@MaKhachHang,@TenKhachHang,@GiaTriThe,@NgayThu,@MaPhieuThu,@Deleted", cn);
cmd.Parameters.Add("ThaoTac", SqlDbType.NVarChar).Value = input.ThaoTac;
//set
cmd.Parameters.Add("MaTheGiamGia", SqlDbType.VarChar).Value = input.MaTheGiamGia;
cmd.Parameters.Add("MaKhachHang", SqlDbType.VarChar).Value = input.MaKhachHang;
cmd.Parameters.Add("TenKhachHang", SqlDbType.NVarChar).Value = input.TenKhachHang;
cmd.Parameters.Add("GiaTriThe", SqlDbType.Float).Value = input.GiaTriThe;
cmd.Parameters.Add("NgayThu", SqlDbType.DateTime).Value = input.NgayThu;
cmd.Parameters.Add("MaPhieuThu", SqlDbType.VarChar).Value = input.MaPhieuThu;
cmd.Parameters.Add("Deleted", SqlDbType.Bit).Value = input.Deleted;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
arr = new ArrayList();
while (dr.Read())
{
Entities.ChiTietTheGiamGia row = new Entities.ChiTietTheGiamGia();
row.MaTheGiamGia = dr["MaTheGiamGia"].ToString();
row.MaKhachHang = dr["MaKhachHang"].ToString();
row.TenKhachHang = dr["TenKhachHang"].ToString();
row.GiaTriThe = float.Parse(dr["GiaTriThe"].ToString());
row.NgayThu = DateTime.Parse(dr["NgayThu"].ToString());
row.MaPhieuThu = dr["MaPhieuThu"].ToString();
row.Deleted = bool.Parse(dr["Deleted"].ToString());
arr.Add(row);
}
int n = arr.Count;
if (n == 0) list = null;
list = new Entities.ChiTietTheGiamGia[n];
for (int i = 0; i < n; i++)
{
list[i] = (Entities.ChiTietTheGiamGia)arr[i];
}
}
catch (Exception ex)
{ string s = ex.Message.ToString(); list = null; }
finally
{
cmd.Connection.Dispose();
cn.Close();
con.closeConnection();
}
return list;
}
public int noOfList(string tablename)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
int rows = 0;
String query = "select * from " + tablename + "";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
rows = dt.Rows.Count;
return rows;
}
//==============Select========================
public Entities.TyLeTinh[] SelectTyLeTinh()
{
Entities.TyLeTinh[] list = null;
try
{
con = new Connection();
cn = con.openConnection();
cmd = new SqlCommand("exec sp_TyLeTinh @ThaoTac,@MaTyLeTinh,@SoTien,@NgayNhap,@GhiChu,@Deleted", cn);
cmd.Parameters.Add("ThaoTac", SqlDbType.NVarChar).Value = "select";
Entities.TyLeTinh input = new Entities.TyLeTinh();
//set
cmd.Parameters.Add("MaTyLeTinh", SqlDbType.VarChar).Value = input.MaTyLeTinh;
cmd.Parameters.Add("SoTien", SqlDbType.Float).Value = input.SoTien;
cmd.Parameters.Add("NgayNhap", SqlDbType.DateTime).Value = input.NgayNhap;
cmd.Parameters.Add("GhiChu", SqlDbType.NVarChar).Value = input.GhiChu;
cmd.Parameters.Add("Deleted", SqlDbType.Bit).Value = input.Deleted;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
arr = new ArrayList();
while (dr.Read())
{
Entities.TyLeTinh row = new Entities.TyLeTinh();
row.MaTyLeTinh = dr["MaTyLeTinh"].ToString();
row.SoTien = double.Parse(dr["SoTien"].ToString());
row.NgayNhap = DateTime.Parse(dr["NgayNhap"].ToString());
row.GhiChu = dr["GhiChu"].ToString();
row.Deleted = bool.Parse(dr["Deleted"].ToString());
arr.Add(row);
}
int n = arr.Count;
if (n == 0) list = null;
list = new Entities.TyLeTinh[n];
for (int i = 0; i < n; i++)
{
list[i] = (Entities.TyLeTinh)arr[i];
}
}
catch (Exception ex)
{ string s = ex.Message.ToString(); list = null; }
finally
{
cmd.Connection.Dispose();
cn.Close();
con.closeConnection();
}
return list;
}
public string[] getInfo(string projectname)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
String[] info=new String[8];
String quary;
SqlCommand com;
try
{
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Delete
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public int Delete(Entities.KhuyenMaiSoLuong item)
{
int rt = 0;
try
{
conn = new Connection();
SqlConnection cn = conn.openConnection();
string sql = "exec sp_DeleteKMSL @MaHangHoa";
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add("MaHangHoa", SqlDbType.VarChar, 50).Value = item.MaHangHoa;
rt = cmd.ExecuteNonQuery();
}
catch
{
rt = 0;
}
return rt;
}
public void editPassword(string username, string password)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
try
{
String query = "UPDATE tbl_authenticate SET password='******'where username='******'";
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void updateProject(string name, string description, string user1, string name1, string user2, string name2, string user3, string name3, string projectname)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
try
{
String query = "UPDATE tbl_project SET project='" + name + "',projectdescription='" + description + "',people1username='******',people1name='" + name1 + "',people2username='******',people2name='" + name2 + "',people3username='******',people3name='" + name3 + "'where project='" + projectname + "'";
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
// MessageBox.Show(name+" "+description+" "+user1+" "+name1+" "+user2+" "+name2+" "+user3+" "+name3+" Edited!!!!");
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public Boolean LoginConnection(string userName, string password)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
int rows = 0;
string uName = userName;
string pass = password;
String query = "select * from tbl_authenticate where username='******' and password='******'";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
rows = dt.Rows.Count;
if (rows > 0)
return true;
else
return false;
}
public string editInfo(string username, string name, string designation, string email, string phoneno, string address,string dob,string gender)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
try
{
String query = "UPDATE tbl_profile SET name='" + name + "',username='******',designation='" + designation + "',email='" + email + "',phoneno='" + phoneno + "',address='" + address + "',dateofbirth='" + dob + "',gender='" + gender + "'where username='******'";
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return username;
}
public void updateTable(string oldname, string newname)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
//try
//{
String query = "sp_rename '" + oldname +
"','" + newname + "'";
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
MessageBox.Show(oldname + " "+" Edited");
con.Close();
//}
//catch (Exception ex)
//{
//MessageBox.Show(ex.Message);
//}
}
public void addToDo(string username, string name, string todo, string date, string table)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
try
{
String query = "INSERT INTO "+table+" VALUES('" + username + "','" + name + "','" + todo + "','" + date + "','" + "false" + "')";
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
MessageBox.Show("Assaigned Work Added!!!");
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
public void projectAdd(string projecttitle,string description,string uname1,string name1,string uname2,string name2, string uname3,string name3)
{
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
try
{
String query = "INSERT INTO tbl_project VALUES('" + projecttitle + "','" +description + "','" + uname1 + "','" +name1 + "','" + uname2 + "','" +name2 + "','" + uname3 + "','" + name3 + "')";
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
MessageBox.Show("Project Added!!!");
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
public int nameCheck(string name)
{
int rows = 0;
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
try
{
string query = "Select * from tbl_profile where name like '%" + name + "%'";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
rows = dt.Rows.Count;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return rows;
}
public int checkProject(string projecttitle)
{
int rows = 0;
Connection cs = new Connection();
SqlConnection con = cs.CreateConnection();
try
{
String query = "select * from tbl_project where project='" + projecttitle + "'";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
rows = dt.Rows.Count;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
return rows;
}