/// <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; }