context: using System;
  using System.Data;
  using System.Collections;
  using System.Text.RegularExpressions;
  using System.Web;
  using System.Web.UI;
  using System.Web.UI.WebControls;
  using System.Web.UI.HtmlControls;
  using SubSonic;
  using SubSonic.Controls;
  using SubSonic.Utilities;
  
  public partial class AutoScaffold : Page
  {
  
      private readonly string _manyToManyMap = String.Empty;
      private readonly Button btnSave = new Button();
      private readonly Button btnCancel = new Button();
      private readonly Button btnDelete = new Button();
      private readonly Button btnAdd = new Button();
      private const string SORT_DIRECTION = "SORT_DIRECTION";
      private const string ORDER_BY = "ORDER_BY";
      private const string PROVIDER_NAME = "AS_PROVIDER";
      private const string TABLE_NAME = "AS_TABLE";
  
      protected void Page_Load(object sender, EventArgs e)
      {
  
          if(Request["refresh"] != null)
          {
              DataService.ResetDatabases();
              Response.Redirect(Request.Path);
          }
  
          if (!Page.IsPostBack)
          {
              if (DataService.Providers != null)
              {
                  ddlProviders.Items.Clear();
                  foreach(DataProvider p in DataService.Providers)
                  {
                      ddlProviders.Items.Add(p.Name);
                  }
              }
  
          }
  
          if(ddlProviders.Items.Count > 0)
          {
              ddlProviders.Visible = true;
              pnlError.Visible = false;
              tblWrapper.Visible = true;
              
          }
          else
          {
              ddlProviders.Visible = false;
              pnlError.Visible = true;
              tblWrapper.Visible = false;
          }
          SetupPage();
  
      }
  
      private void SetupPage()
      {
          BuildTableList();
          pnlButtons.Controls.Clear();
          if (HttpContext.Current.Request.QueryString["id"] != null)
          {
              //add in the control bar
              //add in the button row
              pnlGridView.Visible = false;
              pnlDetail.Visible = true;
              //pnlDetail.Controls.Clear();
  
              btnAdd.ID = "btnAdd";
              pnlButtons.Controls.Add(btnAdd);
  
              btnSave.ID = "btnSave";
              pnlButtons.Controls.Add(btnSave);
  
              btnCancel.ID = "btnCancel";
              pnlButtons.Controls.Add(btnCancel);
  
              btnDelete.ID = "btnDelete";
              pnlButtons.Controls.Add(btnDelete);
  
              foreach (Button button in pnlButtons.Controls)
              {
                  ApplyCssClass(button, "scaffoldButton");
              }
  
              btnDelete.OnClientClick = "return CheckDelete();";
  
              btnSave.Text = "Save";
              //btnSaveAndReturn.Text = "Save & Return";
              btnDelete.Text = "Delete";
              btnCancel.Text = "Return";
              btnAdd.Text = "Add";
  
              btnAdd.Click += btnAdd_Click;
              btnSave.Click += btnSave_Click;
              //btnSaveAndReturn.Click += new EventHandler(btnSaveAndReturn_Click);
              btnCancel.Click += btnCancel_Click;
              btnDelete.Click += btnDelete_Click;
  
              string keyID = HttpContext.Current.Request.QueryString["id"];
              tblEditor.Rows.Clear();
              if (keyID != string.Empty && keyID != "0")
              {
                  CreateEditor(true);
                  BindEditor(keyID);
              }
              else
              {
                  CreateEditor(false);
                  btnAdd.Visible = false;
                  btnDelete.Visible = false;
              }
  
              Label lblMessage = new Label();
              lblMessage.ID = "lblMessage";
              pnlDetail.Controls.Add(lblMessage);
  
          }
          else
          {
              pnlGridView.Visible = true;
              pnlDetail.Visible = false;
              BindGrid(String.Empty);
  
              btnAdd.ID = "btnAdd";
              pnlButtons.Controls.Add(btnAdd);
              btnAdd.Text = "Add";
              ApplyCssClass(btnAdd, "scaffoldButton");
              btnAdd.Click += btnAdd_Click;
          }
      }
  
      protected override void OnPreRender(EventArgs e)
      {
          WebUIHelper.EmitClientScripts(Page);
          if (!String.IsNullOrEmpty(ProviderName))
          {
              ddlProviders.SelectedValue = ProviderName;
          }
          base.OnPreRender(e);
      }
  
      private TableSchema.Table _schema;
      private TableSchema.Table Schema
      {
          get
          {
              if (_schema == null || !Utility.IsMatch(_schema.Name, TableName) || !Utility.IsMatch(_schema.Provider.Name, ProviderName))
              {
                  _schema = DataService.GetTableSchema(TableName, ProviderName, TableType.Table);
              }
              return _schema;
          }
      }
  
      private string _providerName;
      private string ProviderName
      {
          get
          {
              if (Session[PROVIDER_NAME] != null)
              {
                  _providerName = Session[PROVIDER_NAME].ToString();
              }
              else
              {
                  Session.Add(PROVIDER_NAME, ddlProviders.SelectedValue);
                  _providerName = ddlProviders.SelectedValue;
              }
              return _providerName;
          }
  
          set
          {
              _providerName = value;
              Session.Add(PROVIDER_NAME, _providerName);
              //Session.Remove(TABLE_NAME);
          }
      }
  
      private string _tableName;
      private string TableName
      {
          get
          {
              if (Session[ProviderName + TABLE_NAME] != null)
              {
                  _tableName = Session[ProviderName + TABLE_NAME].ToString();
              }
              else
              {
                  _tableName = null;
              }
              return _tableName;
          }
      }
  
      private void SetTableName(TableSchema.AbstractTableSchema tblSchema)
      {
          _tableName = tblSchema.Name;
          Session.Add(ProviderName + TABLE_NAME, _tableName);
          lblHeader.Text = tblSchema.DisplayName;
      }
  
      private void SetTableName(string tableName)
      {
          TableSchema.Table schema = DataService.GetTableSchema(tableName, ProviderName, TableType.Table);
          SetTableName(schema);
      }
  
      private void BuildTableList()
      {
          pnlTableList.Controls.Clear();
          TableSchema.Table[] tables = DataService.GetTables(ProviderName);
          foreach (TableSchema.Table tblSchema in tables)
          {
              if (CodeService.ShouldGenerate(tblSchema.Name, tblSchema.Provider.Name))
              {
                  LiteralControl lcDivStart = new LiteralControl("<div style=\"padding:2px 2px 4px 2px\">");
                  pnlTableList.Controls.Add(lcDivStart);
                  LinkButton lb = new LinkButton();
                  lb.ID = "lbt" + tblSchema.Name;
                  pnlTableList.Controls.Add(lb);
                  lb.Text = tblSchema.DisplayName;
  
                  if(tblSchema.PrimaryKey != null && tblSchema.PrimaryKeys.Length > 0)
                  {
                      lb.CommandName = tblSchema.Name;
                      lb.CommandArgument = tblSchema.DisplayName;
                      lb.Click += lb_Click;
                  }
                  else
                  {
                      lb.Enabled = false;
                  }
                  LiteralControl lcDivEnd = new LiteralControl("</div>");
                  pnlTableList.Controls.Add(lcDivEnd);
              }
          }
          if (tables.Length > 0 && String.IsNullOrEmpty(TableName))
          {
              for (int i = 0; i < tables.Length; i++)
              {
                  if (tables[i].PrimaryKey != null && CodeService.ShouldGenerate(tables[i].Name, tables[i].Provider.Name))
                  {
                      SetTableName(tables[i]);
                      break;
                  }
              }
          }
      }
  
      private void BindGrid(string orderBy)
      {
          //TableSchema.Table schema = DataService.GetTableSchema(tableName, providerName);
          if (Schema != null && Schema.PrimaryKey != null)
          {
              SetTableName(Schema);
              Query query = new Query(Schema);
  
              string sortColumn = null;
              if(!String.IsNullOrEmpty(orderBy))
              {
                  sortColumn = orderBy;
              }
              else if(ViewState[ORDER_BY] != null)
              {
                  sortColumn = (string)ViewState[ORDER_BY];
              }
  
              int colIndex = -1;
  
              if (!String.IsNullOrEmpty(sortColumn))
              {
                  ViewState.Add(ORDER_BY, sortColumn);
                  TableSchema.TableColumn col = Schema.GetColumn(sortColumn);
                  if(col == null)
                  {
                      for (int i = 0; i < Schema.Columns.Count; i++)
                      {
                          TableSchema.TableColumn fkCol = Schema.Columns[i];
                          if(fkCol.IsForeignKey && !String.IsNullOrEmpty(fkCol.ForeignKeyTableName))
                          {
                              TableSchema.Table fkTbl = DataService.GetSchema(fkCol.ForeignKeyTableName, ProviderName, TableType.Table);
                              if(fkTbl != null)
                              {
                                  col = fkTbl.Columns[1];
                                  colIndex = i;
                                  break;
                              }
                          }
                      }
                  }
                  if(col != null && col.MaxLength < 2048)
                  {
                      if (ViewState[SORT_DIRECTION] == null || ((string)ViewState[SORT_DIRECTION]) == SqlFragment.ASC)
                      {
                          if (colIndex > -1)
                          {
                              query.OrderBy = OrderBy.Asc(col, SqlFragment.JOIN_PREFIX + colIndex);
                          }
                          else
                          {
                              query.OrderBy = OrderBy.Asc(col);
                          }
                          ViewState[SORT_DIRECTION] = SqlFragment.ASC;
                      }
                      else
                      {
                          if (colIndex > -1)
                          {
                              query.OrderBy = OrderBy.Desc(col, SqlFragment.JOIN_PREFIX + colIndex);
                          }
                          else
                          {
                              query.OrderBy = OrderBy.Desc(col);
                          }
                          ViewState[SORT_DIRECTION] = SqlFragment.DESC;
                      }
                  }
              }
             
              
              DataTable dt = query.ExecuteJoinedDataSet().Tables[0];
              grid.DataSource = dt;
              grid.AutoGenerateColumns = false;
              grid.Columns.Clear();
  
              HyperLinkField link = new HyperLinkField();
              link.Text = "Edit";
              link.DataNavigateUrlFields = new string[] { Schema.PrimaryKey.ColumnName };
              link.DataNavigateUrlFormatString = HttpContext.Current.Request.CurrentExecutionFilePath + "?id={0}";
              grid.Columns.Insert(0, link);
  
              for (int i = 0; i < Schema.Columns.Count; i++ )
              {
  
                  BoundField field = new BoundField();
                  field.DataField = dt.Columns[i].ColumnName;
                  field.SortExpression = dt.Columns[i].ColumnName;
                  //field.SortExpression = Utility.QualifyColumnName(Schema.Name, dt.Columns[i].ColumnName, Schema.ProviderType);
                  field.HtmlEncode = false;
                  if (Schema.Columns[i].IsForeignKey)
                  {
                      TableSchema.Table schema;
                      if(Schema.Columns[i].ForeignKeyTableName == null)
                      {
                          schema = DataService.GetForeignKeyTable(Schema.Columns[i], Schema);
                      }
                      else
                      {
                          schema = DataService.GetSchema(Schema.Columns[i].ForeignKeyTableName, ProviderName, TableType.Table);
                      }
                      if (schema != null)
                      {
                          field.HeaderText = schema.DisplayName;
                      }
                  }
                  else
                  {
                      field.HeaderText = Schema.Columns[i].DisplayName;
                  }
  
                  field.HeaderText = CleanColumnName(field.HeaderText);
  
                  if (!Utility.IsAuditField(dt.Columns[i].ColumnName))
                  {
                      grid.Columns.Add(field);
                  }
              }
  
              grid.DataBind();
          }
      }
  
      /// <summary>
      /// Used to apply CSS class values to WebControls. Ensures that no empty classes are applied;
      /// </summary>
      /// <param name="control"></param>
      /// <param name="cssClass"></param>
      private static void ApplyCssClass(WebControl control, string cssClass)
      {
          if (!String.IsNullOrEmpty(cssClass))
          {
              control.CssClass = cssClass;
          }
      }
  
  
      /// <summary>
      /// Used to apply class attribute to HtmlControls. Ensures that no empty classes are applied;
      /// </summary>
      /// <param name="control"></param>
      /// <param name="cssClass"></param>
      private static void ApplyCssClass(HtmlControl control, string cssClass)
      {
          if (!String.IsNullOrEmpty(cssClass))
          {
              control.Attributes.Add("class", cssClass);
          }
      }
  
      /// <summary>
      /// 
      /// </summary>
      /// <param name="tbl"></param>
      /// <param name="text"></param>
      /// <param name="colspan"></param>
      private static void AddRow(HtmlTable tbl, string text, int colspan)
      {
          HtmlTableRow tr = new HtmlTableRow();
          tbl.Rows.Add(tr);
  
          HtmlTableCell td = new HtmlTableCell();
          tr.Cells.Add(td);
          ApplyCssClass(td, "scaffoldEditItem");
  
          if (colspan > 0)
          {
              td.ColSpan = colspan;
          }
          td.InnerHtml = text;
      }
  
      /// <summary>
      /// 
      /// </summary>
      /// <param name="tbl"></param>
      /// <param name="cellValue1"></param>
      /// <param name="control"></param>
      private static void AddRow(HtmlTable tbl, string cellValue1, Control control)
      {
          HtmlTableRow tr = new HtmlTableRow();
          tbl.Rows.Add(tr);
  
          HtmlTableCell td = new HtmlTableCell();
          tr.Cells.Add(td);
  
          //label
          ApplyCssClass(td, "scaffoldEditItemCaption");
          td.InnerHtml = "<b>" + cellValue1 + "</b>";
  
          //control
          HtmlTableCell td2 = new HtmlTableCell();
          tr.Cells.Add(td2);
          ApplyCssClass(td, "scaffoldEditItemCaption");
          td2.Controls.Add(control);
      }
  
      /// <summary>
      /// Special builder for many to many relational tables.
      /// </summary>
      /// <returns></returns>
      private void CreateManyMapper(TableSchema.Table schema)
      {
          //add a header row
          AddRow(tblEditor, "<h2>" + schema.DisplayName + " Map </h2>", 2);
  
          foreach (TableSchema.TableColumn col in schema.Columns)
          {
              //by convention, each key in the map table should be a foreignkey
              //if not, it's not good
              if (col.IsPrimaryKey && col.IsForeignKey)
              {
                  string fkTable = col.ForeignKeyTableName;
                  //fkTable = DataService.GetForeignKeyTableName(col.ColumnName, schema.Name, ProviderName);
                  if (!String.IsNullOrEmpty(fkTable))
                  {
                      //Query qry = new Query(DataService.GetTableSchema(fkTable, ProviderName, TableType.Table));
                      TableSchema.Table fkSchema = DataService.GetTableSchema(fkTable, ProviderName, TableType.Table);
                      Query qry = new Query(fkSchema).ORDER_BY(fkSchema.Columns[1]);
                      DropDownList ddl = new DropDownList();
                      ddl.ID = col.ColumnName;
                      AddRow(tblEditor, fkTable, ddl);
  
                      IDataReader rdr = qry.ExecuteReader();
                      while (rdr.Read())
                      {
                          ddl.Items.Add(new ListItem(rdr[1].ToString(), rdr[0].ToString()));
                      }
                      rdr.Close();
                  }
              }
              else
              {
                  Control ctrl = GetEditControl(col);
                  AddRow(tblEditor, Utility.ParseCamelToProper(col.ColumnName), ctrl);
              }
          }
      }
  
      /// <summary>
      /// 
      /// </summary>
      /// <returns></returns>
      private static bool isManyToMany(TableSchema.Table schema)
      {
          int keyCount = 0;
          bool bOut = false;
  
          foreach (TableSchema.TableColumn col in schema.Columns)
          {
              if (col.IsPrimaryKey)
              {
                  keyCount++;
              }
          }
  
          if (keyCount > 1)
          {
              bOut = true;
          }
          return bOut;
      }
  
      /// <summary>
      /// 
      /// </summary>
      /// <param name="isEdit"></param>
      /// <returns></returns>
      private void CreateEditor(bool isEdit)
      {
          //if this is a many to many, we need to construct it differently
          //tbl = new HtmlTable();
          //tbl.ID = "tblEditor";
          //pnlDetail.Controls.Add(tbl);
          if (isManyToMany(Schema))
          {
              CreateManyMapper(Schema);
          }
          else
          {
  
              //add a header row
              //AddRow(tbl, "<h2>" + schema.DisplayName + " Editor</h2>", 2);
  
              foreach (TableSchema.TableColumn col in Schema.Columns)
              {
                  Control ctrl = GetEditControl(col);
                  if (ctrl != null)
                  {
                      string label = col.DisplayName;
                      //label = Utility.ParseCamelToProper(col.ColumnName);
                      //label = CleanColumnName(label);
                      AddRow(tblEditor, label, ctrl);
                      if (ctrl.GetType() == typeof(TextBox))
                      {
                          TextBox tbx = (TextBox)ctrl;
                          if (tbx.TextMode == TextBoxMode.MultiLine)
                          {
                              int efftectiveMaxLength = Utility.GetEffectiveMaxLength(col);
                              string remainingLength = (efftectiveMaxLength - tbx.Text.Length).ToString();
                              string maxLength = efftectiveMaxLength.ToString();
  
                              tbx.Attributes.Add("onkeyup", "return imposeMaxLength(event, this, " + maxLength + ", " + tblEditor.Rows.Count + ");");
                              tbx.Attributes.Add("onChange", "return imposeMaxLength(event, this, " + maxLength + ", " + tblEditor.Rows.Count + ");");
                              LiteralControl lc = new LiteralControl("<div style='padding: 2px;'><div style='float:left'>Characters Remaining:&nbsp;</div><div id=\"counter" + tblEditor.Rows.Count + "\" style=\"visibility:hidden\">" + remainingLength + "</div></div>");
                              tbx.Parent.Controls.Add(lc);
                          }
                      }
                      //else if(ctrl is CalendarControl)
                      //{
                      //    ((Calendar)ctrl).SelectionChanged += new EventHandler(cal_SelectionChanged);
                      //}
                  }
              }
              //need a primary key for many/many editing
              if (!String.IsNullOrEmpty(_manyToManyMap) && isEdit)
              {
                  AddManyToMany(isEdit, tblEditor);
              }
          }
      }
  
      /// <summary>
      /// Converts column name to a value appropriate for display. 
      /// Removes "ID" from the end of columns, can make other changes.
      /// </summary>
      /// <param name="columnName"></param>
      /// <returns></returns>
      private static string CleanColumnName(string columnName)
      {
          //if (!CLEAN_COLUMN_NAMES)
              return columnName;
  
          //if(columnName.EndsWith("ID") && columnName.Length > 2)
          //{
          //    columnName = columnName.Remove(columnName.Length - 2);
          //}
          //return columnName;
      }
  
      /// <summary>
      /// 
      /// </summary>
      /// <param name="keyID"></param>
      private void BindEditor(string keyID)
      {
          //get all the data for this row
          Query qry = new Query(Schema);
          qry.AddWhere(Schema.PrimaryKey.ColumnName, keyID);
          IDataReader rdr = qry.ExecuteReader();
  
          if (rdr.Read())
          {
              foreach (TableSchema.TableColumn col in Schema.Columns)
              {
                  if (col.IsPrimaryKey && !col.IsForeignKey)
                  {
                      Control ctrl = tblEditor.FindControl("pkID");
                      if (ctrl != null)
                      {
                          Type ctrlType = ctrl.GetType();
  
                          string colValue = rdr[col.ColumnName].ToString();
                          if(ctrlType == typeof(Label))
                          {
                              ((Label)ctrl).Text = colValue;
                          }
                          else if(ctrlType == typeof(DropDownList))
                          {
                              ((DropDownList)ctrl).SelectedValue = colValue;
                          }
                          else if(ctrlType == typeof(TextBox))
                          {
                              ((TextBox)ctrl).Text = colValue;
                          }
                      }
                  }
                  else
                  {
                      Control ctrl = tblEditor.FindControl(col.ColumnName);
                      if (ctrl != null)
                      {
                          Type ctrlType = ctrl.GetType();
                          if(ctrlType == typeof(TextBox))
                          {
                              TextBox tbx = ((TextBox)ctrl);
                              tbx.Text = rdr[col.ColumnName].ToString();
                          }
                          else if(ctrlType == typeof(CheckBox))
                          {
                              if(!col.IsNullable || (col.IsNullable && rdr[col.ColumnName] != DBNull.Value))
                              {
                                  ((CheckBox)ctrl).Checked = Convert.ToBoolean(rdr[col.ColumnName]);
                              }
                          }
                          else if(ctrlType == typeof(DropDownList))
                          {
                              ((DropDownList)ctrl).SelectedValue = rdr[col.ColumnName].ToString();
                          }
                          else if(ctrlType == typeof(CalendarControl))
                          {
  
                              CalendarControl cal = (CalendarControl)ctrl;
                              DateTime dt;
                              if(ViewState["vs" + cal.ID] != null)
                              {
                                  dt = (DateTime)ViewState["vs" + cal.ID];
                              }
                              else
                              {
                                  DateTime.TryParse(rdr[col.ColumnName].ToString(), out dt);
                              }
                              cal.SelectedDate = dt.Date;
                              //cal.VisibleDate = dt.Date;
                              ViewState["vs" + cal.ID] = dt;
                          }
                          else if(ctrlType == typeof(Label))
                          {
                              ((Label)ctrl).Text = rdr[col.ColumnName].ToString();
                          }
                      }
                  }
              }
          }
      }
  
      //void cal_SelectionChanged(object sender, EventArgs e)
      //{
      //    CalendarControl cal = (CalendarControl)sender;
      //    ViewState.Add("vs" + cal.ID, cal.SelectedDate.Date);
      //    //throw new Exception("The method or operation is not implemented.");
      //}
  
      /// <summary>
      /// 
      /// </summary>
      /// <param name="isEdit"></param>
      /// <param name="table"></param>
      private void AddManyToMany(bool isEdit, HtmlTable table)
      {
          string[] mmTables = _manyToManyMap.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
  
          if (mmTables.Length > 0)
          {
              foreach (string mmTableName in mmTables)
              {
                  TableSchema.Table mmTable = Query.BuildTableSchema(mmTableName);
  
                  //this table should have one or more primary keys
                  //one of these keys should, by convention, have the same name
                  //as the primary key of our main schema table
                  //need to get this key, then find it's table
                  foreach (TableSchema.TableColumn col in mmTable.Columns)
                  {
                      if (col.IsPrimaryKey && col.ColumnName.ToLower() != Schema.PrimaryKey.ColumnName.ToLower())
                      {
                          //this is the key we need. Get the table for this key
                          string fTableName = DataService.GetForeignKeyTableName(col.ColumnName, mmTableName, ProviderName);
  
                          if (fTableName != string.Empty && isEdit)
                          {
                              CheckBoxList chk = new CheckBoxList();
                              chk.ID = mmTableName;
                              //add the checkbox in
  
gt:                             AddRow(table, Utility.ParseCamelToProper(mmTableName), chk);