public List<admin_ws_log> get_last_rows_from_log(int iLimit, string sMerchantId) { StringBuilder sb = new StringBuilder(); List<admin_ws_log> wsList = new List<admin_ws_log>(); if (isBlank(sMerchantId)) return wsList; string sSql = "select id,timestamp from webservice_log where parameters like '%BaxNumber=%" + sMerchantId + "%' order by id desc limit " + iLimit.ToString(); 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()) { admin_ws_log ws = new admin_ws_log(); ws.iId = (int)reader.c("id"); ws.sTimestamp = reader.c("timestamp").ToString(); wsList.Add(ws); } } catch (Exception e) { wsList = null; } finally { conn.Close(); } return wsList; }
public List<dim_dim_value> dash_maxi_month_member(int iYear, string sChainId) { List<dim_dim_value> monthList = new List<dim_dim_value>(); int iLastYear = iYear - 1; // Default = this year and the year before, which always will be enough ... // Mountain Dashboard 004 string sSql = "select EXTRACT(YEAR FROM accepted_membership_at) accepted_year, EXTRACT(MONTH FROM accepted_membership_at) accepted_month, count(*) year_month_value " + "from consumer c, shop s " + "where " + "s.parent_shop = " + sChainId + " " + "and " + "c.enrolled_by_shop_id = s.id " + "and " + "pincode_verified='yes' and " + "(EXTRACT(YEAR FROM accepted_membership_at)=" + iLastYear + " or EXTRACT(YEAR FROM accepted_membership_at)=" + iYear.ToString() + ") " + "group by EXTRACT(YEAR FROM accepted_membership_at), EXTRACT(MONTH FROM accepted_membership_at) " + "order by EXTRACT(YEAR FROM accepted_membership_at), EXTRACT(MONTH FROM accepted_membership_at) "; 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()) { dim_dim_value month = new dim_dim_value(); month.sDim_1 = reader.c("accepted_year").ToString(); month.sDim_2 = reader.c("accepted_month").ToString(); month.sValue = reader.c("year_month_value").ToString(); monthList.Add(month); } } catch (Exception e) { monthList = null; } finally { conn.Close(); } return monthList; }
public bool loginAndSetAccessLevel(string sUserName, string sPassword) { if (isBlank(sUserName) || isBlank(sPassword)) { accessLevel = HI_ACCESS_LEVEL.NOT_LOGGED_IN; return false; } bool bLoggedIn = false; GLOBAL_SQL_CONN conn = new GLOBAL_SQL_CONN(this); try { string sEncrypted = Dinfordel.Utils.CryptUtils.EncryptPassword(sUserName, sPassword); string sSql = "select a.shop_id from administrator a where a.email='" + sUserName + "' and a.password='******' "; // b) Aministratorlogin. Få tak i shop_id med sMerchantId ... GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn); GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command); if (reader.Read()) { sAdminChain = reader.c("shop_id").ToString(); bLoggedIn = true; // Get shop level ... } } catch (Exception e) { bLoggedIn = false; } finally { conn.Close(); } if (!bLoggedIn) { accessLevel = HI_ACCESS_LEVEL.NOT_LOGGED_IN; } // Top ? ShopParentChild shopParentChild = getShopParentChildFrom(this, sAdminChain); if (shopParentChild.bOnTop) { accessLevel = HI_ACCESS_LEVEL.LOGGED_IN_AS_SUPERUSER; bOriginalSuperUser = true; setCurrentChain(sAdminChain); return true; } // Chain ? shopParentChild = getShopParentChildFrom(this, shopParentChild.sParentId); if (shopParentChild.bOnTop) { accessLevel = HI_ACCESS_LEVEL.LOGGED_IN_AS_CHAIN; sCurrentChainId = sAdminChain; setChainEntity(); return true; } // Shop ? shopParentChild = getShopParentChildFrom(this, shopParentChild.sParentId); if (shopParentChild.bOnTop) { accessLevel = HI_ACCESS_LEVEL.LOGGED_IN_AS_SHOP; return true; } return false; }
public List<dim_value> dash_maxi_month_member_old(int iYear, string sChainId) { List<dim_value> monthList = new List<dim_value>(); // Mountain dashboard 008 string sSql = "select EXTRACT(MONTH FROM accepted_membership_at) accepted_month, count(*) month_value " + "from consumer c, shop s " + "where " + "s.parent_shop=" + sChainId + " and " + "s.id = c.enrolled_by_shop_id and " + "pincode_verified='yes' " + "and " + "EXTRACT(YEAR FROM accepted_membership_at)=" + iYear.ToString() + " " + "group by EXTRACT(MONTH FROM accepted_membership_at) " + "order by EXTRACT(MONTH FROM accepted_membership_at) "; 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()) { dim_value month = new dim_value(); month.sDim = reader.c("accepted_month").ToString(); month.sValue = reader.c("month_value").ToString(); monthList.Add(month); } } catch (Exception e) { monthList = null; } finally { conn.Close(); } return monthList; }
public List<admin_basket> get_last_baskets(int iLimit, string sShopId) { StringBuilder sb = new StringBuilder(); List<admin_basket> basketList = new List<admin_basket>(); string sSql = "select id, basket_id, basket_total_sum,basket_total_discount,confirmed_by_shop,timestamp from consumer_basket cb where shop_id = " + sShopId + " order by id desc limit " + iLimit.ToString(); 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()) { admin_basket basket = new admin_basket(); basket.iId = (int)reader.c("id"); basket.sBasketId = reader.c("basket_id").ToString(); basket.sTotalSum = reader.c("basket_total_sum").ToString(); basket.sTotalDiscount = reader.c("basket_total_discount").ToString(); basket.sConfirmedByShop = reader.c("confirmed_by_shop").ToString(); if (basket.sConfirmedByShop.ToUpper() == "TRUE") basket.sConfirmedByShop = "Ja"; else if (basket.sConfirmedByShop.ToUpper() == "FALSE") basket.sConfirmedByShop = "Nei"; else basket.sConfirmedByShop = "?"; basket.sTimestamp = reader.c("timestamp").ToString(); basketList.Add(basket); } } catch (Exception e) { basketList = null; } finally { conn.Close(); } return basketList; }
public List<chain_shop_standard_report_item> fill_member_count_report(string sChainId, List<chain_shop_standard_report_item> shopList, DateTime startDate, DateTime endDate, bool bIsMember, int iCol) { StringBuilder sb = new StringBuilder(); string sMemberLine = " "; if (bIsMember) { sMemberLine = " pincode_verified='yes' " + " and " + " c.accepted_membership_at >= '" + buildPostgresDateFromStringDayPrecision(startDate) + "' " + "and " + "c.accepted_membership_at <= '" + buildPostgresDateFromStringDayPrecision(endDate) + "' "; } else { sMemberLine = " pincode_verified='no' " + " and " + " c.created_at >= '" + buildPostgresDateFromStringDayPrecision(startDate) + "' " + "and " + "c.created_at <= '" + buildPostgresDateFromStringDayPrecision(endDate) + "' "; } string sSql = "select s.id shop_id, count(*) count_number " + "from consumer c, shop s " + "where " + "s.id = c.enrolled_by_shop_id " + "and " + sMemberLine + "and " + "s.parent_shop=" + sChainId + " " + "group by enrolled_by_shop_id, s.id "; 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()) { int iShopId = (int)reader.c("shop_id"); chain_shop_standard_report_item shop = getReportShopFromId(shopList, iShopId.ToString()); if (shop != null) { int iCountNumber = Convert.ToInt32(reader.c("count_number").ToString()); if (iCol == 1) shop.iNewMembersThisYear = iCountNumber; else if (iCol == 2) shop.iNewMembersInPeriod = iCountNumber; else if (iCol == 3) shop.iNewPotentialMembersThisYear = iCountNumber; else if (iCol == 4) shop.iNewPotentialMembersThisPeriod = iCountNumber; } } } catch (Exception e) { shopList = null; } finally { conn.Close(); } return shopList; }
public List<campaign_consumer> get_campaign_week_36_consumers() { // Used for school start campaign week 36 StringBuilder sb = new StringBuilder(); List<campaign_consumer> consumerList = new List<campaign_consumer>(); // buildPostgresDateFromStringDayPrecision(startDate) string sSql = "select c.phone, c.accepted_membership_at, c.enrolled_by_shop_id, s.name " + "from consumer c, shop s " + "where " + "c.pincode_verified='yes' " + "and " + "(c.phone like '9%' or c.phone like '4%') " + "and " + "c.accepted_membership_at > '2015-01-01' " + "and " + "c.enrolled_by_shop_id = s.id " + "order by c.accepted_membership_at"; 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()) { campaign_consumer campCons = new campaign_consumer(); campCons.sPhone = "12345678"; // campCons.sPhone = (string)reader.c("phone"); campCons.memberSince = (DateTime)reader.c("accepted_membership_at"); campCons.EnrolledByShopId = (int)reader.c("enrolled_by_shop_id"); campCons.EnrolledByShop = (string)reader.c("name"); consumerList.Add(campCons); } } catch (Exception e) { consumerList = null; } finally { conn.Close(); } return consumerList; }
public List<backoffice.dim_dim_value> verifone_to_webservice(DateTime checkDate) { List<backoffice.dim_dim_value> returnList = new List<dim_dim_value>(); GLOBAL_SQL_CONN conn = new GLOBAL_SQL_CONN(this); string sXml = ""; int iMaxSeconds = -10000; int iMinSeconds = 10000; int i = 0; DateTime ss = getDateTime_from_string("2015.09.12 09:40:24 534"); string sVerifoneTimeStampRouter = ""; string sVerifoneTimeStampRouter_1 = ""; string sVerifoneTimeStampRouter_2 = ""; string sVerifoneTimeStamp = ""; string sVerifoneTimeStamp_1 = ""; string sVerifoneTimeStamp_2 = ""; try { // alter table webservice_log add column dt_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT to_timestamp(0) // alter table webservice_log add column response_time int default 0 string sSql = "select * from webservice_log where " + "(name like '%GetCustomerData%' and " + "parameters like '%_GetCustomerData_%') or " + "(name like '%EnrollCustomer%' and " + "parameters like '%_EnrollCustomer_%') " + "and id > 197000 order by id desc"; GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn); GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command); while (reader.Read()) { sXml = reader.c("parameters").ToString(); XmlDocument doc = new XmlDocument(); sXml = sXml.Replace("PROSESSOR_VAS_NO", "PROSESSOR.VAS.NO"); sXml = sXml.Replace("VAS_PROSESSOR", "VAS.PROSESSOR"); sXml = sXml.Replace("VAS_Name_1", "VAS.Name.1"); sXml = sXml.Replace("_", "'"); sXml = sXml.Replace("<", "<"); sXml = sXml.Replace(">", ">"); sXml = sXml.Replace(""", "'"); try { if (sXml.IndexOf("RequestHeader") >= 0) { doc.LoadXml(sXml); XmlNode requestInfo = doc.SelectSingleNode("GetCustomerData/RequestHeader/RequestInfo"); if (requestInfo == null) requestInfo = doc.SelectSingleNode("EnrollCustomer/RequestHeader/RequestInfo"); sVerifoneTimeStampRouter = getSafeAttribute(requestInfo, "TimeStampRouter"); sVerifoneTimeStampRouter_1 = sVerifoneTimeStampRouter.Substring(0, 10); sVerifoneTimeStampRouter_2 = sVerifoneTimeStampRouter.Substring(11, 8); DateTime verifoneTimestamp = getDateTime_from_string(sVerifoneTimeStampRouter_1 + " " + sVerifoneTimeStampRouter_2); string sId = reader.c("id").ToString(); string sBemberDate = reader.c("timestamp").ToString(); DateTime dtBember = getDateTime_from_string(sBemberDate); dtBember = dtBember.Add(new TimeSpan(1, 0, 0)); TimeSpan ts = dtBember.Subtract(verifoneTimestamp); int iSeconds = ts.Seconds; int iMinutes = ts.Minutes; int iResponseInSeconds = iMinutes * 60 + iSeconds; if (iMinutes > 0) { iMinutes = iMinutes; } xSQL_UpdateBuilder ub = new xSQL_UpdateBuilder(this, "webservice_log", "where id=" + sId); ub.add("dt_timestamp", dtBember); ub.add("response_time", iResponseInSeconds); ub.ExecuteSql(); if (iSeconds > iMaxSeconds) iMaxSeconds = iSeconds; if (iSeconds < iMinSeconds) iMinSeconds = iSeconds; ++i; } } catch (Exception eee) { eee = eee; } } } catch (Exception e) { e = e; } finally { conn.Close(); } return returnList; }
public List<chain_shop_invoice> get_chain_shop_BASKET_list(string sParentShopId, DateTime startDateYear, DateTime endDateYear, DateTime startDatePeriod, DateTime endDatePeriod) { StringBuilder sb = new StringBuilder(); List<chain_shop_invoice> basketList = new List<chain_shop_invoice>(); string sSql = "select s.id shop_id, s.name, basket_total_sum, basket_total_sum / 100 bember_profit, c.pincode_verified, cb.timestamp basket_timestamp, c.accepted_membership_at accepted_membership_timestamp " + "from " + "consumer_basket cb, consumer_paytool cp, consumer c, shop s " + "where " + "cb.shop_id=s.id " + "and " + "s.parent_shop=" + sParentShopId + " " + "and " + "cb.confirmed_by_shop=true " + "and " + "cb.consumer_paytool_id = cp.id " + "and ((" + "cb.timestamp >= '" + buildPostgresDateFromStringDayPrecision(startDateYear) + "' " + "and " + "cb.timestamp <= '" + buildPostgresDateFromStringDayPrecision(endDateYear) + "') " + " or " + "(cb.timestamp >= '" + buildPostgresDateFromStringDayPrecision(startDatePeriod) + "' " + "and " + "cb.timestamp <= '" + buildPostgresDateFromStringDayPrecision(endDatePeriod) + "')) " + "and " + "cp.consumer_id = c.id " + "and " + "(c.pincode_verified='yes' or pincode_verified='no') " + "order by s.name, cb.id desc"; /* select count(*) members_accepted_in_period from consumer c where c.accepted_membership_at > '2015-06-01 00:00' and c.accepted_membership_at < '2015-07-01 00:00' and enrolled_by_shop_id=19 */ 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 sPincodeVerified = (string)reader.c("pincode_verified"); // if (sPincodeVerified == "yes" || sPincodeVerified == "no") if (sPincodeVerified == "yes" || sPincodeVerified == "no") { chain_shop_invoice chainInvoice = new chain_shop_invoice(); chainInvoice.iShopId = (int)reader.c("shop_id"); chainInvoice.acceptedMembershipAt = (DateTime)reader.c("accepted_membership_timestamp"); chainInvoice.basketDatetime = (DateTime)reader.c("basket_timestamp"); if (chainInvoice.basketDatetime >= startDateYear && chainInvoice.basketDatetime <= endDateYear) chainInvoice.bInThisYear = true; if (chainInvoice.basketDatetime >= startDatePeriod && chainInvoice.basketDatetime <= endDatePeriod) chainInvoice.bInThisPeriod = true; chainInvoice.dTotalInvoiceAmount = (decimal)reader.c("basket_total_sum"); if (sPincodeVerified == "yes") { chainInvoice.dBemberInvoiceAmount = (decimal)reader.c("bember_profit"); chainInvoice.bIsMember = true; } else { chainInvoice.dBemberInvoiceAmount = 0; chainInvoice.bIsMember = false; } chainInvoice.sShopName = (string)reader.c("name"); basketList.Add(chainInvoice); } } } catch (Exception e) { basketList = null; } finally { conn.Close(); } return basketList; }
public List<dim_value_value> dash_get_members_by_chain_shop(string sParentShopId, DASHBOARD_PERIOD currentPeriod) { DASHBOARD_MONTH yearMonth = new DASHBOARD_MONTH(currentPeriod); string sPeriodFilter = ""; if (currentPeriod != DASHBOARD_PERIOD.DONT_CARE) { string sFromMonthDate = yearMonth.getThisDbYearMonthDay1String(0); string sBeforeMonthDate = yearMonth.getThisDbYearNextMonthDay1String(); sPeriodFilter = " and " + // lunch "c.created_at >= '" + sFromMonthDate + "' " + "and " + "c.created_at < '" + sBeforeMonthDate + "' "; } /* if (yearMonthPeriod == DASHBOARD_PERIOD.THIS_MONTH || yearMonthPeriod == DASHBOARD_PERIOD.PREV_MONTH || yearMonthPeriod == DASHBOARD_PERIOD.PREV_PREV_MONTH) { sFromMonthDate = yearMonth.getThisDbYearMonthDay1String(0); sBeforeMonthDate = yearMonth.getThisDbYearNextMonthDay1String(); } */ // Mountain Dashboard 003 Antall medlemmer med godkjent/ikke godkjent avtale string sSql = "select s.id shop_id, s.name,c.pincode_verified membership_status,count(*) consumer_count " + "from consumer c, shop s " + "where " + "s.id = c.enrolled_by_shop_id " + sPeriodFilter + "and " + "s.parent_shop=" + sParentShopId + " " + // "and " + // "and (s.id <> 20 and s.id <> 25) " + "group by " + "enrolled_by_shop_id, s.name,s.id, c.pincode_verified " + "order by s.name, c.pincode_verified"; List<dim_value_value> shopList = new List<dim_value_value>(); 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); string sOldShopId = ""; dim_value_value currentShop = null; while (reader.Read()) { string sShopId = reader.c("shop_id").ToString(); if (sShopId != sOldShopId) { currentShop = new dim_value_value(); currentShop.sDim = reader.c("name").ToString(); shopList.Add(currentShop); } string sMembershipStatus = reader.c("membership_status").ToString(); string sConsumerCount = reader.c("consumer_count").ToString(); if (sMembershipStatus == "yes") currentShop.sValue_1 = sConsumerCount; // Has approved SMS // 14 okt = 65 else if (sMembershipStatus == "no") currentShop.sValue_2 = sConsumerCount; // Not approved SMS sOldShopId = sShopId; } } catch (Exception e) { shopList = null; } finally { conn.Close(); } return shopList; }
public admin_basket get_basket(string sBasketRowId) { admin_basket basket = null; List<admin_basket> basketList = new List<admin_basket>(); string sSql = "select id, basket_id, basket_a, basket_b, basket_total_sum,basket_total_discount,confirmed_by_shop,timestamp from consumer_basket where id = " + sBasketRowId; 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); if (reader.Read()) { basket = new admin_basket(); basket.iId = (int)reader.c("id"); basket.sBasketId = reader.c("basket_id").ToString(); basket.sBasket_A = reader.c("basket_a").ToString(); basket.sBasket_B = reader.c("basket_b").ToString(); basket.sTotalSum = reader.c("basket_total_sum").ToString(); basket.sTotalDiscount = reader.c("basket_total_discount").ToString(); basket.sConfirmedByShop = reader.c("confirmed_by_shop").ToString(); if (basket.sConfirmedByShop.ToUpper() == "TRUE") basket.sConfirmedByShop = "Ja"; else if (basket.sConfirmedByShop.ToUpper() == "FALSE") basket.sConfirmedByShop = "Nei"; else basket.sConfirmedByShop = "?"; basket.sTimestamp = reader.c("timestamp").ToString(); } } catch (Exception e) { basket = null; } finally { conn.Close(); } return basket; }
public List<dim_value> get_consumer_sex_list(string sChainId, DASHBOARD_PERIOD currentPeriod) { List<dim_value> sexList = new List<dim_value>(); DASHBOARD_MONTH yearMonth = new DASHBOARD_MONTH(currentPeriod); string sPeriodFilter = " "; if (currentPeriod != DASHBOARD_PERIOD.DONT_CARE) { string sFromMonthDate = yearMonth.getThisDbYearMonthDay1String(0); string sBeforeMonthDate = yearMonth.getThisDbYearNextMonthDay1String(); sPeriodFilter = " and " + // lunch "c.accepted_membership_at >= '" + sFromMonthDate + "' " + "and " + "c.accepted_membership_at < '" + sBeforeMonthDate + "' "; } // Mountain dashboard 006 Antall medlemmer etter kjønn string sSql = "select c.sex, count(*) nof_sex " + "from consumer c, shop s " + "where c.enrolled_by_shop_id=s.id and s.parent_shop=" + sChainId + " " + sPeriodFilter + "and c.pincode_verified='yes' " + "and " + "(sex = 'female' or sex = 'male') " + "group by c.sex "; 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()) { dim_value sex = new dim_value(); sex.sDim = reader.c("sex").ToString(); sex.sValue = reader.c("nof_sex").ToString(); sexList.Add(sex); } } catch (Exception e) { sexList = null; } finally { conn.Close(); } return sexList; }
public string get_average_member_age(string sChainId, DASHBOARD_PERIOD currentPeriod) { string sRetVal = "0.0"; string s10_Years_ago = DateTime.Now.AddYears(-10).Year.ToString(); string s110_Years_ago = DateTime.Now.AddYears(-110).Year.ToString(); // Only accepting age beetween 11 and 109 DASHBOARD_MONTH yearMonth = new DASHBOARD_MONTH(currentPeriod); string sPeriodFilter = " "; if (currentPeriod != DASHBOARD_PERIOD.DONT_CARE) { string sFromMonthDate = yearMonth.getThisDbYearMonthDay1String(0); string sBeforeMonthDate = yearMonth.getThisDbYearNextMonthDay1String(); sPeriodFilter = " and " + // lunch "c.accepted_membership_at >= '" + sFromMonthDate + "' " + "and " + "c.accepted_membership_at < '" + sBeforeMonthDate + "' "; } // Mountain dashboard 007 gjennomsnittsalder string sSql = "select avg(age(c.date_of_birth)) average_day_string " + "from consumer c, shop s " + "where " + "EXTRACT(YEAR FROM c.date_of_birth) > " + s110_Years_ago + " " + "and " + "EXTRACT(YEAR FROM c.date_of_birth) < " + s10_Years_ago + " " + sPeriodFilter + "and " + "c.pincode_verified = 'yes' and s.id = c.enrolled_by_shop_id and s.parent_shop=" + sChainId; // 30 sept 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); if (reader.Read()) { sRetVal = reader.c("average_day_string").ToString(); if (sRetVal != null && sRetVal.Length > 0) { string[] sTab = sRetVal.Split(".".ToCharArray()); if (sTab.Length > 0) sRetVal = sTab[0]; decimal dDays = Convert.ToDecimal(sRetVal); decimal dYears = decimal.Divide(dDays, (decimal)365.25); sRetVal = dYears.ToString("#.#"); if (sRetVal.StartsWith(",") || sRetVal.StartsWith(".")) sRetVal = "0" + sRetVal; } } } catch (Exception e) { sRetVal = "0.0"; } finally { conn.Close(); } if (sRetVal == "") sRetVal = "0.0"; return sRetVal; }
public List<dim_dim_value_value> dash_get_average_member_basket(int iYear, string sChainId) { List<dim_dim_value_value> monthList = new List<dim_dim_value_value>(); int iLastYear = iYear - 1; // Mountain dashboard 005 // Default = this year and the year before, which always will be enough ... string sSql = "select EXTRACT(YEAR FROM timestamp) accepted_year, EXTRACT(MONTH FROM timestamp) accepted_month, count(*) nof_baskets, sum(cb.basket_total_sum) basket_sum " + "from consumer_basket cb, consumer_paytool cp, consumer c, shop s " + "where " + "s.parent_shop = " + sChainId + " " + "and " + "s.id = cb.shop_id " + "and " + "cb.confirmed_by_shop='yes' " + "and " + "cb.consumer_paytool_id=cp.id " + "and " + "cp.consumer_id=c.id " + "and " + "c.pincode_verified='yes' " + "and " + "(EXTRACT(YEAR FROM cb.timestamp)=" + iLastYear + " or EXTRACT(YEAR FROM cb.timestamp)=" + iYear.ToString() + ") " + "group by EXTRACT(YEAR FROM cb.timestamp), EXTRACT(MONTH FROM cb.timestamp) " + "order by EXTRACT(YEAR FROM cb.timestamp), EXTRACT(MONTH FROM cb.timestamp) "; 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()) { dim_dim_value_value month = new dim_dim_value_value(); month.sDim_1 = reader.c("accepted_year").ToString(); month.sDim_2 = reader.c("accepted_month").ToString(); month.sValue_1 = reader.c("nof_baskets").ToString(); month.dValue_1 = Convert.ToInt32(month.sValue_1); month.sValue_2 = reader.c("basket_sum").ToString(); month.dValue_2 = (decimal)reader.c("basket_sum"); monthList.Add(month); } } catch (Exception e) { monthList = null; } finally { conn.Close(); } return monthList; }
public List<chain_shop_standard_report_item> get_chain_shop_total_list(string sParentShopId, DateTime startDate, DateTime endDate) { StringBuilder sb = new StringBuilder(); List<chain_shop_standard_report_item> shopList = new List<chain_shop_standard_report_item>(); string sSql = "select s.id, s.name,count(*) members_accepted_in_period " + "from consumer c, shop s " + "where " + "s.id = c.enrolled_by_shop_id and " + "c.accepted_membership_at >= '" + buildPostgresDateFromStringDayPrecision(startDate) + "' " + "and " + "s.parent_shop=" + sParentShopId + " " + "and " + "c.accepted_membership_at <= '" + buildPostgresDateFromStringDayPrecision(endDate) + "' " + "and " + "c.pincode_verified='yes' " + // Just a double check "group by enrolled_by_shop_id, s.name,s.id " + "order by s.name"; 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()) { chain_shop_standard_report_item shopTotal = new chain_shop_standard_report_item(); shopTotal.iNewMembersInPeriod = Convert.ToInt32(reader.c("members_accepted_in_period").ToString()); shopTotal.iShopId = (int)reader.c("id"); shopTotal.sShopName = (string)reader.c("name"); shopList.Add(shopTotal); } } catch (Exception e) { shopList = null; } finally { conn.Close(); } return shopList; }
public List<admin_shop> get_all_shops(string sChainId, bool bEnrollmentAcceptedInTerminal) { StringBuilder sb = new StringBuilder(); List<admin_shop> shopList = new List<admin_shop>(); string sSql = "select id, name,shop_external_id, accept_terminal_enrollment from shop " + "where parent_shop='" + sChainId + "' and accept_terminal_enrollment=" + bEnrollmentAcceptedInTerminal.ToString() + " order by name"; 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()) { admin_shop shop = new admin_shop(); shop.iId = (int)reader.c("id"); shop.sName = (string)reader.c("name"); shop.sMerchantId = (string)reader.c("shop_external_id"); shop.bAcceptTerminalEnrollment = (bool)reader.c("accept_terminal_enrollment"); shopList.Add(shop); } } catch (Exception e) { shopList = null; } finally { conn.Close(); } return shopList; }
public List<chain_shop_standard_report_item> get_chain_shop_list(string sParentShopId) { StringBuilder sb = new StringBuilder(); List<chain_shop_standard_report_item> shopList = new List<chain_shop_standard_report_item>(); string sSql = "select s.id, s.name " + "from shop s " + "where " + "s.parent_shop=" + sParentShopId + " " + "order by s.name"; 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()) { chain_shop_standard_report_item shopTotal = new chain_shop_standard_report_item(); shopTotal.iShopId = (int)reader.c("id"); shopTotal.sShopName = (string)reader.c("name"); shopList.Add(shopTotal); } } catch (Exception e) { shopList = null; } finally { conn.Close(); } return shopList; }
public admin_shop get_shop(string sShopId) { StringBuilder sb = new StringBuilder(); admin_shop shop = new admin_shop(); string sSql = "select s.id, s.parent_name, s.name, s.shop_external_id, c.value_1 percent, accept_terminal_enrollment, enrollment_sms_text " + "from shop s, shop_coupon sc, coupon c, coupon_type ct " + "where " + "s.id = " + sShopId + " and " + "sc.shop_id = s.id and " + "c.id = sc.coupon_id and " + "c.coupon_type_id=ct.id and " + "ct.name='enrollment_loyalty_discount'"; 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); if (reader.Read()) { shop.iId = (int)reader.c("id"); shop.sParentName = (string)reader.c("parent_name"); shop.sName = (string)reader.c("name"); shop.sMerchantId = (string)reader.c("shop_external_id"); shop.sSmsText = (string)reader.c("enrollment_sms_text"); shop.iLoyaltyPercent = (int)reader.c("percent"); shop.bAcceptTerminalEnrollment = (bool)reader.c("accept_terminal_enrollment"); } else shop = null; } catch (Exception e) { shop = null; } finally { conn.Close(); } if (shop == null) return get_shop_heading_only(sShopId); return shop; }
public List<chain_shop_standard_report_item> fill_baskets_per_member_report(string sChainId, List<chain_shop_standard_report_item> shopList) { // Antall medlemsbonger pr. butikk string sSql = "select s.id shop_id, count(*) count_number " + "from consumer_basket cb, consumer c, consumer_paytool cp, shop s " + "where " + "cb.confirmed_by_shop = 'yes' " + "and " + "cb.consumer_paytool_id = cp.id " + "and " + "cp.consumer_id = c.id " + "and " + "cb.shop_id = s.id " + "and " + "s.parent_shop=" + sChainId + " " + "and " + "c.pincode_verified='yes' " + "group by s.id "; 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()) { int iShopId = (int)reader.c("shop_id"); chain_shop_standard_report_item shop = getReportShopFromId(shopList, iShopId.ToString()); if (shop != null) { int iCountNumber = Convert.ToInt32(reader.c("count_number").ToString()); shop.dNofBasketsInShop = iCountNumber; // shop.dAverageNofBasketsPerMember = iCountNumber; } } } catch (Exception e) { shopList = null; } finally { conn.Close(); } // Antall medlemmer pr. butikk sSql = "select s.id shop_id, count(*) count_number " + "from consumer c, shop s " + "where " + "c.enrolled_by_shop_id = s.id " + "and " + "c.pincode_verified='yes' " + "and " + "s.parent_shop=" + sChainId + " " + "group by s.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()) { int iShopId = (int)reader.c("shop_id"); chain_shop_standard_report_item shop = getReportShopFromId(shopList, iShopId.ToString()); if (shop != null) { int iCountNumber = Convert.ToInt32(reader.c("count_number").ToString()); if (iCountNumber < 1) iCountNumber = 1; shop.dMembersOfShop = iCountNumber; shop.dAverageNofBasketsPerMember = shop.dNofBasketsInShop / shop.dMembersOfShop; } } } catch (Exception e) { shopList = null; } finally { conn.Close(); } return shopList; }
private admin_shop get_shop_heading_only(string sShopId) { StringBuilder sb = new StringBuilder(); admin_shop shop = new admin_shop(); string sSql = "select s.id, s.parent_name,s.shop_external_id, s.name, accept_terminal_enrollment, enrollment_sms_text " + "from shop s, shop_coupon sc, coupon c, coupon_type ct " + "where " + "s.id = " + sShopId; 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); if (reader.Read()) { shop.iId = (int)reader.c("id"); shop.sParentName = (string)reader.c("parent_name"); shop.sMerchantId = (string)reader.c("shop_external_id"); shop.sSmsText = (string)reader.c("enrollment_sms_text"); shop.bAcceptTerminalEnrollment = (bool)reader.c("accept_terminal_enrollment"); shop.sName = (string)reader.c("name"); } } catch (Exception e) { shop = null; } finally { conn.Close(); } return shop; }
public List<dim_value> dash_mini_month_member_old(string sYear, string sChainId) { List<dim_value> yearList = new List<dim_value>(); string sSql = "select count(*) year_value from " + "consumer c, shop s " + "where pincode_verified='yes' and " + " c.enrolled_by_shop_id=s.id and s.parent_shop=" + sChainId + " " + "EXTRACT(YEAR FROM accepted_membership_at)=" + sYear; 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()) { dim_value month = new dim_value(); month.sDim = sYear; month.sValue = reader.c("year_value").ToString(); yearList.Add(month); } } catch (Exception e) { yearList = null; } finally { conn.Close(); } return yearList; }
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; }
public List<name_value_value_value_value> get_conversion_data(DASHBOARD_PERIOD yearMonthPeriod, string sChainId, bool bHalfYear) { List<name_value_value_value_value> monthList = new List<name_value_value_value_value>(); DASHBOARD_MONTH yearMonth = new DASHBOARD_MONTH(yearMonthPeriod); string sFromMonthDate = ""; string sBeforeMonthDate = ""; if (yearMonthPeriod == DASHBOARD_PERIOD.THIS_MONTH || yearMonthPeriod == DASHBOARD_PERIOD.PREV_MONTH || yearMonthPeriod == DASHBOARD_PERIOD.PREV_PREV_MONTH) { if (bHalfYear) sFromMonthDate = yearMonth.getThisDbYearMonthDay1String(-5); else sFromMonthDate = yearMonth.getThisDbYearMonthDay1String(0); sBeforeMonthDate = yearMonth.getThisDbYearNextMonthDay1String(); } /* else if (yearMonthPeriod == DASHBOARD_PERIOD.HALF_YEAR) { sFromMonthDate = yearMonth.getThisDbYearMonthDay1String(-5); sBeforeMonthDate = yearMonth.getThisDbYearNextMonthDay1String(); } */ //Mountain Dashboard 001 // Conversion data string sSql = "select " + "EXTRACT(YEAR FROM ua.timestamp) x_year, " + "EXTRACT(MONTH FROM ua.timestamp) x_month, " + "case " + "when sub_action like 'ecr_new%' then 'yes' " + "when sub_action like 'ecr_ref%' then 'no' " + "when sub_action like 'ecr_already%' then 'already_member' " + "end got_mobile, " + "case " + "when c.pincode_verified = 'yes' then 'yes' " + "when c.pincode_verified = 'no' then 'no_not_yet' " + "else 'no_phone' " + "end approved_sms, " + "count(*) nof_events " + "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) and " + // "(s.id <> 20 and s.id <> 25) and " + "s.parent_shop=" + sChainId + ") " + // "s.parent_shop=" + sChainId + ") " + "left JOIN consumer c ON (c.phone = split_part(ua.parameters, ';', 2)) " + "where (sub_action like 'ecr_refused_enrollment' or sub_action like 'ecr_new_enrollment' or sub_action like 'ecr_already%') " + "and ua.id > 4785 " + "and ua.timestamp >= '" + sFromMonthDate + "' " + "and " + "ua.timestamp < '" + sBeforeMonthDate + "' " + "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) " + "group by x_month,x_year, ua.sub_action, c.pincode_verified " + "order by x_year,x_month, ua.sub_action, c.pincode_verified"; // "order by x_month,x_year, ua.sub_action, c.pincode_verified"; 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); string sLastYearMonth = ""; string sValue_1 = ""; // value_1 : Searching for yes - yes string sValue_2 = ""; // value_2 : Searching for yes - no_not_yet string sValue_3 = ""; // value_3 : Searching for no - whatever string sValue_4 = ""; // value_3 : Searching for no - already_member name_value_value_value_value month = null; while (reader.Read()) { string sMonth = reader.c("x_month").ToString(); string sThisYearMonth = reader.c("x_year").ToString() + "_" + sMonth; if (sThisYearMonth != sLastYearMonth) { month = new name_value_value_value_value(); sValue_1 = ""; sValue_2 = ""; sValue_3 = ""; sValue_4 = ""; } string sGotMobile = reader.c("got_mobile").ToString(); string sApprovedSms = reader.c("approved_sms").ToString(); if (sGotMobile == "yes" && sApprovedSms == "yes") sValue_1 = reader.c("nof_events").ToString(); else if (sGotMobile == "yes" && sApprovedSms == "no_not_yet") sValue_2 = reader.c("nof_events").ToString(); else if (sGotMobile == "no") sValue_3 = reader.c("nof_events").ToString(); else if (sGotMobile == "already_member") sValue_4 = reader.c("nof_events").ToString(); if (sValue_1 != "" && sValue_2 != "" && sValue_3 != "" && sValue_4 != "") { // Test if value_4 already member has values. These must be subtracted ... month.sName = DASHBOARD_MONTH.month_text(Convert.ToInt32(sMonth)); // month.sName = sThisYearMonth; month.sValue1 = sValue_1; month.sValue2 = sValue_2; month.sValue3 = sValue_3; month.sValue4 = sValue_4; monthList.Add(month); } sLastYearMonth = sThisYearMonth; } } catch (Exception e) { monthList = null; } finally { conn.Close(); } return monthList; }
public List<dim_value> get_monthly_invoice(string sChainId) { string sSql = "select " + "s.name, sum(cb.basket_total_sum) shop_sum " + "from consumer_basket cb, consumer_paytool cp, consumer c, shop s " + "where " + "cb.shop_id = s.id " + "and " + "cb.consumer_paytool_id = cp.id " + "and " + "cp.consumer_id = c.id " + "and " + "cb.confirmed_by_shop='yes' " + "group by s.name " + "order by s.name"; /* Med 60 dagers liggetid ... */ /* string sSql = "select " + "s.name, sum(cb.basket_total_sum) shop_sum " + "from consumer_basket cb, consumer_paytool cp, consumer c, shop s " + "where " + "cb.shop_id = s.id " + "and " + "cb.consumer_paytool_id = cp.id " + "and " + "cp.consumer_id = c.id " + "and " + "c.pincode_verified='yes' " + "and " + "cb.confirmed_by_shop='yes' " + "and " + "(EXTRACT(DOY FROM timestamp) + EXTRACT(YEAR FROM timestamp) * 365) > (EXTRACT(DOY FROM accepted_membership_at) + EXTRACT(YEAR FROM accepted_membership_at) * 365) -60 " + "group by s.name " + "order by s.name"; */ GLOBAL_SQL_CONN conn = new GLOBAL_SQL_CONN(this); List<dim_value> shopList = new List<dim_value>(); try { GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn); GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command); while (reader.Read()) { dim_value dimValue = new dim_value(); dimValue.sDim = reader.c("name").ToString(); dimValue.sValue = reader.c("shop_sum").ToString(); shopList.Add(dimValue); } } catch (Exception e) { return null; } finally { conn.Close(); } return shopList; }
public List<shop_top_bottom> get_conversion_data_in_shoplist(DASHBOARD_PERIOD yearMonthPeriod, string sChainId) { List<shop_top_bottom> shopList = new List<shop_top_bottom>(); DASHBOARD_MONTH yearMonth = new DASHBOARD_MONTH(yearMonthPeriod); string sFromMonthDate = ""; string sBeforeMonthDate = ""; sFromMonthDate = yearMonth.getThisDbYearMonthDay1String(0); sBeforeMonthDate = yearMonth.getThisDbYearNextMonthDay1String(); // Mountain Conversion rate Dashboard 02 string sSql = "select s.name," + "case when sub_action like 'ecr_new%' then 'yes' when sub_action like 'ecr_ref%' then 'no' when sub_action like 'ecr_already%' then 'already_member' end got_mobile," + "case when c.pincode_verified = 'yes' then 'yes' when c.pincode_verified = 'no' then 'no_not_yet' else 'no_phone' end approved_sms," + "count(*) nof_events " + "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) and s.parent_shop=" + sChainId + ")" + "left JOIN consumer c ON (c.phone = split_part(ua.parameters, ';', 2)) " + "where " + "(sub_action like 'ecr_refused_enrollment' or sub_action like 'ecr_new_enrollment') and " + "ua.id > 4785 and ua.timestamp >= '" + sFromMonthDate + "' and ua.timestamp < '" + sBeforeMonthDate + "' " + "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) " + "group by s.name, ua.sub_action, c.pincode_verified " + "order by s.name, ua.sub_action, c.pincode_verified"; 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); shop_top_bottom shop = null; // Shop, got_mobile, approved_sms, number_of_events string sPrevShopName = ""; int iii = 0; while (reader.Read()) { ++iii; try { if (iii == 18) { iii = iii; } string sShopName = reader.c("name").ToString(); if (sShopName != sPrevShopName) { if (shop != null) { decimal decimal_1 = DASHBOARD_GREEN.get_percent_of_to_decimal(shop.iNoPhone + shop.iYesPhoneApproved + shop.iYesPhoneNotApproved, shop.iYesPhoneApproved + shop.iYesPhoneNotApproved, false); shop.dLeftMobilePercent = decimal_1; decimal decimal_2 = DASHBOARD_GREEN.get_percent_of_to_decimal(shop.iNoPhone + shop.iYesPhoneApproved + shop.iYesPhoneNotApproved, shop.iYesPhoneApproved, false); shop.dConvertedFromTotalPercent = Convert.ToDecimal(decimal_2); shopList.Add(shop); } shop = new shop_top_bottom(); } shop.sName = sShopName; string sGotMobile = reader.c("got_mobile").ToString(); string sApprovedSms = reader.c("approved_sms").ToString(); /* 1 */ if (sGotMobile == "yes" && sApprovedSms == "yes") shop.iYesPhoneApproved = Convert.ToInt64(reader.c("nof_events").ToString()); /* 2 */ else if (sGotMobile == "yes" && sApprovedSms == "no_not_yet") shop.iYesPhoneNotApproved = Convert.ToInt64(reader.c("nof_events").ToString()); /* 3 */ else if (sGotMobile == "no") shop.iNoPhone = Convert.ToInt64(reader.c("nof_events").ToString()); sPrevShopName = sShopName; } catch (Exception) { int t = 0; } } if (shop != null) { shop.dLeftMobilePercent = DASHBOARD_GREEN.get_percent_of_to_decimal(shop.iNoPhone + shop.iYesPhoneApproved + shop.iYesPhoneNotApproved, shop.iYesPhoneApproved + shop.iYesPhoneNotApproved, false); shop.dConvertedFromTotalPercent = DASHBOARD_GREEN.get_percent_of_to_decimal(shop.iNoPhone + shop.iYesPhoneApproved + shop.iYesPhoneNotApproved, shop.iYesPhoneApproved, false); shopList.Add(shop); } } catch (Exception e) { shopList = null; } finally { conn.Close(); } return shopList; }
public List<SHOP_ITEM> getSelectListOfChains(string sParentShop) { List<SHOP_ITEM> shopList = new List<SHOP_ITEM>(); StringBuilder sb = new StringBuilder(); GLOBAL_SQL_CONN conn = new GLOBAL_SQL_CONN(this); try { string sSql = "select s.name, s.id from shop s where parent_shop=" + sParentShop; GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn); GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command); while (reader.Read()) { shopList.Add(new SHOP_ITEM(reader.c("id").ToString(), reader.c("name").ToString())); } } catch (Exception e) { } finally { conn.Close(); } return shopList; }
public ShopParentChild getShopParentChildFrom(webservice_database databaseParent, string sShopId) { ShopParentChild parentChild = new ShopParentChild(); GLOBAL_SQL_CONN conn = new GLOBAL_SQL_CONN(databaseParent); try { string sSql = "select id, parent_shop from shop where id=" + sShopId; GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn); GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command); if (reader.Read()) { parentChild.sId = reader.c("id").ToString().Trim(); parentChild.sParentId = reader.c("parent_shop").ToString().Trim(); if (isBlank(parentChild.sParentId)) parentChild.bOnTop = true; } } catch (Exception e) { } finally { conn.Close(); } return parentChild; }
private bool setChainEntity() { if (isBlank(sCurrentChainId)) { return false; } GLOBAL_SQL_CONN conn = new GLOBAL_SQL_CONN(this); try { string sSql = "select * from shop where id=" + sCurrentChainId; // b) Aministratorlogin. Få tak i shop_id med sMerchantId ... GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn); GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command); if (reader.Read()) { chainEntity = new backoffice.SHOP_ITEM(reader.c("id").ToString(), reader.c("name").ToString()); } } catch (Exception e) { } finally { conn.Close(); } return false; }