public void append_event(shop_event shopEvent) { shop_event looper = firstShopEvent; if (looper == null) { firstShopEvent = shopEvent; } else { while (looper != null) { if (looper.Next == null) { looper.Next = shopEvent; return; } else if (looper == firstShopEvent && shopEvent.timestamp <= firstShopEvent.timestamp) { shopEvent.Next = firstShopEvent; firstShopEvent = shopEvent; break; } else if (shopEvent.timestamp <= looper.Next.timestamp) { shopEvent.Next = looper.Next; looper.Next = shopEvent; break; } looper = looper.Next; } } }
public int appendAllEventsForAllShops(Global global, List<backoffice.admin_shop> shopList, DateTime fromTimestamp) { int iNofEvents = 0; int iHours = -1; iHours = -global.iHours; /* COMPLETED BASKETS */ string sSql = // "select shop_id,basket_id, basket_total_sum,basket_total_discount,confirmed_by_shop,timestamp from consumer_basket cb " + "select basket_b, shop_id,cb.id basket_row_id, basket_id, basket_total_sum,basket_total_discount,confirmed_by_shop,timestamp,cp.token_id " + "from consumer_basket cb, consumer_paytool cp " + "where cb.consumer_paytool_id = cp.id and " + "timestamp BETWEEN " + "'" + buildPostgresDateFromString(fromTimestamp.AddHours(iHours)) + "'::timestamp and " + "'" + buildPostgresDateFromString(fromTimestamp) + "'::timestamp order by cb.id "; // 30 juni GLOBAL_SQL_CONN conn = new GLOBAL_SQL_CONN(this); try { GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn); GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command); while (reader.Read()) { string sShopId = reader.c("shop_id").ToString(); admin_shop currentShop = getShopFromId(shopList, sShopId); if (currentShop != null) { backoffice.shop_event shopEvent = new shop_event(); shopEvent.bConfirmedByShop = (bool)reader.c("confirmed_by_shop"); shopEvent.sDescription = reader.c("basket_id").ToString(); shopEvent.timestamp = (DateTime)reader.c("timestamp"); shopEvent.sBasketRowId = reader.c("basket_row_id").ToString(); shopEvent.sBasket_b = reader.c("basket_b").ToString(); shopEvent.sToken = (string)reader.c("token_id"); shopEvent.amount_1 = reader.c("basket_total_sum").ToString(); shopEvent.amount_2 = reader.c("basket_total_discount").ToString(); if (shopEvent.bConfirmedByShop) shopEvent.shopEventType = SHOP_EVENT_TYPE.B_BASKET_CONFIRMED; else shopEvent.shopEventType = SHOP_EVENT_TYPE.B_BASKET_NOT_CONFIRMED; ++iNofEvents; currentShop.append_event(shopEvent); } } } catch (Exception e) { e = e; } finally { conn.Close(); } /* ENROLLED PHONE OR NOT */ sSql = "select " + "s.id shop_id, parameters, c.accepted_membership_at, c.pincode_verified, " + "case " + "when sub_action like 'ecr_new%' then 'Ja' " + "when sub_action like 'ecr_ref%' then 'Nei' " + "when sub_action like 'ecr_already%' then 'Allerede medlem' " + "when sub_action like 'ecr_additional_card%' then 'Nytt kort' " + // baxid=526595;90187304;additional_card;053670260421520428 "end Oppga_mobilnummer_dim, " + "c.phone, " + "ua.timestamp tidspunkt_text, " + "ua.id ident_text " + "from user_action ua " + "left JOIN shop s ON (s.shop_external_id = substring(ua.parameters for position(';' in ua.parameters)-position('baxid=' in ua.parameters)-6 from position('baxid=' in parameters)+6) ) " + // "left JOIN consumer c ON (c.phone = split_part(ua.parameters, ';', 2)) " + "left JOIN consumer c ON (c.guid = ua.consumer_guid) " + "where (sub_action like 'ecr_refused_enrollment' or sub_action like 'ecr_new_enrollment' or sub_action like 'ecr_already%' or sub_action like 'ecr_additional_card%') " + "and ua.id > 4785 " + "and s.shop_external_id = substring(ua.parameters for position(';' in ua.parameters)-position('baxid=' in ua.parameters)-6 from position('baxid=' in parameters)+6) " + "and timestamp BETWEEN " + "'" + buildPostgresDateFromString(fromTimestamp.AddHours(iHours)) + "'::timestamp and " + "'" + buildPostgresDateFromString(fromTimestamp) + "'::timestamp " + "order by ua.id"; conn = new GLOBAL_SQL_CONN(this); try { GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn); GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command); while (reader.Read()) { string sShopId = reader.c("shop_id").ToString(); admin_shop currentShop = getShopFromId(shopList, sShopId); if (currentShop != null) { backoffice.shop_event shopEvent = new shop_event(); shopEvent.sRawParameters = (string)reader.c("parameters"); shopEvent.sPhone = (string)reader.c("phone"); shopEvent.timestamp = (DateTime)reader.c("tidspunkt_text"); // Får forskjellig klokke for de på nederste linjen !!! string sMobilYesNo = (string)reader.c("Oppga_mobilnummer_dim"); string sIsMember = (string)reader.c("pincode_verified"); shopEvent.set_accepted_membership_at(reader.c("accepted_membership_at")); // Later we will join relations based on token ... if (sMobilYesNo == "Ja") { // "baxid=526588;41293984;enrolled;335475343524073400" shopEvent.shopEventType = SHOP_EVENT_TYPE.A_PHONE_ENROLLED; try { string[] parTab = shopEvent.sRawParameters.Split(";".ToArray()); shopEvent.sBaxId = parTab[0].Split("=".ToCharArray())[1]; shopEvent.sPhone = parTab[1]; shopEvent.sToken = parTab[3]; } catch (Exception) { } } else if (sMobilYesNo == "Nei") { // sRawParameters = "baxid=bbb;ttt;" shopEvent.shopEventType = SHOP_EVENT_TYPE.A_PHONE_SKIPPED; try { string[] parTab = shopEvent.sRawParameters.Split(";".ToCharArray()); shopEvent.sBaxId = parTab[0].Split("=".ToCharArray())[1]; shopEvent.sToken = parTab[1]; if (shopEvent.sToken == "053670260421520428") { int t = 0; } } catch (Exception) { } } else if (sMobilYesNo == "Allerede medlem") { // sRawParameters = "baxid=bbb;token=ttt;" if (sIsMember == "yes") { if (shopEvent.timestamp >= shopEvent.accepted_membership_at && shopEvent.accepted_membership_at.Year > 2001) shopEvent.shopEventType = SHOP_EVENT_TYPE.A_CONSUMER_EXISTS_AND_IS_MEMBER; else shopEvent.shopEventType = SHOP_EVENT_TYPE.A_CONSUMER_EXISTS; // Not member before after ... Have to fix registration data here } else shopEvent.shopEventType = SHOP_EVENT_TYPE.A_CONSUMER_EXISTS; try { string[] parTab = shopEvent.sRawParameters.Split(";".ToCharArray()); shopEvent.sBaxId = parTab[0].Split("=".ToCharArray())[1]; shopEvent.sToken = parTab[1].Split("=".ToCharArray())[1]; } catch (Exception) { } } else if (sMobilYesNo == "Nytt kort") { // sRawParameters : baxid=526595;90187304;additional_card;053670260421520428 if (sIsMember == "yes") shopEvent.shopEventType = SHOP_EVENT_TYPE.A_CONSUMER_EXISTS_AND_IS_MEMBER; else shopEvent.shopEventType = SHOP_EVENT_TYPE.A_CONSUMER_EXISTS; try { string[] parTab = shopEvent.sRawParameters.Split(";".ToArray()); shopEvent.sBaxId = parTab[0].Split("=".ToCharArray())[1]; shopEvent.sToken = parTab[3]; shopEvent.bAdditionalCard = true; } catch (Exception) { } } ++iNofEvents; currentShop.append_event(shopEvent); } } } catch (Exception e) { } finally { conn.Close(); } /* ***** ACCEPTED AGREEMENT */ sSql = "select " + "c.id consumer_id, " + "s.id shop_id, " + "c.accepted_membership_at, " + "cp.token_id " + "from consumer c, shop s, consumer_paytool cp " + "where " + "cp.consumer_id = c.id " + "and c.pincode_verified = 'yes' " + "and c.enrolled_by_shop_id = s.id " + "and length(c.phone) > 2 and length(c.phone) < 11 " + "and c.accepted_membership_at BETWEEN " + "'" + buildPostgresDateFromString(fromTimestamp.AddHours(iHours)) + "'::timestamp and " + "'" + buildPostgresDateFromString(fromTimestamp) + "'::timestamp " + "order by c.id"; conn = new GLOBAL_SQL_CONN(this); try { GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn); GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command); string sLastConsumerId = ""; backoffice.shop_event lastShopEvent = null; // We can get more than one row for each consumer since we join with consumer_paytool. Therefore DO NOT make more than one membership_accepted-event while (reader.Read()) { string sConsumerId = reader.c("consumer_id").ToString(); string sShopId = reader.c("shop_id").ToString(); string sToken = reader.c("token_id").ToString(); admin_shop currentShop = getShopFromId(shopList, sShopId); if (currentShop != null) { if (sConsumerId != sLastConsumerId) { backoffice.shop_event shopEvent = new shop_event(); shopEvent.timestamp = (DateTime)reader.c("accepted_membership_at"); shopEvent.shopEventType = SHOP_EVENT_TYPE.C_MEMBERSHIP_ACCEPTED; shopEvent.sToken = sToken; ++iNofEvents; lastShopEvent = shopEvent; currentShop.append_event(shopEvent); } else { lastShopEvent.sToken += ";" + sToken; // 3 juli TODO (use this match whne testing later ... } } sLastConsumerId = sConsumerId; } } catch (Exception e) { } finally { conn.Close(); } return iNofEvents; }