/// <summary>
/// 以AD帳號查詢使用者相關資訊
/// </summary>
/// <param name="USR_ID"></param>
/// <returns></returns>
public V_EMPLY2 qryByUsrId(String USR_ID, DB_INTRAEntities db)
{
V_EMPLY2 oaEmp = db.V_EMPLY2
.Where(x => x.USR_ID == USR_ID).FirstOrDefault();
return(oaEmp);
}
public JsonResult qryUserRole(string userId)
{
CodeUserRoleDao CodeUserRoleDao = new CodeUserRoleDao();
List <CodeUserRoleModel> rows = CodeUserRoleDao.qryByUserID(userId);
using (DB_INTRAEntities dbIntra = new DB_INTRAEntities())
{
Dictionary <string, string> userNameMap = new Dictionary <string, string>();
OaEmpDao oaEmpDao = new OaEmpDao();
string createUid = "";
foreach (CodeUserRoleModel d in rows)
{
createUid = StringUtil.toString(d.createUid);
if (!"".Equals(createUid))
{
if (!userNameMap.ContainsKey(createUid))
{
userNameMap = oaEmpDao.qryUsrName(userNameMap, createUid, dbIntra);
}
d.createUid = createUid + " " + userNameMap[createUid];
}
}
}
var jsonData = new { success = true, roleList = rows };
return(Json(jsonData, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 畫面初始
/// </summary>
/// <returns></returns>
public Tuple <List <SelectOption>, List <SelectOption> > FirstDropDown()
{
List <SelectOption> CHARGE_DEPT = new List <SelectOption>();
List <SelectOption> TREA_ITEM = new List <SelectOption>();
using (DB_INTRAEntities dbIntra = new DB_INTRAEntities())
{
var _VW_OA_DEPT = dbIntra.VW_OA_DEPT.AsNoTracking();
var _Dpt_types = new List <string>()
{
"03", "02"
};
CHARGE_DEPT = _VW_OA_DEPT.Where(x => _Dpt_types.Contains(x.Dpt_type))
.AsEnumerable()
.Select(x => new SelectOption()
{
Value = x.DPT_CD?.Trim(),
Text = x.DPT_NAME?.Trim()
}).ToList();
}
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
TREA_ITEM = db.TREA_ITEM.AsNoTracking()
//.Where(x => x.DAILY_FLAG == "N")
.Where(x => x.IS_DISABLED == "N" && x.ITEM_OP_TYPE == "3") //「入庫作業類型=3」且啟用中的存取項目
.OrderBy(x => x.ITEM_ID)
.AsEnumerable()
.Select(x => new SelectOption()
{
Value = x.ITEM_ID,
Text = x.ITEM_DESC
}).ToList();
}
return(new Tuple <List <SelectOption>, List <SelectOption> >(CHARGE_DEPT, TREA_ITEM));
}
protected void SetDetail(string vTreaRegisterId, string vUser_Id)
{
_REC = new REC();
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
//取得開庫紀錄檔
var _TOR = db.TREA_OPEN_REC.AsNoTracking()
.FirstOrDefault(x => x.TREA_REGISTER_ID == vTreaRegisterId);
//金庫管理者
var UserData = new V_EMPLY2();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
UserData = dbINTRA.V_EMPLY2.AsNoTracking().FirstOrDefault(x => x.USR_ID == vUser_Id);
}
//開庫類型
var OpenTreaType = db.SYS_CODE.AsNoTracking()
.Where(x => x.CODE_TYPE == "OPEN_TREA_TYPE").ToList();
if (_TOR != null)
{
_REC.SYS_DATE = DateTime.Now.ToString("yyyy/MM/dd");
_REC.TREA_REGISTER_ID = _TOR.TREA_REGISTER_ID;
_REC.USER_NAME = UserData.EMP_NAME;
_REC.ACTUAL_PUT_TIME = string.IsNullOrEmpty(_TOR.ACTUAL_PUT_TIME.ToString()) ? null : DateTime.Parse(_TOR.ACTUAL_PUT_TIME.ToString()).ToString("HH:mm");
_REC.ACTUAL_GET_TIME = string.IsNullOrEmpty(_TOR.ACTUAL_GET_TIME.ToString()) ? null : DateTime.Parse(_TOR.ACTUAL_GET_TIME.ToString()).ToString("HH:mm");
_REC.OPEN_TREA_TYPE = OpenTreaType.FirstOrDefault(x => x.CODE == _TOR.OPEN_TREA_TYPE).CODE_VALUE;
}
}
}
/// <summary>
/// 輸入單位代碼，查詢相關資訊
/// </summary>
/// <param name="DPT_CD"></param>
/// <returns></returns>
public VW_OA_DEPT qryByDptCd(String DPT_CD)
{
DB_INTRAEntities context = new DB_INTRAEntities();
VW_OA_DEPT oaDept = context.VW_OA_DEPT
.Where(x => x.DPT_CD == DPT_CD).FirstOrDefault();
return(oaDept);
}
/// <summary>
/// 獲取 部門資料
/// </summary>
/// <returns></returns>
public List <VW_OA_DEPT> GetDepts()
{
var depts = new List <VW_OA_DEPT>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().ToList();
}
return(depts);
}
/// <summary>
/// 回傳 部門 資料
/// </summary>
/// <param name="DPT_CD"></param>
/// <returns></returns>
public VW_OA_DEPT getDept(string DPT_CD)
{
var result = new VW_OA_DEPT();
using (DB_INTRAEntities db = new DB_INTRAEntities())
{
result = db.VW_OA_DEPT.FirstOrDefault(x => x.DPT_CD == DPT_CD);
}
return(result);
}
/// <summary>
/// 獲取 員工資料
/// </summary>
/// <returns></returns>
public List <V_EMPLY2> GetEmps()
{
var emps = new List <V_EMPLY2>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
emps = dbINTRA.V_EMPLY2.AsNoTracking().Where(x => x.USR_ID != null).ToList();
}
return(emps);
}
public JsonResult LoadData(string codeRole, string roleAuthType, string isDIsabled, string vMemo, string cUpdUserID)
{
CodeRoleDao codeRoleDao = new CodeRoleDao();
OaEmpDao oaEmpDao = new OaEmpDao();
List <RoleMgrModel> rows = new List <RoleMgrModel>();
rows = codeRoleDao.roleMgrQry(codeRole, roleAuthType, isDIsabled, vMemo, cUpdUserID);
Dictionary <string, string> userNameMap = new Dictionary <string, string>();
string userUId = "";
string userFId = "";
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (DB_INTRAEntities dbIntra = new DB_INTRAEntities())
{
foreach (RoleMgrModel role in rows)
{
userUId = StringUtil.toString(role.cUpdUserID);
userFId = StringUtil.toString(role.freezeUid);
if (!"".Equals(userUId))
{
if (!userNameMap.ContainsKey(userUId))
{
userNameMap = oaEmpDao.qryUsrName(userNameMap, userUId, dbIntra);
}
role.cUpdUserID = userUId + " " + userNameMap[userUId];
}
if (!"".Equals(userFId))
{
if (!userNameMap.ContainsKey(userFId))
{
userNameMap = oaEmpDao.qryUsrName(userNameMap, userFId, dbIntra);
}
role.freezeUid = userFId + " " + userNameMap[userFId];
}
}
}
}
var jsonData = new { success = true, rows };
return(Json(jsonData, JsonRequestBehavior.AllowGet));
}
protected void SetDetail(string aply_No)
{
var depts = new List <VW_OA_DEPT>();
var emps = new List <V_EMPLY2>();
_REC = new REC();
//var sys
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
emps = dbINTRA.V_EMPLY2.AsNoTracking().ToList();
}
}
public JsonResult LoadData(String cReviewType)
{
AuthApprDao authApprDao = new AuthApprDao();
List <AuthReviewModel> rows = new List <AuthReviewModel>();
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
try
{
//查出待覆核的資料
rows = authApprDao.qryAuthReview("R", "1", db);
}
catch (Exception e)
{
logger.Error("其它錯誤：" + e.ToString());
return(Json(new { success = false, err = "其它錯誤，請洽系統管理員!!" }));
}
}
}
using (DB_INTRAEntities dbIntra = new DB_INTRAEntities())
{
Dictionary <string, string> userNameMap = new Dictionary <string, string>();
OaEmpDao oaEmpDao = new OaEmpDao();
string createUid = "";
foreach (AuthReviewModel d in rows)
{
createUid = StringUtil.toString(d.createUid);
if (!"".Equals(createUid))
{
if (!userNameMap.ContainsKey(createUid))
{
userNameMap = oaEmpDao.qryUsrName(userNameMap, createUid, dbIntra);
}
d.createUid = createUid + " " + userNameMap[createUid];
}
}
}
var jsonData = new { success = true, rows };
return(Json(jsonData, JsonRequestBehavior.AllowGet));
}
public UserMgrModel getUserOaData(UserMgrModel user, DB_INTRAEntities db)
{
V_EMPLY2 oaEmp = db.V_EMPLY2
.Where(x => x.USR_ID == user.cUserID).FirstOrDefault();
if (oaEmp != null)
{
if (!"".Equals(oaEmp.EMP_NO))
{
user.cUserName = StringUtil.toString(oaEmp.EMP_NAME);
user.cWorkUnitCode = StringUtil.toString(oaEmp.DPT_CD);
user.cWorkUnitDesc = StringUtil.toString(oaEmp.DPT_NAME);
}
}
return(user);
}
/// <summary>
/// 取得 人員基本資料
/// </summary>
/// <param name="cUserID"></param>
/// <returns></returns>
public BaseUserInfoModel GetUserInfo(string cUserID)
{
BaseUserInfoModel user = new BaseUserInfoModel();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
var _emply = dbINTRA.V_EMPLY2.AsNoTracking().FirstOrDefault(x => x.USR_ID == cUserID);
if (_emply != null)
{
user.EMP_ID = cUserID;
user.EMP_Name = _emply.EMP_NAME?.Trim();
user.DPT_ID = _emply.DPT_CD?.Trim();
user.DPT_Name = _emply.DPT_NAME?.Trim();
}
}
return(user);
}
/// <summary>
/// 申請單位 變更時 變更申請人
/// </summary>
/// <param name="DPT_CD">單位</param>
/// <returns></returns>
public List <SelectOption> ChangeUnit(string DPT_CD)
{
List <SelectOption> results = new List <SelectOption>();
using (DB_INTRAEntities db = new DB_INTRAEntities())
{
results = db.V_EMPLY2.AsNoTracking()
.Where(x => x.DPT_CD == DPT_CD)
.AsEnumerable()
.Select(x => new SelectOption()
{
Value = x.USR_ID,
Text = $@"{x.USR_ID}({x.EMP_NAME})"
}).ToList();
}
return(results);
}
/// <summary>
/// 回傳 Emply 資料
/// </summary>
/// <param name="USR_ID"></param>
/// <returns></returns>
public List <V_EMPLY2> getEmply(string USR_ID = null)
{
var result = new List <V_EMPLY2>();
using (DB_INTRAEntities db = new DB_INTRAEntities())
{
if (string.IsNullOrEmpty(USR_ID))
{
result = db.V_EMPLY2.ToList();
}
else
{
result = db.V_EMPLY2.Where(x => x.USR_ID == USR_ID).ToList();
}
}
return(result);
}
public List <SelectOption> selectCodeUser(string ROLE_ID = null)
{
List <SelectOption> results = new List <SelectOption>()
{
new SelectOption()
{
Text = "請選擇", Value = " "
}
};
List <string> userIds = new List <string>();
using (dbTreasuryEntities db = new dbTreasuryEntities())
{
if (string.IsNullOrWhiteSpace(ROLE_ID))
{
userIds = db.CODE_USER.AsNoTracking().Where(x => x.IS_DISABLED != "Y").Select(x => x.USER_ID).ToList();
}
else
{
userIds = db.CODE_ROLE.AsNoTracking().Where(x => x.ROLE_ID == ROLE_ID)
//.Join(db.CODE_USER_ROLE.AsNoTracking(),
//x => x.ROLE_ID,
//y => y.ROLE_ID,
//(x, y) => y)
.Join(db.CODE_USER.AsNoTracking().Where(x => x.IS_DISABLED != "Y"),
x => x.LAST_UPDATE_UID,
y => y.USER_ID,
(x, y) => y)
.Select(x => x.USER_ID).ToList();
}
}
using (DB_INTRAEntities intra = new DB_INTRAEntities())
{
var emps = intra.V_EMPLY2.AsNoTracking().ToList();
foreach (var userId in userIds)
{
results.Add(new SelectOption()
{
Value = userId,
Text = $@"{userId} {emps.FirstOrDefault(x => x.USR_ID == userId)?.EMP_NAME}"
});
}
}
return(results);
}
/**
* 查詢出所有使用者資料(for畫面下拉選單使用)
**/
public SelectList loadSelectList()
{
dbTreasuryEntities context = new dbTreasuryEntities();
List <UserMgrModel> result1 = (from user in context.CODE_USER
select new UserMgrModel
{
cUserID = user.USER_ID.Trim()
}
).ToList();
OaEmpDao oaEmpDao = new OaEmpDao();
using (DB_INTRAEntities dbIntra = new DB_INTRAEntities())
{
foreach (UserMgrModel user in result1)
{
try
{
user.cUserName = user.cUserID + " " + StringUtil.toString(oaEmpDao.qryByUsrId(user.cUserID, dbIntra).EMP_NAME);
}
catch (Exception e)
{
}
}
}
var items = new SelectList
(
items: result1,
dataValueField: "cUserID",
dataTextField: "cUserName",
selectedValue: (object)null
);
return(items);
}
/// <summary>
/// 金庫物品存取申請覆核作業 覆核查詢
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public List <TreasuryAccessApprSearchDetailViewModel> GetApprSearchDetail(TreasuryAccessApprSearchViewModel data)
{
List <TreasuryAccessApprSearchDetailViewModel> result = new List <TreasuryAccessApprSearchDetailViewModel>();
List <string> dptList = new List <string>()
{
data.vCreateUnit
};
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var depts = GetDepts();
var emps = GetEmps();
var _CUser = emps.FirstOrDefault(x => x.USR_ID == data.vCreateUid);
if (_CUser != null)
{
using (DB_INTRAEntities dbIntra = new DB_INTRAEntities())
{
var _V_EMPLY2_DPT_CD = dbIntra.V_EMPLY2.AsNoTracking()
.Where(x => x.DPT_HEAD == _CUser.EMP_NO).Select(x => x.DPT_CD).Distinct();
_V_EMPLY2_DPT_CD.ToList().ForEach(x => {
dptList.Add(x?.Trim());
});
}
}
var treaItems = db.TREA_ITEM.AsNoTracking().Where(x => x.ITEM_OP_TYPE == "3").ToList();
DateTime?_vAPLY_DT_S = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_S);
DateTime?_vAPLY_DT_E = TypeTransfer.stringToDateTimeN(data.vAPLY_DT_E).DateToLatestTime();
result = db.TREA_APLY_REC.AsNoTracking()
.Where(x => x.APLY_DT >= _vAPLY_DT_S, _vAPLY_DT_S != null) //申請日期(起)
.Where(x => x.APLY_DT <= _vAPLY_DT_E, _vAPLY_DT_E != null) //申請日期(迄)
.Where(x => x.APLY_NO == data.vAPLY_NO, !data.vAPLY_NO.IsNullOrWhiteSpace()) //申請單號
//.Where(x => x.CREATE_UNIT == data.vCreateUnit && apprStatus.Contains(x.APLY_STATUS)) //相同部門 & 符合狀態 的資料
.Where(x => dptList.Distinct().Contains(x.CREATE_UNIT) && apprStatus.Contains(x.APLY_STATUS), dptList.Any()) //相同部門含主管 & 符合狀態 的資料
.AsEnumerable()
.Select(x => TreaAplyRecToTAASDViewModel(data.vCreateUid, x, treaItems, depts, emps)).ToList();
}
return(result);
}
/// <summary>
/// 查詢申請單紀錄資料by單號
/// </summary>
/// <param name="aplyNo"></param>
/// <returns></returns>
public TreasuryAccessViewModel GetByAplyNo(string aplyNo)
{
var result = new TreasuryAccessViewModel();
var depts = new List <VW_OA_DEPT>();
var emps = new List <V_EMPLY2>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().ToList();
emps = dbINTRA.V_EMPLY2.AsNoTracking().ToList();
}
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var formStatus = db.SYS_CODE.AsNoTracking().Where(x => x.CODE_TYPE == "FORM_STATUS").ToList();
var treaItemTypes = db.TREA_ITEM.AsNoTracking().Where(x => x.IS_DISABLED == "N" && x.ITEM_OP_TYPE == "3").ToList();
var data = db.TREA_APLY_REC.AsNoTracking().FirstOrDefault(x => x.APLY_NO == aplyNo);
if (data != null)
{
result.vAplyNo = data.APLY_NO;
result.vItem = treaItemTypes.FirstOrDefault(x => x.ITEM_ID == data.ITEM_ID)?.ITEM_DESC;
result.vAplyUnitNmae = depts.FirstOrDefault(y => y.DPT_CD.Trim() == data.APLY_UNIT)?.DPT_NAME; //1904112 Bianco 改為顯示名子
result.vAplyUidName = emps.FirstOrDefault(x => x.USR_ID == data.APLY_UID)?.EMP_NAME; //1904112 Bianco 改為顯示名子
result.vAplyUnit = data.APLY_UNIT;
result.vAplyUid = data.APLY_UID;
result.vChargeUnit = depts.FirstOrDefault(y => y.DPT_CD.Trim() == data.APLY_UNIT)?.DPT_NAME;
result.vAccessType = data.ACCESS_TYPE == "P" ? "存入" : data.ACCESS_TYPE == "G" ? "取出" : ""; //存入(P) or 取出(G)
result.vExpectedAccessDate = TypeTransfer.dateTimeNToString(data.EXPECTED_ACCESS_DATE);
result.vCreateDt = TypeTransfer.dateTimeNToString(data.CREATE_DT);
var _createEmp = emps.FirstOrDefault(x => x.USR_ID == data.CREATE_UID);
result.vCreateUnit = depts.FirstOrDefault(y => y.DPT_CD.Trim() == _createEmp?.DPT_CD?.Trim())?.DPT_NAME;
result.vCreateUid = _createEmp?.EMP_NAME;
result.vCreateUnit_Id = data.CREATE_UNIT;
result.vCreateUid_Id = data.CREATE_UID;
result.vAccessReason = data.ACCESS_REASON;
result.vLastUpdateTime = data.LAST_UPDATE_DT;
}
}
return(result);
}
/// <summary>
/// 取中文姓名
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public ActionResult getUserName(string userId)
{
string userName = "";
OaEmpDao oaEmpDao = new OaEmpDao();
V_EMPLY2 emp = new V_EMPLY2();
using (DB_INTRAEntities dbIntra = new DB_INTRAEntities())
{
emp = oaEmpDao.qryByUsrId(userId, dbIntra);
if (emp != null)
{
userName = StringUtil.toString(emp.EMP_NAME);
}
}
if ("".Equals(userName))
{
return(Json(new { success = false, err = "無此帳號資料，不可新增!!" }));
}
else
{
return(Json(new { success = true, userName = userName }));
}
}
public override DataSet GetData(List <reportParm> parms)
{
//報表資料
List <DepositReportCAData> ReportDataList = new List <DepositReportCAData>();
var resultsTable = new DataSet();
var ReportData = new DepositReportCAData();
string vdept = parms.Where(x => x.key == "vdept").FirstOrDefault()?.value ?? string.Empty;
string vsect = parms.Where(x => x.key == "vsect").FirstOrDefault()?.value ?? string.Empty;
string JobProject = parms.Where(x => x.key == "vJobProject").FirstOrDefault()?.value ?? string.Empty;
string APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value ?? string.Empty; //庫存日期
string APLY_ODT_From = parms.Where(x => x.key == "APLY_ODT_From").FirstOrDefault()?.value ?? string.Empty;
string APLY_ODT_To = parms.Where(x => x.key == "APLY_ODT_To").FirstOrDefault()?.value ?? string.Empty;
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT = TypeTransfer.stringToDateTime(APLY_DT_From).DateToLatestTime();
var _APLY_DT_Date = _APLY_DT.Date;
var dtn = DateTime.Now.Date;
var _APLY_ODT_From = TypeTransfer.stringToDateTimeN(APLY_ODT_From);
var _APLY_ODT_To = TypeTransfer.stringToDateTimeN(APLY_ODT_To).DateToLatestTime();
int TOTAL = 0;
INVENTORY_STATUSs.AddRange(new List <string>()
{
((int)AccessInventoryType._5).ToString(), //預約取出，計庫存
((int)AccessInventoryType._6).ToString(), //已被取出，計庫存
((int)AccessInventoryType._9).ToString()
}); //預約存入，計庫存
var _IC = db.ITEM_CA.AsNoTracking() //判斷是否在庫
.Where(x => INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS), _APLY_DT_Date == dtn)
.Where(x =>
(INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS) && x.PUT_DATE <= _APLY_DT) // 在庫 且 存入日期 <= 庫存日期
||
(x.INVENTORY_STATUS == INVENTORY_STATUSg &&
x.PUT_DATE <= _APLY_DT &&
_APLY_DT < x.GET_DATE), //存入日期 <= 庫存日期 且 庫存日期 < 取出日期
_APLY_DT_Date != dtn)
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.PUT_DATE >= _APLY_ODT_From, _APLY_ODT_From != null)
.Where(x => x.PUT_DATE <= _APLY_ODT_To, _APLY_ODT_To != null)
.ToList();
var depts = new List <VW_OA_DEPT>();
var types = new List <SYS_CODE>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
}
types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE != null).ToList();
foreach (var CAdata in _IC /*.OrderBy(x => x.PUT_DATE).ThenBy(x=>x.CHARGE_DEPT)*/.OrderBy(x => x.CHARGE_DEPT).ThenBy(x => x.CHARGE_SECT).ThenBy(x => x.CA_USE).ThenBy(x => x.CA_DESC).ThenBy(x => x.BANK).ThenBy(x => x.CA_NUMBER))
{
TOTAL++;
var _CHARGE_DEPT = getEmpName(depts, CAdata.CHARGE_DEPT);
var _CHARGE_SECT = getEmpName(depts, CAdata.CHARGE_SECT)?.Replace(_CHARGE_DEPT, "")?.Trim();
ReportData = new DepositReportCAData()
{
ROW = TOTAL,
PUT_DATE = CAdata.PUT_DATE.dateTimeToStr(),
CHARGE_DEPT = _CHARGE_DEPT,
CHARGE_SECT = _CHARGE_SECT,
CA_DESC = getCAdesc(types, CAdata.CA_DESC),
CA_USE = getCAuse(types, CAdata.CA_USE),
BANK = CAdata.BANK,
CA_NUMBER = CAdata.CA_NUMBER,
MEMO = CAdata.MEMO,
CHARGE_DEPT_ID = CAdata.CHARGE_DEPT,
CHARGE_SECT_ID = CAdata.CHARGE_SECT
};
ReportDataList.Add(ReportData);
}
}
resultsTable.Tables.Add(ReportDataList.ToDataTable());
return(resultsTable);
}
/**
* 角色資訊(含功能授權)
**/
public ActionResult detailRole(string cRoleId, string execType)
{
/*---畫面下拉選單初始值---*/
//金庫設備
TreaEquipDao treaEquipDao = new TreaEquipDao();
ViewBag.equipList = treaEquipDao.jqgridSelect();
//存取項目
TreaItemDao treaItemDao = new TreaItemDao();
ViewBag.itemList = treaItemDao.jqgridSelect("");
SysCodeDao sysCodeDao = new SysCodeDao();
//停用註記
var isDisabledList = sysCodeDao.loadSelectList("IS_DISABLED");
ViewBag.isDisabledList = isDisabledList;
//角色群組
var roleAuthTypeList = sysCodeDao.loadSelectList("ROLE_AUTH_TYPE");
ViewBag.roleAuthTypeList = roleAuthTypeList;
//控管模式
var controlList = sysCodeDao.loadSelectList("CONTROL_MODE");
ViewBag.controlList = controlList;
//ViewBag.controlList = sysCodeDao.jqGridList("CONTROL_MODE");
//控管方式
var custodyList = sysCodeDao.loadSelectList("CUSTODY_MODE");
ViewBag.custodyList = custodyList;
//ViewBag.custodyList = sysCodeDao.jqGridList("CUSTODY_MODE");
//入庫作業類型
ViewBag.itemOpTypeList = sysCodeDao.jqGridList("ITEM_OP_TYPE");
//覆核狀態 add by daiyu 20180214
Dictionary <string, string> dicReview = sysCodeDao.qryByTypeDic("DATA_STATUS");
//查詢角色資訊
CodeRoleDao codeRoleDao = new CodeRoleDao();
CODE_ROLE codeRole = new CODE_ROLE();
if (cRoleId != null)
{
codeRole = codeRoleDao.qryRoleByKey(cRoleId);
}
//將值搬給畫面欄位
RoleMgrModel roleMgrModel = new RoleMgrModel();
qryUserFunc(cRoleId); //取得已授權、未授權功能清單
if (!"".Equals(StringUtil.toString(codeRole.ROLE_ID)))
{
roleMgrModel.cRoleID = StringUtil.toString(codeRole.ROLE_ID);
roleMgrModel.cRoleName = StringUtil.toString(codeRole.ROLE_NAME);
roleMgrModel.roleAuthType = StringUtil.toString(codeRole.ROLE_AUTH_TYPE);
roleMgrModel.isDisabled = StringUtil.toString(codeRole.IS_DISABLED);
roleMgrModel.vMemo = StringUtil.toString(codeRole.MEMO);
roleMgrModel.dataStatus = StringUtil.toString(codeRole.DATA_STATUS) == "" ? "" : codeRole.DATA_STATUS + "." + dicReview[codeRole.DATA_STATUS];
roleMgrModel.cCrtDateTime = codeRole.CREATE_DT == null ? "" : DateUtil.DatetimeToString(codeRole.CREATE_DT, "");
roleMgrModel.cUpdDateTime = codeRole.LAST_UPDATE_DT == null ? "" : DateUtil.DatetimeToString(codeRole.LAST_UPDATE_DT, "");
OaEmpDao oaEmpDao = new OaEmpDao();
using (DB_INTRAEntities dbIntra = new DB_INTRAEntities())
{
try
{
roleMgrModel.cCrtUserID = codeRole.CREATE_UID == null ? "" : StringUtil.toString(oaEmpDao.qryByUsrId(codeRole.CREATE_UID, dbIntra).EMP_NAME);
}
catch (Exception e)
{
}
try
{
roleMgrModel.cUpdUserID = codeRole.LAST_UPDATE_UID == null ? "" : StringUtil.toString(oaEmpDao.qryByUsrId(codeRole.LAST_UPDATE_UID, dbIntra).EMP_NAME);
}
catch (Exception e)
{
}
}
roleMgrModel.Categories = ViewBag.funcList;
ViewBag.bHaveData = "Y";
//return RedirectToAction("Index", "Home");
return(View(roleMgrModel));
}
else
{
if ("A".Equals(execType))
{
qryUserFunc(""); //取得已授權、未授權功能清單
roleMgrModel.cRoleID = "";
roleMgrModel.cRoleName = "";
roleMgrModel.isDisabled = "N";
roleMgrModel.vMemo = "";
roleMgrModel.cCrtUserID = "";
roleMgrModel.cCrtDateTime = "";
roleMgrModel.cUpdUserID = "";
roleMgrModel.cUpdDateTime = "";
roleMgrModel.dataStatus = "";
roleMgrModel.Categories = ViewBag.funcList;
ViewBag.bHaveData = "Y";
return(View(roleMgrModel));
}
else
{
ViewBag.bHaveData = "N";
return(View("detailRole"));
}
}
}
/// <summary>
/// 金庫進出管理作業-金庫物品存取申請作業 初始畫面顯示
/// </summary>
/// <param name="cUserID">userId</param>
/// <param name="custodyFlag">管理科Flag</param>
/// <param name="unit">科別指定</param>
/// <returns></returns>
public Tuple <List <SelectOption>, List <SelectOption>, List <SelectOption>, BaseUserInfoModel> TreasuryAccessDetail(string cUserID, bool custodyFlag, string unit = null)
{
List <SelectOption> applicationProject = new List <SelectOption>(); //申請項目
List <SelectOption> applicationUnit = new List <SelectOption>(); //申請單位
List <SelectOption> applicant = new List <SelectOption>(); //申請人
var empty = new SelectOption()
{
Text = string.Empty, Value = string.Empty
};
BaseUserInfoModel user = GetUserInfo(cUserID); //填表人 資料
try
{
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
var depts = dbINTRA.VW_OA_DEPT.AsNoTracking().ToList();
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
#region 保管科人員
if (custodyFlag) //是保管科人員
{
applicationProject = db.TREA_ITEM.AsNoTracking()
.Where(x => x.IS_DISABLED == "N" && x.ITEM_OP_TYPE == "3") //「入庫作業類型=3」且啟用中的存取項目
.OrderBy(x => x.ITEM_ID)
.AsEnumerable()
.Select(x => new SelectOption()
{
Value = x.ITEM_ID,
Text = x.ITEM_DESC
}).ToList();
var Units = new List <string>();
//自【保管單位設定檔】中挑出啟用中的單位
db.ITEM_CHARGE_UNIT.AsNoTracking()
.Where(x => x.IS_DISABLED == "N").ToList()
.ForEach(x => {
if (!x.CHARGE_SECT.IsNullOrWhiteSpace())
{
Units.Add(x.CHARGE_SECT.Trim());
}
else
{
Units.Add(x.CHARGE_DEPT.Trim());
}
});
applicationUnit = Units.Distinct().OrderBy(x => x)
.AsEnumerable()
.Select(x => new SelectOption()
{
Value = x,
Text = depts.FirstOrDefault(y => y.DPT_CD.Trim() == x)?.DPT_NAME
}).ToList();
if (applicationUnit.Any())
{
if (unit != null)
{
var _first = applicationUnit.FirstOrDefault(x => x.Value == unit);
if (_first != null)
{
applicant = dbINTRA.V_EMPLY2.AsNoTracking()
.Where(x => x.DPT_CD == _first.Value)
.AsEnumerable()
.Select(x => new SelectOption()
{
Value = x.USR_ID,
Text = $@"{x.USR_ID}({x.EMP_NAME})"
}).ToList();
}
}
}
if (!applicant.Any())
{
applicant.Add(empty);
}
}
#endregion
#region 非保管科人員
else
{
applicationProject =
db.CODE_USER_ROLE.AsNoTracking()
.Where(x => x.USER_ID == cUserID) //登入者所擁有的角色
.Join(db.CODE_ROLE_ITEM.AsNoTracking()
.Where(x => x.AUTH_TYPE == "2"), //表單申請權限=Y
x => x.ROLE_ID,
y => y.ROLE_ID,
(x, y) => y
).Join(db.TREA_ITEM.AsNoTracking(), //金庫存取作業設定檔
x => x.ITEM_ID,
y => y.ITEM_ID,
(x, y) => y
).AsEnumerable()
.Select(x => new SelectOption()
{
Value = x.ITEM_ID,
Text = x.ITEM_DESC
}).ToList();
if (user != null)
{
applicationUnit.Add(new SelectOption()
{
Value = user.DPT_ID,
Text = user.DPT_Name
});
applicant.Add(new SelectOption()
{
Value = user.EMP_ID,
Text = $@"{user.EMP_ID}({user.EMP_Name})"
});
}
}
#endregion
}
}
}
catch (Exception ex)
{
var message = ex.exceptionMessage();
throw ex;
}
return(new Tuple <List <SelectOption>, List <SelectOption>, List <SelectOption>, BaseUserInfoModel>(applicationProject, applicationUnit, applicant, user));
}
public JsonResult SendReport(
reportModel data,
List <reportParm> parms,
List <reportParm> extensionParms = null
)
{
MSGReturnModel <string> result = new MSGReturnModel <string>();
result.RETURN_FLAG = false;
Treasury.WebUtility.FileRelated.createFile(Server.MapPath("~/Temp/"));
try
{
var fileLocation = Server.MapPath("~/Temp/");
string title = "報表名稱";
if (data.className.IsNullOrWhiteSpace())
{
result.DESCRIPTION = "寄送報表錯誤請聯絡IT人員";
//result.DESCRIPTION = MessageType.parameter_Error.GetDescription(null, "無呼叫的className");
return(Json(result));
}
if (!data.title.IsNullOrWhiteSpace())
{
title = data.title;
}
object obj = Activator.CreateInstance(Assembly.Load("Treasury.Web").GetType($"Treasury.Web.Report.Data.{data.className}"));
MethodInfo[] methods = obj.GetType().GetMethods();
MethodInfo mi = methods.FirstOrDefault(x => x.Name == "GetData");
if (mi == null)
{
//檢查是否有實作資料獲取
result.DESCRIPTION = "寄送報表錯誤請聯絡IT人員";
return(Json(result));
}
DataSet ds = (DataSet)mi.Invoke(obj, new object[] { parms });
List <reportParm> eparm = (List <reportParm>)(obj.GetType().GetProperty("extensionParms").GetValue(obj));
var lr = new LocalReport();
lr.ReportPath = Server.MapPath($"~/Report/Rdlc/{data.className}.rdlc");
lr.DataSources.Clear();
List <ReportParameter> _parm = new List <ReportParameter>();
_parm.Add(new ReportParameter("Title", title));
if (extensionParms != null)
{
_parm.AddRange(extensionParms.Select(x => new ReportParameter(x.key, x.value)));
}
if (eparm.Any())
{
_parm.AddRange(eparm.Select(x => new ReportParameter(x.key, x.value)));
}
if (_parm.Any())
{
lr.SetParameters(_parm);
}
for (int i = 0; i < ds.Tables.Count; i++)
{
lr.DataSources.Add(new ReportDataSource("DataSet" + (i + 1).ToString(), ds.Tables[i]));
}
string _DisplayName = title;
if (_DisplayName != null)
{
_DisplayName = _DisplayName.Replace("(", "-").Replace(")", "");
var _name = _parm.FirstOrDefault(x => x.Name == "vJobProject");
if (_name != null)
{
_DisplayName = $"{_DisplayName}_{_name.Values[0]}";
}
}
lr.DisplayName = _DisplayName;
lr.Refresh();
string mimeType, encoding, extension;
Warning[] warnings;
string[] streams;
var renderedBytes = lr.Render
(
"PDF",
null,
out mimeType,
out encoding,
out extension,
out streams,
out warnings
);
var saveAs = string.Format("{0}.pdf", Path.Combine(fileLocation, _DisplayName));
var idx = 0;
while (Directory.Exists(saveAs))
{
idx++;
saveAs = string.Format("{0}.{1}.pdf", Path.Combine(fileLocation, _DisplayName), idx);
}
//using (var stream = new FileStream(saveAs, FileMode.Create, FileAccess.Write))
//{
// stream.Write(renderedBytes, 0, renderedBytes.Length);
// stream.Close();
//}
lr.Dispose();
#region 寄信
//存許項目
string vitemIdName = extensionParms.FirstOrDefault(x => x.key == "vJobProject")?.value;
//庫存日期
string aplyDt = parms.FirstOrDefault(x => x.key == "APLY_DT_From")?.value;
MAIL_TIME MT = new MAIL_TIME();
MAIL_CONTENT MC = new MAIL_CONTENT();
//List<Tuple<string, string>> _mailTo = new List<Tuple<string, string>>() { new Tuple<string, string>("*****@*****.**", "測試帳號-glsisys") };
List <Tuple <string, string> > _mailTo = new List <Tuple <string, string> >();
List <Tuple <string, string> > _ccTo = new List <Tuple <string, string> >();
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
//季追蹤庫存表 抓5
MT = db.MAIL_TIME.AsNoTracking().FirstOrDefault(x => x.MAIL_TIME_ID == "5" && x.IS_DISABLED != "Y");
var _MAIL_CONTENT_ID = MT?.MAIL_CONTENT_ID;
MC = db.MAIL_CONTENT.AsNoTracking().FirstOrDefault(x => x.MAIL_CONTENT_ID == _MAIL_CONTENT_ID && x.IS_DISABLED != "Y");
var _MAIL_RECEIVE = db.MAIL_RECEIVE.AsNoTracking();
var _CODE_ROLE_FUNC = db.CODE_ROLE_FUNC.AsNoTracking();
var _CODE_USER_ROLE = db.CODE_USER_ROLE.AsEnumerable();
var _CODE_USER = db.CODE_USER.AsNoTracking();
var emps = comm.GetEmps();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
//存許項目
string vitemId = parms.FirstOrDefault(x => x.key == "vJobProject")?.value; //Bianco 20190326 原參數 vjobProject
//權責部門
string vdept = parms.FirstOrDefault(x => x.key == "vdept")?.value; //Bianco 20190326 原參數 CHARGE_DEPT_ID
//權責科別
string vsect = parms.FirstOrDefault(x => x.key == "vsect")?.value; //Bianco 20190326 原參數 CHARGE_SECT_ID
var _VW_OA_DEPT = dbINTRA.VW_OA_DEPT.AsNoTracking();
var _V_EMPLY2 = dbINTRA.V_EMPLY2.AsNoTracking();
var _ITEM_CHARGE_UNIT = db.ITEM_CHARGE_UNIT.AsNoTracking()
.Where(x => x.ITEM_ID == vitemId, vitemId != null)
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.IS_DISABLED == "N")
.AsEnumerable()
.Select(x => new ITEM_CHARGE_UNIT()
{
CHARGE_DEPT = x.CHARGE_DEPT,
CHARGE_SECT = x.CHARGE_SECT,
CHARGE_UID = x.CHARGE_UID,
IS_MAIL_SECT_MGR = x.IS_MAIL_SECT_MGR,
IS_MAIL_DEPT_MGR = x.IS_MAIL_DEPT_MGR
}).ToList();
_ITEM_CHARGE_UNIT.ForEach(x => {
//經辦
var _CHARGE_NAME = _V_EMPLY2.FirstOrDefault(y => y.USR_ID == x.CHARGE_UID);
if (_CHARGE_NAME != null && !_mailTo.Any(y => y.Item1 == _CHARGE_NAME.EMAIL))
{
_mailTo.Add(new Tuple <string, string>(_CHARGE_NAME.EMAIL, _CHARGE_NAME.EMP_NAME));
}
if (x.IS_MAIL_SECT_MGR == "Y")
{
//科主管員編
var _VW_OA_DEPT_DPT_HEAD = _VW_OA_DEPT.FirstOrDefault(y => y.DPT_CD == x.CHARGE_SECT)?.DPT_HEAD;
//人名 EMAIl
var _EMP_NAME = _V_EMPLY2.FirstOrDefault(y => y.EMP_NO == _VW_OA_DEPT_DPT_HEAD);
if (_EMP_NAME != null && !_mailTo.Any(y => y.Item1 == _EMP_NAME.EMAIL))
{
_mailTo.Add(new Tuple <string, string>(_EMP_NAME.EMAIL, _EMP_NAME.EMP_NAME));
}
}
if (x.IS_MAIL_DEPT_MGR == "Y")
{
//部主管員編
//var _UP_DPT_CD = _VW_OA_DEPT.FirstOrDefault(y => y.DPT_CD == x.CHARGE_SECT)?.UP_DPT_CD;
//var _UP_DPT_CD = _VW_OA_DEPT.FirstOrDefault(y => y.DPT_CD == x.CHARGE_SECT)?.UP_DPT_CD;
//var _VW_OA_DEPT_DPT_HEAD = _VW_OA_DEPT.FirstOrDefault(y => y.DPT_CD == _UP_DPT_CD)?.DPT_HEAD;
var _VW_OA_DEPT_DPT_HEAD = _VW_OA_DEPT.FirstOrDefault(y => y.DPT_CD == x.CHARGE_DEPT)?.DPT_HEAD;
//人名 EMAIl
var _EMP_NAME = _V_EMPLY2.FirstOrDefault(y => y.EMP_NO == _VW_OA_DEPT_DPT_HEAD);
if (_EMP_NAME != null && !_ccTo.Any(y => y.Item1 == _EMP_NAME.EMAIL) && !_mailTo.Any(y => y.Item1 == _EMP_NAME.EMAIL))
{
_ccTo.Add(new Tuple <string, string>(_EMP_NAME.EMAIL, _EMP_NAME.EMP_NAME));
}
}
});
if (MC != null)
{
var _FuncId = _MAIL_RECEIVE.Where(x => x.MAIL_CONTENT_ID == MC.MAIL_CONTENT_ID).Select(x => x.FUNC_ID);
var _RoleId = _CODE_ROLE_FUNC.Where(x => _FuncId.Contains(x.FUNC_ID)).Select(x => x.ROLE_ID);
var _UserId = _CODE_USER_ROLE.Where(x => _RoleId.Contains(x.ROLE_ID)).Select(x => x.USER_ID).Distinct();
List <string> _userIdList = new List <string>();
_userIdList.AddRange(_CODE_USER.Where(x => _UserId.Contains(x.USER_ID) && x.IS_MAIL == "Y").Select(x => x.USER_ID).ToList());
if (_userIdList.Any())
{
//人名 EMAIl
var _EMP = emps.Where(x => _userIdList.Contains(x.USR_ID)).ToList();
if (_EMP.Any())
{
_EMP.ForEach(x => {
if (!_ccTo.Any(y => y.Item1 == x.EMAIL) && !_mailTo.Any(y => y.Item1 == x.EMAIL))
{
_ccTo.Add(new Tuple <string, string>(x.EMAIL, x.EMP_NAME));
}
});
}
}
}
}
}
string passwordZip = getPassWord();
Dictionary <string, Stream> attachment = new Dictionary <string, Stream>();
//attachment.Add(string.Format("{0}.pdf", _DisplayName), new MemoryStream(renderedBytes));
using (ZipFile zip = new ZipFile(System.Text.Encoding.Default))
{
var memSteam = new MemoryStream();
var streamWriter = new StreamWriter(memSteam);
ZipEntry e = zip.AddEntry(string.Format("{0}.pdf", _DisplayName), new MemoryStream(renderedBytes));
e.Password = passwordZip;
e.Encryption = EncryptionAlgorithm.WinZipAes256;
var ms = new MemoryStream();
ms.Seek(0, SeekOrigin.Begin);
zip.Save(ms);
ms.Seek(0, SeekOrigin.Begin);
ms.Flush();
attachment.Add(string.Format("{0}.zip", _DisplayName), ms);
}
string str = MC?.MAIL_CONTENT1 ?? string.Empty;
str = str.Replace("@_DATE_", aplyDt);
str = str.Replace("@_ITEM_", vitemIdName);
StringBuilder sb = new StringBuilder();
sb.AppendLine(str);
try
{
var sms = new SendMail.SendMailSelf();
sms.smtpPort = 25;
sms.smtpServer = Properties.Settings.Default["smtpServer"]?.ToString();
sms.mailAccount = Properties.Settings.Default["mailAccount"]?.ToString();
sms.mailPwd = Properties.Settings.Default["mailPwd"]?.ToString();
sms.Mail_Send(
new Tuple <string, string>(sms.mailAccount, "金庫管理系統"),
_mailTo,
_ccTo,
MC?.MAIL_SUBJECT ?? "季追蹤庫存表",
sb.ToString(),
false,
attachment
);
}
catch (Exception ex)
{
result.DESCRIPTION = $"Email 發送失敗請人工通知。";
return(Json(result));
}
//寄密碼
try
{
var sms = new SendMail.SendMailSelf();
sms.smtpPort = 25;
sms.smtpServer = Properties.Settings.Default["smtpServer"]?.ToString();
sms.mailAccount = Properties.Settings.Default["mailAccount"]?.ToString();
sms.mailPwd = Properties.Settings.Default["mailPwd"]?.ToString();
sms.Mail_Send(
new Tuple <string, string>(sms.mailAccount, "金庫管理系統"),
_mailTo,
_ccTo,
"季追蹤庫存表-密碼",
$"密碼:{passwordZip}",
false,
null
);
}
catch (Exception ex)
{
result.DESCRIPTION = $"Email 密碼發送失敗。";
return(Json(result));
}
#endregion
result.RETURN_FLAG = true;
result.DESCRIPTION = "已寄送追蹤報表!";
}
catch (Exception ex)
{
result.DESCRIPTION = ex.exceptionMessage();
}
return(Json(result));
}
public override DataSet GetData(List <reportParm> parms)
{
//報表資料
List <DepositReportESTATEData> ReportDataList = new List <DepositReportESTATEData>();
var resultsTable = new DataSet();
var ReportData = new DepositReportESTATEData();
string vdept = parms.Where(x => x.key == "vdept").FirstOrDefault()?.value ?? string.Empty;
string vsect = parms.Where(x => x.key == "vsect").FirstOrDefault()?.value ?? string.Empty;
string BOOK_NO = parms.Where(x => x.key == "vBook_No").FirstOrDefault()?.value ?? string.Empty;
string JobProject = parms.Where(x => x.key == "vJobProject").FirstOrDefault()?.value ?? string.Empty;
string APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value ?? string.Empty; //庫存日期
string APLY_ODT_From = parms.Where(x => x.key == "APLY_ODT_From").FirstOrDefault()?.value ?? string.Empty;
string APLY_ODT_To = parms.Where(x => x.key == "APLY_ODT_To").FirstOrDefault()?.value ?? string.Empty;
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT = TypeTransfer.stringToDateTime(APLY_DT_From).DateToLatestTime();
var _APLY_DT_Date = _APLY_DT.Date;
var dtn = DateTime.Now.Date;
var _APLY_ODT_From = TypeTransfer.stringToDateTimeN(APLY_ODT_From);
var _APLY_ODT_To = TypeTransfer.stringToDateTimeN(APLY_ODT_To).DateToLatestTime();
int TOTAL = 0;
INVENTORY_STATUSs.AddRange(new List <string>()
{
((int)AccessInventoryType._5).ToString(), //預約取出，計庫存
((int)AccessInventoryType._6).ToString(), //已被取出，計庫存
((int)AccessInventoryType._9).ToString()
}); //預約存入，計庫存
var _IRE = db.ITEM_REAL_ESTATE.AsNoTracking().ToList();
_IRE = db.ITEM_REAL_ESTATE.AsNoTracking()//判斷是否在庫
.Where(x => INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS), _APLY_DT_Date == dtn)
.Where(x =>
(INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS) && x.PUT_DATE <= _APLY_DT) // 在庫 且 存入日期 <= 庫存日期
||
(x.INVENTORY_STATUS == INVENTORY_STATUSg &&
x.PUT_DATE <= _APLY_DT &&
_APLY_DT < x.GET_DATE), //存入日期 <= 庫存日期 且 庫存日期 < 取出日期
_APLY_DT_Date != dtn)
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.GROUP_NO.ToString() == BOOK_NO, BOOK_NO != "All")
.Where(x => x.PUT_DATE >= _APLY_ODT_From, _APLY_ODT_From != null)
.Where(x => x.PUT_DATE <= _APLY_ODT_To, _APLY_ODT_To != null).ToList();
var depts = new List <VW_OA_DEPT>();
var types = new List <SYS_CODE>();
var book = new List <ITEM_BOOK>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
}
types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE != null && x.CODE_TYPE == "ESTATE_TYPE").ToList();
book = db.ITEM_BOOK.AsNoTracking().Where(x => x.GROUP_NO.ToString() != null).ToList();
_IRE.ForEach(x =>
{
x.ESTATE_FORM_NO = types.FirstOrDefault(y => y.CODE_VALUE == x.ESTATE_FORM_NO)?.CODE;
});
foreach (var ESTATEdata in _IRE.OrderBy(x => x.GROUP_NO).ThenBy(x => x.PUT_DATE).ThenBy(x => x.ESTATE_FORM_NO).ThenBy(x => x.ESTATE_DATE).ThenBy(x => x.OWNERSHIP_CERT_NO))
{
TOTAL++;
var _CHARGE_DEPT = getEmpName(depts, ESTATEdata.CHARGE_DEPT);
var _CHARGE_SECT = getEmpName(depts, ESTATEdata.CHARGE_SECT)?.Replace(_CHARGE_DEPT, "")?.Trim();
ReportData = new DepositReportESTATEData()
{
ROW = TOTAL,
PUT_DATE = ESTATEdata.PUT_DATE.dateTimeToStr(),
ESTATE_FORM_NO = types.FirstOrDefault(x => x.CODE == ESTATEdata.ESTATE_FORM_NO)?.CODE_VALUE,
ESTATE_DATE = ESTATEdata.ESTATE_DATE.dateTimeToStr(),
OWNERSHIP_CERT_NO = ESTATEdata.OWNERSHIP_CERT_NO,
LAND_BUILDING_NO = ESTATEdata.LAND_BUILDING_NO,
HOUSE_NO = ESTATEdata.HOUSE_NO,
ESTATE_SEQ = ESTATEdata.ESTATE_SEQ,
BOOK_NO_DETAIL = ESTATEdata.GROUP_NO.ToString(),
BUILDING_NAME = getBuildName(book, ESTATEdata.GROUP_NO.ToString()),
LOCATED = getLocated(book, ESTATEdata.GROUP_NO.ToString()),
CHARGE_DEPT = _CHARGE_DEPT,
CHARGE_SECT = _CHARGE_SECT,
CHARGE_DEPT_ID = ESTATEdata.CHARGE_DEPT,
CHARGE_SECT_ID = ESTATEdata.CHARGE_SECT,
MEMO = ESTATEdata.MEMO,
};
ReportDataList.Add(ReportData);
}
}
resultsTable.Tables.Add(ReportDataList.ToDataTable());
return(resultsTable);
}
/// <summary>
/// 取得人員姓名
/// </summary>
/// <param name="userNameMap"></param>
/// <param name="usrId"></param>
/// <param name="db"></param>
/// <returns></returns>
public Dictionary <string, string> qryUsrName(Dictionary <string, string> userNameMap, string usrId, DB_INTRAEntities db)
{
if (!"".Equals(usrId))
{
if (!userNameMap.ContainsKey(usrId))
{
V_EMPLY2 oaEmp = qryByUsrId(usrId, db);
if (oaEmp != null)
{
if (!"".Equals(StringUtil.toString(oaEmp.EMP_NAME)))
{
userNameMap.Add(usrId, StringUtil.toString(oaEmp.EMP_NAME));
}
else
{
userNameMap.Add(usrId, "");
}
}
else
{
userNameMap.Add(usrId, "");
}
}
}
return(userNameMap);
}
protected void SetDetail(string aply_No)
{
var depts = new List <VW_OA_DEPT>();
var emps = new List <V_EMPLY2>();
_REC = new REC();
//var sys
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
emps = dbINTRA.V_EMPLY2.AsNoTracking().ToList();
}
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var treaItems = db.TREA_ITEM.AsNoTracking().Where(x => x.ITEM_OP_TYPE == "3").ToList();
var data = db.TREA_APLY_REC.AsNoTracking().FirstOrDefault(x => x.APLY_NO == aply_No);
////////////Bianco////////////
var status = db.SYS_CODE.AsNoTracking().Where(x => x.CODE_TYPE == "FORM_STATUS");
var _dept = new INTRA().getDept(data.APLY_UNIT);
if (_dept != null)
{
if (_dept.Dpt_type != null)
{
switch (_dept.Dpt_type.Trim())
{
case "04": //科
_REC.APLY_DEPT = _dept.UP_DPT_CD?.Trim();
_REC.APLY_SECT = _dept.DPT_CD?.Trim();
break;
case "03": //部
case "02": //營管
_REC.APLY_DEPT = _dept.DPT_CD?.Trim();
break;
}
}
}
_REC.APLY_NO = data.APLY_NO; //申請單號
_REC.ACCESS_TYPE = data.ACCESS_TYPE == "P" ? "存入" : data.ACCESS_TYPE == "G" ? "取出" : ""; //動作 存入/取出
_REC.APLY_DT = TypeTransfer.dateTimeNToString(data.CREATE_DT); //申請日期
_REC.ITEM_ID = treaItems.FirstOrDefault(x => x.ITEM_ID == data.ITEM_ID)?.ITEM_DESC; //作業項目
_REC.APLY_UNIT = getEmpName(depts, data.APLY_UNIT); //權責部門
_REC.ACCESS_REASON = data.ACCESS_REASON; //申請原因
_REC.EXPECTED_ACCESS_DATE = TypeTransfer.dateTimeNToString(data.EXPECTED_ACCESS_DATE); //預計存取日期
//申請單位
var APLY_APPR = getDeptName(emps, data.APLY_APPR_UID); //覆核人員資料
_REC.APLY_APPR_UID_UNIT = APLY_APPR.Item1; //覆核人員單位
_REC.APLY_APPR_UID = APLY_APPR.Item2; //覆核人員名稱
var APLY = getDeptName(emps, data.APLY_UID); //申請人員資料
_REC.APLY_UID_UNIT = APLY.Item1; //申請人員單位
_REC.APLY_UID = APLY.Item2; //申請人員名稱
//保管單位
var CUSTODY_APPR = getDeptName(emps, data.CUSTODY_APPR_UID); //覆核人員資料
_REC.CUSTODY_APPR_UID_UNIT = CUSTODY_APPR.Item1; //覆核人員單位
_REC.CUSTODY_APPR_UID = CUSTODY_APPR.Item2; //覆核人員名稱
var CUSTODY = getDeptName(emps, data.CUSTODY_UID); //承辦人員資料
_REC.CUSTODY_UID_UNIT = CUSTODY.Item1; //承辦人員單位
_REC.CUSTODY_UID = CUSTODY.Item2; //承辦人員名稱
_REC.APLY_STATUS = getStatue(data.APLY_STATUS); //申請狀態
}
}
public override DataSet GetData(List <reportParm> parms)
{
//報表資料
List <DepositReportMargingData> ReportDataList = new List <DepositReportMargingData>();
var resultsTable = new DataSet();
var ReportData = new DepositReportMargingData();
string vdept = parms.Where(x => x.key == "vdept").FirstOrDefault()?.value ?? string.Empty;
string vsect = parms.Where(x => x.key == "vsect").FirstOrDefault()?.value ?? string.Empty;
string JobProject = parms.Where(x => x.key == "vJobProject").FirstOrDefault()?.value ?? string.Empty;
string APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value ?? string.Empty; //庫存日期
string APLY_ODT_From = parms.Where(x => x.key == "APLY_ODT_From").FirstOrDefault()?.value ?? string.Empty;
string APLY_ODT_To = parms.Where(x => x.key == "APLY_ODT_To").FirstOrDefault()?.value ?? string.Empty;
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT = TypeTransfer.stringToDateTime(APLY_DT_From).DateToLatestTime();
var _APLY_DT_Date = _APLY_DT.Date;
var dtn = DateTime.Now.Date;
var _APLY_ODT_From = TypeTransfer.stringToDateTimeN(APLY_ODT_From);
var _APLY_ODT_To = TypeTransfer.stringToDateTimeN(APLY_ODT_To).DateToLatestTime();
INVENTORY_STATUSs.AddRange(new List <string>()
{
((int)AccessInventoryType._5).ToString(), //預約取出，計庫存
((int)AccessInventoryType._6).ToString(), //已被取出，計庫存
((int)AccessInventoryType._9).ToString()
}); //預約存入，計庫存
var _IRD = db.ITEM_REFUNDABLE_DEP.AsNoTracking() //判斷是否在庫
.Where(x => INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS), _APLY_DT_Date == dtn)
.Where(x =>
(INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS) && x.PUT_DATE <= _APLY_DT) // 在庫 且 存入日期 <= 庫存日期
||
(x.INVENTORY_STATUS == INVENTORY_STATUSg &&
x.PUT_DATE <= _APLY_DT &&
_APLY_DT < x.GET_DATE), //存入日期 <= 庫存日期 且 庫存日期 < 取出日期
_APLY_DT_Date != dtn)
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.PUT_DATE >= _APLY_ODT_From, _APLY_ODT_From != null)
.Where(x => x.PUT_DATE <= _APLY_ODT_To, _APLY_ODT_To != null)
.ToList();
var depts = new List <VW_OA_DEPT>();
var types = new List <SYS_CODE>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
}
types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE != null).ToList();
foreach (var Stockdata in _IRD.OrderBy(x => x.MARGIN_DEP_TYPE).ThenBy(x => x.ITEM_ID).ThenBy(x => x.BOOK_NO).ThenBy(x => x.PUT_DATE).ThenBy(x => x.CHARGE_DEPT).ThenBy(x => x.CHARGE_SECT))
{
var _CHARGE_DEPT = getEmpName(depts, Stockdata.CHARGE_DEPT);
var _CHARGE_SECT = !_CHARGE_DEPT.IsNullOrWhiteSpace() ? getEmpName(depts, Stockdata.CHARGE_SECT)?.Replace(_CHARGE_DEPT, "")?.Trim() : null;
ReportData = new DepositReportMargingData()
{
MARGIN_DEP_TYPE = getMDTtype(types, Stockdata.MARGIN_DEP_TYPE),
ITEM_ID = Stockdata.ITEM_ID,
BOOK_NO = Stockdata.BOOK_NO,
PUT_DATE = Stockdata.PUT_DATE.dateTimeToStr(),
CHARGE_DEPT = _CHARGE_DEPT,
CHARGE_SECT = !_CHARGE_SECT.IsNullOrWhiteSpace() ? _CHARGE_SECT : null,
TRAD_PARTNERS = Stockdata.TRAD_PARTNERS,
AMOUNT = Stockdata.AMOUNT,
WORKPLACE_CODE = Stockdata.WORKPLACE_CODE,
DESCRIPTION = Stockdata.DESCRIPTION,
MEMO = Stockdata.MEMO,
CHARGE_DEPT_ID = Stockdata.CHARGE_DEPT,
CHARGE_SECT_ID = Stockdata.CHARGE_SECT
};
ReportDataList.Add(ReportData);
}
}
resultsTable.Tables.Add(ReportDataList.ToDataTable());
return(resultsTable);
}
public override DataSet GetData(List <reportParm> parms)
{
//報表資料
List <DepositReportSealData> ReportDataList = new List <DepositReportSealData>();
var resultsTable = new DataSet();
var ReportData = new DepositReportSealData();
string vdept = parms.Where(x => x.key == "vdept").FirstOrDefault()?.value ?? string.Empty;
string vsect = parms.Where(x => x.key == "vsect").FirstOrDefault()?.value ?? string.Empty;
string JobProject = parms.Where(x => x.key == "vJobProject").FirstOrDefault()?.value ?? string.Empty;
string APLY_DT_From = parms.Where(x => x.key == "APLY_DT_From").FirstOrDefault()?.value ?? string.Empty; //庫存日期
string APLY_ODT_From = parms.Where(x => x.key == "APLY_ODT_From").FirstOrDefault()?.value ?? string.Empty;
string APLY_ODT_To = parms.Where(x => x.key == "APLY_ODT_To").FirstOrDefault()?.value ?? string.Empty;
using (TreasuryDBEntities db = new TreasuryDBEntities())
{
var _APLY_DT = TypeTransfer.stringToDateTime(APLY_DT_From).DateToLatestTime();
var _APLY_DT_Date = _APLY_DT.Date;
var dtn = DateTime.Now.Date;
var _APLY_ODT_From = TypeTransfer.stringToDateTimeN(APLY_ODT_From);
var _APLY_ODT_To = TypeTransfer.stringToDateTimeN(APLY_ODT_To).DateToLatestTime();
int TOTAL = 0;
INVENTORY_STATUSs.AddRange(new List <string>()
{
((int)AccessInventoryType._5).ToString(), //預約取出，計庫存
((int)AccessInventoryType._6).ToString(), //已被取出，計庫存
((int)AccessInventoryType._9).ToString()
}); //預約存入，計庫存
//預約取出 , 用印 , 存入用印
var _IS = db.ITEM_SEAL.AsNoTracking() //判斷是否在庫
.Where(x => INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS), _APLY_DT_Date == dtn)
.Where(x =>
(INVENTORY_STATUSs.Contains(x.INVENTORY_STATUS) && x.PUT_DATE_ACCESS <= _APLY_DT) // 在庫 且 存入日期 <= 庫存日期 //(18/12/27 update x.PUT_DATE => x.PUT_DATE_ACCESS)
||
(x.INVENTORY_STATUS == INVENTORY_STATUSg &&
x.PUT_DATE_ACCESS <= _APLY_DT && //(18 / 12 / 27 update x.PUT_DATE => x.PUT_DATE_ACCESS)
_APLY_DT < x.GET_DATE), //存入日期 <= 庫存日期 且 庫存日期 < 取出日期
_APLY_DT_Date != dtn)
.Where(x => x.CHARGE_DEPT == vdept, vdept != "All")
.Where(x => x.CHARGE_SECT == vsect, vsect != "All")
.Where(x => x.PUT_DATE_ACCESS >= _APLY_ODT_From, _APLY_ODT_From != null) //(18 / 12 / 27 update x.PUT_DATE => x.PUT_DATE_ACCESS)
.Where(x => x.PUT_DATE_ACCESS <= _APLY_ODT_To, _APLY_ODT_To != null) //(18 / 12 / 27 update x.PUT_DATE => x.PUT_DATE_ACCESS)
.Where(x => x.TREA_ITEM_NAME == JobProject).ToList();
var depts = new List <VW_OA_DEPT>();
//var types = new List<SYS_CODE>();
using (DB_INTRAEntities dbINTRA = new DB_INTRAEntities())
{
depts = dbINTRA.VW_OA_DEPT.AsNoTracking().Where(x => x.DPT_CD != null).ToList();
}
//types = db.SYS_CODE.AsNoTracking().Where(x => x.CODE !=null).ToList();
foreach (var Sealdata in _IS.OrderBy(x => x.PUT_DATE_ACCESS).ThenByDescending(x => x.LAST_UPDATE_DT) /*.ThenBy(x=>x.CHARGE_DEPT).ThenBy(x=>x.CHARGE_SECT)*/)
{
TOTAL++;
var _CHARGE_DEPT = getEmpName(depts, Sealdata.CHARGE_DEPT);
var _CHARGE_SECT = getEmpName(depts, Sealdata.CHARGE_SECT)?.Replace(_CHARGE_DEPT, "")?.Trim();
ReportData = new DepositReportSealData()
{
ROW = TOTAL,
PUT_DATE = Sealdata.PUT_DATE_ACCESS.dateTimeToStr(),
CHARGE_DEPT = _CHARGE_DEPT,
CHARGE_SECT = _CHARGE_SECT,
SEAL_DESC = Sealdata.SEAL_DESC,
MEMO = Sealdata.MEMO,
CHARGE_DEPT_ID = Sealdata.CHARGE_DEPT,
CHARGE_SECT_ID = Sealdata.CHARGE_SECT
};
ReportDataList.Add(ReportData);
}
}
resultsTable.Tables.Add(ReportDataList.ToDataTable());
return(resultsTable);
}
///// <summary>
///// 開啟使用者修改明細畫面
///// </summary>
///// <param name="aplyNo"></param>
///// <returns></returns>
//public ActionResult detailUser(string cReviewSeq)
//{
// try
// {
// using (DbAccountEntities db = new DbAccountEntities())
// {
// CodeUserHisDao codeUserHisDao = new CodeUserHisDao();
// AuthReviewUserModel userData = codeUserHisDao.qryByNowHis(cReviewSeq, db);
// string[] cDateTime = userData.cCrtDateTime.Split(' ');
// userData.cCrtDateTime = DateUtil.formatDateTimeDbToSc(cDateTime[0] + " " + cDateTime[1], "DT");
// ViewBag.bHaveData = "Y";
// ViewBag.cReviewSeq = cReviewSeq;
// return View(userData);
// }
// }
// catch (Exception e)
// {
// ViewBag.bHaveData = "N";
// return View();
// }
//}
/// <summary>
/// 開啟使用者修改明細畫面
/// </summary>
/// <param name="aplyNo"></param>
/// <returns></returns>
public ActionResult detailUser(string aplyNo, string userId)
{
try
{
string execAction = "";
AuthApprDao AuthApprDao = new AuthApprDao();
AUTH_APPR authAppr = new AUTH_APPR();
if (!"".Equals(StringUtil.toString(aplyNo)))
{
authAppr = AuthApprDao.qryByKey(aplyNo);
ViewBag.bView = "N";
}
else
{
authAppr = AuthApprDao.qryByFreeRole(userId);
if (authAppr != null)
{
aplyNo = StringUtil.toString(authAppr.APLY_NO);
}
ViewBag.bView = "Y";
}
AuthReviewUserModel userData = new AuthReviewUserModel();
userData.aplyNo = aplyNo;
userData.userId = authAppr.APPR_MAPPING_KEY;
userData.createUid = authAppr.CREATE_UID;
OaEmpDao oaEmpDao = new OaEmpDao();
using (DB_INTRAEntities dbIntra = new DB_INTRAEntities())
{
try
{
userData.createUid = userData.createUid == null ? "" : StringUtil.toString(oaEmpDao.qryByUsrId(userData.createUid, dbIntra).EMP_NAME);
userData.userName = userData.userId == null ? "" : StringUtil.toString(oaEmpDao.qryByUsrId(userData.userId, dbIntra).EMP_NAME);
}
catch (Exception e)
{
}
}
userData.createDt = authAppr.CREATE_DT.ToString();
SysCodeDao sysCodeDao = new SysCodeDao();
Dictionary <string, string> dicExecAction = sysCodeDao.qryByTypeDic("EXEC_ACTION");
Dictionary <string, string> dicYNFlag = sysCodeDao.qryByTypeDic("YN_FLAG");
CodeUserHisDao codeUserHisDao = new CodeUserHisDao();
CODE_USER_HIS codeUserHis = codeUserHisDao.qryByAplyNo(aplyNo);
if (codeUserHis != null)
{
execAction = StringUtil.toString(codeUserHis.EXEC_ACTION);
}
if ("".Equals(execAction))
{
CodeUserDao codeUserDao = new CodeUserDao();
CODE_USER codeUser = new CODE_USER();
codeUser = codeUserDao.qryUserByKey(authAppr.APPR_MAPPING_KEY);
userData.isMailB = StringUtil.toString(codeUser.IS_MAIL);
userData.isDisabledB = StringUtil.toString(codeUser.IS_DISABLED);
userData.memoB = StringUtil.toString(codeUser.MEMO);
}
else
{
if ("A".Equals(execAction))
{
userData.isMail = StringUtil.toString(codeUserHis.IS_MAIL);
userData.isDisabled = StringUtil.toString(codeUserHis.IS_DISABLED);
userData.memo = StringUtil.toString(codeUserHis.MEMO);
}
else
{
userData.isMail = StringUtil.toString(codeUserHis.IS_MAIL);
userData.isDisabled = StringUtil.toString(codeUserHis.IS_MAIL);
userData.memo = StringUtil.toString(codeUserHis.MEMO);
userData.isMailB = StringUtil.toString(codeUserHis.IS_MAIL_B);
userData.isDisabledB = StringUtil.toString(codeUserHis.IS_DISABLED_B);
userData.memoB = StringUtil.toString(codeUserHis.MEMO_B);
}
}
userData.execAction = execAction;
userData.execActionDesc = dicExecAction.ContainsKey(userData.execAction) ? dicExecAction[userData.execAction] : userData.execAction;
userData.isDisabledDesc = dicYNFlag.ContainsKey(userData.isDisabled) ? dicYNFlag[userData.isDisabled] : userData.isDisabled;
userData.isDisabledDescB = dicYNFlag.ContainsKey(userData.isDisabledB) ? dicYNFlag[userData.isDisabledB] : userData.isDisabledB;
userData.isMailDesc = dicYNFlag.ContainsKey(userData.isMail) ? dicYNFlag[userData.isMail] : userData.isMail;
userData.isMailDescB = dicYNFlag.ContainsKey(userData.isMailB) ? dicYNFlag[userData.isMailB] : userData.isMailB;
ViewBag.bHaveData = "Y";
ViewBag.aplyNo = aplyNo;
return(View(userData));
}
catch (Exception e)
{
ViewBag.bHaveData = "N";
return(View());
}
}
