public List <Sys_FunctionMaster_fum_Info> SearchRecords(Model.IModel.IModelObject searchCondition) { string sqlString = string.Empty; string whereString = string.Empty; sqlString = "SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine; sqlString += " fum_iRecordID," + Environment.NewLine; sqlString += " fum_cFunctionNumber," + Environment.NewLine; sqlString += " fum_cFunctionDesc," + Environment.NewLine; sqlString += " fum_cRemark," + Environment.NewLine; sqlString += " fum_cAdd," + Environment.NewLine; sqlString += " fum_dAddDate," + Environment.NewLine; sqlString += " fum_cLast," + Environment.NewLine; sqlString += " fum_dLastDate " + Environment.NewLine; sqlString += " FROM Sys_FunctionMaster_fum" + Environment.NewLine; Sys_FunctionMaster_fum_Info info = null; info = searchCondition as Sys_FunctionMaster_fum_Info; if (info != null) { whereString = " WHERE 1=1 "; if (info.fum_cFunctionNumber.Trim() != "") { if (info.fum_cFunctionNumber.ToString().Contains("*") || info.fum_cFunctionNumber.ToString().Contains("?")) { whereString += " AND fum_cFunctionNumber LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.fum_cFunctionNumber) + "'"; } else { whereString += "AND fum_cFunctionNumber = N'" + info.fum_cFunctionNumber.ToString().Trim() + "'"; } } } sqlString += whereString; IEnumerable <Sys_FunctionMaster_fum_Info> infos = null; List <Sys_FunctionMaster_fum_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_FunctionMaster_fum_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_FunctionMaster_fum_Info>(); } } } catch (Exception Ex) { throw Ex; } return(infoList); }
private Sys_UserMaster_usm_Info FindAllFunction(Sys_UserMaster_usm_Info info) { string sqlString = string.Empty; sqlString += "SELECT * " + Environment.NewLine; sqlString += "FROM Sys_FunctionMaster_fum " + Environment.NewLine; IEnumerable <Sys_FunctionMaster_fum_Info> infos = null; List <Sys_FunctionMaster_fum_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_FunctionMaster_fum_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_FunctionMaster_fum_Info>(); } foreach (Sys_FunctionMaster_fum_Info t in infoList) { info.functionMasterList.Add(t); } return(info); } } catch (Exception Ex) { throw Ex; } }
public List <DataRightsRole_drr_Info> SearchRecords(Model.IModel.IModelObject searchCondition) { //throw new NotImplementedException(); strSQL = new StringBuilder(); strSQL.AppendLine("SELECT * FROM DataRightsRole_drr"); DataRightsRole_drr_Info info = null; info = searchCondition as DataRightsRole_drr_Info; if (info != null) { strSQL.AppendLine("WHERE 1=1"); if (info.drr_cNumber != string.Empty) { strSQL.AppendLine("AND drr_cNumber='" + info.drr_cNumber + "'"); } if (info.drr_cName != string.Empty) { strSQL.AppendLine("AND drr_cName='" + info.drr_cName + "'"); } } using (VPMSDBDataContext db = new VPMSDBDataContext()) { try { return(db.ExecuteQuery <DataRightsRole_drr_Info>(strSQL.ToString(), new object[] { }).ToList()); } catch { return(null); } } }
/// <summary> /// 獲得數據表各個欄位的長度 /// </summary> /// <typeparam name="TModel">需要返回的類型</typeparam> /// <param name="tableName">數據表名稱</param> /// <returns></returns> public TModel GetTableFieldLenght <TModel>(string tableName) { List <TableFieldLenghtInfo> infoList = null; TModel info = default(TModel); string sqlString = ""; sqlString = "SELECT (name+'_Length') AS FieldName,prec AS FieldLength FROM syscolumns WHERE id=object_id('" + tableName + "')"; try { using (VPMSDBDataContext dbDataContext = new VPMSDBDataContext()) { IEnumerable <TableFieldLenghtInfo> tableFieldLenghtInfoList = null; tableFieldLenghtInfoList = dbDataContext.ExecuteQuery <TableFieldLenghtInfo>(sqlString, new object[] { }); if (tableFieldLenghtInfoList != null) { infoList = tableFieldLenghtInfoList.ToList <TableFieldLenghtInfo>(); } } } catch (Exception Ex) { throw Ex; } info = GetTableFieldLenghtDataInfo <TModel>(infoList); return(info); }
public List <CodeMaster_cmt_Info> FindRecord(CodeMaster_cmt_Info info) { //throw new NotImplementedException(); List <CodeMaster_cmt_Info> list = new List <CodeMaster_cmt_Info>(); //CodeMaster_cmt_Info info = new CodeMaster_cmt_Info(); //info = searchCondition as CodeMaster_cmt_Info; string sqlString = string.Empty; string whereString = string.Empty; sqlString = "SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine; sqlString += "*" + Environment.NewLine; sqlString += "FROM dbo.CodeMaster_cmt" + Environment.NewLine; whereString = "WHERE 1=1" + Environment.NewLine; if (info.cmt_cKey1 != "") { whereString += "AND cmt_cKey1='" + info.cmt_cKey1 + "' " + Environment.NewLine; } if (info.cmt_cKey2 != "") { whereString += "AND cmt_cKey2='" + info.cmt_cKey2 + "' " + Environment.NewLine; } if (info.cmt_cValue != "") { if (info.cmt_cValue.ToString().Contains("*") || info.cmt_cValue.ToString().Contains("?")) { whereString += "AND cmt_cValue LIKE N'" + info.cmt_cValue.ToString().Replace("*", "%").Replace("?", "_") + "' " + Environment.NewLine; } else { whereString += "AND cmt_cValue LIKE N'%" + info.cmt_cValue + "%' " + Environment.NewLine; } } if (info.cmt_fNumber != 0) { whereString += "AND cmt_fNumber=Convert(decimal," + info.cmt_fNumber.ToString() + ")" + Environment.NewLine; } IEnumerable <CodeMaster_cmt_Info> infos = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <CodeMaster_cmt_Info>(sqlString + whereString, new object[] { }); if (infos != null) { list = infos.ToList <CodeMaster_cmt_Info>(); } } } catch (Exception Ex) { throw Ex; } return(list); }
private bool DelUserPurview(Sys_UserPurview_usp_Info info) { string sqlString = string.Empty; sqlString += "SELECT DISTINCT frp_cPurviewCode" + Environment.NewLine; sqlString += "FROM Sys_FormPurview_frp" + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormMaster_fom" + Environment.NewLine; sqlString += "ON frp_cFormNumber=fom_cFormNumber WHERE fom_cFormNumber='" + info.formMasterList[0].fom_cFormNumber + "'" + Environment.NewLine; IEnumerable <Sys_FormPurview_frp_Info> frps = null; List <Sys_FormPurview_frp_Info> frpList = null; IEnumerable <Sys_UserPurview_usp_Info> usps = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { frps = db.ExecuteQuery <Sys_FormPurview_frp_Info>(sqlString, new object[] { }); if (frps != null) { frpList = frps.ToList <Sys_FormPurview_frp_Info>(); } for (int i = 0; i < frpList.Count; i++) { sqlString = "delete from Sys_UserPurview_usp where usp_cPurviewCode='" + frpList[i].frp_cPurviewCode + "'"; usps = db.ExecuteQuery <Sys_UserPurview_usp_Info>(sqlString, new object[] { }); } } return(true); } catch (Exception Ex) { //throw Ex; return(false); } }
public List <Sys_SupplierMaster_slm_Info> SearchRecords(IModelObject searchCondition) { List <Sys_SupplierMaster_slm_Info> resultList = null; string sqlScript = string.Empty; Sys_SupplierMaster_slm_Info queryInfo = searchCondition as Sys_SupplierMaster_slm_Info; if (queryInfo != null) { resultList = new List <Sys_SupplierMaster_slm_Info>(); sqlScript += "select * from dbo.SupplierMaster_slm" + Environment.NewLine; sqlScript += "where 1=1" + Environment.NewLine; if (queryInfo.slm_cClientNum != null && queryInfo.slm_cClientNum != "") { sqlScript += "and slm_cClientNum like '%" + queryInfo.slm_cClientNum + "%'" + Environment.NewLine; } if (queryInfo.slm_cChinaName != null && queryInfo.slm_cChinaName != "") { sqlScript += "and slm_cChinaName like '%" + queryInfo.slm_cChinaName + "%'" + Environment.NewLine; } IEnumerable <Sys_SupplierMaster_slm_Info> infos = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_SupplierMaster_slm_Info>(sqlScript, new object[] { }); if (infos != null) { foreach (Sys_SupplierMaster_slm_Info item in infos) { resultList.Add(item); } } } } catch (Exception Ex) { throw Ex; } } return(resultList); }
/// <summary> /// 取得日志 /// </summary> /// <param name="p_strIP">IP</param> /// <param name="p_strSystemName">系统名</param> /// <param name="p_strModel">模组</param> /// <param name="p_strType">类型</param> /// <param name="p_strOperator">操作人</param> /// <returns></returns> public List <LogDetail_lgd_Info> GetLogs(string p_strIP, string p_strSystemName, string p_strModel, string p_strType, string p_strOperator) { StringBuilder l_strSQL = new StringBuilder(); l_strSQL.AppendLine("Select Top 200 lgd_iID,"); l_strSQL.AppendLine("lgd_cIpAddr,"); l_strSQL.AppendLine("lgd_cSysName,"); l_strSQL.AppendLine("lgd_cClassMethodName,"); l_strSQL.AppendLine("lgd_cLogType,"); l_strSQL.AppendLine("lgd_cLogMessage,"); l_strSQL.AppendLine("lgd_cRemark,"); l_strSQL.AppendLine("lgd_cOperator,"); l_strSQL.AppendLine("lgd_dOperateDateTime"); l_strSQL.AppendLine("From LogDetail_lgd"); l_strSQL.AppendLine("Where 1=1"); if (p_strIP != "") { l_strSQL.AppendLine("And lgd_cIpAddr = '" + p_strIP + "'"); } if (p_strSystemName != "") { l_strSQL.AppendLine("And lgd_cSysName = '" + p_strSystemName + "'"); } if (p_strModel != "") { l_strSQL.AppendLine("And lgd_cClassMethodName = '" + p_strModel + "'"); } if (p_strType != "") { l_strSQL.AppendLine("And lgd_cLogType = '" + p_strType + "'"); } if (p_strOperator != "") { l_strSQL.AppendLine("And lgd_cOperator = '" + p_strOperator + "'"); } l_strSQL.AppendLine("Order By lgd_dOperateDateTime Desc"); using (VPMSDBDataContext db = new VPMSDBDataContext()) { return(db.ExecuteQuery <LogDetail_lgd_Info>(l_strSQL.ToString(), new object[] { }).ToList()); } }
private Sys_UserMaster_usm_Info FindAllForm(Sys_UserMaster_usm_Info info) { string sqlString = string.Empty; sqlString += "SELECT * " + Environment.NewLine; sqlString += "FROM Sys_FormMaster_fom" + Environment.NewLine; //sqlString += "FROM Sys_FormMaster_fom WHERE fom_iWebForm='false'" + Environment.NewLine; IEnumerable <Sys_FormMaster_fom_Info> infos = null; List <Sys_FormMaster_fom_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_FormMaster_fom_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_FormMaster_fom_Info>(); } Sys_UserMaster_usm_Info cond = new Sys_UserMaster_usm_Info(); //// 请处理 cond.usm_cUserLoginID = info.usm_cUserLoginID; ////// foreach (Sys_FormMaster_fom_Info t in infoList) { info.formMasterList.Add(t); //////////////////////// 请处理 cond.formMasterList.Clear(); cond.formMasterList.Add(t); t.functionMaster = SearchRecords(cond)[0].functionMasterList; ////////////////////// } return(info); } } catch (Exception Ex) { throw Ex; } }
public List <ArticleTypeDefine_atd_Info> GetAllChildren(ArticleTypeDefine_atd_Info ArticleTypeInfo) { List <ArticleTypeDefine_atd_Info> ChildrenInfos = new List <ArticleTypeDefine_atd_Info>(); if (ArticleTypeInfo != null) { string sqlScript = string.Empty; sqlScript += "select * " + Environment.NewLine; sqlScript += "from dbo.ArticleTypeDefine_atd" + Environment.NewLine; sqlScript += "where atd_lValid=1" + Environment.NewLine; sqlScript += "and atd_cParentRecordNum='" + ArticleTypeInfo.atd_cTypeNum + "'" + Environment.NewLine; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { IEnumerable <ArticleTypeDefine_atd_Info> IEChildrenInfo = null; IEChildrenInfo = db.ExecuteQuery <ArticleTypeDefine_atd_Info>(sqlScript, new object[] { }); if (IEChildrenInfo != null) { foreach (ArticleTypeDefine_atd_Info item in IEChildrenInfo) { item.hasChild = HasChild(item); ChildrenInfos.Add(item); } } } } catch (Exception Ex) { throw Ex; } } return(ChildrenInfos); }
private Sys_UserPurview_usp_Info FindFunction(Sys_UserPurview_usp_Info info) { Sys_FormMaster_fom_Info formInfo = new Sys_FormMaster_fom_Info(); if (info.formMasterList != null) { formInfo = info.formMasterList[0]; string sqlString = string.Empty; sqlString += "SELECT DISTINCT fum_cFunctionNumber,fum_cFunctionDesc " + Environment.NewLine; sqlString += "FROM Sys_FunctionMaster_fum " + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine; sqlString += "ON fum_cFunctionNumber=frp_cFunctionNumber" + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormMaster_fom" + Environment.NewLine; sqlString += "ON frp_cFormNumber=fom_cFormNumber WHERE fom_cFormNumber='" + formInfo.fom_cFormNumber + "'"; IEnumerable <Sys_FunctionMaster_fum_Info> infos = null; List <Sys_FunctionMaster_fum_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_FunctionMaster_fum_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_FunctionMaster_fum_Info>(); } foreach (Sys_FunctionMaster_fum_Info t in infoList) { info.functionMasterList.Add(t); } } } catch (Exception Ex) { throw Ex; } } return(info); }
public bool UpdateRecord(Model.SysMaster.DepartmentMaster_dpm_Info infoObject) { using (VPMSDBDataContext db = new VPMSDBDataContext()) { //DepartmentMaster_dpm dpm = new DepartmentMaster_dpm(); try { //dpm = db.DepartmentMaster_dpms.SingleOrDefault(t => t.dpm_iRecordID == infoObject.dpm_iRecordID); //model.DepartmentMaster_dpm_Info info = new Model.SysMaster.DepartmentMaster_dpm_Info(); //info = Common.General.CopyObjectValue<DepartmentMaster_dpm, Model.SysMaster.DepartmentMaster_dpm_Info>(dpm); string sqlString = string.Empty; IEnumerable <Model.SysMaster.DepartmentMaster_dpm_Info> IEdpm = null; sqlString += "UPDATE DepartmentMaster_dpm" + Environment.NewLine; sqlString += " SET dpm_cNumber='" + infoObject.dpm_cNumber + "'," + Environment.NewLine; sqlString += " dpm_cName='" + infoObject.dpm_cName + "'," + Environment.NewLine; sqlString += " dpm_cRemark='" + infoObject.dpm_cRemark + "'," + Environment.NewLine; sqlString += " dpm_cLast='" + infoObject.dpm_cLast + "'," + Environment.NewLine; sqlString += " dpm_dLastDate='" + infoObject.dpm_dLastDate.Value.ToString(DefineConstantValue.gc_DateTimeFormat) + "'" + Environment.NewLine; sqlString += " WHERE dpm_iRecordID='" + infoObject.dpm_iRecordID + "'"; IEdpm = db.ExecuteQuery <Model.SysMaster.DepartmentMaster_dpm_Info>(sqlString, new object[] { }); //if (dpm != null) //{ // dpm.dpm_cName = infoObject.dpm_cName; // dpm.dpm_cNumber = infoObject.dpm_cNumber; // dpm.dpm_cRemark = infoObject.dpm_cRemark; // dpm.dpm_cLast = infoObject.dpm_cLast; // dpm.dpm_dLastDate = infoObject.dpm_dLastDate.Value; // db.SubmitChanges(); //} return(true); } catch (Exception Ex) { throw Ex; } } }
DataRightsRole_drr_Info GetClassList(DataRightsRole_drr_Info info) { strSQL = new StringBuilder(); strSQL.AppendLine("SELECT * FROM DataRightsRole_ClassList_dtc"); strSQL.AppendLine("WHERE 1=1"); strSQL.AppendLine("AND dtc_cRoleNumber='" + info.drr_cNumber + "'"); using (VPMSDBDataContext db = new VPMSDBDataContext()) { try { info.classList = db.ExecuteQuery <DataRightsRole_ClassList_dtc_Info>(strSQL.ToString(), new object[] { }).ToList(); } catch { info.classList = new List <DataRightsRole_ClassList_dtc_Info>(); } } return(info); }
public List <Sys_UserMaster_usm_Info> GetRoleUsers(string roleNumber) { List <Sys_UserMaster_usm_Info> usmList = null; if (roleNumber != null && roleNumber.Trim() != "") { string sqlString = string.Empty; sqlString = "SELECT " + Environment.NewLine; sqlString += " usm_cUserLoginID," + Environment.NewLine; sqlString += " usm_cChaName " + Environment.NewLine; sqlString += " FROM DataRightsRoleUser_dru " + Environment.NewLine; sqlString += " INNER JOIN Sys_UserMaster_usm " + Environment.NewLine; sqlString += " ON dur_cUserLoginID=usm_cUserLoginID " + Environment.NewLine; sqlString += " WHERE dru_cRoleNumber='" + roleNumber + "'"; IEnumerable <Sys_UserMaster_usm_Info> infos = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_UserMaster_usm_Info>(sqlString); if (infos != null) { usmList = infos.ToList(); } } } catch (Exception Ex) { throw Ex; } } return(usmList); }
private Sys_FunctionMaster_fum_Info FindFormPurview(Sys_FunctionMaster_fum_Info info) { string sqlString = string.Empty; sqlString += "SELECT fom_cFormNumber,fom_cFormDesc " + Environment.NewLine; sqlString += "FROM Sys_FormMaster_fom " + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine; sqlString += "ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine; sqlString += "LEFT JOIN Sys_FunctionMaster_fum" + Environment.NewLine; sqlString += "ON frp_cFunctionNumber=fum_cFunctionNumber WHERE fum_iRecordID='" + info.fum_iRecordID + "'"; IEnumerable <Sys_FormMaster_fom_Info> infos = null; List <Sys_FormMaster_fom_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_FormMaster_fom_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_FormMaster_fom_Info>(); } foreach (Sys_FormMaster_fom_Info t in infoList) { info.formMaster.Add(t); } return(info); } } catch (Exception Ex) { throw Ex; } }
private Sys_RoleMaster_rlm_Info FindUserRole(Sys_RoleMaster_rlm_Info info) { string sqlString = string.Empty; sqlString += "SELECT usm_cUserLoginID,usm_cChaName " + Environment.NewLine; sqlString += "FROM Sys_UserMaster_usm " + Environment.NewLine; sqlString += "LEFT JOIN Sys_UserRoles_usr" + Environment.NewLine; sqlString += "ON usm_cUserLoginID=usr_cUserLoginID" + Environment.NewLine; sqlString += "LEFT JOIN Sys_RoleMaster_rlm" + Environment.NewLine; sqlString += "ON rlm_cRoleID=usr_cRoleID WHERE rlm_cRoleID='" + info.rlm_cRoleID + "'"; IEnumerable <Sys_UserMaster_usm_Info> infos = null; List <Sys_UserMaster_usm_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_UserMaster_usm_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_UserMaster_usm_Info>(); } foreach (Sys_UserMaster_usm_Info t in infoList) { info.userMasterList.Add(t); } return(info); } } catch (Exception Ex) { throw Ex; } }
DataRightsRole_drr_Info GetTeacherList(DataRightsRole_drr_Info info) { strSQL = new StringBuilder(); strSQL.AppendLine("SELECT dpm_cName AS dtl_cRoleNumber,dtl_cDataRightType,dtl_iIsAllRights,dtl_cTeacherNum,cus_cChaName AS dtl_cTeacherName FROM DataRightsRole_TeacherList_dtl"); strSQL.AppendLine("LEFT JOIN CardUserMaster_cus on dtl_cTeacherNum=cus_cNumber"); strSQL.AppendLine("LEFT JOIN DepartmentMaster_dpm on cus_cDepartmentNum=dpm_cNumber"); strSQL.AppendLine("WHERE 1=1"); strSQL.AppendLine("AND dtl_cRoleNumber='" + info.drr_cNumber + "'"); using (VPMSDBDataContext db = new VPMSDBDataContext()) { try { info.teacherList = db.ExecuteQuery <DataRightsRole_TeacherList_dtl_Info>(strSQL.ToString(), new object[] { }).ToList(); } catch { info.teacherList = new List <DataRightsRole_TeacherList_dtl_Info>(); } } return(info); }
private List <Sys_UserMaster_usm_Info> RoleFunction(List <Sys_UserMaster_usm_Info> info) { string sqlString = string.Empty; string whereString = string.Empty; sqlString = " SELECT distinct TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine; sqlString += " fum_cFunctionNumber " + Environment.NewLine; sqlString += " FROM Sys_FunctionMaster_fum" + Environment.NewLine; sqlString += " LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine; sqlString += " ON frp_cFunctionNumber=fum_cFunctionNumber" + Environment.NewLine; //**** sqlString += " LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine; sqlString += " ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine; sqlString += " LEFT JOIN Sys_RoleMaster_rlm" + Environment.NewLine; sqlString += " ON rlm_cRoleID=usp_cRoleID" + Environment.NewLine; sqlString += " LEFT JOIN Sys_UserRoles_usr" + Environment.NewLine; sqlString += " ON usr_cRoleID=rlm_cRoleID" + Environment.NewLine; sqlString += " LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine; sqlString += " ON usr_cUserLoginID=usm_cUserLoginID" + Environment.NewLine; //**** sqlString += " LEFT JOIN Sys_FormMaster_fom" + Environment.NewLine; sqlString += " ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine; if (info != null) { whereString = " WHERE 1=1 "; if (info[0].formMasterList[0].fom_cFormNumber.Trim() != "") { if (info[0].formMasterList[0].fom_cFormNumber.ToString().Contains("*") || info[0].formMasterList[0].fom_cFormNumber.ToString().Contains("?")) { whereString += " AND fom_cFormNumber LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info[0].formMasterList[0].fom_cFormNumber) + "'"; } else { whereString += "AND fom_cFormNumber = N'" + info[0].formMasterList[0].fom_cFormNumber.ToString().Trim() + "'"; } } //** if (info[0].usm_cUserLoginID.ToString().ToUpper() != "SA") { if (info[0].usm_cUserLoginID != "") { if (info[0].usm_cUserLoginID.ToString().Contains("*") || info[0].usm_cUserLoginID.ToString().Contains("?")) { whereString += " AND usm_cUserLoginID LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info[0].usm_cUserLoginID) + "'"; } else { whereString += "AND usm_cUserLoginID = N'" + info[0].usm_cUserLoginID.ToString().Trim() + "'"; } } } //***** } sqlString += whereString; IEnumerable <Sys_FunctionMaster_fum_Info> infos = null; List <Sys_FunctionMaster_fum_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_FunctionMaster_fum_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_FunctionMaster_fum_Info>(); foreach (Sys_FunctionMaster_fum_Info item in infoList) { if (info[0].functionMasterList.SingleOrDefault(d => d.fum_cFunctionNumber == item.fum_cFunctionNumber) == null) { info[0].functionMasterList.Add(item); } } } } } catch (Exception Ex) { throw Ex; } return(info); }
public List <Sys_UserMaster_usm_Info> SearchRecords(Model.IModel.IModelObject searchCondition) { Sys_UserMaster_usm_Info info = null; info = searchCondition as Sys_UserMaster_usm_Info; string sqlString = string.Empty; string whereString = string.Empty; sqlString = " SELECT distinct TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine; sqlString += " fum_cFunctionNumber " + Environment.NewLine; sqlString += " FROM Sys_FunctionMaster_fum" + Environment.NewLine; sqlString += " LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine; sqlString += " ON frp_cFunctionNumber=fum_cFunctionNumber" + Environment.NewLine; //**** sqlString += " LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine; sqlString += " ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine; sqlString += " LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine; sqlString += " ON usp_cUserLoginID=usm_cUserLoginID" + Environment.NewLine; //**** sqlString += " LEFT JOIN Sys_FormMaster_fom" + Environment.NewLine; sqlString += " ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine; Sys_FormMaster_fom_Info fomInfo = new Sys_FormMaster_fom_Info(); fomInfo = info.formMasterList[0]; if (info != null) { whereString = " WHERE 1=1 "; if (fomInfo.fom_cFormNumber.Trim() != "") { if (fomInfo.fom_cFormNumber.ToString().Contains("*") || fomInfo.fom_cFormNumber.ToString().Contains("?")) { whereString += " AND fom_cFormNumber LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(fomInfo.fom_cFormNumber) + "'"; } else { whereString += "AND fom_cFormNumber = N'" + fomInfo.fom_cFormNumber.ToString().Trim() + "'"; } } //** if (info.usm_cUserLoginID.ToString().ToUpper() != "SA") { if (info.usm_cUserLoginID != "") { if (info.usm_cUserLoginID.ToString().Contains("*") || info.usm_cUserLoginID.ToString().Contains("?")) { whereString += " AND usm_cUserLoginID LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.usm_cUserLoginID) + "'"; } else { whereString += "AND usm_cUserLoginID = N'" + info.usm_cUserLoginID.ToString().Trim() + "'"; } } } //***** } sqlString += whereString; IEnumerable <Sys_FunctionMaster_fum_Info> infos = null; List <Sys_FunctionMaster_fum_Info> infoList = null; List <Sys_UserMaster_usm_Info> usmInfoList = new List <Sys_UserMaster_usm_Info>(); try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_FunctionMaster_fum_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_FunctionMaster_fum_Info>(); info.functionMasterList = infoList; usmInfoList.Add(info); RoleFunction(usmInfoList); } } } catch (Exception Ex) { throw Ex; } return(usmInfoList); }
public bool UpdateRecord(DataRightsRole_drr_Info infoObject) { string sqlString = string.Empty; using (VPMSDBDataContext db = new VPMSDBDataContext()) { DataRightsRole_drr_Info info = new DataRightsRole_drr_Info(); DataRightsRole_drr drrInfo = new DataRightsRole_drr(); try { drrInfo = db.DataRightsRole_drrs.SingleOrDefault(t => t.drr_iRecordID == infoObject.drr_iRecordID); IEnumerable <DataRightsRole_TeacherList_dtl> _teacher = (from ta in db.DataRightsRole_TeacherList_dtls where ta.dtl_cRoleNumber == infoObject.drr_cNumber orderby ta.dtl_iRecordID ascending select ta); db.DataRightsRole_TeacherList_dtls.DeleteAllOnSubmit(_teacher); db.SubmitChanges(); //foreach (var item in _teacher) //{ // db.DataRightsRole_TeacherList_dtls.DeleteOnSubmit(item); // db.SubmitChanges(); //} IEnumerable <DataRightsRole_ClassList_dtc> _class = (from ta in db.DataRightsRole_ClassList_dtcs where ta.dtc_cRoleNumber == infoObject.drr_cNumber orderby ta.dtc_iRecordID ascending select ta); db.DataRightsRole_ClassList_dtcs.DeleteAllOnSubmit(_class); db.SubmitChanges(); //foreach (var item in _class) //{ // db.DataRightsRole_ClassList_dtcs.DeleteOnSubmit(item); // db.SubmitChanges(); //} info = Common.General.CopyObjectValue <DataRightsRole_drr, DataRightsRole_drr_Info>(drrInfo); IEnumerable <DataRightsRole_drr_Info> IDrr = null; IEnumerable <DataRightsRole_TeacherList_dtl_Info> ITeacher = null; IEnumerable <DataRightsRole_ClassList_dtc_Info> IClass = null; sqlString += " UPDATE DataRightsRole_drr" + Environment.NewLine; sqlString += " SET drr_cNumber='" + infoObject.drr_cNumber + "'," + Environment.NewLine; sqlString += " drr_cName='" + infoObject.drr_cName + "'," + Environment.NewLine; sqlString += " drr_cRemark='" + infoObject.drr_cRemark + "'," + Environment.NewLine; sqlString += " drr_cLast='" + infoObject.drr_cLast + "'," + Environment.NewLine; sqlString += " drr_dLastDate='" + infoObject.drr_dLastDate.Value.ToString(DefineConstantValue.gc_DateTimeFormat) + "'" + Environment.NewLine; sqlString += " WHERE drr_iRecordID='" + infoObject.drr_iRecordID + "'"; IDrr = db.ExecuteQuery <DataRightsRole_drr_Info>(sqlString, new object[] { }); if (infoObject.teacherList != null && infoObject.teacherList.Count > 0) { for (int i = 0; i < infoObject.teacherList.Count; i++) { sqlString = string.Empty; DataRightsRole_TeacherList_dtl_Info teacherInfo = infoObject.teacherList[i]; sqlString += "INSERT INTO DataRightsRole_TeacherList_dtl" + Environment.NewLine; sqlString += "(dtl_cRoleNumber, dtl_cDataRightType,dtl_iIsAllRights,dtl_cTeacherNum)" + Environment.NewLine; sqlString += " VALUES " + Environment.NewLine; sqlString += "('" + infoObject.drr_cNumber + "','" + teacherInfo.dtl_cDataRightType + "','" + teacherInfo.dtl_iIsAllRights + "','" + teacherInfo.dtl_cTeacherNum + "')"; ITeacher = db.ExecuteQuery <DataRightsRole_TeacherList_dtl_Info>(sqlString, new object[] { }); } } if (infoObject.classList != null && infoObject.classList.Count > 0) { for (int i = 0; i < infoObject.classList.Count; i++) { sqlString = string.Empty; DataRightsRole_ClassList_dtc_Info classInfo = infoObject.classList[i]; sqlString += "INSERT INTO DataRightsRole_ClassList_dtc" + Environment.NewLine; sqlString += "(dtc_cRoleNumber, dtc_cDataRightType,dtc_iIsAllRights,dtc_cSchoolNum,dtc_cSpecialtyNum,dtc_cGraduationPeriod,dtc_cClassNum)" + Environment.NewLine; sqlString += " VALUES " + Environment.NewLine; sqlString += "('" + infoObject.drr_cNumber + "','" + classInfo.dtc_cDataRightType + "','" + classInfo.dtc_iIsAllRights + "','" + classInfo.dtc_cSchoolNum + "','" + classInfo.dtc_cSpecialtyNum + "','" + classInfo.dtc_cGraduationPeriod + "','" + classInfo.dtc_cClassNum + "')"; IClass = db.ExecuteQuery <DataRightsRole_ClassList_dtc_Info>(sqlString, new object[] { }); } } return(true); } catch { return(false); } } }
private Sys_UserMaster_usm_Info FindForm(Sys_UserMaster_usm_Info info) { string sqlString = string.Empty; sqlString += "SELECT distinct fom_iRecordID, fom_iParentID, fom_iIndex, fom_cFormNumber, fom_cFormDesc, fom_cExePath,fom_cWebPath,fom_iWebForm " + Environment.NewLine; sqlString += "FROM Sys_FormMaster_fom " + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine; sqlString += "ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine; sqlString += "LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine; sqlString += "ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine; sqlString += "LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine; sqlString += "ON usp_cUserLoginID=usm_cUserLoginID WHERE usm_cUserLoginID='" + info.usm_cUserLoginID + "'"; //sqlString += "ON usp_cUserLoginID=usm_cUserLoginID WHERE usm_cUserLoginID='" + info.usm_cUserLoginID + "' AND fom_iWebForm='false'"; string sqlRole = string.Empty; sqlRole += "SELECT distinct fom_iRecordID, fom_iParentID, fom_iIndex, fom_cFormNumber, fom_cFormDesc, fom_cExePath,fom_cWebPath,fom_iWebForm " + Environment.NewLine; sqlRole += "FROM Sys_FormMaster_fom " + Environment.NewLine; sqlRole += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine; sqlRole += "ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine; sqlRole += "LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine; sqlRole += "ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine; sqlRole += "LEFT JOIN Sys_RoleMaster_rlm" + Environment.NewLine; sqlRole += "ON usp_cRoleID=rlm_cRoleID" + Environment.NewLine; sqlRole += "LEFT JOIN Sys_UserRoles_usr" + Environment.NewLine; sqlRole += "ON rlm_cRoleID=usr_cRoleID" + Environment.NewLine; sqlRole += "LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine; sqlRole += "ON usr_cUserLoginID=usm_cUserLoginID WHERE usm_cUserLoginID='" + info.usm_cUserLoginID + "'"; string sql = "SELECT distinct a.* FROM( (" + sqlString + ") union (" + sqlRole + ")) as a"; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { Sys_UserMaster_usm_Info cond = new Sys_UserMaster_usm_Info(); //// 请处理 cond.usm_cUserLoginID = info.usm_cUserLoginID; ////// List <Sys_FormMaster_fom_Info> list = new List <Sys_FormMaster_fom_Info>(); var a = db.ExecuteQuery <Sys_FormMaster_fom_Info>(sql, new object[] { }); foreach (Sys_FormMaster_fom_Info t in a) { info.formMasterList.Add(t); ////////////////////// 请处理 cond.formMasterList.Clear(); cond.formMasterList.Add(t); t.functionMaster = SearchRecords(cond)[0].functionMasterList; ////////////////////// } return(info); } } catch (Exception Ex) { throw Ex; } }
public List <Sys_RoleMaster_rlm_Info> SearchRecords(Model.IModel.IModelObject searchCondition) { string sqlString = string.Empty; string whereString = string.Empty; sqlString = "SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine; sqlString += " rlm_iRecordID," + Environment.NewLine; sqlString += " rlm_cRoleID," + Environment.NewLine; sqlString += " rlm_cRoleDesc," + Environment.NewLine; //sqlString += " usm_cPasswork," + Environment.NewLine; //sqlString += " usm_cEMail," + Environment.NewLine; //sqlString += " (CASE usm_iLock WHEN 1 THEN N'已锁' WHEN 0 THEN N'未锁' END) AS iLock," + Environment.NewLine; //sqlString += " usm_cRemark," + Environment.NewLine; sqlString += " rlm_cAdd," + Environment.NewLine; sqlString += " rlm_dAddDate," + Environment.NewLine; sqlString += " rlm_cLast," + Environment.NewLine; sqlString += " rlm_dLastDate " + Environment.NewLine; sqlString += " FROM Sys_RoleMaster_rlm" + Environment.NewLine; Sys_RoleMaster_rlm_Info info = null; info = searchCondition as Sys_RoleMaster_rlm_Info; if (info != null) { whereString = " WHERE 1=1 "; if (info.rlm_cRoleID.Trim() != "") { if (info.rlm_cRoleID.ToString().Contains("*") || info.rlm_cRoleID.ToString().Contains("?")) { whereString += " AND rlm_cRoleID LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.rlm_cRoleID) + "'"; } else { whereString += "AND rlm_cRoleID = N'" + info.rlm_cRoleID.ToString().Trim() + "'"; } } } sqlString += whereString; IEnumerable <Sys_RoleMaster_rlm_Info> infos = null; List <Sys_RoleMaster_rlm_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_RoleMaster_rlm_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_RoleMaster_rlm_Info>(); } } } catch (Exception Ex) { throw Ex; } return(infoList); }
public bool UpdateRecord(Sys_FormMaster_fom_Info infoObject) { using (VPMSDBDataContext db = new VPMSDBDataContext()) { Sys_FormMaster_fom frm = new Sys_FormMaster_fom(); try { frm = db.Sys_FormMaster_foms.SingleOrDefault(t => t.fom_iRecordID == infoObject.fom_iRecordID); for (int i = 0; i < frm.Sys_FormPurview_frps.Count; i++) { Sys_FormPurview_frp sta = frm.Sys_FormPurview_frps[i]; db.Sys_FormPurview_frps.DeleteOnSubmit(sta); } string sqlString = string.Empty; Sys_FormMaster_fom_Info info = new Sys_FormMaster_fom_Info(); info = Common.General.CopyObjectValue <Sys_FormMaster_fom, Sys_FormMaster_fom_Info>(frm); IEnumerable <Sys_FormMaster_fom_Info> IEfom = null; IEnumerable <Sys_FormPurview_frp_Info> IEfrp = null; sqlString += "UPDATE Sys_FormMaster_fom" + Environment.NewLine; sqlString += " SET fom_cFormNumber='" + infoObject.fom_cFormNumber + "'," + Environment.NewLine; sqlString += " fom_cFormDesc='" + infoObject.fom_cFormDesc + "'," + Environment.NewLine; sqlString += " fom_cExePath='" + infoObject.fom_cExePath + "'," + Environment.NewLine; sqlString += " fom_cWebPath='" + infoObject.fom_cWebPath + "'," + Environment.NewLine; sqlString += " fom_iWebForm='" + infoObject.fom_iWebForm + "'," + Environment.NewLine; //sqlString += " fom_cExePath='" + infoObject.rlm_cLast + "'," + Environment.NewLine; sqlString += " fom_iParentID='" + infoObject.fom_iParentID + "'," + Environment.NewLine; sqlString += " fom_iIndex='" + infoObject.fom_iIndex + "'," + Environment.NewLine; sqlString += " fom_cLast='" + infoObject.fom_cLast + "'," + Environment.NewLine; sqlString += " fom_dLastDate='" + infoObject.fom_dLastDate.Value.ToString(DefineConstantValue.gc_DateTimeFormat) + "'" + Environment.NewLine; sqlString += " WHERE fom_iRecordID='" + infoObject.fom_iRecordID + "'"; IEfom = db.ExecuteQuery <Sys_FormMaster_fom_Info>(sqlString, new object[] { }); sqlString = string.Empty; sqlString += "DELETE FROM Sys_FormPurview_frp WHERE frp_cFormNumber='" + infoObject.fom_cFormNumber + "'"; IEfrp = db.ExecuteQuery <Sys_FormPurview_frp_Info>(sqlString, new object[] { }); if (infoObject.functionMaster != null && infoObject.functionMaster.Count > 0) { for (int i = 0; i < infoObject.functionMaster.Count; i++) { sqlString = string.Empty; Sys_FunctionMaster_fum_Info fomInfo = infoObject.functionMaster[i]; string frp_code = fomInfo.fum_cFunctionNumber + infoObject.fom_cFormNumber; sqlString += "INSERT INTO Sys_FormPurview_frp" + Environment.NewLine; sqlString += "(frp_cPurviewCode, frp_cFunctionNumber, frp_cFormNumber)" + Environment.NewLine; sqlString += " VALUES " + Environment.NewLine; sqlString += "('" + frp_code + "','" + fomInfo.fum_cFunctionNumber + "','" + infoObject.fom_cFormNumber + "')"; IEfrp = db.ExecuteQuery <Sys_FormPurview_frp_Info>(sqlString, new object[] { }); } } //if (frm != null) //{ // frm.fom_cFormNumber = infoObject.fom_cFormNumber; // frm.fom_cFormDesc = infoObject.fom_cFormDesc; // frm.fom_cExePath = infoObject.fom_cExePath; // frm.fom_cWebPath = infoObject.fom_cWebPath; // frm.fom_iWebForm = infoObject.fom_iWebForm; // //frm.fom_iImageIndex = infoObject.fom_iImageIndex; // frm.fom_iParentID = infoObject.fom_iParentID; // frm.fom_iIndex = infoObject.fom_iIndex; // frm.fom_cLast = infoObject.fom_cLast; // frm.fom_dLastDate = infoObject.fom_dLastDate.Value; // if (infoObject.functionMaster != null && infoObject.functionMaster.Count > 0) // { // for (int i = 0; i < infoObject.functionMaster.Count; i++) // { // Sys_FunctionMaster_fum_Info usmInfo = infoObject.functionMaster[i]; // Sys_FormPurview_frp item = new Sys_FormPurview_frp(); // item.frp_cFunctionNumber = usmInfo.fum_cFunctionNumber; // item.frp_cFormNumber = infoObject.fom_cFormNumber; // item.frp_cPurviewCode = item.frp_cFunctionNumber + item.frp_cFormNumber; // //courseitem.cum_cNumber; // frm.Sys_FormPurview_frps.Add(item); // //db.Sys_UserRoles_usrs.InsertOnSubmit(item); // } // } // db.SubmitChanges(); //} return(true); } catch (Exception Ex) { throw Ex; } } }
private Sys_UserPurview_usp_Info FindFunctionByUR(Sys_UserPurview_usp_Info info) { string temp = string.Empty; Sys_FormMaster_fom_Info formInfo = new Sys_FormMaster_fom_Info(); if (info.formMasterList != null) { formInfo = info.formMasterList[0]; if (info.roleMasterList[0].rlm_cRoleID != "") { temp = info.roleMasterList[0].rlm_cRoleID.ToString(); } else { temp = info.userMasterList[0].usm_cUserLoginID.ToString(); } string sqlString = string.Empty; sqlString += "SELECT DISTINCT fum_cFunctionNumber,fum_cFunctionDesc " + Environment.NewLine; sqlString += "FROM Sys_FunctionMaster_fum " + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine; sqlString += "ON fum_cFunctionNumber=frp_cFunctionNumber" + Environment.NewLine; sqlString += "LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine; sqlString += "ON usp_cPurviewCode=frp_cPurviewCode" + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormMaster_fom" + Environment.NewLine; if (info.roleMasterList[0].rlm_cRoleID != "") { sqlString += "ON frp_cFormNumber=fom_cFormNumber WHERE fom_cFormNumber='" + formInfo.fom_cFormNumber + "' AND rlm_cRoleID='" + temp + "'"; } else { sqlString += "ON frp_cFormNumber=fom_cFormNumber WHERE fom_cFormNumber='" + formInfo.fom_cFormNumber + "' AND usp_cUserLoginID='" + temp + "'"; } IEnumerable <Sys_FunctionMaster_fum_Info> infos = null; List <Sys_FunctionMaster_fum_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_FunctionMaster_fum_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_FunctionMaster_fum_Info>(); } foreach (Sys_FunctionMaster_fum_Info t in infoList) { info.functionMasterList.Add(t); } } } catch (Exception Ex) { throw Ex; } } return(info); }
private Sys_UserPurview_usp_Info FindUser(Sys_UserPurview_usp_Info info) { Sys_FormMaster_fom_Info formInfo = new Sys_FormMaster_fom_Info(); List <Sys_FormMaster_fom_Info> formList = new List <Sys_FormMaster_fom_Info>(); formList = info.formMasterList; if (formList != null) { formInfo = info.formMasterList[0]; string sqlString = string.Empty; sqlString += "SELECT DISTINCT usm_cUserLoginID,usm_cChaName " + Environment.NewLine; sqlString += "FROM Sys_UserMaster_usm " + Environment.NewLine; sqlString += "LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine; sqlString += "ON usm_cUserLoginID=usp_cUserLoginID" + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine; sqlString += "ON usp_cPurviewCode=frp_cPurviewCode" + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormMaster_fom" + Environment.NewLine; sqlString += "ON frp_cFormNumber=fom_cFormNumber WHERE fom_cFormNumber='" + formInfo.fom_cFormNumber + "'"; IEnumerable <Sys_UserMaster_usm_Info> infos = null; List <Sys_UserMaster_usm_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_UserMaster_usm_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_UserMaster_usm_Info>(); } foreach (Sys_UserMaster_usm_Info t in infoList) { info.userMasterList.Add(t); } } } catch (Exception Ex) { throw Ex; } for (int i = 0; i < info.userMasterList.Count; i++) { sqlString = string.Empty; sqlString += "SELECT DISTINCT fum_cFunctionNumber,fum_cFunctionDesc" + Environment.NewLine; sqlString += "FROM Sys_FunctionMaster_fum" + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine; sqlString += "ON fum_cFunctionNumber=frp_cFunctionNumber" + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormMaster_fom" + Environment.NewLine; sqlString += "ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine; sqlString += "LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine; sqlString += "ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine; sqlString += "LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine; sqlString += "ON usp_cUserLoginID=usm_cUserLoginID WHERE usm_cUserLoginID='" + info.userMasterList[i].usm_cUserLoginID + "' AND fom_cFormNumber='" + formInfo.fom_cFormNumber + "'"; IEnumerable <Sys_FunctionMaster_fum_Info> fums = null; List <Sys_FunctionMaster_fum_Info> fumList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { fums = db.ExecuteQuery <Sys_FunctionMaster_fum_Info>(sqlString, new object[] { }); if (fums != null) { fumList = fums.ToList <Sys_FunctionMaster_fum_Info>(); } List <Sys_FunctionMaster_fum_Info> fumList1 = new List <Sys_FunctionMaster_fum_Info>(); foreach (Sys_FunctionMaster_fum_Info t in fumList) { fumList1.Add(t); //info.userMasterList[i].functionMasterList.Add(t); //info.userMasterList.Add(t); } info.userMasterList[i].functionMasterList = fumList1; } } catch (Exception Ex) { throw Ex; } } } return(info); }
private Sys_UserMaster_usm_Info FindFunction(Sys_UserMaster_usm_Info info) { string sqlString = string.Empty; sqlString += "SELECT fum_iRecordID, fum_cFunctionNumber, fum_cFunctionDesc, fum_cRemark " + Environment.NewLine; sqlString += "FROM Sys_FunctionMaster_fum " + Environment.NewLine; sqlString += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine; sqlString += "ON fum_cFunctionNumber=frp_cFunctionNumber" + Environment.NewLine; sqlString += "LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine; sqlString += "ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine; sqlString += "LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine; sqlString += "ON usp_cUserLoginID=usm_cUserLoginID WHERE usm_cUserLoginID='" + info.usm_cUserLoginID + "'"; IEnumerable <Sys_FunctionMaster_fum_Info> infos = null; List <Sys_FunctionMaster_fum_Info> infoList = null; string sqlRole = string.Empty; sqlRole += "SELECT fum_iRecordID, fum_cFunctionNumber, fum_cFunctionDesc, fum_cRemark " + Environment.NewLine; sqlRole += "FROM Sys_FunctionMaster_fum " + Environment.NewLine; sqlRole += "LEFT JOIN Sys_FormPurview_frp" + Environment.NewLine; sqlRole += "ON fum_cFunctionNumber=frp_cFunctionNumber" + Environment.NewLine; sqlRole += "LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine; sqlRole += "ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine; sqlRole += "LEFT JOIN Sys_RoleMaster_rlm" + Environment.NewLine; sqlRole += "ON usp_cRoleID=rlm_cRoleID" + Environment.NewLine; sqlRole += "LEFT JOIN Sys_UserRoles_usr" + Environment.NewLine; sqlRole += "ON rlm_cRoleID=usr_cRoleID" + Environment.NewLine; sqlRole += "LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine; sqlRole += "ON usr_cUserLoginID=usm_cUserLoginID WHERE usm_cUserLoginID='" + info.usm_cUserLoginID + "'"; IEnumerable <Sys_FunctionMaster_fum_Info> roleInfos = null; List <Sys_FunctionMaster_fum_Info> roleInfoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_FunctionMaster_fum_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_FunctionMaster_fum_Info>(); } foreach (Sys_FunctionMaster_fum_Info t in infoList) { info.functionMasterList.Add(t); } roleInfos = db.ExecuteQuery <Sys_FunctionMaster_fum_Info>(sqlRole, new object[] { }); if (roleInfos != null) { roleInfoList = roleInfos.ToList <Sys_FunctionMaster_fum_Info>(); } foreach (Sys_FunctionMaster_fum_Info t in roleInfoList) { info.functionMasterList.Add(t); } return(info); } } catch (Exception Ex) { throw Ex; } }
public List <Sys_FormMaster_fom_Info> SearchRecords(Model.IModel.IModelObject searchCondition, string all) { string sqlString = string.Empty; string whereString = string.Empty; sqlString = "SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine; sqlString += " fom_iRecordID," + Environment.NewLine; sqlString += " fom_iParentID," + Environment.NewLine; sqlString += " fom_iIndex," + Environment.NewLine; sqlString += " fom_cFormNumber," + Environment.NewLine; sqlString += " fom_cFormDesc," + Environment.NewLine; sqlString += " fom_cExePath," + Environment.NewLine; sqlString += " fom_cWebPath," + Environment.NewLine; sqlString += " fom_iWebForm," + Environment.NewLine; sqlString += " fom_iImageIndex," + Environment.NewLine; sqlString += " fom_cRemark," + Environment.NewLine; sqlString += " fom_cAdd," + Environment.NewLine; sqlString += " fom_dAddDate," + Environment.NewLine; sqlString += " fom_cLast," + Environment.NewLine; sqlString += " fom_dLastDate " + Environment.NewLine; sqlString += " FROM Sys_FormMaster_fom" + Environment.NewLine; Sys_FormMaster_fom_Info info = null; info = searchCondition as Sys_FormMaster_fom_Info; if (info != null) { whereString = " WHERE 1=1 AND fom_iWebForm='false' "; if (info.fom_iParentID != 0) { if (info.fom_iParentID.ToString().Contains("*") || info.fom_iParentID.ToString().Contains("?")) { whereString += " AND fom_iParentID LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.fom_iParentID.ToString()) + "'"; } else { whereString += "AND fom_iParentID = N'" + info.fom_iParentID.ToString().Trim() + "'"; } } // if (info.fom_iIndex != 0) { if (info.fom_iIndex.ToString().Contains("*") || info.fom_iIndex.ToString().Contains("?")) { whereString += " AND fom_iIndex LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.fom_iIndex.ToString()) + "'"; } else { whereString += "AND fom_iIndex = N'" + info.fom_iIndex.ToString().Trim() + "'"; } } // if (info.fom_cFormNumber.ToString().Trim() != "") { if (info.fom_cFormNumber.ToString().Contains("*") || info.fom_cFormNumber.ToString().Contains("?")) { whereString += " AND fom_cFormNumber LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.fom_cFormNumber.ToString()) + "'"; } else { whereString += "AND fom_cFormNumber = N'" + info.fom_cFormNumber.ToString().Trim() + "'"; } } } sqlString += whereString + Environment.NewLine; sqlString += " ORDER BY fom_iIndex" + Environment.NewLine; IEnumerable <Sys_FormMaster_fom_Info> infos = null; List <Sys_FormMaster_fom_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_FormMaster_fom_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_FormMaster_fom_Info>(); } } } catch (Exception Ex) { throw Ex; } return(infoList); }
public List <Sys_FormMaster_fom_Info> SearchRecords(Model.IModel.IModelObject searchCondition, Sys_UserMaster_usm_Info userInfo) { string sqlString = string.Empty; string whereString = string.Empty; sqlString = "SELECT DISTINCT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine; sqlString += " fom_iRecordID," + Environment.NewLine; sqlString += " fom_iParentID," + Environment.NewLine; sqlString += " fom_iIndex," + Environment.NewLine; sqlString += " fom_cFormNumber," + Environment.NewLine; sqlString += " fom_cFormDesc," + Environment.NewLine; sqlString += " fom_cExePath," + Environment.NewLine; sqlString += " fom_cWebPath," + Environment.NewLine; sqlString += " fom_iWebForm," + Environment.NewLine; sqlString += " fom_iImageIndex," + Environment.NewLine; sqlString += " fom_cRemark," + Environment.NewLine; sqlString += " fom_cAdd," + Environment.NewLine; sqlString += " fom_dAddDate," + Environment.NewLine; sqlString += " fom_cLast," + Environment.NewLine; sqlString += " fom_dLastDate " + Environment.NewLine; sqlString += " FROM Sys_FormMaster_fom" + Environment.NewLine; sqlString += " LEFT JOIN Sys_FormPurview_frp " + Environment.NewLine; sqlString += " ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine; sqlString += " LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine; sqlString += " ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine; sqlString += " LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine; sqlString += " ON usp_cUserLoginID=usm_cUserLoginID" + Environment.NewLine; /**/ string sqlRole = string.Empty; sqlRole = "SELECT DISTINCT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine; sqlRole += " fom_iRecordID," + Environment.NewLine; sqlRole += " fom_iParentID," + Environment.NewLine; sqlRole += " fom_iIndex," + Environment.NewLine; sqlRole += " fom_cFormNumber," + Environment.NewLine; sqlRole += " fom_cFormDesc," + Environment.NewLine; sqlRole += " fom_cExePath," + Environment.NewLine; sqlRole += " fom_cWebPath," + Environment.NewLine; sqlRole += " fom_iWebForm," + Environment.NewLine; sqlRole += " fom_iImageIndex," + Environment.NewLine; sqlRole += " fom_cRemark," + Environment.NewLine; sqlRole += " fom_cAdd," + Environment.NewLine; sqlRole += " fom_dAddDate," + Environment.NewLine; sqlRole += " fom_cLast," + Environment.NewLine; sqlRole += " fom_dLastDate " + Environment.NewLine; sqlRole += " FROM Sys_FormMaster_fom" + Environment.NewLine; sqlRole += " LEFT JOIN Sys_FormPurview_frp " + Environment.NewLine; sqlRole += " ON fom_cFormNumber=frp_cFormNumber" + Environment.NewLine; sqlRole += " LEFT JOIN Sys_UserPurview_usp" + Environment.NewLine; sqlRole += " ON frp_cPurviewCode=usp_cPurviewCode" + Environment.NewLine; sqlRole += " LEFT JOIN Sys_RoleMaster_rlm" + Environment.NewLine; sqlRole += " ON usp_cRoleID=rlm_cRoleID" + Environment.NewLine; sqlRole += " LEFT JOIN Sys_UserRoles_usr" + Environment.NewLine; sqlRole += " ON usr_cRoleID=rlm_cRoleID" + Environment.NewLine; sqlRole += " LEFT JOIN Sys_UserMaster_usm" + Environment.NewLine; sqlRole += " ON usr_cUserLoginID=usm_cUserLoginID" + Environment.NewLine; /**/ Sys_FormMaster_fom_Info info = null; info = searchCondition as Sys_FormMaster_fom_Info; if (info != null) { whereString = " WHERE 1=1 AND fom_iWebForm='false' "; if (info.fom_iParentID != 0) { if (info.fom_iParentID.ToString().Contains("*") || info.fom_iParentID.ToString().Contains("?")) { whereString += " AND fom_iParentID LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(info.fom_iParentID.ToString()) + "'"; } else { whereString += "AND fom_iParentID = N'" + info.fom_iParentID.ToString().Trim() + "'"; } } if (userInfo.usm_cUserLoginID != "") { if (userInfo.usm_cUserLoginID.ToString().Contains("*") || userInfo.usm_cUserLoginID.ToString().Contains("?")) { whereString += " AND usm_cUserLoginID LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(userInfo.usm_cUserLoginID.ToString()) + "'"; } else { whereString += "AND usm_cUserLoginID = N'" + userInfo.usm_cUserLoginID.ToString().Trim() + "'"; } } } sqlString += whereString + Environment.NewLine; sqlString += " ORDER BY fom_iIndex" + Environment.NewLine; /**/ sqlRole += whereString + Environment.NewLine; sqlRole += " ORDER BY fom_iIndex" + Environment.NewLine; IEnumerable <Sys_FormMaster_fom_Info> roleInfos = null; List <Sys_FormMaster_fom_Info> roleInfoList = null; /**/ IEnumerable <Sys_FormMaster_fom_Info> infos = null; List <Sys_FormMaster_fom_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <Sys_FormMaster_fom_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Sys_FormMaster_fom_Info>(); } /**/ roleInfos = db.ExecuteQuery <Sys_FormMaster_fom_Info>(sqlRole, new object[] { }); if (roleInfos != null) { roleInfoList = roleInfos.ToList <Sys_FormMaster_fom_Info>(); } /**/ //删除重复的再添加 for (int i = 0; i < infoList.Count; i++) { for (int j = 0; j < roleInfoList.Count; j++) { if (infoList[i].fom_cFormNumber == roleInfoList[j].fom_cFormNumber) { roleInfoList.Remove(roleInfoList[j]); j = 0; } } } foreach (Sys_FormMaster_fom_Info t in roleInfoList) { infoList.Add(t); } //******** } } catch (Exception Ex) { throw Ex; } return(infoList); }
public List <Model.SysMaster.DepartmentMaster_dpm_Info> SearchRecords(Model.IModel.IModelObject MDobject) { string sqlString = string.Empty; string whereString = string.Empty; sqlString = "SELECT TOP " + Common.DefineConstantValue.ListRecordMaxCount.ToString() + Environment.NewLine; sqlString += " dpm_iRecordID," + Environment.NewLine; sqlString += " dpm_cNumber," + Environment.NewLine; sqlString += " dpm_cName," + Environment.NewLine; sqlString += " dpm_cRemark, " + Environment.NewLine; sqlString += " dpm_cAdd," + Environment.NewLine; sqlString += " dpm_dAddDate," + Environment.NewLine; sqlString += " dpm_cLast," + Environment.NewLine; sqlString += " dpm_dLastDate " + Environment.NewLine; sqlString += " FROM DepartmentMaster_dpm" + Environment.NewLine; model.DepartmentMaster_dpm_Info dpm_info = null; dpm_info = MDobject as model.DepartmentMaster_dpm_Info; if (dpm_info != null) { whereString = " WHERE 1=1 "; if (dpm_info.dpm_cNumber.Trim() != "") { if (dpm_info.dpm_cNumber.ToString().Contains("*") || dpm_info.dpm_cNumber.ToString().Contains("?")) { whereString += " AND dpm_cNumber LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(dpm_info.dpm_cNumber) + "'"; } else { whereString += "AND dpm_cNumber = N'" + dpm_info.dpm_cNumber.ToString().Trim() + "'"; } } if (dpm_info.dpm_cName.Trim() != "") { if (dpm_info.dpm_cName.ToString().Contains("*") || dpm_info.dpm_cName.ToString().Contains("?")) { whereString += " AND dpm_cName LIKE N'" + LocalDefine.General.ReplaceSQLLikeCondition(dpm_info.dpm_cName) + "'"; } else { whereString += "AND dpm_cName = N'" + dpm_info.dpm_cName.ToString().Trim() + "'"; } } } sqlString += whereString; IEnumerable <model.DepartmentMaster_dpm_Info> infos = null; List <Model.SysMaster.DepartmentMaster_dpm_Info> infoList = null; try { using (VPMSDBDataContext db = new VPMSDBDataContext()) { infos = db.ExecuteQuery <model.DepartmentMaster_dpm_Info>(sqlString, new object[] { }); if (infos != null) { infoList = infos.ToList <Model.SysMaster.DepartmentMaster_dpm_Info>(); } } } catch (Exception Ex) { throw Ex; } return(infoList); }
public bool UpdateRecord(Sys_RoleMaster_rlm_Info infoObject) { string sqlString = string.Empty; using (VPMSDBDataContext db = new VPMSDBDataContext()) { Sys_RoleMaster_rlm rlm = new Sys_RoleMaster_rlm(); Sys_RoleMaster_rlm_Info info = new Sys_RoleMaster_rlm_Info(); try { rlm = db.Sys_RoleMaster_rlms.SingleOrDefault(t => t.rlm_iRecordID == infoObject.rlm_iRecordID); for (int i = 0; i < rlm.Sys_UserRoles_usrs.Count; i++) { Sys_UserRoles_usr sta = rlm.Sys_UserRoles_usrs[i]; db.Sys_UserRoles_usrs.DeleteOnSubmit(sta); } info = Common.General.CopyObjectValue <Sys_RoleMaster_rlm, Sys_RoleMaster_rlm_Info>(rlm); IEnumerable <Sys_RoleMaster_rlm_Info> IErlm = null; IEnumerable <Sys_UserRoles_usr_Info> IEusr = null; sqlString += "UPDATE Sys_RoleMaster_rlm" + Environment.NewLine; sqlString += " SET rlm_cRoleID='" + infoObject.rlm_cRoleID + "'," + Environment.NewLine; sqlString += " rlm_cRoleDesc='" + infoObject.rlm_cRoleDesc + "'," + Environment.NewLine; sqlString += " rlm_cLast='" + infoObject.rlm_cLast + "'," + Environment.NewLine; sqlString += " rlm_dLastDate='" + infoObject.rlm_dLastDate.Value.ToString(DefineConstantValue.gc_DateTimeFormat) + "'" + Environment.NewLine; sqlString += " WHERE rlm_iRecordID='" + infoObject.rlm_iRecordID + "'"; IErlm = db.ExecuteQuery <Sys_RoleMaster_rlm_Info>(sqlString, new object[] { }); sqlString = string.Empty; sqlString += "DELETE FROM Sys_UserRoles_usr WHERE usr_cRoleID='" + infoObject.rlm_cRoleID + "'"; IEusr = db.ExecuteQuery <Sys_UserRoles_usr_Info>(sqlString, new object[] { }); if (infoObject.userMasterList != null && infoObject.userMasterList.Count > 0) { for (int i = 0; i < infoObject.userMasterList.Count; i++) { sqlString = string.Empty; Sys_UserMaster_usm_Info usmInfo = infoObject.userMasterList[i]; sqlString += "INSERT INTO Sys_UserRoles_usr" + Environment.NewLine; sqlString += "(usr_cUserLoginID, usr_cRoleID)" + Environment.NewLine; sqlString += " VALUES " + Environment.NewLine; sqlString += "('" + usmInfo.usm_cUserLoginID + "','" + infoObject.rlm_cRoleID + "')"; IEusr = db.ExecuteQuery <Sys_UserRoles_usr_Info>(sqlString, new object[] { }); } } //if (rlm != null) //{ // //usm = Common.General.SetDataToLingQEntity(usm, infoObject);//时间溢出 // rlm.rlm_cRoleDesc = infoObject.rlm_cRoleDesc; // rlm.rlm_cLast = infoObject.rlm_cLast; // rlm.rlm_dLastDate = infoObject.rlm_dLastDate.Value; // if (infoObject.userMasterList != null && infoObject.userMasterList.Count > 0) // { // for (int i = 0; i < infoObject.userMasterList.Count; i++) // { // Sys_UserMaster_usm_Info usmInfo = infoObject.userMasterList[i]; // Sys_UserRoles_usr item = new Sys_UserRoles_usr(); // item.usr_cUserLoginID = usmInfo.usm_cUserLoginID; // item.usr_cRoleID = infoObject.rlm_cRoleID; // //courseitem.cum_cNumber; // rlm.Sys_UserRoles_usrs.Add(item); // //db.Sys_UserRoles_usrs.InsertOnSubmit(item); // } // } // db.SubmitChanges(); //} return(true); } catch (Exception Ex) { throw Ex; } } }