/// <summary>
/// 是否存在该记录
/// </summary>
/// <param name="fullName">姓名</param>
/// <param name="sex">性别</param>
/// <param name="identityCardNumber">身份证号码</param>
/// <param name="address">地址</param>
/// <param name="issuedAt">签发地</param>
/// <param name="validityPeriodBegin">生效日期</param>
/// <param name="validityPeriodEnd">失效日期</param>
/// <returns>身份证号码库编号</returns>
public int Exists(string fullName, int sex, string identityCardNumber, string address, string issuedAt, string effectedDate, string expiredDate)
{
int result = 0;
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 IdentityCardLibraryId from dbo.[IdentityCardLibrary]");
strSql.Append(" where IsDeleted=0");
strSql.AppendFormat(" and FullName = '{0}'", fullName);
strSql.AppendFormat(" and Sex = {0}", sex);
strSql.AppendFormat(" and IdentityCardNumber = '{0}'", identityCardNumber);
strSql.AppendFormat(" and Address = '{0}'", address);
strSql.AppendFormat(" and IssuedAt = '{0}'", issuedAt);
if (!string.IsNullOrEmpty(effectedDate))
{
strSql.AppendFormat(" and EffectedDate = '{0}'", effectedDate);
}
if (!string.IsNullOrEmpty(expiredDate))
{
strSql.AppendFormat(" and ExpiredDate = '{0}'", expiredDate);
}
DataTable dt = BWJSHelperSQL.ReturnDataTable(strSql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
result = Convert.ToInt32(dt.Rows[0]["IdentityCardLibraryId"]);
}
return(result);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataTable GetUserFunction(int userId)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"select a.UserId,a.LoginName,a.UserName,b.RoleId,c.FunctionId,d.FunctionName,d.FunctionCode,d.FunctionClass,d.ParentId,d.ClassId,d.ExternalLinkAddress from [dbo].[Users] a
left join[dbo].[UserRole] b on b.UserId = a.UserID and b.IsDeleted=0
left join[dbo].[RoleFunction] c on c.RoleId = b.RoleId and c.IsDeleted=0
left join[dbo].[Function] d on d.FunctionId = c.FunctionId and d.IsDeleted=0
where a.UserId ={0} and a.RecordIsDelete=0", userId);
return(BWJSHelperSQL.ReturnDataTable(strSql.ToString()));
}
public DataTable GetUserRoleTable(string where)
{
StringBuilder tablesql = new StringBuilder();
tablesql.Append(@"SELECT u.*,
(ca.name + ca1.Name + ca2.Name+u.Address) AS CityData, d.DepartmentName,re.RoleName,re.RoleId,re.ParentId FROM dbo.Users u
LEFT JOIN dbo.CityArea ca ON u.ProvinceID = ca.ID
LEFT JOIN dbo.CityArea ca1 ON u.CityID = ca1.ID
LEFT JOIN dbo.CityArea ca2 ON u.CountyID = ca2.ID
LEFT JOIN[DepartmentInfo] d ON u.DepartmentID = d.DepartmentID AND d.RecordIsDelete = 0
LEFT JOIN dbo.UserRole uri ON uri.UserId=u.UserID AND uri.IsDeleted=0
LEFT JOIN dbo.[Role] re ON re.RoleId=uri.RoleId AND re.IsDeleted=0");
tablesql.Append(" where " + where + "");
return(BWJSHelperSQL.ReturnDataTable(tablesql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataTable QueryOrderList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"
select a.CompanyId,c.CompanyName,a.UserId,d.UserType,d.UserName MerchantName,b.SettlementMethod,b.SalesPercentage,sum(round(a.OrderMoney,2))sumOrderMoney,sum(round(a.MerchantMoney,2))sumMerchantMoney,sum(round(a.AgentMoney,2))sumAgentMoney,sum(round(a.HQMoney,2))sumHQMoney,sum(round(a.NetProfit,2))sumNetProfit
from [dbo].[OrderRebate] a
left join [dbo].[CompanyRebate] b on b.CompanyId=a.CompanyId
left join [dbo].[Company] c on c.CompanyId=a.CompanyId
left join [dbo].[Users] d on d.UserId=a.UserId");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(@" group by a.CompanyId,c.CompanyName,a.UserId,d.UserType,d.UserName,b.SettlementMethod,b.SalesPercentage");
return BWJSHelperSQL.ReturnDataTable(strSql.ToString());
}
/// <summary>
/// 获取最大排序编号加1
/// </summary>
/// <returns></returns>
public int GetMaxOrderId()
{
int maxOrderId = 1;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select max(OrderId)+1 from [Function]");
DataTable dt = BWJSHelperSQL.ReturnDataTable(strSql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
maxOrderId = Convert.ToInt32(dt.Rows[0][0]);
}
}
catch { }
return(maxOrderId);
}
/// <summary>
/// 获取广告
/// </summary>
/// <returns></returns>
public DataTable GetReleaseExpandL(string where)
{
try
{
StringBuilder tablesql = new StringBuilder();
tablesql.Append(@"select
a.*,b.[AdPositionID] PositionId,b.[Name] PositionName ,b.Sort
from [BWJSDB].[dbo].[AdRelease] a left join [BWJSDB].[dbo].[AdPosition] b on b.[AdReleaseID]=a.[AdReleaseID] AND b.RecordIsDelete=0 ");
if (!string.IsNullOrEmpty(where))
{
tablesql.AppendFormat(" where {0} ", where);
}
return(BWJSHelperSQL.ReturnDataTable(tablesql.ToString()));
}
catch
{
}
return(null);
}
