/// <summary>
/// get group object of Company
/// </summary>
/// <returns></returns>
public Model.Groups GetCompanyGroup()
{
string strSql = "SELECT * FROM dbo.Groups WHERE CompanyID IS NOT NULL AND OrganizationType='Company'";
DataTable dtCompany = DbHelperSQL.ExecuteDataTable(strSql);
LPWeb.Model.Groups groupCompany = null;
if (null != dtCompany && dtCompany.Rows.Count > 0)
{
groupCompany = new Model.Groups();
if (dtCompany.Rows[0]["GroupId"].ToString() != "")
{
groupCompany.GroupId = int.Parse(dtCompany.Rows[0]["GroupId"].ToString());
}
groupCompany.GroupName = dtCompany.Rows[0]["GroupId"].ToString();
if (dtCompany.Rows[0]["Enabled"].ToString() != "")
{
if ((dtCompany.Rows[0]["Enabled"].ToString() == "1") || (dtCompany.Rows[0]["Enabled"].ToString().ToLower() == "true"))
{
groupCompany.Enabled = true;
}
else
{
groupCompany.Enabled = false;
}
}
groupCompany.OrganizationType = dtCompany.Rows[0]["OrganizationType"].ToString();
}
return(groupCompany);
}
/// <summary>
/// Get proformedby user of loan activity
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public DataTable GetProformedBy(string strWhere)
{
string strSql = "SELECT DISTINCT LA.FileId, LA.UserId, U.FirstName, U.LastName, ISNULL(U.FirstName, '') + ' ' + ISNULL(U.LastName, '') AS PerformedBy FROM LoanActivities LA INNER JOIN Users U ON LA.UserId=U.UserId WHERE 1=1 {0} ORDER BY U.FirstName";
strSql = string.Format(strSql, strWhere);
return(DbHelperSQL.ExecuteDataTable(strSql));
}
/// <summary>
/// get related contacts
/// neo 2011-03-15
/// </summary>
/// <param name="ContactId"></param>
/// <param name="iStartIndex"></param>
/// <param name="iEndIndex"></param>
/// <returns></returns>
public DataTable GetRelatedContacts(int ContactId, int iStartIndex, int iEndIndex)
{
string sSql = "select 'To' as Direction, a.ToContactId as RelContactID, a.RelTypeId, c.RelToName as Relationship, b.LastName +', '+ b.FirstName + case when b.MiddleName is null then '' when b.MiddleName='' then '' else ' '+ b.MiddleName end as ContactName "
+ "from Contact_Relationship as a "
+ "inner join Contacts as b on a.ToContactId = b.ContactId "
+ "inner join RelationshipRoles as c on a.RelTypeId = c.RelTypeId "
+ "where a.FromContactId = " + ContactId + " "
+ "union "
+ "select 'From' as Direction, a.FromContactId as RelContactID, a.RelTypeId, c.RelFromName as Relationship, b.LastName +', '+ b.FirstName + case when b.MiddleName is null then '' when b.MiddleName='' then '' else ' '+ b.MiddleName end as ContactName "
+ "from Contact_Relationship as a "
+ "inner join Contacts as b on a.FromContactId = b.ContactId "
+ "inner join RelationshipRoles as c on a.RelTypeId = c.RelTypeId "
+ "where a.ToContactId = " + ContactId;
SqlCommand SqlCmd = new SqlCommand("lpsp_ExecSqlByPager");
SqlCmd.CommandType = CommandType.StoredProcedure;
DbHelperSQL.AddSqlParameter(SqlCmd, "@OrderByField", SqlDbType.NVarChar, "Direction, RelContactID");
DbHelperSQL.AddSqlParameter(SqlCmd, "@AscOrDesc", SqlDbType.NVarChar, "asc");
DbHelperSQL.AddSqlParameter(SqlCmd, "@Fields", SqlDbType.NVarChar, "*");
DbHelperSQL.AddSqlParameter(SqlCmd, "@DbTable", SqlDbType.NVarChar, "(" + sSql + ") as t");
DbHelperSQL.AddSqlParameter(SqlCmd, "@Where", SqlDbType.NVarChar, "");
DbHelperSQL.AddSqlParameter(SqlCmd, "@StartIndex", SqlDbType.Int, iStartIndex);
DbHelperSQL.AddSqlParameter(SqlCmd, "@EndIndex", SqlDbType.Int, iEndIndex);
return(DbHelperSQL.ExecuteDataTable(SqlCmd));
}
/// <summary>
/// get email template
/// neo 2010-11-17
/// </summary>
/// <param name="sWhere"></param>
/// <returns></returns>
public DataTable GetEmailTemplateBase(string sWhere)
{
string sSql = "select * from Template_Email where 1=1 " + sWhere + " order by [Name]";
//sSql += " ORDER BY Name";
return(DbHelperSQL.ExecuteDataTable(sSql));
}
public DataTable GetCompanyLoanPointFieldsInfo()
{
string sSQL = @"select cp.*,pd.Label from dbo.CompanyLoanPointFields cp
left join dbo.PointFieldDesc pd on pd.PointFieldId=cp.PointFieldId";
return(DbHelperSQL.ExecuteDataTable(sSQL));
}
protected void bind()
{
DataTable dt = DbHelperSQL.ExecuteDataTable("select top 20 ro.SerUserID,count(ro.SerUserID)as counts,su.RealName ,su.Phone from Reward_Order ro join ServerUser su on ro.SerUserID=su.SerUserID and ro.SerUserID is not null and OrderState=3 group by ro.SerUserID ,su.RealName,su.Phone order by counts desc", CommandType.Text);
binding.DataSource = dt;
binding.DataBind();
}
protected void btnExport_Click(object sender, EventArgs e)
{
string sSql2 = "select * from (" + this.sDbTable + ") as m where 1=1" + this.sWhere;
this.ReportData = DbHelperSQL.ExecuteDataTable(sSql2);
// Excel Columns
NameValueCollection ExcelCollection = new NameValueCollection();
ExcelCollection.Add("Branch", "BranchName");
ExcelCollection.Add("Name", "FullName");
ExcelCollection.Add("Progress", "Progress");
ExcelCollection.Add("Running Total", "RunningTotal");
ExcelCollection.Add("Low Total", "LowRange");
ExcelCollection.Add("Medium Total", "MediumRange");
ExcelCollection.Add("High Range", "HighRange");
// sheet name
string sSheetName = "User Production Goals";
// 显示给用户的Xls文件名
DateTime dt = DateTime.Now;
string sClientXlsFileName = "User_Production_Goals_Report-" + dt.ToString("MM_dd_yy") + ".xls";
// export and download
XlsExporter.DownloadXls(this, ReportData, sClientXlsFileName, sSheetName);
}
public DataTable GetLoanNoteTypeInfoForAdd(int iContactID)
{
string sSql = @"select (case [Status] when 'Prospect' then 'Opportunity' when 'Processing' then 'Active Loan' else [Status] end) as StatusName, (case [Status] when 'Prospect' then 'Opportunity' when 'Processing' then 'Active Loan' else [Status] + ' Loan' end + ' - ' + LienPosition + ' - ' + PropertyAddr + ' Note') as NoteTypeName,FileId as ContactId from dbo.Loans where FileId in (
select lc.FileId from dbo.[lpvw_GetLoanContactwRoles] lc inner join Loans l on lc.FileId=l.FileId where ContactId=" + iContactID + " and (RoleName='Borrower' or RoleName='CoBorrower')) and ([Status]='Prospect' or [Status]='Processing')";
return(DbHelperSQL.ExecuteDataTable(sSql));
}
/// <summary>
/// 获取具有branc manager 权限的 User列表
/// </summary>
/// <param name="sWhere"></param>
/// <returns></returns>
public DataTable GetBranchManagerSeletion()
{
string sSql = @"SELECT Users.UserId, Users.Username,Users.LastName+', '+ Users.FirstName as FullName FROM
Users WHERE RoleID='2' order by LastName";
return(DbHelperSQL.ExecuteDataTable(sSql));
}
/// <summary>
/// Get point folder id by region id/division id/branch id
/// </summary>
/// <param name="sType"></param>
/// <param name="iID"></param>
/// <returns></returns>
private DataTable GetPointFolderIDByRegion(string sType, int iID)
{
DataTable dtRe = new DataTable();
string sSQL = "SELECT FolderId FROM PointFolders WHERE 1>0";// AND [Enabled]='true' ";
try
{
switch (sType.ToLower())
{
case "region":
sSQL += " AND BranchId IN(SELECT BranchId FROM Branches WHERE RegionID = " + iID.ToString() + ")";
break;
case "division":
sSQL += " AND BranchId IN(SELECT BranchId FROM Branches WHERE DivisionID = " + iID.ToString() + ")";
break;
case "branch":
sSQL += " AND BranchId =" + iID.ToString();
break;
case "company":
sSQL += "";
break;
}
sSQL += " ORDER BY FolderId";
dtRe = DbHelperSQL.ExecuteDataTable(sSQL);
}
catch (Exception ex)
{
throw ex;
}
return(dtRe);
}
public DataTable GetEmployment(int emplid)
{
string querySql = "SELECT T1.CompanyName AS EmploymentName,"
+ " T1.SelfEmployed,"
+ " T1.Position,"
+ " T1.StartMonth,"
+ " T1.StartYear,"
+ " T1.EndMonth,"
+ " T1.EndYear,"
+ " T2.Salary,"
+ " T1.Phone,"
+ " T1.YearsOnWork,"
+ " T1.[Address] AS [Address],"
+ " T1.City AS City,"
+ " T1.[State] AS [State],"
+ " T1.Zip AS Zip,"
+ " T1.ContactBranchId,"
+ " T1.BusinessType,"
+ " T1.VerifyYourTaxes"
+ " FROM ProspectEmployment AS T1"
+ " LEFT JOIN ProspectIncome T2 ON T1.EmplId = T2.EmplId"
+ " WHERE T1.Emplid = {0}";
return(DbHelperSQL.ExecuteDataTable(string.Format(querySql, emplid)));
}
public DataTable GetUserLoansViewPointFieldsHeadingInfo(int iUserID)
{
string sSQL = @"select cpf.Heading,up.PointFieldId from UserLoansViewPointFields up
left join CompanyLoanPointFields cpf on cpf.PointFieldId=up.PointFieldId where up.UserId=" + iUserID + "";
return(DbHelperSQL.ExecuteDataTable(sSQL));
}
/// <summary>
/// get email log list for prospect
/// neo 2011-04-28
/// </summary>
/// <param name="sDbTable"></param>
/// <param name="iStartIndex"></param>
/// <param name="iEndIndex"></param>
/// <param name="strWhere"></param>
/// <param name="orderName"></param>
/// <param name="orderType"></param>
/// <returns></returns>
public DataTable GetProspectEmailLogListBase(string sDbTable, int iStartIndex, int iEndIndex, string strWhere, string orderName, int orderType)
{
string sAscOrDesc = string.Empty;
if (orderType == 0)
{
sAscOrDesc = "asc";
}
else
{
sAscOrDesc = "desc";
}
SqlCommand SqlCmd = new SqlCommand("lpsp_ExecSqlByPager");
SqlCmd.CommandType = CommandType.StoredProcedure;
DbHelperSQL.AddSqlParameter(SqlCmd, "@OrderByField", SqlDbType.NVarChar, orderName);
DbHelperSQL.AddSqlParameter(SqlCmd, "@AscOrDesc", SqlDbType.VarChar, sAscOrDesc);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Fields", SqlDbType.NVarChar, "*");
DbHelperSQL.AddSqlParameter(SqlCmd, "@DbTable", SqlDbType.NVarChar, sDbTable);
DbHelperSQL.AddSqlParameter(SqlCmd, "@Where", SqlDbType.NVarChar, strWhere);
DbHelperSQL.AddSqlParameter(SqlCmd, "@StartIndex", SqlDbType.Int, iStartIndex);
DbHelperSQL.AddSqlParameter(SqlCmd, "@EndIndex", SqlDbType.Int, iEndIndex);
return(DbHelperSQL.ExecuteDataTable(SqlCmd));
}
/// <summary>
/// get loan officer list
/// neo 2011-03-13
/// ProspectDetailPopup.aspx, modified by peter on 2011-03-20
/// </summary>
/// <param name="iUserID"></param>
/// <returns></returns>
private DataTable GetLoanOfficerList(int iUserID)
{
// get branch count
//string sSql0 = "select count(1) from lpfn_GetUserBranches_UserList(" + iUserID + ")";
//int iCount = Convert.ToInt32(DbHelperSQL.ExecuteScalar(sSql0));
// all loan officer, modified by peter
string sSql = "select a.UserID, a.FirstName +', '+ a.LastName as FullName "
+ "from Users as a "
+ "inner join GroupUsers as b on a.UserId = b.UserID "
+ "inner join Groups as c on b.GroupID = c.GroupId "
+ "where c.Enabled = 1 and a.UserEnabled=1 and a.RoleId = 3"
+ " and (c.BranchId in (select * from lpfn_GetUserBranches_UserList(" + iUserID + ")))";
sSql += "GROUP BY a.UserID, a.FirstName, a.LastName";
// if belong to branch, get loan officer under same branch
//if (iCount > 0)
//{
// sSql += " and (c.BranchId in (select * from lpfn_GetUserBranches_UserList(" + iUserID + ")))";
//}
DataTable LoanOfficerList = DbHelperSQL.ExecuteDataTable(sSql);
DataRow NewLORow = LoanOfficerList.NewRow();
NewLORow["UserID"] = 0;
NewLORow["FullName"] = "-- select --";
LoanOfficerList.Rows.InsertAt(NewLORow, 0);
return LoanOfficerList;
}
/// <summary>
/// 获取recipient role list
/// neo 2010-12-08
/// </summary>
/// <returns></returns>
public DataTable GetRecipientRoleListBase()
{
string sSql = "select ContactRoleId as RoleID, 'Contact' as RecipientType, Name as RecipientRole from ContactRoles "
+ "union "
+ "select RoleID, 'User' as RecipientType, Name as RecipientRole from Roles where RoleId not in (1,2)";
return(DbHelperSQL.ExecuteDataTable(sSql));
}
public DataTable GetUserLoansViewPointFieldsInfo(int iUserID, int iFileID)
{
string sSQL = @"select cpf.Heading,lf.CurrentValue from UserLoansViewPointFields up
left join LoanPointFields lf on lf.PointFieldId=up.PointFieldId and FileId=" + iFileID;
sSQL += " left join CompanyLoanPointFields cpf on cpf.PointFieldId=up.PointFieldId where up.UserId=" + iUserID + "";
return(DbHelperSQL.ExecuteDataTable(sSQL));
}
/// <summary>
/// get loan officer user info
/// neo 2012-12-29
/// </summary>
/// <param name="iFileId"></param>
/// <returns></returns>
public DataTable GetLoanOfficerInfo(int iFileId)
{
string sSql = @"select u.* from LoanTeam lt inner join Users u
on lt.UserId=u.UserId inner join Roles r
on lt.RoleId=r.RoleId and r.Name='Loan Officer' and lt.FileId =" + iFileId;
return(DbHelperSQL.ExecuteDataTable(sSql));
}
/// <summary>
/// get rule with alert email template info
/// </summary>
/// <param name="iRuleID"></param>
/// <returns></returns>
public DataTable GetRuleWithAlertEmailTpltInfo(int iRuleID)
{
string sSql = @"SELECT a.*, b.Name AS AlertEmailTpltName, b.Enabled AS EmailTpltEnabled,
b.[Desc] AS EmailTpltDesc, b.FromUserRoles, b.FromEmailAddress, b.Content, b.Subject
FROM Template_Rules a LEFT JOIN Template_Email b ON a.AlertEmailTemplId=b.TemplEmailId
WHERE a.RuleId=" + iRuleID;
return(DbHelperSQL.ExecuteDataTable(sSql));
}
public DataTable GetDisStartByInfo()
{
DataTable dt;
string sSql = @"SELECT Distinct dbo.Users.UserId, dbo.lpfn_GetUserName(dbo.Users.UserId) AS StartedByUser
FROM dbo.LoanMarketing LEFT OUTER JOIN
dbo.Users ON dbo.LoanMarketing.StartedBy = dbo.Users.UserId order by StartedByUser";
dt = DbHelperSQL.ExecuteDataTable(sSql);
return dt;
}
//
public DataTable GetAllList(string OrgCode, int startIndex, int pageSize, string order)
{
try
{
string strSql = string.Format("select a.Id,b.OrgShortName as OrganizationId,a.UserName,a.UserTel,a.ConsContent,a.Editor,a.CreateDate FROM T_Contacts as a left join T_Organization as b on a.FK_OrganizationId = b.OrgCode where a.FK_OrganizationId='{0}' {1}", OrgCode, order);
return(DbHelperSQL.ExecuteDataTable(strSql, startIndex * pageSize, pageSize, "T_Contacts"));
}
catch { throw; }
}
//
public DataTable GetAllList(string sqlWhere, int startIndex, int pageSize, string order)
{
try
{
string strSql = string.Format("select Id,PlacardTitle,PlacardContent,Publisher,ReleaseTime,NumClicks,DepName from T_Placard where {0} {1}", sqlWhere, order);
return(DbHelperSQL.ExecuteDataTable(strSql, startIndex * pageSize, pageSize, "T_Placard"));
}
catch { throw; }
}
public DataTable SearchSingleContact(string strWhere)
{
string sSql = "SELECT * FROM ContactBranches";
if (!string.IsNullOrEmpty(strWhere))
{
sSql += " WHERE " + strWhere;
}
return(DbHelperSQL.ExecuteDataTable(sSql));
}
public bool ExistIncome(int emplid)
{
DataTable dt = DbHelperSQL.ExecuteDataTable(string.Format(existSql, emplid));
if (dt != null && dt.Rows.Count == 1)
{
return(Convert.ToInt32(dt.Rows[0][0].ToString()) == 1);
}
return(false);
}
public DataTable GetBranches(string con)
{
string sql =
"SELECT Branches.Name, GroupUsers.UserID, Groups.DivisionID, Groups.RegionID, Groups.BranchID " +
" FROM GroupUsers INNER JOIN " +
"Groups ON GroupUsers.GroupID = Groups.GroupId INNER JOIN " +
"Branches ON Groups.BranchID = Branches.BranchId";
return(DbHelperSQL.ExecuteDataTable(sql + con));
}
protected void bind()
{
DataTable dtyoule = DbHelperSQL.ExecuteDataTable("select SerUserID from ServerUser where SerUserID not in (select SerUserID from ServerUser_Education)", CommandType.Text);
DataTable dtwanzheng = DbHelperSQL.ExecuteDataTable("select * from ServerUser where SerUserID in (select SerUserID from ServerUser_Work where SerUserID in (select SerUserID from ServerUser_Post))", CommandType.Text);
DataTable dtchengjiao = DbHelperSQL.ExecuteDataTable("select * from ServerUser where SerUserID in(select SerUserID from Reward_Order)", CommandType.Text);
youke = dtyoule.Rows.Count.ToString();
wanzheng = dtwanzheng.Rows.Count.ToString();
chengjiao = dtchengjiao.Rows.Count.ToString();
}
/// <summary>
///
/// </summary>
/// <param name="iUserID"></param>
/// <returns></returns>
public DataTable GetUserPointFieldInfo(int iUserID)
{
string sSql = @"select a.PointFieldId, a.UserId, b.Heading, c.Label, c.DataType,
case when c.DataType=2 then 'Numeric' when c.DataType=3 then 'Yes/No' when c.DataType=4 then 'Date' else 'String' end as DataTypeName
from UserLoansViewPointFields as a
left join CompanyLoanPointFields as b on a.PointFieldId = b.PointFieldId
left join PointFieldDesc as c on a.PointFieldId = c.PointFieldId
where a.UserId=" + iUserID;
return(DbHelperSQL.ExecuteDataTable(sSql));
}
/// <summary>
/// 得到所有的Referra; 数据源
/// Alex 2011-04-07
/// </summary>
/// <returns></returns>
public DataTable GetReferralContactInfo()
{
DataTable dt = new DataTable();
string sSql = @"SELECT c1.ContactId, ISNULL(c1.Lastname,'')+ (CASE ISNULL(c1.Lastname,'') when '' then ' ' else ', ' end) +ISNULL(c1.Firstname,'') + ' ' + ISNULL(c1.Middlename,'') as Referral
FROM dbo.Prospect left outer join dbo.Contacts c1 on c1.ContactId = dbo.Prospect.Referral
WHERE (ISNULL(dbo.Prospect.Referral, '') <> '') order by Referral ";
dt = DbHelperSQL.ExecuteDataTable(sSql);
return(dt);
}
/// <summary>
/// get items of branch filter for "All Loans" in dashboard home
/// neo 2010-10-24
/// </summary>
/// <param name="iManagerID"></param>
/// <param name="iRegionID"></param>
/// <param name="iDivisionID"></param>
/// <returns></returns>
public DataTable GetBranchList_AllLoansBase(int iManagerID, int iRegionID, int iDivisionID)
{
string sWhere1 = string.Empty;
string sWhere2 = string.Empty;
if (iRegionID != 0)
{
sWhere1 += " and b.RegionID=" + iRegionID + " ";
sWhere2 += " and RegionID=" + iRegionID + " ";
}
if (iDivisionID != 0)
{
sWhere1 += " and b.DivisionID=" + iDivisionID + " ";
sWhere2 += " and DivisionID=" + iDivisionID + " ";
}
string sSql = " SELECT [Name] ,[BranchId] FROM( "
+ " select Name, BranchId from Branches where Enabled=1 and exists( "
+ " select * from Groups as a inner join GroupUsers as b on a.GroupId = b.GroupID where OrganizationType='Company' "
+ " and b.UserID = " + iManagerID + ")" + sWhere2
+ "union "
+ "select b.Name, b.BranchId from BranchManagers as a left outer join Branches as b on a.BranchId = b.BranchId "
+ "where b.Enabled=1 and BranchMgrId=" + iManagerID + sWhere1
+ "union "
+ "select b.Name,b.BranchId from GroupUsers as a left outer join Branches as b on a.GroupID = b.GroupID "
+ "where b.Enabled=1 and a.UserID=" + iManagerID + sWhere1
+ "union "
+ "select Name, BranchId from Branches where ((RegionID in ( "
+ " select b.RegionId from RegionExecutives as a left outer join Regions as b on a.RegionId = b.RegionId "
+ " where b.Enabled=1 and ExecutiveId=" + iManagerID + " "
+ " union "
+ " select b.RegionId from GroupUsers as a left outer join Regions as b on a.GroupID = b.GroupID "
+ " where b.Enabled=1 and a.UserID=" + iManagerID + " "
+ ")) "
+ "or (DivisionID in ( "
+ " select b.DivisionId from DivisionExecutives as a left outer join Divisions as b on a.DivisionId = b.DivisionId "
+ " where b.Enabled=1 and ExecutiveId=" + iManagerID + " "
+ " union "
+ " select b.DivisionId from GroupUsers as a left outer join Divisions as b on a.GroupID = b.GroupID "
+ " where b.Enabled=1 and a.UserID=" + iManagerID + " "
+ " union "
+ " select DivisionId from Divisions where RegionID in ( "
+ " select b.RegionId from RegionExecutives as a left outer join Regions as b on a.RegionId = b.RegionId "
+ " where b.Enabled=1 and ExecutiveId=" + iManagerID + " "
+ " union "
+ " select b.RegionId from GroupUsers as a left outer join Regions as b on a.GroupID = b.GroupID "
+ " where b.Enabled=1 and a.UserID=" + iManagerID + " "
+ " ) "
+ ")))" + sWhere2
+ " ) tmptb order by Name ";
return(DbHelperSQL.ExecuteDataTable(sSql));
}
/// <summary>
/// get stage list of workflow tempalte (Template_Wfl_Stages)
/// neo 2011-02-09
/// </summary>
/// <param name="iWorkflowTemplateID"></param>
/// <returns></returns>
public DataTable GetWflStageListBase(int iWorkflowTemplateID)
{
string sSql = "select ROW_NUMBER() OVER(ORDER BY a.SequenceNumber) AS RowIndex, *, (select COUNT(1) from Template_Wfl_Tasks where WflStageId=a.WflStageId) as TaskCount "
+ ", CASE ISNULL(a.CalculationMethod,0) WHEN 0 THEN c.CalculationMethod ELSE a.CalculationMethod END AS CalculationMethodCode"
+ " from Template_Wfl_Stages as a "
+ " inner join Template_Stages as b on a.TemplStageId = b.TemplStageId "
+ " inner join Template_Workflow as c on a.WflTemplId = c.WflTemplId"
+ " where a.WflTemplId=" + iWorkflowTemplateID;
return(DbHelperSQL.ExecuteDataTable(sSql));
}
protected void bind()
{
DataTable dtershi = DbHelperSQL.ExecuteDataTable("select * from Person where PerID not in (select PerID from Person_Education) and PerID not in (select PerID from Person_ExpectWork)and PerID not in (select PerID from Person_Skill) and PerID not in (select PerID from Person_Project) ", CommandType.Text);
DataTable dtwushi = DbHelperSQL.ExecuteDataTable("select * from Person where PerID in (select PerID from Person_Education) ", CommandType.Text);
DataTable dtbashi = DbHelperSQL.ExecuteDataTable("select * from Person where PerID in (select PerID from Person_Work where PerID in (select PerID from Person_Education) )", CommandType.Text);
DataTable dtyibai = DbHelperSQL.ExecuteDataTable("select PerID from Person where PerID in (select PerID from Person_Skill where PerID in (select PerID from Person_Education) and PerID in (select PerID from Person_Work) ) ", CommandType.Text);
ershi = dtershi.Rows.Count.ToString();
wushi = dtwushi.Rows.Count.ToString();
bashi = dtbashi.Rows.Count.ToString();
yibai = dtyibai.Rows.Count.ToString();
}
