void checkNganSach(string id_vi, string id_loai_gd, string ten_vi, string ten_loai_gd)
{
//SELECT dbo.GET_TienNganSach('','')
double tien_ngan_sach = getTienNganSach(id_vi, id_loai_gd);
double so_tien_da_su_dung = getTienDaSuDung(id_vi, id_loai_gd);
double res = tien_ngan_sach - so_tien_da_su_dung;
if (tien_ngan_sach != 0)
{
if (res < 0)
{
Commom.Alert("Chi vượt mức ngân sách " + Commom.getMoneyStr(res.ToString()) + " (" + ten_loai_gd + ", " + ten_vi + ")", Form_Alert.enmType.Error);
}
else if (res == 0) // hết ngân sách
{
Commom.Alert("Chi 100% ngân sách " + "(" + ten_loai_gd + ", " + ten_vi + ")", Form_Alert.enmType.Warning);
}
else // res > 0 // còn ngân sách
{
double per = (so_tien_da_su_dung * tien_ngan_sach) / tien_ngan_sach;
Commom.Alert("Chi " + per.ToString() + " ngân sách " + "(" + ten_loai_gd + ", " + ten_vi + ")", Form_Alert.enmType.Info);
}
}
}
void getData()
{
string query = @"SELECT ten_nguoi_quen, so_tien, sdt, dia_chi
FROM NguoiQuen
WHERE ma_nguoi_quen = "+ IdNguoiQuen +"";
DB.conn.Open();
SqlCommand cmd = new SqlCommand(query, DB.conn);
try
{
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
name = rd[0].ToString() is DBNull ? string.Empty : rd[0].ToString();
lbName.Text = name;
sotien = rd[1].ToString() is DBNull ? string.Empty : rd[1].ToString();
lbSoTien.Text = Commom.getMoneyStr(sotien) + " VND";
sdt = rd[2].ToString() is DBNull ? string.Empty : rd[2].ToString();
lbSDT.Text = sdt;
diachi = rd[3].ToString() is DBNull ? string.Empty : rd[3].ToString();
lbDiaChi.Text = diachi;
}
}
catch (Exception ex)
{
}
DB.conn.Close();
}
void getDataTaiSanGB()
{
getImageTaiSan();
lbNameSP.Text = ((CTaiSan)cbbTaiSan.SelectedItem).Name;
lbCountSP.Text = ((CTaiSan)cbbTaiSan.SelectedItem).Count;
lbValueSP.Text = Commom.getMoneyStr(((CTaiSan)cbbTaiSan.SelectedItem).Value);
}
void getDataGiaoDichTaiChinh()
{
string query = @"SELECT LoaiGiaoDich.img, LoaiGiaoDich.ten_loai_gd, GiaoDichTaiChinh.ghi_chu,NguoiQuen.ten_nguoi_quen , GiaoDichTaiChinh.so_tien, GiaoDichTaiChinh.thoi_gian, VI.img, VI.ten_vi
FROM GiaoDichTaiChinh, VI, LoaiGiaoDich, NguoiQuen
WHERE GiaoDichTaiChinh.ma_vi = VI.ma_vi
AND GiaoDichTaiChinh.ma_loai_gd = LoaiGiaoDich.ma_loai_gd
AND GiaoDichTaiChinh.ma_nguoi_quen = NguoiQuen.ma_nguoi_quen
AND GiaoDichTaiChinh.ma_giao_dich = " + id_giaodich + "";
string imgGiaoDich = "", tenGiaoDich = "", moTa = "", tenNguoiQuen = "", soTien = "", thoiGian = "", imgVi = "", tenVi = "";
DB.conn.Open();
SqlCommand cmd = new SqlCommand(query, DB.conn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
imgGiaoDich = rd[0].ToString();
tenGiaoDich = rd[1].ToString();
moTa = rd[2].ToString();
tenNguoiQuen = rd[3].ToString();
soTien = rd[4].ToString();
thoiGian = rd[5].ToString();
imgVi = rd[6].ToString();
tenVi = rd[7].ToString();
}
DB.conn.Close();
pbImgGiaoDich.Image = Commom.getImage(imgGiaoDich);
lbTenGiaoDich.Text = tenGiaoDich;
lbDescription.Text = moTa + " (" + tenNguoiQuen + ")";
lbMoney.Text = Commom.getMoneyStr(soTien);
string[] l = thoiGian.Split(' ');
lbTime.Text = l[0];
pbImgVi.Image = Commom.getImage(imgVi);
lbTenVi.Text = tenVi;
}
void getTongGiaTri()
{
if (_count == string.Empty || _value == string.Empty)
{
return;
}
int iCount = Convert.ToInt32(_count);
float fValue = float.Parse(_value);
float tongGiaTri = iCount * fValue;
labelTongGiaTri.Text = Commom.getMoneyStr(tongGiaTri.ToString());
}
void getImageViTien()
{
string name_img = ((CWallet)cbbVi.SelectedItem).Img;
pbVi.Image = Commom.getImage(name_img);
string type_money = ((CWallet)cbbVi.SelectedItem).Type;
if (type_money == "VND")
{
pbSoTien.Image = Commom.getImage("vnd.PNG");
}
else
{
pbSoTien.Image = Commom.getImage("usd.PNG");
}
}
void getLbGiaoDichDauCuoi(string id)
{
double db_earn_money = 0;
double db_pay_money = 0;
string earn_money_query = @"SELECT dbo.GET_Earnings('" + id + "', '" + IDUser + "')";
SqlCommand cmd = new SqlCommand(earn_money_query, DB.conn);
DB.conn.Open();
try
{
db_earn_money = double.Parse(cmd.ExecuteScalar().ToString());
}
catch (Exception) { }
DB.conn.Close();
string pay_money_query = @"SELECT dbo.GET_Paying('" + id + "', '" + IDUser + "')";
cmd = new SqlCommand(pay_money_query, DB.conn);
DB.conn.Open();
try
{
db_pay_money = double.Parse(cmd.ExecuteScalar().ToString());
}
catch (Exception) { }
DB.conn.Close();
string money_type = ((CWallet)cbbVi.SelectedItem).Type;
double db_wallet_money_at_end = double.Parse(((CWallet)cbbVi.SelectedItem).Money);
double db_wallet_money_at_first = (db_wallet_money_at_end - db_earn_money) + db_pay_money;
lbAtFirstMoney.Text = Commom.getMoneyStr(db_wallet_money_at_first.ToString()) + money_type;
lbAtEndMoney.Text = Commom.getMoneyStr(db_wallet_money_at_end.ToString()) + money_type;
double db_wallet_money_at_final = (db_wallet_money_at_end - db_wallet_money_at_first);
if (db_wallet_money_at_final >= 0)
{
lbAtFinalMoney.Text = "+" + Commom.getMoneyStr(db_wallet_money_at_final.ToString()) + money_type;
}
else
{
lbAtFinalMoney.Text = Commom.getMoneyStr(db_wallet_money_at_final.ToString()) + money_type;
}
}
void getImageViTien()
{
string name_img = ((CWallet)cbbVi.SelectedItem).Img;
pbVi.Image = Commom.getImage(name_img);
string type_money = ((CWallet)cbbVi.SelectedItem).Type;
if (type_money == "VND")
{
pbTypeMoney.Image = Commom.getImage("vnd.PNG");
}
else
{
pbTypeMoney.Image = Commom.getImage("usd.PNG");
}
lbNameWallet.Text = ((CWallet)cbbVi.SelectedItem).Name;
lbMoney.Text = Commom.getMoneyStr(((CWallet)cbbVi.SelectedItem).Money) + ((CWallet)cbbVi.SelectedItem).Type;
}
/* Lấy dữ liệu từ DB đổ lên List */
void getData()
{
listWallets = new List <CSoNo>();
float sum_cho_vay = 0, sum_di_vay = 0;
string query = @"SELECT NguoiQuen.ten_nguoi_quen, LoaiGiaoDich.ten_loai_gd , GiaoDichTaiChinh.so_tien , COUNT(*)
FROM GiaoDichTaiChinh, TaiKhoan, VI, NguoiQuen, LoaiGiaoDich
WHERE TaiKhoan.taikhoan = VI.taikhoan
AND TaiKhoan.taikhoan = NguoiQuen.taikhoan
AND GiaoDichTaiChinh.ma_vi = VI.ma_vi
AND GiaoDichTaiChinh.ma_nguoi_quen = NguoiQuen.ma_nguoi_quen
AND GiaoDichTaiChinh.ma_loai_gd = LoaiGiaoDich.ma_loai_gd
AND TaiKhoan.taikhoan = '" + IDUser + "'" +
"AND NguoiQuen.ma_nguoi_quen != " + Commom.clone_nguoi_quen + "" +
"GROUP BY NguoiQuen.ten_nguoi_quen, LoaiGiaoDich.ten_loai_gd, GiaoDichTaiChinh.so_tien";
DB.conn.Open();
SqlCommand cmd = new SqlCommand(query, DB.conn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
CSoNo owe = new CSoNo(rd[0].ToString(), rd[1].ToString(), rd[2].ToString(), rd[3].ToString());
listWallets.Add(owe);
if (owe.LoaiNhanTra == "Đi vay")
{
sum_di_vay += float.Parse(owe.SoTien);
}
else
{
sum_cho_vay += float.Parse(owe.SoTien);
}
}
DB.conn.Close();
lbChoVay.Text = Commom.getMoneyStr(sum_cho_vay.ToString());
lbDiVay.Text = Commom.getMoneyStr(sum_di_vay.ToString());
addItem();
}
private void cbbDinhKi_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbbDinhKi.SelectedIndex == -1)
{
return;
}
else if (cbbDinhKi.SelectedIndex == 0) // tất cả
{
loadForm("", "");
}
else if (cbbDinhKi.SelectedIndex == 1) // hôm nay
{
string time = DateTime.Now.ToString("MM/dd/yyyy");
loadForm(time, "");
}
else if (cbbDinhKi.SelectedIndex == 2) // hôm qua
{
string time = DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy");
loadForm(time, "");
}
else if (cbbDinhKi.SelectedIndex == 3) // tuần này
{
string time_start = Commom.FirstDayOfWeek(DateTime.Now).AddDays(1).ToString("MM/dd/yyyy");
string time_end = Commom.FirstDayOfWeek(DateTime.Now).AddDays(7).ToString("MM/dd/yyyy");
loadForm(time_start, time_end);
}
else if (cbbDinhKi.SelectedIndex == 4) // tháng này
{
string time_start = new DateTime(Date.Year, Date.Month, 1).ToString("MM/dd/yyyy");
int day_in_month = DateTime.DaysInMonth(Date.Year, Date.Month);
string time_end = new DateTime(Date.Year, Date.Month, day_in_month).ToString("MM/dd/yyyy");
loadForm(time_start, time_end);
}
else if (cbbDinhKi.SelectedIndex == 5) // Năm nay
{
string time_start = new DateTime(Date.Year, 1, 1).ToString("MM/dd/yyyy");
string time_end = new DateTime(Date.Year, 12, 31).ToString("MM/dd/yyyy");
loadForm(time_start, time_end);
}
}
void getData()
{
string query = @"SELECT ma_tai_san, ten_tai_san, so_luong, tri_gia, mo_ta, img
FROM TaiSan
WHERE ma_tai_san = " + Id + "";
DB.conn.Open();
SqlCommand cmd = new SqlCommand(query, DB.conn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
TaiSan = new CTaiSan(rd[0].ToString(), rd[1].ToString(), rd[2].ToString(), rd[3].ToString(), rd[4].ToString(), (byte[])rd[5]);
}
DB.conn.Close();
lbName.Text = TaiSan.Name;
lbCount.Text = TaiSan.Count;
lbValue.Text = Commom.getMoneyStr(TaiSan.Value);
lbDescription.Text = TaiSan.Description;
pbAnhTaiSan.Image = TaiSan.getImage();
}
void getDataGiaoDichTaiSan()
{
string query = @"SELECT LoaiGiaoDich.img, LoaiGiaoDich.ten_loai_gd, GiaoDichTaiSan.ghi_chu, TaiSan.ten_tai_san, (GiaoDichTaiSan.so_luong * TaiSan.tri_gia), GiaoDichTaiSan.thoi_gian, VI.img, VI.ten_vi, GiaoDichTaiSan.so_luong
FROM GiaoDichTaiSan, VI, LoaiGiaoDich, TaiSan
WHERE GiaoDichTaiSan.ma_vi = VI.ma_vi
AND GiaoDichTaiSan.ma_loai_gd = LoaiGiaoDich.ma_loai_gd
AND GiaoDichTaiSan.ma_tai_san = TaiSan.ma_tai_san
AND GiaoDichTaiSan.ma_giao_dich = " + id_giaodich + "";
string imgGiaoDich = "", tenGiaoDich = "", moTa = "", tenTaiSan = "", soTien = "", thoiGian = "", imgVi = "", tenVi = "", soLuong = "";
DB.conn.Open();
SqlCommand cmd = new SqlCommand(query, DB.conn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
imgGiaoDich = rd[0].ToString();
tenGiaoDich = rd[1].ToString();
moTa = rd[2].ToString();
tenTaiSan = rd[3].ToString();
soTien = rd[4].ToString();
thoiGian = rd[5].ToString();
imgVi = rd[6].ToString();
tenVi = rd[7].ToString();
soLuong = rd[8].ToString();
}
DB.conn.Close();
pbImgGiaoDich.Image = Commom.getImage(imgGiaoDich);
lbTenGiaoDich.Text = tenGiaoDich;
string[] l = soLuong.Split('.');
lbDescription.Text = moTa + " (" + l[0] + "*" + tenTaiSan + ")";
lbMoney.Text = Commom.getMoneyStr(soTien);
l = thoiGian.Split(' ');
lbTime.Text = l[0];
pbImgVi.Image = Commom.getImage(imgVi);
lbTenVi.Text = tenVi;
}
void getImageLoaiGiaoDich()
{
string name_img = ((CLoaiGiaoDich)cbbLoaiGiaoDich.SelectedItem).Img;
pbLoaiGiaoDich.Image = Commom.getImage(name_img);
}
private void llbThemVi_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
string so_tien = tbSoTien.Text;
if (cbbVi.SelectedIndex == -1)
{
MessageBox.Show("Vui lòng chọn ví để giao dịch!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string id_vi = ((CWallet)cbbVi.SelectedItem).Id;
string ten_vi = ((CWallet)cbbVi.SelectedItem).Name;
if (cbbNhomGiaoDich.SelectedIndex == -1)
{
MessageBox.Show("Vui chọn nhóm giao dịch!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string name_nhom_giao_dich = ((CNhomGiaoDich)cbbNhomGiaoDich.SelectedItem).TenNhom;
if (cbbLoaiGiaoDich.SelectedIndex == -1)
{
MessageBox.Show("Vui lòng chọn loại giao dịch!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string ten_loai_giao_dich = ((CLoaiGiaoDich)cbbLoaiGiaoDich.SelectedItem).TenLoaiGiaoDich;
string id_loai_giao_dich = ((CLoaiGiaoDich)cbbLoaiGiaoDich.SelectedItem).MaLoaiGiaoDich;
if (so_tien == string.Empty)
{
MessageBox.Show("Vui lòng nhập số tiền giao dịch!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string tien_trong_vi = getTienTrongVi(id_vi);
float ftien_giao_dich = float.Parse(so_tien);
float ftien_trong_vi = float.Parse(tien_trong_vi);
if (ftien_giao_dich > ftien_trong_vi && (name_nhom_giao_dich != "Khoản thu" || ten_loai_giao_dich == "Đi vay" || ten_loai_giao_dich == "Thu nợ"))
{
MessageBox.Show("Số tiền giao dịch phải nhỏ hơn hoặc bằng số tiền trong ví!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string time = dtpNgayGiaoDich.Value.ToString();
string ghi_chu = tbGhiChu.Text;
string query;
if (name_nhom_giao_dich == "Đi vay/Cho vay")
{
if (cbbNguoiQuen.SelectedIndex == -1) // chọn người quen
{
MessageBox.Show("Chọn người quen để thực hiện giao dịch vay/nợ !", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string id_nguoi_quen = ((CNguoiQuen)cbbNguoiQuen.SelectedItem).MaNguoiQuen;
if (ten_loai_giao_dich == "Trả nợ")
{
string ma_giao_dich = ((CNguoiQuen)cbbNguoiQuen.SelectedItem).MaGiaoDich;
string id_vi_xoa = getMaVi(ma_giao_dich);
string loai_tien_vi_xoa = getLoaiTien(id_vi_xoa);
string loai_tien_vi = getLoaiTien(id_vi);
so_tien = Commom.ConvertMoney(loai_tien_vi_xoa, loai_tien_vi, float.Parse(so_tien));
query = @"INSERT INTO GiaoDichTaiChinh(ma_vi, ma_loai_gd, ma_nguoi_quen, so_tien, thoi_gian, ghi_chu)
VALUES('" + id_vi + "', '" + id_loai_giao_dich + "', '" + id_nguoi_quen + "', " + so_tien + ", '" + time + "', N'" + ghi_chu + "')";
del();
}
else if (ten_loai_giao_dich == "Thu nợ")
{
string ma_giao_dich = ((CNguoiQuen)cbbNguoiQuen.SelectedItem).MaGiaoDich;
string id_vi_xoa = getMaVi(ma_giao_dich);
string loai_tien_vi_xoa = getLoaiTien(id_vi_xoa);
string loai_tien_vi = getLoaiTien(id_vi);
so_tien = Commom.ConvertMoney(loai_tien_vi_xoa, loai_tien_vi, float.Parse(so_tien));
query = @"INSERT INTO GiaoDichTaiChinh(ma_vi, ma_loai_gd, ma_nguoi_quen, so_tien, thoi_gian, ghi_chu)
VALUES('" + id_vi + "', '" + id_loai_giao_dich + "', '" + id_nguoi_quen + "', " + so_tien + ", '" + time + "', N'" + ghi_chu + "')";
del();
}
else// Đi vay || Cho vay
{
query = @"INSERT INTO GiaoDichTaiChinh(ma_vi, ma_loai_gd, ma_nguoi_quen, so_tien, thoi_gian, ghi_chu)
VALUES('" + id_vi + "', '" + id_loai_giao_dich + "', '" + id_nguoi_quen + "', " + so_tien + ", '" + time + "', N'" + ghi_chu + "')";
}
}
else // giao dịch khác vay/trả
{
query = @"INSERT INTO GiaoDichTaiChinh(ma_vi, ma_loai_gd, ma_nguoi_quen, so_tien, thoi_gian, ghi_chu)
VALUES('" + id_vi + "', '" + id_loai_giao_dich + "', '" + Commom.clone_nguoi_quen + "', " + so_tien + ", '" + time + "', N'" + ghi_chu + "')";
}
DB.conn.Open();
try
{
SqlCommand cmd = new SqlCommand(query, DB.conn);
cmd.ExecuteNonQuery();
MessageBox.Show("Thêm giao dịch thành công!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
clearData();
}
catch (Exception ex)
{
MessageBox.Show("Thêm giao dịch thất bại! EX:" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
DB.conn.Close();
}
getDataVi();
checkNganSach(id_vi, id_loai_giao_dich, ten_vi, ten_loai_giao_dich);
}
void getImageViTien()
{
string name_img = ((CWallet)cbbVi.SelectedItem).Img;
pbVi.Image = Commom.getImage(name_img);
}