C# QueryBuilder.addID方法代码示例

        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);
        }

 public static void addID(QueryBuilder filter, string opLeft, PLMultiChoiceGrid input)
 {
     if (!input._IsNotCheckAnyOrCheckedAll)
     {
         filter.addID(opLeft, input._GetSelectedIds());
     }
 }

        /// <summary>CHAUTV : Tính tổng số giờ nhân viên làm được trong khoảng thời gian xác định
        /// </summary>
        public static double GetSumDaysWorked(DateTime from, DateTime to, long employKey)
        {
            QueryBuilder query = new QueryBuilder(@"SELECT distinct NV_ID,NGAY,SANG,CHIEU
                                                    FROM BANG_CHAM_CONG_AUTO
                                                    WHERE 1=1");
            query.addID("NV_ID", employKey);
            query.addDateFromTo("NGAY", from, to);
            DataSet ds = HelpDB.getDatabase().LoadDataSet(query, "BANG_CHAM_CONG_AUTO");

            double cntV = 0;
            double sumTime = 0;
            double cntN = 0;
            //Duyệt đếm thời gian, vắng, nghỉ
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                if (ds.Tables[0].Rows[i]["SANG"].ToString() == "V")
                    cntV++;
                if (ds.Tables[0].Rows[i]["CHIEU"].ToString() == "V")
                    cntV++;
                if (ds.Tables[0].Rows[i]["SANG"].ToString() == "N")
                    cntN++;
                if (ds.Tables[0].Rows[i]["CHIEU"].ToString() == "N")
                    cntN++;
                if (checkNumber(ds.Tables[0].Rows[i]["SANG"].ToString()))
                    sumTime += double.Parse(ds.Tables[0].Rows[i]["SANG"].ToString());
                if (checkNumber(ds.Tables[0].Rows[i]["CHIEU"].ToString()))
                    sumTime += double.Parse(ds.Tables[0].Rows[i]["CHIEU"].ToString());
            }
            //Làm tròn 4 chữ số
            return Math.Round(sumTime / 8 - cntV / 2, 4);
        }

 public static void InitChonChuyenMuc(PLCombobox PLChuyenMuc, long ID_Dien_Dan)
 {
     QueryBuilder query = null;
     query = new QueryBuilder("SELECT * FROM CHUYEN_MUC where 1=1");
     query.addCondition("VISIBLE_BIT='Y'");
     query.addID("ID_NHOM_DIEN_DAN", ID_Dien_Dan);
     DataSet ds = DienDanPermission.I._LoadDataSetWithResPermission(query, "ID");
     PLChuyenMuc._init(ds.Tables[0], "NAME", "ID");
 }

 public override QueryBuilder PLBuildQueryFilter()
 {
     QueryBuilder query = null;
     query = new QueryBuilder(
         "SELECT * " +
         "FROM TEST_PRODUCT_GROUP " +
         "WHERE 1=1"
     );
     query.addID("NOOP", -1);
     return query;
 }

 /// <summary>
 /// Tạo DataTable(HH_ID, SO_LUONG, TRONG_LUONG) lưu trữ hàng hóa, kèm số lượng, trọng lượng
 /// Thuộc 1 phiếu bất kì có loại phiếu và ID
 /// </summary>
 /// <param name="type">Loại phiếu</param>
 /// <param name="id">ID</param>
 /// <returns></returns>
 public static DataTable CreateSoTien(PhieuType type, long id)
 {
     try
     {
         string sql = "select Tong_tien  as \"SO_TIEN\" from "
             + type.GetTableName() + " where 1=1";
         QueryBuilder filter = new QueryBuilder(sql);
         filter.addID(type.GetIDField(), id);
         DataSet ds = DABase.getDatabase().LoadDataSet(filter);
         return ds.Tables[0];
     }
     catch (Exception ex)
     {
         PLException.AddException(ex);
         return null;
     }
 }

 private void frmHopDong_Load(object sender, EventArgs e)
 {
     QueryBuilder query = new QueryBuilder("delete from LOAI_DANH_BA where 1=1");
     query.addID("ID", 10110);
     DBService db = new DABaseFb().SQLStmt;
     db.Begin();
     try
     {
         db.DbCommand.Parameters.AddRange(query.generateDbParam().ToArray());
         db.ExecuteCommand(query.generateParamSQL());
         db.Commit();
     }
     catch (Exception ex)
     {
         db.RollBack();
     }
 }

        public static AddressList GetAddressList(long[] Keys)
        {
            QueryBuilder query = new QueryBuilder(@"SELECT e.ID, e.NAME, cat.USERID as ID, cat.USERNAME, e.EMAIL FROM USER_CAT cat left join DM_NHAN_VIEN e on e.ID=cat.EMPLOYEE_ID WHERE 1=1");
            query.addBoolean("e.VISIBLE_BIT", true);
            query.addCondition("(EMAIL<>'')");
            if (Keys.Length > 0)
                query.addID("e.ID", Keys);
            DataSet dsTo = HelpDB.getDatabase().LoadDataSet(query, "CAT");

            AddressList to = new AddressList();
            foreach (DataRow row in dsTo.Tables[0].Rows)
            {
                if (!to.ToAddressListString().Contains(row["EMAIL"].ToString()))
                    to.Add(new MailboxAddress(row["NAME"].ToString(), row["EMAIL"].ToString()));
            }

            return to;
        }

 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 DataSet dsGetQuyen(long keyFeature)
 {
     QueryBuilder query = new QueryBuilder(@"
         SELECT e.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.addID("fcat.ID", keyFeature);
     query.addBoolean("rel.ISREAD_BIT", true);
     return HelpDB.getDatabase().LoadDataSet(query);
 }

 public bool _LoadDataSetWithResGroupPermission(DataSet ds, string tableName, string KeyFieldRes, long ResGroupID)
 {
     if (ds == null)
     {
         return false;
     }
     try
     {
         QueryBuilder query = new QueryBuilder("SELECT * FROM " + tableName + "where 1=1");
         if (isPermission)
         {
             if (_PerResGroup_READ_Ids != null && _PerResGroup_READ_Ids.Length > 0)
             {
                 if (ResGroupID != -1)
                 {
                     bool isPerMis = false;
                     foreach (long id in _PerResGroup_READ_Ids)
                     {
                         if (id == ResGroupID)
                         {
                             isPerMis = true;
                             break;
                         }
                     }
                     if (isPerMis)
                         query.addID(KeyFieldRes, ResGroupID);
                     else query.addCondition("1=-1");
                 }
                 else
                 {
                     query.addID(KeyFieldRes, _PerResGroup_READ_Ids);
                 }
             }
             else
             {
                 query.addCondition("1=-1");
             }
         }
         else
         {
             if (ResGroupID != -1)
                 query.addID(KeyFieldRes, ResGroupID);
         }
         return DABase.getDatabase().LoadDataSet(query, tableName, ds);
     }
     catch (Exception exception)
     {
         PLException.AddException(exception);
         return false;
     }
 }

 /// <summary>Sự kiện khi chọn "Thêm".
 /// </summary>
 protected void barButtonItemAdd_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
 {
     try
     {
         long[] ids = that.ShowAddForm();
         if (ids != null)
         {
             string query = that.UpdateRow();
             if (query != null && query != "")
             {
                 DataTable table = ((DataTable)(that.gridControlMaster.DataSource)).DataSet.Tables[0];
                 for (int i = 0; i < ids.Length; i++)
                 {
                     QueryBuilder filter = new QueryBuilder(query);
                     filter.addID(that.IDField, ids[i]);
                     DataSet ds = DABase.getDatabase().LoadReadOnlyDataSet(filter);
                     if (ds.Tables[0].Rows.Count == 1)
                     {
                         DataRow newRow = table.NewRow();
                         newRow.ItemArray = ds.Tables[0].Rows[0].ItemArray;
                         table.Rows.Add(newRow);
                     }
                     else
                     {
                     }
                 }
             }
         }
         else
         {
             //PLRefresh();
         }
     }
     catch (Exception ex)
     {
         PLException.AddException(ex);
     }
 }

 public bool UpdateRow()
 {
     try
     {
         string query = that.UpdateRow();
         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.IDField, id);
             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
     {
         return false;
     }
 }

        public override QueryBuilder PLBuildQueryFilter()
        {
            FWWaitingMsg wait = new FWWaitingMsg();
            //Viết theo cách này không áp dụng tìm kiếm nâng cao được
            QueryBuilder query = new QueryBuilder(UpdateRow());
            query.addID("PC.LCV_ID", LoaiCV._getSelectedID());
            if (mruEditTenCongViec.Text.Trim() != "" && mruEditTenCongViec.Text.Trim() != null)
            {
                query.addCondition(string.Format("PC.CONG_VIEC = '{0}'", mruEditTenCongViec.Text.Trim()));
            }
            StringBuilder cond = new StringBuilder("");
            if (NhanVien._getSelectedID() != -1) cond.Append(string.Format("NGUOI_GIAO = {0}", NhanVien._getSelectedID()));
            long [] NTH_ID=NguoiThucHien._SelectedIDs;
            if (NTH_ID.Length > 0 && cond.Length > 0) cond.Append(" OR ");
            int temp = NTH_ID.Length;
            foreach (long id in NTH_ID)
            {
                cond.Append("CT.MA_NV = " + id + "");
                temp--;
                if (temp > 0)
                {
                    cond.Append(" OR ");
                }
            }
            if (cond.Length > 0)
            {
                query.addCondition(cond.ToString());
            }
            query.addID("TINH_TRANG", Tinhtrang._getSelectedID());
            query.addID("MUC_UU_TIEN", cmbDoUuTien._getSelectedID());
            query.setDescOrderBy("PC.NGAY_BAT_DAU");
            query.addDateFromTo("PC.NGAY_BAT_DAU", ngayLamViec.FromDate, ngayLamViec.ToDate);
            GridDataSet = HelpDB.getDatabase().LoadDataSet(query);
            GridDataSet.Tables[0].Columns.Add("NV_THAM_GIA");
            //DataSet dùng cho việc tính tiến độ công việc
            string sql = @"SELECT C.PCCV_ID,PHAN_TRAM_THAM_GIA,MAX(TIEN_DO) TIEN_DO,T.TONG_PHAN_TRAM
                            FROM CHI_TIET_PHAN_CONG C,
                            (SELECT PCCV_ID,SUM(PHAN_TRAM_THAM_GIA) TONG_PHAN_TRAM
                                FROM CHI_TIET_PHAN_CONG WHERE TIEN_DO = 0 GROUP BY PCCV_ID) T
                                    WHERE TIEN_DO > 0 AND T.PCCV_ID = C.PCCV_ID AND
                                    C.THOI_GIAN_CAP_NHAT IN (SELECT MAX(CT.THOI_GIAN_CAP_NHAT) FROM CHI_TIET_PHAN_CONG CT WHERE CT.PCCV_ID=T.PCCV_ID GROUP BY CT.MA_NV)
                                          GROUP BY C.PCCV_ID,C.MA_NV,PHAN_TRAM_THAM_GIA,T.TONG_PHAN_TRAM";
            DataSet ds = HelpDB.getDatabase().LoadDataSet(sql);

            foreach (DataRow dr in GridDataSet.Tables[0].Rows)
            {
                String ten = "";
                String sqlString = "SELECT CT.MA_NV, NV.NAME, CT.PHAN_TRAM_THAM_GIA FROM CHI_TIET_PHAN_CONG CT, DM_NHAN_VIEN NV WHERE CT.MA_NV = NV.ID AND CT.PCCV_ID = " + dr["PCCV_ID"].ToString() + " GROUP BY MA_NV,NAME, PHAN_TRAM_THAM_GIA";
                DataTable dt = HelpDB.getDatabase().LoadDataSet(sqlString).Tables[0];
                foreach (DataRow row in dt.Rows)
                {
                    if (ten == "")
                        ten = ten + row["NAME"].ToString() + " (" + row["PHAN_TRAM_THAM_GIA"].ToString() + "%)";
                    else
                        ten = ten + "\n" + row["NAME"].ToString() + " (" + row["PHAN_TRAM_THAM_GIA"].ToString() + "%)";
                }
                dr["NV_THAM_GIA"] = ten;
                //Tính giá trị cho cột tính độ dựa vào DataSet (ds) ở trên
                long tam=HelpNumber.ParseInt64(dr["PCCV_ID"]);
                DataRow[] arrRow = ds.Tables[0].Select(string.Format("PCCV_ID = {0}", HelpNumber.ParseInt64(dr["PCCV_ID"])));
                decimal tongTienDo = 0;
                foreach (DataRow row in arrRow)
                    tongTienDo += (HelpNumber.ParseInt64(row["TIEN_DO"]) * HelpNumber.ParseInt64(row["PHAN_TRAM_THAM_GIA"]));
                if (arrRow.Length > 0) HelpNumber.RoundDecimal(tongTienDo /= HelpNumber.ParseInt64(arrRow[0]["TONG_PHAN_TRAM"]),2);
                dr["TONG_TIEN_DO"] = tongTienDo.ToString() + "%";
            }
            try
            {
                if (GridDataSet != null && GridDataSet.Tables[0].Rows.Count > 0)
                {
                    gridControlMaster.DataSource = GridDataSet.Tables[0];
                    splitContainerControl1.SplitterPosition = 171;
                }
                else
                {
                    gridControlMaster.DataSource = null;
                }
            }
            catch
            {}
            wait.Finish();
            return null;
        }

        private void InitData(List<long> ids)
        {
            #region 1.Lấy thông tin bên bảng RESUME (Thông tin ứng viên)
            QueryBuilder query = new QueryBuilder(@"
                SELECT  MA_HO_SO as MA_NV,
                        HO_TEN as NAME,
                        NGAY_SINH,DIA_CHI,EMAIL,DIEN_THOAI
                FROM RESUME
                WHERE 1=1");
            query.addID("ID", ids.ToArray());
            DataSet ds = HelpDB.getDatabase().LoadDataSet(query, "DM_NHAN_VIEN");
            ds.Tables["DM_NHAN_VIEN"].Columns.Add("ID", typeof(System.Int64));
            #endregion

            #region 2.Lấy cấu trúc bảng DM_NHAN_VIEN & Merge dữ liệu từ thông tin ứng viên sang
            DataSet dsMain = DABase.getDatabase().LoadDataSet(new QueryBuilder(@"SELECT * FROM DM_NHAN_VIEN WHERE 1=0 and 1=1"), "DM_NHAN_VIEN");
            HelpDataSet.MergeDataSet(new string[] { "ID" }, dsMain, ds);
            foreach (DataRow row in dsMain.Tables["DM_NHAN_VIEN"].Rows)
            {
                row["VISIBLE_BIT"] = "Y";
            }
            #endregion

            this.gridControlMaster.DataSource = dsMain.Tables["DM_NHAN_VIEN"];
        }