/* 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 , SUM(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(LoaiGiaoDich.ten_loai_gd = N'Đi vay' OR LoaiGiaoDich.ten_loai_gd = N'Cho vay')" + "AND NguoiQuen.ma_nguoi_quen != " + Commom.clone_nguoi_quen + ""; if (timeEnd == string.Empty && timeStart != string.Empty) { query += " AND GiaoDichTaiChinh.thoi_gian = '" + timeStart + "'"; } else if (timeEnd != string.Empty && timeStart != string.Empty) { query += " AND GiaoDichTaiChinh.thoi_gian >= '" + timeStart + "' AND GiaoDichTaiChinh.thoi_gian <= '" + timeEnd + "'"; } if (IDWallets != string.Empty) { query += " AND VI.ma_vi = " + IDWallets + ""; } query += " GROUP BY NguoiQuen.ten_nguoi_quen, LoaiGiaoDich.ten_loai_gd"; 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 if (owe.LoaiNhanTra == "Cho vay") { sum_cho_vay += float.Parse(owe.SoTien); } } DB.conn.Close(); lbChoVay.Text = Commom.getMoneyStr(sum_cho_vay.ToString()); lbNo.Text = Commom.getMoneyStr(sum_di_vay.ToString()); }
void getDataChartChi() { double sum = 0; string query = @"SELECT SUM(GiaoDichTaiChinh.so_tien) AS 'so_tien', LoaiGiaoDich.ten_loai_gd FROM GiaoDichTaiChinh, LoaiGiaoDich,NhomGiaoDich,VI WHERE GiaoDichTaiChinh.ma_loai_gd = LoaiGiaoDich.ma_loai_gd AND LoaiGiaoDich.ma_nhom_gd = NhomGiaoDich.ma_nhom_gd AND GiaoDichTaiChinh.ma_vi = VI.ma_vi AND VI.taikhoan = '" + IdUser + "' AND NhomGiaoDich.ten_nhom_gd = N'Khoản chi'"; if (timeEnd == string.Empty && timeStart != string.Empty) { query += " AND GiaoDichTaiChinh.thoi_gian = '" + timeStart + "'"; } else if (timeEnd != string.Empty && timeStart != string.Empty) { query += " AND GiaoDichTaiChinh.thoi_gian >= '" + timeStart + "' AND GiaoDichTaiChinh.thoi_gian <= '" + timeEnd + "'"; } if (IDWallets != string.Empty) { query += " AND VI.ma_vi = '" + IDWallets + "'"; } query += " GROUP BY LoaiGiaoDich.ten_loai_gd"; DB.conn.Open(); SqlCommand cmd = new SqlCommand(query, DB.conn); SqlDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { chartChi.Series["Chi"].Points.AddXY(rd[1].ToString(), double.Parse(rd[0].ToString())); sum += double.Parse(rd[0].ToString()); } DB.conn.Close(); if (sum == 0) { chartChi.Series["Chi"].Points.AddXY("Chưa có giao dịch", 100); } lbKhoanChi.Text = "Khoản chi: " + Commom.getMoneyStr(sum.ToString()); }
public void GetData() { string query = @"SELECT so_tien, ngay_bat_dau, ngay_ket_thuc FROM NganSach WHERE ma_vi = " + iD_Vi + " AND ma_loai_gd = " + iD_Loai_Gd + ""; DB.conn.Open(); SqlCommand cmd = new SqlCommand(query, DB.conn); SqlDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { llbSoTien.Text = Commom.getMoneyStr(rd[0].ToString()); llbNgayBD.Text = Commom.getDate(rd[1].ToString()); llbNgayKT.Text = Commom.getDate(rd[2].ToString()); } DB.conn.Close(); getLoaiGD(iD_Loai_Gd); getVi(iD_Vi); lbSoTienDaChi.Text = so_tien_da_chi; }
private void checkColor(string strMoney) { double dMoney = double.Parse(strMoney); if (dMoney > 0) // còn định mức { lbSoTienConLai.Text = "Còn lại: " + Commom.getMoneyStr(strMoney); lbSoTienConLai.ForeColor = Color.ForestGreen; } else if (dMoney == 0) // bằng định mức { lbSoTienConLai.Text = "Hết định mức: " + Commom.getMoneyStr(strMoney); lbSoTienConLai.ForeColor = Color.ForestGreen; } else // vượt { lbSoTienConLai.Text = "Vượt định mức: " + Commom.getMoneyStr(strMoney); lbSoTienConLai.ForeColor = Color.Red; } }