using System;
using System.IO;
using System.Xml;
using System.Text;
using System.Collections;
using System.Collections.Specialized;
using Microsoft.MetadirectoryServices;
using System.Data; 
using System.Data.SqlClient;  
using System.Data.OleDb;

namespace Miis_CallExport
{
  public class MACallExport :  
  IMAExtensibleFileImport, 
  IMAExtensibleCallExport
  {
  public MACallExport() {}

  // ------------------ C O N S T A N T S ------------------------
  private const string PARAM_SQLTABLENAME = "Table";
  private const string PARAM_SQLWHERECLAUSE = "SQLWhereClause";
  private const string PARAM_SQLDATABASE = "Database";
  private const string PARAM_SQLTIMEOUT = "Timeout";
  private const string PARAM_SQLSECURITY = "Security";
  private const string PARAM_SQLPROVIDER = "Provider";
  // Not needed: private const string 
  // PARAM_ESCAPEANCHOR = "EscapeAnchor";


  // ------------------  I M P O R T  -----------------------------
  //
  private string EscapeStringForImport(string escape)
  {
  if(escape == null)
  return null;

  escape.Replace("\"", "\"\""); 
  return String.Format("\"{0}\"", escape);
  } // EscapeStringForImport

  private string ConstructConnectionString
  (
  string                      connectTo, 
  string                      user, 
  string                      password, 
  ConfigParameterCollection   configParameters
  )
  {
  string connectionString = "";
  if(configParameters[PARAM_SQLSECURITY].Value.ToUpper().Equals
     ("SSPI"))
  {
  connectionString = String.Format("Provider={0};
     Persist Security Info=False;Integrated Security=SSPI;
     Initial Catalog={1};Data Source={2};Connect Timeout={3}",
  configParameters[PARAM_SQLPROVIDER].Value,
  configParameters[PARAM_SQLDATABASE].Value,
  connectTo,
  configParameters[PARAM_SQLTIMEOUT].Value);
  }
  else
  {
  connectionString = String.Format("Provider={0};
     Persist Security Info=False;User ID={1};Password={2};
     Initial Catalog={3};Data Source={4};Connect Timeout={5}",
  configParameters[PARAM_SQLPROVIDER].Value,
  user,
  password,
  configParameters[PARAM_SQLDATABASE].Value,
  connectTo,
  configParameters[PARAM_SQLTIMEOUT].Value);
  }
  return connectionString;
  } //ConstructConnectionString

  public void GenerateImportFile
  ( 
  string                      filename, 
  string                      connectTo, 
  string                      user, 
  string                      password, 
  ConfigParameterCollection   configParameters,
  bool                        fullImport, 
  TypeDescriptionCollection   types,
  ref string                  customData 
  )
  {
  if(!fullImport)
  {
  throw new TerminateRunException
     ("This MA only supports full import"); 
  }

  //---------------------------------------------------------------
  // Construct the columns to select from the SQL table by
  // enumerating through the attributes of the first object type.
  // This assumes that the DB MA schema exposes all attributes 
   // available for all object types
  //---------------------------------------------------------------
  string sqlColumns = "";
  string objectType = "";
  ArrayList attributeList = new ArrayList();
  foreach(TypeDescription t in types)
  {
  objectType = t.Name; 
  foreach(AttributeDescription a in t.Attributes)
  {
  sqlColumns = sqlColumns + a.Name + ","; 
  attributeList.Add(a.Name);
  }
  break;
  }
  if(sqlColumns.Equals(""))
  {
  throw new TerminateRunException(
     "No attributes in schema definition");
  }
   // remove last comma
  sqlColumns = sqlColumns.Substring(0, sqlColumns.Length-1); 

  //----------------------------------------------------------------
  // Construct the SQL Select statement for the intermediate import 
   // file.
  //----------------------------------------------------------------
  string commandString = String.Format("SELECT {0} FROM {1}",
  sqlColumns,
  configParameters[PARAM_SQLTABLENAME].Value);
  try
  {
  if(!configParameters[PARAM_SQLWHERECLAUSE].Value.Equals(""))
  {
  commandString = String.Format("{0} WHERE {1}", commandString, 
     configParameters[PARAM_SQLWHERECLAUSE].Value); 
  }
  }
  // Ignore if the parameter "SQLWhereClause" doesn't exist
   catch {  }
   
  //----------------------------------------------------------------
  // Construct the connection string and connect to the SQL database
  //----------------------------------------------------------------
  string connectionString = ConstructConnectionString(connectTo, 
   user, password, configParameters);

  OleDbConnection myConnection = new OleDbConnection
    (connectionString);
  OleDbCommand myCommand = new OleDbCommand(commandString, 
     myConnection);

  //--------------------------------------------------------------
  // Open the SQL database and create the intermediate file
  // Note that this code only supports String, Integer, GUID, and 
  // DBNULL values
  // Further datatypes must be added by extending the code.
  //---------------------------------------------------------------
  myConnection.Open(); 
  OleDbDataReader result = myCommand.ExecuteReader
     (CommandBehavior.CloseConnection);

  StreamWriter stream = new StreamWriter(filename, false, 
     System.Text.Encoding.Unicode);
  while(result.Read())
  {
  for(int i=0; i<result.FieldCount;i++)
  {
  if(result[i] is string)
  stream.WriteLine(String.Format("{0}: {1}", attributeList[i], 
        result[i]));
  else if (result[i] is System.Int64)
  stream.WriteLine(String.Format("{0}: {1}", attributeList[i],  
        result[i]));
  else if(result[i] is System.Guid)
  stream.WriteLine(String.Format("{0}: {{{1}}}", attributeList[i], 
        System.Convert.ToString(result[i])));
  else if (result[i] is System.DBNull)
  ; // no output --> NULL value
  else
  throw new UnexpectedDataException(String.Format
       ("Unknown type in SQL Column: {0}", attributeList[i]));  }
  stream.WriteLine(); // new record, seperate by new line
  }
  stream.Close();

  } //GenerateImportFile


  // ------------------  E X P O R T  -------------------------------
  // Globals for Export routines
  private string ExportConnectionString;
  private string ExportTableName;
  private OleDbConnection ExportConnection;
  private TypeDescriptionCollection ExportTypes;
  // not needed: private bool ExportEscapeAnchor;

  public void BeginExport
  ( 
  string                      connectTo, 
  string                      user, 
  string                      password,
  ConfigParameterCollection   configParameters,
  TypeDescriptionCollection   types
  )
  {
  //----------------------------------------------------------------
  // Construct the connection string and connect to the SQL database
  //----------------------------------------------------------------
  ExportConnectionString = ConstructConnectionString(connectTo, user, 
     password, configParameters);
  ExportConnection = new OleDbConnection(ExportConnectionString);
  ExportConnection.Open(); 

  // Save Type (Schema) information for later to determine 
   // the anchor for a given object type
  ExportTypes = types;

  // Save the Database table name ro run the Insert/Update/Deletes
  ExportTableName = configParameters[PARAM_SQLTABLENAME].Value;

  // Save for later if in the where clause the anchor needs to get 
  // enclosed in ''
   // not needed: ExportEscapeAnchor = configParameters
   // [PARAM_ESCAPEANCHOR].Value.ToLower().Equals("yes") ? 
   // true : false;  
  }

  public void ExportEntry
  ( 
  ModificationType    modificationType, 
  string[]            changedAttributes,
  CSEntry             csentry 
  )
  {
  switch(modificationType)
  {
  case ModificationType.Replace:
  UpdateEntry(changedAttributes, csentry);
  break;

  case ModificationType.Add:
  InsertEntry(changedAttributes, csentry);
  break;

  case ModificationType.Delete:
  DeleteEntry(csentry);
  break;
  }
  } //ExportEntry

  private string EscapeStringForExport(string escape)
  {
  if(escape == null)
  return null;

  return escape.Replace("'", "''");
  } // EscapeStringForExport

  private string GetAnchorAttributeName(CSEntry cs)
  {
  TypeDescription t = ExportTypes[cs.ObjectType]; 
  //-----------------------------------------------------------
  // Note that this MA only supports one Anchor attribute
  //-----------------------------------------------------------
  if(t.AnchorAttributes.Count > 1) 
  throw new TerminateRunException(String.Format("ObjectType {0} 
     has more than one anchor attribute", cs.ObjectType));

  foreach(AttributeDescription attr in t.AnchorAttributes)
  {
  return attr.Name; 
  }
  return null;
  } // GetAnchorAttributeName

  private bool EscapeAnchorAttribute(CSEntry cs)
  {
  TypeDescription t = ExportTypes[cs.ObjectType]; 
  //-----------------------------------------------------------
  // Note that this MA only supports one Anchor attribute
  //-----------------------------------------------------------
  if(t.AnchorAttributes.Count > 1) 
  throw new TerminateRunException(String.Format("ObjectType {0} 
     has more than one anchor attribute", cs.ObjectType));

  foreach(AttributeDescription attr in t.AnchorAttributes)
  {
  switch (attr.DataType)
  {
  case AttributeType.String:
  return true;
  case AttributeType.Integer:
  return false;
  case AttributeType.Binary:
  return true;
  default:
  throw new TerminateRunException(String.Format("Anchor type 
       unsupported. ObjectType: {0}, Attribute: {1}, Type: {2}", 
       cs.ObjectType, attr.Name, attr.DataType.ToString()));
  }
  }
  return true;
  } // EscapeAnchorAttribute

  private void UpdateEntry(string[] changedAttributes, CSEntry cs)
  {
  //----------------------------------------------------------------
  // Build the SQL Update Query
  //----------------------------------------------------------------
  string commandString = String.Format("UPDATE {0} SET", 
     ExportTableName);
  foreach(string attribName in changedAttributes)
  {
  string val = "";
  Attrib attr = cs[attribName]; 
  if(attr.IsPresent)
  {
  switch(attr.DataType)
  {
  case AttributeType.String:
  val = "'" + EscapeStringForExport(attr.StringValue) + "'";
  break;
  case AttributeType.Integer:
  val = System.Convert.ToString(attr.IntegerValue, 10);   
  break;
  default:
  throw new UnexpectedDataException(String.Format("Unknown data 
        type for export update. Attr: {0}, Type: {1}", attribName, 
        attr.DataType.ToString())); 
  }
  }
  else
  {
  val = "NULL";
  }

  commandString = commandString + String.Format(" {0}={1},", 
      attribName, val);
  }
  commandString = commandString.Substring(0, commandString.Length-1); 
   // remove last comma

  string anchor = GetAnchorAttributeName(cs);
  string anchorValue = cs[anchor].Value.ToString();
  if(EscapeAnchorAttribute(cs))
  {
  anchorValue = String.Format("'{0}'", anchorValue); 
  }
  commandString = commandString + String.Format(" WHERE {0}={1}", 
    anchor, anchorValue);
  //----------------------------------------------------------------
  // Build the SQL Update Query
  //----------------------------------------------------------------

  //----------------------------------------------------------------
  // Execute SQL Update Query
  //----------------------------------------------------------------
  OleDbCommand myCommand = new OleDbCommand(commandString, 
     ExportConnection);
  try { myCommand.ExecuteNonQuery(); }
  catch { throw; } // need better exception to report back the 
     command sent to SQL

  } // UpdateEntry

  private void InsertEntry(string[] changedAttributes, CSEntry cs)
  {
  //---------------------------------------------------------------
  // Build the SQL Insert Query
  //---------------------------------------------------------------
  string commandString = String.Format("INSERT INTO {0} 
     (", ExportTableName);
  foreach(string attribName in changedAttributes)
  {
  commandString = commandString + attribName + ",";
  }
  commandString = commandString.Substring(0, commandString.Length-1); 
   // remove last comma
  commandString = commandString + ") VALUES (";
 
  foreach(string attribName in changedAttributes)
  {
  string val = "";
  Attrib attr = cs[attribName]; 
  if(attr.IsPresent)
  {
  switch(attr.DataType)
  {
  case AttributeType.String:
  val = "'" + EscapeStringForExport(attr.StringValue) + "'";
  break;
  case AttributeType.Integer:
  val = System.Convert.ToString(attr.IntegerValue, 10);   
  break;
  case AttributeType.Binary: // assuming GUID value (e.g. Anchor)
  val = "'" + attr.Value.ToString() + "'";  
  break;
  default:
  throw new UnexpectedDataException(String.Format("Unknown data 
        type for export add. Attr: {0} Type: {1}" + attribName, 
        attr.DataType.ToString())); 
  }
  }
  else
  {
  val = "NULL";
  }

  commandString = commandString + val + ",";
  }
  commandString = commandString.Substring(0, commandString.Length-1); 
   // remove last comma
  commandString = commandString + ")";
  //---------------------------------------------------------------
  // Build the SQL Insert Query
  //---------------------------------------------------------------

  //---------------------------------------------------------------
  // Execute the SQL Insert Query
  //---------------------------------------------------------------
  OleDbCommand myCommand = new OleDbCommand(commandString, 
    ExportConnection);
  try { myCommand.ExecuteNonQuery(); }
  catch { throw; } // need better exception to report back the command 
    sent to SQL
  } // insert entry

  private void DeleteEntry(CSEntry cs)
  {
  string anchor = GetAnchorAttributeName(cs);
  string anchorValue = cs[anchor].Value.ToString();
  if(EscapeAnchorAttribute(cs))
  {
  anchorValue = String.Format("'{0}'", anchorValue); 
  }

  // Build the SQL Delete Query
  string commandString = String.Format("DELETE FROM {0} 
     WHERE {1}={2}", ExportTableName, anchor, anchorValue);

  // Execute the SQL Delete Query
  OleDbCommand myCommand = new OleDbCommand(commandString, 
     ExportConnection);
  try { myCommand.ExecuteNonQuery(); }
  catch { throw; } // need better exception to report back the 
     command sent to SQL
  } 
  // DeleteEntry

  public void EndExport()
  {
  ExportConnection.Close();
  }
  }
}