|
0
|
1 using System;
|
|
|
2 using System.Collections.Generic;
|
|
|
3
|
|
|
4 using BLToolkit.Data;
|
|
|
5 using BLToolkit.DataAccess;
|
|
|
6
|
|
|
7 namespace PetShop.BusinessLogic.DataAccess
|
|
|
8 {
|
|
|
9 using ObjectModel;
|
|
|
10
|
|
|
11 public abstract class ProfileAccessor : AccessorBase<ProfileAccessor.DB, ProfileAccessor>
|
|
|
12 {
|
|
|
13 public class DB : DbManager { public DB() : base("ProfileDB") {} }
|
|
|
14
|
|
|
15 [SqlQuery(@"
|
|
|
16 SELECT
|
|
|
17 UniqueID
|
|
|
18 FROM
|
|
|
19 Profiles
|
|
|
20 WHERE
|
|
|
21 Username = @userName AND ApplicationName = @appName")]
|
|
|
22 public abstract int? GetUniqueID(string @userName, string @appName);
|
|
|
23
|
|
|
24 [SqlQuery(@"
|
|
|
25 SELECT
|
|
|
26 UniqueID
|
|
|
27 FROM
|
|
|
28 Profiles
|
|
|
29 WHERE
|
|
|
30 Username = @userName AND ApplicationName = @appName AND IsAnonymous != @isAuthenticated")]
|
|
|
31 public abstract int? GetUniqueIDAuth(string @userName, string @appName, bool @isAuthenticated);
|
|
|
32
|
|
|
33 [SqlQuery(@"
|
|
|
34 INSERT INTO Profiles (
|
|
|
35 Username, ApplicationName, LastActivityDate, LastUpdatedDate, IsAnonymous
|
|
|
36 ) Values (
|
|
|
37 @userName, @appName, getdate(), getdate(), CASE WHEN @isAuthenticated = 1 THEN 0 ELSE 1 END
|
|
|
38 )
|
|
|
39
|
|
|
40 SELECT SCOPE_IDENTITY()")]
|
|
|
41 public abstract int CreateProfile(string @userName, string @appName, bool @isAuthenticated);
|
|
|
42
|
|
|
43 [SqlQuery(@"
|
|
|
44 SELECT
|
|
|
45 a.FirstName as ToFirstName,
|
|
|
46 a.LastName as ToLastName,
|
|
|
47 a.Address1 as Addr1,
|
|
|
48 a.Address2 as Addr2,
|
|
|
49 a.City,
|
|
|
50 a.State,
|
|
|
51 a.Zip,
|
|
|
52 a.Country,
|
|
|
53 a.Email,
|
|
|
54 a.Phone
|
|
|
55 FROM
|
|
|
56 Account a
|
|
|
57 JOIN Profiles p ON p.UniqueID = a.UniqueID
|
|
|
58 WHERE
|
|
|
59 p.Username = @userName AND p.ApplicationName = @appName;")]
|
|
|
60 public abstract Address GetAccountInfo(string @userName, string @appName);
|
|
|
61
|
|
|
62 [SqlQuery(@"
|
|
|
63 SELECT
|
|
|
64 c.ItemId,
|
|
|
65 c.Name,
|
|
|
66 c.Type,
|
|
|
67 c.Price,
|
|
|
68 c.CategoryId,
|
|
|
69 c.ProductId,
|
|
|
70 c.Quantity
|
|
|
71 FROM
|
|
|
72 Profiles p
|
|
|
73 JOIN Cart c ON c.UniqueID = p.UniqueID
|
|
|
74 WHERE
|
|
|
75 p.Username = @userName AND
|
|
|
76 p.ApplicationName = @appName AND
|
|
|
77 c.IsShoppingCart = @isShoppingCart")]
|
|
|
78 public abstract IList<CartItem> GetCartItems(string @userName, string @appName, bool @isShoppingCart);
|
|
|
79
|
|
|
80 [SqlQuery(@"
|
|
|
81 DELETE FROM Account WHERE UniqueID = @uniqueID
|
|
|
82
|
|
|
83 INSERT INTO Account (
|
|
|
84 UniqueID, Email, FirstName, LastName, Address1, Address2, City, State, Zip, Country, Phone
|
|
|
85 ) VALUES (
|
|
|
86 @uniqueID, @Email, @ToFirstName, @ToLastName, @Addr1, @Addr2, @City, @State, @Zip, @Country, @Phone
|
|
|
87 )")]
|
|
|
88 public abstract void SetAccountInfo(int @uniqueID, Address address);
|
|
|
89
|
|
|
90 // This method is not abstract as BLToolkit does not generate methods for the ExecuteForEach method.
|
|
|
91 // It's virtual as we want to get statistic info for this method.
|
|
|
92 // Counter and Log aspects wrap all abstract, virtual, and override members.
|
|
|
93 //
|
|
|
94 public virtual void SetCartItems(int uniqueID, ICollection<CartItem> cartItems, bool isShoppingCart)
|
|
|
95 {
|
|
|
96 using (DbManager db = GetDbManager())
|
|
|
97 {
|
|
|
98 db.BeginTransaction();
|
|
|
99
|
|
|
100 db
|
|
|
101 .SetCommand(@"
|
|
|
102 DELETE FROM
|
|
|
103 Cart
|
|
|
104 WHERE
|
|
|
105 UniqueID = @uniqueID AND IsShoppingCart = @isShoppingCart",
|
|
|
106 db.Parameter("@uniqueID", uniqueID),
|
|
|
107 db.Parameter("@isShoppingCart", isShoppingCart))
|
|
|
108 .ExecuteNonQuery();
|
|
|
109
|
|
|
110 if (cartItems.Count > 0)
|
|
|
111 {
|
|
|
112 db
|
|
|
113 .SetCommand(@"
|
|
|
114 INSERT INTO Cart (
|
|
|
115 UniqueID, ItemId, Name, Type, Price, CategoryId, ProductId, IsShoppingCart, Quantity
|
|
|
116 ) VALUES (
|
|
|
117 @uniqueID, @ItemId, @Name, @Type, @Price, @CategoryId, @ProductId, @isShoppingCart, @Quantity
|
|
|
118 )",
|
|
|
119 db.CreateParameters(typeof(CartItem),
|
|
|
120 db.Parameter("@uniqueID", uniqueID),
|
|
|
121 db.Parameter("@isShoppingCart", isShoppingCart)))
|
|
|
122 .ExecuteForEach(cartItems);
|
|
|
123 }
|
|
|
124
|
|
|
125 db.CommitTransaction();
|
|
|
126 }
|
|
|
127 }
|
|
|
128
|
|
|
129 [SqlQuery(@"
|
|
|
130 UPDATE
|
|
|
131 Profiles
|
|
|
132 SET
|
|
|
133 LastActivityDate = getdate()
|
|
|
134 WHERE
|
|
|
135 Username = @userName AND ApplicationName = @appName")]
|
|
|
136 public abstract void UpdateActivityDate(string @userName, string @appName);
|
|
|
137
|
|
|
138 [SqlQuery(@"
|
|
|
139 UPDATE
|
|
|
140 Profiles
|
|
|
141 SET
|
|
|
142 LastActivityDate = getdate(),
|
|
|
143 LastUpdatedDate = getdate()
|
|
|
144 WHERE
|
|
|
145 Username = @userName AND ApplicationName = @appName")]
|
|
|
146 public abstract void UpdateActivityAndUdpateDates(string @userName, string @appName);
|
|
|
147
|
|
|
148 [SqlQuery(@"DELETE FROM Profiles WHERE UniqueID = @uniqueID")]
|
|
|
149 [ScalarSource(ScalarSourceType.AffectedRows)]
|
|
|
150 public abstract int DeleteProfile(int @uniqueID);
|
|
|
151
|
|
|
152 [SqlQuery(@"
|
|
|
153 SELECT
|
|
|
154 Username
|
|
|
155 FROM
|
|
|
156 Profiles
|
|
|
157 WHERE ApplicationName = @appName AND LastActivityDate <= @userInactiveSinceDate")]
|
|
|
158 public abstract IList<string> GetInactiveProfiles(DateTime userInactiveSinceDate, string appName);
|
|
|
159
|
|
|
160 [SqlQuery(@"
|
|
|
161 SELECT
|
|
|
162 Username
|
|
|
163 FROM
|
|
|
164 Profiles
|
|
|
165 WHERE ApplicationName = @appName AND LastActivityDate <= @userInactiveSinceDate AND IsAnonymous = @isAnonymous")]
|
|
|
166 public abstract IList<string> GetInactiveProfiles(DateTime @userInactiveSinceDate, string @appName, bool @isAnonymous);
|
|
|
167
|
|
|
168 const string _profileQuery = @"
|
|
|
169 FROM
|
|
|
170 Profiles
|
|
|
171 WHERE
|
|
|
172 ApplicationName = @appName AND
|
|
|
173 (@isAnonymous IS NULL OR IsAnonymous = @isAnonymous) AND
|
|
|
174 (@userName IS NULL OR Username LIKE @userName) AND
|
|
|
175 (@userInactiveSinceDate IS NULL OR LastActivityDate >= @userInactiveSinceDate)";
|
|
|
176
|
|
|
177 [SqlQuery(@"
|
|
|
178 SELECT @totalRecords = Count(*)" + _profileQuery + @"
|
|
|
179 SELECT *" + _profileQuery)]
|
|
|
180 public abstract IList<CustomProfile> GetProfile(
|
|
|
181 bool? @isAnonymous, string @userName, DateTime? @userInactiveSinceDate, string @appName, out int @totalRecords);
|
|
|
182 }
|
|
|
183 }
|
