private void Form2_Load(object sender, EventArgs e) { BHXH.HR.UI.Report.CrystalReportLyLich2C cr1 = new BHXH.HR.UI.Report.CrystalReportLyLich2C(); BHXH.Data.BHXHEntities ctx = new Data.BHXHEntities(); var result = (from a in ctx.HrNhanVien join cvdang in ctx.ListChucVuDang on a.MaCapUy equals cvdang.MaChucVu join cvdangkiem in ctx.ListChucVuDang on a.MaCapUyKiem equals cvdangkiem.MaChucVu join cv in ctx.ListChucVu on a.MaChucVu equals cv.MaChucVu ///////quá trình đào tạo join b in ctx.HrChiTietDaoTao on a.MaNhanVien equals b.MaNhanVien join lh in ctx.ListLHDaoTao on b.MaLHDaoTao equals lh.MaLHDaoTao join bc in ctx.ListBangCap on b.MaBangCap equals bc.MaBangCap ///////quá trình công tác // join c in ctx.HrQuaTrinhCongTac on a.MaNhanVien equals c.MaNhanVien where b.MaNhanVien.ToString() == "706f5bff-d372-412a-9a17-21cfb316c993" //BHXH.HR.UI.frmHrNhanVien.bientoancuc.MaNhanVien.ToString() select new { a.TenNhanVien, a.GioiTinh, a.TenKhac, TenChucVuDang = cvdang.TenChucVu, TenChucVuDangKiem = cvdangkiem.TenChucVu, TenChucVu = cv.TenChucVu, a.PCChucVu, /////đào tạo b.TenTruong, b.NganhHoc, b.ThoiGian, TenLHDaoTao = lh.TenLHDaoTao, TenBangCap = bc.TenBangCap, /////qt công tác // ThoiGianCongTac = c.ThoiGian, //c.CongViec, } ).ToList(); this.GridControlDSNhanVien.DataSource = result; }
private void crystalReportViewer1_Load(object sender, EventArgs e) { DataSet ds = new DataSet(); DataTable MyDataTableNhanVien = ds.Tables.Add("HrNhanVien"); DataTable MyDataTableCTDaoTao = ds.Tables.Add("HrChiTietDaoTao"); DataTable MyDataTableQTCongTac = ds.Tables.Add("HrQuaTrinhCongTac"); DataTable MyDataTableQHGD = ds.Tables.Add("HrQuanHeGiaDinh"); // DataTable MyDataTable = new DataTable(); //DataTable MyDataTableNhanVien = new DataTable(); ////////////////////////////////////////////////// MyDataTableNhanVien.Columns.Add("TenNhanVien", typeof(string)); MyDataTableNhanVien.Columns.Add("SoHieu", typeof(string)); MyDataTableNhanVien.Columns.Add("GioiTinh", typeof(string)); MyDataTableNhanVien.Columns.Add("TenKhac", typeof(string)); MyDataTableNhanVien.Columns.Add("TenChucVuDang", typeof(string)); MyDataTableNhanVien.Columns.Add("TenChucVuDangKiem", typeof(string)); MyDataTableNhanVien.Columns.Add("TenChucVu", typeof(string)); MyDataTableNhanVien.Columns.Add("PCCV", typeof(string)); MyDataTableNhanVien.Columns.Add("NgaySinh", typeof(string)); MyDataTableNhanVien.Columns.Add("NoiSinh", typeof(string)); MyDataTableNhanVien.Columns.Add("QueQuan", typeof(string)); MyDataTableNhanVien.Columns.Add("TruQuan", typeof(string)); MyDataTableNhanVien.Columns.Add("DTNha", typeof(string)); MyDataTableNhanVien.Columns.Add("DTDiDong", typeof(string)); MyDataTableNhanVien.Columns.Add("DanToc", typeof(string)); MyDataTableNhanVien.Columns.Add("TonGiao", typeof(string)); MyDataTableNhanVien.Columns.Add("ThanhPhanGD", typeof(string)); MyDataTableNhanVien.Columns.Add("NgheTruocTD", typeof(string)); MyDataTableNhanVien.Columns.Add("NgayTD", typeof(string)); MyDataTableNhanVien.Columns.Add("CoQuanTD", typeof(string)); MyDataTableNhanVien.Columns.Add("NgayCQ", typeof(string)); MyDataTableNhanVien.Columns.Add("NgayCM", typeof(string)); MyDataTableNhanVien.Columns.Add("NgayDang", typeof(string)); MyDataTableNhanVien.Columns.Add("NgayDangCT", typeof(string)); MyDataTableNhanVien.Columns.Add("NgayTCXH", typeof(string)); MyDataTableNhanVien.Columns.Add("NgayNhapNgu", typeof(string)); MyDataTableNhanVien.Columns.Add("NgayXuatNgu", typeof(string)); MyDataTableNhanVien.Columns.Add("QuanHam", typeof(string)); MyDataTableNhanVien.Columns.Add("TrinhDoVanHoa", typeof(string)); MyDataTableNhanVien.Columns.Add("TrinhDoDaoTao", typeof(string)); MyDataTableNhanVien.Columns.Add("TrinhDoLLCT", typeof(string)); MyDataTableNhanVien.Columns.Add("TrinhDoNgoaiNgu", typeof(string)); MyDataTableNhanVien.Columns.Add("CVDangLam", typeof(string)); MyDataTableNhanVien.Columns.Add("Ngach", typeof(string)); MyDataTableNhanVien.Columns.Add("BacLuong", typeof(string)); MyDataTableNhanVien.Columns.Add("HSL", typeof(double)); MyDataTableNhanVien.Columns.Add("DHDuocPhong", typeof(string)); MyDataTableNhanVien.Columns.Add("SoTruongCT", typeof(string)); MyDataTableNhanVien.Columns.Add("CVLamLN", typeof(string)); MyDataTableNhanVien.Columns.Add("KhenThuong", typeof(string)); MyDataTableNhanVien.Columns.Add("KyLuat", typeof(string)); MyDataTableNhanVien.Columns.Add("SucKhoe", typeof(string)); MyDataTableNhanVien.Columns.Add("ChieuCao", typeof(string)); MyDataTableNhanVien.Columns.Add("CanNang", typeof(string)); MyDataTableNhanVien.Columns.Add("NhomMau", typeof(string)); MyDataTableNhanVien.Columns.Add("SoCMND", typeof(string)); MyDataTableNhanVien.Columns.Add("TenLoaiTB", typeof(string)); MyDataTableNhanVien.Columns.Add("GDLietSy", typeof(string)); MyDataTableNhanVien.Columns.Add("TuDay", typeof(string)); MyDataTableNhanVien.Columns.Add("CheDoCu", typeof(string)); MyDataTableNhanVien.Columns.Add("TCNuocNgoai", typeof(string)); MyDataTableNhanVien.Columns.Add("TNNuocNgoai", typeof(string)); /////quá trình đào tạo MyDataTableCTDaoTao.Columns.Add("TenTruong", typeof(string)); MyDataTableCTDaoTao.Columns.Add("NganhHoc", typeof(string)); MyDataTableCTDaoTao.Columns.Add("ThoiGian", typeof(string)); MyDataTableCTDaoTao.Columns.Add("TenLHDaoTao", typeof(string)); MyDataTableCTDaoTao.Columns.Add("TenBangCap", typeof(string)); /////quá trình công tác MyDataTableQTCongTac.Columns.Add("ThoiGianCongTac", typeof(string)); MyDataTableQTCongTac.Columns.Add("CongViec", typeof(string)); /////quan hệ gia đình MyDataTableQHGD.Columns.Add("TenQuanHeGD", typeof(string)); MyDataTableQHGD.Columns.Add("HoTen", typeof(string)); MyDataTableQHGD.Columns.Add("NamSinh", typeof(string)); MyDataTableQHGD.Columns.Add("ChiTiet", typeof(string)); ////////////////Nguồn BHXH.HR.UI.Report.CrystalReportLyLich2C cr1 = new BHXH.HR.UI.Report.CrystalReportLyLich2C(); BHXH.Data.BHXHEntities ctx = new Data.BHXHEntities(); var resultHrNhanVien = (from a in ctx.HrNhanVien join cvdang in ctx.ListChucVuDang on a.MaCapUy equals cvdang.MaChucVu join cvdangkiem in ctx.ListChucVuDang on a.MaCapUyKiem equals cvdangkiem.MaChucVu join cv in ctx.ListChucVu on a.MaChucVu equals cv.MaChucVu join PhuongQQ in ctx.ListXaPhuong on a.XaPhuongIDQQ equals PhuongQQ.XaPhuongID join HuyenQQ in ctx.ListQuanHuyen on a.QuanHuyenIDQQ equals HuyenQQ.QuanHuyenID join TinhQQ in ctx.ListTinh on a.MaTinhQQ equals TinhQQ.MaTinh join PhuongTQ in ctx.ListXaPhuong on a.XaPhuongIDTQ equals PhuongTQ.XaPhuongID join HuyenTQ in ctx.ListQuanHuyen on a.QuanHuyenIDTQ equals HuyenTQ.QuanHuyenID join TinhTQ in ctx.ListTinh on a.MatinhTQ equals TinhTQ.MaTinh join dt in ctx.ListDanToc on a.MaDanToc equals dt.MaDanToc join tg in ctx.ListTonGiao on a.MaTonGiao equals tg.MaTonGiao join tpgd in ctx.ListThanhPhanGD on a.MaThanhPhanGD equals tpgd.MaThanhPhanGD join tdvh in ctx.ListTDVanHoa on a.MaTDVanHoa equals tdvh.MaTDVanHoa join tddt in ctx.ListTDDaoTao on a.MaTDDaoTao equals tddt.MaTDDaoTao join tdllct in ctx.ListTDLLCT on a.MaTDLLCT equals tdllct.MaTDLLCT join tdnn in ctx.ListTDNgoaiNgu on a.MaTDNgoaiNgu equals tdnn.MaTDNgoaiNgu join ngach in ctx.HrThangBangLuong on a.MaNgach equals ngach.MaNgach join tb in ctx.ListLoaiTB on a.MaLoaiTB equals tb.MaLoaiTB where a.MaNhanVien.ToString() == BHXH.HR.UI.frmHrNhanVien.bientoancuc.MaNhanVien.ToString() select new { a.TenNhanVien, a.SoHieuNV, a.GioiTinh, a.TenKhac, TenChucVuDang = cvdang.TenChucVu, TenChucVuDangKiem = cvdangkiem.TenChucVu, TenChucVu = cv.TenChucVu, a.PCChucVu, NgaySinh = a.NgaySinh, //NgaySinh = a.NgaySinh,//.Substring(0,2) + " tháng " + a.NgaySinh.ToString().Substring(3, 2) +" năm " + a.NgaySinh.ToString().Substring(6,4), NoiSinh = a.NoiSinh, Quequan = PhuongQQ.TenXaPhuong + ", " + HuyenQQ.TenQuanHuyen + ", " + TinhQQ.TenTinh, TruQuan = PhuongTQ.TenXaPhuong + ", " + HuyenTQ.TenQuanHuyen + ", " + TinhTQ.TenTinh, a.DTNha, a.DTDiDong, dt.TenDanToc, tg.TenTonGiao, tpgd.TenThanhPhanGD, a.NgheTruocTD, a.NgayTD, a.CoQuanTD, a.NgayCQ, a.NgayCM, a.NgayDang, a.NgayDangCT, a.NgayTCXH, a.NgayNhapNgu, a.NgayXuatNgu, a.QuanHam, a.MaTDVanHoa, tdvh.TenTDVanHoa, tddt.TenTDDaoTao, tdllct.TenTDLLCT, tdnn.TenTDNgoaiNgu, a.CVDangLam, NgachCC = a.MaNgach + "-" + ngach.TenNgach, a.Bac, a.HSL, a.DHDuocPhong, a.SoTruongCT, a.CVLamLN, a.KhenThuong, a.KyLuat, a.SucKhoe, a.ChieuCao, a.CanNang, a.NhomMau, a.SoCMND, tb.TenLoaiTB, a.GDLietSy, a.TuDay, a.CheDoCu, a.TCNuocNgoai, a.TNNuocNgoai, } ).ToList(); ///////////////////////////truyền dữ liệu /////////////////////////// foreach (var item_loopVariable in resultHrNhanVien) { dynamic row = MyDataTableNhanVien.NewRow(); row["TenNhanVien"] = item_loopVariable.TenNhanVien.ToUpper(); row["SoHieu"] = item_loopVariable.SoHieuNV; row["GioiTinh"] = item_loopVariable.GioiTinh; row["TenKhac"] = item_loopVariable.TenKhac; row["TenChucVuDang"] = item_loopVariable.TenChucVuDang; row["TenChucVuDangKiem"] = item_loopVariable.TenChucVuDangKiem; row["TenChucVu"] = item_loopVariable.TenChucVu; if (item_loopVariable.PCChucVu != null) { row["PCCV"] = Convert.ToDecimal(item_loopVariable.PCChucVu); } row["NgaySinh"] = Convert.ToDateTime(item_loopVariable.NgaySinh).Day + " tháng " + Convert.ToDateTime(item_loopVariable.NgaySinh).Month + " năm " + Convert.ToDateTime(item_loopVariable.NgaySinh).Year; row["NoiSinh"] = item_loopVariable.NoiSinh; row["QueQuan"] = item_loopVariable.Quequan; row["TruQuan"] = item_loopVariable.TruQuan; row["DTNha"] = item_loopVariable.DTNha; row["DTDiDong"] = item_loopVariable.DTDiDong; row["DanToc"] = item_loopVariable.TenDanToc; row["TonGiao"] = item_loopVariable.TenTonGiao; row["ThanhPhanGD"] = item_loopVariable.TenThanhPhanGD; row["NgheTruocTD"] = item_loopVariable.NgheTruocTD; row["NgayTD"] = Convert.ToDateTime(item_loopVariable.NgayTD).ToShortDateString(); row["CoQuanTD"] = item_loopVariable.CoQuanTD; row["NgayCQ"] = Convert.ToDateTime(item_loopVariable.NgayCQ).ToShortDateString(); if (item_loopVariable.NgayCM != null) { row["NgayCM"] = Convert.ToDateTime(item_loopVariable.NgayCM).ToShortDateString(); } if (item_loopVariable.NgayDang != null) { row["NgayDang"] = Convert.ToDateTime(item_loopVariable.NgayDang).ToShortDateString(); } if (item_loopVariable.NgayDangCT != null) { row["NgayDangCT"] = Convert.ToDateTime(item_loopVariable.NgayDangCT).ToShortDateString(); } if (item_loopVariable.NgayTCXH != null) { row["NgayTCXH"] = Convert.ToDateTime(item_loopVariable.NgayTCXH).ToShortDateString(); } if (item_loopVariable.NgayNhapNgu != null) { row["NgayNhapNgu"] = Convert.ToDateTime(item_loopVariable.NgayNhapNgu).ToShortDateString(); } if (item_loopVariable.NgayXuatNgu != null) { row["NgayXuatNgu"] = Convert.ToDateTime(item_loopVariable.NgayXuatNgu).ToShortDateString(); } row["QuanHam"] = item_loopVariable.QuanHam; row["TrinhDoVanHoa"] = item_loopVariable.TenTDVanHoa; row["TrinhDoDaoTao"] = item_loopVariable.TenTDDaoTao; row["TrinhDoLLCT"] = item_loopVariable.TenTDLLCT; row["TrinhDoNgoaiNgu"] = item_loopVariable.TenTDNgoaiNgu; row["CVDangLam"] = item_loopVariable.CVDangLam; row["Ngach"] = item_loopVariable.NgachCC; row["BacLuong"] = item_loopVariable.Bac; row["HSL"] = Convert.ToDecimal(item_loopVariable.HSL); row["DHDuocPhong"] = item_loopVariable.DHDuocPhong; row["SoTruongCT"] = item_loopVariable.SoTruongCT; row["CVLamLN"] = item_loopVariable.CVLamLN; row["KhenThuong"] = item_loopVariable.KhenThuong; row["KyLuat"] = item_loopVariable.KyLuat; row["SucKhoe"] = item_loopVariable.SucKhoe; row["ChieuCao"] = item_loopVariable.ChieuCao; row["CanNang"] = item_loopVariable.CanNang; row["NhomMau"] = item_loopVariable.NhomMau; row["SoCMND"] = item_loopVariable.SoCMND; row["TenLoaiTB"] = item_loopVariable.TenLoaiTB; row["GDLietSy"] = Convert.ToBoolean(item_loopVariable.GDLietSy); row["TuDay"] = item_loopVariable.TuDay; row["CheDoCu"] = item_loopVariable.CheDoCu; row["TCNuocNgoai"] = item_loopVariable.TCNuocNgoai; row["TNNuocNgoai"] = item_loopVariable.TNNuocNgoai; MyDataTableNhanVien.Rows.Add(row); } ////////////////////Nguồn chi tiết đào tạo//////////////////////////////////////////////////// var resultChiTietDaoTao = (from a in ctx.HrNhanVien join b in ctx.HrChiTietDaoTao on a.MaNhanVien equals b.MaNhanVien join lh in ctx.ListLHDaoTao on b.MaLHDaoTao equals lh.MaLHDaoTao join bc in ctx.ListBangCap on b.MaBangCap equals bc.MaBangCap where a.MaNhanVien.ToString() == BHXH.HR.UI.frmHrNhanVien.bientoancuc.MaNhanVien.ToString() orderby b.STT select new { b.TenTruong, b.NganhHoc, b.ThoiGian, TenLHDaoTao = lh.TenLHDaoTao, TenBangCap = bc.TenBangCap, } ).ToList(); ////////////////////////////////////////////////////// foreach (var item_loopVariable in resultChiTietDaoTao) { dynamic row = MyDataTableCTDaoTao.NewRow(); row["TenTruong"] = item_loopVariable.TenTruong; row["NganhHoc"] = item_loopVariable.NganhHoc; row["ThoiGian"] = item_loopVariable.ThoiGian; row["TenLHDaoTao"] = item_loopVariable.TenLHDaoTao; row["TenBangCap"] = item_loopVariable.TenBangCap; MyDataTableCTDaoTao.Rows.Add(row); } ///////////////////////////nguồn quá trình công tác///////////////////////////////////// var resultQTCT = (from a in ctx.HrNhanVien join b in ctx.HrQuaTrinhCongTac on a.MaNhanVien equals b.MaNhanVien where b.MaNhanVien.ToString() == BHXH.HR.UI.frmHrNhanVien.bientoancuc.MaNhanVien.ToString() orderby b.STT select new { ThoiGianCongTac = b.ThoiGian, b.CongViec } ).ToList(); foreach (var item_loopVariable in resultQTCT) { dynamic row = MyDataTableQTCongTac.NewRow(); row["ThoiGianCongTac"] = item_loopVariable.ThoiGianCongTac; row["CongViec"] = item_loopVariable.CongViec; MyDataTableQTCongTac.Rows.Add(row); } ///////////////////////////nguồn quan hệ gia đình///////////////////////////////////// var resultQHGD = (from a in ctx.HrNhanVien join b in ctx.HrQuanHeGiaDinh on a.MaNhanVien equals b.MaNhanVien join c in ctx.ListQuanHeGD on b.MaQuanHeGD equals c.MaQuanHeGD where b.MaNhanVien.ToString() == BHXH.HR.UI.frmHrNhanVien.bientoancuc.MaNhanVien.ToString() orderby b.STT select new { c.TenQuanHeGD, b.HoTen, b.NamSinh, b.ChiTiet, } ).ToList(); foreach (var item_loopVariable in resultQHGD) { dynamic row = MyDataTableQHGD.NewRow(); row["TenQuanHeGD"] = item_loopVariable.TenQuanHeGD; row["HoTen"] = item_loopVariable.HoTen; row["NamSinh"] = item_loopVariable.NamSinh; row["ChiTiet"] = item_loopVariable.ChiTiet; MyDataTableQHGD.Rows.Add(row); } ////////////////////////////////////////////////////////////////////////////// CrystalDecisions.CrystalReports.Engine.ReportDocument rptDoc = default(CrystalDecisions.CrystalReports.Engine.ReportDocument); rptDoc = new BHXH.HR.UI.Report.CrystalReportLyLich2C(); rptDoc.DataSourceConnections.Clear(); rptDoc.SetDataSource(MyDataTableNhanVien); /////////////////////// CrystalDecisions.CrystalReports.Engine.ReportDocument rptDocSubCTDT = default(CrystalDecisions.CrystalReports.Engine.ReportDocument); rptDocSubCTDT = new BHXH.HR.UI.Report.SubReportLyLich2CCTDT(); rptDoc.Subreports[0].DataSourceConnections.Clear(); rptDoc.Subreports[0].SetDataSource(MyDataTableCTDaoTao); ////////////////////////// CrystalDecisions.CrystalReports.Engine.ReportDocument rptDocSubQTCT = default(CrystalDecisions.CrystalReports.Engine.ReportDocument); rptDocSubQTCT = new BHXH.HR.UI.Report.SubReportLyLich2CQTCT(); rptDoc.Subreports[1].DataSourceConnections.Clear(); rptDoc.Subreports[1].SetDataSource(MyDataTableQTCongTac); ////////////////////////// CrystalDecisions.CrystalReports.Engine.ReportDocument rptDocSubQHGD = default(CrystalDecisions.CrystalReports.Engine.ReportDocument); rptDocSubQHGD = new BHXH.HR.UI.Report.SubReportLyLich2CQHGD(); rptDoc.Subreports[2].DataSourceConnections.Clear(); rptDoc.Subreports[2].SetDataSource(MyDataTableQHGD); ////////////////////////////// this.crystalReportViewer1.ReportSource = rptDoc; }