/// <summary>
/// Gets a dictionary of ordermappings for this connectster user from the database.
/// </summary>
/// <param name="user"></param>
/// <returns>A dictionary <int ShopifyOrderId, int ShopsterOrderId> meaning ShopifyOrderId was created in shopster's system as ShopsterOrderId</returns>
internal Dictionary<int, int> SelectOrderMappingsForUser(ConnectsterUser user)
{
Dictionary<int, int> returnMap;
string query = String.Format(
@"SELECT ShopifyOrderId, ShopsterOrderId
FROM connectsterOrder
WHERE shopsterUserId = {0} and ShopifyAccountDomain = '{1}'
;",
user.ShopsterUser, user.ShopifyUser);
var sqlCommand = new MySqlCommand(query, _dbConn);
using (var sqlResult = sqlCommand.ExecuteReader())
{
returnMap = new Dictionary<int, int>();
while (sqlResult.Read())
{
try
{
returnMap.Add(Convert.ToInt32(sqlResult[0]), Convert.ToInt32(sqlResult[1]));
}
catch (FormatException fmtEx)
{
Logger.ErrorFormat(
"SelectOrderMappingsForUser():FormatException User is ({0},{1}), Message: {2}",
user.ShopsterUser, user.ShopifyUser, fmtEx.Message);
continue;
}
catch (OverflowException e)
{
Logger.ErrorFormat(
"SelectOrderMappingsForUser():OverflowException User is ({0},{1}), Message: {2}",
user.ShopsterUser, user.ShopifyUser, e.Message);
continue;
}
}
return returnMap;
}
//Failure if we reach here.
return null;
}
public ShopsterUser SelectShopsterUserDetails(ConnectsterUser user)
{
string query = String.Format(
@"SELECT su.id, su.AuthToken, su.AuthSecret, su.ShopsterAccountType
FROM ShopsterUser su JOIN ConnectsterUserMap smap on smap.ShopsterUserId = su.id
WHERE smap.ShopsterUserId = {0} and smap.ShopifySubdomain = '{1}';",
user.ShopsterUser, user.ShopifyUser);
var sqlCommand = new MySqlCommand(query, _dbConn);
using (var sqlResult = sqlCommand.ExecuteReader())
{
while (sqlResult.Read())
{
return new ShopsterUser(Convert.ToInt32(sqlResult[0]), (string) sqlResult[1], (string) sqlResult[2],
Convert.ToInt32(sqlResult[3]));
}
return null; // no results
}
}
/// <summary>
/// Create and order mapping, also updates on duplicate keys.
/// </summary>
/// <param name="user"></param>
/// <param name="shopifyOrder"></param>
/// <param name="shopsterOrderId"></param>
/// <returns></returns>
internal bool CreateOrderMapping(ConnectsterUser user, ShopifyOrder shopifyOrder, int shopsterOrderId)
{
if (user == null || shopifyOrder == null || shopsterOrderId < 0)
{
throw new ArgumentException("User and shopifyOrder cannot be null. shopsterOrderId must be > 0 ");
}
string query = String.Format(
@"INSERT
INTO connectsterorder (ShopsterOrderId, ShopifyOrderId, ShopsterUserId, ShopifyAccountDomain , ShopsterVersion, ShopifyVersion)
VALUES ({0},{1},{2},'{3}', '{4:yyyy-MM-dd HH:mm:ss}', '{5:yyyy-MM-dd HH:mm:ss}')
ON DUPLICATE KEY UPDATE shopsterVersion = '{4:yyyy-MM-dd HH:mm:ss}' , shopifyVersion = '{5:yyyy-MM-dd HH:mm:ss}'
;",
shopsterOrderId, shopifyOrder.Id, user.ShopsterUser, user.ShopifyUser, shopifyOrder.UpdatedAt,
DateTime.UtcNow);
var sqlCommand = new MySqlCommand(query, _dbConn);
try
{
int result;
if ((result = sqlCommand.ExecuteNonQuery()) == 1 || result == 2) //Inserted (1) or updated(2)
{
return true;
}
#if DEBUG
Logger.DebugFormat("CreateOrderMapping(): Failed to CreateOrderMapping Query was {0}", query);
#endif
return false;
}
catch (Exception dbEx)
{
Logger.ErrorFormat(
"CreateOrderMapping(): Database exception while attempting to insert/update connectsterOrder. ShopifyOrder is ({0}) with timestamp ({1}). " +
dbEx.Message, shopifyOrder.Id, shopifyOrder.UpdatedAt);
return false;
}
}
public ShopifyStoreAuth SelectShopifyUserDetails(ConnectsterUser user)
{
string query = String.Format(
@" SELECT su.AuthToken,su.SubDomain
FROM ShopifyUser su JOIN ConnectsterUserMap smap on smap.ShopifySubdomain = su.subdomain
WHERE smap.ShopsterUserId = {0} and smap.ShopifySubdomain = '{1}';",
user.ShopsterUser, user.ShopifyUser);
var sqlCommand = new MySqlCommand(query, _dbConn);
using (var sqlResult = sqlCommand.ExecuteReader())
{
while (sqlResult.Read())
{
return new ShopifyStoreAuth((string) sqlResult[0], (string) sqlResult[1]);
}
}
return null;
}
public List<ConnectsterProduct> SelectProductForUser(ConnectsterUser user)
{
return SelectProductForUser(user.ShopsterUser, user.ShopifyUser);
}
public List<ConnectsterUser> SelectAllUsers()
{
var returnList = new List<ConnectsterUser>();
const string query =
"SELECT sm.ShopsterUserId, sm.ShopifySubdomain, sm.sleepUntil FROM ConnectsterUserMap AS sm "
+ "JOIN ShopifyUser AS shopifyU on sm.ShopifySubdomain = shopifyU.subdomain "
+ "WHERE shopifyU.dateDisabled is null;";
var sqlCommand = new MySqlCommand(query, _dbConn);
using (var sqlResult = sqlCommand.ExecuteReader())
{
while (sqlResult.Read())
{
try //For the Convert.ToInt32 , also so we can continue reading and get as many users as possible.
{
DateTime time = sqlResult[2] == DBNull.Value
? DateTime.MinValue.ToUniversalTime()
: Convert.ToDateTime(sqlResult[2]).ToUniversalTime();
var tempUser = new ConnectsterUser(Convert.ToInt32(sqlResult[0]),
(string) sqlResult[1], time);
returnList.Add(tempUser);
}
catch (FormatException e)
{
Logger.DebugFormat(
"SelectAllUsers(): FormatException for returned userId({0}) or possibly sleepUntil DateTime. Exception Message is {1}",
sqlResult[0], e.Message);
continue; //Just abandon this user and move on.
}
catch (OverflowException e)
{
Logger.DebugFormat(
"SelectAllUsers(): OverflowException for returned userId({0}) or possibly sleepUntil DateTime. Exception Message is {1}",
sqlResult[0], e.Message);
continue;
}
}
}
return returnList;
}
public bool InsertProductForUser(ConnectsterUser user, InventoryItemType shopsterProduct,
ShopifyProduct shopifyProduct)
{
//TODO: have this take a bool to decide if shopster is master, for now true
//Todo: have actual dates from Shopster API, when available.
return InsertProductForUser(user, Convert.ToInt32(shopsterProduct.ItemId), (DateTime.Now.ToUniversalTime()),
(int) shopifyProduct.Id,
((DateTime) shopifyProduct.Variants[0].UpdatedAt).ToUniversalTime());
}
public bool InsertProductForUser(ConnectsterUser user, int shopsterItemId, DateTime shopsterVersionDate,
int shopifyItemId, DateTime shopifyVersionDate)
{
DateTime insertTime = DateTime.Now.ToUniversalTime();
string query =
String.Format(
" CALL InsertConnectsterProduct({0},'{1:yyyy-MM-dd HH:mm:ss}',{2},{3},'{4}','{5:yyyy-MM-dd HH:mm:ss}',{6}); ",
user.ShopsterUser, insertTime, shopsterItemId, shopifyItemId, user.ShopifyUser, insertTime, true);
var sqlCommand = new MySqlCommand(query, _dbConn);
using (var sqlResult = sqlCommand.ExecuteReader())
{
if (sqlResult.Read())
{
if (Convert.ToInt32(sqlResult[0]) == 1 && Convert.ToInt32(sqlResult[1]) == 1 &&
Convert.ToInt32(sqlResult[2]) == 1)
{
return true;
}
}
}
return false;
}
