C# QueryBuilder.setAscOrderBy方法代码示例

        private void btnView_Click(object sender, EventArgs e)
        {
            if(KenhPhat._getSelectedID()<=1)
            {
                HelpMsgBox.ShowNotificationMessage("Vui lòng chọn kênh phát");
                return;
            }

            var sql =
                string.Format(
                    @"select ct.time_start,ct.time_end,ct.kenh_phat,iif(c.ct_id is null, tct.noi_dung, c.noi_dung) noi_dung,
            c.nuoc_san_xuat, ct.rate_group,datediff(minute,ct.time_start,ct.time_end) timesplit
            from tns_programs_grid_scr_ct ct
            left join tns_chuong_trinh tct on tct.tct_id=ct.chuong_trinh
            left join chuong_trinh c on c.ct_id=tct.ct_id
            where 1=1");
            var query = new QueryBuilder(sql);

            AppCtrl.addID(query, "ct.PGS_id", FileNguon);
            query.addID("CT.KENH_PHAT", KenhPhat._getSelectedID());
            var dt = NgayPhatSong.DateTime;
            query.addDateFromTo("CT.NGAY_PHAT", new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0),
                                new DateTime(dt.Year, dt.Month, dt.Day, 23, 59, 59));

            query.setAscOrderBy("ct.time_start, ct.kenh_phat");

            var ds = HelpDB.getDBService().LoadDataSet(query, KE_HOACH_LPS_CT.TABLE_NAME);
            gridControlDetail.DataSource = ds.Tables[0];
            gridBand1.Caption = dt.ToString("ddd " + FrameworkParams.option.dateFormat);
        }

        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 static void ChonNhomTin(PLImgCombobox Input, bool? IsAdd)
 {
     DataSet dsNTT;
     //true:add, false:edit, null:xem
     QueryBuilder query = new QueryBuilder("select ID,NAME from DM_NHOM_TIN_TUC WHERE 1=1");
     if (IsAdd == true)
         query.addCondition("VISIBLE_BIT='Y'");
     query.setAscOrderBy("NAME");
     dsNTT = TinTucPermission.I._LoadDataSetWithResGroupPermission(query, "ID");
     Input._init(dsNTT.Tables[0], "NAME", "ID");
 }

        public override QueryBuilder PLBuildQueryFilter()
        {
            QueryBuilder filter = null;
            string sql = UpdateRow();
            filter = new QueryBuilder(sql);

            filter.addCondition("1=1");
            if (this.ASCSortClause != "")
                filter.setAscOrderBy(ASCSortClause);
            if (this.DESCSortClause != "")
                filter.setDescOrderBy(DESCSortClause);
            return filter;
        }

        public static DataSet GetDataSet(bool? IsAdd)
        {
            string sql = string.Format(@"SELECT *
                           From {0}
                           where 1=1", TABLE_MAP);

            QueryBuilder query = new QueryBuilder(sql);
            if (IsAdd == true)
                query.addCondition("VISIBLE_BIT='Y'");
            query.setAscOrderBy("lower(NAME)");
            DataSet ds = HelpDB.getDatabase().LoadDataSet(query, TABLE_MAP);
            return ds;
        }

 public static DataTable GetUsingLoaiGhiChu()
 {
     string sql = string.Format(@"SELECT NAME,ID
     From  {0}
     where exists(select first 1 1 from chuong_trinh_ghi_chu where loai_ghi_chu=id) and 1=1", TABLE_MAP);
     var query = new QueryBuilder(sql);
         query.addCondition("VISIBLE_BIT='Y'");
     query.setAscOrderBy("ID");
     DataSet ds = HelpDB.getDatabase().LoadDataSet(query, TABLE_MAP);
     if (ds != null && ds.Tables.Count > 0)
         return ds.Tables[0];
     return null;
 }

        public static RepositoryItemLookUpEdit InitCot(GridColumn column, string fieldName, bool? IsAdd)
        {
            string sql = string.Format(@"SELECT *
                           From {0}
                           where 1=1", TABLE_MAP);

            QueryBuilder query = new QueryBuilder(sql);
            if (IsAdd == true)
                query.addCondition("VISIBLE_BIT='Y'");
            query.setAscOrderBy("lower(NAME)");
            DataSet ds = HelpDB.getDatabase().LoadDataSet(query, TABLE_MAP);
            return HelpGridColumn.CotCombobox(column, ds, "ID", "NAME", fieldName);
        }

 public static DataTable GetUsingDTH(bool? isAdd)
 {
     string sql = string.Format(@"SELECT NAME,id
     From  {0}
     where (exists(select first 1 1 from chuong_trinh_npdt  where dai_truyen_hinh=id)
     or  exists(select first 1 1 from chuong_trinh_psk  where dai_truyen_hinh=id)) and
     1=1", TABLE_MAP);
     var query = new QueryBuilder(sql);
     if (isAdd == true)
         query.addCondition("VISIBLE_BIT='Y'");
     query.setAscOrderBy("ID");
     DataSet ds = HelpDB.getDatabase().LoadDataSet(query, TABLE_MAP);
     if (ds != null && ds.Tables.Count > 0)
         return ds.Tables[0];
     return null;
 }

        public static void InitCtrl(PLCombobox Input, bool? IsAdd)
        {
            string sql = string.Format(@"SELECT *
                           From {0}
                           where 1=1", TABLE_MAP);

            QueryBuilder query = new QueryBuilder(sql);
            if (IsAdd == true)
                query.addCondition("VISIBLE_BIT='Y'");
            query.setAscOrderBy("lower(NAME)");
            DataSet ds = HelpDB.getDatabase().LoadDataSet(query, TABLE_MAP);
            Input.DataSource = ds.Tables[0];
            Input.DisplayField = "NAME";
            Input.ValueField = "ID";
            Input._init();
        }

 public static void InitCtrl(PLCombobox Input, bool? IsAdd)
 {
     string sql = string.Format(@"SELECT *
                    From {0}
                    where 1=1", TABLE_MAP);
     QueryBuilder query = new QueryBuilder(sql);
     if (IsAdd == true)
     {
         query.addID("CONG_TY",  FrameworkParams.LoginCompanyID);
         query.addCondition("VISIBLE_BIT='Y'");
     }
     query.setAscOrderBy("lower(NAME)");
     DataSet ds = HelpDB.getDatabase().LoadDataSet(query,TABLE_MAP);
     Input.MainCtrl.Properties.SortColumnIndex = -1;
     Input.DataSource = AppUtil.SortLogicalTable(ds.Tables[0], "NAME");
     Input.DisplayField = "NAME";
     Input.ValueField = "ID";
     Input._init();
 }

        public static BarButtonItem AddXemTatCa(BarManager barManager, Bar mainBar, 
            BarButtonItem barButtonItemSearch,PopupControlContainer popUP,BarCheckItem filter, GridView gridViewMaster,RepositoryItemCheckedComboBoxEdit listChoseColumn, string filterString
            )
        {
            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;
                }
            }

            BarButtonItem viewAll = new BarButtonItem();
            viewAll.Caption = "Xem tất c&ả";
            viewAll.PaintStyle = BarItemPaintStyle.CaptionGlyph;
            viewAll.Glyph = FrameworkParams.imageStore.GetImage2020("Picture.png");
            viewAll.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(object sender, ItemClickEventArgs e)
            {
                try
                {
                    if (listChoseColumn != null)
                    {
                        AppCtrl.DisplayTheoTuyBienCot(gridViewMaster, listChoseColumn);
                    }
                    QueryBuilder query = new QueryBuilder(filterString);
                    foreach (string sort in BIEN_MUC.DEFAUT_SORT_ASC_FIELDS)
                    {
                        query.setAscOrderBy(sort);
                    }
                    DataSet ds = HelpDB.getDBService().LoadDataSet(query);
                    if (ds != null && ds.Tables.Count > 0)
                        gridViewMaster.GridControl.DataSource = ds.Tables[0];
                    else gridViewMaster.GridControl.DataSource = null;
                    popUP.Visible = false;
                    filter.Checked = false;
                    foreach (Control ctrl in popUP.Controls)
                    {
                        if (ctrl is PLMultiCombobox)
                        {
                            PLMultiCombobox plm = ctrl as PLMultiCombobox;
                            plm._setSelectedIDs(new long[] {-1});
                        }
                        else if (ctrl is SpinEdit)
                        {
                            SpinEdit spin = ctrl as SpinEdit;
                            spin.EditValue = null;
                        }
                        else if (ctrl is TextEdit)
                        {
                            TextEdit txt = ctrl as TextEdit;
                            txt.Text = null;
                        }
                        else if (ctrl is DateEdit)
                        {
                            DateEdit date = ctrl as DateEdit;
                            date.EditValue = null;
                        }
                        else if (ctrl is CheckedListBoxControl)
                        {
                            CheckedListBoxControl check = ctrl as CheckedListBoxControl;
                            check.UnCheckAll();
                        }
                        else if(ctrl is PLDateSelection)
                        {
                            PLDateSelection date = ctrl as PLDateSelection;
                            if (date.AllowNull) date.SelectedType = SelectionTypes.None;
                        }

                    }
                }
                catch
                {

                }
            };
            return viewAll;
        }

 public override QueryBuilder PLBuildQueryFilter()
 {
     QueryBuilder query = new QueryBuilder(UpdateRow());
     AppCtrl.addID(query, LichPhatSong.KENH_PHAT, kenhPhat);
     AppCtrl.addFilterFromTo(query, LichPhatSong.NGAY_PHAT_SONG, ngayNhap);
     query.setDescOrderBy(LichPhatSong.NGAY_PHAT_SONG);
     query.setAscOrderBy(LichPhatSong.GIO_PHAT_SONG);
     query.addCondition("1=1");
     return query;
 }

        private void Search(bool isEmpty)
        {
            string where = "";
            if (isEmpty == false && ChuongTrinhPermission.I.IsPermission)
                where = " exists(select is_per from check_chuongtrinh_isread(" + FrameworkParams.currentUser.employee_id + ",c.ct_id) where is_per='Y') AND ";
            string sql = string.Format(@"select distinct iif(c.code_ct is null or char_length(c.code_ct )=0  or bm.code_bm is null or char_length( bm.code_bm)=0,null, c.code_ct||'_'||bm.code_bm) CODE_CT,  c.NOI_DUNG TEN_CT,tm.NAME TIET_MUC,
            (select LIST_NAME from get_nuoc_san_xuat(c.nuoc_san_xuat)) NUOC_SX,
             cast( c.nam_san_xuat as a_str_20) NAM_SX, pm.name POST_MASTER,cast( bm.TAP_SO as a_str_10) TAP_SO, bm.ten_tap,'' RUN, '' GIA, bm.bm_id
             from bien_muc bm
             inner join chuong_trinh  c on c.ct_id=bm.ct_id
             left join dm_post_master pm on bm.post_master_id=pm.id
             left join dm_tiet_muc tm on tm.id=c.tiet_muc
             where {0} 1=1", where);

            QueryBuilder filter = new QueryBuilder(sql);
            if (isEmpty == false)
            {
                if (xtraTabControl1.SelectedTabPage.Name == xtraTabPageBasic.Name)
                {
                    AppCtrl.addID(filter, "C." + CHUONG_TRINH.TIET_MUC, TietMuc);
                    AppCtrl.addID(filter, "C." + CHUONG_TRINH.PHONG_BAN, PhongBan);
                    AppCtrl.addID(filter, "bm." + BIEN_MUC.POST_MASTER_ID, PostMaster);
                    filter.addLike("LOWER(" + "C." + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
                    filter.addLike("LOWER(" + "C." + CHUONG_TRINH.TEN_GOC + ")", TenGoc.Text.ToLower());
                    filter.addLike("LOWER(" + "C." + CHUONG_TRINH.CATEGORY + ")", Category.Text.ToLower());
                    filter.addDateFromTo("C." + CHUONG_TRINH.NGAY_NHAP, ngayNhapCTTu.DateTime, ngayLapCTDen.DateTime);
                    filter.addCondition("(c.is_delete is null or c.is_delete='N') and (bm.is_delete is null or bm.is_delete='N') ");
                    filter.setAscOrderBy("C.NOI_DUNG, BM.TAP_SO");
                }
                else if (xtraTabControl1.SelectedTabPage.Name == xtraTabPageBudget.Name)
                {
                    bool none = false;
                    if (gridViewBudget.RowCount == 0)
                    {
                        none = true;
                    }
                    DataRow[] rows = null;
                    if (none == false)
                    {
                        DataTable dt = gridControlBudget.DataSource as DataTable;
                        rows = dt.Select("IS_CHON='" + "Y'");
                        if (rows.Length == 0)
                            none = true;
                    }
                    if (none)
                    {
                        HelpMsgBox.ShowNotificationMessage("Vui lòng chọn kế hoạch budget trước khi liệt kê chương trình!");
                        return;
                    }

                    string khids = "";
                    foreach (DataRow r in rows)
                    {
                        khids += r[KE_HOACH_BUDGET.KHBG_ID] + ",";
                    }
                    khids = khids.TrimEnd(',');
                    string cond = string.Format(@" bm.bm_id in ( select abc.bm_id
                        from
                        (select ct.bm_id_2 bm_id
                        from ke_hoach_budget_ct ct
                        where ct.bm_id_2 is not null  and ct.khbg_id in ({0})
                        union
                        select ct.bm_id_3 bm_id
                        from ke_hoach_budget_ct ct
                        where ct.bm_id_3 is not null  and ct.khbg_id in ({0})
                        union
                        select ct.bm_id_4 bm_id
                        from ke_hoach_budget_ct ct
                        where ct.bm_id_4 is not null  and ct.khbg_id in ({0})
                        union
                        select ct.bm_id_5 bm_id
                        from ke_hoach_budget_ct ct
                        where ct.bm_id_5 is not null  and ct.khbg_id in ({0})
                        union
                        select ct.bm_id_6 bm_id
                        from ke_hoach_budget_ct ct
                        where ct.bm_id_6 is not null and ct.khbg_id in ({0})
                        union
                        select ct.bm_id_7 bm_id
                        from ke_hoach_budget_ct ct
                        where ct.bm_id_7 is not null and ct.khbg_id in ({0})
                        union
                        select ct.bm_id_8 bm_id
                        from ke_hoach_budget_ct ct
                        where ct.bm_id_8 is not null and ct.khbg_id in ({0})
                        ) abc )", khids);

                    filter.addCondition(cond);
                    filter.setAscOrderBy("C.NOI_DUNG, BM.TAP_SO");
                }
                else
                {
                    sql = @"select distinct iif(c.code_ct is null or char_length(c.code_ct )=0  or bm.code_bm is null or char_length( bm.code_bm)=0,null, c.code_ct||'_'||bm.code_bm) CODE_CT,  c.ten_ct TEN_CT,tm.NAME TIET_MUC,
            (select LIST_NAME from get_nuoc_san_xuat(c.nuoc_san_xuat)) NUOC_SX,
             cast( c.nam_san_xuat as a_str_20) NAM_SX, pm.name POST_MASTER,cast( bm.TAP_SO as a_str_10) TAP_SO, bm.ten_tap,'' RUN, '' GIA, bm.bm_id
             from ql_lich_phat_song_ct bm
             inner join ql_lich_phat_song  c on c.lps_id=bm.lps_id
             left join dm_post_master pm on c.post_master =pm.id
             left join dm_tiet_muc tm on tm.id=c.tiet_muc
             where 1=1";
//.........这里部分代码省略.........

 private DataTable LoadTable(string TableName , int[] RootID)
 {
     QueryBuilder query = new QueryBuilder("SELECT * FROM " + TableName + " WHERE 1=1");
     if (RootID != null)
     {
         string ids = "";
         for (int i = 0 ; i < RootID.Length ; i++)
         {
             ids += RootID[i].ToString() + ",";
             //query.addID(PLFN.ID_ROOT, RootID[i]);
             //query.addID(this.ParentFieldName, RootID[i]);
         }
         query.addCondition(GlobalConst.ID_ROOT + " in (" + ids + "0)");
     }
     else
     {
         //query.addCondition(this.ParentFieldName + " is null ");
         //query.addID(this.ParentFieldName, -1);
     }
     query.setAscOrderBy(this.order);
     DataSet ds = DABase.getDatabase().LoadDataSet(query , TableName);
     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 filter = null;
            string sql = UpdateRow();
            filter = new QueryBuilder(sql);
            filter.addSoPhieu("CT."+CHUONG_TRINH.MA_CT, MaCT.Text);
            filter.addLike("LOWER(CT." + CHUONG_TRINH.NOI_DUNG + ")", NoiDung.Text.ToLower());
            AppCtrl.addID(filter, "CT." + CHUONG_TRINH.TIET_MUC, TietMuc);
            AppCtrl.addFilterFromTo(filter, "PM." + CHUONG_TRINH_POST_MASTER.NGAY_NHAP, ngayNhap);
            AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.PHONG_BAN, PhongBan);
            AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.NGUOI_NHAP, NguoiNhap);
            AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.POST_MASTER_ID, PostMaster);
            AppCtrl.addID(filter, "PM." + CHUONG_TRINH_POST_MASTER.LOAI_LUU_TRU, LoaiLuuTru);
            if (!SoDKCBHD._IsNotCheckAnyOrCheckedAll)
            {
                filter.addCondition("exists(select chd.ctpm_hd_id from chuong_trinh_post_Master_hd chd where chd.ctpm_id=pm.ctpm_id and chd.so_dkcb_hd in " + SoDKCBHD._getStrSelectedIDs() + ")");
            }

            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 != "")
            {
                filter.addCondition(string.Format("exists(select bm.bm_id from bien_muc bm where (is_delete='N' or is_delete is null) and bm.ctpm_id=pm.ctpm_id {0})", w2));
            }
            filter.addCondition("1=1");
            if (this.ASCSortClause != "")
                filter.setAscOrderBy(ASCSortClause);
            if (this.DESCSortClause != "")
                filter.setDescOrderBy(DESCSortClause);
            return filter;
        }