C# Select.Where方法代码示例

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static List<string> LAYDSACH_IDKHOTHUOC_CHAN()
        {
            DataTable m_dtKhoThuoc = new DataTable();
              SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
              if (!globalVariables.IsAdmin)
              {
              sqlQuery.Where(TDmucKho.Columns.IdKho).In(new Select(QheNhanvienKho.Columns.IdKho)
                                                        .From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
                                                        .IsEqualTo(globalVariables.gv_intIDNhanvien));

              }
              if (sqlQuery.HasWhere)
              sqlQuery.And(TDmucKho.Columns.KieuKho).In(lstKhochan);
              else
              {
              sqlQuery.Where(TDmucKho.Columns.KieuKho).In(lstKhochan);
              }
              sqlQuery.And(TDmucKho.Columns.KhoThuocVt).In(lstKhoThuoc);
              sqlQuery.And(TDmucKho.Columns.LaTuthuoc).IsEqualTo(0);
              sqlQuery.And(TDmucKho.Columns.LoaiKho).IsEqualTo(0);
              sqlQuery.OrderAsc(TDmucKho.Columns.SttHthi);
              m_dtKhoThuoc = sqlQuery.ExecuteDataSet().Tables[0];
              if (m_dtKhoThuoc.Rows.Count <= 0) return new List<string>();
              return m_dtKhoThuoc.AsEnumerable().Select(c => c.Field<string>(TDmucKho.Columns.IdKho)).ToList<string>();
        }

 void cboService_SelectedIndexChanged(object sender, EventArgs e)
 {
     try
     {
         SqlQuery _SqlQuery = new Select().From(VDmucDichvuclsChitiet.Schema);
         if (Utility.Int32Dbnull(cboService.SelectedValue, -1) > -1)
             _SqlQuery.Where(VDmucDichvuclsChitiet.Columns.IdDichvu).IsEqualTo(Utility.Int32Dbnull(cboService.SelectedValue, -1));
          dsTable=   _SqlQuery.OrderAsc(VDmucDichvuclsChitiet.Columns.SttHthi).ExecuteDataSet().Tables[0];
          Utility.SetDataSourceForDataGridEx(grdServiceDetail, dsTable, true, true, "id_cha<=0", DmucDichvuclsChitiet.Columns.SttHthi + "," + DmucDichvuclsChitiet.Columns.TenChitietdichvu);
     }
     catch
     {
     }
 }

        private static ActionResult CapnhatChiphiThuoc(KcbLuotkham objLuotkham, KcbLuotkham objLuotkhamCu)
        {
            using (var Scope = new TransactionScope())
            {
                decimal BHYT_PTRAM_TRAITUYENNOITRU =Utility.DecimaltoDbnull( THU_VIEN_CHUNG.Laygiatrithamsohethong("BHYT_PTRAM_TRAITUYENNOITRU", "0", false),0m);
                THUOC_GIATHEO_KHOAKCB = THU_VIEN_CHUNG.Laygiatrithamsohethong("THUOC_GIATHEO_KHOAKCB", "0", true) == "1";

                KcbDonthuocCollection lstDonthuoc=
                    new Select()
                    .From(KcbDonthuoc.Schema)
                    .Where(KcbDonthuoc.Columns.MaLuotkham).IsEqualTo(objLuotkham.MaLuotkham)
                    .And(KcbDonthuoc.Columns.IdBenhnhan).IsEqualTo(objLuotkham.IdBenhnhan)
                    .ExecuteAsCollection<KcbDonthuocCollection>();
                List<long> lstID = lstDonthuoc.Select(c => c.IdDonthuoc).Distinct().ToList<long>();
                if (lstID.Count <= 0)
                {
                    Scope.Complete();
                    return ActionResult.Success;
                }

                KcbDonthuocChitietCollection lstChitiet =
                    new Select().From(KcbDonthuocChitiet.Schema)
                    .Where(KcbDonthuocChitiet.Columns.IdDonthuoc).In(lstID)
                    .ExecuteAsCollection<KcbDonthuocChitietCollection>();

                bool ApdunggiathuocDoituong = THU_VIEN_CHUNG.Laygiatrithamsohethong("APDUNG_GIATHUOC_DOITUONG", "0", true) == "1";

                DmucDoituongkcb _DmucDoituongkcb = new Select().From(DmucDoituongkcb.Schema).Where(DmucDoituongkcb.Columns.MaDoituongKcb).IsEqualTo(objLuotkham.MaDoituongKcb).ExecuteSingle<DmucDoituongkcb>();
                if (_DmucDoituongkcb == null) return ActionResult.Success;
                //Kiểm tra nếu đối tượng ngoại trú đã có đơn thuốc thanh toán-->Ko cho phép chuyển nữa
                if (Utility.ByteDbnull(objLuotkham.TrangthaiNoitru,0)<=0 && lstChitiet.Where(c => c.TrangthaiThanhtoan > 0).Any())
                {
                    Scope.Complete();
                    return ActionResult.Cancel;
                }
                bool saveParent = false;
                foreach (KcbDonthuoc objKcbDonthuoc in lstDonthuoc)
                {

                    objKcbDonthuoc.IdLichsuDoituongKcb = objLuotkham.IdLichsuDoituongKcb;
                    objKcbDonthuoc.MatheBhyt = objLuotkham.MatheBhyt;
                    objKcbDonthuoc.MaDoituongKcb = objLuotkham.MaDoituongKcb;

                    foreach (KcbDonthuocChitiet objChitietDonthuoc in lstChitiet.Where(c => c.IdDonthuoc.Equals(objKcbDonthuoc.IdDonthuoc)))
                    {
                        if (Utility.Int32Dbnull(objChitietDonthuoc.IdGoi, -1) > 0)
                        {
                            objChitietDonthuoc.MaDoituongKcb = objLuotkham.MaDoituongKcb;
                            if (Utility.Int16Dbnull(objChitietDonthuoc.TrangthaiThanhtoan, 0) == 0)
                            {
                                saveParent = true;
                                objChitietDonthuoc.MadoituongGia = objLuotkham.MaDoituongKcb;
                                objChitietDonthuoc.PtramBhyt = objLuotkham.PtramBhyt;
                                objChitietDonthuoc.PtramBhytGoc = objLuotkham.PtramBhytGoc;
                                DmucThuoc _DmucThuoc = DmucThuoc.FetchByID(objChitietDonthuoc.IdThuoc);
                                if (ApdunggiathuocDoituong || Utility.Byte2Bool(_DmucDoituongkcb.GiathuocQuanhe.Value))//Giá theo bảng quan hệ-->
                                {
                                    QheDoituongThuoc _item = THU_VIEN_CHUNG.LayQheDoituongThuoc(objLuotkham.MaDoituongKcb,
                                                                              objChitietDonthuoc.IdThuoc,
                                                                              objLuotkham.MaKhoaThuchien, THUOC_GIATHEO_KHOAKCB);
                                    if (_item != null)//Tìm thấy quan hệ giá
                                    {
                                        objChitietDonthuoc.DonGia = Utility.DecimaltoDbnull(_item.DonGia);
                                        objChitietDonthuoc.PhuThu = (Utility.isTrue(objLuotkham.DungTuyen.Value) ? Utility.DecimaltoDbnull(_item.PhuthuDungtuyen) : Utility.DecimaltoDbnull(_item.PhuthuTraituyen));
                                        objChitietDonthuoc.TuTuc = 0;
                                    }
                                    else//Tìm giá dịch vụ, chỉ xảy ra khi đối tượng BHYT ko tìm thấy giá,
                                    //còn đối tượng DV chắc chắn do thiếu quan hệ giá nên ko thay đổi gì dòng giá thuốc này
                                    {
                                        _item = THU_VIEN_CHUNG.LayQheDoituongThuoc("DV",
                                                                               objChitietDonthuoc.IdThuoc,
                                                                               objLuotkham.MaKhoaThuchien, THUOC_GIATHEO_KHOAKCB);
                                        if (_item != null)
                                        {
                                            objChitietDonthuoc.MadoituongGia = "DV";
                                            objChitietDonthuoc.DonGia = Utility.DecimaltoDbnull(_item.DonGia);
                                            objChitietDonthuoc.PhuThu = (Utility.isTrue(objLuotkham.DungTuyen.Value) ? Utility.DecimaltoDbnull(_item.PhuthuDungtuyen) : Utility.DecimaltoDbnull(_item.PhuthuTraituyen));
                                            objChitietDonthuoc.TuTuc = 1;
                                            objChitietDonthuoc.PtramBhyt = 0;
                                        }
                                    }
                                }
                                else //Giá theo kho-->
                                {
                                    TThuockho objTK = TThuockho.FetchByID(objChitietDonthuoc.IdThuockho);
                                    if (objTK != null)
                                    {
                                        objChitietDonthuoc.DonGia = THU_VIEN_CHUNG.IsBaoHiem(objLuotkham.IdLoaidoituongKcb) ? Utility.DecimaltoDbnull(objTK.GiaBhyt.Value, objChitietDonthuoc.DonGia) : objTK.GiaBan;
                                        objChitietDonthuoc.PhuThu = (Utility.Byte2Bool(objLuotkham.DungTuyen) ? Utility.DecimaltoDbnull(objTK.PhuthuDungtuyen) : Utility.DecimaltoDbnull(objTK.PhuthuTraituyen));
                                        if (!THU_VIEN_CHUNG.IsBaoHiem(objLuotkham.IdLoaidoituongKcb))//Đối tượng dịch vụ-->Ko tính phụ thu
                                            objChitietDonthuoc.PhuThu = 0;
                                    }
                                }
                                //Tính lại các mục tự túc, BHYT chi trả, BN chi trả
                                if (_DmucThuoc != null && Utility.Int32Dbnull(_DmucThuoc.TuTuc, 0) == 1)
                                    objChitietDonthuoc.TuTuc = 1;//Ke ca co trong bang quan he
                                if (objLuotkham.IdLoaidoituongKcb == 1)//Đối tượng dịch vụ-->ko cần phải đánh dấu tự túc
                                {
                                    objChitietDonthuoc.TuTuc = 0;
                                    objChitietDonthuoc.PhuThu = 0;
//.........这里部分代码省略.........

        void Search()
        {
            SqlQuery _sqlquery = new Select().From(VDmucDichvucl.Schema);
            if (Utility.Int32Dbnull(cboServiceType.SelectedValue, -1) != -1)
                if (_sqlquery.HasWhere)
                    _sqlquery.Where(VDmucDichvucl.Columns.IdLoaidichvu).IsEqualTo(Utility.Int32Dbnull(cboServiceType.SelectedValue, -1));
                else
                    _sqlquery.And(VDmucDichvucl.Columns.IdLoaidichvu).IsEqualTo(Utility.Int32Dbnull(cboServiceType.SelectedValue, -1));

            if (Utility.sDbnull(cbonhombaocao.SelectedValue, "-1") != "-1")
                if (_sqlquery.HasWhere)
                    _sqlquery.Where(VDmucDichvucl.Columns.NhomBaocao).IsEqualTo(Utility.sDbnull(cbonhombaocao.SelectedValue, "-1"));
                else
                    _sqlquery.And(VDmucDichvucl.Columns.NhomBaocao).IsEqualTo(Utility.sDbnull(cbonhombaocao.SelectedValue, "-1"));

            if (Utility.Int32Dbnull(cboDepartment.SelectedValue, -1) != -1)
                if (_sqlquery.HasWhere)
                    _sqlquery.Where(VDmucDichvucl.Columns.IdKhoaThuchien).IsEqualTo(Utility.Int32Dbnull(cboDepartment.SelectedValue, -1));
                else
                    _sqlquery.And(VDmucDichvucl.Columns.IdKhoaThuchien).IsEqualTo(Utility.Int32Dbnull(cboDepartment.SelectedValue, -1));

            if (Utility.Int32Dbnull(cboPhongthuchien.SelectedValue, -1) != -1)
                if (_sqlquery.HasWhere)
                    _sqlquery.Where(VDmucDichvucl.Columns.IdPhongThuchien).IsEqualTo(Utility.Int32Dbnull(cboPhongthuchien.SelectedValue, -1));
                else
                    _sqlquery.And(VDmucDichvucl.Columns.IdPhongThuchien).IsEqualTo(Utility.Int32Dbnull(cboPhongthuchien.SelectedValue, -1));

            dtDataCLS = _sqlquery.ExecuteDataSet().Tables[0];
            dtDataCLS.AcceptChanges();
            Utility.SetDataSourceForDataGridEx(grdList, dtDataCLS, true, true, "1=1", "stt_hthi_loaidvu,ten_loaidichvu,stt_hthi,ten_dichvu");
        }

        /// <summary>
        /// HÀM THỰC HIỆN LẤY THÔNG TIN KHO LẺ
        /// </summary>
        /// <returns></returns>
        public static DataTable LAYTHONGTIN_KHOTHUOC_VATTU_NOITRU_THEOKHOA(int ID_KHOA)
        {
            DataTable m_dtKhoThuoc = new DataTable();
              SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
              if (!globalVariables.IsAdmin)
              {
              sqlQuery.Where(TDmucKho.Columns.IdKho).In(new Select(QheNhanvienKho.Columns.IdKho)
                                                        .From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
                                                        .IsEqualTo(globalVariables.gv_intIDNhanvien));

              }
              if (sqlQuery.HasWhere)
              sqlQuery.And(TDmucKho.Columns.KieuKho).In(lstKhole);//Kho lẻ
              else
              {
              sqlQuery.Where(TDmucKho.Columns.KieuKho).In(lstKhole);
              }
              sqlQuery.And(TDmucKho.Columns.LaTuthuoc).IsEqualTo(0)//Không lấy tủ thuốc
              .And(TDmucKho.Columns.LoaiBnhan).In(lstKhoNoitru);//Chỉ lấy các kho nội trú
              sqlQuery.And(TDmucKho.Columns.KhoThuocVt).In(lstKhoThuocVT);

              sqlQuery.And(TDmucKho.Columns.LoaiKho).IsEqualTo(0);
              sqlQuery.OrderAsc(TDmucKho.Columns.SttHthi);
              m_dtKhoThuoc = sqlQuery.ExecuteDataSet().Tables[0];
              DataTable dt_khovattu=LAYTHONGTIN_VATTU_KHOA(ID_KHOA);
              if(dt_khovattu!=null)
              {
              foreach(DataRow dr in dt_khovattu.Rows)
              m_dtKhoThuoc.ImportRow(dr);
              }
              return m_dtKhoThuoc;
        }

 /// <summary>
 /// hàm thực hiện việc lấy thông tin của dược
 /// </summary>
 /// <returns></returns>
 /// 
 public static DataTable LAYTHONGTIN_KHOTHUOC_TATCA()
 {
     DataTable m_dtKhoThuoc=new DataTable();
       SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
       if(!globalVariables.IsAdmin)
       {
       sqlQuery.Where(TDmucKho.Columns.IdKho).In(
           new Select(QheNhanvienKho.Columns.IdKho).From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
               .IsEqualTo(globalVariables.gv_intIDNhanvien));
       }
       sqlQuery.And(TDmucKho.Columns.KhoThuocVt).In(lstKhoThuoc);
        sqlQuery.OrderAsc(TDmucKho.Columns.SttHthi);
        m_dtKhoThuoc=sqlQuery.ExecuteDataSet().Tables[0];
       return m_dtKhoThuoc;
 }

        /// <summary>
        /// HÀM THỰC HIỆN LẤY THÔNG TIN KHO LẺ
        /// </summary>
        /// <returns></returns>
        public static DataTable LAYTHONGTIN_KHOTHUOC_LE_NGOAI_TRU_KEDON(string MA_DTUONG)
        {
            DataTable m_dtKhoThuoc = new DataTable();
              SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
              if (!globalVariables.IsAdmin)
              {
              sqlQuery.Where(TDmucKho.Columns.IdKho).In(new Select(QheNhanvienKho.Columns.IdKho)
                                                        .From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
                                                        .IsEqualTo(globalVariables.gv_intIDNhanvien));

              }
              if (sqlQuery.HasWhere)
              sqlQuery.And(TDmucKho.Columns.KieuKho).In(lstKhole);
              else
              {
              sqlQuery.Where(TDmucKho.Columns.KieuKho).In(lstKhole);
              }
              if (MA_DTUONG.Trim().ToUpper() != "ALL")
              {
              sqlQuery.And(TDmucKho.Columns.IdKho).In(new Select(QheDoituongKho.IdKhoColumn).From(QheDoituongKho.Schema).Where(QheDoituongKho.MaDoituongKcbColumn).IsEqualTo(MA_DTUONG));
              }
              sqlQuery.And(TDmucKho.Columns.KhoThuocVt).In(lstKhoThuoc);
              sqlQuery.And(TDmucKho.Columns.LaTuthuoc).IsEqualTo(0)
              .And(TDmucKho.Columns.LoaiBnhan).In(lstKhoNgoaitru);
              sqlQuery.And(TDmucKho.Columns.LoaiKho).IsEqualTo(0);
              sqlQuery.OrderAsc(TDmucKho.Columns.SttHthi);
              m_dtKhoThuoc = sqlQuery.ExecuteDataSet().Tables[0];
              return m_dtKhoThuoc;
        }

        /// <summary>
        /// hàm thwucj hiện việc lấy thông tin quan hệ kho theo nhân viên
        /// </summary>
        /// <returns></returns>
        public static DataTable LayThongTinQuanHeKHoTheoNhanVien()
        {
            SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
              if (!globalVariables.IsAdmin)
              {
              sqlQuery.Where(TDmucKho.Columns.IdKho).In(
                  new Select(QheNhanvienKho.Columns.IdKho).From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
                      .IsEqualTo(globalVariables.gv_intIDNhanvien));

              }
              return sqlQuery.ExecuteDataSet().Tables[0];
        }

        /// <summary>
        /// hàm thực hiện việc lấy uqna hệ đối tượng và kho thuốc
        /// </summary>
        /// <param name="MaDoiTuong"></param>
        /// <returns></returns>
        public static DataTable LaythongtinQuanheDoituongKho(string MaDoiTuong)
        {
            SqlQuery sqlQuery=new Select().From(TDmucKho.Schema);
              if(!globalVariables.IsAdmin)
              {
              sqlQuery.Where(TDmucKho.Columns.IdKho).In(
                  new Select(QheDoituongKho.Columns.IdKho).From(QheDoituongKho.Schema).Where(QheDoituongKho.Columns.MaDoituongKcb)
                      .IsEqualTo(MaDoiTuong));

              }

              return sqlQuery.ExecuteDataSet().Tables[0];
        }

        /// <summary>
        /// Data retrieval
        /// </summary>
        /// <returns></returns>
        private DataSet GenerateReturnSet()
        {
            DataSet result = null;
            if(_url != null)
            {
                SqlQuery qry = null;
                //Query q;

                if(!String.IsNullOrEmpty(_url.TableName))
                {
                    qry = new Select().From(_url.TableName);
                    IDataProvider provider = ProviderFactory.GetProvider();
                    ITable tbl = provider.FindTable(_url.TableName);

                    if(_url.PrimaryKey != null)
                        qry = qry.Where(tbl.PrimaryKey.Name).IsEqualTo(_url.PrimaryKey);
                    //q.WHERE(q.Schema.PrimaryKey.ParameterName, _url.PrimaryKey);

                    if(_url.Parameters != null)
                    {
                        IDictionaryEnumerator loopy = _url.Parameters.GetEnumerator();
                        IColumn column;

                        string paramName;
                        object paramValue;

                        while(loopy.MoveNext())
                        {
                            paramName = loopy.Key.ToString();
                            paramValue = loopy.Value;

                            int pageSize = 0;
                            int pageIndex = -1;

                            if(paramName.ToLowerInvariant() == "pagesize" || paramName.ToLowerInvariant() == "pageindex")
                            {
                                if(paramName.ToLowerInvariant() == "pagesize")
                                    pageSize = int.Parse(paramValue.ToString());

                                if(paramName.ToLowerInvariant() == "pageindex")
                                    pageIndex = int.Parse(paramValue.ToString());

                                if(pageSize > 0 && pageIndex > -1)
                                    qry.Paged(pageIndex + 1, pageSize);
                            }
                            else
                            {
                                Comparison comp;
                                EvalComparison(paramName, out paramName, out comp);
                                column = tbl.GetColumn(paramName);

                                //if this column is a string, by default do a fuzzy search
                                if(comp == Comparison.Like || column.IsString)
                                {
                                    comp = Comparison.Like;
                                    paramValue = String.Concat("%", paramValue, "%");
                                    qry = qry.Where(column.Name).Like(paramValue.ToString());
                                }
                                else if(paramValue.ToString().ToLower() == "null")
                                    qry = qry.Where(column.Name).IsNull();

                                //q.WHERE(column.ColumnName, comp, paramValue);
                            }
                        }
                    }

                    result = provider.ExecuteDataSet(qry.GetCommand());
                }
                else if(!String.IsNullOrEmpty(_url.SpName))
                {
                    StoredProcedure sp = new StoredProcedure(_url.SpName);

                    if(_url.Parameters != null)
                    {
                        IDictionaryEnumerator loopy = _url.Parameters.GetEnumerator();
                        while(loopy.MoveNext())
                            sp.Command.AddParameter(loopy.Key.ToString(), loopy.Value, DbType.AnsiString);
                    }
                    result = sp.ExecuteDataSet();
                }
            }
            return result;
        }

        public static DataTable LAYTHONGTIN_KHOTHUOC(string loaikho)
        {
            DataTable m_dtKhoThuoc = new DataTable();
              SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
              if (!globalVariables.IsAdmin)
              {

              sqlQuery.Where(TDmucKho.Columns.IdKho).In(new Select(QheNhanvienKho.Columns.IdKho)
                                                        .From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
                                                        .IsEqualTo(globalVariables.gv_intIDNhanvien)).And(TDmucKho.Columns.LaTuthuoc).IsEqualTo(0);

              }
              if(sqlQuery.HasWhere)
               sqlQuery.And(TDmucKho.Columns.KieuKho).IsEqualTo(loaikho);
              else
              {
              sqlQuery.Where(TDmucKho.Columns.KieuKho).IsEqualTo(loaikho);
              }
              sqlQuery.And(TDmucKho.Columns.KhoThuocVt).In(lstKhoThuoc);
              sqlQuery.OrderAsc(TDmucKho.Columns.SttHthi);
              m_dtKhoThuoc = sqlQuery.ExecuteDataSet().Tables[0];
              return m_dtKhoThuoc;
             // return new Select().From(TDmucKho.Schema).Where(TDmucKho.Columns.KhoThuocVt).IsEqualTo(loaikho).OrderAsc(TDmucKho.Columns.SttHthi).ExecuteDataSet().Tables[0];
        }

        public static DataTable LAYTHONGTIN_VATTU_KHOA(int ID_KHOA)
        {
            DataTable m_dtKhoThuoc = new DataTable();
              SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
              if (!globalVariables.IsAdmin)
              {
              sqlQuery.Where(TDmucKho.Columns.IdKho).In(new Select(QheNhanvienKho.Columns.IdKho)
                                                        .From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
                                                        .IsEqualTo(globalVariables.gv_intIDNhanvien));

              }
              if (sqlQuery.HasWhere)
              sqlQuery.And(TDmucKho.Columns.KhoThuocVt).In(lstKhoVT);
              else
              {
              sqlQuery.Where(TDmucKho.Columns.KhoThuocVt).In(lstKhoVT);
              }
              sqlQuery.And(TDmucKho.Columns.KieuKho).In(lstKhole);
              sqlQuery.And(TDmucKho.Columns.IdKhoaphong).IsEqualTo(ID_KHOA);
              sqlQuery.OrderAsc(TDmucKho.Columns.SttHthi);
              m_dtKhoThuoc = sqlQuery.ExecuteDataSet().Tables[0];
              return m_dtKhoThuoc;
        }

        private static ActionResult CapnhatChiphiKCB(KcbLuotkham objLuotkham, KcbLuotkham objLuotkhamCu)
        {
            using (var Scope = new TransactionScope())
            {
                KcbDangkyKcbCollection lstChiphiKCB =
                    new Select().From(KcbDangkyKcb.Schema).Where(KcbDangkyKcb.Columns.MaLuotkham).IsEqualTo(
                        objLuotkham.MaLuotkham).And(KcbDangkyKcb.Columns.IdBenhnhan).IsEqualTo(objLuotkham.IdBenhnhan)
                        .And(KcbDangkyKcb.Columns.LaPhidichvukemtheo).IsEqualTo(0)
                        .ExecuteAsCollection<KcbDangkyKcbCollection>();
                if (lstChiphiKCB.Count > 0)
                {
                    //Kiểm tra nếu đối tượng ngoại trú đã có dịch vụ thanh toán-->Ko cho phép chuyển nữa
                    if (Utility.ByteDbnull(objLuotkham.TrangthaiNoitru,-1)<=0 && lstChiphiKCB.Where(c => c.TrangthaiThanhtoan > 0).Any())
                    {
                        Scope.Complete();
                        return ActionResult.Cancel;
                    }
                    //CHUYỂN GIÁ KHÁM BỆNH VÀO PHÒNG
                    foreach (KcbDangkyKcb objDangkyKCB in lstChiphiKCB)
                    {
                        if (Utility.Int32Dbnull(objDangkyKCB.IdGoi, -1) <=0)//Chỉ xử lý các bản ghi ngoài gói
                        {
                            objDangkyKCB.MaDoituongkcb = objLuotkham.MaDoituongKcb;
                            objDangkyKCB.IdDoituongkcb = objLuotkham.IdDoituongKcb;
                            objDangkyKCB.IdLoaidoituongkcb = objLuotkham.IdLoaidoituongKcb;
                            objDangkyKCB.IdLichsuDoituongKcb = objLuotkham.IdLichsuDoituongKcb;
                            objDangkyKCB.MatheBhyt = objLuotkham.MatheBhyt;
                            if (objDangkyKCB.TrangthaiThanhtoan == 0)//Các mục chưa thanh toán thì cho phép chuyển
                            {

                                DmucDichvukcb _DichvukcbCu =
                                    DmucDichvukcb.FetchByID(objDangkyKCB.IdDichvuKcb);
                                var _DichvukcbMoi =
                                    new Select().From(DmucDichvukcb.Schema)
                                        .Where(DmucDichvukcb.Columns.IdKhoaphong).IsEqualTo(_DichvukcbCu.IdKhoaphong)
                                        .And(DmucDichvukcb.Columns.IdPhongkham).IsEqualTo(_DichvukcbCu.IdPhongkham)
                                             .And(DmucDichvukcb.Columns.IdKieukham).IsEqualTo(_DichvukcbCu.IdKieukham)
                                        .AndExpression(DmucDichvukcb.Columns.MaDoituongKcb).IsEqualTo(objLuotkham.MaDoituongKcb)
                                        .Or(DmucDichvukcb.Columns.MaDoituongKcb).IsEqualTo("ALL").CloseExpression()
                                        .ExecuteSingle<DmucDichvukcb>();
                                if (_DichvukcbMoi != null)
                                {
                                    objDangkyKCB.IdDichvuKcb = Utility.Int16Dbnull(_DichvukcbMoi.IdDichvukcb, -1);
                                    if (Utility.Int32Dbnull(_DichvukcbMoi.TuTuc, 0) == 1)
                                        objDangkyKCB.TuTuc = 1;//Ke ca co trong bang quan he
                                    if (objLuotkham.IdLoaidoituongKcb == 1)//Đối tượng dịch vụ-->ko cần phải đánh dấu tự túc
                                    {
                                        objDangkyKCB.TuTuc = 0;
                                        objDangkyKCB.PhuThu = 0;
                                    }
                                    objDangkyKCB.TenDichvuKcb = _DichvukcbMoi.TenDichvukcb;
                                    objDangkyKCB.DonGia = Utility.DecimaltoDbnull(_DichvukcbMoi.DonGia);
                                    objDangkyKCB.PhuThu = !Utility.Byte2Bool(objLuotkham.DungTuyen)
                                                       ? Utility.DecimaltoDbnull(_DichvukcbMoi.PhuthuDungtuyen)
                                                       : Utility.DecimaltoDbnull(_DichvukcbMoi.PhuthuTraituyen);
                                    if (!THU_VIEN_CHUNG.IsBaoHiem(objLuotkham.IdLoaidoituongKcb))
                                        objDangkyKCB.PhuThu = 0;
                                    objDangkyKCB.PtramBhyt = objLuotkham.PtramBhyt;//% BHYT ngoại trú
                                    objDangkyKCB.PtramBhytGoc = objLuotkham.PtramBhytGoc;
                                    if (Utility.Byte2Bool(objDangkyKCB.KhamNgoaigio))
                                    {
                                        objDangkyKCB.KhamNgoaigio = 1;
                                        objDangkyKCB.DonGia = Utility.DecimaltoDbnull(_DichvukcbMoi.DongiaNgoaigio, 0);
                                        objDangkyKCB.PhuThu = !Utility.Byte2Bool(objLuotkham.DungTuyen) ? Utility.DecimaltoDbnull(_DichvukcbMoi.PhuthuNgoaigio, 0) : Utility.DecimaltoDbnull(_DichvukcbMoi.PhuthuDungtuyen);
                                    }

                                    if (Utility.Int32Dbnull(objDangkyKCB.TuTuc, 0) == 0)
                                    {
                                        objDangkyKCB.BhytChitra = Utility.DecimaltoDbnull(objDangkyKCB.DonGia) *
                                                               Utility.DecimaltoDbnull(objLuotkham.PtramBhyt) / 100;
                                        objDangkyKCB.BnhanChitra = Utility.DecimaltoDbnull(objDangkyKCB.DonGia, 0) -
                                                                  Utility.DecimaltoDbnull(objDangkyKCB.BhytChitra, 0);
                                    }
                                    else
                                    {
                                        objDangkyKCB.BhytChitra = 0;
                                        objDangkyKCB.BnhanChitra = Utility.DecimaltoDbnull(objDangkyKCB.DonGia, 0);
                                    }
                                }
                            }
                            else
                            {
                                //nếu đã thanh toán thì chỉ xử lý khi đối tượng đang ở trạng thái nội trú.
                                if (Utility.ByteDbnull(objLuotkham.TrangthaiNoitru,0) > 0)
                                {
                                    if (THU_VIEN_CHUNG.IsBaoHiem(objLuotkham.IdLoaidoituongKcb))
                                    {
                                        //Đối tượng mới là BHYT thì kiểm tra đổi các dịch vụ có giá DV về tự túc.
                                        //Các dịch vụ có giá khác DV thì để nguyên(để nếu đối tượng cũ là BHYT thì ko bị thay đổi giá trị tự túc)
                                        if (objDangkyKCB.MadoituongGia == "DV")
                                            objDangkyKCB.TuTuc = 1;
                                    }
                                    else//Nếu đối tượng từ BHYT chuyển sang dịch vụ thì chuyển thành ko tự túc hết
                                    {
                                        if (objLuotkham.MaDoituongKcb != objLuotkhamCu.MaDoituongKcb)//Mới=DV, Cũ=BHYT. Còn lại ko làm gì cả
                                            objDangkyKCB.TuTuc = 0;
                                    }
                                }
                                else
                                    continue;
//.........这里部分代码省略.........

        /// <summary>
        /// Lấy toàn bộ dữ liệu CLS giống phần chỉ định CLS sau đó tính giá dựa trên dữ liệu lấy được đó. Thay vì select lại từ các bảng quan hệ
        /// </summary>
        /// <param name="objLuotkham"></param>
        /// <returns></returns>
        private static ActionResult CapnhatChiphiCLS(KcbLuotkham objLuotkham, KcbLuotkham objLuotkhamCu)
        {
            using (var Scope = new TransactionScope())
            {
                DataTable m_dtServiceDetail = new KCB_CHIDINH_CANLAMSANG().LaydanhsachCLS_chidinh(objLuotkham.MaDoituongKcb, objLuotkham.TrangthaiNoitru, Utility.ByteDbnull(objLuotkham.GiayBhyt, 0), -1, Utility.Int32Dbnull(objLuotkham.DungTuyen.Value, 0), objLuotkham.MaKhoaThuchien, "-GOI,-TIEN");//Ko lấy dữ liệu liên quan đến gói dịch vụ+tiền phí phụ thêm

                KcbChidinhclCollection lstChidinh=
                    new Select()
                    .From(KcbChidinhcl.Schema)
                    .Where(KcbChidinhcl.Columns.MaLuotkham).IsEqualTo(objLuotkham.MaLuotkham)
                    .And(KcbChidinhcl.Columns.IdBenhnhan).IsEqualTo(objLuotkham.IdBenhnhan)
                    .ExecuteAsCollection<KcbChidinhclCollection>();
                List<long> lstID = lstChidinh.Select(c => c.IdChidinh).Distinct().ToList<long>();
                if (lstID.Count <= 0)
                {
                    Scope.Complete();
                    return ActionResult.Success;
                }
                KcbChidinhclsChitietCollection lstChitiet = new Select().From(KcbChidinhclsChitiet.Schema)
                    .Where(KcbChidinhclsChitiet.Columns.IdChidinh).In(lstID)
                .ExecuteAsCollection<KcbChidinhclsChitietCollection>();
                if (lstChitiet.Count > 0)
                {
                    //Kiểm tra nếu đối tượng ngoại trú đã có dịch vụ thanh toán-->Ko cho phép chuyển nữa
                    if (Utility.ByteDbnull(objLuotkham.TrangthaiNoitru,0)<=0 && lstChitiet.Where(c => c.TrangthaiThanhtoan > 0).Any())
                    {
                        Scope.Complete();
                        return ActionResult.Cancel;
                    }
                    bool saveParent = false;
                    foreach (KcbChidinhcl objChidinh in lstChidinh)
                    {

                            objChidinh.IdLichsuDoituongKcb = objLuotkham.IdLichsuDoituongKcb;
                            objChidinh.MatheBhyt = objLuotkham.MatheBhyt;
                            objChidinh.MaDoituongKcb = objLuotkham.MaDoituongKcb;
                            objChidinh.IdLoaidoituongKcb = objLuotkham.IdLoaidoituongKcb;
                            objChidinh.IdDoituongKcb = objLuotkham.IdDoituongKcb;
                            foreach (KcbChidinhclsChitiet objChidinhChitiet in lstChitiet.Where(c => c.IdChidinh.Equals(objChidinh.IdChidinh)))
                            {
                                if (Utility.Int32Dbnull(objChidinhChitiet.IdGoi, -1) <=0)
                                {
                                    objChidinhChitiet.IdDoituongKcb = objLuotkham.IdDoituongKcb;
                                    if (objChidinhChitiet.TrangthaiThanhtoan == 0)//Chưa thanh toán
                                    {
                                        saveParent = true;
                                        objChidinhChitiet.MadoituongGia = objLuotkham.MaDoituongKcb;
                                        objChidinhChitiet.PtramBhyt = objLuotkham.PtramBhyt;
                                        objChidinhChitiet.PtramBhytGoc = objLuotkham.PtramBhytGoc;
                                        DataRow[] arrDr = m_dtServiceDetail.Select(KcbChidinhclsChitiet.Columns.IdChitietdichvu + "=" + Utility.Int32Dbnull(objChidinhChitiet.IdChitietdichvu, -1));
                                        if (arrDr.Length > 0)
                                        {

                                            objChidinhChitiet.PtramBhyt = objLuotkham.PtramBhyt;
                                            objChidinhChitiet.DonGia = Utility.DecimaltoDbnull(arrDr[0][KcbChidinhclsChitiet.Columns.DonGia], 0);
                                            objChidinhChitiet.GiaDanhmuc = Utility.DecimaltoDbnull(arrDr[0][KcbChidinhclsChitiet.Columns.DonGia], 0);
                                            objChidinhChitiet.TuTuc = Utility.ByteDbnull(arrDr[0][KcbChidinhclsChitiet.Columns.TuTuc], 0);
                                            objChidinhChitiet.PhuThu = Utility.DecimaltoDbnull(arrDr[0][KcbChidinhclsChitiet.Columns.PhuThu], 0);
                                            objChidinhChitiet.NguoiSua = globalVariables.UserName;
                                            objChidinhChitiet.NgaySua = DateTime.Now;
                                            TinhCLS.GB_TinhPhtramBHYT(objChidinhChitiet, objLuotkham, Utility.Byte2Bool(objChidinh.Noitru), Utility.DecimaltoDbnull(objLuotkham.PtramBhyt));

                                        }
                                        else//Rất khó nhảy vào nhánh này trừ phi lỗi dữ liệu đặc biệt nào đó
                                        {
                                            CLS_GIATHEO_KHOAKCB = THU_VIEN_CHUNG.Laygiatrithamsohethong("CLS_GIATHEO_KHOAKCB", "0", true) == "1";
                                            QheDoituongDichvucl _Items = THU_VIEN_CHUNG.LayQheDoituongCLS(objLuotkham.MaDoituongKcb, objChidinhChitiet.IdChitietdichvu, objLuotkham.MaKhoaThuchien, CLS_GIATHEO_KHOAKCB);
                                            if (_Items != null)
                                            {
                                                objChidinhChitiet.DonGia = Utility.DecimaltoDbnull(_Items.DonGia);
                                                objChidinhChitiet.PhuThu = (Utility.isTrue(objLuotkham.DungTuyen) ? Utility.DecimaltoDbnull(_Items.PhuthuDungtuyen) : Utility.DecimaltoDbnull(_Items.PhuthuTraituyen));
                                                objChidinhChitiet.TuTuc = 0;
                                                objChidinhChitiet.IdDoituongKcb = objLuotkham.IdDoituongKcb;
                                                objChidinhChitiet.PtramBhyt = objLuotkham.PtramBhyt;
                                            }
                                            else
                                            {
                                                _Items = THU_VIEN_CHUNG.LayQheDoituongCLS("DV", objChidinhChitiet.IdChitietdichvu, objLuotkham.MaKhoaThuchien, CLS_GIATHEO_KHOAKCB);
                                                if (_Items != null)
                                                {
                                                    objChidinhChitiet.MadoituongGia = "DV";
                                                    objChidinhChitiet.DonGia = Utility.DecimaltoDbnull(_Items.DonGia);
                                                    objChidinhChitiet.PhuThu = (Utility.isTrue(objLuotkham.DungTuyen) ? Utility.DecimaltoDbnull(_Items.PhuthuDungtuyen) : Utility.DecimaltoDbnull(_Items.PhuthuTraituyen));
                                                    objChidinhChitiet.TuTuc = 1;
                                                    objChidinhChitiet.IdDoituongKcb = objLuotkham.IdDoituongKcb;
                                                    objChidinhChitiet.PtramBhyt = 0;
                                                }
                                            }
                                            DmucDichvuclsChitiet _DmucDichvuclsChitiet = DmucDichvuclsChitiet.FetchByID(objChidinhChitiet.IdChitietdichvu);
                                            if (_DmucDichvuclsChitiet != null && Utility.Int32Dbnull(_DmucDichvuclsChitiet.TuTuc, 0) == 1)
                                                objChidinhChitiet.TuTuc = 1;//Ke ca co trong bang quan he
                                            if (objLuotkham.IdLoaidoituongKcb == 1)//Đối tượng dịch vụ-->ko cần phải đánh dấu tự túc
                                            {
                                                objChidinhChitiet.TuTuc = 0;
                                                objChidinhChitiet.PhuThu = 0;
//.........这里部分代码省略.........

        private static ActionResult CapnhatBuonggiuong(KcbLuotkham objLuotkham, KcbLuotkham objLuotkhamCu)
        {
            using (var Scope = new TransactionScope())
            {

                NoitruPhanbuonggiuongCollection lstBuonggiuong =
                    new Select()
                    .From(NoitruPhanbuonggiuong.Schema)
                    .Where(NoitruPhanbuonggiuong.Columns.MaLuotkham).IsEqualTo(objLuotkham.MaLuotkham)
                    .And(NoitruPhanbuonggiuong.Columns.IdBenhnhan).IsEqualTo(objLuotkham.IdBenhnhan)
                    .ExecuteAsCollection<NoitruPhanbuonggiuongCollection>();
                if (lstBuonggiuong.Count > 0)
                {
                    //Kiểm tra nếu đối tượng ngoại trú đã có dịch vụ thanh toán-->Ko cho phép chuyển nữa
                    if (Utility.ByteDbnull(objLuotkham.TrangthaiNoitru,0) <= 0 && lstBuonggiuong.Where(c => c.TrangthaiThanhtoan > 0).Any())
                    {
                        Scope.Complete();
                        return ActionResult.Cancel;
                    }
                    foreach (NoitruPhanbuonggiuong objBG in lstBuonggiuong)
                    {
                        if (Utility.Int32Dbnull(objBG.IdGoi, -1) <=0)
                        {
                            objBG.IdLichsuDoituongKcb = objLuotkham.IdLichsuDoituongKcb;
                            noitru_nhapvien.LayThongTinGia(objBG, objLuotkham);
                            objBG.Save();
                        }
                    }
                }
                Scope.Complete();
                return ActionResult.Success;
            }
        }