C# QueryBuilder.add方法代码示例

 public void addNumFromTo(QueryBuilder filter, string opLeft, CalcEdit FromCalc, CalcEdit ToCalc)
 {
     if (FromCalc.EditValue != null)
     {
         filter.add(opLeft, Operator.GreaterEqual, FromCalc.Value, DbType.Decimal);
     }
     if (ToCalc.EditValue != null)
     {
         filter.add(opLeft, Operator.LessEqual, ToCalc.Value, DbType.Decimal);
     }
 }

        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 override QueryBuilder PLBuildQueryFilter()
        {
            FWWaitingMsg msg = new FWWaitingMsg();
            if (dtRecipient == null) dtRecipient = HelpDB.getDatabase().LoadDataSet("SELECT ID,NAME FROM DM_NHAN_VIEN").Tables[0];
            QueryBuilder query = new QueryBuilder(UpdateRow());

            StringBuilder cond = new StringBuilder("");
            if (cmbNguoiYC._getSelectedID() != -1) cond.Append(string.Format("YC.NGUOI_GUI_ID = {0}", cmbNguoiYC._getSelectedID()));
            long[] arrNguoiNhan = cmbNguoiNhan._SelectedIDs;
            if (arrNguoiNhan.Length > 0 && cond.Length > 0) cond.Append(" OR ");
            int temp = arrNguoiNhan.Length;
            foreach (long id in arrNguoiNhan)
            {
                cond.Append(string.Format(@"(YC.NGUOI_NHAN_ID LIKE '{0}%')
                        OR (YC.NGUOI_NHAN_ID LIKE '%,{0},%') OR (YC.NGUOI_NHAN_ID LIKE '%,{0}')
                        OR (YCTL.NGUOI_NHAN_ID LIKE '{0}%')
                        OR (YCTL.NGUOI_NHAN_ID LIKE '%,{0},%') OR (YCTL.NGUOI_NHAN_ID LIKE '%,{0}')", id));
                temp--;
                if (temp > 0)
                {
                    cond.Append(" OR ");
                }
            }
            if (cond.Length > 0)
            {
                query.addCondition(cond.ToString());
            }
            if (mruEditYeuCau.Text.Trim() != "" && mruEditYeuCau.Text.Trim() != null)
            {
                query.addCondition(string.Format("CHU_DE = '{0}'", mruEditYeuCau.Text.Trim()));
            }
            if (PLMucuutien._getSelectedID() != -1)
                query.add("MUC_UU_TIEN", Operator.Equal,PLMucuutien._getSelectedID() , DbType.Int32);
            if (PLTinhtrang._getSelectedID() != -1)
                query.add("TINH_TRANG", Operator.Equal, PLTinhtrang._getSelectedID(), DbType.Int32);

            query.addDateFromTo("YC.NGAY_GUI", ngayLamViec.FromDate, ngayLamViec.ToDate);
            query.setDescOrderBy("YC.NGAY_GUI");
            DataSet ds = HelpDB.getDatabase().LoadDataSet(query);
            foreach (DataRow  row in ds.Tables[0].Rows)
                row["NGUOI_NHAN"] = GetNameOfRecipient(row["NGUOI_NHAN_ID"].ToString());
            gridControlMaster.DataSource = ds.Tables[0];
            msg.Finish();
            return null;
        }

 public override QueryBuilder PLBuildQueryFilter()
 {
     QueryBuilder filter = new QueryBuilder
     (
          "SELECT uv.ID as ID,hpv.DTD_ID,uv.HO_TEN,hpv.VONG_PHONG_VAN,hpv.NGAY_GIO_PHONG_VAN, hpv.LAN_MOI_PHONG_VAN,tths.NAME as TTHS_NAME, " +
          " hpv.NGAY_GIO_PHONG_VAN  as GIO_PHONG_VAN " +
          "FROM RESUME uv, HEN_PHONG_VAN hpv,DM_TINH_TRANG_HO_SO tths " +
          "WHERE uv.ID=hpv.R_ID AND tths.ID = uv.TTHS_ID AND 1=1"
     );
     filter.add("uv.HO_TEN", Operator.Like, "%" + PLHeplString.PLHelpFortmatName(Filter_TenUV.Text) + "%", DbType.String);
     if (Filter_VongPV.Text != "")
         filter.add("hpv.VONG_PHONG_VAN", Operator.Equal, (int)Filter_VongPV.Value, DbType.Int32);
     filter.addDateFromTo("hpv.NGAY_GIO_PHONG_VAN", Filter_NgayPVTu.DateTime.Date, Filter_NgayPVDen.DateTime.Date);
     filter.addHourFrom(Filter_GioPVTu.TimeEditSub.Time, "hpv.NGAY_GIO_PHONG_VAN");
     filter.addHourTo(Filter_GioPVDen.TimeEditSub.Time, "hpv.NGAY_GIO_PHONG_VAN");
     filter.addID("uv.TTHS_ID", Filter_TinhTrangHoSo._getSelectedID());
     filter.setAscOrderBy("uv.HO_TEN");
     return filter;
 }

 public override QueryBuilder PLBuildQueryFilter()
 {
     FWWaitingMsg msg = new FWWaitingMsg();
     QueryBuilder filter = new QueryBuilder(UpdateRow());
     filter.addLike("UPPER(HO_TEN)",Filter_TenUV.Text.Trim().ToUpper());
     if (Filter_VongPV.Text != "")
         filter.add("VONG_PHONG_VAN", Operator.Equal, (int)Filter_VongPV.Value, DbType.Int32);
     filter.addDateFromTo("NGAY_GIO_PHONG_VAN", ngayLamViec.FromDate, ngayLamViec.ToDate);
     filter.addHourFrom(Filter_GioPVTu.Time, "NGAY_GIO_PHONG_VAN");
     filter.addHourTo(Filter_GioPVDen.Time, "NGAY_GIO_PHONG_VAN");
     filter.addID("TTHS_ID", Filter_TinhTrangHoSo._getSelectedID());
     filter.setAscOrderBy("HO_TEN");
     filter.addCondition(" 1=1 ");
     msg.Finish();
     return filter;
 }

 public override DataTable PLLoadDataDetailPart(long MasterID)
 {
     try
     {
         QueryBuilder query = new QueryBuilder("select * from RESUME where 1=1");
         query.add("ID", Operator.Equal, MasterID, DbType.Int32);
         DataTable dt = DABase.getDatabase().LoadDataSet(query, "DETAIL").Tables[0];
         this.Web_TrinhDoChuyenMon.DocumentText = HelpByte.BytesToUTF8String((Byte[])dt.Rows[0]["TRINH_DO_CHUYEN_MON"]);
         this.Web_TrinhDoNgoaiNgu.DocumentText = HelpByte.BytesToUTF8String((Byte[])dt.Rows[0]["TRINH_DO_NGOAI_NGU"]);
         this.Web_ThongTinKhac.DocumentText = HelpByte.BytesToUTF8String((Byte[])dt.Rows[0]["THONG_TIN_KHAC"]);
         this.Web_QuaTrinhCongTac.DocumentText = HelpByte.BytesToUTF8String((Byte[])dt.Rows[0]["QUA_TRINH_CONG_TAC"]);
         this.Web_QuaTrinhDaoTao.DocumentText = HelpByte.BytesToUTF8String((Byte[])dt.Rows[0]["QUA_TRINH_DAO_TAO"]);
         return dt;
     }
     catch (Exception)
     {
         return null;
     }
 }

 public static DataTable GetYeuCauOfYCTraLoi(long ID)
 {
     DataSet ds;
     QueryBuilder query = new QueryBuilder(@"SELECT YC.*,CASE YC.TINH_TRANG
     WHEN 1 THEN 'Mới tạo'WHEN 2 THEN 'Đang xử lý' WHEN 3 THEN 'Không hỗ trợ' ELSE 'Hoàn tất' END TEN_TINH_TRANG
     FROM YEU_CAU YC INNER JOIN YEU_CAU_TRA_LOI YCTL ON YC.ID = YCTL.YEU_CAU_ID WHERE 1=1");
     query.add("YCTL.ID", Operator.Equal, ID, DbType.Int64);
     ds = HelpDB.getDatabase().LoadDataSet(query);
     return ds.Tables[0];
 }

 public static void InitCtrlDuyet(PLDMGrid Input, bool ReadOnly, bool? IsAdd, string FeatureName)
 {
     QueryBuilder query = new QueryBuilder(@"
         SELECT e.ID,e.NAME,e.DEPARTMENT_ID FROM USER_FEATURE_REL rel, USER_CAT u, DM_NHAN_VIEN e ,FEATURE_CAT fcat
         WHERE e.ID = u.EMPLOYEE_ID AND rel.USERID=u.USERID AND fcat.ID=rel.FEATUREID AND 1=1");
     query.add("fcat.NAME", Operator.Equal, FeatureName, DbType.String);
     query.addBoolean("rel.ISREAD_BIT", true);
     if (IsAdd != null)
     {
         query.addBoolean("e.VISIBLE_BIT", true);
     }
     Input._init((ReadOnly == true ? GroupElementType.ONLY_CHOICE : GroupElementType.CHOICE_N_ADD), HelpDB.getDatabase().LoadDataSet(query, "DM_NHAN_VIEN").Tables[0], "ID", "NAME",
         new string[] { "ID" }, new string[] { "ID" }, DMNhanVienX.I.CreatePLDMGroupElementNhan_vien, DMNhanVienX.I.GetRuleDM_NHAN_VIEN, null, null, null, null);
     if (ReadOnly)
     {
         try
         {
             ((System.Windows.Forms.ToolStrip)Input.Controls[0].Controls[0].Controls[1]).Items[4].Visible = true;// Ẩn nút sửa
             ((System.Windows.Forms.ToolStrip)Input.Controls[0].Controls[0].Controls[1]).Items[5].Visible = true;// Ẩn nút Xóa
         }
         catch (Exception ex)
         {
             PLException.AddException(ex);
         }
     }
 }

        public void SetNext()
        {
            //LockControls = false;
            string idT = GetIDsThu();
            _doLichPhatSong = DALichPhatSongNew.I.LoadAll(-2);

            _rootLichPhatSong = null;
            _isAdd = true;
            btnCreateDetail.Text = "Tạo chi tiết LPS  >>";

            var qr = new QueryBuilder(@"select first 1 ngay_phat_song from ql_lich_phat_song_ct lpsct
                 inner join ql_lich_phat_song lps on lps.lps_id=lpsct.lps_id
                where extract(weekday from ngay_phat_song) in(" + idT + ") and 1=1");
            qr.add(QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG, Operator.GreaterEqual, NgayPhatCuoi.DateTime, DbType.DateTime);
            qr.addID("lps." + QL_LICH_PHAT_SONG.KENH_PHAT, KenhPhat._getSelectedID());
            qr.add(string.Format("extract(hour from {0})", "LPS." + QL_LICH_PHAT_SONG.GIO_PHAT_SONG), Operator.Equal, GioPhat.Time.Hour, DbType.Int32);
            qr.add(string.Format("extract(minute from {0})", "LPS." + QL_LICH_PHAT_SONG.GIO_PHAT_SONG), Operator.Equal, GioPhat.Time.Minute, DbType.Int32);
            qr.setDescOrderBy(QL_LICH_PHAT_SONG_CT.NGAY_PHAT_SONG);
            var ds = HelpDB.getDatabase().LoadDataSet(qr);
            var thus = AppCtrl.GetThuTrongTuan(ThuTrongTuan._getSelectedIDs());
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                NgayBatDau.DateTime = AppCtrl.GetNextDate((DateTime)ds.Tables[0].Rows[0][0], thus);
            }

            else
                NgayBatDau.DateTime = AppCtrl.GetNextDate(NgayPhatCuoi.DateTime, thus);

            _replaceType = ReplaceType.NONE;
            NgayPhatCuoi.EditValue = null;
            SoNgayPhat.EditValue = null;
            GhiChu.Text = "";
            NoiDung._setSelectedID(-1);
            TenGoc.Text = "";
            Category.Text = "";
            TongTapPhat.EditValue = null;
            TongTapCT.EditValue = null;
            NuocSX._setSelectedIDs(new long[] { -1 });
            NamSX.EditValue = null;
            TietMuc._setSelectedID(-1);
            PostMaster._setSelectedID(-1);
            ThoiLuong.Text = "";
            // LockControls = false;
            _dsDetail.Clear();
            gridControlDetail.DataSource = _dsDetail.Tables[0];
            btnNext.Enabled = false;
            btnDelete.Enabled = false;
            btnCopy.Enabled = false;
            Title.Text = "TẠO MỚI LỊCH PHÁT SÓNG";
            ColStart.Visible = false;

            NgayBatDau.Properties.ReadOnly = false;
            KenhPhat.MainCtrl.Properties.ReadOnly = false;
            ThuTrongTuan.Properties.ReadOnly = false;
            GioPhat.Properties.ReadOnly = false;

            _error.ClearErrors();
            groupBox1.Visible = false;
        }

 public DataSet GetData()
 {
     QueryBuilder filter = null;
     string sql = "select CTCCV_ID,DA_ID,CV_ID,NV_ID,MO_TA,BAT_DAU,KET_THUC,THOI_GIAN_THUC_HIEN,NGAY_LAM_VIEC,NGAY_CAP_NHAT,case when DUYET = 0 then 'Duyệt' else( case when DUYET = 1 then 'Chưa Duyệt' else 'Chờ Duyệt' end)end duyeta  from KE_HOACH_LAM_VIEC where 1=1";
     filter = new QueryBuilder(sql);
     filter.add("NGAY_LAM_VIEC",Operator.Equal,NgayNhap.DateTime,DbType.DateTime);
     filter.addID("NV_ID",NhanVien._getSelectedID());
     filter.addID("DUYET",comboBoxEdit1.SelectedIndex);
     filter.setAscOrderBy("NGAY_CAP_NHAT");
     DataSet ds = HelpDB.getDatabase().LoadDataSet(filter, "ABC");
     return HelpDB.getDatabase().LoadDataSet(filter, "ABC");
 }

        //Chuyen thoi gian lam viec cua nhan vien vao db
        public bool UpdateThoiGianLamViec(long NVID, DateTime NgayLamViec, double ThoiGian)
        {
            DataSet DatailDataSet = new DataSet();
            QueryBuilder query = new QueryBuilder( "select * from BANG_CHAM_CONG where 1=1");
            query.addID("NV_ID", NVID);
            query.add(new Condition("NGAY", Operator.Equal,NgayLamViec.Date, DbType.Date));
            HelpDB.getDatabase().LoadDataSet(query, "BANG_CHAM_CONG", DatailDataSet);

            if(DatailDataSet.Tables[0].Rows.Count>0 && ThoiGian!=-1)
            {
                DatailDataSet.Tables[0].Rows[0]["THOI_GIAN_LAM_VIEC"] = ThoiGian;
            }
            DatabaseFB db = DABase.getDatabase();
            db.OpenConnection();
            DbTransaction dbTrans = db.BeginTransaction(db.OpenConnection());
            try
            {
                if (db.UpdateDataSet(DatailDataSet, dbTrans) == false) return false;
            }
            catch
            {
                db.RollbackTransaction(dbTrans);
            }
            db.CommitTransaction(dbTrans);
            return true;
        }

 public override QueryBuilder PLBuildQueryFilter()
 {
     var query = new QueryBuilder(UpdateRow());
     query.addLike("LOWER(pgs." + TNS_SALE_REV_SCR.NAME + ")", TenNguon.Text.ToLower());
     query.addLike("LOWER(" + TNS_SALE_REV_SCR.FILE_NAME + ")", TenFile.Text.ToLower());
     AppCtrl.addID(query, TNS_SALE_CPP_SCR.TARGET, Target);
     AppCtrl.addID(query, TNS_SALE_CPP_SCR.THI_TRUONG, ThiTruong);
     AppCtrl.addID(query, TNS_SALE_REV_SCR.NGUOI_TAO, NguoiLap);
     AppCtrl.addFilterFromTo(query, TNS_SALE_REV_SCR.NGAY_TAO, NgayLap);
     if (TuNam.Text != "" && TuThang.Text != "")
     {
         query.add(TNS_SALE_REV_SCR.TU_NGAY, Operator.GreaterEqual,
                   new DateTime((int) TuNam.Value, (int) TuThang.Value, 1, 0, 0, 0), DbType.DateTime);
     }
     if (DenThang.Text != "" && DenNam.Text != "")
     {
         query.add(TNS_SALE_REV_SCR.DEN_NGAY, Operator.LessEqual,
                   new DateTime((int) DenNam.Value, (int) DenThang.Value, 1, 23, 59, 59), DbType.DateTime);
     }
     AppCtrl.addID(query, TNS_SALE_CPP_SCR.PHONG_BAN, PhongBan);
     query.addCondition("1=1");
     query.setDescOrderBy(this.DESCSortClause);
     return query;
 }

 public static DataTable Get_Bang_yeu_cau(long ID)
 {
     DataSet ds;
     QueryBuilder query = new QueryBuilder(@"SELECT YC.*,CASE YC.TINH_TRANG
     WHEN 1 THEN 'Mới tạo'WHEN 2 THEN 'Đang xử lý' WHEN 3 THEN 'Không hỗ trợ' ELSE 'Hoàn tất' END TEN_TINH_TRANG
     ,(SELECT COUNT(*) FROM YEU_CAU_TRA_LOI YCTL WHERE YCTL.YEU_CAU_ID = YC.ID) SO_PHAN_HOI
     FROM YEU_CAU YC WHERE 1=1");
     query.add("ID", Operator.Equal, ID, DbType.Int64);
     ds = HelpDB.getDatabase().LoadDataSet(query);
     return ds.Tables[0];
 }

 public static DataTable Get_Bang_yc_tra_loi(long ID)
 {
     DataSet ds;
     QueryBuilder query = new QueryBuilder(@"SELECT NGUOI_GUI_ID, NGUOI_NHAN_ID, NOI_DUNG, NGAY_GUI
     FROM YEU_CAU_TRA_LOI WHERE 1=1");
     query.add("ID", Operator.Equal, ID, DbType.Int64);
     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()
 {
     QueryBuilder query =  new QueryBuilder(UpdateRow());
     query.addSoPhieu("LOWER(" + BANG_GIA_BUDGET.MA_BGBG + ")", MaKeHoach.Text.ToLower());
     AppCtrl.addID(query, BANG_GIA_BUDGET.NGUOI_LAP, NguoiLap);
     AppCtrl.addFilterFromTo(query, BANG_GIA_BUDGET.NGAY_LAP, NgayLap);
     if (ThoiGianApDung.SelectedType != ProtocolVN.Framework.Win.Trial.SelectionTypes.None)
     {
         query.add(BANG_GIA_BUDGET.TU_NGAY, Operator.GreaterEqual, ThoiGianApDung.FromDate.Date, DbType.Date);
         query.add(BANG_GIA_BUDGET.DEN_NGAY, Operator.LessEqual, ThoiGianApDung.ToDate.Date, DbType.Date);
     }
     query.addCondition("1=1");
     query.setDescOrderBy(this.DESCSortClause);
     return query;
 }