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