public void SetupFixtureWideObjects()
{
connectionStringMinPermissions = ConfigurationManager.ConnectionStrings["MinPermission"].ConnectionString;
connectionStringAdditionalDb = ConfigurationManager.ConnectionStrings[SecondaryConnectionName].ConnectionString;
scriptRunner = ScriptRunner.Get(PrimaryConnectionName);
scriptRunner.CreateDb(SecondaryConnectionName);
scriptRunner.Run(@"..\..\TestScripts\ExtraDB\01_Create_schema.sql");
ParameterFactory = new SqlParameterFactory();
}
public List <P_TAINFO> GetTAInfoByBUID(Guid BUID)
{
List <P_TAINFO> rtnData;
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
rtnData = sqlServerTemplate.Load <P_TAINFO>("SELECT * FROM P_TAINFO WHERE BUID = @BUID ORDER BY TerritoryTA ",
new SqlParameter[] {
SqlParameterFactory.GetSqlParameter("@BUID", BUID)
});
}
return(rtnData);
}
public P_UserDelegate AgentExist(Guid DelegateID)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var entity = sqlServerTemplate.Find <P_UserDelegate>(
"SELECT * FROM P_UserDelegate WHERE ID=@DelegateID",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@DelegateID", DelegateID)
});
return(entity);
}
}
/// <summary>
/// 使用CN号
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public int UsedCN(string code)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var entity = sqlServerTemplate.Update(
"UPDATE [P_MEETING] SET IsUsed=1 WHERE [Code]=@Code ",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@Code", code)
});
return(entity);
}
}
/// <summary>
/// 根据CN号查找会议信息
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public P_MEETING_VIEW FindByCode(string code)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var entity = sqlServerTemplate.Find <P_MEETING_VIEW>(
"SELECT a.*, b.Name AS 'UserName' FROM P_MEETING a LEFT JOIN WP_QYUSER b ON a.UserId=b.UserId WHERE a.Code=@Code ",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@Code", code)
});
return(entity);
}
}
public LineManager FindUserManagerInfo(Guid LineManagerId)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var entity = sqlServerTemplate.Find <LineManager>(
"select userid as LineManagerId,name as LineManagerName from [dbo].[WP_QYUSER] where ID=@LineManagerId",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@LineManagerId", LineManagerId),
});
return(entity);
}
}
/// <summary>
/// 找到可用(未订餐)的会议
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public List <P_MEETING> LoadByUserId(string userId, string approvedDate)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var list = sqlServerTemplate.Load <P_MEETING>("SELECT * FROM P_MEETING WHERE UserId=@UserId AND IsUsed=0 AND ApprovedDate>@ApprovedDate ORDER BY ApprovedDate DESC ",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@UserId", userId),
SqlParameterFactory.GetSqlParameter("@ApprovedDate", approvedDate)
});
return(list);
}
}
public P_BUINFO GetBUInfoByID(Guid ID)
{
P_BUINFO rtnData;
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
rtnData = sqlServerTemplate.Find <P_BUINFO>("SELECT * FROM P_BUINFO WHERE ID = @ID ORDER BY BUName ",
new SqlParameter[] {
SqlParameterFactory.GetSqlParameter("@ID", ID)
});
}
return(rtnData);
}
/// <summary>
/// 根据TA查找成本中心
/// </summary>
/// <param name="ta">TA</param>
/// <returns></returns>
public List <D_COSTCENTERSELECT> LoadCostCenterByMarketUserID(string market, string UserID, string TerritoryCode)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var list = sqlServerTemplate.Load <D_COSTCENTERSELECT>("SELECT NEWID() as ID,TERRITORY_RM as Name FROM V_TerritoryCodeTA WHERE Market=@market and Mudid=@UserID AND TA_CODE = @TerritoryCode", new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@market", market),
SqlParameterFactory.GetSqlParameter("@UserID", UserID),
SqlParameterFactory.GetSqlParameter("@TerritoryCode", TerritoryCode)
});
return(list);
}
}
public P_UserDelegate AgentInfo(string userId)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var entity = sqlServerTemplate.Find <P_UserDelegate>(
"SELECT * FROM P_UserDelegate WHERE UserId=@UserId",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@UserId", userId)
});
return(entity);
}
}
/// <summary>
/// 根据userId查询人员信息（平台）
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public WP_QYUSER Find(string userId)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var entity = sqlServerTemplate.Find <WP_QYUSER>(
"SELECT * FROM WP_QYUSER WHERE UserId=@UserId",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@UserId", userId)
});
return(entity);
}
}
public P_USERINFO FindTAByUserId(string userId)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var entity = sqlServerTemplate.Find <P_USERINFO>(
"SELECT [ID],[UserId],[Name],[PhoneNumber] ,[CreateDate],[IsCheckedStatement],[Market],[TerritoryCode] FROM P_USERINFO UI WHERE UI.UserId =@UserId",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@UserId", userId)
});
return(entity);
}
}
public List <P_UserDelegatePre> ApproverSecondAgentLoad(string ApprovalNameOrMUDID, string AgentNameOrMUDID, int rows, int page, out int total)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var rtnData = sqlServerTemplate.LoadPages <P_UserDelegatePre>(rows, page, out total, "SELECT * FROM P_UserDelegatePre WHERE ((UserName LIKE '%' + @ApprovalNameOrMUDID + '%') or (UserMUDID LIKE '%' + @ApprovalNameOrMUDID + '%')) AND ((DelegateUserName LIKE '%' + @AgentNameOrMUDID + '%') or (DelegateUserMUDID LIKE '%' + @AgentNameOrMUDID + '%'))", " ORDER BY EndTime DESC",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@ApprovalNameOrMUDID", ApprovalNameOrMUDID),
SqlParameterFactory.GetSqlParameter("@AgentNameOrMUDID", AgentNameOrMUDID)
});
return(rtnData);
}
}
public P_TACode LoadRDCode(string Market, string UserId, string TCode)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var list = sqlServerTemplate.Find <P_TACode>("SELECT NEWID() AS ID, TERRITORY_RD AS Name,TERRITORY_RD AS NameShow FROM V_TerritoryCodeRD WHERE MUDID=@UserId AND TA_CODE=@TCode AND Market=@Market ", new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@UserId", UserId),
SqlParameterFactory.GetSqlParameter("@TCode", TCode),
SqlParameterFactory.GetSqlParameter("@Market", Market)
});
return(list);
}
}
public List <P_TA> LoadTAByMarketUserId(string marketName, string UserID, string TerritoryCode)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var list = sqlServerTemplate.Load <P_TA>("SELECT NEWID() AS ID,Market as MarketName, TERRITORY_TA as Name FROM V_TerritoryCodeTA WHERE Market=@MarketName AND MUDID=@UserID AND TA_CODE=@TerritoryCode", new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@MarketName", marketName),
SqlParameterFactory.GetSqlParameter("@UserID", UserID),
SqlParameterFactory.GetSqlParameter("@TerritoryCode", TerritoryCode)
});
return(list);
}
}
public P_TAINFO GetTAInfoByUserId(string UserId)
{
P_TAINFO rtnData;
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
rtnData = sqlServerTemplate.Find <P_TAINFO>("SELECT * FROM P_TAINFO WHERE TerritoryHead = @UserId ORDER BY TerritoryTA ",
new SqlParameter[] {
SqlParameterFactory.GetSqlParameter("@UserId", UserId)
});
}
return(rtnData);
}
public int DelTAInfoByID(Guid ID)
{
int rtnData;
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
rtnData = sqlServerTemplate.ExecuteNonQuery("DELETE FROM P_TAINFO WHERE ID = @ID ",
new SqlParameter[] {
SqlParameterFactory.GetSqlParameter("@ID", ID)
});
}
return(rtnData);
}
public List <P_ORDER> LoadUploadOrder(string userId, string htCode, int rows, int page, out int total)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var list = sqlServerTemplate.LoadPages <P_ORDER>(rows, page, out total,
$"SELECT * FROM P_ORDER WHERE (UserId LIKE '%' + @UserId + '%') AND (CN LIKE '%' + @HtCode + '%') AND State NOT IN (5, 11) ",
" ORDER BY CreateDate DESC", new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@UserId", userId),
SqlParameterFactory.GetSqlParameter("@HtCode", htCode)
});
return(list);
}
}
public P_Count isHaveApproval(string userId)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
return(sqlServerTemplate.Find <P_Count>("SELECT COUNT(*) AS ApprovalCount "
+ "FROM WP_QYUSER U INNER JOIN WP_QYUSER UP "
+ "ON U.ID = UP.LineManagerID "
+ "WHERE U.UserId = @userId ",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@userId", userId),
}));
}
}
/// <summary>
/// 修改信息
/// </summary>
/// <param name="userId"></param>
/// <param name="phoneNumber"></param>
/// <param name="market"></param>
/// <returns></returns>
public int Edit(string userId, string phoneNumber, string market, string tacode)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
return(sqlServerTemplate.ExecuteNonQuery("UPDATE P_USERINFO SET PhoneNumber=@PhoneNumber,Market=@Market,TerritoryCode=@tacode WHERE UserId=@UserId",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@PhoneNumber", phoneNumber),
SqlParameterFactory.GetSqlParameter("@Market", market),
SqlParameterFactory.GetSqlParameter("@tacode", tacode),
SqlParameterFactory.GetSqlParameter("@UserId", userId)
}));
}
}
public List <P_PREUPLOADORDER> LoadMyApproveAll(string UserId, string Applicant)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var list = sqlServerTemplate.Load <P_PREUPLOADORDER>(
$"SELECT * FROM [P_PreUploadOrder] WHERE [BUHeadMUDID]=@UserId AND State='3' and ((ApplierName like '%" + Applicant + "%' or '" + Applicant + "'='') or (ApplierMUDID like '%" + Applicant + "%' or '" + Applicant + "'=''))",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@UserId", UserId),
SqlParameterFactory.GetSqlParameter("@Applicant", Applicant)
});
return(list);
}
}
/// <summary>
/// 根据订单号查询订单详情
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public P_ORDER FindOrderByHTCode(string HTCode)
{
P_ORDER rtnData;
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
rtnData = sqlServerTemplate.Find <P_ORDER>("SELECT * FROM P_ORDER WHERE CN = @CN and State not in(5,11)",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@CN", HTCode)
});
}
return(rtnData);
}
/// <summary>
/// 分页查询成本中心
/// </summary>
/// <param name="code"></param>
/// <param name="name"></param>
/// <param name="rows"></param>
/// <param name="page"></param>
/// <param name="total"></param>
/// <returns></returns>
public List <M_COSTCENTER> LoadPage(string code, string name, int rows, int page, out int total)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var list = sqlServerTemplate.LoadPages <M_COSTCENTER>(rows, page, out total,
"SELECT * FROM M_COSTCENTER WHERE State<>4 AND (Code LIKE @Code OR Name LIKE @Name) ",
" ORDER BY ModifyDate DESC ", new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@Code", code),
SqlParameterFactory.GetSqlParameter("@Name", name),
});
return(list);
}
}
public int AddTAInfo()
{
int rtnData;
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
rtnData = sqlServerTemplate.ExecuteNonQuery("INSERT INTO P_TAINFO SELECT NEWID(),*,'','','',NULL,@CreateDate,@ModifyDate FROM (SELECT DISTINCT TERRITORY_TA FROM V_TERRITORY_TA WHERE TERRITORY_TA NOT IN (SELECT TERRITORYTA FROM P_TAINFO)) TA ",
new SqlParameter[] {
SqlParameterFactory.GetSqlParameter("@CreateDate", DateTime.Now),
SqlParameterFactory.GetSqlParameter("@ModifyDate", DateTime.Now)
});
}
return(rtnData);
}
/// <summary>
/// Updates one or more records from the ComplaintType table
/// </summary>
/// <param name="name" />
/// <param name="id" />
/// <returns></returns>
/// <remarks></remarks>
public IDbCommand GetUpdateDbCommand(string name, int id)
{
var command = new SqlCommand("ComplaintType_Update");
command.CommandType = CommandType.StoredProcedure;
if (!string.IsNullOrEmpty(name))
{
command.Parameters.Add(SqlParameterFactory.CreateInputParameter("@Name", SqlDbType.NVarChar, name));
}
else
{
command.Parameters.Add(SqlParameterFactory.CreateInputParameter("@Name", SqlDbType.NVarChar, null));
}
command.Parameters.Add(SqlParameterFactory.CreateInputParameter("@Id", SqlDbType.Int, id));
command.Connection = (SqlConnection)ComplaintTypeDbConnectionHolder.Connection;
return(command);
}
/// <summary>
/// 加载HT编号
/// </summary>
/// <returns></returns>
public List <P_ORDER> LoadHTCode(string userId)
{
List <P_ORDER> rtnData;
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
rtnData = sqlServerTemplate.Load <P_ORDER>("SELECT a.CN AS CN,a.MeetingName FROM P_ORDER a where (a.State=6 OR a.State=7 OR a.State=8 OR a.State=9) AND a.IsOrderUpload = 0 AND ((a.UserId = @UserId and isnull(IsTransfer,0)=0) or (TransferUserMUDID=@UserId and IsTransfer=1)) ORDER BY a.CreateDate DESC",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@UserId", userId)
});
}
return(rtnData);
}
/// <summary>
/// 查找用户所在的组
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public List <P_GROUP_MEMBER> LoadUserGroup(string userId)
{
List <P_GROUP_MEMBER> rtnData;
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
rtnData = sqlServerTemplate.Load <P_GROUP_MEMBER>(
"SELECT * FROM P_GROUP_MEMBER WHERE (UserId = @UserId) ",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@UserId", userId)
});
}
return(rtnData);
}
/// <summary>
/// 增加一条
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int Add(M_COSTCENTER_MANAGER entity)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
return(sqlServerTemplate.ExecuteNonQuery("INSERT INTO M_COSTCENTER_MANAGER (ID, CostCenterID, QyUserID, CreateDate) VALUES (@ID, @CostCenterID, @QyUserID, @CreateDate) ",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@ID", entity.ID),
SqlParameterFactory.GetSqlParameter("@CostCenterID", entity.CostCenterID),
SqlParameterFactory.GetSqlParameter("@QyUserID", entity.QyUserID),
SqlParameterFactory.GetSqlParameter("@CreateDate", entity.CreateDate)
}));
}
}
public List <P_GROUP_MEMBER> ExportNonHTGroupList(string MUDID, string Name, int GroupType)
{
var sqlServerTemplate = sqlServerTemplFactoryNonHT.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var list = sqlServerTemplate.Load <P_GROUP_MEMBER>(
"SELECT G.*,Q.State AS Qstate FROM [dbo].[P_GROUP_MEMBER] G LEFT JOIN dbo.WP_QYUSER Q ON G.UserId = Q.UserId WHERE (G.GroupType = @GroupType) AND (G.UserId LIKE '%' + @MUDID + '%') AND (G.UserName LIKE '%' + @Name + '%') AND (Q.State=1) ORDER BY G.CreateDate DESC ", new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@MUDID", MUDID),
SqlParameterFactory.GetSqlParameter("@Name", Name),
SqlParameterFactory.GetSqlParameter("@GroupType", GroupType)
});
return(list);
}
}
public List <P_GROUP_MEMBER> GetGroupMembersByType(GroupTypeEnum GroupType)
{
List <P_GROUP_MEMBER> rtnData;
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
rtnData = sqlServerTemplate.Load <P_GROUP_MEMBER>(
"SELECT ID, GroupType, UserId, UserName, CreateDate, CreateUserId FROM P_GROUP_MEMBER WHERE (GroupType = @GroupType) ",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@GroupType", GroupType.ToString("D"))
});
}
return(rtnData);
}
public LineManager FindUserManagerInfo(string UserId)
{
var sqlServerTemplate = sqlServerTemplFactory.CreateDbTemplate();
using (var conn = sqlServerTemplate.GetSqlConnection())
{
conn.Open();
var entity = sqlServerTemplate.Find <LineManager>(
"select pu.UserId as LineManagerId,pu.Name as LineManagerName from [dbo].[WP_QYUSER] qu inner join [dbo].[WP_QYUSER] pu on qu.LineManagerID=pu.ID where qu.UserId=@LineManagerId",
new SqlParameter[]
{
SqlParameterFactory.GetSqlParameter("@LineManagerId", UserId),
});
conn.Close();
return(entity);
}
}
public void FixtureSetup()
{
parameterFactory = new SqlParameterFactory();
}
public void InitializeTests()
{
ConnectionString = ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString;
ParameterFactory = new SqlParameterFactory();
}
