/* 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;
}
}
