#region
Khai báo list
Library.Utilities.ThreadCls threads;
System.Collections.Generic.List<KhachHang> objKH = new
System.Collections.Generic.List<KhachHang>();
System.Collections.Generic.List<TienCongNo> dauKy = new
System.Collections.Generic.List<TienCongNo>();
System.Collections.Generic.List<NoHoaDonDauKy> noDauKyHd1 = new
System.Collections.Generic.List<NoHoaDonDauKy>();
System.Collections.Generic.List<TienCongNo> objHD_NDK = new
System.Collections.Generic.List<TienCongNo>();
System.Collections.Generic.List<TienSoQuy> noDauKySq1 = new
System.Collections.Generic.List<TienSoQuy>();
System.Collections.Generic.List<TienCongNo> objSQ_NDK = new
System.Collections.Generic.List<TienCongNo>();
System.Collections.Generic.List<TienCongNo> objPhatSinh = new
System.Collections.Generic.List<TienCongNo>();
System.Collections.Generic.List<TienCongNo> objDaThu = new
System.Collections.Generic.List<TienCongNo>();
System.Collections.Generic.List<TienCongNo> objKhauTru = new
System.Collections.Generic.List<TienCongNo>();
System.Collections.Generic.List<TienCongNo> objThuTruoc = new
System.Collections.Generic.List<TienCongNo>();
System.Collections.Generic.List<TienCongNo> objThuTruocTrongKy =
new System.Collections.Generic.List<TienCongNo>();
System.Collections.Generic.List<DataCongNo> objList = new
System.Collections.Generic.List<DataCongNo>();
#endregion
public frmReceivables()
{
InitializeComponent();
}
#region Class
public class KhachHang { public
System.Int32 MaKH { get; set; } public System.String KyHieu { get; set; }
public System.String MaPhu { get; set; } public System.String TenKH { get; set;
} public System.String DienThoai { get; set; } public System.String EmailKH {
get; set; } public System.String DiaChi { get; set; } public System.Int32? MaMB
{ get; set; } public string MaSoMB { get; set; } }
public class TienCongNo { public int?
MaKH { get; set; } public System.Decimal SoTien { get; set; } }
public class TienSoQuy { public int?
MaKH { get; set; } public long? LinkID { get; set; } public System.Decimal
SoTien { get; set; } }
public class DataCongNo { public
decimal? ThuTruoc { get; set; } public decimal? NoDauKy { get; set; } public
decimal? PhatSinh { get; set; } public decimal? KhauTru { get; set; } public
decimal? DaThu { get; set; } public decimal? ConNo { get; set; } public int?
MaKH { get; set; } public System.String KyHieu { get; set; } public
System.String MaPhu { get; set; } public System.String TenKH { get; set; }
public System.String DienThoai { get; set; } public System.String EmailKH {
get; set; } public System.String DiaChi { get; set; } public int? MaMB { get;
set; } public decimal? NoCuoi { get; set; } public decimal? ThuTruocTK { get;
set; } public string MaSoMB { get; set; } }
public class NoHoaDonDauKy { public
System.Int64 ID { get; set; } public int? MaKH { get; set; } public
System.Decimal? PhaiThu { get; set; } public int? MaLDV { get; set; } public
int? MaMB { get; set; } }
#endregion
#region GetData
private
System.Collections.Generic.List<KhachHang> GetKhachHangs(byte? _MaTN,
System.DateTime? _DenNgay)
{
using(Library.MasterDataContext db
= new MasterDataContext())
{
return (from kh in
db.tnKhachHangs
//join mb in
db.mbMatBangs on kh.MaKH equals mb.MaKH into matBang from mb in
matBang.DefaultIfEmpty()
where kh.MaTN == _MaTN
& (kh.IsNgungSuDung == null | kh.IsNgungSuDung == false) //&&
kh.MaKH == 13259
select new KhachHang
{
MaKH= kh.MaKH,
KyHieu = kh.KyHieu,
MaPhu= kh.MaPhu,
TenKH = kh.IsCaNhan
== true ? (kh.HoKH + " " + kh.TenKH) : kh.CtyTen,
DienThoai =
kh.DienThoaiKH,
EmailKH=
kh.EmailKH,
DiaChi = kh.DCLL,
MaMB =
db.dvHoaDons.First(p =>
p.MaKH ==
kh.MaKH & SqlMethods.DateDiffDay(p.NgayTT, _DenNgay) >= 0 &
p.IsDuyet == true &
p.MaMB !=
null).MaMB,
MaSoMB =
db.mbMatBangs.FirstOrDefault(_=>_.MaKH == kh.MaKH) !=null?
db.mbMatBangs.FirstOrDefault(_ => _.MaKH == kh.MaKH).MaSoMB:""
}).ToList();
}
}
private
System.Collections.Generic.List<TienCongNo> GetDauKys(byte? _MaTN, int?
_Nam)
{
using(Library.MasterDataContext db
= new MasterDataContext())
{
return (from d in db.dvDauKies
where d.MaTN == _MaTN &
d.Nam == _Nam
group new { d } by new {
d.MaKH }
into g
select new TienCongNo { MaKH=
g.Key.MaKH, SoTien = g.Sum(_ => _.d.SoTien).GetValueOrDefault() }).ToList();
}
}
private
System.Collections.Generic.List<NoHoaDonDauKy> GetNoDauKyHd1(byte? _MaTN,
System.DateTime? _TuNgay, int? _Nam)
{
using(Library.MasterDataContext db
= new Library.MasterDataContext())
{
return (from hd in db.dvHoaDons
where
SqlMethods.DateDiffDay(hd.NgayTT, _TuNgay) > 0 & hd.IsDuyet == true
& hd.MaTN == _MaTN & hd.NgayTT.Value.Year == _Nam
select new
NoHoaDonDauKy { ID = hd.ID, MaKH = hd.MaKH, PhaiThu = hd.PhaiThu, MaLDV=
hd.MaLDV, MaMB= hd.MaMB }).ToList();
}
}
private
System.Collections.Generic.List<TienCongNo>
GetNoDauKyHoaDon(System.Collections.Generic.List<NoHoaDonDauKy>
noDauKyHd1)
{
return (from hd in noDauKyHd1
group hd by new { hd.MaKH }
into ndk
select new TienCongNo
{
MaKH = ndk.Key.MaKH,
SoTien = ndk.Sum(s
=> s.PhaiThu).GetValueOrDefault()
}).ToList();
}
private
System.Collections.Generic.List<TienSoQuy> GetNoDauKySq1(byte? _MaTN,
System.DateTime? _TuNgay, int? _Nam)
{
using(Library.MasterDataContext db
= new Library.MasterDataContext())
{
return (from sq in
db.SoQuy_ThuChis
where
SqlMethods.DateDiffDay(sq.NgayPhieu, _TuNgay) > 0 & sq.MaTN == _MaTN
&
sq.IsPhieuThu == true &
sq.MaLoaiPhieu != 24 & sq.LinkID != null & sq.NgayPhieu.Value.Year ==
_Nam &
sq.TableName ==
"dvHoaDon" //& sq.MaKH == 2634
select new TienSoQuy
{
MaKH= sq.MaKH,
LinkID= sq.LinkID,
SoTien =
sq.DaThu.GetValueOrDefault() - sq.ThuThua.GetValueOrDefault() +
sq.KhauTru.GetValueOrDefault()
}).ToList();
}
}
private
System.Collections.Generic.List<TienCongNo>
GetNoDauKySoQuy(System.Collections.Generic.List<TienSoQuy> noDauKySq1)
{
return (from sq in noDauKySq1
group new { sq } by new {
sq.MaKH }
into ndk
select new TienCongNo {
MaKH = ndk.Key.MaKH, SoTien = ndk.Sum(_ => _.sq.SoTien) })
.ToList();
}
private
System.Collections.Generic.List<TienCongNo> GetPhatSinhs(byte? _MaTN,
System.DateTime? _TuNgay)
{
using(Library.MasterDataContext db
= new Library.MasterDataContext())
{
return (from hd in db.dvHoaDons
where
SqlMethods.DateDiffMonth(hd.NgayTT, _TuNgay) == 0 & hd.IsDuyet == true
&& hd.MaTN == _MaTN
group hd by hd.MaKH
into ps
select new TienCongNo
{
MaKH = ps.Key,
SoTien = ps.Sum(s
=> s.PhaiThu).GetValueOrDefault(),
}).ToList();
}
}
private
System.Collections.Generic.List<TienCongNo> GetDaThus(byte? _MaTN,
System.DateTime? _TuNgay)
{
using(Library.MasterDataContext db
= new Library.MasterDataContext())
{
return (from ct in
db.SoQuy_ThuChis
join hd in db.dvHoaDons
on new { ct.TableName, ct.LinkID } equals new { TableName =
"dvHoaDon", LinkID = (long?)hd.ID } into hoaDon
from hd in
hoaDon.DefaultIfEmpty()
where
SqlMethods.DateDiffMonth(ct.NgayPhieu, _TuNgay) == 0
&&
ct.IsPhieuThu == true && ct.MaLoaiPhieu != 24 && ct.MaTN ==
_MaTN & ct.IsKhauTru == false & ct.LinkID != null & hd.IsDuyet ==
true
group ct by ct.MaKH
into dt
select new TienCongNo
{
MaKH = dt.Key,
//DaThu = dt.Sum(s
=> s.DaThu).GetValueOrDefault()
SoTien = dt.Sum(s
=> s.DaThu).GetValueOrDefault() - dt.Sum(_ =>
_.ThuThua).GetValueOrDefault() - dt.Sum(_ => _.KhauTru).GetValueOrDefault()
}).ToList();
}
}
private
System.Collections.Generic.List<TienCongNo> GetKhauTrus(byte? _MaTN,
System.DateTime? _TuNgay)
{
using(Library.MasterDataContext db
= new Library.MasterDataContext())
{
return (from ct in
db.SoQuy_ThuChis
join hd in db.dvHoaDons
on new { ct.TableName, ct.LinkID } equals new { TableName =
"dvHoaDon", LinkID = (long?)hd.ID } into hoaDon
from hd in
hoaDon.DefaultIfEmpty()
where
SqlMethods.DateDiffMonth(ct.NgayPhieu, _TuNgay) == 0
&&
ct.MaTN == _MaTN && ct.IsPhieuThu == true & ct.IsKhauTru == true
& ct.LinkID != null & hd.IsDuyet == true
group ct by ct.MaKH
into kt
select new TienCongNo
{
MaKH = kt.Key,
SoTien = kt.Sum(s
=> s.KhauTru + s.DaThu).GetValueOrDefault()
}).ToList();
}
}
private
System.Collections.Generic.List<TienCongNo> GetThuTruocs(byte? _MaTN,
System.DateTime? _DenNgay)
{
using (Library.MasterDataContext db
= new Library.MasterDataContext())
{
return (from sq in
db.SoQuy_ThuChis
where
SqlMethods.DateDiffDay(sq.NgayPhieu, _DenNgay) >= 0 && sq.MaTN ==
_MaTN
&& sq.IsPhieuThu
== true && sq.MaLoaiPhieu != 24
group sq by sq.MaKH
into tt
select new TienCongNo
{
MaKH = tt.Key,
SoTien = tt.Sum(s
=> s.ThuThua - s.KhauTru).GetValueOrDefault()
}).ToList();
}
}
private
System.Collections.Generic.List<TienCongNo> GetThuTruocTrongKy(byte?
_MaTN, System.DateTime? _TuNgay)
{
using(Library.MasterDataContext db
= new Library.MasterDataContext())
{
return (from sq in
db.SoQuy_ThuChis
where
SqlMethods.DateDiffMonth(sq.NgayPhieu, _TuNgay) == 0 && sq.MaTN ==
_MaTN
&& sq.IsPhieuThu ==
true && sq.MaLoaiPhieu != 24
group sq by sq.MaKH
into tt
select new TienCongNo
{
MaKH = tt.Key,
SoTien = tt.Sum(s
=> s.ThuThua).GetValueOrDefault()
}).ToList();
}
}
private
System.Collections.Generic.List<DataCongNo>
GetDataCongNos(System.Collections.Generic.List<KhachHang> objKH,
System.Collections.Generic.List<TienCongNo> dauKy, System.Collections.Generic.List<TienCongNo>
objHD_NDK, System.Collections.Generic.List<TienCongNo> objSQ_NDK,
System.Collections.Generic.List<TienCongNo> objPhatSinh,
System.Collections.Generic.List<TienCongNo> objDaThu,
System.Collections.Generic.List<TienCongNo> objKhauTru,
System.Collections.Generic.List<TienCongNo> objThuTruoc,
System.Collections.Generic.List<TienCongNo> objThuTruocTrongKy)
{
return (from kh in objKH
join dk in dauKy on kh.MaKH
equals dk.MaKH into soDuDauKy
from dk in
soDuDauKy.DefaultIfEmpty()
join ndk in objHD_NDK on
kh.MaKH equals ndk.MaKH into nodk
from ndk in
nodk.DefaultIfEmpty()
join sqdk in objSQ_NDK on
kh.MaKH equals sqdk.MaKH into soquydk
from sqdk in
soquydk.DefaultIfEmpty()
join ps in objPhatSinh on
kh.MaKH equals ps.MaKH into psinh
from ps in
psinh.DefaultIfEmpty()
join dt in objDaThu on
kh.MaKH equals dt.MaKH into dthu
from dt in
dthu.DefaultIfEmpty()
join kt in objKhauTru on
kh.MaKH equals kt.MaKH into ktru
from kt in
ktru.DefaultIfEmpty()
join tt in objThuTruoc on
kh.MaKH equals tt.MaKH into ttruoc
from tt in
ttruoc.DefaultIfEmpty()
select new
{
kh.MaKH,
kh.KyHieu,
kh.MaPhu,
TenKH = kh.TenKH,
DienThoai =
kh.DienThoai,
kh.EmailKH,
DiaChi = kh.DiaChi,
kh.MaMB,
NoDauKy = (dk != null ?
dk.SoTien : (decimal?)0) + (ndk == null ? 0 : ndk.SoTien) - (sqdk == null ? 0 :
sqdk.SoTien),
PhatSinh = ps == null ?
0 : ps.SoTien,
DaThu = dt == null ? 0
: dt.SoTien,
KhauTru = kt == null ?
0 : kt.SoTien,
ThuTruoc = tt == null ?
0 : tt.SoTien,
//ThuTruocTK = tttk ==
null ? 0 : tttk.SoTien,
MaSoMB = kh.MaSoMB,
}).Select(p => new
DataCongNo
{
ThuTruoc = p.ThuTruoc,
NoDauKy = p.NoDauKy,
PhatSinh = p.PhatSinh,
KhauTru = p.KhauTru,
DaThu = p.DaThu,
//ConNo = ((p.NoDauKy
< 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu + p.KhauTru - p.ThuTruocTK))
< 0 ? 0 : ((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu +
p.KhauTru - p.ThuTruocTK)),
//ConNo = ((p.NoDauKy
< 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu + p.KhauTru)) < 0 ? 0 :
((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu + p.KhauTru)),
//ConNo = p.NoDauKy +
p.PhatSinh - (p.DaThu + p.KhauTru),
ConNo = p.NoDauKy +
p.PhatSinh - (p.DaThu + p.KhauTru),
MaKH = p.MaKH,
KyHieu = p.KyHieu,
MaPhu = p.MaPhu,
TenKH = p.TenKH,
DienThoai =
p.DienThoai,
EmailKH = p.EmailKH,
DiaChi = p.DiaChi,
MaMB = p.MaMB,
//NoCuoi = (((p.NoDauKy
< 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu + p.KhauTru - p.ThuTruocTK))
< 0 ? 0 : ((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu +
p.KhauTru - p.ThuTruocTK))) - p.ThuTruoc,
//NoCuoi = (((p.NoDauKy
< 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu + p.KhauTru)) < 0 ? 0 :
((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu + p.KhauTru))) -
p.ThuTruoc,
NoCuoi = (p.NoDauKy) +
p.PhatSinh - (p.DaThu + p.KhauTru) - p.ThuTruoc,
//NoCuoi = ((p.NoDauKy)
+ p.PhatSinh - (p.DaThu + p.KhauTru - p.ThuTruocTK)) - p.ThuTruoc,
//ThuTruocTK =
p.ThuTruocTK,
MaSoMB = p.MaSoMB
}).ToList();
}
#endregion
private async void GetData()
{
await
System.Threading.Tasks.Task.Run(() => { objList = GetDataCongNos(objKH,
dauKy, objHD_NDK, objSQ_NDK, objPhatSinh, objDaThu, objKhauTru, objThuTruoc,
objThuTruocTrongKy); });
gcHoaDon.DataSource = objList;
}
private async void GetTask()
{
var db = new MasterDataContext();
db.CommandTimeout = 100000;
var _MaTN =
(byte)itemToaNha.EditValue;
var _Thang =
Convert.ToInt32(itemThang.EditValue);
var _Nam =
Convert.ToInt32(itemNam.EditValue);
var _TuNgay = new DateTime(_Nam,
_Thang, 1);
var _DenNgay =
Common.GetLastDayOfMonth(_Thang, _Nam);
var _NgayHienTai = DateTime.Now;
var index = 0;
index = threads.GetTaskIndex();
switch(index)
{
case 1:
// khách hàng
await
System.Threading.Tasks.Task.Run(() => { objKH = GetKhachHangs(_MaTN,
_DenNgay); });
break;
case 2:
// nợ đầu năm
await
System.Threading.Tasks.Task.Run(() => { dauKy = GetDauKys(_MaTN, _Nam); });
break;
case 3:
// ngày đầu năm
// hóa đơn
await
System.Threading.Tasks.Task.Run(() => { noDauKyHd1 = GetNoDauKyHd1(_MaTN,
_TuNgay, _Nam); });
await
System.Threading.Tasks.Task.Run(() => { objHD_NDK =
GetNoDauKyHoaDon(noDauKyHd1); });
break;
case 4:
// sổ quỹ
await
System.Threading.Tasks.Task.Run(() => { noDauKySq1 = GetNoDauKySq1(_MaTN,
_TuNgay, _Nam); });
await
System.Threading.Tasks.Task.Run(() => { objSQ_NDK =
GetNoDauKySoQuy(noDauKySq1); });
break;
case 5:
// phát sinh
await
System.Threading.Tasks.Task.Run(() => { objPhatSinh = GetPhatSinhs(_MaTN,
_TuNgay); });
break;
case 6:
// đã thu
await
System.Threading.Tasks.Task.Run(() => { objDaThu = GetDaThus(_MaTN,
_TuNgay); });
break;
case 7:
// khầu trừ
await
System.Threading.Tasks.Task.Run(() => { objKhauTru = GetKhauTrus(_MaTN,
_TuNgay); });
break;
case 8:
// thu trước
await
System.Threading.Tasks.Task.Run(() => { objThuTruoc = GetThuTruocs(_MaTN,
_DenNgay); });
break;
case 9:
// thu trước trong kỳ
await
System.Threading.Tasks.Task.Run(() => { objThuTruocTrongKy =
GetThuTruocTrongKy(_MaTN, _TuNgay); });
break;
default:
threads.Stop();
break;
}
}
async void LoadData()
{
gcHoaDon.DataSource = null;
var db = new MasterDataContext();
db.CommandTimeout = 100000;
var _MaTN =
(byte)itemToaNha.EditValue;
var _Thang =
Convert.ToInt32(itemThang.EditValue);
var _Nam =
Convert.ToInt32(itemNam.EditValue);
var _TuNgay = new DateTime(_Nam,
_Thang, 1);
var _DenNgay =
Common.GetLastDayOfMonth(_Thang, _Nam);
var _NgayHienTai = DateTime.Now;
#region Code mới
if (_MaTN == 8)
{
#region
var objKH = (from kh in
db.tnKhachHangs
where kh.MaTN ==
_MaTN & (kh.IsNgungSuDung == null | kh.IsNgungSuDung == false) //&
kh.MaKH == 8730
select new
{
kh.MaKH,
kh.KyHieu,
kh.MaPhu,
TenKH =
kh.IsCaNhan == true ? (kh.HoKH + " " + kh.TenKH) : kh.CtyTen,
DienThoai =
kh.DienThoaiKH,
kh.EmailKH,
DiaChi =
kh.DCLL,
MaMB =
db.dvHoaDons.First(p =>
p.MaKH ==
kh.MaKH & SqlMethods.DateDiffDay(p.NgayTT, _DenNgay) >= 0 &
p.IsDuyet == true &
p.MaMB !=
null).MaMB,
}).ToList();
var noDauKyHd1 = (from hd in
db.dvHoaDons
where
SqlMethods.DateDiffDay(hd.NgayTT, _TuNgay) > 0 & hd.IsDuyet == true
& hd.MaTN == _MaTN //& hd.MaKH == 8730
select new {
hd.ID, hd.MaKH, hd.PhaiThu, hd.MaLDV }).ToList();
var objHD_NDK = (from hd in
noDauKyHd1
group hd by
new { hd.MaKH }
into ndk
select new
{
MaKH =
ndk.Key.MaKH,
PhaiThu = ndk.Sum(s => s.PhaiThu)
}).ToList();
var noDauKySq1 = (from sq in
db.SoQuy_ThuChis
where
SqlMethods.DateDiffDay(sq.NgayPhieu, _TuNgay) > 0 & sq.MaTN == _MaTN
&
sq.IsPhieuThu == true & sq.MaLoaiPhieu != 24 & sq.LinkID != null
&
sq.TableName == "dvHoaDon" //& sq.MaKH == 8730
select new
{
sq.MaKH,
sq.LinkID,
SoTien =
sq.DaThu.GetValueOrDefault() + sq.KhauTru.GetValueOrDefault() -
sq.ThuThua.GetValueOrDefault()
}).ToList();
var noDauKySq_Ldv = (from sq in
noDauKySq1
join hd in
noDauKyHd1 on sq.LinkID equals hd.ID
group new
{ sq, hd } by new { sq.MaKH, hd.MaLDV }
into
ndk
select
new { MaKH = ndk.Key.MaKH, SoTien = ndk.Sum(_ => _.sq.SoTien), MaLDV =
ndk.Key.MaLDV })
.ToList();
var objSQ_NDK = (from sq in
noDauKySq_Ldv
group new { sq
} by new { sq.MaKH }
into ndk
select new
{ MaKH = ndk.Key.MaKH, NoDauKy = ndk.Sum(_ => _.sq.SoTien) })
.ToList();
var objPhatSinh = (from hd in
db.dvHoaDons
where
SqlMethods.DateDiffMonth(hd.NgayTT, _TuNgay) == 0 & hd.IsDuyet == true
&& hd.MaTN == _MaTN //& hd.MaKH == 8730
group hd by
hd.MaKH
into ps
select
new
{
MaKH
= ps.Key,
PhaiThu = ps.Sum(s => s.PhaiThu),
}).ToList();
var objDaThu1 = (from ct in
db.SoQuy_ThuChis
join hd in
db.dvHoaDons on ct.LinkID equals hd.ID
where
SqlMethods.DateDiffMonth(hd.NgayTT, _TuNgay) == 0
&& ct.IsPhieuThu == true && ct.MaLoaiPhieu != 24
&& ct.MaTN == _MaTN && ct.IsKhauTru == false //& ct.MaKH ==
8730
group ct by
ct.MaKH
into dt
select new
{
MaKH =
dt.Key,
DaThu
= dt.Sum(s => s.DaThu)
}).ToList();
var objDaThu2 = (from ct in
db.SoQuy_ThuChis
join hd in
db.dvHoaDons on ct.LinkID equals hd.ID
where
SqlMethods.DateDiffMonth(ct.NgayPhieu, _TuNgay) == 0
&& SqlMethods.DateDiffDay(_TuNgay, hd.NgayTT) < 0
&& ct.IsPhieuThu == true && ct.MaLoaiPhieu != 24
&& ct.MaTN == _MaTN & ct.IsKhauTru == false //& ct.MaKH == 8730
group ct by
ct.MaKH
into dt
select new
{
MaKH =
dt.Key,
DaThu
= dt.Sum(s => s.DaThu)
}).ToList();
var objDaThu3 = (from ct in
db.SoQuy_ThuChis
where
SqlMethods.DateDiffMonth(ct.NgayPhieu, _TuNgay) == 0
&& ct.IsPhieuThu == true && ct.MaLoaiPhieu != 24
&& ct.MaTN == _MaTN &&
ct.LinkID == null & ct.IsKhauTru == false //& ct.MaKH == 8730
& ct.LinkID != null
group ct by
ct.MaKH
into dt
select new
{
MaKH =
dt.Key,
DaThu
= dt.Sum(s => s.DaThu)
}).ToList();
var objDaThuTam =
objDaThu1.Concat(objDaThu2).Concat(objDaThu3);
var objDaThu = (from dt in
objDaThuTam
group dt by
dt.MaKH
into _DT
select new
{
MaKH =
_DT.Key,
DaThu =
_DT.Sum(s => s.DaThu)
}).ToList();
var objKhauTru = (from ct in
db.SoQuy_ThuChis
where
SqlMethods.DateDiffMonth(ct.NgayPhieu, _TuNgay) == 0 && ct.MaTN ==
_MaTN & ct.IsKhauTru == true
& ct.LinkID != null
group ct by
ct.MaKH
into kt
select
new
{
MaKH
= kt.Key,
KhauTru = kt.Sum(s => s.KhauTru + s.DaThu)
}).ToList();
var objThuTruoc = (from sq in
db.SoQuy_ThuChis
where
SqlMethods.DateDiffDay(sq.NgayPhieu, _DenNgay) >= 0 && sq.MaTN ==
_MaTN
&&
sq.IsPhieuThu == true && sq.MaLoaiPhieu != 24 //& sq.MaKH == 8730
// where
SqlMethods.DateDiffDay(sq.NgayPhieu, _DenNgay) >= 0 //&& sq.MaTN ==
_MaTN
group sq by
sq.MaKH
into tt
select
new
{
MaKH
= tt.Key,
ThuTruoc = tt.Sum(s => s.ThuThua - s.KhauTru)
}).ToList();
var objThuTruocTrongKy = (from
sq in db.SoQuy_ThuChis
where
SqlMethods.DateDiffMonth(sq.NgayPhieu, _TuNgay) == 0 && sq.MaTN ==
_MaTN
&&
sq.IsPhieuThu == true && sq.MaLoaiPhieu != 24 //& sq.MaKH == 8730
group
sq by sq.MaKH
into tt
select new
{
MaKH = tt.Key,
ThuTruoc = tt.Sum(s => s.ThuThua)
}).ToList();
var objList = (from kh in objKH
join ndk in
objHD_NDK on kh.MaKH equals ndk.MaKH into nodk
from ndk in
nodk.DefaultIfEmpty()
join sqdk in
objSQ_NDK on kh.MaKH equals sqdk.MaKH into soquydk
from sqdk in
soquydk.DefaultIfEmpty()
join ps in
objPhatSinh on kh.MaKH equals ps.MaKH into psinh
from ps in
psinh.DefaultIfEmpty()
join dt in
objDaThu on kh.MaKH equals dt.MaKH into dthu
from dt in
dthu.DefaultIfEmpty()
join kt in
objKhauTru on kh.MaKH equals kt.MaKH into ktru
from kt in
ktru.DefaultIfEmpty()
join tt in
objThuTruoc on kh.MaKH equals tt.MaKH into ttruoc
from tt in
ttruoc.DefaultIfEmpty()
join tttk in
objThuTruocTrongKy on kh.MaKH equals tttk.MaKH into tttrongky
from tttk in
tttrongky.DefaultIfEmpty()
select new
{
kh.MaKH,
kh.KyHieu,
kh.MaPhu,
TenKH =
kh.TenKH,
DienThoai =
kh.DienThoai,
kh.EmailKH,
DiaChi =
kh.DiaChi,
kh.MaMB,
NoDauKy =
(ndk == null ? 0 : ndk.PhaiThu.GetValueOrDefault()) - (sqdk == null ? 0 :
sqdk.NoDauKy),
PhatSinh =
ps == null ? 0 : ps.PhaiThu,
DaThu = dt
== null ? 0 : dt.DaThu,
KhauTru = kt
== null ? 0 : kt.KhauTru,
ThuTruoc =
tt == null ? 0 : tt.ThuTruoc,
ThuTruocTK =
tttk == null ? 0 : tttk.ThuTruoc,
}).Select(p
=> new
{
ThuTruoc =
p.ThuTruoc,
NoDauKy =
p.NoDauKy < 0 ? 0 : p.NoDauKy,
PhatSinh =
p.PhatSinh,
KhauTru =
p.KhauTru,
DaThu =
p.DaThu,
//ConNo =
((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu + p.KhauTru -
p.ThuTruocTK)) < 0 ? 0 : ((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh -
(p.DaThu + p.KhauTru - p.ThuTruocTK)),
ConNo =
((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu + p.KhauTru)) <
0 ? 0 : ((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu +
p.KhauTru)),
MaKH =
p.MaKH,
KyHieu =
p.KyHieu,
MaPhu =
p.MaPhu,
TenKH =
p.TenKH,
DienThoai =
p.DienThoai,
EmailKH =
p.EmailKH,
DiaChi =
p.DiaChi,
MaMB =
p.MaMB,
//NoCuoi =
(((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu + p.KhauTru -
p.ThuTruocTK)) < 0 ? 0 : ((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh -
(p.DaThu + p.KhauTru - p.ThuTruocTK))) - p.ThuTruoc
//NoCuoi =
(((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh - (p.DaThu + p.KhauTru -
p.ThuTruocTK)) < 0 ? 0 : ((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh -
(p.DaThu + p.KhauTru))) - p.ThuTruoc
NoCuoi =
((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh - p.DaThu - p.KhauTru +
p.ThuTruocTK) < 0 ? 0 : ((p.NoDauKy < 0 ? 0 : p.NoDauKy) + p.PhatSinh -
p.DaThu - p.KhauTru + p.ThuTruocTK) - p.ThuTruoc,
p.ThuTruocTK
});
gcHoaDon.DataSource = objList;
#endregion
}
else
{
itemNap.Visibility =
DevExpress.XtraBars.BarItemVisibility.Never;
itemThuTien.Visibility =
DevExpress.XtraBars.BarItemVisibility.Never;
itemPrint.Visibility =
DevExpress.XtraBars.BarItemVisibility.Never;
itemSendMail.Visibility =
DevExpress.XtraBars.BarItemVisibility.Never;
itemKhauTuDong.Visibility =
DevExpress.XtraBars.BarItemVisibility.Never;
itemExport.Visibility =
DevExpress.XtraBars.BarItemVisibility.Never;
itemPrintAll.Visibility =
DevExpress.XtraBars.BarItemVisibility.Never;
threads = new
Library.Utilities.ThreadCls(8, GetTask, GetData);
threads.RunThread();
itemNap.Visibility =
DevExpress.XtraBars.BarItemVisibility.Always;
itemThuTien.Visibility =
DevExpress.XtraBars.BarItemVisibility.Always;
itemPrint.Visibility =
DevExpress.XtraBars.BarItemVisibility.Always;
itemSendMail.Visibility =
DevExpress.XtraBars.BarItemVisibility.Always;
itemKhauTuDong.Visibility =
DevExpress.XtraBars.BarItemVisibility.Always;
itemExport.Visibility =
DevExpress.XtraBars.BarItemVisibility.Always;
itemPrintAll.Visibility =
DevExpress.XtraBars.BarItemVisibility.Always;
}
#endregion
Detail();
}
Không có nhận xét nào:
Đăng nhận xét
Cám ơn bạn đã quan tâm đến bài viết của mình nhé!