/// <summary>
/// 获得数据列表 分页
/// </summary>
/// <returns></returns>
public DataTable GetList(string strWhere, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM T_SystemInfo ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0]);
}
public DataTable GetQuestionList(string strWhere, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select *,(select f.SystemInfoName from T_SystemInfo f WHERE f.CurrentType='QuestionType' AND LOWER(f.SystemInfoCode)=t_question.QuestionTypeCode )as QuestionTypeTitle from t_question ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0];
}
/// <summary>
/// 获得数据列表 分页
/// </summary>
/// <returns></returns>
public DataTable GetList(string strWhere, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select QuestionTypeID,Title,ToUserIDList,ToUserNameList,OnlyToMyDirectionUser,OrderIndex ");
strSql.Append(" FROM T_QuestionType ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0]);
}
/// <summary>
/// 获得数据列表 分页
/// </summary>
/// <returns></returns>
public DataTable GetListPaging(string strWhere, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select T_OperationLog.*,");
strSql.Append("(select SystemInfoName from T_SystemInfo where CurrentType='OperationType' and lower(SystemInfoCode)=lower(T_OperationLog.Description))as OperationTypeName");
strSql.Append(" from T_OperationLog ");
if (strWhere.Trim() != "")
{
strSql.Append("where " + strWhere);
}
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0]);
}
/// <summary>
/// 获得数据列表 分页
/// </summary>
/// <returns></returns>
public DataTable GetMyChildCompany(Hashtable ht, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT a.*,c.SystemInfoName as CompanyTypeName,");
strSql.Append(" b.UserName,b.UserID,b.LoginName,b.RoleID,d.RoleName ");
strSql.Append(" from T_Company a INNER JOIN T_UsersInfo b ON a.CompanyID=b.CompanyID ");
strSql.Append(" and a.IsCompany=1 and b.IsValid=1 ");
strSql.Append(" INNER JOIN T_SystemInfo c ON a.CompanyType=c.SystemInfoID ");
strSql.Append(" INNER JOIN T_Role d ON b.RoleID=d.RoleID ");
strSql.Append(" where 1=1 ");
if (ht.ContainsKey("RoleCode") && Common.ConvertEx.ToString(ht["RoleCode"]).Length > 0 &&
ht.ContainsKey("RoleType") && Common.ConvertEx.ToString(ht["RoleType"]).Length > 0)
{
strSql.Append(" and lower(d.RoleCode)='" + Common.ConvertEx.ToString(ht["RoleCode"]) + "' ");
strSql.Append(" and lower(d.RoleType)='" + Common.ConvertEx.ToString(ht["RoleType"]) + "' ");
}
if (ht.ContainsKey("Area_Code") && Common.ConvertEx.ToString(ht["Area_Code"]).Length > 0)
{
strSql.Append(" and a.Area_Code LIKE '%" + Common.ConvertEx.ToString(ht["Area_Code"]) + "%'");
}
if (ht.ContainsKey("InCompanyType") && Common.ConvertEx.ToString(ht["InCompanyType"]).Length > 0)
{
strSql.Append(" and a.CompanyType in(" + Common.ConvertEx.ToString(ht["InCompanyType"]) + ")");
}
if (ht.ContainsKey("CompanyName") && Common.ConvertEx.ToString(ht["CompanyName"]).Length > 0)
{
strSql.Append(" and a.CompanyName LIKE '%" + Common.ConvertEx.ToString(ht["CompanyName"]) + "%'");
}
if (ht.ContainsKey("CompanyType") && Common.ConvertEx.ToString(ht["CompanyType"]).Length > 0)
{
strSql.Append(" and a.CompanyType =" + Common.ConvertEx.ToString(ht["CompanyType"]) + "");
}
if (ht.ContainsKey("UserName") && Common.ConvertEx.ToString(ht["UserName"]).Length > 0)
{
strSql.Append(" and b.UserName LIKE '%" + Common.ConvertEx.ToString(ht["UserName"]) + "%'");
}
if (ht.ContainsKey("CompanyID") && Common.ConvertEx.ToString(ht["CompanyID"]).Length > 0)
{
strSql.Append(" and a.CompanyID=" + Common.ConvertEx.ToString(ht["CompanyID"]) + " ");
}
if (ht.ContainsKey("NotINSingleProjectID") && Common.ConvertEx.ToString(ht["NotINSingleProjectID"]).Length > 0)
{
strSql.Append(" and NOT EXISTS (select DISTINCT f.CompanyID from T_SingleProjectCompany f ");
strSql.Append(" where f.SingleProjectID=" + Common.ConvertEx.ToString(ht["NotINSingleProjectID"]) + " AND a.CompanyID=f.CompanyID)");
}
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0]);
}
/// <summary>
/// 获得数据列表 分页
/// </summary>
/// <returns></returns>
public DataTable GetListPaging(string strWhere, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select *,(select UserName from T_UsersInfo where UserID=Signature_UserID)as SignatureUserName, ");
strSql.Append("(select w.RoleName from T_Role w where w.RoleCode=T_FileList_SignatureLog.Signature_UserRoleCode)as RoleName ");
strSql.Append(" FROM T_FileList_SignatureLog ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0]);
}
/// <summary>
/// 获得数据列表 分页
/// </summary>
/// <returns></returns>
public DataTable GetList(string strWhere, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select a.*,b.CompanyName,c.SystemInfoName,d.area_name,e.SystemInfoName as RoleTypeName from T_Role a ");
strSql.Append("left join T_Company b on A.CompanyID=B.CompanyID ");
strSql.Append("left join T_SystemInfo c on B.CompanyType=C.SystemInfoID ");
strSql.Append("left join T_Area D on b.Area_Code=d.area_code ");
strSql.Append("left join T_SystemInfo e on lower(e.CurrentType)='roletype' and lower(a.RoleType)=lower(E.SystemInfoCode) ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0]);
}
/// <summary>
/// 取流程下的工程
/// </summary>
/// <param name="WorkFlowID"></param>
/// <returns></returns>
public DataSet GetProjectListByWorkFlow(string WorkFlowID, bool IsCompany, string roleid, string UserID,
string sqlWhere, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun, bool isChargeUser = false)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT a.*,b.SystemInfoName as ProjectTypeName,SubmitStatus,c.WorkFlowDefineID,c.RecvDateTime FROM T_SingleProject a,");
strSql.Append("T_SystemInfo b,T_WorkFlowDefine c WHERE b.CurrentType='Archive_Form' and SystemInfoCode=a.projecttype ");
strSql.Append("AND a.SingleProjectID=c.SingleProjectID AND WorkFlowID=" + WorkFlowID + " and dostatus=1 and a.Status!=119 ");
if (sqlWhere != "")
{
strSql.Append(sqlWhere);
}
if (IsCompany == true)
{
if (roleid != "")
{
strSql.Append(" and a.SingleProjectID in (select distinct SingleProjectID from t_singleprojectuser where roleid=" + roleid + ")");
}
if (UserID != "")
{
strSql.Append(" and a.SingleProjectID in (select distinct SingleProjectID from t_singleprojectuser where UserID=" + UserID + ")");
}
}
else
{
if (UserID != "") //档案馆，自已的+未受理的
//strSql.Append(" AND (RecvUserID=" + UserID + " OR IsNULL(RecvUserID,0)=0)");
//jdk 9.29
//分配给我的+我受理的
//strSql.Append(" AND (RecvUserID=" + UserID + " OR ChargeUserID=" + UserID + ")");
{
if (isChargeUser)
{
strSql.Append(" AND ChargeUserID=" + UserID + " ");
}
else
{
strSql.Append(" AND (RecvUserID=" + UserID + " OR IsNULL(RecvUserID,0)=0 )");
}
}
}
//strSql.Append(" order by a.SingleProjectID DESC");
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun));
}
/// <summary>
/// 根据公司获取用户相关信息
/// </summary>
/// <param name="strWhere"></param>
/// <param name="PageSize"></param>
/// <param name="curPage"></param>
/// <param name="PRIMARYKey"></param>
/// <param name="OrderByName"></param>
/// <param name="recCoun"></param>
/// <returns></returns>
public DataTable GetListByCompany(string strWhere, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select UserID,a.CompanyID,a.RoleID,UserName,CardID,LoginName,Passwd,Sex,a.Mobile,a.Tel,");
strSql.Append("a.Fax,a.EMail,QQ,MSN,a.Createdate,Createdby,IsValid,UserType,IsLeader,a.OwnerFileTmp,");
strSql.Append("IsSuperAdmin,LastLoginTime,LastLoginIP,TrainCount,RegDate,CompanyName,Position,c.RoleName,");
strSql.Append("(SELECT f0.SystemInfoName from T_SystemInfo f0 where f0.CurrentType='CompanyType' AND f0.SystemInfoID=b.CompanyType)as CompanyTypeName ");
strSql.Append("FROM T_UsersInfo a,T_Company b,T_Role c WHERE a.CompanyID=b.CompanyID and a.RoleID=c.RoleID ");
if (strWhere.Trim() != "")
{
strSql.Append(" AND " + strWhere);
}
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0]);
}
/// <summary>
/// 获得数据列表 分页
/// </summary>
/// <returns></returns>
public DataTable GetList(string strWhere, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select A.*,B.CompanyName,C.SystemInfoName,d.Area_Name,");
strSql.Append("(select top 1 WorkFlowName FROM T_WorkFlow f where f.WorkFlowID=a.PID)as PWorkFlowName ");
strSql.Append("FROM T_WorkFlow A ");
strSql.Append("LEFT JOIN T_Company B on A.CompanyID=B.CompanyID ");
strSql.Append("LEFT JOIN T_SystemInfo C on B.CompanyType=C.SystemInfoID ");
strSql.Append("LEFT JOIN T_Area D on b.Area_Code=d.area_code ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0]);
}
/// <summary>
/// 获得数据列表 分页
/// </summary>
/// <returns></returns>
public DataTable GetListPaging(Hashtable ht, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
if (ht.ContainsKey("ReadyCheckBookTypeFlag") && Common.ConvertEx.ToInt(ht["ReadyCheckBookTypeFlag"]) == 1) //已发放
{
strSql.Append("SELECT f1.SingleProjectID keySingleProjectID, f0.*,len(ISNULL(f0.ReadyCheckBookCode,''))as lenReadyCheckBookCode,f1.gcmc,f1.gcbm,f1.ProjectType,");
strSql.Append("(SELECT ff.UserName FROM T_UsersInfo ff where ff.UserID=f0.CreateUserID)as CreateUserName ");
strSql.Append("from T_SingleProject f1 left JOIN T_ReadyCheckBook f0 ON f0.SingleProjectID=f1.SingleProjectID where 1=1 ");
}
if (ht.ContainsKey("ReadyCheckBookTypeFlag") && Common.ConvertEx.ToInt(ht["ReadyCheckBookTypeFlag"]) == 0) //未发放
{
strSql.Append("SELECT f1.SingleProjectID keySingleProjectID,f1.gcmc,f1.gcbm,f1.ProjectType,");
strSql.Append("'0'as lenReadyCheckBookCode,''as CreateUserName,''as CreateTime,0 as ReadyCheckBookID,''as ReadyCheckBookCode ");
strSql.Append("from T_SingleProject f1 where 1=1 ");
}
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString() + GetListPagingForQueryWhere(ht), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0]);
}
/// <summary>
/// 获得数据列表 分页
/// </summary>
/// <returns></returns>
public DataTable GetList(string strWhere, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select recid,PID,archive_form_no,DefaultCompanyType,bh,title,OrderIndex,pym,MustSubmitFlag,IsVisible,AlonePack,IsFolder,JS,SG,SJ,JL,FileType, ");
strSql.Append("(select t.systeminfoName from t_systeminfo t where t.CurrentType='CompanyType' and t.systeminfoid=DefaultCompanyType)as DefaultCompanyTypeName, ");
strSql.Append("(select t.systeminfoName from t_systeminfo t where t.CurrentType='FileFrom' and t.systeminfoid=FileFrom)as FileFromName, ");
strSql.Append("(select t.systeminfoName from t_systeminfo t where t.CurrentType='Archive_Form' and t.SystemInfoCode=archive_form_no)as Archive_FormName, ");
strSql.Append("(select COUNT(1) from T_FileList_SignatureTmp tt where tt.FileListID=T_FileListTmp.recid)as SignatureTypeCount ");
strSql.Append(" FROM T_FileListTmp ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0]);
}
/// <summary>
/// 取流程下的工程
/// </summary>
/// <param name="WorkFlowID"></param>
/// <returns></returns>
public DataSet GetLHSignatureListPaging(string WorkFlowID, bool IsCompany, string roleid, string UserID,
string sqlWhere, int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT a.*,b.SystemInfoName as ProjectTypeName,SubmitStatus,c.WorkFlowDefineID,c.RecvDateTime,");
strSql.Append("(SELECT f.AttachPath from T_FileAttach f WHERE LOWER(f.Flag)='company_registration' and f.PriKeyValue=a.SingleProjectID and LOWER(f.AttachCode)='ghxkz')as ghxkz_AttachPath,");
strSql.Append("(SELECT f.AttachPath from T_FileAttach f WHERE LOWER(f.Flag)='company_registration' and f.PriKeyValue=a.SingleProjectID and LOWER(f.AttachCode)='sgxkz')as sgxkz_AttachPath ");
strSql.Append("FROM T_SingleProject a,T_SystemInfo b,T_WorkFlowDefine c ");
strSql.Append("WHERE b.CurrentType='Archive_Form' and SystemInfoCode=a.projecttype and a.SingleProjectID=c.SingleProjectID ");
strSql.Append("AND WorkFlowID=" + WorkFlowID + " and dostatus=1 and a.Status!=119 ");
if (sqlWhere != "")
{
strSql.Append(sqlWhere);
}
if (IsCompany == true)
{
if (roleid != "")
{
strSql.Append(" and a.SingleProjectID in (select SingleProjectID from t_singleprojectuser where roleid=" + roleid + ")");
}
if (UserID != "")
{
strSql.Append(" and a.SingleProjectID in (select SingleProjectID from t_singleprojectuser where UserID=" + UserID + ")");
}
}
else
{
if (UserID != "") //档案馆，自已的+未受理的
//strSql.Append(" AND (RecvUserID=" + UserID + " OR IsNULL(RecvUserID,0)=0)");
//jdk 9.29
//分配给我的+我受理的
//strSql.Append(" AND (RecvUserID=" + UserID + " OR ChargeUserID=" + UserID + ")");
{
strSql.Append(" AND (RecvUserID=" + UserID + " OR IsNULL(RecvUserID,0)=0 OR ChargeUserID=" + UserID + ")");
}
}
//strSql.Append(" order by a.SingleProjectID DESC");
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun));
}
/// <summary>
/// 获得数据列表 分页
/// </summary>
/// <returns></returns>
public DataTable GetMyChildCompany(string Gcmc, string CompanyType, string CompanyName, string ParentCompanyID, string MyCompanyID, string Area_Code,
int PageSize, int curPage, string PRIMARYKey, string OrderByName, out int recCoun)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT DISTINCT A.*,C.*,D.UserName,D.LoginName,D.UserID,D.RoleID ");
string sqlGcmc = string.Empty;
if (Gcmc.Length > 0)
{
sqlGcmc = "And t.gcmc like '%" + Gcmc + "%' ";
}
strSql.Append(" FROM T_Company A,T_SingleProjectCompany B,T_SystemInfo C,T_UsersInfo D ");
strSql.Append(" WHERE A.CompanyType=C.SystemInfoID AND A.CompanyID=B.CompanyID AND A.CompanyID=D.CompanyID ");
if (Area_Code != "")
{
strSql.Append(" AND A.Area_Code like '" + Area_Code + "%'");
}
if (MyCompanyID != "")
{
strSql.Append(" AND A.CompanyID=" + MyCompanyID);
}
if (CompanyType != "0" && CompanyType != "")
{
strSql.Append(" AND A.CompanyType=" + CompanyType);
}
if (!String.IsNullOrEmpty(CompanyName))
{
strSql.Append(" AND A.CompanyName LIKE '%" + CompanyName + "%'");
}
if (MyCompanyID == "" && ParentCompanyID != "")
{
strSql.Append(" AND B.SingleProjectID IN (SELECT DISTINCT SingleProjectID FROM T_Company A,T_Construction_Project B,T_SingleProject C WHERE A.CompanyID=B.CompanyID AND B.ConstructionProjectID=C.ConstructionProjectID ");
strSql.Append(" AND A.CompanyID=" + ParentCompanyID);
strSql.Append(")");
}
return(PageCtrl.QueryForDataTableSqlServer(strSql.ToString(), PageSize, curPage, PRIMARYKey, OrderByName, out recCoun).Tables[0]);
}
