private static OrderConfirmation GetFromReader(SqlDataReader reader)
{
return(new OrderConfirmation
{
CustomerId = SQLDataHelper.GetGuid(reader, "CustomerId"),
OrderConfirmationData = JsonConvert.DeserializeObject <OrderConfirmationData>(SQLDataHelper.GetString(reader, "OrderConfirmationData")),
LastUpdate = SQLDataHelper.GetDateTime(reader, "LastUpdate", DateTime.MinValue)
});
}
/// <summary>
/// 获取用户信息
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
private UserModel GetUserModel(SqlDataReader dr)
{
UserModel model = new UserModel();
model.ID = SQLDataHelper.GetGuid(dr, "ID");
model.OpenID = SQLDataHelper.GetString(dr, "OpenID");
model.NickName = SQLDataHelper.GetString(dr, "NickName");
model.Createtime = SQLDataHelper.GetString(dr, "Createtime");
model.Modifytime = SQLDataHelper.GetString(dr, "Modifytime");
model.Disabled = SQLDataHelper.GetInt(dr, "Disabled");
return(model);
}
private static AbandonedCart GetOrderConfirmationFromReader(SqlDataReader reader)
{
return(new AbandonedCart
{
CustomerId = SQLDataHelper.GetGuid(reader, "CustomerId"),
OrderConfirmationData =
JsonConvert.DeserializeObject <OrderConfirmationData>(SQLDataHelper.GetString(reader, "OrderConfirmationData")),
LastUpdate = SQLDataHelper.GetDateTime(reader, "LastUpdate", DateTime.MinValue),
SendingCount = SQLDataHelper.GetInt(reader, "SendingCount"),
SendingDate = SQLDataHelper.GetNullableDateTime(reader, "SendingDate")
});
}
private static ShoppingCartItem GetFromReader(SqlDataReader reader)
{
return(new ShoppingCartItem
{
ShoppingCartItemId = SQLDataHelper.GetInt(reader, "ShoppingCartItemId"),
ShoppingCartType = (ShoppingCartType)SQLDataHelper.GetInt(reader, "ShoppingCartType"),
OfferId = SQLDataHelper.GetInt(reader, "OfferID"),
CustomerId = SQLDataHelper.GetGuid(reader, "CustomerId"),
AttributesXml = SQLDataHelper.GetString(reader, "AttributesXml"),
Amount = SQLDataHelper.GetFloat(reader, "Amount"),
CreatedOn = SQLDataHelper.GetDateTime(reader, "CreatedOn"),
UpdatedOn = SQLDataHelper.GetDateTime(reader, "UpdatedOn"),
});
}
public static List <AbandonedCartLetter> GetAllLetters()
{
var list = ModulesRepository.ModuleExecuteReadList(
"Select * From [Module].[AbandonedCartLetter]", CommandType.Text,
reader => new AbandonedCartLetter()
{
CustomerId = SQLDataHelper.GetGuid(reader, "CustomerId"),
TemplateId = SQLDataHelper.GetInt(reader, "TemplateId"),
Email = SQLDataHelper.GetString(reader, "Email"),
SendingDate = SQLDataHelper.GetDateTime(reader, "SendingDate")
});
return(list ?? new List <AbandonedCartLetter>());
}
public static List <AbandonedCartLetter> GetLetters(Guid customerId)
{
return
(ModulesRepository.ModuleExecuteReadList(
"Select * From [Module].[AbandonedCartLetter] Where CustomerId = @CustomerId", CommandType.Text,
reader => new AbandonedCartLetter()
{
CustomerId = SQLDataHelper.GetGuid(reader, "CustomerId"),
TemplateId = SQLDataHelper.GetInt(reader, "TemplateId"),
Email = SQLDataHelper.GetString(reader, "Email"),
SendingDate = SQLDataHelper.GetDateTime(reader, "SendingDate")
},
new SqlParameter("CustomerId", customerId)));
}
private static Review GetFromReader(SqlDataReader reader)
{
return new Review
{
ReviewId = SQLDataHelper.GetInt(reader, "ReviewId"),
ParentId = SQLDataHelper.GetNullableInt(reader, "ParentId") ?? 0,
EntityId = SQLDataHelper.GetInt(reader, "EntityId"),
CustomerId = SQLDataHelper.GetGuid(reader, "CustomerId"),
Name = SQLDataHelper.GetString(reader, "Name"),
Email = SQLDataHelper.GetString(reader, "Email"),
Text = SQLDataHelper.GetString(reader, "Text"),
Checked = SQLDataHelper.GetBoolean(reader, "Checked"),
AddDate = SQLDataHelper.GetDateTime(reader, "AddDate"),
Ip = SQLDataHelper.GetString(reader, "IP")
};
}
/// <summary>
/// 获取心情信息
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
private MoodModl GetMoodModel(SqlDataReader dr)
{
MoodModl model = new MoodModl();
model.ID = SQLDataHelper.GetGuid(dr, "ID");
model.OpenID = SQLDataHelper.GetString(dr, "OpenID");
model.NickName = SQLDataHelper.GetString(dr, "NickName");
model.Mood = SQLDataHelper.GetString(dr, "Mood");
model.City = SQLDataHelper.GetString(dr, "City");
model.Weather = SQLDataHelper.GetString(dr, "Weather");
model.Createtime = SQLDataHelper.GetString(dr, "Createtime");
model.Modifytime = SQLDataHelper.GetString(dr, "Modifytime");
model.Disabled = SQLDataHelper.GetInt(dr, "Disabled");
model.IsPublic = SQLDataHelper.GetInt(dr, "IsPublic");
return(model);
}
/// <summary>
/// 获取日记信息
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
private DiaryModel GetDiaryModel(SqlDataReader dr)
{
DiaryModel model = new DiaryModel();
model.ID = SQLDataHelper.GetGuid(dr, "ID");
model.OpenID = SQLDataHelper.GetString(dr, "OpenID");
model.NickName = SQLDataHelper.GetString(dr, "NickName");
model.DiaryContent = SQLDataHelper.GetString(dr, "DiaryContent");
model.City = SQLDataHelper.GetString(dr, "City");
model.Weather = SQLDataHelper.GetString(dr, "Weather");
model.Createtime = SQLDataHelper.GetString(dr, "Createtime");
model.Modifytime = SQLDataHelper.GetString(dr, "Modifytime");
model.Disabled = SQLDataHelper.GetInt(dr, "Disabled");
model.SortID = SQLDataHelper.GetInt(dr, "SortID");
model.IsPublic = SQLDataHelper.GetInt(dr, "IsPublic");
return(model);
}
public static Guid AddContact(CustomerContact contact, Guid customerId)
{
var id = SQLDataAccess.ExecuteScalar("[Customers].[sp_AddCustomerContact]",
CommandType.StoredProcedure,
new SqlParameter("@CustomerID", customerId),
new SqlParameter("@Name", contact.Name),
new SqlParameter("@Country", contact.Country),
new SqlParameter("@City", contact.City),
new SqlParameter("@Zone", contact.RegionName),
new SqlParameter("@Address", contact.Address),
new SqlParameter("@Zip", contact.Zip),
new SqlParameter("@CountryID", contact.CountryId),
new SqlParameter("@RegionID",
contact.RegionId.HasValue && contact.RegionId > 0 ? contact.RegionId : (object)DBNull.Value));
return(SQLDataHelper.GetGuid(id));
}
public static Customer GetFromSqlDataReader(SqlDataReader reader)
{
var customer = new Customer
{
Id = SQLDataHelper.GetGuid(reader, "CustomerID"),
CustomerGroupId = SQLDataHelper.GetInt(reader, "CustomerGroupId", 0),
EMail = SQLDataHelper.GetString(reader, "EMail"),
FirstName = SQLDataHelper.GetString(reader, "FirstName"),
LastName = SQLDataHelper.GetString(reader, "LastName"),
RegistrationDateTime = SQLDataHelper.GetDateTime(reader, "RegistrationDateTime"),
SubscribedForNews = SQLDataHelper.GetBoolean(reader, "Subscribed4News"),
Phone = SQLDataHelper.GetString(reader, "Phone"),
Password = SQLDataHelper.GetString(reader, "Password"),
CustomerRole = (Role)SQLDataHelper.GetInt(reader, "CustomerRole")
};
return(customer);
}
public static CustomerContact GetContactFromSqlDataReader(SqlDataReader reader)
{
var contact = new CustomerContact
{
CustomerContactID = SQLDataHelper.GetGuid(reader, "ContactID"),
Address = SQLDataHelper.GetString(reader, "Address"),
City = SQLDataHelper.GetString(reader, "City"),
Country = SQLDataHelper.GetString(reader, "Country"),
Name = SQLDataHelper.GetString(reader, "Name"),
Zip = SQLDataHelper.GetString(reader, "Zip"),
RegionName = SQLDataHelper.GetString(reader, "Zone"),
CountryId = SQLDataHelper.GetInt(reader, "CountryID"),
RegionId = SQLDataHelper.GetNullableInt(reader, "RegionID"),
CustomerGuid = SQLDataHelper.GetGuid(reader, "CustomerID")
};
return(contact);
}
public static Customer GetFromSqlDataReader(SqlDataReader reader)
{
var customer = new Customer
{
Id = SQLDataHelper.GetGuid(reader, "CustomerID"),
CustomerGroupId = SQLDataHelper.GetInt(reader, "CustomerGroupId", 0),
EMail = SQLDataHelper.GetString(reader, "EMail"),
FirstName = SQLDataHelper.GetString(reader, "FirstName"),
LastName = SQLDataHelper.GetString(reader, "LastName"),
Patronymic = SQLDataHelper.GetString(reader, "Patronymic"),
RegistrationDateTime = SQLDataHelper.GetDateTime(reader, "RegistrationDateTime"),
Phone = SQLDataHelper.GetString(reader, "Phone"),
Password = SQLDataHelper.GetString(reader, "Password"),
CustomerRole = (Role)SQLDataHelper.GetInt(reader, "CustomerRole"),
BonusCardNumber = SQLDataHelper.GetNullableLong(reader, "BonusCardNumber")
};
return(customer);
}
public static ShoppingCartItem GetExistsShoppingCartItem(Guid customerId, int offerId, string attributesXml, ShoppingCartType shoppingCartType)
{
return
(SQLDataAccess.ExecuteReadOne(
"SELECT * FROM [Catalog].[ShoppingCart] WHERE [CustomerId] = @CustomerId AND [OfferId] = @OfferId AND [ShoppingCartType] = @ShoppingCartType AND [AttributesXml] = @AttributesXml",
CommandType.Text,
(reader) => new ShoppingCartItem
{
ShoppingCartItemId = SQLDataHelper.GetInt(reader, "ShoppingCartItemId"),
ShoppingCartType = (ShoppingCartType)SQLDataHelper.GetInt(reader, "ShoppingCartType"),
OfferId = SQLDataHelper.GetInt(reader, "OfferID"),
CustomerId = SQLDataHelper.GetGuid(reader, "CustomerId"),
AttributesXml = SQLDataHelper.GetString(reader, "AttributesXml"),
Amount = SQLDataHelper.GetFloat(reader, "Amount"),
CreatedOn = SQLDataHelper.GetDateTime(reader, "CreatedOn"),
UpdatedOn = SQLDataHelper.GetDateTime(reader, "UpdatedOn"),
},
new SqlParameter("@CustomerId", customerId),
new SqlParameter("@OfferId", offerId),
new SqlParameter("@AttributesXml", attributesXml ?? String.Empty),
new SqlParameter("@ShoppingCartType", (int)shoppingCartType)));
}
public static List <string> GetCustomersIDs()
{
List <string> result = SQLDataAccess.ExecuteReadList <string>("SELECT [CustomerID] FROM [Customers].[Customer]",
CommandType.Text, reader => SQLDataHelper.GetGuid(reader, "CustomerID").ToString());
return(result);
}
public List <Guid> ItemsUnionIds(string idName)
{
//get
//{
var cmd = new List <SqlParameter>();
string seachConditionUnionTable = string.Empty;
if (!string.IsNullOrEmpty(TableNameForUnion))
{
bool first = true;
foreach (Field f in FieldsUnionTable.Values)
{
if (f.Filter != null)
{
if (first)
{
seachConditionUnionTable = "WHERE ";
first = false;
}
else
{
seachConditionUnionTable += " AND ";
}
seachConditionUnionTable += f.Filter.GetSqlCondition(f.FilterExpression);
f.Filter.SetSqlConditionParameter(cmd);
}
}
}
string query = string.Empty;
if (!string.IsNullOrEmpty(TableNameForUnion))
{
query = "SELECT " + idName + " FROM " + TableNameForUnion + seachConditionUnionTable;
}
if (_listParam != null)
{
cmd.AddRange(_listParam.Select(param => new SqlParameter {
ParameterName = param.ParamName, Value = param.ParamValue
}));
}
var itemsIds = new List <Guid>();
if (string.IsNullOrEmpty(query))
{
return(itemsIds);
}
itemsIds = SQLDataAccess.ExecuteReadList(query, CommandType.Text, reader => SQLDataHelper.GetGuid(reader, "ID"), cmd.ToArray());
return(itemsIds);
//}
}
public List <Guid> ItemsIds(string idName)
{
//get
//{
var cmd = new List <SqlParameter>();
var searchCondition = string.Empty;
bool first = true;
foreach (var f in Fields.Values)
{
if (f.Filter != null)
{
if (first)
{
searchCondition = " WHERE ";
first = false;
}
else
{
searchCondition += " AND ";
}
searchCondition += f.Filter.GetSqlCondition(f.FilterExpression);
f.Filter.SetSqlConditionParameter(cmd);
}
}
if (!string.IsNullOrWhiteSpace(ExtensionWhere))
{
searchCondition += " " + ExtensionWhere;
}
string query = "SELECT " + idName + " FROM " + TableName + searchCondition;
if (_listParam != null)
{
cmd.AddRange(_listParam.Select(param => new SqlParameter {
ParameterName = param.ParamName, Value = param.ParamValue
}));
}
List <Guid> itemsIds = SQLDataAccess.ExecuteReadList(query, CommandType.Text, reader => SQLDataHelper.GetGuid(reader, "ID"), cmd.ToArray());
return(itemsIds);
//}
}