public List<DTO_BaoCaoCongNo> TaoDanhSachCongNo(DTO_BaoCaoCongNo congno)
{
List<DTO_BaoCaoCongNo> DSCongNo = new List<DTO_BaoCaoCongNo>();
DataTable dt = new DataTable();
double TongNo = 0;
dt = dal_baocaocongno.LayDanhSachKetQua(congno, "");
if (dt.Rows.Count < 1)
return null;
for (int i = 0; i < dt.Rows.Count;i++ )
{
DTO_BaoCaoCongNo baocao = new DTO_BaoCaoCongNo();
baocao.MaKH = dt.Rows[i]["MaKH"].ToString();
double TGD = (!dt.Rows[i][1].ToString().Equals("")) ? Double.Parse(dt.Rows[i][1].ToString()):0;
baocao.TongGiaoDich = TGD;
double DTra = (!dt.Rows[i][2].ToString().Equals("")) ? Double.Parse(dt.Rows[i][2].ToString()):0;
baocao.DaTra = DTra;
baocao.TongNo = TGD - DTra;
DSCongNo.Add(baocao);
TongNo += baocao.TongNo;
}
for(int i = 0;i < DSCongNo.Count;i++)
{
DSCongNo[i].Tile = (DSCongNo[i].TongNo * 100) / TongNo;
}
return SapXepDanhSachKH(DSCongNo);
}
public DataTable LayDanhSachKhachHang(DTO_BaoCaoCongNo congno)
{
DataTable dt = new DataTable();
connect.MoKetNoi();
string sql = "select MaKH, HoTen from KhachHang";
dt = connect.GetDataTable(sql);
return dt;
}
public DataTable LayDanhSachKetQua(DTO_BaoCaoCongNo congno,string makh)
{
DataTable dt = new DataTable();
connect.MoKetNoi();
string sql = "select kh.Makh, COALESCE(sum(hd.tongtien),0), COALESCE(sum(tt.SoTienNop),0) from KhachHang as kh left join HoaDon as hd on kh.MaKH = hd.MaKH left join PhieuThanhToan as tt on hd.MaHD = tt.MaHD left join NhanVien as nv on hd.MaNV = nv.MaNV where hd.ThoiGianLap >= '"+congno.TuNgay+"' and hd.ThoiGianLap <= '"+congno.DenNgay+"' and tt.ThoiGianNop >= '"+congno.TuNgay+"' and tt.ThoiGianNop <= '"+congno.DenNgay+"' group by kh.MaKH";
dt = connect.GetDataTable(sql);
return dt;
}
public List<DTO_BaoCaoCongNo> SapXepDanhSachKH(List<DTO_BaoCaoCongNo> DSCongNo)
{
int index = 0;
DTO_BaoCaoCongNo temp = new DTO_BaoCaoCongNo();
for (int i = 0; i < DSCongNo.Count && i < 10; i++ )
{
index = i;
temp = DSCongNo[i];
for(int j = i+1 ; j < DSCongNo.Count ; j++)
{
if (temp.Tile < DSCongNo[j].Tile)
index = j;
}
temp = DSCongNo[i];
DSCongNo[i]= DSCongNo[index];
DSCongNo[index] = temp;
}
return DSCongNo;
}
public DataTable LayDanhSachKhachHang(DTO_BaoCaoCongNo congno)
{
return dal_baocaocongno.LayDanhSachKhachHang(congno);
}
public DataTable LayDanhSachKetQua(DTO_BaoCaoCongNo congno)
{
return dal_baocaocongno.LayDanhSachKetQua(congno, "");
}