m_array.AddKey(PR_ID); //primary key m_array.AddColumn(PR_CREATIONDATE, t_("Creation Date"), 10) .SetConvert(Single<ConvertYourIntBasedDate>()); ... m_array.AddColumn(COM_NAME, t_("Company Name"), 10); ...
SqlSet GetSqlColumns(const ArrayCtrl &a_array) { SqlSet cols; for(int i = 0; i < a_array.GetIndexCount(); i++) if(!a_array.GetId(i).IsNull()) cols.Cat(SqlId(a_array.GetId(i))); return cols; }
SqlSet cols = GetSqlColumns(m_array); Sql sql; sql * ::Select(Distinct(cols)) .From(PROJECT) .LeftJoin(COMPANY) .On(COM_ID == PR_COMPANYID) .Where(1) .OrderBy(PR_ID) ; for(;;) { Vector<Value> row; if(!sql.Fetch(row)) break; m_array.Add(row); }
struct ConvCompany : Convert { Value Format(const Value &q) const { SQL * Select(COM_NAME).From(COMPANY).Where(COM_ID == q); String company; if(SQL.Fetch()) company = SQL[COM_NAME]; else company = ""; return q.IsNull() ? Null : company; } }; ..... ..... projects.AddColumn(VPR_COMPANYID, t_("Customer")).SetConvert(Single<ConvCompany>());
projects.SetTable(PROJECTV),Leftjoined(COMPANY,VPR_COMPANYID),AS_PREFIX(VCOM_); projects.AddColumn(VCOM_COMPANYNAME, t_("Customer"));
struct NameConvert : Convert { VectorMap<int, String> name; VectorMap<int, String> surname; virtual Value Format(const Value& q) const; void LoadAttrs(); }; Value NameConvert::Format(const Value& q) const { return surname.Get(q, String()) + ' ' + name.Get(q, String()); } void NameConvert::LoadAttrs() { name.Clear(); surname.Clear(); Sql sql; sql * Select(SUBJECT_ID, ATTR, TEXT).From(SUBJECT_ATTR) .OrderBy(Descending(Nvl(TILL, Date::High()))); while(sql.Fetch()) { int sid = sql[SUBJECT_ID]; String id = sql[ATTR]; if(id == "NAME" && name.Find(sid) < 0) name.Add(sid, sql[TEXT]); if(id == "LASTNAME" && surname.Find(sid) < 0) surname.Add(sid, sql[TEXT]); } }
Quote: |
Maybe this way: [code] projects.SetTable(PROJECTV),Leftjoined(COMPANY,VPR_COMPANYID ),AS_PREFIX(VCOM_); projects.AddColumn(VCOM_COMPANYNAME, t_("Customer")); This is the ideal, or something similar. |
sergeynikitin wrote on Sun, 02 November 2008 16:09 |
By the way, I have decided to how to display related fields from other tables by using SqlArray. [code] struct ConvCompany : Convert { Value Format(const Value &q) const { SQL * Select(COM_NAME).From(COMPANY).Where(COM_ID == q); String company; if(SQL.Fetch()) company = SQL[COM_NAME]; else company = ""; return q.IsNull() ? Null : company; } }; |
MySqlSession session; if(!session.Connect(app.mysql_username, app.mysql_password, app.mysql_database, app.mysql_server)) { printf("Can't connect with MySql\n"); PromptOK(t_("Can't connect with MySql")); return; } mysql_set_character_set(session, "utf8"); SQL = session; Sql sql(session);
void Sum(int id) { return SQL % Select(SqlSum(MONEY)).From(TABLE).Where(ID == id); } void PrintSums() { SQL * Select(NAME, MONEY_ID).From(TABLE); while(SQL.Fetch()) Print(SQL[NAME], Sum(SQL[MONEY_ID]); }
Quote: |
MySqlSession session; if(!session.Connect(app.mysql_username, app.mysql_password, app.mysql_database, app.mysql_server)) { printf("Can't connect with MySql\n"); PromptOK(t_("Can't connect with MySql")); return; } mysql_set_character_set(session, "utf8"); SQL = session; Sql sql(session); |
Value ConvNomencl::Format(const Value& q) const { static VectorMap<int, String> nom; static Time lastcleartime; if(GetSysTime()-lastcleartime > 60){ nom.Clear(); lastcleartime=GetSysTime(); } if(nom.GetCount()==0) nom.Add(0, " "); // <==adding empty entry there. int q1=q; int f = nom.Find(q1); if(f > 0){ String s; s = nom.Get(q1); return s; } else { Sql sql; sql * SqlSelect(NOM_ID,NOM_NAME).From(NOMENCL).Where(NOM_ID == q); String nomencl; if(sql.Fetch()) { int sid = sql[NOM_ID]; nom.Add(sid, sql[NOM_NAME]); nomencl = sql[NOM_NAME]; } else { nomencl = ""; } return nomencl; } }
struct ConvCompany : Convert { virtual Value Format(const Value& q) const; }; struct ConvNomencl : Convert { virtual Value Format(const Value& q) const; }; struct ConvManager : Convert { virtual Value Format(const Value& q) const; };
Value ConvCompany::Format(const Value &q) const { if(q.IsNull()) return Null; static VectorMap<int, String> comp; static Time lastcleartime; if(GetSysTime()-lastcleartime > 600){ comp.Clear(); lastcleartime=GetSysTime(); } int f = comp.Find(int(q)); if(f >= 0){ return comp[f]; } else { Sql sql; sql * Select(COM_NAME).From(COMPANY).Where(COM_ID == q); String company; if(sql.Fetch()) { int sid = int(q); company = sql[COM_NAME]; comp.Add(sid, company); } else { company = ""; } return company; } } Value ConvNomencl::Format(const Value& q) const { if(q.IsNull()) return Null; static VectorMap<int, String> nom; static Time lastcleartime; if(GetSysTime()-lastcleartime > 600){ nom.Clear(); lastcleartime=GetSysTime(); } int f = nom.Find(int(q)); if(f >= 0){ return nom[f]; } else { Sql sql; sql * SqlSelect(NOM_ID,NOM_NAME).From(NOMENCL).Where(NOM_ID == q); String nomencl; if(sql.Fetch()) { int sid = sql[NOM_ID]; nomencl = sql[NOM_NAME]; nom.Add(sid, nomencl); } else { nomencl = ""; } return nomencl; } } Value ConvManager::Format(const Value &q) const { if(q.IsNull()) return Null; static VectorMap<int, String> man; static Time lastcleartime; if(GetSysTime()-lastcleartime > 600){ man.Clear(); lastcleartime=GetSysTime(); } int f = man.Find(int(q)); if(f >= 0){ return man[f]; } else { Sql sql; sql * SqlSelect(USR_REALNAME).From(USER).Where(USR_ID == q); String manager; if(sql.Fetch()) { int sid = int(q); manager = sql[USR_REALNAME]; man.Add(sid, manager); } else { manager = ""; } return manager; } }
template <SqlId TBL ,SqlId ID, SqlId NAME,int CLEARTIME> struct ConvDict : Convert //ConvDict<COMPANY,COM_ID,COM_NAME,600>() { virtual Value Format(const Value& q) const { if(q.IsNull()) return Null; static VectorMap<int, String> dict; static Time lastcleartime; if(GetSysTime()-lastcleartime > CLEARTIME){ dict.Clear(); lastcleartime=GetSysTime(); } int f = dict.Find(int(q)); if(f >= 0){ return dict[f]; } else { Sql sql; sql * Select(NAME).From(TBL).Where(ID == q); String s; if(sql.Fetch()) { int sid = int(q); s = sql[NAME]; dict.Add(sid, s); } else { s = ""; } return s; } } };
struct ConvDict : Convert { SqlId TBL ,ID, NAME, CLEARTIME; public: void setValues(SqlId _TBL ,SqlId _ID, SqlId _NAME,int _CLEARTIME) { TBL = _TBL; ID = _ID; NAME = _NAME; CLEARTIME = _CLEARTIME; } // ...
sergeynikitin wrote on Sat, 19 September 2009 14:22 |
The mapping of the field from another table I made with the converter. |
struct ConvDict : Convert { SqlId TBL ,ID, NAME, CLEARTIME; public: void setValues(SqlId _TBL ,SqlId _ID, SqlId _NAME,int _CLEARTIME) { TBL = _TBL; ID = _ID; NAME = _NAME; CLEARTIME = _CLEARTIME; } // ...
Quote: |
2. This method saves SQL trafic and network speed on Huge Database. |
Quote: |
3. View may not be fully compatible with some database engine. |
Quote: |
2. This method saves SQL trafic and network speed on Huge Database. |
Quote: |
3. View may not be fully compatible with some database engine. |
(Single<ConvLookup<COMPANY,COM_ID,COM_NAME> >() )
template<typename T> T& LookupSetup ( T& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid = false, bool valueiscode = true, SqlBool where = true ); template<> DropGrid& LookupSetup ( DropGrid& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where ); template<> WithDropChoice<EditString>& LookupSetup ( WithDropChoice<EditString>& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where ); template<> DropList& LookupSetup ( DropList& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where ); template<typename T> T& LookupSetup1 ( T& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid = false, bool valueiscode = true, SqlBool where = true ); template<> DropGrid& LookupSetup1 ( DropGrid& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid, bool valueiscode, SqlBool where ); template<> WithDropChoice<EditString>& LookupSetup1 ( WithDropChoice<EditString>& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid, bool valueiscode, SqlBool where ); template< SqlId &LookupTable, SqlId &LookupCode, SqlId &LookupValue, bool show_id=false > struct ConvLookup : Convert { Value Format ( const Value &q ) const { int sid = q; if ( q.IsNull() ){ return Null; } else if ( q == 0 ) { return ""; } static VectorMap<int, Value> valuecache; static Time lastcleartime; if ( GetSysTime() - lastcleartime > 600 ) { valuecache.Clear(); lastcleartime = GetSysTime(); } if ( valuecache.Get ( sid, "X") != "X" ) { if (show_id) { return AsString ( sid ) + ": " + AsString(valuecache.Get ( sid, Value() )); } else { return valuecache.Get ( sid, Value() ); } } else { Sql sql; sql * Select ( LookupValue ).From ( LookupTable ).Where ( LookupCode == sid ); if ( sql.Fetch() ) { valuecache.Add ( sid, AsString(sql[LookupValue]) ); if ( valuecache.Get ( sid, "X" ) != "X" ) { if (show_id) { return AsString ( sid ) + ": " + AsString(valuecache.Get ( sid, Value() ) ); } else { return valuecache.Get ( sid, Value() ); } } return sql[LookupValue] ; } else { return AsString ( sid ) + ": ??? " + t_(~LookupTable) + " ???" ; } } return "ERROR RETURN PLACE" ; } };
template<typename T> T& LookupSetup ( T& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where ) {} template<> DropGrid& LookupSetup ( DropGrid& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where ) { Sql sql; if ( grid.GetList().GetColumnCount() == 0 ) { grid.ClearButton(); grid.Resizeable ( false ); grid.AddColumn ( colid, "", showid ? 10 : 0, true ); grid.AddColumn ( colname, showid ? " : " : "", 70 ); if ( showid ) grid.SetValueColumn ( 0 ); else grid.SetValueColumn ( 1 ); if ( valueiscode ) { grid.SetKeyColumn ( 0 ); } else { grid.SetKeyColumn ( 1 ); } if ( showid ) { grid.AddValueColumn ( 0 ); } grid.AddValueColumn ( 1 ); grid.SetFindColumn ( 1 ); grid.NoHeader(); grid.ColorRows(); } grid.Clear(); sql * Select ( colid, colname ).From ( tablename ).Where ( where ).OrderBy ( colid ); while ( sql.Fetch() ) { grid.Add ( sql[colid], sql[colname] ); } return grid; } template<> WithDropChoice<EditString>& LookupSetup ( WithDropChoice<EditString>& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where ) { Sql sql; grid.ClearList(); sql * Select ( colname ).From ( tablename ).Where ( where ).OrderBy ( colname ); while ( sql.Fetch() ) { grid.AddList ( sql[colname] ); } return grid; } template<> DropList& LookupSetup ( DropList& grid, SqlId& tablename, SqlId& colid, SqlId& colname, bool showid, bool valueiscode, SqlBool where ) { Sql sql; if ( grid.GetList().GetColumnCount() == 0 ) { grid.SetDropLines ( 12 ); } grid.ClearList(); sql * Select ( colname ).From ( tablename ).Where ( where ).OrderBy ( colname ); while ( sql.Fetch() ) { grid.Add ( sql[colname] ); } return grid; } template<typename T> T& LookupSetup1 ( T& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid, bool valueiscode, SqlBool where ) {} template<> DropGrid& LookupSetup1 ( DropGrid& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid, bool valueiscode, SqlBool where ) { Sql sql; if ( grid.GetList().GetColumnCount() == 0 ) { grid.ClearButton(); grid.Resizeable ( false ); grid.AddColumn ( colid, "", showid ? 10 : 0, true ); grid.AddColumn ( colname1, valueiscode ? " : " : "", 70 ); grid.AddColumn ( colname2, "", 70 ); if ( showid ) grid.SetValueColumn ( 0 ); else grid.SetValueColumn ( 1 ); if ( valueiscode ) { grid.SetKeyColumn ( 0 ); } else { grid.SetKeyColumn ( 1 ); } if ( showid ) { grid.AddValueColumn ( 0 ); } grid.AddValueColumn ( 1 ); grid.SetFindColumn ( 1 ); grid.NoHeader(); grid.ColorRows(); } grid.Clear(); sql * Select ( colid, colname1, colname2 ).From ( tablename ).Where ( where ).OrderBy ( colid ); while ( sql.Fetch() ) { grid.Add ( sql[colid], sql[colname1], sql[colname2] ); } return grid; } template<> WithDropChoice<EditString>& LookupSetup1 ( WithDropChoice<EditString>& grid, SqlId& tablename, SqlId& colid, SqlId& colname1, SqlId& colname2, bool showid, bool valueiscode, SqlBool where ) { Sql sql; grid.ClearList(); sql * Select ( colname1, colname2 ).From ( tablename ).Where ( where ).OrderBy ( colname1 ); while ( sql.Fetch() ) { grid.AddList ( AsString ( sql[colname1] ) + " " + AsString ( sql[colname2] ) ); } return grid; }
listvisitor.AddColumn ( VISR_COMPANY_ID, t_("Company"), 265 ).SetConvert(Single<ConvLookup<COMPANY,COM_ID,COM_NAME> >()).HeaderAlignCenter();