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(); }