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