public List <CityArea> GetCityAreaAllList(int ParentID)
{
string strSelectFields = " ID,ParentID,Name,Grade";
string strSql = "SELECT " + strSelectFields + " FROM [CityArea] WHERE ParentID=" + ParentID + " ORDER BY ParentID ASC ";
return(SqlDataUtilityHelper.GetListFromDB <CityArea>(strSelectFields, this.ConnectionString, strSql));
}
/// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddExceptionLog(ExceptionLog model)
{
int result = 0;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into ExceptionLog(");
strSql.Append("ClassName,MethodName,ErrorDesc,RecordIsDelete,RecordUpdateTime,RecordCreateTime");
strSql.Append(") values (");
strSql.Append("@ClassName,@MethodName,@ErrorDesc,0,getdate(),getdate()");
strSql.Append(") ");
strSql.Append(";select SCOPE_IDENTITY()");
SqlParameter[] parameters = {
new SqlParameter("@ClassName", model.ClassName),
new SqlParameter("@MethodName", model.MethodName),
new SqlParameter("@ErrorDesc", model.ErrorDesc),
};
result = SqlDataUtilityHelper.ExecuteScalar(this.ConnectionString, strSql.ToString(), parameters);
}
catch (Exception ex)
{
}
return result;
}
public List <AdPostionReleaseOutputModel> GetAdPostionReleaseList(AdPostionReleaseOutputModel AdPostionReleaseIutputModel)
{
try
{
string strOrderBy = " ";
string strOutFields = "DefaultPicUrl, FileName, VirtualPath ,FileSuffix";
string strSelectFields = "AP.DefaultPicUrl, R.FileName, R.VirtualPath , R.FileSuffix";
string strTable = @"AdPosition AP
Left join AdRelease AR on AR.AdReleaseID = AP.AdReleaseID and AR.RecordIsDelete = 0
Left join Resource R on R.ResourceID = AR.ResourceID and R.RecordIsDelete = 0";
string strSql = "SELECT " + strSelectFields + " FROM " + strTable + " WHERE AP.RecordIsDelete= 0 ";
string strWhere = "";
strSql = strSql + strWhere;
strSql = strSql + strOrderBy;
SqlParameter[] parameters = new SqlParameter[] {
// new SqlParameter ("@TypeID",TypeID)
};
List <AdPostionReleaseOutputModel> list = SqlDataUtilityHelper.GetListFromDB <AdPostionReleaseOutputModel>(strOutFields, ConnectionString, strSql, parameters);
return(list);
}
catch
{
}
return(null);
}
{ /// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddDepartmentUserLog(DepartmentUserLog model)
{
int result = 0;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"INSERT INTO [dbo].[DepartmentUserLog]
([DepartmentID],[Remark],[UpType],[UserID],[UpdateUser],[RecordIsDelete],[RecordCreateTime] ,[RecordUpdateTime])
VALUES (@DepartmentID ,@Remark ,@UpType ,@UserID
,@UpdateUser ,@RecordIsDelete ,@RecordCreateTime,@RecordUpdateTime )");
SqlParameter[] parameters =
{
new SqlParameter("@DepartmentID", model.DepartmentID),
new SqlParameter("@Remark", model.Remark),
new SqlParameter("@UpType", model.UpType),
new SqlParameter("@UserID", model.UserID),
new SqlParameter("@UpdateUser", model.UpdateUser),
new SqlParameter("@RecordIsDelete", model.RecordIsDelete),
new SqlParameter("@RecordCreateTime", model.RecordCreateTime),
new SqlParameter("@RecordUpdateTime", model.RecordUpdateTime)
};
result = SqlDataUtilityHelper.ExecuteScalar(this.ConnectionString, strSql.ToString(), parameters);
}
catch (Exception ex)
{
}
return(result);
}
public AdPosition GetAdPositionOne(int AdPositionID)
{
try
{
string outFields = "AdPositionID, Name, Sort, DefaultPicUrl, AdReleaseID, CreatUserID, RecordUpdateUserID, RecordIsDelete, RecordUpdateTime, RecordCreateTime ";
string strSelectFields = "AdPositionID, Name, Sort, DefaultPicUrl, AdReleaseID, CreatUserID, RecordUpdateUserID, RecordIsDelete, RecordUpdateTime, RecordCreateTime ";
string strSql = "Select top 1 " + strSelectFields + " from AdPosition where RecordIsDelete=0 and AdPositionID=@AdPositionID ";
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@AdPositionID", AdPositionID)
};
var result = SqlDataUtilityHelper.GetObjectFromDB <AdPosition>(outFields.Split(','), this.ConnectionString, strSql, parameters);
if (result != null)
{
return(result);
}
else
{
return(null);
}
}
catch
{
}
return(null);
}
//public Function GetFunctionInfoOne(int FunctionId)
//{
// Function item = null;
// try
// {
// string strSelectFields = @"ClassId ,
//CreateDate ,
//CreateId ,
//EditDate ,
//EditId ,
//ExternalLinkAddress ,
//FunctionClass ,
//FunctionCode ,
//FunctionDir ,
//FunctionIcon ,
//FunctionId ,
//FunctionName ,
//IsDeleted ,
//ParentId ,
//Remark ,
//Status ";
// string strSql = @"Select ClassId ,
//CreateDate ,
//CreateId ,
//EditDate ,
//EditId ,
//ExternalLinkAddress ,
//FunctionClass ,
//FunctionCode ,
//FunctionDir ,
//FunctionIcon ,
//FunctionId ,
//FunctionName ,
//IsDeleted ,
//ParentId ,
//Remark ,
//Status from [Function] where FunctionId = @FunctionId ";
// SqlParameter[] parameters = new SqlParameter[]{
// new SqlParameter ("@FunctionId",FunctionId)
// };
// Function obj = SqlDataUtilityHelper.GetObjectFromDB<Function>(strSelectFields.Split(','), this.ConnectionString, strSql, parameters);
// if (obj != null)
// {
// return obj;
// }
// else
// {
// return null;
// }
// }
// catch (Exception ex)
// {
// }
// return item;
//}
//public string GetFunctionName(int FunctionID, int RecordIsDelete)
//{
// try
// {
// string strSql = "Select FunctionName from FunctionInfo where FunctionID =@FunctionID And RecordIsDelete = @RecordIsDelete";
// SqlParameter[] parameters = new SqlParameter[] {
// new SqlParameter("@FunctionID", FunctionID),
// new SqlParameter("@RecordIsDelete", RecordIsDelete)
// };
// object obj = SqlDataUtilityHelper.ExecuteScalar(this.ConnectionString, strSql, parameters);
// if (obj != null)
// {
// return (string)obj;
// }
// else
// {
// return "";
// }
// }
// catch (Exception ex)
// {
// }
// return null;
//}
public bool soleFunctionCode(string RandomCode, int RecordIsDelete)
{
int result = -1;
try
{
string sql = "SELECT COUNT(*) from [Function] where FunctionCode=@FunctionCode And IsDeleted =@IsDeleted ";
IEnumerable <SqlParameter> parameters = new SqlParameter[] {
new SqlParameter("@FunctionCode", RandomCode),
new SqlParameter("@IsDeleted", RecordIsDelete)
};
object obj = SqlDataUtilityHelper.ExecuteScalar(this.ConnectionString, sql, parameters.ToArray());
//如果执行正确并且受影响行数大于1的话，正确。编辑的状态下，有且仅有一条FunctionCode
if (obj != null && ((int)obj) > 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
}
return(result > 0);
}
public List <ProductsViewModel> GetProducts(int CategoryId)
{
try
{
string strOrderBy = " ";
string strOutFields = "ProductsID,CompanyName,ProductName,CategoryName,CaseCode,ProductPictureUrl,ProducDescribe";
string strSelectFields = "a.ProductsID,a.CompanyName,a.ProductName,b.CategoryName,a.CaseCode,a.ProductPictureUrl,a.ProducDescribe";
string strTable = @"products a
Left join ProductCategory b on b.ProductCategoryID=a.ProductCategoryID ";
string strSql = "SELECT " + strSelectFields + " FROM " + strTable + " WHERE a.RecordIsDelete= 0 ";
string strWhere = " and b.ProductCategoryID=" + CategoryId;
strSql = strSql + strWhere;
strSql = strSql + strOrderBy;
SqlParameter[] parameters = new SqlParameter[] {
// new SqlParameter ("@TypeID",TypeID)
};
List <ProductsViewModel> list = SqlDataUtilityHelper.GetListFromDB <ProductsViewModel>(strOutFields, ConnectionString, strSql, parameters);
return(list);
}
catch (Exception ex)
{
}
return(null);
}
public int VerificationPassword(string LoginName, string Password, string CardID)
{
int result = -1;
try
{
string sql = "select top 1 isnull(UserID,0) from Users where LoginName=@LoginName AND RecordIsDelete=0";
IEnumerable <SqlParameter> parameters = new SqlParameter[] {
new SqlParameter("@LoginName", LoginName)
};
result = SqlDataUtilityHelper.ExecuteScalar(this.ConnectionString, sql.ToString(), parameters);
if (result > 0)
{
string sql2 = "select top 1 isnull(UserID,0) from Users where LoginName=@LoginName and Password=@Password AND RecordIsDelete=0";
IEnumerable <SqlParameter> parameters2 = new SqlParameter[] {
new SqlParameter("@LoginName", LoginName),
new SqlParameter("@Password", Password)
};
result = SqlDataUtilityHelper.ExecuteScalar(this.ConnectionString, sql2.ToString(), parameters2);
if (result > 0)
{
string sql3 = "select top 1 isnull(UserID,0) from Users where LoginName=@LoginName and Password=@Password and CardID=@CardID AND RecordIsDelete=0";
IEnumerable <SqlParameter> parameters3 = new SqlParameter[] {
new SqlParameter("@LoginName", LoginName),
new SqlParameter("@CardID", CardID),
new SqlParameter("@Password", Password)
};
result = SqlDataUtilityHelper.ExecuteScalar(this.ConnectionString, sql3.ToString(), parameters3);
if (result > 0)
{
}
else
{
result = -3;
}
}
else
{
result = -2;
}
}
else
{
result = -1;
}
}
catch (Exception)
{
throw;
}
return(result);
}
public DataPager <AdPosition> GetAdPositionList(AdPosition model, int pageNo, int pageSize)
{
string rowNumberOrderby = "AdPositionID asc";
StringBuilder strWhere = new StringBuilder("");
strWhere.Append("1=1");
var selectFields = "AdPositionID, Name, Sort, DefaultPicUrl, AdReleaseID, CreatUserID, RecordUpdateUserID, RecordIsDelete, RecordUpdateTime, RecordCreateTime ";
var outFields = "AdPositionID, Name, Sort, DefaultPicUrl, AdReleaseID, CreatUserID, RecordUpdateUserID, RecordIsDelete, RecordUpdateTime, RecordCreateTime ";
var tableName = @"AdPosition ";
var dataPager = SqlDataUtilityHelper.GetDataToPager <AdPosition>(outFields, selectFields, tableName, rowNumberOrderby, this.ConnectionString, strWhere.ToString(), null, pageNo, pageSize);
return(dataPager);
}
/// <summary>
/// 获取当前部门下的授权功能
/// </summary>
/// <param name="DepartmentID">部门ID</param>
/// <param name=""></param>
/// <returns></returns>
public List <DepartmentInfo> GetDepartmentInfolist(int ParentID, int RecordIsDelete)
{
string selectFields = @" CreatedUser ,
DepartmentCode ,
DepartmentID ,
DepartmentName ,
IsReceiveBusiness ,
ParentID ,
RecordCreateTime ,
RecordIsDelete ,
RecordUpdateTime ,
Remark ,
UpdateUser";
string strSql = "Select " + selectFields + " from DepartmentInfo where ParentID = " + ParentID + " and RecordIsDelete = " + RecordIsDelete + "";
List <DepartmentInfo> dataList = SqlDataUtilityHelper.GetListFromDB <DepartmentInfo>(selectFields, this.ConnectionString, strSql, null);
return(dataList);
}
public int VerificationIsUserName(string LoginName)
{
int result = -1;
try
{
string sql = "select top 1 isnull(UserType,0) from Users where LoginName=@LoginName AND RecordIsDelete=0";
IEnumerable <SqlParameter> parameters = new SqlParameter[] {
new SqlParameter("@LoginName", LoginName)
};
result = SqlDataUtilityHelper.ExecuteScalar(this.ConnectionString, sql.ToString(), parameters);
}
catch (Exception)
{
throw;
}
return(result);
}
public int VerificationIsFunName(string FunctionName, int FunctionId)
{
int result = -1;
try
{
string sql = "SELECT TOP 1 FunctionId FROM dbo.[Function] WHERE IsDeleted=0 AND FunctionId=@FunctionId AND FunctionName=@FunctionName ";
IEnumerable <SqlParameter> parameters = new SqlParameter[] {
new SqlParameter("@FunctionName", FunctionName),
new SqlParameter("@FunctionId", FunctionId)
};
result = SqlDataUtilityHelper.ExecuteScalar(this.ConnectionString, sql.ToString(), parameters);
}
catch (Exception)
{
throw;
}
return(result);
}
/// <summary>
/// 获取当前部门下的授权功能
/// </summary>
/// <param name="DepartmentID">部门ID</param>
/// <param name=""></param>
/// <returns></returns>
public List <Function> GetFuctionDepartmentExtentList2(string where)
{
string selectFields = @" ClassId ,CreateDate ,CreateId ,
EditDate ,
EditId ,
ExternalLinkAddress ,
FunctionClass ,
FunctionCode ,
FunctionDir ,
FunctionIcon ,
FunctionId ,
FunctionName ,
IsDeleted ,
ParentId ,
Remark ,
Status ";
string strSql = @" SELECT f.ClassId ,
f.CreateDate ,
f.CreateId ,
f.EditDate ,
f.EditId ,
f.ExternalLinkAddress ,
f.FunctionClass ,
f.FunctionCode ,
f.FunctionDir ,
f.FunctionIcon ,
f.FunctionId ,
f.FunctionName ,
f.IsDeleted ,
f.OrderId ,
f.ParentId ,
f.Remark ,
f.[Status] FROM dbo.[Function] f
LEFT JOIN [RoleFunction] rf ON rf.FunctionId=f.FunctionId AND rf.IsDeleted=0
LEFT JOIN [role] r ON r.RoleId=rf.RoleId AND r.IsDeleted=0
LEFT JOIN [UserRole] ur ON ur.RoleId=r.RoleId AND ur.IsDeleted=0 ";
strSql += " WHERE " + where;
List <Function> dataList = SqlDataUtilityHelper.GetListFromDB <Function>(selectFields, this.ConnectionString, strSql, null);
return(dataList);
}
public int VerificationIsName(string Name, int AdPositionID)
{
int result = -1;
try
{
string sql = "SELECT TOP 1 AdPositionID FROM dbo.AdPosition WHERE RecordIsDelete=0 AND Name=@Name and AdPositionID<>@AdPositionID";
IEnumerable <SqlParameter> parameters = new SqlParameter[] {
new SqlParameter("@Name", Name),
new SqlParameter("@AdPositionID", AdPositionID)
};
result = SqlDataUtilityHelper.ExecuteScalar(this.ConnectionString, sql.ToString(), parameters);
}
catch (Exception)
{
throw;
}
return(result);
}
/// <summary>
/// 获取当前部门下的授权功能
/// </summary>
/// <param name="DepartmentID">部门ID</param>
/// <param name=""></param>
/// <returns></returns>
public List <Function> GetFuctionDepartmentExtentList()
{
string selectFields = @" ClassId ,
CreateDate ,
CreateId ,
EditDate ,
EditId ,
ExternalLinkAddress ,
FunctionClass ,
FunctionCode ,
FunctionDir ,
FunctionIcon ,
FunctionId ,
FunctionName ,
IsDeleted ,
ParentId ,
Remark ,
Status ";
string strSql = @"SELECT ClassId ,
CreateDate ,
CreateId ,
EditDate ,
EditId ,
ExternalLinkAddress ,
FunctionClass ,
FunctionCode ,
FunctionDir ,
FunctionIcon ,
FunctionId ,
FunctionName ,
IsDeleted ,
ParentId ,
Remark ,
Status
FROM [Function]
WHERE IsDeleted=0 ";
List <Function> dataList = SqlDataUtilityHelper.GetListFromDB <Function>(selectFields, this.ConnectionString, strSql, null);
return(dataList);
}
public List <Function> GetFunctionInfolist(int ParentId)
{
string selectFields = @"ClassId ,
CreateDate ,
CreateId ,
EditDate ,
EditId ,
ExternalLinkAddress ,
FunctionClass ,
FunctionCode ,
FunctionDir ,
FunctionIcon ,
FunctionId ,
FunctionName ,
IsDeleted ,
ParentId ,
Remark ,
Status ";
string strSql = @"select ClassId ,
CreateDate ,
CreateId ,
EditDate ,
EditId ,
ExternalLinkAddress ,
FunctionClass ,
FunctionCode ,
FunctionDir ,
FunctionIcon ,
FunctionId ,
FunctionName ,
IsDeleted ,
ParentId ,
Remark ,
Status from [Function] Where IsDeleted=0 And ParentId=@ParentId ";
List <Function> dataList = SqlDataUtilityHelper.GetListFromDB <Function>(selectFields, this.ConnectionString, strSql, new[] { new SqlParameter("@ParentId", ParentId) });
return(dataList);
}
public List <ProductCategory> GetCardTypeList()
{
try
{
string strOrderBy = " order by OrderId asc";
string strOutFields = "ProductCategoryId,CategoryName,OrderId,ParentId,ProductCaPictureUrl";
string strSelectFields = " ProductCategoryId,CategoryName,OrderId,ParentId,ProductCaPictureUrl";
string strTable = @" ProductCategory ";
string strSql = "SELECT " + strSelectFields + " FROM " + strTable + " ";
string strWhere = "";
strSql = strSql + strWhere;
strSql = strSql + strOrderBy;
SqlParameter[] parameters = new SqlParameter[] {
// new SqlParameter ("@TypeID",TypeID)
};
List <ProductCategory> list = SqlDataUtilityHelper.GetListFromDB <ProductCategory>(strOutFields, ConnectionString, strSql, parameters);
return(list);
}
catch (Exception ex)
{
}
return(null);
}
public List <Products> ProductsByCaseCode(string caseCode)
{
try
{
string strOrderBy = " ";
string strOutFields = "ProductsID,CompanyId ,CompanyName ,ProductTypeId ,ProductTypeName ,ProductCategoryId ,ProductName ,ProductPlan,CaseCode ,PeriodDate ,PeriodAge,InsuranceDate,IsQuitInsure,CardTypeIDs,CreatUserID ,RecordUpdateUserID ,RecordIsDelete ,RecordUpdateTime ,RecordCreateTime,Remark";
string strSelectFields = " ProductsID,CompanyId ,CompanyName ,ProductTypeId ,ProductTypeName ,ProductCategoryId ,ProductName ,ProductPlan,CaseCode ,PeriodDate ,PeriodAge,InsuranceDate,IsQuitInsure,CardTypeIDs,CreatUserID ,RecordUpdateUserID ,RecordIsDelete ,RecordUpdateTime ,RecordCreateTime,Remark ";
string strTable = @" Products ";
string strSql = "SELECT " + strSelectFields + " FROM " + strTable + " ";
string strWhere = " where CaseCode='" + caseCode + "'";
strSql = strSql + strWhere;
strSql = strSql + strOrderBy;
SqlParameter[] parameters = new SqlParameter[] {
// new SqlParameter ("@TypeID",TypeID)
};
List <Products> list = SqlDataUtilityHelper.GetListFromDB <Products>(strOutFields, ConnectionString, strSql, parameters);
return(list);
}
catch (Exception ex)
{
}
return(null);
}
/// <summary>
/// 获取广告
/// </summary>
/// <returns></returns>
public List <AdReleaseExpand> GetReleaseExpandList()
{
try
{
string strOrderBy = " ";
string strOutFields = "a.*,b.[AdPositionID] PositionId,b.[Name] PositionName ";
string strSelectFields = "a.*,b.[AdPositionID] PositionId,b.[Name] PositionName ";
string strTable = @"[BWJSDB].[dbo].[AdRelease] a left join [BWJSDB].[dbo].[AdPosition] b on b.[AdReleaseID]=a.[AdReleaseID]";
string strSql = "SELECT " + strSelectFields + " FROM " + strTable + " WHERE a.RecordIsDelete=0 ";
string strWhere = "";
strSql = strSql + strWhere;
strSql = strSql + strOrderBy;
SqlParameter[] parameters = new SqlParameter[] {
// new SqlParameter ("@TypeID",TypeID)
};
List <AdReleaseExpand> list = SqlDataUtilityHelper.GetListFromDB <AdReleaseExpand>(strOutFields, ConnectionString, strSql, parameters);
return(list);
}
catch
{
}
return(null);
}
public List <RelationViewModel> GetRelationViewModelList(string caseCode)
{
try
{
string strOrderBy = " ";
string strOutFields = "Value,Name";
string strSelectFields = " Value,Name ";
string strTable = @"Relation where ID in(select * from dbo.F_SplitToInt((select RelationIDs from Products where CaseCode='" + caseCode + "'),','))";
string strSql = "SELECT " + strSelectFields + " FROM " + strTable + " ";
string strWhere = "";
strSql = strSql + strWhere;
strSql = strSql + strOrderBy;
SqlParameter[] parameters = new SqlParameter[] {
// new SqlParameter ("@TypeID",TypeID)
};
List <RelationViewModel> list = SqlDataUtilityHelper.GetListFromDB <RelationViewModel>(strOutFields, ConnectionString, strSql, parameters);
return(list);
}
catch (Exception ex)
{
}
return(null);
}
public List <AdPostionReleaseOutputModel> GetAdPostionReleaseList(int typeId)
{
try
{
string strOrderBy = " ORDER BY AP.Sort ";
string strOutFields = "DefaultPicUrl, [FileName], FilePath ,FileSuffix,BeginTime,EndTime";
string strSelectFields = "AP.DefaultPicUrl, R.FileName, R.FilePath , R.FileSuffix,AR.BeginTime,AR.EndTime";
string strTable = @"AdPosition AP
Left join AdRelease AR on AP.AdReleaseID =AR.AdReleaseID and AR.RecordIsDelete = 0
Left join [Resource] R on R.ResourceID = AR.ResourceID and R.RecordIsDelete = 0";
string strSql = "SELECT " + strSelectFields + " FROM " + strTable + " WHERE AP.RecordIsDelete= 0 and AP.TypeId=" + typeId;
strSql = strSql + strOrderBy;
SqlParameter[] parameters = new SqlParameter[] {
// new SqlParameter ("@TypeID",TypeID)
};
List <AdPostionReleaseOutputModel> list = SqlDataUtilityHelper.GetListFromDB <AdPostionReleaseOutputModel>(strOutFields, ConnectionString, strSql, parameters);
return(list);
}
catch
{
}
return(null);
}
