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