C# QueryBuilder.addGroupBy方法代码示例

        private void btnView_Click(object sender, EventArgs e)
        {
            var sql =
                string.Format(
                    @"select right(  '00'||extract(hour from ct.time_start),2) ||':00 '|| ' - ' ||right('00'||(extract(hour from ct.time_start)  +1),2)    ||':00' VET,
            kp.name kenh_phat,avg(ct.rate_group)
            from tns_programs_grid_scr_ct ct
            left join tns_kenh_phat kp on ct.kenh_phat=kp.id
            where 1=1");
            var query = new QueryBuilder(sql);

            AppCtrl.addID(query, "ct.PGS_id", FileNguon);
            AppCtrl.addID(query, "CT.KENH_PHAT", KenhPhat);
            query.addDateFromTo("CT.NGAY_PHAT", TuNgay.DateTime, DenNgay.DateTime);

            query.addGroupBy(@"right(  '00'||extract(hour from ct.time_start),2) ||':00 '|| ' - ' ||right('00'||(extract(hour from ct.time_start)  +1),2)    ||':00',
            kp.name");
            query.setAscOrderBy("ct.time_start, ct.kenh_phat");

            var ds = HelpDB.getDBService().LoadDataSet(query, KE_HOACH_LPS_CT.TABLE_NAME);
            if (ds != null && ds.Tables.Count > 0)
            {
                var dt = ds.Tables[0];
                var dtS = dt.DefaultView.ToTable(true, "VET");

            }

            gridControlDetail.DataSource = ds.Tables[0];
        }

 public DataSet DsChamCongNhanVien(string strThangNam,string Table)
 {
     string[] M = strThangNam.Split('/');
     int mm = HelpNumber.ParseInt32(M[0]);
     int yy = HelpNumber.ParseInt32(M[1]);
     QueryBuilder query = new QueryBuilder(@"SELECT NV_ID FROM " + Table + " WHERE 1=1" );
     query.addDateFromTo("NGAY", HelpDate.GetStartOfMonth(mm, yy), HelpDate.GetEndOfMonth(mm, yy));
     query.addGroupBy("NV_ID");
     DataSet ds = HelpDB.getDatabase().LoadDataSet(query, Table);
     return ds;
 }

        private void btnView_Click(object sender, EventArgs e)
        {
            var sql = string.Format(
                @"select ct.time_start ||' - '|| ct.time_end timeline,ct.kenh_phat, dth.name kenh_phat_name, avg(ct.rate_group) RATE,
               list( distinct nsx.name,'; ') nuoc_sx,
               list( distinct c.noi_dung,'; ') chuong_trinh
               from tns_programs_grid_scr_ct  ct
               inner join tns_programs_grid_scr t on t.pgs_id=ct.pgs_id
               left join tns_chuong_trinh c on c.tct_id=ct.chuong_trinh
               left join tns_nuoc_sx nsx on ct.nuoc_sx=nsx.id
               left join dm_dai_truyen_hinh dth on dth.id=ct.kenh_phat
               where  (t.is_delete='N' or t.is_delete is null) and  ct.chuong_trinh is not null and 1=1");
            var query = new QueryBuilder(sql);
            AppCtrl.addID(query, "CT.PGS_id", FileNguon);
            AppCtrl.addID(query, "CT.KENH_PHAT", KenhPhat);
            query.addDateFromTo("CT.NGAY_PHAT", TuNgay.DateTime, DenNgay.DateTime);

            var minStart = TimeSpanHelper.Parse(TuVet.EditValue.ToString()).TotalMinutes;
            var minEnd = TimeSpanHelper.Parse(DenVet.EditValue.ToString()).TotalMinutes;
            query.add("CT.MINS_START", Operator.GreaterEqual, minStart, DbType.Decimal);
            query.add("CT.MINS_END", Operator.LessEqual, minEnd, DbType.Decimal);

            query.addGroupBy(@"ct.time_start ||' - '|| ct.time_end, ct.kenh_phat,dth.name");
            query.addCondition("1=1");

            var ds = HelpDB.getDBService().LoadDataSet(query, KE_HOACH_LPS_CT.TABLE_NAME);
            RemoveCols();
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dtSource = ds.Tables[0];
                DataTable dtKenh = dtSource.DefaultView.ToTable(true, "kenh_phat", "kenh_phat_name");
                GenColumn(dtKenh);
                DataTable dtRp = CreateReportTable(dtSource, dtKenh);
                gridControlDetail.DataSource = dtRp;
            }
        }

        public void InitDanhSachThangTinhLuongLeft()
        {
            #region Lấy dữ liệu
            QueryBuilder query = new QueryBuilder("select thang_nam , right(thang_nam,4) as nam, is_chot from bang_luong where 1=1");
            query.setDescOrderBy("THANG_NAM");
            query.addGroupBy("THANG_NAM,IS_CHOT");

            DataSet ds = HelpDB.getDatabase().LoadDataSet(query, "BANG_LUONG");
            DataTable Kq = new XLSortThangNam().Sort(ds.Tables[0]);
            treeListBangLuong.ParentFieldName = "NAM";
            query = new QueryBuilder("select right(thang_nam,4) as nam from bang_luong where 1=1");
            query.setDescOrderBy("NAM");
            query.addGroupBy("NAM");

            DataSet dsyy = DABase.getDatabase().LoadDataSet(query, "BANG_LUONG");

            foreach (DataRow dong in dsyy.Tables[0].Rows)
            {
                TreeListNode n = treeListBangLuong.AppendNode(new object[] { "Năm " + dong["NAM"].ToString(), "N" }, -1);
                foreach (DataRow item in Kq.Rows)
                {
                    if (HelpNumber.ParseInt32(item["NAM"]) == HelpNumber.ParseInt32(dong["NAM"]))
                    {
                        treeListBangLuong.AppendNode(new object[] { item["THANG_NAM"], item["IS_CHOT"] }, n.Id);
                        strThangNam.Append(item["THANG_NAM"].ToString() + ",");
                    }
                }
            }
            if (treeListBangLuong.FocusedNode != null)
            {
                barButtonItemChot.Enabled = treeListBangLuong.FocusedNode.GetDisplayText("THANG_NAM").Contains("Năm") ? false : true;
                strBangLuong = treeListBangLuong.FocusedNode.FirstNode.GetValue("THANG_NAM").ToString();
                treeListBangLuong.SetFocusedNode(treeListBangLuong.FocusedNode.FirstNode);
            }
            treeListBangLuong.ExpandAll();

            #endregion

            #region Tạo sự kiện
            treeListBangLuong.FocusedNodeChanged += new FocusedNodeChangedEventHandler(treeList1_FocusedNodeChanged);
            #endregion
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {

                string Month = this.plMonth._timeEdit.Text;
                if (strThangNam.ToString().Contains("," + Month + ","))
                {
                    HelpMsgBox.ShowNotificationMessage(string.Format("Bảng lương của tháng \"{0}\" đã được tạo, vui lòng chọn tháng khác!", Month));
                    return;
                }
                Application.DoEvents();
                FrameworkParams.wait = new FWWaitingMsg();
                ///1.Lấy danh sách nhân viên trong bảng chấm công tự động
                DataSet dsEmployee = DABangLuong.Instance.GetEmployeeChamCongAuto(Month);
                List<string> l = new List<string>();
                foreach (DataRow row in dsEmployee.Tables[0].Rows)
                {
                    ///2.Lấy thông tin tạm ứng của nhân viên trong bảng tạm ứng của tháng cần tính lương
                    DOBangLuong phieu = DABangLuong.Instance.LoadAll(HelpNumber.ParseInt64(row["NV_ID"]), Month);
                    //phieu.TAM_UNG = DABangLuong.Instance.GetSoTienTamUng(HelpNumber.ParseInt64(row["NV_ID"]), Month); //CHAUTV : Chuyển qua nút Tính lương

                    phieu.NV_ID = HelpNumber.ParseInt64(row["NV_ID"]);
                    l.Add(phieu.NV_ID.ToString());
                    phieu.THANG_NAM = Month;
                    phieu.IS_CHOT = "N";
                    ///3.Tạo dữ liệu trống cho bảng lương
                    DABangLuong.Instance.Update(phieu);
                    ///4.Cập nhật đã chuyển qua bảng lương cho dữ liệu tạm ứng (Chuyển qua nút Tính lương)
                    //DAPhieuTamUng.I.UpdatePhieuDuyet(phieu.NV_ID, phieu.THANG_NAM);
                }
                //Add các nhân viên có tạm ứng được duyệt mà không có trong bảng chấm công
                string notIn = "";
                if (l.Count > 0) {
                    for (int i = 0; i < l.Count-1; i++)
                    {
                        notIn += l[i] + ",";
                    }
                    notIn += l[l.Count - 1];
                }
                //BỔ SUNG
                if (notIn == "") notIn = "-1";
                QueryBuilder query = new QueryBuilder("SELECT NGUOI_DE_NGHI_ID FROM PHIEU_TAM_UNG WHERE 1=1");
                query.addCondition("(NGUOI_DE_NGHI_ID not in (" + notIn + "))");
                query.addCondition("DUYET='2'");
                query.add("THANG_TAM_UNG", Operator.Equal, Month, DbType.String);
                query.addGroupBy("NGUOI_DE_NGHI_ID");
                DataSet ds = HelpDB.getDatabase().LoadDataSet(query);
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    DOBangLuong phieu = DABangLuong.Instance.LoadAll(HelpNumber.ParseInt64(item["NGUOI_DE_NGHI_ID"]), Month);
                    phieu.NV_ID = HelpNumber.ParseInt64(item["NGUOI_DE_NGHI_ID"]);
                    phieu.THANG_NAM = Month;
                    phieu.IS_CHOT = "N";
                    DABangLuong.Instance.Update(phieu);
                }
                PLGUIUtil.ClosePhieu(this, true);
            }
            catch (Exception ex)
            {
                HelpSysLog.AddException(ex, "Tạo/Cập nhật bảng lương.");
                ErrorMsg.ErrorSave("Lưu không thành công.");
            }
            finally {
                if (FrameworkParams.wait != null) FrameworkParams.wait.Finish();
            }
        }

 public DataTable GetNhanVien(long IDs)
 {
     QueryBuilder query = new QueryBuilder(@"SELECT NV_ID FROM BANG_CHAM_CONG_AUTO WHERE 1=1");
     query.addGroupBy("NV_ID");
     query.addID("NV_ID", IDs);
     DataSet ds = HelpDB.getDatabase().LoadDataSet(query);
     return ds.Tables[0];
 }

        /// <summary>Step 5: Xây dựng Query Buider cho việc tìm kiếm
        /// Xây dựng một QueryBuilder từ những chọn lựa trong phần filter.
        /// Từ QueryBuilder này ta có thể lấy được dữ liệu thỏa điều kiện.
        /// Nếu hỗ trợ duyệt thì trong câu truy vấn trả về 
        /// phải có cột là DUYET_BIT
        /// </summary>
        public override QueryBuilder PLBuildQueryFilter()
        {
            var query = new QueryBuilder(UpdateRow());
            if (!string.IsNullOrEmpty(TenCT.Text.Trim()))
            {
                query.addCondition(string.Format(@"(lower(ct.{0}) like '%{1}%' or lower(lps.{2}) like '%{1}%' )",
                    CHUONG_TRINH.NOI_DUNG, TenCT.Text.ToLower(), QL_LICH_PHAT_SONG.TEN_CT));
            }
            if (!string.IsNullOrEmpty(Category.Text.Trim()))
            {
                query.addCondition(string.Format(@"(lower(ct.{0}) like '%{1}%' or lower(lps.{2}) like '%{1}%' )",
                 CHUONG_TRINH.CATEGORY, Category.Text.ToLower(), QL_LICH_PHAT_SONG.CATEGORY));
            }
            if (TietMuc._IsNotCheckAnyOrCheckedAll == false)
            {
                query.addCondition(string.Format(@"(ct.{0} in {1} or lps.{2} in {1} )", CHUONG_TRINH.TIET_MUC,
                    TietMuc._getStrSelectedIDs(), QL_LICH_PHAT_SONG.TIET_MUC));
            }
            if (PostMaster._IsNotCheckAnyOrCheckedAll == false)
            {
                query.addCondition(string.Format(@"(CTPM.{0} in {1} or lps.{2} in {1} )",
                   CHUONG_TRINH_POST_MASTER.POST_MASTER_ID, PostMaster._getStrSelectedIDs(), QL_LICH_PHAT_SONG.POST_MASTER)
                  );
            }
            AppCtrl.addID(query, "LPS." + QL_LICH_PHAT_SONG.KENH_PHAT, KenhPhat);

            string fieldGio = string.Format("right( '00'|| extract(hour from lpsct.{0}),2)||':' ||right( '00'|| extract(minute from lpsct.{0}),2)", QL_LICH_PHAT_SONG_CT.GIO_PHAT_SONG);

            if (GioPhatTu.EditValue != null && GioPhatTu.Text != "")
            {
                query.addCondition(string.Format(@"({0}>='{1}')", fieldGio, GioPhatTu.Text));
            }

            if (GioPhatDen.EditValue != null && GioPhatDen.Text != "")
            {
                query.addCondition(string.Format(@"({0}<='{1}')", fieldGio, GioPhatDen.Text));
            }
            if (ThongTinThem.CheckedItems.Count == 1)
            {
                query.addIn("LPS." + QL_LICH_PHAT_SONG.IS_PHAT_LAI, AppCtrl.GetCheckedValues(ThongTinThem, true));
            }
            AppCtrl.addFilterFromTo(query, "LPSCT." + QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG, NgayBatDauPhat);
            AppCtrl.addFilterFromTo(query, "LPS." + QL_LICH_PHAT_SONG.NGAY_TAO, ngayNhap);
            AppCtrl.addID(query, "LPS." + QL_LICH_PHAT_SONG.NGUOI_TAO, NguoiNhap);
            AppCtrl.addID(query, "LPS." + QL_LICH_PHAT_SONG.PHONG_BAN, PhongBan);

            long[] ds = ThuTrongTuan._getSelectedIDs();
            if (ThuTrongTuan._IsNotCheckAnyOrCheckedAll == false)
            {
                string cond = ds.Aggregate("(", (current, id) => current + ("LPS." + QL_LICH_PHAT_SONG.THU_TRONG_TUAN + " like '%," + id + ",%' or "));
                cond = cond.TrimEnd(' ', 'r', 'o');
                cond = cond + ")";
                query.addCondition(cond);
            }

            query.addCondition("1=1");
            query.addGroupBy(GroupByClause);
            if (ASCSortClause != "")
                query.setAscOrderBy(ASCSortClause);
            if (DESCSortClause != "")
                query.setDescOrderBy(DESCSortClause);

            long[] kp=KenhPhat._getSelectedIDs();
            if ( kp.Length == 1 && kp[0] != -1)
            {
                Col_LPS_KenhPhat.UnGroup();
            }
            else Col_LPS_KenhPhat.Group();

            if (NgayBatDauPhat.SelectedType == Framework.Win.Trial.SelectionTypes.OneDate)
            {
                Col_LPS_NgayPhat.UnGroup();
            }
            else
            {
                Col_LPS_NgayPhat.Group();
            }
            gridViewMaster.ViewCaption = "CHƯƠNG TRÌNH PHÁT SÓNG TRÊN " + (KenhPhat.Text == "" ? "CÁC KÊNH " : " KÊNH " + KenhPhat.Text) + " - " + NgayBatDauPhat.Caption.ToUpper();
            return query;
        }

        private static BarButtonItem AddXemTatCa(BarManager barManager, Bar mainBar,
            BarButtonItem barButtonItemSearch, PopupControlContainer popUP,
            BarCheckItem filter, GridView gridViewMaster, string filterString,
            string ascSortClause, string descSortClause, string groupClause
            )
        {
            int index = 0;
            for (int i = 0; i < mainBar.LinksPersistInfo.Count; i++)
            {
                LinkPersistInfo info = mainBar.LinksPersistInfo[i];
                if (info.Item.Name == barButtonItemSearch.Name)
                {
                    info.BeginGroup = false;
                    index = i;
                    break;
                }
            }

            var viewAll = new BarButtonItem
                              {
                                  Caption = "Xem tất c&ả",
                                  PaintStyle = BarItemPaintStyle.CaptionGlyph,
                                  Glyph = FrameworkParams.imageStore.GetImage2020("Picture.png"),
                                  Visibility = BarItemVisibility.Always
                              };

            viewAll.Appearance.ForeColor = Color.Red;
            Font bk = viewAll.Font;
            viewAll.Appearance.Font = new Font(bk, FontStyle.Bold);
            viewAll.Appearance.Options.UseFont = true;
            viewAll.Appearance.Options.UseForeColor = true;

            mainBar.LinksPersistInfo.Insert(index, new LinkPersistInfo(viewAll, true));
            barManager.Items.Add(viewAll);

            viewAll.ItemClick += delegate
                                     {

                                         var wait = new FWWaitingMsg();

                                         try
                                         {

                                             var query = new QueryBuilder(filterString);
                                             if (!string.IsNullOrEmpty(groupClause))
                                             {
                                                 query.addGroupBy(groupClause);
                                             }
                                             if (ascSortClause != "")
                                                 query.setAscOrderBy(ascSortClause);
                                             if (descSortClause != "")
                                                 query.setDescOrderBy(descSortClause);
                                             var ds = HelpDB.getDBService().LoadDataSet(query);
                                             gridViewMaster.ClearSelection();
                                             if (ds != null && ds.Tables.Count > 0)
                                             {
                                                 gridViewMaster.GridControl.DataSource = ds.Tables[0];
                                                 if (gridViewMaster.RowCount > 0)
                                                 {
                                                     gridViewMaster.FocusedRowHandle = -1;
                                                     gridViewMaster.SelectRow(0);
                                                     gridViewMaster.FocusedRowHandle = 0;
                                                 }
                                                 else
                                                 {
                                                     gridViewMaster.SelectRow(-1);
                                                 }

                                             }
                                             else gridViewMaster.GridControl.DataSource = null;

                                             filter.Checked = false;

                                             //if (frm == null
                                             //    || !(frm is PhieuQuanLyBandedChange)
                                             //    || ((PhieuQuanLyBandedChange)frm)._UsingCheckFiler)
                                             //{
                                             //    popUP.Visible = false;
                                             //}

                                             ClearDataCtrl(popUP.Controls);
                                         }
                                         catch (Exception ex)
                                         {
                                             PLException.AddException(ex);
                                         }
                                         finally
                                         {
                                             wait.Finish();
                                         }
                                     };
            return viewAll;
        }

 public override QueryBuilder PLBuildQueryFilter()
 {
     QueryBuilder filter = null;
     string sql = @"select ctccv_id,(select name from dm_loai_cong_viecn lcv where lcv.id=ctcv.lcv_id) TEN_CV,
     nv.ten_nv NHANVIEN,
     ctcv.ngay_lam_viec,ctcv.ngay_cap_nhat,ctcv.lcv_id,ctcv.nv_id,ctcv.mo_ta,ctcv.thoi_gian_thuc_hien,ctcv.duyet
     from chi_tiet_cong_viec ctcv inner join dm_nhan_vien nv on nv.id=ctcv.nv_id
     where nv.visible_bit='Y' and  1=1";
     filter = new QueryBuilder(sql);
     filter.addID("LCV_ID", Loai_Cv._getSelectedID());
     filter.addID("NV_ID", Nhan_vien._getSelectedID());
     filter.addDateFromTo("NGAY_LAM_VIEC", TuNgay.DateTime, DenNgay.DateTime);
     filter.addDuyet("DUYET", DuyetSelect.layTrangThai());
     filter.addGroupBy("ctccv_id,lcv_id,nv.ten_nv,ngay_lam_viec,ngay_cap_nhat,nv_id,mo_ta,thoi_gian_thuc_hien,duyet");
     filter.setDescOrderBy("NGAY_LAM_VIEC");
     DataSet ds = DABase.getDatabase().LoadDataSet(filter);
     gridControlMaster.DataSource = ds.Tables[0];
     return null;
 }

 public bool UpdateRow()
 {
     string query = that.UpdateRow();
     try
     {
         if (query != null && query != "" && that.gridViewMaster.FocusedRowHandle >= 0)
         {
             DataRow row = that.gridViewMaster.GetDataRow(that.gridViewMaster.FocusedRowHandle);
             long id = HelpNumber.ParseInt64(row[that.IDField].ToString());
             QueryBuilder filter = new QueryBuilder(query);
             filter.addID(that.Alias + that.IDField, id);
             if (that.GroupByClause != "")
                 filter.addGroupBy(that.GroupByClause);
             DataSet ds = DABase.getDatabase().LoadReadOnlyDataSet(filter);
             if (ds.Tables[0].Rows.Count == 1)
             {
                 row.ItemArray = ds.Tables[0].Rows[0].ItemArray;
                 PLDaChonMasterRecord(true);
                 that.gridControlDetail.DataSource = that.PLLoadDataDetailPart(HelpNumber.ParseInt64(row[that.IDField].ToString()));
                 that.HookFocusRow();
             }
             else
             {
                 that.gridViewMaster.DeleteRow(that.gridViewMaster.FocusedRowHandle);
             }
             return true;
         }
         else
             return false;
     }
     catch
     {
         PLMessageBoxDev.ShowMessage("Câu truy vấn này bị sai " + query);
         return false;
     }
 }

        /// <summary>Step 5: Xây dựng Query Buider cho việc tìm kiếm
        /// Xây dựng một QueryBuilder từ những chọn lựa trong phần filter.
        /// Từ QueryBuilder này ta có thể lấy được dữ liệu thỏa điều kiện.
        /// Nếu hỗ trợ duyệt thì trong câu truy vấn trả về 
        /// phải có cột là DUYET_BIT
        /// </summary>
        public override QueryBuilder PLBuildQueryFilter()
        {
            string sql = UpdateRow();
            var query = new QueryBuilder(sql);
            query.addLike("LOWER(" + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
            query.addLike("LOWER(" + CHUONG_TRINH.TEN_GOC + ")", TenGoc.Text.ToLower());
            query.addLike("LOWER(" + CHUONG_TRINH.CATEGORY + ")", Category.Text.ToLower());
            AppCtrl.addID(query, CHUONG_TRINH.TIET_MUC, TietMuc);
            AppCtrl.addID(query, CHUONG_TRINH.DON_VI_CUNG_CAP, DonViCungCap);
            AppCtrl.addFilterFromTo(query, BIEN_MUC.NAM_SAN_XUAT, NamSXTu.Value, NamSXDen.Value);
            long[] ids = QuocGia._getSelectedIDs();
            if (ids.Length > 0 && ids.Length < QuocGia.Properties.GetItems().Count)
            {
                string cond = ids.Aggregate("(", (current, id) => current + (BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + ",%' or "));
                cond = cond.TrimEnd(' ', 'r', 'o');
                cond = cond + ")";
                query.addCondition(cond);
            }
            AppCtrl.addID(query, CHUONG_TRINH.BAN_QUYEN_THUOC, BanQuyenThuoc);
            query.addDateFromTo(CHUONG_TRINH.BAN_QUYEN_DID_END, BQDTTNEndTu.DateTime, BQDTTNEndDen.DateTime);
            query.addDateFromTo(CHUONG_TRINH.BAN_QUYEN_DTNN_END, BQDTNNEndTu.DateTime, BQDTNNEndDen.DateTime);
            AppCtrl.addFilterFromTo(query, CHUONG_TRINH.TONG_SO_RUN, TongSoRunTu.Value, TongSoRunDen.Value);
            AppCtrl.addFilterFromTo(query, CHUONG_TRINH.SO_LAN_DA_PHAT, SoRunDaDungTu.Value, SoRunDaDungDen.Value);
            AppCtrl.addFilterFromTo(query, "(" + CHUONG_TRINH.TONG_SO_RUN + "-" + CHUONG_TRINH.SO_LAN_DA_PHAT + ")", SoRunConLaiTu.Value, SoRunConLaiDen.Value);
            AppCtrl.addFilterFromTo(query, Alias + CHUONG_TRINH.NGAY_NHAP, ngayNhap);
            AppCtrl.addID(query, Alias + CHUONG_TRINH.PHONG_BAN, PhongBan);
            query.addIn(Alias + CHUONG_TRINH.IS_TRONG_KHO, AppCtrl.GetCheckedValues(TrongKho, true));
            string ftTimeslot = AppCtrl.getFilterFromTo("CTNPDT.TIMESLOT", TimeslotTu, TimeslotDen);
            if (ftTimeslot != "")
            {
                query.addCondition(string.Format(@"exists(select ctnpdt.ct_id from chuong_trinh_npdt   ctnpdt
                                                    where ctnpdt.timeslot is not null and ctnpdt.ct_id= ct.ct_id {0})", ftTimeslot));
            }
            string w1 = "";
            if (!LoaiLuuTru._IsNotCheckAnyOrCheckedAll)
            {
                w1 += " AND " + CHUONG_TRINH_POST_MASTER.LOAI_LUU_TRU + " in " + LoaiLuuTru._getStrSelectedIDs();
            }
            if (!SoDKCBHD._IsNotCheckAnyOrCheckedAll)
            {
                w1 += " AND exists(select chd.ctpm_hd_id from chuong_trinh_post_Master_hd chd where chd.ctpm_id=cp.ctpm_id and chd.so_dkcb_hd in " + SoDKCBHD._getStrSelectedIDs() + ")";

            }
            if (w1 != "")
                query.addCondition(string.Format(@"exists(select cp.ctpm_id from chuong_trinh_post_Master cp where (is_delete='N' or is_delete is null) and  cp.ct_id = ct.ct_id {0})", w1));

            string w2 = "";
            if (TapSoTu.Value > 0)
            {
                w2 += " AND bm.tap_so >= " + TapSoTu.Value;
            }
            if (TapSoDen.Value > 0)
            {
                w2 += " AND bm.tap_so <=" + TapSoDen.Value;
            }

            if (TenTap.Text.Trim() != "")
            {
                w2 += " AND lower(bm.ten_tap) like '%" + TenTap.Text.Trim().ToLower() + "%'";
            }

            if (w2 != "")
            {
                query.addCondition(string.Format("exists(select bm.bm_id from bien_muc bm where (is_delete='N' or is_delete is null) and bm.ct_id=ct.ct_id {0})", w2));
            }

            query.addCondition("1=1");
            query.addGroupBy(GroupByClause);
            if (ASCSortClause != "")
                query.setAscOrderBy(ASCSortClause);
            if (DESCSortClause != "")
                query.setDescOrderBy(DESCSortClause);
            return query;
        }

 public override QueryBuilder PLBuildQueryFilter()
 {
     FWWaitingMsg msg = new FWWaitingMsg();
     QueryBuilder filter = null;
     string sql = @"SELECT CTCCV_ID,(SELECT NAME FROM DM_LOAI_CONG_VIECN LCV WHERE LCV.ID=CTCV.LCV_ID) TEN_CV,
             NV.NAME NHANVIEN,CTCV.NGAY_LAM_VIEC,CTCV.NGAY_CAP_NHAT
             ,CTCV.LCV_ID,CTCV.NV_ID,CTCV.MO_TA,CAST(CTCV.THOI_GIAN_THUC_HIEN AS TIME) THOI_GIAN_THUC_HIEN
     FROM CHI_TIET_CONG_VIEC CTCV INNER JOIN DM_NHAN_VIEN NV ON NV.ID=CTCV.NV_ID
     WHERE NV.VISIBLE_BIT='Y' AND  1=1";
     filter = new QueryBuilder(sql);
     filter.addID("LCV_ID", Loai_Cv._getSelectedID());
     filter.addID("NV_ID", NhanVien._getSelectedID());
     filter.addDateFromTo("NGAY_LAM_VIEC", ngayLamViec.FromDate, ngayLamViec.ToDate);
     filter.addGroupBy("CTCCV_ID,LCV_ID,NV.NAME,NGAY_LAM_VIEC,NGAY_CAP_NHAT,NV_ID,MO_TA,THOI_GIAN_THUC_HIEN");
     filter.setDescOrderBy("NGAY_LAM_VIEC");
     //DataSet ds = DABase.getDatabase().LoadDataSet(filter);
     //gridControlMaster.DataSource = ds.Tables[0];
     msg.Finish();
     return filter;
 }