public TreasuryReportViewModel GetItemId(bool IsCustody, string DEPT_ITEM, string UserBranch) { var result = new TreasuryReportViewModel(); List <SelectOption> jobProject = new List <SelectOption>(); //庫存表名稱 List <SelectOption> vName = new List <SelectOption>(); //股票編號 List <SelectOption> TRAD = new List <SelectOption>(); //交易對象 List <SelectOption> dept = new List <SelectOption>(); //權責部門 List <SelectOption> branch = new List <SelectOption>(); //權責科別 var strs = new List <string>(); using (TreasuryDBEntities db = new TreasuryDBEntities()) { var deps = new Treasury.Web.Service.Actual.Common().GetDepts(); var items = new List <string>() { Ref.TreaItemType.D1012.ToString(), // 空白票據項目 用於條件判斷 Ref.TreaItemType.D1019.ToString(), // 其他物品項目 用於條件判斷 }; var bill = Ref.TreaItemType.D1012.ToString(); //if (IsCustody) //{ //jobProject = db.TREA_ITEM.AsNoTracking() // 抓資料表的所有資料 // .Where(x => x.ITEM_OP_TYPE == "3" && // x.IS_DISABLED == "N" && // !items.Contains(x.ITEM_ID)) //條件 // .AsEnumerable().Select(x => new SelectOption() // { // Value = x.ITEM_ID, // Text = x.ITEM_DESC // }).ToList(); //印章 // if(db.ITEM_SEAL.AsNoTracking()//抓取在庫的所有資料 //.Where(x => x.INVENTORY_STATUS == "1") //.Select(x => x.TREA_ITEM_NAME) //.Distinct() //.OrderBy(x => x).ToList().Count > 0 ) // strs.Add(Ref.TreaItemType.D1013.ToString()); //} //else //{ //印章 strs = db.ITEM_SEAL.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch, !IsCustody) .Select(x => x.TREA_ITEM_NAME) .Distinct() .OrderBy(x => x).ToList(); if (db.ITEM_DEP_ORDER_M.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch, !IsCustody) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList().Count > 0) { strs.Add(Ref.TreaItemType.D1013.ToString()); } if (db.ITEM_REAL_ESTATE.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch, !IsCustody) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList().Count > 0) { strs.Add(Ref.TreaItemType.D1014.ToString()); } if (db.ITEM_STOCK.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch, !IsCustody) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList().Count > 0) { strs.Add(Ref.TreaItemType.D1015.ToString()); } if (db.ITEM_REFUNDABLE_DEP.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch, !IsCustody) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList().Count > 0) { strs.Add(Ref.TreaItemType.D1016.ToString()); } if (db.ITEM_DEP_RECEIVED.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch, !IsCustody) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList().Count > 0) { strs.Add(Ref.TreaItemType.D1017.ToString()); } if (db.ITEM_IMPO.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch, !IsCustody) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList().Count > 0) { strs.Add(Ref.TreaItemType.D1018.ToString()); } if (db.ITEM_OTHER.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch, !IsCustody) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList().Count > 0) { strs.Add(Ref.TreaItemType.D1019.ToString()); } if (db.ITEM_CA.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch, !IsCustody) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList().Count > 0) { strs.Add(Ref.TreaItemType.D1024.ToString()); } // } jobProject = db.TREA_ITEM.AsNoTracking() // 抓資料表的所有資料 .Where(x => x.ITEM_OP_TYPE == "3" && x.IS_DISABLED == "N" && !items.Contains(x.ITEM_ID) && strs.Contains(x.ITEM_ID)) //條件 .AsEnumerable().Select(x => new SelectOption() { Value = x.ITEM_ID, Text = x.ITEM_DESC }).ToList(); if (strs.Count > 0) { string firstItem = strs.FirstOrDefault(); Ref.TreaItemType _firstItem; if (System.Enum.TryParse(firstItem, false, out _firstItem)) { switch (_firstItem) { case Ref.TreaItemType.D1008: //印章 case Ref.TreaItemType.D1009: case Ref.TreaItemType.D1010: case Ref.TreaItemType.D1011: var item = firstItem; strs = db.ITEM_SEAL.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1" && x.TREA_ITEM_NAME == item) .Select(x => x.CHARGE_DEPT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1013: //定期存單 strs = db.ITEM_DEP_ORDER_M.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Select(x => x.CHARGE_DEPT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1014: //不動產 strs = db.ITEM_REAL_ESTATE.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Select(x => x.CHARGE_DEPT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1015: //股票 strs = db.ITEM_STOCK.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Select(x => x.CHARGE_DEPT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1016: //存出保證金 strs = db.ITEM_REFUNDABLE_DEP.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Select(x => x.CHARGE_DEPT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1017: //存入保證金 strs = db.ITEM_DEP_RECEIVED.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Select(x => x.CHARGE_DEPT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1018: //重要物品 strs = db.ITEM_IMPO.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Select(x => x.CHARGE_DEPT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1019: //其他物品 strs = db.ITEM_OTHER.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Select(x => x.CHARGE_DEPT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1024: //電子憑證 strs = db.ITEM_CA.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Select(x => x.CHARGE_DEPT) .Distinct() .OrderBy(x => x).ToList(); break; } } } //var ITEM_SEALs= db.ITEM_SEAL.AsNoTracking()//抓取在庫的所有資料 //.Where(x => x.INVENTORY_STATUS == "1").ToList(); List <SelectOption> _dept = new List <SelectOption>(); List <SelectOption> _branch = new List <SelectOption>(); _dept = strs.Select(x => x).Distinct() .OrderBy(x => x) .Select(x => new SelectOption() { Value = x, Text = deps.FirstOrDefault(y => y.DPT_CD == x)?.DPT_NAME }).ToList(); var vNames = new Stock().GetStockName(); vNames.RemoveAt(0); vNames.Insert(0, new SelectOption() { Value = "All", Text = "All" }); result.vName = vNames; var vBook_Nos = new Estate().GetBuildName(); vBook_Nos.RemoveAt(0); vBook_Nos.Insert(0, new SelectOption() { Value = "All", Text = "All" }); result.vBook_No = vBook_Nos; TRAD = new Deposit().GetTRAD_Partners(); TRAD.Insert(0, new SelectOption() { Value = "All", Text = "All" }); result.vTRAD_Partners = TRAD; result.vdept = _dept; result.vjobProject = jobProject; return(result); } }
public List <SelectOption> getSECT(string DEPT_ITEM, Ref.TreaItemType type, bool IsCustody, string UserBranch) { var result = new List <SelectOption>(); var strs = new List <string>(); var deps = new Treasury.Web.Service.Actual.Common().GetDepts(); using (TreasuryDBEntities db = new TreasuryDBEntities()) { if (IsCustody) { switch (type) { case Ref.TreaItemType.D1008: //印章 case Ref.TreaItemType.D1009: case Ref.TreaItemType.D1010: case Ref.TreaItemType.D1011: var item = type.ToString(); strs = db.ITEM_SEAL.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1" && x.TREA_ITEM_NAME == item) .Where(x => x.CHARGE_DEPT == DEPT_ITEM) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1013: //定期存單 strs = db.ITEM_DEP_ORDER_M.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1014: //不動產 strs = db.ITEM_REAL_ESTATE.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1015: //股票 strs = db.ITEM_STOCK.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1016: //存出保證金 strs = db.ITEM_REFUNDABLE_DEP.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1017: //存入保證金 strs = db.ITEM_DEP_RECEIVED.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1018: //重要物品 strs = db.ITEM_IMPO.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1019: //其他物品 strs = db.ITEM_OTHER.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1024: //電子憑證 strs = db.ITEM_CA.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; } } else { switch (type) { case Ref.TreaItemType.D1008: //印章 case Ref.TreaItemType.D1009: case Ref.TreaItemType.D1010: case Ref.TreaItemType.D1011: var item = type.ToString(); strs = db.ITEM_SEAL.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1" && x.TREA_ITEM_NAME == item) .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1013: //定期存單 strs = db.ITEM_DEP_ORDER_M.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1014: //不動產 strs = db.ITEM_REAL_ESTATE.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1015: //股票 strs = db.ITEM_STOCK.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1016: //存出保證金 strs = db.ITEM_REFUNDABLE_DEP.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1017: //存入保證金 strs = db.ITEM_DEP_RECEIVED.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1018: //重要物品 strs = db.ITEM_IMPO.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1019: //其他物品 strs = db.ITEM_OTHER.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; case Ref.TreaItemType.D1024: //電子憑證 strs = db.ITEM_CA.AsNoTracking() //抓取在庫的所有資料 .Where(x => x.INVENTORY_STATUS == "1") .Where(x => x.CHARGE_DEPT == DEPT_ITEM && x.CHARGE_SECT == UserBranch) .Select(x => x.CHARGE_SECT) .Distinct() .OrderBy(x => x).ToList(); break; } } if (strs.Count <= 1 && strs.FirstOrDefault()?.Trim() == "") { } else { result = strs .Select(x => new SelectOption() { Value = x, Text = deps.FirstOrDefault(y => y.DPT_CD != null && y.DPT_CD.Trim() == x)?.DPT_NAME }).ToList(); } } return(result); }
public TreasuryKeyCheckViewModel GetItemId() { var result = new TreasuryKeyCheckViewModel(); var emps = new Treasury.Web.Service.Actual.Common().GetEmps(); var All = new SelectOption() { Text = "All", Value = "All" }; List <SelectOption> CONTROL_MODE = new List <SelectOption>(); //管控模式 List <SelectOption> CUSTODY_MODE = new List <SelectOption>(); //保管方式 List <SelectOption> EMP_NAME = new List <SelectOption>(); //保管人 List <SelectOption> AGENT_NAME = new List <SelectOption>(); //代理人 using (TreasuryDBEntities db = new TreasuryDBEntities()) { CONTROL_MODE = db.SYS_CODE.AsNoTracking() .Where(x => x.CODE_TYPE == "CONTROL_MODE") .OrderBy(x => x.ISORTBY) .AsEnumerable().Select(x => new SelectOption() { Value = x.CODE, Text = x.CODE_VALUE }).ToList(); CUSTODY_MODE = db.SYS_CODE.AsNoTracking() .Where(x => x.CODE_TYPE == "CUSTODY_MODE") .OrderBy(x => x.ISORTBY) .AsEnumerable().Select(x => new SelectOption() { Value = x.CODE, Text = x.CODE_VALUE }).ToList(); var _CRTI_EMP = db.CODE_ROLE_TREA_ITEM.AsNoTracking()//找出保管順序=1的角色代碼 .Where(x => x.CUSTODY_ORDER == 1) .Select(x => x.ROLE_ID) .Distinct() .ToList(); var _CUR = db.CODE_USER_ROLE.AsNoTracking() .Where(x => _CRTI_EMP.Contains(x.ROLE_ID)) .Select(x => x.USER_ID) .ToList(); EMP_NAME = _CUR.Distinct() .OrderBy(x => x) .Select(x => new SelectOption() { Value = x, Text = emps.FirstOrDefault(y => y.USR_ID == x)?.EMP_NAME?.Trim() }).ToList(); EMP_NAME.Insert(0, All); var _CRTI_AGENT = db.CODE_ROLE_TREA_ITEM.AsNoTracking()//找出保管順序=2的角色代碼 .Where(x => x.CUSTODY_ORDER == 2) .Select(x => x.ROLE_ID) .ToList(); var _CURs = db.CODE_USER_ROLE.AsNoTracking() .Where(x => _CRTI_AGENT.Contains(x.ROLE_ID)) .Select(x => x.USER_ID).ToList(); AGENT_NAME = _CURs.Distinct() .OrderBy(x => x) .Select(x => new SelectOption() { Value = x, Text = emps.FirstOrDefault(y => y.USR_ID == x)?.EMP_NAME?.Trim() }).ToList(); AGENT_NAME.Insert(0, All); } result.CONTROL_MODE = CONTROL_MODE; result.CUSTODY_MODE = CUSTODY_MODE; result.EMP_NAME = EMP_NAME; result.AGENT_NAME = AGENT_NAME; return(result); }